mysql_insert_data

 inserts new records into a database. Returns vector of ids that were inserted

 INPUT(S):
 arguments are param/value pairs:
  'table': name of the table to insert data into
  'fields': cell array of field names to insert into (should be in the same
            order as values)
  'record_values': a cell array of cell arrays. Each inner cell array is a
                   record with the values to be inserted for a given record, in the same order
                   specified in 'fields'
  'mysql': mysql info, including conn_id and encryption key if necessary

  OUTPUT(S):
  A vector of primary key IDs (tables with composite keys are not supported yet)
  If a duplicate record was inserted, the highest primary key value is
  returned, and a warning is given

  Example:
  inserted_id = mysql_insert_data('table','data_format','fields',{'type','enum_values'},...
                {{'enum','"yes","no"'},{'enum','"left","right"'}});

  Author(s):
  Sept 23, 2009 - Stefan Tomic, first version
  03Sep2012 - PJ added support for encrypted fields; removed WHERE clause
                 handling for returning full matches on inserted IDs.
                 Matching is performed on primary key

0001 function key_list = mysql_insert_data(varargin)
0002 % inserts new records into a database. Returns vector of ids that were inserted
0003 %
0004 % INPUT(S):
0005 % arguments are param/value pairs:
0006 %  'table': name of the table to insert data into
0007 %  'fields': cell array of field names to insert into (should be in the same
0008 %            order as values)
0009 %  'record_values': a cell array of cell arrays. Each inner cell array is a
0010 %                   record with the values to be inserted for a given record, in the same order
0011 %                   specified in 'fields'
0012 %  'mysql': mysql info, including conn_id and encryption key if necessary
0013 %
0014 %  OUTPUT(S):
0015 %  A vector of primary key IDs (tables with composite keys are not supported yet)
0016 %  If a duplicate record was inserted, the highest primary key value is
0017 %  returned, and a warning is given
0018 %
0019 %  Example:
0020 %  inserted_id = mysql_insert_data('table','data_format','fields',{'type','enum_values'},...
0021 %                {{'enum','"yes","no"'},{'enum','"left","right"'}});
0022 %
0023 %  Author(s):
0024 %  Sept 23, 2009 - Stefan Tomic, first version
0025 %  03Sep2012 - PJ added support for encrypted fields; removed WHERE clause
0026 %                 handling for returning full matches on inserted IDs.
0027 %                 Matching is performed on primary key
0028   
0029 encrypted = {};
0030 
0031 for iarg = 1:2:nargin
0032   
0033   switch(varargin{iarg})
0034     
0035    case 'table'
0036     table = varargin{iarg+1};
0037    case 'fields'
0038     fields = varargin{iarg+1};
0039    case 'record_values'
0040     values = varargin{iarg+1};
0041    case 'conn_id'
0042          conn_id = varargin{iarg+1};
0043         case 'encrypted_flds'
0044             encrypted = varargin{iarg+1};
0045         case 'mysql'
0046             mysql_params = varargin{iarg+1};
0047   end
0048  
0049 end
0050 
0051 try
0052   conn_id;
0053 catch
0054     try
0055         conn_id = mysql_params.conn_id;
0056     catch
0057         conn_id = 0;
0058     end
0059 end
0060 
0061 % Make sure we have an encryption key if necessary
0062 if ~isempty(encrypted)
0063     if ~exist('mysql_params','var') || isempty(mysql_params.enc_key)
0064         error('Encrypted fields requested but no encryption key found')
0065     end
0066 end
0067 
0068 mysql_check_conn(conn_id,'open');
0069 
0070 primary_key = mysql_table_key(table,conn_id);
0071 
0072 if(iscell(primary_key))
0073   error('Composite keys are not supported by this function.');
0074 end
0075 
0076 %enclose fieldnames in backquotes
0077 origFields = fields;
0078 fields = regexprep(fields,'^.*$','`$0`');
0079 numFields = length(fields);
0080 
0081 fieldString = cell2str(fields,',');
0082 
0083 % Check to see whether data are coming in as a cell array within a single
0084 % column or a cell array for each of the fields
0085 if size(values,2) == numFields
0086     dataByCol = true;
0087     
0088     % Make sure they are of equal length
0089     nRecords = cellfun('length',values);
0090     if any(diff(nRecords))
0091         error('%s: Columns of values have unequal lengths', mfilename)
0092     else
0093         nRecords = nRecords(1);
0094     end
0095 else
0096     dataByCol = false;
0097     nRecords = length(values);
0098 end
0099 
0100 key_list = [];
0101 for iRecord = 1:nRecords
0102     
0103     if dataByCol
0104         thisRecord = {};
0105         for ifld = 1:numFields
0106             thisRecord{ifld} = values{ifld}{iRecord};
0107         end
0108     else
0109         thisRecord = values{iRecord};
0110     end
0111     
0112     nValues = length(thisRecord);
0113     
0114     %construct a value string for insert command
0115     for iValue = 1:nValues
0116         encrypt = ismember(origFields{iValue}, encrypted);
0117         
0118         if strcmp(origFields{iValue}, primary_key)
0119             is_primary = true;
0120             primary_value = thisRecord{iValue};
0121         else
0122             is_primary = false;
0123         end
0124         
0125         %only supporting types double or char
0126         switch(class(thisRecord{iValue}))
0127             case 'double'
0128                 valueStringArray{iValue} = num2str(thisRecord{iValue});
0129                 if is_primary
0130                     primary_val_str = sprintf('%d', thisRecord{iValue});
0131                 end
0132                 if isempty(thisRecord{iValue})
0133                     valueStringArray{iValue} = 'NULL';
0134                 end
0135             case 'char'
0136                 %note that we're replacing empty strings with NULL. This should be fine,
0137                 %since empty strings are generally represented by NULL in Ensemble
0138                 if(isempty(thisRecord{iValue}))
0139                     valueStringArray{iValue} = 'NULL';
0140                     whereClause{iValue} = sprintf('%s is NULL',fields{iValue});
0141                 else
0142                     if encrypt
0143                         valueStringArray{iValue} = ['aes_encrypt("' thisRecord{iValue} '","' mysql_params.enc_key '")'];
0144                     else
0145                         valueStringArray{iValue} = ['''' thisRecord{iValue} ''''];
0146                     end
0147                 end
0148                 if is_primary
0149                     primary_val_str = thisRecord{iValue};
0150                 end
0151         end
0152         
0153     end
0154     
0155     % Check to see if this particular entry already exists
0156     mysql_str = sprintf('SELECT %s FROM %s WHERE %s="%s"', primary_key, table, primary_key, primary_val_str);
0157     existID = mysql(conn_id, mysql_str);
0158     if ~isempty(existID)
0159         fprintf('Found %d entries for ID: %s ... skipping ...\n', length(existID), primary_val_str);
0160         
0161         % Implement an update protocol if we don't automatically want to skip
0162         continue
0163     end
0164     
0165     valueString = cell2str(valueStringArray,',');
0166     sqlString = sprintf('insert into %s (%s) values(%s)',table,fieldString,valueString);
0167     mysql(conn_id,sqlString);
0168     
0169     mysql_str = sprintf('select %s from %s where %s="%s"',primary_key,table,primary_key, primary_val_str);
0170     insertedID = mysql(conn_id,mysql_str);
0171     
0172     %return only one ID per inserted record (multiple IDs may be returned if this
0173     %is a duplicate record). Throw a warning if this is a duplicate
0174     if(length(insertedID) > 1)
0175         warning('Multiple records exist with this set of values');
0176     elseif(isempty(insertedID))
0177         error('Failed to insert new record or inserted record not found');
0178     end
0179     
0180     if isnumeric(insertedID)
0181         insertedID = insertedID(insertedID == max(insertedID));
0182         key_list(iRecord,1) = insertedID;
0183     else
0184         insertedID = insertedID(end);
0185         key_list{iRecord,1} = insertedID;
0186     end
0187     
0188 end
0189 
0190 
0191