0001: /*
0002: * Copyright (C) 2006 Rob Manning
0003: * manningr@users.sourceforge.net
0004: *
0005: * This program is free software; you can redistribute it and/or
0006: * modify it under the terms of the GNU General Public License
0007: * as published by the Free Software Foundation; either version 2
0008: * of the License, or any later version.
0009: *
0010: * This program is distributed in the hope that it will be useful,
0011: * but WITHOUT ANY WARRANTY; without even the implied warranty of
0012: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
0013: * GNU General Public License for more details.
0014: *
0015: * You should have received a copy of the GNU General Public License
0016: * along with this program; if not, write to the Free Software
0017: * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
0018: */
0019:
0020: package net.sourceforge.squirrel_sql.fw.dialects;
0021:
0022: import java.sql.DatabaseMetaData;
0023: import java.sql.SQLException;
0024: import java.util.ArrayList;
0025: import java.util.Arrays;
0026: import java.util.Collections;
0027: import java.util.Hashtable;
0028: import java.util.List;
0029: import java.util.Vector;
0030:
0031: import net.sourceforge.squirrel_sql.fw.sql.DatabaseObjectType;
0032: import net.sourceforge.squirrel_sql.fw.sql.ForeignKeyInfo;
0033: import net.sourceforge.squirrel_sql.fw.sql.ISQLDatabaseMetaData;
0034: import net.sourceforge.squirrel_sql.fw.sql.ITableInfo;
0035: import net.sourceforge.squirrel_sql.fw.sql.IndexInfo;
0036: import net.sourceforge.squirrel_sql.fw.sql.JDBCTypeMapper;
0037: import net.sourceforge.squirrel_sql.fw.sql.PrimaryKeyInfo;
0038: import net.sourceforge.squirrel_sql.fw.sql.TableColumnInfo;
0039: import net.sourceforge.squirrel_sql.fw.util.StringManager;
0040: import net.sourceforge.squirrel_sql.fw.util.StringManagerFactory;
0041: import net.sourceforge.squirrel_sql.fw.util.log.ILogger;
0042: import net.sourceforge.squirrel_sql.fw.util.log.LoggerController;
0043:
0044: import org.hibernate.HibernateException;
0045:
0046: /**
0047: * A simple utility class in which to place common code shared amongst the
0048: * dialects. Since the dialects all inherit behavior from specific server
0049: * dialects, it is not possible to inherit common behavior from a single base
0050: * class. So, this class is where common code is located.
0051: *
0052: * @author manningr
0053: */
0054: public class DialectUtils {
0055:
0056: /** Logger for this class. */
0057: private static final ILogger log = LoggerController
0058: .createLogger(DialectUtils.class);
0059:
0060: /** Internationalized strings for this class. */
0061: private static final StringManager s_stringMgr = StringManagerFactory
0062: .getStringManager(DialectUtils.class);
0063:
0064: // alter column clauses
0065:
0066: public static final String ALTER_COLUMN_CLAUSE = "ALTER COLUMN";
0067:
0068: public static final String MODIFY_COLUMN_CLAUSE = "MODIFY COLUMN";
0069:
0070: public static final String MODIFY_CLAUSE = "MODIFY";
0071:
0072: public static final String COLUMN_CLAUSE = "COLUMN";
0073:
0074: // alter name clauses
0075:
0076: public static final String RENAME_COLUMN_CLAUSE = "RENAME COLUMN";
0077:
0078: public static final String RENAME_TO_CLAUSE = "RENAME TO";
0079:
0080: public static final String TO_CLAUSE = "TO";
0081:
0082: // alter default clauses
0083:
0084: public static final String DEFAULT_CLAUSE = "DEFAULT";
0085:
0086: public static final String SET_DEFAULT_CLAUSE = "SET DEFAULT";
0087:
0088: public static final String SET_CLAUSE = "SET";
0089:
0090: public static final String ADD_DEFAULT_CLAUSE = "ADD DEFAULT";
0091:
0092: public static final String DROP_DEFAULT_CLAUSE = "DROP DEFAULT";
0093:
0094: // alter type clauses
0095:
0096: public static final String TYPE_CLAUSE = "TYPE";
0097:
0098: public static final String SET_DATA_TYPE_CLAUSE = "SET DATA TYPE";
0099:
0100: // drop column clauses
0101:
0102: public static final String DROP_CLAUSE = "DROP";
0103:
0104: public static final String DROP_COLUMN_CLAUSE = "DROP COLUMN";
0105:
0106: // cascade constraint clauses
0107:
0108: public static final String CASCADE_CLAUSE = "CASCADE";
0109:
0110: public static final String CASCADE_CONSTRAINTS_CLAUSE = "CASCADE CONSTRAINTS";
0111:
0112: // features
0113:
0114: public static final int COLUMN_COMMENT_ALTER_TYPE = 0;
0115: public static final int COLUMN_DEFAULT_ALTER_TYPE = 1;
0116: public static final int COLUMN_DROP_TYPE = 2;
0117: public static final int COLUMN_NAME_ALTER_TYPE = 3;
0118: public static final int COLUMN_NULL_ALTER_TYPE = 4;
0119: public static final int COLUMN_TYPE_ALTER_TYPE = 5;
0120: public static final int ADD_PRIMARY_KEY_TYPE = 6;
0121: public static final int DROP_PRIMARY_KEY_TYPE = 7;
0122:
0123: /**
0124: * Returns the SQL statement to use to add a column to the specified table
0125: * using the information about the new column specified by info.
0126: * @param info information about the new column such as type, name, etc.
0127: * @param dialect the HibernateDialect to use to resolve the type
0128: * @param addDefaultClause whether or not the dialect's SQL supports a
0129: * DEFAULT clause for columns.
0130: * @param addNullClause TODO
0131: * @return
0132: * @throws UnsupportedOperationException if the database doesn't support
0133: * adding columns after a table has already been created.
0134: */
0135: public static String getColumnAddSQL(TableColumnInfo info,
0136: HibernateDialect dialect, boolean addDefaultClause,
0137: boolean supportsNullQualifier, boolean addNullClause)
0138: throws UnsupportedOperationException, HibernateException {
0139: StringBuilder result = new StringBuilder();
0140: result.append("ALTER TABLE ");
0141: result.append(info.getTableName());
0142: result.append(" ");
0143: result.append(dialect.getAddColumnString().toUpperCase());
0144: result.append(" ");
0145: result.append(info.getColumnName());
0146: result.append(" ");
0147: result.append(dialect.getTypeName(info.getDataType(), info
0148: .getColumnSize(), info.getColumnSize(), info
0149: .getDecimalDigits()));
0150:
0151: if (addDefaultClause) {
0152: appendDefaultClause(info, result);
0153: }
0154: if (addNullClause) {
0155: if (info.isNullable().equals("NO")) {
0156: result.append(" NOT NULL ");
0157: } else {
0158: if (supportsNullQualifier) {
0159: result.append(" NULL ");
0160: }
0161: }
0162: }
0163: return result.toString();
0164: }
0165:
0166: public static String appendDefaultClause(TableColumnInfo info,
0167: StringBuilder buffer) {
0168:
0169: if (info.getDefaultValue() != null
0170: && !"".equals(info.getDefaultValue())) {
0171: buffer.append(" DEFAULT ");
0172: if (JDBCTypeMapper.isNumberType(info.getDataType())) {
0173: buffer.append(info.getDefaultValue());
0174: } else {
0175: buffer.append("'");
0176: buffer.append(info.getDefaultValue());
0177: buffer.append("'");
0178: }
0179: }
0180: return buffer.toString();
0181: }
0182:
0183: /**
0184: * Returns the SQL statement to use to add a comment to the specified
0185: * column of the specified table.
0186: *
0187: * @param tableName the name of the table to create the SQL for.
0188: * @param columnName the name of the column to create the SQL for.
0189: * @param comment the comment to add.
0190: * @return
0191: * @throws UnsupportedOperationException if the database doesn't support
0192: * annotating columns with a comment.
0193: */
0194: public static String getColumnCommentAlterSQL(String tableName,
0195: String columnName, String comment) {
0196: StringBuilder result = new StringBuilder();
0197: result.append("COMMENT ON COLUMN ");
0198: result.append(tableName);
0199: result.append(".");
0200: result.append(columnName);
0201: result.append(" IS '");
0202: if (comment != null && !"".equals(comment)) {
0203: result.append(comment);
0204: }
0205: result.append("'");
0206: return result.toString();
0207: }
0208:
0209: /**
0210: * Returns the SQL statement to use to add a comment to the specified
0211: * column of the specified table.
0212: *
0213: * @param tableName the name of the table to create the SQL for.
0214: * @param columnName the name of the column to create the SQL for.
0215: * @param comment the comment to add.
0216: * @return
0217: * @throws UnsupportedOperationException if the database doesn't support
0218: * annotating columns with a comment.
0219: */
0220: public static String getColumnCommentAlterSQL(TableColumnInfo info) {
0221: return getColumnCommentAlterSQL(info.getTableName(), info
0222: .getColumnName(), info.getRemarks());
0223: }
0224:
0225: /**
0226: *
0227: * @param tableName
0228: * @param columnName
0229: * @return
0230: */
0231: public static String getColumnDropSQL(String tableName,
0232: String columnName) {
0233: return getColumnDropSQL(tableName, columnName, "DROP", false,
0234: null);
0235: }
0236:
0237: /**
0238: *
0239: * @param tableName
0240: * @param columnName
0241: * @param addConstraintClause TODO
0242: * @param constraintClause TODO
0243: * @return
0244: */
0245: public static String getColumnDropSQL(String tableName,
0246: String columnName, String dropClause,
0247: boolean addConstraintClause, String constraintClause) {
0248: StringBuilder result = new StringBuilder();
0249: result.append("ALTER TABLE ");
0250: result.append(tableName);
0251: result.append(" ");
0252: result.append(dropClause);
0253: result.append(" ");
0254: result.append(columnName);
0255: if (addConstraintClause) {
0256: result.append(" ");
0257: result.append(constraintClause);
0258: }
0259: return result.toString();
0260: }
0261:
0262: /**
0263: * Returns the SQL that forms the command to drop the specified table. If
0264: * cascade contraints is supported by the dialect and cascadeConstraints is
0265: * true, then a drop statement with cascade constraints clause will be
0266: * formed.
0267: *
0268: * @param iTableInfo the table to drop
0269: * @param supportsCascade whether or not the cascade clause should be added.
0270: * @param cascadeValue whether or not to drop any FKs that may
0271: * reference the specified table.
0272: * @param supportsMatViews TODO
0273: * @param cascadeClause TODO
0274: * @param isMatView TODO
0275: * @return the drop SQL command.
0276: */
0277: public static List<String> getTableDropSQL(ITableInfo iTableInfo,
0278: boolean supportsCascade, boolean cascadeValue,
0279: boolean supportsMatViews, String cascadeClause,
0280: boolean isMatView) {
0281: StringBuilder result = new StringBuilder();
0282: if (supportsMatViews && isMatView) {
0283: result.append("DROP MATERIALIZED VIEW ");
0284: } else {
0285: result.append("DROP TABLE ");
0286: }
0287: result.append(iTableInfo.getQualifiedName());
0288: if (supportsCascade && cascadeValue) {
0289: result.append(" ");
0290: result.append(cascadeClause);
0291: }
0292: return Arrays.asList(new String[] { result.toString() });
0293: }
0294:
0295: public static String getTypeName(TableColumnInfo info,
0296: HibernateDialect dialect) {
0297: return dialect.getTypeName(info.getDataType(), info
0298: .getColumnSize(), info.getColumnSize(), info
0299: .getDecimalDigits());
0300: }
0301:
0302: /**
0303: * Returns the SQL used to alter the specified column to allow/disallow null
0304: * values.
0305: * <br>
0306: * ALTER TABLE table_name <alterClause> column_name TYPE NULL | NOT NULL
0307: * <br>
0308: * ALTER TABLE table_name <alterClause> column_name NULL | NOT NULL
0309: *
0310: * @param info the column to modify
0311: * @param dialect the HibernateDialect representing the target database.
0312: * @param alterClause the alter column clause (e.g. ALTER COLUMN )
0313: * @param specifyType whether or not the column type needs to be specified
0314: *
0315: * @return the SQL to execute
0316: */
0317: public static String getColumnNullableAlterSQL(
0318: TableColumnInfo info, HibernateDialect dialect,
0319: String alterClause, boolean specifyType) {
0320: boolean nullable = info.isNullable().equalsIgnoreCase("YES");
0321: return getColumnNullableAlterSQL(info, nullable, dialect,
0322: alterClause, specifyType);
0323: }
0324:
0325: /**
0326: * Returns the SQL used to alter the specified column to allow/disallow null
0327: * values.
0328: * <br>
0329: * ALTER TABLE table_name <alterClause> column_name TYPE NULL | NOT NULL
0330: * <br>
0331: * ALTER TABLE table_name <alterClause> column_name NULL | NOT NULL
0332: *
0333: * @param info the column to modify
0334: * @param dialect the HibernateDialect representing the target database.
0335: * @param alterClause the alter column clause (e.g. ALTER COLUMN )
0336: * @param specifyType whether or not the column type needs to be specified
0337: *
0338: * @return the SQL to execute
0339: */
0340: public static String getColumnNullableAlterSQL(
0341: TableColumnInfo info, boolean nullable,
0342: HibernateDialect dialect, String alterClause,
0343: boolean specifyType) {
0344: StringBuilder result = new StringBuilder();
0345: result.append("ALTER TABLE ");
0346: result.append(info.getTableName());
0347: result.append(" ");
0348: result.append(alterClause);
0349: result.append(" ");
0350: result.append(info.getColumnName());
0351: if (specifyType) {
0352: result.append(" ");
0353: result.append(getTypeName(info, dialect));
0354: result.append(" ");
0355: }
0356: if (nullable) {
0357: result.append(" NULL");
0358: } else {
0359: result.append(" NOT NULL");
0360: }
0361: return result.toString();
0362: }
0363:
0364: /**
0365: * Populates the specified ArrayList with SQL statement(s) required to
0366: * convert each of the columns to not null. This is typically needed in
0367: * some databases when adding a primary key (some dbs do this step
0368: * automatically)
0369: *
0370: * @param colInfos the columns to be made not null
0371: * @param dialect
0372: * @param result
0373: */
0374: public static void getMultiColNotNullSQL(
0375: TableColumnInfo[] colInfos, HibernateDialect dialect,
0376: String alterClause, boolean specifyType,
0377: ArrayList<String> result) {
0378: for (int i = 0; i < colInfos.length; i++) {
0379: StringBuilder notNullSQL = new StringBuilder();
0380: notNullSQL.append("ALTER TABLE ");
0381: notNullSQL.append(colInfos[i].getTableName());
0382: notNullSQL.append(" ");
0383: notNullSQL.append(alterClause);
0384: notNullSQL.append(" ");
0385: notNullSQL.append(colInfos[i].getColumnName());
0386: if (specifyType) {
0387: notNullSQL.append(" ");
0388: notNullSQL.append(DialectUtils.getTypeName(colInfos[i],
0389: dialect));
0390: }
0391: notNullSQL.append(" NOT NULL");
0392: result.add(notNullSQL.toString());
0393: }
0394: }
0395:
0396: /**
0397: * Returns the SQL for creating a primary key consisting of the specified
0398: * colInfos.
0399: *
0400: * ALTER TABLE table_name ADD CONSTRAINT pkName PRIMARY KEY (col,...);
0401: *
0402: * or
0403: *
0404: * ALTER TABLE table_name ADD CONSTRAINT PRIMARY KEY (col,...) CONSTRAINT pkName;
0405: * @param ti TODO
0406: * @param colInfos
0407: * @param appendConstraintName whether or not the pkName (constraint name)
0408: * should be placed at the end of the statement.
0409: *
0410: * @return
0411: */
0412: public static String getAddPrimaryKeySQL(ITableInfo ti,
0413: String pkName, TableColumnInfo[] colInfos,
0414: boolean appendConstraintName) {
0415: StringBuilder pkSQL = new StringBuilder();
0416: pkSQL.append("ALTER TABLE ");
0417: pkSQL.append(ti.getQualifiedName());
0418: pkSQL.append(" ADD CONSTRAINT ");
0419: if (!appendConstraintName) {
0420: pkSQL.append(pkName);
0421: }
0422: pkSQL.append(" PRIMARY KEY ");
0423: pkSQL.append(getColumnList(colInfos));
0424: if (appendConstraintName) {
0425: pkSQL.append(" CONSTRAINT ");
0426: pkSQL.append(pkName);
0427: }
0428: return pkSQL.toString();
0429: }
0430:
0431: /**
0432: * Returns:
0433: *
0434: * (column1, column2, ...)
0435: *
0436: * @param colInfos
0437: * @return
0438: */
0439: private static String getColumnList(TableColumnInfo[] colInfos) {
0440: StringBuilder result = new StringBuilder();
0441: result.append("(");
0442: for (int i = 0; i < colInfos.length; i++) {
0443: result.append(colInfos[i].getColumnName());
0444: if (i + 1 < colInfos.length) {
0445: result.append(", ");
0446: }
0447: }
0448: result.append(")");
0449: return result.toString();
0450: }
0451:
0452: /**
0453: * Returns the SQL that is used to change the column name.
0454: *
0455: * ALTER TABLE table_name [alterClause] column_name [renameToClause] column_name
0456: *
0457: * @param from the TableColumnInfo as it is
0458: * @param to the TableColumnInfo as it wants to be
0459: *
0460: * @return the SQL to make the change
0461: */
0462: public static String getColumnNameAlterSQL(TableColumnInfo from,
0463: TableColumnInfo to, String alterClause,
0464: String renameToClause) {
0465: StringBuilder result = new StringBuilder();
0466: result.append("ALTER TABLE ");
0467: result.append(from.getTableName());
0468: result.append(" ");
0469: result.append(alterClause);
0470: result.append(" ");
0471: result.append(from.getColumnName());
0472: result.append(" ");
0473: result.append(renameToClause);
0474: result.append(" ");
0475: result.append(to.getColumnName());
0476: return result.toString();
0477: }
0478:
0479: /**
0480: * Returns the SQL command to change the specified column's default value
0481: *
0482: * ALTER TABLE table_name ALTER COLUMN column_name [defaultClause] 'defaultVal'
0483: *
0484: * ALTER TABLE table_name ALTER COLUMN column_name [defaultClause] 1234
0485: *
0486: * @param dialect TODO
0487: * @param info the column to modify and it's default value.
0488: * @param specifyType TODO
0489: *
0490: * @return SQL to make the change
0491: */
0492: public static String getColumnDefaultAlterSQL(
0493: HibernateDialect dialect, TableColumnInfo info,
0494: String alterClause, boolean specifyType,
0495: String defaultClause) {
0496: StringBuilder result = new StringBuilder();
0497: result.append("ALTER TABLE ");
0498: result.append(info.getTableName());
0499: result.append(" ");
0500: result.append(alterClause);
0501: result.append(" ");
0502: result.append(info.getColumnName());
0503: result.append(" ");
0504: if (specifyType) {
0505: result.append(getTypeName(info, dialect));
0506: }
0507: result.append(" ");
0508: result.append(defaultClause);
0509: result.append(" ");
0510: if (JDBCTypeMapper.isNumberType(info.getDataType())) {
0511: result.append(info.getDefaultValue());
0512: } else {
0513: result.append("'");
0514: result.append(info.getDefaultValue());
0515: result.append("'");
0516: }
0517: return result.toString();
0518: }
0519:
0520: /**
0521: * Returns the SQL that is used to change the column type.
0522: *
0523: * ALTER TABLE table_name alter_clause column_name [setClause] data_type
0524: *
0525: * ALTER TABLE table_name alter_clause column_name column_name [setClause] data_type
0526: *
0527: * @param from the TableColumnInfo as it is
0528: * @param to the TableColumnInfo as it wants to be
0529: *
0530: * @return the SQL to make the change
0531: * @throw UnsupportedOperationException if the database doesn't support
0532: * modifying column types.
0533: */
0534: @SuppressWarnings("unused")
0535: public static List<String> getColumnTypeAlterSQL(
0536: HibernateDialect dialect, String alterClause,
0537: String setClause, boolean repeatColumn,
0538: TableColumnInfo from, TableColumnInfo to)
0539: throws UnsupportedOperationException {
0540: ArrayList<String> list = new ArrayList<String>();
0541: StringBuilder result = new StringBuilder();
0542: result.append("ALTER TABLE ");
0543: result.append(to.getTableName());
0544: result.append(" ");
0545: result.append(alterClause);
0546: result.append(" ");
0547: if (repeatColumn) {
0548: result.append(to.getColumnName());
0549: result.append(" ");
0550: }
0551: result.append(to.getColumnName());
0552: result.append(" ");
0553: if (setClause != null && !"".equals(setClause)) {
0554: result.append(setClause);
0555: result.append(" ");
0556: }
0557: result.append(getTypeName(to, dialect));
0558: list.add(result.toString());
0559: return list;
0560: }
0561:
0562: /**
0563: * Returns the SQL that is used to change the column name.
0564: *
0565: * RENAME COLUMN table_name.column_name TO new_column_name
0566: *
0567: * @param from the TableColumnInfo as it is
0568: * @param to the TableColumnInfo as it wants to be
0569: *
0570: * @return the SQL to make the change
0571: */
0572: public static String getColumnRenameSQL(TableColumnInfo from,
0573: TableColumnInfo to) {
0574: StringBuilder result = new StringBuilder();
0575: result.append("RENAME COLUMN ");
0576: result.append(from.getTableName());
0577: result.append(".");
0578: result.append(from.getColumnName());
0579: result.append(" TO ");
0580: result.append(to.getColumnName());
0581: return result.toString();
0582: }
0583:
0584: public static String getUnsupportedMessage(
0585: HibernateDialect dialect, int featureId)
0586: throws UnsupportedOperationException {
0587: String msg = null;
0588: switch (featureId) {
0589: case COLUMN_COMMENT_ALTER_TYPE:
0590: //i18n[DialectUtils.columnCommentUnsupported={0} doesn''t support
0591: //column comments]
0592: msg = s_stringMgr.getString(
0593: "DialectUtils.columnCommentUnsupported", dialect
0594: .getDisplayName());
0595: break;
0596: case COLUMN_DEFAULT_ALTER_TYPE:
0597: //i18n[DialectUtils.columnDefaultUnsupported={0} doesn''t support
0598: //altering a column''s default value]
0599: msg = s_stringMgr.getString(
0600: "DialectUtils.columnDefaultUnsupported", dialect
0601: .getDisplayName());
0602: break;
0603:
0604: case COLUMN_DROP_TYPE:
0605: //i18n[DialectUtils.columnDropUnsupported={0} doesn''t support
0606: //dropping a column]
0607: msg = s_stringMgr.getString(
0608: "DialectUtils.columnDropUnsupported", dialect
0609: .getDisplayName());
0610: break;
0611: case COLUMN_NAME_ALTER_TYPE:
0612: //i18n[DialectUtils.columnNameUnsupported={0} doesn''t support
0613: //altering a column''s name]
0614: msg = s_stringMgr.getString(
0615: "DialectUtils.columnNameUnsupported", dialect
0616: .getDisplayName());
0617: break;
0618: case COLUMN_NULL_ALTER_TYPE:
0619: //i18n[DialectUtils.columnNullUnsupported={0} doesn''t support
0620: //altering a column's nullable attribute]
0621: msg = s_stringMgr.getString(
0622: "DialectUtils.columnCommentUnsupported", dialect
0623: .getDisplayName());
0624: break;
0625: case COLUMN_TYPE_ALTER_TYPE:
0626: //i18n[DialectUtils.columnTypeUnsupported={0} doesn''t support
0627: //altering a column's type attribute]
0628: msg = s_stringMgr.getString(
0629: "DialectUtils.columnTypeUnsupported", dialect
0630: .getDisplayName());
0631: break;
0632: case ADD_PRIMARY_KEY_TYPE:
0633: //i18n[DialectUtils.addPrimaryKeyUnsupported={0} doesn''t
0634: //support adding primary keys]
0635: msg = s_stringMgr.getString(
0636: "DialectUtils.addPrimaryKeyUnsupported", dialect
0637: .getDisplayName());
0638: break;
0639: case DROP_PRIMARY_KEY_TYPE:
0640: //i18n[DialectUtils.dropPrimaryKeyUnsupported={0} doesn''t
0641: //support dropping primary keys]
0642: msg = s_stringMgr.getString(
0643: "DialectUtils.dropPrimaryKeyUnsupported", dialect
0644: .getDisplayName());
0645: break;
0646: default:
0647: throw new IllegalArgumentException("Unknown featureId: "
0648: + featureId);
0649: }
0650: return msg;
0651: }
0652:
0653: /**
0654: * Returns the SQL command to drop the specified table's primary key.
0655: *
0656: * alter table table_name drop primary key
0657: *
0658: * or
0659: *
0660: * alter table table_name drop constraint [pkName]
0661: *
0662: * @param pkName the name of the primary key that should be dropped
0663: * @param tableName the name of the table whose primary key should be
0664: * dropped
0665: * @param useConstraintName if true, the constraint name is used - like
0666: * 'DROP CONSTRAINT pkName'; otherwise
0667: * a generic 'DROP PRIMARY KEY' is used instead.
0668: * @param cascadeConstraints whether or not to append 'CASCADE' to the end.
0669: * @return
0670: */
0671: public static String getDropPrimaryKeySQL(String pkName,
0672: String tableName, boolean useConstraintName,
0673: boolean cascadeConstraints) {
0674: StringBuilder result = new StringBuilder();
0675: result.append("ALTER TABLE ");
0676: result.append(tableName);
0677: if (useConstraintName) {
0678: result.append(" DROP CONSTRAINT ");
0679: result.append(pkName);
0680: } else {
0681: result.append(" DROP PRIMARY KEY");
0682: }
0683: if (cascadeConstraints) {
0684: result.append(" CASCADE");
0685: }
0686: return result.toString();
0687: }
0688:
0689: /**
0690: * CREATE UNIQUE INDEX indexName ON tableName (columns);
0691: *
0692: * @param indexName
0693: * @param tableName
0694: * @param columns
0695: * @return
0696: */
0697: public static String getAddIndexSQL(String indexName,
0698: boolean unique, TableColumnInfo[] columns) {
0699: StringBuilder result = new StringBuilder();
0700: if (unique) {
0701: result.append("CREATE UNIQUE INDEX ");
0702: } else {
0703: result.append("CREATE INDEX ");
0704: }
0705: result.append(indexName);
0706: result.append(" ON ");
0707: result.append(columns[0].getTableName());
0708: result.append(" ");
0709: result.append(getColumnList(columns));
0710: return result.toString();
0711: }
0712:
0713: public static TableColumnInfo getRenamedColumn(
0714: TableColumnInfo info, String newColumnName) {
0715: TableColumnInfo result = new TableColumnInfo(info
0716: .getCatalogName(), info.getSchemaName(), info
0717: .getTableName(), newColumnName, info.getDataType(),
0718: info.getTypeName(), info.getColumnSize(), info
0719: .getDecimalDigits(), info.getRadix(), info
0720: .isNullAllowed(), info.getRemarks(), info
0721: .getDefaultValue(), info.getOctetLength(), info
0722: .getOrdinalPosition(), info.isNullable());
0723: return result;
0724: }
0725:
0726: /**
0727: * Returns the SQL command to drop the specified table's foreign key
0728: * constraint.
0729: *
0730: * @param fkName the name of the foreign key that should be dropped
0731: * @param tableName the name of the table whose foreign key should be
0732: * dropped
0733: * @return
0734: */
0735: public static String getDropForeignKeySQL(String fkName,
0736: String tableName) {
0737: StringBuilder tmp = new StringBuilder();
0738: tmp.append("ALTER TABLE ");
0739: tmp.append(tableName);
0740: tmp.append(" DROP CONSTRAINT ");
0741: tmp.append(fkName);
0742: return tmp.toString();
0743: }
0744:
0745: public static List<String> getCreateTableSQL(
0746: List<ITableInfo> tables, ISQLDatabaseMetaData md,
0747: HibernateDialect dialect, CreateScriptPreferences prefs,
0748: boolean isJdbcOdbc) throws SQLException {
0749: List<String> sqls = new ArrayList<String>();
0750: List<String> allconstraints = new ArrayList<String>();
0751:
0752: for (ITableInfo ti : tables) {
0753: StringBuilder result = new StringBuilder();
0754: String tableName = prefs.isQualifyTableNames() ? ti
0755: .getQualifiedName() : ti.getSimpleName();
0756: result.append("CREATE TABLE ");
0757: result.append(tableName);
0758: result.append("\n(");
0759:
0760: List<PrimaryKeyInfo> pkInfos = getPrimaryKeyInfo(md, ti,
0761: isJdbcOdbc);
0762: List<String> pks = getPKSequenceList(pkInfos);
0763: TableColumnInfo[] infos = md.getColumnInfo(ti);
0764: for (TableColumnInfo tcInfo : infos) {
0765: String columnName = tcInfo.getColumnName();
0766: int columnSize = tcInfo.getColumnSize();
0767: int dataType = tcInfo.getDataType();
0768: int precision = dialect.getPrecisionDigits(columnSize,
0769: dataType);
0770: String column = dialect.getTypeName(tcInfo
0771: .getDataType(), tcInfo.getColumnSize(),
0772: precision, tcInfo.getDecimalDigits());
0773:
0774: result.append("\n ");
0775: result.append(columnName);
0776: result.append(" ");
0777: result.append(column);
0778: String isNullable = tcInfo.isNullable();
0779: if (pks.size() == 1 && pks.get(0).equals(columnName)) {
0780: result.append(" PRIMARY KEY");
0781: }
0782: if ("NO".equalsIgnoreCase(isNullable)) {
0783: result.append(" NOT NULL");
0784: }
0785: result.append(",");
0786: }
0787:
0788: if (pks.size() > 1) {
0789: result.append("\n CONSTRAINT ");
0790: result.append(pkInfos.get(0).getSimpleName());
0791: result.append(" PRIMARY KEY (");
0792: for (int i = 0; i < pks.size(); i++) {
0793: result.append(pks.get(i));
0794: result.append(",");
0795: }
0796: result.setLength(result.length() - 1);
0797: result.append("),");
0798: }
0799: result.setLength(result.length() - 1);
0800:
0801: result.append("\n)");
0802: sqls.add(result.toString());
0803:
0804: if (isJdbcOdbc) {
0805: continue;
0806: }
0807:
0808: List<String> constraints = createConstraints(ti, tables,
0809: prefs, md);
0810: addConstraintsSQLs(sqls, allconstraints, constraints, prefs);
0811:
0812: List<String> indexes = createIndexes(ti, md, pkInfos);
0813: addConstraintsSQLs(sqls, allconstraints, indexes, prefs);
0814: }
0815:
0816: if (prefs.isConstraintsAtEnd()) {
0817: sqls.addAll(allconstraints);
0818: }
0819: return sqls;
0820: }
0821:
0822: private static void addConstraintsSQLs(List<String> sqls,
0823: List<String> allconstraints, List<String> sqlsToAdd,
0824: CreateScriptPreferences prefs) {
0825: if (sqlsToAdd.size() > 0) {
0826: if (prefs.isConstraintsAtEnd()) {
0827: allconstraints.addAll(sqlsToAdd);
0828: } else {
0829: sqls.addAll(sqlsToAdd);
0830: }
0831: }
0832: }
0833:
0834: /**
0835: * Get a list of statements needed to create indexes for the specified table
0836: *
0837: * @param ti
0838: * @param md
0839: * @param primaryKeys can be null
0840: * @return
0841: */
0842: public static List<String> createIndexes(ITableInfo ti,
0843: ISQLDatabaseMetaData md, List<PrimaryKeyInfo> primaryKeys) {
0844: if (ti == null) {
0845: throw new IllegalArgumentException("ti cannot be null");
0846: }
0847: if (md == null) {
0848: throw new IllegalArgumentException("md cannot be null");
0849: }
0850: List<String> result = new ArrayList<String>();
0851: if (ti.getDatabaseObjectType() == DatabaseObjectType.VIEW) {
0852: return result;
0853: }
0854:
0855: List<IndexColInfo> pkCols = new ArrayList<IndexColInfo>();
0856: if (primaryKeys != null) {
0857: for (PrimaryKeyInfo pkInfo : primaryKeys) {
0858: pkCols.add(new IndexColInfo(pkInfo.getColumnName()));
0859: }
0860: Collections.sort(pkCols, IndexColInfo.NAME_COMPARATOR);
0861: }
0862:
0863: List<IndexInfo> indexInfos = null;
0864: try {
0865: indexInfos = md.getIndexInfo(ti);
0866: } catch (SQLException e) {
0867: //i18n[DialectUtils.error.getprimarykey=Unable to get primary key info for table {0}]
0868: String msg = s_stringMgr.getString(
0869: "DialectUtils.error.getprimarykey", ti
0870: .getSimpleName());
0871: log.error(msg, e);
0872: return result;
0873: }
0874:
0875: // Group all columns by index
0876: Hashtable<String, TableIndexInfo> buf = new Hashtable<String, TableIndexInfo>();
0877: for (IndexInfo indexInfo : indexInfos) {
0878: String indexName = indexInfo.getSimpleName();
0879: if (null == indexName) {
0880: continue;
0881: }
0882: TableIndexInfo ixi = buf.get(indexName);
0883: if (null == ixi) {
0884: List<IndexColInfo> ixCols = new ArrayList<IndexColInfo>();
0885:
0886: ixCols.add(new IndexColInfo(indexInfo.getColumnName(),
0887: indexInfo.getOrdinalPosition()));
0888: buf.put(indexName, new TableIndexInfo(indexInfo
0889: .getTableName(), indexName, ixCols, !indexInfo
0890: .isNonUnique()));
0891: } else {
0892: ixi.cols.add(new IndexColInfo(
0893: indexInfo.getColumnName(), indexInfo
0894: .getOrdinalPosition()));
0895: }
0896: }
0897:
0898: TableIndexInfo[] ixs = buf.values().toArray(
0899: new TableIndexInfo[buf.size()]);
0900: for (int i = 0; i < ixs.length; i++) {
0901: Collections.sort(ixs[i].cols, IndexColInfo.NAME_COMPARATOR);
0902:
0903: if (pkCols.equals(ixs[i].cols)) {
0904: // Serveral DBs automatically create an index for primary key fields
0905: // and return this index in getIndexInfo(). We remove this index from the script
0906: // because it would break the script with an index already exists error.
0907: continue;
0908: }
0909:
0910: Collections.sort(ixs[i].cols,
0911: IndexColInfo.ORDINAL_POSITION_COMPARATOR);
0912:
0913: StringBuilder indexSQL = new StringBuilder();
0914: indexSQL.append("CREATE");
0915: indexSQL.append(ixs[i].unique ? " UNIQUE " : " ");
0916: indexSQL.append("INDEX ");
0917: indexSQL.append(ixs[i].ixName);
0918: indexSQL.append(" ON ");
0919: indexSQL.append(ixs[i].table);
0920:
0921: if (ixs[i].cols.size() == 1) {
0922: indexSQL.append("(").append(ixs[i].cols.get(0));
0923:
0924: for (int j = 1; j < ixs[i].cols.size(); j++) {
0925: indexSQL.append(",").append(ixs[i].cols.get(j));
0926: }
0927: } else {
0928: indexSQL.append("\n(\n");
0929: for (int j = 0; j < ixs[i].cols.size(); j++) {
0930: indexSQL.append(" ");
0931: indexSQL.append(ixs[i].cols.get(j));
0932: if (j < ixs[i].cols.size() - 1) {
0933: indexSQL.append(",\n");
0934: } else {
0935: indexSQL.append("\n");
0936: }
0937: }
0938: }
0939: indexSQL.append(")");
0940: result.add(indexSQL.toString());
0941: }
0942: return result;
0943: }
0944:
0945: private static List<String> createConstraints(ITableInfo ti,
0946: List<ITableInfo> tables, CreateScriptPreferences prefs,
0947: ISQLDatabaseMetaData md) throws SQLException {
0948:
0949: List<String> result = new ArrayList<String>();
0950: StringBuffer sbToAppend = new StringBuffer();
0951:
0952: ConstraintInfo[] cis = getConstraintInfos(ti, md);
0953:
0954: for (int i = 0; i < cis.length; i++) {
0955: if (!prefs.isIncludeExternalReferences()) {
0956: boolean found = false;
0957: for (ITableInfo table : tables) {
0958: if (table.getSimpleName().equalsIgnoreCase(
0959: cis[i].pkTable)) {
0960: found = true;
0961: break;
0962: }
0963: }
0964: if (false == found) {
0965: continue;
0966: }
0967: }
0968:
0969: sbToAppend.append("ALTER TABLE " + cis[i].fkTable + "\n");
0970: sbToAppend.append("ADD CONSTRAINT " + cis[i].fkName + "\n");
0971:
0972: if (cis[i].fkCols.size() == 1) {
0973: sbToAppend.append("FOREIGN KEY (").append(
0974: cis[i].fkCols.get(0));
0975:
0976: for (int j = 1; j < cis[i].fkCols.size(); j++) {
0977: sbToAppend.append(",").append(cis[i].fkCols.get(j));
0978: }
0979: sbToAppend.append(")\n");
0980:
0981: sbToAppend.append("REFERENCES " + cis[i].pkTable + "(");
0982: sbToAppend.append(cis[i].pkCols.get(0));
0983: for (int j = 1; j < cis[i].pkCols.size(); j++) {
0984: sbToAppend.append(",").append(cis[i].pkCols.get(j));
0985: }
0986: } else {
0987: sbToAppend.append("FOREIGN KEY\n");
0988: sbToAppend.append("(\n");
0989: for (int j = 0; j < cis[i].fkCols.size(); j++) {
0990: if (j < cis[i].fkCols.size() - 1) {
0991: sbToAppend.append(" " + cis[i].fkCols.get(j)
0992: + ",\n");
0993: } else {
0994: sbToAppend.append(" " + cis[i].fkCols.get(j)
0995: + "\n");
0996: }
0997: }
0998: sbToAppend.append(")\n");
0999:
1000: sbToAppend
1001: .append("REFERENCES " + cis[i].pkTable + "\n");
1002: sbToAppend.append("(\n");
1003: for (int j = 0; j < cis[i].pkCols.size(); j++) {
1004: if (j < cis[i].pkCols.size() - 1) {
1005: sbToAppend.append(" " + cis[i].pkCols.get(j)
1006: + ",\n");
1007: } else {
1008: sbToAppend.append(" " + cis[i].pkCols.get(j)
1009: + "\n");
1010: }
1011: }
1012: }
1013:
1014: sbToAppend.append(")");
1015:
1016: if (prefs.isDeleteRefAction()) {
1017: sbToAppend.append(" ON DELETE ");
1018: sbToAppend.append(prefs.getRefActionByType(prefs
1019: .getDeleteAction()));
1020: } else {
1021: switch (cis[i].deleteRule) {
1022: case DatabaseMetaData.importedKeyCascade:
1023: sbToAppend.append(" ON DELETE CASCADE");
1024: break;
1025: case DatabaseMetaData.importedKeySetNull:
1026: sbToAppend.append(" ON DELETE SET NULL");
1027: break;
1028: case DatabaseMetaData.importedKeySetDefault:
1029: sbToAppend.append(" ON DELETE SET DEFAULT");
1030: break;
1031: case DatabaseMetaData.importedKeyRestrict:
1032: case DatabaseMetaData.importedKeyNoAction:
1033: default:
1034: sbToAppend.append(" ON DELETE NO ACTION");
1035: }
1036: }
1037: if (prefs.isUpdateRefAction()) {
1038: sbToAppend.append(" ON UPDATE ");
1039: sbToAppend.append(prefs.getRefActionByType(prefs
1040: .getUpdateAction()));
1041: } else {
1042: switch (cis[i].updateRule) {
1043: case DatabaseMetaData.importedKeyCascade:
1044: sbToAppend.append(" ON UPDATE CASCADE");
1045: break;
1046: case DatabaseMetaData.importedKeySetNull:
1047: sbToAppend.append(" ON UPDATE SET NULL");
1048: break;
1049: case DatabaseMetaData.importedKeySetDefault:
1050: sbToAppend.append(" ON UPDATE SET DEFAULT");
1051: break;
1052: case DatabaseMetaData.importedKeyRestrict:
1053: case DatabaseMetaData.importedKeyNoAction:
1054: default:
1055: sbToAppend.append(" ON UPDATE NO ACTION");
1056: }
1057: }
1058: sbToAppend.append("\n");
1059: result.add(sbToAppend.toString());
1060: sbToAppend.setLength(0);
1061: }
1062:
1063: return result;
1064: }
1065:
1066: private static ConstraintInfo[] getConstraintInfos(ITableInfo ti,
1067: ISQLDatabaseMetaData md) throws SQLException {
1068: Hashtable<String, ConstraintInfo> buf = new Hashtable<String, ConstraintInfo>();
1069: ForeignKeyInfo[] fkinfos = md.getImportedKeysInfo(ti);
1070: for (ForeignKeyInfo fkinfo : fkinfos) {
1071: ConstraintInfo ci = buf.get(fkinfo.getSimpleName());
1072:
1073: if (null == ci) {
1074: Vector<String> fkCols = new Vector<String>();
1075: Vector<String> pkCols = new Vector<String>();
1076: fkCols.add(fkinfo.getForeignKeyColumnName());
1077: pkCols.add(fkinfo.getPrimaryKeyColumnName());
1078: ci = new ConstraintInfo(
1079: fkinfo.getForeignKeyTableName(), fkinfo
1080: .getPrimaryKeyTableName(), fkinfo
1081: .getSimpleName(), fkCols, pkCols,
1082: (short) fkinfo.getDeleteRule(), (short) fkinfo
1083: .getUpdateRule());
1084: buf.put(fkinfo.getSimpleName(), ci);
1085: } else {
1086: ci.fkCols.add(fkinfo.getForeignKeyColumnName());
1087: ci.pkCols.add(fkinfo.getPrimaryKeyColumnName());
1088: }
1089:
1090: }
1091: return buf.values().toArray(new ConstraintInfo[buf.size()]);
1092: }
1093:
1094: private static List<PrimaryKeyInfo> getPrimaryKeyInfo(
1095: ISQLDatabaseMetaData md, ITableInfo ti, boolean isJdbcOdbc) {
1096: List<PrimaryKeyInfo> result = new ArrayList<PrimaryKeyInfo>();
1097: if (isJdbcOdbc) {
1098: return result;
1099: }
1100: try {
1101: result = Arrays.asList(md.getPrimaryKey(ti));
1102: } catch (SQLException e) {
1103: // i18n[CreateTableScriptCommand.error.getprimarykey=Unable to get
1104: //primary key info for table {0}]
1105: String msg = s_stringMgr.getString(
1106: "DialectUtils.error.getprimarykey", ti
1107: .getSimpleName());
1108: log.error(msg, e);
1109: }
1110: return result;
1111: }
1112:
1113: private static List<String> getPKSequenceList(
1114: List<PrimaryKeyInfo> infos) {
1115: String[] result = new String[infos.size()];
1116: for (PrimaryKeyInfo info : infos) {
1117: int iKeySeq = info.getKeySequence() - 1;
1118: result[iKeySeq] = info.getColumnName();
1119: }
1120: return Arrays.asList(result);
1121: }
1122:
1123: }
|