Часть 5.3: Дата и время, как тип данных в SQLite3
Здравствуйте, уважаемые посетители сайта ZametkiNaPolyah.ru. Продолжаем изучать базы данных и наше знакомство с библиотекой SQLite3.…
Привет, посетитель сайта ZametkiNaPolyah.ru! Продолжаем изучать базы данных и наше знакомство с библиотекой SQLite3. В этой записи мы поговорим про модификацию таблиц в базах данных под управлением SQLite. На самом деле возможности SQLite по модификации таблиц: изначально можно только добавить новый столбец в таблицу базы данных и изменить имя таблицы в SQLite, но из этой записи вы узнаете об одном из приемов, который позволит вам делать любые модификации таблиц базы данных SQLite3.
Содержание статьи:
SQLite3 позволяет модифицировать и изменять таблицы в базах данных. К сожалению, синтаксис команды ALTER в SQLite не так богат, как в других СУБД. Вообще, для модификации таблиц в SQLite у нас есть две функции: добавить столбец в таблицу и переименовать таблицу. Общий синтаксис модификации таблиц в базах данных SQLite представлен на рисунке ниже.
Для того чтобы сказать SQLite о том, что мы хотим модифицировать таблицу, нам следует использовать команду ALTER TABLE, после чего мы указываем имя таблицы, которую хотим изменить или квалификатор, который говорит SQLite о том, какую таблицу в какой базе данных необходимо модифицировать. А дальше у нас два действия на выбор: мы можем добавить столбец к таблице SQLite3 и мы можем переименовать таблицу в SQLite.
Давайте посмотрим, как мы можем добавить столбец в таблицу 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 добавляет всегда в конец списка столбцов. Так же заметим, что добавлять новые столбцы можно с полным описанием, как это мы делали при создании таблиц, но тут действуют некоторые ограничения:
Поэтому, чтобы нам добавить столбец с ограничением 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]
Теперь мы убедились, что все изменения таблицы были сделаны, а новые столбцы были успешно добавлены.
Но еще команда 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, но есть здесь и несколько тонкостей:
Вот такие вот неудобства есть при переименовании таблиц в 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 (переименовывать столбцы, добавлять индексы в таблицу, изменять первичный ключ, изменять тип данных столбцы, удалять столбцы):
Думаю, принцип понятен и каких-то подробных примеров приводить не нужно, скажу лишь, что если вы хотите удалить столбец из таблицы SQLite, то при создании новой таблицы просто его не указывайте, а так же не используйте столбец в подзапросе SELECT, который хотите удалить. Если всё-таки нужны будут подробные примеры изменения и модификации столбцов и таблиц в SQLite, напишите об этом в комментариях.
Выберете удобный для себя способ, чтобы оставить комментарий