Filescript Migrator

Demo application screenshots for this article

Table of contents

1. Using file scripts with KittyORM
2. Using Filescript Migrator for updating your schema

Using file scripts with KittyORM

KittyORM supports running SQLite scripts from files stored in assets or at device file system. This feature can be used for storing create and drop scripts. Also, you can define after create and after update scripts to run. All those scripts should comply two following rules:

1. Each statement should be written on its own line at text file. One statement is one line that ends with ; character.
2. KittyORM supports one line comments.

1-- Some comment here
2CREATE TABLE IF NOT EXISTS mig_four (id INTEGER NOT NULL PRIMARY KEY ASC, mig_three_reference INTEGER NOT NULL REFERENCES mig_three (id) ON UPDATE NO ACTION ON DELETE NO ACTION, mig_two_reference INTEGER NOT NULL REFERENCES mig_two (id) ON UPDATE NO ACTION ON DELETE NO ACTION, creation_date INTEGER NOT NULL DEFAULT  CURRENT_DATE );
3CREATE TABLE IF NOT EXISTS mig_three (id INTEGER NOT NULL PRIMARY KEY ASC, new_sv_name TEXT NOT NULL DEFAULT 'Something random', random_long INTEGER DEFAULT 22);
4
5-- Also blank lines as well as lines that contain only whitespaces are OK (they would be skipped)
6
7CREATE INDEX IF NOT EXISTS m3_rnd_long ON mig_three (random_long);
8CREATE TABLE IF NOT EXISTS mig_two (id INTEGER NOT NULL PRIMARY KEY ASC, mig_one_reference INTEGER, some_animal TEXT, some_animal_sound TEXT);

By default, you can specify following scripts that can be run at your database lifetime:

1. Create script - you can define your script that would be used for schema generation at schema creation. You can specify path to create schema script at createScriptPath property of @KITTY_DATABASE_HELPER annotation.
2. Drop script - you can define your script that should be used for dropping schema. You can specify path to drop schema script at dropScriptPath property of @KITTY_DATABASE_HELPER annotation.
3. After create script - you can run some SQLite script right after creation of your schema. It is useful in cases when you would like to fill your database with some initial data. You can specify path to after create schema script at afterCreateScriptPath property of @KITTY_DATABASE_HELPER annotation.
4. After migrate script - same as after create script but this script will be run after schema upgrade at onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion) method call of KittyDatabaseHelper or its implementation. Use it for same purposes as after create script: in situations when you need to fill your database with some initial data set.

Also, notice that you have no need to explicitly specify location of those scripts, you may just locate them at default location and name them with use if default naming pattern. Default location for those scripts is path kittysqliteorm/{sql_database_name}, and default naming pattern for files is {0}-v-{1}-{2}.sql where {0} is schema name, {1} is schema version and {2} is script type (create, drop, after_create or after_migrate). So, for example, for database used in this lesson for schema version 4 default naming of after create script would be file:///android_asset/kittysqliteorm/mig/mig-v-4-after_create.sql.

However, if you want to explicitly define locations of those scripts try to avoid absolute paths and use instead following uries: KittyNamingUtils.ASSETS_URI_START, KittyNamingUtils.INTERNAL_MEM_URI_START and KittyNamingUtils.EXTERNAL_MEM_URI_START. This would force KittyORM to use asset locations or to acquire base path with ctx.getFilesDir() or ctx.getExternalFilesDir() methods. See example of explicit definition of those scripts locations:

 1@KITTY_DATABASE(
 2    ...
 3)
 4@KITTY_DATABASE_REGISTRY(
 5    ...
 6)
 7@KITTY_DATABASE_HELPER(
 8        onUpgradeBehavior = KITTY_DATABASE_HELPER.UpgradeBehavior.USE_FILE_MIGRATIONS,
 9        migrationScriptsPath = KittyNamingUtils.INTERNAL_MEM_URI_START + "kittysqliteorm/mig/version_migrations",
10        // Defining create script location at external memory
11        createScriptPath =  KittyNamingUtils.EXTERNAL_MEM_URI_START + "one/script/at/sd/card_for_create.sql",
12        // Defining drop script location at internal memory
13        dropScriptPath = KittyNamingUtils.INTERNAL_MEM_URI_START + "and_one/at_internal/memory/to_drop.sql",
14        // Defining after migrate script location at assets
15        afterMigrateScriptPath = KittyNamingUtils.ASSETS_URI_START + "and/after_migrate/script_at_assets.sql",
16        // Defining after create script location at internal memory
17        afterCreateScriptPath = KittyNamingUtils.INTERNAL_MEM_URI_START + "should/be_reference/to_lord_of_the_rings/or_rhyme/but_no.sql"
18)
19public class MigrationDBv4 extends KittyDatabase {
20    ...
21}

Using Filescript Migrator for updating your schema

As mentioned in lesson 5.1: Migrations overview KittyORM provides you some utilities for migration between different schema versions. One of these utilities is Filescript Migrator that uses stored at asset directory or at file system scripts that would be applied to your database at onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion) method call of KittyDatabaseHelper or its implementation when there would be a need to update your schema. Filescript Migrator checks set of files named on one pattern and run SQLite scripts stored in at defined path if such migration sequence is applicable for new schema version. Implemented by KittyORMVersionFileDumpMigrator.class. To set this behavior you have to set onUpgradeBehavior property value of @KITTY_DATABASE_HELPER to KITTY_DATABASE_HELPER.UpgradeBehavior.USE_FILE_MIGRATIONS. By default, as migration root would be used scripts located at path kittysqliteorm/{sql_database_name}/version_migrations, however you can define custom path by setting migrationScriptsPath property of @KITTY_DATABASE_HELPER.

Filescript Migrator takes file list from migration directory and tries to create from this list migration sequence. It requires those files to be named with usage of special pattern: {0}-{1}-{2}-{3}.sql, where {0} is min lower, {1} is min upper, {2} is max lower and {3} is max upper schema version numbers. Filescript Migrator would try to create inseparable sequence of scripts based on those numbers to be applied to upgrade your schema from current version to new. In simple situation, for example, to be able upgrade your database awesome_db with Filescript Migrator from version 4 to 6 you should have at migration directory following files: 4-4-5-5.sql and 5-5-6-6.sql.

 1@KITTY_DATABASE(
 2    ...
 3)
 4@KITTY_DATABASE_REGISTRY(
 5    ...
 6)
 7@KITTY_DATABASE_HELPER(
 8        // Defining onUpgrade behavior
 9        onUpgradeBehavior = KITTY_DATABASE_HELPER.UpgradeBehavior.USE_FILE_MIGRATIONS,
10        // Setting migrations root path
11        migrationScriptsPath = KittyNamingUtils.EXTERNAL_MEM_URI_START + "kittysqliteorm/mig/version_migrations",
12)
13public class MigrationDBv4 extends KittyDatabase {
14    ...
15}

To demonstrate Filescript Migrator, in this lesson we would migrate database mig from version 3 to 4. For those purposes we create at assets following path kittysqliteorm/mig/version_migrations that contains only one script: 3-3-4-4.sql. When you hit “CREATE MIG…” button following can happen (see KittyORM Demo):

1. If database not created yet, then it would be created using script located at KittyNamingUtils.ASSETS_URI_START + "kittysqliteorm/mig/mig-v-4-create.sql and after this would be applied script located at KittyNamingUtils.EXTERNAL_MEM_URI_START + "kittysqliteorm/mig/mig-v-4-after_create.sql.
2. If database already exists and has version code 4 then all data would be wiped and then tables in database would be filled with some randomly generated records.
3. If database already exists and has version code 3 then KittyORM will try to migrate from version 3 to version 4 using script located at KittyNamingUtils.ASSETS_URI_START + "kittysqliteorm/mig/version_migrations/3-3-4-4.sql. After this script applied, KittyORM will run after create script located at KittyNamingUtils.ASSETS_URI_START + "kittysqliteorm/mig/mig-v-4-after_migrate.sql.
4. If database already exists, but have version code lower than 3, then migration will fail, because file located at migration root can be used only for creating inseparable migration script sequence from version code 3 to version code 4.
5. If for some reason version code is higher than 4 then nothing would happen because fragment is not designed to work with database mig version 5 or higher.

Mig v.4

MigTwoModel (mig_two)

MigThreeModel (mig_three)

Index on random_long.

MigFourModel (mig_four)

v.3 -> v.4 diffs

1. - constraint FOREIGN KEY (mig_one_reference) reference on mig_one.id
2. - table mig_one
3. + table mig_four
4. = mig_three.some_value rename to mig_three.new_sv_name
5. + mig_three.random_long
6. + index on mig_three (random_long)

Create schema script for database mig version 4 (KittyNamingUtils.ASSETS_URI_START + "kittysqliteorm/mig/mig-v-4-create.sql)

1CREATE TABLE IF NOT EXISTS mig_four (id INTEGER NOT NULL PRIMARY KEY ASC, mig_three_reference INTEGER NOT NULL REFERENCES mig_three (id) ON UPDATE NO ACTION ON DELETE NO ACTION, mig_two_reference INTEGER NOT NULL REFERENCES mig_two (id) ON UPDATE NO ACTION ON DELETE NO ACTION, creation_date INTEGER NOT NULL DEFAULT  CURRENT_DATE );
2CREATE TABLE IF NOT EXISTS mig_three (id INTEGER NOT NULL PRIMARY KEY ASC, new_sv_name TEXT NOT NULL DEFAULT 'Something random', random_long INTEGER DEFAULT 22);
3CREATE INDEX IF NOT EXISTS m3_rnd_long ON mig_three (random_long);
4CREATE TABLE IF NOT EXISTS mig_two (id INTEGER NOT NULL PRIMARY KEY ASC, mig_one_reference INTEGER, some_animal TEXT, some_animal_sound TEXT);

1DROP TABLE IF EXISTS mig_four;
2DROP TABLE IF EXISTS mig_three;
3DROP TABLE IF EXISTS mig_two;

 1INSERT INTO mig_three (new_sv_name, random_long) VALUES ('AFTER CREATE SCRIPT', 1);
 2INSERT INTO mig_three (new_sv_name, random_long) VALUES ('AFTER CREATE SCRIPT', 2);
 3INSERT INTO mig_three (new_sv_name, random_long) VALUES ('AFTER CREATE SCRIPT', 3);
 4INSERT INTO mig_three (new_sv_name, random_long) VALUES ('AFTER CREATE SCRIPT', 4);
 5INSERT INTO mig_three (new_sv_name, random_long) VALUES ('AFTER CREATE SCRIPT', 5);
 6INSERT INTO mig_three (new_sv_name, random_long) VALUES ('AFTER CREATE SCRIPT', 6);
 7INSERT INTO mig_three (new_sv_name, random_long) VALUES ('AFTER CREATE SCRIPT', 7);
 8INSERT INTO mig_three (new_sv_name, random_long) VALUES ('AFTER CREATE SCRIPT', 8);
 9INSERT INTO mig_three (new_sv_name, random_long) VALUES ('AFTER CREATE SCRIPT', 9);
10INSERT INTO mig_three (new_sv_name, random_long) VALUES ('AFTER CREATE SCRIPT', 10);
11INSERT INTO mig_three (new_sv_name, random_long) VALUES ('AFTER CREATE SCRIPT', 11);
12INSERT INTO mig_three (new_sv_name, random_long) VALUES ('AFTER CREATE SCRIPT', 12);
13INSERT INTO mig_three (new_sv_name, random_long) VALUES ('AFTER CREATE SCRIPT', 13);

 1INSERT INTO mig_three (new_sv_name, random_long) VALUES ('AFTER MIGRATE SCRIPT', 1);
 2INSERT INTO mig_three (new_sv_name, random_long) VALUES ('AFTER MIGRATE SCRIPT', 11);
 3INSERT INTO mig_three (new_sv_name, random_long) VALUES ('AFTER MIGRATE SCRIPT', 111);
 4INSERT INTO mig_three (new_sv_name, random_long) VALUES ('AFTER MIGRATE SCRIPT', 1111);
 5INSERT INTO mig_three (new_sv_name, random_long) VALUES ('AFTER MIGRATE SCRIPT', 11111);
 6INSERT INTO mig_three (new_sv_name, random_long) VALUES ('AFTER MIGRATE SCRIPT', 111111);
 7INSERT INTO mig_three (new_sv_name, random_long) VALUES ('AFTER MIGRATE SCRIPT', 1111111);
 8INSERT INTO mig_three (new_sv_name, random_long) VALUES ('AFTER MIGRATE SCRIPT', 11111111);
 9INSERT INTO mig_three (new_sv_name, random_long) VALUES ('AFTER MIGRATE SCRIPT', 1111111);
10INSERT INTO mig_three (new_sv_name, random_long) VALUES ('AFTER MIGRATE SCRIPT', 111111);
11INSERT INTO mig_three (new_sv_name, random_long) VALUES ('AFTER MIGRATE SCRIPT', 11111);
12INSERT INTO mig_three (new_sv_name, random_long) VALUES ('AFTER MIGRATE SCRIPT', 1111);
13INSERT INTO mig_three (new_sv_name, random_long) VALUES ('AFTER MIGRATE SCRIPT', 111);
14INSERT INTO mig_three (new_sv_name, random_long) VALUES ('AFTER MIGRATE SCRIPT', 11);
15INSERT INTO mig_three (new_sv_name, random_long) VALUES ('AFTER MIGRATE SCRIPT', 1);

 1-- minus FK M2.migOneReference
 2ALTER TABLE mig_two RENAME TO mig_two_old_t;
 3CREATE TABLE IF NOT EXISTS mig_two (id INTEGER NOT NULL PRIMARY KEY ASC, mig_one_reference INTEGER, some_animal TEXT, some_animal_sound TEXT);
 4INSERT INTO mig_two (id, mig_one_reference, some_animal, some_animal_sound) SELECT id, mig_one_reference, some_animal, some_animal_sound FROM mig_two_old_t;
 5DROP TABLE IF EXISTS mig_two_old_t;
 6-- minus M1 table
 7DROP TABLE IF EXISTS mig_one;
 8-- plus M4 table
 9CREATE TABLE IF NOT EXISTS mig_four (id INTEGER NOT NULL PRIMARY KEY ASC, mig_three_reference INTEGER NOT NULL REFERENCES mig_three (id) ON UPDATE NO ACTION ON DELETE NO ACTION, mig_two_reference INTEGER NOT NULL REFERENCES mig_two (id) ON UPDATE NO ACTION ON DELETE NO ACTION, creation_date INTEGER NOT NULL DEFAULT  CURRENT_DATE );
10-- rename M3.some_value to M3.new_sv_name and add M3.random_long
11ALTER TABLE mig_three RENAME TO mig_three_old_t;
12CREATE TABLE IF NOT EXISTS mig_three (id INTEGER NOT NULL PRIMARY KEY ASC, new_sv_name TEXT NOT NULL DEFAULT 'Something random', random_long INTEGER DEFAULT 22);
13INSERT INTO mig_three (id, new_sv_name) SELECT id, some_value FROM mig_three_old_t;
14DROP TABLE IF EXISTS mig_three_old_t;
15-- add index on M3.random_long
16CREATE INDEX IF NOT EXISTS m3_rnd_long ON mig_three (random_long);

Back to table of contents ^