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 021 package org.dbreplicator.replication; 022 import java.io.*; 023 import java.util.Properties; 024 import java.sql.*; 025 import java.util.*; 026 import org.dbreplicator.replication.RepException; 027 import org.dbreplicator.replication.RepConstants; 028 029 public class UpdatePreviousVersion { 030 031 private static String DaffodilDB="DaffodilDB"; 032 private static String SQLServer="SQLServer"; 033 private static String Oracle="Oracle"; 034 private static String Postgres="Postgres"; 035 private static String CloudScape="CloudScape"; 036 private static String DB2="DB2"; 037 038 private static String VendorType="VendorType"; 039 private static String DataBaseName="DataBaseName"; 040 private static String Driver="Driver"; 041 private static String Url="Url"; 042 private static String UserName="UserName"; 043 private static String Password="Password"; 044 045 public UpdatePreviousVersion() throws Exception{ 046 try { 047 getDatabaseVendorType(); 048 } 049 catch (Exception ex) {} 050 } 051 052 053 private void getDatabaseVendorType(){ 054 try { 055 File f = new File("." + File.separator + "config.ini"); 056 if (!f.exists()) 057 f.createNewFile(); 058 Properties p = new Properties(); 059 p.load(new FileInputStream(f)); 060 VendorType = p.getProperty("VendorType"); 061 System.out.println(" VENDOR TYPE : "+VendorType); 062 DataBaseName=p.getProperty("DataBaseName"); 063 System.out.println(" DATABASE NAME : "+DataBaseName); 064 Driver=p.getProperty("Driver"); 065 System.out.println(" DATABASE DRIVER :"+Driver); 066 Url=p.getProperty("Url"); 067 System.out.println(" DATABASE URL :"+Url); 068 UserName=p.getProperty("UserName"); 069 System.out.println(" USER NAME : "+UserName); 070 Password=p.getProperty("Password"); 071 System.out.println(" PASSWORD :"+Password); 072 createTableQuery(VendorType); 073 } catch (Exception ex) { 074 } 075 } 076 private void createTableQuery(String dbServer) throws Exception { 077 dbServer =dbServer.trim(); 078 if (dbServer.equalsIgnoreCase(SQLServer)) { 079 Connection SQLconn = getConnection(Driver, Url, UserName, Password); 080 //Creating trackReplicationTablesUpdation_Table Table 081 StringBuffer trackRepTabUpdation_Table = new StringBuffer(); 082 trackRepTabUpdation_Table.append(" Create Table ") 083 .append(RepConstants.trackReplicationTablesUpdation_Table) 084 .append(" ( ") 085 .append(RepConstants.trackUpdation) 086 .append(" bit PRIMARY KEY default 1) "); 087 runDDL(SQLconn, trackRepTabUpdation_Table.toString()); 088 runDDL(SQLconn,"Insert into "+RepConstants.trackReplicationTablesUpdation_Table+" values(1)" ); 089 //Creating Index on Rep_LogTable 090 StringBuffer indexQuery = new StringBuffer(); 091 indexQuery.append(" CREATE INDEX ") 092 .append(RepConstants.log_Index) 093 .append(" ON " + RepConstants.log_Table) 094 .append(" ( ") 095 .append(RepConstants.logTable_commonId1) 096 .append(" ) "); 097 runDDL(SQLconn, indexQuery.toString()); 098 // Creating Rep_ignoredcolumnName Table 099 StringBuffer ignoredColumnsQuery = new StringBuffer(); 100 ignoredColumnsQuery.append(" Create Table ") 101 .append(RepConstants.ignoredColumns_Table). 102 append(" ( ") 103 .append(RepConstants.ignoredColumnsTable_tableId1).append(" int , ") 104 .append(RepConstants.ignoredColumnsTable_ignoredcolumnName2) 105 .append(" varchar(255) , ") 106 .append(" Primary Key (") 107 .append(RepConstants.ignoredColumnsTable_tableId1) 108 .append(" , ") 109 .append(RepConstants.ignoredColumnsTable_ignoredcolumnName2) 110 .append(" ) ) "); 111 runDDL(SQLconn, ignoredColumnsQuery.toString()); 112 // Alter Rep_RepTable 113 StringBuffer alterRepTable = new StringBuffer(); 114 alterRepTable.append(" Alter table " + RepConstants.rep_TableName) 115 .append(" add " + RepConstants.repTable_createshadowtable6) 116 .append(" char(1) NOT NULL default 'Y' , ") 117 .append(RepConstants.repTable_cyclicdependency7) 118 .append(" char(1) NOT NULL default 'N' "); 119 runDDL(SQLconn,alterRepTable.toString()); 120 121 StringBuffer alterBookMarkTable = new StringBuffer(); 122 alterBookMarkTable.append(" Alter table " + RepConstants.bookmark_TableName) 123 .append(" add " + RepConstants.bookmark_IsDeletedTable) 124 .append(" CHAR(1) DEFAULT 'N'"); 125 runDDL(SQLconn,alterBookMarkTable.toString()); 126 } 127 128 129 130 else if (dbServer.equalsIgnoreCase(Postgres)) { 131 Connection SQLconn = getConnection(Driver, Url, UserName, Password); 132 //Creating trackReplicationTablesUpdation_Table Table 133 StringBuffer trackRepTabUpdation_Table = new StringBuffer(); 134 trackRepTabUpdation_Table.append(" Create Table ") 135 .append(RepConstants.trackReplicationTablesUpdation_Table) 136 .append(" ( ") 137 .append(RepConstants.trackUpdation) 138 .append(" bit PRIMARY KEY default 1 ) "); 139 runDDL(SQLconn, trackRepTabUpdation_Table.toString()); 140 runDDL(SQLconn,"Insert into "+RepConstants.trackReplicationTablesUpdation_Table+" values(1)" ); 141 //Creating Index on Rep_LogTable 142 StringBuffer indexQuery = new StringBuffer(); 143 indexQuery.append(" CREATE INDEX ") 144 .append(RepConstants.log_Index) 145 .append(" ON " + RepConstants.log_Table) 146 .append(" ( ") 147 .append(RepConstants.logTable_commonId1) 148 .append(" ) "); 149 runDDL(SQLconn, indexQuery.toString()); 150 // Creating Rep_ignoredcolumnName Table 151 StringBuffer ignoredColumnsQuery = new StringBuffer(); 152 ignoredColumnsQuery.append(" Create Table ") 153 .append(RepConstants.ignoredColumns_Table). 154 append(" ( ") 155 .append(RepConstants.ignoredColumnsTable_tableId1).append(" int , ") 156 .append(RepConstants.ignoredColumnsTable_ignoredcolumnName2) 157 .append(" varchar(255) , ") 158 .append(" Primary Key (") 159 .append(RepConstants.ignoredColumnsTable_tableId1) 160 .append(" , ") 161 .append(RepConstants.ignoredColumnsTable_ignoredcolumnName2) 162 .append(" ) ) "); 163 runDDL(SQLconn, ignoredColumnsQuery.toString()); 164 // Alter Rep_RepTable 165 StringBuffer alterRepTable = new StringBuffer(); 166 alterRepTable.append(" Alter table " + RepConstants.rep_TableName) 167 .append(" add " + RepConstants.repTable_createshadowtable6) 168 .append(" char(1) NOT NULL default 'Y' , add ") 169 .append(RepConstants.repTable_cyclicdependency7) 170 .append(" char(1) NOT NULL default 'N' "); 171 runDDL(SQLconn,alterRepTable.toString()); 172 StringBuffer alterBookMarkTable = new StringBuffer(); 173 alterBookMarkTable.append(" Alter table " + RepConstants.bookmark_TableName) 174 .append(" add " + RepConstants.bookmark_IsDeletedTable) 175 .append(" CHAR(1) DEFAULT 'N'"); 176 runDDL(SQLconn,alterBookMarkTable.toString()); 177 178 } 179 else if (dbServer.equalsIgnoreCase(DaffodilDB)) { 180 Connection SQLconn = getConnection(Driver, Url, UserName, Password); 181 //Creating trackReplicationTablesUpdation_Table Table 182 StringBuffer trackRepTabUpdation_Table = new StringBuffer(); 183 trackRepTabUpdation_Table.append(" Create Table ") 184 .append(RepConstants.trackReplicationTablesUpdation_Table) 185 .append(" ( ") 186 .append(RepConstants.trackUpdation) 187 .append(" bit PRIMARY KEY default 1 ) "); 188 runDDL(SQLconn, trackRepTabUpdation_Table.toString()); 189 runDDL(SQLconn,"Insert into "+RepConstants.trackReplicationTablesUpdation_Table+" values(1)" ); 190 //Creating Index on Rep_LogTable 191 StringBuffer indexQuery = new StringBuffer(); 192 indexQuery.append(" CREATE INDEX ") 193 .append(RepConstants.log_Index) 194 .append(" ON " + RepConstants.log_Table) 195 .append(" ( ") 196 .append(RepConstants.logTable_commonId1) 197 .append(" ) "); 198 runDDL(SQLconn, indexQuery.toString()); 199 // Creating Rep_ignoredcolumnName Table 200 StringBuffer ignoredColumnsQuery = new StringBuffer(); 201 ignoredColumnsQuery.append(" Create Table ") 202 .append(RepConstants.ignoredColumns_Table). 203 append(" ( ") 204 .append(RepConstants.ignoredColumnsTable_tableId1).append(" int , ") 205 .append(RepConstants.ignoredColumnsTable_ignoredcolumnName2) 206 .append(" varchar(255) , ") 207 .append(" Primary Key (") 208 .append(RepConstants.ignoredColumnsTable_tableId1) 209 .append(" , ") 210 .append(RepConstants.ignoredColumnsTable_ignoredcolumnName2) 211 .append(" ) ) "); 212 runDDL(SQLconn, ignoredColumnsQuery.toString()); 213 // Alter Rep_RepTable 214 StringBuffer alterRepTable = new StringBuffer(); 215 alterRepTable.append(" Alter table " + RepConstants.rep_TableName) 216 .append(" add " + RepConstants.repTable_createshadowtable6) 217 .append(" char(1) default 'Y' "); 218 runDDL(SQLconn,alterRepTable.toString()); 219 220 StringBuffer alterRepTable1 = new StringBuffer(); 221 alterRepTable1.append(" Alter table " + RepConstants.rep_TableName) 222 .append(" add " + RepConstants.repTable_cyclicdependency7) 223 .append(" char(1) default 'N' "); 224 runDDL(SQLconn,alterRepTable1.toString()); 225 226 StringBuffer alterBookMarkTable = new StringBuffer(); 227 alterBookMarkTable.append(" Alter table " + RepConstants.bookmark_TableName) 228 .append(" add " + RepConstants.bookmark_IsDeletedTable) 229 .append(" CHAR(1) DEFAULT 'N'"); 230 runDDL(SQLconn,alterBookMarkTable.toString()); 231 232 233 } 234 235 236 else if (dbServer.equalsIgnoreCase(Oracle)) { 237 Connection SQLconn = getConnection(Driver, Url, UserName, Password); 238 //Creating trackReplicationTablesUpdation_Table Table 239 StringBuffer trackRepTabUpdation_Table = new StringBuffer(); 240 trackRepTabUpdation_Table.append(" Create Table ") 241 .append(RepConstants.trackReplicationTablesUpdation_Table) 242 .append(" ( ") 243 .append(RepConstants.trackUpdation) 244 .append(" smallint PRIMARY KEY default 1 ) "); 245 runDDL(SQLconn, trackRepTabUpdation_Table.toString()); 246 runDDL(SQLconn,"Insert into "+RepConstants.trackReplicationTablesUpdation_Table+" values(1)" ); 247 //Creating Index on Rep_LogTable 248 StringBuffer indexQuery = new StringBuffer(); 249 indexQuery.append(" CREATE INDEX ") 250 .append(RepConstants.log_Index) 251 .append(" ON " + RepConstants.log_Table) 252 .append(" ( ") 253 .append(RepConstants.logTable_commonId1) 254 .append(" ) "); 255 runDDL(SQLconn, indexQuery.toString()); 256 // Creating Rep_ignoredcolumnName Table 257 StringBuffer ignoredColumnsQuery = new StringBuffer(); 258 ignoredColumnsQuery.append(" Create Table ") 259 .append(RepConstants.ignoredColumns_Table). 260 append(" ( ") 261 .append(RepConstants.ignoredColumnsTable_tableId1).append(" int , ") 262 .append(RepConstants.ignoredColumnsTable_ignoredcolumnName2) 263 .append(" varchar(255) , ") 264 .append(" Primary Key (") 265 .append(RepConstants.ignoredColumnsTable_tableId1) 266 .append(" , ") 267 .append(RepConstants.ignoredColumnsTable_ignoredcolumnName2) 268 .append(" ) ) "); 269 runDDL(SQLconn, ignoredColumnsQuery.toString()); 270 // Alter Rep_RepTable 271 StringBuffer alterRepTable = new StringBuffer(); 272 alterRepTable.append(" Alter table " + RepConstants.rep_TableName) 273 .append(" add ( " + RepConstants.repTable_createshadowtable6) 274 .append(" char(1) default 'Y' , ") 275 .append(RepConstants.repTable_cyclicdependency7) 276 .append(" char(1) default 'N' ) "); 277 runDDL(SQLconn,alterRepTable.toString()); 278 StringBuffer alterBookMarkTable = new StringBuffer(); 279 alterBookMarkTable.append(" Alter table " + RepConstants.bookmark_TableName) 280 .append(" add " + RepConstants.bookmark_IsDeletedTable) 281 .append(" CHAR(1) DEFAULT 'N'"); 282 runDDL(SQLconn,alterBookMarkTable.toString()); 283 284 } 285 286 287 else if (dbServer.equalsIgnoreCase(CloudScape)) { 288 Connection SQLconn = getConnection(Driver, Url, UserName, Password); 289 //Creating trackReplicationTablesUpdation_Table Table 290 StringBuffer trackRepTabUpdation_Table = new StringBuffer(); 291 trackRepTabUpdation_Table.append(" Create Table ") 292 .append(RepConstants.trackReplicationTablesUpdation_Table) 293 .append(" ( ") 294 .append(RepConstants.trackUpdation) 295 .append(" smallint not null PRIMARY KEY default 1 ) "); 296 runDDL(SQLconn, trackRepTabUpdation_Table.toString()); 297 runDDL(SQLconn,"Insert into "+RepConstants.trackReplicationTablesUpdation_Table+" values(1)" ); 298 //Creating Index on Rep_LogTable 299 StringBuffer indexQuery = new StringBuffer(); 300 indexQuery.append(" CREATE INDEX ") 301 .append(RepConstants.log_Index) 302 .append(" ON " + RepConstants.log_Table) 303 .append(" ( ") 304 .append(RepConstants.logTable_commonId1) 305 .append(" ) "); 306 runDDL(SQLconn, indexQuery.toString()); 307 // Creating Rep_ignoredcolumnName Table 308 StringBuffer ignoredColumnsQuery = new StringBuffer(); 309 ignoredColumnsQuery.append(" Create Table ") 310 .append(RepConstants.ignoredColumns_Table). 311 append(" ( ") 312 .append(RepConstants.ignoredColumnsTable_tableId1).append(" bigint NOT NULL , ") 313 .append(RepConstants.ignoredColumnsTable_ignoredcolumnName2) 314 .append(" varchar(255) NOT NULL, ") 315 .append(" Primary Key (") 316 .append(RepConstants.ignoredColumnsTable_tableId1) 317 .append(" , ") 318 .append(RepConstants.ignoredColumnsTable_ignoredcolumnName2) 319 .append(" ) ) "); 320 runDDL(SQLconn, ignoredColumnsQuery.toString()); 321 // Alter Rep_RepTable 322 StringBuffer alterRepTable = new StringBuffer(); 323 alterRepTable.append(" Alter table dbo." + RepConstants.rep_TableName) 324 .append(" add column " + RepConstants.repTable_createshadowtable6) 325 .append(" char(1) NOT NULL default 'Y' "); 326 runDDL(SQLconn,alterRepTable.toString()); 327 328 StringBuffer alterRepTable1 = new StringBuffer(); 329 alterRepTable1.append(" Alter table dbo." + RepConstants.rep_TableName) 330 .append(" add column " + RepConstants.repTable_cyclicdependency7) 331 .append(" char(1) NOT NULL default 'N' "); 332 runDDL(SQLconn,alterRepTable1.toString()); 333 StringBuffer alterBookMarkTable = new StringBuffer(); 334 alterBookMarkTable.append(" Alter table " + RepConstants.bookmark_TableName) 335 .append(" add " + RepConstants.bookmark_IsDeletedTable) 336 .append(" CHAR(1) DEFAULT 'N'"); 337 runDDL(SQLconn,alterBookMarkTable.toString()); 338 } 339 else if (dbServer.equalsIgnoreCase(DB2)) { 340 Connection SQLconn = getConnection(Driver, Url, UserName, Password); 341 //Creating trackReplicationTablesUpdation_Table Table 342 StringBuffer trackRepTabUpdation_Table = new StringBuffer(); 343 trackRepTabUpdation_Table.append(" Create Table ") 344 .append(RepConstants.trackReplicationTablesUpdation_Table) 345 .append(" ( ") 346 .append(RepConstants.trackUpdation) 347 .append(" bit PRIMARY KEY default 1 ) "); 348 runDDL(SQLconn, trackRepTabUpdation_Table.toString()); 349 //Creating Index on Rep_LogTable 350 StringBuffer indexQuery = new StringBuffer(); 351 indexQuery.append(" CREATE INDEX ") 352 .append(RepConstants.log_Index) 353 .append(" ON " + RepConstants.log_Table) 354 .append(" ( ") 355 .append(RepConstants.logTable_commonId1) 356 .append(" ) "); 357 runDDL(SQLconn, indexQuery.toString()); 358 runDDL(SQLconn,"Insert into "+RepConstants.trackReplicationTablesUpdation_Table+" values(1)" ); 359 // Creating Rep_ignoredcolumnName Table 360 StringBuffer ignoredColumnsQuery = new StringBuffer(); 361 ignoredColumnsQuery.append(" Create Table ") 362 .append(RepConstants.ignoredColumns_Table). 363 append(" ( ") 364 .append(RepConstants.ignoredColumnsTable_tableId1).append(" int , ") 365 .append(RepConstants.ignoredColumnsTable_ignoredcolumnName2) 366 .append(" varchar(255) , ") 367 .append(" Primary Key (") 368 .append(RepConstants.ignoredColumnsTable_tableId1) 369 .append(" , ") 370 .append(RepConstants.ignoredColumnsTable_ignoredcolumnName2) 371 .append(" ) ) "); 372 runDDL(SQLconn, ignoredColumnsQuery.toString()); 373 // Alter Rep_RepTable 374 StringBuffer alterRepTable = new StringBuffer(); 375 alterRepTable.append(" Alter table " + RepConstants.rep_TableName) 376 .append(" add " + RepConstants.repTable_createshadowtable6) 377 .append(" char(1) NOT NULL default 'Y' , ") 378 .append(RepConstants.repTable_cyclicdependency7) 379 .append(" char(1) NOT NULL default 'N' "); 380 runDDL(SQLconn,alterRepTable.toString()); 381 382 StringBuffer alterBookMarkTable = new StringBuffer(); 383 alterBookMarkTable.append(" Alter table " + RepConstants.bookmark_TableName) 384 .append(" add " + RepConstants.bookmark_IsDeletedTable) 385 .append(" CHAR(1) DEFAULT 'N'"); 386 runDDL(SQLconn,alterBookMarkTable.toString()); 387 388 } 389 } 390 private Connection getConnection(String driver0, String url0, 391 String user0, String pwd0) throws 392 Exception { 393 try { 394 Class.forName(driver0); 395 return DriverManager.getConnection(url0, user0, pwd0); 396 } 397 catch (ClassNotFoundException ex) { 398 System.out.println(" INVALID DRIVER "); 399 throw ex; 400 } 401 catch (SQLException ex) { 402 System.out.println(" INVALID URL "); 403 throw ex; 404 } 405 } 406 private void runDDL(Connection conn, String query) throws SQLException, RepException { 407 Statement stt = conn.createStatement(); 408 try { 409 // System.out.println(" query =" + query); 410 stt.execute(query); 411 // System.out.println(" QUERY EXECUTED SUCCESSFULLY "); 412 } 413 catch (SQLException ex) { 414 } 415 } 416 417 public static void main(String[] args)throws Exception { 418 UpdatePreviousVersion upv=new UpdatePreviousVersion(); 419 } 420 }
Sponsors
