Структура (схема) таблиц: типы данных и ограничения

При описании полей таблицы требуется указать для них типы данных, однако понятие типа данных в SQLite сильно отличается от MQL5.

MQL5 является строго типизированным языком: каждая переменная или поле структуры всегда сохраняет тип данных согласно декларации. SQL же является слабо типизированным языком: те типы, которые мы укажем в описании таблицы — не более, чем рекомендация. При этом программе не запрещено записать в любую "ячейку" (поле в записи) значение произвольного типа, и "ячейка" изменит свой тип, что, в частности, можно будет обнаружить и встроенной MQL-функцией DatabaseColumnType.

Разумеется, на практике все, как правило, придерживаются правила "уважать" типы столбцов.

Второе существенное отличие в механизме типов SQL — наличие большого количества ключевых слов, описывающих типы, однако все эти слова сводятся, в конечном счете, к пяти классам хранения. Будучи упрощенной версией SQL, SQLite в большинстве случаев не делает различий между ключевыми словами одной группы (например, в описании строки с лимитом длины VARCHAR(80) этот лимит не контролируется, и описание эквивалентно классу хранения TEXT), поэтому более логично описывать тип именем группы. Конкретные типы оставлены только для совместимости с другими СУБД (но для нас это неважно).

В следующей таблице приведены типы MQL5 и соответствующие им "аффинности" (affinity, обобщающие признаки типов SQL).

При записи значения в базу SQL определяет его тип по нескольким правилам:

• отсутствие кавычек, десятичной точки или показателя степени дают INTEGER;
• наличие точки и экспоненты означают REAL;
• обрамление из одиночных или двойных кавычек сигнализирует о типе TEXT;
• значение NULL без кавычек соответствует классу NULL;
• литералы (константы) с двоичными данными записываются как шестнадцатеричная строка с префиксом 'x'.

Специальная функция SQL typeof позволяет проверить тип значения. Например, следующий запрос можно выполнить в редакторе MetaEditor.

Он выведет в таблицу результатов:

Проверять значения на NULL сравнением '=' нельзя (потому что результат тоже даст NULL), следует использовать специальный оператор NOT NULL.

SQLite налагает некоторые лимиты на хранимые данные: часть из них трудно достижима (и потому мы их здесь опустим), но другая может учитываться при проектировании программы. Так, максимальное количество столбцов в таблице равно 2000, а размер одной строки, BLOB-а и в целом одной записи не может превышать миллион байтов. Эта же величина выбрана пределом длины SQL-запроса.

Что касается даты и времени, SQL в принципе может хранить их в трех форматах, но лишь первый из них соответствует datetime в MQL5:

• INTEGER — количество секунд с начала 1970.01.01 (оно еще называется "эпохой Unix");
• REAL — количество дней (с долями) от 24 ноября 4714 года до нашей эры;
• TEXT — дата и время с точностью до миллисекунды в формате "YYYY-MM-DD HH:mm:SS.sss", опционально с часовым поясом, для чего добавляют суффикс "[±]HH:mm" со смещением от UTC.

Вещественный тип хранения даты (называемый также "днем по юлианскому календарю", для чего есть встроенная 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) — они записываются специальными ключевыми словами после типа. Ограничение описывает, какие значения может принимать поле, и даже позволяет автоматизировать заполнение согласно предопределенному назначению поля.

Рассмотрим основные ограничения по порядку.

При добавлении новой записи, если значение поля не указано, система автоматически проставит указанное здесь значение (константу) или вычислит выражение (функцию).

При добавлении новой записи система проверит, чтобы выражение, которое может содержать названия полей как переменные, было истинным. Если выражение ложно, запись не будет вставлена, а система вернет ошибку.

Система проверяет, чтобы во всех записях таблицы отличались значения данного поля. Попытка добавить запись со значением, которое уже есть, вызовет ошибку, и добавления не произойдет.

Для отслеживания уникальности система неявным образом создает индекс по указанному полю.

Поле, помеченное данным признаком, используется системой для идентификации записей в таблице и ссылок на них из других таблиц (так образуются реляционные связи, дающие название рассматриваемым реляционным базам данных вроде SQLite). Очевидно, что данный признак также включает уникальный индекс.

Если в таблице нет поля типа INTEGER с признаком PRIMARY KEY, система автоматически неявным образом создает такой столбец под именем rowid. Если в вашей таблице есть целочисленное поле, объявленное первичным ключом, то оно также доступно и под алиасом rowid.

Если в таблицу добавляется запись, в которой rowid опущен или равен NULL, SQLite автоматически присвоит ей следующее целое число (64-битное, соответствующее long в MQL5), на единицу большее максимального rowid в таблице. Начальное значение — 1.

Обычно счетчик просто увеличивается каждый раз на 1, но если количество когда-либо вставленных (и, возможно, затем удаленных) в одну таблицу записей превысит long, счетчик перескочит на начало, и система будет пытаться найти свободные числа. Но такое маловероятно. Например, если писать в таблицу тики со средней скоростью 1 тик в миллисекунду, то переполнение случится через 292 миллиона лет.

Первичный ключ может быть только один, но он может состоять из нескольких столбцов — это делается с помощью иного синтаксиса, нежели ограничения — непосредственно в описании таблицы.

Но вернемся к ограничениям.

Данное ограничение может указываться только как дополнение PRIMARY KEY, гарантируя постоянное увеличение идентификаторов. Это означает, что любые прежние идентификаторы — даже те, что использовались у удаленных записей — не будут выбраны повторно. Однако данный механизм реализован в SQLite менее эффективно, чем простой PRIMARY KEY, в плане потребления вычислительных ресурсов, и поэтому не рекомендуется к применению.

Это ограничение запрещает добавлять в таблицу запись, в которой данное поле не заполнено. По умолчанию, когда ограничения нет, любое неуникальное поле можно опустить в добавляемой записи и ему будет присвоено значение NULL.

Данные инструкции позволяют автоматически заполнять поле временем (без даты), датой (без времени) или полным временем UTC на момент вставки записи (при условии, что SQL-оператор INSERT ничего не записывает в это поле явным образом, даже NULL). SQLite не умеет аналогичным автоматическим образом засекать время изменения записи — для этой цели придётся написать триггер (что выходит за рамки книги).

К сожалению, ограничения группы CURRENT_TIMESTAMP реализованы в SQLite с упущением: временная метка не проставляется, если поле имеет значение NULL. Это отличает SQLite от других движков SQL и от того, как сам SQLite обрабатывает NULL в полях первичных ключей. Получается, что для автоматического проставления метки нельзя записывать в базу весь объект целиком, а нужно явно указать все поля за исключением поля с датой и временем. Для решения проблемы нам потребуется альтернативный вариант с подстановкой SQL-функции STRFTIME('%s') в компилируемый запрос для в соответствующих столбцов.