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,
- 1) The database structure coming from different module authors to be in different scripts
- 2) Each module should maintain its structure version in the database itself so everyone can tell what version we are using
- 3) Take advantage of transactions so that any mistake will force all subsequent steps to fail and rollback
PostgreSQL has an anonymous stored procedure feature that will handle these problems nicely.
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.
- 1) Everything is inside of an anonymous PG/PLSQL block. Using default settings this will automatically be wrapped in a transaction as well. If any instruction fails the entire code block will fail.
- 2) We are creating a db_version table in the public schema.
- 3) The db_version table has only three columns -- the module name, the version (string), and whether the install was complete.
- 4) The code starts by creating the entry in the table and setting the install_complete flag to 'false'.
- 5) Our module's table creation, update, and manipulation statements would go in this area.
- 6) Finally we update the same module entry in the db_version table, setting the install_complete flag to true.
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 Thu Mar 23 00:18:06 2017, from http://www.limsexpert.com/cgi-bin/bixchange/bixchange.cgi?pom=limsexpert3;iid=readMore;go=1442166090