Examples with Insert org.h2.command.dml.Insert used on opensource projects

Example 6 with Insert

use of org.h2.command.dml.Insert in project ignite by apache.

the class DmlAstUtils method selectForInsertOrMerge.

/**
 * Create SELECT on which subsequent INSERT or MERGE will be based.
 *
 * @param cols Columns to insert values into.
 * @param rows Rows to create pseudo-SELECT upon.
 * @param subQry Subquery to use rather than rows.
 * @return Subquery or pseudo-SELECT to evaluate inserted expressions, or {@code null} no query needs to be run.
 */
public static GridSqlQuery selectForInsertOrMerge(GridSqlColumn[] cols, List<GridSqlElement[]> rows, GridSqlQuery subQry) {
    if (!F.isEmpty(rows)) {
        assert !F.isEmpty(cols);
        GridSqlSelect sel = new GridSqlSelect();
        GridSqlFunction from = new GridSqlFunction(GridSqlFunctionType.TABLE);
        sel.from(from);
        GridSqlArray[] args = new GridSqlArray[cols.length];
        boolean noQry = true;
        for (int i = 0; i < cols.length; i++) {
            GridSqlArray arr = new GridSqlArray(rows.size());
            String colName = cols[i].columnName();
            GridSqlAlias alias = new GridSqlAlias(colName, arr);
            alias.resultType(cols[i].resultType());
            from.addChild(alias);
            args[i] = arr;
            GridSqlColumn newCol = new GridSqlColumn(null, from, null, "TABLE", colName);
            newCol.resultType(cols[i].resultType());
            sel.addColumn(newCol, true);
        }
        for (GridSqlElement[] row : rows) {
            assert cols.length == row.length;
            for (int i = 0; i < row.length; i++) {
                GridSqlElement el = row[i];
                noQry &= (el instanceof GridSqlConst || el instanceof GridSqlParameter);
                args[i].addChild(row[i]);
            }
        }
        if (noQry)
            return null;
        return sel;
    } else {
        assert subQry != null;
        return subQry;
    }
}

Example 7 with Insert

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

the class Parser method parseMergeUsing.

private MergeUsing parseMergeUsing(Merge oldCommand, int start) {
    MergeUsing command = new MergeUsing(oldCommand);
    currentPrepared = command;
    if (readIf("(")) {
        /* a select query is supplied */
        if (isSelect()) {
            command.setQuery(parseSelect());
            read(")");
        }
        command.setQueryAlias(readFromAlias(null, Collections.singletonList("ON")));
        String[] querySQLOutput = { null };
        List<Column> columnTemplateList = TableView.createQueryColumnTemplateList(null, command.getQuery(), querySQLOutput);
        TableView temporarySourceTableView = createCTEView(command.getQueryAlias(), querySQLOutput[0], columnTemplateList, false, /* no recursion */
        false, /* do not add to session */
        false, /* isPersistent */
        session);
        TableFilter sourceTableFilter = new TableFilter(session, temporarySourceTableView, command.getQueryAlias(), rightsChecked, (Select) command.getQuery(), 0, null);
        command.setSourceTableFilter(sourceTableFilter);
    } else {
        /* Its a table name, simulate a query by building a select query for the table */
        List<String> excludeIdentifiers = Collections.singletonList("ON");
        TableFilter sourceTableFilter = readSimpleTableFilter(0, excludeIdentifiers);
        command.setSourceTableFilter(sourceTableFilter);
        StringBuilder buff = new StringBuilder("SELECT * FROM ");
        appendTableWithSchemaAndAlias(buff, sourceTableFilter.getTable(), sourceTableFilter.getTableAlias());
        Prepared preparedQuery = prepare(session, buff.toString(), null);
        command.setQuery((Select) preparedQuery);
    }
    read("ON");
    read("(");
    Expression condition = readExpression();
    command.setOnCondition(condition);
    read(")");
    if (readIfAll("WHEN", "MATCHED", "THEN")) {
        int startMatched = lastParseIndex;
        if (readIf("UPDATE")) {
            Update updateCommand = new Update(session);
            // currentPrepared = updateCommand;
            TableFilter filter = command.getTargetTableFilter();
            updateCommand.setTableFilter(filter);
            parseUpdateSetClause(updateCommand, filter, startMatched);
            command.setUpdateCommand(updateCommand);
        }
        startMatched = lastParseIndex;
        if (readIf("DELETE")) {
            Delete deleteCommand = new Delete(session);
            TableFilter filter = command.getTargetTableFilter();
            deleteCommand.setTableFilter(filter);
            parseDeleteGivenTable(deleteCommand, null, startMatched);
            command.setDeleteCommand(deleteCommand);
        }
    }
    if (readIfAll("WHEN", "NOT", "MATCHED", "THEN")) {
        if (readIf("INSERT")) {
            Insert insertCommand = new Insert(session);
            insertCommand.setTable(command.getTargetTable());
            parseInsertGivenTable(insertCommand, command.getTargetTable());
            command.setInsertCommand(insertCommand);
        }
    }
    setSQL(command, "MERGE", start);
    // build and prepare the targetMatchQuery ready to test each rows
    // existence in the target table (using source row to match)
    StringBuilder targetMatchQuerySQL = new StringBuilder("SELECT _ROWID_ FROM ");
    appendTableWithSchemaAndAlias(targetMatchQuerySQL, command.getTargetTable(), command.getTargetTableFilter().getTableAlias());
    targetMatchQuerySQL.append(" WHERE ").append(command.getOnCondition().getSQL());
    command.setTargetMatchQuery((Select) parse(targetMatchQuerySQL.toString()));
    return command;
}

Example 8 with Insert

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

the class Parser method parsePrepared.

private Prepared parsePrepared() {
    int start = lastParseIndex;
    Prepared c = null;
    String token = currentToken;
    if (token.length() == 0) {
        c = new NoOperation(session);
    } else {
        char first = token.charAt(0);
        switch(first) {
            case '?':
                // read the ? as a parameter
                readTerm();
                // this is an 'out' parameter - set a dummy value
                parameters.get(0).setValue(ValueNull.INSTANCE);
                read("=");
                read("CALL");
                c = parseCall();
                break;
            case '(':
                c = parseSelect();
                break;
            case 'a':
            case 'A':
                if (readIf("ALTER")) {
                    c = parseAlter();
                } else if (readIf("ANALYZE")) {
                    c = parseAnalyze();
                }
                break;
            case 'b':
            case 'B':
                if (readIf("BACKUP")) {
                    c = parseBackup();
                } else if (readIf("BEGIN")) {
                    c = parseBegin();
                }
                break;
            case 'c':
            case 'C':
                if (readIf("COMMIT")) {
                    c = parseCommit();
                } else if (readIf("CREATE")) {
                    c = parseCreate();
                } else if (readIf("CALL")) {
                    c = parseCall();
                } else if (readIf("CHECKPOINT")) {
                    c = parseCheckpoint();
                } else if (readIf("COMMENT")) {
                    c = parseComment();
                }
                break;
            case 'd':
            case 'D':
                if (readIf("DELETE")) {
                    c = parseDelete();
                } else if (readIf("DROP")) {
                    c = parseDrop();
                } else if (readIf("DECLARE")) {
                    // support for DECLARE GLOBAL TEMPORARY TABLE...
                    c = parseCreate();
                } else if (readIf("DEALLOCATE")) {
                    c = parseDeallocate();
                }
                break;
            case 'e':
            case 'E':
                if (readIf("EXPLAIN")) {
                    c = parseExplain();
                } else if (readIf("EXECUTE")) {
                    c = parseExecute();
                }
                break;
            case 'f':
            case 'F':
                if (isToken("FROM")) {
                    c = parseSelect();
                }
                break;
            case 'g':
            case 'G':
                if (readIf("GRANT")) {
                    c = parseGrantRevoke(CommandInterface.GRANT);
                }
                break;
            case 'h':
            case 'H':
                if (readIf("HELP")) {
                    c = parseHelp();
                }
                break;
            case 'i':
            case 'I':
                if (readIf("INSERT")) {
                    c = parseInsert();
                }
                break;
            case 'm':
            case 'M':
                if (readIf("MERGE")) {
                    c = parseMerge();
                }
                break;
            case 'p':
            case 'P':
                if (readIf("PREPARE")) {
                    c = parsePrepare();
                }
                break;
            case 'r':
            case 'R':
                if (readIf("ROLLBACK")) {
                    c = parseRollback();
                } else if (readIf("REVOKE")) {
                    c = parseGrantRevoke(CommandInterface.REVOKE);
                } else if (readIf("RUNSCRIPT")) {
                    c = parseRunScript();
                } else if (readIf("RELEASE")) {
                    c = parseReleaseSavepoint();
                } else if (readIf("REPLACE")) {
                    c = parseReplace();
                }
                break;
            case 's':
            case 'S':
                if (isToken("SELECT")) {
                    c = parseSelect();
                } else if (readIf("SET")) {
                    c = parseSet();
                } else if (readIf("SAVEPOINT")) {
                    c = parseSavepoint();
                } else if (readIf("SCRIPT")) {
                    c = parseScript();
                } else if (readIf("SHUTDOWN")) {
                    c = parseShutdown();
                } else if (readIf("SHOW")) {
                    c = parseShow();
                }
                break;
            case 't':
            case 'T':
                if (readIf("TRUNCATE")) {
                    c = parseTruncate();
                }
                break;
            case 'u':
            case 'U':
                if (readIf("UPDATE")) {
                    c = parseUpdate();
                } else if (readIf("USE")) {
                    c = parseUse();
                }
                break;
            case 'v':
            case 'V':
                if (readIf("VALUES")) {
                    c = parseValues();
                }
                break;
            case 'w':
            case 'W':
                if (readIf("WITH")) {
                    c = parseWithStatementOrQuery();
                }
                break;
            case ';':
                c = new NoOperation(session);
                break;
            default:
                throw getSyntaxError();
        }
        if (indexedParameterList != null) {
            for (int i = 0, size = indexedParameterList.size(); i < size; i++) {
                if (indexedParameterList.get(i) == null) {
                    indexedParameterList.set(i, new Parameter(i));
                }
            }
            parameters = indexedParameterList;
        }
        if (readIf("{")) {
            do {
                int index = (int) readLong() - 1;
                if (index < 0 || index >= parameters.size()) {
                    throw getSyntaxError();
                }
                Parameter p = parameters.get(index);
                if (p == null) {
                    throw getSyntaxError();
                }
                read(":");
                Expression expr = readExpression();
                expr = expr.optimize(session);
                p.setValue(expr.getValue(session));
            } while (readIf(","));
            read("}");
            for (Parameter p : parameters) {
                p.checkSet();
            }
            parameters.clear();
        }
    }
    if (c == null) {
        throw getSyntaxError();
    }
    setSQL(c, null, start);
    return c;
}

Example 9 with Insert

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

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

the class Parser method parseInsertGivenTable.

private Insert parseInsertGivenTable(Insert command, Table table) {
    Column[] columns = null;
    if (readIf("(")) {
        if (isSelect()) {
            command.setQuery(parseSelect());
            read(")");
            return command;
        }
        columns = parseColumnList(table);
        command.setColumns(columns);
    }
    if (readIf("DIRECT")) {
        command.setInsertFromSelect(true);
    }
    if (readIf("SORTED")) {
        command.setSortedInsertMode(true);
    }
    if (readIf("DEFAULT")) {
        read("VALUES");
        Expression[] expr = {};
        command.addRow(expr);
    } else if (readIf("VALUES")) {
        read("(");
        do {
            ArrayList<Expression> values = New.arrayList();
            if (!readIf(")")) {
                do {
                    if (readIf("DEFAULT")) {
                        values.add(null);
                    } else {
                        values.add(readExpression());
                    }
                } while (readIfMore(false));
            }
            command.addRow(values.toArray(new Expression[0]));
        // the following condition will allow (..),; and (..);
        } while (readIf(",") && readIf("("));
    } else if (readIf("SET")) {
        if (columns != null) {
            throw getSyntaxError();
        }
        ArrayList<Column> columnList = New.arrayList();
        ArrayList<Expression> values = New.arrayList();
        do {
            columnList.add(parseColumn(table));
            read("=");
            Expression expression;
            if (readIf("DEFAULT")) {
                expression = ValueExpression.getDefault();
            } else {
                expression = readExpression();
            }
            values.add(expression);
        } while (readIf(","));
        command.setColumns(columnList.toArray(new Column[0]));
        command.addRow(values.toArray(new Expression[0]));
    } else {
        command.setQuery(parseSelect());
    }
    return null;
}