|
|
HSQLDB example source code file (advancedtopics.xml)
This example HSQLDB source code file (advancedtopics.xml) is included in the DevDaily.com
"Java Source Code
Warehouse" project. The intent of this project is to help you "Learn Java by Example" TM.
The HSQLDB advancedtopics.xml source code
<?xml version="1.0" encoding="UTF-8"?>
<!-- $Id: advancedtopics.xml,v 1.34 2007/03/24 11:39:08 unsaved Exp $ -->
<chapter id="advanced-chapter">
<title id="advanced-title">Advanced Topics
<chapterinfo>
<authorgroup>
<author>
<firstname>Fred
<surname>Toussi
<affiliation>
<orgname>HSQLDB Development Group
</affiliation>
<email>ft@cluedup.com
</author>
</authorgroup>
<edition>$Revision: 1.34 $
<pubdate>$Date: 2007/03/24 11:39:08 $
<keywordset>
<keyword>Hsqldb
<keyword>Advanced
</keywordset>
<legalnotice>
<para>Copyright 2002-2005 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 HSQLDB
Development Group to distribute this document with or without
alterations under the terms of the HSQLDB license.</para>
</legalnotice>
</chapterinfo>
<section>
<title>Purpose
<para>Many questions repeatedly asked in Forums and mailing lists are
answered in this guide. If you want to use HSQLDB with your application,
you should read this guide. This document covers system related issues.
For issues related to SQL see the <link endterm="sql_issues-title"
linkend="sql_issues-chapter" /> chapter.</para>
</section>
<section>
<title>Connections
<para>The normal method of accessing an HSQLDB database is via the JDBC
Connection interface. An introduction to different methods of providing
database services and accessing them can be found in the <link
endterm="sql_issues-title" linkend="sql_issues-chapter" /> chapter.
Details and examples of how to connect via JDBC are provided in our <ulink
url="../src/org/hsqldb/jdbc/jdbcConnection.html">JavaDoc for
<literal>jdbcConnection.
<para>Version 1.7.2 introduced a uniform method of distinguishing between
different types of connection, alongside new capabilities to provide
access to multiple databases. The common driver identifier is
<literal>jdbc:hsqldb: followed by a protocol identifier
(<literal>mem: file: res: hsql: http: hsqls: https:) then
followed by host and port identifiers in the case of servers, then
followed by database identifier.</para>
<table frame="all" pgwide="1" tocentry="1">
<title>Hsqldb URL Components
<tgroup align="left" cols="3">
<colspec colname="c1" />
<colspec colname="c2" />
<colspec colname="c3" />
<thead>
<row>
<entry>Driver and Protocol
<entry>Host and Port
<entry>Database
</row>
</thead>
<tbody valign="top">
<row>
<entry>
<simplelist type="vert">
<member>
<literal>jdbc:hsqldb:mem:
</member>
</simplelist>
</entry>
<entry>not available
<entry>
<simplelist type="vert">
<member>
<literal>accounts
</member>
</simplelist>
</entry>
</row>
<row>
<entry nameend="c3" namest="c1">
<para>Lowercase, single-word identifier creates the in-memory
database when the first connection is made. Subsequent use of
the same Connection URL connects to the existing DB.</para>
<para>The old form for the URL, jdbc:hsqldb:.
creates or connects to the same database as the new form for the
URL, <literal>jdbc:hsqldb:mem:.
</entry>
</row>
<row>
<entry>
<simplelist type="vert">
<member>
<literal>jdbc:hsqldb:file:
</member>
</simplelist>
</entry>
<entry>not available
<entry>
<simplelist type="vert">
<member>
<filename>mydb
</member>
<member>
<filename>/opt/db/accounts
</member>
<member>
<filename>C:/data/mydb
</member>
</simplelist>
</entry>
</row>
<row>
<entry nameend="c3" namest="c1">
<para>The file path specifies the database file. In the above
examples the first one refers to a set of mydb.* files in the
directory where the <literal>javacommand for running
the application was issued. The second and third examples refer
to absolute paths on the host machine.</para>
</entry>
</row>
<row>
<entry>
<simplelist type="vert">
<member>
<literal>jdbc:hsqldb:res:
</member>
</simplelist>
</entry>
<entry>not available
<entry>
<simplelist type="vert">
<member>
<filename>/adirectory/dbname
</member>
</simplelist>
</entry>
</row>
<row>
<entry nameend="c3" namest="c1">Database files can be loaded from
one of the jars specified as part of the <literal>Java
command the same way as resource files are accessed in Java
programs. The <literal>/adirectory above stands for a
directory in one of the jars.</entry>
</row>
<row>
<entry>
<simplelist type="vert">
<member>
<literal>jdbc:hsqldb:hsql:
</member>
<member>
<literal>jdbc:hsqldb:hsqls:
</member>
<member>
<literal>jdbc:hsqldb:http:
</member>
<member>
<literal>jdbc:hsqldb:https:
</member>
</simplelist>
</entry>
<entry>
<simplelist type="vert">
<member>
<literal>//localhost
</member>
<member>
<literal>//192.0.0.10:9500
</member>
<member>
<literal>//dbserver.somedomain.com
</member>
</simplelist>
</entry>
<entry>
<simplelist type="vert">
<member>
<literal>/an_alias
</member>
<member>
<literal>/enrollments
</member>
<member>
<literal>/quickdb
</member>
</simplelist>
</entry>
</row>
<row>
<entry nameend="c3" namest="c1">
<para>The host and port specify the IP address or host name of
the server and an optional port number. The database to connect
to is specified by an alias. This alias is a lowercase string
defined in the <filename>server.properties file to
refer to an actual database on the file system of the server or
a transient, in-memory database on the server. The following
example lines in <filename>server.properties or
<filename>webserver.properties define the database
aliases listed above and accessible to clients to refer to
different file and in-memory databases.</para>
<programlisting>
database.0=file:/opt/db/accounts
dbname.0=an_alias
database.1=file:/opt/db/mydb
dbname.1=enrollments
database.2=mem:adatabase
dbname.2=quickdb</programlisting>
<para>The old form for the server URL, e.g.,
<literal>jdbc:hsqldb:hsql//localhost connects to the
same database as the new form for the URL,
<literal>jdbc:hsqldb:hsql//localhost/ where the alias
is a zero length string. In the example below, the database
files <literal>lists.* in the
<literal>/home/dbmaster/ directory are associated with
the empty alias:</para>
<programlisting>
database.3=/home/dbmaster/lists
dbname.3=</programlisting>
</entry>
</row>
</tbody>
</tgroup>
</table>
<section>
<title>Connection properties
<para>Each new JDBC Connection to a database can specify connection
properties. The properties <property>user and
<property>password are always required. In 1.8.0 the
following optional properties can also be used.</para>
<para>Connection properties are specified either by establishing the
connection via the:</para>
<programlisting>
DriverManager.getConnection (String url, Properties info);</programlisting>
<para>method call, or the property can be appended to the full
Connection URL.</para>
<table frame="all" pgwide="1" tocentry="1">
<title>Connection Properties
<tgroup align="left" cols="3">
<colspec colname="c1" />
<colspec colname="c2" />
<colspec colname="c3" />
<tbody valign="top">
<row>
<entry>
<property>get_column_name
</entry>
<entry>
<literal>true
</entry>
<entry>column name in ResultSet
</row>
<row>
<entry nameend="c3" namest="c1">
<para>This property is used for compatibility with other JDBC
driver implementations. When true (the default),
<literal>ResultSet.getColumnName(int c) returns the
underlying column name</para>
<para>When false, the above method returns the same value as
<literal>ResultSet.getColumnLabel(int column)
Example below:</para>
<programlisting>
jdbc:hsqldb:hsql://localhost/enrollments;get_column_name=false
</programlisting>
<para>When a ResultSet is used inside a user-defined stored
procedure, the default, true, is always used for this
property.</para>
</entry>
</row>
<row>
<entry>
<property>ifexists
</entry>
<entry>
<literal>false
</entry>
<entry>connect only if database already exists
</row>
<row>
<entry nameend="c3" namest="c1">
<para>Has an effect only with mem: and
<literal>file: database. When true, will not create
a new database if one does not already exist for the
URL.</para>
<para>When false (the default), a new mem:
or <literal>file: database will be created if it
does not exist.</para>
<para>Setting the property to true is useful when
troubleshooting as no database is created if the URL is
malformed. Example below:</para>
<programlisting>
jdbc:hsqldb:file:enrollments;ifexists=true</programlisting>
</entry>
</row>
<row>
<entry>
<property>shutdown
</entry>
<entry>
<literal>false
</entry>
<entry>shut down the database when the last connection is
closed</entry>
</row>
<row>
<entry nameend="c3" namest="c1">
<para>This mimics the behaviour of 1.7.1 and older versions.
When the last connection to a database is closed, the database
is automatically shut down. The property takes effect only
when the first connection is made to the database. This means
the connection that opens the database. It has no effect if
used with subsequent, simultaneous connections.</para>
<para>This command has two uses. One is for test suites, where
connections to the database are made from one JVM context,
immediately followed by another context. The other use is for
applications where it is not easy to configure the environment
to shutdown the database. Examples reported by users include
web application servers, where the closing of the last
connection conisides with the web app being shut down.</para>
</entry>
</row>
</tbody>
</tgroup>
</table>
<para>In addition, when a connection to an in-process database creates a
new database, or opens an existing database (i.e. it is the first
connection made to the database by the application), all the
user-defined database properties can be specified as URL properties.
This can be used to specify properties to enforce more strict SQL
adherence, or to change cache_scale or similar properties before the
database files are created. However, for new databases, it is
recommended to use the SET PROPERTY command for such settings.</para>
</section>
</section>
<section>
<title>Properties Files
<para>HSQLDB relies on a set of properties files for different settings.
Since 1.7.0 property naming has been streamlined and a number of new
properties have been introduced.</para>
<para>In all properties files, values are case-sensitive. All values apart
from names of files or pages are required in lowercase (e.g.
<property>server.silent=FALSE will have no
effect, but <property>server.silent=false
will work).</para>
<para>The properties files and the settings stored in them are as
follows:</para>
<table frame="all" pgwide="1" tocentry="1">
<title>Hsqldb Server Properties Files
<tgroup align="left" cols="3">
<thead>
<row>
<entry>File Name
<entry>Location
<entry>Function
</row>
</thead>
<tbody valign="top">
<row>
<entry>
<filename>server.properties
</entry>
<entry>the directory where the command to run the
<classname>Server class is issued
<entry>settings for running HSQLDB as a database server
communicating with the HSQL protocol</entry>
</row>
<row>
<entry>
<filename>webserver.properties
</entry>
<entry>the directory where the command to run the
<classname>WebServer class is issued
<entry>settings for running HSQLDB as a database server
communicating with the HTTP protocol</entry>
</row>
<row>
<entry>
<filename><dbname>.properties
</entry>
<entry>the directory where all the files for a database are
located</entry>
<entry>settings for each particular database
</row>
</tbody>
</tgroup>
</table>
<para>Properties files for running the servers are not created
automatically. You should create your own files that contain
<property>server.property=value pairs for
each property.</para>
<para>The properties file for each database is generated by the database
engine. This file can be edited after closing the database. In 1.8.0, most
of these properties can be changed via SQL commands.</para>
<section>
<title>Server and Web Server Properties
<para>In both server.properties and
<filename>webserver.properties files, supported values and
their defaults are as follows:</para>
<table frame="all" pgwide="1" tocentry="1">
<title>Property File Properties
<tgroup align="left" cols="3">
<thead>
<row>
<entry>Value
<entry>Default
<entry>Description
</row>
</thead>
<tbody valign="top">
<row>
<entry>
<property>server.database.0
</entry>
<entry>
<literal>test
</entry>
<entry>the path and file name of the first database file to
use</entry>
</row>
<row>
<entry>
<property>server.dbname.0
</entry>
<entry>
<literal>""
</entry>
<entry>lowercase server alias for the first database
file</entry>
</row>
<row>
<entry>
<property>server.urlid.0
</entry>
<entry>
<literal>NONE
</entry>
<entry>SqlTool urlid used by UNIX init script. (This property is
not used if your are running Server/Webserver on a platform
other than UNIX, or of you are not using our UNIX init
script).</entry>
</row>
<row>
<entry>
<property>server.silent
</entry>
<entry>
<literal>true
</entry>
<entry>no extensive messages displayed on console
</row>
<row>
<entry>
<property>server.trace
</entry>
<entry>
<literal>false
</entry>
<entry>JDBC trace messages displayed on console
</row>
</tbody>
</tgroup>
</table>
<para>In 1.8.0, each server can serve up to 10 different databases
simultaneously. The <property>server.database.0 property
defines the filename / path whereas the
<property>server.dbname.0 defines the lowercase alias used by
clients to connect to that database. The digit 0 is incremented for the
second database and so on. Values for the
<property>server.database.{0-9} property can use the
<literal>mem:, file: or
<literal>res: prefixes and properties as discussed above under
CONNECTIONS. For example, <informalexample>
<programlisting>
database.0=mem:temp;sql.enforce_strict_size=true;</programlisting>
</informalexample>
<para>Values specific to server.properties
are:</para>
<table frame="all" pgwide="1" tocentry="1">
<title>Server Property File Properties
<tgroup align="left" cols="3">
<thead>
<row>
<entry>Value
<entry>Default
<entry>Description
</row>
</thead>
<tbody valign="top">
<row>
<entry>
<property>server.port
</entry>
<entry>
<literal>9001 (normal) or 554 (if TLS encrypted)
</entry>
<entry>TCP/IP port used for talking to clients. All databases
are served on the same port.</entry>
</row>
<row>
<entry>
<property>server.no_system_exit
</entry>
<entry>
<literal>true
</entry>
<entry>no System.exit() call when the
database is closed</entry>
</row>
</tbody>
</tgroup>
</table>
<para>Values specific to webserver.properties
are:</para>
<table frame="all" pgwide="1" tocentry="1">
<title>WebServer Property File Properties
<tgroup align="left" cols="3">
<thead>
<row>
<entry>Value
<entry>Default
<entry>Description
</row>
</thead>
<tbody valign="top">
<row>
<entry>
<property>server.port
</entry>
<entry>
<literal>80
</entry>
<entry>TCP/IP port used for talking to clients
</row>
<row>
<entry>
<property>server.default_page
</entry>
<entry>
<literal>index.html
</entry>
<entry>the default web page for server
</row>
<row>
<entry>
<property>server.root
</entry>
<entry>
<literal>./
</entry>
<entry>the location of served pages
</row>
<row>
<entry>
<property>.<extension>
</entry>
<entry>
<literal>?
</entry>
<entry>multiple entries such as
<literal>.html=text/html define the mime types of the
static files served by the web server. See the source for
<filename>WebServer.java for a list.
</row>
</tbody>
</tgroup>
</table>
<para>All the above values can be specified on the command line to start
the server by omitting the <literal>server. prefix.
</section>
<section>
<title>Starting a Server from your application
<para>If you want to start the server from within your application, as
opposed to the command line or batch files, you should create an
instance of Server or Web Server, then assign the properties in the form
of a String and start the Server. An example of this can be found in the
<classname>org.hsqldb.test.TestBase source.
<note>
<para>Upgrading: If you have existing custom properties files, change
the values to the new naming convention. Note the use of digits at the
end of <property>server.database.n and
<property>server.dbname.n properties.
</note>
</section>
<section>
<title>Individual Database Properties
<para>Each database has its own <dbname>.properties
</filename> file as part of a small group of files which also includes
<filename><dbname>.script and
<filename><dbname>.data. The properties files contain
key/value pairs for some important settings.</para>
<para>In version 1.8.0 a new SQL command allows most database properties
to be modified as follows:</para>
<programlisting>
SET PROPERTY "property_name" property_value</programlisting>
<para>Properties that can be modified via SET
PROPERTY</literal> are indicated in the table below. Other properties
are indicated as <literal>PROPERTIES FILE ONLY and can be
modified only by editing the .properties file after a shutdown and
before a restart. Only the user-defined values listed below should ever
be modified. Changing any other value could result in unexpected
malfunction in database operations. Most of these values have been
introduced for the new features since 1.7.0:</para>
<table frame="all" pgwide="1" tocentry="1">
<title>Database-specific Property File Properties
<tgroup align="left" cols="3">
<colspec colname="c1" />
<colspec colname="c2" />
<colspec colname="c3" />
<thead>
<row>
<entry>Value
<entry>Default
<entry>Description
</row>
</thead>
<tbody valign="top">
<row>
<entry>
<property>readonly
</entry>
<entry>
<literal>false
</entry>
<entry>whole database is read-only
</row>
<row>
<entry nameend="c3" namest="c1">
<para>When true, the database cannot be modified in use. This
setting can be changed to <literal>true if the
database is to be opened from a CD. Prior to changing this
setting, the database should be closed with the
<literal>SHUTDOWN COMPACT command to ensure
consistency and compactness of the data. <literal>(PROPERTIES
FILE ONLY) but can be used as a connection property to open a
normal database as readonly.</literal>
</entry>
</row>
<row>
<entry>
<property>hsqldb.files_readonly
</entry>
<entry>
<literal>false
</entry>
<entry>database files will not be written to
</row>
<row>
<entry nameend="c3" namest="c1">
<para>When true, data in MEMORY tables can be modified and new
MEMORY tables can be added. However, these changes are not
saved when the database is shutdown. CACHED and TEXT tables
are always readonly when this setting is true.
<literal>(PROPERTIES FILE ONLY)
</entry>
</row>
<row>
<entry>
<property>hsqldb.cache_file_scale
</entry>
<entry>
<literal>1
</entry>
<entry>Set larger data file limits. Once set, the limit will go
up to 8GB.</entry>
</row>
<row>
<entry nameend="c3" namest="c1">
<para>This property can be set to 8 to increase the size limit
of the .data file from 2GB to 8GB. To apply the change to an
existing database, SHUTDOWN SCRIPT should be performed first,
then the property=value line below should be added to the
.properties file before reopening the database.
<programlisting>hsqldb.cache_file_scale=8
<para>The property can be set with the SQL command (as opposed
to changing the value in the properties file) when the
database has no CACHED tables (e.g. a new database).
<literal>(SET PROPERTY)
</entry>
</row>
<row>
<entry>
<property>sql.enforce_size
</entry>
<entry>
<literal>false
</entry>
<entry>trimming and padding string columns
</row>
<row>
<entry nameend="c3" namest="c1">
<para>This property is no longer supported. Use
sql.enforce_sctrict_size</para>
</entry>
</row>
<row>
<entry>
<property>sql.enforce_strict_size
</entry>
<entry>
<literal>false
</entry>
<entry>size enforcement and padding string columns
</row>
<row>
<entry nameend="c3" namest="c1">
<para>Conforms to SQL standards for size and precision of data
types. When true, all CHARACTER, VARCHAR, NUMERIC and DECIMAL
values that are in a row affected by an INSERT INTO or UPDATE
statement are checked against the size specified in the SQL
table definition. An exception is thrown if the value is too
long. Also all CHARACTER values that are shorter than the
specified size are padded with spaces. TIMESTAMP(0) and
TIMESTAMP(6) are also allowed in order to specify the
subsecond resolution of the values. When false (default),
stores the exact string that is inserted. <literal> (SET
PROPERTY)</literal>
</entry>
</row>
<row>
<entry>
<property>sql.tx_no_multi_rewrite
</entry>
<entry>
<literal>false
</entry>
<entry>transaction management
</row>
<row>
<entry nameend="c3" namest="c1">
<para>In the default READ_UNCOMMITED mode, a transaction can
write over rows inserted or updated by another uncommitted
transaction.<literal> Setting this property to true will raise
an exception when such a write is attempted (SET
PROPERTY)</literal>
</entry>
</row>
<row>
<entry>
<property>hsqldb.cache_scale
</entry>
<entry>
<literal>14
</entry>
<entry>memory cache exponent
</row>
<row>
<entry nameend="c3" namest="c1">
<para>Indicates the maximum number of rows of cached tables
that are held in memory, calculated as 3 *(2**value) (three
multiplied by (two to the power value)). The default results
in up to 3*16384 rows from all cached tables being held in
memory at any time.</para>
<para>The value can range between 8-18. (SET
PROPERTY)</literal>. If the value is set via SET PROPERTY then
it becomes effective after the next database SHUTDOWN or
CHECKPOINT. <literal>(SET PROPERTY)
</entry>
</row>
<row>
<entry>
<property>hsqldb.cache_size_scale
</entry>
<entry>
<literal>10
</entry>
<entry>memory cache exponent
</row>
<row>
<entry nameend="c3" namest="c1">
<para>Indicates the average size of each row in the memory
cache used with cached tables, calculated as 2**value (two to
the power value). This result value is multiplied by the
maximum number of rows defined by
<property>hsqldb.cache_scale to form the maximum
number of bytes for all the rows in memory cache. The default
results in 1024 bytes per row. This default, combined with the
default number of rows, results in approximately 50MB of the
.data file to be stored in the memory cache.</para>
<para>The value can range between 6-20. (SET
PROPERTY)</literal>. If the value is set via SET PROPERTY then
it becomes effective after the next database SHUTDOWN or
CHECKPOINT. <literal>(SET PROPERTY)
</entry>
</row>
<row>
<entry>
<property>hsqldb.log_size
</entry>
<entry>
<literal>200
</entry>
<entry>size of log when checkpoint is performed
</row>
<row>
<entry nameend="c3" namest="c1">
<para>The value is the size in megabytes that the
<literal>.log file can reach before an automatic
checkpoint occurs. A checkpoint and rewrites the
<literal>.script file and clears the
<literal>.log file. The value can be changed via the
<literal>SET LOGSIZE nnn SQL command.
</entry>
</row>
<row>
<entry>
<property>runtime.gc_interval
</entry>
<entry>
<literal>0
</entry>
<entry>forced garbage collection
</row>
<row>
<entry nameend="c3" namest="c1">
<para>This setting forces garbage collection each time a set
number of result set row or cache row objects are created. The
default, "0" means no garbage collection is forced by the
program.</para>
<para>This should not be set when the database engine is
acting as a server inside an exclusive JVM. The setting can be
useful when the database is used in-process with the
application with some Java Runtime Environments (JRE's). Some
JRE's increase the size of the memory heap before doing any
automatic garbage collection. This setting would prevent any
unnecessary enlargement of the heap. Typical values for this
setting would probably be between 10,000 to 100,000.
<literal>(PROPERTIES FILE ONLY)
</entry>
</row>
<row>
<entry>
<property>hsqldb.nio_data_file
</entry>
<entry>
<literal>true
</entry>
<entry>use of nio access methods for the .data file
</row>
<row>
<entry nameend="c3" namest="c1">
<para>When HSQLDB is compiled and run in Java 1.4 or higher,
setting this property to <literal>false will avoid
the use of nio access methods, resulting in somewhat reduced
speed. If the data file is larger than 256MB when it is first
opened, nio access methods are not used. Also, if the file
gets larger than the amount of available computer memory that
needs to be allocated for nio access, non-nio access methods
are used.</para>
<para>(SET PROPERTY). If used before
defining any CACHED table, it applies to the current session,
otherwise it comes to effect after a SHUTDOWN and restart or
CHECKPOINT.</para>
</entry>
</row>
<row>
<entry>
<property>hsqldb.default_table_type
</entry>
<entry>
<literal>memory
</entry>
<entry>type of table created with unqualified CREATE
TABLE</entry>
</row>
<row>
<entry nameend="c3" namest="c1">
<para>The CREATE TABLE command results in a MEMORY table by
default. Setting the value "cached" for this property will
result in a cached table by default. The qualified forms such
as CREATE MEMORY TABLE or CREATE CACHED TABLE are not affected
at all by this property. <literal>(SET
PROPERTY)</literal>
</entry>
</row>
<row>
<entry>
<property>hsqldb.applog
</entry>
<entry>
<literal>0
</entry>
<entry>application logging level
</row>
<row>
<entry nameend="c3" namest="c1">
<para>The default level 0 indicates no logging. Level 1
results in events related to persistence to be logged,
including any failures. The events are logged in a file ending
with .app.log</para>
</entry>
</row>
<row>
<entry>
<property>textdb.*
</entry>
<entry>
<literal>0
</entry>
<entry>default properties for new text tables
</row>
<row>
<entry nameend="c3" namest="c1">
<para>Properties that override the database engine defaults
for newly created text tables. Settings in the text table
<literal>SET <tablename> SOURCE <source string>
</literal>command override both the engine defaults and the
database properties defaults. Individual
<property>textdb.* properties are listed in the
<link endterm="texttables-title"
linkend="texttables-chapter" /> chapter. <literal>(SET
PROPERTY)</literal>
</entry>
</row>
</tbody>
</tgroup>
</table>
<para>When connecting to an in-process database creates a new database,
or opens an existing database (i.e. it is the first connection made to
the database by the application), all the user-defined database
properties listed in this section can be specified as URL
properties.</para>
<note>
<para>Upgrading: From 1.7.0, the location of the database files can no
longer be overridden by paths defined in the properties file. All
files belonging to a database should reside in the same
directory.</para>
</note>
<simpara>The property sql.compare_in_locale=true is no longer supported.
If the line exists in a .properties file, it will switch the database to
the collation for the current default. See the <link
endterm="collation-title" linkend="collation-section" />
command.</simpara>
<simpara>When HSQLDB is used in OpenOffice.org, some property values
will have a different default. The properties and values are:</simpara>
<simpara>hsqldb.default_table_type=cached hsqldb.cache_scale=13
hsqldb.log_size=10; hsqldb.nio_data_file=false
sql.enforce_strict_size=true</simpara>
</section>
</section>
<section>
<title>SQL Commands for Database Properties
<para>There are some database properties that are set with dedicated SQL
commands beginning with SET.</para>
<table frame="all" pgwide="1" tocentry="1">
<title>SQL command properties
<tgroup align="left" cols="1">
<tbody valign="top">
<row>
<entry>
<property>SET WRITE_DELAY {{TRUE | FALSE} | <seconds> |
<milliseconds> MILLIS</property>
</entry>
</row>
<row>
<entry>
<para>The default is TRUE and indicates that the changes to the
database that have been logged are synched to the file system
once every 20 seconds. FALSE indicates there is no delay and at
each commit a file synch operation is performed. Numeric values
from 0 can also be specified for the synch delay.</para>
<para>The purpose of this command is to control the amount of
data loss in case of a total system crash. A delay of 1 second
means at most the data written to disk during the last second
before the crash is lost. All data written prior to this has
been synced and should be recoverable</para>
<para>This setting should be specified on the basis of the
reliability of the hardware used for running the database
engine, the type of disk system used, the possibility of power
failure etc. Also the nature of the data stored should be
considered.</para>
<para>In general, when the system is very reliable, the setting
can be left to the default. If it is not very reliable, or the
data is critical a setting of 1 or 2 seconds would suffice. Only
in the worst case scenario or with the most critical data should
a setting of 0 or FALSE be specified as this will slow the
engine down to the speed at which the file synch operation can
be performed by the disk subsystem.</para>
<para>Values down to 10 millisconds can be specified by adding
MILLIS to the command, but in practice a delay of 100
milliseconds provides 99.99999% reliability with an average one
system crash per 6 days.</para>
</entry>
</row>
<row>
<entry>
<property>SET LOG_SIZE <numeric value>
</entry>
</row>
<row>
<entry>
<para>The engine writes out a log of all the changes to the
database as they occur. This log is synched to the disk based on
the WRITE_DELAY property above. The log is never reused unless
there is an abnormal termination, i.e. the database process is
terminated without SHUTDOWN, or it was terminated using SHUTDOWN
IMMEDIATELY.</para>
<para>The default maximum size of the .log file is 200 MB. When
the maximum size is reached, a CHECKPOINT operation is
performed. This operation will save the other database files in
a consistent state and delete the old log. A value of 0
indicates no limit for the .log file.</para>
</entry>
</row>
<row>
<entry>
<property>SET CHECKPOINT DEFRAG <numeric value>
</entry>
</row>
<row>
<entry>
<para>When rows in CACHED tables are updated or deleted, the
spaces are mostly reused. However, in time, some unused spaces
are left in the .data file, especially when large tables are
dropped or their structure is modified.</para>
<para>A CHECKPOINT operation does not normally reclaim the empty
spaces, whereas CHECKPOINT DEFRAG always does.</para>
<para>This property determines when to perform a CHECKPOINT DEFRAG
when a normal CHECKPOINT occurs (whether initiated by an
administrator or when the size of the log exceeds its limit).
</para>
<para>The numeric value is the number of megabytes of recorded
empty spaces in the .data file that would force a DEFRAG
operation. Low values result in more frequent DEFRAG operations.
A value of 0 indicates no automatic DEFRAG is performed. The
default is 200 megabytes of lost space.</para>
</entry>
</row>
<row>
<entry>
<property>SET REFERENTIAL INTEGRITY {TRUE | FALSE}
</entry>
</row>
<row>
<entry>
<para>This is TRUE by default. If bulk data needs to be loaded
into the database, this property can be set FALSE for the
duration of bulk load operation. This allows loading data for
related tables in any order. The property should be set TRUE
after bulk load. If the loaded data is not guaranteed to conform
to the referential integrity constraints, SQL queries should be
run after loading to identify and modify any non-conforming
rows.</para>
</entry>
</row>
</tbody>
</tgroup>
</table>
</section>
</chapter>
Other HSQLDB examples (source code examples)
Here is a short list of links related to this HSQLDB advancedtopics.xml source code file:
|
The search page
Other HSQLDB source code examples at this package level
Click here to learn more about this project
