224 lines
21 KiB
C#
224 lines
21 KiB
C#
namespace Tiku.Infrastructure.Persistence;
|
|
|
|
internal static class PostgreSqlSaasCatalogConstraintSql
|
|
{
|
|
public const string CreateOfferingVersionImmutabilityGuards = """
|
|
create or replace function tiku_guard_saas_offering_version_immutable()
|
|
returns trigger
|
|
language plpgsql
|
|
as $$
|
|
begin
|
|
if tg_op = 'DELETE' then
|
|
if old.status in ('published', 'retired') then
|
|
raise exception 'published or retired SaaS offering versions cannot be deleted'
|
|
using errcode = '23514',
|
|
constraint = 'ck_saas_offering_versions_published_immutable';
|
|
end if;
|
|
|
|
return old;
|
|
end if;
|
|
|
|
if old.status not in ('published', 'retired') then
|
|
return new;
|
|
end if;
|
|
|
|
if old.status = 'published'
|
|
and new.status = 'retired'
|
|
and new.retired_at is not null
|
|
and new.id is not distinct from old.id
|
|
and new.offering_id is not distinct from old.offering_id
|
|
and new.version is not distinct from old.version
|
|
and new.billing_cycle is not distinct from old.billing_cycle
|
|
and new.original_amount_cents is not distinct from old.original_amount_cents
|
|
and new.amount_cents is not distinct from old.amount_cents
|
|
and new.currency is not distinct from old.currency
|
|
and new.effective_at is not distinct from old.effective_at
|
|
and new.published_at is not distinct from old.published_at
|
|
and new.metadata is not distinct from old.metadata
|
|
and new.created_at is not distinct from old.created_at then
|
|
return new;
|
|
end if;
|
|
|
|
raise exception 'published or retired SaaS offering versions are immutable'
|
|
using errcode = '23514',
|
|
constraint = 'ck_saas_offering_versions_published_immutable';
|
|
end;
|
|
$$;
|
|
|
|
do $guard$
|
|
begin
|
|
if to_regclass('saas_offering_versions') is not null then
|
|
execute 'create trigger trg_saas_offering_versions_published_immutable
|
|
before update or delete on saas_offering_versions
|
|
for each row execute function tiku_guard_saas_offering_version_immutable()';
|
|
end if;
|
|
end
|
|
$guard$;
|
|
""";
|
|
|
|
public const string CreateOfferingVersionChildrenImmutabilityGuards = """
|
|
create or replace function tiku_guard_saas_offering_version_child_immutable()
|
|
returns trigger
|
|
language plpgsql
|
|
as $$
|
|
declare
|
|
version_status text;
|
|
begin
|
|
if tg_op = 'DELETE' then
|
|
select status into version_status
|
|
from saas_offering_versions
|
|
where id = old.offering_version_id;
|
|
|
|
if version_status is distinct from 'draft' then
|
|
raise exception 'published or retired SaaS offering version children are immutable'
|
|
using errcode = '23514',
|
|
constraint = 'ck_saas_offering_versions_published_immutable';
|
|
end if;
|
|
|
|
return old;
|
|
end if;
|
|
|
|
if tg_op = 'UPDATE' and old.offering_version_id <> new.offering_version_id then
|
|
select status into version_status
|
|
from saas_offering_versions
|
|
where id = old.offering_version_id;
|
|
|
|
if version_status is distinct from 'draft' then
|
|
raise exception 'published or retired SaaS offering version children are immutable'
|
|
using errcode = '23514',
|
|
constraint = 'ck_saas_offering_versions_published_immutable';
|
|
end if;
|
|
end if;
|
|
|
|
select status into version_status
|
|
from saas_offering_versions
|
|
where id = new.offering_version_id;
|
|
|
|
if version_status is distinct from 'draft' then
|
|
raise exception 'published or retired SaaS offering version children are immutable'
|
|
using errcode = '23514',
|
|
constraint = 'ck_saas_offering_versions_published_immutable';
|
|
end if;
|
|
|
|
return new;
|
|
end;
|
|
$$;
|
|
|
|
do $guard$
|
|
begin
|
|
if to_regclass('saas_offering_version_features') is not null then
|
|
execute 'create trigger trg_saas_offering_version_features_published_immutable
|
|
before insert or update or delete on saas_offering_version_features
|
|
for each row execute function tiku_guard_saas_offering_version_child_immutable()';
|
|
end if;
|
|
|
|
if to_regclass('saas_offering_version_limits') is not null then
|
|
execute 'create trigger trg_saas_offering_version_limits_published_immutable
|
|
before insert or update or delete on saas_offering_version_limits
|
|
for each row execute function tiku_guard_saas_offering_version_child_immutable()';
|
|
end if;
|
|
end
|
|
$guard$;
|
|
""";
|
|
|
|
public const string CreateSubscriptionOfferingTypeGuards = """
|
|
create or replace function tiku_guard_saas_subscription_offering_type()
|
|
returns trigger
|
|
language plpgsql
|
|
as $$
|
|
declare
|
|
offering_type text;
|
|
source_version_id uuid;
|
|
begin
|
|
if tg_table_name = 'tenant_saas_subscriptions' then
|
|
select o.type into offering_type
|
|
from saas_offering_versions v
|
|
join saas_offerings o on o.id = v.offering_id
|
|
where v.id = new.base_offering_version_id;
|
|
|
|
if offering_type is distinct from 'base_plan' then
|
|
raise exception 'subscription base offering version must belong to a base plan'
|
|
using errcode = '23514', constraint = 'ck_tenant_saas_subscription_base_plan';
|
|
end if;
|
|
|
|
if new.scheduled_base_offering_version_id is not null then
|
|
select o.type into offering_type
|
|
from saas_offering_versions v
|
|
join saas_offerings o on o.id = v.offering_id
|
|
where v.id = new.scheduled_base_offering_version_id;
|
|
if offering_type is distinct from 'base_plan' then
|
|
raise exception 'scheduled subscription offering version must belong to a base plan'
|
|
using errcode = '23514', constraint = 'ck_tenant_saas_subscription_base_plan';
|
|
end if;
|
|
end if;
|
|
return new;
|
|
end if;
|
|
|
|
select o.type into offering_type
|
|
from saas_offering_versions v
|
|
join saas_offerings o on o.id = v.offering_id
|
|
where v.id = new.offering_version_id;
|
|
if offering_type is distinct from new.item_type::text then
|
|
raise exception 'subscription item type must match its offering type'
|
|
using errcode = '23514', constraint = 'ck_tenant_saas_subscription_item_offering_type';
|
|
end if;
|
|
|
|
if new.source_order_item_id is not null then
|
|
select offering_version_id into source_version_id
|
|
from platform_billing_order_items
|
|
where tenant_id = new.tenant_id and id = new.source_order_item_id;
|
|
if source_version_id is distinct from new.offering_version_id then
|
|
raise exception 'subscription item must match its order item snapshot'
|
|
using errcode = '23514', constraint = 'ck_tenant_saas_subscription_item_order_snapshot';
|
|
end if;
|
|
end if;
|
|
return new;
|
|
end;
|
|
$$;
|
|
|
|
do $guard$
|
|
begin
|
|
if to_regclass('tenant_saas_subscriptions') is not null then
|
|
execute 'create trigger trg_tenant_saas_subscriptions_base_plan
|
|
before insert or update on tenant_saas_subscriptions
|
|
for each row execute function tiku_guard_saas_subscription_offering_type()';
|
|
end if;
|
|
if to_regclass('tenant_saas_subscription_items') is not null then
|
|
execute 'create trigger trg_tenant_saas_subscription_items_offering_type
|
|
before insert or update on tenant_saas_subscription_items
|
|
for each row execute function tiku_guard_saas_subscription_offering_type()';
|
|
end if;
|
|
end
|
|
$guard$;
|
|
""";
|
|
|
|
public const string DropOfferingVersionImmutabilityGuards = """
|
|
do $guard$
|
|
begin
|
|
if to_regclass('saas_offering_version_features') is not null then
|
|
execute 'drop trigger if exists trg_saas_offering_version_features_published_immutable on saas_offering_version_features';
|
|
end if;
|
|
|
|
if to_regclass('saas_offering_version_limits') is not null then
|
|
execute 'drop trigger if exists trg_saas_offering_version_limits_published_immutable on saas_offering_version_limits';
|
|
end if;
|
|
|
|
if to_regclass('saas_offering_versions') is not null then
|
|
execute 'drop trigger if exists trg_saas_offering_versions_published_immutable on saas_offering_versions';
|
|
end if;
|
|
|
|
if to_regclass('tenant_saas_subscription_items') is not null then
|
|
execute 'drop trigger if exists trg_tenant_saas_subscription_items_offering_type on tenant_saas_subscription_items';
|
|
end if;
|
|
|
|
if to_regclass('tenant_saas_subscriptions') is not null then
|
|
execute 'drop trigger if exists trg_tenant_saas_subscriptions_base_plan on tenant_saas_subscriptions';
|
|
end if;
|
|
end
|
|
$guard$;
|
|
|
|
drop function if exists tiku_guard_saas_offering_version_child_immutable();
|
|
drop function if exists tiku_guard_saas_offering_version_immutable();
|
|
drop function if exists tiku_guard_saas_subscription_offering_type();
|
|
""";
|
|
} |