Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / SQLite Новый топик    Ответить
 автоматизация связей многие-к-многим хелп!  [new]
pgetwink
Member

Откуда:
Сообщений: 9
доброго времени суток.
начал изучать sql для создания небольшой базы хранения объектов и их тегов (закладок)

собственно структуру придумал такую:

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


в общем то код работает, записи добавляются, но вот сижу ломаю голову как
заполнить таблицу связей тоже автоматически, может кто подскажет что?
ато вечер, голова не бум бум, да и в sql я новичёк совсем ещё.
30 мар 19, 20:02    [21847962]     Ответить | Цитировать Сообщить модератору
 Re: автоматизация связей многие-к-многим хелп!  [new]
Dima T
Member

Откуда:
Сообщений: 13672
INSERT INTO inserter (doc,fmtime,tag1,tag2,tag3) VALUES ('/docs/test1.md', 7346342, 'tag1', 'tag2', 'tag3');

Это нарушение первой нормальной формы, а еще есть вторая и третья нормальные формы, если эти термины ни о чем не говорят, то гугл в помощь, там букв немного, советую для начала изучить теорию реляционных СУБД.
30 мар 19, 20:14    [21847964]     Ответить | Цитировать Сообщить модератору
 Re: автоматизация связей многие-к-многим хелп!  [new]
pgetwink
Member

Откуда:
Сообщений: 9
Dima T
INSERT INTO inserter (doc,fmtime,tag1,tag2,tag3) VALUES ('/docs/test1.md', 7346342, 'tag1', 'tag2', 'tag3');

Это нарушение первой нормальной формы, а еще есть вторая и третья нормальные формы, если эти термины ни о чем не говорят, то гугл в помощь, там букв немного, советую для начала изучить теорию реляционных СУБД.


прочёл честным образом статьи в википедии вплоть до 3й нормальной формы, там моя голова вскипела и я пожалуй позже почитаю.

к такому решению пришлось прийти, поскольку не смог нагуглить ни 1 способа в SQLite заполнять таблицы данных и их связующей таблицы без необходимости запоминания и последующего прописывания ID топиков и соответствующих им тегов.
30 мар 19, 20:41    [21847979]     Ответить | Цитировать Сообщить модератору
 Re: автоматизация связей многие-к-многим хелп!  [new]
Dima T
Member

Откуда:
Сообщений: 13672
pgetwink
прочёл честным образом статьи в википедии вплоть до 3й нормальной формы, там моя голова вскипела и я пожалуй позже почитаю.

Для начала лучше поспать, а потом читать и вникать. В теории РСУБД есть правила, которые надо знать и не надо нарушать, а если нарушишь получишь трудноразрешимые проблемы. Теория работает только на правильно оформленных данных.
30 мар 19, 20:50    [21847981]     Ответить | Цитировать Сообщить модератору
 Re: автоматизация связей многие-к-многим хелп!  [new]
pgetwink
Member

Откуда:
Сообщений: 9
Из упрямства доделал всё же свой вариант сам
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');


так что теперь у меня полностью автоматическое заполнение таблиц тегов, данных и их связей. ура. (удаление связей тоже автоматическое)

осталось только понять как получить список файлов у которых есть несколько тегов...
файлы с 1 тегом получаю так:
-- получаем список всех кошечек
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);

но неужели сложность выборки будет расти по экспоненте при увеличении числа тегов поиска? ведь прийдётся каскадно наращивать запрос как я понимаю, это ж сколько join-ов надо...

вариант
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);

не подходит т.к. даст все файлы с тегами 'wife' или 'mail' а не 'wife' и 'mail'
31 мар 19, 12:30    [21848203]     Ответить | Цитировать Сообщить модератору
 Re: автоматизация связей многие-к-многим хелп!  [new]
pgetwink
Member

Откуда:
Сообщений: 9
Всё, разобрался, уф.. как всегда сам, чёт никто новичкам не хочет кроме меня помогать, а ведь начало то оно самое и трудное.
спасибо хоть 1 чел вовремя меня поправил насчёт 1 формы, атоб так и пилил дальше.

-- собственно сам поиск сразу по всем тегам (условие "И")
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;
31 мар 19, 16:45    [21848310]     Ответить | Цитировать Сообщить модератору
 Re: автоматизация связей многие-к-многим хелп!  [new]
White Owl
Member

Откуда:
Сообщений: 12370
pgetwink
собственно структуру придумал такую:

Тут все правильно.

pgetwink
-- получаю список файлов с тегами так:
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);
А это сложносочиненная ересь.

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

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

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


pgetwink
собственно этот код вроде работает нормально, но добавлять новые
записи довольно утомительно,
ну так потому и утомительно что ты не понял собственной структуры. Ты точно сам придумал эти три таблицы?

А вообще, добавление записей в эти три таблицы очень простая операция. Надо только понять что это три таблицы и они хоть и связаны, но продолжают оставаться тремя таблицами.
Просто поверь что объекты (topic) и тэги (tags) это соверешенно разные сущности. Добавляешь и изменяешь их по отдельности, а потом связываешь их (topic_tag).
1 апр 19, 02:08    [21848600]     Ответить | Цитировать Сообщить модератору
 Re: автоматизация связей многие-к-многим хелп!  [new]
White Owl
Member

Откуда:
Сообщений: 12370
pgetwink
Dima T
INSERT INTO inserter (doc,fmtime,tag1,tag2,tag3) VALUES ('/docs/test1.md', 7346342, 'tag1', 'tag2', 'tag3');

Это нарушение первой нормальной формы, а еще есть вторая и третья нормальные формы, если эти термины ни о чем не говорят, то гугл в помощь, там букв немного, советую для начала изучить теорию реляционных СУБД.


прочёл честным образом статьи в википедии вплоть до 3й нормальной формы, там моя голова вскипела и я пожалуй позже почитаю.
Почитай. Теория это полезно.
А чтобы мозги не вскипали - попробуй сделать это все не в базе данных, а на бумажке. Возьми тетрадку, карандаш - на трех отдельных листочках нарисуй свои три таблицы. А теперь запиши в них карандашиком свои данные. Подумай как это сделать удобнее всего при помощи карандаша. Потом переноси это в компьютер.
1 апр 19, 02:13    [21848601]     Ответить | Цитировать Сообщить модератору
 Re: автоматизация связей многие-к-многим хелп!  [new]
Dima T
Member

Откуда:
Сообщений: 13672
pgetwink
Всё, разобрался, уф.. как всегда сам, чёт никто новичкам не хочет кроме меня помогать, а ведь начало то оно самое и трудное.

В этом форуме мало кто бывает, к тому же в выходные.

Как понимаю то, что ты изобретаешь называется модель EAV. Тут обсуждали эту тему.
1 апр 19, 07:59    [21848641]     Ответить | Цитировать Сообщить модератору
 Re: автоматизация связей многие-к-многим хелп!  [new]
pgetwink
Member

Откуда:
Сообщений: 9
White Owl
pgetwink
собственно этот код вроде работает нормально, но добавлять новые
записи довольно утомительно,
ну так потому и утомительно что ты не понял собственной структуры. Ты точно сам придумал эти три таблицы?

А вообще, добавление записей в эти три таблицы очень простая операция. Надо только понять что это три таблицы и они хоть и связаны, но продолжают оставаться тремя таблицами.
Просто поверь что объекты (topic) и тэги (tags) это соверешенно разные сущности. Добавляешь и изменяешь их по отдельности, а потом связываешь их (topic_tag).


Я прекрасно понимаю что пишу, но немного нехватает глубоких знаний по синтаксису и принципам выполнения команд (пока что).

кстати, ваш код для выборки по тегам и файлам не работает в SQLite3 к сожалению (вероятно в более развитых базах данных он и сработает, но SQLite не понимает что от неё хотят) с ёё точки зрения нет никакой связи между topic, tags и topic_tags поэтому и пришлось писать 2 JOIN

но спасибо за наводку, обязательно почитаю про модель EAV
1 апр 19, 21:32    [21849450]     Ответить | Цитировать Сообщить модератору
 Re: автоматизация связей многие-к-многим хелп!  [new]
pgetwink
Member

Откуда:
Сообщений: 9
кстати, под "сложностью добавления" я имел в виду "сложность добавления 1 файла из командной строки в 1 команду с несколькими тегами" в приложении у меня изначально так записи и добавлялись сначала в topic потом в tags затем связи устанавливались.
1 апр 19, 21:37    [21849461]     Ответить | Цитировать Сообщить модератору
 Re: автоматизация связей многие-к-многим хелп!  [new]
White Owl
Member

Откуда:
Сообщений: 12370
pgetwink
кстати, ваш код для выборки по тегам и файлам не работает в SQLite3 к сожалению (вероятно в более развитых базах данных он и сработает, но SQLite не понимает что от неё хотят) с ёё точки зрения нет никакой связи между topic, tags и topic_tags поэтому и пришлось писать 2 JOIN
Не работает, потому что я ошибся. У тебя три таблицы значит и join должен быть двойной - это верно. А вот вложенные выборки тебе не нужны.

В общем, вот тебе базовый запрос который можно фильтровать по любой из трех таблиц:
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 ....]
4 апр 19, 16:12    [21852914]     Ответить | Цитировать Сообщить модератору
 Re: автоматизация связей многие-к-многим хелп!  [new]
pgetwink
Member

Откуда:
Сообщений: 9
White Owl
pgetwink
кстати, ваш код для выборки по тегам и файлам не работает в SQLite3 к сожалению (вероятно в более развитых базах данных он и сработает, но SQLite не понимает что от неё хотят) с ёё точки зрения нет никакой связи между topic, tags и topic_tags поэтому и пришлось писать 2 JOIN
Не работает, потому что я ошибся. У тебя три таблицы значит и join должен быть двойной - это верно. А вот вложенные выборки тебе не нужны.

В общем, вот тебе базовый запрос который можно фильтровать по любой из трех таблиц:
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 ....]


Спасибо, но мне такой вариант не подходит, так как могут быть документы без тегов, как и теги могут быть "висячими" (оставшиеся без документов в результате удаления).

ps. Я времени даром не терял, у меня уже полностью оптимизированы запросы, выборки строятся исключительно по ID, база данных наполняется и всё работает максимально шустро, даже выборки по условию И по нескольким тегам (самый сложный запрос)
4 апр 19, 21:54    [21853286]     Ответить | Цитировать Сообщить модератору
 Re: автоматизация связей многие-к-многим хелп!  [new]
pgetwink
Member

Откуда:
Сообщений: 9
Стоп, я кажется понял что вы имели в виду, поправьте если неправ:
таблица связей самая большая поэтому выборка должна изначально делаться из неё потому что каждый последующий JOIN как бы это сказать... "усложняет сложность" в зависимости от размера таблицы (читал, что вроде как там умножение), другими словами умножить базу на салую таблицу а потом базу на произведение 1 и 2? так чтоли? надо будет мне потестировать как напишу парсер документов и наполню базу...

Спасибо ещё раз, если это так то вы меня очень выручили.
4 апр 19, 22:10    [21853297]     Ответить | Цитировать Сообщить модератору
 Re: автоматизация связей многие-к-многим хелп!  [new]
pgetwink
Member

Откуда:
Сообщений: 9
сорри, незнаю как исправить очепятку, "салую" читать как "малую"
4 апр 19, 22:12    [21853300]     Ответить | Цитировать Сообщить модератору
 Re: автоматизация связей многие-к-многим хелп!  [new]
White Owl
Member

Откуда:
Сообщений: 12370
pgetwink
Спасибо, но мне такой вариант не подходит, так как могут быть документы без тегов, как и теги могут быть "висячими" (оставшиеся без документов в результате удаления).

Документ без тегов это значит строка в topic не имеющая ни одной строки в topic_tag. И тоже самое для тегов.
А значит и искать такие topic и tag проще простого:
select *
from topic
where id not in (select topic_id from topic_tag)

Достаточно задать вопрос "что я ищу?"
И не стесняйся рисовать таблички и работать с ними на бумаге - это ооочень помогает понять что ты делаешь.
7 апр 19, 01:48    [21854918]     Ответить | Цитировать Сообщить модератору
 Re: автоматизация связей многие-к-многим хелп!  [new]
White Owl
Member

Откуда:
Сообщений: 12370
pgetwink
Стоп, я кажется понял что вы имели в виду, поправьте если неправ:
таблица связей самая большая поэтому выборка должна изначально делаться из неё потому что каждый последующий JOIN как бы это сказать... "усложняет сложность" в зависимости от размера таблицы (читал, что вроде как там умножение), другими словами умножить базу на салую таблицу а потом базу на произведение 1 и 2? так чтоли? надо будет мне потестировать как напишу парсер документов и наполню базу...
И да и нет. Ты правильно понял что join добавляет еще одно произведение, но я показал порядок topic_tag join topic join tags просто для иллюстрации множественных связей. На самом деле ты можешь сделать и цепочку
from topic
join topic_tags on topic.id=topic_tags.topic_id
join tags on tags.id=topic_tags.tag_id
И даже вообще без явных join'ов:
from topic, tags, topic_tags
where topic.id=topic_tag.topic_id and tags.id=topic_tags.tag_id

Функционально это все равнозначно и равноценно. Просто выбираешь то что тебе кажется более читабельным и легче для понимания. Но для сервера это все одно и то-же.

На самом деле очень сложно сказать какой порядок таблиц в join'ах будет более эффективным. По скорости может быть лучше брать маленькую таблицу сначала и навешивать на нее большую, может быть наоборот. Все зависит от данных и насколько эти данные покрыты индексами.
7 апр 19, 02:02    [21854919]     Ответить | Цитировать Сообщить модератору
Все форумы / SQLite Ответить