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 029 /** 030 * Method overrides specific to the Daffodil/One$DB database engine. 031 */ 032 public class DaffodilDBHandler 033 extends AbstractDataBaseHandler { 034 035 private String daff_publication_TableName; 036 private String daff_subscription_TableName; 037 private String daff_bookmark_TableName; 038 private String daff_rep_TableName; 039 private String daff_log_Table; 040 private String daff_schedule_Table; 041 private String daff_ignoredColumns_Table; 042 private String daff_trackReplicationTablesUpdation_Table; 043 private String daff_trackPrimaryKeyUpdation_Table = "Rep_trackPrimaryKey"; 044 protected static Logger log = Logger.getLogger(DaffodilDBHandler.class. 045 getName()); 046 047 public DaffodilDBHandler() {} 048 049 public DaffodilDBHandler(ConnectionPool connectionPool0) { 050 connectionPool = connectionPool0; 051 vendorType = Utility.DataBase_DaffodilDB; 052 daff_publication_TableName = "users." + publication_TableName; 053 daff_subscription_TableName = "users." + subscription_TableName; 054 daff_bookmark_TableName = "users." + bookmark_TableName; 055 daff_rep_TableName = "users." + rep_TableName; 056 daff_log_Table = "users." + log_Table; 057 daff_schedule_Table = "users." + Schedule_TableName; 058 daff_ignoredColumns_Table = "users." + ignoredColumns_Table; 059 daff_trackReplicationTablesUpdation_Table = "users." +trackReplicationTablesUpdation_Table; 060 daff_trackPrimaryKeyUpdation_Table ="users."+trackPrimaryKeyUpdation_Table; 061 062 } 063 064 protected void createSuperLogTable(String pubName) throws SQLException, 065 RepException { 066 StringBuffer logTableQuery = new StringBuffer(); 067 logTableQuery.append(" Create Table ") 068 .append(daff_log_Table) 069 .append(" ( " + RepConstants.logTable_commonId1 + 070 " long autoincrement , " + RepConstants.logTable_tableName2 + 071 " varchar(255) ) "); 072 runDDL(pubName, logTableQuery.toString()); 073 074 StringBuffer indexQuery = new StringBuffer(); 075 indexQuery.append("CREATE INDEX ") 076 .append(RepConstants.log_Index) 077 .append(" ON " + getLogTableName()) 078 .append("(") 079 .append(RepConstants.logTable_commonId1) 080 .append(")"); 081 //System.out.println(" Create Index on LogTable : "+indexQuery.toString()); 082 runDDL(pubName, indexQuery.toString()); 083 084 } 085 086 /** 087 * Because changes has been made in structure of RepTable 088 * by Hisar team.So old method has been commented. After 089 * proper testing with all data base it should be deleted. 090 */ 091 092 093 /* protected void createRepTable(String pubName) throws SQLException, 094 RepException 095 { 096 StringBuffer repTableQuery = new StringBuffer(); 097 repTableQuery.append(" Create Table ").append(getRepTableName()) 098 .append("( "+RepConstants.repTable_pubsubName1+" varchar(255) , "+RepConstants.repTable_tableId2+" int autoincrement, ") 099 .append(" "+RepConstants.repTable_tableName2+" varchar(255) , "+RepConstants.repTable_filter_clause3+" varchar(255), ") 100 .append(" "+RepConstants.repTable_conflict_resolver4+" varchar(255) , Primary Key ("+RepConstants.repTable_pubsubName1+" , "+RepConstants.repTable_tableName2+" ) ) "); 101 runDDL(pubName, repTableQuery.toString()); 102 }*/ 103 104 protected void createRepTable(String pubName) throws SQLException, 105 RepException { 106 StringBuffer repTableQuery = new StringBuffer(); 107 repTableQuery.append(" Create Table ").append(getRepTableName()).append( 108 " ( ") 109 .append(RepConstants.repTable_pubsubName1).append(" varchar(255) , ") 110 .append(RepConstants.repTable_tableId2).append(" int autoincrement, ") 111 .append(RepConstants.repTable_tableName2).append(" varchar(255) , ") 112 .append(RepConstants.repTable_filter_clause3).append( 113 " varchar(255) , ") 114 .append(RepConstants.repTable_createshadowtable6).append( 115 " char(1) Default 'Y', ") 116 .append(RepConstants.repTable_cyclicdependency7).append( 117 " char(1) Default 'N', ") 118 .append(RepConstants.repTable_conflict_resolver4).append( 119 " varchar(255), ") 120 .append(" Primary Key (").append(RepConstants.repTable_pubsubName1). 121 append(" , ") 122 .append(RepConstants.repTable_tableName2).append(" ) ) "); 123 runDDL(pubName, repTableQuery.toString()); 124 } 125 126 public void createShadowTable(String pubsubName, String tableName, 127 String allColseq,String[] primaryColumns) throws RepException { 128 StringBuffer shadowTableQuery = new StringBuffer(); 129 shadowTableQuery.append(" Create Table ") 130 .append(RepConstants.shadow_Table(tableName)) 131 .append(" ( " + RepConstants.shadow_sync_id1 + 132 " long autoincrement , ") 133 .append(" " + RepConstants.shadow_common_id2 + " long , ") 134 .append(" " + RepConstants.shadow_operation3 + " char(1) , ") 135 .append(" " + RepConstants.shadow_status4 + " char(1) ") 136 .append(allColseq) 137 .append(" , " + RepConstants.shadow_serverName_n + " varchar(255) ") 138 .append(" , " + RepConstants.shadow_PK_Changed + " char(1) ) "); 139 try { 140 runDDL(pubsubName, shadowTableQuery.toString()); 141 } 142 catch (RepException ex) { 143 throw ex; 144 } 145 catch (SQLException ex) { 146 // Ignore the Exception 147 } 148 createIndex(pubsubName, RepConstants.shadow_Table(tableName)); 149 } 150 151 protected void createScheduleTable(String subName) throws SQLException, 152 RepException { 153 StringBuffer ScheduleTableQuery = new StringBuffer(); 154 ScheduleTableQuery.append(" Create Table ") 155 .append(daff_schedule_Table) 156 .append(" ( " + RepConstants.schedule_Name + " varchar(255) , " + 157 RepConstants.subscription_subName1 + " varchar(255) unique , ") 158 .append(" " + RepConstants.schedule_type + " varchar(255) , ") 159 .append(" " + RepConstants.publication_serverName3 + " varchar (255) ," + 160 RepConstants.publication_portNo + " varchar(255) ,") 161 .append(" " + RepConstants.recurrence_type + " varchar(255) , " + 162 RepConstants.replication_type + " varchar(255) ,") 163 .append(" " + RepConstants.schedule_time + " Long , ") 164 .append(" " + RepConstants.schedule_counter + " Long , Primary Key (" + 165 RepConstants.schedule_Name + " , " + 166 RepConstants.subscription_subName1 + ") ) "); 167 runDDL(subName, ScheduleTableQuery.toString()); 168 // System.out.println(ScheduleTableQuery.toString()); 169 } 170 171 //DaffodilDB 172 //" create trigger abc2 after insert on a referencing new n for each row " 173 //" begin declare va int ; Select max(a) into va from b; " 174 //" insert into d values ( va , n.a) ; end " ; 175 public void createShadowTableTriggers(String pubsubName, String tableName, 176 ArrayList colInfoList, 177 String[] primCols) throws RepException { 178 179 String serverName = getLocalServerName(); 180 // RepPrinter.print(" Columns are :::::: " + java.util.Arrays.asList(columnTypeInfoMap.keySet().toArray(new String[0]))); 181 // String[] colNames = (String[]) columnTypeInfoMap.keySet().toArray(new String[0]); 182 int size = colInfoList.size(); 183 String[] colNames = new String[size]; 184 for (int i = 0; i < size; i++) { 185 colNames[i] = ( (ColumnsInfo) colInfoList.get(i)).getColumnName(); 186 } 187 //RepPrinter.print(" Columns are :::::: " + java.util.Arrays.asList(colNames)); 188 String colNameSeq = getColumnNameSequence(colNames, "").toString(); 189 String colNameSeqPrefixOldRow = getColumnNameSequence(colNames, "oldRow."). 190 toString(); 191 String colNameSeqPrefixNewRow = getColumnNameSequence(colNames, "newRow."). 192 toString(); 193 String shadowTableName = RepConstants.shadow_Table(tableName); 194 String primColumnNamesSeq = getColumnNameSequence(primCols, "rep_old_"); 195 String primColNameSeqPrefixOldRow = getColumnNameSequence(primCols, 196 "oldRow.").toString(); 197 String primColNameSeqPrefixNewRow = getColumnNameSequence(primCols, 198 "newRow.").toString(); 199 String[] primColsOld= getColumnNameWithOldOrNewPrefix(primCols,"oldRow."); 200 String[] primColsNew= getColumnNameWithOldOrNewPrefix(primCols,"newRow."); 201 StringBuffer insertLogTable = new StringBuffer(); 202 insertLogTable.append(" Insert into ") 203 .append(daff_log_Table) 204 .append(" ( ").append(RepConstants.logTable_tableName2) 205 .append(" ) values ( '") 206 .append(tableName).append("'); "); 207 208 StringBuffer insTriggerQuery = new StringBuffer(); 209 insTriggerQuery.append(" Create trigger ") 210 .append(RepConstants.getInsertTriggerName(tableName)) 211 .append(" after insert on ").append(tableName) 212 .append(" Referencing new as newRow For each Row begin ") 213 .append(insertLogTable).append(" Insert Into ") 214 .append(shadowTableName).append(" ( ") 215 .append(RepConstants.shadow_common_id2).append(", ") 216 .append(RepConstants.shadow_operation3).append(", ") 217 .append(RepConstants.shadow_status4).append(", ") 218 .append(colNameSeq).append(primColumnNamesSeq) 219 .append(RepConstants.shadow_serverName_n) 220 .append(" ) Values ( null , 'I' , null , ") 221 .append(colNameSeqPrefixNewRow).append(primColNameSeqPrefixNewRow) 222 .append("'").append(serverName).append("') ; end "); 223 224 StringBuffer delTriggerQuery = new StringBuffer(); 225 delTriggerQuery.append(" Create trigger ") 226 .append(RepConstants.getDeleteTriggerName(tableName)) 227 .append(" after delete on ").append(tableName) 228 .append(" Referencing old as oldRow For each Row begin ") 229 .append(insertLogTable).append(" Insert Into ") 230 .append(shadowTableName).append(" ( ") 231 .append(RepConstants.shadow_common_id2).append(", ") 232 .append(RepConstants.shadow_operation3).append(", ") 233 .append(RepConstants.shadow_status4).append(", ") 234 .append(colNameSeq).append(primColumnNamesSeq) 235 .append(RepConstants.shadow_serverName_n) 236 .append(" ) Values ( null , 'D' , null , ") 237 .append(colNameSeqPrefixOldRow).append(primColNameSeqPrefixOldRow) 238 .append("'").append(serverName).append("') ; end "); 239 240 StringBuffer updTriggerQuery = new StringBuffer(); 241 updTriggerQuery.append(" Create trigger ") 242 .append(RepConstants.getUpdateTriggerName(tableName)) 243 .append(" after update on ").append(tableName) 244 .append(" Referencing new as newRow old as oldRow For each Row ") 245 .append(" begin declare maxlogid long; declare pkchanged char(1); ").append(insertLogTable) 246 .append(" Select max(" + RepConstants.logTable_commonId1 + 247 ") into maxlogid from ") 248 .append(daff_log_Table).append("; ") 249 //------------- 250 .append(" if( "); 251 for (int i = 0; i < primColsOld.length; i++) { 252 if (i != 0) 253 updTriggerQuery.append(" and "); 254 updTriggerQuery.append(primColsOld[i] ) 255 .append("!=" ) 256 .append(primColsNew[i]); 257 } 258 updTriggerQuery.append(" ) Then") 259 .append(" Set pkchanged ='Y' ; end if;") 260 //-------- 261 .append( "Insert Into ") 262 .append(shadowTableName).append(" ( ") 263 .append(RepConstants.shadow_common_id2).append(", ") 264 .append(RepConstants.shadow_operation3).append(", ") 265 .append(RepConstants.shadow_status4).append(", ") 266 .append(colNameSeq).append(primColumnNamesSeq) 267 .append(RepConstants.shadow_serverName_n) 268 .append(" ) Values ( maxlogid , 'U' , 'B' , ") 269 .append(colNameSeqPrefixOldRow).append(primColNameSeqPrefixOldRow) 270 .append("'").append(serverName).append("') ; Insert Into ") 271 .append(shadowTableName).append(" ( ") 272 .append(RepConstants.shadow_common_id2).append(", ") 273 .append(RepConstants.shadow_operation3).append(", ") 274 .append(RepConstants.shadow_status4).append(", ") 275 .append(colNameSeq).append(primColumnNamesSeq) 276 .append(RepConstants.shadow_serverName_n).append(" , ") 277 .append(RepConstants.shadow_PK_Changed) 278 .append(" ) Values ( maxlogid , 'U' , 'A' , ") 279 .append(colNameSeqPrefixNewRow).append(primColNameSeqPrefixOldRow) 280 .append("'").append(serverName).append("',pkchanged) ; end "); 281 try { 282 runDDL(pubsubName, insTriggerQuery.toString()); 283 } 284 catch (RepException ex) { 285 throw ex; 286 } 287 catch (SQLException ex) { 288 // Ignore Exception 289 } 290 try { 291 runDDL(pubsubName, delTriggerQuery.toString()); 292 } 293 catch (RepException ex) { 294 throw ex; 295 } 296 catch (SQLException ex) { 297 // Ignore Exception 298 } 299 try { 300 301 runDDL(pubsubName, updTriggerQuery.toString()); 302 } 303 catch (RepException ex) { 304 throw ex; 305 } 306 catch (SQLException ex) { 307 // Ignore Exception 308 } 309 310 } 311 312 // public String getDataType(int sqlType, String typeName) { 313 // switch (sqlType) { 314 // case Types.BIT : return "boolean";// //-7; 315 // case Types.TINYINT : return "tinyint";// //-6; 316 // case Types.SMALLINT : return "smallint";// // 5; 317 // case Types.INTEGER : return "int";// // 4; 318 // case Types.BIGINT : return "long";// //-5; 319 // case Types.FLOAT : return "float";// // 6; 320 // case Types.REAL : return "real";// // 7; 321 // case Types.DOUBLE : return "double precision";// // 8; 322 // case Types.NUMERIC : return "numeric";// // 2; 323 // case Types.DECIMAL : return "decimal";// // 3; 324 // case Types.CHAR : return "char";// // 1; 325 // case Types.VARCHAR : return "varchar";// //12; 326 // case Types.LONGVARCHAR : return "long varchar";// //-1; 327 // case Types.DATE : return "date";// //91; 328 // case Types.TIME : return "time";// //92; 329 // case Types.TIMESTAMP : return "timestamp";// //93; 330 // case Types.BINARY : return "binary";// //-2; 331 // case Types.VARBINARY : return "varbinary";// //-3; 332 // case Types.LONGVARBINARY : return "long varbinary";// //-4; 333 // case Types.JAVA_OBJECT : return "long varbinary";//2000; 334 // case Types.BLOB : return "blob";//2004; 335 // case Types.CLOB : return "clob";//2005; 336 // 337 // case Types.REF : return "clob";//2006; 338 // case Types.NULL : return "";// // 0; 339 // case Types.OTHER : return "clob";//1111; 340 // case Types.DISTINCT : return "";//2001; 341 // case Types.STRUCT : return "";//2002; 342 // case Types.ARRAY : return "";//2003; 343 // case Types.DATALINK : return "";//70; 344 // case Types.BOOLEAN : return "";//16; 345 // } 346 // return null; 347 // } 348 349 public boolean isDataTypeOptionalSizeSupported(TypeInfo typeInfo) { 350 int sqlType = typeInfo.getSqlType(); 351 String typeName = typeInfo.getTypeName(); 352 switch (sqlType) { 353 case 4: 354 case 5: 355 case 7: 356 case -4: 357 case -5: 358 case -7: 359 case 91: 360 case 92: 361 case 93: 362 case -6: 363 case 8: 364 case 16: 365 case 2004: 366 case 2005: // BOOLEAN TYPE 367 return false; 368 //case -3: 369 //return !typeName.equalsIgnoreCase("varbinary"); 370 default: 371 return true; 372 } 373 } 374 375 public void setTypeInfo(TypeInfo typeInfo, ResultSet rs) throws RepException, 376 SQLException { 377 switch (typeInfo.getSqlType()) { 378 case Types.BOOLEAN: 379 typeInfo.setTypeName("boolean"); 380 break; // //16; 381 case Types.BIT: 382 if (typeInfo.getTypeName().equalsIgnoreCase("bool")) { 383 typeInfo.setTypeName("boolean"); 384 return; 385 } 386 typeInfo.setTypeName("bit"); 387 break; // //-7; 388 //case Types.BOOLEAN : //16; 389 //case Types.BIT : typeInfo.setTypeName("boolean");break;// //-7; 390 case Types.TINYINT: 391 if (isColumnSizeExceedMaximumSize(typeInfo)) { 392 typeInfo.setSqlType(Types.INTEGER); 393 typeInfo.setTypeName("int"); //4 394 return; 395 } 396 typeInfo.setSqlType(Types.SMALLINT); 397 typeInfo.setTypeName("smallint"); 398 break; // //-6; 399 case Types.SMALLINT: 400 typeInfo.setTypeName("smallint"); 401 break; // // 5; 402 case Types.INTEGER: 403 typeInfo.setTypeName("int"); 404 break; // // 4; 405 case Types.BIGINT: 406 typeInfo.setTypeName("long"); 407 break; // //-5; 408 case Types.FLOAT: 409 typeInfo.setTypeName("float"); 410 break; // // 6; 411 case Types.REAL: 412 typeInfo.setTypeName("real"); 413 break; // // 7; 414 case Types.DOUBLE: 415 typeInfo.setTypeName("double precision"); 416 break; // // 8; 417 case Types.NUMERIC: 418 typeInfo.setTypeName("numeric"); 419 break; // // 2; 420 case Types.DECIMAL: // typeInfo.setTypeName("decimal");break;// // 3; 421 if (typeInfo.getTypeName().equalsIgnoreCase("NUMERIC")) { 422 typeInfo.setTypeName("numeric"); 423 return; 424 } 425 typeInfo.setTypeName("decimal"); 426 break; // // 3; 427 // typeInfo.setTypeName("float");break;// // 8; 428 case Types.CHAR: 429 if (isColumnSizeExceedMaximumSize(typeInfo)) { 430 typeInfo.setSqlType(Types.CLOB); 431 typeInfo.setTypeName("clob"); //2005 432 return; 433 } 434 typeInfo.setTypeName("char"); 435 break; // // 1; 436 case Types.VARCHAR: 437 438 // Added by sube May 5 2004 to handle SQl_Variant 439 // (||) check added by Nancy to handle postgresql text 440 if (isColumnSizeExceedMaximumSize(typeInfo) || 441 typeInfo.getTypeName().equalsIgnoreCase("text")) { 442 typeInfo.setSqlType(Types.CLOB); 443 typeInfo.setTypeName("clob"); //2005 444 return; 445 } 446 else if (typeInfo.getTypeName().trim().equalsIgnoreCase("sql_variant")) { 447 typeInfo.setSqlType(Types.BLOB); 448 typeInfo.setTypeName("blob"); 449 return; //2004 450 } 451 typeInfo.setTypeName("varchar"); 452 break; // //12; 453 case Types.LONGVARCHAR: //typeInfo.setTypeName("clob");break;// //-1; 454 typeInfo.setSqlType(Types.CLOB); 455 typeInfo.setTypeName("clob"); 456 break; //2005 457 case Types.DATE: 458 typeInfo.setTypeName("date"); 459 break; // //91; 460 case Types.TIME: 461 typeInfo.setTypeName("time"); 462 break; // //92; 463 case Types.TIMESTAMP: 464 if (typeInfo.getTypeName().equalsIgnoreCase("Time")) { 465 typeInfo.setSqlType(Types.TIME); 466 typeInfo.setTypeName("time"); 467 break; 468 } 469 else if (typeInfo.getTypeName().equalsIgnoreCase("Date")) { 470 typeInfo.setSqlType(Types.DATE); 471 typeInfo.setTypeName("date"); 472 break; 473 474 } 475 typeInfo.setTypeName("timestamp"); 476 break; // //93; 477 case Types.BINARY: 478 479 //added by Nancy to handle postgresql bytea 480 if (typeInfo.getTypeName().equalsIgnoreCase("bytea")) { 481 typeInfo.setSqlType(Types.BLOB); 482 typeInfo.setTypeName("blob"); 483 return; 484 } 485 typeInfo.setTypeName("binary"); 486 break; // //-2; 487 case Types.VARBINARY: 488 if (typeInfo.getTypeName().equalsIgnoreCase("bit varying")) { 489 break; 490 } 491 typeInfo.setTypeName("varbinary"); 492 break; // //-3; 493 // bit varying is changed into varbinary and causing incompatibility - 3rd april 04 494 //case Types.VARBINARY : typeInfo.setTypeName("varbinary");break;// //-3; 495 case Types.LONGVARBINARY: 496 typeInfo.setTypeName("long varbinary"); 497 break; // //-4; 498 case Types.JAVA_OBJECT: 499 typeInfo.setTypeName("long varbinary"); 500 break; //2000; 501 case Types.BLOB: 502 typeInfo.setTypeName("blob"); 503 break; //2004; 504 case Types.CLOB: 505 typeInfo.setTypeName("clob"); 506 break; //2005; 507 508 case Types.REF: 509 typeInfo.setTypeName("clob"); 510 break; //2006; 511 case Types.OTHER: 512 typeInfo.setTypeName("clob"); 513 break; //1111; 514 case Types.NULL: 515 case Types.DISTINCT: //2001; 516 case Types.STRUCT: //2002; 517 case Types.ARRAY: //2003; 518 case Types.DATALINK: //70; 519 default: 520 throw new RepException("REP031", new Object[] {typeInfo.getTypeName()}); 521 } 522 } 523 524 public AbstractColumnObject getColumnObject(TypeInfo typeInfo) throws 525 RepException { 526 int sqlType = typeInfo.getSqlType(); 527 // RepPrinter.print(" DaffodilDBHandler typeInfo = " + typeInfo); 528 switch (sqlType) { 529 case 1: // char 530 case 12: // char varying 531 case -1: // long varchar 532 return new StringObject(sqlType, this); 533 case -3: // bitvarying 534 String typeName = typeInfo.getTypeName(); 535 if (typeName.equalsIgnoreCase("bit varying")) { 536 return new BytesObject(sqlType, this); 537 } 538 return new BlobObject(sqlType, this); 539 case 2005: // clob 540 return new ClobObject(sqlType, this); 541 case 2004: // blob 542 case -2: // binary 543 // Latest change by sachin - march 04 added 544 case -4: // long varbiary 545 return new BlobObject(sqlType, this); 546 case 4: // int 547 case 5: // small int 548 case -6: // tinyint 549 return new IntegerObject(sqlType, this); 550 // case 2: // numeric 551 case -5: // long 552 // Latest change by sachin - march 04 Deleted 553 // case -4: // long varbiary 554 case 2000: // long varbinary 555 return new LongObject(sqlType, this); 556 case 3: // decimal 557 if ( (typeInfo.getTypeName()).equalsIgnoreCase("NUMERIC")) { 558 return new BigDecimalObject(sqlType, this); 559 } 560 case 8: // double precision 561 case 2: // numeric 562 case 6: // float 563 return new DoubleObject(sqlType, this); 564 case 7: // real 565 return new FloatObject(sqlType, this); 566 case -7: // bit 567 case 16: // boolean 568 return new BooleanObject(sqlType, this); 569 case 91: // date 570 return new DateObject(sqlType, this); 571 case 92: // time 572 return new TimeObject(sqlType, this); 573 case 93: // time stamp 574 return new TimeStampObject(sqlType, this); 575 default: 576 throw new RepException("REP031", new Object[] {new Integer(sqlType)}); 577 } 578 } 579 580 public void createSchemas(String pubName, ArrayList schemas) throws 581 SQLException, RepException { 582 int num = schemas.size(); 583 if (num == 0) { 584 return; 585 } 586 Connection con = connectionPool.getConnection(pubName); 587 String authorization = connectionPool.getUserName(); 588 for (int i = 0; i < num; i++) { 589 String schemaName = (String) schemas.get(i); 590 if (schemaName.equalsIgnoreCase("users")) { 591 continue; 592 } 593 StringBuffer sb = new StringBuffer(); 594 sb.append(" Create Schema ").append(schemaName) 595 .append(" authorization ").append(authorization); 596 try { 597 runDDL(pubName, sb.toString()); 598 } 599 catch (SQLException ex) { 600 RepConstants.writeERROR_FILE(ex); 601 // Ignore the Exception 602 } 603 catch (RepException ex) { 604 connectionPool.returnConnection(con); 605 throw ex; 606 } 607 } 608 connectionPool.returnConnection(con); 609 } 610 611 public String getPublicationTableName() { 612 return daff_publication_TableName; 613 } 614 615 public String getSubscriptionTableName() { 616 return daff_subscription_TableName; 617 } 618 619 public String getRepTableName() { 620 return daff_rep_TableName; 621 } 622 623 public String getLogTableName() { 624 return daff_log_Table; 625 } 626 627 public String getBookMarkTableName() { 628 return daff_bookmark_TableName; 629 } 630 631 public boolean isColumnSizeExceedMaximumSize(TypeInfo typeInfo) throws 632 SQLException, RepException { 633 boolean flag = false; 634 int sqlType = typeInfo.getSqlType(); 635 int columnsize = typeInfo.getcolumnSize(); 636 switch (sqlType) { 637 case 1: // char 638 case 12: //varchar 639 if (columnsize > 4192) { 640 flag = true; 641 642 } 643 break; 644 case -6: // tinyint 645 if (columnsize > 127) { 646 flag = true; 647 648 } 649 break; 650 } 651 return flag; 652 } 653 654 public boolean isColumnSizeExceedMaximumSize1(ResultSet rs, TypeInfo typeInfo) throws 655 SQLException, RepException { 656 boolean flag = false; 657 int sqlType = typeInfo.getSqlType(); 658 int columnsize = rs.getInt("COLUMN_SIZE"); 659 switch (sqlType) { 660 case 1: // char 661 case 12: //varchar 662 if (columnsize > 4192) { 663 flag = true; 664 665 } 666 break; 667 case -6: // tinyint 668 if (columnsize > 127) { 669 flag = true; 670 671 } 672 break; 673 } 674 return flag; 675 } 676 677 public void setDefaultSchema(Connection connection) throws RepException { 678 Statement st = null; 679 try { 680 st = connection.createStatement(); 681 String query = "set schema users "; 682 st.execute(query); 683 } 684 catch (SQLException ex) { 685 RepConstants.writeERROR_FILE(ex); 686 throw new RepException("REP103", new Object[] {ex.getMessage()}); 687 } 688 finally { 689 690 try { 691 st.close(); 692 } 693 catch (SQLException ex2) { 694 } 695 } 696 697 } 698 699 public void setColumnPrecisionInTypeInfo(TypeInfo typeInfo, 700 ResultSetMetaData rsmt, 701 int columnIndex) throws SQLException { 702 int columnPrecion = rsmt.getPrecision(columnIndex); 703 typeInfo.setColumnSize(columnPrecion); 704 705 } 706 707 public boolean isPrimaryKeyException(SQLException ex) throws SQLException { 708 // 1276 for update 709 // 1275 for insert 710 // System.out.println("DaffodilDB Handler = "+ex.getErrorCode()+" ex.getMessage() = "+ex.getMessage()); 711 if (ex.getMessage().indexOf("PRIMARY KEY") != -1 || 712 ex.getErrorCode() == 1276 || ex.getErrorCode() == 1275 || 713 ex.getMessage().indexOf("Constraint Violation") != -1) { 714 return true; 715 } 716 // else if(ex.getMessage().indexOf("column does not allow nulls")!=-1){ 717 // return false; 718 // } 719 720 return false; 721 } 722 723 public int getAppropriatePrecision(int columnSize, String datatypeName) { 724 725 if (datatypeName.equalsIgnoreCase("numeric") && columnSize > 38) { 726 columnSize = 38; 727 } 728 else if ( (datatypeName.equalsIgnoreCase("decimal") || 729 datatypeName.equalsIgnoreCase("dec")) && columnSize > 38) { 730 columnSize = 38; 731 } 732 return columnSize; 733 } 734 735 protected void createIndex(String pubsubName, String tableName) throws 736 RepException { 737 StringBuffer createIndexQuery = new StringBuffer(); 738 // create index ind on cmsadm2.R_S_Bank(Rep_sync_id); 739 createIndexQuery.append("create index ") 740 .append(RepConstants.Index_Name(tableName)) 741 .append(" on ") 742 .append(tableName) 743 .append("(") 744 .append(RepConstants.shadow_sync_id1) 745 .append(")"); 746 // System.out.println(" createIndexQuery : "+createIndexQuery.toString()); 747 try { 748 runDDL(pubsubName, createIndexQuery.toString()); 749 } 750 catch (RepException ex) { 751 // Ignore the Exception 752 } 753 catch (SQLException ex) { 754 // Ignore the Exception 755 } 756 } 757 758 public int getAppropriateScale(int columnScale) throws RepException { 759 if (columnScale < 0) { 760 throw new RepException("REP026", new Object[] {"1", "38"}); 761 } 762 else if (columnScale >= 38) { 763 columnScale = 38; 764 } 765 else if (columnScale >= 0 && columnScale < 38) 766 columnScale = columnScale; 767 log.debug("returning columnScale:: " + columnScale); 768 return columnScale; 769 } 770 771 public PreparedStatement makePrimaryPreperedStatement(Connection pub_sub_Connection, String[] 772 primaryColumns, String shadowTable, String local_pub_sub_name) throws 773 SQLException, RepException { 774 StringBuffer query = new StringBuffer(); 775 query.append(" select top(1) * from "); 776 query.append(shadowTable); 777 query.append(" where "); 778 query.append(RepConstants.shadow_sync_id1); 779 query.append(" > "); 780 query.append("? "); 781 for (int i = 0; i < primaryColumns.length; i++) { 782 query.append(" and "); 783 query.append(primaryColumns[i]); 784 query.append("= ? "); 785 } 786 query.append(" order by " + RepConstants.shadow_sync_id1); 787 return pub_sub_Connection.prepareStatement(query.toString()); 788 } 789 790 public boolean isForeignKeyException(SQLException ex) throws SQLException { 791 if (ex.getErrorCode() == 1277) 792 return true; 793 else 794 return false; 795 } 796 797 public String getIgnoredColumns_Table() { 798 return daff_ignoredColumns_Table; 799 } 800 801 public String getTrackReplicationTablesUpdation_Table() { 802 return daff_trackReplicationTablesUpdation_Table; 803 } 804 805 public String getTrackPrimayKeyUpdation_Table() { 806 return daff_trackPrimaryKeyUpdation_Table; 807 } 808 809 protected void createIgnoredColumnsTable(String pubName) throws SQLException, 810 RepException { 811 StringBuffer ignoredColumnsQuery = new StringBuffer(); 812 ignoredColumnsQuery.append(" Create Table ").append(getIgnoredColumns_Table()). 813 append(" ( ") 814 .append(RepConstants.ignoredColumnsTable_tableId1).append(" int , ") 815 .append(RepConstants.ignoredColumnsTable_ignoredcolumnName2).append( 816 " varchar(255) , ") 817 .append(" Primary Key (").append(RepConstants. 818 ignoredColumnsTable_tableId1).append( 819 " , ") 820 .append(RepConstants.ignoredColumnsTable_ignoredcolumnName2).append( 821 " ) ) "); 822 runDDL(pubName, ignoredColumnsQuery.toString()); 823 } 824 825 protected void createTrackReplicationTablesUpdationTable(String pubSubName) throws 826 RepException, SQLException { 827 StringBuffer trackRepTablesUpdationQuery = new StringBuffer(); 828 trackRepTablesUpdationQuery.append(" CREATE TABLE ").append( 829 getTrackReplicationTablesUpdation_Table()).append(" ( " + 830 RepConstants.trackUpdation + " bit PRIMARY KEY) "); 831 runDDL(pubSubName, trackRepTablesUpdationQuery.toString()); 832 runDDL(pubSubName, 833 "Insert into " + getTrackReplicationTablesUpdation_Table() + 834 " values(1)"); 835 } 836 837 //implement this method for providing provision to stop updations done on shadow table 838 protected void createTriggerForTrackReplicationTablesUpdationTable(String 839 pubSubName) throws RepException, SQLException { 840 /* StringBuffer trackRepTablesUpdationTriggerQuery = new StringBuffer(); 841 trackRepTablesUpdationTriggerQuery.append(" CREATE TRIGGER TRI_") 842 .append(getTrackReplicationTablesUpdation_Table()).append( 843 " ON " + getTrackReplicationTablesUpdation_Table()) 844 .append(" AFTER INSERT AS DELETE FROM " + 845 getTrackReplicationTablesUpdation_Table() + " WHERE ") 846 .append(RepConstants.trackUpdation + " NOT IN(SELECT * FROM inserted)"); 847 runDDL(pubSubName, trackRepTablesUpdationTriggerQuery.toString());*/ 848 } 849 850 851 public PreparedStatement makePrimaryPreperedStatementBackwardTraversing(String[] primaryColumns, long lastId, String local_pub_sub_name, String shadowTable) throws SQLException, RepException { 852 StringBuffer query = new StringBuffer(); 853 query.append(" select top(1) * from "); 854 query.append(shadowTable); 855 query.append(" where "); 856 query.append(RepConstants.shadow_sync_id1); 857 query.append(" < ? "); 858 query.append(" and "); 859 query.append(RepConstants.shadow_sync_id1); 860 query.append(" > "); 861 query.append(lastId); 862 for (int i = 0; i < primaryColumns.length; i++) { 863 query.append(" and "); 864 query.append(primaryColumns[i]); 865 query.append(" = ? "); 866 } 867 query.append(" order by "); 868 query.append(RepConstants.shadow_sync_id1); 869 query.append(" desc "); 870 log.debug(query.toString()); 871 //System.out.println("DaffodilDBHandler makePrimaryPreperedStatementDelete :: " +query.toString()); 872 Connection pub_sub_Connection = connectionPool.getConnection(local_pub_sub_name); 873 return pub_sub_Connection.prepareStatement(query.toString()); 874 } 875 876 /** 877 * isSchemaSupported 878 * DaffodilDB supoort the schema so it return true 879 * @return boolean 880 */ 881 public boolean isSchemaSupported() { 882 return true; 883 } 884 885 }
Sponsors
