Chapter 10. Relational Database (RDB)

Table of Contents

Supported Databases
RDB User Accounts
Network Access
RDB URLs and Schemata

Several CSS tools interface to a relational database. The archive system described in Chapter 11, Archive System can store data in an RDB. The alarm system described in Chapter 14, Alarm System keeps its configuration and persistent state there.

To end users of CSS, this is mostly transparent, but if you install and administer CSS for your site, basic RDB administration skills will be required.

Supported Databases

CSS includes JDBC libraries for the following databases, and the alarm and archive system includes example database definition (DBD) files to create the required tables for these database dialects:

  • MySQL: This database is often the easiest to set up for initial tests and to support smaller operational setups. Free, open-source.

    The Community Version 5 should be usable.

  • Oracle: This database system might be the most powerful, with virtually unlimited table sizes. It is, however, not free.

    Oracle 10 and 11 should work.

  • PostgreSQL: This database might be a good compromise. Bigger table sizes. Free, open-source.

    Postgres has to be at least version 8.4 to support sequences.

Your personal preference might of course differ, and it is also important to consider what database system is already supported at your site.

RDB User Accounts

You will need to configure at least three types of RDB users:

  • Administrator account: A user that can create tables, update indices, reset sequences etc.
  • Write-access users: The ArchiveEngine needs a user account that can write samples to the archive related tables. The AlarmServer needs a user account that can persist the alarm state. For each system you might want to create a user that can write to the necessary tables of that system, but only that system, and without general administrative rights.
  • Read-only access: The CSS data browser needs a user that can read archived data. You might want to create various reports for the RDB data. All of these could use one shared read-only account, a name and password that you can freely distribute to end users who want to create their own reports on the data with MS Access, JSP, PHP, ...

Network Access

CSS or Java JDBC tools in general tend to connect to the database from the network. Even if CSS is running on the same computer that also hosts the database, it will still connect to localhost via network system calls.

You should therefore check that the database is network accessible. Details depend on the database. With MySQL, try for example

mysql -h localhost -u root -p

With Postgres, use the psql shell, and edit the pg_hba.conf file to allow md5 or password access to the Postgres server from the network, including localhost.

RDB URLs and Schemata

Whatever database you use, in the end you need to provide CSS tools or users who want to access the data from other tools with the following configuration information:

  • URL: Depending on the RDB system this will be a URL of the format
    jdbc:mysql://[host]:[port]/[database]
    
    for MySQL (default port is 3306),
    jdbc:postgresql://[host]:[port]/[database]
    
    for PostgreSQL (default port is 5432) or
    jdbc:oracle:thin:@//[host]:[port]/[service]
    
    for Oracle. CSS tools will use the start of that URL to detect the RDB dialect: MySQL, Oracle or PostgreSQL.
  • User name: A user name known to the RDB. To end users, you will typically provide the name of a read-only account.
  • Password: Associated password
  • Schema: A prefix for RDB table names that might be needed by Oracle to access the tables.

To elaborate on the Schema, let's use an example. In the ALARM database, there is a PV table. When using MySQL, one can connect with a URL

jdbc:mysql://my.rdb.host/alarm

as any user and directly read from the PV table. Similar with PostgreSQL.

With Oracle, the URL usually addresses an Oracle service, not a database schema:

jdbc:oracle:thin:@//my.rdb.host:1521/prod

With Oracle, only the schema owner, that is a user with the same name as the database, can directly access the tables in a schema. All other users need to prefix the table name with the schema name, i.e. use ALARM.PV to read from the PV table.

To support all types of database dialects, the CSS tools allow configuration of a URL as well as a schema. For Oracle, you need to set that schema to the respective table prefix. For MySQL and PostgreSQL, you typically define the schema as empty, because the URL already includes the schema name in its [database] section.