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