The log4net ADONetAppender does not define the database layout. Currently the user is required to design their own table structure and then define a stored procedure or prepared statement to write into the database. A layout is used to render each parameter to the stored procedure or prepared statement. Using a stored procedure would allow the user to store the MDC & property values in separate tables, however this would be a complex stored procedure.
Example configurations are here: http://logging.apache.org/log4net/release/config-examples.html#HC-11150143
The appender allows specific MDC or Properties values to be extracted into custom columns, or combined together to make up a single column.
Combining the MDC values into a single column (or Perties into a single column) renders the MDC to become opaque to the RDBMS. Thus, the RDBMS cannot perform queries efficiently when asked to select events pertaining to a given user assumed to be a value stored in the MDC. Expanding the MDC into its own table makes efficient queries possible.
Are you proposing that the DBAppender would only work with a fixed database schema?
Absolutely. Although we must allow for variation in table names, but not column names, so that multiple logging event targets can be set up by the user.
I suppose that you have already thought about how this should work, so you may have thought/discounted/done any of the following:
Rather than having a separate join table for MDC and Properties (and any other key/value fields) you can combine these into a single table with a context column:
CREATE TABLE [dbo].[LogEventValues] ( [EventId] [int] NOT NULL, [Context] [int] NOT NULL, [Key] [varchar] (255) NOT NULL, [Value] [varchar] (4000) NULL )
Where EventId is a foreign key into the LogEvent table, Context looks up into a table with values for MDC and Properties. I think that the MDC is actually "Thread Context" and Properties is actually "Event Context". This means that if we want to add another context, for example properties tied to a specific LoggerRepository or global properties, a database schema change is not required.
I thought about this solution as well. It makes sense. Given that no new context(s) are planned in the foreseeable future, I have a small preference for the simple and stupidly obvious way.
The records of a stack trace need to be ordered and therefore have a 'key' which is used for sorting. Therefore it would also be possible to use the same table above to store the stack trace records for the event, the Context would lookup to stack trace, the Key would be an alpha string to sort on e.g. A, B, C etc... and the value would be the info for the stack frame.
Having a fixed database schema will allow tools to be written to interrogate the logged events. Currently I use the SQL Query Analyser to inspect the logs, but others may prefer a dedicated GUI tool.
There may be scope for having two appenders or at least being able to configure the appender to either write to the 'standard' table layout or to use a user specified table.
I would like to get the simple case right before allowing for user variation.
The user will have to be able to configure the names of the tables to write into and probably also the names of the columns. Different users have different requirements for the naming of database tables and columns.
One completely different alternative is to write the event into the database as XML (XmlLayout). Many databases have a native XML data type and can perform xpath style queries on the contents of the field. XML would allow different log4x platforms (or other user programmes) to add additional fields to the data using namespaces to demark the values.
A friend of mine who designed an object oriented database mentioned this possibility. Well, ...
Nicko
-- Ceki G�lc�
For log4j documentation consider "The complete log4j manual"
ISBN: 2970036908 http://www.qos.ch/shop/products/clm_t.jsp
