Первая часть
Мы продолжаем создавать наш простенький эмулятор биржи. Вот что мы сделаем:
- Создадим схему организации базы данных.
- Распишем что, как и где хранится.
- Узнаем, как данные связаны друг с другом.
- Начнём изучать основы SQL на примере команды создания таблицы SQL CREATE TABLE, Data Definition Language (DDL) языка SQL.
- Продолжим писать Java-программу. Основные функции СУБД в части java.sql по созданию нашей базы данных реализуем программно, используя JDBC и трехзвенную (3-tier) архитектуру.
Эти две части вышли более объёмными, поскольку нам необходимо ознакомиться с основами SQL и организацией СУБД изнутри, и привести аналогии с Java. Чтобы не утомлять листингами кода, в конце приведены ссылки на соответствующий commit github-репозитория с программой.
Дизайн СУБД
Описание приложения
Вы уже слышали о том, что организация хранения данных – неотъемлемая часть программирования. Напомню, цель нашего приложения – простейшая эмуляция биржи:
- Существуют акции, стоимость которых может меняться в течение торгового дня по заданным правилам;
- есть трейдеры с начальным капиталом;
- трейдеры могут покупать и продавать акции, согласно своему алгоритму.
Биржа работает
тиками – фиксированными периодами времени (в нашем случае — 1 мин). В течение тика может поменяться курс акции, далее совершиться покупка или продажа акций трейдером.
Структура данных эмуляции биржи
Назовем отдельные сущности биржи моделями. Чтобы избежать ошибок округления, с финансовыми суммами будем работать через класс
BigDecimal
(подробности можно узнать по ссылке в конце статьи). Распишем структуру каждой модели подробнее:
Акция:
Атрибут |
Тип |
Описание |
name |
Srting |
Наименование |
changeProbability |
int |
Вероятность смены курса в процентах на каждом тике |
startPrice |
BigDecimal |
Начальная стоимость |
delta |
int |
Максимальная величина в процентах, на которую может смениться текущая стоимость |
Курс акции:
Атрибут |
Тип |
Описание |
operDate |
LocalDateTime |
Время (тик) выставления курса |
share |
Акция |
Ссылка на акцию |
rate |
BigDecimal |
Курс акции |
Трейдер:
Атрибут |
Тип |
Описание |
name |
String |
Время (тик) выставления курса |
sfreqTick |
int |
Частота совершения операций. Задана периодом, в тиках, спустя который трейдер совершает операции |
cash |
BigDecimal |
Сумма денег, помимо акций |
traidingMethod |
int |
Используемый трейдером алгоритм. Зададим его числом-константой, реализация алгоритма будет (в следующих частях) в Java-коде |
changeProbability |
int |
Вероятность выполнения операции, в процентах |
about |
String |
Вероятность смены курса, в процентах, на каждом тике |
Действия трейдеров:
Атрибут |
Тип |
Описание |
operation |
int |
Тип операции (покупка или продажа) |
traider |
Трейдер |
Ссылка на трейдера |
shareRate |
Курс акции |
Ссылка на курс акции (соответственно на саму акцию, её курс и время его выставление) |
amount |
Long |
Количество акций, участвующих в операции |
Для обеспечения уникальности каждой модели, добавим атрибут
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-тип
|
SQL-синонимы
|
Соответствие в Java
|
Описание
|
INT
|
INT4,INTEGER
|
java.lang.Integer
|
4-байтовое целое, -2147483648 … 2147483647
|
BOOLEAN
|
BOOL, BIT
|
java.lang.Boolean
|
True, False
|
TINYINT
|
|
java.lang.Byte
|
1-байтовое целое, -128 … 127
|
SMALLINT
|
INT2
|
java.lang.Short
|
2-байтовое целое, -32768 … 32767
|
BIGINT
|
INT8
|
java.lang.Long
|
8-байтовое целое, -9223372036854775808 … 9223372036854775807
|
AUTO_INCREMENT
|
INCREMENT
|
java.lang.Long
|
Инкрементальный счётчик, уникальный для таблицы. Если в неё вставляют новое значение, он увеличивается на единицу Сгенерированные значения никогда не повторяются.
|
Вещественные
SQL-тип
|
SQL-синонимы
|
Соответствие в Java
|
Описание
|
DECIMAL(N,M)
|
DEC, NUMBER
|
java.math.BigDecimal
|
Десятичная дробь с фиксированной точностью (N цифр целой части и M — дробной). В основном предназначены для работы с финансовыми данными.
|
DOUBLE
|
FLOAT8
|
java.lang.Double
|
Вещественное число двойной точности (8 байт).
|
REAL
|
FLOAT4
|
java.lang.Real
|
Вещественное число одинарной точности (4 байта).
|
Строковые
SQL-тип
|
SQL-синонимы
|
Соответствие в Java
|
Описание
|
VARCHAR(N)
|
NVARCHAR
|
java.lang.String
|
Строка в формате UNICODE длины N. Длина ограничена значением 2147483647 Полностью загружает содержимое строки в память.
|
Дата и время
SQL-тип
|
SQL-синонимы
|
Соответствие в Java
|
Описание
|
TIME
|
|
java.time.LocalTime,
java.sql.Time
|
Хранение времени (до наносекунд), при конвертации в DATETIME, в качестве даты выставляется 1 янв 1970.
|
DATE
|
|
java.time.LocalDate,
java.sql.Timestamp
|
Хранение дат в формате yyyy-mm-dd, время выставляется как 00:00
|
DATETIME
|
TIMESTAMP
|
java.time.LocalDateTime, java.sql.Timestamp
|
Хранение даты + времени
(без учёта временных зон).
|
Хранение больших объемов данных
SQL-тип
|
Соответствие в Java
|
Описание
|
BLOB
|
java.io.InputStream, java.sql.Blob
|
Хранение двоичных данных (картинок, файлов...).
|
CLOB
|
java.io.Reader, java.sql.Clob
|
Хранение больших текстовых данных (книг, статей...),
в отличии от VARCHAR загружает данные в память порциями.
|
Стиль написания кода в 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-репозиторий в конце статьи.
Третья часть