mysql_get_stim_by_attribute

 Returns a list of stimuli associated with a given list of attributes
 [data, vars] = mysql_get_stim_by_attribute(varargin);

 Given a list of attribute names, this returns the list of stimuli that are associated
 with those attributes.

 Input arguments:
   'name','attrib_name' - a string or cell array of strings containing the
                          attribute names to be matched.
   'conn_id' - database connection ID to use
   'params' - a structure containing host,database,user,passwd as fields
   'extract_vars' | 'extract_flds' - specify column names to return, default=all
       NOTE: all extract_vars must have the table specified (tbl.field,
       not just 'field') ... due to the specialization of this function to
       stim selection, only the tables specified in the 'tables' cell
       array are valid.

0001 function [data, vars] = mysql_get_stim_by_attribute(varargin)
0002 % Returns a list of stimuli associated with a given list of attributes
0003 % [data, vars] = mysql_get_stim_by_attribute(varargin);
0004 %
0005 % Given a list of attribute names, this returns the list of stimuli that are associated
0006 % with those attributes.
0007 %
0008 % Input arguments:
0009 %   'name','attrib_name' - a string or cell array of strings containing the
0010 %                          attribute names to be matched.
0011 %   'conn_id' - database connection ID to use
0012 %   'params' - a structure containing host,database,user,passwd as fields
0013 %   'extract_vars' | 'extract_flds' - specify column names to return, default=all
0014 %       NOTE: all extract_vars must have the table specified (tbl.field,
0015 %       not just 'field') ... due to the specialization of this function to
0016 %       stim selection, only the tables specified in the 'tables' cell
0017 %       array are valid.
0018 
0019 % 07/12/07 Petr Janata
0020 % 10/09/07 FB - general support to specify return variables, including support
0021 % for when you pass '*'. if no extract_vars are passed, the default set will be used
0022 % 06/15/10 PJ - mysql_make_conn sanitization
0023 
0024 data = {};
0025 vars = {};
0026 inputargs = varargin;
0027 
0028 % valid search tables
0029 tables = {'stimulus','attribute','stimulus_x_attribute'};
0030 
0031 % Check to see if params is one of the input arguments
0032 str_idxs = find(cellfun(@isstr,inputargs));
0033 params_idx = strmatch('params',inputargs(str_idxs));
0034 
0035 if ~isempty(params_idx)
0036   params = inputargs{str_idxs(params_idx)+1};
0037   rm_idxs = str_idxs(params_idx):str_idxs(params_idx)+1;
0038   inputargs(rm_idxs) = [];
0039 end
0040 
0041 % Parse the input arguments
0042 narg = length(inputargs);
0043 for iarg = 1:2:narg
0044   switch inputargs{iarg}
0045     case {'params'}
0046       params = inputargs{iarg+1};
0047       
0048     case 'mysql'
0049       params.mysql = inputargs{iarg+1};
0050       
0051     case 'conn_id'
0052       params.mysql.conn_id = inputargs{iarg+1};
0053       
0054     case 'host'
0055       params.mysql.host = inputargs{iarg+1};
0056     
0057     case 'database'
0058       params.mysql.database = inputargs{iarg+1};
0059       
0060     case {'name','attrib_name'}
0061       params.attrib_names = check_cell(inputargs{iarg+1});
0062     
0063     case {'extract_flds','extract_vars'}
0064       params.extract_vars = check_cell(inputargs{iarg+1});
0065       
0066   end % switch
0067 end % for iarg
0068  
0069 try 
0070     attrib_names = check_cell(params.attrib_names); 
0071 catch
0072   % This field mess should be cleaned up
0073     if isfield(params,'stimselect') && isfield(params.stimselect,'attrib_names')
0074         attrib_names = params.stimselect.attrib_names;
0075   elseif isfield(params,'selection') && isfield(params.selection,'attrib_names')
0076     attrib_names = params.selection.attrib_names;
0077     else
0078         attrib_names = {};
0079     end
0080 end
0081 if isempty(attrib_names)
0082   sprintf('no attribute names specified\n')
0083   return
0084 end
0085 
0086 % Check for connection to database
0087 try conn_id = params.mysql.conn_id;
0088 catch
0089     try conn_id = params.conn_id;
0090     catch
0091         tmp_conn_id = 1;
0092         conn_id = 0;
0093         params.conn_id = 0;
0094     end
0095 end
0096 
0097 if mysql_check_conn(conn_id)
0098   params.mysql.conn_id = mysql_make_conn(params.mysql);
0099 end
0100 
0101 % Form the SQL query
0102 if iscell(attrib_names)
0103   attrib_name_str = sprintf('"%s"', cell2str(attrib_names,'","'));
0104 else
0105   attrib_name_str = sprintf('"%s"', attrib_names);
0106 end
0107 
0108 % if extract_vars are specified, use mysql_describe_table to make sure the
0109 % requested variables exist as columns in the table. if no extract_vars are
0110 % specified, pass * .. ?
0111 
0112 if (~isfield(params,'extract_vars') || isempty(params.extract_vars))
0113 
0114     % default fields
0115     extract_vars = {'stimulus.stimulus_id','stimulus.name','attribute.name'};
0116 
0117 % not sure if this is all necessary, or if I have covered all of the bases,
0118 % to not have an error ...
0119 elseif (isequal(params.extract_vars,{'*'}) || isequal(params.extract_vars,'*'))
0120     
0121     extract_vars = {};
0122     % get all fields from all tables, append table stub, populate extract_vars
0123     for itbls = 1:length(tables)
0124         % create mysql table stub
0125         tbl_stub = sprintf('%s.',tables{itbls});
0126 
0127         tbl_desc = mysql_describe_table(tables{itbls},conn_id);
0128         tbl_desc.flds = strcat(tbl_stub,tbl_desc.flds);
0129         extract_vars = [extract_vars transpose(tbl_desc.flds)];
0130     end
0131     
0132 else
0133 
0134     % validate extract_vars members
0135     extract_vars = check_cell(params.extract_vars);
0136     tbl_members = {};
0137     valid = 1;
0138     for itbls = 1:length(tables)
0139         
0140         % create mysql table stub
0141         tbl_stub = sprintf('%s.',tables{itbls});
0142 
0143         % identify extract_vars indices for this table
0144         tbl_idxs = strmatch(tbl_stub,extract_vars);
0145         extract_vars{tbl_idxs};
0146 
0147         if (length(tbl_idxs) > 0)
0148             % track # of extract_vars members belonging to this table
0149             % get vars from table, validate
0150             tbl_desc = mysql_describe_table(tables{itbls},conn_id);
0151             for tidx = 1:length(tbl_idxs)
0152                 tbl_members = [tbl_members extract_vars{tbl_idxs(tidx)}];
0153                 candidate = strrep(extract_vars{tbl_idxs(tidx)},tbl_stub,'');
0154                 if(~all(ismember(candidate,tbl_desc.flds)))
0155                     sprintf('%s does not exist within %s',candidate,tables{itbls})
0156                     valid = 0;
0157                 end
0158             end
0159         end
0160     end
0161 
0162     % if we didn't account for all extract_vars members, then some of those
0163     % members did not map to one of the 'tables'
0164     if (length(tbl_members) ~= length(extract_vars))
0165         missing = setdiff(params.extract_vars,tbl_members);
0166         for midx = 1:length(missing)
0167             sprintf('%s is not within the allowed search tables\n',missing{midx})
0168         end
0169         valid = 0;
0170     end
0171 
0172     % if any invalid extract_vars were passed, do not continue
0173     if (~valid) return; end
0174         
0175 end
0176 
0177 extract_vars_str = cell2str(extract_vars,',');
0178 
0179 mysql_str = sprintf(['SELECT %s FROM stimulus ' ...
0180     'LEFT JOIN stimulus_x_attribute USING (stimulus_id) ' ...
0181     'LEFT JOIN attribute USING (attribute_id) ' ...
0182     'WHERE attribute.name IN (%s);'], extract_vars_str, attrib_name_str);
0183 
0184 mysql_str = sprintf(['SELECT %s FROM stimulus, attribute, stimulus_x_attribute ' ...    
0185     'WHERE stimulus.stimulus_id = stimulus_x_attribute.stimulus_id ' ...
0186     'AND attribute.attribute_id = stimulus_x_attribute.attribute_id ' ...
0187     'AND attribute.name IN (%s);'], extract_vars_str, attrib_name_str);
0188         
0189 [data{1:length(extract_vars)}] = mysql(conn_id, mysql_str);
0190 
0191 % If variables to extract contain periods, we need to remove those
0192 for ivar = 1:length(extract_vars)
0193   varname =  extract_vars{ivar};
0194 %   vars{ivar} = varname(findstr(varname,'.')+1:end);
0195 %   varsrc = varname(1:findstr(varname,'.')-1); % for varsrc support,
0196 %   varsrc must be added to the returns and caught upon return!!!!!!
0197   vars{ivar} = strrep(varname,'.','__');
0198 end
0199 
0200 if exist('tmp_conn_id','var')
0201   mysql(conn_id,'close');
0202   params.conn_id = [];
0203 end
0204 end % mysql_get_stim_by_attribute
0205 
0206 function var = check_cell(var)
0207   if ~iscell(var)
0208     var = {var};
0209   end
0210 end
0211 
0212