versioned-records.sql

# Given such a DB structure (MySQL):
# 
# Table: module_cms_articles
#
#  id |    version_since    |    version_until    |    creation_date    | creator_id |         title         |      lead       |         content          | visible 
# ----+---------------------+---------------------+---------------------+------------+-----------------------+-----------------+--------------------------+---------
#   1 | 2010-05-12 19:57:05 | 2010-05-12 20:15:20 | 2010-05-12 19:57:05 |          1 | 1-st article          | Introduction... | First article.           |       0 
#   1 | 2010-05-12 20:15:20 | 2010-05-12 20:27:28 | 2010-05-12 19:57:05 |          1 | 1-st article          | Introduction... | First article. Extended. |       0 
#   1 | 2010-05-12 20:27:28 | 2037-12-31 23:59:59 | 2010-05-12 19:57:05 |          1 | 1-st article modified | Introduction... | First Article. Extended. |       0 

-- 1. Find the most recent version for article with id = 1:

SELECT a.* FROM `module_cms_articles` a LEFT JOIN `module_cms_articles` a_next ON ( a.id = a_next.id AND a_next.version_since > a.version_since ) WHERE a.id = 1 AND a_next.id IS NULL

#  id |    version_since    |    version_until    |    creation_date    | creator_id |         title         |      lead       |         content          | visible 
# ----+---------------------+---------------------+---------------------+------------+-----------------------+-----------------+--------------------------+---------
#   1 | 2010-05-12 20:27:28 | 2037-12-31 23:59:59 | 2010-05-12 19:57:05 |          1 | 1-st article modified | Introduction... | First Article. Extended. |       0 

-- 2. Find the most recent version for article with id = 1 from a given date range (e.g. for date range '2010-05-12 20:10:00' - '2010-05-12 20:20:00' version 2 should be returned):

-- 3. Find a version for article with id = 1 that existed on a given date (e.g. for date '2010-05-12 20:20:00' version 2 should be returned):

-- 4. Find the most recent version for article with id = 1 and simultaneously return version_since from the earliest version (thus the creation_date column becomes redundant):