DCS Detector Requirements Database

A MySQL implementation of a DCS detector requirements database is presented here, as first proposed in Jaroslaw Szewinski's talk (attached).

Status

Current status: System up and running at: http://nuclear.gla.ac.uk/DCS/

Components

The MySQL database is located on a Linux machine, running a PHP-enabled Apache server. The UI is custom-written. Everything is modular.

Structure

The implemented structure is one single database DCS_REQ, with eight tables:

mysql> CREATE DATABASE DCS_REQ;
mysql> GRANT ALL ON DCS_REQ.* TO 'dsc_req_db_admin'@'localhost' IDENTIFIED BY '******';
mysql> FLUSH PRIVILEGES;

Tables

There are at present 8 tables in the database:

mysql> CREATE TABLE Subgroups (
         id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
         name VARCHAR(16),
         description TEXT(255),
         contact INT(3),
         alt_contact INT(3)
        );

mysql> CREATE TABLE Types (
         id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
         name VARCHAR(20),
         description TEXT(50)
        );

mysql> CREATE TABLE Parameters (
         id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
         name VARCHAR(32),
         description VARCHAR(60)
        );

mysql> CREATE TABLE Units (
         id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
         quantity VARCHAR(32),
         name VARCHAR(24),
         description TEXT(64)
        );

In this table, items like unit, type are referred by an integer id and cross-queried from the above tables. This ensures standardization.

mysql> CREATE TABLE ProcessVariables (
         id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
         subgroup INT(3),
         parameter INT(11),
         unit INT(3),
         type INT(3),
         channels INT(5),
         nominal_value FLOAT,
         range_min FLOAT,
         range_max FLOAT,
         warn_min FLOAT,
         warn_max FLOAT,
         alarm_min FLOAT,
         alarm_max FLOAT,
         update_rate INT(6),
         is_monitored TINYINT,
         is_controlled TINYINT,
         gen_interlock TINYINT,
         description TEXT(255),
         contact INT(3),
         hardware VARCHAR(60),
         lastupdatedby VARCHAR(40),
         timestamp TIMESTAMP(8)
       );

The auxiliary tables:

mysql> CREATE TABLE Institutes (
         id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
         name VARCHAR(60),
         address VARCHAR(200),
         contact_name VARCHAR(60),
         contact_phone VARCHAR(20),
         contact_email VARCHAR(50)
        );

People table:

mysql> CREATE TABLE Contacts (
         id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
         name VARCHAR(60),
         email VARCHAR(60),
         phone VARCHAR(20),
         alt_phone VARCHAR(20),
         institute INT(3),
         homepage VARCHAR(120),
         registered TINYTEXT
        );

mysql> CREATE TABLE Hardware (
         id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
         vendor VARCHAR(60),
         name VARCHAR(60),
         description VARCHAR(60),
         connectivity VARCHAR(60),
         platform CHAR(20),
         documentation VARCHAR(100),
         supported_by VARCHAR(60)
        );

Access restrictions

Administrative access (SSH to the server and to the DB itself) is restricted to the developers. Access to insert/update data into tables is restricted to registered users, with registration and authentication via the web interface.

Interface

We have a fully custom-written PHP interface. The URL is: http://nuclear.gla.ac.uk/DCS/

Location

The DB and web interface will be physically located on a server in Glasgow for the initial design and testing period. Both DB and UI are very easy to transport to another server, in case a suitable server could be found at GSI.

Present location: http://nuclear.gla.ac.uk/DCS/

-- DanProtopopescu - 04 Mar 2008