<DefinitionDeColonne> a la même syntaxe que la définition de colonne normale. La nouvelle définition de colonne remplace l'ancienne, il est donc possible d'ajouter/supprimer une expression DEFAULT, une contrainte NOT NULL, ou une définition IDENTITY. Il n'est pas possible de changer la clé primaire avec cette commande.
On utilise une syntaxe différente pour changer la prochaine valeur d'une colonne IDENTITY :
ALTER TABLE ALTER [COLUMN] <column name> RESTART WITH <n>
Ajouter ou supprimer des clés primaires
Il est maintenant possible d'ajouter ou de supprimer une clé primaire.
Une clé primaire existante à supprimer ne doit pas être référencée dans une contrainte de clé externe (c.a.d. pas de liaisons avec une autre table). Si une table a une colonne IDENTITY, supprimer une clé primaire supprimera les attributs d'identité de la colonne mais préservera les données.
Lors de l'ajout d'une clé primaire, une contrainte NOT NULL est automatiquement ajoutée aux définitions de colonne. Les données de la table pour les colonnes d'une clé primaire nouvellement déclarée ne doivent pas contenir de valeurs nulles.
ALTER TABLE <name> ADD CONSTRAINT <cname> PRIMARY KEY(collist);
ALTER TABLE <name> DROP CONSTRAINT <cname>;
ALTER TABLE <name> DROP PRIMARY KEY; // alternative syntax
SIZE ENFORCEMENT
La propriété de base de données sql.enforce_strict_size=true a maintenant une plus grande portée (un effet plus large).
Les longueurs précédentes pour les types CHAR / VARCHAR pouvaient être vérifiées et le remplissage exécuté seulement lors de l'insertion / mise à jour de lignes. (Previously CHAR /VARCHAR lengths could be checked and padding performed only when inserting / updating rows.) Prise en charge supplémentaire de CHAR(n), VARCHAR(n), NUMERIC(p,s) et DECIMAL(p,s) incluant la sémantique des fonctions standards SQL cast et convert. Les déclarations CHAR et VARCHAR requièrent maintenant un paramètre Taille (size). Une déclaration CHAR sans paramètre size est interprété comme CHAR(1). TIMESTAMP(0) et TIMESTAMP(6) sont aussi supportés, avec une précision représentant une résolution en fraction de seconde (sub-second).
Une fonction CAST(c AS VARCHAR(2)) explicite tronquera toujours la chaîne de caractères. Une fonction CAST(n AS NUMERIC(p)) explicite exécutera toujours la conversion ou éliminera le superflu si n est en dehors des limites. (Explicit CAST(n AS NUMERIC(p)) will always perform the conversion or throw if n is out of bounds.) Toutes les autres conversions implicites et explicites de CHAR(n) et VARCHAR(n) sont assujetties aux règles du standard SQL.
Les expressions ALL et ANY
Full support for ALL(SELECT ....) and ANY(SELECT ....) with comparison operators: =, >, <, <>, >=, <=
Example:
SELECT ... WHERE <value expression> >= ALL(SELECT ...)
LIMIT and OFFSET
New alternative syntax for LIMIT at the end of the query:
LIMIT L [OFFSET O]
It is now possible to use LIMIT combined with ORDER BY in subqueries and SELECT statements in brackets that are terms of UNION or other set operations.
An ORDER BY or LIMIT clause applies to the complete result of the UNION and other set operations or alternatively to one of its components depending on how parentheses are used. In the first example the scope is the second SELECT, while in the second query, the scope is the result of the UNION.
SELECT ... FROM ... UNION
(SELECT ... FROM ... ORDER BY .. LIMIT)
SELECT ... FROM ... UNION
SELECT ... FROM ... ORDER BY .. LIMIT
Support for ORDER BY, LIMIT and OFFSET in CREATE VIEW statements
COLLATIONS
Each database can have its own collation. The SQL command below sets the collation from the set of collations in the source for org.hsqldb.Collation:
SET DATABASE COLLATION <double quoted collation name>
The command has an effect only on an empty database. Once it has been issued, the database can be opened in any JVM locale and will retain its collation.
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.
NAME RESOLUTION IN QUERIES
Parsing enhancements allow all reserved SQL words to be used as identifiers when double-quoted and then used in queries. E.g. CREATE TABLE "TABLE" ("INTEGER" INTEGER)
Enhancements to resolve column and table aliases used in query conditions.
ENHANCEMENTS
Since 1.7.3, the evaluation of BOOLEAN expressions has changed to conform to SQL standards. Any such expression can be TRUE, FALSE, or UNDEFINED. The UNDEFINED result is equivalent to NULL.
Optional changed behaviour of transactions in the default READ UNCOMMITTED mode. When a database property, sql.tx_no_multi_write=true has been set, a transaction is no longer allowed to delete or update a row that has already been updated or added by another uncommitted transaction.
Support for correct casting of TIME into TIMESTAMP, using CURRENT_DATE
BUG FIXES
Fixed reported bug with NOT LIKE and null values
Fixed bug with OR conditions in OUTER JOIN
Fixed bug with duplicated closing of prepared statements
Fixed various parsing anomalies where SQL commands were accepted when quoted, double-quoted or prefixed with an identifier, or identifiers were accepted in single quotes. Example of a command that is no-longer tolerated:
Malformed query: MY. "SELECT" ID FROM 'CUSTOMER' IF.WHERE ID=0;
Actual query: SELECT ID FROM CUSTOMER WHERE ID=0;
Fixed issue with illegal user names
Fixed TEXT table implementation to maintain the commit status of rows during recovery from an abrupt shutdown. Uncommitted changes will not appear in TEXT tables.
STORAGE AND PERSISTENCE IMPROVEMENTS
New connection property for setting the default table type when CREATE TABLE is used. The connection property, hsqldb.default_table_type=cached will set the default to CACHED tables, or the SET PROPERTY command can be used. Values, "cached" and "memory" are allowed.
Improved support for text tables. Newline support in quoted fields is now complete. It is now possible to save and restore the first line header of a CSV file when ignore_first=true is specified. When a text table is created with a new source (CSV) file, and ignore_first=true has been specified the following command can be used to set a user defined string as the first line:
SET TABLE SOURCE HEADER <double quoted string>.
A new application log has been introduced as an optional feature. The property/value pair "hsqldb.applog=1" can be used in the first connection string to log some important messages. The default is "hsqldb.applog=0", meaning no logging. A file with the ending ".app.log" is generated alongside the rest of the database files for this purpose.
In the current version, only the classes used for file persistence, plus any error encountered while processing the .log file after an abnormal end, are logged.
Note that the JDBC driver and the engine for 1.8.0 cannot be mixed with those of earlier versions in client/server setup. Check your classpaths and use the same version of the engine for both client and server.
New property for larger data file limits is introduced. Once set, the limit will go up to 8GB. The property can be set with the following SQL command only when the database has no tables (new database).
SET PROPERTY "hsqldb.cache_file_scale" 8
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
New property allows a CHECKPOINT DEFRAG to be performed automatically whenever CHECKPOINT is performed internally or via a user command.
SET CHECKPOINT DEFRAG n
The parameter n is the megabytes of abandoned space in the .data file. When a CHECKPOINT is performed either as a result of the .log file reaching the limit set by "SET LOGSIZE m", or by the user issuing a CHECKPOINT command, the amount of space abandoned during the session is checked and if it is larger than n, a CHECKPOINT DEFRAG is performed instead of a checkpoint.
Rewrite of log and cache handling classes, including:
New deleted block manager with more efficient deleted block reuse.
Faster log processing after an abnormal termination.
Better checks when maximum data file size is reached.
Better recovery when maximum data file size is reached.
Support for the res: connection protocol (database files in a jar) has been
extended to allow CACHED tables.
JDBC AND OTHER ENHANCEMENTS
ResultSetMetaData reports identical precision/scale in embedded and client/server modes
When PreparedStatement.setTimestamp() and ResultSet.getTimestamp() are used with a Calendar parameter, the result is symmetrical if the time zones are equal.
Added public shutdown() method to Server.
Enhancements to DatabaseManagerSwing and SqlTool
BUG FIX
Fixed bug where two indexes where switched, causing wrong results in some queries in the following circumstances:
CREATE TABLE is executed.
ALTER TABLE ADD FORIEGN KEY is used to create an FK on a different table that was already present when the first command was issued.
CREATE INDEX is used to add an index.
Data is added to the table.
Database is shutdown.
Database is restarted.
At this point the indexes are switched and queries that use either of the indexes will not return the correct set of rows. Ifst command was issued.
CREATE INDEX is used to add an index.
Data is added to the table.
Database is shutdown.
Database is restarted.
At this point the indexes are switched and queries that use either of the indexes will not return the correct set of rows. If data is not added prior to the first shutdown, the database will works as normal.
UPGRADING DATABASES
Databases that do not contain CACHED tables can be opened with the new version. For databases with CACHED tables, if they are created with versions 1.7.2 or 1.7.3, the SHUTDOWN SCRIPT command should be run once on the database prior to opening with the new version. For databases created with earlier versions, the instructions in the Advanced Topics section of The Guide should be followed.
OPEN OFFICE INTEGRATION
When used in OpenOffice.org as the default database, several defaults and properties are set automatically:
CREATE TABLE ... defaults to CREATE CACHED TABLE ...
hsqldb.cache_scale=13
hsqldb.cache_size_scale=8
hsqldb.log_size=10
SET WRITE DELAY 2
sql.enforce_strict_size=true
