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()
