Java > Open Source Codes > com > sqlmagic > tinysql > dbfFileDatabaseMetaData


1 /*
2  * dbfFileDatabaseMetaData.java
3  */
4
5 package com.sqlmagic.tinysql;
6
7 /**
8  * Comprehensive information about the database as a whole.
9  *
10  * Many of the methods here return lists of information in
11  * the form of ResultSet objects.
12  * You can use the normal ResultSet methods such as getString and getInt
13  * to retrieve the data from these ResultSets. If a given form of
14  * metadata is not available, these methods should throw an SQLException.
15  *
16  * Some of these methods take arguments that are String patterns. These
17  * arguments all have names such as fooPattern. Within a pattern String, "%"
18  * means match any substring of 0 or more characters, and "_" means match
19  * any one character. Only metadata entries matching the search pattern
20  * are returned. If a search pattern argument is set to a null ref,
21  * that argument's criteria will be dropped from the search.
22  *
23  * An SQLException will be thrown if a driver does not support a meta
24  * data method. In the case of methods that return a ResultSet,
25  * either a ResultSet (which may be empty) is returned or a
26  * SQLException is thrown.
27  *
28  * $Author: davis $
29  * $Date: 2004/12/18 21:30:57 $
30  * $Revision: 1.1 $
31  *
32  */
33 import java.sql.SQLException ;
34 import java.sql.Connection ;
35 import java.sql.ResultSet ;
36 import java.sql.Types ;
37 import java.util.Vector ;
38 import java.io.File ;
39
40
41 /**
42 dBase read/write access <br>
43 @author Brian Jepson <bjepson@home.com>
44 @author Marcel Ruff <ruff@swand.lake.de> Added DatabaseMetaData with JDK 2 support
45 @author Thomas Morgner <mgs@sherito.org> Changed DatabaseMetaData to use java.sql.Types.
46 */
47 public class dbfFileDatabaseMetaData extends tinySQLDatabaseMetaData {
48   private final String emptyString = "";
49
50     public dbfFileDatabaseMetaData(Connection connection) {
51         super(connection);
52     }
53
54     public String getDatabaseProductName()
55     {
56         return "tinySQL";
57     }
58     public String getDatabaseProductVersion()
59     {
60         return "2.0";
61     }
62     String getDataDir()
63     {
64         String url = ((dbfFileConnection)getConnection()).url;
65         if (url.length() <= 13)
66             return null;
67
68         String dataDir = url.substring(13);
69         return dataDir;
70     }
71
72     /**
73      * Gets a description of all the standard SQL types supported by
74      * this database. They are ordered by DATA_TYPE and then by how
75      * closely the data type maps to the corresponding JDBC SQL type.
76      *
77      * <P>Each type description has the following columns:
78      * <OL>
79      * <LI><B>TYPE_NAME</B> String => Type name
80      * <LI><B>DATA_TYPE</B> short => SQL data type from java.sql.Types
81      * <LI><B>PRECISION</B> int => maximum precision
82      * <LI><B>LITERAL_PREFIX</B> String => prefix used to quote a literal
83      * (may be null)
84      * <LI><B>LITERAL_SUFFIX</B> String => suffix used to quote a literal
85             (may be null)
86      * <LI><B>CREATE_PARAMS</B> String => parameters used in creating
87      * the type (may be null)
88      * <LI><B>NULLABLE</B> short => can you use NULL for this type?
89      * <UL>
90      * <LI> typeNoNulls - does not allow NULL values
91      * <LI> typeNullable - allows NULL values
92      * <LI> typeNullableUnknown - nullability unknown
93      * </UL>
94      * <LI><B>CASE_SENSITIVE</B> boolean=> is it case sensitive?
95      * <LI><B>SEARCHABLE</B> short => can you use "WHERE" based on this type:
96      * <UL>
97      * <LI> typePredNone - No support
98      * <LI> typePredChar - Only supported with WHERE .. LIKE
99      * <LI> typePredBasic - Supported except for WHERE .. LIKE
100      * <LI> typeSearchable - Supported for all WHERE ..
101      * </UL>
102      * <LI><B>UNSIGNED_ATTRIBUTE</B> boolean => is it unsigned?
103      * <LI><B>FIXED_PREC_SCALE</B> boolean => can it be a money value?
104      * <LI><B>AUTO_INCREMENT</B> boolean => can it be used for an
105      * auto-increment value?
106      * <LI><B>LOCAL_TYPE_NAME</B> String => localized version of type name
107      * (may be null)
108      * <LI><B>MINIMUM_SCALE</B> short => minimum scale supported
109      * <LI><B>MAXIMUM_SCALE</B> short => maximum scale supported
110      * <LI><B>SQL_DATA_TYPE</B> int => unused
111      * <LI><B>SQL_DATETIME_SUB</B> int => unused
112      * <LI><B>NUM_PREC_RADIX</B> int => usually 2 or 10
113      * </OL>
114      *
115      * @return ResultSet - each row is a SQL type description
116      * @exception SQLException if a database access error occurs
117      */
118       public ResultSet getTypeInfo() throws SQLException {
119         tsResultSet jrs = new tsResultSet(null, null, null, null);
120
121         tsColumn jsc = new tsColumn("TYPE_NAME");
122         jsc.type = Types.CHAR;
123         jsc.size = 10;
124         jrs.addColumn (jsc);
125
126         jsc = new tsColumn("DATA_TYPE");
127         jsc.type = Types.INTEGER;
128         jsc.size = 6;
129         jrs.addColumn (jsc);
130
131         jsc = new tsColumn("PRECISION");
132         jsc.type = Types.INTEGER;
133         jsc.size = 8;
134         jrs.addColumn (jsc);
135
136         jsc = new tsColumn("LITERAL_PREFIX");
137         jsc.type = Types.CHAR;
138         jsc.size = 1;
139         jrs.addColumn (jsc);
140
141         jsc = new tsColumn("LITERAL_SUFFIX");
142         jsc.type = Types.CHAR;
143         jsc.size = 1;
144         jrs.addColumn (jsc);
145
146         jsc = new tsColumn("CREATE_PARAMS");
147         jsc.type = Types.CHAR;
148         jsc.size = 20;
149         jrs.addColumn (jsc);
150
151         jsc = new tsColumn("NULLABLE");
152         jsc.type = Types.INTEGER;
153         jsc.size = 6;
154         jrs.addColumn (jsc);
155
156         jsc = new tsColumn("CASE_SENSITIVE");
157         jsc.type = Types.BIT;
158         jsc.size = 1;
159         jrs.addColumn (jsc);
160
161         jsc = new tsColumn("SEARCHABLE");
162         jsc.type = Types.INTEGER;
163         jsc.size = 6;
164         jrs.addColumn (jsc);
165
166          /*
167          * <LI><B>UNSIGNED_ATTRIBUTE</B> boolean => is it unsigned?
168          * <LI><B>FIXED_PREC_SCALE</B> boolean => can it be a money value?
169          * <LI><B>AUTO_INCREMENT</B> boolean => can it be used for an
170          * auto-increment value?
171          * <LI><B>LOCAL_TYPE_NAME</B> String => localized version of type name
172          * (may be null)
173          * <LI><B>MINIMUM_SCALE</B> short => minimum scale supported
174          * <LI><B>MAXIMUM_SCALE</B> short => maximum scale supported
175          * <LI><B>NUM_PREC_RADIX</B> int => usually 2 or 10
176          */
177
178
179         // NOTE: the Hashtable in tsRow expects always a String as its value!
180 // so i use the toString() method here
181 // Perhaps in future the real type should be pushed into the Hashtable?
182
183         tsRow record = new tsRow();
184         record.put("TYPE_NAME", dbfFile.typeToLiteral(Types.CHAR)); // "CHAR", String
185 record.put("DATA_TYPE", new Integer (Types.CHAR).toString());
186         record.put("PRECISION", new Integer (254).toString());
187         record.put("LITERAL_PREFIX", "\"");
188         record.put("LITERAL_SUFFIX", "\"");
189         record.put("CREATE_PARAMS", new Integer (0).toString());
190         record.put("NULLABLE", new Integer (typeNullableUnknown).toString());
191         record.put("CASE_SENSITIVE", "N");
192         record.put("SEARCHABLE", new Integer (typePredBasic).toString());
193         jrs.addRow (record) ;
194
195         record = new tsRow();
196         record.put("TYPE_NAME", dbfFile.typeToLiteral(Types.FLOAT)); // "FLOAT", double
197 record.put("DATA_TYPE", new Integer (Types.FLOAT).toString());
198         record.put("PRECISION", new Integer (19).toString());
199         record.put("LITERAL_PREFIX", emptyString);
200         record.put("LITERAL_SUFFIX", emptyString);
201         record.put("CREATE_PARAMS", new Integer (0).toString());
202         record.put("NULLABLE", new Integer (typeNullableUnknown).toString());
203         record.put("CASE_SENSITIVE", "N");
204         record.put("SEARCHABLE", new Integer (typePredBasic).toString());
205         jrs.addRow (record) ;
206
207         record = new tsRow();
208         record.put("TYPE_NAME", dbfFile.typeToLiteral(Types.BIT)); // "CHAR", boolean "YyNnTtFf"
209 record.put("DATA_TYPE", new Integer (Types.BIT).toString());
210         record.put("PRECISION", new Integer (1).toString());
211         record.put("LITERAL_PREFIX", "\"");
212         record.put("LITERAL_SUFFIX", "\"");
213         record.put("CREATE_PARAMS", new Integer (0).toString());
214         record.put("NULLABLE", new Integer (typeNullableUnknown).toString());
215         record.put("CASE_SENSITIVE", "N");
216         record.put("SEARCHABLE", new Integer (typePredBasic).toString());
217         jrs.addRow (record) ;
218
219         record = new tsRow();
220         record.put("TYPE_NAME", dbfFile.typeToLiteral(Types.INTEGER)); // "INT", unsigned long
221 record.put("DATA_TYPE", new Integer (Types.INTEGER).toString());
222         record.put("PRECISION", new Integer (19).toString());
223         record.put("LITERAL_PREFIX", emptyString);
224         record.put("LITERAL_SUFFIX", emptyString);
225         record.put("CREATE_PARAMS", new Integer (0).toString());
226         record.put("NULLABLE", new Integer (typeNullableUnknown).toString());
227         record.put("CASE_SENSITIVE", "N");
228         record.put("SEARCHABLE", new Integer (typePredBasic).toString());
229         jrs.addRow (record) ;
230
231         record = new tsRow();
232         record.put("TYPE_NAME", dbfFile.typeToLiteral(Types.DATE)); // "DATE", date
233 record.put("DATA_TYPE", new Integer (Types.DATE).toString());
234         record.put("PRECISION", new Integer (8).toString());
235         record.put("LITERAL_PREFIX", "\"");
236         record.put("LITERAL_SUFFIX", "\"");
237         record.put("CREATE_PARAMS", new Integer (0).toString());
238         record.put("NULLABLE", new Integer (typeNullableUnknown).toString());
239         record.put("CASE_SENSITIVE", "N");
240         record.put("SEARCHABLE", new Integer (typePredBasic).toString());
241         jrs.addRow (record) ;
242
243         return new tinySQLResultSet(jrs, (tinySQLStatement)null);
244       }
245 /*
246  * Gets a description of tables available in a catalog.
247  *
248  * Only table descriptions matching the catalog, schema, table
249  * name and type criteria are returned. They are ordered by
250  * TABLE_TYPE, TABLE_SCHEM and TABLE_NAME.
251  *
252  * Each table description has the following columns:
253  *
254  * TABLE_CAT String => table catalog (may be null)
255  * TABLE_SCHEM String => table schema (may be null)
256  * TABLE_NAME String => table name
257  * TABLE_TYPE String => table type. Typical types are "TABLE",
258  * "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY",
259  * "LOCAL TEMPORARY", "ALIAS", "SYNONYM".
260  * REMARKS String => explanatory comment on the table
261  *
262  * Note: Some databases may not return information for
263  * all tables.
264  *
265  * @param catalog a catalog name; "" retrieves those without a
266  * catalog; null means drop catalog name from the selection criteria
267  * THIS VALUE IS IGNORED
268  * @param schemaPattern THIS VALUE IS IGNORED
269  * @param tableNamePattern a table name pattern, ´null´ or "%" delivers all
270  * token will be handled as substrings
271  * @param types a list of table types to include; null returns all DBF types
272  * only "TABLE" is supported, others like "VIEW", "SYSTEM TABLE", "SEQUENCE"
273  * are ignored.
274  * @return ResultSet - each row is a table description
275  * @exception SQLException if a database access error occurs
276  * @see #getSearchStringEscape
277  *
278  * @author Thomas Morgner <mgs@sherito.org> Fill all needed columns, or some query tools will crash :(
279  */
280    public ResultSet getTables(String catalog, String schemaPattern,
281             String tableNamePattern, String types[])
282    {
283       String dataDir = getDataDir();
284       String tableName;
285       File tableFile;
286       tsColumn jsc;
287       int i,dotAt;
288       if (dataDir == null) return null;
289       if (types == null)
290       {
291         types = new String [1];
292         types[0] = "TABLE";
293       }
294       tsResultSet jrs = new tsResultSet(null, null, null, null);
295 /*
296  * Create the header for the tables ResultSet
297  */
298       try
299       {
300          jsc = new tsColumn("TABLE_CAT");
301          jsc.type = Types.CHAR; // CHAR max 254 bytes
302 jsc.size = 10;
303          jrs.addColumn (jsc);
304      
305          jsc = new tsColumn("TABLE_SCHEM");
306          jsc.type = Types.CHAR; // CHAR max 254 bytes
307 jsc.size = 10;
308          jrs.addColumn (jsc);
309          
310          jsc = new tsColumn("TABLE_NAME");
311          jsc.type = Types.CHAR; // CHAR max 254 bytes
312 jsc.size = 10;
313          jrs.addColumn (jsc);
314
315          jsc = new tsColumn("TABLE_TYPE");
316          jsc.type = Types.CHAR; // CHAR max 254 bytes
317 jsc.size = 40;
318          jsc.defaultVal = "TABLE";
319          jrs.addColumn (jsc);
320       
321          jsc = new tsColumn("TABLE_REMARKS");
322          jsc.type = Types.CHAR; // CHAR max 254 bytes
323 jsc.size = 254;
324          jrs.addColumn (jsc);
325 /*
326  * Add the MetaData by examining all the DBF files in the current
327  * directory.
328  */
329          for ( int itype=0; itype < types.length; itype++ )
330          {
331             String type = types[itype];
332             if (type == null) continue;
333             String extension = null;
334             if (type.equalsIgnoreCase("TABLE"))
335                extension = dbfFileTable.dbfExtension; // ".DBF";
336 if (extension == null) continue;
337             Vector vec = Utils.getAllFiles(dataDir, extension);
338             for ( i = 0; i < vec.size(); i++)
339             {
340                tableFile = (File )vec.elementAt(i);
341                tableName = tableFile.getName().toUpperCase();
342                dotAt = tableName.indexOf(".");
343                if ( dotAt > -1 ) tableName = tableName.substring(0,dotAt);
344                if (tableNamePattern == null ) tableNamePattern = "%";
345                if ( tableNamePattern.equals("%") |
346                    tableName.indexOf(tableNamePattern) >= 0)
347                {
348                   if (tableName.length() > jsc.size)
349                      jsc.size = tableName.length();
350                   tsRow record = new tsRow();
351                   record.put("TABLE_NAME", tableName.toUpperCase());
352                   record.put("TABLE_TYPE", "TABLE");
353                   jrs.addRow (record) ;
354                }
355             }
356          }
357       } catch (Exception ex ) {
358          System.out.println("Unable to create MetaData");
359       }
360
361       // This Resultset is not created by an statement
362 return new tinySQLResultSet(jrs, (tinySQLStatement)null);
363    }
364     /**
365      * Gets a description of table columns available in
366          * the specified catalog.
367      *
368      * <P>Only column descriptions matching the catalog, schema, table
369      * and column name criteria are returned. They are ordered by
370      * TABLE_SCHEM, TABLE_NAME and ORDINAL_POSITION.
371      *
372      * <P>Each column description has the following columns:
373      * <OL>
374      * <LI><B>TABLE_CAT</B> String => table catalog (may be null)
375      * <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
376      * <LI><B>TABLE_NAME</B> String => table name
377      * <LI><B>COLUMN_NAME</B> String => column name
378      * <LI><B>DATA_TYPE</B> short => SQL type from java.sql.Types
379      * <LI><B>TYPE_NAME</B> String => Data source dependent type name,
380      * for a UDT the type name is fully qualified
381      * <LI><B>COLUMN_SIZE</B> int => column size. For char or date
382      * types this is the maximum number of characters, for numeric or
383      * decimal types this is precision.
384      * <LI><B>BUFFER_LENGTH</B> is not used.
385      * <LI><B>DECIMAL_DIGITS</B> int => the number of fractional digits
386      * <LI><B>NUM_PREC_RADIX</B> int => Radix (typically either 10 or 2)
387      * <LI><B>NULLABLE</B> int => is NULL allowed?
388      * <UL>
389      * <LI> columnNoNulls - might not allow NULL values
390      * <LI> columnNullable - definitely allows NULL values
391      * <LI> columnNullableUnknown - nullability unknown
392      * </UL>
393      * <LI><B>REMARKS</B> String => comment describing column (may be null)
394      * <LI><B>COLUMN_DEF</B> String => default value (may be null)
395      * <LI><B>SQL_DATA_TYPE</B> int => unused
396      * <LI><B>SQL_DATETIME_SUB</B> int => unused
397      * <LI><B>CHAR_OCTET_LENGTH</B> int => for char types the
398      * maximum number of bytes in the column
399      * <LI><B>ORDINAL_POSITION</B> int => index of column in table
400      * (starting at 1)
401      * <LI><B>IS_NULLABLE</B> String => "NO" means column definitely
402      * does not allow NULL values; "YES" means the column might
403      * allow NULL values. An empty string means nobody knows.
404      * </OL>
405      *
406      * @param catalog a catalog name; "" retrieves those without a
407      * catalog; null means drop catalog name from the selection criteria
408      * @param schemaPattern a schema name pattern; "" retrieves those
409      * without a schema
410      * @param tableNamePattern a table name pattern
411      * @param columnNamePattern a column name pattern
412      * @return ResultSet - each row is a column description
413      * @exception SQLException if a database access error occurs
414      * @see #getSearchStringEscape
415      */
416     public ResultSet getColumns(String catalog, String schemaPattern,
417                 String tableNamePattern, String columnNamePattern)
418     {
419       try {
420         String dataDir = getDataDir();
421
422         Utils.log("Entering getColumns(tableNamePattern='" + tableNamePattern + "')");
423
424         if (dataDir == null) return null;
425
426         ResultSet tableRs = getTables(catalog, schemaPattern, tableNamePattern, null);
427
428         tsResultSet jrs = new tsResultSet(null, null, null, null);
429
430         tsColumn jsc = new tsColumn("TABLE_CAT");
431         jsc.type = Types.CHAR;
432         jsc.size = 9;
433         jrs.addColumn (jsc);
434
435         jsc = new tsColumn("TABLE_SCHEM");
436         jsc.type = Types.CHAR;
437         jsc.size = 11;
438         jrs.addColumn (jsc);
439
440         jsc = new tsColumn("TABLE_NAME");
441         jsc.type = Types.CHAR;
442         jsc.size = 10;
443         jrs.addColumn (jsc);
444
445         jsc = new tsColumn("COLUMN_NAME");
446         jsc.type = Types.CHAR;
447         jsc.size = 11;
448         jrs.addColumn (jsc);
449
450         jsc = new tsColumn("DATA_TYPE");
451         jsc.type = Types.INTEGER;
452         jsc.size = 6;
453         jrs.addColumn (jsc);
454
455         jsc = new tsColumn("TYPE_NAME");
456         jsc.type = Types.CHAR;
457         jsc.size = 9;
458         jrs.addColumn (jsc);
459
460         jsc = new tsColumn("COLUMN_SIZE");
461         jsc.type = Types.INTEGER;
462         jsc.size = 8;
463         jrs.addColumn (jsc);
464
465         jsc = new tsColumn("BUFFER_LENGTH");
466         jsc.type = Types.INTEGER;
467         jsc.size = 8;
468         jrs.addColumn (jsc);
469
470         jsc = new tsColumn("DECIMAL_DIGITS");
471         jsc.type = Types.INTEGER;
472         jsc.size = 8;
473         jrs.addColumn (jsc);
474
475         jsc = new tsColumn("NUM_PREC_RADIX");
476         jsc.type = Types.INTEGER;
477         jsc.size = 8;
478         jrs.addColumn (jsc);
479
480         jsc = new tsColumn("NULLABLE");
481         jsc.type = Types.INTEGER;
482         jsc.size = 8;
483         jrs.addColumn (jsc);
484
485         jsc = new tsColumn("REMARKS");
486         jsc.type = Types.CHAR;
487         jsc.size = 128;
488         jrs.addColumn (jsc);
489
490         jsc = new tsColumn("COLUMN_DEF");
491         jsc.type = Types.CHAR;
492         jsc.size = 128;
493         jrs.addColumn (jsc);
494
495         jsc = new tsColumn("SQL_DATA_TYPE");
496         jsc.type = Types.INTEGER;
497         jsc.size = 128;
498         jrs.addColumn (jsc);
499
500 // Several parameters missing.
501
502         jsc = new tsColumn("IS_NULLABLE");
503         jsc.type = Types.CHAR;
504         jsc.size = 3;
505         jrs.addColumn (jsc);
506
507         while (tableRs.next()) { // process each DBF file and extract column info ...
508
509           String tableName = tableRs.getString("TABLE_NAME");
510
511           dbfFileTable tbl;
512           try {
513             tbl = new dbfFileTable(dataDir, tableName);
514           } catch (Exception e) {
515             continue; // ignore buggy and empty (zero byte size) DBF files
516 }
517
518           Utils.log("Accessing column info for table " + tableName);
519
520           java.util.Hashtable column_info = tbl.column_info;
521           for (java.util.Enumeration e = column_info.elements(); e.hasMoreElements() ;) {
522             tsColumn tsc = (tsColumn)e.nextElement();
523
524             // process each column of the current table ...
525 tsRow record = new tsRow();
526             record.put("TABLE_CAT", "");
527             record.put("TABLE_SCHEM", "");
528             record.put("TABLE_NAME", tableName);
529             record.put("COLUMN_NAME", tsc.name);
530             record.put("DATA_TYPE", new Integer (tsc.type).toString());
531             record.put("TYPE_NAME", dbfFile.typeToLiteral(tsc.type).toString());
532             record.put("COLUMN_SIZE", new Integer (tsc.size).toString());
533             record.put("DECIMAL_DIGITS", new Integer (tsc.decimalPlaces).toString());
534             int nullable = columnNoNulls;
535             if (tsc.notNull == true) nullable = columnNullable;
536             record.put("NULLABLE", new Integer (nullable).toString());
537             record.put("REMARKS", "noRemarks");
538             String defaultVal = tsc.defaultVal;
539             if (defaultVal == null) defaultVal = "";
540             record.put("COLUMN_DEF", defaultVal);
541             String isNullable = "NO";
542             if (tsc.notNull == true) isNullable = "YES";
543             record.put("IS_NULLABLE", isNullable);
544
545             jrs.addRow (record) ;
546           }
547
548           tbl.close();
549           tbl = null;
550         }
551
552         return new tinySQLResultSet(jrs,(tinySQLStatement)null);
553       }
554       catch (Exception e) {
555         return null;
556       }
557     }
558 }
559

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags