Database Triggers

Here lies information about the Database Triggers. Here be dragons and other things that will gobble you up.

The triggers (in no particular order):

First acting triggers

defined-in:cnxdb/archive-sql/schema/triggers.sql

These act_##_... prefixed triggers are the first triggers to run on insert to the modules table. They are named with this prefix to ensure they are the first to run and in a specific numbered order.

Set the default module UUID

name:act_10_module_uuid_default

This trigger runs:

cnxarchive.database.assign_document_controls_default_trigger(plpy, td)[source]

Trigger to fill in document_controls when legacy publishes.

A compatibilty trigger to fill in uuid and licenseid columns of the document_controls table that are not populated when inserting publications from legacy.

This uuid default is not on modules.uuid column itself, because the value needs to be loosely associated with the document_controls entry to prevent uuid collisions and bridge the pending publications gap.

Update the module ACLs

name:act_20_module_acl_upsert

This trigger runs:

cnxarchive.database.upsert_document_acl_trigger(plpy, td)[source]

Trigger for filling in acls when legacy publishes.

A compatibility trigger to upsert authorization control entries (ACEs) for legacy publications.

Upsert users during Legacy publications

name:act_80_legacy_module_user_upsert

This trigger runs:

cnxarchive.database.upsert_users_from_legacy_publication_trigger(plpy, td)[source]

A compatibility trigger to upsert users from legacy persons table.

User shadowing

Upsert users for optional roles

defined-in:cnxdb/archive-sql/schema/triggers.sql
name:optional_roles_user_insert

This trigger runs:

cnxarchive.database.insert_users_for_optional_roles_trigger(plpy, td)[source]

Trigger to update users from optional roles entries.

A compatibility trigger to insert users from moduleoptionalroles records. This is primarily for legacy compatibility, but it is not possible to tell whether the entry came from legacy or cnx-publishing. Therefore, we only insert into users.

Copy records from persons to users table

defined-in:cnxdb/archive-sql/schema/legacy/triggers.sql
name:update_users_from_legacy

This trigger ensures that insertions and updates to the persons table are copied (or shadowed) over to the users table.

Set defaults

defined-in:cnxdb/archive-sql/schema/triggers.sql

Set the default Module ID

defined-in:cnxdb/archive-sql/schema/triggers.sql
name:module_moduleid_default

This trigger runs:

cnxarchive.database.assign_moduleid_default_trigger(plpy, td)[source]

Trigger to fill in legacy moduleid when publishing.

This correctly assigns moduleid value to cnx-publishing publications. This does NOT include matching the moduleid to previous revision by way of uuid.

This correctly updates the sequence values when a legacy publication specifies the moduleid value. This is because legacy does not know about nor use the sequence values when setting legacy moduleid.

Set the default Module version

defined-in:cnxdb/archive-sql/schema/triggers.sql
name:module_version_default

This trigger runs:

cnxarchive.database.assign_version_default_trigger(plpy, td)[source]

Trigger to fill in legacy data fields.

A compatibilty trigger to fill in legacy data fields that are not populated when inserting publications from cnx-publishing.

If this is not a legacy publication the version will be set based on the major_version value.

Set the default module state for legacy publications

defined-in:cnxdb/archive-sql/schema/legacy/triggers.sql
name:update_default_modules_stateid

Sets the default state to post-publication (stateid = 5) for insertions into the modules table that is classified as a Collection (i.e. portal_type = 'Collection').

Set values for computed fields

defined-in:cnxdb/archive-sql/schema/triggers.sql

Computer the MD5 hash

name:update_file_md5

Computes and sets the MD5 hash on insert or update to the files table.

Computer the SHA1 hash

name:update_files_sha1

Computes and sets the SHA1 hash on insert or update to the files table.

Republish Collections sharing a published Module

defined-in:cnxdb/archive-sql/schema/triggers.sql
name:module_published

This trigger runs:

cnxarchive.database.republish_module_trigger(plpy, td)[source]

Trigger called from postgres database when republishing a module.

When a module is republished, the versions of the collections that it is part of will need to be updated (a minor update).

e.g. there is a collection c1 v2.1, which contains module m1 v3

m1 is updated, we have a new row in the modules table with m1 v4

this trigger will create increment the minor version of c1, so we’ll have c1 v2.2

we need to create a collection tree for c1 v2.2 which is exactly the same as c1 v2.1, but with m1 v4 instead of m1 v3, and c1 v2.2 instead of c1 v2.2

Transformation triggers

defined-in:cnxdb/archive-sql/schema/triggers.sql

Transform a Collection’s abstract to HTML

name:collection_html_abstract_trigger

This triggers run on Collection insert or update when a module does not have an HTML abstract. The results of this trigger are to transform the cnxml or plain text abstract to HTML.

Transform a Module’s abstract to HTML

name:module_html_abstract_trigger

This triggers run on Module insert or update when a module does not have an HTML abstract. The results of this trigger are to transform the cnxml or plain text abstract to HTML.

Transform a Module’s document to or from CNXML to HTML

name:module_file_added
cnxarchive.database.add_module_file(plpy, td)[source]

Database trigger for adding a module file.

When a legacy index.cnxml is added, this trigger transforms it into html and stores it as index.cnxml.html. When a cnx-publishing index.cnxml.html is added, this trigger checks if index.html.cnxml exists before transforming it into cnxml and stores it as index.html.cnxml.

Note, we do not use index.html over index.cnxml.html, because legacy allows users to name files index.html.

Update latest version

defined-in:cnxdb/archive-sql/schema/triggers.sql
name:update_latest_version

Copies the record from modules to latest_modules on insert or update when the highest version of a module has successfully baked. A module is considered successfully baked when its state has transitioned to current (stateid = 1) or fallback (stateid = 8).

Delete latest version

defined-in:cnxdb/archive-sql/schema/triggers.sql
name:delete_from_latest_version

Copies the previous latest record from modules to latest_modules on deletion of the existing latest_modules record with the same ID.

Note

As of this writing, this trigger is not baking aware. (bug!)

Post Publication Trigger

defined-in:cnxdb/archive-sql/schema/triggers.sql
name:post_publication_trigger

Notifies the post_publication channel on insert or update to the modules table when the module’s state has been set to post-publication (stateid = 5).

Hint

The post_publication channel is observed by cnx-publishing’s channel processing script, which listens for events and places them into RabbitMQ.

Recipes Triggers

defined-in:cnxdb/archive-sql/schema/triggers.sql

Rebake on recipe insertion

name:ruleset_trigger

When a recipe is manually inserted (or injected) into the database this trigger “rebakes” the content. This trigger is active on the module_files table when a file named ruleset.css (aka recipe) is inserted or updated, which in turn executes the procedure to “rebake” the associated content.

Set the default recipe

name:update_default_recipes

When a new recipe is inserted (or old one updated) this trigger ensures the default_print_style_recipes table updated to with the latest revised recipe. This also ensures that a recipe is not overwritten if it is currently in use by one or more books (aka Collections).

Prevent deletion of used recipes

name:delete_from_default_recipes

Prevents deletion of recipes in the print_style_recipes table if the recipe is in use (therefore is a recipe that has been used in baking book). If the recipe being deleted is in default_print_style_recipes it will be replaced with the next newest available recipe.

Set the default canonical Collection for a Module

defined-in:cnxdb/archive-sql/schema/triggers.sql
name:set_default_canonical_trigger

This trigger finds the first Collection containing the Module and sets it as the canonical value.