Примеры: DEFINE user_id INTEGERDEFINE comp_name CHAR(40) EXIT - прекратить выполнение цикла. После этого слова надо указать какой именно цикл надо прекратитьвыполнять - FOR/WHILE/FOREACH. Примеры: EXIT WHILE....EXIT FOR FOR - цикл со счетчиком. Аналогичен оператору цикла со счетчиком в других языках программирования. Значения шага, начального и конечного значений вычисляются заранее, до начала выполнения операторов тела цикла: FOR <переменная> = <целое> TO <целое> STEP <целое> <операторы>END FOR Если шаг (STEP) не указан, то он принимается равным 1 если начальное значение меньше конечного, и -1, если нет. Пример: FOR i = 1 TO 14 ....END FOR IF - условный оператор. Проверяет условие и, если оно верно, выполняет операторы после слова THEN. Если не верно, и присутствует раздел ELSE - то выполняются операторы после слова ELSE: IF <условие> THEN <операторы> END IFIF <условие> THEN <операторы> ELSE <операторы> END IF Если после слова ELSE по логике должен следовать другой оператор IF, то такое сочетание ELSE IF ... END IF END IF можно записывать более компактно: ELIF ... END IF. Пример: IF j > 20THEN RETURN jELIF j > 10 THEN RETURN 10 ELSE RETURN 0END IF LET - оператор присваивания. Вычисляет выражение и присваивает его значение указанной переменно: LET <имя переменной> = <выражение> Выражение, которое может быть здесь использовано, очень похоже на выражение, которое может использоваться в операторе SELECT. Примеры: LET i = 0LET str1 = "абвгдежз" RAISE EXCEPTION - возбудить ошибку. Если на данную ошибку не стоит реакция (нет соответствующего оператора ON EXCEPTION), то исполнение хранимой процедуры завершается и в вызвавшую программу "передается" данная ошибка: RAISE EXCEPTION <номер ошибки> Можно в качестве номера ошибки использовать как предопределенные, стандартные номера, так и свои собственные. RETURN - завершает исполение хранимой процедуры. Управление передается в вызвавшую программу. После слова RETURN надо указывать возвращаемые значения, если процедура должна возвращать значения: RETURN <выражение 1>, .... SYSTEM - позволяет выполнить внешнюю команду. Имя этой внешней команды передается операционной системе, то есть это может быть или встроенная команда ОС, или исполнимый файл: SYSTEM <символьная строка>SYSTEM <имя символьной переменной> Например, можно выполнить командный файл my_cmd: SYSTEM "/usr/local/bin/my_cmd" WHILE - цикл с завершением по условию. Аналогичен циклу WHILE в других языках программирования: WHILE <условие> <операторы>END WHILE Пример: LET i = 1WHILE i = 1 EXECUTE PROCEDURE my_proc RETURNING IEND WHILE BEGIN ... END - блок операторов. В блоке операторов можно определять и использовать локальные переменные. Допустимые любые другие операторы SPL. Пример: BEGIN DEFINE i CHAR(20) LET i = "Ну, погоди!" IF ... THEN BEGIN DEFINE i INTEGER LET i = 0 .... END END IFEND 5.7.4. Триггеры Идея триггеров Иногда существуют какие-то действия, которые надо выполнять всегда при модификации той или иной таблицы. Например, есть таблица с важными данными и любое изменение надо фиксировать - заносить в протокол кто, что и когда модифицировал в данной таблице. Или, например, поддержание целостности базы данных - при удалении из базы данных информации о некоторой фирме, надо удалить и информацию о работающих в ней людях. Для подобных задач в SQL введено понятие триггера, Триггер - это механизм, который автоматически выполняет некоторый набор SQL-операторов когда происходит некоторое событие. То есть триггер задается парой "событие-действие". Событиями, на которые можно установить триггер, являются модификации данных, то есть операторы DELETE, INSERT, UPDATE. Причем, триггер связан с конкретной таблицей. То есть событием, вызывающим триггер (триггерным событием) является выполнение операторов удаления, вставки или модификации конкретной таблицы. Триггер хранится как объект в базе данных, то есть принадлежит схеме базы данных. ) Создание и удаление триггера Для создания и удаления триггеров, как и для таблиц, и для хранимых процедур, используются операторы CREATE и DROP. CREATE TRIGGER <имя> <событие> <действие>DROP TRIGGER <имя> Имя триггера - обычное имя в SQL. Главное, что бы оно было уникальным. Событие указывает момент, когда триггер срабатывает. Триггеры бывают трех типов - на вставку нового ряда в таблицу, удаление ряда и модификацию каких-либо полей. Для каждой таблицы могут существовать все три типа триггеров, как, впрочем, и не существовать ни одного. При этом, если для каждой таблицы может существовать только по одному триггеру на вставку и удаление, то триггеров на обновление можжет быть несколько в зависимости от того, какие поля обновляются (но не может быть двух триггеров на обновление одного и того же поля). Соответственно, синтаксис на раздел "событие" при создании триггера будет следующим: INSERT ON <имя таблицы>DELETE ON <имя таблицы>UPDATE ON <имя таблицы>UPDATE OF <поле>, <поле>, ... ON <имя таблицы> Событием для триггера является сам факт выполнения оператора вставки, удаления или модификации. То есть, если даже оператор удаления не удалил ни одной записи (ни одна запись не удовлетворила условию), все равно триггер на удаление сработает. Примеры операторов создания триггеров, пока без написания того, что они будут исполнять: { триггер на вставку нового документа }CREATE TRIGGER trig1 INSERT ON documents .... { триггер на удаление информации о фирме }CREATE TRIGGER del_trig DELETE ON companies .... { триггер на обновление цены товара }CREATE TRIGGER upd_price UPDATE OF price ON items .... { триггер на изменение имени или фамилии человека }CREATE TRIGGER upd_name UPDATE OF lname, fname ON persons .... Теперь рассмотрим, как описываются действия триггера. Каждое действие состоит из описания того, что выполняется единожды перед началом исполнения оператора, вызвавшего событие для триггера (раздел BEFORE), для каждого ряда (раздел FOR EACH ROW) и после исполнения оператора (раздел AFTER). CREATE TRIGGER .... ON ... BEFORE <операторы> FOR EACH ROW <операторы> AFTER <операторы> Можно использовать произвольное сочетание из разделов 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 имеет следующую структуру и состоит из следующих записей: CREATE TABLE companies ( company_id SERIAL { уникальный идентификатор фирмы } name CHAR(40) { название фирмы } address CHAR(60) { адрес фирмы }) +------------+----------------------+------------------+| company_id | name | address |+------------+----------------------+------------------+| 101 | АО Рога и Копыта | Одесса, п/я 13 || 105 | ТОО Добро пожаловать | Энск, 5-е авеню || 107 | АОЗТ Сделай сам | Городская свалка |+------------+----------------------+------------------+ При такой структуре данной таблицы и при таком ее содержании оператор UPDATE companies SET name = "ИЧП Мастер Безенчук" WHERE company_id = 101 приведет к исполнению триггера 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 < 57 А следующий оператор вообще не приведет к выполнению триггера upd_trig, так как поле name данной таблицы не изменяется и его вообще нет в списке обновляемых полей: UPDATE companies SET address = "Москва, Бутырка" WHERE company_id = 101 Имена для старого и нового значений записи Вернемся к тем задачам, с которых мы начали разговор о триггерах. А именно, о необходимости удалять информацию о сотрудниках при удалении фирмы и о протоколировании всех изменений в таблице. Для того, чтобы реализовать эти задачи надо из триггера получить значения полей модифицируемой записи. Например, при удалении информации о фирме нам надо знать уникальный ключ удаляемой запии - поле 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 TABLE persons (..., company INTEGER, { ссылка на фирму, где работает } ....) 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 TABLE protocol ( company INTEGER, { идентификатор измененной записи } login CHAR(8), { ситемное имя пользователя } oldname CHAR(40), { старое имя фирмы } newname CHAR(40), { новое имя фирмы } when DATETIME YEAR TO SECOND { когда }) Тогда, требуемый нам триггер будет выглядеть так: 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, newcomp.name, CURRENT) Условия внутри триггера Триггерным событием является как само выполнение оператора, изменяющего таблицу, так и непосредственное изменение какой-либо записи. Но можно наложить и дополнительное условие. Для этого перед набором операторов (которые, напомним, разделены запятой и заключены в круглые скобки) надо поставить условие: ........WHEN ( <условие> ) ( <оператор>, <оператор>, ...),WHEN ( <условие> ) ( <оператор>, <оператор>, ...),........ Для каждого из разделов 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 < 0) (EXECUTE PROCEDURE proc1(post.price)) 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 и логическое выражение в скобках: CREATE TABLE <имя таблицы> ( ..... <имя поля> <тип поля> [NOT NULL] [<значение по умолчанию>] CHECK (<логическое выражение>) .....) Это логическое выражение и будет опредеять допустимость значения. Условие проверяется перед изменением данных в поле операторами UPDATE или INSERT. Если значение логического выражение ложь или NULL, то сервер базы данных возвращает ошибку. Если вычисленное логическое выражение имеет значение истина, то новое значение считается допустимым и операция модификации завершается успешно. Например, можно описание таблицы orders расширить следующим образом: CREATE TABLE orders ( ..... item INTEGER NOT NULL CHECK (EXISTS (SELECT items.item_id FROM items WHERE items.item_id = 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 символов. Тогда требуемая таблица будет выглядеть так: CREATE TABLE trademarks ( name CHAR(64) UNIQUE, { название торговой марки } company INTEGER, { какой компании принадлежит } .... ) Отличие типа 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 соотношением один-к-одному, тем не менее должна рассматриваться как пдчиненная последней, так как поставляемые товары могут существовать без конкретных заказов (мы этот товар еще ни разу не покупали), а наоборот - нет. В соответствии с этим, в подчиненной таблице хранится ссылка на главную, а не наоборот. Итак, в главной таблице есть некоторый первичный ключ, а в подчиненной таблице есть ссылка, представляющая собой поле (или поля), хранящие значение первичного ключа главной таблицы: +-------------+ +-------------+ +----------+ | companies | ++------------+| ++---------+| +-------------+ ++------------+|| ++---------+|| +->| company_id |<--+ | persons ||| | items ||| | | name | | +-------------+|| +----------+|| | | address | | | person_id ||| | item_id ||| | | phone | +--+- company ||| | company -+----+ +-------------+ | lname ||| | name |++ | fname |++ | price ++ | sname ++ +----------+ +-------------+ Для того, чтобы поддерживать эти связки и, в частности, чтобы не возникала информация о людях и товарах, принадлежащих не существующей фирме, и вводятся ссылочные ограничители. Ссылочный ограничитель состоит из двух частей - указания первичного ключа в гланой таблице и ссылки на первичный ключ в подчиненной таблице. Первичный ключ задается ключевыми словами PRIMARY KEY, которые ставятся после типа поля или значения по умолчанию. Ссылка на первичный ключ из вторичной таблицы задается словом REFERENCES, после которого идет имя таблицы и в скобках имя поля с первичным ключом: CREATE TABLE <имя таблицы> ( ..... <имя поля> <тип поля> [NOT NULL] [<значение по умолчанию>] PRIMARY KEY, ....., <имя поля> <тип поля> [NOT NULL] [<значение по умолчанию>] REFERENCES <имя таблицы> (<имя поля>) .....) Если попытаться написать схему нашей базы данных с использованием ссылочных ограничителей, то получится примерно следующее: 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 в скобках перечисляются поля из составного первичного ключа: .... UNIQUE (<имя поля>, <имя поля>, ...) Ссылочный ограничитель для таблицы, так же как и для одного поля, состоит из описания первичного ключа и ссылки на первичный ключ. Первичный ключ задается с помощью ключевых слов PRIMARY KEY, после которых в скобках через запятую идут составляющие его поля. Ссылка на первичный ключ начинается со слов FOREIGN KEY, после которых, также в скобках через запятую, перечислены составляющие ссылку поля. Затем после слова REFERENCES надо указать имя таблицы и в скобках поля первичного ключа для главной таблицы: .... PRIMARY KEY (<имя поля>, <имя поля>, ...).... FOREIGN KEY (<имя поля>, <имя поля> REFERENCES (<имя поля>, <имя поля>, ...) Очевидно, должно быть соответствие между порядком и количеством полей после слов 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) по таблицам (см. ниже), если он имеет права на выборку по требуемым таблицам;·создавать синонимы (см. ниже);·создавать временные таблицы и индексы по временным таблицам;·изменять или удалять те объекты, которыми владеет;·управлять правами других пользователей на объекты, которыми владеет. Пользователь, имеющий права на управление ресурсами (RESOURCE), в дополнение к тем правам, которые имеют пользователии с правом на доступ, может также создавать новые объекты. Например, такой пользователь может создать таблицу, триггер, индекс и т.д. Как только он создает какой-то объект, он становится его владельцем. Право на администрирование базы данных (DBA) подразумевает следующие возможности:·удалить базу данных (выполнить оператор DROP DATABASE);·удалять любые объекты вне зависимости от того, кто ими владеет;·раздавать и менять права доступа других пользователей к базе данных в целом и к отдельным объектам. Когда пользователь создает базу данных, он автоматически получает право на администрирование этой базы. Никакие другие пользователи не имеют никаких прав по отношению к данной базе данных. Для того, чтобы другие пользователи смогли иметь какие-то права на данную базу, эти права надо явно передать. Для этого используется оператор GRANT <тип права на базу данных> TO <имя пользователя> При управлении правами на уровне базы данных имя базы не указывается. Подразумевается текущая база данных. Примеры оператора GRANT: GRANT DBA TO andy GRANT RESOURCE TO micky В качестве имени пользователя можно использовать слово PUBLIC ­оно означает всех возможных пользователей. Например, если мы хотим передать право на доступ всем пользователям, то надо выполнить оператор GRANT CONNECT TO PUBLIC Права могут не только передаваться, но и отбираться. Естественно, для этого надо иметь право на администрирование базы данных. Отбор права выполняется оператором REVOKE <тип права на базу данных> FROM <имя пользователя> Например: 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 - все перечисленные выше права на таблицу. Синтаксис оператора передачи права на таблицу выглядит следующим образом: GRANT <тип права на таблицу> ON <имя таблицы> TO <имя пользователя> Вместо имени пользователя можно использовать слово PUBLIC, означающее всех пользоователей. Например: GRANT ALL ON persons TO mickyGRANT SELECT ON persons TO romaGRANT INSERT ON items TO PUBLIC В тех случаях, когда при передаче права на таблицу можно специфицировать конкретные поля (права UPDATE, SELECT и REFERENCES), то имена полей надо указать в скобках после названия права: GRANT <тип права на таблицу> (<имя поля>, <имя поля>, ....) ON <имя таблицы> TO <имя пользователя>