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.dbreplicator.replication.zip.ImportedTablesInfo; 027 import org.dbreplicator.graph.DirectedGraph; 028 import org.apache.log4j.Logger; 029 030 /** 031 * This Abstract class gets the connection object of the specified publisher or subscriber 032 * and then stores it's metadata information in the DataBaseMetaData object(dbmd). 033 * This inforamtion is used for performing different operations on the tables of 034 * the publication or subscription and on the columns and constraint. 035 * 036 */ 037 038 public abstract class MetaDataInfo 039 { 040 protected DatabaseMetaData dbmd; 041 protected ArrayList notNullColumns; 042 protected static Logger log =Logger.getLogger(MetaDataInfo.class.getName()); 043 public MetaDataInfo() throws RepException 044 {} 045 046 public MetaDataInfo(Connection connection0) throws RepException 047 { 048 try 049 { 050 dbmd = connection0.getMetaData(); 051 } 052 catch (SQLException ex) 053 { 054 log.error(ex.getMessage(),ex); 055 throw new RepException("REP101", new Object[] 056 {ex.getMessage()}); 057 } 058 } 059 060 /** 061 * Checks the existance of the pericular table in the database. 062 * @param sname 063 * @throws RepException 064 */ 065 066 public void checkTableExistance(SchemaQualifiedName sname) throws 067 RepException 068 { 069 String schema = sname.getSchemaName(); 070 String table = sname.getTableName(); 071 //ResultSet rs2 = dbmd.getTables(null,"%","%",new String[] {"TABLE"}); 072 //ResultSet rs3 = dbmd.getTables(null,"%",table.toUpperCase(),new String[] {"TABLE"}); // Oracle 073 //showResultSet(rs3); 074 //ResultSet rs = dbmd.getTables(null,schema.toUpperCase(),table.toUpperCase(),new String[] {"TABLE"}); // Oracle 075 try { 076 ResultSet rs = dbmd.getTables(null, schema, table, new String[] { 077 "TABLE"}); // SQL Server Oracle 078 try { 079 if (rs == null | !rs.next()) { 080 throw new RepException("REP017", new Object[] { 081 sname.toString()}); 082 } 083 sname.setSchemaName(this, rs.getString("TABLE_SCHEM")); 084 if (rs.next()) { 085 throw new RepException("REP018", new Object[] { 086 table}); 087 } 088 } 089 finally { 090 if(rs != null) 091 rs.close(); 092 } 093 } 094 catch (RepException ex) 095 { 096 log.error(ex.getMessage(),ex); 097 throw ex; 098 } 099 catch (SQLException ex) 100 { 101 log.error(ex.getMessage(),ex); 102 throw new RepException("REP006", new Object[] 103 {ex.getMessage()}); 104 } 105 } 106 107 108 109 /** 110 * Check the sequence of table according to primary key. 111 * If table are not sorted order then throw the error 112 * It checks that if there is a table with any primary key and some 113 * foreign keys then all the tables of foreign key columns are also 114 * included in the publisher or not. 115 * 116 * @param schemaName String 117 * @param tableName String 118 * @param tableList ArrayList 119 * @throws RepException 120 */ 121 public void checkTableSequenceAccordingForeignKey(String schemaName,String tableName, ArrayList tableList) throws RepException 122 { 123 try 124 { 125 ResultSet rs2 = dbmd.getExportedKeys(null, schemaName, tableName); 126 try { 127 if (rs2 != null && rs2.next()) { 128 do { 129 String primaryTable = rs2.getString("PKTABLE_SCHEM") + "." + rs2.getString("PKTABLE_NAME"); 130 String foreignTable = rs2.getString("FKTABLE_SCHEM") + "." + rs2.getString("FKTABLE_NAME"); 131 if (tableList.contains(foreignTable.toUpperCase())) { 132 throw new RepException("REP015", new Object[] {primaryTable, foreignTable}); 133 } 134 } 135 while (rs2.next()); 136 } 137 } 138 finally { 139 rs2.close(); 140 } 141 } 142 catch (RepException ex) 143 { 144 log.error(ex.getMessage(),ex); 145 throw ex; 146 } 147 catch (SQLException ex) 148 { 149 log.error(ex.getMessage(),ex); 150 throw new RepException("REP006", new Object[] 151 {ex.getMessage()}); 152 } 153 } 154 155 /** 156 * For each column, its dataype is converted in respective database datatype 157 * format in typeinfo then it is set in the dbh, passed to Class ColumnsInfo 158 * and it's objects are stored in colInfoList. 159 * 160 * @param dbh 161 * @param schemaName 162 * @param tableName 163 * @return ArrayList 164 * @throws RepException 165 * @throws SQLException 166 */ 167 168 public ArrayList getColumnDataTypeInfo(AbstractDataBaseHandler dbh,String schemaName, String tableName) throws RepException, SQLException 169 { 170 ArrayList colInfoList = new ArrayList(); 171 ResultSet rs = dbmd.getColumns(null, schemaName, tableName, "%"); 172 if (rs == null || !rs.next()) 173 { 174 throw new RepException("Internal Error", null); 175 } 176 try { 177 do { 178 TypeInfo typeInfo = new TypeInfo(dbh.updateDataType(rs.getString("TYPE_NAME")), rs.getInt("DATA_TYPE")); 179 int columnPrecision = rs.getInt("COLUMN_SIZE"); 180 int columnScale=rs.getInt("DECIMAL_DIGITS"); 181 String typeName = rs.getString("TYPE_NAME").trim(); 182 columnPrecision = dbh.getAppropriatePrecision(columnPrecision, typeName); 183 columnScale=dbh.getAppropriateScale(columnScale); 184 typeInfo.setOptionalSizeProperty(dbh.isDataTypeOptionalSizeSupported(typeInfo)); 185 typeInfo.setColumnSize(rs.getInt("COLUMN_SIZE")); 186 typeInfo.setColumnScale(columnScale); 187 dbh.setTypeInfo(typeInfo, rs); 188 //colInfoMap.put(rs.getString("COLUMN_NAME").trim(),typeInfo.getTypeDeclaration(rs.getInt("COLUMN_SIZE"))); 189 colInfoList.add(new ColumnsInfo(rs.getString("COLUMN_NAME").trim(), typeInfo.getTypeDeclaration(columnPrecision))); 190 } 191 while (rs.next()); 192 } 193 finally { 194 if(rs != null) 195 rs.close(); 196 } 197 198 199 return colInfoList; 200 } 201 202 /*public HashMap getColumnDataTypeInfo(AbstractDataBaseHandler dbh,String schemaName, String tableName) throws RepException,SQLException { 203 HashMap colInfoMap = new HashMap(); 204 ResultSet rs = dbmd.getColumns(null,schemaName,tableName,"%"); 205 if(rs == null || !rs.next()) 206 throw new RepException("Internal Error",null); 207 int i = 1; 208 do { 209 TypeInfo typeInfo = new TypeInfo( 210 dbh.updateDataType(rs.getString("TYPE_NAME")) ,rs.getInt("DATA_TYPE")); 211 typeInfo.setOptionalSizeProperty(dbh.isDataTypeOptionalSizeSupported(typeInfo)); 212 //colInfoMap.put(rs.getString("COLUMN_NAME").trim(),typeInfo.getTypeDeclaration(rs.getInt("COLUMN_SIZE"))); 213 colInfoMap.put(new Integer(i++), new ColumnsInfo( rs.getString("COLUMN_NAME").trim(),typeInfo.getTypeDeclaration(rs.getInt("COLUMN_SIZE")) )); 214 // System.out.println(" VALUE " + rs.getString("COLUMN_NAME")); 215 }while(rs.next()); 216 return colInfoMap; 217 }*/ 218 219 public HashMap getColumnsInfo(String schemaName, String tableName) throws RepException, SQLException 220 { 221 HashMap colInfoMap = new HashMap(); 222 ResultSet rs = dbmd.getColumns(null, schemaName, tableName, "%"); 223 if (rs == null || !rs.next()) 224 { 225 throw new RepException("Internal Error", null); 226 } 227 int i = 0; 228 try { 229 do { 230 //colInfoMap.put(rs.getString("COLUMN_NAME").trim(),""); 231 colInfoMap.put(new Integer(i++), new ColumnsInfo(rs.getString("COLUMN_NAME").trim(), null)); 232 } 233 while (rs.next()); 234 235 } 236 finally { 237 if(rs != null) 238 rs.close(); 239 } 240 241 return colInfoMap; 242 } 243 244 /*public String generateQueryForLocalShadowTable(AbstractDataBaseHandler dbh,String schemaName, String tableName,ArrayList ciList, int vendorType) throws SQLException { 245 StringBuffer sb = new StringBuffer(); 246 //System.out.println(" dbh " + dbh.getClass()); 247 ResultSet rs = dbmd.getColumns(null,schemaName,tableName,"%"); 248 if(rs!=null && rs.next()) { 249 do { 250 TypeInfo typeInfo = new TypeInfo(rs.getString("TYPE_NAME"), 251 rs.getInt("DATA_TYPE")); 252 int size = rs.getInt("COLUMN_SIZE"); 253 String columnName = rs.getString("COLUMN_NAME"); 254 typeInfo.setOptionalSizeProperty(dbh.isDataTypeOptionalSizeSupported(typeInfo)); 255 sb.append(columnName).append(" ").append(typeInfo.getTypeDeclaration(size)); 256 257 switch (vendorType) { 258 case Utility.DataBase_SqlServer: 259 ciList.add(new ColumnsInfo(columnName,typeInfo,size)); 260 break; 261 case Utility.DataBase_DaffodilDB: 262 case Utility.DataBase_Oracle: 263 ciList.add(columnName); 264 default: 265 } 266 sb.append(" , "); 267 }while(rs.next()); 268 } 269 //System.out.println(" sb " + sb.toString()); 270 return sb.toString(); 271 }*/ 272 273 /** 274 * Generating a query ---- 275 * [ columnName dataType[(size)] [default] [not null] , 276 * [ columnName dataType[(size)] [default] [not null] ,...]] 277 * @param dbh 278 * @param srcVendorType 279 * @param schemaName 280 * @param tableName 281 * @param tgtVendorType 282 * @return String 283 * @throws SQLException 284 * @throws RepException 285 */ 286 public String generateColumnsQueryForClientNode(AbstractDataBaseHandler dbh, 287 int srcVendorType, 288 String schemaName, 289 String tableName, 290 int tgtVendorType) throws 291 RepException 292 { 293 StringBuffer sb = new StringBuffer(); 294 AbstractDataBaseHandler remotedbh = Utility.getDatabaseHandler( 295 tgtVendorType); 296 AbstractDataBaseHandler optSizedbh = (srcVendorType == tgtVendorType) ? 297 dbh : remotedbh; 298 try 299 { 300 //ResultSet rs2 = dbmd.getColumns(null,schemaName, tableName,"%"); 301 //showResultSet(rs2); 302 ResultSet rs = dbmd.getColumns(null, schemaName, tableName, "%"); 303 if (rs == null || !rs.next()) 304 { 305 throw new RepException("REP033", 306 new Object[] 307 {schemaName + "." + tableName}); 308 } 309 do 310 { 311 String typeName = dbh.updateDataType(rs.getString("TYPE_NAME")); 312 TypeInfo typeInfo = new TypeInfo(typeName, rs.getInt("DATA_TYPE")); 313 int columnPrecision = rs.getInt("COLUMN_SIZE"); 314 String columnName = rs.getString("COLUMN_NAME"); 315 columnPrecision = optSizedbh.getAppropriatePrecision(columnPrecision, 316 typeName); 317 typeInfo.setColumnSize(rs.getInt("COLUMN_SIZE")); 318 int columnScale=rs.getInt("DECIMAL_DIGITS"); 319 columnScale=optSizedbh.getAppropriateScale(columnScale); 320 int columnScalePublisher=dbh.getAppropriateScale(columnScale); 321 if(columnScale > columnScalePublisher){ 322 typeInfo.setColumnScale(columnScalePublisher); 323 }else{ 324 typeInfo.setColumnScale(columnScale); 325 } 326 remotedbh.setTypeInfo(typeInfo, rs); 327 typeInfo.setOptionalSizeProperty(optSizedbh. 328 isDataTypeOptionalSizeSupported( 329 typeInfo)); 330 sb.append(columnName).append(" ").append(typeInfo.getTypeDeclaration( 331 columnPrecision)); 332 if (srcVendorType == tgtVendorType) 333 { 334 typeInfo.setTypeName(typeName); 335 //System.out.println(" type Name === " + typeName + " sql Type " + sqlType ) ; 336 String defaultValue = rs.getString("COLUMN_DEF"); //String => default value (may be <code>null</code>) 337 if(defaultValue != null && !defaultValue.equalsIgnoreCase("NULL")) 338 sb.append("DEFAULT").append(" ").append(defaultValue); 339 } 340 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. 341 if (nullable.equalsIgnoreCase("NO")) 342 { 343 if (notNullColumns == null) 344 { 345 notNullColumns = new ArrayList(); 346 } 347 notNullColumns.add(columnName); 348 } 349 //new ColumnsInfo(columnName,typeName,sqlType,columnSize/*,defaultValue,nullable*/); 350 sb.append(" , "); 351 } 352 while (rs.next()); 353 } 354 catch (RepException ex) 355 { 356 log.error(ex.getMessage(),ex); 357 throw ex; 358 } 359 catch (SQLException ex) 360 { 361 log.error(ex.getMessage(),ex); 362 throw new RepException("REP006", new Object[] 363 {ex.getMessage()}); 364 } 365 return sb.toString(); 366 } 367 368 /** 369 * Gets the sequence of different constraint along with datatype in the 370 * query formate. 371 * 372 * @param schemaName 373 * @param tableName 374 * @param primConsMap 375 * @return String 376 * @throws RepException 377 */ 378 379 public String getAppliedConstraints(String schemaName, String tableName, 380 TreeMap primConsMap) throws RepException 381 { 382 StringBuffer cons = new StringBuffer(); 383 appendPrimaryConstraints(schemaName, tableName, cons, primConsMap); 384 appendUniqueKeyConstraints(schemaName, tableName, cons); 385 // appendForeignConstraints(schemaName, tableName, cons, primConsMap); 386 appendCheckConstraints(cons); 387 //System.out.println(" String Returned ===== " + cons.toString()); 388 return cons.toString(); 389 } 390 391 public String getAppliedConstraintsForExistingTable(String schemaName, 392 String tableName, TreeMap primConsMap, AbstractDataBaseHandler dbh) throws 393 RepException 394 { 395 StringBuffer cons = new StringBuffer(); 396 appendPrimaryConstraints(schemaName, tableName, cons, primConsMap); 397 appendUniqueKeyConstraints(schemaName, tableName, cons); 398 // appendForeignConstraints(schemaName, tableName, cons, primConsMap); 399 addCheckConstraintForExistingTable(schemaName, tableName, primConsMap, dbh); 400 appendCheckConstraints(cons); 401 return cons.toString(); 402 } 403 404 public void addCheckConstraintForExistingTable(String schemaName, 405 String tableName, 406 TreeMap primConsMap, 407 AbstractDataBaseHandler dbh) throws 408 RepException 409 { 410 try 411 { 412 ResultSet rs = dbmd.getColumns(null, schemaName, tableName, "%"); 413 if (rs == null || !rs.next()) 414 { 415 log.debug("Resultset found " + rs + " OR Resultset found false"); 416 throw new RepException("REP033", 417 new Object[] 418 {schemaName + "." + tableName}); 419 } 420 try { 421 do { 422 String columnName = rs.getString("COLUMN_NAME"); 423 String nullable = rs.getString("IS_NULLABLE").trim(); 424 if (nullable.equalsIgnoreCase("NO")) 425 { 426 if (notNullColumns == null) 427 { 428 notNullColumns = new ArrayList(); 429 } 430 notNullColumns.add(columnName); 431 } 432 } 433 while (rs.next()); 434 } 435 finally { 436 if(rs != null) 437 rs.close(); 438 } 439 440 } 441 catch (RepException ex) 442 { 443 log.error(ex.getMessage(),ex); 444 throw ex; 445 } 446 catch (SQLException ex) 447 { 448 log.error(ex.getMessage(),ex); 449 throw new RepException("REP006", new Object[] 450 {ex.getMessage()}); 451 } 452 453 } 454 455 /** 456 * Gets the primary key sequence in the query format. 457 * 458 * @param schemaName 459 * @param tableName 460 * @param cols 461 * @param consTableMap 462 * @throws RepException 463 */ 464 465 private void appendPrimaryConstraints(String schemaName, String tableName, 466 StringBuffer cols, TreeMap consTableMap) throws 467 RepException 468 { 469 HashMap primcolmap = null; 470 try 471 { 472 ResultSet rs = dbmd.getPrimaryKeys(null, schemaName, tableName); 473 if (rs == null || !rs.next()) 474 { 475 log.debug("Resultset found " + rs + " OR Resultset found false"); 476 throw new RepException("REP034", 477 new Object[] 478 {schemaName + "." + tableName}); 479 } 480 primcolmap = new HashMap(); 481 try { 482 do { 483 primcolmap.put(new Integer(rs.getInt("KEY_SEQ")), rs.getString("COLUMN_NAME")); 484 } 485 while (rs.next()); 486 } 487 finally { 488 if(rs != null) 489 rs.close(); 490 } 491 492 } 493 catch (RepException ex) 494 { 495 log.error(ex.getMessage(),ex); 496 throw ex; 497 } 498 catch (SQLException ex) 499 { 500 log.error(ex.getMessage(),ex); 501 throw new RepException("REP006", new Object[] 502 {ex.getMessage()}); 503 } 504 Object[] indexes = primcolmap.keySet().toArray(); 505 Arrays.sort(indexes); 506 cols.append(" Primary Key ("); 507 StringBuffer temp = new StringBuffer(); 508 for (int i = 0; i < indexes.length; i++) 509 { 510 if (i != 0) 511 { 512 temp.append(","); 513 } 514 temp.append(primcolmap.get(indexes[i])); 515 } 516 cols.append(temp.toString()).append(" )"); 517 consTableMap.put(schemaName + "." + tableName, 518 schemaName + "." + tableName + "(" + temp.toString() + ")"); 519 } 520 521 // PKTABLE_CAT</B> String => primary key table catalog being imported (may be <code>null</code>) 522 // PKTABLE_SCHEM</B> String => primary key table schema being imported (may be <code>null</code>) 523 // PKTABLE_NAME</B> String => primary key table name being imported 524 // PKCOLUMN_NAME</B> String => primary key column name being imported 525 // FKTABLE_CAT</B> String => foreign key table catalog (may be <code>null</code>) 526 // FKTABLE_SCHEM</B> String => foreign key table schema (may be <code>null</code>) 527 // FKTABLE_NAME</B> String => foreign key table name 528 // FKCOLUMN_NAME</B> String => foreign key column name 529 // KEY_SEQ</B> short => sequence number within a foreign key 530 // UPDATE_RULE</B> short => 531 // DELETE_RULE</B> short => 532 // FK_NAME</B> String => foreign key name (may be <code>null</code>) 533 // PK_NAME</B> String => primary key name (may be <code>null</code>) 534 // DEFERRABILITY</B> short => can the evaluation of foreign key constraints be deferred until commit 535 536 537 /** 538 * Gets foreign key sequence in query format. 539 * 540 * @param schemaName 541 * @param tableName 542 * @param cols 543 * @param consTableMap 544 * @throws RepException 545 */ 546 547 private void appendForeignConstraints(String schemaName, String tableName, 548 StringBuffer cols, TreeMap consTableMap) throws 549 RepException 550 { 551 HashMap fk_Keys = new HashMap(); 552 HashMap fk_pk = new HashMap(); 553 try { 554 ResultSet rs = dbmd.getImportedKeys(null, schemaName, tableName); 555 if (rs == null || !rs.next()) 556 { 557 log.debug("Resultset found " + rs + " OR Resultset found false"); 558 return; 559 } 560 try { 561 do { 562 String pk_tableName = rs.getString("PKTABLE_SCHEM") + "." + rs.getString("PKTABLE_NAME"); 563 Object primObject = consTableMap.get(pk_tableName); 564 if (primObject != null) { 565 String fk_tableName = rs.getString("FKTABLE_SCHEM") + "." + rs.getString("FKTABLE_NAME"); 566 String mapKey = rs.getString("FK_NAME"); 567 Object ob = fk_Keys.get(mapKey); 568 if (ob == null) { 569 HashMap colsMap = new HashMap(); 570 colsMap.put(new Integer(rs.getInt("KEY_SEQ")), rs.getString("FKCOLUMN_NAME")); 571 fk_pk.put(mapKey, primObject); 572 fk_Keys.put(mapKey, colsMap); 573 } 574 else { 575 ( (HashMap) ob).put(new Integer(rs.getInt("KEY_SEQ")), rs.getString("FKCOLUMN_NAME")); 576 } 577 } 578 } 579 while (rs.next()); 580 } 581 finally { 582 if(rs != null) 583 rs.close(); 584 } 585 586 } 587 catch (SQLException ex) 588 { 589 log.error(ex.getMessage(),ex); 590 throw new RepException("REP006", new Object[] 591 {ex.getMessage()}); 592 } 593 Object[] fkeys = fk_Keys.keySet().toArray(); 594 for (int i = 0; i < fkeys.length; i++) 595 { 596 HashMap map = (HashMap) fk_Keys.get(fkeys[i]); 597 Object[] indexes = map.keySet().toArray(); 598 Arrays.sort(indexes); 599 cols.append(" , Foreign Key ("); 600 for (int j = 0; j < indexes.length; j++) 601 { 602 if (j != 0) 603 { 604 cols.append(","); 605 } 606 cols.append(map.get(indexes[j])); 607 } 608 cols.append(" ) References " + fk_pk.get(fkeys[i])); 609 } 610 } 611 612 private void appendCheckConstraints(StringBuffer sb) 613 { 614 for (int i = 0, length = notNullColumns.size(); i < length; i++) 615 { 616 sb.append(" , Check ( ").append(notNullColumns.get(i)).append( 617 " is not null ) "); 618 } 619 notNullColumns = null; 620 } 621 622 public void setPrimaryColumns(RepTable repTable, String schemaName, String tableName) throws RepException, SQLException { 623 //ResultSet rs2 = dbmd.getPrimaryKeys(null,schemaName,tableName.toLowerCase()); 624 //Util.showResultSet(rs2); 625 HashMap primcolmap = new HashMap(); 626 //ResultSet rs = dbmd.getPrimaryKeys(null,schemaName,tableName.toLowerCase()); // SQL Server / Daffodil DB 627 ResultSet rs = dbmd.getPrimaryKeys(null, schemaName, tableName); // Oracle 628 if (rs == null || !rs.next()) 629 { 630 log.debug("Resultset found " + rs + " OR Resultset found false"); 631 throw new RepException("REP034", new Object[] 632 {tableName}); 633 } 634 635 try { 636 do { 637 primcolmap.put(new Integer(rs.getInt("KEY_SEQ")), rs.getString("COLUMN_NAME")); 638 } 639 while (rs.next()); 640 641 } 642 finally { 643 if(rs != null) 644 rs.close(); 645 } 646 647 String[] primColumns = new String[primcolmap.size()]; 648 Object[] indexes = primcolmap.keySet().toArray(); 649 Arrays.sort(indexes); 650 for (int i = 0; i < indexes.length; i++) { 651 primColumns[i] = (String) primcolmap.get(indexes[i]); 652 } 653 repTable.setPrimaryColumns(primColumns); 654 } 655 656 /* TODO On it When cyclic work is to be done */ 657 public void setForeignKeyColumns(RepTable repTable, String schemaName, String tableName) throws RepException, SQLException { 658 //System.out.println("tableName for foreignKey cols ::" + tableName); 659 ArrayList foreignKeyColumnsList = new ArrayList(); 660 // Firebird database does not have schema. It give the null pointer exception for "schemaName.toUpperCase()" 661 // ResultSet rs = dbmd.getImportedKeys(null, schemaName.toUpperCase(), tableName.toUpperCase()); 662 ResultSet rs = dbmd.getImportedKeys(null, schemaName, tableName); 663 if (rs == null || !rs.next()) { 664 //System.out.println(" NO FOREIGN KEYS FOUND FOR Table ::" + tableName); 665 return; 666 } 667 try{ do { 668 String fkCol=rs.getString("FKCOLUMN_NAME"); 669 //System.out.println(" FK_COL :: " + fkCol); 670 if(!foreignKeyColumnsList.contains(fkCol)) 671 foreignKeyColumnsList.add(fkCol); 672 } 673 while (rs.next()); 674 }finally{ 675 rs.close(); 676 } 677 //System.out.println("FK Lists " + foreignKeyColumnsList); 678 if(foreignKeyColumnsList.size() > 0) 679 repTable.setForeignKeyCols((String[])foreignKeyColumnsList.toArray(new String[0])); 680 } 681 682 public String getExistingTableQuery(AbstractDataBaseHandler dbh, SchemaQualifiedName sname, int pubVendorType) throws RepException, SQLException { 683 StringBuffer sb = new StringBuffer(); 684 String table = sname.getTableName(); 685 String schema = sname.getSchemaName(); 686 sb.append(" Create Table ").append(sname.toString()).append(" ( "); 687 ResultSet rs = dbmd.getColumns(null, schema.toLowerCase(), table.toLowerCase(), "%"); 688 //ResultSet rs = dbmd.getColumns(null,schema, table,"%"); 689 if (rs == null || !rs.next()) 690 { 691 log.debug("Resultset found " + rs + " OR Resultset found false"); 692 throw new RepException("REP033", new Object[] 693 {sname.toString()}); 694 } 695 do { 696 String typeName = dbh.updateDataType(rs.getString("TYPE_NAME")); 697 TypeInfo typeInfo = new TypeInfo(typeName, rs.getInt("DATA_TYPE")); 698 int size = rs.getInt("COLUMN_SIZE"); 699 String columnName = rs.getString("COLUMN_NAME"); 700 typeInfo.setColumnSize(rs.getInt("COLUMN_SIZE")); 701 dbh.setTypeInfo(typeInfo, rs); 702 typeInfo.setOptionalSizeProperty(dbh.isDataTypeOptionalSizeSupported(typeInfo)); 703 sb.append(columnName).append(" ").append(typeInfo.getTypeDeclaration(size)); 704 sb.append(" , "); 705 } 706 while (rs.next()); 707 // appendPrimaryConstraints(schema,table,sb,new HashMap()); 708 sb.append(" " + getAppliedConstraintsForExistingTable(schema, table, new TreeMap(String.CASE_INSENSITIVE_ORDER), dbh) + " ) "); 709 return sb.toString(); 710 } 711 712 /** 713 * Return the hierarchy of tables based on parent child relationship. 714 * Parent child relationship depend on Primary key and Foreign Key 715 * Relationship.It first add the parent table in array and after that 716 * child tables. 717 * @param tableNames String[] 718 * @throws RepException 719 * @return String[] 720 */ 721 abstract protected String[] getTablesHierarchy(String[] tableNames) throws RepException; 722 723 /** 724 * Checks the given table sequence, whetehr the tables are in the sequence 725 * of PK->FK or not. 726 * 727 * @param schemaName 728 * @param tableName 729 * @param tableList 730 * @throws RepException 731 */ 732 733 abstract protected ArrayList getTableSequenceRegardingForeignKey(String schemaName, String tableName, ArrayList tableList) throws RepException; 734 735 /** 736 * This method check that the parent table of given 737 * table is included or not. If parent table not included 738 * in list then it give the RepException because further 739 * it create the problem in synchronization. 740 * @param givenListOfTables ArrayList 741 * @param schemaName String 742 * @param tableName String 743 * @throws RepException 744 */ 745 abstract protected void checkParentTablesIncludedInList(ArrayList givenListOfTables, String schemaName, String tableName) throws RepException; 746 747 public String getCatalogName(String identifier) { 748 int catalog_index = identifier.indexOf('.'); 749 if (catalog_index != -1) { 750 return identifier.substring(0, catalog_index).toUpperCase(); 751 } 752 else { 753 return null; 754 } 755 } 756 757 public String getSchemaName(String identifier) { 758 int table_name_index = identifier.lastIndexOf('.'); 759 if (table_name_index != -1) { 760 identifier = identifier.substring(0, table_name_index); 761 int catalog_index = identifier.indexOf('.'); 762 if (catalog_index != -1) { 763 return identifier.substring(catalog_index + 1).toUpperCase(); 764 } 765 else { 766 identifier = identifier.replaceAll("\"", ""); 767 return identifier.toUpperCase(); 768 } 769 } 770 else { 771 return null; 772 } 773 774 } 775 776 /** 777 * Implemented to handle the casesensitveness of postgreSQL 778 * for table and and Schema Name.Method is overrided in 779 * pgMetaDataInfo. 780 * @param schematable String 781 * @return String 782 */ 783 public String getTableName(String schematable) { 784 String table = null; 785 int index = schematable.indexOf('.'); 786 if (index == -1) { 787 if (schematable.lastIndexOf("\"") == -1) { 788 table = schematable.substring(0, schematable.length()); 789 //return table.toUpperCase(); 790 // bjt - remove toUpperCase(); 791 return table; 792 } 793 else { 794 table = schematable.substring(1, schematable.length() - 1); 795 table = table.replaceAll("\"", ""); 796 //return table.toUpperCase(); 797 // bjt - remove toUpperCase(); 798 return table; 799 } 800 } 801 else { 802 if (schematable.lastIndexOf("\"") == -1) { 803 table = schematable.substring(index + 1); 804 table = table.replaceAll("\"", ""); 805 //return table.toUpperCase(); 806 // bjt - remove toUpperCase(); 807 return table; 808 } 809 else { 810 811 table = schematable.substring(index + 2); 812 table = table.replaceAll("\"", ""); 813 //return table.toUpperCase(); 814 // bjt - remove toUpperCase(); 815 return table; 816 } 817 } 818 } 819 820 /** 821 * Only for debugging purpose 822 * @param rs ResultSet 823 * @throws SQLException 824 */ 825 826 827 private static void showResultSet(ResultSet rs) throws SQLException { 828 ResultSetMetaData metaData = rs.getMetaData(); 829 int columnCount = metaData.getColumnCount(); 830 Object[] displayColumn = new Object[columnCount]; 831 for (int i = 1; i <= columnCount; i++) { 832 displayColumn[i - 1] = metaData.getColumnName(i); 833 // System.out.println( Arrays.asList(displayColumn) ); 834 } 835 while (rs.next()) { 836 Object[] columnValues = new Object[columnCount]; 837 for (int i = 1; i <= columnCount; i++) { 838 columnValues[i - 1] = rs.getObject(i); 839 // System.out.println(Arrays.asList(columnValues) ); 840 } 841 } 842 } 843 844 845 846 847 public Map getImportedTablesInfo(SchemaQualifiedName[] schemaQualifiedTableNames,DirectedGraph graph,String[] removeCycleTableNames) throws RepException{ 848 List schemaQualifiedNamesList = Arrays.asList(schemaQualifiedTableNames); 849 // System.out.println("sent list :"+ schemaQualifiedNamesList); 850 851 Map importedTablesMap = getImportedTables(schemaQualifiedNamesList,graph,removeCycleTableNames); 852 // System.out.println("importedTablesMap.entryset()###"+importedTablesMap.entrySet().toString()); 853 Set importedTablesSet = importedTablesMap.entrySet(); 854 // System.out.println("importedTablesSet :::"+importedTablesSet.toString()); 855 Map importedTablesInfoMap = new HashMap(); 856 int count = 0; 857 for (Iterator iter = importedTablesSet.iterator(); iter.hasNext(); count++) { 858 Map.Entry entry = (Map.Entry)iter.next(); 859 SchemaQualifiedName keyTable=(SchemaQualifiedName)entry.getKey(); 860 ArrayList superTablesList = (ArrayList)entry.getValue(); 861 // System.out.println("keyTable entry.getKey():::"+keyTable); 862 // System.out.println("superTablesList entry.getValue():::"+superTablesList); 863 boolean cyclic = false; 864 ArrayList superTablesHierarcyList = new ArrayList(); 865 ArrayList currentTraversalList = new ArrayList(); 866 if(superTablesList!= null){ 867 superTablesHierarcyList.add(keyTable); 868 currentTraversalList.add(keyTable); 869 // System.out.println( count +". TABLE ::: ["+keyTable+"]"); 870 // System.out.println("superTablesList.size() "+superTablesList.size()); 871 for (int i = 0, size = superTablesList.size(); i < size; i++) { 872 SchemaQualifiedName parentTable = (SchemaQualifiedName) superTablesList.get(i); 873 // System.out.println("parentTable :::"+parentTable); 874 superTablesHierarcyList.add(0,parentTable); 875 currentTraversalList.add(0,parentTable); 876 boolean cyclic1 = isCyclicRelationship(parentTable,superTablesHierarcyList,importedTablesMap,currentTraversalList); 877 if (!cyclic) 878 cyclic = cyclic1; 879 int indexInCurrentTraversalList = currentTraversalList.indexOf(parentTable) ; 880 // System.out.println(" List before removing :: " + currentTraversalList); 881 for (int k = 0; k <= indexInCurrentTraversalList; k++) { 882 currentTraversalList.remove(0); 883 } 884 // System.out.println( indexInCurrentTraversalList+" List after removing :: " + currentTraversalList); 885 } 886 ImportedTablesInfo importedTablesInfo = new ImportedTablesInfo(); 887 superTablesHierarcyList.remove(keyTable); 888 importedTablesInfo.setListOfAllAscendents(superTablesHierarcyList); 889 importedTablesInfo.setListOfDirectAscendents(superTablesList); 890 importedTablesInfo.setIsCyclic(cyclic); 891 importedTablesInfoMap.put(keyTable,importedTablesInfo); 892 } 893 // System.out.println(keyTable + "ISCYCLIC ::" + cyclic); 894 // System.out.println(keyTable+" LIST ::" + superTablesHierarcyList + "ISCYCLIC ::" + cyclic); 895 } 896 return importedTablesInfoMap; 897 } 898 /** 899 * THIS METHOD PUT ALL THE TABLES(schemaQualifiedName) AND THEIR PARENT TABLES(innerList) IN mapForImportedTables 900 * USING mapForImportedTables.put(schemaQualifiedName,innerList); 901 * @param schemaQualifiedTableNames List 902 * @param graph JbDirectedGraph 903 * @throws RepException 904 * @return HashMap 905 */ 906 private HashMap getImportedTables(List schemaQualifiedTableNames,DirectedGraph graph,String[] removeCycleTableNames)throws RepException{ 907 HashMap mapForImportedTables = new HashMap(); 908 for (int i = 0,size = schemaQualifiedTableNames.size(); i < size; i++) { 909 SchemaQualifiedName schemaQualifiedName = (SchemaQualifiedName)schemaQualifiedTableNames.get(i); 910 ArrayList innerList=getImportedTables(schemaQualifiedName,schemaQualifiedTableNames,graph,removeCycleTableNames); 911 mapForImportedTables.put(schemaQualifiedName,innerList); 912 } 913 // System.out.println("mapForImportedTables "+mapForImportedTables); 914 return mapForImportedTables; 915 } 916 917 // private ArrayList getListForTable(String tableName)throws Exception{ 918 // if(mapForAllTables.size()==0){ 919 // mapForAllTables = getMapForAllTables(); 920 // } 921 // 922 // return (ArrayList)mapForAllTables.get(tableName); 923 // } 924 925 926 private boolean isCyclicRelationship(SchemaQualifiedName parentTable,ArrayList superTablesHierarcyList,Map importedTablesMap,ArrayList currentTraversalList) throws RepException{ 927 ArrayList importedTablesList = (ArrayList)importedTablesMap.get(parentTable); 928 if(importedTablesList != null){ 929 // System.out.println("parentTable ::" + parentTable + " imported TAbles :: " + importedTablesList); 930 boolean cyclic = false; 931 for (int i =0,size = importedTablesList.size(); i < size ;i++) { 932 SchemaQualifiedName nextLevelParent = (SchemaQualifiedName) importedTablesList.get(i); 933 // System.out.println("calling to match again: mainTable, innerTable[" + parentTable + "], tableName[ " + nextLevelParent); 934 Object initiator = currentTraversalList.get(currentTraversalList.size()-1); 935 //System.out.println("initiator:::::::::"+initiator); 936 if (nextLevelParent.equals(initiator) ){ 937 //System.out.println("#################################### Graph is cyclic "); 938 // System.out.println("\t table in cycle "+ nextLevelParent +" CYCLIC FOR ::" + currentTraversalList); 939 return true; 940 } 941 if(currentTraversalList.contains(parentTable)){ 942 // System.out.println("currentTraversalList.contains(parentTable)"+currentTraversalList.contains(parentTable)); 943 continue; 944 } 945 if(!superTablesHierarcyList.contains(nextLevelParent)) 946 superTablesHierarcyList.add(0,nextLevelParent); 947 currentTraversalList.add(0,nextLevelParent); 948 boolean cyclic1 = isCyclicRelationship(nextLevelParent, superTablesHierarcyList, importedTablesMap,currentTraversalList); 949 // System.out.println("cyclic1::: "+cyclic1); 950 int indexInCurrentTraversalList = currentTraversalList.indexOf(nextLevelParent); 951 // System.out.println(" List before removing :: " + currentTraversalList); 952 for (int k = 0; k <= indexInCurrentTraversalList; k++) { 953 currentTraversalList.remove(0); 954 } 955 // System.out.println(indexInCurrentTraversalList +" List after removng :: "+ currentTraversalList); 956 // System.out.println("nextLevelParent :: " + nextLevelParent +" cyclic1::" + cyclic1 ); 957 if(!cyclic) 958 cyclic = cyclic1; 959 } 960 return cyclic; 961 } 962 return false; 963 } 964 965 abstract public ArrayList getChildTables(String parentTable)throws RepException ; 966 967 abstract public Object[] getImportedColsOfChildTable(String parentTable,String childTable)throws RepException ; 968 969 970 //add Edge to the graph 971 abstract public ArrayList getImportedTables(SchemaQualifiedName 972 schemaQualifiedName, 973 List passedSchemaQualifiedNamesList, 974 DirectedGraph graph, 975 String[] removeCycleTableNames0) throws 976 RepException; 977 //create alter query for table structures 978 abstract public ArrayList getForeignKeyConstraints(String schemaName, 979 String tableName) throws RepException; 980 abstract public void appendUniqueKeyConstraints(String schemaName, String tableName, 981 StringBuffer cols) throws RepException; 982 983 abstract public ArrayList getExportedTableCols(SchemaQualifiedName repTableQualifiedtableName) throws RepException; 984 985 public ArrayList getExportedTableColsList(SchemaQualifiedName repTableQualifiedtableName) throws RepException{ 986 return getExportedTableCols(repTableQualifiedtableName); 987 } 988 989 abstract protected void checkChildTableIncludedInDropTableList(ArrayList pubRepTableList,String[] dropTableList) throws RepException ; 990 991 abstract public void setAllColumns(RepTable repTable, String schemaName, String tableName)throws RepException, SQLException ; 992 993 994 }
Sponsors
