Updating an existing database using script

[This is preliminary documentation and is subject to change.]

This section describes how to update Data Model Version of existing databases using SQL scripts and command line scripts. For example, when a new framework version is put to use.

Note
When updating data model version from version 4.20 to newer versions, it is recommended to use the Administration Module. The Administration Module was introduced in TOPICA 4.25 - but it is recommended to use version 4.27 to update data models (some bugs, that exist in 4.25, are fixed in 4.27).

Note

When updating data model version from version 4.20 to newer versions, it is recommended to use the Administration Module.

The Administration Module was introduced in TOPICA 4.25 - but it is recommended to use version 4.27 to update data models (some bugs, that exist in 4.25, are fixed in 4.27).

Updating data model version is typically done when an application is updated to a new framework version. Updating data version is not absolutely necessary. Because new framework versions are backward compatible with previous data models, it is possible to use a new framework version with databases in data previous data model versions - but the new functionality will not be available. Updating data model version is necessary in these situations:

To get the full functionality of the new framework.
The data model of an existing database is so old, that it is not supported by the new framework. From time to time, support for very old data model versions is dropped from the framework code in order to "clean up" the code. For example, in framework version 4.22, support for data model versions older than 4.17 was dropped. That means, that framework versions 4.22 and later will NOT support data model version 4.16 and earlier.

Caution
The update scripts described here are meant to update databases, that are built for "normal use" - e.g. the Patient database table is used to store patients. If earlier framework versions (below approx. version 4.22), it was possible to use the Patient database table for other purposes (case numbers, etc.). Updating such databases with these scripts will probably not work - some manual intervention will be necessary.

Caution

The update scripts described here are meant to update databases, that are built for "normal use" - e.g. the Patient database table is used to store patients.

If earlier framework versions (below approx. version 4.22), it was possible to use the Patient database table for other purposes (case numbers, etc.). Updating such databases with these scripts will probably not work - some manual intervention will be necessary.

This topic contains the following sections:

Scripts
Updating existing databases
Update scripts - data model version 4.22 and later
Update scripts - data model version 4.21 and earlier

Scripts

To locate the scripts, refer to Database Management Using Scripts.

Each new version of the framework comes with script(s), that may be used to update from the previous data model version AND several earlier data model versions - one version at a time. That is:

Framework version 4.18 comes with scripts to update data model 4.10->4.11, 4.11->4.12, ..., 4.16->4.17, 4.17->4.18.
Framework version 4.19 comes with scripts to update data model 4.10->4.11, 4.11->4.12, ..., 4.16->4.17, 4.17->4.18, 4.18->4.19.
Framework version 4.20 comes with scripts to update data model 4.10->4.11, 4.11->4.12, ..., 4.16->4.17, 4.17->4.18, 4.18->4.19, 4.19->4.20.
etc.

That is, each new framework version contains the update scripts from the previous framework version PLUS one new script to update from previous to the new data model version.

This might be confusing. Example:

You have 4 versions of the framework: 4.17, 4.18, 4.19, 4.20.
You have a database, that is currently in data model version 4.17. Ultimately you will want to have this database updated to data model 4.20, because you want to use the full functionality in the 4.20 framework.
BUT it is only possible to update data model one version at a time. Therefore, the first update will be 4.17-> 4.18.
Each of the frameworks 4.18, 4.19, and 4.20 contain a script to update 4.17->4.18. Which of these scripts should you use?

The 3 scripts to update data model version 4.17->4.18 will in principle be the same. In most cases it should not matter which script you use.

New versions of the framework may contain bugfixes to some of the earlier update steps. This will be a reason to use the newest version of the 4.17->4.18 update script (the script that comes with the newest framework available - in this case 4.20).
There has been some issues with bugs in the database create and update scripts in the past. There may be some situations, where using the script that came with the correponsing framework (in the above example the update script that came wih fraamework 4.17). will work better than the version from the newset framework.
It is difficult to give rules for which version of the an update script will work best, as it depends on which data model the database originally was created in.

Updating existing databases

As described above, if you need to update an existing database from a data model version older than the previous one, you must upgrade in several steps - one version at a time.

Say you have a database in data model version 4.19, and you need to upgrade it to 4.22 (because you need some new functionality introduced in 4.22) - then you will have to update data model version in these steps:

Update from 4.19 to 4.20
Update from 4.20 to 4.21
Update from 4.21 to 4.22

Caution
Even if updating is done using prefabricated scripts, it may be a complicated process. The update scripts require, that the database being updated contains exactly the objects that it is supposed to do, corresponding to its data model version number. If this is not the case, errors in the update process may occur. This could happen if the existing database lacks an object - either because of an error in the scripts that originally built this database, or because these objects have been deleted (or altered) afterwards for some reason. Carefully examine the output from the update process. Errors may leave the database partly converted (which may cause errors, when attempting to use this database). Refer to "best parctices" described in Update to new version (e.g. always have a backup before attempting to update the data model!).

Caution

Even if updating is done using prefabricated scripts, it may be a complicated process. The update scripts require, that the database being updated contains exactly the objects that it is supposed to do, corresponding to its data model version number. If this is not the case, errors in the update process may occur. This could happen if the existing database lacks an object - either because of an error in the scripts that originally built this database, or because these objects have been deleted (or altered) afterwards for some reason.

Carefully examine the output from the update process. Errors may leave the database partly converted (which may cause errors, when attempting to use this database). Refer to "best parctices" described in Update to new version (e.g. always have a backup before attempting to update the data model!).

Update scripts - data model version 4.22 and later

In release 4.22, a culture name is specified as a parameter to the scripts creating a new database - see creating new database on SQL Server 2008.

Depending on the culture specified as parameter, the database will contain different objects. For example, the number, name and contents of tables implementing lookup values for patient properties, will depend on culture.

Databases built in data model 4.21 and earlier are meant to be used in danish culture ("da-DK") only. Therefore, upgrading a database in data model 4.21 to data model 4.22 will result in a database containing the same objects as a database built in data model 4.22 for danish culture (from scratch).

As result, the process of updating from 4.21 to 4.22 is more complicated than earlier. For this reason, upgrading is done by running a command file from a command prompt (just like when creating databases).

Open a command prompt and navigate to the SQLServer2008 folder. In this command prompt, enter the command Update (with no parameters). The command prompt will display:

Update.cmd script

Copy

*******************************************************************************************************
Update - Update TOPICA database from previous data model version to current data model version
*******************************************************************************************************
Missing parameter(s) - expected:
        Server Username Password Database
Server:
        Servername (. for current machine).
Username:
        AdminLogin for the database in question.
Password:
        Password for the AdminLogin for the database in question.
Database:
        Database to update.

That is, the Update command takes 4 parameters:

1	Server	The naming of the server follows the rules used in SQL Server, including sqlcmd: If you are creating a database in the default SQL Server instance on the current computer, use ".". If you are creating a database on a named SQL Server instance on the current computer, use ".\<instancename>". If you are creating a database in the default SQL Server instance on some other computer, use "<servername>". If you are creating a database on a named SQL Server instance on some other computer, use "<servername>\<instancename>".
2	Username	When using Windows integrated authentication, use "*". May be used when you are installing on the current computer, or on some other computer in the same domain as the current computer. When using SQL Server login, enter the name of that login - typically "sa".
3	Password	When using Windows integrated authentication, use "*". When using SQL Server login, enter the password for that login.
4	Database	The name of database to update. Must correspond to an existing database.

Validations:

The specified login/user (specified by username and password) is able to login.
The specified login/user (specified by username and password) has default schema "dbo".
The specified database must exist.
The specified database must have a data model version equal to the data model version immediately before the current one. E.g., when the current data model version number is 4.23, the database to update must be in data model version 4.22.

Examples:

Update using SQL Server login

Copy

Update MyServer sa sapassw MyApp

Update the existing database "MyApp", logging in with an SQL Server. The database will (still) be meant for danish culture.

Update using Windows Authentification

Copy

Update MyServer * * MyApp

Update the existing database "MyApp", logging in with Windows authentication. The database will (still) be meant for danish culture.

The update process generates a lot of output in the console window. To check for errors, it may be useful to "pipe" the output to a text file:

Update -saving the output to text file

Copy

Update MyServer * * MyApp  >update.txt

The generated text file may then be inspected using Notepad or any other text file editor.

Update scripts - data model version 4.21 and earlier

In release 4.21 and all earlier releases, there are no command line script. SQL-scripts are used to update a database from the previous version.

Remember that the SQL scripts in these versions are NOT distributed together with the framework, but as a separate "package" - see Database Management Using Scripts.

Each SQL-script must be run "manually" using SQL Server Management Studio.

In most versions, there is one script for the "core" data model (excluding add-ons) and one script per add-on (add-ons: DocRef, Blog, Accr).

Note
The one exception is the scripts to update data model 4.6->4.7 - in this special case there are several scripts for updating the "core" data model that must be run in proper sequence, and some manual intervention is needed in between (see comments in scripts).

To locate these scripts, look in folder Database/SQLScripts/<version>/Core/Update.sql (update script for the core data model). In version and before 4.10, the name of the update script typically include the version numbers - e.g. Update48to49.sql.

It is the responsibility of the deployer (doing the upgrade) to:

Launch SQL Server Management Studio.
Log in as suitable user (a SQL Server login or using Windows authentication with sufficient permission to create and drop tables etc., and with default schema "dbo").
Set the correct database as current.
Open and run each update script - in this sequence: Frist run the script(s) to update the "core" data model - then run the update scripts for each "add on" installed.