Changes between Version 7 and Version 8 of DmlScript

Timestamp:

05/20/26 10:27:54 (6 days ago)

Author:

231141

Comment:

--

DmlScript

@@ -898 +898 @@
 LIMIT 7000000;  
 
+-- -------------------------------------------------------------
+-- Notification  (~20 M rows)
+-- -------------------------------------------------------------
+INSERT INTO Notification
+(title, body, created_at, user_id, notification_type_id,
+ task_id, offer_id, payment_id)
+SELECT
+    CASE (nt_id % 20) + 1
+        WHEN 1  THEN 'New Task Created'
+        WHEN 2  THEN 'Task Accepted'
+        WHEN 3  THEN 'Task Started'
+        WHEN 4  THEN 'Task Completed'
+        WHEN 5  THEN 'Task Cancelled'
+        WHEN 6  THEN 'Offer Received'
+        WHEN 7  THEN 'Offer Accepted'
+        WHEN 8  THEN 'Offer Rejected'
+        WHEN 9  THEN 'Offer Expired'
+        WHEN 10 THEN 'Payment Pending'
+        WHEN 11 THEN 'Payment Successful'
+        WHEN 12 THEN 'Payment Failed'
+        WHEN 13 THEN 'Review Received'
+        WHEN 14 THEN 'Complaint Opened'
+        WHEN 15 THEN 'Complaint Resolved'
+        WHEN 16 THEN 'Complaint Dismissed'
+        WHEN 17 THEN 'Message Received'
+        WHEN 18 THEN 'Badge Earned'
+        WHEN 19 THEN 'Profile Viewed'
+        ELSE         'System Announcement'
+    END                                                                  AS title,
+    CASE (nt_id % 20) + 1
+        WHEN 1  THEN 'A new task has been created and is awaiting offers.'
+        WHEN 2  THEN 'Your task has been accepted by a worker.'
+        WHEN 3  THEN 'The worker has started working on your task.'
+        WHEN 4  THEN 'Your task has been marked as completed.'
+        WHEN 5  THEN 'The task has been cancelled.'
+        WHEN 6  THEN 'You have received a new offer on your task.'
+        WHEN 7  THEN 'Your offer has been accepted by the client.'
+        WHEN 8  THEN 'Your offer has been rejected.'
+        WHEN 9  THEN 'Your offer has expired without a response.'
+        WHEN 10 THEN 'Your payment is being processed.'
+        WHEN 11 THEN 'Your payment was completed successfully.'
+        WHEN 12 THEN 'Your payment attempt failed. Please try again.'
+        WHEN 13 THEN 'You have received a new review.'
+        WHEN 14 THEN 'A complaint has been filed regarding your task.'
+        WHEN 15 THEN 'Your complaint has been resolved.'
+        WHEN 16 THEN 'Your complaint has been dismissed.'
+        WHEN 17 THEN 'You have a new message.'
+        WHEN 18 THEN 'Congratulations! You earned a new badge.'
+        WHEN 19 THEN 'Someone viewed your profile.'
+        ELSE         'There is a new system announcement.'
+    END                                                                  AS body,
+    -- generated after user_created_at so the trigger is always satisfied
+    user_created_at + (random() * (NOW() - user_created_at))            AS created_at,
+    user_id,
+    (nt_id % 20) + 1                                                     AS notification_type_id,
+    CASE WHEN (nt_id % 20) + 1 IN (1,2,3,4,5,13,14,15,16,17)
+         THEN (floor(random() * (SELECT MAX(id) FROM Task)) + 1)::INT
+         ELSE NULL
+    END                                                                  AS task_id,
+    CASE WHEN (nt_id % 20) + 1 IN (6,7,8,9)
+         THEN (floor(random() * (SELECT MAX(id) FROM Offer)) + 1)::INT
+         ELSE NULL
+    END                                                                  AS offer_id,
+    CASE WHEN (nt_id % 20) + 1 IN (10,11,12)
+         THEN (floor(random() * (SELECT MAX(id) FROM Payment)) + 1)::INT
+         ELSE NULL
+    END                                                                  AS payment_id
+FROM (
+    SELECT
+        nt_id,
+        ua.id                                                            AS user_id,
+        ua.created_at                                                    AS user_created_at
+    FROM generate_series(0, 9999999) AS nt_id
+    CROSS JOIN LATERAL (
+        SELECT id, created_at
+        FROM UserAccount
+        WHERE id = (
+            floor(random() * ((SELECT MAX(id) FROM UserAccount) - (SELECT MIN(id) FROM UserAccount) + 1))
+            + (SELECT MIN(id) FROM UserAccount)
+        )::INT
+    ) ua
+) sub;
+
+
+
+
+select * from Notification;
+
+
+--Notification trigger--
+CREATE OR REPLACE FUNCTION check_notification_created_at()
+    RETURNS TRIGGER AS $$
+BEGIN
+    IF NEW.created_at <= (SELECT created_at FROM UserAccount WHERE id = NEW.user_id) THEN
+        RAISE EXCEPTION 'notification created_at must be after user created_at';
+    END IF;
+    RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER trg_notification_created_at
+    BEFORE INSERT OR UPDATE ON Notification
+    FOR EACH ROW
+EXECUTE FUNCTION check_notification_created_at();
+
+
+
 --++++++++++ compalaints +++++++++++