diff options
Diffstat (limited to 'initialize.sql')
| -rw-r--r-- | initialize.sql | 30 |
1 files changed, 21 insertions, 9 deletions
diff --git a/initialize.sql b/initialize.sql index cb4e3a7..0f43af5 100644 --- a/initialize.sql +++ b/initialize.sql @@ -1,23 +1,35 @@ 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 + 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 ON image(dhash, sha1); --- XXX: The directory hierarchy should be perhaps kept normalized. +-- + +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 ON directory(parent, name); + CREATE TABLE IF NOT EXISTS entry( - path TEXT NOT NULL, -- full FS directory path - basename TEXT NOT NULL, -- last FS path component + 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 (path, basename) + PRIMARY KEY (parent, name) ) STRICT; -CREATE INDEX IF NOT EXISTS entry_sha1 ON entry(sha1, path, basename); +CREATE INDEX IF NOT EXISTS entry_sha1 ON entry(sha1, parent, name); + +-- CREATE TABLE IF NOT EXISTS image_tag( sha1 TEXT NOT NULL REFERENCES image(sha1), |