|
||||||||
|
||||||||
|
Borland Delphi и расширения ADO
Алексей Федоров, Наталия Елманова,
журнал
"КомпьютерПресс" #10/2000 Применение ADO Extension for DDL and Security Введение в ADOX Создание приложения для просмотра метаданных Создание баз данных и их объектов Использование Jet and Replication Objects Введение в JRO Использование объекта JetEngine Использование ADO MultiDimensional OLAP и создание хранилищ данных: краткое введение Объекты ADO MD Объекты для доступа к метаданным Объекты для доступа к данным Создание утилиты просмотра OLAP-кубов Заключение Настоящая статья посвящена некоторым полезным возможностям, предоставляемым
технологией 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
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. В качестве
иллюстрации создадим приложение, с помощью которого пользователь сможет: 1. просматривать метаданные в виде
<дерева> объектов; 2. изучать свойства объектов базы
данных; 3. получать исходный текст хранимых
процедур и представлений. Для выполнения этой задачи создадим новый проект и поместим на главную форму
будущего приложения следующие компоненты: 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; // и MemoMemo1.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); ...
В
приведенном выше коде создается новая база данных - заранее заданного типа
в заранее заданном каталоге. Затем мы можем добавить в эту базу данных таблицы
и поля. Чтобы сделать это, необходимо выполнить такую последовательность
действий: 1. Создать новый экземпляр объекта Table.
2. Создать новый экземпляр объекта Column.
3. Определить свойства вновь
создаваемого поля. 4. Добавить объект Column в
коллекцию Columns объекта Table. 5. Повторить шаги 3-4 для каждого
нового поля. 6. Добавить объект 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. Как и другие расширения ADO, JRO
обладает объектной моделью, содержащей объекты, позволяющие создавать,
модифицировать и синхронизовать реплики. Реплика - это точная копия базы
данных или ее части, сделанная в определенный момент времени. А
репликация - процесс создания реплик по определенным критериям (тип
объектов, периодичность и т.п.). Главным объектом JRO является
объект Replica. Этот объект может быть использован для создания новых
реплик, проверки свойств существующих реплик и синхронизации изменений с другими
репликами. Кроме того, объектная модель JRO
включает объект JetEngine, экспонирующий некоторые особенности Microsoft
Jet Engine. В частности, объект JetEngine может быть использован для
сжатия базы данных, установки пароля, шифрования базы данных, обновления данных
из кэша. Эти объекты образуют иерархию, показанную на рис. 2.
Репликация баз данных Jet может
быть предметом отдельной статьи, и мы не будем рассматривать этот вопрос
подробно. Вместо этого мы кратко перечислим шаги, которые следует выполнить: 1.
Первый
шаг в репликации заключается в создании источника реплик (design master)
и в том, чтобы сделать этот источник способным к репликации. Для этой цели
используются объект Replica и его метод MakeReplicable. 2.
Далее
следует изменить статус объектов базы данных с точки зрения того, доступны ли
они для репликации. Для этого используются методы GetObject Replicability
и SetObjectReplicabilty объекта Replica. 3.
Затем,
в зависимости от задачи, мы должны создать частичную или полную реплику
объектов, которые доступны для репликации в источнике реплик. Для этого следует
определить некоторые правила обновления данных с помощью объекта Filter.
4.
И,
наконец, мы должны синхронизовать данные в двух репликах. Синхронизация может
быть прямой или непрямой, а также осуществляться через Интернет. В последнем
случае нам требуется программный компонент 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, отметим,
что при сжатии базы данных происходит следующее: 1. реорганизуется расположение
табличных данных на страницах - после сжатия таблица располагается на
соседних страницах. Это повышает производительность, так как таблицы теперь
дефрагментированы; 2. объекты и записи, помеченные как
удаленные, реально удаляются, что позволяет высвободить дополнительное
пространство; 3. текущие значения счетчиков,
связанных с полями типа AutoNumber, переопределяются, поэтому следующее
значение для такого поля будет на единицу превышать максимальное имеющееся
значение; 4. обновляются сведения о таблицах,
используемые для оптимизации запросов; 5. поскольку сведения о базе данных
изменились, все запросы будут заново скомпилированы в момент первого обращения.
Рассмотрев ADO Extension for DDL and Security (ADOX) и Jet
and Replication Objects (JRO), мы переходим к последнему расширению
ADO - ADO Multidimensional (ADO MD), используемому для
доступа к многомерным хранилищам данных. Использование ADO MultiDimensional Инструменты для анализа данных и поддержки принятия решений в настоящее
время считаются одним из важнейших типов приложений. Реализация подобного
анализа нередко базируется на построении многомерных хранилищ данных (data
warehouses) и на аналитической обработке данных (On-Line Analytical
Processing, OLAP) - популярной технологии многомерного
бизнес-анализа. Концепция OLAP была описана в 1993 году Е.Ф.Коддом, известным
исследователем баз данных и автором реляционной модели данных. В настоящее
время поддержка OLAP реализована в различных СУБД и инструментах. Если вы
знакомы с компонентами Decision Cube, то вы уже видели простейшую
реализацию OLAP - приложения с такими компонентами представляют собой примитивные
OLAP-инструменты. Подробности об OLAP можно найти на сайте www.olap.ru, сопровождаемом
компанией <Интерфейс>, поставщиком многих OLAP-продуктов на российском рынке. В нашей статье мы можем себе позволить лишь краткое введение в OLAP и
создание хранилищ данных, ибо это потребуется для понимания того, что
представляют собой объекты ADO MD. Более подробно тема OLAP будет раскрыта в
одной из статей цикла <Введение в СУБД>. OLAP и создание хранилищ данных: краткое введение Рассмотрим, из чего обычно состоит многомерное хранилище данных. Представим
себе торговую компанию, которая хранит все сведения о торговых операциях в
какой-либо базе данных, содержащей среди прочих представление Invoices с
подробными сведениями о заказах клиентов. Предположим, что обращение к этому
представлению приводит к появлению набора данных, показанного в табл. 3. Таблица 3
Допустим, нам требуется узнать суммарную стоимость всех заказов, сделанных
клиентами из Германии. Очевидно, что для получения такого суммарного
значения (summary) нужно выполнить следующий запрос: SELECT SUM(Payments) FROM Sales WHERE Country='Germany' В этом запросе можно заменить 'Germany' на 'Austria', или на 'UK',
или на название другой страны. В результате мы получим одномерный массив
значений - по одному числу для каждой страны, как показано в табл. 4: Таблица 4
Теперь немного усложним задачу. Например, спросим, какова суммарная
стоимость заказов овощей, сделанных клиентами из Германии: SELECT SUM (Payment) FROM Invoices WHERE Country=' Germany' AND ProductCategory='Vegetables' Если мы рассмотрим все возможные комбинации категорий продуктов и названий
стран, мы получим двухмерный массив суммарных значений (табл. 5): Таблица 5
Подобная таблица часто именуется перекрестной таблицей, кросс-таблицей,
а также сводной таблицей (Cross table, Pivot table).
Первым измерением (dimension) в ней является измерение Country,
вторым - ProductCategory. Снова модифицируем наш запрос. Предположим, теперь нам нужно узнать, какова
суммарная стоимость заказов овощей, сделанных клиентами из Германии
и обработанных сотрудником по имени Nicolas Wilson. SELECT SUM(Payments) FROM Sales WHERE Country='Germany' AND ProductCategory='Vegetables' AND SalesPerson='Nicolas Wilson' Если мы рассмотрим все возможные комбинации категорий продуктов, названий
стран, имен сотрудников, то получим трехмерный массив суммарных
значений, который можно представить в виде куба данных (на самом деле
это, конечно, параллелепипед, но к таким наборам данных, тем не менее, принято
применять термин <куб>). Размерностями этого набора данных являются Country,
ProductCategory и SalesPerson. Мы можем продолжать добавлять
параметры к нашему запросу (и, следовательно, добавлять к нашему кубу все новые
и новые размерности) и получать соответствующие суммарные значения. Именно это
и делает приложение, использующее компоненты Decision Cube, - оно
вычисляет такие суммы и хранит их в оперативной памяти. Следует отметить, что наборов таких суммарных значений может быть несколько
(в частности, это могут быть суммы платежей, средняя стоимость, количество
заказов и т.п.). Подобные суммарные значения в русской терминологии именуются агрегатными
данными, а в англоязычной наиболее применимы термины summaries и measures.
Следующий важный аспект, часто используемый при создании
OLAP-хранилищ, - это иерархическая структура размерностей. Например, если
одна из размерностей основана на поле типа <дата/время>, то суммарные значения
можно получить для различных годов, а также для кварталов, месяцев, дней (это
единственный тип иерархии, поддерживаемый компонентами Decision Cube). Мы также
можем сравнить, допустим, суммарные значения для сходных периодов времени
(например, для всех сред и всех четвергов или для января 1999
года и января 2000 года). Нам также может понадобиться
и другой тип иерархии (например, Country/State/City или Product
Category/Product Subcategory/Product Name). В нашей статье для иллюстрации применения объектов ADO MultiDimensional
в качестве OLAP-сервера мы будем использовать Microsoft SQL Server 7.0 OLAP
Extensions (или Microsoft SQL Server 2000 Analysis Services). На
данный момент OLE DB Provider for OLAP Services, входящий в состав Microsoft
SQL Server OLAP Extensions (Microsoft SQL Server 2000 Analysis Services) и
в состав Microsoft Office, является единственным OLE DB-провайдером,
позволяющим обращаться к многомерным данным. С его помощью можно обращаться к
данным, хранящимся в многомерных базах данных Microsoft SQL Server 7.0/2000 или
в локальных файлах *.cub, которые можно создать с помощью Microsoft
Excel 2000. Однако мы полагаем, что в ближайшее время следует ожидать
выпуска других OLE DB-провайдеров для других OLAP-серверов. В данной публикации мы не описываем, как программно создать многомерное
хранилище данных - чтобы сделать это в приложении, следует использовать
библиотеку SQL DSO (Decision Support Objects), описание применения
которой выходит за рамки данной статьи. Для иллюстрации работы ADO MD мы используем многомерную базу данных FoodMart,
входящую в комплект Microsoft SQL Server 7.0 OLAP Extensions. Однако при
необходимости можно использовать любой локальный файл *.cub, созданный с
помощью Microsoft Excel 2000, - в этом случае на компьютере, где будут
создаваться примеры, должен быть установлен Microsoft Excel 2000. Начнем изучение ADO MD с объектной модели, доступной в приложениях Delphi. Объектная модель ADO MD, представленная на рис. 3, состоит из двух, так
сказать, ветвей объектов. Первая из них используется для доступа к метаданным многомерной
базы данных, вторая применяется тогда, когда нам нужно извлечь данные с помощью
запросов к OLAP-кубам.
Объекты для доступа к метаданным Первый из объектов ADO MD для доступа к метаданным - объект Catalog -
представляет собой многомерное хранилище данных. Такое хранилище может
содержать ноль, один или много кубов, а следовательно, одним из свойств объекта
Catalog является коллекция CubeDefs. Каждый из элементов этой
коллекции - CubeDef, представляющий конкретный куб в хранилище. Имя
куба является значением свойства Name соответствующего объекта CubeDef.
В базе данных FoodMart имеется три таких куба - Sales, Warehouse
и Warehouse and Sales. Каждый из многомерных кубов содержит несколько размерностей. Соответственно
каждый из объектов CubeDef может содержать коллекцию Dimensions
объектов Dimension. Каждый объект Dimension представляет
конкретную размерность куба, а имя размерности содержится в свойстве Name
соответствующего объекта Dimension. Например, куб Sales в базе
данных FoodMart содержит несколько размерностей - Store, Time,
Product, Promotion Media, Promotions, Customers
и др. Как уже было сказано, данные, из которых состоят размерности, могут быть
иерархическими. Соответственно объект Dimension содержит коллекцию Hierarchies,
которая теоретически может содержать один или более объектов Hierarchy.
Однако на практике для каждой размерности существует только одна иерархия (по
крайней мере для Microsoft SQL Server OLAP Extensions), поэтому данная
коллекция содержит один-единственный элемент. Иерархия размерности может содержать один или несколько уровней, поэтому
объект Hierarchy содержит коллекцию Levels объектов Level.
Например, иерархия размерности Store куба Sales содержит четыре
уровня - Store Country, Store State, Store City и Store Name.
Каждый из уровней иерархии содержит один или более членов, которые
представляют собой либо значения соответствующих полей в исходной базе данных,
либо значения, полученные посредством группировки. Следовательно, объект Level
содержит коллекцию Members объектов Member. Например, уровень Store
Country единственной иерархии размерности Store содержит три
члена - Canada, Mexico и USA, уровень Store State
этой же самой иерархии содержит десять членов - CA, OR, WA и
несколько штатов Канады и Мексики, а уровень Store City содержит
еще больше членов, соответствующих городам в этих штатах. Отметим, что поскольку содержимое коллекции Members зависит от исходных
данных, на основе которых построен куб, то метаданными это можно назвать весьма
условно. Значения членов уровня иерархии являются метаданными в том случае,
когда речь идет о раз и навсегда созданном кубе, который далее не
пересчитывается на основе изменившихся исходных данных. В действительности ADO
MD соответствует именно такой точке зрения - эта технология предназначена
для чтения многомерных данных, но не для их записи. С точки зрения SQL DSO или
иных технологий создания многомерных хранилищ - значения членов уровня
иерархии являются уже не метаданными, а данными. Впрочем, обсуждение создания
многомерных хранилищ - предмет отдельной статьи. Каждый из этих объектов обладает внушительным списком свойств и методов. Их
подробное описание можно найти в MSDN Library (http://www.msdn.microsoft.com/data).
Вторая ветвь объектной модели ADO MD применяется для получения сечений кубов
(как правило, это двухмерные кросс-таблицы, подобные табл. 4, приведенной в
начале этого раздела). Для получения подобных сечений требуется сформулировать
к кубу соответствующий запрос. Поскольку многомерная база данных не является
реляционной (как минимум, с логической точки зрения), то использовать язык SQL
для запроса к ней мы не можем. Вместо этого нам следует описать, из чего
состоят строки и столбцы и какие из доступных агрегатных данных следует
отобразить. Для этого мы можем использовать так называемые multidimensional
expressions (MDX) - расширения SQL для формулирования
запросов к OLAP-кубам. Типичный MDX-запрос выглядит следующим образом: SELECT axis_specification ON COLUMNS, axis_specification ON ROWS FROM cube_name WHERE slicer_specification Здесь axis_specification - описание того, из чего состоит
горизонтальная или вертикальная ось, в частности [Store].[Store
Country].MEMBERS представляет собой список стран, [Store].[Mexico].CHILDREN -
список штатов в Мексике. Slicer specification представляет собой
имя агрегатных данных, используемых для создания кросс-таблицы. Например,
следующий MDX-запрос: SELECT [Time].[Quarter].MEMBERS ON COLUMNS, {[Store].[USA].CHILDREN, [Store].[Canada].CHILDREN} ON ROWS FROM Sales WHERE [Measures].[Profit] приведет к получению кросс-таблицы, содержащей суммарный ежеквартальный
доход для всех штатов США и Канады. Подробности о синтаксисе и ключевых словах MDX можно найти в Microsoft
Platform SDK. В объектной модели ADO MD кросс-таблица, являющаяся результатом MDX-запроса,
представлена объектом CellSet. Этот объект предоставляет доступ к
объектам Cells, представляющим конкретные ячейки в кросс-таблице. Помимо
этого объект CellSet содержит коллекцию Axes объектов Axis
(обычно в этой коллекции два объекта, соответствующие строкам и столбцам). Как
объект Cell, так и объект Axis обладают коллекцией Positions
объектов Position, представляющих позицию вдоль оси. Объект Position
обладает коллекцией Members, представляющей конкретное значение данных
на оси. Рассмотрев объекты ADO MD, мы можем создать приложение, их использующее. Это
будет сделано в следующем разделе. Создание утилиты просмотра OLAP-кубов Выясним, как объекты ADO MD можно использовать в Delphi. В этих целях
создадим приложение, с помощью которого пользователь сможет:
Для этого создадим новый проект и поместим его главную форму будущего
приложения компонента TToolBar с несколькими кнопками, TTreeView,
TListBox, TDBGrid, TClientDataSet, TDataSource и TMemo. Затем
установим значение свойства DataSource компонента DBGrid1 равным DataSource1,
а значение свойства DataSet компонента DataSource1 равным ClientDataSet1.
Компонент ListBox1 следует заполнить ключевыми словами MDX, такими как CHILDREN,
MEMBERS, DESCENDANTS, и др. Далее следует сослаться в нашем приложении на библиотеку типов ADO MD, содержащуюся
в файле MSADOMD.DLL, так как ADO MD не поддерживается в Delphi 5 на
уровне компонентов. Для этого следует выбрать пункт Project | Import Type
Library из главного меню среды разработки, а затем выбрать Microsoft
ActiveX Data Objects (Multi-dimensional) 1.0 Library из списка доступных
библиотек типов. Обратите внимание на то, что если вы уже импортировали
библиотеку типов ADOX и не переименовали класс Delphi для объекта ADOX Catalog,
то класс Delphi TCatalog окажется уже определенным. В этом случае во
избежание конфликта имен можно переименовать TCatalog в TADOMDCatalog.
Желательно также убедиться, что опция Generate Component Wrapper не выбрана,
так как нам нужно создать только *.pas-файл для доступа к
объектам ADO MD. Далее можно нажать кнопку Create Unit, что приведет к
созданию файла ADOMD_TLB.PAS, представляющего собой интерфейсный модуль
к библиотеке типов ADO MD. Наконец, нам нужно включить ссылку на этот файл в
предложение Uses, так же как и ссылку на модули ComObj и ADODB.
Следующий шаг в нашем приложении - соединение с многомерной базой
данных. Параметр ConnectionString, используемый для этой цели, должен ссылаться
на OLE DB Provider for OLAP Services (стоит убедиться, что он
действительно установлен), а также на имя компьютера и имя базы данных,
например: DS := 'Provider=MSOLAP.1;Data Source=localhost; Initial Catalog=FoodMart'; Можно также соединиться с локальными кубами, созданными с помощью Microsoft
Excel и сохраненными в файлах *.cub. В этом случае параметр Connection
String может выглядеть так: DS := 'Provider=MSOLAP.1;Data Source=C:\Data\Cubes\NW1.cub'; Код, отвечающий за соединение с базой данных, можно поместить в обработчик
события OnClick одной из кнопок. Этот код, а также процедура,
заполняющая компонент TreeView1 именами кубов, представлены ниже: procedure TForm1.Button1Click(Sender: TObject); begin DS := 'Provider=MSOLAP.1;Data Source=localhost;'+ 'Initial Catalog=FoodMart'; FillTreeView(DS); end; procedure TForm1.FillTreeView(DataSource: WideString); var I : Integer; begin //Создадим новый объект Catalog Catalog1 := CoCatalog.Create; TreeView1.Items.Clear; RootNode := TreeView1.Items.Add(nil, 'Catalog'); //Соединимся с многомерной базой данных Catalog1._Set_ActiveConnection(OleVariant(DataSource)); //Последовательно получим имена всех кубов в базе данных for I := 0 to Catalog1.CubeDefs.Count-1 do begin CubeDef1 := Catalog1.CubeDefs[I] as CubeDef; CubeDefNode := TreeView1.Items.AddChild(RootNode, CubeDef1.Name); end; end; Здесь мы соединяемся с базой данных, создаем объект Catalog,
просматриваем по очереди все элементы его коллекции CubeDefs и извлекаем
имена кубов (они содержатся в свойстве Name объектов CubeDef). Реальная обработка метаданных куба реализована в обработчике события OnMouseDown
компонента TreeView1: procedure TForm1.TreeView1MouseDown(Sender: TObject; Button: TMouseButton; Shift: TShiftState; X, Y: Integer); var HitTest : THitTests; CurrNode : TTreeNode; I : integer; NodeName : string; AddString : String; begin HitTest := TreeView1.GetHitTestInfoAt(X,Y); //Если пользователь щелкнул мышью на одной из ветвей TTreeView if (htOnItem in HitTest) then begin CurrNode := TreeView1.GetNodeAt(X, Y); //Если у ветви могут быть дочерние ветви, но они еще не добавлены //добавим их if ((CurrNode.Count=0) and (CurrNode.Level<4)) then begin case CurrNode.Level of //Эта ветвь представляет куб 1: begin CubeDef1 := Catalog1.CubeDefs.Get_Item(CurrNode.Text); //Получаем имена всех размерностей куба for I := 0 to CubeDef1.Dimensions.Count-1 do begin Dimension1 := CubeDef1.Dimensions[I] as Dimension; DimNode := TreeView1.Items.AddChild(CurrNode, Dimension1.Name); end; end; //Эта ветвь представляет размерность 2: begin CubeDef1 := Catalog1.CubeDefs.Get_Item(CurrNode.Parent.Text); Dimension1 := CubeDef1.Dimensions.Get_Item(CurrNode.Text); //Получаем имена всех уровней иерархии данной размерности for I := 0 to Dimension1.Hierarchies[0].Levels.Count-1 do begin Level1 := Dimension1.Hierarchies[0].Levels[i] as Level; LevelNode := TreeView1.Items.AddChild(CurrNode, Level1.Name); end; end; //Эта ветвь представляет уровень иерархии 3: begin CubeDef1 := Catalog1.CubeDefs.Get_Item(CurrNode.Parent.Parent.Text); Dimension1 := CubeDef1.Dimensions.Get_Item(CurrNode.Parent.Text); Level1 := Dimension1.Hierarchies[0].Levels.Get_Item(CurrNode.Text); //Получаем имена всех членов данного уровня иерархии for I := 0 to Level1.Members.Count-1 do begin Member1 := Level1.Members[I] as Member; MemberNode := TreeView1.Items.AddChild(CurrNode, Member1.Name); end; end; end; end else //Если данная ветвь уже имеет дочерние ветви (или их не должно быть), //скопируем имя объекта в редактор MDX-запросов begin //Если ветвь не корневая if Currnode.Level>0 then begin CurrNode := TreeView1.GetNodeAt(X, Y); NodeName := CurrNode.Text; //Копируем имя ветви, сформатированное в соответствии //с синтаксисом MDX, в редактор MDX-запросов if ((CurrNode.Level=1) or (CurrNode.Parent.Parent.Text='Measures')) then AddString:='['+NodeName +']' else AddString:='['+NodeName +'].'; Memo1.SetSelTextBuf(PChar(AddString)); end; end; end; end; procedure TForm1.ListBox1Click(Sender: TObject); var AddString:string; begin //Добавим ключевое слово MDX из списка в редактор MDX-запросов AddString := Listbox1.Items[Listbox1.ItemIndex]+' '; Memo1.SetSelTextBuf(PChar(AddString)); end; Здесь мы определяем, что именно представляет ветвь, на которой пользователь
щелкнул мышью (куб, размерность, уровень иерархии, член уровня), используя ее
свойство Level, а также выясняем, имеются ли уже у нее дочерние ветви.
Если дочерние ветви отсутствуют (свойство Count данной ветви равно
нулю), мы обращаемся к базе данных и создаем соответствующие дочерние ветви,
используя свойство Name соответствующего объекта ADO MD. Если же из базы
данных уже нечего загружать, мы копируем имя объекта, представленного данной
ветвью, в компонент Memo1, в то место, где находится курсор. Код для копирования ключевых слов MDX в компонент Memo1 приведен в
этом же фрагменте кода. Таким образом, мы получили инструмент для просмотра
метаданных куба и создания текста MDX-запросов с помощью щелчков мыши на ветвях
дерева объектов ADO MD на элементах списка ключевых слов. Следующий шаг в создании OLAP-клиента заключается в выполнении MDX-запроса,
содержащегося в компоненте Memo1, и в заполнении компонента TClientDataSet
его результатами. Эта функциональность реализована в процедуре CDSFill, приведенной
ниже: procedure TForm1.Button2Click(Sender: TObject); begin CDSFill(DS); end; procedure TForm1.CDSFill(DataSource: WideString); var I,J : Integer; V : OleVariant; begin //Создадим новый объект CellSet CellSet1 := CoCellSet.Create; try //Выполним MDX-запрос, содержащийся в компоненте Memo1, //и откроем объект CellSet CellSet1.Open(Memo1.Text,DataSource); with ClientDataSet1 do begin Close; with FieldDefs do begin //Уничтожим все определения полей в ClientDataset Clear; //Добавим новые определения полей //Первое поле нужно для ранения имен строк with AddFieldDef do begin Name := 'Rows'; DataType := ftString; end; //Перебираем коллекцию Positions первой оси for I := 1 to CellSet1.Axes[0].Positions.Count do begin with AddFieldDef do begin //Значение поля исходной базы данных станет именем колонки Name :=CellSet1.Axes[0].Positions[I-1].Members[0].Caption+ //Имена колонок в наборах данных должны быть уникальны, поэтому //добавим уникальное число, содержащееся в свойстве Ordinal //объекта Position, к значению поля ' ('+IntToStr(CellSet1.Axes[0].Positions[I-1].Ordinal) +')'; DataType := ftFloat; end; end; end; //Создаем и открываем ClientDataSet CreateDataSet; Open; //Добавляем к нему записи for J:=1 to CellSet1.Axes[1].Positions.Count do begin //Добавляем запись Append; //Добавляем имя строки, используя коллекцию Position второй оси Fields[0].Value := CellSet1.Axes[1].Positions[J-1].Members[0].Caption; //Перебираем ячейки в строке, извлекая из них данные for I := 1 to CellSet1.Axes[0].Positions.Count do begin //Создаем массив координат ячеек V:=VarArrayCreate([0,1], varVariant); V[0] := I-1; V[1] := J-1; //Если соответствующая ячейка в CellSet не пуста, if CellSet1.Item[PSafeArray(TVarData(V).VArray)].FormattedValue <> '' then //Значение поля будет равно значению в ячейке Fields[I].Value := Cellset1.Item[PSafeArray(TVarData(V).VArray)].Value else //иначе поместим в поле нулевое значение ClientDataSet1.Fields[I].Value:=0; end;end; //Закрываем Cellset и высвобождаем ресурсы CellSet1.Close; CellSet1 := nil; end;except ShowMessage('Invalid MDX Query'); end;end; В данном фрагменте кода мы создаем объект CellSet и используем его
метод Open. Если MDX-запрос корректен, будет создан пустой набор данных
типа TClientDataset с именами полей, равными свойству Caption
первых элементов коллекций Members, являющихся свойствами
коллекции Positions первой оси (Axis[0]). Обратите внимание на то, что имена полей в наборах данных должны быть
уникальны. Однако в реальных многомерных базах данных свойство Caption
членов коллекции Members таковым не является. Например, в иерархии Year/Month
свойство Caption для членов коллекции Members January 1999 и January
2000 будет равно одному и тому же значению January. Существует много
способов избежать дублирования имен полей, и в данном примере мы использовали
самый простой - добавление уникального числа, содержащегося в свойстве Ordinal
объекта Member. После того как имена полей компонента ClientDataset1 определены,
выполняется цикл перебора строк объекта CellSet, и для каждого ряда мы
устанавливаем значение первого поля равным свойству Caption первого
элемента коллекции Members соответствующего члена коллекции Positions
второй оси (Axis[1]), а затем помещаем значения из соответствующих
объектов Cell (доступных с помощью коллекции Item объекта CellSet)
в оставшиеся поля. В результате получается набор данных, заполненный
двухмерным сечением куба и отображенный в компоненте DBGrid1, как это
показано на рис. 4.
Итак, мы создали простейшее приложение для просмотра OLAP-кубов и выполнения
MDX-запросов, используя ADO MD. Это лишь элементарный пример для иллюстрации
возможностей ADO MD, который можно расширить, например путем добавления
бизнес-графики или более <интеллектуального> генератора запросов. В данной статье мы рассмотрели расширения
ADO - ADO Extension for DDL and Security (ADOX), Jet and
Replication Objects (JRO) и ADO Multidimensional (ADO
MD). Мы узнали, как использовать объекты ADOX для получения
метаданных и создания баз данных <из ничего>. Кроме того, мы изучили, как
использовать объекты JRO для сжатия баз данных Access и кратко рассказали о
процессе репликации баз данных. И, наконец, мы рассмотрели, как можно
использовать объекты ADO MD для получения метаданных и данных из многомерных
OLAP-хранилищ. Все эти расширения позволяют добавить к Delphi-приложениям
многие полезные функции, недоступные с помощью входящих в комплект поставки
Delphi компонентов ADO Express. Отметим, что в настоящее время единственным полноценным источником
информации по использованию ADO в Delphi является книга "Advanced Delphi
Developer's Guide to ADO", выпущенная в июне этого года издательством
Wordware Publishing (http://www.wordware.com/data).
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
За содержание страницы отвечает Гончарова М.Н. © Кафедра СПиКБ, 2002-2017 |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
