Представлення (InterBase та Firebird)
Alexey Kovyazin, останнє оновлення 13 квітня 2012 року
Ті, хто знайомий з мовою SQL, не потребують детальних пояснень цього предмета, але для збереження послідовності викладу ми наведемо коротке визначення уявлень (view).
VIEW (уявлення) - це віртуальна таблиця, створена на основі запиту до звичайних таблиць. Уявлення реалізується як запит, що зберігається на сервері та виконується щоразу, коли ми звертаємося до уявлення.
Розглянемо різні варіанти використання уявлень. Уявлення дозволяють створювати рівні структури даних, що дають змогу відокремити реалізацію зберігання даних від їх типу. Наприклад, ми можемо створити уявлення, яке вибирає дані з кількох таблиць. Якщо клієнти використовують це уявлення замість прямого звернення до базових таблиць, розробник бази даних зможе змінити запит, що лежить в основі уявлення, змінити його (наприклад, для оптимізації), і клієнт нічого не помітить - для нього це буде те саме уявлення. Окрім того, що вони ізолюють реалізацію зберігання даних від користувача, уявлення дозволяють організувати дані зручніше та простіше. Проблема «спрощення» структури даних виникає, коли кількість таблиць у базі даних стає достатньо великою, а зв’язки між ними - складними. Уявлення дозволяє усунути (або, навпаки, додати) частину даних, необхідних конкретному клієнту бази даних (або - необхідних).
Крім того, уявлення дозволяють простіше організувати безпеку в базі даних InterBase. Деякі користувачі можуть мати права лише на читання/оновлення даних в уявленні, але не мати прав (і навіть уявлення) про таблиці, що лежать в основі уявлення! Детальніше про безпеку в InterBase див. розділ «Безпека в InterBase: користувачі, їх функції та права» (частина 4).
Синтаксис DDL для роботи з уявленнями
Тепер розглянемо команди для створення та видалення уявлень, визначені DDL (Data Definition Language - підмножина SQL, див. глосарій). Щоб створити уявлення в InterBase, слід використати речення наступного синтаксису:
CREATE VIEW ім’я_уявлення [(стовпець_уявлення[, стовпець_уявлення…])] AS [WITH CHECK OPTION]; Тут ім’я_уявлення - це назва уявлення, яка має бути унікальною в межах бази даних, а далі йде група не завжди обов’язкових назв полів, включених в уявлення: [(стовпець_уявлення [, стовпець_уявлення …])]. Важливо визначити оператор , який вибирає дані, включені в уявлення. Необов’язковий параметр WITH CHECK OPTION ми обговоримо трохи пізніше в частині «Змінювані уявлення».
Щоб змінити уявлення, нам доведеться перестворити його, тобто видалити та створити заново. При видаленні уявлення необхідно також видалити всі залежні об’єкти - тригери, збережені процедури та інші уявлення. Це одна з головних незручностей роботи з уявленнями: необхідність перестворювати дерево об’єктів, які використовують уявлення (існують утиліти, які дозволяють зробити це простіше, наприклад IBAlterView, див. додаток «Інструменти адміністратора та розробника InterBase»). Для видалення уявлення слід використати наступну команду DDL:
DROP VIEW ім’я_уявлення;
Приклади уявлень
Ось приклад простого уявлення:
CREATE VIEW MyView AS SELECT NAME, PRICE_1 FROM Table_example;
У цьому прикладі ми створюємо уявлення на основі запиту до таблиці Table_example, яку ми розглянули в розділі «Таблиці. Первинні ключі та генератори». У цьому випадку уявлення складатиметься з двох полів - NAME і PRICE_1, які будуть вибрані з таблиці Table_example без жодних умов, тобто кількість записів в уявленні MyView дорівнюватиме кількості записів у Table_example. Однак уявлення не завжди такі прості. Вони можуть базуватися на даних з кількох таблиць і навіть на основі інших уявлень. Крім того, уявлення можуть містити дані, отримані на основі різних виразів - включно з агрегатними функціями. Щоб детальніше розглянути використання цього застосування уявлень, створімо дві таблиці, з’єднані зв’язком один-до-багатьох (такий зв’язок часто називають master-detail). Ось DDL-скрипт для створення цих таблиць:
/\* Таблиця: WISEMEN */
CREATE TABLE WISEMEN ( ID_WISEMAN INTEGER NOT NULL, WISEMAN_NAME VARCHAR(80));
/\* Визначення первинних ключів */
ALTER TABLE WISEMEN ADD CONSTRAINT PK_WISEMEN PRIMARY KEY (ID_WISEMAN);
/\* Таблиця: WISEBOOK */
CREATE TABLE WISEBOOK ( ID_BOOK INTEGER NOT NULL, ID_WISEMAN INTEGER, BOOK VARCHAR(80));
/\* Визначення первинних ключів */
ALTER TABLE WISEBOOK ADD CONSTRAINT PK_WISEBOOK PRIMARY KEY (ID_BOOK);
/\* Визначення зовнішніх ключів */
ALTER TABLE WISEBOOK ADD CONSTRAINT FK_WISEBOOK FOREIGN KEY (ID_WISEMAN) REFERENCES WISEMEN (ID_WISEMAN);
Отже, ми створили дві таблиці - WISEMEN і WISEBOOK, з’єднані зв’язком master-detail за допомогою обмеження зовнішнього ключа - FOREIGN KEY. Припустімо, що ці таблиці зберігатимуть інформацію про великих китайських мудреців та їхні твори. Тепер ми можемо створити кілька уявлень на основі цих таблиць. Наприклад, створімо уявлення, яке показує, скільки творів має кожен мудрець:
CREATE VIEW WiseBookCount (WISEMAN, HOW_WISEBOOKS) AS SELECT M.WISEMAN_NAME, COUNT(B.BOOK) FROM WISEMEN M, WISEBOOK B WHERE (M.ID_WISEMAN = B.ID_WISEMAN) GROUP BY M.WISEMAN_NAME
Зверніть увагу, що при використанні будь-яких обчислюваних виразів, таких як агрегатні функції COUNT (), SUM (), MAX () тощо, важливо використовувати певні назви полів уявлення, тобто давати назви всім полям, які повертає запит. Як видно з цього прикладу, ці назви не обов’язково мають збігатися з назвами полів запиту, але їх кількість має збігатися з кількістю полів, які повертає запит. Визначення того, яке поле, повернуте запитом, відповідає якому полю уявлення, здійснюється за порядковим номером - перше поле запиту відображатиметься в першому полі уявлення, друге - у другому тощо.
А якщо ми хочемо дізнатися, який із мудреців написав найбільше книг? Спробуємо додати вираз для сортування - ORDER BY до запиту, що лежить в основі уявлення. Однак ця спроба буде невдалою: використання сортування ORDER BY в уявленнях не дозволене, і при спробі створити уявлення із запитом, що містить ORDER BY, виникне помилка. Якщо ми хочемо відсортувати результати, повернуті уявленням, нам доведеться зробити це від імені клієнта:
SELECT * FROM WiseBookCount ORDER BY HOW_WISEBOOKS
Виконання цього SQL-запиту призведе до бажаного результату. Окрім обмеження на використання виразу ORDER BY в уявленнях, ми також не можемо використовувати набір даних, отриманий у результаті виконання збережених процедур, як джерело даних (див. розділ «Збережені процедури» нижче).
Можливо, варто навести ще один приклад, що ілюструє застосування уявлень. Припустімо, що нам потрібно вивести список мудреців, чиї імена починаються з літери «К». У цьому випадку ми використаємо уявлення з умовами:
CREATE VIEW WiseMen2 (WISEMAN) AS SELECT M.WISEMAN_NAME FROM WISEMEN M WHERE M.WISEMAN_NAME LIKE ‘K%’
Таким чином, легко створювати уявлення, які відіграють роль постійно оновлюваних постачальників даних, вибираючи їх із бази даних відповідно до певних умов.
Змінювані уявлення
Вище ми згадували, що існує можливість створювати змінювані уявлення даних. Це дійсно так - існує можливість не лише читати дані з уявлення, але й змінювати їх!
Існує два способи зробити уявлення змінюваним. Перший спосіб застосовується, коли уявлення створене на основі єдиної таблиці (або іншого змінюваного уявлення), і всі стовпці даної таблиці мають допускати наявність NULL. Таким чином, запит, на якому базується уявлення, не може містити підзапитів, агрегатних функцій, UDF, збережених процедур, операторів DISTINCT і HAVING. Якщо всі ці умови виконані, уявлення автоматично стає змінюваним, тобто ми можемо виконувати для нього запити DELETE, INSERT і UPDATE, які змінюватимуть дані в таблиці-джерелі.
Список умов досить значний і суттєво обмежує застосування таких змінюваних уявлень, тому вони використовуються досить рідко.
Для того щоб створити модифіковане представлення, яке порушує будь-яку з перелічених вище умов, застосовується механізм тригерів. Детальніше про тригери дивіться у розділі «Тригери» (частина 1). Зараз ми розглянемо лише загальні принципи організації зміни даних у VIEW.
Для реалізації оновлюваного представлення за допомогою тригерів необхідно виконати наступне. Створити 3 тригери для даного представлення на події: BEFORE DELETE, BEFORE UPDATE та BEFORE INSERT. Описати в цих тригерах, що потрібно робити з даними при видаленні, оновленні та вставці.
Потім слід використовувати дане представлення в запитах на модифікацію - DELETE, INSERT або UPDATE. Коли InterBase отримає цей запит, він перевірить, чи існують відповідні тригери для даного представлення, тобто BEFORE DELETE/INSERT/UPDATE. Якщо тригер для виконуваної дії існує, InterBase викличе його для модифікації реальних даних у таблицях, що лежать в основі представлення (хоча це можуть бути й інші дані - текстових обмежень для цих тригерів немає), а потім повторно прочитає рядок (або рядки), над якими виконувалася модифікація.
Таким чином, існує можливість реалізувати складні ланцюжки оновлення даних у представленнях.
Опція WITH CHECK OPTION згадувалася в описі синтаксису створення представлення. Якщо ця опція встановлена при створенні модифікованого представлення, кожен рядок даних, вставлений або змінений у цьому представленні, буде перевірятися на умову потрапляння до представлення. Це можна пояснити так: якщо новий запис, вставлений користувачем або отриманий у результаті оновлення існуючого запису, не задовольняє умовам запиту, який є постачальником даних для VIEW, вставка цього запису буде скасована, і виникне помилка.
Висновок
Незважаючи на уявну простоту створення та використання представлень, вони надають великі можливості для покращення організації даних у базі даних і дозволяють створювати ієрархію організації даних.
Деякі розробники додатків для баз даних дуже часто використовують представлення у своїй роботі, інші уникають їх застосування, мотивуючи це складністю модифікації представлень і прагненням зберегти схему бази даних якомога простішою та ефективнішою. Вирішувати, як застосовувати представлення у своїй роботі, - вам. Найважливіше - пам’ятати про існування такого потужного інструменту, як представлення, і знати, як ним користуватися.