Skip to content

Commit 95960ca

Browse files
dannykoppingdannykopping
committed
Fixture, add composite primary key
Signed-off-by: Danny Kopping <danny@coder.com>
1 parent c30f562 commit 95960ca

File tree

5 files changed

+50
-42
lines changed

5 files changed

+50
-42
lines changed

‎coderd/database/dump.sql

Lines changed: 13 additions & 10 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/migrations/000234_notification_preferences.up.sql

Lines changed: 27 additions & 26 deletions
Original file line numberDiff line numberDiff line change
@@ -1,54 +1,55 @@
11
CREATE TABLE notification_preferences
22
(
3-
user_id uuid REFERENCES users ON DELETE CASCADE NOT NULL,
4-
notification_template_id uuid REFERENCES notification_templates ON DELETE CASCADE NOT NULL,
5-
disabled bool NOT NULL DEFAULT FALSE,
6-
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
7-
updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
3+
user_id uuid REFERENCES users ON DELETE CASCADE NOT NULL,
4+
notification_template_id uuid REFERENCES notification_templates ON DELETE CASCADE NOT NULL,
5+
disabled bool NOT NULL DEFAULT FALSE,
6+
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
7+
updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
8+
PRIMARY KEY (user_id, notification_template_id)
89
);
910

1011
-- Ensure we cannot insert multiple entries for the same user/template combination.
1112
ALTER TABLE notification_preferences
12-
ADD CONSTRAINT unique_user_notification_template UNIQUE (user_id, notification_template_id);
13+
ADD CONSTRAINT unique_user_notification_template UNIQUE (user_id, notification_template_id);
1314

1415
-- Add a new type (to be expanded upon later) which specifies the kind of notification template.
1516
CREATE TYPE notification_template_kind AS ENUM (
16-
'system'
17-
);
17+
'system'
18+
);
1819

1920
ALTER TABLE notification_templates
20-
-- Allow per-template notification method (enterprise only).
21-
ADD COLUMN method notification_method,
22-
-- Update all existing notification templates to be system templates.
23-
ADD COLUMN kind notification_template_kind DEFAULT 'system'::notification_template_kind NOT NULL;
21+
-- Allow per-template notification method (enterprise only).
22+
ADD COLUMN method notification_method,
23+
-- Update all existing notification templates to be system templates.
24+
ADD COLUMN kind notification_template_kind DEFAULT 'system'::notification_template_kind NOT NULL;
2425
COMMENT ON COLUMN notification_templates.method IS 'NULL defers to the deployment-level method';
2526

2627
-- No equivalent in down migration because ENUM values cannot be deleted.
2728
ALTER TYPE notification_message_status ADD VALUE IF NOT EXISTS 'inhibited';
2829

2930
-- Function to prevent enqueuing notifications unnecessarily.
3031
CREATE OR REPLACE FUNCTION inhibit_enqueue_if_disabled()
31-
RETURNS TRIGGER AS
32+
RETURNS TRIGGER AS
3233
$$
3334
BEGIN
34-
-- Fail the insertion if the user has disabled this notification.
35-
IF EXISTS (SELECT 1
36-
FROM notification_preferences
37-
WHERE disabled = TRUE
38-
AND user_id = NEW.user_id
39-
AND notification_template_id = NEW.notification_template_id) THEN
40-
RAISE EXCEPTION 'cannot enqueue message: user has disabled this notification';
41-
END IF;
42-
43-
RETURN NEW;
35+
-- Fail the insertion if the user has disabled this notification.
36+
IF EXISTS (SELECT 1
37+
FROM notification_preferences
38+
WHERE disabled = TRUE
39+
AND user_id = NEW.user_id
40+
AND notification_template_id = NEW.notification_template_id) THEN
41+
RAISE EXCEPTION 'cannot enqueue message: user has disabled this notification';
42+
END IF;
43+
44+
RETURN NEW;
4445
END;
4546
$$ LANGUAGE plpgsql;
4647

4748
-- Trigger to execute above function on insertion.
4849
CREATE TRIGGER inhibit_enqueue_if_disabled
49-
BEFORE INSERT
50-
ON notification_messages
51-
FOR EACH ROW
50+
BEFORE INSERT
51+
ON notification_messages
52+
FOR EACH ROW
5253
EXECUTE FUNCTION inhibit_enqueue_if_disabled();
5354

5455
-- Allow modifications to notification templates to be audited.

‎coderd/database/migrations/testdata/fixtures/000234_notifications.preferences.sql

Lines changed: 0 additions & 6 deletions
This file was deleted.

‎coderd/database/migrations/testdata/fixtures/000234_notifications_preferences.up.sql

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,9 @@
1+
INSERT INTO users(id, email, username, hashed_password, created_at, updated_at, status, rbac_roles, deleted)
2+
VALUES ('fc1511ef-4fcf-4a3b-98a1-8df64160e35a', 'githubuser@coder.com', 'githubuser', '\x',
3+
'2022-11-02 13:05:21.445455+02', '2022-11-02 13:05:21.445455+02', 'active', '{}', false) ON CONFLICT DO NOTHING;
4+
5+
INSERT INTO notification_templates (id, name, title_template, body_template, "group")
6+
VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', 'A', 'title', 'body', 'Group 1') ON CONFLICT DO NOTHING;
7+
8+
INSERT INTO notification_preferences (user_id, notification_template_id, disabled, created_at, updated_at)
9+
VALUES ('a0061a8e-7db7-4585-838c-3116a003dd21', 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', FALSE, '2024-07-15 10:30:00+00', '2024-07-15 10:30:00+00');

‎coderd/database/unique_constraint.go

Lines changed: 1 addition & 0 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

0 commit comments

Comments
 (0)