Revision as of 13:51, 20 March 2009

Chapitre 4. Considérations avancées

Fred Toussi

HSQLDB Development Group

<ft@cluedup.com>

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.

$Date: 2007/03/24 11:39:08 $

But de ce document

Beaucoup de questions fréquemment posées dans les forums et mailing-lists trouvent leurs réponses dans ce guide. Si vous voulez utiliser HSQLDB avec votre application, vous devez lire ce guide. Ce document couvre les problèmes relatifs au système. Pour les problèmes relatifs au SQL voyez le chapitre : Problèmes liés au SQL.

Connexions

La méthode normale d'accès à une base de données HSQLDB est par une interface de connexion JDBC. Un point de départ aux différentes méthodes pour fournir les services des bases de données et y accéder se situe au chapitre Problèmes liés au SQL. Des détails et exemples sur la connexion JDBC sont fournis dans JavaDoc for jdbcConnection.

La version 1.7.2 a introduit une méthode uniforme de distinction entre différents types de connexions, abordant de nouvelles possibilités pour fournir l'accès à de multiples bases de données. L'identifiant de pilote commun est jdbc:hsqldb: suivi par un identifiant de protocole (mem: file: res: hsql: http: hsqls: https:) puis suivi par l'hôte et les identifiants de port dans le cas de serveurs, et enfin suivi de l'identifiant de la base de données.

Tableau 4.1. Composants des URL Hsqldb

   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

   database.3=/home/dbmaster/lists

   dbname.3=

Propriétés de Connexion

Chaque nouvelle connexion JDBC à une base de données peut spécifier des propriétés de connexion. Les propriétés Utilisateur et Mot de passe sont toujours requises. Dans la version 1.8.0 on peut également utiliser les propriétés optionnelles suivantes.

Les propriétés de connexion peuvent être spécifiées soit en établissant la connexion par l'appel de la commande :

   DriverManager.getConnection (String url, Properties info);

Soit la propriété peut être ajoutée à l'URL complet de connexion.

Tableau 4.2. Propriétés de connexion

   jdbc:hsqldb:hsql://localhost/enrollments;get_column_name=false

   jdbc:hsqldb:file:enrollments;ifexists=true

De plus, quand la connexion à une base de données "in-process" crée une nouvelle base de données, ou en ouvre une existante (par exemple si c'est la première connexion à la base de données faite par l'application), toutes les propriétés de la base de donnée définies par l'utilisateur peuvent être spécifiées comme des propriétés URL. On peut l'utiliser pour spécifier des propriétés qui forcent un langage SQL plus strict, ou pour changer cache_scale ou des propriétés similaires avant que les fichiers de la base de données ne soient créés. Toutefois, dans le cas des nouvelles bases de données, il est recommandé d'utiliser la commande SET PROPERTY pour de tels réglages.

Fichiers de propriétés

HSQLDB s'en remet à un jeu de fichiers de propriétés pour différents réglages. Depuis la version 1.7.0 la nomenclature des propriétés a été rationalisée et nombre de nouvelles propriétés ont été introduites.

Dans tous les fichiers de propriétés, les valeurs sont sensibles à la casse. Toutes les valeurs en dehors des noms de fichiers ou de pages sont requises en minuscule (c.a.d. server.silent=FALSE n'aura pas d'effet, mais server.silent=false fonctionnera).

Les fichiers de propriétés et les réglages stockés en eux sont les suivants :

Tableau 4.3. Fichiers de propriétés Hsqldb Server

Les fichiers de propriétés pour exécuter les serveurs ne sont pas créés automatiquement. Vous devez créer vos propres fichiers contenant les paires server.property=value pour chaque propriété.

Le fichier des propriétés de chaque base de données est généré par le moteur de base de données. Ce fichier peut être édité après fermeture de la base de données. Dans la version 1.8.0, la plupart de ces propriétés peuvent être changés via les commandes SQL.

Les propriétés de Serveur et de Serveur Web

Dans les deux fichiers server.properties et webserver.properties, les valeurs prises en charge et leur valeur par défaut sont comme suit :

Tableau 4.4. Propriétés d'un fichier de propriétés

Dans la version 1.8.0, chaque serveur peut servir jusqu'à dix bases de données différentes simultanément. La propriété server.database.0 définit le nom de fichier / chemin alors que server.dbname.0 définit l'alias en minuscule utilisé par les clients pour se connecter à cette base de données. Le chiffre 0 est incrémenté pour la deuxième base de données, et ainsi de suite. Les valeurs pour la propriété server.database.{0-9} peuvent utiliser les préfixes mem:, file: ou res: et les propriétés sont décrites plus haut dans la section Connexions. Par exemple,

   database.0=mem:temp;sql.enforce_strict_size=true;

Les valeurs spécifiques à server.properties sont :

Tableau 4.5. Propriétés du fichier Propriété Serveur

Les valeurs spécifiques à webserver.properties sont :

Tableau 4.6. Propriétés du fichier Propriété WebServeur

Toutes les valeurs ci dessus peuvent être spécifiées en ligne de commande de démarrage du serveur en omettant le préfixe server.

Starting a Server from your application

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 org.hsqldb.test.TestBase source. Note

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 server.database.n and server.dbname.n properties.

Individual Database Properties

Each database has its own <dbname>.properties file as part of a small group of files which also includes <dbname>.script and <dbname>.data. The properties files contain key/value pairs for some important settings.

In version 1.8.0 a new SQL command allows most database properties to be modified as follows:

   SET PROPERTY "property_name" property_value

Properties that can be modified via SET PROPERTY are indicated in the table below. Other properties are indicated as 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:

Table 4.7. Database-specific Property File Properties Value Default Description readonly false whole database is read-only

When true, the database cannot be modified in use. This setting can be changed to true if the database is to be opened from a CD. Prior to changing this setting, the database should be closed with the SHUTDOWN COMPACT command to ensure consistency and compactness of the data. (PROPERTIES FILE ONLY) but can be used as a connection property to open a normal database as readonly. hsqldb.files_readonly false database files will not be written to

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. (PROPERTIES FILE ONLY) hsqldb.cache_file_scale 1 Set larger data file limits. Once set, the limit will go up to 8GB.

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.

hsqldb.cache_file_scale=8

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). (SET PROPERTY) sql.enforce_size false trimming and padding string columns

This property is no longer supported. Use sql.enforce_sctrict_size sql.enforce_strict_size false size enforcement and padding string columns

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. (SET PROPERTY) sql.tx_no_multi_rewrite false transaction management

In the default READ_UNCOMMITED mode, a transaction can write over rows inserted or updated by another uncommitted transaction. Setting this property to true will raise an exception when such a write is attempted (SET PROPERTY) hsqldb.cache_scale 14 memory cache exponent

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.

The value can range between 8-18. (SET PROPERTY). If the value is set via SET PROPERTY then it becomes effective after the next database SHUTDOWN or CHECKPOINT. (SET PROPERTY) hsqldb.cache_size_scale 10 memory cache exponent

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 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.

The value can range between 6-20. (SET PROPERTY). If the value is set via SET PROPERTY then it becomes effective after the next database SHUTDOWN or CHECKPOINT. (SET PROPERTY) hsqldb.log_size 200 size of log when checkpoint is performed

The value is the size in megabytes that the .log file can reach before an automatic checkpoint occurs. A checkpoint and rewrites the .script file and clears the .log file. The value can be changed via the SET LOGSIZE nnn SQL command. runtime.gc_interval 0 forced garbage collection

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.

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. (PROPERTIES FILE ONLY) hsqldb.nio_data_file true use of nio access methods for the .data file

When HSQLDB is compiled and run in Java 1.4 or higher, setting this property to 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.

(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. hsqldb.default_table_type memory type of table created with unqualified CREATE TABLE

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. (SET PROPERTY) hsqldb.applog 0 application logging level

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 textdb.* 0 default properties for new text tables

 command override both the engine defaults and the database properties defaults. Individual textdb.* properties are listed in the Text Tables chapter. (SET PROPERTY)

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.
Note

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.

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 SET DATABASE COLLATION[2] command.

When HSQLDB is used in OpenOffice.org, some property values will have a different default. The properties and values are:

hsqldb.default_table_type=cached hsqldb.cache_scale=13 hsqldb.log_size=10; hsqldb.nio_data_file=false sql.enforce_strict_size=true

== SQL Commands for Database Properties ==
There are some database properties that are set with dedicated SQL commands beginning with SET.

Table 4.8. SQL command properties
SET WRITE_DELAY {{TRUE | FALSE} | <seconds> | <milliseconds> MILLIS

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.

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

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.

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.

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.
SET LOG_SIZE <numeric value>

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.

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.
SET CHECKPOINT DEFRAG <numeric value>

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.

A CHECKPOINT operation does not normally reclaim the empty spaces, whereas CHECKPOINT DEFRAG always does.

This property determines when a normal CHECKPOINT, whether initiated by an administrator or when the size of the log exceeds its limit.

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.
SET REFERENTIAL INTEGRITY {TRUE | FALSE}

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.