При описании полей таблицы требуется указать для них типы данных, однако понятие типа данных в SQLite сильно отличается от MQL5.
MQL5 является строго типизированным языком: каждая переменная или поле структуры всегда сохраняет тип данных согласно декларации. SQL же является слабо типизированным языком: те типы, которые мы укажем в описании таблицы — не более, чем рекомендация. При этом программе не запрещено записать в любую "ячейку" (поле в записи) значение произвольного типа, и "ячейка" изменит свой тип, что, в частности, можно будет обнаружить и встроенной MQL-функцией DatabaseColumnType.
Разумеется, на практике все, как правило, придерживаются правила "уважать" типы столбцов.
Второе существенное отличие в механизме типов SQL — наличие большого количества ключевых слов, описывающих типы, однако все эти слова сводятся, в конечном счете, к пяти классам хранения. Будучи упрощенной версией SQL, SQLite в большинстве случаев не делает различий между ключевыми словами одной группы (например, в описании строки с лимитом длины VARCHAR(80) этот лимит не контролируется, и описание эквивалентно классу хранения TEXT), поэтому более логично описывать тип именем группы. Конкретные типы оставлены только для совместимости с другими СУБД (но для нас это неважно).
В следующей таблице приведены типы MQL5 и соответствующие им "аффинности" (affinity, обобщающие признаки типов SQL).
Типы MQL5 |
Обобщенные типы SQL |
|---|---|
NULL (не является типом в MQL5) |
NULL (значение отсутствует) |
bool, char, short, int, long, uchar, ushort, |
INTEGER |
float, double |
REAL |
(вещественное число фиксированной точности, |
NUMERIC |
string |
TEXT |
(произвольные "сырые" данные, |
BLOB (binary large object), NONE |
При записи значения в базу SQL определяет его тип по нескольким правилам:
Специальная функция SQL typeof позволяет проверить тип значения. Например, следующий запрос можно выполнить в редакторе MetaEditor.
SELECT typeof(100), typeof(10.0), typeof('100'), typeof(x'1000'), typeof(NULL); |
Он выведет в таблицу результатов:
integer | real | text | blob | null |
Проверять значения на NULL сравнением '=' нельзя (потому что результат тоже даст NULL), следует использовать специальный оператор NOT NULL.
SQLite налагает некоторые лимиты на хранимые данные: часть из них трудно достижима (и потому мы их здесь опустим), но другая может учитываться при проектировании программы. Так, максимальное количество столбцов в таблице равно 2000, а размер одной строки, BLOB-а и в целом одной записи не может превышать миллион байтов. Эта же величина выбрана пределом длины SQL-запроса.
Что касается даты и времени, SQL в принципе может хранить их в трех форматах, но лишь первый из них соответствует datetime в MQL5:
Вещественный тип хранения даты (называемый также "днем по юлианскому календарю", для чего есть встроенная SQL-функция julianday) интересен тем, что позволяет хранить время с точностью до миллисекунд. В принципе, это можно делать и в виде строки формата 'YYYY-MM-DDTHH:mm:SS.sssZ', но такое хранение очень неэкономно. Пересчет "дня" в количество секунд с дробной частью, начиная с привычной нам даты 1970.01.01 00:00:00, производится по формуле: julianday('now') - 2440587.5) * 86400.0. Здесь 'now' обозначает текущее время UTC, но может быть заменено на другие значения, описанные в документации SQLite. Константа 2440587.5 как раз равна количеству дней "календаря" на указанную "нулевую" дату — точку отсчета "эпохи Unix".
Помимо типа каждое поле может иметь особую характеристику — одно или несколько ограничений (constraints) — они записываются специальными ключевыми словами после типа. Ограничение описывает, какие значения может принимать поле, и даже позволяет автоматизировать заполнение согласно предопределенному назначению поля.
Рассмотрим основные ограничения по порядку.
... DEFAULT выражение |
При добавлении новой записи, если значение поля не указано, система автоматически проставит указанное здесь значение (константу) или вычислит выражение (функцию).
... CHECK ( логическое_выражение ) |
При добавлении новой записи система проверит, чтобы выражение, которое может содержать названия полей как переменные, было истинным. Если выражение ложно, запись не будет вставлена, а система вернет ошибку.
... UNIQUE |
Система проверяет, чтобы во всех записях таблицы отличались значения данного поля. Попытка добавить запись со значением, которое уже есть, вызовет ошибку, и добавления не произойдет.
Для отслеживания уникальности система неявным образом создает индекс по указанному полю.
... PRIMARY KEY |
Поле, помеченное данным признаком, используется системой для идентификации записей в таблице и ссылок на них из других таблиц (так образуются реляционные связи, дающие название рассматриваемым реляционным базам данных вроде SQLite). Очевидно, что данный признак также включает уникальный индекс.
Если в таблице нет поля типа INTEGER с признаком PRIMARY KEY, система автоматически неявным образом создает такой столбец под именем rowid. Если в вашей таблице есть целочисленное поле, объявленное первичным ключом, то оно также доступно и под алиасом rowid.
Если в таблицу добавляется запись, в которой rowid опущен или равен NULL, SQLite автоматически присвоит ей следующее целое число (64-битное, соответствующее long в MQL5), на единицу большее максимального rowid в таблице. Начальное значение — 1.
Обычно счетчик просто увеличивается каждый раз на 1, но если количество когда-либо вставленных (и, возможно, затем удаленных) в одну таблицу записей превысит long, счетчик перескочит на начало, и система будет пытаться найти свободные числа. Но такое маловероятно. Например, если писать в таблицу тики со средней скоростью 1 тик в миллисекунду, то переполнение случится через 292 миллиона лет.
Первичный ключ может быть только один, но он может состоять из нескольких столбцов — это делается с помощью иного синтаксиса, нежели ограничения — непосредственно в описании таблицы.
CREATE TABLE имя_таблицы (
|
Но вернемся к ограничениям.
... AUTOINCREMENT |
Данное ограничение может указываться только как дополнение PRIMARY KEY, гарантируя постоянное увеличение идентификаторов. Это означает, что любые прежние идентификаторы — даже те, что использовались у удаленных записей — не будут выбраны повторно. Однако данный механизм реализован в SQLite менее эффективно, чем простой PRIMARY KEY, в плане потребления вычислительных ресурсов, и поэтому не рекомендуется к применению.
... NOT NULL |
Это ограничение запрещает добавлять в таблицу запись, в которой данное поле не заполнено. По умолчанию, когда ограничения нет, любое неуникальное поле можно опустить в добавляемой записи и ему будет присвоено значение NULL.
... CURRENT_TIME
|
Данные инструкции позволяют автоматически заполнять поле временем (без даты), датой (без времени) или полным временем UTC на момент вставки записи (при условии, что SQL-оператор INSERT ничего не записывает в это поле явным образом, даже NULL). SQLite не умеет аналогичным автоматическим образом засекать время изменения записи — для этой цели придётся написать триггер (что выходит за рамки книги).
К сожалению, ограничения группы CURRENT_TIMESTAMP реализованы в SQLite с упущением: временная метка не проставляется, если поле имеет значение NULL. Это отличает SQLite от других движков SQL и от того, как сам SQLite обрабатывает NULL в полях первичных ключей. Получается, что для автоматического проставления метки нельзя записывать в базу весь объект целиком, а нужно явно указать все поля за исключением поля с датой и временем. Для решения проблемы нам потребуется альтернативный вариант с подстановкой SQL-функции STRFTIME('%s') в компилируемый запрос для в соответствующих столбцов.