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

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

Модификация и изменение таблиц в базах данных SQLite3

Синтаксис модификации таблицы в базе данных SQLite

SQLite3 позволяет модифицировать и изменять таблицы в базах данных. К сожалению, синтаксис команды ALTER в SQLite не так богат, как в других СУБД. Вообще, для модификации таблиц в SQLite у нас есть две функции: добавить столбец в таблицу и переименовать таблицу. Общий синтаксис модификации таблиц в базах данных SQLite представлен на рисунке ниже.

Синтаксис модификации таблиц в базах данных SQLite

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

Добавить столбец в таблицу SQLite3: ключевое слово ADD COLUMN

Давайте посмотрим, как мы можем добавить столбец в таблицу SQLite. Для добавления столбца к таблице нам следует использовать конструкцию ADD COLUMN, после которого идет имя столбца и его описание. Давайте посмотри на пример такого изменения таблицы SQLite. Но для начала создадим таблицу в базе данных SQLite при помощи команды CREATE TABLE:

[php] CREATE TABLE table1 (c0); [/php]

Мы создали таблицу со столбцом c0. Давайте теперь попробуем модфицировать таблицу средствами SQLite3.

[php]ALTER TABLE table1 ADD COLUMN c1;[/php]

Для добавления столбца в таблицу SQLite позволяет не писать ключевое слово COLUMN:

[php]ALTER TABLE table1 ADD COLUMN c2;[/php]

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

[php]ALTER TABLE table1 ADD COLUMN c3 TEXT;[/php]

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

• добавляемый столбец не может быть первичным ключом или иметь ограничение UNIQUE;
• столбец, который мы добавляем в таблицу SQLite3 не может иметь в качестве значения по умолчанию CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP или какое-либо составное выражение, которое записывается в скобках;
• если вы хотите задать ограничение NOT NULL, то столбцу, который вы хотите добавить с таблицу SQLite необходимо присвоить значение по умолчанию отличное от NULL;
• если в SQLite включены внешние ключи, то столбец, который вы хотите добавить в таблицу базы данных SQLite3 должен иметь значение по умолчанию.

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

[php] ALTER TABLE table1 ADD COLUMN c4 INTEGER DEFAULT 1 NOT NULL; [/php]

По сути, это все изменения столбцов, которые может делать команда ALTER в SQLite. Давайте теперь убедимся, что столбцы были добавлены в таблицу SQLite и с ними можно работать. Для начала добавим данные в нашу таблицу при помощи команды INSERT:

[php]INSERT INTO table1 VALUES (1, 2, 3, 4);[/php]

А теперь получим данные из нашей таблицы при помощи команды SELECT:

[php]SELECT * FROM table1;[/php]

Теперь мы убедились, что все изменения таблицы были сделаны, а новые столбцы были успешно добавлены.

Изменение имени таблицы в базе данных SQLite при помощи команды ALTER

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

[php]CREATE TABLE table2 (a, b, c, d);[/php]

Мы создали простую таблицу со столбцами a, b, c, d. Теперь давайте модифицируем таблицу командой ALTER:

[php]ALTER TABLE table2 RENAME TO table02;[/php]

Убедиться в том, что имя таблицы изменилось, можно SQLite командой .tables. Для изменения имя таблицы можно использовать не только ее имя, но и квалификатор: полное имя таблицы. Давайте изменим имя таблицы SQLite при помощи квалификатора:

[php]ALTER TABLE sampledb.table02 RENAME TO table2;[/php]

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

1. Когда вы переименовываете таблицы в SQLite, то триггеры и индексы, которые были закреплены за таблицей за ней и остаются.
2. Но, если внутри триггера использовалось старое имя таблицы, то вам придется удалить старый триггер и создать новый с новым именем таблицы, который использовалось при переименовании.
3. Такая же участь будет и у представлений (просмотров, VIEW). Чтобы VIEW корректно работали после переименования таблиц в базах данных SQLite, вам необходимо их пересоздать.

Вот такие вот неудобства есть при переименовании таблиц в SQlite.

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

Как сделать любую модификацию таблицы в базе данных SQLite

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

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

Второе, что необходимо для изменения столбцов в SQLite–использовать транзакции. Использование транзакций обезопасит вашу базу данных от различных сбоев. О транзакциях мы подробно поговорим в дальнейшем.

Давайте для примера создадим таблицу example:

[php]CREATE TABLE example (a, b, c, d);[/php]

Вы создали таблицу example, но не указали: ни типы данных для столбцов, ни ограничения, ни первичный ключ, нет у вас тут и индексов и столбцы как-то непонятно называются. Хорошо, что таблица пустая, ее можно просто удалить таблицу из базы данных и создать нормальную с подробным описанием. Однако бывают ситуации, когда в таблице уже несколько тысяч записей, а команда ALTER в SQLite не позволяет: переименовывать столбцы, добавлять индексы в таблицу, изменять первичный ключ, изменять тип данных столбцов, удалять столбцы. Всё, что может команда ALTER – добавить столбец и изменить имя таблицы. Но нам этого мало, поэтому смотрим пример, сначала приведу общий синтаксис.

[php]

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE example RENAME TO _example_old;

CREATE TABLE example (

( id datatype [ NULL | NOT NULL ],

name datatype [ NULL | NOT NULL ],

...

);

INSERT INTO example (id, name, age, address)

SELECT a, b, c, d

FROM _example_old;

COMMIT;

PRAGMA foreign_keys=on;

[/php]

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

1. Отключаем внешние ключи, если они включены.
2. Запускаем транзакцию командой BEGIN TRANSACTION (кстати, это можно сделать и при помощи команды SAVEPOINT).
3. Изменяем имя существующей таблицы на любое другое.
4. Создаем новую таблицу с аналогичной, подобной, похожей структурой и даем столбцам нужные описания.
5. Добавляем данные в новую таблицу из старой при помощи команды INSERT с подзапросом SELECT.
6. Завершаем транзакцию командой COMMIT, обратите внимание: если вы используете команду ROLLBACK, то изменения не вступят в силу.
7. И затем опять включаем поддержку внешних ключей.

Думаю, принцип понятен и каких-то подробных примеров приводить не нужно, скажу лишь, что если вы хотите удалить столбец из таблицы SQLite, то при создании новой таблицы просто его не указывайте, а так же не используйте столбец в подзапросе SELECT, который хотите удалить. Если всё-таки нужны будут подробные примеры изменения и модификации столбцов и таблиц в SQLite, напишите об этом в комментариях.