001/* 002// This software is subject to the terms of the Eclipse Public License v1.0 003// Agreement, available at the following URL: 004// http://www.eclipse.org/legal/epl-v10.html. 005// You must accept the terms of that agreement to use this software. 006// 007// Copyright (C) 2006-2011 Pentaho and others 008// Copyright (C) 2006-2007 Cincom Systems, Inc. 009// Copyright (C) 2006-2007 JasperSoft 010// All Rights Reserved. 011*/ 012package mondrian.gui; 013 014import org.apache.log4j.Logger; 015 016import java.sql.*; 017import java.util.*; 018 019/** 020 */ 021public class JdbcMetaData { 022 023 private static final Logger LOGGER = Logger.getLogger(JdbcMetaData.class); 024 025 // E.g. "org.postgresql.Driver" 026 String jdbcDriverClassName = null; 027 028 // E.g. "jdbc:postgresql://localhost:5432/hello?user=postgres&password=post" 029 String jdbcUsername = null; 030 031 String jdbcConnectionUrl = null; 032 String jdbcPassword = null; 033 String jdbcSchema = null; 034 boolean requireSchema = false; 035 036 Connection conn = null; 037 DatabaseMetaData md = null; 038 039 Workbench workbench; 040 041 public static final String LEVEL_SEPARATOR = "->"; 042 043 private String errMsg = null; 044 private Database db = new Database(); 045 046 public JdbcMetaData( 047 Workbench wb, 048 String jdbcDriverClassName, 049 String jdbcConnectionUrl, 050 String jdbcUsername, 051 String jdbcPassword, 052 String jdbcSchema, 053 boolean requireSchema) 054 { 055 this.workbench = wb; 056 this.jdbcConnectionUrl = jdbcConnectionUrl; 057 this.jdbcDriverClassName = jdbcDriverClassName; 058 this.jdbcUsername = jdbcUsername; 059 this.jdbcPassword = jdbcPassword; 060 this.jdbcSchema = jdbcSchema; 061 this.requireSchema = requireSchema; 062 063 if (initConnection() == null) { 064 setAllSchemas(); 065 closeConnection(); 066 } 067 } 068 069 public boolean getRequireSchema() { 070 return requireSchema; 071 } 072 073 /** 074 * @return the workbench i18n converter 075 */ 076 public I18n getResourceConverter() { 077 return workbench.getResourceConverter(); 078 } 079 080 /** 081 * Tests database connection. Called from Preferences dialog button test 082 * connection. 083 */ 084 public JdbcMetaData( 085 String jdbcDriverClassName, 086 String jdbcConnectionUrl, 087 String jdbcUsername, 088 String jdbcPassword) 089 { 090 this.jdbcConnectionUrl = jdbcConnectionUrl; 091 this.jdbcDriverClassName = jdbcDriverClassName; 092 this.jdbcUsername = jdbcUsername; 093 this.jdbcPassword = jdbcPassword; 094 095 if (initConnection() == null) { 096 closeConnection(); 097 } 098 } 099 100 /* Creates a database connection and initializes the meta data details */ 101 public String initConnection() { 102 LOGGER.debug("JdbcMetaData: initConnection"); 103 104 try { 105 if (jdbcDriverClassName == null 106 || jdbcDriverClassName.trim().length() == 0 107 || jdbcConnectionUrl == null 108 || jdbcConnectionUrl.trim().length() == 0) 109 { 110 errMsg = getResourceConverter().getFormattedString( 111 "jdbcMetaData.blank.exception", 112 "Driver={0}\nConnection URL={1}\nUse Preferences to set Database Connection parameters first and then open a Schema", 113 jdbcDriverClassName, 114 jdbcConnectionUrl); 115 return errMsg; 116 } 117 118 Class.forName(jdbcDriverClassName); 119 120 if (jdbcUsername != null && jdbcUsername.length() > 0) { 121 conn = DriverManager.getConnection( 122 jdbcConnectionUrl, jdbcUsername, jdbcPassword); 123 } else { 124 conn = DriverManager.getConnection(jdbcConnectionUrl); 125 } 126 127 LOGGER.debug("JDBC connection OPEN"); 128 md = conn.getMetaData(); 129 130 db.productName = md.getDatabaseProductName(); 131 db.productVersion = md.getDatabaseProductVersion(); 132 db.catalogName = conn.getCatalog(); 133 134 LOGGER.debug("Catalog name = " + db.catalogName); 135 LOGGER.debug("Database Product Name: " + db.productName); 136 LOGGER.debug("Database Product Version: " + db.productVersion); 137 LOGGER.debug("JdbcMetaData: initConnection - no error"); 138 return null; 139 } catch (Exception e) { 140 errMsg = 141 e.getClass().getSimpleName() + " : " + e.getLocalizedMessage(); 142 LOGGER.error("Database connection exception : " + errMsg, e); 143 return errMsg; 144 //e.printStackTrace(); 145 } 146 } 147 148 public void closeConnection() { 149 if (conn == null) { 150 return; 151 } 152 153 md = null; 154 try { 155 conn.close(); 156 LOGGER.debug("JDBC connection CLOSE"); 157 } catch (Exception e) { 158 LOGGER.error(e); 159 } 160 conn = null; 161 } 162 163 /** 164 * Check to see if the schemaName is in the list of allowed jdbc schemas 165 * 166 * @param schemaName the name of the schmea 167 * @return true if found, or if jdbcSchema is null 168 */ 169 private boolean inJdbcSchemas(String schemaName) { 170 if (jdbcSchema == null || jdbcSchema.trim().length() == 0) { 171 return true; 172 } 173 174 String schemas[] = jdbcSchema.split("[,;]"); 175 for (String schema : schemas) { 176 if (schema.trim().equalsIgnoreCase(schemaName)) { 177 return true; 178 } 179 } 180 181 return false; 182 } 183 184 /* list all schemas in the currently connected database */ 185 public List<String> listAllSchemas() { 186 LOGGER.debug("JdbcMetaData: listAllSchemas"); 187 188 if (initConnection() != null) { 189 return null; 190 } 191 192 List<String> schemaNames = new ArrayList<String>(); 193 ResultSet rs = null; 194 try { 195 rs = md.getSchemas(); 196 197 while (rs.next()) { 198 String schemaName = rs.getString("TABLE_SCHEM"); 199 schemaNames.add(schemaName); 200 } 201 } catch (Exception e) { 202 LOGGER.debug( 203 "Exception : Database does not support schemas." + e 204 .getMessage()); 205 return null; 206 } finally { 207 try { 208 rs.close(); 209 closeConnection(); 210 } catch (Exception e) { 211 // ignore 212 } 213 } 214 215 216 return schemaNames; 217 } 218 219 /* set all schemas in the currently connected database */ 220 private void setAllSchemas() { 221 LOGGER.debug("JdbcMetaData: setAllSchemas"); 222 223 ResultSet rs = null; 224 boolean gotSchema = false; 225 try { 226 rs = md.getSchemas(); 227 while (rs.next()) { 228 String schemaName = rs.getString("TABLE_SCHEM"); 229 if (inJdbcSchemas(schemaName)) { 230 DbSchema dbs = new DbSchema(); 231 dbs.name = schemaName; 232 LOGGER.debug("JdbcMetaData: setAllTables - " + dbs.name); 233 setAllTables(dbs); 234 db.addDbSchema(dbs); 235 gotSchema = true; 236 } 237 } 238 } catch (Exception e) { 239 LOGGER.debug( 240 "Exception : Database does not support schemas." + e 241 .getMessage()); 242 } finally { 243 try { 244 rs.close(); 245 } catch (Exception e) { 246 // ignore 247 } 248 } 249 250 if (!gotSchema) { 251 LOGGER.debug( 252 "JdbcMetaData: setAllSchemas - tables with no schema name"); 253 DbSchema dbs = new DbSchema(); 254 dbs.name = null; //tables with no schema name 255 setAllTables(dbs); 256 db.addDbSchema(dbs); 257 } 258 } 259 260 /* set all tables in the currently connected database */ 261 private void setAllTables(DbSchema dbs) { 262 LOGGER.debug("JdbcMetaData: Loading schema: '" + dbs.name + "'"); 263 ResultSet rs = null; 264 try { 265 // Tables and views can be used 266 try { 267 rs = md.getTables( 268 null, dbs.name, null, new String[]{"TABLE", "VIEW"}); 269 } catch (Exception e) { 270 // this is a workaround for databases that throw an exception 271 // when views are requested. 272 rs = md.getTables(null, dbs.name, null, new String[]{"TABLE"}); 273 } 274 while (rs.next()) { 275 // Oracle 10g Driver returns bogus BIN$ tables that cause 276 // exceptions 277 String tbname = rs.getString("TABLE_NAME"); 278 if (!tbname.matches("(?!BIN\\$).+")) { 279 continue; 280 } 281 282 DbTable dbt; 283 284 /* Note: Imported keys are foreign keys which are primary keys 285 * of in some other tables; Exported keys are primary keys which 286 * are referenced as foreign keys in other tables. 287 */ 288 ResultSet rs_fks = md.getImportedKeys(null, dbs.name, tbname); 289 try { 290 if (rs_fks.next()) { 291 dbt = new FactTable(); 292 do { 293 ((FactTable) dbt).addFks( 294 rs_fks.getString("FKCOLUMN_NAME"), 295 rs_fks.getString("pktable_name")); 296 } while (rs_fks.next()); 297 } else { 298 dbt = new DbTable(); 299 } 300 } finally { 301 try { 302 rs_fks.close(); 303 } catch (Exception e) { 304 // ignore 305 } 306 } 307 dbt.schemaName = dbs.name; 308 dbt.name = tbname; 309 setPKey(dbt); 310 // Lazy loading 311 // setColumns(dbt); 312 dbs.addDbTable(dbt); 313 db.addDbTable(dbt); 314 } 315 } catch (Exception e) { 316 LOGGER.error("setAllTables", e); 317 } finally { 318 try { 319 rs.close(); 320 } catch (Exception e) { 321 // ignore 322 } 323 } 324 } 325 326 /** 327 * Gets the Primary key name for a given table name. 328 * This key may be a composite key made of multiple columns. 329 */ 330 private void setPKey(DbTable dbt) { 331 ResultSet rs = null; 332 try { 333 rs = md.getPrimaryKeys(null, dbt.schemaName, dbt.name); 334 if (rs.next()) { 335 // // a column may have been given a primary key name 336 //===dbt.pk = rs.getString("PK_NAME"); 337 // We need the column name which is primary key for the given 338 // table. 339 dbt.pk = rs.getString("column_name"); 340 } 341 } catch (Exception e) { 342 LOGGER.error("setPKey", e); 343 } finally { 344 try { 345 rs.close(); 346 } catch (Exception e) { 347 // ignore 348 } 349 } 350 } 351 352 private void setColumns(String schemaName, String tableName) { 353 LOGGER.debug( 354 "setColumns: <" + tableName + "> in schema <" + schemaName + ">"); 355 DbTable dbt = db.getTable(schemaName, tableName); 356 if (dbt == null) { 357 LOGGER.debug( 358 "No table with name: <" 359 + tableName 360 + "> in schema <" 361 + schemaName 362 + ">"); 363 return; 364 } 365 if (initConnection() != null) { 366 return; 367 } 368 try { 369 setColumns(dbt); 370 LOGGER.debug("got " + dbt.colsDataType.size() + " columns"); 371 } finally { 372 closeConnection(); 373 } 374 } 375 376 /** 377 * Gets all columns for a given table name. 378 * 379 * Assumes that the caller has acquired a connection using 380 * {@link #initConnection()}. 381 */ 382 private void setColumns(DbTable dbt) { 383 ResultSet rs = null; 384 try { 385 rs = md.getColumns(null, dbt.schemaName, dbt.name, null); 386 while (rs.next()) { 387 DbColumn col = new DbColumn(); 388 389 col.dataType = rs.getInt("DATA_TYPE"); 390 col.name = rs.getString("COLUMN_NAME"); 391 col.typeName = rs.getString("TYPE_NAME"); 392 col.columnSize = rs.getInt("COLUMN_SIZE"); 393 col.decimalDigits = rs.getInt("DECIMAL_DIGITS"); 394 395 dbt.addColsDataType(col); 396 } 397 } catch (Exception e) { 398 LOGGER.error("setColumns", e); 399 } finally { 400 try { 401 rs.close(); 402 } catch (Exception e) { 403 // ignore 404 } 405 } 406 } 407 408 //////////////////////////////////////////////////////////////////////////// 409 // The following functions provide an interface to JdbcMetaData class to 410 // retrieve the meta data details 411 412 public List<String> getAllSchemas() { 413 return db.getAllSchemas(); 414 } 415 416 /** 417 * Returns all tables in a given schema. 418 */ 419 public List<String> getAllTables(String schemaName) { 420 return db.getAllTables(schemaName); 421 } 422 423 /** 424 * Returns all tables in given schema minus the given table name. 425 */ 426 public List<String> getAllTables(String schemaName, String minusTable) { 427 if (minusTable == null) { 428 return getAllTables(schemaName); 429 } else { 430 List<String> allTablesMinusOne = new ArrayList<String>(); 431 for (String s : getAllTables(schemaName)) { 432 if (s.endsWith(minusTable)) { 433 // startsWith and endsWith cannot be compared with 434 // null argument, throws exception 435 if ((schemaName == null) || s.startsWith(schemaName)) { 436 continue; 437 } 438 } 439 allTablesMinusOne.add(s); 440 } 441 return allTablesMinusOne; 442 } 443 } 444 445 /* get all possible cases of fact tables in a schema */ 446 public List<String> getFactTables(String schemaName) { 447 return db.getFactTables(schemaName); 448 } 449 450 /** 451 * Gets all possible cases of dimension tables which are linked to given 452 * fact table by foreign keys. 453 */ 454 public List<String> getDimensionTables( 455 String schemaName, 456 String factTable) 457 { 458 List<String> dimeTables = new ArrayList<String>(); 459 if (factTable == null) { 460 return dimeTables; 461 } else { 462 return db.getDimensionTables(schemaName, factTable); 463 } 464 } 465 466 public boolean isTableExists(String schemaName, String tableName) { 467 if (tableName == null) { 468 return true; 469 } else { 470 return db.tableExists(schemaName, tableName); 471 } 472 } 473 474 public boolean isColExists( 475 String schemaName, String tableName, String colName) 476 { 477 if (tableName == null || colName == null) { 478 return true; 479 } else { 480 if (!db.hasColumns(schemaName, tableName)) { 481 setColumns(schemaName, tableName); 482 } 483 484 return db.colExists(schemaName, tableName, colName); 485 } 486 } 487 488 /* get all foreign keys in given fact table */ 489 public List<String> getFactTableFKs(String schemaName, String factTable) { 490 List<String> fks = new ArrayList<String>(); 491 if (factTable == null) { 492 return fks; 493 } else { 494 return db.getFactTableFKs(schemaName, factTable); 495 } 496 } 497 498 public String getTablePK(String schemaName, String tableName) { 499 if (tableName == null) { 500 return null; 501 } else { 502 return db.getTablePK(schemaName, tableName); 503 } 504 } 505 506 /** 507 * Gets all columns of given table in schema. 508 * column string is formatted. 509 */ 510 public List<String> getAllColumns(String schemaName, String tableName) { 511 List<String> allcols = new ArrayList<String>(); 512 513 if (tableName == null) { 514 List<String> allTables = getAllTables(schemaName); 515 516 for (int i = 0; i < allTables.size(); i++) { 517 String tab = allTables.get(i); 518 List<String> cols; 519 if (tab.indexOf(LEVEL_SEPARATOR) == -1) { 520 cols = getAllColumns(schemaName, tab); 521 } else { 522 String[] names = tab.split(LEVEL_SEPARATOR); 523 cols = getAllColumns(names[0], names[1]); 524 } 525 for (int j = 0; j < cols.size(); j++) { 526 String col = cols.get(j); 527 allcols.add(tab + LEVEL_SEPARATOR + col); 528 } 529 } 530 return allcols; 531 } else { 532 if (!db.hasColumns(schemaName, tableName)) { 533 setColumns(schemaName, tableName); 534 } 535 return db.getAllColumns(schemaName, tableName); 536 } 537 } 538 539 /** 540 * Returns all columns of given table in schema. 541 * Column string is formatted. 542 */ 543 public List<DbColumn> getAllDbColumns(String schemaName, String tableName) { 544 List<DbColumn> allcols = new ArrayList<DbColumn>(); 545 546 if (tableName == null) { 547 List<String> allTables = getAllTables(schemaName); 548 549 for (int i = 0; i < allTables.size(); i++) { 550 String tab = allTables.get(i); 551 List<DbColumn> cols; 552 if (tab.indexOf(LEVEL_SEPARATOR) == -1) { 553 cols = getAllDbColumns(schemaName, tab); 554 } else { 555 String[] names = tab.split(LEVEL_SEPARATOR); 556 cols = getAllDbColumns(names[0], names[1]); 557 } 558 allcols.addAll(cols); 559 } 560 return allcols; 561 } else { 562 if (!db.hasColumns(schemaName, tableName)) { 563 setColumns(schemaName, tableName); 564 } 565 return db.getAllDbColumns(schemaName, tableName); 566 } 567 } 568 569 // get column data type of given table and its col 570 public int getColumnDataType( 571 String schemaName, String tableName, String colName) 572 { 573 if (tableName == null || colName == null) { 574 return -1; 575 } else { 576 if (!db.hasColumns(schemaName, tableName)) { 577 setColumns(schemaName, tableName); 578 } 579 return db.getColumnDataType(schemaName, tableName, colName); 580 } 581 } 582 583 /** 584 * Gets column definition of given table and its col. 585 * 586 * @param schemaName Schema name 587 * @param tableName Table name 588 * @param colName Column name 589 * @return Column definition 590 */ 591 public DbColumn getColumnDefinition( 592 String schemaName, String tableName, String colName) 593 { 594 if (tableName == null || colName == null) { 595 return null; 596 } else { 597 if (!db.hasColumns(schemaName, tableName)) { 598 setColumns(schemaName, tableName); 599 } 600 return db.getColumnDefinition(schemaName, tableName, colName); 601 } 602 } 603 604 public String getDbCatalogName() { 605 return db.catalogName; 606 } 607 608 public String getDatabaseProductName() { 609 return db.productName; 610 } 611 612 public String getJdbcConnectionUrl() { 613 return jdbcConnectionUrl; 614 } 615 616 public String getErrMsg() { 617 return errMsg; 618 } 619 620 public static void main(String[] args) { 621 if (args.length < 2) { 622 throw new RuntimeException( 623 "need at least 2 args: driver class and jdbcUrl"); 624 } 625 626 String driverClass = args[0]; 627 String jdbcUrl = args[1]; 628 629 String username = null; 630 String password = null; 631 632 if (args.length > 2) { 633 if (args.length != 4) { 634 throw new RuntimeException( 635 "need 4 args: including user name and password"); 636 } 637 username = args[2]; 638 password = args[3]; 639 } 640 641 JdbcMetaData sb = new JdbcMetaData( 642 null, driverClass, jdbcUrl, username, password, "", false); 643 644 List<String> foundSchemas = sb.getAllSchemas(); 645 System.out.println("allSchemas = " + foundSchemas); 646 647 for (String schemaName : foundSchemas) { 648 List<String> foundTables = sb.getAllTables(schemaName); 649 650 if (foundTables != null && foundTables.size() > 0) { 651 System.out.println("schema = " + schemaName); 652 for (String tableName : foundTables) { 653 System.out.println("\t" + tableName); 654 655 List<String> foundColumns = sb.getAllColumns( 656 schemaName, tableName); 657 658 for (String columnName : foundColumns) { 659 System.out.println("\t\t" + columnName); 660 } 661 } 662 } 663 } 664 } 665 666 /** 667 * Database metadata. 668 */ 669 class Database { 670 String catalogName = ""; // database name. 671 String productName = "Unknown"; 672 String productVersion = ""; 673 674 // list of all schemas in database 675 Map<String, DbSchema> schemas = new TreeMap<String, DbSchema>(); 676 //ordered collection, allows duplicates and null 677 Map<String, TableTracker> tables = new TreeMap<String, TableTracker>(); 678 // list of all tables in all schemas in database 679 680 List<String> allSchemas; 681 682 private void addDbSchema(DbSchema dbs) { 683 schemas.put( 684 dbs.name != null 685 ? dbs.name 686 : "", dbs); 687 } 688 689 class TableTracker { 690 List<DbTable> namedTable = new ArrayList<DbTable>(); 691 692 public void add(DbTable table) { 693 namedTable.add(table); 694 } 695 696 public int count() { 697 return namedTable.size(); 698 } 699 } 700 701 private void addDbTable(DbTable dbs) { 702 TableTracker tracker = tables.get(dbs.name); 703 704 if (tracker == null) { 705 tracker = new TableTracker(); 706 tables.put(dbs.name, tracker); 707 } 708 tracker.add(dbs); 709 } 710 711 private boolean schemaNameEquals(String a, String b) { 712 return (a != null && a.equals(b)); 713 } 714 715 private DbSchema getSchema(String schemaName) { 716 return schemas.get( 717 schemaName != null 718 ? schemaName 719 : ""); 720 } 721 722 private List<String> getAllSchemas() { 723 if (allSchemas == null) { 724 allSchemas = new ArrayList<String>(); 725 726 allSchemas.addAll(schemas.keySet()); 727 } 728 return allSchemas; 729 } 730 731 private boolean tableExists(String sname, String tableName) { 732 return getTable(sname, tableName) != null; 733 } 734 735 private DbTable getTable(String sname, String tableName) { 736 if (sname == null || sname.equals("")) { 737 TableTracker t = tables.get(tableName); 738 if (t != null) { 739 return t.namedTable.get(0); 740 } else { 741 return null; 742 } 743 } else { 744 DbSchema s = schemas.get(sname); 745 746 if (s == null) { 747 return null; 748 } 749 750 return s.getTable(tableName); 751 } 752 } 753 754 private boolean hasColumns(String schemaName, String tableName) { 755 DbTable table = getTable(schemaName, tableName); 756 if (table != null) { 757 return table.hasColumns(); 758 } 759 return false; 760 } 761 762 private boolean colExists( 763 String sname, String tableName, String colName) 764 { 765 DbTable t = getTable(sname, tableName); 766 767 if (t == null) { 768 return false; 769 } 770 771 return t.getColumn(colName) != null; 772 } 773 774 private List<String> getAllTables(String sname) { 775 return getAllTables(sname, false); 776 } 777 778 private List<String> getFactTables(String sname) { 779 return getAllTables(sname, true); 780 } 781 782 private List<String> getAllTables(String sname, boolean factOnly) { 783 List<String> v = new ArrayList<String>(); 784 785 if (sname == null || sname.equals("")) { 786 // return a list of "schemaname -> table name" string objects 787 for (TableTracker tt : tables.values()) { 788 for (DbTable t : tt.namedTable) { 789 if (!factOnly || (factOnly && t instanceof FactTable)) { 790 if (t.schemaName == null) { 791 v.add(t.name); 792 } else { 793 v.add(t.schemaName + LEVEL_SEPARATOR + t.name); 794 } 795 } 796 } 797 } 798 } else { 799 // return a list of "tablename" string objects 800 DbSchema s = getSchema(sname); 801 802 if (s != null) { 803 for (DbTable t : s.tables.values()) { 804 if (!factOnly || (factOnly && t instanceof FactTable)) { 805 v.add(t.name); 806 } 807 } 808 } 809 } 810 return v; 811 } 812 813 /* get all foreign keys in given fact table */ 814 private List<String> getFactTableFKs(String sname, String factTable) { 815 List<String> f = new ArrayList<String>(); 816 817 if (sname == null || sname.equals("")) { 818 TableTracker tracker = tables.get(factTable); 819 820 if (tracker == null) { 821 return f; 822 } 823 824 // return a list of "schemaname -> table name -> fk col" string 825 // objects if schema is not given 826 boolean duplicate = tracker.count() > 1; 827 828 for (DbTable t : tracker.namedTable) { 829 if (t instanceof FactTable) { 830 if (duplicate) { 831 for (String fk : ((FactTable) t).fks.keySet()) { 832 if (t.schemaName == null) { 833 f.add(t.name + LEVEL_SEPARATOR + fk); 834 } else { 835 f.add( 836 t.schemaName 837 + LEVEL_SEPARATOR 838 + t.name 839 + LEVEL_SEPARATOR 840 + fk); 841 } 842 } 843 } else { 844 f.addAll(((FactTable) t).fks.keySet()); 845 } 846 } 847 } 848 } else { 849 DbSchema s = getSchema(sname); 850 851 if (s == null) { 852 return f; 853 } 854 855 DbTable t = s.getTable(factTable); 856 857 if (t == null) { 858 return f; 859 } 860 861 // return a list of "fk col name" string objects if schema is 862 // given 863 if (t instanceof FactTable && t.name.equals(factTable)) { 864 f.addAll(((FactTable) t).fks.keySet()); 865 } 866 } 867 return f; 868 } 869 870 private List<String> getDimensionTables( 871 String sname, String factTable) 872 { 873 List<String> f = new ArrayList<String>(); 874 875 if (sname == null || sname.equals("")) { 876 TableTracker tracker = tables.get(factTable); 877 878 if (tracker == null) { 879 return f; 880 } 881 882 // return a list of "schemaname -> table name -> fk col" string 883 // objects if schema is not given 884 boolean duplicate = tracker.count() > 1; 885 886 for (DbTable t : tracker.namedTable) { 887 if (t instanceof FactTable) { 888 if (duplicate) { 889 for (String fkt : ((FactTable) t).fks.values()) { 890 if (t.schemaName == null) { 891 f.add(t.name + LEVEL_SEPARATOR + fkt); 892 } else { 893 f.add( 894 t.schemaName 895 + LEVEL_SEPARATOR 896 + t.name 897 + LEVEL_SEPARATOR 898 + fkt); 899 } 900 } 901 } else { 902 f.addAll(((FactTable) t).fks.keySet()); 903 } 904 } 905 } 906 } else { 907 DbSchema s = getSchema(sname); 908 909 if (s == null) { 910 return f; 911 } 912 913 DbTable t = s.getTable(factTable); 914 915 if (t == null) { 916 return f; 917 } 918 919 // return a list of "fk col name" string objects if schema is 920 // given 921 if (t instanceof FactTable && t.name.equals(factTable)) { 922 f.addAll(((FactTable) t).fks.values()); 923 } 924 } 925 return f; 926 } 927 928 private String getTablePK(String sname, String tableName) { 929 if (sname == null || sname.equals("")) { 930 TableTracker tracker = tables.get(tableName); 931 932 if (tracker == null) { 933 return null; 934 } 935 936 // return a list of "schemaname -> table name -> 937 // dimension table name" string objects if schema is not given 938 return tracker.namedTable.get(0).pk; 939 } else { 940 DbTable t = getTable(sname, tableName); 941 942 if (t == null) { 943 return null; 944 } 945 946 return t.pk; 947 } 948 } 949 950 private List<String> getAllColumns(String sname, String tableName) { 951 List<String> f = new ArrayList<String>(); 952 953 if (sname == null || sname.equals("")) { 954 TableTracker tracker = tables.get(tableName); 955 956 if (tracker == null) { 957 return f; 958 } 959 960 // return a list of "schemaname -> table name -> cols" 961 // string objects if schema is not given 962 boolean duplicate = tracker.count() > 1; 963 964 for (DbTable t : tracker.namedTable) { 965 for (Map.Entry<String, DbColumn> c : t.colsDataType 966 .entrySet()) 967 { 968 StringBuffer sb = new StringBuffer(); 969 970 if (t.schemaName != null && !duplicate) { 971 sb.append(t.schemaName).append(LEVEL_SEPARATOR); 972 } 973 sb.append(t.name) 974 .append(LEVEL_SEPARATOR) 975 .append(c.getKey()) 976 .append(" - ") 977 .append(c.getValue().displayType()); 978 979 f.add(sb.toString()); 980 } 981 } 982 } else { 983 DbTable t = getTable(sname, tableName); 984 985 if (t == null) { 986 return f; 987 } 988 // return a list of "col name" string objects if schema is given 989 f.addAll(t.colsDataType.keySet()); 990 } 991 return f; 992 } 993 994 private List<DbColumn> getAllDbColumns(String sname, String tableName) { 995 List<DbColumn> f = new ArrayList<DbColumn>(); 996 997 if (sname == null || sname.equals("")) { 998 TableTracker tracker = tables.get(tableName); 999 1000 if (tracker == null) { 1001 return f; 1002 } 1003 1004 for (DbTable t : tracker.namedTable) { 1005 for (Map.Entry<String, DbColumn> c : t.colsDataType 1006 .entrySet()) 1007 { 1008 f.add(c.getValue()); 1009 } 1010 } 1011 } else { 1012 DbTable t = getTable(sname, tableName); 1013 1014 if (t == null) { 1015 return f; 1016 } 1017 1018 for (Map.Entry<String, DbColumn> c : t.colsDataType 1019 .entrySet()) 1020 { 1021 f.add(c.getValue()); 1022 } 1023 } 1024 return f; 1025 } 1026 1027 private int getColumnDataType( 1028 String sname, String tableName, String colName) 1029 { 1030 DbColumn result = getColumnDefinition(sname, tableName, colName); 1031 1032 if (result == null) { 1033 return -1; 1034 } 1035 1036 return result.dataType; 1037 } 1038 1039 private DbColumn getColumnDefinition( 1040 String sname, String tableName, String colName) 1041 { 1042 DbTable t = getTable(sname, tableName); 1043 1044 if (t == null) { 1045 return null; 1046 } 1047 return t.colsDataType.get(colName); 1048 } 1049 } 1050 1051 class DbSchema { 1052 String name; 1053 /** 1054 * ordered collection, allows duplicates and null 1055 */ 1056 final Map<String, DbTable> tables = new TreeMap<String, DbTable>(); 1057 1058 private DbTable getTable(String tableName) { 1059 return tables.get(tableName); 1060 } 1061 1062 private void addDbTable(DbTable dbt) { 1063 tables.put(dbt.name, dbt); 1064 } 1065 } 1066 1067 public class DbColumn { 1068 public String name; 1069 public int dataType; 1070 public String typeName; 1071 public int columnSize; 1072 public int decimalDigits; 1073 1074 public String displayType() { 1075 StringBuffer sb = new StringBuffer(); 1076 switch (dataType) { 1077 case Types.ARRAY: 1078 sb.append("ARRAY(" + columnSize + ")"); 1079 break; 1080 case Types.BIGINT: 1081 sb.append("BIGINT"); 1082 break; 1083 case Types.BINARY: 1084 sb.append("BINARY(" + columnSize + ")"); 1085 break; 1086 case Types.BLOB: 1087 sb.append("BLOB(" + columnSize + ")"); 1088 break; 1089 case Types.BIT: 1090 sb.append("BIT"); 1091 break; 1092 case Types.BOOLEAN: 1093 sb.append("BOOLEAN"); 1094 break; 1095 case Types.CHAR: 1096 sb.append("CHAR"); 1097 break; 1098 case Types.CLOB: 1099 sb.append("CLOB(" + columnSize + ")"); 1100 break; 1101 case Types.DATE: 1102 sb.append("DATE"); 1103 break; 1104 case Types.DECIMAL: 1105 sb.append("DECIMAL(" + columnSize + ", " + decimalDigits + ")"); 1106 break; 1107 case Types.DISTINCT: 1108 sb.append("DISTINCT"); 1109 break; 1110 case Types.DOUBLE: 1111 sb.append("DOUBLE(" + columnSize + ", " + decimalDigits + ")"); 1112 break; 1113 case Types.FLOAT: 1114 sb.append("FLOAT(" + columnSize + ", " + decimalDigits + ")"); 1115 break; 1116 case Types.INTEGER: 1117 sb.append("INTEGER(" + columnSize + ")"); 1118 break; 1119 case Types.JAVA_OBJECT: 1120 sb.append("JAVA_OBJECT(" + columnSize + ")"); 1121 break; 1122 /* 1123 * No Java 1.6 SQL types for now 1124 case Types.LONGNVARCHAR: 1125 sb.append("LONGNVARCHAR(" + columnSize + ")"); 1126 break; 1127 case Types.LONGVARBINARY: 1128 sb.append("LONGVARBINARY(" + columnSize + ")"); 1129 break; 1130 case Types.LONGVARCHAR: 1131 sb.append("LONGVARCHAR(" + columnSize + ")"); 1132 break; 1133 case Types.NCHAR: 1134 sb.append("NCHAR(" + columnSize + ")"); 1135 break; 1136 case Types.NCLOB: 1137 sb.append("NCLOB(" + columnSize + ")"); 1138 break; 1139 */ 1140 case Types.NULL: 1141 sb.append("NULL"); 1142 break; 1143 case Types.NUMERIC: 1144 sb.append("NUMERIC(" + columnSize + ", " + decimalDigits + ")"); 1145 break; 1146 /* 1147 * No Java 1.6 SQL types for now 1148 case Types.NVARCHAR: 1149 sb.append("NCLOB(" + columnSize + ")"); 1150 break; 1151 */ 1152 case Types.OTHER: 1153 sb.append("OTHER"); 1154 break; 1155 case Types.REAL: 1156 sb.append("REAL(" + columnSize + ", " + decimalDigits + ")"); 1157 break; 1158 case Types.REF: 1159 sb.append("REF"); 1160 break; 1161 /* 1162 * No Java 1.6 SQL types for now 1163 case Types.ROWID: 1164 sb.append("ROWID"); 1165 break; 1166 */ 1167 case Types.SMALLINT: 1168 sb.append("SMALLINT(" + columnSize + ")"); 1169 break; 1170 /* 1171 * No Java 1.6 SQL types for now 1172 case Types.SQLXML: 1173 sb.append("SQLXML(" + columnSize + ")"); 1174 break; 1175 */ 1176 case Types.STRUCT: 1177 sb.append("STRUCT"); 1178 break; 1179 case Types.TIME: 1180 sb.append("TIME"); 1181 break; 1182 case Types.TIMESTAMP: 1183 sb.append("TIMESTAMP"); 1184 break; 1185 case Types.TINYINT: 1186 sb.append("TINYINT(" + columnSize + ")"); 1187 break; 1188 case Types.VARBINARY: 1189 sb.append("VARBINARY(" + columnSize + ")"); 1190 break; 1191 case Types.VARCHAR: 1192 sb.append("VARCHAR(" + columnSize + ")"); 1193 break; 1194 } 1195 return sb.toString(); 1196 } 1197 } 1198 1199 class DbTable { 1200 String schemaName; 1201 String name; 1202 String pk; 1203 /** 1204 * sorted map key=column, value=data type of column 1205 */ 1206 final Map<String, DbColumn> colsDataType = 1207 new TreeMap<String, DbColumn>(); 1208 1209 private void addColsDataType(DbColumn columnDefinition) { 1210 colsDataType.put(columnDefinition.name, columnDefinition); 1211 } 1212 1213 private DbColumn getColumn(String cname) { 1214 return colsDataType.get(cname); 1215 } 1216 1217 private boolean hasColumns() { 1218 return colsDataType.size() > 0; 1219 } 1220 } 1221 1222 class FactTable extends DbTable { 1223 /** 1224 * Sorted map key = foreign key col, value=primary key table associated 1225 * with this fk. 1226 */ 1227 final Map<String, String> fks = new TreeMap<String, String>(); 1228 1229 private void addFks(String fk, String pkt) { 1230 fks.put(fk, pkt); 1231 } 1232 } 1233} 1234 1235// End JdbcMetaData.java