Borland Delphi и расширения ADO

Алексей Федоров, Наталия Елманова

Настоящая статья посвящена некоторым полезным возможностям, предоставляемым технологией Microsoft ActiveX Data Objects (ADO). Как известно, ADO — это часть архитектуры универсального доступа к данным (Microsoft Universal Data Access). Подробнее об объектах ADO и о том, каким образом можно использовать ADO в средствах разработки, можно прочитать в одной из статей цикла «Введение в СУБД», опубликованной в июнь-ском номере журнала.

Отметим, однако, что, помимо собственно доступа к данным (что возможно и с помощью других универсальных механизмов), библиотека ADO содержит несколько расширений, весьма полезных в применении, но на данный момент практически не освещенных в литературе. В нашей статье мы постараемся восполнить этот пробел, рассмотрев такие расширения, а именно: ADO Extension for DDL and Security (ADOX) Jet and Replication Objects (JRO) и ADO Multidimensional (ADO MD)).

Примеры для данной статьи, иллюстрирующие возможности расширений ADO, созданы с помощью Delphi 5 Enterprise. Однако аналогичные примеры можно реализовать и с помощью любого другого средства разработки, позволяющего создавать контроллеры автоматизации (Visual Basic, Delphi 3 или 4, C++Builder 3-5, Visual C++ и др.).

Применение ADO Extension for DDL and Security

Введение в ADOX

ADO Extension for DDL and Security (ADOX) применяется для решения различных задач, недоступных с помощью обычных объектов ADO. Например, используя объекты ADOX, можно извлекать метаданные из баз данных и, следовательно, переносить структуру данных из одной базы данных в другую (в том числе и иного типа). Вторая возможность, предоставляемая этим расширением, — манипулирование сведениями о безопасности. Например, с помощью ADOX можно получать информацию о пользователях базы данных и группах пользователей, а также создавать новых пользователей и группы. ADOX расширяет объектную модель ADO десятью новыми объектами, которые можно использовать как отдельно, так и вместе с другими объектами ADO, в частности можно применять объект ADO Connection для соединения с источником данных и извлекать метаданные из него.

Прежде чем углубляться в детали объектов ADOX, поговорим о том, что такое метаданные. В общем случае метаданные представляют собой описания объектов базы данных (таблиц, полей, индексов, ключей, представлений, хранимых процедур и прочих объектов). В подавляющем большинстве современных СУБД метаданные определяются с помощью языка SQL (Structured Query Language). До появления ADOX единственным программным способом извлечения метаданных из источников данных с помощью ADO был метод OpenSchema объекта ADO Connection. Для создания новых объектов в базе данных применялся язык Data Definition Language (DDL) — подмножество языка SQL, а также объект ADO Command.

ADOX предоставляет более универсальный способ манипуляции метаданными, не требующий знания SQL для того, чтобы получить структуру базы данных или даже создать новые объекты. Обратите внимание на то, что ADOX работает далеко не со всеми базами данных — его функциональность ограничена Microsoft Access и Microsoft SQL Server, а также несколькими другими СУБД. Подробности о поддержке ADOX различными СУБД можно найти на Web-сервере корпорации Microsoft: http://www.microsoft.com/data/.

После этого краткого введения вернемся к ADOX. Как мы уже отмечали выше, ADOX обладает собственной объектной моделью, состоящей из 10 объектов, перечисленных в табл. 1. Эти объекты образуют иерархию, представленную на рис. 1.

Иерархия объектов ADOX начинается с объекта Catalog. Этот объект содержит коллекции таблиц, представлений, процедур, пользователей и групп и может быть использован для открытия существующей базы данных (с помощью объекта ADO Connection), а также для создания новой. В версии ADO 2.1 можно создавать только базы данных Jet (то есть Microsoft Access — файлы с расширением .mdb), но, возможно, в последующих версиях список таких баз данных будет расширен. Открыть же можно и базы данных других типов.

Имея объект Catalog, мы можем работать с таблицами, процедурами и представлениями. Например, просматривая коллекцию Tables, можно узнать, какие таблицы имеются в базе данных, а также получить более детальные сведения о таблицах, изучив коллекции Columns, Indexes и Keys объекта Table. Изучая свойства объектов базы данных,

можно получить сведения о метаданных и, в частности, сохранить их в отдельном файле или куда-либо перенести. Используя коллекции Users и Groups, мы можем манипулировать правилами доступа к данным, создавая отдельных пользователей или группы пользователей базы данных. Отметим, что база данных в этом случае должна быть защищенной (secured): в случае Microsoft Access нам следует включить ссылку на базу данных System.mdw, ответственную за хранение сведений о правилах доступа к данным, в содержимое строки, указывающей на источник данных (connection string).

Еще одна интересная особенность ADOX заключается в том, что с помощью этого расширения можно создавать базы данных и объекты внутри них «из ничего». Например, можно создавать базы данных Access, добавлять таблицы, поля, записи, индексы, ключи, а затем добавлять в созданную таким образом базу данных и сами данные (вручную или с помощью кода). Это бывает полезно в тех ситуациях, когда нужно некоторым образом организовать «сырые» данные. В общем случае для создания новой базы данных следует создать объект Catalog и применить метод Add коллекций Tables, Columns, Keys и Indexes для добавления в него объектов базы данных.

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

Создание приложения для просмотра метаданных

Итак, рассмотрим, как можно применить объекты ADOX в Delphi. В качестве иллюстрации создадим приложение, с помощью которого пользователь сможет:

просматривать метаданные в виде «дерева» объектов;
изучать свойства объектов базы данных;
получать исходный текст хранимых процедур и представлений.

Для выполнения этой задачи создадим новый проект и поместим на главную форму будущего приложения следующие компоненты: TMainMenu, TTreeView, TMemo и TStatusBar. В первую очередь нам следует включить ссылку на библиотеку типов ADOX (она находится в файле MSADOX.DLL), поскольку ADOX не поддерживается в Delphi 5 на уровне компонентов. Для этого следует выбрать Project | Import Type Library главного меню среды разработки Delphi, а затем из списка доступных библиотек типов выбрать Microsoft ADO Ext. 2.1 for DDL and Security. Чтобы избежать конфликтов с именами уже имеющихся классов Delphi (например, TTable), следует переименовать классы ADOX, заменив имена на что-нибудь типа TADOXxxx. Затем нужно убрать отметку из опции Generate Component Wrapper — в данном случае нам нужен только файл *.pas, содержащий интерфейс для доступа к объектам ADOX, а затем нажать кнопку Create Unit. Это приведет к созданию файла ADOX_TLB.PAS, содержащего интерфейс к библиотеке типов ADOX. Создав этот файл, мы должны сослаться на него, а также на модуль ADODB в предложении Uses главного модуля нашего проекта.

Теперь мы готовы к написанию кода для создания объектов ADOX. Создадим пункт меню File | Open Catalog и в обработчике его события OnClick напишем следующий код:

procedure TForm1.OpenCatalog1Click(Sender: TObject);
   begin
   // Получить имя источника данных (DataSource Name) с помощью стандартной
   // диалоговой панели Microsoft
   DS := PromptDataSource(Application.Handle, '');
   // Если пользователь выбрал источник данных
   If DS <> '' Then
      begin
   // просмотреть метаданные
       BrowseData(DS);
      end;
   end;

Здесь мы использовали метод PromptDataSource, реализованный в модуле ADODB, чтобы вызвать стандартную диалоговую панель Data Link Properties. Если источник данных выбран, то вызывается процедура BrowseData. Назначение этой процедуры — отобразить с помощью компонента TreeView метаданные, извлеченные из базы данных. Текст этой процедуры таков:

procedure TForm1.BrowseData(DataSource: String);
   var
   RootNode   : TTreeNode;
   OneNode    : TTreeNode;
   SubNode    : TTreeNode;
   I             :    Integer;
   OldCursor : TCursor;
   begin
   // Заменить стандартный курсор на песочные часы
   OldCursor := Screen.Cursor;
   Screen.Cursor := crHourglass;
   StatusBar1.Panels[0].Text := 'Extracting metadata, please wait...';
   // Очистить компонент TreeView
   ClearTree;
   // и Memo
   Memo1.Lines.Clear;
   Application.ProcessMessages;
   // Соединиться с источником данных
   Catalog._Set_ActiveConnection(DataSource);
   RootNode := TreeView1.Items.Add(Nil, 'Catalog');
   
   // Добавить таблицы
   OneNode   := TreeView1.Items.AddChild(RootNode, 'Tables');
   For I := 0 to Catalog.Tables.Count-1 do
      begin
       SubNode := TreeView1.Items.AddChild(OneNode, Catalog.Tables[I].Name);
   // Добавить поля, индексы и ключи
       ProceedTables(Catalog.Tables[I], SubNode);
      end;
   
   // Добавить представления
   If CheckViews(Catalog) then
   begin
      OneNode   := TreeView1.Items.AddChild(RootNode,    'Views');
      For I := 0 to Catalog.Views.Count-1 do
       begin
         SubNode := TreeView1.Items.AddChild(OneNode,    Catalog.Views[I].Name);
       end;
   end;
   
   // Добавить процедуры
   OneNode   := TreeView1.Items.AddChild(RootNode, 'Procedures');
   For I := 0 to Catalog.Procedures.Count-1 do
      begin
       SubNode := TreeView1.Items.AddChild(OneNode, Catalog.Procedures[I].Name);
      end;
   
   RootNode.Expand(False);
   // Заменить курсор на стандартный и очистить панель состояния
   Screen.Cursor := OldCursor;
   StatusBar1.Panels[0].Text := '';
   end;

Обратите внимание на то, что в приведенной процедуре имеются три цикла для просмотра коллекций Tables, Views и Procedures объекта Catalog. Каждый найденный объект помещен в определенную ветвь компонента TreeView1. Коллекция Tables содержит один или более объектов Table, свойствами которых являются коллекции Columns, Indexes и Keys, и их также следует просмотреть. Это делается с помощью процедуры ProceedTables:

procedure TForm1.ProceedTables(T: Table; N : TTreeNode);
   var
   I          : Integer;
   SubNode : TTreeNode;
   begin
   // Добавить поля
   If T.Columns.Count > 0 Then
   SubNode := TreeView1.Items.AddChild(N, 'Columns');
   For I := 0 to T.Columns.Count-1 do
      TreeView1.Items.AddChild(SubNode, T.Columns.Item[I].Name);
   // Добавить индексы
   If T.Indexes.Count > 0 Then
   SubNode := TreeView1.Items.AddChild(N, 'Indexes');
   For I := 0 to T.Indexes.Count-1 do
      TreeView1.Items.AddChild(SubNode, T.Indexes.Item[I].Name);
   // Добавить ключи
   If T.Keys.Count > 0 Then
   SubNode := TreeView1.Items.AddChild(N, 'Keys');
   For I := 0 to T.Keys.Count-1 do
      TreeView1.Items.AddChild(SubNode, T.Keys.Item[I].Name);
   end;

И снова код содержит три цикла для просмотра коллекций Columns, Indexes и Keys объекта Table.

Вернемся к процедуре BrowseData. Отметим, что перед циклом просмотра коллекции Views следует выполнить проверку того, доступны ли представления для текущего источника данных:

   If CheckViews(Catalog) then ...

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

   function CheckViews(C : _Catalog) : Boolean;
   var
   I : Integer;
   begin
   try
      I := C.Views.Count;
      CheckViews := True;
   except
      CheckViews := False;
   end;
   end;

Теперь компонент TreeView1 заполнен метаданными. Для получения информации о конкретном объекте следует создать для этого компонента обработчик события OnChange. Вот его текст:

   procedure TForm1.TreeView1Change(Sender: TObject; Node: TTreeNode);
   begin
   If Node.Parent.Parent <> Nil Then
   begin
      Case Node.Parent.Text[1] of
       'C' : ViewColumns(Node.Parent.Parent.Text, Node.Text);
       'I' : ViewIndexes(Node.Parent.Parent.Text, Node.Text);
       'K' : ViewKeys(Node.Parent.Parent.Text, Node.Text);
       'T' : ViewTables(Node.Text);
       'V' : ViewProps(Node.Text);
       'P' : ProcProps(Node.Text);
      end;
   end;
   end;

Как можно видеть, в этом обработчике события вызываются различные процедуры в зависимости от того, на какой из ветвей компонента TreeView1 пользователь щелкнул мышью. Например, процедура ViewTables показывает число объектов внутри выбранной таблицы, а процедуры ViewColumns, ViewIndexes и ViewKeys используются для изучения свойств полей, индексов и ключей.

Объекты Column, Index и Key обладают немалым количеством свойств, и в рамках данной статьи рассмотреть их подробно невозможно, поэтому в табл. 2 приведены их краткие описания.

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

   procedure TForm1.ProcProps(Name : String);
   var
   S          : String;
   Disp      : IDispatch;
   Command : _Command;
   begin
   S := 'PROCEDURE : ' + Catalog.Procedures.Item[Name].Name;
   S := S + ^M^J + 'Created    : ' + 
      VarToStr(Catalog.Procedures.Item[Name].DateCreated);
   S := S + ^M^J + 'Modified   : ' + 
      VarToStr(Catalog.Procedures.Item[Name].DateModified);
   If CmdSupported(Catalog.Procedures.Item[Name]) Then
   begin
      Disp := Catalog.Procedures.Item[Name].Get_Command;
      Command := Disp AS Command;
      S := S + ^M^J^M^J + Command.Get_CommandText;
   end;
   Memo1.Text := S;
   end;

В вышеописанном коде мы использовали тот факт, что член коллекции Procedures в действительности указывает на объект ADO Command. Следовательно, мы можем использовать метод Get_Command для получения интерфейса IDispatch объекта Command и использовать их метод Get_CommandText для получения исходного текста хранимой процедуры.

Теперь мы знаем, как использовать объекты ADOX для извлечения и отображения метаданных. Еще одна возможность ADOX, которая будет кратко рассмотрена ниже, — создание баз данных и объектов внутри них без применения сложных DDL-запросов.

Создание баз данных и их объектов

Первый шаг при создании новой базы данных — создание нового экземпляра объекта Catalog. Это позволяет определить не только тип создаваемой базы данных (с помощью OLE DB-провайдера), но и местоположение файла базы данных. Вот как это можно сделать для базы данных Microsoft Access:

   const
   BaseName =   'c:\data\demo.mdb';
   DS          =   'Provider=Microsoft.Jet.OLEDB.4.0;Data    Source='+BaseName;
   
   var
   Catalog   : TADOXCatalog;
   
   ...
   
   // Создать экземпляр объекта ADOX Catalog
   Catalog   := CoCatalog.Create;
   // Если база данных существует, удалим ее
   If FileExists(BaseName) Then DeleteFile(BaseName);
   // Создадим новый MDB-файл
   Catalog.Create(DS);
   // Укажем активное соединение
   Catalog._Set_ActiveConnection(DS);
   ...

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

Создать новый экземпляр объекта Table.

Создать новый экземпляр объекта Column.

Определить свойства вновь создаваемого поля.

Добавить объект Column в коллекцию Columns объекта Table.

Повторить шаги 3-4 для каждого нового поля.

Добавить объект Table в коллекцию Tables объекта Catalog.

Следующий пример показывает, как можно реализовать эту последовательность действий:

   // ШАГ 1
   // Создать новый экземпляр объекта Table
   Table                := CoTable.Create;
   // Give it the name
   Table.Name      := 'Customers';
   // И определить Catalog, к которому он принадлежит
   Table.ParentCatalog := Catalog;
   // ШАГ 2
   // Создать новый экземпляр объекта Column
   Column := CoColumn.Create;
   With Column do
      begin
       ParentCatalog := Catalog;
   // ШАГ 3
   // Установить свойства
       Name    := 'CustID';
       Type_   := adInteger;
       Properties['Autoincrement'].Value := True;
       Properties['Description'].Value    := 'Customer    ID';
      end;
   // ШАГ 4
   // Добавить поле к коллекции Columns объекта Table
      Table.Columns.Append(Column, 0, 0);
      Column := Nil;
   // ШАГ 5
   // Создать несколько объектов Columns и добавить их к объекту Table
      With Table.Columns do
       begin
         Append('FirstName', adVarWChar, 64);
         Append('LastName',   adVarWChar,    64);
         Append('Phone',          adVarWChar, 64);
         Append('Notes',          adLongVarWChar, 128);
       end;
   // ШАГ 6
   // Добавить объект Table в коллекцию Tables объекта Catalog
   Catalog.Tables.Append(Table);
   Catalog := Nil;

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

   Index := CoIndex.Create;
   With Index do
      begin
       Name := 'LastNameIndex';
       IndexNulls := adIndexNullsDisallow;
       Columns.Append('LastName', adVarWChar, 64);
       Columns['LastName'].SortOrder := adSortAscending;
      end;
      Table.Indexes.Append(Index, EmptyParam);

Логика этого примера проста: сначала мы создаем экземпляр объекта Index, затем устанавливаем его свойства — имя, способ обработки «пустых» значений, связываем индекс с полем и, наконец, добавляем его в коллекцию Indexes объекта Table. Примерно то же самое следует делать и при создании ключей.

Обсудив объекты ADOX, мы не коснулись объектов User и Group. В настоящее время в текущей версии ADO эти объекты доступны только для Microsoft Access (Microsoft Jet OLE DB Provider), и пока нет никаких сведений о том, что в последующих версиях ADO будет реализована поддержка этих объектов для других типов баз данных.

Использование Jet and Replication Objects

Следующее расширение ADO, которое мы рассматриваем в данной статье, — Jet and Replication Objects (JRO). В отличие от других расширений ADO (ADOX и ADO MD), которые способны работать с различными источниками данных, объекты JRO были созданы специально для поддержки некоторых операций, характерных для репликации баз данных Jet. Это означает, что такие объекты могут быть использованы только с базами данных Microsoft Access.

Введение в JRO

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

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

Кроме того, объектная модель JRO включает объект JetEngine, экспонирующий некоторые особенности Microsoft Jet Engine. В частности, объект JetEngine может быть использован для сжатия базы данных, установки пароля, шифрования базы данных, обновления данных из кэша. Эти объекты образуют иерархию, показанную на рис. 2.

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

Первый шаг в репликации заключается в создании источника реплик (design master) и в том, чтобы сделать этот источник способным к репликации. Для этой цели используются объект Replica и его метод MakeReplicable.
Далее следует изменить статус объектов базы данных с точки зрения того, доступны ли они для репликации. Для этого используются методы GetObject Replicability и SetObjectReplicabilty объекта Replica.
Затем, в зависимости от задачи, мы должны создать частичную или полную реплику объектов, которые доступны для репликации в источнике реплик. Для этого следует определить некоторые правила обновления данных с помощью объекта Filter.
И, наконец, мы должны синхронизовать данные в двух репликах. Синхронизация может быть прямой или непрямой, а также осуществляться через Интернет. В последнем случае нам требуется программный компонент Replication Manager, входящий в состав Microsoft Office Developer Edition.

Чтобы использовать библиотеку JRO в приложениях Delphi, нужно открыть новый проект, выбрать из главного меню среды разработки пункт Project | Import Type Library, в диалоговой панели Import Type выбрать Microsoft Jet and Replication Objects 2.1 Library (Version 2.1) и нажать кнопку Install. Это приведет к созданию модуля JRO_TLB, который может быть включен в код для доступа к объектам JRO.

Использование объекта JetEngine

Как мы уже знаем, объект JetEngine может быть использован для сжатия баз данных и обновления данных из кэша. Следующий пример показывает, как сжать базу данных Northwind.mdb в каталоге c:\data\ и создать новую сжатую копию Newnorth.mdb в каталоге d:\data\.

   const
    Provider = 'Provider=Microsoft.Jet.OLEDB.4.0;';
    SrcMDB    = 'c:\data\northwind.mdb';
    DstMDB    = 'd:\data\newnorth.mdb';
   
   procedure TForm1.Button1Click(Sender: TObject);
   var
    JetEng : JetEngine;
    Src      : WideString;
    Dest    : WideString;
   begin
   // Создать экземпляр объекта JetEngine
    JetEng := CoJetEngine.Create;
   // Определить источник
    Src      := Provider + 'Data Source=' +    SrcMDB;
   // и место назначения
    Dest    := Provider + 'Data Source=' + DstMDB;
   
   // Проверить, есть ли файл с копией, и если есть, то удалить его
   If FileExists(DstMDB) Then DeleteFile(DstMDB);
   // Сжать базу данных
    JetEng.CompactDatabase(Src, Dest);
   // Уничтожить созданный экземпляр объекта JetEngine
    JetEng := Nil;
   end;

Не углубляясь в подробности функционирования Microsoft Jet Engine, отметим, что при сжатии базы данных происходит следующее:

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

Рассмотрев ADO Extension for DDL and Security (ADOX) и Jet and Replication Objects (JRO), мы переходим к последнему расширению ADO — ADO Multidimensional (ADO MD), используемому для доступа к многомерным хранилищам данных.

1999	1	2	3	4	5	6	7	8	9	10	11	12
2000	1	2	3	4	5	6	7	8	9	10	11	12
2001	1	2	3	4	5	6	7	8	9	10	11	12
2002	1	2	3	4	5	6	7	8	9	10	11	12
2003	1	2	3	4	5	6	7	8	9	10	11	12
2004	1	2	3	4	5	6	7	8	9	10	11	12
2005	1	2	3	4	5	6	7	8	9	10	11	12
2006	1	2	3	4	5	6	7	8	9	10	11	12
2007	1	2	3	4	5	6	7	8	9	10	11	12
2008	1	2	3	4	5	6	7	8	9	10	11	12
2009	1	2	3	4	5	6	7	8	9	10	11	12
2010	1	2	3	4	5	6	7	8	9	10	11	12
2011	1	2	3	4	5	6	7	8	9	10	11	12
2012	1	2	3	4	5	6	7	8	9	10	11	12
2013	1	2	3	4	5	6	7	8	9	10	11	12