sqldescription.java

java开源的企业总线.xmlBlaster

/*------------------------------------------------------------------------------
Name:      SqlDescription.java
Project:   xmlBlaster.org
Copyright: xmlBlaster.org, see xmlBlaster-LICENSE file
------------------------------------------------------------------------------*/

package org.xmlBlaster.contrib.dbwriter.info;


import java.io.BufferedReader;
import java.io.ByteArrayInputStream;
import java.io.FileInputStream;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.Set;
import java.util.TreeMap;
import java.util.logging.Logger;

import org.xmlBlaster.contrib.I_Info;
import org.xmlBlaster.contrib.PropertiesInfo;
import org.xmlBlaster.contrib.dbwriter.SqlInfoParser;
import org.xmlBlaster.contrib.dbwriter.DbWriter;
import org.xmlBlaster.contrib.dbwriter.I_Parser;
import org.xmlBlaster.contrib.replication.I_Mapper;
import org.xmlBlaster.contrib.replication.ReplicationConstants;
import org.xmlBlaster.contrib.replication.impl.SearchableConfig;
import org.xmlBlaster.util.def.Constants;
import org.xmlBlaster.util.qos.ClientProperty;

/**
 * This info object is mainly used for two purposes: the one is the parsed object returned by each message.
 * The other is as an info object for each table in the replication (hold in cache to contain meta information
 * of the table on which to perform the operations (either INSERT, UPDATE or DELETE).
 * 
 * @author <a href="mailto:[e-mail]">Michele Laghi</a>
 */

public class SqlDescription {

   public final static String ME = "SqlDescription";
   
   public final static String DESC_TAG = "desc";
   
   public final static String IDENT_TAG = "ident";
   
   public final static String COMMAND_TAG = "command";
   
   final static String OLD_PREFIX = "<?xml version='1.0' encoding='UTF-8' ?>\n" +
                                      "<sql>\n" + 
                                      "  <desc><command>REPLICATION</command><ident>FAKE</ident></desc>\n" +
                                      "  <row num='0'>\n";

   final static String OLD_POSTFIX = "  </row>\n</sql>\n";

   private String identity;
   private String command;
   
   private List columnList;
   
   private String updateStatementTxt;
   private String deleteStatementTxt;
   private String insertStatementTxt;
   private boolean hasAddedStatements;

   private Map attributes;
   private List attributeKeys;
   private boolean caseSensitive;
   private boolean quoteColumnNames;
   private boolean pk;
   private boolean pkKnown;
   private boolean schemaKnown;
   private String schema; // since this is contained in the col info
   private String charSet = null;
   
   /** this is only needed for tables which do not have any PK and on updates */
   private I_Parser parser;
   
   private static Logger log = Logger.getLogger(SqlDescription.class.getName());

   private I_Info info;
   
   /** 
    * if set, it means the configuration has specified which columns have to be used for searches
    * in this table. It is used for delete and update to find the entry on which to perform the
    * operation
    */
   private volatile Set configuredSearchableColumns;
   
   /**
    * Gets the name of the schema. Since this information is not contained in the object iself but in the
    * Column information (since views could be a combination of more than one schema or catalog), this 
    * method checks that the schema is the same for all columns. If it is different, or if it is not
    * assigned, then null is returned.
    * @return
    */
   public String getSchema() {
      if (this.schemaKnown)
         return this.schema;
      synchronized(this) {
         if (this.schemaKnown)
            return this.schema;
         this.schema = extractColumnInfo(true); // false means catalog, true means schema
      }
      return this.schema;
   }
   
   public String getCompleteTableName() {
      String schema = getSchema();
      if (schema == null || schema.length() < 1)
         return this.identity;
      return schema + "." + this.identity;
   }
   
   /**
    * Gets the name of the schema. Since this information is not contained in the object iself but in the
    * Column information (since views could be a combination of more than one schema or catalog), this 
    * method checks that the catalog is the same for all columns. If it is different, or if it is not
    * assigned, then null is returned.
    * @return
    */
   public String getCatalog() {
      return extractColumnInfo(false); // false means catalog, true means schema
   }
   
   /**
    * Used by getSchema and getCatalog.
    * @return
    */
   private final synchronized String extractColumnInfo(boolean isSchema) {
      String ret = null;
      String tmp = null;
      if (this.columnList != null && this.columnList.size() > 0) {
         for (int i=0; i < this.columnList.size(); i++) {
            if (isSchema)
               tmp = ((SqlColumn)this.columnList.get(i)).getSchema();
            else 
               tmp = ((SqlColumn)this.columnList.get(i)).getCatalog();
            if (i == 0)
               ret = tmp;
            else {
               if (ret == null || tmp == null)
                  return null;
               if (!ret.equalsIgnoreCase(tmp))
                  return null;
            }
         }
         return ret;
      }
      return null;
   }
   
   public SqlDescription(I_Info info) {
      this.columnList = new ArrayList();
      this.attributes = new HashMap();
      this.attributeKeys = new ArrayList();
      this.caseSensitive = info.getBoolean(DbWriter.CASE_SENSITIVE_KEY, false);
      this.quoteColumnNames = info.getBoolean(DbWriter.QUOTE_COLUMN_NAMES_KEY, false);
      this.charSet = info.get("charSet", null);
      this.info = info;
      try {
         this.parser = new SqlInfoParser();
         this.parser.init(info);
      }
      catch (Exception ex) {
         ex.printStackTrace();
      }
   }


   public String[] getAttributeNames() {
      return (String[])this.attributeKeys.toArray(new String[this.attributeKeys.size()]);
   }

   public void clearColumnDescriptions() {
      if (this.columnList != null)
         this.columnList.clear();
   }
   
   public Map getAttributesClone() {
      if (this.attributes == null)
         return new HashMap();
      return new HashMap(this.attributes);
   }
   
   /**
    * Returns the requested attribute. If 'caseSensitive' has been set, the characters of the key are compared
    * case sensitively. If it is set to false, then it first searches for the case sensitive match, if nothing
    * is found it looks for the lowercase of the key, and finally if still no match it looks for the uppercase
    * alternative. If none of these is found, null is returned.
    *  
    * @param key the key of the attribute
    * @return the ClientProperty object associated with the key, or if none found, null is returned.
    */
   public ClientProperty getAttribute(String key) {
      ClientProperty prop = (ClientProperty)this.attributes.get(key);
      if (!this.caseSensitive && prop == null) {
         prop = (ClientProperty)this.attributes.get(key.toLowerCase());
         if (prop == null)
            prop = (ClientProperty)this.attributes.get(key.toUpperCase());
      }
      return prop;
   }
   
   
   /**
    * Stores the client property as a new value.
    * 
    * @param value the value to store as an attribute.
    */
   public void setAttribute(ClientProperty value) {
      SqlRow.storeProp(value, this.attributes, this.attributeKeys);
   }
   
   /**
    * Stores the String as a new value. The passed String is directly transformed into a ClientProperty object. 
    * @param value the value to store as an attribute.
    */
   public void setAttribute(String key, String value) {
      ClientProperty prop = new ClientProperty(key, null, null, value);
      SqlRow.storeProp(prop, this.attributes, this.attributeKeys);
   }
   
   /**
    * It copies (stores) all entries found in the map into the attributes. As values only String and ClientProperty
    * objects are allowed. If another type is found, an IllegalArgumentException is thrown. If null is passed, 
    * nothing is done.
    * 
    * @param map
    */
   public void addAttributes(Map map) {
      SqlRow.addProps(map, this.attributes, this.attributeKeys);
   }

   /**
    * Note this method has only sense when used in such cases where all columns belonging to this
    * description are in the same table.
    * 
    * @return
    */
   private final boolean hasPk() {
      if (this.pkKnown)
         return this.pk;
      synchronized (this) {
         if (this.pkKnown)
            return this.pk;
         this.pk = false;
         this.pkKnown = true;
         for (int i=0; i < this.columnList.size(); i++) {
            SqlColumn col = (SqlColumn)this.columnList.get(i); 
            if (col.isPrimaryKey()) {
               this.pk = true;
               return this.pk;
            }
         }
         return this.pk;
      }
   }
   
   final private boolean canAddColToSearch(SqlColumn sqlCol) {
      if (isColumnSearchConfigured(null))
         return isColumnSearchConfigured(sqlCol.getColName());
      return (sqlCol.isPrimaryKey() || !hasPk()) && sqlCol.isSearchable();
   }
   
   /**
    * 
    * @param searchEntries an empty list. This will be filled with the values of the
    * entries (ClientProperties) found in the row object: also NULL objects are considered.
    * and which can be used as search path (either pk or all).
    * @return a string containing the search statement. As an example:
    * " WHERE one=? AND two=? AND three=? " 
    */
   private final String createWhereStatement(SqlRow row, List searchEntries) {
      String[] colNames = row.getColumnNames();
      StringBuffer buf = new StringBuffer(256);
      buf.append(" WHERE ");
      boolean firstHit = true;
      for (int i=0; i < colNames.length; i++) {
         ClientProperty colContent = row.getColumn(colNames[i]);
         SqlColumn sqlCol = getColumn(colNames[i]);
         if (sqlCol == null) {
            log.info("column '" + colNames[i] + "' not found, will ignore it");
            continue;
         }
         // if ((sqlCol.isPrimaryKey() || !hasPk()) && sqlCol.isSearchable()) {
         boolean isNull = colContent.getType() != null && Constants.TYPE_NULL.equals(colContent.getType());
         if (canAddColToSearch(sqlCol)) {
            if (firstHit)
               firstHit = false;
            else
               buf.append("AND ");
            if (isNull) {
               buf.append(colNames[i]).append(" is NULL ");
            }
            else {
               searchEntries.add(colContent);
               buf.append(colNames[i]).append("=? ");
            }
         }
      }
      /*
       * This code does not work since the COL=NULL gives always back nothing (at least in oracle)
      if (!hasPk()) { // find possible NULL which will serve to determine uniqueness
         for (int i=0; i < this.columnList.size(); i++) {
            String colName = ((SqlColumn)this.columnList.get(i)).getColName();
            ClientProperty prop = row.getColumn(colName);
            if (prop == null) {
               buf.append(" AND ").append(colName).append("=NULL");
            }
         }
      }
      */
      return buf.toString();
   }
   
   /**
    * 
    * @param searchEntries an empty list. This will be filled with the values of the
    * entries (ClientProperties) found in the row object. Also NULL objects are now
    * added and which can be used as search path (either pk or all).
    * @return a string containing the search statement. As an example:
    * " (first, second, third) VALUES ( ? , ? , ? ) " 
    */
   private final String createInsertStatement(SqlRow row, List searchEntries) {
      String[] colNames = row.getColumnNames();
      StringBuffer buf1 = new StringBuffer(256);
      StringBuffer buf2 = new StringBuffer(256);
      buf1.append(" ( ");
      boolean firstHit = true;
      for (int i=0; i < colNames.length; i++) {
         ClientProperty colContent = row.getColumn(colNames[i]);
         if (getColumn(colNames[i]) != null) {
            searchEntries.add(colContent);
            if (firstHit) {
               firstHit = false;
            }
            else {
               buf1.append(", ");
               buf2.append(", ");
            }
            if (this.quoteColumnNames)
               buf1.append("\"").append(colNames[i]).append("\" ");
            else
               buf1.append(colNames[i]).append(" ");
            buf2.append("? ");
         }
      }
      buf1.append(") VALUES (").append(buf2).append(")");
      return buf1.toString();
   }
   
   /**
    * 
    * @param searchEntries an empty list. This will be filled with the values of the
    * entries (ClientProperties) found in the row object. Also null objects are added.
    * and which can be used as search path (either pk or all).
    * @return a string containing the search statement. As an example:
    * " SET one=? , two=? , three=? " 
    */
   private final String createSetStatement(SqlRow row, List searchEntries) {
      String[] colNames = row.getColumnNames();
      StringBuffer buf = new StringBuffer(256);
      buf.append(" SET ");
      boolean firstHit = true;
      for (int i=0; i < colNames.length; i++) {
         ClientProperty colContent = row.getColumn(colNames[i]);
         if (true) { // we need all entries
            searchEntries.add(colContent);
            if (firstHit)
               firstHit = false;