Examples with Comment org.h2.engine.Comment used on opensource projects

Example 21 with Comment

use of org.h2.engine.Comment in project h2database by h2database.

the class CreateIndex method update.

@Override
public int update() {
    if (!transactional) {
        session.commit(true);
    }
    Database db = session.getDatabase();
    boolean persistent = db.isPersistent();
    Table table = getSchema().findTableOrView(session, tableName);
    if (table == null) {
        if (ifTableExists) {
            return 0;
        }
        throw DbException.get(ErrorCode.TABLE_OR_VIEW_NOT_FOUND_1, tableName);
    }
    if (getSchema().findIndex(session, indexName) != null) {
        if (ifNotExists) {
            return 0;
        }
        throw DbException.get(ErrorCode.INDEX_ALREADY_EXISTS_1, indexName);
    }
    session.getUser().checkRight(table, Right.ALL);
    table.lock(session, true, true);
    if (!table.isPersistIndexes()) {
        persistent = false;
    }
    int id = getObjectId();
    if (indexName == null) {
        if (primaryKey) {
            indexName = table.getSchema().getUniqueIndexName(session, table, Constants.PREFIX_PRIMARY_KEY);
        } else {
            indexName = table.getSchema().getUniqueIndexName(session, table, Constants.PREFIX_INDEX);
        }
    }
    IndexType indexType;
    if (primaryKey) {
        if (table.findPrimaryKey() != null) {
            throw DbException.get(ErrorCode.SECOND_PRIMARY_KEY);
        }
        indexType = IndexType.createPrimaryKey(persistent, hash);
    } else if (unique) {
        indexType = IndexType.createUnique(persistent, hash);
    } else if (affinity) {
        indexType = IndexType.createAffinity();
    } else {
        indexType = IndexType.createNonUnique(persistent, hash, spatial);
    }
    IndexColumn.mapColumns(indexColumns, table);
    table.addIndex(session, indexName, id, indexColumns, indexType, create, comment);
    return 0;
}

Example 22 with Comment

use of org.h2.engine.Comment in project h2database by h2database.

the class CreateView method update.

@Override
public int update() {
    session.commit(true);
    session.getUser().checkAdmin();
    Database db = session.getDatabase();
    TableView view = null;
    Table old = getSchema().findTableOrView(session, viewName);
    if (old != null) {
        if (ifNotExists) {
            return 0;
        }
        if (!orReplace || TableType.VIEW != old.getTableType()) {
            throw DbException.get(ErrorCode.VIEW_ALREADY_EXISTS_1, viewName);
        }
        view = (TableView) old;
    }
    int id = getObjectId();
    String querySQL;
    if (select == null) {
        querySQL = selectSQL;
    } else {
        ArrayList<Parameter> params = select.getParameters();
        if (params != null && !params.isEmpty()) {
            throw DbException.getUnsupportedException("parameters in views");
        }
        querySQL = select.getPlanSQL();
    }
    Column[] columnTemplatesAsUnknowns = null;
    Column[] columnTemplatesAsStrings = null;
    if (columnNames != null) {
        columnTemplatesAsUnknowns = new Column[columnNames.length];
        columnTemplatesAsStrings = new Column[columnNames.length];
        for (int i = 0; i < columnNames.length; ++i) {
            // non table expressions are fine to use unknown column type
            columnTemplatesAsUnknowns[i] = new Column(columnNames[i], Value.UNKNOWN);
            // table expressions can't have unknown types - so we use string instead
            columnTemplatesAsStrings[i] = new Column(columnNames[i], Value.STRING);
        }
    }
    if (view == null) {
        if (isTableExpression) {
            view = TableView.createTableViewMaybeRecursive(getSchema(), id, viewName, querySQL, null, columnTemplatesAsStrings, session, false, /* literalsChecked */
            isTableExpression, true, /* isPersistent */
            db);
        } else {
            view = new TableView(getSchema(), id, viewName, querySQL, null, columnTemplatesAsUnknowns, session, false, /* allow recursive */
            false, /* literalsChecked */
            isTableExpression, true);
        }
    } else {
        // TODO support isTableExpression in replace function...
        view.replace(querySQL, columnTemplatesAsUnknowns, session, false, force, false);
        view.setModified();
    }
    if (comment != null) {
        view.setComment(comment);
    }
    if (old == null) {
        db.addSchemaObject(session, view);
        db.unlockMeta(session);
    } else {
        db.updateMeta(session, view);
    }
    return 0;
}

Example 23 with Comment

use of org.h2.engine.Comment in project h2database by h2database.

the class JdbcDatabaseMetaData method getTables.

/**
 * Gets the list of tables in the database. The result set is sorted by
 * TABLE_TYPE, TABLE_SCHEM, and TABLE_NAME.
 *
 * <ol>
 * <li>TABLE_CAT (String) table catalog</li>
 * <li>TABLE_SCHEM (String) table schema</li>
 * <li>TABLE_NAME (String) table name</li>
 * <li>TABLE_TYPE (String) table type</li>
 * <li>REMARKS (String) comment</li>
 * <li>TYPE_CAT (String) always null</li>
 * <li>TYPE_SCHEM (String) always null</li>
 * <li>TYPE_NAME (String) always null</li>
 * <li>SELF_REFERENCING_COL_NAME (String) always null</li>
 * <li>REF_GENERATION (String) always null</li>
 * <li>SQL (String) the create table statement or NULL for systems tables.</li>
 * </ol>
 *
 * @param catalogPattern null (to get all objects) or the catalog name
 * @param schemaPattern null (to get all objects) or a schema name
 *            (uppercase for unquoted names)
 * @param tableNamePattern null (to get all objects) or a table name
 *            (uppercase for unquoted names)
 * @param types null or a list of table types
 * @return the list of columns
 * @throws SQLException if the connection is closed
 */
@Override
public ResultSet getTables(String catalogPattern, String schemaPattern, String tableNamePattern, String[] types) throws SQLException {
    try {
        if (isDebugEnabled()) {
            debugCode("getTables(" + quote(catalogPattern) + ", " + quote(schemaPattern) + ", " + quote(tableNamePattern) + ", " + quoteArray(types) + ");");
        }
        checkClosed();
        int typesLength = types != null ? types.length : 0;
        boolean includeSynonyms = hasSynonyms() && (types == null || Arrays.asList(types).contains("SYNONYM"));
        // (1024 - 16) is enough for the most cases
        StringBuilder select = new StringBuilder(1008);
        if (includeSynonyms) {
            select.append("SELECT " + "TABLE_CAT, " + "TABLE_SCHEM, " + "TABLE_NAME, " + "TABLE_TYPE, " + "REMARKS, " + "TYPE_CAT, " + "TYPE_SCHEM, " + "TYPE_NAME, " + "SELF_REFERENCING_COL_NAME, " + "REF_GENERATION, " + "SQL " + "FROM (" + "SELECT " + "SYNONYM_CATALOG TABLE_CAT, " + "SYNONYM_SCHEMA TABLE_SCHEM, " + "SYNONYM_NAME as TABLE_NAME, " + "TYPE_NAME AS TABLE_TYPE, " + "REMARKS, " + "TYPE_NAME TYPE_CAT, " + "TYPE_NAME TYPE_SCHEM, " + "TYPE_NAME AS TYPE_NAME, " + "TYPE_NAME SELF_REFERENCING_COL_NAME, " + "TYPE_NAME REF_GENERATION, " + "NULL AS SQL " + "FROM INFORMATION_SCHEMA.SYNONYMS " + "WHERE SYNONYM_CATALOG LIKE ?1 ESCAPE ?4 " + "AND SYNONYM_SCHEMA LIKE ?2 ESCAPE ?4 " + "AND SYNONYM_NAME LIKE ?3 ESCAPE ?4 " + "UNION ");
        }
        select.append("SELECT " + "TABLE_CATALOG TABLE_CAT, " + "TABLE_SCHEMA TABLE_SCHEM, " + "TABLE_NAME, " + "TABLE_TYPE, " + "REMARKS, " + "TYPE_NAME TYPE_CAT, " + "TYPE_NAME TYPE_SCHEM, " + "TYPE_NAME, " + "TYPE_NAME SELF_REFERENCING_COL_NAME, " + "TYPE_NAME REF_GENERATION, " + "SQL " + "FROM INFORMATION_SCHEMA.TABLES " + "WHERE TABLE_CATALOG LIKE ?1 ESCAPE ?4 " + "AND TABLE_SCHEMA LIKE ?2 ESCAPE ?4 " + "AND TABLE_NAME LIKE ?3 ESCAPE ?4");
        if (typesLength > 0) {
            select.append(" AND TABLE_TYPE IN(");
            for (int i = 0; i < typesLength; i++) {
                if (i > 0) {
                    select.append(", ");
                }
                select.append('?').append(i + 5);
            }
            select.append(')');
        }
        if (includeSynonyms) {
            select.append(')');
        }
        PreparedStatement prep = conn.prepareAutoCloseStatement(select.append(" ORDER BY TABLE_TYPE, TABLE_SCHEM, TABLE_NAME").toString());
        prep.setString(1, getCatalogPattern(catalogPattern));
        prep.setString(2, getSchemaPattern(schemaPattern));
        prep.setString(3, getPattern(tableNamePattern));
        prep.setString(4, "\\");
        for (int i = 0; i < typesLength; i++) {
            prep.setString(5 + i, types[i]);
        }
        return prep.executeQuery();
    } catch (Exception e) {
        throw logAndConvert(e);
    }
}

Example 24 with Comment

use of org.h2.engine.Comment in project h2database by h2database.

the class JdbcDatabaseMetaData method getColumns.

/**
 * Gets the list of columns. The result set is sorted by TABLE_SCHEM,
 * TABLE_NAME, and ORDINAL_POSITION.
 *
 * <ol>
 * <li>TABLE_CAT (String) table catalog</li>
 * <li>TABLE_SCHEM (String) table schema</li>
 * <li>TABLE_NAME (String) table name</li>
 * <li>COLUMN_NAME (String) column name</li>
 * <li>DATA_TYPE (short) data type (see java.sql.Types)</li>
 * <li>TYPE_NAME (String) data type name ("INTEGER", "VARCHAR",...)</li>
 * <li>COLUMN_SIZE (int) precision
 *         (values larger than 2 GB are returned as 2 GB)</li>
 * <li>BUFFER_LENGTH (int) unused</li>
 * <li>DECIMAL_DIGITS (int) scale (0 for INTEGER and VARCHAR)</li>
 * <li>NUM_PREC_RADIX (int) radix (always 10)</li>
 * <li>NULLABLE (int) columnNoNulls or columnNullable</li>
 * <li>REMARKS (String) comment (always empty)</li>
 * <li>COLUMN_DEF (String) default value</li>
 * <li>SQL_DATA_TYPE (int) unused</li>
 * <li>SQL_DATETIME_SUB (int) unused</li>
 * <li>CHAR_OCTET_LENGTH (int) unused</li>
 * <li>ORDINAL_POSITION (int) the column index (1,2,...)</li>
 * <li>IS_NULLABLE (String) "NO" or "YES"</li>
 * <li>SCOPE_CATALOG (String) always null</li>
 * <li>SCOPE_SCHEMA (String) always null</li>
 * <li>SCOPE_TABLE (String) always null</li>
 * <li>SOURCE_DATA_TYPE (short) null</li>
 * <li>IS_AUTOINCREMENT (String) "NO" or "YES"</li>
 * <li>IS_GENERATEDCOLUMN (String) "NO" or "YES"</li>
 * </ol>
 *
 * @param catalogPattern null (to get all objects) or the catalog name
 * @param schemaPattern null (to get all objects) or a schema name
 *            (uppercase for unquoted names)
 * @param tableNamePattern null (to get all objects) or a table name
 *            (uppercase for unquoted names)
 * @param columnNamePattern null (to get all objects) or a column name
 *            (uppercase for unquoted names)
 * @return the list of columns
 * @throws SQLException if the connection is closed
 */
@Override
public ResultSet getColumns(String catalogPattern, String schemaPattern, String tableNamePattern, String columnNamePattern) throws SQLException {
    try {
        if (isDebugEnabled()) {
            debugCode("getColumns(" + quote(catalogPattern) + ", " + quote(schemaPattern) + ", " + quote(tableNamePattern) + ", " + quote(columnNamePattern) + ");");
        }
        checkClosed();
        boolean includeSynonyms = hasSynonyms();
        StringBuilder select = new StringBuilder(2432);
        if (includeSynonyms) {
            select.append("SELECT " + "TABLE_CAT, " + "TABLE_SCHEM, " + "TABLE_NAME, " + "COLUMN_NAME, " + "DATA_TYPE, " + "TYPE_NAME, " + "COLUMN_SIZE, " + "BUFFER_LENGTH, " + "DECIMAL_DIGITS, " + "NUM_PREC_RADIX, " + "NULLABLE, " + "REMARKS, " + "COLUMN_DEF, " + "SQL_DATA_TYPE, " + "SQL_DATETIME_SUB, " + "CHAR_OCTET_LENGTH, " + "ORDINAL_POSITION, " + "IS_NULLABLE, " + "SCOPE_CATALOG, " + "SCOPE_SCHEMA, " + "SCOPE_TABLE, " + "SOURCE_DATA_TYPE, " + "IS_AUTOINCREMENT, " + "IS_GENERATEDCOLUMN " + "FROM (" + "SELECT " + "s.SYNONYM_CATALOG TABLE_CAT, " + "s.SYNONYM_SCHEMA TABLE_SCHEM, " + "s.SYNONYM_NAME TABLE_NAME, " + "c.COLUMN_NAME, " + "c.DATA_TYPE, " + "c.TYPE_NAME, " + "c.CHARACTER_MAXIMUM_LENGTH COLUMN_SIZE, " + "c.CHARACTER_MAXIMUM_LENGTH BUFFER_LENGTH, " + "c.NUMERIC_SCALE DECIMAL_DIGITS, " + "c.NUMERIC_PRECISION_RADIX NUM_PREC_RADIX, " + "c.NULLABLE, " + "c.REMARKS, " + "c.COLUMN_DEFAULT COLUMN_DEF, " + "c.DATA_TYPE SQL_DATA_TYPE, " + "ZERO() SQL_DATETIME_SUB, " + "c.CHARACTER_OCTET_LENGTH CHAR_OCTET_LENGTH, " + "c.ORDINAL_POSITION, " + "c.IS_NULLABLE IS_NULLABLE, " + "CAST(c.SOURCE_DATA_TYPE AS VARCHAR) SCOPE_CATALOG, " + "CAST(c.SOURCE_DATA_TYPE AS VARCHAR) SCOPE_SCHEMA, " + "CAST(c.SOURCE_DATA_TYPE AS VARCHAR) SCOPE_TABLE, " + "c.SOURCE_DATA_TYPE, " + "CASE WHEN c.SEQUENCE_NAME IS NULL THEN " + "CAST(?1 AS VARCHAR) ELSE CAST(?2 AS VARCHAR) END IS_AUTOINCREMENT, " + "CASE WHEN c.IS_COMPUTED THEN " + "CAST(?2 AS VARCHAR) ELSE CAST(?1 AS VARCHAR) END IS_GENERATEDCOLUMN " + "FROM INFORMATION_SCHEMA.COLUMNS c JOIN INFORMATION_SCHEMA.SYNONYMS s ON " + "s.SYNONYM_FOR = c.TABLE_NAME " + "AND s.SYNONYM_FOR_SCHEMA = c.TABLE_SCHEMA " + "WHERE s.SYNONYM_CATALOG LIKE ?3 ESCAPE ?7 " + "AND s.SYNONYM_SCHEMA LIKE ?4 ESCAPE ?7 " + "AND s.SYNONYM_NAME LIKE ?5 ESCAPE ?7 " + "AND c.COLUMN_NAME LIKE ?6 ESCAPE ?7 " + "UNION ");
        }
        select.append("SELECT " + "TABLE_CATALOG TABLE_CAT, " + "TABLE_SCHEMA TABLE_SCHEM, " + "TABLE_NAME, " + "COLUMN_NAME, " + "DATA_TYPE, " + "TYPE_NAME, " + "CHARACTER_MAXIMUM_LENGTH COLUMN_SIZE, " + "CHARACTER_MAXIMUM_LENGTH BUFFER_LENGTH, " + "NUMERIC_SCALE DECIMAL_DIGITS, " + "NUMERIC_PRECISION_RADIX NUM_PREC_RADIX, " + "NULLABLE, " + "REMARKS, " + "COLUMN_DEFAULT COLUMN_DEF, " + "DATA_TYPE SQL_DATA_TYPE, " + "ZERO() SQL_DATETIME_SUB, " + "CHARACTER_OCTET_LENGTH CHAR_OCTET_LENGTH, " + "ORDINAL_POSITION, " + "IS_NULLABLE IS_NULLABLE, " + "CAST(SOURCE_DATA_TYPE AS VARCHAR) SCOPE_CATALOG, " + "CAST(SOURCE_DATA_TYPE AS VARCHAR) SCOPE_SCHEMA, " + "CAST(SOURCE_DATA_TYPE AS VARCHAR) SCOPE_TABLE, " + "SOURCE_DATA_TYPE, " + "CASE WHEN SEQUENCE_NAME IS NULL THEN " + "CAST(?1 AS VARCHAR) ELSE CAST(?2 AS VARCHAR) END IS_AUTOINCREMENT, " + "CASE WHEN IS_COMPUTED THEN " + "CAST(?2 AS VARCHAR) ELSE CAST(?1 AS VARCHAR) END IS_GENERATEDCOLUMN " + "FROM INFORMATION_SCHEMA.COLUMNS " + "WHERE TABLE_CATALOG LIKE ?3 ESCAPE ?7 " + "AND TABLE_SCHEMA LIKE ?4 ESCAPE ?7 " + "AND TABLE_NAME LIKE ?5 ESCAPE ?7 " + "AND COLUMN_NAME LIKE ?6 ESCAPE ?7");
        if (includeSynonyms) {
            select.append(')');
        }
        PreparedStatement prep = conn.prepareAutoCloseStatement(select.append(" ORDER BY TABLE_SCHEM, TABLE_NAME, ORDINAL_POSITION").toString());
        prep.setString(1, "NO");
        prep.setString(2, "YES");
        prep.setString(3, getCatalogPattern(catalogPattern));
        prep.setString(4, getSchemaPattern(schemaPattern));
        prep.setString(5, getPattern(tableNamePattern));
        prep.setString(6, getPattern(columnNamePattern));
        prep.setString(7, "\\");
        return prep.executeQuery();
    } catch (Exception e) {
        throw logAndConvert(e);
    }
}

Example 25 with Comment

use of org.h2.engine.Comment in project h2database by h2database.

the class TestTools method testRecover.

private void testRecover() throws SQLException {
    if (config.memory) {
        return;
    }
    deleteDb("toolsRecover");
    org.h2.Driver.load();
    String url = getURL("toolsRecover", true);
    Connection conn = getConnection(url, "sa", "sa");
    Statement stat = conn.createStatement();
    stat.execute("create table test(id int primary key, " + "name varchar, b blob, c clob)");
    stat.execute("create table \"test 2\"(id int primary key, name varchar)");
    stat.execute("comment on table test is ';-)'");
    stat.execute("insert into test values" + "(1, 'Hello', SECURE_RAND(4100), '\u00e4' || space(4100))");
    ResultSet rs;
    rs = stat.executeQuery("select * from test");
    rs.next();
    byte[] b1 = rs.getBytes(3);
    String s1 = rs.getString(4);
    conn.close();
    Recover.main("-dir", getBaseDir(), "-db", "toolsRecover");
    // deleteDb would delete the .lob.db directory as well
    // deleteDb("toolsRecover");
    ArrayList<String> list = FileLister.getDatabaseFiles(getBaseDir(), "toolsRecover", true);
    for (String fileName : list) {
        if (!FileUtils.isDirectory(fileName)) {
            FileUtils.delete(fileName);
        }
    }
    conn = getConnection(url);
    stat = conn.createStatement();
    String suffix = ".h2.sql";
    stat.execute("runscript from '" + getBaseDir() + "/toolsRecover" + suffix + "'");
    rs = stat.executeQuery("select * from \"test 2\"");
    assertFalse(rs.next());
    rs = stat.executeQuery("select * from test");
    rs.next();
    assertEquals(1, rs.getInt(1));
    assertEquals("Hello", rs.getString(2));
    byte[] b2 = rs.getBytes(3);
    String s2 = rs.getString(4);
    assertEquals("\u00e4 ", s2.substring(0, 2));
    assertEquals(4100, b2.length);
    assertEquals(4101, s2.length());
    assertEquals(b1, b2);
    assertEquals(s1, s2);
    assertFalse(rs.next());
    conn.close();
    deleteDb("toolsRecover");
    FileUtils.delete(getBaseDir() + "/toolsRecover.h2.sql");
    String dir = getBaseDir() + "/toolsRecover.lobs.db";
    FileUtils.deleteRecursive(dir, false);
}