Частина 2. Структура СУБД, таблиці та типи даних

Перша частина Ми продовжуємо створювати наш простенький емулятор біржі. Ось що ми зробимо:

• Створимо схему організації бази даних.
• Розпишемо що, як і де зберігається.
• Дізнаємось, як дані пов'язані один з одним.
• Почнемо вивчати основи SQL з прикладу команди створення таблиці SQL CREATE TABLE , Data Definition Language ( DDL ) мови SQL.
• Продовжимо писати Java-програму. Основні функції СУБД у частині java.sql по створенню нашої бази даних реалізуємо програмно, використовуючи JDBC та триланкову (3-tier) архітектуру.

Ці дві частини вийшли більш об'ємними, оскільки нам необхідно ознайомитися з основами SQL та організацією СУБД зсередини та навести аналогії з Java. Щоб не втомлювати листинг коду, в кінці наведені посилання на відповідний commit github-репозиторія з програмою.

Дизайн СУБД

Опис програми

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

• Існують акції, вартість яких може змінюватись протягом торговельного дня за заданими правилами;
• є трейдери із початковим капіталом;
• трейдери можуть купувати та продавати акції, відповідно до свого алгоритму.

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

Структура даних емуляції біржі

Назвемо окремі сутності біржі моделями. Щоб уникнути помилок округлення, з фінансовими сумами працюватимемо через клас BigDecimal(подробиці можна дізнатися за посиланням наприкінці статті). Розпишемо структуру кожної моделі докладніше: Акція: Курс акції: Трейдер: Дії трейдерів: Для забезпечення унікальності кожної моделі додамо атрибут idтипу long . Цей атрибут буде унікальним у межах екземплярів моделі і однозначно його визначатиме. Атрибути, які посилаються інші моделі (трейдер, акція, курс акції), можуть використовувати цей idдля однозначного визначення відповідної моделі. Відразу спадає на думку, що ми могли б використовувати Map<Long, Object> для зберігання подібних даних, де Object– відповідна модель. Однак спробуйте реалізувати це в коді за таких умов:

• обсяг даних значно перевищує обсяг доступної оперативної пам'яті;
• доступ до даних передбачається із десятка різних місць;
• необхідна можливість одночасного модифікування та читання даних;
• необхідно забезпечити правила формування та цілісності даних;

…і ви зіткнетеся із завданнями, що вимагають належної кваліфікації та часу на реалізацію. Не варто винаходити велосипед. Багато чого вже продумано та написано за нас. Тож ми будемо використовувати те, що вже перевірено роками.

Зберігання даних у Java

Розглянемо акцію. У Java ми створабо для цієї моделі певний клас Shareз полями name, changeProbability, startPrice, delta. А безліч акцій зберігали як Map<Long, Share>, де ключем є унікальний ідентифікатор кожної акції.

public class Share {
    private String name;
    private BigDecimal startPrice;
    private int changeProbability;
    private int delta;
}
Map<Long, Share> shares = new HashMap<>();
shares.put(1L, new Share("ibm", BigDecimal.valueOf(20.0), 15, 10));
shares.put(2L, new Share("apple", BigDecimal.valueOf(14.0), 25, 15));
shares.put(3L, new Share("google", BigDecimal.valueOf(12.0), 20, 8));
...
shares.put(50L, new Share("microsoft", BigDecimal.valueOf(17.5), 10,4 ));

Для доступу до потрібної акції з ідентифікатора застосовуємо метод shares.get(id). Для завдання знаходження на ім'я акції або ціні, ми б перебирали в циклі всі записи в пошуках потрібної і так далі. Але ми підемо іншим шляхом, і зберігатимемо значення в СУБД.

Зберігання даних у СУБД

Сформулюємо початкове зведення правил зберігання даних для СУБД:

• Дані в СУБД організовані таблиці ( TABLE ), що становлять набір записів.
• Усі записи мають однакові набори полів. Вони задаються під час створення таблиці.
• Для поля можна встановити значення за замовчуванням ( DEFAULT ).
• Для таблиці можна виставити обмеження ( CONSTRAINT ), що описують вимоги до її даних, щоб забезпечити їх цілісність. Це можна зробити на етапі створення таблиці ( CREATE TABLE ) або додати пізніше ( ALTER TABLE … ADD CONSTRAINT ).
• Найбільш поширені CONSTRAINT :
  • Первинний ключ PRIMARY (Id у нашому випадку).
  • Унікальне значення є поле UNIQUE (VIN для таблиці автотранспорту).
  • Перевірка поля CHECK (значення відсотків не може перевищувати 100). Одне з приватних обмежень на полі – NOT NULL або NULL , що забороняє/дозволяє зберігати NULL у полі таблиці.
  • Посилання на сторонню таблицю FOREIGN KEY (посилання на акцію у таблиці курсів акцій).
  • Індекс INDEX (індексування поля для прискорення пошуку значень щодо нього).
  • Виконання модифікації запису ( INSERT , UPDATE ) не відбудеться, якщо значення її полів суперечать обмеженням (CONSTRAINT).
• Кожна таблиця може мати ключове поле (або кілька), за якою можна однозначно визначити запис. Таке поле (або поля, якщо вони формують складовий ключ) утворює первинний ключ таблиці – PRIMARY KEY .
  • Первинний ключ забезпечує унікальність запису таблиці, у ньому створюється індекс, що дає швидкий доступ за значенням ключа до всієї записи.
  • Наявність первинного ключа значно полегшує створення посилань між таблицями. Далі ми будемо використовувати штучний первинний ключ: для першого запису id = 1кожен наступний запис буде вставлятися в таблицю зі збільшеним на одиницю значенням id. Такий ключ часто називають AutoIncrement або AutoIdentity .

Власне, таблиця акцій: Чи можна в такому разі використовувати як ключ ім'я акції? За великим рахунком - так, тільки ось є можливість, що якась компанія випускає різні акції і називає їх тільки своєю назвою. У такому разі, унікальності вже не буде. Насправді штучний первинний ключ використовують досить часто. Погодьтеся, використання ПІБ як унікальний ключ у таблиці, що містить записи по людях, не забезпечить унікальності. Як і використання комбінації ПІБ та дати народження.

Типи даних у СУБД

Як і в будь-якій іншій мові програмування SQL існує типізація даних. Наведемо найпоширеніші типи даних SQL: Цілі типи Речові Рядкові дата та час Зберігання великих обсягів даних

Стиль написання коду в SQL

Для багатьох мов існують рекомендації щодо оформлення коду. Зазвичай такі документи містять правила іменування змінних, констант, методів та інших мовних структур. Так, для Python існує PEP8, для Java – Oracle Code Conventions for Java . Для SQL створено кілька різних склепінь, які дещо відрізняються один від одного. Незважаючи на це, слід виробити звичку дотримуватися правил оформлення коду, особливо якщо ви працюєте в команді. Правила можуть бути, наприклад, наступними (зрозуміло, ви можете розробити для себе інший набір правил, головне дотримуйтесь їх надалі):

• Ключові та зарезервовані слова, у тому числі команди та оператори, потрібно писати великими літерами: CREATE TABLE, CONSTRAINT…
• Імена таблиць, полів та інших об'єктів не повинні збігатися з ключовими словами мови SQL (див. посилання наприкінці статті), але можуть містити їх у собі.
• Імена таблиць мають відображати їх призначення. Вони записуються малими літерами. Слова у найменуванні відокремлені одне від одного підкресленнями. Слово в кінці має бути у множині : traiders (трейдери), share_rates (курс акцій).
• Імена полів таблиць повинні відображати їхнє призначення. Їх потрібно записувати малими літерами, слова в найменуванні потрібно оформляти в стилі Camel Case , а слово в кінці потрібно використовувати в однині : name (найменування), share_rates (курс акцій).
• Поля штучних ключів повинні мати слово id.
• Імена CONSTRAINT повинні відповідати правилам іменування таблиць. Також вони повинні включати поля і таблиці, що беруть участь у них, починатися зі змістового префікса: check_ (перевірка значення поля), pk_ (первинний ключ), fk_ (зовнішній ключ), uniq_ (унікальність поля), idx_ (індекс). Приклад: pk_traider_share_actions_id (первинний ключ з поля id для таблиці traider_share_actions).
• І так далі, в міру вивчення SQL перелік правил буде поповнюватися/змінюватися.

Проектування СУБД

Безпосередньо перед створенням СУБД її необхідно спроектувати. Кінцева схема містить таблиці, набір полів, CONSTRAINT, ключі, умови за промовчанням для полів, зв'язок між таблицями та інші сутності БД. В інтернеті можна знайти багато безкоштовних online/offline дизайнерів для проектування невеликих СУБД. Спробуйте вбити в пошукову систему щось на кшталт “Database designer free”. Такі додатки мають корисні додаткові властивості:

• Вміють генерувати SQL-команди до створення СУБД.
• Візуально відображаються параметри на діаграмі.
• Дозволяють переміщати таблиці для кращої візуалізації.
• Показують на діаграмі ключі, індекси, зв'язки, значення за промовчанням тощо.
• Можуть віддалено зберігати схему СУБД.

Наприклад, dbdiffo.com виділяє ключі, показує міткою NN непусті поля та AI(AutoIncrement) – лічильники:

Створення таблиць у СУБД

Отже, ми маємо схему. Тепер перейдемо безпосередньо до створення таблиць (CREATE TABLE). Для цього нам бажано мати попередні дані:

• ім'я таблиці
• імена та тип полів
• обмеження (CONSTRAINTS) на поля
• значення за промовчанням для полів (за наявності)
• первинний ключ (PRIMARY KEY) за наявності
• зв'язок між таблицями (FOREIGN KEY)

Не будемо вивчати детально всі опції команди CREATE TABLE, розглянемо основи SQL на прикладі створення таблиці для трейдерів:

CREATE TABLE traiders(
	id BIGINT AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(255) NOT NULL,
	freqTiсk INTEGER NOT NULL,
	cash  DECIMAL(15,2) NOT NULL DEFAULT 1000,
	tradingMethod INTEGER NOT NULL,
	changeProbability INTEGER NOT NULL DEFAULT 50,
	about VARCHAR(255) NULL
);
ALTER TABLE traiders ADD CONSTRAINT check_traiders_tradingMethod
	CHECK(tradingMethod IN (1,2,3));
ALTER TABLE traiders ADD CONSTRAINT check_traiders_changeProbability
	CHECK(changeProbability <= 100 AND changeProbability > 0)

Розберемо докладніше:

• CREATE TABLE traiders(опис полів) — створення таблиці із зазначеним ім'ям, в описі поля розділяються комою. Будь-яка команда завершується крапкою з комою.
• Опис поля починається з його імені, далі слідує тип, CONSTRAINT і значення за замовчуванням.
• id BIGINT AUTO_INCREMENT PRIMARY KEY– поле id цілого типу – це первинний ключ та інкрементний лічильник (для кожного нового запису для поля id генеруватиметься значення на одиницю більше раніше створеного для цієї таблиці).
• cash DECIMAL(15,2) NOT NULL DEFAULT 1000– поле cash, десятковий дріб, 15 цифрами до коми та дві після (фінансові дані, наприклад, долари та центи). Не може приймати значення NULL. Якщо значення не встановлено, воно отримає значення 1000.
• about VARCHAR(255) NULL– поле about, рядок до 255 символів завдовжки, може набувати порожніх значень.

Зауважимо, що частину CONSTRAINT -умов ми можемо поставити після створення таблиці. Розглянемо конструкцію для модифікації структури таблиці та її полів: ALTER TABLE ім'я_таблиці ADD CONSTRAINT ім'я_обмеження CHECK(умова) на прикладах:

• CHECK(tradingMethod IN (1,2,3))– поле tradingMethod може набувати лише значення 1,2,3
• CHECK(changeProbability <= 100 AND changeProbability > 0)– поле changeProbability може приймати цілі значення в діапазоні від 1 до 100

Зв'язки між таблицями

Для аналізу опису зв'язків між таблицями подивимося створення share_rates:

CREATE TABLE share_rates(
	id BIGINT AUTO_INCREMENT PRIMARY KEY,
	operDate datetime NOT NULL,
	share BIGINT NOT NULL,
	rate DECIMAL(15,2) NOT NULL
);
ALTER TABLE share_rates ADD FOREIGN KEY (share) REFERENCES shares(id)

Посилання на значення іншої таблиці можна задати наступним чином: ALTER TABLEтаблиця_з_якою_посилаємося ADD FOREIGN KEY(поле_яке_посилається) REFERENCESтаблиця_на_яку_посилаємося(поле_на_яке_посилаємося) Нехай у shares ми маємо записи по акціях, наприклад, для id50 і 50 . шансом зміни 4%. Для таблиці share_rates ми отримуємо три основні властивості:

• Нам достатньо зберігати в полі share тільки значення ключа id з таблиці shares, щоб по ньому отримати інформацію, що залишилася (назва і так далі) з таблиці акцій.
• Ми не можемо створити курс для неіснуючої акції. Вставити в поле share неіснуюче значення (для якого немає запису в таблиці shares із цим id) не можна, тому що не буде відповідності між таблицями.
• Ми не можемо видалити в shares запис акції, для якого задано курси в share_rates.

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