Data Modeling for https://github.com/silverstripe/silverstripe-versioned/issues/195

contains_unpublished_owned.sql

# 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_owned_at_version.sql

# 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

VersionSnapshot.csv

VersionSnapshot.sql

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;

VersionSnapshotItem.csv

VersionSnapshotItem.sql

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;