Examples with Prepared org.h2.test.db.Db.Prepared used on opensource projects

Example 31 with Prepared

use of org.h2.test.db.Db.Prepared in project h2database by h2database.

the class ConstraintReferential method setWhere.

private void setWhere(Prepared command, int pos, Row row) {
    for (int i = 0, len = refColumns.length; i < len; i++) {
        int idx = refColumns[i].column.getColumnId();
        Value v = row.getValue(idx);
        ArrayList<Parameter> params = command.getParameters();
        Parameter param = params.get(pos + i);
        param.setValue(v);
    }
}

Example 32 with Prepared

use of org.h2.test.db.Db.Prepared in project h2database by h2database.

the class ConstraintReferential method prepare.

private Prepared prepare(Session session, String sql, ConstraintActionType action) {
    Prepared command = session.prepare(sql);
    if (action != ConstraintActionType.CASCADE) {
        ArrayList<Parameter> params = command.getParameters();
        for (int i = 0, len = columns.length; i < len; i++) {
            Column column = columns[i].column;
            Parameter param = params.get(i);
            Value value;
            if (action == ConstraintActionType.SET_NULL) {
                value = ValueNull.INSTANCE;
            } else {
                Expression expr = column.getDefaultExpression();
                if (expr == null) {
                    throw DbException.get(ErrorCode.NO_DEFAULT_SET_1, column.getName());
                }
                value = expr.getValue(session);
            }
            param.setValue(value);
        }
    }
    return command;
}

Example 33 with Prepared

use of org.h2.test.db.Db.Prepared in project h2database by h2database.

the class TestFuzzOptimizations method testGroupSorted.

private void testGroupSorted() {
    Db db = new Db(conn);
    db.execute("CREATE TABLE TEST(A INT, B INT, C INT)");
    Random random = new Random();
    long seed = random.nextLong();
    println("seed: " + seed);
    for (int i = 0; i < 100; i++) {
        Prepared p = db.prepare("INSERT INTO TEST VALUES(?, ?, ?)");
        p.set(new String[] { null, "0", "1", "2" }[random.nextInt(4)]);
        p.set(new String[] { null, "0", "1", "2" }[random.nextInt(4)]);
        p.set(new String[] { null, "0", "1", "2" }[random.nextInt(4)]);
        p.execute();
    }
    int len = getSize(1000, 3000);
    for (int i = 0; i < len / 10; i++) {
        db.execute("CREATE TABLE TEST_INDEXED AS SELECT * FROM TEST");
        int jLen = 1 + random.nextInt(2);
        for (int j = 0; j < jLen; j++) {
            String x = "CREATE INDEX IDX" + j + " ON TEST_INDEXED(";
            int kLen = 1 + random.nextInt(2);
            for (int k = 0; k < kLen; k++) {
                if (k > 0) {
                    x += ",";
                }
                x += new String[] { "A", "B", "C" }[random.nextInt(3)];
            }
            db.execute(x + ")");
        }
        for (int j = 0; j < 10; j++) {
            String x = "SELECT ";
            for (int k = 0; k < 3; k++) {
                if (k > 0) {
                    x += ",";
                }
                x += new String[] { "SUM(A)", "MAX(B)", "AVG(C)", "COUNT(B)" }[random.nextInt(4)];
                x += " S" + k;
            }
            x += " FROM ";
            String group = " GROUP BY ";
            int kLen = 1 + random.nextInt(2);
            for (int k = 0; k < kLen; k++) {
                if (k > 0) {
                    group += ",";
                }
                group += new String[] { "A", "B", "C" }[random.nextInt(3)];
            }
            group += " ORDER BY 1, 2, 3";
            List<Map<String, Object>> a = db.query(x + "TEST" + group);
            List<Map<String, Object>> b = db.query(x + "TEST_INDEXED" + group);
            assertEquals(a.toString(), b.toString());
            assertTrue(a.equals(b));
        }
        db.execute("DROP TABLE TEST_INDEXED");
    }
    db.execute("DROP TABLE TEST");
}

Example 34 with Prepared

use of org.h2.test.db.Db.Prepared in project h2database by h2database.

the class TableView method createTableViewMaybeRecursive.

/**
 * Create a view.
 *
 * @param schema the schema
 * @param id the view id
 * @param name the view name
 * @param querySQL the query
 * @param parameters the parameters
 * @param columnTemplates the columns
 * @param session the session
 * @param literalsChecked whether literals in the query are checked
 * @param isTableExpression if this is a table expression
 * @param isPersistent whether the view is persisted
 * @param db the database
 * @return the view
 */
public static TableView createTableViewMaybeRecursive(Schema schema, int id, String name, String querySQL, ArrayList<Parameter> parameters, Column[] columnTemplates, Session session, boolean literalsChecked, boolean isTableExpression, boolean isPersistent, Database db) {
    Table recursiveTable = TableView.createShadowTableForRecursiveTableExpression(isPersistent, session, name, schema, Arrays.asList(columnTemplates), db);
    List<Column> columnTemplateList;
    String[] querySQLOutput = { null };
    ArrayList<String> columnNames = new ArrayList<>();
    for (Column columnTemplate : columnTemplates) {
        columnNames.add(columnTemplate.getName());
    }
    try {
        Prepared withQuery = session.prepare(querySQL, false, false);
        if (isPersistent) {
            withQuery.setSession(session);
        }
        columnTemplateList = TableView.createQueryColumnTemplateList(columnNames.toArray(new String[1]), (Query) withQuery, querySQLOutput);
    } finally {
        TableView.destroyShadowTableForRecursiveExpression(isPersistent, session, recursiveTable);
    }
    // build with recursion turned on
    TableView view = new TableView(schema, id, name, querySQL, parameters, columnTemplateList.toArray(columnTemplates), session, true, /* try recursive */
    literalsChecked, isTableExpression, isPersistent);
    // and no recursive index
    if (!view.isRecursiveQueryDetected()) {
        if (isPersistent) {
            db.addSchemaObject(session, view);
            view.lock(session, true, true);
            session.getDatabase().removeSchemaObject(session, view);
            // during database startup - this method does not normally get called - and it
            // needs to be to correctly un-register the table which the table expression
            // uses...
            view.removeChildrenAndResources(session);
        } else {
            session.removeLocalTempTable(view);
        }
        view = new TableView(schema, id, name, querySQL, parameters, columnTemplates, session, false, /* detected not recursive */
        literalsChecked, isTableExpression, isPersistent);
    }
    return view;
}

Example 35 with Prepared

use of org.h2.test.db.Db.Prepared 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;
}