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
|
CREATE TABLE IF NOT EXISTS image(
sha1 TEXT NOT NULL, -- SHA-1 hash of file in lowercase hexadecimal
thumbw INTEGER, -- cached thumbnail width, if known
thumbh INTEGER, -- cached thumbnail height, if known
dhash INTEGER, -- uint64 perceptual hash as a signed integer
PRIMARY KEY (sha1)
) STRICT;
CREATE INDEX IF NOT EXISTS image_dhash_idx ON image(dhash, sha1);
--
CREATE TABLE IF NOT EXISTS directory(
id INTEGER NOT NULL, -- unique ID
name TEXT NOT NULL, -- basename
parent INTEGER REFERENCES directory(id), -- root if NULL
PRIMARY KEY (id)
) STRICT;
CREATE UNIQUE INDEX IF NOT EXISTS directory_parent_idx
ON directory(parent, name);
CREATE TABLE IF NOT EXISTS entry(
-- FIXME: I want a nullable parent, but that can't be a primary key.
-- - Perhaps have an INTEGER for the PK, and use a UNIQUE INDEX.
-- - Alternatively, create a directory record for the root.
parent INTEGER REFERENCES directory(id),
name TEXT NOT NULL, -- last FS path component
mtime INTEGER NOT NULL, -- Unix time of last modification in seconds
sha1 TEXT NOT NULL REFERENCES image(sha1),
PRIMARY KEY (parent, name)
) STRICT;
CREATE INDEX IF NOT EXISTS entry_sha1_idx ON entry(sha1, parent, name);
--
-- These could also contain a description. In the future.
CREATE TABLE IF NOT EXISTS tag_space(
id INTEGER NOT NULL,
name TEXT NOT NULL,
PRIMARY KEY (id)
) STRICT;
CREATE UNIQUE INDEX IF NOT EXISTS tag_space_name_idx ON tag_space(name);
CREATE TABLE IF NOT EXISTS tag(
sha1 TEXT NOT NULL REFERENCES image(sha1),
-- FIXME: I want a nullable tag space, but that can't be a primary key.
-- - Perhaps have an INTEGER for the PK, and use a UNIQUE INDEX.
-- - (tag, space) pairs could generally use a separate table,
-- so that the TEXT column is deduplicated (or rather compressed).
-- That table just needs garbage collection.
space INTEGER REFERENCES tag_space(id),
tag TEXT NOT NULL,
weight REAL NOT NULL, -- 0..1 normalized weight assigned to tag
PRIMARY KEY (sha1, space, tag)
) STRICT;
CREATE INDEX IF NOT EXISTS tag_space_tag_idx ON tag(space, tag);
CREATE INDEX IF NOT EXISTS tag_tag_idx ON tag(tag);
|
