Documentation of bmrbdata database

(Examples)


If you haven't already read the documentation on the BMRB dictionary or the boilerplate fields, you will need to do so before these examples make any sense.

Let's say that there's a table in the dictionary that we already know is called 'sampleDescr'. We can find all the information about that table with the following query:

	SELECT    *
	FROM      dictionary
	WHERE     dbtablemanual = 'sampleDescr'
	ORDER BY  sequence;
    

Doing that query, we get the following result:


originalsf        save_<sample_label>
originalcategory  sample
originaltag       _Saveframe_category
dictionaryseq     2720
dbtableauto       sample
dbcolumnauto      SaveframeCategory
dbtype            VARCHAR(127)
dbnullable        NOT NULL
enumerationid
foreigntable
foreigncolumn
indexflag
internalflag
dbtablemanual     sampleDescr
dbcolumnmanual    SaveframeCategory
loopflag          N
sequence          10

originalsf        save_<sample_label>
originalcategory  sample
originaltag       _Sample_type
dictionaryseq     2740
dbtableauto       sample
dbcolumnauto      SampleType
dbtype            VARCHAR(127)
dbnullable        NOT NULL
enumerationid
foreigntable
foreigncolumn
indexflag
internalflag
dbtablemanual     sampleDescr
dbcolumnmanual    SampleType
loopflag          N
sequence          20

originalsf        save_<sample_label>
originalcategory  sample
originaltag       _Details
dictionaryseq     2730
dbtableauto       sample
dbcolumnauto      Details   
dbtype            TEXT
dbnullable
enumerationid
foreigntable
foreigncolumn
indexflag
internalflag
dbtablemanual     sampleDescr
dbcolumnmanual    Details   
loopflag          N
sequence          30

    

With the above selection, we can see that the table is not a loop table (loopflag = 'N'), and therefore it is a freetags table. That means that the table will consist of at least the freetags boilerplate fields, plus the fields described in the above selection.

So we see that the first field described in the dictionary for this table is called 'SaveframeCategory'. It is a VARCHAR(127) type, and it is not nullable.

The second field described in the dictionary for this table is called 'SampleType'. It is a VARCHAR(127) type, and it is not allowed to be null either.

The third field described in the dictionary for this table is called 'Details'. It is a TEXT type (a subset of the 'BLOB' type), and it IS allowed to be nulled.

So putting the above together with the boilerplate for free-tag tables, we now know that this table is defined like so:

	originalsf        save_
	originalcategory  sample
	originaltag       _Saveframe_category
	dictionaryseq     2720
	dbtableauto       sample
	dbcolumnauto      SaveframeCategory
	dbtype            VARCHAR(127)
	dbnullable        NOT NULL
	enumerationid
	foreigntable
	foreigncolumn
	indexflag
	internalflag
	dbtablemanual     sampleDescr
	dbcolumnmanual    SaveframeCategory
	loopflag          N
	sequence          10

	originalsf        save_
	originalcategory  sample
	originaltag       _Sample_type
	dictionaryseq     2740
	dbtableauto       sample
	dbcolumnauto      SampleType
	dbtype            VARCHAR(127)
	dbnullable        NOT NULL
	enumerationid
	foreigntable
	foreigncolumn
	indexflag
	internalflag
	dbtablemanual     sampleDescr
	dbcolumnmanual    SampleType
	loopflag          N
	sequence          20

	originalsf        save_
	originalcategory  sample
	originaltag       _Details
	dictionaryseq     2730
	dbtableauto       sample
	dbcolumnauto      Details   
	dbtype            TEXT
	dbnullable
	enumerationid
	foreigntable
	foreigncolumn
	indexflag
	internalflag
	dbtablemanual     sampleDescr
	dbcolumnmanual    Details   
	loopflag          N
	sequence          30
    

So when we combine the above with the boilerplate fields for freetag tables, we see that the definition of this table is as follows:

        CREATE TABLE sampleDescr        (
	    SaveFrameID          INTEGER       PRIMARY KEY,
	    originalBlock        VARCHAR(80),
	    originalFrame        VARCHAR(80),
	    SaveframeCategory    VARCHAR(127)  NOT NULL,
	    SampleType           VARCHAR(127)  NOT NULL,
	    Details              TEXT
	);
	CREATE INDEX T0074 on sampleDescr ( originalBlock, originalFrame ) ;
    

So, theoretically it is possible to create the database tables by writing a program to read the dictionary table and do it automaticly. In fact, this is what was done at BMRB to make the tables.