blob: ff62b674665ef613e82d862884737325f8f8853a (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
BEGIN TRANSACTION;
CREATE TABLE "quotes" (
"id" INTEGER,
"source" INTEGER NOT NULL,
"time" INTEGER NOT NULL,
"text" TEXT NOT NULL,
"text_zh" INTEGER,
FOREIGN KEY("source") REFERENCES "sources"("id") ON DELETE CASCADE ON UPDATE CASCADE,
PRIMARY KEY("id" AUTOINCREMENT)
);
CREATE TABLE "sources" (
"id" INTEGER,
"type" INTEGER NOT NULL,
"title" TEXT NOT NULL,
"title_zh" TEXT,
PRIMARY KEY("id" AUTOINCREMENT)
);
CREATE VIEW "v_random_quote" AS
SELECT
sources.title,
sources.title_zh,
text,
text_zh
FROM quotes
INNER JOIN sources ON sources.id = quotes.source
ORDER BY RANDOM()
LIMIT 1;
COMMIT;
|
