|
HSQLDB example source code file (TableWorks.java)
The HSQLDB TableWorks.java source code/* Copyright (c) 2001-2008, The HSQL Development Group * All rights reserved. * * Redistribution and use in source and binary forms, with or without * modification, are permitted provided that the following conditions are met: * * Redistributions of source code must retain the above copyright notice, this * list of conditions and the following disclaimer. * * Redistributions in binary form must reproduce the above copyright notice, * this list of conditions and the following disclaimer in the documentation * and/or other materials provided with the distribution. * * Neither the name of the HSQL Development Group nor the names of its * contributors may be used to endorse or promote products derived from this * software without specific prior written permission. * * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG, * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. */ package org.hsqldb; import org.hsqldb.HsqlNameManager.HsqlName; import org.hsqldb.index.RowIterator; import org.hsqldb.lib.ArrayUtil; import org.hsqldb.lib.HashSet; /** * The methods in this class perform alterations to the structure of an * existing table which may result in a new Table object * * @author fredt@users * @version 1.8.0 * @since 1.7.0 */ class TableWorks { private Table table; private Session session; TableWorks(Session session, Table table) { this.table = table; this.session = session; } Table getTable() { return table; } /** * Creates a foreign key according to current sql.strict_fk or * sql.strong_fk settings. Foreign keys are enforced via indexes on both * the referencing (child) and referenced (parent) tables. * <p> * In versions 1.7.0 and 1.7.1 some non-standard features were supported * for compatibility with older databases. These allowed foreign keys * to be created without the prior existence of a unique constraint on * the referenced columns. * <p> * In version 1.7.2, a unique constraint on the referenced columns must * exist. * * The non-unique index on the referencing table is now always created * whether or not a PK or unique constraint index on the columns exist. * This closes the loopholes opened by the introduction of ALTER TABLE * for adding foreign keys. * * Foriegn keys on temp tables can reference other temp tables with the * same rules above. Foreign keys on permanent tables cannot reference * temp tables. * * Duplicate foreign keys are now disallowed. * * -- The unique index on the referenced table must always belong to a * constraint (PK or UNIQUE). Otherwise after a SHUTDOWN and restart the * index will not exist at the time of creation of the foreign key when * the foreign key is referencing the same table. * * -- The non-unique index on the referencing table is always created * regardless of any existing index. This allows the foreign key * constraint to be dropped when required. * * * (fred@users) * * @param fkcol * @param expcol * @param name foreign key name * @param expTable * @param deleteAction * @param updateAction * @throws HsqlException */ void createForeignKey(int[] fkcol, int[] expcol, HsqlName name, Table mainTable, int deleteAction, int updateAction) throws HsqlException { table.database.schemaManager.checkConstraintExists(name.name, table.getSchemaName(), false); // name check if (table.getConstraint(name.name) != null) { throw Trace.error(Trace.CONSTRAINT_ALREADY_EXISTS); } // existing FK check if (table.getConstraintForColumns(mainTable, expcol, fkcol) != null) { throw Trace.error(Trace.CONSTRAINT_ALREADY_EXISTS); } if (mainTable.isTemp() != table.isTemp()) { throw Trace.error(Trace.FOREIGN_KEY_NOT_ALLOWED); } boolean isSelf = table == mainTable; int offset = table.database.schemaManager.getTableIndex(table); boolean isforward = offset != -1 && offset < table.database.schemaManager.getTableIndex(mainTable); Index exportindex = mainTable.getUniqueConstraintIndexForColumns(expcol); if (exportindex == null) { throw Trace.error(Trace.SQL_CONSTRAINT_REQUIRED, mainTable.getName().statementName); } // existing rows, value checks Constraint.checkReferencedRows(session, table, fkcol, exportindex); // create HsqlName iname = table.database.nameManager.newAutoName("IDX"); Index fkindex = createIndex(fkcol, iname, false, true, isforward); HsqlName pkname = table.database.nameManager.newAutoName("REF", name.name); if (isSelf) { // in case createIndex resulted in new Table object mainTable = table; } Constraint c = new Constraint(pkname, name, mainTable, table, expcol, fkcol, exportindex, fkindex, deleteAction, updateAction); table.addConstraint(c); mainTable.addConstraint(new Constraint(pkname, c)); table.database.schemaManager.registerConstraintName(name.name, table.getName()); } /** * Because of the way indexes and column data are held in memory and * on disk, it is necessary to recreate the table when an index is added * to a non-empty table cached table.<p> * * With empty tables, Index objects are simply added<p> * * With MEOMRY and TEXT tables, a new index is built up and nodes for * earch row are interlinked (fredt@users) * * @param col * @param name * @param unique * @param constraint * @param forward * @return new index * @throws HsqlException normally for lack of resources */ Index createIndex(int[] col, HsqlName name, boolean unique, boolean constraint, boolean forward) throws HsqlException { Index newindex; if (table.isEmpty(session) || table.isIndexingMutable()) { newindex = table.createIndex(session, col, name, unique, constraint, forward); table.database.schemaManager.clearTempTables(session, table); } else { Table tn = table.moveDefinition(null, null, -1, 0); newindex = tn.createIndexStructure(col, name, unique, constraint, forward); tn.moveData(session, table, -1, 0); tn.updateConstraintsTables(session, table, -1, 0); int index = table.database.schemaManager.getTableIndex(table); table.database.schemaManager.setTable(index, tn); table = tn; } table.database.schemaManager.clearTempTables(session, table); table.database.schemaManager.registerIndexName(newindex.getName().name, table.getName()); table.database.schemaManager.recompileViews(table); return newindex; } void addPrimaryKey(int[] cols, HsqlName name) throws HsqlException { if (name == null) { name = table.makeSysPKName(); } table.database.schemaManager.checkConstraintExists(name.name, table.getSchemaName(), false); addOrDropPrimaryKey(cols, false); Constraint newconstraint = new Constraint(name, table, table.getPrimaryIndex(), Constraint.PRIMARY_KEY); table.addConstraint(newconstraint); table.database.schemaManager.registerConstraintName(name.name, table.getName()); } void addOrDropPrimaryKey(int[] cols, boolean identity) throws HsqlException { if (cols == null) { table.checkDropIndex(table.getIndexes()[0].getName().name, null, true); } Table tn = table.moveDefinitionPK(cols, identity); tn.moveData(session, table, -1, 0); tn.updateConstraintsTables(session, table, -1, 0); int index = table.database.schemaManager.getTableIndex(table); table.database.schemaManager.setTable(index, tn); table = tn; table.database.schemaManager.recompileViews(table); } /** * A unique constraint relies on a unique indexe on the table. It can * cover a single column or multiple columns. * <p> * All unique constraint names are generated by Database.java as unique * within the database. Duplicate constraints (more than one unique * constriant on the same set of columns are still allowed but the * names will be different. (fredt@users) * * @param col * @param name * @throws HsqlException */ void createUniqueConstraint(int[] col, HsqlName name) throws HsqlException { table.database.schemaManager.checkConstraintExists(name.name, table.getSchemaName(), false); Constraint[] constraints = table.getConstraints(); for (int i = 0, size = constraints.length; i < size; i++) { Constraint c = constraints[i]; if (c.isEquivalent(col, Constraint.UNIQUE) || c.getName().name.equals(name.name)) { throw Trace.error(Trace.CONSTRAINT_ALREADY_EXISTS); } } // create an autonamed index HsqlName indexname = table.database.nameManager.newAutoName("IDX", name.name); Index index = createIndex(col, indexname, true, true, false); Constraint newconstraint = new Constraint(name, table, index, Constraint.UNIQUE); table.addConstraint(newconstraint); table.database.schemaManager.registerConstraintName(name.name, table.getName()); } void createCheckConstraint(Constraint c, HsqlName name) throws HsqlException { table.database.schemaManager.checkConstraintExists(name.name, table.getSchemaName(), false); // check the existing rows Expression e = c.core.check; // this workaround is here to stop LIKE optimisation (for proper scripting) e.setLikeOptimised(); Select s = Expression.getCheckSelect(session, table, e); Result r = s.getResult(session, 1); c.core.checkFilter = s.tFilter[0]; c.core.mainTable = table; if (r.getSize() != 0) { throw Trace.error(Trace.CHECK_CONSTRAINT_VIOLATION); } // getDDL() is here to ensure no subselects etc. are in condition e.getDDL(); // removes reference to the Index object in filter c.core.checkFilter.setAsCheckFilter(); table.addConstraint(c); table.database.schemaManager.registerConstraintName(name.name, table.getName()); } /** * Because of the way indexes and column data are held in memory and * on disk, it is necessary to recreate the table when an index is added * to a non-empty table.<p> * Originally, this method would break existing foreign keys as the * table order in the DB was changed. The new table is now linked * in place of the old table (fredt@users) * * @param indexname * @throws HsqlException */ void dropIndex(String indexname) throws HsqlException { if (table.isIndexingMutable()) { table.dropIndex(session, indexname); } else { int[] removeIndex = new int[]{ table.getIndexIndex(indexname) }; Table tn = table.moveDefinition(removeIndex, null, -1, 0); tn.moveData(session, table, -1, 0); tn.updateConstraintsTables(session, table, -1, 0); int i = table.database.schemaManager.getTableIndex(table); table.database.schemaManager.setTable(i, tn); table = tn; } table.database.schemaManager.removeIndexName(indexname, table.getName()); table.database.schemaManager.recompileViews(table); } /** * * @param column * @param colindex * @throws HsqlException */ void retypeColumn(Column column, int colindex) throws HsqlException { if (table.isText() && !table.isEmpty(session)) { throw Trace.error(Trace.OPERATION_NOT_SUPPORTED); } table.database.schemaManager.checkColumnIsInView(table, table.getColumn(colindex).columnName.name); table.checkColumnInCheckConstraint( table.getColumn(colindex).columnName.name); int[] dropIndexes = null; Table tn = table.moveDefinition(dropIndexes, column, colindex, 0); tn.moveData(session, table, colindex, 0); tn.updateConstraintsTables(session, table, colindex, 0); int i = table.database.schemaManager.getTableIndex(table); table.database.schemaManager.setTable(i, tn); table = tn; table.database.schemaManager.recompileViews(table); } /** * * @param colIndex * @throws HsqlException */ void dropColumn(int colIndex) throws HsqlException { HsqlName constNameRemove = null; if (table.isText() && !table.isEmpty(session)) { throw Trace.error(Trace.OPERATION_NOT_SUPPORTED); } table.database.schemaManager.checkColumnIsInView(table, table.getColumn(colIndex).columnName.name); table.checkColumnInCheckConstraint( table.getColumn(colIndex).columnName.name); Table tn = table; int[] dropIndexes = null; String colName = tn.getColumn(colIndex).columnName.name; tn.checkColumnInFKConstraint(colIndex); if (table.getPrimaryKey().length == 1 && table.getPrimaryKey()[0] == colIndex) { table.checkDropIndex(table.getIndex(0).getName().name, null, true); constNameRemove = table.constraintList[0].getName(); tn = table.moveDefinitionPK(null, false); } Constraint c = tn.getUniqueConstraintForColumns(new int[]{ colIndex }); if (c != null) { Index idx = c.getMainIndex(); dropIndexes = new int[]{ tn.getIndexIndex(idx.getName().name) }; constNameRemove = c.getName(); } tn = tn.moveDefinition(dropIndexes, null, colIndex, -1); tn.moveData(session, table, colIndex, -1); tn.updateConstraintsTables(session, table, colIndex, -1); int i = table.database.schemaManager.getTableIndex(table); table.database.schemaManager.setTable(i, tn); table = tn; table.database.schemaManager.recompileViews(table); if (constNameRemove != null) { table.removeConstraint(constNameRemove.name); table.database.schemaManager.removeConstraintName( constNameRemove.name, table.getName()); } } /** * * @param column * @param colIndex * @throws HsqlException */ void addColumn(Column column, int colIndex) throws HsqlException { if (table.isText() && !table.isEmpty(session)) { throw Trace.error(Trace.OPERATION_NOT_SUPPORTED); } Table tn = table; tn = tn.moveDefinition(null, column, colIndex, 1); if (column.isPrimaryKey()) { tn = tn.moveDefinitionPK(new int[]{ colIndex }, true); } tn.moveData(session, table, colIndex, 1); tn.updateConstraintsTables(session, table, colIndex, 1); int i = table.database.schemaManager.getTableIndex(table); table.database.schemaManager.setTable(i, tn); table = tn; table.database.schemaManager.recompileViews(table); if (column.isPrimaryKey()) { HsqlName pkNameAdd = tn.makeSysPKName(); Constraint newconstraint = new Constraint(pkNameAdd, table, table.getPrimaryIndex(), Constraint.PRIMARY_KEY); table.addConstraint(newconstraint); table.database.schemaManager.registerConstraintName(pkNameAdd.name, table.getName()); } } /** * Drop a named constraint * */ void dropConstraint(String name) throws HsqlException { Constraint c = table.getConstraint(name); int ctype; if (c == null) { throw Trace.error(Trace.CONSTRAINT_NOT_FOUND, Trace.TableWorks_dropConstraint, new Object[] { name, table.getName().name }); } ctype = c.getType(); if (ctype == Constraint.MAIN) { throw Trace.error(Trace.DROP_SYSTEM_CONSTRAINT); } if (ctype == Constraint.PRIMARY_KEY) { addOrDropPrimaryKey(null, false); table.removeConstraint(name); } else if (ctype == Constraint.FOREIGN_KEY) { dropFKConstraint(c); } else if (ctype == Constraint.UNIQUE) { HashSet cset = new HashSet(); cset.add(c); // throw if the index for unique constraint is shared table.checkDropIndex(c.getMainIndex().getName().name, cset, false); // all is well if dropIndex throws for lack of resources dropIndex(c.getMainIndex().getName().name); table.removeConstraint(name); } else if (ctype == Constraint.CHECK) { table.removeConstraint(name); } table.database.schemaManager.removeConstraintName(name, table.getName()); } void dropFKConstraint(Constraint c) throws HsqlException { // drop the reference index, which is automatic and unused elsewhere Index constIndex = c.getRefIndex(); // all is well if dropIndex throws for lack of resources dropIndex(constIndex.getName().name); Table mainTable = c.getMain(); // MAIN constraint was created after REF, so delete first mainTable.removeConstraint(c.getPkName()); table.removeConstraint(c.getFkName()); } void reTypeColumn(Column oldCol, Column newCol) throws HsqlException { boolean notallowed = false; int oldtype = oldCol.getType(); int newtype = newCol.getType(); switch (newtype) { case Types.BINARY : case Types.VARBINARY : case Types.LONGVARBINARY : case Types.OTHER : case Types.JAVA_OBJECT : notallowed = !(newtype == oldtype || table.isEmpty(session)); } switch (oldtype) { case Types.BINARY : case Types.VARBINARY : case Types.LONGVARBINARY : case Types.OTHER : case Types.JAVA_OBJECT : notallowed = !(newtype == oldtype || table.isEmpty(session)); break; case Types.TINYINT : case Types.SMALLINT : case Types.INTEGER : case Types.BIGINT : case Types.REAL : case Types.FLOAT : case Types.DOUBLE : case Types.NUMERIC : case Types.DECIMAL : switch (newtype) { case Types.DATE : case Types.TIME : case Types.TIMESTAMP : notallowed = !table.isEmpty(session); default : } break; case Types.DATE : case Types.TIME : case Types.TIMESTAMP : switch (newtype) { case Types.TINYINT : case Types.SMALLINT : case Types.INTEGER : case Types.BIGINT : case Types.REAL : case Types.FLOAT : case Types.DOUBLE : case Types.NUMERIC : case Types.DECIMAL : notallowed = !table.isEmpty(session); default : } break; } if (notallowed) { throw Trace.error(Trace.INVALID_CONVERSION); } int colIndex = table.getColumnNr(oldCol.columnName.name); if (table.getPrimaryKey().length > 1) { // if there is a multi-column PK, do not change the PK attributes if (newCol.isIdentity()) { throw Trace.error(Trace.SECOND_PRIMARY_KEY); } newCol.setPrimaryKey(oldCol.isPrimaryKey()); if (ArrayUtil.find(table.getPrimaryKey(), colIndex) != -1) { newCol.setNullable(false); } } else if (table.hasPrimaryKey()) { if (oldCol.isPrimaryKey()) { newCol.setPrimaryKey(true); newCol.setNullable(false); } else if (newCol.isPrimaryKey()) { throw Trace.error(Trace.SECOND_PRIMARY_KEY); } } else if (newCol.isPrimaryKey()) { throw Trace.error(Trace.PRIMARY_KEY_NOT_ALLOWED); } // apply and return if only metadata change is required if (newtype == oldtype && oldCol.isNullable() == newCol.isNullable() && oldCol.getScale() == newCol.getScale() && oldCol.isIdentity() == newCol.isIdentity() && oldCol.identityIncrement == newCol.identityIncrement && (oldCol.getSize() == newCol.getSize() || (oldCol.getSize() < newCol.getSize() && (oldtype == Types.VARCHAR || oldtype == Types.DECIMAL || oldtype == Types.NUMERIC)))) { // size of some types may be increased with this command // default expressions can change oldCol.setType(newCol); oldCol.setDefaultExpression(newCol.getDefaultExpression()); table.colSizes[colIndex] = oldCol.getSize(); table.colDefaults[colIndex] = oldCol.getDefaultExpression(); table.resetDefaultsFlag(); return; } table.database.schemaManager.checkColumnIsInView(table, table.getColumn(colIndex).columnName.name); table.checkColumnInCheckConstraint( table.getColumn(colIndex).columnName.name); table.checkColumnInFKConstraint(colIndex); checkConvertColDataType(oldCol, newCol); retypeColumn(newCol, colIndex); } void checkConvertColDataType(Column oldCol, Column newCol) throws HsqlException { int colIndex = table.getColumnNr(oldCol.columnName.name); RowIterator it = table.getPrimaryIndex().firstRow(session); while (it.hasNext()) { Row row = it.next(); Object o = row.getData()[colIndex]; Column.convertObject(session, o, newCol.getType(), newCol.getSize(), newCol.getScale()); } } /** * performs the work for changing the nullability of a column */ void setColNullability(Column column, boolean nullable) throws HsqlException { int colIndex = table.getColumnNr(column.columnName.name); if (nullable) { if (column.isPrimaryKey()) { throw Trace.error(Trace.TRY_TO_INSERT_NULL); } table.checkColumnInFKConstraint(colIndex, Constraint.SET_NULL); } else { RowIterator it = table.getPrimaryIndex().firstRow(session); while (it.hasNext()) { Row row = it.next(); Object o = row.getData()[colIndex]; if (o == null) { throw Trace.error(Trace.TRY_TO_INSERT_NULL); } } } column.setNullable(nullable); table.setColumnTypeVars(colIndex); } /** * performs the work for changing the default value of a column */ void setColDefaultExpression(int colIndex, Expression def) throws HsqlException { if (def == null) { table.checkColumnInFKConstraint(colIndex, Constraint.SET_DEFAULT); } table.setDefaultExpression(colIndex, def); } } Other HSQLDB examples (source code examples)Here is a short list of links related to this HSQLDB TableWorks.java source code file: |
... this post is sponsored by my books ... | |
#1 New Release! |
FP Best Seller |
Copyright 1998-2024 Alvin Alexander, alvinalexander.com
All Rights Reserved.
A percentage of advertising revenue from
pages under the /java/jwarehouse
URI on this website is
paid back to open source projects.