mysql_extract_metadata

 Retrieves experiment, form, question or data_format info.

 tableStruct = mysql_extract_metadata(varargin)

 examples of usage:
   tableStruct1 = mysql_extract_metadata('table','experiment','experiment_id',[2 3 4]);
 
   tableStruct2 = mysql_extract_metadata('table','experiment','experiment_title',{'Groove' 'autobio_v1'});

   tableStruct3 = mysql_extract_metadata('table','form','form_id',[100 101 102]);

   tableStruct4 = mysql_extract_metadata('table','experiment');  %extracts metadata on all experiments

 extracts data from experiment, form, question, or data_format
 tables in the Ensemble database and reorganizes the records into
 arrays of structs, where each field in the struct corresponds to
 a field in the Ensemble table. Tables are traversed in a top-down 
 fashion, where experiments are considered top level and link to
 forms, questions, and data formats.

 arguments are tag, value pairs, where possible tags are:
 
 table: either 'experiment','form','question',or 'data_format'

 conn_id: the mysql connection ID to use. Required

 keep_db_open: normally only used for recursively calling this
               function so that the database isn't closed during
               successive function calls.

 <fieldname>: a field name of the table being extracted. The
              specified fields do not have to be key fields.

0001 function tableStruct = mysql_extract_metadata(varargin)
0002 % Retrieves experiment, form, question or data_format info.
0003 %
0004 % tableStruct = mysql_extract_metadata(varargin)
0005 %
0006 % examples of usage:
0007 %   tableStruct1 = mysql_extract_metadata('table','experiment','experiment_id',[2 3 4]);
0008 %
0009 %   tableStruct2 = mysql_extract_metadata('table','experiment','experiment_title',{'Groove' 'autobio_v1'});
0010 %
0011 %   tableStruct3 = mysql_extract_metadata('table','form','form_id',[100 101 102]);
0012 %
0013 %   tableStruct4 = mysql_extract_metadata('table','experiment');  %extracts metadata on all experiments
0014 %
0015 % extracts data from experiment, form, question, or data_format
0016 % tables in the Ensemble database and reorganizes the records into
0017 % arrays of structs, where each field in the struct corresponds to
0018 % a field in the Ensemble table. Tables are traversed in a top-down
0019 % fashion, where experiments are considered top level and link to
0020 % forms, questions, and data formats.
0021 %
0022 % arguments are tag, value pairs, where possible tags are:
0023 %
0024 % table: either 'experiment','form','question',or 'data_format'
0025 %
0026 % conn_id: the mysql connection ID to use. Required
0027 %
0028 % keep_db_open: normally only used for recursively calling this
0029 %               function so that the database isn't closed during
0030 %               successive function calls.
0031 %
0032 % <fieldname>: a field name of the table being extracted. The
0033 %              specified fields do not have to be key fields.
0034 %
0035 
0036 % 19 Jan 2007, First Version, S.T.
0037 % 03/15/07 PJ - added conn_id to a couple of mysql calls that were missing
0038 %               it
0039 % 05/01/07 PJ - enable passing in of host and database arguments for
0040 %               establishing connections to databases other than the
0041 %               default database
0042 % 06/15/10 PJ - added script enforcing of conn_id
0043 
0044 
0045 extractDataArgs = {};
0046 for(itag = 1:2:length(varargin))
0047   
0048   switch(varargin{itag})
0049     case 'table'
0050       table = varargin{itag+1};
0051            
0052     case 'conn_id'
0053       conn_id = varargin{itag+1};
0054     
0055     case 'keep_db_open'
0056       keep_db_open = varargin{itag+1};
0057     
0058     case 'addJunctionTableFields'
0059       addJunctionTableFields = varargin{itag+1}{:};
0060     
0061    case 'addJunctionTableVals'
0062     addJunctionTableVals = varargin{itag+1}{:};
0063                        
0064    otherwise
0065     extractDataArgs{end+1} = varargin{itag};
0066     extractDataArgs{end+1} = varargin{itag+1};
0067     
0068   end
0069 end
0070 
0071 % Check for valid connection to database
0072 if ~exist('conn_id','var') || isempty(conn_id) || mysql(conn_id,'status')
0073   error('%s: Do not have a valid connection ID', mfilename);
0074 end
0075 
0076 try 
0077   addJunctionTableFields;
0078 catch
0079   addJunctionTableFields = {};
0080 end
0081 
0082 try 
0083   addJunctionTableVals;
0084 catch
0085   addJunctionTableVals = {};
0086 end
0087 
0088 
0089 switch table
0090  
0091  case 'experiment'
0092   parentKeyField      = 'experiment_id';
0093   childTable = 'form';
0094   childKeyField   = 'form_id';
0095   foreignKeyField    = childKeyField;
0096   childJunctionTable = 'experiment_x_form';
0097   orderChildrenBy        = 'form_order';
0098   junctionTableInfo = {'form_order','form_handler','goto','repeat','condition','condition_matlab','stimulus_matlab','break_loop_button'};
0099   flattenChildren = 0;
0100   
0101  case 'form'
0102   parentKeyField      = 'form_id';
0103   childTable = 'question';
0104   childKeyField   = 'question_id';
0105   foreignKeyField    = childKeyField;
0106   childJunctionTable = 'form_x_question';
0107   orderChildrenBy = 'form_question_num';
0108   junctionTableInfo = {'question_iteration','form_question_num'};
0109   flattenChildren = 0;
0110   
0111  case 'question'
0112   parentKeyField      = 'question_id';
0113   childTable = 'data_format';
0114   childKeyField   = 'data_format_id';
0115   foreignKeyField    = 'answer_format_id';
0116   childJunctionTable = 'question_x_data_format';
0117   orderChildrenBy = 'subquestion';
0118   junctionTableInfo = {'subquestion','heading','range','default','html_field_type','required'};
0119   flattenChildren = 1;
0120   
0121  case 'data_format'
0122   parentKeyField = 'data_format_id';
0123   childTable = '';
0124   junctionTableInfo = {};
0125   flattenChildren = 0;
0126   
0127   
0128  case 'stimulus'
0129   parentKeyField = 'stimulus_id';
0130   childTable = 'attribute';
0131   childKeyField = 'attribute_id';
0132   foreignKeyField = childKeyField;
0133   childJunctionTable = 'stimulus_x_attribute';
0134   orderChildrenBy = 'attribute_id';
0135   junctionTableInfo = {'attribute_value_double', ...
0136             'attribute_value_text'};
0137   flattenChildren = 0;
0138   
0139  case 'attribute'
0140   parentKeyField = 'attribute_id';
0141   childTable = '';
0142   junctionTableInfo = {};
0143   flattenChildren = 0;
0144   
0145  otherwise
0146   error(sprintf('Unrecognized table: %s',table));
0147   
0148 end
0149 
0150 [tableInfo{1:6}] = mysql(conn_id,sprintf('describe %s',table));
0151 inputFields = extractDataArgs(1:2:end);
0152 [validFields,fieldLocs] = ismember(inputFields,tableInfo{1});
0153 if(any(validFields == 0))
0154   invalidFieldIdxs = find(validFields == 0);
0155   for idxInvalidField = invalidFieldIdxs
0156     disp(sprintf('Invalid Field: %s',inputFields{idxInvalidField}))
0157     error('Cannot continue since one or more incorrect fields have been entered.');
0158   end
0159 end
0160 
0161 
0162 [values,tableFieldNames] = mysql_extract_data('table',table,'conn_id',conn_id,extractDataArgs{:});
0163 
0164 %since mysql_extract_data does not retrieve multiple
0165 %records for repeated keys and automatically sorts them, we need to reorder the
0166 %results here
0167 
0168 [tf,fldIdx]  = ismember(parentKeyField,tableFieldNames);
0169 [tf,parentKeyLoc] = ismember(parentKeyField,extractDataArgs(1:2:end));
0170 
0171 %only reorder if key fields were given. If key fields were not
0172 %given, no reordering is necessary since this would have been done
0173 % at the top level (a selection was made that did not include
0174 % any keys) in which case the function should just return the
0175 % records in the order they were given
0176 if(parentKeyLoc > 0)
0177   %we found the odd ordered index, now convert to absolute index
0178   parentKeyLoc = parentKeyLoc * 2 - 1;
0179   parentKeyVals = extractDataArgs{parentKeyLoc+1};
0180   [tf,keyLocs] = ismember(parentKeyVals,values{fldIdx});
0181   
0182   if(any(keyLocs == 0))
0183     
0184     emptyKeys = find(keyLocs == 0);
0185     keyLocs(emptyKeys) = [];
0186   end
0187 
0188   %reorder the values
0189   for iField = 1:length(tableFieldNames)
0190     values{iField} = values{iField}(keyLocs);
0191   end
0192 
0193 end
0194 
0195 %add junction table tableFieldNames and values if passed through recursion
0196 tableFieldNames = {tableFieldNames{:} addJunctionTableFields{:}};
0197 values     = {values{:}     addJunctionTableVals{:}};
0198 
0199 if(strcmp(table,'question'))
0200   tableFieldNames = {'compqid' tableFieldNames{:}};
0201   values = {repmat(NaN,size(values{1})) values{:}};
0202 end
0203 
0204 %construct arguments for mkstruct
0205 mkstrArgs = cell(1,(length(tableFieldNames)*2));
0206 mkstrArgs(1:2:end) = tableFieldNames;
0207 mkstrArgs(2:2:end) = values;
0208 
0209 % make a structure with the tableFieldNames and values
0210 % and convert the structure to an array of structs
0211 tableStruct = convert_structarray(mkstruct(tableFieldNames,mkstrArgs));
0212 
0213 %add a field to contain the struct for the child table
0214 if(~isempty(childTable))
0215   tableStruct(1).(childTable) = [];
0216 end
0217 
0218 %initialize newTableStruct (to be used in following loop)
0219 newTableStruct = struct([]);
0220 
0221 %go through each record in tableStruct and see if
0222 %there are children structures that need to be added to this structure
0223 for irecord = 1:length(tableStruct)
0224   
0225   if(~isempty(parentKeyField))
0226     parentKeyVal = getfield(tableStruct(irecord),parentKeyField);
0227   end
0228 
0229   childStruct = struct([]);
0230   addTableStructs = struct([]);
0231   
0232   %get child information only if we have a value for the parent key
0233   if(exist('parentKeyVal','var') & ~isempty(parentKeyVal))  
0234     
0235  
0236  
0237    %childTable contains the name of the table for the child
0238    %(e.g. 'question' table is the child of the 'form' table
0239    %obtain child info through the junction table (e.g. form_x_question)
0240    if(~isempty(childTable))
0241    
0242       %construct fieldname strings suitable for sql that do not
0243       %allow for ambiguity of table
0244       sqlJunctionTableInfo = strcat([childJunctionTable '.`'],junctionTableInfo(:),'`');
0245       sqlJunctionTableInfoText = sprintf(',%s', sqlJunctionTableInfo{:});
0246       sqlForeignKeyFieldText = sprintf('%s.`%s`',childJunctionTable,foreignKeyField);
0247       sqlChildKeyFieldText = sprintf('%s.`%s`',childTable,childKeyField);
0248       
0249       %get the foreign key values to the child table as well as any
0250       %info fields from the junction table (e.g. question_x_data_format.subquestion)
0251       sql_junctionTable = sprintf('select %s%s,`%s` from %s join %s on (%s = %s) where %s = %d order by %s',...
0252                   sqlForeignKeyFieldText,...
0253                   sqlJunctionTableInfoText,...
0254                   parentKeyField,...
0255                   childJunctionTable,...
0256                   childTable,...
0257                   sqlChildKeyFieldText,...
0258                   sqlForeignKeyFieldText,... 
0259                   parentKeyField,...
0260                   parentKeyVal,...
0261                   orderChildrenBy);
0262      
0263       %separate the retrieved keys to the child table and the info fields
0264       junctionTable = cell(1,length(junctionTableInfo(:))+2);
0265       [junctionTable{:}] = mysql(conn_id,sql_junctionTable);
0266       childrenKeyVals = junctionTable{1};
0267       
0268       if(length(junctionTableInfo) > 0)
0269     junctionTableInfoVals = junctionTable(2:end-1);
0270       else
0271     junctionTableInfoVals = [];
0272       end
0273       
0274       junctionParentKeyVals = junctionTable{end};
0275       
0276       %if any child keys were obtained, get metadata on the child tables
0277       if(length(childrenKeyVals) > 0)
0278     childStruct = mysql_extract_metadata('table',childTable,...
0279                          childKeyField,childrenKeyVals,...
0280                          'conn_id',conn_id,...
0281                          'keep_db_open',1,...
0282                          'addJunctionTableFields',{junctionTableInfo},...
0283                          'addJunctionTableVals',{junctionTableInfoVals});
0284       end
0285     
0286    end %if ~isempty childTable
0287     
0288   end %if(~isempty(parentKeyVal)
0289 
0290   if(isempty(addTableStructs))
0291     addTableStructs = tableStruct(irecord);
0292   end
0293  
0294   
0295   %if child metadata was not obtained for a question (due to a bad foreign key)
0296   %get child key fields and construct an
0297   %empty child struct so that all tableStruct fields match
0298   if(strcmp(table,'question') & isempty(childStruct))
0299     [childInfo{1:6}] = mysql(conn_id,sprintf('describe %s',childTable));
0300     childFields = childInfo{1};
0301     childStruct = mkstruct({childFields{:} junctionTableInfo{:}});
0302   end
0303   
0304   %add the child structure if it was obtained
0305   if(~isempty(childStruct))
0306     addTableStructs = setfield(tableStruct(irecord),childTable,childStruct);
0307   end
0308   
0309   
0310   %flatten the children structs with the parent struct if set
0311   %to true (currently only done for question table)
0312   if(flattenChildren)
0313     addTableStructs = flatten_children_structs(addTableStructs);
0314   end  
0315   
0316   
0317   % add the new Table structs. We need to do this to a variable
0318   % other than tableStructs (newTableStruct) in order to leave tableStruct intact through
0319   % each iteration of the loop
0320   if(isempty(newTableStruct))
0321     newTableStruct = addTableStructs;
0322   else
0323     newTableStruct(end+1:end+length(addTableStructs)) = addTableStructs;
0324   end
0325   
0326 end %for
0327 
0328 %if a new table structure was created in the for loop above
0329 %then return the new table structure, otherwise return the original
0330 %tableStruct
0331 if(~isempty(newTableStruct))
0332   tableStruct = newTableStruct;
0333 end
0334 
0335 if(strcmp(table,'question'))
0336   for tsIdx = 1:length(tableStruct)
0337     
0338     if(strcmp(table,'question'))
0339       qid = tableStruct(tsIdx).question_id;
0340       sqid = tableStruct(tsIdx).subquestion;
0341       compqid = qid +  sqid ./100;
0342       tableStruct(tsIdx).compqid = compqid;
0343     end
0344     
0345   end
0346 
0347 end
0348 
0349 
0350 if (strcmp(table,'data_format'))
0351   
0352   for tsIdx = 1:length(tableStruct)
0353    
0354     tmpEnumVals = tableStruct(tsIdx).enum_values;
0355     tokenizedEnums= regexp(tmpEnumVals,'"([^"]*)"','tokens');
0356     if(~isempty(tokenizedEnums))
0357       tokenizedEnums = [tokenizedEnums{:}];
0358       tableStruct(tsIdx).enum_values = {tokenizedEnums{:}};
0359     end
0360     
0361   end
0362    
0363 end
0364 
0365 
0366 if(~exist('keep_db_open','var') & exist('temp_conn_id','var'))
0367   mysql(conn_id,'close');
0368 end
0369 
0370 return
0371 
0372