Глава 2. Организация SQL-доступа

Общие сведения

SQL-доступ к базе данных комплекса СБиС++ позволяет создавать собственные приложения, работающие с данными СБиС++. А также работать с данными комплекса из любой программы, поддерживающей интерфейс ODBC, например, из MS Excel.

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

• 
  база данных должна быть в формате Pervasive.SQL (расширение файлов «*.mkd»);
  
• 
  в качестве сервера БД должен быть установлен Pervasive.SQL 2000 и выше.
  

Настройку доступа к базе данных через интерфейс ODBC можно разделить на этапы. Рассмотрим их последовательно.

Подготовка базы данных комплекса

Этот этап наиболее прост. Если Ваша рабочая база данных ещё не сконвертирована в требуемый формат (Pervasive SQL), то нужно это сделать. Для выполнения этой операции используйте программу «jinnee.exe». В меню «База данных» выберите пункт «Сконвертировать», укажите каталог с базой данных (на сервере) и новый формат базы данных – «Переформатировать для Pervasive SQL». После переформатирования работа комплекса СБиС++ с базой данных осуществляется точно так же, как и раньше. Более подробно о работе с СУБД Pervasive.SQL можно прочитать в книге «Администрирование СБиС++» в главе «Работа с Pervasive.SQL».

Настройка Engine DSN на сервере

В ODBC для доступа к базе данных используется DSN (Data Source Name). Именно DSN указывается в программах (например, в Excel) для указания требуемой базы данных. Pervasive.SQL в своей работе использует два типа DSN: Engine DSN и Client DSN. Первый тип используется самим сервером для доступа к таблицам, второй – для доступа по сети с рабочей станции к БД, находящейся на сервере. В случае работы прямо на сервере настройка на нём Client DSN не требуется.

Все действия по настройке БД осуществляются из Pervasive.SQL Control Center – утилиты настройки и администрирования сервера Pervasive.SQL. Рассмотрим настройку на примере Pervasive.SQL v.9

Рис. 2-1 – Окно утилиты Pervasive.SQL Control Center

В списке баз данных, установленных на сервере, присутствует база под названием DEMODATA. Это демонстрационная база данных, устанавливаемая вместе с сервером. Нажав правую кнопку мыши, выбираем пункт «New → Database». Появляется мастер создания базы данных:

Рис. 2-2 – Создание новой БД

В появившемся диалоге следует указать имя БД (DSN), каталог, в котором находятся файлы БД (в нашем случае, это каталог «SBIS»), и путь к этому каталогу на сервере (в нашем случае, это путь «S:\SBIS\db»). Остальные параметры оставляем без изменения. Подтверждаем создание новой БД, нажав кнопку «Finish».

Теперь, когда создан Engine DSN, БД СБиС++ появляется в списке баз данных в Pervasive.SQL Control Center:



Рис. 2-3 – Добавление БД СБиС++

Теперь необходимо указать свойства этой БД, выбрав в контекстном меню «Properties»:

Рис. 2-4 – Задание свойств БД СБиС++

В появившемся диалоге в группе «General» снимаем флаг «Integrity enforced» и устанавливаем «Bound Database». Для установки указанных свойств нажимаем «Apply» и «Ок».

Если теперь вы зайдёте в созданную базу данных, Вы не увидите там никаких таблиц. Это из-за того, что сервер не имеет пока никакой информации об уже существующих таблицах. Чтобы занести её в эту БД, следует использовать программу «Джинн» (jinnee.exe). В меню «База данных» выберите пункт «Обновить DDF-файлы»:



Рис. 2-5 – Задание свойств БД СБиС++

Укажите путь к БД и оставьте предложенное имя пользователя и пустой пароль для входа в базу данных без изменения. Данные о таблицах будут занесены в БД.

Создание пользователей в SQL-представлении БД

Если в СБиС++ установлен пароль базы данных (задача «Администратор» пункт меню «Пользователи/ Установить пароль базы данных»), то для каждой таблицы указывается специальная кодовая строка-пароль (так называемый «owner»), которая требуется для последующего открытия этой таблицы в Pervasive.SQL Control Center. При загрузке СБиС++ это делается автоматически, но в случае назначения прав пользователей в SQL-представлении БД потребуется явное указание этого кода.

Для получения кода в СБиС++ предусмотрена команда «Назначить права для SQL-доступа» (меню «Пользователи» в задаче «Администратор»).

Данный пункт доступен только для пользователей с правами супервизора.

При выполнении данного пункта будет выдано сообщение с текущим кодом:

Рис. 2-6 – Запрос на текущий пароль базы данных

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

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

Сделать это надо следующим образом:

1. 
   в свойствах БД в Pervasive. SQL Control Center («Properties» в контекстном меню) перейти на закладку «Security».
   
2. 
   в поле «Enable Security» установить флаг. После этого появляется окно для введения и подтверждения пароля пользователя с именем «Master», который будет иметь максимальные права на базу данных. В частности, он имеет права на создание новых пользователей и назначение им прав.
   

Здесь следует отметить важное различие между Pervasive.SQL2000 Service Pack1 и последующими версиями сервера. В PSQL2000SP1 при назначении прав указание пароля таблицы (owner) не требуется, но в последующих версиях при назначении прав пользователю на какую-либо таблицу требуется указать пароль этой таблицы (owner). Сделать это нужно только для назначения прав – при работе этого пользователя с базой пароль на таблицы уже не потребуется. Также изменились права по умолчанию для пользователя Master – начиная с Service Pack 2A. По умолчанию он не имеет никаких прав на таблицы, но может сам присвоить их себе.

Существует два способа заведения пользователей и назначения им прав. Это можно делать в интерактивном режиме или с помощью операторов SQL.

Создание групп пользователей

При назначении прав на доступ к БД можно использовать группы пользователей. Определение пользователя к группе гарантирует ему полное предоставление всех прав этой группы.

Для создания групп пользователей в Pervasive Control Center откройте нужную базу данных и перейдите к ветке «Groups»:

Рис. 2-7 – Создание новой группы пользователей

По умолчанию, в этой группе уже имеется группа «PUBLIC». Для создания новой группы в контекстном меню выберите команду «New → Group».

Рис. 2-8 – Название новой группы

Указываем название группы и нажимаем «Finish». Окно закрывается, и новая группа добавляется в список имеющихся групп (ветка «Group»). Для назначения прав щелкаем на названии этой группы в ветке и переходим к закладке «Permissions» для назначения прав пользователям, которые будут определены в эту группу:

Рис. 2-9 – Назначение прав для пользователей этой группы

• 
  если необходимо назначить права на доступ ко всем таблицам БД, то это выполняется в разделе «Database». Указываем необходимые разрешения, установив флаг в нужном квадрате: для создания таблиц необходимо установить флаг в поле «Create table»; для удаления данных – в поле «Delete»; и т.д.
  
• 
  если необходимо разграничить доступ к таблицам БД, то переходим в раздел «Table»:
  

Рис. 2-10 – Детализация прав для таблиц БД

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

Обратите внимание, что на закладке «General» есть поле «Users», показывающее всех пользователей этой группы. Данное поле заполняется автоматически при определении пользователя в эту группу (рассказывается далее по тексту).

Теперь, собственно, надо определить пользователей для работы с БД.

Создание пользователей в интерактивном режиме

Для заведения пользователей в Pervasive Control Center откройте базу данных, в которую вы будете добавлять пользователей. Перейдите к группе «Users»:



Рис. 2-11 – Папка для хранения списка пользователей

Изначально эта группа пустая: пока не заведено ни одного пользователя.

Для добавления нового пользователя в контекстном меню выбираем команду «New → User»:



Рис. 2-12 – Создание нового пользователя

Появится диалоговое окно для внесения сведений о новом пользователе:

Рис. 2-13 – Заполнение сведений о пользователе

В нём следует указать:

• 
  в поле «Name» имя создаваемого пользователя;
  
• 
  в поле «Password» его пароль и подтверждение в поле «Confirm Password».
  
• 
  в поле «Group» выбираем группу с уже назначенными правами доступа к БД, в которую определяем данного пользователя.
  

Заполнив перечисленные поля, нажимаем кнопку «Finish». Данный пользователь появляется в ветке «User».



Рис. 2-14 – Назначение прав пользователю

Теперь необходимо назначить пользователю права на доступ к базе данных. Для этого в свойствах пользователя переходим на закладку «Permissions»:

• 
  если необходимо назначить права на доступ ко всем таблицам БД, то это выполняется в разделе «Database».
  
• 
  если необходимо разграничить доступ к таблицам БД, то переходим в раздел «Table».
  

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

После того, как все требуемые права указаны, можно нажимать «OK». Пользователь создан.

Использование SQL для создания пользователей

Для выполнения операторов SQL, которые потребуются, в том числе, и для заведения пользователей, можно использовать Pervasive Control Center. Откройте в нём базу данных. При входе в базу будет выдан запрос: укажите пользователя «Master» и соответствующий пароль. Когда доступ к базе будет предоставлен, в правой части окна выберите команду «Execute SQL Commands»:



Рис. 2-15 – Выполнение SQL -выражений

В появившемся окне справа можно вводить любые SQL операторы и выполнять их, выбирая соответствующий пункт из меню «SQL», либо кнопки на панели инструментов окна:

Рис. 2-16 – Выражение для создания нового пользователя

Для заведения нового пользователя используется оператор GRANT. Формат его такой:

GRANT LOGIN TO user:password

Здесь <user> – имя пользователя, <password> – назначенный ему пароль. Например, для того, чтобы завести пользователя с именем Elena и паролем PAROL, следует выполнить следующую команду:

GRANT LOGIN TO Elena:PAROL

Чтобы новый пользователь отобразился в ветке «Users», необходимо обновить данные, нажав кнопку , либо <F5>.

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

GRANT right ON table TO user

Если в СБиС++ установлен пароль БД, то следует написать следующее выражение:

GRANT right ON table ‘owner’ TO user

Здесь right – это право, которое требуется дать пользователю по имени <user> на таблицу <table>. Право может быть одно из следующих:

• 
  ALL – все права на таблицу
  
• 
  SELECT – право просматривать данные таблицы
  
• 
  UPDATE – право изменять данные таблицы
  
• 
  INSERT – право добавлять данные в таблицу
  
• 
  DELETE - право удалять данные из таблицы
  

Обратите внимание, что после указания имени таблицы, на которую требуется дать права, в одинарных кавычках указывается строка-пароль (owner) для этой таблицы. О том, как его получить, было написано выше. Соответственно, следующая команда разрешает пользователю Elena все операции с таблицей ANALIT, пароль которой ‘12345678’:

GRANT ALL ON ANALIT ‘12345678’ TO Elena

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

GRANT SELECT ON ANALIT ‘12345678’ TO Elena

Настройка Client DSN на рабочих местах

После того, как на сервере создан Engine DSN и в базе данных заведены пользователи (если требовалось), можно приступать к настройке сетевых рабочих мест. Для программ на рабочем месте создается Client DSN. Для этого в «Панели управления» (Control Panel) выберите «Источники данных (ODBC)» (ODBC Data Sources). Откроется окно, имеющее, как правило, показанный ниже вид.



Рис. 2-17 – Окно администратора источников данных ODBC

Выбрав закладку «Системный DSN», нажимаем на ней кнопку «Добавить» и из предложенного списка драйверов выбираем «Pervasive ODBC Client Interface». Появится окно, показанное ниже, в котором требуется ввести данные, необходимые для создания Client DSN.

Рис. 2-18 – Окно настройки Client DSN

Во-первых, требуется указать имя, под которым база данных будет доступна на этой рабочей станции (причем это имя может и не совпадать с тем, которое база данных имеет на сервере). Его следует ввести в поле «Data Source Name». Затем в поле «Address» требуется указать имя сервера, на котором физически находится база данных, и выбрать в поле «Transport Hint» используемый сетевой протокол. После этих действий кнопка «Get DSN List» должна стать доступной. Нажав на неё, вы получите возможность выбрать интересующий Engine DSN с указанного сервера. После того, как введены все данные, можно убедиться в их правильности, нажав на кнопку «Test». Будут запрошены имя пользователя и пароль (те, которые создавались на предыдущем шаге) и, если всё сделано правильно, будет выдано сообщение «Connection successful!». Можно нажимать «OK» для сохранения созданного DSN.

Извлечение данных из БД в Microsoft Excel

Для примера рассмотрим, что необходимо сделать для того, чтобы получить данные из какой-либо таблицы комплекса СБиС++ в Microsoft Excel.

Для импорта данных выбираем в меню «Данные» пункт «Импорт внешних данных/ Создать запрос»:

Рис. 2-19 – Импорт внешних данных в Microsoft Excel

Будет предложено выбрать требуемый источник данных (DSN). Выбирайте тот самый Client DSN (или Engine DSN, если программа Microsoft Excel запущена на сервере), который был создан на предыдущем шаге. После запроса имени пользователя и пароля мастер создания запросов предложит выбрать столбцы, которые следует включить в запрос:

Рис. 2-20 – Выбор таблицы для импорта

Для примера, выберите всю таблицу «ANALIT». На следующем шаге мастер предлагает указать условия отбора данных:

Рис. 2-21 – Задание правила отбора данных

Для нашего простого запроса пропустим этот этап и перейдём сразу к следующему шагу – указанию порядка сортировки:

Рис. 2-22 – Порядок сортировки данных

Пропустим этот шаг и для простоты не будем указывать сортировку. После этого осталось только согласиться с предложением и передать данные в Excel – и все записи таблицы «Аналитики» будут помещены в текущую или новую страницу программы (зависит от вашего выбора):

Рис. 2-23 – Задание правила отбора данных

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