Creating a new Database

We strongly recommend that you create a new database for the tables used by the Management Console. There are two requirements to the database.

Unicode support is needed because non-ASCII characters, like Danish Æ, German ß or Cyrillic Ѐ may be given as input to robots. This input is stored in the database, and without Unicode support these characters may be stored incorrectly.

Case-sensitive comparison is needed because it is possible to upload a robot named a.robot and another named A.robot. Without case-sensitive comparison uploading the latter would override the first.

Database servers handles Unicode and case-sensitive comparison very differently. The following list contains recommendations for the supported database systems.

Database

Recommendations

IBM DB2

Create the database using CODESET UTF-8

MySQL 5.x

Create the database with utf8_bin collation:

CREATE DATABASE KAPOW_MC COLLATE utf8_bin

Oracle

We use NVARCHAR2, NCLOB types for Unicode. For case-sensitive comparison ensure that NLS_COMP is set to BINARY

Microsoft SQL Server 2005/2008

We use NVARCAR, NTEXT types for Unicode. For case-sensitive comparison create the database with a Case-Sensitive collation like SQL_Latin1_General_CP1_CS_AS:

CREATE DATABASE KAPOW_MC COLLATE SQL_Latin1_General_CP1_CS_AS

Sybase Adaptive Server Enterprise

We use NVARCAR, NTEXT types for Unicode. For case-sensitive comparison ensure that the sort order is binary (see sp_helpsort).

The scripts below use "NVARCHAR(255) NOT NULL UNIQUE", due to index limitations this will not work on an ASE with 2K page size, unless you modify the scripts to use NVARCHAR(200) NOT NULL UNIQUE (or install on a 4/8/16K page server)

Recommendations for Unicode support and case-sensitive comparison


The tables used by the Management Console can be grouped into 3 categories: Platform tables, logging tables, and data view tables. The platform tables hold information exclusive to the Management Console such as the uploaded robots and their scheduling information, while the logging and data view tables are shared with RoboServer.

User privileges

When the Management Console starts, it will automatically try to create the required platform tables and logging tables (if not already created by RoboServer). This means that the user account used to access the database must have the CREATE TABLE and ALTER TABLE privilege. Oracle users also needs the CREATE SEQUENCE privilege. If this is not possible you can have your Database administrator create the tables using the scripts below.

Additionally the user must be allowed to SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES for the system to work properly.

SQL scripts for Management Console tables

Database

Script to Create Tables

Script to Drop Tables

IBM DB2

create

drop

MySQL 5.x

create

drop

Oracle

create

drop

Microsoft SQL Server 2005/2008

create

drop

Sybase Adaptive server

create

drop

SQL scripts for Management Console tables (right-click and choose "Save As")


The Management Console uses a 3rd party scheduling component called Quartz. Quartz also requires a number of tables which must reside among the other platform tables. These tables are also created automatically when the Management Console starts, or may be created manually using the scripts below.

SQL scripts for Quartz tables

Database

Script to Create Tables

Script to Drop Tables

IBM DB2

create

drop

MySQL 5.x

create

drop

Oracle

create

drop

Microsoft SQL Server 2005/2008

create

drop

Sybase Adaptive server

create

drop

SQL scripts for Quartz tables (right-click and choose "Save As")