node-pg-rev-gen - README


This utility takes a JSON file (may be JSON5 syntax - https://json5.org/) and uses it to generate SQL for creating
and dropping all of the necessary tables, views, and procedures for the node-pg-rev system.

Usage of these scripts and tables does not require your app to be a node.js app. If you are using node, please look at node-pg-rev for helpful libraries to implement this system.

Generated SQL Objects

The following objects are generated:

- Shared:
- rev_jsonb_diff_val() - Function that calculates the difference between 2 JSONB objects
- rev_jobs_sequence - Sequence for job numbers
- rev_create_job() - Function to get a job number and the current server timestamp for reference
- Per Record Type:
- xxx_default_source_id() - Function returning the default source id for the type (if one was specified)
- xxx_raw - Table containing a single record representing the current state of the data in the source system
- xxx_raw_load() - Procedure to load a batch of records into xxx_raw
- xxx_raw_trim() - Procedure to trim records which are older than a given date (used after full loads to remove deleted records)
- xxx_rev - Table containing a history of all of the changes to the raw data
- xxx_rev_load() - Generates new revisions based on updated load data
- xxx_rev_trim() - Removes raw data from old notes (can be recovered via the delta fields)
- xxx_rev_view - View of _rev with all extracted columns added
- xxx_current_all_mview - An (optionally materialized) view of all current records (including deleted records) and all materialized extracted columns
- xxx_current_refresh() - A procedure to refresh the materialized view (created even if current is not materialized for consistency) including materialized extracted columns
- XXX_current_all - All current records with non-materialized extracted columns added
- XXX_current - Same as XXX_current_view but with deleted records filtered
- xxx_updated - A table to track external updates for each record - generally used to sink webhook notifications of updates but can also be used to force updates of specific records
- xxx_updated_load() - Procedure to load a batch of records into xxx_updated
- xxx_updated_trim() - Procedure to delete updates older than a given date

JSON Syntax

- createFileName - If specified, write a create SQL script to this file
- dropFileName - If specified, write a drop SQL script to this file
- dropTransientFileName - If specified, write a drop SQL script to this file which only drops transient objects (excludes raw, rev, updates)
- publicNamespace - If specified, prefix all public (non-type-related) objects with this prefix (namespace and/or table name prefix)
- viewers - String of users and roles to grant SELECT access to all types
- updaters - String of users and roles to grant SELECT and EXECUTE access to all types and procedures
- types[] - Array of type objects
- name - Name of the type - used as the ``xxx`` prefix for the table and procedure names
- storeSource - If true, include a source_id column in the tables
- extIdType - If set, include an ext_id column of this type in the tables
- defaultSourceId - If set, default the source id to this value (otherwise it becomes required in xxx_raw_load)
- namespace - If set, prefix all type specific objects with this prefix (namespace and/or table name prefix)
- extraUpdated[] - An array of extra type names to create updated fields for - possibly used if you have multiple webhooks with different meanings
- materializeView - If true, the xxx_current view will be materialized
- concurrentView - If true, then xxx_current_refresh() will refresh the view concurrently
- viewers - String of users and roles to grant SELECT access to this type
- updaters - String of users and roles to grant SELECT and EXECUTE access to this type
- extractedFields - A hash of information on fields to extract into xxx_current
- [name] - The column name in the DB
- doNotMaterialize - If true, the column is omitted from materialized views to save space
- definition - The SQL table definition for the column
- index - If true, index this column (only if materialized)
- unique - If true, make the index unique (only if materialized and index)
- comment - String comment to add to the SQL
- indexes[] - A hash of indexes to add to the xxx_current materialized view (only if materialized)
- [name] - Name of the index
- definition - SQL fefining the index
- unique - If true, make the index unique
- comment - String comment to add to the SQL
- extraCreate[] - Extra SQL to add to the create script
- extraRawTrim[] - Extra SQL to add to the xxx_raw_trim procedure
- extraGrant[] - Extra SQL to add to the grant portion of hte script
- extraDropPersistent[] - Extra SQL to add to the persistent drop scripts
- extraDropTransient[] - Extra SQL to add to the transient drop scripts
- extraRefresh[] - Extra SQL to add to the xxx_current_refresh script