Scripts and Script Arguments¶

Each comparison script file (e.g., pg_compare.sql) contains the following scripts that are defined with execsql’s SCRIPT metacommand:

compare_common

compare_common_vars

compare_changes

compare_changed

The purpose of each script, and the arguments that each expects, are described in the following sections. All of these scripts take the same arguments. The arguments used with Postgres and SQL Server are identical. Because MariaDB and MySQL do not support schemas within a database, a different set of arguments is used to distinguish base and staging tables.

Scripts¶

compare_common: Generates SQL that includes all values of the base table’s primary key that are present in both the base and staging tables, and columns for each attribute indicating whether the value for that attribute is the same or different in the two tables.
compare_common_vals: Generates SQL that includes all values of the base table’s primary key that are present in both the base and staging tables, and two columns for each attribute that show the old value (from the base table) and the new value (from the staging table).
compare_changes: Generates SQL that includes all values of the base table’s primary key that are present in the staging table, and a column indicating whether any of the attributes in the staging table are different from those in the base table.
compare_changed: Generates SQL that includes all values of the base table’s primary key that are present in the base table, and a column indicating whether any of the attributes in the base table are different in the staging table. This example (in the code above) uses a private variable to capture the generated SQL.

Script Arguments¶

Script arguments identify the names of the base and staging schemas, the name of the table for which comparisons are to be done, and the name of the execsql substitution variable that should be used to capture the generated SQL. For MariaDB and MySQL, the base schema name is not specified, and a table name prefix is used instead of a staging schema name.

Required Input Arguments¶

Values for the following parameters must be provided in the ‘WITH ARGUMENTS’ clause of the EXECUTE SCRIPT metacommand.

Postgres and SQL Server¶

base_schema:: The name of the base schema.
staging:: The name of the staging schema.
table:: The name of the table for which comparisons are to be done. The table name should be identical in base and staging schemas.
sql_var:: The name of the execsql substitution variable that should be used to capture the generated SQL. If a local variable is used, the variable name must be prefixed with “+” when used as an argument.

MariaDB and MySQL¶

stage_pfx:: The prefix for staging tables. For example, if the table to be compared is named “person” and the corresponding staging table is named “stg_person”, then this argument should be “stg_”.
table:: The name of the table for which comparisons are to be done. This should be the name of the base table; the corresponding staging table should have the ‘stage_pfx’ prefix prepended.
sql_var:: The name of the execsql substitution variable that should be used to capture the generated SQL. If a local variable is used, the variable name must be prefixed with “+” when used as an argument.

Optional Input Arguments¶

Values for the following parameters may be provided in the WITH ARGUMENTS clause of the EXECUTE SCRIPT metacommand. These allow the comparison to be limited to a subset of the attribute columns in the specified table.

exclude_cols:: A list of attribute columns that should be excluded from the comparison. This should be specified as a comma-separated list of single-quoted column names (as would be used in a SQL ‘in’ clause).
include_cols:: A list of attribute columns that are to be included in the comparison. This should be specified as a comma-separated list of single-quoted column names (as would be used in a SQL ‘in’ clause).

If both the ‘exclude_cols’ and ‘include_cols’ arguments are specified, the ‘include_cols’ argument will be used, and the ‘exclude_cols’ argument will be ignored.