dbResetDB.m

function dbResetDB()
% Initialize DB tables.
%
% WARNING will delete existing data

error('This is out of date. Should use a DB dump to recreate.');

disp('This function will delete all existing data.');
disp('Don''t say yes unless you really know what you are doing.');
reply = input('Proceed? (y/n): ','s');
if isempty(reply) || reply(1) ~= 'y'
  disp('Good choice.');
  return
end

tables = {'model','run','experiment', ...
         'task','microscope','user','log'};

conn = dbOpen();

% Drop tables if they exist.
for i=1:length(tables)
  sql = ['DROP TABLE IF EXISTS ' tables{i} ';'];
  dbCheck(exec(conn.conn, sql));
end

% Create new tables.
sql = ['CREATE TABLE model (' ...
       'id INTEGER AUTO_INCREMENT PRIMARY KEY,' ...
       'name VARCHAR(128) UNIQUE,' ...
       'description TEXT,'...
% paramnames doesn't mean anything, not used and doesn't correspond to
% initparams or the mcmc output
       'paramnames VARCHAR(256),'... % comma-separated list, output key
       'numparams INTEGER,'... % number of real parameters
       'definitparams VARCHAR(1024),'... % comma-separated liEst
       'initparamskey VARCHAR(1024),'...
       'priortypes VARCHAR(256)),'... % comma-separated list
       'defaultpriors VARCHAR(1024));']; % prior string
disp(sql);
dbCheck(exec(conn.conn, sql));

sql = ['CREATE TABLE experiment (' ...
       'id INTEGER AUTO_INCREMENT PRIMARY KEY,'...
       'user_id INTEGER,'...
       'microscope_id INTEGER,'...
       'file VARCHAR(256) UNIQUE,'... % unique to avoid adding twice automatically
       'num_cells INTEGER,'...
       'cellline_id INTEGER,'...
       'date TEXT,'...
       'name TEXT,'...
       'description TEXT);'];
disp(sql);
dbCheck(exec(conn.conn, sql));

sql = ['CREATE TABLE microscope (' ...
       'id INTEGER AUTO_INCREMENT PRIMARY KEY,'...
       'name VARCHAR(256) UNIQUE);'];
disp(sql);
dbCheck(exec(conn.conn, sql));

sql = ['CREATE TABLE run ('...
       'id INTEGER AUTO_INCREMENT PRIMARY KEY,'...
       'name VARCHAR(256) UNIQUE,'... % needs to be unique due to use in run output path
       'user_id INTEGER,'...
       'experiment_id INTEGER',...
       'created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,'...
       'start TIMESTAMP,'...
       'model_id INTEGER,'...
       'numsteps INTEGER DEFAULT 250000,'...
       'subsample INTEGER DEFAULT 20,'...
       'burnin INTEGER DEFAULT 150000,'...
       'numchains INTEGER DEFAULT 5,'...
       'priors VARCHAR(256),'...
       'subset VARCHAR(256),'...
       'extra_options VARCHAR(1024),'...
       'conv_Rc DOUBLE DEFAULT 1.2,'...
       'maxruns INTEGER DEFAULT 4,'...
       'initparams VARCHAR(1024),'...
       'randominit VARCHAR(64) DEFAULT ''overdispersed'');'];
disp(sql);
dbCheck(exec(conn.conn, sql));

sql = ['CREATE TABLE task (',...
       'id INTEGER AUTO_INCREMENT PRIMARY KEY,'...
       'run_id INTEGER,'...
       'trajdata_idx INTEGER,'...
       'cell_idx INTEGER,'...
       'sisterlist_idx INTEGER,'...
       'created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,'...
       'start TIMESTAMP,'....
       'finish TIMESTAMP,'....
       'status CHAR(1) DEFAULT ''P'','...
       'type CHAR(1) DEFAULT ''1'','...
       'runcount INTEGER DEFAULT 0,'...
       'convergence DOUBLE);'];
disp(sql);
dbCheck(exec(conn.conn, sql));

sql = ['CREATE TABLE user (',...
       'id INTEGER AUTO_INCREMENT PRIMARY KEY,'...
       'name VARCHAR(256),'...
       'initials CHAR(3) UNIQUE);'];
disp(sql);
dbCheck(exec(conn.conn, sql));

sql = ['CREATE TABLE log (',...
       'id INTEGER AUTO_INCREMENT PRIMARY KEY,'...
       'task_id INTEGER UNIQUE,'...
       'log LONGTEXT);'];
disp(sql);
dbCheck(exec(conn.conn, sql));

sql = ['CREATE TABLE cellline (',...
       'id INTEGER AUTO_INCREMENT PRIMARY KEY,'...
       'name VARCHAR(256) UNIQUE,'...
       'description TEXT,'...
       'code VARCHAR(16));'];
disp(sql);
dbCheck(exec(conn.conn, sql));