Database Schema Migration

Imagine you have a database with 20 to 500 tables. It is obvious that your database was developed step by step. At first it had 10 tables, then few more, then more and so on. During development you changed existing tables to add/drop/modify fields. Databases change over time, so it is very important that a DBMS is able to modify the structure of existing databases, even if the database is already populated with records.

Another common scenario when you need to be able to change the database structure during application development. Consider the following:

• You shipped Application 1.0 to 1,000 customers. Later you want to ship Application 2.0, and need to change the database structure.
• Application 2.0 should immediately recognize the first time it opens a database that this database is in the old format, perform changes to the structure and continue to work with the changed database as an Application 2.0 database.
• Application 2.0 should do nothing to a database that already is in the Application 2.0 format.

To simplify this data migration task, Valentina database provides the VDatabase.SchemaVersion property.

The following example demonstrates how you can design your code to be able open database in any older schema version and convert it to the current version.

Example

' You can setup constant with the current SchemaVersion.
kCurrentSchemaVersion = 3;
 
' -------------------------------------------------------------------------
' This function opens a database object given as VDatabase class
' and if the database has the old SchemaVersion then do cascade 
' conversion of database schema.
' Returns TRUE if conversion did happens.
OpenDatabase( db as VDatabase, dbPath as string )
    db.Open( dbPath )
 
    if( db.SchemaVersion < kCurrentSchemaVersion )  
       ConvertStructureToCurrentVersion( db ) 
    end if
sub end
 
' -------------------------------------------------------------------------
' Note that db can be as version 1, so version 2.
' This method is able convert both case-sensitive  into version 3.
' This function can be easy extended future to versions 4, 5, ...
ConvertStructureToCurrentVersion( db as VDatabase )
   if db.SchemaVersion == 1
       ConvertSchema_1_2( db )
   end if
 
   if db.SchemaVersion == 2
       ConvertSchema_2_3( db )
   end if
sub end 
 
' -------------------------------------------------------------------------
' This function convert db schema from version 1 to 2
ConvertSchema_1_2( db as VDatabase )
   ' Database Schema Change: 
   ...
 
   db.SchemaVersion = 2
   db.Flush()
end 
 
' -------------------------------------------------------------------------
' This function convert db schema from version 2 to 3
ConvertSchema_2_3( db as VDatabase )
   ' Database Schema Change: 
   ...
 
   db.SchemaVersion = 3
   db.Flush()
end 

Now we need to learn what to write inside of methods as ConvertSchema_1_2().

Let's remind that Valentina ADKs allow to develop code using several styles of coding. We will consider how to change the database schema for each of styles.

Working with SQL style of coding, you use mainly VDatabase class and its methods:

• VDatabase.SqlQuery()
• VDatabase.SqlExecute()
• VDatabase.SqlSelect()

to execute an SQL command or query. To change database structure it is enough just call such commands as:

• CREATE/DROP/ALTER TABLE
• CREATE/DROP/ALTER VIEW
• CREATE/DROP LINK

See Valentina SQL Reference for details.

Example

' This function convert db schema from version 1 to 2
ConvertSchema_1_2( db as VDatabase )
   ' Database Schema Change: 
 
   db.SqlExecute( "DROP TABLE T2" )
   db.SqlExecute( "CREATE TABLE T25( f1 INTEGER, f2 STRING(20) INDEXED )" )
 
   db.SchemaVersion = 2
   db.Flush()
end 

Working with API Style you still use VDatabase class, as well as other classes such as VTable, VField, VCursor, …

Valentina API has set of methods with natural names and location in class hierarchy to change the database structure, for example:

• Class VDatabase has methods:
  • CreateTable(), DropTable()
  • CreateLink(), DropLink()
• Class VTable has methods:
  • CreateField(), DropField, ChangeField()

and so on. So it is quite easy modify structure using API methods.

Example

' This function convert db schema from version 1 to 2
ConvertSchema_1_2( db as VDatabase )
   ' Database Schema Change: 
 
   db.DropTable( db.Table("T2") )
 
   tbl25 = db.CreateTable( "Table25" )   
   tbl25.CreateIntegerField( "f1" )
   tbl25.CreateStringField( "f2", 20, fIndexed )
 
   db.SchemaVersion = 2
   db.Flush()
end 

Class Style usually is used for application development, and usually changes of structure are needed only when a new version of application is developed.

When you use the Class style, you have a sub-class of the VDatabase class. Let it have the name “AccountingDatabase”.

The most important is that you need at first try to open the database as a VDatabase class object and, if needed, convert its structure, then close it. After this you can open it as AccountingDatabase object, so all CLASSES in your code will strictly correspond to the database structure on disk.

App.Open
    ' on start of application we create instance of AccountingDatabase,
    ' Here we assume that mydb is a property of MyApplication class.
    mydb = new AccountingDatabase
end
 
' -------------------------------------------------------------------------
App.OnFileOpen( dbPath as String )
    dim db as VDatabase
 
    ' Cast into VDatabase and try open it and convert if needed, then close.
    db = VDatabase( mydb ) 'cast into parent class
    OpenDatabase( db, dbPath )  
    db.Close()
 
    ' now open database using mydb object that knows classes.
    mydb.Open( dbPath )
end
 
' -------------------------------------------------------------------------
' This function converts the db schema from version 1 to 2
ConvertSchema_1_2( db as VDatabase )
   ' Database Schema Change: 
 
   db.DropTable( db.Table("T2") )
 
   tbl25 = db.CreateTable( "Table25" )   
   tbl25.CreateIntegerField( "f1" )
   tbl25.CreateStringField( "f2", 20, fIndexed )
 
   db.SchemaVersion = 2
   db.Flush()
end