mlisting

 1create table list (
 2       address  TEXT PRIMARY KEY,
 3       create_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 4       name TEXT NOT NULL DEFAULT '',
 5       description  TEXT NOT NULL DEFAULT '',
 6       default_perm INTEGER NOT NULL
 7);
 8
 9create table member (
10       address TEXT NOT NULL,
11       name TEXT NOT NULL DEFAULT "",
12       list TEXT NOT NULL,
13       create_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
14       permission INTEGER DEFAULT NULL,
15       PRIMARY KEY (address, list)
16);
17
18create table message (
19       id INTEGER PRIMARY KEY AUTOINCREMENT,
20       message_id TEXT NOT NULL,
21       list TEXT NOT NULL,
22       header BYTEA NOT NULL,
23       body BYTEA NOT NULL, -- body could has multiple parts
24       create_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
25
26       subject TEXT NOT NULL DEFAULT '',
27       text TEXT NOT NULL DEFAULT '',
28
29       mpath TEXT NOT NULL DEFAULT '/',
30       UNIQUE(list, message_id)
31);
32CREATE VIRTUAL TABLE message_fts USING fts5(
33       subject, text,
34       create_at UNINDEXED,
35       content='message', content_rowid='id', tokenize='trigram');
36CREATE TRIGGER message_fts AFTER INSERT ON message BEGIN
37  INSERT INTO message_fts(rowid, subject, text, create_at) VALUES (new.id, new.subject, new.text, new.create_at);
38END;