Examples with Delete org.h2.command.dml.Delete used on opensource projects

Example 26 with Delete

use of org.h2.command.dml.Delete in project h2database by h2database.

the class TestCrashAPI method getConnection.

private Connection getConnection(int seed, boolean delete) throws SQLException {
    openCount++;
    if (delete) {
        deleteDb();
    }
    // can not use FILE_LOCK=NO, otherwise something could be written into
    // the database in the finalize method
    String add = ";MAX_QUERY_TIMEOUT=10000";
    // int testing;
    // if(openCount >= 32) {
    // int test;
    // Runtime.getRuntime().halt(0);
    // System.exit(1);
    // }
    // System.out.println("now open " + openCount);
    // add += ";TRACE_LEVEL_FILE=3";
    // config.logMode = 2;
    // }
    String dbName = "crashApi" + seed;
    String url = getURL(DIR + "/" + dbName, true) + add;
    // int test;
    // url += ";DB_CLOSE_ON_EXIT=FALSE";
    // int test;
    // url += ";TRACE_LEVEL_FILE=3";
    Connection conn = null;
    String fileName = "temp/backup/db-" + uniqueId++ + ".zip";
    Backup.execute(fileName, getBaseDir() + "/" + DIR, dbName, true);
    // close databases earlier
    System.gc();
    try {
        conn = DriverManager.getConnection(url, "sa", getPassword(""));
        // delete the backup if opening was successful
        FileUtils.delete(fileName);
    } catch (SQLException e) {
        if (e.getErrorCode() == ErrorCode.WRONG_USER_OR_PASSWORD) {
            // delete if the password changed
            FileUtils.delete(fileName);
        }
        throw e;
    }
    int len = random.getInt(50);
    int first = random.getInt(statements.size() - len);
    int end = first + len;
    Statement stat = conn.createStatement();
    stat.execute("SET LOCK_TIMEOUT 10");
    stat.execute("SET WRITE_DELAY 0");
    if (random.nextBoolean()) {
        if (random.nextBoolean()) {
            double g = random.nextGaussian();
            int size = (int) Math.abs(10000 * g * g);
            stat.execute("SET CACHE_SIZE " + size);
        } else {
            stat.execute("SET CACHE_SIZE 0");
        }
    }
    stat.execute("SCRIPT NOPASSWORDS NOSETTINGS");
    for (int i = first; i < end && i < statements.size() && !stopped; i++) {
        try {
            stat.execute("SELECT * FROM TEST WHERE ID=1");
        } catch (Throwable t) {
            printIfBad(seed, -i, -1, t);
        }
        try {
            stat.execute("SELECT * FROM TEST WHERE ID=1 OR ID=1");
        } catch (Throwable t) {
            printIfBad(seed, -i, -1, t);
        }
        String sql = statements.get(i);
        try {
            // if(openCount == 32) {
            // int test;
            // System.out.println("stop!");
            // }
            stat.execute(sql);
        } catch (Throwable t) {
            printIfBad(seed, -i, -1, t);
        }
    }
    if (random.nextBoolean()) {
        try {
            conn.commit();
        } catch (Throwable t) {
            printIfBad(seed, 0, -1, t);
        }
    }
    return conn;
}

Example 27 with Delete

use of org.h2.command.dml.Delete in project h2database by h2database.

the class TestDiskSpaceLeak method main.

/**
 * Run just this test.
 *
 * @param args ignored
 */
public static void main(String... args) throws Exception {
    DeleteDbFiles.execute("data", null, true);
    Class.forName("org.h2.Driver");
    Connection conn;
    long before = 0;
    for (int i = 0; i < 10; i++) {
        conn = DriverManager.getConnection("jdbc:h2:data/test");
        ResultSet rs;
        rs = conn.createStatement().executeQuery("select count(*) from information_schema.lobs");
        rs.next();
        System.out.println("lobs: " + rs.getInt(1));
        rs = conn.createStatement().executeQuery("select count(*) from information_schema.lob_map");
        rs.next();
        System.out.println("lob_map: " + rs.getInt(1));
        rs = conn.createStatement().executeQuery("select count(*) from information_schema.lob_data");
        rs.next();
        System.out.println("lob_data: " + rs.getInt(1));
        conn.close();
        Recover.execute("data", "test");
        new File("data/test.h2.sql").renameTo(new File("data/test." + i + ".sql"));
        conn = DriverManager.getConnection("jdbc:h2:data/test");
        // ((JdbcConnection) conn).setPowerOffCount(i);
        ((JdbcConnection) conn).setPowerOffCount(28);
        String last = "connect";
        try {
            conn.createStatement().execute("drop table test if exists");
            last = "drop";
            conn.createStatement().execute("create table test(id identity, b blob)");
            last = "create";
            conn.createStatement().execute("insert into test values(1, space(10000))");
            last = "insert";
            conn.createStatement().execute("delete from test");
            last = "delete";
            conn.createStatement().execute("insert into test values(1, space(10000))");
            last = "insert2";
            conn.createStatement().execute("delete from test");
            last = "delete2";
        } catch (SQLException e) {
        // ignore
        } finally {
            JdbcUtils.closeSilently(conn);
        }
        long now = new File("data/test.h2.db").length();
        long diff = now - before;
        before = now;
        System.out.println(now + " " + diff + " " + i + " " + last);
    }
}

Example 28 with Delete

use of org.h2.command.dml.Delete in project h2database by h2database.

the class MetaTable method generateRows.

/**
 * Generate the data for the given metadata table using the given first and
 * last row filters.
 *
 * @param session the session
 * @param first the first row to return
 * @param last the last row to return
 * @return the generated rows
 */
public ArrayList<Row> generateRows(Session session, SearchRow first, SearchRow last) {
    Value indexFrom = null, indexTo = null;
    if (indexColumn >= 0) {
        if (first != null) {
            indexFrom = first.getValue(indexColumn);
        }
        if (last != null) {
            indexTo = last.getValue(indexColumn);
        }
    }
    ArrayList<Row> rows = New.arrayList();
    String catalog = identifier(database.getShortName());
    boolean admin = session.getUser().isAdmin();
    switch(type) {
        case TABLES:
            {
                for (Table table : getAllTables(session)) {
                    String tableName = identifier(table.getName());
                    if (!checkIndex(session, tableName, indexFrom, indexTo)) {
                        continue;
                    }
                    if (hideTable(table, session)) {
                        continue;
                    }
                    String storageType;
                    if (table.isTemporary()) {
                        if (table.isGlobalTemporary()) {
                            storageType = "GLOBAL TEMPORARY";
                        } else {
                            storageType = "LOCAL TEMPORARY";
                        }
                    } else {
                        storageType = table.isPersistIndexes() ? "CACHED" : "MEMORY";
                    }
                    String sql = table.getCreateSQL();
                    if (!admin) {
                        if (sql != null && sql.contains(JdbcSQLException.HIDE_SQL)) {
                            // hide the password of linked tables
                            sql = "-";
                        }
                    }
                    add(rows, // TABLE_CATALOG
                    catalog, // TABLE_SCHEMA
                    identifier(table.getSchema().getName()), // TABLE_NAME
                    tableName, // TABLE_TYPE
                    table.getTableType().toString(), // STORAGE_TYPE
                    storageType, // SQL
                    sql, // REMARKS
                    replaceNullWithEmpty(table.getComment()), // LAST_MODIFICATION
                    "" + table.getMaxDataModificationId(), // ID
                    "" + table.getId(), // TYPE_NAME
                    null, // TABLE_CLASS
                    table.getClass().getName(), // ROW_COUNT_ESTIMATE
                    "" + table.getRowCountApproximation());
                }
                break;
            }
        case COLUMNS:
            {
                // reduce the number of tables to scan - makes some metadata queries
                // 10x faster
                final ArrayList<Table> tablesToList;
                if (indexFrom != null && indexFrom.equals(indexTo)) {
                    String tableName = identifier(indexFrom.getString());
                    tablesToList = getTablesByName(session, tableName);
                } else {
                    tablesToList = getAllTables(session);
                }
                for (Table table : tablesToList) {
                    String tableName = identifier(table.getName());
                    if (!checkIndex(session, tableName, indexFrom, indexTo)) {
                        continue;
                    }
                    if (hideTable(table, session)) {
                        continue;
                    }
                    Column[] cols = table.getColumns();
                    String collation = database.getCompareMode().getName();
                    for (int j = 0; j < cols.length; j++) {
                        Column c = cols[j];
                        Sequence sequence = c.getSequence();
                        add(rows, // TABLE_CATALOG
                        catalog, // TABLE_SCHEMA
                        identifier(table.getSchema().getName()), // TABLE_NAME
                        tableName, // COLUMN_NAME
                        identifier(c.getName()), // ORDINAL_POSITION
                        String.valueOf(j + 1), // COLUMN_DEFAULT
                        c.getDefaultSQL(), // IS_NULLABLE
                        c.isNullable() ? "YES" : "NO", // DATA_TYPE
                        "" + DataType.convertTypeToSQLType(c.getType()), // CHARACTER_MAXIMUM_LENGTH
                        "" + c.getPrecisionAsInt(), // CHARACTER_OCTET_LENGTH
                        "" + c.getPrecisionAsInt(), // NUMERIC_PRECISION
                        "" + c.getPrecisionAsInt(), // NUMERIC_PRECISION_RADIX
                        "10", // NUMERIC_SCALE
                        "" + c.getScale(), // CHARACTER_SET_NAME
                        CHARACTER_SET_NAME, // COLLATION_NAME
                        collation, // TYPE_NAME
                        identifier(DataType.getDataType(c.getType()).name), // NULLABLE
                        "" + (c.isNullable() ? DatabaseMetaData.columnNullable : DatabaseMetaData.columnNoNulls), // IS_COMPUTED
                        "" + (c.getComputed() ? "TRUE" : "FALSE"), // SELECTIVITY
                        "" + (c.getSelectivity()), // CHECK_CONSTRAINT
                        c.getCheckConstraintSQL(session, c.getName()), // SEQUENCE_NAME
                        sequence == null ? null : sequence.getName(), // REMARKS
                        replaceNullWithEmpty(c.getComment()), // SOURCE_DATA_TYPE
                        null, // COLUMN_TYPE
                        c.getCreateSQLWithoutName(), // COLUMN_ON_UPDATE
                        c.getOnUpdateSQL());
                    }
                }
                break;
            }
        case INDEXES:
            {
                // reduce the number of tables to scan - makes some metadata queries
                // 10x faster
                final ArrayList<Table> tablesToList;
                if (indexFrom != null && indexFrom.equals(indexTo)) {
                    String tableName = identifier(indexFrom.getString());
                    tablesToList = getTablesByName(session, tableName);
                } else {
                    tablesToList = getAllTables(session);
                }
                for (Table table : tablesToList) {
                    String tableName = identifier(table.getName());
                    if (!checkIndex(session, tableName, indexFrom, indexTo)) {
                        continue;
                    }
                    if (hideTable(table, session)) {
                        continue;
                    }
                    ArrayList<Index> indexes = table.getIndexes();
                    ArrayList<Constraint> constraints = table.getConstraints();
                    for (int j = 0; indexes != null && j < indexes.size(); j++) {
                        Index index = indexes.get(j);
                        if (index.getCreateSQL() == null) {
                            continue;
                        }
                        String constraintName = null;
                        for (int k = 0; constraints != null && k < constraints.size(); k++) {
                            Constraint constraint = constraints.get(k);
                            if (constraint.usesIndex(index)) {
                                if (index.getIndexType().isPrimaryKey()) {
                                    if (constraint.getConstraintType() == Constraint.Type.PRIMARY_KEY) {
                                        constraintName = constraint.getName();
                                    }
                                } else {
                                    constraintName = constraint.getName();
                                }
                            }
                        }
                        IndexColumn[] cols = index.getIndexColumns();
                        String indexClass;
                        if (index instanceof MultiVersionIndex) {
                            indexClass = ((MultiVersionIndex) index).getBaseIndex().getClass().getName();
                        } else {
                            indexClass = index.getClass().getName();
                        }
                        for (int k = 0; k < cols.length; k++) {
                            IndexColumn idxCol = cols[k];
                            Column column = idxCol.column;
                            add(rows, // TABLE_CATALOG
                            catalog, // TABLE_SCHEMA
                            identifier(table.getSchema().getName()), // TABLE_NAME
                            tableName, // NON_UNIQUE
                            index.getIndexType().isUnique() ? "FALSE" : "TRUE", // INDEX_NAME
                            identifier(index.getName()), // ORDINAL_POSITION
                            "" + (k + 1), // COLUMN_NAME
                            identifier(column.getName()), // CARDINALITY
                            "0", // PRIMARY_KEY
                            index.getIndexType().isPrimaryKey() ? "TRUE" : "FALSE", // INDEX_TYPE_NAME
                            index.getIndexType().getSQL(), // IS_GENERATED
                            index.getIndexType().getBelongsToConstraint() ? "TRUE" : "FALSE", // INDEX_TYPE
                            "" + DatabaseMetaData.tableIndexOther, // ASC_OR_DESC
                            (idxCol.sortType & SortOrder.DESCENDING) != 0 ? "D" : "A", // PAGES
                            "0", // FILTER_CONDITION
                            "", // REMARKS
                            replaceNullWithEmpty(index.getComment()), // SQL
                            index.getCreateSQL(), // ID
                            "" + index.getId(), // SORT_TYPE
                            "" + idxCol.sortType, // CONSTRAINT_NAME
                            constraintName, // INDEX_CLASS
                            indexClass, // AFFINITY
                            index.getIndexType().isAffinity() ? "TRUE" : "FALSE");
                        }
                    }
                }
                break;
            }
        case TABLE_TYPES:
            {
                add(rows, TableType.TABLE.toString());
                add(rows, TableType.TABLE_LINK.toString());
                add(rows, TableType.SYSTEM_TABLE.toString());
                add(rows, TableType.VIEW.toString());
                add(rows, TableType.EXTERNAL_TABLE_ENGINE.toString());
                break;
            }
        case CATALOGS:
            {
                add(rows, catalog);
                break;
            }
        case SETTINGS:
            {
                for (Setting s : database.getAllSettings()) {
                    String value = s.getStringValue();
                    if (value == null) {
                        value = "" + s.getIntValue();
                    }
                    add(rows, identifier(s.getName()), value);
                }
                add(rows, "info.BUILD_ID", "" + Constants.BUILD_ID);
                add(rows, "info.VERSION_MAJOR", "" + Constants.VERSION_MAJOR);
                add(rows, "info.VERSION_MINOR", "" + Constants.VERSION_MINOR);
                add(rows, "info.VERSION", "" + Constants.getFullVersion());
                if (admin) {
                    String[] settings = { "java.runtime.version", "java.vm.name", "java.vendor", "os.name", "os.arch", "os.version", "sun.os.patch.level", "file.separator", "path.separator", "line.separator", "user.country", "user.language", "user.variant", "file.encoding" };
                    for (String s : settings) {
                        add(rows, "property." + s, Utils.getProperty(s, ""));
                    }
                }
                add(rows, "EXCLUSIVE", database.getExclusiveSession() == null ? "FALSE" : "TRUE");
                add(rows, "MODE", database.getMode().getName());
                add(rows, "MULTI_THREADED", database.isMultiThreaded() ? "1" : "0");
                add(rows, "MVCC", database.isMultiVersion() ? "TRUE" : "FALSE");
                add(rows, "QUERY_TIMEOUT", "" + session.getQueryTimeout());
                add(rows, "RETENTION_TIME", "" + database.getRetentionTime());
                add(rows, "LOG", "" + database.getLogMode());
                // database settings
                ArrayList<String> settingNames = New.arrayList();
                HashMap<String, String> s = database.getSettings().getSettings();
                settingNames.addAll(s.keySet());
                Collections.sort(settingNames);
                for (String k : settingNames) {
                    add(rows, k, s.get(k));
                }
                if (database.isPersistent()) {
                    PageStore store = database.getPageStore();
                    if (store != null) {
                        add(rows, "info.FILE_WRITE_TOTAL", "" + store.getWriteCountTotal());
                        add(rows, "info.FILE_WRITE", "" + store.getWriteCount());
                        add(rows, "info.FILE_READ", "" + store.getReadCount());
                        add(rows, "info.PAGE_COUNT", "" + store.getPageCount());
                        add(rows, "info.PAGE_SIZE", "" + store.getPageSize());
                        add(rows, "info.CACHE_MAX_SIZE", "" + store.getCache().getMaxMemory());
                        add(rows, "info.CACHE_SIZE", "" + store.getCache().getMemory());
                    }
                    Store mvStore = database.getMvStore();
                    if (mvStore != null) {
                        FileStore fs = mvStore.getStore().getFileStore();
                        add(rows, "info.FILE_WRITE", "" + fs.getWriteCount());
                        add(rows, "info.FILE_READ", "" + fs.getReadCount());
                        long size;
                        try {
                            size = fs.getFile().size();
                        } catch (IOException e) {
                            throw DbException.convertIOException(e, "Can not get size");
                        }
                        int pageSize = 4 * 1024;
                        long pageCount = size / pageSize;
                        add(rows, "info.PAGE_COUNT", "" + pageCount);
                        add(rows, "info.PAGE_SIZE", "" + pageSize);
                        add(rows, "info.CACHE_MAX_SIZE", "" + mvStore.getStore().getCacheSize());
                        add(rows, "info.CACHE_SIZE", "" + mvStore.getStore().getCacheSizeUsed());
                    }
                }
                break;
            }
        case TYPE_INFO:
            {
                for (DataType t : DataType.getTypes()) {
                    if (t.hidden || t.sqlType == Value.NULL) {
                        continue;
                    }
                    add(rows, // TYPE_NAME
                    t.name, // DATA_TYPE
                    String.valueOf(t.sqlType), // PRECISION
                    String.valueOf(MathUtils.convertLongToInt(t.maxPrecision)), // PREFIX
                    t.prefix, // SUFFIX
                    t.suffix, // PARAMS
                    t.params, // AUTO_INCREMENT
                    String.valueOf(t.autoIncrement), // MINIMUM_SCALE
                    String.valueOf(t.minScale), // MAXIMUM_SCALE
                    String.valueOf(t.maxScale), // RADIX
                    t.decimal ? "10" : null, // POS
                    String.valueOf(t.sqlTypePos), // CASE_SENSITIVE
                    String.valueOf(t.caseSensitive), // NULLABLE
                    "" + DatabaseMetaData.typeNullable, // SEARCHABLE
                    "" + DatabaseMetaData.typeSearchable);
                }
                break;
            }
        case HELP:
            {
                String resource = "/org/h2/res/help.csv";
                try {
                    byte[] data = Utils.getResource(resource);
                    Reader reader = new InputStreamReader(new ByteArrayInputStream(data));
                    Csv csv = new Csv();
                    csv.setLineCommentCharacter('#');
                    ResultSet rs = csv.read(reader, null);
                    for (int i = 0; rs.next(); i++) {
                        add(rows, // ID
                        String.valueOf(i), // SECTION
                        rs.getString(1).trim(), // TOPIC
                        rs.getString(2).trim(), // SYNTAX
                        rs.getString(3).trim(), // TEXT
                        rs.getString(4).trim());
                    }
                } catch (Exception e) {
                    throw DbException.convert(e);
                }
                break;
            }
        case SEQUENCES:
            {
                for (SchemaObject obj : database.getAllSchemaObjects(DbObject.SEQUENCE)) {
                    Sequence s = (Sequence) obj;
                    add(rows, // SEQUENCE_CATALOG
                    catalog, // SEQUENCE_SCHEMA
                    identifier(s.getSchema().getName()), // SEQUENCE_NAME
                    identifier(s.getName()), // CURRENT_VALUE
                    String.valueOf(s.getCurrentValue()), // INCREMENT
                    String.valueOf(s.getIncrement()), // IS_GENERATED
                    s.getBelongsToTable() ? "TRUE" : "FALSE", // REMARKS
                    replaceNullWithEmpty(s.getComment()), // CACHE
                    String.valueOf(s.getCacheSize()), // MIN_VALUE
                    String.valueOf(s.getMinValue()), // MAX_VALUE
                    String.valueOf(s.getMaxValue()), // IS_CYCLE
                    s.getCycle() ? "TRUE" : "FALSE", // ID
                    "" + s.getId());
                }
                break;
            }
        case USERS:
            {
                for (User u : database.getAllUsers()) {
                    if (admin || session.getUser() == u) {
                        add(rows, // NAME
                        identifier(u.getName()), // ADMIN
                        String.valueOf(u.isAdmin()), // REMARKS
                        replaceNullWithEmpty(u.getComment()), // ID
                        "" + u.getId());
                    }
                }
                break;
            }
        case ROLES:
            {
                for (Role r : database.getAllRoles()) {
                    if (admin || session.getUser().isRoleGranted(r)) {
                        add(rows, // NAME
                        identifier(r.getName()), // REMARKS
                        replaceNullWithEmpty(r.getComment()), // ID
                        "" + r.getId());
                    }
                }
                break;
            }
        case RIGHTS:
            {
                if (admin) {
                    for (Right r : database.getAllRights()) {
                        Role role = r.getGrantedRole();
                        DbObject grantee = r.getGrantee();
                        String rightType = grantee.getType() == DbObject.USER ? "USER" : "ROLE";
                        if (role == null) {
                            DbObject object = r.getGrantedObject();
                            Schema schema = null;
                            Table table = null;
                            if (object != null) {
                                if (object instanceof Schema) {
                                    schema = (Schema) object;
                                } else if (object instanceof Table) {
                                    table = (Table) object;
                                    schema = table.getSchema();
                                }
                            }
                            String tableName = (table != null) ? identifier(table.getName()) : "";
                            String schemaName = (schema != null) ? identifier(schema.getName()) : "";
                            if (!checkIndex(session, tableName, indexFrom, indexTo)) {
                                continue;
                            }
                            add(rows, // GRANTEE
                            identifier(grantee.getName()), // GRANTEETYPE
                            rightType, // GRANTEDROLE
                            "", // RIGHTS
                            r.getRights(), // TABLE_SCHEMA
                            schemaName, // TABLE_NAME
                            tableName, // ID
                            "" + r.getId());
                        } else {
                            add(rows, // GRANTEE
                            identifier(grantee.getName()), // GRANTEETYPE
                            rightType, // GRANTEDROLE
                            identifier(role.getName()), // RIGHTS
                            "", // TABLE_SCHEMA
                            "", // TABLE_NAME
                            "", // ID
                            "" + r.getId());
                        }
                    }
                }
                break;
            }
        case FUNCTION_ALIASES:
            {
                for (SchemaObject aliasAsSchemaObject : database.getAllSchemaObjects(DbObject.FUNCTION_ALIAS)) {
                    FunctionAlias alias = (FunctionAlias) aliasAsSchemaObject;
                    JavaMethod[] methods;
                    try {
                        methods = alias.getJavaMethods();
                    } catch (DbException e) {
                        methods = new JavaMethod[0];
                    }
                    for (FunctionAlias.JavaMethod method : methods) {
                        int returnsResult = method.getDataType() == Value.NULL ? DatabaseMetaData.procedureNoResult : DatabaseMetaData.procedureReturnsResult;
                        add(rows, // ALIAS_CATALOG
                        catalog, // ALIAS_SCHEMA
                        alias.getSchema().getName(), // ALIAS_NAME
                        identifier(alias.getName()), // JAVA_CLASS
                        alias.getJavaClassName(), // JAVA_METHOD
                        alias.getJavaMethodName(), // DATA_TYPE
                        "" + DataType.convertTypeToSQLType(method.getDataType()), // TYPE_NAME
                        DataType.getDataType(method.getDataType()).name, // COLUMN_COUNT INT
                        "" + method.getParameterCount(), // RETURNS_RESULT SMALLINT
                        "" + returnsResult, // REMARKS
                        replaceNullWithEmpty(alias.getComment()), // ID
                        "" + alias.getId(), // SOURCE
                        alias.getSource());
                    }
                }
                for (UserAggregate agg : database.getAllAggregates()) {
                    int returnsResult = DatabaseMetaData.procedureReturnsResult;
                    add(rows, // ALIAS_CATALOG
                    catalog, // ALIAS_SCHEMA
                    Constants.SCHEMA_MAIN, // ALIAS_NAME
                    identifier(agg.getName()), // JAVA_CLASS
                    agg.getJavaClassName(), // JAVA_METHOD
                    "", // DATA_TYPE
                    "" + DataType.convertTypeToSQLType(Value.NULL), // TYPE_NAME
                    DataType.getDataType(Value.NULL).name, // COLUMN_COUNT INT
                    "1", // RETURNS_RESULT SMALLINT
                    "" + returnsResult, // REMARKS
                    replaceNullWithEmpty(agg.getComment()), // ID
                    "" + agg.getId(), // SOURCE
                    "");
                }
                break;
            }
        case FUNCTION_COLUMNS:
            {
                for (SchemaObject aliasAsSchemaObject : database.getAllSchemaObjects(DbObject.FUNCTION_ALIAS)) {
                    FunctionAlias alias = (FunctionAlias) aliasAsSchemaObject;
                    JavaMethod[] methods;
                    try {
                        methods = alias.getJavaMethods();
                    } catch (DbException e) {
                        methods = new JavaMethod[0];
                    }
                    for (FunctionAlias.JavaMethod method : methods) {
                        // Add return column index 0
                        if (method.getDataType() != Value.NULL) {
                            DataType dt = DataType.getDataType(method.getDataType());
                            add(rows, // ALIAS_CATALOG
                            catalog, // ALIAS_SCHEMA
                            alias.getSchema().getName(), // ALIAS_NAME
                            identifier(alias.getName()), // JAVA_CLASS
                            alias.getJavaClassName(), // JAVA_METHOD
                            alias.getJavaMethodName(), // COLUMN_COUNT
                            "" + method.getParameterCount(), // POS INT
                            "0", // COLUMN_NAME
                            "P0", // DATA_TYPE
                            "" + DataType.convertTypeToSQLType(method.getDataType()), // TYPE_NAME
                            dt.name, // PRECISION INT
                            "" + MathUtils.convertLongToInt(dt.defaultPrecision), // SCALE
                            "" + dt.defaultScale, // RADIX
                            "10", // NULLABLE SMALLINT
                            "" + DatabaseMetaData.columnNullableUnknown, // COLUMN_TYPE
                            "" + DatabaseMetaData.procedureColumnReturn, // REMARKS
                            "", // COLUMN_DEFAULT
                            null);
                        }
                        Class<?>[] columnList = method.getColumnClasses();
                        for (int k = 0; k < columnList.length; k++) {
                            if (method.hasConnectionParam() && k == 0) {
                                continue;
                            }
                            Class<?> clazz = columnList[k];
                            int dataType = DataType.getTypeFromClass(clazz);
                            DataType dt = DataType.getDataType(dataType);
                            int nullable = clazz.isPrimitive() ? DatabaseMetaData.columnNoNulls : DatabaseMetaData.columnNullable;
                            add(rows, // ALIAS_CATALOG
                            catalog, // ALIAS_SCHEMA
                            alias.getSchema().getName(), // ALIAS_NAME
                            identifier(alias.getName()), // JAVA_CLASS
                            alias.getJavaClassName(), // JAVA_METHOD
                            alias.getJavaMethodName(), // COLUMN_COUNT
                            "" + method.getParameterCount(), // POS INT
                            "" + (k + (method.hasConnectionParam() ? 0 : 1)), // COLUMN_NAME
                            "P" + (k + 1), // DATA_TYPE
                            "" + DataType.convertTypeToSQLType(dt.type), // TYPE_NAME
                            dt.name, // PRECISION INT
                            "" + MathUtils.convertLongToInt(dt.defaultPrecision), // SCALE
                            "" + dt.defaultScale, // RADIX
                            "10", // NULLABLE SMALLINT
                            "" + nullable, // COLUMN_TYPE
                            "" + DatabaseMetaData.procedureColumnIn, // REMARKS
                            "", // COLUMN_DEFAULT
                            null);
                        }
                    }
                }
                break;
            }
        case SCHEMATA:
            {
                String collation = database.getCompareMode().getName();
                for (Schema schema : database.getAllSchemas()) {
                    add(rows, // CATALOG_NAME
                    catalog, // SCHEMA_NAME
                    identifier(schema.getName()), // SCHEMA_OWNER
                    identifier(schema.getOwner().getName()), // DEFAULT_CHARACTER_SET_NAME
                    CHARACTER_SET_NAME, // DEFAULT_COLLATION_NAME
                    collation, // IS_DEFAULT
                    Constants.SCHEMA_MAIN.equals(schema.getName()) ? "TRUE" : "FALSE", // REMARKS
                    replaceNullWithEmpty(schema.getComment()), // ID
                    "" + schema.getId());
                }
                break;
            }
        case TABLE_PRIVILEGES:
            {
                for (Right r : database.getAllRights()) {
                    DbObject object = r.getGrantedObject();
                    if (!(object instanceof Table)) {
                        continue;
                    }
                    Table table = (Table) object;
                    if (hideTable(table, session)) {
                        continue;
                    }
                    String tableName = identifier(table.getName());
                    if (!checkIndex(session, tableName, indexFrom, indexTo)) {
                        continue;
                    }
                    addPrivileges(rows, r.getGrantee(), catalog, table, null, r.getRightMask());
                }
                break;
            }
        case COLUMN_PRIVILEGES:
            {
                for (Right r : database.getAllRights()) {
                    DbObject object = r.getGrantedObject();
                    if (!(object instanceof Table)) {
                        continue;
                    }
                    Table table = (Table) object;
                    if (hideTable(table, session)) {
                        continue;
                    }
                    String tableName = identifier(table.getName());
                    if (!checkIndex(session, tableName, indexFrom, indexTo)) {
                        continue;
                    }
                    DbObject grantee = r.getGrantee();
                    int mask = r.getRightMask();
                    for (Column column : table.getColumns()) {
                        addPrivileges(rows, grantee, catalog, table, column.getName(), mask);
                    }
                }
                break;
            }
        case COLLATIONS:
            {
                for (Locale l : Collator.getAvailableLocales()) {
                    add(rows, // NAME
                    CompareMode.getName(l), // KEY
                    l.toString());
                }
                break;
            }
        case VIEWS:
            {
                for (Table table : getAllTables(session)) {
                    if (table.getTableType() != TableType.VIEW) {
                        continue;
                    }
                    String tableName = identifier(table.getName());
                    if (!checkIndex(session, tableName, indexFrom, indexTo)) {
                        continue;
                    }
                    TableView view = (TableView) table;
                    add(rows, // TABLE_CATALOG
                    catalog, // TABLE_SCHEMA
                    identifier(table.getSchema().getName()), // TABLE_NAME
                    tableName, // VIEW_DEFINITION
                    table.getCreateSQL(), // CHECK_OPTION
                    "NONE", // IS_UPDATABLE
                    "NO", // STATUS
                    view.isInvalid() ? "INVALID" : "VALID", // REMARKS
                    replaceNullWithEmpty(view.getComment()), // ID
                    "" + view.getId());
                }
                break;
            }
        case IN_DOUBT:
            {
                ArrayList<InDoubtTransaction> prepared = database.getInDoubtTransactions();
                if (prepared != null && admin) {
                    for (InDoubtTransaction prep : prepared) {
                        add(rows, // TRANSACTION
                        prep.getTransactionName(), // STATE
                        prep.getState());
                    }
                }
                break;
            }
        case CROSS_REFERENCES:
            {
                for (SchemaObject obj : database.getAllSchemaObjects(DbObject.CONSTRAINT)) {
                    Constraint constraint = (Constraint) obj;
                    if (constraint.getConstraintType() != Constraint.Type.REFERENTIAL) {
                        continue;
                    }
                    ConstraintReferential ref = (ConstraintReferential) constraint;
                    IndexColumn[] cols = ref.getColumns();
                    IndexColumn[] refCols = ref.getRefColumns();
                    Table tab = ref.getTable();
                    Table refTab = ref.getRefTable();
                    String tableName = identifier(refTab.getName());
                    if (!checkIndex(session, tableName, indexFrom, indexTo)) {
                        continue;
                    }
                    int update = getRefAction(ref.getUpdateAction());
                    int delete = getRefAction(ref.getDeleteAction());
                    for (int j = 0; j < cols.length; j++) {
                        add(rows, // PKTABLE_CATALOG
                        catalog, // PKTABLE_SCHEMA
                        identifier(refTab.getSchema().getName()), // PKTABLE_NAME
                        identifier(refTab.getName()), // PKCOLUMN_NAME
                        identifier(refCols[j].column.getName()), // FKTABLE_CATALOG
                        catalog, // FKTABLE_SCHEMA
                        identifier(tab.getSchema().getName()), // FKTABLE_NAME
                        identifier(tab.getName()), // FKCOLUMN_NAME
                        identifier(cols[j].column.getName()), // ORDINAL_POSITION
                        String.valueOf(j + 1), // UPDATE_RULE SMALLINT
                        String.valueOf(update), // DELETE_RULE SMALLINT
                        String.valueOf(delete), // FK_NAME
                        identifier(ref.getName()), // PK_NAME
                        identifier(ref.getUniqueIndex().getName()), // DEFERRABILITY
                        "" + DatabaseMetaData.importedKeyNotDeferrable);
                    }
                }
                break;
            }
        case CONSTRAINTS:
            {
                for (SchemaObject obj : database.getAllSchemaObjects(DbObject.CONSTRAINT)) {
                    Constraint constraint = (Constraint) obj;
                    Constraint.Type constraintType = constraint.getConstraintType();
                    String checkExpression = null;
                    IndexColumn[] indexColumns = null;
                    Table table = constraint.getTable();
                    if (hideTable(table, session)) {
                        continue;
                    }
                    Index index = constraint.getUniqueIndex();
                    String uniqueIndexName = null;
                    if (index != null) {
                        uniqueIndexName = index.getName();
                    }
                    String tableName = identifier(table.getName());
                    if (!checkIndex(session, tableName, indexFrom, indexTo)) {
                        continue;
                    }
                    if (constraintType == Constraint.Type.CHECK) {
                        checkExpression = ((ConstraintCheck) constraint).getExpression().getSQL();
                    } else if (constraintType == Constraint.Type.UNIQUE || constraintType == Constraint.Type.PRIMARY_KEY) {
                        indexColumns = ((ConstraintUnique) constraint).getColumns();
                    } else if (constraintType == Constraint.Type.REFERENTIAL) {
                        indexColumns = ((ConstraintReferential) constraint).getColumns();
                    }
                    String columnList = null;
                    if (indexColumns != null) {
                        StatementBuilder buff = new StatementBuilder();
                        for (IndexColumn col : indexColumns) {
                            buff.appendExceptFirst(",");
                            buff.append(col.column.getName());
                        }
                        columnList = buff.toString();
                    }
                    add(rows, // CONSTRAINT_CATALOG
                    catalog, // CONSTRAINT_SCHEMA
                    identifier(constraint.getSchema().getName()), // CONSTRAINT_NAME
                    identifier(constraint.getName()), // CONSTRAINT_TYPE
                    constraintType == Constraint.Type.PRIMARY_KEY ? constraintType.getSqlName() : constraintType.name(), // TABLE_CATALOG
                    catalog, // TABLE_SCHEMA
                    identifier(table.getSchema().getName()), // TABLE_NAME
                    tableName, // UNIQUE_INDEX_NAME
                    uniqueIndexName, // CHECK_EXPRESSION
                    checkExpression, // COLUMN_LIST
                    columnList, // REMARKS
                    replaceNullWithEmpty(constraint.getComment()), // SQL
                    constraint.getCreateSQL(), // ID
                    "" + constraint.getId());
                }
                break;
            }
        case CONSTANTS:
            {
                for (SchemaObject obj : database.getAllSchemaObjects(DbObject.CONSTANT)) {
                    Constant constant = (Constant) obj;
                    ValueExpression expr = constant.getValue();
                    add(rows, // CONSTANT_CATALOG
                    catalog, // CONSTANT_SCHEMA
                    identifier(constant.getSchema().getName()), // CONSTANT_NAME
                    identifier(constant.getName()), // CONSTANT_TYPE
                    "" + DataType.convertTypeToSQLType(expr.getType()), // REMARKS
                    replaceNullWithEmpty(constant.getComment()), // SQL
                    expr.getSQL(), // ID
                    "" + constant.getId());
                }
                break;
            }
        case DOMAINS:
            {
                for (UserDataType dt : database.getAllUserDataTypes()) {
                    Column col = dt.getColumn();
                    add(rows, // DOMAIN_CATALOG
                    catalog, // DOMAIN_SCHEMA
                    Constants.SCHEMA_MAIN, // DOMAIN_NAME
                    identifier(dt.getName()), // COLUMN_DEFAULT
                    col.getDefaultSQL(), // IS_NULLABLE
                    col.isNullable() ? "YES" : "NO", // DATA_TYPE
                    "" + col.getDataType().sqlType, // PRECISION INT
                    "" + col.getPrecisionAsInt(), // SCALE INT
                    "" + col.getScale(), // TYPE_NAME
                    col.getDataType().name, // SELECTIVITY INT
                    "" + col.getSelectivity(), // CHECK_CONSTRAINT
                    "" + col.getCheckConstraintSQL(session, "VALUE"), // REMARKS
                    replaceNullWithEmpty(dt.getComment()), // SQL
                    "" + dt.getCreateSQL(), // ID
                    "" + dt.getId());
                }
                break;
            }
        case TRIGGERS:
            {
                for (SchemaObject obj : database.getAllSchemaObjects(DbObject.TRIGGER)) {
                    TriggerObject trigger = (TriggerObject) obj;
                    Table table = trigger.getTable();
                    add(rows, // TRIGGER_CATALOG
                    catalog, // TRIGGER_SCHEMA
                    identifier(trigger.getSchema().getName()), // TRIGGER_NAME
                    identifier(trigger.getName()), // TRIGGER_TYPE
                    trigger.getTypeNameList(), // TABLE_CATALOG
                    catalog, // TABLE_SCHEMA
                    identifier(table.getSchema().getName()), // TABLE_NAME
                    identifier(table.getName()), // BEFORE BIT
                    "" + trigger.isBefore(), // JAVA_CLASS
                    trigger.getTriggerClassName(), // QUEUE_SIZE INT
                    "" + trigger.getQueueSize(), // NO_WAIT BIT
                    "" + trigger.isNoWait(), // REMARKS
                    replaceNullWithEmpty(trigger.getComment()), // SQL
                    trigger.getCreateSQL(), // ID
                    "" + trigger.getId());
                }
                break;
            }
        case SESSIONS:
            {
                long now = System.currentTimeMillis();
                for (Session s : database.getSessions(false)) {
                    if (admin || s == session) {
                        Command command = s.getCurrentCommand();
                        long start = s.getCurrentCommandStart();
                        if (start == 0) {
                            start = now;
                        }
                        add(rows, // ID
                        "" + s.getId(), // USER_NAME
                        s.getUser().getName(), // SESSION_START
                        new Timestamp(s.getSessionStart()).toString(), // STATEMENT
                        command == null ? null : command.toString(), // STATEMENT_START
                        new Timestamp(start).toString(), // CONTAINS_UNCOMMITTED
                        "" + s.containsUncommitted());
                    }
                }
                break;
            }
        case LOCKS:
            {
                for (Session s : database.getSessions(false)) {
                    if (admin || s == session) {
                        for (Table table : s.getLocks()) {
                            add(rows, // TABLE_SCHEMA
                            table.getSchema().getName(), // TABLE_NAME
                            table.getName(), // SESSION_ID
                            "" + s.getId(), // LOCK_TYPE
                            table.isLockedExclusivelyBy(s) ? "WRITE" : "READ");
                        }
                    }
                }
                break;
            }
        case SESSION_STATE:
            {
                for (String name : session.getVariableNames()) {
                    Value v = session.getVariable(name);
                    add(rows, // KEY
                    "@" + name, // SQL
                    "SET @" + name + " " + v.getSQL());
                }
                for (Table table : session.getLocalTempTables()) {
                    add(rows, // KEY
                    "TABLE " + table.getName(), // SQL
                    table.getCreateSQL());
                }
                String[] path = session.getSchemaSearchPath();
                if (path != null && path.length > 0) {
                    StatementBuilder buff = new StatementBuilder("SET SCHEMA_SEARCH_PATH ");
                    for (String p : path) {
                        buff.appendExceptFirst(", ");
                        buff.append(StringUtils.quoteIdentifier(p));
                    }
                    add(rows, // KEY
                    "SCHEMA_SEARCH_PATH", // SQL
                    buff.toString());
                }
                String schema = session.getCurrentSchemaName();
                if (schema != null) {
                    add(rows, // KEY
                    "SCHEMA", // SQL
                    "SET SCHEMA " + StringUtils.quoteIdentifier(schema));
                }
                break;
            }
        case QUERY_STATISTICS:
            {
                QueryStatisticsData control = database.getQueryStatisticsData();
                if (control != null) {
                    for (QueryStatisticsData.QueryEntry entry : control.getQueries()) {
                        add(rows, // SQL_STATEMENT
                        entry.sqlStatement, // EXECUTION_COUNT
                        "" + entry.count, // MIN_EXECUTION_TIME
                        "" + entry.executionTimeMinNanos / 1000d / 1000, // MAX_EXECUTION_TIME
                        "" + entry.executionTimeMaxNanos / 1000d / 1000, // CUMULATIVE_EXECUTION_TIME
                        "" + entry.executionTimeCumulativeNanos / 1000d / 1000, // AVERAGE_EXECUTION_TIME
                        "" + entry.executionTimeMeanNanos / 1000d / 1000, // STD_DEV_EXECUTION_TIME
                        "" + entry.getExecutionTimeStandardDeviation() / 1000d / 1000, // MIN_ROW_COUNT
                        "" + entry.rowCountMin, // MAX_ROW_COUNT
                        "" + entry.rowCountMax, // CUMULATIVE_ROW_COUNT
                        "" + entry.rowCountCumulative, // AVERAGE_ROW_COUNT
                        "" + entry.rowCountMean, // STD_DEV_ROW_COUNT
                        "" + entry.getRowCountStandardDeviation());
                    }
                }
                break;
            }
        case SYNONYMS:
            {
                for (TableSynonym synonym : database.getAllSynonyms()) {
                    add(rows, // SYNONYM_CATALOG
                    catalog, // SYNONYM_SCHEMA
                    identifier(synonym.getSchema().getName()), // SYNONYM_NAME
                    identifier(synonym.getName()), // SYNONYM_FOR
                    synonym.getSynonymForName(), // SYNONYM_FOR_SCHEMA
                    synonym.getSynonymForSchema().getName(), // TYPE NAME
                    "SYNONYM", // STATUS
                    "VALID", // REMARKS
                    replaceNullWithEmpty(synonym.getComment()), // ID
                    "" + synonym.getId());
                }
                break;
            }
        case TABLE_CONSTRAINTS:
            {
                for (SchemaObject obj : database.getAllSchemaObjects(DbObject.CONSTRAINT)) {
                    Constraint constraint = (Constraint) obj;
                    Constraint.Type constraintType = constraint.getConstraintType();
                    Table table = constraint.getTable();
                    if (hideTable(table, session)) {
                        continue;
                    }
                    String tableName = identifier(table.getName());
                    if (!checkIndex(session, tableName, indexFrom, indexTo)) {
                        continue;
                    }
                    add(rows, // CONSTRAINT_CATALOG
                    catalog, // CONSTRAINT_SCHEMA
                    identifier(constraint.getSchema().getName()), // CONSTRAINT_NAME
                    identifier(constraint.getName()), // CONSTRAINT_TYPE
                    constraintType.getSqlName(), // TABLE_CATALOG
                    catalog, // TABLE_SCHEMA
                    identifier(table.getSchema().getName()), // TABLE_NAME
                    tableName, // IS_DEFERRABLE
                    "NO", // INITIALLY_DEFERRED
                    "NO");
                }
                break;
            }
        case KEY_COLUMN_USAGE:
            {
                for (SchemaObject obj : database.getAllSchemaObjects(DbObject.CONSTRAINT)) {
                    Constraint constraint = (Constraint) obj;
                    Constraint.Type constraintType = constraint.getConstraintType();
                    IndexColumn[] indexColumns = null;
                    Table table = constraint.getTable();
                    if (hideTable(table, session)) {
                        continue;
                    }
                    String tableName = identifier(table.getName());
                    if (!checkIndex(session, tableName, indexFrom, indexTo)) {
                        continue;
                    }
                    if (constraintType == Constraint.Type.UNIQUE || constraintType == Constraint.Type.PRIMARY_KEY) {
                        indexColumns = ((ConstraintUnique) constraint).getColumns();
                    } else if (constraintType == Constraint.Type.REFERENTIAL) {
                        indexColumns = ((ConstraintReferential) constraint).getColumns();
                    }
                    if (indexColumns == null) {
                        continue;
                    }
                    ConstraintUnique referenced;
                    if (constraintType == Constraint.Type.REFERENTIAL) {
                        referenced = lookupUniqueForReferential((ConstraintReferential) constraint);
                    } else {
                        referenced = null;
                    }
                    for (int i = 0; i < indexColumns.length; i++) {
                        IndexColumn indexColumn = indexColumns[i];
                        String ordinalPosition = Integer.toString(i + 1);
                        String positionInUniqueConstraint;
                        if (constraintType == Constraint.Type.REFERENTIAL) {
                            positionInUniqueConstraint = ordinalPosition;
                            if (referenced != null) {
                                Column c = ((ConstraintReferential) constraint).getRefColumns()[i].column;
                                IndexColumn[] refColumns = referenced.getColumns();
                                for (int j = 0; j < refColumns.length; j++) {
                                    if (refColumns[j].column.equals(c)) {
                                        positionInUniqueConstraint = Integer.toString(j + 1);
                                        break;
                                    }
                                }
                            }
                        } else {
                            positionInUniqueConstraint = null;
                        }
                        add(rows, // CONSTRAINT_CATALOG
                        catalog, // CONSTRAINT_SCHEMA
                        identifier(constraint.getSchema().getName()), // CONSTRAINT_NAME
                        identifier(constraint.getName()), // TABLE_CATALOG
                        catalog, // TABLE_SCHEMA
                        identifier(table.getSchema().getName()), // TABLE_NAME
                        tableName, // COLUMN_NAME
                        indexColumn.columnName, // ORDINAL_POSITION
                        ordinalPosition, // POSITION_IN_UNIQUE_CONSTRAINT
                        positionInUniqueConstraint);
                    }
                }
                break;
            }
        case REFERENTIAL_CONSTRAINTS:
            {
                for (SchemaObject obj : database.getAllSchemaObjects(DbObject.CONSTRAINT)) {
                    if (((Constraint) obj).getConstraintType() != Constraint.Type.REFERENTIAL) {
                        continue;
                    }
                    ConstraintReferential constraint = (ConstraintReferential) obj;
                    Table table = constraint.getTable();
                    if (hideTable(table, session)) {
                        continue;
                    }
                    // Should be referenced unique constraint, but H2 uses indexes instead.
                    // So try to find matching unique constraint first and there is no such
                    // constraint use index name to return something.
                    SchemaObject unique = lookupUniqueForReferential(constraint);
                    if (unique == null) {
                        unique = constraint.getUniqueIndex();
                    }
                    add(rows, // CONSTRAINT_CATALOG
                    catalog, // CONSTRAINT_SCHEMA
                    identifier(constraint.getSchema().getName()), // CONSTRAINT_NAME
                    identifier(constraint.getName()), // UNIQUE_CONSTRAINT_CATALOG
                    catalog, // UNIQUE_CONSTRAINT_SCHEMA
                    identifier(unique.getSchema().getName()), // UNIQUE_CONSTRAINT_NAME
                    unique.getName(), // MATCH_OPTION
                    "NONE", // UPDATE_RULE
                    constraint.getUpdateAction().getSqlName(), // DELETE_RULE
                    constraint.getDeleteAction().getSqlName());
                }
                break;
            }
        default:
            DbException.throwInternalError("type=" + type);
    }
    return rows;
}

Example 29 with Delete

use of org.h2.command.dml.Delete in project h2database by h2database.

the class JdbcDatabaseMetaData method getCrossReference.

/**
 * Gets the list of foreign key columns that references a table, as well as
 * the list of primary key columns that are references by a table. The
 * result set is sorted by FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_NAME,
 * FK_NAME, KEY_SEQ.
 *
 * <ol>
 * <li>PKTABLE_CAT (String) primary catalog</li>
 * <li>PKTABLE_SCHEM (String) primary schema</li>
 * <li>PKTABLE_NAME (String) primary table</li>
 * <li>PKCOLUMN_NAME (String) primary column</li>
 * <li>FKTABLE_CAT (String) foreign catalog</li>
 * <li>FKTABLE_SCHEM (String) foreign schema</li>
 * <li>FKTABLE_NAME (String) foreign table</li>
 * <li>FKCOLUMN_NAME (String) foreign column</li>
 * <li>KEY_SEQ (short) sequence number (1,2,...)</li>
 * <li>UPDATE_RULE (short) action on update (see
 * DatabaseMetaData.importedKey...)</li>
 * <li>DELETE_RULE (short) action on delete (see
 * DatabaseMetaData.importedKey...)</li>
 * <li>FK_NAME (String) foreign key name</li>
 * <li>PK_NAME (String) primary key name</li>
 * <li>DEFERRABILITY (short) deferrable or not (always
 * importedKeyNotDeferrable)</li>
 * </ol>
 *
 * @param primaryCatalogPattern null or the catalog name
 * @param primarySchemaPattern the schema name of the primary table
 *          (optional)
 * @param primaryTable the name of the primary table (must be specified)
 * @param foreignCatalogPattern null or the catalog name
 * @param foreignSchemaPattern the schema name of the foreign table
 *          (optional)
 * @param foreignTable the name of the foreign table (must be specified)
 * @return the result set
 * @throws SQLException if the connection is closed
 */
@Override
public ResultSet getCrossReference(String primaryCatalogPattern, String primarySchemaPattern, String primaryTable, String foreignCatalogPattern, String foreignSchemaPattern, String foreignTable) throws SQLException {
    try {
        if (isDebugEnabled()) {
            debugCode("getCrossReference(" + quote(primaryCatalogPattern) + ", " + quote(primarySchemaPattern) + ", " + quote(primaryTable) + ", " + quote(foreignCatalogPattern) + ", " + quote(foreignSchemaPattern) + ", " + quote(foreignTable) + ");");
        }
        checkClosed();
        PreparedStatement prep = conn.prepareAutoCloseStatement("SELECT " + "PKTABLE_CATALOG PKTABLE_CAT, " + "PKTABLE_SCHEMA PKTABLE_SCHEM, " + "PKTABLE_NAME PKTABLE_NAME, " + "PKCOLUMN_NAME, " + "FKTABLE_CATALOG FKTABLE_CAT, " + "FKTABLE_SCHEMA FKTABLE_SCHEM, " + "FKTABLE_NAME, " + "FKCOLUMN_NAME, " + "ORDINAL_POSITION KEY_SEQ, " + "UPDATE_RULE, " + "DELETE_RULE, " + "FK_NAME, " + "PK_NAME, " + "DEFERRABILITY " + "FROM INFORMATION_SCHEMA.CROSS_REFERENCES " + "WHERE PKTABLE_CATALOG LIKE ? ESCAPE ? " + "AND PKTABLE_SCHEMA LIKE ? ESCAPE ? " + "AND PKTABLE_NAME = ? " + "AND FKTABLE_CATALOG LIKE ? ESCAPE ? " + "AND FKTABLE_SCHEMA LIKE ? ESCAPE ? " + "AND FKTABLE_NAME = ? " + "ORDER BY FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_NAME, FK_NAME, KEY_SEQ");
        prep.setString(1, getCatalogPattern(primaryCatalogPattern));
        prep.setString(2, "\\");
        prep.setString(3, getSchemaPattern(primarySchemaPattern));
        prep.setString(4, "\\");
        prep.setString(5, primaryTable);
        prep.setString(6, getCatalogPattern(foreignCatalogPattern));
        prep.setString(7, "\\");
        prep.setString(8, getSchemaPattern(foreignSchemaPattern));
        prep.setString(9, "\\");
        prep.setString(10, foreignTable);
        return prep.executeQuery();
    } catch (Exception e) {
        throw logAndConvert(e);
    }
}

Example 30 with Delete

use of org.h2.command.dml.Delete in project h2database by h2database.

the class JdbcDatabaseMetaData method getImportedKeys.

/**
 * Gets the list of primary key columns that are referenced by a table. The
 * result set is sorted by PKTABLE_CAT, PKTABLE_SCHEM, PKTABLE_NAME,
 * FK_NAME, KEY_SEQ.
 *
 * <ol>
 * <li>PKTABLE_CAT (String) primary catalog</li>
 * <li>PKTABLE_SCHEM (String) primary schema</li>
 * <li>PKTABLE_NAME (String) primary table</li>
 * <li>PKCOLUMN_NAME (String) primary column</li>
 * <li>FKTABLE_CAT (String) foreign catalog</li>
 * <li>FKTABLE_SCHEM (String) foreign schema</li>
 * <li>FKTABLE_NAME (String) foreign table</li>
 * <li>FKCOLUMN_NAME (String) foreign column</li>
 * <li>KEY_SEQ (short) sequence number (1, 2, ...)</li>
 * <li>UPDATE_RULE (short) action on update (see
 * DatabaseMetaData.importedKey...)</li>
 * <li>DELETE_RULE (short) action on delete (see
 * DatabaseMetaData.importedKey...)</li>
 * <li>FK_NAME (String) foreign key name</li>
 * <li>PK_NAME (String) primary key name</li>
 * <li>DEFERRABILITY (short) deferrable or not (always
 * importedKeyNotDeferrable)</li>
 * </ol>
 *
 * @param catalogPattern null (to get all objects) or the catalog name
 * @param schemaPattern the schema name of the foreign table
 * @param tableName the name of the foreign table
 * @return the result set
 * @throws SQLException if the connection is closed
 */
@Override
public ResultSet getImportedKeys(String catalogPattern, String schemaPattern, String tableName) throws SQLException {
    try {
        if (isDebugEnabled()) {
            debugCode("getImportedKeys(" + quote(catalogPattern) + ", " + quote(schemaPattern) + ", " + quote(tableName) + ");");
        }
        checkClosed();
        PreparedStatement prep = conn.prepareAutoCloseStatement("SELECT " + "PKTABLE_CATALOG PKTABLE_CAT, " + "PKTABLE_SCHEMA PKTABLE_SCHEM, " + "PKTABLE_NAME PKTABLE_NAME, " + "PKCOLUMN_NAME, " + "FKTABLE_CATALOG FKTABLE_CAT, " + "FKTABLE_SCHEMA FKTABLE_SCHEM, " + "FKTABLE_NAME, " + "FKCOLUMN_NAME, " + "ORDINAL_POSITION KEY_SEQ, " + "UPDATE_RULE, " + "DELETE_RULE, " + "FK_NAME, " + "PK_NAME, " + "DEFERRABILITY " + "FROM INFORMATION_SCHEMA.CROSS_REFERENCES " + "WHERE FKTABLE_CATALOG LIKE ? ESCAPE ? " + "AND FKTABLE_SCHEMA LIKE ? ESCAPE ? " + "AND FKTABLE_NAME = ? " + "ORDER BY PKTABLE_CAT, PKTABLE_SCHEM, PKTABLE_NAME, FK_NAME, KEY_SEQ");
        prep.setString(1, getCatalogPattern(catalogPattern));
        prep.setString(2, "\\");
        prep.setString(3, getSchemaPattern(schemaPattern));
        prep.setString(4, "\\");
        prep.setString(5, tableName);
        return prep.executeQuery();
    } catch (Exception e) {
        throw logAndConvert(e);
    }
}