A Mockup of an R (or S-Plus) Script for Specifying a Research Database

# Institutional variable definitions used as defaults for all projects
# Will usually be in a centrally stored object that will be loaded here
iVars <- dbVars(sbp=list(label='Systolic Blood Pressure', units='mmHg',
                         type='int', range=c(0,400), srange=c(40,300),
                sex=list(levels=c('female','male'),
                id =list(label='Subject ID', type='int'))
# srange= soft range check, only issues a warning

# Factor out certain information
yn    <- c('yes','no')
sev   <- c('none','mild','moderate','severe')
sites <- c('MC','DU','VU','VA')  # or scan( ) or download.file( )

Vars <- dbVars(age=vattr(label='Age',units='year',range=c(0,100),
                      srange=c(20,80),type='int'),
             albumin=vattr(label='Serum Albumin',units='mg/dl',range=c(.1,20),
                          dec=c(2,1)), # implies type='float'
             symptoms=vattr(levels=c('headache','stomach ache','leg cramps'),
                           type='mchoice'), # multiple choice
             backpain=vattr(levels=sev, label='Back Pain', def='none'),
             hxmi=vattr(levels=yn, label='History of Myocardial Infarction'),
             id=vattr(range=c(1,200)),  # will get label, type from iVars
             rdate=vattr(label='Randomization Date', type='date'),
             site=vattr(label='Study Site', levels=sites),
             default=iVars)

# Add otherVars=c('sex',...) to add other variables to the result
# whose attributes come solely from iVars.  This will allow those
# attributes to be frozen even if iVars changes, until the user
# recreates Vars

# use e.g. c(3,Inf) for no upper limit in ranges
# ranges define default display widths; add dwidth= to be specific
# levels being present implies type='choice' if type omitted
# type='choice' is single choice; type='mchoice' is multiple choice
# For type='mchoice' may specify dtype='check' for checkboxes or 'scroll'
# for pull-down scrollable menu where multiple choices will be shaded if clicked

# Define data collection modules like in Oracle (sub-forms on separate HTML 
# sections; generates navigation bars at bottom of screen
idpanel <- DBpanel(Vars,vars=c('id','site','rdate'),
                   label='Patient Identification')

hxpanel <- dbPanel(Vars,vars=c('age','sex','hxmi'),label='Patient History')
                   default=iVars)  # sex comes from iVars
othpanel<- dbPanel(Vars,vars=Cs(albumin,symptoms,backpain,sbp),
                   label='Lab and Symptoms', default=iVars)
                   # sbp comes from iVars
# Define table
tab1 <- dbTable(name='tab1',
                uniqueID=c('id','site'),  # & these always required
                required=c('age','albumin'),
                panels=list(idpanel,hxpanel,othpanel))
# Instantiate table in MySQL
instantiateTable(tab1)

# Define a second table for the database if needed
tab2 <- dbTable( )
instantiateTable(tab2)

# Define an object defining the whole database for a project
myprojdb <- DB(name='myproj', label='Long Acting Pill Study',
               tables=list(tab1, tab2))

dbWebPerl(myprojdb, file='/var/www/html/cgi-bin/myprojdb.pl') 
# composes Perl web cgi as in Dubois
w <- dbImport(myprojdb)
# import data after some are entered, knowing all attribs
# w is a list with data frames tab1 and tab2

# For the distant future:
tab1new <- dbTable(name='tab1b', 
                   new components with changed/added/deleted variables)
modifyTable(tab1new, tab1,
            varRename=c(old1='new1',old2='new2'),
            levelRename=c(headache='head ache','stomach ache'='dyspepsia'))
# copy tab1 to tab1new, analyze differences
# between tab1new and tab1, issue needed MySQL
# modify commands.  Later check and rename tab1new to tab1 
# If only ranges, labels, display characteristics
# changed, no table modifications needed
# vrename specifies variable renames so that old variables are not
# dropped
# lrename specifies levels renames so that old levels are not dropped
# modifyTable creates an object of class modifyTable
# print.modifyTable gives details of all changes

Single choice variables become MySQL enumerate data types, and multiple choice become MySQL set data types.