Проектирование БД: наследование

Нормально, т.е. ты про каскад джоинов, именно для чего СУБД и писались со всякими там оптимизациями планов запросов, переиспользованием кэшей, оптимизациям выборок по индексам и прочей заумной математикой, отлаживаемой десятилетиями - и вот ты говоришь, что ОРМ от Васяна всё это уделает, работая на соседней машине и дёргая базу простейшими запросами?

Так я тебе больше скажу: если тебе точно известна топология данных и порядок выборок - бери Harbour и пиши простыми сиками, сэкономишь на разборе запросов и тех самых оптимизациях, над которыми трудились столько лет совсем неглупые люди ))

Тупо id, parent_id надо выбрать всех потомков одного id и потомков их потомков, и т.д., одним запросом.

Задача N+1?

Нормально, т.е. ты про каскад джоинов, именно для чего СУБД и писались со всякими там оптимизациями планов запросов

Да, но сколько будет там join'ов, чтобы выбрать всё дерево и как это всё будет работать? Не проще просто по нему пробежаться и распихать всё в какую-нибудь структуру типа хешмапа с хешмапами, что, собственно на SO и советуют. В оракле есть connect by, но он работает так себе, да и это не удивительно, если надо делать n выборок.

Да, рекурсивные запросы. Пишут, что sql сервер могёт, но как он это могёт вопрос, ибо на деле там будет сильно больше чем 10 записей.

Посмотрел твой-же старый топик... Увидел тот-же mybatis. И чем он не торт?

И чем он не торт?

Делали китайцы. Там или пляски с xml или с аннотациями. Ну и никуда не деться от sql. Я не могу просто взять и сляпать на нём какой-нибудь конструктор запросов, который будет мапить в bean. По идее так можно сделать в jdbi, но там интеграции нет со спрингом, да и я уже по js начал угорать.

Посмотрел твой-же старый топик

Так это ты нас тогда имел ввиду?

Неизвестное количество джоинов в запросе == неизвестной структуре данных, или ненормализованным данным. Да, в лоб по имеющейся схеме решается плохо. Но весьма вероятно, что саму схему можно было составить по другому.

По идее так можно сделать в jdbi, но там интеграции нет со спрингом

Т.е. jdbi торт, а интеграция со спрингом никому не нужна: большинство сидит на ОРМ-ах (вычислительные мощности дёшевы), остальным не нужен спринг либо они имеются с sql-запросами вручную. Или сидят на дотнете.

я уже по js начал угорать

Что в нём есть по теме и не трешевого?

Тогда я еще не знал, что тот топик твой ))))

Что в нём есть по теме и не трешевого?

Несмотря на npm помойку и всех этих хипсторов на js можно легко скатывать код в декларативщину. Например я запилил себе DAO в виде микросервиса, который настраивается js структурой с функциями, всё это превращается, например, в вебapi которое принимает запросы с параметрами, верифицирует это всё, делает запрос и выплёвывает json с массивом объектов назад. Вместо sql там структура типа

{
select : {
   fields : {
      alias : "table.field",
      alias2 : "table.field2"
//etc
   },
   tables : {
      "table" : ["field=>fk_table.field"],
      "fk_table" : []
   },
   filters : {
     argvarname : "table.field3 = #var"
   }
}
}

Т.е. jdbi торт

Там и jdbc торт, мне просто совсем влом было разбираться с приведением его типов и хотелось какой-то готовый mapper в бины. Но произошло что произошло, потому что на js вообще не нужны никакие маперы, бины и прочее, лепить что надо из этого говна удобнее, правда надо было изобретать свой тайпчекер, чтобы ничего не растекалось.

большинство сидит на ОРМ-ах (вычислительные мощности дёшевы)

Ну да, но вот у нас начисление делалось часа 3. Потом я сделал на жс по схеме выбрал, посчитал, отдал. Начало делаться минут 5. Это только 1/10 от того что считать надо, 30 часов расчётов - это не приемлемо хотя бы просто потому, что неудобно масштабировать.

Да, в лоб по имеющейся схеме решается плохо. Но весьма вероятно, что саму схему можно было составить по другому.

Ну вот, я про что и говорю. Все это решается какими-то обходными путями. Но находятся люди, которые обходят эти деревья, да еще и на пхп в орм. Некоторые на триггеры вешают перестройку каких-нибудь бинарных деревьев, под которые делают таблицы. Хранить, чтобы потом где-то выбрать сразу всё и поюзать и засунуть обратно - ну ок, что-то делать с этим на sql - беда.

Ну вот, я про что и говорю.

Так и задача, как по мне, не совсем тривиальная для БД - с лёту и не придумаю когда такое надо.

А возняк предлагал SQL с шаблонизатором

https://wozniak.ca/blog/2014/08/03/What-ORMs-have-taught-me-just-learn-SQL/

Например в moodle была структура типа факультет, кафедра, специальность, дисциплина. Да, это у нас было такое наполнение, по факту иерархия была общая под похожую структуру в любой вложенности. Оно до сих пор там так сидит и перестраивает всё это дерево каждый раз, когда туда что-то добавляют.

Ты предлагаешь выбрать всё, что есть и скроллить?

Чё бы вдруг всё? ScrollableResults для того и нужны, чтобы не загружать огромные массивы данных в жабку. Если случился упрт, то setFetchSize выставить и радоваться.

// уверен, что в реальной жизни всё не так просто, но проверять лень.

Сделай вьюху

Ну то такое. Создание вью поддерживают примерно никакие ORM, а значит, надо отключать штатный механизм генерации таблиц (я знаю, знаю, не бейте) и тащить с собой liquibase или что-то подобное. А я ленивая жопа.

Ну то такое. Создание вью поддерживают примерно никакие ORM

Так в базе сделай.

надо отключать штатный механизм генерации таблиц

Так говоришь, как будто каждый день куда-то переезжаешь вместе со всей базой.

Чё бы вдруг всё? ScrollableResults для того и нужны, чтобы не загружать огромные массивы данных в жабку.

Ну да, но всё равно придётся же пройти весь массив данных, которых, там например, 10м записей, вместо того, чтобы быстро выбрать 10к.

каждый день куда-то переезжаешь вместе со всей базой.

На текущем проекте штук 20 независимых инсталляций сервера, многие клиенты требуют размещать весь софт у них, причём зачастую на каких-то упоротых машинах с 2 гб рам и древним, но очень корпоративным дебианом, где никакими контейнерами и не пахнет. Так что автогенерация БД мне очень пригодилась.

придётся же пройти весь массив данных

Кажись, я недопонял задачу. У нас есть большой набор ID, по которому нужно зафетчить сущностей в жабу, но не все сразу? Или что?

общие данные в общих, дополнительные соответственно, более того для доступа к дополнительным данным по идее не нужны роли, а то получается роль самому себе

В рбд должны быть только данные в 3-ей нормальной форме

На самом деле очень спорное утверждение, особенно когда упор в производительность, но на уровне ТС-а, ты прав.

У нас есть большой набор ID, по которому нужно зафетчить сущностей в жабу, но не все сразу? Или что?

Есть таблица с первичным ключом и 10М записей. Надо выбрать 10к произвольных записей из этой таблицы. Выбираемые id могут быть любыми с бд они не как не связаны.

На самом деле очень спорное утверждение, особенно когда упор в производительность

Ну так я и пишу дальше

(возможна небольшая избыточность, без фанатизма)

Как я понял, ему нужно сделать WHERE IN.
LINQ для EF Core будет какой-то такой:

context.Books.Where(b => booksIds.Contains(b.Id)).ToListAsync();

WHERE IN

where in (select id from ...) если точнее. Ну или хз как еще. 10к номеров в оракл, например не влазят в список in. Может можно как-нибудь через коллекцию, но всё равно надо передавать как-то огроменный список.

Тупо id, parent_id надо выбрать всех потомков одного id и потомков их потомков, и т.д., одним запросом.

Добавь ещё full_parent_id и твой запрос будет выглядеть как SELECT * FROM tree WHERE full_parent_id LIKE @full_id + '/%'

full_parent_id - varchar, который будет хранить всю цепочку? Но если там будет 1 и 10, а надо будет всех после 1.

full_parent_id - varchar, который будет хранить всю цепочку? Но если там будет 1 и 10, а надо будет всех после 1.

Да. Между id в full_parent_id разделитель. То есть что-то вроде «1/5/8».

именно для чего СУБД и писались со всякими там оптимизациями планов запросов, переиспользованием кэшей, оптимизациям выборок по индексам и прочей заумной математикой, отлаживаемой десятилетиями - и вот ты говоришь, что ОРМ от Васяна всё это уделает, работая на соседней машине и дёргая базу простейшими запросами?

Есть хоть одна СУБД, которая умеет быстро работать без memcached ? А в идеале, чтобы ещё и JIT денормализация была.

То есть что-то вроде «1/5/8».

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

Он так каждую строчку будет выбирать, потом еще искать что-то в ней.

Там же индекс. Поиск по LIKE «префикс%» происходит практически с той же скоростью, что и по id BETWEEN start AND end.

Там же индекс. Поиск по LIKE «префикс%» происходит практически с той же скоростью, что и по id BETWEEN start AND end.

Но как такое может быть возможно? Ладно у id какой-нибудь btree, а у varchar будет что за мегаиндекс?

Ладно у id какой-нибудь btree, а у varchar будет что за мегаиндекс?

Тот же самый btree. Какая разница, что сравнивать, числа или строки?

Тот же самый btree.

А что в этот btree складывают в качестве ключей?

Какая разница, что сравнивать, числа или строки?

Ну если хеш строки, то без разницы. Но надо-то не строку, а её кусок. В mysql были fulltext индексы, но хз как это всё будет работать. Проще запилить еще одну таблицу с id, child_id, куда тащить всёх потомков, копируя каждый раз кучу записей, что, впрочем придётся делать хоть так хоть так. Ведь если там этих id будет очень большой хвост - это приличный оверхед. Короче РБД не умеет в ссылки кроме связей между таблицами. Можно просто пойди дальше и попробовать реализовать граф с его обходом на sql.

А что в этот btree складывают в качестве ключей?

Строки и складывают.

Ну если хеш строки, то без разницы. Но надо-то не строку, а её кусок.

Из этого куска получается диапазон. Условно, если надо найти «аб%», то будет BETWEEN «aб» AND «ав». Для любых значений, для которых существует операция «меньше» можно построить btree и из btree можно получить диапазон за O(log N).

В mysql были fulltext индексы

Не надо путать fulltext и varchar. Всё-таки уровней в иерархии не будет пару миллионов. Индекс по varchar есть во всех СУБД вплоть до dBase.

Проще запилить еще одну таблицу с id, child_id, куда тащить всёх потомков, копируя каждый раз кучу записей, что, впрочем придётся делать хоть так хоть так.

То есть вместо строки в 30 символов ты предлагаешь добавить десяток строк в таблицу?

Можно просто пойди дальше и попробовать реализовать граф с его обходом на sql.

В некоторых случаях считается просто (например, периметр фигуры). В некоторых и без SQL всё сложно (если что-то вроде OSPF реализовать).

Условно, если надо найти «аб%», то будет BETWEEN «aб» AND «ав»

Но, чтобы всё эти аб и ав были, для них надо просчитать хеш и разложить в дерево. И опять же, «20%» это в том числе и «15720» как кто тут понимает наш разделитель и понимает ли. Каджый потомок должен будет хранить свою родословную, как правоверный Еврей, а эта самая родословная может быть очень большая, всё это будет забивать индекс, при изменениях в иерархии будет вообще ад, потому что это таки не родословная, где твоего прадеда никто не будет ни на кого менять. А нам надо будет делать replace каждому потомку, которых n. Херня это всё в общем.

То есть вместо строки в 30 символов ты предлагаешь добавить десяток строк в таблицу?

Не 30, а n символов. Примеры с лабами на 100 записей не рассматриваем.

В некоторых случаях считается просто

Да не надо ничего сложного. Банальная таблица node_src, node_dest. Двусвязный граф без циклических связей. Надо найди всех, до кого можно добраться из ноды x. Тупо список id. Что делать? Костылять регион с id и списком под эту задачу?

Но, чтобы всё эти аб и ав были, для них надо просчитать хеш и разложить в дерево.

Зачем? Достаточно просто сравнивать строки (типа strcmp).

И опять же, «20%» это в том числе и «15720»

Нет. Чтобы был «15720» надо, чтобы строка поиска была «%20%» или хотя бы «%20». «20%» = «все строки, начинающиеся на 20».

А нам надо будет делать replace каждому потомку, которых n. Херня это всё в общем.

Перемещение папки с объектами в другое место иерархии горазд более редкая операция, чем получение списка объектов в папке. И этот replace в sql всё равно будет одной командой UPDATE (заменить префикс ааа на прфекс ббб всем, у кого сейчас префикс ааа).

Всё-таки уровней в иерархии не будет пару миллионов.

Ну я про что и говорю. Что вот, простой пример с иерархией и в общем случае уже ничего нельзя сделать.

«20%» = «все строки, начинающиеся на 20».

Ну так она в середине записи может быть где-нибудь. Не обязательно выборка будет с верхнего уровня. Ну да, это решается разделителем «%/20/%»

в sql всё равно будет одной командой UPDATE

На хорошей таблице такая команда будет выполняться полдня. Хотя в целом костыль не плох, предложу коллеге в продакшон.

Не 30, а n символов. Примеры с лабами на 100 записей не рассматриваем.

Так вместо n символов ты предлагаешь добавить O(n)строк.

Да не надо ничего сложного. Банальная таблица node_src, node_dest. Двусвязный граф. Надо найди всех, до кого можно добраться из ноды x. Тупо список id. Что делать? Костылять регион с id и списком под эту задачу?

Да. Если работаешь с графом, то его надо раскрашивать. То есть для неориентированного графа просто node_src, node_dest, region_id. После этого все, до кого можно добраться из ноды x будет просто

SELECT gr.node_dst FROM gr JOIN gr2 WHERE gr.region_id = gr2.region_id WHERE gr2.node_src = @x

Так вместо n символов ты предлагаешь добавить

А какая разница? Что так плохо, что так, а там хоть будет индекс.

Да. Если работаешь с графом, то его надо раскрашивать. То есть для неориентированного графа просто node_src, node_dest, region_id.

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

Не обязательно выборка будет с верхнего уровня.

В смысле? У нас каждая папка ровно в одной папке. То есть, если у неё путь «1/5/20», то нет смысле писать «%/20/%», так как результат будет идентичен «1/5/20/%», а для «1/5/20/%» работает индекс.

На хорошей таблице такая команда будет выполняться полдня.

Это сколько же записей в папке должно быть, чтобы оно полдня по ним UPDATE делало? 4 миллиарда?

А какая разница? Что так плохо, что так, а там хоть будет индекс.

Я же тебе рассказываю, что по строкам тоже будет индекс. И, в общем случае более эффективный, так как в индексе будет O(N) строк, а не O(N^2) (как в худшем случае для вспомогательной таблицы). И при переносе папки для строк один UPDATE, а для вспомогательной таблицы DELETE + INSERT для гораздо большего количества строк.