Часть 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 c полями 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 мы имеем записи по акциям, например, для id=50 храним акции Microsoft с начальной ценой 17.5, дельтой 20 и шансом изменения 4%. Для таблицы share_rates мы получаем три основных свойства:

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

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