More basic stuff

1 files changed, 34 insertions, 0 deletions

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);