Пользователь
0,0
рейтинг
10 августа 2012 в 00:08

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

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

Часть 2
Часть 3

Небольшая вводная.

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



Что такое SQLite?

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

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

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

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

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

Страница скачивания: http://sqlite.org/download.html

Для собственной сборки обычно скачивают т.н. «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.

Продолжение следует.
Григорий @Krovosos
карма
125,2
рейтинг 0,0
Реклама помогает поддерживать и развивать наши сервисы

Подробнее
Спецпроект

Самое читаемое Разработка

Комментарии (54)

  • 0
    Добавил в закладки. Жду продолжения :)
  • +8
    покрытие кода тестами 100% (с августа 2009)
    — причем это не просто покрытие тестами, а 100% branch coverage и 100 MC/DC покрытие, и вообще цифры покрытия тут совсем не главные, их можно было гораздо меньшими усилиями достичь, чем в sqlite это сделано.

    Ни в каком другом open-source проекте такой помешанности на тестах не встречал. В sqlite кода тестов в 1000+ раз больше, чем собственно кода проекта, даже с учетом того, что многие тесты написаны на более высокоуровневом Tcl. Всем рекомендую почитать www.sqlite.org/testing.html — душераздирающее чтиво :)
    • 0
      Вот здесь у человека по-русски кратко написано:
    • +2
      Тагоряне. Цивилизация гипертрофированной предусмотрительности. Три четверти всех мощностей направлено у них на изучение вредных последствий, каковые могут проистечь из открытия, изобретения, нового технологического процесса и так далее. Эта цивилизация кажется нам странной только потому, что мы не способны понять, насколько это интересно — предотвращать вредные последствия, какую массу интеллектуального и эмоционального наслаждения это дает. Тормозить прогресс так же увлекательно, как и творить его, — все зависит от исходной установки и от воспитания. В результате транспорт у них только общественный, авиации никакой, зато прекрасно развита проводная связь. © Аркадий и Борис Стругацкие. Волны гасят ветер
  • 0
    Про WAL очень даже интересно было бы почитать. Спасибо за статью.
    • 0
      В реализации WAL есть ошибки, пока не признанные разработчиком. В частности, иногда он растёт больше заданных лимитов размера.
  • +2
    SQlite позволяет занести в этот столбец значения любого типа (999, «abc», «123», 678.525).
    Если вставляемое значение — не целое, то SQlite пытается привести его к целому.

    какой кошмар…

    много встроенных функций
    по ссылке 40 функций, включая перегруженные варианты. Это хорошо или необычно?

    • 0
      В чем кошмар такого подхода с типами?
      По поводу функций, в принципе данного более чем достаточно в повседневной жизни. Если есть необходимость в какой-то специфичной, то можно сделать легко и просто свою функцию, зарегистрировать ее и вызывать уже в SQL запросах, таким образом наращивать функциональность можно столько сколько необходимо.
      Например можно реализовать сортировку сложных структур хранящихся в BLOB полях.
      • 0
        Кошмар как минимум в том, что этот подход сразу больно пинает по производительности
        • 0
          На самом деле на уровне SQLite API можно явно указывать, с каким типом работаешь в каждом поле, в этом случае не потребуется не только автоконвертации, но и вообще конвертации чисел в текст в процессе основной работы (не считая, конечно, вывода итогов пользователю). Это если использовать биндинг полей.
          • 0
            Дело не только в конвертации, а еще и в самом хранении таких полей. По сути мы для каждой ячейки должны хранить некий variant — т. е. идентификатор типа и сами данные, которые при вычитывании конвертить.
            • 0
              Что есть ячейка, поле или запись? Для полей тип данных задан в виде семантической информации, но он никак не влияет на способ хранения, только на то как данные будет представлять редактор и компилятор запросов (т.е. к какому типу приводить на автомате). Таким образом любое BLOB поле можно рассматривать как набор бинарных данных, либо как конкретное значение в зависимости от контекста.

              Например у себя в одном проекте в поле храню все начиная от просто чисел, заканчивая изображениями. При построении точно знаю какие значения в поле будут находится и уже использую явное указание типа. С тем же успехом можно вычитать бинарные данные и скастовать их в то что нужно.
              PS рассуждаю с точки зрения C/C++, в других языках не работал с SQLite.
            • 0
              Да, тип поля, конечно, хранится в БД. Структура файла SQLite хорошо документирована — www.sqlite.org/fileformat.html Типы нужны для 1) конвертации в читабельное состояние при выводе результатов, 2) в SQL-функциях над несовместимыми типами производится конвертация (ну то есть если к строке прибавлять число, то оно конвертируется в число, а не в виде blob'а пристыковывается). Но при операциях над одним типом (наиболее массовые операции — всякие там SUM и прочая арифметика) никакой конвертации, поэтому не ухудшает производительность.
      • 0
        >В чем кошмар такого подхода с типами?

        Кошмар не кошмар, но вызывает иногда трудно диагностируемые глюки. Например, в редакторе БД вставляешь значение, а оно записывается не как 1, а как «1» и потом выборка Where someRow=1 возвращает тебе пустоту.
        • 0
          С записью/чтением никогда не было проблем, работаю через Qt обертку и стараюсь использовать prepared statements.
          Бывает поля делаю с типом BLOB (читай не типизированные), тогда где необходимо явное приведение типа использую уже CAST.
        • +1
          Работаю с SQLite больше 7 лет, никогда с таким не сталкивался. Наверняка это особенность конкретного редактора БД, а не собственно SQLite.
      • 0
        >В чем кошмар такого подхода с типами?
        Сконвертировал/симпортил кривой excel файл и получи на выходи черт пойми какие результаты запросов (все ведь проглотится). Вы в курсе, какие выкрутасы, например, PHP вытворяет при сравнении переменных? Явное, оно, как известно, лучше неявного.
    • 0
      >> по ссылке 40 функций, включая перегруженные варианты. Это хорошо или необычно?

      Это в особенности. Стандарта на функции в SQL, как понимаю, не существует.
  • 0
    Эта статья не для начинающих программистов.
    Статья написана в виде вопросов от гипотетического новичка

    Простите?..
    • +8
      Видимо не для «начинающих программистов в принципе», но для новичка конкретно в SQLite. Т.е., для тех, кто имеет опыт разработки, но без использования SQLite.
      • 0
        Именно. Исправил в статье.
  • +1
    В 2000ом году в качестве халтурки написал биллинг для АТС на 10тыс абонентов, всё это было на Linux, интерфейс через CGI. Тогда использовал Interbase 4 который мог работать без сервера, просто линуешься с библиотекой и она сама открывает файл, ну прямо в точности как SQLlite сегодня. Единственное большое отличие, Interbase реализовывал изоляции транзакций и позволял нескольким процессам писать, использовались средства IPC для синхронизации между несколькими процессами работающими с одной и той же БД, сервера же выделенного при том нет.
  • +2
    Кстати, если вся мощь SQL не особо нужна, а просто ключ-значение, то можно использовать Berkeley DB, который позволяет несколько писателей одновременно, тоже кросс платформенный, на C.
    • +1
      Угу, только Berkeley DB для коммерческого использования нужно покупать. Что как-то не айс для хранения простых «ключ-значение».
      • НЛО прилетело и опубликовало эту надпись здесь
  • 0
    Спасибо. Как раз для меня статья)
    b) можно без труда организовать БД в памяти (это очень удобно и чуть позже расскажу подробнее);

    Вот про это хотелось бы почитать.
  • 0
    Неоднократно приходилось использовать SQLite. Всегда оставался доволен. Правда возникали проблемы с синтаксисом команд, он был несколько отличен от привычного мне MySQL, но решения быстро находились. К сожалению, это было давно и конкретные примеры привести не смогу.
  • +1
    Использовал SQLite в одном своем проекте, напоролся на неприятную вещь. Мне приходилось писать в базу часто и маленькими порциями, происходило все это на панельном PC у которого в качестве диска использовалась Compact Flash, так вот по истечении некоторого времени работы приложения (примерно месяц) слетала файловая система, я достаточно много времени потратил на поиск проблемы, и до сих пор на 100% не уверен что я ее нашел, но после того как нашел описанное ниже и устранил приложение работает уже около полу года, так вот, у Sqlite по умолчанию включено ведение журнала, и журнал этот ведется следующим образом — перед началом транзакции создается файл журнала, производится запись в базу, журнал удаляется, и так при каждой транзакции, у меня это происходило где-то раз в секунду. Как известно для удаления файл просто помечается в таблице размещения что он удален, то-есть производилась операция записи в одно и тоже самое место. На обыкновенном HDD такое поведение не вызвало бы никаких проблем, но на флеше это приводило к порче носителя(колличество циклов записи флеш памяти — 10е6 раз), когда вы пишете на нее данные она пишется циклично и одна и таже ячейка не модифицируется постоянно, в моем же случае происходила именно модификация одной и той же самой ячейки, что приводило к сбою. Вот так вот коварно…
    • 0
      «устранил»
      Что именно вы сделали, подскажите? Отключили журнал? А бэкап прикрутили потом? Или уже был?
      • 0
        Отключил ведение журнала и написал скрипты востановления базы, база Sqlite, если в ней возникли проблемы, может быть считана до того места где эта проблема возникла, меня это устраивало. Бекапить в поле некуда. :)
        • 0
          > база Sqlite, если в ней возникли проблемы, может быть считана до того места где эта проблема возникла,

          Можете чуть подробнее об этом рассказать? очень интересно.
          • 0
            Если SQLite база сбоит (обычные команды открытия/чтения базы дают ошибку SQLITE_CORRUPT), то часто все-равно можно сделать «sqlite .dump» через консоль, получить все данные (в виде команд insert) до сбойного места. Ну как в MySQL в случае myisam можно частично восстановить табличку после сбоя.
            • 0
              Опередили:)
    • +3
      Можно было не отключать ведение журнала, а вести его в памяти:

      PRAGMA journal_mode = MEMORY

      И тем самым сохранить возможность делать ROLLBACK.

      • 0
        Спасибо, попробую.
  • +1
    sqlite — безусловно хорошая штука. Пользовался ей в нескольких проектах, единственное что огорчает — отсутствие враппера для С++ в стандартной поставке, в результате их пишут все кому не лень (да и чего скрывать — я тоже свой писал, т.к. не нашел подходящего под свои требования). А без враппера юзать sqlite не очень удобно, в плюсах разумеется.
    • 0
      Хороших врапперов мало, но я не так давно стал использовать Poco::Data и остался очень доволен. Все в стиле с++, очень хорошо ложится в проект.
  • 0
    в контексте SQLITE меня убивают вещи типа «sqlite-server»
    люди СПЕЦИАЛЬНО делали встраиваимою базу данных
    так надо поверх нее сделать клиент сервера…
    причем с завидным упорством…

    p.s. а уж в где только SQLITE не используется, даже вон в Adobe Light Room
    • 0
      Ну а что удивительного? Есть отлично работающая база и возникает задача одновременного доступа из нескольких приложений.

      И не хочется все бросить и ставить Oracle, если можно придумать как обойтись SQLite ;-)
      • 0
        ага, и как результат — получим недо mySQL/etc
        у этого проекта есть идея — быть локальной базой
        нужны сетевые — их есть
      • 0
        Дык мускуль на что? :-)
      • 0
        На самом деле SQLite3 и так позволяет нормальный параллельный доступ из разных процессов и из разных потоков к одним и тем же БД, не требуется ничего наворачивать сверху, SQLite сам разбирается со всеми блокировками, кэшами и т.п. В частности, это очень активно используется в Eserv (отечественный почтовый и веб сервер), начиная с 4й версии (индексы писем и пр.).
  • 0
    Наиглавнейшее достоинство SQLite — переносимость. За это ей можно было бы простить все что угодно. А уж то что она сама по себе неплоха и вовсе замечательно.
  • 0
    Мне вот единственное что мешает — отсутствие редактируемых вьюх.
    • 0
      Я как-то редко использую view. Может и зря. А зачем их редактировать?
      • 0
        Скажем, в моем случае:
        1. Есть БД. Делается запрос со всякими джойнами и кладется в табличное представление.
        2. В табличном представлении пользователь что-то меняет.
        3. Апдейтится БД.

        Через View пункт 3 делается с пол тычка. Без этого же приходится писать промежуточную модель данных с таблицей и расшифровкой каждого ее столбца — из какой таблицы/столбца БД взяты эти данные. Иначе непонятно, куда записывать изменения.
        • +1
          То есть базы которые update view сразу занесут сами в несколько таблиц? Не знал. Круто.
          • –1
            Как минимум MS умеет это делать msdn.microsoft.com/en-us/library/ms180800.aspx
          • 0
            Как вариант, можно использовать INSTEAD OF триггер для изменения/удаления/вставки и самому реализовать необходимую логику. (применимо для MS/Oracle/PostgreSQL)
  • 0
    Получил очень неприятный опыт. Как оказалось, SQLite некорректно обрабатывает запросы с выборками дат. Выяснилось в самый последний момент.
    • +1
      Рекомендую хранить даты SQLite в строковом формате «YYYY-MM-DD hh:mm:ss», это избавляет от проблем сортировки и выборки.
      • 0
        Обычно даты хранят в UNIX-формате (число секунд) в поясе UTC. Зачем хранить в строковом виде?

Только зарегистрированные пользователи могут оставлять комментарии. Войдите, пожалуйста.