1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18 package net.wotonomy.access;
19
20 import java.util.Enumeration;
21
22 import net.wotonomy.control.EOAndQualifier;
23 import net.wotonomy.control.EOFetchSpecification;
24 import net.wotonomy.control.EOKeyComparisonQualifier;
25 import net.wotonomy.control.EOKeyValueQualifier;
26 import net.wotonomy.control.EOOrQualifier;
27 import net.wotonomy.control.EOQualifier;
28 import net.wotonomy.control.EOSortOrdering;
29 import net.wotonomy.foundation.NSArray;
30 import net.wotonomy.foundation.NSData;
31 import net.wotonomy.foundation.NSDictionary;
32 import net.wotonomy.foundation.NSKeyValueCoding;
33 import net.wotonomy.foundation.NSMutableArray;
34 import net.wotonomy.foundation.NSMutableDictionary;
35 import net.wotonomy.foundation.NSSelector;
36 import net.wotonomy.foundation.NSTimestamp;
37 import net.wotonomy.foundation.NSTimestampFormatter;
38
39 /***
40 * @author ezamudio@nasoft.com
41 * @author $Author: cgruber $
42 * @version $Revision: 894 $
43 */
44 public abstract class EOSQLExpression {
45
46 public static final String BindVariableAttributeKey = "BindVariableAttribute";
47 public static final String BindVariableColumnKey = "BindVariableColumn";
48 public static final String BindVariableNameKey = "BindVariableName";
49 public static final String BindVariablePlaceHolderKey = "BindVariablePlaceholder";
50 public static final String BindVariableValueKey = "BindVariableValue";
51 private static int UseBindings;
52 private static final int _DefaultFormatSQLStringLength = 64;
53 private static final int _DefaultListStringLength = 256;
54 private static final int _DefaultOrderByStringLength = 128;
55 private static final int _DefaultPathLength = 128;
56 private static final int _DefaultTableListLength = 128;
57 protected static final char[] _hexChars = new char[]{
58 '0', '1', '2', '3', '4', '5', '6', '7', '8', '9', 'A', 'B', 'C', 'D', 'E', 'F'
59 };
60 private static final int _ValueLengthLimit = 40;
61 protected NSMutableDictionary _aliasesByRelationshipPath;
62 protected NSMutableDictionary _aliasesByEntityName;
63 protected NSMutableArray _bindings;
64 protected NSMutableArray _contextStack;
65 protected static NSTimestampFormatter _defaultDateFormatter;
66 protected EOEntity _entity;
67 protected StringBuffer _joinClauseString;
68 protected StringBuffer _listString;
69 protected StringBuffer _orderByString;
70 protected String _statement;
71 protected String _upperFunctionName;
72 protected boolean _useAliases = true;
73 protected static boolean _quoteExternalNames;
74 protected StringBuffer _valueListString;
75 protected String _whereClauseString;
76
77 private EOSQLExpression() {
78 super();
79 }
80
81 public EOSQLExpression(EOEntity entity) {
82 super();
83 _entity = entity;
84 }
85
86 public String _aliasForRelatedAttributeRelationshipPath(EOAttribute a, String path) {
87 return null;
88 }
89
90 public String _aliasForRelationshipPath(String path) {
91 return (String)_aliasesByRelationshipPath.objectForKey(path);
92 }
93
94 protected NSTimestampFormatter _defaultDateFormatter() {
95 return _defaultDateFormatter;
96 }
97
98 protected StringBuffer _listString() {
99 if (_listString == null)
100 _listString = new StringBuffer();
101 return _listString;
102 }
103
104 protected StringBuffer _orderByString() {
105 if (_orderByString == null)
106 _orderByString = new StringBuffer();
107 return _orderByString;
108 }
109
110 public EOEntity _rootEntityForExpression() {
111 return _entity;
112 }
113
114 public void _setEntity(EOEntity value) {
115 _entity = value;
116 }
117
118 public String _sqlStringForJoinSemanticMatchSemantic(int semantic,int match) {
119 return null;
120 }
121
122 protected String _stringForDate(NSTimestamp timestamp) {
123 return null;
124 }
125
126 protected StringBuffer _valueList() {
127 if (_valueListString == null)
128 _valueListString = new StringBuffer();
129 return _valueListString;
130 }
131
132 public void addBindVariableDictionary( NSDictionary dict ) {
133 }
134
135 /*** Adds the SQL to create the attribute to the attribute list.
136 * The appended text is of the form attr_name attr_type allow_null
137 * @param attr The attribute to create the SQL for.
138 */
139 public void addCreateClauseForAttribute(EOAttribute attr) {
140 StringBuffer buf = new StringBuffer(attr.columnName());
141 buf.append(' ');
142 buf.append(columnTypeStringForAttribute(attr));
143 buf.append(allowsNullClauseForConstraint(attr.allowsNull()));
144 appendItemToListString(buf.toString(), _listString());
145 }
146
147 public void addInsertListAttribute(EOAttribute attr, Object o) {
148 }
149
150 public void addJoinClause(String left, String right, int semantic) {
151 String s = assembleJoinClause(left, right, semantic);
152 if (_joinClauseString == null)
153 _joinClauseString = new StringBuffer();
154 if (_joinClauseString.length() > 0)
155 _joinClauseString.append(" AND ");
156 _joinClauseString.append(s);
157 }
158
159 public void addOrderByAttributeOrdering(EOSortOrdering order) {
160 String sql = sqlStringForAttributeNamed(order.key());
161 if (order.selector().equals(EOSortOrdering.CompareCaseInsensitiveAscending) || order.selector().equals(EOSortOrdering.CompareCaseInsensitiveDescending))
162 sql = "UPPER(" + sql + ")";
163 if (order.selector().equals(EOSortOrdering.CompareCaseInsensitiveAscending) || order.selector().equals(EOSortOrdering.CompareAscending))
164 sql += " ASC";
165 else
166 sql += " DESC";
167 appendItemToListString(sql, _orderByString());
168 }
169
170 public void addSelectListAttribute(EOAttribute attr) {
171 appendItemToListString(formatSQLString(sqlStringForAttribute(attr), attr.readFormat()), _listString());
172 }
173
174 public void addUpdateListAttribute(EOAttribute attr, Object o) {
175 StringBuffer buf = new StringBuffer(attr.columnName());
176 buf.append('=');
177 buf.append(formatSQLString(formatValueForAttribute(o, attr), attr.writeFormat()));
178 appendItemToListString(buf.toString(), _listString());
179 }
180
181 public NSMutableDictionary aliasesByRelationshipPath() {
182 if (_aliasesByRelationshipPath == null) {
183 _aliasesByRelationshipPath = new NSMutableDictionary();
184 if (!_useAliases)
185 return _aliasesByRelationshipPath;
186 _aliasesByRelationshipPath.setObjectForKey("t0", "");
187 }
188 return _aliasesByRelationshipPath;
189 }
190
191 public String allowsNullClauseForConstraint(boolean flag) {
192 return flag ? "" : " NOT NULL";
193 }
194
195 public void appendItemToListString(String item, StringBuffer list) {
196 if (list.length() > 0)
197 list.append(", ");
198 list.append(item);
199 }
200
201 public String assembleDeleteStatementWithQualifier(EOQualifier q, String tableList, String whereClause) {
202 String s = "DELETE FROM " + tableList;
203 if (whereClause != null && whereClause.length() > 0)
204 s += " WHERE " + whereClause;
205 return s;
206 }
207
208 public String assembleInsertStatementWithRow(NSDictionary row,
209 String tableList, String columnList, String valueList) {
210 String sql = "INSERT INTO " + tableList;
211 if (columnList != null)
212 sql += " (" + columnList + ")";
213 sql += " VALUES " + valueList;
214 return sql;
215 }
216
217 public String assembleJoinClause(String leftName, String rightName, int semantic) {
218 String op = "=";
219 if (semantic == EORelationship.LeftOuterJoin)
220 op = "*=";
221 else if (semantic == EORelationship.RightOuterJoin)
222 op = "=*";
223 return leftName + op + rightName;
224 }
225
226 public String assembleSelectStatementWithAttributes(NSArray attributes,
227 boolean lock, EOQualifier q, NSArray fetchOrder, String selectString, String columnList,
228 String tableList, String whereClause, String joinClause, String orderByClause, String lockClause) {
229 String sql = selectString + " " + columnList + " FROM " + tableList;
230 if (lockClause != null)
231 sql += " " + lockClause;
232 if (whereClause != null || joinClause != null)
233 sql += " WHERE ";
234 if (whereClause != null)
235 sql += whereClause;
236 if (whereClause != null && joinClause != null)
237 sql += " AND ";
238 if (joinClause != null)
239 sql += joinClause;
240 if (orderByClause != null)
241 sql += " ORDER BY " + orderByClause;
242 return sql;
243 }
244
245 public String assembleUpdateStatementWithRow(NSDictionary row, EOQualifier q, String tableList, String updateList, String whereClause) {
246 String s = "UPDATE " + tableList + " SET " + updateList;
247 if (whereClause != null && whereClause.length() > 0)
248 s += " WHERE " + whereClause;
249 return s;
250 }
251
252 public NSArray bindVariableDictionaries() {
253 return null;
254 }
255
256 public abstract NSMutableDictionary bindVariableDictionaryForAttribute(EOAttribute attr, Object o);
257
258 public String columnTypeStringForAttribute(EOAttribute attr) {
259 String x = attr.externalType();
260 if (attr.precision() > 0) {
261 x += " (" + attr.precision() + "," + attr.scale() + ")";
262 } else if (attr.width() > 0) {
263 x += " (" + attr.width() + ")";
264 }
265 return x;
266 }
267
268 public EOEntity entity() {
269 return _entity;
270 }
271
272 public String externalNameQuoteCharacter() {
273 return "\"";
274 }
275
276 public String formatSQLString(String value, String format) {
277 if (format == null)
278 return value;
279 return value;
280 }
281
282 /***
283 * Returns the received string wrapped in single quotes,
284 * with any quotes or escape chars found inside it
285 * properly escaped.
286 * @param s The string to format.
287 */
288 public String formatStringValue(String s) {
289 StringBuffer buf = new StringBuffer(s);
290 for (int i = buf.length()-1; i >= 0; i--) {
291 if (buf.charAt(i) == sqlEscapeChar()) {
292 buf.insert(i, sqlEscapeChar());
293 i++;
294 }
295 if (buf.charAt(i) == '\'') {
296 buf.insert(i, sqlEscapeChar());
297 i++;
298 }
299 }
300 buf.append('\'');
301 buf.insert(0, '\'');
302 return buf.toString();
303 }
304
305 public String formatValueForAttribute(Object value, EOAttribute attr) {
306 if (value == null || value == NSKeyValueCoding.NullValue)
307 return "NULL";
308 if (value instanceof String)
309 return formatStringValue((String)value);
310 if (value instanceof Number)
311 return sqlStringForNumber((Number)value);
312
313 return value.toString();
314 }
315
316 public String joinClauseString() {
317 if (_joinClauseString == null)
318 return null;
319 return _joinClauseString.toString();
320 }
321
322 public void joinExpression() {
323 _joinClauseString = null;
324 if (_aliasesByEntityName.count() > 1) {
325 }
326 }
327
328 public String listString() {
329 return _listString().toString();
330 }
331
332 public String lockClause() {
333 return "";
334 }
335
336 public boolean mustUseBindVariableForAttribute(EOAttribute attr) {
337 return false;
338 }
339
340 public String orderByString() {
341 if (_orderByString == null)
342 return null;
343 return _orderByString.toString();
344 }
345
346 public void prepareConstraintStatementForRelationship(EORelationship rel, NSArray arr1, NSArray arr2) {
347 }
348
349 public void prepareDeleteExpressionForQualifier(EOQualifier q) {
350 String where = null;
351 setStatement(assembleDeleteStatementWithQualifier(q, _entity.externalName(), where));
352 }
353
354 public void prepareInsertExpressionWithRow(NSDictionary row) {
355 StringBuffer cols = new StringBuffer("(");
356 StringBuffer values = new StringBuffer("(");
357 Enumeration enumeration = row.keyEnumerator();
358 while (enumeration.hasMoreElements()) {
359 String key = (String)enumeration.nextElement();
360 EOAttribute a = _entity.attributeNamed(key);
361 cols.append(a.columnName());
362 values.append(formatValueForAttribute(row.objectForKey(key), a));
363 if (enumeration.hasMoreElements()) {
364 cols.append(", ");
365 values.append(", ");
366 }
367 }
368 cols.append(")");
369 cols.append(")");
370 setStatement(assembleInsertStatementWithRow(row, _entity.externalName(), cols.toString(), values.toString()));
371 }
372
373 public void prepareSelectExpressionWithAttributes(NSArray atts, boolean lock, EOFetchSpecification fspec) {
374 _aliasesByRelationshipPath = new NSMutableDictionary();
375 _aliasesByEntityName = new NSMutableDictionary();
376 EOQualifier q = null;
377 NSArray order = null;
378 if (fspec != null) {
379 q = fspec.qualifier();
380 order = fspec.sortOrderings();
381 }
382
383 for (int i = 0; i < atts.count(); i++)
384 addSelectListAttribute((EOAttribute)atts.objectAtIndex(i));
385
386 if (q != null) {
387 if (q instanceof EOQualifierSQLGeneration)
388 setWhereClauseString(sqlStringForQualifier((EOQualifierSQLGeneration)q));
389 else {
390 EOQualifierSQLGeneration.Support sup = EOQualifierSQLGeneration.Support.supportForClass(q.getClass());
391 setWhereClauseString(sup.sqlStringForSQLExpression(q, this));
392 }
393 }
394
395 joinExpression();
396
397 if (order != null && order.count() > 0) {
398 for (int i = 0; i < order.count(); i++) {
399 EOSortOrdering so = (EOSortOrdering)order.objectAtIndex(i);
400 addOrderByAttributeOrdering(so);
401 }
402 }
403
404 setStatement(assembleSelectStatementWithAttributes(atts, lock, q, order, "SELECT", listString(),
405 tableListWithRootEntity(_entity), whereClauseString(), joinClauseString(), orderByString(), lockClause()));
406 }
407
408 /*** Build an UPDATE statement with the given information. */
409 public void prepareUpdateExpressionWithRow(NSDictionary row, EOQualifier q) {
410 StringBuffer buf = new StringBuffer();
411 Enumeration enumeration = row.keyEnumerator();
412 while (enumeration.hasMoreElements()) {
413 String key = (String)enumeration.nextElement();
414 EOAttribute a = _entity.attributeNamed(key);
415 if (a == null)
416 throw new EOGeneralAdaptorException("Cannot find attribute named " + key + " in entity " + _entity.name());
417 buf.append(a.columnName());
418 buf.append('=');
419 buf.append(formatValueForAttribute(row.objectForKey(key), a));
420 if (enumeration.hasMoreElements())
421 buf.append(", ");
422 }
423 if (q != null) {
424 setWhereClauseString(sqlStringForQualifier(null));
425 }
426 setStatement(assembleUpdateStatementWithRow(row, q, _entity.externalName(), buf.toString(), whereClauseString()));
427 }
428
429 public void setStatement(String statement) {
430 _statement = statement;
431 }
432
433 public String statement() {
434 return _statement;
435 }
436
437 public void setUseAliases(boolean flag) {
438 _useAliases = flag;
439 }
440
441 public boolean useAliases() {
442 return _useAliases;
443 }
444
445 public void setUseBindVariables(boolean flag) {
446 }
447
448 public boolean useBindVariables() {
449 return System.getProperty("EOAdaptorUseBindVariables", "false").equals("true");
450 }
451
452 /*** @deprecated Check externalNameQuoteCharacter instead. */
453 public static void setUseQuotedExternalNames(boolean flag) {
454 _quoteExternalNames = flag;
455 }
456 /*** @deprecated Use the instance method externalNameQuoteCharacter instead. */
457 public static boolean useQuotedExternalNames() {
458 return _quoteExternalNames;
459 }
460
461 public void setWhereClauseString(String clause) {
462 _whereClauseString = clause;
463 }
464
465 public String whereClauseString() {
466 return _whereClauseString;
467 }
468
469 public boolean shouldUseBindVariableForAttribute(EOAttribute attr) {
470 return false;
471 }
472
473 public char sqlEscapeChar() {
474 return '//';
475 }
476
477 public String sqlPatternFromShellPattern(String pattern) {
478 return sqlPatternFromShellPatternWithEscapeCharacter(pattern, sqlEscapeChar());
479 }
480
481 public String sqlPatternFromShellPatternWithEscapeCharacter(String pattern, char escape) {
482 StringBuffer buf = new StringBuffer(pattern);
483 int idx = 0;
484
485 do {
486 idx = buf.indexOf("%");
487 if (idx == 0)
488 buf.insert(escape, 0);
489 else if (idx > 0 && buf.charAt(idx-1) != escape)
490 buf.insert(escape, idx);
491 } while (idx >= 0);
492
493 do {
494 idx = buf.indexOf("_");
495 if (idx == 0)
496 buf.insert(escape, 0);
497 else if (idx > 0 && buf.charAt(idx-1) != escape)
498 buf.insert(escape, idx);
499 } while (idx >= 0);
500
501 do {
502 idx = buf.indexOf("*");
503 if (idx >= 0)
504 buf.replace(idx, idx+1, "%");
505 } while (idx >= 0);
506
507 do {
508 idx = buf.indexOf("?");
509 if (idx >= 0)
510 buf.replace(idx, idx+1, "_");
511 } while (idx >= 0);
512 return buf.toString();
513 }
514
515 public String sqlStringForAttribute(EOAttribute attr) {
516 if (_aliasesByEntityName == null)
517 _aliasesByEntityName = new NSMutableDictionary();
518 String alias = (String)_aliasesByEntityName.objectForKey(attr.entity().name());
519 if (alias == null) {
520 alias = "t" + (_aliasesByEntityName.count() + 1);
521 _aliasesByEntityName.setObjectForKey(alias, attr.entity().name());
522 }
523 if (useAliases())
524 return alias + "." + attr.columnName();
525 else
526 return attr.entity().externalName() + "." + attr.columnName();
527 }
528
529 public String sqlStringForAttributeNamed(String name) {
530 if (name.indexOf('.') > 0) {
531 return sqlStringForAttribute(_entity._attributeForPath(name));
532 }
533 return sqlStringForAttribute(_entity.attributeNamed(name));
534
535 }
536
537 /***
538 * Returns a string representing the path from the first
539 * relationship in the array to the last one.
540 * @param path An array of EORelationship objects.
541 * @return A string consisting of the names of the relationships
542 * separated by dots.
543 */
544 public String sqlStringForAttributePath(NSArray path) {
545 StringBuffer buf = new StringBuffer();
546 for (int i = 0; i < path.count(); i++) {
547 EORelationship rel = (EORelationship)path.objectAtIndex(i);
548 if (i > 0)
549 buf.append('.');
550 buf.append(rel.name());
551 }
552 return buf.toString();
553 }
554
555 public String sqlStringForCaseInsensitiveLike(String key, String value) {
556 return "LOWER(" + key + ") LIKE LOWER(" + sqlPatternFromShellPattern(value) + ")";
557 }
558
559 public String sqlStringForConjoinedQualifiers(NSArray qualifiers) {
560 EOAndQualifier q = new EOAndQualifier(qualifiers);
561 return EOQualifierSQLGeneration.Support.supportForClass(EOAndQualifier.class).sqlStringForSQLExpression(q, this);
562 }
563
564 public String sqlStringForData(NSData data) {
565 byte[] b = data.bytes();
566 char[] c = new char[b.length * 2];
567 int pos = 0;
568 for (int i = 0; i < b.length; i++) {
569 int x = (b[i] & 0xf0) >> 4;
570 c[pos++] = _hexChars[x];
571 x = (b[i] & 0x0f);
572 c[pos++] = _hexChars[x];
573 }
574 return new String(c);
575 }
576
577 public String sqlStringForDisjoinedQualifiers(NSArray qualifiers) {
578 EOOrQualifier q = new EOOrQualifier(qualifiers);
579 return EOQualifierSQLGeneration.Support.supportForClass(EOOrQualifier.class).sqlStringForSQLExpression(q, this);
580 }
581
582 public String sqlStringForKeyComparisonQualifier(EOKeyComparisonQualifier q) {
583 return EOQualifierSQLGeneration.Support.supportForClass(EOKeyComparisonQualifier.class).sqlStringForSQLExpression(q, this);
584 }
585
586 public String sqlStringForKeyValueQualifier(EOKeyValueQualifier q) {
587 return EOQualifierSQLGeneration.Support.supportForClass(EOKeyValueQualifier.class).sqlStringForSQLExpression(q, this);
588 }
589
590 public String sqlStringForNegatedQualifier(EOQualifier q) {
591 EOQualifierSQLGeneration.Support sup = EOQualifierSQLGeneration.Support.supportForClass(q.getClass());
592 String sql = sup.sqlStringForSQLExpression(q, this);
593 return "NOT (" + sql + ")";
594 }
595
596 public static String sqlStringForNumber(Number number) {
597 return number.toString();
598 }
599
600 public String sqlStringForQualifier(EOQualifierSQLGeneration sql) {
601 return sql.sqlStringForSQLExpression(this);
602 }
603
604 public String sqlStringForSchemaObjectName(String name) {
605 return name;
606 }
607
608 public String sqlStringForSelector(NSSelector sel, Object value) {
609 if (sel == EOQualifier.QualifierOperatorEqual) {
610 if (value == NSKeyValueCoding.NullValue)
611 return " is ";
612 return "=";
613 } else if (sel == EOQualifier.QualifierOperatorNotEqual) {
614 if (value == NSKeyValueCoding.NullValue)
615 return " is not ";
616 return "<>";
617 } else if (sel == EOQualifier.QualifierOperatorLessThan) {
618 return "<";
619 } else if (sel == EOQualifier.QualifierOperatorGreaterThan) {
620 return ">";
621 } else if (sel == EOQualifier.QualifierOperatorLessThanOrEqualTo) {
622 return "<=";
623 } else if (sel == EOQualifier.QualifierOperatorGreaterThanOrEqualTo) {
624 return ">=";
625 } else if (sel == EOQualifier.QualifierOperatorLike || sel == EOQualifier.QualifierOperatorCaseInsensitiveLike) {
626 return " like ";
627 }
628 return sel.name();
629 }
630
631 public static String sqlStringForString(String s) {
632 return s;
633 }
634
635 public String sqlStringForValue(Object value, String keyPath) {
636 EOAttribute a = _entity._attributeForPath(keyPath);
637 return formatValueForAttribute(value, a);
638 }
639
640 public String tableListWithRootEntity(EOEntity root) {
641 StringBuffer buf = new StringBuffer(root.externalName());
642 if (useAliases()) {
643 buf.append(" ");
644 buf.append(_aliasesByEntityName.objectForKey(root.name()));
645 }
646 if (_aliasesByEntityName.count() > 0) {
647 Enumeration enumeration = _aliasesByEntityName.keyEnumerator();
648 while (enumeration.hasMoreElements()) {
649 String key = (String)enumeration.nextElement();
650 if (!key.equals(root.name())) {
651 buf.append(", ");
652 buf.append(key);
653 if (useAliases())
654 buf.append(_aliasesByEntityName.objectForKey(key));
655 }
656 }
657 }
658 return buf.toString();
659 }
660
661 public String toString() {
662 return "<" + getClass().getName() + "> " + statement();
663 }
664
665 public String valueList() {
666 return _valueList().toString();
667 }
668
669 }
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697