Sql informix sdk


Содержание

Соединение ODBC от 64-битного SQL Server к источнику данных Informix

Я пытаюсь создать связанную таблицу в 64-разрядном SQL Server для Informix, и сервер SQL не отображает ODBC Informix в списке DSN. Я скачал 64-битный Informix Client SDK, однако, несмотря на то, что он содержит 64-битный Informix Client SDK, он появляется только в 32-битном ODBC DSN. Я попытался создать связанную таблицу в 32-разрядном SQL Server, и тогда я смог увидеть Informix ODBC DDN.

Мне просто интересно, есть ли Informix Client SDK, который будет отображаться в 64-ODBC DSN, а не в 32-разрядной версии?

Также есть способ связать 64-битный SQL Server с Informix, пожалуйста? Благодарю.

4 ответа

Создать ссылку на БД в SQL Server для Informix

Я использовал следующую программную среду:

  • SQL Server 2012
  • Informix SDK 4.10 FC2
  • Informix 11.5

Сначала установите некоторые свойства для драйвера Ifxoledbc:

Затем создайте связанный сервер:

Общие настройки — источником данных является ваша БД и имя вашего сервера

Настройки безопасности — Добавить действительные учетные данные базы данных Informix

Параметры сервера — установите RPC и RPC Out в True

Я использую ссылку db для вызова хранимой процедуры в Informix и записи данных в SQL Server.

Использовали ли вы 64-битную версию ODBCAD32.EXE для создания DSN?

Существует две версии администратора ODBC.

Это 32-битная версия:

Он может видеть только 32-битные драйверы, и только 32-битные процессы могут видеть его DSN

Это 64-битная версия:

Он может видеть только 64-битные драйверы, и только 64-битные процессы (т.е. 64-битный SQL Server) могут видеть его DSN

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

Я бы использовал функцию комментирования, но у меня недостаточно «очков репутации» для этого.

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

Позвольте мне добавить к его ответу следующее:

Заполнение источника данных (YourDatabaseName @ YourInformixServerName) может быть чем-то простым, например Fred @ Barney, а не тем, что я пробовал, а именно Fred@199.188.77.66 или Fred@199.188.77.66: 32002 (последний указывает порт / службу число).

После создания связанного сервера, если вы вернетесь на страницу «Общие свойства», вы увидите, что вы не можете редактировать то, что вы ввели для Связанного сервера, «Имя продукта» (почему это не называется «Имя поставщика», поскольку Ifxoledbc указан в Связанные серверы / провайдеры в дереве обозревателя объектов?), Источник данных и т. Д. Итак, если ваше соединение не удается из-за того, что вы что-то опечатали, неясно, как это исправить.

Вот что вы можете сделать:

Щелкните правой кнопкой мыши связанный сервер.

Под «Проверка соединения» ищите «Script Linked Server as».

Щелкните по последнему, а затем по каждому из следующих пунктов: «DROP AND CREATE To» и «Окно нового редактора запросов».

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

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

Откройте страницу свойств безопасности вашего связанного сервера и повторно введите любой пароль подключения (даже если в соответствующей записи могут отображаться звездочки — предполагающие, что пароль введен и правильный). Кроме того, изучите страницу общих свойств. Убедитесь, что ваши изменения застряли. Даже если вы запустили сценарий и не получили никаких ошибок, SQL Server будет очищать заполнение, такое как «Связанный сервер», «Имя продукта» и «Источник данных», если, например, у вас, как и у меня, был мозг аневризма и выбрал неправильный элемент для «Поставщик».

Как отмечено выше, нажмите «Проверить соединение», чтобы убедиться, что ваши изменения работают.

Sql informix sdk

NOTE : Most older tools use the ODBC driver however some of the new .NET tools coming out require the .NET driver. It’s best to install ODBC, .NET and the GLS components from the client install below. The rest is not necessary, although no harm comes from installing everything.

NOTE : If you install the driver via VPN you might have to reboot the system before it’s available for use. It’s best to install at the local console if you can.

NOTE : The IBM Informix SDK limits passwords used for authenticating against the ODBC service on Unity Connection to 18 characters. There is nothing Cisco can do on the server or client side to get around this. Here’s a link to an IBM document on this.

Support Support policy follows the tools that use these drivers.

IBM Informix Client SDK Install 4.10 TC9 for all 32 and 64 bit Windows Systems. Download Now (ZIP file — unpack and run install)

NOTE : IBM finally updated their install package in recent builds so installing this 32 bit version onto 64 bit operating systems should require only that you run the installer executable found in the zip as administrator — I leave the instructions below «just in case» folks have trouble. I’ve tested the 4.10 TC6 install on 64 bit versions of Windows 7, 8.1 and 10 with no issue.

If you are installing the 32 bit version onto a 64 bit OS you might need to prepend the SysWOW64 path temporarily to your %PATH% variable or the install may bark at you. To do this open a comand prompt — be sure to run «as administrator» , go to the folder where the Informix SDK install is at and type:

set PATH=C:\WINDOWS\SysWOW64\;%PATH%

and then launch the setup from the command line and it should go through. The same technique will work for Windows 8 installs, however you have to run the setup in Windows 7 compatibility mode because the Install Anywhere package IBM uses does not work with Windows 8. Here is a short training video showing me setting up the drivers on Windows 8 (5 minute Flash video).

You can find the latest SDK downloads from IBM using this link. All downloads are free but you do have to register on their site. Be sure to download the 32 bit version even if you are on a 64 bit OS — all tools are compiled for 32 bit operation.

If you are using Java and need to know the details of using JDBC via the Unity Connection database proxy, this document has the details for doing that form both Windows and Linux environments.

Description

The Informix drivers are used by off box tools that connect to Unity Connection 2.1(1) or later systems via the database proxy service.

The help files for each tool that uses the remote database proxy service to attach to Connection has instructions for how to configure the remote ODBC access from off box. You must create an account with remote administration access and enabled/activate the remote proxy service before you can use it — out of the box Connection has this disabled for security purposes.

Sql informix sdk

Для начала несколько определений, которые, впрочем, всем известны и ничего не определяют. Понятие «Система Управления Базами Данных» (СУБД), она же DBMS (DataBase Managment System) может означать, по большому счету, все что угодно.
В самом общем случае это собственно база данных, которая предполагает какой либо метод сохранения информации на диске и возможности доступа и манипуляции с нею, и набор программных продуктов, предоставляющий пользователю все допустимые в базе средства работы с данными.
Набор программных средств манипуляции данными СУБД удовлетворяет свойствам полноты (консистентности). Полагаю, что коммерческие варианты СУБД стремятся быть еще и замкнутыми, т.е. самодостаточными, не требующими и не поддающимися расширению.

Архитектура Клиент-Сервер.

Очень многие СУБД разделяют свою работу на два уровня по системе «Клиент-Сервер». Хитрость состоит в том, что не только пользователь, но и программист этого не замечает. Разделение функций его задачи выполняется автоматически системой.

Итак, двухуровневая система «Клиент-Сервер» это:

  1. Клиент — Программа обработки, она же пользовательская, она же прикладная программа. Занимается обычно интерфейсом с пользователем, а всю фактическую работу с базой данных возлагает на плечи БД-сервера.
  2. Сервер Базы Данных — базис (database engine), он же ядро базы данных. Отдельная программа, выполняемая как отдельный процесс. Передает выбранную из базы информацию по межпроцессному каналу клиенту. Именно он, и только он фактически работает с данными, занимается их размещением на диске.
  3. В первый момент может возникнуть вопрос, а зачем такие сложности? Вот несколько соображений в пользу такого подхода.
    Представьте, вы работаете в сети, ваша программа обработки идет на одном компьютере, а сама база данных хранится на другом. Тут разделение выглядит совершенно естественным: клиент — ваша программа (точнее, та ее часть, которая отвечает за интерфейс с вами), гонит по сети запросы на обработку самих данных на другой компьютер, а там БД-сервер их прочитывает, выполняет требуемое, и по сети гонит ответы вам. При этом по сети передается только полезная информация.
    Другое соображение: постоянно идет работа по совершенствованию самого метода хранения и обработки информации, и если его реализация (т.е. БД-сервер) сменилась, та вам не потребуется перелопачивать и перекомпилировать с новыми библиотеками все свои разработанные программы, а достаточно будет инсталлировать новый БД-сервер взамен старого и перевести свои базы данных в формат нового сервера (применив для этого прилагаемую к нему утилиту). Естественно, все это можно проделать, если новый сервер придерживается тех же правил обмена между ним и пользовательской программой, что и старый, что, впрочем, наверняка имеет место.
    Всвязи с СУБД INFORMIX нас, естественно, интересует только одна модель данных — реляционная. Сетевых и иерархических моделей рассматривать здесь я не буду. Можно, конечно, дать строгое определение реляционной модели данных по Э.Ф. Кодду, но ведь ни одну из коммерческих СУБД нельзя назвать реляционной в строгом смысле этого слова.

Основные Объекты Реляционной Базы Данных.

(DATABASE) Реляционная База Данных: это множество прямоугольных таблиц с данными. Никаких дополнительных сведений и ссылок не хранится.

(TABLE) Таблица — прямоугольная таблица, состоящая из СТРОК и СТОЛБЦОВ. Задать таблицу — значит указать, из каких столбцов она состоит. Обращаю внимание поклонников DBase — то, что в DBase называлось базой данных, в INFORMIX называется таблицей.

(ROW) Строка — запись, состоящая из полей — столбцов. В каждом поле сидит его значение, либо значение NULL — «пусто». Строк в таблице может быть сколько угодно. Физический порядок их расположения друг относительно друга не определен.

(COLUMN) Столбец — Каждый столбец в таблице имеет собственные имя и тип.

В INFORMIX допустимы следующие типы столбцов:
где qualif \in , возможны и другие типы, в зависимости от реализаций.
Реляционная модель идейно проста — основной ее принцип — «База Данных обеспечивает хранение и выдачу информации из своих прямоугольных таблиц, и ничего не желает знать о логической структуре данных, которую на ней реализовывают.» Все логические отношения и связи между разными таблицами вынесены наружу «Исполнителя База Данных» и отданы на откуп программисту. Такой подход позволяет просто и эффективно реализовывать саму базу данных, соответственно и быстродействие и мощность реляционных баз удается держать на весьма высоком уровне.
Физическую целостность базы данных обычно поддерживает сам метод доступа, сервер базы.
Но логическая (внешняя) структура пользовательской базы может быть весьма сложной. Для поддержания целостности логической структуры базы данных в реляционной базе имеется механизм транзакций. Транзакция — это объединение нескольких простых операторов манипулирования данными, которые должны быть выполнены только вместе, а если это не удается, то база вернется в состояние, предшествующее выполнению самого первого из них.

Понятия, Связанные с Реляционной Базой Данных.

Реляционные Методы Манипулирования Таблицами.

Запросы на поиск. Соединение таблиц. Внешнее соединение таблиц.

Представьте, что вам нужно в таблице «КАДРЫ» хранить данные о личном составе предприятия. О каждом человеке желательно помнить его фамилию, зарплату, пол, и т.д., а также название подразделения, в котором он работает. Но название подразделения длинно, самих подразделений немного, зато строк в таблице «КАДРЫ» много. Для экономии места применяется стандартный реляционный метод: в таблице «КАДРЫ» хранится не само название, а только его короткий код, и создается еще одна таблица, с помощью которой мы будем определять название подразделения по его коду. Табличку эту мы, естественно, назовем «ПОДРАЗДЕЛЕНИЯ», и будет в ней всего два столбца: столбец «КОД» и второй «название подразделения». И вот мы подошли к самому главному, что есть в реляционной базе, к «СОЕДИНЕНИЮ ТАБЛИЦ» по значению столбца (оно же соединение по ключу, по коду). Когда нам потребуются данные о человеке по фамилии Иванов, мы захотим увидеть название подразделения в котором он работает, а не малопонятный код. Значит поиск данных об Иванове нужно вести в двух таблицах. Сперва мы найдем в таблице «КАДРЫ» строчку, в которой в столбце ФАМИЛИЯ стоит значение «Иванов», мы возьмем в этой строчке значение кода подразделения, и будем искать в таблице «ПОДРАЗДЕЛЕНИЯ» строку, со значением в столбце КОД равным коду из строки с Ивановым. А найдя мы соединим данные из строки с Ивановым из первой таблицы и из строки с кодом из второй, получив одну, общую строку.

Индекс таблицы — это вспомогательный файл, который создается для того, чтобы ускорить выполнение реляционных операций с участием этой таблицы. Индекс строится для определенного столбца (или совокупности столбцов) таблицы. Он состоит из пар вида <Значение столбца, физический адрес соответствующей строки на диске>и отсортирован по значению столбца в порядке возрастания или убывания. Для одной и той же таблицы может существовать несколько индексов. Не следует путать индекс, построенный для совокупности нескольких столбцов таблицы, и несколько различных индексов, построенных для отдельных столбцов.
Если индекс создан, то при выполнении операций с таблицей система будет пользоваться им автоматически.
Перечислим коротко ряд рекомендаций по индексации таблиц.

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

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

Системный Журнал (logfile), он же журнал транзакций.

Запись всех изменений, происходящих в базе данных.
Контрольные точки.
Откатка.
Транзакции.
Оператор начала транзакции.
Оператор нормального завершения транзакции.
Оператор ненормального завершения транзакции — откатка к предыдущей контрольной точке.
Базы данных в режиме ANSI.
Прогон базы данных по системному журналу из backup копии.

Многопользовательский Режим Обработки Базы Данных.

Блокирование (locking), оно же запирание.

Сервер базы блокирует строки и таблицы во избежание коллизий при записи в одну и ту же строку. Строки блокируются автоматически (неявно). В блокированную строку нельзя писать, но можно смотреть. На время выполнения транзакции все измененные в ней строки блокируются. Можно заблокировать всю таблицу целиком явной командой блокировки. Таблицы можно блокировать не только от записи, но и от просмотра. Программе можно задавать режим:
ждать/ не ждать блокированных строк оператором SET LOCK MODE.

Права Доступа в Базе Данных, которые могут иметь пользователи. На уровне базы данных определены следующие уровни прав:

  • пользовательский
  • право на ресурсы
  • администратор базы данных

На уровне таблиц можно давать пользователям (и отбирать у них) следующие привилегии:

  • читать строки
  • вставлять строки
  • изменять строки
  • уничтожать строки
  • переопределять таблицу.

SQL — Структурный Язык Запросов.

ANSI (American National Standart Institute) разработал Структурный Язык Запросов (SQL) — Structured Query Language для определения, манипуляции и контролирования данных. Содержит 4 группы операторов.
В SQL выделяются следующие группы операторов:

  • Операторы описания данных: CREATE, DROP, ALTER
  • Операторы манипуляции данными: INSERT, DELETE, SELECT, UPDATE
  • Операторы задания прав доступа: GRANT / REVOKE , LOCK / UNLOCK , SET LOCK MODE
  • Операторы защиты и восстановления данных, и прочие: Transaction, Audit trail, Logfile

Язык Программирования 4-го Поколения 4GL.

Включает следующие возможности:

  • Средства стандартных языков программирования 3-го поколения.
  • Программные переменные.
  • Изготовление операторов по ходу программы.
  • Операторы манипуляции базой данных. SQL.
  • Переброска данных из базы данных в программные переменные, и обратно.
  • Курсоры.
  • Печать результатов запросов. Отчеты.
  • Экранный обмен с пользователем.
  • Меню. Окна.
  • Экранные формы, экранные поля, экранные массивы.
  • Файл описания экранной формы.
  • Файлы с исходными текстами.
  • Описание состава программы.

Продукты INFORMIX для Обработки Баз Данных.

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


    Серверы Базы Данных:

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

INFORMIX-NET.
Сетевое дополнение к INFORMIX-SE. Превращает его в сервер удаленных баз данных. Работает поверх сетевого протокола TCP/IP.

INFORMIX-OnLine.
Современный OLTP (On-Line-Transaction-Processing) базис баз данных. В отличие от SE взял физическое хранение данных в свои руки, отнимает у операционной системы логический раздел(ы) диска и самостоятельно работает с ними на низком уровне. В 2-4 раза быстрее SE, однако более сложен в настройке на конкретный компьютер, требует от администратора OnLine базы данных хороших познаний в операционной системе. Имеет много дополнительных возможностей по сравнению с SE, в том числе хранение таких типов данных как Binary Large Objects, текстовых и байтовых, а также символьных строк переменной длины как полей в базе данных, разделяемую буферизацию данных, буферизацию транзакций, зеркальный диск, автоматическое восстановление после системных сбоев, автоматический backup.

INFORMIX-STAR.
Сетевое дополнение к INFORMIX-OnLine. Превращает его в сервер распределенной базы данных. Разрешает одновременный доступ к данным нескольких INFORMIX-STAR систем. Работает поверх протокола TCP/IP. Естественно, в MS-DOSе отсутствует.
Диалоговая Система Поддержки и Администрирования Баз Данных для Подготовленных Пользователей.

INFORMIX-SQL.
Система управления и поддержки баз данных. Содержит 5 компонент:

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

    INFORMIX-4GL.
    Основное инструментальное средство для программирования баз данных и создания пользовательских приложений на языке 4GL. Включает:

    • компилятор с 4GL на Си и далее, в машинный код.
    • генератор и компилятор экранных форм 4GL.
    • описание и поддержка многомодульных программ (а ля make).
      Все это собранно в единую интегрированную среду-оболочку поддержки программирования, управляемую меню.

    Язык 4GL содержит в себе:

    1. Операторы SQL — для работы с данными.
    2. Программные операторы — IF, CASE, CALL, FUNCTION, FOR, WHILE, GOTO, DEFINE и т.д.
    3. Операторы экранного обмена — меню, окна, ввод-вывод через экранные формы и экранные массивы.
    4. Перехват и обработка исключительных ситуаций

    INFORMIX-4GL-RDS+ID.
    Rapid Development Sistem and Interactive Debugger
    То же самое, только компилируются программы гораздо быстрее, но не до выполнимого кода, а до «псевдо»-кода, который выполняется runner’ом. Диалоговый Отладчик 4GL программ использует «псевдо»-код.

    INFORMIX-ESQL/C.
    EmbeddedSQL/C (Вложенный в Си SQL)
    Система Программирования Баз Данных на языке Си, к которому добавлены операторы языка SQL.

    • представляет собой компилятор-препроцессор, который превращает программный код, написанный на ESQL/C (Си с SQL), в чистый Си код, который дальше транслируется обычным Си компилятором. Не имеет среды-оболочки.

    C-ISAM.
    Библиотека подпрограмм индексно-последовательного метода доступа для языка Си, на котором реализован INFORMIX-SE.
    Обеспечение Пользовательских Приложений INFORMIX

    INFORMIX-RunTime.
    Компонента INFORMIX, которую необходимо установить на машину, чтобы на ней могли работать пользовательские программы, изготовленные с использованием средств разработки INFORMIX. (SQL,ESQL, 4GL — для каждого из них требуется своя собственная версия RunTime). Представляет собой совокупность служебных файлов с системными сообщениями, программ, интерпретирующих псевдокод для 4GL-RDS, и программ выполнения отчетов, меню и экранных форм INFORMIX-SQL. Дополнительно к INFORMIX-RunTime необходимо иметь один из серверов базы данных SE или OnLine, поставляемых как отдельные продукты.
    Системы Автоматизации Конторской Деятельности

    Wingz.
    Электронная таблица с трехмерной графикой для обработки данных большого объема (32.768 строк и столько же столбцов, до биллиона элементов). Таблица дополнена возможностями 140 встроенных функций на все случаи жизни и собственным языком HiperScript для написания прикладных программ. Может выполнять как стандартные операции над базами данных, так и строить объемные цветные иллюстрации достижений вашей деятельности. Возможно вращение и движение по графическому изображению, использование данных из баз данных INFORMIX. Позволяет в рабочей области, на одной странице разместить тексты, числа, графы и графические изображения.

    SmartWareII.
    Интегрированная система, объединяющая базу данных, электронную таблицу с деловой графикой, текстовый процессор и сетевые возможности. Имеется собственный язык для создания пользовательских программ. Используется язык QBE для доступа к базе данных. В создании отчетов могут участвовать до 100 файлов. Текстовый процессор имеет словарь синонимов и может выполнять орфографическую проверку написания 140.000 слов, конвертирует файлы из других редакторов. Работает в операционных системах UNIX и DOS. Могут использоваться данные из баз данных INFORMIX-SQL.
    Прочее.

    INFORMIX-QuickStep.
    Интерактивный генератор отчетов. Позволяет создавать отчет без программирования, используя систему выпадающих меню. На выходе получается отчет, а также генерится программа на 4GL, этот отчет производящая.

    INFORMIX-FORMS.
    Генератор экранных форм и программного 4GL кода для работы с ними. Для INFORMIX-4GL.

    INFORMIX-Menu.
    Позволяет сгенерить систему меню, объединяющую прикладные программы на уровне вызова выполнимых модулей и Shell-процедур.

    INFORMIX-Optical.
    Пакет обеспечивает создание и работу с информационно поисковыми системами, использующими INFORMIX-Online (и его возможности по хранению изображений, звуков, и т.п. BLOBов) в качестве средства хранения, индексирования и доступа к данным, «зашиваемым» на перезаписываемых оптических WORM дисках (Write Once Read Many).
    Новое поколение — графические средства разработки.

    INFORMIX-GX.
    Графический интерпретатор псевдокода, получаемого в результате быстрой компиляции 4GL-программ. Выполняет псевдо-код не в алфавитно-цифровом режиме, а в графическом окошке — клиенте MS-Windows 3.1 или X-Window OSF/Motif. Позволяет понизить скорость перерисовки экрана прикладной программы на SPARC-станции до уровня алфавитно-цифрового терминала. Для INFORMIX-4GL Compiler поставляется в виде библиотеки, замещающей стандартные функции библиотеки curses для рисования на АЦТ.

    INFORMIX-4GL for Windows.
    Старый знакомый 4GL версии 4.12, только в среде MS Windows. Включает компилятор 4GL и среду-оболочку для программиста со стандартным для MS-Windows мышино-оконным интерфейсом.


    INFORMIX-NET/PC.
    Позволяет клиентским приложениям на PC обращаться к удаленным базам данных, расположенных на Unix серверах: умеет обращаться как к SE+NET, так и к OnLine+Star. В пакеты INFORMIX for MS Windows (Hyperscript, 4GL, NewEra) включается по умолчанию.

    INFORMIX-HyperScript.
    Система, не имеющая ничего общего с линией SQL продуктов фирмы INFORMIX. Это система для организации доступа к разнообразным данным (графическим, текстовым, мультимедиа) в виде гипертекста. Имеет собственный язык для описания/программирования приложений. Имеет графический генератор диалогов (наподобие Visual Basic). Есть версии для Unix/X-Windows, NeXT, MS/Windows, Macintosh. Является естественным развитием-продолжением пакета Wingz. Wingz включен как подмножество HyperScript

    INFORMIX-ViewPoint/DBA.
    Нечто вроде ISQL для MS Windows. Экранные (пардон, графические) формы для работы с базой данных INFORMIX. С естественными для графических приложений прибамбасами (шрифты, картинки, мышка.) Генератор отчетов. Средство гля генерации-разработки этих самых форм. DBA — дополнительный пакет администратора базы данных, необходимый для работы ViewPoint, DBA используется, чтобы создать так называемое SuperView — этакая единая квази-таблица -структура, описывающая логику и взаимоотношения таблиц и полей базы данных. ViewPoint нужно по одному на каждого пользователя, а DBA необходимо и достаточно одного на всю локальную сеть.

    INFORMIX-NewEra.
    Условное название — INFORMIX-4GL++. Объектно-ориентированный язык программирования. Позволяет программировать графические прикладные интерфейсы для баз данных INFORMIX. Частично совместим с 4GL (кроме операторов экранного обмена). Наконец-то INFORMIX сделал шаг в сторону разработчиков графических приложений. Имеются версии для Motif X-Windows и для MS-Windows.

    Выбор и назначение компонентов Microsoft SQL Server

    При работе с информационными системами ранее установленными системными администраторами, достаточно часто можно столкнуться с ситуацией, что на сервере, где требуется исключительно управление базами данных — установлены все компоненты, которые поставляются в дистрибутиве SQL сервера. На резонный вопросе: «Зачем установлены все компоненты?», можно получить ответ «Я всегда ставлю все компоненты» или «Я не знаю зачем нужен каждый из компонентов, поэтому на всякий случай установил все».
    Понятно, что такой подход в корне неверный, так как на сервере работают службы, которые никем и никогда не используются, и эти службы, в свою очередь впустую используют вычислительные ресурсы сервера, что может негативно отражаться на производительности самого сервера, так и хоста виртуализации (в случае если SQL сервер виртуализован).

    Ниже представлен список основных компонентов поставляемых в дистрибутиве SQL сервер, их краткое описание и назначение:

    Database Engine Services (Службы компонента Database Engine или Службы ядра СУБД) — это основная служба для хранения, обработки и защиты данных, репликации, полнотекстового поиска, средств управления реляционными и XML-данными, а также Data Quality Services SQL сервера (DQS). К службам Database Engine можно доустановить необязательные компоненты, если этого требует функционал SQL сервера:

    • Replication (Репликация): Этот компонент представляет собой набор технологий копирования и распространения данных и объектов баз данных между базами данных, а также синхронизации баз данных для поддержания согласованности.
    • Full-Text Search (Полнотекстовый и семантический поиск): Этот компонент позволяет выполнять полнотекстовые запросы по таблицах SQL сервера для произвольных символьных данных.
    • Data Quality Services (Служба качества данных): этот компонент, который дает возможность обнаруживать несогласованные и неверные данные в источнике данных и предоставляет компьютеризированные и интерактивные методы очистки данных.

    Analysis Services (Службы анализа) — эта служба содержит инструменты для создания приложений оперативной аналитической обработки (online analytical processing (OLAP)) и приложений интеллектуального анализа данных, а также средства управления ими. Это его принципиально отличиает от компонента Database Engine, который работает в режиме оперативной обработки транзакций (online transaction processing (OLTP)).

    Reporting Services – Native (Службы отчетов в собственном режиме) — эта служба использует серверные и клиентские компоненты для создания и развертывания табличных, матричных и графических отчетов и отчетов в свободной форме, а также управления ими.

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

    Reporting Services – SharePoint (Службы Reporting Services в режиме SharePoint) это серверное приложение для создания, управления и доставки отчетов по электронной почте, но в отличии от Reporting Services – Native, в режиме SharePoint, функции просмотра отчетов и управления ими интегрированы в продукты SharePoint.

    Reporting Services Add-in for SharePoint Products (Надстройка для службы отчетов для продуктов SharePoint) — содержит компоненты интерфейса пользователя и средства управления, позволяющие интегрировать продукт SharePoint с Службами Reporting Services сервера отчетов в режиме интеграции с SharePoint

    Data Quality Client (Клиент Data Quality) — это отдельное приложение, которое подключается к серверу DQS и обеспечивает интуитивный графический пользовательский интерфейс для очистки данных, выполнения операций подбора данных и административных задач в DQS.

    Client Tools Connectivity (Средства связи клиентских средств) — В клиентские средства входят компоненты, предназначенные для обеспечения взаимодействия между клиентами и серверами, в том числе сетевые библиотеки для DB-Library, OLEDB для OLAP, ODBC, ADODB и ADOMD+.

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

    Client Tools Backward Compatibility (Обратная совместимость клиентских средств) — обеспечивается следующими компонентами: SQL Distributed Management Objects (SQL-DMO) и Decision Support Objects (DSO).

    Client Tools SDK (Пакет SDK клиентских средств) Содержит пакет средств разработки программного обеспечения, содержащий ресурсы для программистов.
    Documentation Components (Документация) Компоненты документации содержат компоненты для просмотра и управления содержимым справки.

    Management Tools — Basic (Средства управления — базовый набор). К этим средствам относятся следующие инструменты:

    • Среда SQL Server Management Studio
    • Поддержка Компонент SQL Server Database Engine,
    • SQL Server Express, служебной программы sqlcmd
    • SQL Server поставщика PowerShell

    Management Tools — Full (Средства управления — полный набор) — помимо компонентов базовой версии, сюда также входят:

    • Поддержка среды Среда SQL Server Management Studio для служб Reporting Services
    • Analysis Services
    • Integration Services
    • Приложение SQL Server Profiler
    • Database Engine Tuning Advisor
    • SQL Server Управление программой

    Distributed Replay Controller (Контроллер распределенного воспроизведения) Контроллер распределенного воспроизведения управляет согласованными действиями клиентов распределенного воспроизведения. В каждой среде распределенного воспроизведения можно установить только один экземпляр контроллера.

    Distributed Replay Client (Клиент распределенного воспроизведения) Клиенты распределенного воспроизведения работают совместно для имитации рабочей нагрузки на экземпляре SQL Server. В каждой среде распределенного воспроизведения можно установить один или несколько клиентов.

    SQL Client Connectivity SDK (Пакет SDK для подключения клиентов SQL) Содержит пакет SDK для подключения собственных клиентов Native Client SQL Server (ODBC/OLE DB) для разработки приложений баз данных.

    Master Data Services (Службы Master Data ) Службы Службы Master Data Services — это платформа для интеграции данных из различных систем на предприятии в единый источник основных данных для повышения точности и удобной организации аудита. При выборе параметра Службы Master Data Services устанавливается Диспетчер конфигурации Master Data Services, сборки, оснастка Windows PowerShell, папки и файлы для веб-приложений и служб.

    По умолчанию экземпляр SQL сервера и общие компоненты (shared components) устанавливаются в каталог %Program Files%\Microsoft SQL Server\. Изменить путь установки можно в процессе начальной установки SQL сервера. Изменение пути установки для одного общего компонента приводит к его изменению для всех остальных общих компонентов. При последующей доустановке общих компонентов, они будут устанавливаться в каталог, заданный при начальной установке.

    Sql informix sdk

    I’m attempting to use an ODBC connection from a Windows 2012R2 server to an Informix server using the clientsdk.4.10.TC6DE.WIN install.

    However, when I try to test my ODBC connection, I get the following error:

    ‘Test connection was NOT successful.[Informix][Informix ODBC Driver][Informix]Unable to load locale categories.’

    If the locale data was correctly installed, where should it be? I don’t see a gls folder in my INFORMIXDIR.

    setnet32 seems to have the correct locale values, and matches what is in the registry.

    Does anyone have any ideas on how I might resolve this issue? Am I installing the correct driver? I’m using clientsdk.4.10.TC4DE.WIN on Windows Server 2008 machines without any problems.

    I want to use the driver with SSIS so I’m installing the 32 bit version.

    32 bit vs 64 bit system — Informix Client SDK

    If I create an Informix SSIS package in my 32 bit system using 32 bit Client SDK and deployed it in 64 bit server. Will it create issue?

    2 Answers 2

    If a package is using 32-bit components (e.g. OLEDB libraries), you will need to choose the «32-bit runtime» option on the Advanced tab when running the package on a 64-bit server.

    (I know this is an old question; just adding an answer for posterity.)

    You would need the 32-bit CSDK or Informix Connect (effectively the CSDK Runtime) on the 64-bit system (as well as a 64-bit CSDK or I-Connect for 64-bit applications). Apart from that, I believe that it should work. If it runs into problems, it is because of the Windows registry and the need for separate INFORMIXDIRs.

    On 64-bit Unix systems, it was common to have both 32-bit and 64-bit versions of Informix CSDK or I-Connect on a single machine in separate INFORMIXDIRs. These days, there is less 32-bit code — there are no longer any 32-bit servers on 64-bit Unix systems, and usually there isn’t a 32-bit CSDK either.

    Not the answer you’re looking for? Browse other questions tagged informix or ask your own question.

    Hot Network Questions

    Subscribe to RSS

    To subscribe to this RSS feed, copy and paste this URL into your RSS reader.

    site design / logo © 2020 Stack Exchange Inc; user contributions licensed under cc by-sa 4.0 with attribution required. rev 2020.11.11.35402

    Язык SQL (на примере диалекта Informix)

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

    5.1. Типы данных, доступные в SQL

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

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

    В варианте SQL, реализованным фирмой Informix, имеются следующие типы данных:

    INTEGER и SMALLINT — целое и короткое целое. Для их представления используется, соответственно, 4 и 2 байта.

    Следовательно, диапазон допустимых значений для INTEGER будет от -2 147 483 647 до 2 147 483 647, а для SMALLINT — от -32 767 до 32 767. Данный тип используется для представления счетчиков, кодов чего-либо и т.д. К недостаткам данного типа данных следует отнести ограниченный набор значений. Впрочем, это не проблема, если Вы уверены, что для Вашего поля допустимый диапазон значений данного типа подходит.

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

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

    Объем памяти (число байтов), выделяемых для хранения значений данного типа зависит от компьютера, но обычно составляет 8 байт для типа FLOAT и 4 байта для SMALLFLOAT. При таком представлении точность типа FLOAT составляет 16 десятичных цифр, а SMALLFLOAT — 8 цифр.

    Типы FLOAT и SMALLFLOAT имеют одну особенность — из-за внутреннего двоичного представления нельзя гарантировать, что значения данного типа будут представлены точно. Поэтому данный тип нельзя использовать для хранения денежных сумм или других величин, которые нельзя округлять. Для хранения «неокругляемых» значений используются другие типы данных — DECIMAL и MONEY.

    DECIMAL(p) — этот тип аналогичен FLOAT, но предназначен для хранения данных с фиксированным числом значащих цифр. Например, если Вы знаете, что некоторое значение всегда измеряется с точностью до пяти знаков, то это как раз тот тип данных, который Вам нужен. Чмсло значащих цифр (параметр ‘p’) может быть в пределах от 1 до 32. Диапазон допустимых значений для данного типа находится в пределах от 10^-128 до 10^126.

    Для представления данных типа DECIMAL(p) используется двоично-десятичное представление. Следовательно, число байтов, необходимое для представления одного значения типа DECIMAL(p) будет равно 1+p/2.

    По сравнению с типом FLOAT тип DECIMAL(p) имеет следующие преимущества:

    — можно регулировать точность представления;

    — размер требуемой памяти зависит от точности;

    Но есть у типа DECIMAL(p) и недостатки по сравнению с типом FLOAT:

    — операции сортировки и арифметические операции требуют большего, чем для типа FLOAT, времени;

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

    DECIMAL (p,n) — этот тип предназначен для хранения данных не только с фиксированным числом значащих цифр, но и с фиксированным числом значащих цифр после запятой. Параметр ‘p’ задает общее число десятичных цифр в числе, а ‘n’ — сколько десятичных цифр будет после запятой. Таким образом, данный тип данный позволяет хранить числа, в десятичном представлении которых не более 32 цифр. Размер памяти для хранения одного значения данного типа составляет 1+p/2 байт.

    По сравнению с типами INTEGER и FLOAT тип DECIMAL(p,n) имеет те же достоинства и недостатки, что и тип DECIMAL(p).

    MONEY (p,n) — этот тип предназначен для хранения денежных величин. Он полностью аналогичен типу DECIMAL(p,n), но отличается от него тем, что в некоторых языках (например, INFORMIX 4GL) существуют специальные способы форматирования для этого типа на основе некоторых внешних по отношению к программе переменных окружения. Это позволяет писать программы, не зависящие от способа написания денжных величин в каждой конкретной стране (например, величина 3000000 будет печататься или как $3,000,000.00 или как 3.000.000,00Рбл).

    DATE — этот тип предназначен для хранения дат. По сути, значение этого типа представляет собой число дней, прошедших с 31 декабря 1899 года. Так как это значение может быть отрицательным, то можно хранить и даты до 1899 года. Под значения этого типа выделяется 4 байта, поэтому диапазон допустимых значений очень широк — около 58 000 столетий вперед и назад. Так же как и для типа MONEY, форматирование ввода и вывода для переменных данного типа может быть указано с помощью внешних переменных.

    DATETIME — этот тип предназначен для хранения точных моментов времени. Тип DATETIME содержит информацию о годе (YEAR), месяце (MONTH), дне (DAY), часе (HOUR), минуте (MINUTE), секунде (SECOND) и долях секунды (FRACTION). Вы может выбирать нужный диапазон этих значений. Например, если Вам нужен момент времени с точностью до секунды в течении дня, то следует указать тип DATETIME HOUR TO SECOND. Если же Вам нужна информация о событиии с точностью до минуты, но в произвольном году, то требуемый тип должен записываться как DATETIME YEAR TO MINUTE. При указании долей секунды, надо указывать точность представления — от десятых до тысячных. Соответственно, FRACTION(1) указывает время с точностью до десятых, FRACTION(2) — до сотых, а FRACTION(3) — до тысячных.

    По сравнению с типом DATE тип DATETIME может хранить данные более точно, однако он требует больше места для хранения и обрабатывается более медленно.

    INTERVAL — тип данных для хранения временных интервалов. Значение типа INTERVAL получается, когда, например, из одной даты вычитают другую. Так же как и DATETIME, следует уточнить диапазон возможных значений.

    CHAR и CHAR(n) — тип для хранения символьных строк фиксированной длины. Для типа CHAR(n) параметр ‘n’ задает длину строки. Тип CHAR без параметра является одиночным символом и идентичен типу CHAR(1). Максимальная длина данного типа (значение параметра ‘n’) равно 32 511. Для хранения данных данного типа всегда отводитя n байт вне зависимости от реальной длины строки.

    VARCHAR(m) — короткая (до 255 символов) символьная строка переменной длины. Параметр ‘m’ задает максимальную длину строки, но не более 255. Для хранения строк переменной длины произвольного размера испльзуеся тип TEXT. При хранении значений типа VARCHAR используется столько байтов, сколько реально занимает строка.

    По сравнению с типом CHAR(n), тип VARCHAR имеет то преимущество, что экономится место при хранении строк разной длины. Но такой способ хранения замедляет операции модификации данных.

    TEXT — этот тип предназначен для хранения символьных строк произвольной переменной длины. По сравнению с типом VARCHAR тип TEXT имеет повышенные накладные расходы, но не имеет практического ограничения по длине, Например, для сервера Informix DS максимальная длина значения типа TEXT составяет 2 Гигабайта.

    BYTE — тип для хранения двоичных объектов произвольного объема. Этот тип данных можно (и нужно) использовать для хранения исполняемых файлов, оцифрованных картинок, звука и т.д. Максимальная длина значения данного типа (для сервера Informix DS ) ограничена 2 Гигабайтами.

    У каждого из перечисленных выше типов данных свои операции, свои значения. Но существует одно общее для всех типов значение ­NULL. Это значение «не определено». Не надо путать это значение с нулем. Значение «нуль» — это вполне определенное значение, а NULL означает, что никакого определенного значения в атрибут или в переменную никогда не записывалось или явно было присвоено значение NULL.

    5.2. SQL-операторы создания схемы базы данных

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

    Первым делом надо создать саму базу данных. Для этого используется оператор

    В качестве имени базы данных Вы можете использовать любое имя, состоящее из латинских букв, цифр и символа подчеркивания. Имя должно начинаться с буквы или символа подчеркивания. Если используемые SQL-сервер и операционная система обеспечивают NLS (National Language Support — поддержка национальных языков)), или SQL-сервер поддерживает GLS (Global Language Support – глобальная поддержка языков), то Вы можете использовать для именования и национальные символы. Кстати, большие и маленькие буквы не различаются. Максимальная длина имени составляет 14 символов. Вы не можете создать на одном SQL-сервере две базы данных с одинаковым именем.

    CREATE DATABASE my_base

    В SQL существует понятие «текущая база данных». После того, как Вы создали базу данных оператором CREATE DATABASE, она становится текущей. Все указываемые таблицы, все индексы берутся из текущей базы данных. В каждый момент времени для каждой задачи существует единственная текущая база данных (или таковой не существует вовсе). Это не означает, что Вы не можете обратиться к таблице из другой базы данных, просто при обращении к такой таблице надо кроме имени таблицы, еще указывать и имя базы данны, а если база данных расположена на другом сервере, то и имя сервера базы данных.

    Сразу опишем и обратный оператор — оператор удаления базы данных

    DROP DATABASE my_base

    Естественно, удаляемая база должна существовать и Вы должны иметь право на ее удаление (но о правах несколько позднее). Кстати, Вы не можете удалить текущую базу.

    Если база данных уже существует, то сделать ее текущей (открыть ее) можно оператором

    Закрытие текущей базы данных производится оператором

    Итак, предположим, что база данных создана. Для того, чтобы создавать таблицы используется оператор CREATE TABLE. Его синтаксис

    CREATE TABLE (
    [ NOT NULL],
    [ NOT NULL]

    То есть Вы указываете имя таблицы, а затем в скобках через запятую перечисляете названия полей и их тип. Если Вы хотите сказать серверу, что какое-то поле обязательно должно иметь какое-то определенное значение, то после типа надо добавить слова NOT NULL.

    Например, для создания таблицы, содержащей уникальный код, название и адрес призводителей товара, надо выполнить оператор

    CREATE TABLE companies (
    company_id SERIAL,
    name CHAR(40) NOT NULL,
    address CHAR(60)

    При создании данной таблицы мы явно указали, что поле name (название) не может быть неопределенным, но поле address (адрес) вполне может быть неопределено.

    Теперь создадим таблицу для описания товаров. В ней надо указать ссылку на производителя, название товара, его стоимость:

    CREATE TABLE items (
    item_id SERIAL,
    company INTEGER NOT NULL,
    name CHAR(40) NOT NULL,
    price MONEY(20,2)

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

    Оператор удаления таблицы очень похож на оператор удаления базы данных:

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

    Добавление новых полей в твблицу производится следующим образом:

    ALTER TABLE ADD
    ( [ NOT NULL]

    Например, если для поставщика товаров (таблица ‘companies’) надо добавить и контактный телефон, то следует выполнить следующий оператор:

    ALTER TABLE companies ADD (phone CHAR(35))

    Для удаления ненужных полей надо выполнить другой вариант оператора ALTER TABLE:


    ALTER TABLE DROP ( [, . ] )

    Существует вариант этого оператора и для модификации поля:

    ALTER TABLE MODIFY
    ( [ NOT NULL]

    При изменении типа поля производится автоматическая преобразование значений к новому типу. В частности, если тип меняется с целого на символьное, то число 2385 будет преобразовано в строку ‘2385’.

    5.3. Основные SQL-операторы для доступа и модификации данных

    Итак, структура базы данных создана. Надо как-то начинать работать с базой данных — заносить новые данные, извлекать существующие и т.д. Существует четыре основных оператора манипулирования данными — SELECT, INSERT, UPDATE и DELETE. Примеры, которые быдут сопровождать рассказ об этих операторах будут основываться на предположении, что в нашей текущей базе данных есть таблицы companies и items (см. параграф 5). В таблице companies хранится информация о производителях товаров, а в таблице items — о самих товарах.

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

    Оператор INSERT вставляет в таблицу новую запись:

    После имени таблицы в скобках надо указать те поля, которым мы хотим присвоить некоторе значение явно. После ключевого слова VALUES в скобках указан список значений для перечисленных полей. Число значений в этом списке должно соответствовать числу указанных полей. Полям, не перечисленным в списке (за исключением поля типа SERIAL) присваивается значение NULL.

    Полю типа SERIAL, если его нет в списке или его значением указано 0, присваивается новое уникальное значение. Если для поля типа SERIAL указано отличное от нуля значение, то СУБД использует указанное значение.

    Например, последовательность операторов

    INSERT INTO companies (name) VALUES («АО Рога и Копыта»)

    INSERT INTO companies (name, address)

    VALUES («ТОО Добро пожаловать», «Энск, 5-е авеню»)

    INSERT INTO companies (company_id, name)

    VALUES (157, «АОЗТ Сделай Сам»)

    наполнит таблицу companies следующим содержанием

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

    USER — имя пользователя, который выполняет этот SQL-оператор;

    TODAY — дату выполнения этого оператора;

    CURRENT — момент времени, когда выполняется этот оператор.

    Например, если некоторая таблица под названием ‘protocol’ содержит описание некоторых действий и, в частности, поля when и who, указывающие когда и кто выполнил это действие, занесение новой записи в эту таблицу будет выглядеть так:

    INSERT INTO protocol (who, when, . )

    VALUES (USER, CURRENT YEAR TO MINUTE, . )

    Для модификации записей, которые уже есть в таблице, используется оператор UPDATE:

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

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

    В качестве условия могут использоваться логические выражения над константами и полями. В логических выражениях допускается испльзовать операции сравнения >, =, , !=. Для проверки поля на значение NULL используются логические операции IS NULL или IS NOT NULL. Отдельные логические операции могут быть соединены связками AND, OR, NOT и сгруппированы с помощью скобок. Примеры правильных условий:

    price > 200 OR name = «кеды»

    (name IS NULL AND address IS NULL) OR (name = «АО Рога и Копыта»)

    Использование в SQL логических связок и операций сравнения аналогично логическим выражениям в обычных языках программирования. Представляет интерес использование в операциях сравнения и в логических связках значения NULL (неопределено). Если NULL появляется в операциях сравнения ( , [WHERE ]

    Использование ключевого слово WHERE, задающего условие на записи, подлежащие удалению, аналогично его использованию в операторе UPDATE. Точно так же, если условие не задано, то удалены будут все записи из таблицы.

    Предположим, фирма «АОЗТ Сделай Сам» разорилась, больше не поставляет нам ни одного товара, и мы хотим удалить ее из списка поставщиков. Это может быть сделано оператором:

    DELETE FROM companies WHERE name = «АОЗТ Сделай Сам»

    Теперь рассмотрим оператор выборки SELECT. На его долю приходится, наверное, более 2/3 всех SQL-операторов используемых в программах. Оператор SELECT выбирает из одной или нескольких таблиц множество значений, которое и является результатом его работы. К сложным, многотабличным вариантам этого оператора мы вернемся позже.

    Синтаксис оператора SELECT (сильно упрощенный вариант):

    SELECT [. ] FROM [WHERE ]

    В операторе SELECT указываются нужные Вам поля, имя таблицы (FROM), из которой производится выборка, и условие (WHERE), которому должны удовлетворять отобранные значения. Оператор SELECT просматривает все записи в таблице и отбирает те, которые удовлетворяют условию. Из отобранных записей берутся только те поля, которые указаны. Таким образом, результатом работы оператора SELECT будет набор значений, фактически таблица, содержащая часть полей и часть записей от исходной. В предельных случаях это может быть пустое множество (ни одна запись не удовлетворила условию) или одно единственное значение, если выбиралось одно поле и только одна запись удовлетворила условию.

    SELECT company_id, name, address FROM companies

    — результатом этого запроса будет вся таблица ‘companies’.

    — этот запрос возвращает одно единственное значение (1), а именно уникальный идентификатор фирмы «АО Рога и Копыта».

    SELECT name FROM companies

    — возвращает имена всех фирм-поставщиков, имеющихся в нашей базе данных и занесенных в таблицу ‘companies’.

    SELECT name, price FROM items WHERE company =
    (SELECT company_id FROM companies

    WHERE name = «АО Рога и Копыта»)

    — возвращает все названия товаров и их цены, поставляемые фирмой «АО Рога и Копыта».

    SELECT name FROM companies WHERE address IS NULL

    — возвращает названия тех фирм, у которых неизвестен адрес.

    5.4. Управление транзакциями

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

    В SQL имеются следующие операторы управления транзакциями:

    · BEGIN WORK — начать транзакцию;
    · COMMIT WORK — успешно завершить транзакцию;
    · ROLLBACK WORK — откатить транзакцию, то есть вернуть базу данных в состояние, которое она имела на момент начала текущей транзакции.

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

    Операторы управления транзакциями выполняет программа-клиент. Если она обнаруживает какую-то ошибку, то она может выполнить откат транзакции. В самом SQL нет управляющих структур (циклов, условных операторов, операторов перехода), поэтому приведем примеры на языке Informix 4GL, который содержит SQL как подмножество:

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

    Представим себе, что на компьютере, где исполняется программа-клиент, то есть, где работает пользователь, пропало напряжение. Программа-сервер обнаруживает «гибель» клиента и выполняет оператор ROLLBACK WORK сама, без явного указания от клиента.

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

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

    CREATE DATABASE WITH LOG

    — создание базы данных с транзакциями, и

    CREATE DATABASE WITH LOG MODE ANSI

    — создание базы данных с транзакциями в режиме ANSI.

    Если база данных была создана с параметром WITH LOG, то сервер начинает для данной базы данных отслеживать транзакции. Выполнение оператора COMMIT WORK, говорит серверу о начале транзакции, которая закончится по оператору COMMIT WORK или откатится по оператору ROLLBACK WORK в состояние на момент выполнения BEGIN WORK.

    Если база данных была создана в режиме ANSI, то есть в операторе CREATE DATABASE было указано WITH LOG MODE ANSI, то оператор BEGIN WORK не нужен. Транзакция открывается автоматически сразу после удачного или неудачного завершения предыдущей. Открытие базы данных автоматически открывает транзакцию.

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

    5.5. Продвинутые варианты оператора поиска

    5.5.1. Поиск по нескольким таблицам

    Оператор SELECT может осуществлять поиск сразу по нескольким таблицам. В этом случае после слова FROM в операторе SELECT надо указать таблицы,по которым производится поиск. Если в нескольких полях имеются одноименные поля (например, поле name присутсвует и в таблице companies, и в таблице items), то для устранения неясностей надо перед именем поля указать имя таблицы и символ точка.

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

    SELECT name, address, lname, fname FROM companies, persons

    WHERE company_ > В данном примере не возникает коллизий и запрос будет правильно отработан. Но если мы захотим выдать список товаров и названий фирм, их производящих, то, так как поле name присутствует и в таблице items, и в таблице companies, нам потребуется для поля name указывать таблицу:

    SELECT items.name, companies.name FROM persons, companies

    WHERE company = company_id

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

    Стоит обратить внимание, что при выборке из двух таблиц мы получаем все возможные комбинации значений, при этом из всех таблиц выбираются только те записи, которые удовлетворяют заданному условию. Например, в приведенном выше примере на получение информации о фирмах и людях, в них работающих, мы получим список только тех компаний, для которых в таблице persons есть хотя бы один человек. А если мы хотим в этом запросе получить информацию и о фирмах, для которых мы не знаем ни одного сотрудника? Для этого в разделе FROM оператора SELECT перед таблицей persons надо поставить слово OUTER:

    SELECT name, address, lname, fname FROM companies, OUTER persons

    WHERE company_ > Тогда такой оператор будет работать следующим образом. Он последовательно будет перебирать все записи из таблицы companies. Для каждой записи из companies будет просмотрена таблица persons. Как только будет найдена запись из persons, удосвлетворяющая условию WHERE, в результат добавится новый ряд, сформированный из полей name и address записи из companies и полей lname и fname записи из persons. Если же в persons не будет найдено ни одной записи, то при простом операторе SELECT информация о текущей записи из companies в результат не попадет, а в случае SELECT с OUTER — попадет, а поля lname и fname получат значение NULL:

    Таблица companies:
    ————-T———————-T——————¬
    ¦ company_id ¦ name ¦ address ¦
    +————+———————-+——————+
    ¦ 101 ¦ АО Рога и Копыта ¦ Одесса, п/я 13 ¦
    ¦ 105 ¦ ТОО Добро пожаловать ¦ Энск, 5-е авеню ¦
    ¦ 107 ¦ АОЗТ Сделай сам ¦ Городская свалка ¦

    Таблица persons:
    ———-T———-T———¬
    ¦ company ¦ lname ¦ fname ¦
    +———+———-+———+
    ¦ 101 ¦ Антонов ¦ Сергей ¦
    ¦ 105 ¦ Шапокляк ¦ Алексей ¦
    ¦ 102 ¦ Антонов ¦ Антон ¦
    ¦ 101 ¦ Бендер ¦ Остап ¦

    Результат простого оператора SELECT

    SELECT name, address, lname, fname FROM companies, persons

    WHERE company_ > ————————T——————T———-T———¬
    ¦ name ¦ address ¦ lname ¦ fname ¦
    +———————-+——————+———-+———+
    ¦ АО Рога и Копыта ¦ Одесса, п/я 13 ¦ Антонов ¦ Сергей ¦
    ¦ АО Рога и Копыта ¦ Одесса, п/я 13 ¦ Бендер ¦ Остап ¦
    ¦ ТОО Добро пожаловать ¦ Энск, 5-е авеню ¦ Шапокляк ¦ Алексей ¦

    Результат оператора SELECT с указателем OUTER

    SELECT name, address, lname, fname
    FROM companies, OUTER persons

    WHERE company_ > ————————T——————T———-T———¬
    ¦ name ¦ address ¦ lname ¦ fname ¦
    +———————-+——————+———-+———+
    ¦ АО Рога и Копыта ¦ Одесса, п/я 13 ¦ Антонов ¦ Сергей ¦
    ¦ АО Рога и Копыта ¦ Одесса, п/я 13 ¦ Бендер ¦ Остап ¦
    ¦ ТОО Добро пожаловать ¦ Энск, 5-е авеню ¦ Шапокляк ¦ Алексей ¦
    ¦ АОЗТ Сделай сам ¦ Городская свалка¦ NULL ¦ NULL ¦

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

    Действие, производимое оператором SELECT при выборке из двух таблиц называют слияние таблиц (join), а если производится слияние с OUTER, то такое слияние таблиц называется внешним (outer join). Внутри одного запроса можно использовать несколько таблиц с OUTER (кстати, такие таблицы называют подчиненными, а таблицы без OUTER — ведущими).

    5.5.2. Устранение повторения данных в операторе SELECT

    Отношение или таблица- это множество. Следовательно, в них могут содержаться повторяющиеся элементы (одинаковые записи). И если в реальных таблицах это практически никогда не встречается (ведь надо же как-то различать описания разных сущностей!), то в отношении, которое является результатом работы оператора SELECT такие повторения могут возникать достаточно часто. Если в формируемой с помощью оператора SELECT отношении Вам надо избавиться от повторений, то перед списком полей следует поставить ключевое слово DISTINCT или UNIQUE (это синонимы). В этом случае, если в выборке, удовлетворяющей условию WHERE, имеется несколько записей с одинаковыми значениями, то оператор SELECT исключит повторения. Например, если Вам требуется распечатать только номенклатуру поставляемых товаров, без учета того, что один и тот же вид продукта может пставляться разными поставщиками, Вам следует выполнить запрос:

    SELECT UNIQUE name FROM items

    Если в запросе выбирается два или более поля, то две записи считаются равными, если равны все их составляющие поля.

    5.5.3. Вычисления внутри оператора SELECT

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

    Выражения над полями

    Оператор SELECT может производить сразу вычисления. Над отдельными полями можно производить символьные иарифметические операции, а также функции. В вражениях можно использовать как значения полей, так и константы.

    К символьным операциям относится конкатенация двух строк и выделение подстроки. Операция конкатенации записывается в виде двух вертикальных черт: ‘||’. Операция выделения подстроки записывается с помощью квадратных скобок, в которых через запятую указан номер первого и последнего символа в подстроке. Рассмотрим примеры:

    SELECT lname[4,9] FROM companies

    «Рога и Ко»
    » Добро по»

    SELECT lname || » » || fname FROM persons

    «Антонов Сергей»
    «Шапокляк Алексей»
    «Антонов Антон»

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

    SELECT lname, fname FROM persons

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

    В последнем примере мы также использовали и текстовую константу — строку из одного пробела, заключенную в кавычки. Можно использовать строковые константы любой длины и состоящие из любых символов. Если внутри константы надо использовать символ «кавычки», то следует написать его подряд дважды. В символьных выражениях Вы можете использовать и не символьные выражения ­операция преобразования типов (например, числа 1234 в строку «1234») будет произведена автоматически.

    К арифметическим операциям относятся операции сложения (‘+’), вычитания (‘-‘), умножения (‘*’) и деления (‘/’). Пример:

    SELECT price*1.2 FROM items

    SELECT tax*quantity/1.2 FROM orders

    В выражениях внутри оператора SELECT имеется возможность вызывать функции. Некоторые доступные функции приведены в табличке.

    Функция определения длины:


    LENGTH( ) — вычисляет длину строки, которая

    LENGTH( ) содержится в указанном поле записи или указанной текстовой констант;

    DATE ( ) — преобразует выражение типа CHAR, INTEGER или DATETIMEв значение типа DATE;

    DAY( ) — по заданному в качестве аргумента значению вычисляет номер дня в месяце;

    MDY( , , ) — по заданным трем целым значением, определяющим номер месяца, дня, года строится значение типа DATE;

    MONTH ( ) — по заданному в качестве аргумента значению вычисляет номер месяца в годе;

    WEEKDAY ( ) — по заданному в качестве аргумента значению вычисляет номер дня недели, причем восвкресенью соответсвует значение 0, понедельнику — 1 и т.д.;

    YEAR ( ) — по заданному в качестве аргумента значению вычисляет номер года, состоящий из 4-х цифр;

    HEX ( ) — преобразует целое, заданное в качестве аргумента, в строку, представляющую шестнадцатеричную запись данного целого;

    ROUND ( ) — округляет с указанной точностью числовое выражение, заданное в качестве аргумента; точность определяет степень одной десятой, до которой надо округлять.

    TRUNC ( ) — обрезает незначащие цифры у заданного в качестве аргумента выражения, причем точность определяет смещение влево от запятой последней значащей цифры;

    ACOS( ) — арккосинус от заданного значения; ASIN( ) — арксинус от заданного значения;

    ATAN ( ) — арктангенс от заданного значения;

    COS( ) — косинус от заданного числа радиан;
    SIN( ) — синус от заданного числа радиан;

    TAN ( ) — тангенс от заданного числа радиан;
    ABS ( ) — абсолютное значение;

    EXP ( ) — вычисление заданной экспоненты;

    LOGN ( ) — вычисление натурального логарифма;
    LOG10 ( ) — вычисление десятичного логарифма;
    MOD ( , ) — остаток от деления;

    POW ( , ) — возведение в степень;

    ROOT ( , ) — вычисление корня заданной степени;

    SQRT ( ) — квадратный корень;

    CURRENT — возвращает точный момент времени (тип DATETIME), когда происхoдит обработка данного запроса; параметр задает (как и при описании типа DATETIME) требуемое точность — от года до тысячных секунды (например, CURRENT DAY TO SECOND);

    TODAY — возвращает дату (тип DATE) выполнения даного запроса;

    USER — возвращает имя пользователя (как он зарегестрировался в операционной системе), который выполняет данный запрос.

    Следует напомнить про специальное значение NULL — «не определено». Если это значение встретится в выражении, то и все выражение будет иметь значение NULL. Даже умножив нуль на NULL, Вы все равно получите NULL. Конкатенация любой строки с неопределенной строкой (то есть строкой, имеющей значение NULL) тоже в результате даст NULL.

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

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

    SELECT MAX(price) FROM items

    В этом запросе будут просмотрены все записи из таблицы items и будет определено максимальное значение среди всех полей price.

    Если мы хотим просто узнать, сколько записей получается в результирующей выборке, то надо использовать агрегатную функцию COUNT(*). Например, для того, чтобы получить число предложений поставок джинсов (сколько раз в таблице items имеется записей с названием товара «джинсы»), надо выполнить запрос

    SELECT COUNT(*) FROM items WHERE name=»джинсы»

    А если мы хотим получить пару значений — минимальная и максимальная цены на кеды, то это достигается следующим запросом:

    SELECT MIN(price), MAX(price) FROM items WHERE name=»кеды»

    Некоторые агрегатные функции перечислены в таблице:

    AVG( ) — среднее по всем значениям данного поля;
    COUNT(*) — количество записей;
    MAX( ) — максимум из всех значений данного поля;
    MIN( ) — минимум по всем значениям данного поля;

    SUM( ) — сумма всех значений данного поля.

    Приведенные выше примеры агрегатных функций работали со всей выборкой сразу — либо со всей таблицей, если условие не было задано, либо с записями, удовлетворяющими условию, если условие присутствовало. Но для агрегатных функций можно использовать и группирование по значениям полей. Для этого после условия (или после раздела FROM, если условие отсутствует) указывается раздел GROUP BY. В этом разделе, после слов GROUP BY, указываются имена полей, по которым надо проводить группировку. Например, нам хочется получить выборку, состоящую из названия товара и числа фирм, этот товар предлагающий и максимальной цены на данный товар. Для этого выполним запрос

    SELECT name, COUNT(*), MAX(price) FROM items GROUP BY name

    Полученные результаты могли бы быть, например, такими:

    кеды 2 3000.00
    джинсы 4 46000.00

    рога 1 999999.85

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

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

    SELECT name, COUNT(*), MAX(price) FROM items

    GROUP BY name HAVING COUNT(*) > 1

    и получим следующие результаты:

    джинсы 4 46000.00

    5.5.4. Логические выражения в условии SQL-операторов

    Когда выше обсуждались SQL-операторы и, в частности, раздел WHERE (условие), то были перечислены лишь некоторые возможные способы задания условия на выбираемые записи. В частности, были упомянуты операции сравнения (больше, меньше и т.д.) и логические связки (и, или, не). В условии поиска (в разделе WHERE) операторов SELECT, UPDATE и DELETE можно использовать и более интересные логические выражения.

    Сопоставление текстовых полей с образцом

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

    Образец для оператора MATCHES является текстовой строкой и строится по следующим правилам:

    · специальный символ ‘*’ соответсвует произвольной последовательности из 0 или более символов;
    · специальный символ ‘?’ соответсвует одному произвольному символу;
    · символы внутри квадратных скобок ([. ]) позволяют задавать удовлетворяющие образцу символы (например, [абв] или [аеиуюыя]) диапазон символов через дефис (например, [А-Я] или [0-9]), или с помощью символа ‘^’ указывать те символы, которые не удовлетворяют образцу (например, [^абв]);
    · специальный символ ‘\’ отменяет значение специального символа, указанного за ‘\’, например ‘\*’ означает именно символ ‘*’, а не произвольную последовательность символов;
    · все другие символы являются обычными и должны соответсвовать символам из сравниваемой строки.

    SELECT person_id FROM persons WHERE lname MATCHES «[Бб]ендер»

    будут выбраны номера записей (значения поля person_id) для лиц с фамилиями «Бендер» и «бендер».

    Если нам требуется отобрать все записи о товарах из таблицы items, в названии которых присутствует слово «грибы» (например, «Соленые грибы», «Грибы по-французски», «Грибы-мухоморы маринованные»), то такую задачу решает следующий запрос:

    SELECT item_id FROM items WHERE name MATCHES «*[Гг]рибы*»

    Операцию MATCHES, как и любую другую, можно использовать вместе с другими логическими операциями путем использования логических связок OR/NOT/AND и скобок:

    SELECT item_id FROM items

    WHERE (name MATCHES «теннис») AND price > 20.00

    Проверка на вхождение в множество

    Но самая, пожалуй, мощная логическая операция, которую можно использовать в условии для операторов SELECT, UPDATE и DELETE ­это операция IN. Эта логическая операция возвращает истину, когда значение слева от слова IN входит в множество значений, указанное справа от слова IN. Множество возможный значений может быть указано явно — через запятую в скобках, а может формроваться другим оператором SELECT:

    Например, если мы хотим получить адреса фирм «АО Рога и Копыта» и «ТОО Добро пожаловать», то должны выполнить следующий запрос

    SELECT name, address FROM companies

    WHERE name IN («АО Рога и Копыта», «ТОО Добро пожаловать»)

    Очевидно, последний запрос можно сформулировать и с помощью операций «=» и «OR», но во многих случаях использование операции IN более наглядно и компактно.

    Если же множество значений формируется динамически, в процессе выполнения запроса, то использование операции IN становится единственно возможным способом. Например, нам надо выполнить запрос «найти названия и адреса всех фирм, поставляющих джинсы». Этот запрос выполняется одним SQL-оператором:

    SELECT name, address FROM companies WHERE company_id IN

    (SELECT company FROM items WHERE name = «джинсы»)

    В запросах подобного рода во внутренний оператор SELECT можно добавить слово UNIQUE — это позволит повысить скорость обработки запроса:

    SELECT name, address FROM companies WHERE company_id IN

    (SELECT UNIQUE company FROM items WHERE name = «джинсы»)

    Если же требуется найти все фирмы, которые еще не поставляют нам кеды, то такой запрос получается небольшой модификацией предыдущего — заменой «джинсы» на «кеды» и добавлении операции NOT:

    SELECT name, address FROM companies WHERE company_ )

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

    DELETE FROM companies WHERE company_id NOT IN

    (SELECT company FROM items).

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

    BEGIN WORK
    DELETE FROM companies WHERE company_id NOT IN
    (SELECT company FROM items)
    DELETE FROM persons WHERE company NOT IN (SELECT company_id FROM companies)

    Если известно, что оператор SELECT возвращает одну запись, то можно вместо слова IN использовать проверку на равенство. Например, если мы хотим получить список самых дорогих товаров, а именно их названия и цену (цена будет для всех одна — максимальная), то надо выплнить запрос:

    SELECT name, price FROM items WHERE price = (SELECT MAX(price) FROM items)

    Проверка на существование выборки

    С помощью логической операции EXISTS можно проверить, возвращает ли тот или иной SELECT-оператор какие-то значения. Соответственно, можно в условии определять только те записи, для которых существует (или не существует) какая-то информация. Формат логической операции EXISTS:

    Например, приведенный выше запрос на поиск фирм, который ничего не поставляют, можно сформулировать с помощью операции EXISTS:

    SELECT name, address FROM companies WHERE
    NOT EXISTS (SELECT company FROM items

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

    Сравнивая операцию EXISTS с операцией IN, можно заметить. что одни и те же запросы можно выразить с помощью разных средств (а именно, EXISTS или IN). Какое из средств выбрать, должно определяться логикой задачи и эффективностью исполнения. Последнее, в свою очередь, зависит от реализации и может принципиально различаться для SQL-серверов разных фирм.

    Условия «Существует» и «Для всех»

    Всем, кто когда-то более или менее серьезно, занимался математикой, известны кванторы «Существует» и «Для всех». Аналогичные операции введены и в SQL. Эти операции должны использоваться после операции сравнения (больше, меньше, равно и т.д.), а в качестве правого операнда должен задаваться оператор SELECT:

    Соответсвенно, операция ALL возвращает значение «истина», если операция сравнения истинна для каждого значения, возвращаемого оператором SELECT, а операция ANY — хотя бы для одного. Если оператор SELECT не вернул ни одного знчения, то операция ALL возвращает истину, а ANY — ложь. Вместо ключевого слова ANY можно использовать слоово SOME.

    Например, если мы хотим определить фирму-производителя самых дешевых кед, то запрос с использованием операции ALL будет выглядеть следующим образом:

    SELECT company_id, name FROM companies WHERE
    EXISTS (SELECT company FROM items
    WHERE companies.company_ > AND items.name = «кеды»
    AND items.price AND items.name = «кеды»
    AND items.price =
    (SELECT MIN(price) FROM items WHERE name = «кеды»)

    5.5.5. Слияние двух выборок

    Результаты работы двух или более операторов SELECT могут быть объединены в одну выборку с помощью операторов UNION или UNION ALL. Оператор UNION, помещенный между двумя операторами SELECT делает из двух выборок одну, причем повторяющиеся записи отсутствуют в результирующей выборке. Оператор UNION ALL не убирает повторяющиеся записи. Синтаксис:

    Например, если бы мы имели вместо одной таблицы companies две ­фирмы-поставщики (suppliers) и фирмы-производители (manufacters), и хотели бы получить выборку с названиями всех фирм, с которыми мы работаем, то требуемый запрос формулируется так:

    SELECT name FROM suppliers
    UNION

    SELECT name FROM manufactures

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

    5.5.6. Сортировка выборки

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

    Для упорядочивания выборки в оператор SELECT добавляется раздел ORDER BY. Этот раздел указывается псле условия (раздела WHERE). После слов ORDER BY перечисляются имена полей, по которым надо производить сортировку. Поля, по которым надо сортировать, обязательно должны быть в списке выбираемых полей (присутствовать в разделе SELECT).

    Например, если мы хотим получить в алфавитном порядке список фирм, с которыми мы работаем, то надо выполнить запрос:

    SELECT name FROM companies ORDER BY name

    Для упорядочивания в обратном порядке, например, если мы хотим получить список названий и цен на товары в порядке уменьшения цен, то после имени поля в разделе GROUP BY надо поставить ключевое слово DESC:

    SELECT name, price FROM items ORDER BY price DESC

    Если в разделе ORDER BY указано несколько полей, то вначале производится сортировка по первому полю, затем записи, у которых первые поля совпадают, сортируются по второму полю и т.д.

    Если мы желаем отсортировать по некоторому значению, которое не является полем, а, например, вычисляется по значению поля, то для этого в разделе ORDER BY надо указывать не имена полей и не выражения, а номер выражения (начиная с 1) в разделе SELECT. Например, у нас есть таблица result с результатами некоторого физического эксперимента. Пусть в этой таблице есть поле angle со значением некоторого угла, и height — высота чего-то там для заданного угла. Если мы хотим получить выборку, содержащую значения полей angle, height и sin(angle), при этом отсортировать эту выборку в соответствии с синусом угла, то надо выполнить запрос


    SELECT angle, height, SIN(angle) FROM results ORDER BY 3

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

    5.5.7. Вставка в таблицу нескольких строк одновременно

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

    INSERT INTO (имя поля1, имя поля2, . )

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

    INSERT INTO strange_companies(name, address)
    SELECT name, address FROM companies
    WHERE company_id NOT IN

    (SELECT UNIQUE company FROM items)

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

    Для создания временной таблицы одноврееменно с заполнением ее значениями используется оператор SELECT, в конец которого помещен раздел INTO TEMP с именем временной таблицы. Например, если для хранения информации о фирмах, ничего не поставляющих, мы решили завести не обычную, а временную таблицу strange_companies, то для этого надо выполнить оператор

    SELECT name, address FROM companies
    WHERE company_id NOT IN (SELECT UNIQUE company FROM items)

    INTO TEMP strange_companies

    Оператор SELECT . INTO TEMP автоматически создает таблицу, причем эта таблица имеет столько полей, сколько выражений указано в разделе SELECT. Если в разделе SELECT выражение есть просто значение поля (указно имя поля), то формируемая временная таблица будет иметь поле такого же типа с таким же именем. Если в разделе SELECT стоит составное выражение, то имя поля формируется автоматически по некоторым правилам (здесь они не рассматриваются). В нашем примере, временная таблица strange_companies будет иметь два поля — name и address. Типы этих полей будут идентичны типам полей name и address таблицы companies, то есть CHAR(40) и CHAR(60) соответственно.

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

    Кроме оператора SELECT . INTO TEMP временные таблицы могут быть созданы с помощью оператора создания таблицы — в этом случае он записывается как CREATE TEMP TABLE. Например:

    CREATE TEMP TABLE strange_companies (
    name CHAR(40),
    address CHAR(60)

    5.6. Использование SQL в языках программирования

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

    SQL обычно встраивается в другие языки. Например, существуют продукты Informix ESQL/C, /COBOL, /Ada, /FORTRAN, которые представляют собой препроцессор и библиотеки соответственно для С/С++, COBOL, Ada и FORTRAN. На ESQL/C, например, SQL-оператор, встраиваемый в программу, должен начинаться с символа ‘$’ или со слов ‘exec sql’. Пример фрагмента программы на ESQL/C:

    $database test;
    for (i=0; i++; i CURSOR FOR

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

    SELECT name, address FROM companies ORDER BY name

    <курсор для перебора названий товаров и фирм, их производящих>
    DECLARE dialog_c CURSOR FOR
    SELECT items.name, company.name FROM companies,items

    WHERE companies.company_ > После того, как курсор описан, с ним можно начинать работу. Простейший пример использования курсора был приведен выше ­цикл FOREACH . END FOREACH. Внутри этого цикла производится последовательный перебор записей из выборки.

    Последовательный курсор хорош для случаев, когда не надо организовывать диалог с пользователем — например, для формирования отчетов. Если же на основе курсора планируется организовать диалог с пользователем, то последовательным курсором здесь не обойтись — человеку привычно просматривать содержимое базы данных в любых направлениях (как вперед, так и назад). Для таких задач используется скроллируемый курсор, который описывается так:

    DECLARE SCROLL CURSOR FOR

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

    Если курсор является скроллируемым, то к нему, как и к последовательному, можно применять цикл FOREACH . END FOREACH. Но для того, чтобы организовать произвольный доступ к записям из выборки, связанной со скроллируемым курсором, служат другие операторы.

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

    Для доступа к набору записей, связанных с курсором, служит оператор перемещения по выборке FETCH. В зависимости от того, какую запись Вы хотите выбрать (сделать текущей), можно использовать следующие варианты этого оператора:

    FETCH NEXT
    FETCH PREVIOUS
    FETCH FIRST
    FETCH LAST
    FETCH CURRENT
    FETCH RELATIVE

    Смысл ключевых слов NEXT, PREVIOUS и т.д. и, соответственно, выполняемых ими действий, понятен из их перевода с английского:

    · NEXT — перейти на следующую запись в выборке;
    · PREVIOUS — перейти на предыдущую запись в выборке;
    · FIRST — перейти на первую запись выборки;
    · LAST — перейти на последнюю запись выборки;
    · CURRENT — никуда не перемещаясь, перечитать текущую запись;
    · RELATIVE — сместиться от текущей записи на записей, причем если смещение больше нуля, то сместиться «вниз» — к последней записи, а если меньше — то вверх, к первой записи;
    · ABSOLUTE — перейти на запись, имеющую в данной выборке указанный порядковый номер, причем записи нумеруются с единицы.

    Слово NEXT в операторе FETCH можно опускать — оно принимается по умолчанию. Вместо ключевого слова PREVIOUS можно использовать синоним PRIOR.

    В конкретных языках программирования для задания переменных можно (как и для оператора SELECT) использовать расширение INTO со списком переменных:

    Например, если в программе на INFORMIX-4GL нам надо прочитать очередную запись в переменные, то соотвествующий оператор будет выглядеть так:

    FETCH NEXT my_cursor INTO my_var1, my_var2

    Или для программы на INFORMIX ESQL/C:

    $fetch next $my_cursor into $my_var1, $my_var2

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

    Если же Вы хотите полностью освободить все ресурсы, связанные с объявленным курсором, то для этого используется оператор

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

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

    DECLARE my_scroll_cursor SCROLL CURSOR FOR
    SELECT lname, fname FROM persons ORDER BY lname, fname

    После этих операторов сформирован набор записей в следующем порядке (порядок уже определен разделом ORDER BY оператора SELECT):

    ———T————T———-¬
    ¦ No п/п ¦ lname ¦ fname ¦
    +———+————+———-+
    ¦ 1 ¦ Антонов ¦ Антон ¦
    ¦ 2 ¦ Антонов ¦ Сергей ¦
    ¦ 3 ¦ Бендер ¦ Остап ¦
    ¦ 4 ¦ Шапокляк ¦ Алексей ¦

    Теперь рассмотрим последовательность операторов FETCH и запись, ими выбираемая (порядок выполнения операторов FETCH, очевидно, существенен):

    ———————T————————————¬
    ¦ Оператор FETCH | Выбранная запись No (lname, fname) ¦
    +———————+————————————+
    ¦ FETCH NEXT ¦ 1 (Антонов Антон ) ¦
    ¦ FETCH NEXT ¦ 2 (Антонов Сергей ) ¦
    ¦ FETCH CURRENT ¦ 2 (Антонов Сергей ) ¦
    ¦ FETCH NEXT ¦ 3 (Бендер Остап ) ¦
    ¦ FETCH RELATIVE -2 ¦ 1 (Антонов Антон ) ¦
    ¦ FETCH LAST ¦ 4 (Шапокляк Алексей ) ¦
    ¦ FETCH ABSOLUTE 3 ¦ 3 (Бендер Остап ) ¦
    ¦ FETCH PREVIOUS ¦ 2 (Антонов Сергей ) ¦
    ¦ FETCH FIRST ¦ 1 (Антонов Антон ) ¦
    ¦ FETCH RELATIVE 2 ¦ 3 (Бендер Остап ) ¦

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

    Область взаимодействия SQLCA

    Сервер базы данных для каждого пользователя (точнее, для каждого процесса) заводит так называемую область взаимодействия SQLCA (SQL Communication Area). Эта область фактически является глобальной структурой данных, состощей из нескольких именованных полей (переменных). Каждая переменная этой структуры содержит ту или иную характеристику последнего выполненного оператора или состояние сервера в целом. Поля структуры SQLCA перечислены в таблице:

    ————T————T—————————————-¬
    ¦ название ¦ тип ¦ описание ¦
    +———-+————+—————————————-+
    ¦ SQLCODE ¦ целое ¦ Содержит признак завершения оператора. ¦
    ¦ ¦ ¦ Может принимать следующие значения: ¦
    ¦ ¦ ¦ 0 — признак успешного завершения; ¦
    ¦ ¦ ¦ 100 — признак того, что запрос завер- ¦
    ¦ ¦ ¦ шен нормально, но не было найдено ¦
    ¦ ¦ ¦ ни одной записи; ¦
    ¦ ¦ ¦ отрицательное значение — признак неу- ¦
    ¦ ¦ ¦ дачного завершения; содержит код ¦
    ¦ ¦ ¦ ошибки. ¦
    +———-+————+—————————————-+
    ¦ SQLERRM ¦ строка из ¦ Содержит текстовую строку с описанием ¦
    ¦ ¦ 71 символа ¦ ошибки в случае, если поле SQLCODE ¦
    ¦ ¦ ¦ меньше нуля. ¦
    +———-+————+—————————————-+
    ¦ SQLERRD ¦ массив из ¦ Описывает результат выполнения послед- ¦
    ¦ ¦ 6 целых ¦ него оператора SQL: ¦
    ¦ ¦ ¦ 1-й элемент — внутренняя информация; ¦
    ¦ ¦ ¦ 2-й элемент — содержит сгенерированное ¦
    ¦ ¦ ¦ сервером значение поля типа SERIAL ¦
    ¦ ¦ ¦ для оператора INSERT, либо допол- ¦
    ¦ ¦ ¦ нительный код ошибки; ¦
    ¦ ¦ ¦ 3-й элемент — равен количеству обрабо- ¦
    ¦ ¦ ¦ танных записей; ¦
    ¦ ¦ ¦ 4-й элемент — примерная «стоимость» ¦
    ¦ ¦ ¦ выполнения данного оператора; ¦
    ¦ ¦ ¦ 5-й элемент — смещение ошибки в текс- ¦
    ¦ ¦ ¦ товой записи оператор SQL ¦
    ¦ ¦ ¦ 6-й элемент — внутренняя информация; ¦

    Таблица: Поля области взаимодействия (SQLCA) )

    Предположим, мы хотим снизить на 20% цену на все товары, описанные в таблице items. А заодно, сообщить пользователю, что цены снижены на столько-то товаров. С ипользованием Informix ESQL/C и области взаимодействия SQLCA фрагмент программы будет выглядеть так:

    $update items set price = 0.8*price;

    printf («Цены снижены на %d наименований.\n», sqlca.sqlerrd[2]);

    В данном примере (на ESQL/C) количество обработанных записей содержится в sqlca.sqlerrd[2], так как в языке C элементы массива нумеруются с 0 и 3-й элемент массива sqlca.sqlerrd как раз и будет иметь индекс 2.

    Теперь рассмотрим пример вставки связанных значений в две таблицы. Например, мы подписали договор с новым поставщиком на поставку некоторого товара. Нам надо вставить информацию о поставщике в таблицу companies, а о товаре в таблицу items. Причем, в таблице items есть поле company, которое указывает на запись в таблице companies. Эта запись определяет фирму-поставщика данного товара (см. описание структуры базы данных в предыдущем номере). Можно, конечно, вставить информацию о фирме, по ее имени найти ее ключ, и это значение использовать для ссылки. Но это плохо — выполняется лишняя операция поиска и нигде не сказано, что имя компании уникально. С помощью области взаимодействия SQLCA данная задача решается достаточно просто (пример на Informix 4GL или NewEra):

    DEFINE new_serial INTEGER
    .
    BEGIN WORK
    INSERT INTO companies(name) VALUES («Наш новый партнер»)
    LET new_serial = SQLCA.SQLERRD[2] — ключ (company_id) для новой фирмы
    INSERT INTO items (name, company) VALUES («Новый товар», new_serial)

    В данном примере, кстати, объединять два оператора в одну транзакцию (BEGIN WORK . COMMIT WORK) очень желательно. Если этого не сделать и произойдет какой-то сбой между двумя операторами INSERT, то в базе окажется информация о компании, но не будет информации о товаре. В случае использования транзакции этого можно не опасаться.

    Другое очень важное использование области взаимодействия SQL ­это проверка на то, что в результате оператора выборки (FETCH или SELECT) была найдена хотя бы одна запись. Для этого служит поле SQLCODE области взаимодействия. Данное поле также устанавливается оператором открытия курсора OPEN. Например, если мы хотим после открытия курсора выдать либо первую запись, либо сообщить о том, что записей не найдено, то соответствующий фрагмент программы будет выглядеть примерно так (пример на Informix ESQL/C):

    #define NOTFOUND 100
    .
    $declare x scroll cursor for select name, address from companies; $open x;
    /* теперь проверим на наличие хотя бы одной записи в выборке */
    if (sqlca.sqlerrd == NOTFOUND) <
    /* нет ни одной записи — сообщить об этом */
    printf(«Записей не найдено.\n»);
    >
    else <
    /* показать первую запись и перейти к диалогу */
    $fetch x into $name, $address;
    printf(«Фирма %s расположена по адресу %s.\n», name, address);
    .
    /* диалог с пользователем */
    >

    $close x; /* закрыте курсора по завершению работы */

    Примером проверки ситуации, когда пользователь, выбрав последнюю запись, захотел выбрать еще и следующую (несуществующую), может служить следующий фрагмент программы на языке Informix 4GL:

    И, естественно, область взаимодействия SQL активно используется для контроля за ошибками и состоянием базы данных. Для этого надо проверять сотояние сервера SQL после выполнения какого-либо оператора. Надо отметить, что можно управлять реакцией прикладной программы на возникновение ошибок. Например, программа может автоматически завершаться при возникновении ошибки, а может и продолжать работу, сама обрабатывая ошибочную ситуацию (см. следующий параграф). Предположим, что программа работает в режиме, когда обработкой ошибок занимется сама программа и при возникновении ошибки выполнение программы продолжается. Приведем фрагмент программы, проверяющий наличие базы данных, и, если таковая отсутствует, создающий ее (Informix ESQL/C):

    $database my_base;
    if (sqlca.sqlerrd

    В случае CONTINUE при возникновении ошибки управление будет передано на следующий оператор, а в случае CALL — вначале будет вызвана указанная функция. Отслеживание возникновения ошибки в этом случае производится тестированием поля SQLCODE области взаимодействия SQL.

    С помощью оператора WHENEVER можно отлавливать и обрабатывать не только возникновение ошибок, но и другие события. Например, оператор WHENEVER можно использовать для отслеживания события «записей не найдено» или при выдаче сервером предупреждений. Соответствующие варианты оператора WHENEVER выглядят так:

    WHENEVER NOT FOUND

    Для примера приведем простую программу на Informix-4GL с тремя операторами WHENEVER. Первый оператор задает режим продолжения работы (CONTINUE) при вознкновении ошибок, второй — задает реакцию на ненахождение выборки, а третий — говорит о необходимости завершить программу при возникновении ошибки. Любая ошибка между первым и третьим опреатором WHENEVER будет проигнорирована, а ошибка после третьего оператора WHENEVER приведет к аварийному завершнию программы.

    MAIN
    DEFINE char_num INTEGER
    DATABASE test
    WHENEVER SQLERROR CONTINUE — первый оператор WHENEVER
    DISPLAY «Пытаемся выполнить первый оператор INSERT»
    INSERT INTO test_table(color) VALUES («красный»)
    IF SQLCA.SQLCODE FROM

    Оператор PREPARE «подготавливает» SQL-оператор для исполнения. Сам SQL-оператор указывается либо явно в виде текстовой строки, либо через значение текстовой переменной. Оператор PREPARE «связывает» с SQL-оператором имя. Это имя — самое обычное имя в среде разработки. Примеры (Informix-4GL):

    PREPARE sel1 FROM «select name from items where price ( )

    Если хранимая процедура будет возвращать какие-то значения, то добавляется раздел RETURNING, в котором перечислены типы возвращаемых значений:

    CREATE PROCEDURE ( )
    RETURNING , .

    Хранимая процедура может получать и возвращать произвольное число значений разных типов. Типы входных параметров и выходных значений могут быть любыми, доступными в SQL, за исключением SERIAL, BYTE и TEXT. Вместо типа SERIAL надо указывать тип INTEGER.

    Примеры описаний хранимых процедур:

    CREATE PROCEDURE incr_account (account_no INTEGER,
    quantity MONEY(20,2))
    .

    CREATE PROCEDURE add_new_user (name CHAR(20) default NULL)
    RETURNING INTEGER <серийный номер нового пользователя>
    .

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

    Хранимая процедура, хоторая больше не нужна, может быть удалена. Для этого используется оператор:

    DROP PROCEDURE add_new_user

    Для исполнения хранимой процедуры используется оператор

    EXECUTE PROCEDURE incr_account (213917008, 23000000.00)

    Если хранимая процедура возвращает какие-то значения (то есть в ее описании есть раздел RETURNING), то при исполнении этой процедуры в оператор EXECUTE PROCEDURE надо добавить раздел INTO с перечислением имен переменных (иногда вместо INTO используется слово RETURNING):

    EXECUTE PROCEDURE add_new_user («Прутков») INTO user_id

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

    EXECUTE PROCEDURE incr_account (123456789, 1500000.00)
    EXECUTE PROCEDURE incr_account
    (account_no=123456789, quantity=1500000.00)
    EXECUTE PROCEDURE incr_account

    Если при исполнении хранимой процедуры значения каких-либо фактических параметров не указаны, то используются значения по умолчанию. Значения по умолчанию задаются при описании хранимой процедуры с помощью ключевого слова DEFAULT. Например, в описанной выше процедуре add_new_user для параметра name задано значение по умолчанию — NULL.

    Язык хранимых процедур

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

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

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

    В разделе исполняемых операторов могут использоваться любые операторы из таблицы 1, а также любой из SQL-операторов, за исключеием тех, которые создают, удаляют, открывают или закрывают базу данных. При обращении к хранимой процедуре управление передается первому оператору в ее теле. Исполнение оператора RETURN завершает хранимую процедуру и возвращает исполнение в то место, откуда хранимая процедура была вызвана. Хранимая процедура завершаетя также и при достижении оператора END PROCEDURE. При этом никаких значений в вызывающую программу не возвращается.

    В качестве примера рассмотрим реализацию хранимых процедур, приведенных при рассмотрении оператора CREATE PROCEDURE:

    CREATE PROCEDURE incr_account (account_no INTEGER, quantity MONEY(20,2))
    UPDATE accounts SET value = value + quantity
    WHERE account_ >

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

    CREATE PROCEDURE add_new_user (name CHAR(20) default NULL)
    RETURNING INTEGER <серийный номер нового пользователя>
    DEFINE user_id INTEGER;
    INSERT INTO users(lname) VALUES (name);
    LET user_ > RETURN user_id

    Рассмотрим основные операторы, имеющиеся в языке хранимых процедур Informix SPL (SPL — это аббревиатура отStored Procedure Language):

    CALL — то же самое, что и EXECUTE PROCEDURE.

    CONTINUE — продолжить выполнение цикла. После этого слова надо указать какой именно цикл надо продолжить выполнять -FOR/WHILE/FOREACH. Примеры:

    DEFINE — определить внутреннюю переменную. Для каждой переменной надо задать ее имя и ее тип:

    DEFINE user_id INTEGER

    DEFINE comp_name CHAR(40)

    EXIT — прекратить выполнение цикла. После этого слова надо указать какой именно цикл надо прекратитьвыполнять — FOR/WHILE/FOREACH. Примеры:


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

    Если шаг (STEP) не указан, то он принимается равным 1 если начальное значение меньше конечного, и -1, если нет. Пример:

    IF — условный оператор. Проверяет условие и, если оно верно, выполняет операторы после слова THEN. Если не верно, и присутствует раздел ELSE — то выполняются операторы после слова ELSE:

    IF THEN END IF
    IF
    THEN
    ELSE

    Если после слова ELSE по логике должен следовать другой оператор IF, то такое сочетание ELSE IF . END IF END IF можно записывать более компактно: ELIF . END IF. Пример:

    IF j > 20
    THEN RETURN j
    ELIF j > 10
    THEN RETURN 10
    ELSE RETURN 0

    LET — оператор присваивания. Вычисляет выражение и присваивает его значение указанной переменно:

    Выражение, которое может быть здесь использовано, очень похоже на выражение, которое может использоваться в операторе SELECT. Примеры:

    LET str1 = «абвгдежз»

    RAISE EXCEPTION — возбудить ошибку. Если на данную ошибку не стоит реакция (нет соответствующего оператора ON EXCEPTION), то исполнение хранимой процедуры завершается и в вызвавшую программу «передается» данная ошибка:

    Можно в качестве номера ошибки использовать как предопределенные, стандартные номера, так и свои собственные.

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

    SYSTEM — позволяет выполнить внешнюю команду. Имя этой внешней команды передается операционной системе, то есть это может быть или встроенная команда ОС, или исполнимый файл:

    Например, можно выполнить командный файл my_cmd:

    WHILE — цикл с завершением по условию. Аналогичен циклу WHILE в других языках программирования:

    LET i = 1
    WHILE i = 1
    EXECUTE PROCEDURE my_proc RETURNING I

    BEGIN . END — блок операторов. В блоке операторов можно определять и использовать локальные переменные. Допустимые любые другие операторы SPL. Пример:

    BEGIN
    DEFINE i CHAR(20)
    LET i = «Ну, погоди!»
    IF . THEN
    BEGIN
    DEFINE i INTEGER
    LET i = 0
    .
    END
    END IF

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

    Для подобных задач в SQL введено понятие триггера, Триггер — это механизм, который автоматически выполняет некоторый набор SQL-операторов когда происходит некоторое событие. То есть триггер задается парой «событие-действие». Событиями, на которые можно установить триггер, являются модификации данных, то есть операторы DELETE, INSERT, UPDATE. Причем, триггер связан с конкретной таблицей. То есть событием, вызывающим триггер (триггерным событием) является выполнение операторов удаления, вставки или модификации конкретной таблицы.

    Триггер хранится как объект в базе данных, то есть принадлежит схеме базы данных. )

    Создание и удаление триггера

    Для создания и удаления триггеров, как и для таблиц, и для хранимых процедур, используются операторы CREATE и DROP.

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

    Соответственно, синтаксис на раздел «событие» при создании триггера будет следующим:

    INSERT ON
    DELETE ON
    UPDATE ON

    UPDATE OF , , . ON

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

    INSERT ON documents .

    DELETE ON companies .

    UPDATE OF price ON items .

    UPDATE OF lname, fname ON persons .

    Теперь рассмотрим, как описываются действия триггера. Каждое действие состоит из описания того, что выполняется единожды перед началом исполнения оператора, вызвавшего событие для триггера (раздел BEFORE), для каждого ряда (раздел FOR EACH ROW) и после исполнения оператора (раздел AFTER).

    CREATE TRIGGER . ON .
    BEFORE
    FOR EACH ROW

    Можно использовать произвольное сочетание из разделов BEFORE, FOR EACH ROW и AFTER, главное, что бы был хотя бы один раздел. В качестве операторов для триггеров могут быть использованы всего четыре типа SQL-операторов — оператор вставки (INSERT), удаления (DELETE), обновления (UPDATE) и выполнения хранимой процедуры (EXECUTE PROCEDURE). Очевидно, что наличие в этом списке оператора вызова хранимой процедуры позводяет сделать триггер сколь угодно сложным. Эти SQL-операторы должны разделяться запятой и находиться в круглых скобках.

    CREATE TRIGGER trig1 DELETE ON persons
    AFTER ( UPDATE tab2 SET x=x+1,

    UPDATE tab3 SET y=y-1 )

    CREATE TRIGGER upd_trig UPDATE OF name ON companies
    FOR EACH ROW ( EXECUTE PROCEDURE proc1 )

    AFTER ( EXECUTE PROCEDURE proc2 )

    Рассмотрим поведение последнего триггера (upd_trig) более подробно. Пусть таблица companies имеет следующую структуру и состоит из следующих записей:

    +————+———————-+——————+
    | company_id | name | address |
    +————+———————-+——————+
    | 101 | АО Рога и Копыта | Одесса, п/я 13 |
    | 105 | ТОО Добро пожаловать | Энск, 5-е авеню |
    | 107 | АОЗТ Сделай сам | Городская свалка |

    При такой структуре данной таблицы и при таком ее содержании оператор

    UPDATE companies
    SET name = «ИЧП Мастер Безенчук»

    WHERE company_ > приведет к исполнению триггера upd_trig. Причем, так как модифицироваться будет одна запись, то и процедура proc1, и proc2 будут исполнены по одному разу, причем вначале proc1, а затем proc2. Раздел AFTER и, соответсвенно, процедура proc2, будут исполнены уже после внесения изменений в базу данных. Но если оператор UPDATE модифицирует несколько записей, то процедура proc1 будет исполнена несколько раз (по одному разу для каждой модифицируемой записи), а процедура proc2 — только один раз — после внесения всех изменений. Так, оператор

    UPDATE companies
    SET name = «ТОО Льдинка»

    WHERE company_id > 103

    изменит название у двух компаний, то есть в одном запросе будут модифицированы поля name у двух записей. В результате, процедура proc1 будет исполнена дважды, а затем один раз будет вызвана процедура proc2. Если в операторе UPDATE предполагается изменение поля name, но ни одна запись не была модифицирована (не удовлетворила условию в разделе WHERE, например), то раздел FOR EACH ROW триггера не будет выполнен ни разу, тогда как разделы BEFORE и AFTER все равно сработают. Так, следующий оператор исполнит процедуру proc2 и ни разу не выполнит proc1:

    UPDATE companies
    SET name = NULL

    WHERE company_id Имена для старого и нового значений записи

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

    В механизме триггеров предусмотрена возможность получения значений полей модифицируемой записи. Причем, можно получить значение записи как до модификации (старого значения), так и после (нового значения). Для этого, раздел «действие» в описании триггера надо начать с задания связанных имен записи:

    REFERENCING OLD AS

    REFERENCING NEW AS

    Указывать связанные имена можно в любом порядке. Если какое-то из имен не нужно (например, нам не требуется имя для старого значения), то его можно не указывать. Слово AS можно опускать. )

    Примеры использования связанных имен:

    CREATE TRIGGER del_trig DELETE ON items
    REFERENCING OLD del_rec

    FOR EACH ROW ( UPDATE tab2 SET total=total-del_rec.price )

    CREATE TRIGGER upd_trig UPDATE OF name ON companies
    REFERENCING NEW newval
    REFERENCING OLD oldval
    FOR EACH ROW
    (EXECUTE PROCEDURE proc4(oldval.name, newval.name) )

    AFTER ( EXECUTE PROCEDURE proc2 )

    Итак, все-таки, напишем триггер, который будет срабатывать на удаление информации о фирме (таблица companies) и действие которого будет состоять в удалении из таблицы persons информацию о всех сотрудниках этой фирмы:

    CREATE TABLE companies (
    company_id SERIAL, < первичный ключ фирмы >

    CREATE TRIGGER del_comp DELETE ON companies
    REFERENCING OLD AS rec
    FOR EACH ROW
    (DELETE FROM persons

    WHERE persons.company = rec.company_id )

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

    Тогда, требуемый нам триггер будет выглядеть так:

    CREATE TRIGGER upd_compname UPDATE OF name ON companies
    REFERENNCING NEW AS newcomp
    REFERENNCING OLD AS oldcomp
    FOR EACH ROW
    (INSERT
    INTO protocol (company, login, oldname, newname, when)
    VALUES (oldcomp.company_id, USER, oldcomp.name,

    Условия внутри триггера

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

    Для каждого из разделов BEFORE/FOR EACH ROW/AFTER можно указывать произвольное количество таких пар условие-операторы. Пример триггера с дополнительными условиями:

    CREATE TRIGGER upd_price UPDATE OF price ON items
    REFERENCING OLD AS pre
    REFERENCING NEW AS post
    FOR EACH ROW
    WHEN (post.price pre.price * 2)

    (EXECUTE PROCEDURE proc2(ppost.price, pre.price))

    5.8. Ограничители (задание целостности на уровне схемы)

    Целостность и ограничители

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

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

    Выше мы рассмотрели как с помощью триггера можно поддерживать целостность базы данных, а именно как устранять «висячие» ссылки. Другим примером ограничителя, с которым мы уже знакомы, является описатель NOT NULL (смотри оператор создания таблицы CREATE TABLE), который говорит серверу о том, что данное поле всегда должно иметь определенное значение.

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

    Ограничители, по сути, определяют возможные значения для данного поля данной таблицы. Ограничители являются частью схемы, то есть они проверяются помимо воли программиста или пользователя. Другими словами, пока явно ограничители не будут убраны, состояние базы данных будет удовлетворять заданным ограничениям. В SQL (напомним, что речь идет о варианте SQL фирмы Informix версии 5) присутствуют следующие типы ограничителей — значения по умолчанию, ограничители на значение, ограничители на уникальность и ссылочные ограничители.

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

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

    Значения по умолчанию для полей

    При вставке новых рядов в таблицу часто указываются значения не для всех полей, а только для некоторых. Поля, значения для которых не указаны, приобретают значение NULL (не определено).Но иногда разработчикам хочется, что бы значение по умолчанию было каким-то конкретным. Например, при вставке информации о каком-то товаре поле «количество заказов на этот товар» разумно обнулять.

    Значения по умолчанию для того или иного пля задаются при создании таблицы (оператор CREATE TABLE), либо при модификации структуры таблицы (оператор ALTER TABLE). При описании поля после типа данных надо указать ключевое слово DEFAULT и значение по умолчанию. В качестве значения по умолчанию может стоять константа (в том числе NULL) или функция-псевдополе (CURRENT, TODAY, USER). Пример:

    < таблица с заказами >
    CREATE TABLE orders (
    order_id SERIAL, < уникальный ключ для заказа >
    item INTEGER NOT NULL, < ссылка на товар >
    when DATE DEFAULT TODAY, < дата заказа >
    quantity INTEGER DEFAULT 0, < количество >
    tot_price MONEY(20,2) DEFAULT «0.0» < общая стоимость >

    Проверка на допустимость значения поля

    Когда создается таблица, то для каждого поля задается тип его значения. Это может быть INTEGER, CHAR и т.д. Тип определяет допустимое множество значений для данного поля. Но в некоторых случаях это множество значений много шире реально используемого множества. Например, для обозначения количества единиц товара в заказе (см. структуру таблицы orders в предыдущем пункте) используется тип INTEGER. Но это количество не может быть отрицательным — это диктуется логикой задачи, а тип INTEGER допускает отрицательные значения.

    В SQL есть средства более тонкого описания множества допустимых значений поля (кстати, это множество в теории называют доменом). Более точно задать домен для того или иного поля можно с помощью ограничителя на значение (check constraint). Этот ограничитель указывается при создании таблицы (оператор CREATE TABLE). После типа поля или значения по умолчанию надо указать ключевое слово CHECK и логическое выражение в скобках:

    Это логическое выражение и будет опредеять допустимость значения. Условие проверяется перед изменением данных в поле операторами UPDATE или INSERT. Если значение логического выражение ложь или NULL, то сервер базы данных возвращает ошибку. Если вычисленное логическое выражение имеет значение истина, то новое значение считается допустимым и операция модификации завершается успешно. Например, можно описание таблицы orders расширить следующим образом:

    CREATE TABLE orders (
    .
    item INTEGER NOT NULL CHECK
    (EXISTS (SELECT items.item_id
    FROM items
    WHERE items.item_ > .
    quantity INTEGER DEFAULT 0 CHECK (quantity > 0),
    tot_price MONEY(20,2) DEFAULT «0.0» CHECK (quantity > 0)

    В ограничителе на значение поля можно использовать сколь угодно сложное логическое выражение. Требования к этому выражению такие же, как и к условию в разделе WHERE оператора SELECT. То есть можно использовать логические операции IN, MATCHES, NOT, OR, AND, EXISTS и т.д. Не допускается, однако, использование вложенных подзапросов, агрегатных функций, псевдофункций-полей (CURRENT, TODAY, USER) и вызовов хранимых процедур. Нельзя, также, использовать логическое выражение, которое зависит от других полей в этой таблице. Например, если мы напишем

    CREATE TABLE .
    price MONEY,
    quantity INTEGER,
    tot_price MONEY CHECK (tot_price = price*quantity)

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

    Для задания уникального ключа в таблице обычно используется тип SERIAL. Этот тип по множеству значений и способу внутреннего представления идентичен типу INTEGER. Но гарантировать уникальность значений поля типа SERIAL можно только в том случае, когда сервер сам генерирует новое значение для типа SERIAL, то есть оператор INSERT с явным указанием нового значения SERIAL не выполняется. Но иногда задача требует, что бы гарантированно поля любого типа, в том числе и SERIAL, использовались в качестве первичного ключа. Или, что тоже часто встречается, предметная область накладывает требование, что бы набор некоторых полей был уникальным, то есть был бы тоже первичным ключом (ключ, состоящий из нескольких полей, называется составным). Для проверки поля или группы полей на то, что они являются первичным ключом используется ограничитель на уникальность. Ограничитель на уникальность группы полей будет рассмотрен в параграфе 4.5. Здесь мы рассмотрим ограничитель на уникальность для отдельного поля.

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

    CREATE TABLE (
    .
    [NOT NULL]
    [ ] UNIQUE

    Предположим, мы хотим создать таблицу для хранения зарегестрированных торговых марок. Естественно предположить, что торговая марка должна быть уникальной. Торговая марка — это символьная строка. Пусть для ее хранения достаточно 64 символов. Тогда требуемая таблица будет выглядеть так:

    Отличие типа SERIAL от поля, объявленного уникальным, заключается в том, что сервер при вставке новой записи с полем типа SERIAL сам генерирует новое уникальное значение для типа SERIAL и не делает этого для поля с ограничителем на уникальность. Приложение должно само заботиться о присвоении полям с ограничителем на уникальность новых значений. Иначе, при вставке нового ряда при неуказанном значении уникального поля ему будет присвоено или значение NULL, или значение по умолчанию. Ни то, ни другое значение, скорее всего, не будет уникальным.

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

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

    CREATE TABLE trademarks (
    trademark_id SERIAL UNIQUE, < первичный ключ >
    name CHAR(64) UNIQUE, < название торговой марки >
    company INTEGER, < какой компании принадлежит >

    Ограничитель на уникальность не может использоваться совместо с ограничителем на значение.

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

    Для реализации таких отношений между таблицами используются ссылки. То есть в одной из двух связанных таблиц заводится поле (или поля), которое представляет собой значение первичного ключа в другой таблице. Именно таким образом связаны таблицы companies, persons, items, и orders. Здесь главной таблицей является таблица с информацией о фирмах (companies). Все остальные таблицы — люди (persons) и товары (items) в некотором смысле являются подчиненными таблице companies. То есть ни человек, ни товар не могут быть определены, если нет информации о соответсвующей фирме. Таблица orders, хотя и связана с таблицей items соотношением один-к-одному, тем не менее должна рассматриваться как пдчиненная последней, так как поставляемые товары могут существовать без конкретных заказов (мы этот товар еще ни разу не покупали), а наоборот — нет. В соответствии с этим, в подчиненной таблице хранится ссылка на главную, а не наоборот.

    Итак, в главной таблице есть некоторый первичный ключ, а в подчиненной таблице есть ссылка, представляющая собой поле (или поля), хранящие значение первичного ключа главной таблицы:

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

    CREATE TABLE companies (
    company_id SERIAL PRIMARY KEY,

    CREATE TABLE items (
    item_id SERIAL PRIMARY KEY,
    company INTEGER REFERENCES companies(company_id),

    CREATE TABLE persons (
    person_id SERIAL PRIMARY KEY,
    company INTEGER REFERENCES companies(company_id),

    CREATE TABLE orders (
    .
    item INTEGER REFERENCES items(item_id),

    Ссылочный ограничитель не может использоваться совместо с ограничителем на значение или с ограничителем на уникальность.

    Ограничители на уровне таблицы


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

    Ограничитель на значения (в нем можно использовать проверку нескольких полей одновременно) записывается точно так же, как и для одного поля — ключевое слово CHECK, после которого в скобках задается условие. Например:

    CREATE TABLE .
    price MONEY,
    quantity INTEGER,
    tot_price MONEY,
    .

    CHECK (tot_price = price*quantity) )

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

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

    . PRIMARY KEY ( , , . )
    . FOREIGN KEY ( ,

    Очевидно, должно быть соответствие между порядком и количеством полей после слов FOREIGN KEY и слова REFERENCES. Пример:

    CREATE TABLE accounts (
    acc_num INTEGER,
    acc_type INTEGER,
    .

    PRIMARY KEY (acc_num, acc_type))

    CREATE TABLE sub_accounts (
    .
    ref_num INTEGER NOT NULL,
    ref_type INTEGER NOT NULL,
    .
    FOREIGN KEY (ref_num, ref_type)

    REFERENCES accounts (acc_num, acc_type) )

    5.9. Разграничение в SQL прав пользователей

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

    5.9.1. Права доступа

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

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

    INSERT INTO protocol(user_id, action, date)

    VALUES (USER, «удалил запись», CURRENT YEAR TO SECOND)

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

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

    5.9.2. Права на уровне базы данных

    Имеется три категории прав на уровне базы данных. Это право на адимнистрирование (DBA), право на управление ресурсами (RESOURCE), право на доступ (CONNECT). Некоторые пользователи могут вообще не иметь каких-либо прав, связанных с конкретной базой данных.

    Пользователь, имеющий права на доступ (CONNECT) имеет возможность получать и модифицировать данные в базе. Он может модифицировать те объекты, которыми владеет. Любой пользователь, имеющий право доступа может делать следующее:

    · выполнять операторы SELECT, INSERT, DELETE, UPDATE, если это ему позволено на уровне объекта (таблицы);
    · создавать новую псевдотаблицу (VIEW) по таблицам (см. ниже), если он имеет права на выборку по требуемым таблицам;
    · создавать синонимы (см. ниже);
    · создавать временные таблицы и индексы по временным таблицам;
    · изменять или удалять те объекты, которыми владеет;
    · управлять правами других пользователей на объекты, которыми владеет.

    Право на администрирование базы данных (DBA) подразумевает следующие возможности:

    · удалить базу данных (выполнить оператор DROP DATABASE);
    · удалять любые объекты вне зависимости от того, кто ими владеет;
    · раздавать и менять права доступа других пользователей к базе данных в целом и к отдельным объектам.

    При управлении правами на уровне базы данных имя базы не указывается. Подразумевается текущая база данных. Примеры оператора GRANT:

    GRANT DBA TO andy

    GRANT RESOURCE TO micky

    В качестве имени пользователя можно использовать слово PUBLIC ­оно означает всех возможных пользователей. Например, если мы хотим передать право на доступ всем пользователям, то надо выполнить оператор

    GRANT CONNECT TO PUBLIC

    Права могут не только передаваться, но и отбираться. Естественно, для этого надо иметь право на администрирование базы данных. Отбор права выполняется оператором

    REVOKE CONNECT FROM roma

    Поскольку права на базу данных имеют иерархию (право на администрирование включает в себя право на управление ресурсами, а право на управление ресурсами включает в себя право на доступ), то попытка лишить права на доступ пользователя, имеющего право на администрирование ни к чему не приведет. Пользователя надо явно лишить права на администрирование, при этом ему останется право на доступ. Предположим, мы хотим лишить пользователя «andy» каких-либо прав на базу данных. Если он имеет право на администрирование, то надо выполнить следующие два оператора:

    REVOKE DBA FROM andy

    REVOKE CONNECT FROM andy

    Аналогично и с правом на управление ресурсами — выполнение оператора

    REVOKE CONNECT FROM micky

    ни к чему не приведет, так как пользователь «micky» имеет права RESOURCE, а выполнение оператора

    REVOKE RESOURCE FROM micky

    оставит пользователю «micky» право на доступ.

    Администратор базы данных не может лишить права на администрирование самого себя. Но он может лишиться этого права, если это выполнит другой пользователь, имеющий право на администрирование.

    5.9.3. Права на таблицы

    Если пользователь имеет права на базу данных, это не означает автоматически, что он имеет возможность получать любую информацию из любой таблицы. Наличие права на уровне базы данных означает возможность подключиться к базе данных, то есть выполнить оператор DATABASE. Все дальнейшие действия с содержимым базы данных производятся в соответствии с правами на уровне объектов (таблиц и процедур).

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

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

    · SELECT — право на получение информации из таблицы. При необходимости можно дополнительно указать те поля, значения которых доступны. То есть можно указать, что
    · DELETE — право на удаление записей из таблицы.
    · INDEX — право на создание индексов для данной таблицы (индексы будут рассмотрены позднее). Для использования этого права надо иметь право на управление ресурсами на уровне всей базы.
    · ALTER — право на изменение структуры таблицы, то есть право добавлять или удалять колонки, изменять их тип, создавать или удалять ограничители.
    · REFERENCES — право на задание ссылочных ограничетелей для данной таблицы. Для данного права можно специфицировать поля, которые можно использовать в ограничителях.
    · ALL — все перечисленные выше права на таблицу.

    Вместо имени пользователя можно использовать слово PUBLIC, означающее всех пользоователей. Например:

    GRANT ALL ON persons TO micky
    GRANT SELECT ON persons TO roma

    GRANT INSERT ON items TO PUBLIC

    В тех случаях, когда при передаче права на таблицу можно специфицировать конкретные поля (права UPDATE, SELECT и REFERENCES), то имена полей надо указать в скобках после названия права:

    GRANT SELECT(person_id, lname, fname)

    ON persons TO PUBLIC

    Если какой-либо пользователь не имеет права на выборку того или иного поля, то он не может его использовать не только в разделе SELECT, но и в разделе WHERE. Например, если пользователь nick не имеет права на выборку поля address из таблицы persons, то он не имеет права выполнить следующие два запроса:

    SELECT lname, fname, address FROM persons WHERE person_ >

    SELECT lname, fname FROM persons WHERE address MATCHES «*Одесса*»

    Пользователь, получивший то или иное право с помощью оператора GRANT . ON . TO . не может передать это право другому пользователю. Для возможности передачи права другим, пользователь должен получить право на его передачу. Это право указывается в операторе GRANT с помощью ключевых слов WITH GRANT OPTION:

    TO WITH GRANT OPTION

    Например, владелец таблицы tabl может передать право на выборку для пользователей «sales» и «tech», но пользователю «tech» это право передается с правом передачи:

    GRANT SELECT ON tabl TO sales

    GRANT SELECT ON tabl TO tech WITH GRANT OPTION

    После этого пользователь «tech» может выполнить следующие два оператора, а пользователь «sales» не может:

    GRANT SELECT ON tabl TO jason WITH GRANT OPTION

    GRANT SELECT ON tabl TO micky

    Права на таблицу могут как передаваться, так и отбираться. Отобрать право у пользователя можно только в том случае, если Вы передали ему это право, либо Вы являетесь владельцем таблицы.

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

    REVOKE UPDATE ON persons FROM george

    REVOKE ALL ON passwords FROM PUBLIC

    Если какое-либо право отбирается у пользователя, которому оно было дано с правом на передачу (WITH GRANT OPTION), то это право теряют и те пользователи, кому этот пользователь его передал. Предположим, Вы владелец таблицы tab1. Вы передаете право на вставку пользователям user1, user2:

    GRANT INSERT ON tab1 TO user1 WITH GRANT OPTION

    GRANT INSERT ON tab1 TO user2 WITH GRANT OPTION

    Затем, пользователь «user1» передал это право пользователю «user3»:

    GRANT INSERT ON tab1 TO user3

    Теперь, если Вы отберете право на выборку у пользователя «user1», то его автоматически потеряет и пользователь «user3».

    В операторах GRANT и REVOKE для таблиц и процедур можно использовать несколько прав одновременно и указывать несколько пользователей одновременно. Название прав и имена пользователей в этом случае надо указывать через запятую. Например оператор

    GRANT SELECT, UPDATE(fname, lname, address) ON persons TO micky, roma

    Эквивалентен следующим четырем операторам:

    GRANT UPDATE(fname, lname, address) ON persons TO micky
    GRANT SELECT ON persons TO micky
    GRANT UPDATE(fname, lname, address) ON persons TO roma

    GRANT SELECT ON persons TO roma

    5.9.4. Права на хранимые процедуры

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

    GRANT EXECUTE ON TO

    GRANT EXECUTE ON TO WITH GRANT OPTION REVOKE EXECUTE ON FROM

    5.9.5. Кто и как следит за соблюдением прав

    В заключении разговора о правах стоит отметить, что стоит за словами «имеет право» и «не имеет право». Контроль за доступом к информации осуществляет сервер базы данных. Пользователь не имеет доступа непосредственно к файлам с базой данных. Он даже не знает, как и где хранятся эти данные. При выполнении запроса пользователя, SQL-сервер получает его, определяет имя пользователя и по внутренней информации определяет может ли этот пользователь выполнить этот запрос. Если имеет право, то сервер производит обработку запроса, если нет — пользователю посылается сообщение об ошибке.

    Как хранится информация о привилегиях — это внутреннее дело SQL-сервера. Например, Informix DS использует для этого системные таблицы (каталоги), где и зафиксирована кто и что может делать с данными.

    5.9.6. Механизм ролей

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

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

    Например, если мы хотим создать три группы — admin (администратор), sales (продавцы) и tech (технический персонал), то надо выполнить следующие операторы:

    CREATE ROLE admin
    CREATE ROLE sales

    CREATE ROLE tech

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

    Для указания пользователей, входящих в ту или иную роль, используется оператор

    Один пользователь может принадлежать нескольким ролям, также как и не принадлежать ни одной роли (или, что тоже самое, принадлежать только роли по имени NULL). Например:

    GRANT admin TO andy
    GRANT tech TO andy, micky

    GRANT sales TO nick, kate


    В этом примере пользователь andy входит сразу в две роли — admin и tech, пользователи micky, nick, kate — входят только в одну роль.

    Исключение пользователя из роли производится оператором

    REVOKE sales FROM nick

    Для удаления роли используется оператор

    Для приписывания роли или отбирания у роли тех или иных прав и привилегий используются варианты операторов GRANT и REVOKE, рассмотренные в пунктах 5.9.2 и 5.9.3, только вместо имени пользователя необходимо указывать имя роли:

    GRANT UPDATE(fname, lname, address) ON persons TO admin
    GRANT SELECT ON persons TO tech

    REVOKE UPDATE(fname, lname, address) ON persons FROM sales

    Определять новые роли может только пользователь, являющийся администратором данной базы данных, то есть имеющий привилегию DBA на текущую базу данных. Удалить роль, модифицировать список пользователей, входящих в ту или иную роль, а также приписать или отобрать у роли некоторые привилегии, может только адинистратор базы данных, или пользователь, которому данная роль была передана с опцией WITH GRANT OPTION.

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

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

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

    5.9.7. Псевдотаблицы (VIEW)

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

    В SQL данная проблема может быть решена с помощью псевдотаблиц (VIEW). Псевдотаблица строится на основе уже существующих таблиц или пседотаблиц. В основе псевдотаблицы лежит оператор выборки. Собственно говоря, псевдотаблица и задается оператором выборки:

    CREATE VIEW cheap_items AS
    SELECT item_id, company, name, price FROM items

    WITH CHECK OPTION

    Например, в определенную выше псевдотаблицу cheap_items можно вставить запись с ценой, равной 500 единиц:

    INSERT INTO cheap_items (company, name, price)

    VALUES (101, «смокинг», 500)

    так как эта псевдотаблица создана без проверки. Правда, сразу после этого оператора вставки в данной псевдотаблице запись о смокинге ценой в 500 единиц будет все равно отсутствовать (при выборке эта запись будет отсечена по условию price

    DROP VIEW cheap_items

    DROP VIEW employers

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

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

    SQL позволяет создавать синонимы для таблиц. Синонимы бывают публичными или личными. Публичный синоним доступен после создания всем пользователям. Личный синоним — тольо владельцу, то есть пльзователю, который его создал. Нельзя создать синоним на синоним. Публичный синоним создается оператором

    CREATE PUBLIC SYNONYM FOR

    Личный синоним создается аналогично, только вместо ключевого слова PUBLIC используется слово PRIVATE:

    CREATE PRIVATE SYNONYM FOR

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

    Примеры создания синонимов:

    CREATE PUBLIC SYNONYM goods FOR items

    CREATE PRIVATE SYNONYM tovary FOR items

    Для баз данных в режиме ANSI синонимы бывают только личными и при создании синонима слово PRIVATE указывать не надо. Имена синонимов могут использоваться в операторах GRANT и REVOKE при передаче или лишения права на доступ к таблице..

    Удаляется синоним оператором

    5.10. Управление одновременным доступом к данным

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

    5.10.1. Что бывает, когда несколько человек одновременно пытаются обновить одни и теже данные

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

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

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

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

    5.10.2. Открытие базы данных только для себя

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

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

    Точно так же, если указанная база данных уже открыта другим пользователем (как с указанием EXCLUSIVE, так и в обычном режиме), то Вы получите сообщение об ошибке.

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

    Оператор DATABASE EXCLUSIVE используется достаточно редко. Он обычно применяется при каком-то существенном перестроении схемы базы данных. Для реализации многопользовательского доступа к данным существуют более мягкие и изящные способы — блокирование таблицы и уровни изоляции.

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

    5.10.3. Блокирование таблицы

    Выше мы расмотрели способы ограничения доступа ко всей базе данных. При необходимости ограничить доступ других пользователей к некоторой таблице можно заблокировать только эту таблицу. Заблокировать таблицу можно двумя способами. Первый способ разрешает доступ других пользователей к заблокированной таблице на чтение данных. Такая блокировка называется «разделяемой». При втором способе любой доступ к таблице для других пользователей запрещен. Такая блокировка называется эксклюзивной (эксклюзивная блокировка аналогична открытию базы данных в исключительном режиме).

    Разделяемая блокировка на таблицу ставится оператором

    LOCK TABLE IN SHARE MODE

    Эксклюзивная блокировка на таблицу ставится оператором

    LOCK TABLE IN EXCLUSIVE MODE

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

    Снятие блокировки производится оператором

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

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

    5.10.4. Механизм блокирования записей и уровни изоляции

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

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

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

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

    Существуют следующие основные уровни изоляции: грязное чтение (DIRTY READ), достоверное чтение (COMMITTED READ), стабильный курсор (CURSOR STABILITY), многократное чтение (REPEATABLE READ).

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

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

    Уровень изоляции DIRTY READ является единственно допустимым для баз данных без транзакций, но может применяться и для баз данных с транзакциями. Уровень изоляции «грязное чтение» обеспечивает самую высокую производительность. Его обычно используют для доступа к данным, которые хранятся в малоизменяемых таблицах, например для выборки из таблиц с описанием классификаторов или для чтения таблиц с результатами проведенных экспериментов.

    Уровень изоляции COMMITTED READ (достоверное чтение) обеспечивает чтение данных, когда они не модифицируются другим процессом. Использование этого уровня изоляции обеспечивает Вам невозможность прочесть так называемые «фантомные» записи, которые появляется в процессе выполнения транзакции другим процессом. То есть если Ваша программа прочитала запись, то эта запись гарантирована существует. Однако, никаких блокировок на прочитанные записи при этом не делается, то есть другой процесс может прочитать эту же запись и удалить или изменить ее. Данный уровень изоляции является уровнем, принимаемым по умолчанию для баз данных с транзакциями.

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

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

    Уровень изоляции CURSOR STABILITY (стабильный курсор) обеспечивает невозможность другим пользователям изменить прочитанную Вами запись до тех пор, пока Вы не прочтете другую запись. То есть, прочитанный ряд блокируется. Обычно последовательный просмотр записей реализуется с помощью механизма, называющегося «курсор», отсюда и название данного уровня изоляции.

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

    Самый высший уровень изоляции — это REPETABLE READ (повторяемое чтение). Если программа использует данный уровень изоляции, то SQL-сервер заблокирует все записи, прочитанные данной программой в пределах транзакции. То есть уровень изоляции REPEATABLE READ означает запрещение изменения другими процессами всех записей, задействованных Вами в течении транзакции. Это означает, что Вы можете в течение одной транзакции многократно читать одни и те же записи и они гарантированно не могут быть изменены другими пользователями. Этот уровень изоляции является самым сильным и принимается по умолчанию для баз данных с транзакциями в режиме ANSI.

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

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

    SET ISOLATION TO

    SET ISOLATION TO REPEATABLE READ

    SET ISOLATION TO DIRTY READ

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

    SET ISOLATION TO DIRTY READ
    SELECT . INTO .
    .
    SET ISOLATION TO CURSOR STABILITY
    FOREACH my_cursor INTO var, var
    .

    5.10.5. Управление ожиданием снятия блокировок

    В реальных программах надо как-то уметь обрабатывать ситуации, когда пользовательская программа пытается обратитьться к заблокированным ресурсам (таблице, записи). Помимо стандартного управления ошибками (оператор WHENEVER, мы его рассмотрели в одной из предыдущих статей), существует специальный оператор установки режима ожидания SET LOCK MODE. Его синтакис:

    SET LOCK MODE TO WAIT

    SET LOCK MODE TO NOT WAIT

    Если Вы установили режим ожидание в «NOT WAIT» («не ждать»), то если Ваша программа пытается обратиться к заблокированным ресурсам, то тут же получает сообщение об ошибке. Этот режим устанавливается по умолчанию.

    Если Вы установили режим ожидания в «WAIT» («ждать») и не указали число секунд, то Ваша прогрмма будет ожидать разблокирования ресурсов до бесконечности. Если же при этом указать и число секунд, то Ваша программа будет ожидать разблокирования ресурсов указанное число секунд, и, если за указанное время этого не произошло, то она получает сообщение об ошибке, Например, если Вы хотите, что бы пользователь ждал не более 5 секунд, а затем получал сообщение о недоступности ресурсов, то надо выполнить оператор

    SET LOCK MODE TO WAIT 5

    5.10.6. Тупиковые ситуации

    Теоретически, особенно при использовании режима ожидания до бесконечности, возможно возникновение тупиковой ситуации, клинча (английский термин deadlock — смертельные объятия). Это означает, что две или более программ заблокировали некоторые данные, но для продолжения работы им нужны данные, которые заблокированы другими программами. То есть программы ждут друг друга. Рассмотрим пример: пользователи А и В исполняют некоторые программы (соответсвенно программы А и В), работающие с одной и той же базой данных. Обе программы находятся в режиме бесконечного ожидания снятия блокировок (был выполнен оператор SET LOCK MODE TO WAIT). Программа А выполнила оператор

    LOCK TABLE table_a IN EXCLUSIVE MODE

    заблокировав, таким образом, таблицу table_a. Затем программа В выполняет оператор

    LOCK TABLE table_b IN EXCLUSIVE MODE

    и блокирует таблицу. Для дальнейшей работы программе А надо заблокировать таблицу table_b и она выполняет оператор

    LOCK TABLE table_b IN EXCLUSIVE MODE

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

    LOCK TABLE table_a IN EXCLUSIVE MODE

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

    5.11. Повышение скорости обработки запросов.

    Язык SQL — не процедурный язык. Когда мы обсуждали, ЧТО делает тот или иной оператор, то никогда не обсуждалось, КАК это выполняется. Но SQL используется для написания реальных программ, с которыми работают реальные люди. Поэтому во многих случаях требуется обеспечить быстрое исполнение SQL-операторов. В данном параграфе будут рассмотрены способы управления скоростью исполнения тех или иных SQL-запросов.

    Предположим, в нашей базе данных есть таблица persons, содержащая сведения о людях (ФИО и где работают:)

    CREATE TABLE persons (
    person_id SERIAL UNIQUE,
    company INTEGER,
    lname CHAR(40),
    fname CHAR(30),
    sname CHAR(30),

    Если мы попытаемся определить, какие люди работают в фирме с уникальным номером 105 (то есть у какх записей в таблице persons поле company равно 105), нам надо будет выполнить запрос

    SELECT lname, fname, sname FROM persons

    WHERE company = 105

    При исполнении этого запроса сервер базы данных должен будет просмотрть всю таблицу persons и для каждой записи из этой таблицы проверить условие «company = 105». Если в таблице persons несколько миллионов записей, то такой запрос может потребовать для своей обработки длительное время. Совсем не обязательно иметь таблицу в несколько миллионов записей, чтобы нагрузить сервер работой на несколько минут. Предположим, в таблице companies одна тысяча записей, а в таблице persons ­всего десять тысяч записей (в каждой фирме, в среднем, работает десять человек). Таблица compenies имеет следующую структуру:

    CREATE TABLE companies (
    company_id SERIAL UNIQUE,
    name CHAR(40),
    address CHAR(40)

    Если мы будем искать всех людей и их рабочие адреса для фирм под названием «АО Рога и Копыта» и «АОЗТ Сделай Сам», то при исполнении запроса

    SELECT persons.lname, persons.fname, persons.sname, companies.address
    FROM persons, companies
    WHERE company.name IN («АО Рога и Копыта», «АОЗТ Сделай Сам»)

    AND persons.company = company.company_id

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

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


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

    CREATE INDEX pers_comp_index ON persons(company)

    В общем случае, оператор создания индекса выглядит так:

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

    CREATE index1 ON persons(lname)

    CREATE comp_indx ON items(company, name)

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

    Пусть у нас есть таблица persons с полями:

    ————T———T———-T———¬
    ¦ person_id ¦ company ¦ lname ¦ fname ¦
    +————+———+———-+———+
    ¦ 1 ¦ 101 ¦ Антонов ¦ Сергей ¦
    ¦ 2 ¦ 105 ¦ Шапокляк ¦ Алексей ¦
    ¦ 3 ¦ 102 ¦ Антонов ¦ Антон ¦
    ¦ 4 ¦ 101 ¦ Бендер ¦ Остап ¦

    Каждая запись в этой таблице реально где-то размещена. С местом ее размещения может быть однозначно связано какое-то значение. Если для хранения используется файл, как в сервере Informix-SE, то в качестве этого уникального значения может выступать смещение записи от начала файла. Если для хранения используется своя собственная файловая ситема, как, например, в Informix DS, то в качестве внутреннего идентификатора записи может выступать составной ключ (номер диска, номер сектора, смещение от начала сектора). Как выглядит это уникальное для каждой записи значение — абсолютно не важно, это внутреннее дело сервера. Очевидно, что такое значение всегда можно построить. Предположим, что это значение всегда присутствует для любой таблицы и выглядит как псевдополе с именем «rowid» (идентификатор записи). С учетом этого поля записи в данной таблице выглядят следующим образом:

    — — — — T————T———T———-T———¬
    ¦ rowid ¦ person_id ¦ company ¦ lname ¦ fname ¦
    + — — — +————+———+———-+———+
    ¦ 1003 ¦ 1 ¦ 101 ¦ Антонов ¦ Сергей ¦
    ¦ 1023 ¦ 2 ¦ 105 ¦ Шапокляк ¦ Алексей ¦
    ¦ 1063 ¦ 3 ¦ 102 ¦ Антонов ¦ Антон ¦
    ¦ 1053 ¦ 4 ¦ 101 ¦ Бендер ¦ Остап ¦

    При исполнении оператора создания индекса сервер создает набор записей, собственно и составляющих индекс. Каждая запись из индекса состоит из значений той таблицы, по которой построен этот индекс (естественно, хранятся только те поля, которые указаны в данном индексе — эти поля называют проиндексированными) и ссылки на физическое расположение записи в таблице (rowid этой записи). Записи из индекса упорядочены по индексируемым значениям. Например, для индекса pers_comp_indx, построенного по полю company таблицы persons набор составляющих его записей будет выглядеть следующим образом:

    Тогда при исполнении запроса

    SELECT . FROM persons WHERE company = 105

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

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

    SELECT company FROM persons WHERE lname = «Бендер»

    Если подобные запросы возникают часто, то для того, чтобы убыстрить их обработку, надо создать индекс и по полю lname:

    CRATE INDEX second_index ON persons(lname)

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

    CREATE INDEX pers_names ON persons(lname, fname)

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

    CREATE INDEX pers_names2 ON persons(fname, sname)

    Кстати, если создан составной индекс по полям (lname, sname), то отпадает необходимость в индексе по полю lname (но не в индексе по полю sname). То есть, если создан индекс по некоторой последовательности полей (a1, a2, . aN), то он функционально покрывает индекс по последовательности полей (a1, a2, . aK), если K

    Ключевое слово UNIQUE (вместо него можно использовать слово DISTINCT) означает, что в таблице не допускаются одинаковые наборы значений по совокупности полей, указанным в индексе. Другими словами, это способ следить за уникальностью указанного набора полей. Поэтому такой индекс называется уникальным. Использование уникального индекса функционально идентично заданию уникального ключа в таблице за тем исключением, что для добавления уникального индекса надо иметь привилегии RESOURCE, а для указания уникального ключа в структуре таблицы — быть владельцем таблицы, иметь права администратора или иметь привилегию на модификацию таблицы. Пример:

    CREATE UNIQUE INDEX my_index ON persons (person_id, company)

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

    CREATE CLUSTER INDEX my_2nd_index ON persons (lname, fname)

    Использование индекса значительно повышает скорость поиска записей при выполнения операторов с логическим условием (то есть для операторов SELECT, DELETE или UPDATE с условием WHERE). При этом, естественно, нужно, чтобы сервер базы данных мог использовать существующие индексы для поиска записей, удовлетворяющих условию в разделе WHERE. С другой стороны, индексы, за исключением кластерных, требуют дополнительных затрат памяти. Кроме того, при выполнении операций, изменяющих содержимое базы данных (INSERT, UPDATE, DELETE), если изменения затрагивают проиндексированные поля, требуется дополнительное время на перестройку индекса. Но так как во многих задачах бОльшая часть обращений к базе данных связана с поиском информации, индексы позволяют значительно увеличить общую производительность информационной системы.

    Использование индексов в SQL принципиально отличается от того, как они применяются в индексно-последовательных СУБД типа dBase, Clipper или FoxPro. В случае SQL-сервера, поддержание целостности индекса и его использование — это внутренняя задача самого сервера. У пользователя или программиста, работающего с SQL-сервером, есть только возможность создать (оператор CREATE INDEX) и удалить (оператор DROP INDEX) индекс, а также изменить его структуру (с помощью оператора ALTER INDEX, который здесь не рассматривается). Оператор удаления индекса имеет следующий синтаксис:

    Естественно, для удаления индекса надо быть или его владельцем, или иметь права администратора.

    5.11.2. Буферизация журнала транзакций

    Обычно, при завершении транзакции (оператор COMMIT WORK) происходит принудительный сброс обновленной базы данных на внешний носитель (жесткий диск). Причем информация о проведенной транзакции дополнительно записывается в специальный файл — журнал транзакций. Если этот журнал и обновленное содержимое базы данных не сбрасывать на диск по окончании транзакции, а буферизировать в памяти, то в случае падения напряжения питания или сбоя аппаратуры информация о нескольких последних завершенных транзакциях может быть потеряна. Целостность базы данных при этом гарантированно сохранится, однако ее содержимое будет соответсвовать или последней завершенной транзакции, или предпоследней, или пред…предпоследней. Для банковских задач буферизация журнала транзакций, наверное, неприемлима, но для целого ряда других задакч, например регистрации каких-либо параметров, подлежащих статистической обработке. — вполне допустима.

    Операции обмена с жестким диском — одно из самых узких мест в современных вычислительных системах (с точки зрения производительности). Поэтому буферизация журнала транзакций может заметно повысить общую производительность системы. Естественно, повышать производительность в ущерб надежности мы не рекомендуем, но если задача допускает буферизацию журнала транзакций, то при создании базы данных это можно указать с помощью оператора CREATE DATABASE:

    CREATE DATABASE WITH BUFFERED LOG

    Буферизация журнала транзакций позволяет в несколько раз повысить скорость исполнения операторов INSERT, UPDATE и DELETE, но при этом практически не влияет на скорость работы оператора SELECT.

    5.11.3. Блокировка на уровне записей и страниц

    При рассмотрении уровней изоляции предполагалось, что блокировки ставятся на уровне записей (то есть блокируется отдельная запись). На самом деле, сервера Informix Dynamic Server позволяют выбирать уровень блокировки между блокировкой на уровне записи и блокировкой на уровне страницы. Практически во всех SQLсерверах (в том числе, и Informix Dynamic Server) память под таблицы выделяется порциями фиксированного объема — страницами или группами страниц. Обычно размер страницы составляет 2 или 4 килобайта. В целях повышения производительности можно блокировать записи не по одной, а целыми страницами.

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

    CREATE TABLE … LOCK MODE

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

    CREATE TABLE companies (
    company_id SERIAL UNIQUE,
    name CHAR(40),
    address CHAR(40)

    CREATE TABLE persons (
    person_id SERIAL UNIQUE,
    company INTEGER,
    lname CHAR(40),
    fname CHAR(30),
    sname CHAR(20),
    position CHAR(20)

    5.11.4. Эффективное построение запросов

    Производительность сервера базы данных при выполнении доступа к информации зависит не только от наличия или отсутствия индексов, буферизации журнала транзакций и т.д., но и от того, какие запросы исполняются. Не секрет, что одно и тоже действие (например, построение списка сотрудников какой-либо фирмы) можно запрограммировать поразному. Суммарная скорость исполнения данного действия зависит от того, какими операторами на SQL оно представлено. Рассмотрим пример. Допустим, у нас есть таблицы persons и companies (их структура привевдена выше, в пункте 5.11.3). Требуется построить поименный список сотрудников, работающих в фирме “АО Рога и Копыта”. Первый вариант реализации данного действия выглядит так:

    SELECT lname, fname FROM persons, companies
    WHERE persons.company = companies.company_id

    AND companies.name = “АО Рога и Копыта”

    При исполнении данного запроса при отсутствии индексов сервер базы данных должен будет перебрать все возможные комбинации записей из таблиц persons и companies, и для каждой комбинации проверить, выполняется ли условие из раздела WHERE. Если в таблице persons находится M записей, а в таблице companies — N записей, то всего будет проверено M*N комбинаций.

    Другой запрос, функцмионально реализующий тоже самое действие, а именно, формирование списка сотрудников АО Рога и Копыта выглядит следующим образом:

    SELECT lname, fname FROM persons
    WHERE persons.company IN
    (SELECT company_id FROM companies

    WHERE name = “АО Рога и Копыта”)

    При исполнении этого запроса, сервер вначале просмотрит таблицу companies и найдет одно требуемое значение, затем он просмотрит таблицу persons и сравнит поле company с найденным значением. В итоге, он просмотрит M+N записей. Если значения M и N достаточно велики (порядка сотен или более), то второй запрос будет исполняться много быстрее первого. Естественно, это очень грубая оценка без учета наличия индексов, возможностей сервера по оптимальному исполнению запросов и т.д., но выигрыш в полмиллиона раз раз для таблиц с миллионом записей каждая уже впечатляет.

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

    На основании этого правила можно сделать вывод о том, что иногда выгоднее разбить сложный запрос на несколько простых (с использованием временной таблицы), нежели пытаться уместить какое-либо действие в один запрос. Допустим, вас отправляют в командировку в г. Тверь, и вы хотите посетить директоров всех имеющихся в этом городе фирм-партнеров. Если данный запрос представить на SQL, то первый, неоптимизированный вариант запроса мог бы выгшлядеть следующим образом:

    SELECT persons.lname, persons.fname, persons.sname, companies.address
    FROM persons, companies
    WHERE companies.address MATCHES «*Тверь*»
    AND companies.companies_ >

    AND persons.position = “директор”

    Привести данный запрос к виду

    SELECT . . . FROM persons WHERE . . .

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

    CREATE TEMP TABLE local_companies (
    company_id INTEGER,
    name CHAR(40),
    address CHAR(40)

    <наполнение временной таблицы списком фирм, распорложенных в Твери>
    INSERT INTO local_companies(company_id, name, address)
    SELECT company_id, name, address
    FROM companies

    WHERE address MATCHES «*Тверь*»

    <получение требуемых данных>
    SELECT persons.lname, persons.fname, persons.sname,
    local_companies.address, local_companies.name
    FROM persons, local_companies
    WHERE local_companies.companies_ >

    AND persons.position = “директор”

    DROP TABLE local_companies

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

    5.11.5. Сортировка и поиск по коротким полям. Классификаторы

    Сортировка (т.е. использование раздела ORDER BY в операторе SELECT) требует очень больших времЕнных затрат. Особенно это касается больших таблиц. Сортировка, выполняемая по проиндексированным полям, происходит существенно быстрее, чем по непроиндексированным. Если же сортировка все же проводится по полям, для которых нет индекса, то лучше для сортировки использовать короткие поля (типа INTEGER, SMALLINT), нежели длинные (например, символьные). В условии WHERE операторов также предпочтительнее использовать короткие поля. Например, оператор

    SELECT address FROM companies WHERE name=”АО Рога и Копыта”

    будет в среднем выполняться дольше, нежели оператор

    SELECT address FROM companies WHERE company_ > Это правило можно распространить и на использование классификаторов. Классификатор — это специально введенная таблица, в которой хранится набор возможных значений какого-либо атрибута. Предположим, для каждой фирмы требуется хранить ее тип (госпредприятие, ООО, АОЗТ и т.д.). Набор типов предприятия ограничен. В принципе, в таблице companies нетрудно предусмотреть на этот случай специальное символьное поле:

    CREATE TABLE companies(
    . . . . .

    Сортировка и поиск по полю CHAR(20) nбудет происходить значительно медленне, чем по полю INTEGER. К тому же, если записей в таблице companies много, то использование поля INTEGER как ссылки на классификатор с типами предприятий вместо непосредственного задания этого значения в таблице даст экономию памяти. Поэтому более грамотным решением, на наш взгляд, является создание специального классификатора (таблицы types):

    CREATE TABLE types (
    type_id SERIAL UNIQUE,

    и введение в таблицу companies ссылки (внешнего ключа) на этот классификатор:

    CREATE TABLE companies(
    . . . . .

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

    SELECT name, address FROM companies
    WHERE type = (SELECT type_id FROM types

    WHERE name = “ООО”)

    5.12. Объектное расширение SQL в Informix DS/Universal Data Option

    5.12.1. Зачем нужна поддержка объектов в серверах БД?

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

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

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

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

    CREATE TABLE persons (
    tabel_num INTEGER, <табельный номер>
    last_name CHAR(40), <фамилия>
    first_name CHAR(40), <имя>
    second_name CHAR(40), <отчество>
    department INTEGER, <отдел>
    dolgnost CHAR(20), <должность>
    emp_from DATE, <работает с…>
    . . . . . .

    При такой структуре данной таблицы все атрибуты “свалены” в кучу. Более правильно было бы сгруппировать фамилию, имя и отчество в новый составной тип под названием “ФИО”, а отдел и должность — в тип “позиция”:

    CREATE ROW TYPE fio_t (
    last_name CHAR(40), <фамилия>
    first_name CHAR(40), <имя>
    second_name CHAR(40) <отчество>

    CREATE ROW TYPE position_t (
    department INTEGER, <отдел>
    dolgnost CHAR(20) <должность>

    После чего определить таблицу уже с учетом структурированных, упорядоченных атрибутов:

    CREATE TABLE persons (
    tabel_num INTEGER, <табельный номер>
    fio fio_t, <ФИО>
    position position_t, <позиция>
    emp_from DATE, <работает с…>
    . . . . . .

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

    Ограниченный набор базовых типов

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

    Нельзя сказать, что современные реляционные СУБД такой возможности не предоставляют совсем. Например, такие типы данных в Informix Dynamic Server, как BYTE и TEXT предназначены для хранения больших (до 2 ГБт) двоичных и текстовых объектов соответственно. Сервер обеспечивает только хранение таких объектов, он не может сам обрабатывать данные этих типов. Вся обработка должна проводиться на программеклиенте. То есть для того, чтобы, например, просмотреть все имеющиеся в базе данных отпечатки пальцев и отобрать те, которые похожи на заданный образец, программаклиент должна последовательно просмотреть все имеющиеся отпечатки. Проведение такого просмотра самим сервером, а, тем более, наличие индекса по отпечаткам, значительно бы ускорило обработку такого запроса.

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

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

    Рассмотрим простейший пример. Предположим, нам надо хранить информацию об адресах и делать выборку по улице. В Российских городах для названия улиц может использоваться дополнительная нумерация (1-я Парковая, 9-я Парковая) или прилагательное “Большой” или “Малый” (Малая Бронная). Соответственно, существует несколько способов записи таких названий. Следующие три названия обозначают одну и туже улицу:

    М.Бронная
    Бронная, М.

    Очевидно, что если мы будем использовать для хранения названия улиц обычные строковые типы, то придется следить за тем, чтобы в базе данных названия улиц хранились в едином, унифицированном формате. А при запросе на поиск по названию улицы потребуется преобразовывать введенное пользователем название к этому единому формату. Было бы удобнее, если бы можно было определить новый тип данных “Название улицы”, который бы делал это автоматически и, следовательно, минимизировал возможные ошибки. Для данного типа нужно было бы переопределить операцию сравнения, чтобы при сравнении двух значений учитывалось разное написание улиц. Кроме того, для данного типа потребуется переопределить операции сравнения, чтобы сортировка значений данного типа проводилась с учетом разных написаний:

    SELECT streets FROM table1 ORDER by streets

    . . . .
    Бауманская
    Б.Бронная
    М.Бронная
    Буденного

    Можно привести и другие примеры, когда стандартного набора базовых типов недостаточно. Введение в ту или иную СУБД новых предопределенных типов не может решить данную проблему — всегда найдется задачу, которую не предусмотрели.

    Сложные типы данных

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

    Клиент Informix на Linux

    Здесь располагаются заметки по установке клиента Informix на Linux.

    В качестве операционной системы применяется RedHat Enterprisr Linux AS 3 (Taroon). Как вы прекрасно понимаете, операционная система (Unix) может быть абсолютно любая, Linux в том числе. Рассматриваться же здесь будет только RHAS.

    В качестве DBMS применяется: IBM Informix Dynamic Server Version 9.40.UC2. Опять же, Informix-сервер может быть совершенно любой, в том числе SE, IDS, IUS и т.д. даже под Windows. Работу с сервером мы здесь не рассматриваем.

    Клиент, которым мы будем пользоваться : IBM Informix CSDK Version 2.81. На момент написания статьи — это самый последний клиент от IBM. См. CSDK

    Установка клиента


    Сначала насколько слов об «архитектуре» клиентов Informix. Существуют две разновидности клиента : Connect и Client SDK. На самом деле их конечно больше, но дальше уже начинаются «толстые» средства разработки Informix, типа 4GL.

    Клиент типа Connect содержит только необходимые библиотеки Informix (.so, NLS/GLS) и предназначен для запуска уже готовых приложений. Client SDK предназначен для разработки приложений и вдобавок содержит заголовочные файлы, примеры приложений и кроме того «пред-компилятор» ESQL/C.

    CSDK можно скачать с сайта IBM совершенно бесплатно, лицензия на него вбита прямо в скрипты инсталляции и ее нигде не требуют. Стандарта на имя дистрибутивного файла нет (встречаются самые странные комбинации из тире, точек, подчерков и сокращений), но в имени файла обязательно должна присутствовать латинская «U» — 32бит Unix (например «-UC1-«). Когда-то давно Informix, а позже IBM раздавали CSDK в формате RPM, по крайней мере встречался csdk-2.70.UC3-1.i386.rpm, но теперь раздают только .tgz :( Если кому-то встретится, дайте знать.

    Итак, предположим мы скачали clientsdk.2.81.UC3.LINUX-I32.tar длиной почти 67Мб.

    Внутри оказывается архив программы cpio. Развернем его.

    Все готово к инсталляции. Теперь займемся системой.

    Надеюсь, в RPM все эти процедуры выполнялись автоматически. К сожалению, новых версий CSDK в формате RPM больше нет, так что будем ставить все руками (пусть даже нарушая стандарты RedHat).

    1) Ставить будем в каталог /opt/informix где собственно ему и место (для продуктов 3-х фирм)

    2) Необходимо завести пользователя informix и группу informix. Номера u >

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

    3) Необходимо создать домашний каталог пользователя informix и назначить ему владельца :

    ВНИМАНИЕ: Это очень важный каталог, именно он обозначается как INFORMIXDIR=/opt/informix

    4) Для серверов версии >9 инсталляция клиента должна происходить от имени пользователя informix. Зачем — не известно, но не будем нарушать правила.

      Прикинемся на время пользователем informix:
      # su — informix
      $ cd

    Скопируем все развернутые файлы: cpio и скрипты в домашний каталог

    /informix
    $ cp /dist/ifx/* .

  • Установим переменную окружения
    $ export INFORMIXDIR=/opt/informix
  • Запускаем инсталляцию
    $ ./installclientsdk
  • Клиент установлен. Файлы *.cpio инсталляционный скрипт убьет самостоятельно.

    Настройка клиента

    1) Необходимо установить переменные окружения Informix. Для того, чтобы они были доступны всем пользователям, в том числе и например, серверу Apache, их следует сделать системными. В дистрибутиве RedHat есть очень удачное место — каталог /etc/profile.d где лежит масса настроечных скриптов, экспортирующих кучу переменных.

    И сделать этот файл исполняемым. Обратите внимание, что локаль сервера — Windows-1251, а клиента — KOI8-R

    2) Начинаем настраивать доступ к серверу. Первым делом необходимо отредактировать файл $INFORMIXDIR/etc/sqlhosts . Здесь хранятся названия серверов и способ доступа к ним.

    Формат этого файла очень простой.

    1. Имя сервера баз данных. Собственное имя сервера Informix, к которому осуществляется доступ.
    2. Тип доступа (nettype). Сложное поле, состоящее из трех частей
      1. Тип сервера: on — Online Server, IDS; se — Infornix SE server
      2. Способ соединения: soc — Сокеты; ipc — локальный Interprocess Communication
      3. Конкретный способ соедиенения: tcp — TCP/IP; shm — разделяемая память
    3. Имя сервера (хоста), на котором крутится база данных. Для TCP/IP — доменное имя или IP адрес.
    4. Имя сокета или порта TCP/IP для доступа к серверу. (Чаще всего порт 1425)
    5. Опции. Нужны для некоторых типов соедиения

    Имя сервера Informix и имя хоста — совершенно разные вещи. На одном хосте (теоретически) может крутится несколько серверов баз данных.

    Хост (компьютер) должен отвечать по доменному имени (резолвится в DNS). В противном случае необходимо прописать этот хост в /etc/hosts

    Имя порта для TCP/IP должно быть прописано в /etc/services

    2) Необходимо прописать путь к динамическим библиотекам в /etc/ls.so.conf

    и запустить ldconfig.

    На этом настройку клиента можно считать законченной.

    Утилиты.

    К сожалению, в состав клиента Informix не входит утилит диалогового режима SQL (как например mysql). Такая утилита (ISQL, она же DB-Connect ) в принципе существует и входит в состав некоторых коммерческих продуктов Informix (Server, 4GL) которые мы не рассматриваем.

    Однако существуют утилиты клиента Informix от других разработчиков, например SQLCMD.

    Установка DBD::Informix.

    Для доступа к базам данных Informix из PERL применяется общий для PERL модуль DBI/DBD.

    На сайте CPAN имеется модуль Informix DBI, самый последний на момент написания текста : DBD-Informix-2003.04

    Первым делом, необходимо обновить модуль perl-DBI на версию выше >1.33. Например RPM, можно взять из RedoraCore, он прекрасно работает на RHAS 3 (тянет за собой perl-Time-HiRes).

    SQL + Informix: как добавить BLOB-объект при вставке? (Использование .NET (C #) SDK)

    Я использую Informix и .NET SDK (C #):

    В принципе, есть ли способ вставить BLOB-объект при выполнении стандартного SQL-оператора вставки?

    Да, и у меня есть данные в виде массива byte [].

    4 ответа

    1) Вы должны использовать параметризованные запросы

    Несколько вещей, на которые следует обратить внимание: Informix имеет ошибку, когда дело доходит до Client SDK 3.5.xC7 (пока). Вы можете легко передать байтовый массив во время вставок, но при обновлении у вас будет ошибка 609, если вы передадите массив byte []. Вместо этого вы должны использовать объект IfxBlob .

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

    Кроме того, имейте в виду, что если вы пытаетесь установить null , вы получите ошибку. Вместо этого, если значение равно нулю, передайте DBNull.Value.

    НЕ УКАЗЫВАЙТЕ ТИП ПАРАМЕТРА

    Если вы сделаете один из них, вам нужно будет сделать следующее:

    • Если значение Blob не равно нулю : «INSERT INTO mytable (name, theblob) VALUES (?, . blob);»;
    • Когда значение Blob равно нулю : «INSERT INTO mytable (name, theblob) VALUES (?, . byte);»;

    Вы можете видеть, что будет неприятно иметь разные запросы из-за типа и значения. Только не указывайте DbType или IfxType и позволяйте провайдеру Informix .NET отображать правильные типы для вас (даже если он не может правильно отображать массив byte [] при обновлении).

    Надеюсь, это сработает для вас, потому что я прошел через те же усилия, пытаясь выяснить это и обнаружив, как мне кажется, ошибку в провайдере Informix .NET (версия 3.5.xC7).

    Microsoft SQL Server Data Tools

    В данной статье хотел бы рассказать про набор полезных дополнений к Visual Studio, которые могут в значительной мере облегчить разработку баз данных на основе MS SQL Server.
    Основными преимуществами использования SSDT я бы выделил следующее:

    • возможность простого изменения (refactoring) схемы базы (можно переименовать колонку таблицы и все Views, Functions и Stored Procedures ссылающиеся на неё автоматически будут исправлены для отражения изменений)
    • создание юнит тестов для базы данных
    • хранение структуры базы данных в Source Control
    • сравнение схемы/данных c возможностью генерации скрипта для приведения схемы/данных к требуемому состоянию

    Безусловно на этом плюсы использования SSDT не заканчиваются, но остальное не так сильно впечатляет, как то, что упомянуто выше. Если вас интересует, как воспользоваться этими и другими преимуществами — прошу под кат.

    Установка и первое знакомство

    Всё необходимое для установки можно найти на странице загрузки в Data Developer Center. Выбрав необходимую версию вы сможете без труда установить инструменты на свой компьютер и описывать это не вижу смысла. После установки в окне создания нового проекта у вас появится новый тип проекта:

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

    На панели SQL Server Object Explorer (меню View -> SQL Server Object Explorer) мы видим нечто очень похожее на Object Explorer в SQL Server Management Studio, из которого убрано всё, что не имеет большого смысла на этапе разработки базы данных.
    Подключившись к существующей базе, можно производить разработку базы данных в так называемом Connected режиме. Это мало чем отличается от классического подхода используемого в SQL Server Management Studio и в данной статье рассматриваться не будет.

    Disconnected режим

    Этот режим разработки нам наиболее интересен, т.к. именно он позволяет получить основные преимущества использования SSDT.
    В основе работы лежит очень простая идея – позволить разработчикам хранить все скрипты создания объектов БД (tables, views, store procedures и т. д.) в проекте специального типа в составе имеющегося или нового решения (solution). На основе скриптов, Visual Studio может сгенерировать DACPAC файл, который по сути является zip архив со всеми t-sql скриптами. Имея DACPAC файл можно будет произвести публикацию (publish) на требуемом экземпляре базы данных, путём сравнения схемы описанной в DACPAC и схемы в целевой базе данных. В ходе публикации, специальные механизмы производят сравнения, в результате чего автоматически создаются миграционные скрипты для применения изменений без потери данных.
    Для того что увидеть это в действии, предлагаю посмотреть следующие примеры.
    Начнём с возможности импорта. Вызываем контекстное меню проекта и видим 3 возможных варианта:

    • Script (*.sql) – добавляет один или несколько *.sql файлов из заданного расположения в структуру проекта;
    • Data-tier Application (*.dacpac) – добавляет *.sql файлы, а так же различные настройки базы данных из специального DACPAC файла, описанного выше; может содержать не только схему базы, но так же данные и различные настройки базы;
    • Database… — аналогичен предыдущему варианту, но источником данных служит существующая база

    Мы выберем вариант “Database…” и импортируем локальную базу. Она содержит одну таблицу и одну хранимую процедуру. В SQL Server Object Explorer исходная база выглядит следующим образом:

    После завершения импорта мы увидим крайне похожую картину, с тем единственным различием, что структура базы будет представлена в Solution Explorer в качестве *.sql файлов.

    Так же мы всегда можем добавить новые элементы воспользовавшись диалоговым окном Add New Item, в котором перечислены все возможные объекты базы данных:

    Добавим таблицу TestTable. Новый файл-скрипт TestTable.sql будет добавлен в корень проекта и для удобства мы его перенесём в папку Tables.

    Для создания схемы таблицы мы можем использовать как панель дизайнера, так и панель T-SQL. Все изменения сделанные на одной панели будут сразу же отображены в другой.
    Так же мы можем изменять существующие скрипты. Visual Studio для этого предоставляет удобный и любимый всеми IntelliSense. Так как мы не подключены к физической базе данных, Visual Studio для корректной работы IntelliSence парсит все скрипты в проекте, что позволяет ей мгновенно отражать последние изменения сделанные в схеме базы данных.

    Хочу обратить внимание на то, что мы не должны заботиться об инкрементных изменениях нашей базы. Вместо этого мы всегда создаём скрипты так, как если бы объекты создавались заново. При публикации DACPAC пакета миграционные скрипты будут сгенерированы автоматически, путём сравнения DACPAC файла и схемы в целевой базе (target Database).
    Как уже упоминалось, DACPAC содержит не только схему и данные, но ещё и ряд полезных настроек, для просмотра/редактирования которых мы можем воспользоваться окном свойств нашего проекта.

    Свойство Target platform позволяет выставить версию базы данных, для которой будут валидироваться скрипты в проекте. Минимальная поддерживаемая версия MS SQL Server 2005. Если например задать версию базы 2005 и попробовать создать колонку типа Geography, то при компиляции мы получим следующее сообщение:

    На закладке Project Settings, мы можем задать настройки базы данных, нажав на кнопку Database Settings. Нажав на неё мы увидим диалог с настройками, аналогичные тем, что мы привыкли видеть в SQL Server Management Studio:

    Так же хочется упомянуть закладку SQLCMD Variables, на которой мы можем задавать различные переменные для дальнейшего их использования в наших скриптах.

    Публикация DACPAC файла (publishing)

    После того, как все настройки заданы и *.sql скрипты добавлены/обновлены мы можем применить изменения к целевой базе (target database). Для этого идём в меню Build->Publish или же выбираем аналогичный пункт в контекстном меню проекта.

    В появившемся диалоговом окне задаём строку подключения к базе назначения (target database) и если необходимо — дополнительные настройки, нажав на кнопку Advanced:

    Большинство настроек понятны без дополнительного описания, поэтому не будем на них останавливаться подробно, но рекомендую с ними ознакомиться, что бы в случае невозможности успешно «запаблишить» проект, вы знали в чём может быть проблема.

    Если требуется производить публикацию в целевую базу данных более одного раза, то настройки можно сохранить в publish профиль, нажав на кнопку Create Profile. Это добавит в наш проект файл с расширением *.publish.xml и в дальнейшем мы сможем произвести публикацию без необходимости вводить настройки ещё раз. Если же какой-то из профилей публикации должен быть использован по умолчанию, то можно в контекстном меню файла публикации выбрать пункт Set As Default Publish Profile. Этот профиль будет автоматически загружаться в диалог Publish.

    Все необходимые изменения, можно применить сразу, нажав на кнопку Publish. А можно отложить на потом, сгенерировав соответствующий миграционный скрип (кнопка Generate Script) — он будет содержать все необходимые инструкции для приведения базы назначения к требуемому состоянию.
    Если же у нас нет доступа к базе данных, то мы можем передать результаты нашего труда в виде DACPAC файла, который создаётся путём компиляции проекта и находится в ../bin/Debug/Database1.dacpac. Отдав файл, например, администратору базы данных, тот в свою очередь сможет воспользоваться любым удобным способом для применения изменений в целевой базе.
    Способы публикации DACPAC (publishing):

    • Бесплатная редакция Visual Studio с установленными SSDT (в частности для publish используются клиентские инструменты, входящие в состав DAC Framework, устанавливаемые вместе с SSDT)
    • MS SQL Server Management Studio + DAC Framework
    • Консольная утилита SqlPackage.exe
    • Windows PowerShell (пример)
    • Data-tier Application Framework (DACFx) позволяющий поставить DACPAC файл, путём вызова методов из C# программы (документация и пример)

    Data Seeding

    В нашем проекте создадим папку DataSeeding (имя не имеет значения) и в неё добавим новый скрипт.

    По сути все типы в разделе User Script являются обычными *.sql скриптами и отличаются лишь значением свойства “Build Action” у вновь созданного файла.

    Логика из файла PostDeployment.sql будет выполнена после применения всех изменений схемы базы данных. В случае создания PreDeployment.sql — логика выполнится перед применением изменений схемы.
    Значение свойства Build Action для файлов созданных через шаблон Script (Not in Build) будет установлено в «None». Они полезные для удобного структурирования команд в отдельных файлах, которые вызываются из Pre или Post Deployment скриптов.
    Файлы созданные через шаблон Script имеют значение Build Action равное «Build», и их содержимое добавляется к результирующему скрипту, который выполняется при publish’e DACPAC файла в момент изменения схемы базы.
    В виду того, что в проекте может быть только один Post Deployment script и его размер может быстро вырасти, рекомендуется логику вставки данных выносить в отдельные скрипты. Именно поэтому мы добавим файл типа Script (Not in Build), а в Post Deployment script добавим ссылку на него. Вот как это будет выглядеть:

    Теперь при публикации нашего проекта, в базе всегда будут вставлены 2 записи в таблицу Employees.

    Tools -> SQL Server

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

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

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

    В результирующем окне мы можем применить различные способы группировки (по схеме, по типу объектов и по требуемому действию) для более удобного просмотра предлагаемых изменений и выбрать те объекты, которые требуется обновить. Для того, что бы применить миграционный скрипт необходимо нажать кнопку Update – это приведёт Target DB к состоянию нашего проекта.

    Refactoring

    Это моя любимая фича. Для примера, покажем как переименовать колонку LastName в таблице Employees. Для этого открываем скрипт создания таблицы, в редакторе таблицы выделяем колонку LastName и в меню SQL -> Refactor выбираем пункт Rename:

    Задаём новое имя:

    Просматриваем последствия переименования и применяем предложенные изменения:

    В результате все скрипты будут изменены и после первого рефакторинга в проект будет добавлен специальный файл *.refactoring. В нём будут сохраняться все изменения схемы в историческом порядке в формате XML документа. Эти данные будут полезны при генерации миграционного скрипта и позволят более правильно мигрировать схему и данные.

    Unit testing

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

    В появившемся диалоговом окне у нас будет возможность выбрать дополнительные объекты (если они есть) и задать тип и имя тестового проекта и имя класса, содержащего код юнит теста:

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

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

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

    А вот так можно проверить Checksum:

    По сути эта проверка выполняет наш скрипт (получает 2 строки из таблицы Employees) и на полученных данных находит Checksum. Наша задача на этапе создания теста, найти эталонные данные, на них посчитать Checksum и в дальнейшем с этим значением будет производиться сверка полученного результата. Иными словами, это удобный способ убедиться, что результат хранимой процедуры не меняется. Для получения контрольного значения Checksum, необходимо воспользоваться кнопкой в окне Properties, которая позволит выбрать эталонную базу и получить эталонное значение Cheсksum:

    Илон Маск рекомендует:  Получи, клиент, рассылку
    Понравилась статья? Поделиться с друзьями:
    Кодинг, CSS и SQL