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.

    Продолжение следует.
    Поделиться публикацией
    Похожие публикации
    AdBlock похитил этот баннер, но баннеры не зубы — отрастут

    Подробнее
    Реклама
    Комментарии 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 можно частично восстановить табличку после сбоя.
                                • +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. Зачем хранить в строковом виде?

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