Home > database > mysql_export_resp_demographics_to_subtable.m

mysql_export_resp_demographics_to_subtable

PURPOSE ^

SYNOPSIS ^

function mysql_export_resp_demographics_to_subtable(varargin)

DESCRIPTION ^

 Updates subject table from the demographics responses for a given experiment
 This is useful for older experiments which submitted demographics information
 to the response table instead of the subject table. The function populates
 the subject table from the responses.

 mysql_export_resp_demographics_to_subtable(varargin)

 INPUTS are tag/value pairs:

 'exp_name' - the title of the experiment in the experiment table
 'gender_qid' - the question ID of the gender question
 'race_qid' - the question ID of the race question
 'ethnicity_qid' - the question ID of the ethnicity question
 'db' - the name of the database (if ommitted uses the default database)
 'dryrun' - (optional) 0 or 1. If 1, performs a dry-run and just displays the sql
                          commands to be executed rather than performing the
                          updates. The default is 0.

  Note that this function attempts to perform enum mapping between the
  data_format table and the demographics fields in the subject table.
  It is highly recommended that you perform a dryrun to ensure that the 
  mapping is working (the mapping is hard-coded here, if there are other
  maps they need to be introduced).

  November 4, 2009 - Stefan Tomic, First Version

CROSS-REFERENCE INFORMATION ^

This function calls: This function is called by:

SOURCE CODE ^

0001 function mysql_export_resp_demographics_to_subtable(varargin)
0002 %
0003 % Updates subject table from the demographics responses for a given experiment
0004 % This is useful for older experiments which submitted demographics information
0005 % to the response table instead of the subject table. The function populates
0006 % the subject table from the responses.
0007 %
0008 % mysql_export_resp_demographics_to_subtable(varargin)
0009 %
0010 % INPUTS are tag/value pairs:
0011 %
0012 % 'exp_name' - the title of the experiment in the experiment table
0013 % 'gender_qid' - the question ID of the gender question
0014 % 'race_qid' - the question ID of the race question
0015 % 'ethnicity_qid' - the question ID of the ethnicity question
0016 % 'db' - the name of the database (if ommitted uses the default database)
0017 % 'dryrun' - (optional) 0 or 1. If 1, performs a dry-run and just displays the sql
0018 %                          commands to be executed rather than performing the
0019 %                          updates. The default is 0.
0020 %
0021 %  Note that this function attempts to perform enum mapping between the
0022 %  data_format table and the demographics fields in the subject table.
0023 %  It is highly recommended that you perform a dryrun to ensure that the
0024 %  mapping is working (the mapping is hard-coded here, if there are other
0025 %  maps they need to be introduced).
0026 %
0027 %  November 4, 2009 - Stefan Tomic, First Version
0028   
0029   
0030 for iarg = 1:nargin
0031   
0032   switch(varargin{iarg})
0033     
0034    case 'exp_name'
0035     exp_name = varargin{iarg+1};
0036    case 'gender_qid'
0037     gID = varargin{iarg+1};
0038    case 'race_qid'
0039     rID = varargin{iarg+1};
0040    case 'ethnicity_qid'
0041     eID = varargin{iarg+1};
0042    case 'db'
0043     db = varargin{iarg+1};
0044    case 'dryrun'
0045     dryrun = 1;
0046   end
0047  
0048 end
0049 
0050 try
0051   dryrun;
0052 catch
0053   dryrun = 0;
0054 end
0055 
0056 try
0057   mmc.db = db;
0058   conn_id = mysql_make_conn(mmc);
0059 catch
0060   conn_id = mysql_make_conn;
0061 end
0062 
0063 
0064 %get response table name
0065 sql_resp_table = sprintf(['select response_table from experiment where' ...
0066             ' experiment_title = ''%s'''],exp_name);
0067 resp_table_name = mysql(conn_id,sql_resp_table);
0068 
0069 resp_table_name = resp_table_name{1};
0070 
0071 
0072 subjectFields = {'gender','ethnic_category','racial_category'};
0073 
0074 nSubFields = length(subjectFields);
0075 for thisSubField = subjectFields
0076 
0077   thisSubField = thisSubField{1};
0078   
0079   switch(thisSubField)
0080     
0081    case 'gender'
0082     thisQID = gID;
0083     
0084     enum_map = {'F','Female';
0085         'M','Male';
0086         '','No Answer'};
0087     
0088    case 'ethnic_category'
0089     thisQID = eID;
0090     
0091     enum_map = {'HL','Hispanic or Latino';
0092         'not_HL','Not Hispanic or Latino';
0093         'unknown','Unknown (or care not to report)'};
0094     
0095    case 'racial_category'
0096     thisQID = rID;
0097     enum_map = {'NatAm','American Indian';
0098         'NatHaw','Native Hawaiian';
0099         'AfrAm','Black or African American';
0100         'Asian','Asian';
0101         'Cauc','Caucasian';
0102         'More','More than One Race';
0103         'Unknown','Unknown or No Answer';
0104         'Other','Other'};
0105   end
0106      
0107   %get responses for this question
0108   sql_get_resp = sprintf('select subject_id,response_enum from %s where question_id = %d',resp_table_name,thisQID);
0109   [subID,resp_enum] = mysql(conn_id,sql_get_resp);
0110   
0111   %resolve enum values
0112   sql_get_dfid = sprintf('select answer_format_id from question_x_data_format where question_id = %d',thisQID);
0113   dfID = mysql(conn_id,sql_get_dfid);
0114   sql_get_enum_strings = sprintf('select enum_values from data_format where data_format_id = %d',dfID);
0115   enum_all = mysql(conn_id,sql_get_enum_strings);
0116   enum_tokens = regexp(enum_all{1},'\"([^,]+)\",?','tokens');
0117   nenum = length(enum_tokens);
0118 
0119   for ienum = 1:nenum
0120     enum_strings{ienum} = enum_tokens{ienum}{1};
0121   end
0122 
0123   resp_vals = enum_strings(log2(resp_enum)+1);
0124   
0125   nresp = length(resp_vals);
0126   for iresp = 1:nresp
0127     resp_idx = strmatch(resp_vals{iresp},enum_map(:,2));
0128     mapped_resp_vals{iresp} = enum_map{resp_idx,1};
0129 
0130     %submit to subject table
0131     sql_update_subject_table = sprintf(['update subject set `%s` = ''%s'' where' ...
0132             ' subject_id = ''%s'''],thisSubField, mapped_resp_vals{iresp},subID{iresp});
0133     
0134     if(dryrun)
0135       disp(sql_update_subject_table);
0136     else
0137       mysql(conn_id,sql_update_subject_table);
0138     end
0139     
0140   end
0141 
0142   
0143 end

Generated on Thu 23-May-2019 04:00:50 by m2html © 2003