Examples with CreateTable org.h2.command.ddl.CreateTable used on opensource projects

Example 1 with CreateTable

use of org.h2.command.ddl.CreateTable in project ignite by apache.

the class GridQueryParsingTest method checkCreateTable.

/**
     * @param qry Query.
     */
private void checkCreateTable(String qry) throws Exception {
    Prepared prepared = parse(qry);
    assertTrue(prepared instanceof CreateTable);
    GridSqlStatement gridStmt = new GridSqlQueryParser(false).parse(prepared);
    String res = createTableToSql((GridSqlCreateTable) gridStmt);
    System.out.println(normalizeSql(res));
    assertSqlEquals(U.firstNotNull(prepared.getPlanSQL(), prepared.getSQL()), res);
}

Example 2 with CreateTable

use of org.h2.command.ddl.CreateTable in project nifi by apache.

the class DBCPServiceTest method createInsertSelectDrop.

protected void createInsertSelectDrop(Connection con) throws SQLException {
    final Statement st = con.createStatement();
    try {
        st.executeUpdate(dropTable);
    } catch (final Exception e) {
    // table may not exist, this is not serious problem.
    }
    st.executeUpdate(createTable);
    st.executeUpdate("insert into restaurants values (1, 'Irifunes', 'San Mateo')");
    st.executeUpdate("insert into restaurants values (2, 'Estradas', 'Daly City')");
    st.executeUpdate("insert into restaurants values (3, 'Prime Rib House', 'San Francisco')");
    int nrOfRows = 0;
    final ResultSet resultSet = st.executeQuery("select * from restaurants");
    while (resultSet.next()) nrOfRows++;
    assertEquals(3, nrOfRows);
    st.close();
}

Example 3 with CreateTable

use of org.h2.command.ddl.CreateTable in project h2database by h2database.

the class Parser method parseCreateTable.

private CreateTable parseCreateTable(boolean temp, boolean globalTemp, boolean persistIndexes) {
    boolean ifNotExists = readIfNotExists();
    String tableName = readIdentifierWithSchema();
    if (temp && globalTemp && equalsToken("SESSION", schemaName)) {
        // support weird syntax: declare global temporary table session.xy
        // (...) not logged
        schemaName = session.getCurrentSchemaName();
        globalTemp = false;
    }
    Schema schema = getSchema();
    CreateTable command = new CreateTable(session, schema);
    command.setPersistIndexes(persistIndexes);
    command.setTemporary(temp);
    command.setGlobalTemporary(globalTemp);
    command.setIfNotExists(ifNotExists);
    command.setTableName(tableName);
    command.setComment(readCommentIf());
    if (readIf("(")) {
        if (!readIf(")")) {
            do {
                parseTableColumnDefinition(command, schema, tableName);
            } while (readIfMore(false));
        }
    }
    // Allows "COMMENT='comment'" in DDL statements (MySQL syntax)
    if (readIf("COMMENT")) {
        if (readIf("=")) {
            // read the complete string comment, but nothing with it for now
            readString();
        }
    }
    if (readIf("ENGINE")) {
        if (readIf("=")) {
            // map MySQL engine types onto H2 behavior
            String tableEngine = readUniqueIdentifier();
            if ("InnoDb".equalsIgnoreCase(tableEngine)) {
            // ok
            } else if (!"MyISAM".equalsIgnoreCase(tableEngine)) {
                throw DbException.getUnsupportedException(tableEngine);
            }
        } else {
            command.setTableEngine(readUniqueIdentifier());
        }
    }
    if (readIf("WITH")) {
        command.setTableEngineParams(readTableEngineParams());
    }
    // MySQL compatibility
    if (readIf("AUTO_INCREMENT")) {
        read("=");
        if (currentTokenType != VALUE || currentValue.getType() != Value.INT) {
            throw DbException.getSyntaxError(sqlCommand, parseIndex, "integer");
        }
        read();
    }
    readIf("DEFAULT");
    if (readIf("CHARSET")) {
        read("=");
        if (!readIf("UTF8")) {
            read("UTF8MB4");
        }
    }
    if (temp) {
        if (readIf("ON")) {
            read("COMMIT");
            if (readIf("DROP")) {
                command.setOnCommitDrop();
            } else if (readIf("DELETE")) {
                read("ROWS");
                command.setOnCommitTruncate();
            }
        } else if (readIf("NOT")) {
            if (readIf("PERSISTENT")) {
                command.setPersistData(false);
            } else {
                read("LOGGED");
            }
        }
        if (readIf("TRANSACTIONAL")) {
            command.setTransactional(true);
        }
    } else if (!persistIndexes && readIf("NOT")) {
        read("PERSISTENT");
        command.setPersistData(false);
    }
    if (readIf("HIDDEN")) {
        command.setHidden(true);
    }
    if (readIf("AS")) {
        if (readIf("SORTED")) {
            command.setSortedInsertMode(true);
        }
        command.setQuery(parseSelect());
    }
    // for MySQL compatibility
    if (readIf("ROW_FORMAT")) {
        if (readIf("=")) {
            readColumnIdentifier();
        }
    }
    return command;
}

Example 4 with CreateTable

use of org.h2.command.ddl.CreateTable 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 5 with CreateTable

use of org.h2.command.ddl.CreateTable in project h2database by h2database.

the class CreateTable method update.

@Override
public int update() {
    if (!transactional) {
        session.commit(true);
    }
    Database db = session.getDatabase();
    if (!db.isPersistent()) {
        data.persistIndexes = false;
    }
    boolean isSessionTemporary = data.temporary && !data.globalTemporary;
    if (!isSessionTemporary) {
        db.lockMeta(session);
    }
    if (getSchema().resolveTableOrView(session, data.tableName) != null) {
        if (ifNotExists) {
            return 0;
        }
        throw DbException.get(ErrorCode.TABLE_OR_VIEW_ALREADY_EXISTS_1, data.tableName);
    }
    if (asQuery != null) {
        asQuery.prepare();
        if (data.columns.isEmpty()) {
            generateColumnsFromQuery();
        } else if (data.columns.size() != asQuery.getColumnCount()) {
            throw DbException.get(ErrorCode.COLUMN_COUNT_DOES_NOT_MATCH);
        }
    }
    changePrimaryKeysToNotNull(data.columns);
    data.id = getObjectId();
    data.create = create;
    data.session = session;
    Table table = getSchema().createTable(data);
    ArrayList<Sequence> sequences = generateSequences(data.columns, data.temporary);
    table.setComment(comment);
    if (isSessionTemporary) {
        if (onCommitDrop) {
            table.setOnCommitDrop(true);
        }
        if (onCommitTruncate) {
            table.setOnCommitTruncate(true);
        }
        session.addLocalTempTable(table);
    } else {
        db.lockMeta(session);
        db.addSchemaObject(session, table);
    }
    try {
        for (Column c : data.columns) {
            c.prepareExpression(session);
        }
        for (Sequence sequence : sequences) {
            table.addSequence(sequence);
        }
        createConstraints();
        if (asQuery != null) {
            boolean old = session.isUndoLogEnabled();
            try {
                session.setUndoLogEnabled(false);
                session.startStatementWithinTransaction();
                Insert insert = new Insert(session);
                insert.setSortedInsertMode(sortedInsertMode);
                insert.setQuery(asQuery);
                insert.setTable(table);
                insert.setInsertFromSelect(true);
                insert.prepare();
                insert.update();
            } finally {
                session.setUndoLogEnabled(old);
            }
        }
        HashSet<DbObject> set = new HashSet<>();
        set.clear();
        table.addDependencies(set);
        for (DbObject obj : set) {
            if (obj == table) {
                continue;
            }
            if (obj.getType() == DbObject.TABLE_OR_VIEW) {
                if (obj instanceof Table) {
                    Table t = (Table) obj;
                    if (t.getId() > table.getId()) {
                        throw DbException.get(ErrorCode.FEATURE_NOT_SUPPORTED_1, "Table depends on another table " + "with a higher ID: " + t + ", this is currently not supported, " + "as it would prevent the database from " + "being re-opened");
                    }
                }
            }
        }
    } catch (DbException e) {
        db.checkPowerOff();
        db.removeSchemaObject(session, table);
        if (!transactional) {
            session.commit(true);
        }
        throw e;
    }
    return 0;
}