0001: /*
0002: * JFolder, Copyright 2001-2006 Gary Steinmetz
0003: *
0004: * Distributable under LGPL license.
0005: * See terms of license at gnu.org.
0006: */
0007:
0008: package org.jfolder.platforms.stores.instances.mysql;
0009:
0010: //base classes
0011: import java.math.BigDecimal;
0012: import java.sql.Connection;
0013: import java.sql.PreparedStatement;
0014: import java.sql.ResultSet;
0015: import java.sql.SQLException;
0016: import java.sql.Statement;
0017: import java.sql.Types;
0018: import java.util.ArrayList;
0019:
0020: //project specific classes
0021: import org.jfolder.common.UnexpectedSystemException;
0022: import org.jfolder.common.utils.misc.MiscHelper;
0023: import org.jfolder.platforms.stores.base.CreateStatement;
0024: import org.jfolder.platforms.stores.base.DeleteStatement;
0025: import org.jfolder.platforms.stores.base.DropStatement;
0026: import org.jfolder.platforms.stores.base.InsertStatement;
0027: import org.jfolder.platforms.stores.base.SelectStatement;
0028: import org.jfolder.platforms.stores.base.SequenceStatement;
0029: import org.jfolder.platforms.stores.base.SystemDatabase;
0030: import org.jfolder.platforms.stores.base.SystemResultSet;
0031: import org.jfolder.platforms.stores.base.SystemStatement;
0032: import org.jfolder.platforms.stores.base.SystemStore;
0033: import org.jfolder.platforms.stores.base.UpdateStatement;
0034: import org.jfolder.platforms.stores.base.WhereStatement;
0035:
0036: //other classes
0037:
0038: public class MySQLStore extends SystemStore {
0039:
0040: private final static String TYPE__INNO_DB = " Type=InnoDB";
0041:
0042: //
0043: private final static String ID_PK = "ID_PK";
0044: private final static String SERIES_SEQUENCE = "SERIES_SEQUENCE";
0045: //
0046: private final static String PARAM = "?";
0047: private final static String NULL = "null";
0048:
0049: //
0050: private final static String TYPE__DECIMAL = "DECIMAL(30,12)";
0051: private final static String TYPE__BOOLEAN = "BOOLEAN";
0052: private final static String TYPE__LSTRING = "LONGTEXT";
0053: private final static String TYPE__SSTRING = "TEXT("
0054: + SystemDatabase.MAXIMUM_SHORT_STRING_LENGTH + ")";
0055: private final static String TYPE__REFERENCE = "INT";
0056: private final static String TYPE__BOBJECT = "LONGBLOB";
0057:
0058: //
0059: private int constraintCount = 0;
0060:
0061: //
0062: public MySQLStore() {
0063: }
0064:
0065: public static String getDisplayName() {
0066: return "MySQL";
0067: }
0068:
0069: public void prepareConnection(Connection inConn)
0070: throws SQLException {
0071:
0072: Statement s = null;
0073:
0074: try {
0075: inConn.setAutoCommit(false);
0076: s = inConn.createStatement();
0077: s.execute("START TRANSACTION");
0078: } finally {
0079: closeSQLObjects(null, s, null);
0080: }
0081: //inConn.setAutoCommit(false);
0082: }
0083:
0084: public void execute(SystemStatement inWs, Connection inConn) {
0085:
0086: try {
0087: this .constraintCount = 0;
0088:
0089: if (inWs instanceof CreateStatement) {
0090: //
0091: //SELECT NEXT VALUE FOR mysequence, col1, col2 FROM mytable
0092: CreateStatement wcs = (CreateStatement) inWs;
0093: processCreateStatement(wcs, inConn);
0094: } else if (inWs instanceof DropStatement) {
0095: //
0096: //SELECT NEXT VALUE FOR mysequence, col1, col2 FROM mytable
0097: DropStatement wds = (DropStatement) inWs;
0098: processDropStatement(wds, inConn);
0099: } else if (inWs instanceof InsertStatement) {
0100: //
0101: InsertStatement wis = (InsertStatement) inWs;
0102: processInsertStatement(wis, inConn);
0103: } else if (inWs instanceof SelectStatement) {
0104: //
0105: SelectStatement wss = (SelectStatement) inWs;
0106: wss.setResults(processSelectStatement(wss, inConn));
0107: } else if (inWs instanceof DeleteStatement) {
0108: //
0109: DeleteStatement wds = (DeleteStatement) inWs;
0110: processDeleteStatement(wds, inConn);
0111: } else if (inWs instanceof UpdateStatement) {
0112: //
0113: UpdateStatement wus = (UpdateStatement) inWs;
0114: processUpdateStatement(wus, inConn);
0115: } else {
0116: throw UnexpectedSystemException.notImplemented();
0117: }
0118: } catch (SQLException sqle) {
0119: throw new UnexpectedSystemException(sqle);
0120: } finally {
0121: this .constraintCount = 0;
0122: }
0123: }
0124:
0125: public void commit(Connection inConn) {
0126: //TO DO: try to determine how to set autocommit=false through prop file
0127: PreparedStatement ps = null;
0128: Statement s = null;
0129:
0130: try {
0131: s = inConn.createStatement();
0132: s.execute("COMMIT");
0133: } catch (SQLException sqle) {
0134: throw new UnexpectedSystemException(sqle);
0135: } finally {
0136: closeSQLObjects(null, s, null);
0137: }
0138: //try {
0139: // inConn.commit();
0140: //// ps = inConn.prepareStatement("COMMIT");
0141: //// ps.execute();
0142: //}
0143: //catch (SQLException sqle) {
0144: // throw new UnexpectedSystemException(sqle);
0145: //}
0146: //finally {
0147: // closeSQLObjects(null, ps, null);
0148: //}
0149: }
0150:
0151: protected void prepareParameters(PreparedStatement inPs,
0152: ArrayList inTypes, ArrayList inValues) throws SQLException {
0153:
0154: if (inTypes != null && inValues != null) {
0155: if (inTypes.size() == inValues.size()) {
0156: for (int i = 0; i < inTypes.size(); i++) {
0157: //
0158: Integer nextType = (Integer) inTypes.get(i);
0159: Object nextValue = inValues.get(i);
0160: //
0161: //MiscHelper.println("HSQLStore nextType = " + nextType);
0162: //MiscHelper.println("HSQLStore nextValue = " + nextValue);
0163: //
0164: if (nextType.equals(SystemStatement.DECIMAL)) {
0165: inPs.setBigDecimal((i + 1),
0166: (BigDecimal) nextValue);
0167: } else if (nextType
0168: .equals(SystemStatement.SHORT_STRING)) {
0169: inPs.setString((i + 1), (String) nextValue);
0170: } else if (nextType
0171: .equals(SystemStatement.LONG_STRING)) {
0172: inPs.setString((i + 1), (String) nextValue);
0173: } else if (nextType.equals(SystemStatement.BOOLEAN)) {
0174: Boolean nextBoolean = (Boolean) nextValue;
0175: if (nextBoolean != null) {
0176: inPs.setBoolean((i + 1), nextBoolean
0177: .booleanValue());
0178: } else {
0179: inPs.setNull((i + 1), Types.BOOLEAN);
0180: }
0181: } else if (nextType
0182: .equals(SystemStatement.BINARY_OBJECT)) {
0183: inPs.setBytes((i + 1), (byte[]) nextValue);
0184: } else {
0185: throw UnexpectedSystemException.unknownState();
0186: }
0187: }
0188: } else {
0189: throw UnexpectedSystemException.unknownState();
0190: }
0191: }
0192: }
0193:
0194: //
0195: private void processCreateStatement(CreateStatement inWcs,
0196: Connection inConn) throws SQLException {
0197:
0198: //
0199: if (inWcs.isSeriesSequenceColumnPresent()) {
0200: //
0201: startStatement();
0202:
0203: printAndIndent("CREATE TABLE "
0204: + getActualSeriesSequenceName(inWcs.getName())
0205: + " (");
0206: simpleAndPrint(ID_PK + " " + TYPE__REFERENCE
0207: + " AUTO_INCREMENT PRIMARY KEY");
0208: revertAndPrint(")" + TYPE__INNO_DB);
0209: //simpleAndPrint("");
0210:
0211: endStatement(inConn, null, null, null, null, false);
0212: }
0213: //
0214: startStatement();
0215: //
0216: printAndIndent("CREATE TABLE "
0217: + getActualTableName(inWcs.getName()) + " (");
0218: //
0219: ArrayList subStatements = new ArrayList();
0220: //
0221: if (inWcs.isIdColumnPresent()) {
0222: subStatements.add(ID_PK + " " + TYPE__REFERENCE
0223: + " AUTO_INCREMENT PRIMARY KEY");
0224: }
0225: //
0226: if (inWcs.isSeriesSequenceColumnPresent()) {
0227: subStatements.add(SERIES_SEQUENCE + " " + TYPE__DECIMAL
0228: + " NOT NULL");
0229: }
0230: //
0231: for (int i = 0; i < inWcs.getColumnCount(); i++) {
0232: String nextColumnName = inWcs.getColumnName(i);
0233: boolean nextColumnNull = inWcs.getColumnNull(i);
0234: Integer nextColumnDataTypeCode = inWcs.getColumnDataType(i);
0235: String nextForeignTable = inWcs.getColumnForeignKey(i);
0236:
0237: //
0238: String nextColumnType = null;
0239: if (nextForeignTable != null) {
0240: nextColumnType = TYPE__REFERENCE;
0241: } else if (nextColumnDataTypeCode
0242: .equals(SystemStatement.DECIMAL)) {
0243: nextColumnType = TYPE__DECIMAL;
0244: } else if (nextColumnDataTypeCode
0245: .equals(SystemStatement.SHORT_STRING)) {
0246: nextColumnType = TYPE__SSTRING;
0247: } else if (nextColumnDataTypeCode
0248: .equals(SystemStatement.LONG_STRING)) {
0249: nextColumnType = TYPE__LSTRING;
0250: } else if (nextColumnDataTypeCode
0251: .equals(SystemStatement.BOOLEAN)) {
0252: nextColumnType = TYPE__BOOLEAN;
0253: } else if (nextColumnDataTypeCode
0254: .equals(SystemStatement.BINARY_OBJECT)) {
0255: nextColumnType = TYPE__BOBJECT;
0256: }
0257: //else if (nextColumnTypeCode.equals(
0258: // WorkflowStatement.BINARY_STREAM)) {
0259: //}
0260: else {
0261: throw UnexpectedSystemException.unknownState();
0262: }
0263:
0264: //
0265: if (!nextColumnNull) {
0266: subStatements.add(nextColumnName + " " + nextColumnType
0267: + " NOT NULL");
0268: } else {
0269: subStatements
0270: .add(nextColumnName + " " + nextColumnType);
0271: }
0272: }
0273: //
0274: for (int i = 0; i < inWcs.getColumnCount(); i++) {
0275: String nextColumnName = inWcs.getColumnName(i);
0276: String nextForeignTable = inWcs.getColumnForeignKey(i);
0277: //
0278: if (nextForeignTable != null) {
0279: subStatements.add("CONSTRAINT "
0280: + getConstraintName(inWcs.getName())
0281: + " FOREIGN KEY (" + nextColumnName + ")"
0282: + " REFERENCES "
0283: + getActualTableName(nextForeignTable) + " ("
0284: + ID_PK + ")");
0285: }
0286: }
0287: //
0288: for (int i = 0; i < inWcs.getUniqueConstraintCount(); i++) {
0289: ArrayList nextUniqueList = new ArrayList(inWcs
0290: .getUniqueConstraint(i));
0291: for (int j = 0; j < nextUniqueList.size(); j++) {
0292: //
0293: Integer nextUniqueColumnType = inWcs
0294: .getColumnDataType(((String) nextUniqueList
0295: .get(j)));
0296: if (SystemStatement.SHORT_STRING
0297: .equals(nextUniqueColumnType)) {
0298: String nextUniqueColumnName = ((String) nextUniqueList
0299: .get(j));
0300: nextUniqueList.remove(j);
0301: nextUniqueList
0302: .add(
0303: j,
0304: nextUniqueColumnName
0305: + "("
0306: + SystemDatabase.MAXIMUM_SHORT_STRING_LENGTH
0307: + ")");
0308: }
0309: }
0310: //
0311: subStatements.add("CONSTRAINT "
0312: + getConstraintName(inWcs.getName()) + " UNIQUE ("
0313: + convertToList(nextUniqueList) + ")");
0314: }
0315: //
0316: if (inWcs.isSeriesSequenceColumnPresent()) {
0317: subStatements.add("CONSTRAINT "
0318: + getConstraintName(inWcs.getName()) + " UNIQUE ("
0319: + SERIES_SEQUENCE + ")");
0320: }
0321: //
0322: for (int i = 0; i < subStatements.size(); i++) {
0323: if (i < (subStatements.size() - 1)) {
0324: simpleAndPrint(subStatements.get(i).toString() + ",");
0325: } else {
0326: simpleAndPrint(subStatements.get(i).toString());
0327: }
0328: }
0329: //
0330: revertAndPrint(")" + TYPE__INNO_DB);
0331: //
0332: endStatement(inConn, null, null, null, null, false);
0333: }
0334:
0335: //
0336: private void processDropStatement(DropStatement inWds,
0337: Connection inConn) throws SQLException {
0338:
0339: CreateStatement wcs = inWds.getEntity();
0340:
0341: SQLException previousSqle = null;
0342:
0343: //
0344: try {
0345: startStatement();
0346: simpleAndPrint("DROP TABLE "
0347: + getActualTableName(wcs.getName()));
0348: endStatement(inConn, null, null, null, null, false);
0349: } catch (SQLException sqle) {
0350: previousSqle = sqle;
0351: }
0352:
0353: //
0354: if (wcs.isSeriesSequenceColumnPresent()) {
0355: startStatement();
0356: simpleAndPrint("DROP TABLE "
0357: + getActualSeriesSequenceName(wcs.getName()));
0358: endStatement(inConn, null, null, null, null, false);
0359: }
0360:
0361: //
0362: if (previousSqle != null) {
0363: throw previousSqle;
0364: }
0365: }
0366:
0367: //
0368: private void processInsertStatement(InsertStatement inWis,
0369: Connection inConn) throws SQLException {
0370:
0371: CreateStatement wcs = inWis.getEntity();
0372:
0373: ArrayList columnNames = new ArrayList();
0374: ArrayList columnTypes = new ArrayList();
0375: ArrayList columnParams = new ArrayList();
0376: ArrayList columnValues = new ArrayList();
0377:
0378: if (wcs.isSeriesSequenceColumnPresent()) {
0379: //
0380: BigDecimal seriesSequence = getNextSeriesSequence(wcs
0381: .getName(), inConn);
0382: inWis.setSeriesSequenceColumnValue(seriesSequence);
0383: //
0384: columnNames.add(SERIES_SEQUENCE);
0385: columnTypes.add(SystemStatement.DECIMAL);
0386: columnParams.add(PARAM);
0387: columnValues.add(seriesSequence);
0388: }
0389:
0390: for (int i = 0; i < inWis.getColumnCount(); i++) {
0391: columnNames.add(inWis.getColumnName(i));
0392: columnTypes.add(inWis.getColumnType(i));
0393: columnParams.add(PARAM);
0394: columnValues.add(inWis.getColumnValue(i));
0395: }
0396:
0397: PreparedStatement ps[] = new PreparedStatement[1];
0398: startStatement();
0399: simpleAndPrint("INSERT INTO "
0400: + getActualTableName(wcs.getName()) + " ("
0401: + convertToList(columnNames) + ") VALUES ("
0402: + convertToList(columnParams) + ")");
0403: endStatement(inConn, columnTypes, columnValues, ps, null, false);
0404: //endStatement(inConn, columnTypes, columnValues, null, null, false);
0405:
0406: //
0407: //Statement s = null;
0408:
0409: if (inWis.getEntity().isIdColumnPresent()) {
0410: //Statement s = null;
0411: ResultSet rs = null;
0412: try {
0413: //s = inConn.createStatement();
0414: //rs = s.executeQuery("SELECT LAST_INSERT_ID()");
0415: rs = (ps[0]).getGeneratedKeys();
0416: if (rs.next()) {
0417: inWis
0418: .setIdColumnValue(new BigDecimal(rs
0419: .getInt(1)));
0420: } else {
0421: throw UnexpectedSystemException.unknownState();
0422: }
0423: } finally {
0424: //s.close();
0425: closeSQLObjects(null, ps[0], rs);
0426: //closeSQLObjects(null, s, rs);
0427: }
0428: }
0429: }
0430:
0431: //
0432: private void processDeleteStatement(DeleteStatement inWds,
0433: Connection inConn) throws SQLException {
0434:
0435: //
0436: ArrayList parameterTypes = new ArrayList();
0437: ArrayList parameterValues = new ArrayList();
0438: //
0439: String where = createWhereClause(inWds, parameterTypes,
0440: parameterValues, 0);
0441:
0442: //
0443: int deleteCount[] = new int[1];
0444: String tableName = getActualTableName(inWds.getEntity()
0445: .getName());
0446: //
0447: startStatement();
0448: simpleAndPrint("DELETE FROM " + tableName);
0449: if (where != null) {
0450: simpleAndPrint(where);
0451: }
0452: endStatement(inConn, parameterTypes, parameterValues, null,
0453: deleteCount, false);
0454:
0455: //
0456: inWds.setDeleteCount(deleteCount[0]);
0457: }
0458:
0459: //
0460: private void processUpdateStatement(UpdateStatement inWus,
0461: Connection inConn) throws SQLException {
0462:
0463: //
0464: CreateStatement table = inWus.getEntity();
0465: int updateCount[] = new int[1];
0466: //
0467: ArrayList parameterTypes = new ArrayList();
0468: ArrayList parameterValues = new ArrayList();
0469: //
0470: ArrayList parameterPlaceholders = new ArrayList();
0471: for (int i = 0; i < inWus.getUpdateColumnCount(); i++) {
0472: //
0473: String nextColumnName = inWus.getUpdateColumnName(i);
0474: Object nextColumnValue = inWus.getUpdateColumnValue(i);
0475: Integer nextColumnDataType = table
0476: .getColumnDataType(nextColumnName);
0477:
0478: //
0479: parameterTypes.add(nextColumnDataType);
0480: parameterValues.add(nextColumnValue);
0481: //
0482: parameterPlaceholders.add(getActualTableName(table
0483: .getName())
0484: + "." + nextColumnName + " = " + PARAM);
0485: }
0486: //
0487: String where = createWhereClause(inWus, parameterTypes,
0488: parameterValues, 0);
0489:
0490: //
0491: startStatement();
0492: simpleAndPrint("UPDATE " + getActualTableName(table.getName()));
0493: simpleAndPrint("SET " + convertToList(parameterPlaceholders));
0494: if (where != null) {
0495: simpleAndPrint(where);
0496: }
0497: endStatement(inConn, parameterTypes, parameterValues, null,
0498: updateCount, false);
0499:
0500: //
0501: inWus.setUpdateCount(updateCount[0]);
0502: }
0503:
0504: //
0505: private SystemResultSet processSelectStatement(
0506: SelectStatement inWss, Connection inConn)
0507: throws SQLException {
0508:
0509: SystemResultSet outValue = null;
0510:
0511: ResultSet rs = null;
0512: PreparedStatement ps[] = new PreparedStatement[1];
0513:
0514: try {
0515: StringBuffer statement = new StringBuffer();
0516: ArrayList parameterTypes = new ArrayList();
0517: ArrayList parameterValues = new ArrayList();
0518:
0519: createSelectStatement(inWss, statement, parameterTypes,
0520: parameterValues, 0, false);
0521:
0522: MiscHelper
0523: .println("MySQLStore params = " + parameterValues);
0524: MiscHelper.println("MySQLStore selState = "
0525: + statement.toString());
0526:
0527: startStatement();
0528: simpleAndPrint(statement.toString());
0529: rs = endStatement(inConn, parameterTypes, parameterValues,
0530: ps, null, true);
0531: //MiscHelper.println("Got result set = " + inWss.getQuerySize());
0532:
0533: outValue = SystemStatement.newResultSet(inWss
0534: .getQuerySize());
0535: //
0536: for (int i = 0; i < inWss.getQuerySize() && rs.next(); i++) {
0537: //MiscHelper.println("Reading next row = "
0538: // + inWss.getSelectColumnCount());
0539: ArrayList nextRowTypes = new ArrayList();
0540: ArrayList nextRowValues = new ArrayList();
0541: for (int j = 0; j < inWss.getSelectColumnCount(); j++) {
0542: //
0543: String nextColumnName = inWss
0544: .getSelectColumnName(j);
0545: Integer nextColumnMetaType = inWss
0546: .getSelectColumnMetaType(j);
0547: //MiscHelper.println("nextColumnName = "
0548: // + nextColumnName);
0549: //MiscHelper.println("nextColumnMetaType = "
0550: // + nextColumnMetaType);
0551: CreateStatement nextTable = inWss
0552: .getSelectColumnTable(j);
0553: //
0554: if (nextColumnMetaType
0555: .equals(SystemStatement.NORMAL_COLUMN)) {
0556: //
0557: //
0558: String nextTableName = getActualTableName(nextTable
0559: .getName());
0560: Integer nextColumnDataType = nextTable
0561: .getColumnDataType(nextColumnName);
0562: //MiscHelper.println("nextColumnDataType = "
0563: // + nextColumnDataType);
0564: //
0565: nextRowTypes.add(nextColumnDataType);
0566: if (nextColumnDataType
0567: .equals(SystemStatement.DECIMAL)) {
0568: //
0569: nextRowValues.add(rs.getBigDecimal(j + 1));
0570: } else if (nextColumnDataType
0571: .equals(SystemStatement.SHORT_STRING)) {
0572: //
0573: nextRowValues.add(rs.getString(j + 1));
0574: } else if (nextColumnDataType
0575: .equals(SystemStatement.LONG_STRING)) {
0576: //
0577: nextRowValues.add(rs.getString(j + 1));
0578: } else if (nextColumnDataType
0579: .equals(SystemStatement.BOOLEAN)) {
0580: //
0581: nextRowValues.add(new Boolean(rs
0582: .getBoolean(j + 1)));
0583: } else if (nextColumnDataType
0584: .equals(SystemStatement.BINARY_OBJECT)) {
0585: //
0586: nextRowValues.add(rs.getBytes(j + 1));
0587: } else {
0588: throw UnexpectedSystemException
0589: .unknownState();
0590: }
0591: } else {
0592: nextRowTypes.add(SystemStatement.DECIMAL);
0593: nextRowValues.add(rs.getBigDecimal(j + 1));
0594: }
0595: }
0596: //MiscHelper.println("HSQLStore nextRowVal = " + nextRowValues);
0597: outValue.addRow(nextRowTypes, nextRowValues);
0598: }
0599: } finally {
0600: closeSQLObjects(null, ps[0], rs);
0601: }
0602:
0603: return outValue;
0604: }
0605:
0606: private void createSelectStatement(SelectStatement inWss,
0607: StringBuffer inStatement, ArrayList inParameterTypes,
0608: ArrayList inParameterValues, int inIndent,
0609: boolean inTopSelect) throws SQLException {
0610:
0611: //construct from
0612: ArrayList fromList = new ArrayList();
0613: ArrayList fromListWithTables = inWss.getAllTables();
0614: for (int i = 0; i < fromListWithTables.size(); i++) {
0615: CreateStatement wcs = (CreateStatement) fromListWithTables
0616: .get(i);
0617: fromList.add(getActualTableName(wcs.getName()));
0618: }
0619:
0620: //construct where
0621: String where = createWhereClause(inWss, inParameterTypes,
0622: inParameterValues, inIndent);
0623:
0624: //construct group by
0625: ArrayList groupByList = new ArrayList();
0626: //for (int i = 0; i < fromListWithTables.size(); i++) {
0627: // CreateStatement nextTable =
0628: // (CreateStatement)fromListWithTables.get(i);
0629: // String nextTableName = getActualTableName(nextTable.getName());
0630: // if (nextTable.isSeriesSequenceColumnPresent()) {
0631: // for (int j = 0; j < nextTable.getGroupByColumnCount(); j++) {
0632: // String nextGroupByName =
0633: // nextTable.getGroupByColumnName(j);
0634: // Integer nextGroupByType =
0635: // nextTable.getGroupByColumnType(j);
0636: // //
0637: // if (SelectStatement.NORMAL_COLUMN.equals(
0638: // nextGroupByType)) {
0639: // groupByList.add(
0640: // nextTableName + "." + nextGroupByName);
0641: // }
0642: // else if (
0643: // SelectStatement.SERIES_SEQUENCE_COLUMN.equals(
0644: // nextGroupByType)) {
0645: // groupByList.add(
0646: // nextTableName + "." + SERIES_SEQUENCE);
0647: // }
0648: // else if (SelectStatement.ID_COLUMN.equals(
0649: // nextGroupByType)) {
0650: // groupByList.add(nextTableName + "." + ID_PK);
0651: // }
0652: // else {
0653: // throw new UnexpectedSystemException("UnknownState");
0654: // }
0655: // }
0656: // }
0657: //}
0658: for (int i = 0; i < inWss.getGroupByColumnCount(); i++) {
0659: //
0660: String nextGroupByColumnName = inWss
0661: .getGroupByColumnName(i);
0662: Integer nextGroupByColumnType = inWss
0663: .getGroupByColumnType(i);
0664: //
0665: CreateStatement nextGroupByColumnTable = inWss
0666: .getGroupByColumnTable(i);
0667: String nextGroupByColumnTableName = getActualTableName(nextGroupByColumnTable
0668: .getName());
0669:
0670: if (SelectStatement.NORMAL_COLUMN
0671: .equals(nextGroupByColumnType)) {
0672:
0673: groupByList.add(nextGroupByColumnTableName + "."
0674: + nextGroupByColumnName);
0675: } else if (SelectStatement.SERIES_SEQUENCE_COLUMN
0676: .equals(nextGroupByColumnType)) {
0677:
0678: groupByList.add(nextGroupByColumnTableName + "."
0679: + SERIES_SEQUENCE);
0680: } else if (SelectStatement.ID_COLUMN
0681: .equals(nextGroupByColumnType)) {
0682:
0683: groupByList.add(nextGroupByColumnTableName + "."
0684: + ID_PK);
0685: } else {
0686: throw UnexpectedSystemException.unknownState();
0687: }
0688: }
0689: StringBuffer groupBy = null;
0690: if (groupByList.size() > 0) {
0691: groupBy = new StringBuffer();
0692: groupBy.append("GROUP BY ");
0693: groupBy.append(convertToList(groupByList));
0694: }
0695:
0696: ////construct having
0697: //ArrayList havingList = new ArrayList();
0698: //for (int i = 0; i < fromListWithTables.size(); i++) {
0699: // CreateStatement nextTable =
0700: // (CreateStatement)fromListWithTables.get(i);
0701: // String nextTableName = getActualTableName(nextTable.getName());
0702: // if (nextTable.isSeriesSequenceColumnPresent()) {
0703: // havingList.add(nextTableName + "." + SERIES_SEQUENCE
0704: // + " = MAX(" + nextTableName + "." + SERIES_SEQUENCE
0705: // + ")");
0706: // }
0707: //}
0708: //StringBuffer having = null;
0709: //if (havingList.size() > 0) {
0710: // having = new StringBuffer();
0711: // having.append("HAVING ");
0712: // having.append(convertToList(havingList, " AND "));
0713: //}
0714:
0715: //construct select
0716: ArrayList selectList = new ArrayList();
0717: for (int i = 0; i < inWss.getSelectColumnCount(); i++) {
0718: //
0719: String nextColumnName = inWss.getSelectColumnName(i);
0720: Integer nextColumnMetaType = inWss
0721: .getSelectColumnMetaType(i);
0722: CreateStatement nextTable = inWss.getSelectColumnTable(i);
0723: String nextTableName = getActualTableName(nextTable
0724: .getName());
0725: Integer nextColumnQuantifier = inWss
0726: .getSelectColumnQuantifier(i);
0727: //
0728: if (SelectStatement.NORMAL_COLUMN
0729: .equals(nextColumnMetaType)) {
0730: //
0731: String nextColumn = nextTableName + "."
0732: + nextColumnName;
0733: //
0734: //if (groupBy != null && !groupByList.contains(nextColumn)) {
0735: // throw UnexpectedSystemException.unknownState();
0736: //}
0737: //else {
0738: selectList.add(quantify(nextColumn,
0739: nextColumnQuantifier));
0740: //}
0741: } else if (SelectStatement.SERIES_SEQUENCE_COLUMN
0742: .equals(nextColumnMetaType)) {
0743: //
0744: String nextColumn = nextTableName + "."
0745: + SERIES_SEQUENCE;
0746: //if (groupBy != null) {
0747: // selectList.add("MAX(" + nextColumn + ")");
0748: //}
0749: //else {
0750: selectList.add(quantify(nextColumn,
0751: nextColumnQuantifier));
0752: //}
0753: } else if (SelectStatement.ID_COLUMN
0754: .equals(nextColumnMetaType)) {
0755: //
0756: String nextColumn = nextTableName + "." + ID_PK;
0757: //if (groupBy != null) {
0758: // selectList.add("MAX(" + nextColumn + ")");
0759: //}
0760: //else {
0761: selectList.add(quantify(nextColumn,
0762: nextColumnQuantifier));
0763: //}
0764: } else {
0765: throw UnexpectedSystemException.unknownState();
0766: }
0767: }
0768:
0769: //construct order by
0770: StringBuffer orderBy = new StringBuffer("ORDER BY ");
0771: orderBy.append(selectList.get(0));
0772: //orderBy.append(getActualTableName(inWss.getOrderByTable().getName()));
0773: //orderBy.append(".");
0774: //Integer orderByType = inWss.getOrderByType();
0775: //
0776: //if (SelectStatement.NORMAL_COLUMN.equals(orderByType)) {
0777: // orderBy.append(inWss.getOrderByName());
0778: //}
0779: //else if (SelectStatement.SERIES_SEQUENCE_COLUMN.equals(
0780: // orderByType)) {
0781: // orderBy.append(SERIES_SEQUENCE);
0782: //}
0783: //else if (SelectStatement.ID_COLUMN.equals(orderByType)) {
0784: // orderBy.append(ID_PK);
0785: //}
0786: //else {
0787: // throw UnexpectedSystemException.unknownState();
0788: //}
0789: //
0790: if (inWss.isListOrderAscending()) {
0791: orderBy.append(" ASC");
0792: } else {
0793: orderBy.append(" DESC");
0794: }
0795:
0796: //MiscHelper.println("@@@@@@@@@@@@@@@ HSQLStore REMOVING HAVING");
0797: ////if (having != null) {
0798: //// selectList.clear();
0799: //// selectList.add("T_JF_DOCS_RO.SECTION");
0800: ////}
0801: //having = null;
0802: //orderBy = null;
0803:
0804: //construct statement
0805: //-select-
0806: if (selectList.size() > 0) {
0807: inStatement.append(MiscHelper.fixString("", inIndent));
0808: inStatement.append("SELECT " + convertToList(selectList));
0809: inStatement.append("\n");
0810: }
0811: //-from-
0812: if (fromList.size() > 0) {
0813: inStatement.append(MiscHelper.fixString("", inIndent));
0814: inStatement.append("FROM " + convertToList(fromList));
0815: inStatement.append("\n");
0816: }
0817: //-where-
0818: if (where != null) {
0819: inStatement.append(where);
0820: }
0821: //-group by-
0822: if (groupBy != null) {
0823: inStatement.append(MiscHelper.fixString("", inIndent));
0824: inStatement.append(groupBy);
0825: inStatement.append("\n");
0826: }
0827: //-having-
0828: //if (having != null) {
0829: // inStatement.append(MiscHelper.fixString("", inIndent));
0830: // inStatement.append(having);
0831: // inStatement.append("\n");
0832: //}
0833: //-order by-
0834: if (orderBy != null) {
0835: inStatement.append(MiscHelper.fixString("", inIndent));
0836: inStatement.append(orderBy);
0837: inStatement.append("\n");
0838: }
0839: //-limit-
0840: if (inTopSelect) {
0841: inStatement.append(MiscHelper.fixString("", inIndent));
0842: inStatement.append("LIMIT " + inWss.getStartRow() + " "
0843: + inWss.getQuerySize());
0844: inStatement.append("\n");
0845: }
0846: }
0847:
0848: private String quantify(String inName, Integer inQuantifier) {
0849:
0850: String outValue = inName;
0851:
0852: if (inQuantifier != null) {
0853: if (inQuantifier.equals(SystemStatement.MAX_COLUMN)) {
0854: outValue = "MAX(" + outValue + ")";
0855: } else {
0856: throw UnexpectedSystemException.unknownState();
0857: }
0858: }
0859:
0860: return outValue;
0861: }
0862:
0863: private void constructCompare(StringBuffer inStatement,
0864: ArrayList inParameterTypes, ArrayList inParameterValues,
0865: CreateStatement inTable, Integer inMetaType,
0866: String inColumn, Integer inDataType,
0867: String inCompareSymbol, Object inValue) {
0868: //
0869: inStatement.append(getActualTableName(inTable.getName()));
0870: inStatement.append(".");
0871: if (inMetaType.equals(SystemStatement.NORMAL_COLUMN)) {
0872: inStatement.append(inColumn);
0873: } else if (inMetaType.equals(SystemStatement.ID_COLUMN)) {
0874: inStatement.append(ID_PK);
0875: } else if (inMetaType
0876: .equals(SystemStatement.SERIES_SEQUENCE_COLUMN)) {
0877: inStatement.append(SERIES_SEQUENCE);
0878: } else {
0879: throw UnexpectedSystemException.unknownState();
0880: }
0881: inStatement.append(" ");
0882: inStatement.append(inCompareSymbol);
0883: if (inValue != null) {
0884: inStatement.append(" ");
0885: inStatement.append("?");
0886: }
0887: //
0888: //MiscHelper.println("HSQLStore1 inDataType = " + inDataType);
0889: if (inDataType == null) {
0890: throw new UnexpectedSystemException("Null Data Type");
0891: }
0892: inParameterTypes.add(inDataType);
0893: inParameterValues.add(inValue);
0894: }
0895:
0896: private String createWhereClause(WhereStatement inWws,
0897: ArrayList inParameterTypes, ArrayList inParameterValues,
0898: int inIndent) throws SQLException {
0899:
0900: String outValue = null;
0901:
0902: //construct where
0903: ArrayList fromListWithTables = inWws.getAllTables();
0904: //
0905: ArrayList foreignKeyList = new ArrayList();
0906: for (int i = 0; i < fromListWithTables.size(); i++) {
0907: CreateStatement nextTable = (CreateStatement) fromListWithTables
0908: .get(i);
0909: String nextTableName = getActualTableName(nextTable
0910: .getName());
0911: for (int j = 0; j < nextTable.getColumnCount(); j++) {
0912: String nextColumnName = nextTable.getColumnName(j);
0913: String nextColumnForeign = nextTable
0914: .getColumnForeignKey(j);
0915: //
0916: if (!inWws.isForeignKeyBlocked(nextTable,
0917: nextColumnName)
0918: && nextColumnForeign != null
0919: && inWws
0920: .isPresentWithinAllTables(nextColumnForeign)) {
0921: //
0922: foreignKeyList.add(nextTableName + "."
0923: + nextColumnName + " = "
0924: + getActualTableName(nextColumnForeign)
0925: + "." + ID_PK);
0926: }
0927: }
0928: }
0929: //
0930: StringBuffer customWhere = new StringBuffer();
0931: for (int i = 0; i < inWws.getWhereColumnCount(); i++) {
0932: //
0933: customWhere.append(MiscHelper.fixString("", inIndent));
0934: //
0935: String nextColumnName = inWws.getWhereColumnName(i);
0936: Integer nextColumnMetaType = inWws
0937: .getWhereColumnMetaType(i);
0938: //
0939: CreateStatement nextColumnTable = inWws
0940: .getWhereColumnTable(i);
0941: Integer nextColumnDataType = null;
0942: //
0943: if (nextColumnMetaType
0944: .equals(SystemStatement.NORMAL_COLUMN)
0945: && nextColumnName != null) {
0946: //
0947: nextColumnDataType = nextColumnTable
0948: .getColumnDataType(nextColumnName);
0949: } else if (nextColumnMetaType
0950: .equals(SystemStatement.ID_COLUMN)) {
0951: nextColumnDataType = SystemStatement.DECIMAL;
0952: } else if (nextColumnMetaType
0953: .equals(SystemStatement.SERIES_SEQUENCE_COLUMN)) {
0954: nextColumnDataType = SystemStatement.DECIMAL;
0955: } else {
0956: throw UnexpectedSystemException.unknownState();
0957: }
0958: //
0959: CreateStatement nextColumnAlternateTable = inWws
0960: .getWhereColumnAlternateTable(i);
0961: Object nextColumnAlternateValue = inWws
0962: .getWhereColumnAlternateValue(i);
0963: String nextColumnAlternateName = inWws
0964: .getWhereColumnAlternateName(i);
0965: Integer nextColumnAlternateMetaType = inWws
0966: .getWhereColumnAlternateMetaType(i);
0967: //
0968: String nextColumnConjunction = inWws
0969: .getWhereColumnConjunction(i);
0970: Integer nextColumnComparison = inWws
0971: .getWhereColumnComparison(i);
0972: //
0973: int nextColumnParenthesis = inWws
0974: .getWhereColumnParenthesis(i);
0975: // convertToParenthesis(inWss.getWhereColumnParenthesis(i));
0976:
0977: if (nextColumnParenthesis > 0) {
0978: if (i > 0) {
0979: customWhere.append(" ");
0980: customWhere.append(nextColumnConjunction);
0981: customWhere.append(" ");
0982: }
0983: customWhere
0984: .append(convertToParenthesis(nextColumnParenthesis));
0985: } else if (nextColumnParenthesis < 0) {
0986: customWhere
0987: .append(convertToParenthesis(nextColumnParenthesis));
0988: if (i > 0) {
0989: customWhere.append(" ");
0990: customWhere.append(nextColumnConjunction);
0991: customWhere.append(" ");
0992: }
0993: } else {
0994: if (i > 0) {
0995: customWhere.append(" ");
0996: customWhere.append(nextColumnConjunction);
0997: customWhere.append(" ");
0998: }
0999: }
1000:
1001: //
1002: //MiscHelper.println("HSQLStore::769 nextColumnAlternateTable = "
1003: // + nextColumnAlternateTable);
1004: //MiscHelper.println("HSQLStore::769 nextColumnAlternateValue = "
1005: // + nextColumnAlternateValue);
1006: if (nextColumnAlternateTable != null
1007: && nextColumnAlternateValue == null) {
1008: //
1009: throw UnexpectedSystemException.notImplemented();
1010: } else if (nextColumnAlternateTable == null
1011: && nextColumnAlternateValue != null) {
1012: //
1013: if (SelectStatement.EQUALS.equals(nextColumnComparison)) {
1014: //
1015: constructCompare(customWhere, inParameterTypes,
1016: inParameterValues, nextColumnTable,
1017: nextColumnMetaType, nextColumnName,
1018: nextColumnDataType, "=",
1019: nextColumnAlternateValue);
1020: } else if (SelectStatement.GREATER_THAN
1021: .equals(nextColumnComparison)) {
1022: //
1023: constructCompare(customWhere, inParameterTypes,
1024: inParameterValues, nextColumnTable,
1025: nextColumnMetaType, nextColumnName,
1026: nextColumnDataType, ">",
1027: nextColumnAlternateValue);
1028: } else if (SelectStatement.GREATER_THAN_OR_EQUAL
1029: .equals(nextColumnComparison)) {
1030: //
1031: constructCompare(customWhere, inParameterTypes,
1032: inParameterValues, nextColumnTable,
1033: nextColumnMetaType, nextColumnName,
1034: nextColumnDataType, ">=",
1035: nextColumnAlternateValue);
1036: } else if (SelectStatement.NOT_EQUAL
1037: .equals(nextColumnComparison)) {
1038: //
1039: constructCompare(customWhere, inParameterTypes,
1040: inParameterValues, nextColumnTable,
1041: nextColumnMetaType, nextColumnName,
1042: nextColumnDataType, "<>",
1043: nextColumnAlternateValue);
1044: } else if (SelectStatement.LESS_THAN
1045: .equals(nextColumnComparison)) {
1046: //
1047: constructCompare(customWhere, inParameterTypes,
1048: inParameterValues, nextColumnTable,
1049: nextColumnMetaType, nextColumnName,
1050: nextColumnDataType, "<",
1051: nextColumnAlternateValue);
1052: } else if (SelectStatement.LESS_THAN_OR_EQUAL
1053: .equals(nextColumnComparison)) {
1054: //
1055: constructCompare(customWhere, inParameterTypes,
1056: inParameterValues, nextColumnTable,
1057: nextColumnMetaType, nextColumnName,
1058: nextColumnDataType, "<=",
1059: nextColumnAlternateValue);
1060: } else if (SelectStatement.LIKE
1061: .equals(nextColumnComparison)) {
1062: //
1063: constructCompare(customWhere, inParameterTypes,
1064: inParameterValues, nextColumnTable,
1065: nextColumnMetaType, nextColumnName,
1066: nextColumnDataType, "LIKE",
1067: nextColumnAlternateValue);
1068: } else if (SelectStatement.NOT_LIKE
1069: .equals(nextColumnComparison)) {
1070: //
1071: constructCompare(customWhere, inParameterTypes,
1072: inParameterValues, nextColumnTable,
1073: nextColumnMetaType, nextColumnName,
1074: nextColumnDataType, "NOT LIKE",
1075: nextColumnAlternateValue);
1076: } else if (SelectStatement.IS_NULL
1077: .equals(nextColumnComparison)) {
1078: //
1079: constructCompare(customWhere, inParameterTypes,
1080: inParameterValues, nextColumnTable,
1081: nextColumnMetaType, nextColumnName,
1082: nextColumnDataType, "IS NULL", null);
1083: } else if (SelectStatement.IS_NOT_NULL
1084: .equals(nextColumnComparison)) {
1085: //
1086: constructCompare(customWhere, inParameterTypes,
1087: inParameterValues, nextColumnTable,
1088: nextColumnMetaType, nextColumnName,
1089: nextColumnDataType, "IS NOT NULL", null);
1090: } else if (SelectStatement.IN
1091: .equals(nextColumnComparison)) {
1092: //
1093: if (nextColumnAlternateValue instanceof ArrayList) {
1094: ArrayList compareList = (ArrayList) nextColumnAlternateValue;
1095: ArrayList paramList = new ArrayList();
1096: for (int j = 0; j < compareList.size(); j++) {
1097: paramList.add("?");
1098: //MiscHelper.println("HSQLStore2 inDataType = "
1099: // + nextColumnDataType);
1100: inParameterTypes.add(nextColumnDataType);
1101: inParameterValues.add(compareList.get(j));
1102: }
1103: customWhere
1104: .append(getActualTableName(nextColumnTable
1105: .getName()));
1106: customWhere.append(".");
1107: //
1108: if (nextColumnMetaType
1109: .equals(SystemStatement.NORMAL_COLUMN)) {
1110:
1111: customWhere.append(nextColumnName);
1112: } else if (nextColumnMetaType
1113: .equals(SystemStatement.ID_COLUMN)) {
1114:
1115: customWhere.append(ID_PK);
1116: } else if (nextColumnMetaType
1117: .equals(SystemStatement.SERIES_SEQUENCE_COLUMN)) {
1118:
1119: customWhere.append(SERIES_SEQUENCE);
1120: } else {
1121: throw UnexpectedSystemException
1122: .unknownState();
1123: }
1124: //
1125: //customWhere.append(nextColumnName);
1126: customWhere.append(" IN (");
1127: customWhere.append(convertToList(paramList));
1128: customWhere.append(")");
1129: } else if (nextColumnAlternateValue instanceof SelectStatement) {
1130: //
1131: customWhere
1132: .append(getActualTableName(nextColumnTable
1133: .getName()));
1134: customWhere.append(".");
1135: //
1136: if (nextColumnMetaType
1137: .equals(SystemStatement.NORMAL_COLUMN)) {
1138:
1139: customWhere.append(nextColumnName);
1140: } else if (nextColumnMetaType
1141: .equals(SystemStatement.ID_COLUMN)) {
1142:
1143: customWhere.append(ID_PK);
1144: } else if (nextColumnMetaType
1145: .equals(SystemStatement.SERIES_SEQUENCE_COLUMN)) {
1146:
1147: customWhere.append(SERIES_SEQUENCE);
1148: } else {
1149: throw UnexpectedSystemException
1150: .unknownState();
1151: }
1152: //
1153: //customWhere.append(nextColumnName);
1154: customWhere.append(" IN (");
1155: //
1156: customWhere.append("\n");
1157: createSelectStatement(
1158: (SelectStatement) nextColumnAlternateValue,
1159: customWhere, inParameterTypes,
1160: inParameterValues, inIndent + 4, false);
1161: customWhere.append(MiscHelper.fixString("",
1162: inIndent));
1163: //
1164: customWhere.append(")");
1165: } else {
1166: throw UnexpectedSystemException.unknownState();
1167: }
1168: } else if (SelectStatement.NOT_IN
1169: .equals(nextColumnComparison)) {
1170: //
1171: } else {
1172: throw UnexpectedSystemException.notImplemented();
1173: }
1174: //
1175: //
1176: } else {
1177: throw UnexpectedSystemException.unknownState();
1178: }
1179: //
1180: customWhere.append("\n");
1181: }
1182: //
1183: if (customWhere.length() > 0) {
1184: customWhere.append(convertToParenthesis(inWws
1185: .getLastWhereColumnParenthesis()));
1186: } else {
1187: customWhere = null;
1188: }
1189:
1190: //
1191: StringBuffer statement = new StringBuffer();
1192: if (foreignKeyList.size() > 0 && customWhere != null) {
1193: statement.append(MiscHelper.fixString("", inIndent));
1194: statement.append("WHERE ("
1195: + convertToList(foreignKeyList, " AND ")
1196: + ") AND (" + customWhere + ")");
1197: statement.append("\n");
1198: } else if (foreignKeyList.size() > 0 && customWhere == null) {
1199: statement.append(MiscHelper.fixString("", inIndent));
1200: statement.append("WHERE "
1201: + convertToList(foreignKeyList, " AND "));
1202: statement.append("\n");
1203: } else if (foreignKeyList.size() == 0 && customWhere != null) {
1204: statement.append(MiscHelper.fixString("", inIndent));
1205: statement.append("WHERE " + customWhere);
1206: statement.append("\n");
1207: } else {
1208: //do nothing
1209: statement = null;
1210: }
1211:
1212: //
1213: if (statement != null) {
1214: outValue = statement.toString();
1215: } else {
1216: outValue = null;
1217: }
1218:
1219: return outValue;
1220: }
1221:
1222: private BigDecimal getNextSeriesSequence(String inName,
1223: Connection inConn) throws SQLException {
1224:
1225: BigDecimal outValue = null;
1226:
1227: ResultSet rs = null;
1228: PreparedStatement ps[] = new PreparedStatement[1];
1229:
1230: try {
1231:
1232: //
1233: startStatement();
1234: simpleAndPrint("INSERT INTO "
1235: + getActualSeriesSequenceName(inName)
1236: + " VALUES (NULL)");
1237: endStatement(inConn, null, null, ps, null, false);
1238: //closeSQLObjects(null, null, rs);
1239:
1240: rs = (ps[0]).getGeneratedKeys();
1241:
1242: //
1243: //startStatement();
1244: //simpleAndPrint("SELECT LAST_INSERT_ID()");
1245: //rs = endStatement(inConn, null, null, ps, null, false);
1246:
1247: if (rs.next()) {
1248: outValue = rs.getBigDecimal(1);
1249: } else {
1250: throw UnexpectedSystemException.unknownState();
1251: }
1252: } finally {
1253: closeSQLObjects(null, ps[0], rs);
1254: }
1255:
1256: return outValue;
1257: }
1258:
1259: private String getConstraintName(String inTable) {
1260:
1261: StringBuffer outValue = new StringBuffer();
1262:
1263: //
1264: outValue.append("CON__");
1265: outValue.append(getActualTableName(inTable));
1266: outValue.append("__");
1267: outValue.append(this .constraintCount);
1268: //
1269: this.constraintCount++;
1270:
1271: return outValue.toString();
1272: }
1273: }
|