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;
021    
022    import java.sql.*;
023    import java.util.*;
024    
025    import org.dbreplicator.replication.DBHandler.*;
026    import org.apache.log4j.Logger;
027    import org.dbreplicator.graph.DirectedGraph;
028    
029    /**
030     * This Class gets the connection object of the specified publisher or subscriber
031     * and then stores it's metadata information in the DataBaseMetaData object(dbmd).
032     * This inforamtion is used for performing different operations on the tables of
033     * the publication or subscription and on the columns and constraint.
034     */
035    
036    public class pgMetaDataInfo
037        extends MetaDataInfo {
038      protected static Logger log = Logger.getLogger(pgMetaDataInfo.class.getName());
039    
040      public pgMetaDataInfo(ConnectionPool connectionPool0, String pubsubName) throws RepException {
041        Connection con = connectionPool0.getConnection(pubsubName);
042        try
043        {
044          dbmd = con.getMetaData();
045        }
046        catch (SQLException ex) {
047          log.error(ex.getMessage(),ex);
048          throw new RepException("REP101", new Object[] {ex.getMessage()});
049        }
050        finally {
051          connectionPool0.returnConnection(con);
052        }
053      }
054    
055        public pgMetaDataInfo(Connection connection0) throws RepException
056        {
057            try
058            {
059          dbmd = connection0.getMetaData();
060        }
061        catch (SQLException ex) {
062          log.error(ex.getMessage(),ex);
063          throw new RepException("REP101", new Object[] {ex.getMessage()});
064        }
065      }
066    
067      public void checkTableExistance(SchemaQualifiedName sname) throws RepException {
068        String schema = sname.getSchemaName();
069        String table = sname.getTableName();
070        try {
071          ResultSet rs = dbmd.getTables(null, schema, table, new String[] {"TABLE"});
072          if (rs == null || !rs.next()) {
073            log.debug("Resultset  found  " + rs + " OR Resultset found false");
074            throw new RepException("REP017", new Object[] {sname.toString()});
075          }
076          sname.setSchemaName(this, rs.getString("TABLE_SCHEM"));
077                if (rs.next())
078                {
079            throw new RepException("REP018", new Object[] {table});
080    
081          }
082                /** @todo rs has been closed */
083                rs.close();
084        }
085        catch (RepException ex) {
086          log.error(ex.getMessage(),ex);
087          throw ex;
088        }
089        catch (SQLException ex) {
090          log.error(ex.getMessage(),ex);
091          throw new RepException("REP006", new Object[] {ex.getMessage()});
092        }
093      }
094    
095      public String[] getTablesHierarchy(String[] tableNames) throws RepException {
096        ArrayList givenListOfTables = new ArrayList();
097        for (int i = 0; i < tableNames.length; i++) {
098          givenListOfTables.add(tableNames[i].toUpperCase());
099        }
100        ArrayList listToReturn = new ArrayList();
101        ArrayList sublist1 = new ArrayList();
102        for (int i = 0; i < tableNames.length; i++) {
103          SchemaQualifiedName sname = new SchemaQualifiedName(this, tableNames[i]);
104          String schemaName = sname.getSchemaName();
105          String tableName = sname.getTableName();
106              listToReturn.add(sname.toString());
107              sublist1 = getTableSequenceRegardingForeignKey(schemaName,tableName,listToReturn);
108              addTablesRecursively(givenListOfTables,listToReturn,sublist1);
109              sublist1.clear();
110        }
111        String[] arr = new String[listToReturn.size()];
112        listToReturn.toArray(arr);
113        log.debug(listToReturn);
114        return arr;
115      }
116    
117      private void addTablesRecursively(ArrayList givenListOfTables, ArrayList listToReturn, ArrayList sublist) throws RepException {
118    
119        for (int j = 0; j < sublist.size(); j++) {
120          String table = (String) sublist.get(j);
121          if (listToReturn.contains(table)) {
122            SchemaQualifiedName sname = new SchemaQualifiedName(this, table);
123            String schemaName = sname.getSchemaName();
124            String tableName = sname.getTableName();
125            ArrayList subList1 = getTableSequenceRegardingForeignKey(schemaName, tableName, listToReturn);
126            listToReturn.remove(listToReturn.indexOf(table));
127            listToReturn.add(table);
128            addTablesRecursively(givenListOfTables,listToReturn,subList1);
129            subList1.clear();
130          } else {
131            listToReturn.add(table);
132          }
133        }
134      }
135    
136      public void checkParentTablesIncludedInList(ArrayList givenListOfTables, String schemaName, String tableName) throws RepException {
137    
138        try {
139          ResultSet rs = dbmd.getImportedKeys(null, schemaName, tableName);
140          if (rs != null && rs.next()) {
141            do {
142              String pk_tableName = rs.getString("PKTABLE_SCHEM") + "." + rs.getString("PKTABLE_NAME");
143              log.debug("Primary Tablename " + pk_tableName);
144              if (!givenListOfTables.contains(pk_tableName)) {
145                int lastIndexOfDot = pk_tableName.lastIndexOf(".");
146                if (lastIndexOfDot != -1) {
147                  String pktableName = pk_tableName.substring(lastIndexOfDot + 1);
148                  if (!givenListOfTables.contains(pktableName)) {
149                    throw new RepException("REP0201", new Object[] {
150                        pk_tableName, tableName});
151                  }
152    
153                }
154                else {
155                  throw new RepException("REP0201", new Object[] {
156                      pk_tableName, tableName});
157                }
158              }
159    
160            }
161            while (rs.next());
162               /** @todo rs has been closed */
163               rs.close();
164          }
165        }
166        catch (SQLException ex) {
167          log.error(ex.getMessage(),ex);
168          throw new RepException("REP0202", new Object[] {tableName, ex.getMessage()});
169    
170        }
171      }
172    
173      protected ArrayList getTableSequenceRegardingForeignKey(String schemaName, String tableName, ArrayList tableList) throws RepException {
174        ArrayList list = new ArrayList();
175        try {
176          log.debug(" TableName : " + schemaName + "." + tableName);
177          ResultSet rs2 = dbmd.getExportedKeys(null, schemaName, tableName);
178          log.debug(" ExportedKeys Table Name's ResultSet " + rs2);
179          String qualifiedName = null;
180                if (rs2 != null && rs2.next())
181                {
182                    do
183                    {
184                        String foreignTable = rs2.getString("FKTABLE_SCHEM") + "." + rs2.getString("FKTABLE_NAME");
185              if (tableList.size() > 0) {
186                String table_list = (String) tableList.get(0);
187                if (table_list.indexOf(".") == -1) {
188                  int indexInforiegnTable = foreignTable.lastIndexOf(".");
189                  if (indexInforiegnTable != -1) {
190                    foreignTable = foreignTable.substring(indexInforiegnTable + 1);
191                  }
192                }
193              }
194    
195    
196              if (schemaName != null) {
197                qualifiedName = schemaName + "." + tableName;
198              }
199              else {
200                qualifiedName = tableName;
201              }
202    
203                        if (tableList.contains(foreignTable)&& !foreignTable.equalsIgnoreCase(qualifiedName))
204                        {
205                list.add(tableList.get(tableList.indexOf(foreignTable)));
206                tableList.remove(foreignTable.toUpperCase());
207              }
208            }
209            while (rs2.next());
210                    /** @todo rs has been closed */
211                    rs2.close();
212          }
213        }
214        catch (SQLException ex) {
215          log.error(ex.getMessage(),ex);
216          throw new RepException("REP006", new Object[] {ex.getMessage()});
217        }
218        log.debug(" Sublist of tables  =" + list);
219        return list;
220      }
221    
222      public void checkTableSequenceAccordingForeignKey(String schemaName,
223          String tableName, ArrayList tableList) throws RepException {
224        log.debug(" TableName : " + schemaName + "." + tableName);
225        try {
226          ResultSet rs2 = dbmd.getExportedKeys(null, schemaName, tableName);
227          log.debug(" ExportedKeys Table Name's ResultSet " + rs2);
228          if (rs2 != null && rs2.next()) {
229            do {
230              String primaryTable = rs2.getString("PKTABLE_SCHEM") + "." + rs2.getString("PKTABLE_NAME");
231              String foreignTable = rs2.getString("FKTABLE_SCHEM") + "." + rs2.getString("FKTABLE_NAME");
232              if (primaryTable.equalsIgnoreCase(foreignTable)) {
233                continue;
234              }
235              if (tableList.size() > 0) {
236                String table_list = (String) tableList.get(0);
237                if (table_list.indexOf(".") == -1) {
238                  int indexInforiegnTable = foreignTable.lastIndexOf(".");
239                  if (indexInforiegnTable != -1) {
240                    foreignTable = foreignTable.substring(indexInforiegnTable + 1);
241                  }
242                }
243              }
244    
245              if (tableList.contains(foreignTable)) {
246                throw new RepException("REP015", new Object[] {
247                    primaryTable, foreignTable});
248              }
249            }
250            while (rs2.next());
251                    /** @todo rs2 has been closed */
252                    rs2.close();
253          }
254        }
255        catch (RepException ex) {
256          log.error(ex.getMessage(),ex);
257          throw ex;
258        }
259        catch (SQLException ex) {
260          log.error(ex.getMessage(),ex);
261          throw new RepException("REP006", new Object[] {ex.getMessage()});
262        }
263      }
264      public ArrayList getColumnDataTypeInfo(AbstractDataBaseHandler dbh,
265                                             String schemaName, String tableName) throws
266            RepException, SQLException
267        {
268        ArrayList colInfoList = new ArrayList();
269        ResultSet rs = dbmd.getColumns(null, schemaName, tableName, "%");
270        if (rs == null || !rs.next()) {
271          log.debug("Resultset  found" + rs + " OR Resultset found false");
272          throw new RepException("Internal Error", null);
273        }
274            do
275            {
276          TypeInfo typeInfo = new TypeInfo(
277              dbh.updateDataType(rs.getString("TYPE_NAME")), rs.getInt("DATA_TYPE"));
278          int columnPrecision = rs.getInt("COLUMN_SIZE");
279           int columnScale=rs.getInt("DECIMAL_DIGITS");
280          String typeName = rs.getString("TYPE_NAME").trim();
281          columnPrecision = dbh.getAppropriatePrecision(columnPrecision, typeName);
282          columnScale=dbh.getAppropriateScale(columnScale);
283          typeInfo.setOptionalSizeProperty(dbh.isDataTypeOptionalSizeSupported(
284              typeInfo));
285          typeInfo.setColumnSize(rs.getInt("COLUMN_SIZE"));
286          typeInfo.setColumnScale(columnScale);
287          dbh.setTypeInfo(typeInfo, rs);
288          //colInfoMap.put(rs.getString("COLUMN_NAME").trim(),typeInfo.getTypeDeclaration(rs.getInt("COLUMN_SIZE")));
289          colInfoList.add(new ColumnsInfo(rs.getString("COLUMN_NAME").trim(),
290                                          typeInfo.getTypeDeclaration(
291              columnPrecision)));
292          log.debug("Column Name " + rs.getString("COLUMN_NAME").trim());
293          log.debug("Type declaration " +
294                    typeInfo.getTypeDeclaration(columnPrecision));
295        }
296        while (rs.next());
297    
298            /** @todo rs has been closed */
299            rs.close();
300    
301        return colInfoList;
302      }
303    
304      public HashMap getColumnsInfo(String schemaName, String tableName) throws
305            RepException, SQLException
306        {
307        HashMap colInfoMap = new HashMap();
308        ResultSet rs = dbmd.getColumns(null, schemaName, tableName, "%");
309        if (rs == null || !rs.next()) {
310          log.debug("Resultset  found" + rs + " OR Resultset found false");
311          throw new RepException("Internal Error", null);
312        }
313        int i = 0;
314            do
315            {
316                colInfoMap.put(new Integer(i++),new ColumnsInfo(rs.getString("COLUMN_NAME").trim(), null));
317        }
318        while (rs.next());
319        log.debug(" colInfoMap  : " + colInfoMap);
320     /** @todo rs has been closed */
321            rs.close();
322        return colInfoMap;
323      }
324    
325      /**
326       * @param dbh
327       * @param srcVendorType
328       * @param schemaName
329       * @param tableName
330       * @param tgtVendorType
331       * @return String
332       * @throws SQLException
333       * @throws RepException
334       */
335      public String generateColumnsQueryForClientNode(AbstractDataBaseHandler dbh,
336                                                      int srcVendorType,
337                                                      String schemaName,
338                                                      String tableName,
339                                                      int tgtVendorType) throws
340            RepException
341        {
342        StringBuffer sb = new StringBuffer();
343            AbstractDataBaseHandler remotedbh = Utility.getDatabaseHandler(tgtVendorType);
344        AbstractDataBaseHandler optSizedbh = (srcVendorType == tgtVendorType) ?
345            dbh : remotedbh;
346        try {
347          ResultSet rs = dbmd.getColumns(null, schemaName, tableName, "%");
348          if (rs == null || !rs.next()) {
349            log.debug("Resultset  found" + rs + " OR Resultset found false");
350            throw new RepException("REP033",
351                                   new Object[] {schemaName + "." + tableName});
352          }
353                do
354                {
355            String typeName = dbh.updateDataType(rs.getString("TYPE_NAME"));
356            TypeInfo typeInfo = new TypeInfo(typeName, rs.getInt("DATA_TYPE"));
357            int columnPrecision = rs.getInt("COLUMN_SIZE");
358            int columnScale=rs.getInt("DECIMAL_DIGITS");
359            String columnName = rs.getString("COLUMN_NAME");
360            columnPrecision = optSizedbh.getAppropriatePrecision(columnPrecision,
361                typeName);
362            typeInfo.setColumnSize(rs.getInt("COLUMN_SIZE"));
363            columnScale=optSizedbh.getAppropriateScale(columnScale);
364            int columnScalePublisher=dbh.getAppropriateScale(columnScale);
365            if (columnScale > columnScalePublisher) {
366              typeInfo.setColumnScale(columnScalePublisher);
367            }
368            else {
369              typeInfo.setColumnScale(columnScale);
370            }
371    
372            optSizedbh.setTypeInfo(typeInfo, rs);
373                    typeInfo.setOptionalSizeProperty(optSizedbh.isDataTypeOptionalSizeSupported(typeInfo));
374            String nullable = rs.getString("IS_NULLABLE").trim();
375            if (nullable.equalsIgnoreCase("NO") &&
376                        tgtVendorType == Utility.DataBase_Cloudscape)
377                    {
378              sb.append(columnName).append(" ").append(typeInfo.getTypeDeclaration(
379                  columnPrecision)).append(" NOT NULL ");
380            }
381                    else if (nullable.equalsIgnoreCase("NO") && tgtVendorType == Utility.DataBase_DB2)
382                    {
383              sb.append(columnName).append(" ").append(typeInfo.getTypeDeclaration(
384                  columnPrecision)).append(" NOT NULL ");
385            }
386                    else
387                    {
388              sb.append(columnName).append(" ").append(typeInfo.getTypeDeclaration(
389                  columnPrecision));
390            }
391             if (srcVendorType == tgtVendorType)
392             {
393              typeInfo.setTypeName(typeName);
394            }
395             String defaultValue = rs.getString("COLUMN_DEF"); //String => default value (may be <code>null</code>)
396             if (defaultValue != null && !defaultValue.equalsIgnoreCase("NULL"))
397               sb.append(" ").append("DEFAULT").append("  ").append(defaultValue);
398            nullable = rs.getString("IS_NULLABLE").trim(); // String => "NO" means column definitely does not allow NULL values; "YES" means the column might allow NULL values.  An empty string means nobody knows.
399                    if (nullable.equalsIgnoreCase("NO"))
400                    {
401                        if (notNullColumns == null)
402                        {
403                notNullColumns = new ArrayList();
404              }
405              notNullColumns.add(columnName);
406            }
407            sb.append(" , ");
408          }
409          while (rs.next());
410                /** @todo rs has been closed */
411                rs.close();
412        }
413        catch (RepException ex) {
414          log.error(ex.getMessage(),ex);
415          throw ex;
416        }
417        catch (SQLException ex) {
418          log.error(ex.getMessage(),ex);
419          throw new RepException("REP006", new Object[] {ex.getMessage()});
420        }
421        log.debug(sb.toString());
422        return sb.toString();
423      }
424    
425      public String getAppliedConstraints(String schemaName, String tableName,
426                                            TreeMap primConsMap) throws RepException
427        {
428        StringBuffer cons = new StringBuffer();
429        appendPrimaryConstraints(schemaName, tableName, cons, primConsMap);
430         appendUniqueKeyConstraints(schemaName, tableName, cons);
431        appendCheckConstraints(cons);
432        return cons.toString();
433      }
434    
435      public String getAppliedConstraintsForExistingTable(String schemaName,
436          String tableName, TreeMap primConsMap, AbstractDataBaseHandler dbh) throws
437            RepException
438        {
439        StringBuffer cons = new StringBuffer();
440        appendPrimaryConstraints(schemaName, tableName, cons, primConsMap);
441        appendUniqueKeyConstraints(schemaName, tableName, cons);
442        addCheckConstraintForExistingTable(schemaName, tableName, primConsMap, dbh);
443        appendCheckConstraints(cons);
444        return cons.toString();
445      }
446    
447      public void addCheckConstraintForExistingTable(String schemaName,
448                                                     String tableName,
449                                                     TreeMap primConsMap,
450                                                     AbstractDataBaseHandler dbh) throws
451          RepException {
452        try {
453          ResultSet rs = dbmd.getColumns(null, schemaName, tableName, "%");
454          if (rs == null || !rs.next()) {
455            log.debug("Resultset  found  " + rs + " OR Resultset found false");
456            throw new RepException("REP033",
457                                   new Object[] {schemaName + "." + tableName});
458          }
459                do
460                {
461            String columnName = rs.getString("COLUMN_NAME");
462            String nullable = rs.getString("IS_NULLABLE").trim();
463                    if (nullable.equalsIgnoreCase("NO"))
464                    {
465                        if (notNullColumns == null)
466                        {
467                notNullColumns = new ArrayList();
468              }
469              notNullColumns.add(columnName);
470            }
471          }
472          while (rs.next());
473                /** @todo rs has been closed */
474                rs.close();
475        }
476        catch (RepException ex) {
477          log.error(ex.getMessage(),ex);
478          throw ex;
479        }
480        catch (SQLException ex) {
481          log.error(ex.getMessage(),ex);
482          throw new RepException("REP006", new Object[] {ex.getMessage()});
483        }
484    
485      }
486    
487      private void appendPrimaryConstraints(String schemaName, String tableName,
488                                            StringBuffer cols, TreeMap consTableMap) throws
489            RepException
490        {
491        HashMap primcolmap = null;
492        try {
493          ResultSet rs = dbmd.getPrimaryKeys(null, schemaName, tableName);
494          if (rs == null || !rs.next()) {
495            log.debug("Resultset  found  " + rs + " OR Resultset found false");
496            throw new RepException("REP034",
497                                   new Object[] {schemaName + "." + tableName});
498          }
499          primcolmap = new HashMap();
500                do
501                {
502                    primcolmap.put(new Integer(rs.getInt("KEY_SEQ")),rs.getString("COLUMN_NAME"));
503          }
504          while (rs.next());
505                /** @todo rs has been closed */
506                rs.close();
507        }
508        catch (RepException ex) {
509          log.error(ex.getMessage(),ex);
510          throw ex;
511        }
512        catch (SQLException ex) {
513          log.error(ex.getMessage(),ex);
514          throw new RepException("REP006", new Object[] {ex.getMessage()});
515        }
516        Object[] indexes = primcolmap.keySet().toArray();
517        Arrays.sort(indexes);
518        cols.append(" Primary Key (");
519        StringBuffer temp = new StringBuffer();
520            for (int i = 0; i < indexes.length; i++)
521            {
522                if (i != 0)
523                {
524            temp.append(",");
525          }
526          temp.append(primcolmap.get(indexes[i]));
527        }
528        cols.append(temp.toString()).append(" )");
529        log.debug(cols.toString());
530        consTableMap.put(schemaName + "." + tableName,
531                         schemaName + "." + tableName + "(" + temp.toString() + ")");
532      }
533    
534      // PKTABLE_CAT</B> String => primary key table catalog being imported (may be <code>null</code>)
535      // PKTABLE_SCHEM</B> String => primary key table schema being imported (may be <code>null</code>)
536      // PKTABLE_NAME</B> String => primary key table name being imported
537      // PKCOLUMN_NAME</B> String => primary key column name being imported
538      // FKTABLE_CAT</B> String => foreign key table catalog (may be <code>null</code>)
539      // FKTABLE_SCHEM</B> String => foreign key table schema (may be <code>null</code>)
540      // FKTABLE_NAME</B> String => foreign key table name
541      // FKCOLUMN_NAME</B> String => foreign key column name
542      // KEY_SEQ</B> short => sequence number within a foreign key
543      // UPDATE_RULE</B> short =>
544      // DELETE_RULE</B> short =>
545      // FK_NAME</B> String => foreign key name (may be <code>null</code>)
546      // PK_NAME</B> String => primary key name (may be <code>null</code>)
547      // DEFERRABILITY</B> short => can the evaluation of foreign key constraints be deferred until commit
548      private void appendForeignConstraints(String schemaName, String tableName,
549                                            StringBuffer cols, TreeMap consTableMap) throws
550            RepException
551        {
552        HashMap fk_Keys = new HashMap();
553        HashMap fk_pk = new HashMap();
554        try {
555          ResultSet rs = dbmd.getImportedKeys(null, schemaName, tableName);
556          if (rs == null || !rs.next()) {
557            log.debug("Resultset  found  " + rs + " OR Resultset found false");
558            return;
559          }
560                do
561                {
562            String pk_tableName = rs.getString("PKTABLE_SCHEM") + "." +
563                rs.getString("PKTABLE_NAME");
564            Object primObject = consTableMap.get(pk_tableName);
565    
566            if (primObject == null) {
567              ResultSet primaryColumns = dbmd.getPrimaryKeys(null,
568                  rs.getString("PKTABLE_SCHEM"), rs.getString("PKTABLE_NAME"));
569              HashMap map = new HashMap();
570              while (primaryColumns.next()) {
571                int columnIndex = primaryColumns.getInt("KEY_SEQ");
572                String columnName = primaryColumns.getString("COLUMN_NAME");
573                map.put(new Integer(columnIndex), columnName);
574              }
575              Object[] indexes = map.keySet().toArray();
576              Arrays.sort(indexes);
577              StringBuffer temp = new StringBuffer();
578              for (int i = 0; i < indexes.length; i++) {
579                if (i != 0) {
580                  temp.append(",");
581                }
582                temp.append(map.get(indexes[i]));
583              }
584              consTableMap.put(pk_tableName.toUpperCase(),
585                               pk_tableName.toUpperCase() + "(" + temp.toString() +
586                               ") ");
587              primObject = consTableMap.get(pk_tableName);
588            }
589    
590    
591                    if (primObject != null)
592                    {
593              String fk_tableName = rs.getString("FKTABLE_SCHEM") + "." +
594                  rs.getString("FKTABLE_NAME");
595              String mapKey = rs.getString("FK_NAME");
596              Object ob = fk_Keys.get(mapKey);
597                        if (ob == null)
598                        {
599                HashMap colsMap = new HashMap();
600                            colsMap.put(new Integer(rs.getInt("KEY_SEQ")),rs.getString("FKCOLUMN_NAME"));
601                fk_pk.put(mapKey, primObject);
602                fk_Keys.put(mapKey, colsMap);
603              }
604                        else
605                        {
606                            ( (HashMap) ob).put(new Integer(rs.getInt("KEY_SEQ")),rs.getString("FKCOLUMN_NAME"));
607              }
608            }
609          }
610          while (rs.next());
611                rs.close();
612        }
613            catch (SQLException ex)
614            {
615                throw new RepException("REP006", new Object[]
616                                       {ex.getMessage()});
617        }
618        Object[] fkeys = fk_Keys.keySet().toArray();
619        Arrays.sort(fkeys);
620            for (int i = 0; i < fkeys.length; i++)
621            {
622          HashMap map = (HashMap) fk_Keys.get(fkeys[i]);
623          Object[] indexes = map.keySet().toArray();
624          Arrays.sort(indexes);
625          cols.append(" , Foreign Key (");
626                for (int j = 0; j < indexes.length; j++)
627                {
628                    if (j != 0)
629                    {
630              cols.append(",");
631            }
632            cols.append(map.get(indexes[j]));
633          }
634          cols.append(" ) References " + fk_pk.get(fkeys[i]));
635          log.debug(cols.toString());
636        }
637      }
638    
639        private void appendCheckConstraints(StringBuffer sb)
640        {
641            for (int i = 0, length = notNullColumns.size(); i < length; i++)
642            {
643          sb.append(" , Check ( ").append(notNullColumns.get(i)).append(
644              " is not null ) ");
645        }
646        notNullColumns = null;
647      }
648    
649      public void setPrimaryColumns(RepTable repTable, String schemaName,
650                                    String tableName) throws RepException,
651            SQLException
652        {
653        HashMap primcolmap = new HashMap();
654        ResultSet rs = dbmd.getPrimaryKeys(null, schemaName, tableName);
655        if (rs == null || !rs.next()) {
656          log.debug("Resultset  found " + rs + " OR Resultset found false");
657          throw new RepException("REP034", new Object[] {tableName});
658        }
659            do
660            {
661                primcolmap.put(new Integer(rs.getInt("KEY_SEQ")),rs.getString("COLUMN_NAME"));
662        }
663        while (rs.next());
664            /** @todo rs has been closed */
665            rs.close();
666        String[] primColumns = new String[primcolmap.size()];
667        Object[] indexes = primcolmap.keySet().toArray();
668        Arrays.sort(indexes);
669            for (int i = 0; i < indexes.length; i++)
670            {
671          primColumns[i] = (String) primcolmap.get(indexes[i]);
672        }
673        repTable.setPrimaryColumns(primColumns);
674      }
675    
676    
677      public String getCatalogName(String identifier) {
678        int catalog_index = identifier.indexOf('.');
679        if (catalog_index != -1) {
680          return identifier.substring(0, catalog_index).toLowerCase();
681        }
682        else {
683          return null;
684        }
685      }
686    
687      public String getSchemaName(String identifier) {
688        int table_name_index = identifier.lastIndexOf('.');
689    
690        if (table_name_index != -1) {
691          identifier = identifier.substring(0, table_name_index);
692          int catalog_index = identifier.indexOf('.');
693          if (catalog_index != -1) {
694            return identifier.substring(catalog_index + 1).toLowerCase();
695          }
696          else {
697            identifier = identifier.replaceAll("\"","");
698            return identifier.toLowerCase();
699          }
700        }
701        else {
702          return null;
703        }
704    
705      }
706    
707      public String getTableName(String schematable) {
708    
709        String table = null;
710        int index = schematable.indexOf('.');
711        if (index == -1) {
712          if (schematable.lastIndexOf("\"") == -1) {
713            table = schematable.substring(0, schematable.length());
714            return table.toLowerCase();
715          }
716          else {
717            table = schematable.substring(1, schematable.length() - 1);
718            table = table.replaceAll("\"", "");
719            return table.toLowerCase();
720          }
721        }
722        else {
723          if (schematable.lastIndexOf("\"") == -1) {
724            table = schematable.substring(index + 1);
725            table = table.replaceAll("\"", "");
726            return table.toLowerCase();
727          }
728          else {
729            table = schematable.substring(index + 2);
730            table = table.replaceAll("\"", "");
731            return table.toLowerCase();
732          }
733        }
734    }
735    
736    
737    
738    
739      public String getExistingTableQuery(AbstractDataBaseHandler dbh,
740                                          SchemaQualifiedName sname,
741                                          int pubVendorType) throws RepException,
742               SQLException
743           {
744               String table =null,schema =null;
745        StringBuffer sb = new StringBuffer();
746        table = sname.getTableName();
747        schema = sname.getSchemaName();
748        sb.append(" Create Table ").append(sname.toString()).append(" ( ");
749        ResultSet rs = dbmd.getColumns(null, schema, table, "%");
750        if (rs == null || !rs.next()) {
751          log.debug("Resultset  found " + rs + " OR Resultset found false");
752          throw new RepException("REP033", new Object[] {sname.toString()});
753        }
754               do
755               {
756          String typeName = dbh.updateDataType(rs.getString("TYPE_NAME"));
757          TypeInfo typeInfo = new TypeInfo(typeName,
758                                           rs.getInt("DATA_TYPE"));
759          int size = rs.getInt("COLUMN_SIZE");
760          String columnName = rs.getString("COLUMN_NAME");
761          typeInfo.setColumnSize(rs.getInt("COLUMN_SIZE"));
762          dbh.setTypeInfo(typeInfo, rs);
763          typeInfo.setOptionalSizeProperty(dbh.isDataTypeOptionalSizeSupported(
764              typeInfo));
765          sb.append(columnName).append(" ").append(typeInfo.getTypeDeclaration(size));
766          sb.append(" , ");
767        }
768        while (rs.next());
769               /** @todo rs has been closed */
770               rs.close();
771        sb.append(" " +
772                         getAppliedConstraintsForExistingTable(schema, table, new TreeMap(String.CASE_INSENSITIVE_ORDER),
773            dbh) + " ) ");
774        log.debug(sb.toString());
775        return sb.toString();
776      }
777    
778    
779     private String[] getTables() throws SQLException{
780    
781        ArrayList list = new ArrayList();
782       ResultSet rsTableNames = dbmd.getTables(null, null, "%",new String[] {"TABLE"});
783        while (rsTableNames.next()) {
784          if (!rsTableNames.getString("TABLE_SCHEM").equalsIgnoreCase("SYSTEM")) {
785            if (rsTableNames.getString("TABLE_NAME").equalsIgnoreCase(RepConstants.
786                publication_TableName) ||
787                rsTableNames.getString("TABLE_NAME").equalsIgnoreCase(RepConstants.
788                subscription_TableName) ||
789                rsTableNames.getString("TABLE_NAME").equalsIgnoreCase(RepConstants.
790                bookmark_TableName) ||
791                rsTableNames.getString("TABLE_NAME").equalsIgnoreCase(RepConstants.
792                rep_TableName) ||
793                rsTableNames.getString("TABLE_NAME").equalsIgnoreCase(RepConstants.
794                log_Table) ||
795                rsTableNames.getString("TABLE_NAME").startsWith("Rep_shadow_")) {
796              continue;
797            }
798    
799            list.add(rsTableNames.getString("TABLE_SCHEM") + "." +
800                     rsTableNames.getString("TABLE_NAME"));
801          }
802    
803        }
804        String[] st = new String[list.size()];
805        list.toArray(st);
806        log.debug(list);
807        return st;
808      }
809    
810    
811      /**
812       * checkChildTableIncludedInDropTableList
813       *
814       * @param pubRepTableList ArrayList
815       * @param dropTableList ArrayList
816       * @return ArrayList
817       */
818      protected void checkChildTableIncludedInDropTableList(ArrayList
819          pubRepTableList, String[] dropTableList) {
820    
821      }
822    
823      public ArrayList getChildTables(String parentTable)throws RepException {
824       String foreignTable =null;
825       ArrayList childTableList =new ArrayList();
826       try {
827         SchemaQualifiedName sname =new SchemaQualifiedName(this,parentTable);
828         ResultSet rs2 = dbmd.getExportedKeys(null, sname.getSchemaName(),sname.getTableName());
829         log.debug(" ExportedKeys Table Name's ResultSet " + rs2);
830         if (rs2 != null && rs2.next()) {
831           do {
832              foreignTable = rs2.getString("FKTABLE_SCHEM") + "." +rs2.getString("FKTABLE_NAME");
833              childTableList.add(foreignTable);
834           }
835           while (rs2.next());
836         }
837         /** @todo   rs has been closed  */
838         rs2.close();
839    
840       }
841       catch (SQLException ex) {
842         log.error(ex.getMessage(), ex);
843         throw new RepException("REP006", new Object[] {ex.getMessage()});
844       }
845       return childTableList;
846     }
847    
848    
849    
850    
851    
852     public Object[] getImportedColsOfChildTable(String parentTable,String childTable)throws RepException {
853       ArrayList fkColsList=new ArrayList();
854       ArrayList referColsList=new ArrayList();
855       try {
856          SchemaQualifiedName sname =new SchemaQualifiedName(this,childTable);
857         ResultSet rs = dbmd.getImportedKeys(null, sname.getSchemaName(), sname.getTableName());
858         if (rs == null || !rs.next()) {
859           log.debug("Resultset  found  " + rs + " OR Resultset found false");
860           return null;
861         }
862         do {
863           String pk_tableName = rs.getString("PKTABLE_SCHEM") + "." +rs.getString("PKTABLE_NAME");
864           if(pk_tableName.equalsIgnoreCase(parentTable)){
865             fkColsList.add(rs.getString("FKCOLUMN_NAME"));
866             referColsList.add(rs.getString("PKCOLUMN_NAME"));
867           }
868         }
869         while (rs.next());
870         /** @todo   rs has been closed  */
871         rs.close();
872       }
873       catch (SQLException ex) {
874         log.error(ex.getMessage(), ex);
875         throw new RepException("REP006", new Object[] {ex.getMessage()});
876       }
877        String[] fkCols =new String[fkColsList.size()];
878        fkColsList.toArray(fkCols);
879        String[] pkCols =new String[referColsList.size()];
880        referColsList.toArray(pkCols);
881    
882       return new Object[]{fkCols,pkCols};
883     }
884    
885     public ArrayList getImportedTables(SchemaQualifiedName schemaQualifiedName, List passedSchemaQualifiedNamesList,DirectedGraph graph,String[] removeCycleTableNames0) throws RepException {
886             try {
887               ResultSet rs = dbmd.getImportedKeys(null, schemaQualifiedName.getSchemaName(), schemaQualifiedName.getTableName());
888               ArrayList listOfSuperTables = new ArrayList();
889               while (rs.next()) {
890                 SchemaQualifiedName superTableSchemaQualifiedName = new SchemaQualifiedName(this, null,rs.getString(2), rs.getString(3));
891                 /** @todo
892                  * define Ex in properties file
893                  *  */
894    
895                 if (!passedSchemaQualifiedNamesList.contains(superTableSchemaQualifiedName)) {
896                            throw new RepException("REP038", new Object[] {
897                       superTableSchemaQualifiedName, schemaQualifiedName});
898                 }
899                 //to handle tables in cycle case.we do not add edge for tables in removeCycleTableNames arraylist provided by user
900                 String [] removeCycleTableNames=removeCycleTableNames0;
901                 boolean addEdge=true;
902                 if(removeCycleTableNames!=null){
903                   for (int i = 0; i < removeCycleTableNames.length; i++) {
904                     StringTokenizer str = new StringTokenizer(removeCycleTableNames[i],"-");
905                     SchemaQualifiedName sname = new SchemaQualifiedName(this, (String)str.nextElement());
906                     SchemaQualifiedName sourceSname = sname;
907                     checkTableExistance(sname);
908                     if(!passedSchemaQualifiedNamesList.contains(sname))
909                       throw new RepException("Rep0206",new Object[] {sname});
910                     sname = new SchemaQualifiedName(this, (String)str.nextElement());
911                     checkTableExistance(sname);
912                     if(!passedSchemaQualifiedNamesList.contains(sname))
913                       throw new RepException("Rep0206",new Object[] {sname});
914                     SchemaQualifiedName targetSname = sname;
915                     if (sourceSname.toString().equalsIgnoreCase(schemaQualifiedName.toString()) &&
916                         targetSname.toString().equalsIgnoreCase(superTableSchemaQualifiedName.toString()))
917                       addEdge = false;
918                   }
919                 }
920                 // to handle self referencing case
921                 if(schemaQualifiedName.toString().equalsIgnoreCase(superTableSchemaQualifiedName.toString()))
922                      addEdge = false;
923    
924                 if(addEdge){
925                   graph.addEdge(schemaQualifiedName, superTableSchemaQualifiedName, 1);
926                 }
927    
928                 if(!listOfSuperTables.contains(superTableSchemaQualifiedName))
929                   listOfSuperTables.add(superTableSchemaQualifiedName);
930               }
931               rs.close();
932               return listOfSuperTables;
933             }catch(NoSuchElementException ex1){
934               throw new RepException("Rep0204",null);
935             }
936             catch (SQLException ex) {
937               throw new RepException("REP039",new Object[]{ex.getMessage()});
938             }
939           }
940           public ArrayList getForeignKeyConstraints(String schemaName, String tableName) throws RepException {
941             HashMap fk_Keys = new HashMap();
942             HashMap fk_pk = new HashMap();
943             HashMap consPkTableMap=new HashMap();
944             HashMap referenceString=new HashMap();
945             StringBuffer alterTableQuery = new StringBuffer();
946             ResultSet rs;
947             ArrayList foreignKeyConstraintsList = new ArrayList();
948    
949             try {
950               rs = dbmd.getImportedKeys(null, schemaName, tableName);
951               boolean next = rs.next();
952               if (!next) {
953                 return null;
954               }
955               do {
956                 String pk_tableName = rs.getString("PKTABLE_SCHEM") + "." + rs.getString("PKTABLE_NAME");
957    
958                 String mapPkKey = rs.getString("FK_NAME");
959                 //changed to handle the case for unique key
960    
961                 Object primObjectTemp = consPkTableMap.get(mapPkKey);
962                 if (primObjectTemp == null) {
963                   HashMap colsPkMap = new HashMap();
964                   colsPkMap.put(new Integer(rs.getInt("KEY_SEQ")),rs.getString("PKCOLUMN_NAME"));
965    
966                   consPkTableMap.put(mapPkKey, colsPkMap);
967                 }
968                 else {
969                   ((HashMap) primObjectTemp).put(new Integer(rs.getInt("KEY_SEQ")),rs.getString("PKCOLUMN_NAME"));
970                 }
971                 Object primObject = consPkTableMap.get(mapPkKey);
972                 if (primObject != null) {
973                   String fk_tableName = rs.getString("FKTABLE_SCHEM") + "." + rs.getString("FKTABLE_NAME");
974                   String mapKey = rs.getString("FK_NAME");
975                   Object ob = fk_Keys.get(mapKey);
976                   if (ob == null) {
977                     HashMap colsMap = new HashMap();
978                     colsMap.put(new Integer(rs.getInt("KEY_SEQ")), rs.getString("FKCOLUMN_NAME"));
979                     fk_pk.put(mapKey, primObject);
980                     fk_Keys.put(mapKey, colsMap);
981                   }
982                   else {
983                     ( (HashMap) ob).put(new Integer(rs.getInt("KEY_SEQ")), rs.getString("FKCOLUMN_NAME"));
984                   }
985                 }
986                   HashMap mapTemp=(HashMap)primObject;
987                   Iterator itr=mapTemp.values().iterator();
988                   Object colNames = null;
989                   do {
990                     if(colNames!=null){
991                       colNames =itr.next()+ "," +colNames  ;
992                     }
993                     else{
994                       colNames = itr.next();
995                     }
996                   }while(itr.hasNext()) ;
997                   referenceString.put(mapPkKey, pk_tableName + " ( " + colNames + " )");
998               }
999               while (rs.next());
1000               rs.close();
1001             }
1002             catch (SQLException ex) {
1003               throw new RepException("REP006", new Object[] {
1004                   ex.getMessage()});
1005             }
1006    
1007             Object[] fkeys = fk_Keys.keySet().toArray();
1008             Object[] pkeys = referenceString.keySet().toArray();
1009             for (int i = 0; i < fkeys.length; i++) {
1010    
1011                 alterTableQuery.append("alter table ")
1012                     .append(schemaName + "." + tableName).append(" add constraint ")
1013                     .append(fkeys[i]);
1014                 HashMap map = (HashMap) fk_Keys.get(fkeys[i]);
1015                 Object[] indexes = map.keySet().toArray();
1016                 Arrays.sort(indexes);
1017                 alterTableQuery.append(" Foreign Key (");
1018                 for (int j = 0; j < indexes.length; j++) {
1019                   if (j != 0) {
1020                     alterTableQuery.append(",");
1021                   }
1022                   alterTableQuery.append(map.get(indexes[j]));
1023                 }
1024                 alterTableQuery.append(" ) References ");
1025    
1026                 alterTableQuery.append(referenceString.get(pkeys[i])); //fk_pk.get(fkeys[i]));
1027                 foreignKeyConstraintsList.add(alterTableQuery.toString());
1028                 alterTableQuery.setLength(0);
1029    
1030             }
1031             return foreignKeyConstraintsList;
1032           }
1033    
1034           public void appendUniqueKeyConstraints(String schemaName, String tableName,
1035                                                   StringBuffer cols) throws RepException {
1036    
1037             HashMap consPkTableMap = new HashMap();
1038             HashMap uniqueCol = new HashMap();
1039             ArrayList primaryKeyColumns = new ArrayList();
1040             ResultSet rs=null,rsPk=null;
1041    
1042             try {
1043                rsPk = dbmd.getPrimaryKeys(null, schemaName, tableName);
1044               while (rsPk.next()) {
1045                 primaryKeyColumns.add(rsPk.getString("PK_NAME"));
1046               }
1047               rs = dbmd.getExportedKeys(null, schemaName, tableName);
1048               boolean next = rs.next();
1049               if (!next) {
1050                 return;
1051               }
1052               do {
1053                 String pk_tableName = rs.getString("PKTABLE_SCHEM") + "." +rs.getString("PKTABLE_NAME");
1054                 String mapPkKey = rs.getString("PK_NAME");
1055                 if (!primaryKeyColumns.contains(mapPkKey)) {
1056                   Object primObjectTemp = consPkTableMap.get(mapPkKey);
1057                   if (primObjectTemp == null) {
1058                     HashMap colsPkMap = new HashMap();
1059                     colsPkMap.put(new Integer(rs.getInt("KEY_SEQ")),
1060                                   rs.getString("PKCOLUMN_NAME"));
1061    
1062                     consPkTableMap.put(mapPkKey, colsPkMap);
1063                   }
1064                   else {
1065                     ( (HashMap) primObjectTemp).put(new Integer(rs.getInt("KEY_SEQ")),
1066                                                     rs.getString("PKCOLUMN_NAME"));
1067                   }
1068                   Object primObject = consPkTableMap.get(mapPkKey);
1069                   HashMap mapTemp = (HashMap) primObject;
1070                   Iterator itr = mapTemp.values().iterator();
1071                   Object colNames = null;
1072                   do {
1073                     if (colNames != null) {
1074                       colNames = itr.next() + "," + colNames;
1075                     }
1076                     else {
1077                       colNames = itr.next();
1078                     }
1079                   }
1080                   while (itr.hasNext());
1081                   uniqueCol.put(mapPkKey, " Unique " + " ( " + colNames + " )");
1082                 }
1083               }
1084               while (rs.next());
1085    
1086             }
1087             catch (SQLException ex) {
1088               throw new RepException("REP006", new Object[] {
1089                                      ex.getMessage()});
1090             }finally{
1091                try {
1092                  if (rs != null) {
1093                    rs.close();
1094                  }
1095                  if (rsPk != null) {
1096                    rsPk.close();
1097                  }
1098                }
1099                catch (SQLException ex1) {
1100                }
1101             }
1102             System.out.println("unique cols::" + uniqueCol);
1103             Object[] ukeys = uniqueCol.keySet().toArray();
1104             for (int j = 0; j < ukeys.length; j++) {
1105               cols.append("," + uniqueCol.get(ukeys[j]));
1106             }
1107           }
1108    
1109           public ArrayList getExportedTableCols(SchemaQualifiedName repTable) throws RepException{
1110             ResultSet rs=null;
1111             ArrayList exportedColumns = new ArrayList();
1112             try {
1113               rs = dbmd.getExportedKeys(null, repTable.getSchemaName(), repTable.getTableName());
1114                   boolean next = rs.next();
1115                   if (!next) {
1116                     return null;
1117                   }
1118                   do {
1119                     exportedColumns.add(rs.getString("PKCOLUMN_NAME"));
1120                   }
1121                   while (rs.next());
1122                 } catch (SQLException ex) {
1123                     throw new RepException("REP006", new Object[] {
1124                                            ex.getMessage()});
1125                   }finally{
1126                      try {
1127                        if (rs != null) {
1128                          rs.close();
1129                        }
1130                      }
1131                      catch (SQLException ex1) {
1132                      }
1133                   }
1134        return exportedColumns;
1135               }
1136    
1137               public void setAllColumns(RepTable repTable, String schemaName,
1138                                     String tableName) throws RepException, SQLException {
1139    
1140             ResultSet rs = dbmd.getColumns(null, schemaName, tableName, "%");
1141             if (rs == null || !rs.next()) {
1142               log.debug("Resultset  found  " + rs + " OR Resultset found false");
1143               throw new RepException("REP033",
1144                                      new Object[] {schemaName + "." + tableName});
1145             }
1146             ArrayList allColumnsList=new ArrayList();
1147             try {
1148               do {
1149                 allColumnsList.add(rs.getString("COLUMN_NAME"));
1150               }
1151               while (rs.next());
1152    
1153             }
1154             finally {
1155               if (rs != null)
1156                 rs.close();
1157             }
1158             for (int i = 0; i < allColumnsList.toArray().length; i++) {
1159             }
1160             int numberOfColumns =allColumnsList.size();
1161             String columns[] =new String[numberOfColumns];
1162             for (int i = 0; i < numberOfColumns; i++) {
1163               columns[i] = (String)allColumnsList.get(i);
1164             }
1165             repTable.setAllColumns(columns);
1166           }
1167    
1168    }