Содержание
Часть 2. Структура СУБД, таблицы и типы данных
Назовем отдельные сущности биржи моделями. Чтобы избежать ошибок округления, с финансовыми суммами будем работать через класс 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 | Количество акций, участвующих в операции |
- размер данных существенно превышает объем доступной оперативной памяти;
- доступ к данным предполагается с десятка разных мест;
- необходима возможность одновременного модифицирования и чтения данных;
- нужно обеспечить правила формирования и целостности данных;
Хранение данных в Java
Рассмотрим акцию. В Java мы создали для этой модели определенный класс Share c полями name , changeProbability , startPrice , delta . А множество акций хранили как Map , где ключом служит уникальный идентификатор для каждой акции.
public class Share < private String name; private BigDecimal startPrice; private int changeProbability; private int delta; >Map 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
- Ключевые и зарезервированные слова, в том числе команды и операторы, нужно писать прописными буквами: 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 список правил будет пополняться/изменяться.
Проектирование СУБД
- Умеют генерировать SQL-команды для создания СУБД.
- Визуально отображают настройки на диаграмме.
- Позволяют перемещать таблицы для лучшей визуализации.
- Показывают на диаграмме ключи, индексы, связи, значения по умолчанию и тому подобное.
- Могут удалённо хранить схему СУБД.
Создание таблиц в СУБД
- имя таблицы
- имена и тип полей
- ограничения (CONSTRAINTS) на поля
- значения по умолчанию для полей (при наличии)
- первичный ключ (PRIMARY KEY) при наличии
- связи между таблицами (FOREIGN KEY)
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 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 символов длиной, может принимать пустые значения.
- CHECK(tradingMethod IN (1,2,3)) – поле tradingMethod может принимать только значения 1,2,3
- CHECK(changeProbability 0) – поле changeProbability может принимать целые значения в диапазоне от 1 до 100
Связи между таблицами
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) 
- Нам достаточно хранить в поле share только значение ключа id из таблицы shares, чтобы по нему получить оставшуюся информацию (название и так далее) из таблицы акций.
- Мы не можем создать курс для несуществующей акции. Вставить в поле share несуществующее значение (для которого нет записи в таблице shares с этим id) нельзя, так как не будет соответствия между таблицами.
- Мы не можем удалить в shares запись акции, для которой заданы курсы в share_rates.