(Glue-2305) App API

Table management

Create an SNP Glue^TM table in the specified source, name, and table definition.

call core.create_glue_table(<Source>, <Table>, <Table definition>);

Alter an SNP Glue^TM table in the specified source, name, and table definition

call core.alter_glue_table(<Source>, <Table>, <Table definition>);

Table definition formatting:
{
-- Example of column definition, each column should have said definition
{"fieldname": "<fieldname>", "type": <type>, "key": <true/false>, "comment": "<comment>" }
}
-- Fieldname = Name of the column
-- Type      = Data type of the column
-- Key       = If the column is part of the primary key or not
-- Comment   = Column comment  

Drop an SNP Glue^TM table by specifying the source where it is located and its name. Its merge-related artifacts are dropped as well → stream, view, task, and historical table. Before the historical table is deleted it is backed up as <table name>_BAK_<current timestamp> e.g. 'ZZVBAK_BAK_20230606084229'.

call core.drop_glue_table(<Source>, <Table>);

Merge

Start the merge on a specific source using its settings.

call core.start_merge(<Source>);

Start merge by providing all the necessary information.

core.start_merge(<Warehouse>, 
                 <App name>,
                 <Source>,
                 <Metadata stream name>,
                 <Metadata table name>,
                 <Historical table suffix>,
                 <Control schedule period>, 
                 'MINUTE',
                 <Merge schedule period>, 
                 'MINUTE');

Resumes all merges inside a source. This only applies to MERGE_<table> tasks and not the 'START_CONTROL' task. Call ‘CORE.RESUME_CONTROL’ for resuming the 'START_CONTROL’ task.

call core.resume_merge(<Source>); 

Resumes merge on a specific table.

call core.resume_merge(<Source>, <Table>);

Resumes merge on an array of tables.

call core.resume_merge(<Source>, <Array_of_tables>);

Suspends all merges inside a source. This only applies to MERGE_<table> tasks and not the ‘START_CONTROL' task. Call ‘CORE.SUSPEND_CONTROL’ for suspending the 'START_CONTROL’ task.

call core.suspend_merge(<Source>);

Suspends merge on a specific table.

call core.suspend_merge(<Source>, <Table>);

Suspend merge on an array of tables.

call core.suspend_merge(<Source>, <Array_of_tables>);

Resume ‘START_CONTROL’ task.

call core.resume_control(<Source>);

Suspend the ‘START_CONTROL’ task.

call core.suspend_control(<Source>);

Settings

Get settings of specified scope → 'APP' for application settings, 'DEFAULT' for default (template) source settings, <Source> for specific source settings.

call core.get_settings(<Scope>);

Update settings overwrite existing settings with the same key and appended new ones.

call core.update_merge(<Scope>, to_variant(parse_json('<Settings in json formatting>')));

Example: call core.update_settings('<Scope>',  to_variant(parse_json('
                                              {"warehouse": "<Warehouse>",
                                               "metadata_table": "<Metadata table name>",
                                               "stream_name": "<Stream name>"})));

Set settings completely overwrite all existing values.

call core.set_settings(<Source>, to_variant(parse_json('<Settings in json formatting>')));

Example: call core.set_settings('<Source>','{"warehouse": "<Warehouse>",
                                             "metadata_table": "<Metadata table name>",
                                             "stream_name": "<Stream name>"});

List of all source settings:

• warehouse → Warehouse under which merge task runs.

• metadata_table → SNP Glue^TM table that specifies which tables were replicated.

• stream_name → Name of the stream to be created on the metadata table.

• historical_tab_suffix → Suffix of the table the replicated table is going to be merged into.

• control_schedule_time → Defines a period how often 'START_CONTROL' task should run.

• control_schedule_type → Defines the time unit for the 'control_schedule_time'.

• merge_schedule_time → Defines a period of how often merge tasks should run.

• merge_schedule_type → Defines the time unit for the 'merge_schedule_time'.

• merge_status → Flag if a merge was initiated and is set by the ‘CORE.START_MERGE' procedure. Not present in 'DEFAULT’ settings.

• control_status → Flag if the 'START_CONTROL' task exists, is running, or is suspended. Not present in 'DEFAULT’ settings.

Application settings:

• app_name → Name of the application.

Source management

Register a new source. It creates a new schema if it does not already exists and set default source settings.

call core.register_source(<Source>)

Remove source drops source, its schema, if it exists.

call core.remove_source(<Source>);

Cleanup

Remove control drops 'START_CONTROL' task, thus preventing from starting merges on newly replicated tables.

call core.remove_control(<Source>);

Drop an SNP Glue^TM by specifying its source, type (view, stream, or task), and its name.

call core.drop_glue_object(<Source>, <Type>, <Object>);

By not specifying the object name, all objects of said type will be dropped in the specified source.

call core.drop_glue_object(<Source>, <Type>);

Drop glue tasks

call core.drop_glue_tasks(<Source>);

Drop glue views

call core.drop_glue_views(<Source>);

Drop glue streams

call core.drop_glue_streams(<So