LibsqlDatabaseMetaData.java

package com.github.magnusp.libsql.jdbc;

import com.github.magnusp.libsql.hrana.codec.Col;
import com.github.magnusp.libsql.hrana.codec.Row;
import com.github.magnusp.libsql.hrana.codec.Stmt;
import com.github.magnusp.libsql.hrana.codec.StmtResult;
import com.github.magnusp.libsql.hrana.codec.Value;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.RowIdLifetime;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.Locale;
import java.util.regex.Pattern;

public class LibsqlDatabaseMetaData implements DatabaseMetaData {

    private static final Pattern INTEGER_TYPE = Pattern.compile(".*(INT|BOOL).*");
    private static final Pattern VARCHAR_TYPE = Pattern.compile(".*(CHAR|CLOB|TEXT|BLOB).*");
    private static final Pattern FLOAT_TYPE = Pattern.compile(".*(REAL|FLOA|DOUB|DEC|NUM).*");
    private static final int UNKNOWN_COLUMN_SIZE = 2_000_000_000;
    private static final int DEFAULT_DECIMAL_DIGITS = 10;

    private final LibsqlConnection connection;
    private String cachedDatabaseProductVersion;

    public LibsqlDatabaseMetaData(LibsqlConnection connection) {
        this.connection = connection;
    }

    private String resolveDatabaseProductVersion() throws SQLException {
        if (cachedDatabaseProductVersion == null) {
            try (var rs = connection.createStatement().executeQuery("SELECT sqlite_version()")) {
                cachedDatabaseProductVersion = rs.next() ? rs.getString(1) : "3.0";
            } catch (SQLException e) {
                cachedDatabaseProductVersion = "3.0";
            }
        }
        return cachedDatabaseProductVersion;
    }

    @Override
    public boolean allProceduresAreCallable() throws SQLException { return false; }
    @Override
    public boolean allTablesAreSelectable() throws SQLException { return true; }
    @Override
    public String getURL() throws SQLException {
        return connection.getConfig() != null ? connection.getConfig().url() : null;
    }
    @Override
    public String getUserName() throws SQLException { return null; }
    @Override
    public boolean isReadOnly() throws SQLException { return connection.isReadOnly(); }
    @Override
    public boolean nullsAreSortedHigh() throws SQLException { return true; }
    @Override
    public boolean nullsAreSortedLow() throws SQLException { return false; }
    @Override
    public boolean nullsAreSortedAtStart() throws SQLException { return false; }
    @Override
    public boolean nullsAreSortedAtEnd() throws SQLException { return false; }
    @Override
    public String getDatabaseProductName() throws SQLException { return "SQLite (libSQL)"; }
    @Override
    public String getDatabaseProductVersion() throws SQLException { return resolveDatabaseProductVersion(); }
    @Override
    public String getDriverName() throws SQLException { return "libSQL JDBC Driver"; }
    @Override
    public String getDriverVersion() throws SQLException { return "0.1.0"; }
    @Override
    public int getDriverMajorVersion() { return 0; }
    @Override
    public int getDriverMinorVersion() { return 1; }
    @Override
    public boolean usesLocalFiles() throws SQLException { return false; }
    @Override
    public boolean usesLocalFilePerTable() throws SQLException { return false; }
    @Override
    public boolean supportsMixedCaseIdentifiers() throws SQLException { return false; }
    @Override
    public boolean storesUpperCaseIdentifiers() throws SQLException { return false; }
    @Override
    public boolean storesLowerCaseIdentifiers() throws SQLException { return false; }
    @Override
    public boolean storesMixedCaseIdentifiers() throws SQLException { return true; }
    @Override
    public boolean supportsMixedCaseQuotedIdentifiers() throws SQLException { return false; }
    @Override
    public boolean storesUpperCaseQuotedIdentifiers() throws SQLException { return false; }
    @Override
    public boolean storesLowerCaseQuotedIdentifiers() throws SQLException { return false; }
    @Override
    public boolean storesMixedCaseQuotedIdentifiers() throws SQLException { return true; }
    @Override
    public String getIdentifierQuoteString() throws SQLException { return "\""; }
    @Override
    public String getSQLKeywords() throws SQLException { return ""; }
    @Override
    public String getNumericFunctions() throws SQLException { return ""; }
    @Override
    public String getStringFunctions() throws SQLException { return ""; }
    @Override
    public String getSystemFunctions() throws SQLException { return ""; }
    @Override
    public String getTimeDateFunctions() throws SQLException { return ""; }
    @Override
    public String getSearchStringEscape() throws SQLException { return "\\"; }
    @Override
    public String getExtraNameCharacters() throws SQLException { return ""; }
    @Override
    public boolean supportsAlterTableWithAddColumn() throws SQLException { return true; }
    @Override
    public boolean supportsAlterTableWithDropColumn() throws SQLException { return true; }
    @Override
    public boolean supportsColumnAliasing() throws SQLException { return true; }
    @Override
    public boolean nullPlusNonNullIsNull() throws SQLException { return true; }
    @Override
    public boolean supportsConvert() throws SQLException { return false; }
    @Override
    public boolean supportsConvert(int fromType, int toType) throws SQLException { return false; }
    @Override
    public boolean supportsTableCorrelationNames() throws SQLException { return true; }
    @Override
    public boolean supportsDifferentTableCorrelationNames() throws SQLException { return false; }
    @Override
    public boolean supportsExpressionsInOrderBy() throws SQLException { return true; }
    @Override
    public boolean supportsOrderByUnrelated() throws SQLException { return true; }
    @Override
    public boolean supportsGroupBy() throws SQLException { return true; }
    @Override
    public boolean supportsGroupByUnrelated() throws SQLException { return true; }
    @Override
    public boolean supportsGroupByBeyondSelect() throws SQLException { return true; }
    @Override
    public boolean supportsLikeEscapeClause() throws SQLException { return true; }
    @Override
    public boolean supportsMultipleResultSets() throws SQLException { return false; }
    @Override
    public boolean supportsMultipleTransactions() throws SQLException { return true; }
    @Override
    public boolean supportsNonNullableColumns() throws SQLException { return true; }
    @Override
    public boolean supportsMinimumSQLGrammar() throws SQLException { return true; }
    @Override
    public boolean supportsCoreSQLGrammar() throws SQLException { return true; }
    @Override
    public boolean supportsExtendedSQLGrammar() throws SQLException { return false; }
    @Override
    public boolean supportsANSI92EntryLevelSQL() throws SQLException { return true; }
    @Override
    public boolean supportsANSI92IntermediateSQL() throws SQLException { return false; }
    @Override
    public boolean supportsANSI92FullSQL() throws SQLException { return false; }
    @Override
    public boolean supportsIntegrityEnhancementFacility() throws SQLException { return false; }
    @Override
    public boolean supportsOuterJoins() throws SQLException { return true; }
    @Override
    public boolean supportsFullOuterJoins() throws SQLException { return false; }
    @Override
    public boolean supportsLimitedOuterJoins() throws SQLException { return true; }
    @Override
    public String getSchemaTerm() throws SQLException { return "schema"; }
    @Override
    public String getProcedureTerm() throws SQLException { return "procedure"; }
    @Override
    public String getCatalogTerm() throws SQLException { return "catalog"; }
    @Override
    public boolean isCatalogAtStart() throws SQLException { return true; }
    @Override
    public String getCatalogSeparator() throws SQLException { return "."; }
    @Override
    public boolean supportsSchemasInDataManipulation() throws SQLException { return false; }
    @Override
    public boolean supportsSchemasInProcedureCalls() throws SQLException { return false; }
    @Override
    public boolean supportsSchemasInTableDefinitions() throws SQLException { return false; }
    @Override
    public boolean supportsSchemasInIndexDefinitions() throws SQLException { return false; }
    @Override
    public boolean supportsSchemasInPrivilegeDefinitions() throws SQLException { return false; }
    @Override
    public boolean supportsCatalogsInDataManipulation() throws SQLException { return false; }
    @Override
    public boolean supportsCatalogsInProcedureCalls() throws SQLException { return false; }
    @Override
    public boolean supportsCatalogsInTableDefinitions() throws SQLException { return false; }
    @Override
    public boolean supportsCatalogsInIndexDefinitions() throws SQLException { return false; }
    @Override
    public boolean supportsCatalogsInPrivilegeDefinitions() throws SQLException { return false; }
    @Override
    public boolean supportsPositionedDelete() throws SQLException { return false; }
    @Override
    public boolean supportsPositionedUpdate() throws SQLException { return false; }
    @Override
    public boolean supportsSelectForUpdate() throws SQLException { return false; }
    @Override
    public boolean supportsStoredProcedures() throws SQLException { return false; }
    @Override
    public boolean supportsSubqueriesInComparisons() throws SQLException { return true; }
    @Override
    public boolean supportsSubqueriesInExists() throws SQLException { return true; }
    @Override
    public boolean supportsSubqueriesInIns() throws SQLException { return true; }
    @Override
    public boolean supportsSubqueriesInQuantifieds() throws SQLException { return false; }
    @Override
    public boolean supportsCorrelatedSubqueries() throws SQLException { return true; }
    @Override
    public boolean supportsUnion() throws SQLException { return true; }
    @Override
    public boolean supportsUnionAll() throws SQLException { return true; }
    @Override
    public boolean supportsOpenCursorsAcrossCommit() throws SQLException { return false; }
    @Override
    public boolean supportsOpenCursorsAcrossRollback() throws SQLException { return false; }
    @Override
    public boolean supportsOpenStatementsAcrossCommit() throws SQLException { return true; }
    @Override
    public boolean supportsOpenStatementsAcrossRollback() throws SQLException { return true; }
    @Override
    public int getMaxBinaryLiteralLength() throws SQLException { return 0; }
    @Override
    public int getMaxCharLiteralLength() throws SQLException { return 0; }
    @Override
    public int getMaxColumnNameLength() throws SQLException { return 0; }
    @Override
    public int getMaxColumnsInGroupBy() throws SQLException { return 0; }
    @Override
    public int getMaxColumnsInIndex() throws SQLException { return 0; }
    @Override
    public int getMaxColumnsInOrderBy() throws SQLException { return 0; }
    @Override
    public int getMaxColumnsInSelect() throws SQLException { return 0; }
    @Override
    public int getMaxColumnsInTable() throws SQLException { return 0; }
    @Override
    public int getMaxConnections() throws SQLException { return 0; }
    @Override
    public int getMaxCursorNameLength() throws SQLException { return 0; }
    @Override
    public int getMaxIndexLength() throws SQLException { return 0; }
    @Override
    public int getMaxSchemaNameLength() throws SQLException { return 0; }
    @Override
    public int getMaxProcedureNameLength() throws SQLException { return 0; }
    @Override
    public int getMaxCatalogNameLength() throws SQLException { return 0; }
    @Override
    public int getMaxRowSize() throws SQLException { return 0; }
    @Override
    public boolean doesMaxRowSizeIncludeBlobs() throws SQLException { return false; }
    @Override
    public int getMaxStatementLength() throws SQLException { return 0; }
    @Override
    public int getMaxStatements() throws SQLException { return 0; }
    @Override
    public int getMaxTableNameLength() throws SQLException { return 0; }
    @Override
    public int getMaxTablesInSelect() throws SQLException { return 0; }
    @Override
    public int getMaxUserNameLength() throws SQLException { return 0; }
    @Override
    public int getDefaultTransactionIsolation() throws SQLException { return Connection.TRANSACTION_SERIALIZABLE; }
    @Override
    public boolean supportsTransactions() throws SQLException { return true; }
    @Override
    public boolean supportsTransactionIsolationLevel(int level) throws SQLException {
        return level == Connection.TRANSACTION_SERIALIZABLE || level == Connection.TRANSACTION_READ_COMMITTED;
    }
    @Override
    public boolean supportsDataDefinitionAndDataManipulationTransactions() throws SQLException { return true; }
    @Override
    public boolean supportsDataManipulationTransactionsOnly() throws SQLException { return false; }
    @Override
    public boolean dataDefinitionCausesTransactionCommit() throws SQLException { return false; }
    @Override
    public boolean dataDefinitionIgnoredInTransactions() throws SQLException { return false; }

    private ResultSet executeMetadataQuery(String sql) throws SQLException {
        LibsqlStatement statement = connection.createStatementInternal();
        try {
            statement.closeOnCompletion();
            return statement.executeQuery(sql);
        } catch (SQLException e) {
            statement.close();
            throw e;
        }
    }

    private static String escapeLiteral(String value) {
        return value.replace("'", "''");
    }

    @Override
    public ResultSet getTables(String catalog, String schemaPattern, String tableNamePattern, String[] types) throws SQLException {
        String pattern = tableNamePattern == null || tableNamePattern.isEmpty()
                ? "%"
                : escapeLiteral(tableNamePattern);
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT NULL AS TABLE_CAT, NULL AS TABLE_SCHEM, NAME AS TABLE_NAME, TYPE AS TABLE_TYPE, ")
                .append("NULL AS REMARKS, NULL AS TYPE_CAT, NULL AS TYPE_SCHEM, NULL AS TYPE_NAME, ")
                .append("NULL AS SELF_REFERENCING_COL_NAME, NULL AS REF_GENERATION FROM (")
                .append("SELECT 'sqlite_schema' AS NAME, 'SYSTEM TABLE' AS TYPE")
                .append(" UNION ALL SELECT NAME, UPPER(TYPE) AS TYPE FROM sqlite_schema")
                .append(" WHERE NAME NOT LIKE 'sqlite\\_%' ESCAPE '\\' AND UPPER(TYPE) IN ('TABLE', 'VIEW')")
                .append(" UNION ALL SELECT NAME, 'GLOBAL TEMPORARY' AS TYPE FROM sqlite_temp_master")
                .append(" UNION ALL SELECT NAME, 'SYSTEM TABLE' AS TYPE FROM sqlite_schema")
                .append(" WHERE NAME LIKE 'sqlite\\_%' ESCAPE '\\'")
                .append(") WHERE TABLE_NAME LIKE '").append(pattern).append("' ESCAPE '\\'");
        if (types != null && types.length != 0) {
            sql.append(" AND TABLE_TYPE IN (");
            for (int i = 0; i < types.length; i++) {
                if (i > 0) {
                    sql.append(",");
                }
                sql.append("'").append(escapeLiteral(types[i].toUpperCase())).append("'");
            }
            sql.append(")");
        }
        sql.append(" ORDER BY TABLE_TYPE, TABLE_NAME");
        return executeMetadataQuery(sql.toString());
    }

    @Override
    public ResultSet getColumns(String catalog, String schemaPattern, String tableNamePattern, String columnNamePattern) throws SQLException {
        String tablePattern = tableNamePattern == null || tableNamePattern.isEmpty() ? "%" : tableNamePattern;
        String columnPattern = columnNamePattern == null || columnNamePattern.isEmpty() ? "%" : columnNamePattern;
        String sql = "SELECT m.name AS TABLE_NAME, m.sql AS CREATE_SQL, p.cid AS CID, p.name AS COLUMN_NAME, "
                + "p.type AS DECLARED_TYPE, p.\"notnull\" AS NOT_NULL, p.dflt_value AS COLUMN_DEF, p.pk AS PK, "
                + "p.hidden AS HIDDEN FROM sqlite_master m JOIN pragma_table_xinfo(m.name) p "
                + "WHERE m.type IN ('table', 'view')"
                + " AND m.name LIKE '" + escapeLiteral(tablePattern) + "' ESCAPE '\\'"
                + " AND p.name LIKE '" + escapeLiteral(columnPattern) + "' ESCAPE '\\'"
                + " ORDER BY m.name, p.cid";
        return new LibsqlResultSet(null, columnsResult(connection.executeStatement(new Stmt(sql))));
    }

    private static StmtResult columnsResult(StmtResult raw) {
        List<Col> columns = List.of(
                new Col("TABLE_CAT", "TEXT"),
                new Col("TABLE_SCHEM", "TEXT"),
                new Col("TABLE_NAME", "TEXT"),
                new Col("COLUMN_NAME", "TEXT"),
                new Col("DATA_TYPE", "INTEGER"),
                new Col("TYPE_NAME", "TEXT"),
                new Col("COLUMN_SIZE", "INTEGER"),
                new Col("BUFFER_LENGTH", "INTEGER"),
                new Col("DECIMAL_DIGITS", "INTEGER"),
                new Col("NUM_PREC_RADIX", "INTEGER"),
                new Col("NULLABLE", "INTEGER"),
                new Col("REMARKS", "TEXT"),
                new Col("COLUMN_DEF", "TEXT"),
                new Col("SQL_DATA_TYPE", "INTEGER"),
                new Col("SQL_DATETIME_SUB", "INTEGER"),
                new Col("CHAR_OCTET_LENGTH", "INTEGER"),
                new Col("ORDINAL_POSITION", "INTEGER"),
                new Col("IS_NULLABLE", "TEXT"),
                new Col("SCOPE_CATALOG", "TEXT"),
                new Col("SCOPE_SCHEMA", "TEXT"),
                new Col("SCOPE_TABLE", "TEXT"),
                new Col("SOURCE_DATA_TYPE", "INTEGER"),
                new Col("IS_AUTOINCREMENT", "TEXT"),
                new Col("IS_GENERATEDCOLUMN", "TEXT"));
        List<Row> rows = new ArrayList<>();
        if (raw != null && raw.rows() != null) {
            for (Row row : raw.rows()) {
                List<Value> values = row.values();
                String tableName = text(values.get(0));
                String createSql = text(values.get(1));
                long cid = integer(values.get(2));
                String columnName = text(values.get(3));
                String declaredType = text(values.get(4));
                boolean notNull = integer(values.get(5)) != 0;
                String columnDefault = text(values.get(6));
                boolean primaryKey = integer(values.get(7)) != 0;
                long hidden = integer(values.get(8));

                ColumnShape shape = columnShape(declaredType);
                boolean autoIncrement = primaryKey
                        && LibsqlResultSetMetaData.isAutoIncrementColumn(createSql, columnName);
                boolean generated = hidden == 2 || hidden == 3;

                rows.add(new Row(List.of(
                        Value.ofNull(),
                        Value.ofNull(),
                        Value.of(tableName),
                        Value.of(columnName),
                        Value.of(shape.jdbcType()),
                        Value.of(shape.typeName()),
                        Value.of(shape.columnSize()),
                        Value.of(UNKNOWN_COLUMN_SIZE),
                        Value.of(shape.decimalDigits()),
                        Value.of(10),
                        Value.of(notNull ? DatabaseMetaData.columnNoNulls : DatabaseMetaData.columnNullable),
                        Value.ofNull(),
                        Value.of(columnDefault),
                        Value.of(0),
                        Value.of(0),
                        Value.of(UNKNOWN_COLUMN_SIZE),
                        Value.of(cid + 1),
                        Value.of(notNull ? "NO" : "YES"),
                        Value.ofNull(),
                        Value.ofNull(),
                        Value.ofNull(),
                        Value.ofNull(),
                        Value.of(autoIncrement ? "YES" : "NO"),
                        Value.of(generated ? "YES" : "NO"))));
            }
        }
        return new StmtResult(columns, rows, 0, null);
    }

    private static ColumnShape columnShape(String declaredType) {
        String typeName = declaredType == null ? "TEXT" : declaredType.toUpperCase(Locale.ENGLISH);
        int jdbcType;
        int decimalDigits = DEFAULT_DECIMAL_DIGITS;
        if (INTEGER_TYPE.matcher(typeName).find()) {
            jdbcType = Types.INTEGER;
            decimalDigits = 0;
        } else if (VARCHAR_TYPE.matcher(typeName).find()) {
            jdbcType = Types.VARCHAR;
            decimalDigits = 0;
        } else if (FLOAT_TYPE.matcher(typeName).find()) {
            jdbcType = Types.FLOAT;
        } else {
            jdbcType = Types.VARCHAR;
        }

        int columnSize = UNKNOWN_COLUMN_SIZE;
        int startOfDimension = typeName.indexOf('(');
        if (startOfDimension > 0) {
            int endOfDimension = typeName.indexOf(')', startOfDimension);
            if (endOfDimension > 0) {
                int dimensionSeparator = typeName.indexOf(',', startOfDimension);
                String integerPart;
                String decimalPart;
                if (dimensionSeparator > 0) {
                    integerPart = typeName.substring(startOfDimension + 1, dimensionSeparator);
                    decimalPart = typeName.substring(dimensionSeparator + 1, endOfDimension);
                } else {
                    integerPart = typeName.substring(startOfDimension + 1, endOfDimension);
                    decimalPart = null;
                }
                try {
                    int integerValue = Integer.parseUnsignedInt(integerPart.trim());
                    if (decimalPart != null) {
                        decimalDigits = Integer.parseUnsignedInt(decimalPart.trim());
                        columnSize = integerValue + decimalDigits;
                    } else {
                        decimalDigits = 0;
                        columnSize = integerValue;
                    }
                } catch (NumberFormatException ignored) {
                }
            }
            typeName = typeName.substring(0, startOfDimension).trim();
        }
        return new ColumnShape(jdbcType, typeName, columnSize, decimalDigits);
    }

    private static String text(Value value) {
        return value instanceof Value.Text text ? text.value() : null;
    }

    private static long integer(Value value) {
        return value instanceof Value.Integer integer ? integer.value() : 0L;
    }

    private record ColumnShape(int jdbcType, String typeName, int columnSize, int decimalDigits) {}

    @Override
    public ResultSet getPrimaryKeys(String catalog, String schema, String table) throws SQLException {
        String escapedTable = escapeLiteral(table == null ? "" : table);
        String sql = "SELECT NULL AS TABLE_CAT, NULL AS TABLE_SCHEM, '" + escapedTable + "' AS TABLE_NAME, name AS COLUMN_NAME, pk AS KEY_SEQ, NULL AS PK_NAME " +
                "FROM pragma_table_info('" + escapedTable + "') WHERE pk > 0 ORDER BY pk";
        return executeMetadataQuery(sql);
    }

    @Override
    public ResultSet getTableTypes() throws SQLException {
        return executeMetadataQuery("SELECT 'TABLE' AS TABLE_TYPE UNION SELECT 'VIEW' AS TABLE_TYPE");
    }

    @Override
    public Connection getConnection() throws SQLException {
        return connection;
    }

    @Override public ResultSet getProcedures(String catalog, String schemaPattern, String procedureNamePattern) throws SQLException { throw new UnsupportedOperationException(); }
    @Override public ResultSet getProcedureColumns(String catalog, String schemaPattern, String procedureNamePattern, String columnNamePattern) throws SQLException { throw new UnsupportedOperationException(); }
    @Override public ResultSet getSchemas() throws SQLException { return connection.createStatement().executeQuery("SELECT NULL AS TABLE_SCHEM, NULL AS TABLE_CATALOG WHERE 0"); }
    @Override public ResultSet getCatalogs() throws SQLException { return connection.createStatement().executeQuery("SELECT NULL AS TABLE_CAT WHERE 0"); }
    @Override public ResultSet getColumnPrivileges(String catalog, String schema, String table, String columnNamePattern) throws SQLException { throw new UnsupportedOperationException(); }
    @Override public ResultSet getTablePrivileges(String catalog, String schemaPattern, String tableNamePattern) throws SQLException { throw new UnsupportedOperationException(); }
    @Override public ResultSet getBestRowIdentifier(String catalog, String schema, String table, int scope, boolean nullable) throws SQLException { throw new UnsupportedOperationException(); }
    @Override public ResultSet getVersionColumns(String catalog, String schema, String table) throws SQLException { throw new UnsupportedOperationException(); }
    @Override public ResultSet getImportedKeys(String catalog, String schema, String table) throws SQLException {
        String sql = "SELECT NULL AS PKTABLE_CAT, NULL AS PKTABLE_SCHEM, NULL AS PKTABLE_NAME, NULL AS PKCOLUMN_NAME, " +
                "NULL AS FKTABLE_CAT, NULL AS FKTABLE_SCHEM, NULL AS FKTABLE_NAME, NULL AS FKCOLUMN_NAME, " +
                "0 AS KEY_SEQ, 0 AS UPDATE_RULE, 0 AS DELETE_RULE, NULL AS FK_NAME, NULL AS PK_NAME, 0 AS DEFERRABILITY WHERE 0";
        return connection.createStatement().executeQuery(sql);
    }
    @Override public ResultSet getExportedKeys(String catalog, String schema, String table) throws SQLException {
        String sql = "SELECT NULL AS PKTABLE_CAT, NULL AS PKTABLE_SCHEM, NULL AS PKTABLE_NAME, NULL AS PKCOLUMN_NAME, " +
                "NULL AS FKTABLE_CAT, NULL AS FKTABLE_SCHEM, NULL AS FKTABLE_NAME, NULL AS FKCOLUMN_NAME, " +
                "0 AS KEY_SEQ, 0 AS UPDATE_RULE, 0 AS DELETE_RULE, NULL AS FK_NAME, NULL AS PK_NAME, 0 AS DEFERRABILITY WHERE 0";
        return connection.createStatement().executeQuery(sql);
    }
    @Override public ResultSet getCrossReference(String parentCatalog, String parentSchema, String parentTable, String foreignCatalog, String foreignSchema, String foreignTable) throws SQLException {
        String sql = "SELECT NULL AS PKTABLE_CAT, NULL AS PKTABLE_SCHEM, NULL AS PKTABLE_NAME, NULL AS PKCOLUMN_NAME, " +
                "NULL AS FKTABLE_CAT, NULL AS FKTABLE_SCHEM, NULL AS FKTABLE_NAME, NULL AS FKCOLUMN_NAME, " +
                "0 AS KEY_SEQ, 0 AS UPDATE_RULE, 0 AS DELETE_RULE, NULL AS FK_NAME, NULL AS PK_NAME, 0 AS DEFERRABILITY WHERE 0";
        return connection.createStatement().executeQuery(sql);
    }
    @Override public ResultSet getTypeInfo() throws SQLException {
        String sql = "SELECT 'VARCHAR' AS TYPE_NAME, " + java.sql.Types.VARCHAR + " AS DATA_TYPE, 0 AS PRECISION, " +
                "NULL AS LITERAL_PREFIX, NULL AS LITERAL_SUFFIX, NULL AS CREATE_PARAMS, 1 AS NULLABLE, 1 AS CASE_SENSITIVE, " +
                "3 AS SEARCHABLE, 0 AS UNSIGNED_ATTRIBUTE, 0 AS FIXED_PREC_SCALE, 0 AS AUTO_INCREMENT, " +
                "NULL AS LOCAL_TYPE_NAME, 0 AS MINIMUM_SCALE, 0 AS MAXIMUM_SCALE, 0 AS SQL_DATA_TYPE, 0 AS SQL_DATETIME_SUB, 10 AS NUM_PREC_RADIX WHERE 0";
        return connection.createStatement().executeQuery(sql);
    }
    @Override public ResultSet getIndexInfo(String catalog, String schema, String table, boolean unique, boolean approximate) throws SQLException {
        String sql = "SELECT NULL AS TABLE_CAT, NULL AS TABLE_SCHEM, '" + table + "' AS TABLE_NAME, " +
                "CASE il.\"unique\" WHEN 1 THEN 0 ELSE 1 END AS NON_UNIQUE, " +
                "NULL AS INDEX_QUALIFIER, il.name AS INDEX_NAME, 3 AS TYPE, " +
                "ii.seqno + 1 AS ORDINAL_POSITION, ii.name AS COLUMN_NAME, " +
                "'A' AS ASC_OR_DESC, 0 AS CARDINALITY, 0 AS PAGES, NULL AS FILTER_CONDITION " +
                "FROM pragma_index_list('" + table + "') il, pragma_index_info(il.name) ii";
        if (unique) {
            sql += " WHERE il.\"unique\" = 1";
        }
        sql += " ORDER BY NON_UNIQUE, TYPE, INDEX_NAME, ORDINAL_POSITION";
        return connection.createStatement().executeQuery(sql);
    }
    @Override public boolean supportsResultSetType(int type) throws SQLException { return type == ResultSet.TYPE_FORWARD_ONLY; }
    @Override public boolean supportsResultSetConcurrency(int type, int concurrency) throws SQLException { return type == ResultSet.TYPE_FORWARD_ONLY && concurrency == ResultSet.CONCUR_READ_ONLY; }
    @Override public boolean ownUpdatesAreVisible(int type) throws SQLException { return false; }
    @Override public boolean ownDeletesAreVisible(int type) throws SQLException { return false; }
    @Override public boolean ownInsertsAreVisible(int type) throws SQLException { return false; }
    @Override public boolean othersUpdatesAreVisible(int type) throws SQLException { return false; }
    @Override public boolean othersDeletesAreVisible(int type) throws SQLException { return false; }
    @Override public boolean othersInsertsAreVisible(int type) throws SQLException { return false; }
    @Override public boolean updatesAreDetected(int type) throws SQLException { return false; }
    @Override public boolean deletesAreDetected(int type) throws SQLException { return false; }
    @Override public boolean insertsAreDetected(int type) throws SQLException { return false; }
    @Override public boolean supportsBatchUpdates() throws SQLException { return true; }
    @Override public ResultSet getUDTs(String catalog, String schemaPattern, String typeNamePattern, int[] types) throws SQLException { throw new UnsupportedOperationException(); }
    @Override public boolean supportsSavepoints() throws SQLException { return false; }
    @Override public boolean supportsNamedParameters() throws SQLException { return false; }
    @Override public boolean supportsMultipleOpenResults() throws SQLException { return false; }
    @Override public boolean supportsGetGeneratedKeys() throws SQLException { return true; }
    @Override public ResultSet getSuperTypes(String catalog, String schemaPattern, String typeNamePattern) throws SQLException { throw new UnsupportedOperationException(); }
    @Override public ResultSet getSuperTables(String catalog, String schemaPattern, String tableNamePattern) throws SQLException { throw new UnsupportedOperationException(); }
    @Override public ResultSet getAttributes(String catalog, String schemaPattern, String typeNamePattern, String attributeNamePattern) throws SQLException { throw new UnsupportedOperationException(); }
    @Override public boolean supportsResultSetHoldability(int holdability) throws SQLException { return holdability == ResultSet.HOLD_CURSORS_OVER_COMMIT; }
    @Override public int getResultSetHoldability() throws SQLException { return ResultSet.HOLD_CURSORS_OVER_COMMIT; }
    @Override public int getDatabaseMajorVersion() throws SQLException {
        String[] parts = resolveDatabaseProductVersion().split("\\.");
        try { return Integer.parseInt(parts[0]); } catch (NumberFormatException e) { return 3; }
    }
    @Override public int getDatabaseMinorVersion() throws SQLException {
        String[] parts = resolveDatabaseProductVersion().split("\\.");
        try { return parts.length > 1 ? Integer.parseInt(parts[1]) : 0; } catch (NumberFormatException e) { return 0; }
    }
    @Override public int getJDBCMajorVersion() throws SQLException { return 4; }
    @Override public int getJDBCMinorVersion() throws SQLException { return 3; }
    @Override public int getSQLStateType() throws SQLException { return sqlStateSQL; }
    @Override public boolean locatorsUpdateCopy() throws SQLException { return false; }
    @Override public boolean supportsStatementPooling() throws SQLException { return false; }
    @Override public RowIdLifetime getRowIdLifetime() throws SQLException { return RowIdLifetime.ROWID_UNSUPPORTED; }
    @Override public ResultSet getSchemas(String catalog, String schemaPattern) throws SQLException { return getSchemas(); }
    @Override public boolean supportsStoredFunctionsUsingCallSyntax() throws SQLException { return false; }
    @Override public boolean autoCommitFailureClosesAllResultSets() throws SQLException { return false; }
    @Override public ResultSet getClientInfoProperties() throws SQLException { throw new UnsupportedOperationException(); }
    @Override public ResultSet getFunctions(String catalog, String schemaPattern, String functionNamePattern) throws SQLException { throw new UnsupportedOperationException(); }
    @Override public ResultSet getFunctionColumns(String catalog, String schemaPattern, String functionNamePattern, String columnNamePattern) throws SQLException { throw new UnsupportedOperationException(); }
    @Override public ResultSet getPseudoColumns(String catalog, String schemaPattern, String tableNamePattern, String columnNamePattern) throws SQLException { throw new UnsupportedOperationException(); }
    @Override public boolean generatedKeyAlwaysReturned() throws SQLException { return false; }

    @Override
    public <T> T unwrap(Class<T> iface) throws SQLException {
        if (iface.isInstance(this)) return iface.cast(this);
        throw new SQLException("Cannot unwrap to " + iface.getName());
    }

    @Override
    public boolean isWrapperFor(Class<?> iface) throws SQLException {
        return iface.isInstance(this);
    }
}