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; 021 022 import java.sql.*; 023 import java.util.*; 024 025 import org.dbreplicator.replication.DBHandler.*; 026 import org.apache.log4j.Logger; 027 import org.dbreplicator.graph.DirectedGraph; 028 029 /** 030 * This Class gets the connection object of the specified publisher or subscriber 031 * and then stores it's metadata information in the DataBaseMetaData object(dbmd). 032 * This inforamtion is used for performing different operations on the tables of 033 * the publication or subscription and on the columns and constraint. 034 */ 035 036 public class CloudscapeMataDataInfo 037 extends MetaDataInfo { 038 protected static Logger log = Logger.getLogger(CloudscapeMataDataInfo.class. 039 getName()); 040 041 public CloudscapeMataDataInfo(ConnectionPool connectionPool0, 042 String pubsubName) throws RepException { 043 Connection con = connectionPool0.getConnection(pubsubName); 044 try 045 { 046 dbmd = con.getMetaData(); 047 } 048 catch (SQLException ex) { 049 log.error(ex.getMessage(),ex); 050 throw new RepException("REP101", new Object[] {ex.getMessage()}); 051 } 052 finally { 053 connectionPool0.returnConnection(con); 054 } 055 } 056 057 public CloudscapeMataDataInfo(Connection connection0) throws RepException { 058 try { 059 dbmd = connection0.getMetaData(); 060 } 061 catch (SQLException ex) { 062 log.error(ex.getMessage(), ex); 063 throw new RepException("REP101", new Object[] {ex.getMessage()}); 064 } 065 } 066 067 public void checkTableExistance(SchemaQualifiedName sname) throws 068 RepException { 069 String schema = sname.getSchemaName(); 070 String table = sname.getTableName(); 071 try { 072 // System.out.println(" dbmd ="+dbmd); 073 // System.out.println(" schema ="+schema); 074 // System.out.println(" table ="+table); 075 076 // ResultSet rs1 = dbmd.getTables(null, null, null,new String[] {"TABLE"}); 077 // CommonMetaDataInfo.showResultSet(rs1); 078 // rs1 = dbmd.getTables(null, null, table.toLowerCase(),new String[] {"TABLE"}); 079 //CommonMetaDataInfo.showResultSet(rs1); 080 081 ResultSet rs = dbmd.getTables(null, null, table.toUpperCase(), 082 new String[] {"TABLE"}); 083 084 if (rs == null || !rs.next()) { 085 log.debug("Resultset found " + rs + " OR Resultset found false"); 086 throw new RepException("REP017", new Object[] {sname.toString()}); 087 } 088 sname.setSchemaName(this, rs.getString("TABLE_SCHEM")); 089 if (rs.next()) { 090 throw new RepException("REP018", new Object[] {table}); 091 } 092 /** @todo rs has been closed */ 093 rs.close(); 094 } 095 catch (RepException ex) { 096 log.error(ex.getMessage(), ex); 097 throw ex; 098 } 099 catch (SQLException ex) { 100 log.error(ex.getMessage(), ex); 101 throw new RepException("REP006", new Object[] {ex.getMessage()}); 102 } 103 } 104 105 public String[] getTablesHierarchy(String[] tableNames) throws RepException { 106 ArrayList givenListOfTables = new ArrayList(); 107 for (int i = 0; i < tableNames.length; i++) { 108 givenListOfTables.add(tableNames[i].toUpperCase()); 109 } 110 ArrayList listToReturn = new ArrayList(); 111 ArrayList sublist1 = new ArrayList(); 112 for (int i = 0; i < tableNames.length; i++) { 113 SchemaQualifiedName sname = new SchemaQualifiedName(this, tableNames[i]); 114 String schemaName = sname.getSchemaName(); 115 String tableName = sname.getTableName(); 116 // checkParentTablesIncludedInList(givenListOfTables,schemaName,tableName); 117 listToReturn.add(sname.toString()); 118 sublist1 = getTableSequenceRegardingForeignKey(schemaName, tableName, 119 listToReturn); 120 addTablesRecursively(givenListOfTables, listToReturn, sublist1); 121 sublist1.clear(); 122 } 123 String[] arr = new String[listToReturn.size()]; 124 listToReturn.toArray(arr); 125 log.debug(listToReturn); 126 return arr; 127 } 128 129 private void addTablesRecursively(ArrayList givenListOfTables, 130 ArrayList listToReturn, ArrayList sublist) throws 131 RepException { 132 for (int j = 0; j < sublist.size(); j++) { 133 String table = (String) sublist.get(j); 134 if (listToReturn.contains(table)) { 135 SchemaQualifiedName sname = new SchemaQualifiedName(this, table); 136 String schemaName = sname.getSchemaName(); 137 String tableName = sname.getTableName(); 138 ArrayList subList1 = getTableSequenceRegardingForeignKey(schemaName, 139 tableName, listToReturn); 140 // if(givenListOfTables.contains(table)) { 141 listToReturn.remove(listToReturn.indexOf(table)); 142 listToReturn.add(table); 143 // } 144 addTablesRecursively(givenListOfTables, listToReturn, subList1); 145 subList1.clear(); 146 147 } 148 else { 149 // if(givenListOfTables.contains(table)) { 150 listToReturn.add(table); 151 // } 152 } 153 } 154 } 155 156 public void checkParentTablesIncludedInList(ArrayList givenListOfTables, 157 String schemaName, 158 String tableName) throws 159 RepException { 160 try { 161 ResultSet rs = dbmd.getImportedKeys(null, schemaName, tableName); 162 if (rs != null && rs.next()) { 163 do { 164 String pk_tableName = rs.getString("PKTABLE_SCHEM") + "." + 165 rs.getString("PKTABLE_NAME"); 166 log.debug("Primary Tablename " + pk_tableName); 167 if (!givenListOfTables.contains(pk_tableName.toUpperCase())) { 168 int lastIndexOfDot = pk_tableName.lastIndexOf("."); 169 if (lastIndexOfDot != -1) { 170 String pktableName = pk_tableName.substring(lastIndexOfDot + 1); 171 if (!givenListOfTables.contains(pktableName.toUpperCase())) { 172 throw new RepException("REP0201", 173 new Object[] {pk_tableName.toUpperCase(), 174 tableName.toUpperCase()}); 175 } 176 177 } 178 else { 179 throw new RepException("REP0201", 180 new Object[] {pk_tableName.toUpperCase(), 181 tableName.toUpperCase()}); 182 } 183 } 184 } 185 while (rs.next()); 186 /** @todo rs has been closed */ 187 rs.close(); 188 } 189 } 190 catch (SQLException ex) { 191 log.error(ex.getMessage(), ex); 192 throw new RepException("REP0202", new Object[] {tableName, ex.getMessage()}); 193 } 194 } 195 196 protected ArrayList getTableSequenceRegardingForeignKey(String schemaName, 197 String tableName, ArrayList tableList) throws RepException { 198 ArrayList list = new ArrayList(); 199 String qualifiedName = null; 200 try { 201 log.debug(" TableName : " + schemaName + "." + tableName); 202 ResultSet rs2 = dbmd.getExportedKeys(null, schemaName.toUpperCase(), 203 tableName.toUpperCase()); 204 log.debug(" ExportedKeys Table Name's ResultSet " + rs2); 205 if (rs2 != null && rs2.next()) { 206 do { 207 // String primaryTable = rs2.getString("PKTABLE_SCHEM") + "." +rs2.getString("PKTABLE_NAME"); 208 String foreignTable = rs2.getString("FKTABLE_SCHEM") + "." + 209 rs2.getString("FKTABLE_NAME"); 210 if (tableList.size() > 0) { 211 String table_list = (String) tableList.get(0); 212 if (table_list.indexOf(".") == -1) { 213 int indexInforiegnTable = foreignTable.lastIndexOf("."); 214 if (indexInforiegnTable != -1) { 215 foreignTable = foreignTable.substring(indexInforiegnTable + 1); 216 } 217 } 218 } 219 220 if (schemaName != null) { 221 qualifiedName = schemaName + "." + tableName; 222 } 223 else { 224 qualifiedName = tableName; 225 } 226 if (tableList.contains(foreignTable.toUpperCase()) && 227 !foreignTable.equalsIgnoreCase(qualifiedName)) { 228 list.add(tableList.get(tableList.indexOf(foreignTable.toUpperCase()))); 229 tableList.remove(foreignTable.toUpperCase()); 230 // throw new RepException("REP015", new Object[] {primaryTable,foreignTable}); 231 } 232 } 233 while (rs2.next()); 234 /** @todo rs2 has been closed */ 235 rs2.close(); 236 } 237 } 238 // catch (RepException ex) { 239 // throw ex; 240 // } 241 catch (SQLException ex) { 242 log.error(ex.getMessage(), ex); 243 throw new RepException("REP006", new Object[] {ex.getMessage()}); 244 } 245 log.debug(" Sublist of tables =" + list); 246 return list; 247 } 248 249 public void checkTableSequenceAccordingForeignKey(String schemaName, 250 String tableName, ArrayList tableList) throws RepException { 251 log.debug(" TableName : " + schemaName + "." + tableName); 252 try { 253 ResultSet rs2 = dbmd.getExportedKeys(null, schemaName.toUpperCase(), 254 tableName.toUpperCase()); 255 log.debug(" ExportedKeys Table Name's ResultSet " + rs2); 256 if (rs2 != null && rs2.next()) { 257 do { 258 String primaryTable = rs2.getString("PKTABLE_SCHEM") + "." + 259 rs2.getString("PKTABLE_NAME"); 260 String foreignTable = rs2.getString("FKTABLE_SCHEM") + "." + 261 rs2.getString("FKTABLE_NAME"); 262 if (primaryTable.equalsIgnoreCase(foreignTable)) 263 continue; 264 if (tableList.size() > 0) { 265 String table_list = (String) tableList.get(0); 266 if (table_list.indexOf(".") == -1) { 267 int indexInforiegnTable = foreignTable.lastIndexOf("."); 268 if (indexInforiegnTable != -1) { 269 foreignTable = foreignTable.substring(indexInforiegnTable + 1); 270 } 271 } 272 } 273 274 if (tableList.contains(foreignTable.toUpperCase())) { 275 throw new RepException("REP015", new Object[] {primaryTable, 276 foreignTable}); 277 } 278 } 279 while (rs2.next()); 280 /** @todo rs2 has been closed */ 281 rs2.close(); 282 } 283 284 } 285 catch (RepException ex) { 286 log.error(ex.getMessage(), ex); 287 throw ex; 288 } 289 catch (SQLException ex) { 290 log.error(ex.getMessage(), ex); 291 throw new RepException("REP006", new Object[] {ex.getMessage()}); 292 } 293 } 294 295 public ArrayList getColumnDataTypeInfo(AbstractDataBaseHandler dbh, 296 String schemaName, String tableName) throws 297 RepException, SQLException { 298 ArrayList colInfoList = new ArrayList(); 299 ResultSet rs = dbmd.getColumns(null, schemaName.toUpperCase(), 300 tableName.toUpperCase(), "%"); 301 if (rs == null || !rs.next()) { 302 throw new RepException("Internal Error", null); 303 } 304 do { 305 TypeInfo typeInfo = new TypeInfo( 306 dbh.updateDataType(rs.getString("TYPE_NAME")), rs.getInt("DATA_TYPE")); 307 int columnPrecision = rs.getInt("COLUMN_SIZE"); 308 int columnScale = rs.getInt("DECIMAL_DIGITS"); 309 String typeName = rs.getString("TYPE_NAME").trim(); 310 columnPrecision = dbh.getAppropriatePrecision(columnPrecision, typeName); 311 columnScale = dbh.getAppropriateScale(columnScale); 312 typeInfo.setOptionalSizeProperty(dbh.isDataTypeOptionalSizeSupported( 313 typeInfo)); 314 typeInfo.setColumnSize(rs.getInt("COLUMN_SIZE")); 315 typeInfo.setColumnScale(columnScale); 316 dbh.setTypeInfo(typeInfo, rs); 317 //colInfoMap.put(rs.getString("COLUMN_NAME").trim(),typeInfo.getTypeDeclaration(rs.getInt("COLUMN_SIZE"))); 318 colInfoList.add(new ColumnsInfo(rs.getString("COLUMN_NAME").trim(), 319 typeInfo.getTypeDeclaration( 320 columnPrecision))); 321 log.debug("Column Name " + rs.getString("COLUMN_NAME").trim()); 322 log.debug("Type declaration " + 323 typeInfo.getTypeDeclaration(columnPrecision)); 324 } 325 while (rs.next()); 326 /** @todo rs has been closed */ 327 rs.close(); 328 return colInfoList; 329 } 330 331 public HashMap getColumnsInfo(String schemaName, String tableName) throws 332 RepException, SQLException { 333 HashMap colInfoMap = new HashMap(); 334 ResultSet rs = dbmd.getColumns(null, schemaName.toUpperCase(), 335 tableName.toUpperCase(), "%"); 336 if (rs == null || !rs.next()) { 337 log.debug("Resultset found" + rs + " OR Resultset found false"); 338 throw new RepException("Internal Error", null); 339 } 340 int i = 0; 341 do { 342 //colInfoMap.put(rs.getString("COLUMN_NAME").trim(),""); 343 colInfoMap.put(new Integer(i++), 344 new ColumnsInfo(rs.getString("COLUMN_NAME").trim(), null)); 345 } 346 while (rs.next()); 347 log.debug(" colInfoMap : " + colInfoMap); 348 349 /** @todo rs has been closed */ 350 rs.close(); 351 return colInfoMap; 352 } 353 354 /** 355 * @param dbh 356 * @param srcVendorType 357 * @param schemaName 358 * @param tableName 359 * @param tgtVendorType 360 * @return StringBuffer 361 * @throws SQLException 362 * @throws RepException 363 */ 364 public String generateColumnsQueryForClientNode(AbstractDataBaseHandler dbh, 365 int srcVendorType, 366 String schemaName, 367 String tableName, 368 int tgtVendorType) throws 369 RepException { 370 StringBuffer sb = new StringBuffer(); 371 AbstractDataBaseHandler remotedbh = Utility.getDatabaseHandler( 372 tgtVendorType); 373 AbstractDataBaseHandler optSizedbh = (srcVendorType == tgtVendorType) ? 374 dbh : remotedbh; 375 try { 376 ResultSet rs = dbmd.getColumns(null, schemaName.toUpperCase(), 377 tableName.toUpperCase(), "%"); 378 if (rs == null || !rs.next()) { 379 log.debug("Resultset found" + rs + " OR Resultset found false"); 380 throw new RepException("REP033", 381 new Object[] {schemaName + "." + tableName}); 382 } 383 do { 384 385 String typeName = dbh.updateDataType(rs.getString("TYPE_NAME")); 386 int dataType = rs.getInt("DATA_TYPE"); 387 TypeInfo typeInfo = new TypeInfo(typeName, dataType); 388 int columnPrecision = rs.getInt("COLUMN_SIZE"); 389 int columnScale = rs.getInt("DECIMAL_DIGITS"); 390 String columnName = rs.getString("COLUMN_NAME"); 391 columnPrecision = optSizedbh.getAppropriatePrecision(columnPrecision, 392 typeName); 393 typeInfo.setColumnSize(rs.getInt("COLUMN_SIZE")); 394 columnScale = optSizedbh.getAppropriateScale(columnScale); 395 int columnScalePublisher = dbh.getAppropriateScale(columnScale); 396 if (columnScale > columnScalePublisher) { 397 typeInfo.setColumnScale(columnScalePublisher); 398 } 399 else { 400 typeInfo.setColumnScale(columnScale); 401 } 402 optSizedbh.setTypeInfo(typeInfo, rs); 403 typeInfo.setOptionalSizeProperty(optSizedbh. 404 isDataTypeOptionalSizeSupported( 405 typeInfo)); 406 String nullable = rs.getString("IS_NULLABLE").trim(); 407 // String columnInfo =remotedbh.getTableColumns(dbh.getvendorName(),columnName,typeInfo,columnPrecision,rs); 408 // System.out.println("CLOUDSCAPECommonMetaDataInfo.GENERATECOLUMNQUERYFORCLIENT ="+columnInfo); 409 // sb.append(columnInfo); 410 if (nullable.equalsIgnoreCase("NO") && 411 tgtVendorType == Utility.DataBase_Cloudscape) { 412 sb.append(columnName).append(" ").append(typeInfo.getTypeDeclaration( 413 columnPrecision)).append(" NOT NULL "); 414 } 415 else if (nullable.equalsIgnoreCase("NO") && 416 tgtVendorType == Utility.DataBase_DB2) { 417 sb.append(columnName).append(" ").append(typeInfo.getTypeDeclaration( 418 columnPrecision)).append(" NOT NULL "); 419 } 420 else { 421 sb.append(columnName).append(" ").append(typeInfo.getTypeDeclaration( 422 columnPrecision)); 423 } 424 if (srcVendorType == tgtVendorType) { 425 typeInfo.setTypeName(typeName); 426 } 427 //System.out.println(" type Name === " + typeName + " sql Type " + sqlType ) ; 428 String defaultValue = rs.getString("COLUMN_DEF"); //String => default value (may be <code>null</code>) 429 if (defaultValue != null && !defaultValue.equalsIgnoreCase("NULL")) 430 sb.append("DEFAULT").append(" ").append(defaultValue); 431 // String nullable = rs.getString("IS_NULLABLE").trim(); // String => "NO" means column definitely does not allow NULL values; "YES" means the column might allow NULL values. An empty string means nobody knows. 432 if (nullable.equalsIgnoreCase("NO")) { 433 //System.out.println(" I m here"); 434 if (notNullColumns == null) { 435 notNullColumns = new ArrayList(); 436 } 437 notNullColumns.add(columnName); 438 } 439 //new ColumnsInfo(columnName,typeName,sqlType,columnSize/*,defaultValue,nullable*/); 440 sb.append(" , "); 441 } 442 while (rs.next()); 443 /** @todo rs has been closed */ 444 rs.close(); 445 } 446 catch (RepException ex) { 447 log.error(ex.getMessage(), ex); 448 throw ex; 449 } 450 catch (SQLException ex) { 451 log.error(ex.getMessage(), ex); 452 throw new RepException("REP006", new Object[] {ex.getMessage()}); 453 } 454 log.debug(sb.toString()); 455 return sb.toString(); 456 } 457 458 public String getAppliedConstraints(String schemaName, String tableName, 459 TreeMap primConsMap) throws RepException { 460 StringBuffer cons = new StringBuffer(); 461 appendPrimaryConstraints(schemaName, tableName, cons, primConsMap); 462 appendUniqueKeyConstraints(schemaName, tableName, cons); 463 // appendForeignConstraints(schemaName, tableName, cons, primConsMap); 464 appendCheckConstraints(cons); 465 return cons.toString(); 466 } 467 468 public String getAppliedConstraintsForExistingTable(String schemaName, 469 String tableName, TreeMap primConsMap, AbstractDataBaseHandler dbh) throws 470 RepException { 471 StringBuffer cons = new StringBuffer(); 472 appendPrimaryConstraints(schemaName, tableName, cons, primConsMap); 473 appendUniqueKeyConstraints(schemaName, tableName, cons); 474 // appendForeignConstraints(schemaName, tableName, cons, primConsMap); 475 addCheckConstraintForExistingTable(schemaName, tableName, primConsMap, dbh); 476 appendCheckConstraints(cons); 477 return cons.toString(); 478 } 479 480 public void addCheckConstraintForExistingTable(String schemaName, 481 String tableName, 482 TreeMap primConsMap, 483 AbstractDataBaseHandler dbh) throws 484 RepException { 485 try { 486 ResultSet rs = dbmd.getColumns(null, schemaName.toUpperCase(), 487 tableName.toUpperCase(), "%"); 488 if (rs == null || !rs.next()) { 489 log.debug("Resultset found " + rs + " OR Resultset found false"); 490 throw new RepException("REP033", 491 new Object[] {schemaName + "." + tableName}); 492 } 493 do { 494 String columnName = rs.getString("COLUMN_NAME"); 495 String nullable = rs.getString("IS_NULLABLE").trim(); 496 if (nullable.equalsIgnoreCase("NO")) { 497 if (notNullColumns == null) { 498 notNullColumns = new ArrayList(); 499 } 500 notNullColumns.add(columnName); 501 } 502 } 503 while (rs.next()); 504 /** @todo rs has been closed */ 505 rs.close(); 506 } 507 catch (RepException ex) { 508 log.error(ex.getMessage(), ex); 509 throw ex; 510 } 511 catch (SQLException ex) { 512 log.error(ex.getMessage(), ex); 513 throw new RepException("REP006", new Object[] {ex.getMessage()}); 514 } 515 516 } 517 518 private void appendPrimaryConstraints(String schemaName, String tableName, 519 StringBuffer cols, TreeMap consTableMap) throws 520 RepException { 521 HashMap primcolmap = null; 522 try { 523 ResultSet rs = dbmd.getPrimaryKeys(null, schemaName.toUpperCase(), 524 tableName.toUpperCase()); 525 if (rs == null || !rs.next()) { 526 log.debug("Resultset found " + rs + " OR Resultset found false"); 527 throw new RepException("REP034", 528 new Object[] {schemaName + "." + tableName}); 529 } 530 primcolmap = new HashMap(); 531 do { 532 primcolmap.put(new Integer(rs.getInt("KEY_SEQ")), 533 rs.getString("COLUMN_NAME")); 534 } 535 while (rs.next()); 536 /** @todo rs has been closed */ 537 rs.close(); 538 } 539 catch (RepException ex) { 540 log.error(ex.getMessage(), ex); 541 throw ex; 542 } 543 catch (SQLException ex) { 544 log.error(ex.getMessage(), ex); 545 throw new RepException("REP006", new Object[] {ex.getMessage()}); 546 } 547 Object[] indexes = primcolmap.keySet().toArray(); 548 Arrays.sort(indexes); 549 cols.append(" Primary Key ("); 550 StringBuffer temp = new StringBuffer(); 551 for (int i = 0; i < indexes.length; i++) { 552 if (i != 0) { 553 temp.append(","); 554 } 555 temp.append(primcolmap.get(indexes[i])); 556 } 557 cols.append(temp.toString()).append(" )"); 558 log.debug(cols.toString()); 559 consTableMap.put(schemaName + "." + tableName, 560 schemaName + "." + tableName + "(" + temp.toString() + ")"); 561 } 562 563 // PKTABLE_CAT</B> String => primary key table catalog being imported (may be <code>null</code>) 564 // PKTABLE_SCHEM</B> String => primary key table schema being imported (may be <code>null</code>) 565 // PKTABLE_NAME</B> String => primary key table name being imported 566 // PKCOLUMN_NAME</B> String => primary key column name being imported 567 // FKTABLE_CAT</B> String => foreign key table catalog (may be <code>null</code>) 568 // FKTABLE_SCHEM</B> String => foreign key table schema (may be <code>null</code>) 569 // FKTABLE_NAME</B> String => foreign key table name 570 // FKCOLUMN_NAME</B> String => foreign key column name 571 // KEY_SEQ</B> short => sequence number within a foreign key 572 // UPDATE_RULE</B> short => 573 // DELETE_RULE</B> short => 574 // FK_NAME</B> String => foreign key name (may be <code>null</code>) 575 // PK_NAME</B> String => primary key name (may be <code>null</code>) 576 // DEFERRABILITY</B> short => can the evaluation of foreign key constraints be deferred until commit 577 private void appendForeignConstraints(String schemaName, String tableName, 578 StringBuffer cols, TreeMap consTableMap) throws 579 RepException { 580 HashMap fk_Keys = new HashMap(); 581 HashMap fk_pk = new HashMap(); 582 try { 583 ResultSet rs = dbmd.getImportedKeys(null, schemaName.toUpperCase(), 584 tableName.toUpperCase()); 585 if (rs == null || !rs.next()) { 586 log.debug("Resultset found " + rs + " OR Resultset found false"); 587 return; 588 } 589 do { 590 String pk_tableName = rs.getString("PKTABLE_SCHEM") + "." + 591 rs.getString("PKTABLE_NAME"); 592 Object primObject = consTableMap.get(pk_tableName.toUpperCase()); 593 594 if (primObject == null) { 595 ResultSet primaryColumns = dbmd.getPrimaryKeys(null, 596 rs.getString("PKTABLE_SCHEM"), rs.getString("PKTABLE_NAME")); 597 HashMap map = new HashMap(); 598 while (primaryColumns.next()) { 599 int columnIndex = primaryColumns.getInt("KEY_SEQ"); 600 String columnName = primaryColumns.getString("COLUMN_NAME"); 601 map.put(new Integer(columnIndex), columnName); 602 } 603 Object[] indexes = map.keySet().toArray(); 604 Arrays.sort(indexes); 605 StringBuffer temp = new StringBuffer(); 606 for (int i = 0; i < indexes.length; i++) { 607 if (i != 0) { 608 temp.append(","); 609 } 610 temp.append(map.get(indexes[i])); 611 } 612 consTableMap.put(pk_tableName.toUpperCase(), 613 pk_tableName.toUpperCase() + "(" + temp.toString() + 614 ") "); 615 primObject = consTableMap.get(pk_tableName.toUpperCase()); 616 } 617 if (primObject != null) { 618 String fk_tableName = rs.getString("FKTABLE_SCHEM") + "." + 619 rs.getString("FKTABLE_NAME"); 620 String mapKey = rs.getString("FK_NAME"); 621 Object ob = fk_Keys.get(mapKey); 622 if (ob == null) { 623 HashMap colsMap = new HashMap(); 624 colsMap.put(new Integer(rs.getInt("KEY_SEQ")), 625 rs.getString("FKCOLUMN_NAME")); 626 fk_pk.put(mapKey, primObject); 627 fk_Keys.put(mapKey, colsMap); 628 } 629 else { 630 ( (HashMap) ob).put(new Integer(rs.getInt("KEY_SEQ")), 631 rs.getString("FKCOLUMN_NAME")); 632 } 633 } 634 } 635 while (rs.next()); 636 /** @todo rs has been closed */ 637 rs.close(); 638 } 639 catch (SQLException ex) { 640 log.error(ex.getMessage(), ex); 641 throw new RepException("REP006", new Object[] {ex.getMessage()}); 642 } 643 Object[] fkeys = fk_Keys.keySet().toArray(); 644 Arrays.sort(fkeys); 645 for (int i = 0; i < fkeys.length; i++) { 646 HashMap map = (HashMap) fk_Keys.get(fkeys[i]); 647 Object[] indexes = map.keySet().toArray(); 648 Arrays.sort(indexes); 649 cols.append(" , Foreign Key ("); 650 for (int j = 0; j < indexes.length; j++) { 651 if (j != 0) { 652 cols.append(","); 653 } 654 cols.append(map.get(indexes[j])); 655 } 656 cols.append(" ) References " + fk_pk.get(fkeys[i])); 657 log.debug(cols.toString()); 658 } 659 } 660 661 private void appendCheckConstraints(StringBuffer sb) { 662 for (int i = 0, length = notNullColumns.size(); i < length; i++) { 663 sb.append(" , Check ( ").append(notNullColumns.get(i)).append( 664 " is not null ) "); 665 } 666 notNullColumns = null; 667 } 668 669 public void setPrimaryColumns(RepTable repTable, String schemaName, 670 String tableName) throws RepException, 671 SQLException { 672 HashMap primcolmap = new HashMap(); 673 ResultSet rs = dbmd.getPrimaryKeys(null, schemaName.toUpperCase(), 674 tableName.toUpperCase()); 675 if (rs == null || !rs.next()) { 676 log.debug("Resultset found " + rs + " OR Resultset found false"); 677 throw new RepException("REP034", new Object[] {tableName}); 678 } 679 do { 680 primcolmap.put(new Integer(rs.getInt("KEY_SEQ")), 681 rs.getString("COLUMN_NAME")); 682 } 683 while (rs.next()); 684 /** @todo rs has been closed */ 685 rs.close(); 686 687 String[] primColumns = new String[primcolmap.size()]; 688 Object[] indexes = primcolmap.keySet().toArray(); 689 Arrays.sort(indexes); 690 for (int i = 0; i < indexes.length; i++) { 691 primColumns[i] = (String) primcolmap.get(indexes[i]); 692 } 693 repTable.setPrimaryColumns(primColumns); 694 } 695 696 public String getExistingTableQuery(AbstractDataBaseHandler dbh, 697 SchemaQualifiedName sname, 698 int pubVendorType) throws RepException, 699 SQLException { 700 StringBuffer sb = new StringBuffer(); 701 String table = sname.getTableName(); 702 String schema = sname.getSchemaName(); 703 sb.append(" Create Table ").append(sname.toString()).append(" ( "); 704 ResultSet rs = dbmd.getColumns(null, schema.toUpperCase(), 705 table.toUpperCase(), "%"); 706 if (rs == null || !rs.next()) { 707 log.debug("Resultset found " + rs + " OR Resultset found false"); 708 throw new RepException("REP033", new Object[] {sname.toString()}); 709 } 710 do { 711 String typeName = dbh.updateDataType(rs.getString("TYPE_NAME")); 712 int dataType = rs.getInt("DATA_TYPE"); 713 TypeInfo typeInfo = new TypeInfo(typeName, dataType); 714 int columnPrecision = rs.getInt("COLUMN_SIZE"); 715 columnPrecision = dbh.getAppropriatePrecision(columnPrecision, typeName); 716 String columnName = rs.getString("COLUMN_NAME"); 717 typeInfo.setColumnSize(rs.getInt("COLUMN_SIZE")); 718 dbh.setTypeInfo(typeInfo, rs); 719 typeInfo.setOptionalSizeProperty(dbh.isDataTypeOptionalSizeSupported( 720 typeInfo)); 721 String nullable = rs.getString("IS_NULLABLE").trim(); 722 // String columnInfo =dbh.getTableColumns(pubVendorType,columnName,typeInfo,columnPrecision,rs); 723 // System.out.println("CLOUDSCAPECommonMetaDataInfo.GetexistingTableQuery ="+columnInfo); 724 // sb.append(columnInfo); 725 if (nullable.equalsIgnoreCase("NO")) { 726 sb.append(columnName).append(" ").append(typeInfo.getTypeDeclaration( 727 columnPrecision)).append(" NOT NULL "); 728 } 729 else { 730 sb.append(columnName).append(" ").append(typeInfo.getTypeDeclaration( 731 columnPrecision)); 732 } 733 sb.append(" , "); 734 } 735 while (rs.next()); 736 /** @todo rs has been closed */ 737 rs.close(); 738 // appendPrimaryConstraints(schema,table,sb,new HashMap()); 739 sb.append(" " + 740 getAppliedConstraintsForExistingTable(schema, table, 741 new TreeMap(String.CASE_INSENSITIVE_ORDER), 742 dbh) + " ) "); 743 log.debug(sb.toString()); 744 return sb.toString(); 745 } 746 747 /** 748 * checkChildTableIncludedInDropTableList 749 * 750 * @param pubRepTableList ArrayList 751 * @param dropTableList ArrayList 752 * @return ArrayList 753 */ 754 protected void checkChildTableIncludedInDropTableList(ArrayList 755 pubRepTableList, String[] dropTableList) { 756 757 } 758 759 public ArrayList getChildTables(String parentTable) throws RepException { 760 String foreignTable = null; 761 ArrayList childTableList = new ArrayList(); 762 try { 763 SchemaQualifiedName sname = new SchemaQualifiedName(this, parentTable); 764 ResultSet rs2 = dbmd.getExportedKeys(null, sname.getSchemaName(), 765 sname.getTableName()); 766 log.debug(" ExportedKeys Table Name's ResultSet " + rs2); 767 if (rs2 != null && rs2.next()) { 768 do { 769 foreignTable = rs2.getString("FKTABLE_SCHEM") + "." + 770 rs2.getString("FKTABLE_NAME"); 771 childTableList.add(foreignTable); 772 } 773 while (rs2.next()); 774 } 775 /** @todo rs has been closed */ 776 rs2.close(); 777 778 } 779 catch (SQLException ex) { 780 log.error(ex.getMessage(), ex); 781 throw new RepException("REP006", new Object[] {ex.getMessage()}); 782 } 783 System.out.println("CommonMetaDataInfo.getChildTables() : " + 784 childTableList); 785 return childTableList; 786 } 787 788 public Object[] getImportedColsOfChildTable(String parentTable, 789 String childTable) throws 790 RepException { 791 ArrayList fkColsList = new ArrayList(); 792 ArrayList referColsList = new ArrayList(); 793 try { 794 SchemaQualifiedName sname = new SchemaQualifiedName(this, childTable); 795 ResultSet rs = dbmd.getImportedKeys(null, sname.getSchemaName(), 796 sname.getTableName()); 797 if (rs == null || !rs.next()) { 798 log.debug("Resultset found " + rs + " OR Resultset found false"); 799 return null; 800 } 801 do { 802 String pk_tableName = rs.getString("PKTABLE_SCHEM") + "." + 803 rs.getString("PKTABLE_NAME"); 804 if (pk_tableName.equalsIgnoreCase(parentTable)) { 805 fkColsList.add(rs.getString("FKCOLUMN_NAME")); 806 referColsList.add(rs.getString("PKCOLUMN_NAME")); 807 } 808 } 809 while (rs.next()); 810 /** @todo rs has been closed */ 811 rs.close(); 812 } 813 catch (SQLException ex) { 814 log.error(ex.getMessage(), ex); 815 throw new RepException("REP006", new Object[] {ex.getMessage()}); 816 } 817 String[] fkCols = new String[fkColsList.size()]; 818 fkColsList.toArray(fkCols); 819 String[] pkCols = new String[referColsList.size()]; 820 referColsList.toArray(pkCols); 821 822 return new Object[] { 823 fkCols, pkCols}; 824 825 } 826 827 public ArrayList getImportedTables(SchemaQualifiedName schemaQualifiedName, 828 List passedSchemaQualifiedNamesList, 829 DirectedGraph graph, 830 String[] removeCycleTableNames0) throws 831 RepException { 832 try { 833 ResultSet rs = dbmd.getImportedKeys(null, 834 schemaQualifiedName.getSchemaName(), 835 schemaQualifiedName.getTableName()); 836 ArrayList listOfSuperTables = new ArrayList(); 837 while (rs.next()) { 838 SchemaQualifiedName superTableSchemaQualifiedName = new 839 SchemaQualifiedName(this, null, rs.getString(2), rs.getString(3)); 840 /** @todo 841 * define Ex in properties file 842 * */ 843 844 if (!passedSchemaQualifiedNamesList.contains( 845 superTableSchemaQualifiedName)) { 846 throw new RepException("REP038", new Object[] { 847 superTableSchemaQualifiedName, 848 schemaQualifiedName}); 849 } 850 //to handle tables in cycle case.we do not add edge for tables in removeCycleTableNames arraylist provided by user 851 String[] removeCycleTableNames = removeCycleTableNames0; 852 boolean addEdge = true; 853 if (removeCycleTableNames != null) { 854 for (int i = 0; i < removeCycleTableNames.length; i++) { 855 StringTokenizer str = new StringTokenizer(removeCycleTableNames[i], 856 "-"); 857 SchemaQualifiedName sname = new SchemaQualifiedName(this, 858 (String) str.nextElement()); 859 SchemaQualifiedName sourceSname = sname; 860 checkTableExistance(sname); 861 if (!passedSchemaQualifiedNamesList.contains(sname)) 862 throw new RepException("Rep0206", new Object[] {sname}); 863 // System.out.println("source::::"+sourceSname.toString()); 864 sname = new SchemaQualifiedName(this, (String) str.nextElement()); 865 checkTableExistance(sname); 866 if (!passedSchemaQualifiedNamesList.contains(sname)) 867 throw new RepException("Rep0206", new Object[] {sname}); 868 SchemaQualifiedName targetSname = sname; 869 // System.out.println("target::::"+targetSname.toString()); 870 // System.out.println("schemaQualifiedName.toString():::"+schemaQualifiedName.toString()); 871 // System.out.println("superTableSchemaQualifiedName.toString():::"+superTableSchemaQualifiedName.toString()); 872 if (sourceSname.toString().equalsIgnoreCase(schemaQualifiedName. 873 toString()) && 874 targetSname.toString().equalsIgnoreCase( 875 superTableSchemaQualifiedName.toString())) 876 addEdge = false; 877 } 878 } 879 // to handle self referencing case 880 if (schemaQualifiedName.toString().equalsIgnoreCase( 881 superTableSchemaQualifiedName.toString())) 882 addEdge = false; 883 884 if (addEdge) { 885 //System.out.println("schemaQualifiedName.toString():::" +schemaQualifiedName.toString()); 886 //System.out.println("superTableSchemaQualifiedName.toString():::" +superTableSchemaQualifiedName.toString()); 887 graph.addEdge(schemaQualifiedName, superTableSchemaQualifiedName, 1); 888 } 889 890 if (!listOfSuperTables.contains(superTableSchemaQualifiedName)) 891 listOfSuperTables.add(superTableSchemaQualifiedName); 892 } 893 rs.close(); 894 return listOfSuperTables; 895 } 896 catch (NoSuchElementException ex1) { 897 throw new RepException("Rep0204", null); 898 } 899 catch (SQLException ex) { 900 throw new RepException("REP039", new Object[] {ex.getMessage()}); 901 } 902 } 903 904 public ArrayList getForeignKeyConstraints(String schemaName, String tableName) throws 905 RepException { 906 HashMap fk_Keys = new HashMap(); 907 HashMap fk_pk = new HashMap(); 908 HashMap consPkTableMap = new HashMap(); 909 HashMap referenceString = new HashMap(); 910 StringBuffer alterTableQuery = new StringBuffer(); 911 ResultSet rs; 912 ArrayList foreignKeyConstraintsList = new ArrayList(); 913 // System.out.println("in the getForeignKeyConstraints :::::::::: ****************[" + schemaName+"] tableName ["+tableName+"]"); 914 try { 915 rs = dbmd.getImportedKeys(null, schemaName, tableName); 916 boolean next = rs.next(); 917 if (!next) { 918 return null; 919 } 920 do { 921 String pk_tableName = rs.getString("PKTABLE_SCHEM") + "." + 922 rs.getString("PKTABLE_NAME"); 923 924 String mapPkKey = rs.getString("FK_NAME"); 925 //changed to handle the case for unique key 926 927 Object primObjectTemp = consPkTableMap.get(mapPkKey); 928 if (primObjectTemp == null) { 929 HashMap colsPkMap = new HashMap(); 930 colsPkMap.put(new Integer(rs.getInt("KEY_SEQ")), 931 rs.getString("PKCOLUMN_NAME")); 932 933 consPkTableMap.put(mapPkKey, colsPkMap); 934 } 935 else { 936 ( (HashMap) primObjectTemp).put(new Integer(rs.getInt("KEY_SEQ")), 937 rs.getString("PKCOLUMN_NAME")); 938 } 939 Object primObject = consPkTableMap.get(mapPkKey); 940 if (primObject != null) { 941 String fk_tableName = rs.getString("FKTABLE_SCHEM") + "." + 942 rs.getString("FKTABLE_NAME"); 943 String mapKey = rs.getString("FK_NAME"); 944 Object ob = fk_Keys.get(mapKey); 945 if (ob == null) { 946 HashMap colsMap = new HashMap(); 947 colsMap.put(new Integer(rs.getInt("KEY_SEQ")), 948 rs.getString("FKCOLUMN_NAME")); 949 fk_pk.put(mapKey, primObject); 950 fk_Keys.put(mapKey, colsMap); 951 } 952 else { 953 ( (HashMap) ob).put(new Integer(rs.getInt("KEY_SEQ")), 954 rs.getString("FKCOLUMN_NAME")); 955 } 956 } 957 HashMap mapTemp = (HashMap) primObject; 958 Iterator itr = mapTemp.values().iterator(); 959 Object colNames = null; 960 do { 961 if (colNames != null) { 962 colNames = itr.next() + "," + colNames; 963 } 964 else { 965 colNames = itr.next(); 966 } 967 } 968 while (itr.hasNext()); 969 referenceString.put(mapPkKey, pk_tableName + " ( " + colNames + " )"); 970 } 971 while (rs.next()); 972 rs.close(); 973 } 974 catch (SQLException ex) { 975 throw new RepException("REP006", new Object[] { 976 ex.getMessage()}); 977 } 978 979 Object[] fkeys = fk_Keys.keySet().toArray(); 980 Object[] pkeys = referenceString.keySet().toArray(); 981 for (int i = 0; i < fkeys.length; i++) { 982 983 alterTableQuery.append("alter table ") 984 .append(schemaName + "." + tableName).append(" add constraint ") 985 .append(fkeys[i]); 986 HashMap map = (HashMap) fk_Keys.get(fkeys[i]); 987 Object[] indexes = map.keySet().toArray(); 988 Arrays.sort(indexes); 989 alterTableQuery.append(" Foreign Key ("); 990 for (int j = 0; j < indexes.length; j++) { 991 if (j != 0) { 992 alterTableQuery.append(","); 993 } 994 alterTableQuery.append(map.get(indexes[j])); 995 } 996 alterTableQuery.append(" ) References "); 997 998 // for (int j = 0; j < pkeys.length; j++) { 999 alterTableQuery.append(referenceString.get(pkeys[i])); //fk_pk.get(fkeys[i])); 1000 // } 1001 //System.out.println("Alter table query is : " + alterTableQuery.toString()); 1002 foreignKeyConstraintsList.add(alterTableQuery.toString()); 1003 alterTableQuery.setLength(0); 1004 1005 } 1006 return foreignKeyConstraintsList; 1007 } 1008 1009 public void appendUniqueKeyConstraints(String schemaName, String tableName, 1010 StringBuffer cols) throws RepException { 1011 1012 HashMap consPkTableMap = new HashMap(); 1013 HashMap uniqueCol = new HashMap(); 1014 ArrayList primaryKeyColumns = new ArrayList(); 1015 ResultSet rs = null, rsPk = null; 1016 1017 try { 1018 rsPk = dbmd.getPrimaryKeys(null, schemaName, tableName); 1019 while (rsPk.next()) { 1020 primaryKeyColumns.add(rsPk.getString("PK_NAME")); 1021 } 1022 rs = dbmd.getExportedKeys(null, schemaName, tableName); 1023 boolean next = rs.next(); 1024 if (!next) { 1025 return; 1026 } 1027 do { 1028 String pk_tableName = rs.getString("PKTABLE_SCHEM") + "." + 1029 rs.getString("PKTABLE_NAME"); 1030 //System.out.println("pk_tableName" + rs.getString("PKTABLE_NAME")); 1031 //System.out.println("KEY_SEQ::::" + rs.getShort("KEY_SEQ")); 1032 //System.out.println("PKCOLUMN_NAME " + pk_tableName + "(" +rs.getString("PKCOLUMN_NAME") + ")"); 1033 String mapPkKey = rs.getString("PK_NAME"); 1034 if (!primaryKeyColumns.contains(mapPkKey)) { 1035 Object primObjectTemp = consPkTableMap.get(mapPkKey); 1036 if (primObjectTemp == null) { 1037 HashMap colsPkMap = new HashMap(); 1038 colsPkMap.put(new Integer(rs.getInt("KEY_SEQ")), 1039 rs.getString("PKCOLUMN_NAME")); 1040 1041 consPkTableMap.put(mapPkKey, colsPkMap); 1042 } 1043 else { 1044 ( (HashMap) primObjectTemp).put(new Integer(rs.getInt("KEY_SEQ")), 1045 rs.getString("PKCOLUMN_NAME")); 1046 } 1047 Object primObject = consPkTableMap.get(mapPkKey); 1048 HashMap mapTemp = (HashMap) primObject; 1049 Iterator itr = mapTemp.values().iterator(); 1050 Object colNames = null; 1051 do { 1052 if (colNames != null) { 1053 colNames = itr.next() + "," + colNames; 1054 } 1055 else { 1056 colNames = itr.next(); 1057 } 1058 } 1059 while (itr.hasNext()); 1060 uniqueCol.put(mapPkKey, " Unique " + " ( " + colNames + " )"); 1061 } 1062 } 1063 while (rs.next()); 1064 1065 } 1066 catch (SQLException ex) { 1067 throw new RepException("REP006", new Object[] { 1068 ex.getMessage()}); 1069 } 1070 finally { 1071 try { 1072 if (rs != null) { 1073 rs.close(); 1074 } 1075 if (rsPk != null) { 1076 rsPk.close(); 1077 } 1078 } 1079 catch (SQLException ex1) { 1080 } 1081 } 1082 // System.out.println("unique cols::" + uniqueCol); 1083 Object[] ukeys = uniqueCol.keySet().toArray(); 1084 for (int j = 0; j < ukeys.length; j++) { 1085 cols.append("," + uniqueCol.get(ukeys[j])); 1086 // System.out.println("Ukeys::::" + cols.toString()); 1087 } 1088 } 1089 1090 public ArrayList getExportedTableCols(SchemaQualifiedName repTable) throws 1091 RepException { 1092 ResultSet rs = null; 1093 ArrayList exportedColumns = new ArrayList(); 1094 try { 1095 rs = dbmd.getExportedKeys(null, repTable.getSchemaName(), 1096 repTable.getTableName()); 1097 boolean next = rs.next(); 1098 if (!next) { 1099 return null; 1100 } 1101 do { 1102 exportedColumns.add(rs.getString("PKCOLUMN_NAME")); 1103 // System.out.println("Ukeys::::" + cols.toString()); 1104 } 1105 while (rs.next()); 1106 } 1107 catch (SQLException ex) { 1108 throw new RepException("REP006", new Object[] { 1109 ex.getMessage()}); 1110 } 1111 finally { 1112 try { 1113 if (rs != null) { 1114 rs.close(); 1115 } 1116 } 1117 catch (SQLException ex1) { 1118 } 1119 } 1120 return exportedColumns; 1121 } 1122 1123 public void setAllColumns(RepTable repTable, String schemaName, 1124 String tableName) throws RepException, SQLException { 1125 1126 ResultSet rs = dbmd.getColumns(null, schemaName, tableName, "%"); 1127 if (rs == null || !rs.next()) { 1128 log.debug("Resultset found " + rs + " OR Resultset found false"); 1129 throw new RepException("REP033", 1130 new Object[] {schemaName + "." + tableName}); 1131 } 1132 ArrayList allColumnsList=new ArrayList(); 1133 try { 1134 do { 1135 allColumnsList.add(rs.getString("COLUMN_NAME")); 1136 } 1137 while (rs.next()); 1138 1139 } 1140 finally { 1141 if (rs != null) 1142 rs.close(); 1143 } 1144 // for (int i = 0; i < allColumnsList.toArray().length; i++) { 1145 // System.out.println(" allColumnsList.toArray() : "+allColumnsList.toArray()[i].getClass()); 1146 // } 1147 int numberOfColumns =allColumnsList.size(); 1148 String columns[] =new String[numberOfColumns]; 1149 for (int i = 0; i < numberOfColumns; i++) { 1150 columns[i] = (String)allColumnsList.get(i); 1151 } 1152 repTable.setAllColumns(columns); 1153 } 1154 1155 1156 }
Sponsors
