225
Для организации связи между таблицами в подчиненной таблице используются следующие свойства, указывающие:
□ MasterSource — источник данных главной таблицы;
□ indexName — текущий индекс подчиненной таблицы;
□ indexFieldNames — поле или поля связи текущего индекса подчиненной таблицы;
□ MasterFields — поле или поля связи индекса главной таблицы.
Работа со связанными таблицами имеет определенные особенности.
□ При изменении (редактировании) поля связи может нарушиться связь между записями двух таблиц. Поэтому при редактировании поля связи записи главной таблицы нужно соответственно изменять и значения поля связи всех подчиненных записей.
□ При удалении записи главной таблицы нужно удалять и соответствующие ей записи в подчиненной таблице (каскадное удаление).
□ При добавлении записи в подчиненную таблицу значение поля связи формируется автоматически по значению поля связи главной таблицы.
Ограничения по изменению полей связи и каскадному удалению записей могут быть наложены на таблицы при их создании, например, в среде программы Database Desktop, или реализовываться программно. Напомним, что эти ограничения ссылочной целостности относятся к так называемым бизнес-правилам — правилам управления БД и поддержания ее в целостном и непротиворечивом состоянии.
Пример приложения
В качестве примера работы со связанными таблицами рассмотрим приложение, предназначенное для автоматизации складского учета.
При организации складского учета используются две таблицы формата Paradox: store для хранения информации о товарах и Cards для хранения карточек товара, в которой отмечается движение (приход и расход) каждого товара. Структура таблиц показана в табл. 7.1 и 7.2. В названия полей включены префиксы s и с (по первым буквам названий таблиц). Такое обозначение помогает при установлении связи между таблицами — из названия поля сразу видно, к какой таблице оно принадлежит.
Таблица 7.1. Структура таблицы store
Кл ючспос
Имя поля Тип Размер ■■"■■»*-"=■««» Примечание
поле
S_Code + * Уникальный код товара. Используется
для связи с подчиненной таблицей
226
Часть II. Технологии доступа к данным
Между таблицами устанавливается связь "главный-подчиненный", при которой таблица store склада является главной, а таблица Cards движения товара — подчиненной (рис. 7.11). Для организации связи в качестве поля связи главной таблицы берется автоинкрементное поле s_Code уникального кода товара. По этому полю построен ключ, значение которого автоматически формируется при добавлении новой записи и в пределах таблицы является уникальным. В подчиненной таблице полем связи (внешним ключом) является целочисленное поле c_Code, по которому построен индекс.
Приложение для работы со складом включает главную форму fmstore (рис. 7.12) и форму fminput ввода данных о новом товаре.
В верхней части главной формы выводится информация о состоянии склада, в нижней части — сведения о движении товара. При выборе в таблице склада записи о товаре в таблице движения товара автоматически отображаются только записи, соответствующие движению именно этого товара. Для наглядности в наборы данных включены все поля таблиц, которые отображаются в компонентах DBGrid. При этом названия заголовков столбцов совпадают с названиями полей.
Глава 7. Навигационный доступ к данным с помощью BDE
227
Модификация данных таблиц с помощью компонентов DBGrid запрещена, для этого их свойства AutoEdit установлены в значение False. Для модификации таблиц используются кнопки Button, а также отдельная форма fminput. Обработчики событий нажатия кнопок Добавить (btnNew) и Удалить (btnDelete) добавляют записи о новом товаре в таблицу склада и удаляют записи о товаре.
При нажатии кнопки btnNew выводится в модальном режиме форма fminput (рис. 7.13), содержащая четыре элемента DBEdit, которые связаны с полями названия, единицы измерения, цены товара и примечания таблицы store. Связь устанавливается через источник данных dsStore, расположенный в главной форме fmstore. Чтобы такая связь стала возможной, в модуле uinput
228
Часть II. Технологии доступа к данным
формы ввода выполнена ссылка на модуль uStore главной формы. В свою очередь, в модуле главной формы есть ссылка на форму ввода. |
Перед вызовом формы ввода данных о новом товаре в таблицу склада добавляется новая запись, и компоненты-редакторы DBEdit этой формы содержат значения полей (первоначально пустые) новой записи. В процессе ввода пользователь может утвердить ввод, нажав кнопку ОК, или отменить его, нажав кнопку Отмена. После закрытия модальной формы ввода проверяется, какая кнопка была нажата: если ОК, то сделанные изменения принимаются, в противном случае — нет.
Для удаления записи с данными о товаре следует нажать кнопку btnDelete, после чего выдается запрос на подтверждение операции. В случае подтверждения сначала в цикле удаляются все записи дочерней таблицы с данными о движении этого товара, а затем происходит удаление записи с данными о товаре.
Добавление новой записи в таблицу движения товара выполняется при нажатии кнопки Приход-расход (btnMove). При добавлении к таблице движения новой записи поле кода товара, являющееся полем связи, автоматически заполняется правильным значением из текущей записи таблицы склада. В поле даты с помощью инструкции присваивания заносится текущая дата.
Пользователь должен вводить только приходное количество, поэтому для ввода этой информации специальная форма не создавалась, а используется функция inputQuery, позволяющая ввести строковое значение. На практике обычно требуется ввод большего количества данных и применяется форма, построенная таким же образом, как и форма fminput. Поступление товара (приход) кодируется положительным числом, расход товара — отрицательным числом. После ввода количества товара выполняются преобразование и проверка формата введенного числа. В случае ошибки выдается соответствующее сообщение, и ввод записи отменяется.
После ввода новой записи о движении товара происходит изменение значения поля s_Quantity количества товара в таблице склада.
Для разрыва связи между таблицами используется флажок показывать все записи
(cbMoveAll). По умолчанию он снят, и связь между таблицами существует. После разрыва связи в таблице движения товара отображаются все записи, независимо от положения текущего указателя в таблице склада. При этом блокируется
Глава 7. Навигационный доступ к данным с помощью BDE
229
кнопка btnDelete удаления записей, т. к. при ее нажатии будут удалены все записи о движении товара.
Таким образом, в приложении выполнены следующие действия:
□ организована связь между двумя таблицами по полю связи;
□ реализовано каскадное удаление записей таблиц;
□ запрещено изменение полей связи — пользователь не имеет возможности редактировать их с помощью компонентов DBGrid, а в коде модулей эти поля не затрагиваются.
Ниже приведены коды модулей форм приложения. Установка свойств большинства компонентов выполнена в обработчиках событий создания форм приложения.
// Модуль главной формы unit uStore;
interface
uses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs, Db, DBTables, ExtCtrls, DBCtrls, Grids, DBGrids, StdCtrls, DBCGrids;
type
dsStore TableStore DBGridStore dsCard TableCard DBGridCard Labell Label2 btnMove btnNew btnDelete cbMoveAU |
TfmStore = class(TForm)
TDataSource;
TTable;
TDBGrid;
TDataSource;
TTable;
TDBGrid;
TLabel;
TLabel;
TButton;
TButton;
TButton;
TCheckBox;
procedure FormCreate(Sender: TObject); procedure btnNewClick(Sender: TObject); procedure btnDeleteClick(Sender: TObject) procedure btnMoveClick(Sender: TObject); procedure cbMoveAUClick (Sender: TObject)
private
{ Private declarations }
230
Часть II. Технологии доступа к данным
public
{ Public declarations }
end;
var
fmStore: TfmStore;
implementation
uses ulnput;
{$R *.DFM}
procedure TfmStore.FormCreate(Sender: TObject); begin
dsStore.AutoEdit:= False; dsCard.AutoEdit: = False; TableCard.MasterSource: = dsStore; cbMoveAll.Cheeked:= False; cbMoveAUClick (Sender); end;
procedure TfmStore.btnNewClick(Sender: TObject);
begin
TableStore.Append;
if fmlnput.ShowModal = mrOK
then begin
TableStore.FieldByName('S_Quantify').AsFloat: = 0;
TableStore.Post;
end
else TableStore.Cancel;
end;
procedure TfmStore.btnDeleteClick(Sender: TObject);
var n: longint;
begin
if TableStore.RecordCount = 0 then exit;
if MessageDlg('Удалить запись?', mtConfirmation, [mbOK, mbNo], 0) = mrOK
then begin
// Удаление записей в карточке движения товара (с конца набора данных)
TableCard.Last;
for n:= 1 to TableCard.RecordCount do TableCard.Delete;
// Удаление карточки движения товара
TableStore.Delete;
end;
end;
Глава 7. Навигационный доступ к данным с помощью BDE
231
procedure TfmStore.btnMoveClick(Sender: TObject);
var sMove: string;
nMove: double;
begin
if InputQuery ('Поступление товара +
TableStore.FieldByName('S_Name').AsString,
'Приход-расход', sMove) then begin
// Проверка введенного приходного или расходного количества товара
try
nMove:= StrToFloat(sMove);
except
Beep;
MessageDlg ('Неправильно введен приход-расход: ' + sMove, mtError, [mbOK], Ob-exit; end;
// Добавление новой записи в карточку движения товара
TableCard.Append;
// Поле C_Code заполняется автоматически по полю S_Code
TableCard.FieldByName('C_Move').AsFloat := nMove;
TableCard.FieldByName('C_Date').AsDateTime:= Now;
TableCard.Post;
// Пересчет наличного количества товара
TableStore.Edit;
TableStore.FieldByName('S_Quantify').AsFloat:=
TableStore.FieldByName('S_Quantify').AsFloat + nMove;
TableStore.Post;
end;
end;
procedure TfmStore.cbMoveAllClick(Sender: TObject);
begin
if not cbMoveAll.Checked then begin
TableCard.IndexName:= 'indC_Code';
TableCard.MasterFields:= 'S_Code';
btnDelete.Enabled:= True;
end
else begin
TableCard.IndexName:= '';
TableCard. IndexFieldNames :=''■,
TableCard.MasterFields:= '';
btnDelete.Enabled:= False;
end;
end;
end.
232
Часть II. Технологии доступа к данным
II Модуль формы ввода unit ulnput;
interface
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs, StdCtrls, Mask, DBCtrls;
type
Tfmlnput
dbeName
dbeUnit
dbePrice
dbeNote
Labell
Label2
Label3
Label4
btnOK:
btnCancel:
class(TForm)
TDBEdit;
TDBEdit;
TDBEdit;
TDBEdit;
TLabel;
TLabel;
TLabel;
TLabel; TButton; TButton;
procedure FormCreate(Sender: TObject);
private
{ Private declarations } public
{ Public declarations }
end;
fmlnput: Tfmlnput; implementation uses uStore; {$R *.DFM}
= fmStore.dsStore; = 'S_Name'; = fmStore.dsStore; = ' S_Umt'; = fmStore.dsStore; |
procedure Tfmlnput.FormCreate(Sender: TObject); begin
dbeName.DataSource dbeName.DataField dbeUnit.DataSource dbeUnit.DataField dbePrice.DataSource
Глава 7. Навигационный доступ к данным с помощью BDE
233
dbePrice.DataField dbeNote.DataSource dbeNote.DataField btnOK.ModalResult btnCancel.ModalResult end;
= 'S_Price';
= fmStore.dsStore;
= 'S_Note';
= mrOK;
= mrCancel;
end.
В рассмотренном примере связь между таблицами устанавливалась уже при выполнении приложения. Обычно же таблицы связываются на этапе разработки через Инспектор объектов. При этом для установки свойств indexName и MasterFields удобно использовать специальный Редактор полей связи (Field Link Designer), вызываемый двойным щелчком в области значения свойства MasterFields в окне Инспектора объектов. В списке Available Indexes (Доступные индексы) выбирается индекс подчиненной таблицы, после чего составляющие его поля отображаются в списке Detail Fields (Детальные поля). В этом списке необходимо выбрать поле подчиненной таблицы, а в списке Master Fields (Главное поле) — поле главной таблицы (рис. 7.14). После нажатия кнопки Add выбранные поля связываются между собой, что отображается в списке Joined Fields (Связанные поля), например, так: c_Code -> s_Code. При этом оба поля пропадают из своих списков. Заполнение свойств IndexName и MasterFields происходит после закрытия окна при нажатии кнопки ОК.
} |
Замечание
Перед открытием окна Редактора полей связи необходимо установить значение свойства MasterSource подчиненного набора данных TableCard, которое должно указывать на источник данных ds Store главной таблицы.
234
Часть II. Технологии доступа к данным
Аналогично можно установить связь между несколькими таблицами, например, таблицей приходной накладной и таблицами поставщиков, покупателей и товаров.
Использование механизма транзакций
При работе с несколькими таблицами БД взаимосвязанные изменения периодически вносятся в разные таблицы. Например, в рассмотренном выше примере добавлялись новые записи о приходе или расходе товара в таблицу движения товара и соответствующим образом изменялось количество товара в таблице склада.
При возникновении какой-либо ошибки, связанной с записью нового количества товара, новое значение может быть не занесено в соответствующую запись, в результате чего целостность БД нарушится, и она будет содержать некорректные значения. Такая ситуация возможна, например, в случае многопользовательского доступа к БД при редактировании этой записи другим приложением. Поэтому в случае невозможности изменить информацию о количестве товара должно блокироваться и добавление новой записи о движении товара.
Для поддержания целостности БД используется так называемый механизм транзакций. Транзакция представляет собой последовательность операций, обычно выполняемую для нескольких наборов данных. Транзакция переводит БД из одного целостного состояния в другое. Чтобы транзакция была успешной, в обязательном порядке должны быть выполнены все операции, предусмотренные в ее рамках (принцип "всё или ничего"). В случае возникновения ошибки при выполнении хотя бы одной из операций вся транзакция считается неуспешной, и база данных возвращается в предшествующее транзакции состояние.
Транзакция может быть явной или неявной. Неявная транзакция запускается автоматически при модификации набора данных, например, при выполнении методов Edit, insert, Append и Delete. Неявная транзакция утверждается методом Post закрепления изменений в наборе данных, а отменяется методом Cancel.
Явная транзакция означает, что программист должен самостоятельно организовывать операции изменения наборов данных. Для реализации механизма явных транзакций Delphi предоставляет специальные методы startTransaction, Commit и Rollback компонента DataBase. Метод StartTransaction начинает транзакцию, после него должны располагаться инструкции, составляющие транзакцию. При выполнении операций производится обработка возникающих исключений. Если исключения не возникли, то после выполнения всех операций вызывается метод Commit, подтверждающий транзакцию, и все изменения вступают в силу. При возникновении исключения должен вызываться метод Rollback, котрый отменяет транзакцию и действие всех операций в рамках этой транзакции. Более подробно компонент DataBase будет рассмотрен в главе 14, посвященной работе с удаленными БД. Здесь же мы немного расширим наше приложение по работе со складом, включив в него механизм транзакций путем изменения кода обработчика события нажатия кнопки btnMove, как показано ниже. Для вызова методов, связанных с запуском
Глава 7. Навигационный доступ к данным с помощью BDE
235
ниже. Для вызова методов, связанных с запуском и завершением транзакции, в форме размещен компонент DataBasel.
// Начало транзакции Databasel.StartTransaction; try
TableCard.Append;
TableCard.FieldByName('C_Move').AsFloat:= nMove;
TableCard.FieldByName('C_Date').AsDateTime:= Now;
TableCard.Post;
TableStore.Edit;
TableStore.FieldByName('S_Quantity').AsFloat:=
TableStore.FieldByName('S_Quantity').AsFloat + nMove;
TableStore.Post;
// Транзакция выполнена успешно
// Утвердить изменения
Databasel.Commit;
except
// Транзакция не выполнена
// Отказаться от изменений
Databasel.Rollback;
end;
В приведенном примере механизм транзакций применяется к связанным таблицам, что в общем случае не обязательно. В одну транзакцию могут быть объединены операции, выполняемые и над отдельными таблицами.
При использовании реляционного способа доступа к данным с помощью инструкций SQL также можно явно управлять транзакциями. Эти возможности рассматриваются в главе 8.
Глава 8
Реляционный доступ к данным с помощью BDE
Реляционный способ доступа к данным основан на операциях с группами записей. Для задания операций используются средства языка структурированных запросов SQL (Structured Query Language), поэтому реляционный способ доступа называют также ^(^-ориентированным. Для его реализации в приложениях Delphi при использовании механизма BDE в качестве набора данных должны применяться такие компоненты, как Query или storedProc, позволяющие выполнить SQL-запрос.
Средства SQL применимы для выполнения операций с локальными и удаленными БД. Наиболее полно преимущества реляционного способа доступа и языка SQL проявляются при работе с удаленными БД. Основным достоинством реляционного способа доступа является небольшая загрузка сети, поскольку передаются только запросы и результат их выполнения.
Применение реляционного способа доступа для локальных БД не дает существенного преимущества, но и в этом случае с помощью SQL-запроса можно:
□ формировать состав полей набора данных при выполнении приложения;
□ включать в набор данных поля и записи из нескольких таблиц;
□ отбирать записи по сложным критериям;
□ сортировать набор данных по любому полю, в том числе неиндексированному;
□ осуществлять поиск данных по частичному совпадению со значениями в поле.
(_____ Замечание
Многие из названных действий неприменимы к набору данных Table.
Для компонента Query реляционный способ доступа реализуется в случае, когда используются только средства SQL-запросов. Если дополнительно применять методы, ориентированные на операции с отдельными записями, например, Next или Edit, то будет реализован навигационный способ доступа со всеми его недостатками.
При работе с удаленными БД можно также использовать навигационный способ доступа, но только для небольших сетей, чтобы не создавать большой загрузки.
Глава 8. Реляционный доступ к данным с помощью BDE
237
Основные сведения о языке SQL
Язык SQL ориентирован на выполнение действий с таблицами БД и данными в этих таблицах, а также некоторых вспомогательных действий. В отличие от процедурных языков программирования, в нем нет инструкций управления вычислительным процессом (циклов, переходов, ветвления) и средств ввода/вывода. Составленную на языке SQL программу также называют SQL-запросом.
Язык SQL обычно интегрируется в другие средства (оболочку) и используется в интерактивном режиме. Так, в системе управления базами данных, имеющей интерактивный интерфейс, пользователь может работать, ничего не зная об языке SQL и независимо от того, какая БД используется: локальная или удаленная. Такие СУБД, как Microsoft Access, Visual FoxPro или Paradox, сами выполняют действия, связанные с программированием запросов на SQL, предлагая пользователю средства визуального построения запросов, например, Query By Example (QBE) — запрос по образцу.
Поскольку SQL не обладает возможностями полнофункционального языка программирования, а ориентирован на доступ к данным, его часто включают в средства разработки программ. Встроен он и в систему Delphi. При этом для работы с командами SQL предлагаются соответствующие средства и компоненты. В Delphi
К таким компонентам ОТНОСЯТСЯ наборы данных Query, SQLQuery И ADOQuery.
Различают два вида SQL-запросов: статический и динамический. Статический SQL-запрос включается в исходный код на этапе разработки и в процессе выполнения приложения не изменяется. Разработчик может изменить SQL-запрос путем использования параметров, если таковые имеются в его тексте.
Код динамического SQL-запроса формируется или изменяется при выполнении приложения. Такие запросы обычно применяются в случае, когда при выполнении запроса требуется учитывать действия пользователя.
(_____ ЗамечаниеJ
Принятая нами классификация не является однозначной. Так, в некоторых источниках SQL-запросы с параметрами также относят к разряду динамических.
Язык SQL имеет несколько стандартов, из которых наиболее популярными среди производителей программных продуктов являются стандарты SQL-89 и SQL-92. Стандарт SQL-92, поддерживаемый Американским национальным институтом стандартов (ANSI, American National Standards Institute) и Международной организацией по стандартизации (ISO, International Standard Organization), также называют стандартом ANSI или ANSI/ISO. Вследствие наличия нескольких стандартов и их различных интерпретаций появилось множество диалектов языка SQL, более или менее отличающихся друг от друга.
В языке SQL можно выделить следующие основные подмножества инструкций:
□ определения данных;
□ обработки данных;
238
Часть II. Технологии доступа к данным
П управления привилегиями (доступом к данным);
□ управления транзакциями.
Рассмотрим основные возможности версии языка SQL, используемой в Delphi. Эта версия несколько отличается от стандарта SQL-92, например, в ней нельзя работать с просмотрами и управлять привилегиями.
Особенности языка SQL, используемого для работы с удаленными БД (промышленными СУБД), будут рассмотрены в следующих главах.
В приложениях Delphi для выполнения инструкций SQL, применяя механизм BDE, можно использовать набор данных Query. Напомним, что текст SQL-запроса является значением свойства sql компонента Query и формируется либо при разработке приложения, либо во время его выполнения. Компонент Query обеспечивает выполнение SQL-запроса и получение соответствующего набора данных. Формирование набора данных выполняется при активизации
компонента Query путем ВЫЗОВа метода Open ИЛИ установкой СВОЙСТВа Active В
значение True. Иногда при отработке SQL-запроса нет необходимости получать набор данных, например, при удалении, вставке или изменении записей. В этом случае предпочтительнее выполнять запрос вызовом метода ExecSQL. При работе в сети вызов этого метода производит требуемую модификацию набора данных, не передавая в вызывающее приложение (компьютер) записи набора данных, что существенно снижает нагрузку на сеть.
Кроме того, набрать и выполнить в интерактивном режиме текст SQL-запроса позволяют инструментальные программы, поставляемые вместе с Delphi, например, Database Desktop, SQL Explorer и SQL Builder. Отметим, что первые две программы вызываются одноименными командами меню Tools и Database соответственно, а визуальный построитель запросов SQL Builder вызывается через контекстное меню компонента Query.
Проверка синтаксиса и отработка запроса, встроенного в приложение (чаще всего с помощью компонента Query), производятся на этапе выполнения приложения. При наличии синтаксических ошибок в тексте SQL-запроса генерируется исключение, интерпретировать которое порой не просто. Для отладки SQL-запросов удобно использовать программы с развитым интерфейсом, например, Database Desktop. После отладки текст запроса вставляется в разрабатываемое приложение. При таком подходе значительно сокращается время создания запросов и существенно уменьшается вероятность появления динамических ошибок.
В качестве результата выполнения SQL-запроса может возвращаться набор данных, который составляют отобранные с его помощью записи. Этот набор данных называют результирующим.
В дальнейшем при описании инструкций языка мы будем опускать несущественные операнды и элементы, используя для обозначения отдельных элементов символы < и > (при программировании не указываются), а необязательные конструкции заключать в квадратные скобки. Для наглядности зарезервированные слова языка SQL будут писаться прописными, а имена — преимущественно строчными буквами. Регистр букв не влияет на интерпретацию инструкций языка. Точка с запятой в конце SQL-инструкций необязательна. Элементы в списках, например, имена полей и таблиц, должны быть разделены запятыми.
Глава 8. Реляционный доступ к данным с помощью BDE
239
Имена таблиц и полей (столбцов) заключаются в апострофы или двойные кавычки, например, "First Name". Если имя не содержит пробелы и другие специальные символы, то апострофы можно не указывать.
В SQL-запросе допускаются комментарии, поясняющие текст программмы. Комментарий ограничивается символами /* и */.
Функции языка
Язык SQL, как и другие языки, предоставляет для использования ряд функций, из которых наиболее употребительны следующие:
□ агрегатные, или статистические, функции:
• avg () (среднее значение);
• мах () (максимальное значение);
• min () (минимальное значение);
• sum () (сумма);
• count () (количество значений);
• count (*) (количество ненулевых значений);
□ функции работы со строками:
• upper (str) (преобразование символов строки str к верхнему регистру);
• lower (str) (преобразование символов строки str к нижнему регистру);
• trim (str) (удаление пробелов в начале и в конце строки str);
• substring (str from ni то n2) (выделение из строки str под строки, которая включает в себя символы, начиная с номера (позиции) ni и заканчивая номером п2);
• CAST (<Expression> AS <Type>) (приведение выражения Expression К типу Туре);
□ функции декодирования даты и времени:
• extract (<Элемент> from <выражение>) (из выражения, содержащего зна
чение даты или времени, извлекается значение, соответствующее указан
ному элементу); в качестве элемента даты или времени можно указывать
значения: year, month, day, hour, minute или second.