====== Sqlite: программа для доступа к базам SQLite из командной строки ======


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

===== Приступим =====

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

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

  $ sqlite3 ex1
  SQLite version 2.0.0
  Enter ".help" for instructions
  sqlite> create table tbl1(one varchar(10), two smallint);
  sqlite> insert into tbl1 values('hello!',10);
  sqlite> insert into tbl1 values('goodbye', 20);
  sqlite> select * from tbl1;
  hello!|10
  goodbye|20
  sqlite> 

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

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

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

Отступление: запрашиваем таблицу "SQLITE_MASTER"

В базе SQLite схема хранится в специальной таблице, называемой "sqlite_master". Вы можете выполнять инструкции "SELECT" на этой таблице, чтобы узнать, какие другие таблицы есть в этой базе SQLite. Например: 

  $ sqlite ex1
  SQlite vresion 2.0.0
  Enter ".help" for instructions
  sqlite> select * from sqlite_master;
  type = table
  name = tbl1
  tbl_name = tbl1
  rootpage = 3
  sql = create table tbl1(one varchar(10), two smallint)
  sqlite> 

Но вы не можете выполнять "DROP TABLE", "UPDATE", "INSERT" и "DELETE" на таблице "sqlite_master". Таблица "sqlite_master" изменяется автоматически, когда вы создаете или удаляете таблицы и индексы в базе. Вы не должны вручную изменять таблицу "sqlite_master". 

Схема временных ("TEMPORARY") таблиц не хранится в таблице "sqlite_master", так как временные таблицы видны только тем приложениям, которые их создали. Схема временных таблиц хранится в другой специальной таблице, которая называется "sqlite_temp_master". Таблица "sqlite_temp_master" сама является временной. 
Специальные команды sqlite

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

Для получения списка доступных команд с точкой вы в любое время можете ввести ".help". Например [На самом деле описания команд будут выданы на английском языке. - Прим. Д.С.]: 

  sqlite> .help
  .databases Список имен и файлов подключенных баз данных
  .dump ?TABLE? ... Дамп базы данных в текстовом формате
  .echo ON|OFF Установить эхо команд в "on" или "off"
  .exit Завершить эту программу
  .explain ON|OFF Установить режим вывода удобным для "EXPLAIN";
  on или off.
  .header(s) ON|OFF Установить вывод заголовков в on или off
  .help Показать это сообщение
  .indices TABLE Показать имена всех индексов для "TABLE"
  .mode MODE Установить режим вывода в значение "line(s)",
  "column(s)", "insert", "list", или "html"
  .mode insert TABLE Генерировать SQL-инструкции "INSERT" для "TABLE"
  .nullvalue STRING Выводить "STRING" вместо пустой строки
  для значений "NULL"
  .output FILENAME Перенаправить вывод в "FILENAME"
  .output stdout Перенаправить вывод на экран
  .prompt MAIN CONTINUE Заменить стандартные приглашения
  .quit Завершить эту программу
  .read FILENAME Выполнить SQL из "FILENAME"
  .schema ?TABLE? Показать инструкции "CREATE"
  .separator STRING Изменить разделитель строк для режима ("mode") "list"
  .show Показать текущие значения установленных переменных
  .tables ?PATTERN? Список имен таблиц согласно шаблону
  .timeout MS Попытаться открыть заблокированные таблицы
  через MS миллисекунд
  .width NUM NUM ... Установить ширину столбцов для режима ("mode") "column"
  sqlite> 

===== Изменяем формат вывода =====

Программа sqlite умеет показывать результаты запросов в пяти различных форматах: "line", "column", "list", "html" и "insert". Вы можете использовать команду с точкой ".mode", чтобы переключаться между этими форматами вывода. 

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

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

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

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

В режиме вывода "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> 

По умолчанию, каждый столбец имеет ширину не менее чем в 10 символов. Данные, которые не умещаются в столбец, обрезаются. Вы можете изменить ширину столбцов с помощью команды ".width". Например так: 

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

В этом примере команда ".width" устанавливает ширину первого столбца в 12 символов и ширину второго столбца - в 6 символов. Для всех остальных столбцов ширина осталась неизменной. Для команды ".width" можно указывать столько аргументов, сколько столбцов содержится в результате вашего запроса. 

Если вы указали ширину столбца как 0, то эта ширина будет автоматически установлена в максимум трех чисел: 10, ширина заголовка, ширина первой строки данных. Таким образом, ширина столбца станет самонастраивающейся. По умолчанию, ширина каждого столбца имеет самонастраивающее значение 0. 

Обозначения столбцов в первых двух строках вывода могут быть включены ("on") или выключены ("off") с помощью команды с точкой ".header". В примере выше обозначения столбцов включены ("on"). Перевести их в "off" вы можете следующим образом: 

  sqlite> .header off
  sqlite> select * from tbl1;
  hello 10
  goodbye 20
  sqlite> 

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

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

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

Остался режим вывода "html". В этом режиме SQLite записывает результат запроса в виде таблицы XHTML. Начальный <TABLE> и конечный </TABLE> не записываются, но все промежуточные <TR>, <TH> и <TD> пишутся. Режим вывода "html" полезен для CGI. 

===== Записываем результаты в файл =====

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

  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
  $ 

===== Запрашиваем схему базы данных =====

Программа sqlite поддерживает несколько удобных команд для просмотра схемы базы данных. Нет ничего такого, что можно было бы сделать этими командами, и нельзя было бы сделать другими методами. Эти команды поддерживаются исключительно из-за краткости. 

Например, чтобы просмотреть список таблиц в базе, вы можете ввести ".tables". 

  sqlite> .tables
  tbl1
  tbl2
  sqlite> 

Команда ".tables" выдает то же самое, что и следующий запрос в списочном режиме вывода: 

  SELECT name FROM sqlite_master WHERE type='table'
  UNION ALL SELECT name FROM sqlite_temp_master WHERE type='table'
  ORDER BY name;

В сущности, если вы посмотрите исходный код программы sqlite (смотрите файл src/shell.c в дереве исходников), вы увидите вывод именно этого запроса. 

Команда ".indices" аналогично выдает список всех индексов на данной таблице. Команда ".indices" имеет единственный аргумент, - имя таблицы, чьи индексы вы хотите видеть. И, наконец, команда ".schema". Без аргументов команда ".schema" показывает оригинальные операторы "CREATE TABLE" и "CREATE INDEX", которые использовались для построения текущей базы данных. Если в качестве аргумента вы указываете имя таблицы, ".schema" покажет оригинальный оператор "CREATE" для этой таблицы и всех ее индексов. Пример: 

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

Команда ".schema" выдает в списочном режиме результат следующего запроса:
  SELECT sql FROM
   (SELECT * FROM sqlite_master UNION ALL
   SELECT * FROM sqlite_temp_master)
  WHERE type!='meta'
  ORDER BY tbl_name, type DESC, name

Или, если желаете получить схему единственной таблицы и, потому, ввели аргумент, запрос будет следующим: 
  SELECT sql FROM
   (SELECT * FROM sqlite_master UNION ALL
   SELECT * FROM sqlite_temp_master)
  WHERE tbl_name LIKE '%s' AND type!='meta'
  ORDER BY type DESC, name

Разумеется, строка %s в этом запросе заменяется на аргумент ".schema". Заметьте, что аргумент команды ".schema" находится в правой части SQL-оператора "LIKE". Значит, вы можете использовать шаблон имен таблиц. Например, чтобы получить схемы всех таблиц, в именах которых содержится строка "abc", вы должны ввести: 

  sqlite> .schema %abc% 

Команда ".table" также может принимать шаблон в своем первом аргументе. Если вы указываете аргумент для команды ".table", к нему в начало и в конец присоединяется "%", а к запросу добавляется оператор "LIKE". Это позволяет вам получить список только тех таблиц, которые соответствуют заданному запросу. 

Команда ".databases" показывает список всех баз данных, открытых в текущем соединении. Их всегда будет не менее 2. Первая, - "main", - текущая открытая база данных. Вторая, - "temp", - база данных, используемая для временных таблиц. Но в списке могут быть и дополнительные базы данных, включенные с помощью инструкции "ATTACH". В первом выведенном столбце содержатся имена включенных баз данных, во втором столбце - имена файлов этих баз. 
 sqlite> .databases 

===== Конвертация всей базы данных в текстовый файл ASCII =====

Используйте команду ".dump" для конвертации всего содержимого базы данных в единственный текстовый файл ASCII. Потом средствами программы sqlite этот файл может быть конвертирован обратно в базу данных. 

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

  $ echo '.dump' | sqlite ex1 | gzip -c >ex1.dump.gz 

Таким способом будет создан файл ex1.dump.gz, содержащий все необходимое для реконструкции базы данных позднее или на другой машине. Для реконструкции базы введите: 

  $ zcat ex1.dump.gz | sqlite ex2 

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

  $ createdb ex2
  $ echo '.dump' | sqlite ex1 | psql ex2 

Вы можете проделать обратное, и с помощью утилиты pg_dump почти полностью экспортировать (но не всю) базу данных PostgreSQL в базу SQLite. К сожалению, когда pg_dump записывает информацию о схеме базы, то использует такой синтаксис SQL, который SQLite не понимает. Поэтому вы не сможете направить вывод pg_dump непосредственно в sqlite. Но вы можете пересоздать схему отдельно, и использовать pg_dump с опцией -a для получения собственно данных из базы PostgreSQL, которые можете непосредственно импортировать в SQLite. 

  $ sqlite ex3 <schema.sql
  $ pg_dump -a ex2 | sqlite ex3 

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

Команда с точкой ".explain" может быть использована в режиме вывода "column" с подходящей шириной столбцов для просмотра вывода команды "EXPLAIN". Команда "EXPLAIN" в SQLite является специфическим расширением SQL, используемое для отладки. Если перед некоторым правильным SQL стоит "EXPLAIN", то эта команда SQL парсится и анализируется, но не выполняется. Вместо этого в результате запроса выдается последовательность инструкций виртуальной машины, которая используется для выполнения команды SQL. Например: 

  sqlite> .explain
  sqlite> explain delete from tbl1 where two<20;
  addr opcode  p1  p2  p3
  ---- ------------ ----- ----- -------------------------------------
  0  ListOpen  0  0
  1  Open   0  1  tbl1
  2  Next   0  9
  3  Field   0  1
  4  Integer  20  0
  5  Ge   0  2
  6  Key   0  0
  7  ListWrite  0  0
  8  Goto   0  2
  9  Noop   0  0
  10 ListRewind 0  0
  11 ListRead  0  14
  12 Delete  0  0
  13 Goto   0  11
  14 ListClose  0  0

Команда ".timeout" устанавливает промежуток времени, в течение которого программа sqlite будет ждать снятия блокировки доступа с файла, прежде чем вернет ошибку. Умолчальное значение опции "timeout" есть ноль, так что ошибка возвращается немедленно, если блокирована нужная таблица базы или индекс. 

Под конец упомянем команду ".exit", которая приводит к завершению программы sqlite. 

===== Используем sqlite в скрипте shell =====

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

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

===== Завершение команд shell =====

Команды SQLite обычно заканчиваются точкой с запятой. В shell-е вы также можете использовать слово "GO" (без учета регистра) или символом слэша "/" в строке, завершающей команду, как это используется для SQL Server-а или Oracle соответственно. Это не будет работать в sqlite_exec(), так как shell переводит их в точку с запятой прежде, чем подставит их в эту функцию.