ensemble_migrate_expdata

 migrates experiment data (only questions are currently supported) from one database to another

 POSSIBLE FUTURE EXTENSIONS:
  - migration of complete forms or experiments. This would probably involve implementing recursion
  - Detection of questions, forms, or experiments that already exist in the
    destination. In this case, the pre-existing destination IDs in addition to
    the IDs of records that are created should be returned. This
    functionality would be essential if implementing the migration of forms
    and experiments.
  
 INPUTS
  the following tag/value pairs:
        'table' -            the name of the table to migrate (only 'question' table currently supported)
        'conn_id_from' -     the connection ID of the originating database (optionally can provide 'database_from')
        'conn_id_to' -       the connection ID of the destination database (optionally can provide 'database_to')
        'database_from' -    the name of the originating database (used if conn_id_from is not provided)
        'database_to' -      the name of the destination database (used if conn_id_to is not provided)
        'primary_key_vals' - a vector (array) of the primary keys corresponding to the records to migrate
                             (e.g. question_id)
        'host_from' -        The hostname of the originating database (default host used if ommitted)
        'host_to' -          The hostname of the destination database (default host used if ommitted)
        'ignore' -           fields to ignore. They will be ommitted during migration
                             and the database will assign a default value (if specified)

 OUTPUT(S):
  A vector of primary keys corresponding to the records that were created at
  the destination.

 Example:

 destIDs = ensemble_migrate_expdata('table','question','database_from','ensemble_tarp','database_to',...
                                    'ensemble_main','primary_key_vals',[100 101 102],'ignore','question_category');
            

 **********************************************************************************************************  

 "Ensemble" is the proprietary property of The Regents of the University of California ("The Regents.")

  Copyright (c) 2005-09 The Regents of the University of California, Davis campus. All Rights Reserved.

  Redistribution and use in source and binary forms, with or without modification, are permitted by
  nonprofit, research institutions for research use only, provided the conditions in the included
  license agreement are met.

  Refer to  for the license agreement,
  

 **********************************************************************************************************

 Author(s):
 September 24, 2009 - Stefan Tomic, first version

0001 function outData = ensemble_migrate_expdata(varargin)
0002 % migrates experiment data (only questions are currently supported) from one database to another
0003 %
0004 % POSSIBLE FUTURE EXTENSIONS:
0005 %  - migration of complete forms or experiments. This would probably involve implementing recursion
0006 %  - Detection of questions, forms, or experiments that already exist in the
0007 %    destination. In this case, the pre-existing destination IDs in addition to
0008 %    the IDs of records that are created should be returned. This
0009 %    functionality would be essential if implementing the migration of forms
0010 %    and experiments.
0011 %
0012 % INPUTS
0013 %  the following tag/value pairs:
0014 %        'table' -            the name of the table to migrate (only 'question' table currently supported)
0015 %        'conn_id_from' -     the connection ID of the originating database (optionally can provide 'database_from')
0016 %        'conn_id_to' -       the connection ID of the destination database (optionally can provide 'database_to')
0017 %        'database_from' -    the name of the originating database (used if conn_id_from is not provided)
0018 %        'database_to' -      the name of the destination database (used if conn_id_to is not provided)
0019 %        'primary_key_vals' - a vector (array) of the primary keys corresponding to the records to migrate
0020 %                             (e.g. question_id)
0021 %        'host_from' -        The hostname of the originating database (default host used if ommitted)
0022 %        'host_to' -          The hostname of the destination database (default host used if ommitted)
0023 %        'ignore' -           fields to ignore. They will be ommitted during migration
0024 %                             and the database will assign a default value (if specified)
0025 %
0026 % OUTPUT(S):
0027 %  A vector of primary keys corresponding to the records that were created at
0028 %  the destination.
0029 %
0030 % Example:
0031 %
0032 % destIDs = ensemble_migrate_expdata('table','question','database_from','ensemble_tarp','database_to',...
0033 %                                    'ensemble_main','primary_key_vals',[100 101 102],'ignore','question_category');
0034 %
0035 %
0036 % **********************************************************************************************************
0037 %
0038 % "Ensemble" is the proprietary property of The Regents of the University of California ("The Regents.")
0039 %
0040 %  Copyright (c) 2005-09 The Regents of the University of California, Davis campus. All Rights Reserved.
0041 %
0042 %  Redistribution and use in source and binary forms, with or without modification, are permitted by
0043 %  nonprofit, research institutions for research use only, provided the conditions in the included
0044 %  license agreement are met.
0045 %
0046 %  Refer to  for the license agreement,
0047 %
0048 %
0049 % **********************************************************************************************************
0050 %
0051 % Author(s):
0052 % September 24, 2009 - Stefan Tomic, first version
0053   
0054   
0055 for iarg = 1:2:nargin
0056   
0057   switch(varargin{iarg})
0058     
0059    case 'table'
0060     table = varargin{iarg+1};
0061    case 'conn_id_from'
0062     conn_id_from = varargin{iarg+1};
0063    case 'conn_id_to'
0064     conn_id_to = varargin{iarg+1};
0065    case 'database_from'
0066     database_from = varargin{iarg+1};
0067    case 'database_to'
0068     database_to = varargin{iarg+1};
0069    case 'primary_key_vals'
0070     primary_key_vals = varargin{iarg+1};
0071    case 'host_from'
0072     host_from = varargin{iarg+1};
0073    case 'host_to'
0074     host_to = varargin{iarg+1};
0075    case 'ignore'
0076     ignore = varargin{iarg+1};
0077     
0078   end
0079 end
0080 
0081 % if host_from and host_to were not supplied, then make these blank
0082 % mysql_make_conn will assign the default host
0083 if(~exist('host_from','var'))
0084   host_from = '';
0085 end
0086 
0087 if(~exist('host_to','var'))
0088   host_to = '';
0089 end
0090 
0091 try
0092   
0093   %set ignore to only those fields which are supported to ignore
0094   switch(table)
0095     case 'question'
0096      ignore = intersect(ignore,'question_category');
0097   end
0098   
0099 catch
0100   ignore = {};
0101 end
0102 
0103 try
0104   status_conn_id_from = mysql(conn_id_from,'status');
0105 catch
0106   %if try failed, conn_id variable is probably not set
0107   status_conn_id_from = 1;
0108 
0109   %find the next available conn id
0110   for conn_id_from = 1:9
0111     if(mysql(conn_id_from,'status'))
0112       break;
0113     end
0114     
0115   end
0116   
0117   if(conn_id_from == 9 && ~mysql(conn_id_from,'status'))
0118     error('Could not find an available database connection');
0119   end
0120   
0121   %in case this was a connection that had an error status (instead of closed
0122   %status), close it
0123   mysql(conn_id_from,'close');
0124   
0125 end
0126 
0127 try
0128   status_conn_id_to = mysql(conn_id_to,'status');
0129 catch
0130   status_conn_id_to = 1;
0131 
0132   %if try failed, conn_id variable is probably not set
0133   
0134   %find the next available conn id
0135   for conn_id_to = setdiff(1:9,conn_id_from)
0136     if(mysql(conn_id_to,'status'))
0137       break;
0138     end
0139   end
0140   
0141   if(conn_id_to == 9 && ~mysql(conn_id_to,'status'))
0142     error('Could not find an available database connection');
0143   end
0144   
0145   %in case this was a connection that had an error status (instead of closed
0146   %status), close it
0147   mysql(conn_id_to,'close');
0148   
0149 end
0150 
0151 % if status was non-zero, then the connection id is not valid
0152 % (or it wasn't supplied and was assigned above)
0153 % establish a connection here
0154 if(status_conn_id_from)
0155   conn_id_from = mysql_make_conn(host_from,database_from,conn_id_from);
0156 end
0157 
0158 if(status_conn_id_to)
0159   conn_id_to = mysql_make_conn(host_to,database_to,conn_id_to);
0160 end
0161 
0162 switch(table)
0163  case 'experiment'
0164   primary_key = 'experiment_id';
0165   ignoreCompare = {'experiment_id'};
0166  case 'form'
0167   primary_key = 'form_id';
0168  case 'question'
0169   primary_key = 'question_id';
0170   ignoreCompare = {'question_id','compqid','data_format_id','locked'};
0171 end
0172 
0173 %obtain the metadata
0174 tableMeta = mysql_extract_metadata('table',table,primary_key,primary_key_vals, ...
0175                  'conn_id',conn_id_from);
0176 
0177 %now submit the data to the destination database
0178 %the data types will be different for different tables
0179 %so we need a big switch block in here
0180 
0181 
0182   switch(table)
0183    
0184    case 'question'
0185     
0186     nQuestions = length(primary_key_vals);
0187     for iQuestion = 1:nQuestions
0188       oldQid = primary_key_vals(iQuestion);
0189       metaQids = [tableMeta.question_id];
0190       metaIdx = find(oldQid == metaQids,1,'first');
0191       
0192       %obtaining the question text from the database, since
0193       %mysql_extract_metadata replaces question_text with the first header
0194       question_text = mysql(conn_id_from,sprintf('select question_text from question where question_id = %d',oldQid));
0195       question_text = question_text{1};
0196       question_category = tableMeta(metaIdx).question_category;
0197       heading_format = tableMeta(metaIdx).heading_format;
0198       locked = 'F'; %the question is new to the new database, so unlock it
0199     
0200       %mostly, one would just want to ignore question_category, so that's why
0201       %ignore is only implemented here
0202       insertFields = setdiff({'question_text','question_category','heading_format','locked'},ignore);
0203       nflds = length(insertFields);
0204       for ifld = 1:nflds
0205     insertVals{1}{ifld}  = eval(insertFields{ifld});
0206       end
0207       newQid = mysql_insert_data('conn_id',conn_id_to,'table','question','fields',insertFields,'record_values',insertVals);
0208   
0209       qidMap(iQuestion,1:2) = [oldQid newQid];
0210       
0211     end
0212     
0213     
0214     
0215     nMeta = length(tableMeta);
0216     for iMeta = 1:nMeta
0217       origQid = tableMeta(iMeta).question_id;
0218       dfType = tableMeta(iMeta).type;
0219       enum_values = tableMeta(iMeta).enum_values;
0220       subquestion = tableMeta(iMeta).subquestion;
0221       heading = tableMeta(iMeta).heading;
0222       range = tableMeta(iMeta).range;
0223       default = tableMeta(iMeta).default;
0224       html_field_type = tableMeta(iMeta).html_field_type;
0225       required = tableMeta(iMeta).required;
0226     
0227       %try to find a suitable data_format_id in the new database, if this
0228       %type already exists
0229     
0230       %reformat enum_values to values that exist in the database
0231       if(isempty(enum_values))
0232     expr_enum_values = 'is NULL';
0233       else
0234     %escape any special characters in the enum
0235     nEnum = length(enum_values);
0236     for iEnum = 1:nEnum
0237       esc_enum_values{iEnum} = regexptranslate('escape',enum_values{iEnum});
0238     end
0239     expr_enum_values = regexprep(esc_enum_values,'^.*$','[[.quotation-mark.]]$0[[.quotation-mark.]]');
0240     expr_enum_values = ['regexp ''^' cell2str(expr_enum_values,'[[:space:]]*,[[:space:]]*') '$'''];
0241       end
0242       
0243       %see if this data type exists in destination
0244       dfID = mysql(conn_id_to,sprintf('select data_format_id from data_format where type = ''%s'' and enum_values %s',...
0245                     dfType,expr_enum_values));
0246     
0247       if(~isempty(dfID))
0248     newDfID = dfID;
0249       else
0250     if(~isempty(enum_values))
0251       quoted_enum_values = regexprep(enum_values,'^.*$','"$0"');
0252       enum_str = cell2str(quoted_enum_values,',');
0253       newDfID = mysql_insert_data('conn_id',conn_id_to,'table','data_format','fields',{'type','enum_values'},'record_values',{{dfType,enum_str}});
0254     else
0255       newDfID = mysql_insert_data('conn_id',conn_id_to,'table','data_format','fields',{'type'},'record_values',{{dfType}});
0256     end
0257       end
0258    
0259       destQid = qidMap(find(qidMap(:,1) == origQid),2);
0260     
0261       %insert new record to question_x_data_format
0262       qdfFieldNames = {'question_id','subquestion','answer_format_id','heading','range','default',...
0263                'html_field_type','required'};
0264       qdfValues = {{destQid,subquestion,newDfID,heading,range,default,html_field_type,required}};
0265       mysql_insert_data('conn_id',conn_id_to,'table','question_x_data_format','fields',qdfFieldNames,'record_values',qdfValues);
0266       
0267     end %for iMeta
0268     primary_key_list = qidMap(:,2);
0269     
0270   end %switch(table)
0271 
0272 
0273 
0274 %if we made the database connections in this function, close them
0275 if(status_conn_id_from)
0276   mysql(conn_id_from,'close');
0277 end
0278 
0279 if(status_conn_id_to)
0280   mysql(conn_id_to,'close');
0281 end
0282 
0283 outData = primary_key_list;
0284