def generates_asset_upload_triggers(): '''Generates AssetUpload triggers Those triggers update `etag` fields of the AssetUpload on update or insert. Returns: tuple tuple for all needed triggers ''' etag_func = """ -- update AssetUpload auto variable NEW.etag = public.gen_random_uuid(); RETURN NEW; """ return ( pgtrigger.Trigger(name="add_asset_upload_trigger", operation=pgtrigger.Insert, when=pgtrigger.Before, func=etag_func), pgtrigger.Trigger( name="update_asset_upload_trigger", operation=pgtrigger.Update, condition=pgtrigger.Condition('OLD.* IS DISTINCT FROM NEW.*'), when=pgtrigger.Before, func=etag_func), )
def generate_child_triggers(parent_name, child_name): '''Function that generates two triggers for parent child relation. The triggers update the `updated` and `etag` fields of the parent when a child gets inserted, updated or deleted. Returns: tuple Tuple of Trigger ''' child_update_func = """ -- update related {parent_name} UPDATE stac_api_{parent_name} SET updated = now(), etag = public.gen_random_uuid() WHERE id = {child_obj}.{parent_name}_id; RAISE INFO 'Parent table {parent_name}.id=% auto fields updated due to child {child_name}.id=% updates.', {child_obj}.{parent_name}_id, {child_obj}.id; RETURN {child_obj}; """ return (pgtrigger.Trigger(name=f"add_{parent_name}_child_trigger", operation=pgtrigger.Insert, when=pgtrigger.After, func=child_update_func.format( parent_name=parent_name, child_obj="NEW", child_name=child_name)), pgtrigger.Trigger( name=f"update_{parent_name}_child_trigger", operation=pgtrigger.Update, when=pgtrigger.After, condition=pgtrigger.Condition('OLD.* IS DISTINCT FROM NEW.*'), func=child_update_func.format(parent_name=parent_name, child_obj="NEW", child_name=child_name)), pgtrigger.Trigger(name=f"del_{parent_name}_child_trigger", operation=pgtrigger.Delete, when=pgtrigger.After, func=child_update_func.format( parent_name=parent_name, child_obj="OLD", child_name=child_name)))
def generates_collection_triggers(): '''Generates Collection triggers Those triggers update the `updated` and `etag` fields of the collections on update or insert. Returns: tuple tuple for all needed triggers ''' return ( pgtrigger.Trigger(name="add_collection_auto_variables_trigger", operation=pgtrigger.Insert, when=pgtrigger.Before, func=AUTO_VARIABLES_FUNC), pgtrigger.Trigger( name="update_collection_auto_variables_trigger", operation=pgtrigger.Update, when=pgtrigger.Before, condition=pgtrigger.Condition('OLD.* IS DISTINCT FROM NEW.*'), func=AUTO_VARIABLES_FUNC), )
def generates_asset_triggers(): '''Generates Asset triggers Those triggers update the `updated` and `etag` fields of the assets and their parents on update, insert or delete. It also update the collection summaries. Returns: tuple tuple for all needed triggers ''' class UpdateCollectionSummariesTrigger(pgtrigger.Trigger): when = pgtrigger.After func = ''' asset_instance = COALESCE(NEW, OLD); related_collection_id = ( SELECT collection_id FROM stac_api_item WHERE id = asset_instance.item_id ); -- Update related item auto variables UPDATE stac_api_item SET updated = now(), etag = gen_random_uuid() WHERE id = asset_instance.item_id; RAISE INFO 'item.id=% auto fields updated, due to asset.name=% updates.', asset_instance.item_id, asset_instance.name; -- Compute collection summaries SELECT item.collection_id, array_remove(array_agg(DISTINCT(asset.proj_epsg)), null) AS proj_epsg, array_remove(array_agg(DISTINCT(asset.geoadmin_variant)), null) AS geoadmin_variant, array_remove(array_agg(DISTINCT(asset.geoadmin_lang)), null) AS geoadmin_lang, array_remove(array_agg(DISTINCT(asset.eo_gsd)), null) AS eo_gsd INTO collection_summaries FROM stac_api_item AS item LEFT JOIN stac_api_asset AS asset ON (asset.item_id = item.id) WHERE item.collection_id = related_collection_id GROUP BY item.collection_id; -- Update related collection (auto variables + summaries) UPDATE stac_api_collection SET updated = now(), etag = gen_random_uuid(), summaries_proj_epsg = collection_summaries.proj_epsg, summaries_geoadmin_variant = collection_summaries.geoadmin_variant, summaries_geoadmin_lang = collection_summaries.geoadmin_lang, summaries_eo_gsd = collection_summaries.eo_gsd WHERE id = related_collection_id; RAISE INFO 'collection.id=% summaries updated, due to asset.name=% update.', related_collection_id, asset_instance.name; RETURN asset_instance; ''' def get_declare(self, model): return [ ('asset_instance', 'stac_api_asset%ROWTYPE'), ('related_collection_id', 'INT'), ('collection_summaries', 'RECORD'), ] return (UpdateCollectionSummariesTrigger( name='update_asset_collection_summaries_trigger', operation=pgtrigger.Update, condition=pgtrigger.Condition('OLD.* IS DISTINCT FROM NEW.*')), UpdateCollectionSummariesTrigger( name='add_del_asset_collection_summaries_trigger', operation=pgtrigger.Delete | pgtrigger.Insert, ), pgtrigger.Trigger(name="add_asset_auto_variables_trigger", operation=pgtrigger.Insert, when=pgtrigger.Before, func=AUTO_VARIABLES_FUNC), pgtrigger.Trigger( name="update_asset_auto_variables_trigger", operation=pgtrigger.Update, condition=pgtrigger.Condition('OLD.* IS DISTINCT FROM NEW.*'), when=pgtrigger.Before, func=AUTO_VARIABLES_FUNC))
def generates_item_triggers(): '''Generates Item triggers Those triggers update the `updated` and `etag` fields of the items and their parents on update, insert or delete. It also update the collection extent. Returns: tuple tuple for all needed triggers ''' class UpdateCollectionExtentTrigger(pgtrigger.Trigger): when = pgtrigger.After func = ''' item_instance = COALESCE(NEW, OLD); -- Compute collection extent SELECT item.collection_id, ST_SetSRID(ST_EXTENT(item.geometry),4326) as extent_geometry, MIN(LEAST(item.properties_datetime, item.properties_start_datetime)) as extent_start_datetime, MAX(GREATEST(item.properties_datetime, item.properties_end_datetime)) as extent_end_datetime INTO collection_extent FROM stac_api_item AS item WHERE item.collection_id = item_instance.collection_id GROUP BY item.collection_id; -- Update related collection (auto variables + extent) UPDATE stac_api_collection SET updated = now(), etag = gen_random_uuid(), extent_geometry = collection_extent.extent_geometry, extent_start_datetime = collection_extent.extent_start_datetime, extent_end_datetime = collection_extent.extent_end_datetime WHERE id = item_instance.collection_id; RAISE INFO 'collection.id=% extent updated, due to item.name=% updates.', item_instance.collection_id, item_instance.name; RETURN item_instance; ''' def get_declare(self, model): return [ ('item_instance', 'stac_api_item%ROWTYPE'), ('collection_extent', 'RECORD'), ] return (pgtrigger.Trigger(name="add_item_auto_variables_trigger", operation=pgtrigger.Insert, when=pgtrigger.Before, func=AUTO_VARIABLES_FUNC), pgtrigger.Trigger( name="update_item_auto_variables_trigger", operation=pgtrigger.Update, when=pgtrigger.Before, condition=pgtrigger.Condition('OLD.* IS DISTINCT FROM NEW.*'), func=AUTO_VARIABLES_FUNC), UpdateCollectionExtentTrigger( name='update_item_collection_extent_trigger', operation=pgtrigger.Update, condition=pgtrigger.Condition('OLD.* IS DISTINCT FROM NEW.*')), UpdateCollectionExtentTrigger( name='add_del_item_collection_extent_trigger', operation=pgtrigger.Delete | pgtrigger.Insert))
class LogEntry(models.Model): """Created when ToLogModel is updated""" level = models.CharField(max_length=16) old_field = models.CharField(max_length=16, null=True) new_field = models.CharField(max_length=16, null=True) @pgtrigger.register( pgtrigger.Trigger( name='update_of_statement_test', level=pgtrigger.Statement, operation=pgtrigger.UpdateOf('field'), when=pgtrigger.After, func=f''' INSERT INTO {LogEntry._meta.db_table}(level) VALUES ('STATEMENT'); RETURN NULL; ''', ), pgtrigger.Trigger( name='after_update_statement_test', level=pgtrigger.Statement, operation=pgtrigger.Update, when=pgtrigger.After, referencing=pgtrigger.Referencing(old='old_values', new='new_values'), func=f''' INSERT INTO {LogEntry._meta.db_table}(level, old_field, new_field) SELECT 'STATEMENT' AS level, old_values.field AS old_field,
import pgtrigger from django.db import models from django.utils.translation import gettext_lazy as _ @pgtrigger.register( pgtrigger.Trigger( name="uppercase_college_code", operation=pgtrigger.Insert | pgtrigger.Update, when=pgtrigger.Before, func="NEW.code=upper(NEW.code); RETURN NEW;", ), pgtrigger.Protect( name="protect_college_deletes", operation=pgtrigger.Delete, ), ) class College(models.Model): class Region(models.TextChoices): NORTH = "N", _("North") SOUTH = "S", _("South") EAST = "E", _("East") WEST = "W", _("West") code = models.CharField(max_length=10, primary_key=True) name = models.CharField(max_length=100) address = models.CharField(max_length=500) region = models.CharField( max_length=1, choices=Region.choices, default=Region.NORTH )
class SoftDelete(models.Model): """ This model cannot be deleted. When a user tries to delete it, the model will be "soft" deleted instead and have the ``is_active`` boolean set to ``False`` """ is_active = models.BooleanField(default=True) @pgtrigger.register( pgtrigger.Protect( operation=pgtrigger.Update, condition=pgtrigger.Q(old__version__df=pgtrigger.F('new__version'))), pgtrigger.Trigger( when=pgtrigger.Before, operation=pgtrigger.Update, func='NEW.version = NEW.version + 1; RETURN NEW;', condition=pgtrigger.Condition('OLD.* IS DISTINCT FROM NEW.*'))) class Versioned(models.Model): """ This model is versioned. The "version" field is incremented on every update, and users cannot directly update the "version" field. """ version = models.IntegerField(default=0) char_field = models.CharField(max_length=32) class OfficialInterfaceManager(models.Manager): @pgtrigger.ignore('tutorial.OfficialInterface:protect_inserts') def official_create(self): return self.create()