0001: //** Copyright Statement ***************************************************
0002: //The Salmon Open Framework for Internet Applications (SOFIA)
0003: // Copyright (C) 1999 - 2002, Salmon LLC
0004: //
0005: // This program is free software; you can redistribute it and/or
0006: // modify it under the terms of the GNU General Public License version 2
0007: // as published by the Free Software Foundation;
0008: //
0009: // This program is distributed in the hope that it will be useful,
0010: // but WITHOUT ANY WARRANTY; without even the implied warranty of
0011: // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
0012: // GNU General Public License for more details.
0013: //
0014: // You should have received a copy of the GNU General Public License
0015: // along with this program; if not, write to the Free Software
0016: // Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
0017: //
0018: // For more information please visit http://www.salmonllc.com
0019: //** End Copyright Statement ***************************************************
0020: package com.salmonllc.sql;
0021:
0022: /////////////////////////
0023: //$Archive: /SOFIA/SourceCode/com/salmonllc/sql/DataDictionary.java $
0024: //$Author: Dan $
0025: //$Revision: 50 $
0026: //$Modtime: 11/16/04 5:27p $
0027: /////////////////////////
0028:
0029: import java.io.Serializable;
0030: import java.sql.*;
0031: import java.util.*;
0032:
0033: import com.salmonllc.properties.Props;
0034:
0035: /**
0036: * Builds a data dictionary of meta-data for all tables/columns in the database associated with a database profile
0037: */
0038:
0039: public class DataDictionary implements Serializable {
0040: private Hashtable _elements;
0041: private Hashtable _tables;
0042: private String _application;
0043: private String _profile;
0044: private boolean _allTablesLoaded;
0045: private Hashtable _htOracleSynonyms = null;
0046: private DBConnection _conn;
0047:
0048: private class TableDefinition implements Serializable {
0049: public String tableName = null;
0050: public boolean keysLoaded = false;
0051: public Vector columns = new Vector();
0052: }
0053:
0054: private class StringVector extends com.salmonllc.util.VectorSort {
0055: public StringVector(int i) {
0056: super (i);
0057: }
0058:
0059: public boolean compare(Object o1, Object o2) {
0060: boolean ret = (((String) o1)
0061: .compareToIgnoreCase((String) o2) < 0);
0062: return ret;
0063: }
0064: }
0065:
0066: /**
0067: * Package Constructor, Only call from DBConnection
0068: */
0069: DataDictionary(DBConnection conn) {
0070: _conn = conn;
0071: _elements = new java.util.Hashtable();
0072: _tables = new java.util.Hashtable();
0073: _application = conn.getApplication();
0074: _profile = conn.getProfileName();
0075: }
0076:
0077: /**
0078: * Creates a data dictionary using the application name and default database profile.
0079: */
0080: public DataDictionary(String application) {
0081: this (application, null);
0082: }
0083:
0084: /**
0085: * Creates a data dictionary using the application name and database profile.
0086: */
0087: public DataDictionary(String application, String profile) {
0088: _application = application;
0089: _profile = profile;
0090: _elements = new java.util.Hashtable();
0091: _tables = new java.util.Hashtable();
0092: }
0093:
0094: /**
0095: * Returns the column definition object for a particular column (indicated by the column name table.column).
0096: */
0097: public ColumnDefinition getColumnDefintion(String columnName) {
0098: if (isLoaded(columnName))
0099: return (ColumnDefinition) _elements.get(columnName);
0100: else
0101: return null;
0102: }
0103:
0104: /**
0105: * Returns a Vector of column names in the database.
0106: */
0107: public Vector getColumnNames() {
0108: loadTable(null);
0109: Enumeration enum = _elements.keys();
0110: StringVector columns = new StringVector(_elements.size());
0111: String columnName = "";
0112: while (enum.hasMoreElements()) {
0113: columnName = (String) enum.nextElement();
0114: columns.addElement(columnName);
0115: }
0116: columns.sort();
0117:
0118: return columns;
0119: }
0120:
0121: /**
0122: * Returns a vector of column definitions for the specified table
0123: */
0124: public java.util.Vector getColumns(String tableName) {
0125: loadTable(tableName);
0126: TableDefinition def = (TableDefinition) _tables.get(tableName);
0127: if (def == null)
0128: return null;
0129: else
0130: return def.columns;
0131: }
0132:
0133: private DBConnection getConnection() throws SQLException {
0134: if (_conn != null)
0135: return _conn;
0136: else if (_profile == null)
0137: return DBConnection.getConnection(_application);
0138: else
0139: return DBConnection.getConnection(_application, _profile);
0140: }
0141:
0142: private void freeConnection(DBConnection conn) {
0143: if (_conn != null && conn == _conn)
0144: return;
0145: conn.freeConnection();
0146: }
0147:
0148: /**
0149: * Returns the Database datatype for the column.
0150: */
0151: public String getDBDataType(String columnName) {
0152: if (isLoaded(columnName)) {
0153: ColumnDefinition cd = (ColumnDefinition) _elements
0154: .get(columnName);
0155: return cd.getDBDataType();
0156: } else
0157: return null;
0158: }
0159:
0160: /**
0161: * Returns the datastore datatype of a column
0162: */
0163: public int getDSDataType(String columnName) {
0164: if (isLoaded(columnName)) {
0165: ColumnDefinition cd = (ColumnDefinition) _elements
0166: .get(columnName);
0167: return cd.getDSDataType();
0168: } else
0169: return -1;
0170: }
0171:
0172: /**
0173: * Returns the datalength of the column
0174: */
0175: public int getLength(String columnName) {
0176: if (isLoaded(columnName)) {
0177: ColumnDefinition cd = (ColumnDefinition) _elements
0178: .get(columnName);
0179: return cd.getLength();
0180: } else
0181: return -1;
0182:
0183: }
0184:
0185: private String getTable(String columnName) {
0186: if (columnName == null)
0187: return null;
0188: int pos = columnName.lastIndexOf(".");
0189: if (pos < 0)
0190: return null;
0191: return columnName.substring(0, pos);
0192: }
0193:
0194: /**
0195: * Returns a vector of all the table names in the database
0196: */
0197: public Vector getTableNames() {
0198: loadTable(null);
0199: Enumeration enum = _tables.keys();
0200: StringVector tables = new StringVector(_tables.size());
0201: String tableName = "";
0202: while (enum.hasMoreElements()) {
0203: tableName = (String) enum.nextElement();
0204: tables.addElement(tableName);
0205: }
0206: tables.sort();
0207: return tables;
0208: }
0209:
0210: private boolean isLoaded(String columnName) {
0211: String tab = getTable(columnName);
0212: if (tab == null)
0213: return false;
0214: if (tab.equals("null"))
0215: return false;
0216: loadTable(tab);
0217: return _elements.containsKey(columnName);
0218: }
0219:
0220: /**
0221: * Returns true if the column can be nulled.
0222: */
0223: public boolean isNullable(String columnName) {
0224: if (isLoaded(columnName)) {
0225: ColumnDefinition cd = (ColumnDefinition) _elements
0226: .get(columnName);
0227: return cd.isNullable();
0228: } else
0229: return false;
0230: }
0231:
0232: /**
0233: * Returns true if the column is part of the primary key of the table.
0234: */
0235: public boolean isPkey(String columnName) {
0236: if (isLoaded(columnName)) {
0237: ColumnDefinition cd = (ColumnDefinition) _elements
0238: .get(columnName);
0239: return cd.isPkey();
0240: } else
0241: return false;
0242: }
0243:
0244: private void loadTable(String tableName) {
0245: String user = null;
0246: String fullTableName = tableName;
0247: tableName = getTableFromFullName(tableName);
0248:
0249: if (tableName != null) {
0250: TableDefinition def = (TableDefinition) _tables
0251: .get(fullTableName);
0252: if (def != null) {
0253: if (!def.keysLoaded)
0254: loadKeys(tableName, fullTableName);
0255: return;
0256: }
0257: } else if (_allTablesLoaded)
0258: return;
0259:
0260: if (_htOracleSynonyms != null) {
0261: if (_htOracleSynonyms.containsKey(tableName)) {
0262: tableName = (String) _htOracleSynonyms.get(tableName);
0263: }
0264: }
0265:
0266: DBConnection connection = null;
0267: try {
0268: connection = getConnection();
0269: String dbms = connection.getDBMS();
0270: String column5 = null;
0271: boolean isSybase = dbms
0272: .equals(DBConnection.SYBASE_CONNECTION);
0273: boolean isSQLAnywhere = dbms
0274: .equals(DBConnection.SQLANYWHERE_CONNECTION);
0275: boolean isMSSQL = dbms
0276: .equals(DBConnection.MSSQLSEVER_CONNECTION);
0277: boolean isOracle = dbms
0278: .equals(DBConnection.ORACLE_CONNECTION);
0279: boolean isDB2MVS = dbms
0280: .equals(DBConnection.DB2MVS_CONNECTION);
0281: boolean isMySql = dbms
0282: .equals(DBConnection.MYSQL_CONNECTION);
0283: boolean isDB2 = dbms.equals(DBConnection.DB2_CONNECTION);
0284: boolean isDB2400 = dbms
0285: .equals(DBConnection.DB2400_CONNECTION);
0286: boolean isIngres = dbms
0287: .equals(DBConnection.INGRES_CONNECTION);
0288: boolean isFireBird = dbms
0289: .equals(DBConnection.FIREBIRDSQL_CONNECTION);
0290: boolean isAnsiSQL92 = dbms
0291: .equals(DBConnection.ANSISQL92_CONNECTION);
0292: boolean isPostGres = dbms
0293: .equals(DBConnection.POSTGRES_CONNECTION);
0294: String query;
0295: String dbname = connection.getDBName();
0296:
0297: if (isMySql) {
0298: Statement s = connection.createStatement();
0299: Vector vTables = new Vector();
0300: if (tableName == null) {
0301: ResultSet r = s.executeQuery("show tables");
0302: while (r.next()) {
0303: vTables.addElement(r.getString(1));
0304: }
0305: r.close();
0306: } else
0307: vTables.addElement(tableName);
0308: for (int i = 0; i < vTables.size(); i++) {
0309: String table = (String) vTables.elementAt(i);
0310: ResultSet r = s.executeQuery("describe "
0311: + fixDashes(table));
0312: String column = null;
0313: String field = null;
0314: String type = null;
0315: boolean nullable = false;
0316: int length = -1;
0317: while (r.next()) {
0318: column = r.getString(1);
0319: field = table + "." + column;
0320: type = r.getString(2);
0321: int iOpenParen = type.indexOf('(');
0322: int iCloseParen = type.indexOf(')');
0323: if (iOpenParen != -1) {
0324: String typeCheck = type.toUpperCase();
0325: if (typeCheck.startsWith("ENUM")
0326: || typeCheck.startsWith("SET"))
0327: type = type.substring(0, iOpenParen);
0328: else {
0329: String sLength = type.substring(
0330: iOpenParen + 1, iCloseParen);
0331: int iComma = sLength.indexOf(",");
0332: if (iComma == -1)
0333: length = (new Integer(sLength))
0334: .intValue();
0335: else {
0336: String sFirstNum = sLength
0337: .substring(0, iComma);
0338: String sLastNum = sLength
0339: .substring(iComma + 1);
0340: length = (new Integer(sFirstNum))
0341: .intValue()
0342: + (new Integer(sLastNum))
0343: .intValue();
0344: }
0345: type = type.substring(0, iOpenParen);
0346: }
0347: }
0348: String sNullable = r.getString(3);
0349: nullable = (sNullable.length() > 0 && sNullable
0350: .charAt(0) == 'Y');
0351: ColumnDefinition columnDef = new ColumnDefinition(
0352: connection.getDBMS(), table, column,
0353: type, length, nullable);
0354: _elements.put(field, columnDef);
0355: TableDefinition def = (TableDefinition) _tables
0356: .get(table);
0357: if (def != null) {
0358: def.columns.addElement(columnDef);
0359: } else {
0360: def = new TableDefinition();
0361: def.columns.addElement(columnDef);
0362: _tables.put(table, def);
0363: }
0364: }
0365: r.close();
0366: }
0367: s.close();
0368: } else if (isIngres) {
0369: Statement s = connection.createStatement();
0370: Vector vTables = new Vector();
0371: if (tableName == null) {
0372: ResultSet r = s
0373: .executeQuery("SELECT trim(table_name), table_type FROM iitables where "
0374: + "table_owner != '$ingres' and table_name not like '$%' and "
0375: + "table_name not like 'ii%' and table_type != 'I'");
0376: while (r.next()) {
0377: vTables.addElement(r.getString(1));
0378: }
0379: r.close();
0380: } else
0381: vTables.addElement(tableName);
0382: for (int i = 0; i < vTables.size(); i++) {
0383: String table = (String) vTables.elementAt(i);
0384: ResultSet r = s
0385: .executeQuery("select * from iicolumns where table_name = '"
0386: + table + "'");
0387: String column = null;
0388: String field = null;
0389: String type = null;
0390: boolean nullable = false;
0391: int length = -1;
0392: while (r.next()) {
0393: column = r.getString(3).trim();
0394: field = table + "." + column;
0395: type = r.getString(4).trim();
0396: int iOpenParen = type.indexOf('(');
0397: int iCloseParen = type.indexOf(')');
0398: if (iOpenParen != -1) {
0399: String sLength = type.substring(
0400: iOpenParen + 1, iCloseParen);
0401: int iComma = sLength.indexOf(",");
0402: if (iComma == -1)
0403: length = (new Integer(sLength))
0404: .intValue();
0405: else {
0406: String sFirstNum = sLength.substring(0,
0407: iComma);
0408: String sLastNum = sLength
0409: .substring(iComma + 1);
0410: length = (new Integer(sFirstNum))
0411: .intValue()
0412: + (new Integer(sLastNum))
0413: .intValue();
0414: }
0415: type = type.substring(0, iOpenParen);
0416: } else
0417: length = r.getInt(5);
0418: String sNullable = r.getString(7);
0419: nullable = (sNullable.length() > 0 && sNullable
0420: .charAt(0) == 'Y');
0421: ColumnDefinition columnDef = new ColumnDefinition(
0422: connection.getDBMS(), table, column,
0423: type, length, nullable);
0424: _elements.put(field, columnDef);
0425: TableDefinition def = (TableDefinition) _tables
0426: .get(table);
0427: if (def != null) {
0428: def.columns.addElement(columnDef);
0429: } else {
0430: def = new TableDefinition();
0431: def.columns.addElement(columnDef);
0432: _tables.put(table, def);
0433: }
0434: }
0435: r.close();
0436: }
0437: s.close();
0438: } else if (isPostGres) {
0439: boolean nullable = false;
0440:
0441: Statement s = connection.createStatement();
0442: Vector vTables = new Vector();
0443: if (tableName == null) {
0444: ResultSet r = s
0445: .executeQuery("select relname from pg_class where relkind = 'r' and not relname like 'pg_%'");
0446: while (r.next()) {
0447: vTables.addElement(r.getString(1));
0448: }
0449: r.close();
0450: } else
0451: vTables.addElement(tableName);
0452: for (int i = 0; i < vTables.size(); i++) {
0453: String table = (String) vTables.elementAt(i);
0454: ResultSet r = s.executeQuery("select * from "
0455: + table + " where 1 = 2 ");
0456: ResultSetMetaData rsmd = r.getMetaData();
0457: int numberOfColumns = rsmd.getColumnCount();
0458:
0459: String column = null;
0460: String field = null;
0461: String type = null;
0462: nullable = false;
0463: int length = -1;
0464: int precision = -1;
0465: int scale = -1;
0466: for (int j = 1; j <= numberOfColumns; j++) {
0467: column = rsmd.getColumnName(j);
0468: field = table + "." + column;
0469: type = rsmd.getColumnTypeName(j);
0470:
0471: length = rsmd.getColumnDisplaySize(j);
0472: precision = rsmd.getPrecision(j);
0473: scale = rsmd.getScale(1);
0474:
0475: if (type.equals("numeric")
0476: && (length - precision == 1))
0477: type = "long";
0478: ColumnDefinition columnDef4 = new ColumnDefinition(
0479: table, column, type, length, nullable,
0480: precision, scale);
0481: _elements.put(field, columnDef4);
0482: TableDefinition def4 = (TableDefinition) _tables
0483: .get(table);
0484: if (def4 != null) {
0485: def4.columns.addElement(columnDef4);
0486: } else {
0487: def4 = new TableDefinition();
0488: def4.columns.addElement(columnDef4);
0489: _tables.put(table, def4);
0490: }
0491: }
0492: r.close();
0493: }
0494: s.close();
0495: } else if (isFireBird) {
0496: Statement s = connection.createStatement();
0497: Vector vTables = new Vector();
0498: if (tableName == null) {
0499: ResultSet r = s
0500: .executeQuery("Select RDB$RELATION_NAME from RDB$RELATIONS"
0501: + " where RDB$VIEW_BLR is NULL and RDB$SYSTEM_FLAG = 0");
0502: while (r.next()) {
0503: vTables.addElement(r.getString(1).trim());
0504: }
0505: r.close();
0506: } else
0507: vTables.addElement(tableName);
0508: for (int i = 0; i < vTables.size(); i++) {
0509: String table = (String) vTables.elementAt(i);
0510: String SQL = "Select R.RDB$FIELD_NAME, T.RDB$TYPE_NAME,"
0511: + "R.RDB$NULL_FLAG,"
0512: + "F.RDB$FIELD_LENGTH,"
0513: + "RDB$FIELD_POSITION"
0514: + " from RDB$RELATION_FIELDS R, RDB$FIELDS F ,RDB$TYPES T "
0515: + " where R.RDB$RELATION_NAME = '"
0516: + table
0517: + "'"
0518: + " and R.RDB$FIELD_SOURCE = F.RDB$FIELD_NAME"
0519: + " and t.rdb$type = f.rdb$field_type and t.RDB$FIELD_NAME='RDB$FIELD_TYPE'"
0520: + " order by R.RDB$FIELD_POSITION";
0521: ResultSet r = s.executeQuery(SQL);
0522: String column = null;
0523: String field = null;
0524: String type = null;
0525: boolean nullable = false;
0526: int length = -1;
0527:
0528: while (r.next()) {
0529: column = r.getString(1).trim();
0530: field = table + "." + column; //get field name
0531: type = r.getString(2).trim(); //get field type
0532: String sNullable = r.getString(3); // ==1 presend nullable
0533: length = r.getInt(4); //get field length
0534: nullable = (sNullable != null)
0535: && (sNullable.length() > 0)
0536: && (sNullable.compareTo("1") != -1);
0537: nullable = !nullable;
0538: ColumnDefinition columnDef = new ColumnDefinition(
0539: connection.getDBMS(), table, column,
0540: type, length, nullable);
0541: _elements.put(field, columnDef);
0542: TableDefinition def = (TableDefinition) _tables
0543: .get(table);
0544: if (def != null) {
0545: def.columns.addElement(columnDef);
0546: } else {
0547: def = new TableDefinition();
0548: def.columns.addElement(columnDef);
0549: _tables.put(table, def);
0550: }
0551: }
0552: r.close();
0553: }
0554: s.close();
0555: } else if (isAnsiSQL92) {
0556: Connection con = connection.getJDBCConnection();
0557: DatabaseMetaData meta = con.getMetaData();
0558: ResultSet tables = meta.getTables(null, null,
0559: tableName, null);
0560: while (tables.next()) {
0561: String catName = tables.getString(1);
0562: String schName = tables.getString(2);
0563: String tabName = tables.getString(3);
0564: TableDefinition def = (TableDefinition) _tables
0565: .get(tabName);
0566: if (def != null)
0567: continue;
0568: def = new TableDefinition();
0569: def.tableName = tabName;
0570:
0571: ResultSet columns = meta.getColumns(catName,
0572: schName, tabName, null);
0573: Vector cols = def.columns;
0574: ColumnDefinition colDef;
0575: String colName;
0576: while (columns.next()) {
0577: colName = columns.getString(4);
0578: colDef = new ColumnDefinition(connection
0579: .getDBMS(), tabName, colName, columns
0580: .getString(6), columns.getInt(7), "YES"
0581: .equals(columns.getString(18)));
0582: cols.add(colDef);
0583: _elements.put(tabName + "." + colName, colDef);
0584: }
0585: columns.close();
0586:
0587: ResultSet pkeys = meta.getPrimaryKeys(catName,
0588: schName, tabName);
0589: while (pkeys.next()) {
0590: colName = tabName + "." + pkeys.getString(4);
0591: colDef = (ColumnDefinition) _elements
0592: .get(colName);
0593: if (colDef != null)
0594: colDef.setPkey();
0595: }
0596: pkeys.close();
0597: def.keysLoaded = true;
0598: _tables.put(tabName, def);
0599: }
0600: } else {
0601: if (isDB2MVS) {
0602: query = "SELECT O.NAME,C.NAME,COLTYPE,LENGTH,NULLS FROM SYSIBM.SYSTABLES O,SYSIBM.SYSCOLUMNS C "
0603: + "WHERE O.NAME = C.TBNAME AND O.CREATOR = C.TBCREATOR "
0604: + "and "
0605: + (tableName == null ? "O.CREATOR = '"
0606: + dbname + "' " : "O.CREATOR = '"
0607: + dbname + "' and O.NAME = '"
0608: + tableName + "' ")
0609: + "ORDER BY O.NAME, C.NAME";
0610: } else if (isDB2) {
0611: query = "SELECT O.NAME,C.NAME,COLTYPE,LENGTH,NULLS FROM SYSIBM.SYSTABLES O,SYSIBM.SYSCOLUMNS C "
0612: + "WHERE O.NAME = C.TBNAME AND O.CREATOR = C.TBCREATOR AND "
0613: + (tableName == null ? "O.CREATOR != 'SYSIBM' AND O.CREATOR != 'SYSCAT' AND O.CREATOR != 'SYSSTAT' "
0614: : "O.NAME = '" + tableName + "' ")
0615: + "ORDER BY O.NAME, C.NAME";
0616: } else if (isDB2400) {
0617: String locDbName = dbname.toUpperCase();
0618: String locTableName = null;
0619: if (tableName != null)
0620: locTableName = tableName.toUpperCase();
0621: query = "SELECT O.NAME,C.NAME,COLTYPE,LENGTH,NULLS FROM QSYS2.SYSTABLES O,QSYS2.SYSCOLUMNS C "
0622: + "WHERE O.NAME = C.TBNAME AND O.TABLE_SCHEMA = C.TABLE_SCHEMA "
0623: + "and "
0624: + (locTableName == null ? "O.TABLE_SCHEMA = '"
0625: + locDbName + "' "
0626: : "O.TABLE_SCHEMA = '" + locDbName
0627: + "' and O.NAME = '"
0628: + locTableName + "' ")
0629: + "ORDER BY O.NAME, C.NAME";
0630: } else if (isSQLAnywhere) {
0631: query = "SELECT o.name, c.cname, c.coltype, c.length, c.nulls "
0632: + "FROM sys.syscolumns c, dbo.sysobjects o "
0633: + "WHERE "
0634: + "c.tname = o.name and o.type in ('U','V') and "
0635: + "o.name not in('pbcatcol','pbcatedt','pbcatfmt','pbcattbl','pbcatvld', 'dtproperties') "
0636: + "ORDER BY o.name, c.cname ";
0637: } else if (!isOracle) {
0638: String userType = "usertype";
0639: if (isSybase)
0640: column5 = "c.status";
0641: else if (isMSSQL) {
0642: column5 = "c.isnullable";
0643: userType = "xusertype";
0644: }
0645: /* Changed to use xusertype column on 11/11/2004 by MK */
0646: query = "SELECT o.name, c.name, t.name, c.length, "
0647: + column5
0648: + " FROM syscolumns c, sysobjects o, systypes t "
0649: + "WHERE c.id = o.id and (o.type = 'U' or o.type='V') and c."
0650: + userType
0651: + " = t."
0652: + userType
0653: + " and "
0654: + (tableName == null ? "o.name not in('pbcatcol','pbcatedt','pbcatfmt','pbcattbl','pbcatvld', 'dtproperties')"
0655: : "o.name = '" + tableName + "'")
0656: + "ORDER BY o.name, c.name";
0657: } else {
0658: Props p = Props.getProps(_application, null);
0659:
0660: String def = p.getProperty(Props.DB_DEFAULT);
0661: if (def != null)
0662: def += ".";
0663: else
0664: def = "";
0665:
0666: String name = _profile;
0667: if (name == null)
0668: name = def;
0669: else
0670: name += ".";
0671:
0672: user = p.getProperty(name + Props.DB_USER)
0673: .toUpperCase();
0674: if (_htOracleSynonyms == null)
0675: _htOracleSynonyms = new Hashtable();
0676: String sQuerySynonym = "SELECT distinct table_name,synonym_name from all_synonyms where owner='"
0677: + user + "'";
0678: Statement s = connection.createStatement();
0679: ResultSet r = s.executeQuery(sQuerySynonym);
0680: while (r.next()) {
0681: _htOracleSynonyms.put(r.getString(2), r
0682: .getString(1));
0683: }
0684: String sSynonymName = null;
0685: if (tableName != null) {
0686: if (_htOracleSynonyms.containsKey(tableName)) {
0687: sSynonymName = tableName;
0688: tableName = (String) _htOracleSynonyms
0689: .get(tableName);
0690: }
0691: }
0692: r.close();
0693: s.close();
0694: //,(select synonym_name from all_synonyms where owner='"+user+"' and table_name=o.object_name and table_owner=o.owner)
0695: query = "SELECT distinct o.object_name, c.column_name, c.data_type, c.data_length, c.nullable, c.data_precision, c.data_scale,o.owner,(select synonym_name from all_synonyms where owner='"
0696: + user
0697: + "' and table_name=o.object_name and table_owner=o.owner) FROM sys.all_tab_columns c, sys.all_objects o WHERE c.table_name = o.object_name and ((o.owner = '"
0698: + user
0699: + "' and c.owner = '"
0700: + user
0701: + "') or (c.owner||c.table_name in (select distinct table_owner||table_name from all_synonyms where table_name=c.table_name and owner='"
0702: + user
0703: + "'"
0704: + (sSynonymName == null ? " and synonym_name in (select synonym_name from all_synonyms where owner='"
0705: + user
0706: + "' and table_name=o.object_name and table_owner=o.owner)"
0707: : " and synonym_name='"
0708: + sSynonymName + "'")
0709: + "))) and "
0710: + (tableName == null ? "o.object_name not in('pbcatcol','pbcatedt','pbcatfmt','pbcattbl','pbcatvld')"
0711: : "o.object_name = '" + tableName
0712: + "'")
0713: + " and (o.object_type='TABLE' or o.object_type='VIEW') ORDER BY o.object_name, c.column_name";
0714:
0715: }
0716: Statement s = connection.createStatement();
0717: ResultSet r = s.executeQuery(query);
0718: String table = null;
0719: String column = null;
0720: String field = null;
0721: String type = null;
0722: boolean nullable = false;
0723: int precision = -1;
0724: int scale = -1;
0725: int length = -1;
0726: /*if (dbname == null)
0727: dbname = "";
0728: else if (isSybase)
0729: dbname += ".dbo.";
0730: else
0731: dbname += ".";*/
0732: dbname = "";
0733:
0734: while (r.next()) {
0735: if (isOracle && !r.getString(8).equals(user)) {
0736: String sSynom = r.getString(9);
0737: table = dbname + sSynom;
0738: } else
0739: table = dbname + r.getString(1);
0740: column = r.getString(2);
0741: field = table + "." + column;
0742: type = r.getString(3).trim();
0743: length = r.getInt(4);
0744: if (isSybase)
0745: nullable = (((r.getInt(5) % 16) / 8) == 1);
0746: else if (isSQLAnywhere)
0747: nullable = (r.getString(5).charAt(0) == 'Y');
0748: else if (isMSSQL)
0749: nullable = (r.getInt(5) == 1);
0750: else if (isOracle)
0751: nullable = (r.getString(5).charAt(0) == 'Y');
0752:
0753: // TODO: get precision and scale for other database systems
0754: if (isOracle) {
0755: precision = r.getInt(6);
0756: scale = r.getInt(7);
0757: }
0758:
0759: // ColumnDefinition columnDef = new ColumnDefinition(table, column, type, length, nullable);
0760: ColumnDefinition columnDef = new ColumnDefinition(
0761: connection.getDBMS(), table, column, type,
0762: length, nullable, precision, scale);
0763:
0764: _elements.put(field, columnDef);
0765: TableDefinition def = (TableDefinition) _tables
0766: .get(table);
0767: if (def != null) {
0768: def.columns.addElement(columnDef);
0769: } else {
0770: def = new TableDefinition();
0771: def.columns.addElement(columnDef);
0772: _tables.put(table, def);
0773: }
0774: }
0775: r.close();
0776: s.close();
0777: }
0778: if (tableName == null)
0779: _allTablesLoaded = true;
0780: } catch (SQLException e) {
0781: com.salmonllc.util.MessageLog.writeErrorMessage(e, this );
0782: } finally {
0783: if (connection != null)
0784: freeConnection(connection);
0785: }
0786:
0787: if (tableName != null) {
0788: TableDefinition def = (TableDefinition) _tables
0789: .get(fullTableName);
0790: if (def != null) {
0791: if (!def.keysLoaded)
0792: loadKeys(tableName, fullTableName);
0793: }
0794: }
0795:
0796: }
0797:
0798: /*
0799: TODO: research this code
0800: This method was submitted by a SOFIA user. It is the loadTable method done in
0801: a generic fashon using the JDBC driver built-in code. It has a few problems which has prevented
0802: it from being incorporated:
0803: 1) It uses the full name for tables for every database including the database, owner and table.
0804: This should only be used for some engines and not others
0805: 2) It is very slow to load all table at once for big databases
0806: 3) There is no good way to test the code for every different engine since we don't have them all available
0807: private void loadTable(String tableName) {
0808: if (_allTablesLoaded || (tableName != null && _tables.get(tableName) != null))
0809: return;
0810:
0811: String catalogName = null, schemaName = null;
0812: String fullTableName = tableName;
0813: String[] names = new String[6];
0814: for (int i = 0; i < 3; i++)
0815: names[i] = null;
0816: if (fullTableName != null) {
0817: fullTableName = fullTableName.replaceAll("..", ". .");
0818: StringTokenizer st = new StringTokenizer(fullTableName, ".");
0819: int pos = 0;
0820: while (st.hasMoreTokens()) {
0821: String name = st.nextToken();
0822: if (" ".equals(name))
0823: name = null;
0824: names[pos++] = name;
0825: }
0826: tableName = names[--pos];
0827: if (pos > 0)
0828: schemaName = names[--pos];
0829: if (pos > 0)
0830: catalogName = names[--pos];
0831: }
0832:
0833: DBConnection connection = null;
0834: try {
0835: connection = getConnection();
0836: Connection con = connection.getJDBCConnection();
0837: DatabaseMetaData meta = con.getMetaData();
0838: ResultSet tables = meta.getTables(catalogName, schemaName, tableName, null);
0839: while (tables.next()) {
0840: String catName = tables.getString(1);
0841: String schName = tables.getString(2);
0842: String tabName = tables.getString(3);
0843: String fullName = getFullName(catName, schName, tabName);
0844: TableDefinition def = (TableDefinition) _tables.get(fullName);
0845: if (def != null)
0846: continue;
0847: def = new TableDefinition();
0848: def.tableName = fullName;
0849:
0850: ResultSet columns = meta.getColumns(catName, schName, tabName, null);
0851: Vector cols = def.columns;
0852: ColumnDefinition colDef;
0853: String colName;
0854: while (columns.next()) {
0855: colName = columns.getString(4);
0856: colDef =
0857: new ColumnDefinition(
0858: fullName,
0859: colName,
0860: columns.getString(6),
0861: columns.getInt(7),
0862: "YES".equals(columns.getString(18)));
0863: cols.add(colDef);
0864: _elements.put(fullName + "." + colName, colDef);
0865: }
0866:
0867: ResultSet pkeys = meta.getPrimaryKeys(catalogName, schemaName, tabName);
0868: while (pkeys.next()) {
0869: colName = fullName + "." + pkeys.getString(4);
0870: colDef = (ColumnDefinition) _elements.get(colName);
0871: if (colDef != null)
0872: colDef.setPkey();
0873: }
0874: _tables.put(fullName, def);
0875: }
0876: } catch (SQLException e) {
0877: com.salmonllc.util.MessageLog.writeErrorMessage(e, this);
0878: } finally {
0879: if (connection != null)
0880: connection.freeConnection();
0881: }
0882:
0883: if (tableName == null) {
0884: _allTablesLoaded = true;
0885: }
0886: }
0887:
0888: private String getFullName(String catName,String schemaName, String tabName) {
0889: String ret = "";
0890: if (catName != null && catName.length() > 0)
0891: ret += catName + ".";
0892: if (schemaName != null && schemaName.length() > 0)
0893: ret += schemaName + ".";
0894: ret += tabName;
0895: return ret;
0896: }
0897:
0898: */
0899:
0900: private String getTableFromFullName(String fullName) {
0901: if (fullName == null)
0902: return null;
0903: int pos = fullName.lastIndexOf(".");
0904: if (pos < 0)
0905: return fullName;
0906: return fullName.substring(pos + 1);
0907: }
0908:
0909: private void loadKeys(String tableName, String fullTableName) {
0910: DBConnection conn = null;
0911: try {
0912: int indexNo = -1;
0913: int keyCount = -1;
0914: conn = getConnection();
0915: Statement st = conn.createStatement();
0916: if (conn.getDBMS().equals(DBConnection.MYSQL_CONNECTION)) {
0917: StringBuffer query = new StringBuffer(
0918: "show index from " + fixDashes(tableName));
0919: ResultSet r = st.executeQuery(query.toString());
0920: while (r.next()) {
0921: query.setLength(0);
0922: query.append(fullTableName);
0923: query.append(".");
0924: query.append(r.getString(5));
0925: if (r.getString(3).equals("PRIMARY")) {
0926: ColumnDefinition def = (ColumnDefinition) _elements
0927: .get(query.toString());
0928: if (def != null)
0929: def.setPkey();
0930: }
0931: }
0932: r.close();
0933:
0934: TableDefinition tab = (TableDefinition) _tables
0935: .get(fullTableName);
0936: tab.keysLoaded = true;
0937: } else if (conn.getDBMS().equals(
0938: DBConnection.FIREBIRDSQL_CONNECTION)) {
0939: String theSQL = "SELECT R.RDB$FIELD_NAME FROM "
0940: + " RDB$INDEX_SEGMENTS R, rdb$indices S"
0941: + " WHERE S.rdb$relation_name ='"
0942: + tableName
0943: + "'"
0944: + " AND R.RDB$INDEX_NAME= S.RDB$INDEX_NAME AND S.RDB$INDEX_ID=1";
0945: //select out the main key
0946: StringBuffer query = new StringBuffer(" " + tableName);
0947: ResultSet r = st.executeQuery(theSQL);
0948: while (r.next()) {
0949: query.setLength(0);
0950: query.append(fullTableName);
0951: query.append(".");
0952: query.append(r.getString(1).trim());
0953: ColumnDefinition def = (ColumnDefinition) _elements
0954: .get(query.toString());
0955: if (def != null)
0956: def.setPkey();
0957: }
0958: r.close();
0959:
0960: TableDefinition tab = (TableDefinition) _tables
0961: .get(fullTableName);
0962: tab.keysLoaded = true;
0963: } else if (conn.getDBMS().equals(
0964: DBConnection.INGRES_CONNECTION)) {
0965: StringBuffer query = new StringBuffer(
0966: "SELECT trim(column_name), trim(table_name) from iiconstraints, iiindex_columns WHERE index_name = constraint_name "
0967: + "AND constraint_type = 'P' AND table_name = '");
0968: query.append(tableName);
0969: query.append("'");
0970: ResultSet r = st.executeQuery(query.toString());
0971: while (r.next()) {
0972: query.setLength(0);
0973: query.append(fullTableName);
0974: query.append(".");
0975: query.append(r.getString(1));
0976: ColumnDefinition def = (ColumnDefinition) _elements
0977: .get(query.toString());
0978: if (def != null)
0979: def.setPkey();
0980: }
0981: r.close();
0982:
0983: TableDefinition tab = (TableDefinition) _tables
0984: .get(fullTableName);
0985: tab.keysLoaded = true;
0986: } else if (conn.getDBMS().equals(
0987: DBConnection.POSTGRES_CONNECTION)) {
0988: //TODO:Add load keys for POSTGRES
0989: TableDefinition tab = (TableDefinition) _tables
0990: .get(fullTableName);
0991: tab.keysLoaded = true;
0992: } else if (conn.getDBMS().equals(
0993: DBConnection.DB2MVS_CONNECTION)) {
0994: String dbname = conn.getDBName();
0995: StringBuffer query = new StringBuffer(
0996: "SELECT C.COLNAME FROM SYSIBM.SYSKEYS C, SYSIBM.SYSINDEXES O ");
0997: query
0998: .append("WHERE O.NAME = C.IXNAME AND O.CREATOR = C.IXCREATOR ");
0999: query
1000: .append("AND O.UNIQUERULE IN ('U','P') AND O.CREATOR = '"
1001: + dbname + "' AND O.TBNAME = '");
1002: query.append(tableName);
1003: query.append("'");
1004: ResultSet r = st.executeQuery(query.toString());
1005: while (r.next()) {
1006: query.setLength(0);
1007: query.append(fullTableName);
1008: query.append(".");
1009: query.append(r.getString(1));
1010: ColumnDefinition def = (ColumnDefinition) _elements
1011: .get(query.toString());
1012: if (def != null)
1013: def.setPkey();
1014: }
1015: r.close();
1016:
1017: TableDefinition tab = (TableDefinition) _tables
1018: .get(fullTableName);
1019: tab.keysLoaded = true;
1020: } else if (conn.getDBMS().equals(
1021: DBConnection.DB2_CONNECTION)) {
1022: StringBuffer query = new StringBuffer(
1023: "SELECT C.COLNAME, I.TBNAME FROM SYSIBM.SYSINDEXCOLUSE C, SYSIBM.SYSINDEXES I WHERE I.NAME = C.INDNAME AND I.UNIQUERULE IN ('U','P') AND I.TBNAME='");
1024: query.append(tableName);
1025: query.append("'");
1026: ResultSet r = st.executeQuery(query.toString());
1027: while (r.next()) {
1028: query.setLength(0);
1029: query.append(fullTableName);
1030: query.append(".");
1031: query.append(r.getString(1));
1032: ColumnDefinition def = (ColumnDefinition) _elements
1033: .get(query.toString());
1034: if (def != null)
1035: def.setPkey();
1036: }
1037: r.close();
1038:
1039: TableDefinition tab = (TableDefinition) _tables
1040: .get(fullTableName);
1041: tab.keysLoaded = true;
1042: } else if (conn.getDBMS().equals(
1043: DBConnection.DB2400_CONNECTION)) {
1044: String dbname = conn.getDBName();
1045: dbname = dbname.toUpperCase();
1046: StringBuffer query = new StringBuffer(
1047: "SELECT C.COLUMN_NAME FROM QSYS2.SYSCSTCOL C, QSYS2.SYSCST O ");
1048: query
1049: .append("WHERE O.CONSTRAINT_NAME = C.CONSTRAINT_NAME AND O.TABLE_SCHEMA = C.CONSTRAINT_SCHEMA ");
1050: query
1051: .append("AND O.CONSTRAINT_TYPE = 'PRIMARY KEY' AND O.TABLE_SCHEMA = '"
1052: + dbname + "' AND O.TABLE_NAME = '");
1053: query.append(tableName.toUpperCase());
1054: query.append("'");
1055: ResultSet r = st.executeQuery(query.toString());
1056: while (r.next()) {
1057: query.setLength(0);
1058: query.append(fullTableName);
1059: query.append(".");
1060: query.append(r.getString(1));
1061: ColumnDefinition def = (ColumnDefinition) _elements
1062: .get(query.toString());
1063: if (def != null)
1064: def.setPkey();
1065: }
1066: r.close();
1067:
1068: TableDefinition tab = (TableDefinition) _tables
1069: .get(fullTableName);
1070: tab.keysLoaded = true;
1071: } else if (!conn.getDBMS().equals(
1072: DBConnection.ORACLE_CONNECTION)) {
1073: //StringBuffer query = new StringBuffer("SELECT sysindexes.indid, sysindexes.keycnt,sysindexes.status FROM sysindexes,sysobjects WHERE sysindexes.id = sysobjects.id and (sysindexes.status & 2048 = 2048 or sysindexes.status & 2 = 2) and sysobjects.name ='");
1074: StringBuffer query;
1075: if (conn.getDBMS().equals(
1076: DBConnection.SQLANYWHERE_CONNECTION))
1077: query = new StringBuffer(
1078: "SELECT sysindexes.indid, sysindexes.keycnt,sysindexes.status FROM dbo.sysindexes, dbo.sysobjects WHERE sysindexes.id = sysobjects.id and (sysindexes.status & 2048 = 2048 or sysindexes.status & 2 = 2) and sysobjects.name ='");
1079: else
1080: query = new StringBuffer(
1081: "SELECT sysindexes.indid, sysindexes.keycnt,sysindexes.status FROM sysindexes,sysobjects WHERE sysindexes.id = sysobjects.id and (sysindexes.status & 2048 = 2048 or sysindexes.status & 2 = 2) and sysobjects.name ='");
1082:
1083: query.append(tableName);
1084: query.append("'");
1085: ResultSet r = st.executeQuery(query.toString());
1086: while (r.next()) {
1087: indexNo = r.getInt(1);
1088: keyCount = r.getInt(2);
1089: int status = r.getInt(3);
1090: if ((status & 2048) > 0)
1091: break;
1092: }
1093: r.close();
1094:
1095: if ((indexNo > -1) && (keyCount >= 1)) {
1096: String indexSt = new Integer(indexNo).toString();
1097: query.setLength(7);
1098: for (int i = 1; i <= keyCount; i++) {
1099: query.append("index_col('");
1100: query.append(tableName);
1101: query.append("',");
1102: query.append(indexSt);
1103: query.append(",");
1104: query.append(new Integer(i).toString());
1105: query.append("),");
1106: }
1107: query.setLength(query.length() - 1);
1108:
1109: r = st.executeQuery(query.toString());
1110: if (r.next()) {
1111: for (int i = 1; i <= keyCount; i++) {
1112: query.setLength(0);
1113: query.append(fullTableName);
1114: query.append(".");
1115: query.append(r.getString(i));
1116: ColumnDefinition def = (ColumnDefinition) _elements
1117: .get(query.toString());
1118: if (def != null)
1119: def.setPkey();
1120: }
1121: }
1122: r.close();
1123:
1124: TableDefinition tab = (TableDefinition) _tables
1125: .get(fullTableName);
1126: tab.keysLoaded = true;
1127: }
1128: } else {
1129: StringBuffer query = new StringBuffer(
1130: "SELECT c.column_name FROM user_ind_columns c, user_constraints o WHERE o.constraint_name = c.index_name and o.constraint_type='P' and c.table_name ='");
1131: query.append(tableName);
1132: query.append("'");
1133: ResultSet r = st.executeQuery(query.toString());
1134: while (r.next()) {
1135: query.setLength(0);
1136: query.append(fullTableName);
1137: query.append(".");
1138: query.append(r.getString(1));
1139: ColumnDefinition def = (ColumnDefinition) _elements
1140: .get(query.toString());
1141: if (def != null)
1142: def.setPkey();
1143: }
1144: r.close();
1145:
1146: TableDefinition tab = (TableDefinition) _tables
1147: .get(fullTableName);
1148: tab.keysLoaded = true;
1149: }
1150: st.close();
1151: } catch (Exception e) {
1152: com.salmonllc.util.MessageLog.writeErrorMessage(
1153: "loadKeys()", e, this );
1154: } finally {
1155: if (conn != null)
1156: freeConnection(conn);
1157: }
1158:
1159: }
1160:
1161: private String fixDashes(String value) {
1162: if (value != null)
1163: if (value.indexOf("-") > -1) {
1164: return "`" + value + "`";
1165: }
1166:
1167: return value;
1168: }
1169: }
|