Deploying UltraLite database schema upgrades

UltraLite database schema upgrades can be deployed using one of the following techniques:

  • Individual DDL statements   For example, in UltraLite C, you can run the following statement to create a new publication: 
    dbconnection->ExecuteStatement("CREATE PUBLICATION p (table t)");

  • The ALTER DATABASE SCHEMA FROM FILE statement   This statement can be used to perform schema upgrades when you do not know the DDL statement requirements, or do not want to specify the individual DDL statements. The SQL file you use must contain the entire new schema.

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

UltraLite executes the following steps when you upgrade an UltraLite database schema with the ALTER DATABASE SCHEMA FROM FILE statement:

  1. Both the new and existing database schemas are compared to see what differs.

  2. The schema of the existing database is altered.

  3. 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 a row has the value 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.

  4. When rows are dropped, a SQLE_ROW_DROPPED_DURING_SCHEMA_UPGRADE (130) warning is raised.

 Upgrade an UltraLite database schema with the ALTER DATABASE SCHEMA FROM FILE statement

  1. Create a SQL script of DDL statements to create a completely new schema.

    You can keep a master schema on your development machine and update the schema as your application changes.

    Use either the ulinit or ulunload utilities 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:

    • For an UltraLite database, use the ulunload utility with the -n and -s [ schema-file ] options. For example: 

      ulunload -c dbf=mydatabase.udb -n -s MySchema.sql

    • For a SQL Anywhere database, use the ulinit utility with the -a and -l [ schema-file ] option. For example: 

      ulinit -a "dsn=mysqlanywheredatabase" -l MySchema.sql

    If you do not use the ulunload or ulinit utilities, review the script and ensure the following:

    • The script declares the entire desired schema with CREATE statements.

    • Tables, columns, and publications are not renamed. The RENAME operation is not supported. Renamed tables are processed as a DROP TABLE and CREATE TABLE operation.

    • There are no non-DDL statements, including non-DDL statements that may not have the effect you expect.

    • Words in the SQL statement are separated by spaces.

    • Only one SQL statement appears in each line.

    • Comments are prepended with double hyphens (-), and only occur at the start of a line.

    • Each statement is separated by a line containing exactly the word GO.

  2. Deploy the new SQL script file.

  3. Ensure that the database is synchronized.

  4. Run the new statement on the device. For example:

    ALTER DATABASE SCHEMA FROM FILE 'MySchema.sql';
 Error notification
 See also
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1