diff options
Diffstat (limited to 'initialize.sql')
| -rw-r--r-- | initialize.sql | 24 |
1 files changed, 11 insertions, 13 deletions
diff --git a/initialize.sql b/initialize.sql index 6d5a9ad..cb4e3a7 100644 --- a/initialize.sql +++ b/initialize.sql @@ -1,26 +1,24 @@ +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 ON image(dhash, sha1); + -- 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 + sha1 TEXT NOT NULL REFERENCES image(sha1), PRIMARY KEY (path, basename) ) STRICT; CREATE INDEX IF NOT EXISTS entry_sha1 ON entry(sha1, path, basename); --- XXX: Shouldn't perhaps "entry.sha1" reference "image.sha1"? --- FIXME -CREATE TABLE IF NOT EXISTS image( - sha1 TEXT NOT NULL REFERENCES entry(sha1, path, basename), - 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); - CREATE TABLE IF NOT EXISTS image_tag( sha1 TEXT NOT NULL REFERENCES image(sha1), tag TEXT NOT NULL, |