Examples with Sequence org.h2.schema.Sequence used on opensource projects

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;
}

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);
    }
}

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;
}

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;
}

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();
}