From bfbdf29ca3a60798c5267e88de5577a3faead9e9 Mon Sep 17 00:00:00 2001 From: Přemysl Eric Janouch Date: Fri, 8 Dec 2023 05:42:24 +0100 Subject: More basic stuff --- initialize.sql | 34 ++++++++++++++++++++++++++++++++++ 1 file changed, 34 insertions(+) create mode 100644 initialize.sql (limited to 'initialize.sql') diff --git a/initialize.sql b/initialize.sql new file mode 100644 index 0000000..07d2969 --- /dev/null +++ b/initialize.sql @@ -0,0 +1,34 @@ +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); + +CREATE TABLE IF NOT EXISTS image( + sha1 TEXT NOT NULL REFERENCES entry(sha1), + thumbw INTEGER, + thumbh INTEGER, + dhash INTEGER, -- uint64 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), + tag TEXT NOT NULL, + PRIMARY KEY (sha1) +); + +CREATE TABLE IF NOT EXISTS image_autotag( + sha1 TEXT NOT NULL REFERENCES entry(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); -- cgit v1.2.3-70-g09d2