HyperSQL User Guide
HyperSQL Database Engine (HSQLDB) 2.2
Edited by , Blaine Simpson, and Fred Toussi
HyperSQL User Guide: HyperSQL Database Engine (HSQLDB) 2.2
by , Blaine Simpson, and Fred Toussi
$Revision: 4864 $
Publication date 2012-08-06 00:12:50+0100
Copyright 2002-2011 The HSQL Development Group. Permission is granted to distribute this document without any alteration under the terms of
the HSQLDB license. You are not allowed to distribute or display this document on the web in an altered form.
iii
Table of Contents
Preface ........................................................................................................................................ xiii
Available formats for this document ......................................................................................... xiii
1. Running and Using HyperSQL ....................................................................................................... 1
The HSQLDB Jar .................................................................................................................... 1
Running Database Access Tools ................................................................................................. 1
A HyperSQL Database .............................................................................................................. 2
In-Process Access to Database Catalogs ....................................................................................... 3
Server Modes .......................................................................................................................... 3
HyperSQL HSQL Server ................................................................................................... 4
HyperSQL HTTP Server ................................................................................................... 4
HyperSQL HTTP Servlet ................................................................................................... 4
Connecting to a Database Server ......................................................................................... 4
Security Considerations ..................................................................................................... 5
Using Multiple Databases .................................................................................................. 5
Accessing the Data ................................................................................................................... 5
Closing the Database ................................................................................................................ 6
Creating a New Database .......................................................................................................... 7
2. SQL Language ............................................................................................................................. 8
Standards Support .................................................................................................................... 8
SQL Data and Tables ............................................................................................................... 9
Temporary Tables ............................................................................................................ 9
Persistent Tables .............................................................................................................. 9
Lob Data ...................................................................................................................... 10
Short Guide to Data Types ....................................................................................................... 10
Data Types and Operations ...................................................................................................... 11
Numeric Types .............................................................................................................. 11
Boolean Type ................................................................................................................ 13
Character String Types .................................................................................................... 14
Binary String Types ........................................................................................................ 15
Bit String Types ............................................................................................................. 15
Storage and Handling of Java Objects ................................................................................ 16
Type Length, Precision and Scale ...................................................................................... 16
Datetime types ....................................................................................................................... 17
Interval Types ........................................................................................................................ 20
Arrays .................................................................................................................................. 23
Array Definition ............................................................................................................. 23
Array Reference ............................................................................................................. 24
Array Operations ............................................................................................................ 25
Indexes and Query Speed ......................................................................................................... 26
Query Processing and Optimisation ........................................................................................... 27
Indexes and Conditions ................................................................................................... 27
Indexes and Operations ................................................................................................... 28
Indexes and ORDER BY, OFFSET and LIMIT .................................................................... 28
3. Sessions and Transactions ............................................................................................................ 30
Overview .............................................................................................................................. 30
Session Attributes and Variables ............................................................................................... 30
Session Attributes ........................................................................................................... 31
Session Variables ........................................................................................................... 31
Session Tables ............................................................................................................... 31
Transactions and Concurrency Control ....................................................................................... 32
Two Phase Locking ........................................................................................................ 32
HyperSQL User Guide
iv
Two Phase Locking with Snapshot Isolation ........................................................................ 33
Lock Contention in 2PL .................................................................................................. 33
Locks in SQL Routines and Triggers ................................................................................. 33
MVCC ......................................................................................................................... 33
Choosing the Transaction Model ....................................................................................... 34
Schema and Database Change ........................................................................................... 35
Simultaneous Access to Tables ......................................................................................... 35
Viewing Sessions ........................................................................................................... 35
Session and Transaction Control Statements ................................................................................ 35
4. Schemas and Database Objects ...................................................................................................... 42
Overview .............................................................................................................................. 42
Schemas and Schema Objects ................................................................................................... 42
Names and References .................................................................................................... 43
Character Sets ................................................................................................................ 43
Collations ...................................................................................................................... 44
Distinct Types ................................................................................................................ 44
Domains ....................................................................................................................... 44
Number Sequences ......................................................................................................... 44
Tables .......................................................................................................................... 46
Views ........................................................................................................................... 47
Constraints .................................................................................................................... 47
Assertions ..................................................................................................................... 48
Triggers ........................................................................................................................ 48
Routines ....................................................................................................................... 49
Indexes ......................................................................................................................... 49
Statements for Schema Definition and Manipulation ..................................................................... 49
Common Elements and Statements .................................................................................... 49
Renaming Objects .......................................................................................................... 51
Commenting Objects ....................................................................................................... 51
Schema Creation ............................................................................................................ 51
Table Creation ............................................................................................................... 52
Table Manipulation ......................................................................................................... 58
View Creation and Manipulation ....................................................................................... 62
Domain Creation and Manipulation .................................................................................... 63
Trigger Creation ............................................................................................................. 64
Routine Creation ............................................................................................................ 66
Sequence Creation .......................................................................................................... 68
SQL Procedure Statement ................................................................................................ 70
Other Schema Object Creation .......................................................................................... 70
The Information Schema .......................................................................................................... 73
Predefined Character Sets, Collations and Domains ............................................................... 74
Views in INFORMATION SCHEMA ................................................................................ 74
Visibility of Information .................................................................................................. 74
Name Information .......................................................................................................... 74
Data Type Information .................................................................................................... 75
Product Information ........................................................................................................ 75
Operations Information .................................................................................................... 75
SQL Standard Views ....................................................................................................... 75
5. Text Tables ................................................................................................................................ 82
Overview .............................................................................................................................. 82
The Implementation ................................................................................................................ 82
Definition of Tables ........................................................................................................ 82
Scope and Reassignment .................................................................................................. 82
Null Values in Columns of Text Tables .............................................................................. 83
HyperSQL User Guide
v
Configuration ................................................................................................................. 83
Disconnecting Text Tables ............................................................................................... 84
Text File Usage ..................................................................................................................... 85
Text File Global Properties ...................................................................................................... 85
Transactions .......................................................................................................................... 86
6. Access Control ........................................................................................................................... 87
Overview .............................................................................................................................. 87
Authorizations and Access Control ............................................................................................ 87
Built-In Roles and Users .................................................................................................. 88
Listing Users and Roles ................................................................................................... 89
Access Rights ................................................................................................................ 89
Statements for Authorization and Access Control ......................................................................... 90
7. Data Access and Change .............................................................................................................. 95
Overview .............................................................................................................................. 95
Cursors And Result Sets .......................................................................................................... 95
Columns and Rows ......................................................................................................... 95
Navigation ..................................................................................................................... 95
Updatability ................................................................................................................... 96
Sensitivity ..................................................................................................................... 97
Holdability .................................................................................................................... 97
Autocommit ................................................................................................................... 97
JDBC Overview ............................................................................................................. 97
JDBC Parameters ........................................................................................................... 98
JDBC and Data Change Statements ................................................................................... 98
JDBC Callable Statement ................................................................................................. 98
JDBC Returned Values .................................................................................................... 99
Cursor Declaration .......................................................................................................... 99
Syntax Elements ..................................................................................................................... 99
Literals ......................................................................................................................... 99
References, etc. ............................................................................................................ 103
Value Expression .......................................................................................................... 104
Predicates .................................................................................................................... 111
Aggregate Functions ...................................................................................................... 116
Other Syntax Elements .................................................................................................. 117
Data Access Statements ......................................................................................................... 119
Select Statement ........................................................................................................... 120
Table .......................................................................................................................... 120
Subquery ..................................................................................................................... 120
Query Specification ....................................................................................................... 121
Table Expression .......................................................................................................... 121
Table Primary .............................................................................................................. 122
Joined Table ................................................................................................................ 124
Selection ..................................................................................................................... 126
Projection .................................................................................................................... 126
Computed Columns ....................................................................................................... 127
Naming ....................................................................................................................... 127
Grouping Operations ..................................................................................................... 128
Aggregation ................................................................................................................. 128
Set Operations .............................................................................................................. 128
With Clause and Recursive Queries .................................................................................. 128
Query Expression .......................................................................................................... 129
Ordering ...................................................................................................................... 130
Slicing ........................................................................................................................ 131
Data Change Statements ......................................................................................................... 131
