Система управления базами данных SQLite. Изучаем язык запросов SQL и реляционные базы данных на примере библиотекой SQLite3. Курс для начинающих.

Привет, посетитель сайта ZametkiNaPolyah.ru! Продолжаем рубрику реляционные базы данных и начинаем новый раздел библиотека SQLite.  В этой записи мы поговорим про возможности администрирования и управления базами данных, которые есть в библиотеки SQLite3.  Замечу, что таких возможностей не так уж и много, так как SQLite — это встраиваемая СУБД, которая не предполагает наличие административной части. Во многих больших и известных СУБД возможности по администрирование и управлению пользователями и самими базами данных довольно широкие, так как эти СУБД работают по принципу клиент-сервер. SQLite не работает по данному принципу.

Итак, в этой записи мы поговорим о том, как мы можем управлять пользователями в базах данных SQLite. Затем разберемся с процессом создания баз данных в SQLite (в SQLite нет команды CREATE DATABASE, опять же, это потому, что SQLite является встраиваемой СУБД), поговорим о том, как подключать и отключать базы данных в рамках одного соединения (для этого есть специальные команды ATTACH DATABASE и DETACH DATABASE). Также мы рассмотрим SQL команду VACUUM, которая позволяет заново собрать базу данных (если можно так сказать, сделать дефрагментацию файла базы данных). А в завершении статьи мы поговорим про импорт базы данных в SQLite, возможности резервного копирования баз данных и о том, как удалить базу данных (команды DROP DATABASE в SQLite нет).

Управление пользователя в SQLite3 и их правами доступа

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

Итак, в SQLite нет возможности администрировать и управлять пользователями базы данных за исключением среды, в которой библиотека SQLite3 работает. Но мы можем, например, создавать VIEW в базе данных и не создавать для VIEW различные триггеры, которые позволяют манипулировать данными в базе данных.

При этом мы можем написать программный код так, чтобы он работал только с представлениями (если у представлений нет INSTEAD OF триггеров, то для него доступна только команда SELECT), таким образом мы ограничим количество команд доступных пользователям.

Создание базы данных в SQLite

Здесь мы опишем три способа создания базы данных в SQLite. Отметим, что в SQLite3 нет команды CREATE DATABASE. Команда  CREATE в SQLite создает любые объекты базы данных, но не саму базу данных. Итак, мы помним, что при запуске шелла sqlite3.exe мы можем передать в качестве параметра имя базы данных. Поэтому первый способ создания базы данных SQLite заключает в том, чтобы передать параметр команде, позволяющей запустить шелл:

В данном случае мы создали базу данных с именем mydb и расширением sqlite3. В этом мы можем легко убедиться, воспользовавшись dot-командой .database:

[php]

sqlite> .database

seq  name             file

---  ---------------  ----------------------------------------------------------

0    main             c:\sqlite\mydb.sqlite3

[/php]

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

Мы создали файл mydatabase.db3 и сохранили его в папку: c:\sqlite. Этот файл еще не является файлом базы данных SQLite3, так как у него нет служебных заголовков. Давайте это исправим, запустим sqlite3 без параметров и откроем созданный файл при помощи терминала:

[php]

c:\sqlite>sqlite3

SQLite version 3.12.2 2016-04-18 17:30:31

Enter «.help» for usage hints.

Connected to a transient in-memory database.

Use «.open FILENAME» to reopen on a persistent database.

sqlite> .open mydatabase.db3

sqlite> .database

seq  name             file

---  ---------------  ----------------------------------------------------------

0    main             c:\sqlite\mydatabase.db3

[/php]

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

Для создания базы данных в SQLite мы можем использовать команду .save, которой можем передать в качестве параметра имя файла базы данных, давайте сохраним нашу базу данных:

[php]

sqlite> .save testsavedb.sample

[/php]

Если вы откроете рабочую папку, то увидите, что в ней появится новый файл, в нашем случае файл будет называться testsavedb, а его расширение будет .sample. Запомните, в SQLIte3 нет команды CREATE DATABASE, базы данных здесь не создаются средствами языка запросов SQL. В SQLite их можно создавать только при помощи административных возможностей библиотеки.

Управление базами данных в SQLite3

В SQLite есть возможности по управлению базами данных посредствам языка запросов SQL. Давайте посмотрим на возможности управления базами данных в SQLite3

Подключение базы данных: реализация SQL команды ATTACH DATABASE

Итак, первое, о чем стоит сказать, так это о том, что в SQLite есть специальная SQL команда ATTACH DATABASE, которая позволяет работать с несколькими базами данных в одном соединение. Давайте рассмотрим эту возможность администрирования баз данных. У нас есть три недавно созданных файла баз данных: testsavedb.sample, mydatabase.db3 и mydb.sqlite3. В данный момент мы работаем с файлом testsavedb.sample. Давайте воспользуемся SQL командой ATTACH DATABASE, чтобы подключить две оставшиеся базы данных:

[php]

ATTACH DATABASE ‘mydatabase.db3’ AS ‘first.db1’;

ATTACH DATABASE ‘mydb.sqlite3’;

[/php]

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

[php]

ATTACH DATABASE ‘mydb.sqlite3’ AS ‘second.db2’;

[/php]

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

[php]

sqlite> .database

seq  name             file

---  ---------------  ----------------------------------------------------------

0    main             c:\sqlite\mydatabase.db3

2    first.db1        c:\sqlite\mydatabase.db3

3    second.db2       c:\sqlite\mydb.sqlite3

[/php]

Мы видим, что у подключенных баз данных есть псевдонимы, а также указан путь к файлу базы данных SQLite3. Теперь, если мы захотим поработать с той или иной базой данных, то нам следует использовать квалификатор или полное имя объекта базы данных, например, давайте создадим таблицу в базе данных first.db1:

[php]

CREATE TABLE ‘first.db1’.testtable (a, b, c, d);

[/php]

И попробуем создать таблицу в главной базе данных, которая называется main:

[php]

CREATE TABLE exmptbl (a, b, c, d);

CREATE TABLE main.exmptbl (a, b, c, d);

[/php]

Вторая команда CREATE выполнена не будет, так как если мы не указываем имя базы данных, то SQLite по умолчанию работает с главной базой данных main. Квалификатор это: database_name.table_name. Но обратите внимание, если во всех трех базах данных у нас будет таблица с уникальным именем, то к ней мы сможем обращаться без использования квалификатора, SQLite нас поймет.

Отключение базы данных: реализация SQL команды DETACH DATABASE

Так же мы можем отключать базы данных от текущего соединения при помощи SQL команды DETACH DATABASE. Команде DETACH DATABASE мы должны передать псевдоним, который мы использовали в команду ATTACH. Давайте отключим все подключенные ранее базы данных:

[php]

DETACH DATABASE ‘first.db1’;

DETACH DATABASE ‘second.db2’;

DETACH DATABASE ‘main’;

[/php]

SQLite отключит все базы данных, кроме main. Main является основной базой данных в SQLite и ее мы никогда отключить не сможем, даже если мы явно не указали файл базы данных, с которым будем работать.

Стоит вспомнить о том, что все команды манипуляции данными (за исключением команды SELECT) и все команды определения данных работают в SQLite как транзакции, поэтому стоит отметить, что свойство атомарности при работе с несколькими базами данных в одном соединение сохраняется.

Повторная сборка базы данных: реализация SQL команды VACUUM. Дефрагментация базы данных в SQLite

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

Все мы знаем, что дефрагментация диска – это процесс оптимизации его логической структуры с целью увеличения скорости доступа к объектам файловой системы (папкам и файлам).  Примерно тоже самое делает команда VACUUM с базами данных SQLite3. Допустим, у нас есть файл базы данных, с которым мы постоянно работаем: удаляем строки, модифицируем значения в таблица, добавляем новые строки в таблицу. При этом стоит помнить, что любая СУБД – это в первую очередь абстракция над физическими данными, которая позволяет нам с ними работать, как с таблицами.

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

Команда VACUUM позволяет это исправить. В SQLite команда VACUUM может быть применена только к базе данных main. Данная команда помещает содержимое основной базы данных во временные файлы, а затем собирает новый файл базы данных из временных файлов, в котором данные упорядочены и записаны последовательно.

Стоит обратить внимание на то, что команда VACUUM может нарушить внутренние индексы таблицы в SQLite, если в таблице нет ограничения первичного ключа PRIMARY KEY. Причем ключевой атрибут таблицы должен быть объявлен, как INTEGER PRIMARY KEY (в этом случае он совпадает со столбцом ROWID).

Команду VACUUM в SQLite мы можем использовать следующим образом:

[php]

VACUUM;

database_name VACUUM;

VACUUM table_name;

[/php]

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

В SQLite3 есть специальные команды PRAGMA, которые реализуют команду Auto-VACUUM, ее не стоит путать с командой VACUUM, так как Auto-VACUUM не занимается повторной сборкой базы данных, а лишь перемещает свободные области (пустые страницы) в конец файла базы данных, тем самым освобождая место на жестком диске.

[php]

sqlite> PRAGMA auto_vacuum = NONE;  — 0 отключить auto-vacuum

sqlite> PRAGMA auto_vacuum = INCREMENTAL;  — 1 включить инкрементный auto-vacuum

sqlite> PRAGMA auto_vacuum = FULL;  — 2 включить полный auto-vacuum

[/php]

Таким образом вы можете осуществлять пересборку базы данных в SQLite и оптимизировать работу базы данных.

Как сделать резервную копию базы данных SQLite. Бэкап базы данных SQLite

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

Итак, у нас есть команда .dump, которая делает дамп базы данных в виде SQL-скрипта, давайте попробуем команду в деле:

[php]

sqlite> .dump

PRAGMA foreign_keys=OFF;

BEGIN TRANSACTION;

CREATE TABLE table1 (a,b,c,d);

CREATE TABLE testtable (a, b, c, d);

CREATE TABLE exmptbl (a, b, c, d);

COMMIT;

[/php]

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

[php]

.exit

sqlite3 mydatabase.db3 .dump > res.sql

[/php]

Теперь в нашей папке появится бэкап базы данных в виде SQL-скрипта. Давайте создадим резервную базу данных из файла с расширением .sql, воспользовавшись тем, что мы можем передавать разные параметры при запуске терминала:

[php]

sqlite3 res.db3 < res.sql

[/php]

Есть еще один способ создания резервной копии базы данных в SQLite. У нас есть специальная команда .output, которая позволяет изменить место вывода данных (по умолчанию данные выводятся на экран):

[php]

.output my.sql  -- данные будут выводиться в файл my.sql

.dump — делаем дамп данных

.output stdout — возвращаем вывод данных на экран

[/php]

Таким образом мы сделали резервную копию базы данных в файле my.sql. Еще один способ сделать бэкап базы данных заключается в использовании команды .backup:

[php]

.backup backup.txt

[/php]

Если у вас подключено несколько баз данных, то резервная копия будет создана только для базы данных main. Также стоит отметить, что команда backup создает файл базы данных, а не SQL-скрипт, поэтому называйте файлы осмысленно, а не как в примере.

Импорт баз данных SQLite

В SQLite можно осуществлять импорт данных в базу данных при помощи специальной команды .import. Данная команда может работать только с таблицами, а импортировать данные в таблицы базы данных SQLite команда .import может только из файлов с расширением .csv, общий синтаксис импорта данных в таблицу базы данных SQLite выглядит следующим образом:

[php]

.import file_name.csv table_name

[/php]

Если мы хотим импортировать данные из файла с расширением .sql, то можно использовать команду .read. В этом случае импорт будет осуществлен для всей базы данных, а не для отдельной таблицы.

Удаление базы данных SQLite

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