Имеется некая таблица, в которой регистрируются определенные события (например: OPER -"Сделки с заказчиками").
Таблица связана со справочниками, каждый из которых в свою очередь может быть тоже связан с другими справочниками (например: CUSTOMERS - "Справочник заказчиков" связан с TOWNS - "Справочником городов")
Связи между таблицами, таким образом, образуют дерево. Инными словами БД имеет классическую структуру.
На всякий случай скажу, что ВСЕ ключи связывающие таблицу со справочниками и справочники с другими справочниками - суррогатные, одного типа, имена которых четко следуют единому правилу.

Нужно построить запрос к таблице, такой, чтобы записи результата содержали ВСЕ поля таблицы, ВСЕ поля из соответствующих справочников, ВСЕ поля справочников, связанных со справочниками и т.д.
Инными словами, хочется получить из трех таблиц OPER , CUSTOMERS , TOWNS , - таблицу с полями:
OPER.ID, OPER.DATE, OPER.CUSTOMERS_ID, CUSTOMERS.ID, CUSTOMERS.NAME, CUSTOMERS.TOWNS_ID, TOWNS.ID, TOWNS.NAME
Делается такая таблица при помощи LEFT OUTER JOIN .

Чего хотелось бы...
Описать структуру такой БД каким-то образом (DDL, XML, просто объектная древовидная модель на языке ппрограммирования, или ещё каким-то способом). Далее, дать это описание "скушать" какой-то программе, которая бы построила на его основе строку, содержащую SELECT со всеми необходимыми полями и LEFT OUTER JOIN -ами.
Естественно, такая программа должна также генерировать алиасы для каждого из полей, так как названия полей в различных таблицах могут повторяться.

Писать самому такую программу мне страшно. Или мне лишь кажется, что это не просто?
Может быть кто-то знает подобные разработки. Я понимаю, что вопросы сформулированы достаточно размыто, но может быть у кого-нибудь будут какие-то оригинальные мысли по этому поводу?

Valentin Kotelnitski
Почему связи образуют дерево?

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

Valentin Kotelnitski
А если, например, присутсвуют циклические
ссылки (для справочников-деревьев)?

Вполне могут присутствовать. Разве это создает какие-то дополнительные проблемы?

Valentin Kotelnitski
Зачем тебе запрос с абсолютно всеми полями. Какую практическую цель ты
преследуешь?

Цель практическая. GUI-клиент отображает таблицу операций, которая связана со многими справочниками. Пользователь хочет видеть сразу не только реквезиты самой операции, но и реквезиты заказчика и то, в каком городе заказчик находится. Или, например, отсортировать операции по городу заказчика или по региону, которые суть находятся не в основной таблице, а в справочниках.
Для такой таблицы предполагаю сделать диалог задающий её структуру, чтобы пользователь мог выбрать из всей массы предлагаемых полей лишь те (и в том порядке), которые в данный момент ему необходимы. Будет ещё, конечно, фильтр записей и диалог выбора полей для сортировки, но это уже детали к делу не относящиеся. Для меня сейчас важно понять, каким образом построить VIEW для такой, "увешанной" справочниками таблицы.

Может быть интересно, почему я затеял весь этот сыр-бор с автоматизацией, вместо того, чтобы просто написать требуемый VIEW руками? Справочников может быть очень много, полей в них - тоже не мало. Да и самих оперативных таблиц - не одна. Вобщем, как я себе представляю, при необходимости добавить/удалить/изменить поле или справочник. Я погрязну в SELECT -ах состоящих возможно из сотни строк. Можно пользоваться, конечно, каким-то средством визуального проектирования, но мне ведь нужно работать с запросом из программы, а выуживть глазами поля из сгенерированного SELECT -а представляется мне неоправданно трудоемким.

Valentin Kotelnitski
Описать структуру - ты же вроде как бы запроектировал уже базу на SQL?

Ничего я ещё не запроектировал. Размышляю...

Valentin Kotelnitski
Про конструктор запросов - почему ты считаешь, что его можно написать только
для твоей базы, а нельзя писать в общем случае?
(ты так формулируешь вопрос, что кажется, что такой запрос можно написать
только и исключительно для твоей базы).


Нет, Вам показалось :)
Я бы очень не хотел решения, которое основано на уникальных особенностях какой-то конкретной БД.

Valentin Kotelnitski
И опять же, зачем тебе ВСЕ поля?


Не мне, а пользователю)))

Valentin Kotelnitski
Может, тебе нужен графический конструктор запросов из нескольких таблиц, как
в некоторых средах RAD?


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

Valentin Kotelnitski
Определись с тем, что ты конкретно хочешь получить, если ты действительно
собираешься что-то писать, или тебя интересует лишь теория?

Собираюсь писать... Теория интересует лишь в той степени, в которой она способна помочь в практике.

Только сходил ребенку молока купить, а здесь уже баталии начались:))
Чайк у , и отвечу подробно.

Valentin Kotelnitski
Надо хорошо подумать, какой набор полей нужен пользователю и написать
запрос.


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

Bely
Ну что сказать...
Все мне известные попытки написания таких программ - ни к чему хорошему не привели.

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

Самое трудное в программировании - это не составить запрос (или 10 запросов), а ПОНЯТЬ что надо показать пользователю. Написать запрос с JOIN-ами для всех таблиц - ну максимум пару дней. А вот понять что именно надо пользователю для ЭФФЕКТИВНОЙ работы - тут приходится голову ломать всем.

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

Проблема именно в том, что мне трудно писать, а затем сопровождать кучу отдельных запросов. Ну не выношу я черной работы... Я хочу (хотеть я имею право?!), чтобы запросы генерировались програмно, автоматически, благо есть чистая (классически построенная БД).
P.S. Возможно, мне и потребуется писать специфические запросы для каких-то хитрых желаний пользователя, но это будет либо отдельная программа, либо отдельный модуль отчентов. Пока мне не хотелось бы думать ни о первом, ни о втором. Сейчас речь идет лишь о Create, read, update and delete ( CRUD ) приложении, работающем день ото дня в скучном, стандартном режиме, а не пульте управления полетами, где требуется то химсостав батареи проанализировать, то орбиту подправить :)

Bely
Но обычно выдача всей информации в одной таблице сводится к простыне из 30-40 полей, с которой потом нельзя нормально работать.

Пользователь увидит простыню из 30-40 полей, только если отметит все их для показа. По умолчанию будут показаны лишь наиболее необходимые поля. И поверьте, я последний человек, который хочет показывать пользователю малоинтересные данные.

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

Если честно, я создал подобный генератор некоторое время назад. В отличие от того, который я хочу сейчас он "сгребал" все неключевые логические поля каждой таблицы в некую строку и записывал в единое физическое поле, которое парсилось на лету перед построением модели для GUI. Все прекрасно работало. Но далее, с этой штукой произошли две вещи.

Во-первых. Нашелся человек, который сказал: Слушай, мне нравится твоя программа, как средство ввода и изменения даннных CRUD, но твои жалкие потуги сделать нормальную систему отчетов не идут ни в какое сравнение с нашими репорт-генераторами, графо-построителями, эвристическими анализаторами данных и прочими уважаемыми инструментами, для которых у нас есть все - лицензии, специалисты, время. Твои репорты нам по барабану, дай нам доступ к твоей БД и мы сами получим всё, что нам нужно и в том виде, в котором это необходимо.
- Ага, приехали, подумал я... База данных-то с хитрецой... Я имею ввиду этот длинный VARCHAR , в котором были собраны все поля.
Весь мощный инструментарий того дяди вмиг встал и все спецы развели руками и ушли пить пиво. И правильно сделали.
В нашем мире нужно быть максимально переносимым и совместимым, чтобы уметь договариваться и сотрудничать с другими людьми.

Во-вторых , через какое-то время после написания этой "чудо-программы", я попытался переосмыслить, как она работает. И не смог. Сложно, длинно и непонятно. Т.е. сам-же не смог разобраться со своими-же исходниками. Разозлился я (думаю, понятно на кого:-), сжег все рукописи и на какое-то время отошел от дел.

Кажется, я пишу генератор...
Мне хотелось бы услышать вашу критику. Может быть действительно, зря все?...

ЦЕЛЬ:

Единый механизм построения SELECT/INSERT/UPDATE/DELETE для всех таблиц.

ТРУДНОСТИ РЕАЛИЗАЦИИ:

1. Необходима генерация алиасов, для каждого из полей объединения.
2. Необходимо каким-то образом из имени алиаса получить имя исходной таблицы и поля (парсинг алиаса)
3. Необходимо отсортировать список таблиц в порядке увеличеня ссылок на них. Самые простые таблицы - это те, у которых вообще нет связи с другими, таким таблицам место вверху. Далее идут таблицы, которые связаны со справочниками, которые сами по себе не связаны с другими таблицами, и т.д. На данном этапе мне не совсем понятно, как это сделать, поэтому, для начала, я скормлю генератору уже отсортированный вручную список таблиц.

ИСХОДНЫЕ ПОСЫЛКИ:

1. PRIMARY KEY - суррогатный ключ
2. Название поля для PRIMARY KEY - ID
3. Название поля для FOREIGN KEY - {внешняя_таблица__ID} Например: CUSTOMERS__ID


СТРАТЕГИЯ:

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


РЕАЛИЗАЦИЯ:

Схема действий, которую язык не поворачивается назвать алгоритмом примерно следующая.
1. Анализируем описание таблицы.
2 Если нет ссылок на справочники, пропускаем, потому что во VIEW в этом случае просто нет необходимости.
3. Если есть ссылки на справочники, строим VIEW с соответствующими LEFT OUTER JOIN-ами.
4 Переходим к следующей таблице.


P.S. Возможно, даже скорее всего, я изложил идеи не очень ясно. На этом этапе у меня самого еще нет полного понимания.

Bely , спасибо за ценные (действительно ценные) замечания!
Bely korda2. Необходимо каким-то образом из имени алиаса получить имя исходной таблицы и поля (парсинг алиаса)
А зачем?
Запрос строит программа, а значит результатом ее работы может стать не только текст запроса, но и таблица соответствия полей и алиасов.

Я вполне согласен с Вами, у меня тоже была такая мысль, просто подумал, что на фоне общей сложности, не засорять программу таблицей соответствия полей и алиасов, а сначала получить результаты запроса и уж затем разбираться с тем, что получено, в том числе и метаданными (алиасами).
Пока что я оставлю этот аспект для размышлений чуть на более продвинутой стадии понимания сути задачи.

Bely korda3. Название поля для FOREIGN KEY - {внешняя_таблица__ID} Например: CUSTOMERS__IDполе можно называть как угодно, если для построения FK использовать системные таблицы. В них можно натий по каким полям связаны таблицы. Иначе непонятно как быть с несколькими полями в одной таблице, которые будут ссылаться на другую таблицу.
Например: в таблице организации будет три поля ссылки на таблицу адресов: адрес фактический, адрес почтовый, адрес юридический.


Спасибо, за ценное замечание! Этот аспект я совершенно упустил из вида. Видимо имя FOREIGN KEY должно содержать ещё и поряковый номер связи. Что-то вроде: CUSTOMERS__2ID.

BelyКак отличить таблицы справочники от основных таблиц?


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

Bely
3) Самый непонятный вопрос - какие названия полей показывать пользователю? Не будем же мы пользователю показывать MAIN_POST_ADDRESS_ID вместо "Основной почтовый адрес". Взять названия полей неоткуда.


Представьте, я тоже думал на эту тему! Вот что я предполагаю сделать.
Приложение в целом имеет свои текстовые файлы единого типа для поддержки ВСЕХ имеющихся в нем строк на различных языках, например:

и т.д.

В описании поля нужно будет отвести параметр для ссылки на файл messages_XX.txt
что-то типа того:

P.S. Кстати, в документации по поводу column label, он же алиас, написано:
Gets the designated column's suggested title for use in printouts and displays.
Однако, column label поддерживает только ASCII - это раз и как быть со всякими там пробелами и прочим в именах алиасов не ясно - это два. Ну и одновременная поддержка различных языков тоже не понятно как может быть выполнена - это три.

Bely
как разбираться со ссылками на самого себя?

Отличный вопрос, который я тоже упустил из вида!
Может быть имя VIEW и алиас должны содержать порядковый номер ссылки?
view: WORKERS_12, алиас поля: WORKERS_12_AGE

Bely
5) Как различить случаи MASTER DETAIL от ссылки на другой объект?
MASTER DETAIL:
таблица "Организации" (ID, NAME)
таблица "Телефоны" (ID, ORG_ID, PHONE )

Ссылка на объект:
таблица "Организации" (ID, NAME, PHONE_ID)
таблица "Телефоны" (ID, PHONE)

Я хочу уйти от представления документов на экране в виде шапка(header), а под ней строки(details).
(Если будет интересно, расскажу почему). Если пользователь просматривает справочник организаций, а у каждой из организаций есть список телефонов отделов, то это не значит, что вверху я напишу НОВОПРЕСНЕСКИЙ СТРОИТЕЛЬНО-МОНТАЖНЫЙ КОМБИНАТ, а внизу будет таблица с телефонами. Вместо этого, пользователь кликнет на какую-то там кнопку и ему покажется полнофункциональный справочник телефонов, но в котором доступны лишь телефоны просматриваемой организации, с соответствующими Добавить/Изменить/Удалить.

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

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

Об отображаемых пользователю текстах(именах) полей.

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

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

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