Командная оболочка SQLite: работа с sqlite3.exe

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

---

SQLite предоставляет командную строку sqlite3 (или sqlite3.exe в Windows), позволяющую выполнять SQL-запросы в интерактивном режиме к базе данных SQLite. В этом документе описаны основы работы с программой sqlite3

Программа командной строки SQLite и библиотека SQLite

SQLite — это код, реализующий движок базы данных SQL. Программа командной строки sqlite3 (CLI) принимает ввод пользователя и передает его в библиотеку SQLite для обработки

Этот документ посвящён CLI, а не базовой библиотеке SQLite

По этой теме полезно отдельно посмотреть EXPLAIN QUERY PLAN: план выполнения SQL-запроса в SQLite, чтобы расширить контекст и сравнить подходы

По этой теме полезно отдельно посмотреть Создание Flutter-приложения с SQLite, BLoC и Streams, чтобы расширить контекст и сравнить подходы

Графические альтернативы CLI

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

Запуск CLI

Для запуска sqlite3 введите ‘sqlite3’ в командной строке, при желании указав имя файла базы данных SQLite или ZIP-архива. Если файл не существует, программа создаст новый с указанным именем. В противном случае будет использована временная база данных, которая исчезает при закрытии программы

После запуска sqlite3 вы увидите приветственное сообщение и приглашение для ввода SQL-команд. Вводите команды, завершая их точкой с запятой, и нажимайте ‘Enter’ для выполнения

Например, чтобы создать новую базу данных SQLite с именем «ex1.db» с единственной таблицей «tbl1», можно сделать следующее:

$ sqlite3 ex1.db
SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
sqlite> create table tbl1(one text, two int);
sqlite> insert into tbl1 values('hello!',10),('goodbye',20);
sqlite> select * from tbl1;
┌───────────┬─────┐
│ one │ two │
├───────────┼─────┤
│ 'hello!' │ 10 │
│ 'goodbye' │ 20 │
└───────────┴─────┘
sqlite>

Завершите работу программы sqlite3, введя символ EOF (Ctrl-D), или остановите выполняющийся запрос при помощи Control-C

Не забудьте ставить точку с запятой в конце каждой SQL-команды! Программа sqlite3 требует этого знака, чтобы определить, когда команда завершена. При пропуске точки с запятой вы увидите приглашение продолжения, ожидающее ввода дополнительного текста

Запуск sqlite3.exe в Windows

Пользователи Windows могут запустить sqlite3, дважды щелкнув по файлу sqlite3.exe. Это откроет окно терминала, где запустится SQLite, используя временную базу данных

SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open ex1.db
sqlite>

Пример выше открывает базу данных ‘ex1.db’ и начинает с ней работать. Если файл еще не создан, он будет автоматически создан. Рекомендуется использовать полный путь к файлу, чтобы убедиться, что он находится в правильной директории, например, ‘c:/work/ex1.db’, а не ‘c:\work\ex1.db’

Кроме того, вы можете создать новую базу данных с использованием временного хранилища по умолчанию, а затем сохранить её в файл на диске с помощью команды «.save»:

SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> ... many SQL commands omitted ...
sqlite> .save ex1.db
sqlite>

Будьте осторожны при использовании команды «.save», так как она перезапишет любые существующие файлы баз данных с тем же именем без запроса подтверждения. Как и в случае с командой «.open», для избежания неоднозначности рекомендуется использовать полный путь с прямыми косыми чертами в качестве разделителей каталогов

Запуск CLI в веб-браузере

CLI можно скомпилировать с помощью Emscripten так, чтобы он работал внутри веб-браузера. Поэкспериментировать с последними версиями CLI можно по адресу https://sqlite.org/fiddle. Вкладка веб-браузера — это не компьютер общего назначения, поэтому не все функции CLI доступны в «fiddle». Однако «fiddle» можно использовать как песочницу для проверки SQL-команд

Dot-команды sqlite3: специальные директивы оболочки

В большинстве случаев sqlite3 просто считывает строки SQL-ввода и передаёт их в библиотеку SQLite для выполнения. Однако строки ввода, начинающиеся с точки («.»), перехватываются и интерпретируются самой программой sqlite3. Такие «dot-команды» обычно используются для изменения формата вывода запросов или для выполнения определённых заранее подготовленных операторов запросов

Изначально dot-команд было совсем немного, но за прошедшие годы накопилось множество новых возможностей, и сегодня их насчитывается более 60

Чтобы получить список доступных dot-команд, введите «.help» без аргументов. Или введите «.help TOPIC» для получения подробной информации по теме TOPIC. Список доступных dot-команд для SQLite версии 3.52.0 выглядит следующим образом:

sqlite> .help
.archive ... Manage SQL archives
.auth ON|OFF Show authorizer callbacks
.backup ?DB? FILE Backup DB (default "main") to FILE
.bail on|off Stop after hitting an error. Default OFF
.cd DIRECTORY Change the working directory to DIRECTORY
.changes on|off Show number of rows changed by SQL
.check GLOB Fail if output since .testcase does not match
.clone NEWDB Clone data into NEWDB from the existing database
.connection [close] [#] Open or close an auxiliary database connection
.crlf ?on|off? Whether or not to use \r\n line endings
.databases List names and files of attached databases
.dbconfig ?op? ?val? List or change sqlite3_db_config() options
.dbinfo ?DB? Show status information about the database
.dbtotxt Hex dump of the database file
.dump ?OBJECTS? Render database content as SQL
.echo on|off Turn command echo on or off
.eqp on|off|full|... Enable or disable automatic EXPLAIN QUERY PLAN
.excel Display the output of next command in spreadsheet
.exit ?CODE? Exit this program with return-code CODE
.expert EXPERIMENTAL. Suggest indexes for queries
.explain ?on|off|auto? Change the EXPLAIN formatting mode. Default: auto
.filectrl CMD ... Run various sqlite3_file_control() operations
.fullschema ?--indent? Show schema and the content of sqlite_stat tables
.help ?-all? ?PATTERN? Show help text for PATTERN
.import FILE TABLE Import data from FILE into TABLE
.imposter INDEX TABLE Create imposter table TABLE on index INDEX
.indexes ?TABLE? Show names of indexes
.intck ?STEPS_PER_UNLOCK? Run an incremental integrity check on the db
.limit ?LIMIT? ?VAL? Display or change the value of an SQLITE_LIMIT
.lint OPTIONS Report potential schema issues.
.load FILE ?ENTRY? Load an extension library
.log FILE|on|off Turn logging on or off. FILE can be stderr/stdout
.mode ?MODE? ?OPTIONS? Set output mode
.nonce STRING Suspend safe mode for one command if nonce matches
.nullvalue STRING Use STRING in place of NULL values
.once ?OPTIONS? ?FILE? Output for the next SQL command only to FILE
.open ?OPTIONS? ?FILE? Close existing database and reopen FILE
.output ?FILE? Send output to FILE or stdout if FILE is omitted
.parameter CMD ... Manage SQL parameter bindings
.print STRING... Print literal STRING
.progress N Invoke progress handler after every N opcodes
.prompt MAIN CONTINUE Replace the standard prompts
.quit Stop interpreting input stream, exit if primary.
.read FILE Read input from FILE or command output
.recover Recover as much data as possible from corrupt db.
.restore ?DB? FILE Restore content of DB (default "main") from FILE
.save ?OPTIONS? FILE Write database to FILE (an alias for .backup ...)
.scanstats on|off|est Turn sqlite3_stmt_scanstatus() metrics on or off
.schema ?PATTERN? Show the CREATE statements matching PATTERN
.session ?NAME? CMD ... Create or control sessions
.sha3sum ... Compute a SHA3 hash of database content
.shell CMD ARGS... Run CMD ARGS... in a system shell
.stats ?ARG? Show stats or turn stats on or off
.system CMD ARGS... Run CMD ARGS... in a system shell
.tables ?TABLE? List names of tables matching LIKE pattern TABLE
.timeout MS Try opening locked tables for MS milliseconds
.timer on|off Turn SQL timer on or off
.trace ?OPTIONS? Output each SQL statement as it is run
.unmodule NAME ... Unregister virtual table modules
.version Show source, library and compiler versions
.vfsinfo ?AUX? Information about the top-level VFS
.vfslist List all available VFSes
.vfsname ?AUX? Print the name of the VFS stack
.www Display output of the next command in web browser
sqlite>

Помимо dot-команд, отображаемых командой «.help», существуют недокументированные команды, используемые для тестирования, а также устаревшие команды, сохранённые для обратной совместимости

Большинство dot-команд можно сокращать. Например, «.q» является распространённым сокращением для «.quit»

Правила для dot-команд, SQL и не только

Структура строк

Ввод CLI представляет собой смешанную последовательность из:

• SQL-операторов
• dot-команд
• комментариев CLI

SQL-операторы имеют свободную форму и могут занимать несколько строк, с пробельными символами или SQL-комментариями в любом месте. Они завершаются либо символом «;» в конце строки ввода, либо символом «/» или словом «go» на отдельной строке. Когда символ «;» не находится в конце строки ввода, он служит для разделения SQL-операторов. Завершающие пробельные символы игнорируются при определении конца оператора

Dot-команды имеют специфический синтаксис:

• Dot-команды должны начинаться с «.» у левого края строки без предшествующих пробельных символов.
• Dot-команды должны полностью умещаться в одной строке ввода.
• Dot-команды не могут встречаться в середине обычного SQL-оператора. Иными словами, dot-команды не могут появляться в строке продолжения ввода.
• Для dot-команд не предусмотрен синтаксис комментариев.
• Одиночные (без кавычек) точки с запятой в конце dot-команд игнорируются (начиная с версии 3.52.0 и выше).

CLI также принимает однострочные комментарии, начинающиеся с символа «#» и продолжающиеся до конца строки. Перед начальным символом «#» не должно быть пробельных символов

Аргументы dot-команд

За dot-командами может следовать ноль или более аргументов, разделённых пробелами. Аргументы разбираются по следующим правилам:

• Завершающие пробельные символы и финальный «;» удаляются, если они присутствуют.
• Аргументы отделяются друг от друга и от самой dot-команды пробельными символами.
• Текст внутри «’…’» рассматривается как единый аргумент с удалёнными разделителями «’», даже если текст содержит пробельные символы.
• Текст внутри «»…»» рассматривается как единый аргумент с удалёнными разделителями «»».
• Экранирование в стиле C с обратной косой чертой (например: \\, \n, \r, \", \033 и т. д.) работает только внутри аргументов в двойных кавычках.

Dot-команды выполняются отдельно

Dot-команды интерпретируются программой командной строки sqlite3.exe, а не библиотекой SQLite. Поэтому dot-команды не будут работать в качестве аргумента для интерфейсов основной библиотеки SQLite, таких как sqlite3_prepare() или sqlite3_exec()

Форматы вывода

CLI может отображать результаты SQL-запросов в самых разнообразных форматах. Команда .mode используется для управления форматированием результатов запросов. Подробности о работе команды .mode весьма обширны и поэтому рассматриваются в отдельном документе

Не вдаваясь в излишние подробности, приведу несколько быстрых примеров, демонстрирующих работу команды .mode:

.mode box Показывает результаты запроса в виде сетки, сформированной с использованием символов рисования рамок Unicode.
.mode quote Показывает результаты запроса в виде строк SQL-литералов, разделённых запятыми, по одной строке на каждую строку вывода.
.mode csv Показывает результаты запроса в формате CSV («значения, разделённые запятыми»).
.mode --list Показывает список доступных режимов вывода.
.mode Показывает текущий режим вывода.
.mode --once box Показывает следующий SQL-оператор в режиме "box", а затем автоматически возвращается к текущему режиму.

Запрос схемы базы данных

Программа sqlite3 предоставляет несколько удобных команд, полезных для просмотра схемы базы данных. Всё, что делают эти команды, можно выполнить и другими способами. Данные команды предоставляются исключительно как сокращение

Например, чтобы увидеть список таблиц в базе данных, можно ввести «.tables»:

sqlite> .tables
tbl1 tbl2
sqlite>

Команда «.tables» аналогична установке режима списка и выполнению следующего запроса:

SELECT name FROM sqlite_schema
WHERE type IN ('table','view')
AND name NOT LIKE 'sqlite_%'
ORDER BY 1

Однако команда «.tables» делает больше. Она запрашивает таблицу sqlite_schema для всех подключённых баз данных, а не только для основной. Кроме того, она упорядочивает вывод в аккуратные столбцы

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

Команда «.schema» показывает полную схему базы данных или схему отдельной таблицы, если указан необязательный аргумент с именем таблицы:

sqlite> .schema
create table tbl1(one varchar(10), two smallint)
CREATE TABLE tbl2 ( f1 varchar(30) primary key, f2 text, f3 real
);
sqlite> .schema tbl2
CREATE TABLE tbl2 ( f1 varchar(30) primary key, f2 text, f3 real
);
sqlite>

Команда «.schema» примерно эквивалентна установке режима списка и выполнению следующего запроса:

SELECT sql FROM sqlite_schema ORDER BY tbl_name, type DESC, name

Как и «.tables», команда «.schema» показывает схему для всех подключённых баз данных. Если нужно увидеть схему только одной базы данных (например, «main»), можно добавить аргумент к «.schema», чтобы ограничить вывод:

sqlite> .schema main.*

Команду «.schema» можно дополнить параметром «—indent», при использовании которого она пытается переформатировать различные операторы CREATE в схеме, чтобы они были более удобочитаемы для человека

Команда «.databases» показывает список всех баз данных, открытых в текущем соединении. Их всегда будет не менее двух. Первая — «main», исходная открытая база данных. Вторая — «temp», база данных, используемая для временных таблиц. Могут быть перечислены дополнительные базы данных, подключённые с помощью оператора ATTACH

Первый столбец вывода — имя, с которым подключена база данных, второй столбец результата — имя файла внешнего файла. Может присутствовать третий столбец результата со значением «r/o» или «r/w» в зависимости от того, открыт ли файл базы данных только для чтения или для чтения и записи. Также может быть четвёртый столбец результата, показывающий результат sqlite3_txn_state() для данного файла базы данных

Dot-команда «.fullschema» работает как команда «.schema» в том смысле, что отображает полную схему базы данных. Но «.fullschema» также включает дампы таблиц статистики «sqlite_stat1», «sqlite_stat3» и «sqlite_stat4», если они существуют. Команда «.fullschema» обычно предоставляет всю информацию, необходимую для точного воспроизведения плана запроса для конкретного запроса

При сообщении о предполагаемых проблемах с планировщиком запросов SQLite разработчиков просят предоставить полный вывод «.fullschema» в составе отчёта об ошибке. Обратите внимание, что таблицы sqlite_stat3 и sqlite_stat4 содержат образцы записей индексов и поэтому могут содержать конфиденциальные данные, поэтому не отправляйте вывод «.fullschema» проприетарной базы данных по общедоступному каналу

Открытие файлов базы данных

Команда «.open» открывает новое соединение с базой данных, предварительно закрыв ранее открытое соединение. В простейшей форме команда «.open» просто вызывает sqlite3_open() для файла, указанного в качестве аргумента. Используйте имя «:memory:», чтобы открыть новую базу данных в памяти, которая исчезнет при выходе из CLI или при повторном запуске команды «.open»

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

Если в команду «.open» включён параметр --new, база данных сбрасывается перед открытием. Все предыдущие данные уничтожаются. Это деструктивная перезапись предыдущих данных, и подтверждение не запрашивается, поэтому используйте этот параметр осторожно

Если включён параметр --ifexists, команда «.open» работает только в том случае, если файл базы данных уже существует. Иными словами, --ifexists предотвращает создание новой пустой базы данных

Параметр --readonly открывает базу данных в режиме только для чтения. Запись будет запрещена

Параметр --deserialize приводит к тому, что всё содержимое файла на диске считывается в память и затем открывается как база данных в памяти с использованием интерфейса sqlite3_deserialize(). Это, конечно, потребует много памяти при наличии большой базы данных. Кроме того, любые изменения, внесённые в базу данных, не будут сохранены обратно на диск, если только вы явно не сохраните их с помощью команд «.save» или «.backup»

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

Параметр --zip приводит к тому, что указанный входной файл интерпретируется как ZIP-архив, а не как файл базы данных SQLite

Параметр --hexdb приводит к тому, что содержимое базы данных считывается из последующих строк ввода в шестнадцатеричном формате, а не из отдельного файла на диске. Для генерации соответствующего текста для базы данных можно использовать dot-команду «.dbtotxt» и/или инструмент командной строки dbtotxt. Параметр --hexdb предназначен для использования разработчиками SQLite в целях тестирования

Каких-либо вариантов использования этого параметра за пределами внутреннего тестирования и разработки SQLite не известно

Перенаправление ввода/вывода

Запись результатов в файл

По умолчанию sqlite3 отправляет результаты запросов на стандартный вывод. Вы можете изменить это с помощью команд «.output» и «.once». Просто укажите имя выходного файла в качестве аргумента команды .output, и все последующие результаты запросов будут записаны в этот файл

Или используйте команду .once вместо .output, и вывод будет перенаправлен только для одной следующей команды, после чего вернётся на консоль. Используйте .output без аргументов, чтобы снова начать запись на стандартный вывод. Например:

sqlite> .mode list --colsep "|"
sqlite> .output test_file_1.txt
sqlite> select * from tbl1;
sqlite> .exit
$ cat test_file_1.txt
hello|10
goodbye|20
$

Если первый символ имени файла в командах «.output» или «.once» является символом вертикальной черты («|»), то оставшиеся символы воспринимаются как команда, и вывод отправляется в эту команду. Это позволяет легко передавать результаты запроса в другой процесс. Например, команда «open -f» на Mac открывает текстовый редактор для отображения содержимого, которое она читает со стандартного ввода

Таким образом, чтобы увидеть результаты запроса в текстовом редакторе, можно ввести:

sqlite> .once | open -f
sqlite> SELECT * FROM bigTable;

Если команды «.output» или «.once» имеют аргумент «-e», то вывод собирается во временный файл, и системный текстовый редактор вызывается для работы с этим файлом. Таким образом, команда «.once -e» достигает того же результата, что и «.once ‘|open -f’», но с преимуществом переносимости на все системы

Если команды «.output» или «.once» имеют аргумент «-x», это заставляет их накапливать вывод в формате значений, разделённых запятыми (CSV), во временном файле, а затем вызывать стандартную системную утилиту для просмотра CSV-файлов (обычно программу для работы с электронными таблицами) с полученным результатом. Это быстрый способ отправить результат запроса в электронную таблицу для удобного просмотра:

sqlite> .once -x
sqlite> SELECT * FROM bigTable;

Команда «.excel» является псевдонимом для «.once -x». Она делает ровно то же самое

Параметр «-w» для команд «.output» или «.once» вызывает отображение вывода в вашем веб-браузере. Команда «.www» является псевдонимом для «.once -w». Обычно данные, отображаемые в веб-браузере, представлены в виде HTML-таблицы, но вместо этого можно отобразить их в виде обычного текста, добавив аргумент «—plain»

Чтение SQL из файла

В интерактивном режиме sqlite3 читает входной текст (SQL-операторы или dot-команды) с клавиатуры. Конечно, вы также можете перенаправить ввод из файла при запуске sqlite3, но тогда у вас не будет возможности взаимодействовать с программой. Иногда полезно выполнить SQL-скрипт, содержащийся в файле, одновременно вводя другие команды из командной строки. Для этого предусмотрена dot-команда «.read»

Команда «.read» принимает один аргумент, которым (как правило) является имя файла, из которого следует читать входной текст:

sqlite> .read myscript.sql

Команда «.read» временно прекращает чтение с клавиатуры и вместо этого получает ввод из указанного файла. По достижении конца файла ввод возвращается обратно на клавиатуру. Файл скрипта может содержать dot-команды, как и обычный интерактивный ввод

Если аргумент команды «.read» начинается с символа «|», то вместо открытия аргумента как файла выполняется аргумент (без ведущего символа «|») как команда, а затем вывод этой команды используется в качестве входных данных. Таким образом, если у вас есть скрипт, генерирующий SQL, вы можете выполнить этот SQL напрямую с помощью команды, аналогичной следующей:

sqlite> .read |myscript.sh

Функции файлового ввода-вывода

Командная оболочка добавляет две определяемые приложением SQL-функции, которые обеспечивают соответственно чтение содержимого из файла в столбец таблицы и запись содержимого столбца в файл

SQL-функция readfile(X) читает всё содержимое файла с именем X и возвращает это содержимое в виде BLOB (бинарного объекта, Binary Large Object). Её можно использовать для загрузки содержимого в таблицу. Например:

sqlite> CREATE TABLE images(name TEXT, type TEXT, img BLOB);
sqlite> INSERT INTO images(name,type,img) ...> VALUES('icon','jpeg',readfile('icon.jpg'));

SQL-функция writefile(X,Y) записывает blob Y в файл с именем X и возвращает количество записанных байт. Используйте эту функцию для извлечения содержимого отдельного столбца таблицы в файл. Например:

sqlite> SELECT writefile('icon.jpg',img) FROM images WHERE name='icon';

Обратите внимание, что функции readfile(X) и writefile(X,Y) являются функциями-расширениями и не встроены в основную библиотеку SQLite. Эти процедуры доступны в виде загружаемого расширения в исходном файле ext/misc/fileio.c в репозиториях исходного кода SQLite

SQL-функция edit()

CLI имеет ещё одну встроенную SQL-функцию с именем edit(). Функция edit() принимает один или два аргумента. Первый аргумент — это значение, зачастую большая многострочная строка, которую необходимо отредактировать. Второй аргумент — это вызов текстового редактора. (Он может включать параметры, влияющие на поведение редактора.) Если второй аргумент опущен, используется переменная окружения VISUAL

Функция edit() записывает свой первый аргумент во временный файл, вызывает редактор для работы с этим временным файлом, после завершения работы редактора повторно считывает файл в память, а затем возвращает отредактированный текст

Функцию edit() можно использовать для внесения изменений в большие текстовые значения. Например:

sqlite> UPDATE docs SET body=edit(body) WHERE name='report-15';

В этом примере содержимое поля docs.body для записи, где docs.name равно «report-15», будет отправлено в редактор. После завершения работы редактора результат будет записан обратно в поле docs.body

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

sqlite> UPDATE pics SET img=edit(img,'gimp') WHERE id='pic-1542';

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

sqlite> SELECT edit(img,'gimp') FROM pics WHERE id='pic-1542';

Импорт файлов в формате CSV или других форматах

Используйте команду «.import» для импорта данных в формате CSV (значения, разделённые запятыми, Comma-Separated Values) или аналогично разделённых данных в таблицу SQLite. Команда «.import» принимает два аргумента: источник, из которого будут считаны данные, и имя таблицы SQLite, в которую данные будут вставлены

Аргумент источника — это имя файла для чтения или, если он начинается с символа «|», команда, которая будет выполнена для получения входных данных

Обратите внимание, что перед выполнением команды «.import» может быть важно установить «mode» (режим). Это позволяет предотвратить попытку командной строки интерпретировать текст входного файла в формате, отличном от того, в котором файл структурирован. Если используются параметры --csv или --ascii, они управляют разделителями при импорте. В противном случае используются разделители, действующие для текущего режима вывода

Для импорта в таблицу, не находящуюся в схеме «main», можно использовать параметр --schema, чтобы указать, что таблица находится в другой схеме. Это может быть полезно для баз данных, подключённых через ATTACH, или для импорта во временную таблицу TEMP

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

Если целевая таблица уже существует, каждая строка входных данных, включая первую, считается фактическим содержимым данных. Если входной файл содержит начальную строку с метками столбцов, можно заставить команду .import пропустить эту начальную строку с помощью параметра «—skip 1»

Ниже приведён пример использования: загрузка уже существующей временной таблицы из файла CSV, в первой строке которого содержатся имена столбцов:

sqlite> .import --csv --skip 1 --schema temp C:/work/somedata.csv tab1

При чтении входных данных в режимах, отличных от «ascii», команда «.import» интерпретирует входные данные как записи, состоящие из полей в соответствии со спецификацией RFC 4180, за следующим исключением: разделители записей и полей задаются параметрами --rowsep и --colsep команды .mode

К полям всегда применяется удаление кавычек для отмены экранирования, выполненного согласно RFC 4180, за исключением режима --ascii. Для импорта данных с произвольными разделителями и без кавычек используйте параметр --ascii вместе с параметрами --colsep и --rowsep для определения разделителей

Экспорт в CSV

Чтобы экспортировать таблицу SQLite (или её часть) в формате CSV, просто установите «mode» в значение «csv» и выполните запрос для извлечения нужных строк таблицы. Вывод будет отформатирован как CSV в соответствии с RFC 4180

sqlite> .mode csv --titles on
sqlite> .once c:/work/dataout.csv
sqlite> SELECT * FROM tab1;
sqlite> .system c:/work/dataout.csv

В приведённом выше примере параметр «—titles on» приводит к тому, что метки столбцов выводятся в первой строке вывода. Это означает, что первая строка результирующего файла CSV будет содержать метки столбцов. Если метки столбцов не нужны, используйте вместо этого «—titles off». (Параметр «—titles off» является значением по умолчанию и может быть опущен, если заголовки ранее не были включены.)

Строка «.once FILENAME» направляет весь вывод запросов в указанный файл вместо вывода на консоль. В приведённом выше примере эта строка записывает содержимое CSV в файл с именем «C:/work/dataout.csv»

Последняя строка примера («.system c:/work/dataout.csv») имеет тот же эффект, что и двойной щелчок по файлу c:/work/dataout.csv в Windows. Обычно это открывает программу для работы с электронными таблицами для отображения файла CSV

Эта команда работает в том виде, как написана, только в Windows. Эквивалентная строка на Mac будет выглядеть так:

sqlite> .system open dataout.csv

В Linux и других Unix-системах потребуется ввести что-то вроде:

sqlite> .system xdg-open dataout.csv

Экспорт в Excel

Для упрощения экспорта в электронную таблицу CLI предоставляет команду «.excel», которая захватывает вывод одного запроса и отправляет его в программу для работы с электронными таблицами по умолчанию на хост-компьютере. Используйте её следующим образом:

sqlite> .excel
sqlite> SELECT * FROM tab;

Приведённая выше команда записывает вывод запроса в формате CSV во временный файл, вызывает обработчик по умолчанию для файлов CSV (обычно предпочтительная программа для работы с электронными таблицами, например Excel или LibreOffice), затем удаляет временный файл. По существу, это сокращённый способ выполнения последовательности команд «.csv», «.once» и «.system», описанных выше

Команда «.excel» фактически является псевдонимом для «.once -x». Параметр -x команды .once заставляет её записывать результаты в формате CSV во временный файл с суффиксом «.csv», а затем вызывать обработчик системы по умолчанию для файлов CSV

Существует также команда «.once -e», которая работает аналогично, за исключением того, что она присваивает временному файлу суффикс «.txt», чтобы вместо программы для работы с электронными таблицами был вызван текстовый редактор по умолчанию для системы

Экспорт в TSV (значения, разделённые табуляцией)

Экспорт в чистый TSV (Tab-Separated Values, значения, разделённые табуляцией) без кавычек полей можно выполнить, введя «.mode tabs» перед выполнением запроса. Однако вывод не будет корректно прочитан в режиме tabs командой «.import», если он содержит символы двойных кавычек. Чтобы получить TSV с кавычками согласно RFC 4180, который можно использовать в качестве входных данных в режиме tabs с помощью «.import», выполните:

sqlite> .mode csv --colsep "\t"
sqlite> SELECT * FROM tab;

Доступ к ZIP-архивам как к файлам баз данных

Помимо чтения и записи файлов баз данных SQLite, программа sqlite3 также читает и записывает ZIP-архивы. Просто укажите имя файла ZIP-архива вместо имени файла базы данных SQLite в начальной командной строке или в команде «.open», и sqlite3 автоматически определит, что файл является ZIP-архивом, а не базой данных SQLite, и откроет его соответствующим образом. Это работает независимо от расширения файла

Таким образом, можно открывать файлы JAR, DOCX и ODP, а также любые другие форматы файлов, которые по сути являются ZIP-архивами, и SQLite прочитает их

ZIP-архив представляется как база данных, содержащая единственную таблицу со следующей схемой:

CREATE TABLE zip( name, -- Имя файла mode, -- Права доступа в стиле Unix mtime, -- Временная метка, секунды с 1970 года sz, -- Размер файла после распаковки rawdata, -- Необработанные сжатые данные файла data, -- Несжатое содержимое файла method -- Код метода сжатия ZIP
);

Так, например, если вы хотите увидеть эффективность сжатия (выраженную как размер сжатого содержимого относительно исходного несжатого размера файла) для всех файлов в ZIP-архиве, отсортированных от наиболее сжатых к наименее сжатым, можно выполнить следующий запрос:

sqlite> SELECT name, (100.0*length(rawdata))/sz FROM zip ORDER BY 2;

Или с помощью функций файлового ввода-вывода можно извлекать элементы ZIP-архива:

sqlite> SELECT writefile(name,data) FROM zip WHERE name LIKE '%.txt';

Как реализован доступ к ZIP-архивам

Командная строка использует виртуальную таблицу Zipfile для доступа к ZIP-архивам. Это можно увидеть, выполнив команду «.schema», когда открыт ZIP-архив:

sqlite> .schema
CREATE VIRTUAL TABLE zip USING zipfile('document.docx')
/* zip(name,mode,mtime,sz,rawdata,data,method) */;

При открытии файла, если клиент командной строки обнаруживает, что файл является ZIP-архивом, а не базой данных SQLite, он фактически открывает базу данных в памяти, а затем в этой базе данных создаёт экземпляр виртуальной таблицы Zipfile, привязанной к ZIP-архиву

Специальная обработка при открытии ZIP-архивов — это особенность командной строки, а не основной библиотеки SQLite. Поэтому если вы хотите открыть ZIP-архив как базу данных в своём приложении, вам потребуется активировать модуль виртуальной таблицы Zipfile, а затем выполнить соответствующий оператор CREATE VIRTUAL TABLE

Преобразование всей базы данных в текстовый файл

Используйте команду «.dump» для преобразования всего содержимого базы данных в единый текстовый файл в кодировке UTF-8. Этот файл можно преобразовать обратно в базу данных, передав его на вход sqlite3

Хороший способ создать архивную копию базы данных выглядит так:

$ sqlite3 ex1 .dump | gzip -c >ex1.dump.gz

Это создаёт файл с именем ex1.dump.gz, содержащий всё необходимое для восстановления базы данных в будущем или на другой машине. Чтобы восстановить базу данных, просто введите:

$ zcat ex1.dump.gz | sqlite3 ex2

Текстовый формат представляет собой чистый SQL, поэтому команду .dump также можно использовать для экспорта базы данных SQLite в другие популярные СУБД. Например, вот так:

$ sqlite3 ex1 .dump | psql -d ex1 -U postgres

Восстановление данных из повреждённой базы данных

Как и команда «.dump», команда «.recover» пытается преобразовать всё содержимое файла базы данных в текст. Разница в том, что вместо чтения данных через обычный интерфейс SQL-базы данных, «.recover» пытается восстановить базу данных на основе данных, извлечённых непосредственно из максимально возможного числа страниц базы данных

Если база данных повреждена, «.recover» обычно способна восстановить данные из всех неповреждённых частей, тогда как «.dump» останавливается при первом признаке повреждения

Если команда «.recover» восстанавливает одну или несколько строк, которые невозможно отнести ни к одной таблице базы данных, выходной скрипт создаёт таблицу «lost_and_found» для хранения осиротевших строк. Схема таблицы lost_and_found выглядит следующим образом:

CREATE TABLE lost_and_found( rootpgno INTEGER, -- корневая страница дерева, частью которого является pgno pgno INTEGER, -- номер страницы, на которой найдена строка nfield INTEGER, -- количество полей в строке id INTEGER, -- значение поля rowid или NULL c0, c1, c2, c3... -- столбцы для полей строки
);

Таблица «lost_and_found» содержит по одной строке для каждой осиротевшей строки, восстановленной из базы данных. Кроме того, в ней есть по одной строке для каждой восстановленной записи индекса, которую невозможно отнести ни к одному SQL-индексу. Это связано с тем, что в базе данных SQLite один и тот же формат используется для хранения записей SQL-индексов и записей таблиц WITHOUT ROWID

Если восстановленная схема базы данных уже содержит таблицу с именем «lost_and_found», команда «.recover» использует имя «lost_and_found0». Если имя «lost_and_found0» также уже занято — «lost_and_found1», и так далее. Имя по умолчанию «lost_and_found» можно переопределить, вызвав «.recover» с ключом --lost-and-found. Например, чтобы выходной скрипт называл таблицу «orphaned_rows»:

sqlite> .recover --lost-and-found orphaned_rows

Загрузка расширений

Вы можете добавлять новые пользовательские SQL-функции, последовательности сортировки, виртуальные таблицы и VFS (виртуальная файловая система, Virtual File System) в командную строку во время выполнения с помощью команды «.load». Сначала соберите расширение как DLL или разделяемую библиотеку (как описано в документе Run-Time Loadable Extensions), затем введите:

sqlite> .load /path/to/my_extension

Обратите внимание, что SQLite автоматически добавляет соответствующий суффикс расширения («.dll» в Windows, «.dylib» на Mac, «.so» в большинстве других Unix-систем) к имени файла расширения. Как правило, рекомендуется указывать полный путь к расширению

SQLite вычисляет точку входа для расширения на основе имени файла расширения. Чтобы переопределить этот выбор, просто добавьте имя точки входа вторым аргументом команды «.load»

Исходный код нескольких полезных расширений можно найти в подкаталоге ext/misc дерева исходных кодов SQLite. Вы можете использовать эти расширения как есть или как основу для создания собственных расширений под свои конкретные нужды

Криптографические хэши содержимого базы данных

Dot-команда «.sha3sum» вычисляет SHA3-хэш содержимого базы данных. Важно уточнить: хэш вычисляется по содержимому базы данных, а не по её представлению на диске. Это означает, например, что VACUUM или аналогичные преобразования, сохраняющие данные, не изменяют хэш

Команда «.sha3sum» поддерживает параметры «—sha3-224», «—sha3-256», «—sha3-384» и «—sha3-512» для выбора варианта SHA3, используемого при вычислении хэша. По умолчанию используется SHA3-256

Схема базы данных (в таблице sqlite_schema) обычно не включается в хэш, но может быть добавлена с помощью параметра «—schema»

Команда «.sha3sum» принимает один необязательный аргумент — шаблон LIKE. Если этот параметр указан, хэшируются только таблицы, имена которых соответствуют шаблону LIKE

Команда «.sha3sum» реализована с помощью функции расширения «sha3_query()», которая входит в состав командной строки

Самотестирование содержимого базы данных

Команда «.selftest» пытается проверить целостность базы данных и отсутствие повреждений. Команда .selftest ищет в схеме таблицу с именем «selftest», определённую следующим образом:

CREATE TABLE selftest( tno INTEGER PRIMARY KEY, -- Номер теста op TEXT, -- 'run' или 'memo' cmd TEXT, -- SQL-команда для выполнения или текст "memo" ans TEXT -- Ожидаемый результат SQL-команды
);

Команда .selftest читает строки таблицы selftest в порядке selftest.tno. Для каждой строки «memo» она выводит текст из «cmd» в вывод. Для каждой строки «run» она выполняет текст «cmd» как SQL, сравнивает результат со значением в «ans» и выводит сообщение об ошибке, если результаты различаются

Если таблица selftest отсутствует, команда «.selftest» выполняет PRAGMA integrity_check

Команда «.selftest —init» создаёт таблицу selftest, если она ещё не существует, а затем добавляет записи, проверяющие SHA3-хэш содержимого всех таблиц. При последующих запусках «.selftest» будет проверять, что база данных не была изменена никаким образом. Чтобы сгенерировать тесты для проверки неизменности подмножества таблиц, просто выполните «.selftest —init», а затем удалите строки selftest, ссылающиеся на таблицы, которые не являются постоянными

Поддержка формата SQLite Archive

Dot-команда «.archive» и параметр командной строки «-A» обеспечивают встроенную поддержку формата SQLite Archive. Интерфейс похож на команду «tar» в unix-системах. Каждый вызов команды «.ar» должен содержать единственный параметр-команду. Для «.archive» доступны следующие команды:

Параметр	Описание
--create / -c	Создать новый архив
--extract / -x	Извлечь файлы из архива
--list / -t	Вывести содержимое архива
--update / -u	Добавить или обновить файлы в архиве
--insert / -i	Вставить файлы в архив
--remove / -r	Удалить файлы из архива

Помимо параметра-команды, каждый вызов «.ar» может содержать один или несколько модификаторов. Некоторые модификаторы требуют аргумента, некоторые — нет. Доступны следующие модификаторы:

Модификатор	Описание
--file FILE / -f FILE	Использовать FILE в качестве архива
--directory DIR / -C DIR	Извлекать файлы в каталог DIR
--append / -a	Работать с appendvfs
--dryrun / -n	Показать SQL без выполнения
--glob / -g	Интерпретировать аргументы как шаблоны glob
--verbose / -v	Подробный вывод

При использовании в командной строке краткие параметры-команды указываются сразу после «-A» без пробела. Все последующие аргументы считаются частью команды .archive. Например, следующие команды эквивалентны:

sqlite3 new_archive.db -Acv file1 file2 file3
sqlite3 new_archive.db ".ar -cv file1 file2 file3"

Длинные и краткие параметры можно смешивать. Например, следующие команды эквивалентны:

-- Два способа создать новый архив "new_archive.db", содержащий
-- файлы "file1", "file2" и "file3".
.ar -c --file new_archive.db file1 file2 file3
.ar -f new_archive.db --create file1 file2 file3

Кроме того, первый аргумент после «.ar» может быть конкатенацией кратких форм всех необходимых параметров (без символов «-»). В этом случае аргументы для параметров, которые их требуют, считываются из командной строки следующими, а оставшиеся слова считаются аргументами команды

Команда создания SQLite Archive

Создаёт новый архив, перезаписывая любой существующий архив (либо в текущей базе данных «main», либо в файле, указанном параметром --file). Каждый аргумент после параметров — это файл, добавляемый в архив. Каталоги импортируются рекурсивно. Примеры см. выше

Команда извлечения SQLite Archive

Извлекает файлы из архива (либо в текущий рабочий каталог, либо в каталог, указанный параметром --directory). Извлекаются файлы и каталоги, имена которых совпадают с аргументами с учётом параметра --glob. Если после параметров не указаны аргументы, извлекаются все файлы и каталоги. Указанные каталоги извлекаются рекурсивно. Если какие-либо указанные имена или шаблоны совпадений не найдены в архиве, возникает ошибка

Команда просмотра содержимого SQLite Archive

Выводит содержимое архива. Если аргументы не указаны, перечисляются все файлы. В противном случае перечисляются только те, которые совпадают с аргументами с учётом параметра --glob. В настоящее время параметр --verbose не изменяет поведение этой команды. В будущем это может измениться

Команды вставки и обновления SQLite Archive

Команды --update и --insert работают аналогично команде --create, за исключением того, что они не удаляют текущий архив перед началом работы. Новые версии файлов молча заменяют существующие файлы с теми же именами, однако исходное содержимое архива (если оно есть) в остальном остаётся нетронутым

Для команды --insert все перечисленные файлы вставляются в архив. Для команды --update файлы вставляются только в том случае, если они ранее не существовали в архиве или если их «mtime» или «mode» отличаются от текущих значений в архиве

Примечание о совместимости: до версии SQLite 3.28.0 (2019-04-16) поддерживался только параметр --update, однако он работал как --insert — то есть всегда повторно вставлял каждый файл независимо от того, изменился ли он

Команда удаления SQLite Archive

Команда --remove удаляет файлы и каталоги, совпадающие с указанными аргументами (если они есть) с учётом параметра --glob. Указание аргументов, которые ни с чем не совпадают в архиве, является ошибкой

Операции с ZIP-архивами

Если FILE является ZIP-архивом, а не SQLite Archive, команда «.archive» и параметр командной строки «-A» всё равно работают. Это реализуется с помощью расширения zipfile. Таким образом, следующие команды примерно эквивалентны и отличаются только форматированием вывода:

sqlite3 file.zip -Atv
sqlite3 file.zip "SELECT name FROM zip;"

SQL, используемый для реализации операций SQLite Archive

Различные команды SQLite Archive реализованы с помощью SQL-операторов. Разработчики приложений могут легко добавить поддержку чтения и записи SQLite Archive в собственные проекты, выполняя соответствующий SQL

Чтобы увидеть, какие SQL-операторы используются для реализации операции SQLite Archive, добавьте параметр --dryrun или -n. Это приводит к отображению SQL без его выполнения

SQL-операторы, используемые для реализации операций SQLite Archive, задействуют различные загружаемые расширения. Все эти расширения доступны в дереве исходного кода SQLite в подпапке ext/misc/. Расширения, необходимые для полной поддержки SQLite Archive, включают:

fileio.c — Это расширение добавляет SQL-функции readfile() и writefile() для чтения и записи содержимого файлов на диске. Расширение fileio.c также включает табличную функцию fsdir() для вывода содержимого каталога и функцию lsmode() для преобразования числовых целых значений st_mode из системного вызова stat() в удобочитаемые строки по образцу команды «ls -l»

sqlar.c — Это расширение добавляет функции sqlar_compress() и sqlar_uncompress(), необходимые для сжатия и распаковки содержимого файлов при вставке и извлечении из SQLite Archive

zipfile.c — Это расширение реализует табличную функцию «zipfile(FILE)», используемую для чтения ZIP-архивов. Это расширение необходимо только при чтении ZIP-архивов вместо SQLite-архивов

appendvfs.c — Это расширение реализует новый VFS, позволяющий добавлять базу данных SQLite к другому файлу, например к исполняемому файлу. Это расширение необходимо только в том случае, если используется параметр --append команды .archive

SQL-параметры

SQLite позволяет использовать связанные параметры в SQL-выражении в любом месте, где допустимо литеральное значение. Значения этих параметров задаются с помощью семейства API sqlite3_bind_…()

Параметры могут быть именованными или безымянными. Безымянный параметр — это одиночный знак вопроса («?»). Именованные параметры — это «?» с непосредственно следующим за ним числом (например: «?15» или «?123») или один из символов «$», «:», «@», за которым следует буквенно-цифровое имя (например: «$var1», «:xyz», «@bingo»)

Эта командная оболочка оставляет безымянные параметры несвязанными, то есть они будут иметь значение SQL NULL, однако именованным параметрам могут быть присвоены значения. Если существует временная таблица TEMP с именем «sqlite_parameters» со схемой следующего вида:

CREATE TEMP TABLE sqlite_parameters( key TEXT PRIMARY KEY, value
) WITHOUT ROWID;

И если в этой таблице есть запись, в которой столбец key точно совпадает с именем параметра (включая начальный символ «?», «$», «:», или «@»), то параметру присваивается значение столбца value. Если такой записи нет, параметр по умолчанию принимает значение NULL

Команда «.parameter» предназначена для упрощения управления этой таблицей. Команда «.parameter init» (часто сокращаемая до «.param init») создаёт таблицу temp.sqlite_parameters, если она ещё не существует. Команда «.param list» отображает все записи в таблице temp.sqlite_parameters. Команда «.param clear» удаляет таблицу temp.sqlite_parameters. Команды «.param set KEY VALUE» и «.param unset KEY» создают или удаляют записи из таблицы temp.sqlite_parameters

Значение VALUE, передаваемое в «.param set KEY VALUE», может быть либо SQL-литералом, либо любым другим SQL-выражением или запросом, который можно вычислить для получения значения. Это позволяет задавать значения разных типов. Если такое вычисление завершается неудачей, переданное значение VALUE вместо этого заключается в кавычки и вставляется как текст

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

Типичные ошибки при работе с sqlite3

На практике я замечаю, что большинство проблем при работе с CLI сводится к нескольким повторяющимся ситуациям. Зная их заранее, можно сэкономить немало времени

Пропущенная точка с запятой. Самая частая ошибка — отсутствие «;» в конце SQL-команды. sqlite3 не выполнит оператор и будет ждать продолжения, показывая приглашение «…>». Если вы видите такое приглашение и не планировали вводить многострочный запрос, просто допишите «;» и нажмите Enter

Использование обратных косых черт в путях Windows. При указании пути к файлу в командах «.open» или «.save» в Windows используйте прямые косые черты: «c:/work/ex1.db», а не «c:\work\ex1.db». Обратная косая черта в контексте аргументов dot-команд может интерпретироваться как escape-символ

Потеря данных при работе с базой данных в памяти. Если sqlite3 запущен без указания файла или через двойной щелчок в Windows, все данные хранятся только в памяти и будут уничтожены при закрытии программы. Чтобы сохранить результат работы, используйте «.save имя_файла.db» до выхода

Перезапись существующей базы данных командой «.open —new». Параметр --new уничтожает все данные в существующем файле без предупреждения. Перед его использованием убедитесь, что у вас есть резервная копия

Неверный режим при импорте CSV. Если перед командой «.import» не установлен режим «.mode csv», sqlite3 может неверно интерпретировать разделители и импортировать данные некорректно. Всегда явно устанавливайте режим перед импортом

Ответы на эти вопросы могут быть для вас полезными

Чем отличается библиотека SQLite от программы sqlite3? Библиотека SQLite — это движок базы данных, реализующий SQL. Программа sqlite3 (CLI) — это отдельное приложение, которое принимает ввод пользователя и передаёт его в библиотеку для выполнения. Это две разные вещи, хотя их названия часто используются взаимозаменяемо

Как сохранить базу данных, созданную в памяти, в файл на диске? Используйте команду «.save имя_файла.db». Будьте осторожны: если файл с таким именем уже существует, он будет перезаписан без запроса подтверждения

Как импортировать CSV-файл в таблицу SQLite? Установите режим командой «.mode csv», затем выполните «.import имя_файла.csv имя_таблицы». Если первая строка файла содержит заголовки столбцов, а таблица уже существует, добавьте параметр «—skip 1», чтобы пропустить строку заголовков

Можно ли открыть ZIP-архив или DOCX-файл через sqlite3? Да. sqlite3 автоматически определяет ZIP-архивы и открывает их как виртуальную таблицу с именем «zip». Это работает для любых файлов, основанных на формате ZIP: JAR, DOCX, ODP и других

Что делать, если база данных повреждена и «.dump» не работает? Используйте команду «.recover». В отличие от «.dump», она работает напрямую со страницами базы данных и способна восстановить данные из неповреждённых частей файла даже при серьёзных повреждениях. Строки, которые невозможно отнести к конкретной таблице, попадают в автоматически создаваемую таблицу «lost_and_found»