1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47 package org.melati.poem.dbms;
48
49 import org.melati.poem.*;
50 import org.melati.poem.SQLType;
51 import org.melati.poem.util.StringUtils;
52
53 import java.math.BigDecimal;
54 import java.sql.*;
55 import java.util.Enumeration;
56 import java.util.Properties;
57
58
59
60
61
62
63
64
65 public class AnsiStandard implements Dbms {
66
67 protected String schema;
68 private boolean driverLoaded = false;
69 private String driverClassName = null;
70 private Driver driver = null;
71
72 protected synchronized String getDriverClassName() {
73 if (driverClassName == null)
74 throw new PoemBugPoemException(
75 "No Driver Classname set in dbms specific class");
76
77 return driverClassName;
78 }
79
80 protected synchronized void setDriverClassName(String name) {
81 driverClassName = name;
82 }
83
84 @Override
85 public void unloadDriver() {
86 driver = null;
87 setDriverLoaded(false);
88 }
89
90 protected synchronized boolean getDriverLoaded() {
91 return driverLoaded;
92 }
93
94 protected synchronized void setDriverLoaded(boolean loaded) {
95 driverLoaded = loaded;
96 }
97
98 @Override
99 public String getSchema() {
100 return null;
101 }
102
103 @Override
104 public void shutdown(Connection connection)
105 throws SQLException{
106 }
107
108 @Override
109 public boolean canDropColumns(){
110 return true;
111 }
112
113 @Override
114 public boolean canStoreBlobs(){
115 return true;
116 }
117
118 protected synchronized void loadDriver() {
119 Class<?> driverClass;
120 try {
121 driverClass = Class.forName(getDriverClassName());
122 } catch (ClassNotFoundException e) {
123 throw new UnexpectedExceptionPoemException(e);
124 }
125
126 setDriverLoaded(true);
127
128 try {
129 driver = (Driver)driverClass.newInstance();
130 } catch (java.lang.Exception e) {
131
132
133 throw new UnexpectedExceptionPoemException(e);
134 }
135 }
136
137
138
139
140
141
142 @Override
143 public Connection getConnection(String url, String user, String password)
144 throws ConnectionFailurePoemException {
145 schema = user;
146 try {
147 if (!getDriverLoaded()) loadDriver();
148
149 Connection c;
150 if (driver != null) {
151 Properties info = new Properties();
152 if (user != null)
153 info.put("user", user);
154 if (password != null)
155 info.put("password", password);
156
157 c = driver.connect(url, info);
158 if (c == null)
159 throw new SQLException(
160 "Null connection from driver using url: " +
161 url +
162 " user: " +
163 user +
164 " password: " + password);
165 } else {
166 c = DriverManager.getConnection(url, user, password);
167 if (c == null)
168 throw new SQLException(
169 "Null connection from DriverManager using url: " +
170 url +
171 " user: " +
172 user +
173 " password: " + password);
174 }
175 if (c.getAutoCommit())
176 c.setAutoCommit(false);
177
178 return c;
179 } catch (Exception e) {
180 throw new ConnectionFailurePoemException(e);
181 }
182 }
183
184 @Override
185 public String preparedStatementPlaceholder(PoemType<?> type) {
186 return "?";
187 }
188
189 @Override
190 public String createTableSql(Table<?> table) {
191 StringBuffer sqb = new StringBuffer();
192 sqb.append("CREATE " + createTableTypeQualifierSql(table) +
193 "TABLE " + table.quotedName() + " (");
194 Enumeration<Column<?>> columns = table.columns();
195 int colCount = 0;
196 while (columns.hasMoreElements()) {
197 Column<?> col = (Column<?>)columns.nextElement();
198 if (colCount != 0)
199 sqb.append(", ");
200 colCount++;
201 sqb.append(col.quotedName() + " " +
202 col.getSQLType().sqlDefinition(this));
203
204 }
205 sqb.append(")");
206 sqb.append(createTableOptionsSql());
207 return sqb.toString();
208 }
209
210 @Override
211 public String createTableTypeQualifierSql(Table<?> table) {
212 return "";
213 }
214
215 @Override
216 public String createTableOptionsSql() {
217 return "";
218 }
219
220 @Override
221 public String tableInitialisationSql(Table<?> table) {
222 return null;
223 }
224
225 @Override
226 public String getSqlDefinition(String sqlTypeName) {
227 return sqlTypeName;
228 }
229
230 @Override
231 public String getStringSqlDefinition(int size) throws SQLException {
232 if (size < 0)
233 throw new SQLException(
234 "Unlimited length not supported in AnsiStandard STRINGs");
235
236 return "VARCHAR(" + size + ")";
237 }
238
239 @Override
240 public String getLongSqlDefinition() {
241 return "INT8";
242 }
243
244 @Override
245 public String getBinarySqlDefinition(int size) throws SQLException {
246 if (size < 0)
247 throw new SQLException(
248 "Unlimited length not supported in AnsiStandard BINARYs");
249
250 return "LONGVARBINARY(" + size + ")";
251 }
252
253 @Override
254 public String getFixedPtSqlDefinition(int scale, int precision)
255 throws SQLException {
256 if (scale < 0)
257 throw new SQLException(
258 "negative scale not supported " +
259 "in AnsiStandard DECIMALs");
260 if (precision <= 0)
261 throw new SQLException(
262 "nonpositive precision not supported " +
263 "in AnsiStandard DECIMALs");
264
265 return "DECIMAL(" + precision + "," + scale + ")";
266 }
267
268 @Override
269 public String sqlBooleanValueOfRaw(Object raw) {
270 return raw.toString();
271 }
272
273 @Override
274 public <S,O>PoemType<O> canRepresent(PoemType<S> storage, PoemType<O> type) {
275 return storage.canRepresent(type);
276 }
277
278 private SQLPoemType<?> unsupported(String sqlTypeName, ResultSet md)
279 throws UnsupportedTypePoemException {
280 UnsupportedTypePoemException e;
281 try {
282 e = new UnsupportedTypePoemException(md.getString("TABLE_NAME"),
283 md.getString("COLUMN_NAME"),
284 md.getShort("DATA_TYPE"),
285 sqlTypeName, md.getString("TYPE_NAME"));
286 } catch (SQLException ee) {
287 throw new UnsupportedTypePoemException(sqlTypeName);
288 }
289
290 throw e;
291 }
292
293 @Override
294 public SQLPoemType<?> defaultPoemTypeOfColumnMetaData(ResultSet columnsMetaData)
295 throws SQLException {
296 int typeCode = columnsMetaData.getShort("DATA_TYPE");
297 boolean nullable = columnsMetaData.getInt("NULLABLE") == DatabaseMetaData.columnNullable;
298 int width = columnsMetaData.getInt("COLUMN_SIZE");
299 int scale = columnsMetaData.getInt("DECIMAL_DIGITS");
300
301 switch (typeCode) {
302 case Types.BIT :
303 return new BooleanPoemType(nullable);
304 case Types.TINYINT :
305 return unsupported("TINYINT", columnsMetaData);
306 case Types.SMALLINT :
307 return unsupported("SMALLINT", columnsMetaData);
308 case Types.INTEGER :
309 return new IntegerPoemType(nullable);
310 case Types.BIGINT :
311 return new LongPoemType(nullable);
312
313 case Types.FLOAT :
314 return unsupported("FLOAT", columnsMetaData);
315 case Types.REAL :
316 return new DoublePoemType(nullable);
317 case Types.DOUBLE :
318 return new DoublePoemType(nullable);
319
320 case Types.NUMERIC :
321 return new BigDecimalPoemType(nullable, width, scale);
322 case Types.DECIMAL :
323 return new BigDecimalPoemType(nullable, width, scale);
324
325 case Types.CHAR :
326 return unsupported("CHAR", columnsMetaData);
327 case Types.VARCHAR :
328 return new StringPoemType(nullable, width == 0 ? -1 : width);
329 case Types.LONGVARCHAR :
330 return new StringPoemType(nullable, width == 0 ? -1 : width);
331
332 case Types.DATE :
333 return new DatePoemType(nullable);
334 case Types.TIME :
335 return new TimePoemType(nullable);
336 case Types.TIMESTAMP :
337 return new TimestampPoemType(nullable);
338
339 case Types.BINARY :
340 return unsupported("BINARY", columnsMetaData);
341 case Types.VARBINARY :
342 return new BinaryPoemType(nullable, width);
343 case Types.LONGVARBINARY :
344 return new BinaryPoemType(nullable, width);
345
346 case Types.NULL :
347 return unsupported("NULL", columnsMetaData);
348
349 case Types.OTHER :
350 return unsupported("OTHER", columnsMetaData);
351
352
353
354 case Types.JAVA_OBJECT :
355 return unsupported("JAVA_OBJECT", columnsMetaData);
356 case Types.DISTINCT :
357 return unsupported("DISTINCT", columnsMetaData);
358 case Types.STRUCT :
359 return unsupported("STRUCT", columnsMetaData);
360 case Types.ARRAY :
361 return unsupported("ARRAY", columnsMetaData);
362 case Types.BLOB :
363 return unsupported("BLOB", columnsMetaData);
364 case Types.CLOB:
365 return new StringPoemType(nullable, width == 0 ? -1 : width);
366 case Types.REF:
367 return unsupported("REF", columnsMetaData);
368 case Types.DATALINK :
369 return unsupported("DATLINK", columnsMetaData);
370
371 case Types.BOOLEAN :
372 return new BooleanPoemType(nullable);
373 default :
374 return unsupported("<code not in Types.java!>", columnsMetaData);
375 }
376 }
377
378 @Override
379 public SQLPoemException exceptionForUpdate(Table<?> table, String sql,
380 boolean insert, SQLException e) {
381 return new ExecutingSQLPoemException(sql, e);
382 }
383
384 @Override
385 public SQLPoemException exceptionForUpdate(Table<?> table, PreparedStatement ps,
386 boolean insert, SQLException e) {
387 return exceptionForUpdate(table, ps == null ? null : ps.toString(), insert,
388 e);
389 }
390
391 @Override
392 public String getQuotedName(String name) {
393 StringBuffer b = new StringBuffer();
394 StringUtils.appendQuoted(b, unreservedName(name), '"');
395 return b.toString();
396 }
397
398 @Override
399 public String getQuotedValue(SQLType<?> sqlType, String value) {
400 if (sqlType instanceof BooleanPoemType) {
401 return value;
402 }
403 if (sqlType instanceof DoublePoemType) {
404 return value;
405 }
406 if (sqlType instanceof LongPoemType) {
407 return value;
408 }
409 if (sqlType instanceof BinaryPoemType) {
410 return StringUtils.quoted(value,'\'');
411 }
412 if (sqlType instanceof BigDecimalPoemType) {
413 return value;
414 }
415 if (sqlType instanceof DatePoemType) {
416 return StringUtils.quoted(value,'\'');
417 }
418 if (sqlType instanceof TimestampPoemType) {
419 return StringUtils.quoted(value,'\'');
420 }
421 if (sqlType instanceof TimePoemType) {
422 return StringUtils.quoted(value,'\'');
423 }
424 if (sqlType instanceof PasswordPoemType) {
425 return StringUtils.quoted(value,'\'');
426 }
427 if (sqlType instanceof StringPoemType) {
428 return StringUtils.quoted(value,'\'');
429 }
430 if (sqlType instanceof IntegrityFixPoemType) {
431 return value;
432 }
433 if (sqlType instanceof IntegerPoemType) {
434 return value;
435 }
436 throw new PoemBugPoemException("Unrecognised sqlType: " + sqlType);
437
438 }
439
440 @Override
441 public String getJdbcMetadataName(String name) {
442 return name;
443 }
444
445
446
447
448
449
450
451
452
453
454 public String unreservedName(String name) {
455 return name;
456 }
457
458 @Override
459 public String melatiName(String name) {
460 return name;
461 }
462
463
464
465
466
467
468
469 @Override
470 public String getIndexLength(Column<?> column) {
471 return "";
472 }
473
474
475
476
477
478 @Override
479 public boolean canBeIndexed(Column<?> column) {
480 return true;
481 }
482
483
484
485
486
487
488
489
490 @Override
491 public String givesCapabilitySQL(Integer userTroid, String capabilityExpr) {
492 return "SELECT * FROM " + getQuotedName("groupMembership") + " WHERE "
493 + getQuotedName("user") + " = " + userTroid + " AND "
494 + "EXISTS ( " + "SELECT " + getQuotedName("groupCapability") + "."
495 + getQuotedName("group") + " FROM "
496 + getQuotedName("groupCapability") + " WHERE "
497 + getQuotedName("groupCapability") + "." + getQuotedName("group")
498 + " = " + getQuotedName("groupMembership") + "."
499 + getQuotedName("group") + " AND " + getQuotedName("capability")
500 + " = " + capabilityExpr + ")";
501 }
502
503
504
505
506 @Override
507 public String caseInsensitiveRegExpSQL(String term1, String term2) {
508 if (StringUtils.isQuoted(term2)) {
509 term2 = term2.substring(1, term2.length() - 1);
510 }
511 term2 = StringUtils.quoted(StringUtils.quoted(term2, '%'), '\'');
512
513 return term1 + " ILIKE " + term2;
514 }
515
516 @Override
517 public String toString() {
518 return this.getClass().getName();
519 }
520
521 @Override
522 public String getForeignKeyDefinition(String tableName, String fieldName,
523 String targetTableName, String targetTableFieldName, String fixName) {
524 String q = " ADD FOREIGN KEY (" + getQuotedName(fieldName) + ") " +
525 "REFERENCES " + getQuotedName(targetTableName) +
526 "(" + getQuotedName(targetTableFieldName) + ")";
527 if (fixName.equals("prevent"))
528 q += " ON DELETE RESTRICT";
529 if (fixName.equals("delete"))
530 q += " ON DELETE CASCADE";
531 if (fixName.equals("clear"))
532 q += " ON DELETE SET NULL";
533 return q;
534 }
535
536 @Override
537 public String getPrimaryKeyDefinition(String fieldName) {
538 return " ADD PRIMARY KEY (" + getQuotedName(fieldName) + ")";
539 }
540
541 @Override
542 public String alterColumnNotNullableSQL(String tableName, Column<?> column) {
543 return "ALTER TABLE " + getQuotedName(tableName) +
544 " ALTER COLUMN " + getQuotedName(column.getName()) +
545 " SET NOT NULL";
546 }
547
548 @Override
549 public String selectLimit(String querySelection, int limit) {
550 return "SELECT " + querySelection + " LIMIT " + limit;
551 }
552
553 @Override
554 public String booleanTrueExpression(Column<Boolean> booleanColumn) {
555 return booleanColumn.fullQuotedName();
556 }
557
558 @Override
559 public String getSqlDefaultValue(SQLType<?> sqlType) {
560 if (sqlType instanceof BooleanPoemType) {
561 return ("false");
562 }
563 if (sqlType instanceof DoublePoemType) {
564 return ("0.0");
565 }
566 if (sqlType instanceof LongPoemType) {
567 return ("0");
568 }
569 if (sqlType instanceof BinaryPoemType) {
570 return "";
571 }
572 if (sqlType instanceof BigDecimalPoemType) {
573 return new BigDecimal(0.0).toString();
574 }
575 if (sqlType instanceof DatePoemType) {
576 return new Date(new java.util.Date().getTime()).toString();
577 }
578 if (sqlType instanceof TimestampPoemType) {
579 return new Timestamp(System.currentTimeMillis()).toString();
580 }
581 if (sqlType instanceof TimePoemType) {
582 return new Time(System.currentTimeMillis()).toString();
583 }
584 if (sqlType instanceof PasswordPoemType) {
585 return "FIXME";
586 }
587 if (sqlType instanceof StringPoemType) {
588 return "default";
589 }
590
591 if (sqlType instanceof IntegrityFixPoemType) {
592 return StandardIntegrityFix.prevent.getIndex().toString();
593 }
594
595
596
597
598 if (sqlType instanceof IntegerPoemType) {
599 return ("0");
600 }
601 throw new PoemBugPoemException("Unrecognised sqlType: " + sqlType);
602
603 }
604
605
606 @Override
607 public String alterColumnAddCommentSQL(Column<?> column, String comment) {
608
609 return "COMMENT ON COLUMN "
610 + getQuotedName(unreservedName(column.getTable().getName()))
611 +"."
612 + getQuotedName(unreservedName(column.getName()))
613 + " IS '"
614 + comment
615 + "'";
616 }
617
618
619 @Override
620 public String alterTableAddCommentSQL(Table<?> table, String comment) {
621 return "COMMENT ON TABLE "
622 + getQuotedName(table.getName())
623 + " IS '"
624 + comment
625 + "'";
626 }
627
628 }
629