1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253 |
- CREATE TABLE IF NOT EXISTS post (
- id INTEGER PRIMARY KEY AUTOINCREMENT,
- uuid TEXT NOT NULL UNIQUE
- );
- CREATE TABLE IF NOT EXISTS tag (
- id INTEGER PRIMARY KEY AUTOINCREMENT,
- name TEXT NOT NULL UNIQUE
- );
- CREATE TABLE IF NOT EXISTS posts_index (
- post_id INTEGER NOT NULL REFERENCES post(id) ON DELETE CASCADE,
- post_time INTEGER NOT NULL,
- tag_id INTEGER NOT NULL REFERENCES tag(id) ON DELETE RESTRICT
- );
- CREATE INDEX IF NOT EXISTS tag_idx ON tag(name);
- CREATE VIEW IF NOT EXISTS posts AS SELECT pu.uuid AS id, p.post_time AS created, t.name AS tag FROM posts_index AS p JOIN tag AS t ON t.id=p.tag_id JOIN post AS pu ON p.post_id=pu.id;
- /* The intention is to read this entirely into memory at app startup */
- /* This should not incur significant costs, even with millions of posts. */
- CREATE TABLE IF NOT EXISTS routes (
- id INTEGER PRIMARY KEY AUTOINCREMENT,
- post_id INTEGER NOT NULL REFERENCES post(id) ON DELETE CASCADE,
- route TEXT NOT NULL UNIQUE,
- method_id TEXT NOT NULL REFERENCES methods(id) ON DELETE RESTRICT,
- callback_id TEXT NOT NULL REFERENCES callbacks(id) ON DELETE RESTRICT
- );
- CREATE TABLE IF NOT EXISTS methods (
- id INTEGER PRIMARY KEY AUTOINCREMENT,
- method TEXT NOT NULL UNIQUE
- );
- CREATE TABLE IF NOT EXISTS callbacks (
- id INTEGER PRIMARY KEY AUTOINCREMENT,
- callback TEXT NOT NULL UNIQUE
- );
- /* The actual routing table */
- CREATE VIEW IF NOT EXISTS all_routes AS SELECT r.id AS id, r.route AS route, m.method AS method, c.callback AS callback FROM routes AS r JOIN methods AS m ON m.id=r.method_id JOIN callbacks AS c ON c.id=r.callback_id;
- /* Fill the methods table with the HTTP verbs */
- INSERT OR IGNORE INTO methods (method) VALUES ('GET'),('POST'),('DELETE'),('PUT'),('HEAD'),('PATCH'),('CONNECT'),('OPTIONS'),('TRACE');
- CREATE TABLE IF NOT EXISTS post_aliases (
- route_id INTEGER NOT NULL REFERENCES routes(id) ON DELETE CASCADE,
- alias TEXT NOT NULL UNIQUE
- );
- CREATE VIEW IF NOT EXISTS aliases AS SELECT r.route AS actual, a.alias AS alias FROM routes AS r JOIN post_aliases AS a ON r.id=a.route_id;
|