001    /**
002     * Copyright (c) 2003 Daffodil Software Ltd all rights reserved,
003     * Modifications Copyright (c) 2008 Regiscope Digital Imaging Co, LLC, All rights reserved.
004     * This program is free software; you can redistribute it and/or modify
005     * it under the terms of version 2 of the GNU General Public License as
006     * published by the Free Software Foundation.
007     * There are special exceptions to the terms and conditions of the GPL
008     * as it is applied to this software. See the GNU General Public License for more details.
009     *
010     * This program is distributed in the hope that it will be useful,
011     * but WITHOUT ANY WARRANTY; without even the implied warranty of
012     * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
013     * GNU General Public License for more details.
014     *
015     * You should have received a copy of the GNU General Public License
016     * along with this program; if not, write to the Free Software
017     * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
018     */
019    
020    package org.dbreplicator.replication.DBHandler;
021    
022    import java.sql.*;
023    import java.util.*;
024    
025    import org.dbreplicator.replication.*;
026    import org.dbreplicator.replication.column.*;
027    import org.apache.log4j.Logger;
028    
029    /**
030     * In replicator because of the feature of multidatabase synchronization, It has
031     * become very much necessary to handle different databases deifferently.
032     * By identifying the database from Utility class a proper databasehandler is returned,
033     * And every handler extends this abstract class. So the abstract methods declared
034     * in it are defined accordingly in different handlers.
035     * This Class is solely defined for table creations and for appropriate column
036     * datatype sequence generation.
037     *
038     */
039    
040    public abstract class AbstractDataBaseHandler   {
041      protected final static String publication_TableName = "Rep_Publications";
042      protected final static String subscription_TableName = "Rep_Subscriptions";
043      protected final static String bookmark_TableName = "Rep_BookMarkTable";
044      protected final static String rep_TableName = "Rep_RepTable";
045      protected final static String log_Table = "Rep_LogTable";
046      protected final static String Schedule_TableName = "Rep_ScheduleTable";
047      public final static String ignoredColumns_Table = "Rep_IgnoredColumnsTable";
048      public final static String trackReplicationTablesUpdation_Table = "Rep_TrackRepTabUpdation";
049      public final static String trackPrimaryKeyUpdation_Table = "Rep_trackPrimaryKey";
050    
051      protected ConnectionPool connectionPool;
052      protected String localServerName;
053      protected static int vendorType = -1;
054      protected static Logger log =Logger.getLogger(AbstractDataBaseHandler.class.getName());
055      /**
056       * Creates Log Table and Rep Table.
057       * @param pubName
058       * @throws SQLException
059       * @throws RepException
060       */
061    
062      abstract protected void createSuperLogTable(String pubName) throws SQLException, RepException;
063    
064      abstract protected void createRepTable(String pubName) throws  SQLException, RepException;
065    
066      public void CreateSequenceOnLogTable(String pubSubName) throws SQLException,
067          RepException {
068        // This method os currently implemented by OracleHandler
069      }
070    
071      public void CreateSequenceOnRepTable(String pubSubName) throws SQLException,
072          RepException {
073        // This method os currently implemented by OracleHandler
074      }
075    
076      public void CreateSequenceOnShadowTable(String pubSubName) throws
077          SQLException, RepException {
078        // This method os currently implemented by OracleHandler
079      }
080    
081      /**
082       * This is a very important function which sets the parameter (instance of
083       * Class TypeInfo) with the corresponding typeName of the sqlType for different
084       * databases. So that we can make corresponding queries.
085       * @param typeInfo
086       * @param rs
087       * @throws RepException
088       * @throws SQLException
089       */
090    
091      abstract public void setTypeInfo(TypeInfo typeInfo, ResultSet rs) throws
092          RepException, SQLException;
093      /**
094       * This method is added for checking of schema.If schema exist in database
095       * then it return true else false.All the database support the schema except
096       * My SQL and Firebird.
097       * @return boolean
098       */
099    
100      abstract public boolean isSchemaSupported();
101      abstract public boolean isDataTypeOptionalSizeSupported(TypeInfo typeInfo);
102    
103      /**
104       * According to the sqlType, different column's for different databases are handled
105       * saperately. It returns the object of AbstractColumnObject which is extended
106       * by each type of column Object classes. This fundamental is used basically for
107       * handeling Blob and Clob type of columns.
108       * @param typeInfo
109       * @throws RepException
110       */
111    
112      abstract public AbstractColumnObject getColumnObject(TypeInfo typeInfo) throws RepException;
113    
114      /**
115       * Creates Shadow Table as per the database.
116       * @param pubsubName
117       * @param tableName
118       * @param allColSequence
119       * @throws RepException
120       */
121    
122      abstract public void createShadowTable(String pubsubName, String tableName,  String allColSequence,String[] primaryColumns) throws RepException;
123    
124      /**
125       * Creates ShadowTable triggers on different replication tables.
126       * @param pubName
127       * @param tableName
128       * @param colNameDataType
129       * @param primCols
130       * @throws RepException
131       */
132    
133      abstract public void createShadowTableTriggers(String pubName,
134                                                     String tableName,
135                                                     ArrayList colNameDataType,
136                                                     String[] primCols) throws RepException;
137    
138      abstract public boolean isPrimaryKeyException(SQLException ex) throws SQLException;
139    
140      /**
141       * It checks from the  sqlType values whether the datatype is optional size
142       * supported or not.
143       * @param connection Connection
144       * @throws RepException
145       */
146    
147      public void setDefaultSchema(Connection connection) throws RepException {
148    // no need to implement, currently daffodildb implements its
149      }
150    
151      /**
152       * This method is implemented in child classes if precision exceed the
153       * maximum precision of the data type
154       * @param columnSize int
155       * @throws RepException
156       * @return int
157       */
158      public int getAppropriatePrecision(int columnSize, String datatypeName) {
159        return columnSize;
160      }
161    
162      public void createRemoteSystemTables(String pubName) throws RepException {
163        try {
164          createPublicationTable(pubName);
165        }
166        catch (RepException ex) {
167          throw ex;
168        }
169        catch (SQLException ex) {
170          // Ignore the Exception, System Table alredy exists.
171        }
172        try {
173          createBookMarkTable(pubName);
174        }
175        catch (RepException ex1) {
176          throw ex1;
177        }
178        catch (SQLException ex1) {
179          // Ignore the Exception, System Table alredy exists.
180        }
181        try {
182          createSuperLogTable(pubName);
183        }
184        catch (RepException ex2) {
185          throw ex2;
186        }
187        catch (SQLException ex2) {
188          // Ignore the Exception, System Table alredy exists.
189        }
190    
191        try {
192          CreateSequenceOnLogTable(pubName);
193        }
194        catch (RepException ex4) {
195          throw ex4;
196        }
197        catch (SQLException ex4) {
198        }
199    
200        try {
201          createRepTable(pubName);
202        }
203        catch (RepException ex3) {
204          throw ex3;
205        }
206        catch (SQLException ex3) {
207          // Ignore the Exception, System Table alredy exists.
208        }
209        try {
210          CreateSequenceOnRepTable(pubName);
211        }
212        catch (RepException ex5) {
213          throw ex5;
214        }
215        catch (SQLException ex5) {
216        }
217    
218        try {
219          createIgnoredColumnsTable(pubName);
220        }
221        catch (RepException ex6) {
222          throw ex6;
223        }
224        catch (SQLException ex6) {
225        }
226    
227        // work for TrackReplicationTablesUpdation table
228        try {
229          createTrackReplicationTablesUpdationTable(pubName);
230        }
231        catch (RepException ex5) {
232          throw ex5;
233        }
234        catch (SQLException ex5) {
235        }
236        // trigger for track table
237        try {
238          createTriggerForTrackReplicationTablesUpdationTable(pubName);
239        }
240        catch (RepException ex5) {
241          throw ex5;
242        }
243        catch (SQLException ex5) {
244        }
245    
246    
247      }
248    
249      public int getvendorName() {
250        return vendorType;
251      }
252    
253      public void createClientSystemTables(String subName) throws RepException {
254        try {
255          createSubscriptionTable(subName);
256        }
257        catch (RepException ex) {
258          throw ex;
259        }
260        catch (SQLException ex) {
261        RepConstants.writeERROR_FILE(ex);
262          // Ignore the Exception, System Table alredy exists.
263        }
264        try {
265          createBookMarkTable(subName);
266        }
267        catch (RepException ex1) {
268          throw ex1;
269        }
270        catch (SQLException ex1) {
271          // Ignore the Exception, System Table alredy exists.
272        }
273        try {
274          createSuperLogTable(subName);
275        }
276        catch (RepException ex2) {
277          throw ex2;
278        }
279        catch (SQLException ex2) {
280          // Ignore the Exception, System Table alredy exists.
281        }
282    
283        try {
284          CreateSequenceOnLogTable(subName);
285        }
286        catch (RepException ex4) {
287          throw ex4;
288        }
289        catch (SQLException ex4) {}
290        try {
291          createRepTable(subName);
292        }
293        catch (RepException ex3) {
294          throw ex3;
295        }
296        catch (SQLException ex3) {
297          // Ignore the Exception, System Table alredy exists.
298        }
299        try {
300          CreateSequenceOnRepTable(subName);
301        }
302        catch (RepException ex5) {
303          throw ex5;
304        }
305        catch (SQLException ex5) {}
306        try {
307          createScheduleTable(subName);
308        }
309        catch (RepException ex) {
310          throw ex;
311        }
312      catch (SQLException ex5) {
313      }
314    
315      try {
316        createIgnoredColumnsTable(subName);
317      }
318      catch (RepException ex6) {
319        throw ex6;
320      }
321      catch (SQLException ex6) {
322      }
323    
324      // work for TrackReplicationTablesUpdation table
325      try {
326        createTrackReplicationTablesUpdationTable(subName);
327      }
328      catch (RepException ex5) {
329        throw ex5;
330        }
331      catch (SQLException ex5) {
332      }
333      // trigger for track table
334      try {
335        createTriggerForTrackReplicationTablesUpdationTable(subName);
336      }
337      catch (RepException ex5) {
338        throw ex5;
339      }
340      catch (SQLException ex5) {
341      }
342    }
343    
344    
345    
346      protected void createPublicationTable(String pubName) throws RepException, SQLException {
347        StringBuffer pubsTableQuery = new StringBuffer();
348        pubsTableQuery.append(" Create Table ")
349            .append(getPublicationTableName())
350            .append(" ( " + RepConstants.publication_pubName1 + " varchar(255) , " +
351                    RepConstants.publication_conflictResolver2 + " varchar(255) , ")
352            .append(" " + RepConstants.publication_serverName3 +
353                    " varchar (255) , Primary Key (" +
354                    RepConstants.publication_pubName1 + ") ) ");
355        runDDL(pubName, pubsTableQuery.toString());
356      }
357    
358      protected void createSubscriptionTable(String pubName) throws RepException,
359          SQLException {
360        String subsTableQuery = " Create Table  "
361            + getSubscriptionTableName()
362            + " ( " + RepConstants.subscription_subName1 + " varchar(255) , "
363            + "   " + RepConstants.subscription_pubName2 + " varchar(255)  , "
364            + "   " + RepConstants.subscription_conflictResolver3 +
365            " varchar(255) , "
366            + "   " + RepConstants.subscription_serverName4 + " varchar (255) , "
367            + "   Primary Key (" + RepConstants.subscription_subName1 + ") ) ";
368        runDDL(pubName, subsTableQuery);
369      }
370    
371      protected void createBookMarkTable(String pubName) throws SQLException,
372          RepException {
373        StringBuffer bookmarkTableQuery = new StringBuffer();
374        bookmarkTableQuery.append(" Create Table ")
375            .append(getBookMarkTableName())
376            .append(" ( " + RepConstants.bookmark_LocalName1 + " varchar(255) , " +
377                    RepConstants.bookmark_RemoteName2 + " varchar(255) , ")
378            .append(" " + RepConstants.bookmark_TableName3 + " varchar(255) , " +
379                    RepConstants.bookmark_lastSyncId4 + " Long , ")
380            .append(" " + RepConstants.bookmark_ConisderedId5 +
381                    " Long ,"+RepConstants.bookmark_IsDeletedTable+" char(1) default 'N' , Primary Key (" + RepConstants.bookmark_LocalName1 +
382                    ", " + RepConstants.bookmark_RemoteName2 + ", " +
383                    RepConstants.bookmark_TableName3 + ") ) ");
384        runDDL(pubName, bookmarkTableQuery.toString());
385      }
386    
387      protected void createScheduleTable(String subName) throws SQLException,
388          RepException {
389        StringBuffer ScheduleTableQuery = new StringBuffer();
390        ScheduleTableQuery.append(" Create Table ")
391            .append(getScheduleTableName())
392            .append(" ( " + RepConstants.schedule_Name + " varchar(255) , " +
393                    RepConstants.subscription_subName1 + " varchar(255) unique , ")
394            .append("  " + RepConstants.schedule_type + " varchar(255) , ")
395            .append(" " + RepConstants.publication_serverName3 + " varchar (255) ," +
396                    RepConstants.publication_portNo + " varchar(255) ,")
397            .append(" " + RepConstants.recurrence_type + " varchar(255) , " +
398                    RepConstants.replication_type + " varchar(255) ,")
399            .append(" " + RepConstants.schedule_time + " Long , ")
400            .append(" " + RepConstants.schedule_counter + " Long , Primary Key (" +
401                    RepConstants.schedule_Name + " , " +
402                    RepConstants.subscription_subName1 + ") ) ");
403        runDDL(subName, ScheduleTableQuery.toString());
404      }
405    
406      void runDDL(String pubsubName, String query) throws SQLException, RepException {
407        Connection connection = connectionPool.getConnection(pubsubName);
408        Statement stt = connection.createStatement();
409        try {
410          log.debug(query);
411    //System.out.println(" ***********************************************************************************");
412    //System.out.println("                                                                                 ");
413    //System.out.println(" query ="+query);
414    //System.out.println("                                                                                 ");
415    //System.out.println(" ***********************************************************************************");
416          stt.execute(query);
417        log.info("Query executed "+query);
418    //System.out.println(" QUERY EXECUTED SUCCESSFULLY ");
419    
420        }
421        finally {
422              //bjt - don't run removeSubPubFromMap because it sets connection to null and
423              //forces connection pooling to create a new connection.  This means when
424              //creating a new publication the number of open database connections jumps to
425              //something like 2x number of published tables.
426          //connectionPool.removeSubPubFromMap(pubsubName);
427          if(stt!=null)
428        stt.close();
429            connectionPool.returnConnection(connection);
430      }
431      }
432    
433      /**
434        * This method create the main table, its corresponding shadow table and
435        * triggers in the database. If main table already created in the database,
436        * then it get existing table query. If table structure is same then it
437        * subscribe the table in database otherwise it thorw the exception table
438        * already exist with different structure the database.
439        * @param subName String
440        * @param pubTableQueries String[]
441        * @param primCols HashMap
442        * @param pubVendorType int
443        * @throws RepException
444        * @throws SQLException
445        */
446       public void createSubscribedTablesTriggersAndShadowTables(String subName,
447        String[] pubTableQueries,String[] alterTableAddFKQueries,HashMap primCols, int pubVendorType,ArrayList repTables) throws RepException, SQLException {
448         Connection connection = connectionPool.getConnection(subName);
449         Statement stt = connection.createStatement();
450         try
451         {
452         for (int i = 0; i < pubTableQueries.length; i++) {
453           try {
454             stt.execute(pubTableQueries[i]);
455           }
456           catch (SQLException ex) {
457        /*    SchemaQualifiedName sname = new SchemaQualifiedName(mdi, getTableName(pubTableQueries[i]));
458    //        MetaDataInfo mdi = new MetaDataInfo(connection);
459     >>>>>>> 1.67.6.4
460             String existing = null;
461             ArrayList foreignKeyQueries=new ArrayList();
462             try {
463               existing = mdi.getExistingTableQuery(this, sname, pubVendorType);
464               ArrayList alterTabelStatements = mdi.getForeignKeyConstraints(sname.getSchemaName(), sname.getTableName());
465    // System.out.println("Getting the query : " + alterTabelStatements);
466              if (alterTabelStatements != null && alterTabelStatements.size() > 0) {
467                foreignKeyQueries.addAll(alterTabelStatements);
468              }
469            //System.out.println(" existing Query =" + existing);
470               existing = existing.toLowerCase().replaceAll(" ", "");
471             }
472             catch (RepException ex1) {
473               if (ex1.getRepCode().equalsIgnoreCase("REP033")) {
474                 throw ex;
475               }
476             }
477    //System.out.println(" pubTableQueries[i] =" + pubTableQueries[i]);
478    
479         /*    if (pubTableQueries[i].toLowerCase().replaceAll(" ",
480                 "").indexOf(existing.toLowerCase()) != 0) {
481               throw new RepException("REP024", new Object[] {subName,
482                                      sname.toString()});
483             } */
484             // Ignore the exception if the same table exists with same colums and primary key
485           }
486         }
487         if(alterTableAddFKQueries != null){
488           for (int i = 0, size = alterTableAddFKQueries.length; i < size; i++) {
489             try {
490    //System.out.println(" alterTableQueries ="+alterTableAddFKQueries[i]);
491               stt.execute(alterTableAddFKQueries[i]);
492    
493             }
494             catch (SQLException ex) {
495               /** @todo
496                * if it is required ti check for existence of foreign constraint
497                * then we have to get the constraint name and check whether it is defined as required
498                * if not then give any other name to the constraint and add it or throw exception
499                *  */
500    //          ex.printStackTrace();
501               // Ignore the exception if the constraint is already made
502             }
503           }
504         }
505         } finally {
506           if(stt!=null)
507         stt.close();
508             connectionPool.returnConnection(connection);
509         }
510         // Just Get the Column Sequenes from Create Table Queries
511         // and create Shadow Table and Triggers on Shadow Table
512         try
513             {
514               connection = connectionPool.getConnection(subName);
515               MetaDataInfo mdi = Utility.getDatabaseMataData(connection);
516         for (int i = 0; i < pubTableQueries.length; i++) {
517           RepTable reptable=(RepTable)repTables.get(i);
518           if(reptable.getCreateShadowTable().equalsIgnoreCase(RepConstants.NO))
519             continue ;
520           String tableName = getTableName(pubTableQueries[i]);
521           SchemaQualifiedName sname = new SchemaQualifiedName(mdi,getTableName(pubTableQueries[i]));
522           String colSeqenceWithDataType = getColumnSequenceWithDataTypes(pubTableQueries[i]);
523           ArrayList colInfoList = getColumnNamesAndDataTypes(colSeqenceWithDataType.trim());
524               for (int cnt = 0; cnt < colInfoList.size(); cnt++) {
525                    log.debug("column "+cnt+" - "+( (ColumnsInfo) colInfoList.get(cnt)).getColumnName()+" "+((ColumnsInfo) colInfoList.get(cnt)).getDataTypeDeclaration());
526               }
527           String[] pcols = (String[]) ( (ArrayList) primCols.get(tableName.toLowerCase())).toArray(new String[0]);
528           String allColSequence = getShadowTableColumnDataTypeSequence(colInfoList,pcols,reptable);
529           createShadowTable(subName, sname.toString(), allColSequence,pcols);
530           makeProvisionForLOBDataTypes(colInfoList);
531           createShadowTableTriggers(subName, sname.toString(), colInfoList, pcols);
532           //createShadowTablesAndSubscriptionTableTriggers(subName,tableName,colSeqenceWithDataType,(ArrayList) primCols.get(tableName.toLowerCase()));
533         }
534       }
535             finally {
536                    connectionPool.returnConnection(connection);
537             }
538       }
539    
540    
541      /**
542       * Get the main table name from CreateTableQuery
543       * @param createTableQuery String
544       * @return String
545       */
546      String getTableName(String createTableQuery) {
547        int startIndex = createTableQuery.toLowerCase().indexOf("table") + 5 ;
548        int endIndex = createTableQuery.indexOf("(") - 1;
549        return createTableQuery.substring(startIndex, endIndex).trim();
550      }
551    
552      String getColumnSequenceWithDataTypes(String createTableQuery) {
553        int startIndex = createTableQuery.indexOf("(") + 1;
554        int endIndex = createTableQuery.toLowerCase().indexOf("primary key") - 1;
555        return createTableQuery.substring(startIndex, endIndex).trim();
556      }
557    
558      /**
559       * Return the list of shadowtabls cols in a string.
560       * @param nameTypeList ArrayList
561       * @param primCols String[]
562       * @return String
563       */
564      public String getShadowTableColumnDataTypeSequence(ArrayList nameTypeList, String[] primCols,RepTable reptable) {
565        StringBuffer toRet = new StringBuffer();
566        toRet.append(getColumnDataTypeSequence(nameTypeList,reptable))
567            .append(getPrimaryColumnDataTypeSequence(primCols,"rep_old_"));
568        return toRet.toString();
569      }
570    
571      protected String getPrimaryColumnDataTypeSequence(String[] primCols,String prefix) {
572        StringBuffer toRet = new StringBuffer();
573        for (int i = 0, size = primCols.length; i < size; i++) {
574          toRet.append(" , ").append(prefix).append(primCols[i]).append(" ")
575              .append(ColumnsInfo.getDataTypeDeclaration(primCols[i]));
576        }
577        return toRet.toString();
578      }
579    //we will also set allColumns list in Reptable in this method alongwith getting column datatype Sequence
580      private String getColumnDataTypeSequence(ArrayList colTypeMap,RepTable reptable) {
581        StringBuffer toRet = new StringBuffer();
582        String[] allColumns=new String[colTypeMap.size()];
583        for (int i = 0, size = colTypeMap.size(); i < size; i++) {
584          ColumnsInfo ci = (ColumnsInfo) colTypeMap.get(i);
585          String colName = ci.getColumnName();
586          allColumns[i]=colName;
587          toRet.append(" , ").append(colName).append(" ")
588              .append(ci.getDataTypeDeclaration());
589        }
590        reptable.setAllColumns(allColumns);
591        return toRet.toString();
592      }
593    
594      private String[] getColumnsNames(String columnSeqWithDataTypes) {
595        ArrayList colNames = new ArrayList();
596        do {
597          int index = columnSeqWithDataTypes.indexOf(" ");
598          int commaIndex = columnSeqWithDataTypes.indexOf(",");
599          if (index == -1 || commaIndex == -1) {
600            return (String[]) colNames.toArray(new String[0]);
601          }
602          colNames.add(columnSeqWithDataTypes.substring(0, index).trim());
603          columnSeqWithDataTypes = columnSeqWithDataTypes.substring(commaIndex + 1).trim();
604        }
605        while (true);
606      }
607    
608      // creating a columns sequence col1[,col2[,col3,...]] to be used for all shadow table trigger
609      String getColumnNameSequence(String[] columnNames, String prefix) {
610        StringBuffer sb = new StringBuffer();
611        for (int i = 0; i < columnNames.length; i++) {
612          sb.append(prefix).append(columnNames[i]).append(" , ");
613        }
614        return sb.toString();
615      }
616    
617      String[] getColumnNameWithOldOrNewPrefix(String[] columnNames, String prefix) {
618          String[] sb = new String[columnNames.length];
619          for (int i = 0; i < columnNames.length; i++) {
620            sb[i] = prefix+columnNames[i];
621          }
622          return sb;
623        }
624    
625    
626      public void setLocalServerName(String name0) {
627        localServerName = name0;
628      }
629    
630      public String getLocalServerName() {
631        return localServerName;
632      }
633    
634      /**
635       * Save the RepTable data which is a system table.
636       * It store the Publication/Subscription Name,
637       * its corresponding table with filter clause and conflict
638       * resolver.
639       */
640    
641      public String saveRepTableData(Connection connection, String pubsubName, RepTable repTable) throws SQLException, RepException {
642        StringBuffer sb = new StringBuffer();
643        PreparedStatement repPreparedStmt =null;
644        String filter = repTable.getFilterClause();
645        String[] ignoredColumns = repTable.getColumnsToBeIgnored();
646        if (filter != null) {
647          if (!filter.equalsIgnoreCase("")) {
648    
649    //            Rep_PubSub_Name
650    //            Rep_Table_Id
651    //            Rep_Table_Name
652    //            Rep_filter_clause
653    //            Rep_createshadowtable
654    //            Rep_cyclicdependency
655    //            Rep_conflict_resolver
656    
657    
658            sb.append("insert into ").append(getRepTableName())
659                .append(" ( ").append(RepConstants.repTable_pubsubName1).append(" , ")
660                .append(RepConstants.repTable_tableName2).append(" , ")
661                .append(RepConstants.repTable_filter_clause3).append(" , ")
662                .append(RepConstants.repTable_createshadowtable6).append(" , ")
663                .append(RepConstants.repTable_cyclicdependency7).append(" , ")
664                .append(RepConstants.repTable_conflict_resolver4).append(" ) ")
665                .append(" values ( ?,?,?,?,?,?) ");
666            repPreparedStmt = connection.prepareStatement(sb.toString());
667            repPreparedStmt.setString(1, pubsubName);
668            repPreparedStmt.setString(2, repTable.getSchemaQualifiedName().toString());
669            repPreparedStmt.setString(3, repTable.getFilterClause());
670            repPreparedStmt.setString(4, repTable.getCreateShadowTable());
671    //System.out.println("AbstractDataBaseHandler.saveRepTableData(connection, pubsubName, repTable) hashCode ="+repTable.hashCode() +"  cyclic dependency ="+repTable.getCyclicDependency() );
672            repPreparedStmt.setString(5, repTable.getCyclicDependency());
673            repPreparedStmt.setString(6, repTable.getConflictResolver());
674            repPreparedStmt.execute();
675             log.info("Query exceuted"+sb.toString());
676             log.info(pubsubName);
677             log.info(repTable.getSchemaQualifiedName().toString());
678             log.info(repTable.getFilterClause());
679             log.info(repTable.getConflictResolver());
680    
681          }
682        }
683        else {
684          sb.append("insert into ").append(getRepTableName()).append(" ( ")
685              .append(RepConstants.repTable_pubsubName1).append(" , ")
686              .append(RepConstants.repTable_tableName2).append(" , ")
687              .append(RepConstants.repTable_createshadowtable6).append(" , ")
688              .append(RepConstants.repTable_cyclicdependency7).append(" , ")
689              .append(RepConstants.repTable_conflict_resolver4).append(" ) ")
690              .append(" values(?,?,?,?,?)");
691          repPreparedStmt = connection.prepareStatement(sb.toString());
692          repPreparedStmt.setString(1, pubsubName);
693          repPreparedStmt.setString(2, repTable.getSchemaQualifiedName().toString());
694          repPreparedStmt.setString(3, repTable.getCreateShadowTable());
695          repPreparedStmt.setString(4, repTable.getCyclicDependency());
696          repPreparedStmt.setString(5, repTable.getConflictResolver());
697          repPreparedStmt.execute();
698        }
699        /** @todo statement has been closed */
700        repPreparedStmt.close();
701        if(ignoredColumns != null){
702          int tableId = getRepTableId(connection, pubsubName, repTable);
703          String queryToInsert="INSERT INTO "+getIgnoredColumns_Table()+ " VALUES ( ?, ?)";
704          PreparedStatement statementToInsert= connection.prepareStatement(queryToInsert);
705          for (int i = 0,size=ignoredColumns.length; i < size; i++) {
706            statementToInsert.setInt(1,tableId);
707            statementToInsert.setString(2,ignoredColumns[i]);
708            statementToInsert.execute();
709          }
710        }
711        return sb.toString();
712      }
713    
714      public void setIgnoredColumns(Connection connection,String pubsubName,RepTable repTable) throws RepException,SQLException{
715        int tableId = getRepTableId(connection, pubsubName, repTable);
716        ArrayList columnsToBeIgnored=new ArrayList();
717        StringBuffer sb = new StringBuffer();
718        sb.append("SELECT ")
719          .append(RepConstants.ignoredColumnsTable_ignoredcolumnName2)
720          .append(" FROM ")
721          .append(getIgnoredColumns_Table())
722          .append(" WHERE ")
723          .append(RepConstants.ignoredColumnsTable_tableId1)
724          .append(" = ")
725          .append(tableId);
726    //    String query="SELECT "+RepConstants.ignoredColumnsTable_ignoredcolumnName2+" FROM "+getIgnoredColumns_Table()+ " WHERE " +RepConstants.ignoredColumnsTable_tableId1 +" = "+tableId;
727        Statement statement=connection.createStatement();
728        ResultSet rs=statement.executeQuery(sb.toString());
729        while(rs.next()){
730          String columnName = rs.getString(1);
731    //System.out.println("Adding the column in the list :  " + columnName);
732          columnsToBeIgnored.add(columnName);
733        }
734        repTable.setColumnsToBeIgnored((String[])columnsToBeIgnored.toArray(new String[0]));
735        rs.close();
736        statement.close();
737      }
738    
739      private int getRepTableId(Connection connection, String pubsubName, RepTable repTable) throws SQLException {
740         StringBuffer sb =new StringBuffer();
741         sb.append("SELECT ")
742            .append(RepConstants.repTable_tableId2)
743            .append(" FROM ")
744            .append(RepConstants.rep_TableName)
745            .append(" WHERE ")
746            .append(RepConstants.repTable_pubsubName1)
747            .append(" = '")
748            .append(pubsubName)
749            .append("' AND ")
750            .append(RepConstants.repTable_tableName2)
751            .append(" = '")
752            .append(repTable.getSchemaQualifiedName().toString())
753            .append("'");
754    //     String queryToGetTableId="SELECT "+RepConstants.repTable_tableId2+" FROM "+RepConstants.rep_TableName+ " WHERE " + RepConstants.repTable_pubsubName1 +" = ?"+" AND  "+RepConstants.repTable_tableName2+" = ?" ;
755    //     PreparedStatement statement = connection.prepareStatement(queryToGetTableId);
756           Statement statement = connection.createStatement();
757    //     statement.setString(1,pubsubName);
758    //     statement.setString(2,repTable.getSchemaQualifiedName().toString());
759         ResultSet rs= statement.executeQuery(sb.toString());
760         rs.next();
761         int tableId=rs.getInt(1);
762         rs.close();
763         statement.close();
764         return tableId;
765       }
766    
767      protected Object getQuotedStringData(Object value) {
768        if (value == null) {
769          return value;
770        }
771        return "'" + value + "'";
772      }
773    
774      protected HashMap schemas;
775    
776      public boolean checkSchema(String schemaName) {
777        if (schemas == null) {
778          schemas = new HashMap();
779          schemas.put(schemaName.toLowerCase(), "");
780          return false;
781        }
782        if (schemas.containsKey(schemaName.toLowerCase())) {
783          return true;
784        }
785        schemas.put(schemaName.toLowerCase(), "");
786        return false;
787      }
788    
789      public void dropTriggersAndShadowTable(Connection connection, String table, String pubsubName) throws SQLException, RepException {
790        String tableName = table.substring(table.indexOf('.') + 1);
791        fireDropQuery(connection, " drop trigger " + RepConstants.getInsertTriggerName(table));
792        fireDropQuery(connection, " drop trigger " + RepConstants.getDeleteTriggerName(table));
793        fireDropQuery(connection, " drop trigger " + RepConstants.getUpdateTriggerName(table));
794        fireDropQuery(connection, " drop table  " + RepConstants.shadow_Table(table));
795        dropSequences(connection,RepConstants.seq_Name(RepConstants.shadow_Table(tableName)));
796        dropGenerators(connection,RepConstants.gen_Name(RepConstants.shadow_Table(tableName)));
797        fireDropQuery(connection, " delete from " + getLogTableName() + " where " +RepConstants.logTable_tableName2 + " = '" + table + "'");
798    
799      }
800    
801      public void fireDropQuery(Connection con, String query) throws SQLException {
802        Statement stt = con.createStatement();
803        try {
804          stt.execute(query);
805          log.info("query executed "+query);
806        }
807        catch (SQLException sqlEx) {
808          // Ignore the Exception
809        }
810        finally {
811          stt.close();
812        }
813      }
814    
815      /*public void writeSchemaName(OutputStreamWriter os, String schemaName) throws
816          java.io.IOException {
817        if (schemas == null) {
818          os.write("<SchemaName>");
819          os.write(schemaName);
820          os.write("</SchemaName>");
821          schemas = new HashMap();
822          schemas.put(schemaName.toLowerCase(), "");
823          return;
824        }
825        if (schemas.containsKey(schemaName.toLowerCase()))
826          return;
827        os.write("<SchemaName>");
828        os.write(schemaName);
829        os.write("</SchemaName>");
830         }*/
831    
832      public String updateDataType(String dataType0) {
833        return dataType0;
834      }
835    
836    //  Current this method is implemented by Daffodildb and PostgreSQL
837      public void createSchemas(String pubName, ArrayList schemas) throws SQLException, RepException {
838      }
839    
840      public void dropSequences(Connection con, String sequenceName) throws SQLException {
841      }
842    
843      public void makeProvisionForLOBDataTypes(ArrayList list) {
844      }
845    
846      public String getPublicationTableName() {
847        return publication_TableName;
848      }
849    
850      public String getSubscriptionTableName() {
851        return subscription_TableName;
852      }
853    
854      public String getRepTableName() {
855        return rep_TableName;
856      }
857    
858      public String getLogTableName() {
859        return log_Table;
860      }
861    
862      public String getBookMarkTableName() {
863        return bookmark_TableName;
864      }
865    
866      public String getScheduleTableName() {
867        return Schedule_TableName;
868      }
869    
870      public void setColumnPrecisionInTypeInfo(TypeInfo typeInfo, ResultSetMetaData rsmt, int columnIndex) throws SQLException {
871        // not implmented . child class sql server, daffodildb,and oracle implement it
872      }
873    
874      public ArrayList getColumnNamesAndDataTypes1(String columnSequence) {
875        // col1 datatyoe, colName datatype
876        ArrayList list = new ArrayList();
877        String lowerString ;
878        do {
879          lowerString = columnSequence.toLowerCase();
880          int startIndex = columnSequence.indexOf(" ");
881          int lastIndex = columnSequence.indexOf(" , ");
882    
883          int notNullIndex = lowerString.indexOf("not null");
884          String name = columnSequence.substring(0, startIndex);
885          String dataType = "";
886          if (notNullIndex != -1 && notNullIndex < lastIndex) {
887            dataType = columnSequence.substring(startIndex + 1, notNullIndex - 1);
888          }
889          else {
890            dataType = columnSequence.substring(startIndex + 1, lastIndex - 1);
891          }
892          int defaultIndex = dataType.toLowerCase().indexOf("default ");
893    
894          if(defaultIndex != -1 ){
895            dataType = dataType.substring(0, defaultIndex);
896          }
897          list.add(new ColumnsInfo(name, dataType));
898          columnSequence = columnSequence.substring(lastIndex + 1).trim();
899        }
900        while (columnSequence.length() > 0); return list;
901      }
902    
903      public String getTableColumns(int VendorType, String columnName, TypeInfo typeInfo, int columnPrecision, ResultSet rs) throws RepException, SQLException {
904        StringBuffer sb = new StringBuffer();
905        sb.append(columnName).append(" ").append(typeInfo.getTypeDeclaration(columnPrecision));
906        return sb.toString();
907      }
908    
909      public String getShadowTableName(String tableName) {
910        return RepConstants.shadow_Table(tableName);
911      }
912    
913      /**
914       * Create index on syn_id column of shadowTable to
915       * improve the performance of Replicator Operation
916       * @param tableName
917       * @param columnName
918       */
919    
920      abstract protected void createIndex(String pubsubName,String tableName) throws RepException ;
921    //to handle the column scale
922       public ArrayList getColumnNamesAndDataTypes(String columnSequence) {
923        // col1 datatyoe, colName datatype
924        ArrayList list = new ArrayList();
925        String lowerString = columnSequence;
926        do {
927          lowerString = columnSequence.toLowerCase();
928          int startIndex = columnSequence.indexOf(" ");
929          int lastIndex = columnSequence.indexOf(","); /* get a supposed lastIndex */
930              /* loop through, using indexOf(int ch, int fromIndex), to find all the closed ')' for each open '(' */
931              int openIndex = startIndex;
932              int closeIndex = startIndex;
933              int i=0; int nextIndex = 0;
934              while (nextIndex < lastIndex) {
935                              openIndex = nextIndex;
936                              i++; /* we've got at least one that's less than lastIndex, so we'll need to check
937                                              to make sure they all close before lastIndex */
938                              nextIndex = columnSequence.indexOf("(",nextIndex+1);
939                              if (nextIndex == -1) break; /* last open paren of the whole string */
940              }
941              i--; /* decrement the counter, b/c it counted the first instance, even if no open paren was found */
942              while (i > 0) {
943                              closeIndex = columnSequence.indexOf(")",closeIndex+1);
944                              i--;
945              }
946              /* if the last closing paren is after the comma, find the next one after the last closing paren */
947              if (closeIndex > lastIndex) lastIndex = columnSequence.indexOf(",",closeIndex); 
948    
949          int notNullIndex = lowerString.indexOf("not null");
950          String name = columnSequence.substring(0, startIndex);
951          String dataType = "";
952          if (notNullIndex != -1 && notNullIndex < lastIndex) {
953                    /* if we've got a not null */
954            dataType=columnSequence.substring(startIndex + 1, notNullIndex-1 );
955           if(dataType.indexOf("(")!=-1){
956             if(dataType.indexOf(")")==-1){
957                 String dataType1 = columnSequence.substring(notNullIndex + 1).trim();
958    
959               dataType = dataType + " , " + dataType1.substring(dataType1.indexOf(" ")+1, (dataType1.indexOf(",")-1));
960                lastIndex= lastIndex=(lastIndex+1)+dataType1.indexOf(" ,");//(lastIndex+1)+dataType1.length()-1;
961               }else{
962                dataType = columnSequence.substring(startIndex + 1, notNullIndex-1 );
963               }
964            }else{
965               dataType = columnSequence.substring(startIndex + 1, notNullIndex-1 );
966            }
967          }
968          else{
969                    /* no 'not null' string */
970            dataType=columnSequence.substring(startIndex + 1, lastIndex-1 );
971                    log.debug("name = "+name+", dataType = "+dataType);
972           /*
973               if(dataType.indexOf("(")!=-1){
974             if(dataType.indexOf(")")==-1){
975               String dataType1 =columnSequence.substring(lastIndex+1);
976               dataType = dataType + " , " + dataType1.substring((dataType1.indexOf(" ")+1), (dataType1.indexOf(",")-1));
977               lastIndex= lastIndex=(lastIndex+1)+dataType1.indexOf(",");//(lastIndex+1)+dataType1.length()-1;
978              }else{
979               dataType = columnSequence.substring(startIndex + 1, lastIndex-1 );
980                            System.out.println("bjt2, dataType - "+dataType);
981              }
982           }else{
983    
984              dataType = columnSequence.substring(startIndex + 1, lastIndex-1 );
985                            System.out.println("bjt3, dataType - "+dataType);
986           }
987               */
988          }
989          list.add(new ColumnsInfo(name, dataType));
990          columnSequence = columnSequence.substring(lastIndex + 1).trim();
991        }
992        while (columnSequence.length() > 0);
993        return list;
994      }
995    
996      //if precison is less than scale ,Exception will be thrown to user depending on subscriber databse
997      //---as suggested by Parveen Sir
998    abstract public int getAppropriateScale(int columnScale) throws RepException;
999    
1000      public void dropPublisherSystemTables(Connection con) {
1001          try {
1002            fireDropQuery(con, " drop table " + getPublicationTableName());
1003            fireDropQuery(con, " drop table " + getBookMarkTableName());
1004            fireDropQuery(con, " drop table " + getRepTableName());
1005            fireDropQuery(con, " drop table " + getLogTableName());
1006            fireDropQuery(con, " drop table " + getIgnoredColumns_Table());
1007            fireDropQuery(con, " drop table " + getTrackReplicationTablesUpdation_Table());
1008            fireDropQuery(con, " drop table " + getTrackPrimayKeyUpdation_Table());
1009          }
1010          catch (Exception ex) {
1011          }
1012        }
1013        public void dropSubscriberSystemTables(Connection con) {
1014            try {
1015              fireDropQuery(con, " drop table " + getSubscriptionTableName());
1016              fireDropQuery(con, " drop table " + getBookMarkTableName());
1017              fireDropQuery(con, " drop table " + getRepTableName());
1018              fireDropQuery(con, " drop table " + getLogTableName());
1019              fireDropQuery(con, " drop table " + getScheduleTableName());
1020              fireDropQuery(con, " drop table " + getIgnoredColumns_Table());
1021              fireDropQuery(con, " drop table " + getTrackReplicationTablesUpdation_Table());
1022              fireDropQuery(con, " drop table " + getTrackPrimayKeyUpdation_Table());
1023            }
1024            catch (Exception ex) {
1025            }
1026          }
1027    
1028          public void deleteRecordsFromSuperLogTable(Statement subStatment) throws
1029              SQLException {
1030            // insert one record in superLogTable
1031    
1032            StringBuffer query = new StringBuffer();
1033            query.append("insert into ").append(getLogTableName()).append(
1034                " (").
1035                append(RepConstants.logTable_tableName2).append(
1036                ") values  ('$$$$$$')");
1037    
1038            subStatment.execute(query.toString());
1039    
1040            query = new StringBuffer();
1041            // deleting all but one last record from super log table where commonid is maximum
1042            query.append("Select max(").append(RepConstants.logTable_commonId1).
1043                append(") from ").append(getLogTableName());
1044            ResultSet rs = subStatment.executeQuery(query.toString());
1045            rs.next();
1046            long maxCID = rs.getLong(1);
1047    
1048            query = new StringBuffer();
1049    
1050            query.append("delete from ").append(getLogTableName()).append(
1051                " where ")
1052                .append(RepConstants.logTable_commonId1).append(" !=").append(maxCID);
1053            subStatment.executeUpdate(query.toString());
1054            log.debug(query.toString());
1055          }
1056    
1057    
1058    
1059    
1060    
1061          public void dropGenerators(Connection con, String generatorName) throws SQLException {
1062          }
1063    
1064      /**
1065       * To get the foreign key error code to resolve
1066      *  the Synchronization problem when tables have
1067      *  parent - child relationship.
1068       * @return String
1069       */
1070      abstract public boolean isForeignKeyException(SQLException ex) throws
1071          SQLException;
1072    
1073      abstract public PreparedStatement makePrimaryPreperedStatement(Connection pub_sub_Connection, String[]
1074          primaryColumns, String shadowTable, String local_pub_sub_name) throws
1075          SQLException, RepException;
1076    
1077      public String getIgnoredColumns_Table() {
1078       return ignoredColumns_Table;
1079     }
1080    
1081     public String getTrackReplicationTablesUpdation_Table() {
1082        return trackReplicationTablesUpdation_Table;
1083      }
1084    
1085      public String getTrackPrimayKeyUpdation_Table() {
1086        return trackPrimaryKeyUpdation_Table;
1087      }
1088    
1089     abstract protected void createIgnoredColumnsTable(String pubName) throws SQLException,
1090          RepException;
1091      abstract protected void createTrackReplicationTablesUpdationTable(String pubSubName) throws
1092          RepException, SQLException;
1093      abstract protected void createTriggerForTrackReplicationTablesUpdationTable(String
1094         pubSubName) throws RepException, SQLException ;
1095     public Object getMinValOfSyncIdTodeleteRecordsFromShadowTable(String tableName,Statement stmt) throws SQLException {
1096         ResultSet rs =null;
1097        // selecting min of syncid or concideredId  from bookmarks table for one table
1098       try {
1099          StringBuffer query = new StringBuffer();
1100           query.append("Select case when min(").append(RepConstants.
1101               bookmark_lastSyncId4).append(")< min(").append(
1102               RepConstants.
1103               bookmark_ConisderedId5).append(" )then min( ").append(
1104               RepConstants.
1105               bookmark_lastSyncId4).append(" ) else min( ").append(
1106               RepConstants.
1107               bookmark_ConisderedId5).append(") end from ").append(
1108               getBookMarkTableName()).append(" where ").append(
1109               RepConstants.
1110               bookmark_TableName3).append(" = '").append( tableName).append("'");
1111    //    System.out.println("AbstractDBHandler.deleteRecordsFromShadowTable=::"+query.toString());
1112         rs = stmt.executeQuery(query.toString());
1113           rs.next();
1114          return rs.getObject(1);
1115        }finally{
1116          if(rs!=null)
1117            try {
1118              rs.close();
1119            }
1120            catch (SQLException ex) {
1121            }
1122        }
1123    }
1124    
1125    
1126      abstract public PreparedStatement makePrimaryPreperedStatementBackwardTraversing(String[] primaryColumns,long lastId,String local_pub_sub_name,String shadowTable) throws SQLException,RepException  ;
1127    
1128      String[] addPrefixWithColumnName(String[] columnNames0, String prefix) {
1129          String[] columnNames = new String[columnNames0.length];
1130          for (int i = 0; i < columnNames.length; i++) {
1131           columnNames[i] = prefix+columnNames0[i];
1132          }
1133          return columnNames;
1134        }
1135    
1136    }