View Javadoc
1   /*
2    * Copyright (C) 2000 David Warnock
3    * 
4    * Part of Melati (http://melati.org), a framework for the rapid
5    * development of clean, maintainable web applications.
6    *
7    * Melati is free software; Permission is granted to copy, distribute
8    * and/or modify this software under the terms either:
9    *
10   * a) the GNU General Public License as published by the Free Software
11   *    Foundation; either version 2 of the License, or (at your option)
12   *    any later version,
13   *
14   *    or
15   *
16   * b) any version of the Melati Software License, as published
17   *    at http://melati.org
18   *
19   * You should have received a copy of the GNU General Public License and
20   * the Melati Software License along with this program;
21   * if not, write to the Free Software Foundation, Inc.,
22   * 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA to obtain the
23   * GNU General Public License and visit http://melati.org to obtain the
24   * Melati Software License.
25   *
26   * Feel free to contact the Developers of Melati (http://melati.org),
27   * if you would like to work out a different arrangement than the options
28   * outlined here.  It is our intention to allow Melati to be used by as
29   * wide an audience as possible.
30   *
31   * This program is distributed in the hope that it will be useful,
32   * but WITHOUT ANY WARRANTY; without even the implied warranty of
33   * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
34   * GNU General Public License for more details.
35   *
36   * Contact details for copyright holder:
37   *
38   *     David Warnock (david At sundayta.co.uk)
39   *     Sundayta Ltd
40   *     International House, 
41   *     174 Three Bridges Road, 
42   *     Crawley, 
43   *     West Sussex RH10 1LE, UK
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   * An SQL 92 compliant Database Management System. 
60   * <p>
61   * Should there ever be such a
62   * thing then you wouldn't need to extend this, but all DBs used with Melati so
63   * far have needed to extend the standard with their own variations.
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       // ... otherwise, "something went wrong" and I don't here care what
132       // or have the wherewithal to do anything about it :)
133       throw new UnexpectedExceptionPoemException(e);
134     }
135   }
136 
137   /**
138    * The default windows installation of MySQL has autocommit set true, 
139    * which throws an SQLException when one issues a commit.
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         //c.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED); 
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         // Following introduced since 1.1
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    * A pair of functions for getting around keywords which make your 
447    * JDBC driver barf, as 'group' does for MySQL.
448    * 
449    * {@inheritDoc}
450    * @see org.melati.poem.dbms.Dbms#unreservedName(java.lang.String)
451    * @see org.melati.poem.dbms.MySQL#unreservedName
452    * @see org.melati.poem.dbms.MySQL#melatiName
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    * MySQL requires a length argument when creating an index on a BLOB or TEXT
465    * column.
466    * 
467    * @see org.melati.poem.dbms.MySQL#getIndexLength
468    */
469   @Override
470   public String getIndexLength(Column<?> column) {
471     return "";
472   }
473 
474   /**
475    * MSSQL cannot index a TEXT column. But neither can it compare them so we
476    * don't use it, we use VARCHAR(255).
477    */
478   @Override
479   public boolean canBeIndexed(Column<?> column) {
480     return true;
481   }
482 
483   /**
484    * MySQL had no EXISTS keyword, from 4.1 onwards it does.
485    * NOTE There is a bootstrap problem here, we need to use the 
486    * unchecked troid, otherwise we get a stack overflow.
487    *
488    * @see org.melati.poem.dbms.MySQL#givesCapabilitySQL
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    * This is the Postgresql syntax.
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     //Set prevent as default fix
591     if (sqlType instanceof IntegrityFixPoemType) {
592       return StandardIntegrityFix.prevent.getIndex().toString();
593     }
594 
595     // Defaults to User for ColumnPoemType
596     // Primary for SearchabilityPoemType
597     // This needs to be last, as types above extend IntegerPoemType
598     if (sqlType instanceof IntegerPoemType) {
599       return ("0");
600     }
601     throw new PoemBugPoemException("Unrecognised sqlType: " + sqlType);
602 
603   }
604 
605   /** TODO test on something which actually uses this */
606   @Override
607   public String alterColumnAddCommentSQL(Column<?> column, String comment) {
608     // FIREBIRD, ORACLE, postgresql
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   /** TODO test on something which actually uses this */
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