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
|
-- XXX: The directory hierarchy should be perhaps kept normalized.
CREATE TABLE IF NOT EXISTS entry(
path TEXT NOT NULL, -- full FS directory path
basename TEXT NOT NULL, -- last FS path component
mtime INTEGER NOT NULL, -- Unix time of last modification in seconds
sha1 TEXT NOT NULL, -- SHA-1 hash of file in lowercase hexadecimal
PRIMARY KEY (path, basename)
);
CREATE INDEX IF NOT EXISTS entry_sha1 ON entry(sha1, path, basename);
-- XXX: Shouldn't perhaps "entry.sha1" reference "image.sha1"?
CREATE TABLE IF NOT EXISTS image(
sha1 TEXT NOT NULL REFERENCES entry(sha1),
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)
);
CREATE INDEX IF NOT EXISTS image_dhash ON image(dhash, sha1);
CREATE TABLE IF NOT EXISTS image_tag(
sha1 TEXT NOT NULL REFERENCES image(sha1),
tag TEXT NOT NULL,
PRIMARY KEY (sha1)
);
CREATE TABLE IF NOT EXISTS image_autotag(
sha1 TEXT NOT NULL REFERENCES image(sha1),
tag TEXT NOT NULL,
weight REAL NOT NULL, -- 0..1 normalized weight assigned to tag
PRIMARY KEY (sha1, tag)
);
CREATE INDEX IF NOT EXISTS image_autotag_tag ON image_autotag(tag, sha1);
|
