Командная строка для SQLite

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

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

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

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

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

$ sqlite3 ex1
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);
sqlite> insert into tbl1 values('goodbye', 20);
sqlite> select * from tbl1;
hello!|10
goodbye|20
sqlite>

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

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

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

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

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

Большую часть времени sqlite3 просто считывает строки ввода и передает их библиотеке SQLite для выполнения. Но строки ввода, начинающиеся с точки (".") перехватываются и интерпретируются самой программой sqlite3. Эти "команды с точкой" обычно используются для изменения формата вывода запросов или для выполнения определенных предварительно подготовленных запросов. Первоначально существовало всего несколько команд с точкой, но со временем накопилось много новых функций, так что сегодня их более 60.

Для просмотра доступных команд с точкой, вы можете ввести ".help" без аргументов. Или введите ".help TOPIC" для получения подробной информации о TOPIC. Список доступных команд с точкой представлен ниже:

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
.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
.headers on|off          Turn display of headers on or off
.help ?-all? ?PATTERN?   Show help text for PATTERN
.import FILE TABLE       Import data from FILE into TABLE
.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
.separator COL ?ROW?     Change the column and row separators
.sha3sum ...             Compute a SHA3 hash of database content
.shell CMD ARGS...       Run CMD ARGS... in a system shell
.show                    Show the current values for various settings
.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
.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
.width NUM1 NUM2 ...     Set minimum column widths for columnar output
.www                     Display output of the next command in web browser
sqlite>

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

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

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

• операторов SQL;
• команд с точкой; или
• комментариев CLI

Операторы SQL имеют свободную форму и могут быть распределены по нескольким строкам, со встроенным пробелом или комментариями SQL в любом месте. Они завершаются символом ';' в конце строки ввода или символом '/' или словом "go" в отдельной строке. Когда не в конце строки ввода, символ ';' действует как разделитель операторов SQL. Конечные пробелы игнорируются для целей завершения.

Команда с точкой имеет более ограниченную структуру:

• Она должна начинаться с точки "." слева без предшествующих пробелов.
• Она должна быть полностью заключена в одну строку ввода.
• Она не может быть в середине обычного оператора SQL. Другими словами, она не может быть в приглашении для продолжения.
• Нет синтаксиса комментариев для команд с точкой.

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

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

Аргументы, передаваемые командам с точкой, анализируются из хвоста команды по этим правилам:

1. Конец строки и любые другие символы пробелов в конце отбрасываются;
2. Пробелы непосредственно после имени команды с точкой или любого ограничения ввода аргумента отбрасываются;
3. Ввод аргумента начинается с любого символа, отличного от пробела;
4. Ввод аргумента заканчивается символом, который зависит от его ведущего символа таким образом:
• для ведущего одиночного кавычки ('), одиночная кавычка действует как разделитель конца;
• для ведущей двойной кавычки ("), неэкранированная двойная кавычка действует как разделитель конца;
• для любого другого ведущего символа разделителем конца является любой пробел; и
• конец команды действует как разделитель конца для любого аргумента;
5. Внутри ввода аргумента в двойных кавычках обратная косая черта с экранированной двойной кавычкой является частью аргумента, а не его завершающей кавычкой;
6. Внутри аргумента в двойных кавычках, традиционной строке C-стиля, выполняется перевод последовательности обратной косой черты;
7. Разделители ввода аргументов (ограничивающие кавычки или пробелы) отбрасываются, чтобы получить переданный аргумент.

4.3. Выполнение команд с точкой

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

5. Изменение форматов вывода

Программа sqlite3 может отображать результаты запроса в 14 различных форматах вывода:

Вы можете использовать команду с точкой ".mode", чтобы переключаться между этими форматами вывода. По умолчанию используется режим вывода "list". В режиме "list" каждая строка результата запроса записывается в отдельной строке вывода, а каждый столбец в этой строке отделяется определённой строкой-разделителем. По умолчанию разделителем является символ "pipe" ("|"). Режим "list" особенно полезен, когда вы собираетесь передать вывод запроса другой программе (например, AWK) для дополнительной обработки.

sqlite> .mode list
sqlite> select * from tbl1;
hello!|10
goodbye|20
sqlite>

Введите ".mode" без аргументов, чтобы отобразить текущий режим:

sqlite> .mode
current output mode: list
sqlite>

Используйте команду с точкой ".separator", чтобы изменить разделитель. Например, чтобы изменить разделитель на запятую и пробел, можно сделать так:

sqlite> .separator ", "
sqlite> select * from tbl1;
hello!, 10
goodbye, 20
sqlite>

Следующая команда ".mode" может сбросить ".separator" до некоторого значения по умолчанию (в зависимости от её аргументов). Поэтому вам, вероятно, потребуется повторять команду ".separator" всякий раз, когда вы меняете режимы, если вы хотите продолжить использование нестандартного разделителя.

В режиме "quote" вывод форматируется как SQL-литералы. Строки заключены в одинарные кавычки, а внутренние одинарные кавычки экранируются удвоением. Двоичные данные отображаются в шестнадцатеричном формате (например: x'abcd'). Числа отображаются как ASCII-текст, а значения NULL отображаются как "NULL". Все столбцы отделяются друг от друга запятой (или любым другим символом, выбранным с помощью ".separator").

sqlite> .mode quote
sqlite> select * from tbl1;
'hello!',10
'goodbye',20
sqlite>

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

sqlite> .mode line
sqlite> select * from tbl1;
one = hello!
two = 10

one = goodbye
two = 20
sqlite>

В режиме "column" каждая запись отображается в отдельной строке с данными, выровненными в столбцах. Например:

sqlite> .mode column
sqlite> select * from tbl1;
one       two
--------  ---
hello!    10
goodbye   20
sqlite>

В режиме "column" (а также в режимах "box", "table" и "markdown") ширина столбцов автоматически подстраивается. Но вы можете переопределить это, указав заданную ширину для каждого столбца с помощью команды ".width". Аргументы к ".width" — это целые числа, которые представляют количество символов, выделяемых для каждого столбца. Отрицательные числа означают выравнивание вправо. Таким образом:

sqlite> .width 12 -6
sqlite> select * from tbl1;
one              two
------------  ------
hello!            10
goodbye           20
sqlite>

Ширина 0 означает, что ширина столбца выбирается автоматически. Неуказанные ширины столбцов становятся нулевыми. Следовательно, команда ".width" без аргументов сбрасывает все ширины столбцов до нуля и, следовательно, заставляет все ширины столбцов определяться автоматически.

Режим "column" — это табличный формат вывода. Другие табличные форматы вывода — "box", "markdown" и "table":

sqlite> .width
sqlite> .mode markdown
sqlite> select * from tbl1;
|   one   | two |
|---------|-----|
| hello!  | 10  |
| goodbye | 20  |
sqlite> .mode table
sqlite> select * from tbl1;
+---------+-----+
|   one   | two |
+---------+-----+
| hello!  | 10  |
| goodbye | 20  |
+---------+-----+
sqlite> .mode box
sqlite> select * from tbl1;
┌─────────┬─────┐
│   one   │ two │
├─────────┼─────┤
│ hello!  │ 10  │
│ goodbye │ 20  │
└─────────┴─────┘
sqlite>

Режимы столбцов принимают дополнительные параметры для управления форматированием. Параметр "--wrap N" (где N — целое число) заставляет столбцы переносить текст, длина которого превышает N символов. Перенос отключается, если N равно нулю.

sqlite> insert into tbl1 values('The quick fox jumps over a lazy brown dog.',90);
sqlite> .mode box --wrap 30
sqlite> select * from tbl1 where two>50;
┌────────────────────────────────┬─────┐
│              one               │ two │
├────────────────────────────────┼─────┤
│ The quick fox jumps over a laz │ 90  │
│ y brown dog.                   │     │
└────────────────────────────────┴─────┘
sqlite>

Перенос происходит после ровно N символов, что может быть посередине слова. Чтобы переносить по границам слов, добавьте параметр "--wordwrap on" (или просто "-ww" для краткости):

sqlite> .mode box --wrap 30 -ww
sqlite> select * from tbl1 where two>50;
┌─────────────────────────────┬─────┐
│             one             │ two │
├─────────────────────────────┼─────┤
│ The quick fox jumps over a  │ 90  │
│ lazy brown dog.             │     │
└─────────────────────────────┴─────┘
sqlite>

Параметр "--quote" заставляет результаты в каждом столбце цитироваться как SQL-литерал, как в режиме "quote". См. онлайн-справочную информацию для получения дополнительных параметров.

Команда ".mode box --wrap 60 --quote" настолько полезна для запросов к базам данных общего назначения, что ей присвоен псевдоним. Вместо того, чтобы набирать всю эту 27-символьную команду, вы можете просто сказать ".mode qbox".

Другим полезным режимом вывода является "insert". В режиме "insert" вывод форматируется таким образом, чтобы он выглядел как операторы SQL INSERT. Используйте режим "insert", чтобы сгенерировать текст, который впоследствии можно использовать для ввода данных в другую базу данных.

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

sqlite> .mode insert new_table
sqlite> select * from tbl1 where two<50;
INSERT INTO "new_table" VALUES('hello',10);
INSERT INTO "new_table" VALUES('goodbye',20);
sqlite>

Другие режимы вывода включают "csv", "json" и "tcl". Попробуйте их сами, чтобы увидеть, что они делают.

5.1. Управление концами строк

По умолчанию в Windows концы строк могут быть "\r\n" (CRLF) или "\n" (NL). Конец строки контролируется командой с точкой ".crlf". Используйте ".crlf on", чтобы установить CRLF-конец строки, и ".crlf off" для NL. Как и принято в Windows, по умолчанию используется CRLF. Однако это приводит к тому, что некоторые выводы отличаются от выводов на платформах, не являющихся Windows, из-за добавления символов "\r". Чтобы сделать вывод в командной строке Windows идентичным выводу на всех остальных системах, выполните ".crlf off".

На платформах, не являющихся Windows, команда ".crlf" является бесполезной операцией, и режим crlf всегда отключен. Для вывода CSV, конец строки всегда "\r\n" независимо от настройки .crlf, из-за требований RFC-4180.

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

Программа 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" отображает полную схему базы данных или схему одной таблицы, если указан необязательный аргумент с именем таблицы:

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" отображает список всех баз данных, открытых в текущем подключении. Их всегда будет минимум 2. Первая — "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" частной базы данных по открытому каналу.

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

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

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

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

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

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

Опция --zip заставляет интерпретировать указанный входной файл как архив ZIP, а не как файл базы данных SQLite.

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

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

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

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

sqlite> .mode list
sqlite> .separator |
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 (Comma-Separated-Values) во временный файл, а затем вызывать стандартную системную утилиту для просмотра файлов 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';

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

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

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

sqlite> .read myscript.sql

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

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

sqlite> .read |myscript.bat

8.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. Эти функции доступны как загружаемое расширение в файле loadext в файле исходного кода ext/misc/fileio.c в репозиториях исходного кода SQLite .

8.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';

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

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

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

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

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

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

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

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

Чтобы импортировать данные с произвольными разделителями и без кавычек, сначала установите режим ascii (".mode ascii"), а затем установите разделители полей и записей с помощью команды «.separator». Это подавит снятие кавычек. При «.import» данные будут разделены на поля и записи в соответствии с указанными разделителями.

8.6. Экспорт в CSV

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

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

В примере выше строка «.headers on» вызывает вывод меток столбцов в качестве первой строки вывода. Это означает, что первая строка результирующего файла CSV будет содержать метки столбцов. Если метки столбцов не нужны, установите «.headers off» вместо этого. (Настройка «.headers 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

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

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

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

9. Доступ к архивам 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/%';

9.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.

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

Используйте команду ".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

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

Как и команда ".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

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

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

sqlite> .load /path/to/my_extension

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

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

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

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

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

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

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

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

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

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

Команда ".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", а затем DELETE строки selftest, которые ссылаются на таблицы, которые не являются постоянными.

15. Поддержка архивов SQLite

Команда ".archive" и опция командной строки "-A" обеспечивают встроенную поддержку формата архивов SQLite. Интерфейс похож на интерфейс команды "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

15.1. Команда создания архива SQLite

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

15.2. Команда извлечения из архива SQLite

Извлекает файлы из архива (либо в текущую рабочую директорию, либо в директорию, указанную опцией --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

15.3. Команда просмотра содержимого архива SQLite

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

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

15.4. Команды вставки и обновления архива SQLite

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

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

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

15.5. Команда удаления из архива SQLite

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

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

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

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

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

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

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

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

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

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

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

16. Параметры 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;

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

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

Значение VALUE, передаваемое в ".param set KEY VALUE", может быть либо SQL-литералом, либо любым другим SQL-выражением или запросом, который может быть вычислен для получения значения. Это позволяет устанавливать значения разных типов. Если такое вычисление не удаётся, предоставленное значение VALUE вместо этого цитируется и вставляется как текст. Поскольку такое начальное вычисление может или не может завершиться успешно в зависимости от содержимого VALUE, надёжный способ получить текстовое значение — заключить его в одинарные кавычки, защищённые от описанного выше разбора хвоста команды. Например (если не подразумевается значение -1365):

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

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

Временная таблица sqlite_parameters предоставляет значения только для параметров в оболочке командной строки. Временная таблица sqlite_parameter не влияет на запросы, которые выполняются непосредственно с помощью API SQLite на языке C. От отдельных приложений ожидается реализация собственной привязки параметров. Вы можете найти "sqlite_parameters" в исходном коде оболочки командной строки, чтобы увидеть, как оболочка командной строки выполняет привязку параметров, и использовать это как подсказку для реализации её самостоятельно.

17. Рекомендации по индексам (эксперт SQLite)

Примечание: Эта команда находится в стадии разработки. Она может быть удалена или её интерфейс изменён несовместимым образом в какой-то момент в будущем.

Для большинства нетривиальных баз данных 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 expert.

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

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

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

Используйте точку-команду «.connection» (часто сокращается до «.conn»), чтобы увидеть список подключений к базе данных и указание того, какое из них в настоящее время активно. Каждое подключение к базе данных идентифицируется целым числом от 0 до 9. (Максимальное количество одновременно открытых подключений — 10.) Переключиться на другое подключение к базе данных, создав его, если оно не существует, можно, набрав команду «.conn» и затем его номер. Закрыть подключение к базе данных можно, набрав «.conn close N», где N — номер подключения.

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

19. Разные функциональные возможности расширений

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

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

20. Другие точки-команды

В командной оболочке доступны и другие точки-команды. Полный список для любой конкретной версии и сборки SQLite см. в команде «.help».

21. Использование sqlite3 в скрипте оболочки

Один из способов использования sqlite3 в скрипте оболочки — использовать «echo» или «cat», чтобы сгенерировать последовательность команд в файле, а затем вызвать sqlite3, перенаправив вход из сгенерированного файла команд. Это работает и подходит во многих случаях. Но для удобства sqlite3 позволяет ввести одну команду SQL в командной строке в качестве второго аргумента после имени базы данных. При запуске программы sqlite3 с двумя аргументами второй аргумент передается библиотеке SQLite для обработки, результаты запроса выводятся в стандартный вывод в режиме списка, и программа завершается. Этот механизм разработан для того, чтобы 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
$

22. Разделение оператора SQL

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

23. Опции командной строки

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

$ sqlite3 --help
Usage: ./sqlite3 [OPTIONS] FILENAME [SQL]
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
   -init FILENAME       read/process named file
   -[no]header          turn headers on or off
   -help                show this message
   -html                set output mode to HTML
   -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
   -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
   -zip                 open the file as a ZIP Archive

CLI гибко относится к формату опций командной строки. Разрешены один или два ведущих символа «-». Таким образом, «-box» и «--box» означают одно и то же. Опции командной строки обрабатываются слева направо. Следовательно, опция «--box» переопределит предыдущую опцию «--quote».

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

23.1. Опция командной строки --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, которая читает или записывает из файла на диске, кроме основного файла базы данных, отключена.

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

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

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

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

23.2. Опция командной строки --unsafe-testing

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

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

23.3. Опции командной строки --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 платформах эти опции также игнорируются.

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

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

sh configure; make

Настройка 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

24.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

Последнее изменение этой страницы: 14.10.2024 10:44:08 UTC