001: /*
002: * dbfFileDatabaseMetaData.java
003: */
004:
005: package com.sqlmagic.tinysql;
006:
007: /**
008: * Comprehensive information about the database as a whole.
009: *
010: * Many of the methods here return lists of information in
011: * the form of ResultSet objects.
012: * You can use the normal ResultSet methods such as getString and getInt
013: * to retrieve the data from these ResultSets. If a given form of
014: * metadata is not available, these methods should throw an SQLException.
015: *
016: * Some of these methods take arguments that are String patterns. These
017: * arguments all have names such as fooPattern. Within a pattern String, "%"
018: * means match any substring of 0 or more characters, and "_" means match
019: * any one character. Only metadata entries matching the search pattern
020: * are returned. If a search pattern argument is set to a null ref,
021: * that argument's criteria will be dropped from the search.
022: *
023: * An SQLException will be thrown if a driver does not support a meta
024: * data method. In the case of methods that return a ResultSet,
025: * either a ResultSet (which may be empty) is returned or a
026: * SQLException is thrown.
027: *
028: * $Author: davis $
029: * $Date: 2004/12/18 21:30:57 $
030: * $Revision: 1.1 $
031: *
032: */
033: import java.sql.SQLException;
034: import java.sql.Connection;
035: import java.sql.ResultSet;
036: import java.sql.Types;
037: import java.util.Vector;
038: import java.io.File;
039:
040: /**
041: dBase read/write access <br>
042: @author Brian Jepson <bjepson@home.com>
043: @author Marcel Ruff <ruff@swand.lake.de> Added DatabaseMetaData with JDK 2 support
044: @author Thomas Morgner <mgs@sherito.org> Changed DatabaseMetaData to use java.sql.Types.
045: */
046: public class dbfFileDatabaseMetaData extends tinySQLDatabaseMetaData {
047: private final String emptyString = "";
048:
049: public dbfFileDatabaseMetaData(Connection connection) {
050: super (connection);
051: }
052:
053: public String getDatabaseProductName() {
054: return "tinySQL";
055: }
056:
057: public String getDatabaseProductVersion() {
058: return tinySQLGlobals.VERSION;
059: }
060:
061: String getDataDir() {
062: String url = ((dbfFileConnection) getConnection()).url;
063: if (url.length() <= 13)
064: return null;
065:
066: String dataDir = url.substring(13);
067: return dataDir;
068: }
069:
070: /**
071: * Gets a description of all the standard SQL types supported by
072: * this database. They are ordered by DATA_TYPE and then by how
073: * closely the data type maps to the corresponding JDBC SQL type.
074: *
075: * <P>Each type description has the following columns:
076: * <OL>
077: * <LI><B>TYPE_NAME</B> String => Type name
078: * <LI><B>DATA_TYPE</B> short => SQL data type from java.sql.Types
079: * <LI><B>PRECISION</B> int => maximum precision
080: * <LI><B>LITERAL_PREFIX</B> String => prefix used to quote a literal
081: * (may be null)
082: * <LI><B>LITERAL_SUFFIX</B> String => suffix used to quote a literal
083: (may be null)
084: * <LI><B>CREATE_PARAMS</B> String => parameters used in creating
085: * the type (may be null)
086: * <LI><B>NULLABLE</B> short => can you use NULL for this type?
087: * <UL>
088: * <LI> typeNoNulls - does not allow NULL values
089: * <LI> typeNullable - allows NULL values
090: * <LI> typeNullableUnknown - nullability unknown
091: * </UL>
092: * <LI><B>CASE_SENSITIVE</B> boolean=> is it case sensitive?
093: * <LI><B>SEARCHABLE</B> short => can you use "WHERE" based on this type:
094: * <UL>
095: * <LI> typePredNone - No support
096: * <LI> typePredChar - Only supported with WHERE .. LIKE
097: * <LI> typePredBasic - Supported except for WHERE .. LIKE
098: * <LI> typeSearchable - Supported for all WHERE ..
099: * </UL>
100: * <LI><B>UNSIGNED_ATTRIBUTE</B> boolean => is it unsigned?
101: * <LI><B>FIXED_PREC_SCALE</B> boolean => can it be a money value?
102: * <LI><B>AUTO_INCREMENT</B> boolean => can it be used for an
103: * auto-increment value?
104: * <LI><B>LOCAL_TYPE_NAME</B> String => localized version of type name
105: * (may be null)
106: * <LI><B>MINIMUM_SCALE</B> short => minimum scale supported
107: * <LI><B>MAXIMUM_SCALE</B> short => maximum scale supported
108: * <LI><B>SQL_DATA_TYPE</B> int => unused
109: * <LI><B>SQL_DATETIME_SUB</B> int => unused
110: * <LI><B>NUM_PREC_RADIX</B> int => usually 2 or 10
111: * </OL>
112: *
113: * @return ResultSet - each row is a SQL type description
114: * @exception SQLException if a database access error occurs
115: */
116: public ResultSet getTypeInfo() throws SQLException {
117: tsResultSet jrs = new tsResultSet();
118:
119: tsColumn jsc = new tsColumn("TYPE_NAME");
120: jsc.type = Types.CHAR;
121: jsc.size = 10;
122: jrs.addColumn(jsc);
123:
124: jsc = new tsColumn("DATA_TYPE");
125: jsc.type = Types.INTEGER;
126: jsc.size = 6;
127: jrs.addColumn(jsc);
128:
129: jsc = new tsColumn("PRECISION");
130: jsc.type = Types.INTEGER;
131: jsc.size = 8;
132: jrs.addColumn(jsc);
133:
134: jsc = new tsColumn("LITERAL_PREFIX");
135: jsc.type = Types.CHAR;
136: jsc.size = 1;
137: jrs.addColumn(jsc);
138:
139: jsc = new tsColumn("LITERAL_SUFFIX");
140: jsc.type = Types.CHAR;
141: jsc.size = 1;
142: jrs.addColumn(jsc);
143:
144: jsc = new tsColumn("CREATE_PARAMS");
145: jsc.type = Types.CHAR;
146: jsc.size = 20;
147: jrs.addColumn(jsc);
148:
149: jsc = new tsColumn("NULLABLE");
150: jsc.type = Types.INTEGER;
151: jsc.size = 6;
152: jrs.addColumn(jsc);
153:
154: jsc = new tsColumn("CASE_SENSITIVE");
155: jsc.type = Types.BIT;
156: jsc.size = 1;
157: jrs.addColumn(jsc);
158:
159: jsc = new tsColumn("SEARCHABLE");
160: jsc.type = Types.INTEGER;
161: jsc.size = 6;
162: jrs.addColumn(jsc);
163:
164: /*
165: * <LI><B>UNSIGNED_ATTRIBUTE</B> boolean => is it unsigned?
166: * <LI><B>FIXED_PREC_SCALE</B> boolean => can it be a money value?
167: * <LI><B>AUTO_INCREMENT</B> boolean => can it be used for an
168: * auto-increment value?
169: * <LI><B>LOCAL_TYPE_NAME</B> String => localized version of type name
170: * (may be null)
171: * <LI><B>MINIMUM_SCALE</B> short => minimum scale supported
172: * <LI><B>MAXIMUM_SCALE</B> short => maximum scale supported
173: * <LI><B>NUM_PREC_RADIX</B> int => usually 2 or 10
174: */
175:
176: // NOTE: the Hashtable in tsRow expects always a String as its value!
177: // so i use the toString() method here
178: // Perhaps in future the real type should be pushed into the Hashtable?
179: tsRow record = new tsRow();
180: record.put("TYPE_NAME", dbfFile.typeToLiteral(Types.CHAR)); // "CHAR", String
181: record.put("DATA_TYPE", new Integer(Types.CHAR).toString());
182: record.put("PRECISION", new Integer(254).toString());
183: record.put("LITERAL_PREFIX", "\"");
184: record.put("LITERAL_SUFFIX", "\"");
185: record.put("CREATE_PARAMS", new Integer(0).toString());
186: record.put("NULLABLE", new Integer(typeNullableUnknown)
187: .toString());
188: record.put("CASE_SENSITIVE", "N");
189: record.put("SEARCHABLE", new Integer(typePredBasic).toString());
190: jrs.addRow(record);
191:
192: record = new tsRow();
193: record.put("TYPE_NAME", dbfFile.typeToLiteral(Types.FLOAT)); // "FLOAT", double
194: record.put("DATA_TYPE", new Integer(Types.FLOAT).toString());
195: record.put("PRECISION", new Integer(19).toString());
196: record.put("LITERAL_PREFIX", emptyString);
197: record.put("LITERAL_SUFFIX", emptyString);
198: record.put("CREATE_PARAMS", new Integer(0).toString());
199: record.put("NULLABLE", new Integer(typeNullableUnknown)
200: .toString());
201: record.put("CASE_SENSITIVE", "N");
202: record.put("SEARCHABLE", new Integer(typePredBasic).toString());
203: jrs.addRow(record);
204:
205: record = new tsRow();
206: record.put("TYPE_NAME", dbfFile.typeToLiteral(Types.BIT)); // "CHAR", boolean "YyNnTtFf"
207: record.put("DATA_TYPE", new Integer(Types.BIT).toString());
208: record.put("PRECISION", new Integer(1).toString());
209: record.put("LITERAL_PREFIX", "\"");
210: record.put("LITERAL_SUFFIX", "\"");
211: record.put("CREATE_PARAMS", new Integer(0).toString());
212: record.put("NULLABLE", new Integer(typeNullableUnknown)
213: .toString());
214: record.put("CASE_SENSITIVE", "N");
215: record.put("SEARCHABLE", new Integer(typePredBasic).toString());
216: jrs.addRow(record);
217:
218: record = new tsRow();
219: record.put("TYPE_NAME", dbfFile.typeToLiteral(Types.INTEGER)); // "INT", unsigned long
220: record.put("DATA_TYPE", new Integer(Types.INTEGER).toString());
221: record.put("PRECISION", new Integer(19).toString());
222: record.put("LITERAL_PREFIX", emptyString);
223: record.put("LITERAL_SUFFIX", emptyString);
224: record.put("CREATE_PARAMS", new Integer(0).toString());
225: record.put("NULLABLE", new Integer(typeNullableUnknown)
226: .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)
239: .toString());
240: record.put("CASE_SENSITIVE", "N");
241: record.put("SEARCHABLE", new Integer(typePredBasic).toString());
242: jrs.addRow(record);
243:
244: return new tinySQLResultSet(jrs, (tinySQLStatement) null);
245: }
246:
247: /*
248: * Gets a description of tables available in a catalog.
249: *
250: * Only table descriptions matching the catalog, schema, table
251: * name and type criteria are returned. They are ordered by
252: * TABLE_TYPE, TABLE_SCHEM and TABLE_NAME.
253: *
254: * Each table description has the following columns:
255: *
256: * TABLE_CAT String => table catalog (may be null)
257: * TABLE_SCHEM String => table schema (may be null)
258: * TABLE_NAME String => table name
259: * TABLE_TYPE String => table type. Typical types are "TABLE",
260: * "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY",
261: * "LOCAL TEMPORARY", "ALIAS", "SYNONYM".
262: * REMARKS String => explanatory comment on the table
263: *
264: * Note: Some databases may not return information for
265: * all tables.
266: *
267: * @param catalog a catalog name; "" retrieves those without a
268: * catalog; null means drop catalog name from the selection criteria
269: * THIS VALUE IS IGNORED
270: * @param schemaPattern THIS VALUE IS IGNORED
271: * @param tableNamePattern a table name pattern, īnullī or "%" delivers all
272: * token will be handled as substrings
273: * @param types a list of table types to include; null returns all DBF types
274: * only "TABLE" is supported, others like "VIEW", "SYSTEM TABLE", "SEQUENCE"
275: * are ignored.
276: * @return ResultSet - each row is a table description
277: * @exception SQLException if a database access error occurs
278: * @see #getSearchStringEscape
279: *
280: * @author Thomas Morgner <mgs@sherito.org> Fill all needed columns, or some query tools will crash :(
281: */
282: public ResultSet getTables(String catalog, String schemaPattern,
283: String tableNamePattern, String types[]) {
284: String dataDir = getDataDir();
285: String tableName;
286: File tableFile;
287: tsColumn jsc;
288: int i, dotAt;
289: if (dataDir == null)
290: return null;
291: if (types == null) {
292: types = new String[1];
293: types[0] = "TABLE";
294: }
295: tsResultSet jrs = new tsResultSet();
296: /*
297: * Create the header for the tables ResultSet
298: */
299: try {
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: String type = types[itype];
331: if (type == null)
332: continue;
333: String extension = null;
334: if (type.equalsIgnoreCase("TABLE"))
335: extension = dbfFileTable.dbfExtension; // ".DBF";
336: if (extension == null)
337: continue;
338: Vector vec = Utils.getAllFiles(dataDir, extension);
339: for (i = 0; i < vec.size(); i++) {
340: tableFile = (File) vec.elementAt(i);
341: tableName = tableFile.getName().toUpperCase();
342: dotAt = tableName.indexOf(".");
343: if (dotAt > -1)
344: tableName = tableName.substring(0, dotAt);
345: if (tableNamePattern == null)
346: tableNamePattern = "%";
347: if (tableNamePattern.equals("%")
348: | tableName
349: .equalsIgnoreCase(tableNamePattern)) {
350: if (tableName.length() > jsc.size)
351: jsc.size = tableName.length();
352: tsRow record = new tsRow();
353: record.put("TABLE_NAME", tableName
354: .toUpperCase());
355: record.put("TABLE_TYPE", "TABLE");
356: jrs.addRow(record);
357: }
358: }
359: }
360: } catch (Exception ex) {
361: System.out.println("Unable to create MetaData");
362: ex.printStackTrace(System.out);
363: }
364:
365: // This Resultset is not created by an statement
366: return new tinySQLResultSet(jrs, (tinySQLStatement) null);
367: }
368:
369: /**
370: * Gets a description of table columns available in
371: * the specified catalog.
372: *
373: * <P>Only column descriptions matching the catalog, schema, table
374: * and column name criteria are returned. They are ordered by
375: * TABLE_SCHEM, TABLE_NAME and ORDINAL_POSITION.
376: *
377: * <P>Each column description has the following columns:
378: * <OL>
379: * <LI><B>TABLE_CAT</B> String => table catalog (may be null)
380: * <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
381: * <LI><B>TABLE_NAME</B> String => table name
382: * <LI><B>COLUMN_NAME</B> String => column name
383: * <LI><B>DATA_TYPE</B> short => SQL type from java.sql.Types
384: * <LI><B>TYPE_NAME</B> String => Data source dependent type name,
385: * for a UDT the type name is fully qualified
386: * <LI><B>COLUMN_SIZE</B> int => column size. For char or date
387: * types this is the maximum number of characters, for numeric or
388: * decimal types this is precision.
389: * <LI><B>BUFFER_LENGTH</B> is not used.
390: * <LI><B>DECIMAL_DIGITS</B> int => the number of fractional digits
391: * <LI><B>NUM_PREC_RADIX</B> int => Radix (typically either 10 or 2)
392: * <LI><B>NULLABLE</B> int => is NULL allowed?
393: * <UL>
394: * <LI> columnNoNulls - might not allow NULL values
395: * <LI> columnNullable - definitely allows NULL values
396: * <LI> columnNullableUnknown - nullability unknown
397: * </UL>
398: * <LI><B>REMARKS</B> String => comment describing column (may be null)
399: * <LI><B>COLUMN_DEF</B> String => default value (may be null)
400: * <LI><B>SQL_DATA_TYPE</B> int => unused
401: * <LI><B>SQL_DATETIME_SUB</B> int => unused
402: * <LI><B>CHAR_OCTET_LENGTH</B> int => for char types the
403: * maximum number of bytes in the column
404: * <LI><B>ORDINAL_POSITION</B> int => index of column in table
405: * (starting at 1)
406: * <LI><B>IS_NULLABLE</B> String => "NO" means column definitely
407: * does not allow NULL values; "YES" means the column might
408: * allow NULL values. An empty string means nobody knows.
409: * </OL>
410: *
411: * @param catalog a catalog name; "" retrieves those without a
412: * catalog; null means drop catalog name from the selection criteria
413: * @param schemaPattern a schema name pattern; "" retrieves those
414: * without a schema
415: * @param tableNamePattern a table name pattern
416: * @param columnNamePattern a column name pattern
417: * @return ResultSet - each row is a column description
418: * @exception SQLException if a database access error occurs
419: * @see #getSearchStringEscape
420: */
421: public ResultSet getColumns(String catalog, String schemaPattern,
422: String tableNamePattern, String columnNamePattern) {
423: int i;
424: String columnNameKey;
425: try {
426: String dataDir = getDataDir();
427:
428: Utils.log("Entering getColumns(tableNamePattern='"
429: + tableNamePattern + "')");
430:
431: if (dataDir == null)
432: return null;
433:
434: ResultSet tableRs = getTables(catalog, schemaPattern,
435: tableNamePattern, null);
436:
437: tsResultSet jrs = new tsResultSet();
438:
439: tsColumn jsc = new tsColumn("TABLE_CAT");
440: jsc.type = Types.CHAR;
441: jsc.size = 9;
442: jrs.addColumn(jsc);
443:
444: jsc = new tsColumn("TABLE_SCHEM");
445: jsc.type = Types.CHAR;
446: jsc.size = 11;
447: jrs.addColumn(jsc);
448:
449: jsc = new tsColumn("TABLE_NAME");
450: jsc.type = Types.CHAR;
451: jsc.size = 10;
452: jrs.addColumn(jsc);
453:
454: jsc = new tsColumn("COLUMN_NAME");
455: jsc.type = Types.CHAR;
456: jsc.size = 11;
457: jrs.addColumn(jsc);
458:
459: jsc = new tsColumn("DATA_TYPE");
460: jsc.type = Types.INTEGER;
461: jsc.size = 6;
462: jrs.addColumn(jsc);
463:
464: jsc = new tsColumn("TYPE_NAME");
465: jsc.type = Types.CHAR;
466: jsc.size = 9;
467: jrs.addColumn(jsc);
468:
469: jsc = new tsColumn("COLUMN_SIZE");
470: jsc.type = Types.INTEGER;
471: jsc.size = 8;
472: jrs.addColumn(jsc);
473:
474: jsc = new tsColumn("BUFFER_LENGTH");
475: jsc.type = Types.INTEGER;
476: jsc.size = 8;
477: jrs.addColumn(jsc);
478:
479: jsc = new tsColumn("DECIMAL_DIGITS");
480: jsc.type = Types.INTEGER;
481: jsc.size = 8;
482: jrs.addColumn(jsc);
483:
484: jsc = new tsColumn("NUM_PREC_RADIX");
485: jsc.type = Types.INTEGER;
486: jsc.size = 8;
487: jrs.addColumn(jsc);
488:
489: jsc = new tsColumn("NULLABLE");
490: jsc.type = Types.INTEGER;
491: jsc.size = 8;
492: jrs.addColumn(jsc);
493:
494: jsc = new tsColumn("REMARKS");
495: jsc.type = Types.CHAR;
496: jsc.size = 128;
497: jrs.addColumn(jsc);
498:
499: jsc = new tsColumn("COLUMN_DEF");
500: jsc.type = Types.CHAR;
501: jsc.size = 128;
502: jrs.addColumn(jsc);
503:
504: jsc = new tsColumn("SQL_DATA_TYPE");
505: jsc.type = Types.INTEGER;
506: jsc.size = 128;
507: jrs.addColumn(jsc);
508:
509: // Several parameters missing.
510:
511: jsc = new tsColumn("IS_NULLABLE");
512: jsc.type = Types.CHAR;
513: jsc.size = 3;
514: jrs.addColumn(jsc);
515:
516: while (tableRs.next()) { // process each DBF file and extract column info ...
517:
518: String tableName = tableRs.getString("TABLE_NAME");
519:
520: dbfFileTable tbl;
521: try {
522: tbl = new dbfFileTable(dataDir, tableName);
523: } catch (Exception e) {
524: continue; // ignore buggy and empty (zero byte size) DBF files
525: }
526:
527: Utils.log("Accessing column info for table "
528: + tableName);
529:
530: java.util.Hashtable column_info = tbl.column_info;
531: for (i = 0; i < tbl.columnNameKeys.size(); i++) {
532: columnNameKey = (String) tbl.columnNameKeys
533: .elementAt(i);
534: tsColumn tsc = (tsColumn) column_info
535: .get(columnNameKey);
536: // process each column of the current table ...
537: tsRow record = new tsRow();
538: record.put("TABLE_CAT", "");
539: record.put("TABLE_SCHEM", "");
540: record.put("TABLE_NAME", tableName);
541: record.put("COLUMN_NAME", tinySQLGlobals
542: .getLongName(tsc.name));
543: record.put("DATA_TYPE", new Integer(tsc.type)
544: .toString());
545: record.put("TYPE_NAME", dbfFile.typeToLiteral(
546: tsc.type).toString());
547: record.put("COLUMN_SIZE", new Integer(tsc.size)
548: .toString());
549: record.put("DECIMAL_DIGITS", new Integer(
550: tsc.decimalPlaces).toString());
551: int nullable = columnNoNulls;
552: if (tsc.notNull == true)
553: nullable = columnNullable;
554: record.put("NULLABLE", new Integer(nullable)
555: .toString());
556: record.put("REMARKS", "noRemarks");
557: String defaultVal = tsc.defaultVal;
558: if (defaultVal == null)
559: defaultVal = "";
560: record.put("COLUMN_DEF", defaultVal);
561: String isNullable = "NO";
562: if (tsc.notNull == true)
563: isNullable = "YES";
564: record.put("IS_NULLABLE", isNullable);
565: /*
566: * Suppress any sorting of the ResultSet. Column Metadata should
567: * be presented in the order the columns exist in the dBase header.
568: */
569:
570: jrs.addRow(record, false);
571: }
572:
573: tbl.close();
574: tbl = null;
575: }
576:
577: return new tinySQLResultSet(jrs, (tinySQLStatement) null);
578: } catch (Exception e) {
579: return null;
580: }
581: }
582: }
|