PRAGMA foreign_keys=on;

CREATE TABLE tags (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
CONSTRAINT uc_tagsTitle UNIQUE (title)
);

CREATE TABLE topic (
id INTEGER PRIMARY KEY,
path TEXT NOT NULL,
ftime TIMESTAMP,
CONSTRAINT uc_topicTitle UNIQUE (path)
);

CREATE TABLE topic_tags (
topic_id INTEGER NOT NULL,
tags_id INTEGER NOT NULL,
FOREIGN KEY (topic_id) REFERENCES topic(id) ON DELETE CASCADE,
FOREIGN KEY (tags_id) REFERENCES tags(id) ON DELETE CASCADE,
CONSTRAINT uc_TopicTags UNIQUE (topic_id, tags_id)
);

-- получаю список файлов с тегами так:
SELECT topic.path,tags.title FROM (
    topic JOIN topic_tags ON (topic.id = topic_tags.topic_id)
    ) JOIN tags ON (tags.id = topic_tags.tags_id);

-- получаю список файлов по указанному тегу так:
SELECT topic.path FROM (
    tags JOIN topic_tags ON tags.title = 'tag3' and tags.id = topic_tags.tags_id
	)    JOIN topic ON (topic.id = topic_tags.topic_id);

-- получаю список тегов для файла так:
SELECT tags.title FROM (
    topic JOIN topic_tags ON topic.path = '/docs/doc4.md' and topic.id = topic_tags.topic_id
	)     JOIN tags ON (tags.id = topic_tags.tags_id);

-- фейковое представление для вставки новых документов с тегами в 1 запрос:
CREATE VIEW inserter AS SELECT null as doc, null as fmtime, null as tag1, null as tag2, null as tag3;

-- триггер для представления (в него идёт запись документа с тегами):
CREATE TRIGGER tr_newdoc
INSTEAD OF INSERT ON inserter FOR EACH ROW
BEGIN
INSERT INTO topic (path, ftime) VALUES (new.doc, new.fmtime);
INSERT INTO tags (title) SELECT new.tag1 as title WHERE title NOT NULL;
INSERT INTO tags (title) SELECT new.tag2 as title WHERE title NOT NULL;
INSERT INTO tags (title) SELECT new.tag3 as title WHERE title NOT NULL;
END;

-- идея была использовать для добавления запрос в 1 строку наподобие:
-- для 3 тегов к документу
INSERT INTO inserter (doc,fmtime,tag1,tag2,tag3) VALUES ('/docs/test1.md', 7346342, 'tag1', 'tag2', 'tag3');
-- для документа без тегов:
INSERT INTO inserter (doc,fmtime) VALUES ('/docs/test2.md', 7346342);
-- для документа с тегами в упрощенном виде:
INSERT INTO inserter VALUES ('/docs/test3.md', 7346342, 'tag1','tag2','tag3');

INSERT INTO inserter (doc,fmtime,tag1,tag2,tag3) VALUES ('/docs/test1.md', 7346342, 'tag1', 'tag2', 'tag3');

CREATE TRIGGER tr_newdoc
INSTEAD OF INSERT ON inserter FOR EACH ROW
BEGIN
INSERT INTO topic (path, ftime) SELECT NEW.doc, NEW.ftime WHERE NOT EXISTS
    (SELECT 1 FROM topic WHERE path = NEW.doc);

INSERT INTO tags (title) SELECT NEW.tag1 WHERE NEW.tag1 NOT NULL AND NOT EXISTS
    (SELECT 1 FROM tags WHERE title = NEW.tag1);
INSERT INTO topic_tags SELECT topic.id, tags.id FROM tags JOIN topic ON 
    (NEW.tag1 NOT NULL AND topic.path = NEW.doc and tags.title = NEW.tag1);

INSERT INTO tags (title) SELECT NEW.tag2 WHERE NEW.tag2 NOT NULL AND NOT EXISTS
    (SELECT 1 FROM tags WHERE title = NEW.tag2);
INSERT INTO topic_tags SELECT topic.id, tags.id FROM tags JOIN topic ON 
    (NEW.tag2 NOT NULL AND topic.path = NEW.doc and tags.title = NEW.tag2);

INSERT INTO tags (title) SELECT NEW.tag3 WHERE NEW.tag3 NOT NULL AND NOT EXISTS
    (SELECT 1 FROM tags WHERE title = NEW.tag3);
INSERT INTO topic_tags SELECT topic.id, tags.id FROM tags JOIN topic ON 
    (NEW.tag3 NOT NULL AND topic.path = NEW.doc and tags.title = NEW.tag3);
END;

INSERT INTO inserter (doc, ftime, tag1, tag2, tag3) VALUES ('~/mail/736.msg', 34534563, 'work', 'mail', 'to-do');
INSERT INTO inserter (doc, ftime, tag1, tag2, tag3) VALUES ('~/mail/534.msg', 56756342, 'to-do', 'mail', 'wife');
INSERT INTO inserter (doc, ftime, tag1, tag2, tag3) VALUES ('~/mail/456.msg', 45674345, 'work', 'mail', 'hobby');
INSERT INTO inserter (doc, ftime, tag1, tag2, tag3) VALUES ('~/mail/645.msg', 45743445, 'pic', 'cat', 'to-do');

-- фейковое представление для установки принадлежности тега к документу
CREATE VIEW docinserter AS SELECT null as path, null as ftime, null as tag;

-- триггер для добавления документов
CREATE TRIGGER tr_docinsert
INSTEAD OF INSERT ON docinserter FOR EACH ROW
BEGIN
INSERT INTO topic (path, ftime) SELECT NEW.path, NEW.ftime WHERE NOT EXISTS
    (SELECT 1 FROM topic WHERE path = NEW.path);

INSERT INTO tags (title) SELECT NEW.tag WHERE NEW.tag NOT NULL AND NOT EXISTS
    (SELECT 1 FROM tags WHERE title = NEW.tag);

INSERT INTO topic_tags SELECT topic.id, tags.id FROM tags JOIN topic ON 
    (NEW.tag NOT NULL AND topic.path = NEW.path and tags.title = NEW.tag);
END;


INSERT INTO docinserter (path, ftime, tag) VALUES ('~/mail/645.msg', 45743445, 'to-do');
INSERT INTO docinserter (path, ftime, tag) VALUES ('~/mail/645.msg', 45743445, 'work');
INSERT INTO docinserter (path, ftime, tag) VALUES ('~/mail/645.msg', 45743445, 'sql');

-- получаем список всех кошечек
SELECT topic.path, tags.title FROM (
    tags JOIN topic_tags ON tags.title like 'cat%' and tags.id = topic_tags.tags_id
	)    JOIN topic ON (topic.id = topic_tags.topic_id);

SELECT topic.path, tags.title FROM (
    tags JOIN topic_tags ON tags.title in ('wife','mail') and tags.id = topic_tags.tags_id
	)    JOIN topic ON (topic.id = topic_tags.topic_id);

-- собственно сам поиск сразу по всем тегам (условие "И")
SELECT topic.path FROM (
    tags JOIN topic_tags ON tags.title in ('to-do','home','wife') and tags.id = topic_tags.tags_id
	)    JOIN topic ON (topic.id = topic_tags.topic_id) group by topic.id having count (*) = 3;

SELECT *
FROM topic_tags 
JOIN topic ON topic_tags.topic_id = topic.id    [and topic.a=b ....]
JOIN tags ON topic_tags.tags_id = tags.tags_Id  [and tags.c=d ....]