sql for wiki changes: notes

-- In my old notes, I expected that the schema for changes would look 
-- something lime the following.

create table changes (
    change_id   integer primary key auto_increment,
                                    -- arbitrary number for key
    page_id     integer not null,   -- page number
    version	integer not null,   -- version number
    change_time timestamp not null, -- timestamp of change
    who         varchar(20),        -- user who made the change
    delta       integer,            -- total magnitude of the change
    constraint fk_changes_page foreign key(page_id) references pages(page_id)
);
create unique index idx_changes on changes (page_id, version desc);

create table diffs{
    diff_id     integer primary key auto_increment,
                                    -- arbitrary number for key
    change_id   integer not null    -- what change does this refer to?
    from_line   integer not null,   -- position in the NEW page image of change start
    to_line     integer not null,   -- position in the NEW page of change_end
    old_text    longvarchar,        -- text in the OLD page that was replaced
    constraint fk_diffs_change 
                 foreign key (change_id) references changes(change_id)
);
create unique index idx_diffs on diffs (change_id, from_line desc);

-- The key query to reconstruct an old version has to go through the changes
-- starting from the most recent and go through the lines in reverse order.
-- The metakit stuff is arond line 315 of db.tcl. SQL counterpart
-- would be something like:

select changes.version, diffs.from_line, diffs.to_line, diffs.old_text
from changes, diffs
where changes.page_id = :page_id
  and changes.version > :version
  and diffs.change_id = changes.change_id
  order by changes.version desc, diffs,from_line desc;

-- How best to translate this sort of structure to mk?