1 /*
2 * $Source$
3 * $Revision$
4 *
5 * Copyright (C) 2000 David Warnock
6 *
7 * Part of Melati (http://melati.org), a framework for the rapid
8 * development of clean, maintainable web applications.
9 *
10 * Melati is free software; Permission is granted to copy, distribute
11 * and/or modify this software under the terms either:
12 *
13 * a) the GNU General Public License as published by the Free Software
14 * Foundation; either version 2 of the License, or (at your option)
15 * any later version,
16 *
17 * or
18 *
19 * b) any version of the Melati Software License, as published
20 * at http://melati.org
21 *
22 * You should have received a copy of the GNU General Public License and
23 * the Melati Software License along with this program;
24 * if not, write to the Free Software Foundation, Inc.,
25 * 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA to obtain the
26 * GNU General Public License and visit http://melati.org to obtain the
27 * Melati Software License.
28 *
29 * Feel free to contact the Developers of Melati (http://melati.org),
30 * if you would like to work out a different arrangement than the options
31 * outlined here. It is our intention to allow Melati to be used by as
32 * wide an audience as possible.
33 *
34 * This program is distributed in the hope that it will be useful,
35 * but WITHOUT ANY WARRANTY; without even the implied warranty of
36 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
37 * GNU General Public License for more details.
38 *
39 * Contact details for copyright holder:
40 *
41 * David Warnock (david@sundayta.co.uk)
42 * Sundayta Ltd
43 * International House, 174 Three Bridges Road, Crawley,
44 * West Sussex RH10 1LE, UK
45 *
46 */
47
48 package org.melati.poem.dbms;
49
50 import java.sql.Connection;
51 import java.sql.PreparedStatement;
52 import java.sql.ResultSet;
53 import java.sql.SQLException;
54
55 import org.melati.poem.Column;
56 import org.melati.poem.PoemType;
57 import org.melati.poem.SQLPoemException;
58 import org.melati.poem.SQLPoemType;
59 import org.melati.poem.SQLType;
60 import org.melati.poem.Table;
61
62 /**
63 * A Database Management System.
64 */
65 public interface Dbms {
66
67 /**
68 * Used in tests to allow multiple dbmsen to be loaded and unloaded.
69 */
70 void unloadDriver();
71
72 /**
73 * Return a connection.
74 * @param url the jdbc URL
75 * @param user the user to connect as, may be null
76 * @param password the password for user, may be null
77 * @return the connection
78 * @throws ConnectionFailurePoemException is we cannot connect
79 */
80 Connection getConnection(String url, String user, String password)
81 throws ConnectionFailurePoemException;
82
83 /**
84 * The db schema name to use, if any.
85 * This is typically the JDBC connection URL User string.
86 *
87 * @return the schema to use or null if not required
88 */
89 String getSchema();
90
91 /**
92 * A no-op for all but hsqldb, where the db needs to be shutdown
93 * when the servlet container or jvm is destroyed.
94 */
95 void shutdown(Connection connection) throws SQLException;
96
97 /**
98 * Accommodate different quoting strategies.
99 *
100 * @param name the unquoted name
101 * @return the name quoted (or not) appropriate for this Dbms
102 */
103 String getQuotedName(String name);
104
105 /**
106 * Accommodate different quoting strategies for values.
107 *
108 * @param sqlType the SQLType of the value
109 * @param value the value
110 * @return a String quoted appropriately
111 */
112 String getQuotedValue(SQLType<?> sqlType, String value);
113
114 /**
115 * Some DBMSen (HSQLDB) use canonical uppercased names in the metadata but not
116 * in normal use.
117 *
118 * see org.melati.poem.Table#unifyWithDB
119 * @param name entity name such as <tt>tableinfo</tt>
120 * @return the (un)quoted name
121 */
122 String getJdbcMetadataName(String name);
123
124 /**
125 * Accommodate casting in placeholders.
126 *
127 * @param type the PoemType
128 * @return the place holder
129 * @see Postgresql
130 */
131 String preparedStatementPlaceholder(PoemType<?> type);
132
133 /**
134 *
135 * @return The appropriate SQL string to create a table
136 */
137 String createTableSql(Table<?> table);
138
139 /**
140 * Allow Hsqldb to have a different create table syntax.
141 * Should have trailing space if not empty String
142 */
143 String createTableTypeQualifierSql(Table<?> table);
144
145 /**
146 * Accomodate MySQL table creation options.
147 * @return DMBS specific table creation options or empty String
148 */
149 String createTableOptionsSql();
150
151 /**
152 * @return SQL to be run after creation or null
153 */
154 String tableInitialisationSql(Table<?> table);
155
156 /**
157 * Retrieve a SQL type keyword used by the DBMS
158 * for the given Melati type name.
159 *
160 * Override this in non-Ansi standard dbms to handle
161 * variants.
162 *
163 * @param sqlTypeName the Melati internal type name
164 * @return this dbms specific type keyword
165 */
166 String getSqlDefinition(String sqlTypeName);
167
168 /**
169 * Accommodate String / Text distinction.
170 *
171 * @param size the string length (-1 means no limit)
172 * @return the SQL definition for a string of this size
173 * @throws SQLException
174 */
175 String getStringSqlDefinition(int size) throws SQLException;
176
177 /**
178 * Accommodate Long / Bigint deviants.
179 * @return the keyword to use.
180 */
181 String getLongSqlDefinition();
182
183 /**
184 * Accommodate different true and false values.
185 *
186 * @return the DBMS specific truth and false values
187 */
188 String sqlBooleanValueOfRaw(Object raw);
189
190 /**
191 * Accommodate different treatment of different sized binary data.
192 *
193 * @param size how big the field is
194 * @return the keyword to use
195 * @throws SQLException
196 */
197 String getBinarySqlDefinition(int size) throws SQLException;
198
199 /**
200 * Accommodate differing Fixed Point notations.
201 *
202 * @param scale the number of places to right of decimal point
203 * @param precision how many digits in total
204 * @return the keywords to use
205 * @throws SQLException potentially
206 */
207 String getFixedPtSqlDefinition(int scale, int precision) throws SQLException;
208
209 /**
210 * Enable one PoemType to represent another,
211 * for example a <tt>bit</tt> to represent a <tt>boolean</tt>.
212 *
213 * @param storage the container
214 * @param other the type to store
215 * @return the PoemType to use
216 */
217 <S,O>PoemType<O> canRepresent(PoemType<S> storage, PoemType<O> other);
218
219 /**
220 * The simplest POEM type corresponding to a JDBC description from the
221 * database.
222 *
223 * @param rs the JDBC metadata
224 * @return the PoemType to use
225 * @throws SQLException potentially
226 */
227 SQLPoemType<?> defaultPoemTypeOfColumnMetaData(ResultSet rs)
228 throws SQLException;
229
230 /**
231 * Whether this DBMS can drop columns.
232 *
233 * @return true if we can
234 */
235 boolean canDropColumns();
236
237 /**
238 * Whether this DBMS can store binary data.
239 *
240 * @return true if we can
241 */
242 boolean canStoreBlobs();
243
244 /**
245 * An exception appropriate for expressing what really went wrong
246 * during a write to the db. This gives the opportunity to
247 * try to interpret the <TT>getMessage</TT> text returned by
248 * the underlying driver, so that a more friendly error page
249 * can be put together for the user.
250 *
251 * Canonically, this is used to separate out "duplicate key"
252 * errors from more serious problems.
253 *
254 * @param table The table on which the update was affected
255 * @param sql The operation attempted, or possibly <TT>null</TT>
256 * @param insert Whether the operation was an <TT>INSERT</TT> as
257 * opposed to an <TT>UPDATE</TT>
258 * @param e The raw SQL exception: the routine is meant to
259 * try to interpret <TT>e.getMessage</TT> if it can
260 *
261 * @return an appropriate exception
262 * @see Postgresql#exceptionForUpdate
263 */
264 SQLPoemException exceptionForUpdate(Table<?> table, String sql, boolean insert,
265 SQLException e);
266
267 /**
268 * Version of previous method for <TT>PreparedStatement</TT>s. By default
269 * (in the <TT>AnsiStandard</TT> implementation of <TT>Dbms</TT>) this simply
270 * invokes <TT>PreparedStatement.toString()</TT> and calls the
271 * <TT>String</TT> version.
272 *
273 * @param table The table on which the update was affected
274 * @param ps The operation attempted, or possibly <TT>null</TT>
275 * @param insert Whether the operation was an <TT>INSERT</TT> as
276 * opposed to an <TT>UPDATE</TT>
277 * @param e The raw SQL exception: the routine is meant to
278 * try to interpret <TT>e.getMessage</TT> if it can
279 * @return an appropriate exception
280 * @see AnsiStandard#exceptionForUpdate(org.melati.poem.Table, java.lang.String,
281 * boolean, java.sql.SQLException)
282 */
283
284 SQLPoemException exceptionForUpdate(Table<?> table, PreparedStatement ps,
285 boolean insert, SQLException e);
286
287 /**
288 * Translate special names to non special ones.
289 *
290 * @param name the field or table name
291 * @return the name translated if necessary
292 */
293 String unreservedName(String name);
294
295 /**
296 * Reverse the mapping in <tt>unreservedName</tt>.
297 *
298 * @param name an SQL name
299 * @return the corresponding name to use within Melati
300 */
301 String melatiName(String name);
302
303 /**
304 * Accommodate DBMS which require a length for BLOBS.
305 *
306 * @param column the POEM Column we are dealing with
307 * @return SQL length string
308 */
309 String getIndexLength(Column<?> column);
310
311 /**
312 * Whether a <tt>Column</tt> can have an SQL index applied to it.
313 *
314 * @param column the POEM Column we are dealing with
315 * @return true if it can, false otherwise.
316 */
317 boolean canBeIndexed(Column<?> column);
318
319 /**
320 * SQL string to get a <tt>Capability</tt>.
321 *
322 * @param userTroid the troid of the User to use in the query
323 * @param capabilityExpr the capability troid we need
324 * @return the SQL query to use
325 */
326 String givesCapabilitySQL(Integer userTroid, String capabilityExpr);
327
328 /**
329 * Accommodate the variety of ways of ignoring case.
330 *
331 * @param term1 the term to find in
332 * @param term2 the quoted term to find
333 * @return the SQL query to use
334 */
335 String caseInsensitiveRegExpSQL(String term1, String term2);
336
337 /**
338 * A string to represent this DBMS.
339 *
340 * @return the class name.
341 */
342 String toString();
343
344 /**
345 * If Foreign key definitions are part of field definitions,
346 * otherwise blank (silently unsupported).
347 *
348 * @param tableName the table that this column is in, unquoted
349 * @param fieldName often the name of the foreign table, unquoted
350 * @param targetTableName the table that this is a foreign key into, unquoted
351 * @param targetTableFieldName name of the primary key field of the foreign
352 * table, often id, unquoted
353 * @param fixName name of the IntegrityFix
354 *
355 * @return The definition string
356 */
357 String getForeignKeyDefinition(String tableName, String fieldName,
358 String targetTableName, String targetTableFieldName, String fixName);
359
360 /**
361 * Return the PRIMARY KEY definition string for this dbms.
362 *
363 * @param fieldName the table Troid column, often <code>id</code>, unquoted
364 *
365 * @return The definition string
366 */
367 String getPrimaryKeyDefinition(String fieldName);
368
369 /**
370 * Return the SQL snippet to alter a column to not nullable.
371 * @param tableName
372 * @param column
373 * @return SQL snippet to set a column not nullable
374 */
375 String alterColumnNotNullableSQL(String tableName, Column<?> column);
376
377 /**
378 * @param column the target to add a remark to
379 * @param comment the remark to add
380 * @return an update SQL command or null
381 */
382 String alterColumnAddCommentSQL(Column<?> column, String comment);
383
384 /**
385 * @param table the target to add a remark to
386 * @param comment the remark to add
387 * @return an update SQL command or null
388 */
389 String alterTableAddCommentSQL(Table<?> table, String comment);
390
391 /**
392 * Accommodate different limiting syntax.
393 *
394 * @param querySelection main body of query
395 * @param limit number to limit to
396 * @return limited query
397 */
398 String selectLimit(String querySelection, int limit);
399
400 /**
401 * Accommodate lack of boolean types in underlying DBMS.
402 * @param booleanColumn the column which should be a boolean
403 * @return an expression that evaluates to True ie the column name or column name = 1
404 */
405 String booleanTrueExpression(Column<Boolean> booleanColumn);
406
407 /**
408 * Used to set a not null value when
409 * creating a non nullable column.
410 * @param type the type name
411 * @return a String suitable for substitution in UPDATE table SET field = ?
412 */
413 String getSqlDefaultValue(SQLType<?> type);
414
415 }