MeatballDatabaseRelations

MeatballWiki | RecentChanges | Random Page | Indices | Categories

---

For OpenProcess, this page will store the current format of the relations used in the MeatballWiki database store. (This is distinct from, but may match, the InfiniteTypewriterDatabase, which documents whatever format we intend to move to next, again for OpenProcess purposes.)

  CREATE TABLE revisions (
    revision  int      PRIMARY KEY AUTO_INCREMENT,
    page      text     NOT NULL,
    title     text     NOT NULL,
    digest    text     NOT NULL,
    timestamp datetime NOT NULL,
    author    text     NOT NULL,
    ip        text     NOT NULL,
    host      text     NOT NULL,
    text      mediumtext, -- can be NULL
    
    INDEX ( page(30) ),
    INDEX ( page(30), text(1) ),
    INDEX ( timestamp ),
    FULLTEXT ( text )
  );

The revisions table uses a global revision counter to uniquely reference every edit made to the wiki. As SunirShah said, "This is an interesting design philosophically speaking: it says the wiki is primarily characterized as a set of changes."

The separate text column stores the text associated with each revision. Allowing it to be NULL allows ForgiveAndForget without losing the RC log, as stale revisions can simply have their text column set to NULL. Equally, deleting a page means simply deleting all text associated with it.

Some sample queries:

1.1. Load current revision

SELECT * FROM revisions WHERE page = ? AND text IS NOT NULL ORDER BY revision DESC LIMIT 1;

This query is optimized by the page-text index.

1.2. Load old revision

SELECT * FROM revisions WHERE revision = ? AND page = ?;

Note that the page = ? here is just a (redundant) verification, as revision IDs are unique across the whole db.

1.3. AllPages

SELECT page, max(revision) FROM revisions WHERE text IS NOT NULL GROUP BY page;

1.4. RecentChanges

SELECT * FROM revisions WHERE timestamp >= ? ORDER BY timestamp;

This query is optimized by the timestamp index.

1.5. Accessible historical revisions

SELECT revision FROM revisions WHERE page = ? AND text IS NOT NULL ORDER BY revision DESC;

This query is optimized by the page-text index.

Maintenance

With this design, post-PeerReview maintenance can be handled fast, without requiring strong isolation:

• Deleted pages are special backlinks of DeletedPage. Backlinks can be determined fast.
• Deleted pages consist of revisions older than the PeerReview period. Thus, deleting only such revisions obviates the need for isolation.
• Replacement pages are special backlinks of ReplaceFile. Backlinks can be determined fast.
• Replacement files require the whole PeerReview period to change content. Thus, replacement can be done without isolation provided the operation does not take two weeks.
• Old revisions targeted for expiration can be determined with a single query.
• The "to be expired" property is monotonic; once an old revision is found, it can be safely expired at any point in the future.

---

MeatballWiki | RecentChanges | Random Page | Indices | Categories
Edit text of this page | View other revisions
Search: