{{ message }}
Repository navigation
Expand file tree
/
Copy pathcreate.sql
More file actions
231 lines (215 loc) · 9.71 KB
/
Copy pathcreate.sql
File metadata and controls
231 lines (215 loc) · 9.71 KB
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
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
-- v2 codebase schema
CREATE TABLE schema_version (
version INTEGER NOT NULL
);
INSERT INTO schema_version (version) VALUES (1);
-- actually stores the 512-byte hashes
CREATE TABLE hash (
id INTEGER PRIMARY KEY NOT NULL,
-- this would be the full hash, represented as base32 instead of bytes,
-- to optimize for looking them up by prefix.
base32 TEXT NOT NULL
);
CREATE UNIQUE INDEX hash_base32 ON hash(base32 COLLATE NOCASE);
-- Per https://sqlite.org/optoverview.html#the_like_optimization,
-- we need COLLATE NOCASE to enable prefix scanning with `LIKE`.
-- If we want LIKE to be case sensitive (defaults to no) then
-- see that link.
-- We want:
-- sqlite> explain query plan select id from hash where base32 like 'a1b2c3%'
-- QUERY PLAN
-- `--SEARCH TABLE hash USING COVERING INDEX hash_base32 (base32>? AND base32<?)
-- Not:
-- sqlite> explain query plan select id from hash where base32 like 'a1b2c3%'
-- QUERY PLAN
-- `--SCAN TABLE hash
CREATE TABLE text (
id INTEGER PRIMARY KEY NOT NULL,
text TEXT UNIQUE NOT NULL
);
-- The `hash_object` table is a layer of indirection that allows multiple
-- hashes to be associated to the same object. For example, if the hashing
-- algorithm for the object is changed.
-- I could imagine a UNIQUE (object_id, hash_version) constraint
-- or a UNIQUE (hash_id, hash_version) constraint, or both, but I'm not sure
-- if that will cause trouble later?
-- So: SELECT object.id, bytes FROM object
-- INNER JOIN hash_object ON object_id = object.id
-- INNER JOIN hash ON hash_id = hash.id
-- WHERE base32 LIKE 'a1b2c3%'
CREATE TABLE hash_object (
-- hashes are UNIQUE, many hashes correspond to one object
-- (causal nodes are not considered objects atm)
hash_id INTEGER PRIMARY KEY NOT NULL CONSTRAINT hash_object_fk1 REFERENCES hash(id),
object_id INTEGER NOT NULL CONSTRAINT hash_object_fk2 REFERENCES object(id),
hash_version INTEGER NOT NULL
);
-- efficient lookup of hashes by objects
CREATE INDEX hash_object_object_id ON hash_object(object_id);
-- This table is just for diagnostic queries, not for normal ucm operation,
-- by joining `ON object.type_id = object_type_description.id`
CREATE TABLE object_type_description (
id INTEGER PRIMARY KEY NOT NULL,
description TEXT UNIQUE NOT NULL
);
INSERT INTO object_type_description (id, description) VALUES
(0, "Term Component"), -- ping x = pong (x + 1), pong x = ping (x + 1)
(1, "Decl Component"), -- unique type Animal = Cat | Dog | Mouse
(2, "Namespace"), -- a one-level slice with no history
(3, "Patch") -- replace term #abc with term #def
;
-- `object` stores binary blobs that are uniquely identified by hash (alone).
-- Within the database, objects (including terms, decls, namespaces) are primarily
-- referenced by their object_ids, not hash ids. (The use of object_id is
-- meant to prove that the referenced object actually exists in the database,
-- whereas if hash_ids were used, we could only prove that the hash was in the
-- database.)
-- The `hash_object` table allows us to associate multiple hashes to objects,
-- but when we want to display a hash back to the user, we have to choose one.
-- The `primary_hash_id` tells us which one we'll use.
CREATE TABLE object (
id INTEGER PRIMARY KEY NOT NULL,
primary_hash_id INTEGER NOT NULL CONSTRAINT object_fk1 REFERENCES hash(id),
type_id INTEGER NOT NULL CONSTRAINT object_fk2 REFERENCES object_type_description(id),
bytes BLOB NOT NULL
);
-- look up objects by primary_hash_id.
CREATE UNIQUE INDEX object_hash_id ON object(primary_hash_id);
-- filter objects by
CREATE INDEX object_type_id ON object(type_id);
-- `causal` references value hash ids instead of value ids, in case you want
-- to be able to drop values and keep just the causal spine.
-- `commit_flag` and `gc_generation` are basically unused at the moment.
CREATE TABLE causal (
self_hash_id INTEGER PRIMARY KEY NOT NULL CONSTRAINT causal_fk1 REFERENCES hash(id),
value_hash_id INTEGER NOT NULL CONSTRAINT causal_fk2 REFERENCES hash(id)
);
-- Don't remember why this index exists —AI
CREATE INDEX causal_value_hash_id ON causal(value_hash_id);
-- We expect exactly 1 row, which we overwrite when we setRootNamespace.
CREATE TABLE namespace_root (
causal_id INTEGER PRIMARY KEY NOT NULL CONSTRAINT namespace_root_fk1 REFERENCES causal(self_hash_id)
);
-- LCA computations only need to look at this table
-- A causal can have many parents, and a parent may be a parent to many causals.
CREATE TABLE causal_parent (
causal_id INTEGER NOT NULL CONSTRAINT causal_parent_fk1 REFERENCES causal(self_hash_id),
parent_id INTEGER NOT NULL CONSTRAINT causal_parent_fk2 REFERENCES causal(self_hash_id),
PRIMARY KEY (causal_id, parent_id)
) WITHOUT ROWID;
CREATE INDEX causal_parent_causal_id ON causal_parent(causal_id); -- maybe redundant? covered by PK index? —AI
CREATE INDEX causal_parent_parent_id ON causal_parent(parent_id); -- ? Potentially allow a "redo" command?
-- links reference.id to causals
-- Currently unused.
CREATE TABLE causal_metadata (
causal_id INTEGER NOT NULL REFERENCES causal(self_hash_id),
metadata_object_id INTEGER NOT NULL REFERENCES object(id),
metadata_component_index INTEGER NOT NULL,
PRIMARY KEY (causal_id, metadata_object_id, metadata_component_index)
) WITHOUT ROWID;
CREATE INDEX causal_metadata_causal_id ON causal_metadata(causal_id);
CREATE TABLE watch_result (
hash_id INTEGER NOT NULL CONSTRAINT watch_result_fk1 REFERENCES hash(id),
component_index INTEGER NOT NULL,
result BLOB NOT NULL,
PRIMARY KEY (hash_id, component_index)
) WITHOUT ROWID;
CREATE TABLE watch (
hash_id INTEGER NOT NULL CONSTRAINT watch_fk1 REFERENCES hash(id),
component_index INTEGER NOT NULL,
watch_kind_id INTEGER NOT NULL CONSTRAINT watch_fk2 REFERENCES watch_kind_description(id),
PRIMARY KEY (hash_id, component_index, watch_kind_id)
) WITHOUT ROWID;
CREATE INDEX watch_kind ON watch(watch_kind_id);
CREATE TABLE watch_kind_description (
id INTEGER PRIMARY KEY NOT NULL,
description TEXT UNIQUE NOT NULL
);
INSERT INTO watch_kind_description (id, description) VALUES
(0, "Regular"), -- won't be synced
(1, "Test") -- will be synced
;
-- Related to the discussion at the `object` table, `find_type_index` indexes
-- the types by hash-based references instead of object-based references, because
-- they may be arbitrary types, not just the head types that are stored in the
-- codebase. The terms having these types are indexed by object-based referents.
CREATE TABLE find_type_index (
type_reference_builtin INTEGER NULL CONSTRAINT find_type_index_fk1 REFERENCES text(id),
type_reference_hash_id INTEGER NULL CONSTRAINT find_type_index_fk2 REFERENCES hash(id),
type_reference_component_index INTEGER NULL,
term_referent_object_id INTEGER NOT NULL CONSTRAINT find_type_index_fk3 REFERENCES object(id),
term_referent_component_index INTEGER NOT NULL,
term_referent_constructor_index INTEGER NULL,
CONSTRAINT find_type_index_c1 UNIQUE (
term_referent_object_id,
term_referent_component_index,
term_referent_constructor_index
),
CONSTRAINT find_type_index_c2 CHECK (
(type_reference_builtin IS NULL) =
(type_reference_hash_id IS NOT NULL)
),
CONSTRAINT find_type_index_c3 CHECK (
(type_reference_hash_id IS NULL) =
(type_reference_component_index IS NULL)
)
);
CREATE INDEX find_type_index_type ON find_type_index (
type_reference_builtin,
type_reference_hash_id,
type_reference_component_index
);
CREATE TABLE find_type_mentions_index (
type_reference_builtin INTEGER NULL CONSTRAINT find_type_mentions_index_fk1 REFERENCES text(id),
type_reference_hash_id INTEGER NULL CONSTRAINT find_type_mentions_index_fk2 REFERENCES hash(id),
type_reference_component_index INTEGER NULL,
term_referent_object_id INTEGER NOT NULL CONSTRAINT find_type_mentions_index_fk3 REFERENCES object(id),
term_referent_component_index INTEGER NOT NULL,
term_referent_constructor_index INTEGER NULL,
CONSTRAINT find_type_mentions_index_c1 CHECK (
(type_reference_builtin IS NULL) =
(type_reference_hash_id IS NOT NULL)
),
CONSTRAINT find_type_mentions_index_c2 CHECK (
(type_reference_hash_id IS NULL) =
(type_reference_component_index IS NULL)
)
);
CREATE INDEX find_type_mentions_index_type ON find_type_mentions_index (
type_reference_builtin,
type_reference_hash_id,
type_reference_component_index
);
-- dependents and dependencies are all in the codebase, so they use object-based references.
CREATE TABLE dependents_index (
dependency_builtin INTEGER NULL CONSTRAINT dependents_index_fk1 REFERENCES text(id),
dependency_object_id INTEGER NULL CONSTRAINT dependents_index_fk2 REFERENCES object(id),
dependency_component_index INTEGER NULL,
dependent_object_id INTEGER NOT NULL CONSTRAINT dependents_index_fk3 REFERENCES object(id),
dependent_component_index INTEGER NOT NULL,
CONSTRAINT dependents_index_c1 CHECK (
(dependency_builtin IS NULL) =
(dependency_object_id IS NOT NULL)
),
CONSTRAINT dependents_index_c2 CHECK (
(dependency_object_id IS NULL) =
(dependency_component_index IS NULL)
)
);
CREATE INDEX dependents_by_dependency ON dependents_index (
dependency_builtin,
dependency_object_id,
dependency_component_index
);
CREATE INDEX dependencies_by_dependent ON dependents_index (
dependent_object_id,
dependent_component_index
)
-- Semicolon intentionally omitted, for the same reason
-- semicolons in comments will blow up codebase initialization.
-- (oops, almost used a semicolon at the end of that last phrase!)
-- Sqlite doesn't let us submit multiple statements in the same
-- command, so we are using Haskell code to segment the statements
-- by splitting on semicolons. It doesn't know to ignore comments,
-- though I guess that wouldn't be hard to implement. Should have
-- done it from the start.
You can’t perform that action at this time.