0001: /*
0002: * DbMetadata.java
0003: *
0004: * This file is part of SQL Workbench/J, http://www.sql-workbench.net
0005: *
0006: * Copyright 2002-2008, Thomas Kellerer
0007: * No part of this code maybe reused without the permission of the author
0008: *
0009: * To contact the author please send an email to: support@sql-workbench.net
0010: *
0011: */
0012: package workbench.db;
0013:
0014: import java.io.File;
0015: import java.sql.Connection;
0016: import java.sql.DatabaseMetaData;
0017: import java.sql.ResultSet;
0018: import java.sql.SQLException;
0019: import java.sql.Savepoint;
0020: import java.sql.Statement;
0021: import java.sql.Types;
0022: import java.util.ArrayList;
0023: import java.util.Collection;
0024: import java.util.Collections;
0025: import java.util.HashMap;
0026: import java.util.HashSet;
0027: import java.util.HashSet;
0028: import java.util.Iterator;
0029: import java.util.LinkedList;
0030: import java.util.List;
0031: import java.util.List;
0032: import java.util.Map;
0033: import java.util.Map.Entry;
0034: import java.util.Set;
0035: import java.util.Map.Entry;
0036: import java.util.TreeSet;
0037: import java.util.regex.Matcher;
0038: import java.util.regex.Pattern;
0039: import workbench.db.derby.DerbyConstraintReader;
0040: import workbench.db.derby.DerbySynonymReader;
0041: import workbench.db.firebird.FirebirdProcedureReader;
0042: import workbench.db.firstsql.FirstSqlMetadata;
0043: import workbench.db.hsqldb.HsqlSequenceReader;
0044: import workbench.db.ibm.Db2SequenceReader;
0045: import workbench.db.ibm.Db2SynonymReader;
0046: import workbench.db.ingres.IngresMetadata;
0047: import workbench.db.mckoi.McKoiMetadata;
0048: import workbench.db.mssql.SqlServerConstraintReader;
0049: import workbench.db.mssql.SqlServerProcedureReader;
0050: import workbench.db.mysql.EnumReader;
0051: import workbench.db.mysql.MySqlProcedureReader;
0052: import workbench.db.oracle.DbmsOutput;
0053: import workbench.db.oracle.OracleConstraintReader;
0054: import workbench.db.oracle.OracleIndexReader;
0055: import workbench.db.oracle.OracleMetadata;
0056: import workbench.db.oracle.OracleProcedureReader;
0057: import workbench.db.oracle.OracleSynonymReader;
0058: import workbench.db.postgres.PostgresDDLFilter;
0059: import workbench.db.postgres.PostgresIndexReader;
0060: import workbench.db.postgres.PostgresSequenceReader;
0061: import workbench.db.postgres.PostgresConstraintReader;
0062: import workbench.db.postgres.PostgresProcedureReader;
0063: import workbench.util.ExceptionUtil;
0064: import workbench.log.LogMgr;
0065: import workbench.resource.Settings;
0066: import workbench.storage.DataStore;
0067: import workbench.util.SqlUtil;
0068: import workbench.util.StringUtil;
0069: import workbench.db.hsqldb.HsqlConstraintReader;
0070: import workbench.db.firebird.FirebirdConstraintReader;
0071: import workbench.db.h2database.H2ConstraintReader;
0072: import workbench.db.h2database.H2SequenceReader;
0073: import workbench.db.oracle.OracleSequenceReader;
0074: import workbench.db.postgres.PostgresDataTypeResolver;
0075: import workbench.sql.formatter.SqlFormatter;
0076:
0077: /**
0078: * Retrieve meta data information from the database.
0079: * This class returns more information than the generic JDBC DatabaseMetadata.
0080: *
0081: * @author support@sql-workbench.net
0082: */
0083: public class DbMetadata {
0084: public static final String MVIEW_NAME = "MATERIALIZED VIEW";
0085: private String schemaTerm;
0086: private String catalogTerm;
0087: private String productName;
0088: private String dbId;
0089:
0090: protected MetaDataSqlManager metaSqlMgr;
0091: private DatabaseMetaData metaData;
0092: private WbConnection dbConnection;
0093:
0094: private OracleMetadata oracleMetaData;
0095:
0096: private ConstraintReader constraintReader;
0097: private DataTypeResolver dataTypeResolver;
0098: private SynonymReader synonymReader;
0099: private SequenceReader sequenceReader;
0100: private ProcedureReader procedureReader;
0101: private ErrorInformationReader errorInfoReader;
0102: private SchemaInformationReader schemaInfoReader;
0103: private IndexReader indexReader;
0104: private DDLFilter ddlFilter;
0105:
0106: private DbmsOutput oraOutput;
0107:
0108: private boolean isOracle;
0109: private boolean isPostgres;
0110: private boolean isFirstSql;
0111: private boolean isHsql;
0112: private boolean isFirebird;
0113: private boolean isSqlServer;
0114: private boolean isMySql;
0115: private boolean isApacheDerby;
0116: private boolean isExcel;
0117: private boolean isAccess;
0118:
0119: private boolean createInlineConstraints;
0120: private boolean useNullKeyword = true;
0121: private boolean columnsListInViewDefinitionAllowed = true;
0122:
0123: private String quoteCharacter;
0124: private SqlKeywordHandler keywordHandler;
0125:
0126: private Pattern selectIntoPattern = null;
0127:
0128: private String tableTypeName;
0129:
0130: private String[] tableTypesTable;
0131: private String[] tableTypesSelectable;
0132: private List schemasToIgnore;
0133: private List catalogsToIgnore;
0134:
0135: private DbSettings dbSettings;
0136:
0137: public DbMetadata(WbConnection aConnection) throws SQLException {
0138: this .dbConnection = aConnection;
0139: this .metaData = aConnection.getSqlConnection().getMetaData();
0140:
0141: try {
0142: this .schemaTerm = this .metaData.getSchemaTerm();
0143: } catch (SQLException e) {
0144: LogMgr
0145: .logWarning("DbMetadata.<init>",
0146: "Could not retrieve Schema term: "
0147: + e.getMessage());
0148: this .schemaTerm = "Schema";
0149: }
0150:
0151: try {
0152: this .catalogTerm = this .metaData.getCatalogTerm();
0153: } catch (SQLException e) {
0154: LogMgr.logWarning("DbMetadata.<init>",
0155: "Could not retrieve Catalog term: "
0156: + e.getMessage());
0157: this .catalogTerm = "Catalog";
0158: }
0159:
0160: // Some JDBC drivers do not return a value for getCatalogTerm() or getSchemaTerm()
0161: // and don't throw an Exception. This is to ensure that our getCatalogTerm() will
0162: // always return something usable.
0163: if (StringUtil.isEmptyString(this .schemaTerm))
0164: this .schemaTerm = "Schema";
0165: if (StringUtil.isEmptyString(this .catalogTerm))
0166: this .catalogTerm = "Catalog";
0167:
0168: try {
0169: this .productName = this .metaData.getDatabaseProductName();
0170: this .dbId = null;
0171: } catch (SQLException e) {
0172: LogMgr.logWarning("DbMetadata.<init>",
0173: "Could not retrieve Database Product name", e);
0174: this .productName = aConnection.getProfile()
0175: .getDriverclass();
0176: }
0177:
0178: String productLower = this .productName.toLowerCase();
0179:
0180: if (productLower.indexOf("oracle") > -1) {
0181: this .isOracle = true;
0182: this .oracleMetaData = new OracleMetadata(this .dbConnection);
0183: this .constraintReader = new OracleConstraintReader();
0184: this .synonymReader = new OracleSynonymReader();
0185: this .sequenceReader = new OracleSequenceReader(
0186: this .dbConnection);
0187: this .procedureReader = new OracleProcedureReader(
0188: this .dbConnection);
0189: this .errorInfoReader = this .oracleMetaData;
0190: this .dataTypeResolver = this .oracleMetaData;
0191: this .indexReader = new OracleIndexReader(this );
0192: } else if (productLower.indexOf("postgres") > -1) {
0193: this .isPostgres = true;
0194: this .constraintReader = new PostgresConstraintReader();
0195: this .sequenceReader = new PostgresSequenceReader(
0196: this .dbConnection);
0197: this .procedureReader = new PostgresProcedureReader(
0198: this .dbConnection);
0199: this .indexReader = new PostgresIndexReader(this );
0200: this .dataTypeResolver = new PostgresDataTypeResolver();
0201:
0202: // Starting with the version 8.2 the driver supports the dollar quoting
0203: // out of the box, so there is no need to use our own workaround
0204: if (!JdbcUtils.hasMiniumDriverVersion(dbConnection
0205: .getSqlConnection(), "8.2")) {
0206: this .ddlFilter = new PostgresDDLFilter();
0207: }
0208: } else if (productLower.indexOf("hsql") > -1) {
0209: this .isHsql = true;
0210: this .constraintReader = new HsqlConstraintReader(
0211: this .dbConnection.getSqlConnection());
0212: this .sequenceReader = new HsqlSequenceReader(
0213: this .dbConnection.getSqlConnection());
0214: this .columnsListInViewDefinitionAllowed = JdbcUtils
0215: .hasMinimumServerVersion(dbConnection, "1.8");
0216: } else if (productLower.indexOf("firebird") > -1) {
0217: this .isFirebird = true;
0218: this .constraintReader = new FirebirdConstraintReader();
0219: this .procedureReader = new FirebirdProcedureReader(
0220: this .dbConnection);
0221: // Jaybird 2.0 reports the Firebird version in the
0222: // productname. To ease the DBMS handling we'll use the same
0223: // product name that is reported with the 1.5 driver.
0224: // Otherwise the DBID would look something like:
0225: // firebird_2_0_wi-v2_0_1_12855_firebird_2_0_tcp__wallace__p10
0226: this .productName = "Firebird";
0227: } else if (productLower.indexOf("sql server") > -1) {
0228: this .isSqlServer = true;
0229: this .constraintReader = new SqlServerConstraintReader();
0230: boolean useJdbc = Settings.getInstance().getBoolProperty(
0231: "workbench.db.mssql.usejdbcprocreader", true);
0232: if (!useJdbc) {
0233: this .procedureReader = new SqlServerProcedureReader(
0234: this .dbConnection);
0235: }
0236: } else if (productLower.indexOf("db2") > -1) {
0237: this .synonymReader = new Db2SynonymReader();
0238: this .sequenceReader = new Db2SequenceReader(
0239: this .dbConnection);
0240: } else if (productLower.indexOf("adaptive server") > -1) {
0241: // this covers adaptive server Enterprise and Anywhere
0242: this .constraintReader = new ASAConstraintReader();
0243: } else if (productLower.indexOf("mysql") > -1) {
0244: this .procedureReader = new MySqlProcedureReader(
0245: this .dbConnection);
0246: this .isMySql = true;
0247: } else if (productLower.indexOf("cloudscape") > -1) {
0248: this .isApacheDerby = true;
0249: this .constraintReader = new DerbyConstraintReader();
0250: } else if (productLower.indexOf("derby") > -1) {
0251: this .isApacheDerby = true;
0252: this .constraintReader = new DerbyConstraintReader();
0253: this .synonymReader = new DerbySynonymReader(this );
0254: } else if (productLower.indexOf("ingres") > -1) {
0255: IngresMetadata imeta = new IngresMetadata(this .dbConnection
0256: .getSqlConnection());
0257: this .synonymReader = imeta;
0258: this .sequenceReader = imeta;
0259: } else if (productLower.indexOf("mckoi") > -1) {
0260: // McKoi reports the version in the database product name
0261: // which makes setting up the meta data stuff lookups
0262: // too complicated, so we'll strip the version info
0263: int pos = this .productName.indexOf('(');
0264: if (pos == -1)
0265: pos = this .productName.length() - 1;
0266: this .productName = this .productName.substring(0, pos)
0267: .trim();
0268: this .sequenceReader = new McKoiMetadata(this .dbConnection
0269: .getSqlConnection());
0270: } else if (productLower.indexOf("firstsql") > -1) {
0271: this .constraintReader = new FirstSqlMetadata();
0272: this .isFirstSql = true;
0273: } else if (productLower.indexOf("excel") > -1) {
0274: this .isExcel = true;
0275: } else if (productLower.indexOf("access") > -1) {
0276: this .isAccess = true;
0277: } else if (productLower.equals("h2")) {
0278: this .sequenceReader = new H2SequenceReader(
0279: this .dbConnection.getSqlConnection());
0280: this .constraintReader = new H2ConstraintReader();
0281: }
0282:
0283: // Use default implementations for non-JDBC supplied information
0284: if (this .procedureReader == null) {
0285: this .procedureReader = new JdbcProcedureReader(
0286: this .dbConnection);
0287: }
0288:
0289: if (this .indexReader == null) {
0290: this .indexReader = new JdbcIndexReader(this );
0291: }
0292:
0293: if (this .schemaInfoReader == null) {
0294: this .schemaInfoReader = new GenericSchemaInfoReader(this
0295: .getDbId());
0296: }
0297:
0298: if (this .dataTypeResolver == null) {
0299: this .dataTypeResolver = new DefaultDataTypeResolver();
0300: }
0301:
0302: try {
0303: this .quoteCharacter = this .metaData
0304: .getIdentifierQuoteString();
0305: } catch (Exception e) {
0306: this .quoteCharacter = null;
0307: }
0308: if (StringUtil.isEmptyString(quoteCharacter))
0309: this .quoteCharacter = "\"";
0310:
0311: this .dbSettings = new DbSettings(this .getDbId(),
0312: this .productName);
0313: Settings settings = Settings.getInstance();
0314: this .createInlineConstraints = settings
0315: .getServersWithInlineConstraints()
0316: .contains(productName);
0317: this .useNullKeyword = !settings.getServersWithNoNullKeywords()
0318: .contains(this .getDbId());
0319:
0320: this .metaSqlMgr = new MetaDataSqlManager(this .getProductName());
0321:
0322: tableTypeName = settings.getProperty(
0323: "workbench.db.basetype.table." + this .getDbId(),
0324: "TABLE");
0325: tableTypesTable = new String[] { tableTypeName };
0326:
0327: // The tableTypesSelectable array will be used
0328: // to fill the completion cache. In that case
0329: // we do not want system tables included (which
0330: // is done for the objectsWithData as that
0331: // drives the "Data" tab in the DbExplorer)
0332: Set<String> types = getObjectsWithData();
0333: List<String> realTypes = new ArrayList<String>(types.size());
0334:
0335: Iterator itr = types.iterator();
0336: for (String s : types) {
0337: if (s.toUpperCase().indexOf("SYSTEM") == -1) {
0338: realTypes.add(s);
0339: }
0340: }
0341: tableTypesSelectable = new String[realTypes.size()];
0342: int i = 0;
0343: for (String s : realTypes) {
0344: tableTypesSelectable[i++] = s.toUpperCase();
0345: }
0346:
0347: String pattern = Settings.getInstance().getProperty(
0348: "workbench.db." + getDbId() + ".selectinto.pattern",
0349: null);
0350: if (pattern != null) {
0351: try {
0352: this .selectIntoPattern = Pattern.compile(pattern,
0353: Pattern.CASE_INSENSITIVE);
0354: } catch (Exception e) {
0355: LogMgr
0356: .logError(
0357: "DbMetadata.<init>",
0358: "Incorrect Pattern for detecting SELECT ... INTO <new table> specified",
0359: e);
0360: this .selectIntoPattern = null;
0361: }
0362: }
0363: }
0364:
0365: public String getTableTypeName() {
0366: return tableTypeName;
0367: }
0368:
0369: public String getMViewTypeName() {
0370: return MVIEW_NAME;
0371: }
0372:
0373: public String getViewTypeName() {
0374: return "VIEW";
0375: }
0376:
0377: public DatabaseMetaData getJdbcMetadata() {
0378: return this .metaData;
0379: }
0380:
0381: public WbConnection getWbConnection() {
0382: return this .dbConnection;
0383: }
0384:
0385: public Connection getSqlConnection() {
0386: return this .dbConnection.getSqlConnection();
0387: }
0388:
0389: /**
0390: * Check if the given DB object type can contain data. i.e. if
0391: * a SELECT FROM can be run against this type
0392: */
0393: public boolean objectTypeCanContainData(String type) {
0394: if (type == null)
0395: return false;
0396: return getObjectsWithData().contains(type.toLowerCase());
0397: }
0398:
0399: private Set<String> getObjectsWithData() {
0400: Set<String> objectsWithData = new HashSet<String>(7);
0401: String keyPrefix = "workbench.db.objecttype.selectable.";
0402: String defValue = Settings.getInstance().getProperty(
0403: keyPrefix + "default", null);
0404: String types = Settings.getInstance().getProperty(
0405: keyPrefix + getDbId(), defValue);
0406:
0407: if (types == null) {
0408: objectsWithData.add("table");
0409: objectsWithData.add("view");
0410: objectsWithData.add("synonym");
0411: objectsWithData.add("system view");
0412: objectsWithData.add("system table");
0413: } else {
0414: List<String> l = StringUtil.stringToList(types
0415: .toLowerCase(), ",", true, true);
0416: objectsWithData.addAll(l);
0417: }
0418:
0419: if (this .isPostgres) {
0420: objectsWithData.add("sequence");
0421: }
0422:
0423: if (this .isOracle) {
0424: objectsWithData.add(MVIEW_NAME.toLowerCase());
0425: }
0426:
0427: return objectsWithData;
0428: }
0429:
0430: /**
0431: * Return the name of the DBMS as reported by the JDBC driver
0432: */
0433: public String getProductName() {
0434: return this .productName;
0435: }
0436:
0437: /**
0438: * Return a clean version of the productname that can be used
0439: * as the part of a properties key
0440: * @see #getProductName()
0441: */
0442: public String getDbId() {
0443: if (this .dbId == null) {
0444: this .dbId = this .productName.replaceAll(
0445: "[ \\(\\)\\[\\]\\/$,.'=\"]", "_").toLowerCase();
0446: // Use the same dbid for DB2/LINUX, DB2/NT, ...
0447: if (dbId.startsWith("db2") && productName.indexOf("/") > -1)
0448: dbId = "db2";
0449: LogMgr.logInfo("DbMetadata", "Using DBID=" + this .dbId);
0450: }
0451: return this .dbId;
0452: }
0453:
0454: public DbSettings getDbSettings() {
0455: return this .dbSettings;
0456: }
0457:
0458: /**
0459: * Returns true if the current DBMS supports a SELECT syntax
0460: * which creates a new table (e.g. SELECT .. INTO new_table FROM old_table)
0461: *
0462: * It simply checks if a regular expression has been defined to
0463: * detect this kind of statements
0464: *
0465: * @see #isSelectIntoNewTable(String)
0466: */
0467: public boolean supportsSelectIntoNewTable() {
0468: return this .selectIntoPattern != null;
0469: }
0470:
0471: /**
0472: * Checks if the given SQL string is actually some kind of table
0473: * creation "disguised" as a SELECT.
0474: * Whether a statement is identified as a SELECT into a new table
0475: * is defined through the regular expression that can be set for
0476: * the DBMS using the property:
0477: * <tt>workbench.sql.[dbid].selectinto.pattern</tt>
0478: *
0479: * This method returns true if a Regex has been defined and matches the given SQL
0480: */
0481: public boolean isSelectIntoNewTable(String sql) {
0482: if (this .selectIntoPattern == null)
0483: return false;
0484: if (sql == null || sql.length() == 0)
0485: return false;
0486: Matcher m = this .selectIntoPattern.matcher(sql);
0487: return m.find();
0488: }
0489:
0490: public boolean isMySql() {
0491: return this .isMySql;
0492: }
0493:
0494: public boolean isPostgres() {
0495: return this .isPostgres;
0496: }
0497:
0498: public boolean isOracle() {
0499: return this .isOracle;
0500: }
0501:
0502: public boolean isHsql() {
0503: return this .isHsql;
0504: }
0505:
0506: public boolean isFirebird() {
0507: return this .isFirebird;
0508: }
0509:
0510: public boolean isSqlServer() {
0511: return this .isSqlServer;
0512: }
0513:
0514: public boolean isApacheDerby() {
0515: return this .isApacheDerby;
0516: }
0517:
0518: /**
0519: * If a DDLFilter is registered for the current DBMS, this
0520: * method will replace all "problematic" characters in the
0521: * SQL string, and will return a String that the DBMS will
0522: * understand.
0523: * Currently this is only implemented for PostgreSQL to
0524: * mimic pgsql's $$ quoting for stored procedures
0525: *
0526: * @see workbench.db.postgres.PostgresDDLFilter
0527: */
0528: public String filterDDL(String sql) {
0529: if (this .ddlFilter == null)
0530: return sql;
0531: return this .ddlFilter.adjustDDL(sql);
0532: }
0533:
0534: public boolean ignoreSchema(String schema) {
0535: if (StringUtil.isEmptyString(schema))
0536: return true;
0537: if (schemasToIgnore == null) {
0538: String ids = Settings.getInstance().getProperty(
0539: "workbench.sql.ignoreschema." + this .getDbId(),
0540: null);
0541: if (ids != null) {
0542: schemasToIgnore = StringUtil.stringToList(ids, ",");
0543: } else {
0544: schemasToIgnore = Collections.EMPTY_LIST;
0545: }
0546: }
0547: return schemasToIgnore.contains("*")
0548: || schemasToIgnore.contains(schema);
0549: }
0550:
0551: /**
0552: * Check if the given {@link TableIdentifier} requires
0553: * the usage of the schema for a DML (select, insert, update, delete)
0554: * statement. By default this is not required for an Oracle
0555: * connetion where the schema is the current user.
0556: * For all other DBMS, the usage can be disabled by setting
0557: * a property in the configuration file
0558: */
0559: public boolean needSchemaInDML(TableIdentifier table) {
0560: try {
0561: String tblSchema = table.getSchema();
0562: if (ignoreSchema(tblSchema))
0563: return false;
0564:
0565: if (this .isOracle) {
0566: // The current schema can be changed in Oracle using ALTER SESSION
0567: // in that case the current user is still the one used to log-in
0568: // but the current schema is different, and we do need to qualify
0569: // objects with the schema.
0570: return !getCurrentSchema().equalsIgnoreCase(tblSchema);
0571: }
0572: } catch (Throwable th) {
0573: return false;
0574: }
0575: return true;
0576: }
0577:
0578: public boolean needCatalogInDML(TableIdentifier table) {
0579: if (this .isAccess)
0580: return true;
0581: if (!this .supportsCatalogs())
0582: return false;
0583: String cat = table.getCatalog();
0584: if (StringUtil.isEmptyString(cat))
0585: return false;
0586: String currentCat = getCurrentCatalog();
0587:
0588: if (this .isExcel) {
0589: // Excel puts the directory into the catalog
0590: // so we need to normalize the directory name
0591: File c1 = new File(cat);
0592: File c2 = new File(currentCat);
0593: if (c1.equals(c2))
0594: return false;
0595: return true;
0596: }
0597:
0598: if (StringUtil.isEmptyString(currentCat)) {
0599: return this .dbSettings.needsCatalogIfNoCurrent();
0600: }
0601: return !cat.equalsIgnoreCase(currentCat);
0602: }
0603:
0604: public boolean ignoreCatalog(String catalog) {
0605: if (catalog == null)
0606: return true;
0607: String c = getCurrentCatalog();
0608: if (c != null && c.equalsIgnoreCase(catalog))
0609: return true;
0610: if (catalogsToIgnore == null) {
0611: String cats = Settings.getInstance().getProperty(
0612: "workbench.sql.ignorecatalog." + this .getDbId(),
0613: null);
0614: if (cats != null) {
0615: catalogsToIgnore = StringUtil.stringToList(cats, ",");
0616: } else {
0617: catalogsToIgnore = Collections.EMPTY_LIST;
0618: }
0619: }
0620: return catalogsToIgnore.contains("*")
0621: || catalogsToIgnore.contains(catalog);
0622: }
0623:
0624: /**
0625: * Wrapper for DatabaseMetaData.supportsBatchUpdates() that throws
0626: * no exception. If any error occurs, false will be returned
0627: */
0628: public boolean supportsBatchUpdates() {
0629: try {
0630: return this .metaData.supportsBatchUpdates();
0631: } catch (SQLException e) {
0632: return false;
0633: }
0634: }
0635:
0636: public Set<String> getDbDataTypes() {
0637: SqlDataTypesHandler handler = new SqlDataTypesHandler(
0638: this .dbConnection.getSqlConnection(), this .getDbId());
0639: return handler.getDataTypes();
0640: }
0641:
0642: public Set<String> getDbFunctions() {
0643: Set<String> dbFunctions = new HashSet<String>();
0644: try {
0645: String funcs = this .metaData.getSystemFunctions();
0646: this .addStringList(dbFunctions, funcs);
0647:
0648: funcs = this .metaData.getStringFunctions();
0649: this .addStringList(dbFunctions, funcs);
0650:
0651: funcs = this .metaData.getNumericFunctions();
0652: this .addStringList(dbFunctions, funcs);
0653:
0654: funcs = this .metaData.getTimeDateFunctions();
0655: this .addStringList(dbFunctions, funcs);
0656:
0657: // Add Standard ANSI SQL Functions
0658: this .addStringList(dbFunctions, Settings.getInstance()
0659: .getProperty("workbench.db.syntax.functions",
0660: "COUNT,AVG,SUM,MAX,MIN"));
0661:
0662: // Add additional DB specific functions
0663: this .addStringList(dbFunctions, Settings.getInstance()
0664: .getProperty(
0665: "workbench.db." + getDbId()
0666: + ".syntax.functions", null));
0667: } catch (Exception e) {
0668: LogMgr.logWarning("DbMetadata.getDbFunctions()",
0669: "Error retrieving function list from DB: "
0670: + e.getMessage());
0671: }
0672: return dbFunctions;
0673: }
0674:
0675: private void addStringList(Set<String> target, String list) {
0676: if (list == null)
0677: return;
0678: List<String> tokens = StringUtil.stringToList(list, ",", true,
0679: true, false);
0680: Iterator itr = tokens.iterator();
0681: while (itr.hasNext()) {
0682: String keyword = (String) itr.next();
0683: target.add(keyword.toUpperCase().trim());
0684: }
0685: }
0686:
0687: /**
0688: * Returns the type of the passed TableIdentifier. This could
0689: * be VIEW, TABLE, SYNONYM, ...
0690: * If the JDBC driver does not return the object through the getTables()
0691: * method, null is returned, otherwise the value reported in TABLE_TYPE
0692: * If there is more than object with the same name but different types
0693: * (is there a DB that supports that???) than the first object found
0694: * will be returned.
0695: * @see #getTables(String, String, String, String[])
0696: */
0697: public String getObjectType(TableIdentifier table) {
0698: String type = null;
0699: try {
0700: TableIdentifier tbl = table.createCopy();
0701: tbl.adjustCase(this .dbConnection);
0702: DataStore ds = getTables(tbl.getCatalog(), tbl.getSchema(),
0703: tbl.getTableName(), null);
0704: if (ds.getRowCount() > 0) {
0705: type = ds.getValueAsString(0,
0706: COLUMN_IDX_TABLE_LIST_TYPE);
0707: }
0708: } catch (Exception e) {
0709: type = null;
0710: }
0711: return type;
0712: }
0713:
0714: public CharSequence getExtendedViewSource(TableIdentifier tbl,
0715: boolean includeDrop) throws SQLException {
0716: return this .getExtendedViewSource(tbl, null, includeDrop);
0717: }
0718:
0719: /**
0720: * Returns a complete SQL statement to (re)create the given view.
0721: */
0722: public CharSequence getExtendedViewSource(TableIdentifier view,
0723: DataStore viewTableDefinition, boolean includeDrop)
0724: throws SQLException {
0725: GetMetaDataSql sql = metaSqlMgr.getViewSourceSql();
0726: if (sql == null) {
0727: SourceStatementsHelp help = new SourceStatementsHelp();
0728: return help.explainMissingViewSourceSql(this
0729: .getProductName());
0730: }
0731:
0732: if (viewTableDefinition == null) {
0733: viewTableDefinition = this .getTableDefinition(view);
0734: }
0735: CharSequence source = this .getViewSource(view);
0736:
0737: if (StringUtil.isEmptyString(source))
0738: return StringUtil.EMPTY_STRING;
0739:
0740: StringBuilder result = new StringBuilder(source.length() + 100);
0741:
0742: String lineEnding = Settings.getInstance()
0743: .getInternalEditorLineEnding();
0744: String verb = SqlUtil.getSqlVerb(source);
0745:
0746: // ThinkSQL and DB2 return the full CREATE VIEW statement
0747: if (verb.equalsIgnoreCase("CREATE")) {
0748: if (includeDrop) {
0749: String type = SqlUtil.getCreateType(source);
0750: result.append("DROP ");
0751: result.append(type);
0752: result.append(' ');
0753: result.append(view.getTableName());
0754: result.append(';');
0755: result.append(lineEnding);
0756: result.append(lineEnding);
0757: }
0758: result.append(source);
0759: if (this .dbSettings.ddlNeedsCommit()) {
0760: result.append(lineEnding);
0761: result.append("COMMIT;");
0762: result.append(lineEnding);
0763: }
0764: return result.toString();
0765: }
0766:
0767: result.append(generateCreateObject(includeDrop, view.getType(),
0768: view.getTableName()));
0769:
0770: if (columnsListInViewDefinitionAllowed
0771: && !MVIEW_NAME.equalsIgnoreCase(view.getType())) {
0772: result.append(lineEnding + "(" + lineEnding);
0773: int rows = viewTableDefinition.getRowCount();
0774: for (int i = 0; i < rows; i++) {
0775: String colName = viewTableDefinition
0776: .getValueAsString(
0777: i,
0778: DbMetadata.COLUMN_IDX_TABLE_DEFINITION_COL_NAME);
0779: result.append(" ");
0780: result.append(quoteObjectname(colName));
0781: if (i < rows - 1) {
0782: result.append(',');
0783: result.append(lineEnding);
0784: }
0785: }
0786: result.append(lineEnding + ")");
0787: }
0788:
0789: result.append(lineEnding + "AS " + lineEnding);
0790: result.append(source);
0791: result.append(lineEnding);
0792:
0793: // Oracle and MS SQL Server support materialized views. For those
0794: // the index definitions are of interest as well.
0795: DataStore indexInfo = this .getTableIndexInformation(view);
0796: if (indexInfo.getRowCount() > 0) {
0797: StringBuilder idx = this .indexReader.getIndexSource(view,
0798: indexInfo, null);
0799: if (idx.length() > 0) {
0800: result.append(lineEnding);
0801: result.append(lineEnding);
0802: result.append(idx);
0803: result.append(lineEnding);
0804: }
0805: }
0806:
0807: if (this .dbSettings.ddlNeedsCommit()) {
0808: result.append("COMMIT;");
0809: }
0810: return result;
0811: }
0812:
0813: /**
0814: * Return the source of a view definition as it is stored in the database.
0815: * Usually (depending on how the meta data is stored in the database) the DBMS
0816: * only stores the underlying SELECT statement, and that will be returned by this method.
0817: * To create a complete SQL to re-create a view, use {@link #getExtendedViewSource(TableIdentifier, DataStore, boolean)}
0818: *
0819: * @return the view source as stored in the database.
0820: */
0821: public CharSequence getViewSource(TableIdentifier viewId) {
0822: if (viewId == null)
0823: return null;
0824:
0825: if (this .isOracle
0826: && MVIEW_NAME.equalsIgnoreCase(viewId.getType())) {
0827: return oracleMetaData.getSnapshotSource(viewId);
0828: }
0829:
0830: StringBuilder source = new StringBuilder(500);
0831: Statement stmt = null;
0832: ResultSet rs = null;
0833: try {
0834: GetMetaDataSql sql = metaSqlMgr.getViewSourceSql();
0835: if (sql == null)
0836: return StringUtil.EMPTY_STRING;
0837: TableIdentifier tbl = viewId.createCopy();
0838: tbl.adjustCase(this .dbConnection);
0839: sql.setSchema(tbl.getSchema());
0840: sql.setObjectName(tbl.getTableName());
0841: sql.setCatalog(tbl.getCatalog());
0842: stmt = this .dbConnection.createStatementForQuery();
0843: String query = this .adjustHsqlQuery(sql.getSql());
0844: if (Settings.getInstance().getDebugMetadataSql()) {
0845: LogMgr.logInfo("DbMetadata.getViewSource()",
0846: "Using query=\n" + query);
0847: }
0848: rs = stmt.executeQuery(query);
0849: while (rs.next()) {
0850: String line = rs.getString(1);
0851: if (line != null) {
0852: source.append(line);
0853: }
0854: }
0855: StringUtil.trimTrailingWhitespace(source);
0856: if (this .dbSettings.getFormatViewSource()) {
0857: SqlFormatter f = new SqlFormatter(source);
0858: source = new StringBuilder(f.getFormattedSql());
0859: }
0860: if (!StringUtil.endsWith(source, ';'))
0861: source.append(';');
0862: source.append(Settings.getInstance()
0863: .getInternalEditorLineEnding());
0864: } catch (Exception e) {
0865: LogMgr.logWarning("DbMetadata.getViewSource()",
0866: "Could not retrieve view definition for "
0867: + viewId.getTableExpression(), e);
0868: source = new StringBuilder(ExceptionUtil.getDisplay(e));
0869: if (this .isPostgres)
0870: try {
0871: this .dbConnection.rollback();
0872: } catch (Throwable th) {
0873: }
0874: } finally {
0875: SqlUtil.closeAll(rs, stmt);
0876: }
0877: return source;
0878: }
0879:
0880: private StringBuilder generateCreateObject(boolean includeDrop,
0881: String type, String name) {
0882: StringBuilder result = new StringBuilder();
0883: boolean replaced = false;
0884:
0885: String prefix = "workbench.db.";
0886: String suffix = "." + type.toLowerCase() + ".sql."
0887: + this .getDbId();
0888:
0889: String replace = Settings.getInstance().getProperty(
0890: prefix + "replace" + suffix, null);
0891: if (replace != null) {
0892: replace = StringUtil.replace(replace, "%name%",
0893: quoteObjectname(name));
0894: result.append(replace);
0895: replaced = true;
0896: }
0897:
0898: if (includeDrop && !replaced) {
0899: String drop = Settings.getInstance().getProperty(
0900: prefix + "drop" + suffix, null);
0901: if (drop == null) {
0902: result.append("DROP ");
0903: result.append(type.toUpperCase());
0904: result.append(' ');
0905: result.append(quoteObjectname(name));
0906: String cascade = this .dbSettings
0907: .getCascadeConstraintsVerb(type);
0908: if (cascade != null) {
0909: result.append(' ');
0910: result.append(cascade);
0911: }
0912: result.append(";\n");
0913: } else {
0914: drop = StringUtil.replace(drop, "%name%",
0915: quoteObjectname(name));
0916: result.append(drop);
0917: }
0918: result.append('\n');
0919: }
0920:
0921: if (!replaced) {
0922: String create = Settings.getInstance().getProperty(
0923: prefix + "create" + suffix, null);
0924: if (create == null) {
0925: result.append("CREATE ");
0926: result.append(type.toUpperCase());
0927: result.append(' ');
0928: result.append(quoteObjectname(name));
0929: } else {
0930: create = StringUtil.replace(create, "%name%",
0931: quoteObjectname(name));
0932: result.append(create);
0933: }
0934: }
0935: return result;
0936: }
0937:
0938: public CharSequence getProcedureSource(String aCatalog,
0939: String aSchema, String aProcname, int type) {
0940: try {
0941: ProcedureDefinition def = new ProcedureDefinition(aCatalog,
0942: aSchema, aProcname, type);
0943: readProcedureSource(def);
0944: return def.getSource();
0945: } catch (NoConfigException e) {
0946: SourceStatementsHelp help = new SourceStatementsHelp();
0947: return help.explainMissingProcSourceSql(this
0948: .getProductName());
0949: }
0950: }
0951:
0952: public void readProcedureSource(ProcedureDefinition def)
0953: throws NoConfigException {
0954: if (procedureReader != null) {
0955: this .procedureReader.readProcedureSource(def);
0956: }
0957: }
0958:
0959: private void initKeywordHandler() {
0960: this .keywordHandler = new SqlKeywordHandler(this .dbConnection
0961: .getSqlConnection(), this .getDbId());
0962: }
0963:
0964: public boolean isKeyword(String name) {
0965: if (this .keywordHandler == null)
0966: this .initKeywordHandler();
0967: return this .keywordHandler.isKeyword(name);
0968: }
0969:
0970: public Collection<String> getSqlKeywords() {
0971: if (this .keywordHandler == null)
0972: this .initKeywordHandler();
0973: return this .keywordHandler.getSqlKeywords();
0974: }
0975:
0976: public String quoteObjectname(String aName) {
0977: return quoteObjectname(aName, false);
0978: }
0979:
0980: /**
0981: * Encloses the given object name in double quotes if necessary.
0982: * Quoting of names is necessary if the name is a reserved word in the
0983: * database. To check if the given name is a keyword, it is compared
0984: * to the words returned by getSQLKeywords().
0985: *
0986: * If the given name is not a keyword, {@link workbench.util.SqlUtil#quoteObjectname(String)}
0987: * will be called to check if the name contains special characters which require
0988: * double quotes around the object name.
0989: *
0990: * For Oracle and HSQL strings starting with a digit will
0991: * always be quoted.
0992: */
0993: public String quoteObjectname(String aName, boolean quoteAlways) {
0994: if (aName == null)
0995: return null;
0996: if (aName.length() == 0)
0997: return aName;
0998:
0999: // already quoted?
1000: if (aName.startsWith(this .quoteCharacter))
1001: return aName;
1002:
1003: if (this .dbSettings.neverQuoteObjects())
1004: return StringUtil.trimQuotes(aName);
1005:
1006: boolean needQuote = quoteAlways;
1007:
1008: // Excel does not support the standard rules for SQL identifiers
1009: // Basically anything that does not contain only characters needs to
1010: // be quoted.
1011: if (this .isExcel) {
1012: Pattern chars = Pattern.compile("[A-Za-z0-9]*");
1013: Matcher m = chars.matcher(aName);
1014: needQuote = !m.matches();
1015: }
1016:
1017: try {
1018:
1019: if (!needQuote && !this .storesMixedCaseIdentifiers()) {
1020: if (this .storesUpperCaseIdentifiers()
1021: && !StringUtil.isUpperCase(aName)) {
1022: needQuote = true;
1023: } else if (this .storesLowerCaseIdentifiers()
1024: && !StringUtil.isLowerCase(aName)) {
1025: needQuote = true;
1026: }
1027: }
1028:
1029: if (needQuote || isKeyword(aName)) {
1030: StringBuilder result = new StringBuilder(
1031: aName.length() + 4);
1032: result.append(this .quoteCharacter);
1033: result.append(aName.trim());
1034: result.append(this .quoteCharacter);
1035: return result.toString();
1036: }
1037:
1038: } catch (Exception e) {
1039: LogMgr.logWarning("DbMetadata.quoteObjectName()",
1040: "Error when retrieving DB information", e);
1041: }
1042:
1043: // if it is not a keyword, we have to check for special characters such
1044: // as a space, $, digits at the beginning etc
1045: return SqlUtil.quoteObjectname(aName);
1046: }
1047:
1048: /**
1049: * Adjusts the case of the given schema name to the
1050: * case in which the server stores schema names.
1051: *
1052: * This is needed e.g. when the user types a
1053: * table name, and that value is used to retrieve
1054: * the table definition.
1055: *
1056: * @param schema the schema name to adjust
1057: * @return the adjusted schema name
1058: */
1059: public String adjustSchemaNameCase(String schema) {
1060: if (schema == null)
1061: return null;
1062: schema = StringUtil.trimQuotes(schema).trim();
1063: try {
1064: if (this .storesUpperCaseSchemas()) {
1065: return schema.toUpperCase();
1066: } else if (this .storesLowerCaseSchemas()) {
1067: return schema.toLowerCase();
1068: }
1069: } catch (Exception e) {
1070: }
1071: return schema;
1072: }
1073:
1074: /**
1075: * Returns true if the given object name needs quoting due
1076: * to mixed case writing or because the case of the name
1077: * does not match the case in which the database stores its objects
1078: */
1079: public boolean isDefaultCase(String name) {
1080: if (name == null)
1081: return true;
1082:
1083: if (supportsMixedCaseIdentifiers())
1084: return true;
1085:
1086: boolean isUpper = StringUtil.isUpperCase(name);
1087: boolean isLower = StringUtil.isLowerCase(name);
1088:
1089: if (isUpper && storesUpperCaseIdentifiers())
1090: return true;
1091: if (isLower && storesLowerCaseIdentifiers())
1092: return true;
1093:
1094: return false;
1095: }
1096:
1097: /**
1098: * Adjusts the case of the given object to the
1099: * case in which the server stores objects
1100: * This is needed e.g. when the user types a
1101: * table name, and that value is used to retrieve
1102: * the table definition. Usually the getColumns()
1103: * method is case sensitiv.
1104: *
1105: * @param name the object name to adjust
1106: * @return the adjusted object name
1107: */
1108: public String adjustObjectnameCase(String name) {
1109: if (name == null)
1110: return null;
1111: // if we have quotes, keep them...
1112: if (name.indexOf("\"") > -1)
1113: return name.trim();
1114:
1115: try {
1116: if (this .storesMixedCaseIdentifiers()) {
1117: return name;
1118: } else if (this .storesUpperCaseIdentifiers()) {
1119: return name.toUpperCase();
1120: } else if (this .storesLowerCaseIdentifiers()) {
1121: return name.toLowerCase();
1122: }
1123: } catch (Exception e) {
1124: }
1125: return name.trim();
1126: }
1127:
1128: /**
1129: * Returns the current schema.
1130: */
1131: public String getCurrentSchema() {
1132: if (this .schemaInfoReader != null) {
1133: return this .schemaInfoReader
1134: .getCurrentSchema(this .dbConnection);
1135: }
1136: return null;
1137: }
1138:
1139: /**
1140: * Returns the schema that should be used for the current user
1141: * This essential call {@link #getCurrentSchema()}. The method
1142: * then checks if the schema should be ignored for the current
1143: * dbms by calling {@link #ignoreSchema(String)}. If the
1144: * Schema should not be ignored, the it's returned, otherwise
1145: * the method will return null
1146: */
1147: public String getSchemaToUse() {
1148: String schema = this .getCurrentSchema();
1149: if (schema == null)
1150: return null;
1151: if (this .ignoreSchema(schema))
1152: return null;
1153: return schema;
1154: }
1155:
1156: /**
1157: * The column index of the column in the DataStore returned by getTables()
1158: * the stores the table's name
1159: */
1160: public final static int COLUMN_IDX_TABLE_LIST_NAME = 0;
1161:
1162: /**
1163: * The column index of the column in the DataStore returned by getTables()
1164: * that stores the table's type. The available types can be retrieved
1165: * using {@link #getTableTypes()}
1166: */
1167: public final static int COLUMN_IDX_TABLE_LIST_TYPE = 1;
1168:
1169: /**
1170: * The column index of the column in the DataStore returned by getTables()
1171: * the stores the table's catalog
1172: */
1173: public final static int COLUMN_IDX_TABLE_LIST_CATALOG = 2;
1174:
1175: /**
1176: * The column index of the column in the DataStore returned by getTables()
1177: * the stores the table's schema
1178: */
1179: public final static int COLUMN_IDX_TABLE_LIST_SCHEMA = 3;
1180:
1181: /**
1182: * The column index of the column in the DataStore returned by getTables()
1183: * the stores the table's comment
1184: */
1185: public final static int COLUMN_IDX_TABLE_LIST_REMARKS = 4;
1186:
1187: public String getTableType(TableIdentifier table)
1188: throws SQLException {
1189: TableIdentifier tbl = table.createCopy();
1190: tbl.adjustCase(this .dbConnection);
1191: DataStore ds = getTables(tbl.getCatalog(), tbl.getSchema(), tbl
1192: .getTableName(), null);
1193: if (ds == null)
1194: return this .tableTypeName;
1195: if (ds.getRowCount() != 1)
1196: return null;
1197: return ds.getValueAsString(0, COLUMN_IDX_TABLE_LIST_TYPE);
1198: }
1199:
1200: public DataStore getTables() throws SQLException {
1201: String user = this .getCurrentSchema();
1202: return this .getTables(null, user, (String[]) null);
1203: }
1204:
1205: public DataStore getTables(String aCatalog, String aSchema,
1206: String[] types) throws SQLException {
1207: return getTables(aCatalog, aSchema, null, types);
1208: }
1209:
1210: public DataStore getTables(String aCatalog, String aSchema,
1211: String tables, String[] types) throws SQLException {
1212: if ("*".equals(aSchema) || "%".equals(aSchema))
1213: aSchema = null;
1214: if ("*".equals(tables) || "%".equals(tables))
1215: tables = null;
1216:
1217: if (aSchema != null)
1218: aSchema = StringUtil.replace(aSchema, "*", "%");
1219: if (tables != null)
1220: tables = StringUtil.replace(tables, "*", "%");
1221: String[] cols = new String[] { "NAME", "TYPE",
1222: catalogTerm.toUpperCase(), schemaTerm.toUpperCase(),
1223: "REMARKS" };
1224: int coltypes[] = { Types.VARCHAR, Types.VARCHAR, Types.VARCHAR,
1225: Types.VARCHAR, Types.VARCHAR };
1226: int sizes[] = { 30, 12, 10, 10, 20 };
1227:
1228: DataStore result = new DataStore(cols, coltypes, sizes);
1229:
1230: boolean sequencesReturned = false;
1231: boolean checkOracleSnapshots = (isOracle
1232: && Settings.getInstance().getBoolProperty(
1233: "workbench.db.oracle.detectsnapshots", true) && typeIncluded(
1234: "TABLE", types));
1235: boolean synRetrieved = false;
1236:
1237: String excludeSynsRegex = Settings.getInstance()
1238: .getProperty(
1239: "workbench.db." + getDbId()
1240: + ".exclude.synonyms", null);
1241: Pattern synPattern = null;
1242: if (typeIncluded("SYNONYM", types) && excludeSynsRegex != null) {
1243: try {
1244: synPattern = Pattern.compile(excludeSynsRegex);
1245: } catch (Exception e) {
1246: LogMgr
1247: .logError(
1248: "DbMetadata.getTables()",
1249: "Invalid RegEx for excluding public synonyms specified. RegEx ignored",
1250: e);
1251: synPattern = null;
1252: }
1253: }
1254:
1255: String excludeTablesRegex = Settings.getInstance().getProperty(
1256: "workbench.db." + getDbId() + ".exclude.tables", null);
1257: Pattern excludeTablePattern = null;
1258: if (excludeTablesRegex != null && typeIncluded("TABLE", types)) {
1259: try {
1260: excludeTablePattern = Pattern
1261: .compile(excludeTablesRegex);
1262: } catch (Exception e) {
1263: LogMgr.logError("DbMetadata.getTables()",
1264: "Invalid RegEx for excluding tables. RegEx '"
1265: + excludeTablesRegex + "' ignored", e);
1266: excludeTablePattern = null;
1267: }
1268: LogMgr.logInfo("DbMetadata.getTables()",
1269: "Excluding tables that match the following regex: "
1270: + excludeTablesRegex);
1271: }
1272:
1273: if (isPostgres && types == null) {
1274: // The current PG drivers to not adhere to the JDBC javadocs
1275: // and return nothing when passing null for the types
1276: // so we retrieve all possible types, and pass them
1277: // as this is the meaning of "null" for the types parameter
1278: Collection<String> typeList = this .getTableTypes();
1279: types = StringUtil.toArray(typeList);
1280: }
1281:
1282: Set snapshotList = Collections.EMPTY_SET;
1283: if (checkOracleSnapshots) {
1284: snapshotList = this .oracleMetaData.getSnapshots(aSchema);
1285: }
1286:
1287: boolean hideIndexes = hideIndexes();
1288:
1289: ResultSet tableRs = null;
1290: try {
1291: tableRs = this .metaData.getTables(StringUtil
1292: .trimQuotes(aCatalog), StringUtil
1293: .trimQuotes(aSchema),
1294: StringUtil.trimQuotes(tables), types);
1295: if (tableRs == null) {
1296: LogMgr
1297: .logError(
1298: "DbMetadata.getTables()",
1299: "Driver returned a NULL ResultSet from getTables()",
1300: null);
1301: return result;
1302: }
1303:
1304: while (tableRs.next()) {
1305: String cat = tableRs.getString(1);
1306: String schem = tableRs.getString(2);
1307: String name = tableRs.getString(3);
1308: String ttype = tableRs.getString(4);
1309: if (name == null)
1310: continue;
1311:
1312: // filter out "internal" synonyms for Oracle
1313: if (synPattern != null) {
1314: //if (name.indexOf('/') > -1) continue;
1315: Matcher m = synPattern.matcher(name);
1316: if (m.matches())
1317: continue;
1318: }
1319:
1320: // prevent duplicate retrieval of SYNONYMS if the driver
1321: // returns them already, but the Settings have enabled
1322: // Synonym retrieval as well
1323: // (e.g. because an upgraded Driver now returns the synonyms)
1324: if (!synRetrieved && "SYNONYM".equals(ttype)) {
1325: synRetrieved = true;
1326: }
1327:
1328: if (excludeTablePattern != null
1329: && ttype.equalsIgnoreCase("TABLE")) {
1330: Matcher m = excludeTablePattern.matcher(name);
1331: if (m.matches())
1332: continue;
1333: }
1334:
1335: if (hideIndexes && isIndexType(ttype))
1336: continue;
1337:
1338: if (checkOracleSnapshots) {
1339: StringBuilder t = new StringBuilder(30);
1340: t.append(schem);
1341: t.append('.');
1342: t.append(name);
1343: if (snapshotList.contains(t.toString())) {
1344: ttype = MVIEW_NAME;
1345: }
1346: }
1347:
1348: String rem = tableRs.getString(5);
1349: int row = result.addRow();
1350: result.setValue(row, COLUMN_IDX_TABLE_LIST_NAME, name);
1351: result.setValue(row, COLUMN_IDX_TABLE_LIST_TYPE, ttype);
1352: result
1353: .setValue(row, COLUMN_IDX_TABLE_LIST_CATALOG,
1354: cat);
1355: result.setValue(row, COLUMN_IDX_TABLE_LIST_SCHEMA,
1356: schem);
1357: result
1358: .setValue(row, COLUMN_IDX_TABLE_LIST_REMARKS,
1359: rem);
1360: if (!sequencesReturned && "SEQUENCE".equals(ttype))
1361: sequencesReturned = true;
1362: }
1363: } finally {
1364: SqlUtil.closeResult(tableRs);
1365: }
1366:
1367: if (this .sequenceReader != null
1368: && typeIncluded("SEQUENCE", types)
1369: && Settings.getInstance().getBoolProperty(
1370: "workbench.db." + this .getDbId()
1371: + ".retrieve_sequences", true)
1372: && !sequencesReturned) {
1373: List<String> seq = this .sequenceReader
1374: .getSequenceList(aSchema);
1375: for (String seqName : seq) {
1376: int row = result.addRow();
1377:
1378: result.setValue(row, COLUMN_IDX_TABLE_LIST_NAME,
1379: seqName);
1380: result.setValue(row, COLUMN_IDX_TABLE_LIST_TYPE,
1381: "SEQUENCE");
1382: result.setValue(row, COLUMN_IDX_TABLE_LIST_CATALOG,
1383: null);
1384: result.setValue(row, COLUMN_IDX_TABLE_LIST_SCHEMA,
1385: aSchema);
1386: result.setValue(row, COLUMN_IDX_TABLE_LIST_REMARKS,
1387: null);
1388: }
1389: }
1390:
1391: boolean retrieveSyns = (this .synonymReader != null && Settings
1392: .getInstance().getBoolProperty(
1393: "workbench.db." + this .getDbId()
1394: + ".retrieve_synonyms", false));
1395: if (retrieveSyns && !synRetrieved
1396: && typeIncluded("SYNONYM", types)) {
1397: LogMgr.logDebug("DbMetadata.getTables()",
1398: "Retrieving synonyms...");
1399: List<String> syns = this .synonymReader.getSynonymList(
1400: this .dbConnection.getSqlConnection(), aSchema);
1401: for (String synName : syns) {
1402: int row = result.addRow();
1403:
1404: result.setValue(row, COLUMN_IDX_TABLE_LIST_NAME,
1405: synName);
1406: result.setValue(row, COLUMN_IDX_TABLE_LIST_TYPE,
1407: "SYNONYM");
1408: result.setValue(row, COLUMN_IDX_TABLE_LIST_CATALOG,
1409: null);
1410: result.setValue(row, COLUMN_IDX_TABLE_LIST_SCHEMA,
1411: aSchema);
1412: result.setValue(row, COLUMN_IDX_TABLE_LIST_REMARKS,
1413: null);
1414: }
1415: }
1416: return result;
1417: }
1418:
1419: private boolean typeIncluded(String type, String[] types) {
1420: if (types == null)
1421: return true;
1422: if (type == null)
1423: return false;
1424: int l = types.length;
1425: for (int i = 0; i < l; i++) {
1426: if (types[i].equals("*"))
1427: return true;
1428: if (type.equalsIgnoreCase(types[i]))
1429: return true;
1430: }
1431: return false;
1432: }
1433:
1434: /**
1435: * Check if the given table exists in the database
1436: */
1437: public boolean tableExists(TableIdentifier aTable) {
1438: return objectExists(aTable, tableTypesTable);
1439: }
1440:
1441: public boolean objectExists(TableIdentifier aTable, String type) {
1442: String[] types = null;
1443: if (type != null) {
1444: types = new String[] { type };
1445: }
1446: return objectExists(aTable, types);
1447: }
1448:
1449: public TableIdentifier findTable(TableIdentifier tbl) {
1450: if (tbl == null)
1451: return null;
1452:
1453: ResultSet rs = null;
1454: TableIdentifier result = null;
1455: TableIdentifier table = tbl.createCopy();
1456: table.adjustCase(dbConnection);
1457: try {
1458: rs = this .metaData.getTables(table.getRawCatalog(), table
1459: .getRawSchema(), table.getRawTableName(),
1460: tableTypesTable);
1461: if (rs.next()) {
1462: result = new TableIdentifier(rs.getString(1), rs
1463: .getString(2), rs.getString(3));
1464: }
1465: } catch (Exception e) {
1466: LogMgr.logError("DbMetadata.tableExists()",
1467: "Error checking table existence", e);
1468: } finally {
1469: SqlUtil.closeResult(rs);
1470: }
1471: return result;
1472: }
1473:
1474: public boolean objectExists(TableIdentifier aTable, String[] types) {
1475: if (aTable == null)
1476: return false;
1477: boolean exists = false;
1478: ResultSet rs = null;
1479: TableIdentifier tbl = aTable.createCopy();
1480: try {
1481: tbl.adjustCase(this .dbConnection);
1482: String c = tbl.getRawCatalog();
1483: String s = tbl.getRawSchema();
1484: String t = tbl.getRawTableName();
1485: rs = this .metaData.getTables(c, s, t, types);
1486: exists = rs.next();
1487: } catch (Exception e) {
1488: LogMgr.logError("DbMetadata.tableExists()",
1489: "Error checking table existence", e);
1490: } finally {
1491: SqlUtil.closeResult(rs);
1492: }
1493: return exists;
1494: }
1495:
1496: protected boolean supportsMixedCaseIdentifiers() {
1497: try {
1498: return this .metaData.supportsMixedCaseIdentifiers();
1499: } catch (Exception e) {
1500: return false;
1501: }
1502: }
1503:
1504: protected boolean supportsMixedCaseQuotedIdentifiers() {
1505: try {
1506: return this .metaData.supportsMixedCaseQuotedIdentifiers();
1507: } catch (Exception e) {
1508: return false;
1509: }
1510: }
1511:
1512: /**
1513: * Returns true if the server stores identifiers in mixed case.
1514: * Usually this is delegated to the JDBC driver, but as some drivers
1515: * (e.g. Frontbase) implement this incorrectly, this can be overriden
1516: * in workbench.settings with the property:
1517: * workbench.db.[dbid].objectname.case
1518: */
1519: public boolean storesMixedCaseIdentifiers() {
1520: IdentifierCase ocase = this .dbSettings.getObjectNameCase();
1521: if (ocase != IdentifierCase.unknown) {
1522: return ocase == IdentifierCase.mixed;
1523: }
1524: try {
1525: boolean upper = this .metaData.storesUpperCaseIdentifiers();
1526: boolean lower = this .metaData.storesLowerCaseIdentifiers();
1527: boolean mixed = this .metaData.storesMixedCaseIdentifiers();
1528:
1529: return mixed || (upper && lower);
1530: } catch (SQLException e) {
1531: return false;
1532: }
1533: }
1534:
1535: public boolean storesUpperCaseSchemas() {
1536: IdentifierCase ocase = this .dbSettings.getSchemaNameCase();
1537: if (ocase == IdentifierCase.unknown) {
1538: return storesUpperCaseIdentifiers();
1539: }
1540: return ocase == IdentifierCase.upper;
1541: }
1542:
1543: public boolean storesLowerCaseSchemas() {
1544: IdentifierCase ocase = this .dbSettings.getSchemaNameCase();
1545: if (ocase == IdentifierCase.unknown) {
1546: return storesLowerCaseIdentifiers();
1547: }
1548: return ocase == IdentifierCase.lower;
1549: }
1550:
1551: public boolean isCaseSensitive() {
1552: try {
1553: // According to the JDBC docs, supportsMixedCaseIdentifiers()
1554: // should only return true if the server is case sensitive...
1555: return this .metaData.supportsMixedCaseIdentifiers();
1556: } catch (SQLException ex) {
1557: LogMgr
1558: .logWarning(
1559: "DbMetadata.isCaseSensitive()",
1560: "Error when calling supportsMixedCaseIdentifiers()",
1561: ex);
1562: // Standard SQL identifiers are not case sensitive.
1563: return false;
1564: }
1565: }
1566:
1567: /**
1568: * Returns true if the server stores identifiers in lower case.
1569: * Usually this is delegated to the JDBC driver, but as some drivers
1570: * (e.g. Frontbase) implement this incorrectly, this can be overriden
1571: * in workbench.settings with the property:
1572: * workbench.db.objectname.case.<dbid>
1573: */
1574: public boolean storesLowerCaseIdentifiers() {
1575: IdentifierCase ocase = this .dbSettings.getObjectNameCase();
1576: if (ocase != IdentifierCase.unknown) {
1577: return ocase == IdentifierCase.lower;
1578: }
1579: try {
1580: return this .metaData.storesLowerCaseIdentifiers();
1581: } catch (SQLException e) {
1582: return false;
1583: }
1584: }
1585:
1586: /**
1587: * Returns true if the server stores identifiers in upper case.
1588: * Usually this is delegated to the JDBC driver, but as some drivers
1589: * (e.g. Frontbase) implement this incorrectly, this can be overriden
1590: * in workbench.settings
1591: */
1592: public boolean storesUpperCaseIdentifiers() {
1593: IdentifierCase ocase = this .dbSettings.getObjectNameCase();
1594: if (ocase != IdentifierCase.unknown) {
1595: return ocase == IdentifierCase.upper;
1596: }
1597: try {
1598: return this .metaData.storesUpperCaseIdentifiers();
1599: } catch (SQLException e) {
1600: return false;
1601: }
1602: }
1603:
1604: /**
1605: * Returns the columns (==parameters) defined for the given procedure.
1606: */
1607: public DataStore getProcedureColumns(String aCatalog,
1608: String aSchema, String aProcname) throws SQLException {
1609: return this .procedureReader.getProcedureColumns(aCatalog,
1610: aSchema, aProcname);
1611: }
1612:
1613: public boolean procedureExists(ProcedureDefinition def) {
1614: return procedureReader.procedureExists(def.getCatalog(), def
1615: .getSchema(), def.getProcedureName(), def
1616: .getResultType());
1617: }
1618:
1619: /**
1620: * Return a list of stored procedures that are available
1621: * in the database. This call is delegated to the
1622: * currently defined {@link workbench.db.ProcedureReader}
1623: * If no DBMS specific reader is used, this is the {@link workbench.db.JdbcProcedureReader}
1624: *
1625: * @return a DataStore with the list of procedures.
1626: */
1627: public DataStore getProcedures(String aCatalog, String aSchema)
1628: throws SQLException {
1629: return this .procedureReader.getProcedures(aCatalog, aSchema);
1630: }
1631:
1632: /**
1633: * Return a list of stored procedures that are available
1634: * in the database. This call is delegated to the
1635: * currently defined {@link workbench.db.ProcedureReader}
1636: * If no DBMS specific reader is used, this is the {@link workbench.db.JdbcProcedureReader}
1637: *
1638: * @return a DataStore with the list of procedures.
1639: */
1640: public DataStore getProceduresAndTriggers(String aCatalog,
1641: String aSchema) throws SQLException {
1642: DataStore ds = this .procedureReader.getProcedures(aCatalog,
1643: aSchema);
1644: return ds;
1645: }
1646:
1647: /**
1648: * Return a List of {@link workbench.db.ProcedureDefinition} objects
1649: * for Oracle packages only one ProcedureDefinition per package is returned (although
1650: * the DbExplorer will list each function of the packages).
1651: */
1652: public List<ProcedureDefinition> getProcedureList(String aCatalog,
1653: String aSchema) throws SQLException {
1654: assert (procedureReader != null);
1655:
1656: List<ProcedureDefinition> result = new LinkedList<ProcedureDefinition>();
1657: DataStore procs = this .procedureReader.getProcedures(aCatalog,
1658: aSchema);
1659: if (procs == null || procs.getRowCount() == 0)
1660: return result;
1661: procs.sortByColumn(ProcedureReader.COLUMN_IDX_PROC_LIST_NAME,
1662: true);
1663: int count = procs.getRowCount();
1664: Set<String> oraPackages = new HashSet<String>();
1665:
1666: for (int i = 0; i < count; i++) {
1667: String schema = procs.getValueAsString(i,
1668: ProcedureReader.COLUMN_IDX_PROC_LIST_SCHEMA);
1669: String cat = procs.getValueAsString(i,
1670: ProcedureReader.COLUMN_IDX_PROC_LIST_CATALOG);
1671: String procName = procs.getValueAsString(i,
1672: ProcedureReader.COLUMN_IDX_PROC_LIST_NAME);
1673: int type = procs.getValueAsInt(i,
1674: ProcedureReader.COLUMN_IDX_PROC_LIST_TYPE,
1675: DatabaseMetaData.procedureNoResult);
1676: ProcedureDefinition def = null;
1677: if (this .isOracle && cat != null) {
1678: // The package name for Oracle is reported in the catalog column.
1679: // each function/procedure of the package is listed separately,
1680: // but we only want to create one ProcedureDefinition for the whole package
1681: if (!oraPackages.contains(cat)) {
1682: def = ProcedureDefinition.createOraclePackage(
1683: schema, cat);
1684: oraPackages.add(cat);
1685: }
1686: } else {
1687: def = new ProcedureDefinition(cat, schema, procName,
1688: type);
1689: }
1690: if (def != null)
1691: result.add(def);
1692: }
1693: return result;
1694: }
1695:
1696: /**
1697: * Enable Oracle's DBMS_OUTPUT package with a default buffer size
1698: * @see #enableOutput(long)
1699: */
1700: public void enableOutput() {
1701: this .enableOutput(-1);
1702: }
1703:
1704: /**
1705: * Enable Oracle's DBMS_OUTPUT package.
1706: * @see workbench.db.oracle.DbmsOutput#enable(long)
1707: */
1708: public void enableOutput(long aLimit) {
1709: if (!this .isOracle) {
1710: return;
1711: }
1712:
1713: if (this .oraOutput == null) {
1714: try {
1715: this .oraOutput = new DbmsOutput(this .dbConnection
1716: .getSqlConnection());
1717: } catch (Exception e) {
1718: LogMgr.logError("DbMetadata.enableOutput()",
1719: "Could not create DbmsOutput", e);
1720: this .oraOutput = null;
1721: }
1722: }
1723:
1724: if (this .oraOutput != null) {
1725: try {
1726: this .oraOutput.enable(aLimit);
1727: } catch (Throwable e) {
1728: LogMgr.logError("DbMetadata.enableOutput()",
1729: "Error when enabling DbmsOutput", e);
1730: }
1731: }
1732: }
1733:
1734: /**
1735: * Disable Oracle's DBMS_OUTPUT package
1736: * @see workbench.db.oracle.DbmsOutput#disable()
1737: */
1738: public void disableOutput() {
1739: if (!this .isOracle)
1740: return;
1741:
1742: if (this .oraOutput != null) {
1743: try {
1744: this .oraOutput.disable();
1745: this .oraOutput = null;
1746: } catch (Throwable e) {
1747: LogMgr.logError("DbMetadata.disableOutput()",
1748: "Error when disabling DbmsOutput", e);
1749: }
1750: }
1751: }
1752:
1753: /**
1754: * Return any server side messages. Currently this is only implemented
1755: * for Oracle (and is returning messages that were "printed" using
1756: * the DBMS_OUTPUT package
1757: */
1758: public String getOutputMessages() {
1759: String result = StringUtil.EMPTY_STRING;
1760:
1761: if (this .oraOutput != null) {
1762: try {
1763: result = this .oraOutput.getResult();
1764: } catch (Throwable th) {
1765: LogMgr.logError("DbMetadata.getOutputMessages()",
1766: "Error when retrieving Output Messages", th);
1767: result = StringUtil.EMPTY_STRING;
1768: }
1769: }
1770: return result;
1771: }
1772:
1773: /**
1774: * Release any resources for this object. After a call
1775: * to close(), this object should not be used any longer
1776: */
1777: public void close() {
1778: if (this .oraOutput != null)
1779: this .oraOutput.close();
1780: if (this .oracleMetaData != null)
1781: this .oracleMetaData.columnsProcessed();
1782: }
1783:
1784: public int fixColumnType(int type) {
1785: if (this .isOracle) {
1786: if (type == Types.DATE
1787: && this .oracleMetaData.getMapDateToTimestamp())
1788: return Types.TIMESTAMP;
1789:
1790: // Oracle reports TIMESTAMP WITH TIMEZONE with the numeric
1791: // value -101 (which is not an official java.sql.Types value
1792: // TIMESTAMP WITH LOCAL TIMEZONE is reported as -102
1793: if (type == -101 || type == -102)
1794: return Types.TIMESTAMP;
1795: }
1796:
1797: return type;
1798: }
1799:
1800: /**
1801: * Return the column list for the given table.
1802: * @param table the table for which to retrieve the column definition
1803: * @see #getTableDefinition(String, String, String, String)
1804: */
1805: public List<ColumnIdentifier> getTableColumns(TableIdentifier table)
1806: throws SQLException {
1807: DataStore ds = this .getTableDefinition(table);
1808: return createColumnIdentifiers(ds);
1809: }
1810:
1811: private List<ColumnIdentifier> createColumnIdentifiers(DataStore ds) {
1812: int count = ds.getRowCount();
1813: List<ColumnIdentifier> result = new ArrayList<ColumnIdentifier>(
1814: count);
1815: for (int i = 0; i < count; i++) {
1816: String col = ds.getValueAsString(i,
1817: COLUMN_IDX_TABLE_DEFINITION_COL_NAME);
1818: int type = ds.getValueAsInt(i,
1819: COLUMN_IDX_TABLE_DEFINITION_JAVA_SQL_TYPE,
1820: Types.OTHER);
1821: boolean pk = "YES".equals(ds.getValueAsString(i,
1822: COLUMN_IDX_TABLE_DEFINITION_PK_FLAG));
1823: ColumnIdentifier ci = new ColumnIdentifier(SqlUtil
1824: .quoteObjectname(col), fixColumnType(type), pk);
1825: int size = ds.getValueAsInt(i,
1826: COLUMN_IDX_TABLE_DEFINITION_SIZE, 0);
1827: int digits = ds.getValueAsInt(i,
1828: COLUMN_IDX_TABLE_DEFINITION_DIGITS, 0);
1829: String nullable = ds.getValueAsString(i,
1830: COLUMN_IDX_TABLE_DEFINITION_NULLABLE);
1831: int position = ds.getValueAsInt(i,
1832: COLUMN_IDX_TABLE_DEFINITION_POSITION, 0);
1833: String dbmstype = ds.getValueAsString(i,
1834: COLUMN_IDX_TABLE_DEFINITION_DATA_TYPE);
1835: String comment = ds.getValueAsString(i,
1836: COLUMN_IDX_TABLE_DEFINITION_REMARKS);
1837: String def = ds.getValueAsString(i,
1838: COLUMN_IDX_TABLE_DEFINITION_DEFAULT);
1839: ci.setColumnSize(size);
1840: ci.setDecimalDigits(digits);
1841: ci.setIsNullable(StringUtil.stringToBool(nullable));
1842: ci.setDbmsType(dbmstype);
1843: ci.setComment(comment);
1844: ci.setDefaultValue(def);
1845: ci.setPosition(position);
1846: result.add(ci);
1847: }
1848: return result;
1849: }
1850:
1851: /** The column index for a {@link workbench.storage.DataStore} returned
1852: * by {@link #getTableDefinition(TableIdentifier)} that holds
1853: * the column name
1854: */
1855: public final static int COLUMN_IDX_TABLE_DEFINITION_COL_NAME = 0;
1856:
1857: /** The column index for a {@link workbench.storage.DataStore} returned
1858: * by {@link #getTableDefinition(TableIdentifier)} that holds
1859: * the DBMS specific data type string
1860: */
1861: public final static int COLUMN_IDX_TABLE_DEFINITION_DATA_TYPE = 1;
1862:
1863: /** The column index for a {@link workbench.storage.DataStore} returned
1864: * by {@link #getTableDefinition(TableIdentifier)} that holds
1865: * the primary key flag
1866: */
1867: public final static int COLUMN_IDX_TABLE_DEFINITION_PK_FLAG = 2;
1868:
1869: /** The column index for a {@link workbench.storage.DataStore} returned
1870: * by {@link #getTableDefinition(TableIdentifier)} that holds
1871: * the nullable flag
1872: */
1873: public final static int COLUMN_IDX_TABLE_DEFINITION_NULLABLE = 3;
1874:
1875: /** The column index for a {@link workbench.storage.DataStore} returned
1876: * by {@link #getTableDefinition(TableIdentifier)} that holds
1877: * the default value for this column
1878: */
1879: public final static int COLUMN_IDX_TABLE_DEFINITION_DEFAULT = 4;
1880:
1881: /** The column index for a {@link workbench.storage.DataStore} returned
1882: * by {@link #getTableDefinition(TableIdentifier)} that holds
1883: * the remark for this column
1884: */
1885: public final static int COLUMN_IDX_TABLE_DEFINITION_REMARKS = 5;
1886:
1887: /** The column index for a {@link workbench.storage.DataStore} returned
1888: * by {@link #getTableDefinition(TableIdentifier)} that holds
1889: * the integer value of the java datatype from {@link java.sql.Types}
1890: */
1891: public final static int COLUMN_IDX_TABLE_DEFINITION_JAVA_SQL_TYPE = 6;
1892:
1893: /** The column index for a {@link workbench.storage.DataStore} returned
1894: * by {@link #getTableDefinition(TableIdentifier)} that holds
1895: * the integer value of siez of the column
1896: */
1897: public final static int COLUMN_IDX_TABLE_DEFINITION_SIZE = 7;
1898:
1899: /** The column index for a {@link workbench.storage.DataStore} returned
1900: * by {@link #getTableDefinition(TableIdentifier)} that holds
1901: * the number of digits for the column
1902: */
1903: public final static int COLUMN_IDX_TABLE_DEFINITION_DIGITS = 8;
1904:
1905: /** The column index for a {@link workbench.storage.DataStore} returned
1906: * by {@link #getTableDefinition(TableIdentifier)} that holds
1907: * the ordinal position of the column
1908: */
1909: public final static int COLUMN_IDX_TABLE_DEFINITION_POSITION = 9;
1910:
1911: /**
1912: * Returns the definition of the given
1913: * table in a {@link workbench.storage.DataStore }
1914: * @return definiton of the datastore
1915: * @param id The identifier of the table
1916: * @throws SQLException If the table was not found or an error occurred
1917: * @see #getTableDefinition(String, String, String, String)
1918: */
1919: public DataStore getTableDefinition(TableIdentifier id)
1920: throws SQLException {
1921: if (id == null)
1922: return null;
1923: String type = id.getType();
1924: if (type == null)
1925: type = tableTypeName;
1926: TableIdentifier tbl = id.createCopy();
1927: tbl.adjustCase(dbConnection);
1928: return this .getTableDefinition(tbl.getRawCatalog(), tbl
1929: .getRawSchema(), tbl.getRawTableName(), type);
1930: }
1931:
1932: public static final String[] TABLE_DEFINITION_COLS = {
1933: "COLUMN_NAME", "DATA_TYPE", "PK", "NULLABLE", "DEFAULT",
1934: "REMARKS", "java.sql.Types", "SCALE/SIZE", "PRECISION",
1935: "POSITION" };
1936:
1937: private DataStore createTableDefinitionDataStore() {
1938: final int[] types = { Types.VARCHAR, Types.VARCHAR,
1939: Types.VARCHAR, Types.VARCHAR, Types.VARCHAR,
1940: Types.VARCHAR, Types.INTEGER, Types.INTEGER,
1941: Types.INTEGER, Types.INTEGER };
1942: final int[] sizes = { 20, 18, 5, 8, 10, 25, 18, 2, 2, 2 };
1943: DataStore ds = new DataStore(TABLE_DEFINITION_COLS, types,
1944: sizes);
1945: return ds;
1946: }
1947:
1948: /** Return a DataStore containing the definition of the given table.
1949: *
1950: * @param aCatalog The catalog in which the table is defined. This should be null if the DBMS does not support catalogs
1951: * @param aSchema The schema in which the table is defined. This should be null if the DBMS does not support schemas
1952: * @param aTable The name of the table
1953: * @param aType The type of the table
1954: * @throws SQLException
1955: * @return A DataStore with the table definition.
1956: * The individual columns should be accessed using the
1957: * COLUMN_IDX_TABLE_DEFINITION_xxx constants.
1958: */
1959: protected DataStore getTableDefinition(String aCatalog,
1960: String aSchema, String aTable, String aType)
1961: throws SQLException {
1962: if (aTable == null)
1963: throw new IllegalArgumentException(
1964: "Tablename may not be null!");
1965:
1966: DataStore ds = this .createTableDefinitionDataStore();
1967:
1968: aCatalog = StringUtil.trimQuotes(aCatalog);
1969: aSchema = StringUtil.trimQuotes(aSchema);
1970: aTable = StringUtil.trimQuotes(aTable);
1971:
1972: if (aSchema == null && this .isOracle()) {
1973: aSchema = this .getSchemaToUse();
1974: }
1975:
1976: if (this .sequenceReader != null
1977: && "SEQUENCE".equalsIgnoreCase(aType)) {
1978: DataStore seqDs = this .sequenceReader
1979: .getRawSequenceDefinition(aSchema, aTable);
1980: if (seqDs != null)
1981: return seqDs;
1982: }
1983:
1984: if ("SYNONYM".equalsIgnoreCase(aType)) {
1985: TableIdentifier id = this .getSynonymTable(aSchema, aTable);
1986: if (id != null) {
1987: aSchema = id.getSchema();
1988: aTable = id.getTableName();
1989: aCatalog = null;
1990: }
1991: }
1992:
1993: ArrayList<String> keys = new ArrayList<String>();
1994: if (this .dbSettings.supportsGetPrimaryKeys()) {
1995: ResultSet keysRs = null;
1996: try {
1997: keysRs = this .metaData.getPrimaryKeys(aCatalog,
1998: aSchema, aTable);
1999: while (keysRs.next()) {
2000: keys.add(keysRs.getString("COLUMN_NAME")
2001: .toLowerCase());
2002: }
2003: } catch (Throwable e) {
2004: LogMgr.logWarning("DbMetaData.getTableDefinition()",
2005: "Error retrieving key columns: "
2006: + e.getMessage());
2007: } finally {
2008: SqlUtil.closeResult(keysRs);
2009: }
2010: }
2011:
2012: boolean hasEnums = false;
2013:
2014: ResultSet rs = null;
2015:
2016: try {
2017: if (this .oracleMetaData != null) {
2018: rs = this .oracleMetaData.getColumns(aCatalog, aSchema,
2019: aTable, "%");
2020: } else {
2021: rs = this .metaData.getColumns(aCatalog, aSchema,
2022: aTable, "%");
2023: }
2024:
2025: while (rs != null && rs.next()) {
2026: int row = ds.addRow();
2027:
2028: // The columns should be retrieved (getXxx()) in the order
2029: // as they appear in the result set as some drivers
2030: // do not like an out-of-order processing of the columns
2031:
2032: String colName = rs.getString("COLUMN_NAME"); // index 4
2033: int sqlType = rs.getInt("DATA_TYPE"); // index 5
2034: String typeName = rs.getString("TYPE_NAME");
2035: if (this .isMySql && !hasEnums) {
2036: hasEnums = typeName.toLowerCase()
2037: .startsWith("enum")
2038: || typeName.toLowerCase().startsWith("set");
2039: }
2040:
2041: int size = rs.getInt("COLUMN_SIZE"); // index 7
2042: int digits = rs.getInt("DECIMAL_DIGITS"); // index 9
2043: String remarks = rs.getString("REMARKS"); // index 12
2044: String defaultValue = rs.getString("COLUMN_DEF"); // index 13
2045: if (defaultValue != null
2046: && this .dbSettings.trimDefaults()) {
2047: defaultValue = defaultValue.trim();
2048: }
2049:
2050: int sqlDataType = -1;
2051: try {
2052: // This column is used by our own OracleMetaData to
2053: // return information about char/byte semantics
2054: sqlDataType = rs.getInt("SQL_DATA_TYPE"); // index 14
2055: } catch (Throwable th) {
2056: // The specs says "unused" for this column, so maybe
2057: // there are drivers that do not return this column at all.
2058: sqlDataType = -1;
2059: }
2060:
2061: int position = -1;
2062: try {
2063: position = rs.getInt("ORDINAL_POSITION"); // index 17
2064: } catch (SQLException e) {
2065: LogMgr
2066: .logWarning(
2067: "DbMetadata",
2068: "JDBC driver does not suport ORDINAL_POSITION column for getColumns()",
2069: e);
2070: position = -1;
2071: }
2072:
2073: String nullable = rs.getString("IS_NULLABLE"); // index 18
2074:
2075: String display = this .dataTypeResolver
2076: .getSqlTypeDisplay(typeName, sqlType, size,
2077: digits, sqlDataType);
2078:
2079: ds.setValue(row, COLUMN_IDX_TABLE_DEFINITION_COL_NAME,
2080: colName);
2081: ds.setValue(row, COLUMN_IDX_TABLE_DEFINITION_DATA_TYPE,
2082: display);
2083:
2084: if (keys.contains(colName.toLowerCase()))
2085: ds.setValue(row,
2086: COLUMN_IDX_TABLE_DEFINITION_PK_FLAG, "YES");
2087: else
2088: ds.setValue(row,
2089: COLUMN_IDX_TABLE_DEFINITION_PK_FLAG, "NO");
2090:
2091: ds.setValue(row, COLUMN_IDX_TABLE_DEFINITION_NULLABLE,
2092: nullable);
2093: ds.setValue(row, COLUMN_IDX_TABLE_DEFINITION_DEFAULT,
2094: defaultValue);
2095: ds.setValue(row, COLUMN_IDX_TABLE_DEFINITION_REMARKS,
2096: remarks);
2097: ds.setValue(row,
2098: COLUMN_IDX_TABLE_DEFINITION_JAVA_SQL_TYPE,
2099: new Integer(sqlType));
2100: ds.setValue(row, COLUMN_IDX_TABLE_DEFINITION_SIZE,
2101: new Integer(size));
2102: ds.setValue(row, COLUMN_IDX_TABLE_DEFINITION_DIGITS,
2103: new Integer(digits));
2104: ds.setValue(row, COLUMN_IDX_TABLE_DEFINITION_POSITION,
2105: new Integer(position));
2106: }
2107: } finally {
2108: SqlUtil.closeResult(rs);
2109: if (this .oracleMetaData != null) {
2110: this .oracleMetaData.columnsProcessed();
2111: }
2112: }
2113:
2114: if (hasEnums) {
2115: TableIdentifier tbl = new TableIdentifier(aCatalog,
2116: aSchema, aTable);
2117: EnumReader.updateEnumDefinition(tbl, ds, this .dbConnection);
2118: }
2119:
2120: return ds;
2121: }
2122:
2123: public static final int COLUMN_IDX_TABLE_INDEXLIST_INDEX_NAME = 0;
2124: public static final int COLUMN_IDX_TABLE_INDEXLIST_UNIQUE_FLAG = 1;
2125: public static final int COLUMN_IDX_TABLE_INDEXLIST_PK_FLAG = 2;
2126: public static final int COLUMN_IDX_TABLE_INDEXLIST_COL_DEF = 3;
2127: public static final int COLUMN_IDX_TABLE_INDEXLIST_TYPE = 4;
2128:
2129: /**
2130: * If the passed TableIdentifier is a Synonym and the current
2131: * DBMS supports synonyms, a TableIdentifier for the "real"
2132: * table is returned.
2133: *
2134: * Otherwise the passed TableIdentifier is returned
2135: */
2136: public TableIdentifier resolveSynonym(TableIdentifier tbl) {
2137: if (tbl == null)
2138: return null;
2139: if (!supportsSynonyms())
2140: return tbl;
2141: String type = tbl.getType();
2142: if (type != null && !dbSettings.isSynonymType(type))
2143: return tbl;
2144: TableIdentifier syn = getSynonymTable(tbl);
2145: if (syn == null)
2146: return tbl;
2147: return syn;
2148: }
2149:
2150: /**
2151: * Return the index information for a table as a DataStore. This is
2152: * delegated to getTableIndexList() and from the resulting collection
2153: * the datastore is created.
2154: *
2155: * @param table the table to get the indexes for
2156: * @see #getTableIndexList(TableIdentifier)
2157: */
2158: public DataStore getTableIndexInformation(TableIdentifier table) {
2159: String[] cols = { "INDEX_NAME", "UNIQUE", "PK", "DEFINITION",
2160: "TYPE" };
2161: final int types[] = { Types.VARCHAR, Types.VARCHAR,
2162: Types.VARCHAR, Types.VARCHAR, Types.VARCHAR };
2163: final int sizes[] = { 30, 7, 6, 40, 10 };
2164: DataStore idxData = new DataStore(cols, types, sizes);
2165: if (table == null)
2166: return idxData;
2167: Collection<IndexDefinition> indexes = getTableIndexList(table);
2168: for (IndexDefinition idx : indexes) {
2169: int row = idxData.addRow();
2170: idxData.setValue(row,
2171: COLUMN_IDX_TABLE_INDEXLIST_INDEX_NAME, idx
2172: .getName());
2173: idxData.setValue(row,
2174: COLUMN_IDX_TABLE_INDEXLIST_UNIQUE_FLAG, (idx
2175: .isUnique() ? "YES" : "NO"));
2176: idxData.setValue(row, COLUMN_IDX_TABLE_INDEXLIST_PK_FLAG,
2177: (idx.isPrimaryKeyIndex() ? "YES" : "NO"));
2178: idxData.setValue(row, COLUMN_IDX_TABLE_INDEXLIST_COL_DEF,
2179: idx.getExpression());
2180: idxData.setValue(row, COLUMN_IDX_TABLE_INDEXLIST_TYPE, idx
2181: .getIndexType());
2182: }
2183: idxData.sortByColumn(0, true);
2184: return idxData;
2185: }
2186:
2187: /**
2188: * Returns a list of indexes defined for the given table
2189: * @param table the table to get the indexes for
2190: */
2191: public Collection<IndexDefinition> getTableIndexList(
2192: TableIdentifier table) {
2193: ResultSet idxRs = null;
2194: TableIdentifier tbl = table.createCopy();
2195: tbl.adjustCase(this .dbConnection);
2196:
2197: // This will map an indexname to an IndexDefinition object
2198: // getIndexInfo() returns one row for each column
2199: HashMap<String, IndexDefinition> defs = new HashMap<String, IndexDefinition>();
2200:
2201: try {
2202: // Retrieve the name of the PK index
2203: String pkName = "";
2204: if (this .dbSettings.supportsGetPrimaryKeys()) {
2205: ResultSet keysRs = null;
2206: try {
2207: keysRs = this .metaData.getPrimaryKeys(tbl
2208: .getCatalog(), tbl.getSchema(), tbl
2209: .getTableName());
2210: while (keysRs.next()) {
2211: pkName = keysRs.getString("PK_NAME");
2212: }
2213: } catch (Exception e) {
2214: LogMgr.logWarning(
2215: "DbMetadata.getTableIndexInformation()",
2216: "Error retrieving PK information", e);
2217: pkName = "";
2218: } finally {
2219: SqlUtil.closeResult(keysRs);
2220: }
2221: }
2222:
2223: idxRs = this .indexReader.getIndexInfo(tbl, false);
2224:
2225: while (idxRs.next()) {
2226: boolean unique = idxRs.getBoolean("NON_UNIQUE");
2227: String indexName = idxRs.getString("INDEX_NAME");
2228: if (idxRs.wasNull())
2229: continue;
2230: if (indexName == null)
2231: continue;
2232: String colName = idxRs.getString("COLUMN_NAME");
2233: String dir = idxRs.getString("ASC_OR_DESC");
2234:
2235: IndexDefinition def = defs.get(indexName);
2236: if (def == null) {
2237: def = new IndexDefinition(tbl, indexName, null);
2238: def.setUnique(!unique);
2239: def.setPrimaryKeyIndex(pkName.equals(indexName));
2240: defs.put(indexName, def);
2241: Object type = idxRs.getObject("TYPE");
2242: def.setIndexType(dbSettings.mapIndexType(type));
2243: }
2244:
2245: def.addColumn(colName, dir);
2246: }
2247:
2248: this .indexReader.processIndexList(tbl, defs.values());
2249: } catch (Exception e) {
2250: LogMgr.logWarning("DbMetadata.getTableIndexInformation()",
2251: "Could not retrieve indexes", e);
2252: } finally {
2253: SqlUtil.closeResult(idxRs);
2254: this .indexReader.indexInfoProcessed();
2255: }
2256: return defs.values();
2257: }
2258:
2259: public List<TableIdentifier> getTableList(String schema,
2260: String[] types) throws SQLException {
2261: if (schema == null)
2262: schema = this .getCurrentSchema();
2263: return getTableList(null, schema, types);
2264: }
2265:
2266: public List<TableIdentifier> getTableList(String table,
2267: String schema) throws SQLException {
2268: return getTableList(table, schema, tableTypesTable);
2269: }
2270:
2271: public List<TableIdentifier> getSelectableObjectsList(String schema)
2272: throws SQLException {
2273: return getTableList(null, schema, tableTypesSelectable, false);
2274: }
2275:
2276: public List<TableIdentifier> getTableList(String table,
2277: String schema, String[] types) throws SQLException {
2278: return getTableList(table, schema, types, false);
2279: }
2280:
2281: /**
2282: * Return a list of tables for the given schema
2283: * if the schema is null, all tables will be returned
2284: */
2285: public List<TableIdentifier> getTableList(String table,
2286: String schema, String[] types, boolean returnAllSchemas)
2287: throws SQLException {
2288: DataStore ds = getTables(null, schema, table, types);
2289: int count = ds.getRowCount();
2290: List<TableIdentifier> tables = new ArrayList<TableIdentifier>(
2291: count);
2292: for (int i = 0; i < count; i++) {
2293: String t = ds.getValueAsString(i,
2294: COLUMN_IDX_TABLE_LIST_NAME);
2295: String s = ds.getValueAsString(i,
2296: COLUMN_IDX_TABLE_LIST_SCHEMA);
2297: String c = ds.getValueAsString(i,
2298: COLUMN_IDX_TABLE_LIST_CATALOG);
2299: if (!returnAllSchemas && this .ignoreSchema(s)) {
2300: s = null;
2301: }
2302: if (this .ignoreCatalog(c)) {
2303: c = null;
2304: }
2305: TableIdentifier tbl = new TableIdentifier(c, s, t);
2306: tbl.setNeverAdjustCase(true);
2307: tbl.setType(ds.getValueAsString(i,
2308: COLUMN_IDX_TABLE_LIST_TYPE));
2309: tables.add(tbl);
2310: }
2311: return tables;
2312: }
2313:
2314: /**
2315: * Return the current catalog for this connection. If no catalog is defined
2316: * or the DBMS does not support catalogs, an empty string is returned.
2317: *
2318: * This method works around a bug in Microsoft's JDBC driver which does
2319: * not return the correct database (=catalog) after the database has
2320: * been changed with the USE <db> command from within the Workbench.
2321: *
2322: * If no query has been configured for the current DBMS, DatabaseMetaData.getCatalog()
2323: * is used, otherwise the query that is configured with the property
2324: * workbench.db.[dbid].currentcatalog.query
2325: *
2326: * @see DbSettings#getQueryForCurrentCatalog()
2327: *
2328: * @return The name of the current catalog or an empty String if there is no current catalog
2329: */
2330: public String getCurrentCatalog() {
2331: String catalog = null;
2332:
2333: String query = this .dbSettings.getQueryForCurrentCatalog();
2334: if (query != null) {
2335: // for some reason, getCatalog() does not return the correct
2336: // information when using Microsoft's JDBC driver.
2337: // If this is the case, a SQL query can be defined that is
2338: // used instead of the JDBC call, e.g. SELECT db_name()
2339: Statement stmt = null;
2340: ResultSet rs = null;
2341: try {
2342: stmt = this .dbConnection.createStatementForQuery();
2343: rs = stmt.executeQuery(query);
2344: if (rs.next())
2345: catalog = rs.getString(1);
2346: } catch (Exception e) {
2347: LogMgr.logWarning("DbMetadata.getCurrentCatalog()",
2348: "Error retrieving current catalog using query=["
2349: + query + "]", e);
2350: catalog = null;
2351: } finally {
2352: SqlUtil.closeAll(rs, stmt);
2353: }
2354: }
2355:
2356: if (catalog == null) {
2357: try {
2358: catalog = this .dbConnection.getSqlConnection()
2359: .getCatalog();
2360: } catch (Exception e) {
2361: LogMgr
2362: .logWarning(
2363: "DbMetadata.getCurrentCatalog",
2364: "Could not retrieve catalog using getCatalog()",
2365: e);
2366: catalog = StringUtil.EMPTY_STRING;
2367: }
2368: }
2369: if (catalog == null)
2370: catalog = StringUtil.EMPTY_STRING;
2371:
2372: return catalog;
2373: }
2374:
2375: public boolean supportsCatalogs() {
2376: boolean supportsCatalogs = false;
2377: try {
2378: supportsCatalogs = metaData
2379: .supportsCatalogsInDataManipulation()
2380: || metaData.supportsCatalogsInTableDefinitions()
2381: || metaData.supportsCatalogsInProcedureCalls();
2382: } catch (Exception e) {
2383: supportsCatalogs = false;
2384: }
2385: return supportsCatalogs;
2386: }
2387:
2388: /**
2389: * Changes the current catalog using Connection.setCatalog()
2390: * and notifies the connection object about the change.
2391: *
2392: * @param newCatalog the name of the new catalog/database that should be selected
2393: * @see WbConnection#catalogChanged(String, String)
2394: */
2395: public boolean setCurrentCatalog(String newCatalog)
2396: throws SQLException {
2397: if (StringUtil.isEmptyString(newCatalog))
2398: return false;
2399:
2400: String old = getCurrentCatalog();
2401: boolean useSetCatalog = dbSettings.useSetCatalog();
2402: boolean clearWarnings = Settings.getInstance().getBoolProperty(
2403: "workbench.db." + this .getDbId()
2404: + ".setcatalog.clearwarnings", true);
2405:
2406: // MySQL does not seem to like changing the current database by executing a USE command
2407: // through Statement.execute(), so we'll use setCatalog() instead
2408: // which seems to work with SQL Server as well.
2409: // If for some reason this does not work, it could be turned off
2410: if (useSetCatalog) {
2411: this .dbConnection.getSqlConnection().setCatalog(
2412: trimQuotes(newCatalog));
2413: } else {
2414: Statement stmt = null;
2415: try {
2416: stmt = this .dbConnection.createStatement();
2417: stmt.execute("USE " + newCatalog);
2418: if (clearWarnings)
2419: stmt.clearWarnings();
2420: } finally {
2421: SqlUtil.closeStatement(stmt);
2422: }
2423: }
2424:
2425: if (clearWarnings)
2426: this .dbConnection.clearWarnings();
2427:
2428: String newCat = getCurrentCatalog();
2429: if (!StringUtil.equalString(old, newCat)) {
2430: this .dbConnection.catalogChanged(old, newCatalog);
2431: }
2432: LogMgr.logDebug("DbMetadata.setCurrentCatalog",
2433: "Catalog changed to " + newCat);
2434:
2435: return true;
2436: }
2437:
2438: /**
2439: * Remove quotes from an object's name.
2440: * For MS SQL Server this also removes [] brackets
2441: * around the identifier.
2442: */
2443: private String trimQuotes(String s) {
2444: if (s.length() < 2)
2445: return s;
2446: if (this .isSqlServer) {
2447: String clean = s.trim();
2448: int len = clean.length();
2449: if (clean.charAt(0) == '[' && clean.charAt(len - 1) == ']')
2450: return clean.substring(1, len - 1);
2451: }
2452:
2453: return StringUtil.trimQuotes(s);
2454: }
2455:
2456: /**
2457: * Returns a list of all catalogs in the database.
2458: * Some DBMS's do not support catalogs, in this case the method
2459: * will return an empty Datastore.
2460: */
2461: public DataStore getCatalogInformation() {
2462:
2463: String[] cols = { this .getCatalogTerm().toUpperCase() };
2464: int[] types = { Types.VARCHAR };
2465: int[] sizes = { 10 };
2466:
2467: DataStore result = new DataStore(cols, types, sizes);
2468: ResultSet rs = null;
2469: try {
2470: rs = this .metaData.getCatalogs();
2471: while (rs.next()) {
2472: String cat = rs.getString(1);
2473: if (cat != null) {
2474: int row = result.addRow();
2475: result.setValue(row, 0, cat);
2476: }
2477: }
2478: } catch (Exception e) {
2479: } finally {
2480: SqlUtil.closeResult(rs);
2481: }
2482:
2483: if (result.getRowCount() == 1) {
2484: String cat = result.getValueAsString(0, 0);
2485: if (cat.equals(this .getCurrentCatalog())) {
2486: result.reset();
2487: }
2488: }
2489:
2490: return result;
2491: }
2492:
2493: /**
2494: * The column index in the DataStore returned by getTableTriggers which identifies
2495: * the name of the trigger.
2496: */
2497: public static final int COLUMN_IDX_TABLE_TRIGGERLIST_TRG_NAME = 0;
2498: /**
2499: * The column index in the DataStore returned by getTableTriggers which identifies
2500: * the type (INSERT, UPDATE etc) of the trigger.
2501: */
2502: public static final int COLUMN_IDX_TABLE_TRIGGERLIST_TRG_TYPE = 1;
2503: /**
2504: * The column index in the DataStore returned by getTableTriggers which identifies
2505: * the event (before, after) of the trigger.
2506: */
2507: public static final int COLUMN_IDX_TABLE_TRIGGERLIST_TRG_EVENT = 2;
2508:
2509: /**
2510: * Return a list of triggers available in the given schema.
2511: */
2512: public DataStore getTriggers(String catalog, String schema)
2513: throws SQLException {
2514: return getTriggers(catalog, schema, null);
2515: }
2516:
2517: /**
2518: * Return the list of defined triggers for the given table.
2519: */
2520: public DataStore getTableTriggers(TableIdentifier table)
2521: throws SQLException {
2522: TableIdentifier tbl = table.createCopy();
2523: tbl.adjustCase(this .dbConnection);
2524: return getTriggers(tbl.getCatalog(), tbl.getSchema(), tbl
2525: .getTableName());
2526: }
2527:
2528: protected DataStore getTriggers(String catalog, String schema,
2529: String tableName) throws SQLException {
2530: final String[] cols = { "NAME", "TYPE", "EVENT" };
2531: final int types[] = { Types.VARCHAR, Types.VARCHAR,
2532: Types.VARCHAR };
2533: final int sizes[] = { 30, 30, 20 };
2534:
2535: DataStore result = new DataStore(cols, types, sizes);
2536:
2537: GetMetaDataSql sql = metaSqlMgr.getListTriggerSql();
2538: if (sql == null) {
2539: return result;
2540: }
2541:
2542: sql.setSchema(schema);
2543: sql.setCatalog(catalog);
2544: sql.setObjectName(tableName);
2545:
2546: Statement stmt = this .dbConnection.createStatementForQuery();
2547: String query = this .adjustHsqlQuery(sql.getSql());
2548:
2549: if (Settings.getInstance().getDebugMetadataSql()) {
2550: LogMgr.logInfo("DbMetadata.getTableTriggers()",
2551: "Using query=\n" + query);
2552: }
2553: ResultSet rs = stmt.executeQuery(query);
2554: try {
2555: while (rs.next()) {
2556: int row = result.addRow();
2557: String value = rs.getString(1);
2558: if (!rs.wasNull() && value != null)
2559: value = value.trim();
2560: result.setValue(row,
2561: COLUMN_IDX_TABLE_TRIGGERLIST_TRG_NAME, value);
2562:
2563: value = rs.getString(2);
2564: result.setValue(row,
2565: COLUMN_IDX_TABLE_TRIGGERLIST_TRG_TYPE, value);
2566:
2567: value = rs.getString(3);
2568: result.setValue(row,
2569: COLUMN_IDX_TABLE_TRIGGERLIST_TRG_EVENT, value);
2570: }
2571: } finally {
2572: SqlUtil.closeAll(rs, stmt);
2573: }
2574: return result;
2575: }
2576:
2577: /**
2578: * Retrieve the SQL Source of the given trigger.
2579: *
2580: * @param aCatalog The catalog in which the trigger is defined. This should be null if the DBMS does not support catalogs
2581: * @param aSchema The schema in which the trigger is defined. This should be null if the DBMS does not support schemas
2582: * @param aTriggername
2583: * @throws SQLException
2584: * @return the trigger source
2585: */
2586: public String getTriggerSource(String aCatalog, String aSchema,
2587: String aTriggername) throws SQLException {
2588: StringBuilder result = new StringBuilder(500);
2589:
2590: if ("*".equals(aCatalog))
2591: aCatalog = null;
2592: if ("*".equals(aSchema))
2593: aSchema = null;
2594:
2595: GetMetaDataSql sql = metaSqlMgr.getTriggerSourceSql();
2596: if (sql == null)
2597: return StringUtil.EMPTY_STRING;
2598:
2599: sql.setSchema(aSchema);
2600: sql.setCatalog(aCatalog);
2601: sql.setObjectName(aTriggername);
2602: Statement stmt = this .dbConnection.createStatementForQuery();
2603: String query = this .adjustHsqlQuery(sql.getSql());
2604:
2605: if (Settings.getInstance().getDebugMetadataSql()) {
2606: LogMgr.logInfo("DbMetadata.getTriggerSource()",
2607: "Using query=\n" + query);
2608: }
2609:
2610: String nl = Settings.getInstance()
2611: .getInternalEditorLineEnding();
2612:
2613: ResultSet rs = null;
2614: try {
2615: // for some DBMS (e.g. SQL Server)
2616: // we need to run a exec which might not work
2617: // when using executeQuery() (depending on the JDBC driver)
2618: stmt.execute(query);
2619: rs = stmt.getResultSet();
2620:
2621: if (rs != null) {
2622: int colCount = rs.getMetaData().getColumnCount();
2623: while (rs.next()) {
2624: for (int i = 1; i <= colCount; i++) {
2625: result.append(rs.getString(i));
2626: }
2627: }
2628: }
2629: CharSequence warn = SqlUtil.getWarnings(this .dbConnection,
2630: stmt);
2631: if (warn != null && result.length() > 0)
2632: result.append(nl + nl);
2633: result.append(warn);
2634: } catch (SQLException e) {
2635: LogMgr.logError("DbMetadata.getTriggerSource()",
2636: "Error reading trigger source", e);
2637: if (this .isPostgres)
2638: try {
2639: this .dbConnection.rollback();
2640: } catch (Throwable th) {
2641: }
2642: result.append(ExceptionUtil.getDisplay(e));
2643: SqlUtil.closeAll(rs, stmt);
2644: return result.toString();
2645: } finally {
2646: SqlUtil.closeAll(rs, stmt);
2647: }
2648:
2649: boolean replaceNL = Settings.getInstance().getBoolProperty(
2650: "workbench.db." + getDbId()
2651: + ".replacenl.triggersource", false);
2652:
2653: String source = result.toString();
2654: if (replaceNL) {
2655: source = StringUtil.replace(source, "\\n", nl);
2656: }
2657: return source;
2658: }
2659:
2660: /** Returns the list of schemas as returned by DatabaseMetadata.getSchemas()
2661: * @return List
2662: */
2663: public List<String> getSchemas() {
2664: ArrayList<String> result = new ArrayList<String>();
2665: ResultSet rs = null;
2666: try {
2667: rs = this .metaData.getSchemas();
2668: while (rs.next()) {
2669: result.add(rs.getString(1));
2670: }
2671: } catch (Exception e) {
2672: LogMgr
2673: .logWarning("DbMetadata.getSchemas()",
2674: "Error retrieving schemas: "
2675: + e.getMessage(), null);
2676: } finally {
2677: SqlUtil.closeResult(rs);
2678: }
2679: if (this .isOracle) {
2680: result.add("PUBLIC");
2681: Collections.sort(result);
2682: }
2683: return result;
2684: }
2685:
2686: private boolean isIndexType(String type) {
2687: if (type == null)
2688: return false;
2689: return (type.indexOf("INDEX") > -1);
2690: }
2691:
2692: private boolean hideIndexes() {
2693: return (isPostgres && Settings.getInstance().getBoolProperty(
2694: "workbench.db.postgres.hideindex", true));
2695: }
2696:
2697: public Collection<String> getTableTypes() {
2698: TreeSet<String> result = new TreeSet<String>();
2699: ResultSet rs = null;
2700: boolean hideIndexes = hideIndexes();
2701:
2702: try {
2703: rs = this .metaData.getTableTypes();
2704: while (rs != null && rs.next()) {
2705: String type = rs.getString(1);
2706: if (type == null)
2707: continue;
2708: // for some reason oracle sometimes returns
2709: // the types padded to a fixed length. I'm assuming
2710: // it doesn't harm for other DBMS as well to
2711: // trim the returned value...
2712: type = type.trim();
2713: if (hideIndexes && isIndexType(type))
2714: continue;
2715: result.add(type);
2716: }
2717: String additional = Settings.getInstance().getProperty(
2718: "workbench.db." + this .getDbId()
2719: + ".additional.tabletypes", null);
2720: List<String> addTypes = StringUtil.stringToList(additional,
2721: ",", true, true);
2722: result.addAll(addTypes);
2723: } catch (Exception e) {
2724: LogMgr.logError("DbMetadata.getTableTypes()",
2725: "Error retrieving table types", e);
2726: } finally {
2727: try {
2728: rs.close();
2729: } catch (Throwable e) {
2730: }
2731: }
2732: return result;
2733: }
2734:
2735: public String getSchemaTerm() {
2736: return this .schemaTerm;
2737: }
2738:
2739: public String getCatalogTerm() {
2740: return this .catalogTerm;
2741: }
2742:
2743: public static final int COLUMN_IDX_FK_DEF_FK_NAME = 0;
2744: public static final int COLUMN_IDX_FK_DEF_COLUMN_NAME = 1;
2745: public static final int COLUMN_IDX_FK_DEF_REFERENCE_COLUMN_NAME = 2;
2746: public static final int COLUMN_IDX_FK_DEF_UPDATE_RULE = 3;
2747: public static final int COLUMN_IDX_FK_DEF_DELETE_RULE = 4;
2748: public static final int COLUMN_IDX_FK_DEF_DEFERRABLE = 5;
2749: public static final int COLUMN_IDX_FK_DEF_UPDATE_RULE_VALUE = 6;
2750: public static final int COLUMN_IDX_FK_DEF_DELETE_RULE_VALUE = 7;
2751: public static final int COLUMN_IDX_FK_DEF_DEFERRABLE_RULE_VALUE = 8;
2752:
2753: public DataStore getExportedKeys(TableIdentifier tbl)
2754: throws SQLException {
2755: return getRawKeyList(tbl, true);
2756: }
2757:
2758: public DataStore getImportedKeys(TableIdentifier tbl)
2759: throws SQLException {
2760: return getRawKeyList(tbl, false);
2761: }
2762:
2763: private DataStore getRawKeyList(TableIdentifier tbl,
2764: boolean exported) throws SQLException {
2765: TableIdentifier table = tbl.createCopy();
2766: table.adjustCase(this .dbConnection);
2767:
2768: ResultSet rs;
2769: if (exported)
2770: rs = this .metaData.getExportedKeys(table.getCatalog(),
2771: table.getSchema(), table.getTableName());
2772: else
2773: rs = this .metaData.getImportedKeys(table.getCatalog(),
2774: table.getSchema(), table.getTableName());
2775:
2776: DataStore ds = new DataStore(rs, false);
2777: try {
2778: while (rs.next()) {
2779: int row = ds.addRow();
2780: ds.setValue(row, 0, rs.getString(1));
2781: ds.setValue(row, 1, rs.getString(2));
2782: ds.setValue(row, 2, rs.getString(3));
2783: ds.setValue(row, 3, rs.getString(4));
2784: ds.setValue(row, 4, rs.getString(5));
2785: ds.setValue(row, 5, rs.getString(6));
2786: ds.setValue(row, 6, rs.getString(7));
2787: ds.setValue(row, 7, rs.getString(8));
2788: ds.setValue(row, 8, new Integer(rs.getInt(9)));
2789: ds.setValue(row, 9, new Integer(rs.getInt(10)));
2790: ds.setValue(row, 10, rs.getString(11));
2791: String fk_name = this .fixFKName(rs.getString(12));
2792: ds.setValue(row, 11, fk_name);
2793: ds.setValue(row, 12, rs.getString(13));
2794: ds.setValue(row, 13, new Integer(rs.getInt(14)));
2795: }
2796: } finally {
2797: SqlUtil.closeResult(rs);
2798: }
2799: return ds;
2800: }
2801:
2802: /**
2803: * Works around a bug in Postgres' JDBC driver.
2804: * For Postgres strips everything after \000 for any
2805: * other DBMS the given name is returned without change
2806: */
2807: private String fixFKName(String aName) {
2808: if (aName == null)
2809: return null;
2810: if (!this .isPostgres)
2811: return aName;
2812: int pos = aName.indexOf("\\000");
2813: if (pos > -1) {
2814: // the Postgres JDBC driver seems to have a bug here,
2815: // because it appends the whole FK information to the fk name!
2816: // the actual FK name ends at the first \000
2817: return aName.substring(0, pos);
2818: }
2819: return aName;
2820: }
2821:
2822: public DataStore getForeignKeys(TableIdentifier table,
2823: boolean includeNumericRuleValue) {
2824: DataStore ds = this .getKeyList(table, true,
2825: includeNumericRuleValue);
2826: return ds;
2827: }
2828:
2829: public DataStore getReferencedBy(TableIdentifier table) {
2830: DataStore ds = this .getKeyList(table, false, false);
2831: return ds;
2832: }
2833:
2834: private DataStore getKeyList(TableIdentifier tableId,
2835: boolean getOwnFk, boolean includeNumericRuleValue) {
2836: String cols[];
2837: String refColName;
2838:
2839: if (getOwnFk) {
2840: refColName = "REFERENCES";
2841: } else {
2842: refColName = "REFERENCED BY";
2843: }
2844: int types[];
2845: int sizes[];
2846:
2847: if (includeNumericRuleValue) {
2848: cols = new String[] { "FK_NAME", "COLUMN", refColName,
2849: "UPDATE_RULE", "DELETE_RULE", "DEFERRABLE",
2850: "UPDATE_RULE_VALUE", "DELETE_RULE_VALUE",
2851: "DEFER_RULE_VALUE" };
2852: types = new int[] { Types.VARCHAR, Types.VARCHAR,
2853: Types.VARCHAR, Types.VARCHAR, Types.VARCHAR,
2854: Types.VARCHAR, Types.INTEGER, Types.INTEGER,
2855: Types.INTEGER };
2856: sizes = new int[] { 25, 10, 30, 12, 12, 15, 1, 1, 1 };
2857: } else {
2858: cols = new String[] { "FK_NAME", "COLUMN", refColName,
2859: "UPDATE_RULE", "DELETE_RULE", "DEFERRABLE" };
2860: types = new int[] { Types.VARCHAR, Types.VARCHAR,
2861: Types.VARCHAR, Types.VARCHAR, Types.VARCHAR,
2862: Types.VARCHAR };
2863: sizes = new int[] { 25, 10, 30, 12, 12, 15 };
2864: }
2865: DataStore ds = new DataStore(cols, types, sizes);
2866: if (tableId == null)
2867: return ds;
2868:
2869: ResultSet rs = null;
2870:
2871: try {
2872: TableIdentifier tbl = tableId.createCopy();
2873: tbl.adjustCase(this .dbConnection);
2874:
2875: int tableCol;
2876: int fkNameCol;
2877: int colCol;
2878: int fkColCol;
2879: int deleteActionCol = 11;
2880: int updateActionCol = 10;
2881: int schemaCol;
2882:
2883: if (getOwnFk) {
2884: rs = this .metaData.getImportedKeys(tbl.getCatalog(),
2885: tbl.getSchema(), tbl.getTableName());
2886: tableCol = 3;
2887: schemaCol = 2;
2888: fkNameCol = 12;
2889: colCol = 8;
2890: fkColCol = 4;
2891: } else {
2892: rs = this .metaData.getExportedKeys(tbl.getCatalog(),
2893: tbl.getSchema(), tbl.getTableName());
2894: tableCol = 7;
2895: schemaCol = 6;
2896: fkNameCol = 12;
2897: colCol = 4;
2898: fkColCol = 8;
2899: }
2900:
2901: while (rs.next()) {
2902: String table = rs.getString(tableCol);
2903: String fk_col = rs.getString(fkColCol);
2904: String col = rs.getString(colCol);
2905: String fk_name = this
2906: .fixFKName(rs.getString(fkNameCol));
2907: String schema = rs.getString(schemaCol);
2908: if (!this .ignoreSchema(schema)) {
2909: table = schema + "." + table;
2910: }
2911: int updateAction = rs.getInt(updateActionCol);
2912: String updActionDesc = this .dbSettings
2913: .getRuleDisplay(updateAction);
2914: int deleteAction = rs.getInt(deleteActionCol);
2915: String delActionDesc = this .dbSettings
2916: .getRuleDisplay(deleteAction);
2917:
2918: int deferrableCode = rs.getInt(14);
2919: String deferrable = this .dbSettings
2920: .getRuleDisplay(deferrableCode);
2921:
2922: int row = ds.addRow();
2923: ds.setValue(row, COLUMN_IDX_FK_DEF_FK_NAME, fk_name);
2924: ds.setValue(row, COLUMN_IDX_FK_DEF_COLUMN_NAME, col);
2925: ds.setValue(row,
2926: COLUMN_IDX_FK_DEF_REFERENCE_COLUMN_NAME, table
2927: + "." + fk_col);
2928: ds.setValue(row, COLUMN_IDX_FK_DEF_UPDATE_RULE,
2929: updActionDesc);
2930: ds.setValue(row, COLUMN_IDX_FK_DEF_DELETE_RULE,
2931: delActionDesc);
2932: ds.setValue(row, COLUMN_IDX_FK_DEF_DEFERRABLE,
2933: deferrable);
2934: if (includeNumericRuleValue) {
2935: ds.setValue(row,
2936: COLUMN_IDX_FK_DEF_DELETE_RULE_VALUE,
2937: new Integer(deleteAction));
2938: ds.setValue(row,
2939: COLUMN_IDX_FK_DEF_UPDATE_RULE_VALUE,
2940: new Integer(updateAction));
2941: ds.setValue(row,
2942: COLUMN_IDX_FK_DEF_DEFERRABLE_RULE_VALUE,
2943: new Integer(deferrableCode));
2944: }
2945: }
2946: } catch (Exception e) {
2947: LogMgr.logError("DbMetadata.getKeyList()",
2948: "Error when retrieving foreign keys", e);
2949: ds.reset();
2950: } finally {
2951: SqlUtil.closeResult(rs);
2952: }
2953: return ds;
2954: }
2955:
2956: private String getPkIndexName(DataStore anIndexDef) {
2957: if (anIndexDef == null)
2958: return null;
2959: int count = anIndexDef.getRowCount();
2960:
2961: String name = null;
2962: for (int row = 0; row < count; row++) {
2963: String is_pk = anIndexDef.getValue(row,
2964: COLUMN_IDX_TABLE_INDEXLIST_PK_FLAG).toString();
2965: if ("YES".equalsIgnoreCase(is_pk)) {
2966: name = anIndexDef.getValue(row,
2967: COLUMN_IDX_TABLE_INDEXLIST_INDEX_NAME)
2968: .toString();
2969: break;
2970: }
2971: }
2972: return name;
2973: }
2974:
2975: public SequenceReader getSequenceReader() {
2976: return this .sequenceReader;
2977: }
2978:
2979: public CharSequence getSequenceSource(String fullName) {
2980: String sequenceName = fullName;
2981: String schema = null;
2982:
2983: int pos = fullName.indexOf('.');
2984: if (pos > 0) {
2985: sequenceName = fullName.substring(pos);
2986: schema = fullName.substring(0, pos - 1);
2987: }
2988: return this .getSequenceSource(null, schema, sequenceName);
2989: }
2990:
2991: public CharSequence getSequenceSource(String aCatalog,
2992: String aSchema, String aSequence) {
2993: if (this .sequenceReader != null) {
2994: if (aSchema == null) {
2995: aSchema = this .getCurrentSchema();
2996: }
2997: return this .sequenceReader.getSequenceSource(aSchema,
2998: aSequence);
2999: }
3000: return StringUtil.EMPTY_STRING;
3001: }
3002:
3003: public boolean isTableType(String type) {
3004: for (String t : tableTypesTable) {
3005: if (t.equalsIgnoreCase(type))
3006: return true;
3007: }
3008: return false;
3009: }
3010:
3011: /**
3012: * Checks if the current DBMS supports synonyms.
3013: * @return true if the synonym support is available (basically if synonymReader != null)
3014: */
3015: public boolean supportsSynonyms() {
3016: return this .synonymReader != null;
3017: }
3018:
3019: /**
3020: * Return the underlying table of a synonym.
3021: * @param synonym the synonym definition
3022: *
3023: * @return the table to which the synonym points or null if the passed
3024: * name does not reference a synonym or if the DBMS does not support synonyms
3025: * @see #getSynonymTable(String, String)
3026: */
3027: public TableIdentifier getSynonymTable(TableIdentifier synonym) {
3028: if (this .synonymReader == null)
3029: return null;
3030: TableIdentifier tbl = synonym.createCopy();
3031: tbl.adjustCase(this .dbConnection);
3032: return getSynonymTable(tbl.getSchema(), tbl.getTableName());
3033: }
3034:
3035: /**
3036: * Return the underlying table of a synonym.
3037: *
3038: * @param schema the schema of the synonym
3039: * @param synonym the name of the synonym
3040: *
3041: * @return the table to which the synonym points or null if the passed
3042: * name does not reference a synonym or if the DBMS does not support synonyms
3043: * @see #getSynonymTable(String, String)
3044: */
3045: protected TableIdentifier getSynonymTable(String schema,
3046: String synonym) {
3047: if (this .synonymReader == null)
3048: return null;
3049: TableIdentifier id = null;
3050: try {
3051: id = this .synonymReader.getSynonymTable(this .dbConnection
3052: .getSqlConnection(), schema, synonym);
3053: if (id != null && id.getType() == null) {
3054: String type = getTableType(id);
3055: id.setType(type);
3056: }
3057: } catch (Exception e) {
3058: LogMgr.logError("DbMetadata.getSynonymTable()",
3059: "Could not retrieve table for synonym", e);
3060: }
3061: return id;
3062: }
3063:
3064: /**
3065: * Return the SQL statement to recreate the given synonym.
3066: * @return the SQL to create the synonym.
3067: */
3068: public String getSynonymSource(TableIdentifier synonym) {
3069: if (this .synonymReader == null)
3070: return StringUtil.EMPTY_STRING;
3071: String result = null;
3072: TableIdentifier tbl = synonym.createCopy();
3073: tbl.adjustCase(dbConnection);
3074: try {
3075: result = this .synonymReader.getSynonymSource(
3076: this .dbConnection.getSqlConnection(), tbl
3077: .getSchema(), tbl.getTableName());
3078: } catch (Exception e) {
3079: result = StringUtil.EMPTY_STRING;
3080: }
3081:
3082: return result;
3083: }
3084:
3085: /**
3086: * Return the SQL statement to re-create the given table. (in the dialect for the
3087: * current DBMS)
3088: *
3089: * @return the SQL statement to create the given table.
3090: * @param table the table for which the source should be retrievedcatalog The catalog in which the table is defined. This should be null if the DBMS does not support catalogs
3091: * @param includeDrop If true, a DROP TABLE statement will be included in the generated SQL script.
3092: * @param includeFk if true, the foreign key constraints will be added after the CREATE TABLE
3093: * @throws SQLException
3094: */
3095: public String getTableSource(TableIdentifier table,
3096: boolean includeDrop, boolean includeFk) throws SQLException {
3097: if (getViewTypeName().equalsIgnoreCase(table.getType())) {
3098: CharSequence s = getExtendedViewSource(table, includeDrop);
3099: if (s == null)
3100: return null;
3101: return s.toString();
3102: }
3103: List<ColumnIdentifier> cols = getTableColumns(table);
3104: DataStore index = this .getTableIndexInformation(table);
3105: TableIdentifier tbl = table.createCopy();
3106: tbl.adjustCase(this .dbConnection);
3107: DataStore fkDef = null;
3108: if (includeFk)
3109: fkDef = this .getForeignKeys(tbl, false);
3110: String source = this .getTableSource(table, cols, index, fkDef,
3111: includeDrop, null, includeFk);
3112: return source;
3113: }
3114:
3115: public String getTableSource(TableIdentifier table,
3116: List<ColumnIdentifier> columns, String tableNameToUse) {
3117: return getTableSource(table, columns, null, null, false,
3118: tableNameToUse, true);
3119: }
3120:
3121: public String getTableSource(TableIdentifier table,
3122: DataStore columns, DataStore aIndexDef, DataStore aFkDef,
3123: boolean includeDrop, String tableNameToUse) {
3124: List<ColumnIdentifier> cols = this
3125: .createColumnIdentifiers(columns);
3126: return getTableSource(table, cols, aIndexDef, aFkDef,
3127: includeDrop, tableNameToUse, true);
3128: }
3129:
3130: protected String getMViewSource(TableIdentifier table,
3131: List<ColumnIdentifier> columns, DataStore aIndexDef,
3132: boolean includeDrop) {
3133: StringBuilder result = new StringBuilder(250);
3134:
3135: try {
3136: result.append(getExtendedViewSource(table, includeDrop));
3137: } catch (SQLException e) {
3138: result.append(ExceptionUtil.getDisplay(e));
3139: }
3140: result.append("\n\n");
3141:
3142: StringBuilder indexSource = this .indexReader.getIndexSource(
3143: table, aIndexDef, table.getTableName());
3144:
3145: result.append(indexSource);
3146: if (this .dbSettings.ddlNeedsCommit()) {
3147: result.append('\n');
3148: result.append("COMMIT;");
3149: result.append('\n');
3150: }
3151: return result.toString();
3152: }
3153:
3154: public String getTableSource(TableIdentifier table,
3155: List<ColumnIdentifier> columns, DataStore aIndexDef,
3156: DataStore aFkDef, boolean includeDrop,
3157: String tableNameToUse, boolean includeFk) {
3158: if (columns == null || columns.size() == 0)
3159: return StringUtil.EMPTY_STRING;
3160:
3161: if (table.getType().equals(MVIEW_NAME)) {
3162: return getMViewSource(table, columns, aIndexDef,
3163: includeDrop);
3164: }
3165:
3166: StringBuilder result = new StringBuilder(250);
3167:
3168: Map<String, String> columnConstraints = this
3169: .getColumnConstraints(table);
3170:
3171: result.append(generateCreateObject(includeDrop, "TABLE",
3172: (tableNameToUse == null ? table.getTableName()
3173: : tableNameToUse)));
3174: result.append("\n(\n");
3175:
3176: List<String> pkCols = new LinkedList<String>();
3177: int maxColLength = 0;
3178: int maxTypeLength = 0;
3179:
3180: // calculate the longest column name, so that the display can be formatted
3181: for (ColumnIdentifier column : columns) {
3182: String colName = quoteObjectname(column.getColumnName());
3183: String type = column.getDbmsType();
3184: maxColLength = Math.max(maxColLength, colName.length());
3185: maxTypeLength = Math.max(maxTypeLength,
3186: (type != null ? type.length() : 0));
3187: }
3188: maxColLength++;
3189: maxTypeLength++;
3190:
3191: // Some RDBMS require the "DEFAULT" clause before the [NOT] NULL clause
3192: boolean defaultBeforeNull = this .dbSettings
3193: .getDefaultBeforeNull();
3194: String nullKeyword = Settings.getInstance().getProperty(
3195: "workbench.db.nullkeyword." + getDbId(), "NULL");
3196: boolean includeCommentInTableSource = Settings.getInstance()
3197: .getBoolProperty(
3198: "workbench.db.colcommentinline."
3199: + this .getDbId(), false);
3200:
3201: String lineEnding = Settings.getInstance()
3202: .getInternalEditorLineEnding();
3203:
3204: Iterator<ColumnIdentifier> itr = columns.iterator();
3205: while (itr.hasNext()) {
3206: ColumnIdentifier column = itr.next();
3207: String colName = column.getColumnName();
3208: String quotedColName = quoteObjectname(colName);
3209: String type = column.getDbmsType();
3210: if (type == null)
3211: type = "";
3212: String def = column.getDefaultValue();
3213: int typeLength = type.length();
3214: result.append(" ");
3215: result.append(quotedColName);
3216:
3217: if (column.isPkColumn()
3218: && (!this .isFirstSql || this .isFirstSql
3219: && !"sequence".equals(type))) {
3220: pkCols.add(colName.trim());
3221: }
3222:
3223: for (int k = 0; k < maxColLength - quotedColName.length(); k++)
3224: result.append(' ');
3225: result.append(type);
3226:
3227: // Check if any additional keywords are coming after
3228: // the datatype. If yes, we fill the line with spaces
3229: // to align the keywords properly
3230: if (!StringUtil.isEmptyString(def)
3231: || (!column.isNullable())
3232: || (column.isNullable() && this .useNullKeyword)) {
3233: for (int k = 0; k < maxTypeLength - typeLength; k++)
3234: result.append(' ');
3235: }
3236:
3237: if (defaultBeforeNull && !StringUtil.isEmptyString(def)) {
3238: result.append(" DEFAULT ");
3239: result.append(def.trim());
3240: }
3241:
3242: if (this .isFirstSql && "sequence".equals(type)) {
3243: // with FirstSQL a column of type "sequence" is always the primary key
3244: result.append(" PRIMARY KEY");
3245: } else if (column.isNullable()) {
3246: if (this .useNullKeyword) {
3247: result.append(' ');
3248: result.append(nullKeyword);
3249: }
3250: } else {
3251: result.append(" NOT NULL");
3252: }
3253:
3254: if (!defaultBeforeNull && !StringUtil.isEmptyString(def)) {
3255: result.append(" DEFAULT ");
3256: result.append(def.trim());
3257: }
3258:
3259: String constraint = columnConstraints.get(colName);
3260: if (constraint != null && constraint.length() > 0) {
3261: result.append(' ');
3262: result.append(constraint);
3263: }
3264:
3265: if (includeCommentInTableSource
3266: && !StringUtil.isEmptyString(column.getComment())) {
3267: result.append(" COMMENT '");
3268: result.append(column.getComment());
3269: result.append('\'');
3270: }
3271:
3272: if (itr.hasNext())
3273: result.append(',');
3274: result.append(lineEnding);
3275: }
3276:
3277: String cons = this .getTableConstraints(table, " ");
3278: if (cons != null && cons.length() > 0) {
3279: result.append(" ,");
3280: result.append(cons);
3281: result.append(lineEnding);
3282: }
3283:
3284: if (this .createInlineConstraints && pkCols.size() > 0) {
3285: result.append(lineEnding + " ,PRIMARY KEY (");
3286: result.append(StringUtil.listToString(pkCols, ','));
3287: result.append(")" + lineEnding);
3288:
3289: if (includeFk) {
3290: StringBuilder fk = this .getFkSource(table, aFkDef,
3291: tableNameToUse, createInlineConstraints);
3292: if (fk.length() > 0) {
3293: result.append(fk);
3294: }
3295: }
3296: }
3297:
3298: result.append(");" + lineEnding);
3299: // end of CREATE TABLE
3300:
3301: if (!this .createInlineConstraints && pkCols.size() > 0) {
3302: String name = this .getPkIndexName(aIndexDef);
3303: CharSequence pkSource = getPkSource(
3304: (tableNameToUse == null ? table
3305: : new TableIdentifier(tableNameToUse)),
3306: pkCols, name);
3307: result.append(pkSource);
3308: }
3309:
3310: StringBuilder indexSource = this .indexReader.getIndexSource(
3311: table, aIndexDef, tableNameToUse);
3312: if (!StringUtil.isEmptyString(indexSource)) {
3313: result.append(lineEnding);
3314: result.append(indexSource);
3315: }
3316:
3317: if (!this .createInlineConstraints && includeFk) {
3318: CharSequence fk = this .getFkSource(table, aFkDef,
3319: tableNameToUse, createInlineConstraints);
3320: if (!StringUtil.isEmptyString(fk)) {
3321: result.append(lineEnding);
3322: result.append(fk);
3323: }
3324: }
3325:
3326: String tableComment = this .getTableCommentSql(table);
3327: if (!StringUtil.isEmptyString(tableComment)) {
3328: result.append(lineEnding);
3329: result.append(tableComment);
3330: }
3331:
3332: StringBuilder colComments = this .getTableColumnCommentsSql(
3333: table, columns);
3334: if (!StringUtil.isEmptyString(colComments)) {
3335: result.append(lineEnding);
3336: result.append(colComments);
3337: }
3338:
3339: StringBuilder grants = this .getTableGrantSource(table);
3340: if (grants.length() > 0) {
3341: result.append(lineEnding);
3342: result.append(grants);
3343: }
3344:
3345: if (this .dbSettings.ddlNeedsCommit()) {
3346: result.append(lineEnding);
3347: result.append("COMMIT;");
3348: }
3349:
3350: result.append(lineEnding);
3351:
3352: return result.toString();
3353: }
3354:
3355: private boolean isSystemConstraintName(String name) {
3356: if (name == null)
3357: return false;
3358: String regex = Settings.getInstance().getProperty(
3359: "workbench.db." + this .getDbId()
3360: + ".constraints.systemname", null);
3361: if (StringUtil.isEmptyString(regex))
3362: return false;
3363:
3364: try {
3365: Pattern p = Pattern.compile(regex);
3366: Matcher m = p.matcher(name);
3367: return m.matches();
3368: } catch (Exception e) {
3369: LogMgr.logError("DbMetadata.isSystemConstraintName()",
3370: "Error in regex", e);
3371: }
3372: return false;
3373: }
3374:
3375: /**
3376: * Builds an ALTER TABLE to add a primary key definition for the given tablename.
3377: *
3378: * @param table
3379: * @param pkCols
3380: * @param pkName
3381: * @return
3382: */
3383: public CharSequence getPkSource(TableIdentifier table, List pkCols,
3384: String pkName) {
3385: String template = metaSqlMgr.getPrimaryKeyTemplate();
3386:
3387: if (StringUtil.isEmptyString(template))
3388: return "";
3389:
3390: StringBuilder result = new StringBuilder(100);
3391: String tablename = table.getTableExpression(this .dbConnection);
3392:
3393: template = StringUtil.replace(template,
3394: MetaDataSqlManager.TABLE_NAME_PLACEHOLDER, tablename);
3395: template = StringUtil.replace(template,
3396: MetaDataSqlManager.COLUMN_LIST_PLACEHOLDER, StringUtil
3397: .listToString(pkCols, ','));
3398:
3399: if (isSystemConstraintName(pkName)) {
3400: pkName = null;
3401: }
3402:
3403: if (pkName == null
3404: && Settings.getInstance().getAutoGeneratePKName()) {
3405: pkName = "pk_" + tablename.toLowerCase();
3406: }
3407:
3408: if (isKeyword(pkName))
3409: pkName = this .quoteCharacter + pkName + this .quoteCharacter;
3410:
3411: if (StringUtil.isEmptyString(pkName)) {
3412: pkName = ""; // remove placeholder if no name is available
3413: template = StringUtil.replace(template, " CONSTRAINT ", ""); // remove CONSTRAINT KEYWORD if no name is available
3414: }
3415:
3416: template = StringUtil.replace(template,
3417: MetaDataSqlManager.PK_NAME_PLACEHOLDER, pkName);
3418: result.append(template);
3419: result.append(";\n");
3420:
3421: return result;
3422: }
3423:
3424: /**
3425: * Return constraints defined for each column in the given table.
3426: * @param table The table to check
3427: * @return A Map with columns and their constraints. The keys to the Map are column names
3428: * The value is the SQL source for the column. The actual retrieval is delegated to a {@link ConstraintReader}
3429: * @see ConstraintReader#getColumnConstraints(java.sql.Connection, TableIdentifier)
3430: */
3431: public Map<String, String> getColumnConstraints(
3432: TableIdentifier table) {
3433: Map<String, String> columnConstraints = Collections.emptyMap();
3434: if (this .constraintReader != null) {
3435: try {
3436: columnConstraints = this .constraintReader
3437: .getColumnConstraints(this .dbConnection
3438: .getSqlConnection(), table);
3439: } catch (Exception e) {
3440: if (this .isPostgres)
3441: try {
3442: this .dbConnection.rollback();
3443: } catch (Throwable th) {
3444: }
3445: columnConstraints = Collections.emptyMap();
3446: }
3447: }
3448: return columnConstraints;
3449: }
3450:
3451: /**
3452: * Return the SQL source for check constraints defined for the table. This is
3453: * delegated to a {@link ConstraintReader}
3454: * @return A String with the table constraints. If no constrains exist, a null String is returned
3455: * @param tbl The table to check
3456: * @param indent A String defining the indention for the source code
3457: */
3458: public String getTableConstraints(TableIdentifier tbl, String indent) {
3459: if (this .constraintReader == null)
3460: return null;
3461: String cons = null;
3462: Savepoint sp = null;
3463: try {
3464: if (dbSettings.useSavePointForDML()) {
3465: sp = this .dbConnection.setSavepoint();
3466: }
3467: cons = this .constraintReader.getTableConstraints(
3468: dbConnection.getSqlConnection(), tbl, indent);
3469: dbConnection.releaseSavepoint(sp);
3470: } catch (SQLException e) {
3471: LogMgr.logError("DbMetadata.getTableConstraints()",
3472: "Error retrieving table constraints", e);
3473: dbConnection.rollback(sp);
3474: sp = null;
3475: cons = null;
3476: }
3477: return cons;
3478: }
3479:
3480: /**
3481: * Return the SQL that is needed to re-create the comment on the given columns.
3482: * The syntax to be used, can be configured in the ColumnCommentStatements.xml file.
3483: */
3484: public StringBuilder getTableColumnCommentsSql(
3485: TableIdentifier table, List<ColumnIdentifier> columns) {
3486: String columnStatement = metaSqlMgr.getColumnCommentSql();
3487: if (columnStatement == null
3488: || columnStatement.trim().length() == 0)
3489: return null;
3490: StringBuilder result = new StringBuilder(columns.size() * 25);
3491: for (ColumnIdentifier col : columns) {
3492: String column = col.getColumnName();
3493: String comment = col.getComment();
3494: if (Settings.getInstance().getIncludeEmptyComments()
3495: || comment != null && comment.trim().length() > 0) {
3496: try {
3497: String commentSql = columnStatement
3498: .replaceAll(
3499: MetaDataSqlManager.COMMENT_TABLE_PLACEHOLDER,
3500: table.getTableName());
3501: commentSql = StringUtil
3502: .replace(
3503: commentSql,
3504: MetaDataSqlManager.COMMENT_COLUMN_PLACEHOLDER,
3505: column);
3506: commentSql = StringUtil.replace(commentSql,
3507: MetaDataSqlManager.COMMENT_PLACEHOLDER,
3508: comment == null ? "" : comment.replaceAll(
3509: "'", "''"));
3510: result.append(commentSql);
3511: result.append("\n");
3512: } catch (Exception e) {
3513: LogMgr.logError(
3514: "DbMetadata.getTableColumnCommentsSql()",
3515: "Error creating comments SQL for remark="
3516: + comment, e);
3517: }
3518: }
3519: }
3520: return result;
3521: }
3522:
3523: /**
3524: * Return the SQL that is needed to re-create the comment on the given table.
3525: * The syntax to be used, can be configured in the TableCommentStatements.xml file.
3526: */
3527: public String getTableCommentSql(TableIdentifier table) {
3528: String commentStatement = metaSqlMgr.getTableCommentSql();
3529: if (commentStatement == null
3530: || commentStatement.trim().length() == 0)
3531: return null;
3532:
3533: String comment = this .getTableComment(table);
3534: String result = null;
3535: if (Settings.getInstance().getIncludeEmptyComments()
3536: || comment != null && comment.trim().length() > 0) {
3537: result = commentStatement.replaceAll(
3538: MetaDataSqlManager.COMMENT_TABLE_PLACEHOLDER, table
3539: .getTableName());
3540: result = result.replaceAll(
3541: MetaDataSqlManager.COMMENT_PLACEHOLDER,
3542: comment == null ? "" : comment
3543: .replaceAll("'", "''"));
3544: }
3545: return result;
3546: }
3547:
3548: public String getTableComment(TableIdentifier tbl) {
3549: TableIdentifier table = tbl.createCopy();
3550: table.adjustCase(this .dbConnection);
3551: ResultSet rs = null;
3552: String result = null;
3553: Savepoint sp = null;
3554: try {
3555: if (dbSettings.useSavePointForDML()) {
3556: sp = dbConnection.setSavepoint();
3557: }
3558: rs = this .metaData.getTables(table.getCatalog(), table
3559: .getSchema(), table.getTableName(), null);
3560: if (rs.next()) {
3561: result = rs.getString("REMARKS");
3562: }
3563: dbConnection.releaseSavepoint(sp);
3564: } catch (Exception e) {
3565: dbConnection.rollback(sp);
3566: LogMgr.logError("DbMetadata.getTableComment()",
3567: "Error retrieving comment for table "
3568: + table.getTableExpression(), e);
3569: result = null;
3570: } finally {
3571: SqlUtil.closeResult(rs);
3572: }
3573:
3574: return result;
3575: }
3576:
3577: public StringBuilder getFkSource(TableIdentifier table) {
3578: DataStore fkDef = this .getForeignKeys(table, false);
3579: return getFkSource(table, fkDef, null, createInlineConstraints);
3580: }
3581:
3582: /**
3583: * Return a SQL script to re-create the Foreign key definition for the given table.
3584: *
3585: * @param aTable the tablename for which the foreign keys should be created
3586: * @param aFkDef a DataStore with the FK definition as returned by #getForeignKeys()
3587: *
3588: * @return a SQL statement to add the foreign key definitions to the given table
3589: */
3590: public StringBuilder getFkSource(TableIdentifier table,
3591: DataStore aFkDef, String tableNameToUse,
3592: boolean forInlineUse) {
3593: if (aFkDef == null)
3594: return StringUtil.emptyBuffer();
3595: int count = aFkDef.getRowCount();
3596: if (count == 0)
3597: return StringUtil.emptyBuffer();
3598:
3599: String template = metaSqlMgr
3600: .getForeignKeyTemplate(forInlineUse);
3601:
3602: // collects all columns from the base table mapped to the
3603: // defining foreign key constraing.
3604: // The fk name is the key.
3605: // to the hashtable. The entry will be a HashSet containing the column names
3606: // this ensures that each column will only be used once per fk definition
3607: // (the postgres driver returns some columns twice!)
3608: HashMap<String, List<String>> fkCols = new HashMap<String, List<String>>();
3609:
3610: // this hashmap contains the columns of the referenced table
3611: HashMap<String, List<String>> fkTarget = new HashMap<String, List<String>>();
3612:
3613: HashMap<String, String> fks = new HashMap<String, String>();
3614: HashMap<String, String> updateRules = new HashMap<String, String>();
3615: HashMap<String, String> deleteRules = new HashMap<String, String>();
3616: HashMap<String, String> deferrable = new HashMap<String, String>();
3617:
3618: String fkname;
3619: String col;
3620: String fkCol;
3621: String updateRule;
3622: String deleteRule;
3623: String deferRule;
3624:
3625: for (int i = 0; i < count; i++) {
3626: //"FK_NAME", "COLUMN_NAME", "REFERENCES"};
3627: fkname = aFkDef.getValueAsString(i,
3628: COLUMN_IDX_FK_DEF_FK_NAME);
3629: col = aFkDef.getValueAsString(i,
3630: COLUMN_IDX_FK_DEF_COLUMN_NAME);
3631: fkCol = aFkDef.getValueAsString(i,
3632: COLUMN_IDX_FK_DEF_REFERENCE_COLUMN_NAME);
3633: updateRule = aFkDef.getValueAsString(i,
3634: COLUMN_IDX_FK_DEF_UPDATE_RULE);
3635: deleteRule = aFkDef.getValueAsString(i,
3636: COLUMN_IDX_FK_DEF_DELETE_RULE);
3637: deferRule = aFkDef.getValueAsString(i,
3638: COLUMN_IDX_FK_DEF_DEFERRABLE);
3639:
3640: List<String> colList = fkCols.get(fkname);
3641: if (colList == null) {
3642: colList = new LinkedList<String>();
3643: fkCols.put(fkname, colList);
3644: }
3645: colList.add(col);
3646: updateRules.put(fkname, updateRule);
3647: deleteRules.put(fkname, deleteRule);
3648: deferrable.put(fkname, deferRule);
3649:
3650: colList = fkTarget.get(fkname);
3651: if (colList == null) {
3652: colList = new LinkedList<String>();
3653: fkTarget.put(fkname, colList);
3654: }
3655: colList.add(fkCol);
3656: }
3657:
3658: // now put the real statements together
3659: Iterator<Map.Entry<String, List<String>>> names = fkCols
3660: .entrySet().iterator();
3661: while (names.hasNext()) {
3662: Map.Entry<String, List<String>> mapentry = names.next();
3663: fkname = mapentry.getKey();
3664: List<String> colList = mapentry.getValue();
3665:
3666: String stmt = fks.get(fkname);
3667: if (stmt == null) {
3668: // first time we hit this FK definition in this loop
3669: stmt = template;
3670: }
3671: stmt = StringUtil.replace(stmt,
3672: MetaDataSqlManager.TABLE_NAME_PLACEHOLDER,
3673: (tableNameToUse == null ? table.getTableName()
3674: : tableNameToUse));
3675:
3676: if (this .isSystemConstraintName(fkname)) {
3677: stmt = StringUtil.replace(stmt,
3678: MetaDataSqlManager.FK_NAME_PLACEHOLDER, "");
3679: stmt = StringUtil.replace(stmt, " CONSTRAINT ", "");
3680: } else {
3681: stmt = StringUtil.replace(stmt,
3682: MetaDataSqlManager.FK_NAME_PLACEHOLDER, fkname);
3683: }
3684:
3685: String entry = StringUtil.listToString(colList, ',');
3686: stmt = StringUtil.replace(stmt,
3687: MetaDataSqlManager.COLUMN_LIST_PLACEHOLDER, entry);
3688: String rule = updateRules.get(fkname);
3689: stmt = StringUtil.replace(stmt,
3690: MetaDataSqlManager.FK_UPDATE_RULE, " ON UPDATE "
3691: + rule);
3692: rule = deleteRules.get(fkname);
3693: if (this .isOracle()) {
3694: // Oracle does not allow ON DELETE RESTRICT, so we'll have to
3695: // remove the placeholder completely
3696: if ("restrict".equalsIgnoreCase(rule)) {
3697: stmt = MetaDataSqlManager.removePlaceholder(stmt,
3698: MetaDataSqlManager.FK_DELETE_RULE, true);
3699: } else {
3700: stmt = StringUtil.replace(stmt,
3701: MetaDataSqlManager.FK_DELETE_RULE,
3702: " ON DELETE " + rule);
3703: }
3704: } else {
3705: stmt = StringUtil.replace(stmt,
3706: MetaDataSqlManager.FK_DELETE_RULE,
3707: " ON DELETE " + rule);
3708: }
3709:
3710: rule = getDeferrableVerb(deferrable.get(fkname));
3711: if (StringUtil.isEmptyString(rule)) {
3712: stmt = MetaDataSqlManager.removePlaceholder(stmt,
3713: MetaDataSqlManager.DEFERRABLE, true);
3714: } else {
3715: stmt = StringUtil.replace(stmt,
3716: MetaDataSqlManager.DEFERRABLE, rule.trim());
3717: }
3718:
3719: colList = fkTarget.get(fkname);
3720: if (colList == null) {
3721: LogMgr
3722: .logError(
3723: "DbMetadata.getFkSource()",
3724: "Retrieved a null list for constraing ["
3725: + fkname
3726: + "] but should contain a list for table ["
3727: + table.getTableName() + "]",
3728: null);
3729: continue;
3730: }
3731:
3732: Iterator itr = colList.iterator();
3733: StringBuilder colListBuffer = new StringBuilder(30);
3734: String targetTable = null;
3735: boolean first = true;
3736:
3737: while (itr.hasNext()) {
3738: col = (String) itr.next();//tok.nextToken();
3739: int pos = col.lastIndexOf('.');
3740: if (targetTable == null) {
3741: // The last element has to be the column name!
3742: String t = col.substring(0, pos);
3743: TableIdentifier tbl = new TableIdentifier(t);
3744: targetTable = tbl
3745: .getTableExpression(this .dbConnection);
3746: }
3747: if (!first) {
3748: colListBuffer.append(',');
3749: } else {
3750: first = false;
3751: }
3752: colListBuffer.append(col.substring(pos + 1));
3753: }
3754: stmt = StringUtil.replace(stmt,
3755: MetaDataSqlManager.FK_TARGET_TABLE_PLACEHOLDER,
3756: targetTable);
3757: stmt = StringUtil.replace(stmt,
3758: MetaDataSqlManager.FK_TARGET_COLUMNS_PLACEHOLDER,
3759: colListBuffer.toString());
3760: fks.put(fkname, stmt.trim());
3761: }
3762: StringBuilder fk = new StringBuilder();
3763:
3764: String nl = Settings.getInstance()
3765: .getInternalEditorLineEnding();
3766:
3767: Iterator<String> values = fks.values().iterator();
3768: while (values.hasNext()) {
3769: if (forInlineUse) {
3770: fk.append(" ,");
3771: fk.append(values.next());
3772: } else {
3773: fk.append(values.next());
3774: fk.append(';');
3775: fk.append(nl);
3776: }
3777: fk.append(nl);
3778: }
3779:
3780: return fk;
3781: }
3782:
3783: private String getDeferrableVerb(String type) {
3784: if (dbSettings.isNotDeferrable(type))
3785: return StringUtil.EMPTY_STRING;
3786: return " DEFERRABLE " + type;
3787: }
3788:
3789: /**
3790: * Build the SQL statement to create an Index on the given table.
3791: * @param aTable - The table name for which the index should be constructed
3792: * @param indexName - The name of the Index
3793: * @param unique - Should the index be unique
3794: * @param columnList - The columns that should build the index
3795: */
3796: public String buildIndexSource(TableIdentifier aTable,
3797: String indexName, boolean unique, String[] columnList) {
3798: return this .indexReader.buildCreateIndexSql(aTable, indexName,
3799: unique, columnList);
3800: }
3801:
3802: /**
3803: * Return the GRANTs for the given table
3804: *
3805: * Some JDBC drivers return all GRANT privileges separately even if the original
3806: * GRANT was a GRANT ALL ON object TO user.
3807: *
3808: * @return a List with TableGrant objects.
3809: */
3810: public Collection<TableGrant> getTableGrants(TableIdentifier table) {
3811: Collection<TableGrant> result = new HashSet<TableGrant>();
3812: ResultSet rs = null;
3813: try {
3814: TableIdentifier tbl = table.createCopy();
3815: tbl.adjustCase(this .dbConnection);
3816: rs = this .metaData.getTablePrivileges(tbl.getCatalog(), tbl
3817: .getSchema(), tbl.getTableName());
3818: while (rs.next()) {
3819: String from = rs.getString(4);
3820: String to = rs.getString(5);
3821: String what = rs.getString(6);
3822: boolean grantable = StringUtil.stringToBool(rs
3823: .getString(7));
3824: TableGrant grant = new TableGrant(to, what, grantable);
3825: result.add(grant);
3826: }
3827: } catch (Exception e) {
3828: LogMgr.logError("DbMetadata.getTableGrants()",
3829: "Error when retrieving table privileges", e);
3830: } finally {
3831: try {
3832: rs.close();
3833: } catch (Throwable th) {
3834: }
3835: }
3836: return result;
3837: }
3838:
3839: /**
3840: * Creates an SQL Statement which can be used to re-create the GRANTs on the
3841: * given table.
3842: *
3843: * @return SQL script to GRANT access to the table.
3844: */
3845: public StringBuilder getTableGrantSource(TableIdentifier table) {
3846: Collection<TableGrant> grantList = this .getTableGrants(table);
3847: StringBuilder result = new StringBuilder(200);
3848: int count = grantList.size();
3849:
3850: // as several grants to several users can be made, we need to collect them
3851: // first, in order to be able to build the complete statements
3852: Map<String, List<String>> grants = new HashMap<String, List<String>>(
3853: count);
3854:
3855: for (TableGrant grant : grantList) {
3856: String grantee = grant.getGrantee();
3857: String priv = grant.getPrivilege();
3858: if (priv == null)
3859: continue;
3860: List<String> privs = grants.get(grantee);
3861: if (privs == null) {
3862: privs = new LinkedList<String>();
3863: grants.put(grantee, privs);
3864: }
3865: privs.add(priv.trim());
3866: }
3867: Iterator<Entry<String, List<String>>> itr = grants.entrySet()
3868: .iterator();
3869:
3870: String user = dbConnection.getCurrentUser();
3871: while (itr.hasNext()) {
3872: Entry<String, List<String>> entry = itr.next();
3873: String grantee = entry.getKey();
3874: // Ignore grants to ourself
3875: if (user.equalsIgnoreCase(grantee))
3876: continue;
3877:
3878: List<String> privs = entry.getValue();
3879: result.append("GRANT ");
3880: result.append(StringUtil.listToString(privs, ','));
3881: result.append(" ON ");
3882: result.append(table.getTableExpression(this .dbConnection));
3883: result.append(" TO ");
3884: result.append(grantee);
3885: result.append(";\n");
3886: }
3887: return result;
3888: }
3889:
3890: /**
3891: * Returns the errors available for the given object and type. This call
3892: * is delegated to the available {@link ErrorInformationReader}
3893: * @return extended error information if the current DBMS is Oracle. An empty string otherwise.
3894: * @see ErrorInformationReader
3895: */
3896: public String getExtendedErrorInfo(String schema,
3897: String objectName, String objectType) {
3898: if (this .errorInfoReader == null)
3899: return StringUtil.EMPTY_STRING;
3900: return this .errorInfoReader.getErrorInfo(schema, objectName,
3901: objectType);
3902: }
3903:
3904: /**
3905: * With v1.8 of HSQLDB the tables that list table and view
3906: * information, are stored in the INFORMATION_SCHEMA schema.
3907: * Although the table names are the same, prior to 1.8 you
3908: * cannot use the schema, so it needs to be removed
3909: */
3910: private String adjustHsqlQuery(String query) {
3911: if (!this .isHsql)
3912: return query;
3913: if (JdbcUtils.hasMinimumServerVersion(dbConnection, "1.8"))
3914: return query;
3915:
3916: Pattern p = Pattern.compile("\\sINFORMATION_SCHEMA\\.",
3917: Pattern.CASE_INSENSITIVE);
3918: Matcher m = p.matcher(query);
3919: return m.replaceAll(" ");
3920: }
3921:
3922: }
|