schema.html

CREATE TABLE messages (  author TEXT,  id TEXT PRIMARY KEY,  sequence INTEGER,  timestamp REAL,  previous TEXT,  hash TEXT,  content BLOB,  signature TEXT,  flags INTEGER,  UNIQUE(author, sequence));

CREATE TABLE messages_stats (  author TEXT PRIMARY KEY,  max_sequence INTEGER NOT NULL,  max_timestamp REAL NOT NULL,  size INTEGER NOT NULL DEFAULT 0);

CREATE TRIGGER messages_ai_stats AFTER INSERT ON messages BEGIN INSERT INTO messages_stats(author, max_sequence, max_timestamp, size) VALUES (new.author, new.sequence, new.timestamp, length(json(new.content))) ON CONFLICT DO UPDATE SET max_sequence = MAX(max_sequence, excluded.max_sequence), max_timestamp = MAX(max_timestamp, excluded.max_timestamp), size = size + excluded.size; END;

CREATE TRIGGER messages_ad_stats AFTER DELETE ON messages BEGIN UPDATE messages_stats SET max_sequence = updated.sequence, max_timestamp = updated.timestamp, size = size - length(json(old.content)) FROM (  SELECT COALESCE(MAX(messages.sequence), 0) AS sequence, COALESCE(MAX(messages.timestamp), 0) AS timestamp   FROM messages WHERE messages.author = old.author) AS updated WHERE messages_stats.author = old.author; DELETE FROM messages_stats WHERE messages_stats.author = old.author AND messages_stats.max_sequence = 0; END;

CREATE INDEX messages_author_sequence_index ON messages (author, sequence);

CREATE INDEX messages_author_timestamp_index ON messages (author, timestamp);

CREATE INDEX messages_channel_author_timestamp_root_index ON messages (content ->> 'channel', author, timestamp, content ->> 'root');

CREATE INDEX messages_contact_index2 ON messages(author, content ->> '$.contact', sequence, content ->> '$.following', content ->> '$.blocking') WHERE content ->> '$.type' = 'contact';

CREATE INDEX messages_id_author_index ON messages (id, author);

CREATE INDEX messages_size_by_author_index ON messages (author, length(content));

CREATE INDEX messages_timestamp_index ON messages (timestamp);

CREATE INDEX messages_type_timestamp_index ON messages (content ->> 'type', timestamp);

CREATE INDEX messages_type_root_author_channel_index ON messages (author, content ->> 'type', content ->> 'root', content ->> 'channel');

CREATE TABLE blobs (  id TEXT PRIMARY KEY,  content BLOB,  created INTEGER);

CREATE INDEX blobslength ON blobs (id, length(content));

CREATE TABLE properties (  id TEXT,  key TEXT,  value TEXT,  UNIQUE(id, key));

CREATE TABLE connections (  host TEXT,  port INTEGER,  key TEXT,  last_attempt INTEGER,  last_success INTEGER,  UNIQUE(host, port, key));

CREATE TABLE identities (  user TEXT,  public_key TEXT UNIQUE,  private_key TEXT UNIQUE);

CREATE INDEX identities_user ON identities (user, public_key);

CREATE TABLE invites (  invite_public_key TEXT PRIMARY KEY,  account TEXT,  use_count INTEGER,  expires INTEGER);

CREATE TABLE blocks (  id TEXT PRIMARY KEY,  timestamp REAL);

CREATE VIRTUAL TABLE messages_fts USING fts5(content, content=messages, content_rowid=rowid)

/* messages_fts(content) */;

CREATE TABLE 'messages_fts_data'(id INTEGER PRIMARY KEY, block BLOB);

CREATE TABLE 'messages_fts_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID;

CREATE TABLE 'messages_fts_docsize'(id INTEGER PRIMARY KEY, sz BLOB);

CREATE TABLE 'messages_fts_config'(k PRIMARY KEY, v) WITHOUT ROWID;

CREATE TRIGGER messages_ai AFTER INSERT ON messages BEGIN INSERT INTO messages_fts(rowid, content) VALUES (new.rowid, json(new.content)); END;

CREATE TRIGGER messages_ad AFTER DELETE ON messages BEGIN INSERT INTO messages_fts(messages_fts, rowid, content) VALUES ('delete', old.rowid, old.content); END;

CREATE TABLE messages_refs (  message TEXT,   ref TEXT,   UNIQUE(message, ref));

CREATE TRIGGER messages_ai_refs AFTER INSERT ON messages BEGIN INSERT INTO messages_refs(message, ref) SELECT DISTINCT new.id, j.value FROM json_tree(new.content) AS j WHERE j.value LIKE '&%.sha256' OR j.value LIKE '!%%.sha256' ESCAPE '!' OR j.value LIKE '@%.ed25519' OR (j.value LIKE '#%' AND INSTR(j.value, ' ') = 0 AND INSTR(j.value, char(9)) = 0 AND INSTR(j.value, char(10)) = 0 AND INSTR(j.value, char(13)) = 0 AND INSTR(j.value, ',') = 0) ON CONFLICT DO NOTHING; END;

CREATE TRIGGER messages_ad_refs AFTER DELETE ON messages BEGIN DELETE FROM messages_refs WHERE messages_refs.message = old.id; END;

CREATE UNIQUE INDEX messages_refs_message_ref_idx ON messages_refs (message, ref);

CREATE UNIQUE INDEX messages_refs_ref_message_idx ON messages_refs (ref, message);

CREATE TABLE blobs_refs (  blob TEXT,   ref TEXT,   UNIQUE(blob, ref));

CREATE TRIGGER blobs_ai_refs AFTER INSERT ON blobs BEGIN INSERT INTO blobs_refs(blob, ref) SELECT DISTINCT new.id, j.value FROM json_tree(new.content) AS j WHERE json_valid(new.content) AND j.value LIKE '&%.sha256' ON CONFLICT DO NOTHING; END;

CREATE TRIGGER blobs_ad_refs AFTER DELETE ON blobs BEGIN DELETE FROM blobs_refs WHERE blobs_refs.blob = old.id; END;

CREATE INDEX blobs_refs_blob_idx ON blobs_refs (blob);

CREATE INDEX blobs_refs_ref_idx ON blobs_refs (ref);

CREATE VIEW blob_wants_view (source, id, timestamp) AS   WITH RECURSIVE   wanted1 AS (     SELECT messages_refs.message AS source, messages_refs.ref AS id, messages.timestamp AS timestamp     FROM messages_refs     JOIN messages ON messages.id = messages_refs.message     UNION     SELECT messages_refs.message AS source, messages_refs.ref AS id, unixepoch() * 1000 AS timestamp     FROM messages_refs     JOIN messages ON messages.id = messages_refs.message     WHERE messages.content ->> 'type' = 'about'   ),   wanted(source, id, timestamp) AS (     SELECT wanted1.source AS source, wanted1.id AS id, wanted1.timestamp AS timestamp FROM wanted1     UNION     SELECT wanted.source AS source, br.ref AS id, wanted.timestamp AS timestamp FROM wanted JOIN blobs_refs br ON br.blob = wanted.id   )   SELECT wanted.source, wanted.id, wanted.timestamp FROM wanted   LEFT OUTER JOIN blobs ON wanted.id = blobs.id   WHERE blobs.id IS NULL     AND LENGTH(wanted.id) = 52     AND wanted.id LIKE '&%.sha256'

/* blob_wants_view(source,id,timestamp) */;

CREATE TABLE blob_wants_cache (source TEXT, id TEXT, timestamp REAL, UNIQUE(source, id));

CREATE UNIQUE INDEX blob_wants_cache_source_id_unique_index ON blob_wants_cache (COALESCE(source, ''), id);

CREATE INDEX blob_wants_cache_id_idx ON blob_wants_cache (id);

CREATE INDEX blob_wants_cache_timestamp_id_idx ON blob_wants_cache (timestamp, id);

CREATE TRIGGER messages_ai_blob_wants_cache AFTER INSERT ON messages_refs BEGIN INSERT INTO blob_wants_cache (source, id, timestamp) SELECT messages.id, new.ref, messages.timestamp FROM messages LEFT OUTER JOIN blobs ON new.ref = blobs.id WHERE messages.id = new.message AND LENGTH(new.ref) = 52 AND new.ref LIKE '&%.sha256' AND blobs.content IS NULL ON CONFLICT (source, id) DO NOTHING; END;

CREATE TRIGGER blobs_refs_ai_blob_wants_cache AFTER INSERT ON blobs_refs BEGIN INSERT INTO blob_wants_cache (source, id, timestamp) SELECT new.blob, blobs_refs.ref, unixepoch() * 1000 AS timestamp FROM blobs_refs LEFT OUTER JOIN blobs ON new.ref = blobs.id WHERE blobs_refs.blob = new.ref AND blobs.content IS NULL ON CONFLICT (source, id) DO NOTHING; END;

CREATE TRIGGER messages_ad_blob_wants_cache AFTER DELETE ON messages BEGIN DELETE FROM blob_wants_cache WHERE blob_wants_cache.source = old.id; END;

CREATE TRIGGER blobs_ai_blob_wants_cache AFTER INSERT ON blobs BEGIN DELETE FROM blob_wants_cache WHERE blob_wants_cache.id = new.id; END;

CREATE TABLE sqlite_stat1(tbl,idx,stat);