001: /*
002: * Licensed to the Apache Software Foundation (ASF) under one
003: * or more contributor license agreements. See the NOTICE file
004: * distributed with this work for additional information
005: * regarding copyright ownership. The ASF licenses this file
006: * to you under the Apache License, Version 2.0 (the
007: * "License"); you may not use this file except in compliance
008: * with the License. You may obtain a copy of the License at
009: *
010: * http://www.apache.org/licenses/LICENSE-2.0
011: *
012: * Unless required by applicable law or agreed to in writing,
013: * software distributed under the License is distributed on an
014: * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
015: * KIND, either express or implied. See the License for the
016: * specific language governing permissions and limitations
017: * under the License.
018: */
019: package org.apache.openjpa.jdbc.sql;
020:
021: import java.lang.reflect.Method;
022: import java.sql.Connection;
023: import java.sql.DatabaseMetaData;
024: import java.sql.SQLException;
025: import java.sql.Types;
026: import java.util.Arrays;
027: import java.util.StringTokenizer;
028:
029: import org.apache.openjpa.jdbc.kernel.JDBCFetchConfiguration;
030: import org.apache.openjpa.jdbc.kernel.exps.FilterValue;
031: import org.apache.openjpa.jdbc.kernel.exps.Lit;
032: import org.apache.openjpa.jdbc.kernel.exps.Param;
033: import org.apache.openjpa.jdbc.kernel.exps.Val;
034: import org.apache.openjpa.jdbc.schema.Column;
035: import org.apache.openjpa.jdbc.schema.Index;
036: import org.apache.openjpa.jdbc.schema.Schema;
037: import org.apache.openjpa.jdbc.schema.Sequence;
038: import org.apache.openjpa.jdbc.schema.Table;
039: import org.apache.openjpa.kernel.Filters;
040: import org.apache.openjpa.lib.util.Localizer;
041: import org.apache.openjpa.meta.JavaTypes;
042: import org.apache.openjpa.util.OpenJPAException;
043: import org.apache.openjpa.util.UnsupportedException;
044:
045: import serp.util.Strings;
046:
047: /**
048: * Dictionary for IBM DB2 database.
049: */
050: public class DB2Dictionary extends AbstractDB2Dictionary {
051: private static final Localizer _loc = Localizer
052: .forPackage(DB2Dictionary.class);
053:
054: public String optimizeClause = "optimize for";
055: public String rowClause = "row";
056: protected int db2ServerType = 0;
057: public static final int db2ISeriesV5R3OrEarlier = 1;
058: public static final int db2UDBV81OrEarlier = 2;
059: public static final int db2ZOSV8xOrLater = 3;
060: public static final int db2UDBV82OrLater = 4;
061: public static final int db2ISeriesV5R4OrLater = 5;
062: private static final String forUpdate = "FOR UPDATE";
063: private static final String withRSClause = "WITH RS";
064: private static final String withRRClause = "WITH RR";
065: private static final String useKeepUpdateLockClause = "USE AND KEEP UPDATE LOCKS";
066: private static final String useKeepExclusiveLockClause = "USE AND KEEP EXCLUSIVE LOCKS";
067: private static final String forReadOnlyClause = "FOR READ ONLY";
068: protected String databaseProductName = null;
069: protected String databaseProductVersion = null;
070: protected int maj = 0;
071: protected int min = 0;
072:
073: private int defaultBatchLimit = 100;
074:
075: public DB2Dictionary() {
076: platform = "DB2";
077: validationSQL = "SELECT DISTINCT(CURRENT TIMESTAMP) FROM "
078: + "SYSIBM.SYSTABLES";
079: supportsSelectEndIndex = true;
080:
081: nextSequenceQuery = "VALUES NEXTVAL FOR {0}";
082:
083: sequenceSQL = "SELECT SEQSCHEMA AS SEQUENCE_SCHEMA, "
084: + "SEQNAME AS SEQUENCE_NAME FROM SYSCAT.SEQUENCES";
085: sequenceSchemaSQL = "SEQSCHEMA = ?";
086: sequenceNameSQL = "SEQNAME = ?";
087: characterColumnSize = 254;
088:
089: binaryTypeName = "BLOB(1M)";
090: longVarbinaryTypeName = "BLOB(1M)";
091: varbinaryTypeName = "BLOB(1M)";
092: clobTypeName = "CLOB(1M)";
093: longVarcharTypeName = "LONG VARCHAR";
094: datePrecision = MICRO;
095: storeCharsAsNumbers = false;
096:
097: fixedSizeTypeNameSet.addAll(Arrays.asList(new String[] {
098: "LONG VARCHAR FOR BIT DATA", "LONG VARCHAR",
099: "LONG VARGRAPHIC", }));
100: systemSchemas = new String(
101: "SYSCAT,SYSIBM,SYSSTAT,SYSIBMADM,SYSTOOLS");
102: maxConstraintNameLength = 18;
103: maxIndexNameLength = 18;
104: maxColumnNameLength = 30;
105: supportsDeferredConstraints = false;
106: supportsDefaultDeleteAction = false;
107: supportsAlterTableWithDropColumn = false;
108:
109: supportsNullTableForGetColumns = false;
110: requiresCastForMathFunctions = true;
111: requiresCastForComparisons = true;
112:
113: reservedWordSet.addAll(Arrays.asList(new String[] { "AFTER",
114: "ALIAS", "ALLOW", "APPLICATION", "ASSOCIATE",
115: "ASUTIME", "AUDIT", "AUX", "AUXILIARY", "BEFORE",
116: "BINARY", "BUFFERPOOL", "CACHE", "CALL", "CALLED",
117: "CAPTURE", "CARDINALITY", "CCSID", "CLUSTER",
118: "COLLECTION", "COLLID", "COMMENT", "CONCAT",
119: "CONDITION", "CONTAINS", "COUNT_BIG",
120: "CURRENT_LC_CTYPE", "CURRENT_PATH", "CURRENT_SERVER",
121: "CURRENT_TIMEZONE", "CYCLE", "DATA", "DATABASE",
122: "DAYS", "DB2GENERAL", "DB2GENRL", "DB2SQL", "DBINFO",
123: "DEFAULTS", "DEFINITION", "DETERMINISTIC", "DISALLOW",
124: "DO", "DSNHATTR", "DSSIZE", "DYNAMIC", "EACH",
125: "EDITPROC", "ELSEIF", "ENCODING", "END-EXEC1", "ERASE",
126: "EXCLUDING", "EXIT", "FENCED", "FIELDPROC", "FILE",
127: "FINAL", "FREE", "FUNCTION", "GENERAL", "GENERATED",
128: "GRAPHIC", "HANDLER", "HOLD", "HOURS", "IF",
129: "INCLUDING", "INCREMENT", "INDEX", "INHERIT", "INOUT",
130: "INTEGRITY", "ISOBID", "ITERATE", "JAR", "JAVA",
131: "LABEL", "LC_CTYPE", "LEAVE", "LINKTYPE", "LOCALE",
132: "LOCATOR", "LOCATORS", "LOCK", "LOCKMAX", "LOCKSIZE",
133: "LONG", "LOOP", "MAXVALUE", "MICROSECOND",
134: "MICROSECONDS", "MINUTES", "MINVALUE", "MODE",
135: "MODIFIES", "MONTHS", "NEW", "NEW_TABLE", "NOCACHE",
136: "NOCYCLE", "NODENAME", "NODENUMBER", "NOMAXVALUE",
137: "NOMINVALUE", "NOORDER", "NULLS", "NUMPARTS", "OBID",
138: "OLD", "OLD_TABLE", "OPTIMIZATION", "OPTIMIZE", "OUT",
139: "OVERRIDING", "PACKAGE", "PARAMETER", "PART",
140: "PARTITION", "PATH", "PIECESIZE", "PLAN", "PRIQTY",
141: "PROGRAM", "PSID", "QUERYNO", "READS", "RECOVERY",
142: "REFERENCING", "RELEASE", "RENAME", "REPEAT", "RESET",
143: "RESIGNAL", "RESTART", "RESULT", "RESULT_SET_LOCATOR",
144: "RETURN", "RETURNS", "ROUTINE", "ROW", "RRN", "RUN",
145: "SAVEPOINT", "SCRATCHPAD", "SECONDS", "SECQTY",
146: "SECURITY", "SENSITIVE", "SIGNAL", "SIMPLE", "SOURCE",
147: "SPECIFIC", "SQLID", "STANDARD", "START", "STATIC",
148: "STAY", "STOGROUP", "STORES", "STYLE", "SUBPAGES",
149: "SYNONYM", "SYSFUN", "SYSIBM", "SYSPROC", "SYSTEM",
150: "TABLESPACE", "TRIGGER", "TYPE", "UNDO", "UNTIL",
151: "VALIDPROC", "VARIABLE", "VARIANT", "VCAT", "VOLUMES",
152: "WHILE", "WLM", "YEARS", }));
153:
154: super .setBatchLimit(defaultBatchLimit);
155:
156: selectWordSet.add("WITH");
157: }
158:
159: public boolean supportsRandomAccessResultSet(Select sel,
160: boolean forUpdate) {
161: return !forUpdate
162: && super .supportsRandomAccessResultSet(sel, forUpdate);
163: }
164:
165: protected void appendSelectRange(SQLBuffer buf, long start,
166: long end, boolean subselect) {
167: // appends the literal range string, since DB2 is unable to handle
168: // a bound parameter for it
169: // do not generate FETCH FIRST clause for subselect
170: if (!subselect)
171: buf.append(" FETCH FIRST ").append(Long.toString(end))
172: .append(" ROWS ONLY");
173: }
174:
175: protected void appendSelect(SQLBuffer selectSQL, Object alias,
176: Select sel, int idx) {
177: // if this is a literal value, add a cast...
178: Object val = sel.getSelects().get(idx);
179: if (val instanceof Lit)
180: selectSQL.append("CAST(");
181:
182: // ... and add the select per super's behavior...
183: super .appendSelect(selectSQL, alias, sel, idx);
184:
185: // ... and finish the cast
186: if (val instanceof Lit) {
187: Class c = ((Lit) val).getType();
188: int javaTypeCode = JavaTypes.getTypeCode(c);
189: int jdbcTypeCode = getJDBCType(javaTypeCode, false);
190: String typeName = getTypeName(jdbcTypeCode);
191: selectSQL.append(" AS " + typeName);
192:
193: // if the literal is a string, use the default char col size
194: // in the cast statement.
195: if (String.class.equals(c))
196: selectSQL.append("(" + characterColumnSize + ")");
197:
198: selectSQL.append(")");
199: }
200: }
201:
202: public String[] getCreateSequenceSQL(Sequence seq) {
203: String[] sql = super .getCreateSequenceSQL(seq);
204: if (seq.getAllocate() > 1)
205: sql[0] += " CACHE " + seq.getAllocate();
206: return sql;
207: }
208:
209: protected String getSequencesSQL(String schemaName,
210: String sequenceName) {
211: StringBuffer buf = new StringBuffer();
212: buf.append(sequenceSQL);
213: if (schemaName != null || sequenceName != null)
214: buf.append(" WHERE ");
215: if (schemaName != null) {
216: buf.append(sequenceSchemaSQL);
217: if (sequenceName != null)
218: buf.append(" AND ");
219: }
220: if (sequenceName != null)
221: buf.append(sequenceNameSQL);
222: return buf.toString();
223: }
224:
225: public Connection decorate(Connection conn) throws SQLException {
226: // some versions of the DB2 driver seem to default to
227: // READ_UNCOMMITTED, which will prevent locking from working
228: // (multiple SELECT ... FOR UPDATE statements are allowed on
229: // the same instance); if we have not overridden the
230: // transaction isolation in the configuration, default to
231: // TRANSACTION_READ_COMMITTED
232: conn = super .decorate(conn);
233:
234: if (conf.getTransactionIsolationConstant() == -1
235: && conn.getTransactionIsolation() < conn.TRANSACTION_READ_COMMITTED)
236: conn
237: .setTransactionIsolation(conn.TRANSACTION_READ_COMMITTED);
238:
239: return conn;
240: }
241:
242: private boolean isJDBC3(DatabaseMetaData meta) {
243: try {
244: // JDBC3-only method, so it might throw a AbstractMethodError
245: return meta.getJDBCMajorVersion() >= 3;
246: } catch (Throwable t) {
247: return false;
248: }
249: }
250:
251: public void connectedConfiguration(Connection conn)
252: throws SQLException {
253: super .connectedConfiguration(conn);
254:
255: DatabaseMetaData metaData = conn.getMetaData();
256: databaseProductName = metaData.getDatabaseProductName();
257: databaseProductVersion = metaData.getDatabaseProductVersion();
258:
259: // Determine the type of DB2 database
260: // First check for AS/400
261: getProductVersionMajorMinorForISeries();
262:
263: if (maj > 0) {
264: if (isDB2ISeriesV5R3OrEarlier())
265: db2ServerType = db2ISeriesV5R3OrEarlier;
266: else if (isDB2ISeriesV5R4OrLater())
267: db2ServerType = db2ISeriesV5R4OrLater;
268: }
269:
270: if (db2ServerType == 0) {
271: if (isJDBC3(metaData)) {
272: maj = metaData.getDatabaseMajorVersion();
273: min = metaData.getDatabaseMinorVersion();
274: } else
275: getProductVersionMajorMinor();
276:
277: // Determine the type of DB2 database for ZOS & UDB
278: if (isDB2UDBV81OrEarlier())
279: db2ServerType = db2UDBV81OrEarlier;
280: else if (isDB2ZOSV8xOrLater())
281: db2ServerType = db2ZOSV8xOrLater;
282: else if (isDB2UDBV82OrLater())
283: db2ServerType = db2UDBV82OrLater;
284: }
285:
286: // verify that databae product is supported
287: if (db2ServerType == 0 || maj == 0)
288: throw new UnsupportedException(_loc.get("db-not-supported",
289: new Object[] { databaseProductName,
290: databaseProductVersion }));
291:
292: if (maj >= 9 || (maj == 8 && min >= 2)) {
293: supportsLockingWithMultipleTables = true;
294: supportsLockingWithInnerJoin = true;
295: supportsLockingWithOuterJoin = true;
296: forUpdateClause = "WITH RR USE AND KEEP UPDATE LOCKS";
297: if (maj >= 9)
298: supportsXMLColumn = true;
299: }
300:
301: // platform specific settings
302: switch (db2ServerType) {
303: case db2ZOSV8xOrLater:
304: // DB2 Z/OS
305: characterColumnSize = 255;
306: lastGeneratedKeyQuery = "SELECT IDENTITY_VAL_LOCAL() FROM "
307: + "SYSIBM.SYSDUMMY1";
308: nextSequenceQuery = "SELECT NEXTVAL FOR {0} FROM "
309: + "SYSIBM.SYSDUMMY1";
310: sequenceSQL = "SELECT SCHEMA AS SEQUENCE_SCHEMA, "
311: + "NAME AS SEQUENCE_NAME FROM SYSIBM.SYSSEQUENCES";
312: sequenceSchemaSQL = "SCHEMA = ?";
313: sequenceNameSQL = "NAME = ?";
314: if (maj == 8)
315: // DB2 Z/OS Version 8: no bigint support, hence map Java
316: // long to decimal
317: bigintTypeName = "DECIMAL(31,0)";
318: break;
319: case db2ISeriesV5R3OrEarlier:
320: case db2ISeriesV5R4OrLater:
321: lastGeneratedKeyQuery = "SELECT IDENTITY_VAL_LOCAL() FROM "
322: + "SYSIBM.SYSDUMMY1";
323: nextSequenceQuery = "SELECT NEXTVAL FOR {0} FROM "
324: + "SYSIBM.SYSDUMMY1";
325: validationSQL = "SELECT DISTINCT(CURRENT TIMESTAMP) FROM "
326: + "QSYS2.SYSTABLES";
327: sequenceSQL = "SELECT SEQUENCE_SCHEMA, "
328: + "SEQUENCE_NAME FROM QSYS2.SYSSEQUENCES";
329: sequenceSchemaSQL = "SEQUENCE_SCHEMA = ?";
330: sequenceNameSQL = "SEQUENCE_NAME = ?";
331: break;
332: }
333: }
334:
335: /**
336: * Get the update clause for the query based on the
337: * updateClause and isolationLevel hints
338: */
339: protected String getForUpdateClause(JDBCFetchConfiguration fetch,
340: boolean isForUpdate, Select sel) {
341: int isolationLevel;
342: // For db2UDBV81OrEarlier and db2ISeriesV5R3OrEarlier:
343: // "optimize for" clause appears before "for update" clause.
344: StringBuffer forUpdateString = new StringBuffer(
345: getOptimizeClause(sel));
346: try {
347: // Determine the isolationLevel; the fetch
348: // configuration data overrides the persistence.xml value
349: if (fetch != null && fetch.getIsolation() != -1)
350: isolationLevel = fetch.getIsolation();
351: else
352: isolationLevel = conf.getTransactionIsolationConstant();
353:
354: if (isForUpdate) {
355: switch (db2ServerType) {
356: case db2ISeriesV5R3OrEarlier:
357: case db2UDBV81OrEarlier:
358: if (isolationLevel == Connection.TRANSACTION_SERIALIZABLE)
359: forUpdateString.append(" ").append(
360: forUpdateClause);
361: else
362: forUpdateString.append(" ").append(forUpdate)
363: .append(" ").append(withRSClause);
364: break;
365: case db2ZOSV8xOrLater:
366: case db2UDBV82OrLater:
367: if (isolationLevel == Connection.TRANSACTION_SERIALIZABLE) {
368: forUpdateString.append(" ").append(
369: forReadOnlyClause).append(" ").append(
370: withRRClause).append(" ").append(
371: useKeepUpdateLockClause);
372: } else {
373: forUpdateString.append(" ").append(
374: forReadOnlyClause).append(" ").append(
375: withRSClause).append(" ").append(
376: useKeepUpdateLockClause);
377: }
378: break;
379: case db2ISeriesV5R4OrLater:
380: if (isolationLevel == Connection.TRANSACTION_SERIALIZABLE) {
381: forUpdateString.append(" ").append(
382: forReadOnlyClause).append(" ").append(
383: withRRClause).append(" ").append(
384: useKeepExclusiveLockClause);
385: } else {
386: forUpdateString.append(" ").append(
387: forReadOnlyClause).append(" ").append(
388: withRSClause).append(" ").append(
389: useKeepExclusiveLockClause);
390: }
391: break;
392: }
393: }
394: } catch (Exception e) {
395: if (log.isTraceEnabled())
396: log.error(e.toString(), e);
397: }
398: return forUpdateString.toString();
399: }
400:
401: public boolean isDB2UDBV82OrLater() {
402: boolean match = false;
403: if ((databaseProductVersion.indexOf("SQL") != -1 || databaseProductName
404: .indexOf("DB2/") != -1)
405: && ((maj == 8 && min >= 2) || (maj >= 9)))
406: match = true;
407: return match;
408: }
409:
410: public boolean isDB2ZOSV8xOrLater() {
411: boolean match = false;
412: if ((databaseProductVersion.indexOf("DSN") != -1 || databaseProductName
413: .indexOf("DB2/") == -1)
414: && maj >= 8)
415: match = true;
416: return match;
417: }
418:
419: public boolean isDB2ISeriesV5R3OrEarlier() {
420: boolean match = false;
421: if (databaseProductName.indexOf("AS") != -1
422: && ((maj == 5 && min <= 3) || maj < 5))
423: match = true;
424: return match;
425: }
426:
427: public boolean isDB2ISeriesV5R4OrLater() {
428: boolean match = false;
429: if (databaseProductName.indexOf("AS") != -1
430: && (maj >= 6 || (maj == 5 && min >= 4)))
431: match = true;
432: return match;
433: }
434:
435: public boolean isDB2UDBV81OrEarlier() {
436: boolean match = false;
437: if ((databaseProductVersion.indexOf("SQL") != -1 || databaseProductName
438: .indexOf("DB2/") != -1)
439: && ((maj == 8 && min <= 1) || maj < 8))
440: match = true;
441: return match;
442: }
443:
444: /** Get the version Major/Minor for the ISeries
445: */
446: private void getProductVersionMajorMinorForISeries() {
447: // ISeries DBProdName DB2 UDB for AS/400
448: // (Toolbox)DBProdVersion 05.04.0000 V5R4m0
449: // ISeries DB2 UDB for AS/400
450: // (Native) V5R4M0
451: if (databaseProductName.indexOf("AS") != -1) {
452: String s = databaseProductVersion
453: .substring(databaseProductVersion.indexOf('V'));
454: s = s.toUpperCase();
455:
456: StringTokenizer stringtokenizer = new StringTokenizer(s,
457: "VRM", false);
458: if (stringtokenizer.countTokens() == 3) {
459: String s1 = stringtokenizer.nextToken();
460: maj = Integer.parseInt(s1);
461: String s2 = stringtokenizer.nextToken();
462: min = Integer.parseInt(s2);
463: }
464: }
465: }
466:
467: private void getProductVersionMajorMinor() {
468: // Incase JDBC driver version is lower than 3
469: // use following info to determine Major and Minor
470: // CLI vs JCC
471: // ZDBV8 DBProdName DB2 DB2
472: // DBProdVersion 08.01.0005 DSN08015
473: // ZDBV9 DB2 DB2
474: // 09.01.0005 DSN09015
475: // WinV9 DB2/NT DB2/NT
476: // 09.01.0000 SQL09010
477: // SolarisV9 DB2/SUN64
478: // SQL0901
479: // Linux DB2/LINUX DB2/LINUX
480: // 09.01.0000 SQL0901
481: if (databaseProductVersion.indexOf("09") != -1) {
482: maj = 9;
483: if (databaseProductVersion.indexOf("01") != -1) {
484: min = 1;
485: }
486: } else if (databaseProductVersion.indexOf("08") != -1) {
487: maj = 8;
488: min = 2;
489: if (databaseProductVersion.indexOf("01") != -1) {
490: min = 1;
491: }
492: }
493: }
494:
495: protected String getOptimizeClause(Select sel) {
496: if (sel != null && sel.getExpectedResultCount() > 0) {
497: StringBuffer buf = new StringBuffer();
498: buf.append(" ").append(optimizeClause).append(" ").append(
499: String.valueOf(sel.getExpectedResultCount()))
500: .append(" ").append(rowClause);
501: return buf.toString();
502: }
503:
504: return "";
505: }
506:
507: public OpenJPAException newStoreException(String msg,
508: SQLException[] causes, Object failed) {
509: if (causes != null && causes.length > 0)
510: msg = appendExtendedExceptionMsg(msg, causes[0]);
511: return super .newStoreException(msg, causes, failed);
512: }
513:
514: /**
515: * Append exception information from SQLCA to the exsisting
516: * exception meassage
517: */
518: private String appendExtendedExceptionMsg(String msg,
519: SQLException sqle) {
520: final String GETSQLCA = "getSqlca";
521: String exceptionMsg = new String();
522: try {
523: Method sqlcaM2 = sqle.getNextException().getClass()
524: .getMethod(GETSQLCA, null);
525: Object sqlca = sqlcaM2.invoke(sqle.getNextException(),
526: new Object[] {});
527: Method getSqlErrpMethd = sqlca.getClass().getMethod(
528: "getSqlErrp", null);
529: Method getSqlWarnMethd = sqlca.getClass().getMethod(
530: "getSqlWarn", null);
531: Method getSqlErrdMethd = sqlca.getClass().getMethod(
532: "getSqlErrd", null);
533: StringBuffer errdStr = new StringBuffer();
534:
535: int[] errds = (int[]) getSqlErrdMethd.invoke(sqlca,
536: new Object[] {});
537: for (int i = 0; i < errds.length; i++)
538: errdStr.append(errdStr.length() > 0 ? ", " : "")
539: .append(errds[i]);
540: exceptionMsg = exceptionMsg.concat("SQLCA OUTPUT"
541: + "[Errp="
542: + getSqlErrpMethd.invoke(sqlca, new Object[] {})
543: + ", Errd=" + errdStr);
544:
545: String Warn = new String((char[]) getSqlWarnMethd.invoke(
546: sqlca, new Object[] {}));
547: if (Warn.trim().length() != 0)
548: exceptionMsg = exceptionMsg.concat(", Warn=" + Warn
549: + "]");
550: else
551: exceptionMsg = exceptionMsg.concat("]");
552: msg = msg.concat(exceptionMsg);
553: return msg;
554: } catch (Throwable t) {
555: return sqle.getMessage();
556: }
557: }
558:
559: public int getDb2ServerType() {
560: return db2ServerType;
561: }
562:
563: protected void appendLength(SQLBuffer buf, int type) {
564: if (type == Types.VARCHAR)
565: buf.append("(").append(
566: Integer.toString(characterColumnSize)).append(")");
567: }
568:
569: /**
570: * If this dictionary supports XML type,
571: * use this method to append xml predicate.
572: *
573: * @param buf the SQL buffer to write the comparison
574: * @param op the comparison operation to perform
575: * @param lhs the left hand side of the comparison
576: * @param rhs the right hand side of the comparison
577: * @param lhsxml indicates whether the left operand maps to xml
578: * @param rhsxml indicates whether the right operand maps to xml
579: */
580: public void appendXmlComparison(SQLBuffer buf, String op,
581: FilterValue lhs, FilterValue rhs, boolean lhsxml,
582: boolean rhsxml) {
583: super .appendXmlComparison(buf, op, lhs, rhs, lhsxml, rhsxml);
584: if (lhsxml && rhsxml)
585: appendXmlComparison2(buf, op, lhs, rhs);
586: else if (lhsxml)
587: appendXmlComparison1(buf, op, lhs, rhs);
588: else
589: appendXmlComparison1(buf, op, rhs, lhs);
590: }
591:
592: /**
593: * Append an xml comparison predicate.
594: *
595: * @param buf the SQL buffer to write the comparison
596: * @param op the comparison operation to perform
597: * @param lhs the left hand side of the comparison (maps to xml column)
598: * @param rhs the right hand side of the comparison
599: */
600: private void appendXmlComparison1(SQLBuffer buf, String op,
601: FilterValue lhs, FilterValue rhs) {
602: boolean castrhs = false;
603: Class rc = Filters.wrap(rhs.getType());
604: int type = 0;
605: if (rhs.isConstant()) {
606: type = getJDBCType(JavaTypes.getTypeCode(rc), false);
607: castrhs = true;
608: }
609:
610: appendXmlExists(buf, lhs);
611:
612: buf.append(" ").append(op).append(" ");
613:
614: buf.append("$");
615: if (castrhs)
616: buf.append("Parm");
617: else
618: rhs.appendTo(buf);
619:
620: buf.append("]' PASSING ");
621: appendXmlVar(buf, lhs);
622: buf.append(", ");
623:
624: if (castrhs)
625: appendCast(buf, rhs, type);
626: else
627: rhs.appendTo(buf);
628:
629: buf.append(" AS \"");
630: if (castrhs)
631: buf.append("Parm");
632: else
633: rhs.appendTo(buf);
634: buf.append("\")");
635: }
636:
637: /**
638: * Append an xml comparison predicate. (both operands map to xml column)
639: *
640: * @param buf the SQL buffer to write the comparison
641: * @param op the comparison operation to perform
642: * @param lhs the left hand side of the comparison (maps to xml column)
643: * @param rhs the right hand side of the comparison (maps to xml column)
644: */
645: private void appendXmlComparison2(SQLBuffer buf, String op,
646: FilterValue lhs, FilterValue rhs) {
647: appendXmlExists(buf, lhs);
648:
649: buf.append(" ").append(op).append(" ");
650:
651: buf.append("$").append(
652: rhs
653: .getColumnAlias(rhs.getFieldMapping()
654: .getColumns()[0])).append("/*/");
655: rhs.appendTo(buf);
656:
657: buf.append("]' PASSING ");
658: appendXmlVar(buf, lhs);
659: buf.append(", ");
660: appendXmlVar(buf, rhs);
661: buf.append(")");
662: }
663:
664: private void appendXmlVar(SQLBuffer buf, FilterValue val) {
665: buf.append(
666: val
667: .getColumnAlias(val.getFieldMapping()
668: .getColumns()[0])).append(" AS ")
669: .append("\"").append(
670: val.getColumnAlias(val.getFieldMapping()
671: .getColumns()[0])).append("\"");
672: }
673:
674: private void appendXmlExists(SQLBuffer buf, FilterValue val) {
675: buf.append("XMLEXISTS('");
676: buf.append("$").append(
677: val
678: .getColumnAlias(val.getFieldMapping()
679: .getColumns()[0])).append("/*[");
680: val.appendTo(buf);
681: }
682:
683: /**
684: * add CAST for a scalar function where operand is a param
685: *
686: * @param func original string
687: * @param target substring to look for
688: * @param asString
689: * @return updated string (func)
690: */
691: private String addCastAsString(String func, String target,
692: String asString) {
693: String fstring = func;
694: if (func.indexOf(target) != -1)
695: fstring = Strings.replace(func, target, "CAST(" + target
696: + asString + ")");
697: return fstring;
698: }
699:
700: /**
701: * add CAST for a function operator where operand is a param
702: *
703: * @param func function name
704: * @param val type
705: * @return updated string (func)
706: */
707: public String addCastAsType(String func, Val val) {
708: String fstring = null;
709: String type = getTypeName(getJDBCType(JavaTypes.getTypeCode(val
710: .getType()), false));
711: if (String.class.equals(val.getType()))
712: type = type + "(" + characterColumnSize + ")";
713: fstring = "CAST(? AS " + type + ")";
714: return fstring;
715: }
716:
717: /**
718: * Return the batch limit. If the batchLimit is -1, change it to 100 for
719: * best performance
720: */
721: public int getBatchLimit() {
722: int limit = super .getBatchLimit();
723: if (limit == UNLIMITED) {
724: limit = defaultBatchLimit;
725: if (log.isTraceEnabled())
726: log.trace(_loc.get("batch_unlimit", String
727: .valueOf(limit)));
728: }
729: return limit;
730: }
731:
732: /**
733: * Return the correct CAST function syntax
734: *
735: * @param val operand of cast
736: * @param func original string
737: * @return a String with the correct CAST function syntax
738: */
739: public String getCastFunction(Val val, String func) {
740: if (val instanceof Lit || val instanceof Param)
741: if (func.indexOf("VARCHAR") == -1)
742: func = addCastAsString(func, "{0}", " AS VARCHAR(1000)");
743: return func;
744: }
745:
746: public void indexOf(SQLBuffer buf, FilterValue str,
747: FilterValue find, FilterValue start) {
748: if (find.getValue() != null) { // non constants
749: buf.append("(LOCATE(CAST((");
750: find.appendTo(buf);
751: buf.append(") AS VARCHAR(1000)), ");
752: } else {
753: // this is a constant
754: buf.append("(LOCATE(");
755: find.appendTo(buf);
756: buf.append(", ");
757: }
758: if (str.getValue() != null) {
759: buf.append("CAST((");
760: str.appendTo(buf);
761: buf.append(") AS VARCHAR(1000))");
762: } else {
763: str.appendTo(buf);
764: }
765: if (start != null) {
766: if (start.getValue() == null) {
767: buf.append(", CAST((");
768: start.appendTo(buf);
769: buf.append(") AS INTEGER) + 1");
770: } else {
771: buf.append(", ");
772: start.appendTo(buf);
773: }
774: }
775: buf.append(") - 1)");
776: }
777:
778: /**
779: * Cast the specified value to the specified type.
780: *
781: * @param buf the buffer to append the cast to
782: * @param val the value to cast
783: * @param type the type of the case, e.g. {@link Types#NUMERIC}
784: */
785: public void appendCast(SQLBuffer buf, FilterValue val, int type) {
786:
787: // Convert the cast function: "CAST({0} AS {1})"
788: int firstParam = castFunction.indexOf("{0}");
789: String pre = castFunction.substring(0, firstParam); // "CAST("
790: String mid = castFunction.substring(firstParam + 3);
791: int secondParam = mid.indexOf("{1}");
792: String post;
793: if (secondParam > -1) {
794: post = mid.substring(secondParam + 3); // ")"
795: mid = mid.substring(0, secondParam); // " AS "
796: } else
797: post = "";
798:
799: // No need to add CAST if the value is a constant
800: if (val instanceof Lit || val instanceof Param) {
801: buf.append(pre);
802: val.appendTo(buf);
803: buf.append(mid);
804: buf.append(getTypeName(type));
805: appendLength(buf, type);
806: buf.append(post);
807: } else {
808: val.appendTo(buf);
809: String sqlString = buf.getSQL(false);
810: if (sqlString.endsWith("?")) {
811: // case "(?" - convert to "CAST(? AS type"
812: String typeName = getTypeName(type);
813: if (String.class.equals(val.getType()))
814: typeName = typeName + "(" + characterColumnSize
815: + ")";
816: String str = "CAST(? AS " + typeName + ")";
817: buf.replaceSqlString(sqlString.length() - 1, sqlString
818: .length(), str);
819: }
820: }
821: }
822:
823: /**
824: * Create an index if necessary for some database tables
825: */
826: public void createIndexIfNecessary(Schema schema, String table,
827: Column pkColumn) {
828: if (isDB2ZOSV8xOrLater()) {
829: // build the index for the sequence tables
830: // the index name will the fully qualified table name + _IDX
831: Table tab = schema.getTable(table);
832: Index idx = tab.addIndex(tab.getFullName() + "_IDX");
833: idx.setUnique(true);
834: idx.addColumn(pkColumn);
835: }
836: }
837: }
|