|
1
17 package org.apache.ws.jaxme.sqls.impl;
18
19 import java.util.ArrayList  ;
20 import java.util.Collection ;
21 import java.util.Collections ;
22 import java.util.Iterator ;
23 import java.util.List ;
24
25 import org.apache.ws.jaxme.sqls.BinaryColumn;
26 import org.apache.ws.jaxme.sqls.BooleanConstraint;
27 import org.apache.ws.jaxme.sqls.Case;
28 import org.apache.ws.jaxme.sqls.Column;
29 import org.apache.ws.jaxme.sqls.ColumnReference;
30 import org.apache.ws.jaxme.sqls.CombinedConstraint;
31 import org.apache.ws.jaxme.sqls.Constraint;
32 import org.apache.ws.jaxme.sqls.DeleteStatement;
33 import org.apache.ws.jaxme.sqls.Expression;
34 import org.apache.ws.jaxme.sqls.ForeignKey;
35 import org.apache.ws.jaxme.sqls.Function;
36 import org.apache.ws.jaxme.sqls.Index;
37 import org.apache.ws.jaxme.sqls.RawSQLCode;
38 import org.apache.ws.jaxme.sqls.SQLGenerator;
39 import org.apache.ws.jaxme.sqls.InsertStatement;
40 import org.apache.ws.jaxme.sqls.JoinReference;
41 import org.apache.ws.jaxme.sqls.Schema;
42 import org.apache.ws.jaxme.sqls.SelectStatement;
43 import org.apache.ws.jaxme.sqls.SelectTableReference;
44 import org.apache.ws.jaxme.sqls.SetStatement;
45 import org.apache.ws.jaxme.sqls.Statement;
46 import org.apache.ws.jaxme.sqls.StringColumn;
47 import org.apache.ws.jaxme.sqls.Table;
48 import org.apache.ws.jaxme.sqls.TableReference;
49 import org.apache.ws.jaxme.sqls.UpdateStatement;
50 import org.apache.ws.jaxme.sqls.Value;
51
52
53
57 public class SQLGeneratorImpl implements SQLGenerator {
58 private String statementTerminator, lineTerminator;
59
60 public String getStatementTerminator() {
61 return statementTerminator;
62 }
63
64 public void setStatementTerminator(String pStatementTerminator) {
65 statementTerminator = pStatementTerminator;
66 }
67
68 public String getLineTerminator() {
69 return lineTerminator;
70 }
71
72 public void setLineTerminator(String pLineTerminator) {
73 lineTerminator = pLineTerminator;
74 }
75
76 protected String newStatement(String pStatement) {
77 String s = getStatementTerminator();
78 return s == null ? pStatement : pStatement + s;
79 }
80
81 public Collection getCreate(Schema pSchema) {
82 if (pSchema.getName() == null) {
83 return Collections.EMPTY_LIST;
85 }
86 List result = new ArrayList ();
87 result.add(newStatement("CREATE SCHEMA " + pSchema.getName()));
88 return result;
89 }
90
91 public Collection getDrop(Schema pSchema) {
92 if (pSchema.getName() == null) {
93 return Collections.EMPTY_LIST;
95 }
96 List result = new ArrayList ();
97 result.add(newStatement("DROP SCHEMA " + pSchema.getName()));
98 return result;
99 }
100
101 protected String getIndent() { return " "; }
102
103 protected String getTypeName(Column.Type pType) {
104 if (pType.equals(Column.Type.BIGINT)) {
105 return "BIGINT";
106 } else if (pType.equals(Column.Type.BINARY)) {
107 return "BINARY";
108 } else if (pType.equals(Column.Type.BIT)) {
109 return "BIT";
110 } else if (pType.equals(Column.Type.CHAR)) {
111 return "CHAR";
112 } else if (pType.equals(Column.Type.DATE)) {
113 return "DATE";
114 } else if (pType.equals(Column.Type.DOUBLE)) {
115 return "DOUBLE";
116 } else if (pType.equals(Column.Type.FLOAT)) {
117 return "FLOAT";
118 } else if (pType.equals(Column.Type.INTEGER)) {
119 return "INT";
120 } else if (pType.equals(Column.Type.SMALLINT)) {
121 return "SMALLINT";
122 } else if (pType.equals(Column.Type.TIME)) {
123 return "TIME";
124 } else if (pType.equals(Column.Type.TIMESTAMP)) {
125 return "TIMESTAMP";
126 } else if (pType.equals(Column.Type.TINYINT)) {
127 return "TINYINT";
128 } else if (pType.equals(Column.Type.VARBINARY)) {
129 return "VARBINARY";
130 } else if (pType.equals(Column.Type.VARCHAR)) {
131 return "VARCHAR";
132 } else if (pType.equals(Column.Type.CLOB)) {
133 return "CLOB";
134 } else if (pType.equals(Column.Type.BLOB)) {
135 return "BLOB";
136 } else if (pType.equals(Column.Type.OTHER)) {
137 return "BLOB";
138 } else {
139 throw new IllegalStateException ("Unknown column type: " + pType);
140 }
141 }
142
143 protected String getCreate(Column pColumn) {
144 StringBuffer sb = new StringBuffer ();
145 sb.append(pColumn.getName()).append(" ");
146 Column.Type type = pColumn.getType();
147 Long length = null;
148 if (type.equals(Column.Type.BINARY)
149 || type.equals(Column.Type.VARBINARY)
150 || type.equals(Column.Type.BLOB)
151 || type.equals(Column.Type.OTHER)) {
152 length = ((BinaryColumn) pColumn).getLength();
153 if (length == null) {
154 throw new IllegalStateException ("The length of column " + pColumn.getQName() + " is not set.");
155 }
156 } else if (type.equals(Column.Type.CHAR)
157 || type.equals(Column.Type.VARCHAR)
158 || type.equals(Column.Type.CLOB)) {
159 length = ((StringColumn) pColumn).getLength();
160 if (length == null) {
161 throw new IllegalStateException ("The length of column " + pColumn.getQName() + " is not set.");
162 }
163 }
164 sb.append(getTypeName(type));
165 if (length != null) {
166 sb.append("(").append(length).append(")");
167 }
168 if (!pColumn.isNullable()) {
169 sb.append(" NOT NULL");
170 }
171 return sb.toString();
172 }
173
174
180 protected boolean isPrimaryKeyUniqueIndex() { return false; }
181
182
185 protected boolean isPrimaryKeyPartOfCreateTable() { return false; }
186
187
190 protected boolean isUniqueIndexPartOfCreateTable() { return false; }
191
192
195 protected boolean isNonUniqueIndexPartOfCreateTable() { return false; }
196
197
200 protected boolean isForeignKeyPartOfCreateTable() { return false; }
201
202 protected String createPrimaryKeyAsPartOfCreateTable(Table pTable) {
203 Index index = pTable.getPrimaryKey();
204 if (index == null) {
205 return null;
206 }
207 StringBuffer sb = new StringBuffer ();
208 sb.append("PRIMARY KEY").append(" (");
209 boolean first = true;
210 for (Iterator iter = index.getColumns(); iter.hasNext(); ) {
211 if (first) {
212 first = false;
213 } else {
214 sb.append(", ");
215 }
216 sb.append(((Column) iter.next()).getName().getName());
217 }
218 sb.append(")");
219 return sb.toString();
220 }
221
222 protected String createIndexAsPartOfCreateTable(Index pIndex) {
223 StringBuffer sb = new StringBuffer ();
224 sb.append(pIndex.isUnique() ? "UNIQUE" : "KEY").append(" (");
225 boolean first = true;
226 for (Iterator iter = pIndex.getColumns(); iter.hasNext(); ) {
227 if (first) {
228 first = false;
229 } else {
230 sb.append(", ");
231 }
232 sb.append(((Column) iter.next()).getName().getName());
233 }
234 sb.append(")");
235 return sb.toString();
236 }
237
238 protected String createForeignKeyAsPartOfCreateTable(ForeignKey pKey) {
239 StringBuffer sb = new StringBuffer ();
240 sb.append("FOREIGN KEY (");
241 boolean first = true;
242 Iterator iter = pKey.getColumnLinks();
243 if (!iter.hasNext()) {
244 throw new IllegalStateException ("Foreign key on " +
245 pKey.getTable().getQName() +
246 " referencing " +
247 pKey.getReferencedTable().getQName() +
248 " doesn't have any columns.");
249 }
250 while (iter.hasNext()) {
251 ForeignKey.ColumnLink link = (ForeignKey.ColumnLink) iter.next();
252 if (first) {
253 first = false;
254 } else {
255 sb.append(", ");
256 }
257 sb.append(link.getLocalColumn().getName().getName());
258 }
259 sb.append(") REFERENCES ");
260 sb.append(pKey.getReferencedTable().getQName());
261 sb.append(" (");
262 first = true;
263 iter = pKey.getColumnLinks();
264 while (iter.hasNext()) {
265 ForeignKey.ColumnLink link = (ForeignKey.ColumnLink) iter.next();
266 if (first) {
267 first = false;
268 } else {
269 sb.append(", ");
270 }
271 sb.append(link.getReferencedColumn().getName().getName());
272 }
273 sb.append(")");
274
275 ForeignKey.Mode deleteMode = pKey.getOnDelete();
276 if (deleteMode != null) {
277 if (ForeignKey.Mode.CASCADE.equals(deleteMode)) {
278 sb.append(" ON DELETE CASCADE");
279 } else if (ForeignKey.Mode.REJECT.equals(deleteMode)) {
280 sb.append(" ON DELETE RESTRICT");
281 } else if (ForeignKey.Mode.SETNULL.equals(deleteMode)) {
282 sb.append(" ON DELETE SET NULL");
283 } else {
284 throw new IllegalStateException ("Unknown foreign key mode for ON DELETE: " + deleteMode);
285 }
286 }
287 ForeignKey.Mode updateMode = pKey.getOnUpdate();
288 if (updateMode != null) {
289 if (ForeignKey.Mode.CASCADE.equals(updateMode)) {
290 sb.append(" ON UPDATE CASCADE");
291 } else if (ForeignKey.Mode.REJECT.equals(updateMode)) {
292 sb.append(" ON UPDATE RESTRICT");
293 } else {
294 throw new IllegalStateException ("Unknown foreign key mode for ON UPDATE: " + updateMode);
295 }
296 }
297
298 return sb.toString();
299 }
300
301 protected String getCreateTableHeader(Table pTable) {
302 return "CREATE TABLE " + pTable.getQName();
303 }
304
305 public Collection getCreate(Table pTable) {
306 String lf = getLineTerminator() == null ? "" : getLineTerminator();
307 String indent = lf == null ? "" : getIndent();
308 StringBuffer sb = new StringBuffer ();
309 sb.append(getCreateTableHeader(pTable)).append(" (");
310 String s = lf + indent;
311 for (Iterator iter = pTable.getColumns(); iter.hasNext(); ) {
312 sb.append(s).append(getCreate((Column) iter.next()));
313 s = "," + lf + indent;
314 }
315
316 for (Iterator iter = pTable.getIndexes(); iter.hasNext(); ) {
317 Index index = (Index) iter.next();
318 String st;
319 if (index.isPrimaryKey() && !isPrimaryKeyUniqueIndex()) {
320 if (!isPrimaryKeyPartOfCreateTable()) {
321 continue;
322 }
323 st = createPrimaryKeyAsPartOfCreateTable(pTable);
324 } else if (index.isUnique()) {
325 if (!isUniqueIndexPartOfCreateTable()) {
326 continue;
327 }
328 st = createIndexAsPartOfCreateTable(index);
329 } else {
330 if (!isNonUniqueIndexPartOfCreateTable()) {
331 continue;
332 }
333 st = createIndexAsPartOfCreateTable(index);
334 }
335 if (st != null) {
336 sb.append(s).append(st);
337 }
338 }
339 if (isForeignKeyPartOfCreateTable()) {
340 for (Iterator iter = pTable.getForeignKeys(); iter.hasNext(); ) {
341 ForeignKey key = (ForeignKey) iter.next();
342 String st = createForeignKeyAsPartOfCreateTable(key);
343 if (st != null) {
344 sb.append(s).append(st);
345 }
346 }
347 }
348 sb.append(lf).append(")").append(lf);
349 List result = new ArrayList ();
350 result.add(newStatement(sb.toString()));
351 return result;
352 }
353
354 public Collection getDrop(Table pTable) {
355 List result = new ArrayList ();
356 result.add(newStatement("DROP TABLE " + pTable.getQName()));
357 return result;
358 }
359
360 private ColumnReference[] getSetStatementsColumns(SetStatement pStatement) {
361 List result = new ArrayList ();
362 for (Iterator iter = pStatement.getSetValues(); iter.hasNext(); ) {
363 SetStatement.SetValue setValue = (SetStatement.SetValue) iter.next();
364 result.add(setValue.getColumnReference());
365 }
366 return (ColumnReference[]) result.toArray(new ColumnReference[result.size()]);
367 }
368
369 private ColumnReference[] getInsertStatementsColumns(InsertStatement pStatement) {
370 SelectStatement subSelect = pStatement.getSubSelect();
371 if (subSelect == null) {
372 return getSetStatementsColumns(pStatement);
373 } else {
374 List result = new ArrayList ();
375 for (Iterator iter = subSelect.getResultColumns(); iter.hasNext(); ) {
376 ColumnReference cRef = (ColumnReference) iter.next();
377 Column.Name name;
378 if (cRef.getAlias() == null) {
379 name = cRef.getColumn().getName();
380 } else {
381 name = cRef.getAlias();
382 }
383 Column col = pStatement.getTableReference().getTable().getColumn(name);
384 if (col == null) {
385 throw new IllegalStateException ("A result column " + name
386 + " is used in the subselect, which is not present in the insert statements table.");
387 }
388 result.add(pStatement.getTableReference().newColumnReference(col));
389 }
390 return (ColumnReference[]) result.toArray(new ColumnReference[result.size()]);
391 }
392 }
393
394 private void addSetValuesToInsertStatement(StringBuffer pBuffer,
395 StatementMetaData pData,
396 InsertStatement pQuery) {
397 pBuffer.append(" VALUES (");
398 boolean first = true;
399 for (Iterator iter = pQuery.getSetValues(); iter.hasNext(); ) {
400 if (first) {
401 first = false;
402 } else {
403 pBuffer.append(", ");
404 }
405 SetStatement.SetValue setValue = (SetStatement.SetValue) iter.next();
406 Object o = setValue.getValue();
407 String s = getBooleanConstraintPart(pData, o);
408 pBuffer.append(s);
409 }
410 pBuffer.append(")");
411 }
412
413 private void addSubSelectToInsertStatement(StringBuffer pBuffer, StatementMetaData pData,
414 InsertStatement pStatement) {
415 pBuffer.append(" (");
416 pBuffer.append(getSelectQuery(pStatement.getSubSelect(), pData));
417 pBuffer.append(")");
418 }
419
420 public String getInsertQuery(InsertStatement pQuery) {
421 ColumnReference[] columns = getInsertStatementsColumns(pQuery);
422 StatementMetaData smd = newStatementMetaData(pQuery, columns);
423
424 StringBuffer result = new StringBuffer ();
425 result.append("INSERT INTO ");
426 result.append(getTableAlias(smd, pQuery.getTableReference()));
427 if (columns.length > 0) {
428 result.append(" (");
429 for (int i = 0; i < columns.length; i++) {
430 if (i > 0) {
431 result.append(", ");
432 }
433 result.append(getColumnAlias(smd, columns[i]));
434 }
435 result.append(")");
436 }
437 boolean hasSetValues = pQuery.getSetValues().hasNext();
438 boolean hasSubselect = pQuery.getSubSelect() != null;
439 if (hasSetValues) {
440 if (hasSubselect) {
441 throw new IllegalStateException ("Using values (InsertStatement.addFoo(..)) and subselects (InsertStatement.setSubSelect(...)) is mutually exclusive.");
442 } else {
443 addSetValuesToInsertStatement(result, smd, pQuery);
444 }
445 } else {
446 if (hasSubselect) {
447 addSubSelectToInsertStatement(result, smd, pQuery);
448 } else {
449 throw new IllegalStateException ("Neither values (InsertStatement.addFoo(..)) nor a subselect (InsertStatement.setSubSelect(...)) are set.");
450 }
451 }
452 return newStatement(result.toString());
453 }
454
455 protected String getValue(Value pValue) {
456 Value.Type type = pValue.getType();
457 Object o = pValue.getValue();
458 if (Value.Type.BOOLEAN.equals(type)) {
459 return o == null ? "null" : (((Boolean ) o).booleanValue() ? "TRUE" : "FALSE");
460 } else if (Value.Type.BYTE.equals(type) ||
461 Value.Type.SHORT.equals(type) ||
462 Value.Type.INT.equals(type) ||
463 Value.Type.LONG.equals(type) ||
464 Value.Type.FLOAT.equals(type) ||
465 Value.Type.DOUBLE.equals(type)) {
466 return o == null ? "null" : o.toString();
467 } else if (Value.Type.DATE.equals(type) ||
468 Value.Type.DATETIME.equals(type) ||
469 Value.Type.TIME.equals(type)) {
470 throw new IllegalStateException ("Date/time handling not yet implemented.");
471 } else if (Value.Type.DATE.equals(type)) {
472 throw new IllegalStateException ("Date handling not yet implemented.");
473 } else if (Value.Type.PLACEHOLDER.equals(type)) {
474 return "?";
475 } else if (Value.Type.STRING.equals(type)) {
476 return o == null ? "null" : getEscapedString(o.toString());
477 } else if (Value.Type.NULL.equals(type)) {
478 return "null";
479 } else {
480 throw new IllegalStateException ("Unknown value type: " + type);
481 }
482 }
483
484 protected String getUpdateQuery(UpdateStatement pQuery) {
485 ColumnReference[] columns = getSetStatementsColumns(pQuery);
486 StatementMetaData smd = newStatementMetaData(pQuery, columns);
487
488 StringBuffer sb = new StringBuffer ();
489 sb.append("UPDATE ");
490 sb.append(getTableAlias(smd, pQuery.getTableReference()));
491 sb.append(" SET ");
492 boolean first = true;
493 for (Iterator iter = pQuery.getSetValues(); iter.hasNext(); ) {
494 UpdateStatement.SetValue setValue = (UpdateStatement.SetValue) iter.next();
495 if (first) {
496 first = false;
497 } else {
498 sb.append(", ");
499 }
500 sb.append(getColumnAlias(smd, setValue.getColumnReference()));
501 sb.append("=");
502 sb.append(getBooleanConstraintPart(smd, setValue.getValue()));
503 }
504 String s = getWhereClause(smd, pQuery.getWhere());
505 if (s != null) {
506 sb.append(" WHERE ").append(s);
507 }
508 return newStatement(sb.toString());
509 }
510
511 protected String getDeleteQuery(DeleteStatement pQuery) {
512 StatementMetaData smd = newStatementMetaData(pQuery);
513
514 StringBuffer result = new StringBuffer ("DELETE FROM ");
515 result.append(getTableAlias(smd, pQuery.getTableReference()));
516 String s = getWhereClause(smd, pQuery.getWhere());
517 if (s != null) {
518 result.append(" WHERE ");
519 result.append(s);
520 }
521 return result.toString();
522 }
523
524 protected boolean isQualifiedColumn(StatementMetaData pData, ColumnReference pColumn) {
525 if (pData == null) {
526 return false;
527 }
528 Integer num = (Integer ) pData.getColumnNames().get(pColumn.getColumn().getName().toString().toUpperCase());
529 if (num == null) {
530 throw new IllegalStateException ("Column not in map of column counts: "
531 + pColumn.getColumn().getName());
532 }
533 return num.intValue() > 1;
534 }
535
536 protected String getFunction(StatementMetaData pData, Function f) {
537 return f.getName() + '(' + getParts(pData, f.getParts()) + ')';
538 }
539
540 protected String getColumnAlias(StatementMetaData pData, ColumnReference pColumn) {
541 Column col = pColumn.getColumn();
542 String s = col.getName().toString();
543 if (col.isVirtual()) {
544 VirtualColumn virtCol = (VirtualColumn) col;
545 Object o = virtCol.getValue();
546 if (o instanceof SelectStatement) {
547 return "(" + getSelectQuery((SelectStatement) o, pData) + ") AS " + s;
548 } else if (o instanceof Function) {
549 return getFunction(pData, (Function) o) + " AS " + s;
550 } else if (o instanceof String ) {
551 return ((String ) o) + " AS " + s;
552 } else {
553 throw new IllegalStateException ("Invalid type of VirtualColumn: " + o);
554 }
555 } else {
556 if (isQualifiedColumn(pData, pColumn)) {
557 TableReference tableReference = pColumn.getTableReference();
558 if (tableReference.getAlias() != null) {
559 s = tableReference.getAlias().getName() + "." + s;
560 } else {
561 s = tableReference.getTable().getName() + "." + s;
562 }
563 }
564
565 if (pColumn.getAlias() != null) {
566 s = s + " AS " + pColumn.getAlias().getName();
567 }
568 }
569 return s;
570 }
571
572 protected String getColumnAlias(StatementMetaData pData, ColumnReference[] pColumn) {
573 StringBuffer sb = new StringBuffer ("(");
574 for (int i = 0; i < pColumn.length; i++) {
575 if (i > 0) sb.append(", ");
576 sb.append(getColumnAlias(pData, pColumn[i]));
577 }
578 sb.append(")");
579 return sb.toString();
580 }
581
582 protected boolean isTableAliasUsingAs() { return true; }
583
584 protected String getTableAlias(StatementMetaData pData, TableReference pTable) {
585 Table t = pTable.getTable();
586 String tableName;
587 Table.Name alias = pTable.getAlias();
588 if (t instanceof ViewImpl) {
589 ViewImpl v = (ViewImpl) t;
590 tableName = "(" + getSelectQuery(v.getViewStatement(), pData) + ")";
591 } else {
592 tableName = pTable.getTable().getQName();
593 }
594 if (alias == null) {
595 return tableName;
596 } else {
597 if (isTableAliasUsingAs()) {
598 return tableName + " AS " + alias.getName();
599 } else {
600 return tableName + " " + alias.getName();
601 }
602 }
603 }
604
605 protected String getJoinAlias(StatementMetaData pData, JoinReference pJoinReference) {
606 StringBuffer result = new StringBuffer ();
607 if (pJoinReference.isLeftOuterJoin()) {
608 result.append(" LEFT OUTER JOIN ");
609 } else if (pJoinReference.isJoin()) {
610 result.append(" JOIN ");
611 } else {
612 throw new IllegalStateException ("Unknown join type");
613 }
614 result.append(getTableAlias(pData, pJoinReference));
615 String s = getWhereClause(pData, pJoinReference.getOn());
616 if (s != null) {
617 result.append(" ON ");
618 result.append(s);
619 }
620 return result.toString();
621 }
622
623 protected String getEscapedString(String s) {
624 if (s.indexOf('\n') > -1 || s.indexOf('\r') > -1 || s.indexOf('\f') > -1) {
625 throw new IllegalArgumentException ("Don't know how to handle line or page terminators.");
626 }
627 if (s.indexOf('\'') > -1) {
628 throw new IllegalArgumentException ("Don't know how to handle the char ' in strings.");
629 }
630 return "'" + s + "'";
631 }
632
633 protected String getParts(StatementMetaData pData, Iterator pParts) {
634 StringBuffer sb = new StringBuffer ();
635 while (pParts.hasNext()) {
636 if (sb.length() > 0) {
637 sb.append(", ");
638 }
639 sb.append(getBooleanConstraintPart(pData, pParts.next()));
640 }
641 return sb.toString();
642 }
643
644 &nb
|
Java API By Example, From Geeks To Geeks.