0001: /*******************************************************************************
0002: * Licensed to the Apache Software Foundation (ASF) under one
0003: * or more contributor license agreements. See the NOTICE file
0004: * distributed with this work for additional information
0005: * regarding copyright ownership. The ASF licenses this file
0006: * to you under the Apache License, Version 2.0 (the
0007: * "License"); you may not use this file except in compliance
0008: * with the License. You may obtain a copy of the License at
0009: *
0010: * http://www.apache.org/licenses/LICENSE-2.0
0011: *
0012: * Unless required by applicable law or agreed to in writing,
0013: * software distributed under the License is distributed on an
0014: * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
0015: * KIND, either express or implied. See the License for the
0016: * specific language governing permissions and limitations
0017: * under the License.
0018: *******************************************************************************/package org.ofbiz.entity.jdbc;
0019:
0020: import java.io.Serializable;
0021: import java.sql.Connection;
0022: import java.sql.DatabaseMetaData;
0023: import java.sql.ResultSet;
0024: import java.sql.SQLException;
0025: import java.sql.Statement;
0026: import java.util.ArrayList;
0027: import java.util.Collection;
0028: import java.util.Collections;
0029: import java.util.Iterator;
0030: import java.util.List;
0031: import java.util.Map;
0032: import java.util.Set;
0033: import java.util.TreeSet;
0034:
0035: import javolution.util.FastList;
0036: import javolution.util.FastMap;
0037: import org.w3c.dom.Document;
0038: import org.w3c.dom.Element;
0039:
0040: import org.ofbiz.base.util.Debug;
0041: import org.ofbiz.base.util.UtilTimer;
0042: import org.ofbiz.base.util.UtilValidate;
0043: import org.ofbiz.base.util.UtilXml;
0044: import org.ofbiz.entity.GenericEntityException;
0045: import org.ofbiz.entity.config.DatasourceInfo;
0046: import org.ofbiz.entity.config.EntityConfigUtil;
0047: import org.ofbiz.entity.model.ModelEntity;
0048: import org.ofbiz.entity.model.ModelField;
0049: import org.ofbiz.entity.model.ModelFieldType;
0050: import org.ofbiz.entity.model.ModelFieldTypeReader;
0051: import org.ofbiz.entity.model.ModelIndex;
0052: import org.ofbiz.entity.model.ModelKeyMap;
0053: import org.ofbiz.entity.model.ModelRelation;
0054: import org.ofbiz.entity.model.ModelViewEntity;
0055: import org.ofbiz.entity.transaction.TransactionUtil;
0056:
0057: /**
0058: * Utilities for Entity Database Maintenance
0059: *
0060: */
0061: public class DatabaseUtil {
0062:
0063: public static final String module = DatabaseUtil.class.getName();
0064:
0065: // OFBiz Connections
0066: protected ModelFieldTypeReader modelFieldTypeReader = null;
0067: protected DatasourceInfo datasourceInfo = null;
0068: protected String helperName = null;
0069:
0070: // Legacy Connections
0071: protected String connectionUrl = null;
0072: protected String driverName = null;
0073: protected String userName = null;
0074: protected String password = null;
0075:
0076: boolean isLegacy = false;
0077:
0078: // OFBiz DatabaseUtil
0079: public DatabaseUtil(String helperName) {
0080: this .helperName = helperName;
0081: this .modelFieldTypeReader = ModelFieldTypeReader
0082: .getModelFieldTypeReader(helperName);
0083: this .datasourceInfo = EntityConfigUtil
0084: .getDatasourceInfo(helperName);
0085: }
0086:
0087: // Legacy DatabaseUtil
0088: public DatabaseUtil(String driverName, String connectionUrl,
0089: String userName, String password) {
0090: this .driverName = driverName;
0091: this .connectionUrl = connectionUrl;
0092: this .userName = userName;
0093: this .password = password;
0094: this .isLegacy = true;
0095: }
0096:
0097: protected Connection getConnection() throws SQLException,
0098: GenericEntityException {
0099: Connection connection = null;
0100: if (!isLegacy) {
0101: connection = ConnectionFactory.getConnection(helperName);
0102: } else {
0103: connection = ConnectionFactory.getConnection(driverName,
0104: connectionUrl, null, userName, password);
0105: }
0106:
0107: if (connection == null) {
0108: if (!isLegacy) {
0109: throw new GenericEntityException(
0110: "No connection available for helper named ["
0111: + helperName + "]");
0112: } else {
0113: throw new GenericEntityException(
0114: "No connection avaialble for URL ["
0115: + connectionUrl + "]");
0116: }
0117: }
0118: if (!TransactionUtil.isTransactionInPlace()) {
0119: connection.setAutoCommit(true);
0120: }
0121: return connection;
0122: }
0123:
0124: public DatasourceInfo getDatasourceInfo() {
0125: return this .datasourceInfo;
0126: }
0127:
0128: /* ====================================================================== */
0129:
0130: /* ====================================================================== */
0131:
0132: public void checkDb(Map modelEntities, List messages,
0133: boolean addMissing) {
0134: checkDb(
0135: modelEntities,
0136: null,
0137: messages,
0138: datasourceInfo.checkPrimaryKeysOnStart,
0139: (datasourceInfo.useFks && datasourceInfo.checkForeignKeysOnStart),
0140: (datasourceInfo.useFkIndices && datasourceInfo.checkFkIndicesOnStart),
0141: addMissing);
0142: }
0143:
0144: public void checkDb(Map modelEntities, List colWrongSize,
0145: List messages, boolean checkPks, boolean checkFks,
0146: boolean checkFkIdx, boolean addMissing) {
0147: if (isLegacy) {
0148: throw new RuntimeException(
0149: "Cannot run checkDb on a legacy database connection; configure a database helper (entityengine.xml)");
0150: }
0151: UtilTimer timer = new UtilTimer();
0152: timer.timerString("Start - Before Get Database Meta Data");
0153:
0154: // get ALL tables from this database
0155: TreeSet tableNames = this .getTableNames(messages);
0156: TreeSet fkTableNames = tableNames == null ? null : new TreeSet(
0157: tableNames);
0158: TreeSet indexTableNames = tableNames == null ? null
0159: : new TreeSet(tableNames);
0160:
0161: if (tableNames == null) {
0162: String message = "Could not get table name information from the database, aborting.";
0163: if (messages != null)
0164: messages.add(message);
0165: Debug.logError(message, module);
0166: return;
0167: }
0168: timer.timerString("After Get All Table Names");
0169:
0170: // get ALL column info, put into hashmap by table name
0171: Map colInfo = this
0172: .getColumnInfo(tableNames, checkPks, messages);
0173: if (colInfo == null) {
0174: String message = "Could not get column information from the database, aborting.";
0175: if (messages != null)
0176: messages.add(message);
0177: Debug.logError(message, module);
0178: return;
0179: }
0180: timer.timerString("After Get All Column Info");
0181:
0182: // -make sure all entities have a corresponding table
0183: // -list all tables that do not have a corresponding entity
0184: // -display message if number of table columns does not match number of entity fields
0185: // -list all columns that do not have a corresponding field
0186: // -make sure each corresponding column is of the correct type
0187: // -list all fields that do not have a corresponding column
0188:
0189: timer.timerString("Before Individual Table/Column Check");
0190:
0191: ArrayList modelEntityList = new ArrayList(modelEntities
0192: .values());
0193: // sort using compareTo method on ModelEntity
0194: Collections.sort(modelEntityList);
0195: Iterator modelEntityIter = modelEntityList.iterator();
0196: int curEnt = 0;
0197: int totalEnt = modelEntityList.size();
0198: List entitiesAdded = FastList.newInstance();
0199: while (modelEntityIter.hasNext()) {
0200: curEnt++;
0201: ModelEntity entity = (ModelEntity) modelEntityIter.next();
0202:
0203: // if this is a view entity, do not check it...
0204: if (entity instanceof ModelViewEntity) {
0205: String entMessage = "(" + timer.timeSinceLast()
0206: + "ms) NOT Checking #" + curEnt + "/"
0207: + totalEnt + " View Entity "
0208: + entity.getEntityName();
0209: Debug.logVerbose(entMessage, module);
0210: if (messages != null)
0211: messages.add(entMessage);
0212: continue;
0213: }
0214:
0215: String entMessage = "(" + timer.timeSinceLast()
0216: + "ms) Checking #" + curEnt + "/" + totalEnt
0217: + " Entity " + entity.getEntityName()
0218: + " with table "
0219: + entity.getTableName(datasourceInfo);
0220:
0221: Debug.logVerbose(entMessage, module);
0222: if (messages != null)
0223: messages.add(entMessage);
0224:
0225: // -make sure all entities have a corresponding table
0226: if (tableNames
0227: .contains(entity.getTableName(datasourceInfo))) {
0228: tableNames.remove(entity.getTableName(datasourceInfo));
0229:
0230: if (colInfo != null) {
0231: Map fieldColNames = FastMap.newInstance();
0232: Iterator fieldIter = entity.getFieldsIterator();
0233: while (fieldIter.hasNext()) {
0234: ModelField field = (ModelField) fieldIter
0235: .next();
0236: fieldColNames.put(field.getColName(), field);
0237: }
0238:
0239: Map colMap = (Map) colInfo.get(entity
0240: .getTableName(datasourceInfo));
0241: if (colMap != null) {
0242: Iterator colEntryIter = colMap.entrySet()
0243: .iterator();
0244: while (colEntryIter.hasNext()) {
0245: Map.Entry colEntry = (Map.Entry) colEntryIter
0246: .next();
0247: ColumnCheckInfo ccInfo = (ColumnCheckInfo) colEntry
0248: .getValue();
0249:
0250: // -list all columns that do not have a corresponding field
0251: if (fieldColNames
0252: .containsKey(ccInfo.columnName)) {
0253: ModelField field = null;
0254:
0255: field = (ModelField) fieldColNames
0256: .remove(ccInfo.columnName);
0257: ModelFieldType modelFieldType = modelFieldTypeReader
0258: .getModelFieldType(field
0259: .getType());
0260:
0261: if (modelFieldType != null) {
0262: // make sure each corresponding column is of the correct type
0263: String fullTypeStr = modelFieldType
0264: .getSqlType();
0265: String typeName;
0266: int columnSize = -1;
0267: int decimalDigits = -1;
0268:
0269: int openParen = fullTypeStr
0270: .indexOf('(');
0271: int closeParen = fullTypeStr
0272: .indexOf(')');
0273: int comma = fullTypeStr
0274: .indexOf(',');
0275:
0276: if (openParen > 0 && closeParen > 0
0277: && closeParen > openParen) {
0278: typeName = fullTypeStr
0279: .substring(0, openParen);
0280: if (comma > 0
0281: && comma > openParen
0282: && comma < closeParen) {
0283: String csStr = fullTypeStr
0284: .substring(
0285: openParen + 1,
0286: comma);
0287: try {
0288: columnSize = Integer
0289: .parseInt(csStr);
0290: } catch (NumberFormatException e) {
0291: Debug.logError(e,
0292: module);
0293: }
0294:
0295: String ddStr = fullTypeStr
0296: .substring(
0297: comma + 1,
0298: closeParen);
0299: try {
0300: decimalDigits = Integer
0301: .parseInt(ddStr);
0302: } catch (NumberFormatException e) {
0303: Debug.logError(e,
0304: module);
0305: }
0306: } else {
0307: String csStr = fullTypeStr
0308: .substring(
0309: openParen + 1,
0310: closeParen);
0311: try {
0312: columnSize = Integer
0313: .parseInt(csStr);
0314: } catch (NumberFormatException e) {
0315: Debug.logError(e,
0316: module);
0317: }
0318: }
0319: } else {
0320: typeName = fullTypeStr;
0321: }
0322:
0323: // override the default typeName with the sqlTypeAlias if it is specified
0324: if (UtilValidate
0325: .isNotEmpty(modelFieldType
0326: .getSqlTypeAlias())) {
0327: typeName = modelFieldType
0328: .getSqlTypeAlias();
0329: }
0330:
0331: // NOTE: this may need a toUpperCase in some cases, keep an eye on it, okay just compare with ignore case
0332: if (!ccInfo.typeName
0333: .equalsIgnoreCase(typeName)) {
0334: String message = "WARNING: Column ["
0335: + ccInfo.columnName
0336: + "] of table ["
0337: + entity
0338: .getTableName(datasourceInfo)
0339: + "] of entity ["
0340: + entity
0341: .getEntityName()
0342: + "] is of type ["
0343: + ccInfo.typeName
0344: + "] in the database, but is defined as type ["
0345: + typeName
0346: + "] in the entity definition.";
0347: Debug.logError(message, module);
0348: if (messages != null)
0349: messages.add(message);
0350: }
0351: if (columnSize != -1
0352: && ccInfo.columnSize != -1
0353: && columnSize != ccInfo.columnSize
0354: && (columnSize * 3) != ccInfo.columnSize) {
0355: String message = "WARNING: Column ["
0356: + ccInfo.columnName
0357: + "] of table ["
0358: + entity
0359: .getTableName(datasourceInfo)
0360: + "] of entity ["
0361: + entity
0362: .getEntityName()
0363: + "] has a column size of ["
0364: + ccInfo.columnSize
0365: + "] in the database, but is defined to have a column size of ["
0366: + columnSize
0367: + "] in the entity definition.";
0368: Debug.logWarning(message,
0369: module);
0370: if (messages != null)
0371: messages.add(message);
0372: if (columnSize > ccInfo.columnSize
0373: && colWrongSize != null) {
0374: // add item to list of wrong sized columns; only if the entity is larger
0375: colWrongSize.add(entity
0376: .getEntityName()
0377: + "."
0378: + field.getName());
0379: }
0380: }
0381: if (decimalDigits != -1
0382: && decimalDigits != ccInfo.decimalDigits) {
0383: String message = "WARNING: Column ["
0384: + ccInfo.columnName
0385: + "] of table ["
0386: + entity
0387: .getTableName(datasourceInfo)
0388: + "] of entity ["
0389: + entity
0390: .getEntityName()
0391: + "] has a decimalDigits of ["
0392: + ccInfo.decimalDigits
0393: + "] in the database, but is defined to have a decimalDigits of ["
0394: + decimalDigits
0395: + "] in the entity definition.";
0396: Debug.logWarning(message,
0397: module);
0398: if (messages != null)
0399: messages.add(message);
0400: }
0401:
0402: // do primary key matching check
0403: if (checkPks && ccInfo.isPk
0404: && !field.getIsPk()) {
0405: String message = "WARNING: Column ["
0406: + ccInfo.columnName
0407: + "] of table ["
0408: + entity
0409: .getTableName(datasourceInfo)
0410: + "] of entity ["
0411: + entity
0412: .getEntityName()
0413: + "] IS a primary key in the database, but IS NOT a primary key in the entity definition. The primary key for this table needs to be re-created or modified so that this column is NOT party of the primary key.";
0414: Debug.logError(message, module);
0415: if (messages != null)
0416: messages.add(message);
0417: }
0418: if (checkPks && !ccInfo.isPk
0419: && field.getIsPk()) {
0420: String message = "WARNING: Column ["
0421: + ccInfo.columnName
0422: + "] of table ["
0423: + entity
0424: .getTableName(datasourceInfo)
0425: + "] of entity ["
0426: + entity
0427: .getEntityName()
0428: + "] IS NOT a primary key in the database, but IS a primary key in the entity definition. The primary key for this table needs to be re-created or modified to add this column to the primary key. Note that data may need to be added first as a primary key column cannot have an null values.";
0429: Debug.logError(message, module);
0430: if (messages != null)
0431: messages.add(message);
0432: }
0433: } else {
0434: String message = "Column ["
0435: + ccInfo.columnName
0436: + "] of table ["
0437: + entity
0438: .getTableName(datasourceInfo)
0439: + "] of entity ["
0440: + entity.getEntityName()
0441: + "] has a field type name of ["
0442: + field.getType()
0443: + "] which is not found in the field type definitions";
0444: Debug.logError(message, module);
0445: if (messages != null)
0446: messages.add(message);
0447: }
0448: } else {
0449: String message = "Column ["
0450: + ccInfo.columnName
0451: + "] of table ["
0452: + entity
0453: .getTableName(datasourceInfo)
0454: + "] of entity ["
0455: + entity.getEntityName()
0456: + "] exists in the database but has no corresponding field"
0457: + ((checkPks && ccInfo.isPk) ? " (and it is a PRIMARY KEY COLUMN)"
0458: : "");
0459: Debug.logWarning(message, module);
0460: if (messages != null)
0461: messages.add(message);
0462: }
0463: }
0464:
0465: // -display message if number of table columns does not match number of entity fields
0466: if (colMap.size() != entity.getFieldsSize()) {
0467: String message = "Entity ["
0468: + entity.getEntityName()
0469: + "] has "
0470: + entity.getFieldsSize()
0471: + " fields but table ["
0472: + entity
0473: .getTableName(datasourceInfo)
0474: + "] has " + colMap.size()
0475: + " columns.";
0476: Debug.logWarning(message, module);
0477: if (messages != null)
0478: messages.add(message);
0479: }
0480: }
0481:
0482: // -list all fields that do not have a corresponding column
0483: Iterator fcnIter = fieldColNames.keySet()
0484: .iterator();
0485: while (fcnIter.hasNext()) {
0486: String colName = (String) fcnIter.next();
0487: ModelField field = (ModelField) fieldColNames
0488: .get(colName);
0489: String message = "Field ["
0490: + field.getName()
0491: + "] of entity ["
0492: + entity.getEntityName()
0493: + "] is missing its corresponding column ["
0494: + field.getColName()
0495: + "]"
0496: + (field.getIsPk() ? " (and it is a PRIMARY KEY FIELD)"
0497: : "");
0498:
0499: Debug.logWarning(message, module);
0500: if (messages != null)
0501: messages.add(message);
0502:
0503: if (addMissing) {
0504: // add the column
0505: String errMsg = addColumn(entity, field);
0506:
0507: if (errMsg != null && errMsg.length() > 0) {
0508: message = "Could not add column ["
0509: + field.getColName()
0510: + "] to table ["
0511: + entity
0512: .getTableName(datasourceInfo)
0513: + "]: " + errMsg;
0514: Debug.logError(message, module);
0515: if (messages != null)
0516: messages.add(message);
0517: } else {
0518: message = "Added column ["
0519: + field.getColName()
0520: + "] to table ["
0521: + entity
0522: .getTableName(datasourceInfo)
0523: + "]"
0524: + (field.getIsPk() ? " (NOTE: this is a PRIMARY KEY FIELD, but the primary key was not updated automatically (not considered a safe operation), be sure to fill in any needed data and re-create the primary key)"
0525: : "");
0526: Debug.logImportant(message, module);
0527: if (messages != null)
0528: messages.add(message);
0529: }
0530: }
0531: }
0532: }
0533: } else {
0534: String message = "Entity [" + entity.getEntityName()
0535: + "] has no table in the database";
0536: Debug.logWarning(message, module);
0537: if (messages != null)
0538: messages.add(message);
0539:
0540: if (addMissing) {
0541: // create the table
0542: String errMsg = createTable(entity, modelEntities,
0543: false);
0544: if (errMsg != null && errMsg.length() > 0) {
0545: message = "Could not create table ["
0546: + entity.getTableName(datasourceInfo)
0547: + "]: " + errMsg;
0548: Debug.logError(message, module);
0549: if (messages != null)
0550: messages.add(message);
0551: } else {
0552: entitiesAdded.add(entity);
0553: message = "Created table ["
0554: + entity.getTableName(datasourceInfo)
0555: + "]";
0556: Debug.logImportant(message, module);
0557: if (messages != null)
0558: messages.add(message);
0559: }
0560: }
0561: }
0562: }
0563:
0564: timer.timerString("After Individual Table/Column Check");
0565:
0566: // -list all tables that do not have a corresponding entity
0567: Iterator tableNamesIter = tableNames.iterator();
0568: while (tableNamesIter != null && tableNamesIter.hasNext()) {
0569: String tableName = (String) tableNamesIter.next();
0570: String message = "Table named ["
0571: + tableName
0572: + "] exists in the database but has no corresponding entity";
0573: Debug.logWarning(message, module);
0574: if (messages != null)
0575: messages.add(message);
0576: }
0577:
0578: // for each newly added table, add fk indices
0579: if (datasourceInfo.useFkIndices) {
0580: int totalFkIndices = 0;
0581: Iterator eaIter = entitiesAdded.iterator();
0582: while (eaIter.hasNext()) {
0583: ModelEntity curEntity = (ModelEntity) eaIter.next();
0584: if (curEntity.getRelationsOneSize() > 0) {
0585: totalFkIndices += this .createForeignKeyIndices(
0586: curEntity,
0587: datasourceInfo.constraintNameClipLength,
0588: messages);
0589: }
0590: }
0591: if (totalFkIndices > 0)
0592: Debug.logImportant(
0593: "==== TOTAL Foreign Key Indices Created: "
0594: + totalFkIndices, module);
0595: }
0596:
0597: // for each newly added table, add fks
0598: if (datasourceInfo.useFks) {
0599: int totalFks = 0;
0600: Iterator eaIter = entitiesAdded.iterator();
0601: while (eaIter.hasNext()) {
0602: ModelEntity curEntity = (ModelEntity) eaIter.next();
0603: totalFks += this
0604: .createForeignKeys(
0605: curEntity,
0606: modelEntities,
0607: datasourceInfo.constraintNameClipLength,
0608: datasourceInfo.fkStyle,
0609: datasourceInfo.useFkInitiallyDeferred,
0610: messages);
0611: }
0612: if (totalFks > 0)
0613: Debug.logImportant("==== TOTAL Foreign Keys Created: "
0614: + totalFks, module);
0615: }
0616:
0617: // for each newly added table, add declared indexes
0618: if (datasourceInfo.useIndices) {
0619: int totalDis = 0;
0620: Iterator eaIter = entitiesAdded.iterator();
0621: while (eaIter.hasNext()) {
0622: ModelEntity curEntity = (ModelEntity) eaIter.next();
0623: if (curEntity.getIndexesSize() > 0) {
0624: totalDis += this .createDeclaredIndices(curEntity,
0625: messages);
0626: }
0627: }
0628: if (totalDis > 0)
0629: Debug.logImportant(
0630: "==== TOTAL Declared Indices Created: "
0631: + totalDis, module);
0632: }
0633:
0634: // make sure each one-relation has an FK
0635: if (checkFks) {
0636: //if (!justColumns && datasourceInfo.useFks && datasourceInfo.checkForeignKeysOnStart) {
0637: // NOTE: This ISN'T working for Postgres or MySQL, who knows about others, may be from JDBC driver bugs...
0638: int numFksCreated = 0;
0639: // TODO: check each key-map to make sure it exists in the FK, if any differences warn and then remove FK and recreate it
0640:
0641: // get ALL column info, put into hashmap by table name
0642: Map refTableInfoMap = this .getReferenceInfo(fkTableNames,
0643: messages);
0644:
0645: // Debug.logVerbose("Ref Info Map: " + refTableInfoMap, module);
0646:
0647: if (refTableInfoMap == null) {
0648: // uh oh, something happened while getting info...
0649: if (Debug.verboseOn())
0650: Debug.logVerbose("Ref Table Info Map is null",
0651: module);
0652: } else {
0653: Iterator refModelEntityIter = modelEntityList
0654: .iterator();
0655: while (refModelEntityIter.hasNext()) {
0656: ModelEntity entity = (ModelEntity) refModelEntityIter
0657: .next();
0658: String entityName = entity.getEntityName();
0659: // if this is a view entity, do not check it...
0660: if (entity instanceof ModelViewEntity) {
0661: String entMessage = "NOT Checking View Entity "
0662: + entity.getEntityName();
0663: Debug.logVerbose(entMessage, module);
0664: if (messages != null) {
0665: messages.add(entMessage);
0666: }
0667: continue;
0668: }
0669:
0670: // get existing FK map for this table
0671: Map rcInfoMap = (Map) refTableInfoMap.get(entity
0672: .getTableName(datasourceInfo));
0673: // Debug.logVerbose("Got ref info for table " + entity.getTableName(datasourceInfo) + ": " + rcInfoMap, module);
0674:
0675: // go through each relation to see if an FK already exists
0676: Iterator relations = entity.getRelationsIterator();
0677: boolean createdConstraints = false;
0678: while (relations.hasNext()) {
0679: ModelRelation modelRelation = (ModelRelation) relations
0680: .next();
0681: if (!"one".equals(modelRelation.getType())) {
0682: continue;
0683: }
0684:
0685: ModelEntity relModelEntity = (ModelEntity) modelEntities
0686: .get(modelRelation.getRelEntityName());
0687: if (relModelEntity == null) {
0688: Debug.logError("No such relation: "
0689: + entity.getEntityName() + " -> "
0690: + modelRelation.getRelEntityName(),
0691: module);
0692: continue;
0693: }
0694: String relConstraintName = makeFkConstraintName(
0695: modelRelation,
0696: datasourceInfo.constraintNameClipLength);
0697: ReferenceCheckInfo rcInfo = null;
0698:
0699: if (rcInfoMap != null) {
0700: rcInfo = (ReferenceCheckInfo) rcInfoMap
0701: .get(relConstraintName);
0702: }
0703:
0704: if (rcInfo != null) {
0705: rcInfoMap.remove(relConstraintName);
0706: } else {
0707: // if not, create one
0708: String noFkMessage = "No Foreign Key Constraint ["
0709: + relConstraintName
0710: + "] found for entity ["
0711: + entityName + "]";
0712: if (messages != null)
0713: messages.add(noFkMessage);
0714: if (Debug.infoOn())
0715: Debug.logInfo(noFkMessage, module);
0716:
0717: if (addMissing) {
0718: String errMsg = createForeignKey(
0719: entity,
0720: modelRelation,
0721: relModelEntity,
0722: datasourceInfo.constraintNameClipLength,
0723: datasourceInfo.fkStyle,
0724: datasourceInfo.useFkInitiallyDeferred);
0725: if (errMsg != null
0726: && errMsg.length() > 0) {
0727: String message = "Could not create foreign key "
0728: + relConstraintName
0729: + " for entity ["
0730: + entity.getEntityName()
0731: + "]: " + errMsg;
0732: Debug.logError(message, module);
0733: if (messages != null)
0734: messages.add(message);
0735: } else {
0736: String message = "Created foreign key "
0737: + relConstraintName
0738: + " for entity ["
0739: + entity.getEntityName()
0740: + "]";
0741: Debug.logVerbose(message, module);
0742: if (messages != null)
0743: messages.add(message);
0744: createdConstraints = true;
0745: numFksCreated++;
0746: }
0747: }
0748: }
0749: }
0750: if (createdConstraints) {
0751: String message = "Created foreign key(s) for entity ["
0752: + entity.getEntityName() + "]";
0753: Debug.logImportant(message, module);
0754: if (messages != null)
0755: messages.add(message);
0756: }
0757:
0758: // show foreign key references that exist but are unknown
0759: if (rcInfoMap != null) {
0760: Iterator rcInfoKeysLeft = rcInfoMap.keySet()
0761: .iterator();
0762: while (rcInfoKeysLeft.hasNext()) {
0763: String rcKeyLeft = (String) rcInfoKeysLeft
0764: .next();
0765: String message = "Unknown Foreign Key Constraint "
0766: + rcKeyLeft
0767: + " found in table "
0768: + entity
0769: .getTableName(datasourceInfo);
0770: Debug.logImportant(message, module);
0771: if (messages != null)
0772: messages.add(message);
0773: }
0774: }
0775: }
0776: }
0777: if (Debug.infoOn())
0778: Debug.logInfo("Created " + numFksCreated + " fk refs",
0779: module);
0780: }
0781:
0782: // make sure each one-relation has an index
0783: if (checkFkIdx) {
0784: //if (!justColumns && datasourceInfo.useFkIndices && datasourceInfo.checkFkIndicesOnStart) {
0785: int numIndicesCreated = 0;
0786: // TODO: check each key-map to make sure it exists in the index, if any differences warn and then remove the index and recreate it
0787:
0788: // TODO: also check the declared indices on start, if the datasourceInfo.checkIndicesOnStart flag is set
0789:
0790: // get ALL column info, put into hashmap by table name
0791: Map tableIndexListMap = this .getIndexInfo(indexTableNames,
0792: messages);
0793:
0794: // Debug.logVerbose("Ref Info Map: " + refTableInfoMap, module);
0795:
0796: if (tableIndexListMap == null) {
0797: // uh oh, something happened while getting info...
0798: if (Debug.verboseOn())
0799: Debug.logVerbose("Ref Table Info Map is null",
0800: module);
0801: } else {
0802: Iterator refModelEntityIter = modelEntityList
0803: .iterator();
0804: while (refModelEntityIter.hasNext()) {
0805: ModelEntity entity = (ModelEntity) refModelEntityIter
0806: .next();
0807: String entityName = entity.getEntityName();
0808: // if this is a view entity, do not check it...
0809: if (entity instanceof ModelViewEntity) {
0810: String entMessage = "NOT Checking View Entity "
0811: + entity.getEntityName();
0812: Debug.logVerbose(entMessage, module);
0813: if (messages != null)
0814: messages.add(entMessage);
0815: continue;
0816: }
0817:
0818: // get existing index list for this table
0819: TreeSet tableIndexList = (TreeSet) tableIndexListMap
0820: .get(entity.getTableName(datasourceInfo));
0821:
0822: // Debug.logVerbose("Got ind info for table " + entity.getTableName(datasourceInfo) + ": " + tableIndexList, module);
0823:
0824: if (tableIndexList == null) {
0825: // evidently no indexes in the database for this table, do the create all
0826: this
0827: .createForeignKeyIndices(
0828: entity,
0829: datasourceInfo.constraintNameClipLength,
0830: messages);
0831: } else {
0832: // go through each relation to see if an FK already exists
0833: boolean createdConstraints = false;
0834: Iterator relations = entity
0835: .getRelationsIterator();
0836: while (relations.hasNext()) {
0837: ModelRelation modelRelation = (ModelRelation) relations
0838: .next();
0839: if (!"one".equals(modelRelation.getType())) {
0840: continue;
0841: }
0842:
0843: String relConstraintName = makeFkConstraintName(
0844: modelRelation,
0845: datasourceInfo.constraintNameClipLength);
0846: if (tableIndexList
0847: .contains(relConstraintName)) {
0848: tableIndexList
0849: .remove(relConstraintName);
0850: } else {
0851: // if not, create one
0852: String noIdxMessage = "No Index ["
0853: + relConstraintName
0854: + "] found for entity ["
0855: + entityName + "]";
0856: if (messages != null)
0857: messages.add(noIdxMessage);
0858: if (Debug.infoOn())
0859: Debug.logInfo(noIdxMessage, module);
0860:
0861: if (addMissing) {
0862: String errMsg = createForeignKeyIndex(
0863: entity,
0864: modelRelation,
0865: datasourceInfo.constraintNameClipLength);
0866: if (errMsg != null
0867: && errMsg.length() > 0) {
0868: String message = "Could not create foreign key index "
0869: + relConstraintName
0870: + " for entity ["
0871: + entity
0872: .getEntityName()
0873: + "]: " + errMsg;
0874: Debug.logError(message, module);
0875: if (messages != null)
0876: messages.add(message);
0877: } else {
0878: String message = "Created foreign key index "
0879: + relConstraintName
0880: + " for entity ["
0881: + entity
0882: .getEntityName()
0883: + "]";
0884: Debug.logVerbose(message,
0885: module);
0886: if (messages != null)
0887: messages.add(message);
0888: createdConstraints = true;
0889: numIndicesCreated++;
0890: }
0891: }
0892: }
0893: }
0894: if (createdConstraints) {
0895: String message = "Created foreign key index/indices for entity ["
0896: + entity.getEntityName() + "]";
0897: Debug.logImportant(message, module);
0898: if (messages != null)
0899: messages.add(message);
0900: }
0901: }
0902:
0903: // show foreign key references that exist but are unknown
0904: if (tableIndexList != null) {
0905: Iterator tableIndexListIter = tableIndexList
0906: .iterator();
0907: while (tableIndexListIter.hasNext()) {
0908: String indexLeft = (String) tableIndexListIter
0909: .next();
0910: String message = "Unknown Index "
0911: + indexLeft
0912: + " found in table "
0913: + entity
0914: .getTableName(datasourceInfo);
0915: Debug.logImportant(message, module);
0916: if (messages != null)
0917: messages.add(message);
0918: }
0919: }
0920: }
0921: }
0922: if (Debug.infoOn())
0923: Debug.logInfo("Created " + numIndicesCreated
0924: + " indices", module);
0925: }
0926:
0927: timer.timerString("Finished Checking Entity Database");
0928: }
0929:
0930: /** Creates a list of ModelEntity objects based on meta data from the database */
0931: public List induceModelFromDb(Collection messages) {
0932: // get ALL tables from this database
0933: TreeSet tableNames = this .getTableNames(messages);
0934:
0935: // get ALL column info, put into hashmap by table name
0936: Map colInfo = this .getColumnInfo(tableNames, true, messages);
0937:
0938: // go through each table and make a ModelEntity object, add to list
0939: // for each entity make corresponding ModelField objects
0940: // then print out XML for the entities/fields
0941: List newEntList = FastList.newInstance();
0942:
0943: boolean isCaseSensitive = false;
0944: DatabaseMetaData dbData = this .getDatabaseMetaData(null,
0945: messages);
0946: if (dbData != null) {
0947: try {
0948: isCaseSensitive = dbData.supportsMixedCaseIdentifiers();
0949: } catch (SQLException e) {
0950: Debug
0951: .logError(
0952: e,
0953: "Error getting db meta data about case sensitive",
0954: module);
0955: }
0956: }
0957:
0958: // iterate over the table names is alphabetical order
0959: Iterator tableNamesIter = new TreeSet(colInfo.keySet())
0960: .iterator();
0961: while (tableNamesIter.hasNext()) {
0962: String tableName = (String) tableNamesIter.next();
0963: Map colMap = (Map) colInfo.get(tableName);
0964: ModelEntity newEntity = new ModelEntity(tableName, colMap,
0965: modelFieldTypeReader, isCaseSensitive);
0966: newEntList.add(newEntity);
0967: }
0968:
0969: return newEntList;
0970: }
0971:
0972: public Document induceModelFromDb(String packageName) {
0973: Document document = UtilXml.makeEmptyXmlDocument("entitymodel");
0974: Element root = document.getDocumentElement();
0975: root.appendChild(document.createElement("title"));
0976: root.appendChild(document.createElement("description"));
0977: root.appendChild(document.createElement("copyright"));
0978: root.appendChild(document.createElement("author"));
0979: root.appendChild(document.createElement("version"));
0980:
0981: // messages list
0982: List messages = new ArrayList();
0983:
0984: // get ALL tables from this database
0985: TreeSet tableNames = this .getTableNames(messages);
0986:
0987: // get ALL column info, put into hashmap by table name
0988: Map colInfo = this .getColumnInfo(tableNames, true, messages);
0989:
0990: boolean isCaseSensitive = false;
0991: DatabaseMetaData dbData = this .getDatabaseMetaData(null,
0992: messages);
0993: if (dbData != null) {
0994: try {
0995: isCaseSensitive = dbData.supportsMixedCaseIdentifiers();
0996: } catch (SQLException e) {
0997: Debug
0998: .logError(
0999: e,
1000: "Error getting db meta data about case sensitive",
1001: module);
1002: }
1003: }
1004:
1005: if (UtilValidate.isNotEmpty(packageName)) {
1006: String catalogName = null;
1007: try {
1008: catalogName = this .getConnection().getCatalog();
1009: } catch (Exception e) {
1010: // ignore
1011: }
1012: packageName = "org.ofbiz.ext."
1013: + (catalogName != null ? catalogName : "unknown");
1014: }
1015:
1016: // iterate over the table names is alphabetical order
1017: Iterator tableNamesIter = new TreeSet(colInfo.keySet())
1018: .iterator();
1019: while (tableNamesIter.hasNext()) {
1020: String tableName = (String) tableNamesIter.next();
1021: Map colMap = (Map) colInfo.get(tableName);
1022: ModelEntity newEntity = new ModelEntity(tableName, colMap,
1023: modelFieldTypeReader, isCaseSensitive);
1024: root.appendChild(newEntity.toXmlElement(document,
1025: "org.ofbiz.ext." + packageName));
1026: }
1027:
1028: // print the messages to the console
1029: for (int i = 0; i < messages.size(); i++) {
1030: Debug.logInfo((String) messages.get(i), module);
1031: }
1032: return document;
1033: }
1034:
1035: public Document induceModelFromDb() {
1036: return this .induceModelFromDb("");
1037: }
1038:
1039: public DatabaseMetaData getDatabaseMetaData(Connection connection,
1040: Collection messages) {
1041: if (connection == null) {
1042: try {
1043: connection = getConnection();
1044: } catch (SQLException e) {
1045: String message = "Unable to esablish a connection with the database... Error was:"
1046: + e.toString();
1047: Debug.logError(message, module);
1048: if (messages != null)
1049: messages.add(message);
1050: return null;
1051: } catch (GenericEntityException e) {
1052: String message = "Unable to esablish a connection with the database... Error was:"
1053: + e.toString();
1054: Debug.logError(message, module);
1055: if (messages != null)
1056: messages.add(message);
1057: return null;
1058: }
1059: }
1060:
1061: if (connection == null) {
1062: String message = "Unable to esablish a connection with the database, no additional information available.";
1063: Debug.logError(message, module);
1064: if (messages != null)
1065: messages.add(message);
1066: return null;
1067: }
1068:
1069: DatabaseMetaData dbData = null;
1070: try {
1071: dbData = connection.getMetaData();
1072: } catch (SQLException e) {
1073: String message = "Unable to get database meta data... Error was:"
1074: + e.toString();
1075: Debug.logError(message, module);
1076: if (messages != null) {
1077: messages.add(message);
1078: }
1079: return null;
1080: }
1081:
1082: if (dbData == null) {
1083: Debug
1084: .logWarning(
1085: "Unable to get database meta data; method returned null",
1086: module);
1087: }
1088:
1089: return dbData;
1090: }
1091:
1092: public void printDbMiscData(DatabaseMetaData dbData, Connection con) {
1093: if (dbData == null) {
1094: return;
1095: }
1096: // Database Info
1097: if (Debug.infoOn()) {
1098: try {
1099: Debug.logInfo("Database Product Name is "
1100: + dbData.getDatabaseProductName(), module);
1101: Debug.logInfo("Database Product Version is "
1102: + dbData.getDatabaseProductVersion(), module);
1103: } catch (SQLException e) {
1104: Debug
1105: .logWarning(
1106: "Unable to get Database name & version information",
1107: module);
1108: }
1109: }
1110: // JDBC Driver Info
1111: if (Debug.infoOn()) {
1112: try {
1113: Debug.logInfo("Database Driver Name is "
1114: + dbData.getDriverName(), module);
1115: Debug.logInfo("Database Driver Version is "
1116: + dbData.getDriverVersion(), module);
1117: Debug.logInfo("Database Driver JDBC Version is "
1118: + dbData.getJDBCMajorVersion() + "."
1119: + dbData.getJDBCMinorVersion(), module);
1120: } catch (SQLException e) {
1121: Debug
1122: .logWarning(
1123: "Unable to get Driver name & version information",
1124: module);
1125: } catch (AbstractMethodError ame) {
1126: Debug.logWarning("Unable to get Driver JDBC Version",
1127: module);
1128: }
1129: }
1130: // Db/Driver support settings
1131: if (Debug.infoOn()) {
1132: try {
1133: Debug
1134: .logInfo(
1135: "Database Setting/Support Information (those with a * should be true):",
1136: module);
1137: Debug.logInfo("- supports transactions ["
1138: + dbData.supportsTransactions() + "]*", module);
1139: Debug
1140: .logInfo(
1141: "- isolation None ["
1142: + dbData
1143: .supportsTransactionIsolationLevel(Connection.TRANSACTION_NONE)
1144: + "]", module);
1145: Debug
1146: .logInfo(
1147: "- isolation ReadCommitted ["
1148: + dbData
1149: .supportsTransactionIsolationLevel(Connection.TRANSACTION_READ_COMMITTED)
1150: + "]", module);
1151: Debug
1152: .logInfo(
1153: "- isolation ReadUncommitted["
1154: + dbData
1155: .supportsTransactionIsolationLevel(Connection.TRANSACTION_READ_UNCOMMITTED)
1156: + "]", module);
1157: Debug
1158: .logInfo(
1159: "- isolation RepeatableRead ["
1160: + dbData
1161: .supportsTransactionIsolationLevel(Connection.TRANSACTION_REPEATABLE_READ)
1162: + "]", module);
1163: Debug
1164: .logInfo(
1165: "- isolation Serializable ["
1166: + dbData
1167: .supportsTransactionIsolationLevel(Connection.TRANSACTION_SERIALIZABLE)
1168: + "]", module);
1169: Debug.logInfo("- default fetchsize ["
1170: + con.createStatement().getFetchSize() + "]",
1171: module);
1172: Debug
1173: .logInfo(
1174: "- forward only type ["
1175: + dbData
1176: .supportsResultSetType(ResultSet.TYPE_FORWARD_ONLY)
1177: + "]", module);
1178: Debug
1179: .logInfo(
1180: "- scroll sensitive type ["
1181: + dbData
1182: .supportsResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE)
1183: + "]", module);
1184: Debug
1185: .logInfo(
1186: "- scroll insensitive type ["
1187: + dbData
1188: .supportsResultSetType(ResultSet.TYPE_SCROLL_INSENSITIVE)
1189: + "]", module);
1190: Debug.logInfo("- is case sensitive ["
1191: + dbData.supportsMixedCaseIdentifiers() + "]",
1192: module);
1193: Debug.logInfo("- stores LowerCase ["
1194: + dbData.storesLowerCaseIdentifiers() + "]",
1195: module);
1196: Debug.logInfo("- stores MixedCase ["
1197: + dbData.storesMixedCaseIdentifiers() + "]",
1198: module);
1199: Debug.logInfo("- stores UpperCase ["
1200: + dbData.storesUpperCaseIdentifiers() + "]",
1201: module);
1202: Debug.logInfo("- max table name length ["
1203: + dbData.getMaxTableNameLength() + "]", module);
1204: Debug
1205: .logInfo(
1206: "- max column name length ["
1207: + dbData
1208: .getMaxColumnNameLength()
1209: + "]", module);
1210: Debug
1211: .logInfo(
1212: "- max schema name length ["
1213: + dbData
1214: .getMaxSchemaNameLength()
1215: + "]", module);
1216: Debug.logInfo("- concurrent connections ["
1217: + dbData.getMaxConnections() + "]", module);
1218: Debug.logInfo("- concurrent statements ["
1219: + dbData.getMaxStatements() + "]", module);
1220: Debug.logInfo("- ANSI SQL92 Entry ["
1221: + dbData.supportsANSI92EntryLevelSQL() + "]",
1222: module);
1223: Debug.logInfo("- ANSI SQL92 Itermediate ["
1224: + dbData.supportsANSI92IntermediateSQL() + "]",
1225: module);
1226: Debug.logInfo("- ANSI SQL92 Full ["
1227: + dbData.supportsANSI92FullSQL() + "]", module);
1228: Debug
1229: .logInfo(
1230: "- ODBC SQL Grammar Core ["
1231: + dbData
1232: .supportsCoreSQLGrammar()
1233: + "]", module);
1234: Debug.logInfo("- ODBC SQL Grammar Extended["
1235: + dbData.supportsExtendedSQLGrammar() + "]",
1236: module);
1237: Debug.logInfo("- ODBC SQL Grammar Minimum ["
1238: + dbData.supportsMinimumSQLGrammar() + "]",
1239: module);
1240: Debug.logInfo("- outer joins ["
1241: + dbData.supportsOuterJoins() + "]*", module);
1242: Debug.logInfo("- limited outer joins ["
1243: + dbData.supportsLimitedOuterJoins() + "]",
1244: module);
1245: Debug
1246: .logInfo(
1247: "- full outer joins ["
1248: + dbData
1249: .supportsFullOuterJoins()
1250: + "]", module);
1251: Debug.logInfo("- group by ["
1252: + dbData.supportsGroupBy() + "]*", module);
1253: Debug.logInfo("- group by not in select ["
1254: + dbData.supportsGroupByUnrelated() + "]",
1255: module);
1256: Debug
1257: .logInfo(
1258: "- column aliasing ["
1259: + dbData
1260: .supportsColumnAliasing()
1261: + "]", module);
1262: Debug.logInfo("- order by not in select ["
1263: + dbData.supportsOrderByUnrelated() + "]",
1264: module);
1265: // this doesn't work in HSQLDB, other databases? Debug.logInfo("- named parameters [" + dbData.supportsNamedParameters() + "]", module);
1266: Debug.logInfo("- alter table add column ["
1267: + dbData.supportsAlterTableWithAddColumn()
1268: + "]*", module);
1269: Debug.logInfo("- non-nullable column ["
1270: + dbData.supportsNonNullableColumns() + "]*",
1271: module);
1272: } catch (Exception e) {
1273: Debug
1274: .logWarning(
1275: e,
1276: "Unable to get misc. support/setting information",
1277: module);
1278: }
1279: }
1280: }
1281:
1282: public TreeSet getTableNames(Collection messages) {
1283: Connection connection = null;
1284:
1285: try {
1286: connection = getConnection();
1287: } catch (SQLException e) {
1288: String message = "Unable to esablish a connection with the database... Error was:"
1289: + e.toString();
1290: Debug.logError(message, module);
1291: if (messages != null)
1292: messages.add(message);
1293: return null;
1294: } catch (GenericEntityException e) {
1295: String message = "Unable to esablish a connection with the database... Error was:"
1296: + e.toString();
1297: Debug.logError(message, module);
1298: if (messages != null)
1299: messages.add(message);
1300: return null;
1301: }
1302:
1303: if (connection == null) {
1304: String message = "Unable to esablish a connection with the database, no additional information available.";
1305: Debug.logError(message, module);
1306: if (messages != null)
1307: messages.add(message);
1308: return null;
1309: }
1310:
1311: DatabaseMetaData dbData = this .getDatabaseMetaData(connection,
1312: messages);
1313: if (dbData == null) {
1314: return null;
1315: }
1316:
1317: printDbMiscData(dbData, connection);
1318: if (Debug.infoOn())
1319: Debug.logInfo("Getting Table Info From Database", module);
1320:
1321: // get ALL tables from this database
1322: TreeSet tableNames = new TreeSet();
1323: ResultSet tableSet = null;
1324:
1325: String lookupSchemaName = null;
1326: try {
1327: String[] types = { "TABLE", "VIEW", "ALIAS", "SYNONYM" };
1328: lookupSchemaName = getSchemaName(dbData);
1329: tableSet = dbData.getTables(null, lookupSchemaName, null,
1330: types);
1331: if (tableSet == null) {
1332: Debug.logWarning("getTables returned null set", module);
1333: }
1334: } catch (SQLException e) {
1335: String message = "Unable to get list of table information, let's try the create anyway... Error was:"
1336: + e.toString();
1337: Debug.logError(message, module);
1338: if (messages != null)
1339: messages.add(message);
1340:
1341: try {
1342: connection.close();
1343: } catch (SQLException e2) {
1344: String message2 = "Unable to close database connection, continuing anyway... Error was:"
1345: + e2.toString();
1346: Debug.logError(message2, module);
1347: if (messages != null)
1348: messages.add(message2);
1349: }
1350: // we are returning an empty set here because databases like SapDB throw an exception when there are no tables in the database
1351: return tableNames;
1352: }
1353:
1354: try {
1355: boolean needsUpperCase = false;
1356: try {
1357: needsUpperCase = dbData.storesLowerCaseIdentifiers()
1358: || dbData.storesMixedCaseIdentifiers();
1359: } catch (SQLException e) {
1360: String message = "Error getting identifier case information... Error was:"
1361: + e.toString();
1362: Debug.logError(message, module);
1363: if (messages != null)
1364: messages.add(message);
1365: }
1366: while (tableSet.next()) {
1367: try {
1368: String tableName = tableSet.getString("TABLE_NAME");
1369: // for those databases which do not return the schema name with the table name (pgsql 7.3)
1370: boolean appendSchemaName = false;
1371: if (tableName != null && lookupSchemaName != null
1372: && !tableName.startsWith(lookupSchemaName)) {
1373: appendSchemaName = true;
1374: }
1375: if (needsUpperCase && tableName != null) {
1376: tableName = tableName.toUpperCase();
1377: }
1378: if (appendSchemaName) {
1379: tableName = lookupSchemaName + "." + tableName;
1380: }
1381:
1382: // NOTE: this may need a toUpperCase in some cases, keep an eye on it, okay for now just do a compare with equalsIgnoreCase
1383: String tableType = tableSet.getString("TABLE_TYPE");
1384: // only allow certain table types
1385: if (tableType != null
1386: && !"TABLE".equalsIgnoreCase(tableType)
1387: && !"VIEW".equalsIgnoreCase(tableType)
1388: && !"ALIAS".equalsIgnoreCase(tableType)
1389: && !"SYNONYM".equalsIgnoreCase(tableType)) {
1390: continue;
1391: }
1392:
1393: // String remarks = tableSet.getString("REMARKS");
1394: tableNames.add(tableName);
1395: // if (Debug.infoOn()) Debug.logInfo("Found table named [" + tableName + "] of type [" + tableType + "] with remarks: " + remarks, module);
1396: } catch (SQLException e) {
1397: String message = "Error getting table information... Error was:"
1398: + e.toString();
1399: Debug.logError(message, module);
1400: if (messages != null)
1401: messages.add(message);
1402: continue;
1403: }
1404: }
1405: } catch (SQLException e) {
1406: String message = "Error getting next table information... Error was:"
1407: + e.toString();
1408: Debug.logError(message, module);
1409: if (messages != null)
1410: messages.add(message);
1411: } finally {
1412: try {
1413: tableSet.close();
1414: } catch (SQLException e) {
1415: String message = "Unable to close ResultSet for table list, continuing anyway... Error was:"
1416: + e.toString();
1417: Debug.logError(message, module);
1418: if (messages != null)
1419: messages.add(message);
1420: }
1421:
1422: try {
1423: connection.close();
1424: } catch (SQLException e) {
1425: String message = "Unable to close database connection, continuing anyway... Error was:"
1426: + e.toString();
1427: Debug.logError(message, module);
1428: if (messages != null)
1429: messages.add(message);
1430: }
1431: }
1432: return tableNames;
1433: }
1434:
1435: public Map getColumnInfo(Set tableNames, boolean getPks,
1436: Collection messages) {
1437: // if there are no tableNames, don't even try to get the columns
1438: if (tableNames.size() == 0) {
1439: return FastMap.newInstance();
1440: }
1441:
1442: Connection connection = null;
1443: try {
1444: try {
1445: connection = getConnection();
1446: } catch (SQLException e) {
1447: String message = "Unable to esablish a connection with the database... Error was:"
1448: + e.toString();
1449: Debug.logError(e, message, module);
1450: if (messages != null)
1451: messages.add(message);
1452: return null;
1453: } catch (GenericEntityException e) {
1454: String message = "Unable to esablish a connection with the database... Error was:"
1455: + e.toString();
1456: Debug.logError(e, message, module);
1457: if (messages != null)
1458: messages.add(message);
1459: return null;
1460: }
1461:
1462: DatabaseMetaData dbData = null;
1463: try {
1464: dbData = connection.getMetaData();
1465: } catch (SQLException e) {
1466: String message = "Unable to get database meta data... Error was:"
1467: + e.toString();
1468: Debug.logError(message, module);
1469: if (messages != null)
1470: messages.add(message);
1471:
1472: try {
1473: connection.close();
1474: } catch (SQLException e2) {
1475: String message2 = "Unable to close database connection, continuing anyway... Error was:"
1476: + e2.toString();
1477: Debug.logError(message2, module);
1478: if (messages != null)
1479: messages.add(message2);
1480: }
1481: return null;
1482: }
1483:
1484: if (Debug.infoOn())
1485: Debug.logInfo("Getting Column Info From Database",
1486: module);
1487:
1488: Map colInfo = FastMap.newInstance();
1489: String lookupSchemaName = null;
1490: try {
1491: if (dbData.supportsSchemasInTableDefinitions()) {
1492: if (this .datasourceInfo.schemaName != null
1493: && this .datasourceInfo.schemaName.length() > 0) {
1494: lookupSchemaName = this .datasourceInfo.schemaName;
1495: } else {
1496: lookupSchemaName = dbData.getUserName();
1497: }
1498: }
1499:
1500: boolean needsUpperCase = false;
1501: try {
1502: needsUpperCase = dbData
1503: .storesLowerCaseIdentifiers()
1504: || dbData.storesMixedCaseIdentifiers();
1505: } catch (SQLException e) {
1506: String message = "Error getting identifier case information... Error was:"
1507: + e.toString();
1508: Debug.logError(message, module);
1509: if (messages != null)
1510: messages.add(message);
1511: }
1512:
1513: boolean foundCols = false;
1514: ResultSet rsCols = dbData.getColumns(null,
1515: lookupSchemaName, null, null);
1516: if (!rsCols.next()) {
1517: try {
1518: rsCols.close();
1519: } catch (SQLException e) {
1520: String message = "Unable to close ResultSet for column list, continuing anyway... Error was:"
1521: + e.toString();
1522: Debug.logError(message, module);
1523: if (messages != null)
1524: messages.add(message);
1525: }
1526: rsCols = dbData.getColumns(null, lookupSchemaName,
1527: "%", "%");
1528: if (!rsCols.next()) {
1529: // TODO: now what to do? I guess try one table name at a time...
1530: } else {
1531: foundCols = true;
1532: }
1533: } else {
1534: foundCols = true;
1535: }
1536: if (foundCols) {
1537: do {
1538: try {
1539: ColumnCheckInfo ccInfo = new ColumnCheckInfo();
1540:
1541: ccInfo.tableName = ColumnCheckInfo
1542: .fixupTableName(rsCols
1543: .getString("TABLE_NAME"),
1544: lookupSchemaName,
1545: needsUpperCase);
1546: // ignore the column info if the table name is not in the list we are concerned with
1547: if (!tableNames.contains(ccInfo.tableName)) {
1548: continue;
1549: }
1550:
1551: ccInfo.columnName = rsCols
1552: .getString("COLUMN_NAME");
1553: if (needsUpperCase
1554: && ccInfo.columnName != null) {
1555: ccInfo.columnName = ccInfo.columnName
1556: .toUpperCase();
1557: }
1558: // NOTE: this may need a toUpperCase in some cases, keep an eye on it
1559: ccInfo.typeName = rsCols
1560: .getString("TYPE_NAME");
1561: ccInfo.columnSize = rsCols
1562: .getInt("COLUMN_SIZE");
1563: ccInfo.decimalDigits = rsCols
1564: .getInt("DECIMAL_DIGITS");
1565: // NOTE: this may need a toUpperCase in some cases, keep an eye on it
1566: ccInfo.isNullable = rsCols
1567: .getString("IS_NULLABLE");
1568:
1569: Map tableColInfo = (Map) colInfo
1570: .get(ccInfo.tableName);
1571: if (tableColInfo == null) {
1572: tableColInfo = FastMap.newInstance();
1573: colInfo.put(ccInfo.tableName,
1574: tableColInfo);
1575: }
1576: tableColInfo.put(ccInfo.columnName, ccInfo);
1577: } catch (SQLException e) {
1578: String message = "Error getting column info for column. Error was:"
1579: + e.toString();
1580: Debug.logError(message, module);
1581: if (messages != null)
1582: messages.add(message);
1583: continue;
1584: }
1585: } while (rsCols.next());
1586: }
1587:
1588: try {
1589: rsCols.close();
1590: } catch (SQLException e) {
1591: String message = "Unable to close ResultSet for column list, continuing anyway... Error was:"
1592: + e.toString();
1593: Debug.logError(message, module);
1594: if (messages != null)
1595: messages.add(message);
1596: }
1597:
1598: if (getPks) {
1599: boolean foundPks = false;
1600: ResultSet rsPks = dbData.getPrimaryKeys(null,
1601: lookupSchemaName, null);
1602: if (!rsPks.next()) {
1603: try {
1604: rsPks.close();
1605: } catch (SQLException e) {
1606: String message = "Unable to close ResultSet for primary key list, continuing anyway... Error was:"
1607: + e.toString();
1608: Debug.logError(message, module);
1609: if (messages != null)
1610: messages.add(message);
1611: }
1612: rsPks = dbData.getPrimaryKeys(null,
1613: lookupSchemaName, "%");
1614: if (!rsPks.next()) {
1615: // TODO: now what to do? I guess try one table name at a time...
1616: } else {
1617: foundPks = true;
1618: }
1619: } else {
1620: foundPks = true;
1621: }
1622: if (foundPks) {
1623: do {
1624: try {
1625: String tableName = ColumnCheckInfo
1626: .fixupTableName(
1627: rsPks
1628: .getString("TABLE_NAME"),
1629: lookupSchemaName,
1630: needsUpperCase);
1631: String columnName = rsPks
1632: .getString("COLUMN_NAME");
1633: if (needsUpperCase
1634: && columnName != null) {
1635: columnName = columnName
1636: .toUpperCase();
1637: }
1638: Map tableColInfo = (Map) colInfo
1639: .get(tableName);
1640: if (tableColInfo == null) {
1641: // not looking for info on this table
1642: continue;
1643: }
1644: ColumnCheckInfo ccInfo = (ColumnCheckInfo) tableColInfo
1645: .get(columnName);
1646: if (ccInfo == null) {
1647: // this isn't good, what to do?
1648: Debug
1649: .logWarning(
1650: "Got primary key information for a column that we didn't get column information for: tableName=["
1651: + tableName
1652: + "], columnName=["
1653: + columnName
1654: + "]",
1655: module);
1656: continue;
1657: }
1658:
1659: /*
1660: KEY_SEQ short => sequence number within primary key
1661: PK_NAME String => primary key name (may be null)
1662: */
1663: ccInfo.isPk = true;
1664: ccInfo.pkSeq = rsPks
1665: .getShort("KEY_SEQ");
1666: ccInfo.pkName = rsPks
1667: .getString("PK_NAME");
1668: } catch (SQLException e) {
1669: String message = "Error getting primary key info for column. Error was:"
1670: + e.toString();
1671: Debug.logError(message, module);
1672: if (messages != null)
1673: messages.add(message);
1674: continue;
1675: }
1676: } while (rsPks.next());
1677: }
1678:
1679: try {
1680: rsPks.close();
1681: } catch (SQLException e) {
1682: String message = "Unable to close ResultSet for primary key list, continuing anyway... Error was:"
1683: + e.toString();
1684: Debug.logError(message, module);
1685: if (messages != null)
1686: messages.add(message);
1687: }
1688: }
1689: } catch (SQLException e) {
1690: String message = "Error getting column meta data for Error was:"
1691: + e.toString() + ". Not checking columns.";
1692: Debug.logError(message, module);
1693: if (messages != null)
1694: messages.add(message);
1695: // we are returning an empty set in this case because databases like SapDB throw an exception when there are no tables in the database
1696: // colInfo = null;
1697: }
1698: return colInfo;
1699: } finally {
1700: if (connection != null) {
1701: try {
1702: connection.close();
1703: } catch (SQLException e) {
1704: String message = "Unable to close database connection, continuing anyway... Error was:"
1705: + e.toString();
1706: Debug.logError(message, module);
1707: if (messages != null)
1708: messages.add(message);
1709: }
1710: }
1711: }
1712: }
1713:
1714: public Map getReferenceInfo(Set tableNames, Collection messages) {
1715: Connection connection = null;
1716: try {
1717: connection = getConnection();
1718: } catch (SQLException e) {
1719: String message = "Unable to esablish a connection with the database... Error was:"
1720: + e.toString();
1721: Debug.logError(message, module);
1722: if (messages != null)
1723: messages.add(message);
1724: return null;
1725: } catch (GenericEntityException e) {
1726: String message = "Unable to esablish a connection with the database... Error was:"
1727: + e.toString();
1728: Debug.logError(message, module);
1729: if (messages != null)
1730: messages.add(message);
1731: return null;
1732: }
1733:
1734: DatabaseMetaData dbData = null;
1735: try {
1736: dbData = connection.getMetaData();
1737: } catch (SQLException e) {
1738: String message = "Unable to get database meta data... Error was:"
1739: + e.toString();
1740: Debug.logError(message, module);
1741: if (messages != null)
1742: messages.add(message);
1743:
1744: try {
1745: connection.close();
1746: } catch (SQLException e2) {
1747: String message2 = "Unable to close database connection, continuing anyway... Error was:"
1748: + e2.toString();
1749: Debug.logError(message2, module);
1750: if (messages != null)
1751: messages.add(message2);
1752: }
1753: return null;
1754: }
1755:
1756: /*
1757: try {
1758: if (Debug.infoOn()) Debug.logInfo("Database Product Name is " + dbData.getDatabaseProductName(), module);
1759: if (Debug.infoOn()) Debug.logInfo("Database Product Version is " + dbData.getDatabaseProductVersion(), module);
1760: } catch (SQLException e) {
1761: Debug.logWarning("Unable to get Database name & version information", module);
1762: }
1763: try {
1764: if (Debug.infoOn()) Debug.logInfo("Database Driver Name is " + dbData.getDriverName(), module);
1765: if (Debug.infoOn()) Debug.logInfo("Database Driver Version is " + dbData.getDriverVersion(), module);
1766: } catch (SQLException e) {
1767: Debug.logWarning("Unable to get Driver name & version information", module);
1768: }
1769: */
1770:
1771: if (Debug.infoOn())
1772: Debug
1773: .logInfo(
1774: "Getting Foreign Key (Reference) Info From Database",
1775: module);
1776:
1777: Map refInfo = FastMap.newInstance();
1778:
1779: try {
1780: // ResultSet rsCols = dbData.getCrossReference(null, null, null, null, null, null);
1781: String lookupSchemaName = null;
1782: if (dbData.supportsSchemasInTableDefinitions()) {
1783: if (this .datasourceInfo.schemaName != null
1784: && this .datasourceInfo.schemaName.length() > 0) {
1785: lookupSchemaName = this .datasourceInfo.schemaName;
1786: } else {
1787: lookupSchemaName = dbData.getUserName();
1788: }
1789: }
1790:
1791: boolean needsUpperCase = false;
1792: try {
1793: needsUpperCase = dbData.storesLowerCaseIdentifiers()
1794: || dbData.storesMixedCaseIdentifiers();
1795: } catch (SQLException e) {
1796: String message = "Error getting identifier case information... Error was:"
1797: + e.toString();
1798: Debug.logError(message, module);
1799: if (messages != null)
1800: messages.add(message);
1801: }
1802:
1803: ResultSet rsCols = dbData.getImportedKeys(null,
1804: lookupSchemaName, null);
1805: int totalFkRefs = 0;
1806:
1807: // Iterator tableNamesIter = tableNames.iterator();
1808: // while (tableNamesIter.hasNext()) {
1809: // String tableName = (String) tableNamesIter.next();
1810: // ResultSet rsCols = dbData.getImportedKeys(null, null, tableName);
1811: // Debug.logVerbose("Getting imported keys for table " + tableName, module);
1812:
1813: while (rsCols.next()) {
1814: try {
1815: ReferenceCheckInfo rcInfo = new ReferenceCheckInfo();
1816:
1817: rcInfo.pkTableName = rsCols
1818: .getString("PKTABLE_NAME");
1819: if (needsUpperCase && rcInfo.pkTableName != null) {
1820: rcInfo.pkTableName = rcInfo.pkTableName
1821: .toUpperCase();
1822: }
1823: rcInfo.pkColumnName = rsCols
1824: .getString("PKCOLUMN_NAME");
1825: if (needsUpperCase && rcInfo.pkColumnName != null) {
1826: rcInfo.pkColumnName = rcInfo.pkColumnName
1827: .toUpperCase();
1828: }
1829:
1830: rcInfo.fkTableName = rsCols
1831: .getString("FKTABLE_NAME");
1832: if (needsUpperCase && rcInfo.fkTableName != null) {
1833: rcInfo.fkTableName = rcInfo.fkTableName
1834: .toUpperCase();
1835: }
1836: // ignore the column info if the FK table name is not in the list we are concerned with
1837: if (!tableNames.contains(rcInfo.fkTableName)) {
1838: continue;
1839: }
1840: rcInfo.fkColumnName = rsCols
1841: .getString("FKCOLUMN_NAME");
1842: if (needsUpperCase && rcInfo.fkColumnName != null) {
1843: rcInfo.fkColumnName = rcInfo.fkColumnName
1844: .toUpperCase();
1845: }
1846: rcInfo.fkName = rsCols.getString("FK_NAME");
1847: if (needsUpperCase && rcInfo.fkName != null) {
1848: rcInfo.fkName = rcInfo.fkName.toUpperCase();
1849: }
1850:
1851: if (Debug.verboseOn())
1852: Debug.logVerbose("Got: " + rcInfo.toString(),
1853: module);
1854:
1855: Map tableRefInfo = (Map) refInfo
1856: .get(rcInfo.fkTableName);
1857: if (tableRefInfo == null) {
1858: tableRefInfo = FastMap.newInstance();
1859: refInfo.put(rcInfo.fkTableName, tableRefInfo);
1860: if (Debug.verboseOn())
1861: Debug.logVerbose(
1862: "Adding new Map for table: "
1863: + rcInfo.fkTableName,
1864: module);
1865: }
1866: if (!tableRefInfo.containsKey(rcInfo.fkName))
1867: totalFkRefs++;
1868: tableRefInfo.put(rcInfo.fkName, rcInfo);
1869: } catch (SQLException e) {
1870: String message = "Error getting fk reference info for table. Error was:"
1871: + e.toString();
1872: Debug.logError(message, module);
1873: if (messages != null)
1874: messages.add(message);
1875: continue;
1876: }
1877: }
1878:
1879: // if (Debug.infoOn()) Debug.logInfo("There are " + totalFkRefs + " in the database", module);
1880: try {
1881: rsCols.close();
1882: } catch (SQLException e) {
1883: String message = "Unable to close ResultSet for fk reference list, continuing anyway... Error was:"
1884: + e.toString();
1885: Debug.logError(message, module);
1886: if (messages != null)
1887: messages.add(message);
1888: }
1889: // }
1890: if (Debug.infoOn())
1891: Debug.logInfo("There are " + totalFkRefs
1892: + " foreign key refs in the database", module);
1893:
1894: } catch (SQLException e) {
1895: String message = "Error getting fk reference meta data Error was:"
1896: + e.toString() + ". Not checking fk refs.";
1897: Debug.logError(message, module);
1898: if (messages != null)
1899: messages.add(message);
1900: refInfo = null;
1901: } finally {
1902: try {
1903: connection.close();
1904: } catch (SQLException e) {
1905: String message = "Unable to close database connection, continuing anyway... Error was:"
1906: + e.toString();
1907: Debug.logError(message, module);
1908: if (messages != null)
1909: messages.add(message);
1910: }
1911: }
1912: return refInfo;
1913: }
1914:
1915: public Map getIndexInfo(Set tableNames, Collection messages) {
1916: Connection connection = null;
1917:
1918: try {
1919: connection = getConnection();
1920: } catch (SQLException e) {
1921: String message = "Unable to esablish a connection with the database... Error was:"
1922: + e.toString();
1923: Debug.logError(message, module);
1924: if (messages != null)
1925: messages.add(message);
1926: return null;
1927: } catch (GenericEntityException e) {
1928: String message = "Unable to esablish a connection with the database... Error was:"
1929: + e.toString();
1930: Debug.logError(message, module);
1931: if (messages != null)
1932: messages.add(message);
1933: return null;
1934: }
1935:
1936: DatabaseMetaData dbData = null;
1937: try {
1938: dbData = connection.getMetaData();
1939: } catch (SQLException e) {
1940: String message = "Unable to get database meta data... Error was:"
1941: + e.toString();
1942: Debug.logError(message, module);
1943: if (messages != null)
1944: messages.add(message);
1945:
1946: try {
1947: connection.close();
1948: } catch (SQLException e2) {
1949: String message2 = "Unable to close database connection, continuing anyway... Error was:"
1950: + e2.toString();
1951: Debug.logError(message2, module);
1952: if (messages != null)
1953: messages.add(message2);
1954: }
1955: return null;
1956: }
1957:
1958: boolean needsUpperCase = false;
1959: try {
1960: needsUpperCase = dbData.storesLowerCaseIdentifiers()
1961: || dbData.storesMixedCaseIdentifiers();
1962: } catch (SQLException e) {
1963: String message = "Error getting identifier case information... Error was:"
1964: + e.toString();
1965: Debug.logError(message, module);
1966: if (messages != null)
1967: messages.add(message);
1968: }
1969:
1970: if (Debug.infoOn())
1971: Debug.logInfo("Getting Index Info From Database", module);
1972:
1973: Map indexInfo = FastMap.newInstance();
1974: try {
1975: int totalIndices = 0;
1976: Iterator tableNamesIter = tableNames.iterator();
1977: while (tableNamesIter.hasNext()) {
1978: String curTableName = (String) tableNamesIter.next();
1979:
1980: String lookupSchemaName = null;
1981: if (dbData.supportsSchemasInTableDefinitions()) {
1982: if (this .datasourceInfo.schemaName != null
1983: && this .datasourceInfo.schemaName.length() > 0) {
1984: lookupSchemaName = this .datasourceInfo.schemaName;
1985: } else {
1986: lookupSchemaName = dbData.getUserName();
1987: }
1988: }
1989:
1990: ResultSet rsCols = null;
1991: try {
1992: // false for unique, we don't really use unique indexes
1993: // true for approximate, don't really care if stats are up-to-date
1994: rsCols = dbData
1995: .getIndexInfo(null, lookupSchemaName,
1996: curTableName, false, true);
1997: } catch (Exception e) {
1998: Debug.logWarning(e,
1999: "Error getting index info for table: "
2000: + curTableName
2001: + " using lookupSchemaName "
2002: + lookupSchemaName, module);
2003: }
2004:
2005: while (rsCols != null && rsCols.next()) {
2006: // NOTE: The code in this block may look funny, but it is designed so that the wrapping loop can be removed
2007: try {
2008: // skip all index info for statistics
2009: if (rsCols.getShort("TYPE") == DatabaseMetaData.tableIndexStatistic)
2010: continue;
2011:
2012: // HACK: for now skip all "unique" indexes since our foreign key indices are not unique, but the primary key ones are
2013: if (!rsCols.getBoolean("NON_UNIQUE"))
2014: continue;
2015:
2016: String tableName = rsCols
2017: .getString("TABLE_NAME");
2018: if (needsUpperCase && tableName != null) {
2019: tableName = tableName.toUpperCase();
2020: }
2021: if (!tableNames.contains(tableName))
2022: continue;
2023:
2024: String indexName = rsCols
2025: .getString("INDEX_NAME");
2026: if (needsUpperCase && indexName != null) {
2027: indexName = indexName.toUpperCase();
2028: }
2029:
2030: TreeSet tableIndexList = (TreeSet) indexInfo
2031: .get(tableName);
2032: if (tableIndexList == null) {
2033: tableIndexList = new TreeSet();
2034: indexInfo.put(tableName, tableIndexList);
2035: if (Debug.verboseOn())
2036: Debug.logVerbose(
2037: "Adding new Map for table: "
2038: + tableName, module);
2039: }
2040: if (!tableIndexList.contains(indexName))
2041: totalIndices++;
2042: tableIndexList.add(indexName);
2043: } catch (SQLException e) {
2044: String message = "Error getting fk reference info for table. Error was:"
2045: + e.toString();
2046: Debug.logError(message, module);
2047: if (messages != null)
2048: messages.add(message);
2049: continue;
2050: }
2051: }
2052:
2053: // if (Debug.infoOn()) Debug.logInfo("There are " + totalIndices + " indices in the database", module);
2054: if (rsCols != null) {
2055: try {
2056: rsCols.close();
2057: } catch (SQLException e) {
2058: String message = "Unable to close ResultSet for fk reference list, continuing anyway... Error was:"
2059: + e.toString();
2060: Debug.logError(message, module);
2061: if (messages != null)
2062: messages.add(message);
2063: }
2064: }
2065: }
2066: if (Debug.infoOn())
2067: Debug.logInfo("There are " + totalIndices
2068: + " indices in the database", module);
2069:
2070: } catch (SQLException e) {
2071: String message = "Error getting fk reference meta data Error was:"
2072: + e.toString() + ". Not checking fk refs.";
2073: Debug.logError(message, module);
2074: if (messages != null)
2075: messages.add(message);
2076: indexInfo = null;
2077: } finally {
2078: try {
2079: connection.close();
2080: } catch (SQLException e) {
2081: String message = "Unable to close database connection, continuing anyway... Error was:"
2082: + e.toString();
2083: Debug.logError(message, module);
2084: if (messages != null)
2085: messages.add(message);
2086: }
2087: }
2088: return indexInfo;
2089: }
2090:
2091: /* ====================================================================== */
2092:
2093: /* ====================================================================== */
2094:
2095: public String createTable(ModelEntity entity, Map modelEntities,
2096: boolean addFks) {
2097: if (entity == null) {
2098: return "ModelEntity was null and is required to create a table";
2099: }
2100: if (entity instanceof ModelViewEntity) {
2101: return "ERROR: Cannot create table for a view entity";
2102: }
2103:
2104: Connection connection = null;
2105: Statement stmt = null;
2106:
2107: try {
2108: connection = getConnection();
2109: } catch (SQLException e) {
2110: String errMsg = "Unable to esablish a connection with the database for helperName ["
2111: + this .helperName
2112: + "]... Error was: "
2113: + e.toString();
2114: Debug.logError(e, errMsg, module);
2115: return errMsg;
2116: } catch (GenericEntityException e) {
2117: String errMsg = "Unable to esablish a connection with the database for helperName ["
2118: + this .helperName
2119: + "]... Error was: "
2120: + e.toString();
2121: Debug.logError(e, errMsg, module);
2122: return errMsg;
2123: }
2124:
2125: StringBuffer sqlBuf = new StringBuffer("CREATE TABLE ");
2126: sqlBuf.append(entity.getTableName(this .datasourceInfo));
2127: sqlBuf.append(" (");
2128: Iterator fieldIter = entity.getFieldsIterator();
2129: while (fieldIter.hasNext()) {
2130: ModelField field = (ModelField) fieldIter.next();
2131: ModelFieldType type = modelFieldTypeReader
2132: .getModelFieldType(field.getType());
2133: if (type == null) {
2134: return "Field type [" + type
2135: + "] not found for field [" + field.getName()
2136: + "] of entity [" + entity.getEntityName()
2137: + "], not creating table.";
2138: }
2139:
2140: sqlBuf.append(field.getColName());
2141: sqlBuf.append(" ");
2142: sqlBuf.append(type.getSqlType());
2143:
2144: if ("String".equals(type.getJavaType())
2145: || "java.lang.String".equals(type.getJavaType())) {
2146: // if there is a characterSet, add the CHARACTER SET arg here
2147: if (UtilValidate
2148: .isNotEmpty(this .datasourceInfo.characterSet)) {
2149: sqlBuf.append(" CHARACTER SET ");
2150: sqlBuf.append(this .datasourceInfo.characterSet);
2151: }
2152: // if there is a collate, add the COLLATE arg here
2153: if (UtilValidate
2154: .isNotEmpty(this .datasourceInfo.collate)) {
2155: sqlBuf.append(" COLLATE ");
2156: sqlBuf.append(this .datasourceInfo.collate);
2157: }
2158: }
2159:
2160: if (field.getIsPk()) {
2161: if (this .datasourceInfo.alwaysUseConstraintKeyword) {
2162: sqlBuf.append(" CONSTRAINT NOT NULL, ");
2163: } else {
2164: sqlBuf.append(" NOT NULL, ");
2165: }
2166: } else {
2167: sqlBuf.append(", ");
2168: }
2169: }
2170:
2171: String pkName = makePkConstraintName(entity,
2172: this .datasourceInfo.constraintNameClipLength);
2173: if (this .datasourceInfo.usePkConstraintNames) {
2174: sqlBuf.append("CONSTRAINT ");
2175: sqlBuf.append(pkName);
2176: }
2177: sqlBuf.append(" PRIMARY KEY (");
2178: sqlBuf.append(entity.colNameString(entity.getPksCopy()));
2179: sqlBuf.append(")");
2180:
2181: if (addFks) {
2182: // NOTE: This is kind of a bad idea anyway since ordering table creations is crazy, if not impossible
2183:
2184: // go through the relationships to see if any foreign keys need to be added
2185: Iterator relationsIter = entity.getRelationsIterator();
2186: while (relationsIter.hasNext()) {
2187: ModelRelation modelRelation = (ModelRelation) relationsIter
2188: .next();
2189: if ("one".equals(modelRelation.getType())) {
2190: ModelEntity relModelEntity = (ModelEntity) modelEntities
2191: .get(modelRelation.getRelEntityName());
2192: if (relModelEntity == null) {
2193: Debug.logError(
2194: "Error adding foreign key: ModelEntity was null for related entity name "
2195: + modelRelation
2196: .getRelEntityName(),
2197: module);
2198: continue;
2199: }
2200: if (relModelEntity instanceof ModelViewEntity) {
2201: Debug
2202: .logError(
2203: "Error adding foreign key: related entity is a view entity for related entity name "
2204: + modelRelation
2205: .getRelEntityName(),
2206: module);
2207: continue;
2208: }
2209:
2210: String fkConstraintClause = makeFkConstraintClause(
2211: entity,
2212: modelRelation,
2213: relModelEntity,
2214: this .datasourceInfo.constraintNameClipLength,
2215: this .datasourceInfo.fkStyle,
2216: this .datasourceInfo.useFkInitiallyDeferred);
2217: if (UtilValidate.isNotEmpty(fkConstraintClause)) {
2218: sqlBuf.append(", ");
2219: sqlBuf.append(fkConstraintClause);
2220: } else {
2221: continue;
2222: }
2223: }
2224: }
2225: }
2226:
2227: sqlBuf.append(")");
2228:
2229: // if there is a tableType, add the TYPE arg here
2230: if (UtilValidate.isNotEmpty(this .datasourceInfo.tableType)) {
2231: sqlBuf.append(" TYPE ");
2232: sqlBuf.append(this .datasourceInfo.tableType);
2233: }
2234:
2235: // if there is a characterSet, add the CHARACTER SET arg here
2236: if (UtilValidate.isNotEmpty(this .datasourceInfo.characterSet)) {
2237: sqlBuf.append(" CHARACTER SET ");
2238: sqlBuf.append(this .datasourceInfo.characterSet);
2239: }
2240:
2241: // if there is a collate, add the COLLATE arg here
2242: if (UtilValidate.isNotEmpty(this .datasourceInfo.collate)) {
2243: sqlBuf.append(" COLLATE ");
2244: sqlBuf.append(this .datasourceInfo.collate);
2245: }
2246:
2247: if (Debug.verboseOn())
2248: Debug.logVerbose("[createTable] sql=" + sqlBuf.toString(),
2249: module);
2250: try {
2251: stmt = connection.createStatement();
2252: stmt.executeUpdate(sqlBuf.toString());
2253: } catch (SQLException e) {
2254: return "SQL Exception while executing the following:\n"
2255: + sqlBuf.toString() + "\nError was: "
2256: + e.toString();
2257: } finally {
2258: try {
2259: if (stmt != null)
2260: stmt.close();
2261: } catch (SQLException e) {
2262: Debug.logError(e, module);
2263: }
2264: try {
2265: if (connection != null) {
2266: connection.close();
2267: }
2268: } catch (SQLException e) {
2269: Debug.logError(e, module);
2270: }
2271: }
2272: return null;
2273: }
2274:
2275: public void deleteTable(ModelEntity entity, List messages) {
2276: if (entity == null) {
2277: String errMsg = "ModelEntity was null and is required to delete a table";
2278: Debug.logError(errMsg, module);
2279: if (messages != null)
2280: messages.add(errMsg);
2281: return;
2282: }
2283: if (entity instanceof ModelViewEntity) {
2284: //String errMsg = "ERROR: Cannot delete table for a view entity";
2285: //Debug.logError(errMsg, module);
2286: //if (messages != null) messages.add(errMsg);
2287: return;
2288: }
2289:
2290: Connection connection = null;
2291: Statement stmt = null;
2292: try {
2293: connection = getConnection();
2294: } catch (SQLException e) {
2295: String errMsg = "Unable to esablish a connection with the database for helperName ["
2296: + this .helperName
2297: + "]... Error was: "
2298: + e.toString();
2299: Debug.logError(errMsg, module);
2300: if (messages != null)
2301: messages.add(errMsg);
2302: return;
2303: } catch (GenericEntityException e) {
2304: String errMsg = "Unable to esablish a connection with the database for helperName ["
2305: + this .helperName
2306: + "]... Error was: "
2307: + e.toString();
2308: Debug.logError(errMsg, module);
2309: if (messages != null)
2310: messages.add(errMsg);
2311: return;
2312: }
2313:
2314: String message = "Deleting table for entity ["
2315: + entity.getEntityName() + "]";
2316: Debug.logImportant(message, module);
2317: if (messages != null)
2318: messages.add(message);
2319:
2320: StringBuffer sqlBuf = new StringBuffer("DROP TABLE ");
2321: sqlBuf.append(entity.getTableName(datasourceInfo));
2322: if (Debug.verboseOn())
2323: Debug.logVerbose("[deleteTable] sql=" + sqlBuf.toString(),
2324: module);
2325: try {
2326: stmt = connection.createStatement();
2327: stmt.executeUpdate(sqlBuf.toString());
2328: } catch (SQLException e) {
2329: String errMsg = "SQL Exception while executing the following:\n"
2330: + sqlBuf.toString()
2331: + "\nError was: "
2332: + e.toString();
2333: Debug.logError(errMsg, module);
2334: if (messages != null)
2335: messages.add(errMsg);
2336: } finally {
2337: try {
2338: if (stmt != null)
2339: stmt.close();
2340: } catch (SQLException e) {
2341: Debug.logError(e, module);
2342: }
2343: try {
2344: if (connection != null) {
2345: connection.close();
2346: }
2347: } catch (SQLException e) {
2348: Debug.logError(e, module);
2349: }
2350: }
2351: }
2352:
2353: public String addColumn(ModelEntity entity, ModelField field) {
2354: if (entity == null || field == null)
2355: return "ModelEntity or ModelField where null, cannot add column";
2356: if (entity instanceof ModelViewEntity) {
2357: return "ERROR: Cannot add column for a view entity";
2358: }
2359:
2360: Connection connection = null;
2361: Statement stmt = null;
2362:
2363: try {
2364: connection = getConnection();
2365: } catch (SQLException e) {
2366: String errMsg = "Unable to esablish a connection with the database for helperName ["
2367: + this .helperName
2368: + "]... Error was: "
2369: + e.toString();
2370: Debug.logError(e, errMsg, module);
2371: return errMsg;
2372: } catch (GenericEntityException e) {
2373: String errMsg = "Unable to esablish a connection with the database for helperName ["
2374: + this .helperName
2375: + "]... Error was: "
2376: + e.toString();
2377: Debug.logError(e, errMsg, module);
2378: return errMsg;
2379: }
2380:
2381: ModelFieldType type = modelFieldTypeReader
2382: .getModelFieldType(field.getType());
2383:
2384: if (type == null) {
2385: return "Field type [" + type + "] not found for field ["
2386: + field.getName() + "] of entity ["
2387: + entity.getEntityName() + "], not adding column.";
2388: }
2389:
2390: StringBuffer sqlBuf = new StringBuffer("ALTER TABLE ");
2391: sqlBuf.append(entity.getTableName(datasourceInfo));
2392: sqlBuf.append(" ADD ");
2393: sqlBuf.append(field.getColName());
2394: sqlBuf.append(" ");
2395: sqlBuf.append(type.getSqlType());
2396:
2397: if ("String".equals(type.getJavaType())
2398: || "java.lang.String".equals(type.getJavaType())) {
2399: // if there is a characterSet, add the CHARACTER SET arg here
2400: if (UtilValidate
2401: .isNotEmpty(this .datasourceInfo.characterSet)) {
2402: sqlBuf.append(" CHARACTER SET ");
2403: sqlBuf.append(this .datasourceInfo.characterSet);
2404: }
2405:
2406: // if there is a collate, add the COLLATE arg here
2407: if (UtilValidate.isNotEmpty(this .datasourceInfo.collate)) {
2408: sqlBuf.append(" COLLATE ");
2409: sqlBuf.append(this .datasourceInfo.collate);
2410: }
2411: }
2412:
2413: String sql = sqlBuf.toString();
2414: if (Debug.infoOn())
2415: Debug.logInfo("[addColumn] sql=" + sql, module);
2416: try {
2417: stmt = connection.createStatement();
2418: stmt.executeUpdate(sql);
2419: } catch (SQLException e) {
2420: // if that failed try the alternate syntax real quick
2421: StringBuffer sql2Buf = new StringBuffer("ALTER TABLE ");
2422: sql2Buf.append(entity.getTableName(datasourceInfo));
2423: sql2Buf.append(" ADD COLUMN ");
2424: sql2Buf.append(field.getColName());
2425: sql2Buf.append(" ");
2426: sql2Buf.append(type.getSqlType());
2427:
2428: if ("String".equals(type.getJavaType())
2429: || "java.lang.String".equals(type.getJavaType())) {
2430: // if there is a characterSet, add the CHARACTER SET arg here
2431: if (UtilValidate
2432: .isNotEmpty(this .datasourceInfo.characterSet)) {
2433: sql2Buf.append(" CHARACTER SET ");
2434: sql2Buf.append(this .datasourceInfo.characterSet);
2435: }
2436:
2437: // if there is a collate, add the COLLATE arg here
2438: if (UtilValidate
2439: .isNotEmpty(this .datasourceInfo.collate)) {
2440: sql2Buf.append(" COLLATE ");
2441: sql2Buf.append(this .datasourceInfo.collate);
2442: }
2443: }
2444:
2445: String sql2 = sql2Buf.toString();
2446: if (Debug.infoOn())
2447: Debug.logInfo("[addColumn] sql failed, trying sql2="
2448: + sql2, module);
2449: try {
2450: stmt = connection.createStatement();
2451: stmt.executeUpdate(sql2);
2452: } catch (SQLException e2) {
2453: // if this also fails report original error, not this error...
2454: return "SQL Exception while executing the following:\n"
2455: + sql + "\nError was: " + e.toString();
2456: }
2457: } finally {
2458: try {
2459: if (stmt != null) {
2460: stmt.close();
2461: }
2462: } catch (SQLException e) {
2463: Debug.logError(e, module);
2464: }
2465: try {
2466: if (connection != null) {
2467: connection.close();
2468: }
2469: } catch (SQLException e) {
2470: Debug.logError(e, module);
2471: }
2472: }
2473: return null;
2474: }
2475:
2476: public String renameColumn(ModelEntity entity, ModelField field,
2477: String newName) {
2478: if (entity == null || field == null)
2479: return "ModelEntity or ModelField where null, cannot rename column";
2480: if (entity instanceof ModelViewEntity) {
2481: return "ERROR: Cannot rename column for a view entity";
2482: }
2483:
2484: Connection connection = null;
2485: Statement stmt = null;
2486:
2487: try {
2488: connection = getConnection();
2489: } catch (SQLException e) {
2490: String errMsg = "Unable to esablish a connection with the database for helperName ["
2491: + this .helperName
2492: + "]... Error was: "
2493: + e.toString();
2494: Debug.logError(e, errMsg, module);
2495: return errMsg;
2496: } catch (GenericEntityException e) {
2497: String errMsg = "Unable to esablish a connection with the database for helperName ["
2498: + this .helperName
2499: + "]... Error was: "
2500: + e.toString();
2501: Debug.logError(e, errMsg, module);
2502: return errMsg;
2503: }
2504:
2505: ModelFieldType type = modelFieldTypeReader
2506: .getModelFieldType(field.getType());
2507:
2508: if (type == null) {
2509: return "Field type [" + type + "] not found for field ["
2510: + field.getName() + "] of entity ["
2511: + entity.getEntityName()
2512: + "], not renaming column.";
2513: }
2514:
2515: StringBuffer sqlBuf = new StringBuffer("ALTER TABLE ");
2516: sqlBuf.append(entity.getTableName(datasourceInfo));
2517: sqlBuf.append(" RENAME ");
2518: sqlBuf.append(field.getColName());
2519: sqlBuf.append(" TO ");
2520: sqlBuf.append(newName);
2521:
2522: String sql = sqlBuf.toString();
2523: if (Debug.infoOn())
2524: Debug.logInfo("[renameColumn] sql=" + sql, module);
2525: try {
2526: stmt = connection.createStatement();
2527: stmt.executeUpdate(sql);
2528: } catch (SQLException e) {
2529: return "SQL Exception while executing the following:\n"
2530: + sql + "\nError was: " + e.toString();
2531: } finally {
2532: try {
2533: if (stmt != null) {
2534: stmt.close();
2535: }
2536: } catch (SQLException e) {
2537: Debug.logError(e, module);
2538: }
2539: try {
2540: if (connection != null) {
2541: connection.close();
2542: }
2543: } catch (SQLException e) {
2544: Debug.logError(e, module);
2545: }
2546: }
2547: return null;
2548: }
2549:
2550: public void repairColumnSize(ModelEntity entity, ModelField field,
2551: List messages) {
2552: // first rename the column
2553: String tempName = makeTempFieldName(field);
2554: String renamedErr = renameColumn(entity, field, tempName);
2555: if (!UtilValidate.isEmpty(renamedErr)) {
2556: if (messages != null)
2557: messages.add(renamedErr);
2558: Debug.logError(renamedErr, module);
2559: return;
2560: }
2561:
2562: // next add back in the column
2563: String addedErr = addColumn(entity, field);
2564: if (!UtilValidate.isEmpty(addedErr)) {
2565: if (messages != null)
2566: messages.add(addedErr);
2567: Debug.logError(addedErr, module);
2568: return;
2569: }
2570:
2571: // need connection
2572: Connection connection = null;
2573: Statement stmt = null;
2574:
2575: try {
2576: connection = getConnection();
2577: } catch (SQLException e) {
2578: String errMsg = "Unable to esablish a connection with the database for helperName ["
2579: + this .helperName
2580: + "]... Error was: "
2581: + e.toString();
2582: Debug.logError(e, errMsg, module);
2583: if (messages != null) {
2584: messages.add(errMsg);
2585: }
2586: return;
2587: } catch (GenericEntityException e) {
2588: String errMsg = "Unable to esablish a connection with the database for helperName ["
2589: + this .helperName
2590: + "]... Error was: "
2591: + e.toString();
2592: Debug.logError(e, errMsg, module);
2593: if (messages != null) {
2594: messages.add(errMsg);
2595: }
2596: return;
2597: }
2598:
2599: // copy the data from old to new
2600: StringBuffer sqlBuf1 = new StringBuffer("UPDATE ");
2601: sqlBuf1.append(entity.getTableName(datasourceInfo));
2602: sqlBuf1.append(" SET ");
2603: sqlBuf1.append(field.getColName());
2604: sqlBuf1.append(" = ");
2605: sqlBuf1.append(tempName);
2606:
2607: String sql1 = sqlBuf1.toString();
2608: if (Debug.infoOn())
2609: Debug.logInfo("[moveData] sql=" + sql1, module);
2610: try {
2611: stmt = connection.createStatement();
2612: int changed = stmt.executeUpdate(sql1);
2613: if (Debug.infoOn())
2614: Debug.logInfo("[moveData] " + changed
2615: + " records updated", module);
2616: } catch (SQLException e) {
2617: String this Msg = "SQL Exception while executing the following:\n"
2618: + sql1 + "\nError was: " + e.toString();
2619: if (messages != null)
2620: messages.add(this Msg);
2621: Debug.logError(this Msg, module);
2622: return;
2623: } finally {
2624: try {
2625: if (stmt != null) {
2626: stmt.close();
2627: }
2628: } catch (SQLException e) {
2629: Debug.logError(e, module);
2630: }
2631: try {
2632: if (connection != null) {
2633: connection.close();
2634: }
2635: } catch (SQLException e) {
2636: Debug.logError(e, module);
2637: }
2638: }
2639:
2640: // fresh connection
2641: try {
2642: connection = getConnection();
2643: } catch (SQLException e) {
2644: if (messages != null)
2645: messages
2646: .add("Unable to esablish a connection with the database for helperName ["
2647: + this .helperName
2648: + "]... Error was: "
2649: + e.toString());
2650: return;
2651: } catch (GenericEntityException e) {
2652: if (messages != null)
2653: messages
2654: .add("Unable to esablish a connection with the database for helperName ["
2655: + this .helperName
2656: + "]... Error was: "
2657: + e.toString());
2658: return;
2659: }
2660:
2661: // remove the old column
2662: StringBuffer sqlBuf2 = new StringBuffer("ALTER TABLE ");
2663: sqlBuf2.append(entity.getTableName(datasourceInfo));
2664: sqlBuf2.append(" DROP COLUMN ");
2665: sqlBuf2.append(tempName);
2666:
2667: String sql2 = sqlBuf2.toString();
2668: if (Debug.infoOn())
2669: Debug.logInfo("[dropColumn] sql=" + sql2, module);
2670: try {
2671: stmt = connection.createStatement();
2672: stmt.executeUpdate(sql2);
2673: } catch (SQLException e) {
2674: String this Msg = "SQL Exception while executing the following:\n"
2675: + sql2 + "\nError was: " + e.toString();
2676: if (messages != null)
2677: messages.add(this Msg);
2678: Debug.logError(this Msg, module);
2679: return;
2680: } finally {
2681: try {
2682: if (stmt != null) {
2683: stmt.close();
2684: }
2685: } catch (SQLException e) {
2686: Debug.logError(e, module);
2687: }
2688: try {
2689: if (connection != null) {
2690: connection.close();
2691: }
2692: } catch (SQLException e) {
2693: Debug.logError(e, module);
2694: }
2695: }
2696: }
2697:
2698: public void repairColumnSizeChanges(Map modelEntities,
2699: List fieldsWrongSize, List messages) {
2700: if (modelEntities == null || fieldsWrongSize == null
2701: || fieldsWrongSize.size() == 0) {
2702: messages.add("No fields to repair");
2703: return;
2704: }
2705:
2706: if (messages == null)
2707: messages = new ArrayList();
2708:
2709: Iterator i = fieldsWrongSize.iterator();
2710: while (i.hasNext()) {
2711: String fieldInfo = (String) i.next();
2712: String entityName = fieldInfo.substring(0, fieldInfo
2713: .indexOf('.'));
2714: String fieldName = fieldInfo.substring(fieldInfo
2715: .indexOf('.') + 1);
2716:
2717: ModelEntity modelEntity = (ModelEntity) modelEntities
2718: .get(entityName);
2719: ModelField modelField = modelEntity.getField(fieldName);
2720: repairColumnSize(modelEntity, modelField, messages);
2721: }
2722: }
2723:
2724: private String makeTempFieldName(ModelField field) {
2725: String tempName = "tmp_" + field.getName();
2726: if (tempName.length() > 30) {
2727: tempName = tempName.substring(0, 30);
2728: }
2729: return tempName.toUpperCase();
2730: }
2731:
2732: /* ====================================================================== */
2733:
2734: /* ====================================================================== */
2735: public String makePkConstraintName(ModelEntity entity,
2736: int constraintNameClipLength) {
2737: String pkName = "PK_" + entity.getPlainTableName();
2738:
2739: if (pkName.length() > constraintNameClipLength) {
2740: pkName = pkName.substring(0, constraintNameClipLength);
2741: }
2742:
2743: return pkName;
2744: }
2745:
2746: public String makeFkConstraintName(ModelRelation modelRelation,
2747: int constraintNameClipLength) {
2748: String relConstraintName = modelRelation.getFkName();
2749:
2750: if (relConstraintName == null
2751: || relConstraintName.length() == 0) {
2752: relConstraintName = modelRelation.getTitle()
2753: + modelRelation.getRelEntityName();
2754: relConstraintName = relConstraintName.toUpperCase();
2755: }
2756:
2757: if (relConstraintName.length() > constraintNameClipLength) {
2758: relConstraintName = relConstraintName.substring(0,
2759: constraintNameClipLength);
2760: }
2761:
2762: return relConstraintName;
2763: }
2764:
2765: /* ====================================================================== */
2766: public String makeIndexName(ModelIndex modelIndex,
2767: int constraintNameClipLength) {
2768: String indexName = modelIndex.getName();
2769:
2770: if (indexName.length() > constraintNameClipLength) {
2771: indexName = indexName
2772: .substring(0, constraintNameClipLength);
2773: }
2774:
2775: return indexName;
2776: }
2777:
2778: /* ====================================================================== */
2779: public int createForeignKeys(ModelEntity entity, Map modelEntities,
2780: List messages) {
2781: return this .createForeignKeys(entity, modelEntities,
2782: datasourceInfo.constraintNameClipLength,
2783: datasourceInfo.fkStyle,
2784: datasourceInfo.useFkInitiallyDeferred, messages);
2785: }
2786:
2787: public int createForeignKeys(ModelEntity entity, Map modelEntities,
2788: int constraintNameClipLength, String fkStyle,
2789: boolean useFkInitiallyDeferred, List messages) {
2790: if (entity == null) {
2791: String errMsg = "ModelEntity was null and is required to create foreign keys for a table";
2792: Debug.logError(errMsg, module);
2793: if (messages != null)
2794: messages.add(errMsg);
2795: return 0;
2796: }
2797: if (entity instanceof ModelViewEntity) {
2798: //String errMsg = "ERROR: Cannot create foreign keys for a view entity";
2799: //Debug.logError(errMsg, module);
2800: //if (messages != null) messages.add(errMsg);
2801: return 0;
2802: }
2803:
2804: int fksCreated = 0;
2805:
2806: // go through the relationships to see if any foreign keys need to be added
2807: Iterator relationsIter = entity.getRelationsIterator();
2808: while (relationsIter.hasNext()) {
2809: ModelRelation modelRelation = (ModelRelation) relationsIter
2810: .next();
2811: if ("one".equals(modelRelation.getType())) {
2812: ModelEntity relModelEntity = (ModelEntity) modelEntities
2813: .get(modelRelation.getRelEntityName());
2814:
2815: if (relModelEntity == null) {
2816: String errMsg = "Error adding foreign key: ModelEntity was null for related entity name "
2817: + modelRelation.getRelEntityName();
2818: Debug.logError(errMsg, module);
2819: if (messages != null)
2820: messages.add(errMsg);
2821: continue;
2822: }
2823: if (relModelEntity instanceof ModelViewEntity) {
2824: String errMsg = "Error adding foreign key: related entity is a view entity for related entity name "
2825: + modelRelation.getRelEntityName();
2826: Debug.logError(errMsg, module);
2827: if (messages != null)
2828: messages.add(errMsg);
2829: continue;
2830: }
2831:
2832: String retMsg = createForeignKey(entity, modelRelation,
2833: relModelEntity, constraintNameClipLength,
2834: fkStyle, useFkInitiallyDeferred);
2835: if (retMsg != null && retMsg.length() > 0) {
2836: Debug.logError(retMsg, module);
2837: if (messages != null)
2838: messages.add(retMsg);
2839: continue;
2840: }
2841:
2842: fksCreated++;
2843: }
2844: }
2845:
2846: if (fksCreated > 0) {
2847: String message = "Created " + fksCreated
2848: + " foreign keys for entity ["
2849: + entity.getEntityName() + "]";
2850: Debug.logImportant(message, module);
2851: if (messages != null)
2852: messages.add(message);
2853: }
2854:
2855: return fksCreated;
2856: }
2857:
2858: public String createForeignKey(ModelEntity entity,
2859: ModelRelation modelRelation, ModelEntity relModelEntity,
2860: int constraintNameClipLength, String fkStyle,
2861: boolean useFkInitiallyDeferred) {
2862: Connection connection = null;
2863: Statement stmt = null;
2864:
2865: try {
2866: connection = getConnection();
2867: } catch (SQLException e) {
2868: String errMsg = "Unable to esablish a connection with the database for helperName ["
2869: + this .helperName
2870: + "]... Error was: "
2871: + e.toString();
2872: Debug.logError(e, errMsg, module);
2873: return errMsg;
2874: } catch (GenericEntityException e) {
2875: String errMsg = "Unable to esablish a connection with the database for helperName ["
2876: + this .helperName
2877: + "]... Error was: "
2878: + e.toString();
2879: Debug.logError(e, errMsg, module);
2880: return errMsg;
2881: }
2882:
2883: // now add constraint clause
2884: StringBuffer sqlBuf = new StringBuffer("ALTER TABLE ");
2885: sqlBuf.append(entity.getTableName(datasourceInfo));
2886: sqlBuf.append(" ADD ");
2887: String fkConstraintClause = makeFkConstraintClause(entity,
2888: modelRelation, relModelEntity,
2889: constraintNameClipLength, fkStyle,
2890: useFkInitiallyDeferred);
2891: if (UtilValidate.isEmpty(fkConstraintClause)) {
2892: return "Error creating foreign key constraint clause, see log for details";
2893: }
2894: sqlBuf.append(fkConstraintClause);
2895:
2896: if (Debug.verboseOn())
2897: Debug.logVerbose("[createForeignKey] sql="
2898: + sqlBuf.toString(), module);
2899: try {
2900: stmt = connection.createStatement();
2901: stmt.executeUpdate(sqlBuf.toString());
2902: } catch (SQLException e) {
2903: return "SQL Exception while executing the following:\n"
2904: + sqlBuf.toString() + "\nError was: "
2905: + e.toString();
2906: } finally {
2907: try {
2908: if (stmt != null) {
2909: stmt.close();
2910: }
2911: } catch (SQLException e) {
2912: Debug.logError(e, module);
2913: }
2914: try {
2915: if (connection != null) {
2916: connection.close();
2917: }
2918: } catch (SQLException e) {
2919: Debug.logError(e, module);
2920: }
2921: }
2922: return null;
2923: }
2924:
2925: public String makeFkConstraintClause(ModelEntity entity,
2926: ModelRelation modelRelation, ModelEntity relModelEntity,
2927: int constraintNameClipLength, String fkStyle,
2928: boolean useFkInitiallyDeferred) {
2929: // make the two column lists
2930: Iterator keyMapsIter = modelRelation.getKeyMapsIterator();
2931: StringBuffer mainCols = new StringBuffer();
2932: StringBuffer relCols = new StringBuffer();
2933:
2934: while (keyMapsIter.hasNext()) {
2935: ModelKeyMap keyMap = (ModelKeyMap) keyMapsIter.next();
2936:
2937: ModelField mainField = entity.getField(keyMap
2938: .getFieldName());
2939: if (mainField == null) {
2940: Debug.logError(
2941: "Bad key-map in entity ["
2942: + entity.getEntityName()
2943: + "] relation to ["
2944: + modelRelation.getTitle()
2945: + modelRelation.getRelEntityName()
2946: + "] for field ["
2947: + keyMap.getFieldName() + "]", module);
2948: return null;
2949: }
2950:
2951: if (mainCols.length() > 0) {
2952: mainCols.append(", ");
2953: }
2954: mainCols.append(mainField.getColName());
2955:
2956: ModelField relField = relModelEntity.getField(keyMap
2957: .getRelFieldName());
2958:
2959: if (relCols.length() > 0) {
2960: relCols.append(", ");
2961: }
2962: relCols.append(relField.getColName());
2963: }
2964:
2965: StringBuffer sqlBuf = new StringBuffer("");
2966:
2967: if ("name_constraint".equals(fkStyle)) {
2968: sqlBuf.append("CONSTRAINT ");
2969: String relConstraintName = makeFkConstraintName(
2970: modelRelation, constraintNameClipLength);
2971:
2972: sqlBuf.append(relConstraintName);
2973:
2974: sqlBuf.append(" FOREIGN KEY (");
2975: sqlBuf.append(mainCols.toString());
2976: sqlBuf.append(") REFERENCES ");
2977: sqlBuf.append(relModelEntity.getTableName(datasourceInfo));
2978: sqlBuf.append(" (");
2979: sqlBuf.append(relCols.toString());
2980: sqlBuf.append(")");
2981: if (useFkInitiallyDeferred) {
2982: sqlBuf.append(" INITIALLY DEFERRED");
2983: }
2984: } else if ("name_fk".equals(fkStyle)) {
2985: sqlBuf.append(" FOREIGN KEY ");
2986: String relConstraintName = makeFkConstraintName(
2987: modelRelation, constraintNameClipLength);
2988:
2989: sqlBuf.append(relConstraintName);
2990: sqlBuf.append(" (");
2991: sqlBuf.append(mainCols.toString());
2992: sqlBuf.append(") REFERENCES ");
2993: sqlBuf.append(relModelEntity.getTableName(datasourceInfo));
2994: sqlBuf.append(" (");
2995: sqlBuf.append(relCols.toString());
2996: sqlBuf.append(")");
2997: if (useFkInitiallyDeferred) {
2998: sqlBuf.append(" INITIALLY DEFERRED");
2999: }
3000: } else {
3001: String emsg = "ERROR: fk-style specified for this data-source is not valid: "
3002: + fkStyle;
3003:
3004: Debug.logError(emsg, module);
3005: throw new IllegalArgumentException(emsg);
3006: }
3007:
3008: return sqlBuf.toString();
3009: }
3010:
3011: public void deleteForeignKeys(ModelEntity entity,
3012: Map modelEntities, List messages) {
3013: this .deleteForeignKeys(entity, modelEntities,
3014: datasourceInfo.constraintNameClipLength, messages);
3015: }
3016:
3017: public void deleteForeignKeys(ModelEntity entity,
3018: Map modelEntities, int constraintNameClipLength,
3019: List messages) {
3020: if (entity == null) {
3021: String errMsg = "ModelEntity was null and is required to delete foreign keys for a table";
3022: if (messages != null)
3023: messages.add(errMsg);
3024: Debug.logError(errMsg, module);
3025: return;
3026: }
3027: if (entity instanceof ModelViewEntity) {
3028: //String errMsg = "ERROR: Cannot delete foreign keys for a view entity";
3029: //if (messages != null) messages.add(errMsg);
3030: //Debug.logError(errMsg, module);
3031: return;
3032: }
3033:
3034: String message = "Deleting foreign keys for entity ["
3035: + entity.getEntityName() + "]";
3036: Debug.logImportant(message, module);
3037: if (messages != null)
3038: messages.add(message);
3039:
3040: // go through the relationships to see if any foreign keys need to be added
3041: Iterator relationsIter = entity.getRelationsIterator();
3042: while (relationsIter.hasNext()) {
3043: ModelRelation modelRelation = (ModelRelation) relationsIter
3044: .next();
3045:
3046: if ("one".equals(modelRelation.getType())) {
3047: ModelEntity relModelEntity = (ModelEntity) modelEntities
3048: .get(modelRelation.getRelEntityName());
3049:
3050: if (relModelEntity == null) {
3051: String errMsg = "Error removing foreign key: ModelEntity was null for related entity name "
3052: + modelRelation.getRelEntityName();
3053: if (messages != null)
3054: messages.add(errMsg);
3055: Debug.logError(errMsg, module);
3056: continue;
3057: }
3058: if (relModelEntity instanceof ModelViewEntity) {
3059: String errMsg = "Error removing foreign key: related entity is a view entity for related entity name "
3060: + modelRelation.getRelEntityName();
3061: if (messages != null)
3062: messages.add(errMsg);
3063: Debug.logError(errMsg, module);
3064: continue;
3065: }
3066:
3067: String retMsg = deleteForeignKey(entity, modelRelation,
3068: relModelEntity, constraintNameClipLength);
3069: if (retMsg != null && retMsg.length() > 0) {
3070: if (messages != null)
3071: messages.add(retMsg);
3072: Debug.logError(retMsg, module);
3073: }
3074: }
3075: }
3076: }
3077:
3078: public String deleteForeignKey(ModelEntity entity,
3079: ModelRelation modelRelation, ModelEntity relModelEntity,
3080: int constraintNameClipLength) {
3081: Connection connection = null;
3082: Statement stmt = null;
3083:
3084: try {
3085: connection = getConnection();
3086: } catch (SQLException e) {
3087: String errMsg = "Unable to esablish a connection with the database for helperName ["
3088: + this .helperName
3089: + "]... Error was: "
3090: + e.toString();
3091: Debug.logError(e, errMsg, module);
3092: return errMsg;
3093: } catch (GenericEntityException e) {
3094: String errMsg = "Unable to esablish a connection with the database for helperName ["
3095: + this .helperName
3096: + "]... Error was: "
3097: + e.toString();
3098: Debug.logError(e, errMsg, module);
3099: return errMsg;
3100: }
3101:
3102: String relConstraintName = makeFkConstraintName(modelRelation,
3103: constraintNameClipLength);
3104:
3105: // now add constraint clause
3106: StringBuffer sqlBuf = new StringBuffer("ALTER TABLE ");
3107: sqlBuf.append(entity.getTableName(datasourceInfo));
3108: if (datasourceInfo.dropFkUseForeignKeyKeyword) {
3109: sqlBuf.append(" DROP FOREIGN KEY ");
3110: } else {
3111: sqlBuf.append(" DROP CONSTRAINT ");
3112: }
3113: sqlBuf.append(relConstraintName);
3114:
3115: if (Debug.verboseOn())
3116: Debug.logVerbose("[deleteForeignKey] sql="
3117: + sqlBuf.toString(), module);
3118: try {
3119: stmt = connection.createStatement();
3120: stmt.executeUpdate(sqlBuf.toString());
3121: } catch (SQLException e) {
3122: return "SQL Exception while executing the following:\n"
3123: + sqlBuf.toString() + "\nError was: "
3124: + e.toString();
3125: } finally {
3126: try {
3127: if (stmt != null)
3128: stmt.close();
3129: } catch (SQLException e) {
3130: Debug.logError(e, module);
3131: }
3132: try {
3133: if (connection != null) {
3134: connection.close();
3135: }
3136: } catch (SQLException e) {
3137: Debug.logError(e, module);
3138: }
3139: }
3140: return null;
3141: }
3142:
3143: /* ====================================================================== */
3144: /* ====================================================================== */
3145: public void createPrimaryKey(ModelEntity entity,
3146: boolean usePkConstraintNames, int constraintNameClipLength,
3147: List messages) {
3148: if (messages == null)
3149: messages = new ArrayList();
3150: String err = createPrimaryKey(entity, usePkConstraintNames,
3151: constraintNameClipLength);
3152: if (!UtilValidate.isEmpty(err)) {
3153: messages.add(err);
3154: }
3155: }
3156:
3157: public void createPrimaryKey(ModelEntity entity,
3158: boolean usePkConstraintNames, List messages) {
3159: createPrimaryKey(entity, usePkConstraintNames,
3160: datasourceInfo.constraintNameClipLength, messages);
3161: }
3162:
3163: public void createPrimaryKey(ModelEntity entity, List messages) {
3164: createPrimaryKey(entity, datasourceInfo.usePkConstraintNames,
3165: messages);
3166: }
3167:
3168: public String createPrimaryKey(ModelEntity entity,
3169: boolean usePkConstraintNames, int constraintNameClipLength) {
3170: if (entity == null) {
3171: return "ModelEntity was null and is required to create the primary key for a table";
3172: }
3173: if (entity instanceof ModelViewEntity) {
3174: return "Ignoring view entity [" + entity.getEntityName()
3175: + "]";
3176: }
3177:
3178: String message;
3179: if (entity.getPksSize() > 0) {
3180: message = "Creating primary key for entity ["
3181: + entity.getEntityName() + "]";
3182: Connection connection = null;
3183: Statement stmt = null;
3184:
3185: try {
3186: connection = getConnection();
3187: } catch (SQLException e) {
3188: return "Unable to esablish a connection with the database for helperName ["
3189: + this .helperName
3190: + "]... Error was: "
3191: + e.toString();
3192: } catch (GenericEntityException e) {
3193: return "Unable to esablish a connection with the database for helperName ["
3194: + this .helperName
3195: + "]... Error was: "
3196: + e.toString();
3197: }
3198:
3199: // now add constraint clause
3200: StringBuffer sqlBuf = new StringBuffer("ALTER TABLE ");
3201: sqlBuf.append(entity.getTableName(datasourceInfo));
3202: sqlBuf.append(" ADD ");
3203:
3204: String pkName = makePkConstraintName(entity,
3205: constraintNameClipLength);
3206:
3207: if (usePkConstraintNames) {
3208: sqlBuf.append("CONSTRAINT ");
3209: sqlBuf.append(pkName);
3210: }
3211: sqlBuf.append(" PRIMARY KEY (");
3212: sqlBuf.append(entity.colNameString(entity.getPksCopy()));
3213: sqlBuf.append(")");
3214:
3215: if (Debug.verboseOn())
3216: Debug.logVerbose("[createPrimaryKey] sql="
3217: + sqlBuf.toString(), module);
3218: try {
3219: stmt = connection.createStatement();
3220: stmt.executeUpdate(sqlBuf.toString());
3221: } catch (SQLException e) {
3222: return "SQL Exception while executing the following:\n"
3223: + sqlBuf.toString() + "\nError was: "
3224: + e.toString();
3225: } finally {
3226: try {
3227: if (stmt != null) {
3228: stmt.close();
3229: }
3230: } catch (SQLException e) {
3231: Debug.logError(e, module);
3232: }
3233: try {
3234: if (connection != null) {
3235: connection.close();
3236: }
3237: } catch (SQLException e) {
3238: Debug.logError(e, module);
3239: }
3240: }
3241: } else {
3242: message = "No primary-key defined for table ["
3243: + entity.getEntityName() + "]";
3244: }
3245:
3246: Debug.logImportant(message, module);
3247: return message;
3248: }
3249:
3250: public void deletePrimaryKey(ModelEntity entity,
3251: boolean usePkConstraintNames, int constraintNameClipLength,
3252: List messages) {
3253: if (messages == null)
3254: messages = new ArrayList();
3255: String err = deletePrimaryKey(entity, usePkConstraintNames,
3256: constraintNameClipLength);
3257: if (!UtilValidate.isEmpty(err)) {
3258: messages.add(err);
3259: }
3260: }
3261:
3262: public void deletePrimaryKey(ModelEntity entity,
3263: boolean usePkConstraintNames, List messages) {
3264: deletePrimaryKey(entity, usePkConstraintNames,
3265: datasourceInfo.constraintNameClipLength, messages);
3266: }
3267:
3268: public void deletePrimaryKey(ModelEntity entity, List messages) {
3269: deletePrimaryKey(entity, datasourceInfo.usePkConstraintNames,
3270: messages);
3271: }
3272:
3273: public String deletePrimaryKey(ModelEntity entity,
3274: boolean usePkConstraintNames, int constraintNameClipLength) {
3275: if (entity == null) {
3276: return "ModelEntity was null and is required to delete the primary key for a table";
3277: }
3278: if (entity instanceof ModelViewEntity) {
3279: return "Ignoring view entity [" + entity.getEntityName()
3280: + "]";
3281: }
3282:
3283: String message;
3284: if (entity.getPksSize() > 0) {
3285: message = "Deleting primary key for entity ["
3286: + entity.getEntityName() + "]";
3287: Connection connection = null;
3288: Statement stmt = null;
3289: try {
3290: connection = getConnection();
3291: } catch (SQLException e) {
3292: String errMsg = "Unable to esablish a connection with the database for helperName ["
3293: + this .helperName
3294: + "]... Error was: "
3295: + e.toString();
3296: Debug.logError(e, errMsg, module);
3297: return errMsg;
3298: } catch (GenericEntityException e) {
3299: String errMsg = "Unable to esablish a connection with the database for helperName ["
3300: + this .helperName
3301: + "]... Error was: "
3302: + e.toString();
3303: Debug.logError(e, errMsg, module);
3304: return errMsg;
3305: }
3306:
3307: // now add constraint clause
3308: StringBuffer sqlBuf = new StringBuffer("ALTER TABLE ");
3309: sqlBuf.append(entity.getTableName(datasourceInfo));
3310: sqlBuf.append(" DROP ");
3311:
3312: String pkName = makePkConstraintName(entity,
3313: constraintNameClipLength);
3314:
3315: if (usePkConstraintNames) {
3316: sqlBuf.append("CONSTRAINT ");
3317: sqlBuf.append(pkName);
3318: sqlBuf.append(" CASCADE");
3319: } else {
3320: sqlBuf.append(" PRIMARY KEY");
3321: // DEJ20050502 not sure why this is here, shouldn't be needed and some dbs don't support like this, ie when used with PRIMARY KEY: sqlBuf.append(" CASCADE");
3322: }
3323:
3324: if (Debug.verboseOn())
3325: Debug.logVerbose("[deletePrimaryKey] sql="
3326: + sqlBuf.toString(), module);
3327: try {
3328: stmt = connection.createStatement();
3329: stmt.executeUpdate(sqlBuf.toString());
3330: } catch (SQLException e) {
3331: String errMsg = "SQL Exception while executing the following:\n"
3332: + sqlBuf.toString()
3333: + "\nError was: "
3334: + e.toString();
3335: Debug.logError(e, errMsg, module);
3336: return errMsg;
3337: } finally {
3338: try {
3339: if (stmt != null)
3340: stmt.close();
3341: } catch (SQLException e) {
3342: Debug.logError(e, module);
3343: }
3344: try {
3345: if (connection != null) {
3346: connection.close();
3347: }
3348: } catch (SQLException e) {
3349: Debug.logError(e, module);
3350: }
3351: }
3352: } else {
3353: message = "No primary-key defined for table ["
3354: + entity.getEntityName() + "]";
3355: }
3356:
3357: Debug.logImportant(message, module);
3358: return message;
3359: }
3360:
3361: /* ====================================================================== */
3362: /* ====================================================================== */
3363: public int createDeclaredIndices(ModelEntity entity, List messages) {
3364: if (entity == null) {
3365: String message = "ERROR: ModelEntity was null and is required to create declared indices for a table";
3366: Debug.logError(message, module);
3367: if (messages != null)
3368: messages.add(message);
3369: return 0;
3370: }
3371: if (entity instanceof ModelViewEntity) {
3372: String message = "WARNING: Cannot create declared indices for a view entity";
3373: Debug.logWarning(message, module);
3374: if (messages != null)
3375: messages.add(message);
3376: return 0;
3377: }
3378:
3379: int dinsCreated = 0;
3380:
3381: // go through the indexes to see if any need to be added
3382: Iterator indexesIter = entity.getIndexesIterator();
3383: while (indexesIter.hasNext()) {
3384: ModelIndex modelIndex = (ModelIndex) indexesIter.next();
3385:
3386: String retMsg = createDeclaredIndex(entity, modelIndex);
3387: if (retMsg != null && retMsg.length() > 0) {
3388: String message = "Could not create declared indices for entity ["
3389: + entity.getEntityName() + "]: " + retMsg;
3390: Debug.logError(message, module);
3391: if (messages != null)
3392: messages.add(message);
3393: continue;
3394: }
3395: dinsCreated++;
3396: }
3397:
3398: if (dinsCreated > 0) {
3399: String message = "Created " + dinsCreated
3400: + " declared indices for entity ["
3401: + entity.getEntityName() + "]";
3402: Debug.logImportant(message, module);
3403: if (messages != null)
3404: messages.add(message);
3405: }
3406: return dinsCreated;
3407: }
3408:
3409: public String createDeclaredIndex(ModelEntity entity,
3410: ModelIndex modelIndex) {
3411: Connection connection = null;
3412: Statement stmt = null;
3413:
3414: try {
3415: connection = getConnection();
3416: } catch (SQLException e) {
3417: String errMsg = "Unable to esablish a connection with the database for helperName ["
3418: + this .helperName
3419: + "]... Error was: "
3420: + e.toString();
3421: Debug.logError(e, errMsg, module);
3422: return errMsg;
3423: } catch (GenericEntityException e) {
3424: String errMsg = "Unable to esablish a connection with the database for helperName ["
3425: + this .helperName
3426: + "]... Error was: "
3427: + e.toString();
3428: Debug.logError(e, errMsg, module);
3429: return errMsg;
3430: }
3431:
3432: String createIndexSql = makeIndexClause(entity, modelIndex);
3433: if (Debug.verboseOn())
3434: Debug.logVerbose("[createForeignKeyIndex] index sql="
3435: + createIndexSql, module);
3436:
3437: try {
3438: stmt = connection.createStatement();
3439: stmt.executeUpdate(createIndexSql);
3440: } catch (SQLException e) {
3441: return "SQL Exception while executing the following:\n"
3442: + createIndexSql + "\nError was: " + e.toString();
3443: } finally {
3444: try {
3445: if (stmt != null)
3446: stmt.close();
3447: } catch (SQLException e) {
3448: Debug.logError(e, module);
3449: }
3450: try {
3451: if (connection != null) {
3452: connection.close();
3453: }
3454: } catch (SQLException e) {
3455: Debug.logError(e, module);
3456: }
3457: }
3458: return null;
3459: }
3460:
3461: public String makeIndexClause(ModelEntity entity,
3462: ModelIndex modelIndex) {
3463: Iterator fieldNamesIter = modelIndex.getIndexFieldsIterator();
3464: StringBuffer mainCols = new StringBuffer();
3465:
3466: while (fieldNamesIter.hasNext()) {
3467: String fieldName = (String) fieldNamesIter.next();
3468: ModelField mainField = entity.getField(fieldName);
3469: if (mainCols.length() > 0) {
3470: mainCols.append(", ");
3471: }
3472: mainCols.append(mainField.getColName());
3473: }
3474:
3475: StringBuffer indexSqlBuf = new StringBuffer("CREATE ");
3476: if (modelIndex.getUnique()) {
3477: indexSqlBuf.append("UNIQUE ");
3478: }
3479: indexSqlBuf.append("INDEX ");
3480: indexSqlBuf.append(makeIndexName(modelIndex,
3481: datasourceInfo.constraintNameClipLength));
3482: indexSqlBuf.append(" ON ");
3483: indexSqlBuf.append(entity.getTableName(datasourceInfo));
3484:
3485: indexSqlBuf.append(" (");
3486: indexSqlBuf.append(mainCols.toString());
3487: indexSqlBuf.append(")");
3488:
3489: return indexSqlBuf.toString();
3490: }
3491:
3492: public void deleteDeclaredIndices(ModelEntity entity, List messages) {
3493: if (messages == null)
3494: messages = new ArrayList();
3495: String err = deleteDeclaredIndices(entity);
3496: if (!UtilValidate.isEmpty(err)) {
3497: messages.add(err);
3498: }
3499: }
3500:
3501: public String deleteDeclaredIndices(ModelEntity entity) {
3502: if (entity == null) {
3503: return "ModelEntity was null and is required to delete declared indices for a table";
3504: }
3505: if (entity instanceof ModelViewEntity) {
3506: return "ERROR: Cannot delete declared indices for a view entity";
3507: }
3508:
3509: StringBuffer retMsgsBuffer = new StringBuffer();
3510:
3511: // go through the relationships to see if any foreign keys need to be added
3512: Iterator indexesIter = entity.getIndexesIterator();
3513: while (indexesIter.hasNext()) {
3514: ModelIndex modelIndex = (ModelIndex) indexesIter.next();
3515: String retMsg = deleteDeclaredIndex(entity, modelIndex);
3516: if (retMsg != null && retMsg.length() > 0) {
3517: if (retMsgsBuffer.length() > 0) {
3518: retMsgsBuffer.append("\n");
3519: }
3520: retMsgsBuffer.append(retMsg);
3521: if (Debug.infoOn())
3522: Debug.logInfo(retMsg, module);
3523: }
3524: }
3525:
3526: if (retMsgsBuffer.length() > 0) {
3527: return retMsgsBuffer.toString();
3528: } else {
3529: return null;
3530: }
3531: }
3532:
3533: public String deleteDeclaredIndex(ModelEntity entity,
3534: ModelIndex modelIndex) {
3535: Connection connection = null;
3536: Statement stmt = null;
3537:
3538: try {
3539: connection = getConnection();
3540: } catch (SQLException e) {
3541: String errMsg = "Unable to esablish a connection with the database for helperName ["
3542: + this .helperName
3543: + "]... Error was: "
3544: + e.toString();
3545: Debug.logError(e, errMsg, module);
3546: return errMsg;
3547: } catch (GenericEntityException e) {
3548: String errMsg = "Unable to esablish a connection with the database for helperName ["
3549: + this .helperName
3550: + "]... Error was: "
3551: + e.toString();
3552: Debug.logError(e, errMsg, module);
3553: return errMsg;
3554: }
3555:
3556: // TODO: also remove the constraing if this was a unique index, in most databases dropping the index does not drop the constraint
3557:
3558: StringBuffer indexSqlBuf = new StringBuffer("DROP INDEX ");
3559: String tableName = entity.getTableName(datasourceInfo);
3560: String schemaName = (tableName == null
3561: || tableName.length() == 0 || tableName.indexOf('.') == -1) ? ""
3562: : tableName.substring(0, tableName.indexOf('.'));
3563:
3564: indexSqlBuf.append(schemaName);
3565: indexSqlBuf.append(".");
3566: indexSqlBuf.append(modelIndex.getName());
3567:
3568: String deleteIndexSql = indexSqlBuf.toString();
3569: if (Debug.verboseOn())
3570: Debug.logVerbose("[deleteDeclaredIndex] index sql="
3571: + deleteIndexSql, module);
3572:
3573: try {
3574: stmt = connection.createStatement();
3575: stmt.executeUpdate(deleteIndexSql);
3576: } catch (SQLException e) {
3577: return "SQL Exception while executing the following:\n"
3578: + deleteIndexSql + "\nError was: " + e.toString();
3579: } finally {
3580: try {
3581: if (stmt != null)
3582: stmt.close();
3583: } catch (SQLException e) {
3584: Debug.logError(e, module);
3585: }
3586: try {
3587: if (connection != null) {
3588: connection.close();
3589: }
3590: } catch (SQLException e) {
3591: Debug.logError(e, module);
3592: }
3593: }
3594: return null;
3595: }
3596:
3597: /* ====================================================================== */
3598: /* ====================================================================== */
3599: public int createForeignKeyIndices(ModelEntity entity, List messages) {
3600: return createForeignKeyIndices(entity,
3601: datasourceInfo.constraintNameClipLength, messages);
3602: }
3603:
3604: public int createForeignKeyIndices(ModelEntity entity,
3605: int constraintNameClipLength, List messages) {
3606: if (entity == null) {
3607: String message = "ERROR: ModelEntity was null and is required to create foreign keys indices for a table";
3608: Debug.logError(message, module);
3609: if (messages != null)
3610: messages.add(message);
3611: return 0;
3612: }
3613: if (entity instanceof ModelViewEntity) {
3614: String message = "WARNING: Cannot create foreign keys indices for a view entity";
3615: Debug.logWarning(message, module);
3616: if (messages != null)
3617: messages.add(message);
3618: return 0;
3619: }
3620:
3621: int fkisCreated = 0;
3622:
3623: // go through the relationships to see if any foreign keys need to be added
3624: Iterator relationsIter = entity.getRelationsIterator();
3625: while (relationsIter.hasNext()) {
3626: ModelRelation modelRelation = (ModelRelation) relationsIter
3627: .next();
3628: if ("one".equals(modelRelation.getType())) {
3629: String retMsg = createForeignKeyIndex(entity,
3630: modelRelation, constraintNameClipLength);
3631: if (retMsg != null && retMsg.length() > 0) {
3632: String message = "Could not create foreign key indices for entity ["
3633: + entity.getEntityName() + "]: " + retMsg;
3634: Debug.logError(message, module);
3635: if (messages != null)
3636: messages.add(message);
3637: continue;
3638: }
3639: fkisCreated++;
3640: }
3641: }
3642:
3643: if (fkisCreated > 0) {
3644: String message = "Created " + fkisCreated
3645: + " foreign key indices for entity ["
3646: + entity.getEntityName() + "]";
3647: Debug.logImportant(message, module);
3648: if (messages != null)
3649: messages.add(message);
3650: }
3651: return fkisCreated;
3652: }
3653:
3654: public String createForeignKeyIndex(ModelEntity entity,
3655: ModelRelation modelRelation, int constraintNameClipLength) {
3656: Connection connection = null;
3657: Statement stmt = null;
3658:
3659: try {
3660: connection = getConnection();
3661: } catch (SQLException e) {
3662: String errMsg = "Unable to esablish a connection with the database for helperName ["
3663: + this .helperName
3664: + "]... Error was: "
3665: + e.toString();
3666: Debug.logError(e, errMsg, module);
3667: return errMsg;
3668: } catch (GenericEntityException e) {
3669: String errMsg = "Unable to esablish a connection with the database for helperName ["
3670: + this .helperName
3671: + "]... Error was: "
3672: + e.toString();
3673: Debug.logError(e, errMsg, module);
3674: return errMsg;
3675: }
3676:
3677: String createIndexSql = makeFkIndexClause(entity,
3678: modelRelation, constraintNameClipLength);
3679: if (UtilValidate.isEmpty(createIndexSql)) {
3680: return "Error creating foreign key index clause, see log for details";
3681: }
3682:
3683: if (Debug.verboseOn())
3684: Debug.logVerbose("[createForeignKeyIndex] index sql="
3685: + createIndexSql, module);
3686:
3687: try {
3688: stmt = connection.createStatement();
3689: stmt.executeUpdate(createIndexSql);
3690: } catch (SQLException e) {
3691: return "SQL Exception while executing the following:\n"
3692: + createIndexSql + "\nError was: " + e.toString();
3693: } finally {
3694: try {
3695: if (stmt != null)
3696: stmt.close();
3697: } catch (SQLException e) {
3698: Debug.logError(e, module);
3699: }
3700: try {
3701: if (connection != null) {
3702: connection.close();
3703: }
3704: } catch (SQLException e) {
3705: Debug.logError(e, module);
3706: }
3707: }
3708: return null;
3709: }
3710:
3711: public String makeFkIndexClause(ModelEntity entity,
3712: ModelRelation modelRelation, int constraintNameClipLength) {
3713: Iterator keyMapsIter = modelRelation.getKeyMapsIterator();
3714: StringBuffer mainCols = new StringBuffer();
3715:
3716: while (keyMapsIter.hasNext()) {
3717: ModelKeyMap keyMap = (ModelKeyMap) keyMapsIter.next();
3718: ModelField mainField = entity.getField(keyMap
3719: .getFieldName());
3720:
3721: if (mainField == null) {
3722: Debug.logError(
3723: "Bad key-map in entity ["
3724: + entity.getEntityName()
3725: + "] relation to ["
3726: + modelRelation.getTitle()
3727: + modelRelation.getRelEntityName()
3728: + "] for field ["
3729: + keyMap.getFieldName() + "]", module);
3730: return null;
3731: }
3732:
3733: if (mainCols.length() > 0) {
3734: mainCols.append(", ");
3735: }
3736: mainCols.append(mainField.getColName());
3737: }
3738:
3739: StringBuffer indexSqlBuf = new StringBuffer("CREATE INDEX ");
3740: String relConstraintName = makeFkConstraintName(modelRelation,
3741: constraintNameClipLength);
3742:
3743: indexSqlBuf.append(relConstraintName);
3744: indexSqlBuf.append(" ON ");
3745: indexSqlBuf.append(entity.getTableName(datasourceInfo));
3746:
3747: indexSqlBuf.append(" (");
3748: indexSqlBuf.append(mainCols.toString());
3749: indexSqlBuf.append(")");
3750:
3751: return indexSqlBuf.toString();
3752: }
3753:
3754: public void deleteForeignKeyIndices(ModelEntity entity,
3755: List messages) {
3756: if (messages == null)
3757: messages = new ArrayList();
3758: String err = deleteForeignKeyIndices(entity,
3759: datasourceInfo.constraintNameClipLength);
3760: if (!UtilValidate.isEmpty(err)) {
3761: messages.add(err);
3762: }
3763: }
3764:
3765: public String deleteForeignKeyIndices(ModelEntity entity,
3766: int constraintNameClipLength) {
3767: if (entity == null) {
3768: return "ModelEntity was null and is required to delete foreign keys indices for a table";
3769: }
3770: if (entity instanceof ModelViewEntity) {
3771: return "ERROR: Cannot delete foreign keys indices for a view entity";
3772: }
3773:
3774: StringBuffer retMsgsBuffer = new StringBuffer();
3775:
3776: // go through the relationships to see if any foreign keys need to be added
3777: Iterator relationsIter = entity.getRelationsIterator();
3778:
3779: while (relationsIter.hasNext()) {
3780: ModelRelation modelRelation = (ModelRelation) relationsIter
3781: .next();
3782:
3783: if ("one".equals(modelRelation.getType())) {
3784: String retMsg = deleteForeignKeyIndex(entity,
3785: modelRelation, constraintNameClipLength);
3786:
3787: if (retMsg != null && retMsg.length() > 0) {
3788: if (retMsgsBuffer.length() > 0) {
3789: retMsgsBuffer.append("\n");
3790: }
3791: retMsgsBuffer.append(retMsg);
3792: }
3793: }
3794: }
3795: if (retMsgsBuffer.length() > 0) {
3796: return retMsgsBuffer.toString();
3797: } else {
3798: return null;
3799: }
3800: }
3801:
3802: public String deleteForeignKeyIndex(ModelEntity entity,
3803: ModelRelation modelRelation, int constraintNameClipLength) {
3804: Connection connection = null;
3805: Statement stmt = null;
3806:
3807: try {
3808: connection = getConnection();
3809: } catch (SQLException e) {
3810: String errMsg = "Unable to esablish a connection with the database for helperName ["
3811: + this .helperName
3812: + "]... Error was: "
3813: + e.toString();
3814: Debug.logError(e, errMsg, module);
3815: return errMsg;
3816: } catch (GenericEntityException e) {
3817: String errMsg = "Unable to esablish a connection with the database for helperName ["
3818: + this .helperName
3819: + "]... Error was: "
3820: + e.toString();
3821: Debug.logError(e, errMsg, module);
3822: return errMsg;
3823: }
3824:
3825: StringBuffer indexSqlBuf = new StringBuffer("DROP INDEX ");
3826: String relConstraintName = makeFkConstraintName(modelRelation,
3827: constraintNameClipLength);
3828:
3829: String tableName = entity.getTableName(datasourceInfo);
3830: String schemaName = (tableName == null
3831: || tableName.length() == 0 || tableName.indexOf('.') == -1) ? ""
3832: : tableName.substring(0, tableName.indexOf('.'));
3833:
3834: if (UtilValidate.isNotEmpty(schemaName)) {
3835: indexSqlBuf.append(schemaName);
3836: indexSqlBuf.append(".");
3837: }
3838: indexSqlBuf.append(relConstraintName);
3839:
3840: String deleteIndexSql = indexSqlBuf.toString();
3841:
3842: if (Debug.verboseOn())
3843: Debug.logVerbose("[deleteForeignKeyIndex] index sql="
3844: + deleteIndexSql, module);
3845:
3846: try {
3847: stmt = connection.createStatement();
3848: stmt.executeUpdate(deleteIndexSql);
3849: } catch (SQLException e) {
3850: return "SQL Exception while executing the following:\n"
3851: + deleteIndexSql + "\nError was: " + e.toString();
3852: } finally {
3853: try {
3854: if (stmt != null) {
3855: stmt.close();
3856: }
3857: } catch (SQLException e) {
3858: Debug.logError(e, module);
3859: }
3860: try {
3861: if (connection != null) {
3862: connection.close();
3863: }
3864: } catch (SQLException e) {
3865: Debug.logError(e, module);
3866: }
3867: }
3868: return null;
3869: }
3870:
3871: public String getSchemaName(DatabaseMetaData dbData)
3872: throws SQLException {
3873: if (!isLegacy && this .datasourceInfo.useSchemas
3874: && dbData.supportsSchemasInTableDefinitions()) {
3875: if (this .datasourceInfo.schemaName != null
3876: && this .datasourceInfo.schemaName.length() > 0) {
3877: return this .datasourceInfo.schemaName;
3878: } else {
3879: return dbData.getUserName();
3880: }
3881: }
3882: return null;
3883: }
3884:
3885: /* ====================================================================== */
3886: /* ====================================================================== */
3887: public void updateCharacterSetAndCollation(ModelEntity entity,
3888: List messages) {
3889: if (entity instanceof ModelViewEntity) {
3890: return;
3891: }
3892: if (UtilValidate.isEmpty(this .datasourceInfo.characterSet)
3893: && UtilValidate.isEmpty(this .datasourceInfo.collate)) {
3894: messages
3895: .add("Not setting character-set and collate for entity ["
3896: + entity.getEntityName()
3897: + "], options not specified in the datasource definition in the entityengine.xml file.");
3898: return;
3899: }
3900:
3901: Connection connection = null;
3902:
3903: try {
3904: Statement stmt = null;
3905:
3906: try {
3907: connection = getConnection();
3908: } catch (SQLException e) {
3909: String errMsg = "Unable to esablish a connection with the database for helperName ["
3910: + this .helperName
3911: + "]... Error was: "
3912: + e.toString();
3913: Debug.logError(e, errMsg, module);
3914: messages.add(errMsg);
3915: } catch (GenericEntityException e) {
3916: String errMsg = "Unable to esablish a connection with the database for helperName ["
3917: + this .helperName
3918: + "]... Error was: "
3919: + e.toString();
3920: Debug.logError(e, errMsg, module);
3921: messages.add(errMsg);
3922: }
3923: if (connection == null) {
3924: return;
3925: }
3926:
3927: StringBuffer sqlTableBuf = new StringBuffer("ALTER TABLE ");
3928: sqlTableBuf
3929: .append(entity.getTableName(this .datasourceInfo));
3930: //sqlTableBuf.append("");
3931:
3932: // if there is a characterSet, add the CHARACTER SET arg here
3933: if (UtilValidate
3934: .isNotEmpty(this .datasourceInfo.characterSet)) {
3935: sqlTableBuf.append(" DEFAULT CHARACTER SET ");
3936: sqlTableBuf.append(this .datasourceInfo.characterSet);
3937: }
3938: // if there is a collate, add the COLLATE arg here
3939: if (UtilValidate.isNotEmpty(this .datasourceInfo.collate)) {
3940: sqlTableBuf.append(" COLLATE ");
3941: sqlTableBuf.append(this .datasourceInfo.collate);
3942: }
3943:
3944: if (Debug.verboseOn())
3945: Debug.logVerbose(
3946: "[updateCharacterSetAndCollation] character-set and collate sql="
3947: + sqlTableBuf, module);
3948:
3949: try {
3950: stmt = connection.createStatement();
3951: stmt.executeUpdate(sqlTableBuf.toString());
3952: } catch (SQLException e) {
3953: String errMsg = "SQL Exception while executing the following:\n"
3954: + sqlTableBuf + "\nError was: " + e.toString();
3955: messages.add(errMsg);
3956: Debug.logError(errMsg, module);
3957: } finally {
3958: try {
3959: if (stmt != null)
3960: stmt.close();
3961: } catch (SQLException e) {
3962: Debug.logError(e, module);
3963: }
3964: }
3965:
3966: Iterator fieldIter = entity.getFieldsIterator();
3967: while (fieldIter.hasNext()) {
3968: ModelField field = (ModelField) fieldIter.next();
3969: ModelFieldType type = modelFieldTypeReader
3970: .getModelFieldType(field.getType());
3971: if (type == null) {
3972: messages.add("Field type [" + type
3973: + "] not found for field ["
3974: + field.getName() + "] of entity ["
3975: + entity.getEntityName()
3976: + "], not creating table.");
3977: continue;
3978: }
3979: if (!"String".equals(type.getJavaType())
3980: && !"java.lang.String".equals(type
3981: .getJavaType())) {
3982: continue;
3983: }
3984:
3985: StringBuffer sqlBuf = new StringBuffer("ALTER TABLE ");
3986: sqlBuf.append(entity.getTableName(this .datasourceInfo));
3987: sqlBuf.append(" MODIFY COLUMN ");
3988: sqlBuf.append(field.getColName());
3989: sqlBuf.append(" ");
3990: sqlBuf.append(type.getSqlType());
3991:
3992: // if there is a characterSet, add the CHARACTER SET arg here
3993: if (UtilValidate
3994: .isNotEmpty(this .datasourceInfo.characterSet)) {
3995: sqlBuf.append(" CHARACTER SET ");
3996: sqlBuf.append(this .datasourceInfo.characterSet);
3997: }
3998: // if there is a collate, add the COLLATE arg here
3999: if (UtilValidate
4000: .isNotEmpty(this .datasourceInfo.collate)) {
4001: sqlBuf.append(" COLLATE ");
4002: sqlBuf.append(this .datasourceInfo.collate);
4003: }
4004:
4005: if (field.getIsPk()) {
4006: if (this .datasourceInfo.alwaysUseConstraintKeyword) {
4007: sqlBuf.append(" CONSTRAINT NOT NULL");
4008: } else {
4009: sqlBuf.append(" NOT NULL");
4010: }
4011: }
4012:
4013: if (Debug.verboseOn())
4014: Debug.logVerbose(
4015: "[updateCharacterSetAndCollation] character-set and collate sql="
4016: + sqlBuf, module);
4017: try {
4018: stmt = connection.createStatement();
4019: stmt.executeUpdate(sqlBuf.toString());
4020: } catch (SQLException e) {
4021: String errMsg = "SQL Exception while executing the following:\n"
4022: + sqlBuf + "\nError was: " + e.toString();
4023: messages.add(errMsg);
4024: Debug.logError(errMsg, module);
4025: } finally {
4026: try {
4027: if (stmt != null)
4028: stmt.close();
4029: } catch (SQLException e) {
4030: Debug.logError(e, module);
4031: }
4032: }
4033: }
4034: } finally {
4035: try {
4036: if (connection != null) {
4037: connection.close();
4038: }
4039: } catch (SQLException e) {
4040: Debug.logError(e, module);
4041: }
4042: }
4043: }
4044:
4045: /* ====================================================================== */
4046: /* ====================================================================== */
4047: public static class ColumnCheckInfo implements Serializable {
4048: public String tableName;
4049: public String columnName;
4050: public String typeName;
4051: public int columnSize;
4052: public int decimalDigits;
4053: public String isNullable; // YES/NO or "" = ie nobody knows
4054: public boolean isPk = false;
4055: public int pkSeq;
4056: public String pkName;
4057:
4058: public static String fixupTableName(String rawTableName,
4059: String lookupSchemaName, boolean needsUpperCase) {
4060: String tableName = rawTableName;
4061: // for those databases which do not return the schema name with the table name (pgsql 7.3)
4062: boolean appendSchemaName = false;
4063: if (tableName != null && lookupSchemaName != null
4064: && !tableName.startsWith(lookupSchemaName)) {
4065: appendSchemaName = true;
4066: }
4067: if (needsUpperCase && tableName != null) {
4068: tableName = tableName.toUpperCase();
4069: }
4070: if (appendSchemaName) {
4071: tableName = lookupSchemaName + "." + tableName;
4072: }
4073: return tableName;
4074: }
4075: }
4076:
4077: public static class ReferenceCheckInfo implements Serializable {
4078: public String pkTableName;
4079:
4080: /** Comma separated list of column names in the related tables primary key */
4081: public String pkColumnName;
4082: public String fkName;
4083: public String fkTableName;
4084:
4085: /** Comma separated list of column names in the primary tables foreign keys */
4086: public String fkColumnName;
4087:
4088: public String toString() {
4089: return "FK Reference from table " + fkTableName
4090: + " called " + fkName + " to PK in table "
4091: + pkTableName;
4092: }
4093: }
4094: }
|