Notes on a sqlite-based database

[shawnw]

I want to allow for saving the database in a single sqlite database as an alternative to flatfiles, as I think it'll make a lot of things easier and faster and more reliable.

This wouldn't replace the current in-memory representation of a database (I don't really want to do SQL queries to find out what type an object is, or if it has a flag, or where its location is). The exception might be @mail, where moving it to a nicely normalized database would be an improvement over the current data structures - it would make seeing everybody who got a message trivial, for example, where doing it now requires just as major a rewrite of the code.

Other advantages include having a single file to backup instead of 3, and getting sqlite3's ACID properties to help limit data corruption and loss to practically 0.

The workflow on startup would be something like this:

• If no sql db, but flatfiles exist, read them and populate a new sql database.
• If both are present, and the flatfiles are newer, see previous point.
• If both are present and the sql db is newer, load the database from it.
• Alternatively, if both are present require a command-line argument to tell which to use. Or have this plus 'newest' as the default policy.

The workflow while the game is running:

• As objects and other data like mail and channels are modified, add them to a list.
• Periodically, write the modified objects to the database. Since it's SQL and not a flatfile you don't have to write the entire thing all at once, leading to time savings.
• This can be done on a fixed interval like current dumps, or maybe more fine grained, after, say, every queue entry completes. I favor the latter.
• Still allow for dumping traditional flatfiles on demand. @dump/flatfile or something.

The workflow on shutdown:

• One last sync of modified objects.

Other musings.

• It might be possible to, say, load attributes on demand. Savings in startup time and memory.

• It's possible to make a sqlite3 module that represents the database as virtual sql tables. This makes syncing between in-memory and db versions as simple as things like INSERT INTO attributes SELECT * from mushdb_attributes; instead of doing it with binding values to prepared statements in C. Nice and clean (Just don't look at the module source).

• We can also track not just which objects were modified, but what part of them were, and only update that.

Wilder dreaming

• Replace @search with SQL queries (@entrances already does this), with appropriate indexes on the tables to speed things up.

• Supplement @grep with full text search of attributes.

[shawnw]

Incomplete rough draft of the database schema:

PRAGMA foreign_keys = ON;
CREATE TABLE objects(
          dbref INTEGER NOT NULL PRIMARY KEY
        , name TEXT NOT NULL
        -- other per-object fields
        );

CREATE TABLE attributes(
         dbref INTEGER NOT NULL
       , name TEXT NOT NULL
       , owner INTEGER
       , flags TEXT
       , contents TEXT
       , PRIMARY KEY(dbref, name)
       , FOREIGN KEY(dbref) REFERENCES objects(dbref) ON DELETE CASCADE
       , FOREIGN KEY(owner) REFERENCES objects(dbref) ON DELETE SET NULL
       ) WITHOUT ROWID;
CREATE INDEX attributes_idx_owner ON attributes(owner);
CREATE TABLE locks(
         dbref INTEGER NOT NULL
       , name TEXT NOT NULL
       , owner INTEGER
       , flags TEXT
       , boolexp TEXT NOT NULL
       , PRIMARY KEY(dbref, name)
       , FOREIGN KEY(dbref) REFERENCES objects(dbref) ON DELETE CASCADE
       , FOREIGN KEY(owner) REFERENCES objects(dbref) ON DELETE SET NULL
       ) WITHOUT ROWID;
CREATE INDEX locks_idx_owner ON locks(owner);

-- Data for a single @mail
CREATE TABLE mail_message(
         id INTEGER NOT NULL PRIMARY KEY
       , sender INTEGER
       , timestamp INTEGER NOT NULL
       , subject TEXT
       , body TEXT
       , urgent INTEGER NOT NULL
       -- plus other global per-message flags
       , FOREIGN KEY(sender) REFERENCES objects(dbref) ON DELETE SET NULL
       );
CREATE INDEX mail_message_idx_sender ON mail_message(sender);
CREATE TABLE mail_recipients(
         mailid INTEGER NOT NULL
       , addressee INTEGER
       , PRIMARY KEY(mailid, addressee)
       , FOREIGN KEY(mailid) REFERENCES mail_message(id) ON DELETE CASCADE
       , FOREIGN KEY(addressee) REFERENCES objects(dbref) ON DELETE SET NULL
       );
CREATE INDEX mail_recipients_idx_addresse ON mail_recipients(addressee);

-- @mail full text search
CREATE VIRTUAL TABLE mailfts USING fts5(
         subject
       , body
       , content='mail_message'
       , content_rowid='id'
       );
CREATE TRIGGER mail_message_ai AFTER INSERT ON mail_message
   BEGIN
      INSERT INTO mailfts(rowid, subject, body)
                  VALUES (new.id, new.subject, new.body);
   END;
CREATE TRIGGER mail_message_ad AFTER DELETE ON mail_message
   BEGIN
      INSERT INTO mailfts(mailfts, rowid, subject, body)
                  VALUES ('delete', old.id, old.subject, old.body);
   END;

-- Recipient-specific data about their @mails
CREATE TABLE mail_envelope(
         player INTEGER NOT NULL
       , mailid INTEGER NOT NULL
       , folder INTEGER NOT NULL DEFAULT 0
       , read INTEGER NOT NULL DEFAULT 0
       , cleared INTEGER NOT NULL DEFAULT 0
       , tagged INTEGER NOT NULL DEFAULT 0
       -- plus other player-specific per-message flags
       , PRIMARY KEY(player, mailid)
       , FOREIGN KEY(player) REFERENCES objects(dbref) ON DELETE CASCADE
       , FOREIGN KEY(mailid) REFERENCES mail_message(id) ON DELETE RESTRICT
       ) WITHOUT ROWID;
CREATE INDEX mail_envelope_idx_mailid ON mail_envelope(mailid);

-- Get all messages in a particular player's given folder
-- SELECT mm.sender, mm.timestamp, mm.subject, mm.body, group_concat(ifnull((SELECT name FROM objects WHERE dbref = mr.addressee), '(deleted)'), ', ') AS sent_to FROM mail_message AS mm JOIN mail_envelope AS me ON mm.id = me.mailid JOIN mail_recipients AS mr ON mm.id = mr.mailid WHERE me.player = ? AND me.folder = ? GROUP BY mm.id ORDER BY timestamp DESC;

-- @malias data
CREATE TABLE mail_alias(
         id INTEGER NOT NULL PRIMARY KEY
       , name TEXT NOT NULL UNIQUE
       , description TEXT
       , owner INTEGER
       , use_flags INTEGER NOT NULL
       , list_flags INTEGER NOT NULL
       , FOREIGN KEY(owner) REFERENCES objects(dbref) ON DELETE SET NULL
       );
CREATE INDEX mail_alias_idx_owner ON mail_alias(owner);
CREATE TABLE mail_alias_members(
         maliasid INTEGER NOT NULL
       , player INTEGER NOT NULL
       , PRIMARY KEY(maliasid, player)
       , FOREIGN KEY(maliasid) REFERENCES mail_alias(id) ON DELETE CASCADE
       , FOREIGN KEY(player) REFERENCES objects(dbref) ON DELETE CASCADE
       ) WITHOUT ROWID;
CREATE INDEX mail_alias_members_idx_player ON mail_alias_members(player);

-- @channels
CREATE TABLE channel(
          id INTEGER NOT NULL PRIMARY KEY
        , name TEXT NOT NULL UNIQUE
        , description TEXT
        , privbits TEXT
        , cost INTEGER NOT NULL
        , creator INTEGER
        , mogrifier INTEGER
        , num_messages INTEGER NOT NULL DEFAULT 0
        , joinlock TEXT
        , speaklock TEXT
        , modifylock TEXT
        , seelock TEXT
        , hidelock TEXT
        , buffer_lines INTEGER NOT NULL DEFAULT 0
        , FOREIGN KEY(creator) REFERENCES objects(dbref) ON DELETE SET NULL
        , FOREIGN KEY(mogrifier) REFERENCES objects(dbref) ON DELETE SET NULL
        );
CREATE INDEX channel_idx_creator ON channel(creator);
CREATE INDEX channel_idx_mogrifier ON channel(mogrifier);

CREATE TABLE channel_users(
          chanid INTEGER NOT NULL
        , dbref INTEGER NOT NULL
        , privbits TEXT
        , title TEXT
        , PRIMARY KEY(chanid, dbref)
        , FOREIGN KEY(chanid) REFERENCES channel(id) ON DELETE CASCADE
        , FOREIGN KEY(dbref) REFERENCES objects(dbref) ON DELETE CASCADE
        ) WITHOUT ROWID;
CREATE INDEX channel_users_idx_dbref ON channel_users(dbref);

CREATE TABLE channel_buffer(
          chanid INTEGER NOT NULL
        , timestamp INTEGER NOT NULL
        , speaker INTEGER
        , message TEXT
        , FOREIGN KEY(chanid) REFERENCES channel(id) ON DELETE CASCADE
        , FOREIGN KEY(speaker) REFERENCES objects(dbref) ON DELETE SET NULL
        );
CREATE INDEX channel_buffer_idx_chanid_ts ON channel_buffer(chanid, timestamp DESC);
CREATE INDEX channel_buffer_idx_speaker ON channel_buffer(speaker);

-- Delete all but the last N lines (Requires a custom sqlite3.c)
-- DELETE FROM channel_buffer WHERE chanid = ? ORDER BY timestamp DESC LIMIT -1 OFFSET ?

[shawnw]

qa was asking about tracking ctime/mtime on a per-attribute basis. This has been brought up a few times over the years, but we've always said no due to not wanting to add the extra fields to the attribute struct and increased memory usage that goes long with that.

However, it's possible to add them to the sql attribute table, and have the ctime() and mtime() functions query it instead of the in-memory database. Best of both worlds type thing. Could even possibly have triggers that are fired on inserts and updates to automatically update the fields.

[mike347]

I do like the idea of per-attr ctime/mtime, seems like an appropriate time to do it.