/*
 * Copyright (C) The Apache Software Foundation. All rights reserved.
*/

package com.klopotek.utils.log;

import java.sql.*;
import java.util.*;
import org.apache.log4j.*;
import org.apache.log4j.helpers.*;
import org.apache.log4j.spi.*;

/**
The JDBCAppender, writes messages into a database

The JDBCAppender is configurable at runtime by setting options in two alternatives : 

	
1. Use a configuration-file
	
Define the options in a file (example) and call a PropertyConfigurator.configure(filename) in your code.
	
2. Use the methods of JDBCAppender to do it
	
Call JDBCAppender::setOption(JDBCAppender.xxx_OPTION, String value) to do it analogically without a configuration-file (example)


All available options are defined as static String-constants in JDBCAppender named xxx_OPTION.

Here is a description of all available options :

	
1. Database-options to connect to the database
	
- URL_OPTION			: a database url of the form jdbc:subprotocol:subname
	
- USERNAME_OPTION	: the database user on whose behalf the connection is being made
	
- PASSWORD_OPTION	: the user's password

	
2. Connector-option to specify your own JDBCConnectionHandler
	
- CONNECTOR_OPTION	: a classname which is implementing the JDBCConnectionHandler-interface
	
This interface is used to get a customized connection.
	
If in addition the database-options are given, these options will be used as arguments for the JDBCConnectionHandler-interface to get a connection.
	
Else if no database-options are given, the JDBCConnectionHandler-interface is called without them.
	
Else if this option is not defined, the database-options are required to open a connection by the JDBCAppender.

	
3. SQL-option to specify a static sql-statement which will be performed with every occuring message-event
	
- SQL_OPTION			: a sql-statement which will be used to write to the database
	
Use the variable @MSG@ on a location in the statement, which has to be dynamically replaced by the message-text.
	
If you give this option, the table-option and columns-option will be ignored !

	
4. Table-option to specify a table contained by the database
	
- TABLE_OPTION		: the table in which the logging will be done

	
5. Columns-option to describe the important columns of the table (Not nullable columns are mandatory to describe!)
	
- COLUMNS_OPTION		: a formatted list of column-descriptions
	
Each column description consists of
	
		
- the name of the column (required)
		
- a logtype which is a static constant of class LogType (required)
		
- and a value which depends by the LogType (optional/required, depending by logtype)
	
	
Here is a description of the available logtypes of class {@link LogType} and how to handle the value:
	
		
o MSG			= a value will be ignored, the column will get the message. (One columns need to be of this type!)
		
o STATIC		= the value will be filled into the column with every logged message. (Ensure that the type of value can be casted into the sql-type of the column!)
		
o ID			= value must be a classname, which implements the JDBCIDHandler-interface.
		
o TIMESTAMP	= a value will be ignored, the column will be filled with a actually timestamp with every logged message.
		
o EMPTY		= a value will be ignored, the column will be ignored when writing to the database (Ensure to fill not nullable columns by a database trigger!)
	
	
If there are more than one column to describe, the columns must be separated by a Tabulator-delimiter (unicode0008) !
	
The arguments of a column-description must be separated by the delimiter '~' !
	
(Example :  name1~logtype1~value1   name2~logtype2~value2...)

	
6. Layout-options to define the layout of the messages (optional)
	
- _ : the layout wont be set by a xxx_OPTION
	
See the configuration-file and code examples below...
	
The default is a layout of the class {@link org.apache.log4j.PatternLayout} with the pattern=%m which representate only the message.

	
7. Buffer-option to define the size of the message-event-buffer (optional)
	
- BUFFER_OPTION		: define how many messages will be buffered until they will be updated to the database.
	
The default is buffer=1, which will do a update with every happened message-event.

	
8. Commit-option to define a auto-commitment
	
- COMMIT_OPTION		: define whether updated messages should be committed to the database (Y) or not (N).
	
The default is commit=Y.


The sequence of some options is important :

	
1. Connector-option OR/AND Database-options
	
Any database connection is required !
	
2. (Table-option AND Columns-option) OR SQL-option
	
Anything of that is required ! Whether where to write something OR what to write somewhere...;-)
	
3. All other options can be set at any time...
	
The other options are optional and have a default initialization, which can be customized.


Here is a configuration-file example, which can be used as argument for the PropertyConfigurator :  configfile_example.txt

Here is a code-example to configure the JDBCAppender with a configuration-file :  code_example1.java

Here is a another code-example to configure the JDBCAppender without a configuration-file :  code_example2.java



Author : Thomas Fenner

@since 1.0
*/
public class JDBCAppender extends AppenderSkeleton
{
	/**
	A database-option to to set a database url of the form jdbc:subprotocol:subname.
	*/
	public static final String URL_OPTION			= "url";

	/**
	A database-option to set the database user on whose behalf the connection is being made.
	*/
	public static final String USERNAME_OPTION	= "username";

	/**
	A database-option to set the user's password.
	*/
	public static final String PASSWORD_OPTION	= "password";

	/**
	A table-option to specify a table contained by the database
	*/
	public static final String TABLE_OPTION		= "table";

	/**
	A connector-option to specify your own JDBCConnectionHandler
	*/
	public static final String CONNECTOR_OPTION	= "connector";

	/**
   A columns-option to describe the important columns of the table
	*/
	public static final String COLUMNS_OPTION		= "columns";

	/**
	A sql-option to specify a static sql-statement which will be performed with every occuring message-event
   */
	public static final String SQL_OPTION			= "sql";

	/**
   A buffer-option to define the size of the message-event-buffer
	*/
	public static final String BUFFER_OPTION		= "buffer";

	/**
   A commit-option to define a auto-commitment
	*/
	public static final String COMMIT_OPTION		= "commit";


	//Variables to store the options values setted by setOption() :
	private String url		= null;
	private String username	= null;
	private String password	= null;
	private String table		= null;
	private String connection_class = null;
	private String sql		= null;
	private boolean docommit = true;
	private int buffer_size	= 1;
   private JDBCConnectionHandler connectionHandler = null;

	//This buffer stores message-events.
   //When the buffer_size is reached, the buffer will be flushed and the messages will updated to the database.
	private ArrayList buffer = new ArrayList();

   //Database-connection
	private Connection con = null;

	//This class encapsulate the logic which is necessary to log into a table
	private JDBCLogger jlogger = new JDBCLogger();

   //Flags :
   //A flag to indicate a established database connection
	private boolean connected = false;
   //A flag to indicate configuration status
	private boolean configured = false;
   //A flag to indicate that everything is ready to get append()-commands.
	private boolean ready = false;

	/**
	If program terminates close the database-connection and flush the buffer
   */
	public void finalize()
	{
		close();
      super.finalize();
	}

	/**
	Internal method. Returns a array of strings containing the available options which can be set with method setOption()
	*/
	public String[] getOptionStrings()
   {
   	// The sequence of options in this string is important, because setOption() is called this way ...
		return new String[]{CONNECTOR_OPTION, URL_OPTION, USERNAME_OPTION, PASSWORD_OPTION, SQL_OPTION, TABLE_OPTION, COLUMNS_OPTION, BUFFER_OPTION, COMMIT_OPTION};
	}


	/**
     Sets all necessary options
	*/
	public void setOption(String _option, String _value)
	{
   	_option = _option.trim();
      _value = _value.trim();

		if(_option == null || _value == null) return;
		if(_option.length() == 0 || _value.length() == 0) return;

      _value = _value.trim();

		if(_option.equals(CONNECTOR_OPTION))
      {
      	if(!connected) connection_class = _value;
      }
		else if(_option.equals(URL_OPTION))
		{
			if(!connected) url = _value;
		}
		else if(_option.equals(USERNAME_OPTION))
		{
			if(!connected) username = _value;
		}
		else if(_option.equals(PASSWORD_OPTION))
		{
			if(!connected) password = _value;
		}
		else if(_option.equals(SQL_OPTION))
      {
			sql = _value;
      }
		else if(_option.equals(TABLE_OPTION))
      {
      	if(sql != null) return;
      	table = _value;
      }
		else if(_option.equals(COLUMNS_OPTION))
      {
      	if(sql != null) return;

			String name = null;
         int logtype = -1;
         String value = null;
         String column = null;
         String arg = null;
         int num_args = 0;
         int num_columns = 0;
			StringTokenizer st_col;
			StringTokenizer st_arg;

         //Columns are TAB-separated
			st_col = new StringTokenizer(_value,  "	");

			num_columns = st_col.countTokens();

         if(num_columns < 1)
  	      {
     	   	errorHandler.error("JDBCAppender::setOption(), Invalid COLUMN_OPTION value : " + _value + " !");
            return;
        	}

         for(int i=1; i<=num_columns; i++)
         {
				column = st_col.nextToken();

            //Arguments are ~-separated
				st_arg = new StringTokenizer(column, "~");

				num_args = st_arg.countTokens();

	         if(num_args < 2)
   	      {
      	   	errorHandler.error("JDBCAppender::setOption(), Invalid COLUMN_OPTION value : " + _value + " !");
               return;
         	}

	         for(int j=1; j<=num_args; j++)
   	      {
					arg = st_arg.nextToken();

					if(j == 1) name = arg;
					else if(j == 2)
      	      {
         	   	try
            	   {
							logtype = Integer.parseInt(arg);
	               }
   	            catch(Exception e)
      	         {
         	      	logtype = LogType.parseLogType(arg);
	               }

						if(!LogType.isLogType(logtype))
   	            {
	   	            errorHandler.error("JDBCAppender::setOption(), Invalid COLUMN_OPTION LogType : " + arg + " !");
                     return;
         	      }
            	}
					else if(j == 3) value = arg;
   	      }

	         if(!setLogType(name, logtype, value)) return;
         }
      }
		else if(_option.equals(BUFFER_OPTION))
      {
        	try
         {
				buffer_size = Integer.parseInt(_value);
         }
         catch(Exception e)
         {
	         errorHandler.error("JDBCAppender::setOption(), Invalid BUFFER_OPTION value : " + _value + " !");
				return;
         }
      }
		else if(_option.equals(COMMIT_OPTION))
      {
      	docommit = _value.equals("Y");
      }

      if(_option.equals(SQL_OPTION) || _option.equals(TABLE_OPTION))
      {
			if(!configured) configure();
      }
	}

	/**
	Internal method. Returns true, you may define your own layout...
	*/
	public boolean requiresLayout()
	{
		return true;
	}


	/**
	Internal method. Close the database connection & flush the buffer.
	*/
	public void close()
	{
	   flush_buffer();
      if(connection_class == null)
      {
			try{con.close();}catch(Exception e){errorHandler.error("JDBCAppender::close(), " + e);}
      }
		this.closed = true;
	}


	/**
	You have to call this function for all provided columns of your log-table !
   */
	public boolean setLogType(String _name, int _logtype, Object _value)
	{
   	if(sql != null) return true;

		if(!configured)
		{
			if(!configure()) return false;
		}

		try
		{
			jlogger.setLogType(_name, _logtype, _value);
		}
		catch(Exception e)
		{
			errorHandler.error("JDBCAppender::setLogType(), " + e);
			return false;
		}

		return true;
	}


	/**
	Internal method. Appends the message to the database table.
	*/
	public void append(LoggingEvent event)
	{
		if(!ready)
      {
      	if(!ready())
         {
				errorHandler.error("JDBCAppender::append(), Not ready to append !");
         	return;
			}
      }

		buffer.add(event);

		if(buffer.size() >= buffer_size) flush_buffer();
	}


	/**
	Internal method. Flushes the buffer.
	*/
   public void flush_buffer()
   {
   	try
      {
      	int size = buffer.size();

         if(size < 1) return;

        	for(int i=0; i