Типи даних
NOTICE: Цей документ є розділом з книги “The InterBase World”, яку написали Олексій Ковязін та Серг Востріков.
Незважаючи на те, що типи даних детально описані в документації (див. [1, розділ 4.), необхідно розглянути ряд понять, які часто використовуватимуться в наступних розділах цієї книги. Окрім загальної інформації, також буде розглянуто кілька прикладів використання типів даних у базах даних InterBase та рекомендації щодо їх використання та перетворення. Ми детально розглянемо відмінності типів даних, які існують у 1-му та 3-му діалектах баз даних InterBase.
Про дані
Типи даних є основними елементами будь-якої мови програмування або будь-якого сервера СУБД. InterBase не є винятком. Коли ми говоримо, що база даних зберігає певну інформацію, ми повинні усвідомлювати, що ця інформація не може зберігатися в одному місці. Навпаки, дані мають бути розсортовані та розкладені по своїх “полицях”. Типи даних визначають, що можна покласти на певну “полицю”, а що ні. Коли ми говоримо про “полицю”, ми насамперед маємо на увазі поля таблиць бази даних (див. розділ “Таблиці. Первинні ключі та генератори” (частина 1)), а також змінні в тригерах і збережених процедурах тощо. Кожен тип даних має набір операцій, які можна виконувати над значеннями цього типу. Тому важливо правильно вибрати тип даних під час проєктування бази даних. Це допоможе уникнути багатьох проблем під час розробки клієнтських програм. В InterBase існує 12 типів даних, які можуть задовольнити вимоги розробника щодо зберігання даних. Ці типи умовно поділяються на 6 наступних груп:
- для зберігання цілих чисел - INTEGER та SMALLINT;
- для зберігання дійсних чисел - FLOAT та DOUBLE PRECISION;
- для зберігання чисел з фіксованою точністю - NUMERIC та DECIMAL;
- для зберігання дати, часу та мітки часу - DATE, TIME та TIMESTAMP;
- для зберігання символів - CHARACTER (у скороченій формі - CHAR) та VARYING CHARACTER (VARCHAR);
- для зберігання динамічно розширюваних даних - BLOB
Крім того, можна визначати масиви значень примітивних типів, тобто всіх перелічених типів, окрім BLOB.
Більшість типів даних InterBase відповідають типам, визначеним у стандарті SQL92, однак, окрім цього, існують і власні особливості - масиви примітивних типів даних та BLOB. Масиви в InterBase можуть містити набір даних одного типу в одному полі. Наприклад, ми можемо визначити масив значень типу INTEGER. Масиви можуть мати кілька вимірів. Тип даних BLOB - це тип даних, що динамічно розширюється, назву якого часто розшифровують як Binary Large OBject. Слід зазначити, що BLOB є винаходом розробників InterBase, який пізніше поширився і закріпився у всіх сучасних SQL-серверах.
Синтаксис визначення типів даних
Типи даних використовуються для опису полів у таблицях, змінних у тригерах та збережених процедурах. Загальний синтаксис визначення всіх можливих типів даних в InterBase наведено нижче.
< datatype> =
{SMALLINT | INTEGER | FLOAT | DOUBLE PRECISION}[ ]
| {DATE | TIME | TIMESTAMP} [ ]
| {DECIMAL | NUMERIC} [( precision [, scale])] [ ]
| {CHAR | CHARACTER | CHARACTER VARYING | VARCHAR} [( int)
[ ] [CHARACTER SET charname]
| {NCHAR | NATIONAL CHARACTER | NATIONAL CHAR}
[VARYING] [( int)] [ ]
| BLOB [SUB_TYPE { int | subtype_name}] [SEGMENT SIZE int]
[CHARACTER SET charname]
| BLOB [(seglen [, subtype])]
Характеристики типів даних, такі як розмір, точність та діапазон можливих значень, детально описані в таблиці 4.1 в [1., тому ми не будемо повторюватися тут. Тепер коротко розглянемо основні особливості типів даних і зосередимося на їх можливому застосуванні.
Цілі типи
SMALLINT та INTEGER відносяться до цілих типів. Слід зазначити, що SMALLINT є урізаною версією INTEGER. Його довжина становить 2 байти, на відміну від 4, призначених для зберігання INTEGER. Зазвичай не варто економити на дисковому просторі. З цієї причини краще використовувати тип INTEGER для зберігання цілих значень.
Сфера застосування цілих типів очевидна: вони необхідні для полів, що містять лише цілі числа - для зберігання лічильників, номерів тощо. Зазвичай тип INTEGER також використовують для полів, що містять первинні ключі.
Дійсні типи даних
Типи FLOAT та DOUBLE PRECISION відносяться до дійсних (їх також називають числовими типами з плаваючою точкою). Перш за все, я хочу застерегти читача від використання типу FLOAT - його точності недостатньо для зберігання більшості дробових значень. Особливо не рекомендується зберігати в ньому грошові значення - у змінних типу FLOAT помилки округлення з’являються дуже швидко, і це може здивувати бухгалтера під час розрахунків. Найкращий спосіб зберігати числа з плаваючою точкою (наприклад, у бухгалтерських системах та системах для наукових розрахунків) у базі даних - це зберігати їх у типі DOUBLE PRECISION.
Слід враховувати, що в 3-му діалекті InterBase існує механізм зберігання типів з фіксованою точкою розміром 64 байти. Цей механізм використовується для зберігання грошових значень. Використання цих типів забезпечує найкращу точність.
Типи даних з фіксованою точкою
NUMERIC та DECIMAL відносяться до цих типів даних. Питання про різницю між NUMERIC та DECIMAL звучить дуже часто. Обидва ці типи мають однакову розрядність - від 1 до 18 знаків, однакову точність - від нуля до розрядності.
Нагадаємо, що: розрядність - це загальна кількість цифр, а точність - кількість знаків після коми.
Найцікавіше те, що ці типи відрізняються максимальною розрядністю згідно з документацією, але насправді вони реалізовані практично однаково, і між ними немає різниці.
Ви можете легко це перевірити, запустивши утиліту isql та виконавши наведену нижче послідовність дій. Створюємо таблицю наступного вигляду:
SQL> CREATE TABLE test (
CON> Num_field NUMERIC(15,2),
CON> Dec_field DECIMAL(15,2));
Потім даємо команду показати структуру таблиці:
SQL> show tables test;
І бачимо наступне:
NUM_FIELD NUMERIC(15, 2) Nullable
DEC_FIELD NUMERIC(15, 2) Nullable
Як бачите, InterBase повідомляє, що обидві наведені колонки мають тип NUMERIC. Причини такої поведінки криються в реалізації типів даних з фіксованою точкою. Справа в тому, що InterBase має лише три механізми зберігання будь-якого цілого виразу, і всі типи, як би вони не називалися, реалізовані за цими варіантами.
Ось таблиця з [1., яка ілюструє, як зберігаються різні цілі типи (таблиця 1.1). Як бачите, зберігання даних у 3-му діалекті відрізняється для чисел з великою розрядністю:
Таблиця 1.1. Зберігання чисел з фіксованою точкою
| Розрядність | Діалект 1 | Діалект 3 |
|---|---|---|
| від 1 до 4 | SMALLINT для NUMERIC INTEGER для DECIMAL |
SMALLINT |
| від 5 до 9 | INTEGER | INTEGER |
| від 10 до 18 | DOUBLE PRECISION | INT64 |
Тепер ми можемо точно сказати, які основні відмінності між типами NUMERIC та DECIMAL: у разі визначення поля (змінної) з малою розрядністю (до чотирьох) перший зберігається як 2-байтове ціле SMALLINT, а другий - як 4-байтове INTEGER. Таким чином, якщо розрядність більше чотирьох, типи DECIMAL та NUMERIC будуть еквівалентними.
Зверніть увагу на різницю в реалізації типів з великою розрядністю в першому та третьому діалектах. У першому діалекті число з фіксованою точкою перетворювалося з цілого на дійсне, де застосовувалися механізми округлення. У третьому діалекті цю особливість було усунено - великі цілі числа зберігаються насправді як цілі - за допомогою механізму INT64, який може зберігати 64-бітові числа в діапазоні +/-2^32. Тому краще зберігати дані про грошові кошти в базах даних, створених із використанням 3-го діалекту. Лише використання механізму INT64 гарантує збереження дрібних грошових залишків.
Типи для зберігання дати та часу
Типи для зберігання дати та часу змінилися у версії InterBase 6.x та її клонах порівняно з 4.x та 5.x. Щоб не заплутатися в історичному павутинні з цими типами, розглянемо ситуацію у версії InterBase 6. Потім на основі цього коротко згадаємо, що було раніше. Це зроблено для тих користувачів, які досі працюють з ранніми версіями InterBase. Отже, в InterBase 6.x існує 3 типи для зберігання дати та часу - DATE, TIME та TIMESTAMP.
- Тип DATE зберігає дати з точністю до дня. Діапазон можливих значень - від 1 січня 100 року н.е. до 29 лютого 32768 року.
- Тип TIME зберігає дані про час з точністю до десятитисячної частки секунди. Діапазон можливих значень - від 00:00 до 23:59.9999.
- Тип TIMESTAMP являє собою комбінацію типів DATE та TIME.
Як працювати з датами? Якщо питання стосується роботи на рівні сервера в збережених процедурах або тригерах, все досить просто - ми завжди можемо оголосити змінну потрібного типу та присвоїти їй значення з таблиць і навпаки. Однак необхідно передавати дані з бази даних у додаток і назад. У цьому випадку існує два підходи: використовувати бібліотеки, які застосовують оригінальний формат дат InterBase для доступу до об’єктів цих типів і конвертують цей формат у звичайні внутрішньомовні типи дати/часу (FIBPlus є прикладом такої бібліотеки), або використовувати вбудований в InterBase механізм перетворення дат у рядки.
Що робити, якщо вам потрібно виокремити лише рік або місяць з повної дати? Для цієї мети доведеться використати групу функцій EXTRACT (доступних у всіх клонах InterBase 6.x), які дозволяють витягти з дати лише потрібну частину. Ці функції використовуються наступним чином:
EXTRACT (MONTH FROM DATE_FIELD)
EXTRACT (YEAR FROM DATE_FIELD)
Повний список параметрів функції EXTRACT наступний: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, WEEKDAY, YEARDAY. Їхнє призначення випливає з їхніх назв, тому ми не будемо їх тут пояснювати.
Типи даних для зберігання тексту
В InterBase існує два типи, призначені для зберігання текстової інформації - CHAR та VARCHAR. Їхні повні назви - CHARACTER та CHARACTER VARYING, однак немає причин використовувати довгі назви - навіть команда Show tables в утиліті isql видає короткі назви типів.
Щоб визначити поле або змінну символьного типу, необхідно вказати в дужках після назви типу кількість символів, які будуть використовуватися у визначеному об’єкті, або пропустити кількість символів - таким чином буде створено поле довжиною 1 символ.
CREATE TABLE testCHARLen(
Field1 CHAR(255),
Field2 CHAR);
При створенні цієї таблиці Field1 матиме довжину 255 символів, а Field2 - 1 символ.
Типи CHAR та VARCHAR багато в чому схожі - обидва можуть містити до 32768 символів, однак існують деякі відмінності. Хоча обидва ці типи зберігаються в базі даних однаковим чином, InterBase працює з ними по-різному. Наступний приклад демонструє це:
SQL> create table testCHAR ( c1 char(10), c2 varchar(10));
SQL> insert into testCHAR(c1,c2) values(‘Test’,‘Test’);
SQL> SELECT ‘(’||c1.|’)’, ‘(’||c2.|’)’ from testCHAR;
У результаті ми отримаємо наступне:
(Test ) (Test)
Як бачите, після значення ‘Test’, вибраного з поля c1, з’явилися пробіли. Це означає, що при виборі даних з поля типу CHAR повернуте значення доповнюється пробілами до повної довжини поля. Важко припустити, навіщо потрібна така поведінка, що призводить до значного зростання мережевого трафіку (навантаження на мережу). У будь-якому випадку, VARCHAR - це символьний тип, який рекомендується використовувати.
Однією з найважливіших характеристик символьного типу є його набір символів - CHARACTER SET. Набір символів визначається для всієї бази даних і використовується за замовчуванням для всіх символьних полів, якщо він не перевизначений явно при створенні поля. Щоб створити символьне поле з явним зазначенням набору символів, необхідно додати опис набору символів в опис колонки (у реченнях CREATE TABLE або ALTER TABLE). Набір символів WIN1251 зазвичай використовується для підтримки російської мови (якщо ви хочете детально дізнатися про використання російської мови в InterBase, дивіться розділ «Інтернаціоналізація InterBase» (частина 1)). Ось приклад таблиці, що містить символьне поле з явно описаним набором символів WIN1251:
CREATE TABLE TestCHARSET(
Field1 VARCHAR(255),
Field2 VARCHAR(255) CHARACTER SET win1251);
Тут Field1 - це поле без явного зазначення набору символів, тому для нього буде використано набір символів, указаний при створенні бази даних. Для поля Field2 явно визначено, що воно зберігатиме символи в кодуванні WIN1251.
Окрім зазначення набору символів для символьних полів, можна також вказати порядок сортування (collation), який визначає, як будуть сортуватися символи цього набору даних. Для російської мови існує два варіанти сортування - WIN1251 та PXW_CYRL. Детальніше про використання COLLATION ORDER дивіться в розділі «Інтернаціоналізація InterBase».
Повний список наборів символів та COLLATION ORDER, що застосовуються до них, можна знайти в документації [1, розділ 13].
Увага! Згідно з документацією щодо InterBase 6, існує 4 символьні типи: окрім вищезгаданих типів даних існують ще 2 - NCHAR та NCHAR VARYING, однак та сама документація нижче пояснює, що останні два типи є тими самими типами, що й CHAR та VARCHAR, лише з набором символів ISO8859.1 за замовчуванням. Це означає, що фактично використання псевдотипу NCHAR еквівалентне застосуванню CHAR DEFAULT CHARACTER SET ISO8859.1. Аналогічно для NCHAR VARYING, лише там замість CHAR використовується VARCHAR. Очевидно, що застосування цих псевдотипів призначене для користувачів у Західній Європі та США, де набір символів ISO8859.1 створений для підтримки мов.
Тип даних BLOB
Тип даних BLOB призначений для зберігання великої кількості даних змінного розміру. Тип BLOB дозволяє зберігати дані, які не можна розмістити в полях інших типів, - наприклад, зображення, музичні файли, відеофрагменти тощо. Вимоги до визначення найпростішого поля типу BLOB у таблиці такі самі, як і для визначення поля будь-якого елементарного типу:
CREATE TABLE testBLOB(
myBlobField BLOB);
У результаті буде створено поле myBlobField, у якому можна зберігати дані значного розміру. Незважаючи на те, що BLOB-поля не відрізняються від інших за способом визначення, їхня реалізація в базі даних дуже відрізняється. Не-BLOB-поля розташовані на сторінці даних (дивіться розділ «Структура бази даних InterBase» (частина 4)) близько одне до одного, а у випадку BLOB на сторінці даних зберігається лише ідентифікатор BLOB, сам же BLOB розміщується на спеціальній сторінці. Така структура даних дозволяє зберігати дані нефіксованого розміру.
Тип BLOB має можливість визначення набору кількох підтипів та спеціальних процедур, які називаються фільтрами (BLOB filters), для роботи з цими підтипами. В InterBase вбудовано кілька заздалегідь визначених підтипів BLOB. Усі ці підтипи мають невід’ємні числа, наприклад, підтип 0 - дані невизначеного типу, підтип 1 - текст, підтип 2 - BLR (Binary Language Representation, дивіться глосарій та розділ «Структура бази даних InterBase») тощо. Користувач також може визначати підтипи BLOB, які можуть мати від’ємні значення. До кожного типу можна застосувати фільтр. Він перетворюватиме поле одного підтипу в інший.
Слід зазначити, що використання BLOB-полів зазвичай є альтернативою зберіганню зовнішніх відносно бази даних файлів. Що стосується BLOB-фільтрів, вони використовуються досить рідко через орієнтацію на вузьку категорію завдань.
Масиви
СУБД InterBase була однією з перших, у якій з’явилися масиви. Підтримка масивів у базі даних є розширенням традиційної реляційної моделі. Наявність масивів дозволяє спростити роботу з наборами даних одного типу.
Масив - це сукупність значень одного типу, що має спільне ім’я та дозволяє звертатися до будь-якого елемента масиву за його номером. Масиви в InterBase можуть бути одновимірними та багатовимірними.
Щоб створити поле типу масив чисел INTEGER у таблиці, слід записати щось подібне до наступного:
CREATE TABLE test(
myOneDimArray INTEGER[12.,
myTwoDimArray INTEGER[5,4.,
myThreeDimArray INTEGER[2,10,8.);
Таким чином, буде створено три поля типу масив: myOneDimArray - поле, що містить одновимірний масив довжиною 12 чисел, myTwoDimArray - поле, що містить двовимірний масив (матрицю) - 5х4 цілих чисел, та myThreeDimArray - поле, що містить тривимірний масив 2х10х8. Слід зазначити, що за такого визначення елементи масиву нумеруються, починаючи з «одиниці», тобто перший елемент має номер 1, другий - номер 2 тощо. Якщо хтось бажає самостійно задати межі масиву, наприклад від 0 до 5, слід вказати визначення поля наступним чином:
myArray INTEGER[0:5.
Масиви реалізуються на основі полів типу BLOB, тому не варто боятися, що багатовимірний масив «засмітить» вашу таблицю великою кількістю даних: InterBase акуратно розмістить дані масиву на окремих сторінках, щоб оптимізувати операції введення-виведення для цих полів. Як використовувати масиви? Вони надають зручний механізм для зберігання об’єктів одного типу. Однак у 80% випадків замість масивів розробники віддають перевагу зберіганню множинних даних у підпорядкованих (детальних) таблицях, тому масиви не так часто використовуються в клієнтських застосунках СКБД InterBase. Це відбувається тому, що бібліотеки доступу, які постачаються з Delphi та C++ Builder, такі як BDE та IBX, не здатні працювати з масивами. Згідно з документацією щодо InterBase, можна працювати з масивами за допомогою препроцесора gpre, однак це не найзручніший спосіб для розробника Delphi/C++ Builder. На щастя, у бібліотеці FIBPlus є підтримка полів-масивів в InterBase. Детальніше про це можна прочитати в розділі «Спеціальні можливості FIBPlus». Клієнтська бібліотека IBProvider, яка дозволяє створювати клієнтські застосунки для InterBase за допомогою засобів розробки компанії Microsoft, також підтримує роботу з масивами (див. розділ «Розробка клієнтських застосунків InterBase DBMS з використанням технології Microsoft OLE DB» (частина 3)).
Висновок
Слід зазначити, що неможливо розповісти про типи даних, не забігаючи наперед, оскільки вони проникають у всі ключові сфери, пов’язані з розробкою застосунків баз даних. Тому під час читання цієї книги краще використовувати цей розділ як швидкий довідник, до якого можна звертатися щоразу, коли потрібно освіжити основи InterBase.