Search in sources :

Example 11 with Insert

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

the class Parser method parseCreateTrigger.

private CreateTrigger parseCreateTrigger(boolean force) {
    boolean ifNotExists = readIfNotExists();
    String triggerName = readIdentifierWithSchema(null);
    Schema schema = getSchema();
    boolean insteadOf, isBefore;
    if (readIf("INSTEAD")) {
        read("OF");
        isBefore = true;
        insteadOf = true;
    } else if (readIf("BEFORE")) {
        insteadOf = false;
        isBefore = true;
    } else {
        read("AFTER");
        insteadOf = false;
        isBefore = false;
    }
    int typeMask = 0;
    boolean onRollback = false;
    do {
        if (readIf("INSERT")) {
            typeMask |= Trigger.INSERT;
        } else if (readIf("UPDATE")) {
            typeMask |= Trigger.UPDATE;
        } else if (readIf("DELETE")) {
            typeMask |= Trigger.DELETE;
        } else if (readIf("SELECT")) {
            typeMask |= Trigger.SELECT;
        } else if (readIf("ROLLBACK")) {
            onRollback = true;
        } else {
            throw getSyntaxError();
        }
    } while (readIf(","));
    read("ON");
    String tableName = readIdentifierWithSchema();
    checkSchema(schema);
    CreateTrigger command = new CreateTrigger(session, getSchema());
    command.setForce(force);
    command.setTriggerName(triggerName);
    command.setIfNotExists(ifNotExists);
    command.setInsteadOf(insteadOf);
    command.setBefore(isBefore);
    command.setOnRollback(onRollback);
    command.setTypeMask(typeMask);
    command.setTableName(tableName);
    if (readIf("FOR")) {
        read("EACH");
        read("ROW");
        command.setRowBased(true);
    } else {
        command.setRowBased(false);
    }
    if (readIf("QUEUE")) {
        command.setQueueSize(readPositiveInt());
    }
    command.setNoWait(readIf("NOWAIT"));
    if (readIf("AS")) {
        command.setTriggerSource(readString());
    } else {
        read("CALL");
        command.setTriggerClassName(readUniqueIdentifier());
    }
    return command;
}
Also used : CreateTrigger(org.h2.command.ddl.CreateTrigger) DropSchema(org.h2.command.ddl.DropSchema) CreateSchema(org.h2.command.ddl.CreateSchema) Schema(org.h2.schema.Schema) ValueString(org.h2.value.ValueString) AlterTableRenameConstraint(org.h2.command.ddl.AlterTableRenameConstraint) AlterTableAddConstraint(org.h2.command.ddl.AlterTableAddConstraint) AlterTableDropConstraint(org.h2.command.ddl.AlterTableDropConstraint)

Example 12 with Insert

use of org.h2.command.dml.Insert 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 13 with Insert

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

the class Parser method parseInsert.

private Insert parseInsert() {
    Insert command = new Insert(session);
    currentPrepared = command;
    if (database.getMode().onDuplicateKeyUpdate && readIf("IGNORE")) {
        command.setIgnore(true);
    }
    read("INTO");
    Table table = readTableOrView();
    command.setTable(table);
    Insert returnedCommand = parseInsertGivenTable(command, table);
    if (returnedCommand != null) {
        return returnedCommand;
    }
    if (database.getMode().onDuplicateKeyUpdate) {
        if (readIf("ON")) {
            read("DUPLICATE");
            read("KEY");
            read("UPDATE");
            do {
                String columnName = readColumnIdentifier();
                if (readIf(".")) {
                    String schemaOrTableName = columnName;
                    String tableOrColumnName = readColumnIdentifier();
                    if (readIf(".")) {
                        if (!table.getSchema().getName().equals(schemaOrTableName)) {
                            throw DbException.get(ErrorCode.SCHEMA_NAME_MUST_MATCH);
                        }
                        columnName = readColumnIdentifier();
                    } else {
                        columnName = tableOrColumnName;
                        tableOrColumnName = schemaOrTableName;
                    }
                    if (!table.getName().equals(tableOrColumnName)) {
                        throw DbException.get(ErrorCode.TABLE_OR_VIEW_NOT_FOUND_1, tableOrColumnName);
                    }
                }
                Column column = table.getColumn(columnName);
                read("=");
                Expression expression;
                if (readIf("DEFAULT")) {
                    expression = ValueExpression.getDefault();
                } else {
                    expression = readExpression();
                }
                command.addAssignmentForDuplicate(column, expression);
            } while (readIf(","));
        }
    }
    if (database.getMode().isolationLevelInSelectOrInsertStatement) {
        parseIsolationClause();
    }
    return command;
}
Also used : RangeTable(org.h2.table.RangeTable) TruncateTable(org.h2.command.ddl.TruncateTable) CreateTable(org.h2.command.ddl.CreateTable) FunctionTable(org.h2.table.FunctionTable) CreateLinkedTable(org.h2.command.ddl.CreateLinkedTable) Table(org.h2.table.Table) DropTable(org.h2.command.ddl.DropTable) AlterTableRenameColumn(org.h2.command.ddl.AlterTableRenameColumn) AlterTableAlterColumn(org.h2.command.ddl.AlterTableAlterColumn) Column(org.h2.table.Column) ExpressionColumn(org.h2.expression.ExpressionColumn) IndexColumn(org.h2.table.IndexColumn) Expression(org.h2.expression.Expression) ValueExpression(org.h2.expression.ValueExpression) ValueString(org.h2.value.ValueString) Insert(org.h2.command.dml.Insert)

Example 14 with Insert

use of org.h2.command.dml.Insert 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)

Example 15 with Insert

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

the class ScriptCommand method query.

@Override
public ResultInterface query(int maxrows) {
    session.getUser().checkAdmin();
    reset();
    Database db = session.getDatabase();
    if (schemaNames != null) {
        for (String schemaName : schemaNames) {
            Schema schema = db.findSchema(schemaName);
            if (schema == null) {
                throw DbException.get(ErrorCode.SCHEMA_NOT_FOUND_1, schemaName);
            }
        }
    }
    try {
        result = createResult();
        deleteStore();
        openOutput();
        if (out != null) {
            buffer = new byte[Constants.IO_BUFFER_SIZE];
        }
        if (settings) {
            for (Setting setting : db.getAllSettings()) {
                if (setting.getName().equals(SetTypes.getTypeName(SetTypes.CREATE_BUILD))) {
                    // (it is only set when creating the database)
                    continue;
                }
                add(setting.getCreateSQL(), false);
            }
        }
        if (out != null) {
            add("", true);
        }
        for (User user : db.getAllUsers()) {
            add(user.getCreateSQL(passwords), false);
        }
        for (Role role : db.getAllRoles()) {
            add(role.getCreateSQL(true), false);
        }
        for (Schema schema : db.getAllSchemas()) {
            if (excludeSchema(schema)) {
                continue;
            }
            add(schema.getCreateSQL(), false);
        }
        for (UserDataType datatype : db.getAllUserDataTypes()) {
            if (drop) {
                add(datatype.getDropSQL(), false);
            }
            add(datatype.getCreateSQL(), false);
        }
        for (SchemaObject obj : db.getAllSchemaObjects(DbObject.CONSTANT)) {
            if (excludeSchema(obj.getSchema())) {
                continue;
            }
            Constant constant = (Constant) obj;
            add(constant.getCreateSQL(), false);
        }
        final ArrayList<Table> tables = db.getAllTablesAndViews(false);
        // sort by id, so that views are after tables and views on views
        // after the base views
        Collections.sort(tables, new Comparator<Table>() {

            @Override
            public int compare(Table t1, Table t2) {
                return t1.getId() - t2.getId();
            }
        });
        // Generate the DROP XXX  ... IF EXISTS
        for (Table table : tables) {
            if (excludeSchema(table.getSchema())) {
                continue;
            }
            if (excludeTable(table)) {
                continue;
            }
            if (table.isHidden()) {
                continue;
            }
            table.lock(session, false, false);
            String sql = table.getCreateSQL();
            if (sql == null) {
                // null for metadata tables
                continue;
            }
            if (drop) {
                add(table.getDropSQL(), false);
            }
        }
        for (SchemaObject obj : db.getAllSchemaObjects(DbObject.FUNCTION_ALIAS)) {
            if (excludeSchema(obj.getSchema())) {
                continue;
            }
            if (drop) {
                add(obj.getDropSQL(), false);
            }
            add(obj.getCreateSQL(), false);
        }
        for (UserAggregate agg : db.getAllAggregates()) {
            if (drop) {
                add(agg.getDropSQL(), false);
            }
            add(agg.getCreateSQL(), false);
        }
        for (SchemaObject obj : db.getAllSchemaObjects(DbObject.SEQUENCE)) {
            if (excludeSchema(obj.getSchema())) {
                continue;
            }
            Sequence sequence = (Sequence) obj;
            if (drop) {
                add(sequence.getDropSQL(), false);
            }
            add(sequence.getCreateSQL(), false);
        }
        // Generate CREATE TABLE and INSERT...VALUES
        int count = 0;
        for (Table table : tables) {
            if (excludeSchema(table.getSchema())) {
                continue;
            }
            if (excludeTable(table)) {
                continue;
            }
            if (table.isHidden()) {
                continue;
            }
            table.lock(session, false, false);
            String createTableSql = table.getCreateSQL();
            if (createTableSql == null) {
                // null for metadata tables
                continue;
            }
            final TableType tableType = table.getTableType();
            add(createTableSql, false);
            final ArrayList<Constraint> constraints = table.getConstraints();
            if (constraints != null) {
                for (Constraint constraint : constraints) {
                    if (Constraint.Type.PRIMARY_KEY == constraint.getConstraintType()) {
                        add(constraint.getCreateSQLWithoutIndexes(), false);
                    }
                }
            }
            if (TableType.TABLE == tableType) {
                if (table.canGetRowCount()) {
                    String rowcount = "-- " + table.getRowCountApproximation() + " +/- SELECT COUNT(*) FROM " + table.getSQL();
                    add(rowcount, false);
                }
                if (data) {
                    count = generateInsertValues(count, table);
                }
            }
            final ArrayList<Index> indexes = table.getIndexes();
            for (int j = 0; indexes != null && j < indexes.size(); j++) {
                Index index = indexes.get(j);
                if (!index.getIndexType().getBelongsToConstraint()) {
                    add(index.getCreateSQL(), false);
                }
            }
        }
        if (tempLobTableCreated) {
            add("DROP TABLE IF EXISTS SYSTEM_LOB_STREAM", true);
            add("CALL SYSTEM_COMBINE_BLOB(-1)", true);
            add("DROP ALIAS IF EXISTS SYSTEM_COMBINE_CLOB", true);
            add("DROP ALIAS IF EXISTS SYSTEM_COMBINE_BLOB", true);
            tempLobTableCreated = false;
        }
        // Generate CREATE CONSTRAINT ...
        final ArrayList<SchemaObject> constraints = db.getAllSchemaObjects(DbObject.CONSTRAINT);
        Collections.sort(constraints, new Comparator<SchemaObject>() {

            @Override
            public int compare(SchemaObject c1, SchemaObject c2) {
                return ((Constraint) c1).compareTo((Constraint) c2);
            }
        });
        for (SchemaObject obj : constraints) {
            if (excludeSchema(obj.getSchema())) {
                continue;
            }
            Constraint constraint = (Constraint) obj;
            if (excludeTable(constraint.getTable())) {
                continue;
            }
            if (constraint.getTable().isHidden()) {
                continue;
            }
            if (Constraint.Type.PRIMARY_KEY != constraint.getConstraintType()) {
                add(constraint.getCreateSQLWithoutIndexes(), false);
            }
        }
        // Generate CREATE TRIGGER ...
        for (SchemaObject obj : db.getAllSchemaObjects(DbObject.TRIGGER)) {
            if (excludeSchema(obj.getSchema())) {
                continue;
            }
            TriggerObject trigger = (TriggerObject) obj;
            if (excludeTable(trigger.getTable())) {
                continue;
            }
            add(trigger.getCreateSQL(), false);
        }
        // Generate GRANT ...
        for (Right right : db.getAllRights()) {
            DbObject object = right.getGrantedObject();
            if (object != null) {
                if (object instanceof Schema) {
                    if (excludeSchema((Schema) object)) {
                        continue;
                    }
                } else if (object instanceof Table) {
                    Table table = (Table) object;
                    if (excludeSchema(table.getSchema())) {
                        continue;
                    }
                    if (excludeTable(table)) {
                        continue;
                    }
                }
            }
            add(right.getCreateSQL(), false);
        }
        // Generate COMMENT ON ...
        for (Comment comment : db.getAllComments()) {
            add(comment.getCreateSQL(), false);
        }
        if (out != null) {
            out.close();
        }
    } catch (IOException e) {
        throw DbException.convertIOException(e, getFileName());
    } finally {
        closeIO();
    }
    result.done();
    LocalResult r = result;
    reset();
    return r;
}
Also used : SchemaObject(org.h2.schema.SchemaObject) User(org.h2.engine.User) Constraint(org.h2.constraint.Constraint) Constant(org.h2.schema.Constant) Schema(org.h2.schema.Schema) Right(org.h2.engine.Right) TriggerObject(org.h2.schema.TriggerObject) Index(org.h2.index.Index) ValueString(org.h2.value.ValueString) LocalResult(org.h2.result.LocalResult) Database(org.h2.engine.Database) Comment(org.h2.engine.Comment) Table(org.h2.table.Table) TableType(org.h2.table.TableType) DbObject(org.h2.engine.DbObject) Setting(org.h2.engine.Setting) UserDataType(org.h2.engine.UserDataType) Sequence(org.h2.schema.Sequence) IOException(java.io.IOException) Constraint(org.h2.constraint.Constraint) Role(org.h2.engine.Role) UserAggregate(org.h2.engine.UserAggregate)

Aggregations

Statement (java.sql.Statement)215 ResultSet (java.sql.ResultSet)204 PreparedStatement (java.sql.PreparedStatement)201 Connection (java.sql.Connection)199 JdbcConnection (org.h2.jdbc.JdbcConnection)97 SimpleResultSet (org.h2.tools.SimpleResultSet)64 SQLException (java.sql.SQLException)56 JdbcStatement (org.h2.jdbc.JdbcStatement)46 JdbcPreparedStatement (org.h2.jdbc.JdbcPreparedStatement)35 Savepoint (java.sql.Savepoint)32 Random (java.util.Random)28 Value (org.h2.value.Value)28 DbException (org.h2.message.DbException)27 Task (org.h2.util.Task)17 Column (org.h2.table.Column)16 ValueString (org.h2.value.ValueString)15 ByteArrayInputStream (java.io.ByteArrayInputStream)14 StringReader (java.io.StringReader)12 InputStream (java.io.InputStream)11 ArrayList (java.util.ArrayList)11