Some of the important points that I had jotted down while working on MySQL to HSQL Migration:
1. HSQL does not support the BLOB. We can use LONGVARBINARY. That is, an HSQLDB Blob object does not contain a logical pointer to SQL BLOB data; rather it directly contains a representation of the data (a byte array). Similarly, for 'mediumblob' we can use VARBINARY.
2. HSQL does not support the TEXT / MEDIUMTEXT. We can use LONGVARCHAR.
3. We can not create a column which is equivalent to a keyword without putting it inside escape (double) quotes. HSQL Syntax: CREATE TABLE test_table ( key_id VARCHAR(500) NOT NULL, first_col VARCHAR(50) NOT NULL, "position" BIGINT NOT NULL, third_col VARCHAR(50) NOT NULL, PRIMARY KEY (key_id) );
4. TINYINT, INT, BIGINT does not have precision support. We can not use the precision for the columns and using the data types as is without precision.
5. HSQL does not support column level privileges. UNSIGNED keyword is not supported. We can neglect this keyword.
6. HSQL does not support column level privileges. HSQL does not support the AUTO_INCREMENT. We can use IDENTITY keyword instead. An IDENTITY column is always treated as the primary key for the table (as a result, multi-column primary keys are not possible with an IDENTITY column present).
7. HSQL does not support column level privileges. HSQL (1.8.1) does not support the UNIQUE keyword in a column definition. But it does support unique constraints defined separately. HSQL Syntax: ALTER TABLE <tablename> ADD CONSTRAINT <constraintname> UNIQUE (username);
8. HSQL does not support column level privileges. HSQL does not support the DEFAULT key word in a column definition. But it does support default values to be defined separately. HSQL Syntax: ALTER TABLE <tablename> ALTER COLUMN <columnname> SET DEFAULT <defaultvalue>;
9. HSQL does not support column level privileges. HSQL does not support creation of indices using create table statement. We can create them separately. HSQL Syntax: CREATE INDEX <indexname> ON <tablename> (<columnname>, ...);
10. HSQL does not support "..IF NOT EXISTS.." clause for create table statement and “...IF EXISTS...” clause for drop table statement. We can simply attempt to create the table and catch the exception if it doesn't succeed, then coninue.
11. HSQL does not support "ENGINE, DEFAULT_CHARSET and ROW_FORMAT" options for create table statement. We will provide a work-around to support the same functionality or if not supported at all then will add our comments to the JIRA issue.
12. HSQL does not support insert statment variation such as: "INSERT INTO ... ON DUPLICATE KEY UPDATE". We will provide a work-around to support the same functionality or if not supported at all then will add our comments to the JIRA issue.
13. HSQL (1.8.1.2) does not support TRUNCATE keyword. But it is supported in versions 1.9 onwards. We can use DELETE FROM <tablename> for HSQL (1.8.1.2).
14. HSQL does not support the keyword IGNORE for insert statments.
15. We can use RAWTOHEX(str) method to test insertion of BLOB, CLOB fields. HSQL Syntax: INSERT INTO <tablename> VALUES (RAWTOHEX('SomeStringDataToBeConvertedToBinary'))
16. HSQL does not support LIMIT clause separately. But it does support the LIMIT clause within the SELECT clause itself. HSQL Syntax: SELECT LIMIT <offset> <length> <<columnname>, ... > FROM <tablename>
17. HSQL does not support statement USE <schemaname>. But it does support SET SCHEMA <schemaname> statement.
18. HSQL does not support create/drop of database. We can use create/drop schema directly. HSQL Syntax: CREATE SCHEMA <schemaname> AUTHORIZATION DBA; and DROP SCHEMA <schemaname> CASCADE;
19. HSQL does not support GRANT with wild-cards.
20. HSQL does not support “ALGORITHM=UNDEFINED DEFINER=<username> SQL SECURITY DEFINER” part for the create procedure syntax.
21. NOTE: Since HSQLDB is written in Java, it uses Java classes for its stored procs. Thus, potentially any "public static" java method can be configured as a stored procedure.
1. HSQL does not support the BLOB. We can use LONGVARBINARY. That is, an HSQLDB Blob object does not contain a logical pointer to SQL BLOB data; rather it directly contains a representation of the data (a byte array). Similarly, for 'mediumblob' we can use VARBINARY.
2. HSQL does not support the TEXT / MEDIUMTEXT. We can use LONGVARCHAR.
3. We can not create a column which is equivalent to a keyword without putting it inside escape (double) quotes. HSQL Syntax: CREATE TABLE test_table ( key_id VARCHAR(500) NOT NULL, first_col VARCHAR(50) NOT NULL, "position" BIGINT NOT NULL, third_col VARCHAR(50) NOT NULL, PRIMARY KEY (key_id) );
4. TINYINT, INT, BIGINT does not have precision support. We can not use the precision for the columns and using the data types as is without precision.
5. HSQL does not support column level privileges. UNSIGNED keyword is not supported. We can neglect this keyword.
6. HSQL does not support column level privileges. HSQL does not support the AUTO_INCREMENT. We can use IDENTITY keyword instead. An IDENTITY column is always treated as the primary key for the table (as a result, multi-column primary keys are not possible with an IDENTITY column present).
7. HSQL does not support column level privileges. HSQL (1.8.1) does not support the UNIQUE keyword in a column definition. But it does support unique constraints defined separately. HSQL Syntax: ALTER TABLE <tablename> ADD CONSTRAINT <constraintname> UNIQUE (username);
8. HSQL does not support column level privileges. HSQL does not support the DEFAULT key word in a column definition. But it does support default values to be defined separately. HSQL Syntax: ALTER TABLE <tablename> ALTER COLUMN <columnname> SET DEFAULT <defaultvalue>;
9. HSQL does not support column level privileges. HSQL does not support creation of indices using create table statement. We can create them separately. HSQL Syntax: CREATE INDEX <indexname> ON <tablename> (<columnname>, ...);
10. HSQL does not support "..IF NOT EXISTS.." clause for create table statement and “...IF EXISTS...” clause for drop table statement. We can simply attempt to create the table and catch the exception if it doesn't succeed, then coninue.
11. HSQL does not support "ENGINE, DEFAULT_CHARSET and ROW_FORMAT" options for create table statement. We will provide a work-around to support the same functionality or if not supported at all then will add our comments to the JIRA issue.
12. HSQL does not support insert statment variation such as: "INSERT INTO ... ON DUPLICATE KEY UPDATE". We will provide a work-around to support the same functionality or if not supported at all then will add our comments to the JIRA issue.
13. HSQL (1.8.1.2) does not support TRUNCATE keyword. But it is supported in versions 1.9 onwards. We can use DELETE FROM <tablename> for HSQL (1.8.1.2).
14. HSQL does not support the keyword IGNORE for insert statments.
15. We can use RAWTOHEX(str) method to test insertion of BLOB, CLOB fields. HSQL Syntax: INSERT INTO <tablename> VALUES (RAWTOHEX('SomeStringDataToBeConvertedToBinary'))
16. HSQL does not support LIMIT clause separately. But it does support the LIMIT clause within the SELECT clause itself. HSQL Syntax: SELECT LIMIT <offset> <length> <<columnname>, ... > FROM <tablename>
17. HSQL does not support statement USE <schemaname>. But it does support SET SCHEMA <schemaname> statement.
18. HSQL does not support create/drop of database. We can use create/drop schema directly. HSQL Syntax: CREATE SCHEMA <schemaname> AUTHORIZATION DBA; and DROP SCHEMA <schemaname> CASCADE;
19. HSQL does not support GRANT with wild-cards.
20. HSQL does not support “ALGORITHM=UNDEFINED DEFINER=<username> SQL SECURITY DEFINER” part for the create procedure syntax.
21. NOTE: Since HSQLDB is written in Java, it uses Java classes for its stored procs. Thus, potentially any "public static" java method can be configured as a stored procedure.
public class HSQLAllStoredProcs {
public static void proc_Int(Integer i1) {
}
public static void proc_IntInt(Integer i1, Integer i2) {
}
public static void proc_IntDate(Integer i1, Date d1) {
}
public static void proc_LongDate(Long l1, Date d1) {
}
}
CREATE ALIAS load_proc_IntDate FOR "com.mycompany.dao.sp.HSQLAllStoredProcs.proc_IntDate";| MySQL Datatype | HSQL (1.8.1.2) Datatype | Oracle (10 g) Datatype | MS SQL 2005 Datatype |
| INT | INT | INT | INT |
| INT(PRECISION) | INT | NUMBER(PRECISION) | INT |
| VARCHAR | VARCHAR | VARCHAR | VARCHAR |
| VARCHAR(PRECISION) | VARCHAR(PRECISION) | VARCHAR(PRECISION) | VARCHAR(PRECISION) |
| CHAR | CHAR | CHAR | CHAR |
| MEDIUMTEXT | LONGVARCHAR | CLOB | NVARCHAR(MAX) |
| TEXT | LONGVARCHAR | CLOB | TEXT |
| DATETIME | DATETIME | TIMESTAMP | DATETIME |
| TIMESTAMP | TIMESTAMP | TIMESTAMP | TIMESTAMP |
| TINYINT | TINYINT | TINYINT | TINYINT |
| TINYINT(PRECISION) | TINYINT | NUMBER(PRECISION) | TINYINT(PRECISION) |
| BIGINT | BIGINT | BIGINT | BIGINT |
| BIGINT(PRECISION) | BIGINT | NUMBER(PRECISION) | BIGINT(PRECISION) |
| MEDIUMBLOB | VARBINARY | BLOB | VARBINARY(MAX) |
| BLOB | LONGVARBINARY | BLOB | VARBINARY(MAX) |
| BOOLEAN | BOOLEAN | NUMBER(1) | TINYINT |
| BOOLEAN DEFAULT TRUE | BOOLEAN | NUMBER(1) DEFAULT 1 | TINYINT DEFAULT 1 |
| VARCHAR(4096) - Precision more than 4000 | VARCHAR(4096) | CLOB | VARCHAR(4096) |
| UNSIGNED | -- NA -- There is no discrimination between SIGNED and UNSIGNED numbers. | -- NA -- There is no discrimination between SIGNED and UNSIGNED numbers. | -- NA -- There is no discrimination between SIGNED and UNSIGNED numbers. |
| DEFAULT now() | CURRENT_TIMESTAMP | DEFAULT SYSTIMESTAMP | -- NA -- We cannot set to default value for TIMESTAMP fields. SQL Error: Defaults cannot be created on columns of data type timestamp |
| ON DELETE RESTRICT (while defining foreign contraint) | ON DELETE RESTRICT | -- NA -- Default behavior provided by Oracle | -- NA -- Default behavior provided by MS SQL |
| NOT NULL DEFAULT '' | NOT NULL DEFAULT '' | DEFAULT '' | NOT NULL DEFAULT '' |
| TIMESTAMP DEFAULT '0000-00-00 00:00:00' | CURRENT_TIMESTAMP | TIMESTAMP DEFAULT SYSTIMESTAMP | -- NA -- We cannot set to default value for TIMESTAMP fields. SQL Error: Defaults cannot be created on columns of data type timestamp |
| BEGIN TRANSACTION | -- NA -- Stored procedures are not supported using SQL syntax in HSQL. | -- NA -- Transactions are inbuilt in Oracle | BEGIN TRANSACTION |
4 comments:
Thank you for sharing this information. It was very useful for me.
Hey, guys!
Thanks for this!
But what about foreign keys syntax?
Good post but you should add the versions for which some of the comments. Recent HSQL does support the creation of a table if does not exist: "CREATE TABLE IF NOT EXISTS"
Thank you very much. You really saved lot of my time.
Post a Comment