package triggertool.Components;
 
 import java.io.BufferedReader;
 import java.io.File;
 import java.io.FileWriter;
 import java.io.FileInputStream;
 import java.io.FileNotFoundException;
 import java.io.IOException;
 import java.io.InputStream;
 import java.io.InputStreamReader;
 import java.sql.Clob;
 import java.sql.Connection;
 import java.sql.DriverManager;
 import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.Statement;
 import java.util.regex.*;
 import java.util.Collections;
 import java.util.Iterator;
 import java.util.TreeMap;
 import java.util.Vector;
 import java.util.logging.Logger;
 import java.net.URL;
 import javax.swing.JOptionPane;
 import javax.xml.parsers.DocumentBuilder;
 import javax.xml.parsers.DocumentBuilderFactory;
 import oracle.jdbc.OracleConnection;
 import org.w3c.dom.Document;
 import org.w3c.dom.Element;
 import org.w3c.dom.NodeList;
 
 ///Handles the connection to the Trigger Database.
 /**
  * The new, reduced ConnectionManager class.  Deals with the database connection.
  * For example the difference between Oracle, MySQL, SQLite.  Also takes care
  * of the ATONR connection, and the differences between dealing with that and 
  * a normal conenction.  e.g. user access restrictions.
  * 
  * @author Simon Head
  */
 public class ConnectionManager {
 
     ///Version numbers of the trigger tool.  Major and Minor.
     public static int TT_MAJOR = 1;
     /** Minor version number of the trigger tool */
     public static int TT_MINOR = 34;
     /** Schema that this version of the TriggerTool requires.  Held in trigger_schema table, as the id */
     public static int SCHEMA_VERSION = 8;
     ///Message Log.
     private static Logger logger = Logger.getLogger(ConnectionManager.class.getName());
     /** Singleton pointer */
     private static ConnectionManager connMa = null;
     ///Login screen.
     public InitDialog theInitDialog;
     ///We need to keep a copy of the InitInfo.
     private InitInfo savedInitInfo;
     ///Path for logging and TMC files.
     private String log_path;
     ///Connection is made when the user logs in, then used whenever we call an SQL command.
     private Connection conn;
     ///Statement.
     private PreparedStatement stmt;
     ///If we're doing a dbCopy and writing, change this to true.
     private boolean doDBCopy = false;
     ///Combined with doDBCopy, this is the connection to write to.
     ///Not currently used since for DBCopy2 we just change the main connection.
     private Connection copy_to_connection = null;
 
      /**
      * This registers the additional JAR packages we need to
      * talk to the database(s) using Oracle, MySQL and SQLite
      */
     public ConnectionManager() {
         try {
             Class.forName("com.mysql.jdbc.Driver").newInstance();
         } catch (Exception e) {
             logger.severe("Error loading MySQL driver");
             e.printStackTrace();
         }
 
         try {
             DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
         } catch (SQLException e) {
             logger.severe("Error loading Oracle driver");
             e.printStackTrace();
         }
 
         try {
             Class.forName("org.sqlite.JDBC").newInstance();
         } catch (Exception e) {
             logger.severe("Error loading SQLite driver");
             e.printStackTrace();
         }
 
         log_path = System.getenv("TRIGGERTOOL_LOGDIRECTORY");
 
         if (log_path == null) {
             log_path = "";
         } else if (log_path.lastIndexOf("/") != log_path.length() - 1) {
             log_path += "/";
         }
     }
 
        /**
      * Form a connection to the relevant database
      * At the moment this can connect to MySQL, Oracle and a SQLite file
      * Also stores a copy of the initialisation information in savedInitInfo
      * 
      * Does a lot of tricky things on atonr, including looking up a user's 
      * access level and signing in as either the read only or write account
      * based on its findings.
      *
      * @param theInitInfo Object that contains the start-up information
      * @return true if the connection was made ok
      */
     public boolean connect(InitInfo theInitInfo) throws SQLException {
         savedInitInfo = theInitInfo;
         String username = theInitInfo.getUserName();
         String password = theInitInfo.getPassWord();
         String tablename = theInitInfo.getTableName();
         String hostname = theInitInfo.getdbServer();
         String dbtechnology = theInitInfo.getdbTechnology();
         String filename = theInitInfo.getFilename();
 
         //System.out.println("filename here is " + filename);
         
         // first check if dblookup and authentication have to be used
         if ((dbtechnology.equals("SQLite")&&filename.equals(""))||              
             (((dbtechnology.equals("MySQL"))||(dbtechnology.equals("Oracle")))&&(password.equals("") && username.equals("")))) { 
             // need to authenticate using authentication.xml
 
             String svcName = "";
             File f = null;
 
             // first build a xml file parser
             DocumentBuilder parser = null;
             try {
                 DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
                 factory.setIgnoringComments(true);
                 factory.setCoalescing(true);
                 factory.setNamespaceAware(false);
                 factory.setValidating(false);
                 parser = factory.newDocumentBuilder();
             } catch (Exception e) {
                 System.out.println("Problems building document parser " + e.getMessage());
                 return false;
             }
 
             if (!dbtechnology.equals("Oracle") &&
                 !dbtechnology.equals("MySQL") &&
                 !dbtechnology.equals("SQLite")) { // need to lookup using dblookup.xml
 
                 String alias = hostname;
                 logger.fine("TriggerTool is using dblookup on alias " + alias + " to find the connection parameter");
                 String coral_dblookup_path = System.getenv("CORAL_DBLOOKUP_PATH");
 
                 Vector<File> dbLUfiles = new Vector<File>();
 
                 f = new File("./dblookup.xml");
                 if (f.exists()) {
                     dbLUfiles.addElement(f);
                 }
                 f = new File(coral_dblookup_path + "/dblookup.xml");
                 if (f.exists()) {
                     dbLUfiles.addElement(f);
                 }
                 if (dbLUfiles.isEmpty()) {
                     System.out.println("No dblookup file, neither locally nor in '" + coral_dblookup_path + "'");
                     return false;
                 }
 
                 // look in each file (one or two) to find the first
                 // occurance of the alias and take the first instance of
                 // service (this is also the behavior of CORAL, so we do
                 // the same here)
                 Element lookedUp = null;
                 Iterator<File> fIt = dbLUfiles.iterator();
                 while (fIt.hasNext()) {
                     f = fIt.next();
                     try {
                         // first get the xml document from the file
                         Document document = parser.parse(f);
                         // then get all the locical services
                         NodeList lservices = document.getElementsByTagName("logicalservice");
                         // and find ours
                         for (int i = 0; i < lservices.getLength(); i++) {
                             if (((Element) lservices.item(i)).getAttribute("name").equals(alias)) {
                                 Element lservice = (Element) lservices.item(i);
                                 lookedUp = (Element) lservice.getElementsByTagName("service").item(0);
                                 break;
                             }
                         }
                     } catch (Exception e) {
                         System.out.println("Problems in file " + f.getPath() + "/" + f.getName() + ": " + e.getMessage());
                     }
                     if (lookedUp != null) {
                         break;
                     }
                 }
                 if (lookedUp == null) {
                     System.out.println("Could not find alias " + alias + " in any of the dblookup files!");
                     return false;
                 }
                 svcName = lookedUp.getAttribute("name");
                 String connectionSt = lookedUp.getAttribute("name");
                 Pattern p = Pattern.compile("(\\w+)://(\\w+?)/(\\w+)");
                 Matcher m = p.matcher(connectionSt);
                 if (m.find()) {
                     dbtechnology = m.group(1);
                     hostname = m.group(2).toLowerCase();
                     tablename = m.group(3);
                     if (dbtechnology.toLowerCase().equals("oracle")) {
                         dbtechnology = "Oracle";
                     }
                     if (dbtechnology.toLowerCase().equals("mysql")) {
                         dbtechnology = "MySQL";
                     }
                     if (dbtechnology.toLowerCase().equals("sqlite")) {
                         dbtechnology = "SQLite";
                     }
                 } else {
                     System.out.println("Connection " + svcName + " does not match pattern 'techno://server/schema'");
                 }
             } else {
                 // a technology is given, put the connection together for authentication
                 svcName = dbtechnology.toLowerCase() + "://" + hostname + "/" + tablename;
             }
 
             logger.fine("TriggerTool is using authentication on connection " + svcName + " to find account name and password");
             String coral_auth_path = System.getenv("CORAL_AUTH_PATH");
             Vector<File> dbAuthfiles = new Vector<File>();
             f = new File("./authentication.xml");
             if (f.exists()) {
                 dbAuthfiles.addElement(f);
             }
             f = new File(coral_auth_path + "/authentication.xml");
             if (f.exists()) {
                 dbAuthfiles.addElement(f);
             }
             if (dbAuthfiles.isEmpty()) {
                 System.out.println("No authentication file, neither locally nor in '" + coral_auth_path + "'");
                 return false;
             }
 
 
             // look in each file (one or two) to find the first
             // occurance of the alias and take the first instance of
             // service (this is also the behavior of CORAL, so we do
             // the same here)
             Element authConn = null;
             Iterator<File> fIt = dbAuthfiles.iterator();
             while (fIt.hasNext()) {
                 f = fIt.next();
                 try {
                     // first get the xml document from the file
                     Document document = parser.parse(f);
                     // then get all the connections
                     NodeList connections = document.getElementsByTagName("connection");
                     // and find ours
                     for (int i = 0; i < connections.getLength(); i++) {
                         if (!((Element) connections.item(i)).getAttribute("name").equals(svcName)) {
                             continue;
                         }
                         logger.fine("Found connection for " + svcName);
                         authConn = (Element) connections.item(i);
                         NodeList params = authConn.getElementsByTagName("parameter");
                         for (int j = 0; j < params.getLength(); j++) {
                             Element param = (Element) params.item(j);
                             if (param.getAttribute("name").equals("user")) {
                                 username = param.getAttribute("value");
                             }
                             if (param.getAttribute("name").equals("password")) {
                                 password = param.getAttribute("value");
                             }
                         }
                         if (username == null || password == null) {
                             System.out.println("In authentication file " + f.getPath() + "/" + f.getName());
                             System.out.print("connection " + svcName);
                             System.out.println(" has no user or no password");
                             return false;
                         }
                         break;
                     }
                 } catch (Exception e) {
                     System.out.println("Problems in file " + f.getPath() + "/" + f.getName() + ": " + e.getMessage());
                 }
                 if (authConn != null) {
                     break;
                 }
             }
             if (authConn == null) {
                 System.out.println("Could not find connection " + svcName + " in any of the authentication files!");
                 return false;
             }
             logger.fine("Found user " + username + "on host " + hostname);
         //hostname might be intr, which is ok, but atlas_config means atonr! confusing
         if(hostname.toLowerCase().equals("atlas_config")) hostname = "atonr";
             theInitInfo.setdbServer(hostname);
             theInitInfo.setUserName(username);
             theInitInfo.setPassWord(password);
             theInitInfo.setTableName(tablename);
             theInitInfo.setdbTechnology(dbtechnology);
             theInitInfo.setFilename(filename);
             if (dbtechnology.equals("Oracle")) {
                 hostname = theInitInfo.getdbServer(); // the translation from server name to server connection is in InitInfo
             }
 
         }//end of dblookup/authentication style connection
         ////////////////////////////////////////////////////////////////////////
         
         String url = "";
         boolean ok = true;
 
         if (dbtechnology.equals("Oracle")) {
             url = hostname;
         } else if (dbtechnology.equals("MySQL")) {
             //url = "jdbc:mysql://" + hostname + ":3306/" + tablename + "?zeroDateTimeBehavior=convertToNull";
             url = "jdbc:mysql://" + hostname + ":3306/" + tablename;
         } else if (dbtechnology.equals("SQLite")) {
             url = "jdbc:sqlite:" + filename;
         }
 
         logger.info("url: " + url);
 
         String r_username = "atlas_conf_trigger_v2_r";
         String atonrR_password = theInitInfo.getAtonrRPassword();
 
         //writer account on atonr
         if (theInitInfo.getUserName().contains("atlas_conf_trigger_v2_w") && theInitInfo.getdbServer().contains("atonr")) { // || theInitInfo.getdbServer().contains("devdb10")) {
 
             //log on first as reader to check if privilege ok to log on as writer!
             logger.fine("Attempting to log on to atlas_conf_trigger_v2_w@atonr");
             conn = DriverManager.getConnection(url, r_username, atonrR_password);
 
             //System.out.println("Attempting to log on to atlas_conf_trigger_v2_w@atonr");
             //System.out.println("User level requested: " + theInitInfo.getMode());
             
             boolean pw_ok = checkPassword(getInitInfo().getSystemUsername(), getInitInfo().encryptAtonrPassword());
 
             if (!pw_ok) {
                 throw new SQLException("Incorrect password or user level for user " + getInitInfo().getSystemUsername(), "Error");
             }
         }
 
         //reader account on atlr
         if (theInitInfo.getUserName().contains("atlas_conf_trigger_v2_r") && theInitInfo.getdbServer().contains("atlr")) { // || theInitInfo.getdbServer().contains("devdb10")) {
             username = r_username;
             getInitInfo().setUserName(r_username);
             //password is set in the box - users must know it!
             getInitInfo().setTableName("atlas_conf_trigger_v2");
         }
 
         logger.fine(url);
         logger.fine(username);
         conn = DriverManager.getConnection(url, username, password);
 
         if (dbtechnology.equals("Oracle")) {
             ((OracleConnection) conn).setStatementCacheSize(100);
             ((OracleConnection) conn).setImplicitCachingEnabled(true);
         }
 
         //if (dbtechnology.equals("SQLite") && theInitInfo.getInsertSchema()) {
         if (dbtechnology.equals("SQLite")) {
             logger.info("Selected SQLite...");
         }
 
         //check the schema version matches the trigger tool
         int version = -1;
 
         try {
             version = getSchemaVersion();
         } catch (SQLException ex) {
             //CASE WHERE NO SCHEMA VERSION FOUND
             //*** Used to offer a chance to INSTALL the schema - don't do this anymore!
             JOptionPane.showMessageDialog(null, "I think you have no schema installed, or a very old one!", "Error", JOptionPane.ERROR_MESSAGE);
             throw new SQLException("I think you have no schema installed, or a very old one!" + ex);
             //if (!theInitInfo.getdbServer().contains("atonr")) {
             //    Object[] options = {"Yes, please", "No, thanks"};
             //    int n = JOptionPane.showOptionDialog(null,
             //            "I think you have no schema installed, or a very out of date one. Would you like me to make one? All existing data will be lost.",
             //            "Populate Schema?",
             //            JOptionPane.YES_NO_CANCEL_OPTION,
             //            JOptionPane.QUESTION_MESSAGE,
             //            null,
             //            options,
             //            options[1]);
 
             //    if (n == 0) {
             //        new ResetSchema();
             //        version = getSchemaVersion();
             //    } else {
             //        ok = false;
             //        return ok;
             //    }
             //} else {
             //    throw new SQLException("No schema version found on ATONR!", "Error, atonr schema out of date.");
             //}
             
         }
 
         if (version == SCHEMA_VERSION) {
             ok = true;
         } else {
             //CASE WHERE OLD SCHEMA VERSION FOUND
             //*** Used to offer a chance to REGEN the schema - don't do this anymore!
             JOptionPane.showMessageDialog(null, "I think you have an out of data schema installed!", "Error", JOptionPane.ERROR_MESSAGE);
             //if (!theInitInfo.getdbServer().contains("atonr")) {
             //    Object[] options = {"Yes, please", "No, thanks"};
             //    int n = JOptionPane.showOptionDialog(null,
             //            "This TriggerTool needs schema version " + SCHEMA_VERSION + ".\n" +
             //            "This Database has schema version " + version + "\n" +
             //            "Would you like to replace the Schema in the Database?  All existing data will be lost.",
             //            "Replace Schema?",
             //            JOptionPane.YES_NO_CANCEL_OPTION,
             //            JOptionPane.QUESTION_MESSAGE,
             //            null,
             //            options,
             //            options[1]);
 
             //    if (n == 0) {
             //        new ResetSchema();
             //        version = getSchemaVersion();
             //    } else {
             //        ok = false;
             //        return ok;
             //    }
 
             //    if (version != SCHEMA_VERSION) {
             //        throw new SQLException("Even after replacing the Schema it still didn't work", "Very Bad Error indeed");
             //    }
             //}
         }
         logger.fine("Connection made " + ok);
         return ok;
     }
 
     public void changeUser(String user, String pwd, int mode) {
         //System.out.println("Changing connection parameters");
         boolean good = false;
 
         if (mode == InitInfo.USER) {
             good = true;
         } else {
             try {
                 good = checkPassword(user, getInitInfo().encrypt(pwd));
             } catch (SQLException ex) {
                 System.out.println(ex.getMessage());
                 ex.printStackTrace();
             }
         }
 
         if (good) {
             try {
                 conn.close();
 
                 if (mode == InitInfo.USER) {
                     getInitInfo().setUserName("atlas_conf_trigger_v2_r");
                     getInitInfo().setMode(mode);
                 } else {
                     getInitInfo().setUserName("atlas_conf_trigger_v2_w");
                     getInitInfo().setSystemUsername(user);
                     getInitInfo().setPassWord(getInitInfo().getAtonrWPassword());
                     getInitInfo().setOnlineUserPassword(pwd);
                     getInitInfo().setMode(mode);
                 }
 
                 connect(getInitInfo());
             } catch (SQLException ex) {
                 System.out.println(ex.getMessage());
                 ex.printStackTrace();
             }
         }
 
         //System.out.println("Done with change");
     }
 
     private boolean checkPassword(String user, String pwd) throws SQLException {
         boolean pw_ok = false;
         try {
             
             String query = "SELECT TT_LEVEL, TT_PASSWORD FROM ATLAS_CONF_TRIGGER_V2.TT_USERS WHERE TT_USER=?";
             PreparedStatement ps = getConnection().prepareStatement(query);
             ps.setString(1, user);
             ResultSet rset = ps.executeQuery();
 
             //System.out.println("Setting to shift mode for some reason... will override with correct privilege");
             //getInitInfo().setMode(InitInfo.SHIFTER);
 
             //System.out.println("User level query: " + query);
             
             while (rset.next()) {
                 
                 //ugly
                 int allowedmode = -1;
                 if(rset.getString(1).equals("Shifter")) allowedmode = 2;
                 if(rset.getString(1).equals("TriggerMenuMeister")) allowedmode = 5;
                 if(rset.getString(1).equals("TriggerMeister")) allowedmode = 6;
                 //System.out.println("PJB trying to connect in mode " + getInitInfo().getMode());
                 //System.out.println("PJB user " + user + " has access " + rset.getString(1) + " = " + allowedmode);
                 
                 if (rset.getString(2).equals(pwd) && allowedmode>=getInitInfo().getMode()) {
                     //System.out.println("The passwords match and the level is OK");
                     pw_ok = true;
                 } else {
                     pw_ok = false;
                 }
             }
 
             rset.close();
             ps.close();
             getConnection().close();
         } catch (SQLException ex) {
             throw new SQLException("Table containing access levels is missing in atonr", "Error");
         }
         return pw_ok;
     }
 
     public int countSuperMasterTables(String query, int id) {
         int count = -10;
         try {
             query = fix_schema_name(query);
             PreparedStatement ps = getConnection().prepareStatement(query);
 
             ps.setInt(1, id);
             ResultSet rs = ps.executeQuery();
 
             while (rs.next()) {
                 count = rs.getInt(1);
             }
 
             ps.close();
         } catch (SQLException ex) {
             logger.warning("SQL Ex: " + ex.getMessage());
         }
 
         return count;
     }
 
     public boolean doingDBCopy() {
         return doDBCopy;
     }
     
     ///Reload a row from the database.
     /**
      * Cause a single row to be reloaded from the database.  This is ok for a 
      * small number of rows, but is far too slow to be used in general.
      * 
      * @param aThis The record that we want reloading.
      */
     public void forceLoad(AbstractTable aThis) {
         try {
             String query = aThis.getQueryString();
             query += " WHERE " + aThis.tablePrefix + "ID=?";
             query = fix_schema_name(query);
 
             PreparedStatement ps = ConnectionManager.getInstance().getConnection().prepareStatement(query);
             ps.setInt(1, aThis.get_id());
             ResultSet rset = ps.executeQuery();
 
             while (rset.next()) {
                 aThis.loadFromRset(rset);
             }
 
             rset.close();
             ps.close();
         } catch (SQLException ex) {
             ex.printStackTrace();
         }
     }
 
     ///Get the connection information.
     /**
      * Get the init info, which contains important information about the user
      * and the database connection parameters.
      * 
      * @return The Init Info object.
      */
     public InitInfo getInitInfo() {
         return savedInitInfo;
     }
 
     ///Return the connection. Must be used.
     /**
      * Use this method when referring to the connection, so that we can swap
      * between the conncetion to read from and the conncetion to write to.
      * 
      * @return The connection.  If doDBCopy return the connection to write to.
      */
     public Connection getConnection() {
         Connection connection = conn;
 
         //if (doDBCopy) {
         //    connection = copy_to_connection;
         //}
 
         return connection;
     }
 
     ///Set another connection  for the write account.
     /**
      * The user wants to switch from the reading account, to the connection to 
      * write to.  For now this is hard coded but will change.
      * 
      * @param string Null for now.
      * @param string0 Null for now.
      * @throws java.sql.SQLException Quit when we have a problem.
      */
     public void setWriteDB(String url, String userName, String userPassword) throws SQLException {
         //System.out.println("Setting Write DB");
         doDBCopy = true;
         if (userName.equals("")){
             userName = getInitInfo().getUserName();
         }
         copy_to_connection = DriverManager.getConnection(url, userName, userPassword);
         //System.out.println("Opened write to account");
     }
 
     /**
      * Swap back to the reading database, and kill the copy to database.
      */
     public void cancelWriteDB() throws SQLException {
         doDBCopy = false;
         //copy_to_connection.close();
         //copy_to_connection = null;
     }
 
     /**
      * Save a clob to the database
      *
      * @param tableName Name of the table to insert the clob into
      * @param fieldName Field for the clob
      * @param filename Text file to insert
      * @param tablePrefix Used to concatenate a string prefix_id
      * @param id integer id of the record we want to replace (insert not yet supported)
      */
     public void set_clob(String tableName, String fieldName, String filename, String tablePrefix, int id) {
         logger.fine("Ready to insert clob");
 
         try {
             if (getInitInfo().getdbTechnology().contains("Oracle") && getInitInfo().getTableName().length() > 2) {
                 tableName = getInitInfo().getTableName() + "." + tableName;
             }
 
             String query = "UPDATE " + tableName + " SET " + fieldName + "=? WHERE " + tablePrefix + "ID=" + id;
 
             query = fix_schema_name(query);
             logger.fine("Query: " + query);
             PreparedStatement pstmt = getConnection().prepareStatement(query);
 
             logger.fine("Opening " + filename);
 
             File fFile = new File(filename);
             FileInputStream fis = new FileInputStream(fFile);
 
             if (getInitInfo().getdbTechnology().contains("Oracle")) {
                 InputStreamReader irdr = new InputStreamReader(fis);
                 BufferedReader brdr = new BufferedReader(irdr, 8192);
 
                 String str = "";
                 String line;
                 int nlines = 0;
                 StringBuffer buffer = new StringBuffer();
                 logger.fine("going to read file");
                 try {
                     while ((line = brdr.readLine()) != null) {
                         nlines++;
                         buffer.append(line + "\n");
                     }
                     logger.fine("read lines:  " + nlines);
                     brdr.close();
                 } catch (IOException e) {
                     logger.warning("File reading failed");
                     logger.warning("Sometimes this is ok");
                 }
                 logger.fine("going to save clob:");
                 str = buffer.toString();
                 oracle.sql.CLOB newClob = oracle.sql.CLOB.createTemporary(conn, false, oracle.sql.CLOB.DURATION_CALL);
 
                 newClob.putString(1, str);
                 pstmt.setClob(1, newClob);
             } else {
                 //MySQL / SQLite
                 if (fis != null) {
                     pstmt.setAsciiStream(1, fis, (int) fFile.length());
                 } else {
                     logger.warning("Null fis");
                 }
             }
             pstmt.executeUpdate();
 
             pstmt.close();
             logger.finer("Clob insert ok");
         } catch (FileNotFoundException e) {
             logger.warning("CLOB FILE ERROR");
             logger.warning(e.getMessage());
             e.printStackTrace();
         } catch (SQLException e) {
             logger.warning("CLOB SQL ERROR!");
             logger.warning(e.getMessage());
             e.printStackTrace();
         }
     }
 
     /**
      * Get a clob from the database
      * 
      * @param tableName
      * @param tablePrefix
      * @param id
      * @param field
      * @return String representation of the clob
      */
     public String get_clob(String tableName, String tablePrefix, int id, String field) {
         StringBuffer strOut = new StringBuffer();
         String query = "SELECT " + tablePrefix + field + " FROM " + tableName + " WHERE " + tablePrefix + "ID=?";
         query = fix_schema_name(query);
         try {
             PreparedStatement ps = getConnection().prepareStatement(query);
             ps.setInt(1, id);
             ResultSet rset = ps.executeQuery();
             while (rset.next()) {
                 Clob out = rset.getClob(tablePrefix + field);
                 if (out != null) {
 
                     String aux;
                     BufferedReader br = new BufferedReader(out.getCharacterStream());
 
                     try {
                         while ((aux = br.readLine()) != null) {
                             strOut.append(aux).append("\n");
                         }
                     } catch (IOException e) {
                         logger.warning("problem converting clob to string");
                         e.printStackTrace();
                     }
                 }
             }
             rset.close();
             ps.close();
         } catch (Exception e) {
             logger.warning("Error reading clob");
         }
 
         return strOut.toString();
     }
 
     /**
      * Get the log path.
      * 
      * @return The log path.
      */
     public String get_log_path() {
         return log_path;
     }
 
     /**
      * This needs to be a singleton so we can manage the connection
      * to the database properly
      *
      * @return Instance of the singleton
      */
     public static synchronized ConnectionManager getInstance() {
         if (connMa == null) {
             connMa = new ConnectionManager();
         }
         return connMa;
     }
 
     /**
      * Function to connect towrite db, uses connect(..) function - see below.
      * @param ii - the InitInfo object
      * @return true if successful
      * @throws java.sql.SQLException
      */
     public boolean connect_towritedb(InitInfo ii) throws SQLException {
         doDBCopy=true;
         return connect(ii);
     }
     
     
     /**
      * Converts a java Boolean type to a string (as used in the
      * database schema)
      *
      * @param used True or false
      * @return 0 or 1 as a string
      */
     private String format_bool(Boolean used) {
         String used_str = "0";
         if (used) {
             used_str = "1";
         }
         return used_str;
     }
 
     /**
      * When doing a SQL statement that doesn't have a return
      * for example removing a record use this
      *
      * @param query The SQL code to be executed
      * @throws SQLException
      */
     public void executeStatement(String query) throws SQLException {
         query = fix_schema_name(query);
         try {
             if (stmt != null) {
                 stmt.close();
             }
         } catch (Exception ex) {
             logger.warning("can't close stmt");
             String exceptionMessage = "DB Error: ";
             exceptionMessage += ex.getMessage();
             exceptionMessage += "Query:\t" + query;
             throw new SQLException(exceptionMessage);
         }
 
         stmt = getConnection().prepareStatement(query);
 
         try {
             stmt.execute();
         } catch (SQLException ex) {
             String exceptionMessage = "DB Error ";
             exceptionMessage += ex.getMessage();
             exceptionMessage += "Query:\t" + query;
             throw new SQLException(exceptionMessage, ex.getSQLState());
         }
 
         if (!getInitInfo().getdbTechnology().contains("SQLite")) {
             try {
                 stmt.executeUpdate("COMMIT");
             } catch (SQLException ex) {
                 String exceptionMessage = "DB Error ";
                 exceptionMessage += ex.getMessage();
                 exceptionMessage += "Query:\t" + query;
                 throw new SQLException(exceptionMessage, ex.getSQLState());
             }
         }
     }
 
     public int save(String tableName, String tablePrefix, Integer id, TreeMap<String, Object> fields_values) throws SQLException {
         if (id > 0) {
             //logger.info("Will not save");
             return id;
         }
 
         //logger.info("Will save");
 
         //versioning for comp depends on name and alias
         if(!tableName.equals("HLT_COMPONENT")){
             if (fields_values.containsKey("VERSION")) {
                 fields_values.put("VERSION", new Integer(1 + getMaxVersion(tableName, tablePrefix, (String) fields_values.get("NAME"))));
             }
         }else{
             if (fields_values.containsKey("VERSION")) {
                 fields_values.put("VERSION", new Integer(1 + getMaxCompVersion((String) fields_values.get("NAME"), (String) fields_values.get("ALIAS"))));
             }
         }
         
 
         //for 
         StringBuffer sb = new StringBuffer();
         StringBuffer sb2 = new StringBuffer();
         StringBuffer sb3 = new StringBuffer();
 
         //code to save a new record
         sb.append("INSERT INTO ");
         sb.append(tableName);
         sb.append(" (" + tablePrefix + "ID," + tablePrefix + "MODIFIED_TIME," + tablePrefix + "USERNAME,");
 
         for (String key : fields_values.keySet()) {
 
             if (key.equals("MODIFIED_TIME") || key.equals("USERNAME") || key.equals("ID")) {
                 continue;
             }
             sb.append(tablePrefix);
             sb.append(key);
             sb.append(",");
             sb2.append("?,");
         }
 
         sb.deleteCharAt(sb.length() - 1);
         sb2.deleteCharAt(sb2.length() - 1);
 
         sb.append(") VALUES (");
 
         if (getInitInfo().getdbTechnology().contains("MySQL") ||
                 getInitInfo().getdbTechnology().contains("SQLite")) {
             sb.append("?,NULL,?,");
         } else {
             sb.append("?,sysdate,?,");
         }
         sb.append(sb2);
         sb.append(")");
 
         //System.out.println("Insert Query : " + sb.toString());
 
         String query = "SELECT MAX(" + tablePrefix + "ID) FROM " + tableName;
         query = fix_schema_name(query);
         PreparedStatement s = getConnection().prepareStatement(query);
 
         ResultSet r = s.executeQuery();
         while (r.next()) {
             id = r.getInt(1) + 1;
         }
 
         s.close();
 
         s = getConnection().prepareStatement(fix_schema_name(sb.toString()));
 
         String humanQuery = fix_schema_name(sb.toString());
         humanQuery = humanQuery.replaceFirst("\\?", "" + id);
         humanQuery = humanQuery.replaceFirst("\\?", getInitInfo().getSystemUsername());
 
         s.setInt(1, id);
 
         s.setString(2, getInitInfo().getSystemUsername());
         sb3.append(getInitInfo().getSystemUsername());
         sb3.append(" ");
 
         int i = 3;
 
         for (String key : fields_values.keySet()) {
             if (key.equals("MODIFIED_TIME") || key.equals("USERNAME") || key.equals("ID")) {
                 continue;
             }
 
             Object obj = fields_values.get(key);
 
             if (obj instanceof String) {
                 String str = (String) obj;
                 if (str == null || str.equals("")) {
                     str = "~";
                 }
 
                 s.setString(i, str);
                 sb3.append(str);
                 sb3.append(" ");
             }
 
             if (obj instanceof Integer) {
                 s.setInt(i, (Integer) obj);
                 sb3.append((Integer) obj);
                 sb3.append(" ");
             }
 
             if (obj instanceof Boolean) {
                 s.setString(i, format_bool((Boolean) obj));
                 sb3.append(format_bool((Boolean) obj));
                 sb3.append(" ");
             }
 
             if (obj instanceof Double) {
                 s.setDouble(i, (Double) obj);
                 sb3.append((Double) obj);
                 sb3.append(" ");
             }
 
             if (obj instanceof Float) {
                 s.setDouble(i, (Float) obj);
                 sb3.append((Float) obj);
                 sb3.append(" ");
             }
 
             humanQuery = humanQuery.replaceFirst("\\?", obj.toString());
 
             ++i;
         }
 
         //logger.info("Insert Params 1: " + sb3.toString());
 
         //System.out.println("INSERT: " + humanQuery);
 
         try {
             s.executeUpdate();
         } catch (SQLException ex) {
             String errMsg = "Caught SQL error: " + ex.getMessage();
             errMsg += "query: " + sb.toString() + "\n";
             errMsg += "params: " + sb3.toString() + "\n";
             logger.severe(errMsg);
             ex.printStackTrace();
             throw ex;
         }
 
         s.close();
 
         return id;
     }
 
     /**
      * @brief Get the version.
      * 
      * When we do a copy we need to increment the version.  This returns the max
      * version that exists in the database.  Also used in the XML reading in to
      * database.
      *
      * @param tableName Name of the table we're searching.
      * @param tablePrefix Prefix for the table.
      * @param name The name of the row.
      * 
      * @return the max version, if you're writing a new record add one to this
      */
     public int getMaxVersion(String tableName, String tablePrefix, String name) {
         StringBuffer query = new StringBuffer();
 
         if (tableName.equals("HLTTriggerSignature") || tableName.equals("HLTRelease")) {
             query.append("SELECT MAX(");
             query.append(tableName);
             query.append(") FROM ");
             query.append(tableName);
             query.append(" WHERE ");
             query.append(tableName);
             query.append("=?");
         } else {
             query.append("SELECT MAX(");
             query.append(tablePrefix);
             query.append("VERSION) FROM ");
             query.append(tableName);
             query.append(" WHERE ");
             query.append(tablePrefix);
             query.append("NAME=?");
         }
 
         int max = 0;
         try {
             String q = fix_schema_name(query.toString());
             PreparedStatement ps = getConnection().prepareStatement(q);
             ps.setString(1, name);
             ResultSet rset = ps.executeQuery();
 
             while (rset.next()) {
                 max = rset.getInt(1);
             }
 
             rset.close();
             ps.close();
         } catch (SQLException e) {
             e.printStackTrace();
         }
         return max;
     }
 
     /**
      * Special adaptation of getMaxVersion for compt, which needs name and alias
      */ 
     
     public int getMaxCompVersion(String name, String alias) {
 
         String query = "SELECT MAX(HCP_VERSION) FROM HLT_COMPONENT WHERE HCP_NAME=? AND HCP_ALIAS=?";
 
         int max = 0;
         try {
             query = fix_schema_name(query);
             PreparedStatement ps = getConnection().prepareStatement(query);
             ps.setString(1, name);
             ps.setString(2, alias);
             ResultSet rset = ps.executeQuery();
 
             while (rset.next()) {
                 max = rset.getInt(1);
             }
 
             rset.close();
             ps.close();
         } catch (SQLException e) {
             e.printStackTrace();
         }
         return max;
     }
 
     /**
      * For HLT XML upload.  Need to link elements to a component
      *
      * @param setupID id for the setup record
      * @param alias
      * @return id of the hlt component
      */
     public int findComponent(int setupID, String alias) {
         int hcp_id = -1;
 
         StringBuffer buffer = new StringBuffer();
         buffer.append("SELECT HCP_ID FROM HLT_COMPONENT, HLT_ST_TO_CP");
         buffer.append(" WHERE HLT_COMPONENT.HCP_ALIAS=\'?\'");
         buffer.append(" AND HLT_ST_TO_CP.HST2CP_COMPONENT_ID=HLT_COMPONENT.HCP_ID");
         buffer.append(" AND HLT_ST_TO_CP.HST2CP_SETUP_ID=?");
 
         String query = fix_schema_name(buffer.toString());
 
         try {
             logger.finer("findcom " + query);
             PreparedStatement ps = getConnection().prepareStatement(query);
             ps.setString(1, alias);
             ps.setInt(1, setupID);
 
             ResultSet rset = ps.executeQuery();
             while (rset.next()) {
                 hcp_id = rset.getInt(1);
             }
             rset.close();
             ps.close();
         } catch (SQLException e) {
             e.printStackTrace();
         }
 
         return hcp_id;
     }
 
     public Vector<Integer> get_IDs(String table_name, String table_prefix, TreeMap<String, Object> fields_values, String order, String ignore) throws SQLException {
         Vector<String> ignoreList = new Vector<String>();
         ignoreList.add(ignore);
         return get_IDs(table_name, table_prefix, fields_values, order, ignoreList);
     }
 
     ///Get the rows from the database that have columns matching those in fields_values.
     /**
      * Note you can pass keyValues for a class and specify those to be ignore.
      * By default USERNAME, MODIFIED_TIME, USED and VERSION are ignore by the
      * seach.  ID is not, since you might not always want to!
      * 
      * @param table_name Database name of table.
      * @param table_prefix Database prefix including underscore, SMT_
      * @param fields_values Criteria to match in search.
      * @param order NULL?
      * @param ignoreList Vector of fields to ignore in the match.
      * @return Vector of all the matching IDs in ascending order.
      * @throws java.sql.SQLException
      */
     public Vector<Integer> get_IDs(String table_name, String table_prefix, TreeMap<String, Object> fields_values, String order, Vector<String> ignoreList) throws SQLException {
         if (ignoreList == null || ignoreList.size() == 0) {
             ignoreList = new Vector<String>();
         }
 
         ignoreList.add("USERNAME");
         ignoreList.add("MODIFIED_TIME");
         ignoreList.add("VERSION");
         ignoreList.add("USED");
 
         if (order == null) {
             order = "";
         }
 
         Vector<Integer> ids_vec = new Vector<Integer>();
 
         StringBuffer sb = new StringBuffer();
         sb.append("SELECT ");
         sb.append(table_prefix);
         sb.append("ID FROM ");
         sb.append(table_name);
         sb.append(" WHERE ");
 
         int i = 1;
         StringBuffer sb3 = new StringBuffer();
 
         try {
             for (String key : fields_values.keySet()) {
                 boolean skipField = false;
                 for (String ignore : ignoreList) {
                     if (key.equals(ignore)) {
                         skipField = true;
                     }
                 }
 
                 if (skipField) {
                     continue;
                 }
                 sb.append(table_prefix);
                 sb.append(key);
                 sb.append("=? AND ");
             }
 
             sb.delete(sb.length() - 5, sb.length());
 
             sb.append(" ORDER BY " + table_prefix + "ID ASC");
 
             //logger.info("query:  " + sb.toString());
 
             String q = fix_schema_name(sb.toString());
             PreparedStatement s = getConnection().prepareStatement(q);
 
             for (String key : fields_values.keySet()) {
                 boolean skipField = false;
                 for (String ignore : ignoreList) {
                     if (key.equals(ignore)) {
                         skipField = true;
                     }
                 }
 
                 if (skipField) {
                     continue;
                 }
 
                 Object obj = fields_values.get(key);
 
                 if (obj instanceof String) {
                     String str = (String) obj;
                     if (str == null || str.equals("")) {
                         str = "~";
                     }
 
                     s.setString(i, str);
                     sb3.append(str);
                     sb3.append(" ");
                 }
 
                 if (obj instanceof Integer) {
                     s.setInt(i, (Integer) obj);
                     sb3.append((Integer) obj);
                     sb3.append(" ");
                 }
 
                 if (obj instanceof Boolean) {
                     s.setString(i, format_bool((Boolean) obj));
                     sb3.append(format_bool((Boolean) obj));
                     sb3.append(" ");
                 }
 
                 if (obj instanceof Double) {
                     s.setDouble(i, (Double) obj);
                     sb3.append((Double) obj);
                     sb3.append(" ");
                 }
 
                 if (obj instanceof Float) {
                     s.setDouble(i, (Float) obj);
                     sb3.append((Float) obj);
                     sb3.append(" ");
                 }
 
                 ++i;
             }
 
             //logger.info("values: " + sb3.toString());
 
             ResultSet rset = s.executeQuery();
 
             while (rset.next()) {
                 ids_vec.addElement(rset.getInt(1));
             }
 
             rset.close();
             s.close();
 
         } catch (SQLException ex) {
             logger.severe("SQL error with following query " + ex.getMessage());
             logger.severe("query:  " + sb.toString());
             logger.severe("warning: " + sb3.toString());
             throw ex;
         }
 
         return ids_vec;
     }
 
     /**
      * For HLT XML upload.  Need to link elements to a component
      *
      * @param setupID id for the setup record
      * @param alias
      * @param name
      * @return id of the hlt component
      */
     public int findComponent(int setupID, String alias, String name) {
         int hcp_id = -1;
 
         String query = "SELECT HCP_ID FROM HLT_COMPONENT, HLT_ST_TO_CP ";
         query += "WHERE HLT_COMPONENT.HCP_ALIAS=? ";
         if (name != null) {
             query += " AND HLT_ST_TO_CP.HST2CP_COMPONENT_ID=HLT_COMPONENT.HCP_ID AND HLT_ST_TO_CP.HST2CP_SETUP_ID=?";
         }
 
         try {
             logger.finer("findcom " + query);
             PreparedStatement ps = getConnection().prepareStatement(query);
             ps.setString(1, alias);
             if (name != null) {
                 ps.setInt(2, setupID);
             }
             ResultSet rset = ps.executeQuery();
             while (rset.next()) {
                 hcp_id = rset.getInt(1);
             }
             rset.close();
             ps.close();
         } catch (SQLException e) {
             e.printStackTrace();
         }
         logger.finer("-------------- found comp " + hcp_id);
         return hcp_id;
     }
 
     /**
      * For HLT XML upload.  Need to link elements to a component
      *
      * @param setupID id for the setup record
      * @param aliases Aliases of the HLT_COMPONENT
      * @param names Names of the HLT_COMPONENT
      * @return id of the hlt component
      */
     public Vector<Integer> findComponents(int setupID, Vector<String> aliases, Vector<String> names) throws SQLException {
         Vector<Integer> hcp_ids = new Vector<Integer>();
 
         int nAliases = aliases.size();
         int nNames = names.size();
         int nMaxNA = (nNames > nAliases) ? nNames : nAliases;
         // Test the input variables
         // Mirek: make simpler condition
         if (!(nNames == nAliases || nNames == 0 || nAliases == 0)) {
             return hcp_ids;
         }
         // Go through components one by one to have the name and aliases id's sorted
         for (int iComponent = 0; iComponent < nMaxNA; iComponent++) {
             String query = "SELECT DISTINCT HLT_COMPONENT.HCP_ID FROM HLT_SETUP ";
             query += "JOIN HLT_ST_TO_CP ON (HLT_ST_TO_CP.HST2CP_SETUP_ID = HLT_SETUP.HST_ID) ";
             query += "JOIN HLT_COMPONENT ON (HLT_ST_TO_CP.HST2CP_COMPONENT_ID = HLT_COMPONENT.HCP_ID) ";
             query += "WHERE HLT_SETUP.HST_ID=? ";
             query += " AND ";
 
             if (iComponent < nAliases) {
                 query += " HLT_COMPONENT.HCP_ALIAS=? ";
             }
 
             if (iComponent < nAliases && iComponent < nNames) {
                 query += " AND ";
             }
             if (iComponent < nNames) {
                 query += " HLT_COMPONENT.HCP_NAME=? ";
             }
 
             int hcp_id = -1;
             int nhcp_ids = 0;
 
             logger.finer("findComponents " + query);
             query = fix_schema_name(query);
             PreparedStatement ps = getConnection().prepareStatement(query);
 
             ps.setInt(1, setupID);
             int count = 2;
 
             if (iComponent < nAliases) {
                 ps.setString(count, aliases.get(iComponent));
                 ++count;
             }
 
             if (iComponent < nNames) {
                 ps.setString(count, names.get(iComponent));
             }
 
             ResultSet rset = ps.executeQuery();
             nhcp_ids = 0;
             while (rset.next()) {
                 hcp_id = rset.getInt(1);
                 nhcp_ids++;
             }
 
             rset.close();
             ps.close();
 
 
             if (nhcp_ids != 1) {
                 if (nhcp_ids == 0) {
                     // No Component found
                     logger.finer("No Matching Component found");
                     hcp_ids.addElement(-1);
                 } else {
                     // Multiple Components found - that should not happen
                     logger.finer("Multiple Components found:\t" + nhcp_ids);
                     hcp_ids.addElement(-2);
                 }
             } else {
                 // everything OK
                 hcp_ids.add(hcp_id);
             }
         }
 
         if (hcp_ids.size() != nMaxNA) {
             logger.finer("Output not matching the input:\t" + "Size of Input: " + nMaxNA + "\tSize of Output: " + hcp_ids.size());
         }
 
         return hcp_ids;
     }
 
     public TreeMap<Integer, Integer> getConstrainedItemList(String query, int id) {
         TreeMap<Integer, Integer> results = new TreeMap<Integer, Integer>();
         query = ConnectionManager.getInstance().fix_schema_name(query);
 
         try {
             PreparedStatement ps = getConnection().prepareStatement(query);
             ps.setInt(1, id);
             ResultSet rset = ps.executeQuery();
 
             while (rset.next()) {
                 results.put(rset.getInt(1), rset.getInt(2));
             }
 
             rset.close();
             ps.close();
         } catch (SQLException e) {
             e.printStackTrace();
         }
 
         return results;
     }
 
     /**
      * Get all Items used
      *
      * @param table_name
      * @param table_prefix
      * @param base_name
      * @param reference_name
      * @return Vector of Item's ID's
      */
     public Vector<Integer> get_ItemList(String table_name, String table_prefix, String base_name, String reference_name, int baseID) {
         // Don't use distinct there can be multiple relations
 
         Connection connection = conn;
         
         StringBuffer query = new StringBuffer();
         query.append("SELECT ");
         query.append(reference_name);
         query.append("_ID FROM ");
         query.append(table_name);
         query.append(" WHERE ");
         query.append(base_name);
         query.append("_ID =?");
         query.append(" ORDER BY ");
         query.append(reference_name + "_ID ASC");
 
         //         if (query.toString().contains("HLT_TC_TO_TS")) {
         //             System.out.println("ConnectionManager.get_ItemList: " + query);
         //         }
 
         Vector<Integer> ids_vec = new Vector<Integer>();
 
         String query2 = fix_schema_name(query.toString());
 
         try {
             PreparedStatement ps = connection.prepareStatement(query2);
             ps.setInt(1, baseID);
             ResultSet rset = ps.executeQuery();
 
             while (rset.next()) {
                 ids_vec.add(rset.getInt(1));
             }
 
             rset.close();
             ps.close();
         } catch (SQLException e) {
             e.printStackTrace();
         }
         return ids_vec;
     }
 
     public void dblog(String message) {
         String values = "";
         if (getInitInfo().getdbTechnology().contains("MySQL") ||
                 getInitInfo().getdbTechnology().contains("SQLite")) {
             values = "?,?,NULL";
         } else {
             values = "?,?,sysdate";
         }
 
         String query = "INSERT INTO TRIGGER_LOG (TLOG_USERNAME, TLOG_MESSAGE, TLOG_MODIFIED_TIME) VALUES (" + values + ")";
         query = fix_schema_name(query);
         try {
             PreparedStatement ps = getConnection().prepareStatement(query);
             ps.setString(1, getInitInfo().getSystemUsername());
             ps.setString(2, message);
             ps.executeUpdate();
             ps.close();
         } catch (SQLException e) {
             e.printStackTrace();
         }
     }
 
     /**
      * Here we look into a connection table for an existing mapping
      * for instance:
      * If there is a trigger_menu with exactly the chains (a,b,c) and we
      * want to add a trigger_menu with exactly those three
      * chains (meaning exactly the same chain_IDs), then we don't have to
      * add a new trigger_menu
      * Since in the 'connection tables' the mapping (e.g.
      *
      * @param id_vec
      * @param id2_vec 
      * @return Vector of matching ID's of the base name, Remember there can be more things referring to same Items with different own parameters
      */
     public Vector<Integer> matchingConnectionSignatureIDs(Vector<Integer> id_vec, Vector<Integer> id2_vec) throws SQLException {
 
         Collections.sort(id2_vec);
 
         String table_name = "HLT_TS_TO_TE";
         String base_name = "HTS2TE_TRIGGER_SIGNATURE";
         String reference_name = "HTS2TE_TRIGGER_ELEMENT";
 
         Vector<Integer> baseIDs = new Vector<Integer>(); // Output vector with base ID with matching references
 
         if (id_vec == null || id_vec.size() == 0) {
             return baseIDs;
         }
         Vector<Integer> idV = new Vector<Integer>();
         Vector<Integer> ecV = new Vector<Integer>();
 
         Integer ec = 0;
         for (Integer id : id_vec) {
             idV.addElement(id);
             ecV.addElement(ec++);
         }
         for (Integer id : id2_vec) {
             idV.addElement(id);
             ecV.addElement(-1);
         }
 
         StringBuffer buffer = new StringBuffer();
         buffer.append("SELECT DISTINCT HTS2TE_TRIGGER_SIGNATURE_ID FROM HLT_TS_TO_TE WHERE ");
         Integer nRef = idV.size();
         for (int i = 0; i < nRef; i++) {
             buffer.append("(HTS2TE_TRIGGER_ELEMENT_ID=? AND HTS2TE_ELEMENT_COUNTER=?)");
             if (i < nRef - 1) {
                 buffer.append(" OR ");
             }
         }
 
 
         String query = fix_schema_name(buffer.toString());
 
         logger.finer("MCL: " + query);
 
         Vector<Integer> base_ids = new Vector<Integer>();
         try {
             PreparedStatement ps = getConnection().prepareStatement(query);
             for (int i = 0; i < nRef; i++) {
                 ps.setInt(2 * i + 1, idV.get(i));
                 ps.setInt(2 * i + 2, ecV.get(i));
             }
             ResultSet rset = ps.executeQuery();
 
             while (rset.next()) {
                 base_ids.add(rset.getInt(1));
             }
             rset.close();
             ps.close();
         } catch (SQLException e) {
             logger.warning("Error finding base ID's");
             e.printStackTrace();
         }
 
         int nbase_ids = base_ids.size();
         if (nbase_ids == 0) {
             // Base ID's is still empty and there was no result searching reference ID's
             return baseIDs;
         }
         //      Now loop over found base ID's and compare found references with the sorted input sorted_ref_vec
         Collections.sort(base_ids);
 
         // Do not use DISTINCT we need all the ID some of them can be doubled
         buffer = new StringBuffer();
         buffer.append("SELECT HTS2TE_TRIGGER_ELEMENT_ID, HTS2TE_ELEMENT_COUNTER FROM ");
         buffer.append(table_name);
         buffer.append(" WHERE ");
         buffer.append(base_name);
         buffer.append("_ID=?");
         //buffer.append(base_ids.get(ibase_id));
         buffer.append(" ORDER BY HTS2TE_ELEMENT_COUNTER, HTS2TE_TRIGGER_ELEMENT_ID");
 
         query = fix_schema_name(buffer.toString());
 
 
         logger.fine("   MCL 2: " + query);
 
         PreparedStatement ps = null;
 
         try {
             ps = getConnection().prepareStatement(query);
             for (int ibase_id = 0; ibase_id < nbase_ids; ibase_id++) {
 
                 Vector<Integer> refIDs = new Vector<Integer>();
                 Vector<Integer> refIDsNoEC = new Vector<Integer>();
                 // Address the database
 
                 ps.setInt(1, base_ids.get(ibase_id));
                 ResultSet rset = ps.executeQuery();
 
                 while (rset.next()) {
                     if (rset.getInt(2) < 0 || rset.getInt(2) > 99) {
                         refIDsNoEC.add(rset.getInt(1)); // the floating TE's
 
                     } else {
                         refIDs.add(rset.getInt(1)); // the TE's that are listed in the signature
 
                     }
                 }
 
                 // In case of matching references add base id into the output list
                 if (refIDs.equals(id_vec) && refIDsNoEC.equals(id2_vec)) {
                     baseIDs.add(base_ids.get(ibase_id));
                 }
                 rset.close();
             }
             ps.close();
         } catch (SQLException e) {
             logger.warning("Error preparing statement");
             e.printStackTrace();
         }
 
         return baseIDs;
     }
 
     /**
      * Here we look into a connection table for an existing mapping
      * for instance:
      * If there is a trigger_menu with exactly the chains (a,b,c) and we
      * want to add a trigger_menu with exactly those three
      * chains (meaning exactly the same chain_IDs), then we don't have to
      * add a new trigger_menu
      * Since in the 'connection tables' the mapping (e.g.
      *
      * @param componentIDs Vector of component IDs sorted accordingly to the algorithm counter
      * @return Vector of matching ID's of the base name, Remember there can be more things referring to same Items with different own parameters
      */
     public Vector<Integer> matchingElementIDs(Vector<Integer> componentIDs) {
 
         String table_name = "HLT_TE_TO_CP";
         String base_name = "HTE2CP_TRIGGER_ELEMENT";
         String reference_name = "HTE2CP_COMPONENT";
 
         Vector<Integer> baseIDs = new Vector<Integer>(); // Output vector with base ID with matching references
 
         if (componentIDs == null || componentIDs.size() == 0) {
             return baseIDs;
         }
 
 
         StringBuffer queryList = new StringBuffer();
 
         int nReferences = componentIDs.size();
 
         for (int i = 0; i < nReferences; i++) {
             queryList.append("(HTE2CP_COMPONENT_ID=" + componentIDs.get(i) + " AND HTE2CP_ALGORITHM_COUNTER=" + i + ")");
             if (i < nReferences - 1) {
                 queryList.append(" OR ");
             }
         }
 
         String query = "SELECT DISTINCT " + base_name + "_ID FROM " + table_name + " WHERE " + queryList.toString();
 
         Vector<Integer> base_ids = new Vector<Integer>();
 
         try {
             //    System.out.println("MCL : " + query);
             Statement stmt2 = getConnection().createStatement();
             ResultSet rset = stmt2.executeQuery(query);
 
             while (rset.next()) {
                 base_ids.add(rset.getInt(1));
             }
             rset.close();
             stmt2.close();
         } catch (SQLException e) {
             logger.finer("Error finding base ID's");
             e.printStackTrace();
         }
 
         int nbase_ids = base_ids.size();
         if (nbase_ids <= 0) {
             // Base ID's is still empty and there was no result searching reference ID's
             return baseIDs;
         }
         Collections.sort(base_ids);
         // Do not use DISTINCT we need all the ID some of them can be doubled
         query = "SELECT " + reference_name + "_ID FROM " + table_name;
         query += " WHERE " + base_name + "_ID=? ORDER BY HTE2CP_ALGORITHM_COUNTER";
 
         try {
             PreparedStatement ps = getConnection().prepareStatement(query);
             // Now loop over found base ID's and compare found references
             for (int ibase_id = 0; ibase_id < nbase_ids; ibase_id++) {
 
                 // Address the database
                 ps.setInt(1, base_ids.get(ibase_id));
                 ResultSet rset = ps.executeQuery();
 
                 Vector<Integer> refIDs = new Vector<Integer>();
                 while (rset.next()) {
                     refIDs.add(rset.getInt(1));
                 }
 
                 // In case of matching references add base id into the output list
                 if (refIDs.equals(componentIDs)) {
                     baseIDs.add(base_ids.get(ibase_id));
                 }
                 rset.close();
             }
             ps.close();
         } catch (SQLException e) {
             logger.finer("Error finding children");
             e.printStackTrace();
         }
 
         return baseIDs;
     }
 
     public String fix_schema_name(String query) {
 
         String[] tableNames = TableNames.allTableName;
 
         if (getInitInfo().getdbTechnology().equals("MySQL") || getInitInfo().getTableName().equals("")) {
             return query;
         }
 
         String prefix = getInitInfo().getTableName().toUpperCase() + ".";
         
         for (int i = 0; i < tableNames.length; ++i) {
             query = query.replace(" " + tableNames[i], " " + prefix + tableNames[i]);
         }
 
         return query;
     }
 
     private int getSchemaVersion() throws SQLException {
         int version = -1;
         String query = "SELECT MAX(TS_ID) FROM TRIGGER_SCHEMA";
         query = fix_schema_name(query);
 
         //System.out.println(query);
 
         PreparedStatement ps = getConnection().prepareStatement(query);
         ResultSet rset = ps.executeQuery();
 
         while (rset.next()) {
             version = rset.getInt(1);
         }
 
         rset.close();
         ps.close();
         return version;
     }
     
     static boolean loadSchema2SqliteFile( String fname, ConnectionManager instance ) throws Exception {
         
         //see if the schema is in the jar file
         URL url1 = null;
         URL url2 = null;
         try{
             url1 = instance.getClass().getClassLoader().getResource("DBstartup");
             url1.getPath();
             url2 = instance.getClass().getClassLoader().getResource("sql/combined_schema.sql");
             url2.getPath();
         }catch(Exception ex){
             JOptionPane.showMessageDialog(null,"The jar file does not contain the schema.\n" +
                                                  "You need to make the TriggerTool in cmt",
                                                  "Schema missing", JOptionPane.ERROR_MESSAGE);
             return false;
         }
         
         //make DBStartup in the directory if not there already
         boolean DBsu_exists = (new File("DBstartup")).exists();
         if(!DBsu_exists){
             File DBstartup_file = new File("DBstartup");
             FileWriter os_DBstartup  = new FileWriter(DBstartup_file);
             InputStream is_DBstartup  = instance.getClass().getClassLoader().getResourceAsStream("DBstartup");
             BufferedReader in_DBstartup  = new BufferedReader(new InputStreamReader(is_DBstartup));
             String line_DB="";
             while((line_DB=in_DBstartup.readLine())!=null){
                 os_DBstartup.write(line_DB+"\n");
             }
             os_DBstartup.close();
             //DBstartup_file.setExecutable(true); 1.6 only!
         }
         
         //make combined_schema.sql in the directory
         boolean CS_exists = (new File("sql/combined_schema.sql")).exists();
         boolean dir_exists = (new File("sql")).exists();
         if(!CS_exists&&!dir_exists){
             (new File("sql")).mkdir();
             File CombSchema_file = new File("sql/combined_schema.sql");
             FileWriter os_CombSchema = new FileWriter(CombSchema_file);
             InputStream is_CombSchema = instance.getClass().getClassLoader().getResourceAsStream("sql/combined_schema.sql");
             BufferedReader in_CombSchema = new BufferedReader(new InputStreamReader(is_CombSchema));
             String line_CS="";
             while((line_CS=in_CombSchema.readLine())!=null){
                 os_CombSchema.write(line_CS+"\n");
             }
             os_CombSchema.close();
             //CombSchema_file.setExecutable(true); 1.6 only!
         }
         
         //now run dbstartup script which must now exist
         Runtime rt = Runtime.getRuntime();
         String args = "-t sqlite -f " + fname;
         //logger.info("DBStartUp args are: " + args);
         rt.exec("chmod 777 DBstartup");
         rt.exec("chmod 777 sql/combined_schema.sql");
         Process pr = rt.exec("./DBstartup " + args);
         
         pr.waitFor();
     
         return true;
     }
 }

Due to the LXR bug, the updates fail sometimes to remove references to deleted files. The Saturday's full rebuilds fix these problems
This page was automatically generated by the LXR engine.