Examples with IdExpr org.sqlite.parser.ast.IdExpr used on opensource projects

Example 1 with IdExpr

use of org.sqlite.parser.ast.IdExpr in project sqlite-jna by gwenn.

the class DbMeta method getExportedKeys.

@Override
public ResultSet getExportedKeys(String catalog, String schema, String table) throws SQLException {
    checkOpen();
    final StringBuilder sql = new StringBuilder();
    catalog = schemaProvider.getDbName(catalog, table);
    sql.append("select ").append(quote(catalog)).append(" as PKTABLE_CAT, ").append("null as PKTABLE_SCHEM, ").append(quote(table)).append(" as PKTABLE_NAME, ").append("pc as PKCOLUMN_NAME, ").append(quote(catalog)).append(" as FKTABLE_CAT, ").append("null as FKTABLE_SCHEM, ").append("ft as FKTABLE_NAME, ").append("fc as FKCOLUMN_NAME, ").append("seq as KEY_SEQ, ").append(importedKeyNoAction).append(// FIXME on_update (6) NO ACTION, CASCADE
    " as UPDATE_RULE, ").append(importedKeyNoAction).append(// FIXME on_delete (7) NO ACTION, CASCADE
    " as DELETE_RULE, ").append("fk as FK_NAME, ").append("null as PK_NAME, ").append(importedKeyNotDeferrable).append(// FIXME
    " as DEFERRABILITY ").append("from (");
    final Collection<String> fkTables = new ArrayList<>();
    final String s;
    if ("main".equalsIgnoreCase(catalog)) {
        s = "SELECT name FROM sqlite_master WHERE type = 'table' AND name NOT LIKE ? AND sql LIKE ?";
    } else if ("temp".equalsIgnoreCase(catalog)) {
        s = "SELECT name FROM sqlite_temp_master WHERE type = 'table' AND name NOT LIKE ? AND sql LIKE ?";
    } else {
        s = "SELECT name FROM \"" + escapeIdentifier(catalog) + "\".sqlite_master WHERE type = 'table' AND name NOT LIKE ? AND sql LIKE ?";
    }
    try (PreparedStatement fks = c.prepareStatement(s)) {
        fks.setString(1, table);
        fks.setString(2, "%REFERENCES%" + table + "%(%");
        try (ResultSet rs = fks.executeQuery()) {
            while (rs.next()) {
                fkTables.add(rs.getString(1));
            }
        }
    }
    int count = 0;
    if (!fkTables.isEmpty()) {
        for (String fkTable : fkTables) {
            Pragma pragma = new Pragma(new QualifiedName(catalog, "foreign_key_list"), new IdExpr(fkTable));
            // Pragma cannot be used as subquery...
            try (PreparedStatement foreign_key_list = c.prepareStatement(pragma.toSql());
                ResultSet rs = foreign_key_list.executeQuery()) {
                // 1:id|2:seq|3:table|4:from|5:to|6:on_update|7:on_delete|8:match
                while (rs.next()) {
                    if (!rs.getString(3).equalsIgnoreCase(table)) {
                        continue;
                    }
                    if (count > 0) {
                        sql.append(" UNION ALL ");
                    }
                    sql.append("SELECT ").append(quote(fkTable + '_' + table + '_' + rs.getString(1))).append(// to be kept in sync with getForeignKeys
                    " AS fk, ").append(quote(fkTable)).append(" AS ft, ").append(quote(rs.getString(5))).append(" AS pc, ").append(quote(rs.getString(4))).append(" AS fc, ").append(rs.getShort(2) + 1).append(" AS seq");
                    count++;
                }
            } catch (StmtException e) {
                // query does not return ResultSet
                assert e.getErrorCode() == ErrCodes.WRAPPER_SPECIFIC;
            }
        }
    }
    if (count == 0) {
        sql.append("SELECT NULL AS fk, NULL AS ft, NULL AS pc, NULL AS fc, NULL AS seq) limit 0");
    } else {
        sql.append(") order by FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_NAME, KEY_SEQ");
    }
    final PreparedStatement eks = c.prepareStatement(sql.toString());
    eks.closeOnCompletion();
    return eks.executeQuery();
}

Example 2 with IdExpr

use of org.sqlite.parser.ast.IdExpr in project sqlite-jna by gwenn.

the class DbMeta method getColumns.

@Override
public ResultSet getColumns(String catalog, String schemaPattern, String tableNamePattern, String columnNamePattern) throws SQLException {
    checkOpen();
    final StringBuilder sql = new StringBuilder();
    final List<QualifiedName> tbls = schemaProvider.findTables(catalog, tableNamePattern);
    sql.append("select ").append("cat as TABLE_CAT, ").append("null as TABLE_SCHEM, ").append("tbl as TABLE_NAME, ").append("cn as COLUMN_NAME, ").append("ct as DATA_TYPE, ").append("tn as TYPE_NAME, ").append(// FIXME precision or display size
    "10 as COLUMN_SIZE, ").append(// not used
    "null as BUFFER_LENGTH, ").append(// FIXME scale or null
    "10 as DECIMAL_DIGITS, ").append("10 as NUM_PREC_RADIX, ").append("colnullable as NULLABLE, ").append("null as REMARKS, ").append("cdflt as COLUMN_DEF, ").append(// unused
    "null as SQL_DATA_TYPE, ").append(// unused
    "null as SQL_DATETIME_SUB, ").append(// FIXME same as COLUMN_SIZE
    "10 as CHAR_OCTET_LENGTH, ").append("ordpos as ORDINAL_POSITION, ").append("(case colnullable when 0 then 'NO' when 1 then 'YES' else '' end)").append(" as IS_NULLABLE, ").append("null as SCOPE_CATLOG, ").append("null as SCOPE_SCHEMA, ").append("null as SCOPE_TABLE, ").append("null as SOURCE_DATA_TYPE, ").append(// TODO http://sqlite.org/autoinc.html
    "'' as IS_AUTOINCREMENT, ").append("'' as IS_GENERATEDCOLUMN from (");
    boolean colFound = false;
    for (QualifiedName tbl : tbls) {
        Pragma pragma = new Pragma(new QualifiedName(tbl.dbName, "table_info"), new IdExpr(tbl.name));
        // Pragma cannot be used as subquery...
        try (PreparedStatement table_info = c.prepareStatement(pragma.toSql());
            ResultSet rs = table_info.executeQuery()) {
            // 1:cid|2:name|3:type|4:notnull|5:dflt_value|6:pk
            while (rs.next()) {
                if (colFound)
                    sql.append(" UNION ALL ");
                colFound = true;
                final String colType = getSQLiteType(rs.getString(3));
                final int colJavaType = getJavaType(colType);
                sql.append("SELECT ").append(quote(tbl.dbName)).append(" AS cat, ").append(quote(tbl.name)).append(" AS tbl, ").append(rs.getInt(1) + 1).append(" AS ordpos, ").append(rs.getBoolean(4) ? columnNoNulls : columnNullable).append(" AS colnullable, ").append(colJavaType).append(" AS ct, ").append(quote(rs.getString(2))).append(" AS cn, ").append(quote(colType)).append(" AS tn, ").append(quote(rs.getString(5))).append(" AS cdflt");
                if (columnNamePattern != null && !"%".equals(columnNamePattern)) {
                    sql.append(" WHERE cn LIKE ").append(quote(columnNamePattern));
                }
            }
        } catch (StmtException e) {
            // query does not return ResultSet
            assert e.getErrorCode() == ErrCodes.WRAPPER_SPECIFIC;
        }
    }
    sql.append(colFound ? ") order by TABLE_SCHEM, TABLE_NAME, ORDINAL_POSITION" : "SELECT NULL AS cat, NULL AS tbl, NULL AS ordpos, NULL AS colnullable, NULL AS ct, " + "NULL AS cn, NULL AS tn, NULL AS cdflt) limit 0");
    final PreparedStatement columns = c.prepareStatement(sql.toString());
    columns.closeOnCompletion();
    return columns.executeQuery();
}

Example 3 with IdExpr

use of org.sqlite.parser.ast.IdExpr in project sqlite-jna by gwenn.

the class DbMeta method getIndexInfo.

@Override
public ResultSet getIndexInfo(String catalog, String schema, String table, boolean unique, boolean approximate) throws SQLException {
    checkOpen();
    catalog = schemaProvider.getDbName(catalog, table);
    final StringBuilder sql = new StringBuilder();
    sql.append("select ").append(quote(catalog)).append(" as TABLE_CAT, ").append("null as TABLE_SCHEM, ").append(quote(table)).append(" as TABLE_NAME, ").append("nu as NON_UNIQUE, ").append(quote(catalog)).append(" as INDEX_QUALIFIER, ").append("idx as INDEX_NAME, ").append(tableIndexOther).append(" as TYPE, ").append("seqno as ORDINAL_POSITION, ").append("cn as COLUMN_NAME, ").append("null as ASC_OR_DESC, ").append("0 as CARDINALITY, ").append("0 as PAGES, ").append("null as FILTER_CONDITION ").append("from (");
    final Map<String, Boolean> indexes = new HashMap<>();
    Pragma pragma = new Pragma(new QualifiedName(catalog, "index_list"), new IdExpr(table));
    try (PreparedStatement index_list = c.prepareStatement(pragma.toSql());
        ResultSet rs = index_list.executeQuery()) {
        // 1:seq|2:name|3:unique
        while (rs.next()) {
            final boolean notuniq = !rs.getBoolean(3);
            if (unique && notuniq) {
                continue;
            }
            indexes.put(rs.getString(2), notuniq);
        }
    } catch (StmtException e) {
        // query does not return ResultSet
        assert e.getErrorCode() == ErrCodes.WRAPPER_SPECIFIC;
    }
    if (indexes.isEmpty()) {
        sql.append("SELECT NULL AS nu, NULL AS idx, NULL AS seqno, NULL AS cn) limit 0");
    } else {
        boolean found = false;
        for (final Entry<String, Boolean> index : indexes.entrySet()) {
            Pragma _pragma = new Pragma(new QualifiedName(null, "index_info"), new IdExpr(index.getKey()));
            try (PreparedStatement index_info = c.prepareStatement(_pragma.toSql());
                ResultSet rs = index_info.executeQuery()) {
                // 1:seqno|2:cid|3:name
                while (rs.next()) {
                    if (found) {
                        sql.append(" UNION ALL ");
                    }
                    sql.append("SELECT ").append(index.getValue() ? 1 : 0).append(" AS nu, ").append(quote(index.getKey())).append(" AS idx, ").append(rs.getInt(1)).append(" AS seqno, ").append(quote(rs.getString(3))).append(" AS cn");
                    found = true;
                }
            // } catch(StmtException e) { // query does not return ResultSet
            }
        }
        if (found) {
            sql.append(") order by NON_UNIQUE, TYPE, INDEX_NAME, ORDINAL_POSITION");
        } else {
            sql.append("SELECT NULL AS nu, NULL AS idx, NULL AS seqno, NULL AS cn) limit 0");
        }
    }
    final PreparedStatement idx = c.prepareStatement(sql.toString());
    idx.closeOnCompletion();
    return idx.executeQuery();
}

Example 4 with IdExpr

use of org.sqlite.parser.ast.IdExpr in project sqlite-jna by gwenn.

the class DbMeta method getBestRowIdentifier.

@Override
public ResultSet getBestRowIdentifier(String catalog, String schema, String table, int scope, boolean nullable) throws SQLException {
    checkOpen();
    final StringBuilder sql = new StringBuilder();
    catalog = schemaProvider.getDbName(catalog, table);
    sql.append("select ").append(scope).append(" as SCOPE, ").append("cn as COLUMN_NAME, ").append("ct as DATA_TYPE, ").append("tn as TYPE_NAME, ").append(// FIXME precision (19 for LONG, 15 for REAL) or display size (20 for LONG, 25 for REAL)
    "10 as COLUMN_SIZE, ").append("0 as BUFFER_LENGTH, ").append(// FIXME scale (0 for LONG, 15 for REAL)
    "0 as DECIMAL_DIGITS, ").append("pc as PSEUDO_COLUMN from (");
    // Pragma cannot be used as subquery...
    int count = -1;
    String colName = null;
    String colType = null;
    Pragma pragma = new Pragma(new QualifiedName(catalog, "table_info"), new IdExpr(table));
    try (PreparedStatement table_info = c.prepareStatement(pragma.toSql());
        ResultSet rs = table_info.executeQuery()) {
        // 1:cid|2:name|3:type|4:notnull|5:dflt_value|6:pk
        while (count < 2 && rs.next()) {
            if (count < 0) {
                // table exists
                count = 0;
            }
            if (rs.getBoolean(6) && (nullable || rs.getBoolean(4))) {
                colName = rs.getString(2);
                colType = getSQLiteType(rs.getString(3));
                count++;
            }
        }
    } catch (StmtException e) {
        // query does not return ResultSet
        assert e.getErrorCode() == ErrCodes.WRAPPER_SPECIFIC;
        count = -1;
    }
    if (count == 1) {
        sql.append("SELECT ").append(quote(colName)).append(" as cn, ").append(getJavaType(colType)).append(" as ct, ").append("'").append(colType).append("' as tn, ").append(bestRowNotPseudo).append(" as pc) order by SCOPE");
    } else {
        sql.append("SELECT ").append("'ROWID' AS cn, ").append(Types.INTEGER).append(" AS ct, ").append("'INTEGER' AS tn, ").append(bestRowPseudo).append(" AS pc) order by SCOPE");
        if (count < 0) {
            sql.append(" limit 0");
        }
    }
    final PreparedStatement columns = c.prepareStatement(sql.toString());
    columns.closeOnCompletion();
    return columns.executeQuery();
}