mbloecker has asked for the wisdom of the Perl Monks concerning the following question:

I'm trying to setup Log4perl to optionally log into a SQLite database. With the following code: 

use strict;
use warnings;

use Log::Log4perl;

# initialize logging
Log::Log4perl->init('log4perl.conf');
my $log = Log::Log4perl->get_logger();

# perform some logging including MDC
$log->info("Logging initialized");
Log::Log4perl::MDC->put("MDC_key", "my MDC value");
$log->info("MDC used");

[download]
and this Log4perl configuration: 
log4perl.rootLogger = TRACE, app_screen, app_db

# configuration for screen appender
log4perl.appender.app_screen = Log::Log4perl::Appender::Screen
log4perl.appender.app_screen.layout = Log::Log4perl::Layout::PatternLa
+yout
log4perl.appender.app_screen.layout.ConversionPattern = [%p] %m{indent
+}%n

# configuration for database logging
log4perl.appender.app_db = Log::Log4perl::Appender::DBI
log4perl.appender.app_db.datasource = dbi:SQLite:uri=file:log4perl.sql
+ite
log4perl.appender.app_db.sql = \
insert into log \
(priority, my_key, message) \
values (?, ?, ? ) \

log4perl.appender.app_db.params.1 = %p
log4perl.appender.app_db.params.2 = %X{MDC_key}
log4perl.appender.app_db.usePreparedStm = 1
log4perl.appender.app_db.warp_message = 0
log4perl.appender.app_db.attrs.f_encoding = utf8
log4perl.appender.app_db.layout =  Log::Log4perl::Layout::NoopLayout

[download]
this works fine as long as the SQLite database has been setup beforehand. Namely the log table needs to be setup correctly.

If the log4perl.sqlite file is not present when the above code is run an empty SQLite file is generated by DBD::SQLite during Log4perl initialization (as expected). But when the first logging statement is executed the perl code certainly errors out with 

Log4perl: DBI->prepare failed no such table: log
insert into log (priority, my_key, message) values (?, ?, ? ) at C:\Us
+ers\bloeckm\Perl\DB_logging\log4perl_dbi_demo.pl line 13.

[download]
as the log table has not been set up in the SQLite database.

My concern is that the SQLite file might (accidentally or intentionally) be deleted by the user or not be present in the first place. I was wondering if there was any way to implement/configure Log4perl in a "self-contained" way so that the SQLite file will be created properly if it does not (yet) exist. I have been trying to wrap my head around this but have not found a way to put the required CREATE TABLE IF NOT EXISTS SQL statements into the Log4perl configuration file. Is there any way to do that?

Or is there any way to programmatically detect that a DBI::SQLite appender has been configured and get the associated DB handle? With the respective DB handle it should be possible to achieve what I want using CREATE TABLE IF NOT EXISTS.

Replies are listed 'Best First'.
Re: Log4perl: Create missing SQLite file and log to it
by thanos1983 (Parson) on May 09, 2019 at 13:29 UTC

    Hello mbloecker,

    Welcome to the Monastery. If you are running SQLite above 2012-03-20 (3.7.11) you can use the exact condition you mentioned :). SQLite/Release History

    create table if not exists TableName (col1 typ1, ..., colN typN)

    [download]

    Alternatively there is a similar question on the forum SQLite Table Existence?. There a few other ways proposed :)

    Hope this helps, BR

    Seeking for Perl wisdom...on the process of learning...not there...yet!
[reply]
[d/l]
[select]
      Yes, querying sqlite_master is another way. I used this formulation last time I did this, probably an even better formulation is possible: 
      my $check_if_bracket_table = $dbh->prepare("SELECT name 
                                            FROM sqlite_master
                                            WHERE 
                                              type = 'table' AND
                                              name = 'Brackets'");
$check_if_bracket_table->execute();
my $ref = $check_if_bracket_table -> fetchall_arrayref;

if (@$ref >0){...}   # the Bracket Table exists...

      [download]
      Update: the sqlite_master table contains a lot of meta information about the DB. A lot more can be discovered than just whether a table exists or not. I am not an SQL guru, this is something I did last year to solve a problem. I had to do some stuff with the Brackets table, if it existed. Otherwise there were some rules about how to deal with situation.
[reply]
[d/l]

      Thanks a lot for the kind reply and for welcoming me to the Perl Monastery.

      I apologize if I may not have been clear on my question: I already knew that CREATE TABLE IF NOT EXISTS does work using DBI. My question was about how to implement this in the log4perl configuration file so that it will be executed when log4perl does create the SQLite database file. There does not seem to be a standard handle to do so...

[reply]
[d/l]

        My Log4perl chops are extremely stale at this point. My question would be, is it properly the logger’s responsibility? Log4perl can send email and such too but you would never expect it to configure/start sendmail as a part of normal “just in time” operations. I think the application consuming the logger should be responsible for setting up the DB. That said, this might do it. See also: Log::Log4perl::FAQ. Completely untested–

        log4perl.appender.app_db.datasource = sub { \
   require DBI; \
   my $connect_info = "dbi:SQLite:uri=file:log4perl.sqlite"; \
   my $dbh = DBI->connect($connect_info); \
   # ERROR handling, possible DB    creation left out! \
   $dbh->do(<<"_SQL_"); \
       CREATE TABLE IF NOT EXISTS log (    \
             # ... table definition    .... \
       ); \
_SQL_ \
   $connect_info; \
}

        [download]
[reply]
[d/l]

Back to Seekers of Perl Wisdom