☰
Navigation :

Simple Migration Script Generator Migrator

alt text alt text alt text

Demo application screenshots for this article
SimpleMigrationScriptGenerator Migrator is a simple migration utility that tries to generate migration script based on differences between current and new schema and save as many data as possible. It is implemented by KittySimpleMigrator.class that uses net.akaish.kitty.orm.util.KittySimpleMigrationScriptGenerator for generating migration script. KittySimpleMigrationScriptGenerator is capable only to delete and create new indexes and table as well as adding or deleting columns in existing tables, so use it only when you’re sure that there are no constraints defined that can be violated with script created by this utility class. KittySimpleMigrationScriptGenerator gets list of existing tables, table columns and indexes of current database and compares it to new schema tables, table columns and indexes list, then generates statements to drop redundant tables, indexes and table columns, statements to alter existing tables and statements to create new tables and indexes. Due to SQLite ALTER restrictions, a lot of operations, for example, operation of deleting redundant column in existing table would be not ALTER statement but list of statements: renaming old table, creating new version of table from new schema, inserting values from old table to new one.

To use SimpleMigrationScriptGenerator Migrator you should set onUpgradeBehavior property value of @KITTY_DATABASE_HELPER to KITTY_DATABASE_HELPER.UpgradeBehavior.USE_SIMPLE_MIGRATIONS: 

 1@KITTY_DATABASE(
 2        isLoggingOn = true,
 3        isProductionOn = false,
 4        isKittyDexUtilLoggingEnabled = false,
 5        logTag = MigrationDBv3.LTAG,
 6        databaseName = "mig",
 7        databaseVersion = 3,
 8        domainPackageNames = {"net.akaish.kittyormdemo.sqlite.migrations.migv3"}
 9)
10@KITTY_DATABASE_REGISTRY(
11        domainModels = {
12                net.akaish.kittyormdemo.sqlite.migrations.migv3.MigOneModel.class,
13                net.akaish.kittyormdemo.sqlite.migrations.migv3.MigTwoModel.class,
14                net.akaish.kittyormdemo.sqlite.migrations.migv3.MigThreeModel.class
15        }
16)
17@KITTY_DATABASE_HELPER(
18        onUpgradeBehavior = KITTY_DATABASE_HELPER.UpgradeBehavior.USE_SIMPLE_MIGRATIONS
19)
20public class MigrationDBv3 extends KittyDatabase {
21    ...
22}

Mig v.3

MigOneModel (mig_one)

Java type Name SQLite name Constraints
Long id id PRIMARY KEY
String creationDate creation_date NOT_NULL DEFAULT(CURRENT_DATE)
Integer defaultInteger default_integer DEFAULT(228)

Index on default_integer.

MigTwoModel (mig_two)

Java type Name SQLite name Constraints
Long id id PRIMARY KEY
Long migOneReference mig_one_reference FOREIGN KEY reference on mig_one.id
Animals someAnimal some_animal -
AnimalSounds someAnimalSound some_animal_sound -

MigThreeModel (mig_three)

Java type Name SQLite name Constraints
Long id id PRIMARY KEY
String someValue some_value DEFAULT(“Something random”)

v.2 -> v.3 diffs

  1. - column mig_one.current_timestamp
  2. + column mig_two.someAnimalSound
  3. + table mig_three
  4. - index on mig_two (some_animal)

Create schema script generated by KittyORM for database mig version 3 

CREATE TABLE IF NOT EXISTS mig_three (id INTEGER NOT NULL PRIMARY KEY ASC, some_value TEXT NOT NULL DEFAULT 'Something random');

CREATE TABLE IF NOT EXISTS mig_one (id INTEGER NOT NULL PRIMARY KEY ASC, creation_date TEXT NOT NULL DEFAULT  CURRENT_DATE , default_integer INTEGER DEFAULT 228);

CREATE INDEX IF NOT EXISTS m1_di_index ON mig_one (default_integer);

CREATE TABLE IF NOT EXISTS mig_two (id INTEGER NOT NULL PRIMARY KEY ASC, mig_one_reference INTEGER REFERENCES mig_one (id) ON UPDATE CASCADE ON DELETE CASCADE, some_animal TEXT, some_animal_sound TEXT);
Drop schema script generated by KittyORM for database mig version 3 
DROP TABLE IF EXISTS mig_one;

DROP TABLE IF EXISTS mig_two;

DROP TABLE IF EXISTS mig_three;
Migration script generated by KittyORM for database mig from version 2 to version 3 (SimpleMigrationScriptGenerator migrator) 
ALTER TABLE mig_one RENAME TO mig_one_t_old;

CREATE TABLE IF NOT EXISTS mig_one (id INTEGER NOT NULL PRIMARY KEY ASC, creation_date TEXT NOT NULL DEFAULT  CURRENT_DATE , default_integer INTEGER DEFAULT 228);

INSERT INTO mig_one (id, creation_date, default_integer) SELECT id, creation_date, default_integer FROM mig_one_t_old;

DROP TABLE IF EXISTS mig_one_t_old;

CREATE INDEX IF NOT EXISTS m1_di_index ON mig_one (default_integer);

ALTER TABLE mig_two ADD COLUMN some_animal_sound;

CREATE TABLE IF NOT EXISTS mig_three (id INTEGER NOT NULL PRIMARY KEY ASC, some_value TEXT NOT NULL DEFAULT 'Something random');

DROP INDEX IF EXISTS mig.m2_sa_index;
Migration script generated by KittyORM for database mig from version 1 to version 3 (SimpleMigrationScriptGenerator migrator) 
ALTER TABLE mig_one RENAME TO mig_one_t_old;

CREATE TABLE IF NOT EXISTS mig_one (id INTEGER NOT NULL PRIMARY KEY ASC, creation_date TEXT NOT NULL DEFAULT  CURRENT_DATE , default_integer INTEGER DEFAULT 228);

INSERT INTO mig_one (id, creation_date) SELECT id, creation_date FROM mig_one_t_old;

DROP TABLE IF EXISTS mig_one_t_old;

CREATE INDEX IF NOT EXISTS m1_di_index ON mig_one (default_integer);

CREATE TABLE IF NOT EXISTS mig_two (id INTEGER NOT NULL PRIMARY KEY ASC, mig_one_reference INTEGER REFERENCES mig_one (id) ON UPDATE CASCADE ON DELETE CASCADE, some_animal TEXT, some_animal_sound TEXT);

CREATE TABLE IF NOT EXISTS mig_three (id INTEGER NOT NULL PRIMARY KEY ASC, some_value TEXT NOT NULL DEFAULT 'Something random');