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.util.*;
023    import org.dbreplicator.replication.DBHandler.*;
024    import org.apache.log4j.Logger;
025    import java.util.TreeMap;
026    import java.sql.*;
027    
028    /**
029     * This Class stores all the relevant information for any replication table
030     * involved in the publication or subscription. A publisher or subscriber saves
031     * the objects of this class corresponding to all replication tables.
032     * This class is used to create Reptable table for the perticular publisher or sub.
033     *
034     */
035    
036    public class RepTable {
037    
038      private SchemaQualifiedName sname;
039      private String filterClause = null;
040      private String conflictResolver;
041      private String[] primCols;
042      private String[] foreignKeyCols;
043      private TreeMap columnsTypeInfo;
044      private StringBuffer questionMarksQuery;
045      private String createShadowTable = RepConstants.YES;
046      private String cyclicDependency = RepConstants.NO;
047      private String serverType;
048      private String[] columnsToBeIgnored;
049      private StringBuffer columnNamesQuery;
050      private TreeMap allColumns;
051      protected static Logger log =Logger.getLogger(RepTable.class.getName());
052    
053      public RepTable(SchemaQualifiedName sName0, String serverType0) {
054        sname = sName0;
055        serverType = serverType0;
056      }
057    
058      public RepTable(SchemaQualifiedName sName0, String filterClause0, String serverType0) {
059        this(sName0, serverType0);
060        filterClause = filterClause0;
061      }
062    
063      public String getFilterClause() {
064        return filterClause;
065      }
066    
067      public void setFilterClause(String filterClause0) {
068        filterClause = filterClause0;
069      }
070    
071      public String getConflictResolver() {
072        return conflictResolver;
073      }
074    
075      public void setConflictResolver(String conflictResolver0) {
076    //    if (conflictResolver0 == null) {
077    //      Thread.dumpStack();
078    //    }
079        conflictResolver = conflictResolver0;
080      }
081    
082      public String getCreateShadowTable() {
083        return createShadowTable;
084      }
085    
086      public String[] getForeignKeyCols() {
087        return foreignKeyCols;
088      }
089    
090      public void setCreateShadowTable(String createShadowTable) {
091        this.createShadowTable = createShadowTable;
092      }
093    
094      public void setForeignKeyCols(String[] foreignKeyCols0) {
095        foreignKeyCols = foreignKeyCols0;
096      }
097    
098      public SchemaQualifiedName getSchemaQualifiedName() {
099        return sname;
100      }
101    
102    
103    
104      /**
105       * Return a Treemap containg column Names and their respective data type.
106       * @param connection
107       * @param dbDatatypeHandler
108       * @return columnTypeInfo
109       */
110      public TreeMap getColumnTreeMap(Connection connection, AbstractDataBaseHandler dbDatatypeHandler) throws
111          SQLException, RepException {
112        if (columnsTypeInfo != null) {
113          return columnsTypeInfo;
114        }
115    
116        questionMarksQuery = new StringBuffer(); // for parameterised query
117        columnNamesQuery = new StringBuffer();
118        // Dont use putAll method for treeMap which uses equals method of
119        // comparator whihc is unImplemented
120        columnsTypeInfo = new TreeMap(String.CASE_INSENSITIVE_ORDER);
121        Statement st = connection.createStatement();
122        try {
123              /* bjt - replace with something more efficient to get metaData */
124          //st.execute("SELECT * FROM " + sname.toString());
125              /*
126               * give us just one record at 'random' from the table from which to
127               * grab table metadata
128               */
129          st.execute("SELECT * FROM " + sname.toString() + " LIMIT 1");
130          ResultSetMetaData rsmd = st.getResultSet().getMetaData();
131          for (int i = 0; i < rsmd.getColumnCount(); i++) {
132            boolean isIgnoredColumn = false;
133            String colName = rsmd.getColumnName(i + 1);
134            String[] ignoredColumnNames = getColumnsToBeIgnored();
135            int length = ignoredColumnNames == null ? 0 : ignoredColumnNames.length;
136            for (int j = 0; j < length; j++) {
137              if (colName.equalsIgnoreCase(ignoredColumnNames[j])) {
138                isIgnoredColumn = true;
139                break;
140              }
141            }
142            if (!isIgnoredColumn) {
143              columnNamesQuery.append(", " + colName);
144              questionMarksQuery.append(" , ? "); // For parameterised Query
145              int sqlType = rsmd.getColumnType(i + 1);
146              String typeName = rsmd.getColumnTypeName(i + 1);
147              TypeInfo typeInfo = new TypeInfo(typeName, sqlType);
148              dbDatatypeHandler.setColumnPrecisionInTypeInfo(typeInfo, rsmd, i + 1);
149              columnsTypeInfo.put(colName,dbDatatypeHandler.getColumnObject(typeInfo));
150            }
151          }
152        }
153        finally {
154          st.close();
155        }
156        int indexOfFirstComma = questionMarksQuery.indexOf(",");
157        if (indexOfFirstComma != -1) {
158          questionMarksQuery.deleteCharAt(indexOfFirstComma);
159        }
160        indexOfFirstComma = columnNamesQuery.indexOf(",");
161        if (indexOfFirstComma != -1) {
162          columnNamesQuery.deleteCharAt(indexOfFirstComma);
163        }
164        return columnsTypeInfo;
165      }
166    
167      public boolean isIgnoredColumn(String columnName) {
168        String ignoredColumnNames[] = getColumnsToBeIgnored();
169        if (ignoredColumnNames != null && ignoredColumnNames.length > 0) {
170          for (int i = 0; i < ignoredColumnNames.length; i++) {
171            if (ignoredColumnNames[i].equalsIgnoreCase(columnName)) {
172              return true;
173            }
174          }
175        }
176        return false;
177      }
178    
179      public String createInsertQueryForSnapShot() {
180        StringBuffer insertQuery = new StringBuffer();
181        insertQuery.append("INSERT INTO ")
182            .append(sname.toString())
183            .append(" ( ")
184            .append(columnNamesQuery)
185            .append(" )")
186            .append(" VALUES ( ")
187            .append(questionMarksQuery.toString())
188            .append(" ) ");
189            log.debug(insertQuery.toString());
190           return insertQuery.toString();
191      }
192    
193      public String createDeleteQueryForSynchronise() {
194        StringBuffer deleteQuery = new StringBuffer();
195        String[] primaryColumns = getPrimaryColumns();
196        deleteQuery.append("DELETE FROM ")
197            .append(sname.toString())
198            .append(" WHERE ( ");
199        for (int i = 0; i < primaryColumns.length; i++) {
200          if (i != 0) {
201            deleteQuery.append(" and ");
202          }
203          deleteQuery.append(primaryColumns[i]).append(" = ?");
204        }
205        deleteQuery.append(" ) ");
206        log.debug(deleteQuery.toString());
207        return deleteQuery.toString();
208      }
209    
210      public String createUpdateQueryForSnapShot() {
211        if (foreignKeyCols == null || foreignKeyCols.length == 0) {
212          return "";
213        }
214        StringBuffer updateQuery = new StringBuffer();
215        String primarycols[]=getPrimaryColumns();
216        updateQuery.append("UPDATE ")
217                   .append(sname.toString())
218                   .append(" SET ");
219        int size = foreignKeyCols.length;
220        for (int i = 0; i < size; i++) {
221          if(i!=0){
222            updateQuery.append(",");
223          }
224          updateQuery.append(foreignKeyCols[i]).append("= ? ");
225        }
226        updateQuery.append(" WHERE  ");
227       for (int i = 0; i < primarycols.length; i++) {
228         if (i != 0) {
229           updateQuery.append(" and ");
230         }
231         updateQuery.append(primarycols[i]);
232         updateQuery.append("= ?");
233       }
234        log.debug(updateQuery.toString());
235        return updateQuery.toString();
236      }
237    
238      public boolean isForeignKeyColumn(String columnName) {
239        if (foreignKeyCols != null) {
240          for (int i = 0, size = foreignKeyCols.length; i < size; i++) {
241            if (columnName.equalsIgnoreCase(foreignKeyCols[i])) {
242              return true;
243            }
244          }
245        }
246        return false;
247      }
248    
249      public String createDeleteQueryForSynchronise_ShadowTable(long lastConisderedId, String remoteServerName) {
250        StringBuffer selectQuery = new StringBuffer();
251        String[] primaryColumns = getPrimaryColumns();
252        selectQuery.append("SELECT * FROM  ")
253            .append(RepConstants.shadow_Table(getSchemaQualifiedName().toString()))
254            .append(" WHERE ")
255            .append(RepConstants.shadow_sync_id1)
256            .append(" > ")
257            .append(lastConisderedId);
258        for (int i = 0; i < primaryColumns.length; i++) {
259          selectQuery.append(" and ")
260                     .append(primaryColumns[i])
261                     .append(" = ?");
262        }
263        selectQuery.append(" and ")
264                   .append(RepConstants.shadow_serverName_n)
265                   .append("!='")
266                   .append(remoteServerName)
267                   .append("'  ");
268        return selectQuery.toString();
269      }
270    
271      /**
272       * Return the array of all primary columns that are declared
273       * in table which is included in publication.
274       * @return String[] Array of primary columns.
275       */
276      public String[] getPrimaryColumns() {
277        return primCols;
278      }
279    
280      /**
281       * Retunrs weather local server is conflict resolver or not.
282       * @return boolean
283       */
284      public boolean isLocalServerWinner() {
285        if (serverType.equalsIgnoreCase(RepConstants.subscriber)) {
286          return (conflictResolver.equalsIgnoreCase(RepConstants.subscriber_wins)) ? true : false;
287        }
288        else {
289          return (conflictResolver.equalsIgnoreCase(RepConstants.publisher_wins)) ? true : false;
290        }
291      }
292    
293      /**
294       * Retunrs a query for Update in Main table
295       * This method is called for performing update
296       * operation when Replicator found the update
297       * operation in XML file.
298       * @param loaclColumnNames
299       * @param primaryColumnName
300       * @return string preparedStatement
301       */
302      public String getUpdatePreStmt(ArrayList loaclColumnNames,
303                                         String[] primaryColumnName) {
304        StringBuffer updateQuery = new StringBuffer();
305        updateQuery.append(" UPDATE ")
306            .append(sname.toString())
307            .append(" SET ");
308        for (int i = 0; i < loaclColumnNames.size(); i++) {
309          if (i != 0) {
310            updateQuery.append(" , ");
311          }
312          updateQuery.append(loaclColumnNames.get(i))
313                     .append(" = ? ");
314        }
315        updateQuery.append(" WHERE  ");
316        for (int i = 0; i < primaryColumnName.length; i++) {
317          if (i != 0) {
318            updateQuery.append(" and ");
319          }
320          updateQuery.append(primaryColumnName[i])
321                     .append("= ?");
322        }
323        return updateQuery.toString();
324      }
325    
326      public void setPrimaryColumns(String[] primCols0) {
327        primCols = primCols0;
328      }
329    
330      public String toString() {
331        return "REPTABLE NAME [" + getSchemaQualifiedName().toString() +
332            "] PRIMCOLS [" + primCols + "] ConflictResolver [" + conflictResolver +
333            "] Filter [" + filterClause + "] ";
334      }
335    
336      public String getRepTableQualifiedIdentifier() {
337        return sname.toString();
338      }
339    
340      public String getCyclicDependency() {
341        return cyclicDependency;
342      }
343    
344    
345      public void setCyclicDependency(String cyclicDependency0) {
346        cyclicDependency = cyclicDependency0;
347      }
348    
349      public String[] getColumnsToBeIgnored() {
350        return columnsToBeIgnored;
351      }
352    
353      public String getServerType() {
354        return serverType;
355      }
356    
357      public void setColumnsToBeIgnored(String[] columnsToBeIgnored) {
358        this.columnsToBeIgnored = columnsToBeIgnored;
359      }
360    
361     public void setServerType(String serverType) {
362        this.serverType = serverType;
363      }
364    
365      public String createUpdateQueryForSynchronize() {
366         if (foreignKeyCols == null || foreignKeyCols.length == 0) {
367           return "";
368         }
369         StringBuffer updateQuery = new StringBuffer();
370         String[] primaryColumns = getPrimaryColumns();
371         updateQuery.append("UPDATE ").append(sname.toString()).append(" SET ");
372         int size = foreignKeyCols.length;
373         for (int i = 0; i < size; i++) {
374           if(i!=0) {
375             updateQuery.append(",");
376           }
377           updateQuery.append(foreignKeyCols[i]).append("= ? ");
378         }
379         updateQuery.append(" WHERE  ");
380       for (int i = 0; i < primaryColumns.length; i++) {
381         if (i != 0) {
382           updateQuery.append(" and ");
383         }
384         updateQuery.append(primaryColumns[i]).append(" = ?");
385         }
386    
387         log.debug(updateQuery.toString());
388         return updateQuery.toString();
389       }
390    
391       public void setAllColumns(String[] allColumns0) {
392         allColumns=new TreeMap();
393         for (int i = 0; i < allColumns0.length; i++) {
394           allColumns.put("c"+new Integer(i + 1), allColumns0[i]);
395         }
396       }
397    
398        public TreeMap getAllColumns() {
399          return allColumns;
400        }
401    }