Configuring the database connection manually

As part of the Content Migrator installation process we need to create a repository for holding all application data. Content Migrator has support for 3 database platforms, MySQL, Oracle and Microsoft SQL Server. Here we provide a short description on the steps required to manually create the database schema, when it is not created as part of the installation process.

• MySQL
• Oracle
• Microsoft SQL Server

We will also discuss editing the database settings, which may be required to modify the connection details for content migrator.

Once the installation process has completed, the installation folder, typically 'c:\Program Files\Vamosa\Content Migrator', will contain a sub folder named 'db_sql_files'. This folder will contain the required SQL files that will create the schema for the chosen database platform.

Note, before any of the SQL files can be executed an empty database must be created with the correct character set and at least one user that has permissions for creating the schema and INSERT, UPDATE, SELECT and DELETE privileges. It is not within the scope of this article to outline the steps required to create the database for each database platform.

MySQL

MySQL5 has been tested and is supported for Content Migrator 3.0, other MySQL versions may work but are not currently supported.

MySQL will require 3 SQL files to be executed, these files are listed below:

• vamosa-mysql5.sql - will create the Content Migrator schema, which should result in 23 tables being created for the selected database.
• extra-indices.sql - will create additional indices for selected database tables, used to improve performance.
• admin-user-creation.sql - creates the initial administrator user of Content Migrator, required to log onto the application and create further users.

Each of the above files should be taken in turn and executed against the selected database.

Oracle

Oracle9i has been tested and is supported for Content Migrator 3.0, other Oracle versions may work but are not currently supported.

Oracle will require 2 SQL files to be executed, these files are listed below:

• vamosa-oracle.sql - will create the Content Migrator schema, which should result in 23 tables being created for the selected database. Extra indices will be created using this SQL file, you may wish to split the table create and index creation into 2 steps at this point.
• create-oracle-admin-user.sql - Creates the initial administrator user of Content Migrator, required to log onto the application and create further users.

Each of the above files should be taken in turn and executed against the selected database.

Microsoft SQL Server

Microsoft SQL Server 2005 has been tested and is supported for content migrator 3.0, other versions may work but are not currently supported.

Microsoft SQL Server will require 2 SQL files to be executed, these files are listed below:

• vamosa-mssql2005.sql - will create the content migrator schema, which should result in 23 tables being created for the selected database. Extra indices will be created using this SQL file, you may wish to split the table create and index creation into 2 steps at this point.
• admin-mssql2005-user-creation.sql - creates the initial administrator user of content migrator, required to log onto the application and create further users.

Each of the above files should be taken in turn and executed against the selected database.

Edit Settings

During the installation process you may wrongly configure the database server details, such as entering an incorrect host name or database name, these values can be modified using the following files.

database.yml - located in the root of the install directory

    db_settings: &db_settings
        adapter: jdbcmysql
        database: vamosa_database
        username: database user
        password: database password
        host: database host name or ip address

The databse.yml file contains name:value pairs of the database connection details, typically the properties for database, username, password and host will require modification, do not modify the 'adapter' property.

vamosa-ds.xml - located in 'install directory'\jboss\server\default\deploy'

The vamosa-ds file contains connection details for the database, the following snippet shows the nodes that may require modification to update connection settings.

<datasources>
    <local-tx-datasource id="vamosa">
    <jndi-name>jdbc/Vamosa</jndi-name>
    <connection-url>database host and database name contained in this connection string</connection-url>
    <driver-class>Database Driver, should not require modification</driver-class>
    <user-name>database user</user-name>
    <password>database password</password>