Робота з реляційними базами даних на прикладі PostgreSQL

1. Вступ

PostgreSQL — це об'єктно-реляційна система управління базами даних (ORDBMS) заснована на POSTGRES версії 4.2, яка була розроблена в Науковому Комп’ютерному Департаменті Берклі Каліфорнійського Університету. Проект POSTGRES, під керівництвом професора Майкла Стоунбрейкера (Michael Stonebraker), був підтриманий Агентством розширення оборонної Дослідних Проектів (Defense Advanced Research Projects Agency (DARPA)), Офісом Армійських Досліджень (Army Research Office (ARO)), Національним Науковим Фондом (National Science Foundation (NSF)), а також ESL, Inc. PostgreSQL є прямим нащадком з відкритим вихідним кодом від оригінального коду, зробленого в Берклі. СУБД надає підтримку SQL92/SQL99 та інші сучасні можливості. POSTGRES є піонером у багатьох об'єктно-реляційних аспектах, що з’явилися тепер в деяких комерційних СУБД. Традиційні реляційні СУБД (RDBMS) підтримують модель даних, яка становить колекцію пойменованих кортежів, що містять атрибути заданого типу. У сучасних комерційних системах, до можливих типів відносяться числа з плаваючою точкою, цілі числа, символьні рядки, грошові типи та дати. Це зазвичай призводить до того, що дана модель є неадекватною для майбутніх програм обробки даних. Реляційна модель успішно замінює попередні моделі почасти в силу «спартанської простоти». Однак, така простота робить реалізацію деяких додатків дуже важкою. PostgreSQL пропонує суттєве збільшення потужності СУБД, через впровадження наступних додаткових аспектів, які дозволяють користувачам легко розширювати систему:

· успадкування

· типи даних

· функції

PostgreSQL належить до категорії СУБД, відомих як об'єктно-реляційні (object-relation). Зауважимо, що тут є відмінність від тих об'єктно-орієнтованих (object-oriented) СУБД, які в основному підтримують традиційні мови реляційних СУБД. Однак, PostgreSQL має деякі об'єктно-орієнтовані можливості, це важливо в світі реляційних СУБД.

2. Коротка історія PostgreSQL

Об'єктно-реляційна СУБД тепер відома як PostgreSQL (і раніше звана Postgres95) веде своє походження від пакета POSTGRES, який був написаний в департаменті Берклі, Каліфорнійського Університету. Більш ніж десятирічна розробка PostgreSQL зробила цей продукт однією з найбільш потужних СУБД з відкритим вихідним кодом у світі, пропонуючи багатоверсійність управління паралельним доступом, підтримуючи практично всі конструкції SQL (включаючи підзапити, транзакції і визначені користувачем типи і функції) і маючи широкий вибір мов, з допомогою яких можна працювати з СУБД (включаючи C, C + +, Java, Perl, Tcl і Python).

2.1 Проект POSTGRES департаменту Берклі

Реалізація реляційної СУБД POSTGRES почалася в 1986. Початкові концепції для цієї системи були представлені в «The design of POSTGRES», а визначення початкової моделі даних було здійснено в «The POSTGRES data model». Пристрій системи управління на той момент, було описано в «The design of the POSTGRES rules system». Обгрунтування архітектури і менеджери зберігання були детально описані в «The design of the POSTGRES storage system» .

Потім вийшло кілька версій Postgres. Перша «demoware» система запрацювала в 1987 і була продемонстрована в 1988 на Конференції ACM-SIGMOD. Версія 1, описана в The implementation ofPOSTGRES була випущена в червні 1989 року і могла працювати з декількома зовнішніми користувачами. У відповідь на критику першого варіанту системи управління, був зроблений наступний варіант (варіант «A commentary on the POSTGRES rules system») був перероблений як (On Rules, Procedures, Caching and Views in Database Systems) і версія 2, випущена в червні 1990 року була заснована на новій системі управління. Версія 3 випущена в 1991, включала в себе підтримку кількох менеджерів зберігання, покращений обробник запитів і знову переписану систему управління. Більшість наступних версій до появи Postgres95 (див. нижче) були сфокусовані на питаннях переносимості та стабільності. POSTGRES був використаний для реалізації багатьох різних досліджень і написання програм. Сюди увійшли: система аналізу фінансових даних, пакет моніторингу продуктивності струменевих установок, база даних переміщень астероїдів, база даних медичної інформації та кілька географічних інформаційних систем. POSTGRES також використовувався як засіб навчання в декількох університетах. Нарешті компанія Illustra Information Technologies (пізніше влилися в компанію Informix, якої тепер володіє IBM .) взяла код цієї СУБД і комерціалізувала его. POSTGRES став пріоритетним менеджером даних для проекту наукових обчислень Sequoia 2000 після 1992 року.

Розмір спільноти користувачів цього продукту подвоївся в 1993 році. Стало дуже очевидно, що обслуговування прототипу коду і його підтримка займають набагато більше часу, ніж самі дослідження в області баз даних. Намагаючись знизити навантаження, пов’язане з підтримкою, проект БеркліPOSTGRES офіційно припинив своє існування з виходом версії 4.2.

2.2 Postgres95

У 1994, Ендрю Ю (Andrew Yu) і Джоллі Чен (Jolly Chen) додали в POSTGRES інтерпретатор мови SQL. Потім Postgres95 був викладений в Інтернет, щоб знайти свій власний шлях у світі продуктів з відкритим вихідним кодом, як нащадок, заснований на оригінальному коді Берклі POSTGRES. Postgres95 був повністю приведений до стандарту ANSI C і скоротив свій розмір на 25%. Були внесено багато внутрішні зміни, які збільшили продуктивність і налагоджуваність коду. Postgres95 версій 1.0.x був швидше на 30−50% згідно Wisconsin Benchmark у порівнянні з POSTGRES, Version 4.2. За винятком виправлення помилок, були зроблені наступні серйозні розширення: Мова запитів PostQUEL була замінена на SQL (реалізований в цьому сервері). Підзапити не підтримувалися аж до виходу PostgreSQL (див. нижче), але в Postgres95 їх можна було зімітувати за допомогою функцій SQL, що визначаються користувачем. Агрегати були переписані. Також в запити була додана підтримка GROUP BY. Інтерфейс libpq залишився доступним для програм на C.

· В додаток до програми monitor, була надана нова програма (psql), яка використовувала бібліотеку GNU Readline і була призначена для інтерактивних SQL запитів.

· Створена нова front-end бібліотека, libpgtcl, що підтримує клієнтів, заснованих на Tcl. Проста оболонка pgtclsh, що надає нові команди Tcl для забезпечення взаімодействіяTcl програм і Postgres95.

· Була ретельно переглянута робота з великими об'єктами. Інверсійні великі об'єкти представляли собою тільки механізм для зберігання великих об'єктів. (Інверсійна файлова система була видалена).

· Разом з вихідним кодом став поставлятися короткий підручник з особливостей роботи з SQL вPostgres95.

· Для побудови проекту став використовуватися GNU make (замість BSD make). Також, Postgres95 був скомпільований зі стандартною версією GCC (вирівнювання даних типу double було виправлено).

2.3. PostgreSQL

У 1996 році було вирішено, що ім'я «Postgres95» не відповідає сьогоденню. Ми вибрали нове ім'я PostgreSQL щоб підкреслити відмінність від оригінального POSTGRES і вихід безлічі версій з підтримкою SQL. У той же час, ми встановили нумерацію версій починаючи з 6.0, повернувшись назад до нумерації, яку почали в проекті Берклі POSTGRES.

При розробці Postgres95 акцент ставився на виявлення і розуміння існуючих проблем в коді продукту. В PostgreSQL акцент змістився на розширення можливостей та сумісності при продовженні роботи в усіх інших областях. Головні зміни в PostgreSQL включають:

· Блокування на рівні таблиць була замінена на Багатоверсійність управління паралельним доступом, що дозволяє клієнтам виробляють читання, продовжувати читання даних під час роботи клієнтів виробляють запис, а також дозволяє виробляти гаряче резервне копіювання програмою pg_dump в той час, як база залишається доступною для запитів.

· Були реалізовані такі важливі можливості, як підзапити, умовчання, примуси і тригери.

· Були додані можливості для сумісності зі стандартом SQL92, включаючи первинні ключі, ідентифікатори запитів, literal string type coercion, створення типів, а також двійковий та шістнадцятковий введення цілих чисел.

· Були поліпшені вбудовані типи даних, включаючи нові широкодіапазонним типи дати / часу і додаткові геометричні типи даних.

· Швидкість роботи backend коду була збільшена приблизно на 20−40%, а час запуску backend’а було скорочено на 80% в порівнянні з версією 6.0.

3. Основні концепції роботи з PostgreSQL

3.1 Створення таблиць

PostgreSQL — це система управління реляційними базами даних (СКБД). Це означає, що це система для управління даними, які зберігаються у вигляді відносин. (В підручниках з СУБД існують і інші терміни, наприклад, кортежі - прим. Пер.). Ставлення — це математичний термін для таблиці. Поняття зберігання даних в таблицях є сьогодні таким банальним, що воно може здатися самоочевидним, однак є кілька інших способів організації баз даних. Файли і каталоги в Unix-подібних операційних системах є прикладом ієрархічної бази даних. Одне з найбільш сучасних напрямків розробки СУБД — це об'єктно-орієнтовані бази даних.

Кожна таблиця є пойменованої колекцією рядків (rows) (в російській літературі по SQL переважно говорять «записів» — прим. Пер.). Кожен запис у таблиці має деякий набір пойменованих колонок (columns) (знову-таки в російській літературі переважно говорять «полів» — прим. Пер.) І кожне поле є певним типом даних. Поля в записі розташовані в фіксований порядку, важливо пам’ятати, що SQL ніколи не гарантує упорядкованого проходження записів в таблиці (за винятком випадку, коли вони можуть бути явно розсортовані для видачі користувачеві).

Таблиці групуються в бази даних, а колекція баз даних, керована однією копією сервераPostgreSQL називається кластером баз даних. Команда створення таблиці:

CREATE [ TEMPORARY | TEMP ] TABLE ім'я_таблиці (

ім'я_поля тип [ обмеження_поля […] ])

[ INHERITS (базова_таблиця [, … ]) ] ;

TEMPORARY | TEMP. Ознака тимчасової таблиці. Таблиця, створена з ключовим словом TEMPORARY або TEMP, автоматично знищується наприкінці поточного сеансу Всі конструкції рівня таблиці (наприклад, індекси і обмеження) знищуються наприкінці сеансу разом з таблицею. Якщо ім'я тимчасової таблиці співпадає з ім'ям існуючої таблиці, то всі посилання на таблицю з цим ім'ям впродовж сеансу відноситимуться до тимчасової таблиці. Іноді це викликає проблеми, оскільки тимчасова таблиця побічно заміщає існуючу таблицю в контексті поточного сеансу до моменту її знищення.

таблиця. Ім'я створюваної таблиці.

поле. Ім'я поля в новій таблиці. Імена полів перераховуються в круглих дужках і розділяються комами.

тип. Відразу ж після імені користувача задається його тип - стандартний тип або масив одного із стандартних типів.

обмеження_поля.

базова_таблиця. Ім'я таблиці, від якої нова таблиця успадковує поля. Якщо імена успадкованих полів збігаються з іменами полів, раніше включених в структуру таблиці, PostgreSQL видає повідомлення про помилку і перериває виконання команди.

3.1.1 Типи даних

varchar (80) задає тип даних, який може зберігати символьні рядки довжиною до 80 символів. int — це звичайний цілочисельний тип. real — це тип даних, що зберігає числа з плаваючою точкою одинарної точності. Тип date говорить сам за себе. (Цілком вірно, поле з типом date так і називається дата. Зручно це чи ні - вирішувати вам.)

PostgreSQL підтримує таки корисні типи SQL як int, smallint, real, double precision, char (N), varchar (N), date, time, timestamp і interval, а також і інші загальні типи і багатий набір геометричних типів. PostgreSQL можна налаштувати так, щоб він працював з довільним числом типів даних, визначених користувачем. Отже, імена типів не є синтаксичними ключовими словами, за виключаємо тих випадків, де потрібні підтримка спеціально згідно стандартуSQL.

3.2 Внесення даних у таблицю Для додавання записів в таблицю використовується тригер INSERT:

INSERT INTO имя_таблиці [(список_імен_полів)] VALUES (значення1, значення2, …, значення n);

Для завантаження великої кількості даних з простого текстового файлу, ви також можете використовувати команду COPY. Зазвичай це працює швидше, тому що команда COPY оптимізована для операції, яку вона виконує, але в той же час вона менш гнучка ніж команда INSERT. Ось приклад еЈ використання:

COPY weather FROM '/home/user/weather.txt';

де файл, вказаний як джерело даних повинен бути доступний на машині з backend сервером, а не на клієнтській машині, тому що backend сервер читає цей файл безпосередньо. Ви можете прочитати подробиці про команду COPY в PostgreSQL Reference Manual.

3.3 Редагування таблиць Під час редагування таблиці доводиться виконувати такі роботи:

· зміна даних, тобто редагування вмісту полів;

· маніпуляція записами, їх вилучення і вставка;

· перейменування та знищення таблиці;

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

Змінити дані таблиці дозволяє команда:

UPDATE ім'я_таблиці SET ім'я_поля1=значення1, ім'я_поля2 = значення2, … WHERE вираз Приклад. Скласти команду для заміни в таблиці gazpr поля naz_g на Україна тих записів, де kod_g = 2:

UPDATE gazpr SET naz_g = 'Україна' WHERE kod_g = 2;

Якщо редагуються всі записи, то інструкція WHERE може бути опущена. Для нашого прикладу така команда матиме такий вигляд:

UPDATE gazpr SET naz_g = 'Україна';

Знищення всіх або вибраних за умовою записів таблиці забезпечує команда:

DЕLETE FROM ім'я_таблиці WHERE вираз Приклад. Знищити ті записи таблиці gazpr, яких naz_g = Україна.

DELETE FROM gazpr WHERE gazpr. naz_g = 'Україна';

Вставку записів забезпечує вже розглянена команда INSERT.

Модифікація таблиці командою ALTER TABLE. У більшості сучасних РСУБД передбачена можливість модифікації таблиць командою ALTER TABLE. Її реалізація в PostgreSQL забезпечує такі види модифікації:

· створення полів;

· призначення і скасування значень за умовчанням;

· перейменування таблиці;

· перейменування полів;

· додавання обмежень;

· зміна власника.

Додавання полів. Для створення нового поля в команду ALTER TABLE включається секція ADD COLUMN. Синтаксис команди ALTER TABLE з секцією ADD COLUMN:

ALTER TABLE ім'я_таблиці ADD [COLUMN] ім'я_поля тип_поля

· таблиця — ім'я таблиці, в якій створюється нове поле;

· ім'я_поля — ім'я нового поля;

· тип_поля — тип нового поля.

Ключове слово COLUMN не є обов’язковим і включається в команду лише для наочності. Приклад включення в таблицю books нового поля publication для зберігання дати публікації:

ALTER TABLE books ADD publication date;

Призначення і скасування значень за замовчуванням

ALTER TABLE таблиця ALTER [COLUMN] імя_поля

DROP DEFAULT

Як і в попередньому розділі, ключове слово COLUMN є необов’язковим і включається в команду лише для наочності. Нижче наведено приклади встановлення та скасування послідовності значень за замовчуванням для поля id таблиці books.

ALTER TABLE books ALTER COLUMN id SET DEFAULT nextval ('books.id');

ALTER TABLE books ALTER id DROP DEFAULT;

Перейменування таблиці забезпечує команда:

ALTER TABLE таблиця RENAME ТО нове_імя Таблицю можна перейменовувати скільки завгодно разів, це ніяк не відбивається на стані зберігаються в ній даних. Зрозуміло, що в деяких ситуаціях перейменування небажані, зокрема, якщо таблиця використовується зовнішнім додатком. Приклад зміни імені literature на books:

ALTER TABLE literature RENAME TO books;

Перейменування полів. PostgreSQL дозволяє змінювати імена полів без зміни даних, що зберігаються в таблиці. Втім, перейменування полів — справа ризикована, оскільки програми можуть містити посилання на імена полів. Якщо програма звертається до поля по імені, то перейменування може порушити її працездатність.

ALTER TABLE таблиця RENAME [COLUMN] імя_поля ТО нове_імя_поля Як і в інших командах ALTER TABLE, ключове слово COLUMN є необов’язковим. По двох ідентифікаторах, розділеним ключовим словом ТО, PostgreSQL може визначити, що команда перейменування відноситься до одного поля, а не до таблиці. Приклад:

ALTER TABLE daily RENAME COLUMN in_stock TO is_in_stock:

Додавання обмежень. Після створення таблиці зберігаються деякі можливості додавання обмеження. У PostgreSQL команда ALTER TABLE з секцією ADD CONSTRAINT дозволяє визначати для полів існуючих таблиць тільки обмеження зовнішнього ключа. Команда створення нових обмежень має такий синтаксис:

ALTER TABLE таблиця ADD CONSTRAINT ім'я обмеження визначення Синтаксис визначення залежить від типу обмеження. Нижче показано створення нового обмеження зовнішнього ключа для таблиці editions (пов'язаної з полем id таблиці books) і обмеження перевірки для поля type.

ALTER TABLE editions

ADD CONSTRAINT foreign_book FOREIGN KEY (bookjd) REFERENCES books (id);

ALTER TABLE editions

ADD CONSTRAINT hard_or_paper_back CHECK (type = 'p1 OR type =' h ');

Установка обмеження зовнішнього ключа призводить до того, що будь-яке значення book_id у таблиці editions також має існувати і в таблиці books. Крім того, внаслідок встановленого обмеження перевірки полі type в таблиці editions може містити тільки значення р або b.

Зауважимо, що при створенні унікального індекса командою CREATE INDEX (буде розглянена у відповідному розділі) також неявно встановлюється і обмеження унікальності.

В існуючу таблицю можна додати тільки обмеження таблиць. Це робить така команда:

ALTER TABLE ім'я_таблиці

ADD [ CONSTRAINT ім'я_та_визначення_обмеження ]

Команда ALTER додає обмеження таблиці, але не поля. Нижче встановлюється нове обмеження FOREIGN KEY для поля kod_g таблиці gazpr, яке зв’язується з полем kod_g таблиці oblik. Обмеження FOREIGN KEY гарантує, що внаслідок вставки або оновлення даних у поле kod_g таблиці oblik не з’являться значення, відсутні в полі kod_g таблиці gazpr, тому у прикладі система видала відповідне повідомлення про помилку, тобто про те, що в таблиці gazpr не існує газопроводу з кодом 7.

ALTER TABLE gazpr ADD CONSTRAINT bbb PRIMARY KEY (kod_g);

ALTER TABLE oblik ADD CONSTRAINT aaa

FOREIGN KEY (kod_g) REFERENCES gazpr (kod_g);

INSERT INTO OBLIK VALUES (4,4,'21.12.2000', 4);

INSERT INTO OBLIK VALUES (7,4,'21.12.2000', 4);

ERROR: insert or update on table «oblik» violates foreign key constraint «aaa»

DETAIL: Key (kod_g)=(7) is not present in table «gazpr» .

SQL state: 23 503

Зауважимо, що обмеження унікальності також неявно встановлюється при створенні унікального індекса командою CREATE INDEX.

Видалення обмежень. Деякі версії PostgreSQL не підтримують пряме видалення обмежень із таблиці. Добитися потрібного результату можна лише одним способом: створити копію таблиці, що практично повністю повторює оригінал, який не містить тих обмежень, які видаляються. Дані копіюються з початкової таблиці в нову, після чого таблиці перейменовуються командою ALTER TABLE і копія замінює оригінал.

Зміна власника. За замовчуванням творець таблиці автоматично стає її власником. Власник володіє всіма правами, пов’язаними з таблицею, в тому числі правами передання і відкликання прав командами GRANT і REVOKE (будуть розглянені пізніше у відповідному розділі). Зміна власника проводиться командою ALTER TABLE з секцією OWNER. Команда має такий синтаксис:

ALTER TABLE ім'я_таблиці OWNER ТО новий_власник Змінювати власника таблиці може або поточний власник, або суперкористувач. Приклад зміни власника таблиці employee, де новим власником стає користувач corwin:

ALTER TABLE employees OWNER TO corwin;

Реструктуризація таблиць командами CREATE TABLE і INSERT INTO. Якщо таблиця, створена командою CREATE TABLE AS, не влаштовує (наприклад, якщо в таблиці необхідно встановити обмеження полів), то одну команду CREATE TABLE AS можна замінити двома командами SQL. Спочатку команда CREATE TABLE створює нову таблицю, а потім команда INSERT INTO з запрограмованим SELECT заповнює її даними. Видалення таблиць забезпечує команда:

DROP TABLE таблиця Використання команди DROP TABLE вимагає обережності, оскільки видалення таблиці спричиняє знищення всіх збережених у ній даних. При знищенні таблиці з неявно створеним індексом знищуються і всі пов’язані з нею індекси.

Копіювання даних із зовнішніх файлів командою COPY. У PostgreSQL підтримується і така корисна можливість, як пряме імпортування даних в таблицю з зовнішніх файлів командою COPY. Файл, з якого беруться дані, зберігається або в стандартному текстовому форматі ASCII з обмеженням полів спеціальним символом-роздільником, або в двійковому форматі таблиць PostgreSQL. У ASCII-файлах як роздільник зазвичай використовується символ табуляції або кома. При імпортуванні данних з ASCII-файлу кожен рядок файлу інтерпретується як окремий запис даних, а кожен компонент рядка — як значення відповідного поля запису.

Команда COPY FROM працює значно швидше за звичайну команду INSERT, оскільки дані передаються прямо в приймальну таблицю за одну транзакцію. З іншого боку, до формату вихідного файлу пред’являються надзвичайно жорсткі вимоги, тому помилка всього в одному рядку спричинить до збою всієї команди COPY. Синтаксис команди COPY FROM:

COPY [BINARY] таблиця [WITH 0IDS]

FROM 'ім'я_файлу'

[[USING] DELIMITERS 'роздільник']

[WITH NULL AS `рядок_null']

Параметри команди:

— BINARY. Ознака імпортування вхідних даних із двійкового файлу, раніше створеного командою COPY ТО;

— таблиця. Ім'я таблиці, в яку імпортуються дані;

— WITH 0IDS. З першого рядка файлу завантажуються значення всіх ідентифікаторів OID імпортованої таблиці;

— FROM 'ім'я_файлу'. Джерело, з якого PostgreSQL отримує вхідні дані, це може бути файл із заданим ім'ям або стандартний ввід (stdin);

— [USING] DELIMITERS 'роздільник'. Символ, який використовується в якості розділювача при розборі вхідних даних. Не використовується для файлів, виведених у двійковому форматі PostgreSQL;

— WITH NULL AS 'рядок null'. Заданий рядок повинен інтерпретуватися як значення NULL. Не використовується для файлів, виведених у двійковому форматі PostgreSQL.

При підготовці до імпортування файлу слід простежити за тим, щоб він був доступний для читання процесом postmaster (тобто користувачем, який запустив PostgreSQL). Крім того, дозволені тільки абсолютні імена файлів; при спробі передати відносне ім'я відбувається помилка.

При роботі з вхідними файлами в форматі ASCII в секції DELIMITERS передається символ, використаний як роздільник значень полів у рядках файлу. Якщо роздільник не вказаний, PostgreSQL вважає, що значення розділюються символом табуляції. Необов’язкова секція WITH NULL визначає формат, в якому передається значення NULL. Якщо секція відсутня, то PostgreSQL інтерпретує послідовність N як NULL (наприклад, порожні поля вихідного файлу за замовчуванням інтерпретуються як порожні рядкові константи, а не як NULL).

Якщо дані вводяться вручну або передаються терміналу іншою програмою, в якості джерела в секції FROM можна вказати стандартний файл (stdin). При отриманні даних з стандартного вводу вхідний потік повинен завершуватися послідовністю. (зворотна коса риска плюс точка), зразу за якою слідує символ нового рядка.

Нижче приведено вміст файлу, виведеного PostgreSQL в форматі ASCII. Поля (3 шт.) розділяються комами, а для відображення значення NULL використовується рядок nul1. У файлі збережені дані з таблиці subjects.

1,Наука, Productivity Ave

2,Релігія, null

3,Класика, Academic Rd

4,Комп'ютери, Productivity Ave

5,Cooking, Creativity St

Двійковий формат. Команда COPY також дозволяє виконувати операції введення і виведення з даними в двійковому форматі. Якщо команда COPY FROM містить ключове слово BINARY, то вхідний файл повинен бути створений командою COPY ТО в двійковому форматі PostgreSQL. Двійкові файли завантажуються швидше за ASCII-файли, але на відміну від останніх їх не можна читати і редагувати в простих текстових редакторах.

Системні поля. У PostgreSQL всі таблиці містять системні поля, які залишаються невидимими для користувача і не виводяться при вибірці (якщо службова інформації не запитується спеціально). У системних полях зберігаються метадані, які описують вміст записів, вони такі:

Ідентифікатори записів дозволяють розрізнити два записи з однаковими значеннями полів. Для цього в PostgreSQL передбачений ідентифікатор OID, унікальний в межах таблиці. Приклад:

SELECT *, oid FROM my_list;

Знищення таблиці виконує команда:

DROP TABLE tablename;

3.4 Обмеження

PostgreSQL має декілька варіантів обмеження даних (constraint), які впливають на операції вставки і оновлення. Розглянемо один із них, який полягає в установці обмежень для таблиць і полів. Обмеженням є особливий атрибут таблиці, який встановлює критерії допустимості для вмісту її полів. Дотримання цих правил допомагає запобігти заповненню бази помилковими або невідповідними даними. Обмеження задаються в секції CONSTRAINT при створенні таблиці командою CREATE TABLE. Обмеження полів завжди стосуються лише одного поля, тоді як обмеження таблиць можуть встановлюватися як для одного, так і для декількох полів. У команді CREATE TABLE обмеження полів задаються відразу ж після визначення поля, тоді як обмеження таблиці встановлюється в спеціальному блоці, виділеному комами від усіх визначень полів.

Опис обмеження поля виглядає так:

[ CONSTRAINT обмеження]

DEFAULT значення CHECK (умова)

Визначення обмеження слідує в команді CREATE TABLE відразу ж за типом обмежуваного поля і передує комі, яка відокремлює його від наступного поля. Обмеження можуть встановлюватися для будь-якої кількості полів, а ключове слово CONSTRAINT і ідентифікатор обмеження не обов’язкові.

Існує шість типів обмежень полів, які задаються за допомогою спеціальних ключових слів. Деякі з них побічно встановлюються при створені обмежень іншого типу. Типи обмежень полів перераховані нижче, вони такі:

NOT NULL. Поле не може містити псевдозначення NULL. Обмеження NOT NULL еквівалентно обмеженню CHECK (поле NOT NULL);

UNIQUE. Поле не може містити значення, які повторюються. Слід враховувати, що обмеження UNIQUE допускає багатократне входження псевдозначень NULL, оскільки формально NULL не збігається ні з яким іншим значенням;

PRIMARY KEY. Автоматично встановлює обмеження UNIQUE і NOT NULL, а для заданого поля створюється індекс. У таблиці може встановлюватися тільки одне обмеження первинного ключа;

DEFAULT значення. Пропущені значення поля замінюються заданою величиною. Тип значення за замовчуванням повинно відповідати типу поля;

CHECK умова. Команда INSERT або UPDATE завершується успішно лише при виконанні заданої умови (виразу, що повертає логічний результат). При установці обмеження поля в секції CHECK може використовуватися тільки поле, для якого встановлюється обмеження;

REFERENCES. Це обмеження складається з таких секцій:

REFERENCES таблиця [(поле)]. Вхідні значення обмежуваного поля порівнюються із значеннями іншого поля в заданій таблиці. Якщо збіги відсутні, то команда INSERT або UPDATE завершується невдачею. Якщо параметр поле не вказаний, то перевірка виконується за первинним ключем. Обмеження REFERENCES подібне до обмеження таблиці FOREIGN KEY, яке описане в наступному пункті цього підрозділу. Дійсно, між цими обмеженнями є багато спільного. Приклад таблиці, створеної з обмеженням FOREIGN KEY, приведений у лістингу 7.8;

MATCH FULL | MATCH PARTIAL. Секція MATCH указує, чи дозволяється змішувати значення NULL і звичайні значення при вставці в таблицю, у якої зовнішній ключ посилається на декілька полів. Таким чином, на практиці секція MATCH приносить користь лише в обмеженнях таблиць, хоча формально вона може використовуватися і при обмеженні полів. Конструкція MATCH FULL забороняє вставку даних, у яких частина полів зовнішнього ключа містить псевдозначення NULL (крім випадку, коли NULL міститься у всіх полях). У PostgreSQL 7.1.x конструкція MATCH PARTIAL не підтримується. Якщо секція MATCH відсутня, то вважається, що поля з псевдозначепиями NULL задовільняють обмеження. Також буде доречно нагадати, що обмеження полів стосується лише одного поля, тому секція MATCH використовується лише в обмеженнях таблиць;

ON DELETE операція. При виконанні команди DELETE для заданої таблиці з обмежуваним полем виконується одна з таких операцій:

N0 ACTION (якщо видалення спричиняє порушення цілісності посилань, то відбувається помилка; використовується за замовчуванням, якщо операція не вказана),

RESTRICT (аналогічно N0 ACTION),

CASCADE (видалення всіх записів, що містять посилання на запис, який видаляється),

SET NULL (поля, що містять посилання на запис, який видаляється, замінюються псевдозначеннями NULL),

SET DEFAULT (полям, що містять посилання на запис, який видаляється, привласнюється значення за замовчуванням);

ON UPDATE операція. При виконанні команди UPDATE для заданої таблиці виконується одна з вищеописаних операцій. За замовчуванням використовується значення N0 ACTION. Якщо вибрана операція CASCADE, всі записи, які містять посилання на оновлюваний запис, оновлюються новим значенням (замість видалення, як у випадку з ON DELETE CASCADE);

DEFERRABLE | NOT DEFERRABLE. Значення DEFERRABLE дозволяє відкласти виконання обмеження до кінця транзакції (замість негайного виконання після завершення команди). Значення NOT DEFERRABLE означає, що обмеження завжди перевіряється відразу ж після завершення чергової команди. В цьому випадку користувач не може відкласти перевірку обмеження до кінця транзакції. За замовчуванням вибирається саме цей варіант;

INITIALLY DEFERRED | INITIALLY IMMEDIATE. Секція INITIALLY задається лише для обмежень, визначених з ключовим словом DEFERRED. Значення INITIALLY DEFERRED відкладає перевірку обмеження до кінця транзакції, а при установці значення INITIALLY IMMEDIATE перевірка проводиться після кожної команди. За відсутності секції INITIALLY за замовчуванням використовується значення INITIALLY IMMEDIATE.

Обмеження таблиць подібні до обмежень полів, але на відміну від них, можуть стосуватися відразу декількох полів таблиці.

Зауважимо, що деякі версії PostgreSQL забезпечують лише додавання обмежень CHECK і FOREIGN KEY.

Вибір режиму перевірки обмежень забезпечує команда:

SET CONSTRAINTS ALL DEFERRED

Параметри:

ALL означає, що вказаний режим повинен стосуватися всіх обмежень;

режим - ім'я обмеження, для якого встановлюється режим перевірки;

DEFERRED - перевірка обмежень (або конкретного обмеження) вікладається до

момента фіксації транзакції, тобто до виконання команди COMMIT;

IMMEDIATE - всі або конкретне обмеження перевіряються наприкінці кожної команди.

Команда SET CONSTRAINTS задає режим перевірки для всіх обмежень або одного обмеження в поточному транзакційному блоці. Існує два режими перевірки: негайна (IMMEDIATE) і відкладена (DEFERRED) перевірка обмежень. У режимі IMMEDIATE всі обмеження перевіряються після виконання кожної команди транзакції, а в режимі DEFERRED обмеження перевіряються лише після виконання команди COMMIT.

Зауважимо, що деякі версії PostgreSQL забезпечують зміну режиму перевірки тільки для обмеження FOREIGN KEY. Команда SET CONSTRAINTS не разповсюджується на обмеження CHECK і UNIQUE

Нижченаведена команда вибирає режим негайної перевірки всіх обмежень.

SET CONSTRAINTS ALL IMMEDIATE;

3.5 Послідовності

PostgreSQL є об'єктно-реляційною СУБД, що дозволило включити в неї ряд нестандартних розширень SQL. Частина цих розширень пов’язана з автоматизацією часто вживаних операцій з базами даних, це, зокрема, послідовності і тригери.

Послідовність (sequence) являє собою послідовність чисел. Значення послідовності мають тип integer, тому її значення повинні лежати в межах від 2 147 483 647 до -2 147 483 647. Як правило, її використовують у обмеженні DEFAULT для надання унікальних значень полям таблиць. Ці значення автоматично змінюються шляхом виклику функції nextval (). У інших СУБД, наприклад MS Access, послідовності називають лічильниками.

Послідовність створюється командою:

CREATE SEQUENCE послідовність

[ INCREMENT приріст] [ MINVALUE мінімум ] [ MAXVALUE максимум ] [ START початок ] [ CACHE кеш ] [ CYCLE ]

Тут:

— послідовність — ім'я послідовності, це єдиний обов’язковий параметр;

— INCREMENT приріст — нарощення поточного значення послідовності, це число може бути від'ємним або додатнім. За замовчуванням приріст дорівнює 1.

— MINVALUE мінімумум — мінімально допустиме значення. Спроба зменшити поточне значення нижче за заданий мінімум спричинить помилку або циклічний перехід до максимального значення (якщо послідовність створювалася з ключовим словом CYCLE). За замовчуванням мінімальне значення дорівнює 1;

— MAXVALUE максимум — максимально допустиме значення послідовності. Спроба перевищити заданий максимум спричинить помилку або циклічний перехід до мінімального значення (при CYCLE). За замовчуванням максимальне значення дорівнює 2 147 483 647;

— START початок — початкове значення послідовності, це будь-яке ціле число в інтервалі між мінімальним і максимальним значеннями. За замовчуванням послідовність починається з нижнього порога при зростанні послідовності або з верхнього — при убуванні;

— CACHE кеш — забезпечує можливість попереднього обчислення і зберігання значень послідовності в оперативній пам’яті. Кешування прискорює доступ до тих послідовностей, які часто використовуються;

— CYCLE — повторне, циклічне використання послідовності. Досягши нижнього або верхнього порога, послідовність продовжує генерувати нові значення, тоді вона переходить до мінімального значення при зростанні послідовності або до максимального — при убуванні. Зауважимо, що, оскільки в цих випадках значення послідовності повторюються, то вони не будуть унікальними.

У нижченаведеному прикладі створюється послідовність з ім'ям ship, яка починається із значення 0 і збільшується на 1 до тих пір, поки не досягне максимального значення. Ключове слово CYCLE не вказане, тому ця послідовність набуває лише унікальних значень.

CREATE SEQUENCE ship MINVALUE 0;

До послідовності також можна звернутися командою SELECT, як до таблиці, хоча така можливість використовується відносно рідко, наприклад, під час випробування або перевірки послідовності. При складанні запиту до послідовності в списку вибірки вказуються її атрибути, перелічені в таблиці. 5.1.

Таблиця 5.1. Атрибути послідовності

Нижче показано запит до послідовності ship. Він повертає атрибути last_value (поточне значення, оскільки послідовність щойно створена, то воно мінімально задане і дорівнює 0) та increment_by (приріст, за замовчуванням дорівнює 1).

SELECT last_value, increment_by FROM ship;

Як правило, всі операції з послідовностями виконуються за допомогою таких трьох спеціальних функцій PostgreSQL:

— nextval (`послідовність') — нарощує поточне і повертає нове значення;

— currval (`послідовність') — повертає поточне значення;

— setval (`послідовність', n) — змінює поточне значення на число n.

Нижче в прикладі виводиться пара чергових значень послідовності ship.

Послідовності найчастіше використовуються для задавання значень за замовчуванням у полях таблиць.

Видалення послідовності забезпечує команда SQL DROP SEQUENCE, вона видаляє одну або декілька послідовностей одночасно. Ця команда має такий вигляд:

DROP SEQUENCE перелік_послідовностей Приклад видалення послідовності ship: DROP SEQUENCE ship;

Перш ніж знищувати послідовність, слід переконатися в тому, що вона не використовується іншою таблицею, функцією або іншим об'єктом бази даних. Якщо забути про цю перевірку, то можна порушити роботу інших операцій, залежних від даної послідовності. Вивести імена всіх таблиць, в яких використовується задана послідовність можна за допомогою такого запиту:

SELECT p. relname, a. adsrc FROM pg_class p JOIN pg_attrdef a ON (p.relfilenode = a. adrelid) WHERE a. adsrc ~ '" sequence_name?" ';

3.6 Запити Центральнемісце в SQL займає команда SELECT, призначена для побудови запитів та вибірки даних з таблиць. Дані, повернені в результаті запиту, називаються підсумковим набором. Дані підсумкового набору не зберігаються на диску в постійній формі. Підсумковий набір є лише тимчасовим поданням даних, отриманих в результаті запиту.

У найпростішому випадку команда SELECT вибирає з заданої таблиці всі дані. Повна вибірка даних проводиться командою SELECT * FROM імя_таблиці.

Запит являє собою команду, написану мовою SQL, яка вибирає дані з таблиць. Якщо запит виготовляється візуальним способом, то інтерпретатор складає його скрипт у вигляді тексту, написаний теж мовою SQL. Скрипт вигідний тим, що його можна редагувати, він також застосовується на стадії вивчення мови SQL. На відміну від інших мов програмування, запит вказує серверу, які дані потрібно знайти, але не повідомляє, як це зробити. Слід особливо наголосити на тому, що запит — команда, але не вибірка даних.

У підручниках з SQL терміни «команда» і «запит» часто вважаються еквівалентними. Тут термін «запит» стосується тільки команд, які повертають дані, але не до загальних команд SQL, які створюють або модифікують дані.

Дані, повернені запитом, називаються підсумковим набором. Як і таблиця, він складається із записів та полів, тобто виглядає так, як таблиця. Вибірка не дублює таблицю, але містить її задану частину або всю інформацію, вона формується заново при кожному відкритті (запуску) запиту. Підсумковий набір не зберігається на диску, це тимчасові дані. Він може містити поля як із однієї, так з декількох таблиць.

Загальний варіант команди SELECT:

SELECT список імен полів запиту FROM ім'я джерела

INNER JOIN ім'я зв’язаного джерела ON ім'я поля = ім'я зв’язаного поля

WHERE ім'я поля, оператор, значення

GROUP BY список імен полів, у яких відбувається групування

HAVING умова відбору записів

ORDER BY ім'я поля, за даними якого записи запиту будуть посортовані;

Тут службові слова SELECT і FROM — обов’язкові.

WHERE дозволяє обмежити набір записів. Якщо використовуються всі поля джерела, то замість їх переліку у списку імен полів запиту вживається символ зірочка.

Секція GROUP BY дозволяє групувати записи за заданим критерієм (наприклад, підсумувати кількість транспортованого газу для кожного газопроводу).

Як бачимо, запит може містити вирази. До складу виразу можуть входити:

— константи;

— оператори;

— імена полів і таблиць;

— функції;

— фігурні, квадратні і круглі дужки.

Нижче поданий перелік та опис цих складників (нагадаємо, що константи та імена полів і таблиць вже розглядалися в попередніх розділах).

Оператори зазвичайно містять від одного до чотирьох символів. Слід зауважити, що деякі з них існують тільки у версії SQL для PostgreSQL і можуть не бути в інших СУБД. Об'єднання двох рядкових констант у один виконується оператором конкатенації (||), наприклад, так: SELECT 'Ра' || 'кета' AS example;

Крім, власне демонстрації злиття двох рядків, у цьому прикладі показано застосування альтернативного імені example, яке тут дозволяє підвищити наглядність результату.

Строкові оператори служать для порівняння двох рядків (або чисел), вони такі:

Оператори регулярних виразів розширюють можливості строкових операторів, вони такі:

~ - перевіряє (видає true, якщо так) чи в рядку існує збіг для регулярного виразу;

!~ - перевіряє чи в рядку немає збігу для регулярного виразу;

~* - перевіряє чи в рядку існує збіг без урахування регістру символів;

!~* - перевіряє чи в рядку немає збігу без урахування регістру символів.

Регулярний вираз може містити такі метасимволи (спеціальні символи):

^ - з початку рядка;

$ - наприкінці рядка;

. — один символ;

[ ] - будь-який із символів, перелічених у квадратних дужках;

[^] - будь-який символ, крім перелічених у квадратних дужках;

[-] - будь-який символ з інтервалу, заданого в квадратних дужках;

[^-] - будь-який символ, крім символів з інтервалу, заданого в квадратних дужках;

? — один примірник попереднього символа або підвиразу (якщо, звичайно, він є) ;

* - довільна кількість примірників попереднього символа або підвиразу;

+ - один і більше примірників попереднього символа або підвиразу;

| - лівий або правий підвираз;

() — групування підвиразів з явним визначенням пріоритету операцій;

Нижче наведено декілька прикладів застосування регулярних виразів для видачі:

џ всіх значень поля name таблиці authors, які починаються з літер, А або Т:

SELECT name FROM authors WHERE name ~ '^A|^T';

џ всіх значень поля title тих записів таблиці books, де зустрічається слово «Кобзар»:

SELECT title FROM books WHERE title ~*'Кобзар';

џ всіх значень, які починаються (^) на «Кобзар» без врахування регістра (*), а далі через довільне число символів (.) містять слово «малий» або (|) «повний», написане лише малими буквами (перед ними немає зірочки):

SELECT title FROM books WHERE title ~* '^ Кобзар.(малий | повний)';

џ всіх значень, які починаються (^) на «К» без врахування регістра (*), а далі через довільне число символів (.) зустрічається якась буква з переліку ([]) «о» та «м», наступним символом після яких зразу ж стоїть буква «к», або (|) видати всі ті значення, які закінчуються ($) на «ий» або (|) на «не»:

SELECT title FROM books WHERE title ~* '(^К.*[о, м]к)|(ий$|не$)';

Математичні оператори використовуються в числових виразах, вони такі:

Приклад застосування операції множення: видати коди газопроводів та кількість транспортованого газу за весь період обліку, збільшену в 1.6 разів (так начислюється премія персоналу, який обслуговує газопровід) і названу premia, взяті з таблиці oblik:

SELECT kod_g, (1.6 * kilk):numeric (8, 2) AS premia FROM oblik;

Математичні оператори порівняння такі ж, як вищерозглянені строкові.

Ключове слово BETWEEN (також іноді називається оператором) дозволяє перевірити, чи входить значення в деякий інтервал. Наприклад, нищенаведена команда SELECT видає книги, ціна яких знаходиться в інтервалі між 10 та 17:

SELECT naz_kniga FROM kniga WHERE cina BETWEEN 10 AND 17;

Двійкові оператори виконують порозрядні операції з бітовими послідовностями або цілими числами, вони аналогічні до мови С. Приклад зсуву числа 8 (двійкове 1000) на два розряди вправо та перетворення одержаної бітової послідовності в цілочисловий тип функцією bittoint4():

SELECT bittoint4(b'1000' >> 2); Пріоритет операторів SQL (у порядку спадання):

3.7 Об'єднання таблиць (Join)

Ми бачимо, що наші запити використовують в тільки одну таблицю. Але запити можуть одночасно звертатися до декількох таблиць або до в один і той же час до тієї ж таблиці але з іншим способом обробки довільних записів. Запит, який звертається до кількох записів однієї таблиці або до декількох таблиць одночасно, називається join запитом (об'єднанням). Як приклад, скажімо ви хочете подивитися всі записи про погоду разом з тими містами, до яких вони належать. Щоб це зробити, нам потрібно порівняти поле city кожного запису про погоду в таблиці погоди weather з ім'ям поля всіх записів в таблиці міст cities, і вибрати пари записів, де ці значення збігаються.