You are viewing a plain text version of this content. The canonical link for it is here.
Posted to log4j-dev@logging.apache.org by ce...@apache.org on 2004/05/26 17:36:18 UTC
cvs commit: logging-log4j/src/java/org/apache/log4j/db/dialect db2l.sql
ceki 2004/05/26 08:36:18
Modified: src/java/org/apache/log4j/db ConnectionSource.java
DBAppender.java
Added: src/java/org/apache/log4j/db package.html DBAppender2.java
src/java/org/apache/log4j/db/dialect db2l.sql
Log:
- Added db2 sql script for table creation.
- Improved docs.
- DBAppender2 uses batched updates using the getGeneratedKeys method. However, this does
not work correctly on PostgreSQL nor MySQL.
Revision Changes Path
1.7 +2 -5 logging-log4j/src/java/org/apache/log4j/db/ConnectionSource.java
Index: ConnectionSource.java
===================================================================
RCS file: /home/cvs/logging-log4j/src/java/org/apache/log4j/db/ConnectionSource.java,v
retrieving revision 1.6
retrieving revision 1.7
diff -u -r1.6 -r1.7
--- ConnectionSource.java 24 May 2004 16:14:34 -0000 1.6
+++ ConnectionSource.java 26 May 2004 15:36:18 -0000 1.7
@@ -56,18 +56,15 @@
void setErrorHandler(ErrorHandler errorHandler);
/**
- *
* Get the SQL dialect that should be used for this connection. Note that the
- * dialect is not needed if the DBC 3.0 getGeneratedKeys is supported.
- *
- *
+ * dialect is not needed if the JDBC driver supports the getGeneratedKeys
+ * method.
*/
int getSQLDialectCode();
/**
* If the connection supports the JDBC 3.0 getGeneratedKeys method, then
* we do not need any specific dialect support.
- *
*/
boolean supportsGetGeneratedKeys();
1.13 +52 -10 logging-log4j/src/java/org/apache/log4j/db/DBAppender.java
Index: DBAppender.java
===================================================================
RCS file: /home/cvs/logging-log4j/src/java/org/apache/log4j/db/DBAppender.java,v
retrieving revision 1.12
retrieving revision 1.13
diff -u -r1.12 -r1.13
--- DBAppender.java 24 May 2004 16:14:34 -0000 1.12
+++ DBAppender.java 26 May 2004 15:36:18 -0000 1.13
@@ -34,24 +34,66 @@
/**
- * The DBAppender writes events to a database table. You first need to
- * create the database tables with the help of SQL scripts found in the
- * org/apache/log4j/db/dialect/ directory. There exist scripts for the most
- * popular database systems. If it is missing, you should be able to write
- * the appropriate script quite quickly.
+ * The DBAppender inserts loggin events into three database tables in a format
+ * independent of the Java programming language. The three tables that DBAppender
+ * inserts to must exists before DBAppender can be used. These tables may be
+ * created with the help of SQL scripts found in the
+ * <em>src/java/org/apache/log4j/db/dialect</em> directory. There is a specific
+ * script for each of the most popular database systems. If the script for your
+ * particular type of database system is missing, it should be quite easy to
+ * write one, taking example on the already existing scripts. If you send them
+ * to us, we will gladly include missing scripts in future releases.
*
- * <p>If the JDBC driver you are using supports the getGeneratedKeys method
- * introduced in JDBC 3.0 specification, then you are all set. Otherwise,
+ * <p>If the JDBC driver you are using supports the {@link java.sql.Statement#getGeneratedKeys}
+ * method introduced in JDBC 3.0 specification, then you are all set. Otherwise,
* there must be an {@link SQLDialect} appropriate for your database system.
* Currently, we have dialects for PostgreSQL, MySQL, Oracle and MsSQL. As
- * mentioed previously, an SQLDialect is required only the JDBC driver for your
- * database system does not support the getGeneratedKeys method.
+ * mentioed previously, an SQLDialect is required only if the JDBC driver for
+ * your database system does not support the {@link java.sql.Statement#getGeneratedKeys getGeneratedKeys} method.
* </p>
*
- * <p><b>Performance</b> Experiments show that writing a single event into the
+ * <table border="1" cellpadding="4">
+ * <tr><th>RDBMS</th>
+ * <th>supports<br/><code>getGeneratedKeys()</code> method</th>
+ * <th>specific<br/>SQLDialect support</th>
+ * <tr>
+ * <tr>
+ * <td>PostgreSQL</td>
+ * <td align="center">NO</td>
+ * <td> present and used </td>
+ * <tr>
+ * <tr>
+ * <td>MySQL</td>
+ * <td align="center">YES</td>
+ * <td> present, but not actually needed or used</td>
+ * <tr>
+ * <tr>
+ * <td>Oracle</td>
+ * <td align="center">YES</td>
+ * <td> present, but not actually needed or used</td>
+ * <tr>
+ * <tr>
+ * <td>DB2</td>
+ * <td align="center">YES</td>
+ * <td> not present, and not needed or used</td>
+ * <tr>
+ * <tr>
+ * <td>MsSQL</td>
+ * <td align="center">YES</td>
+ * <td> not present, and not needed or used</td>
+ * <tr>
+ * </table>
+ * <p><b>Performance:</b> Experiments show that writing a single event into the
* database takes approximately 50 milliseconds, on a "standard" PC. If pooled
* connections are used, this figure drops to under 10 milliseconds. Note that
* most JDBC drivers already ship with connection pooling support.
+ * </p>
+ *
+ *
+ *
+ * <p><b>Configuration</b> DBAppender can be configured programmatically, or
+ * using {@link org.apache.log4j.joran.JoranConfigurator JoranConfigurator}.
+ * Example scripts can be found in the <em>tests/input/db</em> directory.
*
* @author Ceki Gülcü
* @author Ray DeCampo
1.1 logging-log4j/src/java/org/apache/log4j/db/package.html
Index: package.html
===================================================================
<html>
<body>
<p>The org.apache.log4j.db package provides means to append logging events
into various databases. The persisted data can be later read back using
{@link DBReceiver}.
</p>
<p>Most popular database systems, such as PostgreSQL, MySQL, Oracle, DB2 and MsSQL
are supported.
</p>
<p>Just as importantly, the way for obtaining JDBC connections is pluggable. Connections can
be obtained through the tradinal way of DriverManager, or alternatively as a DataSource.
A DataSource can be instantiated directly or it can obtained through JNDI.
</p>
</body>
</html>
1.1 logging-log4j/src/java/org/apache/log4j/db/DBAppender2.java
Index: DBAppender2.java
===================================================================
/*
* Copyright 1999,2004 The Apache Software Foundation.
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package org.apache.log4j.db;
import org.apache.log4j.AppenderSkeleton;
import org.apache.log4j.db.dialect.SQLDialect;
import org.apache.log4j.db.dialect.Util;
import org.apache.log4j.helpers.LogLog;
import org.apache.log4j.spi.LocationInfo;
import org.apache.log4j.spi.LoggingEvent;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Iterator;
import java.util.Set;
import java.util.Vector;
/**
*
* @author Ceki Gülcü
*
*/
public class DBAppender2 extends AppenderSkeleton {
static final String insertPropertiesSQL =
"INSERT INTO logging_event_property (event_id, mapped_key, mapped_value) VALUES (?, ?, ?)";
static final String insertExceptionSQL =
"INSERT INTO logging_event_exception (event_id, i, trace_line) VALUES (?, ?, ?)";
static final String insertSQL;
static {
StringBuffer sql = new StringBuffer();
sql.append("INSERT INTO logging_event (");
sql.append("sequence_number, ");
sql.append("timestamp, ");
sql.append("rendered_message, ");
sql.append("logger_name, ");
sql.append("level_string, ");
sql.append("ndc, ");
sql.append("thread_name, ");
sql.append("reference_flag, ");
sql.append("caller_filename, ");
sql.append("caller_class, ");
sql.append("caller_method, ");
sql.append("caller_line) ");
sql.append(" VALUES (?, ?, ? ,?, ?, ?, ?, ?, ?, ?, ?, ?)");
insertSQL = sql.toString();
}
int batchSize = 20;
ConnectionSource connectionSource;
SQLDialect sqlDialect;
boolean locationInfo = false;
Vector eventsBuffer = new Vector();
int[] eventIDArray;
public DBAppender2() {
}
public void activateOptions() {
LogLog.debug("DBAppender.activateOptions called");
if (connectionSource == null) {
throw new IllegalStateException(
"DBAppender cannot function without a connection source");
}
if (connectionSource.supportsGetGeneratedKeys()) {
LogLog.info(
"****JDBC getGeneratedKeys method is supported. Will use batch mode.");
} else {
// if getGeneratedKeys method is not supported, we fallback to batch size
// of one.
batchSize = 1;
Util.getDialectFromCode(connectionSource.getSQLDialectCode());
if (sqlDialect == null) {
throw new IllegalStateException(
"DBAppender cannot function without a determined SQL dialect");
}
}
eventIDArray = new int[batchSize];
}
/**
* @return Returns the connectionSource.
*/
public ConnectionSource getConnectionSource() {
return connectionSource;
}
/**
* @param connectionSource The connectionSource to set.
*/
public void setConnectionSource(ConnectionSource connectionSource) {
LogLog.debug("setConnectionSource called for DBAppender");
this.connectionSource = connectionSource;
}
protected void append(LoggingEvent event) {
event.prepareForSerialization();
eventsBuffer.add(event);
LogLog.info("=========Buffer size is "+eventsBuffer.size());
if (eventsBuffer.size() >= batchSize) {
flush();
}
}
void flush() {
LogLog.info("=============flush called");
Connection connection = null;
LogLog.info("Buffer size is "+eventsBuffer.size());
try {
connection = connectionSource.getConnection();
connection.setAutoCommit(false);
PreparedStatement insertStatement =
connection.prepareStatement(DBAppender2.insertSQL, Statement.RETURN_GENERATED_KEYS);
for (int i = 0; i < eventsBuffer.size(); i++) {
LoggingEvent event = (LoggingEvent) eventsBuffer.get(i);
LogLog.info("*** event message is "+event.getRenderedMessage());
insertStatement.setLong(1, event.getSequenceNumber());
insertStatement.setLong(2, event.getTimeStamp());
insertStatement.setString(3, event.getRenderedMessage());
insertStatement.setString(4, event.getLoggerName());
insertStatement.setString(5, event.getLevel().toString());
insertStatement.setString(6, event.getNDC());
insertStatement.setString(7, event.getThreadName());
insertStatement.setShort(8, DBHelper.computeReferenceMask(event));
LocationInfo li;
if (event.locationInformationExists() || locationInfo) {
li = event.getLocationInformation();
} else {
li = LocationInfo.NA_LOCATION_INFO;
}
insertStatement.setString(9, li.getFileName());
insertStatement.setString(10, li.getClassName());
insertStatement.setString(11, li.getMethodName());
insertStatement.setString(12, li.getLineNumber());
LogLog.info("*** performing insertion.");
insertStatement.addBatch();
}
int[] r = insertStatement.executeBatch();
if(r != null) {
for(int x = 0; x < r.length; x++) {
LogLog.info("inserted "+r[x]);
}
}
connection.commit();
fillEventIDArray(connection, insertStatement);
//insertProperties(connection, eventsBuffer, eventIDArray);
//insertAllExceptions(connection, eventsBuffer, eventIDArray);
// remove the events
connection.commit();
DBHelper.closeStatement(insertStatement);
//insertStatement = null;
eventsBuffer.setSize(0);
} catch (SQLException sqle) {
LogLog.error("problem appending event", sqle);
} finally {
DBHelper.closeConnection(connection);
}
}
void fillEventIDArray(Connection connection, Statement insertStatement)
throws SQLException {
//if (connectionSource.supportsGetGeneratedKeys()) {
if(true) {
ResultSet keyRS = insertStatement.getGeneratedKeys();
ResultSetMetaData rsmd = keyRS.getMetaData();
LogLog.info(" numberOfColumns = "+rsmd.getColumnCount());
int i = 0;
while (keyRS.next()) {
int id = keyRS.getInt(1);
LogLog.info("**filling id ="+id);
eventIDArray[i++] = id;;
}
if (i != eventsBuffer.size()) {
LogLog.error("Number of inserted objects do not match.");
// TODO CG better errorHandling code here
// no point in continuing
LogLog.error("number of found events: "+i+" bufferSize="+eventsBuffer.size());
//throw new IllegalStateException("Programming error. Number of inserted objects do not match.");
}
} else {
Statement idStatement = connection.createStatement();
idStatement.setMaxRows(1);
ResultSet rs = idStatement.executeQuery(sqlDialect.getSelectInsertId());
rs.first();
eventIDArray[0] = rs.getInt(1);
}
}
void insertProperties(
Connection connection, Vector eventsVector, int[] eventIDArray)
throws SQLException {
PreparedStatement insertPropertiesStatement = null;
try {
insertPropertiesStatement =
connection.prepareStatement(DBAppender2.insertPropertiesSQL);
for (int i = 0; i < eventsBuffer.size(); i++) {
LoggingEvent event = (LoggingEvent) eventsBuffer.get(i);
Set propertiesKeys = event.getPropertyKeySet();
if (propertiesKeys.size() > 0) {
for (Iterator iterator = propertiesKeys.iterator();
iterator.hasNext();) {
String key = (String) iterator.next();
String value = (String) event.getProperty(key);
LogLog.info(
"id " + eventIDArray[i] + ", key " + key + ", value " + value);
insertPropertiesStatement.setInt(1, eventIDArray[i]);
insertPropertiesStatement.setString(2, key);
insertPropertiesStatement.setString(3, value);
insertPropertiesStatement.addBatch();
}
}
}
// now that all properties for all events have been inserted, we can
// execute insertPropertiesStatement
insertPropertiesStatement.executeBatch();
} finally {
DBHelper.closeStatement(insertPropertiesStatement);
}
}
void insertAllExceptions(
Connection connection, Vector eventsVector, int[] eventIDArray)
throws SQLException {
PreparedStatement insertExceptionStatement = null;
try {
insertExceptionStatement =
connection.prepareStatement(DBAppender2.insertExceptionSQL);
for (int i = 0; i < eventsBuffer.size(); i++) {
LoggingEvent event = (LoggingEvent) eventsBuffer.get(i);
String[] strRep = event.getThrowableStrRep();
if (strRep != null) {
LogLog.info("Logging an exception");
for (short k = 0; k < strRep.length; k++) {
insertExceptionStatement.setInt(1, eventIDArray[i]);
insertExceptionStatement.setShort(2, k);
insertExceptionStatement.setString(3, strRep[k]);
insertExceptionStatement.addBatch();
}
}
}
insertExceptionStatement.executeBatch();
} finally {
DBHelper.closeStatement(insertExceptionStatement);
}
}
public void close() {
if (closed) {
} else {
closed = true;
flush();
}
}
/*
* The DBAppender does not require a layout.
*/
public boolean requiresLayout() {
return false;
}
/**
* Returns value of the <b>LocationInfo</b> property which determines whether
* caller's location info is written to the database.
* */
public boolean getLocationInfo() {
return locationInfo;
}
/**
* If true, the information written to the database will include
* caller's location information. Due to performance concerns, by default no
* location information is written to the database.
* */
public void setLocationInfo(boolean locationInfo) {
this.locationInfo = locationInfo;
}
}
1.1 logging-log4j/src/java/org/apache/log4j/db/dialect/db2l.sql
Index: db2l.sql
===================================================================
# This SQL script creates the required tables by org.apache.log4j.db.DBAppender and
# org.apache.log4j.db.DBReceiver.
#
# It is intended for PostgreSQL databases.
DROP TABLE logging_event_property;
DROP TABLE logging_event_exception;
DROP TABLE logging_event;
CREATE SEQUENCE logging_event_id_seq MINVALUE 1 START 1;
CREATE TABLE logging_event
(
sequence_number BIGINT NOT NULL,
timestamp BIGINT NOT NULL,
rendered_message TEXT NOT NULL,
logger_name VARCHAR(254) NOT NULL,
level_string VARCHAR(254) NOT NULL,
ndc TEXT,
thread_name VARCHAR(254),
reference_flag SMALLINT,
caller_filename VARCHAR(254) NOT NULL,
caller_class VARCHAR(254) NOT NULL,
caller_method VARCHAR(254) NOT NULL,
caller_line CHAR(4) NOT NULL,
event_id INT IDENTITY GENERATED ALWAYS PRIMARY KEY
);
CREATE TABLE logging_event_property
(
event_id INT NOT NULL,
mapped_key VARCHAR(254) NOT NULL,
mapped_value VARCHAR(1024),
PRIMARY KEY(event_id, mapped_key),
FOREIGN KEY (event_id) REFERENCES logging_event(event_id)
);
CREATE TABLE logging_event_exception
(
event_id INT NOT NULL,
i SMALLINT NOT NULL,
trace_line VARCHAR(254) NOT NULL,
PRIMARY KEY(event_id, i),
FOREIGN KEY (event_id) REFERENCES logging_event(event_id)
);
---------------------------------------------------------------------
To unsubscribe, e-mail: log4j-dev-unsubscribe@logging.apache.org
For additional commands, e-mail: log4j-dev-help@logging.apache.org