001 /** 002 * Copyright (c) 2003 Daffodil Software Ltd all rights reserved, 003 * Modifications Copyright (c) 2008 Regiscope Digital Imaging Co, LLC, All rights reserved. 004 * This program is free software; you can redistribute it and/or modify 005 * it under the terms of version 2 of the GNU General Public License as 006 * published by the Free Software Foundation. 007 * There are special exceptions to the terms and conditions of the GPL 008 * as it is applied to this software. See the GNU General Public License for more details. 009 * 010 * This program is distributed in the hope that it will be useful, 011 * but WITHOUT ANY WARRANTY; without even the implied warranty of 012 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 013 * GNU General Public License for more details. 014 * 015 * You should have received a copy of the GNU General Public License 016 * along with this program; if not, write to the Free Software 017 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA 018 */ 019 020 package org.dbreplicator.replication.DBHandler; 021 022 import java.sql.*; 023 import java.util.*; 024 025 import org.dbreplicator.replication.*; 026 import org.dbreplicator.replication.column.*; 027 import org.apache.log4j.Logger; 028 import java.io.StringBufferInputStream; 029 030 /** 031 * Method overrides specific to Oracle. 032 */ 033 public class OracleHandler 034 extends AbstractDataBaseHandler { 035 protected static Logger log = Logger.getLogger(OracleHandler.class.getName()); 036 037 public OracleHandler() {} 038 039 public OracleHandler(ConnectionPool connectionPool0) { 040 connectionPool = connectionPool0; 041 vendorType = Utility.DataBase_Oracle; 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 //create table test (id number, testdata varchar2(255)); 050 //create sequence test_seq start with 1 increment by 1 nomaxvalue; 051 // create trigger test_trigger before insert on test for each row 052 // begin select test_seq.nextval into :new.id from dual; end; 053 054 protected void createSuperLogTable(String pubName) throws SQLException, 055 RepException { 056 StringBuffer logTableQuery = new StringBuffer(); 057 logTableQuery.append(" Create Table ") 058 .append(log_Table).append(" (") 059 .append(RepConstants.logTable_commonId1).append(" number ,") 060 .append(RepConstants.logTable_tableName2).append(" varchar(255) ) "); 061 try{ 062 runDDL(pubName, logTableQuery.toString()); 063 } 064 catch (RepException ex) { 065 throw ex; 066 } 067 catch (SQLException ex) { 068 // System.out.println("Error Code==::"+ex.getErrorCode()); 069 if(ex.getErrorCode()!=955) 070 throw new RepException("REP999", new String[] {log_Table, 071 ex.getMessage()}); 072 073 } 074 075 } 076 077 public void CreateSequenceOnLogTable(String pubsubName) throws SQLException, 078 RepException { 079 StringBuffer seqOnlogTableQuery = new StringBuffer(); 080 seqOnlogTableQuery.append(" Create SEQUENCE ") 081 .append(RepConstants.seq_Name(log_Table)) 082 .append(" start with 1 increment by 1 nomaxvalue "); 083 runDDL(pubsubName, seqOnlogTableQuery.toString()); 084 StringBuffer indexQuery = new StringBuffer(); 085 indexQuery.append("CREATE INDEX ") 086 .append(RepConstants.log_Index) 087 .append(" ON " + getLogTableName()) 088 .append("(") 089 .append(RepConstants.logTable_commonId1) 090 .append(")"); 091 //System.out.println(" Create Index on LogTable : " + indexQuery.toString()); 092 try{ 093 runDDL(pubsubName, indexQuery.toString()); 094 } 095 catch (RepException ex) { 096 throw ex; 097 } 098 catch (SQLException ex) { 099 100 } 101 102 } 103 104 /** 105 * Because changes has been made in structure of RepTable 106 * by Hisar team.So old method has been commented. After 107 * proper testing with all data base it should be deleted. 108 */ 109 110 /* protected void createRepTable(String pubName) throws SQLException, 111 RepException 112 { 113 114 StringBuffer repTableQuery = new StringBuffer(); 115 repTableQuery.append(" Create Table ").append(rep_TableName) 116 .append(" ( "+RepConstants.repTable_pubsubName1+" 117 varchar(255) , "+RepConstants.repTable_tableId2+ 118 " int , ") 119 .append(" "+RepConstants.repTable_tableName2+" varchar(255) , "+RepConstants.repTable_filter_clause3+" varchar(255) , ") 120 .append( 121 " "+RepConstants.repTable_conflict_resolver4+" varchar(255) , Primary Key ( "+RepConstants.repTable_pubsubName1+" , "+RepConstants.repTable_tableName2+" ) ) "); 122 runDDL(pubName, repTableQuery.toString()); 123 } */ 124 125 protected void createRepTable(String pubName) throws SQLException, 126 RepException { 127 StringBuffer repTableQuery = new StringBuffer(); 128 repTableQuery.append(" Create Table ").append(getRepTableName()).append( 129 " ( ") 130 .append(RepConstants.repTable_pubsubName1).append(" varchar(255) , ") 131 .append(RepConstants.repTable_tableId2).append(" int , ") 132 .append(RepConstants.repTable_tableName2).append(" varchar(255) , ") 133 .append(RepConstants.repTable_filter_clause3).append( 134 " varchar(255) , ") 135 .append(RepConstants.repTable_createshadowtable6).append( 136 " char(1) Default 'Y', ") 137 .append(RepConstants.repTable_cyclicdependency7).append( 138 " char(1) Default 'N', ") 139 .append(RepConstants.repTable_conflict_resolver4).append( 140 " varchar(255), ") 141 .append(" Primary Key (").append(RepConstants.repTable_pubsubName1). 142 append(" , ") 143 .append(RepConstants.repTable_tableName2).append(" ) ) "); 144 try{ 145 runDDL(pubName, repTableQuery.toString()); 146 } 147 catch (RepException ex) { 148 throw ex; 149 } 150 catch (SQLException ex) { 151 //System.out.println("Error Code==::"+ex.getErrorCode()); 152 if(ex.getErrorCode()!=955) 153 throw new RepException("REP999", new String[] {rep_TableName,ex.getMessage()}); 154 155 } 156 157 } 158 159 public void CreateSequenceOnRepTable(String pubsubName) throws SQLException, 160 RepException { 161 StringBuffer seqOnrepTableQuery = new StringBuffer(); 162 seqOnrepTableQuery.append(" Create SEQUENCE ") 163 .append(RepConstants.seq_Name(rep_TableName)) 164 .append(" start with 1 increment by 1 nomaxvalue "); 165 try{ 166 runDDL(pubsubName, seqOnrepTableQuery.toString()); 167 } 168 catch (RepException ex) { 169 throw ex; 170 } 171 catch (SQLException ex) { 172 173 } 174 175 } 176 177 protected void createBookMarkTable(String pubName) throws SQLException, 178 RepException { 179 StringBuffer bookmarkTableQuery = new StringBuffer(); 180 bookmarkTableQuery.append(" Create Table ") 181 .append(bookmark_TableName) 182 .append(" ( " + RepConstants.bookmark_LocalName1 + 183 " varchar(255) , " + RepConstants.bookmark_RemoteName2 + 184 " varchar(255) , ") 185 .append(" " + RepConstants.bookmark_TableName3 + " varchar(255) , " + 186 RepConstants.bookmark_lastSyncId4 + " NUMBER , ") 187 .append( 188 " " + RepConstants.bookmark_ConisderedId5 + " NUMBER," + 189 RepConstants.bookmark_IsDeletedTable + 190 " char(1) default 'N' , Primary Key ( " + 191 RepConstants.bookmark_LocalName1 + " , " + 192 RepConstants.bookmark_RemoteName2 + " , " + 193 RepConstants.bookmark_TableName3 + " ) ) "); 194 try{ 195 runDDL(pubName, bookmarkTableQuery.toString()); 196 } 197 catch (RepException ex) { 198 throw ex; 199 } 200 catch (SQLException ex) { 201 // System.out.println("Error Code==::"+ex.getErrorCode()); 202 if(ex.getErrorCode()!=955) 203 throw new RepException("REP999", new String[] {bookmark_TableName, 204 ex.getMessage()}); 205 206 } 207 208 } 209 210 public void createScheduleTable(String subName) throws SQLException, 211 RepException { 212 StringBuffer ScheduleTableQuery = new StringBuffer(); 213 ScheduleTableQuery.append(" Create Table ") 214 .append(getScheduleTableName()) 215 .append(" ( " + RepConstants.schedule_Name + " varchar(255) , " + 216 RepConstants.subscription_subName1 + " varchar(255) unique , ") 217 .append(" " + RepConstants.schedule_type + " varchar(255) , ") 218 .append(" " + RepConstants.publication_serverName3 + " varchar (255) ," + 219 RepConstants.publication_portNo + " varchar(255) ,") 220 .append(" " + RepConstants.recurrence_type + " varchar(255) , " + 221 RepConstants.replication_type + " varchar(255) ,") 222 .append(" " + RepConstants.schedule_time + " NUMBER , ") 223 .append(" " + RepConstants.schedule_counter + " NUMBER , Primary Key (" + 224 RepConstants.schedule_Name + " , " + 225 RepConstants.subscription_subName1 + ") ) "); 226 try { 227 runDDL(subName, ScheduleTableQuery.toString()); 228 } 229 catch (RepException ex) { 230 throw ex; 231 } 232 catch (SQLException ex) { 233 //System.out.println("Error Code==::"+ex.getErrorCode()); 234 if(ex.getErrorCode()!=955) 235 throw new RepException("REP999", new String[] {Schedule_TableName,ex.getMessage()}); 236 237 } 238 } 239 240 public void createShadowTable(String pubsubName, String tableName, 241 String allColSequence,String[] primaryColumns) throws RepException { 242 StringBuffer shadowTableQuery = new StringBuffer(); 243 String shadowTableName = RepConstants.shadow_Table(tableName); 244 shadowTableQuery.append(" Create Table ") 245 .append(shadowTableName).append(" ( ") 246 .append(RepConstants.shadow_sync_id1).append(" number ,") 247 .append(RepConstants.shadow_common_id2).append(" number , ") 248 .append(RepConstants.shadow_operation3).append(" char(1) , ") 249 .append(RepConstants.shadow_status4).append(" char(1) ") 250 .append(allColSequence).append(" , ") 251 .append(RepConstants.shadow_serverName_n).append(" varchar(255) , ") 252 .append(RepConstants.shadow_PK_Changed).append(" char(1) )"); 253 254 StringBuffer seqOnShadowTableQuery = new StringBuffer(); 255 seqOnShadowTableQuery.append(" Create SEQUENCE ") 256 .append(RepConstants.seq_ShadowTableName(shadowTableName)) 257 .append(" start with 1 increment by 1 nomaxvalue "); 258 259 try { 260 runDDL(pubsubName, shadowTableQuery.toString()); 261 } 262 catch (RepException ex) { 263 throw ex; 264 } 265 catch (SQLException ex) { 266 // System.out.println("Error Code==::"+ex.getErrorCode()); 267 if(ex.getErrorCode()!=955) 268 throw new RepException("REP999", new String[] {shadowTableName, 269 ex.getMessage()}); 270 } 271 try { 272 runDDL(pubsubName, seqOnShadowTableQuery.toString()); 273 } 274 catch (RepException ex) { 275 throw ex; 276 } 277 catch (SQLException ex) { 278 // Ignore Exception 279 } 280 createIndex(pubsubName, RepConstants.shadow_Table(tableName)); 281 } 282 283 // Oracle 284 // create trigger abc2 after insert on a referencing new as newRow 285 // for each row 286 // declare va int ; 287 // begin 288 // Select max(a) into va from b; 289 // insert into d values ( va , :newrow.a) ; 290 // end ; 291 292 public void createShadowTableTriggers(String pubsubName, String tableName, 293 ArrayList colInfoList, 294 String[] primCols) throws RepException { 295 String serverName = getLocalServerName(); 296 int size = colInfoList.size(); 297 String[] colNames = new String[size]; 298 for (int i = 0; i < size; i++) { 299 colNames[i] = ( (ColumnsInfo) colInfoList.get(i)).getColumnName(); 300 } 301 String colNameSeqPrefixOldRow = getColumnNameSequence(colNames, ":oldRow."); 302 String colNameSeqPrefixNewRow = getColumnNameSequence(colNames, ":newRow."); 303 String primColNameSeqPrefixOldRow = getColumnNameSequence(primCols,":oldRow."); 304 String primColNameSeqPrefixNewRow = getColumnNameSequence(primCols,":newRow."); 305 306 String primColWithOldPrefix[] =addPrefixWithColumnName(primCols,":oldRow."); 307 String primColWithNewPrefix[] =addPrefixWithColumnName(primCols,":newRow."); 308 309 //// 310 StringBuffer insertLogTable = new StringBuffer(); 311 insertLogTable.append(" Insert into ") 312 .append(log_Table).append(" values ( ") 313 .append(RepConstants.seq_Name(log_Table) + ".nextVal , '") 314 .append(tableName).append("'); "); 315 316 StringBuffer shadowTableQuery = new StringBuffer(); 317 shadowTableQuery.append(" Insert Into ") 318 .append(RepConstants.shadow_Table(tableName)).append(" ( ") 319 .append(RepConstants.shadow_sync_id1).append(", ") 320 .append(RepConstants.shadow_common_id2).append(", ") 321 .append(RepConstants.shadow_operation3).append(", ") 322 .append(RepConstants.shadow_status4).append(", ") 323 .append(getColumnNameSequence(colNames, "")) 324 .append(getColumnNameSequence(primCols, "rep_old_")) 325 .append(RepConstants.shadow_serverName_n) 326 .append(" , ").append(RepConstants.shadow_PK_Changed ).append(" ) Values ( ") 327 .append(RepConstants.seq_ShadowTableName( 328 RepConstants.shadow_Table(tableName))).append(".nextVal"); 329 330 StringBuffer insTriggerQuery = new StringBuffer(); 331 insTriggerQuery.append(" Create trigger ") 332 .append(RepConstants.getInsertTriggerName(tableName)) 333 .append(" after insert on ").append(tableName) 334 .append(" Referencing new as newRow For each Row begin ") 335 .append(insertLogTable).append(shadowTableQuery.toString()) 336 .append(", null ,'I', null , ").append(colNameSeqPrefixNewRow) 337 .append(primColNameSeqPrefixNewRow) 338 .append("'").append(serverName).append("',null) ; end ; "); 339 340 StringBuffer delTriggerQuery = new StringBuffer(); 341 delTriggerQuery.append(" Create trigger ") 342 .append(RepConstants.getDeleteTriggerName(tableName)) 343 .append(" after delete on ").append(tableName) 344 .append(" Referencing old as oldRow For each Row begin ") 345 .append(insertLogTable).append(shadowTableQuery.toString()) 346 .append(", null ,'D', null , ").append(colNameSeqPrefixOldRow) 347 .append(primColNameSeqPrefixOldRow) 348 .append("'").append(serverName).append("',null) ; end ; "); 349 350 StringBuffer updTriggerQuery = new StringBuffer(); 351 updTriggerQuery.append(" Create trigger ") 352 .append(RepConstants.getUpdateTriggerName(tableName)) 353 .append(" after update on ").append(tableName) 354 .append(" Referencing new as newRow old as oldRow For each Row ") 355 .append(" declare maxlogid number; pkchanged char(1); begin ") 356 .append(insertLogTable) 357 .append(" Select max(" + RepConstants.logTable_commonId1 + 358 ") into maxlogid from ") 359 .append(log_Table).append("; ") 360 .append(" if( "); 361 for (int i = 0; i < primColWithOldPrefix.length; i++) { 362 if (i != 0) 363 updTriggerQuery.append(" and "); 364 updTriggerQuery.append(primColWithOldPrefix[i]) 365 .append("!= ") 366 .append(primColWithNewPrefix[i]); 367 } 368 updTriggerQuery.append(" ) THEN pkChanged := 'Y'; END IF; ") 369 .append(shadowTableQuery.toString()).append(",maxlogid,'U','B',") 370 .append(colNameSeqPrefixOldRow).append(primColNameSeqPrefixOldRow) 371 .append("'").append(serverName).append("',null) ; ") 372 .append(shadowTableQuery.toString()).append(", maxlogid,'U','A',") 373 .append(colNameSeqPrefixNewRow).append(primColNameSeqPrefixOldRow) 374 .append("'").append(serverName).append("',pkChanged) ; ") 375 .append(" end ; "); 376 //System.out.println(" Oracle update trigger query is : "+updTriggerQuery.toString()); 377 378 try { 379 runDDL(pubsubName, insTriggerQuery.toString()); 380 } 381 catch (RepException ex) { 382 throw ex; 383 } 384 catch (SQLException ex) { 385 //System.out.println("OracleHandler.createShadowTableTriggers() : "+ex.getErrorCode()); 386 if(ex.getErrorCode()!=4081) { 387 throw new RepException("REP999", new String[] {Schedule_TableName,ex.getMessage()}); 388 } 389 // Ignore Exception 390 } 391 try { 392 runDDL(pubsubName, delTriggerQuery.toString()); 393 } 394 catch (RepException ex) { 395 throw ex; 396 } 397 catch (SQLException ex) { 398 if(ex.getErrorCode()!=4081) { 399 throw new RepException("REP999", new String[] {Schedule_TableName,ex.getMessage()}); 400 } 401 402 // Ignore Exception 403 } 404 try { 405 runDDL(pubsubName, updTriggerQuery.toString()); 406 } 407 catch (RepException ex) { 408 throw ex; 409 } 410 catch (SQLException ex) { 411 if(ex.getErrorCode()!=4081) { 412 throw new RepException("REP999", new String[] {Schedule_TableName,ex.getMessage()}); 413 } 414 // Ignore Exception 415 } 416 417 } 418 419 public boolean isDataTypeOptionalSizeSupported(TypeInfo typeInfo) { 420 int sqlType = typeInfo.getSqlType(); 421 String typeName = typeInfo.getTypeName(); 422 switch (sqlType) { 423 case -4: 424 case -1: 425 case 7: 426 case 91: 427 case 92: 428 case 93: 429 case 2002: 430 case 2003: 431 case 2004: 432 case 2005: 433 case 2006: 434 case 1111: // Special Handling for 1111 435 return false; 436 default: 437 return true; 438 } 439 } 440 441 public void setTypeInfo(TypeInfo typeInfo, ResultSet rs) throws RepException, 442 SQLException { 443 int sqlType = typeInfo.getSqlType(); 444 String typeName = typeInfo.getTypeName(); 445 switch (sqlType) { 446 case Types.BIT: 447 typeInfo.setTypeName("number"); 448 break; //-7; 449 case Types.TINYINT: 450 typeInfo.setTypeName("number"); 451 break; //-6; 452 case Types.SMALLINT: 453 typeInfo.setTypeName("number"); 454 break; // 5; 455 case Types.INTEGER: 456 typeInfo.setTypeName("number"); 457 break; // 4; 458 case Types.BIGINT: 459 typeInfo.setTypeName("number"); 460 break; //-5; 461 case Types.FLOAT: 462 typeInfo.setTypeName("float"); 463 break; // 6; 464 case Types.REAL: 465 typeInfo.setTypeName("real"); 466 break; // 7; 467 case Types.DOUBLE: 468 typeInfo.setTypeName("number"); 469 break; // 8; 470 case Types.NUMERIC: 471 typeInfo.setTypeName("number"); 472 break; // 2; 473 case Types.DECIMAL: 474 typeInfo.setTypeName("number"); 475 break; // 3; 476 case Types.CHAR: 477 typeInfo.setTypeName("char"); 478 break; // 1; 479 case Types.VARCHAR: 480 if (typeName.equalsIgnoreCase("sql_variant")) { 481 typeInfo.setTypeName("clob"); 482 typeInfo.setSqlType(2005); 483 return; 484 } 485 else if (typeName.equalsIgnoreCase("text")) { 486 typeInfo.setTypeName("clob"); 487 typeInfo.setSqlType(2005); 488 return; 489 } 490 else { 491 typeInfo.setTypeName("varchar2"); 492 break; //12 493 } 494 case Types.LONGVARCHAR: 495 if (typeName.equalsIgnoreCase("text")) { 496 typeInfo.setTypeName("clob"); 497 typeInfo.setSqlType(2005); 498 break; 499 } 500 typeInfo.setTypeName("long"); 501 502 break; //-1; 503 case Types.DATE: 504 typeInfo.setTypeName("date"); 505 break; // //91; 506 case Types.TIME: 507 typeInfo.setTypeName("date"); 508 break; // //92; 509 case Types.TIMESTAMP: 510 typeInfo.setTypeName("timestamp"); 511 break; // //93; 512 case Types.BINARY: 513 typeInfo.setTypeName("blob"); 514 // typeInfo.setTypeName("raw"); 515 break; //-2; 516 case Types.VARBINARY: 517 typeInfo.setTypeName("blob"); 518 // typeInfo.setTypeName("raw"); 519 break; //-3; 520 case Types.LONGVARBINARY: 521 typeInfo.setTypeName("blob"); 522 typeInfo.setSqlType(2004); 523 break; //-4; // sachin 524 //typeInfo.setTypeName("number");break;//-4; 525 case Types.OTHER: 526 if (typeInfo.getTypeName().equalsIgnoreCase("FLOAT")) { 527 typeInfo.setTypeName("FLOAT"); 528 typeInfo.setSqlType(Types.FLOAT); 529 return; // 6 530 } 531 if (typeInfo.getTypeName().equalsIgnoreCase("BLOB")) { 532 typeInfo.setTypeName("BLOB"); 533 typeInfo.setSqlType(Types.BLOB); 534 return; 535 } 536 typeInfo.setTypeName("clob"); 537 break; //1111; 538 case Types.STRUCT: 539 typeInfo.setTypeName("struct"); 540 break; //2002; 541 case Types.ARRAY: 542 typeInfo.setTypeName("array"); 543 break; //2003; 544 case Types.BLOB: 545 typeInfo.setTypeName("blob"); 546 break; //2004; 547 case Types.CLOB: 548 typeInfo.setTypeName("clob"); 549 break; //2005; 550 case Types.REF: 551 typeInfo.setTypeName("ref"); 552 break; //2006; 553 case Types.JAVA_OBJECT: 554 typeInfo.setTypeName("clob"); 555 break; //2000; 556 557 case Types.DISTINCT: //2001; 558 case Types.NULL: // 0; 559 case Types.DATALINK: //70; 560 case Types.BOOLEAN: //16; 561 default: 562 throw new RepException("REP031", new Object[] {typeInfo.getTypeName()}); 563 } 564 } 565 566 public AbstractColumnObject getColumnObject(TypeInfo typeInfo) throws 567 RepException { 568 int sqlType = typeInfo.getSqlType(); 569 switch (sqlType) { 570 case -7: // number 571 case -6: // number 572 case -5: // number 573 case 4: // number 574 case 5: // number 575 case 3: // number /// Added by sachin 576 return new IntegerObject(sqlType, this); 577 case -4: // Long Raw 578 case -3: // raw 579 case -1: // long 580 return new LongObject(sqlType, this); 581 case 1: // char 582 case 12: // varchar 583 case 2002: // strcut 584 case 2003: // array 585 return new StringObject(sqlType, this); 586 case 6: // float 587 return new FloatObject(sqlType, this); 588 case 7: // real 589 case 8: 590 case 2: // number 591 return new DoubleObject(sqlType, this); 592 593 case 93: // date 594 // if(typeInfo.getTypeName().equalsIgnoreCase("timestamp")) 595 return new TimeStampObject(sqlType, this); 596 // return new DateObject(sqlType, this); 597 case 91: 598 return new DateObject(sqlType, this); 599 case 2004: // blob 600 return new BlobObject(sqlType, this); 601 case 2005: // clob 602 return new ClobStreamObject(sqlType, this); 603 // case 2006: // ref 604 default: 605 throw new RepException("REP031", new Object[] {typeInfo.getTypeName()}); 606 } 607 } 608 609 public String saveRepTableData(Connection connection, String pubsubName, 610 RepTable repTable) throws SQLException, 611 RepException { 612 StringBuffer sb = new StringBuffer(); 613 PreparedStatement repPreparedStatement = null; 614 String filter = repTable.getFilterClause(); 615 if (filter != null) { 616 if (!filter.equalsIgnoreCase("")) { 617 // sb.append("insert into "+RepConstants.rep_TableName+" ").append(" values ( '") 618 // .append(pubsubName).append("',") 619 // .append(RepConstants.seq_Name(RepConstants.rep_TableName)).append(".nextVal, '") 620 // .append(repTable.getSchemaQualifiedName()).append("','") 621 // .append(repTable.getFilterClause()).append("','") 622 // .append(repTable.getConflictResolver()).append("' ) "); 623 624 sb.append("insert into " + RepConstants.rep_TableName + 625 " ").append(" values ( ?,") 626 .append(RepConstants.seq_Name(RepConstants.rep_TableName)).append( 627 ".nextVal,?,?,?)"); 628 //System.out.println(" sb.toString() ="+sb.toString().toUpperCase()); 629 repPreparedStatement = connection.prepareStatement(sb.toString()); 630 631 repPreparedStatement.setString(1, pubsubName); 632 repPreparedStatement.setString(2, 633 repTable.getSchemaQualifiedName().toString()); 634 repPreparedStatement.setString(3, repTable.getFilterClause()); 635 repPreparedStatement.setString(4, repTable.getConflictResolver()); 636 repPreparedStatement.execute(); 637 //System.out.println("QUERY EXECUTED SUCCESSFULLY"); 638 } 639 } 640 else { 641 // sb.append("insert into "+RepConstants.rep_TableName+" ").append(" ( ") 642 // .append(RepConstants.repTable_pubsubName1) 643 // .append(" , "+RepConstants.repTable_tableId2+" , ") 644 // .append(RepConstants.repTable_tableName2).append(" , ") 645 // .append(RepConstants.repTable_conflict_resolver4).append(" ) ") 646 // .append(" values ( '").append(pubsubName).append("',") 647 // .append(RepConstants.seq_Name(RepConstants.rep_TableName)).append(".nextVal, '") 648 // .append(repTable.getSchemaQualifiedName()).append("','") 649 // .append(repTable.getConflictResolver()).append("') "); 650 651 sb.append("insert into " + RepConstants.rep_TableName + " (") 652 .append(RepConstants.repTable_pubsubName1) 653 .append(" , " + RepConstants.repTable_tableId2 + " , ") 654 .append(RepConstants.repTable_tableName2).append(" , ") 655 .append(RepConstants.repTable_conflict_resolver4).append(" ) ") 656 .append(" values ( ?,") 657 .append(RepConstants.seq_Name(RepConstants.rep_TableName)).append( 658 ".nextVal,?,?)"); 659 //System.out.println(" sb.toString() ="+sb.toString().toUpperCase()); 660 repPreparedStatement = connection.prepareStatement(sb.toString()); 661 repPreparedStatement.setString(1, pubsubName); 662 repPreparedStatement.setString(2, 663 repTable.getSchemaQualifiedName().toString()); 664 repPreparedStatement.setString(3, repTable.getConflictResolver()); 665 repPreparedStatement.execute(); 666 // System.out.println("QUERY EXECUTED SUCCESSFULLY"); 667 668 } 669 /** @todo statement has been closed */ 670 repPreparedStatement.close(); 671 return sb.toString(); 672 } 673 674 public void dropSequences(Connection con, String sequenceName) throws 675 SQLException { 676 Statement stt = con.createStatement(); 677 try { 678 String dropsequencequery = " drop sequence " + sequenceName; 679 // Seq_Shadow_TEST1 680 stt.execute(dropsequencequery); 681 } 682 catch (SQLException ex) { 683 } 684 finally { 685 if (stt != null) 686 stt.close(); 687 } 688 } 689 690 protected void createIndex(String pubsubName, String tableName) throws 691 RepException { 692 StringBuffer createIndexQuery = new StringBuffer(); 693 // create index ind on cmsadm2.R_S_Bank(Rep_sync_id); 694 createIndexQuery.append("create index ") 695 .append(RepConstants.Index_Name(tableName)) 696 .append(" on ") 697 .append(tableName) 698 .append("(") 699 .append(RepConstants.shadow_sync_id1) 700 .append(")"); 701 //System.out.println(" createIndexQuery : "+createIndexQuery.toString()); 702 try { 703 runDDL(pubsubName, createIndexQuery.toString()); 704 } 705 catch (RepException ex) { 706 // Ignore the Exception 707 } 708 catch (SQLException ex) { 709 // Ignore the Exception 710 } 711 } 712 713 public void makeProvisionForLOBDataTypes(ArrayList dataTypeList) { 714 ArrayList removeKeysList = null; 715 for (int i = 0, size = dataTypeList.size(); i < size; i++) { 716 ColumnsInfo ci = (ColumnsInfo) dataTypeList.get(i); 717 String dataType = ci.getDataTypeDeclaration(); 718 if (dataType.indexOf("long") != -1) { 719 if (removeKeysList == null) { 720 removeKeysList = new ArrayList(); 721 } 722 removeKeysList.add(ci); 723 } 724 } 725 if (removeKeysList != null) { 726 for (int i = 0, length = removeKeysList.size(); i < length; i++) { 727 dataTypeList.remove(removeKeysList.get(i)); 728 } 729 } 730 } 731 732 // references to columns of type LONG are not allowed in triggers 733 734 public boolean isColumnSizeExceedMaximumSize(TypeInfo typeInfo) throws 735 SQLException, RepException { 736 boolean flag = false; 737 int sqlType = typeInfo.getSqlType(); 738 int columnsize = typeInfo.getcolumnSize(); 739 switch (sqlType) { 740 case 12: //varchar 741 if (columnsize > 4000) { 742 flag = true; 743 break; 744 } 745 } 746 return flag; 747 } 748 749 public boolean isPrimaryKeyException(SQLException ex) throws SQLException { 750 if (ex.getErrorCode() == 1) { 751 return true; 752 } 753 return false; 754 } 755 756 public void setColumnPrecisionInTypeInfo(TypeInfo typeInfo, 757 ResultSetMetaData rsmt, 758 int columnIndex) throws SQLException { 759 if (! (typeInfo.getTypeName().equalsIgnoreCase("CLOB") && 760 typeInfo.getSqlType() == 2005 || 761 typeInfo.getTypeName().equalsIgnoreCase("blob") && 762 typeInfo.getSqlType() == 2004)) { 763 int columnPrecion = rsmt.getPrecision(columnIndex); 764 typeInfo.setColumnSize(columnPrecion); 765 } 766 767 } 768 769 public int getAppropriatePrecision(int columnSize, String datatypeName) { 770 if (datatypeName.equalsIgnoreCase("DOUBLE") || 771 datatypeName.equalsIgnoreCase("DECIMAL") || 772 datatypeName.equalsIgnoreCase("numeric") && columnSize > 38) { 773 columnSize = 38; 774 } 775 else if (datatypeName.equalsIgnoreCase("varchar") && columnSize > 4000 || 776 datatypeName.equalsIgnoreCase("varchar2") && columnSize > 4000) { 777 columnSize = 4000; 778 } 779 else if (datatypeName.equalsIgnoreCase("LONG VARCHAR") && columnSize > 4000) { 780 columnSize = 4000; 781 } 782 783 return columnSize; 784 } 785 786 public String getTableColumns(int VendorType, String ColumnName, 787 TypeInfo typeInfo, int columnPrecision, 788 ResultSet rs) throws RepException, SQLException { 789 StringBuffer sb = new StringBuffer(); 790 String nullable = rs.getString("IS_NULLABLE").trim(); 791 int SQLType = typeInfo.getSqlType(); 792 switch (VendorType) { 793 case Utility.DataBase_DaffodilDB: 794 switch (SQLType) { 795 case Types.BIT: 796 sb.append(ColumnName).append(" ").append(typeInfo. 797 getTypeDeclaration(columnPrecision)). 798 append(" ").append("check( ").append(ColumnName).append("=0"). 799 append(" or ").append(ColumnName).append("=1)").toString(); 800 break; 801 case Types.TINYINT: 802 sb.append(ColumnName).append(" ").append(typeInfo. 803 getTypeDeclaration(columnPrecision)).append(" ").append( 804 "check( ").append(ColumnName).append( 805 " between -127 and 127)").toString(); 806 break; 807 default: 808 sb.append(ColumnName).append(" ").append(typeInfo. 809 getTypeDeclaration(columnPrecision)); 810 } 811 break; 812 case Utility.DataBase_SqlServer: 813 switch (SQLType) { 814 case Types.TINYINT: 815 sb.append(ColumnName).append(" ").append(typeInfo. 816 getTypeDeclaration(columnPrecision)).append(" ").append( 817 "check( ").append(ColumnName).append( 818 " between 0 and 255)").toString(); 819 break; 820 default: 821 sb.append(ColumnName).append(" ").append(typeInfo. 822 getTypeDeclaration(columnPrecision)); 823 } 824 break; 825 default: 826 sb.append(ColumnName).append(" ").append(typeInfo. 827 getTypeDeclaration( 828 columnPrecision)); 829 } 830 return sb.toString(); 831 } 832 833 //scale -84 to 127 only 834 public int getAppropriateScale(int columnScale) throws RepException { 835 if (columnScale < -84) { 836 throw new RepException("REP026", new Object[] {"-84", "127"}); 837 } 838 else if (columnScale >= 127) { 839 columnScale = 127; 840 } 841 else if (columnScale >= -84 && columnScale < 127) 842 columnScale = columnScale; 843 log.debug("returning columnScale:: " + columnScale); 844 return columnScale; 845 } 846 847 public void dropPublisherSystemTables(Connection con) { 848 try { 849 fireDropQuery(con, " drop table " + getPublicationTableName()); 850 fireDropQuery(con, " drop table " + getBookMarkTableName()); 851 fireDropQuery(con, " drop table " + getRepTableName()); 852 fireDropQuery(con, " drop table " + getLogTableName()); 853 fireDropQuery(con, " drop table " + getIgnoredColumns_Table()); 854 fireDropQuery(con, 855 " drop table " + getTrackReplicationTablesUpdation_Table()); 856 //drop sequences on reptable and logtable 857 fireDropQuery(con, 858 " drop sequence " + RepConstants.seq_Name(rep_TableName)); 859 fireDropQuery(con, " drop sequence " + RepConstants.seq_Name(log_Table)); 860 } 861 catch (Exception ex) { 862 } 863 } 864 865 public void dropSubscriberSystemTables(Connection con) { 866 try { 867 fireDropQuery(con, " drop table " + getSubscriptionTableName()); 868 fireDropQuery(con, " drop table " + getBookMarkTableName()); 869 fireDropQuery(con, " drop table " + getRepTableName()); 870 fireDropQuery(con, " drop table " + getLogTableName()); 871 fireDropQuery(con, " drop table " + getScheduleTableName()); 872 fireDropQuery(con, " drop table " + getIgnoredColumns_Table()); 873 fireDropQuery(con, 874 " drop table " + getTrackReplicationTablesUpdation_Table()); 875 //drop sequences on reptable and logtable 876 fireDropQuery(con, 877 " drop sequence " + RepConstants.seq_Name(rep_TableName)); 878 fireDropQuery(con, " drop sequence " + RepConstants.seq_Name(log_Table)); 879 880 } 881 catch (Exception ex) { 882 } 883 } 884 885 public void deleteRecordsFromSuperLogTable(Statement subStatment) throws 886 SQLException { 887 // insert one record in superLogTable 888 889 StringBuffer query = new StringBuffer(); 890 query.append("insert into ").append(log_Table).append( 891 " values (").append(RepConstants.seq_Name(RepConstants.rep_TableName)) 892 .append(".nextVal ,'$$$$$$')"); 893 894 subStatment.execute(query.toString()); 895 896 query = new StringBuffer(); 897 // deleting all but one last record from super log table where commonid is maximum 898 query.append("Select max (").append(RepConstants.logTable_commonId1). 899 append(") from ").append(log_Table); 900 // System.out.println(query.toString()); 901 ResultSet rs = subStatment.executeQuery(query.toString()); 902 rs.next(); 903 long maxCID = rs.getLong(1); 904 905 query = new StringBuffer(); 906 907 query.append("delete from ").append(log_Table).append( 908 " where ") 909 .append(RepConstants.logTable_commonId1).append(" !=").append(maxCID); 910 // System.out.println(query.toString()); 911 subStatment.executeUpdate(query.toString()); 912 log.debug(query.toString()); 913 } 914 915 public PreparedStatement makePrimaryPreperedStatement(Connection pub_sub_Connection, String[] 916 primaryColumns, String shadowTable, String local_pub_sub_name) throws 917 SQLException, RepException { 918 StringBuffer query = new StringBuffer(); 919 query.append(" select * from "); 920 query.append(shadowTable); 921 query.append(" where "); 922 query.append(RepConstants.shadow_sync_id1); 923 query.append(" > "); 924 query.append("? "); 925 for (int i = 0; i < primaryColumns.length; i++) { 926 query.append(" and "); 927 query.append(primaryColumns[i]); 928 query.append("= ? "); 929 } 930 query.append(" and rownum=1 "); 931 query.append(" order by " + RepConstants.shadow_sync_id1); 932 return pub_sub_Connection.prepareStatement(query.toString()); 933 } 934 935 public boolean isForeignKeyException(SQLException ex) throws SQLException { 936 if (ex.getErrorCode() == 2292) 937 return true; 938 else 939 return false; 940 } 941 942 protected void createIgnoredColumnsTable(String pubName) throws SQLException, 943 RepException { 944 StringBuffer ignoredColumnsQuery = new StringBuffer(); 945 ignoredColumnsQuery.append(" Create Table ").append(getIgnoredColumns_Table()). 946 append(" ( ") 947 .append(RepConstants.ignoredColumnsTable_tableId1).append(" int , ") 948 .append(RepConstants.ignoredColumnsTable_ignoredcolumnName2).append( 949 " varchar(255) , ") 950 .append(" Primary Key (").append(RepConstants. 951 ignoredColumnsTable_tableId1).append( 952 " , ") 953 .append(RepConstants.ignoredColumnsTable_ignoredcolumnName2).append( 954 " ) ) "); 955 runDDL(pubName, ignoredColumnsQuery.toString()); 956 } 957 958 protected void createTrackReplicationTablesUpdationTable(String pubSubName) throws 959 RepException, SQLException { 960 StringBuffer trackRepTablesUpdationQuery = new StringBuffer(); 961 trackRepTablesUpdationQuery.append(" CREATE TABLE ").append(getTrackReplicationTablesUpdation_Table()).append(" ( " + 962 RepConstants.trackUpdation + " smallint PRIMARY KEY) "); 963 runDDL(pubSubName, trackRepTablesUpdationQuery.toString()); 964 runDDL(pubSubName,"Insert into "+getTrackReplicationTablesUpdation_Table()+" values(1)" ); 965 } 966 //implement this method for providing provision to stop updations done on shadow table 967 protected void createTriggerForTrackReplicationTablesUpdationTable(String 968 pubSubName) throws RepException, SQLException { 969 /* StringBuffer trackRepTablesUpdationTriggerQuery = new StringBuffer(); 970 trackRepTablesUpdationTriggerQuery.append(" CREATE TRIGGER TRI_") 971 .append(getTrackReplicationTablesUpdation_Table()).append( 972 " ON " + getTrackReplicationTablesUpdation_Table()) 973 .append(" AFTER INSERT AS DELETE FROM " + 974 getTrackReplicationTablesUpdation_Table() + " WHERE ") 975 .append(RepConstants.trackUpdation + " NOT IN(SELECT * FROM inserted)"); 976 runDDL(pubSubName, trackRepTablesUpdationTriggerQuery.toString());*/ 977 } 978 void runDDL(String pubsubName, String query) throws SQLException, RepException { 979 Connection connection = connectionPool.getConnection(pubsubName); 980 Statement stt = connection.createStatement(); 981 try { 982 log.debug(query); 983 //System.out.println(" query ="+query); 984 stt.execute(query); 985 log.info("Query executed "+query); 986 //System.out.println(" QUERY EXECUTED SUCCESSFULLY "); 987 988 }catch(SQLException ex){ 989 throw ex; 990 //Ignore the exception 991 } 992 finally { 993 connectionPool.removeSubPubFromMap(pubsubName); 994 if(stt!=null) 995 stt.close(); 996 connectionPool.returnConnection(connection); 997 } 998 } 999 1000 public PreparedStatement makePrimaryPreperedStatementBackwardTraversing(String[] primaryColumns, long lastId, String local_pub_sub_name, String shadowTable) throws SQLException, RepException { 1001 StringBuffer query = new StringBuffer(); 1002 query.append(" select * from ") 1003 .append(shadowTable) 1004 .append(" where ") 1005 .append(RepConstants.shadow_sync_id1) 1006 .append(" < ? ") 1007 .append(" and ") 1008 .append(RepConstants.shadow_sync_id1) 1009 .append(" > ") 1010 .append(lastId); 1011 for (int i = 0; i < primaryColumns.length; i++) { 1012 query.append(" and ") 1013 .append(primaryColumns[i]) 1014 .append(" = ? "); 1015 } 1016 query.append(" and rownum=1 ") 1017 .append(" order by ") 1018 .append(RepConstants.shadow_sync_id1) 1019 .append(" desc "); 1020 log.debug(query.toString()); 1021 //System.out.println("OralceHandler makePrimaryPreperedStatementDelete :: " +query.toString()); 1022 Connection pub_sub_Connection = connectionPool.getConnection(local_pub_sub_name); 1023 return pub_sub_Connection.prepareStatement(query.toString()); 1024 } 1025 1026 /** 1027 * isSchemaSupported 1028 * 1029 * @return boolean 1030 */ 1031 public boolean isSchemaSupported() { 1032 return true; 1033 } 1034 1035 }
Sponsors
