Posted to tcl by kbk at Sat Sep 19 21:50:13 GMT 2009view raw

  1. -- In my old notes, I expected that the schema for changes would look
  2. -- something lime the following.
  3.  
  4. create table changes (
  5. change_id integer primary key auto_increment,
  6. -- arbitrary number for key
  7. page_id integer not null, -- page number
  8. version integer not null, -- version number
  9. change_time timestamp not null, -- timestamp of change
  10. who varchar(20), -- user who made the change
  11. delta integer, -- total magnitude of the change
  12. constraint fk_changes_page foreign key(page_id) references pages(page_id)
  13. );
  14. create unique index idx_changes on changes (page_id, version desc);
  15.  
  16. create table diffs{
  17. diff_id integer primary key auto_increment,
  18. -- arbitrary number for key
  19. change_id integer not null -- what change does this refer to?
  20. from_line integer not null, -- position in the NEW page image of change start
  21. to_line integer not null, -- position in the NEW page of change_end
  22. old_text longvarchar, -- text in the OLD page that was replaced
  23. constraint fk_diffs_change
  24. foreign key (change_id) references changes(change_id)
  25. );
  26. create unique index idx_diffs on diffs (change_id, from_line desc);
  27.  
  28. -- The key query to reconstruct an old version has to go through the changes
  29. -- starting from the most recent and go through the lines in reverse order.
  30. -- The metakit stuff is arond line 315 of db.tcl. SQL counterpart
  31. -- would be something like:
  32.  
  33. select changes.version, diffs.from_line, diffs.to_line, diffs.old_text
  34. from changes, diffs
  35. where changes.page_id = :page_id
  36. and changes.version > :version
  37. and diffs.change_id = changes.change_id
  38. order by changes.version desc, diffs,from_line desc;
  39.  
  40. -- How best to translate this sort of structure to mk?