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

Уважаемые господа!

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

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

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

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

Итак, определена следующая иерархия данных:

• данные о структуре базовых таблиц;
• данные о структуре конгломерата;
• собственные данные конгломерата;
• конгломерат - массив табличной структуры.

Дерево таблиц конгломерата

Определим понятие дерева таблиц конгломерата. Рассмотрим связи между таблицами следующим образом: каждой таблице конгломерата поставим в соответствие главную таблицу, по отношению к которой она является подчиненной. Исключение представляет единственная таблица, которой в конгломерате не поставлено в соответствие главной таблицы; назовем ее Root. Каждой записи главной таблицы должно соответствовать не более одной записи подчиненных таблиц из состава конгломерата. Рассмотрим условия выполнения данного правила:

• Подчиненная таблица имеет потенциальный ключ, имеющий в своем составе внешний ключ по отношению к главной таблице. Для каждого поля из состава этого потенциального ключа, за исключением внешнего, определим значение (параметр), которому должны соответствовать все значения указанных полей записей подчиненной таблицы конгломерата. Таким образом, каждой записи главной таблицы будет соответствовать не более одной записи подчиненной таблицы, ограниченной набором параметров. Классифицируя типы связей таблиц конгломерата, подчиненным таблицам поставим в соответствие тип связи Single, корневой таблице - Root.
• Главная таблица имеет потенциальный ключ, имеющий в своем составе внешний ключ по отношению к подчиненной таблице. Данный тип (Multi) характерен для справочных таблиц и подчиненных таблиц, для которых главная таблица в конгломерате имеет потенциальный ключ, содержащий в своем составе внешний ключ по отношению к подчиненной таблице (в контексте структуры конгломерата).

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

Механизм обновления данных

Рассмотрим массив информации о данных обновления следующей структуры:

• тип обновления;
• physical name or alias database;
• имя таблицы;
• имя и значение поля первичного ключа;
• массив имен и значений полей, соответствующих набору параметров, определяющих описанные выше значения потенциального ключа, использованного деревом таблиц конгломерата;
• имя и модифицированное значение обновляемого поля.

Собственно, технология обновления данных состоит в следующем:

• выполнение действия, модифицирующего конгломерат: редактирование ячейки, вставка / удаление строки конгломерата;
• подготовка данных структуры обновления;
• поддержка бизнес-правил; реализация поддержки сводится, главным образом, к генерации структур обновления, которые соответствуют обновлениям, производимым бизнес-правилами.
• составление SQL-выражения и выполнение соответствующего запроса обновления;
• передача данных структуры обновления построенным конгломератам.

Тип обновления:

Update

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

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

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

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

Итак, указанная ячейка не пуста.

Структура обновления может не содержать массива параметров, указанных в составе данных структуры обновления.

Выполним конструирование SQL-выражения:

• инструкция SET содержит имя и значение поля, соответствующее значению модифицированной ячейки;
• инструкция WHERE содержит имя и значение поля первичного ключа.

В соответствии с построенным SQL-выражением выполним запрос.

Выполним передачу данных структуры обновления построенным конгломератам.

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

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

Если вхождение базовой таблицы в состав дерева конгломерата имеет тип Single или Root, просмотр строк может быть прекращен, иначе (тип связи - Multi) запись может входить в состав данных неоднократно и необходим полный просмотр строк.

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

Заметим, что операции обновления не требуют обновления на уровне множества. Иначе говоря, операция обновления происходит не на уровне столбцов, а на уровне строк.

Единственное замечание о сортировке: данные о структуре конгломерата могут содержать данные о составе полей инструкции ORDER BY, осуществляя сортировку на уровне клиента.

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

Здесь требуется рассмотреть механизм этой работы:

1. Событие обновления анализируется, составляется структура обновления.

2. Структура обновления передается процедуре рассылки структуры обновления с указанием следующих параметров:

-P1 = TRUE локальная рассылка обновления; сервер СУБД не получает запросов, остальные приложения не получают сообщений обновления. Эта ситуация имеет место:

2.1. при обработке сообщений обновления, посланных другими приложениями;

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

Итак, обрабатывается первичное сообщение; распознается случай обновления внешних ключей. Для данного типа обновления генерируются дополнительные сообщения (P1 = TRUE):

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

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

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

Далее, P1 = FALSE обновление произошло в собственном приложении и требует рассылки обновления СУБД и другим приложениям. Для реализации бизнес-правил структура обновления передается пользовательским библиотекам, которые, в свою очередь, рекурсивно рассылают собственные структуры обновления.

-P2 = TRUE сообщение обновления рассылается пользовательским библиотекам реализации бизнес-правил. Этот параметр задействован для управления рассылкой сообщений, генерируемых самими библиотеками, и создателями самих библиотек управляется;

-P3 = TRUE параметр используется ядром обновления для регистрации окончания рекурсивного круга вызовов.

Insert

Ячейка, соответствующая столбцу первичного поля и строке модифицированной ячейки пуста.

Проблема, связанная с получением значения первичного ключа, будет обсуждена ниже. Для определенности будем считать, что это значение может быть получено до вставки. Иначе, если это значение получено после вставки, тип обновления должен быть изменен на Update.

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

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

Дополним данный набор параметром, содержащим имя поля и значение первичного ключа модифицированной таблицы.

Дополним данный набор параметром, содержащим имя поля и значение модифицированной ячейки.

Выполним конструирование SQL-выражения: инструкции INTO / VALUES должны содержать все имена и значения, определенные составом полученного набора.

Рассмотрим случай, когда массив параметров содержит в своем составе, кроме внешнего ключа по отношению к главной таблице в контексте конгломерата, внешние ключи, ссылающиеся на другие таблицы. Обновление в этом случае аналогично разобранному выше случаю модификации внешнего ключа для типа обновления Update.

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

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

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

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

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

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

Delete

Операция удаления требует включения в состав структуры обновления только имя первичного поля и его значения для данной записи. Рассмотрим удаление строки конгломерата. Для запросов рассматриваемого типа удаление строки однозначно требует удаления записи таблицы Root.

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

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

Итак, для таблицы Root структура обновления содержит имя первичного поля и его значение для данной записи. Конструктор SQL-выражения составит инструкцию WHERE, состоящую из этих значений. Для таблиц, подчиненных (подчиненность на основе описаний внешних ключей) таблице Root, инструкция WHERE должна содержать имя внешнего ключа и значение первичного поля удаляемой записи таблицы Root, т.е. значение из предыдущего набора структуры обновления. В случае ограничений RESTRICTED внешнего ключа необходим запрос о возможности каскадного удаления (отсутствии соответствующих записей), описанного выше, использующий то же значение из предыдущего набора структуры обновления.

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

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

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

Удаление же коснется, очевидно, таблиц всех уровней подчиненности.

Основной вывод таков: для удаления в таблице уровня n+1 необходимы значения всех первичных (опять в рамках упрощений) ключей удаляемых записей таблицы уровня n, так или иначе - будет ли проводиться удаление на уровне СУБД или нет, это необходимое условие составления данных структур обновления, иначе говоря, необходимое условие обновления на уровне строк.

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

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

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

Механизм извлечения данных из базовых таблиц

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

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

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

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

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

Собственно, этот способ извлечения последовательными запросами представляет собой результат выполнения реляционных выражений, осуществленных на уровне приложения.

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

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

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

В случае, если часть таблиц конгломерата не относятся к базе Root, конструируется SQL-выражение, на основе выполнения которого извлекается часть данных, соответствующая другой базе. Результат выполнения этого запроса может быть включен в конструируемый конгломерат механизмом второго способа извлечения.

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

Работа с автоинкрементальным типом поля

Лучшим примером, по моему мнению, служит работа с полем типа "счетчик" MS Access в силу отсутствия триггеров, которые могли бы каким-либо образом идентифицировать вставленную запись - кстати, идентификация триггером может быть поддержана на уровне пользовательских библиотек любой модификацией описанного ниже способа.

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

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

Каждой таблице поставим в соответствие поле из состава таблицы, для которого при вставке записи конструктор выражения INSERT будет расширять инструкцию VALUES, включая в нее имя этого поля и значение идентификатора приложения. Следующий запрос SELECT теперь в состоянии получить необходимое значение первичного ключа по идентификатору приложения. Третий запрос UPDATE обновит значение поля, содержащего идентификатор, необходимым или пустым значением.

Данные о данных СУБД

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

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

Новое поколение Database Engine

С моей точки зрения, представленная идеология обновления может быть использована при создании машин типа BDE нового поколения.

Данная статья описывает решение следующей задачи:

Объект типа TDataSet нового BDE, составив запрос обновления, должен определить область данных, которую следует обновить, отправив этот запрос СУБД; установить возможность обновления на клиентской стороне по данным запроса обновления и возможно, прийти к выводу о необходимости дополнительных запросов для обновления рассматриваемой области данных. О проделанной работе указанный объект оповещает другие объекты своего типа, как в рамках собственного приложения, так и приложений, работающих на других машинах.