org.sqldroid.SQLDroidDatabaseMetaData Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of SQLDroid Show documentation
Show all versions of SQLDroid Show documentation
SQLDroid with some performance improvements
The newest version!
package org.sqldroid;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.RowIdLifetime;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import android.database.Cursor;
import android.database.MatrixCursor;
import android.database.MergeCursor;
public class SQLDroidDatabaseMetaData implements DatabaseMetaData {
private static final int SQLITE_DONE = 101;
private static final String VIEW_TYPE = "VIEW";
private static final String TABLE_TYPE = "TABLE";
private PreparedStatement
getTableTypes = null, getCatalogs = null,
getUDTs = null, getSuperTypes = null,
getTablePrivileges = null, getProcedures = null,
getProcedureColumns = null, getAttributes = null,
getBestRowIdentifier = null, getVersionColumns = null,
getColumnPrivileges = null;
SQLDroidConnection con;
public SQLDroidDatabaseMetaData(SQLDroidConnection con) {
this.con = con;
}
@Override
public boolean allProceduresAreCallable() throws SQLException {
return false;
}
@Override
public boolean allTablesAreSelectable() throws SQLException {
return true;
}
@Override
public boolean dataDefinitionCausesTransactionCommit() throws SQLException {
return false;
}
@Override
public boolean dataDefinitionIgnoredInTransactions() throws SQLException {
return false;
}
@Override
public boolean deletesAreDetected(int type) throws SQLException {
return false;
}
@Override
public boolean doesMaxRowSizeIncludeBlobs() throws SQLException {
return false;
}
@Override
public ResultSet getAttributes(String catalog, String schemaPattern,
String typeNamePattern, String attributeNamePattern)
throws SQLException {
if (getAttributes == null) {
getAttributes = con.prepareStatement("select null as TYPE_CAT, null as TYPE_SCHEM, " +
"null as TYPE_NAME, null as ATTR_NAME, null as DATA_TYPE, " +
"null as ATTR_TYPE_NAME, null as ATTR_SIZE, null as DECIMAL_DIGITS, " +
"null as NUM_PREC_RADIX, null as NULLABLE, null as REMARKS, null as ATTR_DEF, " +
"null as SQL_DATA_TYPE, null as SQL_DATETIME_SUB, null as CHAR_OCTET_LENGTH, " +
"null as ORDINAL_POSITION, null as IS_NULLABLE, null as SCOPE_CATALOG, " +
"null as SCOPE_SCHEMA, null as SCOPE_TABLE, null as SOURCE_DATA_TYPE limit 0;");
}
return getAttributes.executeQuery();
}
@Override
public ResultSet getBestRowIdentifier(String catalog, String schema,
String table, int scope, boolean nullable) throws SQLException {
if (getBestRowIdentifier == null) {
getBestRowIdentifier = con.prepareStatement("select null as SCOPE, null as COLUMN_NAME, " +
"null as DATA_TYPE, null as TYPE_NAME, null as COLUMN_SIZE, " +
"null as BUFFER_LENGTH, null as DECIMAL_DIGITS, null as PSEUDO_COLUMN limit 0;");
}
return getBestRowIdentifier.executeQuery();
}
@Override
public String getCatalogSeparator() throws SQLException {
return ".";
}
@Override
public String getCatalogTerm() {
return "catalog";
}
@Override
public ResultSet getCatalogs() throws SQLException {
if (getCatalogs == null) {
getCatalogs = con.prepareStatement("select null as TABLE_CAT limit 0;");
}
return getCatalogs.executeQuery();
}
@Override
public ResultSet getColumnPrivileges(String c, String s, String t, String colPat) throws SQLException {
if (getColumnPrivileges == null) {
getColumnPrivileges = con.prepareStatement("select null as TABLE_CAT, null as TABLE_SCHEM, " +
"null as TABLE_NAME, null as COLUMN_NAME, null as GRANTOR, null as GRANTEE, " +
"null as PRIVILEGE, null as IS_GRANTABLE limit 0;");
}
return getColumnPrivileges.executeQuery();
}
@Override
public ResultSet getColumns(String catalog, String schemaPattern,
String tableNamePattern, String columnNamePattern) throws SQLException {
// get the list of tables matching the pattern (getTables)
// create a Matrix Cursor for each of the tables
// create a merge cursor from all the Matrix Cursors
// and return the columname and type from:
// "PRAGMA table_info(tablename)"
// which returns data like this:
// sqlite> PRAGMA lastyear.table_info(gross_sales);
// cid|name|type|notnull|dflt_value|pk
// 0|year|INTEGER|0|'2006'|0
// 1|month|TEXT|0||0
// 2|monthlygross|REAL|0||0
// 3|sortcol|INTEGER|0||0
// sqlite>
// and then make the cursor have these columns
// TABLE_CAT String => table catalog (may be null)
// TABLE_SCHEM String => table schema (may be null)
// TABLE_NAME String => table name
// COLUMN_NAME String => column name
// DATA_TYPE int => SQL type from java.sql.Types
// TYPE_NAME String => Data source dependent type name, for a UDT the type name is fully qualified
// COLUMN_SIZE int => column size.
// BUFFER_LENGTH is not used.
// DECIMAL_DIGITS int => the number of fractional digits. Null is returned for data types where DECIMAL_DIGITS is not applicable.
// NUM_PREC_RADIX int => Radix (typically either 10 or 2)
// NULLABLE int => is NULL allowed.
// columnNoNulls - might not allow NULL values
// columnNullable - definitely allows NULL values
// columnNullableUnknown - nullability unknown
// REMARKS String => comment describing column (may be null)
// COLUMN_DEF String => default value for the column, which should be interpreted as a string when the value is enclosed in single quotes (may be null)
// SQL_DATA_TYPE int => unused
// SQL_DATETIME_SUB int => unused
// CHAR_OCTET_LENGTH int => for char types the maximum number of bytes in the column
// ORDINAL_POSITION int => index of column in table (starting at 1)
// IS_NULLABLE String => ISO rules are used to determine the nullability for a column.
// YES --- if the parameter can include NULLs
// NO --- if the parameter cannot include NULLs
// empty string --- if the nullability for the parameter is unknown
// SCOPE_CATLOG String => catalog of table that is the scope of a reference attribute (null if DATA_TYPE isn't REF)
// SCOPE_SCHEMA String => schema of table that is the scope of a reference attribute (null if the DATA_TYPE isn't REF)
// SCOPE_TABLE String => table name that this the scope of a reference attribure (null if the DATA_TYPE isn't REF)
// SOURCE_DATA_TYPE short => source type of a distinct type or user-generated Ref type, SQL type from java.sql.Types (null if DATA_TYPE isn't DISTINCT or user-generated REF)
// IS_AUTOINCREMENT String => Indicates whether this column is auto incremented
// YES --- if the column is auto incremented
// NO --- if the column is not auto incremented
// empty string --- if it cannot be determined whether the column is auto incremented parameter is unknown
final String[] columnNames = new String [] {"TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME", "COLUMN_NAME",
"DATA_TYPE", "TYPE_NAME", "COLUMN_SIZE", "BUFFER_LENGTH", "DECIMAL_DIGITS", "NUM_PREC_RADIX",
"NULLABLE", "REMARKS","COLUMN_DEF", "SQL_DATA_TYPE", "SQL_DATETIME_SUB", "CHAR_OCTET_LENGTH",
"ORDINAL_POSITION", "IS_NULLABLE", "SCOPE_CATLOG", "SCOPE_SCHEMA", "SCOPE_TABLE", "SOURCE_DATA_TYPE",
"IS_AUTOINCREMENT"};
final Object[] columnValues = new Object[] {null, null, null, null, null, null, null, null, null, Integer.valueOf(10),
Integer.valueOf(2) /* columnNullableUnknown */, null, null, null, null, Integer.valueOf(-1), Integer.valueOf(-1), "",
null, null, null, null, ""};
SQLiteDatabase db = con.getDb();
final String[] types = new String[] {TABLE_TYPE, VIEW_TYPE};
ResultSet rs = null;
List cursorList = new ArrayList();
try {
rs = getTables(catalog, schemaPattern, tableNamePattern, types);
while ( rs.next() ) {
Cursor c = null;
try {
String tableName = rs.getString(3);
String pragmaStatement = "PRAGMA table_info('"+ tableName + "')"; // ?)"; substitutions don't seem to work in a pragma statment...
c = db.rawQuery(pragmaStatement, new String[] {});
MatrixCursor mc = new MatrixCursor (columnNames,c.getCount());
while (c.moveToNext() ) {
Object[] column = columnValues.clone();
column[2] = tableName;
column[3] = c.getString(1);
String type = c.getString(2);
column[5] = type;
type = type.toUpperCase();
// types are (as far as I can tell, the pragma document is not specific):
if ( type.equals("TEXT" ) || type.startsWith("CHAR") ) {
column[4] = java.sql.Types.VARCHAR;
}
else if ( type.equals("NUMERIC") ) {
column[4] = java.sql.Types.NUMERIC;
}
else if ( type.startsWith("INT") ) {
column[4] = java.sql.Types.INTEGER;
}
else if ( type.equals("REAL") ) {
column[4] = java.sql.Types.REAL;
}
else if ( type.equals("BLOB") ) {
column[4] = java.sql.Types.BLOB;
}
else { // manufactured columns, eg select 100 as something from tablename, may not have a type.
column[4] = java.sql.Types.NULL;
}
int nullable = c.getInt(3);
//public static final int columnNoNulls 0
//public static final int columnNullable 1
//public static final int columnNullableUnknown 2
if ( nullable == 0 ) {
column[10] = Integer.valueOf(1);
} else if ( nullable == 1 ) {
column[10] = Integer.valueOf(0);
}
column[12] = c.getString(4); // we should check the type for this, but I'm not going to.
mc.addRow(column);
}
cursorList.add(mc);
} catch (SQLException e) {
// failure of one query will no affect the others...
// this will already have been printed. e.printStackTrace();
} finally {
if ( c != null ) {
c.close();
}
}
}
} finally {
if ( rs != null ) {
try {
rs.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
SQLDroidResultSet resultSet;
Cursor[] cursors = new Cursor[cursorList.size()];
cursors = cursorList.toArray(cursors);
if ( cursors.length == 0 ) {
resultSet = new SQLDroidResultSet(new MatrixCursor(columnNames,0));
} else if ( cursors.length == 1 ) {
resultSet = new SQLDroidResultSet(cursors[0]);
} else {
resultSet = new SQLDroidResultSet(new MergeCursor( cursors ));
}
return resultSet;
}
@Override
public Connection getConnection() throws SQLException {
return con;
}
@Override
public ResultSet getCrossReference(String pc, String ps, String pt, String fc, String fs, String ft) throws SQLException {
if (pt == null) {
return getExportedKeys(fc, fs, ft);
}
if (ft == null) {
return getImportedKeys(pc, ps, pt);
}
StringBuilder query = new StringBuilder();
query.append("select ").append(quote(pc)).append(" as PKTABLE_CAT, ")
.append(quote(ps)).append(" as PKTABLE_SCHEM, ").append(quote(pt)).append(" as PKTABLE_NAME, ")
.append("'' as PKCOLUMN_NAME, ").append(quote(fc)).append(" as FKTABLE_CAT, ")
.append(quote(fs)).append(" as FKTABLE_SCHEM, ").append(quote(ft)).append(" as FKTABLE_NAME, ")
.append("'' as FKCOLUMN_NAME, -1 as KEY_SEQ, 3 as UPDATE_RULE, 3 as DELETE_RULE, '' as FK_NAME, '' as PK_NAME, ")
.append(Integer.toString(importedKeyInitiallyDeferred)).append(" as DEFERRABILITY limit 0 ");
return con.createStatement().executeQuery(query.toString());
}
@Override
public int getDatabaseMajorVersion() throws SQLException {
return con.getDb().getSqliteDatabase().getVersion();
}
@Override
public int getDatabaseMinorVersion() throws SQLException {
return 0;
}
@Override
public String getDatabaseProductName() throws SQLException {
return "SQLite for Android";
}
@Override
public String getDatabaseProductVersion() throws SQLException {
return "";
}
@Override
public int getDefaultTransactionIsolation() throws SQLException {
return Connection.TRANSACTION_SERIALIZABLE;
}
@Override
public int getDriverMajorVersion() {
return 1;
}
@Override
public int getDriverMinorVersion() {
return 1;
}
@Override
public String getDriverName() throws SQLException {
return "SQLDroid";
}
@Override
public String getDriverVersion() throws SQLException {
return "0.0.1 alpha";
}
private final static Map RULE_MAP = new HashMap();
static {
RULE_MAP.put("NO ACTION", importedKeyNoAction);
RULE_MAP.put("CASCADE", importedKeyCascade);
RULE_MAP.put("RESTRICT", importedKeyRestrict);
RULE_MAP.put("SET NULL", importedKeySetNull);
RULE_MAP.put("SET DEFAULT", importedKeySetDefault);
}
/**
* Pattern used to extract a named primary key.
*/
protected final static Pattern FK_NAMED_PATTERN =
Pattern.compile(".* constraint +(.*?) +foreign +key *\\((.*?)\\).*", Pattern.CASE_INSENSITIVE);
/**
* Pattern used to extract column order for an unnamed primary key.
*/
protected final static Pattern PK_UNNAMED_PATTERN =
Pattern.compile(".* primary +key *\\((.*?,+.*?)\\).*", Pattern.CASE_INSENSITIVE);
/**
* Pattern used to extract a named primary key.
*/
protected final static Pattern PK_NAMED_PATTERN =
Pattern.compile(".* constraint +(.*?) +primary +key *\\((.*?)\\).*", Pattern.CASE_INSENSITIVE);
@Override
public ResultSet getExportedKeys(String catalog, String schema, String table)
throws SQLException {
PrimaryKeyFinder pkFinder = new PrimaryKeyFinder(table);
String[] pkColumns = pkFinder.getColumns();
Statement stat = con.createStatement();
catalog = (catalog != null) ? quote(catalog) : null;
schema = (schema != null) ? quote(schema) : null;
StringBuilder exportedKeysQuery = new StringBuilder(512);
int count = 0;
if (pkColumns != null) {
// retrieve table list
ResultSet rs = stat.executeQuery("select name from sqlite_master where type = 'table'");
ArrayList tableList = new ArrayList();
while (rs.next()) {
tableList.add(rs.getString(1));
}
rs.close();
ResultSet fk = null;
String target = table.toLowerCase();
// find imported keys for each table
for (String tbl : tableList) {
try {
fk = stat.executeQuery("pragma foreign_key_list('" + escape(tbl) + "')");
} catch (SQLException e) {
if (e.getErrorCode() == SQLITE_DONE)
continue; // expected if table has no foreign keys
throw e;
}
Statement stat2 = null;
try {
stat2 = con.createStatement();
while(fk.next()) {
int keySeq = fk.getInt(2) + 1;
String PKTabName = fk.getString(3).toLowerCase();
if (PKTabName == null || !PKTabName.equals(target)) {
continue;
}
String PKColName = fk.getString(5);
PKColName = (PKColName == null) ? pkColumns[0] : PKColName.toLowerCase();
exportedKeysQuery
.append(count > 0 ? " union all select " : "select ")
.append(Integer.toString(keySeq)).append(" as ks, lower('")
.append(escape(tbl)).append("') as fkt, lower('")
.append(escape(fk.getString(4))).append("') as fcn, '")
.append(escape(PKColName)).append("' as pcn, ")
.append(RULE_MAP.get(fk.getString(6))).append(" as ur, ")
.append(RULE_MAP.get(fk.getString(7))).append(" as dr, ");
rs = stat2.executeQuery("select sql from sqlite_master where" +
" lower(name) = lower('" + escape(tbl) + "')");
if (rs.next()) {
Matcher matcher = FK_NAMED_PATTERN.matcher(rs.getString(1));
if (matcher.find()){
exportedKeysQuery.append("'").append(escape(matcher.group(1).toLowerCase())).append("' as fkn");
}
else {
exportedKeysQuery.append("'' as fkn");
}
}
rs.close();
count++;
}
}
finally {
try{
if (rs != null) rs.close();
}catch(SQLException e) {}
try{
if (stat2 != null) stat2.close();
}catch(SQLException e) {}
try{
if (fk != null) fk.close();
}catch(SQLException e) {}
}
}
}
boolean hasImportedKey = (count > 0);
StringBuilder sql = new StringBuilder(512);
sql.append("select ")
.append(catalog).append(" as PKTABLE_CAT, ")
.append(schema).append(" as PKTABLE_SCHEM, ")
.append(quote(table)).append(" as PKTABLE_NAME, ")
.append(hasImportedKey ? "pcn" : "''").append(" as PKCOLUMN_NAME, ")
.append(catalog).append(" as FKTABLE_CAT, ")
.append(schema).append(" as FKTABLE_SCHEM, ")
.append(hasImportedKey ? "fkt" : "''").append(" as FKTABLE_NAME, ")
.append(hasImportedKey ? "fcn" : "''").append(" as FKCOLUMN_NAME, ")
.append(hasImportedKey ? "ks" : "-1").append(" as KEY_SEQ, ")
.append(hasImportedKey ? "ur" : "3").append(" as UPDATE_RULE, ")
.append(hasImportedKey ? "dr" : "3").append(" as DELETE_RULE, ")
.append(hasImportedKey ? "fkn" : "''").append(" as FK_NAME, ")
.append(pkFinder.getName() != null ? pkFinder.getName() : "''").append(" as PK_NAME, ")
.append(Integer.toString(importedKeyInitiallyDeferred)) // FIXME: Check for pragma foreign_keys = true ?
.append(" as DEFERRABILITY ");
if (hasImportedKey) {
sql.append("from (").append(exportedKeysQuery).append(") order by fkt");
}
else {
sql.append("limit 0");
}
return stat.executeQuery(sql.toString());
}
@Override
public String getExtraNameCharacters() throws SQLException {
return "";
}
@Override
public String getIdentifierQuoteString() throws SQLException {
return " ";
}
@Override
public ResultSet getImportedKeys(String catalog, String schema, String table)
throws SQLException {
ResultSet rs = null;
Statement stat = con.createStatement();
StringBuilder sql = new StringBuilder(700);
sql.append("select ").append(quote(catalog)).append(" as PKTABLE_CAT, ")
.append(quote(schema)).append(" as PKTABLE_SCHEM, ")
.append("ptn as PKTABLE_NAME, pcn as PKCOLUMN_NAME, ")
.append(quote(catalog)).append(" as FKTABLE_CAT, ")
.append(quote(schema)).append(" as FKTABLE_SCHEM, ")
.append(quote(table)).append(" as FKTABLE_NAME, ")
.append("fcn as FKCOLUMN_NAME, ks as KEY_SEQ, ur as UPDATE_RULE, dr as DELETE_RULE, '' as FK_NAME, '' as PK_NAME, ")
.append(Integer.toString(importedKeyInitiallyDeferred)).append(" as DEFERRABILITY from (");
// Use a try catch block to avoid "query does not return ResultSet" error
try {
rs = stat.executeQuery("pragma foreign_key_list('" + escape(table) + "');");
}
catch (SQLException e) {
sql.append("select -1 as ks, '' as ptn, '' as fcn, '' as pcn, ")
.append(importedKeyNoAction).append(" as ur, ")
.append(importedKeyNoAction).append(" as dr) limit 0;");
return stat.executeQuery(sql.toString());
}
boolean rsHasNext = false;
for (int i = 0; rs.next(); i++) {
rsHasNext = true;
int keySeq = rs.getInt(2) + 1;
String PKTabName = rs.getString(3);
String FKColName = rs.getString(4);
String PKColName = rs.getString(5);
if (PKColName == null) {
PKColName = new PrimaryKeyFinder(PKTabName).getColumns()[0];
}
String updateRule = rs.getString(6);
String deleteRule = rs.getString(7);
if (i > 0) {
sql.append(" union all ");
}
sql.append("select ").append(keySeq).append(" as ks,")
.append("'").append(escape(PKTabName)).append("' as ptn, '")
.append(escape(FKColName)).append("' as fcn, '")
.append(escape(PKColName)).append("' as pcn,")
.append("case '").append(escape(updateRule)).append("'")
.append(" when 'NO ACTION' then ").append(importedKeyNoAction)
.append(" when 'CASCADE' then ").append(importedKeyCascade)
.append(" when 'RESTRICT' then ").append(importedKeyRestrict)
.append(" when 'SET NULL' then ").append(importedKeySetNull)
.append(" when 'SET DEFAULT' then ").append(importedKeySetDefault).append(" end as ur, ")
.append("case '").append(escape(deleteRule)).append("'")
.append(" when 'NO ACTION' then ").append(importedKeyNoAction)
.append(" when 'CASCADE' then ").append(importedKeyCascade)
.append(" when 'RESTRICT' then ").append(importedKeyRestrict)
.append(" when 'SET NULL' then ").append(importedKeySetNull)
.append(" when 'SET DEFAULT' then ").append(importedKeySetDefault).append(" end as dr");
}
rs.close();
if(!rsHasNext){
sql.append("select -1 as ks, '' as ptn, '' as fcn, '' as pcn, ")
.append(importedKeyNoAction).append(" as ur, ")
.append(importedKeyNoAction).append(" as dr) limit 0;");
}
return stat.executeQuery(sql.append(");").toString());
}
@Override
public ResultSet getIndexInfo(String catalog, String schema, String table,
boolean unique, boolean approximate) throws SQLException {
ResultSet rs = null;
Statement stat = con.createStatement();
StringBuilder sql = new StringBuilder(500);
sql.append("select null as TABLE_CAT, null as TABLE_SCHEM, '")
.append(escape(table)).append("' as TABLE_NAME, un as NON_UNIQUE, null as INDEX_QUALIFIER, n as INDEX_NAME, ")
.append(Integer.toString(tableIndexOther)).append(" as TYPE, op as ORDINAL_POSITION, ")
.append("cn as COLUMN_NAME, null as ASC_OR_DESC, 0 as CARDINALITY, 0 as PAGES, null as FILTER_CONDITION from (");
// Use a try catch block to avoid "query does not return ResultSet" error
try {
rs = stat.executeQuery("pragma index_list('" + escape(table) + "');");
}
catch (SQLException e) {
sql.append("select null as un, null as n, null as op, null as cn) limit 0;");
return stat.executeQuery(sql.toString());
}
ArrayList> indexList = new ArrayList>();
while (rs.next()) {
indexList.add(new ArrayList
© 2015 - 2025 Weber Informatics LLC | Privacy Policy