## Purpose The compare and synchronize statement provides one-directional and bi-directional analysis and alignment of data in two tables. It is an extended version of SQL statements like 'upsert' and 'merge' found in other SQL implementations. The two tables can be located in any data container, enabling cross-data container synchronisation of data. In most scenarios, data from both tables is downloaded and compared by Invantive UniversalSQL to establish the necessary actions. Some specific edge cases may have an optimised algorithm to reduce data downloads. In an one-directional approach as specified by 'FROM' or 'TO', the compare and synchronize statement typically analyses the differences between two tables with identically named columns, separating all differences in one out of four groups: - insert: a row exists in the source table and not in the target table; - delete: a row exists not in the source table, but exists in the target table; - update: the row exists in both tables, but the column contents are different. - none: the row exists in both tables and the column contents are equal. Column values are matched on name. By default, all columns having identical names will be included in the matching process. The IGNORE CHANGES TO can be used to exclude columns by name from matching. IGNORE CHANGES TO is typically used to exclude changes in column values that should not trigger a DML-operation, such as for columns whose value is managed by the application, including technical keys. Unique rows in both tables are matched using the list of columns as specified by 'IDENTIFIED BY'. Identifying column NULL contents are considered a specific value for matching; when both the source and target table have a null value, the rows will be compared. Rows are completely ignored for synchronisation when all identifying columns have a NULL value and the clause "IGNORE NULLS" is present as in "IGNORE NULLS", "IGNORE NULLS ON ALL" or "IGNORE NULLS ON IDENTIFIED BY". Null column values are ignored when "IGNORE NULLS" is specified as "IGNORE NULLS", "IGNORE NULLS ON ALL" or "IGNORE NULLS ON VALUES". This allows columns to be filled more easy from multiple sources, each synchronize only completing for which it has data. After analysis, the differences are be filtered to only contain a combination of DML-operations specified using 'WITH'. Applying all DML-operation types INSERT, UPDATE and DELETE would result in the two tables having identical contents. The INSERT and UPDATE operations by default apply to all columns, but columns can be excluded using 'ALL EXCEPT'. Excluding columns is typically used to leave out audit columns such as a generated creation date upon update and still have it included upon insert. For better performance, SYNCHRONIZE uses bulk operations where supported, similar to CREATE TABLE and BULK INSERT. Bulk operations typically offer an order of magnitude better performance than solitary operations. However, error handling can be inconvenient since it is often unclear what specific row triggered an error. Neither, it is not easily established upon an error whether other rows in the same bulk operation have been processed. The BATCHSIZE clause allows specification of the number of rows per bulk operation with a minimum of 1. When BATCHSIZE is not specified, a platform-specific default value is used which can fluctuate due to dynamical management by Invantive UniversalSQL. In case of bi-directional synchronisation, the RESOLVE BY-clause enables specification of the column names whose value determines what table contains the preferred row with most current values. Using left-to-right column preference, both values of all RESOLVE BY columns are inspected. The first column value to have a higher ranking value than the other table selects that table as source. The logic is reversed for a column from higher ranking to lower ranking when DESC is specified. The RESOLVE BY-clause is typically used in combination with columns containing a (synchronised) timestamp value such as UTC time. The 'APPLY TO' syntax is reserved for future use to allow routing DML-operations to other tables. The PARALLEL clause applies the differences using several workers at the same time instead of one row after the other, which shortens the duration of a synchronisation whose target costs a round trip per row. An integer behind PARALLEL limits the number of workers of this statement; without one the session setting `invantive-sql-psql-max-parallel` applies, the same setting that limits the parallel PSQL constructs. The value 1 applies the differences sequentially. Comparing the rows and applying the updates, the inserts and the deletions is spread across the workers, including the trigger blocks. A deletion on a target carrying a primary key remains a single call for the whole set, as it was before. Where no BATCHSIZE is specified, the BEFORE INSERT and AFTER INSERT blocks run once for the whole set as they do sequentially, whereas with BATCHSIZE 1 every block runs on the worker of that row. Where the two tables come from different service providers, both are read at the same time; from one and the same service provider they are read one after the other. The order between the kinds of operation is unchanged: updates, then inserts, then deletions. Within one kind the rows are handed out in the order of the source, whereas the order in which they complete follows the pace of the target. The number of workers is reduced to 1 while a transaction is open on the session and when the statement itself already runs on a worker of a parallel PSQL construct. The limits of the target platform continue to apply, such as the number of concurrent calls per service provider. A PSQL variable declared outside a trigger block is not suitable for counting across workers, and a trigger block which writes one is refused while the statement is compiled, before any row has been read; the message names the trigger and the variable. The package `dbms_atomic` serves that purpose, and collecting rows is done through a table such as one on the InMemoryStorage service provider. Reading such a variable remains allowed, since the enclosing block is suspended while the statement runs. The same trigger without the PARALLEL clause keeps working unchanged. The lines a trigger writes with `dbms_output` stay contiguous and appear in the order of the source rows. By default, the SYNCHRONIZE statement will fail upon the first error returned. Using 'CONTINUE ON FIRST ... ERRORS' the threshold can be increased. However, the statement will fail whenever any error occurred during execution. The row limits of 'EXIT AFTER ... ROWS' and 'ERROR AFTER ... ROWS' remain exact irrespective of the number of workers: a worker claims the rows it is about to attempt before attempting them, so 'EXIT AFTER 5 ROWS' attempts five rows rather than five per worker. Which rows those are is not fixed when the differences are applied in parallel, since the order of completion is not. The differences can then either be: - applied on the target table to make the tables equal, using bulk operations where supported. - returned as a list of content differences (not yet available); - returned as Invantive UniversalSQL DML-statements to achieve synchronisation (not yet available). After checking the output of the last two categories of analysis, the differences can be applied on the target table. In a bi-directional approach, as specified by using the keyword 'AND' between the two table identifiers, deletes will not be returned from the analysis phase and replaced by inserts on the table which does not have the other table's row. The determination of the target for an update is based upon the resolution preference as defined by the 'RESOLVE' clause. The 'RESOLVE' clause either defines to always apply updates to the left or right table as listed in their order using, respectively, 'PREFER LEFT' and 'PREFER RIGHT'. Otherwise, the combined ordinal value of the columns listed in the 'RESOLVE BY' clause will be evaluated and the highest-ranking value is considered to be leading. ## Syntax ```mermaid %%{init: { 'theme': 'base', 'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' }, 'themeVariables': { 'fontSize': '11px', 'fontFamily': 'Arial' } }}%% flowchart TD synchronizeStatement_start((START)) synchronizeStatement_start --> synchronizeStatement_0_0[synchronize] synchronizeStatement_0_0 --> synchronizeStatement_0_1[COMPARE]:::quoted synchronizeStatement_0_1 --> synchronizeStatement_0_2[tableSpec] synchronizeStatement_0_2 --> synchronizeStatement_0_3[TO]:::quoted synchronizeStatement_0_3 --> synchronizeStatement_0_4[AND]:::quoted synchronizeStatement_0_4 --> synchronizeStatement_0_5[tableSpec] synchronizeStatement_0_5 --> synchronizeStatement_0_6[synchronizeIudStatement] synchronizeStatement_0_6 --> synchronizeStatement_0_7[identifiedByMultipleClause] synchronizeStatement_0_7 --> synchronizeStatement_0_8[synchronizeIgnoreClause] synchronizeStatement_0_8 --> synchronizeStatement_0_9[resolveByClause] synchronizeStatement_0_9 --> synchronizeStatement_0_10[applyToClause] synchronizeStatement_0_10 --> synchronizeStatement_0_11[limitClause] synchronizeStatement_0_11 --> synchronizeStatement_0_12[RETURNING]:::quoted synchronizeStatement_0_12 --> synchronizeStatement_0_13[SQL]:::quoted synchronizeStatement_0_13 --> synchronizeStatement_0_14[DIFFERENCES]:::quoted synchronizeStatement_0_14 --> synchronizeStatement_0_15[BATCHSIZE]:::quoted synchronizeStatement_0_15 --> synchronizeStatement_0_16[numericConstant] synchronizeStatement_0_16 --> synchronizeStatement_0_17[PARALLEL]:::quoted synchronizeStatement_0_17 --> synchronizeStatement_0_18[numericConstant] synchronizeStatement_0_18 --> synchronizeStatement_0_19[CONTINUE]:::quoted synchronizeStatement_0_19 --> synchronizeStatement_0_20[ON]:::quoted synchronizeStatement_0_20 --> synchronizeStatement_0_21[FIRST]:::quoted synchronizeStatement_0_21 --> synchronizeStatement_0_22[numericConstant] synchronizeStatement_0_22 --> synchronizeStatement_0_23[ERRORS]:::quoted synchronizeStatement_0_23 --> synchronizeStatement_0_24[synchronizeTriggers] synchronizeStatement_0_24 --> synchronizeStatement_end((END)) ``` ## synchronizeIudStatement ```mermaid %%{init: { 'theme': 'base', 'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' }, 'themeVariables': { 'fontSize': '11px', 'fontFamily': 'Arial' } }}%% flowchart TD synchronizeIudStatement_start((START)) synchronizeIudStatement_start --> synchronizeIudStatement_0_0[WITH]:::quoted synchronizeIudStatement_0_0 --> synchronizeIudStatement_0_1[synchronizeInsertStatement] synchronizeIudStatement_0_1 --> synchronizeIudStatement_0_2[OR]:::quoted synchronizeIudStatement_0_2 --> synchronizeIudStatement_0_3[synchronizeUpdateStatement] synchronizeIudStatement_0_3 --> synchronizeIudStatement_0_4[OR]:::quoted synchronizeIudStatement_0_4 --> synchronizeIudStatement_0_5[synchronizeDeleteStatement] synchronizeIudStatement_0_5 --> synchronizeIudStatement_0_6[synchronizeUpdateStatement] synchronizeIudStatement_0_6 --> synchronizeIudStatement_0_7[OR]:::quoted synchronizeIudStatement_0_7 --> synchronizeIudStatement_0_8[synchronizeDeleteStatement] synchronizeIudStatement_0_8 --> synchronizeIudStatement_0_9[synchronizeDeleteStatement] synchronizeIudStatement_0_9 --> synchronizeIudStatement_end((END)) ``` ## synchronizeInsertStatement ```mermaid %%{init: { 'theme': 'base', 'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' }, 'themeVariables': { 'fontSize': '11px', 'fontFamily': 'Arial' } }}%% flowchart LR synchronizeInsertStatement_start((START)) synchronizeInsertStatement_start --> synchronizeInsertStatement_0_0[INSERT]:::quoted synchronizeInsertStatement_0_0 --> synchronizeInsertStatement_0_1[ALL]:::quoted synchronizeInsertStatement_0_1 --> synchronizeInsertStatement_0_2[EXCEPT]:::quoted synchronizeInsertStatement_0_2 --> synchronizeInsertStatement_0_3[columnList] synchronizeInsertStatement_0_3 --> synchronizeInsertStatement_end((END)) ``` ## synchronizeUpdateStatement ```mermaid %%{init: { 'theme': 'base', 'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' }, 'themeVariables': { 'fontSize': '11px', 'fontFamily': 'Arial' } }}%% flowchart TD synchronizeUpdateStatement_start((START)) synchronizeUpdateStatement_start --> synchronizeUpdateStatement_0_0[UPDATE]:::quoted synchronizeUpdateStatement_0_0 --> synchronizeUpdateStatement_0_1[ALL]:::quoted synchronizeUpdateStatement_0_1 --> synchronizeUpdateStatement_0_2[EXCEPT]:::quoted synchronizeUpdateStatement_0_2 --> synchronizeUpdateStatement_0_3[columnList] synchronizeUpdateStatement_0_3 --> synchronizeUpdateStatement_0_4[IGNORE]:::quoted synchronizeUpdateStatement_0_4 --> synchronizeUpdateStatement_0_5[CHANGES]:::quoted synchronizeUpdateStatement_0_5 --> synchronizeUpdateStatement_0_6[TO]:::quoted synchronizeUpdateStatement_0_6 --> synchronizeUpdateStatement_0_7[columnList] synchronizeUpdateStatement_0_7 --> synchronizeUpdateStatement_end((END)) ``` ## synchronizeDeleteStatement ```mermaid %%{init: { 'theme': 'base', 'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' }, 'themeVariables': { 'fontSize': '11px', 'fontFamily': 'Arial' } }}%% flowchart LR Start((START)) --> synchronizeDeleteStatement synchronizeDeleteStatement -->| | DELETE[DELETE]:::quoted DELETE --> End((END)) ``` ## synchronizeUsingPSqlBlock ```mermaid %%{init: { 'theme': 'base', 'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' }, 'themeVariables': { 'fontSize': '11px', 'fontFamily': 'Arial' } }}%% flowchart LR synchronizeUsingPSqlBlock_start((START)) synchronizeUsingPSqlBlock_start --> synchronizeUsingPSqlBlock_0_0[USING]:::quoted synchronizeUsingPSqlBlock_0_0 --> synchronizeUsingPSqlBlock_0_1[PSQL]:::quoted synchronizeUsingPSqlBlock_0_1 --> synchronizeUsingPSqlBlock_0_2[pSqlBlockNoBatchSeparator] synchronizeUsingPSqlBlock_0_2 --> synchronizeUsingPSqlBlock_end((END)) ``` ## synchronizeTriggers ```mermaid %%{init: { 'theme': 'base', 'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' }, 'themeVariables': { 'fontSize': '11px', 'fontFamily': 'Arial' } }}%% flowchart LR synchronizeTriggers_start((START)) synchronizeTriggers_start --> synchronizeTriggers_0_0[TRIGGERS]:::quoted synchronizeTriggers_0_0 --> synchronizeTriggers_0_1[synchronizeTrigger] synchronizeTriggers_0_1 --> synchronizeTriggers_end((END)) ``` ## synchronizeTrigger ```mermaid %%{init: { 'theme': 'base', 'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' }, 'themeVariables': { 'fontSize': '11px', 'fontFamily': 'Arial' } }}%% flowchart TD synchronizeTrigger_start((START)) synchronizeTrigger_start --> synchronizeTrigger_0_0[BEFORE]:::quoted synchronizeTrigger_0_0 --> synchronizeTrigger_0_1[INSTEAD]:::quoted synchronizeTrigger_0_1 --> synchronizeTrigger_0_2[OF]:::quoted synchronizeTrigger_0_2 --> synchronizeTrigger_0_3[AFTER]:::quoted synchronizeTrigger_0_3 --> synchronizeTrigger_0_4[INSERT]:::quoted synchronizeTrigger_0_4 --> synchronizeTrigger_0_5[UPDATE]:::quoted synchronizeTrigger_0_5 --> synchronizeTrigger_0_6[DELETE]:::quoted synchronizeTrigger_0_6 --> synchronizeTrigger_0_7[ON]:::quoted synchronizeTrigger_0_7 --> synchronizeTrigger_0_8[LEFT]:::quoted synchronizeTrigger_0_8 --> synchronizeTrigger_0_9[RIGHT]:::quoted synchronizeTrigger_0_9 --> synchronizeTrigger_0_10[pSqlBlockNoBatchSeparator] synchronizeTrigger_0_10 --> synchronizeTrigger_end((END)) ``` ## synchronize ```mermaid %%{init: { 'theme': 'base', 'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' }, 'themeVariables': { 'fontSize': '11px', 'fontFamily': 'Arial' } }}%% flowchart LR Start((START)) --> synchronize synchronize -->| | SYNCHRONIZE[SYNCHRONIZE]:::quoted SYNCHRONIZE --> End((END)) synchronize -->| | SYNC SYNC --> End((END)) ``` ## synchronizeIgnoreClause ```mermaid %%{init: { 'theme': 'base', 'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' }, 'themeVariables': { 'fontSize': '11px', 'fontFamily': 'Arial' } }}%% flowchart LR synchronizeIgnoreClause_start((START)) synchronizeIgnoreClause_start --> synchronizeIgnoreClause_0_0[IGNORE]:::quoted synchronizeIgnoreClause_0_0 --> synchronizeIgnoreClause_0_1[NULLS]:::quoted synchronizeIgnoreClause_0_1 --> synchronizeIgnoreClause_end((END)) ``` ## resolveByClause ```mermaid %%{init: { 'theme': 'base', 'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' }, 'themeVariables': { 'fontSize': '11px', 'fontFamily': 'Arial' } }}%% flowchart LR resolveByClause_start((START)) resolveByClause_start --> resolveByClause_0_0[RESOLVE]:::quoted resolveByClause_0_0 --> resolveByClause_0_1[BY]:::quoted resolveByClause_0_1 --> resolveByClause_0_2[sortedColumnList] resolveByClause_0_2 --> resolveByClause_0_3[PREFER]:::quoted resolveByClause_0_3 --> resolveByClause_0_4[LEFT]:::quoted resolveByClause_0_4 --> resolveByClause_0_5[RIGHT]:::quoted resolveByClause_0_5 --> resolveByClause_end((END)) ``` ## applyToClause ```mermaid %%{init: { 'theme': 'base', 'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' }, 'themeVariables': { 'fontSize': '11px', 'fontFamily': 'Arial' } }}%% flowchart LR applyToClause_start((START)) applyToClause_start --> applyToClause_0_0[APPLY]:::quoted applyToClause_0_0 --> applyToClause_0_1[TO]:::quoted applyToClause_0_1 --> applyToClause_0_2[tableSpec] applyToClause_0_2 --> applyToClause_0_3[","]:::quoted applyToClause_0_3 --> applyToClause_0_4[tableSpec] applyToClause_0_4 --> applyToClause_end((END)) ``` ## triggerRecordNameExpression ```mermaid %%{init: { 'theme': 'base', 'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' }, 'themeVariables': { 'fontSize': '11px', 'fontFamily': 'Arial' } }}%% flowchart LR triggerRecordNameExpression_start((START)) triggerRecordNameExpression_start --> triggerRecordNameExpression_0_0[COLON]:::quoted triggerRecordNameExpression_0_0 --> triggerRecordNameExpression_0_1[OLD]:::quoted triggerRecordNameExpression_0_1 --> triggerRecordNameExpression_0_2[NEW]:::quoted triggerRecordNameExpression_0_2 --> triggerRecordNameExpression_0_3[SOURCE]:::quoted triggerRecordNameExpression_0_3 --> triggerRecordNameExpression_end((END)) ``` ## triggerRecordVariableExpression ```mermaid %%{init: { 'theme': 'base', 'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' }, 'themeVariables': { 'fontSize': '11px', 'fontFamily': 'Arial' } }}%% flowchart LR triggerRecordVariableExpression_start((START)) triggerRecordVariableExpression_start --> triggerRecordVariableExpression_0_0[triggerRecordNameExpression] triggerRecordVariableExpression_0_0 --> triggerRecordVariableExpression_0_1["."]:::quoted triggerRecordVariableExpression_0_1 --> triggerRecordVariableExpression_0_2[<a href="Invantive UniversalSQL/Grammar/Identifier" class="internal-link">identifier</a>] triggerRecordVariableExpression_0_2 --> triggerRecordVariableExpression_end((END)) ```