Добрый вечер.
Не смог ничего найти приемлемого для себя в справочниках и гугле, вот решил задать вопрос.
Допустим, в БД хранится документ с двумя табличными частями - в трех таблицах соответственно. Задача состоит в запросе списка документов с содержимым всех табличных частей.
В аналоги могу лишь привести 1С : если сделать подобный запрос с итогами, с помощью нескольких выборок на разных уровнях иерархии можно обойти как шапки документов, так и табличные части. Здесь главным критерием является то, что запрос на сервер оформляется один. Код выглядит как обход дерева.
Ничего похожего без извращений я не могу придумать по отношению к Firebird. Была бы одна табличная часть - можно было бы просто сделать левое соединение табличной части к шапке по ключу документа, таким образом каждая строка табличной части сопровождалась бы комплектом реквизитов из шапки документа. Конечно, объем передаваемых данных умножается, но это не так критично (если не говорить о сотне реквизитов в шапке и табличной части).
Передо мной стоит задача работы с документами, содержащими несколько табличных частей. Решать в лоб - в цикле по каждому документу отдельными запросами к каждой табличной части - дилетантизм. Слишком медленно, даже с учетом предварительной подготовки запросов и работы через параметры.
Возможно написать хранимую процедуру, в которой уже циклами обработать все данные согласно структуре, но как в таком случае возвращать данные на клиента? В ячейке ведь нельзя передать коллекцию, как в Oracle? Составлять протокол ключ-значение и передавать иерархию, развернутую в список? Выглядит извращением и болью при необходимости изменить потом что-либо в структуре документа.
Подскажите, пожалуйста, как возможно решить подобную задачу одним запросом. Наверняка должны быть типовые решения.
Буду благодарен за указание любого источника, спасибо.

superspy2019,

единственное разумное, что могу себе представить - для моего примера использовать три селекта с условием отбора документов (а-ля "Дата документа = ...") - к таблице с шапками и таблицам с табличными частями. На клиенте строить словарь шапок и по ключу городить деревянную структуру.

Интересно, как такую задачу решают профессионалы

m7m,

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

Пример можно привести, например, такой:
Таблица Заказ: ORDER_ID, ORDER_DATE, ORDER_NUMBER, DELIVERY_DATE, DELIVERY_PLACE
Таблица Товары: ORDER_ID, ITEM_ID, GTIN, DESCRIPTION, NETPRICE, TAXRATE
Таблица ЛогистическиеОкна: ORDER_ID, LOG_ID, DATETIME_FROM, DATETIME_TO, COMMENT
Таблица ДопИнфо: ORDER_ID, KEY, VALUE

Соответственно, чтобы запросить все заказы с датой доставки = DELIVERY_DATE, я бы сделал 4 селекта и строил дерево на клиенте. При этом, если документы (да не обязательно документы - справочники, регистры, что угодно в принципе с иерархической структурой) могут изменяться параллельно разными пользователями, read_committed транзакция уже не подойдет, придется либо собирать мусор на версиях, либо вовсе терпеть блокировки для чтения. Но это отдельная история.

При этом, если понадобится сделать фильтр а-ля "заказы с логистическим окном с 2:00 - 4:00, в которых присутствует номенклатура из группы весовых сыров", я в первую очередь растеряюсь и буду долго сидеть кипеть в попытке что-нибудь придумать.

Вариант с использованием ORM возможно как-то развернуть, чтобы стало понятнее, о чем идет речь?

rdb_dev,

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

rdb_dev,

нет, ну физически конечно можно все, что угодно мапировать в единую таблицу - набор колонок для шапки, колонки для товаров, колонки для окон и т.д. При этом надо учитывать пропорции между количеством строк в таблицах - если заказы имеют от 50 до 300 товаров, то лог.окна обычно приходят в количестве 1-2 строки в 1 из 2000 заказов.
В теории, если склеить кучу left join в одну таблицу и идти разными циклами по разным столбцам, а первый попавшийся NULL считать окончанием данных в "подтаблице" (назовем приклеенные столбцы из подчиненной таблицы так), то что-то похожее на правду возможно получить. Однако, это справедливо только для простейших случаев.
При формате "табличные части в элементах табличных частей" (на практике встретится проще простого) - кроме как декартово произведение ничего придумтаь невозможно. И конечный набор данных станет, во-первых, дико большим, даже, я бы сказал, заметным в трафике, во-вторых, непригодным для чистого обхода и требующим свой алгоритм обработки.

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

Симонов Денис,

обычно, если БД разработана для задачи предварительной обработки (сведения из разных источников, приведения к унифицированной структуре, пересчета или подбора значений) и выгрузки полученной выборки в основную учетную систему - единственное решение читать готовый к выгрузке объем данных и обрабатывать его согласно алгоритму синхронизации. Я же не говорю, что требуется вручную открывать каждый элемент, проверять его и нажимать на кнопку "Сохранить и отправить". Задачи обычно немножко более сложные и автоматизированные.

KreatorXXI,

Уважаемый, union работает только с потоками данных одинаковой структуры. Мой пост совершенно о другом

Симонов Денис,

Не могу согласиться в том, что мои слова являются чепухой. Проецирование дерева в одну таблицу приблизительно тем методом, что я описал - левым соединением строк каждой табличной части с таблицей заголовков по ключу и объединением в одну выборку по столбцам, - отличается от метода "1 селект на каждую табличную часть" очень сильно. Не столько количеством запросов, сколько стратегией управления транзакциями. Мы же с вами говорим не про студенческую курсовую

[quot m7m]superspy2019m7m,
Беспокойство по поводу " read_committed транзакция уже не подойдет, придется либо собирать мусор на версиях, либо вовсе терпеть блокировки для чтения" не понял

Понять это можно, имея представление о различных уровнях изоляций и соответствующих стратегиях управления ими. Простите, я не знаю ваш уровень компетенции.
Если говорить образно - read_committed транзакция прочитает любую закоммиченную версию - не важно, какой транзакцией и в какой момент времени это произошло. Если разными селектами выбирать части одного объекта - теоретически возможна ситуация, когда пользователь параллельно с вами выполняет сохранение изменений в этом объекте, один ваш селект прочитал запись для старого объекта, второй селект прочитал запись уже нового объекта. В итоге вы получаете чепуху. Это же азы многопоточности.
Чтобы этого избежать, нужен либо более строгий уровень изоляции, либо стратегия блокировок. И уже при попытке прочитать объект получать либо всегда данные одной версии (не важно, что уже закоммичено обновление данных этого объекта) - здесь напарываемся на непрогнозируемые проседания производительности СУБД при сборке мусора, либо всегда получаем исключение при наличии более новой версии - суть есть блокировки, необходимость повторного запроса

m7mИ если очень хочется то строй дерево, однако я-бы просто показал все это в 4-х гридках и не парился.


Вообще, основные вещи, которые мне приходится разрабатывать - десериализовать структуру, хранить ее в БД, сериализовать в другом формате. Сильно упрощенно и образно. Про UI я ничего не говорю. Мне нужен лишь оптимальный способ получения максимально подходящей к сериализации сразу после выборки структуры данных - как можно меньшим количеством запросов, как можно меньшим количеством клиентского кода для обработки, как можно более быстрым способом управления транзакциями.
К сожалению, все это сразу на пальцах объяснить не получается

Dimitry Sibiryakovесли ты выкинешь требование "как можно меньшим количеством запросов",
ибо от правильно составленных запросов серверу не плохеет.


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

Основной критерий - это 1 запрос. Все остальное - объем кода и удобство разработки, - вторичны.

m7m,

В целом, практически вся работа с СУБД происходит через сеть - вполне возможно, что кроссдоменно с удаленных площадок. Среднее время на запрос - 5-500 мс. Как советует архитектор выше - делать запросы в цикле, - уже накодили и теперь приходится все просто переписывать. Благо я гибче и инструментов у меня больше. И если задачу синхронизации с БД десятками тысяч insert / update / update or insert я эффективно решаю объединением в execute block скрипты (уменьшая количество запросов в сотник раз), то вот запрос структуры из разных таблиц сделан, на мой взгляд, не очень оптимально. В этом и хотелось бы разобраться глубже.

ёёёёё,

Уважаемый, прочитайте ветку от начала до конца, чтобы не повторять уже разобранные мной вопросы

Dimitry SibiryakovА теперь, внимание, вопрос: какая часть этого времени тратится на prepare (отдельно),
execute (отдельно) и fetch (отдельно).


Вы уже продемонстрировали свой уровень, что еще хотите?
99% этого времени клиент в принципе не тратит, так как весь код асинхронный, однако в разрезе на запрос время уходит на ожидание TCP запроса. Я проводил замеры на небольших update, получил разницу между подготовленными с параметрами и текстовыми со случайными значениями запросами на грани статистической погрешности. Все одиночные запросы я параметризую - не из-за безопасности (нет у меня задач вытаскивать работу с SQL наружу, все запросы программируются), а просто потому что удобнее. Вы промахиваетесь с этой темой.

m7mДерево да чтобы в разных узлах была разная структура данных и все это одним запросом

В общем, максимум из темы смог выжать запрос



Пришлось вводить в каждую табличную часть поле POSITION, в котором строки нумеруются от 1 до N для каждого родителя.

Результат

ITEM_ORDERITEM_POSWIN_ORDERWIN_POS111112NULLNULL13NULLNULLNULLNULL21NULLNULL22
обходится двумя параллельными вложенными циклами, по одному на табличную часть - в моем случае ITEMS и WINDOWS. Значение NULL игнорируется. Соответственно, объект на клиенте заполняется в один проход по выборке, запрос один, данных относительно немного, алгоритм прост. В этом примере используется две колонки с ключом, т.к. задачу усложнил и принял возможность пустой табличной части. Можно избавиться от лишних колонок, введя внешний запрос с еще одним join'ом, но надо учитывать производительность.

Ничего более умного не могу придумать

Dimitry SibiryakovЧтобы Вы таки продемонстрировали свой.


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

H5N1,

Абсолютно верно - выше я этот вопрос озвучил. Либо snapshot с блокированием на запись, либо собирать мусор на версиях. Сложно на самом деле, поэтому вариант нескольких параллельных селектов мне не нравится

m7mИ теперь если удалится строка из табличной части надо будет заново проводить нумерацию или как?

Да, выходит так. При наличии потребности изменять хранящиеся объекты - придется при таком подходе городить хранимые процедуры для пересчета каждой такой таблицы.
Вообще, я уже ближе подошел к решению в виде разворачивания структуры в таблицу (на примере выше), но до конца пока не получается сделать, как мне бы хотелось. Либо получаю нужные мне NULL и на сдачу лишние столбцы, либо столбец родителя один, но связанные строки дублируются. Как смогу собрать задачу - напишу просьбу помочь составить запрос. Сейчас не смогу точный ТЗ описать.

Dimitry SibiryakovУ тебя чисто читающая транзакция. О каких блокировках и мусоре ты говоришь?


Да, я выразился совершенно неправильно, перепутав местами условия. Snapshot транзакция использует версионность и не дает возможность прочитать обновленные после ее старта данные. Именно использование snapshot на чтение вынуждает СУБД хранить версии записей и потом их чистить. Read_committed readonly транзакция не вынуждает собирать версии, но потребует заблокировать нужные записи, иначе нельзя гарантировать консистентность, о чем абсолютно точно сказал H5N1. Причем Firebird (знаю только про 2.5) по умолчанию вопреки канонам использует вариант no_record_version, что приводит к двухфазной блокировке и дает исключение при попытке прочитать неконсистентный набор.

Про все это я уже говорил выше, здесь вынужден повторяться

m7mХранимая процедура с использованием явных курсоров позволит отказаться от доп.поля POSITION

Я, к сожалению, не умею работать с курсорами и не владею матчастью, никак не могу это прокомментировать.

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

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

Dimitry Sibiryakov,
Вы не правы по всем трем пунктам. Но так как читать вы не умеете и в предметную область вникать не можете, в чем-то вас убеждать я не буду

kdvчестное слово, большей ахинеи про версионность в ФБ я нигде не читал.

какие еще, ..., каноны? Где "по умолчанию использует no_rec_version"? Какая еще "двухфазная блокировка"?
Вы не просто бредите, вы в другой вселенной находитесь.
kdvгражданин, я вас уверяю - вы несёте ахинею, и при этом считаете её истиной. Это ВЫ читать не умеете, о чем и заявляете с непомерным пафосом.

А чтобы не подвергнуться аналогичным обвинениям с вашей стороны, на всякий случай сообщу, что ibase.ru это мой сайт, и я и есть kdv, который написал тучу статей про версионность, сборку мусора, транзакции, и так далее.
И если вы собираетесь на эту тему что-то еще брякнуть, то рекомендую ПЕРЕЧИТАТЬ эти самые статьи.

Добрый день.
Если я в чем-то заблуждаюсь, то охотно выслушаю истину в последней инстанции. Я в общем-то, за этим на форум и обратился.
И злиться совершенно не обязательно (если это, конечно, не ваш режим по-умолчанию), я к этому никого не принуждаю. Сведения про работу с транзакциями черпаю отсюда: http://www.ibase.ru/files/firebird/langref25rus/index.html#transaction.

Цитирую:
NO RECORD_VERSION (значение по умолчанию) является в некотором роде механизмом двухфазной блокировки. В этом случае транзакция не может прочитать любую запись, которая была изменена параллельной активной (неподтвержденной) транзакцией.
Если указана стратегия разрешения блокировок NO WAIT, то будет немедленно выдано соответствующее исключение.

Если вы пришли сказать что-то конструктивное, интересно будет услышать комментарий в контексте ваших сообщений - в чем конкретно я ошибся.

Dimitry SibiryakovА для получения исключений неопытный (криворукий) разработчик должен явно установить
read_committed + nowait, что уже совсем не умолчание.

Вы совершенно забыли о том, что в режиме No_Record_Version + Wait исключение вызывается, если параллельная транзакция не была отменена по истечению таймаута. Или не забыли, а явно пытаетесь вводить в заблуждение. Не знаю.

vvvaitНужно что-то вроде этого?
Насколько я понял, здесь дерево с тремя уровнями иерархии? Вот если у каждого узла иерархии будет не одно название, а десяток различных реквизитов, да еще и разных по составу на разных уровнях - тогда будет то самое.

vvvaitно профессионалы для передачи таких структур используют XML или JSON
Тоже про это думал, но как-то диковато выглядит... Одной строкой не передать, придется теги как-то размазывать, а на клиенте собирать и десериализовывать.

vvvaitно профессионалы для передачи таких структур используют XML или JSON

Если конечно эта фраза была написана в отношении формата получаемых от БД данных.
Могу придумать только собирать схему вложенными for select и суспендить из хранимой процедуры. Очень интересно посмотреть на производительность в деле. Очень сомневаюсь