ALTER INDEX <indexname> RENAME TO <newname>;

Index names can be changed so long as they do not conflict with other user-defined or sytem-defined names.

ALTER SEQUENCE <sequencename> RESTART WITH <value>;

Resets the next value to be returned from the sequence.

ALTER SCHEMA <schemaname> RENAME TO <newname>;

Renames schema as specified. All objects of the schema will hereafter be accessible only with the new schema name.

Requires Administrative privileges.

ALTER TABLE <tablename> ADD [COLUMN] <columnname> Datatype [(columnSize[,precision])]
    [{DEFAULT <defaultValue> |
    GENERATED BY DEFAULT AS IDENTITY (START WITH <n>[, INCREMENT BY <m>])}] |
    [[NOT] NULL] [IDENTITY] [PRIMARY KEY]
    [BEFORE <existingcolumn>];

Adds the column to the end of the column list. The optional BEFORE <existingcolumn> can be used to specify the name of an existing column so that the new column is inserted in a position just before the <existingcolumn>.

It accepts a columnDefinition as in a CREATE TABLE command. If NOT NULL is specified and the table is not empty, then a default value must be specified. In all other respects, this command is the equivalent of a column definition statement in a CREATE TABLE statement.

If an SQL view includes a SELECT * FROM <tablename> in its select statement, the new column is added to the view. This is a non-standard feature which is likely to change in the future.

ALTER TABLE <tablename> DROP [COLUMN] <columnname>;

Drops the column from the table. Will drop any single-column primary key or unique constraint on the column as well. The command will not work if there is any multiple key constraint on the column or the column is referenced in a check constraint or a foreign key.

It will also fail if an SQL view includes the column.

ALTER TABLE <tablename> ALTER COLUMN <columnname> RENAME TO <newname> 

Changes a column name.

ALTER TABLE <tablename> ALTER COLUMN <columnname> SET DEFAULT <defaultvalue>};

Adds the specified default value to the column. Use NULL to remove a default.

ALTER TABLE <tablename> ALTER COLUMN <columnname> SET [NOT] NULL

Sets or removes a NOT NULL constraint for the column.

ALTER TABLE <tablename> ALTER COLUMN <columnDefinition>;

This form of ALTER TABLE ALTER COLUMN accepts a columnDefinition as in a CREATE TABLE command, with the following restrictions.

ALTER TABLE <tablename> ALTER COLUMN <columnname> RESTART WITH <new sequence vale>

This form is used exclusively for IDENTITY columns and changes the next automatic value for the identity sequence.

ALTER TABLE <tablename> ADD [CONSTRAINT <constraintname>] CHECK (<search condition>);

Adds a check constraint to the table. In the current version, a check constraint can reference only the row being inserted or updated.

ALTER TABLE <tablename> ADD [CONSTRAINT <constraintname>] UNIQUE (<column list>);

Adds a unique constraint to the table. This will not work if there is already a unique constraint covering exactly the same <column list>.

This will work only if the values of the column list for the existing rows are unique or include a null value.

ALTER TABLE <tablename> ADD [CONSTRAINT <constraintname>] PRIMARY KEY (<column list>);

Adds a primary key constraint to the table, using the same constraint syntax as when the primary key is specified in a table definition.

ALTER TABLE <tablename> ADD [CONSTRAINT <constraintname>] FOREIGN KEY (<column list>) REFERENCES <exptablename> (<column list>)
    [ON {DELETE | UPDATE} {CASCADE | SET DEFAULT | SET NULL}];

Adds a foreign key constraint to the table, using the same constraint syntax as when the foreign key is specified in a table definition.

This will fail if for each existing row in the referring table, a matching row (with equal values for the column list) is not found in the referenced tables.

ALTER TABLE <tablename> DROP CONSTRAINT <constraintname>;

Drop a named unique, check or foreign key constraint from the table.

ALTER TABLE <tablename> RENAME TO <newname>;

ALTER USER <username> SET PASSWORD <password>;

Changes the password for an existing user. Password must be double quoted. Use "" for an empty password.

Only an administrator can do this.

CALL Expression;

Any expression can be called like a stored procedure, including, but not only Java stored procedures or functions. This command returns a ResultSet with one column and one row (the result) just like a SELECT statement with one row and one column.

See also: Stored Procedures / Functions, SQL Expression.

CHECKPOINT [DEFRAG[1]];

Closes the database files, rewrites the script file, deletes the log file and opens the database.

If DEFRAG is specified, this command also shrinks the .data file to its minimal size.

See also: SHUTDOWN, SET LOGSIZE.

COMMIT [WORK];

Ends a transaction and makes the changes permanent.

See also: ROLLBACK, SET AUTOCOMMIT, SET LOGSIZE.

CONNECT USER <username> PASSWORD <password>;

Connects to the database as a different user. Password should be double quoted. Use "" for an empty password.

See also: GRANT, REVOKE.

CREATE ALIAS <function> FOR <javaFunction>;

Creates an alias for a Java function. The function must be accessible from the JVM in which the database runs. Example:

    CREATE ALIAS ABS FOR "java.lang.Math.abs";

See also: CALL, Stored Procedures / Functions.

CREATE [UNIQUE] INDEX <index> ON <table> (<column> [DESC] [, ...]) [DESC];

Creates an index on one or more columns in a table.

Creating an index on searched columns may improve performance. The qualifier DESC can be present for command compatibility with other databases but it has no effect. Unique indexes can be defined but this is deprecated. Use UNIQUE constraints instead. The name of an index must be unique within the whole database.

See also: CREATE TABLE, DROP INDEX.

CREATE ROLE <rolename>;

Creates the named role with no members. Requires Administrative privileges.

CREATE SCHEMA <schemaname> AUTHORIZATION DBA [<createStatement> [<grantStatement<] [...];

Creates the named schema.

Optional (nested) CREATE and GRANT statements can be given only for new objects in this new schema. Only the last nested statement should be terminated with a semicolon, because the first semicolon encountered after "CREATE SCHEMA" will end the CREATE SCHEMA command. In the example below, a new schema, ACCOUNTS, is created, then two tables and a view are added to this schma and some rights on these objects are granted.

    CREATE SCHEMA ACCOUNTS AUTHORIZATION DBA
    CREATE TABLE AB(A INTEGER, ...)
    CREATE TABLE CD(C CHAHR, ...)
    CREATE VIEW VI AS SELECT ...
    GRANT SELECT TO PUBLIC ON AB
    GRANT SELECT TO JOE ON CD;

Requires Administrative privileges.

CREATE SEQUENCE <sequencename> [AS {INTEGER | BIGINT}] [START WITH <startvalue>] [INCREMENT BY <incrementvalue>];

Creates a sequence. The default type is INTEGER. The default start value is 0 and the increment 1. Negative values are not allowed. If a sequence goes beyond Integer.MAXVALUE or Long.MAXVALUE, the next result is determined by 2's complement arithmetic.

The next value for a sequence can be included in SELECT, INSERT and UPDATE statements as in the following example:

SELECT [...,] NEXT VALUE FOR <sequencename> [, ...] FROM <tablename>

In the proposed SQL 200n and in the current version, there is no way of retreiving the last returned value of a sequence.

CREATE [MEMORY | CACHED | [GLOBAL] TEMPORARY | TEMP [1] | TEXT[1]] TABLE <name>
( <columnDefinition> [, ...] [, <constraintDefinition>...] ) [ON COMMIT {DELETE | PRESERVE} ROWS];

Creates a tables in memory (default) or on disk and only cached in memory. If the database is all-in-memory, both MEMORY and CACHED forms of CREATE TABLE return a MEMORY table while the TEXT form is not allowed.

See also: DROP TABLE.

CREATE TRIGGER <name> {BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON <table> [FOR EACH ROW] [QUEUE n] [NOWAIT] CALL <TriggerClass>;

TriggerClass is an application-supplied class that implements the org.hsqldb.Trigger interface e.g. "mypackage.TrigClass". It is the fire method of this class that is invoked when the trigger event occurs. You should provide this class, which can have any name, and ensure that this TriggerClass is present in the classpath which you use to start hsqldb.

Since 1.7.2 the implementation has been changed and enhanced. When the 'fire' method is called, it is passed the following arguments:

    fire (String name, String table, Object row1[], Object row2[])

where 'row1' and 'row2' represent the 'before' and 'after' states of the row acted on, with each column being a member of the array. The mapping of members of the row arrays to database types is specified in Data Types. For example, BIGINT is represented by a java.lang.Long Object. Note that the number of elements in the row arrays is larger than the number of columns by one or two elements. Never modify the last elements of the array, which are not part of the actual row.

If the trigger method wants to access the database, it must establish its own JDBC connection. This can cause data inconsistency and other problems so it is not recommended. The jdbc:default:connection: URL is not currently supported.

Implementation note:

If QUEUE 0 is specified, the fire method is execued in the same thread as the database engine. This allows trigger action to alter the data that is about to be stored in the database. Data can be checked or modified in BEFORE INSERT / UPDATE + FOR EACH ROW triggers. All table constraints are then enforced by the database engine and if there is a violation, the action is rejected for the SQL command that initiated the INSERT or UPDATE. There is an exception to this rule, that is with UPDATE queries, referential integrity and cascading actions resulting from ON UPDATE CASCASE / SET NULL / SET DEFAULT are all performed prior to the invocation of the trigger method. If an invalid value that breaks referential integrity is inserted in the row by the trigger method, this action is not checked and results in inconsistent data in the table.

Alternatively, if the trigger is used for external communications and not for checking or altering the data, a queue size larger than zero can be specified. This is in the interests of not blocking the database's main thread as each trigger will run in a thread that will wait for its firing event to occur. When this happens, the trigger's thread calls TriggerClass.fire. There is a queue of events waiting to be run by each trigger thread. This is particularly useful for 'FOR EACH ROW' triggers, when a large number of trigger events occur in rapid succession, without the trigger thread getting a chance to run. If the queue becomes full, subsequent additions to it cause the database engine to suspend awaiting space in the queue. Take great care to avoid this situation if the trigger action involves accessing the database, as deadlock will occur. This can be avoided either by ensuring the QUEUE parameter makes a large enough queue, or by using the NOWAIT parameter, which causes a new trigger event to overwrite the most recent event in the queue. The default queue size is 1024. Note also that the timing of trigger method calls is not guaranteed, so applications should implement their own synchronization measures if necessary.

With a non-zero QUEUE parameter, if the trigger methods modifies the 'row2' values, these changes may or may not affect the database and will almost certainly result in data inconsistency.

Please refer to the code for org.hsqldb.sample.Trigger and org.hsqldb.sample.TriggerSample for more information on how to write a trigger class.

See also: DROP TRIGGER.

CREATE USER <username> PASSWORD <password> [ADMIN];

Creates a new user or new administrator in this database. Password must be double quoted. Empty password can be made using "". You can change a password afterwards using a ALTER USER^[1] command.

Only an administrator can do this.

See also: CONNECT, GRANT, REVOKE. ALTER USER^[1],

CREATE VIEW <viewname>[(<viewcolumn>,..) AS SELECT ... FROM ... [WHERE Expression];
[ORDER BY orderExpression [, ...]]
[LIMIT <limit> [OFFSET <offset>]];

A view can be thought of as either a virtual table or a stored query. The data accessible through a view is not stored in the database as a distinct object. What is stored in the database is a SELECT statement. The result set of the SELECT statement forms the virtual table returned by the view. A user can use this virtual table by referencing the view name in SQL statements the same way a table is referenced. A view is used to do any or all of these functions:

Views are created by defining the SELECT statement that retrieves the data to be presented by the view. The data tables referenced by the SELECT statement are known as the base tables for the view. In this example, is a view that selects data from three base tables to present a virtual table of commonly needed data:

    CREATE VIEW mealsjv AS
      SELECT m.mid mid, m.name name, t.mealtype mt, a.aid aid,
             a.gname + ' ' + a.sname author, m.description description,
             m.asof asof
        FROM meals m, mealtypes t, authors a
       WHERE m.mealtype = t.mealtype
        AND m.aid = a.aid;

You can then reference mealsjv in statements in the same way you would reference a table:

    SELECT * FROM mealsjv;

A view can reference another view. For example, mealsjv presents information that is useful for long descriptions that contain identifiers, but a short list might be all a web page display needs. A view can be built that selects only specific mealsjv columns:

    CREATE VIEW mealswebv AS SELECT name, author FROM mealsjv;

The SELECT statement in a VIEW definition should return columns with distinct names. If the names of two columns in the SELECT statement are the same, use a column alias to distinguish between them. A list of new column names can always be defined for a view.

    CREATE VIEW aview (new_name, new_author) AS
      SELECT name, author
      FROM mealsjv

See also: SQL Expression, SELECT^[1], DROP VIEW^[1].

DELETE FROM table [WHERE Expression];

Removes rows in a table.

See also: SQL Expression, INSERT, SELECT^[1].

DISCONNECT;

Closes this connection. It is not required to call this command when using the JDBC interface: it is called automatically when the connection is closed. After disconnecting, it is not possible to execute other queries (including CONNECT) with this connection.

See also: CONNECT.

DROP INDEX index [IF EXISTS];

Removes the specified index from the database. Will not work if the index backs a UNIQUE of FOREIGN KEY constraint.

See also: CREATE INDEX.

DROP ROLE <rolename>;

Removes all members from specified role, then removes the role itself.

DROP SEQUENCE <sequencename> [IF EXISTS] [RESTRICT | CASCADE];

Removes the specified sequence from the database. When IF EXIST is used, the statement returns without an error if the sequence does not exist. The RESTRICT option is in effect by default, meaning that DROP will fail if any view reference the sequence. Specify the CASCADE option to silently drop all dependent database objects.

DROP SCHEMA <schemaname> [RESTRICT | CASCADE];

Removes the specified schema from the database. The RESTRICT option is in effect by default, meaning that DROP will fail if any objects such as tables or sequences have been defined in the schema. Specify the CASCADE option to silently drop all database objects in the schema.

Requires Administrative privileges.

DROP TABLE <table> [IF EXISTS] [RESTRICT | CASCADE];

Removes a table, the data and indexes from the database. When IF EXIST is used, the statement returns without an error even if the table does not exist.

The RESTRICT option is in effect by default, meaning that DROP will fail if any tables or views refer to this table. Specify the CASCADE option to silently drop all dependent views, and to drop any foreign key constraint that links this table with other tables.

See also:

CREATE TABLE.

DROP TRIGGER <trigger>;

Removes a trigger from the database.

See also: CREATE TRIGGER^[1].

DROP USER <username>;

Removes a user from the database.

Only an administrator do this.

See also: CREATE USER.

DROP VIEW <viewname> [IF EXISTS] [RESTRICT | CASCADE];

Removes a view from the database. When IF EXIST is used, the statement returns without an error if the view does not exist. The RESTRICT option is in effect by default, meaning that DROP will fail if any other view refers to this view. Specify the CASCADE option to silently drop all dependent views.

See also: CREATE VIEW^[1].

EXPLAIN PLAN FOR { SELECT ... | DELETE ... | INSERT ... | UPDATE ..};

EXPLAIN PLAN FOR can be used with any query to get a detailed list of the elements in the execution plan.

This list includes the indexes used for performing the query and can be used to optimise the query or to add indexes to tables.

GRANT { SELECT | DELETE | INSERT | UPDATE | ALL } [,...]
ON { table | CLASS "package.class" } TO <grantee>;

GRANT <rolename> [,...] TO <grantee>[1];

<grantee> is either a user name, a role name, or PUBLIC. PUBLIC means all users.

The first form of the GRANT command assigns privileges to a grantee for a table or for a class. To allow a user to call a function from a class, the right ALL must be used. Examples:

    GRANT SELECT ON Test TO GUEST;
    GRANT ALL ON CLASS "java.lang.String" TO PUBLIC;

The second form of the GRANT command gives the specified <grantee> membership in the specified role.

Require Administrative privileges.

See also: REVOKE, CREATE USER, CREATE ROLE^[1].

INSERT INTO table [( column [,...] )]
{ VALUES(Expression [,...]) | SelectStatement};

Adds one or more new rows of data into a table.

REVOKE { SELECT | DELETE | INSERT | UPDATE | ALL } [,...]
ON { table | CLASS "package.class" } FROM <grantee>;

REVOKE <rolename> [,...] FROM <grantee>[1];

<grantee> is either a user name, a role name, or PUBLIC. PUBLIC means all users.

The first form of the REVOKE command withdraws privileges from a grantee for a table or for a class.

The second form of the REVOKE command withdraws membership of the specified <grantee> from the specified role.

Both forms require Administrative privileges.

See also: GRANT.

ROLLBACK [TO SAVEPOINT <savepoint name>[1] |  WORK}];

ROLLBACK used on its own, or with WORK, undoes changes made since the last COMMIT or ROLLBACK.

ROLLBACK TO SAVEPOINT <savepoint name> undoes the change since the named savepoint. It has no effect if the savepoint is not found.

See also: COMMIT.

SAVEPOINT <savepoint name>;

Sets up a SAVEPOINT for use with ROLLBACK TO SAVEPOINT.

See also: COMMIT.

SCRIPT ['file'];

Creates an SQL script describing the database. If the file is not specified, a result set containing only the DDL script is returned. If the file is specified then this file is saved with the path relative to the machine where the database engine is located.

Only an administrator may do this.

SELECT [{LIMIT <offset> <limit> | TOP <limit>}[1]][ALL | DISTINCT]
{ selectExpression | table.* | * } [, ...]
[INTO [CACHED | TEMP  | TEXT][1] newTable]
FROM tableList
[WHERE Expression]
[GROUP BY Expression [, ...]]
[HAVING Expression]
[{ UNION [ALL | DISTINCT] | {MINUS [DISTINCT] | EXCEPT [DISTINCT] } | INTERSECT [DISTINCT] } selectStatement]
[ORDER BY orderExpression [, ...]]
[LIMIT <limit> [OFFSET <offset>]];

Retrieves information from one or more tables in the database.

See also: INSERT, UPDATE, DELETE.

SET AUTOCOMMIT { TRUE | FALSE };

Switches on or off the connection's auto-commit mode. If switched on, then all statements will be committed as individual transactions. Otherwise, the statements are grouped into transactions that are terminated by either COMMIT or ROLLBACK. By default, new connections are in auto-commit mode. This command should not be used directly. Use the JDBC equivalent method, Connection.setAutoCommit(boolean autocommit).

SET DATABASE COLLATION <double quoted collation name>;

Each database can have its own collation. Sets the collation from the set of collations in the source for org.hsqldb.Collation. Once this command has been issued, the database can be opened in any JVM and will retain its collation.

Once this command has been issued, the database can be opened in any JVM and will retain its collation.

SET CHECKPOINT DEFRAG <size>;

The parameter size is the megabytes of abandoned space in the .data file. When a CHECKPOINT is performed either as a result of the .log file reaching the limit set by "SET LOGSIZE size", or by the user issuing a CHECKPOINT command, the amount of space abandoned during the session is checked and if it is larger than size, a CHECKPOINT DEFRAG is performed instead of a checkpoint.

SET IGNORECASE { TRUE | FALSE };

Disables (ignorecase = true) or enables (ignorecase = false) the case sensitivity of text comparison and indexing for new tables. By default, character columns in new databases are case sensitive. The sensitivity must be switched before creating tables. Existing tables and their data are not affected. When switched on, the data type VARCHAR is set to VARCHAR_IGNORECASE in new tables. Alternatively, you can specify the VARCHAR_IGNORECASE type for the definition of individual columns. So it is possible to have some columns case sensitive and some not, even in the same table.

Only an administrator may do this.

SET LOGSIZE <size>;

Sets the maximum size in MB of the .log file. Default is 200 MB. The database will be closed and opened (just like using CHECKPOINT) if the .log file gets over this limit, and so the .log file will shrink. 0 means no limit.

See also: CHECKPOINT.

SET PASSWORD <password>;

Changes the password of the currently connected user. Password must be double quotedEmpty password can be set using "".

SET PROPERTY <double quoted name> <value>;

Sets a database property. Properties that can be set using this command are either boolean or integral and are listed in the Advanced Topics chapter.

SET REFERENTIAL_INTEGRITY { TRUE | FALSE };

This commands enables / disables the referential integrity checking (foreign keys). Normally it should be switched on (this is the default) but when importing data (and the data is imported in the 'wrong' order) the checking can be switched off.

Only an administrator may do this.

See also: CREATE TABLE.

SET SCHEMA <schemaname>;

Sets the current JDBC session's schema. The sole purpose for the session schema is to provide a default schema name for schema objects that do not have the schema name specified explicitly in the SQL command, or by association with another object of known schema. For example, if you run SELECT * FROM atbl;, HSQLDB will look for the table or view named atbl in the session's current schema.

Session schemas last only for the duration of the current session. When a new JDBC session is obtained, the new session will have the default schema.

SET SCRIPTFORMAT {TEXT | BINARY | COMPRESSED};

Changes the format of the script file. BINARY and COMPRESSED formats are slightly faster and more compact than the default TEXT. Recommended only for very large script files.

SET TABLE tableName INDEX 'index1rootPos index2rootPos ... ';

This command is only used internally to store the position of index roots in the .data file. It appears only in database script files; it should not be used directly.

SET TABLE <tablename> READONLY {TRUE | FALSE};

Sets the table as read only.

SET TABLE <tablename> SOURCE <file and options> [DESC];

For details see the Text Tables chapter.

This command is used exclusively with TEXT tables to specify which file is used for storage of the data. The optional DESC qualifier results in the text file indexed from the end and opened as readonly. The <file and options> argument is a double quoted string that consists of:

    <file and options>::= <doublequote> <filepath> [<semicolon> <option>...] <doublequote>

Example:

    SET TABLE mytable SOURCE "myfile;fs=|;vs=.;lvs=~"

Only an administrator may do this.

SET WRITE_DELAY {{ TRUE | FALSE } | <seconds> | <milliseconds> MILLIS};

This controls the frequency of file sync for the log file. When WRITE_DELAY is set to FALSE or 0, the sync takes place immediately at each COMMIT. WRITE_DELAY TRUE performs the sync once every 20 seconds (which is the default). A numeric value can be specified instead.

The purpose of this command is to control the amount of data loss in case of a total system crash. A delay of 1 second means at most the data written to disk during the last second before the crash is lost. All data written prior to this has been synced and should be recoverable.

A write delay of 0 impacts performance in high load situations, as the engine has to wait for the file system to catch up.

To avoid this, you can set write delay down to 10 milliseconds. In practice, a write delay of 100 milliseconds provides better than 99.9999% reliability with an average one system crash per day, or 99.99999% with an average one system crash per 6 days.

Each time a SET WRITE_DELAY is issued with any value, a sync is immediately performed.

Only an administrator may do this.

SHUTDOWN [IMMEDIATELY | COMPACT | SCRIPT[1]];

Closes the current database.

Only an administrator may use the SHUTDOWN command.

UPDATE table SET column = Expression [, ...] [WHERE Expression];

Modifies data of a table in the database.

See also: SELECT^[1], INSERT, DELETE.