Triggers and sequences

Introduction

Information is presented in this set of pages in order to help the user set up Instant JChem for use with three common relational database management systems (RDBMS): Oracle, MySQL and Apache Derby. As such, help and context for setting up sequences and triggers, associated with tables in each RDBMS is presented. It is assumed the reader of this might not be a DBA and rather is a Medicinal Chemist with a reasonable understanding of IT and databases - as such code examples are given. In the case of Oracle and MySQL the user will be expected to have a RDBMS user available as a container, with adequate disk space to create database tables and the neccessary permissions to create various objects. Instant JChem administration is discussed further here Admin docs .

Overview

In most RDBMS system a row is normally required to be uniquely identified so that it and associated data can be easily retrieved. As such, an additional column is required for each table which is normally referred to as a primary key (for a JChem table this is normally the CD_ID). One way to populate this column is by means of a sequence which is a value which is generated sequentially. Normally sequences are integer based but can easily be modified by concatenation to be character based i.e. "mol-008". In addition to key generation further derived data items might be required for each row to facilitate virtual screening. For example, if each row represents a single molecule then a set of searchable descriptors might be appended as additional columns. In both cases, the use of sequences and triggers in the respective RDBMS can be used to create or derive new data items based upon existing data items or generated using the many chemical terms and calculator plugins available. Simple examples for the generation of unique identifiers and additional calculated data items in conjunction with Instant JChem deployment are presented via links below for each respective RDBMS. Behind each link is a quick guide to administer a table in each RDBMS within an Instant JChem project and then examples of simple additional tasks such as unique key generation and derived columns. For each of the RDBMS system we should need to connect to our schema (data repository) and manage the contents using the RDBMS vendor tools or more conveniently the Instant JChem explorer which is described in each case and here: Using the database explorer .

Oracle

In Oracle RDBMS a sequence object is available to generate integers and the method to generate character sequences using integer sequences and triggers is described as well as some simple calculation examples. Oracle .
In MySQL and Derby no such sequence object type exists and so one needs to emulate the sequence object using other available objects. Calculations are easily implemented using triggers.

MySQL

An approach to using tables and triggers for key generation and derivation columns in MySQL is described here. MySQL .

Apache Derby

An approach to using tables and triggers for key generation and derivation columns in Apache Derby is described here. Derby .