23 Nov 2011 The MiniLIMS 4 column table and it's PHP manifestation, the TypeInstance
Previous posts in this series:
If you’ve done any data modeling or database design in the last few years, particularly in bioinformatics, you’ve probably run across a name-value pair construct. Name-value pairs are often used when you have a variable number of information bits that have to be tracked, but that can’t be delineated up front. When implemented as a database table, there is a column called “NAME” that specifies the name of the element and a column called “VALUE” that stores the value of that pair for a particular record. A classic example is the GenBank feature tag list.
Not without reason, database purists dislike the lack of data integrity enforcement. For example, since all values must be the lowest common denominator, a string, there’s no type checking at the database level. However, with proper design, and loosely coupled type guidance, this can be made up for by moving any type-checking to the code.
The MiniLIMS 4 column table
MiniLIMS takes this construct and applies it to all of the data in the system. As a result, changes to the data model, the equivalent of adding columns or refactoring table structure, can be done with nothing more than SQL update statements.
Every element of data in the MiniLIMS system is stored in a single, 4 column table that is no more complex than the name-value pair table you would use to describe GenBank feature tags. The only difference in MiniLIMS is that, instead of relating those names and values (Property and Value in MiniLIMS) to a specific element like a feature record, any combination of type and instance can be used to group them together.
As a result a table like this:
will end up with records like this:
As a developer, you can interact directly with the MiniLIMS data table. I myself have done a number of data model changes with SELECT…INSERT statements. However, MiniLIMS has a PHP API that can be called upon to simplify your code. The main classes that you would use are Table and TypeInstance.
The MiniLIMS Table and TypeInstance classes
The Table class represents a connection to a particular table in the MiniLIMS database. Though there are some other tables used by MiniLIMS for things like user auth data, the important one is the semantic_data table. To establish a connection to it,
$minilims_dir = "/somewhere"; require_once("$minilims_dir/GlobalConfig.php"); $table = new Table("semantic_data");
That’s all. Including the
GlobalConfig.php file will get you all the class definitions that you need. It’s location will be different on your system.
Once you’ve established a connection to the MiniLIMS data table, the next step is to either create or retrieve TypeInstances. The TypeInstance class represents the collection of Properties and Values that are combined for a particular instance of a type. For a typical relational table or Excel sheet, this is a row of data. In terms of the semantic_data table, it’s all the rows that have a particular combination of “name” and “thing” values.
$minilims_dir = '/somewhere'; require_once("$minilims_dir/GlobalConfig.php"); $table = new Table('semantic_data'); $sample = new TypeInstance('Sample','SN100045'); $sample->fetch($table);
The fetch method of the TypeInstance is used to retrieve data from the table using the thing (Sample) and name (SN100045) specified in the constructor. If you’re creating a new instance of data, you don’t need the fetch– you’ll use the store method.
To populate your new instance with actual data, there is one key operation, setPropertyValue. This method takes the property and value as parameters and is called every time you want to add more information.
$sample->setPropertyValue('Location','E401); $sample->setPropertyValue('Project','Rat genome'); ... $sample->store($table);
Not much more to it than that.
The key to making our Torrent Server API work with MiniLIMS is going to be coming up with a simple way to translate between the MiniLIMS TypeInstances that we want to create or retrieve and the Torrent Server’s JSON data representation.
Luckily, a JSON object is the really just a text object manifestation of the MiniLIMS Semantic Data table!
Next post in this series: