31 lines
1.6 KiB
PL/PgSQL
31 lines
1.6 KiB
PL/PgSQL
ALTER TABLE "SmsMessageRecord" ADD COLUMN "drainageGate" JSONB;
|
|
ALTER TABLE "SmsSubmitRecord" ADD COLUMN "drainageGate" JSONB;
|
|
ALTER TABLE "SmsMessageRecord" ADD COLUMN "drainageReceiptPending" BOOLEAN NOT NULL DEFAULT false;
|
|
CREATE INDEX "SmsMessageRecord_drainage_receipt_pending" ON "SmsMessageRecord" ("updatedAt") WHERE "drainageReceiptPending" = true;
|
|
CREATE TABLE "SmsDrainageDecision" (
|
|
"id" TEXT PRIMARY KEY,
|
|
"messageRecordId" TEXT NOT NULL,
|
|
"decidedAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"snapshot" JSONB NOT NULL
|
|
);
|
|
CREATE INDEX "SmsDrainageDecision_messageRecordId_decidedAt_idx" ON "SmsDrainageDecision" ("messageRecordId", "decidedAt");
|
|
|
|
CREATE OR REPLACE FUNCTION drainage_authorization_lock() RETURNS trigger LANGUAGE plpgsql AS $$
|
|
BEGIN
|
|
IF TG_OP = 'UPDATE' AND OLD."signatureId" IS DISTINCT FROM NEW."signatureId" THEN
|
|
PERFORM pg_advisory_xact_lock(hashtextextended(value, 910))
|
|
FROM unnest(ARRAY[OLD."signatureId", NEW."signatureId"]) AS ids(value) ORDER BY value;
|
|
RETURN NEW;
|
|
END IF;
|
|
IF TG_OP = 'DELETE' THEN
|
|
PERFORM pg_advisory_xact_lock(hashtextextended(OLD."signatureId", 910));
|
|
RETURN OLD;
|
|
END IF;
|
|
PERFORM pg_advisory_xact_lock(hashtextextended(NEW."signatureId", 910));
|
|
RETURN NEW;
|
|
END $$;
|
|
CREATE TRIGGER drainage_material_authorization_lock BEFORE INSERT OR UPDATE OR DELETE ON "SmsDrainageInfo"
|
|
FOR EACH ROW EXECUTE FUNCTION drainage_authorization_lock();
|
|
CREATE TRIGGER drainage_report_authorization_lock BEFORE INSERT OR UPDATE OR DELETE ON "ChannelSignatureReportTask"
|
|
FOR EACH ROW EXECUTE FUNCTION drainage_authorization_lock();
|