On 17 July 2014 21:43, Tristan Van Berkom <[email protected]> wrote:
>
> Hi all,
>
> We've been pondering how to keep history of structured records without
> storing the entirety of the structured record for each new revision, we
> have some ideas on how to achieve this but I wanted to consult this list
> in case we have overlooked some other strategies, and also just to see
> if our proposed solution to the problem is sound.
We faced similar requirements when designing a financial application and
after some experimentation we settled on an approach very similar to what
you propose. There are quite a few tricky details, one relates to deletions
(which is not mentioned in your email so perhaps it is not relevant) and an
other relates to the actual workflow; how the main application interacts with
the database. Here is a detailed example that highlights some of the tricky
parts (long -- sorry list):
----------
TransactionId is a unique id corresponding to the actual database transaction
which may or may not be useful to you
EntryId corresponds to your revision number and is (strictly)
monotically increasing
to avoid having to rely on timestamps for temporal ordering.
GroupEntryId is the unique id of the event which remains constant throughout
the life time of the event
EntryType is how we deal with the workflow issue; when working with an event
record the main app sets the EntryType to 'New' for new records,
'Amend' when amending an existing record and 'Delete' for deleting an existing
record. The main app interacts with the Event table by issuing only inserts
and issues queries on the EventCurrent table which is automatically maintained
by the triggers below. Manual (ie sql console) maintenance of the
Event table is also
handled by triggers.
create table Event (
_id integer primary key autoincrement,
TimeStamp timestamp not null,
TransactionId text not null COLLATE NOCASE,
EntryId text unique COLLATE NOCASE,
GroupEntryId text not null COLLATE NOCASE,
EntryType text COLLATE NOCASE,
EventProperty1 text,
EventProperty2 text
)
create table EventCurrent (
_id integer primary key autoincrement,
TimeStamp timestamp not null,
TransactionId text not null COLLATE NOCASE,
EntryId text unique COLLATE NOCASE,
GroupEntryId text not null COLLATE NOCASE,
EntryType text COLLATE NOCASE,
EventProperty1 text,
EventProperty2 text
)
create trigger trg_Event_update after update on Event for each row
begin
update EventCurrent set
_id = NEW._id,
TimeStamp = NEW.TimeStamp,
TransactionId = NEW.TransactionId,
EntryId = NEW.EntryId,
GroupEntryId = NEW.GroupEntryId,
EntryType = NEW.EntryType,
EventProperty1 = NEW.EventProperty1,
EventProperty2 = NEW.EventProperty2
where EventCurrent._id = NEW._id;
end
create trigger trg_Event_delete after delete on Event for each row
begin
delete from EventCurrent
where EventCurrent.EntryId = OLD.EntryId;
replace into EventCurrent
select * from Event where GroupEntryId = OLD.GroupEntryId and
EntryId = (select max(EntryId) from Event where
GroupEntryId = OLD.GroupEntryId);
delete from EventCurrent
where EventCurrent.EntryId is null;
end
create trigger trg_Event_insert_amend before insert on Event for each row
when NEW.EntryType is not null and NEW.EntryType = 'Amend'
begin
delete from EventCurrent
where EventCurrent.GroupEntryId = NEW.GroupEntryId;
end
create trigger trg_Event_insert_delete after insert on Event for each row
when NEW.EntryType is not null and NEW.EntryType = 'Delete'
begin
delete from EventCurrent
where EventCurrent.GroupEntryId = NEW.GroupEntryId;
end
create trigger trg_Event_insert_new after insert on Event for each row
when NEW.EntryType is not null and (NEW.EntryType = 'New' or
NEW.EntryType = 'Amend' or NEW.EntryType = '')
begin
insert into EventCurrent values (
NEW._id,
NEW.TimeStamp,
NEW.TransactionId,
NEW.EntryId,
NEW.GroupEntryId,
NEW.EntryType,
NEW.EventProperty1,
NEW.EventProperty2
);
end
Cheers
--
Pavlos Christoforou
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
