Hsqldb User Guide

The HSQLDB Development Group

Edited by

Blaine Simpson

Fred Toussi

$Date: 2007/08/28 12:13:28 $

Table of Contents

Introduction

Available formats for this document

1. Running and Using Hsqldb

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
Disconnecting Text Tables

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, Coverage, Changes in Behavior

Platforms and SqlTool versions covered
Functional Changes

The Bare Minimum

Non-displayable Types
Desktop shortcuts
Loading sample data

RC File Authentication Setup

Using Inline RC Authentication

Using the current version of SqlTool with an older HSQLDB distribution.

Interactive Usage

Command Types

Command Types

Special Commands

Edit Buffer / History Commands

PL Commands

? Variable
Storing and retrieving binary files
Command 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 Edit 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

Delimiter-Separated-Value Imports and Exports

Simple DSV exports and imports using default settings
Specifying queries and options

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 INITIAL SCHEMA
SET LOGSIZE
SET MAXROWS
SET PASSWORD
SET PROPERTY
SET READONLY
SET REFERENTIAL INTEGRITY
SET SCHEMA
SET SCRIPTFORMAT
SET TABLE INDEX
SET TABLE READONLY
SET TABLE SOURCE
SET WRITE DELAY
SHUTDOWN
UPDATE

Schema object naming

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. Interactive Raw Mode example
8.13. PL/SQL Example
8.14. DSV Export Example
8.15. DSV Import Example
8.16. DSV Export of an Arbitrary SELECT Statement
8.17. Sample DSV headerswitch settings
8.18. DSV targettable setting
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


		Introduction