SQLite¶

Поддержка стандарта SQL¶

Как известно, в своем развитии SQL устремился в разные стороны. Крупные производители начали впихивать всякие расширения. И хотя принимаются всякие стандарты, в реальной жизни все крупные БД не поддерживают стандартов полностью. Но зато имеют что-то свое.

Так вот, SQLite старается жить по принципу «минимальный, но полный набор». Она не поддерживает сложные штуки, но во многом соответствует SQL 92. И вводит некие свои особенности, которые очень удобны, но — не стандартны.

Неподдерживаются следующие возможности:

• RIGHT и FULL OUTER JOIN. Реализован только LEFT OUTER JOIN.
• Частично реализован ALTER TABLE. Доступны только RENAME TABLE и ADD COLUMN.
• Частичная поддержка тригеров. Доступены только FOR EACH ROW тригеры.
• Запись во VIEWS. В SQLite VIEWS доступны только на чтение. Частично обходится через тригеры.
• В силу реализации базы данных, как единственного файла и отхода от концепции «клиент-сервер», не используются возможности GRANT и REVOKE.
• По умолчанию отключены внешние ключи. Это сделано для обратной совместимости.

Типизация¶

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

Тип столбца определяет как сравнивать значения (нужно же их привести к единому типу при сравнении, скажем, внутри индекса). Но не обязывает заносить значения именно такого типа в столбец.

Допустим, мы объявили столбец как «A INTEGER». SQLite позволяет занести в этот столбец значения любого типа (999, «abc», «123», 678.525). Если вставляемое значение — не целое, то SQLite пытается привести его к целому. Т.е. строка «123» превратится в целое 123, а остальные значения запишутся «как есть».

Возможные типы полей: NULL, INTEGER, REAL, TEXT, BLOB.

Надёжность¶

Ситуация с покрытиями тестами исходного кода SQLite в некотором роде является легендой.

Это связно с тем, что тестами описано практически все возможные (и невозможные) ситуации. Кода, описывающего тесты, намного больше, чем кода реализующего SQLite. Естественно, что покрытие кода тестами 100%.

Сам же подход к тестированию использующийся разработчиками SQLite достоин подражания. И может быть взят как идеал, к которому следуют стремиться.

Практика¶

В качестве практического занятия для закрепления понимания реляционной модели и иллюстрации её ограниченности, мы рассмотрим пример создания структуры каталога товаров.

Для того чтобы запустить SQL-код необходимо выполнить следующую команду:

sqlite3 ~/example.sqlite

Далее откроется интерактивный SQLite-shell, куда можно вводить команды.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

CREATE TABLE category (name TEXT NOT NULL);

INSERT INTO category (name) VALUES
    ('Тапки'),
    ('Самолёты'),
    ('Ноутбуки');


CREATE TABLE product (
    name TEXT NOT NULL,
    price NUMERIC NOT NULL,
    category REFERENCES category(rowid)
);

INSERT INTO product (name, price, category) VALUES
    ('Босоножки', 1.17, 1),
    ('Вьетнамки', 2.36, 1),
    ('Макасины', 4.99, 1),
    ('ИЛ-2', 556000, 2),
    ('Суперджет 100', 1500000, 2),
    ('Ту-160', 25000000, 2),
    ('Dell', 590, 3),
    ('Lenovo', 200, 3),
    ('Sony', 437, 3);

1
2
3
4
5

ALTER TABLE product ADD size INTEGER;

ALTER TABLE product ADD capacity INTEGER;

ALTER TABLE product ADD processor TEXT;