Skip to content

Instantly share code, notes, and snippets.

@chillu
Last active January 11, 2019 03:22
Show Gist options
  • Save chillu/f98f75fc98d461dfe23574f5e6686198 to your computer and use it in GitHub Desktop.
Save chillu/f98f75fc98d461dfe23574f5e6686198 to your computer and use it in GitHub Desktop.
# Does owner have any unpublished owned objects since it was last published?
# This case demonstrates how items can be deleted from live and aren't included here
SET @Owner = 'A1';
# Case 1: Version 10 has two unpublished changes: C1 and C3
#SET @Version = 10;
# Case 2: Version 11 has no changes (C1 and C3 have been published)
SET @Version = 11;
# Step 3: Filter to only draft items
SELECT * FROM VersionSnapshotItem
WHERE
ID IN (
# Step 2: Only get latest version entry for each item
SELECT MAX(VersionSnapshotItem.ID) FROM VersionSnapshotItem
LEFT JOIN VersionSnapshot ON VersionSnapshot.ID = VersionSnapshotItem.VersionSnapshotID
WHERE
VersionSnapshotItem.VersionSnapshotID IN (
# Step 1: Get all snapshots where this version of the owner has been involved
SELECT VersionSnapshot.ID
FROM VersionSnapshot
LEFT JOIN VersionSnapshotItem ON VersionSnapshot.ID = VersionSnapshotItem.VersionSnapshotID
WHERE
Object = @Owner
AND Version = @Version
ORDER BY Created DESC
)
GROUP BY Object
ORDER BY Created DESC
)
AND WasDeleted = 0
AND WasPublished = 0;
# Show all owned objects at the time the owner was published.
# Incl. draft and live, but excl. deleted.
# Assumes that lower cutoff is A1v10, which is published,
# and would've cascaded publication to all owned objects.
# This avoids selecting large amounts of versions before only getting the latest one
SET @Object := 'A1';
SET @Version := 12;
# Step 1: Get the very first snapshot where this owner was involved
SET @LowerSnapshotID := (SELECT VersionSnapshot.ID
FROM VersionSnapshot
LEFT JOIN VersionSnapshotItem ON VersionSnapshot.ID = VersionSnapshotItem.VersionSnapshotID
WHERE
Object = @Object
AND WasPublished = 1
ORDER BY Created ASC
LIMIT 1);
# Step 2: Get the first snapshot where the owner has been published
# Anything after that would've been modifications to that state
SET @UpperSnapshotID := (SELECT VersionSnapshot.ID
FROM VersionSnapshot
LEFT JOIN VersionSnapshotItem ON VersionSnapshot.ID = VersionSnapshotItem.VersionSnapshotID
WHERE
Object = @Object
AND Version = @Version
AND WasPublished = 1
ORDER BY Created ASC
LIMIT 1);
# Step 3: Select full items
SELECT * FROM VersionSnapshotItem
WHERE
ID IN (
# Step 2: Only get latest entry for each item
SELECT MAX(VersionSnapshotItem.ID) FROM VersionSnapshotItem
LEFT JOIN VersionSnapshot ON VersionSnapshot.ID = VersionSnapshotItem.VersionSnapshotID
WHERE VersionSnapshotItem.VersionSnapshotID BETWEEN @LowerSnapshotID AND @UpperSnapshotID
GROUP BY Object
ORDER BY Created ASC
)
AND WasDeleted = 0
ID Created Comment
1 2019-01-01 12:30:00 Modify A1>B1>C1
2 2019-01-01 12:31:00 Modify A1>B2>C2
3 2019-01-01 12:31:01 Publish A1>B2>C2
4 2019-01-01 12:32:00 Add A1>B2>C3
5 2019-01-01 12:33:00 Publish A1
6 2019-01-01 12:34:00 Delete A1>B2>C3
7 2019-01-01 12:35:00 Publish A1
CREATE TABLE `VersionSnapshot` (
`ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
`Created` datetime DEFAULT NULL,
`Comment` text,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
ID VersionSnapshotID Object Version WasPublished WasDeleted
25 1 C1 31 0 0
26 1 B1 20 1 0
27 1 A1 10 1 0
28 2 C2 51 0 0
29 2 B2 20 1 0
30 2 A1 10 1 0
31 3 C2 52 1 0
32 3 B2 20 1 0
33 3 A1 10 1 0
34 4 C3 60 0 0
35 4 B2 20 1 0
36 4 A1 10 1 0
37 5 A1 11 1 0
38 5 C1 32 1 0
39 5 C3 61 1 0
40 6 C3 62 0 1
41 6 B2 20 1 0
42 6 A1 11 1 0
43 7 A1 12 1 0
CREATE TABLE `VersionSnapshotItem` (
`ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
`VersionSnapshotID` int(11) DEFAULT NULL,
`Object` text,
`Version` int(11) DEFAULT NULL,
`WasPublished` int(11) DEFAULT NULL,
`WasDeleted` int(11) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=utf8;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment