Fixture, add composite primary key

Signed-off-by: Danny Kopping <danny@coder.com>
coder · dannykopping · Aug 5, 2024 · Jul 25, 2024 · Jul 23, 2024 · Jul 23, 2024
commit 17d27bed94b3dc9b83abfd601954ab222f83f6d2
diff --git a/coderd/database/dump.sql b/coderd/database/dump.sql
diff --git a/coderd/database/migrations/000234_notification_preferences.up.sql b/coderd/database/migrations/000234_notification_preferences.up.sql
@@ -1,54 +1,55 @@
 CREATE TABLE notification_preferences
 (
-    user_id                  uuid REFERENCES users ON DELETE CASCADE                  NOT NULL,
-    notification_template_id uuid REFERENCES notification_templates ON DELETE CASCADE NOT NULL,
-    disabled                 bool                                                     NOT NULL DEFAULT FALSE,
-    created_at               TIMESTAMP WITH TIME ZONE                                 NOT NULL DEFAULT CURRENT_TIMESTAMP,
-    updated_at               TIMESTAMP WITH TIME ZONE                                 NOT NULL DEFAULT CURRENT_TIMESTAMP
+	user_id                  uuid REFERENCES users ON DELETE CASCADE                  NOT NULL,
+	notification_template_id uuid REFERENCES notification_templates ON DELETE CASCADE NOT NULL,
+	disabled                 bool                                                     NOT NULL DEFAULT FALSE,
+	created_at               TIMESTAMP WITH TIME ZONE                                 NOT NULL DEFAULT CURRENT_TIMESTAMP,
+	updated_at               TIMESTAMP WITH TIME ZONE                                 NOT NULL DEFAULT CURRENT_TIMESTAMP,
+	PRIMARY KEY (user_id, notification_template_id)
 );
 
 -- Ensure we cannot insert multiple entries for the same user/template combination.
 ALTER TABLE notification_preferences
-    ADD CONSTRAINT unique_user_notification_template UNIQUE (user_id, notification_template_id);
+	ADD CONSTRAINT unique_user_notification_template UNIQUE (user_id, notification_template_id);
 
 -- Add a new type (to be expanded upon later) which specifies the kind of notification template.
 CREATE TYPE notification_template_kind AS ENUM (
-    'system'
-    );
+	'system'
+	);
 
 ALTER TABLE notification_templates
-    -- Allow per-template notification method (enterprise only).
-    ADD COLUMN method notification_method,
-    -- Update all existing notification templates to be system templates.
-    ADD COLUMN kind   notification_template_kind DEFAULT 'system'::notification_template_kind NOT NULL;
+	-- Allow per-template notification method (enterprise only).
+	ADD COLUMN method notification_method,
+	-- Update all existing notification templates to be system templates.
+	ADD COLUMN kind   notification_template_kind DEFAULT 'system'::notification_template_kind NOT NULL;
 COMMENT ON COLUMN notification_templates.method IS 'NULL defers to the deployment-level method';
 
 -- No equivalent in down migration because ENUM values cannot be deleted.
 ALTER TYPE notification_message_status ADD VALUE IF NOT EXISTS 'inhibited';
 
 -- Function to prevent enqueuing notifications unnecessarily.
 CREATE OR REPLACE FUNCTION inhibit_enqueue_if_disabled()
-    RETURNS TRIGGER AS
+	RETURNS TRIGGER AS
 $$
 BEGIN
-    -- Fail the insertion if the user has disabled this notification.
-    IF EXISTS (SELECT 1
-               FROM notification_preferences
-               WHERE disabled = TRUE
-                 AND user_id = NEW.user_id
-                 AND notification_template_id = NEW.notification_template_id) THEN
-        RAISE EXCEPTION 'cannot enqueue message: user has disabled this notification';
-    END IF;
-
-    RETURN NEW;
+	-- Fail the insertion if the user has disabled this notification.
+	IF EXISTS (SELECT 1
+			   FROM notification_preferences
+			   WHERE disabled = TRUE
+				 AND user_id = NEW.user_id
+				 AND notification_template_id = NEW.notification_template_id) THEN
+		RAISE EXCEPTION 'cannot enqueue message: user has disabled this notification';
+	END IF;
+
+	RETURN NEW;
 END;
 $$ LANGUAGE plpgsql;
 
 -- Trigger to execute above function on insertion.
 CREATE TRIGGER inhibit_enqueue_if_disabled
-    BEFORE INSERT
-    ON notification_messages
-    FOR EACH ROW
+	BEFORE INSERT
+	ON notification_messages
+	FOR EACH ROW
 EXECUTE FUNCTION inhibit_enqueue_if_disabled();
 
 -- Allow modifications to notification templates to be audited.

diff --git a/coderd/database/migrations/testdata/fixtures/000234_notifications.preferences.sql b/coderd/database/migrations/testdata/fixtures/000234_notifications.preferences.sql
diff --git a/coderd/database/migrations/testdata/fixtures/000234_notifications_preferences.up.sql b/coderd/database/migrations/testdata/fixtures/000234_notifications_preferences.up.sql
@@ -0,0 +1,9 @@
+INSERT INTO users(id, email, username, hashed_password, created_at, updated_at, status, rbac_roles, deleted)
+VALUES ('fc1511ef-4fcf-4a3b-98a1-8df64160e35a', 'githubuser@coder.com', 'githubuser', '\x',
+		'2022-11-02 13:05:21.445455+02', '2022-11-02 13:05:21.445455+02', 'active', '{}', false) ON CONFLICT DO NOTHING;
+
+INSERT INTO notification_templates (id, name, title_template, body_template, "group")
+VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', 'A', 'title', 'body', 'Group 1') ON CONFLICT DO NOTHING;
+
+INSERT INTO notification_preferences (user_id, notification_template_id, disabled, created_at, updated_at)
+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');
diff --git a/coderd/database/unique_constraint.go b/coderd/database/unique_constraint.go