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.

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.