From abdb3a4322c3e89ba1052bcff6d0b0a532e7b986 Mon Sep 17 00:00:00 2001 From: Přemysl Eric Janouch Date: Fri, 8 Dec 2023 10:16:13 +0100 Subject: Implement a few functions --- initialize.sql | 12 +++++++----- 1 file changed, 7 insertions(+), 5 deletions(-) (limited to 'initialize.sql') diff --git a/initialize.sql b/initialize.sql index 07d2969..746b0e2 100644 --- a/initialize.sql +++ b/initialize.sql @@ -1,3 +1,4 @@ +-- 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 @@ -8,24 +9,25 @@ CREATE TABLE IF NOT EXISTS entry( 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, - thumbh INTEGER, - dhash INTEGER, -- uint64 as a signed integer + 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 entry(sha1), + 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 entry(sha1), + 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) -- cgit v1.2.3-70-g09d2