Creating a new Database

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

This pages describes how to create a new create a database - using command line scripts. As described in Operations Architecture, each configuration should be connected to its own database.

Note
If the version of the framework is 4.25 or later, it is recommended to use the Administration Module to create new databases. This is much easier than using command line scripts.

This topic contains the following sections.

Alternative procedures for creating databases
Characteristics of the databases created
Prerequisites
Creating a new database with TOPICA core data model
Creating a new empty database
Building the TOPICA data model

Alternative procedures for creating databases

In some environments (e.g. development and test environments) the deployer may have full control over the database server. But in other environments ("managed" production environments), only the administrators of the database servers (DBA staff) have permission to create and drop databases.

Therefore, how databases should be created depend on the environment:

Environments, where the user has permission to create databases:
- To create a TOPICA database, open a command prompt, navigate to the folder containing the command scripts, and execute the CreateTopicaCore.cmd command file described below. This command runs these other scripts as subroutines:
  1. CreateDatabase.cmd: creates a new empty database in more or less the same way as the DBA staff does.
  2. BuildTopicaCore.cmd: creates tables etc. implementing TOPICA's core data model.
For "managed" production environments, the DBA staff creates a new empty database (using scripts etc. managed by the DBA),
- The deployer should "only" build the tables, views, etc. used in the TOPICA data model. The process in this case is:
  1. The deployer requests creation of a new, empty database (specifying a database name and a schema name) from the DBA staff.
  2. The DBA staff creates a database with the requested name, schema, and 2 logins: an "Admin" login and an "App" login. The DBA staff decides password for these logins, and informs the deployer (the requester) of these passwords.
  3. The deployer executes the BuildTopicaCore.cmd command file described below (that creates tables etc. implementing TOPICA's core data model).

Characteristics of the databases created

When using the procedures described here, the following objects are built in the database server:

A database with a user specified name.
A schema with a user specified name.
A login named (databasename)_AdminLogin - this login should be used for administrative tasks on the new database.
A login named (databasename)_AppLogin - this login should be used in the connection string for the TOPICA application.

The user specifies both a database name and a schema name. Having the possibility to specify database and schema names independently, makes it possible (and advisable) to use same schema names in databases, that hold the same data model.

Example: An application "MyApp" should be set up in 2 environments (test and production) on the same server. In this case the following names could be chosen:

Database name	Schema name
MyAppTest	MyApp
MyAppProd	MyApp

Prerequisites

A login that gives "create database" permission on the database server:

An SQL Server login. This requires that the SQL Server login option was selected when SQL Server was installed (per default this options is NOT selected)!
Windows authentication. This requires that the computer running the database scripts and the server where SQL Server is installed, are in the same domain (or in trusted domains).

Creating a new database with TOPICA core data model

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

Copy

CreateTopicaCore
Missing parameter(s) - expected:
    Server Username Password NewDbName Schema AdmPassword AppPassword Culture [Version]
        Server:
            Servername (use "." or "localhost" for current machine).
        Username:
            Login used for creating database, logins, etc.
            For trusted connection (= Windows authentication): use "*".
        Password:
            Password for login used for creating database, logins, etc.
            For trusted connection (= Windows authentication): "*".
        NewDbName:
            Name of the new database to be created.
        Schema:
            Schema used for application specific (dynamically created) tables etc.
        AdmPassword:
            Password for new login {NewDbName}_AdminLogin.
        AppPassword:
            Password for new login {NewDbName}_AppLogin.
        Culture:
            The culture to build the database for.
        Version:
            Optional version number for data model.
            If omitted, default value is "4.25".

That is, the CreateTopicaCore command takes 9 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 - for example "sa".
3	Password	When using Windows integrated authentication, use "*". When using SQL Server login, enter the password for that login.
4	NewDbName	The name of the new database. It is recommended to use only letters A-Z, digits and _ (underscore). It is possible, but not recommended, to use special characters and national letters.
5	Schema	Enter the name of schema to create. This schema will be the "owner" of tables etc. for the dynamic data model (e.g. tables created automatically according to configured forms). It is recommended to use only letters A-Z, digits and _ (underscore). It is possible, but not recommended, to use special characters and national letters. 2 or more databases for the same application (and hence same data model), should have the same schema name. For example, if application X should be running in 2 environments (=databases) Xprod and Xtest, these databases should both use X as schema name.
6	AdmPassword	Password for the created login {NewDbName}_AdminLogin. This login/password should be used for administrative tasks. This login has "dbo" as default schema.
7	AppPassword	Password for the created login {NewDbName}_AppLogin. This login/password should be used for the TOPICA application. This login has the specified schema (see 5. parameter above) as the default schema.
8	Culture	Specifies the culture to build the database for. Currently these values are valid: da-DK, en-GB, en-US, sv-SE. This value controls various lookup texts, algorithm for national id validation, culture-specific patient properties, etc. NOTE: The newly create database will contain an initial organization structure dependent on culture. This may be delete and replaced with imported data.
9	Version	The data model version number. This parameter is optional - defaults to the current version (in above example: "4.25").

Examples:

Copy

CreateTopicaCore MyServer sa sapassw MyAppDK MyAppDK adm app da-DK 4.24

Create danish database "MyAppDK" with schema "MyAppDK" on server "MyServer" (using SQL Server login "sa"). The login "MyAppProdDK_AdminLogin" will have password "adm". The login "MyAppProdDK_AppLogin" will have password "app". The TOPICA data model version will be 4.24.

Copy

CreateTopicaCore MyServer * * MyAppUK MyAppUK adm app en-GB 4.23

Create an english database "MyAppUK" with schema "MyAppUK" on server "MyServer" (using Windows authentication). The login "MyAppUK_AdminLogin" will have password "adm". The login "MyAppUK_AppLogin" will have password "app". The TOPICA data model version will be 4.23.

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 not already exist.
The specified culture must exist (i.e. must be equal one of the supported cultures). If this is not the case, a list of the valid cultures is displayed.

Creating a new empty database

Prerequisite: a login/password, that gives "create database" permission on the database server (i.e. the deployer cannot execute this in "managed" production environments).

The purpose of this command is to create an empty database, and the 2 associated logins - as if it had been created in the "managed" environment by DBA staff. This empty database may then be used to test the below described command BuildTopicaCore.

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

Copy

CreateDatabase
Missing parameter(s) - expected:
    Server Username Password NewDbName Schema AdmPassword AppPassword
        Server:
            Servername (use "." or "localhost" for current machine).
        Username:
            Login used for creating database, logins, etc.
            For trusted connection (= Windows authentication): use "*".
        Password:
            Password for login used for creating database, logins, etc.
            For trusted connection (= Windows authentication): use "*".
        NewDbName:
            Name of the new database to be created.
        Schema:
            Schema used for application specific (dynamically created) tables etc.
            To use database name for schema, use "*".
        AdmPassword:
            Password for new login {NewDbName}_AdminLogin.
        AppPassword:
            Password for new login {NewDbName}_AppLogin.

That is, the CreateDatabase command takes 7 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	NewDbName	The name of the new database. Use only letters A-Z, digits and _ (underscore) - no special characters or national letters.
5	Schema	Enter the name of schema to create. This schema will be the "owner" of tables etc. for the dynamic data model (e.g. tables created automatically according to configured forms). Use only letters A-Z, digits and _ (underscore) - no special characters or national letters. 2 or more databases for the same application (and hence same data model), should have the same schema name. For example, if application X should be running in 2 environments=databases Xprod and Xtest, these databases should both use X as schema name.
6	AdmPassword	Password for the created login {NewDbName}_AdminLogin. This login/password should be used for administrative tasks. This login has "dbo" as default schema.
7	AppPassword	Password for the created login {NewDbName}_AppLogin. This login/password should be used for the TOPICA application. This login has the specified schema (see 5. parameter above) as the default schema.

Examples:

Copy

CreateDatabase MyServer sa sapassw MyApp1Prod MyApp1 adm app

Create database "MyApp1Prod" with schema "MyApp1" on server "MyServer" (using SQL Server login "sa"). The login "MyAppProd1_AdminLogin" will have password "adm". The login "MyAppProd1_AppLogin" will have password "app". No TOPICA data model is created.

Copy

CreateDatabase MyServer * * MyApp1Prod MyApp2 adm app

Create database "MyApp1Prod" with schema "MyApp1" on server "MyServer" (using Windows authentication). The login "MyAppProd1_AdminLogin" will have password "adm". The login "MyAppProd1_AppLogin" will have password "app". No TOPICA data model is created.

Building the TOPICA data model

Prerequisite: a new, empty database must have been created previously - and the "Admin" password must be known. This may be accomplished either by requesting the DBA staff to create the new database (in "managed" production environments), or by executing the CreateDatabase command described above (in other environments).

This procedure builds database objects (tables, views, etc.) of at TOPIAC database.

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

Copy

BuildTopicaCore
Missing parameter(s) - expected:
    Server Username Password Database [Version]
        Server:
            Servername (use "." or "localhost" for current machine).
        Username:
            AdminLogin for the database in question.
        Password:
            Password for the AdminLogin for the database in question.
        Database:
            Database to create tables etc. in.
        Culture:
            Culture name.
        Version:
            Optional version number for data model.
            If omitted, default value is 4.25

That is, the BuildTopicaCore command takes 5 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	This should be the "AdminLogin" previously created.
3	Password	This should be the password for the "AdminLogin" previously created.
4	DatabaseName	The name of the previously created, empty database.
5	Culture	Specifies the culture to build the database for. Currently these values are valid: da-DK, en-GB, en-US, sv-SE. This value controls various lookup texts, algorithm for national id validation, culture-specific patient properties, etc. NOTE: The newly created database will contain an initial organization structure dependent on culture. This may be deleted and replaced with imported data.
6	Version	Data model version. This parameter is optional - defaults to the latest released version (currently 4.25).

Example:

Copy

BuildTopicaCore MyServer MyApp1Prod_AdminLogin adm MyApp1Prod da-DK 4.23

Login to the previously created database "MyApp1Prod" using SQL Server login "MyAppProd1_AdminLogin" and password "adm" and build database for danish culture in data model version 4.23.