Homec4science
Diffusion ShoulderDB_ImportSegmentMeasureAnalyze (aterrier-plotUpdate) Browse

R8218/XLS_MySQL_DB/upsert/upsert.m
f619ec087b5eaterrier-plotUpdate

Last Change
Raw File

upsert.m
View Options

function [insertMask, returnedKeys] = upsert(conn,tableName,fieldNames,keyFields,data, varargin)
% UPSERT inserts new and updates old data to a database table
%
% UPSERT(CONNECT,TABLENAME,FIELDNAMES,KEYFIELDS,DATA).
% CONNECT is a database connection object. TABLENAME is the database
% table. FIELDNAMES is a string array of database column names. KEYFIELDS
% is the list of primary key fields that must be matched to perform an
% UPDATE rather than an INSERT. It may be given as a logical (or 0s, 1s)
% array the same length as FIELDNAMES, or a string or cell array of
% strings of key column names (in which case KEYFIELDS must be a subset
% of FIELDNAMES). DATA is a MATLAB cell array.
%
% INSERTEDMASK = UPSERT(...) returns a logical vector with one element for
% each row of DATA, indicating whether the "upsert" operation meant that
% corresponding row of DATA was inserted (TRUE) or merely updated (FALSE).
%
% UPSERT(...,'dateFields',DATEFIELDS) allows a DATE type field to be used
% as one of the primary key fields. DATEFIELDS is specified equivalently to
% KEYFIELDS. Each primary key DATE type field's data MUST be given as an
% ANSI string literal (i.e., '1998-12-25'), rather than a MATLAB datenum
% number or a differently formatted date string.
% (see http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements003.htm#SQLRF51062)
%
% UPSERT(...,'updateFcn',FUNCTION_HANDLE)
% UPSERT(...,'insertFcn',FUNCTION_HANDLE) optionally allows replacement
% functions for the default use of MATLAB's "update" and "fastinsert".
%
% UPSERT(...,'debug',true) prints out diagnostic information.
%
% Example:
%
% Imagine a database table "PHONE_NOS" with data like:
% PERSONID | TYPE | NUMBER
% 1 'HOME' 1234567
% 1 'MOB' 1222222
% 2 'HOME' 9888888
%
% Then the MATLAB commands:
% newNos = {1 'MOB' 4444444
% 2 'MOB' 5555555};
% INS = upsert(conn, 'PHONE_NOS', {'PERSONID','TYPE','NUMBER'}, [1 1 0], newNos)
%
% Would result in the table having contents:
% PERSONID | TYPE | NUMBER
% 1 'HOME' 1234567
% 1 'MOB' 4444444
% 2 'HOME' 9888888
% 2 'MOB' 5555555
%
% The returned variable (INS) would be [0; 1], meaning the second row was
% updated, the first row was inserted.
% Author: Sven Holcombe, 2015-09-01
% Handle user configuration input
IP = inputParser;
IP.addParameter('updateFcn', @update, @(x)isa(x,'function_handle'));
IP.addParameter('insertFcn', @fastinsert, @(x)isa(x,'function_handle'));
IP.addParameter('dateFields', []);
IP.addParameter('returnKeys',false,@(x)iscellstr(x)||(isscalar(x)&&nnz(x==[0 1])==1));
IP.addParameter('debug', false);
IP.parse(varargin{:});
doPrint = IP.Results.debug;
updateFcn = IP.Results.updateFcn;
insertFcn = IP.Results.insertFcn;
doReturnKeys = iscellstr(IP.Results.returnKeys) || IP.Results.returnKeys;
if ~doReturnKeys && nargout>1
warning('upsert:noKeys', 'Second output (returned keys) will be empty because ''returnKeys'' option was not set')
end
% Firstly, handle large data sets. Later we will use an IN clause with a
% comma-separated list to check key fields. Oracle has a limit of 1000
% items in a list, so let's process 1000 at a time at most. Not an optimal
% solution but best solutions will differ by database flavour so this is
% adequate for the moment.
numRows = size(data,1);
returnedKeys = zeros(numRows,0);
if numRows>1000
insertMask = true(numRows,1);
chunks = unique([1:999:numRows numRows+1]);
for i = 1:length(chunks)-1
dataInds = chunks(i):chunks(i+1)-1;
[insertMask(dataInds), rk] = ...
upsert(conn,tableName,fieldNames,keyFields,data(dataInds,:), varargin{:});
if doReturnKeys
if i==1
returnedKeys = zeros(numRows,size(rk,2));
end
returnedKeys(dataInds,:) = rk;
end
end
return;
end
% keyFields may be input as:
% - A string: 'id'
% - A cellstr: {'id','groupNo'}
% - A logical mask the same size as fieldNames (true where field is a key)
% - Indices into fieldNames of the key fields
% keyFields will be transformed to the indices representation below
keyFields = convertSubsetOfFieldsToIndices(keyFields, fieldNames);
% Get a numeric array of which fields are DATE types for comparison
dateFields = convertSubsetOfFieldsToIndices(IP.Results.dateFields, fieldNames);
if isempty(data)
insertMask = true(0,1);
return;
end
% Currently it's easier (if perhaps slightly slower) to treat data as a
% cell regardless of what format it was provided as.
if isnumeric(data)
data = num2cell(data);
end
% Which fields are keyFields? Build lists of them for an SQL fetch
keyFieldsCell = fieldNames(keyFields);
keyFieldsIsnumeric = cellfun(@(x)isnumeric(x)||islogical(x), data(1,keyFields));
keyFieldsIsdatestr = ismember(keyFields, dateFields);
keyFieldsListStr = sprintf('%s,',keyFieldsCell{:});
% We don't want to gather the whole table. Only the rows matching the
% primary key fields. This is most generalised by building IN () lists from
% a single database query, rather than sending/recieving one query for
% every row of "data" being upserted.
inClauses = cell(length(keyFields),1);
for i=1:length(keyFields)
if keyFieldsIsnumeric(i)
inSet = unique([data{:,keyFields(i)}]);
if all(inSet==round(inSet))
inStr = sprintf('%d,',inSet);
else
inStr = sprintf('%g,',inSet);
end
elseif ischar(data{1,keyFields(i)})
inSet = unique(data(:,keyFields(i)));
if keyFieldsIsdatestr(i)
inStr = sprintf('date ''%s'',',inSet{:});
else
inStr = sprintf('''%s'',',inSet{:});
end
else
error('upsert:badKey', 'Primary key field cannot contain %s data',class(data{1,keyFields(i)}))
end
if length(inSet)>1
inClauses{i} = sprintf('%s IN (%s)', keyFieldsCell{i}, inStr(1:end-1));
else
inClauses{i} = sprintf('%s = %s', keyFieldsCell{i}, inStr(1:end-1));
end
end
% Fetch all table rows potentially matching the data we want to upsert
fetchWhereClause = sprintf(' %s AND', inClauses{:});
fetchSqlStr = sprintf('SELECT %s FROM %s WHERE %s', keyFieldsListStr(1:end-1), tableName, fetchWhereClause(1:end-3));
if doPrint, fprintf('Fetching %s data in %s matching given data...', keyFieldsListStr(1:end-1), tableName), end
fetchedData = fetch(conn, fetchSqlStr);
if doPrint, fprintf(' done. (%d potential matches found)\n', size(fetchedData,1)), end
% Build a map of which rows to be upserted already exist in the table.
insertMask = true(size(data,1),1); % One
if ~isempty(fetchedData)
eqMap = false(size(data,1), size(fetchedData,1), length(keyFields));
for i = 1:length(keyFields)
if keyFieldsIsnumeric(i)
thisUpsertData = cell2mat(data(:,keyFields(i)));
thisFetchedData = cast(cell2mat(fetchedData(:,i)), 'like',thisUpsertData);
eqMap(:,:,i) = bsxfun(@eq, thisUpsertData, thisFetchedData');
elseif keyFieldsIsdatestr(i)
thisUpsertData = datenum(data(:,keyFields(i)));
thisFetchedData = datenum(fetchedData(:,i));
eqMap(:,:,i) = bsxfun(@eq, thisUpsertData, thisFetchedData');
else
thisUpsertData = data(:,keyFields(i));
thisFetchedData = fetchedData(:,i)';
eqCell = cellfun(@(x)strcmp(x, thisFetchedData), thisUpsertData, 'Un',0);
eqMap(:,:,i) = cat(1, eqCell{:});
end
end
pkeysMatchMap = all(eqMap,3);
insertMask = ~any(pkeysMatchMap,2);
end
% First find any data rows that do NOT yet exist in table. Insert them.
if any(insertMask)
if doPrint, fprintf('Inserting %d data rows not currently in %s...', nnz(insertMask), tableName), end
if doReturnKeys
insertedRK = insertFcn(conn,tableName,fieldNames,data(insertMask,:),'returnKeys',IP.Results.returnKeys);
else
insertFcn(conn,tableName,fieldNames,data(insertMask,:));
end
if doPrint, fprintf(' done.\n'), end
else
insertedRK = [];
end
% Next, update ALL rows to the values given in data. First build WHERE.
whereEqClauses = cell(numRows, length(keyFields));
for i=1:length(keyFields)
if keyFieldsIsnumeric(i)
whereEqClauses(:,i) = cellfun(@(dat)sprintf('%s = %g', keyFieldsCell{i}, dat), data(:,keyFields(i)),'Un',0);
elseif keyFieldsIsdatestr(i)
whereEqClauses(:,i) = cellfun(@(dat)sprintf('%s = date ''%s''', keyFieldsCell{i}, dat), data(:,keyFields(i)),'Un',0);
else
whereEqClauses(:,i) = cellfun(@(dat)sprintf('%s = ''%s''', keyFieldsCell{i}, dat), data(:,keyFields(i)),'Un',0);
end
end
dataWhereClauses = cellfun(@(strs)sprintf(' %s AND',strs{:}), num2cell(whereEqClauses,2),'Un',0);
dataWhereClauses = cellfun(@(str)['WHERE ' str(1:end-3)], dataWhereClauses, 'Un',0);
% Next, run the update on all the NON-keyField fields (since the key fields
% themselves are being matched, so won't change). Note that the "update"
% function can be replaced by a user's modified update function.
otherFields = setdiff(1:length(fieldNames), keyFields);
if doPrint, fprintf('Updating %d data rows (%d new, %d old) in %s...', length(insertMask), nnz(insertMask), nnz(~insertMask), tableName), end
if doReturnKeys
updatedRK = updateFcn(conn,tableName,fieldNames(otherFields),data(~insertMask,otherFields), dataWhereClauses(~insertMask),'returnKeys',IP.Results.returnKeys);
returnedKeys = zeros(size(cat(1,insertedRK, updatedRK)));
if any(insertMask)
returnedKeys(insertMask,:) = insertedRK;
end
if any(~insertMask)
returnedKeys(~insertMask,:) = updatedRK;
end
else
updateFcn(conn,tableName,fieldNames(otherFields),data(~insertMask,otherFields), dataWhereClauses(~insertMask));
end
if doPrint, fprintf(' done.\n'); end
function subFields = convertSubsetOfFieldsToIndices(subFields, allFields)
% convert subFields from various classes to a numeric index of allFields
% may be input as:
% - A string: 'id'
% - A cellstr: {'id','groupNo'}
% - A logical (or 0,1) mask the same size as fieldNames
% - Indices into fieldNames of the key fields
% keyFields will be transformed to the indices representation below
if ischar(subFields) || iscellstr(subFields)
subFields = ismember(upper(allFields), upper(subFields));
end
if isnumeric(subFields) && (any(subFields==0) || nnz(subFields==1)>1)
subFields = logical(subFields);
end
if islogical(subFields)
subFields = find(subFields);
end
c4science · Help