DataTongs Walkthrough - PostgreSQL (Enterprise)

For this example, we are using Pagila, which is a port of the Sakila example database for MySQL, featuring data related to a DVD rental store. In our case, it is named DVDRental and we are using the docker compose file from the demo repository.

Install DataTongs and configure the AppSettings.json file with the server, login details, and the first database to extract data from. Specify the directory for the new product and the names for the Product and Template. Renaming a template later is possible but cumbersome, as it affects both the template file and folder, as well as the product file. Below is an example of the edited AppSettings.json.

{
    "Source": {
        "Server": "localhost",
        "User": "TestUser",
        "Password": "**************************",
        "Database": "DVDRental"
    },
    "ScriptPath": "C:/Temp/postgres/DVDRental/Templates/DVDRental/Table Data",
    "ContentPath": "C:/Temp/postgres/DVDRental/Templates/DVDRental/Table Contents",
    "Tables": [ // { "Name": "", "KeyColumns": "", "SelectColumns": "", "Filter": "" }
        { "Name": "public.actor" },
        { "Name": "public.category" },
        { "Name": "public.city" },
        { "Name": "public.country" }
    ],
    "ShouldCast": {
        "OutputContentFiles": true,
        "OutputScripts": true,
        "TokenizeScripts": true,
        "DisableTriggers": false,
        "DisableRules": false,
        "UpdateDescendents": true,
        "MergeUpdate": true,
        "MergeDelete": false
    }
}                        

The Tables section is where you provide the list of tables to extract data from. The key columns that will be used to order the data and also match it for updating in the generated MERGE script. You can optionally set which columns are the key for matching the updates, which columns to include in the export, and/or a filter to scope the export.

The ShouldCast section is where you define what type of output(s) you want, whether json exports or scripts or a combination of both. If you export scripts and set TokenizeScripts to true the json data will be included in the script in the form of a token that would need to be defined in your product to point to the appropriate content file. There are also options for defining whether your MERGE scripts will also update and/or delete data. The MERGE will always include the section to insert missing rows. In the example product we exported the data to json files and used the built in data delivery code to manage the updates rather than scripts but if you needed to do any custom handling, you could generate your own script and use a token to allow separately dealing with the actual data without needing to touch the script again unless the structure or logic changed.

The tool will output scripts for the table(s) specified in the AppSettings.json to the folder you specified in OutputPath like this:

An example tokenized merge script would look like this:

DO $$
DECLARE
  v_json JSON = '{{{public.actor.tabledata}}}';
  nextval BIGINT;
BEGIN


MERGE INTO "public"."actor" AS "Target"
USING (
    WITH my_tables(arr) AS (VALUES(v_json::JSON))
    SELECT (elem ->> 'actor_id')::int4 AS "actor_id",
           (elem ->> 'first_name')::varchar(45) AS "first_name",
           (elem ->> 'last_name')::varchar(45) AS "last_name",
           (elem ->> 'last_update')::timestamp(6) AS "last_update"
      FROM my_tables, JSON_ARRAY_ELEMENTS(arr) AS elem
) AS "Source"
ON "Source"."actor_id" = "Target"."actor_id"

WHEN MATCHED AND (NOT ("Target"."first_name" = "Source"."first_name" OR ("Target"."first_name" IS NULL AND "Source"."first_name" IS NULL)) OR NOT ("Target"."last_name" = "Source"."last_name" OR ("Target"."last_name" IS NULL AND "Source"."last_name" IS NULL)) OR NOT ("Target"."last_update" = "Source"."last_update" OR ("Target"."last_update" IS NULL AND "Source"."last_update" IS NULL))) THEN
  UPDATE SET
        "first_name" = "Source"."first_name",
        "last_name" = "Source"."last_name",
        "last_update" = "Source"."last_update"

 WHEN NOT MATCHED BY TARGET THEN
   INSERT (
        "actor_id",
        "first_name",
        "last_name",
        "last_update"
   ) OVERRIDING USER VALUE
  VALUES (
        "Source"."actor_id",
        "Source"."first_name",
        "Source"."last_name",
        "Source"."last_update"
   )
 ;


SELECT SETVAL('actor_actor_id_seq', (SELECT MAX("actor_id") FROM "public"."actor")) INTO nextval;

END $$ LANGUAGE plpgsql;

You need only configure the content location and merge properties for each table with the data that you want to deliver and then make sure the files are located in the correct relative or absolute path at Quench time. Some companies put the data into their product repository, and some treat the data differently, generating the artifact separately in the release process. SchemaQuench only needs to know where to find them. You can do the same when using tokenized merge scripts. As long as the tokens define the correct location for the files the method of generating them and making them available is completely in your hands.