Тема 9. Клієнт-сервер та мова sql. Елементи мови sql на прикладі transact-sql. Безпека в sql server 2005

План лекції

1. Середовища програмування мовою SQL. Ідентифікатори мови TRANSACT-SQL.

2. Типи даних мови TRANSACT-SQL.

Самостійна робота

3. Функції мовиTRANSACT-SQL.

4. Доступ до метаданих. Огляд безпеки SQL Server 2005.

Зміст лекції

SQL (Structured Query Language - мова структурованих запитів) - мова, вживана для створення, модифікації і управління даними в реляційних базах даних.

SQL в його базовому виді є інформаційно-логічною мовою, а не мовою програмування. В той же час стандарт мови специфікацією SQL/PSM передбачає можливість його процедурних розширень, з урахуванням яких мова вже цілком може розглядатися як мова програмування.

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

Мова SQL стала фактично стандартною мовою доступу до баз даних. Усі СУБД, що претендують на назву "реляційні", реалізують той або інший діалект SQL. У СУБД MS SQL Server 2008 реалізована мова Transact - SQL. Багато нереляційних систем також мають нині засоби доступу до реляційних даних. Так, наприклад, постреляційна (об'єктна) СУБД Caсhe має у своєму складі мову Cache - SQL для доступу до даних. Метою стандартизації в даному випадку є переносимість додатків між різними СУБД.

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

Проте, знання основ мови SQL і досвід використання одного з його діалектів у складі однієї з СУБД дозволяють досить швидко освоювати засоби розробки додатків, наявні в інших СУБД.

Мова SQL оперує термінами, що дещо відрізняються від термінів реляційної моделі. Наприклад, замість поняття "відношення" використовується "таблиця", замість поняття "кортеж" - "рядок", замість поняття "атрибут" - "колонка" або "стовпець".

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

Кожен варіант стандарту мови SQL можна було трактувати як визначення моделі даних відповідно до критеріїв Едгара Кодда. Починаючи із стандарту SQL :89 було визначене, що SQL -ориентированная база даних складається з таблиць, в стовпцях яких можуть міститися дані типів, специфікованих в стандарті. Говорилося, що в таблицях можуть визначатися первинний і можливий ключі, і в цьому випадку СУБД повинна підтримувати унікальність їх значень. Деяким чином була потрібна підтримка посилальної цілісності. Нарешті, "абстрактним" механізмом маніпулювання даними виступали засоби самої мови SQL.

У стандарті SQL :89, визначення хоч би одного можливого ключа в таблиці було необов'язковим, так що в таких таблицях могли бути присутній рядки-дублікати, вони не були безліччю рядків; отже, вони не були аналогами стосунків реляційної моделі даних. У заголовках таблиць був присутній неявний порядок імен стовпців, отже, вони не були аналогами заголовків стосунків реляційної моделі даних. У стовпцях таблиць, разом зі значеннями, що типізуються, могли зберігатися невизначені значення, що не типізувалися, викликають необхідність у використанні тризначної логіки і призводять до іноді парадоксальних ситуацій.

З іншого боку, тільки в стандарті SQL :92 були явно введені конструкції визначення базових таблиць, а тим самим, і можливості визначення первинних і можливих ключів. Тільки у стандарті SQL :1999 був специфікований повний набір типів даних, значення яких можуть бути присутній в стовпцях таблиць SQL -ориентированных баз даних, що параметризувалися, генерованих і визначуваних користувачами. У міру розвитку стандарту розширювалися і уточнювалися засоби підтримки посилальної цілісності і маніпулювання даними і так далі

Трактування стандарту SQL як специфікації деякої особливої моделі даних SQL стала дійсно актуальною після появи стандарту SQL :1999. До цього стверджувалося, що мова SQL багато в чому наслідує реляційну модель даних, але з деякими відхиленнями, які слід мати на увазі. Проте поява "об'єктних розширень" в SQL:1999, наприклад, повної системи типів, робить закінченою власну модель SQL, ще більше віддаляє модель SQL від класичної реляційної моделі даних Кодда. Тим самим створює потребу в зіставленні моделі SQL з об'єктною моделлю ODMG і "істинно реляційною" моделлю Крістофера Дейта і Х'ю Дарвена.

Розуміння відмінностей моделі даних SQL від реляційної моделі даних дуже важливе для проектувальників, адміністраторів і розробників додатків SQL -ориентированных баз даних. Сьогоднішня модель даних SQL включає класичну реляційну модель даних, хоча розходиться в частині механізму типів даних з "істинно реляційною" моделлю. Очевидною перевагою класичної реляційної моделі даних є її власна простота і простота баз даних і їх застосувань, що виходить з неї. Тому при використанні SQL треба явно усвідомлювати реальну потребу використання можливостей, що виходять за межі класичної реляційної моделі даних.

Історія розвитку і стандарти мови SQL

На початку 70-х років XX століття в одній з дослідницьких лабораторій компанії IBM була розроблена експериментальна реляційна СУБД IBM System R, для якої потім була створена спеціальна мова SEQUEL, що дозволяла відносно просто управляти даними в цій СУБД. Абревіатура SEQUEL розшифровувалася як Structured English QUEry Language - "структурована англійська мова запитів". Пізніше з юридичних міркувань мова SEQUEL була перейменована в SQL.

Метою розробки було створення простої непроцедурної мови, яким міг скористатися будь-який користувач, що навіть не має навичок програмування. Власне розробкою мови запитів займалися Дональд Чэмбэрлин (Donald D. Chamberlin) і Рэй Бойс (Ray Boyce). Пет Селинджер (Pat Selinger) займалася розробкою вартісного оптимізатора (cost - based optimizer), Рэймонд Лорі (Raymond Lorie) займався компілятором запитів.

Варто відмітити, що SEQUEL був не єдиною мовою подібного призначення. У Каліфорнійському Університеті Беркли була розроблена некомерційна СУБД Ingres, яка була реляційною СУБД, але використовувала свою власну мову QUEL, яка, проте, не витримав конкуренції по кількості підтримувальних його СУБД з мовою SQL.

Першими СУБД, що підтримують мову SQL, стали в 1979 році Oracle V2 для машин VAX від компанії Relational Software Inc., компанією Oracle, що згодом стала, і System/38 від IBM, заснована на System/R. Всупереч думці, що склалася, першою стала саме СУБД Oracle V2.

Перший офіційний стандарт мови SQL, так званий SQL - 86, був прийнятий ANSI (American National Standards Institute - американський національний інститут стандартизації) в 1986 році і ISO (International Organization for Standardzation - Міжнародна організація по стандартизації) в 1987 році і дещо уточнений в 1989 році.

Подальший розвиток мови постачальниками СУБД зажадав прийняття в 1992 році нового розширеного стандарту (ANSI SQL - 92 або просто SQL2). Наступним стандартом став SQL :1999 (SQL3). Нині діє стандарт, прийнятий в 2003 році (SQL :2003) з невеликими модифікаціями, внесеними пізніше. Зведення про версії стандарту мови SQL приведені в таблиці 7.1.

Таблиця 7.1 - Версії стандартів мови SQL

Рік	Назва	Інша назва	Зміни
1986	SQL - 86	SQL - 87	Перший варіант стандарту, прийнятий інститутом ANSI і схвалений ISO в 1987 році.
1989	SQL - 89	FIPS 127-1	Трохи допрацьований варіант попереднього стандарту.
1992	SQL - 92	SQL2, FIPS 127-2	Значні зміни (ISO 9075); рівень Entry Level стандарту SQL - 92 був прийнятий як стандарт FIPS 127-2.
1999	SQL :1999	SQL3	Додана підтримка регулярних виразів, рекурсивних запитів, підтримка тригерів, базові процедурні розширення, нескалярні типи даних і деякі об'єктно-орієнтовані можливості.
2003	SQL :2003		Введені розширення для роботи з XML -данными, віконні функції, вживані для роботи з OLAP -базами даних, генератори послідовностей і засновані на них типи даних.
2006	SQL :2006		Функціональність роботи з XML -данными значно розширена. З'явилася можливість спільно використовувати в запитах SQL і XQuery.
2008	SQL :2008		Поліпшені можливості віконних функцій, усунені деякі неоднозначності стандарту SQL :2003

Як і з багатьма стандартами в IT -индустрии, з мовою SQL виникла проблема: колись багато виробників використовуючого SQL програмного забезпечення вирішили, що функціонал в поточній на той момент часу версії стандарту недостатній і його бажано розширити. Це привело до того, що у різних виробників систем управління баз даних в ходу різні діалекти SQL, в загальному випадку між собою несумісні.

До 1996 року питаннями відповідності комерційних реалізацій SQL стандарту займався в основному інститут NIST, який і встановлював рівень відповідності стандарту. Але пізній підрозділ, що займався СУБД, був розформований, і на даний момент усі зусилля із перевірки СУБД на відповідність стандарту лягають на її виробника.

Уперше поняття "Рівня відповідності" було запропоноване в стандарті SQL - 92. А саме, ANSI і NIST визначали чотири рівні відповідності реалізації цьому стандарту :

1) Entry - базовий;

2) Transitional - перехідний (перевірку на відповідність цьому рівню проводив тільки інститут NIST);

3) Intermediate - проміжний;

4) Full - повний.

Кожен наступний рівень відповідності свідомо мав на увазі відповідність попередньому рівню. Будь-яка СУБД, яка відповідала рівню Entry, могла заявляти себе як "SQL - 92 compliant", хоча насправді переносимість і відповідність стандарту обмежувалося набором можливостей, що входять в цей рівень.

Положення змінилося з введенням стандарту SQL :1999. Віднині стандарт придбав модульну структуру - основна частина стандарту була винесена в розділ "SQL/Foundation", усі інші були виведені в окремі модулі. Відповідно, залишився тільки один рівень сумісності - Core, що означало підтримку цієї основної частини. Підтримка інших можливостей залишена на розсуд виробників СУБД. Аналогічне положення мало місце і з наступними версіями стандарту.

До безперечних достоїнств мови SQL слід віднести:

• переносимість;

• наявність стандартів;

• декларативність.

Переносимість мови SQL. Незважаючи на наявність діалектів і відмінностей в синтаксисі, в більшості своїй тексти SQL -запросов, можуть бути досить легко перенесені з однієї СУБД в іншу. Існують системи, розробники яких спочатку закладалися на застосування, щонайменше, декількох СУБД. Наприклад, система електронного документообігу Documentum може працювати як з СУБД Oracle, так і з Microsoft SQL Server і IBM DB2. Природно, що при застосуванні деяких специфічних для реалізації можливостей такої переносимості добитися вже дуже важко.

Наявність стандартів мови SQL. Наявність стандартів і набору тестів для виявлення сумісності і відповідності конкретної реалізації SQL загальноприйнятому стандарту тільки сприяє "стабілізації" мови. Правда, варто звернути увагу, що сам по собі стандарт місцями занадто формалізований і роздутий в розмірах, наприклад, Core -часть стандарту SQL :2003 є більше 1300 сторінок тексту.

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

До недоліків мови SQL варто віднести:

• неповна відповідність мови реляційній моделі;

• складність;

• відступ від стандартів;

• відсутність або обмеженість можливості роботи з ієрархічними структурами даних.

Невідповідність мови SQL реляційної моделі даних. Творці реляційної моделі даних Едгар Кодд, Крістофера Дейт і їх прибічники вказують на те, що SQL не є істинно реляційною мовою. Зокрема вони вказують на наступні проблеми SQL :

• наявність рядків, що повторюються, в таблицях бази даних;

• невизначені значення (nulls);

• явна вказівка порядку колонок зліва направо;

• колонки без імені і імена колонок, що дублюються;

• відсутність підтримки властивості "=";

• використання покажчиків;

• висока надмірність.

У опублікованому Крістофером Дейтом і Х'ю Дарвеном Третьому Маніфесті вони викладають принципи СУБД наступного покоління і пропонують мову Tutorial D, яка є достовірно реляційною.

Складність мови SQL. Хоча SQL і замислювався, як засіб роботи кінцевого користувача, врешті-решт, він став настільки складним, що перетворився на інструмент програміста.

Відступ від стандартів мови SQL. Незважаючи на наявність міжнародних стандартів, багато компаній, СУБД, що займаються розробкою, наприклад, Oracle, Sybase, Microsoft, MySQL AB, вносять зміни в мову SQL, вживану в СУБД, що розробляється, тим самим відступаючи від стандарту. Таким чином, з'являються специфічні для кожної конкретної СУБД діалекти мови SQL.

Відсутність або обмеженість можливості роботи з ієрархічними структурами даних. Раніше діалекти SQL більшості СУБД не пропонували способу маніпуляції деревовидними структурами. Деякі постачальники СУБД пропонували свої рішення. Наприклад, Oracle використовує вираження CONNECT BY. Нині стандартизована рекурсивна конструкція WITH з діалекту SQL DB2.

Оскільки SQL не є мовою програмування, тобто не надає засобів для автоматизації операцій з даними, розширення, що вводяться різними виробниками, торкалися в першу чергу процедурних розширень. Це процедури (stored procedures), що зберігаються, і процедурні мови -"надстройки". Практично в кожній СУБД застосовується своя процедурна мова. Стандарт для процедурних розширень представлений специфікацією SQL/PSM. Перелік процедурних розширень для популярних СУБД приведений в таблиці 7.2.

Таблиця 7.2 - Процедурні розширення в сучасних СУБД

СУБД	Коротка назва	Розшифровка
Microsoft SQL Server	Transact - SQL	Transact - SQL
Oracle	PL/SQL	Procedural Language/SQL (заснований на мові Ada)
IBM DB2	SQL PL	SQL Procedural Language (розширює SQL/PSM)
PostgreSQL	PL/pgSQL	Procedural Language/PostgreSQL Structured Query Language (дуже схожий на Oracle PL/SQL)
MySQL	SQL/PSM	SQL/Persistent Stored Module
Borland InterBase/Firebird	PSQL	Procedural SQL

У 1999 році був прийнятий стандарт SQL :1999. У цей стандарт не вдалося включити ряд важливих компонентів, серед яких засоби для управління розподіленими транзакціями (SQL/Transaction), підтримки тимчасових властивостей даних (SQL/Temporal), управління зовнішніми даними (SQL/MED), зв'язування для об'єктних мов програмування (SQL/OLB), а також можливості підтримки інтерактивної аналітичної обробки бази даних (SQL/OLAP). Три останні компоненти були допрацьовані і прийняті ISO/IEC як доповнення до стандарту SQL :1999. Проте створення SQL/Transaction було виключене з плану розвитку стандарту SQL. До моменту прийняття нової версії стандарту не була завершена і робота над SQL/Temporal.

Нова версія стандарту SQL - 2003 була прийнята ISO/IEC осінню 2003 р. Головне її нововведення - компонент SQL/XML, що визначає розширення мови SQL, яке дозволяє підтримувати XML -документы в середовищі баз даних SQL і виконувати операції маніпулювання даними над ними, а також правила для відображення таблиць, схем і каталогів SQL в XML -документы. Включені в новий стандарт специфікації SQL/JRT (Java Routines and Types) розширюють мову можливостями виклику статичних методів Java як підпрограм SQL, що викликаються, а також використовувати класи Java як структурні типи SQL. Що ж до компонента SQL/Replication, то його розробка не була завершена, і він не увійшов до нової версії стандарту.