diff options
| -rw-r--r-- | initialize.sql | 51 |
1 files changed, 30 insertions, 21 deletions
diff --git a/initialize.sql b/initialize.sql index 63632c2..706580d 100644 --- a/initialize.sql +++ b/initialize.sql @@ -6,10 +6,11 @@ CREATE TABLE IF NOT EXISTS image( PRIMARY KEY (sha1) ) STRICT; -CREATE INDEX IF NOT EXISTS image_dhash_idx ON image(dhash, sha1); +CREATE INDEX IF NOT EXISTS image__dhash ON image(dhash); -- +-- NOTE: This table requires garbage collection. CREATE TABLE IF NOT EXISTS directory( id INTEGER NOT NULL, -- unique ID name TEXT NOT NULL, -- basename @@ -17,45 +18,53 @@ CREATE TABLE IF NOT EXISTS directory( PRIMARY KEY (id) ) STRICT; -CREATE UNIQUE INDEX IF NOT EXISTS directory_parent_idx -ON directory(parent, name); +CREATE UNIQUE INDEX IF NOT EXISTS directory__parent_name +ON directory(IFNULL(parent, 0), name); +-- FIXME: I want a nullable parent, but that can't be a primary key. +-- - Perhaps have an INTEGER for the PK of entry, and use a UNIQUE INDEX. +-- - Alternatively, create a directory record for the root. CREATE TABLE IF NOT EXISTS entry( - -- FIXME: I want a nullable parent, but that can't be a primary key. - -- - Perhaps have an INTEGER for the PK, and use a UNIQUE INDEX. - -- - Alternatively, create a directory record for the root. - parent INTEGER REFERENCES directory(id), + parent INTEGER NOT NULL 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 (parent, name) ) STRICT; -CREATE INDEX IF NOT EXISTS entry_sha1_idx ON entry(sha1, parent, name); +CREATE INDEX IF NOT EXISTS entry__sha1 ON entry(sha1); -- --- These could also contain a description. In the future. CREATE TABLE IF NOT EXISTS tag_space( + id INTEGER NOT NULL, + name TEXT NOT NULL, + description TEXT, + PRIMARY KEY (id) +) STRICT; + +CREATE UNIQUE INDEX IF NOT EXISTS tag_space__name ON tag_space(name); + +-- To avoid having to deal with NULLs, always create this special tag space. +INSERT INTO tag_space(id, name, description) +VALUES(0, '', 'User-defined tags') +ON CONFLICT DO NOTHING; + +-- NOTE: This table requires garbage collection. +CREATE TABLE IF NOT EXISTS tag( id INTEGER NOT NULL, + space INTEGER NOT NULL REFERENCES tag_space(id), name TEXT NOT NULL, PRIMARY KEY (id) ) STRICT; -CREATE UNIQUE INDEX IF NOT EXISTS tag_space_name_idx ON tag_space(name); +CREATE UNIQUE INDEX IF NOT EXISTS tag__space_name ON tag(space, name); -CREATE TABLE IF NOT EXISTS tag( +CREATE TABLE IF NOT EXISTS tag_assignment( sha1 TEXT NOT NULL REFERENCES image(sha1), - -- FIXME: I want a nullable tag space, but that can't be a primary key. - -- - Perhaps have an INTEGER for the PK, and use a UNIQUE INDEX. - -- - (tag, space) pairs could generally use a separate table, - -- so that the TEXT column is deduplicated (or rather compressed). - -- That table just needs garbage collection. - space INTEGER REFERENCES tag_space(id), - tag TEXT NOT NULL, + tag INTEGER NOT NULL REFERENCES tag(id), weight REAL NOT NULL, -- 0..1 normalized weight assigned to tag - PRIMARY KEY (sha1, space, tag) + PRIMARY KEY (sha1, tag) ) STRICT; -CREATE INDEX IF NOT EXISTS tag_space_tag_idx ON tag(space, tag); -CREATE INDEX IF NOT EXISTS tag_tag_idx ON tag(tag); +CREATE INDEX IF NOT EXISTS tag_assignment__tag ON tag_assignment(tag); |