mysql_extract_data

 This is a very generic routine for extracting data from a table.

 [data,vars] = mysql_extract_data(varargin);

 This is a very generic routine for extracting data from a table.
 It accepts an variable length argument list of tag/value pairs.

 'table' - specifies name of table to extract data from
 {'extract_flds','extract_vars'} - a cell array of strings containing a list of fields to
                  extract from the table. If this is not specified, all fields
                  are extracted.
 {'order_by','sort_by'} - an optional field that specifies which fields should be used to
              order the data

 'conn_id' - the connection ID to the MySQL database to use. REQUIRED

 'encrypted_fields' - a cell array of field names that were encrypted in the
                      table using aes_encryption. They will be decrypted using
                      the supplied encryption key 'enc_key'
 'enc_key' - a string used to decrypt the data, using aes_decrypt

 All other tag/value pairs specify fields to be used for searching and the
 values to search for. With the exception of the following variables, the tag
 must match the field name in the table exactly.

 {'session_id','session_ids','sessid','sessids'} - map to 'session_id'
 {'subject_id','subject_ids','subid','subids'} - map to 'subject_id'
 {'form_id','form_ids','formid','formids'} - map to 'form_id'

0001 function [data,vars] = mysql_extract_data(varargin)
0002 % This is a very generic routine for extracting data from a table.
0003 %
0004 % [data,vars] = mysql_extract_data(varargin);
0005 %
0006 % This is a very generic routine for extracting data from a table.
0007 % It accepts an variable length argument list of tag/value pairs.
0008 %
0009 % 'table' - specifies name of table to extract data from
0010 % {'extract_flds','extract_vars'} - a cell array of strings containing a list of fields to
0011 %                  extract from the table. If this is not specified, all fields
0012 %                  are extracted.
0013 % {'order_by','sort_by'} - an optional field that specifies which fields should be used to
0014 %              order the data
0015 %
0016 % 'conn_id' - the connection ID to the MySQL database to use. REQUIRED
0017 %
0018 % 'encrypted_fields' - a cell array of field names that were encrypted in the
0019 %                      table using aes_encryption. They will be decrypted using
0020 %                      the supplied encryption key 'enc_key'
0021 % 'enc_key' - a string used to decrypt the data, using aes_decrypt
0022 %
0023 % All other tag/value pairs specify fields to be used for searching and the
0024 % values to search for. With the exception of the following variables, the tag
0025 % must match the field name in the table exactly.
0026 %
0027 % {'session_id','session_ids','sessid','sessids'} - map to 'session_id'
0028 % {'subject_id','subject_ids','subid','subids'} - map to 'subject_id'
0029 % {'form_id','form_ids','formid','formids'} - map to 'form_id'
0030 
0031 % 01/04/07 Petr Janata
0032 % 01/10/07 S.T. Added clause for handling empty search criteria
0033 %               (i.e. no extract_flds or values are given)
0034 % 03/12/08 PJ field names now enclosed in quotes. MySQL 5 compatibility
0035 % 10/05/09 Stefan Tomic - added support for reading in encrypted fields,
0036 %                         identified by 'encrypted_fields' cell array. The key
0037 %                         is passed in as 'enc_key'
0038 % 06/15/10 PJ Sanitized mysql_make_conn
0039 % 29May2014 PJ Made failing due to missing search criteria more graceful
0040 
0041 % Initialize some variables
0042 fld.crit_flds = {};
0043 fld.extract_flds = {};
0044 fld.order_by = {};
0045 crit_vals = {};
0046 table = '';
0047 
0048 data = {};
0049 vars = {};
0050 
0051 check_fld_vars = fieldnames(fld);
0052 
0053 % Parse the input arguments
0054 narg = length(varargin);
0055 for iarg = 1:2:narg
0056 
0057   switch varargin{iarg}
0058     case {'experiment_id','experiment_ids','expid','expids'}
0059       fld.crit_flds{end+1} = 'experiment_id';
0060       crit_vals{end+1} = check_cell(varargin{iarg+1});
0061    
0062     case {'session_id','session_ids','sessid','sessids'}
0063       fld.crit_flds{end+1} = 'session_id';
0064       crit_vals{end+1} = check_cell(varargin{iarg+1});
0065    
0066     case {'subject_id','subject_ids','subid','subids'}
0067       fld.crit_flds{end+1} = 'subject_id';
0068       crit_vals{end+1} = check_cell(varargin{iarg+1});
0069    
0070     case {'form_id','form_ids','formid','formids'}
0071       fld.crit_flds{end+1} = 'form_id';
0072       crit_vals{end+1} = check_cell(varargin{iarg+1});
0073       
0074     case {'extract_flds','extract_vars'}
0075       fld.extract_flds = check_cell(varargin{iarg+1});
0076       
0077     case {'order_by','sort_by'}
0078       fld.order_by = check_cell(varargin{iarg+1});
0079       
0080     case {'table','resp_table'}
0081       table = varargin{iarg+1};
0082       
0083     case 'conn_id'
0084       conn_id = varargin{iarg+1};
0085       
0086     case {'encrypted_fields','encrypted_flds'}
0087       encrypted_fields = varargin{iarg+1};
0088     
0089     case 'enc_key'
0090       enc_key = varargin{iarg+1};
0091     
0092     otherwise % assume a criterion field/value pair
0093       fld.crit_flds{end+1} =  varargin{iarg};
0094       crit_vals{end+1} = check_cell(varargin{iarg+1});
0095       
0096   end % switch
0097 end % iarg
0098 
0099 % Make sure a table was specified
0100 if isempty(table)
0101   fprintf('mysql_extract_data: No table specified\n');
0102   return
0103 end
0104 
0105 % Check for valid connection to database
0106 if ~exist('conn_id','var') || isempty(conn_id) || mysql(conn_id,'status')
0107   error('%s: Do not have a valid connection ID', mfilename);
0108 end
0109 
0110 % Make sure that all the fields we want to extract or sort by actually exist in the table
0111 % we want to extract them from
0112 tbl = mysql_describe_table(table,conn_id);
0113 
0114 nfld_vars = length(check_fld_vars);
0115 for ivar = 1:nfld_vars
0116   curr_fld = check_fld_vars{ivar};
0117   if strcmp(curr_fld,'extract_flds') && isempty(fld.extract_flds)
0118     fld.extract_flds = tbl.flds;
0119   end
0120 
0121   exist_mask = ismember(fld.(curr_fld),tbl.flds);
0122   bad_flds = find(~exist_mask);
0123   if ~isempty(bad_flds)
0124     for ibad = 1:length(bad_flds)
0125       fprintf('Field %s does not exist in table %s\n', fld.(curr_fld){bad_flds(ibad)}, table);
0126     end
0127     fld.(curr_fld)(bad_flds) = [];  % remove bad fields
0128   end % if ~isempty(bad_flds)
0129 end % for ivar
0130 
0131 % Initialize the output variables and data array
0132 vars = fld.extract_flds;
0133 data = cell(1,length(vars));
0134 cols = set_var_col_const(vars);
0135 
0136 % Prepare elements of the query
0137 extract_vars_str = cell2str(fld.extract_flds,',');
0138 if(exist('encrypted_fields','var'))
0139  
0140   nflds = length(fld.extract_flds);
0141   for ifld = 1:nflds
0142     fldname = fld.extract_flds{ifld};
0143     if(ismember(fldname,encrypted_fields))
0144       sel_query{ifld} = sprintf('aes_decrypt(`%s`,''%s'')',fldname,enc_key);
0145     else
0146       sel_query{ifld} = sprintf('`%s`',fldname);
0147     end
0148   end
0149   extract_vars_str = cell2str(sel_query,',');
0150 
0151 else
0152   extract_vars_str = sprintf('`%s`,',fld.extract_flds{:});
0153   extract_vars_str(end) = [];
0154 end
0155 
0156 
0157 % Make the criterion string
0158 ncrit = length(fld.crit_flds);
0159 if(ncrit == 0)
0160   crit_str = [];
0161 else
0162   crit_str = 'WHERE';
0163   for icrit = 1:ncrit
0164     if icrit > 1
0165       crit_str = [crit_str ' AND'];
0166     end
0167   
0168     if ~isempty(crit_vals{icrit}) && ~isempty(crit_vals{icrit}{1})
0169       if isstr(crit_vals{icrit}{1})
0170         crit_val_str = sprintf('"%s",', crit_vals{icrit}{:});
0171       else
0172         crit_val_str = sprintf('%d,', crit_vals{icrit}{:});
0173       end
0174       crit_val_str(end) = [];
0175     else
0176       fprintf('%s: No criterion specified for %s. Search will fail!\n', mfilename, fld.crit_flds{icrit});
0177       return
0178     end
0179     
0180     curr_crit_str = sprintf(' %s IN (%s)', fld.crit_flds{icrit},crit_val_str);
0181     
0182     crit_str = [crit_str curr_crit_str];
0183   end
0184 end %else
0185   
0186   
0187 sort_str = '';
0188 if ~isempty(fld.order_by)
0189   sort_str = sprintf('ORDER BY %s', cell2str(fld.order_by,','));
0190 end
0191 
0192 % Construct the query
0193 mysql_str = sprintf(['SELECT %s FROM %s ' ...
0194       '%s %s'], extract_vars_str, table, crit_str, sort_str);
0195 
0196 % Extract the data
0197 [data{1:length(fld.extract_flds)}] = mysql(conn_id, mysql_str);
0198 
0199 % Deal with any necessary conversions from numeric fields that were
0200 % encrypted
0201 if exist('encrypted_fields','var') && any(strcmp('response_enum', encrypted_fields)) && any(strcmp('response_enum',vars))
0202   tmp = data{cols.response_enum};
0203   datamask = ~cellfun('isempty',tmp);
0204   data{cols.response_enum} = nan(size(datamask));
0205     data{cols.response_enum}(datamask) = cell2mat(cellfun(@str2num,tmp,'UniformOutput',0));
0206 end
0207 
0208 if exist('tmp_conn_id','var')
0209   mysql(conn_id,'close');
0210 end
0211 end % mysql_extract_data
0212 
0213 function var = check_cell(var)
0214   if ~iscell(var)
0215     var = {var};
0216   end
0217 end