2XZcqW4L

1CREATE TABLE IF NOT EXISTS DFBRestaurantStatusLog (
2	DFBRestaurantStatusLogId BIGINT NOT NULL AUTO_INCREMENT,
3	WebRID int NOT NULL,
4	Operation nvarchar(1) NOT NULL,
5	UpdatedUTC datetime,
6	RestaurantStatusTypeID_Prior int,
7	RestaurantStatusTypeID_After int,
8	UpdatedBy_Prior varchar(50),
9	UpdatedBy_After varchar(50),
10	UpdatedUTC_Prior datetime,
11	UpdatedUTC_After datetime,
12	PRIMARY KEY (DFBRestaurantStatusLogId)
13);
14
15 CREATE TABLE IF NOT EXISTS DFBRestaurantStatusEventWatermark (
16	 Watermark BIGINT NOT NULL,
17	 UpdatedDateUtc DATETIME NOT NULL
18 )
19
20 CREATE TRIGGER tr_insert_DFBRestaurantStatus AFTER INSERT ON DFBRestaurantStatus
21	FOR EACH ROW
22	BEGIN
23	  INSERT INTO DFBRestaurantStatusLog (WebRID, Operation, UpdatedUTC, RestaurantStatusTypeID_Prior, RestaurantStatusTypeID_After, UpdatedBy_Prior, UpdatedBy_After, UpdatedUTC_Prior, UpdatedUTC_After)
24	  VALUES(NEW.WebRID, 'I', NOW(), NULL, NEW.RestaurantStatusTypeID, NULL, NEW.UpdatedBy, NULL, NEW.UpdatedUTC);
25	END;
26
27	CREATE TRIGGER tr_update_DFBRestaurantStatus AFTER UPDATE ON DFBRestaurantStatus
28	FOR EACH ROW
29	BEGIN
30		INSERT INTO DFBRestaurantStatusLog (WebRID, Operation, UpdatedUTC, RestaurantStatusTypeID_Prior, RestaurantStatusTypeID_After, UpdatedBy_Prior, UpdatedBy_After, UpdatedUTC_Prior, UpdatedUTC_After)
31		VALUES(NEW.WebRID, 'U', NOW(), OLD.RestaurantStatusTypeID, NEW.RestaurantStatusTypeID, OLD.UpdatedBy, NEW.UpdatedBy, OLD.UpdatedUTC, NEW.UpdatedUTC);
32	END;
33
34	CREATE TRIGGER tr_delete_DFBRestaurantStatus AFTER DELETE ON DFBRestaurantStatus
35	FOR EACH ROW
36	BEGIN
37		INSERT INTO DFBRestaurantStatusLog (WebRID, Operation, UpdatedUTC, RestaurantStatusTypeID_Prior, RestaurantStatusTypeID_After, UpdatedBy_Prior, UpdatedBy_After, UpdatedUTC_Prior, UpdatedUTC_After)
38		VALUES(OLD.WebRID, 'U', NOW(), OLD.RestaurantStatusTypeID, NULL, OLD.UpdatedBy, NULL, OLD.UpdatedUTC, NULL);
39	END;
40
41	CREATE PROCEDURE `RestaurantEventPublisher_CleanupDFBRestaurantStatusWatermark_V1` (
42		IN OlderThanHours int
43	)
44	BEGIN
45		DELETE FROM DFBRestaurantStatusEventWatermark
46		WHERE UpdatedDateUtc <= DATE_SUB(CURDATE(), INTERVAL OlderThanHours HOUR)
47	END
48
49	CREATE PROCEDURE `RestaurantEventPublisher_CleanupDFBRestaurantStatusWatermark_V1` (
50		IN OlderThanHours int
51	)
52	BEGIN
53		DELETE FROM DFBRestaurantStatusEventWatermark
54		WHERE UpdatedDateUtc <= DATE_SUB(CURDATE(), INTERVAL OlderThanHours HOUR)
55	END
56
57	CREATE PROCEDURE `RestaurantEventPublisher_GetDFBRestaurantStatusLogs_V1` (
58		IN BatchSize int,
59		IN HighWatermark BIGINT
60	)
61	BEGIN
62		SELECT TOP(BatchSize) * FROM DFBRestaurantStatusLog
63		WHERE DFBRestaurantStatusLogId > HighWatermark
64		AND UpdatedUTC < DATE_SUB(CURDATE(), INTERVAL 1 MINUTE)
65		ORDER BY DFBRestaurantStatusLogId ASC
66	END
67
68	CREATE PROCEDURE `RestaurantEventPublisher_GetDFBRestaurantStatusWatermark_V1` (
69	)
70	BEGIN
71		SELECT TOP(1)
72		IFNULL(Watermark, 1) AS HighWatermark
73		FROM DFBRestaurantStatusEventWatermark
74		ORDER BY WATERMARK DESC
75	END
76
77	CREATE PROCEDURE `RestaurantEventPublisher_UpdateDFBRestaurantStatusWatermark_V1` (
78		IN HighWatermark BIGINT
79	)
80	BEGIN
81		INSERT INTO DFBRestaurantStatusEventWatermark (Watermark, UpdatedDateUtc) VALUES (HighWatermark, NOW());
82	END