Sql lite что это

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

Особенности SQLite

Ограничения SQLite

В SQLite имеется несколько неподдерживаемых функций SQL92:

Команды SQLite

Стандартные команды SQLite для взаимодействия с реляционными базами данных аналогичны SQL. Это CREATE, SELECT, INSERT, UPDATE, DELETE и DROP.

Источник

Национальная библиотека им. Н. Э. Баумана
Bauman National Library

Персональные инструменты

SQLite

SQLite — это встраиваемая кроссплатформенная БД, которая поддерживает достаточно полный набор команд SQL и доступна в исходных кодах (на языке C).

Содержание

Общее

SQLite – это встраиваемая библиотека в которой реализовано многое из стандарта SQL 92. Её притязанием на известность является как собственно сам движок базы, так и её интерфейс (точнее его движок) в пределах одной библиотеки, а также возможность хранить все данные в одном файле. Позиция функциональности SQLite где-то между MySQL и PostgreSQL. Однако, на практике, SQLite нередко оказывается в 2-3 раза (и даже больше) быстрее. Такое возможно благодаря высокоупорядоченной внутренней архитектуре и устранению необходимости в соединениях типа «сервер-клиент» и «клиент-сервер».

Всё это, собранное в один пакет, лишь немногим больше по размеру клиентской части библиотеки MySQL, является впечатляющим достижением для полноценной базы данных. Используя высоко эффективную инфраструктуру, SQLite может работать в крошечном объёме выделяемой для неё памяти, гораздо меньшем, чем в любых других системах БД. Это делает SQLite очень удобным инструментом с возможностью использования практически в любых задачах возлагаемых на базу данных.

Преимущества

Недостатки

SQLite поддерживает динамическое типизирование данных.

Возможные типы полей

Архитектура

Движок БД представляет библиотеку, с которой программа компонуется и SQLite становится составной частью программы. Вся БД хранится в единственном стандартном файле на машине, на которой исполняется программа.

Несколько процессов или потоков могут одновременно без каких-либо проблем читать данные из одной базы. Запись в базу можно осуществить только в том случае, если никаких других запросов в данный момент не обслуживается; в противном случае попытка записи оканчивается неудачей, и в программу возвращается код ошибки. Другим вариантом развития событий является автоматическое повторение попыток записи в течение заданного интервала времени.

Особенности

Эта часть является собранием всевозможных особенностей SQLite, без понимания которых невозможно постичь SQLite.

Использование SQLite в многопоточных приложениях

SQLite может быть собран в однопоточном варианте (параметр компиляции SQLITE_THREADSAFE = 0 ). В этом варианте его нельзя одновременно использовать из нескольких потоков, поскольку полностью отсутствует код синхронизации. Проверить, есть ли многопоточность можно через вызов sqlite3_threadsafe(): если вернула 0, то это однопоточный SQLite. По умолчанию, SQLite собран с поддержкой потоков (sqlite3.dll). Есть два способа использования многопоточного SQLite: serialized и multi-thread.

Serialized (надо указать флаг SQLITE_OPEN_FULLMUTEX при открытии соединения). В этом режиме потоки могут как угодно дергать вызовы SQLite, никаких ограничений. Но все вызовы блокируют друг друга и обрабатываются строго последовательно.

Multi-thread ( SQLITE_OPEN_NOMUTEX ). В этом режиме нельзя использовать одно и то же соединение одновременно из нескольких потоков (но допускается одновременное использование разных соединений разными потоками). Обычно используется именно этот режим.

Формат данных

База данных SQLite может хранить (текстовые) данные в UTF-8 или UTF-16. Набор вызовов API состоит из вызовов, которые получают UTF-8 (sqlite3_XXX) и вызовов, которые получают UTF-16 (sqlite3_XXX16). Если тип данных интерфейса и соединения не совпадает, то выполняется конвертация «на лету».

Поддержка UNICODE

Немного про работу ICU и SQLite.

Порядок сортировки значений разных типов:

SQLite выполняет неявные преобразования типов «на лету» в нескольких местах:

Значения BLOB и NULL всегда заносятся в любой столбец «как есть».

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

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

В SQLite в уникальном индексе может быть сколько угодно NULL значений (с этим согласен Oracle и не согласен MS SQL).

Если в вызове sqlite3_open() передать имя файла как «:memory:», то SQLite создаст соединение к новой (чистой) БД в памяти. Это соединение абсолютно неотличимо от соединения к БД в файле по логике использования: доступен тот же набор SQL команд. Сейчас это исправлено и можно открыть два соединения к одной БД в памяти.

Теперь все таблицы БД в файле db1.sqlite3 стали прозрачно доступны в нашем соединении. Для разрешения конфликтов имен следует использовать имя присоединения (основная база называется «main»):

Ничего не мешает присоединить к БД новую базу в памяти и использовать ее для кэширования и пр.

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

SQL команда PRAGMA служит для задания всевозможных настроек у соединения или у самой БД:

Настройку соединения (очевидно) следует проводить сразу после открытия и до его использования.

Журнал и фиксация транзакций

50 в секунду. Именно поэтому, не получается вставлять записи быстро, используя неявную транзакцию.

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

— до любой модификации БД SQLite сохраняет изменяемые страницы из БД в отдельном файле (журнале), то есть просто копирует их туда; — убедившись, что копия страниц создана, SQLite начинает менять БД; — убедившись, что все изменения в БД «дошли до диска» и БД стала целостной, SQLite стирает журнал.

PRAGMA journal_mode = DELETE

Это означает, что файл журнала удаляется после завершения транзакции. Сам факт наличия файла с журналом в этом режиме означает для SQLite, что транзакция не была завершена, база нуждается в восстановлении. Файл журнала имеет имя файла БД, к которому добавлено «-journal».

В режиме TRUNCATE файл журнала обрезается до нуля (на некоторых системах это работает быстрее, чем удаление файла).

В режиме PERSIST начало файла журнала забивается нулями (при этом его размер не меняется и он может занимать кучу места).

В режиме MEMORY файл журнала ведется в памяти и это работает быстро, но не гарантирует восстановление базы при сбоях (копии данных-то нету на диске).

Мы знаем, что современные системы используют хитроумное кэширование для повышения производительности и могут откладывать запись на диск. Допустим, SQLite завершил запись в БД и хочет стереть файл журнала, чтобы отметить факт фиксации транзакции. Если в этот промежуток времени отключится питание, то журнала уже не будет, а БД еще не будет целостной — потеря данных!

PRAGMA synchronous задает степень «паранойи» SQLite на это счет.

Режим OFF (или 0) означает: SQLite считает, что данные фиксированы на диске сразу после того как он передал их ОС (то есть сразу после вызова соот-го API ОС). Это означает, что целостность гарантирована при аварии приложения (поскольку ОС продолжает работать), но не при аварии ОС или отключении питания.

Режим синхронизации NORMAL (или 1) гарантирует целостность при авариях ОС и почти при всех отключениях питания. Существует ненулевой шанс, что при потере питания в самый неподходящий момент база испортится. Это некий средний, компромисный режим по производительности и надежности.

Режим FULL гарантирует целостность всегда и везде и при любых авариях. Но работает, разумеется, медленнее, поскольку в определенных местах делаются паузы ожидания. И это режим по умолчанию.

Режим журнала WAL

Режим журнала WAL работает иначе — он «постоянный». Как только мы перевели базу в режим WAL, она останется в этом режиме, пока ей явно не поменяют режим журнала на другой.

Изначально SQLite проектировалась как встроенная БД. Архитектура разделения одновременного доступа к данным была устроена примитивно: одновременно несколько соединений могут читать БД, а вот записывать в данный момент времени может только одно соединение. Это, как минимум, означает, что пишущее соединение ждет «освобождения» БД от читающих. При попытке записать в «занятую» БД приложение получает ошибку SQLITE_BUSY (не путать с SQLITE_LOCKED!). Достигается этот механизм разделения доступа через API блокировки файлов (которые плохо работают на сетевых дисках, поэтому там не рекомендуется использовать SQLite; узнать больше )

В режиме WAL (Write-Ahead Logging) «читатели» БД и «писатели» в БД уже не мешают друг другу, то есть допускается модификация данных при одновременном чтении. Короче говоря, это шаг в сторону больших и серьезных СУБД, в которых все так и есть. Утверждается также, что SQLite в WAL работает быстрее.

Но есть и недостатки: — требуется некоторые дополнительные ништяки от ОС (unix и Windows имеют эти ништяки); — БД занимает несколько файлов (файлы «XXX-wal» и «XXX-shm»); — плохо работает на больших транзакциях (условно, если транзакция больше 50 Мбайт); — нельзя открыть такую БД в режиме «только чтение»; — возникает дополнительная операция checkpoint.

Фактически, в режиме WAL данные БД разделяются между БД и файлом журнала. Операция checkpoint переносит данные в БД. По умолчанию, это делается автоматически, если журнал занял 1000 страниц БД. То есть, идут быстрые COMMIT-ы и вдруг какой-то COMMIT задумался и начал делать checkpoint. Если такое поведение нежелательно, можно делать checkpoint вручную (когда все спокойно), можно это делать и в отдельном процессе.

Пределы

Несмотря на миниатюрность, SQLite в реальности не накладывает серьезных ограничений на размеры полей, таблиц или БД.

По умолчанию, BLOB или строкое значение могут занимать 1 Гбайт и это же ограничение размера одной записи (можно поднять до 2^31 — 1, параметр SQLITE_MAX_LENGTH ).

Количество столбцов: 2000 (можно поднять до 32767, SQLITE_MAX_COLUMN ).

Размер SQL оператора: 1 МБайт (1073741824 байт, SQLITE_MAX_SQL_LENGTH ).

Одновременный join: 64 таблицы.

Присоединить баз к соединению: 10 (до 62, SQLITE_MAX_ATTACHED )

Максимальное количество страниц в БД: 1073741823 (до 2147483646, SQLITE_MAX_PAGE_COUNT ).

Если задать размер страницы 65636 байт, то максимальный размер БД будет примерно 14 Терабайт.

Максимальное число записей в таблице: 2^64 — 1, но на практике, конечно, ограничение размера вступит раньше.

Пример

Использование

Автоматическое резервирование

Пример скрипта, которые раз в день создает резервную копию бд.

Источник

SQLite — замечательная встраиваемая БД (часть 1)

Решил все-таки написать статью про SQLite, в которой хочу обобщить свой 3-х летний опыт использования этой БД под Windows. Вижу, что тема популярная, но информации мало.

Эта статья не для начинающих программистов.
Она не является учебником по SQL.
Она не агитирует использовать SQLite.
Она не агитирует не использовать SQLite.
Статья написана в виде вопросов от гипотетического новичка в SQLite и ответов на них (поскольку информации очень много и так хоть немного проще ее структурировать).

Что такое SQLite?

SQLite — это встраиваемая кроссплатформенная БД, которая поддерживает достаточно полный набор команд SQL и доступна в исходных кодах (на языке C).

Исходные коды SQLite находятся в public domain, то есть вообще никаких ограничений на использование.

Сайт (с прекрасной документацией на английском): http://sqlite.org

Текущая версия: 3.7.13

SQLite можно скомпилировать самому, но я скачиваю ее уже скомпилированную в виде Windows DLL.

Для собственной сборки обычно скачивают т.н. «amalgamation»,
т.е. исходники SQLite в виде единого файла на языке C + sqlite3.h.

Чтобы уменьшить размер кода SQlite, выкинув ненужные ништяки, используются всякие DEFINE.

Насколько SQLite популярна?

Кратко: она везде. Как минимум, на любом смартфоне.

Насколько она надежна?

2 млн тестов), покрытие кода тестами 100% (с августа 2009).

А какие еще инструменты дают разработчики?

Доступна консольная утилита для работы с базами (sqlite3.exe, «a command-line shell for accessing and modifying SQLite databases»).

И все?

Да, от основных разработчиков — все. Однако, другие люди пишут всякие менеджеры и пр.
Лично я так и не нашел идеального и пользуюсь консолью.

Что значит «достаточно полный набор SQL»?

Как известно, в своем развитии SQL устремился в разные стороны. Крупные производители начали впихивать всякие расширения. И хотя принимаются всякие стандарты (SQL 92), в реальной жизни все крупные БД не поддерживают стандартов полностью + имеют что-то свое. Так вот, SQLite старается жить по принципу «минимальный, но полный набор». Она не поддерживает сложные штуки, но во многом соответствует SQL 92.
И вводит некие свои особенности, которые очень удобны, но — не стандартны.

Что конкретно в поддержке SQL может вызвать недоумение?

Нельзя удалить или изменить столбец в таблице (ALTER TABLE DROP COLUMN…, ALTER TABLE ALTER COLUMN… ).
Есть триггеры, но не настолько мощные как у крупных RDBMS.
Есть поддержка foreign key, но по умолчанию — она ОТКЛЮЧЕНА.
Нет встроенной поддержки UNICODE (но ее, вообщем, нетрудно добиться).
Нет хранимых процедур.

А что своего хорошего или необычного?

a) каждая запись содержит виртуальный столбец rowid, который равен 64-битному номеру (уникальному для таблицы).
Можно объявить свой столбец INTEGER PRIMARY KEY и тогда этот столбец станет rowid (со своим именем, имя rowid все равно работает).
При вставке записи можно указать rowid, а можно — не указывать (и система тогда вставит уникальный).
Подробности: www.sqlite.org/autoinc.html
b) можно без труда организовать БД в памяти (это очень удобно и чуть позже расскажу подробнее);
c) легко переносить: по умолчанию, БД — это один файл (в кроссплатформенном формате);
d) тип столбца не определяет тип хранимого значения в этом поле записи, то есть в любой столбец можно занести любое значение;
e) много встроенных функций (которые можно использовать в SQL): www.sqlite.org/lang_corefunc.html;

Не понял — что там с типом? Зачем нужен тип столбца тогда вообще?

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

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

Так можно вообще не задавать тип столбца?

Очень часто так и делается: CREATE TABLE foo (a,b,c,d).

А как с архитектурой? Сервера-то нету?

Сервера нету, само приложение является сервером. Доступ к БД происходит через «подключения» к БД (нечто вроде хэндла файла ОС), которые мы открываем через вызов соот-й функции DLL. При открытии указывается имя файла БД. Если такого нету — он автоматически создается.
Допустимо открывать множество подключений к одной и тоже БД (через имя файла) в одном или разных приложениях.
Система использует механизмы блокировки доступа к файлу на уровне ОС, чтобы это все работало
(эти механизмы обычно плохо работают на сетевых дисках, так что не рекомендуется использовать SQlite с файлом на сети).
Изначально SQlite работал по принципу «многие читают — один пишет».
То есть только одно соединение пишет в БД в данный момент времени. Если другие соединения попробуют тоже записать, то словят ошибку SQLITE_BUSY.
Можно, однако, ввести таймаут операций. Тогда подключение, столкнувшись с занятостью БД, будет ждать N секунду прежде, чем отвалиться с ошибкой SQLITE_BUSY.

И как быть?

Либо одно подключение и все запросы через него, либо исходить из возможного таймаута и предусмотреть повтор выполнения SQL.
Есть и еще одна возможность: не так давно появился новый вид лога SQlite: Write Ahead Log, WAL.
Если включить для БД именно этот режим лога, то несколько подключений смогут одновременно модифицировать БД.
Но в этом режиме БД уже занимает несколько файлов.

Ну понятно теперь почему SQLite — ужасна, ведь у нее нет ГЛОБАЛЬНОГО КЭША?

Действительно, все современные RDBMS немыслимы без глобального разделяемого кэша, который может хранить всякие ништяки вроде скомпилированных параметризованных запросов. Этим занят сервер, которого тут нет. Однако, в рамках одного приложения SQlite может разделять кэш между несколькими подключениями (читать тут: www.sqlite.org/sharedcache.html) и немного сэкономить память.

А почему все жалуются, что SQLite — тормозит?

Две причины. Первая — настройки по умолчанию. Они работают на надежность, а не на производительность.
Вторая — непонимание механизма фиксации транзакций. По умолчанию после любой команды SQlite будет фиксировать транзакцию (то есть ожидать пока БД окажется в целостном состоянии для отключения питания). В зависимости от режима паранойи SQLite потратит на это от 50 до 300 мс (ожидая окончания записи данных на диск).

Что делать-то? Мне нужно вставить 100 тыс записей и быстро!

Удалить индексы, включить режим синхронизации OFF (или NORMAL), вставлять порциями по N тысяч (N — подобрать, для начала взять 5000). Перед вставкой порции сделать BEGIN TRANSACTION, после — COMMIT.

А вот я нашел ошибку! Как рапортовать?

Дело в том, что популярность SQLite страшна — она везде. Это не шутка.
И разработчики столкнулись с валом сообщений об ошибках, которые либо были вызваны непониманием, либо являлись скрытым feature request. Они, фактически, закрыли прямой прием репортов с ошибками.
Так что следует подписаться на список рассылки и описать там проблему и надеятся на лучшее.

Лично у меня возникла ситуация, которую я трактовал как дефект SQLIte. Я описал это в рассылке. В следующей версии поведение SQLite было исправлено.

Удобная утилита, чтобы поиграться с SQLite.

Источник

SQLite — замечательная встраиваемая БД (часть 3)

Третья часть — тонкости и особенности.

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

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

Использование SQLite в многопоточных приложениях

SQLite может быть собран в однопоточном варианте (параметр компиляции SQLITE_THREADSAFE = 0).

В этом варианте его нельзя одновременно использовать из нескольких потоков, поскольку полностью отсутствует код синхронизации. Зачем? Для бешеной скорости.

Проверить, есть ли многопоточность можно через вызов sqlite3_threadsafe(): если вернула 0, то это однопоточный SQLite.

По умолчанию, SQLite собран с поддержкой потоков (sqlite3.dll).

Есть два способа использования многопоточного SQLite: serialized и multi-thread.

Serialized (надо указать флаг SQLITE_OPEN_FULLMUTEX при открытии соединения). В этом режиме потоки могут как угодно дергать вызовы SQLite, никаких ограничений. Но все вызовы блокируют друг друга и обрабатываются строго последовательно.

Multi-thread (SQLITE_OPEN_NOMUTEX). В этом режиме нельзя использовать одно и то же соединение одновременно из нескольких потоков (но допускается одновременное использование разных соединений разными потоками). Обычно используется именно этот режим.

Формат данных

База данных SQLite может хранить (текстовые) данные в UTF-8 или UTF-16.

Набор вызовов API состоит из вызовов, которые получают UTF-8 (sqlite3_XXX) и вызовов, которые получают UTF-16 (sqlite3_XXX16).

Если тип данных интерфейса и соединения не совпадает, то выполняется конвертация «на лету».

Всегда используйте UTF-8.

Поддержка UNICODE

И некоторые собирают SQLite DLL уже с ним.

Типы данных и сравнение значений

Как уже говорилось, SQLIte позволяет записать в любой столбец любое значение.

Значение внутри БД может принадлежать к одному из следующих типов хранения (storage class):
NULL,
INTEGER (занимает 1,2,3,4,6 или 8 байт),
REAL (число с плавающей точкой, 8 байт в формате IEEE),
TEXT (строка в формате данных базы, обычно UTF-8),
BLOB (двоичные данные, хранятся «как есть»).

Порядок сортировки значений разных типов:
— NULL меньше всего (включая другой NULL);
— INTEGER и REAL меньше любого TEXT и BLOB, между собой сравниваются арифметически;
— TEXT меньше любого BLOB, между собой сравниваются на базе своих collation;
— BLOB-ы сравниваются между собой через memcmp().

SQLite выполняет неявные преобразования типов «на лету» в нескольких местах:
— при занесении значения в столбец (тип столбца задает рекомендацию по преобразованию);
— при сравнении значений между собой.

Столбец может иметь следующие рекомендации приведения типа: TEXT, NUMERIC, INTEGER, REAL, NONE.

Значения BLOB и NULL всегда заносятся в любой столбец «как есть».

В столбец TEXT значения TEXT заносятся «как есть», значения INTEGER и REAL становятся строками.
В столбец NUMERIC, INTEGER числа записываются «как есть», а строки становятся числами, если _могут_ (то есть допустимо обратное преобразование «без потерь»).
Для столбца REAL правила похожи на INTEGER(NUMERIC); отличие в том, что все числа представлены в формате с плавающей запятой.
В столбец NONE значения заносятся «как есть» (этот тип используется по умолчанию, если не задан другой).

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

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

Отмечу здесь, что в SQLite в уникальном индексе может быть сколько угодно NULL значений (с этим согласен Oracle и не согласен MS SQL).

База данных в памяти

Если в вызове sqlite3_open() передать имя файла как «:memory:», то SQLite создаст соединение к новой (чистой) БД в памяти.

Это соединение абсолютно неотличимо от соединения к БД в файле по логике использования: доступен тот же набор SQL команд.

Увы, не существует возможности открыть два соединения к одной и той же БД в памяти.

UPD: Уже, оказывается, можно открыть два соединения к одной БД в памяти.

Присоединение одновременно к нескольким БД

Чтобы открыть соединение к БД используется вызов sqlite3_open().

В любой момент времени мы можем к открытому соединению присоединить еще до 10 баз данных через SQL команду ATTACH DATABASE.

Теперь все таблицы БД в файле db1.sqlite3 стали прозрачно доступны в нашем соединении.

Для разрешения конфликтов имен следует использовать имя присоединения (основная база называется «main»):

Ничего не мешает присоединить к БД новую базу в памяти и использовать ее для кэширования и пр.

Это очень полезная возможность. Присоединяемые БД должны иметь формат данных такой же, как и у основной БД, иначе — ошибка.

Временная база данных

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

Тонкие настройки БД через команду PRAGMA

SQL команда PRAGMA служит для задания всевозможных настроек у соединения или у самой БД:

Настройку соединения (очевидно) следует проводить сразу после открытия и до его использования.

Полное описание всех параметров находится здесь.

Остановлюсь на важнейших вещах.

Журнал и фиксация транзакций

Вот и подошли к теме, овладение которой сразу переводит вас на третий уровень магистра SQLite.

SQLite тщательно блюдет целостность данных в БД (ACID), реализуя механизм изменения данных через транзакции.

Кратко о транзакциях: транзакция либо полностью накатывается, либо полностью откатывается. Промежуточных состояний быть не может.

Отсюда, кстати, и жалобы на «медленность» SQLite. SQLite может вставлять и до 50 тыс записей в секунду, но фиксировать транзакций он не может больше, чем

Именно поэтому, не получается вставлять записи быстро, используя неявную транзакцию.

При настройках по умолчанию SQLite гарантирует целостность БД даже при отключении питания в процессе работы.

Достигается подобное изумительное поведение ведением журнала (специального файла) и хитроумным механизмом синхронизации изменений на диске.

Кратенько обновление данных в БД работает так:

— до любой модификации БД SQLite сохраняет изменяемые страницы из БД в отдельном файле (журнале), то есть просто копирует их туда;
— убедившись, что копия страниц создана, SQLite начинает менять БД;
— убедившись, что все изменения в БД «дошли до диска» и БД стала целостной, SQLite стирает журнал.

Подробно атомарность механизма транзакций описана тут.

Если SQLite открывает соединение к БД и видит, что журнал уже есть, он соображает, что БД находится в незавершенном состоянии и автоматически откатывает последнюю транзакцию.

То есть механизм восстановления БД после сбоев, фактически, встроен в SQLite и работает незаметно для пользователя.

Это означает, что файл журнала удаляется после завершения транзакции. Сам факт наличия файла с журналом в этом режиме означает для SQLite, что транзакция не была завершена, база нуждается в восстановлении. Файл журнала имеет имя файла БД, к которому добавлено «-journal».

В режиме TRUNCATE файл журнала обрезается до нуля (на некоторых системах это работает быстрее, чем удаление файла).

В режиме PERSIST начало файла журнала забивается нулями (при этом его размер не меняется и он может занимать кучу места).

В режиме MEMORY файл журнала ведется в памяти и это работает быстро, но не гарантирует восстановление базы при сбоях (копии данных-то нету на диске).

А можно и совсем отключить журнал (PRAGMA journal_mode = OFF). В этой ситуации перестает работать откат транзакций (команда ROLLBACK) и база, скорее всего, испортится, если программа будет завершена аварийно.

Для базы данных в памяти режим журнала может быть только либо MEMORY, либо OFF.

Вернемся немного назад. Как же SQLite «убеждается», что база всегда будет целостной?

Мы знаем, что современные системы используют хитроумное кэширование для повышения производительности и могут откладывать запись на диск.

Допустим, SQLite завершил запись в БД и хочет стереть файл журнала, чтобы отметить факт фиксации транзакции.

А вдруг файл сотрется раньше, чем обновится БД?

Если в этот промежуток времени отключится питание, то журнала уже не будет, а БД еще не будет целостной — потеря данных!

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

PRAGMA synchronous задает степень «паранойи» SQLite на это счет.

Режим OFF (или 0) означает: SQLite считает, что данные фиксированы на диске сразу после того как он передал их ОС (то есть сразу после вызова соот-го API ОС).

Это означает, что целостность гарантирована при аварии приложения (поскольку ОС продолжает работать), но не при аварии ОС или отключении питания.

Режим синхронизации NORMAL (или 1) гарантирует целостность при авариях ОС и почти при всех отключениях питания. Существует ненулевой шанс, что при потере питания в самый неподходящий момент база испортится. Это некий средний, компромисный режим по производительности и надежности.

Режим FULL гарантирует целостность всегда и везде и при любых авариях. Но работает, разумеется, медленнее, поскольку в определенных местах делаются паузы ожидания. И это режим по умолчанию.

Итак, осталась неохваченной только тема журнала типа WAL.

Режим журнала WAL

По умолчанию, режим журнала БД всегда «возвращается» в DELETE. Допустим, мы открыли соединение к БД и установили режим PERSIST. Изменили данные, закрыли соединение.

На диске остался файл журнала (начало которого забито нулями).

Открываем соединение к БД снова. Если не задать режим журнала в этом соединении, он опять будет работать в DELETE. Как только мы обновим данные, механизм фиксации транзакций сотрет файл журнала.

Режим журнала WAL работает иначе — он «постоянный». Как только мы перевели базу в режим WAL, она останется в этом режиме, пока ей явно не поменяют режим журнала на другой.

Итак, зачем он нужен?

Изначально SQLite проектировалась как встроенная БД. Архитектура разделения одновременного доступа к данным была устроена примитивно: одновременно несколько соединений могут читать БД, а вот записывать в данный момент времени может только одно соединение. Это, как минимум, означает, что пишущее соединение ждет «освобождения» БД от читающих. При попытке записать в «занятую» БД приложение получает ошибку SQLITE_BUSY (не путать с SQLITE_LOCKED!). Достигается этот механизм разделения доступа через API блокировки файлов (которые плохо работают на сетевых дисках, поэтому там не рекомендуется использовать SQLite; узнать больше )

В режиме WAL (Write-Ahead Logging) «читатели» БД и «писатели» в БД уже не мешают друг другу, то есть допускается модификация данных при одновременном чтении. Короче говоря, это шаг в сторону больших и серьезных СУБД, в которых все так и есть. Утверждается также, что SQLite в WAL работает быстрее.

Но есть и недостатки:
— требуется некоторые дополнительные ништяки от ОС (unix и Windows имеют эти ништяки);
— БД занимает несколько файлов (файлы «XXX-wal» и «XXX-shm»);
— плохо работает на больших транзакциях (условно, если транзакция больше 50 Мбайт);
— нельзя открыть такую БД в режиме «только чтение»;
— возникает дополнительная операция checkpoint.

Фактически, в режиме WAL данные БД разделяются между БД и файлом журнала. Операция checkpoint переносит данные в БД. По умолчанию, это делается автоматически, если журнал занял 1000 страниц БД.
То есть, идут быстрые COMMIT-ы и вдруг какой-то COMMIT задумался и начал делать checkpoint. Если такое поведение нежелательно, можно делать checkpoint вручную (когда все спокойно), можно это делать и в отдельном процессе.

Пределы

Несмотря на миниатюрность, SQLite в реальности не накладывает серьезных ограничений на размеры полей, таблиц или БД.

По умолчанию, BLOB или строкое значение могут занимать 1 Гбайт и это же ограничение размера одной записи (можно поднять до 2^31 — 1, параметр SQLITE_MAX_LENGTH).

Количество столбцов: 2000 (можно поднять до 32767, SQLITE_MAX_COLUMN).

Размер SQL оператора: 1 МБайт (1073741824 байт, SQLITE_MAX_SQL_LENGTH).

Одновременный join: 64 таблицы.

Присоединить баз к соединению: 10 (до 62, SQLITE_MAX_ATTACHED)

Максимальное количество страниц в БД: 1073741823 (до 2147483646, SQLITE_MAX_PAGE_COUNT).

Если задать размер страницы 65636 байт, то максимальный размер БД будет примерно 14 Терабайт.

Максимальное число записей в таблице: 2^64 — 1, но на практике, конечно, ограничение размера вступит раньше.

UDP: Ссылки по оптимизации SQLite: 1 2 android-1 android-2

Источник