I have been working on porting the database creation SQL scripts to
MySQL 5.x (with a few delays caused by other projects). So far, I've
completed awl/dba/awl-tables.sql and awl/dba/schema-management.sql, and
am currently working on davical/dba/davical.sql. The biggest issues
I've encountered that could affect functionality are the limitations of
using TEXT columns with indexes and no built-in mechanisms for storing
time zone information. Here are my notes on the issues I've encountered
so far:
1. MySQL doesn't support storing time zone information in TIMESTAMP,
DATETIME, and DATE data types, and the TIMESTAMPTZ data type doesn't
exist. This affects all table columns declared as TIMESTAMPTZ or
TIMESTAMP WITH TIME ZONE. I haven't dug through the source code yet to
see how the time zone information is used.
2. MySQL requires special handling for TEXT columns used in indexes,
which includes primary key columns and UNIQUE constraints. The total
size of the index keys must be 1000 bytes or less (thats bytes, not
characters). For TEXT keys, a size must be specified in the PRIMARY KEY
or UNIQUE statements. This size specifies how much of the beginning of
each string is used to build the index. For example, using
"UNIQUE(user_no,dav_name(200))" in the collection table definition
(davical/dba/davical.sql) would mean that only the first 200 characters
in the dav_name column are considered to be unique.
TEXT columns are very convenient because they can store arbitrarily long
strings. However, they also have a significant impact on query
performance, especially when used as index keys. VARCHAR strings also
provide the variable-length storage benefits of TEXT strings and can be
initialized with DEFAULT values (see #8, below), but do have the cost of
a fixed maximum length. My suggestion is to use VARCHAR strings in
place of TEXT wherever the columns are used as index keys.
3. MySQL does not allow stored functions to return VOID. For example,
see the new_db_revision() function declared in
awl/dba/schema-management.sql. I changed it to return the calculated
new_id value (i.e. INT).
4. MySQL doesn't have a mechanism similar to the RAISE EXCEPTION
statement in PostgreSQL. There is a way to emulate it by trying to
execute a prepared statement containing an invalid SQL statement. This
generates an error with the same transaction rollback affects as the
RAISE EXCEPTION statement.
5. MySQL doesn't support the ARRAY data type. It is used for the
parameter to the privilege_to_bits() stored function in
davical/dba/caldav_functions.sql, which is only called from within
davical/dba/base-data.sql during the table data initialization. The PHP
source code uses its own equivalent PHP function. I haven't tackled how
to handle this one yet.
6. MySQL doesn't support PostgreSQL sequences and sequence manipulation
functions. This is easily emulated.
7. DEFAULT values in CREATE TABLE statements must be constants in MySQL
(i.e. no function calls allowed). This is fixed by adding the function
calls to the MySQL versions of INSERT statements that otherwise rely
upon the default values in the PostgreSQL versions.
8. MySQL doesn't allow DEFAULT values for TEXT columns in CREATE TABLE
statements. This is fixed the same way as #7, or by changing the data
type to a VARCHAR string.
Thoughts/Comments?
//Jeff
MySQL 5.x (with a few delays caused by other projects). So far, I've
completed awl/dba/awl-tables.sql and awl/dba/schema-management.sql, and
am currently working on davical/dba/davical.sql. The biggest issues
I've encountered that could affect functionality are the limitations of
using TEXT columns with indexes and no built-in mechanisms for storing
time zone information. Here are my notes on the issues I've encountered
so far:
1. MySQL doesn't support storing time zone information in TIMESTAMP,
DATETIME, and DATE data types, and the TIMESTAMPTZ data type doesn't
exist. This affects all table columns declared as TIMESTAMPTZ or
TIMESTAMP WITH TIME ZONE. I haven't dug through the source code yet to
see how the time zone information is used.
2. MySQL requires special handling for TEXT columns used in indexes,
which includes primary key columns and UNIQUE constraints. The total
size of the index keys must be 1000 bytes or less (thats bytes, not
characters). For TEXT keys, a size must be specified in the PRIMARY KEY
or UNIQUE statements. This size specifies how much of the beginning of
each string is used to build the index. For example, using
"UNIQUE(user_no,dav_name(200))" in the collection table definition
(davical/dba/davical.sql) would mean that only the first 200 characters
in the dav_name column are considered to be unique.
TEXT columns are very convenient because they can store arbitrarily long
strings. However, they also have a significant impact on query
performance, especially when used as index keys. VARCHAR strings also
provide the variable-length storage benefits of TEXT strings and can be
initialized with DEFAULT values (see #8, below), but do have the cost of
a fixed maximum length. My suggestion is to use VARCHAR strings in
place of TEXT wherever the columns are used as index keys.
3. MySQL does not allow stored functions to return VOID. For example,
see the new_db_revision() function declared in
awl/dba/schema-management.sql. I changed it to return the calculated
new_id value (i.e. INT).
4. MySQL doesn't have a mechanism similar to the RAISE EXCEPTION
statement in PostgreSQL. There is a way to emulate it by trying to
execute a prepared statement containing an invalid SQL statement. This
generates an error with the same transaction rollback affects as the
RAISE EXCEPTION statement.
5. MySQL doesn't support the ARRAY data type. It is used for the
parameter to the privilege_to_bits() stored function in
davical/dba/caldav_functions.sql, which is only called from within
davical/dba/base-data.sql during the table data initialization. The PHP
source code uses its own equivalent PHP function. I haven't tackled how
to handle this one yet.
6. MySQL doesn't support PostgreSQL sequences and sequence manipulation
functions. This is easily emulated.
7. DEFAULT values in CREATE TABLE statements must be constants in MySQL
(i.e. no function calls allowed). This is fixed by adding the function
calls to the MySQL versions of INSERT statements that otherwise rely
upon the default values in the PostgreSQL versions.
8. MySQL doesn't allow DEFAULT values for TEXT columns in CREATE TABLE
statements. This is fixed the same way as #7, or by changing the data
type to a VARCHAR string.
Thoughts/Comments?
//Jeff