mysql_get_expinfo

 Gets information for an experiment from a mysql database

 [expinfo] = mysql_get_expinfo(expmt, host, db, conn_id);

 expmt -- experiment to look for.  If this is left empty, a list of
 experiments is displayed to the screen
 host -- machine running the mysql server
 db -- database on the server

0001 function [expinfo] = mysql_get_expinfo(expmt, host, database, conn_id)
0002 % Gets information for an experiment from a mysql database
0003 %
0004 % [expinfo] = mysql_get_expinfo(expmt, host, db, conn_id);
0005 %
0006 % expmt -- experiment to look for.  If this is left empty, a list of
0007 % experiments is displayed to the screen
0008 % host -- machine running the mysql server
0009 % db -- database on the server
0010 %
0011 
0012 % 01/25/05 Petr Janata
0013 % 06/26/05 PJ - modified conn_id handling
0014 % 03/13/06 PJ - Exclusion of tmp_ entries when retrieving subject_id
0015 % 06/20/06 PJ - Added retrieval of form names and form IDs
0016 % 07/17/06 PJ - Accelerated script using GROUP BY clauses
0017 % 10/28/06 PJ - Made connecting to arbitrary desired database a bit more
0018 %               robust. Now returns information for list of experiments in
0019 %               expmt.
0020 % 10/05/09 Stefan Tomic - reads in subject name and DOB as encrypted data
0021 % 06/15/10 PJ - sanitized mysql_make_conn
0022   
0023 expinfo = [];
0024 
0025 % Do some parameter checking
0026 msg = nargchk(0,4,nargin);
0027 if ~isempty(msg)
0028   disp(msg)
0029   return
0030 end
0031 
0032 % Check for valid connection to database
0033 if ~exist('conn_id','var') || isempty(conn_id) || mysql(conn_id,'status')
0034   if exist('host','var') && ~isempty(host) && exist('database','var') && ~isempty(database)
0035     conn_id = mysql_make_conn(mysql_login(struct('host',host,'database',database,'login_type','researcher')));
0036     tmp_conn_id = 1;
0037   else
0038     error('%s: Do not have a valid connection ID', mfilename);
0039   end
0040 end
0041 
0042 % Get a list of experiment titles, IDs, and response tables
0043 mysql_str = sprintf('SELECT experiment_id,experiment_title,response_table FROM experiment');
0044 [exp_ids, exp_names, resp_table_list] = mysql(conn_id,mysql_str);
0045 
0046 % Display experiment list if no experiment name passed in. Otherwise return
0047 % info on desired experiment
0048 if ~exist('expmt') || isempty(expmt)
0049   fprintf('%s\n',cell2str(exp_names,'\n'));
0050 else
0051   if ~iscell(expmt)
0052     expmt = {expmt};
0053   end
0054   
0055   nexp = length(expmt);
0056   
0057   for iexp = 1:nexp
0058     expinfo(iexp).name = expmt{iexp};
0059     
0060     % Determine which row of the response corresponds to the desired experiment
0061     row_idx = find(ismember(exp_names,expmt{iexp}));
0062     
0063     expinfo(iexp).expid = exp_ids(row_idx);
0064     
0065     % Get the associated response table name
0066     resp_table = resp_table_list{row_idx};
0067     expinfo(iexp).resp_table = resp_table;
0068 
0069     % Get a list of the form IDs in this experiment
0070     sql_str = sprintf(['SELECT form_id, form_order FROM experiment_x_form WHERE' ...
0071       ' experiment_id = %d GROUP BY form_id;'], expinfo(iexp).expid);
0072     [form_ids, form_order] = mysql(conn_id,sql_str);
0073     
0074     % Sort form_ids by form_orders
0075     [sorted_form_order, form_order_idxs] = sort(form_order);
0076     expinfo(iexp).forms.ids = form_ids(form_order_idxs);
0077     
0078     form_id_str = sprintf('%d,', form_ids(form_order_idxs));
0079     form_id_str(end) = [];
0080     
0081     sql_str = sprintf('SELECT form_name, form_id FROM form WHERE form_id IN (%s);', form_id_str);
0082     [form_names, form_ids2] = mysql(conn_id,sql_str);
0083 
0084     % Need to sort form_ids such that they line up, given that they were
0085     % retrieved from separate tables. This could probably be accomplished with
0086     % a single nice SQL query, but I don't have the manual in front of me right
0087     % now, so I'll do it a complicated Matlab way.  PJ
0088     
0089     [in,ia,ib] = intersect(form_ids(form_order_idxs),form_ids2);
0090     [sort_ia, sort_ia_idx] = sort(ia); % get original list lined back up
0091     form_order_idxs2 = ib(sort_ia_idx); % use to dereference 2nd list index
0092                                         % generated by intersect
0093     
0094     expinfo(iexp).forms.names = form_names(form_order_idxs2);
0095     
0096     % Get desired variables from the response table, but ignore entries beginning
0097     % with tmp_ which denote temporary subject IDs left over from aborted
0098     % consenting processes.
0099     sql_str = sprintf(['SELECT subject_id, session_id, ' ...
0100       'MIN(date_time) AS start_time, MAX(date_time) AS stop_time ' ...
0101       'FROM %s WHERE subject_id NOT LIKE "tmp_%%" GROUP BY session_id;'], resp_table);
0102     
0103     [ids,sess_ids,start_times,stop_times] = mysql(conn_id,sql_str);
0104 %     start_times = datenum(start_times,'yyyy-mm-dd HH:MM:SS');
0105 %     stop_times = datenum(stop_times,'yyyy-mm-dd HH:MM:SS');
0106     
0107             % Get a list of subjects in the experiment
0108     [expinfo(iexp).subs.ids] = unique(ids);
0109     nsubs = length(expinfo(iexp).subs.ids);
0110     
0111     % Get subject info
0112     sub_str = sprintf('''%s'',', expinfo(iexp).subs.ids{:});
0113     sub_str(end) = [];
0114     enc_key = ensemble_get_encryption_key;
0115     mysql_str = sprintf(['SELECT aes_decrypt(`name_first`,''%s''), aes_decrypt(`name_last`,''%s''),' ...
0116             'aes_decrypt(`dob`,''%s'') FROM subject WHERE subject_id IN (%s)'], ...
0117             enc_key,enc_key,enc_key,sub_str);
0118     [first_names, last_names, birthdays] = mysql(conn_id, mysql_str);
0119     %since encrypted birthdays are stored as encrypted strings, we need to
0120     %convert these to datenums
0121     birthdays = datenum(birthdays);
0122     
0123     % Determine the number of sessions for each subject in the experiment
0124     for isub = 1:nsubs
0125       sub_idx_mask = ismember(ids, expinfo(iexp).subs.ids{isub});
0126       [curr_sess_ids, curr_idxs] = unique(sess_ids(sub_idx_mask));
0127       expinfo(iexp).subs.sess{isub} = curr_sess_ids;
0128       expinfo(iexp).subs.nsess(isub) = length(curr_sess_ids);
0129       expinfo(iexp).subs.names{isub} = sprintf('%s %s',first_names{isub},last_names{isub});
0130       datediff = datevec(start_times(curr_idxs)-birthdays(isub));
0131 
0132       % take the average age if dealing with multiple sessions
0133       expinfo(iexp).subs.ages(isub) = mean(datediff(:,1));  
0134     end % for isub
0135     
0136     % Deal with things from the perspective of sessions
0137     expinfo(iexp).sess.ids = sess_ids;
0138     expinfo(iexp).sess.subids = ids;    
0139     expinfo(iexp).sess.start_time = start_times;
0140     expinfo(iexp).sess.stop_time = stop_times;
0141     
0142     % Deal with the rare situation (from early days of Ensemble) where we don't
0143     % have a session stop time.
0144     missing_idxs = find(isnan(expinfo(iexp).sess.stop_time));
0145     if any(missing_idxs)
0146       sess_str = sprintf('%d,',expinfo(iexp).sess.ids(missing_idxs));
0147       sess_str(end) = [];
0148       mysql_str = sprintf(['SELECT session_id, MAX(date_time) AS endtime FROM %s ' ...
0149         'WHERE session_id IN (%s) GROUP BY session_id'], resp_table, sess_str);
0150       tic
0151       [sess_ids, end_times] = mysql(conn_id, mysql_str);
0152       toc
0153       expinfo(iexp).sess.stop_time(missing_idxs) = end_times;
0154     end % if any(missing_idxs)
0155   end % for iexp
0156 end % if ~exist('expmt') || isempty(expmt)
0157 
0158 % Close the mysql connection if this was a temporary opening of the database
0159 if exist('tmp_conn_id','var')
0160   mysql(conn_id,'close');
0161 end