Командная оболочка SQLite

1. Начало работы

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

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

Библиотека SQLite — это код, реализующий ядро базы данных SQL. Программа для командной строки «sqlite3», или «CLI», — это приложение, которое принимает вводимые пользователем данные и передаёт их библиотеке SQLite для обработки. Важно понимать, что это две разные вещи. Когда кто-то говорит «SQLite» или «sqlite3», он может иметь в виду как саму библиотеку SQLite, так и CLI, предоставляющий пользовательский интерфейс к библиотеке. Часто контекст помогает понять, о чём именно из этих двух вещей говорит собеседник.

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

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

Программа sqlite3 написана основными разработчиками SQLite и предназначена для их работы; это официальный способ интерактивного доступа к файлам баз данных SQLite. Однако некоторые пользователи могут предпочесть графический пользовательский интерфейс (GUI). Существует несколько таких программ от сторонних разработчиков. Одна из них — Visual DB, спонсор проекта SQLite:

Благодарим Visual DB за помощь в том, чтобы SQLite стал лучше для всех!

1.3. Запуск CLI

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

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

Например, чтобы создать новую базу данных 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),('goodbuy',20);
sqlite> select * from tbl1;
┌───────────┬─────┐
│    one    │ two │
├───────────┼─────┤
│ 'hello!'  │ 10  │
│ 'goodbye' │ 20  │
└───────────┴─────┘
sqlite>

Завершите работу программы sqlite3, введя системный символ конца файла (обычно Control-D). Чтобы остановить долго выполняющуюся инструкцию SQL, используйте символ прерывания (обычно Control-C).

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

sqlite> CREATE TABLE tbl2 (
   ...>   f1 varchar(30) primary key,
   ...>   f2 text,
   ...>   f3 real
   ...> );
sqlite>

1.4. Запуск двойным щелчком в Windows

Пользователи Windows могут дважды щёлкнуть значок sqlite3.exe, чтобы открыть окно терминала с запущенной командной оболочкой SQLite. Однако двойной щелчок запускает sqlite3.exe без аргументов командной строки, поэтому файл базы данных указан не будет и SQLite использует временную базу данных в памяти, которая удаляется по завершении сеанса. Чтобы использовать постоянный файл на диске в качестве базы данных, сразу после запуска окна терминала введите команду «.open»:

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» и начинается работа с ним. Если файл «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», во избежание неоднозначности можно указать полный путь с прямыми косыми чертами в качестве разделителей каталогов.

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

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

2. Специальные команды (команды с точкой)

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

Чтобы просмотреть список доступных команд с точкой, введите «.help» без аргументов. Или введите «.help TOPIC», чтобы получить подробную информацию о TOPIC. Список доступных команд с точкой для 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>

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

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

3. Правила для команд с точкой, SQL и других команд

3.1. Структура строки

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

• инструкции SQL
• команды с точкой
• комментарии CLI

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

Команды с точкой имеют специальный синтаксис:

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

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

3.2. Аргументы команд с точкой

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

1. Все конечные пробелы и последняя точка с запятой, если она есть, удаляются.
2. Аргументы отделяются друг от друга и от самой команды с точкой пробелами.
3. Текст между '...' считается одним аргументом, а разделители ' удаляются, даже если в тексте есть пробелы.
4. Текст между "..." считается одним аргументом, а разделители " удаляются.
5. Экранирование обратной косой чертой в стиле C (например, \\, \n, \r, \", \033 и т. д.) работает только внутри аргументов в двойных кавычках.

3.3. Команды с точкой обрабатываются отдельно

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

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

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

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

   
.mode box 
 
 Show query results in a grid formed using Unicode box drawing characters. 
  
.mode quote 
 
 Show query results as lines of comma-separated SQL literals, one line per row of output. 
  
.mode csv 
 
 Show query results as CSV ("Comma-Separated Values"). 
  
.mode --list 
 
 Show a list of available output modes 
  
.mode 
 
 Show the current output mode 
  
.mode --once box 
 
 Show the next SQL statement using "box" mode but then automatically revert back to the current mode. 
  

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

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

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

sqlite> .tables
tbl1 tbl2
sqlite>

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

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

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

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

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

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» примерно эквивалентна включению режима list с последующим выполнением такого запроса:

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() для этого файла базы данных.

sqlite> .databases

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

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

Команда «.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 указывает считывать содержимое базы данных из следующих строк ввода в шестнадцатеричном формате, а не из отдельного файла на диске. Команду с точкой «.dbtotxt» и/или инструмент командной строки dbtotxt можно использовать для создания соответствующего текстового представления базы данных. Параметр --hexdb предназначен для тестирования разработчиками SQLite. Нам неизвестны сценарии его применения за пределами внутреннего тестирования и разработки SQLite.

7. Перенаправление ввода-вывода

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

По умолчанию sqlite3 отправляет результаты запросов в стандартный вывод. Это поведение можно изменить командами «.output» и «.once». Укажите имя выходного файла в качестве аргумента команды .output, и все последующие результаты запросов будут записываться в этот файл. Вместо .output можно использовать команду .once: вывод будет перенаправлен только для следующей команды, после чего снова будет выводиться в консоль. Выполните .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».

sqlite> .www
sqlite> SELECT * FROM users WHERE email LIKE '%@aol.com';

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

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

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

sqlite> .read myscript.sql

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

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

sqlite> .read |myscript.bat

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

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

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

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.

7.4. Функция 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 length(edit(img,'gimp')) WHERE id='pic-1542';

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

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

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

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

При выполнении команды .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.

7.6. Экспорт в 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

7.6.1. Экспорт в 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", чтобы вызвать текстовый редактор системы, используемый по умолчанию, вместо стандартной программы для работы с электронными таблицами.

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

Экспорт в чистый TSV без кавычек вокруг полей можно выполнить, введя ".mode tabs" перед запуском запроса. Однако команда ".import" в режиме tabs не сможет правильно прочитать вывод, если он содержит символы двойных кавычек. Чтобы получить TSV с кавычками в соответствии с RFC 4180, который можно импортировать в режиме tabs с помощью ".import", выполните:

.mode csv -colsep "\t"

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

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

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

CREATE TABLE zip(
  name,     -- Name of the file
  mode,     -- Unix-style file permissions
  mtime,    -- Timestamp, seconds since 1970
  sz,       -- File size after decompression
  rawdata,  -- Raw compressed file data
  data,     -- Uncompressed file content
  method    -- ZIP compression method code
);

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

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

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

sqlite> SELECT writefile(name,content) FROM zip
   ...> WHERE name LIKE 'docProps/%';

8.1. Как реализован доступ к 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.

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

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

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

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

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

$ zcat ex1.dump.gz | sqlite3 ex2

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

$ createdb ex2
$ sqlite3 ex1 .dump | psql ex2

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

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

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

CREATE TABLE lost_and_found(
    rootpgno INTEGER,             -- root page of tree pgno is a part of
    pgno INTEGER,                 -- page number row was found on
    nfield INTEGER,               -- number of fields in row
    id INTEGER,                   -- value of rowid field, or NULL
    c0, c1, c2, c3...             -- columns for fields of row
);

Таблица "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

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

Во время выполнения можно добавлять в оболочку командной строки новые пользовательские функции SQL, определённые приложением, последовательности сортировки, виртуальные таблицы и VFS, используя команду ".load". Сначала соберите расширение в виде DLL или динамической библиотеки (как описано в документе Загружаемые во время выполнения расширения), а затем введите:

sqlite> .load /path/to/my_extension

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

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

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

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

Команда ".sha3sum" вычисляет хеш SHA3 для содержимого базы данных. Важно отметить, что хеш вычисляется по содержимому базы данных, а не по его представлению на диске. Это означает, например, что VACUUM и другие аналогичные преобразования, сохраняющие данные, не изменяют хеш.

Команда ".sha3sum" поддерживает параметры "--sha3-224", "--sha3-256", "--sha3-384" и "--sha3-512", задающие используемый для хеширования вариант SHA3. По умолчанию используется SHA3-256.

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

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

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

13. Самопроверка содержимого базы данных

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

CREATE TABLE selftest(
  tno INTEGER PRIMARY KEY,  -- Test number
  op TEXT,                  -- 'run' or 'memo'
  cmd TEXT,                 -- SQL command to run, or text of "memo"
  ans TEXT                  -- Expected result of the SQL command
);

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

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

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

14. Поддержка SQLite Archive

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

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

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

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

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

-- Two ways to create a new archive named "new_archive.db" containing
-- files "file1", "file2" and "file3".
.ar -c --file new_archive.db file1 file2 file3
.ar -f new_archive.db --create file1 file2 file3

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

-- Create a new archive "new_archive.db" containing files "file1" and
-- "file2" from directory "dir1".
.ar cCf dir1 new_archive.db file1 file2 file3

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

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

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

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

-- Extract all files from the archive in the current "main" db to the
-- current working directory. List files as they are extracted. 
.ar --extract --verbose

-- Extract file "file1" from archive "ar.db" to directory "dir1".
.ar fCx ar.db dir1 file1

-- Extract files with ".h" extension to directory "headers".
.ar -gCx headers *.h

14.3. Команда просмотра списка SQLite Archive

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

-- List contents of archive in current "main" db..
.ar --list

14.4. Команды добавления и обновления SQLite Archive

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

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

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

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

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

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

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

14.7. SQL-код для реализации операций SQLite Archive

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

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

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

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

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

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

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

15. Параметры SQL

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

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

Эта оболочка командной строки оставляет безымянные параметры несвязанными, то есть они принимают значение SQL NULL, но именованным параметрам могут присваиваться значения. Если существует временная таблица с именем "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, надёжный способ получить текстовое значение — заключить его в одинарные кавычки, защищённые от описанного выше разбора хвоста команды. Например (если только не требуется значение -1365):

.parameter init
.parameter set @phoneNumber "'202-456-1111'"

Обратите внимание: двойные кавычки защищают одинарные кавычки и гарантируют, что текст в кавычках будет разобран как один аргумент.

Таблица temp.sqlite_parameters предоставляет значения параметров только в оболочке командной строки. Таблица temp.sqlite_parameter не влияет на запросы, выполняемые непосредственно с помощью API SQLite на языке C. Предполагается, что отдельные приложения реализуют собственную привязку параметров. Чтобы посмотреть, как оболочка командной строки выполняет привязку параметров, найдите "sqlite_parameters" в исходном коде оболочки командной строки и используйте его в качестве подсказки для собственной реализации.

16. Рекомендации по индексам (SQLite Expert)

Примечание: эта команда является экспериментальной. В будущем её могут удалить или изменить её интерфейс несовместимым образом.

Для большинства нетривиальных баз данных SQL ключ к высокой производительности — создание подходящих индексов SQL. В данном контексте «подходящие индексы SQL» — это индексы, благодаря которым запросы, производительность которых нужно оптимизировать приложению, выполняются быстро. Команда ".expert" помогает в этом, предлагая индексы, которые могли бы ускорить определённые запросы, если бы присутствовали в базе данных.

Сначала вводится команда ".expert", а затем на отдельной строке — запрос SQL. Например, рассмотрим следующий сеанс:

sqlite> CREATE TABLE x1(a, b, c);                  -- Create table in database 
sqlite> .expert
sqlite> SELECT * FROM x1 WHERE a=? AND b>?;        -- Analyze this SELECT 
CREATE INDEX x1_idx_000123a7 ON x1(a, b);

0|0|0|SEARCH TABLE x1 USING INDEX x1_idx_000123a7 (a=? AND b>?)

sqlite> CREATE INDEX x1ab ON x1(a, b);             -- Create the recommended index 
sqlite> .expert
sqlite> SELECT * FROM x1 WHERE a=? AND b>?;        -- Re-analyze the same SELECT 
(no new indexes)

0|0|0|SEARCH TABLE x1 USING INDEX x1ab (a=? AND b>?)

В примере выше пользователь создаёт схему базы данных (одну таблицу — "x1"), а затем использует команду ".expert" для анализа запроса, в данном случае "SELECT * FROM x1 WHERE a=? AND b>?". Оболочка рекомендует создать новый индекс (индекс "x1_idx_000123a7") и выводит план, который использовался бы для запроса, в формате EXPLAIN QUERY PLAN. Затем пользователь создаёт индекс с эквивалентной схемой и повторно анализирует тот же запрос. На этот раз оболочка не рекомендует создавать новые индексы и выводит план, который SQLite использует для запроса с учётом существующих индексов.

Команда ".expert" поддерживает следующие параметры:

Описанные в этом разделе функции можно интегрировать в другие приложения или инструменты с помощью кода расширения SQLite Expert.

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

17. Работа с несколькими подключениями к базам данных

Начиная с версии 3.37.0 (2021-11-27), CLI может одновременно поддерживать открытыми несколько подключений к базе данных. В каждый момент времени активно только одно подключение. Неактивные подключения остаются открытыми, но бездействуют.

Используйте команду ".connection" (часто сокращаемую до ".conn"), чтобы увидеть список подключений к базам данных и узнать, какое из них активно в данный момент. Каждое подключение к базе данных обозначается целым числом от 0 до 9. (Одновременно может быть открыто не более 10 подключений.) Чтобы переключиться на другое подключение к базе данных, создав его, если оно ещё не существует, введите команду ".conn", а затем его номер. Чтобы закрыть подключение к базе данных, введите ".conn close N", где N — номер подключения.

Хотя базовые подключения к базам данных SQLite полностью независимы друг от друга, многие параметры CLI, например формат вывода, общие для всех подключений. Поэтому изменение режима вывода в одном подключении изменит его во всех остальных. С другой стороны, некоторые команды с точкой, например .open, влияют только на текущее подключение.

18. Различные возможности расширений

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

• последовательность сортировки UINT, которая при сортировке учитывает значения беззнаковых целых чисел, встроенных в текст, наряду с другим текстом;
• десятичная арифметика, предоставляемая расширением decimal;
• табличная функция generate_series();
• функции base64() и base85(), которые кодируют двоичный объект в текст base64 или base85 либо декодируют такой текст обратно в двоичный объект; и
• поддержка расширенных регулярных выражений POSIX, связанных с оператором REGEXP.

19. Другие команды с точкой

В оболочке командной строки доступны и многие другие команды с точкой. Выполните команду ".help", чтобы получить полный список для конкретной версии и сборки SQLite.

20. Использование sqlite3 в командном скрипте

Один из способов использовать sqlite3 в командном скрипте — с помощью "echo" или "cat" создать файл с последовательностью команд, а затем запустить sqlite3, перенаправив ввод из созданного файла с командами. Этот способ работает хорошо и подходит для многих ситуаций. Однако для дополнительного удобства sqlite3 позволяет передать одну команду SQL в командной строке вторым аргументом после имени базы данных. Если программу sqlite3 запустить с двумя аргументами, второй аргумент передаётся библиотеке SQLite для обработки, результаты запроса выводятся в стандартный вывод в режиме list, а программа завершает работу. Этот механизм предназначен для упрощения использования sqlite3 совместно с такими программами, как "awk". Например:

$ sqlite3 ex1 'select * from tbl1' \
>  | awk '{printf "<tr><td>%s<td>%s\n",$1,$2 }'
<tr><td>hello<td>10
<tr><td>goodbye<td>20
$

21. Обозначение конца оператора SQL

Команды SQLite обычно завершаются точкой с запятой. В CLI для завершения команды также можно использовать слово "GO" (без учёта регистра) или символ косой черты "/" на отдельной строке. Они используются соответственно в SQL Server и Oracle и поддерживаются CLI SQLite для совместимости. В sqlite3_exec() они работать не будут, поскольку CLI преобразует эти вводные конструкции в точку с запятой перед передачей в ядро SQLite.

22. Подробнее о запуске CLI

Как упоминалось ранее, обычно CLI запускают, вводя "sqlite3" и имя файла базы данных. Однако программа "sqlite3" принимает и многие другие аргументы, помимо имени файла базы данных.

22.1. Дополнительные аргументы командной строки

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

$ sqlite3 test.db   ".mode box"   "SELECT * FROM users;"

Если таким образом указаны дополнительные аргументы, стандартный ввод не считывается, а CLI завершает работу после обработки всех дополнительных аргументов.

22.2. Параметры командной строки

Дополнительные аргументы, начинающиеся с символа "-", являются параметрами командной строки. Доступно множество параметров командной строки. Чтобы просмотреть список, используйте параметр командной строки --help:

$ sqlite3 --help
FILENAME is the name of an SQLite database. A new database is created
if the file does not previously exist. Defaults to :memory:.
OPTIONS include:
   --                   treat no subsequent arguments as options
   -A ARGS...           run ".archive ARGS" and exit
   -append              append the database to the end of the file
   -ascii               set output mode to 'ascii'
   -bail                stop after hitting an error
   -batch               force batch I/O
   -box                 set output mode to 'box'
   -column              set output mode to 'column'
   -cmd COMMAND         run "COMMAND" before reading stdin
   -csv                 set output mode to 'csv'
   -deserialize         open the database using sqlite3_deserialize()
   -echo                print inputs before execution
   -escape T            ctrl-char escape; T is one of: symbol, ascii, off
   -init FILENAME       read/process named file
   -[no]header          turn headers on or off
   -heap SIZE           Size of heap for memsys3 or memsys5
   -help                show this message
   -html                set output mode to HTML
   -ifexists            only open if database already exists
   -interactive         force interactive I/O
   -json                set output mode to 'json'
   -line                set output mode to 'line'
   -list                set output mode to 'list'
   -lookaside SIZE N    use N entries of SZ bytes for lookaside memory
   -markdown            set output mode to 'markdown'
   -maxsize N           maximum size for a --deserialize database
   -memtrace            trace all memory allocations and deallocations
   -mmap N              default mmap size set to N
   -newline SEP         set output row separator. Default: '\n'
   -nofollow            refuse to open symbolic links to database files
   -nonce STRING        set the safe-mode escape nonce
   -no-rowid-in-view    Disable rowid-in-view using sqlite3_config()
   -nullvalue TEXT      set text string for NULL values. Default ''
   -pagecache SIZE N    use N slots of SZ bytes each for page cache memory
   -pcachetrace         trace all page cache operations
   -quote               set output mode to 'quote'
   -readonly            open the database read-only
   -safe                enable safe-mode
   -separator SEP       set output column separator. Default: '|'
   -stats               print memory stats before each finalize
   -table               set output mode to 'table'
   -tabs                set output mode to 'tabs'
   -unsafe-testing      allow unsafe commands and modes for testing
   -version             show SQLite version
   -vfs NAME            use NAME as the default VFS
   -vfstrace            enable tracing of all VFS calls
   -zip                 open the file as a ZIP Archive

CLI поддерживает гибкий формат параметров командной строки. Допускается один или два начальных символа "-". Таким образом, "-box" и "--box" означают одно и то же. Параметры командной строки обрабатываются слева направо. Поэтому параметр "--box" переопределит предшествующий параметр "--quote".

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

22.3. Параметр командной строки --safe

Параметр командной строки --safe пытается отключить все функции CLI, которые могут привести к каким-либо изменениям на компьютере, за исключением изменений в конкретном файле базы данных, указанном в командной строке. Идея заключается в том, что если вы получили большой SQL-скрипт из неизвестного или ненадежного источника, его можно запустить, чтобы посмотреть, что он делает, не подвергая себя риску эксплуатации уязвимости благодаря параметру --safe. Параметр --safe отключает (помимо прочего):

• Команду .open, если не используется параметр --hexdb или имя файла не равно ":memory:". Это не позволяет скрипту читать или записывать файлы баз данных, не указанные в исходной командной строке.
• Команду SQL ATTACH.
• Функции SQL с потенциально вредными побочными эффектами, такие как edit(), fts3_tokenizer(), load_extension(), readfile() и writefile().
• Команду .archive.
• Команды .backup и .save.
• Команду .import.
• Команду .load.
• Команду .log.
• Команды .shell и .system.
• Команды .excel, .once и .output.
• Другие команды, которые могут иметь вредные побочные эффекты.

По сути, отключается любая функция CLI, которая читает или записывает данные в файл на диске, отличный от основного файла базы данных.

22.3.1. Обход ограничений --safe для отдельных команд

Если в командной строке также указан параметр "--nonce NONCE" с достаточно длинной строкой NONCE произвольного содержания, команда ".nonce NONCE" (с той же длинной строкой nonce) позволит следующему оператору SQL или команде с точкой обойти ограничения --safe.

Предположим, вы хотите запустить подозрительный скрипт, для работы которого нужны одна или две функции, обычно отключаемые параметром --safe. Например, ему может потребоваться подключить дополнительную базу данных с помощью ATTACH. Или, возможно, скрипту нужно загрузить конкретное расширение. Для этого достаточно предварить (тщательно проверенный) оператор ATTACH или команду ".load" соответствующей командой ".nonce" и передать то же значение nonce с помощью параметра командной строки "--nonce". Тогда эти конкретные команды смогут выполняться в обычном режиме, но все остальные небезопасные команды по-прежнему будут ограничены.

Использование ".nonce" опасно, поскольку ошибка может позволить вредоносному скрипту нанести ущерб вашей системе. Поэтому используйте ".nonce" осторожно и экономно, только в крайнем случае, когда нет других способов запустить скрипт в режиме --safe.

22.4. Параметр командной строки --unsafe-testing

Параметр командной строки --unsafe-testing включает функции CLI, предназначенные только для внутреннего тестирования. Параметр --unsafe-testing отключает встроенные в SQLite средства защиты, например SQLITE_DBCONFIG_DEFENSIVE и SQLITE_DBCONFIG_TRUSTED_SCHEMA. Параметр --unsafe-testing также включает функции, неправильное использование которых может привести к повреждению базы данных, ошибкам памяти или аналогичным проблемам в самом CLI либо в библиотеке SQLite. Например, --unsafe-testing включает команду ".testctrl assert false", которая намеренно вызывает сбой проверки assert, чтобы убедиться в работоспособности механизма обработки таких сбоев.

Нарушение работы, для которого требуется параметр --unsafe-testing, как правило, не считается ошибкой.

22.5. Параметры командной строки --no-utf8 и --utf8

На платформе Windows при использовании консоли для ввода или вывода требуется преобразование между кодировкой символов, доступной в консоли или передаваемой ей, и внутренним текстовым представлением CLI в UTF-8. В предыдущих версиях CLI эти параметры включали или отключали преобразование, использовавшее функцию консоли Windows, которая позволяла принимать или выдавать UTF-8 в современных версиях ОС.

Текущие версии CLI (3.44.1 и более поздние) выполняют ввод-вывод через консоль, считывая или записывая UTF-16 с помощью API консоли Windows. Поскольку это работает даже в версиях Windows, начиная с Windows 2000, эти параметры больше не нужны. Они по-прежнему принимаются, но не оказывают никакого эффекта.

Во всех случаях текстовый ввод-вывод вне консоли выполняется в кодировке UTF-8.

На платформах, отличных от Windows, эти параметры также игнорируются.

23. Компиляция программы sqlite3 из исходного кода

Для компиляции оболочки командной строки в системах Unix и Windows с MinGW подходит обычная команда configure-make:

sh configure; make

Команда configure-make подходит как для сборки из канонических исходников дерева исходного кода, так и из объединённого пакета. Зависимостей немного. При сборке из канонических исходников требуется работоспособный tclsh. При использовании объединённого пакета все подготовительные операции, обычно выполняемые tclsh, уже выполнены, и требуются только стандартные инструменты сборки.

Для работы команды .archive требуется работоспособная библиотека сжатия zlib.

В Windows с MSVC используйте nmake с файлом Makefile.msc:

nmake /f Makefile.msc

Для корректной работы команды .archive скопируйте исходный код zlib в подкаталог compat/zlib дерева исходного кода и выполните сборку следующим образом:

nmake /f Makefile.msc USE_ZLIB=1

23.1. Сборка своими силами

Исходный код интерфейса командной строки sqlite3 находится в одном файле с именем "shell.c". Исходный файл shell.c создаётся из других исходников, но большую часть кода для shell.c можно найти в src/shell.c.in. (Чтобы заново создать shell.c, выполните команду "make shell.c" из канонического дерева исходного кода.) Скомпилируйте файл shell.c (вместе с исходным кодом библиотеки sqlite3), чтобы создать исполняемый файл. Например:

gcc -o sqlite3 shell.c sqlite3.c -ldl -lpthread -lz -lm

Для получения полнофункциональной оболочки командной строки рекомендуются следующие дополнительные параметры компиляции:

• -DSQLITE_THREADSAFE=0
• -DSQLITE_ENABLE_EXPLAIN_COMMENTS
• -DSQLITE_HAVE_ZLIB
• -DSQLITE_INTROSPECTION_PRAGMAS
• -DSQLITE_ENABLE_UNKNOWN_SQL_FUNCTION
• -DSQLITE_ENABLE_STMTVTAB
• -DSQLITE_ENABLE_DBPAGE_VTAB
• -DSQLITE_ENABLE_DBSTAT_VTAB
• -DSQLITE_ENABLE_OFFSET_SQL_FUNC
• -DSQLITE_ENABLE_JSON1
• -DSQLITE_ENABLE_RTREE
• -DSQLITE_ENABLE_FTS4
• -DSQLITE_ENABLE_FTS5

Последнее обновление этой страницы: 2026-01-03 12:42:10Z