-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathinit.go
110 lines (105 loc) · 5.49 KB
/
init.go
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
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
package sqlite
const (
createActorsQuery = `
CREATE TABLE IF NOT EXISTS actors (
"raw" BLOB,
"iri" TEXT GENERATED ALWAYS AS (json_extract(raw, '$.id')) VIRTUAL NOT NULL constraint actors_key unique,
"type" TEXT GENERATED ALWAYS AS (json_extract(raw, '$.type')) VIRTUAL NOT NULL,
"to" BLOB GENERATED ALWAYS AS (json_extract(raw, '$.to')) VIRTUAL,
"bto" BLOB GENERATED ALWAYS AS (json_extract(raw, '$.bto')) VIRTUAL,
"cc" BLOB GENERATED ALWAYS AS (json_extract(raw, '$.cc')) VIRTUAL,
"bcc" BLOB GENERATED ALWAYS AS (json_extract(raw, '$.bcc')) VIRTUAL,
"published" TEXT GENERATED ALWAYS AS (json_extract(raw, '$.published')) VIRTUAL,
"updated" TEXT GENERATED ALWAYS AS (coalesce(json_extract(raw, '$.updated'), json_extract(raw, '$.deleted'), json_extract(raw, '$.published'))) VIRTUAL,
"url" TEXT GENERATED ALWAYS AS (json_extract(raw, '$.url')) VIRTUAL,
"name" TEXT GENERATED ALWAYS AS (json_extract(raw, '$.name')) VIRTUAL,
"preferred_username" TEXT GENERATED ALWAYS AS (json_extract(raw, '$.preferredUsername')) VIRTUAL
) STRICT;
CREATE INDEX actors_type ON actors(type);
CREATE INDEX actors_name ON actors(name, preferred_username);
CREATE INDEX actors_published ON actors(published);
CREATE INDEX actors_updated ON actors(updated);
`
createActivitiesQuery = `
CREATE TABLE IF NOT EXISTS activities (
"raw" BLOB,
"iri" TEXT GENERATED ALWAYS AS (json_extract(raw, '$.id')) VIRTUAL NOT NULL constraint activities_key unique,
"type" TEXT GENERATED ALWAYS AS (json_extract(raw, '$.type')) VIRTUAL NOT NULL,
"to" BLOB GENERATED ALWAYS AS (json_extract(raw, '$.to')) VIRTUAL,
"bto" BLOB GENERATED ALWAYS AS (json_extract(raw, '$.bto')) VIRTUAL,
"cc" BLOB GENERATED ALWAYS AS (json_extract(raw, '$.cc')) VIRTUAL,
"bcc" BLOB GENERATED ALWAYS AS (json_extract(raw, '$.bcc')) VIRTUAL,
"published" TEXT GENERATED ALWAYS AS (json_extract(raw, '$.published')) VIRTUAL,
"updated" TEXT GENERATED ALWAYS AS (coalesce(json_extract(raw, '$.updated'), json_extract(raw, '$.deleted'), json_extract(raw, '$.published'))) VIRTUAL,
"url" TEXT GENERATED ALWAYS AS (json_extract(raw, '$.url')) VIRTUAL,
"actor" TEXT GENERATED ALWAYS AS (json_extract(raw, '$.actor')) VIRTUAL NOT NULL CONSTRAINT activities_actors_iri_fk REFERENCES actors (iri),
"object" TEXT GENERATED ALWAYS AS (json_extract(raw, '$.object')) VIRTUAL CONSTRAINT activities_objects_iri_fk REFERENCES objects (iri)
) STRICT;
CREATE INDEX activities_type ON activities(type);
CREATE INDEX activities_actor ON activities(actor);
CREATE INDEX activities_object ON activities(object);
CREATE INDEX activities_published ON activities(published);
CREATE INDEX activities_updated ON activities(updated);
`
createObjectsQuery = `
CREATE TABLE IF NOT EXISTS objects (
"raw" BLOB,
"iri" TEXT GENERATED ALWAYS AS (json_extract(raw, '$.id')) VIRTUAL NOT NULL constraint objects_key unique,
"type" TEXT GENERATED ALWAYS AS (json_extract(raw, '$.type')) VIRTUAL,
"to" BLOB GENERATED ALWAYS AS (json_extract(raw, '$.to')) VIRTUAL,
"bto" BLOB GENERATED ALWAYS AS (json_extract(raw, '$.bto')) VIRTUAL,
"cc" BLOB GENERATED ALWAYS AS (json_extract(raw, '$.cc')) VIRTUAL,
"bcc" BLOB GENERATED ALWAYS AS (json_extract(raw, '$.bcc')) VIRTUAL,
"published" TEXT GENERATED ALWAYS AS (json_extract(raw, '$.published')) VIRTUAL,
"updated" TEXT GENERATED ALWAYS AS (coalesce(json_extract(raw, '$.updated'), json_extract(raw, '$.deleted'), json_extract(raw, '$.published'))) VIRTUAL,
"url" TEXT GENERATED ALWAYS AS (json_extract(raw, '$.url')) VIRTUAL,
"name" TEXT GENERATED ALWAYS AS (json_extract(raw, '$.name')) VIRTUAL,
"summary" TEXT GENERATED ALWAYS AS (json_extract(raw, '$.summary')) VIRTUAL,
"content" TEXT GENERATED ALWAYS AS (json_extract(raw, '$.content')) VIRTUAL
) STRICT;
CREATE INDEX objects_type ON objects(type);
CREATE INDEX objects_name ON objects(name);
CREATE INDEX objects_content ON objects(content);
CREATE INDEX objects_published ON objects(published);
CREATE INDEX objects_updated ON objects(updated);
`
createCollectionsQuery = `
CREATE TABLE IF NOT EXISTS collections (
"raw" BLOB,
"iri" TEXT GENERATED ALWAYS AS (json_extract(raw, '$.id')) VIRTUAL NOT NULL constraint collections_key unique,
"type" TEXT GENERATED ALWAYS AS (json_extract(raw, '$.type')) VIRTUAL,
"to" BLOB GENERATED ALWAYS AS (json_extract(raw, '$.to')) VIRTUAL,
"bto" BLOB GENERATED ALWAYS AS (json_extract(raw, '$.bto')) VIRTUAL,
"cc" BLOB GENERATED ALWAYS AS (json_extract(raw, '$.cc')) VIRTUAL,
"bcc" BLOB GENERATED ALWAYS AS (json_extract(raw, '$.bcc')) VIRTUAL,
"published" TEXT GENERATED ALWAYS AS (json_extract(raw, '$.published')) VIRTUAL,
"updated" TEXT GENERATED ALWAYS AS (json_extract(raw, '$.updated')) VIRTUAL,
"items" BLOB
) STRICT;
CREATE INDEX collections_type ON collections(type);
CREATE INDEX collections_published ON collections(published);
CREATE INDEX collections_updated ON collections(updated);
`
createMetaQuery = `
CREATE TABLE IF NOT EXISTS meta (
"published" TEXT default CURRENT_TIMESTAMP,
"raw" BLOB,
"iri" TEXT NOT NULL constraint meta_key unique
) STRICT;
`
tuneQuery = `
-- Use WAL mode (writers don't block readers):
--PRAGMA journal_mode = DELETE;
-- Use memory as temporary storage:
PRAGMA temp_store = MEMORY;
-- Faster synchronization that still keeps the data safe:
PRAGMA synchronous = NORMAL;
-- Increase cache size (in this case to 64MB), the default is 2MB
PRAGMA cache_size = -64000;
-- from BJohnson's recommendations to use with litestream
PRAGMA journal_mode = WAL;
PRAGMA busy_timeout = 5000;
PRAGMA wal_autocheckpoint = 0;
PRAGMA strict=ON;
`
)