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 021 package org.dbreplicator.replication.DBHandler; 022 023 import java.sql.*; 024 import java.util.*; 025 026 import org.dbreplicator.replication.*; 027 import org.dbreplicator.replication.column.*; 028 import org.apache.log4j.Logger; 029 030 /** 031 * Method overrides specific to the Firebird database engine. 032 */ 033 public class FireBirdDatabaseHandler 034 extends AbstractDataBaseHandler { 035 protected static Logger log = Logger.getLogger(FireBirdDatabaseHandler.class. 036 getName()); 037 public FireBirdDatabaseHandler() {} 038 039 public FireBirdDatabaseHandler(ConnectionPool connectionPool0) { 040 connectionPool = connectionPool0; 041 vendorType = Utility.DataBase_Firebird; 042 } 043 044 //Table command: CREATE TABLE tablename (ID VARCHAR(10) CONSTRAINT pk_id PRIMARY KEY, ...) 045 //Sequence command: CREATE SEQUENCE seq tablename minvalue 1 increment by 1 nomaxvalue 046 //Insert command: INSERT INTO tablename (ID,...) VALUES(seq_tablename.nextval,....) 047 048 049 protected void createPublicationTable(String pubName) throws RepException, 050 SQLException { 051 StringBuffer pubsTableQuery = new StringBuffer(); 052 pubsTableQuery.append(" Create Table ") 053 .append(getPublicationTableName()) 054 .append(" ( " + RepConstants.publication_pubName1 + 055 " varchar(50) NOT NULL , " + 056 RepConstants.publication_conflictResolver2 + " varchar(255) , ") 057 .append(" " + RepConstants.publication_serverName3 + 058 " varchar (255) , Primary Key (" + 059 RepConstants.publication_pubName1 + ") ) "); 060 runDDL(pubName, pubsTableQuery.toString()); 061 } 062 063 protected void createSubscriptionTable(String pubName) throws RepException, 064 SQLException { 065 String subsTableQuery = " Create Table " 066 + getSubscriptionTableName() 067 + " ( " + RepConstants.subscription_subName1 + 068 " varchar(50) NOT NULL, " 069 + " " + RepConstants.subscription_pubName2 + " varchar(50) , " 070 + " " + RepConstants.subscription_conflictResolver3 + 071 " varchar(255) , " 072 + " " + RepConstants.subscription_serverName4 + " varchar (255) , " 073 + " Primary Key (" + RepConstants.subscription_subName1 + ") ) "; 074 runDDL(pubName, subsTableQuery); 075 } 076 077 protected void createBookMarkTable(String pubName) throws SQLException, 078 RepException { 079 StringBuffer bookmarkTableQuery = new StringBuffer(); 080 bookmarkTableQuery.append(" Create Table ") 081 .append(bookmark_TableName) 082 .append(" ( " + RepConstants.bookmark_LocalName1 + 083 " varchar(50) not null, " + RepConstants.bookmark_RemoteName2 + 084 " varchar(50) not null , ") 085 .append(" " + RepConstants.bookmark_TableName3 + 086 " varchar(50) not null , " + RepConstants.bookmark_lastSyncId4 + 087 " integer , ") 088 .append( 089 " " + RepConstants.bookmark_ConisderedId5 + " integer ," + 090 RepConstants.bookmark_IsDeletedTable + 091 " char(1) default 'N' , Primary Key (" + 092 RepConstants.bookmark_LocalName1 + " , " + 093 RepConstants.bookmark_RemoteName2 + " , " + 094 RepConstants.bookmark_TableName3 + ") ) "); 095 runDDL(pubName, bookmarkTableQuery.toString()); 096 } 097 098 public void createShadowTable(String pubsubName, String tableName, 099 String allColSequence,String[] primaryColumns) throws RepException { 100 StringBuffer shadowTableQuery = new StringBuffer(); 101 String shadowTableName = RepConstants.shadow_Table(tableName); 102 shadowTableQuery.append(" Create Table ") 103 .append(shadowTableName).append(" ( ") 104 .append(RepConstants.shadow_sync_id1).append(" integer ,") 105 .append(RepConstants.shadow_common_id2).append(" integer , ") 106 .append(RepConstants.shadow_operation3).append(" char(1) , ") 107 .append(RepConstants.shadow_status4).append(" char(1) ") 108 .append(allColSequence).append(" , ") 109 .append(RepConstants.shadow_serverName_n).append(" varchar(255) ") 110 .append(" , " + RepConstants.shadow_PK_Changed + " char(1) ) "); 111 StringBuffer genOnShadowTableQuery = new StringBuffer(); 112 genOnShadowTableQuery.append(" Create GENERATOR ") 113 .append(RepConstants.gen_ShadowTableName(shadowTableName)); 114 115 try { 116 runDDL(pubsubName, shadowTableQuery.toString()); 117 } 118 catch (RepException ex) { 119 throw ex; 120 } 121 catch (SQLException ex) { 122 // Ignore Exception 123 } 124 try { 125 runDDL(pubsubName, genOnShadowTableQuery.toString()); 126 } 127 catch (RepException ex) { 128 throw ex; 129 } 130 catch (SQLException ex) { 131 // Ignore Exception 132 } 133 createIndex(pubsubName,shadowTableQuery.toString()); 134 } 135 136 // Oracle 137 // create trigger abc2 after insert on a referencing new as newRow 138 // for each row 139 // declare va int ; 140 // begin 141 // Select max(a) into va from b; 142 // insert into d values ( va , :newrow.a) ; 143 // end ; 144 public void createShadowTableTriggers(String pubsubName, String tableName, 145 ArrayList colInfoList, 146 String[] primCols) throws RepException { 147 String serverName = getLocalServerName(); 148 int size = colInfoList.size(); 149 String[] colNames = new String[size]; 150 for (int i = 0; i < size; i++) { 151 colNames[i] = ( (ColumnsInfo) colInfoList.get(i)).getColumnName(); 152 } 153 String colNameSeqPrefixOldRow = getColumnNameSequence(colNames, "OLD."); 154 String colNameSeqPrefixNewRow = getColumnNameSequence(colNames, "NEW."); 155 String primColNameSeqPrefixOldRow = getColumnNameSequence(primCols,"OLD."); 156 String primColNameSeqPrefixNewRow = getColumnNameSequence(primCols,"NEW."); 157 String[] primOld = getColumnNameWithOldOrNewPrefix(primCols,"OLD."); 158 String[] primNew = getColumnNameWithOldOrNewPrefix(primCols,"NEW."); 159 StringBuffer insertLogTable = new StringBuffer(); 160 insertLogTable.append(" Insert into ") 161 .append(log_Table).append(" values ( GEN_ID(") 162 .append(RepConstants.gen_Name(log_Table) + ", 1) , '") 163 .append(tableName).append("'); "); 164 165 StringBuffer shadowTableQuery = new StringBuffer(); 166 shadowTableQuery.append(" Insert Into ") 167 .append(RepConstants.shadow_Table(tableName)).append(" ( ") 168 .append(RepConstants.shadow_sync_id1).append(", ") 169 .append(RepConstants.shadow_common_id2).append(", ") 170 .append(RepConstants.shadow_operation3).append(", ") 171 .append(RepConstants.shadow_status4).append(", ") 172 .append(getColumnNameSequence(colNames, "")) 173 .append(getColumnNameSequence(primCols, "REP_OLD_")) 174 .append(RepConstants.shadow_serverName_n).append(",") 175 .append(RepConstants.shadow_PK_Changed) 176 .append(" ) Values ( GEN_ID(") 177 .append(RepConstants.gen_ShadowTableName( 178 RepConstants.shadow_Table(tableName))).append(", 1),"); 179 180 StringBuffer insTriggerQuery = new StringBuffer(); 181 // CREATE TRIGGER TRI_INSERT FOR t1 AFTER INSERT AS BEGIN insert into logtable values(GEN_ID(GEN_USER_LOG, 1),'ServerName'); insert into t2 values(NEW.C1,NEW.C2); END; 182 insTriggerQuery.append(" Create trigger ") 183 .append(RepConstants.getInsertTriggerName(tableName)) 184 .append(" for ").append(tableName) 185 .append(" after insert as begin ") 186 .append(insertLogTable).append(shadowTableQuery.toString()) 187 .append(" null ,'I', null , ").append(colNameSeqPrefixNewRow) 188 .append(primColNameSeqPrefixNewRow) 189 .append("'").append(serverName).append("',null) ; end ; "); 190 191 StringBuffer delTriggerQuery = new StringBuffer(); 192 delTriggerQuery.append(" Create trigger ") 193 .append(RepConstants.getDeleteTriggerName(tableName)) 194 .append(" for ").append(tableName) 195 .append(" after delete as begin ") 196 .append(insertLogTable).append(shadowTableQuery.toString()) 197 .append(" null ,'D', null , ").append(colNameSeqPrefixOldRow) 198 .append(primColNameSeqPrefixOldRow) 199 .append("'").append(serverName).append("',null) ; end ; "); 200 201 StringBuffer updTriggerQuery = new StringBuffer(); 202 203 /* create trigger TRI_UPDATE for t1 after UPDATE AS declare variable maxlogid integer; 204 BEGIN FOR SELECT max(c1) FROM logtable INTO :maxlogid do begin 205 insert into logtable values(GEN_ID(GEN_USER_LOG, 1),'ServerName'); 206 insert into t2 values(:maxlogid,NEW.C2);insert into t2 values(:maxlogid,OLD.C2); end END; 207 */ 208 updTriggerQuery.append(" Create trigger ") 209 .append(RepConstants.getUpdateTriggerName(tableName)) 210 .append(" for ").append(tableName) 211 .append(" after update as declare variable maxlogid integer; declare variable pkchanged char(1); ") 212 .append(" begin FOR SELECT max(" + RepConstants.logTable_commonId1 + 213 ") FROM " + log_Table + 214 " INTO :maxlogid do begin ").append(insertLogTable) 215 .append(shadowTableQuery.toString()) 216 .append(":maxlogid,'U','B',") 217 .append(colNameSeqPrefixOldRow).append(primColNameSeqPrefixOldRow) 218 .append("'").append(serverName).append("') ; ") 219 .append(" if( "); 220 for (int i = 0; i < primOld.length; i++) { 221 if (i != 0) 222 updTriggerQuery.append(" and "); 223 updTriggerQuery.append(primOld[i] ) 224 .append("!=" ) 225 .append(primNew[i]); 226 } 227 updTriggerQuery.append(" ) Then ") 228 .append(" pkchanged :='Y'; ") 229 .append(shadowTableQuery.toString()) 230 .append(":maxlogid,'U','A',") 231 .append(colNameSeqPrefixNewRow).append(primColNameSeqPrefixOldRow) 232 .append("'").append(serverName).append("',:pkchanged) ; end end ; "); 233 234 try { 235 runDDL(pubsubName, insTriggerQuery.toString()); 236 } 237 catch (RepException ex) { 238 throw ex; 239 } 240 catch (SQLException ex) { 241 // Ignore Exception 242 } 243 try { 244 runDDL(pubsubName, delTriggerQuery.toString()); 245 } 246 catch (RepException ex) { 247 throw ex; 248 } 249 catch (SQLException ex) { 250 // Ignore Exception 251 } 252 try { 253 runDDL(pubsubName, updTriggerQuery.toString()); 254 } 255 catch (RepException ex) { 256 throw ex; 257 } 258 catch (SQLException ex) { 259 // Ignore Exception 260 } 261 262 } 263 264 // BIGINT -5 265 // [BLOB SUB_TYPE 0 -4 266 // [BLOB SUB_TYPE 1 -1 267 // [CHAR 1 268 // [NUMERIC 2 269 // [DECIMAL 3 270 // [INTEGER 4 271 // [SMALLINT 5 272 // [FLOAT 6 273 // [DOUBLE PRECISION 8 274 // [VARCHAR 12 275 // [DATE 91 276 // [TIME 92 277 // [TIMESTAMP 93 278 // [ARRAY 1111 279 // [BLOB SUB_TYPE <0 2004 280 281 public boolean isDataTypeOptionalSizeSupported(TypeInfo typeInfo) { 282 int sqlType = typeInfo.getSqlType(); 283 String typeName = typeInfo.getTypeName(); 284 switch (sqlType) { 285 case -4: 286 case 4: 287 case 5: 288 case -1: 289 case -5: 290 case 8: 291 case 91: 292 case 92: 293 case 93: 294 case 2004: 295 return false; 296 default: 297 return true; 298 } 299 } 300 301 public void setTypeInfo(TypeInfo typeInfo, ResultSet rs) throws RepException, 302 SQLException { 303 int sqlType = typeInfo.getSqlType(); 304 String typeName = typeInfo.getTypeName(); 305 //System.out.println("sqlType::" + sqlType); 306 //System.out.println("typeName::" + typeName); 307 switch (sqlType) { 308 case Types.BIT: 309 typeInfo.setTypeName("SMALLINT"); 310 break; //-7 311 case Types.TINYINT: 312 typeInfo.setTypeName("SMALLINT"); 313 break; //-6 314 case Types.SMALLINT: 315 typeInfo.setTypeName("SMALLINT"); 316 break; // 5; 317 case Types.INTEGER: 318 typeInfo.setTypeName("INTEGER"); 319 break; // 4; 320 case Types.BIGINT: 321 typeInfo.setTypeName("BIGINT"); //numeric 322 break; //-5; 323 case Types.FLOAT: 324 typeInfo.setTypeName("FLOAT"); 325 break; // 6; 326 case Types.REAL: 327 typeInfo.setTypeName("DOUBLE PRECISION"); //real 328 break; // 7; 329 case Types.DOUBLE: 330 typeInfo.setTypeName("DOUBLE PRECISION"); //double 331 break; // 8; 332 case Types.NUMERIC: 333 typeInfo.setTypeName("NUMERIC"); 334 break; // 2; 335 case Types.DECIMAL: 336 typeInfo.setTypeName("DECIMAL"); 337 break; // 3; 338 case Types.CHAR: 339 typeInfo.setTypeName("CHAR"); 340 break; // 1; 341 case Types.VARCHAR: 342 typeInfo.setTypeName("VARCHAR"); 343 break; //12 344 case Types.LONGVARCHAR: 345 typeInfo.setTypeName("BLOB SUB_TYPE 1"); 346 break; //-1; 347 case Types.DATE: 348 typeInfo.setTypeName("DATE"); 349 break; // //91; 350 case Types.TIME: 351 typeInfo.setTypeName("TIME"); 352 break; // //92; 353 case Types.TIMESTAMP: 354 typeInfo.setTypeName("TIMESTAMP"); 355 break; // //93; 356 case Types.BINARY: 357 case Types.VARBINARY: //-3; 358 case Types.LONGVARBINARY: 359 typeInfo.setTypeName("BLOB SUB_TYPE 0"); 360 break; //-4; 361 case Types.OTHER: 362 typeInfo.setTypeName("BLOB SUB_TYPE 0"); 363 break; //1111; 364 case Types.BLOB: 365 typeInfo.setTypeName("BLOB SUB_TYPE 0 "); //blob 366 break; //2004; 367 case Types.CLOB: 368 typeInfo.setTypeName("BLOB SUB_TYPE 0"); //sub type1 369 break; //2005; 370 case Types.REF: //2006; 371 case Types.JAVA_OBJECT: //2000; 372 case Types.DISTINCT: //2001; 373 case Types.NULL: // 0; 374 case Types.DATALINK: //70; 375 case Types.BOOLEAN: //16; 376 case Types.STRUCT: 377 case Types.ARRAY: 378 default: 379 throw new RepException("REP011", new Object[] {typeInfo.getTypeName()}); 380 } 381 } 382 383 public AbstractColumnObject getColumnObject(TypeInfo typeInfo) throws 384 RepException { 385 int sqlType = typeInfo.getSqlType(); 386 switch (sqlType) { 387 case -7: // number 388 case -6: // number 389 case 4: // number 390 case 5: // number 391 return new IntegerObject(sqlType,this); 392 case -5: // bigint 393 return new LongObject(sqlType,this); 394 case 1: // char 395 case 12: // varchar 396 return new StringObject(sqlType,this); 397 case 6: // float 398 case 7: // real 399 return new FloatObject(sqlType,this); 400 case 8: 401 case 2: // number 402 case 3: 403 return new DoubleObject(sqlType,this); 404 case 91: // date 405 return new DateObject(sqlType,this); 406 case 92: // date 407 return new TimeObject(sqlType,this); 408 case 93: // date 409 return new TimeStampObject(sqlType,this); 410 case 2004: // blob 411 case -1: //BLOB SUB_TYPE 1 412 return new BlobObject(sqlType,this); 413 case 2005: // clob 414 case -4: //BLOB SUB_TYPE 0 415 return new ClobObject(sqlType,this); 416 default: 417 throw new RepException("REP011", new Object[] {typeInfo.getTypeName()}); 418 } 419 } 420 421 public String getRepTableInsertQuery(String pubsubName, RepTable repTable) { 422 StringBuffer sb = new StringBuffer(); 423 String filter = repTable.getFilterClause(); 424 425 if (filter != null) { 426 if (!filter.equalsIgnoreCase("")) { 427 sb.append("insert into RepTable ").append(" values ( '") 428 .append(pubsubName).append("',GEN_ID(") 429 .append(RepConstants.gen_Name("RepTable")) 430 .append(", 1),'") 431 .append(repTable.getSchemaQualifiedName()).append("','") 432 .append(repTable.getFilterClause()).append(",") 433 .append(repTable.getConflictResolver()).append(" ) "); 434 } 435 } 436 else { 437 sb.append("insert into RepTable ").append(" ( ") 438 .append(RepConstants.repTable_pubsubName1).append(" , Table_Id , ") 439 .append(RepConstants.repTable_tableName2).append(" , ") 440 .append(RepConstants.repTable_conflict_resolver4).append(" ) ") 441 .append(" values ( '") 442 .append(pubsubName).append("',GEN_ID(") 443 .append(RepConstants.gen_Name("RepTable")) 444 .append(", 1),'") 445 .append(repTable.getSchemaQualifiedName()).append("','") 446 .append(repTable.getConflictResolver()).append("') "); 447 } 448 //System.out.println(" Query = "+sb.toString()); 449 return sb.toString(); 450 } 451 452 public void dropGenerator(Connection con, String sequenceName) throws 453 SQLException { 454 Statement stt = con.createStatement(); 455 try { 456 String dropgeneratorquery = " drop generator " + sequenceName; 457 // Seq_Shadow_TEST1 458 stt.execute(dropgeneratorquery); 459 } 460 catch (SQLException ex) { 461 } 462 stt.close(); 463 } 464 465 /** 466 * CLOB and BLOB type columns do not added in trigger definition 467 * So this type columns has been removed fom the arraylist 468 * @param dataTypeList ArrayList 469 */ 470 471 public void makeProvisionForLOBDataTypes(ArrayList dataTypeList) { 472 /** 473 * Proper handling required here 474 */ 475 476 /* ArrayList removeKeysList = null; 477 for (int i = 0, size = dataTypeList.size(); i < size; i++) { 478 ColumnsInfo ci = (ColumnsInfo) dataTypeList.get(i); 479 String dataType = ci.getDataTypeDeclaration(); 480 if (dataType.indexOf("long") != -1) { 481 if (removeKeysList == null) { 482 removeKeysList = new ArrayList(); 483 } 484 removeKeysList.add(ci); 485 } 486 } 487 if (removeKeysList != null) { 488 for (int i = 0, length = removeKeysList.size(); i < length; i++) { 489 dataTypeList.remove(removeKeysList.get(i)); 490 } 491 } */ 492 } 493 494 // references to columns of type LONG are not allowed in triggers 495 496 public boolean isColumnSizeExceedMaximumSize(TypeInfo typeInfo) throws 497 SQLException, RepException { 498 boolean flag = false; 499 int sqlType = typeInfo.getSqlType(); 500 int columnsize = typeInfo.getcolumnSize(); 501 switch (sqlType) { 502 case 12: //varchar 503 if (columnsize > 4000) { 504 flag = true; 505 break; 506 } 507 } 508 return flag; 509 } 510 511 public void setColumnPrecisionInTypeInfo(TypeInfo typeInfo, 512 ResultSetMetaData rsmt, 513 int columnIndex) throws SQLException { 514 } 515 516 public int getAppropriatePrecision(int columnSize, String datatypeName) { 517 return columnSize; 518 } 519 520 public boolean checkSchema(String schemaName) { 521 if (schemaName == null) { 522 return true; 523 } 524 if (schemas == null) { 525 schemas = new HashMap(); 526 schemas.put(schemaName.toLowerCase(), ""); 527 return false; 528 } 529 if (schemas.containsKey(schemaName.toLowerCase())) { 530 return true; 531 } 532 schemas.put(schemaName.toLowerCase(), ""); 533 return false; 534 } 535 536 //if precison is less than scale ,Exception will be thrown to user depending on subscriber databse 537 //---as suggested by Parveen Sir 538 public int getAppropriateScale(int columnScale) throws RepException { 539 return columnScale; 540 } 541 542 protected void createIndex(String pubsubName, String tableName) throws 543 RepException { 544 StringBuffer createIndexQuery = new StringBuffer(); 545 // create index ind on cmsadm2.R_S_Bank(Rep_sync_id); 546 createIndexQuery.append("create index ") 547 .append(RepConstants.Index_Name(tableName)) 548 .append(" on ") 549 .append(tableName) 550 .append("(") 551 .append(RepConstants.shadow_sync_id1) 552 .append(")"); 553 //System.out.println(" createIndexQuery : "+createIndexQuery.toString()); 554 try { 555 runDDL(pubsubName, createIndexQuery.toString()); 556 } 557 catch (RepException ex) { 558 // Ignore the Exception 559 } 560 catch (SQLException ex) { 561 // Ignore the Exception 562 } 563 } 564 565 protected void createRepTable(String pubName) throws SQLException, 566 RepException { 567 StringBuffer repTableQuery = new StringBuffer(); 568 repTableQuery.append(" Create Table ").append(rep_TableName) 569 .append(" ( " + RepConstants.repTable_pubsubName1 +" varchar(50) not null,") 570 .append(RepConstants.repTable_tableId2 +" integer , ") 571 .append(" " + RepConstants.repTable_tableName2 +" varchar(50) not null, ") 572 .append(RepConstants.repTable_filter_clause3 + " varchar(255) , ") 573 .append(RepConstants.repTable_createshadowtable6).append(" char(1) Default 'Y', ") 574 .append(RepConstants.repTable_cyclicdependency7).append(" char(1) Default 'N', ") 575 .append(" " + RepConstants.repTable_conflict_resolver4 +" varchar(255) , Primary Key (" + RepConstants.repTable_pubsubName1 +", " + RepConstants.repTable_tableName2 + " ) ) "); 576 StringBuffer genOnrepTableQuery = new StringBuffer(); 577 genOnrepTableQuery.append(" Create GENERATOR ") 578 .append(RepConstants.gen_Name(rep_TableName)); 579 runDDL(pubName, repTableQuery.toString()); 580 runDDL(pubName, genOnrepTableQuery.toString()); 581 } 582 583 protected void createSuperLogTable(String pubName) throws SQLException, 584 RepException { 585 StringBuffer logTableQuery = new StringBuffer(); 586 587 logTableQuery.append(" Create Table ") 588 .append(log_Table).append(" (") 589 .append(RepConstants.logTable_commonId1).append(" integer ,") 590 .append(RepConstants.logTable_tableName2).append(" varchar(255) ) "); 591 StringBuffer GeneratorOnlogTableQuery = new StringBuffer(); 592 GeneratorOnlogTableQuery.append(" Create GENERATOR ") 593 .append(RepConstants.gen_Name(log_Table)); 594 595 StringBuffer indexQuery =new StringBuffer(); 596 indexQuery.append("CREATE INDEX ") 597 .append(RepConstants.log_Index) 598 .append(" ON "+getLogTableName()) 599 .append("(") 600 .append(RepConstants.logTable_commonId1) 601 .append(")"); 602 //System.out.println(" Create Index on LogTable : "+indexQuery.toString()); 603 runDDL(pubName, logTableQuery.toString()); 604 runDDL(pubName, GeneratorOnlogTableQuery.toString()); 605 runDDL(pubName, indexQuery.toString()); 606 } 607 608 public void createScheduleTable(String subName) throws SQLException, 609 RepException { 610 StringBuffer ScheduleTableQuery = new StringBuffer(); 611 ScheduleTableQuery.append(" Create Table ") 612 .append(getScheduleTableName()) 613 .append(" ( " + RepConstants.schedule_Name + " varchar(50) NOT NULL , " + 614 RepConstants.subscription_subName1 + " varchar(50) NOT NULL, ") 615 .append(" " + RepConstants.schedule_type + " varchar(255) , ") 616 .append(" " + RepConstants.publication_serverName3 + " varchar (255) ," + 617 RepConstants.publication_portNo + " varchar(255) ,") 618 .append(" " + RepConstants.recurrence_type + " varchar(255) , " + 619 RepConstants.replication_type + " varchar(255) ,") 620 .append(" " + RepConstants.schedule_time + " Integer , ") 621 .append(" " + RepConstants.schedule_counter + 622 " integer , constraint rep_sch_unq unique(" + 623 RepConstants.subscription_subName1 + ") , Primary Key (" + 624 RepConstants.schedule_Name + " , " + 625 RepConstants.subscription_subName1 + ") ) "); 626 runDDL(subName, ScheduleTableQuery.toString()); 627 } 628 629 public String getScheduleTableName() { 630 return Schedule_TableName; 631 } 632 633 public String saveRepTableData(Connection connection, String pubsubName, 634 RepTable repTable) throws SQLException, 635 RepException { 636 StringBuffer sb = new StringBuffer(); 637 PreparedStatement repPreparedStatement = null; 638 String filter = repTable.getFilterClause(); 639 if (filter != null) { 640 if (!filter.equalsIgnoreCase("")) { 641 642 sb.append("insert into " + rep_TableName + 643 " ").append(" values ( ?,GEN_ID(") 644 .append(RepConstants.gen_Name(rep_TableName)).append(",1),?,?,?)"); 645 repPreparedStatement = connection.prepareStatement(sb.toString()); 646 647 repPreparedStatement.setString(1, pubsubName); 648 repPreparedStatement.setString(2, 649 repTable.getSchemaQualifiedName(). 650 toString()); 651 repPreparedStatement.setString(3, repTable.getFilterClause()); 652 repPreparedStatement.setString(4, repTable.getConflictResolver()); 653 repPreparedStatement.execute(); 654 //System.out.println("QUERY EXECUTED SUCCESSFULLY"); 655 } 656 } 657 else { 658 sb.append("insert into " + rep_TableName + " (") 659 .append(RepConstants.repTable_pubsubName1) 660 .append(" , " + RepConstants.repTable_tableId2 + " , ") 661 .append(RepConstants.repTable_tableName2).append(" , ") 662 .append(RepConstants.repTable_conflict_resolver4).append(" ) ") 663 .append(" values ( ?,GEN_ID(") 664 .append(RepConstants.gen_Name(rep_TableName)).append(",1),?,?)"); 665 //System.out.println(" sb.toString() ="+sb.toString().toUpperCase()); 666 repPreparedStatement = connection.prepareStatement(sb.toString()); 667 repPreparedStatement.setString(1, pubsubName); 668 repPreparedStatement.setString(2, 669 repTable.getSchemaQualifiedName().toString()); 670 repPreparedStatement.setString(3, repTable.getConflictResolver()); 671 repPreparedStatement.execute(); 672 // System.out.println("QUERY EXECUTED SUCCESSFULLY"); 673 674 } 675 try { 676 repPreparedStatement.close(); 677 } 678 catch (SQLException ex) { 679 //sqlexception must be ignored 680 } 681 return sb.toString(); 682 } 683 684 public void dropPublisherSystemTables(Connection con) { 685 try { 686 fireDropQuery(con, " drop table " + publication_TableName); 687 fireDropQuery(con, " drop table " + bookmark_TableName); 688 fireDropQuery(con, " drop table " + rep_TableName); 689 fireDropQuery(con, " drop table " + log_Table); 690 fireDropQuery(con, " drop table " + getIgnoredColumns_Table()); 691 fireDropQuery(con, " drop table " + getTrackReplicationTablesUpdation_Table()); 692 //drop generators on reptable and logtable 693 fireDropQuery(con, 694 " drop generator " + RepConstants.gen_Name(rep_TableName)); 695 fireDropQuery(con, " drop generator " + RepConstants.gen_Name(log_Table)); 696 } 697 catch (Exception ex) { 698 //exception must be ignored 699 } 700 } 701 702 public void dropSubscriberSystemTables(Connection con) { 703 try { 704 fireDropQuery(con, " drop table " + subscription_TableName); 705 fireDropQuery(con, " drop table " + bookmark_TableName); 706 fireDropQuery(con, " drop table " + rep_TableName); 707 fireDropQuery(con, " drop table " + log_Table); 708 fireDropQuery(con, " drop table " + Schedule_TableName); 709 fireDropQuery(con, " drop table " + getIgnoredColumns_Table()); 710 fireDropQuery(con, " drop table " + getTrackReplicationTablesUpdation_Table()); 711 //drop generators on reptable and logtable 712 fireDropQuery(con, 713 " drop generator " + RepConstants.gen_Name(rep_TableName)); 714 fireDropQuery(con, " drop generator " + RepConstants.gen_Name(log_Table)); 715 } 716 catch (Exception ex) { 717 //exception must be ignored 718 } 719 } 720 721 public void deleteRecordsFromSuperLogTable(Statement subStatment) throws 722 SQLException { 723 // insert one record in superLogTable 724 725 StringBuffer query = new StringBuffer(); 726 query.append("insert into ").append(log_Table).append( 727 " values (GEN_ID(") 728 .append(RepConstants.gen_Name(rep_TableName)).append(",1),'$$$$$$')"); 729 subStatment.execute(query.toString()); 730 731 query = new StringBuffer(); 732 // deleting all but one last record from super log table where commonid is maximum 733 query.append("Select max (").append(RepConstants.logTable_commonId1). 734 append(") from ").append(log_Table); 735 ResultSet rs = subStatment.executeQuery(query.toString()); 736 rs.next(); 737 long maxCID = rs.getLong(1); 738 739 query = new StringBuffer(); 740 741 query.append("delete from ").append(log_Table).append( 742 " where ") 743 .append(RepConstants.logTable_commonId1).append(" !=").append(maxCID); 744 log.debug(query.toString()); 745 subStatment.executeUpdate(query.toString()); 746 log.debug("Query executed succssfully"); 747 } 748 749 public void dropGenerators(Connection con, String generatorName) throws 750 SQLException { 751 Statement stt = con.createStatement(); 752 try { 753 String dropGeneratorquery = " drop generator " + generatorName; 754 log.debug(dropGeneratorquery); 755 stt.execute(dropGeneratorquery); 756 log.debug("Query executed succssfully"); 757 } 758 catch (SQLException ex) { 759 } 760 finally { 761 if (stt != null) 762 stt.close(); 763 } 764 } 765 766 public PreparedStatement makePrimaryPreperedStatement(Connection pub_sub_Connection, String[] 767 primaryColumns, String shadowTable, String local_pub_sub_name) throws 768 SQLException, RepException { 769 StringBuffer query = new StringBuffer(); 770 query.append(" select FIRST 1 SKIP 0 * from "); 771 query.append(shadowTable); 772 query.append(" where "); 773 query.append(RepConstants.shadow_sync_id1); 774 query.append(" > "); 775 query.append("? "); 776 for (int i = 0; i < primaryColumns.length; i++) { 777 query.append(" and "); 778 query.append(primaryColumns[i]); 779 query.append("= ? "); 780 } 781 query.append(" order by " + RepConstants.shadow_sync_id1); 782 //System.out.println("OracleHandler.makePrimaryPreperedStatement(primaryColumns, shadowTable, local_pub_sub_name) : "+query.toString()); 783 return pub_sub_Connection.prepareStatement(query.toString()); 784 } 785 786 787 788 /** 789 * isPrimaryKeyException 790 * 791 * @param ex SQLException 792 * @return boolean 793 */ 794 public boolean isPrimaryKeyException(SQLException ex) { 795 // System.out.println("FireBird Hanlder ERRRRRRRORR CODE ::::: "+ex.getErrorCode()); 796 return ex.getErrorCode()==335544665 ? true:false; 797 } 798 799 /** 800 * isForeignKeyException 801 * 802 * @param ex SQLException 803 * @return boolean 804 */ 805 public boolean isForeignKeyException(SQLException ex) { 806 return false; 807 } 808 809 810 811 protected void createTrackReplicationTablesUpdationTable(String pubSubName) throws 812 RepException, SQLException { 813 StringBuffer trackRepTablesUpdationQuery = new StringBuffer(); 814 trackRepTablesUpdationQuery.append(" CREATE TABLE ").append(getTrackReplicationTablesUpdation_Table()).append(" ( " + 815 RepConstants.trackUpdation + " SMALLINT NOT NULL PRIMARY KEY) "); 816 runDDL(pubSubName, trackRepTablesUpdationQuery.toString()); 817 runDDL(pubSubName,"Insert into "+getTrackReplicationTablesUpdation_Table()+" values(1)" ); 818 } 819 820 821 protected void createIgnoredColumnsTable(String pubName) throws SQLException, RepException { 822 StringBuffer ignoredColumnsQuery = new StringBuffer(); 823 ignoredColumnsQuery.append(" Create Table ").append(getIgnoredColumns_Table()).append(" ( ") 824 .append(RepConstants.ignoredColumnsTable_tableId1).append(" INTEGER NOT NULL , ") 825 .append(RepConstants.ignoredColumnsTable_ignoredcolumnName2).append(" varchar(150) NOT NULL , ") 826 .append(" Primary Key (").append(RepConstants.ignoredColumnsTable_tableId1).append(" , ") 827 .append(RepConstants.ignoredColumnsTable_ignoredcolumnName2).append(" ) ) "); 828 runDDL(pubName, ignoredColumnsQuery.toString()); 829 } 830 //implement this method for providing provision to stop updations done on shadow table 831 protected void createTriggerForTrackReplicationTablesUpdationTable(String 832 pubSubName) throws RepException, SQLException { 833 /* StringBuffer trackRepTablesUpdationTriggerQuery = new StringBuffer(); 834 trackRepTablesUpdationTriggerQuery.append(" CREATE TRIGGER TRI_") 835 .append(getTrackReplicationTablesUpdation_Table()).append( 836 " ON " + getTrackReplicationTablesUpdation_Table()) 837 .append(" AFTER INSERT AS DELETE FROM " + 838 getTrackReplicationTablesUpdation_Table() + " WHERE ") 839 .append(RepConstants.trackUpdation + " NOT IN(SELECT * FROM inserted)"); 840 runDDL(pubSubName, trackRepTablesUpdationTriggerQuery.toString());*/ 841 } 842 843 public Object getMinValOfSyncIdTodeleteRecordsFromShadowTable(String 844 tableName, Statement stmt) throws SQLException { 845 ResultSet rsSyncId = null, rsConsideredId = null; 846 // selecting min of syncid or concideredId from bookmarks table for one table 847 try { 848 StringBuffer query = new StringBuffer(); 849 query.append("Select min(").append(RepConstants. 850 bookmark_lastSyncId4).append(") from "). 851 append( 852 getBookMarkTableName()).append(" where ").append( 853 RepConstants. 854 bookmark_TableName3).append(" = '").append(tableName).append("'"); 855 rsSyncId = stmt.executeQuery(query.toString()); 856 rsSyncId.next(); 857 int minSyncId = rsSyncId.getInt(1); 858 859 StringBuffer queryForMinConsideredId = new StringBuffer(); 860 queryForMinConsideredId.append("Select min(").append(RepConstants. 861 bookmark_ConisderedId5).append(") from ").append( 862 getBookMarkTableName()).append(" where ").append( 863 RepConstants. 864 bookmark_TableName3).append(" = '").append(tableName).append("'"); 865 rsConsideredId = stmt.executeQuery(queryForMinConsideredId.toString()); 866 rsConsideredId.next(); 867 int minConsideredId = rsConsideredId.getInt(1); 868 869 if (minSyncId < minConsideredId) { 870 return new Integer(minSyncId); 871 } 872 else { 873 return new Integer(minConsideredId); 874 } 875 } 876 finally { 877 if (rsSyncId != null) 878 try { 879 rsSyncId.close(); 880 } 881 catch (SQLException ex) { 882 //ignore SQLException 883 } 884 if (rsConsideredId != null) 885 try { 886 rsConsideredId.close(); 887 } 888 catch (SQLException ex) { 889 //ignore SQLException 890 } 891 } 892 } 893 894 public PreparedStatement makePrimaryPreperedStatementBackwardTraversing(String[] primaryColumns, long lastId, String local_pub_sub_name,String shadowTable) throws SQLException, RepException { 895 StringBuffer query = new StringBuffer(); 896 query.append(" select FIRST 1 SKIP 0 * from ") 897 .append(shadowTable) 898 .append(" where ") 899 .append(RepConstants.shadow_sync_id1) 900 .append(" < ? ") 901 .append(" and ") 902 .append(RepConstants.shadow_sync_id1) 903 .append(" > ") 904 .append(lastId); 905 for (int i = 0; i < primaryColumns.length; i++) { 906 query.append(" and ") 907 .append(primaryColumns[i]) 908 .append(" = ? "); 909 } 910 query.append(" order by ") 911 .append(RepConstants.shadow_sync_id1) 912 .append(" desc "); 913 log.debug(query.toString()); 914 // System.out.println("FireBirdDatabaseHandler makePrimaryPreperedStatementDelete :: " +query.toString()); 915 Connection pub_sub_Connection = connectionPool.getConnection(local_pub_sub_name); 916 return pub_sub_Connection.prepareStatement(query.toString()); 917 } 918 919 /** 920 * isSchemaSupported 921 * Returing false because FireBird database does not support schema 922 * @return boolean 923 */ 924 public boolean isSchemaSupported() { 925 return false; 926 } 927 928 }
Sponsors
