Re: [sqlite] Data structure for versioned data

[Kees Nuyt]

Hi Sam,

On Wed, 20 Jun 2007 15:33:23 -0400, you wrote:

>Not specific to SQLite, but we're working on an app that needs to keep
>versioned data (i.e., the current values plus all previous values).  The
>versioning is integral to the app so it's more than just an audit trail or
>history.
>
>Can anyone share experiences with the database structure for this type of
>requirement or point me to helpful resources?

Joe Celko has a good chapter on temporal data
in his book "SQL for smarties" [1].

Some time ago I found a bash script from Mike Chirico
which maintains a notes database:
http://souptonuts.sourceforge.net/code/n.html
and changed it to my liking as a 
demonstration of temporal data [2].

Editing it for publication here may have introduced some
mistakes, testing is left to you ;)

References and quotes:
[1]
Google for ISDN-13: 978-0-12-369379-2

[2]
-- init database
PRAGMA page_size=4096;
PRAGMA cache_size=100;
PRAGMA default_cache_size=100;

-- define schema
BEGIN TRANSACTION;

 DROP TABLE IF EXISTS notes;
 CREATE TABLE notes (
        nkey      INTEGER PRIMARY KEY, -- names ROWID
        -- category left out in this demo
        msg       TEXT
 );

-- '9999-12-31 23:59:59' means 'end-of-times'
 DROP TABLE IF EXISTS audit;
 CREATE TABLE audit (
        nkey      INTEGER,
        -- category left out in this demo
        msg       TEXT,
        timeFr    DATETEXT,
        timeTo    DATETEXT DEFAULT '9999-12-31 23:59:59',
        PRIMARY KEY (nkey,timeFr)
 );

 CREATE TRIGGER audit_insert_notes 
 AFTER INSERT ON notes FOR EACH ROW 
  BEGIN
        INSERT INTO audit (nkey, msg, timeFr)
         VALUES (new.nkey, new.msg, CURRENT_TIMESTAMP);
  END;

 CREATE TRIGGER audit_update_notes 
 AFTER UPDATE ON notes FOR EACH ROW
 BEGIN 
  UPDATE audit SET timeTo = CURRENT_TIMESTAMP
   WHERE nkey == new.nkey
     AND timeTo == '9999-12-31 23:59:59';
  INSERT INTO audit (nkey, msg, timeFr)
   VALUES (new.nkey, new.msg, CURRENT_TIMESTAMP);
 END;

 CREATE TRIGGER audit_delete_notes
 AFTER DELETE ON notes FOR EACH ROW
 BEGIN
  UPDATE audit SET timeTo = CURRENT_TIMESTAMP
        WHERE nkey == old.nkey
     AND timeTo == '9999-12-31 23:59:59';
  END;

 DROP VIEW IF EXISTS sh_audit;
 CREATE VIEW sh_audit AS
  SELECT *,
        strftime('%H:%M:%S',
        (strftime('%s',CASE timeTo 
                WHEN '9999-12-31 23:59:59' THEN CURRENT_TIMESTAMP
                ELSE timeTo 
        END) - strftime('%s',timeFr)),'unixepoch') AS lifespan
  FROM audit
  ORDER BY nkey,timeFr;

 DROP VIEW IF EXISTS sh_status;
 CREATE VIEW sh_status AS
  SELECT
        COUNT(CASE 
                WHEN timeTo == '9999-12-31 23:59:59' THEN 1 
                ELSE NULL 
        END) AS active,
        COUNT(CASE 
                WHEN timeTo <   CURRENT_TIMESTAMP    THEN 1
                ELSE NULL
        END) AS deleted
   FROM audit
  GROUP BY nkey;

COMMIT;  -- done definitions

-- test data
 INSERT INTO notes (msg) VALUES ('note 1 version 1');
 INSERT INTO notes (msg) VALUES ('note 2 version 1');
 UPDATE notes SET msg = 'note 1 version 2' WHERE nkey == 1;
 UPDATE notes SET msg = 'note 2 version 2' WHERE nkey == 2;
 UPDATE notes SET msg = 'note 1 version 3' WHERE nkey == 1;

-- queries
SELECT * FROM sh_audit;
SELECT * FROM sh_status;

SELECT 'notes';
SELECT * FROM notes;

SELECT 'audit';
SELECT * FROM audit;

SELECT 'audit with timediff';
SELECT * FROM sh_audit;


>Thanks,
>
>Sam
>

I hope this helps.
-- 
  (  Kees Nuyt
  )
c[_]

-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------