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 028 /** 029 * Method overrides specific to Sybase. 030 */ 031 public class SybaseHandler 032 extends AbstractDataBaseHandler { 033 034 public SybaseHandler() {} 035 036 public SybaseHandler(ConnectionPool connectionPool0) { 037 connectionPool = connectionPool0; 038 vendorType = Utility.DataBase_Sybase; 039 log.debug("VENDER TYPE " + vendorType); 040 } 041 042 protected void createSuperLogTable(String pubName) throws SQLException, 043 RepException { 044 StringBuffer logTableQuery = new StringBuffer(); 045 logTableQuery.append(" Create Table ") 046 .append(log_Table) 047 .append(" ( " + RepConstants.logTable_commonId1 + 048 " bigint default autoincrement , " + 049 RepConstants.logTable_tableName2 + " varchar(255) ) "); 050 log.debug(logTableQuery.toString()); 051 runDDL(pubName, logTableQuery.toString()); 052 StringBuffer indexQuery =new StringBuffer(); 053 indexQuery.append("CREATE INDEX ") 054 .append(RepConstants.log_Index) 055 .append(" ON "+getLogTableName()) 056 .append("(") 057 .append(RepConstants.logTable_commonId1) 058 .append(")"); 059 runDDL(pubName, indexQuery.toString()); 060 } 061 062 protected void createRepTable(String pubName) throws SQLException, 063 RepException { 064 StringBuffer repTableQuery = new StringBuffer(); 065 repTableQuery.append(" Create Table ").append(getRepTableName()) 066 .append("( " + RepConstants.repTable_pubsubName1 + " varchar(255) , " + 067 RepConstants.repTable_tableId2 + 068 " bigint default autoincrement, ") 069 .append(" " + RepConstants.repTable_tableName2 + " varchar(255) , " + 070 RepConstants.repTable_filter_clause3 + " varchar(255) null, ") 071 .append(RepConstants.repTable_createshadowtable6).append(" char(1) Default 'Y', ") 072 .append(RepConstants.repTable_cyclicdependency7).append(" char(1) Default 'N', ") 073 .append(" " + RepConstants.repTable_conflict_resolver4 + 074 " varchar(255) , Primary Key (" + 075 RepConstants.repTable_pubsubName1 + " , " + 076 RepConstants.repTable_tableName2 + " ) ) "); 077 078 log.debug(repTableQuery.toString()); 079 runDDL(pubName, repTableQuery.toString()); 080 } 081 082 protected void createBookMarkTable(String pubName) throws SQLException, 083 RepException { 084 StringBuffer bookmarkTableQuery = new StringBuffer(); 085 bookmarkTableQuery.append(" Create Table ") 086 .append(getBookMarkTableName()) 087 .append(" ( " + RepConstants.bookmark_LocalName1 + " varchar(255) , " + 088 RepConstants.bookmark_RemoteName2 + " varchar(255) , ") 089 .append(" " + RepConstants.bookmark_TableName3 + " varchar(255) , " + 090 RepConstants.bookmark_lastSyncId4 + " bigint , ") 091 .append(" " + RepConstants.bookmark_ConisderedId5 + 092 " bigint ," + RepConstants.bookmark_IsDeletedTable + 093 " char(1) default 'N' , Primary Key (" + 094 RepConstants.bookmark_LocalName1 + 095 ", " + RepConstants.bookmark_RemoteName2 + ", " + 096 RepConstants.bookmark_TableName3 + ") ) "); 097 log.debug(bookmarkTableQuery.toString()); 098 runDDL(pubName, bookmarkTableQuery.toString()); 099 } 100 101 public void createShadowTable(String pubsubName, String tableName, 102 String allColseq,String[] primaryColumns) throws RepException { 103 StringBuffer shadowTableQuery = new StringBuffer(); 104 shadowTableQuery.append(" Create Table ") 105 .append(RepConstants.shadow_Table(tableName)) 106 .append(" ( " + RepConstants.shadow_sync_id1 + 107 " bigint default autoincrement , ") 108 .append(" " + RepConstants.shadow_common_id2 + " bigint null, ") 109 .append(" " + RepConstants.shadow_operation3 + " char(1) , ") 110 .append(" " + RepConstants.shadow_status4 + " char(1) null") 111 .append(allColseq) 112 .append(" , " + RepConstants.shadow_serverName_n + " varchar(255) ") 113 .append(" , " + RepConstants.shadow_PK_Changed + " char(1) ) "); 114 try { 115 log.debug(shadowTableQuery.toString()); 116 runDDL(pubsubName, shadowTableQuery.toString()); 117 118 } 119 catch (RepException ex) { 120 throw ex; 121 } 122 catch (SQLException ex) { 123 // ex.printStackTrace(); 124 // Ignore the Exception 125 } 126 createIndex(pubsubName,RepConstants.shadow_Table(tableName)); 127 } 128 129 protected void createScheduleTable(String subName) throws SQLException, 130 RepException { 131 StringBuffer ScheduleTableQuery = new StringBuffer(); 132 ScheduleTableQuery.append(" Create Table ") 133 .append(Schedule_TableName) 134 .append(" ( " + RepConstants.schedule_Name + " varchar(255) , " + 135 RepConstants.subscription_subName1 + " varchar(255) unique , ") 136 .append(" " + RepConstants.schedule_type + " varchar(255) , ") 137 .append(" " + RepConstants.publication_serverName3 + " varchar (255) ," + 138 RepConstants.publication_portNo + " varchar(255) ,") 139 .append(" " + RepConstants.recurrence_type + " varchar(255) , " + 140 RepConstants.replication_type + " varchar(255) ,") 141 .append(" " + RepConstants.schedule_time + " bigint , ") 142 .append(" " + RepConstants.schedule_counter + 143 " numeric , Primary Key (" + RepConstants.schedule_Name + " , " + 144 RepConstants.subscription_subName1 + ") ) "); 145 log.debug(ScheduleTableQuery.toString()); 146 runDDL(subName, ScheduleTableQuery.toString()); 147 } 148 149 /* ALTER TRIGGER "TRD_contact" .TRD_contact 150 after delete on DBA.contact Referencing 151 old as oldRow For each Row 152 begin Insert into Rep_LogTable ( Rep_table_name ) values ( 'DBA.contact'); 153 Insert Into DBA.REP_SHADOW_contact ( Rep_common_id, Rep_operationType, Rep_status, id , last_name , first_name , title , street , city , state , zip , phone , fax , old_id , Rep_server_name ) Values ( null , 'D' , null , oldRow.id , oldRow.last_name , oldRow.first_name , oldRow.title , oldRow.street , oldRow.city , oldRow.state , oldRow.zip , oldRow.phone , oldRow.fax , oldRow.id , 'sube_3001') ; end 154 */ 155 /* ALTER TRIGGER "TRI_contact" .TRI_contact after insert on 156 DBA.contact Referencing new as newRow For each Row begin 157 Insert into Rep_LogTable ( Rep_table_name ) values ( 'DBA.contact'); 158 Insert Into DBA.REP_SHADOW_contact ( Rep_common_id, Rep_operationType, Rep_status, id , last_name , first_name , title , street , city , state , zip , phone , fax , old_id , Rep_server_name ) Values ( null , 'I' , null , newRow.id , newRow.last_name , newRow.first_name , newRow.title , newRow.street , newRow.city , newRow.state , newRow.zip , newRow.phone , newRow.fax , newRow.id , 'sube_3001') ; end 159 */ 160 /* ALTER TRIGGER "TRU_contact" .TRU_contact after update on 161 DBA.contact Referencing new as newRow old as oldRow 162 For each Row begin declare maxlogid numeric; 163 Insert into Rep_LogTable ( Rep_table_name ) values ( 'DBA.contact'); 164 Select max(Rep_cid) into maxlogid from Rep_LogTable; Insert Into DBA.REP_SHADOW_contact ( Rep_common_id, Rep_operationType, Rep_status, id , last_name , first_name , title , street , city , state , zip , phone , fax , old_id , Rep_server_name ) Values ( maxlogid , 'U' , 'B' , oldRow.id , oldRow.last_name , oldRow.first_name , oldRow.title , oldRow.street , oldRow.city , oldRow.state , oldRow.zip , oldRow.phone , oldRow.fax , oldRow.id , 'sube_3001') ; Insert Into DBA.REP_SHADOW_contact ( Rep_common_id, Rep_operationType, Rep_status, id , last_name , first_name , title , street , city , state , zip , phone , fax , old_id , Rep_server_name ) Values ( maxlogid , 'U' , 'A' , newRow.id , newRow.last_name , newRow.first_name , newRow.title , newRow.street , newRow.city , newRow.state , newRow.zip , newRow.phone , newRow.fax , oldRow.id , 'sube_3001') ; end 165 */ 166 167 public void createShadowTableTriggers(String pubsubName, String tableName, 168 ArrayList colInfoList, 169 String[] primCols) throws RepException { 170 171 String serverName = getLocalServerName(); 172 int size = colInfoList.size(); 173 String[] colNames = new String[size]; 174 for (int i = 0; i < size; i++) { 175 colNames[i] = ( (ColumnsInfo) colInfoList.get(i)).getColumnName(); 176 } 177 //RepPrinter.print(" Columns are :::::: " + java.util.Arrays.asList(colNames)); 178 String colNameSeq = getColumnNameSequence(colNames, "").toString(); 179 String colNameSeqPrefixOldRow = getColumnNameSequence(colNames, "oldRow."). 180 toString(); 181 String colNameSeqPrefixNewRow = getColumnNameSequence(colNames, "newRow."). 182 toString(); 183 String shadowTableName = RepConstants.shadow_Table(tableName); 184 String primColumnNamesSeq = getColumnNameSequence(primCols, "old_"); 185 String primColNameSeqPrefixOldRow = getColumnNameSequence(primCols, 186 "oldRow.").toString(); 187 String primColNameSeqPrefixNewRow = getColumnNameSequence(primCols, 188 "newRow.").toString(); 189 String[] primOld = getColumnNameWithOldOrNewPrefix(primCols,"OLD."); 190 String[] primNew = getColumnNameWithOldOrNewPrefix(primCols,"NEW."); 191 StringBuffer insertLogTable = new StringBuffer(); 192 insertLogTable.append(" Insert into ") 193 .append(log_Table) 194 .append(" ( ").append(RepConstants.logTable_tableName2) 195 .append(" ) values ( '") 196 .append(tableName).append("'); "); 197 198 StringBuffer insTriggerQuery = new StringBuffer(); 199 insTriggerQuery.append(" Create trigger ") 200 .append(RepConstants.getInsertTriggerName(tableName)) 201 .append(" after insert on ").append(tableName) 202 .append(" Referencing new as newRow For each Row begin ") 203 .append(insertLogTable).append(" Insert Into ") 204 .append(shadowTableName).append(" ( ") 205 .append(RepConstants.shadow_common_id2).append(", ") 206 .append(RepConstants.shadow_operation3).append(", ") 207 .append(RepConstants.shadow_status4).append(", ") 208 .append(colNameSeq).append(primColumnNamesSeq) 209 .append(RepConstants.shadow_serverName_n) 210 .append(" ) Values ( null , 'I' , null , ") 211 .append(colNameSeqPrefixNewRow).append(primColNameSeqPrefixNewRow) 212 .append("'").append(serverName).append("') ; end "); 213 214 StringBuffer delTriggerQuery = new StringBuffer(); 215 delTriggerQuery.append(" Create trigger ") 216 .append(RepConstants.getDeleteTriggerName(tableName)) 217 .append(" after delete on ").append(tableName) 218 .append(" Referencing old as oldRow For each Row begin ") 219 .append(insertLogTable).append(" Insert Into ") 220 .append(shadowTableName).append(" ( ") 221 .append(RepConstants.shadow_common_id2).append(", ") 222 .append(RepConstants.shadow_operation3).append(", ") 223 .append(RepConstants.shadow_status4).append(", ") 224 .append(colNameSeq).append(primColumnNamesSeq) 225 .append(RepConstants.shadow_serverName_n) 226 .append(" ) Values ( null , 'D' , null , ") 227 .append(colNameSeqPrefixOldRow).append(primColNameSeqPrefixOldRow) 228 .append("'").append(serverName).append("') ; end "); 229 230 StringBuffer updTriggerQuery = new StringBuffer(); 231 updTriggerQuery.append(" Create trigger ") 232 .append(RepConstants.getUpdateTriggerName(tableName)) 233 .append(" after update on ").append(tableName) 234 .append(" Referencing new as newRow old as oldRow For each Row ") 235 .append(" begin declare maxlogid numeric; pkchanged char(1); ").append(insertLogTable) 236 .append(" Select max(" + RepConstants.logTable_commonId1 + 237 ") into maxlogid from ") 238 .append(log_Table).append("; Insert Into ") 239 .append(shadowTableName).append(" ( ") 240 .append(RepConstants.shadow_common_id2).append(", ") 241 .append(RepConstants.shadow_operation3).append(", ") 242 .append(RepConstants.shadow_status4).append(", ") 243 .append(colNameSeq).append(primColumnNamesSeq) 244 .append(RepConstants.shadow_serverName_n) 245 .append(" ) Values ( maxlogid , 'U' , 'B' , ") 246 .append(colNameSeqPrefixOldRow).append(primColNameSeqPrefixOldRow) 247 .append("'").append(serverName).append("') ;") 248 .append(" if( "); 249 for (int i = 0; i < primOld.length; i++) { 250 if (i != 0) 251 updTriggerQuery.append(" and "); 252 updTriggerQuery.append(primOld[i] ) 253 .append("!=" ) 254 .append(primNew[i]); 255 } 256 updTriggerQuery.append(" ) Then ") 257 .append(" pkchanged :='Y'; end if; ") 258 .append(" Insert Into ") 259 .append(shadowTableName).append(" ( ") 260 .append(RepConstants.shadow_common_id2).append(", ") 261 .append(RepConstants.shadow_operation3).append(", ") 262 .append(RepConstants.shadow_status4).append(", ") 263 .append(colNameSeq).append(primColumnNamesSeq) 264 .append(RepConstants.shadow_serverName_n).append(" , ") 265 .append(RepConstants.shadow_PK_Changed) 266 .append(" ) Values ( maxlogid , 'U' , 'A' , ") 267 .append(colNameSeqPrefixNewRow).append(primColNameSeqPrefixOldRow) 268 .append("'").append(serverName).append("',pkchanged) ; end "); 269 try { 270 log.debug(insTriggerQuery.toString()); 271 runDDL(pubsubName, insTriggerQuery.toString()); 272 } 273 catch (RepException ex) { 274 throw ex; 275 } 276 catch (SQLException ex) { 277 log.error(ex); 278 // Ignore Exception 279 } try { 280 log.debug(delTriggerQuery.toString()); 281 runDDL(pubsubName, delTriggerQuery.toString()); 282 } 283 catch (RepException ex) { 284 throw ex; 285 } 286 catch (SQLException ex) { 287 log.error(ex); 288 // Ignore Exception 289 } 290 try { 291 log.debug(updTriggerQuery.toString()); 292 runDDL(pubsubName, updTriggerQuery.toString()); 293 294 } 295 catch (RepException ex) { 296 throw ex; 297 } 298 catch (SQLException ex) { 299 log.error(ex); 300 // Ignore Exception 301 } 302 303 } 304 305 public boolean isDataTypeOptionalSizeSupported(TypeInfo typeInfo) { 306 log.debug(typeInfo); 307 int sqlType = typeInfo.getSqlType(); 308 String typeName = typeInfo.getTypeName(); 309 switch (sqlType) { 310 case 4: 311 case 5: 312 case 7: 313 case -4: 314 case -5: 315 case -7: 316 case 91: 317 case 92: 318 case 93: 319 case -6: 320 case 16: 321 case -3: 322 return false; 323 324 default: 325 return true; 326 } 327 } 328 329 public void setTypeInfo(TypeInfo typeInfo, ResultSet rs) throws RepException, 330 SQLException { 331 log.debug(typeInfo); 332 switch (typeInfo.getSqlType()) { 333 334 case Types.BOOLEAN: //16 335 case Types.BIT: 336 typeInfo.setTypeName("bit"); 337 break; //-7; 338 case Types.TINYINT: 339 typeInfo.setTypeName("tinyint"); 340 break; //-6; 341 case Types.SMALLINT: 342 typeInfo.setTypeName("smallint"); 343 break; // 5; 344 case Types.INTEGER: 345 typeInfo.setTypeName("integer"); 346 break; // 4; 347 case Types.BIGINT: 348 typeInfo.setTypeName("bigint"); 349 break; //-5; 350 case Types.REAL: 351 typeInfo.setTypeName("real"); 352 break; // 7; 353 case Types.FLOAT: // 6 354 case Types.DOUBLE: 355 typeInfo.setTypeName("float"); 356 break; // 8; 357 case Types.NUMERIC: 358 typeInfo.setTypeName("numeric"); 359 break; // 2; 360 case Types.DECIMAL: // 3; 361 if (typeInfo.getTypeName().equalsIgnoreCase("money")) { 362 typeInfo.setTypeName("money"); 363 return; 364 } 365 else if (typeInfo.getTypeName().equalsIgnoreCase("smallmoney")) { 366 typeInfo.setTypeName("smallmoney"); 367 return; 368 } 369 typeInfo.setTypeName("decimal"); 370 break; // 3; 371 case Types.CHAR: 372 typeInfo.setTypeName("char"); 373 break; // 1; 374 case Types.VARCHAR: 375 typeInfo.setTypeName("varchar"); 376 break; //12; 377 case Types.DATE: 378 typeInfo.setTypeName("date"); 379 break; //91; 380 case Types.TIME: 381 typeInfo.setTypeName("time"); 382 break; // 92; 383 case Types.TIMESTAMP: 384 typeInfo.setTypeName("timestamp"); 385 break; // 93; 386 case Types.BINARY: 387 typeInfo.setTypeName("binary"); 388 break; // -2; 389 case Types.VARBINARY: 390 typeInfo.setTypeName("varbinary"); 391 break; // -3; 392 case Types.LONGVARBINARY: 393 if (typeInfo.getTypeName().equalsIgnoreCase("long binary")) { 394 typeInfo.setTypeName("long binary"); 395 return; 396 } 397 typeInfo.setTypeName("image"); 398 break; //-4; 399 case Types.BLOB: 400 typeInfo.setTypeName("image"); 401 break; //2004; 402 case Types.LONGVARCHAR: //-1 403 case Types.CLOB: 404 typeInfo.setTypeName("text"); 405 break; //2005; 406 case Types.OTHER: 407 typeInfo.setTypeName("BLOB"); 408 break; //1111; 409 case Types.REF: //2006; 410 case Types.NULL: 411 case Types.DISTINCT: //2001; 412 case Types.STRUCT: //2002; 413 case Types.ARRAY: //2003; 414 case Types.DATALINK: //70; 415 case Types.JAVA_OBJECT: //2000; 416 // case Types.BOOLEAN: //16 417 default: 418 throw new RepException("REP031", new Object[] {typeInfo.getTypeName()}); 419 } 420 } 421 422 public AbstractColumnObject getColumnObject(TypeInfo typeInfo) throws 423 RepException { 424 log.debug(typeInfo); 425 int sqlType = typeInfo.getSqlType(); 426 switch (sqlType) { 427 case 1: // char 428 case 12: // char varying 429 case -1: // long varchar 430 return new StringObject(sqlType,this); 431 case -3: // bitvarying 432 return new BlobObject(sqlType,this); 433 case 2005: // clob 434 return new ClobObject(sqlType,this); 435 case 2004: // blob 436 case -2: // binary 437 case -4: // long varbiary 438 return new BlobObject(sqlType,this); 439 case 4: // int 440 case 5: // small int 441 case -6: // tinyint 442 return new IntegerObject(sqlType,this); 443 case -5: // long 444 case 2000: // long varbinary 445 return new LongObject(sqlType,this); 446 case 3: // decimal 447 if ( (typeInfo.getTypeName()).equalsIgnoreCase("numeric")) { 448 return new BigDecimalObject(sqlType,this); 449 } 450 case 8: // double precision 451 case 2: // numeric 452 case 6: // float 453 return new DoubleObject(sqlType,this); 454 case 7: // real 455 return new FloatObject(sqlType,this); 456 case -7: // bit 457 case 16: // boolean 458 return new BooleanObject(sqlType,this); 459 case 91: // date 460 return new DateObject(sqlType,this); 461 case 92: // time 462 return new TimeObject(sqlType,this); 463 case 93: // time stamp 464 return new TimeStampObject(sqlType,this); 465 default: 466 throw new RepException("REP031", new Object[] {new Integer(sqlType)}); 467 } 468 } 469 470 public boolean isColumnSizeExceedMaximumSize(TypeInfo typeInfo) throws 471 SQLException, RepException { 472 log.debug(typeInfo); 473 boolean flag = false; 474 int sqlType = typeInfo.getSqlType(); 475 int columnsize = typeInfo.getcolumnSize(); 476 switch (sqlType) { 477 case 1: // char 478 case 12: //varchar 479 if (columnsize > 255) { 480 flag = true; 481 482 } 483 break; 484 case -6: // tinyint 485 if (columnsize > 3) { 486 flag = true; 487 488 } 489 break; 490 } 491 return flag; 492 } 493 494 public void setColumnPrecisionInTypeInfo(TypeInfo typeInfo, 495 ResultSetMetaData rsmt, 496 int columnIndex) throws SQLException { 497 int columnPrecion = rsmt.getPrecision(columnIndex); 498 typeInfo.setColumnSize(columnPrecion); 499 500 } 501 502 public boolean getPrimaryKeyErrorCode(SQLException ex) throws SQLException { 503 if (ex.getMessage().indexOf("PRIMARY KEY") != -1 || 504 ex.getErrorCode() == 2601) { 505 return true; 506 } 507 return false; 508 } 509 510 public int getAppropriatePrecision(int columnSize, String datatypeName) { 511 512 if (datatypeName.equalsIgnoreCase("numeric") && columnSize > 38) { 513 columnSize = 38; 514 } 515 else if ( (datatypeName.equalsIgnoreCase("decimal") || 516 datatypeName.equalsIgnoreCase("dec")) && columnSize > 38) { 517 columnSize = 38; 518 } 519 else if (datatypeName.equalsIgnoreCase("varchar") && columnSize > 255) { 520 columnSize = 255; 521 } 522 else if (datatypeName.equalsIgnoreCase("tinyint") && columnSize > 3) { 523 columnSize = 3; 524 } 525 526 return columnSize; 527 } 528 529 protected void createIndex(String pubsubName, String tableName) throws 530 RepException { 531 StringBuffer createIndexQuery = new StringBuffer(); 532 // create index ind on cmsadm2.R_S_Bank(Rep_sync_id); 533 createIndexQuery.append("create index ") 534 .append(RepConstants.Index_Name(tableName)) 535 .append(" on ") 536 .append(tableName) 537 .append("(") 538 .append(RepConstants.shadow_sync_id1) 539 .append(")"); 540 try { 541 runDDL(pubsubName, createIndexQuery.toString()); 542 } 543 catch (RepException ex) { 544 // Ignore the Exception 545 } 546 catch (SQLException ex) { 547 // Ignore the Exception 548 } 549 } 550 551 //scale 0 to 14 only 552 public int getAppropriateScale(int columnScale) throws RepException { 553 if (columnScale < 0) { 554 throw new RepException("REP026", new Object[] {"1", "14"}); 555 } 556 else if (columnScale >= 14) { 557 columnScale = 14; 558 } 559 else if (columnScale >= 0 && columnScale < 14) 560 columnScale = columnScale; 561 log.debug("returning columnScale::" + columnScale); 562 return columnScale; 563 564 } 565 566 public PreparedStatement makePrimaryPreperedStatement(Connection pub_sub_Connection, String[] 567 primaryColumns, String shadowTable, String local_pub_sub_name) throws 568 SQLException, RepException { 569 StringBuffer query = new StringBuffer(); 570 query.append(" select first * from ") 571 .append(shadowTable) 572 .append(" where ") 573 .append(RepConstants.shadow_sync_id1) 574 .append(" > "); 575 query.append("? "); 576 for (int i = 0; i < primaryColumns.length; i++) { 577 query.append(" and ") 578 .append(primaryColumns[i]) 579 .append("= ? "); 580 } 581 query.append(" order by " + RepConstants.shadow_sync_id1); 582 return pub_sub_Connection.prepareStatement(query.toString()); 583 } 584 585 586 587 /** 588 * isPrimaryKeyException 589 * 590 * @param ex SQLException 591 * @return boolean 592 */ 593 public boolean isPrimaryKeyException(SQLException ex) { 594 return false; 595 } 596 597 /** 598 * isForeignKeyException 599 * 600 * @param ex SQLException 601 * @return boolean 602 */ 603 public boolean isForeignKeyException(SQLException ex) { 604 return false; 605 } 606 protected void createIgnoredColumnsTable(String pubName) throws SQLException, 607 RepException 608 { 609 StringBuffer ignoredColumnsQuery = new StringBuffer(); 610 ignoredColumnsQuery.append(" Create Table ").append(getIgnoredColumns_Table()).append(" ( ") 611 .append(RepConstants.ignoredColumnsTable_tableId1).append(" bigint , ") 612 .append(RepConstants.ignoredColumnsTable_ignoredcolumnName2).append(" varchar(255) , ") 613 .append(" Primary Key (").append(RepConstants.ignoredColumnsTable_tableId1).append(" , ") 614 .append(RepConstants.ignoredColumnsTable_ignoredcolumnName2).append(" ) ) "); 615 runDDL(pubName, ignoredColumnsQuery.toString()); 616 } 617 618 protected void createTrackReplicationTablesUpdationTable(String pubSubName) throws RepException, SQLException { 619 StringBuffer trackRepTablesUpdationQuery = new StringBuffer(); 620 trackRepTablesUpdationQuery.append(" CREATE TABLE ").append(getTrackReplicationTablesUpdation_Table()).append(" ( " + 621 RepConstants.trackUpdation + " smallint PRIMARY KEY) "); 622 runDDL(pubSubName, trackRepTablesUpdationQuery.toString()); 623 runDDL(pubSubName,"Insert into "+getTrackReplicationTablesUpdation_Table()+" values(1)" ); 624 } 625 626 protected void createTriggerForTrackReplicationTablesUpdationTable(String pubSubName) throws RepException, SQLException { 627 // StringBuffer trackRepTablesUpdationTriggerQuery = new StringBuffer(); 628 // trackRepTablesUpdationTriggerQuery.append(" CREATE TRIGGER TRI_") 629 // .append(getTrackReplicationTablesUpdation_Table()).append( 630 // " ON " + getTrackReplicationTablesUpdation_Table()) 631 // .append(" AFTER INSERT AS DELETE FROM " + 632 // getTrackReplicationTablesUpdation_Table() + " WHERE ") 633 // .append(RepConstants.trackUpdation + " NOT IN(SELECT * FROM inserted)"); 634 // runDDL(pubSubName, trackRepTablesUpdationTriggerQuery.toString()); 635 } 636 637 public PreparedStatement makePrimaryPreperedStatementBackwardTraversing(String[] primaryColumns, long lastId, String local_pub_sub_name, 638 String shadowTable) throws SQLException, RepException { 639 StringBuffer query = new StringBuffer(); 640 query.append(" select * from ") 641 .append(shadowTable) 642 .append(" where ") 643 .append(RepConstants.shadow_sync_id1) 644 .append(" < ? ") 645 .append(" and ") 646 .append(RepConstants.shadow_sync_id1) 647 .append(" > ") 648 .append(lastId); 649 for (int i = 0; i < primaryColumns.length; i++) { 650 query.append(" and ") 651 .append(primaryColumns[i]) 652 .append(" = ? "); 653 } 654 query.append(" order by ") 655 .append(RepConstants.shadow_sync_id1) 656 .append(" desc "); 657 log.debug(query.toString()); 658 //System.out.println("SybaseHandler makePrimaryPreperedStatementDelete :: " +query.toString()); 659 Connection pub_sub_Connection = connectionPool.getConnection(local_pub_sub_name); 660 return pub_sub_Connection.prepareStatement(query.toString()); 661 } 662 663 /** 664 * isSchemaSupported 665 * 666 * @return boolean 667 */ 668 public boolean isSchemaSupported() { 669 return true; 670 } 671 672 }
Sponsors
