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