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