Search in sources :

Example 6 with Sequence

use of org.h2.schema.Sequence in project h2database by h2database.

the class AlterTableAlterColumn method cloneTableStructure.

private Table cloneTableStructure(Table table, Column[] columns, Database db, String tempName, ArrayList<Column> newColumns) {
    for (Column col : columns) {
        newColumns.add(col.getClone());
    }
    if (type == CommandInterface.ALTER_TABLE_DROP_COLUMN) {
        for (Column removeCol : columnsToRemove) {
            Column foundCol = null;
            for (Column newCol : newColumns) {
                if (newCol.getName().equals(removeCol.getName())) {
                    foundCol = newCol;
                    break;
                }
            }
            if (foundCol == null) {
                throw DbException.throwInternalError(removeCol.getCreateSQL());
            }
            newColumns.remove(foundCol);
        }
    } else if (type == CommandInterface.ALTER_TABLE_ADD_COLUMN) {
        int position;
        if (addFirst) {
            position = 0;
        } else if (addBefore != null) {
            position = table.getColumn(addBefore).getColumnId();
        } else if (addAfter != null) {
            position = table.getColumn(addAfter).getColumnId() + 1;
        } else {
            position = columns.length;
        }
        if (columnsToAdd != null) {
            for (Column column : columnsToAdd) {
                newColumns.add(position++, column);
            }
        }
    } else if (type == CommandInterface.ALTER_TABLE_ALTER_COLUMN_CHANGE_TYPE) {
        int position = oldColumn.getColumnId();
        newColumns.set(position, newColumn);
    }
    // create a table object in order to get the SQL statement
    // can't just use this table, because most column objects are 'shared'
    // with the old table
    // still need a new id because using 0 would mean: the new table tries
    // to use the rows of the table 0 (the meta table)
    int id = db.allocateObjectId();
    CreateTableData data = new CreateTableData();
    data.tableName = tempName;
    data.id = id;
    data.columns = newColumns;
    data.temporary = table.isTemporary();
    data.persistData = table.isPersistData();
    data.persistIndexes = table.isPersistIndexes();
    data.isHidden = table.isHidden();
    data.create = true;
    data.session = session;
    Table newTable = getSchema().createTable(data);
    newTable.setComment(table.getComment());
    StringBuilder buff = new StringBuilder();
    buff.append(newTable.getCreateSQL());
    StringBuilder columnList = new StringBuilder();
    for (Column nc : newColumns) {
        if (columnList.length() > 0) {
            columnList.append(", ");
        }
        if (type == CommandInterface.ALTER_TABLE_ADD_COLUMN && columnsToAdd != null && columnsToAdd.contains(nc)) {
            Expression def = nc.getDefaultExpression();
            columnList.append(def == null ? "NULL" : def.getSQL());
        } else {
            columnList.append(nc.getSQL());
        }
    }
    buff.append(" AS SELECT ");
    if (columnList.length() == 0) {
        // special case: insert into test select * from
        buff.append('*');
    } else {
        buff.append(columnList);
    }
    buff.append(" FROM ").append(table.getSQL());
    String newTableSQL = buff.toString();
    String newTableName = newTable.getName();
    Schema newTableSchema = newTable.getSchema();
    newTable.removeChildrenAndResources(session);
    execute(newTableSQL, true);
    newTable = newTableSchema.getTableOrView(session, newTableName);
    ArrayList<String> triggers = New.arrayList();
    for (DbObject child : table.getChildren()) {
        if (child instanceof Sequence) {
            continue;
        } else if (child instanceof Index) {
            Index idx = (Index) child;
            if (idx.getIndexType().getBelongsToConstraint()) {
                continue;
            }
        }
        String createSQL = child.getCreateSQL();
        if (createSQL == null) {
            continue;
        }
        if (child instanceof TableView) {
            continue;
        } else if (child.getType() == DbObject.TABLE_OR_VIEW) {
            DbException.throwInternalError();
        }
        String quotedName = Parser.quoteIdentifier(tempName + "_" + child.getName());
        String sql = null;
        if (child instanceof ConstraintReferential) {
            ConstraintReferential r = (ConstraintReferential) child;
            if (r.getTable() != table) {
                sql = r.getCreateSQLForCopy(r.getTable(), newTable, quotedName, false);
            }
        }
        if (sql == null) {
            sql = child.getCreateSQLForCopy(newTable, quotedName);
        }
        if (sql != null) {
            if (child instanceof TriggerObject) {
                triggers.add(sql);
            } else {
                execute(sql, true);
            }
        }
    }
    table.setModified();
    // otherwise the sequence is dropped if the table is dropped
    for (Column col : newColumns) {
        Sequence seq = col.getSequence();
        if (seq != null) {
            table.removeSequence(seq);
            col.setSequence(null);
        }
    }
    for (String sql : triggers) {
        execute(sql, true);
    }
    return newTable;
}
Also used : Table(org.h2.table.Table) DbObject(org.h2.engine.DbObject) Schema(org.h2.schema.Schema) TriggerObject(org.h2.schema.TriggerObject) Index(org.h2.index.Index) Sequence(org.h2.schema.Sequence) ConstraintReferential(org.h2.constraint.ConstraintReferential) Constraint(org.h2.constraint.Constraint) Column(org.h2.table.Column) Expression(org.h2.expression.Expression) TableView(org.h2.table.TableView)

Example 7 with Sequence

use of org.h2.schema.Sequence in project h2database by h2database.

the class AlterTableAlterColumn method copyData.

private void copyData(Table table, ArrayList<Sequence> sequences, boolean createConstraints) {
    if (table.isTemporary()) {
        throw DbException.getUnsupportedException("TEMP TABLE");
    }
    Database db = session.getDatabase();
    String baseName = table.getName();
    String tempName = db.getTempTableName(baseName, session);
    Column[] columns = table.getColumns();
    ArrayList<Column> newColumns = New.arrayList();
    Table newTable = cloneTableStructure(table, columns, db, tempName, newColumns);
    if (sequences != null) {
        for (Sequence sequence : sequences) {
            table.addSequence(sequence);
        }
    }
    try {
        // check if a view would become invalid
        // (because the column to drop is referenced or so)
        checkViews(table, newTable);
    } catch (DbException e) {
        execute("DROP TABLE " + newTable.getName(), true);
        throw DbException.get(ErrorCode.VIEW_IS_INVALID_2, e, getSQL(), e.getMessage());
    }
    String tableName = table.getName();
    ArrayList<TableView> dependentViews = new ArrayList<>(table.getDependentViews());
    for (TableView view : dependentViews) {
        table.removeDependentView(view);
    }
    execute("DROP TABLE " + table.getSQL() + " IGNORE", true);
    db.renameSchemaObject(session, newTable, tableName);
    for (DbObject child : newTable.getChildren()) {
        if (child instanceof Sequence) {
            continue;
        }
        String name = child.getName();
        if (name == null || child.getCreateSQL() == null) {
            continue;
        }
        if (name.startsWith(tempName + "_")) {
            name = name.substring(tempName.length() + 1);
            SchemaObject so = (SchemaObject) child;
            if (so instanceof Constraint) {
                if (so.getSchema().findConstraint(session, name) != null) {
                    name = so.getSchema().getUniqueConstraintName(session, newTable);
                }
            } else if (so instanceof Index) {
                if (so.getSchema().findIndex(session, name) != null) {
                    name = so.getSchema().getUniqueIndexName(session, newTable, name);
                }
            }
            db.renameSchemaObject(session, so, name);
        }
    }
    if (createConstraints) {
        createConstraints();
    }
    for (TableView view : dependentViews) {
        String sql = view.getCreateSQL(true, true);
        execute(sql, true);
    }
}
Also used : SchemaObject(org.h2.schema.SchemaObject) Table(org.h2.table.Table) DbObject(org.h2.engine.DbObject) Constraint(org.h2.constraint.Constraint) ArrayList(java.util.ArrayList) Index(org.h2.index.Index) Sequence(org.h2.schema.Sequence) DbException(org.h2.message.DbException) Column(org.h2.table.Column) Database(org.h2.engine.Database) TableView(org.h2.table.TableView)

Example 8 with Sequence

use of org.h2.schema.Sequence in project h2database by h2database.

the class AlterTableAlterColumn method update.

@Override
public int update() {
    session.commit(true);
    Database db = session.getDatabase();
    Table table = getSchema().resolveTableOrView(session, tableName);
    if (table == null) {
        if (ifTableExists) {
            return 0;
        }
        throw DbException.get(ErrorCode.TABLE_OR_VIEW_NOT_FOUND_1, tableName);
    }
    session.getUser().checkRight(table, Right.ALL);
    table.checkSupportAlter();
    table.lock(session, true, true);
    if (newColumn != null) {
        checkDefaultReferencesTable(table, newColumn.getDefaultExpression());
        checkClustering(newColumn);
    }
    if (columnsToAdd != null) {
        for (Column column : columnsToAdd) {
            checkDefaultReferencesTable(table, column.getDefaultExpression());
            checkClustering(column);
        }
    }
    switch(type) {
        case CommandInterface.ALTER_TABLE_ALTER_COLUMN_NOT_NULL:
            {
                if (!oldColumn.isNullable()) {
                    // no change
                    break;
                }
                checkNoNullValues(table);
                oldColumn.setNullable(false);
                db.updateMeta(session, table);
                break;
            }
        case CommandInterface.ALTER_TABLE_ALTER_COLUMN_NULL:
            {
                if (oldColumn.isNullable()) {
                    // no change
                    break;
                }
                checkNullable(table);
                oldColumn.setNullable(true);
                db.updateMeta(session, table);
                break;
            }
        case CommandInterface.ALTER_TABLE_ALTER_COLUMN_DEFAULT:
            {
                Sequence sequence = oldColumn == null ? null : oldColumn.getSequence();
                checkDefaultReferencesTable(table, defaultExpression);
                oldColumn.setSequence(null);
                oldColumn.setDefaultExpression(session, defaultExpression);
                removeSequence(table, sequence);
                db.updateMeta(session, table);
                break;
            }
        case CommandInterface.ALTER_TABLE_ALTER_COLUMN_ON_UPDATE:
            {
                checkDefaultReferencesTable(table, defaultExpression);
                oldColumn.setOnUpdateExpression(session, defaultExpression);
                db.updateMeta(session, table);
                break;
            }
        case CommandInterface.ALTER_TABLE_ALTER_COLUMN_CHANGE_TYPE:
            {
                // and does not affect the storage structure.
                if (oldColumn.isWideningConversion(newColumn)) {
                    convertAutoIncrementColumn(table, newColumn);
                    oldColumn.copy(newColumn);
                    db.updateMeta(session, table);
                } else {
                    oldColumn.setSequence(null);
                    oldColumn.setDefaultExpression(session, null);
                    oldColumn.setConvertNullToDefault(false);
                    if (oldColumn.isNullable() && !newColumn.isNullable()) {
                        checkNoNullValues(table);
                    } else if (!oldColumn.isNullable() && newColumn.isNullable()) {
                        checkNullable(table);
                    }
                    if (oldColumn.getVisible() ^ newColumn.getVisible()) {
                        oldColumn.setVisible(newColumn.getVisible());
                    }
                    convertAutoIncrementColumn(table, newColumn);
                    copyData(table);
                }
                table.setModified();
                break;
            }
        case CommandInterface.ALTER_TABLE_ADD_COLUMN:
            {
                // ifNotExists only supported for single column add
                if (ifNotExists && columnsToAdd != null && columnsToAdd.size() == 1 && table.doesColumnExist(columnsToAdd.get(0).getName())) {
                    break;
                }
                ArrayList<Sequence> sequences = generateSequences(columnsToAdd, false);
                if (columnsToAdd != null) {
                    changePrimaryKeysToNotNull(columnsToAdd);
                }
                copyData(table, sequences, true);
                break;
            }
        case CommandInterface.ALTER_TABLE_DROP_COLUMN:
            {
                if (table.getColumns().length - columnsToRemove.size() < 1) {
                    throw DbException.get(ErrorCode.CANNOT_DROP_LAST_COLUMN, columnsToRemove.get(0).getSQL());
                }
                table.dropMultipleColumnsConstraintsAndIndexes(session, columnsToRemove);
                copyData(table);
                break;
            }
        case CommandInterface.ALTER_TABLE_ALTER_COLUMN_SELECTIVITY:
            {
                int value = newSelectivity.optimize(session).getValue(session).getInt();
                oldColumn.setSelectivity(value);
                db.updateMeta(session, table);
                break;
            }
        case CommandInterface.ALTER_TABLE_ALTER_COLUMN_VISIBILITY:
            {
                oldColumn.setVisible(newVisibility);
                table.setModified();
                db.updateMeta(session, table);
                break;
            }
        default:
            DbException.throwInternalError("type=" + type);
    }
    return 0;
}
Also used : Table(org.h2.table.Table) Column(org.h2.table.Column) Database(org.h2.engine.Database) ArrayList(java.util.ArrayList) Sequence(org.h2.schema.Sequence)

Example 9 with Sequence

use of org.h2.schema.Sequence in project h2database by h2database.

the class Parser method parseWith.

private Prepared parseWith() {
    List<TableView> viewsCreated = new ArrayList<>();
    readIf("RECURSIVE");
    // this WITH statement might not be a temporary view - allow optional keyword to
    // tell us that this keyword. This feature will not be documented - H2 internal use only.
    boolean isPersistent = readIf("PERSISTENT");
    // as in CREATE VIEW abc AS WITH my_cte - this auto detects that condition
    if (session.isParsingCreateView()) {
        isPersistent = true;
    }
    do {
        viewsCreated.add(parseSingleCommonTableExpression(isPersistent));
    } while (readIf(","));
    Prepared p = null;
    // reverse the order of constructed CTE views - as the destruction order
    // (since later created view may depend on previously created views -
    // we preserve that dependency order in the destruction sequence )
    // used in setCteCleanups
    Collections.reverse(viewsCreated);
    if (isToken("SELECT")) {
        Query query = parseSelectUnion();
        query.setPrepareAlways(true);
        query.setNeverLazy(true);
        p = query;
    } else if (readIf("INSERT")) {
        p = parseInsert();
        p.setPrepareAlways(true);
    } else if (readIf("UPDATE")) {
        p = parseUpdate();
        p.setPrepareAlways(true);
    } else if (readIf("MERGE")) {
        p = parseMerge();
        p.setPrepareAlways(true);
    } else if (readIf("DELETE")) {
        p = parseDelete();
        p.setPrepareAlways(true);
    } else if (readIf("CREATE")) {
        if (!isToken("TABLE")) {
            throw DbException.get(ErrorCode.SYNTAX_ERROR_1, WITH_STATEMENT_SUPPORTS_LIMITED_SUB_STATEMENTS);
        }
        p = parseCreate();
        p.setPrepareAlways(true);
    } else {
        throw DbException.get(ErrorCode.SYNTAX_ERROR_1, WITH_STATEMENT_SUPPORTS_LIMITED_SUB_STATEMENTS);
    }
    // dependencies) - but only if they are not persistent
    if (!isPersistent) {
        p.setCteCleanups(viewsCreated);
    }
    return p;
}
Also used : Query(org.h2.command.dml.Query) ArrayList(java.util.ArrayList) TableView(org.h2.table.TableView)

Example 10 with Sequence

use of org.h2.schema.Sequence in project h2database by h2database.

the class MergeUsing method prepare.

@Override
public void prepare() {
    onCondition.addFilterConditions(sourceTableFilter, true);
    onCondition.addFilterConditions(targetTableFilter, true);
    onCondition.mapColumns(sourceTableFilter, 2);
    onCondition.mapColumns(targetTableFilter, 1);
    if (keys == null) {
        HashSet<Column> targetColumns = buildColumnListFromOnCondition(targetTableFilter);
        keys = targetColumns.toArray(new Column[0]);
    }
    if (keys.length == 0) {
        throw DbException.get(ErrorCode.COLUMN_NOT_FOUND_1, "No references to target columns found in ON clause:" + targetTableFilter.toString());
    }
    if (sourceKeys == null) {
        HashSet<Column> sourceColumns = buildColumnListFromOnCondition(sourceTableFilter);
        sourceKeys = sourceColumns.toArray(new Column[0]);
    }
    if (sourceKeys.length == 0) {
        throw DbException.get(ErrorCode.COLUMN_NOT_FOUND_1, "No references to source columns found in ON clause:" + sourceTableFilter.toString());
    }
    // only do the optimize now - before we have already gathered the
    // unoptimized column data
    onCondition = onCondition.optimize(session);
    onCondition.createIndexConditions(session, sourceTableFilter);
    onCondition.createIndexConditions(session, targetTableFilter);
    if (columns == null) {
        if (!valuesExpressionList.isEmpty() && valuesExpressionList.get(0).length == 0) {
            // special case where table is used as a sequence
            columns = new Column[0];
        } else {
            columns = targetTable.getColumns();
        }
    }
    if (!valuesExpressionList.isEmpty()) {
        for (Expression[] expr : valuesExpressionList) {
            if (expr.length != columns.length) {
                throw DbException.get(ErrorCode.COLUMN_COUNT_DOES_NOT_MATCH);
            }
            for (int i = 0; i < expr.length; i++) {
                Expression e = expr[i];
                if (e != null) {
                    expr[i] = e.optimize(session);
                }
            }
        }
    } else {
        query.prepare();
    }
    int embeddedStatementsCount = 0;
    // collaboration
    if (updateCommand != null) {
        updateCommand.setSourceTableFilter(sourceTableFilter);
        updateCommand.setCondition(appendOnCondition(updateCommand));
        updateCommand.prepare();
        embeddedStatementsCount++;
    }
    if (deleteCommand != null) {
        deleteCommand.setSourceTableFilter(sourceTableFilter);
        deleteCommand.setCondition(appendOnCondition(deleteCommand));
        deleteCommand.prepare();
        embeddedStatementsCount++;
    }
    if (insertCommand != null) {
        insertCommand.setSourceTableFilter(sourceTableFilter);
        insertCommand.prepare();
        embeddedStatementsCount++;
    }
    if (embeddedStatementsCount == 0) {
        throw DbException.get(ErrorCode.SYNTAX_ERROR_1, "At least UPDATE, DELETE or INSERT embedded statement must be supplied.");
    }
    // setup the targetMatchQuery - for detecting if the target row exists
    Expression targetMatchCondition = targetMatchQuery.getCondition();
    targetMatchCondition.addFilterConditions(sourceTableFilter, true);
    targetMatchCondition.mapColumns(sourceTableFilter, 2);
    targetMatchCondition = targetMatchCondition.optimize(session);
    targetMatchCondition.createIndexConditions(session, sourceTableFilter);
    targetMatchQuery.prepare();
}
Also used : Column(org.h2.table.Column) Expression(org.h2.expression.Expression)

Aggregations

Sequence (org.h2.schema.Sequence)19 ValueString (org.h2.value.ValueString)14 PreparedStatement (java.sql.PreparedStatement)10 Column (org.h2.table.Column)10 Database (org.h2.engine.Database)9 Expression (org.h2.expression.Expression)8 DbException (org.h2.message.DbException)8 Table (org.h2.table.Table)8 ResultSet (java.sql.ResultSet)7 Statement (java.sql.Statement)7 Constraint (org.h2.constraint.Constraint)7 Index (org.h2.index.Index)7 Connection (java.sql.Connection)6 SQLException (java.sql.SQLException)6 SchemaObject (org.h2.schema.SchemaObject)6 ArrayList (java.util.ArrayList)5 DropSequence (org.h2.command.ddl.DropSequence)5 DbObject (org.h2.engine.DbObject)5 Schema (org.h2.schema.Schema)5 TriggerObject (org.h2.schema.TriggerObject)5