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 At sundayta.co.uk)
42 * Sundayta Ltd
43 * International House,
44 * 174 Three Bridges Road,
45 * Crawley, West Sussex
46 * RH10 1LE, UK
47 *
48 */
49 package org.melati.poem.dbms;
50
51 import java.sql.DatabaseMetaData;
52 import java.sql.PreparedStatement;
53 import java.sql.ResultSet;
54 import java.sql.SQLException;
55
56 import org.melati.poem.BinaryPoemType;
57 import org.melati.poem.BooleanPoemType;
58 import org.melati.poem.Column;
59 import org.melati.poem.DatePoemType;
60 import org.melati.poem.DoublePoemType;
61 import org.melati.poem.PoemType;
62 import org.melati.poem.SQLPoemType;
63 import org.melati.poem.SQLType;
64 import org.melati.poem.StringPoemType;
65 import org.melati.poem.TimestampPoemType;
66 import org.melati.poem.util.StringUtils;
67
68 /**
69 * A Driver for the Microsoft SQL server.
70 */
71 public class SQLServer extends AnsiStandard {
72
73 /**
74 * SQL Server does not have a pleasant <code>TEXT</code> datatype, so we use
75 * an arbetary value in a <code>VARCHAR</code>.
76 */
77 public static final int sqlServerTextHack = 2333;
78
79 /**
80 * Maximum size of a binary field.
81 */
82 public static final int sqlServerMaxBinarySize = 2147483647;
83
84 /**
85 * Constructor.
86 */
87 public SQLServer() {
88 // buggy
89 // setDriverClassName("com.merant.datadirect.jdbc.sqlserver.SQLServerDriver");
90 // setDriverClassName("sun.jdbc.odbc.JdbcOdbcDriver"); //does not work
91 // setDriverClassName("com.ashna.jturbo.driver.Driver"); //works
92 // setDriverClassName("com.jnetdirect.jsql.JSQLDriver"); //works
93
94 // 2003
95 // does not return indices without schema name ?
96 // setDriverClassName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
97 // FreeTDS driver has many unimplemented features and => does not work.
98
99 // Works with 2003
100 // setDriverClassName("com.inet.tds.TdsDriver");
101
102 // Not working
103 //setDriverClassName("easysoft.sql.jobDriver");
104 // 2005
105 setDriverClassName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
106 }
107
108 /**
109 * Get the user we are connected as and return that as the schema.
110 *
111 * @see org.melati.poem.dbms.Dbms#getSchema()
112 * @see org.melati.poem.dbms.AnsiStandard#getSchema()
113 */
114 public String getSchema() {
115 return null;
116 }
117
118 // Commented out as PMD objects to over riding method which only call
119 // super.
120 // public String getQuotedName(String name) {
121 // if you don't want to set 'use ANSI quoted identifiers' database property
122 // to 'true' (on SQL Server)
123
124 /*
125 * if(name.equalsIgnoreCase("nullable")) return "\"" + name+"\"";
126 * if(name.equalsIgnoreCase("unique")) return "\"" + name+"\"";
127 * if(name.equalsIgnoreCase("user")) return "q" + name;
128 * if(name.equalsIgnoreCase("group")) return "q" + name; return name;
129 */
130
131 // if you already set 'use ANSI quoted identifiers' property to 'true'
132 // return super.getQuotedName(name);
133 // }
134 /**
135 * {@inheritDoc}
136 *
137 * @see org.melati.poem.dbms.AnsiStandard#getSqlDefinition(java.lang.String)
138 */
139 public String getSqlDefinition(String sqlTypeName) {
140 if (sqlTypeName.equals("BOOLEAN")) {
141 return ("BIT");
142 }
143 if (sqlTypeName.equals("DATE")) {
144 return ("DATETIME");
145 }
146 if (sqlTypeName.equals("TIMESTAMP")) {
147 return ("DATETIME");
148 }
149 return super.getSqlDefinition(sqlTypeName);
150 }
151
152 /**
153 * {@inheritDoc}
154 *
155 * @see org.melati.poem.dbms.AnsiStandard#getStringSqlDefinition(int)
156 */
157 public String getStringSqlDefinition(int size) throws SQLException {
158 if (size < 0) { // Don't use TEXT as it doesn't support
159 // indexing or comparison
160 return "VARCHAR(" + sqlServerTextHack + ")";
161 }
162 return super.getStringSqlDefinition(size);
163 }
164
165 /**
166 * Translates a MSSQL String into a Poem <code>StringPoemType</code>.
167 */
168 //public static class SQLServerStringPoemType extends StringPoemType {
169
170 /**
171 * Constructor.
172 *
173 * @param nullable
174 * nullability
175 * @param size
176 * length
177 */
178 //public SQLServerStringPoemType(boolean nullable, int size) {
179 // super(nullable, size);
180 //}
181
182 // MSSQL returns metadata info size 2147483647 for its TEXT type
183 // We set size to sqlServerTextHack for our Text type
184 //protected boolean _canRepresent(SQLPoemType other) {
185 // return (getSize() < 0 || getSize() == 2147483647
186 // || getSize() == sqlServerTextHack || getSize() >= ((StringPoemType)other)
187 // .getSize());
188 //}
189
190 /**
191 * {@inheritDoc}
192 *
193 * @see org.melati.poem.BasePoemType#canRepresent(PoemType)
194 */
195 /*
196 * public PoemType canRepresent(PoemType other) { return other instanceof
197 * StringPoemType && _canRepresent((StringPoemType) other) &&
198 * !(!getNullable() && ((StringPoemType) other).getNullable()) ? other :
199 * null; }
200 */
201 //}
202
203 /**
204 * Accomodate our String size hack. {@inheritDoc}
205 *
206 * @see org.melati.poem.dbms.AnsiStandard#canRepresent
207 */
208 public <S,O>PoemType<O> canRepresent(PoemType<S> storage, PoemType<O> type) {
209 if (storage instanceof StringPoemType && type instanceof StringPoemType) {
210 if (((StringPoemType)storage).getSize() == sqlServerTextHack
211 && ((StringPoemType)type).getSize() == -1
212 && !(!storage.getNullable() && type.getNullable())) {
213 return type;
214 } else {
215 return storage.canRepresent(type);
216 }
217 } else if (storage instanceof BinaryPoemType
218 && type instanceof BinaryPoemType) {
219 if (((BinaryPoemType)storage).getSize() == sqlServerMaxBinarySize
220 && ((BinaryPoemType)type).getSize() == -1
221 && !(!storage.getNullable() && type.getNullable())) {
222 return type;
223 } else {
224 return storage.canRepresent(type);
225 }
226 } else if (storage instanceof DatePoemType
227 && type instanceof TimestampPoemType) {
228 if (!(!storage.getNullable() && type.getNullable())) {
229 return type;
230 } else {
231 return storage.canRepresent(type);
232 }
233 } else {
234 return super.canRepresent(storage, type);
235 }
236 }
237
238 /**
239 * Translates a MSSQL Date into a Poem <code>DatePoemType</code>.
240 */
241 // public static class SQLServerDatePoemType extends DatePoemType {
242
243 /**
244 * Constructor.
245 *
246 * @param nullable
247 * nullability
248 */
249 // public SQLServerDatePoemType(boolean nullable) {
250 // super(Types.DATE, "DATETIME", nullable);
251 // }
252
253 // protected boolean _canRepresent(SQLPoemType other) {
254 // return other instanceof DatePoemType
255 // || other instanceof TimestampPoemType;
256 // }
257
258 // }
259
260 /**
261 * Translates a MSSQL Date into a Poem <code>TimestampPoemType</code>.
262 */
263 //public static class SQLServerTimestampPoemType extends TimestampPoemType {
264
265 /**
266 * Constructor.
267 *
268 * @param nullable
269 * nullability
270 */
271 //public SQLServerTimestampPoemType(boolean nullable) {
272 // super(Types.TIMESTAMP, "DATETIME", nullable);
273 //}
274 //}
275
276 /**
277 * {@inheritDoc}
278 *
279 * @see org.melati.poem.dbms.AnsiStandard#sqlBooleanValueOfRaw(java.lang.Object)
280 */
281 public String sqlBooleanValueOfRaw(Object raw) {
282 if (((Boolean)raw).booleanValue())
283 return "1";
284 else
285 return "0";
286 }
287
288 /**
289 * Translates an SQLServer Boolean into a Poem <code>BooleanPoemType</code>.
290 */
291 public static class SQLServerBooleanPoemType extends BooleanPoemType {
292
293 /**
294 * Constructor.
295 *
296 * @param nullable
297 * nullability
298 */
299 public SQLServerBooleanPoemType(boolean nullable) {
300 super(nullable);
301 }
302
303 protected Boolean _getRaw(ResultSet rs, int col) throws SQLException {
304 synchronized (rs) {
305 boolean v = rs.getBoolean(col);
306 return rs.wasNull() ? null : (v ? Boolean.TRUE : Boolean.FALSE);
307 }
308 }
309
310 protected void _setRaw(PreparedStatement ps, int col, Object bool)
311 throws SQLException {
312 ps.setInt(col, ((Boolean)bool).booleanValue() ? 1 : 0);
313 }
314
315 }
316
317 /**
318 * {@inheritDoc}
319 *
320 * @see org.melati.poem.dbms.Dbms#getLongSqlDefinition()
321 */
322 public String getLongSqlDefinition() {
323 return "BIGINT";
324 }
325
326 /**
327 * {@inheritDoc}
328 *
329 * @see org.melati.poem.dbms.Dbms#getBinarySqlDefinition(int)
330 */
331 public String getBinarySqlDefinition(int size) throws SQLException {
332 if (size < 0)
333 return "VARBINARY(MAX)";
334
335 return "VARBINARY(" + size + ")";
336 }
337
338 /**
339 * {@inheritDoc}
340 *
341 * @see org.melati.poem.dbms.AnsiStandard#defaultPoemTypeOfColumnMetaData(
342 * java.sql.ResultSet)
343 */
344 public SQLPoemType<?> defaultPoemTypeOfColumnMetaData(ResultSet md)
345 throws SQLException {
346
347 /*
348 ResultSetMetaData rsmd = md.getMetaData();
349 int cols = rsmd.getColumnCount();
350 for (int i = 1; i <= cols; i++) {
351 String table = rsmd.getTableName(i);
352 System.err.println("table name: " + table);
353 String column = rsmd.getColumnName(i);
354 System.err.println("column name: " + column);
355 int type = rsmd.getColumnType(i);
356 System.err.println("type: " + type);
357 String typeName = rsmd.getColumnTypeName(i);
358 System.err.println("type Name: " + typeName);
359 String className = rsmd.getColumnClassName(i);
360 System.err.println("class Name: " + className);
361 System.err.println("String val: " + md.getString(i));
362 System.err.println("");
363 }
364 */
365 // Not used in Poem
366 //if (md.getString("TYPE_NAME").equals("text"))
367 // return new SQLServerStringPoemType(
368 // md.getInt("NULLABLE") == DatabaseMetaData.columnNullable, md
369 // .getInt("COLUMN_SIZE"));
370 // We use a magic number for text fields
371 if (md.getString("TYPE_NAME").equals("varchar")
372 && md.getInt("COLUMN_SIZE") == sqlServerTextHack)
373 return new StringPoemType(
374 md.getInt("NULLABLE") == DatabaseMetaData.columnNullable, -1);
375
376 // Not used in Poem
377 if (md.getString("TYPE_NAME").equals("char"))
378 return new StringPoemType(
379 md.getInt("NULLABLE") == DatabaseMetaData.columnNullable, md
380 .getInt("COLUMN_SIZE"));
381 if (md.getString("TYPE_NAME").equals("float"))
382 return new DoublePoemType(
383 md.getInt("NULLABLE") == DatabaseMetaData.columnNullable);
384 if (md.getString("TYPE_NAME").equals("datetime"))
385 return new DatePoemType(
386 md.getInt("NULLABLE") == DatabaseMetaData.columnNullable);
387 if (md.getString("TYPE_NAME").equals("bit"))
388 return new SQLServerBooleanPoemType(
389 md.getInt("NULLABLE") == DatabaseMetaData.columnNullable);
390 /*
391 * // MSSQL returns type -2 (BINARY) not 93 (TIMESTAMP)
392 * // They don't mean what we mean by timestamp
393 * // They mean a one-per-record record creation timestamp
394 * if( md.getString("TYPE_NAME").equals("timestamp"))
395 * return new TimestampPoemType(md.getInt("NULLABLE")== DatabaseMetaData.columnNullable);
396 */
397 return super.defaultPoemTypeOfColumnMetaData(md);
398 }
399
400 /**
401 * Ignore <TT>dtproperties</TT> as it is a 'System' table used to store
402 * Entity Relationship diagrams which have a jdbc type of TABLE when it should
403 * probably have a jdbc type of 'SYSTEM TABLE'.
404 *
405 * {@inheritDoc}
406 * @see org.melati.poem.dbms.AnsiStandard#melatiName(java.lang.String)
407 */
408 public String melatiName(String name) {
409 if (name == null)
410 return null;
411 if (name.equalsIgnoreCase("dtproperties"))
412 return null;
413 return name;
414 }
415
416 /**
417 * MSSQL cannot index TEXT fields. Probably means that if you are serious
418 * about using MSSQL you should use a varchar.
419 *
420 * If a field is defined as Text in the DSD we use a VARCHAR. Not sure what
421 * happens if a legacy db really uses TEXT.
422 *
423 * @return whether it is allowed.
424 */
425 public boolean canBeIndexed(Column<?> column) {
426 PoemType<?> t = column.getType();
427 if (t instanceof StringPoemType && ((StringPoemType)t).getSize() < 0)
428 return false;
429 return true;
430 }
431
432 /**
433 * {@inheritDoc}
434 *
435 * @see org.melati.poem.dbms.AnsiStandard#caseInsensitiveRegExpSQL
436 */
437 public String caseInsensitiveRegExpSQL(String term1, String term2) {
438 if (StringUtils.isQuoted(term2)) {
439 term2 = term2.substring(1, term2.length() - 1);
440 }
441 term2 = StringUtils.quoted(StringUtils.quoted(term2, '%'), '\'');
442
443 return term1 + " LIKE " + term2;
444 }
445
446 /**
447 * Slightly different - embrace and extend.
448 */
449 public String getForeignKeyDefinition(String tableName, String fieldName,
450 String targetTableName, String targetTableFieldName, String fixName) {
451 StringBuffer sb = new StringBuffer();
452 sb.append(" ADD FOREIGN KEY (" + getQuotedName(fieldName) + ") REFERENCES "
453 + getQuotedName(targetTableName) + "("
454 + getQuotedName(targetTableFieldName) + ")");
455 if (fixName.equals("prevent"))
456 sb.append(" ON DELETE NO ACTION");
457 if (fixName.equals("delete"))
458 sb.append(" ON DELETE CASCADE");
459 if (fixName.equals("clear"))
460 sb.append(" ON DELETE SET NULL");
461 return sb.toString();
462 }
463
464 /**
465 * Accommodate SQLServer syntax.
466 */
467 public String alterColumnNotNullableSQL(String tableName, Column<?> column) {
468 return "ALTER TABLE " + getQuotedName(tableName) + " ALTER COLUMN "
469 + getQuotedName(column.getName()) + " "
470 + column.getSQLType().sqlDefinition(this);
471 }
472
473 /**
474 * {@inheritDoc}
475 *
476 * @see org.melati.poem.dbms.Dbms#selectLimit(java.lang.String, int)
477 */
478 public String selectLimit(String querySelection, int limit) {
479 return "SELECT TOP " + limit + querySelection;
480 }
481
482
483 /**
484 * {@inheritDoc}
485 * @see org.melati.poem.dbms.Dbms#booleanTrueExpression(org.melati.poem.Column)
486 */
487 public String booleanTrueExpression(Column<Boolean> booleanColumn) {
488 return booleanColumn.fullQuotedName() + "=1";
489 }
490
491 /**
492 * {@inheritDoc}
493 * @see org.melati.poem.dbms.AnsiStandard#getSqlDefaultValue(org.melati.poem.SQLType)
494 */
495 public String getSqlDefaultValue(SQLType<?> sqlType) {
496 if (sqlType instanceof BooleanPoemType) {
497 return ("0");
498 }
499 if (sqlType instanceof BinaryPoemType) {
500 return "convert(varbinary, '')";
501 }
502 return super.getSqlDefaultValue(sqlType);
503 }
504
505 public String getQuotedValue(SQLType<?> sqlType, String value) {
506 if (sqlType instanceof BinaryPoemType) {
507 return value;
508 }
509 return super.getQuotedValue(sqlType, value);
510 }
511 }