Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked

Re: Log4perl: Create missing SQLite file and log to it

by thanos1983 (Parson)
on May 09, 2019 at 13:29 UTC ( #1233504=note: print w/replies, xml ) Need Help??

in reply to Log4perl: Create missing SQLite file and log to it

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)

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!

Replies are listed 'Best First'.
Re^2: Log4perl: Create missing SQLite file and log to it
by Marshall (Canon) on May 10, 2019 at 03:20 UTC
    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...
    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.
Re^2: Log4perl: Create missing SQLite file and log to it
by mbloecker (Novice) on May 09, 2019 at 15:35 UTC

    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...

      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; \ }

        Thanks a lot for pointing out the usage of sub{...} in this context. This does the trick! I needed to do some minor tweaking of your code though as both the inline document <<"_SQL_" and the comment line were causing problems. As a reference for others that might read/find this conversation here's the complete log4perl.conf file that did work (again, no error checking implemented!):

        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 = sub { \ require DBI; \ my $connect_info = "dbi:SQLite:uri=file:log4perl.sqlite"; \ my $dbh = DBI->connect($connect_info); \ $dbh->do(' \ CREATE TABLE IF NOT EXISTS log ( \ priority, \ message, \ my_key \ )' \ ); \ $connect_info; \ } 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

        You are certainly right that in general the database should be set up beforehand and for server-type DB engines this probably can be assumed to be the case. My use-case is to retrofit some existing applications with DB logging and SQLite is the simplest choice for this scenario. Thus putting all the DB setup stuff into the log4perl configuration is the simplest way for a self-contained log4perl setup without any change to the actual application codebase.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1233504]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (4)
As of 2021-04-10 22:10 GMT
Find Nodes?
    Voting Booth?

    No recent polls found