private int generateInsertValues(int count, Table table) throws IOException { PlanItem plan = table.getBestPlanItem(session, null, null); Index index = plan.getIndex(); Cursor cursor = index.find(session, null, null); Column[] columns = table.getColumns(); StatementBuilder buff = new StatementBuilder("INSERT INTO "); buff.append(table.getSQL()).append('('); for (Column col : columns) { buff.appendExceptFirst(", "); buff.append(Parser.quoteIdentifier(col.getName())); } buff.append(") VALUES"); if (!simple) { buff.append('\n'); } buff.append('('); String ins = buff.toString(); buff = null; while (cursor.next()) { Row row = cursor.get(); if (buff == null) { buff = new StatementBuilder(ins); } else { buff.append(",\n("); } for (int j = 0; j < row.getColumnCount(); j++) { if (j > 0) { buff.append(", "); } Value v = row.getValue(j); if (v.getPrecision() > lobBlockSize) { int id; if (v.getType() == Value.CLOB) { id = writeLobStream(v); buff.append("SYSTEM_COMBINE_CLOB(" + id + ")"); } else if (v.getType() == Value.BLOB) { id = writeLobStream(v); buff.append("SYSTEM_COMBINE_BLOB(" + id + ")"); } else { buff.append(v.getSQL()); } } else { buff.append(v.getSQL()); } } buff.append(')'); count++; if ((count & 127) == 0) { checkCanceled(); } if (simple || buff.length() > Constants.IO_BUFFER_SIZE) { add(buff.toString(), true); buff = null; } } if (buff != null) { add(buff.toString(), true); } return count; }
public void prepare() { if (columns == null) { if (list.size() > 0 && list.get(0).length == 0) { // special case where table is used as a sequence columns = new Column[0]; } else { columns = table.getColumns(); } } if (list.size() > 0) { for (Expression[] expr : list) { 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(); if (query.getColumnCount() != columns.length) { throw DbException.get(ErrorCode.COLUMN_COUNT_DOES_NOT_MATCH); } } if (keys == null) { Index idx = table.getPrimaryKey(); if (idx == null) { throw DbException.get(ErrorCode.CONSTRAINT_NOT_FOUND_1, "PRIMARY KEY"); } keys = idx.getColumns(); } StatementBuilder buff = new StatementBuilder("UPDATE "); buff.append(table.getSQL()).append(" SET "); for (Column c : columns) { buff.appendExceptFirst(", "); buff.append(c.getSQL()).append("=?"); } buff.append(" WHERE "); buff.resetCount(); for (Column c : keys) { buff.appendExceptFirst(" AND "); buff.append(c.getSQL()).append("=?"); } String sql = buff.toString(); update = session.prepare(sql); }
public int update() { session.commit(true); Database db = session.getDatabase(); session.getUser().checkRight(table, Right.ALL); table.checkSupportAlter(); table.renameColumn(column, newName); table.setModified(); db.update(session, table); for (DbObject child : table.getChildren()) { if (child.getCreateSQL() != null) { db.update(session, child); } } return 0; }
private static Index getIndex(Table t, IndexColumn[] cols) { for (Index idx : t.getIndexes()) { if (canUseIndex(idx, t, cols)) { return idx; } } return null; }
public int update() { int count; session.getUser().checkRight(table, Right.INSERT); session.getUser().checkRight(table, Right.UPDATE); setCurrentRowNumber(0); if (list.size() > 0) { count = 0; for (int x = 0, size = list.size(); x < size; x++) { Expression[] expr = list.get(x); Row newRow; try { newRow = createRow(expr, x); if (newRow == null) { continue; } } catch (DbException ex) { throw setRow(ex, count + 1, getSQL(expr)); } setCurrentRowNumber(++count); merge(newRow); } } else { ResultInterface rows = query.query(0); count = 0; table.fire(session, Trigger.UPDATE | Trigger.INSERT, true); table.lock(session, true, false); while (rows.next()) { Value[] values = rows.currentRow(); Row newRow; try { newRow = createRow(values); if (newRow == null) { continue; } } catch (DbException ex) { throw setRow(ex, count + 1, getSQL(values)); } setCurrentRowNumber(++count); merge(newRow); } rows.close(); table.fire(session, Trigger.UPDATE | Trigger.INSERT, false); } return count; }
protected Row createRow(Value[] values) { Row newRow = table.getTemplateRow(); for (int j = 0; j < columns.length; j++) { Column c = columns[j]; int index = c.getColumnId(); Value v = c.convert(values[j]); newRow.setValue(index, v); } return newRow; }
private Index createIndex(Table t, IndexColumn[] cols, boolean unique) { int indexId = getObjectId(); IndexType indexType; if (unique) { // for unique constraints indexType = IndexType.createUnique(t.isPersistIndexes(), false); } else { // constraints indexType = IndexType.createNonUnique(t.isPersistIndexes()); } indexType.setBelongsToConstraint(true); String prefix = constraintName == null ? "CONSTRAINT" : constraintName; String indexName = t.getSchema().getUniqueIndexName(session, t, prefix + "_INDEX_"); try { return t.addIndex(session, indexName, indexId, cols, indexType, true, null); } finally { getSchema().freeUniqueName(indexName); } }
private void merge(Row row) { ArrayList<Parameter> k = update.getParameters(); for (int i = 0; i < columns.length; i++) { Column col = columns[i]; Value v = row.getValue(col.getColumnId()); Parameter p = k.get(i); p.setValue(v); } for (int i = 0; i < keys.length; i++) { Column col = keys[i]; Value v = row.getValue(col.getColumnId()); if (v == null) { throw DbException.get(ErrorCode.COLUMN_CONTAINS_NULL_VALUES_1, col.getSQL()); } Parameter p = k.get(columns.length + i); p.setValue(v); } int count = update.update(); if (count == 0) { try { table.validateConvertUpdateSequence(session, row); boolean done = table.fireBeforeRow(session, null, row); if (!done) { table.lock(session, true, false); table.addRow(session, row); session.log(table, UndoLogRecord.INSERT, row); table.fireAfterRow(session, null, row, false); } } catch (DbException e) { if (e.getErrorCode() == ErrorCode.DUPLICATE_KEY_1) { // possibly a concurrent merge or insert Index index = (Index) e.getSource(); if (index != null) { // verify the index columns match the key Column[] indexColumns = index.getColumns(); boolean indexMatchesKeys = false; if (indexColumns.length <= keys.length) { for (int i = 0; i < indexColumns.length; i++) { if (indexColumns[i] != keys[i]) { indexMatchesKeys = false; break; } } } if (indexMatchesKeys) { throw DbException.get(ErrorCode.CONCURRENT_UPDATE_1, table.getName()); } } } throw e; } } else if (count != 1) { throw DbException.get(ErrorCode.DUPLICATE_KEY_1, table.getSQL()); } }
protected Row createRow(Expression[] expr, int rowId) { Row newRow = table.getTemplateRow(); for (int i = 0, len = columns.length; i < len; i++) { Column c = columns[i]; int index = c.getColumnId(); Expression e = expr[i]; if (e != null) { // e can be null (DEFAULT) try { Value v = c.convert(e.getValue(session)); newRow.setValue(index, v); } catch (DbException ex) { throw setRow(ex, rowId, getSQL(expr)); } } } return newRow; }
public String getPlanSQL() { StatementBuilder buff = new StatementBuilder("MERGE INTO "); buff.append(table.getSQL()).append('('); for (Column c : columns) { buff.appendExceptFirst(", "); buff.append(c.getSQL()); } buff.append(')'); if (keys != null) { buff.append(" KEY("); buff.resetCount(); for (Column c : keys) { buff.appendExceptFirst(", "); buff.append(c.getSQL()); } buff.append(')'); } buff.append('\n'); if (list.size() > 0) { buff.append("VALUES "); int row = 0; for (Expression[] expr : list) { if (row++ > 0) { buff.append(", "); } buff.append('('); buff.resetCount(); for (Expression e : expr) { buff.appendExceptFirst(", "); if (e == null) { buff.append("DEFAULT"); } else { buff.append(e.getSQL()); } } buff.append(')'); } } else { buff.append(query.getPlanSQL()); } return buff.toString(); }
Constraint(Schema schema, int id, String name, Table table) { initSchemaObjectBase(schema, id, name, Trace.CONSTRAINT); this.table = table; this.setTemporary(table.isTemporary()); }
@Override public boolean isHidden() { return table.isHidden(); }
@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))) { // don't add CREATE_BUILD to the script // (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 String tableType = table.getTableType(); add(createTableSql, false); final ArrayList<Constraint> constraints = table.getConstraints(); if (constraints != null) { for (Constraint constraint : constraints) { if (Constraint.PRIMARY_KEY.equals(constraint.getConstraintType())) { add(constraint.getCreateSQLWithoutIndexes(), false); } } } if (Table.TABLE.equals(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.PRIMARY_KEY.equals(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()) { Table table = right.getGrantedTable(); if (table != null) { 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; }
/** * Try to execute the statement. * * @return the update count */ private int tryUpdate() { if (!transactional) { session.commit(true); } Database db = session.getDatabase(); Table table = getSchema().getTableOrView(session, tableName); if (getSchema().findConstraint(session, constraintName) != null) { if (ifNotExists) { return 0; } throw DbException.get(ErrorCode.CONSTRAINT_ALREADY_EXISTS_1, constraintName); } session.getUser().checkRight(table, Right.ALL); db.lockMeta(session); table.lock(session, true, true); Constraint constraint; switch (type) { case CommandInterface.ALTER_TABLE_ADD_CONSTRAINT_PRIMARY_KEY: { IndexColumn.mapColumns(indexColumns, table); index = table.findPrimaryKey(); ArrayList<Constraint> constraints = table.getConstraints(); for (int i = 0; constraints != null && i < constraints.size(); i++) { Constraint c = constraints.get(i); if (Constraint.PRIMARY_KEY.equals(c.getConstraintType())) { throw DbException.get(ErrorCode.SECOND_PRIMARY_KEY); } } if (index != null) { // if there is an index, it must match with the one declared // we don't test ascending / descending IndexColumn[] pkCols = index.getIndexColumns(); if (pkCols.length != indexColumns.length) { throw DbException.get(ErrorCode.SECOND_PRIMARY_KEY); } for (int i = 0; i < pkCols.length; i++) { if (pkCols[i].column != indexColumns[i].column) { throw DbException.get(ErrorCode.SECOND_PRIMARY_KEY); } } } if (index == null) { IndexType indexType = IndexType.createPrimaryKey(table.isPersistIndexes(), primaryKeyHash); String indexName = table.getSchema().getUniqueIndexName(session, table, Constants.PREFIX_PRIMARY_KEY); int id = getObjectId(); try { index = table.addIndex(session, indexName, id, indexColumns, indexType, true, null); } finally { getSchema().freeUniqueName(indexName); } } index.getIndexType().setBelongsToConstraint(true); int constraintId = getObjectId(); String name = generateConstraintName(table); ConstraintUnique pk = new ConstraintUnique(getSchema(), constraintId, name, table, true); pk.setColumns(indexColumns); pk.setIndex(index, true); constraint = pk; break; } case CommandInterface.ALTER_TABLE_ADD_CONSTRAINT_UNIQUE: { IndexColumn.mapColumns(indexColumns, table); boolean isOwner = false; if (index != null && canUseUniqueIndex(index, table, indexColumns)) { isOwner = true; index.getIndexType().setBelongsToConstraint(true); } else { index = getUniqueIndex(table, indexColumns); if (index == null) { index = createIndex(table, indexColumns, true); isOwner = true; } } int id = getObjectId(); String name = generateConstraintName(table); ConstraintUnique unique = new ConstraintUnique(getSchema(), id, name, table, false); unique.setColumns(indexColumns); unique.setIndex(index, isOwner); constraint = unique; break; } case CommandInterface.ALTER_TABLE_ADD_CONSTRAINT_CHECK: { int id = getObjectId(); String name = generateConstraintName(table); ConstraintCheck check = new ConstraintCheck(getSchema(), id, name, table); TableFilter filter = new TableFilter(session, table, null, false, null); checkExpression.mapColumns(filter, 0); checkExpression = checkExpression.optimize(session); check.setExpression(checkExpression); check.setTableFilter(filter); constraint = check; if (checkExisting) { check.checkExistingData(session); } break; } case CommandInterface.ALTER_TABLE_ADD_CONSTRAINT_REFERENTIAL: { Table refTable = refSchema.getTableOrView(session, refTableName); session.getUser().checkRight(refTable, Right.ALL); if (!refTable.canReference()) { throw DbException.get( ErrorCode.FEATURE_NOT_SUPPORTED_1, "Reference " + refTable.getSQL()); } boolean isOwner = false; IndexColumn.mapColumns(indexColumns, table); if (index != null && canUseIndex(index, table, indexColumns)) { isOwner = true; index.getIndexType().setBelongsToConstraint(true); } else { index = getIndex(table, indexColumns); if (index == null) { index = createIndex(table, indexColumns, false); isOwner = true; } } if (refIndexColumns == null) { Index refIdx = refTable.getPrimaryKey(); refIndexColumns = refIdx.getIndexColumns(); } else { IndexColumn.mapColumns(refIndexColumns, refTable); } if (refIndexColumns.length != indexColumns.length) { throw DbException.get(ErrorCode.COLUMN_COUNT_DOES_NOT_MATCH); } boolean isRefOwner = false; if (refIndex != null && refIndex.getTable() == refTable && canUseIndex(refIndex, refTable, refIndexColumns)) { isRefOwner = true; refIndex.getIndexType().setBelongsToConstraint(true); } else { refIndex = null; } if (refIndex == null) { refIndex = getIndex(refTable, refIndexColumns); if (refIndex == null) { refIndex = createIndex(refTable, refIndexColumns, true); isRefOwner = true; } } int id = getObjectId(); String name = generateConstraintName(table); ConstraintReferential ref = new ConstraintReferential(getSchema(), id, name, table); ref.setColumns(indexColumns); ref.setIndex(index, isOwner); ref.setRefTable(refTable); ref.setRefColumns(refIndexColumns); ref.setRefIndex(refIndex, isRefOwner); if (checkExisting) { ref.checkExistingData(session); } constraint = ref; refTable.addConstraint(constraint); ref.setDeleteAction(deleteAction); ref.setUpdateAction(updateAction); break; } default: throw DbException.throwInternalError("type=" + type); } // parent relationship is already set with addConstraint constraint.setComment(comment); if (table.isTemporary() && !table.isGlobalTemporary()) { session.addLocalTempTableConstraint(constraint); } else { db.addSchemaObject(session, constraint); } table.addConstraint(constraint); return 0; }