Set the current default time zone displacement for the current SQL-session. When the session starts, the time zone displacement is set to the time zone of the client. This command changes the time zone displacement. The effect of this lasts for the lifetime of the session. If LOCAL is specified, the time zone displacement reverts to the local time zone of the session.
Example 3.8. Setting Session Time Zone
SET TIME ZONE LOCAL SET TIME ZONE INTERVAL '+6:00' HOUR TO MINUTE
SET CATALOG
set catalog statement
<set catalog statement> ::= SET <catalog name
characteristic>
<catalog name characteristic> ::= CATALOG
<value specification>
Set the default schema name for unqualified names used in SQL statements that are prepared or executed directly in the current sessions. As there is only one catalog in the database, only the name of this catalog can be used. The current catalog can be accessed with the CURRENT_CATALOG function.
SET SCHEMA
set schema statement
<set schema statement> ::= SET <schema name
characteristic>
<schema name characteristic> ::= SCHEMA <value
specification> | <schema name>
Set the default schema name for unqualified names used in SQL statements that are prepared or executed directly in the current sessions. The effect of this lasts for the lifetime of the session. The SQL Standard form requires the schema name as a single-quoted string. HyperSQL also allows the use of the identifier for the schema. The current schema can be accessed with the CURRENT_SCHEMA function.
SET PATH
set path statement
<set path statement> ::= SET <SQL-path
characteristic>
<SQL-path characteristic> ::= PATH <value
specification>
Set the SQL-path used to determine the subject routine of routine invocations with unqualified routine names used in SQL statements that are prepared or executed directly in the current sessions. The effect of this lasts for the lifetime of the session.
SET MAXROWS
set max rows statement
<set max rows statement> ::= SET MAXROWS
<unsigned integer literal>
The normal operation of the session has no limit on the number of rows returned from a SELECT statement. This command set the maximum number of rows of the result returned by executing queries.
This statement has a similar effect to the
setMaxRows(int max)
method of the JDBC
Statement
interface, but it affects the results
returned from the next statement execution only. After the execution of
the next statement, the MAXROWS limit is removed.
Only zero or positive values can be used with this command. The
value overrides any value specified with setMaxRows(int
max)
method of a JDBC statement. The statement SET
MAXROWS 0
means no limit.
It is possible to limit the number of rows returned from SELECT statements with the FETCH <n> ROWS ONLY, or its alternative, LIMIT <n>. Therefore this command is not recommended for general use. The only legitimate use of this command is for checking and testing queries that may return very large numbers of rows.
SET SESSION RESULT MEMORY ROWS
set session result memory rows statement
<set session result memory rows statement> ::= SET
SESSION RESULT MEMORY ROWS <unsigned integer
literal>
By default the session uses memory to build result sets, subquery results and temporary tables. This command sets the maximum number of rows of the result (and temporary tables) that should be kept in memory. If the row count of the result or temporary table exceeds the setting, the result is stored on disk. The default is 0, meaning all result sets are held in memory.
This statement applies to the current session only. The general database setting is:
SET DATABASE DEFAULT RESULT MEMORY ROWS <unsigned
integer literal>
SET IGNORECASE
set ignore case statement
<set ignore case statement> ::= SET IGNORECASE {
TRUE | FALSE }
This is a legacy method for creating case-insensitive columns. Still supported but not recommended for use.
Sets the type used for new VARCHAR table columns. By default,
character columns in new databases are case-sensitive. If SET
IGNORECASE TRUE
is used, all VARCHAR columns in new tables are
set to use a collation that converts strings to uppercase for comparison.
In the latest versions of HyperSQL you can specify the collations for the
database and for each column and have some columns case-sensitive and some
not, even in the same table. The collation's strength is used to force
case-insensitive comparison. Collations are discussed in the Schemas and Database Objects chapter.
This statement must be switched before creating tables. Existing tables and their data are not affected.
$Revision: 5243 $
Copyright 2009-2013 Fred Toussi. Permission is granted to distribute this document without any alteration under the terms of the HSQLDB license. Additional permission is granted to the HSQL Development Group to distribute this document with or without alterations under the terms of the HSQLDB license.
2013-07-08 15:31:27+0100
Table of Contents
The persistent elements of an SQL environment are database objects. The database consists of catalogs plus authorizations.
A catalog contains schemas, while schemas contain the objects that contain data or govern the data.
Each catalog contains a special schema called INFORMATION_SCHEMA. This schema is read-only and contains some views and other schema objects. The views contain lists of all the database objects that exist within the catalog, plus all authorizations.
Each database object has a name. A name is an identifier and is unique within its name-space.
In HyperSQL, there is only one catalog per database. The name of the
catalog is PUBLIC. You can rename the catalog with the ALTER
CATALOG RENAME TO
statement. All schemas belong the this
catalog. The catalog name has no relation to the file name of the
database.
Each database has also an internal "unique" name which is automatically generated when the database is created. This name is used for event logging. You can also change this unique name.
Schema objects are database objects that contain data or govern or perform operations on data. By definition, each schema object belongs to a specific schema.
Schema objects can be divided into groups according to their characteristics.
Some kinds of schema objects can exist independently from other schema object. Other kinds can exist only as an element of another schema object. These dependent objects are automatically destroyed when the parent object is dropped.
Separate name-spaces exists for different kinds of schema object. Some name-spaces are shared between two similar kinds of schema objects.
There can be dependencies between various schema objects, as a schema object can include references to other schema objects. These references can cross schema boundaries. Interdependence and cross referencing between schema objects is allowed in some circumstances and disallowed in some others.
Schema objects can be destroyed with the DROP statement. If dependent schema objects exist, a DROP statement will succeed only if it has a CASCADE clause. In this case, dependent objects are also destroyed in most cases. In some cases, such as dropping DOMAIN objects, the dependent objects are not destroyed, but modified to remove the dependency.
A new HyperSQL catalog contains an empty schema called PUBLIC. By default, this schema is the initial schema when a new session is started. New schemas and schema objects can be defined and used in the PUBLIC schema, as well as any new schema that is created by the user. You can rename the PUBLIC schema.
HyperSQL allows all schemas to be dropped, except the schema that is the default initial schema for new sessions (by default, the PUBLIC schema). For this schema, a DROP SCHEMA ... CASCADE statement will succeed but will result in an empty schema, rather than no schema.
The statements for setting the initial schema for users are described in the Statements for Authorization and Access Control chapter.
The name of a schema object is an
<identifier>
. The name belongs to the
name-space for the particular kind of schema object. The name is unique
within its name-space. For example, each schema has a separate
name-space for TRIGGER objects.
In addition to the name-spaces in the schema. Each table has a name-space for the names of its columns.
Because a schema object is always in a schema and a schema always
in a catalog, it is possible, and sometimes necessary, to qualify the
name of the schema object that is being referenced in an SQL statement.
This is done by forming an <identifier chain>
.
In some contexts, only a simple <identifier>
can be used and the <identifier chain>
is
prohibited. While in some other contexts, the use of
<identifier chain>
is optional. An identifier
chain is formed by qualifying each object with the name of the object
that owns its name-space. Therefore a column name is prefixed with a
table name, a table name is prefixed with a schema name, and a schema
name is prefixed with a catalog name. A fully qualified column name is
in the form <catalog name>.<schema name>.<table
name>.<column name>
, likewise, a fully qualified
sequence name is in the form <catalog name>.<schema
name>.<sequence name>
.
HyperSQL extends the SQL standard to allow renaming all database objects. The ALTER ... RENAME TO command has slightly different forms depending on the type of object. If an object is referenced in a VIEW or ROUTINE definition, it is not always possible to rename it.
A CHARACTER SET is the whole or a subset of the UNICODE character set.
A character set name can only be a <regular
identifier>
. There is a separate name-space for character
sets.
There are several predefined character sets. These character sets belong to INFORMATION_SCHEMA. However, when they are referenced in a statement, no schema prefix is necessary.
The following character sets, together with some others, have been specified by the SQL Standard:
SQL_TEXT, SQL_IDENTIFIER, SQL_CHARACTER
The SQL_CHARACTER consists of ASCII letters, digits and the symbols used in the SQL language. SQL_TEXT and SQL_IDENTIFIER are implementation defined. HyperSQL defines SQL_TEXT as the UNICODE character set and SQL_IDENTIFIER as the UNICODE character set minus the SQL language special characters.
SQL_TEXT consists of the full set of Unicode characters. These characters can be used in strings and clobs stored in the database. The character repertoire of HyperSQL is the UTF16 character set, which covers all possible character sets.
If a predefined character set is specified for a table column, then any string stored in the column must contain only characters from the specified character set. HyperSQL does not enforce the CHARACTER SET that is specified for a column and may accept any character string supported by SQL_TEXT.
A COLLATION is the method used for ordering character strings in ordered sets and to determine equivalence of two character strings.
The system collation is called SQL_TEXT. This collation sorts according to the Unicode code of the characters, UNICODE_SIMPLE. The system collation is always used for INFORMATION_SCHEMA tables.
The default database collation is the same as the system collation. You can change this default, either with a language collation, or with the SQL_TEXT_UCC. This collation is a case-insensitive form of the UNICODE_SIMPLE collation.
Collations for a large number of languages are supported by HyperSQL. These collations belong to INFORMATION_SCHEMA. However, when they are referenced in a statement, there is no need for a schema prefix.
A different collation than the default collation can be specified for each table column that is defined as CHAR or VARCHAR.
A collation can also be used in an ORDER BY clause.
A collation can be used in the GROUP BY clause.
CREATE TABLE t (id INTEGER PRIMARY KEY, name VARCHAR(20) COLLATE "English") SELECT * FROM t ORDER BY name COLLATE "French" SELECT COUNT(*), name FROM t GROUP BY name COLLATE "English 0"
In the examples above, the collation for the column is already
specified when it is defined. In the first SELECT statement, the column
is sorted using the French collation. In the second SELECT, the
"English 0"
collation is used in the GROUP BY clause.
This collation is case insensitive, so the same name with different uses
of upper and lower case letters is considered the same and counted
together.
The supported collations are named according to the language. You
can see the list in the INFORMATION_SCHEMA.COLLATIONS view. You can use
just the name in double quotes for the default form of the collation. If
you add a strength between 0, 1, 2, 3, the case sensitivity and accent
sensitivity changes. The value 0 indicates least sensitivity to
differences. At this strength the collation is case-insensitive and
ignores differences between accented letters. At strength 1, differences
between accented letters are taken into account. At strength 2, both
case and accent are significant. Finally 3 indicates additional
sensitivity to different punctuation. A second parameter can also be
used with values 0 or 1, to indicate how decomposition of accented
characters for comparison is handled for languages that support such
characters. See the Java and ICU (International Components for Unicode)
collation documentation for more details on these values. For example,
possible forms of the French collation are "French"
,
"French 0"
, "French 1"
, etc. and
"French 2 1"
, etc. When the collation is specified
without strength, it seems the system defaults to strength 2, which is
case and accent sensitive.
When a collation is not explicitly used in the CREATE TABLE statement for a column, then the database default collation is used for this column. If you change the database default collation afterwards, the new collation will be used.
With the older versions of HyperSQL the special type VARCHAR_IGNORECASE was used as the column type for case-insensitive comparison. Any column already defined as VARCHAR_IGNORECASE will be compared exactly as before. In version 2.3.0 and later, this form is represented by the addition of UCC after the collation name, for example "French UCC". You can still use the SET IGNORECASE TRUE statement in your session to force the UCC to be applied to the collation for the VARCHAR columns of new tables. UCC stands for Upper Case Comparison. Before comparing two strings, both are converted to uppercase using the current collation. This is exactly how VARCHAR_IGNORECASE worked.
It is recommended to use the default SQL_TEXT collation for your general CHAR or VARCHAR columns. For columns where a language collation is desirable, the choice should be made very carefully, because names that are very similar but only differ in the accents may be considered equal in searches.
When comparing two strings, HyperSQL 2 pads the shorter string with spaces in order to compare two strings of equal length. You can change the default database collation with one that does not pad the string with spaces before comparison. This method of comparison was used in versions older than 2.
User defined collations can be created based on existing collations to control the space padding. These collations are part of the current schema.
See the COLLATE keyword and SET DATABASE COLLATION statement in the System Management chapter. The PAD SPACE or NO PAD clause is used to control padding.
| Important | |
|---|---|
|
If you change the default collation of a database when there are tables containing data with CHAR or VARCHAR columns that are part of an index, a primary key or a unique constraint, you must execute SHUTDOWN COMPACT or SHUTDOWN SCRIPT after the change. If you do not do this, your queries and other statements will show erratic behaviour and may result in unrecoverable errors. |
A distinct, user-defined TYPE is simply based on a built-in type. A distinct TYPE is used in table definitions and in CAST statements.
Distinct types share a name-space with domains.
A DOMAIN is a user-defined type, simply based on a built-in type. A DOMAIN can have constraints that limit the values that the DOMAIN can represent. A DOMAIN can be used in table definitions and in CAST statements.
Distinct types share a name-space with domains.
A SEQUENCE object produces INTEGER values in sequence. The SEQUENCE can be referenced in special contexts only within certain SQL statements. For each row where the object is referenced, its value is incremented.
There is a separate name-space for SEQUENCE objects.
IDENTITY columns are columns of tables which have an internal, unnamed SEQUENCE object. HyperSQL also supports IDENTITY columns that use a named SEQUENCE object.
SEQUENCE objects and IDENTITY columns are supported fully according to the latest SQL 2008 Standard syntax.
Sequences
The SQL:2008 syntax and usage is different from what is supported
by many existing database engines. Sequences are created with the
CREATE SEQUENCE
command and their current value can
be modified at any time with ALTER SEQUENCE
. The next
value for a sequence is retrieved with the NEXT VALUE FOR
<name>
expression. This expression can be used for
inserting and updating table rows.
Example 4.1. inserting the next sequence value into a table row
INSERT INTO mytable VALUES 2, 'John', NEXT VALUE FOR mysequence
You can also use it in select statements. For example, if you want to number the returned rows of a SELECT in sequential order, you can use:
Example 4.2. numbering returned rows of a SELECT in sequential order
SELECT NEXT VALUE FOR mysequence, col1, col2 FROM mytable WHERE ...
In version 2.0, the semantics of sequences is exactly as defined by SQL:2008. If you use the same sequence twice in the same row in an INSERT statement, you will get the same value as required by the Standard.
The correct way to use a sequence value is the NEXT VALUE FOR expression.
HyperSQL adds an extension to Standard SQL to return the last value returned by the NEXT VALUE FOR expression in the current session. After a statement containing NEXT VALUE FOR is executed, the value that was returned for NEXT VALUE FOR is available using the CURRENT VALUE FOR expression. In the example below, the NEXT VALUE FOR expression is used to insert a new row. The value that was returned by NEXT VALUE FOR is retrieved with the CURRENT VALUE FOR in the next insert statements to populate two new rows in a different table that has a parent child relationship with the first table. For example if the value 15 was returned by the sequence, the same value 15 is inserted in the three rows.
Example 4.3. using the last value of a sequence
INSERT INTO mytable VALUES 2, 'John', NEXT VALUE FOR mysequence INSERT INTO childtable VALUES 4, CURRENT VALUE FOR mysequence INSERT INTO childtable VALUES 5, CURRENT VALUE FOR mysequence
The INFORMATION_SCHEMA.SEQUENCES table contains the next
value that will be returned from any of the defined sequences. The
SEQUENCE_NAME column contains the name and the NEXT_VALUE column
contains the next value to be returned. Note that this is only for
getting information and you should not use it for accessing the next
sequence value. When multiple sessions access the same sequence, the
value returned from this table by one session could also be used by a
different session, causing a sequence value to be used twice
unintentionally.
Identity Auto-Increment Columns
Each table can contain a single auto-increment column, known as the IDENTITY column. An IDENTITY column is a SMALLINT, INTEGER, BIGINT, DECIMAL or NUMERIC column with its value generated by a sequence generator.
In HyperSQL 2.0, an IDENTITY column is not by default treated as the primary key for the table (as a result, multi-column primary keys are possible with an IDENTITY column present). Use the SQL standard syntax for declaration of the IDENTITY column.
The SQL standard syntax is used, which allows the initial value and other options to be specified.
<colname> [ INTEGER | BIGINT | DECIMAL | NUMERIC ]
GENERATED { BY DEFAULT | ALWAYS} AS IDENTITY [( <options>
)]
/* this table has no primary key */ CREATE TABLE vals (id INTEGER GENERATED BY DEFAULT AS IDENTITY, data VARBINARY(2000)) /* in this table id becomes primary key because the old syntax is used - avoid this syntax */ CREATE TABLE vals (id INTEGER IDENTITY, data VARBINARY(2000)) /* use the standard syntax and explicity declare a primary key identity column */ CREATE TABLE vals (id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, data VARBINARY(2000))
When you add a new row to such a table using an INSERT
INTO <tablename> ...
statement, you can use the DEFAULT
keyword for the IDENTITY column, which results in an auto-generated
value for the column.
The IDENTITY()
function returns the last value
inserted into any IDENTITY column by this session. Each session manages
this function call separately and is not affected by inserts in other
sessions. Use CALL IDENTITY()
as an SQL statement to
retrieve this value. If you want to use the value for a field in a child
table, you can use INSERT INTO <childtable> VALUES
(...,IDENTITY(),...);
. Both types of call to
IDENTITY()
must be made before any additional update or insert
statements are issued by the session.
In triggers and routines, the value returned by the
IDENTITY()
function is correct for the given context.
For example, if a call to a stored procedure inserts a row into a table,
causing a new identity value to be generated, a call to
IDENTITY()
inside the procedure will return the new
identity, but a call outside the procedure will return the last identity
value that was generated before a call was made to the procedure.
The last inserted IDENTITY value can also be retrieved via JDBC, by specifying the Statement or PreparedStatement object to return the generated value.
The next IDENTITY value to be used can be changed with the following statement. Note that this statement is not used in normal operation and is only for special purposes, for example resetting the identity generator:
ALTER TABLE ALTER COLUMN <column name> RESTART WITH <new value>;
For
backward compatibility, support has been retained for CREATE
TABLE <tablename>(<colname> IDENTITY, ...)
as a
shortcut which defines the column both as an IDENTITY column and a
PRIMARY KEY column. Also, for backward compatibility, it is possible to
use NULL as the value of an IDENTITY column in an INSERT statement and
the value will be generated automatically. You should avoid these
compatibility features as they may be removed from future versions of
HyperSQL.
In the following example, the identity value for the first INSERT statement is generated automatically using the DEFAULT keyword. The second INSERT statement uses a call to the IDENTITY() function to populate a row in the child table with the generated identity value.
CREATE TABLE star (id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, firstname VARCHAR(20), lastname VARCHAR(20)) CREATE TABLE movies (starid INTEGER, movieid INTEGER PRIMARY KEY, title VARCHAR(40)) INSERT INTO star (id, firstname, lastname) VALUES (DEFAULT, 'Felix', 'the Cat') INSERT INTO movies (starid, movieid, title) VALUES (IDENTITY(), 10, 'Felix in Hollywood')
HyperSQL 2.1 also supports IDENTITY columns that use an external, named SEQUENCE object. This feature is not part of the SQL Standard. The example below uses this type of IDENTITY. Note the use of CURRENT VALUE FOR seq here is multi-session safe. The returned value is the last value used by this session when the row was inserted into the star table. This value is available until the transaction is committed. After commit, NULL is returned by the CURRENT VALUE FOR expression until the SEQUENCE is used again.
CREATE SEQUENCE seq CREATE TABLE star (id INTEGER GENERATED BY DEFAULT AS SEQUENCE seq PRIMARY KEY, firstname VARCHAR(20), lastname VARCHAR(20)) CREATE TABLE movies (starid INTEGER, movieid INTEGER PRIMARY KEY, title VARCHAR(40)) INSERT INTO star (id, firstname, lastname) VALUES (DEFAULT, 'Felix', 'the Cat') INSERT INTO movies (starid, movieid, title) VALUES (CURRENT VALUE FOR seq, 10, 'Felix in Hollywood')
In the SQL environment, tables are the most essential components, as they hold all persistent data.
If TABLE is considered as metadata (i.e. without its actual data) it is called a relation in relational theory. It has one or more columns, with each column having a distinct name and a data type. A table usually has one or more constraints which limit the values that can potentially be stored in the TABLE. These constraints are discussed in the next section.
A single column of the table can be defined as IDENTITY. The values stored in this column are auto-generated and are based on an (unnamed) identity sequence, or optionally, a named SEQUENCE object.
A VIEW is similar to a TABLE but it does not permanently contain rows of data. A view is defined as a QUERY EXPRESSION, which is often a SELECT statement that references views and tables, but it can also consist of a TABLE CONSTRUCTOR that does not reference any tables or views.
A view has many uses:
Hide the structure and column names of tables. The view can represent one or more tables or views as a separate table. This can include aggregate data, such as sums and averages, from other tables.
Allow access to specific rows in a table. For example, allow access to records that were added since a given date, while hiding older records.
Allow access to specific columns. For example allow access to columns that contain non-confidential information. Note that this can also be achieved with the GRANT SELECT statement, using column-level privileges
A VIEW that returns the columns of a single ordinary TABLE is updatable if the query expression of the view is an updatable query expression as discussed in the Data Access and Change chapter. Some updatable views are insertable-into because the query expression is insertable-into. In these views, each column of the query expressions must be a column of the underlying table and those columns of the underlying table that are not in the view must have a default clause, or be an IDENTITY or GENERATED column. When rows of an updatable view are updated, or new rows are inserted, or rows are deleted, these changes are reflected in the base table. A VIEW definition may specify that the inserted or updated rows conform to the search condition of the view. This is done with the CHECK OPTION clause.
A view that is not updatable according to the above paragraph can be made updatable or insertable-into by adding INSTEAD OF triggers to the view. These triggers contain statements to use the submitted data to modify the contents of the underlying tables of the view separately. For example, a view that represents a SELECT statements that joins two tables can have an INSTEAD OF DELETE trigger with two DELETE statements, one for each table. Views that have an INSTEAD OF trigger are called TRIGGER INSERTABLE, TRIGGER UPDATABLE, etc. according to the triggers that have been defined.
Views share a name-space with tables.
A CONSTRAINT is a child schema object and can belong to a DOMAIN or a TABLE. CONSTRAINT objects can be defined without specifying a name. In this case the system generates a name for the new object beginning with "SYS_".
In a DOMAIN, CHECK constraints can be defined that limits the value represented by the DOMAIN. These constraints work exactly like a CHECK constraint on a single column of a table as described below.
In a TABLE, a constraint takes three basic forms.
CHECK
A CHECK constraint consists of a <search
condition>
that must not be false (can be unknown) for each
row of the table. The <search condition>
can
reference all the columns of the current row, and if it contains a
<subquery>
, other tables and views in the
database (excluding its own table).
NOT NULL
A simple form of check constraint is the NOT NULL constraint, which applies to a single column.
UNIQUE
A UNIQUE constraint is based on an equality comparison of values of specific columns (taken together) of one row with the same values from each of the other rows. The result of the comparison must never be true (can be false or unknown). If a row of the table has NULL in any of the columns of the constraint, it conforms to the constraint. A unique constraint on multiple columns (c1, c2, c3, ..) means that in no two rows, the sets of values for the columns can be equal unless at lease one of them is NULL. Each single column taken by itself can have repeat values in different rows. The following example satisfies a UNIQUE constraint on the two columns
Example 4.4. Column values which satisfy a 2-column UNIQUE constraint
| 1, | 2 |
| 2, | 1 |
| 2, | 2 |
| NULL, | 1 |
| NULL, | 1 |
| 1, | NULL |
| NULL, | NULL |
| NULL, | NULL |
If the SET DATABASE SQL UNIQUE NULLS FALSE has been set, then if not all the values set of columns are null, the not null values are compared and it is disallowed to insert identical rows that contain at least one not-null value.
PRIMARY KEY
A PRIMARY KEY constraint is equivalent to a UNIQUE constraint on one or more NOT NULL columns. Only one PRIMARY KEY can be defined in each table.
FOREIGN KEY
A FOREIGN key constraint is based on an equality comparison between values of specific columns (taken together) of each row with the values of the columns of a UNIQUE constraint on another table or the same table. The result of the comparison must never be false (can be unknown). A special form of FOREIGN KEY constraint, based on its CHECK clause, allows the result to be unknown only if the values for all columns are NULL. A FOREIGN key can be declared only if a UNIQUE constraint exists on the referenced columns.
Constraints share a name space with assertions.
An ASSERTION is a top-level schema objects. It consists of a
<search condition>
that must not be false (can
be unknown). HyperSQL does not yet support assertions.
Assertions share a name-space with constraints
A TRIGGER is a child schema object that always belongs to a TABLE or a VIEW.
Each time a DELETE, UPDATE or INSERT is performed on the table or view, additional actions are taken by the triggers that have been declared on the table or view.
Triggers are discussed in detail in Triggers chapter.
Routines are user-defined functions or procedures. The names and usage of functions and procedures are different. FUNCTION is a routine that can be referenced in many types of statements. PROCEDURE is a routine that can be referenced only in a CALL statement.
There is a separate name-space for routines.
Because of the possibility of overloading, each routine can have more than one name. The name of the routine is the same for all overloaded variants, but each variant has a specific name, different from all other routine names and specific names in the schema. The specific name can be specified in the routine definition statement. Otherwise it is assigned by the engine. The specific name is used only for schema manipulation statements, which need to reference a specific variant of the routine. For example, if a routine has two signatures, each signature has its own specific name. This allows the user to drop one of the signatures while keeping the other.
Routines are discussed in detail in chapter SQL-Invoked Routines .
Schemas and schema objects can be created, modified and dropped. The SQL Standard defines a range of statements for this purpose. HyperSQL supports many additional statements, especially for changing the properties of existing schema objects.
These elements and statements are used for different types of object. They are described here, before the statements that can use them.
identifier
definition of identifier
<identifier> ::= <regular identifier> |
<delimited identifier> | <SQL language identifier>
<delimited identifier> ::= <double quote>
<character sequence> <double quote>
<regular identifier> ::= <special character
sequence>
<SQL language identifier> ::= <special
character sequence>
A <delimited identifier>
is a sequence of
characters enclosed with double-quote symbols. All characters are
allowed in the character sequence.
A <regular identifier>
is a special
sequence of characters. It consists of letters, digits and the
underscore characters. It must begin with a letter. All the letters are
translated to their upper-case version.
The database setting, SET DATABASE SQL REGULAR NAMES
FALSE
can be used to relax the rules for regular identifier.
With this setting, an underscore character can appear at the start of
the regular identifier, and the dollar sign character can be used in the
identifier.
A <SQL language identifier>
is similar to
<regular identifier>
but the letters can range
only from A-Z in the ASCII character set. This type of identifier is
used for names of CHARACTER SET objects.
If the character sequence of a delimited identifier is the same as
an undelimited identifier, it represents the same identifier. For
example "JOHN" is the same identifier as JOHN. In a <regular
identifier>
the case-normal form is considered for
comparison. This form consists of the upper-case equivalent of all the
letters. When a database object is created with one of the CREATE
statements or renamed with the ALTER statement, if the name is enclosed
in double quotes, the exact name is used as the case-normal form. But if
it is not enclosed in double quotes, the name is converted to uppercase
and this uppercase version is stored in the database as the case-normal
form.
The character sequence length of all identifiers must be between 1 and 128 characters.
A reserved word is one that is used by the SQL Standard for
special purposes. It is similar to a <regular
identifier>
but it cannot be used as an identifier for user
objects. If a reserved word is enclosed in double quote characters, it
becomes a quoted identifier and can be used for database objects.
Case sensitivity rules for identifiers can be described simply as follows:
all parts of SQL statements are converted to upper case before processing, except identifiers in double quotes and strings in single quotes
identifiers, both unquoted and double quoted, are then treated as case-sensitive
most database engines follow the same rule, except, in some respects, MySQL and MS SQLServer.
CASCADE or RESTRICT
drop behavior
<drop behavior> ::= CASCADE |
RESTRICT
The <drop behavior>
is a required element
of statements that drop a SCHEMA or a schema object. If
<drop behavior>
is not specified then
RESTRICT
is implicit. It determines the effect of the
statement if there are other objects in the catalog that reference the
SCHEMA or the schema object. If RESTRICT is specified, the statement
fails if there are referencing objects. If CASCADE is specified, all the
referencing objects are modified or dropped with cascading effect.
Whether a referencing object is modified or dropped, depends on the kind
of schema object that is dropped.
IF EXISTS
drop condition (HyperSQL)
<if exists clause> ::= IF
EXISTS
This clause is not part of the SQL standard and is a HyperSQL extension to some commands that drop objects (schemas, tables, views, sequences and indexes). If it is specified, then the statement does not return an error if the drop statement is issued on a non-existent object.
SPECIFIC
specific routine designator
<specific routine designator> ::= SPECIFIC
<routine type> <specific name>
<routine type> ::= ROUTINE | FUNCTION |
PROCEDURE
This clause is used in statements that need to specify one of the
multiple versions of an overloaded routine. The <specific
name>
is the one specified in the <routine
definition>
statement.
RENAME
rename statement (HyperSQL)
<rename statement> ::= ALTER <object type>
<name> RENAME TO <new name>
<object type> ::= CATALOG | SCHEMA | DOMAIN |
TYPE | TABLE | CONSTRAINT | INDEX | ROUTINE | SPECIFIC
ROUTINE
<column rename statement> ::= ALTER TABLE
<table name> ALTER COLUMN <name> RENAME TO <new
name>
This statement is used to rename an existing object. It is not
part of the SQL Standard. The specified <name>
is the existing name, which can be qualified with a schema name, while
the <new name>
is the new name for the
object.
COMMENT
comment statement (HyperSQL)
<comment statement> ::= COMMENT ON { TABLE |
COLUMN | ROUTINE } <name> IS <character string
literal>
Adds a comment to the object metadata, which can later be read
from an INFORMATION_SCHEMA view. This command is not part of the SQL
Standard. The strange syntax is due to compatibility with other database
engines that support the statement. The <name>
is the name of a table, view, column or routine. The name of the column
consists of dot-separated <table name> . <column
name>
. The name of the table, view or routine can be a
simple name. All names can be qualified with a schema name. If there is
already a comment on the object, the new comment will replace it.
The comments appear in the results returned by JDBC DatabaseMetaData methods, getTables() and getColumns(). The INFORMATION_SCHEMA.SYSTEM_COMMENTS view contains the comments. You can query this view using the schema, table, and column names to retrieve the comments.
CREATE SCHEMA
schema definition
The CREATE_SCHEMA or DBA role is required in order to create a
schema. A schema can be created with or without schema objects. Schema
objects can always be added after creating the schema, or existing ones
can be dropped. Within the <schema definition>
statement, all schema object creation takes place inside the newly
created schema. Therefore, if a schema name is specified for the schema
objects, the name must match that of the new schema. In addition to
statements for creating schema objects, the statement can include
instances of <grant statement>
and
<role definition>
. This is a curious aspect of
the SQL standard, as these elements do not really belong to schema
creation.
<schema definition> ::= CREATE SCHEMA <schema
name clause> [ <schema character set specification> ] [
<schema element>... ]
<schema name clause> ::= <schema name> |
AUTHORIZATION <authorization identifier> | <schema name>
AUTHORIZATION <authorization identifier>
If the name of the schema is specified simply as
<schema name>
, then the AUTHORIZATION is the
current user. Otherwise, the specified <authorization
identifier>
is used as the AUTHORIZATION for the schema. If
<schema name>
is omitted, then the name of the
schema is the same as the specified <authorization
identifier>
.
<schema element> ::= <table definition> |
<view definition> | <domain definition> | <character set
definition> | <collation definition> | <transliteration
definition> | <assertion definition> | <trigger
definition> | <user-defined type definition> | <user-defined
cast definition> | <user-defined ordering definition> |
<transform definition> | <schema routine> | <sequence
generator definition> | <grant statement> | <role
definition>
An example of the command is given below. Note that a single semicolon appears at the end. There should be no semicolon between the statements:
CREATE SCHEMA ACCOUNTS AUTHORIZATION DBA CREATE TABLE AB(A INTEGER, ...) CREATE TABLE CD(C CHAR(10), ...) CREATE VIEW VI AS SELECT ... GRANT SELECT ON AB TO PUBLIC GRANT SELECT ON CD TO JOE;
It is not really necessary to create a schema and all its objects as one command. The schema can be created first, and its objects can be created one by one.
DROP SCHEMA
drop schema statement
<drop schema statement> ::= DROP SCHEMA [ IF
EXISTS ] <schema name> [ IF EXISTS ] <drop behavior>
This command destroys an existing schema. If <drop
behavior>
is RESTRICT
, the schema must
be empty, otherwise an error is raised. If CASCADE
is
specified, then all the objects contained in the schema are destroyed
with a CASCADE option.
CREATE TABLE
table definition
<table definition> ::= CREATE [ { <table
scope> | <table type> } ] TABLE <table name> <table
contents source> [ ON COMMIT { PRESERVE | DELETE } ROWS
]
<table scope> ::= { GLOBAL | LOCAL }
TEMPORARY
<table type> :: = MEMORY |
CACHED
<table contents source> ::= <table element
list> | <as subquery clause>
<table element list> ::= <left paren>
<table element> [ { <comma> <table element> }... ]
<right paren>
<table element> ::= <column definition> |
<table constraint definition> | <like
clause>
like clause
A <like clause>
copies all column
definitions from another table into the newly created table. Its three
options indicate if the <default clause>
,
<identity column specification>
and
<generation clause>
associated with the column
definitions are copied or not. If an option is not specified, it
defaults to EXCLUDING
. The <generation
clause>
refers to columns that are generated by an
expression but not to identity columns. All NOT NULL constraints are
copied with the original columns, other constraints are not. The
<like clause>
can be used multiple times,
allowing the new table to have copies of the column definitions of one
or more other tables.
CREATE TABLE t (id INTEGER PRIMARY KEY, LIKE atable INCLUDING DEFAULTS EXCLUDING IDENTITY)
<like clause> ::= LIKE <table name> [
<like options> ]
<like options> ::= <like
option>...
<like option> ::= <identity option> |
<column default option> | <generation
option>
<identity option> ::= INCLUDING IDENTITY |
EXCLUDING IDENTITY
<column default option> ::= INCLUDING DEFAULTS |
EXCLUDING DEFAULTS
<generation option> ::= INCLUDING GENERATED |
EXCLUDING GENERATED
as subquery clause
<as subquery clause> ::= [ <left paren>
<column name list> <right paren> ] AS <table subquery>
{ WITH NO DATA | WITH DATA }
An <as subquery clause>
used in table
definition creates a table based on a <table
subquery>
. This kind of table definition is similar to a
view definition. If WITH DATA
is specified, then the
new table will contain the rows of data returned by the
<table subquery>
.
CREATE TABLE t (a, b, c) AS (SELECT * FROM atable) WITH DATA
column definition
A column definition consists of a <column
name>
and in most cases a <data
type>
or <domain name>
as minimum.
The other elements of <column definition>
are
optional. Each <column name>
in a table is
unique.
<column definition> ::= <column name> [
<data type or domain name> ] [ <default clause> |
<identity column specification> | <identity column sequence
specification> | <generation clause> ] [ <column constraint
definition>... ] [ <collate clause> ]
<data type or domain name> ::= <data type>
| <domain name>
<column constraint definition> ::= [
<constraint name definition> ] <column constraint> [
<constraint characteristics> ]
<column constraint> ::= NOT NULL | <unique
specification> | <references specification> | <check
constraint definition>
A <column constraint definition>
is a
shortcut for a <table constraint definition>
. A
constraint that is defined in this way is automatically turned into a
table constraint. A name is automatically generated for the constraint
and assigned to it.
If a <collate clause>
is specified,
then a UNIQUE or PRIMARY KEY constraint or an INDEX on the column will
use the specified collation. Otherwise the default collation for the
database is used.
generated columns
The value of a column can be autogenerated in two ways.
One way is specific to columns of integral types (INTEGER, BIGINT, etc.) and associates a sequence generator with the column. When a new row is inserted into the table, the value of the column is generated as the next available value in the sequence.
The SQL Standard supports the use of unnamed sequences with the IDENTITY keyword. In addition, HyperSQL supports the use of a named SEQUENCE object, which must be in the same schema as the table.
<identity column specification> ::= GENERATED {
ALWAYS | BY DEFAULT } AS IDENTITY [ <left paren> <common
sequence generator options> <right paren> ]
<identity column sequence specification ::=
GENERATED BY DEFAULT AS SEQUENCE <sequence name>
The <identity column specification>
or
<identity column sequence specification>
can be
specified for only a single column of the table.
The <identity column specification>
is
used for columns which represent values based on an unnamed sequence
generator. It is possible to insert a row into the table without
specifying a value for the column. The value is then generated by the
sequence generators according to its rules. An identity column may or
may not be the primary key. Example below:
CREATE TABLE t (id INTEGER GENERATED ALWAYS AS IDENTITY(START WITH 100), name VARCHAR(20) PRIMARY KEY)
The <identity column sequence
specification>
is used when the column values are based on
a named SEQUENCE object (which must already exist). Example
below:
CREATE TABLE t (id INTEGER GENERATED BY DEFAULT AS SEQUENCE s, name VARCHAR(20) PRIMARY KEY)
Inserting rows is done in the same way for a named or unnamed sequence generator. In both cases, if no value is specified to be inserted, or the DEFAULT keyword is used for the column, the value is generated by the sequence generator. If a value is specified, this value is used if the column definition has the BY DEFAULT specification. If the column definition has the ALWAYS specification, a value can be specified but the OVERRIDING SYSTEM VALUES must be specified in the INSERT statement.
The other way in which the column value is autogenerated is by using the values of other columns in the same row. This method is often used to create an index on a value that is derived from other column values.
<generation clause> ::= GENERATED ALWAYS AS
<generation expression>
<generation expression> ::= <left paren>
<value expression> <right paren>
The <generation clause>
is used for
special columns which represent values based on the values held in other
columns in the same row. The <value expression>
must reference only other, non-generated, columns of the table in the
same row. Any function used in the expression must be deterministic and
must not access SQL-data. No <query expression>
is allowed. When <generation clause>
is used,
<data type>
must be specified.
A generated column can be part of a foreign key or unique constraints or a column of an index. This capability is the main reason for using generated columns. A generated column may contain a formula that computes a value based on the values of other columns. Fast searches of the computed value can be performed when an index is declared on the generated column. Or the computed values can be declared to be unique, using a UNIQUE constraint on the table. The computed column cannot be overridden by user supplied values. When a row is updated and the column values change, the generated columns are computed with the new values.
When a row is inserted into a table, or an existing row is updated, no value except DEFAULT can be specified for a generated column. In the example below, data is inserted into the non-generated columns and the generated column will contain 'Felix the Cat' or 'Pink Panther'.
CREATE TABLE t (id INTEGER PRIMARY KEY, firstname VARCHAR(20), lastname VARCHAR(20), fullname VARCHAR(40) GENERATED ALWAYS AS (firstname || ' ' || lastname)) INSERT INTO t (id, firstname, lastname) VALUES (1, 'Felix', 'the Cat') INSERT INTO t (id, firstname, lastname, fullname) VALUES (2, 'Pink', 'Panther', DEFAULT)
DEFAULT
default clause
A default clause can be used if GENERATED is not specified. If a
column has a <default clause>
then it is
possible to insert a row into the table without specifying a value for
the column.
<default clause> ::= DEFAULT <default
option>
<default option> ::= <literal> |
<datetime value function> | USER | CURRENT_USER | CURRENT_ROLE |
SESSION_USER | SYSTEM_USER | CURRENT_CATALOG | CURRENT_SCHEMA |
CURRENT_PATH | NULL
The type of the <default option>
must
match the type of the column.
In PGS (PostgreSQL) compatibility mode, a NEXTVAL function can be used. Also, in MSS compatibility mode, the default value can be enclosed in parentheses.
CONSTRAINT
constraint name and characteristics
<constraint name definition> ::= CONSTRAINT
<constraint name>
<constraint characteristics> ::= <constraint
check time> [ [ NOT ] DEFERRABLE [ <constraint check time> ]
]
<constraint check time> ::= INITIALLY DEFERRED |
INITIALLY IMMEDIATE
Specify the name of a constraint and its characteristics. By
default the constraint is NOT DEFERRABLE
and
INITIALLY IMMEDIATE
. This means the constraint is
enforced as soon as a data change statement is executed. If
INITIALLY DEFERRED
is specified, then the constraint
is enforced when the session commits. The characteristics must be
compatible. The constraint check time can be changed temporarily for an
SQL session. HyperSQL does not support deferring constraint enforcement.
This feature of the SQL Standard has been criticised because it allows a
session to read uncommitted data that violates database integrity
constraints but has not yet been checked.
CONSTRAINT
table constraint definition
<table constraint definition> ::= [
<constraint name definition> ] <table constraint> [
<constraint characteristics> ]
<table constraint> ::= <unique constraint
definition> | <referential constraint definition> | <check
constraint definition>
Three kinds of constraint can be defined on a table: UNIQUE (including PRIMARY KEY), FOREIGN KEY and CHECK. Each kind has its own rules to limit the values that can be specified for different columns in each row of the table.
UNIQUE
unique constraint definition
<unique constraint definition> ::= <unique
specification> <left paren> <unique column list>
<right paren> | UNIQUE ( VALUE )
<unique specification> ::= UNIQUE | PRIMARY
KEY
<unique column list> ::= <column name
list>
A unique constraint is specified on a single column or on multiple columns. On each set of columns taken together, only one UNIQUE constraint can be specified. Each column of a PRIMARY KEY constraint has an implicit NOT NULL constraint.
If UNIQUE( VALUE )
is specified, the
constraint created on all columns of the table.
FOREIGN KEY
referential constraint definition
<referential constraint definition> ::= FOREIGN
KEY <left paren> <referencing columns> <right paren>
<references specification>
<references specification> ::= REFERENCES
<referenced table and columns> [ MATCH <match type> ] [
<referential triggered action> ]
<match type> ::= FULL | PARTIAL |
SIMPLE
<referencing columns> ::= <reference column
list>
<referenced table and columns> ::= <table
name> [ <left paren> <reference column list> <right
paren> ]
<reference column list> ::= <column name
list>
<referential triggered action> ::= <update
rule> [ <delete rule> ] | <delete rule> [ <update
rule> ]
<update rule> ::= ON UPDATE <referential
action>
<delete rule> ::= ON DELETE <referential
action>
<referential action> ::= CASCADE | SET NULL |
SET DEFAULT | RESTRICT | NO ACTION
A referential constraint allows links to be established between
the rows of two tables. The specified list of <referencing
columns>
corresponds one by one to the columns of the
specified list of <referenced columns>
in
another table (or sometimes in the same table). For each row in the
table, a row must exist in the referenced table with equivalent values
in the two column lists. There must exist a single unique constraint in
the referenced table on all the <referenced
columns>
.
The [ MATCH match type ]
clause is optional and
has an effect only on multi-column foreign keys and only on rows
containing at least a NULL in one of the <referencing
columns>
. If the clause is not specified, MATCH SIMPLE is
the default. If MATCH SIMPLE
is specified, then any
NULL means the row can exist (without a corresponding row in the
referenced table). If MATCH FULL
is specified then
either all the column values must be NULL or none of them.
MATCH PARTIAL
allows any NULL but the non NULL values
must match those of a row in the referenced table. HyperSQL does not
support MATCH PARTIAL
.
Referential actions are specified with ON UPDATE and ON DELETE clauses. These actions take place when a row in the referenced table (the parent table) has referencing rows in the referencing table and it is deleted or modified with any SQL statement. The default is NO ACTION. This means the SQL statement that causes the DELETE or UPDATE is terminated with an exception. The RESTRICT option is similar and works exactly the same without deferrable constraints (which are not allowed by HyperSQL). The other three options, CASCADE, SET NULL and SET DEFAULT all allow the DELETE or UPDATE statement to complete. With DELETE statements the CASCADE option results in the referencing rows to be deleted. With UPDATE statements, the changes to the values of the referenced columns are copied to the referencing rows. With both DELETE or UPDATE statement, the SET NULL option results in the columns of the referencing rows to be set to NULL. Similarly, the SET DEFAULT option results in the columns of the referencing rows to be set to their default values.
CHECK
check constraint definition
<check constraint definition> ::= CHECK <left
paren> <search condition> <right
paren>
A CHECK constraint can exist for a TABLE or for a DOMAIN. The
<search condition>
evaluates to an SQL BOOLEAN
value for each row of the table. Within the <search
condition>
all columns of the table row can be referenced.
For all rows of the table, the <search
condition>
evaluates to TRUE or UNKNOWN. When a new row is
inserted, or an existing row is updated, the <search
condition>
is evaluated and if it is FALSE, the insert or
update fails.
A CHECK constraint for a DOMAIN is similar. In its
<search condition>
, the term VALUE is used to
represents the value to which the DOMAIN applies.
CREATE TABLE t (a VARCHAR(20) CHECK (a IS NOT NULL AND CHARACTER_LENGTH(a) > 2))
The search condition of a CHECK constraint cannot contain any function that is not deterministic. A check constraint is a data integrity constraint, therefore it must hold with respect to the rest of the data in the database. It cannot use values that are temporal or ephemeral. For example CURRENT_USER is a function that returns different values depending on who is using the database, or CURRENT_DATE changes day-to-day. Some temporal expressions are retrospectively deterministic and are allowed in check constraints. For example, (CHECK VALUE < CURRENT_DATE) is valid, because CURRENT_DATE will not move backwards in time, but (CHECK VALUE > CURRENT_DATE) is not acceptable.
If you want to enforce the condition that a date value that is inserted into the database belongs to the future (at the time of insertion), or any similar constraint, then use a TRIGGER with the desired condition.
DROP TABLE
drop table statement
<drop table statement> ::= DROP TABLE [ IF
EXISTS ] <table name> [ IF EXISTS ] <drop
behavior>
Destroy a table. The default drop behaviour is RESTRICT and will
cause the statement to fail if there is any view, routine or foreign key
constraint that references the table. If <drop
behavior>
is CASCADE
, it causes all
schema objects that reference the table to drop. Referencing views are
dropped. In the case of foreign key constraints that reference the
table, the constraint is dropped, rather than the TABLE or DOMAIN that
contains it.
Table manipulation statements change the attributes of tables or modify the objects such as columns and constraints.
SET TABLE CLUSTERED
set table clustered property
<set table clustered statement> ::= SET TABLE
<table name> CLUSTERED ON <left paren> <column name
list> <right paren>
Set the row clustering property of a table. The <column name list> is a list of column names that must correspond to the columns of an existing PRIMARY KEY, UNIQUE or FOREIGN KEY index, or to the columns of a user defined index. This statement is only valid for CACHED or TEXT tables.
Tables rows are stored in the database files as they are created, sometimes at the end of the file, sometimes in the middle of the file. After a CHECKPOINT DEFRAG or SHUTDOWN COMPACT, the rows are reordered according to the primary key of the table, or if there is no primary key, in no particular order.
When several consecutive rows of a table are retrieved during query execution it is more efficient to retrieve rows that are stored adjacent to one another. After executing this command, nothing changes until a CHECKPOINT DEFRAG or SHUTDOWN COMPACT or SHUTDOWN SCRIPT is performed. After these operations, the rows are stored in the specified clustered order. The property is stored in the database and applies to all future reordering of rows. Note that if extensive inserts or updates are performed on the tables, the rows will get out of order until the next reordering.
SET TABLE TYPE
set table type
<set table type statement> ::= SET TABLE
<table name> TYPE { MEMORY | CACHED }
Changes the storage type of an existing table between CACHED and MEMORY types.
Only a user with the DBA role can execute this statement.
SET TABLE writeability
set table write property
<set table read only statement> ::= SET TABLE
<table name> { READ ONLY | READ WRITE }
Set the writeability property of a table. Tables are writeable
by default. This statement can be used to change the property between
READ ONLY
and READ WRITE
. This is
a feature of HyperSQL.
SET TABLE SOURCE
set table source statement
<set table source statement> ::= SET TABLE
<table name> SOURCE <file and options>
[DESC]
<file and options>::= <doublequote>
<file path> [<semicolon> <property>...]
<doublequote>
Set the text source for a text table. This statement cannot be used for tables that are not defined as TEXT TABLE.
Supported Properties
|
quoted = { true | false } |
default is true. If false, treats double quotes as normal characters |
|
all_quoted = { true | false } |
default is false. If true, adds double quotes around all fields. |
|
encoding = <encoding name> |
character encoding for text and character fields, for example, encoding=UTF-8. UTF-16 cannot be used. |
|
ignore_first = { true | false } |
default is false. If true ignores the first line of the file |
|
cache_rows= <numeric value> |
rows of the text file in the cache. Default is 1000 rows |
|
cache_size = <numeric value>r |
total size of the row in the cache. Default is 100 KB. |
|
cache_scale= <numeric value> and cache_size_scale = <numeric value> |
deprecated properties, replaced by cached_rows and cache_size properties above. |
|
fs = <unquoted character> |
field separator |
|
vs = <unquoted character> |
varchar separator |
Special indicators for HyperSQL Text Table separators
|
\semi |
semicolon |
|
\quote |
quote |
|
\space |
space character |
|
\apos |
apostrophe |
|
\n |
newline - Used as an end anchor (like $ in regular expressions) |
|
\r |
carriage return |
|
\t |
tab |
|
\\ |
backslash |
|
\u#### |
a Unicode character specified in hexadecimal |
In the example below, the text source of the table is set to "myfile", the field separator to the pipe symbol, and the varchar separator to the tilde symbol.
SET TABLE mytable SOURCE 'myfile;fs=|;vs=.;vs=~'
Only a user with the DBA role can execute this statement.
SET TABLE SOURCE HEADER
set table source header statement
<set table source header statement> ::= SET
TABLE <table name> SOURCE HEADER <header
string>
Set the header for the text source for a text table. If this
command is used, the <header string>
is used as
the first line of the source file of the text table. This line is not
part of the table data. Only a user with the DBA role can execute this
statement.
SET TABLE SOURCE on-off
set table source on-off statement
<set table source on-off statement> ::= SET
TABLE <table name> SOURCE { ON | OFF }
Attach or detach a text table from its text source. This command does not change the properties or the name of the file that is the source of a text table. When OFF is specified, the command detaches the table from its source and closes the file for the source. In this state, it is not possible to read or write to the table. This allows the user to replace the file with a different file, or delete it. When ON is specified, the source file is read. Only a user with the DBA role can execute this statement
ALTER TABLE
alter table statement
<alter table statement> ::= ALTER TABLE
<table name> <alter table action>
<alter table action> ::= <add column
definition> | <alter column definition> | <drop column
definition> | <add table constraint definition> | <drop
table constraint definition>
Change the definition of a table. Specific types of this statement are covered below.
ADD COLUMN
add column definition
<add column definition> ::= ADD [ COLUMN ]
<column definition> [ BEFORE <other column name>
]
Add a column to an existing table. The <column
definition>
is specified the same way as it is used in
<table definition>
. HyperSQL allows the use of
[ BEFORE <other column name> ]
to specify at
which position the new column is added to the table.
If the table contains rows, the new column must have a
<default clause>
or use one of the forms of
GENERATED. The column values for each row is then filled with the result
of the <default clause>
or the generated
value.
DROP COLUMN
drop column definition
<drop column definition> ::= DROP [ COLUMN ]
<column name> <drop behavior>
Destroy a column of a base table. The <drop
behavior>
is either RESTRICT
or
CASCADE
. If the column is referenced in a table
constraint that references other columns as well as this column, or if
the column is referenced in a VIEW, or the column is referenced in a
TRIGGER, then the statement will fail if RESTRICT
is
specified. If CASCADE
is specified, then any
CONSTRAINT, VIEW or TRIGGER object that references the column is dropped
with a cascading effect.
ADD CONSTRAINT
add table constraint definition
<add table constraint definition> ::= ADD
<table constraint definition>
Add a constraint to a table. The existing rows of the table must conform to the added constraint, otherwise the statement will not succeed.
DROP CONSTRAINT
drop table constraint definition
<drop table constraint definition> ::= DROP
CONSTRAINT <constraint name> <drop
behavior>
Destroy a constraint on a table. The <drop
behavior>
has an effect only on UNIQUE and PRIMARY KEY
constraints. If such a constraint is referenced by a FOREIGN KEY
constraint, the FOREIGN KEY constraint will be dropped if
CASCADE
is specified. If the columns of such a
constraint are used in a GROUP BY clause in the query expression of a
VIEW or another kind of schema object, and a functional dependency
relationship exists between these columns and the other columns in that
query expression, then the VIEW or other schema object will be dropped
when CASCADE
is specified.
ALTER COLUMN
alter column definition
<alter column definition> ::= ALTER [ COLUMN ]
<column name> <alter column action>
<alter column action> ::= <set column default
clause> | <drop column default clause> | <alter column data
type clause> | <alter identity column specification> |
<alter column nullability> | <alter column name> | <add
column identity specification> | <drop column identity
specification>
Change a column and its definition. Specific types of this statement are covered below. See also the RENAME statement above.
SET DEFAULT
set column default clause
<set column default clause> ::= SET <default
clause>
Set the default clause for a column. This can be used if the column is not defined as GENERATED.
DROP DEFAULT
drop column default clause
<drop column default clause> ::= DROP
DEFAULT
Drop the default clause from a column.
SET DATA TYPE
alter column data type clause
<alter column data type clause> ::= SET DATA
TYPE <data type>
Change the declared type of a column. The latest SQL Standard allows only changes to type properties such as maximum length, precision, or scale, and only changes that cause the property to enlarge. HyperSQL allows changing the type if all the existing values can be cast into the new type without string truncation or loss of significant digits.
alter column add identity generator
alter column add identity generator
<add column identity generator> ::= <identity
column specification>
Adds an identity specification to the column. The type of the column must be an integral type and the existing values must not include nulls. This option is specific to HyperSQL
ALTER TABLE mytable ALTER COLUMN id GENERATED ALWAYS AS IDENTITY (START WITH 20000)
alter column identity generator
alter identity column specification
<alter identity column specification> ::=
<alter identity column option>...
<alter identity column option> ::= <alter
sequence generator restart option> | SET <basic sequence generator
option>
Change the properties of an identity column. This command is similar to the commands used for changing the properties of named SEQUENCE objects discussed earlier and can use the same options.
ALTER TABLE mytable ALTER COLUMN id RESTART WITH 1000 ALTER TABLE mytable ALTER COLUMN id SET INCREMENT BY 5
DROP GENERATED
drop column identity generator
<drop column identity specification> ::= DROP
GENERATED
Removes the identity generator from a column. After executing this statement, the column values are no longer generated automatically. This option is specific to HyperSQL
ALTER TABLE mytable ALTER COLUMN id DROP GENERATED
SET [ NOT ] NULL
alter column nullability
<alter column nullability> ::= SET [ NOT ]
NULL
Adds or removes a NOT NULL constraint from a column. This option is specific to HyperSQL
CREATE VIEW
view definition
<view definition> ::= CREATE VIEW <table
name> <view specification> AS <query expression> [ WITH [
CASCADED | LOCAL ] CHECK OPTION ]
<view specification> ::= [ <left paren>
<view column list> <right paren> ]
<view column list> ::= <column name
list>
Define a view. The <query expression>
is a SELECT or similar statement. The <view column
list>
is the list of unique names for the columns of the
view. The number of columns in the <view column
list>
must match the number of columns returned by the
<query expression>
. If <view column
list>
is not specified, then the columns of the
<query expression>
should have unique names and
are used as the names of the view column.
Some views are updatable. As covered elsewhere, an updatable
view is based on a single table or updatable view. For updatable views,
the optional CHECK OPTION
clause can be specified. If
this option is specified, then if a row of the view is updated or a new
row is inserted into the view, then it should contain such values that
the row would be included in the view after the change. If WITH
CASCADED CHECK OPTION
is specified, then if the
<query expression>
of the view references
another view, then the search condition of the underlying view should
also be satisfied by the update or insert operation.
DROP VIEW
drop view statement
<drop view statement> ::= DROP VIEW [ IF EXISTS
] <table name> [ IF EXISTS ] <drop
behavior>
Destroy a view. The <drop behavior>
is
similar to dropping a table.
ALTER VIEW
alter view statement
<alter view statement> ::= ALTER VIEW <table
name> <view specification> AS <query expression> [ WITH [
CASCADED | LOCAL ] CHECK OPTION ]
Alter a view. The statement is otherwise identical to CREATE VIEW. The new definition replaces the old. If there are database objects such as routines or views that reference the view, then these objects are recompiled with the new view definition. If the new definition is not compatible, the statement fails.
CREATE DOMAIN
domain definition
<domain definition> ::= CREATE DOMAIN <domain
name> [ AS ] <predefined type> [ <default clause> ] [
<domain constraint>... ] [ <collate clause>
]
<domain constraint> ::= [ <constraint name
definition> ] <check constraint definition> [ <constraint
characteristics> ]
Define a domain. Although a DOMAIN is not strictly a type in the
SQL Standard, it can be informally considered as a type. A DOMAIN is
based on a <predefined type>
, which is a base
type defined by the Standard. It can have a <default
clause>
, similar to a column default clause. It can also
have one or more CHECK constraints which limit the values that can be
assigned to a column or variable that has the DOMAIN as its type.
CREATE DOMAIN valid_string AS VARCHAR(20) DEFAULT 'NO VALUE' CHECK (value IS NOT NULL AND CHARACTER_LENGTH(value) > 2)
ALTER DOMAIN
alter domain statement
<alter domain statement> ::= ALTER DOMAIN
<domain name> <alter domain action>
<alter domain action> ::= <set domain default
clause> | <drop domain default clause> | <add domain
constraint definition> | <drop domain constraint
definition>
Change a domain and its definition.
SET DEFAULT
set domain default clause
<set domain default clause> ::= SET <default
clause>
Set the default value in a domain.
DROP DEFAULT
drop domain default clause
<drop domain default clause> ::= DROP
DEFAULT
Remove the default clause of a domain.
ADD CONSTRAINT
add domain constraint definition
<add domain constraint definition> ::= ADD
<domain constraint>
Add a constraint to a domain.
DROP CONSTRAINT
drop domain constraint definition
<drop domain constraint definition> ::= DROP
CONSTRAINT <constraint name>
Destroy a constraint on a domain. If the <drop
behavior>
is CASCADE
, and the constraint
is a UNIQUE constraint which is referenced by a FOREIGN KEY constraint
on another table, then the FOREIGN KEY constraint is also
dropped.
DROP DOMAIN
drop domain statement
<drop domain statement> ::= DROP DOMAIN
<domain name> <drop behavior>
Destroy a domain. If <drop behavior>
is
CASCADE
, it works differently from most other
objects. If a table features a column of the specified DOMAIN, the
column survives and inherits the DEFAULT CLAUSE, and the CHECK
CONSTRAINT of the DOMAIN.
CREATE TRIGGER
trigger definition
<trigger definition> ::= CREATE TRIGGER
<trigger name> <trigger action time> <trigger event>
ON <table name> [ REFERENCING <transition table or variable
list> ] <triggered action>
<trigger action time> ::= BEFORE | AFTER |
INSTEAD OF
<trigger event> ::= INSERT | DELETE | UPDATE [
OF <trigger column list> ]
<trigger column list> ::= <column name
list>
<triggered action> ::= [ FOR EACH { ROW |
STATEMENT } ] [ <triggered when clause> ] <triggered SQL
statement>
<triggered when clause> ::= WHEN <left
paren> <search condition> <right
paren>
<triggered SQL statement> ::= <SQL procedure
statement> | BEGIN ATOMIC { <SQL procedure statement>
<semicolon> }... END | [QUEUE <integer literal>] [NOWAIT]
CALL <HSQLDB trigger class FQN>
<transition table or variable list> ::=
<transition table or variable>...
<transition table or variable> ::= OLD [ ROW ] [
AS ] <old transition variable name> | NEW [ ROW ] [ AS ] <new
transition variable name> | OLD TABLE [ AS ] <old transition table
name> | NEW TABLE [ AS ] <new transition table
name>
<old transition table name> ::= <transition
table name>
<new transition table name> ::= <transition
table name>
<transition table name> ::=
<identifier>
<old transition variable name> ::=
<correlation name>
<new transition variable name> ::=
<correlation name>
Trigger definition is a relatively complex statement. The
combination of <trigger action time>
and
<trigger event>
determines the type of the
trigger. Examples include BEFORE DELETE, AFTER UPDATE, INSTEAD OF
INSERT. If the optional [ OF <trigger column list>
]
is specified for an UPDATE trigger, then the trigger is
activated only if one of the columns that is in the <trigger
column list>
is specified in the UPDATE statement that
activates the trigger.
If a trigger is FOR EACH ROW
, which is the
default option, then the trigger is activated for each row of the table
that is affected by the execution of an SQL statement. Otherwise, it is
activated once only per statement execution. In the first case, there is
a before and after state for each row. For UPDATE triggers, both before
and after states exist, representing the row before the update, and
after the update. For DELETE, triggers, there is only a before state.
For INSERT triggers, there is only an after state. If a trigger is
FOR EACH STATEMENT
, then a transient table is created
containing all the rows for the before state and another transient table
is created for the after state.
The [ REFERENCING <transition table or variable>
]
is used to give a name to the before and after data row or
table. This name can be referenced in the <SQL procedure
statement>
to access the data.
The optional <triggered when clause>
is a
search condition, similar to the search condition of a DELETE or UPDATE
statement. If the search condition is not TRUE for a row, then the
trigger is not activated for that row.
The <SQL procedure statement>
is limited
to INSERT, DELETE, UPDATE and MERGE statements.
The <HSQLDB trigger class FQN>
is a
delimited identifier that contains the fully qualified name of a Java
class that implements the org.hsqldb.Trigger
interface.
HyperSQL do not yet allow the use of OLD TABLE or NEW TABLE in statement level trigger definitions.
DROP TRIGGER
drop trigger statement
<drop trigger statement> ::= DROP TRIGGER
<trigger name>
Destroy a trigger.
schema routine
SQL-invoked routine
<SQL-invoked routine> ::= <schema
routine>
<schema routine> ::= <schema procedure> |
<schema function>
<schema procedure> ::= CREATE <SQL-invoked
procedure>
<schema function> ::= CREATE <SQL-invoked
function>
<SQL-invoked procedure> ::= PROCEDURE <schema
qualified routine name> <SQL parameter declaration list>
<routine characteristics> <routine body>
<SQL-invoked function> ::= { <function
specification> | <method specification designator> }
<routine body>
<SQL parameter declaration list> ::= <left
paren> [ <SQL parameter declaration> [ { <comma> <SQL
parameter declaration> }... ] ] <right
paren>
<SQL parameter declaration> ::= [ <parameter
mode> ] [ <SQL parameter name> ] <parameter type> [
RESULT ]
<parameter mode> ::= IN | OUT |
INOUT
<parameter type> ::= <data
type>
<function specification> ::= FUNCTION <schema
qualified routine name> <SQL parameter declaration list>
<returns clause> <routine characteristics> [ <dispatch
clause> ]
<method specification designator> ::= SPECIFIC
METHOD <specific method name> | [ INSTANCE | STATIC | CONSTRUCTOR
] METHOD <method name> <SQL parameter declaration list> [
<returns clause> ] FOR <schema-resolved user-defined type
name>
<routine characteristics> ::= [ <routine
characteristic>... ]
<routine characteristic> ::= <language
clause> | <parameter style clause> | SPECIFIC <specific
name> | <deterministic characteristic> | <SQL-data access
indication> | <null-call clause> | <returned result sets
characteristic> | <savepoint level
indication>
<savepoint level indication> ::= NEW SAVEPOINT
LEVEL | OLD SAVEPOINT LEVEL
<returned result sets characteristic> ::=
DYNAMIC RESULT SETS <maximum returned result
sets>
<parameter style clause> ::= PARAMETER STYLE
<parameter style>
<dispatch clause> ::= STATIC
DISPATCH
<returns clause> ::= RETURNS <returns
type>
<returns type> ::= <returns data type> [
<result cast> ] | <returns table type>
<returns table type> ::= TABLE <table
function column list>
<table function column list> ::= <left
paren> <table function column list element> [ { <comma>
<table function column list element> }... ] <right
paren>
<table function column list element> ::=
<column name> <data type>
<result cast> ::= CAST FROM <result cast from
type>
<result cast from type> ::= <data type> [
<locator indication> ]
<returns data type> ::= <data type> [
<locator indication> ]
<routine body> ::= <SQL routine spec> |
<external body reference>
<SQL routine spec> ::= [ <rights clause> ]
<SQL routine body>
<rights clause> ::= SQL SECURITY INVOKER | SQL
SECURITY DEFINER
<SQL routine body> ::= <SQL procedure
statement>
<external body reference> ::= EXTERNAL [ NAME
<external routine name> ] [ <parameter style clause>
]
<parameter style> ::= SQL |
GENERAL
<deterministic characteristic> ::= DETERMINISTIC
| NOT DETERMINISTIC
<SQL-data access indication> ::= NO SQL |
CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA
<null-call clause> ::= RETURNS NULL ON NULL
INPUT | CALLED ON NULL INPUT
<maximum returned result sets> ::= <unsigned
integer>
Define an SQL-invoked routine. A few of the options are not used by HyperSQL and have default behaviours. See the SQL-Invoked Routines chapter for more details of various options and examples.
ALTER routine
alter routine statement
<alter routine statement> ::= ALTER <specific
routine designator> [ <alter routine characteristics> ] [
RESTRICT ] <routine body>
<alter routine characteristics> ::= <alter
routine characteristic>...
<alter routine characteristic> ::= <language
clause> | <parameter style clause> | <SQL-data access
indication> | <null-call clause> | <returned result sets
characteristic>
<alter routine body> ::= <SQL routine
body>
Alter the characteristic and the body of an SQL-invoked routine. If RESTRICT is specified and the routine is already used in a a different routine or view definition, an exception is raised. Altering the routine changes the implementation without changing the parameters. Defining recursive SQL/PSM SQL functions is only possible by altering a non-recursive routine body. An example is given in the SQL-Invoked Routines chapter.
An example is given below for a function defined as a Java method, then redefined as an SQL function.
CREATE FUNCTION zero_pad(x BIGINT, digits INT, maxsize INT) RETURNS CHAR VARYING(100) SPECIFIC zero_pad_01 NO SQL DETERMINISTIC LANGUAGE JAVA EXTERNAL NAME 'CLASSPATH:org.hsqldb.lib.StringUtil.toZeroPaddedString'; ALTER SPECIFIC ROUTINE zero_pad_01 LANGUAGE SQL BEGIN ATOMIC DECLARE str VARCHAR(128); SET str = CAST(x AS VARCHAR(128)); SET str = SUBSTRING('0000000000000' FROM 1 FOR digits - CHAR_LENGTH(str)) + str; return str; END
DROP
drop routine statement
<drop routine statement> ::= DROP <specific
routine designator> <drop behavior>
Destroy an SQL-invoked routine.
CREATE SEQUENCE
sequence generator definition
<sequence generator definition> ::= CREATE
SEQUENCE <sequence generator name> [ <sequence generator
options> ]
<sequence generator options> ::= <sequence
generator option> ...
<sequence generator option> ::= <sequence
generator data type option> | <common sequence generator
options>
<common sequence generator options> ::=
<common sequence generator option> ...
<common sequence generator option> ::=
<sequence generator start with option> | <basic sequence
generator option>
<basic sequence generator option> ::=
<sequence generator increment by option> | <sequence generator
maxvalue option> | <sequence generator minvalue option> |
<sequence generator cycle option>
<sequence generator data type option> ::= AS
<data type>
<sequence generator start with option> ::= START
WITH <sequence generator start value>
<sequence generator start value> ::= <signed
numeric literal>
<sequence generator increment by option> ::=
INCREMENT BY <sequence generator increment>
<sequence generator increment> ::= <signed
numeric literal>
<sequence generator maxvalue option> ::=
MAXVALUE <sequence generator max value> | NO
MAXVALUE
<sequence generator max value> ::= <signed
numeric literal>
<sequence generator minvalue option> ::=
MINVALUE <sequence generator min value> | NO
MINVALUE
<sequence generator min value> ::= <signed
numeric literal>
<sequence generator cycle option> ::= CYCLE | NO
CYCLE
Define a named sequence generator. A SEQUENCE object generates a
sequence of integers according to the specified rules. The simple
definition without the options defines a sequence of numbers in INTEGER
type starting at 1 and incrementing by 1. By default the
CYCLE
property is set and the minimum and maximum
limits are the minimum and maximum limits of the type of returned
values. There are self-explanatory options for changing various
properties of the sequence. The MAXVALUE
and
MINVALUE
specify the upper and lower limits. If
CYCLE
is specified, after the sequence returns the
highest or lowest value in range, the next value will respectively be
the lowest or highest value in range. If NO CYCLE
is
specified, the use of the sequence generator results in an error once
the limit has been reached.
The integer types: SMALLINT, INTEGER, BIGINT, DECIMAL and NUMERIC can be used as the type of the sequence. DECIMAL and NUMERIC types must have a scale of 0 and a precision not exceeding 18.
ALTER SEQUENCE
alter sequence generator statement
<alter sequence generator statement> ::= ALTER
SEQUENCE <sequence generator name> <alter sequence generator
options>
<alter sequence generator options> ::= <alter
sequence generator option>...
<alter sequence generator option> ::= <alter
sequence generator restart option> | <basic sequence generator
option>
<alter sequence generator restart option> ::=
RESTART [ WITH <sequence generator restart value>
]
<sequence generator restart value> ::=
<signed numeric literal>
Change the definition of a named sequence generator. The same
options that are used in the definition of the SEQUENCE can be used to
alter it. The exception is the option for the start value which is
RESTART WITH
for the ALTER SEQUENCE statement.
If RESTART is used by itself (without a value), then the current value of the sequence is reset to the start value. Otherwise, the current value is reset to the given restart value.
DROP SEQUENCE
drop sequence generator statement
<drop sequence generator statement> ::= DROP
SEQUENCE [ IF EXISTS ] <sequence generator name> [ IF EXISTS ]
<drop behavior>
Destroy an external sequence generator. If the <drop
behavior>
is CASCADE
, then all objects
that reference the sequence are dropped. These objects can be VIEW,
ROUTINE or TRIGGER objects.
SQL procedure statement
SQL procedure statement
The definition of CREATE TRIGGER and CREATE PROCEDURE statements refers to <SQL procedure statement>. The definition of this element is given below. However, only a subset of these statements are allowed in trigger or routine definition.
<SQL procedure statement> ::= <SQL executable
statement>
<SQL executable statement> ::= <SQL schema
statement> | <SQL data statement> | <SQL control
statement> | <SQL transaction statement> | <SQL connection
statement> | <SQL session statement> | <SQL diagnostics
statement> | <SQL dynamic statement>
<SQL schema statement> ::= <SQL schema
definition statement> | <SQL schema manipulation
statement>
<SQL schema definition statement> ::= <schema
definition> | <table definition> | <view definition> |
<SQL-invoked routine> | <grant statement> | <role
definition> | <domain definition> | <character set
definition> | <collation definition> | <transliteration
definition> | <assertion definition> | <trigger
definition> | <user-defined type definition> | <user-defined
cast definition> | <user-defined ordering definition> |
<transform definition> | <sequence generator
definition>
<SQL schema manipulation statement> ::= <drop
schema statement> | <alter table statement> | <drop table
statement> | <drop view statement> | <alter routine
statement> | <drop routine statement> | <drop user-defined
cast statement> | <revoke statement> | <drop role
statement> | <alter domain statement> | <drop domain
statement> | <drop character set statement> | <drop
collation statement> | <drop transliteration statement> |
<drop assertion statement> | <drop trigger statement> |
<alter type statement> | <drop data type statement> |
<alter sequence generator statement> | <drop sequence generator
statement>
CREATE INDEX
create index statement
<create index statement> ::= CREATE INDEX
<index name> ON <table name> <left paren> {<column
name> [ASC | DESC]}, ... <right paren>
Creates an index on a group of columns of a table. The optional [ASC | DESC] specifies if the column is indexed in the ascending or descending order, but has no effect on how the index is created (it is allowed for compatibility with other database engines). HyperSQL can use all indexes in ascending or descending order as needed. Indexes should not duplicate the columns of PRIMARY KEY, UNIQUE or FOREIGN key constraints as each of these constraints creates an index automatically.
DROP INDEX
drop index statement
<drop index statement> ::= DROP INDEX [ IF
EXISTS ] <index name> [ IF EXISTS ]
Destroy an index.
ALTER INDEX
change the columns of an index
<alter index statement> ::= ALTER INDEX
<index name> <left paren> {<column name>} , ...
<right paren>
Redefine an index with a new column list. This statement is more efficient than dropping an existing index and creating a new one.
CREATE TYPE
user-defined type definition
<user-defined type definition> ::= CREATE TYPE
<user-defined type body>
<user-defined type body> ::= <schema-resolved
user-defined type name> [ AS <representation>
]
<representation> ::= <predefined
type>
Define a user-defined type. Currently only simple distinct types can be defined without further attributes.
CREATE CAST
user-defined cast definition
<user-defined cast definition> ::= CREATE CAST
<left paren> <source data type> AS <target data type>
<right paren> WITH <cast function> [ AS ASSIGNMENT
]
<cast function> ::= <specific routine
designator>
<source data type> ::= <data
type>
<target data type> ::= <data
type>
Define a user-defined cast. This feature may be supported in a future versions of HyperSQL.
DROP CAST
drop user-defined cast statement
<drop user-defined cast statement> ::= DROP CAST
<left paren> <source data type> AS <target data type>
<right paren> <drop behavior>
Destroy a user-defined cast. This feature may be supported in a future versions of HyperSQL.
CREATE CHARACTER SET
character set definition
<character set definition> ::= CREATE CHARACTER
SET <character set name> [ AS ] <character set source> [
<collate clause> ]
<character set source> ::= GET <character set
specification>
Define a character set. A new CHARACTER SET is based on an
existing CHARACTER SET. The optional <collate
clause>
specifies the collation to be used, otherwise the
collation is inherited from the default collation for the source
CHARACTER SET. Currently this statement has no effect, as the character
set used by HSQLDB is Unicode and there is no need for subset character
sets.
DROP CHARACTER SET
drop character set statement
<drop character set statement> ::= DROP
CHARACTER SET <character set name>
Destroy a character set. If the character set name is
referenced in any database object, the command fails. Note that
CASCADE
or RESTRICT
cannot be
specified for this command.
CREATE COLLATION
collation definition
<collation definition> ::= CREATE COLLATION
<collation name> FOR <character set specification> FROM
<existing collation name> [ <pad characteristic>
]
<existing collation name> ::= <collation
name>
<pad characteristic> ::= NO PAD | PAD
SPACE
Define a collation. A new collation is based on an existing
COLLATION and applies to an existing CHARACTER SET. The <character
set specification> is always SQL_TEXT. The <existing collation
name> is either SQL_TEXT or one of the language collations supported
by HSQLDB. The <pad characteristic>
specifies
whether strings are padded with spaces for comparison.
This statement is typically used when a collation is required that
does not pad spaces before comparing two strings. For example,
CREATE COLLATION FRENCH_NOPAD FOR INFORMATION_SCHEMA.SQL_TEXT
FROM "French" NO PAD
, results in a French collation without
padding. This collation can be used for sorting or for individual
columns of tables.
DROP COLLATION
drop collation statement
<drop collation statement> ::= DROP COLLATION
<collation name> <drop behavior>
Destroy a collation. If the <drop
behavior>
is CASCADE
, then all
references to the collation revert to the default collation that would
be in force if the dropped collation was not specified.
CREATE TRANSLATION
transliteration definition
<transliteration definition> ::= CREATE
TRANSLATION <transliteration name> FOR <source character set
specification> TO <target character set specification> FROM
<transliteration source>
<source character set specification> ::=
<character set specification>
<target character set specification> ::=
<character set specification>
<transliteration source> ::= <existing
transliteration name> | <transliteration
routine>
<existing transliteration name> ::=
<transliteration name>
<transliteration routine> ::= <specific
routine designator>
Define a character transliteration. This feature may be supported in a future versions of HyperSQL.
DROP TRANSLATION
drop transliteration statement
<drop transliteration statement> ::= DROP
TRANSLATION <transliteration name>
Destroy a character transliteration. This feature may be supported in a future versions of HyperSQL.
CREATE ASSERTION
assertion definition
<assertion definition> ::= CREATE ASSERTION
<constraint name> CHECK <left paren> <search
condition> <right paren> [ <constraint characteristics>
]
Specify an integrity constraint. This feature may be supported in a future versions of HyperSQL.
DROP ASSERTION
drop assertion statement
<drop assertion statement> ::= DROP ASSERTION
<constraint name> [ <drop behavior> ]
Destroy an assertion. This feature may be supported in a future versions of HyperSQL.
The Information Schema is a special schema in each catalog. The SQL Standard defines a number of character sets and domains in this schema. In addition, all the implementation-defined collations belong to the Information Schema.
The SQL Standard defines many views in the Information Schema. These views show the properties of the database objects that currently exist in the database. When a user accesses one these views, only the properties of database objects that the user can access are included.
HyperSQL supports all the views defined by the Standard, apart from a few views that report on extended user-defined types and other optional features of the Standard that are not supported by HyperSQL.
HyperSQL also adds some views to the Information Schema. These views are for features that are not reported in any of the views defined by the Standard, or for use by JDBC DatabaseMetaData.
The SQL Standard defines a number of character sets and domains in the INFORMATION SCHEMA.
These domains are used in the INFORMATION SCHEMA views:
CARDINAL_NUMBER, YES_OR_NO, CHARACTER_DATA, SQL_IDENTIFIER, TIME_STAMP
All available collations are in the INFORMATION SCHEMA.
HyperSQL supports a vast range of views in the INFORMATION_SCHEMA. These include views specified by the SQL Standard, SQL/Schemata part, plus views that are specific to HyperSQL and are used for JDBC DatabaseMetaData queries, which are based on SQL/CLI part, or other information that is not covered by the SQL Standard. The names of views that are not part of SQL/Schemata start with SYSTEM_.
The views cover different types of information. These are covered in the next sections.
Users with the special ADMIN role can see the full information on all database objects. Ordinary, non-admin users can see information on the objects for which they have some privileges.
The rows returned to a non-admin user exclude objects on which the user has no privilege. The extent of the information in visible rows varies with the user's privilege. For example, the owner of a VIEW can see the text of the view query, but a user of the view cannot see this text. When a user cannot see the contents of some column, null is returned for that column.
The names of database objects are stored in hierarchical views. The top level view is INFORMATION_SCHEMA_CATALOG_NAME.
Below this level, there is a group of views that covers authorizations and roles, without referencing schema objects. These are AUTHORIZATIONS and ADMINSTRABLE_ROLE_AUTHORIZATIONS.
Also below the top level, there is the SCHEMATA view, which lists the schemas in the catalog.
The views that refer to top-level schema objects are divided by object type. These includes ASSERTIONS, CHARACTER_SETS, COLLATIONS, DOMAINS, ROUTINES, SEQUENCES, TABLES, USER_DEFINED_TYPES and VIEWS.
There are views that refer to objects that are dependent on the top-level schema objects. These include COLUMNS and PARAMETERS, views for constraints, including CHECK_CONSTRAINTS, REFERENTIAL_CONSTRAINTS and TABLE_CONSTRAINTS, and finally the TRIGGERS view.
The usage of each type of top-level object by another is covered by several views. For example TRIGGER_SEQUENCE_USAGE or ROUTINE_TABLE_USAGE.
Several other views list the individual privileges owned or granted to each AUTHORIZATION. For example ROLE_ROUTINE_GRANTS or TABLE_PRIVILEGES.
The INFORMATION_SCHEMA contains comprehensive information on the data types of each schema object and its elements. For example, the ROUTINES view includes the return data type for each FUNCTION definition. The columns for this information contain nulls for rows that cover PROCEDURE definitions.
The COLUMNS, PARAMETERS and SEQUENCES views contain the type information in columns with similar names.
The type information for ARRAY types is returned in the ELEMENT_TYPES view. When a row of the COLUMNS or other view indicates that the type of the object is an ARRAY type, then there is a corresponding entry for this row in the ELEMENT_TYPES view. The following columns in the ELEMENTS_TYPES view identify the database object whose data type is being described: OBJECT_CATALOG, OBJECT_SCHEMA, OBJECT_NAME, OBJECT_TYPE, COLLECTION_TYPE_IDENTIFIER. The last column's counterpart in the COLUMNS view is named differently as DTD_IDENTIFIER. So in order to determine the array element type of a column, an equi-join between the COLUMNS and ELEMENT_TYPES tables on the six listed columns in the ELEMENT_TYPES view and their counterparts in the COLUMNS view is needed.
A group of views, including SQL_IMPLEMENTATION_INFO, SQL_FEATURES, SQL_SIZING and others cover the capabilities of HyperSQL in detail. These views hold static data and can be explored even when the database is empty.
There are some HyperSQL custom views cover the current state of operation of the database. These include SYSTEM_CACHEINFO, SYSTEM_SESSIONINFO and SYSTEM_SESSIONS views.
The following views are defined by the SQL Standard and supported by HyperSQL. The columns and contents exactly match the Standard requirements.
ADMINISTRABLE_ROLE_AUTHORIZATIONS
Information on ROLE authorizations, all granted by the admin role.
APPLICABLE_ROLES
Information on ROLE authorizations for the current user
ASSERTIONS
Empty view as ASSERTION objects are not yet supported.
AUTHORIZATIONS
Top level information on USER and ROLE objects in the database
CHARACTER_SETS
List of supported CHARACTER SET objects
CHECK_CONSTRAINTS
Additional information specific to each CHECK constraint, including the search condition
CHECK_CONSTRAINT_ROUTINE_USAGE
Information on FUNCTION objects referenced in CHECK constraints search conditions
COLLATIONS
Information on collations supported by the database.
COLUMNS
Information on COLUMN objects in TABLE and VIEW definitions
COLUMN_COLUMN_USAGE
Information on references to COLUMN objects from other, GENERATED, COLUMN objects
COLUMN_DOMAIN_USAGE
Information on DOMAIN objects used in type definition of COLUMN objects
COLUMN_PRIVILEGES
Information on privileges on each COLUMN object, granted to different ROLE and USER authorizations
COLUMN_UDT_USAGE
Information on distinct TYPE objects used in type definition of COLUMN objects
CONSTRAINT_COLUMN_USAGE
Information on COLUMN objects referenced by CONSTRAINT objects in the database
CONSTRAINT_TABLE_USAGE
Information on TABLE and VIEW objects referenced by CONSTRAINT objects in the database
DATA_TYPE_PRIVILEGES
Information on top level schema objects of various kinds that reference TYPE objects
DOMAINS
Top level information on DOMAIN objects in the database.
DOMAIN_CONSTRAINTS
Information on CONSTRAINT definitions used for DOMAIN objects
ELEMENT_TYPES
Information on the type of elements of ARRAY used in database columns or routine parameters and return values
ENABLED_ROLES
Information on ROLE privileges enabled for the current session
INFORMATION_SCHEMA_CATALOG_NAME
Information on the single CATALOG object of the database
KEY_COLUMN_USAGE
Information on COLUMN objects of tables that are used by PRIMARY KEY, UNIQUE and FOREIGN KEY constraints
PARAMETERS
Information on parameters of each FUNCTION or PROCEDURE
REFERENTIAL_CONSTRAINTS
Additional information on FOREIGN KEY constraints, including triggered action and name of UNIQUE constraint they refer to
ROLE_AUTHORIZATION_DESCRIPTORS
ROLE_COLUMN_GRANTS
Information on privileges on COLUMN objects granted to or by the current session roles
ROLE_ROUTINE_GRANTS
Information on privileges on FUNCTION and PROCEDURE objects granted to or by the current session roles
ROLE_TABLE_GRANTS
Information on privileges on TABLE and VIEW objects granted to or by the current session roles
ROLE_UDT_GRANTS
Information on privileges on TYPE objects granted to or by the current session roles
ROLE_USAGE_GRANTS
Information on privileges on USAGE privileges granted to or by the current session roles
ROUTINE_COLUMN_USAGE
Information on COLUMN objects of different tables that are referenced in FUNCTION and PROCEDURE definitions
ROUTINE_JAR_USAGE
Information on JAR usage by Java language FUNCTION and PROCEDURE objects.
ROUTINE_PRIVILEGES
Information on EXECUTE privileges granted on PROCEDURE and FUNCTION objects
ROUTINE_ROUTINE_USAGE
Information on PROCEDURE and FUNCTION objects that are referenced in FUNCTION and PROCEDURE definitions
ROUTINE_SEQUENCE_USAGE
Information on SEQUENCE objects that are referenced in FUNCTION and PROCEDURE definitions
ROUTINE_TABLE_USAGE
Information on TABLE and VIEW objects that are referenced in FUNCTION and PROCEDURE definitions
ROUTINES
Top level information on all PROCEDURE and FUNCTION objects in the database
SCHEMATA
Information on all the SCHEMA objects in the database
SEQUENCES
Information on SEQUENCE objects
SQL_FEATURES
List of all SQL:2008 standard features, including information on whether they are supported or not supported by HyperSQL
SQL_IMPLEMENTATION_INFO
Information on name, capabilities and defaults of the database engine software.
SQL_PACKAGES
List of the SQL:2008 Standard packages, including information on whether they are supported or not supported by HyperSQL
SQL_PARTS
List of the SQL:2008 Standard parts, including information on whether they are supported or not supported by HyperSQL
SQL_SIZING
List of the SQL:2008 Standard maximum supported sizes for different features as supported by HyperSQL
SQL_SIZING_PROFILES
TABLES
Information on all TABLE and VIEW object, including the INFORMATION_SCHEMA views themselves
TABLE_CONSTRAINTS
Information on all table level constraints, including PRIMARY KEY, UNIQUE, FOREIGN KEY and CHECK constraints
TABLE_PRIVILEGES
Information on privileges on TABLE and VIEW objects owned or given to the current user
TRANSLATIONS
TRIGGERED_UPDATE_COLUMNS
Information on columns that have been used in TRIGGER definitions in the ON UPDATE clause
TRIGGERS
Top level information on the TRIGGER definitions in the databases
TRIGGER_COLUMN_USAGE
Information on COLUMN objects that have been referenced in the body of TRIGGER definitions
TRIGGER_ROUTINE_USAGE
Information on FUNCTION and PROCEDURE objects that have been used in TRIGGER definitions
TRIGGER_SEQUENCE_USAGE
Information on SEQUENCE objects that been referenced in TRIGGER definitions
TRIGGER_TABLE_USAGE
Information on TABLE and VIEW objects that have been referenced in TRIGGER definitions
USAGE_PRIVILEGES
Information on USAGE privileges granted to or owned by the current user
USER_DEFINED_TYPES
Top level information on TYPE objects in the database
VIEWS
Top Level information on VIEW objects in the database
VIEW_COLUMN_USAGE
Information on COLUMN objects referenced in the query expressions of the VIEW objects
VIEW_ROUTINE_USAGE
Information on FUNCTION and PROCEDURE objects that have been used in the query expressions of the VIEW objects
VIEW_TABLE_USAGE
Information on TABLE and VIEW objects that have been referenced in the query expressions of the VIEW objects
The following views are specific to HyperSQL. Most of these views are used directly by JDBC DatabaseMetaData method calls and are indicated as such. Some views contain information that is specific to HyperSQL and is not covered by the SQL Standard views.
SYSTEM_BESTROWIDENTIFIER
For DatabaseMetaData.getBestRowIdentifier
SYSTEM_CACHEINFO
Contains the current settings and variables of the data cache used for all CACHED tables, and the data cache of each TEXT table.
SYSTEM_COLUMN_SEQUENCE_USAGE
Contains a row for each column that is defined as GENERATED BY DEFAULT AS SEQUENCE with the column name and sequence name
SYSTEM_COLUMNS
For DatabaseMetaData.getColumns, contains a row for each column
SYSTEM_COMMENTS
Contains the user-defined comments added to tables and their columns.
SYSTEM_CONNECTION_PROPERTIES
For DatabaseMetaData.getClientInfoProperties
SYSTEM_CROSSREFERENCE
Full list of all columns referenced by FOREIGN KEY constraints. For DatabaseMetaData.getCrossReference, getExportedKeys and getImportedKeys.
SYSTEM_INDEXINFO
For DatabaseMetaData.getIndexInfo
SYSTEM_PRIMARYKEYS
For DatabaseMetaData.getPrimaryKeys
SYSTEM_PROCEDURECOLUMNS
For DatabaseMetaData.getProcedureColumns
SYSTEM_PROCEDURES
For DatabaseMetaData.getFunctionColumns, getFunctions and getProcedures
SYSTEM_PROPERTIES
Contains the current values of all the database level properties. Settings such as SQL rule enforcement, database transaction model and default transaction level are all reported in this view. The names of the properties are listed in the Properties chapter together with the corresponding SQL statements used to change the properties.
SYSTEM_SCHEMAS
For DatabaseMetaData.getSchemas
SYSTEM_SEQUENCES
SYSTEM_SESSIONINFO
Information on the settings and properties of the current session.
SYSTEM_SESSIONS
Information on all open sessions in the database (when used by a DBA user), or just the current session. Includes the current transaction state of each session.
SYSTEM_TABLES
Information on tables and views for DatabaseMetaData.getTables
SYSTEM_TABLESTATS
Information on table spaces and cardinality for each table
SYSTEM_TABLETYPES
For DatabaseMetaData.getTableTypes
SYSTEM_TEXTTABLES
Information on the settings of each text table.
SYSTEM_TYPEINFO
For DatabaseMetaData.getTypeInfo
SYSTEM_UDTS
For DatabaseMetaData.getUDTs
SYSTEM_USERS
Contains the list of all users in the database (when used by a DBA user), or just the current user.
SYSTEM_VERSIONCOLUMNS
For DatabaseMetaData.getVersionColumns
$Revision: 5212 $
Copyright 2002-2012 Bob Preston and Fred Toussi. Permission is granted to distribute this document without any alteration under the terms of the HSQLDB license. Additional permission is granted to the HSQL Development Group to distribute this document with or without alterations under the terms of the HSQLDB license.
2013-07-08 15:31:27+0100
Table of Contents
Text Table support for HSQLDB was originally developed by Bob Preston independently from the Project. Subsequently Bob joined the Project and incorporated this feature into version 1.7.0, with a number of enhancements, especially the use of conventional SQL commands for specifying the files used for Text Tables.
In a nutshell, Text Tables are CSV or other delimited files treated as SQL tables. Any ordinary CSV or other delimited file can be used. The full range of SQL queries can be performed on these files, including SELECT, INSERT, UPDATE and DELETE. Indexes and unique constraints can be set up, and foreign key constraints can be used to enforce referential integrity between Text Tables themselves or with conventional tables.
The delimited file can be created by the engine, or an existing file can be used.
HyperSQL with Text Table support is the only comprehensive solution that employs the power of SQL and the universal reach of JDBC to handle data stored in text files.
Text Tables are defined similarly to conventional tables with the added TEXT keyword.
CREATE TEXT TABLE <tablename> (<column definition> [<constraint definition>])
The table is at first empty and cannot be written to. An additional SET command specifies the file and the separator character that the Text table uses. It assigns the file to the table.
SET TABLE <tablename> SOURCE <quoted_filename_and_options> [DESC]
A Text table without a file assigned to it is READ ONLY and EMPTY.
Reassigning a Text Table definition to a new file has implications in the following areas:
The user is required to be an administrator.
Existing transactions are committed at this point.
Constraints, including foreign keys referencing this table, are kept intact but not checked. It is the responsibility of the administrator to ensure their integrity.
The new source file is scanned and indexes are built when it is assigned to the table. At this point any violation of NOT NULL, UNIQUE or PRIMARY KEY constraints are caught and the assignment is aborted. However, foreign key constraints are not checked at the time of assignment or reassignment of the source file.
Empty fields are treated as NULL. These are fields where there is nothing or just spaces between the separators.
Quoted empty strings are treated as empty strings.
The default field separator is a comma (,). A different field separator can be specified within the SET TABLE SOURCE statement. For example, to change the field separator for the table mytable to a vertical bar, place the following in the SET TABLE SOURCE statement, for example:
SET TABLE mytable SOURCE "myfile;fs=|"
Since HSQLDB treats CHAR and VARCHAR strings the same, the ability to assign a different separator to the latter is provided. When a different separator is assigned to a VARCHAR, it will terminate any CSV field of that type. For example, if the first field is CHAR, and the second field VARCHAR, and the separator fs has been defined as the pipe (|) and vs as the period (.) then the data in the CSV file for a row will look like:
First field data|Second field data.Third field data
This facility in effect offers an extra, special separator which can be used in addition to the global separator. The following example shows how to change the default separator to the pipe (|), VARCHAR separator to the period (.) within a SET TABLE SOURCE statement:
SET TABLE mytable SOURCE "myfile;fs=|;vs=."
HSQLDB also recognises the following special indicators for separators:
special indicators for separators
|
\semi |
semicolon |
|
\quote |
single-quote |
|
\space |
space character |
|
\apos |
apostrophe |
|
\n |
newline - Used as an end anchor (like $ in regular expressions) |
|
\r |
carriage return |
|
\t |
tab |
|
\\ |
backslash |
|
\u#### |
a Unicode character specified in hexadecimal |
Furthermore, HSQLDB provides csv file support with three
additional boolean options: ignore_first
,
quoted
and all_quoted
. The
ignore_first
option (default false) tells HSQLDB to
ignore the first line in a file. This option is used when the first line
of the file contains column headings. The all_quoted
option (default false) tells the program that it should use quotes
around all character fields when writing to the source file. The
quoted
option (default true) uses quotes only when
necessary to distinguish a field that contains the separator character.
It can be set to false to prevent the use of quoting altogether and
treat quote characters as normal characters. These options may be
specified within the SET TABLE SOURCE
statement:
SET TABLE mytable SOURCE "myfile;ignore_first=true;all_quoted=true"
When the default options all_quoted=
false
and quoted=true
are in
force, fields that are written to a line of the csv file will be quoted
only if they contain the separator or the quote character. The quote
character is doubled when used inside a string. When
all_quoted=false
and quoted=false
the quote character is not doubled. With this option, it is not possible
to insert any string containing the separator into the table, as it
would become impossible to distinguish from a separator. While reading
an existing data source file, the program treats each individual field
separately. It determines that a field is quoted only if the first
character is the quote character. It interprets the rest of the field on
this basis.
The character encoding for the source file is ASCII
by default. To support UNICODE or source files prepared with
different encodings this can be changed to UTF-8
or
any other encoding. The default is encoding=ASCII
and
the option encoding=UTF-8
or other supported
encodings can be used.
Finally, HSQLDB provides the ability to read a text file as READ ONLY, by placing the keyword "DESC" at the end of the SET TABLE SOURCE statement:
SET TABLE mytable SOURCE "myfile" DESC
Text table source files are cached in memory. The maximum number
of rows of data that are in memory at any time is controlled by the
cache_rows
property. The default value for
cache_rows
is 1000 and can be changed by setting the
default database property .The cache_size
property
sets the maximum amount of memory used for each text table. The default
is 100 KB. The properties can be set for individual text tables. These
properties do not control the maximum size of each text table, which can
be much larger. An example is given below:
SET TABLE mytable SOURCE "myfile;ignore_first=true;all_quoted=true;cache_rows=10000;cache_size=1000"
The properties used in earlier versions, namely the
textdb.cache_scale
and the
textdb.cache_size_scale
can still be used for
backward compatibility, but the new properties are preferred.
Supported Properties
|
quoted = { true | false } |
default is true. If false, treats double quotes as normal characters |
|
all_quoted = { true | false } |
default is false. If true, adds double quotes around all fields. |
|
encoding = <encoding name> |
character encoding for text and character fields, for example, encoding=UTF-8. UTF-16 cannot be used. |
|
ignore_first = { true | false } |
default is false. If true ignores the first line of the file |
|
cache_rows= <numeric value> |
rows of the text file in the cache. Default is 1000 rows |
|
cache_size = <numeric value>r |
total size of the rows in the cache. Default is 100 KB. |
|
cache_scale= <numeric value> and cache_size_scale = <numeric value> |
deprecated properties, replaced by cached_rows and cache_size properties above. |
|
fs = <unquoted character> |
field separator |
|
vs = <unquoted character> |
varchar separator |
Text tables may be disconnected from their underlying data source, i.e. the text file.
You can explicitly disconnect a text table from its file by issuing the following statement:
SET TABLE mytable SOURCE OFF
Subsequently, mytable
will be empty and
read-only. However, the data source description will be preserved, and
the table can be re-connected to it with
SET TABLE mytable SOURCE ON
When a database is opened, if the source file for an existing text table is missing, the table remains disconnected from its data source but the source description is preserved. This allows the missing source file to be added to the directory and the table re-connected to it with the above command.
Disconnecting text tables from their source has several uses. While disconnected, the text source can be edited outside HSQLDB, provided data integrity is respected. When large text sources are used, and several constraints or indexes need to be created on the table, it is possible to disconnect the source during the creation of constraints and indexes and reduce the time it takes to perform the operation.
The following information applies to the usage of text tables.
Text File Issues
With file databases, text fiile locations are restricted to
below the directory that contains the database, unless the
textdb.allow_full_path
property is set true as a
Java system property. This feature is for security, otherwise an admin
database user may be able to open random files. The specified text
source path is interpreted differently according to this property. By
default, the path is interpreted as a relative path to the directory
path of database files, it therefore cannot contain the double dot
notation for parent directory. This path is then appended by the
engine to the directory path to form a full path.
When the property is true, and the path starts with the forward slash or back slash, or the path contains a semicolon, the path is not appended to the directory path and is used as it is to open the file. In this usage the path is absolute.
By default, all-in-memory databases cannot use text tables. To
enable this capability the textdb.allow_full_path
property must be set true
as a Java system
property. The text file path is used as submitted and interpreted as
an absolute path as described above, or a path relative to the Java
process execute path. These text tables are always read-only.
Databases store in jars or as files on the classpath and opened with the res: protocol can reference read-only text files. These files are opened as resources. The file path is an absolute path beginning with a forward slash.
Blank lines are allowed anywhere in the text file, and are ignored.
It is possible to define a primary key, identity column, unique, foreign key and check constraints for text tables.
When a table source file is used with the
ignore_first=true
option, the first, ignored line is
replaced with a blank line after a SHUTDOWN COMPACT, unless the SOURCE
HEADER statement has been used.
An existing table source file may include CHARACTER fields that do not begin with the quote character but contain instances of the quote character. These fields are read as literal strings. Alternatively, if any field begins with the quote character, then it is interpreted as a quoted string that should end with the quote character and any instances of the quote character within the string is doubled. When any field containing the quote character or the separator is written out to the source file by the program, the field is enclosed in quote character and any instance of the quote character inside the field is doubled.
Inserts or updates of CHARACTER type field values are allowed with strings that contains the linefeed or the carriage return character. This feature is disabled when both quoted and all_quoted properties are false.
ALTER TABLE commands that add or drop columns or constraints (apart from check constraints) are not supported with text tables that are connected to a source. First use the SET TABLE <name> SOURCE OFF, make the changes, then turn the source ON.
Use the default setting (quoted=true) for selective quoting of fields. Those fields that need quoting are quoted, other not.
Use the quoted=false setting to avoid quoting of fields completely. With this setting any quote character is considered part of the text.
Use the all_quoted=true setting to force all fields to be quoted.
SHUTDOWN COMPACT results in a complete rewrite of text table sources that are open at the time. The settings for quoted and all_quoted are applied for the rewrite.
The database engine uses a set of defaults for text table properties. Each table's data source may override these defaults. It is also possible to override the defaults globally, so they apply to all text tables. The statement SET DATABASE TEXT TABLE DEFAULTS <properties string> can be used to override the default global properties. An example is given below:
SET DATABASE TEXT TABLE DEFAULTS 'all_quoted=true;encoding=UTF-8;cache_rows=10000;cache_size=2000'
List of supported global properties
fs=,
vs=,
quoted=true
all_quoted=false
ignore_first=false
encoding=ASCII
cache_rows=1000
cache_size=100
textdb.allow_full_path=false (a system
property)
Text tables fully support transactions. New or changed rows that have not been committed are not updated in the source file. Therefore the source file always contains committed rows.
However, text tables are not as resilient to machine crashes as other types of tables. If the crash happens while the text source is being written to, the text source may contain only some of the changes made during a committed transaction. With other types of tables, additional mechanisms ensure the integrity of the data and this situation will not arise.
$Revision: 3096 $
Copyright 2010-2012 Fred Toussi. Permission is granted to distribute this document without any alteration under the terms of the HSQLDB license. Additional permission is granted to the HSQL Development Group to distribute this document with or without alterations under the terms of the HSQLDB license.
2013-07-08 15:31:27+0100
Table of Contents
This chapter is about access control to database objects such as tables, inside the database engine. Other issues related to security include user authentication, password complexity and secure connections are covered in the System Management chapter and the HyperSQL Network Listeners (Servers) chapter.
Apart from schemas and their object, each HyperSQL catalog has USER and ROLE objects. These objects are collectively called authorizations. Each AUTHORIZATION has some access rights on some of the schemas or the objects they contain. The persistent elements of an SQL environment are database objects
Authorizations names are stored in the database in the case-normal form. When connecting to a database via JDBC, the user name and password must match the case of this case-normal form.
When a user is created with the CREATE USER statement, if the user name is enclosed in double quotes, the exact name is used as the case-normal form. But if it is not enclosed in double quotes, the name is converted to uppercase and this uppercase version is stored in the database as the case-normal form.
In general, ROLE and USER objects simply control access to schema objects. This is the scope of the SQL Standard. However, there are special roles that allow the creation of USER and ROLE objects and also allow some special operations on the database as a whole. These roles are not defined by the Standard, which has left it to implementers to define such roles as they are needed for the particular SQL implementation.
A ROLE has a name a collection of zero or more other roles, plus some privileges (access rights). A USER has a name and a password. It similarly has a collection of zero or more roles plus some privileges.
USER objects existed in the SQL-92, but ROLE objects were introduced in SQL:1999. Originally it was intended that USER objects would normally be the same as the operating system USER objects and their authentication would be handled outside the SQL environment. The co-existence of ROLE and USER objects results in complexity. With the addition of ROLE objects, there is no rationale, other than legacy support, for granting privileges to USER objects directly. It is better to create roles and grant privileges to them, then grant the roles to USER objects.
The Standard effectively defines a special ROLE, named PUBLIC. All authorization have the PUBLIC role, which cannot be removed from them. Therefore any access right assigned to the PUBLIC role applies to all authorizations in the database. For many simple databases, it is adequate to create a single, non-admin user, then assign access rights to the pre-existing PUBLIC role. Access to INFORMATION_SCHEMA views is granted to PUBLIC, therefore these views are accessible to all. However, the contents of each view depends on the ROLE or USER (AUTHORIZATION) that is in force while accessing the view.
Each schema has a single AUTHORIZATION. This is commonly known as the owner of the schema. All the objects in the schema inherit the schema owner. The schema owner can add objects to the schema, drop them or alter them.
By default, the objects in a schema can only be accessed by the schema owner. The schema owner can grant access rights on the objects to other users or roles.
authorization identifier
authorization identifier
<authorization identifier> ::= <role name> |
<user name>
Authorization identifiers share the same name-space within the database. The same name cannot be used for a USER and a ROLE.
There are some pre-defined roles in each database; some defined by the SQL Standard, some by HyperSQL. These roles can be assigned to users (directly or via other, user-defined roles). In addition, there is the initial SYS user created with each new database. The initial user name and password is defined in the connection properties when the first connection to the database is made. In older versions of HSQLDB, this name was always SA. But in the latest version, the name can be defined as a different string.
PUBLIC
the PUBLIC role
The role that is assigned to all authorizations (roles and users) in the database. This role has access rights to all objects in the INFORMATION_SCHEMA. Any roles or rights granted to this role, are in effect granted to all users of the database.
_SYSTEM
the _SYSTEM role
This role is the authorization for the pre-defined (system) objects in the database, including the INFORMATION_SCHEMA. This role cannot be assigned to any authorization.
DBA
the DBA role (HyperSQL-specific)
This is a special role in HyperSQL. A user that has this role can perform all possible administrative tasks on the database. The DBA role can also act as a proxy for all the roles and users in the database. This means it can do everything the authorization for a schema can do, including dropping the schema or its objects, or granting rights on the schema objects to a grantee.
CREATE_SCHEMA
the CREATE_SCHEMA role (HyperSQL-specific)
An authorization that has this role, can create schemas. The DBA authorization has this role and can grant it to other authorizations.
CHANGE_AUTHORIZATION
the CHANGE_AUTHORIZATION role (HyperSQL-specific)
A user that has this role, can change the authorization for the current session to another user. The other user cannot have the DBA role (otherwise, the original user would gain DBA privileges). The DBA authorization has this role and can grant it to other authorizations.
SYS User
the SYS user (HyperSQL-specific)
This user is automatically created with a new database and has the DBA role. This user name and its password are defined in the connection properties when connecting to the new database to create the database. As this user, it is possible to change the password, create other users and created new schema objects. The initial SYS user can be dropped by another user that has the DBA role. As a result, there is always at least one SYS user in the database.
Tables in the INFORMATION_SCHEMA contain the list of users and roles for the database.
The SYSTEM_USERS tables contains the list of users, with some extra settings for each user. The AUTHORIZATIONS table contains a list of both users and roles.
Several other INFORMATION_SCHEMA tables list the privileges granted to users and roles on different database objects. Refer to the Schemas and Database Objects chapter for a list and description of the tables. Example below:
SELECT * FROM INFORMATION_SCHEMA.SYSTEM_USERS SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
By default, the objects in a schema can only be accessed by the schema owner. But the schema owner can grant privileges (access rights) on the objects to other users or roles.
Things can get far more complex, because the grant of privileges can be made WITH GRANT OPTION. In this case, the role or user that has been granted the privilege can grant the privilege to other roles and users.
Privileges can also be revoked from users or roles.
The statements for granting and revoking privileges normally
specify which privileges are granted or revoked. However, there is a
shortcut, ALL PRIVILEGES, which means all the privileges that the
<grantor>
has on the schema object. The
<grantor>
is normally the CURRENT_USER of the
session that issues the statement.
The user or role that is granted privileges is referred to as
<grantee>
for the granted privileges.
Table
For tables, including views, privileges can be granted with different degrees of granularity. It is possible to grant a privilege on all columns of a table, or on specific columns of the table.
The DELETE privilege applies to the table, rather than its columns. It applies to all DELETE statements.
The SELECT, INSERT and UPDATE privileges may apply to all
columns or to individual columns. These privileges determine whether the
<grantee>
can execute SQL data statements on
the table.
The SELECT privilege designates the columns that can be referenced in SELECT statements, as well as the columns that are read in a DELETE or UPDATE statement, including the search condition.
The INSERT privilege designates the columns into which explicit values can be inserted. To be able to insert a row into the table, the user must therefore have the INSERT privilege on the table, or at least all the columns that do not have a default value.
The UPDATE privilege simply designates the table or the specific columns that can be updated.
The REFERENCES privilege allows the
<grantee>
to define a FOREIGN KEY constraint on
a different table, which references the table or the specific columns
designated for the REFERENCES privilege.
The TRIGGER privilege allows adding a trigger to the table.
Sequence, Type, Domain, Character Set, Collation, Transliteration,
For these objects, only USAGE can be granted. The USAGE privilege is needed when object is referenced directly in an SQL statement.
Routine
For routines, including procedures or functions, only EXECUTE privilege can be granted. This privilege is needed when the routine is used directly in an SQL statement.
Other Objects
Other objects such as constraints and assertions are not used directly and there is no grantable privilege that refers to them.
The statements listed below allow creation and destruction of USER and ROLE objects. The GRANT and REVOKE statements allow roles to be assigned to other roles or to users. The same statements are also used in a different form to assign privileges on schema objects to users and roles.
user definition (HyperSQL)
<user definition> ::= CREATE USER <user
name> PASSWORD <password> [ ADMIN ]
Define a new user and its password. <user
name>
is an SQL identifier. If it is double-quoted it is
case-sensitive, otherwise it is turned to uppercase.
<password>
is a string enclosed with single quote
characters and is case-sensitive. If ADMIN
is
specified, the DBA role is granted to the new user. Only a user with the
DBA role can execute this statement.
DROP USER
drop user statement (HyperSQL)
<drop user statement> ::= DROP USER <user
name>
Drop (destroy) an existing user. If the specified user is the authorization for a schema, the schema is destroyed.
Only a user with the DBA role can execute this statement.
ALTER USER ... SET PASSWORD
set the password for a user (HyperSQL)
<alter user set password statement> ::= ALTER USER
<user name> SET PASSWORD <password>
Change the password of an existing user. <user
name>
is an SQL identifier. If it is double-quoted it is
case-sensitive, otherwise it is turned to uppercase.
<password>
is a string enclosed with single quote
characters and is case-sensitive.
Only a user with the DBA role can execute this command.
ALTER USER ... SET INITIAL SCHEMA
set the initial schema for a user (HyperSQL)
<alter user set initial schema statement> ::=
ALTER USER <user name> SET INITIAL SCHEMA <schema name> |
DEFAULT
Change the initial schema for a user. The initial schema is the
schema used by default for SQL statements issued during a session. If
DEFAULT
is used, the default initial schema for all
users is used as the initial schema for the user. The SET SCHEMA command
allows the user to change the schema for the duration of the
session.
Only a user with the DBA role can execute this statement.
ALTER USER ... SET LOCAL
set the user authentication as local (HyperSQL)
<alter user set local> ::= ALTER USER <user
name> SET LOCAL { TRUE | FALSE }
Sets the authentication method for the user as local. This statement has an effect only when external authentication with role names is enabled. In this method of authentication, users created in the database are ignored and an external authentication mechanism, such as LDAP is used. This statement is used if you want to use local, password authentication for a specific user.