0001: /*
0002: * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
0003: *
0004: * Copyright 1997-2007 Sun Microsystems, Inc. All rights reserved.
0005: *
0006: * The contents of this file are subject to the terms of either the GNU
0007: * General Public License Version 2 only ("GPL") or the Common
0008: * Development and Distribution License("CDDL") (collectively, the
0009: * "License"). You may not use this file except in compliance with the
0010: * License. You can obtain a copy of the License at
0011: * http://www.netbeans.org/cddl-gplv2.html
0012: * or nbbuild/licenses/CDDL-GPL-2-CP. See the License for the
0013: * specific language governing permissions and limitations under the
0014: * License. When distributing the software, include this License Header
0015: * Notice in each file and include the License file at
0016: * nbbuild/licenses/CDDL-GPL-2-CP. Sun designates this
0017: * particular file as subject to the "Classpath" exception as provided
0018: * by Sun in the GPL Version 2 section of the License file that
0019: * accompanied this code. If applicable, add the following below the
0020: * License Header, with the fields enclosed by brackets [] replaced by
0021: * your own identifying information:
0022: * "Portions Copyrighted [year] [name of copyright owner]"
0023: *
0024: * Contributor(s):
0025: *
0026: * The Original Software is NetBeans. The Initial Developer of the Original
0027: * Software is Sun Microsystems, Inc. Portions Copyright 1997-2007 Sun
0028: * Microsystems, Inc. All Rights Reserved.
0029: *
0030: * If you wish your version of this file to be governed by only the CDDL
0031: * or only the GPL Version 2, indicate your decision by adding
0032: * "[Contributor] elects to include this software in this distribution
0033: * under the [CDDL or GPL Version 2] license." If you do not indicate a
0034: * single choice of license, a recipient has the option to distribute
0035: * your version of this file under either the CDDL, the GPL Version 2 or
0036: * to extend the choice of license to its licensees as provided above.
0037: * However, if you add GPL Version 2 code and therefore, elected the GPL
0038: * Version 2 license, then the option applies only if the new code is
0039: * made subject to such option by the copyright holder.
0040: */
0041: package org.netbeans.modules.sql.framework.model.utils;
0042:
0043: import java.sql.Connection;
0044: import java.sql.ResultSet;
0045: import java.sql.SQLException;
0046: import java.sql.Statement;
0047: import java.util.ArrayList;
0048: import java.util.Collection;
0049: import java.util.Collections;
0050: import java.util.Iterator;
0051: import java.util.List;
0052: import java.util.Map;
0053:
0054: import org.netbeans.modules.sql.framework.model.DBMetaDataFactory;
0055: import org.netbeans.modules.sql.framework.common.utils.DBExplorerUtil;
0056: import org.netbeans.modules.sql.framework.model.ColumnRef;
0057: import org.netbeans.modules.sql.framework.model.RuntimeDatabaseModel;
0058: import org.netbeans.modules.sql.framework.model.RuntimeInput;
0059: import org.netbeans.modules.sql.framework.model.SQLCanvasObject;
0060: import org.netbeans.modules.sql.framework.model.SQLCondition;
0061: import org.netbeans.modules.sql.framework.model.SQLConnectableObject;
0062: import org.netbeans.modules.sql.framework.model.SQLConstants;
0063: import org.netbeans.modules.sql.framework.model.SQLContainerObject;
0064: import org.netbeans.modules.sql.framework.model.SQLDBColumn;
0065: import org.netbeans.modules.sql.framework.model.SQLDBModel;
0066: import org.netbeans.modules.sql.framework.model.SQLDBTable;
0067: import org.netbeans.modules.sql.framework.model.SQLDefinition;
0068: import org.netbeans.modules.sql.framework.model.SQLGenericOperator;
0069: import org.netbeans.modules.sql.framework.model.SQLInputObject;
0070: import org.netbeans.modules.sql.framework.model.SQLJoinOperator;
0071: import org.netbeans.modules.sql.framework.model.SQLJoinTable;
0072: import org.netbeans.modules.sql.framework.model.SQLJoinView;
0073: import org.netbeans.modules.sql.framework.model.SQLModelObjectFactory;
0074: import org.netbeans.modules.sql.framework.model.SQLObject;
0075: import org.netbeans.modules.sql.framework.model.SQLObjectFactory;
0076: import org.netbeans.modules.sql.framework.model.SQLPredicate;
0077: import org.netbeans.modules.sql.framework.model.SourceColumn;
0078: import org.netbeans.modules.sql.framework.model.SourceTable;
0079: import org.netbeans.modules.sql.framework.model.TargetTable;
0080: import org.netbeans.modules.sql.framework.model.VisibleSQLPredicate;
0081: import org.netbeans.modules.sql.framework.ui.model.CollabSQLUIModel;
0082: import com.sun.sql.framework.exception.BaseException;
0083: import com.sun.sql.framework.utils.Attribute;
0084: import net.java.hulp.i18n.Logger;
0085: import com.sun.sql.framework.utils.StringUtil;
0086: import java.util.HashMap;
0087: import org.netbeans.modules.etl.logger.Localizer;
0088: import org.netbeans.modules.etl.logger.LogUtil;
0089: import org.netbeans.modules.etl.ui.ETLDataObject;
0090: import org.netbeans.modules.etl.ui.model.impl.ETLCollaborationModel;
0091: import org.netbeans.modules.mashup.db.model.impl.FlatfileDBTableImpl;
0092: import org.netbeans.modules.sql.framework.model.DBConnectionDefinition;
0093: import org.netbeans.modules.sql.framework.model.DBTable;
0094: import org.netbeans.modules.sql.framework.model.ForeignKey;
0095: import org.netbeans.modules.sql.framework.model.PrimaryKey;
0096: import org.openide.awt.StatusDisplayer;
0097:
0098: /**
0099: * @author Ritesh Adval
0100: * @author Ahimanikya Satapathy
0101: */
0102: public class SQLObjectUtil {
0103:
0104: public static final String FILE_LOC = "FILE_LOC";
0105: /* Log4J category string */
0106: private static final String LOG_CATEGORY = SQLObjectUtil.class
0107: .getName();
0108: private static transient final Logger mLogger = LogUtil
0109: .getLogger(SQLObjectUtil.class.getName());
0110: private static transient final Localizer mLoc = Localizer.get();
0111:
0112: public static Map<String, String> getTableMetaData(SQLDBTable table)
0113: throws SQLException, BaseException {
0114: SQLDBModel dbModel = (SQLDBModel) table.getParent();
0115: HashMap<String, String> map = new HashMap<String, String>();
0116: Connection conn = null;
0117: Statement stmt = null;
0118: ResultSet rs = null;
0119: try {
0120: if (dbModel.getETLDBConnectionDefinition().getDBType()
0121: .equals(DBMetaDataFactory.AXION)
0122: || dbModel.getETLDBConnectionDefinition()
0123: .getDBType().equalsIgnoreCase("Internal")) {
0124: DBConnectionDefinition conndef = dbModel
0125: .getETLDBConnectionDefinition();
0126: conn = DBExplorerUtil.createConnection(conndef
0127: .getDriverClass(), conndef.getConnectionURL(),
0128: conndef.getUserName(), conndef.getPassword());
0129:
0130: stmt = conn.createStatement();
0131: String query = "select PROPERTY_NAME, PROPERTY_VALUE from AXION_TABLE_PROPERTIES "
0132: + "where TABLE_NAME = '"
0133: + table.getName()
0134: + "' ORDER BY PROPERTY_NAME";
0135: stmt.execute(query);
0136: rs = stmt.getResultSet();
0137: while (rs.next()) {
0138: rs.getMetaData().getColumnCount();
0139: String propKey = rs.getString(1);
0140: String propVal = rs.getString(2);
0141: map.put(propKey, StringUtil
0142: .escapeControlChars(propVal));
0143: }
0144: }
0145: } finally {
0146:
0147: try {
0148: rs.close();
0149: } catch (Exception ex) {
0150: //ignore
0151: }
0152:
0153: try {
0154: stmt.close();
0155: } catch (Exception ex) {
0156: //ignore
0157: }
0158:
0159: try {
0160: conn.close();
0161: } catch (Exception ex) {
0162: //ignore
0163: }
0164: }
0165: return map;
0166: }
0167:
0168: public static void setOrgProperties(SQLDBTable sTable)
0169: throws BaseException {
0170: try {
0171: Map<String, String> propsMap = SQLObjectUtil
0172: .getTableMetaData(sTable);
0173: for (String key : propsMap.keySet()) {
0174: sTable
0175: .setAttribute("ORGPROP_" + key, propsMap
0176: .get(key));
0177: }
0178: } catch (SQLException ex) {
0179: throw new BaseException(ex);
0180: }
0181: }
0182:
0183: public static void createMissingModelTablesInDB(
0184: ETLDataObject dataObj) {
0185: // This is the method that gets called when opening the eTL collab
0186: // for the first time.
0187: ETLCollaborationModel etlCollabModel = dataObj.getModel();
0188: List<SQLDBModel> models = etlCollabModel
0189: .getSourceDatabaseModels();
0190: List<SQLDBModel> targetModels = etlCollabModel
0191: .getTargetDatabaseModels();
0192: models.addAll(targetModels);
0193: // Get the database connection, query all the tables
0194: SQLDBModel aModel = null;
0195: for (int li = 0; li < models.size(); li++) {
0196: aModel = models.get(li);
0197: if (aModel == null) {
0198: //Fix me.
0199: continue;
0200: }
0201: try {
0202: //Check if all the tables from this model are present in the database
0203: checkAndCreateTables(aModel);
0204: } catch (BaseException be) {
0205: be.printStackTrace();
0206: } catch (SQLException sqe) {
0207: StatusDisplayer.getDefault().setStatusText(
0208: "SQLObjectUtil.createMissingModelTablesInDB(): "
0209: + sqe.getMessage());
0210: }
0211: } // end for
0212: }
0213:
0214: private static String[][] getTablesForDBModel(SQLDBModel model)
0215: throws BaseException, SQLException {
0216: String[][] tableList = null;
0217: Connection conn = null;
0218: DBConnectionDefinition conndef = model
0219: .getETLDBConnectionDefinition();
0220:
0221: DBMetaDataFactory dbMeta = new DBMetaDataFactory();
0222: try {
0223: conn = DBExplorerUtil.createConnection(conndef
0224: .getDriverClass(), conndef.getConnectionURL(),
0225: conndef.getUserName(), conndef.getPassword());
0226: String catalog = null;
0227: String[] types = { "TABLE" };
0228:
0229: catalog = (conn.getCatalog() == null) ? "" : conn
0230: .getCatalog();
0231: dbMeta.connectDB(conn);
0232: tableList = dbMeta.getTables(catalog, "", "", types);
0233:
0234: } catch (Exception ex) {
0235: StatusDisplayer.getDefault().setStatusText(
0236: "Failed to get tables for model: "
0237: + model.getDisplayName() + "Reason: "
0238: + ex.getMessage());
0239: return null;
0240: } finally {
0241: dbMeta.disconnectDB();
0242: }
0243: return tableList;
0244: }
0245:
0246: private static void checkAndCreateTables(SQLDBModel aModel)
0247: throws BaseException, SQLException {
0248: //String aString = aModel.getETLDBConnectionDefinition().toString();
0249: //From the connection get all the tables
0250: if (aModel.getETLDBConnectionDefinition().getDBType().equals(
0251: DBMetaDataFactory.AXION)
0252: || aModel.getETLDBConnectionDefinition().getDBType()
0253: .equalsIgnoreCase("Internal")) {
0254:
0255: String[][] tableList = getTablesForDBModel(aModel);
0256: List<DBTable> tables = aModel.getTables();
0257: if (tables == null) {
0258: throw new BaseException(
0259: "No tables in DB Model. Chceck.");
0260: }
0261: if (tableList == null) {
0262: //Recreate all tables from the model.
0263: for (DBTable table : tables) {
0264: createTable((SQLDBTable) table, aModel);
0265: }
0266: } else {
0267: //Create only missing tables
0268: SQLDBTable aTable = null;
0269: for (int i = 0; i < tables.size(); i++) {
0270: aTable = (SQLDBTable) tables.get(i);
0271: String[] currTable;
0272: boolean tableFound = false;
0273: for (int ii = 0; ii < tableList.length; ii++) {
0274: currTable = tableList[ii];
0275: if (aTable.getName().equals(
0276: currTable[DBMetaDataFactory.NAME])) {
0277: tableFound = true;
0278: break;
0279: }
0280: }
0281: if (!tableFound) {
0282: createTable(aTable, aModel);
0283: }
0284: }
0285: }
0286: }
0287: }
0288:
0289: public static void dropTable(SQLDBTable table, SQLDBModel model)
0290: throws BaseException, SQLException {
0291: Connection conn = null;
0292: Statement stmt = null;
0293: DBConnectionDefinition conndef = model
0294: .getETLDBConnectionDefinition();
0295: try {
0296: conn = DBExplorerUtil.createConnection(conndef
0297: .getDriverClass(), conndef.getConnectionURL(),
0298: conndef.getUserName(), conndef.getPassword());
0299: stmt = conn.createStatement();
0300: stmt
0301: .execute(FlatfileDBTableImpl
0302: .getDropStatementSQL(table));
0303: } catch (Exception ex) {
0304: StatusDisplayer.getDefault().setStatusText(
0305: "Failed to drop table: " + table.getName()
0306: + "Reason: " + ex.getMessage());
0307: } finally {
0308: if (conn != null) {
0309: try {
0310: conn.close();
0311: } catch (SQLException ex) {
0312: // ignore
0313: }
0314: }
0315: }
0316: }
0317:
0318: public static void createTable(SQLDBTable aTable, SQLDBModel model)
0319: throws BaseException, SQLException {
0320: Connection conn = null;
0321: Statement stmt = null;
0322: try {
0323: String prefix = "ORGPROP_";
0324: FlatfileDBTableImpl fftbl = new FlatfileDBTableImpl(aTable);
0325: Attribute attr = aTable.getAttribute("ORGPROP_LOADTYPE");
0326:
0327: if (attr != null) {
0328: DBConnectionDefinition conndef = model
0329: .getETLDBConnectionDefinition();
0330: conn = DBExplorerUtil.createConnection(conndef
0331: .getDriverClass(), conndef.getConnectionURL(),
0332: conndef.getUserName(), conndef.getPassword());
0333: stmt = conn.createStatement();
0334:
0335: String parseType = attr.getAttributeValue().toString();
0336: fftbl.setParseType(parseType);
0337: String createSQL = fftbl.getCreateStatementSQL(fftbl);
0338:
0339: StringBuffer sb = new StringBuffer(createSQL);
0340: sb.append(" ORGANIZATION( ");
0341:
0342: Collection<String> attrNames = (Collection<String>) aTable
0343: .getAttributeNames();
0344: for (String attrName : attrNames) {
0345: if (attrName.startsWith(prefix)) {
0346: String keyName = attrName.substring(attrName
0347: .indexOf(prefix)
0348: + prefix.length());
0349: sb.append(keyName + "='");
0350: String propValue = (String) aTable
0351: .getAttribute(attrName)
0352: .getAttributeValue();
0353: sb.append(StringUtil
0354: .escapeControlChars(propValue)
0355: + "' ");
0356: }
0357: }
0358: sb.append(")");
0359:
0360: stmt.execute(sb.toString().trim());
0361: stmt.execute("shutdown");
0362: }
0363:
0364: } catch (Exception ex) {
0365: StatusDisplayer.getDefault().setStatusText(
0366: "Failed to create table: " + aTable.getName()
0367: + "Reason: " + ex.getMessage());
0368: } finally {
0369: try {
0370: if (conn != null) {
0371: conn.close();
0372: }
0373: } catch (Exception ex) {
0374: // ignore
0375: }
0376: }
0377: }
0378:
0379: public static SourceColumn createRuntimeInput(SQLDBTable sTable,
0380: SQLDefinition sqlDefn) throws BaseException {
0381: SQLDBModel dbModel = (SQLDBModel) sTable.getParent();
0382: // set the flatfile location name for this table
0383: sTable
0384: .setFlatFileLocationRuntimeInputName(generateFFRuntimeInputName(
0385: FILE_LOC, sTable));
0386: RuntimeDatabaseModel rtDBModel = getOrCreateRuntimeModel(sqlDefn);
0387: // now use the same name as runtime input argument for file location
0388: String argName = sTable.getFlatFileLocationRuntimeInputName();
0389: RuntimeInput rtInput = rtDBModel.getRuntimeInput();
0390:
0391: if (rtInput == null) {
0392: rtInput = getOrCreateRuntimeInput(rtDBModel);
0393: }
0394: // if runtime input arg does not exist then only add it
0395: if (rtInput.getColumn(argName) == null) {
0396: SourceColumn arg = createRuntimeInputArg(sTable, argName,
0397: dbModel.getETLDBConnectionDefinition());
0398: rtInput.addColumn(arg);
0399: return arg;
0400: }
0401: return null;
0402: }
0403:
0404: public static SourceColumn createRuntimeInputArg(SQLDBTable sTable,
0405: String ffArgName) {
0406: SourceColumn srcColumn = SQLModelObjectFactory.getInstance()
0407: .createSourceColumn(ffArgName, java.sql.Types.VARCHAR,
0408: 0, 0, true);
0409: srcColumn.setEditable(false); // the name is not editable
0410: srcColumn.setVisible(false); // the column is not visible in canvas
0411:
0412: // set default value
0413: FlatfileDBTableImpl flatfileTable = (FlatfileDBTableImpl) (sTable);
0414: if (flatfileTable != null) {
0415: srcColumn.setDefaultValue(flatfileTable.getFileName());
0416: }
0417: return srcColumn;
0418: }
0419:
0420: public static SourceColumn createRuntimeInputArg(SQLDBTable sTable,
0421: String ffArgName, DBConnectionDefinition connDef)
0422: throws BaseException {
0423: SourceColumn srcColumn = SQLModelObjectFactory.getInstance()
0424: .createSourceColumn(ffArgName, java.sql.Types.VARCHAR,
0425: 0, 0, true);
0426: srcColumn.setEditable(false); // the name is not editable
0427: srcColumn.setVisible(false); // the column is not visible in canvas
0428: SQLDBModel dbModel = (SQLDBModel) sTable.getParent();
0429: // set default value if not flatfile
0430: if (dbModel.getETLDBConnectionDefinition().getDBType().equals(
0431: DBMetaDataFactory.AXION)
0432: || dbModel.getETLDBConnectionDefinition().getDBType()
0433: .equalsIgnoreCase("Internal")) {
0434: srcColumn
0435: .setDefaultValue(getFileNameFromDB(sTable, connDef));
0436: } else {
0437: srcColumn.setDefaultValue("RUNTIME_INPUT");
0438: }
0439: return srcColumn;
0440: }
0441:
0442: private static String getFileNameFromDB(SQLDBTable sTable,
0443: DBConnectionDefinition conDef) {
0444: String fileName = "";
0445: Connection conn = null;
0446: try {
0447: conn = DBExplorerUtil.createConnection(conDef
0448: .getConnectionProperties());
0449: Statement stmt = conn.createStatement();
0450: String query = "select PROPERTY_NAME, PROPERTY_VALUE from AXION_TABLE_PROPERTIES "
0451: + "where TABLE_NAME = '" + sTable.getName() + "'";
0452: stmt.execute(query);
0453:
0454: ResultSet rs = stmt.getResultSet();
0455: while (rs.next()) {
0456: if (rs.getString(1).equals("FILENAME")) {
0457: fileName = fileName + rs.getString(2);
0458: break;
0459: }
0460: }
0461: } catch (Exception ex) {
0462: mLogger.errorNoloc(mLoc.t(
0463: "PRSR124: Error while retrieving file name{0}",
0464: SQLDefinition.class.getName()), ex);
0465: } finally {
0466: try {
0467: if (conn != null) {
0468: conn.close();
0469: }
0470: } catch (SQLException ex) {
0471: conn = null;
0472: }
0473: }
0474: return fileName;
0475: }
0476:
0477: public static String generateFFRuntimeInputName(String prefix,
0478: SQLDBTable table) {
0479: String genName = prefix + "_" + table.getUniqueTableName();
0480: mLogger.infoNoloc(mLoc.t("PRSR125: table name{0} for table",
0481: genName, table.getName()));
0482: return genName;
0483: }
0484:
0485: public static String generateTemporaryTableName(String tableName) {
0486: // converting time to hex to take less chars
0487: String sysName = "RAW_"
0488: + Long
0489: .toHexString((long) (java.lang.Math.random() * 100000000))
0490: + "_" + tableName;
0491:
0492: // DB2 cannot accept table names > 18 char
0493: if (sysName.length() > 18) {
0494: sysName = sysName.substring(0, 18);
0495: }
0496:
0497: mLogger.infoNoloc(mLoc.t(
0498: "PRSR126: temp table name{0} for table", sysName,
0499: tableName));
0500: return sysName;
0501: }
0502:
0503: public static String generateTemporaryTableName(String prefix,
0504: String tableName) {
0505: String sysName = prefix
0506: + "_"
0507: + Long
0508: .toHexString((long) (java.lang.Math.random() * 100000000))
0509: + "_" + tableName;
0510:
0511: // DB2 cannot accept table names > 18 char
0512: if (sysName.length() > 18) {
0513: sysName = sysName.substring(0, 18);
0514: }
0515:
0516: mLogger.infoNoloc(mLoc.t(
0517: "PRSR127: temp table name{0} for table", sysName,
0518: tableName));
0519: return sysName;
0520: }
0521:
0522: public static List getAllExpressionObjects(Collection objs) {
0523: ArrayList expObjects = new ArrayList();
0524:
0525: Iterator it = objs.iterator();
0526:
0527: while (it.hasNext()) {
0528: SQLObject obj = (SQLObject) it.next();
0529: if (obj instanceof SQLConnectableObject) {
0530: expObjects.add(obj);
0531: }
0532: }
0533: return expObjects;
0534: }
0535:
0536: /**
0537: * Gets ancestral SQLDefinition instance for the given SQLExpresionObject.
0538: *
0539: * @param sqlObj SQLConnectableObject whose ancestor SQLDefinition is sought
0540: * @return SQLDefinition instance
0541: */
0542: public static SQLDefinition getAncestralSQLDefinition(
0543: SQLObject sqlObj) {
0544: SQLDefinition defn = null;
0545: SQLObject loopObj = sqlObj;
0546: Object parentObject = loopObj.getParentObject();
0547:
0548: do {
0549: if (parentObject instanceof SQLDefinition) {
0550: defn = (SQLDefinition) parentObject;
0551: break;
0552: } else if (parentObject instanceof SQLContainerObject) {
0553: parentObject = ((SQLContainerObject) parentObject)
0554: .getParent();
0555: } else if (parentObject == null) {
0556: return null;
0557: } else if (parentObject instanceof SQLObject) {
0558: parentObject = ((SQLObject) parentObject)
0559: .getParentObject();
0560: }
0561: } while (true);
0562:
0563: return defn;
0564: }
0565:
0566: /**
0567: * Creates a List of automatically generated join objects (if any) between the given
0568: * and existing s in the model. Any new instances will need to be added to this
0569: * SQLDefinition, as well as their contained SQLPredicate conditions.
0570: *
0571: * @param right SourceTable against which we check for join relationships with
0572: * existing SourceTables
0573: * @lookupCollection collection where to lookup the join
0574: * @return List of Join objects, empty if no relationships were detected.
0575: * @throws BaseException for any internal (unspecific) error.
0576: */
0577: public static List getAutoJoins(SQLJoinTable right,
0578: Collection lookupCollection) throws BaseException {
0579: if (right == null) {
0580: throw new BaseException(
0581: "Must supply non-null ref for param 'right'.");
0582: }
0583:
0584: List joinList = Collections.EMPTY_LIST;
0585: Iterator iter = lookupCollection.iterator();
0586: if (iter.hasNext()) {
0587: joinList = new ArrayList();
0588: do {
0589: SQLJoinTable left = (SQLJoinTable) iter.next();
0590: if (left == right) {
0591: continue;
0592: }
0593:
0594: SQLJoinOperator newJoin = createAutoJoin(left, right);
0595: if (newJoin != null) {
0596: joinList.add(newJoin);
0597: }
0598: } while (iter.hasNext());
0599: }
0600:
0601: return joinList;
0602: }
0603:
0604: public static SQLConnectableObject getExpressionObject(
0605: SQLObject sqlObject, Collection allObjects) {
0606: Iterator it = allObjects.iterator();
0607: while (it.hasNext()) {
0608: SQLObject obj = (SQLObject) it.next();
0609: if (obj instanceof SQLConnectableObject) {
0610: SQLConnectableObject expObj = (SQLConnectableObject) obj;
0611: Iterator eIt = expObj.getInputObjectMap().values()
0612: .iterator();
0613: while (eIt.hasNext()) {
0614: SQLInputObject inObj = (SQLInputObject) eIt.next();
0615: SQLObject sqlObj = inObj.getSQLObject();
0616:
0617: if (sqlObj != null && sqlObj.equals(sqlObject)) {
0618: return expObj;
0619: }
0620: }
0621: }
0622: }
0623: return null;
0624: }
0625:
0626: /**
0627: * get source table connected to this sql object
0628: */
0629: public static SourceTable getInputSourceTable(SQLObject obj) {
0630: if (obj == null) {
0631: return null;
0632: }
0633:
0634: if (obj.getObjectType() == SQLConstants.SOURCE_TABLE) {
0635: return (SourceTable) obj;
0636: }
0637:
0638: if (obj.getObjectType() == SQLConstants.SOURCE_COLUMN) {
0639: SourceColumn column = (SourceColumn) obj;
0640: return getInputSourceTable((SourceTable) column.getParent());
0641: }
0642:
0643: if (obj instanceof SQLConnectableObject) {
0644: SQLConnectableObject expObj = (SQLConnectableObject) obj;
0645:
0646: Iterator it = expObj.getInputObjectMap().values()
0647: .iterator();
0648: while (it.hasNext()) {
0649: SQLInputObject inObj = (SQLInputObject) it.next();
0650: SQLObject sqlObj = inObj.getSQLObject();
0651: SourceTable table = getInputSourceTable(sqlObj);
0652: if (table != null) {
0653: return table;
0654: }
0655: }
0656:
0657: List children = expObj.getChildSQLObjects();
0658: Iterator cIt = children.iterator();
0659: while (cIt.hasNext()) {
0660: SQLObject chObj = (SQLObject) cIt.next();
0661:
0662: SourceTable table = getInputSourceTable(chObj);
0663: if (table != null) {
0664: return table;
0665: }
0666: }
0667: }
0668: return null;
0669: }
0670:
0671: /**
0672: * get mapped target table for a source table
0673: */
0674: public static SourceTable getInputSourceTable(SQLObject obj,
0675: Collection allObjects) {
0676: SQLConnectableObject topExpObj = getTopExpressionObject(obj,
0677: allObjects);
0678: if (topExpObj != null) {
0679: return getInputSourceTable(topExpObj);
0680: }
0681: return getInputSourceTable(obj);
0682: }
0683:
0684: /**
0685: * get mapped target table for a source table
0686: */
0687: public static TargetTable getMappedTargetTable(SQLJoinView jv,
0688: Collection targetTables) {
0689: if (jv == null) {
0690: return null;
0691: }
0692:
0693: Iterator it = targetTables.iterator();
0694: while (it.hasNext()) {
0695: TargetTable tt = (TargetTable) it.next();
0696: if (jv.equals(tt.getJoinView())) {
0697: return tt;
0698: }
0699: }
0700: return null;
0701: }
0702:
0703: /**
0704: * get mapped target table for a source table
0705: */
0706: public static TargetTable getMappedTargetTable(SQLObject sqlObj,
0707: Collection targetTables) {
0708: if (sqlObj.getObjectType() == SQLConstants.TARGET_TABLE) {
0709: return (TargetTable) sqlObj;
0710: }
0711:
0712: Iterator it = targetTables.iterator();
0713:
0714: while (it.hasNext()) {
0715: TargetTable tt = (TargetTable) it.next();
0716: if (isSQLObjectMappedToTarget(sqlObj, tt)) {
0717: return tt;
0718: }
0719: }
0720: return null;
0721: }
0722:
0723: /**
0724: * Gets RuntimeInput, if any, associated with the parent SQLDefinition of the given
0725: * SQLDBTable.
0726: *
0727: * @param table SQLDBTable whose parent's RuntimeInput is to be obtained
0728: * @return RuntimeInput associated with the parent of <i>table </i>, or null if no
0729: * such instance exists.
0730: */
0731: public static RuntimeInput getRuntimeInput(SQLDBTable table) {
0732: SQLDefinition sqlDefinition = getAncestralSQLDefinition(table);
0733: if (sqlDefinition != null) {
0734: RuntimeDatabaseModel runModel = sqlDefinition
0735: .getRuntimeDbModel();
0736: if (runModel != null) {
0737: RuntimeInput rInput = runModel.getRuntimeInput();
0738: return rInput;
0739: }
0740: }
0741: return null;
0742: }
0743:
0744: /**
0745: * Generates runtime output name to be used in referencing the count of rows inserted
0746: * or updated for the given TargetTable.
0747: *
0748: * @param targetTable TargetTable whose count of rows will be referenced by the
0749: * generated runtime output
0750: * @return String representing runtime output name for count of rows processed in
0751: * <code>targetTable</code>
0752: */
0753: public static String getTargetTableCountRuntimeOutput(
0754: TargetTable targetTable) {
0755: return "Count_" + targetTable.getUniqueTableName(); // NOI18N
0756: }
0757:
0758: /*
0759: * get the expression object whose input is given sql object
0760: */
0761: // since currently we do not keep the reference to output object to which
0762: // a given input object is connected we need to do this logic to do that.
0763: public static SQLConnectableObject getTopExpressionObject(
0764: SQLObject sqlObject, Collection allObjects) {
0765: SQLConnectableObject topExpObj = null;
0766: SQLConnectableObject tmpObj = null;
0767: topExpObj = getExpressionObject(sqlObject, allObjects);
0768: tmpObj = topExpObj;
0769:
0770: while (tmpObj != null) {
0771: tmpObj = getExpressionObject(tmpObj, allObjects);
0772: if (tmpObj != null) {
0773: topExpObj = tmpObj;
0774: }
0775: }
0776:
0777: return topExpObj;
0778: }
0779:
0780: public static SQLCanvasObject getTopSQLCanvasObject(SQLObject sqlObj) {
0781: if (sqlObj instanceof SQLCanvasObject) {
0782: return (SQLCanvasObject) sqlObj;
0783: }
0784:
0785: Object parentObj = sqlObj.getParentObject();
0786: while (parentObj != null && parentObj instanceof SQLObject
0787: && !(parentObj instanceof SQLCanvasObject)) {
0788: parentObj = ((SQLObject) parentObj).getParentObject();
0789: }
0790:
0791: if (parentObj instanceof SQLCanvasObject) {
0792: return (SQLCanvasObject) parentObj;
0793: }
0794:
0795: return null;
0796: }
0797:
0798: /**
0799: * get source table connected to this sql object
0800: */
0801: public static boolean isAggregateFunctionMapped(SQLObject obj) {
0802: if (obj == null) {
0803: return false;
0804: }
0805:
0806: if (obj instanceof SQLGenericOperator) {
0807: return ((SQLGenericOperator) obj).isAggregateFunction();
0808: }
0809:
0810: if (obj instanceof SQLConnectableObject) {
0811: SQLConnectableObject expObj = (SQLConnectableObject) obj;
0812:
0813: Iterator it = expObj.getInputObjectMap().values()
0814: .iterator();
0815: while (it.hasNext()) {
0816: SQLInputObject inObj = (SQLInputObject) it.next();
0817: SQLObject sqlObj = inObj.getSQLObject();
0818: if (isAggregateFunctionMapped(sqlObj)) {
0819: return true;
0820: }
0821: }
0822:
0823: List children = expObj.getChildSQLObjects();
0824: Iterator cIt = children.iterator();
0825: while (cIt.hasNext()) {
0826: SQLObject chObj = (SQLObject) cIt.next();
0827: if (isAggregateFunctionMapped(chObj)) {
0828: return true;
0829: }
0830: }
0831: }
0832: return false;
0833: }
0834:
0835: public static boolean isObjectMappedToExpression(SQLObject obj,
0836: SQLConnectableObject expObj) {
0837: boolean response = false;
0838:
0839: Iterator it = expObj.getInputObjectMap().values().iterator();
0840: while (it.hasNext()) {
0841: SQLInputObject inObj = (SQLInputObject) it.next();
0842: SQLObject sqlObj = inObj.getSQLObject();
0843:
0844: if (sqlObj != null && sqlObj.equals(obj)) {
0845: response = true;
0846: break;
0847: }
0848: }
0849:
0850: return response;
0851: }
0852:
0853: public static void migrateJoinCondition(SQLPredicate predicate,
0854: SQLCondition condition) throws BaseException {
0855: migrateConnectableObject(predicate, condition);
0856: condition.addObject(predicate); // add predicate to SQLCondition
0857: }
0858:
0859: public static SourceColumn removeRuntimeInput(SQLDBTable sTable,
0860: CollabSQLUIModel sqlModel) throws BaseException {
0861: SQLDefinition sqlDefn = sqlModel.getSQLDefinition();
0862: // get the file location arg name
0863: String fArgName = sTable.getFlatFileLocationRuntimeInputName();
0864: RuntimeDatabaseModel rtDBModel = getOrCreateRuntimeModel(sqlDefn);
0865:
0866: RuntimeInput rtInput = rtDBModel.getRuntimeInput();
0867: if (rtInput != null && fArgName != null) {
0868: SourceColumn arg = (SourceColumn) rtInput
0869: .getColumn(fArgName);
0870: if (arg != null) {
0871: rtInput.deleteColumn(fArgName);
0872: return arg;
0873: }
0874: }
0875:
0876: return null;
0877: }
0878:
0879: private static SQLJoinOperator createAutoJoin(SQLJoinTable one,
0880: SQLJoinTable two) {
0881: return discoverJoinPredicate(one, two);
0882: }
0883:
0884: private static SQLJoinOperator discoverJoinPredicate(
0885: SQLJoinTable one, SQLJoinTable two) {
0886: SourceTable oneS = one.getSourceTable();
0887: SourceTable twoS = two.getSourceTable();
0888: String name = "Join-" + oneS.getName() + "-" + twoS.getName();
0889:
0890: PrimaryKey pk = null;
0891: SQLJoinTable left = null;
0892: SourceColumn leftColumn;
0893:
0894: ForeignKey fk = null;
0895: SQLJoinTable right = null;
0896: SourceColumn rightColumn;
0897:
0898: SQLJoinOperator jmd = null;
0899:
0900: if (oneS.references(twoS)) {
0901: pk = twoS.getPrimaryKey();
0902: fk = oneS.getReferenceFor(twoS);
0903: left = two;
0904: right = one;
0905: } else if (twoS.references(oneS)) {
0906: pk = oneS.getPrimaryKey();
0907: fk = twoS.getReferenceFor(oneS);
0908: left = one;
0909: right = two;
0910:
0911: }
0912:
0913: if (left != null && right != null && pk != null && fk != null) {
0914: // TODO support composite keys
0915: // Just get first column for now - joins and predicates don't yet.
0916: leftColumn = (SourceColumn) left.getSourceTable()
0917: .getColumn((String) pk.getColumnNames().get(0));
0918: rightColumn = (SourceColumn) right.getSourceTable()
0919: .getColumn((String) fk.getColumnNames().get(0));
0920:
0921: try {
0922: jmd = (SQLJoinOperator) SQLObjectFactory
0923: .createObjectForTag(SQLConstants.STR_JOIN_OPERATOR);
0924: jmd.setDisplayName(name);
0925: jmd.setJoinType(SQLConstants.INNER_JOIN);
0926: jmd
0927: .setJoinConditionType(SQLJoinOperator.SYSTEM_DEFINED_CONDITION);
0928:
0929: SQLCondition condition = SQLModelObjectFactory
0930: .getInstance().createSQLCondition(
0931: SQLJoinOperator.JOIN_CONDITION);
0932:
0933: VisibleSQLPredicate joinPredicate = (VisibleSQLPredicate) SQLObjectFactory
0934: .createObjectForTag(SQLConstants.STR_VISIBLE_PREDICATE);
0935: joinPredicate.setDisplayName(oneS.getName() + "-"
0936: + twoS.getName());
0937: joinPredicate.setOperatorType("=");
0938:
0939: ColumnRef leftColumnRef = SQLModelObjectFactory
0940: .getInstance().createColumnRef(leftColumn);
0941: condition.addObject(leftColumnRef);
0942:
0943: ColumnRef rightColumnRef = SQLModelObjectFactory
0944: .getInstance().createColumnRef(rightColumn);
0945: condition.addObject(rightColumnRef);
0946:
0947: joinPredicate
0948: .addInput(SQLPredicate.LEFT, leftColumnRef);
0949: joinPredicate.addInput(SQLPredicate.RIGHT,
0950: rightColumnRef);
0951: joinPredicate.setRoot(null);
0952:
0953: condition.addObject(joinPredicate);
0954:
0955: // Make sure condition object state is stable.
0956: condition.setGuiMode(SQLCondition.GUIMODE_GRAPHICAL);
0957: condition.getRootPredicate();
0958:
0959: jmd.setJoinCondition(condition);
0960:
0961: jmd.addInput(SQLJoinOperator.LEFT, left);
0962: jmd.addInput(SQLJoinOperator.RIGHT, right);
0963: } catch (BaseException sqlEx) {
0964: mLogger.errorNoloc(mLoc.t(
0965: "PRSR128: Failed to create auto-join{0}",
0966: LOG_CATEGORY), sqlEx);
0967:
0968: jmd = null;
0969: }
0970: }
0971:
0972: return jmd;
0973: }
0974:
0975: private static RuntimeInput getOrCreateRuntimeInput(
0976: RuntimeDatabaseModel rtDBModel) throws BaseException {
0977: RuntimeInput rtInput = rtDBModel.getRuntimeInput();
0978: // do nothing, just return
0979: if (rtInput != null) {
0980: return rtInput;
0981: }
0982:
0983: rtInput = SQLModelObjectFactory.getInstance()
0984: .createRuntimeInput();
0985: rtInput.setName("RuntimeInput"); // NOI18N
0986:
0987: return rtInput;
0988: }
0989:
0990: private static RuntimeDatabaseModel getOrCreateRuntimeModel(
0991: SQLDefinition sqlDefn) throws BaseException {
0992: if (sqlDefn != null) {
0993: RuntimeDatabaseModel rtDBModel = sqlDefn
0994: .getRuntimeDbModel();
0995: // If RuntimeDBModel exists
0996: if (rtDBModel != null) {
0997: return rtDBModel;
0998: }
0999: rtDBModel = SQLModelObjectFactory.getInstance()
1000: .createRuntimeDatabaseModel();
1001: sqlDefn.addObject(rtDBModel);
1002: return rtDBModel;
1003: }
1004:
1005: return null;
1006: }
1007:
1008: private static boolean isSQLObjectMappedToTarget(
1009: SQLObject sqlObject, SQLConnectableObject expObj) {
1010: Iterator it = expObj.getInputObjectMap().values().iterator();
1011: while (it.hasNext()) {
1012: SQLInputObject inObj = (SQLInputObject) it.next();
1013: SQLObject sqlObj = inObj.getSQLObject();
1014:
1015: if (sqlObj != null) {
1016: if (sqlObj.equals(sqlObject)) {
1017: return true;
1018: } else if (sqlObject.getObjectType() == SQLConstants.SOURCE_TABLE
1019: && sqlObj.getObjectType() == SQLConstants.SOURCE_COLUMN) {
1020: SourceColumn sColumn = (SourceColumn) sqlObj;
1021: if (sqlObject.equals(sColumn.getParent())) {
1022: return true;
1023: }
1024: } else if (sqlObj instanceof SQLConnectableObject) {
1025: SQLConnectableObject linedExpObj = (SQLConnectableObject) sqlObj;
1026: if (isSQLObjectMappedToTarget(sqlObject,
1027: linedExpObj)) {
1028: return true;
1029: }
1030: }
1031: }
1032: }
1033:
1034: List children = expObj.getChildSQLObjects();
1035: Iterator cIt = children.iterator();
1036: while (cIt.hasNext()) {
1037: SQLObject chObj = (SQLObject) cIt.next();
1038: if (chObj != null) {
1039: if (chObj instanceof SQLConnectableObject) {
1040: if (isSQLObjectMappedToTarget(sqlObject,
1041: (SQLConnectableObject) chObj)) {
1042: return true;
1043: }
1044: } else if (sqlObject.getObjectType() == SQLConstants.SOURCE_TABLE
1045: && chObj.getObjectType() == SQLConstants.SOURCE_COLUMN) {
1046: SourceColumn sColumn = (SourceColumn) chObj;
1047: if (sqlObject.equals(sColumn.getParent())) {
1048: return true;
1049: }
1050: } else if (chObj.equals(sqlObject)) {
1051: return true;
1052: }
1053: }
1054: }
1055:
1056: return false;
1057: }
1058:
1059: private static void migrateConnectableObject(
1060: SQLConnectableObject expObj, SQLCondition condition)
1061: throws BaseException {
1062: expObj.reset(); // reset the predicate id and add it to jon view
1063:
1064: Map inputs = expObj.getInputObjectMap();
1065: Iterator it = inputs.values().iterator();
1066: while (it.hasNext()) {
1067: SQLInputObject objIn = (SQLInputObject) it.next();
1068: SQLObject obj = objIn.getSQLObject();
1069: if (obj != null) {
1070: if (obj.getObjectType() == SQLConstants.SOURCE_COLUMN) {
1071: ColumnRef columnRef = SQLModelObjectFactory
1072: .getInstance().createColumnRef(
1073: (SQLDBColumn) obj);
1074: condition.addObject(columnRef);
1075: objIn.setSQLObject(columnRef);
1076: } else {
1077: obj.reset();
1078: if (obj instanceof SQLConnectableObject) {
1079: migrateConnectableObject(
1080: (SQLConnectableObject) obj, condition);
1081: }
1082: condition.addObject(obj);
1083: }
1084: }
1085: }
1086: }
1087: }
|