Deploying UltraLite schema upgrades

To make schema upgrades, use the SQL statement ALTER DATABASE SCHEMA FROM FILE.

The upgrade process

Caution

Do not reset a device during a schema upgrade. If you reset the device during a schema upgrade, data will be lost and the UltraLite database marked as "bad."

Both the new and existing database schemas are compared to see what differs.
The schema of the existing database is altered.
Rows that do not fit the new schema are dropped. For example:
- If you add a uniqueness constraint to a table and there are multiple rows with the same values, all but one row will be dropped.
- If you try to change a column domain and a conversion error occurs, then that row will be dropped. For example, if you have a VARCHAR column and convert it to an INT column and the value for a row is ABCD, then that row is dropped.
- If your new schema has new foreign keys where the foreign row does not have a matching primary row, these rows are dropped.
When rows are dropped, a SQLE_ROW_DROPPED_DURING_SCHEMA_UPGRADE (130) warning is raised.

To upgrade the UltraLite schema

Create a SQL script of DDL statements that define the new schema. The character set of the SQL script file must match the character set of the database you want to upgrade.

You should use either ulinit or ulunload to extract the DDL statements required for your script. By using these utilities with the following options, you ensure that the DDL statements are syntactically correct.
- If you are using ulunload, use the -n and -s [ file ] options.
- If you are using ulinit, use the -l [ file ] option.
If you do not use either ulunload or ulinit, review the script and ensure that:
- You do not rename tables, columns, or publications. RENAME operations are not supported. If you rename a table, it is processed as a DROP TABLE and CREATE TABLE operation.
- You have not included non-DDL statements. Including non-DDL statements may not have the effect you expect.
- Words in the SQL statement are separated by spaces.
- Only one SQL statement can appear in each line.
- Comments are prepended with double hyphens (–), and only occur at the start of a line.
Backup the database against which the upgrade will be performed.

Run the new statement. For example:

ALTER DATABASE SCHEMA FROM FILE 'MySchema.sql';

Error notification

Because Ultralite error callback is active during the upgrade process, you are notified of errors during the conversion process. For example, SQLE_CONVERSION_ERROR reports all values that could not be converted in its parameters. Errors do not mean the process failed. The final SQL code after the statement returns is a 130 warning in this case. These warnings describe operations of the conversion process and do not stop the upgrade process.