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

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]

Comment on Re^3: Log4perl: Create missing SQLite file and log to it Download Code

Replies are listed 'Best First'.
Re^4: Log4perl: Create missing SQLite file and log to it by mbloecker (Novice) on May 10, 2019 at 09:23 UTC
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 [download] 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.	[reply] [d/l] [select]

Replies are listed 'Best First'.

Re^4: Log4perl: Create missing SQLite file and log to it
by mbloecker (Novice) on May 10, 2019 at 09:23 UTC

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
[download]

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.

[reply]
[d/l]
[select]