HSQLDB example source code file (TableWorks.java)

This example HSQLDB source code file (TableWorks.java) is included in the DevDaily.com "Java Source Code Warehouse" project. The intent of this project is to help you "Learn Java by Example" ^TM.

Java - HSQLDB tags/keywords

column, constraint, constraint, hsqlexception, hsqlexception, hsqlname, hsqlname, idx, index, object, rowiterator, table, table, tableworks

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