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