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 package org.melati.poem.dbms;
45 import java.sql.Connection;
46 import java.sql.DatabaseMetaData;
47 import java.sql.ResultSet;
48 import java.sql.SQLException;
49
50 import org.melati.poem.BigDecimalPoemType;
51 import org.melati.poem.BinaryPoemType;
52 import org.melati.poem.BooleanPoemType;
53 import org.melati.poem.Column;
54 import org.melati.poem.DatePoemType;
55 import org.melati.poem.DoublePoemType;
56 import org.melati.poem.IntegerPoemType;
57 import org.melati.poem.LongPoemType;
58 import org.melati.poem.PoemType;
59 import org.melati.poem.SQLPoemType;
60 import org.melati.poem.SQLType;
61 import org.melati.poem.StringPoemType;
62 import org.melati.poem.TimestampPoemType;
63 import org.melati.poem.dbms.SQLServer.SQLServerBooleanPoemType;
64 import org.melati.poem.util.StringUtils;
65
66
67
68
69
70 public class MSAccess extends AnsiStandard {
71
72 public static final int msAccessTextHack = 250;
73
74 public static final int msAccessMemoSize = 1073741823;
75
76 public static final int msAccessBinarySize = 510;
77
78
79 public MSAccess() {
80
81 setDriverClassName("sun.jdbc.odbc.JdbcOdbcDriver");
82
83
84
85 }
86
87
88
89
90
91 public boolean canDropColumns() {
92 return false;
93 }
94
95
96
97
98 public boolean canStoreBlobs(){
99 return false;
100 }
101
102
103 @Override
104 public void shutdown(Connection connection) throws SQLException {
105
106 connection.commit();
107 }
108
109 @Override
110 public String unreservedName(String name) {
111 if(name.equalsIgnoreCase("GROUP")) name = "MELATI_" + name.toUpperCase();
112 if(name.equalsIgnoreCase("USER")) name = "MELATI_" + name.toUpperCase();
113 return name;
114 }
115
116
117
118
119
120 @Override
121 public String melatiName(String name) {
122 if (name == null) return name;
123 if (name.startsWith("~"))
124 return null;
125 if(name.equalsIgnoreCase("MELATI_GROUP")) name = "group";
126 if(name.equalsIgnoreCase("MELATI_USER")) name = "user";
127 return name;
128 }
129
130
131 @Override
132 public String getSqlDefinition(String sqlTypeName) {
133 if (sqlTypeName.equals("BOOLEAN")) {
134 return ("BIT");
135 }
136 if (sqlTypeName.equals("DOUBLE PRECISION")) {
137 return ("DOUBLE");
138 }
139 if (sqlTypeName.equals("INT8")) {
140 return ("INTEGER");
141 }
142 if (sqlTypeName.equals("Big Decimal")) {
143 return ("NUMERIC");
144 }
145 return super.getSqlDefinition(sqlTypeName);
146 }
147
148 @Override
149 public String getLongSqlDefinition() {
150 return "INTEGER";
151 }
152
153 @Override
154 public String getStringSqlDefinition(int size) throws SQLException {
155 if (size < 0) {
156
157 return "VARCHAR(" + msAccessTextHack + ")";
158 }
159 return super.getStringSqlDefinition(size);
160 }
161
162
163
164
165 @Override
166 public String getFixedPtSqlDefinition(int scale, int precision)
167 throws SQLException {
168 if (scale < 0 || precision <= 0)
169 throw new SQLException(
170 "negative scale or nonpositive precision not supported in AnsiStandard DECIMALs");
171 return "NUMERIC";
172 }
173
174 @Override
175 public String getBinarySqlDefinition(int size) throws SQLException {
176 if (size < 0)
177 return "BINARY";
178 return "BINARY(" + size + ")";
179 }
180
181 @Override
182 public String sqlBooleanValueOfRaw(Object raw) {
183 if (((Boolean)raw).booleanValue())
184 return "1";
185 else
186 return "0";
187 }
188
189 @Override
190 public <S,O>PoemType<O> canRepresent(PoemType<S> storage, PoemType<O> type) {
191 if (storage instanceof StringPoemType && type instanceof StringPoemType) {
192 if (((StringPoemType)storage).getSize() == msAccessTextHack
193 && ((StringPoemType) type).getSize() == -1) {
194 return type;
195 } else if (((StringPoemType)storage).getSize() == msAccessMemoSize
196 && ((StringPoemType)type).getSize() == -1) {
197 return type;
198 } else {
199 return storage.canRepresent(type);
200 }
201 } else if (storage instanceof BinaryPoemType && type instanceof BinaryPoemType) {
202 if (((BinaryPoemType)storage).getSize() == msAccessBinarySize
203 && ((BinaryPoemType) type).getSize() == -1) {
204 return type;
205 } else {
206 return storage.canRepresent(type);
207 }
208 } else if (storage instanceof TimestampPoemType && type instanceof DatePoemType) {
209 return type;
210 } else if (storage instanceof BooleanPoemType && type instanceof BooleanPoemType) {
211
212 return type;
213 } else if (storage instanceof DoublePoemType && type instanceof BigDecimalPoemType
214 && !(!storage.getNullable() && type.getNullable())
215 ) {
216 return type;
217 } else if (storage instanceof IntegerPoemType && type instanceof LongPoemType
218 && !(!storage.getNullable() && type.getNullable())
219 ) {
220 return type;
221 } else {
222 return storage.canRepresent(type);
223 }
224 }
225
226
227
228
229 public static class MSAccessStringPoemType extends StringPoemType {
230
231
232
233
234
235 public MSAccessStringPoemType(boolean nullable, int size) {
236 super(nullable, size);
237 }
238
239 protected boolean _canRepresent(SQLPoemType<?> other) {
240 return sqlTypeCode() == other.sqlTypeCode() &&
241 (getSize() < 0 ||
242 getSize() == msAccessTextHack ||
243 getSize() >= ((StringPoemType) other).getSize());
244 }
245
246 @Override
247 public <O>PoemType<O> canRepresent(PoemType<O> other) {
248 return other instanceof StringPoemType
249 && _canRepresent((StringPoemType) other)
250 && !(!getNullable() && ((StringPoemType) other).getNullable()) ? other
251 : null;
252 }
253 }
254
255 @Override
256 public SQLPoemType<?> defaultPoemTypeOfColumnMetaData(ResultSet md)
257 throws SQLException {
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277 String typeName = md.getString("TYPE_NAME");
278 if (typeName.equals("text"))
279 return new MSAccessStringPoemType(
280 md.getInt("NULLABLE") == DatabaseMetaData.columnNullable,
281 md.getInt("COLUMN_SIZE"));
282
283
284
285
286
287
288
289
290
291
292
293
294 if (typeName.equals("BINARY"))
295 return new BinaryPoemType(
296 md.getInt("NULLABLE") == DatabaseMetaData.columnNullable,
297 md.getInt("COLUMN_SIZE"));
298 if (typeName.equals("INT"))
299 return new LongPoemType(
300 md.getInt("NULLABLE") == DatabaseMetaData.columnNullable);
301 if (typeName.equals("SMALLINT"))
302 return new IntegerPoemType(
303 md.getInt("NULLABLE") == DatabaseMetaData.columnNullable);
304 if (typeName.equals("bit"))
305 return new SQLServerBooleanPoemType(
306 md.getInt("NULLABLE") == DatabaseMetaData.columnNullable);
307 return super.defaultPoemTypeOfColumnMetaData(md);
308 }
309
310 @Override
311 public String caseInsensitiveRegExpSQL(String term1, String term2) {
312 if (StringUtils.isQuoted(term2)) {
313 term2 = term2.substring(1, term2.length() - 1);
314 }
315 term2 = StringUtils.quoted(StringUtils.quoted(term2, '%'), '\'');
316
317 return term1 + " LIKE " + term2;
318 }
319
320
321
322
323
324 @Override
325 public String alterColumnNotNullableSQL(String tableName, Column<?> column) {
326 return "ALTER TABLE " + getQuotedName(tableName) + " ALTER COLUMN "
327 + getQuotedName(column.getName()) + " "
328 + column.getSQLType().sqlDefinition(this);
329 }
330
331 @Override
332 public String selectLimit(String querySelection, int limit) {
333 return "SELECT TOP " + limit + " " + querySelection;
334 }
335
336
337 @Override
338 public String getSqlDefaultValue(SQLType<?> sqlType) {
339 if (sqlType instanceof BooleanPoemType) {
340 return ("0");
341 }
342 return super.getSqlDefaultValue(sqlType);
343 }
344 }