View Javadoc
1   /*
2   Copyright (c) 2005 Health Market Science, Inc.
3   
4   Licensed under the Apache License, Version 2.0 (the "License");
5   you may not use this file except in compliance with the License.
6   You may obtain a copy of the License at
7   
8       http://www.apache.org/licenses/LICENSE-2.0
9   
10  Unless required by applicable law or agreed to in writing, software
11  distributed under the License is distributed on an "AS IS" BASIS,
12  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13  See the License for the specific language governing permissions and
14  limitations under the License.
15  */
16  
17  package com.healthmarketscience.jackcess;
18  
19  import java.io.IOException;
20  import java.sql.SQLException;
21  import java.sql.Types;
22  import java.util.HashMap;
23  import java.util.Map;
24  import java.util.Date;
25  import java.math.BigDecimal;
26  import java.math.BigInteger;
27  
28  import com.healthmarketscience.jackcess.impl.JetFormat;
29  
30  /**
31   * Supported access data types.
32   * 
33   * @author Tim McCune
34   * @usage _general_class_
35   */
36  public enum DataType {
37  
38    /**
39     * Corresponds to a java {@link Boolean}.  Accepts Boolean or {@code null}
40     * (which is considered {@code false}).  Equivalent to SQL {@link
41     * Types#BOOLEAN}.
42     */
43    BOOLEAN((byte) 0x01, Types.BOOLEAN, 0),
44    /**
45     * Corresponds to a java {@link Byte}.  Accepts any {@link Number} (using
46     * {@link Number#byteValue}), Boolean as 1 or 0, any Object converted to a
47     * String and parsed as Double, or {@code null}.  Equivalent to SQL
48     * {@link Types#TINYINT}, {@link Types#BIT}.
49     */
50    BYTE((byte) 0x02, Types.TINYINT, 1),
51    /**
52     * Corresponds to a java {@link Short}.  Accepts any {@link Number} (using
53     * {@link Number#shortValue}), Boolean as 1 or 0, any Object converted to a
54     * String and parsed as Double, or {@code null}.  Equivalent to SQL
55     * {@link Types#SMALLINT}.
56     */
57    INT((byte) 0x03, Types.SMALLINT, 2),
58    /**
59     * Corresponds to a java {@link Integer}.  Accepts any {@link Number} (using
60     * {@link Number#intValue}), Boolean as 1 or 0, any Object converted to a
61     * String and parsed as Double, or {@code null}.  Equivalent to SQL
62     * {@link Types#INTEGER}, {@link Types#BIGINT}.
63     */
64    LONG((byte) 0x04, Types.INTEGER, 4),
65    /**
66     * Corresponds to a java {@link BigDecimal} with at most 4 decimal places.
67     * Accepts any {@link Number} (using {@link Number#doubleValue}), a
68     * BigInteger, a BigDecimal (with at most 4 decimal places), Boolean as 1 or
69     * 0, any Object converted to a String and parsed as BigDecimal, or {@code
70     * null}.  Equivalent to SQL {@link Types#DECIMAL}.
71     */
72    MONEY((byte) 0x05, Types.DECIMAL, 8, false, false, 0, 0, 0, false, 4, 4, 4,
73          19, 19, 19, 1),
74    /**
75     * Corresponds to a java {@link Float}.  Accepts any {@link Number} (using
76     * {@link Number#floatValue}), Boolean as 1 or 0, any Object converted to a
77     * String and parsed as Double, or {@code null}.  Equivalent to SQL
78     * {@link Types#FLOAT}.
79     */
80    FLOAT((byte) 0x06, Types.FLOAT, 4),
81    /**
82     * Corresponds to a java {@link Double}.  Accepts any {@link Number} (using
83     * {@link Number#doubleValue}), Boolean as 1 or 0, any Object converted to a
84     * String and parsed as Double, or {@code null}.  Equivalent to SQL
85     * {@link Types#DOUBLE}, {@link Types#REAL}.
86     */
87    DOUBLE((byte) 0x07, Types.DOUBLE, 8),
88    /**
89     * Corresponds to a java {@link Date}.  Accepts a Date, any {@link Number}
90     * (using {@link Number#longValue}), or {@code null}.  Equivalent to SQL
91     * {@link Types#TIMESTAMP}, {@link Types#DATE}, {@link Types#TIME}.
92     */
93    SHORT_DATE_TIME((byte) 0x08, Types.TIMESTAMP, 8),
94    /**
95     * Corresponds to a java {@code byte[]} of max length 255 bytes.  Accepts a
96     * {@code byte[]}, or {@code null}.  Equivalent to SQL {@link Types#BINARY},
97     * {@link Types#VARBINARY}.
98     */
99    BINARY((byte) 0x09, Types.BINARY, null, true, false, 0, 255, 255, 1),
100   /**
101    * Corresponds to a java {@link String} of max length 255 chars.  Accepts
102    * any {@link CharSequence}, any Object converted to a String , or {@code
103    * null}.  Equivalent to SQL {@link Types#VARCHAR}, {@link Types#CHAR}.
104    */
105   TEXT((byte) 0x0A, Types.VARCHAR, null, true, false, 0,
106        JetFormat.TEXT_FIELD_MAX_LENGTH, JetFormat.TEXT_FIELD_MAX_LENGTH, 
107        JetFormat.TEXT_FIELD_UNIT_SIZE),
108   /**
109    * Corresponds to a java {@code byte[]} of max length 16777215 bytes.
110    * Accepts a {@code byte[]}, or {@code null}.  Equivalent to SQL
111    * {@link Types#LONGVARBINARY}, {@link Types#BLOB}.
112    */
113   OLE((byte) 0x0B, Types.LONGVARBINARY, null, true, true, 0, 0, 0x3FFFFFFF,
114       1),
115   /**
116    * Corresponds to a java {@link String} of max length 8388607 chars.
117    * Accepts any {@link CharSequence}, any Object converted to a String , or
118    * {@code null}.  Equivalent to SQL {@link Types#LONGVARCHAR}, {@link
119    * Types#CLOB}.
120    */
121   MEMO((byte) 0x0C, Types.LONGVARCHAR, null, true, true, 0, 0, 0x3FFFFFFF,
122        JetFormat.TEXT_FIELD_UNIT_SIZE),
123   /**
124    * Unknown data.  Handled like {@link #BINARY}.
125    */
126   UNKNOWN_0D((byte) 0x0D, null, null, true, false, 0, 255, 255, 1),
127   /**
128    * Corresponds to a java {@link String} with the pattern
129    * <code>"{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}"</code>, also known as a
130    * "Replication ID" in Access.  Accepts any
131    * Object converted to a String matching this pattern (surrounding "{}" are
132    * optional, so {@link java.util.UUID}s are supported), or {@code null}.
133    */
134   GUID((byte) 0x0F, null, 16),
135   /**
136    * Corresponds to a java {@link BigDecimal}.  Accepts any {@link Number}
137    * (using {@link Number#doubleValue}), a {@link BigInteger}, a BigDecimal,
138    * Boolean as 1 or 0, any Object converted to a String and parsed as
139    * BigDecimal, or {@code null}.  Equivalent to SQL {@link Types#NUMERIC}.
140    */
141   // for some reason numeric is "var len" even though it has a fixed size...
142   NUMERIC((byte) 0x10, Types.NUMERIC, 17, true, false, 17, 17, 17,
143           true, 0, 0, 28, 1, 18, 28, 1),
144   /**
145    * Unknown data (seems to be an alternative {@link #OLE} type, used by
146    * MSysAccessObjects table).  Handled like a fixed length BINARY/OLE.
147    */
148   UNKNOWN_11((byte) 0x11, null, 3992),
149   /**
150    * Complex type corresponds to a special {@link #LONG} autonumber field
151    * which is the key for a secondary table which holds the "real" data.
152    */
153   COMPLEX_TYPE((byte) 0x12, null, 4),    
154   /**
155    * Dummy type for a fixed length type which is not currently supported.
156    * Handled like a fixed length {@link #BINARY}.
157    */
158   UNSUPPORTED_FIXEDLEN((byte) 0xFE, null, null),
159   /**
160    * Placeholder type for a variable length type which is not currently
161    * supported.  Handled like {@link #BINARY}.
162    */
163   UNSUPPORTED_VARLEN((byte) 0xFF, null, null, true, false, 0, 0, 0x3FFFFFFF,
164       1);
165 
166   /** Map of SQL types to Access data types */
167   private static final Map<Integer, DataType> SQL_TYPES =
168     new HashMap<Integer, DataType>();
169   /** Alternate map of SQL types to Access data types */
170   private static final Map<Integer, DataType> ALT_SQL_TYPES =
171     new HashMap<Integer, DataType>();
172   static {
173     for (DataType type : DataType.values()) {
174       if (type._sqlType != null) {
175         SQL_TYPES.put(type._sqlType, type);
176       }
177     }
178     SQL_TYPES.put(Types.BIT, BYTE);
179     SQL_TYPES.put(Types.BLOB, OLE);
180     SQL_TYPES.put(Types.CLOB, MEMO);
181     SQL_TYPES.put(Types.BIGINT, LONG);
182     SQL_TYPES.put(Types.CHAR, TEXT);
183     SQL_TYPES.put(Types.DATE, SHORT_DATE_TIME);
184     SQL_TYPES.put(Types.REAL, DOUBLE);
185     SQL_TYPES.put(Types.TIME, SHORT_DATE_TIME);
186     SQL_TYPES.put(Types.VARBINARY, BINARY);
187 
188     // the "alternate" types allow for larger values
189     ALT_SQL_TYPES.put(Types.VARCHAR, MEMO);
190     ALT_SQL_TYPES.put(Types.VARBINARY, OLE);
191     ALT_SQL_TYPES.put(Types.BINARY, OLE);
192 
193     // add newer sql types if available in this jvm
194     addNewSqlType("NCHAR", TEXT, null);
195     addNewSqlType("NVARCHAR", TEXT, MEMO);
196     addNewSqlType("LONGNVARCHAR", MEMO, null);
197     addNewSqlType("NCLOB", MEMO, null);
198     addNewSqlType("TIME_WITH_TIMEZONE", SHORT_DATE_TIME, null);
199     addNewSqlType("TIMESTAMP_WITH_TIMEZONE", SHORT_DATE_TIME, null);
200   }
201   
202   private static Map<Byte, DataType> DATA_TYPES = new HashMap<Byte, DataType>();
203   static {
204     for (DataType type : DataType.values()) {
205       if(type.isUnsupported()) {
206         continue;
207       }
208       DATA_TYPES.put(type._value, type);
209     }
210   }
211 
212   /** is this a variable length field */
213   private final boolean _variableLength;
214   /** is this a long value field */
215   private final boolean _longValue;
216   /** does this field have scale/precision */
217   private final boolean _hasScalePrecision;
218   /** Internal Access value */
219   private final byte _value;
220   /** Size in bytes of fixed length columns */
221   private final Integer _fixedSize;
222   /** min in bytes size for var length columns */
223   private final int _minSize;
224   /** default size in bytes for var length columns */
225   private final int _defaultSize;
226   /** Max size in bytes for var length columns */
227   private final int _maxSize;
228   /** SQL type equivalent, or null if none defined */
229   private final Integer _sqlType;
230   /** min scale value */
231   private final int _minScale;
232   /** the default scale value */
233   private final int _defaultScale;
234   /** max scale value */
235   private final int _maxScale;
236   /** min precision value */
237   private final int _minPrecision;
238   /** the default precision value */
239   private final int _defaultPrecision;
240   /** max precision value */
241   private final int _maxPrecision;
242   /** the number of bytes per "unit" for this data type */
243   private final int _unitSize;
244   
245   private DataType(byte value) {
246     this(value, null, null);
247   }
248   
249   private DataType(byte value, Integer sqlType, Integer fixedSize) {
250     this(value, sqlType, fixedSize, false, false, 0, 0, 0, 1);
251   }
252 
253   private DataType(byte value, Integer sqlType, Integer fixedSize,
254                    boolean variableLength,
255                    boolean longValue,
256                    int minSize,
257                    int defaultSize,
258                    int maxSize,
259                    int unitSize) {
260     this(value, sqlType, fixedSize, variableLength, longValue,
261          minSize, defaultSize, maxSize,
262          false, 0, 0, 0, 0, 0, 0, unitSize);
263   }
264   
265   private DataType(byte value, Integer sqlType, Integer fixedSize,
266                    boolean variableLength,
267                    boolean longValue,
268                    int minSize,
269                    int defaultSize,
270                    int maxSize,
271                    boolean hasScalePrecision,
272                    int minScale,
273                    int defaultScale,
274                    int maxScale,
275                    int minPrecision,
276                    int defaultPrecision,
277                    int maxPrecision,
278                    int unitSize) {
279     _value = value;
280     _sqlType = sqlType;
281     _fixedSize = fixedSize;
282     _variableLength = variableLength;
283     _longValue = longValue;
284     _minSize = minSize;
285     _defaultSize = defaultSize;
286     _maxSize = maxSize;
287     _hasScalePrecision = hasScalePrecision;
288     _minScale = minScale;
289     _defaultScale = defaultScale;
290     _maxScale = maxScale;
291     _minPrecision = minPrecision;
292     _defaultPrecision = defaultPrecision;
293     _maxPrecision = maxPrecision;
294     _unitSize = unitSize;
295   }
296   
297   public byte getValue() {
298     return _value;
299   }
300   
301   public boolean isVariableLength() {
302     return _variableLength;
303   }
304 
305   public boolean isTrueVariableLength() {
306     // some "var len" fields do not really have a variable length,
307     // e.g. NUMERIC
308     return (isVariableLength() && (getMinSize() != getMaxSize()));
309   }
310   
311   public boolean isLongValue() {
312     return _longValue;
313   }
314 
315   public boolean getHasScalePrecision() {
316     return _hasScalePrecision;
317   }
318 
319   public int getFixedSize() {
320     return getFixedSize(null);
321   }
322   
323   public int getFixedSize(Short colLength) {
324     if(_fixedSize != null) {
325       if(colLength != null) {
326         return Math.max(_fixedSize, colLength);
327       }
328       return _fixedSize;
329     }
330     if(colLength != null) {
331       return colLength;
332     }
333     throw new IllegalArgumentException("Unexpected fixed length column " + 
334                                        this);
335   }
336 
337   public int getMinSize() {
338     return _minSize;
339   }
340 
341   public int getDefaultSize() {
342     return _defaultSize;
343   }
344 
345   public int getMaxSize() {
346     return _maxSize;
347   }
348   
349   public int getSQLType() throws SQLException {
350     if (_sqlType != null) {
351       return _sqlType;
352     }
353     throw new SQLException("Unsupported data type: " + toString());
354   }
355 
356   public int getMinScale() {
357     return _minScale;
358   }
359 
360   public int getDefaultScale() {
361     return _defaultScale;
362   }
363   
364   public int getMaxScale() {
365     return _maxScale;
366   }
367   
368   public int getMinPrecision() {
369     return _minPrecision;
370   }
371   
372   public int getDefaultPrecision() {
373     return _defaultPrecision;
374   }
375   
376   public int getMaxPrecision() {
377     return _maxPrecision;
378   }
379 
380   public int getUnitSize() {
381     return _unitSize;
382   }
383 
384   public int toUnitSize(int size)
385   {
386     return(size / getUnitSize());
387   }
388 
389   public int fromUnitSize(int unitSize)
390   {
391     return(unitSize * getUnitSize());
392   }
393 
394   public boolean isValidSize(int size) {
395     return isWithinRange(size, getMinSize(), getMaxSize());
396   }
397 
398   public boolean isValidScale(int scale) {
399     return isWithinRange(scale, getMinScale(), getMaxScale());
400   }
401 
402   public boolean isValidPrecision(int precision) {
403     return isWithinRange(precision, getMinPrecision(), getMaxPrecision());
404   }
405 
406   private static boolean isWithinRange(int value, int minValue, int maxValue) {
407     return((value >= minValue) && (value <= maxValue));
408   }
409   
410   public int toValidSize(int size) {
411     return toValidRange(size, getMinSize(), getMaxSize());
412   }
413 
414   public int toValidScale(int scale) {
415     return toValidRange(scale, getMinScale(), getMaxScale());
416   }
417 
418   public int toValidPrecision(int precision) {
419     return toValidRange(precision, getMinPrecision(), getMaxPrecision());
420   }
421 
422   public boolean isTextual() {
423     return ((this == TEXT) || (this == MEMO));
424   }
425 
426   public boolean mayBeAutoNumber() {
427     return((this == LONG) || (this == GUID) || (this == COMPLEX_TYPE));
428   }
429 
430   public boolean isMultipleAutoNumberAllowed() {
431     return (this == COMPLEX_TYPE);
432   }
433 
434   public boolean isUnsupported() {
435     return((this == UNSUPPORTED_FIXEDLEN) || (this == UNSUPPORTED_VARLEN));
436   }
437   
438   private static int toValidRange(int value, int minValue, int maxValue) {
439     return((value > maxValue) ? maxValue :
440            ((value < minValue) ? minValue : value));
441   }
442   
443   public static DataType fromByte(byte b) throws IOException {
444     DataType rtn = DATA_TYPES.get(b);
445     if (rtn != null) {
446       return rtn;
447     }
448     throw new IOException("Unrecognized data type: " + b);
449   }
450   
451   public static DataType fromSQLType(int sqlType)
452     throws SQLException
453   {
454     return fromSQLType(sqlType, 0);
455   }
456   
457   public static DataType fromSQLType(int sqlType, int lengthInUnits)
458     throws SQLException
459   {
460     DataType rtn = SQL_TYPES.get(sqlType);
461     if(rtn == null) {
462       throw new SQLException("Unsupported SQL type: " + sqlType);
463     }
464 
465     // make sure size is reasonable
466     int size = lengthInUnits * rtn.getUnitSize();
467     if(rtn.isVariableLength() && !rtn.isValidSize(size)) {
468       // try alternate type.  we always accept alternate "long value" types
469       // regardless of the given lengthInUnits
470       DataType altRtn = ALT_SQL_TYPES.get(sqlType);
471       if((altRtn != null) &&
472          (altRtn.isLongValue() || altRtn.isValidSize(size))) {
473         // use alternate type
474         rtn = altRtn;
475       }
476     }
477       
478     return rtn;
479   }
480 
481   /**
482    * Adds mappings for a sql type which was added after jdk 1.5 (using
483    * reflection).
484    */
485   private static void addNewSqlType(String typeName, DataType type, 
486                                     DataType altType)
487   {
488     try {
489       java.lang.reflect.Field sqlTypeField = Types.class.getField(typeName);
490       Integer value = (Integer)sqlTypeField.get(null);
491       SQL_TYPES.put(value, type);
492       if(altType != null) {
493         ALT_SQL_TYPES.put(value, altType);
494       }
495     } catch(Exception ignored) {
496       // must not be available
497     }
498   }
499 
500 }