This file is indexed.

/usr/share/doc/libhsqldb-java-doc/guide/ch04.html is in libhsqldb-java-doc 1.8.0.10-9ubuntu2.

This file is owned by root:root, with mode 0o644.

The actual contents of the file can be viewed below.

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
<html><head><META http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"><title>Chapter&nbsp;4.&nbsp;Advanced Topics</title><link href="guide.css" rel="stylesheet" type="text/css"><meta content="DocBook XSL Stylesheets V1.65.1" name="generator"><meta name="keywords" content="Hsqldb, Advanced"><meta name="keywords" content="Hsqldb, Hypersonic, Database, JDBC, Java"><link rel="home" href="index.html" title="Hsqldb User Guide"><link rel="up" href="index.html" title="Hsqldb User Guide"><link rel="previous" href="ch03.html" title="Chapter&nbsp;3.&nbsp;UNIX Quick Start"><link rel="next" href="ch05.html" title="Chapter&nbsp;5.&nbsp;Deployment Issues"></head><body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="navheader"><table summary="Navigation header" width="100%"><tr><th align="center" colspan="3">Chapter&nbsp;4.&nbsp;Advanced Topics</th></tr><tr><td align="left" width="20%"><a accesskey="p" href="ch03.html"><img src="navicons/prev.gif" alt="Prev"></a>&nbsp;</td><th align="center" width="60%">&nbsp;</th><td align="right" width="20%">&nbsp;<a accesskey="n" href="ch05.html"><img src="navicons/next.gif" alt="Next"></a></td></tr></table><hr></div><div class="chapter" lang="en"><div class="titlepage"><div><div><h2 class="title"><a name="advanced-chapter"></a>Chapter&nbsp;4.&nbsp;Advanced Topics</h2></div><div><div class="authorgroup"><div class="author"><h3 class="author"><span class="firstname">Fred</span> <span class="surname">Toussi</span></h3><div class="affiliation"><span class="orgname">HSQLDB Development Group<br></span></div><tt class="email">&lt;<a href="mailto:ft@cluedup.com">ft@cluedup.com</a>&gt;</tt></div></div></div><div><div class="legalnotice"><p>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.</p></div></div><div><p class="pubdate">$Date: 2007/03/24 11:39:08 $</p></div></div><div></div></div><div class="toc"><p><b>Table of Contents</b></p><dl><dt><span class="section"><a href="ch04.html#N108C9">Purpose</a></span></dt><dt><span class="section"><a href="ch04.html#N108D2">Connections</a></span></dt><dd><dl><dt><span class="section"><a href="ch04.html#N109E6">Connection properties</a></span></dt></dl></dd><dt><span class="section"><a href="ch04.html#N10A68">Properties Files</a></span></dt><dd><dl><dt><span class="section"><a href="ch04.html#N10AC1">Server and Web Server Properties</a></span></dt><dt><span class="section"><a href="ch04.html#N10BBC">Starting a Server from your application</a></span></dt><dt><span class="section"><a href="ch04.html#N10BCF">Individual Database Properties</a></span></dt></dl></dd><dt><span class="section"><a href="ch04.html#N10D67">SQL Commands for Database Properties</a></span></dt></dl></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="N108C9"></a>Purpose</h2></div></div><div></div></div><p>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 <a href="ch02.html" title="Chapter&nbsp;2.&nbsp;SQL Issues">SQL Issues</a> chapter.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="N108D2"></a>Connections</h2></div></div><div></div></div><p>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 <a href="ch02.html" title="Chapter&nbsp;2.&nbsp;SQL Issues">SQL Issues</a> chapter.
    Details and examples of how to connect via JDBC are provided in our <a href="../src/org/hsqldb/jdbc/jdbcConnection.html" target="_top">JavaDoc for
    <tt class="literal">jdbcConnection</tt></a>.</p><p>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
    <tt class="literal">jdbc:hsqldb:</tt> followed by a protocol identifier
    (<tt class="literal">mem: file: res: hsql: http: hsqls: https:</tt>) then
    followed by host and port identifiers in the case of servers, then
    followed by database identifier.</p><div class="table"><a name="N108EC"></a><p class="title"><b>Table&nbsp;4.1.&nbsp;Hsqldb URL Components</b></p><table summary="Hsqldb URL Components" width="100%" border="1"><colgroup><col align="left"><col align="left"><col align="left"></colgroup><thead><tr><th align="left">Driver and Protocol</th><th align="left">Host and Port</th><th align="left">Database</th></tr></thead><tbody valign="top"><tr><td align="left"><table summary="Simple list" border="0" class="simplelist"><tr><td>
                  <tt class="literal">jdbc:hsqldb:mem:</tt>
                </td></tr></table></td><td align="left">not available</td><td align="left"><table summary="Simple list" border="0" class="simplelist"><tr><td>
                  <tt class="literal">accounts</tt>
                </td></tr></table></td></tr><tr><td colspan="3" align="left"><p>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.</p><p>The old form for the URL, <tt class="literal">jdbc:hsqldb:.</tt>
              creates or connects to the same database as the new form for the
              URL, <tt class="literal">jdbc:hsqldb:mem:.</tt></p></td></tr><tr><td align="left"><table summary="Simple list" border="0" class="simplelist"><tr><td>
                  <tt class="literal">jdbc:hsqldb:file:</tt>
                </td></tr></table></td><td align="left">not available</td><td align="left"><table summary="Simple list" border="0" class="simplelist"><tr><td>
                  <tt class="filename">mydb</tt>
                </td></tr><tr><td>
                  <tt class="filename">/opt/db/accounts</tt>
                </td></tr><tr><td>
                  <tt class="filename">C:/data/mydb</tt>
                </td></tr></table></td></tr><tr><td colspan="3" align="left"><p>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 <tt class="literal">java</tt>command for running
              the application was issued. The second and third examples refer
              to absolute paths on the host machine.</p></td></tr><tr><td align="left"><table summary="Simple list" border="0" class="simplelist"><tr><td>
                  <tt class="literal">jdbc:hsqldb:res:</tt>
                </td></tr></table></td><td align="left">not available</td><td align="left"><table summary="Simple list" border="0" class="simplelist"><tr><td>
                  <tt class="filename">/adirectory/dbname</tt>
                </td></tr></table></td></tr><tr><td colspan="3" align="left">Database files can be loaded from
            one of the jars specified as part of the <tt class="literal">Java</tt>
            command the same way as resource files are accessed in Java
            programs. The <tt class="literal">/adirectory</tt> above stands for a
            directory in one of the jars.</td></tr><tr><td align="left"><table summary="Simple list" border="0" class="simplelist"><tr><td>
                  <tt class="literal">jdbc:hsqldb:hsql:</tt>
                </td></tr><tr><td>
                  <tt class="literal">jdbc:hsqldb:hsqls:</tt>
                </td></tr><tr><td>
                  <tt class="literal">jdbc:hsqldb:http:</tt>
                </td></tr><tr><td>
                  <tt class="literal">jdbc:hsqldb:https:</tt>
                </td></tr></table></td><td align="left"><table summary="Simple list" border="0" class="simplelist"><tr><td>
                  <tt class="literal">//localhost</tt>
                </td></tr><tr><td>
                  <tt class="literal">//192.0.0.10:9500</tt>
                </td></tr><tr><td>
                  <tt class="literal">//dbserver.somedomain.com</tt>
                </td></tr></table></td><td align="left"><table summary="Simple list" border="0" class="simplelist"><tr><td>
                  <tt class="literal">/an_alias</tt>
                </td></tr><tr><td>
                  <tt class="literal">/enrollments</tt>
                </td></tr><tr><td>
                  <tt class="literal">/quickdb</tt>
                </td></tr></table></td></tr><tr><td colspan="3" align="left"><p>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 <tt class="filename">server.properties</tt> 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 <tt class="filename">server.properties </tt> or
              <tt class="filename">webserver.properties</tt> define the database
              aliases listed above and accessible to clients to refer to
              different file and in-memory databases.</p><pre class="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</pre><p>The old form for the server URL, e.g.,
              <tt class="literal">jdbc:hsqldb:hsql//localhost</tt> connects to the
              same database as the new form for the URL,
              <tt class="literal">jdbc:hsqldb:hsql//localhost/</tt> where the alias
              is a zero length string. In the example below, the database
              files <tt class="literal">lists.*</tt> in the
              <tt class="literal">/home/dbmaster/</tt> directory are associated with
              the empty alias:</p><pre class="programlisting">
    database.3=/home/dbmaster/lists
    dbname.3=</pre></td></tr></tbody></table></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="N109E6"></a>Connection properties</h3></div></div><div></div></div><p>Each new JDBC Connection to a database can specify connection
      properties. The properties <span class="property">user</span> and
      <span class="property">password</span> are always required. In 1.8.0 the
      following optional properties can also be used.</p><p>Connection properties are specified either by establishing the
      connection via the:</p><pre class="programlisting">
    DriverManager.getConnection (String url, Properties info);</pre><p>method call, or the property can be appended to the full
      Connection URL.</p><div class="table"><a name="N109FA"></a><p class="title"><b>Table&nbsp;4.2.&nbsp;Connection Properties</b></p><table summary="Connection Properties" width="100%" border="1"><colgroup><col align="left"><col align="left"><col align="left"></colgroup><tbody valign="top"><tr><td align="left"><span class="property">get_column_name</span></td><td align="left"><tt class="literal">true</tt></td><td align="left">column name in ResultSet</td></tr><tr><td colspan="3" align="left"><p>This property is used for compatibility with other JDBC
                driver implementations. When true (the default),
                <tt class="literal">ResultSet.getColumnName(int c)</tt> returns the
                underlying column name</p><p>When false, the above method returns the same value as
                <tt class="literal">ResultSet.getColumnLabel(int column)</tt>
                Example below:</p><pre class="programlisting">
    jdbc:hsqldb:hsql://localhost/enrollments;get_column_name=false
                    </pre><p>When a ResultSet is used inside a user-defined stored
                procedure, the default, true, is always used for this
                property.</p></td></tr><tr><td align="left"><span class="property">ifexists</span></td><td align="left"><tt class="literal">false</tt></td><td align="left">connect only if database already exists</td></tr><tr><td colspan="3" align="left"><p>Has an effect only with <tt class="literal">mem:</tt> and
                <tt class="literal">file:</tt> database. When true, will not create
                a new database if one does not already exist for the
                URL.</p><p>When false (the default), a new <tt class="literal">mem:</tt>
                or <tt class="literal">file:</tt> database will be created if it
                does not exist.</p><p>Setting the property to true is useful when
                troubleshooting as no database is created if the URL is
                malformed. Example below:</p><pre class="programlisting">
    jdbc:hsqldb:file:enrollments;ifexists=true</pre></td></tr><tr><td align="left"><span class="property">shutdown</span></td><td align="left"><tt class="literal">false</tt></td><td align="left">shut down the database when the last connection is
              closed</td></tr><tr><td colspan="3" align="left"><p>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.</p><p>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.</p></td></tr></tbody></table></div><p>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.</p></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="N10A68"></a>Properties Files</h2></div></div><div></div></div><p>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.</p><p>In all properties files, values are case-sensitive. All values apart
    from names of files or pages are required in lowercase (e.g.
    <span class="property">server.silent</span>=<tt class="literal">FALSE</tt> will have no
    effect, but <span class="property">server.silent</span>=<tt class="literal">false</tt>
    will work).</p><p>The properties files and the settings stored in them are as
    follows:</p><div class="table"><a name="N10A81"></a><p class="title"><b>Table&nbsp;4.3.&nbsp;Hsqldb Server Properties Files</b></p><table summary="Hsqldb Server Properties Files" width="100%" border="1"><colgroup><col><col><col></colgroup><thead><tr><th align="left">File Name</th><th align="left">Location</th><th align="left">Function</th></tr></thead><tbody valign="top"><tr><td align="left"><tt class="filename">server.properties</tt></td><td align="left">the directory where the command to run the
            <tt class="classname">Server</tt> class is issued</td><td align="left">settings for running HSQLDB as a database server
            communicating with the HSQL protocol</td></tr><tr><td align="left"><tt class="filename">webserver.properties</tt></td><td align="left">the directory where the command to run the
            <tt class="classname">WebServer</tt> class is issued</td><td align="left">settings for running HSQLDB as a database server
            communicating with the HTTP protocol</td></tr><tr><td align="left"><tt class="filename">&lt;dbname&gt;.properties</tt></td><td align="left">the directory where all the files for a database are
            located</td><td align="left">settings for each particular database</td></tr></tbody></table></div><p>Properties files for running the servers are not created
    automatically. You should create your own files that contain
    <span class="property">server.property</span>=<tt class="literal">value</tt> pairs for
    each property.</p><p>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.</p><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="N10AC1"></a>Server and Web Server Properties</h3></div></div><div></div></div><p>In both <tt class="filename">server.properties</tt> and
      <tt class="filename">webserver.properties</tt> files, supported values and
      their defaults are as follows:</p><div class="table"><a name="N10ACE"></a><p class="title"><b>Table&nbsp;4.4.&nbsp;Property File Properties</b></p><table summary="Property File Properties" width="100%" border="1"><colgroup><col><col><col></colgroup><thead><tr><th align="left">Value</th><th align="left">Default</th><th align="left">Description</th></tr></thead><tbody valign="top"><tr><td align="left"><span class="property">server.database.0</span></td><td align="left"><tt class="literal">test</tt></td><td align="left">the path and file name of the first database file to
              use</td></tr><tr><td align="left"><span class="property">server.dbname.0</span></td><td align="left"><tt class="literal">""</tt></td><td align="left">lowercase server alias for the first database
              file</td></tr><tr><td align="left"><span class="property">server.urlid.0</span></td><td align="left"><tt class="literal">NONE</tt></td><td align="left">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).</td></tr><tr><td align="left"><span class="property">server.silent</span></td><td align="left"><tt class="literal">true</tt></td><td align="left">no extensive messages displayed on console</td></tr><tr><td align="left"><span class="property">server.trace</span></td><td align="left"><tt class="literal">false</tt></td><td align="left">JDBC trace messages displayed on console</td></tr></tbody></table></div><p>In 1.8.0, each server can serve up to 10 different databases
      simultaneously. The <span class="property">server.database.0</span> property
      defines the filename / path whereas the
      <span class="property">server.dbname.0</span> 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
      <span class="property">server.database.{0-9}</span> property can use the
      <tt class="literal">mem:</tt>, <tt class="literal">file:</tt> or
      <tt class="literal">res:</tt> prefixes and properties as discussed above under
      CONNECTIONS. For example, <div class="informalexample"><pre class="programlisting">
    database.0=mem:temp;sql.enforce_strict_size=true;</pre></div></p><p>Values specific to <tt class="filename">server.properties</tt>
      are:</p><div class="table"><a name="N10B3C"></a><p class="title"><b>Table&nbsp;4.5.&nbsp;Server Property File Properties</b></p><table summary="Server Property File Properties" width="100%" border="1"><colgroup><col><col><col></colgroup><thead><tr><th align="left">Value</th><th align="left">Default</th><th align="left">Description</th></tr></thead><tbody valign="top"><tr><td align="left"><span class="property">server.port</span></td><td align="left"><tt class="literal">9001 (normal) or 554 (if TLS encrypted)</tt></td><td align="left">TCP/IP port used for talking to clients. All databases
              are served on the same port.</td></tr><tr><td align="left"><span class="property">server.no_system_exit</span></td><td align="left"><tt class="literal">true</tt></td><td align="left">no <tt class="literal">System.exit()</tt> call when the
              database is closed</td></tr></tbody></table></div><p>Values specific to <tt class="filename">webserver.properties</tt>
      are:</p><div class="table"><a name="N10B6F"></a><p class="title"><b>Table&nbsp;4.6.&nbsp;WebServer Property File Properties</b></p><table summary="WebServer Property File Properties" width="100%" border="1"><colgroup><col><col><col></colgroup><thead><tr><th align="left">Value</th><th align="left">Default</th><th align="left">Description</th></tr></thead><tbody valign="top"><tr><td align="left"><span class="property">server.port</span></td><td align="left"><tt class="literal">80</tt></td><td align="left">TCP/IP port used for talking to clients</td></tr><tr><td align="left"><span class="property">server.default_page</span></td><td align="left"><tt class="literal">index.html</tt></td><td align="left">the default web page for server</td></tr><tr><td align="left"><span class="property">server.root</span></td><td align="left"><tt class="literal">./</tt></td><td align="left">the location of served pages</td></tr><tr><td align="left"><span class="property">.&lt;extension&gt;</span></td><td align="left"><tt class="literal">?</tt></td><td align="left">multiple entries such as
              <tt class="literal">.html=text/html</tt> define the mime types of the
              static files served by the web server. See the source for
              <tt class="filename">WebServer.java</tt> for a list.</td></tr></tbody></table></div><p>All the above values can be specified on the command line to start
      the server by omitting the <tt class="literal">server.</tt> prefix.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="N10BBC"></a>Starting a Server from your application</h3></div></div><div></div></div><p>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
      <tt class="classname">org.hsqldb.test.TestBase</tt> source.</p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>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 <span class="property">server.database.n</span> and
        <span class="property">server.dbname.n</span> properties.</p></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="N10BCF"></a>Individual Database Properties</h3></div></div><div></div></div><p>Each database has its own <tt class="filename">&lt;dbname&gt;.properties
      </tt> file as part of a small group of files which also includes
      <tt class="filename">&lt;dbname&gt;.script</tt> and
      <tt class="filename">&lt;dbname&gt;.data</tt>. The properties files contain
      key/value pairs for some important settings.</p><p>In version 1.8.0 a new SQL command allows most database properties
      to be modified as follows:</p><pre class="programlisting">
    SET PROPERTY "property_name" property_value</pre><p>Properties that can be modified via <tt class="literal">SET
      PROPERTY</tt> are indicated in the table below. Other properties
      are indicated as <tt class="literal">PROPERTIES FILE ONLY</tt> 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:</p><div class="table"><a name="N10BEF"></a><p class="title"><b>Table&nbsp;4.7.&nbsp;Database-specific Property File Properties</b></p><table summary="Database-specific Property File Properties" width="100%" border="1"><colgroup><col align="left"><col align="left"><col align="left"></colgroup><thead><tr><th align="left">Value</th><th align="left">Default</th><th align="left">Description</th></tr></thead><tbody valign="top"><tr><td align="left"><span class="property">readonly</span></td><td align="left"><tt class="literal">false</tt></td><td align="left">whole database is read-only</td></tr><tr><td colspan="3" align="left"><p>When true, the database cannot be modified in use. This
                setting can be changed to <tt class="literal">true</tt> if the
                database is to be opened from a CD. Prior to changing this
                setting, the database should be closed with the
                <tt class="literal">SHUTDOWN COMPACT</tt> command to ensure
                consistency and compactness of the data. <tt class="literal">(PROPERTIES
                FILE ONLY) but can be used as a connection property to open a
                normal database as readonly.</tt></p></td></tr><tr><td align="left"><span class="property">hsqldb.files_readonly</span></td><td align="left"><tt class="literal">false</tt></td><td align="left">database files will not be written to</td></tr><tr><td colspan="3" align="left"><p>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.
                <tt class="literal">(PROPERTIES FILE ONLY)</tt></p></td></tr><tr><td align="left"><span class="property">hsqldb.cache_file_scale</span></td><td align="left"><tt class="literal">1</tt></td><td align="left">Set larger data file limits. Once set, the limit will go
              up to 8GB.</td></tr><tr><td colspan="3" align="left"><p>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.
                <pre class="programlisting">hsqldb.cache_file_scale=8</pre></p><p>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).
                <tt class="literal">(SET PROPERTY)</tt></p></td></tr><tr><td align="left"><span class="property">sql.enforce_size</span></td><td align="left"><tt class="literal">false</tt></td><td align="left">trimming and padding string columns</td></tr><tr><td colspan="3" align="left"><p>This property is no longer supported. Use
                sql.enforce_sctrict_size</p></td></tr><tr><td align="left"><span class="property">sql.enforce_strict_size</span></td><td align="left"><tt class="literal">false</tt></td><td align="left">size enforcement and padding string columns</td></tr><tr><td colspan="3" align="left"><p>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. <tt class="literal"> (SET
                PROPERTY)</tt></p></td></tr><tr><td align="left"><span class="property">sql.tx_no_multi_rewrite</span></td><td align="left"><tt class="literal">false</tt></td><td align="left">transaction management</td></tr><tr><td colspan="3" align="left"><p>In the default READ_UNCOMMITED mode, a transaction can
                write over rows inserted or updated by another uncommitted
                transaction.<tt class="literal"> Setting this property to true will raise
                an exception when such a write is attempted (SET
                PROPERTY)</tt></p></td></tr><tr><td align="left"><span class="property">hsqldb.cache_scale</span></td><td align="left"><tt class="literal">14</tt></td><td align="left">memory cache exponent</td></tr><tr><td colspan="3" align="left"><p>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.</p><p>The value can range between 8-18. <tt class="literal">(SET
                PROPERTY)</tt>. If the value is set via SET PROPERTY then
                it becomes effective after the next database SHUTDOWN or
                CHECKPOINT. <tt class="literal">(SET PROPERTY)</tt></p></td></tr><tr><td align="left"><span class="property">hsqldb.cache_size_scale</span></td><td align="left"><tt class="literal">10</tt></td><td align="left">memory cache exponent</td></tr><tr><td colspan="3" align="left"><p>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
                <span class="property">hsqldb.cache_scale</span> 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.</p><p>The value can range between 6-20. <tt class="literal">(SET
                PROPERTY)</tt>. If the value is set via SET PROPERTY then
                it becomes effective after the next database SHUTDOWN or
                CHECKPOINT. <tt class="literal">(SET PROPERTY)</tt></p></td></tr><tr><td align="left"><span class="property">hsqldb.log_size</span></td><td align="left"><tt class="literal">200</tt></td><td align="left">size of log when checkpoint is performed</td></tr><tr><td colspan="3" align="left"><p>The value is the size in megabytes that the
                <tt class="literal">.log</tt> file can reach before an automatic
                checkpoint occurs. A checkpoint and rewrites the
                <tt class="literal">.script</tt> file and clears the
                <tt class="literal">.log</tt> file. The value can be changed via the
                <tt class="literal">SET LOGSIZE nnn</tt> SQL command.</p></td></tr><tr><td align="left"><span class="property">runtime.gc_interval</span></td><td align="left"><tt class="literal">0</tt></td><td align="left">forced garbage collection</td></tr><tr><td colspan="3" align="left"><p>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.</p><p>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.
                <tt class="literal">(PROPERTIES FILE ONLY)</tt></p></td></tr><tr><td align="left"><span class="property">hsqldb.nio_data_file</span></td><td align="left"><tt class="literal">true</tt></td><td align="left">use of nio access methods for the .data file</td></tr><tr><td colspan="3" align="left"><p>When HSQLDB is compiled and run in Java 1.4 or higher,
                setting this property to <tt class="literal">false</tt> 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.</p><p><tt class="literal">(SET PROPERTY)</tt>. 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.</p></td></tr><tr><td align="left"><span class="property">hsqldb.default_table_type</span></td><td align="left"><tt class="literal">memory</tt></td><td align="left">type of table created with unqualified CREATE
              TABLE</td></tr><tr><td colspan="3" align="left"><p>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. <tt class="literal">(SET
                PROPERTY)</tt></p></td></tr><tr><td align="left"><span class="property">hsqldb.applog</span></td><td align="left"><tt class="literal">0</tt></td><td align="left">application logging level</td></tr><tr><td colspan="3" align="left"><p>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</p></td></tr><tr><td align="left"><span class="property">textdb.*</span></td><td align="left"><tt class="literal">0</tt></td><td align="left">default properties for new text tables</td></tr><tr><td colspan="3" align="left"><p>Properties that override the database engine defaults
                for newly created text tables. Settings in the text table
                <tt class="literal">SET &lt;tablename&gt; SOURCE &lt;source string&gt;
                </tt>command override both the engine defaults and the
                database properties defaults. Individual
                <span class="property">textdb.*</span> properties are listed in the
                <a href="ch06.html" title="Chapter&nbsp;6.&nbsp;Text Tables">Text Tables</a> chapter. <tt class="literal">(SET
                PROPERTY)</tt></p></td></tr></tbody></table></div><p>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.</p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>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.</p></div><p>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 <a href="ch09.html#collation-section" title="SET DATABASE COLLATION">SET DATABASE COLLATION<sup>[<a href="#ftn.posthyper">2</a>]</sup></a>
      command.</p><p>When HSQLDB is used in OpenOffice.org, some property values
      will have a different default. The properties and values are:</p><p>hsqldb.default_table_type=cached hsqldb.cache_scale=13
      hsqldb.log_size=10; hsqldb.nio_data_file=false
      sql.enforce_strict_size=true</p></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="N10D67"></a>SQL Commands for Database Properties</h2></div></div><div></div></div><p>There are some database properties that are set with dedicated SQL
    commands beginning with SET.</p><div class="table"><a name="N10D6C"></a><p class="title"><b>Table&nbsp;4.8.&nbsp;SQL command properties</b></p><table summary="SQL command properties" width="100%" border="1"><colgroup><col></colgroup><tbody valign="top"><tr><td align="left"><span class="property">SET WRITE_DELAY {{TRUE | FALSE} | &lt;seconds&gt; |
              &lt;milliseconds&gt; MILLIS</span></td></tr><tr><td align="left"><p>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.</p><p>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</p><p>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.</p><p>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.</p><p>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.</p></td></tr><tr><td align="left"><span class="property">SET LOG_SIZE &lt;numeric value&gt;</span></td></tr><tr><td align="left"><p>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.</p><p>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.</p></td></tr><tr><td align="left"><span class="property">SET CHECKPOINT DEFRAG &lt;numeric value&gt;</span></td></tr><tr><td align="left"><p>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.</p><p>A CHECKPOINT operation does not normally reclaim the empty
              spaces, whereas CHECKPOINT DEFRAG always does.</p><p>This property determines when a normal CHECKPOINT, whether
              initiated by an administrator or when the size of the log
              exceeds its limit.</p><p>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.</p></td></tr><tr><td align="left"><span class="property">SET REFERENTIAL INTEGRITY {TRUE | FALSE}</span></td></tr><tr><td align="left"><p>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.</p></td></tr></tbody></table></div></div></div><div class="navfooter"><hr><table summary="Navigation footer" width="100%"><tr><td align="left" width="40%"><a accesskey="p" href="ch03.html"><img src="navicons/prev.gif" alt="Prev"></a>&nbsp;</td><td align="center" width="20%"><a accesskey="u" href="index.html"><img src="navicons/up.gif" alt="Up"></a></td><td align="right" width="40%">&nbsp;<a accesskey="n" href="ch05.html"><img src="navicons/next.gif" alt="Next"></a></td></tr><tr><td valign="top" align="left" width="40%">Chapter&nbsp;3.&nbsp;UNIX Quick Start&nbsp;</td><td align="center" width="20%"><a accesskey="h" href="index.html"><img src="navicons/home.gif" alt="Home"></a></td><td valign="top" align="right" width="40%">&nbsp;Chapter&nbsp;5.&nbsp;Deployment Issues</td></tr></table></div></body></html>