|
2013-07-08 15:31:27+0100 |
|
Table of Contents
List of Tables
List of Examples
Table of Contents
HSQLDB (HyperSQL DataBase) is a modern relational database manager that conforms closely to the SQL:2008 Standard and JDBC 4 specifications. It supports all core features and many of the optional features of SQL:2008.
The first versions of HSQLDB were released in 2001. Version 2, first released in 2010, includes a complete rewrite of most parts of the database engine.
This documentation covers the latest HyperSQL version 2.3. This documentation is regularly improved and updated. The latest, updated version can be found at http://hsqldb.org/doc/2.0/
If you notice any mistakes in this document, or if you have problems with the procedures themselves, please use the HSQLDB support facilities which are listed at http://hsqldb.org/support
This document is available in several formats.
You may be reading this document right now at http://hsqldb.org/doc/2.0, or in a distribution somewhere else. I hereby call the document distribution from which you are reading this, your current distro.
http://hsqldb.org/doc/2.0 hosts the latest production versions of all available formats. If you want a different format of the same version of the document you are reading now, then you should try your current distro. If you want the latest production version, you should try http://hsqldb.org/doc/2.0.
Sometimes, distributions other than http://hsqldb.org/doc/2.0 do not host all available formats. So, if you can't access the format that you want in your current distro, you have no choice but to use the newest production version at http://hsqldb.org/doc/2.0.
Table 1. Available formats of this document
| format | your distro | at http://hsqldb.org/doc/2.0 |
|---|---|---|
| Chunked HTML | index.html | http://hsqldb.org/doc/2.0/guide/ |
| All-in-one HTML | guide.html | http://hsqldb.org/doc/2.0/guide/guide.html |
| guide.pdf | http://hsqldb.org/doc/2.0/guide/guide.pdf |
If you are reading this document now with a standalone PDF reader, the
your distro links may not work.
$Revision: 5240 $
Copyright 2002-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
HyperSQL Database (HSQLDB ) is a modern relational database system. Version 2.3 is the latest release of the all-new version 2 code. Written from ground up to follow the international ISO SQL:2011 standard, it supports the complete set of the classic features, together with optional features such as stored procedures and triggers.
HyperSQL is used for development, testing and deployment of database applications.
Standard compliance is the most unique characteristic of HyperSQL. There are several other distinctive features. HyperSQL can provide database access within the user's application process, within an application server, or as a separate server process. HyperSQL can run entirely in memory using dedicated fast memory structures as opposed to ram disk. HyperSQL can use disk persistence in a flexible way, with reliable crash-recovery. HyperSQL is the only open-source relational database management system with a high performance dedicated lob storage system, suitable for gigabytes of lob data. It is also the only relational database that can create and access large comma delimited files as SQL tables. HyperSQL supports three live switchable transaction control models, including fully multi threaded MVCC, and is suitable for high performance transaction processing applications. HyperSQL is also suitable for business intelligence, ETL and other applications that process large data sets. HyperSQL has a wide range of enterprise deployment options, such as XA transactions, connection pooling data sources and remote authentication.
New SQL syntax compatibility modes have been added to HyperSQL. These modes allow a high degree of compatibility with several other database systems which use non-standard SQL syntax.
HyperSQL is written in the Java programming language and runs in a Java virtual machine (JVM). It supports the JDBC interface for database access.
An ODBC driver is also available as a separate download.
This guide covers the database engine features, SQL syntax and different modes of operation. The Server, JDBC interfaces, pooling and XA components are documented in the JavaDoc. Utilities such as SqlTool and DatabaseManager are covered in a separate Utilities Guide.
The HSQLDB jar package, hsqldb.jar, is located in the /lib directory of the ZIP package and contains several components and programs.
Components of the Hsqldb jar package
HyperSQL RDBMS Engine (HSQLDB)
HyperSQL JDBC Driver
Database Manager (GUI database access tool, with Swing and AWT versions)
The HyperSQL RDBMS and JDBC Driver provide the core functionality. DatabaseManagers are general-purpose database access tools that can be used with any database engine that has a JDBC driver.
An additional jar, sqltool.jar, contains Sql Tool, command line database access tool. This is a general purpose command line database access tool that can be ued with other database engines as well.
The tools are used for interactive user access to databases,
including creation of a database, inserting or modifying data, or querying
the database. All tools are run in the normal way for Java programs. In
the following example the Swing version of the Database Manager is
executed. The hsqldb.jar
is located in the directory
../lib
relative to the current directory.
java -cp ../lib/hsqldb.jar org.hsqldb.util.DatabaseManagerSwing
If hsqldb.jar
is in the current directory, the
command would change to:
java -cp hsqldb.jar org.hsqldb.util.DatabaseManagerSwing
Main classes for the Hsqldb tools
org.hsqldb.util.DatabaseManager
org.hsqldb.util.DatabaseManagerSwing
When a tool is up and running, you can connect to a database (may be a new database) and use SQL commands to access and modify the data.
Tools can use command line arguments. You can add the command line argument --help to get a list of available arguments for these tools.
Double clicking the HSQLDB jar will start the DatabaseManagerSwing application.
Each HyperSQL database is called a catalog. There are three types of catalog depending on how the data is stored.
Types of catalog data
mem: stored entirely in RAM - without any persistence beyond the JVM process's life
file: stored in filesystem files
res: stored in a Java resource, such as a Jar and always read-only
All-in-memory, mem: catalogs can be used for test data or as sophisticated caches for an application. These databases do not have any files.
A file: catalog consists of between 2 to 6 files, all named the same but with different extensions, located in the same directory. For example, the database named "test" consists of the following files:
test.properties
test.script
test.log
test.data
test.backup
test.lobs
The properties file contains a few settings about the database. The
script file contains the definition of tables and other database objects,
plus the data for non-cached tables. The log file contains recent changes
to the database. The data file contains the data for cached tables and the
backup file is a compressed backup of the last known consistent state of
the data file. All these files are essential and should never be deleted.
For some catalogs, the test.data
and
test.backup
files will not be present. In addition to
those files, a HyperSQL database may link to any formatted text files,
such as CSV lists, anywhere on the disk.
While the "test" catalog is open, a test.log
file is used to write the changes made to data. This file is removed at a
normal SHUTDOWN. Otherwise (with abnormal shutdown) this file is used at
the next startup to redo the changes. A test.lck
file
is also used to record the fact that the database is open. This is deleted
at a normal SHUTDOWN.
| Note | |
|---|---|
|
When the engine closes the database at a shutdown, it creates
temporary files with the extension |
A res: catalog consists of the files for a small, read-only database that can be stored inside a Java resource such as a ZIP or JAR archive and distributed as part of a Java application program.
In general, JDBC is used for all access to databases. This is done
by making a connection to the database, then using various methods of the
java.sql.Connection
object that is returned to
access the data. Access to an in-process database
is started from JDBC, with the database path specified in the connection
URL. For example, if the file: database name is
"testdb" and its files are located in the same directory as where the
command to run your application was issued, the following code is used for
the connection:
Connection c = DriverManager.getConnection("jdbc:hsqldb:file:testdb", "SA", "");
The database file path format can be specified using forward slashes
in Windows hosts as well as Linux hosts. So relative paths or paths that
refer to the same directory on the same drive can be identical. For
example if your database directory in Linux is /opt/db/
containing a database testdb (with files named testdb.*), then the
database file path is /opt/db/testdb.
If you create an
identical directory structure on the C:
drive of a
Windows host, you can use the same URL in both Windows and Linux:
Connection c = DriverManager.getConnection("jdbc:hsqldb:file:/opt/db/testdb", "SA", "");
When using relative paths, these paths will be taken relative to the
directory in which the shell command to start the Java Virtual Machine was
executed. Refer to the Javadoc for JDBCConnection
for more
details.
Paths and database names for file databases are treated as case-sensitive when the database is created or the first connection is made to the database. But if a second connection is made to an open database, using a path and name that differs only in case, then the connection is made to the existing open database. This measure is necessary because in Windows the two paths are equivalent.
A mem: database is specified by the mem: protocol. For mem: databases, the path is simply a name. Several mem: databases can exist at the same time and distinguished by their names. In the example below, the database is called "mymemdb":
Connection c = DriverManager.getConnection("jdbc:hsqldb:mem:mymemdb", "SA", "");
A res: database, is specified by the res: protocol. As it is a Java resource, the database path is a Java URL (similar to the path to a class). In the example below, "resdb" is the root name of the database files, which exists in the directory "org/my/path" within the classpath (probably in a Jar). A Java resource is stored in a compressed format and is decompressed in memory when it is used. For this reason, a res: database should not contain large amounts of data and is always read-only.
Connection c = DriverManager.getConnection("jdbc:hsqldb:res:org.my.path.resdb", "SA", "");
The first time in-process connection is made to a database, some general data structures are initialised and a few helper threads are started. After this, creation of connections and calls to JDBC methods of the connections execute as if they are part of the Java application that is making the calls. When the SQL command "SHUTDOWN" is executed, the global structures and helper threads for the database are destroyed.
Note that only one Java process at a time can make in-process connections to a given file: database. However, if the file: database has been made read-only, or if connections are made to a res: database, then it is possible to make in-process connections from multiple Java processes.
For most applications, in-process access is faster, as the data is not converted and sent over the network. The main drawback is that it is not possible by default to connect to the database from outside your application. As a result you cannot check the contents of the database with external tools such as Database Manager while your application is running.
Server modes provide the maximum accessibility. The database engine runs in a JVM and opens one or more in-process catalogs. It listens for connections from programs on the same computer or other computers on the network. It translates these connections into in-process connections to the databases.
Several different programs can connect to the server and retrieve or update information. Applications programs (clients) connect to the server using the HyperSQL JDBC driver. In most server modes, the server can serve an unlimited number of databases that are specified at the time of running the server, or optionally, as a connection request is received.
A Sever mode is also the preferred mode of running the database during development. It allows you to query the database from a separate database access utility while your application is running.
There are three server modes, based on the protocol used for communications between the client and server. They are briefly discussed below. More details on servers is provided in the HyperSQL Network Listeners (Servers) chapter.
This is the preferred way of running a database server and the fastest one. A proprietary communications protocol is used for this mode. A command similar to those used for running tools and described above is used for running the server. The following example of the command for starting the server starts the server with one (default) database with files named "mydb.*" and the public name of "xdb". The public name hides the file names from users.
java -cp ../lib/hsqldb.jar org.hsqldb.server.Server --database.0 file:mydb --dbname.0 xdb
The command line argument --help
can be used to
get a list of available arguments.
This method of access is used when the computer hosting the database server is restricted to the HTTP protocol. The only reason for using this method of access is restrictions imposed by firewalls on the client or server machines and it should not be used where there are no such restrictions. The HyperSQL HTTP Server is a special web server that allows JDBC clients to connect via HTTP. The server can also act as a small general-purpose web server for static pages.
To run an HTTP server, replace the main class for the server in the example command line above with the following:
org.hsqldb.server.WebServer
The command line argument --help
can be used to
get a list of available arguments.
This method of access also uses the HTTP protocol. It is used when
a separate servlet engine (or application server) such as Tomcat or
Resin provides access to the database. The Servlet Mode cannot be
started independently from the servlet engine. The
Servlet
class, in the HSQLDB jar, should be
installed on the application server to provide the connection. The
database file path is specified using an application server property.
Refer to the source file
src/org/hsqldb/server/Servlet.java
to see the details.
Both HTTP Server and Servlet modes can be accessed using the JDBC driver at the client end. They do not provide a web front end to the database. The Servlet mode can serve multiple databases.
Please note that you do not normally use this mode if you are using the database engine in an application server. In this situation, connections to a catalog are usually made in-process, or using a separate Server
When a HyperSQL server is running, client programs can connect to
it using the HSQLDB JDBC Driver contained in
hsqldb.jar
. Full information on how to connect to a
server is provided in the Java Documentation for JDBCConnection
(located in the /doc/apidocs
directory of HSQLDB
distribution). A common example is connection to the default port (9001)
used for the hsql: protocol on the same
machine:
Example 1.1. Java code to connect to the local hsql Server
try { Class.forName("org.hsqldb.jdbc.JDBCDriver" ); } catch (Exception e) { System.err.println("ERROR: failed to load HSQLDB JDBC driver."); e.printStackTrace(); return; } Connection c = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/xdb", "SA", "");
If the HyperSQL HTTP server is used, the protocol is http: and the URL will be different:
Example 1.2. Java code to connect to the local http Server
Connection c = DriverManager.getConnection("jdbc:hsqldb:http://localhost/xdb", "SA", "");
Note in the above connection URL, there is no mention of the database file, as this was specified when running the server. Instead, the public name defined for dbname.0 is used. Also, see the HyperSQL Network Listeners (Servers) chapter for the connection URL when there is more than one database per server instance.
When a HyperSQL server is run, network access should be adequately protected. Source IP addresses may be restricted by use of our Access Control List feature, network filtering software, firewall software, or standalone firewalls. Only secure passwords should be used-- most importantly, the password for the default system user should be changed from the default empty string. If you are purposefully providing data to the public, then the wide-open public network connection should be used exclusively to access the public data via read-only accounts. (i.e., neither secure data nor privileged accounts should use this connection). These considerations also apply to HyperSQL servers run with the HTTP protocol.
HyperSQL provides two optional security mechanisms. The encrypted SSL protocol, and Access Control Lists. Both mechanisms can be specified when running the Server or WebServer. On the client, the URL to connect to an SSL server is slightly different:
Example 1.3. Java code to connect to the local secure SSL hsql and http Servers
Connection c = DriverManager.getConnection("jdbc:hsqldb:hsqls://localhost/xdb", "SA", ""); Connection c = DriverManager.getConnection("jdbc:hsqldb:https://localhost/xdb", "SA", "");
The security features are discussed in detail in the HyperSQL Network Listeners
(Servers)
chapter.
A server can provide connections to more than one database. In the
examples above, more than one set of database names can be specified on
the command line. It is also possible to specify all the databases in a
.properties
file, instead of the command line. These
capabilities are covered in the HyperSQL Network Listeners
(Servers) chapter
As shown so far, a java.sql.Connection
object
is always used to access the database. But the speed and performance
depends on the type of connection.
Establishing a connection and closing it has some overheads, therefore it is not good practice to create a new connection to perform a small number of operations. A connection should be reused as much as possible and closed only when it is not going to be used again for a long while.
Reuse is more important for server connections. A server connection uses a TCP port for communications. Each time a connection is made, a port is allocated by the operating system and deallocated after the connection is closed. If many connections are made from a single client, the operating system may not be able to keep up and may refuse the connection attempt.
A java.sql.Connection
object has some methods
that return further java.sql.*
objects. All these
objects belong to the connection that returned them and are closed when
the connection is closed. These objects can be reused, but if they are not
needed after performing the operations, they should be closed.
A java.sql.DatabaseMetaData
object is used to
get metadata for the database.
A java.sql.Statement
object is used to
execute queries and data change statements. A
java.sql.Statement
can be reused to execute a
different statement each time.
A java.sql.PreparedStatement
object is used
to execute a single statement repeatedly. The SQL statement usually
contains parameters, which can be set to new values before each reuse.
When a java.sql.PreparedStatement
object is
created, the engine keeps the compiled SQL statement for reuse, until the
java.sql.PreparedStatement
object is closed. As a
result, repeated use of a
java.sql.PreparedStatement
is much faster than
using a java.sql.Statement
object.
A java.sql.CallableStatement
object is used
to execute an SQL CALL statement. The SQL CALL statement may contain
parameters, which should be set to new values before each reuse. Similar
to java.sql.PreparedStatement
, the engine keeps the
compiled SQL statement for reuse, until the
java.sql.CallableStatement
object is closed.
A java.sql.Connection
object also has some
methods for transaction control.
The commit()
method performs a
COMMIT
while the rollback()
method performs a ROLLBACK
SQL statement.
The setSavepoint(String name)
method
performs a SAVEPOINT <name>
SQL statement and
returns a java.sql.Savepoint
object. The
rollback(Savepoint name)
method performs a
ROLLBACK TO SAVEPOINT <name>
SQL
statement.
The Javadoc for
JDBCConnection
,
JDBCDriver
,
JDBCDatabaseMetadata
JDBCResultSet
,
JDBCStatement
,
JDBCPreparedStatement
list all the supported JDBC methods
together with information that is specific to HSQLDB.
All databases running in different modes can be closed with the SHUTDOWN command, issued as an SQL statement.
When SHUTDOWN is issued, all active transactions are rolled back. The catalog files are then saved in a form that can be opened quickly the next time the catalog is opened.
A special form of closing the database is via the SHUTDOWN COMPACT
command. This command rewrites the .data
file that
contains the information stored in CACHED tables and compacts it to its
minimum size. This command should be issued periodically, especially when
lots of inserts, updates or deletes have been performed on the cached
tables. Changes to the structure of the database, such as dropping or
modifying populated CACHED tables or indexes also create large amounts of
unused file space that can be reclaimed using this command.
Databases are not closed when the last connection to the database is
explicitly closed via JDBC. A connection property,
shutdown=true
, can be specified on the first connection
to the database (the connection that opens the database) to force a
shutdown when the last connection closes.
Example 1.4. specifying a connection property to shutdown the database when the last connection is closed
Connection c = DriverManager.getConnection( "jdbc:hsqldb:file:/opt/db/testdb;shutdown=true", "SA", "");
This feature is useful for running tests, where it may not be
practical to shutdown the database after each test. But it is not
recommended for application programs.
When a server instance is started, or when a connection is made to an in-process database, a new, empty database is created if no database exists at the given path.
With HyperSQL 2.0 the username and password that are specified for the connection are used for the new database. Both the username and password are case-sensitive. (The exception is the default SA user, which is not case-sensitive). If no username or password is specified, the default SA user and an empty password are used.
This feature has a side effect that can confuse new users. If a
mistake is made in specifying the path for connecting to an existing
database, a connection is nevertheless established to a new database. For
troubleshooting purposes, you can specify a connection property
ifexists=true
to allow connection
to an existing database only and avoid creating a new database. In this
case, if the database does not exist, the
getConnection()
method will throw an
exception.
Example 1.5. specifying a connection property to disallow creating a new database
Connection c = DriverManager.getConnection( "jdbc:hsqldb:file:/opt/db/testdb;ifexists=true", "SA", "");
A database has many optional properties, described in the System Management chapter. You can specify most of these properties on the URL or in the connection properties for the first connection that creates the database. See the Properties chapter.
$Revision: 5240 $
Copyright 2002-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
HyperSQL 2 supports the dialect of SQL defined by SQL standards 92, 1999, 2003, 2008 and 2011. This means where a feature of the standard is supported, e.g. left outer join, the syntax is that specified by the standard text. Almost all syntactic features of SQL-92 up to Advanced Level are supported, as well as SQL:2011 core and many optional features of this standard. Work is in progress for a formal declaration of conformance.
At the time of this release, HyperSQL supports the widest range of SQL standard features among all open source RDBMS.
Various chapters of this guide list the supported syntax. When writing or converting existing SQL DDL (Data Definition Language), DML (Data Manipulation Language) or DQL (Data Query Language) statements for HSQLDB, you should consult the supported syntax and modify the statements accordingly. Some statements written for older versions may have to be modified.
Over 300 words are reserved by the standard and should not be used
as table or column names. For example, the word POSITION is reserved as it
is a function defined by the Standards with a similar role as
String.indexOf()
in Java. HyperSQL does not
currently prevent you from using a reserved word if it does not support
its use or can distinguish it. For example CUBE is a reserved words that
is not currently supported by HyperSQL and is allowed as a table or column
name. You should avoid using such names as future versions of HyperSQL are
likely to support the reserved words and may reject your table definitions
or queries. The full list of SQL reserved words is in the appendix Lists of Keywords .
There are several user-defined properties to control the strict application of the SQL Standard in different areas.
If you have to use a reserved keyword as the name of a database object, you can enclose it in double quotes.
HyperSQL also supports enhancements with keywords and expressions
that are not part of the SQL standard. Expressions such as SELECT
TOP 5 FROM ..
, SELECT LIMIT 0 10 FROM ...
or
DROP TABLE mytable IF EXISTS
are among such
constructs.
Many print books cover SQL Standard syntax and can be consulted.
In HyperSQL version 2, all features of JDBC4 that apply to the
capabilities of HSQLDB are fully supported. The relevant JDBC classes are
thoroughly documented with additional clarifications and HyperSQL specific
comments. See the JavaDoc for the
org.hsqldb.jdbc.*
classes.
In an SQL system, all significant data is stored in tables and sequence generators. Therefore, the first step in creating a database is defining the tables and their columns. The SQL standard supports temporary tables, which are for temporary data, and permanent base tables, which are for persistent data.
Data in TEMPORARY tables is not saved and lasts only for the lifetime of the session. The contents of each TEMP table is visible only from the session that is used to populate it.
HyperSQL supports two types of temporary tables.
The GLOBAL TEMPORARY
type is a schema object.
It is created with the CREATE GLOBAL TEMPORARY TABLE
statement. The definition of the table persists, and each session has
access to the table. But each session sees its own copy of the table,
which is empty at the beginning of the session.
The LOCAL TEMPORARY
type is not a schema
object. It is created with the DECLARE LOCAL TEMPORARY
TABLE
statement. The table definition lasts only for the
duration of the session and is not persisted in the database. The table
can be declared in the middle of a transaction without committing the
transaction. If a schema name is needed to reference these tables in a
given SQL statement, the pseudo schema names MODULE
or SESSION
can be used.
When the session commits, the contents of all temporary tables are cleared by default. If the table definition statements includes ON COMMIT PRESERVE ROWS, then the contents are kept when a commit takes place.
The rows in temporary tables are stored in memory by default. If
the hsqldb.result_max_memory_rows
property has been
set or the SET SESSION RESULT MEMORY ROWS <row
count>
has been specified, tables with row count above the
setting are stored on disk.
HyperSQL supports the Standard definition of persistent base table, but defines three types according to the way the data is stored. These are MEMORY tables, CACHED tables and TEXT tables.
Memory tables are the default type when the CREATE TABLE command
is used. Their data is held entirely in memory but any change to their
structure or contents is written to the *.log
and
*.script
files. The *.script
file and the *.log
file are read the next time the
database is opened, and the MEMORY tables are recreated with all their
contents. So unlike TEMPORARY tables, MEMORY tables are persistent. When
the database is opened, all the data for the memory tables is read and
inserted. This process may take a long time if the database is larger
than tens of megabytes. When the database is shutdown, all the data is
saved. This can also take a long time.
CACHED tables are created with the CREATE CACHED TABLE command. Only part of their data or indexes is held in memory, allowing large tables that would otherwise take up to several hundred megabytes of memory. Another advantage of cached tables is that the database engine takes less time to start up when a cached table is used for large amounts of data. The disadvantage of cached tables is a reduction in speed. Do not use cached tables if your data set is relatively small. In an application with some small tables and some large ones, it is better to use the default, MEMORY mode for the small tables.
TEXT tables use a CSV (Comma Separated Value) or other delimited text file as the source of their data. You can specify an existing CSV file, such as a dump from another database or program, as the source of a TEXT table. Alternatively, you can specify an empty file to be filled with data by the database engine. TEXT tables are efficient in memory usage as they cache only part of the text data and all of the indexes. The Text table data source can always be reassigned to a different file if necessary. The commands are needed to set up a TEXT table as detailed in the Text Tables chapter.
With all-in-memory databases, both MEMORY table and CACHED table declarations are treated as declarations for non-persistent memory tables. In the latest versions of HyperSQL, TEXT table declarations are allowed in all-in-memory databases.
The default type of tables resulting from future CREATE TABLE statements can be specified with the SQL command:
SET DATABASE DEFAULT TABLE TYPE { CACHED | MEMORY };
The type of an existing table can be changed with the SQL command:
SET TABLE <table name> TYPE { CACHED | MEMORY };
SQL statements access different types of tables uniformly. No change to statements is needed to access different types of table.
Most other RDBMS do not conform to the SQL Standard in all areas, but they are gradually moving towards Standard conformance. When switching from another SQL dialect, the following should be considered:
Numeric types TINYINT, SMALLINT, INTEGER and BIGINT are types with fixed binary precision. These types are more efficient to store and retrieve. NUMERIC and DECIMAL are types with user-defined decimal precision. They can be used with zero scale to store very large integers, or with a non-zero scale to store decimal fractions. The DOUBLE type is a 64 bit, approximate floating point types. HyperSQL even allows you to store infinity in this type.
The BOOLEAN type is for logical values and can hold TRUE, FALSE or UNKNOWN. Although HyperSQL allows you to use one and zero in assignment or comparison, you should use the standard values for this type.
Character string types are CHAR(L), VARCHAR(L) and CLOB. CHAR is
for fixed width strings and any string that is assigned to this type
is padded with spaces at the end. Do not use this type for general
storage of strings. If you use CHAR without the length L, then it is
interpreted as a single character string. Use VARCHAR(L) for general
strings. There are only memory limits and performance implications for
the maximum length of VARCHAR(L). If the strings are larger than a few
kilobytes, consider using CLOB. The CLOB types is for very large
strings. Do not use this type for short strings as there are
performance implications. The CLOB type is a better choice for the
storage of long strings. By default LONGVARCHAR is a synonym for a
long VARCHAR and can be used without specifying the size. You can set
LONGVARCHAR to map to CLOB, with the
sql.longvar_is_lob
connection property or the SET
DATABASE SQL LONGVAR IS LOB TRUE statement.
Binary string types are BINARY(L), VARBINARY(L) and BLOB. Do not
use BINARY(L) unless you are storing keys such as UUID. This type pads
short binary strings with zero bytes. BINARY without the length L
means a single byte. Use VARBINARY(L) for general binary strings, and
BLOB for large binary objects. You should apply the same
considerations as with the character string types. By default
LONGVARBINARY is a synonym for a long VARCHAR and can be used without
specifying the size. You can set LONGVARBINARY to map to BLOB, with
the sql.longvar_is_lob
connection property or the
SET DATABASE SQL LONGVAR IS LOB TRUE statement.
The BIT(L) and BITVARYING(L) types are for bit maps. Do not use them for other types of data. BIT without the length L argument means a single bit and is sometimes used as a logical type. Use BOOLEAN instead of this type.
The datetime types DATE, TIME and TIMESTAMP, together with their WITH TIME ZONE variations are available. Read the details in this chapter on how to use these types.
The INTERVAL type is very powerful when used together with the
datetime types. This is very easy to use, but is supported mainly by
"big iron" database systems. Note that functions that add days or
months to datetime values are not really a substitute for the INTERVAL
type. Expressions such as (datecol - 7 DAY) >
CURRENT_DATE
are optimised to use indexes when it is
possible, while the equivalent function calls are not
optimised.
The OTHER type is for storage of Java objects. If your objects are large, serialize them in your application and store them as BLOB in the database.
The ARRAY type supports all base types except LOB and OTHER types. ARRAY data objects are held in memory while being processed. It is therefore not recommended to store more than about a thousand objects in an ARRAY in normal operations with disk based databases. For specialised applications, use ARRAY with as many elements as your memory allocation can support.
HyperSQL 2.3 has several compatibility modes which allow the type names that are used by other RDBMS to be accepted and translated into the closest SQL Standard type. For example the type TEXT, supported by MySQL and PostgreSQL is translated in these compatibility modes.
HyperSQL supports all the types defined by SQL-92, plus BOOLEAN, BINARY and LOB types that were later added to the SQL Standard. It also supports the non-standard OTHER type to store serializable Java objects.
SQL is a strongly typed language. All data stored in specific columns of tables and other objects (such as sequence generators) have specific types. Each data item conforms to the type limits such as precision and scale for the column. It also conforms to any additional integrity constraints that are defined as CHECK constraints in domains or tables. Types can be explicitly converted using the CAST expression, but in most expressions they are converted automatically.
Data is returned to the user (or the application program) as a result of executing SQL statements such as query expressions or function calls. All statements are compiled prior to execution and the return type of the data is known after compilation and before execution. Therefore, once a statement is prepared, the data type of each column of the returned result is known, including any precision or scale property. The type does not change when the same query that returned one row, returns many rows as a result of adding more data to the tables.
Some SQL functions used within SQL statements are polymorphic, but the exact type of the argument and the return value is determined at compile time.
When a statement is prepared, using a JDBC PreparedStatement object, it is compiled by the engine and the type of the columns of its ResultSet and / or its parameters are accessible through the methods of PreparedStatement.
TINYINT, SMALLINT, INTEGER, BIGINT, NUMERIC and DECIMAL (without a
decimal point) are the supported integral types. They correspond
respectively to byte
,
short
, int
,
long
, BigDecimal
and
BigDecimal
Java types in the range of values that
they can represent (NUMERIC and DECIMAL are equivalent). The type
TINYINT is an HSQLDB extension to the SQL Standard, while the others
conform to the Standard definition. The SQL type dictates the maximum
and minimum values that can be held in a field of each type. For example
the value range for TINYINT is -128 to +127. The bit precision of
TINYINT, SMALLINT, INTEGER and BIGINT is respectively 8, 16, 32 and 64.
For NUMERIC and DECIMAL, decimal precision is used.
DECIMAL and NUMERIC with decimal fractions are mapped to
java.math.BigDecimal
and can have very large
numbers of digits. In HyperSQL the two types are equivalent. These
types, together with integral types, are called exact numeric
types.
In HyperSQL, REAL, FLOAT, DOUBLE are equivalent and all mapped to
double
in Java. These types are defined by the
SQL Standard as approximate numeric types. The bit-precision of all
these types is 64 bits.
The decimal precision and scale of NUMERIC and DECIMAL types can be optionally defined. For example, DECIMAL(10,2) means maximum total number of digits is 10 and there are always 2 digits after the decimal point, while DECIMAL(10) means 10 digits without a decimal point. The bit-precision of FLOAT can be defined but it is ignored and the default bit-precision of 64 is used. The default precision of NUMERIC and DECIMAL (when not defined) is 100.
Note: If a database has been set to ignore type precision limits with the SET DATABASE SQL SIZE FALSE command, then a type definition of DECIMAL with no precision and scale is treated as DECIMAL(100,10). In normal operation, it is treated as DECIMAL(100).
Integral Types
In expressions, TINYINT, SMALLINT, INTEGER, BIGINT, NUMERIC and DECIMAL (without a decimal point) can be freely combined and no data narrowing takes place. The resulting value is of a type that can support all possible values.
If the SELECT statement refers to a simple column or function, then the return type is the type corresponding to the column or the return type of the function. For example:
CREATE TABLE t(a INTEGER, b BIGINT); SELECT MAX(a), MAX(b) FROM t;
will return a ResultSet
where the type of
the first column is java.lang.Integer
and the
second column is java.lang.Long
. However,
SELECT MAX(a) + 1, MAX(b) + 1 FROM t;
will return java.lang.Long
and
BigDecimal
values, generated as a result of
uniform type promotion for all the return values. Note that type
promotion to BigDecimal
ensures the correct value
is returned if MAX(b)
evaluates to
Long.MAX_VALUE
.
There is no built-in limit on the size of intermediate integral
values in expressions. As a result, you should check for the type of the
ResultSet
column and choose an appropriate
getXXXX()
method to retrieve it. Alternatively,
you can use the getObject()
method, then cast
the result to java.lang.Number
and use the
intValue()
or
longValue()
methods on the result.
When the result of an expression is stored in a column of a
database table, it has to fit in the target column, otherwise an error
is returned. For example when<PHONE_NUMBER>123456789012 /
12345687901234567890
is evaluated, the result can be stored in
any integral type column, even a TINYINT column, as it is a small
value.
In SQL Statements, an integer literal is treated as INTEGER, unless its value does not fit. In this case it is treated as BIGINT or DECIMAL, depending on the value.
Depending on the types of the operands, the result of the
operation is returned in a JDBC ResultSet
in any
of the related Java types: Integer
,
Long
or BigDecimal
. The
ResultSet.getXXXX()
methods can be used to
retrieve the values so long as the returned value can be represented by
the resulting type. This type is deterministically based on the query,
not on the actual rows returned.
Other Numeric Types
In SQL statements, number literals with a decimal point are
treated as DECIMAL unless they are written with an exponent. Thus
0.2
is considered a DECIMAL value but
0.2E0
is considered a DOUBLE value.
When an approximate numeric type, REAL, FLOAT or DOUBLE (all
synonymous) is part of an expression involving different numeric types,
the type of the result is DOUBLE. DECIMAL values can be converted to
DOUBLE unless they are beyond the Double.MIN_VALUE -
Double.MAX_VALUE
range. For example, A * B, A / B, A + B, etc.
will return a DOUBLE value if either A or B is a DOUBLE.
Otherwise, when no DOUBLE value exists, if a DECIMAL or NUMERIC value is part an expression, the type of the result is DECIMAL or NUMERIC. Similar to integral values, when the result of an expression is assigned to a table column, the value has to fit in the target column, otherwise an error is returned. This means a small, 4 digit value of DECIMAL type can be assigned to a column of SMALLINT or INTEGER, but a value with 15 digits cannot.
When a DECIMAL values is multiplied by a DECIMAL or integral type, the resulting scale is the sum of the scales of the two terms. When they are divided, the result is a value with a scale (number of digits to the right of the decimal point) equal to the larger of the scales of the two terms. The precision for both operations is calculated (usually increased) to allow all possible results.
The distinction between DOUBLE and DECIMAL is important when a
division takes place. For example, 10.0/8.0
(DECIMAL)
equals 1.2
but 10.0E0/8.0E0
(DOUBLE) equals 1.25
. Without division operations,
DECIMAL values represent exact arithmetic.
REAL, FLOAT and DOUBLE values are all stored in the database as
java.lang.Double
objects. Special values such as
NaN and +-Infinity are also stored and supported. These values can be
submitted to the database via JDBC
PreparedStatement
methods and are returned in
ResultSet
objects. In order to allow division by
zero of DOUBLE values in SQL statements (which returns NaN or
+-Infinity) you should set the property hsqldb.double_nan as false (SET
DATABASE SQL DOUBLE NAN FALSE). The double values can be retrieved from
a ResultSet
in the required type so long as they
can be represented. For setting the values, when
PreparedStatement.setDouble()
or
setFloat()
is used, the value is treated as a
DOUBLE automatically.
In short,
<numeric type> ::= <exact numeric type> |
<approximate numeric type>
<exact numeric type> ::= NUMERIC [ <left
paren> <precision> [ <comma> <scale> ] <right
paren> ] | { DECIMAL | DEC } [ <left paren> <precision> [
<comma> <scale> ] <right paren> ] | SMALLINT | INTEGER
| INT | BIGINT
<approximate numeric type> ::= FLOAT [ <left
paren> <precision> <right paren> ] | REAL | DOUBLE
PRECISION
<precision> ::= <unsigned
integer>
<scale> ::= <unsigned
integer>
The BOOLEAN type conforms to the SQL Standard and represents the
values TRUE
, FALSE
and
UNKNOWN
. This type of column can be initialised with
Java boolean values, or with NULL
for the
UNKNOWN
value.
The three-value logic is sometimes misunderstood. For example, x IN (1, 2, NULL) does not return true if x is NULL.
In previous versions of HyperSQL, BIT was simply an alias for BOOLEAN. In version 2, BIT is a single-bit bit map.
<boolean type> ::= BOOLEAN
The SQL Standard does not support type conversion to BOOLEAN apart from character strings that consists of boolean literals. Because the BOOLEAN type is relatively new to the Standard, several database products used other types to represent boolean values. For improved compatibility, HyperSQL allows some type conversions to boolean.
Values of BIT and BIT VARYING types with length 1 can be converted to BOOLEAN. If the bit is set, the result of conversion is the TRUE value, otherwise it is FALSE.
Values of TINYINT, SMALLINT, INTEGER and BIGINT types can be converted to BOOLEAN. If the value is zero, the result is the FALSE value, otherwise it is TRUE.
The CHARACTER, CHARACTER VARYING and CLOB types are the SQL
Standard character string types. CHAR, VARCHAR and CHARACTER LARGE
OBJECT are synonyms for these types. HyperSQL also supports LONGVARCHAR
as a synonym for VARCHAR. If LONGVARCHAR is used without a length, then
a length of 16M is assigned. You can set LONGVARCHAR to map to CLOB,
with the sql.longvar_is_lob
connection property or
the SET DATABASE SQL LONGVAR IS LOB TRUE statement..
HyperSQL's default character set is Unicode, therefore all possible character strings can be represented by these types.
The SQL Standard behaviour of the CHARACTER type is a remnant of legacy systems in which character strings are padded with spaces to fill a fixed width. These spaces are sometimes significant while in other cases they are silently discarded. It would be best to avoid the CHARACTER type altogether. With the rest of the types, the strings are not padded when assigned to columns or variables of the given type. The trailing spaces are still considered discardable for all character types. Therefore if a string with trailing spaces is too long to assign to a column or variable of a given length, the spaces beyond the type length are discarded and the assignment succeeds (provided all the characters beyond the type length are spaces).
The VARCHAR and CLOB types have length limits, but the strings are not padded by the system. Note that if you use a large length for a VARCHAR or CLOB type, no extra space is used in the database. The space used for each stored item is proportional to its actual length.
If CHARACTER is used without specifying the length, the length
defaults to 1. For the CLOB type, the length limit can be defined in
units of kilobyte (K, 1024), megabyte (M, 1024 * 1024) or gigabyte (G,
1024 * 1024 * 1024), using the <multiplier>
. If
CLOB is used without specifying the length, the length defaults to
1GB.
<character string type> ::= { CHARACTER | CHAR }
[ <left paren> <character length> <right paren> ] | {
CHARACTER VARYING | CHAR VARYING | VARCHAR } <left paren>
<character length> <right paren> | LONGVARCHAR [ <left
paren> <character length> <right paren> ] | <character
large object type>
<character large object type> ::= { CHARACTER
LARGE OBJECT | CHAR LARGE OBJECT | CLOB } [ <left paren>
<character large object length> <right paren>
]
<character length> ::= <unsigned integer>
[ <char length units> ]
<large object length> ::= <length> [
<multiplier> ] | <large object length
token>
<character large object length> ::= <large
object length> [ <char length units> ]
<large object length token> ::= <digit>...
<multiplier>
<multiplier> ::= K | M | G
<char length units> ::= CHARACTERS |
OCTETS
Each character type has a collation. This is either a default collation or stated explicitly with the COLLATE clause. Collations are discussed in the Schemas and Database Objects chapter.
CHAR(10) CHARACTER(10) VARCHAR(2) CHAR VARYING(2) CLOB(1000) CLOB(30K) CHARACTER LARGE OBJECT(1M) LONGVARCHAR
The BINARY, BINARY VARYING and BLOB types are the SQL Standard
binary string types. VARBINARY and BINARY LARGE OBJECT are synonyms for
BINARY VARYING and BLOB types. HyperSQL also supports LONGVARBINARY as a
synonym for VARBINARY. You can set LONGVARBINARY to map to BLOB, with
the sql.longvar_is_lob
connection property or the SET
DATABASE SQL LONGVAR IS LOB TRUE statement.
Binary string types are used in a similar way to character string types. There are several built-in functions that are overloaded to support character, binary and bit strings.
The BINARY type represents a fixed width-string. Each shorter string is padded with zeros to fill the fixed width. Similar to the CHARACTER type, the trailing zeros in the BINARY string are simply discarded in some operations. For the same reason, it is best to avoid this particular type and use VARBINARY instead.
When two binary values are compared, if one is of BINARY type, then zero padding is performed to extend the length of the shorter string to the longer one before comparison. No padding is performed with other binary types. If the bytes compare equal to the end of the shorter value, then the longer string is considered larger than the shorter string.
If BINARY is used without specifying the length, the length
defaults to 1. For the BLOB type, the length limit can be defined in
units of kilobyte (K, 1024), megabyte (M, 1024 * 1024) or gigabyte (G,
1024 * 1024 * 1024), using the <multiplier>
. If
BLOB is used without specifying the length, the length defaults to
1GB.
<binary string type> ::= BINARY [ <left
paren> <length> <right paren> ] | { BINARY VARYING |
VARBINARY } <left paren> <length> <right paren> |
LONGVARBINARY [ <left paren> <length> <right paren> ]
| <binary large object string type>
<binary large object string type> ::= { BINARY
LARGE OBJECT | BLOB } [ <left paren> <large object length>
<right paren> ]
<length> ::= <unsigned
integer>
BINARY(10) VARBINARY(2) BINARY VARYING(2) BLOB(1000) BLOB(30G) BINARY LARGE OBJECT(1M) LONGVARBINARY
The BIT and BIT VARYING types are the supported bit string types. These types were defined by SQL:1999 but were later removed from the Standard. Bit types represent bit maps of given lengths. Each bit is 0 or 1. The BIT type represents a fixed width-string. Each shorter string is padded with zeros to fill the fixed with. If BIT is used without specifying the length, the length defaults to 1. The BIT VARYING type has a maximum width and shorter strings are not padded.
Before the introduction of the BOOLEAN type to the SQL Standard, a single-bit string of the type BIT(1) was commonly used. For compatibility with other products that do not conform to, or extend, the SQL Standard, HyperSQL allows values of BIT and BIT VARYING types with length 1 to be converted to and from the BOOLEAN type. BOOLEAN TRUE is considered equal to B'1', BOOLEAN FALSE is considered equal to B'0'.
For the same reason, numeric values can be assigned to columns and variables of the type BIT(1). For assignment, the numeric value zero is converted to B'0', while all other values are converted to B'1'. For comparison, numeric values 1 is considered equal to B'1' and numeric value zero is considered equal to B'0'.
It is not allowed to perform other arithmetic or boolean operations involving BIT(1) and BIT VARYING(1). The kid of operations allowed on bit strings are analogous to those allowed on BINARY and CHARACTER strings. Several built-in functions support all three types of string.
<bit string type> ::= BIT [ <left paren>
<length> <right paren> ] | BIT VARYING <left paren>
<length> <right paren>
BIT BIT(10) BIT VARYING(2)
BLOB and CLOB are lob types. These types are used for very long strings that do not necessarily fit in memory. Small lobs that fit in memory can be accessed just like BINARY or VARCHAR column data. But lobs are usually much larger and therefore accessed with special JDBC methods.
To insert a lob into a table, or to update a column of lob type
with a new lob, you can use the setBinaryStream()
and
setCharacterStream()
methods of JDBC
java.sql.PreparedStatement
. These are very efficient
methods for long lobs. Other methods are also supported. If the data for
the BLOB or CLOB is already a memory object, you can use the
setBytes()
or setString()
methods,
which are efficient for memory data. Another method is to obtain a lob
with the getBlob()
and getClob()
methods of java.sql.Connection
, populate its data,
then use the setBlob()
or
setClob()
methods of
PreparedStatement
. Yet another method allows to
create instances of org.hsqldb.jdbc.JDBCBlobFile
and
org.hsqldb.jdbc.JDBCClobFile
and construct a large
lob for use with setBlob()
and
setClob()
methods.
A lob is retrieved from a ResultSet with the
getBlob()
or getClob()
method. The
steaming methods of the lob objects are then used to access the data.
HyperSQL also allows efficient access to chunks of lobs with
getBytes()
or getString()
methods.
Furthermore, parts of a BLOB or CLOB already stored in a table can be
modified. An updatable ResultSet
is used to select
the row from the table. The getBlob()
or
getClob()
methods of ResultSet
are
used to access the lob as a java.sql.Blob
or
java.sql.Clob
object. The
setBytes()
and setString()
methods
of these objects can be used to modify the lob. Finally the
updateRow()
method of the
ResultSet
is used to update the lob in the row. Note
these modifications are not allowed with compressed or encrypted
lobs.
Lobs are logically stored in columns of tables. Their physical storage is a separate *.lobs file. This file is created as soon as a BLOB or CLOB is inserted into the database. The file will grow as new lobs are inserted into the database. In version 2, the *.lobs file is never deleted even if all lobs are deleted from the database. In this case you can delete the *.lobs file after a SHUTDOWN. When a CHECKPOINT happens, the space used for deleted lobs is freed and is reused for future lobs. By default, clobs are stored without compression. You can use a database setting to enable compression of clobs. This can significantly reduce the storage size of clobs.
Any serializable JAVA Object can be inserted directly into a
column of type OTHER using any variation of
PreparedStatement.setObject()
methods.
For comparison purposes and in indexes, any two Java Objects are considered equal unless one of them is NULL. You cannot search for a specific object or perform a join on a column of type OTHER.
Please note that HSQLDB is not an object-relational database. Java
Objects can simply be stored internally and no operations should be
performed on them other than assignment between columns of type OTHER or
tests for NULL. Tests such as WHERE object1 = object2
do not mean what you might expect, as any non-null object
would satisfy such a tests. But WHERE object1 IS NOT
NULL
is perfectly acceptable.
The engine does not allow normal column values to be assigned to
Java Object columns (for example, assigning an INTEGER or STRING to such
a column with an SQL statement such as UPDATE mytable SET
objectcol = intcol WHERE ...
).
<java object type> ::= OTHER
In older version of HyperSQL, all table column type definitions with a column length, precision or scale qualifier were accepted and ignored. HSQLDB 1.8 enforced correctness but included an option to enforce the length, precision or scale.
In HyperSQL 2, length, precision and scale qualifiers are always enforced. For backward compatibility, when older databases which had the property hsqldb.enforce_strict_size=false are converted to version 2, this property is retained. However, this is a temporary measure. You should test your application to ensure the length, precision and scale that is used for column definitions is appropriate for the application data. You can test with the default database setting, which enforces the sizes.
String types, including all BIT, BINARY and CHAR string types plus CLOB and BLOB, are generally defined with a length. If no length is specified for BIT, BINARY and CHAR, the default length is 1. For CLOB and BLOB an implementation defined length of 1M is used.
TIME and TIMESTAMP types can be defined with a fractional second precision between 0 and 9. INTERVAL type definition may have precision and, in some cases, fraction second precision. DECIMAL and NUMERIC types may be defined with precision and scale. For all of these types a default precision or scale value is used if one is not specified. The default scale is 0. The default fractional precision for TIME is 0, while it is 6 for TIMESTAMP.
Values can be converted from one type to another in two different ways: by using explicit CAST expression or by implicit conversion used in assignment, comparison and aggregation.
String values cannot be assigned to VARCHAR columns if they are longer than the defined type length. For CHARACTER columns, a long string can be assigned (with truncation) only if all the characters after the length are spaces. Shorter strings are padded with the space character when inserted into a CHARACTER column. Similar rules are applied to VARBINARY and BINARY columns. For BINARY columns, the padding and truncation rules are applied with zero bytes, instead of spaces.
Explicit CAST of a value to a CHARACTER or VARCHAR type will
result in forced truncation or padding. So a test such as CAST
(mycol AS VARCHAR(2)) = 'xy'
will find the values beginning
with 'xy'. This is the equivalent of SUBSTRING(mycol FROM 1 FOR
2)= 'xy'
.
For all numeric types, the rules of explicit cast and implicit conversion are the same. If cast or conversion causes any digits to be lost from the fractional part, it can take place. If the non-fractional part of the value cannot be represented in the new type, cast or conversion cannot take place and will result in a data exception.
There are special rules for DATE, TIME, TIMESTAMP and INTERVAL casts and conversions.
HSQLDB fully supports datetime and interval types and operations, including all relevant optional features, as specified by the SQL Standard since SQL-92. The two groups of types are complementary.
The DATE type represents a calendar date with YEAR, MONTH and DAY fields.
The TIME type represents time of day with HOUR, MINUTE and SECOND fields, plus an optional SECOND FRACTION field.
The TIMESTAMP type represents the combination of DATE and TIME types.
TIME and TIMESTAMP types can include WITH TIME ZONE or WITHOUT TIME ZONE (the default) qualifiers. They can have fractional second parts. For example, TIME(6) has six fractional digits for the second field.
If fractional second precision is not specified, it defaults to 0 for TIME and to 6 for TIMESTAMP.
<datetime type> ::= DATE | TIME [ <left
paren> <time precision> <right paren> ] [ <with or
without time zone> ] | TIMESTAMP [ <left paren> <timestamp
precision> <right paren> ] [ <with or without time zone>
]
<with or without time zone> ::= WITH TIME ZONE |
WITHOUT TIME ZONE
<time precision> ::= <time fractional seconds
precision>
<timestamp precision> ::= <time fractional
seconds precision>
<time fractional seconds precision> ::=
<unsigned integer>
DATE TIME(6) TIMESTAMP(2) WITH TIME ZONE
Examples of the string literals used to represent date time values, some with time zone, some without, are below:
DATE '2008-08-22' TIMESTAMP '2008-08-08 20:08:08' TIMESTAMP '2008-08-08 20:08:08+8:00' /* Beijing */ TIME '20:08:08.034900' TIME '20:08:08.034900-8:00' /* US Pacific */
Time Zone
DATE values do not take time zones. For example United Nations designates 5 June as World Environment Day, which was observed on DATE '2008-06-05' in different time zones.
TIME and TIMESTAMP values without time zone, usually have a context that indicates some local time zone. For example, a database for college course timetables usually stores class dates and times without time zones. This works because the location of the college is fixed and the time zone displacement is the same for all the values. Even when the events take place in different time zones, for example international flight times, it is possible to store all the datetime information as references to a single time zone, usually GMT. For some databases it may be useful to store the time zone displacement together with each datetime value.
The time zone displacement is of the type INTERVAL HOUR TO MINUTE. This data type is described in the next section. The legal values are between '–14:00' and '+14:00'.
Operations on Datetime Types
The expression <datetime expression> AT TIME ZONE
<time displacement>
evaluates to a datetime value
representing exactly the same point of time in the specified
<time displacement>
. The expression, AT
LOCAL
is equivalent to AT TIME ZONE <local time
displacement>
. If AT TIME ZONE
is used
with a datetime operand of type WITHOUT TIME ZONE, the operand is first
converted to a value of type WITH TIME ZONE at the session’s time
displacement, then the specified time zone displacement is set for the
value. Therefore, in these cases, the final value depends on the time zone
of the session in which the statement was used.
AT TIME ZONE, modifies the field values of the datetime operand. This is done by the following procedure:
determine the corresponding datetime at UTC.
find the datetime value at the given time zone that corresponds with the UTC value from step 1.
Example a:
TIME '12:00:00' AT TIME ZONE INTERVAL '1:00' HOUR TO MINUTE
If the session’s time zone displacement is -'8:00', then in step 1, TIME '12:00:00' is converted to UTC, which is TIME '20:00:00+0:00'. In step 2, this value is expressed as TIME '21:00:00+1:00'.
Example b:
TIME '12:00:00-5:00' AT TIME ZONE INTERVAL '1:00' HOUR TO MINUTE
Because the operand has a time zone, the result is independent of the session time zone displacement. Step 1 results in TIME '17:00:00+0:00', and step 2 results in TIME '18:00:00+1:00'
Note that the operand is not limited to datetime literals used in these examples. Any valid expression that evaluates to a datetime value can be the operand.
Type Conversion
CAST is used to for all other conversions. Examples:
CAST (<value> AS TIME WITHOUT TIME ZONE) CAST (<value> AS TIME WITH TIME ZONE)
In the first example, if <value>
has a time
zone component, it is simply dropped. For example TIME '12:00:00-5:00' is
converted to TIME '12:00:00'
In the second example, if <value>
has no
time zone component, the current time zone displacement of the session is
added. For example TIME '12:00:00' is converted to TIME '12:00:00-8:00'
when the session time zone displacement is '-8:00'.
Conversion between DATE and TIMESTAMP is performed by removing the TIME component of a TIMESTAMP value or by setting the hour, minute and second fields to zero. TIMESTAMP '2008-08-08 20:08:08+8:00' becomes DATE '2008-08-08', while DATE '2008-08-22' becomes TIMESTAMP '2008-08-22 00:00:00'.
Conversion between TIME and TIMESTAMP is performed by removing the DATE field values of a TIMESTAMP value or by appending the fields of the TIME value to the fields of the current session date value.
Assignment
When a value is assigned to a datetime target, e.g., a value is used to update a row of a table, the type of the value must be the same as the target, but the WITH TIME ZONE or WITHOUT TIME ZONE characteristics can be different. If the types are not the same, an explicit CAST must be used to convert the value into the target type.
Comparison
When values WITH TIME ZONE are compared, they are converted to UTC values before comparison. If a value WITH TIME ZONE is compared to another WITHOUT TIME ZONE, then the WITH TIME ZONE value is converted to AT LOCAL, then converted to WITHOUT TIME ZONE before comparison.
It is not recommended to design applications that rely on comparisons and conversions between TIME values WITH TIME ZONE. The conversions may involve normalisation of the time value, resulting in unexpected results. For example, the expression: BETWEEN(TIME '12:00:00-8:00', TIME '22:00:00-8:00') is converted to BETWEEN(TIME '20:00:00+0:00', TIME '06:00:00+0:00') when it is evaluated in the UTC zone, which is always FALSE.
Functions
Several functions return the current session timestamp in different datetime types:
|
CURRENT_DATE |
DATE |
|
CURRENT_TIME |
TIME WITH TIME ZONE |
|
CURRENT_TIMESTAMP |
TIMESTAMP WITH TIME ZONE |
|
LOCALTIME |
TIME WITHOUT TIME ZONE |
|
LOCALTIMESTAMP |
TIMESTAMP WITHOUT TIME ZONE |
HyperSQL supports a very extensive range of functions for conversion, extraction and manipulation of DATE and TIMESTAMP values. See the Built In Functions chapter.
Session Time Zone Displacement
When an SQL session is started (with a JDBC connection) the local time zone of the client JVM (including any seasonal time adjustments such as daylight saving time) is used as the session time zone displacement. Note that the SQL session time displacement is not changed when a seasonal time adjustment takes place while the session is open. To change the SQL session time zone displacement use the following commands:
SET TIME ZONE <time
displacement>
SET TIME ZONE LOCAL
The first command sets the displacement to the given value. The second command restores the original, real time zone displacement of the session.
Datetime Values and Java
When datetime values are sent to the database using the
PreparedStatement
or
CallableStatement
interfaces, the Java object is
converted to the type of the prepared or callable statement parameter.
This type may be DATE, TIME, or TIMESTAMP (with or without time zone). The
time zone displacement is the time zone of the JDBC session.
When datetime values are retrieved from the database using the
ResultSet
interface, there are two representations. The
getString(…)
methods of the
ResultSet
interface, return an exact representation
of the value in the SQL type as it is stored in the database. This
includes the correct number of digits for the fractional second field, and
for values with time zone displacement, the time zone displacement.
Therefore if TIME '12:00:00' is stored in the database, all users in
different time zones will get '12:00:00' when they retrieve the value as a
string. The getTime(…)
and
getTimestamp(…)
methods of the
ResultSet
interface return Java objects that are
corrected for the session time zone. The UTC millisecond value contained
the java.sql.Time
or
java.sql.Timestamp
objects will be adjusted to the
time zone of the session, therefore the
toString()
method of these objects return the
same values in different time zones.
If you want to store and retrieve UTC values that are independent of any session's time zone, you can use a TIMESTAMP WITH TIME ZONE column. The setTime(...) and setTimestamp(...) methods of the PreparedStatement interface which have a Calendar parameter can be used to assign the values. The time zone of the given Calendar argument is used as the time zone. Conversely, the getTime(...) and getTimestamp(...) methods of the ResultSet interface which have a Calendar parameter can be used with a Calendar argument to retrieve the values.
JDBC has an unfortunate limitation and does not include type codes
for SQL datetime types that have a TIME ZONE property. Therefore, for
compatibility with database tools that are limited to the JDBC type codes,
HyperSQL reports these types by default as datetime types without TIME
ZONE. You can use the URL property
hsqldb.translate_dti_types=false
to override the
default behaviour.
Non-Standard Extensions
HyperSQL version 2.3.0 supports some extenstions to the SQL standard treatment of datetime and interval types. For example, the Standard expression to add a number of days to a date has an explicit INTERVAL value but HSQLDB also allows an integer to be used without specifying DAY. Examples of some Standard expressions and their non-standard alternatives are given below:
-- standard forms CURRENT_DATE + '2' DAY SELECT (LOCALTIMESTAMP - atimestampcolumn) DAY TO SECOND FROM atable -- non-standard forms CURRENT_DATE + 2 SELECT LOCALTIMESTAMP - atimestampcolumn FROM atable
It is recommended to use the SQL Standard syntax as it is more precise and avoids ambiguity.
Interval types are used to represent differences between date time values. The difference between two date time values can be measured in seconds or in months. For measurements in months, the units YEAR and MONTH are available, while for measurements in seconds, the units DAY, HOUR, MINUTE, SECOND are available. The units can be used individually, or as a range. An interval type can specify the precision of the most significant field and the second fraction digits of the SECOND field (if it has a SECOND field). The default precision is 2. The default second precision is 0.
<interval type> ::= INTERVAL <interval
qualifier>
<interval qualifier> ::= <start field> TO
<end field> | <single datetime field>
<start field> ::= <non-second primary datetime
field> [ <left paren> <interval leading field precision>
<right paren> ]
<end field> ::= <non-second primary datetime
field> | SECOND [ <left paren> <interval fractional seconds
precision> <right paren> ]
<single datetime field> ::= <non-second primary
datetime field> [ <left paren> <interval leading field
precision> <right paren> ] | SECOND [ <left paren>
<interval leading field precision> [ <comma> <interval
fractional seconds precision> ] <right paren>
]
<primary datetime field> ::= <non-second
primary datetime field> | SECOND
<non-second primary datetime field> ::= YEAR |
MONTH | DAY | HOUR | MINUTE
<interval fractional seconds precision> ::=
<unsigned integer>
<interval leading field precision> ::=
<unsigned integer>
Examples of INTERVAL type definition:
INTERVAL YEAR TO MONTH INTERVAL YEAR(3) INTERVAL DAY(4) TO HOUR INTERVAL MINUTE(4) TO SECOND(6) INTERVAL SECOND(4,6)
The word INTERVAL indicates the general type name. The rest of the
definition is called an <interval qualifier>
.
This designation is important, as in most expressions
<interval qualifier>
is used without the word
INTERVAL.
Interval Values
An interval value can be negative, positive or zero. An interval type has all the datetime fields in the specified range. These fields are similar to those in the TIMESTAMP type. The differences are as follows:
The first field of an interval value can hold any numeric value up to the specified precision. For example, the hour field in HOUR(2) TO SECOND can hold values above 23 (up to 99). The year and month fields can hold zero (unlike a TIMESTAMP value) and the maximum value of a month field that is not the most significant field, is 11.
The standard function ABS(<interval value
expression>)
can be used to convert a negative interval value
to a positive one.
The literal representation of interval values consists of the type definition, with a string representing the interval value inserted after the word INTERVAL. Some examples of interval literal below:
INTERVAL '145 23:12:19.345' DAY(3) TO SECOND(3) INTERVAL '3503:12:19.345' HOUR TO SECOND(3) /* equal to the first value */ INTERVAL '19.345' SECOND(4,3) /* maximum number of digits for the second value is 4, and each value is expressed with three fraction digits. */ INTERVAL '-23-10' YEAR(2) TO MONTH
Interval values of the types that are based on seconds can be cast into one another. Similarly those that are based on months can be cast into one another. It is not possible to cast or convert a value based on seconds to one based on months, or vice versa.
When a cast is performed to a type with a smaller least-significant field, nothing is lost from the interval value. Otherwise, the values for the missing least-significant fields are discarded. Examples:
CAST ( INTERVAL '145 23:12:19' DAY TO SECOND AS INTERVAL DAY TO HOUR ) = INTERVAL '145 23' DAY TO HOUR CAST(INTERVAL '145 23' DAY TO HOUR AS INTERVAL DAY TO SECOND) = INTERVAL '145 23:00:00' DAY TO SECOND
A numeric value can be cast to an interval type. In this case the numeric value is first converted to a single-field INTERVAL type with the same field as the least significant field of the target interval type. This value is then converted to the target interval type For example CAST( 22 AS INTERVAL YEAR TO MONTH) evaluates to INTERVAL '22' MONTH and then INTERVAL '1 10' YEAR TO MONTH. Note that SQL Standard only supports casts to single-field INTERVAL types, while HyperSQL allows casting to multi-field types as well.
An interval value can be cast to a numeric type. In this case the interval value is first converted to a single-field INTERVAL type with the same field as the least significant filed of the interval value. The value is then converted to the target type. For example CAST (INTERVAL '1-11' YEAR TO MONTH AS INT) evaluates to INTERVAL '23' MONTH, and then 23.
An interval value can be cast into a character type, which results in an INTERVAL literal. A character value can be cast into an INTERVAL type so long as it is a string with a format compatible with an INTERVAL literal.
Two interval values can be added or subtracted so long as the types of both are based on the same field, i.e., both are based on MONTH or SECOND. The values are both converted to a single-field interval type with same field as the least-significant field between the two types. After addition or subtraction, the result is converted to an interval type that contains all the fields of the two original types.
An interval value can be multiplied or divided by a numeric value. Again, the value is converted to a numeric, which is then multiplied or divided, before converting back to the original interval type.
An interval value is negated by simply prefixing with the minus sign.
Interval values used in expressions are either typed values,
including interval literals, or are interval casts. The expression:
<expression> <interval qualifier>
is a cast
of the result of the <expression>
into the
INTERVAL type specified by the <interval qualifier>. The
cast can be formed by adding the keywords and parentheses as follows: CAST
( <expression> AS INTERVAL <interval qualifier>
).
The examples below feature different forms of expression
that represent an interval value, which is then added to the given date
literal.
DATE '2000-01-01' + INTERVAL '1-10' YEAR TO MONTH /* interval literal */ DATE '2000-01-01' + '1-10' YEAR TO MONTH /* the string '1-10' is cast into INTERVAL YEAR TO MONTH */ DATE '2000-01-01' + 22 MONTH /* the integer 22 is cast into INTERVAL MONTH, same value as above */ DATE '2000-01-01' - 22 DAY /* the integer 22 is cast into INTERVAL DAY */ DATE '2000-01-01' + COL2 /* the type of COL2 must be an INTERVAL type */ DATE '2000-01-01' + COL2 MONTH /* COL2 may be a number, it is cast into a MONTH interval */
Datetime and Interval Operations
An interval can be added to or subtracted from a datetime value so long as they have some fields in common. For example, an INTERVAL MONTH cannot be added to a TIME value, while an INTERVAL HOUR TO SECOND can. The interval is first converted to a numeric value, then the value is added to, or subtracted from, the corresponding field of the datetime value.
If the result of addition or subtraction is beyond the permissible range for the field, the field value is normalised and carried over to the next significant field until all the fields are normalised. For example, adding 20 minutes to TIME '23:50:10' will result successively in '23:70:10', '24:10:10' and finally TIME '00:10:10'. Subtracting 20 minutes from the result is performed as follows: '00:-10:10', '-1:50:10', finally TIME '23:50:10'. Note that if DATE or TIMESTAMP normalisation results in the YEAR field value out of the range (1,1000), then an exception condition is raised.
If an interval value based on MONTH is added to, or subtracted from a DATE or TIMESTAMP value, the result may have an invalid day (30 or 31) for the given result month. In this case an exception condition is raised.
The result of subtraction of two datetime expressions is an interval
value. The two datetime expressions must be of the same type. The type of
the interval value must be specified in the expression, using only the
interval field names. The two datetime expressions are enclosed in
parentheses, followed by the <interval qualifier>
fields. In the first example below, COL1 and COL2 are of the same datetime
type, and the result is evaluated in INTERVAL YEAR TO MONTH type.
(COL1 – COL2) YEAR TO MONTH /* the difference between two DATE or two TIEMSTAMP values in years and months */ (CURRENT_DATE – COL3) DAY /* the number of days between the value of COL3 and the current date */ (CURRENT_DATE - DATE '2000-01-01') YEAR TO MONTH /* the number of years and months since the beginning of this century */ CURRENT_DATE - 2 DAY /* the date of the day before yesterday */ (CURRENT_TIMESTAMP - TIMESTAMP '2009-01-01 00:00:00') DAY(4) TO SECOND(2) /* days to seconds since the given date */
The individual fields of both datetime and interval values can be extracted using the EXTRACT function. The same function can also be used to extract the time zone displacement fields of a datetime value.
EXTRACT ({YEAR | MONTH | DAY | HOUR | MINUTE | SECOND |
TIMEZONE_HOUR | TIMEZONE_MINUTE | DAY_OF_WEEK | WEEK_OF_YEAR } FROM
{<datetime value> | <interval value>})
The dichotomy between interval types based on seconds, and those based on months, stems from the fact that the different calendar months have different numbers of days. For example, the expression, “nine months and nine days since an event” is not exact when the date of the event is unknown. It can represent a period of around 284 days give or take one. SQL interval values are independent of any start or end dates or times. However, when they are added to or subtracted from certain date or timestamp values, the result may be invalid and cause an exception (e.g. adding one month to January 30 results in February 30, which is invalid).
JDBC has an unfortunate limitation and does not include type codes
for SQL INTERVAL types. Therefore, for compatibility with database tools
that are limited to the JDBC type codes, HyperSQL reports these types by
default as VARCHAR. You can use the URL property
hsqldb.translate_dti_types=false
to override the
default behaviour.
Array are a powerful feature of SQL:2008 and can help solve many common problems. Arrays should not be used as a substitute for tables.
HyperSQL supports arrays of values according to the SQL:2008 Standard.
Elements of the array are either NULL, or of the same data type. It is possible to define arrays of all supported types, including the types covered in this chapter and user defined types, except LOB types. An SQL array is one dimensional and is addressed from position 1. An empty array can also be used, which has no element.
Arrays can be stored in the database, as well as being used as temporary containers of values for simplifying SQL statements. They facilitate data exchange between the SQL engine and the user's application.
The full range of supported syntax allows array to be created, used in SELECT or other statements, combined with rows of tables and used in routine calls.
The type of a table column, a routine parameter, a variable, or the return value of a function can be defined as an array.
<array type> ::= <data type> ARRAY [ <left
bracket or trigraph> <maximum cardinality> <right bracket or
trigraph> ]
The word ARRAY is added to any valid type definition except BLOB
and CLOB type definitions. If the optional <maximum
cardinality>
is not used, the default value is 1024. The
size of the array cannot be extended beyond maximum cardinality.
In the example below, the table contains a column of integer
arrays and a column of varchar arrays. The VARCHAR array has an explicit
maximum size of 10, which means each array can have between 0 and 10
elements. The INTEGER array has the default maximum size of 1024. The
scores column has a default clause with an empty array. The default
clause can be defined only as DEFAULT NULL
or
DEFAULT ARRAY[]
and does not allow arrays containing
elements.
CREATE TABLE t (id INT PRIMARY KEY, scores INT ARRAY DEFAULT ARRAY[], names VARCHAR(20) ARRAY[10])
An array can be constructed from value expressions or a query expression.
<array value constructor by enumeration> ::= ARRAY
<left bracket or trigraph> <array element list> <right
bracket or trigraph>
<array element list> ::= <value expression> [
{ <comma> <value expression> }... ]
<array value constructor by query> ::= ARRAY
<left paren> <query expression> [ <order by clause> ]
<right paren>
In the examples below, arrays are constructed from values, column references or variables, function calls, or query expressions.
ARRAY [ 1, 2, 3 ] ARRAY [ 'HOT', 'COLD' ] ARRAY [ var1, var2, CURRENT_DATE ] ARRAY (SELECT lastname FROM namestable ORDER BY id)
Inserting and updating a table with an ARRAY column can use array constructors, not only for updated column values, but also in equality search conditions:
INSERT INTO t VALUES 10, ARRAY[1,2,3], ARRAY['HOT', 'COLD'] UPDATE t SET names = ARRAY['LARGE', 'SMALL'] WHERE id = 12 UPDATE t SET names = ARRAY['LARGE', 'SMALL'] WHERE id < 12 AND scores = ARRAY[3,4]
When using a PreparedStatement with an ARRAY parameter, an object
of the type java.sql.Array must be used to set the parameter. The
org.hsqldb.jdbc.JDBCArrayBasic
class can be used
for constructing a java.sql.Array object in the user's application. Code
fragment below:
String sql = "UPDATE t SET names = ? WHERE id = ?"; PreparedStatement ps = connection.prepareStatement(sql) Object[] data = new Object[]{"one", "two"}; // default types defined in org.hsqldb.types.Type can be used org.hsqldb.types.Type type = org.hsqldb.types.Type.SQL_VARCHAR_DEFAULT; JDBCArrayBasic array = new JDBCArrayBasic(data, type); ps.setArray(1, array); ps.setInt(2, 1000); ps.executeUpdate();
A trigraph is a substitute for <left bracket> and <right bracket>.
<left bracket trigraph> ::= ??(
<right bracket trigraph> ::= ??)
The example below shows the use of trigraphs instead of brackets.
INSERT INTO t VALUES 10, ARRAY??(1,2,3??), ARRAY['HOT', 'COLD'] UPDATE t SET names = ARRAY ??('LARGE', 'SMALL'??) WHERE id = 12 UPDATE t SET names = ARRAY['LARGE', 'SMALL'] WHERE id < 12 AND scores = ARRAY[3,4]
The most common operations on an array are element reference and assignment, which are used when reading or writing an element of the array. Unlike Java and many other languages, arrays are extended if an element is assigned to an index beyond the current length. This can result in gaps containing NULL elements. Array length cannot exceed the maximum cardinality.
Elements of all arrays, including those that are the result of function calls or other operations can be referenced for reading.
<array element reference> ::= <array value
expression> <left bracket> <numeric value expression>
<right bracket>
Elements of arrays that are table columns or routine variables can be referenced for writing. This is done in a SET statement, either inside an UPDATE statement, or as a separate statement in the case of routine variables, OUT and INOUT parameters.
<target array element specification> ::= <target
array reference> <left bracket or trigraph> <simple value
specification> <right bracket or trigraph>
<target array reference> ::= <SQL parameter
reference> | <column reference>
Note that only simple values or variables are allowed for the array index when an assignment is performed. The examples below demonstrates how elements of the array are referenced in SELECT and an UPDATE statement.
SELECT scores[ranking], names[ranking] FROM t JOIN t1 on (t.id = t1.tid) UPDATE t SET scores[2] = 123, names[2] = 'Reds' WHERE id = 10
SELECT scores[ranking], names[ranking] FROM t JOIN t1 on (t.id = t1.tid) UPDATE t SET scores[2] = 123, names[2] = 'Reds' WHERE id = 10
Several SQL operations and functions can be used with arrays.
CONCATENATION
Array concatenation is performed similar to string concatenation. All elements of the array on the right are appended to the array on left.
<array concatenation> ::= <array value
expression 1> <concatenation operator> <array value
expression 2>
<concatenation operator> ::= ||
FUNCTIONS
Seven functions operate on arrays. Details are described in the Built In Functions chapter.
ARRAY_AGG
is an aggregate function and produces
an array containing values from differnt rows of a SELECT statement.
Details are described in the Data Access and Change chapter.
SEQUENCE_ARRAY
creates an array with sequential
elements.
CARDINALITY <left paren> <array value
expression> <right paren>
MAX_CARDINALITY <left paren> <array value
expression> <right paren>
Array cardinality and max cardinality are functions that return an integer. CARDINALITY returns the element count, while MAX_CARDINALITY returns the maximum declared cardinality of an array.
POSITION_ARRAY <left paren> <value
expression> IN <array value expression> [FROM <numeric value
expression>] <right paren>
The POSITION_ARRAY function returns the position of the first match for the <value expression> from the start or from the given start position when <numeric value expression> is used.
TRIM_ARRAY <left paren> <array value
expression> <comma> <numeric value expression> <right
paren>
The TRIM_ARRAY function returns a copy of an array with the
specified number of elements removed from the end of the array. The
<array value expression>
can be any expression
that evaluates to an array.
SORT_ARRAY <left paren> <array value
expression> [ { ASC | DESC } ] [ NULLS { FIRST | LAST } ] <right
paren>
The SORT_ARRAY function returns a sorted copy of an array. NULL elements appear at the beginning of the new array. You can change the sort direction or the position of NULL elements with the option keywords.
CAST
An array can be cast into an array of a different type. Each element of the array is cast into the element type of the target array type.
UNNEST
Arrays can be converted into table references with the UNNEST keyword.
UNNEST(<array value expression>) [ WITH ORDINALITY
]
The <array value expression>
can be any
expression that evaluates to an array. A table is returned that contains
one column when WITH ORDINALITY is not used, or two columns when WITH
ORDINALITY is used. The first column contains the elements of the array
(including all the nulls). When the table has two columns, the second
column contains the ordinal position of the element in the array. When
UNNEST is used in the FROM clause of a query, it implies the LATERAL
keyword, which means the array that is converted to table can belong to
any table that precedes the UNNEST in the FROM clause. This is explained
in the Data Access and Change chapter.
INLINE CONSTRUCTOR
Array constructors can be used in SELECT and other statements. For example, an array constructor with a subquery can return the values from several rows as one array.
The example below shows an ARRAY constructor with a correlated subquery to return the list of order values for each customer. The CUSTOMER table that is included for tests in the DatabaseManager GUI app is the source of the data.
SELECT FIRSTNAME, LASTNAME, ARRAY(SELECT INVOICE.TOTAL FROM INVOICE WHERE CUSTOMERID = CUSTOMER.ID) AS ORDERS FROM CUSTOMER FIRSTNAME LASTNAME ORDERS --------- --------- -------------------------------------- Laura Steel ARRAY[2700.90,4235.70] Robert King ARRAY[4761.60] Robert Sommer ARRAY[] Michael Smith ARRAY[3420.30]
COMPARISON
Arrays can be compared for equality, but they cannot be compared for ordering values or range comparison. Array expressions are therefore not allowed in an ORDER BY clause, or in a comparison expression such as GREATER THAN. It is possible to define a UNIQUE constraint on a column of ARRAY type. Two arrays are equal if they have the same length and the values at each index position are either equal or both NULL.
USER DEFINED FUNCTIONS and PROCEDURES
Array parameters, variables and return values can be specified in user defined functions and procedures, including aggregate functions. An aggregate function can return an array that contains all the scalar values that have been aggregated. These capabilities allow a wider range of applications to be covered by user defined functions and easier data exchange between the engine and the user's application.
HyperSQL supports PRIMARY KEY, UNIQUE and FOREIGN KEY constraints, which can span multiple columns.
The engine creates indexes internally to support PRIMARY KEY, UNIQUE and FOREIGN KEY constraints: a unique index is created for each PRIMARY KEY or UNIQUE constraint; an ordinary index is created for each FOREIGN KEY constraint.
HyperSQL allows defining indexes on single or multiple columns. You should not create duplicate user-defined indexes on the same column sets covered by constraints. This would result in unnecessary memory and speed overheads. See the discussion in the Deployment Guide chapter for more information.
Indexes are crucial for adequate query speed. When range or equality
conditions are used e.g. SELECT ... WHERE acol > 10 AND bcol =
0
, an index should exist on one of the columns that has a
condition. In this example, the bcol
column is the best
candidate. HyperSQL always uses the best condition and index. If there are
two indexes, one on acol, and another on bcol, it will choose the index on
bcol.
Queries always return results whether indexes exist or not, but they
return much faster when an index exists. As a rule of thumb, HSQLDB is
capable of internal processing of queries at over 100,000 rows per second.
Any query that runs into several seconds is clearly accessing thousands of
rows. The query should be checked and indexes should be added to the
relevant columns of the tables if necessary. The EXPLAIN PLAN FOR
<query>
statement can be used to see which indexes are
used to process the query.
When executing a DELETE or UPDATE statement, the engine needs to find the rows that are to be deleted or updated. If there is an index on one of the columns in the WHERE clause, it is often possible to start directly from the first candidate row. Otherwise all the rows of the table have to be examined.
Indexes are even more important in joins between multiple tables.
SELECT ... FROM t1 JOIN t2 ON t1.c1 = t2.c2
is
performed by taking rows of t1 one by one and finding a matching row in
t2. If there is no index on t2.c2 then for each row of t1, all the rows of
t2 must be checked. Whereas with an index, a matching row can be found in
a fraction of the time. If the query also has a condition on t1, e.g.,
SELECT ... FROM t1 JOIN t2 ON t1.c1 = t2.c2 WHERE t1.c3 =
4
then an index on t1.c3 would eliminate the need for checking
all the rows of t1 one by one, and will reduce query time to less than a
millisecond per returned row. So if t1 and t2 each contain 10,000 rows,
the query without indexes involves checking 100,000,000 row combinations.
With an index on t2.c2, this is reduced to 10,000 row checks and index
lookups. With the additional index on t2.c2, only about 4 rows are checked
to get the first result row.
Note that in HSQLDB an index on multiple columns can be used
internally as a non-unique index on the first column in the list. For
example: CONSTRAINT name1 UNIQUE (c1, c2, c3);
means
there is the equivalent of CREATE INDEX name2 ON
atable(c1);
. So you do not need to specify an extra index if you
require one on the first column of the list.
In HyperSQL 2, a multi-column index will speed up queries that
contain joins or values on the first n columns of the index. You need NOT
declare additional individual indexes on those columns unless you use
queries that search only on a subset of the columns. For example, rows of
a table that has a PRIMARY KEY or UNIQUE constraint on three columns or
simply an ordinary index on those columns can be found efficiently when
values for all three columns, or the first two columns, or the first
column, are specified in the WHERE clause. For example, SELECT
... FROM t1 WHERE t1.c1 = 4 AND t1.c2 = 6 AND t1.c3 = 8
will use
an index on t1(c1,c2,c3)
if it exists.
A multi-column index will not speed up queries on the second or third column only. The first column must be specified in the JOIN .. ON or WHERE conditions.
Sometimes query speed depends on the order of the tables in the JOIN
.. ON or FROM clauses. For example the second query below should be faster
with large tables (provided there is an index on
TB.COL3
). The reason is that TB.COL3
can be evaluated very quickly if it applies to the first table (and there
is an index on TB.COL3
):
-- TB is a very large table with only a few rows where TB.COL3 = 4 SELECT * FROM TA JOIN TB ON TA.COL1 = TB.COL2 AND TB.COL3 = 4; SELECT * FROM TB JOIN TA ON TA.COL1 = TB.COL2 AND TB.COL3 = 4;
The general rule is to put first the table that has a narrowing condition on one of its columns. In certain cases, HyperSQL 2.2.x reorders the joined tables if it is obvious that this will introduce a narrowing condition.
HyperSQL features automatic, on-the-fly indexes for views and subselects that are used in a query.
Indexes are used when a LIKE condition searches from the start of the string.
Indexes are used for ORDER BY clauses if the same index is used for selection and ordering of rows. It is possible to force the use of index for ORDER BY.
HyperSQL 2.2.x changes the order of tables in a query in order to optimise processing. This happens only when one of the tables has a narrowing condition and reordering does not change the result of the query.
HyperSQL optimises queries to use indexes, for all types of range and equality conditions, including IS NULL and NOT NULL conditions. Conditions can be in join or WHERE clauses, including all types of joins.
In addition, HyperSQL will use an index (if one exists) for IN conditions, whether constants, variable, or subqueries are used on the right hand side of the IN predicate. Multicolumn IN conditions can also use an index.
HyperSQL can always use indexes when several conditions are combined with the AND operator, choosing a conditions which can use an index. This now extended to all equality conditions on multiple columns that are part of an index.
HyperSQL will also use indexes when several conditions are combined with the OR operator and each condition can use an index (each condition may use a different index). For example, if a huge table has two separate columns for first name and last name, and both columns are indexed, a query such as the following example will use the indexes and complete in a short time:
-- TC is a very large table SELECT * FROM TC WHERE TC.FIRSTNAME = 'John' OR TC.LASTNAME = 'Smith' OR TC.LASTNAME = 'Williams'
Each subquery is considered a separate SELECT statement and uses indexes when they are available.
In each SELECT statement, at least one index per table can be used if there is a query conditions that can use the index. When conditions on a table are combined with the OR operator, and each condition can use an index, multiple indexes per table are used.
HyperSQL optimises simple row count queries in the form of
SELECT COUNT(*) FROM <table>
and returns the
result immediately (this optimisation does not take place in MVCC
mode).
HyperSQL can use an index on a column for SELECT
MAX(<column>) FROM <table>
and SELECT
MIN(<column>) FROM <table>
queries. There should
be an index on the <column> and the query can have a WHERE
condition on the same column. In the example below the maximum value for
the TB.COL3 below 1000000 is returned.
SELECT MAX(TB.COL3) FROM TB WHERE TB.COL < 1000000
HyperSQL can use an index for simple queries containing DISTINCT or GROUP BY to avoid checking all the rows of the table. Note that indexes are always used if the query has a condition, regardless of the use of DISTINCT or GROUP BY. This particular optimisation applies to cases in which all the columns in the SELECT list are from the same table and are covered by a single index, and any join or query condition uses this index.
For example, with the large table below, a DISTINCT or GROUP BY query to return all the last names, can use an the index on the TC.LASTNAME column. Similarly, a GROUP BY query on two columns can use an index that covers the two columns.
-- TC is a very large table SELECT DISTINCT LASTNAME FROM TC WHERE TC.LASTNAME > 'F' SELECT STATE, LASTNAME FROM TC GROUP BY STATE, LASTNAME
HyperSQL can use an index on an ORDER BY clause if all the columns in ORDER BY are in a single-column or multi-column index (in the exact order). This is important if there is a LIMIT n (or FETCH n ROWS ONLY) clause. In this situation, the use of index allows the query processor to access only the number of rows specified in the LIMIT clause, instead of building the whole result set, which can be huge. This also works for joined tables when the ORDER BY clause is on the columns of the first table in a join. Indexes are used in the same way when ORDER BY ... DESC is specified in the query. Note that unlike some other RDBMS, HyperSQL does not need or create DESC indexes. It can use any ordinary, ascending index for ORDER BY ... DESC.
If there is an equality or range condition (e.g. EQUALS, GREATER THAN) condition on the columns specified in the ORDER BY clause, the index is still used.
In the two examples below, the index on TA.COL3 is used and only up to 1000 rows are processed and returned.
-- TA is a very large table with an index on TA.COL3 SELECT * FROM TA JOIN TB ON TA.COL2 = TB.COL1 WHERE TA.COL3 > 40000 ORDER BY TA.COL3 LIMIT 1000; SELECT * FROM TA JOIN TB ON TA.COL2 = TB.COL1 WHERE TA.COL3 > 40000 AND TA.COL3 < 100000 ORDER BY TA.COL3 DESC LIMIT 1000;
But if the query contains an equality condition on another indexed column in the table, this may take precedence and no index may be used for ORDER BY. In this case USING INDEX can be added to the end of the query to force the use of the index for the LIMIT operation. In the example below there is an index on TA.COL1 as well as the index on TA.COL3. Normally the index on TA.COL1 is used, but the USING INDEX hint results in the index on TB.COL3 to be used for selecting the first 1000 rows.
-- TA is a very large table with an index on TA.COL3 and a separate index on TA.COL1 SELECT * FROM TA JOIN TB ON TA.COL2 = TB.COL1 WHERE TA.COL1 = 'SENT' AND TB.COL3 > 40000 ORDER BY TB.COL3 LIMIT 1000 USING INDEX;
$Revision: 5212 $
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
All SQL statements are executed in sessions. When a connection is established to the database, a session is started. The authorization of the session is the name of the user that started the session. A session has several properties. These properties are set by default at the start according to database settings.
SQL Statements are generally transactional statements. When a transactional statement is executed, it starts a transaction if no transaction is in progress. If SQL Data is modified during a transaction, the change can be undone with a ROLLBACK statement. When a COMMIT statement is executed, the transaction is ended. If a single statement fails, the transaction is not normally terminated. However, some failures are caused by execution of statements that are in conflict with statements executed in other concurrent sessions. Such failures result in an implicit ROLLBACK, in addition to the exception that is raised.
Schema definition and manipulation statements are also transactional according to the SQL Standard. HyperSQL 2.0 performs automatic commits before and after the execution of such transactions. Therefore, schema-related statements cannot be rolled back. This is likely to change in future versions.
Some statements are not transactional. Most of these statements are used to change the properties of the session. These statements begin with the SET keyword.
If the AUTOCOMMIT property of a session is TRUE, then each transactional statement is followed by an implicit COMMIT.
The default isolation level for a session is READ COMMITTED. This
can be changed using the JDBC java.sql.Connection
object and its setTransactionIsolation(int level)
method. The session can be put in read-only mode using the
setReadOnly(boolean readOnly)
method. Both
methods can be invoked only after a commit or a rollback, but not during a
transaction.
The isolation level and / or the readonly mode of a transaction can also be modified using an SQL statement. You can use the statement to change only the isolation mode, only the read-only mode, or both at the same time. This command can be issued only after a commit or rollback.
SET TRANSACTION <transaction characteristic> [
<comma> <transaction characteristic> ]
Details of the statement is described later in this chapter.
Each session has several system attributes. A session can also have user-defined session variables.
The system attributes reflect the current mode of operation for
the session. These attributes can be accessed with function calls and
can be referenced in queries. For example, they can be returned using
the VALUES <attribute function>, ...
statement.
The named attributes such as CURRENT_USER, CURRENT_SCHEMA, etc. are SQL Standard functions. Other attributes of the session, such as auto-commit or read-only modes can be read using other built-in functions. All these functions are listed in the Built In Functions chapter.
Session variables are user-defined variables created the same way as the variables for stored procedures and functions. Currently, these variables cannot be used in general SQL statements. They can be assigned to IN, INOUT and OUT parameters of stored procedures. This allows calling stored procedures which have INOUT or OUT arguments and is useful for development and debugging. See the example in the SQL-Invoked Routines chapter, under Formal Parameters.
Example 3.1. User-defined Session Variables
DECLARE counter INTEGER DEFAULT 3; DECLARE result VARCHAR(20) DEFAULT NULL; SET counter=15; CALL myroutine(counter, result)
With necessary access privileges, sessions can access all table, including GLOBAL TEMPORARY tables, that are defined in schemas. Although GLOBAL TEMPORARY tables have a single name and definition which applies to all sessions that use them, the contents of the tables are different for each session. The contents are cleared either at the end of each transaction or when the session is closed.
Session tables are different because their definition is visible only within the session that defines a table. The definition is dropped when the session is closed. Session tables do not belong to schemas.
<temporary table declaration> ::= DECLARE LOCAL
TEMPORARY TABLE <table name> <table element list> [ ON
COMMIT { PRESERVE | DELETE } ROWS ]
The syntax for declaration is based on the SQL Standard. A session table cannot have FOREIGN KEY constraints, but it can have PRIMARY KEY, UNIQUE or CHECK constraints. A session table definition cannot be modified by adding or removing columns, indexes, etc.
It is possible to refer to a session table using its name, which takes precedence over a schema table of the same name. To distinguish a session table from schema tables, the pseudo schema names, MODULE or SESSION can be used. An example is given below:
Example 3.2. User-defined Temporary Session Tables
DECLARE LOCAL TEMPORARY TABLE buffer (id INTEGER PRIMARY KEY, textdata VARCHAR(100)) ON COMMIT PRESERVE ROWS INSERT INTO module.buffer SELECT id, firstname || ' ' || lastname FROM customers -- do some more work DROP TABLE module.buffer -- or use alternative pseudo schema name DROP TABLE session.buffer
Session tables can be created inside a transaction.
Automatic indexes are created and used on session tables when necessary
for a query or other statement. By default, session table data is held
in memory. This can be changed with the SET SESSION RESULT
MEMORY ROWS
statement.
HyperSQL 2 has been fully redesigned to support different transaction isolation models. It no longer supports the old 1.8.x model with "dirty read". Although it is perfectly possible to add an implementation of the transaction manager that supports the legacy model, we thought this is no longer necessary. The new system allows you to select the transaction isolation model while the engine is running. It also allows you to choose different isolation levels for different simultaneous sessions.
HyperSQL 2 supports three concurrency control models, two-phase-locking (2PL), which is the default, multiversion concurrency control (MVCC) and a hybrid model, which is 2PL plus multiversion rows. Within each model, it supports some of the 4 standard levels of transaction isolation: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE. The concurrency control model is a strategy that governs all the sessions and is set for the database, as opposed for individual sessions. The isolation level is a property of each SQL session, so different sessions can have different isolation levels. In the new implementation, all isolation levels avoid the "dirty read" phenomenon and do not read uncommitted changes made to rows by other transactions.
HyperSQL is fully multi threaded in all transaction models. Sessions continue to work simultaneously and can fully utilise multi-core processors.
To concurrency control model of a live database can be changed. The
SET DATABASE TRANSACTION CONTROL { LOCKS | MVLOCKS | MVCC
}
can be used by a user with the DBA role.
The two-phase locking model is the default mode. It is referred to by the keyword, LOCKS. In the 2PL model, each table that is read by a transaction is locked with a shared lock (read lock), and each table that is written to is locked with an exclusive lock (write lock). If two sessions read and modify different tables then both go through simultaneously. If one session tries to lock a table that has been locked by the other, if both locks are shared locks, it will go ahead. If either of the locks is an exclusive lock, the engine will put the session in wait until the other session commits or rolls back its transaction. In some cases the engine will invalidate the transaction of the current session, if the action would result in deadlock.
HyperSQL also supports explicit locking of a group of tables for the duration of the current transaction. Use of this command blocks access to the locked tables by other sessions and ensures the current session can complete the intended reads and writes on the locked tables.
If a table is read-only, it will not be locked by any transaction.
The READ UNCOMMITTED isolation level can be used in 2PL modes for read-only operations. It is the same as READ COMMITTED plus read only.
The READ COMMITTED isolation level is the default. It keeps write locks on tables until commit, but releases the read locks after each operation.
The REPEATABLE READ level is upgraded to SERIALIZABLE. These levels keep both read and write locks on tables until commit.
It is possible to perform some critical operations at the SERIALIZABLE level, while the rest of the operations are performed at the READ COMMITTED level.
Note: two phase locking refers to two periods in the life of a transaction. In the first period, locks are acquired, in the second period locks are released. No new lock is acquired after releasing a lock.
This model is referred to as MVLOCKS. It works the same way as normal 2PL as far as updates are concerned.
SNAPSHOT ISOLATION is a multiversion concurrency strategy which uses the snapshot of the whole database at the time of the start of the transaction. In this model, read only transactions use SNAPSHOT ISOLATION. While other sessions are busy changing the database, the read only session sees a consistent view of the database and can access all the tables even when they are locked by other sessions for updates.
There are many applications for this mode of operation. In heavily updated data sets, this mode allows uninterrupted read access to the data.
When multiple connections are used to access the database, the transaction manager controls their activities. When each transaction performs only reads or writes on a single table, there is no contention. Each transaction waits until it can obtain a lock then performs the operation and commits. All contentions occur when transactions perform reads and writes on more than one table, or perform a read, followed by a write, on the same table.
For example, when sessions are working at the SERIALIZABLE level, when multiple sessions first read from a table in order to check if a row exists, then insert a row into the same table when it doesn't exist, there will be regular contention. Transaction A reads from the table, then does Transaction B. Now if either Transaction A or B attempts to insert a row, it will have to be terminated as the other transaction holds a shared lock on the table. If instead of two operations, a single MERGE statement is used to perform the read and write, no contention occurs because both locks are obtained at the same time.
Alternatively, there is the option of obtaining the necessary locks with an explicit LOCK TABLE statement. This statement should be executed before other statements and should include the names of all the tables and the locks needed. After this statement, all the other statements in the transaction can be executed and the transaction committed. The commit will remove all the locks.
HyperSQL detects deadlocks before attempting to execute a statement. When a lock is released after the completion of the statement, the first transaction that is waiting for the lock is allowed to continue.
HyperSQL is fully multi threaded. It therefore allows different transactions to execute concurrently so long as they are not waiting to lock the same table for write.
In both LOCKS and MVLOCKS models, SQL routines (functions and procedures) and triggers obtain all the read and write locks at the beginning of the routine execution. SQL statements contained in the routine or trigger are all executed without deadlock as all the locks have already been obtained. At the end of execution of the routine or trigger, read locks are released if the session isolation level is READ COMMITTED.
In the MVCC model, there are no shared, read locks. Exclusive locks are used on individual rows, but their use is different. Transactions can read and modify the same table simultaneously, generally without waiting for other transactions. The SQL Standard isolation levels are used by the user's application, but these isolation levels are translated to the MVCC isolation levels READ CONSISTENCY or SNAPSHOT ISOLATION.
When transactions are running at READ COMMITTED level, no conflict will normally occur. If a transaction that runs at this level wants to modify a row that has been modified by another uncommitted transaction, then the engine puts the transaction in wait, until the other transaction has committed. The transaction then continues automatically. This isolation level is called READ CONSISTENCY.
Deadlock is completely avoided. In theory conflict is possible if
each transaction is waiting for a different row modified by the other
transaction. In this case, one of the transactions is immediately
terminated (rolled back) unless the setting has been changed with the
<set database transaction rollback on conflict
statement>
. When this setting is changed to FALSE, the
session that avoided executing the deadlock-causing statement returns an
error, but without rolling back the previous actions. This will cause
the other transaction to wait for the current transaction. The property
should not be changed unless the application can quickly perform an
alternative statement to continue or roll back the transaction. This
allows maximum flexibility and compatibility with other database engines
which do not roll back the transaction upon deadlock.
When transactions are running in REPEATABLE READ or SERIALIZABLE isolation levels, conflict is more likely to happen. There is no difference in operation between these two isolation levels. This isolation level is called SNAPSHOT ISOLATION.
In this mode, when the duration of two transactions overlaps, if
one of the transactions has modified a row and the second transaction
wants to modify the same row, the action of the second transaction will
fail. The engine will invalidate the second transaction and roll back
all its changes. If the setting is changed to false with the
<set database transaction rollback on conflict
statement>
, then the second transaction will just return an
error without rolling back. The application must perform an alternative
statement to continue or roll back the transaction.
In the MVCC model, READ UNCOMMITTED is promoted to READ COMMITTED, as the new architecture is based on multi-version rows for uncommitted data and more than one version may exist for some rows.
With MVCC, when a transaction only reads data, then it will go ahead and complete regardless of what other transactions may do. This does not depend on the transaction being read-only or the isolation modes.
The SQL Standard defines the isolation levels as modes of operation that avoid the three unwanted phenomena, "dirty read", "fuzzy read" and "phantom row". The "dirty read" phenomenon occurs when a session can read a row that has been changed by another session. The "fuzzy read" phenomenon occurs when a row that was read by a session is modified by another session, then the first session reads the row again. The "phantom row" phenomenon occurs when a session performs an operation that affects several rows, for example, counts the rows or modifies them using a search condition, then another session adds one or more rows that fulfil the same search condition, then the first session performs an operation that relies on the results of its last operation. According to the Standard, the SERIALIZABLE isolation level avoids all three phenomena and also ensures that all the changes performed during a transaction can be considered as a series of uninterrupted changes to the database without any other transaction changing the database at all for the duration of these actions. The changes made by other transactions are considered to occur before the SERIALIZABLE transaction starts, or after it ends. The READ COMMITTED level avoids "dirty read" only, while the REPEATABLE READ level avoids "dirty read" and "fuzzy read", but not "phantom row".
The Standard allows the engine to return a higher isolation level than requested by the application. HyperSQL promotes a READ UNCOMMITTED request to READ COMMITTED and promotes a REPEATABLE READ request to SERIALIZABLE.
The MVCC model is not covered directly by the Standard. Research has established that the READ CONSISTENCY level fulfils the requirements of (and is stronger than) the READ COMMITTED level. The SNAPSHOT ISOLATION level is stronger than the READ CONSISTENCY level. It avoids the three anomalies defined by the Standard, and is therefore stronger than the REPEATABLE READ level as defined by the Standard. When operating with the MVCC model, HyperSQL treats a REPEATABLE READ or SERIALIZABLE setting for a transaction as SNAPSHOT ISOLATION.
All modes can be used with as many simultaneous connections as required. The default 2PL model is fine for applications with a single connection, or applications that do not access the same tables heavily for writes. With multiple simultaneous connections, MVCC can be used for most applications. Both READ CONSISTENCY and SNAPSHOT ISOLATION levels are stronger than the corresponding READ COMMITTED level in the 2PL mode. Some applications require SERIALIZABLE transactions for at least some of their operations. For these applications, one of the 2PL modes can be used. It is possible to switch the concurrency model while the database is operational. Therefore, the model can be changed for the duration of some special operations, such as synchronization with another data source.
All concurrency models are very fast in operation. When data change operations are mainly on the same tables, the MVCC model may be faster, especially with multi-core processors.
There are a few SQL statements that must access a consistent state of the database during their executions. These statements, which include CHECKPOINT and BACKUP, put an exclusive lock on all the tables of the database when they start.
Some schema manipulation statements put an exclusive lock on one or more tables. For example changing the columns of a table locks the table exclusively.
In the MVCC model, all statements that need an exclusive lock on one or more tables, put an exclusive lock on the database catalog until they complete.
The effect of these exclusive locks is similar to the execution of data manipulation statements with write locks. The session that is about to execute the schema change statement waits until no other session is holding a lock on any of the objects. At this point it starts its operation and locks the objects to prevents any other session from accessing the locked objects. As soon as the operation is complete, the locks are all removed.
It was mentioned that there is no limit on the number of sessions that can access the tables and all sessions work simultaneously in multi threaded execution. However there are internal resources that are shared. Simultaneous access to these resources can reduce the overall efficiency of the system. MEMORY and TEXT tables do not share resources and do not block multi threaded access. With CACHED tables, each row change operation blocks the file and its cache momentarily until the operation is finished. This is done separately for each row, therefore a multi-row INSERT, UPDATE, or DELETE statement will allow other sessions to access the file during its execution. With CACHED tables, SELECT operations do not block each other, but selecting from different tables and different parts of a large table causes the row cache to be updated frequently and will reduce overall performance.
The new access pattern is the opposite of the access pattern of version 1.8.x. In the old version, even when 20 sessions are actively reading and writing, only a single session at a time performs an SQL statement completely, before the next session is allowed access. In the new version, while a session is performing a SELECT statement and reading rows of a CACHED table to build a result set, another session may perform an UPDATE statement that reads and writes rows of the same table. The two operations are performed without any conflict, but the row cache is updated more frequently than when one operation is performed after the other operation has finished.
As HyperSQL is multithreaded, you can view the current sessions
and their state from any admin session. The
INFORMATION_SCHEMA.SYSTEM_SESSIONS
table contains the
list of open sessions, their unique ids and the statement currently
executed or waiting to be executed by each session. For each session, it
displays the list of sessions that are waiting for it to commit, or the
session that this session is waiting for.
ALTER SESSION
alter session statement
<alter session statement> ::= ALTER SESSION
<numeric literal> { CLOSE | RELEASE }
<alter current session statement> ::= ALTER
SESSION RESET { ALL | RESULT SETS | TABLE DATA }
The <alter session statement> is used by an administrator to close another session or to release the transaction in another session. When a session is released, its current transaction is terminated with a failure. The session remains open. This statement is different from the other statements discussed in this chapter as it is not used for changing the settings of the current session.
The session ID is used as a <numeric
literal>
in this statement. The administrator can use the
INFORMATION_SCHEMA.SYSTEM_SESSIONS
table to find the
session IDs of other sessions.
The <alter current session statement> is used to clear and reset different states of the current session. When ALL is specified, the current transaction is rolled back, the session settings such as time zone, current schema etc. are restored to their original state at the time the session was opened and all open result sets are closed and temporary tables cleared. When RESULT SETS is specified, all currently open result sets are closed and the resources are released. When TABLE DATA is specified, the data in all temporary tables is cleared.
SET AUTOCOMMIT
set autocommit command
<set autocommit statement> ::= SET AUTOCOMMIT {
TRUE | FALSE }
When an SQL session is started by creating a JDBC connection, it
is in AUTOCOMMIT mode. In this mode, after each SQL statement a COMMIT is
performed automatically. This statement changes the mode. It is equivalent
to using the setAutoCommit( boolean autoCommit)
method of the JDBC Connection
object.
START TRANSACTION
start transaction statement
<start transaction statement> ::= START
TRANSACTION [ <transaction characteristics> ]
Start an SQL transaction and set its characteristics. All transactional SQL statements start a transaction automatically, therefore using this statement is not necessary. If the statement is called in the middle of a transaction, an exception is thrown.
SET TRANSACTION
set next transaction characteristics
<set transaction statement> ::= SET [ LOCAL ]
TRANSACTION <transaction characteristics>
Set the characteristics of the next transaction in the current session. This statement has an effect only on the next transactions and has no effect on the future transactions after the next.
transaction characteristics
transaction characteristics
<transaction characteristics> ::= [
<transaction mode> [ { <comma> <transaction mode> }... ]
]
<transaction mode> ::= <isolation level> |
<transaction access mode> | <diagnostics
size>
<transaction access mode> ::= READ ONLY | READ
WRITE
<isolation level> ::= ISOLATION LEVEL <level of
isolation>
<level of isolation> ::= READ UNCOMMITTED | READ
COMMITTED | REPEATABLE READ | SERIALIZABLE
<diagnostics size> ::= DIAGNOSTICS SIZE <number
of conditions>
<number of conditions> ::= <simple value
specification>
Specify transaction characteristics.
Example 3.3.
]
If the statement is issued during a transaction, it applies to the rest of the current transaction. If the statement is issued when a transaction is not active then it applies only to the next transaction in the current session. HyperSQL does not yet support this feature.
LOCK TABLE
lock table statement
<lock table statement> ::= LOCK TABLE <table
name> { READ | WRITE} [, <table name> { READ | WRITE}
...]}
In some circumstances, where multiple simultaneous transactions are in progress, it may be necessary to ensure a transaction consisting of several statements is completed, without being terminated due to possible deadlock. When this statement is executed, it waits until it can obtain all the listed locks, then returns. If obtaining the locks would result in a deadlock an error is raised. The SQL statements following this statements use the locks already obtained (and obtain new locks if necessary) and can proceed without waiting. All the locks are released when a COMMIT or ROLLBACK statement is issued.
When the isolation level of a session is READ COMMITTED, read locks are released immediately after the execution of the statement, therefore you should use only WRITE locks in this mode. Alternatively, you can switch to the SERIALIZABLE isolation mode before locking the tables for the specific transaction that needs to finish consistently and without a deadlock. It is best to execute this statement at the beginning of the transaction with the complete list of required read and write locks.
Currently, this command does not have any effect when the database transaction control model is MVCC.
SAVEPOINT
savepoint statement
<savepoint statement> ::= SAVEPOINT <savepoint
specifier>
<savepoint specifier> ::= <savepoint
name>
Establish a savepoint. This command is used during an SQL transaction. It establishes a milestone for the current transaction. The SAVEPOINT can be used at a later point in the transaction to rollback the transaction to the milestone.
RELEASE SAVEPOINT
release savepoint statement
<release savepoint statement> ::= RELEASE
SAVEPOINT <savepoint specifier>
Destroy a savepoint. This command is rarely used as it is not very useful. It removes a SAVEPOINT that has already been defined.
COMMIT
commit statement
<commit statement> ::= COMMIT [ WORK ] [ AND [ NO
] CHAIN ]
Terminate the current SQL-transaction with commit. This make all the changes to the database permanent.
ROLLBACK
rollback statement
<rollback statement> ::= ROLLBACK [ WORK ] [ AND [
NO ] CHAIN ]
Rollback the current SQL transaction and terminate it. The statement rolls back all the actions performed during the transaction. If NO CHAIN is specified, a new SQL transaction is started just after the rollback. The new transaction inherits the properties of the old transaction.
ROLLBACK TO SAVEPOINT
rollback statement
<rollback statement> ::= ROLLBACK [ WORK ] TO
SAVEPOINT <savepoint specifier>
Rollback part of the current SQL transaction and continue the
transaction. The statement rolls back all the actions performed after the
specified SAVEPOINT was created. The same effect can be achieved with the
rollback( Savepoint savepoint)
method of the JDBC
Connection
object.
Example 3.5. Rollback
-- perform some inserts, deletes, etc. SAVEPOINT A -- perform some inserts, deletes, selects etc. ROLLBACK WORK TO SAVEPOINT A -- all the work after the declaration of SAVEPOINT A is rolled back
DISCONNECT
disconnect statement
<disconnect statement> ::=
DISCONNECT
Terminate the current SQL session. Closing a JDBC connection has the same effect as this command.
SET SESSION CHARACTERISTICS
set session characteristics statement
<set session characteristics statement> ::= SET
SESSION CHARACTERISTICS AS <session characteristic
list>
<session characteristic list> ::= <session
characteristic> [ { <comma> <session characteristic> }...
]
<session characteristic> ::= <session
transaction characteristics>
<session transaction characteristics> ::=
TRANSACTION <transaction mode> [ { <comma> <transaction
mode> }... ]
Set one or more characteristics for the current SQL-session. This command is used to set the transaction mode for the session. This endures for all transactions until the session is closed or the next use of this command. The current read-only mode can be accessed with the ISREADONLY() function.
Example 3.6.  This statement changes the current user. The user that executes this command must have the CHANGE_AUTHORIZATION role, or the DBA role. After this statement is executed, all SQL statements are executed with the privileges of the new user. The current authorisation can be accessed with the CURRENT_USER and SESSION_USER functions.
Example 3.7. Setting Session Authorization
SET SESSION AUTHORIZATION 'FELIX' SET SESSION AUTHORIZATION SESSION_USER
SET ROLE
set role statement
<set role statement> ::= SET ROLE <role
specification>
<role specification> ::= <value
specification> | NONE
Set the SQL-session role name and the current role name for the current SQL-session context. The user that executes this command must have the specified role. If NONE is specified, then the previous CURRENT_ROLE is eliminated. The effect of this lasts for the lifetime of the session. The current role can be accessed with the CURRENT_ROLE function.
SET TIME ZONE
set local time zone statement
<set local time zone statement> ::= SET TIME ZONE
<set time zone value>
<set time zone value> ::= <interval value
expression> | LOCAL