Prep: LAB-A1-002 Continued

(Ref Id: 1442166090)

Typical Scenario

When you get a system you are often supplied with a database script that contains everything -- statements that create the tables, views, stored procedures, sequences, indexes, and initial data set the vendor wants you to have. It was probably created by doing something like this:

pg_dump mydatabase > thedatabaseloadscript.sql

As long as this is in a readable format you are in business. Just load the database like so (this tutorial assumes you are using PostgreSQL):

psql -d mydatabase -f thedatabaseloadscript.sql

The most common problems with this scenario involve changes to the database structure by someone that is not the vendor. Users might want to make changes to the database structure and/or data for their internal environment. When the vendor releases new versions of the software the database modifications get wiped out.

DIY Modular Needs

In a truly modular system the vendor is responsible for distributing a core system platform that facilitates the modules. Those modules can, but need not, come from that same vendor. This exacerbates the database maintenance issue.

Most of our previous examples have concentrated on equipment records (sometimes referred to as instrument records) because they are normally a point of contention between different laboratories. Some labs maintain intricate records pertaining to equipment maintenance and even sometimes personnel equipment training records. It is safe to say that there is no one way of looking at equipment records in a modern LIMS, so why bother? The core system can include its own equipment module but should allow for it to be supplanted by a third party. Equipment is an ideal place to start thinking about modularity.

When a module author publishes a component that needs to add to or modify the existing database it should be done in a predictable way that will facilitate automated building of the system and avoid conflicts with other authors. If Module A wants to add the field 'foo' to the equipment table and Module B comes along to add the same field you have a problem. You cannot expect the two authors to collaborate and they might have very different reasons for adding that field.

Enter PostgreSQL's Anonymous Stored Procedures

There are all sorts of techniques module authors could use to avoid conflicts like naming conventions, etc. We are interested in making naming conflicts and other problems stop the database creation process dead in its tracks.

Ideally we want the following,

PostgreSQL has an anonymous stored procedure feature that will handle these problems nicely.


DO $$
create table public.db_version (
modname varchar(15) primary key,
modvers varchar(5) not null,
install_complete boolean default false );

insert into public.db_version VALUES ('SERIES-A1','1.0',false);

-- put actual table/view/stored procedures here

update public.db_version set install_complete = true
where modname = 'SERIES-A1'
and modvers = '1.0';

The above is deceptively simple so let's take a good look.

Going Forward

This post was supposed to include the next lab but that has been delayed to describe this strategy for database creation and manipulation. We will expand upon the example above in the next lab and include instructions for running small, self-contained stored procedures like these to create and manipulate the target database for the core system and modules alike.

Go Back

Citation: Prep: LAB-A1-002 Continued. (2015). Retrieved Sat Jul 31 15:54:46 2021, from;iid=readMore;go=1442166090