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