Database migration

Database version migration is when you need to upgrade the database from a version to another. Kripton support version migration step 1 with SQLiteUpdateTask implementations. Kripton allows generating schemas. Every schema version is stored as .sql file in schemas folder.

Kripton has a jar library named kripton-sqlite-test-library that help a developer to test database schema version migration.

In the library kripton-sqlite-test-library, there are needed utilities and methods to write test needed to migrate between versions.

Let suppose that we have a School data-source for which we want to migrate from version 1 to version 2.

@BindDataSource(fileName = "school", version = 2, daoSet = { DaoProfessor.class, DaoSeminar.class, DaoSeminar2Student.class, DaoStudent.class }, schema=true)
public interface SchoolDataSource {

}

For every version, in schemas folder, there is one schema definition for each version.

school_schema_1.sql school_schema_2.sql

Each file contains all the DDL code to create the database at specific version. In our example, the schema version 2 is so defined:

CREATE TABLE seminar_2_student (id INTEGER PRIMARY KEY AUTOINCREMENT, student_id INTEGER, seminar_id INTEGER, FOREIGN KEY(student_id) REFERENCES student(id), FOREIGN KEY(seminar_id) REFERENCES seminar(id)); 
CREATE UNIQUE INDEX idx_seminar_2_student_0 on seminar_2_student (student_id asc, seminar_id desc);
CREATE TABLE professor (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, birth_date TEXT, surname TEXT NOT NULL);
CREATE INDEX idx_professor_0 on professor (surname);
CREATE TABLE student (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, location TEXT);
CREATE TABLE seminar (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, location TEXT);

The unit test that you can write can be similar to:

SQLiteTestUtils.resetDataSourceInstance(BindSchoolDataSource.class);

BindSchoolDataSource
  .build(DataSourceOptions.builder()
    .addUpdateTask(2, new FileInputStream("schemas/school_update_1_2.sql"))
    .build());
SQLiteTestUtils.forceDataSourceSchemaUpdate(BindSchoolDataSource.instance(), 2);
SQLiteTestUtils.verifySchema(BindSchoolDataSource.instance(), new FileInputStream("schemas/school_schema_2.sql"));

The class SQLiteTestUtils contains all methods needed to test database schema migrations.

All code used in *.sql is standard SQL. No JQL code is allowed in there situations.

It's important to mark some useful methods of SQLiteTestUtils:

• verifySchema: verify that current database schema is the same that is passed as method's parameter.
• getAllTable: retrieve all table presents in database
• getAllIndexes: retrieve all table presents in database
• renameAllTablesWithPrefix: rename all table with the specified prefix
• executeSQL: execute SQL code, via code or via file
• forceSchemaUpdate: force database to be opened with the specified version