1 /*
2  * Copyright (c) 2007 David Crawshaw <david@zentus.com>
3  *
4  * Permission to use, copy, modify, and/or distribute this software for any
5  * purpose with or without fee is hereby granted, provided that the above
6  * copyright notice and this permission notice appear in all copies.
7  *
8  * THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES
9  * WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF
10  * MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR
11  * ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES
12  * WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN
13  * ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF
14  * OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.
15  */
16 
17 package org.sqlite;
18 
19 import java.sql.*;
20 
21 class MetaData implements DatabaseMetaData
22 {
23     private Conn conn;
24     private PreparedStatement
25         getTables = null,
26         getTableTypes = null,
27         getTypeInfo = null,
28         getCrossReference = null,
29         getCatalogs = null,
30         getSchemas = null,
31         getUDTs = null,
32         getColumnsTblName = null,
33         getSuperTypes = null,
34         getSuperTables = null,
35         getTablePrivileges = null,
36         getExportedKeys = null,
37         getProcedures = null,
38         getProcedureColumns = null,
39         getAttributes = null,
40         getBestRowIdentifier = null,
41         getVersionColumns = null,
42         getColumnPrivileges = null;
43 
44     /** Used by PrepStmt to save generating a new statement every call. */
45     private PreparedStatement getGeneratedKeys = null;
46 
MetaData(Conn conn)47     MetaData(Conn conn) { this.conn = conn; }
48 
checkOpen()49     void checkOpen() throws SQLException {
50         if (conn == null) throw new SQLException("connection closed"); }
51 
close()52     synchronized void close() throws SQLException {
53         if (conn == null) return;
54 
55         try {
56             if (getTables != null) getTables.close();
57             if (getTableTypes != null) getTableTypes.close();
58             if (getTypeInfo != null) getTypeInfo.close();
59             if (getCrossReference != null) getCrossReference.close();
60             if (getCatalogs != null) getCatalogs.close();
61             if (getSchemas != null) getSchemas.close();
62             if (getUDTs != null) getUDTs.close();
63             if (getColumnsTblName != null) getColumnsTblName.close();
64             if (getSuperTypes != null) getSuperTypes.close();
65             if (getSuperTables != null) getSuperTables.close();
66             if (getTablePrivileges != null) getTablePrivileges.close();
67             if (getExportedKeys != null) getExportedKeys.close();
68             if (getProcedures != null) getProcedures.close();
69             if (getProcedureColumns != null) getProcedureColumns.close();
70             if (getAttributes != null) getAttributes.close();
71             if (getBestRowIdentifier != null) getBestRowIdentifier.close();
72             if (getVersionColumns != null) getVersionColumns.close();
73             if (getColumnPrivileges != null) getColumnPrivileges.close();
74             if (getGeneratedKeys != null) getGeneratedKeys.close();
75 
76             getTables = null;
77             getTableTypes = null;
78             getTypeInfo = null;
79             getCrossReference = null;
80             getCatalogs = null;
81             getSchemas = null;
82             getUDTs = null;
83             getColumnsTblName = null;
84             getSuperTypes = null;
85             getSuperTables = null;
86             getTablePrivileges = null;
87             getExportedKeys = null;
88             getProcedures = null;
89             getProcedureColumns = null;
90             getAttributes = null;
91             getBestRowIdentifier = null;
92             getVersionColumns = null;
93             getColumnPrivileges = null;
94             getGeneratedKeys = null;
95         } finally {
96             conn = null;
97         }
98     }
99 
getConnection()100     public Connection getConnection() { return conn; }
getDatabaseMajorVersion()101     public int getDatabaseMajorVersion() { return 3; }
getDatabaseMinorVersion()102     public int getDatabaseMinorVersion() { return 0; }
getDriverMajorVersion()103     public int getDriverMajorVersion() { return 1; }
getDriverMinorVersion()104     public int getDriverMinorVersion() { return 1; }
getJDBCMajorVersion()105     public int getJDBCMajorVersion() { return 2; }
getJDBCMinorVersion()106     public int getJDBCMinorVersion() { return 1; }
getDefaultTransactionIsolation()107     public int getDefaultTransactionIsolation()
108         { return Connection.TRANSACTION_SERIALIZABLE; }
getMaxBinaryLiteralLength()109     public int getMaxBinaryLiteralLength() { return 0; }
getMaxCatalogNameLength()110     public int getMaxCatalogNameLength() { return 0; }
getMaxCharLiteralLength()111     public int getMaxCharLiteralLength() { return 0; }
getMaxColumnNameLength()112     public int getMaxColumnNameLength() { return 0; }
getMaxColumnsInGroupBy()113     public int getMaxColumnsInGroupBy() { return 0; }
getMaxColumnsInIndex()114     public int getMaxColumnsInIndex() { return 0; }
getMaxColumnsInOrderBy()115     public int getMaxColumnsInOrderBy() { return 0; }
getMaxColumnsInSelect()116     public int getMaxColumnsInSelect() { return 0; }
getMaxColumnsInTable()117     public int getMaxColumnsInTable() { return 0; }
getMaxConnections()118     public int getMaxConnections() { return 0; }
getMaxCursorNameLength()119     public int getMaxCursorNameLength() { return 0; }
getMaxIndexLength()120     public int getMaxIndexLength() { return 0; }
getMaxProcedureNameLength()121     public int getMaxProcedureNameLength() { return 0; }
getMaxRowSize()122     public int getMaxRowSize() { return 0; }
getMaxSchemaNameLength()123     public int getMaxSchemaNameLength() { return 0; }
getMaxStatementLength()124     public int getMaxStatementLength() { return 0; }
getMaxStatements()125     public int getMaxStatements() { return 0; }
getMaxTableNameLength()126     public int getMaxTableNameLength() { return 0; }
getMaxTablesInSelect()127     public int getMaxTablesInSelect() { return 0; }
getMaxUserNameLength()128     public int getMaxUserNameLength() { return 0; }
getResultSetHoldability()129     public int getResultSetHoldability()
130         { return ResultSet.CLOSE_CURSORS_AT_COMMIT; }
getSQLStateType()131     public int getSQLStateType() { return sqlStateSQL99; }
132 
getDatabaseProductName()133     public String getDatabaseProductName() { return "SQLite"; }
getDatabaseProductVersion()134     public String getDatabaseProductVersion() throws SQLException {
135         return conn.libversion();
136     }
getDriverName()137     public String getDriverName() { return "SQLiteJDBC"; }
getDriverVersion()138     public String getDriverVersion() { return conn.getDriverVersion(); }
getExtraNameCharacters()139     public String getExtraNameCharacters() { return ""; }
getCatalogSeparator()140     public String getCatalogSeparator() { return "."; }
getCatalogTerm()141     public String getCatalogTerm() { return "catalog"; }
getSchemaTerm()142     public String getSchemaTerm() { return "schema"; }
getProcedureTerm()143     public String getProcedureTerm() { return "not_implemented"; }
getSearchStringEscape()144     public String getSearchStringEscape() { return null; }
getIdentifierQuoteString()145     public String getIdentifierQuoteString() { return " "; }
getSQLKeywords()146     public String getSQLKeywords() { return ""; }
getNumericFunctions()147     public String getNumericFunctions() { return ""; }
getStringFunctions()148     public String getStringFunctions() { return ""; }
getSystemFunctions()149     public String getSystemFunctions() { return ""; }
getTimeDateFunctions()150     public String getTimeDateFunctions() { return ""; }
151 
getURL()152     public String getURL() { return conn.url(); }
getUserName()153     public String getUserName() { return null; }
154 
allProceduresAreCallable()155     public boolean allProceduresAreCallable() { return false; }
allTablesAreSelectable()156     public boolean allTablesAreSelectable() { return true; }
dataDefinitionCausesTransactionCommit()157     public boolean dataDefinitionCausesTransactionCommit() { return false; }
dataDefinitionIgnoredInTransactions()158     public boolean dataDefinitionIgnoredInTransactions() { return false; }
doesMaxRowSizeIncludeBlobs()159     public boolean doesMaxRowSizeIncludeBlobs() { return false; }
deletesAreDetected(int type)160     public boolean deletesAreDetected(int type) { return false; }
insertsAreDetected(int type)161     public boolean insertsAreDetected(int type) { return false; }
isCatalogAtStart()162     public boolean isCatalogAtStart() { return true; }
locatorsUpdateCopy()163     public boolean locatorsUpdateCopy() { return false; }
nullPlusNonNullIsNull()164     public boolean nullPlusNonNullIsNull() { return true; }
nullsAreSortedAtEnd()165     public boolean nullsAreSortedAtEnd() { return !nullsAreSortedAtStart(); }
nullsAreSortedAtStart()166     public boolean nullsAreSortedAtStart() { return true; }
nullsAreSortedHigh()167     public boolean nullsAreSortedHigh() { return true; }
nullsAreSortedLow()168     public boolean nullsAreSortedLow() { return !nullsAreSortedHigh(); }
othersDeletesAreVisible(int type)169     public boolean othersDeletesAreVisible(int type) { return false; }
othersInsertsAreVisible(int type)170     public boolean othersInsertsAreVisible(int type) { return false; }
othersUpdatesAreVisible(int type)171     public boolean othersUpdatesAreVisible(int type) { return false; }
ownDeletesAreVisible(int type)172     public boolean ownDeletesAreVisible(int type) { return false; }
ownInsertsAreVisible(int type)173     public boolean ownInsertsAreVisible(int type) { return false; }
ownUpdatesAreVisible(int type)174     public boolean ownUpdatesAreVisible(int type) { return false; }
storesLowerCaseIdentifiers()175     public boolean storesLowerCaseIdentifiers() { return false; }
storesLowerCaseQuotedIdentifiers()176     public boolean storesLowerCaseQuotedIdentifiers() { return false; }
storesMixedCaseIdentifiers()177     public boolean storesMixedCaseIdentifiers() { return true; }
storesMixedCaseQuotedIdentifiers()178     public boolean storesMixedCaseQuotedIdentifiers() { return false; }
storesUpperCaseIdentifiers()179     public boolean storesUpperCaseIdentifiers() { return false; }
storesUpperCaseQuotedIdentifiers()180     public boolean storesUpperCaseQuotedIdentifiers() { return false; }
supportsAlterTableWithAddColumn()181     public boolean supportsAlterTableWithAddColumn() { return false; }
supportsAlterTableWithDropColumn()182     public boolean supportsAlterTableWithDropColumn() { return false; }
supportsANSI92EntryLevelSQL()183     public boolean supportsANSI92EntryLevelSQL() { return false; }
supportsANSI92FullSQL()184     public boolean supportsANSI92FullSQL() { return false; }
supportsANSI92IntermediateSQL()185     public boolean supportsANSI92IntermediateSQL() { return false; }
supportsBatchUpdates()186     public boolean supportsBatchUpdates() { return true; }
supportsCatalogsInDataManipulation()187     public boolean supportsCatalogsInDataManipulation() { return false; }
supportsCatalogsInIndexDefinitions()188     public boolean supportsCatalogsInIndexDefinitions() { return false; }
supportsCatalogsInPrivilegeDefinitions()189     public boolean supportsCatalogsInPrivilegeDefinitions() { return false; }
supportsCatalogsInProcedureCalls()190     public boolean supportsCatalogsInProcedureCalls() { return false; }
supportsCatalogsInTableDefinitions()191     public boolean supportsCatalogsInTableDefinitions() { return false; }
supportsColumnAliasing()192     public boolean supportsColumnAliasing() { return true; }
supportsConvert()193     public boolean supportsConvert() { return false; }
supportsConvert(int fromType, int toType)194     public boolean supportsConvert(int fromType, int toType) { return false; }
supportsCorrelatedSubqueries()195     public boolean supportsCorrelatedSubqueries() { return false; }
supportsDataDefinitionAndDataManipulationTransactions()196     public boolean supportsDataDefinitionAndDataManipulationTransactions()
197         { return true; }
supportsDataManipulationTransactionsOnly()198     public boolean supportsDataManipulationTransactionsOnly() { return false; }
supportsDifferentTableCorrelationNames()199     public boolean supportsDifferentTableCorrelationNames() { return false; }
supportsExpressionsInOrderBy()200     public boolean supportsExpressionsInOrderBy() { return true; }
supportsMinimumSQLGrammar()201     public boolean supportsMinimumSQLGrammar() { return true; }
supportsCoreSQLGrammar()202     public boolean supportsCoreSQLGrammar() { return true; }
supportsExtendedSQLGrammar()203     public boolean supportsExtendedSQLGrammar() { return false; }
supportsLimitedOuterJoins()204     public boolean supportsLimitedOuterJoins() { return true; }
supportsFullOuterJoins()205     public boolean supportsFullOuterJoins() { return false; }
supportsGetGeneratedKeys()206     public boolean supportsGetGeneratedKeys() { return false; }
supportsGroupBy()207     public boolean supportsGroupBy() { return true; }
supportsGroupByBeyondSelect()208     public boolean supportsGroupByBeyondSelect() { return false; }
supportsGroupByUnrelated()209     public boolean supportsGroupByUnrelated() { return false; }
supportsIntegrityEnhancementFacility()210     public boolean supportsIntegrityEnhancementFacility() { return false; }
supportsLikeEscapeClause()211     public boolean supportsLikeEscapeClause() { return false; }
supportsMixedCaseIdentifiers()212     public boolean supportsMixedCaseIdentifiers() { return true; }
supportsMixedCaseQuotedIdentifiers()213     public boolean supportsMixedCaseQuotedIdentifiers() { return false; }
supportsMultipleOpenResults()214     public boolean supportsMultipleOpenResults() { return false; }
supportsMultipleResultSets()215     public boolean supportsMultipleResultSets() { return false; }
supportsMultipleTransactions()216     public boolean supportsMultipleTransactions() { return true; }
supportsNamedParameters()217     public boolean supportsNamedParameters() { return true; }
supportsNonNullableColumns()218     public boolean supportsNonNullableColumns() { return true; }
supportsOpenCursorsAcrossCommit()219     public boolean supportsOpenCursorsAcrossCommit() { return false; }
supportsOpenCursorsAcrossRollback()220     public boolean supportsOpenCursorsAcrossRollback() { return false; }
supportsOpenStatementsAcrossCommit()221     public boolean supportsOpenStatementsAcrossCommit() { return false; }
supportsOpenStatementsAcrossRollback()222     public boolean supportsOpenStatementsAcrossRollback() { return false; }
supportsOrderByUnrelated()223     public boolean supportsOrderByUnrelated() { return false; }
supportsOuterJoins()224     public boolean supportsOuterJoins() { return true; }
supportsPositionedDelete()225     public boolean supportsPositionedDelete() { return false; }
supportsPositionedUpdate()226     public boolean supportsPositionedUpdate() { return false; }
supportsResultSetConcurrency(int t, int c)227     public boolean supportsResultSetConcurrency(int t, int c)
228         { return t == ResultSet.TYPE_FORWARD_ONLY
229               && c == ResultSet.CONCUR_READ_ONLY; }
supportsResultSetHoldability(int h)230     public boolean supportsResultSetHoldability(int h)
231         { return h == ResultSet.CLOSE_CURSORS_AT_COMMIT; }
supportsResultSetType(int t)232     public boolean supportsResultSetType(int t)
233         { return t == ResultSet.TYPE_FORWARD_ONLY; }
supportsSavepoints()234     public boolean supportsSavepoints() { return false; }
supportsSchemasInDataManipulation()235     public boolean supportsSchemasInDataManipulation() { return false; }
supportsSchemasInIndexDefinitions()236     public boolean supportsSchemasInIndexDefinitions() { return false; }
supportsSchemasInPrivilegeDefinitions()237     public boolean supportsSchemasInPrivilegeDefinitions() { return false; }
supportsSchemasInProcedureCalls()238     public boolean supportsSchemasInProcedureCalls() { return false; }
supportsSchemasInTableDefinitions()239     public boolean supportsSchemasInTableDefinitions() { return false; }
supportsSelectForUpdate()240     public boolean supportsSelectForUpdate() { return false; }
supportsStatementPooling()241     public boolean supportsStatementPooling() { return false; }
supportsStoredProcedures()242     public boolean supportsStoredProcedures() { return false; }
supportsSubqueriesInComparisons()243     public boolean supportsSubqueriesInComparisons() { return false; }
supportsSubqueriesInExists()244     public boolean supportsSubqueriesInExists() { return true; } // TODO: check
supportsSubqueriesInIns()245     public boolean supportsSubqueriesInIns() { return true; } // TODO: check
supportsSubqueriesInQuantifieds()246     public boolean supportsSubqueriesInQuantifieds() { return false; }
supportsTableCorrelationNames()247     public boolean supportsTableCorrelationNames() { return false; }
supportsTransactionIsolationLevel(int level)248     public boolean supportsTransactionIsolationLevel(int level)
249         { return level == Connection.TRANSACTION_SERIALIZABLE; }
supportsTransactions()250     public boolean supportsTransactions() { return true; }
supportsUnion()251     public boolean supportsUnion() { return true; }
supportsUnionAll()252     public boolean supportsUnionAll() { return true; }
updatesAreDetected(int type)253     public boolean updatesAreDetected(int type) { return false; }
usesLocalFilePerTable()254     public boolean usesLocalFilePerTable() { return false; }
usesLocalFiles()255     public boolean usesLocalFiles() { return true; }
isReadOnly()256     public boolean isReadOnly() throws SQLException
257         { return conn.isReadOnly(); }
258 
getAttributes(String c, String s, String t, String a)259     public ResultSet getAttributes(String c, String s, String t, String a)
260             throws SQLException {
261         if (getAttributes == null) getAttributes = conn.prepareStatement(
262             "select "
263             + "null as TYPE_CAT, "
264             + "null as TYPE_SCHEM, "
265             + "null as TYPE_NAME, "
266             + "null as ATTR_NAME, "
267             + "null as DATA_TYPE, "
268             + "null as ATTR_TYPE_NAME, "
269             + "null as ATTR_SIZE, "
270             + "null as DECIMAL_DIGITS, "
271             + "null as NUM_PREC_RADIX, "
272             + "null as NULLABLE, "
273             + "null as REMARKS, "
274             + "null as ATTR_DEF, "
275             + "null as SQL_DATA_TYPE, "
276             + "null as SQL_DATETIME_SUB, "
277             + "null as CHAR_OCTET_LENGTH, "
278             + "null as ORDINAL_POSITION, "
279             + "null as IS_NULLABLE, "
280             + "null as SCOPE_CATALOG, "
281             + "null as SCOPE_SCHEMA, "
282             + "null as SCOPE_TABLE, "
283             + "null as SOURCE_DATA_TYPE limit 0;");
284         return getAttributes.executeQuery();
285     }
286 
getBestRowIdentifier(String c, String s, String t, int scope, boolean n)287     public ResultSet getBestRowIdentifier(String c, String s, String t,
288             int scope, boolean n) throws SQLException {
289         if (getBestRowIdentifier == null)
290             getBestRowIdentifier = conn.prepareStatement(
291             "select "
292             + "null as SCOPE, "
293             + "null as COLUMN_NAME, "
294             + "null as DATA_TYPE, "
295             + "null as TYPE_NAME, "
296             + "null as COLUMN_SIZE, "
297             + "null as BUFFER_LENGTH, "
298             + "null as DECIMAL_DIGITS, "
299             + "null as PSEUDO_COLUMN limit 0;");
300         return getBestRowIdentifier.executeQuery();
301     }
302 
getColumnPrivileges(String c, String s, String t, String colPat)303     public ResultSet getColumnPrivileges(String c, String s, String t,
304                                          String colPat)
305             throws SQLException {
306         if (getColumnPrivileges == null)
307             getColumnPrivileges = conn.prepareStatement(
308             "select "
309             + "null as TABLE_CAT, "
310             + "null as TABLE_SCHEM, "
311             + "null as TABLE_NAME, "
312             + "null as COLUMN_NAME, "
313             + "null as GRANTOR, "
314             + "null as GRANTEE, "
315             + "null as PRIVILEGE, "
316             + "null as IS_GRANTABLE limit 0;");
317         return getColumnPrivileges.executeQuery();
318     }
319 
getColumns(String c, String s, String tbl, String colPat)320     public ResultSet getColumns(String c, String s, String tbl, String colPat)
321             throws SQLException {
322         Statement stat = conn.createStatement();
323         ResultSet rs;
324         String sql;
325 
326         checkOpen();
327 
328         if (getColumnsTblName == null)
329             getColumnsTblName = conn.prepareStatement(
330                 "select tbl_name from sqlite_master where tbl_name like ?;");
331 
332         // determine exact table name
333         getColumnsTblName.setString(1, tbl);
334         rs = getColumnsTblName.executeQuery();
335         if (!rs.next())
336             return rs;
337         tbl = rs.getString(1);
338         rs.close();
339 
340         sql = "select "
341             + "null as TABLE_CAT, "
342             + "null as TABLE_SCHEM, "
343             + "'" + escape(tbl) + "' as TABLE_NAME, "
344             + "cn as COLUMN_NAME, "
345             + "ct as DATA_TYPE, "
346             + "tn as TYPE_NAME, "
347             + "2000000000 as COLUMN_SIZE, "
348             + "2000000000 as BUFFER_LENGTH, "
349             + "10   as DECIMAL_DIGITS, "
350             + "10   as NUM_PREC_RADIX, "
351             + "colnullable as NULLABLE, "
352             + "null as REMARKS, "
353             + "null as COLUMN_DEF, "
354             + "0    as SQL_DATA_TYPE, "
355             + "0    as SQL_DATETIME_SUB, "
356             + "2000000000 as CHAR_OCTET_LENGTH, "
357             + "ordpos as ORDINAL_POSITION, "
358             + "(case colnullable when 0 then 'N' when 1 then 'Y' else '' end)"
359             + "    as IS_NULLABLE, "
360             + "null as SCOPE_CATLOG, "
361             + "null as SCOPE_SCHEMA, "
362             + "null as SCOPE_TABLE, "
363             + "null as SOURCE_DATA_TYPE from (";
364 
365         // the command "pragma table_info('tablename')" does not embed
366         // like a normal select statement so we must extract the information
367         // and then build a resultset from unioned select statements
368         rs = stat.executeQuery("pragma table_info ('"+escape(tbl)+"');");
369 
370         boolean colFound = false;
371         for (int i=0; rs.next(); i++) {
372             String colName = rs.getString(2);
373             String colType = rs.getString(3);
374             String colNotNull = rs.getString(4);
375 
376             int colNullable = 2;
377             if (colNotNull != null) colNullable = colNotNull.equals("0") ? 1:0;
378             if (colFound) sql += " union all ";
379             colFound = true;
380 
381             colType = colType == null ? "TEXT" : colType.toUpperCase();
382             int colJavaType = -1;
383             if (colType == "INT" || colType == "INTEGER")
384                 colJavaType = Types.INTEGER;
385             else if (colType == "TEXT")
386                 colJavaType = Types.VARCHAR;
387             else if (colType == "FLOAT")
388                 colJavaType = Types.FLOAT;
389             else
390                 colJavaType = Types.VARCHAR;
391 
392             sql += "select "
393                 + i + " as ordpos, "
394                 + colNullable + " as colnullable, '"
395                 + colJavaType + "' as ct, '"
396                 + escape(colName) + "' as cn, '"
397                 + escape(colType) + "' as tn";
398 
399             if (colPat != null)
400                 sql += " where upper(cn) like upper('" + escape(colPat) + "')";
401         }
402         sql += colFound ? ");" :
403             "select null as ordpos, null as colnullable, "
404             + "null as cn, null as tn) limit 0;";
405         rs.close();
406 
407         return stat.executeQuery(sql);
408     }
409 
getCrossReference(String pc, String ps, String pt, String fc, String fs, String ft)410     public ResultSet getCrossReference(String pc, String ps, String pt,
411                                        String fc, String fs, String ft)
412             throws SQLException {
413         if (getCrossReference == null)
414             getCrossReference = conn.prepareStatement("select "
415                 + "null as PKTABLE_CAT, "
416                 + "null as PKTABLE_SCHEM, "
417                 + "null as PKTABLE_NAME, "
418                 + "null as PKCOLUMN_NAME, "
419                 + "null as FKTABLE_CAT, "
420                 + "null as FKTABLE_SCHEM, "
421                 + "null as FKTABLE_NAME, "
422                 + "null as FKCOLUMN_NAME, "
423                 + "null as KEY_SEQ, "
424                 + "null as UPDATE_RULE, "
425                 + "null as DELETE_RULE, "
426                 + "null as FK_NAME, "
427                 + "null as PK_NAME, "
428                 + "null as DEFERRABILITY "
429                 + "limit 0;");
430         getCrossReference.clearParameters();
431         return getCrossReference.executeQuery();
432     }
433 
getSchemas()434     public ResultSet getSchemas() throws SQLException {
435         if (getSchemas == null) getSchemas = conn.prepareStatement("select "
436                 + "null as TABLE_SCHEM, "
437                 + "null as TABLE_CATALOG "
438                 + "limit 0;");
439         getSchemas.clearParameters();
440         return getSchemas.executeQuery();
441     }
442 
getCatalogs()443     public ResultSet getCatalogs() throws SQLException {
444         if (getCatalogs == null) getCatalogs = conn.prepareStatement(
445                 "select null as TABLE_CAT limit 0;");
446         getCatalogs.clearParameters();
447         return getCatalogs.executeQuery();
448     }
449 
getPrimaryKeys(String c, String s, String table)450     public ResultSet getPrimaryKeys(String c, String s, String table)
451             throws SQLException {
452         String sql;
453         ResultSet rs;
454         Statement stat = conn.createStatement();
455 
456         rs = stat.executeQuery("pragma table_info('"+escape(table)+"');");
457 
458         sql = "select "
459             + "null as TABLE_CAT, "
460             + "null as TABLE_SCHEM, "
461             + "'" + escape(table) + "' as TABLE_NAME, "
462             + "cn as COLUMN_NAME, "
463             + "0 as KEY_SEQ, "
464             + "null as PK_NAME from (";
465 
466         int i;
467         for (i=0; rs.next(); i++) {
468             String colName = rs.getString(2);
469 
470             if (!rs.getBoolean(6)) { i--; continue; }
471             if (i > 0) sql += " union all ";
472 
473             sql += "select '" + escape(colName) + "' as cn";
474         }
475         sql += i == 0 ? "select null as cn) limit 0;" : ");";
476         rs.close();
477 
478         return stat.executeQuery(sql);
479     }
480 
getExportedKeys(String c, String s, String t)481     public ResultSet getExportedKeys(String c, String s, String t)
482             throws SQLException {
483         if (getExportedKeys == null) getExportedKeys = conn.prepareStatement(
484                 "select "
485                 + "null as PKTABLE_CAT, "
486                 + "null as PKTABLE_SCHEM, "
487                 + "null as PKTABLE_NAME, "
488                 + "null as PKCOLUMN_NAME, "
489                 + "null as FKTABLE_CAT, "
490                 + "null as FKTABLE_SCHEM, "
491                 + "null as FKTABLE_NAME, "
492                 + "null as FKCOLUMN_NAME, "
493                 + "null as KEY_SEQ, "
494                 + "null as UPDATE_RULE, "
495                 + "null as DELETE_RULE, "
496                 + "null as FK_NAME, "
497                 + "null as PK_NAME, "
498                 + "null as DEFERRABILITY limit 0;");
499         return getExportedKeys.executeQuery();
500     }
501 
getImportedKeys(String c, String s, String t)502     public ResultSet getImportedKeys(String c, String s, String t)
503         throws SQLException { throw new SQLException("not yet implemented"); }
getIndexInfo(String c, String s, String t, boolean u, boolean approximate)504     public ResultSet getIndexInfo(String c, String s, String t,
505                                   boolean u, boolean approximate)
506         throws SQLException { throw new SQLException("not yet implemented"); }
getProcedureColumns(String c, String s, String p, String colPat)507     public ResultSet getProcedureColumns(String c, String s, String p,
508                                          String colPat)
509             throws SQLException {
510         if (getProcedures == null) getProcedureColumns = conn.prepareStatement(
511             "select "
512             + "null as PROCEDURE_CAT, "
513             + "null as PROCEDURE_SCHEM, "
514             + "null as PROCEDURE_NAME, "
515             + "null as COLUMN_NAME, "
516             + "null as COLUMN_TYPE, "
517             + "null as DATA_TYPE, "
518             + "null as TYPE_NAME, "
519             + "null as PRECISION, "
520             + "null as LENGTH, "
521             + "null as SCALE, "
522             + "null as RADIX, "
523             + "null as NULLABLE, "
524             + "null as REMARKS limit 0;");
525         return getProcedureColumns.executeQuery();
526 
527     }
528 
getProcedures(String c, String s, String p)529     public ResultSet getProcedures(String c, String s, String p)
530             throws SQLException {
531         if (getProcedures == null) getProcedures = conn.prepareStatement(
532             "select "
533             + "null as PROCEDURE_CAT, "
534             + "null as PROCEDURE_SCHEM, "
535             + "null as PROCEDURE_NAME, "
536             + "null as UNDEF1, "
537             + "null as UNDEF2, "
538             + "null as UNDEF3, "
539             + "null as REMARKS, "
540             + "null as PROCEDURE_TYPE limit 0;");
541         return getProcedures.executeQuery();
542     }
543 
getSuperTables(String c, String s, String t)544     public ResultSet getSuperTables(String c, String s, String t)
545             throws SQLException {
546         if (getSuperTables == null) getSuperTables = conn.prepareStatement(
547             "select "
548             + "null as TABLE_CAT, "
549             + "null as TABLE_SCHEM, "
550             + "null as TABLE_NAME, "
551             + "null as SUPERTABLE_NAME limit 0;");
552         return getSuperTables.executeQuery();
553     }
554 
getSuperTypes(String c, String s, String t)555     public ResultSet getSuperTypes(String c, String s, String t)
556             throws SQLException {
557         if (getSuperTypes == null) getSuperTypes = conn.prepareStatement(
558             "select "
559             + "null as TYPE_CAT, "
560             + "null as TYPE_SCHEM, "
561             + "null as TYPE_NAME, "
562             + "null as SUPERTYPE_CAT, "
563             + "null as SUPERTYPE_SCHEM, "
564             + "null as SUPERTYPE_NAME limit 0;");
565         return getSuperTypes.executeQuery();
566     }
567 
getTablePrivileges(String c, String s, String t)568     public ResultSet getTablePrivileges(String c, String s, String t)
569             throws SQLException {
570         if (getTablePrivileges == null)
571             getTablePrivileges = conn.prepareStatement(
572             "select "
573             + "null as TABLE_CAT, "
574             + "null as TABLE_SCHEM, "
575             + "null as TABLE_NAME, "
576             + "null as GRANTOR, "
577             + "null as GRANTEE, "
578             + "null as PRIVILEGE, "
579             + "null as IS_GRANTABLE limit 0;");
580         return getTablePrivileges.executeQuery();
581     }
582 
getTables(String c, String s, String t, String[] types)583     public synchronized ResultSet getTables(String c, String s,
584             String t, String[] types) throws SQLException {
585         checkOpen();
586 
587         t = (t == null || "".equals(t)) ? "%" : t.toUpperCase();
588 
589         String sql = "select"
590                 + " null as TABLE_CAT,"
591                 + " null as TABLE_SCHEM,"
592                 + " upper(name) as TABLE_NAME,"
593                 + " upper(type) as TABLE_TYPE,"
594                 + " null as REMARKS,"
595                 + " null as TYPE_CAT,"
596                 + " null as TYPE_SCHEM,"
597                 + " null as TYPE_NAME,"
598                 + " null as SELF_REFERENCING_COL_NAME,"
599                 + " null as REF_GENERATION"
600                 + " from (select name, type from sqlite_master union all"
601                 + "       select name, type from sqlite_temp_master)"
602                 + " where TABLE_NAME like '" + escape(t) + "'";
603 
604         if (types != null) {
605             sql += " and TABLE_TYPE in (";
606             for (int i=0; i < types.length; i++) {
607                 if (i > 0) sql += ", ";
608                 sql += "'" + types[i].toUpperCase() + "'";
609             }
610             sql += ")";
611         }
612 
613         sql += ";";
614 
615         return conn.createStatement().executeQuery(sql);
616     }
617 
getTableTypes()618     public ResultSet getTableTypes() throws SQLException {
619         checkOpen();
620         if (getTableTypes == null) getTableTypes = conn.prepareStatement(
621                 "select 'TABLE' as TABLE_TYPE"
622                 + " union select 'VIEW' as TABLE_TYPE;");
623         getTableTypes.clearParameters();
624         return getTableTypes.executeQuery();
625     }
626 
getTypeInfo()627     public ResultSet getTypeInfo() throws SQLException {
628         if (getTypeInfo == null) {
629             getTypeInfo = conn.prepareStatement(
630                   "select "
631                 + "tn as TYPE_NAME, "
632                 + "dt as DATA_TYPE, "
633                 + "0 as PRECISION, "
634                 + "null as LITERAL_PREFIX, "
635                 + "null as LITERAL_SUFFIX, "
636                 + "null as CREATE_PARAMS, "
637                 + typeNullable + " as NULLABLE, "
638                 + "1 as CASE_SENSITIVE, "
639                 + typeSearchable + " as SEARCHABLE, "
640                 + "0 as UNSIGNED_ATTRIBUTE, "
641                 + "0 as FIXED_PREC_SCALE, "
642                 + "0 as AUTO_INCREMENT, "
643                 + "null as LOCAL_TYPE_NAME, "
644                 + "0 as MINIMUM_SCALE, "
645                 + "0 as MAXIMUM_SCALE, "
646                 + "0 as SQL_DATA_TYPE, "
647                 + "0 as SQL_DATETIME_SUB, "
648                 + "10 as NUM_PREC_RADIX from ("
649                 + "    select 'BLOB' as tn, " + Types.BLOB + " as dt union"
650                 + "    select 'NULL' as tn, " + Types.NULL + " as dt union"
651                 + "    select 'REAL' as tn, " + Types.REAL+ " as dt union"
652                 + "    select 'TEXT' as tn, " + Types.VARCHAR + " as dt union"
653                 + "    select 'INTEGER' as tn, "+ Types.INTEGER +" as dt"
654                 + ") order by TYPE_NAME;"
655             );
656         }
657 
658         getTypeInfo.clearParameters();
659         return getTypeInfo.executeQuery();
660     }
661 
getUDTs(String c, String s, String t, int[] types)662     public ResultSet getUDTs(String c, String s, String t, int[] types)
663             throws SQLException {
664         if (getUDTs == null) getUDTs = conn.prepareStatement("select "
665                 + "null as TYPE_CAT, "
666                 + "null as TYPE_SCHEM, "
667                 + "null as TYPE_NAME, "
668                 + "null as CLASS_NAME, "
669                 + "null as DATA_TYPE, "
670                 + "null as REMARKS, "
671                 + "null as BASE_TYPE "
672                 + "limit 0;");
673 
674         getUDTs.clearParameters();
675         return getUDTs.executeQuery();
676     }
getVersionColumns(String c, String s, String t)677     public ResultSet getVersionColumns(String c, String s, String t)
678             throws SQLException {
679         if (getVersionColumns == null)
680             getVersionColumns = conn.prepareStatement(
681             "select "
682             + "null as SCOPE, "
683             + "null as COLUMN_NAME, "
684             + "null as DATA_TYPE, "
685             + "null as TYPE_NAME, "
686             + "null as COLUMN_SIZE, "
687             + "null as BUFFER_LENGTH, "
688             + "null as DECIMAL_DIGITS, "
689             + "null as PSEUDO_COLUMN limit 0;");
690         return getVersionColumns.executeQuery();
691     }
692 
getGeneratedKeys()693     ResultSet getGeneratedKeys() throws SQLException {
694         if (getGeneratedKeys == null) getGeneratedKeys = conn.prepareStatement(
695             "select last_insert_rowid();");
696         return getGeneratedKeys.executeQuery();
697     }
698 
699     /** Replace all instances of ' with '' */
escape(final String val)700     private String escape(final String val) {
701         // TODO: this function is ugly, pass this work off to SQLite, then we
702         //       don't have to worry about Unicode 4, other characters needing
703         //       escaping, etc.
704         int len = val.length();
705         StringBuffer buf = new StringBuffer(len);
706         for (int i=0; i < len; i++) {
707             if (val.charAt(i) == '\'') buf.append('\'');
708             buf.append(val.charAt(i));
709         }
710         return buf.toString();
711     }
712 
createStruct(String t, Object[] attr)713     public Struct createStruct(String t, Object[] attr) throws SQLException {
714         throw new SQLException("Not yet implemented by SQLite JDBC driver"); }
getFunctionColumns(String a, String b, String c, String d)715     public ResultSet getFunctionColumns(String a, String b, String c,
716                 String d) throws SQLException {
717         throw new SQLException("Not yet implemented by SQLite JDBC driver"); }
718 }
719