Hsqldb User Guide
   Next

Hsqldb User Guide

The HSQLDB Development Group

Edited by

Blaine Simpson

Fred Toussi

Copyright 2002-2005 HSQLDB Development Group. Permission is granted to distribute this document without any alteration under the terms of the HSQLDB license.

$Date: 2005/07/25 23:02:52 $

Table of Contents

Introduction
Available formats for this document
1. Running and Using Hsqldb
Introduction
Running Tools
Running Hsqldb
Server Modes
Hsqldb Server
Hsqldb Web Server
Hsqldb Servlet
In-Process (Standalone) Mode
Memory-Only Databases
General
Closing the Database
Using Multiple Databases in One JVM
Creating a New Database
Using the Database Engine
Different Types of Tables
Constraints and Indexes
SQL Support
JDBC Support
2. SQL Issues
Purpose
SQL Standard Support
Constraints and Indexes
Primary Key Constraints
Unique Constraints
Unique Indexes
FOREIGN KEYS
Indexes and Query Speed
Where Condition or Join
Subqueries and Joins
Types and Arithmetic Operations
Integral Types
Other Numeric Types
Bit and Boolean Types
Storage and Handling of Java Objects
Type Size, Precision and Scale
Sequences and Identity
Identity Auto-Increment Columns
Sequences
Issues with Transactions
New Features and Changes
3. UNIX Quick Start
Purpose
Installation
Setting up Database Instance and Server
Accessing your Database
Create additional Accounts
Shutdown
Running Hsqldb as a System Daemon
Portability of hsqldb init script
Init script Setup Procedure
Troubleshooting the Init Script
4. Advanced Topics
Purpose
Connections
Connection properties
Properties Files
Server and Web Server Properties
Starting a Server from your application
Individual Database Properties
SQL Commands for Database Properties
5. Deployment Issues
Purpose
Mode of Operation and Tables
Mode of Operation
Tables
Large Objects
Deployment context
Memory and Disk Use
Cache Memory Allocation
Managing Database Connections
Upgrading Databases
Upgrading Using the SCRIPT Command
Manual Changes to the .script File
Backing Up Databases
6. Text Tables
The Implementation
Definition of Tables
Scope and Reassignment
Null Values in Columns of Text Tables
Configuration
Text File Issues
Text File Global Properties
Importing from a Text Table file
7. TLS
Requirements
Encrypting your JDBC connection
Client-Side
Server-Side
JSSE
Making a Private-key Keystore
CA-Signed Cert
Non-CA-Signed Cert
Automatic Server or WebServer startup on UNIX
8. SqlTool
Purpose
Recent changes
The Bare Minimum
Non-displayable Types
Desktop shortcuts
Loading sample data
RC File Authentication Setup
Using the current version of SqlTool with an older HSQLDB distribution.
Interactive
Command Types
Special Commands
Buffer Commands
PL Commands
Storing and retrieving binary files
SQL History
Shell scripting and command-line piping
Emulating Non-Interactive mode
Non-Interactive
Giving SQL on the Command Line
SQL Files
Piping and shell scripting
Optimally Compatible SQL Files
Comments
Special Commands and Buffer Commands in SQL Files
Automation
Getting Interactive Functionality with SQL Files
Character Encoding
Generating Text or HTML Reports
SqlTool Procedural Language
Variables
PL Aliases
Logical Expressions
Flow Control
Chunking
Why?
How?
Raw Mode
PL/SQL
Using hsqltool.jar and hsqldbutil.jar
Unit Testing SqlTool
9. SQL Syntax
Notational Conventions Used in this Chapter
SQL Commands
ALTER INDEX
ALTER SEQUENCE
ALTER SCHEMA
ALTER TABLE
ALTER USER
CALL
CHECKPOINT
COMMIT
CONNECT
CREATE ALIAS
CREATE INDEX
CREATE ROLE
CREATE SCHEMA
CREATE SEQUENCE
CREATE TABLE
CREATE TRIGGER
CREATE USER
CREATE VIEW
DELETE
DISCONNECT
DROP INDEX
DROP ROLE
DROP SEQUENCE
DROP SCHEMA
DROP TABLE
DROP TRIGGER
DROP USER
DROP VIEW
EXPLAIN PLAN
GRANT
INSERT
REVOKE
ROLLBACK
SAVEPOINT
SCRIPT
SELECT
SET AUTOCOMMIT
SET DATABASE COLLATION
SET CHECKPOINT DEFRAG
SET IGNORECASE
SET LOGSIZE
SET PASSWORD
SET PROPERTY
SET REFERENTIAL INTEGRITY
SET SCHEMA
SET SCRIPTFORMAT
SET TABLE INDEX
SET TABLE READONLY
SET TABLE SOURCE
SET WRITE DELAY
SHUTDOWN
UPDATE
Data Types
SQL Comments
Stored Procedures / Functions
Built-in Functions and Stored Procedures
SQL Expression
A. Building HSQLDB
Purpose
Building with Ant
Obtaining Ant
Building Hsqldb with Ant
Building with DOS Batch Files
Hsqldb CodeSwitcher
Building documentation
B. First JDBC Client Example
C. Hsqldb Database Files and Recovery
States
Procedures
Clean Shutdown
Startup
Repair
D. Running Hsqldb with OpenOffice.org 1.1.x
Introduction
Installing
Setting up OpenOffice.org
On Windows
On Linux
E. Hsqldb Test Utility
F. Database Manager
Brief Introduction
Auto tree-update
Automatic Connection
RC File
Using the current DatabaseManagers with an older HSQLDB distribution.
DatabaseManagerSwing as an Applet
G. Transfer Tool
Brief Introduction

List of Tables

1. Alternate formats of this document
4.1. Hsqldb URL Components
4.2. Connection Properties
4.3. Hsqldb Server Properties Files
4.4. Property File Properties
4.5. Server Property File Properties
4.6. WebServer Property File Properties
4.7. Database-specific Property File Properties
4.8. SQL command properties
9.1. Data Types

List of Examples

1.1. Java code to connect to the local Server above
2.1. Column values which satisfy a 2-column UNIQUE constraint
2.2. Query comparison
2.3. Numbering returned rows of a SELECT in sequential order
3.1. server.properties fragment
3.2. example sqltool.rc stanza
7.1. Exporting certificate from the server's keystore
7.2. Adding a certificate to the client keystore
7.3. Specifying your own trust store to a JDBC client
7.4. Running an Hsqldb server with TLS encryption
7.5. Getting a pem-style private key into a JKS keystore
8.1. Sample RC File
8.2. Defining and using a PL alias (PL variable)
8.3. Inserting binary data into database from a file
8.4. Downloading binary data from database to a file
8.5. Piping input into SqlTool
8.6. Valid comment example
8.7. Invalid comment example
8.8. Simple SQL file using PL
8.9. SQL File showing use of most PL features
8.10. Single-line chunking example
8.11. Multi-line chunking example
8.12. Raw Mode example
8.13. PL/SQL Example
A.1. Buiding the standard Hsqldb jar file with Ant
A.2. Example source code before CodeSwitcher is run
A.3. CodeSwitcher command line invocation
A.4. Source code after CodeSwitcher processing
A.5. Building HTML User Guides
A.6. Building User Guides in all formats
B.1. JDBC Client source code example
   Next
   Introduction