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 MSSQL Servere. 031 */ 032 public class SQLServerHandler 033 extends AbstractDataBaseHandler { 034 protected static Logger log = Logger.getLogger(SQLServerHandler.class.getName()); 035 public SQLServerHandler() {} 036 037 public SQLServerHandler(ConnectionPool connectionPool0) { 038 connectionPool = connectionPool0; 039 vendorType = Utility.DataBase_SqlServer; 040 } 041 042 protected void createSuperLogTable(String pubName) throws SQLException, 043 RepException { 044 StringBuffer logTableQuery = new StringBuffer(); 045 logTableQuery.append(" Create Table ") 046 .append(getLogTableName()) 047 .append(" ( " + RepConstants.logTable_commonId1 + " bigint identity , " + 048 RepConstants.logTable_tableName2 + " varchar(255) ) "); 049 runDDL(pubName, logTableQuery.toString()); 050 StringBuffer indexQuery =new StringBuffer(); 051 indexQuery.append("CREATE INDEX ") 052 .append(RepConstants.log_Index) 053 .append(" ON "+getLogTableName()) 054 .append("(") 055 .append(RepConstants.logTable_commonId1) 056 .append(")"); 057 //System.out.println(" Create Index on LogTable : "+indexQuery.toString()); 058 runDDL(pubName, indexQuery.toString()); 059 } 060 061 protected void createRepTable(String pubName) throws SQLException, 062 RepException 063 { 064 StringBuffer repTableQuery = new StringBuffer(); 065 repTableQuery.append(" Create Table ").append(getRepTableName()).append(" ( ") 066 .append(RepConstants.repTable_pubsubName1).append(" varchar(255) , ") 067 .append(RepConstants.repTable_tableId2).append(" int identity, ") 068 .append(RepConstants.repTable_tableName2).append(" varchar(255) , ") 069 .append(RepConstants.repTable_filter_clause3).append(" varchar(255) , ") 070 .append(RepConstants.repTable_createshadowtable6).append(" char(1) Default 'Y', ") 071 .append(RepConstants.repTable_cyclicdependency7).append(" char(1) Default 'N', ") 072 .append(RepConstants.repTable_conflict_resolver4).append(" varchar(255), ") 073 .append(" Primary Key (").append(RepConstants.repTable_pubsubName1).append(" , ") 074 .append(RepConstants.repTable_tableName2).append(" ) ) "); 075 runDDL(pubName, repTableQuery.toString()); 076 } 077 078 public void createShadowTable(String pubsubName, String tableName, 079 String allColSequence,String[] primaryColumns) throws RepException { 080 StringBuffer shadowTableQuery = new StringBuffer(); 081 shadowTableQuery.append(" Create Table ") 082 .append(RepConstants.shadow_Table(tableName)) 083 .append(" ( " + RepConstants.shadow_sync_id1 + " bigint identity , ") 084 .append(" " + RepConstants.shadow_common_id2 + " bigint , ") 085 .append(" " + RepConstants.shadow_operation3 + " char(1) , ") 086 .append(" " + RepConstants.shadow_status4 + " char(1) ") 087 .append(allColSequence) 088 .append(" , " + RepConstants.shadow_serverName_n + " varchar(255) ") 089 .append(" , " + RepConstants.shadow_PK_Changed + " char(1) ) "); 090 try { 091 runDDL(pubsubName, shadowTableQuery.toString()); 092 } 093 catch (SQLException ex) { 094 // Ignore Exception 095 } 096 catch (RepException ex) { 097 throw ex; 098 } 099 createIndex(pubsubName,RepConstants.shadow_Table(tableName)); 100 101 } 102 103 public void createScheduleTable(String subName) throws SQLException, 104 RepException { 105 StringBuffer ScheduleTableQuery = new StringBuffer(); 106 ScheduleTableQuery.append(" Create Table ") 107 .append(getScheduleTableName()) 108 .append(" ( " + RepConstants.schedule_Name + " varchar(255) , " + RepConstants.subscription_subName1 + " varchar(255) unique , ") 109 .append(" " + RepConstants.schedule_type + " varchar(255) , " ) 110 .append( " "+ RepConstants.publication_serverName3 + " varchar (255) ," + RepConstants.publication_portNo + " varchar(255) ,") 111 .append(" " + RepConstants.recurrence_type + " varchar(255) , " + RepConstants.replication_type + " varchar(255) ,") 112 .append(" " + RepConstants.schedule_time + " bigint , " ) 113 .append(" " + RepConstants.schedule_counter + " bigint , Primary Key (" + RepConstants.schedule_Name + " , " + RepConstants.subscription_subName1 + ") ) "); 114 runDDL(subName, ScheduleTableQuery.toString()); 115 } 116 117 public void createShadowTableTriggers(String pubName, String tableName, 118 HashMap colNameDataType, 119 String[] primCols) throws RepException { 120 } 121 122 public void createShadowTableTriggers(String pubName, String tableName, 123 ArrayList colNameDataType, 124 String[] primCols) throws RepException { 125 126 //SQLServer 127 // create trigger abc3 on a for insert as begin 128 // declare @va int ,@vb int; 129 // Select @va = max(a) from b; 130 // select @vb = inserted.a from inserted ; 131 // insert into d values ( @va , @vb) ; end 132 133 String serverName = getLocalServerName(); 134 String shadowTableName = RepConstants.shadow_Table(tableName); 135 StringBuffer insVars = new StringBuffer(); 136 StringBuffer delVars = new StringBuffer(); 137 StringBuffer insVarsDataType = new StringBuffer(); 138 StringBuffer delVarsDataType = new StringBuffer(); 139 StringBuffer colNameSeq = new StringBuffer(); 140 StringBuffer primVars = new StringBuffer(); 141 StringBuffer primColNameSeq = new StringBuffer(); 142 StringBuffer primColAssign1 = new StringBuffer(); 143 StringBuffer primColAssign2 = new StringBuffer(); 144 StringBuffer primVarsDataType = new StringBuffer(); 145 StringBuffer delAssignSeq = new StringBuffer(); //getColumnNameSequence(colNames,":oldRow.").toString(); 146 StringBuffer insAssignSeq = new StringBuffer(); //getColumnNameSequence(colNames,":newRow.").toString(); 147 StringBuffer insertcursor = new StringBuffer(); 148 StringBuffer deletecursor = new StringBuffer(); 149 StringBuffer oldRecordPrimaryKey = new StringBuffer(); 150 String[] oldPrimaryKeyValues = new String[primCols.length]; 151 String[] newPrimaryKeyValues = new String[primCols.length]; 152 153 154 //Object[] keys = colNameDataType.keySet().toArray(); 155 //java.util.Arrays.sort(keys); 156 int size = colNameDataType.size(); 157 for (int i = 0; i < size; ) { 158 ColumnsInfo ci = (ColumnsInfo) colNameDataType.get(i); 159 String columnName = ci.getColumnName(); 160 for (int j = 0; j < primCols.length; j++) { 161 if(primCols[j].equalsIgnoreCase(columnName)) { 162 oldPrimaryKeyValues[j] = " @varx" + i; 163 newPrimaryKeyValues[j] = " @vary" + i; 164 } 165 166 } 167 String dataType = ci.getDataTypeDeclaration(); 168 insVars.append(" @varx" + i); 169 delVars.append(" @vary" + i); 170 insVarsDataType.append(" @varx" + i + " " + dataType); 171 delVarsDataType.append(" @vary" + i + " " + dataType); 172 colNameSeq.append(columnName); 173 insAssignSeq.append(" @varx" + i + " = inserted." + columnName); 174 insertcursor.append(" inserted." + columnName); 175 delAssignSeq.append(" @vary" + i + " = deleted." + columnName); 176 deletecursor.append(" deleted." + columnName); 177 if (++i != size) { 178 insVars.append(" , "); 179 delVars.append(" , "); 180 insVarsDataType.append(" , "); 181 delVarsDataType.append(" , "); 182 colNameSeq.append(" , "); 183 insAssignSeq.append(" , "); 184 insertcursor.append(" , "); 185 delAssignSeq.append(" , "); 186 deletecursor.append(" , "); 187 } 188 } 189 190 StringBuffer trackPrimaryKeyUpdation = new StringBuffer(); 191 int primaryColumnLength = oldPrimaryKeyValues.length; 192 trackPrimaryKeyUpdation.append(" if( "); 193 for (int i = 0; i < primaryColumnLength; i++) { 194 if (i != 0) 195 trackPrimaryKeyUpdation.append(" and "); 196 trackPrimaryKeyUpdation.append(oldPrimaryKeyValues[i]).append(" != ") 197 .append(newPrimaryKeyValues[i]); 198 //.append(newPrimaryKeyValues[i]).append(")"); 199 // bjt - per "Clone" on googlegroups for daffodil os 200 } 201 // bjt - add the following per "Clone" on googlegroups for daffodil os 202 trackPrimaryKeyUpdation.append(" ) "); 203 204 205 for (int j = 0, length = primCols.length; j < length; ) { 206 String col = (String) primCols[j]; 207 primColNameSeq.append("rep_old_" + col); 208 primVars.append(" @varz" + j); 209 primColAssign1.append(" @varz" + j + " = deleted." + col); 210 oldRecordPrimaryKey.append("deleted." + col); 211 primColAssign2.append(" @varz" + j + " = inserted." + col); 212 primVarsDataType.append(" @varz" + j +" ").append(ColumnsInfo.getDataTypeDeclaration(col)); 213 if (++j != length) { 214 primColNameSeq.append(" , "); 215 primVars.append(" , "); 216 primColAssign1.append(" , "); 217 primColAssign2.append(" , "); 218 primVarsDataType.append(" , "); 219 oldRecordPrimaryKey.append(" , "); 220 } 221 } 222 223 StringBuffer insertLogTable = new StringBuffer(); 224 insertLogTable.append(" Insert into ") 225 .append(getLogTableName()) 226 .append(" ( ").append(RepConstants.logTable_tableName2) 227 .append(" ) values ( '") 228 .append(tableName).append("'); "); 229 230 StringBuffer insTriggerQuery = new StringBuffer(); 231 232 String insertCursorName = RepConstants.getCursorName(tableName, "Cursor_I"); 233 insTriggerQuery.append(" Create trigger ") 234 .append(RepConstants.getInsertTriggerName(tableName)) 235 .append(" on ").append(tableName) 236 .append(" for insert as begin declare @firetrigger bit; declare ") 237 .append(insVarsDataType.toString()).append(" , ") 238 .append(primVarsDataType.toString()) 239 .append(" ; declare " + insertCursorName).append(" cursor for select ") 240 .append(insertcursor.toString()).append(" from inserted ") 241 .append(" SELECT @firetrigger = "+RepConstants.trackUpdation +" FROM "+getTrackReplicationTablesUpdation_Table()) 242 .append(" if(@firetrigger = 1) begin") 243 .append(" open ").append(insertCursorName) 244 .append(" fetch next from ").append(insertCursorName).append(" into ") 245 .append(insVars.toString()) 246 .append(" while @@fetch_status = 0 ").append(" begin ") 247 248 .append(" Insert Into ") 249 .append(shadowTableName).append(" ( ") 250 .append(RepConstants.shadow_common_id2).append(", ") 251 .append(RepConstants.shadow_operation3).append(", ") 252 .append(RepConstants.shadow_status4).append(", ") 253 .append(colNameSeq).append(",").append(primColNameSeq).append(",") 254 .append(RepConstants.shadow_serverName_n) 255 .append(" ) Values ( null , 'I' , null , ") 256 .append(insVars).append(",").append(primVars) 257 .append(",'").append(serverName).append("') ; ") 258 .append(" fetch next from ").append(insertCursorName).append(" into ") 259 .append(insVars.toString()).append(" end ") 260 .append(" close ").append(insertCursorName) 261 .append(" end; deallocate ").append(insertCursorName).append(" end"); 262 263 String deleteCursorName = RepConstants.getCursorName(tableName, "Cursor_D"); 264 StringBuffer delTriggerQuery = new StringBuffer(); 265 delTriggerQuery.append(" Create trigger ") 266 .append(RepConstants.getDeleteTriggerName(tableName)) 267 .append(" on ").append(tableName) 268 .append(" for delete as begin declare ") 269 .append(delVarsDataType.toString()).append(" , ") 270 .append(primVarsDataType.toString()) 271 272 .append(" declare @firetrigger bit; declare " + deleteCursorName).append(" cursor for select ") 273 .append(deletecursor.toString()).append(" from deleted ") 274 .append(" SELECT @firetrigger = "+RepConstants.trackUpdation +" FROM "+getTrackReplicationTablesUpdation_Table()) 275 .append(" if(@firetrigger = 1) begin") 276 .append(" open ").append(deleteCursorName) 277 .append(" fetch next from ").append(deleteCursorName).append(" into ") 278 .append(delVars.toString()) 279 .append(" while @@fetch_status = 0 ").append(" begin ") 280 .append(" Insert Into ") 281 .append(shadowTableName).append(" ( ") 282 .append(RepConstants.shadow_common_id2).append(", ") 283 .append(RepConstants.shadow_operation3).append(", ") 284 .append(RepConstants.shadow_status4).append(", ") 285 .append(colNameSeq).append(",") 286 .append(primColNameSeq).append(",") 287 .append(RepConstants.shadow_serverName_n) 288 .append(" ) Values ( null , 'D' , null , ") 289 .append(delVars).append(",").append(primVars) 290 .append(",'").append(serverName).append("') ") 291 .append(" fetch next from ").append(deleteCursorName).append(" into ") 292 .append(delVars.toString() + " end ") 293 294 .append(" close ").append(deleteCursorName) 295 .append(" end; deallocate ").append(deleteCursorName).append(" end "); 296 297 String updateCursorNameOld = RepConstants.getCursorName(tableName, 298 "Cursor_UO"); 299 String updateCursorNameNew = RepConstants.getCursorName(tableName, 300 "Cursor_UN"); 301 StringBuffer updTriggerQuery = new StringBuffer(); 302 303 updTriggerQuery.append(" Create trigger ") 304 .append(RepConstants.getUpdateTriggerName(tableName)) 305 .append(" on ").append(tableName) 306 .append(" for update as begin declare @maxlogid bigint , ") 307 .append(delVarsDataType.toString()).append(" , ") 308 .append(insVarsDataType.toString()).append(" , ") 309 .append(primVarsDataType.toString()).append(" ; ") 310 .append(" declare @firetrigger bit; declare " + updateCursorNameOld).append(" cursor for select ") 311 .append(deletecursor.toString()).append(" , ").append( 312 oldRecordPrimaryKey) 313 .append(" from deleted ") 314 .append(" declare " + updateCursorNameNew).append(" cursor for select ") 315 .append(insertcursor.toString()).append(" from inserted ") 316 .append(" SELECT @firetrigger = "+RepConstants.trackUpdation +" FROM "+getTrackReplicationTablesUpdation_Table()) 317 .append(" if(@firetrigger = 1) begin") 318 .append(" open ").append(updateCursorNameOld) 319 .append(" open ").append(updateCursorNameNew) 320 321 .append(" fetch next from ").append(updateCursorNameOld).append( 322 " into ") 323 .append(delVars.toString()).append(" , ").append(primVars) 324 325 .append(" fetch next from ").append(updateCursorNameNew).append( 326 " into ") 327 .append(insVars.toString()) 328 329 .append(" while @@fetch_status = 0 ").append(" begin ") 330 331 .append(insertLogTable.toString()) 332 .append(" Select @maxlogid = max(").append(RepConstants. 333 logTable_commonId1) 334 .append(") from ").append(getLogTableName()).append(" ; ") 335 336 // .append(trackPriamryKeyUpdation(oldPrimaryKeyValues,newPrimaryKeyValues,primCols,tableName)) 337 338 .append(" Insert Into ") 339 .append(shadowTableName).append(" ( ") 340 .append(RepConstants.shadow_common_id2).append(", ") 341 .append(RepConstants.shadow_operation3).append(", ") 342 .append(RepConstants.shadow_status4).append(", ") 343 .append(colNameSeq).append(",") 344 .append(primColNameSeq).append(",") 345 .append(RepConstants.shadow_serverName_n) 346 .append(" ) Values ( @maxlogid , 'U' , 'B' , ") 347 .append(delVars).append(",").append(primVars) 348 .append(",'").append(serverName).append("') ;") 349 .append(" declare @pkChanged char(1); ") 350 .append(trackPrimaryKeyUpdation.toString()) 351 .append(" begin set @pkChanged ='Y'; end; ") 352 .append(" Insert Into ") 353 .append(shadowTableName).append(" ( ") 354 .append(RepConstants.shadow_common_id2).append(", ") 355 .append(RepConstants.shadow_operation3).append(", ") 356 .append(RepConstants.shadow_status4).append(", ") 357 .append(colNameSeq).append(",") 358 .append(primColNameSeq).append(",") 359 .append(RepConstants.shadow_serverName_n).append(",") 360 .append(RepConstants.shadow_PK_Changed) 361 .append(" ) Values ( @maxlogid , 'U' , 'A' , ") 362 .append(insVars).append(",").append(primVars) 363 .append(",'").append(serverName).append("',@pkChanged) ; ") 364 365 .append(" fetch next from ").append(updateCursorNameOld).append(" into ") 366 .append(delVars.toString()).append(" , ").append(primVars) 367 .append(" fetch next from ").append(updateCursorNameNew).append(" into ") 368 .append(insVars.toString()).append(" ; end") 369 370 .append(" close ").append(updateCursorNameOld) 371 .append(" close ").append(updateCursorNameNew) 372 .append(" end; deallocate ") 373 .append(updateCursorNameOld) 374 .append(" deallocate ").append(updateCursorNameNew).append(" end "); 375 376 try 377 { 378 //System.out.println(" insTriggerQuery.toString() "+insTriggerQuery.toString()); 379 runDDL(pubName, insTriggerQuery.toString()); 380 } 381 catch (RepException ex) 382 { 383 throw ex; 384 } 385 catch (SQLException ex) { 386 // ex.printStackTrace(); 387 // Ignore Exception 388 } try 389 { 390 //System.out.println(" delTriggerQuery.toString() "+delTriggerQuery.toString()); 391 runDDL(pubName, delTriggerQuery.toString()); 392 } 393 catch (RepException ex) 394 { 395 throw ex; 396 } 397 catch (SQLException ex) { 398 // ex.printStackTrace(); 399 // Ignore Exception 400 } 401 try 402 { 403 runDDL(pubName, updTriggerQuery.toString()); 404 } 405 catch (RepException ex) 406 { 407 throw ex; 408 } 409 catch (SQLException ex) { 410 // ex.printStackTrace(); 411 // Ignore Exception 412 } 413 414 } 415 416 protected void createBookMarkTable(String pubName) throws SQLException, 417 RepException { 418 StringBuffer bookmarkTableQuery = new StringBuffer(); 419 bookmarkTableQuery.append(" Create Table ") 420 .append(bookmark_TableName) 421 .append(" ( " + RepConstants.bookmark_LocalName1 + " varchar(255) , " + 422 RepConstants.bookmark_RemoteName2 + " varchar(255) , ") 423 .append(" " + RepConstants.bookmark_TableName3 + " varchar(255) , " + 424 RepConstants.bookmark_lastSyncId4 + " bigint , ") 425 .append(" " + RepConstants.bookmark_ConisderedId5 + " bigint ," + 426 RepConstants.bookmark_IsDeletedTable + 427 " char(1) default 'N' , Primary Key (" + 428 RepConstants.bookmark_LocalName1 + ", " + 429 RepConstants.bookmark_RemoteName2 + ", " + 430 RepConstants.bookmark_TableName3 + ") ) "); 431 runDDL(pubName, bookmarkTableQuery.toString()); 432 } 433 434 public boolean isDataTypeOptionalSizeSupported(TypeInfo typeInfo) { 435 // sql type 3 436 // 1 unique identifier 437 //y -3 -2 1 2 3 6 12 438 //n 1111 -6 -7 -2 -1 3 4 5 7 439 int sqlType = typeInfo.getSqlType(); 440 String typeName = typeInfo.getTypeName(); 441 switch (sqlType) { 442 case -3: 443 case 2: 444 case 6: 445 return true; 446 case -2: 447 return!typeName.equalsIgnoreCase("timestamp"); 448 case 1: 449 return!typeName.equalsIgnoreCase("uniqueidentifier"); 450 case 3: 451 if (typeName.equalsIgnoreCase("real")) { 452 return false; 453 } 454 return typeName.indexOf("money") == -1; 455 case 12: 456 return typeName.toLowerCase().startsWith("varchar") || 457 typeName.toLowerCase().equalsIgnoreCase("nvarchar") || 458 typeName.toLowerCase().equalsIgnoreCase("ID"); 459 default: 460 return false; 461 } 462 } 463 464 public void setTypeInfo(TypeInfo typeInfo, ResultSet rs) throws RepException, 465 SQLException { 466 //RepPrinter.print("In SQLServerHandler SQL Server setTypeInfo " + typeInfo); 467 //System.out.println("In SQLServerHandler SQL Server setTypeInfo " + typeInfo ); 468 String typeName = typeInfo.getTypeName().trim(); 469 int sqlType = typeInfo.getSqlType(); 470 switch (sqlType) { 471 case Types.BOOLEAN: 472 case Types.BIT: 473 typeInfo.setTypeName("bit"); 474 break; //-7; 475 case Types.TINYINT: 476 typeInfo.setTypeName("tinyint"); 477 break; //-6; 478 case Types.SMALLINT: 479 typeInfo.setTypeName("bigint"); 480 break; //5; 481 case Types.INTEGER: 482 typeInfo.setTypeName("int"); 483 break; //4; 484 case Types.BIGINT: 485 typeInfo.setTypeName("bigint"); 486 break; //-5; 487 case Types.FLOAT: 488 typeInfo.setTypeName("float"); 489 break; //6; 490 case Types.REAL: 491 typeInfo.setTypeName("real"); 492 break; //7; 493 case Types.NUMERIC: 494 typeInfo.setTypeName("numeric"); 495 break; //2; 496 case Types.DECIMAL: 497 498 // typeInfo.setTypeName("real"); 499 // typeInfo.setSqlType(Types.REAL); 500 typeInfo.setTypeName("decimal"); 501 502 break; //3; 503 case Types.CHAR: 504 typeInfo.setTypeName("char"); 505 break; //1; 506 case Types.VARCHAR: 507 if (typeInfo.getTypeName().trim().equalsIgnoreCase("sql_variant")) { 508 typeInfo.setSqlType(Types.BLOB); 509 return; 510 } 511 typeInfo.setTypeName("varchar"); 512 break; //12; 513 case Types.LONGVARCHAR: 514 typeInfo.setTypeName("text"); 515 break; //-1; 516 case Types.DATE: 517 typeInfo.setTypeName("datetime"); 518 break; //91; 519 case Types.BINARY: 520 521 // if(typeInfo.getTypeName().equalsIgnoreCase("timestamp")) 522 // { 523 // typeInfo.setTypeName("timestamp"); 524 // break; 525 // } 526 typeInfo.setTypeName("binary"); 527 break; //-2; 528 case Types.VARBINARY: 529 typeInfo.setTypeName("varbinary"); 530 break; //-3; 531 case Types.LONGVARBINARY: 532 typeInfo.setTypeName("image"); 533 break; //-4; 534 case Types.BLOB: 535 case Types.CLOB: 536 case Types.OTHER: 537 if (typeName.equalsIgnoreCase("CLOB")) { 538 typeInfo.setTypeName("text"); 539 typeInfo.setSqlType(Types.BLOB); 540 return; 541 } 542 else if (typeName.equalsIgnoreCase("BLOB")) { 543 typeInfo.setTypeName("image"); 544 typeInfo.setSqlType(Types.BLOB); 545 return; 546 } 547 typeInfo.setTypeName("sql_variant"); 548 break; //1111; 549 case Types.DOUBLE: 550 typeInfo.setTypeName("real"); 551 typeInfo.setSqlType(Types.REAL); 552 break; //8; 553 case Types.TIME: 554 typeInfo.setTypeName("datetime"); 555 break; //92; 556 case Types.TIMESTAMP: 557 if (typeName.equalsIgnoreCase("date")) { 558 typeInfo.setSqlType(Types.DATE); 559 } 560 else if (typeName.equalsIgnoreCase("time")) { 561 typeInfo.setSqlType(Types.TIME); 562 } 563 typeInfo.setTypeName("datetime"); 564 break; //93; 565 case Types.NULL: 566 case Types.JAVA_OBJECT: 567 case Types.DISTINCT: 568 case Types.STRUCT: 569 case Types.ARRAY: 570 case Types.REF: 571 case Types.DATALINK: 572 default: 573 throw new RepException("REP031", new Object[] {typeInfo.getTypeName()}); 574 } 575 } 576 577 public AbstractColumnObject getColumnObject(TypeInfo typeInfo) throws 578 RepException { 579 //RepPrinter.print(" SQLSERVER SqlType called for >>>>>>>>> " + typeInfo); 580 //System.out.println(" SQLSERVER SqlType called for >>>>>>>>> " + typeInfo); 581 String typeName = typeInfo.getTypeName().trim(); 582 int sqlType = typeInfo.getSqlType(); 583 switch (sqlType) { 584 case 2005: // SQL_vqriant 585 return new ClobObject(sqlType,this); 586 case 2004: 587 case 1111: // SQL_vqriant 588 if (typeInfo.getTypeName().equalsIgnoreCase("sql_variant")) { 589 return new StringSQL_VariantObject(sqlType,this); 590 } 591 else { 592 return new BlobObject(sqlType,this); 593 } 594 case -1: 595 return new ClobObject(sqlType,this); 596 case 12: // nvarchar 597 if (typeInfo.getTypeName().equalsIgnoreCase("sql_variant")) { 598 return new StringSQL_VariantObject(sqlType,this); 599 } 600 else { 601 if (isColumnSizeExceedMaximumSize(typeInfo)) { 602 return new SQLServerVarCharObject(sqlType,this); 603 } 604 return new StringObject(sqlType,this); 605 } 606 case 1: // char 607 return new StringObject(sqlType,this); 608 case 4: // int 609 case 5: // smallint 610 // case 2: // numeric Commented By sube May 13 611 case -6: // tinyint 612 return new IntegerObject(sqlType,this); 613 case -5: // bigint 614 return new LongObject(sqlType,this); 615 case -3: // varbinary 616 return new ByteObject(sqlType,this); 617 // return new BytesObject(sqlType,this); 618 case -4: // image 619 case -2: // binary , timestamp 620 621 // if(typeInfo.getTypeName().equalsIgnoreCase("timestamp")) 622 // return new SQLTimeStamp(sqlType); 623 return new BlobObject(sqlType,this); 624 case 2: // numeric 625 case 3: // decimal 626 case 7: // real 627 case 8: 628 return new DoubleObject(sqlType,this); 629 case 6: // float 630 return new FloatObject(sqlType,this); 631 case 16: 632 case -7: // bit 633 return new BooleanObject(sqlType,this); 634 //Date and Time add by sube 635 case 91: // DATE 636 return new DateObject(sqlType,this); 637 case 92: //TIME 638 return new TimeObject(sqlType,this); 639 case 93: // datetime , smalldatetime 640 return new TimeStampObject(sqlType,this); 641 // 642 // case 91 : // DATE 643 // case 92 : //TIME 644 // case 93: // datetime , smalldatetime 645 // return typeName.equalsIgnoreCase("date") ? (AbstractColumnObject) new DateObject(sqlType,this) 646 // : typeName.equalsIgnoreCase("time") ? (AbstractColumnObject) new TimeObject(sqlType,this) 647 // : new TimeStampObject(sqlType,this); 648 default: 649 throw new RepException("REP031", new Object[] {typeInfo.getTypeName()}); 650 } 651 } 652 653 public void makeProvisionForLOBDataTypes(HashMap dataTypeMap) { 654 } 655 656 public void makeProvisionForLOBDataTypes(ArrayList dataTypeMap) { 657 ArrayList removeKeysList = null; 658 for (int i = 0, size = dataTypeMap.size(); i < size; i++) { 659 ColumnsInfo ci = (ColumnsInfo) dataTypeMap.get(i); 660 String dataType = ci.getDataTypeDeclaration(); 661 if (dataType.indexOf("text") != -1 || 662 dataType.indexOf("image") != -1) { 663 if (removeKeysList == null) { 664 removeKeysList = new ArrayList(); 665 } 666 removeKeysList.add(ci); 667 } 668 } 669 if (removeKeysList != null) { 670 for (int i = 0, length = removeKeysList.size(); i < length; i++) { 671 dataTypeMap.remove(removeKeysList.get(i)); 672 } 673 } 674 } 675 676 public String updateDataType(String dataType0) { 677 int index = dataType0.indexOf("identity"); 678 if (index == -1) { 679 return dataType0; 680 } 681 return dataType0.substring(0, index); 682 } 683 684 public boolean isColumnSizeExceedMaximumSize(TypeInfo typeInfo) { 685 return typeInfo.getSqlType() == 12 ? typeInfo.getcolumnSize() > 4192 ? true : false : false; 686 } 687 688 public String getTableColumns(int VendorType, String ColumnName, 689 TypeInfo typeInfo, int columnPrecision, 690 ResultSet rs) throws RepException { 691 StringBuffer sb = new StringBuffer(); 692 int SQLType = typeInfo.getSqlType(); 693 if (VendorType == Utility.DataBase_DaffodilDB) { 694 switch (SQLType) { 695 case Types.TINYINT: 696 sb.append(ColumnName).append(" ").append(typeInfo.getTypeDeclaration( 697 columnPrecision)).append(" ").append("check( ").append(ColumnName). 698 append(" between 0 and 255)").toString(); 699 break; 700 default: 701 sb.append(ColumnName).append(" ").append(typeInfo.getTypeDeclaration( 702 columnPrecision)); 703 } 704 } 705 else { 706 sb.append(ColumnName).append(" ").append(typeInfo.getTypeDeclaration( 707 columnPrecision)); 708 } 709 return sb.toString(); 710 } 711 712 public boolean getPrimaryKeyErrorCode(SQLException ex) throws SQLException { 713 if (ex.getErrorCode() == 2627) { 714 return true; 715 } 716 return false; 717 } 718 719 public void setColumnPrecisionInTypeInfo(TypeInfo typeInfo, 720 ResultSetMetaData rsmt, 721 int columnIndex) throws SQLException { 722 int columnPrecion = rsmt.getPrecision(columnIndex); 723 if (typeInfo.getTypeName().equalsIgnoreCase("numeric") || 724 typeInfo.getTypeName().equalsIgnoreCase("decimal") && 725 columnPrecion > 38) { 726 typeInfo.setColumnSize(columnPrecion); 727 728 } 729 else { 730 typeInfo.setColumnSize(columnPrecion); 731 } 732 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 } 751 catch (RepException ex) { 752 // Ignore the Exception 753 } 754 catch (SQLException ex) { 755 // ex.printStackTrace(); 756 // Ignore the Exception 757 } 758 } 759 760 //scale 0 to 14 only 761 public int getAppropriateScale(int columnScale) throws RepException { 762 if (columnScale < 0) { 763 throw new RepException("REP026", new Object[] {"1", "14"}); 764 } 765 else if (columnScale >= 14) { 766 columnScale = 14; 767 } 768 else if (columnScale >= 0 && columnScale < 14) 769 columnScale = columnScale; 770 // System.out.println("returning columnScale::" + columnScale); 771 log.debug("returning columnScale::" + columnScale); 772 return columnScale; 773 } 774 775 public int getAppropriatePrecision(int columnSize, String datatypeName) { 776 if (datatypeName.equalsIgnoreCase("numeric") || 777 datatypeName.equalsIgnoreCase("decimal") && columnSize > 38) { 778 columnSize = 38; 779 } 780 return columnSize; 781 782 } 783 784 public PreparedStatement makePrimaryPreperedStatement(Connection pub_sub_Connection, String[] 785 primaryColumns, String shadowTable, String local_pub_sub_name) throws 786 SQLException, RepException { 787 StringBuffer query = new StringBuffer(); 788 query.append(" select top 1 * from "); 789 query.append(shadowTable); 790 query.append(" where "); 791 query.append(RepConstants.shadow_sync_id1); 792 query.append(" > "); 793 query.append("? "); 794 for (int i = 0; i < primaryColumns.length; i++) { 795 query.append(" and "); 796 query.append(primaryColumns[i]); 797 query.append("= ? "); 798 } 799 query.append(" order by " + RepConstants.shadow_sync_id1); 800 return pub_sub_Connection.prepareStatement(query.toString()); 801 } 802 803 804 805 public boolean isForeignKeyException(SQLException ex) throws SQLException { 806 //System.out.println(" ex Message : "+ex.getMessage()+" ex Error code : "+ex.getErrorCode()); 807 if(ex.getErrorCode()== 547) 808 return true; 809 else 810 return false; 811 } 812 813 /** 814 * isPrimaryKeyException 815 * @param ex SQLException 816 * @return boolean 817 */ 818 public boolean isPrimaryKeyException(SQLException ex) throws SQLException { 819 if (ex.getErrorCode() == 2627) { 820 return true; 821 } 822 return false; 823 } 824 825 protected void createIgnoredColumnsTable(String pubName) throws SQLException, 826 RepException { 827 StringBuffer ignoredColumnsQuery = new StringBuffer(); 828 ignoredColumnsQuery.append(" Create Table ").append(getIgnoredColumns_Table()). 829 append(" ( ") 830 .append(RepConstants.ignoredColumnsTable_tableId1).append(" int , ") 831 .append(RepConstants.ignoredColumnsTable_ignoredcolumnName2).append( 832 " varchar(255) , ") 833 .append(" Primary Key (").append(RepConstants. 834 ignoredColumnsTable_tableId1).append( 835 " , ") 836 .append(RepConstants.ignoredColumnsTable_ignoredcolumnName2).append( 837 " ) ) "); 838 runDDL(pubName, ignoredColumnsQuery.toString()); 839 } 840 841 protected void createTrackReplicationTablesUpdationTable(String pubSubName) throws 842 RepException, SQLException { 843 StringBuffer trackRepTablesUpdationQuery = new StringBuffer(); 844 trackRepTablesUpdationQuery.append(" CREATE TABLE ").append( 845 getTrackReplicationTablesUpdation_Table()).append(" ( " + 846 RepConstants.trackUpdation + " bit PRIMARY KEY) "); 847 runDDL(pubSubName, trackRepTablesUpdationQuery.toString()); 848 runDDL(pubSubName, 849 "Insert into " + getTrackReplicationTablesUpdation_Table() + " values(1)"); 850 } 851 852 protected void createTriggerForTrackReplicationTablesUpdationTable(String 853 pubSubName) throws RepException, SQLException { 854 StringBuffer trackRepTablesUpdationTriggerQuery = new StringBuffer(); 855 trackRepTablesUpdationTriggerQuery.append(" CREATE TRIGGER TRI_") 856 .append(getTrackReplicationTablesUpdation_Table()).append( 857 " ON " + getTrackReplicationTablesUpdation_Table()) 858 .append(" AFTER INSERT AS DELETE FROM " + 859 getTrackReplicationTablesUpdation_Table() + " WHERE ") 860 .append(RepConstants.trackUpdation + " NOT IN(SELECT * FROM inserted)"); 861 runDDL(pubSubName, trackRepTablesUpdationTriggerQuery.toString()); 862 } 863 864 public PreparedStatement makePrimaryPreperedStatementBackwardTraversing( 865 String[] primaryColumns, long lastId, String local_pub_sub_name, 866 String shadowTable) throws SQLException, RepException { 867 StringBuffer query = new StringBuffer(); 868 query.append(" select top 1 * from ") 869 .append(shadowTable) 870 .append(" where ") 871 .append(RepConstants.shadow_sync_id1) 872 .append(" < ? ") 873 .append(" and ") 874 .append(RepConstants.shadow_sync_id1) 875 .append(" > ") 876 .append(lastId); 877 for (int i = 0; i < primaryColumns.length; i++) { 878 query.append(" and ") 879 .append(primaryColumns[i]) 880 .append(" = ? "); 881 } 882 query.append(" order by ") 883 .append(RepConstants.shadow_sync_id1) 884 .append(" desc "); 885 log.debug(query.toString()); 886 //System.out.println("SQlServerHandler makePrimaryPreperedStatementDelete :: "+query.toString()); 887 Connection pub_sub_Connection = connectionPool.getConnection( 888 local_pub_sub_name); 889 return pub_sub_Connection.prepareStatement(query.toString()); 890 } 891 892 /** 893 * isSchemaSupported 894 * 895 * @return boolean 896 */ 897 public boolean isSchemaSupported() { 898 return true; 899 } 900 901 }
Sponsors
