Рубрика SQLite на сайте: http://zametkinapolyah.ru/zametki-o-mysql/sqlite/
Текстовая версия: http://zametkinapolyah.ru/zame....tki-o-mysql/tema-13-
Паблик блога в ВК: https://vk.com/zametkinapolyah
Тематическая группа в ВК: https://vk.com/zametki_bd

Помощь проекту:
WMR: R288272666982
WMZ: Z293550531456
Яндекс.Деньги: 410011531129223

-- Редактирование VIEW при помощи INSTEAD OF триггера в SQLite


-- В SQLite нет возможности редактировать VIEW, Но мы можем манипулировать данными VIEW, которые хранятся в представлениях (данное выражение не совсем корректно, так как данные в представлениях не хранятся, это всего лишь результирующая таблица запроса SELECT) при помощи INSTEAD OF триггера.


-- UPDATE VIEW
CREATE TRIGGER trigg_name
INSTEAD OF UPDATE OF column_name ON view_name
BEGIN
-- делаем команду UPDATE для таблицы, на основе которой создана VIEW
END;


-- INSERT VIEW
CREATE TRIGGER trigg_name
INSTEAD OF INSERT ON view_name
BEGIN
-- делаем команду INSERT для таблицы, на основе которой создана VIEW
END;


-- DELETE VIEW
CREATE TRIGGER trigg_name
INSTEAD OF DELETE ON view_name
BEGIN
-- делаем команду DELETE для таблицы, на основе которой создана VIEW
END;

-- создадим представление
CREATE VIEW vw_city AS
SELECT city.name AS gorod, city.population AS naselenie
FROM city LIMIT 10;

-- посмотрим на нашу VIEW
SELECT * FROM vw_city;

-- Попробуем отредактировать VIEW
CREATE TRIGGER update_vw_city
INSTEAD OF UPDATE ON vw_city
BEGIN
UPDATE city SET name = NEW.gorod WHERE population = NEW.naselenie;
UPDATE city SET population = NEW.naselenie WHERE name = NEW.gorod;
END;

UPDATE vw_city SET gorod = 'Новое название города'
WHERE naselenie = 234323;

UPDATE vw_city SET naselenie = 15
WHERE gorod = 'Kabul';

SELECT * FROM vw_city;

SELECT * FROM city LIMIT 10;

-- по аналогии вы можете сделать свои триггеры для редактирования VIEW

Рубрика SQLite на сайте: http://zametkinapolyah.ru/zametki-o-mysql/sqlite/
Текстовая версия: http://zametkinapolyah.ru/zame....tki-o-mysql/tema-13-
Паблик блога в ВК: https://vk.com/zametkinapolyah
Тематическая группа в ВК: https://vk.com/zametki_bd

Помощь проекту:
WMR: R288272666982
WMZ: Z293550531456
Яндекс.Деньги: 410011531129223

-- Функция RAISE () и SQL триггеры в базах данных SQLite

-- к сожалению, ни одна в мире СУБД не знает, например, что Хемингуэй написал «Старик и море», а Исаак Ньютон – это физик, поэтому когда мы наполняем наши таблицы данными, могут возникать не очевидные ошибки с точки зрения логики реляционных баз данных, от которых не сможет защитить ни одна нормальная форма.
-- Например, когда мы говорили про внешние ключи, мы реализовывали связь один ко многим между таблицами, для этого мы создавали результирующую таблицу и я демонстрировал пример неудачно добавления данных, при котором получалось, что Джек Лондон написал «Войну и мир». Такие ошибки могут происходить довольно часто и их можно назвать конфликтами в базе данных.
-- Конфликт в базе данных не стоит путать с аномалией, потому что с точки зрения логики СУБД ничего криминального не происходит, а вот с точки зрения человека, знающего предметную область происходит существенная ошибка, которая может ввести человека в ступор.
-- Избавиться от подобных конфликтов мы можем при помощи триггеров и функции RAISE ()

/*
Отметим, что RAISE () – это специальная функция, которая используется только вместе с триггерами, ее синтаксис вы найдете на рисунке ниже.

Функция RAISE может принимать два аргумента. Первый аргумент описывает действие при возникновении конфликта:
Значение IGNORE. Говорит SQLite3 о том, чтобы она игнорировала строку породившую конфликт и продолжала выполнение последующих операций.
Значение ROLLBACK. ROLLBACK говорит о том, что SQLite должна откатить все операции к исходному состоянию при возникновении конфликта. При этом пользователь может изменить и повторить запрос. Не путайте с командой ROLLBACK, которая откатывая транзакцию
Значение ABORT. Данное значение похоже на ROLLBACK, но разница в том, что оно отменяет не все выполненные ранее SQL запросы, а только тот запрос, при котором возникла конфликтная ситуация.
Значение FAIL. Данное значение говорит СУБД о том, что нужно прервать выполнение текущей операции и сохранить результаты успешных операций, при этом операции, следующие за конфликтной, выполнены не будут.
*/

-- Создадим таблицы и наполним их данными
PRAGMA foreign_keys=on;
CREATE TABLE books(
id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
count_page INTEGER NOT NULL),
price REAL
);

CREATE TABLE auth(
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
age INTEGER
);

CREATE TABLE auth_book (
auth_id INTEGER NOT NULL,
books_id INTEGER NOT NULL,
FOREIGN KEY (auth_id) REFERENCES auth(id)
FOREIGN KEY (books_id) REFERENCES books(id)
);



INSERT INTO books (id, title, count_page, price)
VALUES (1, 'Белый клык', 287, 300.00);

INSERT INTO books (id, title, count_page, price)
VALUES (2, 'Война и мир', 806, 780.00);

INSERT INTO books (id, title, count_page, price)
VALUES (3, '12 стульев', 516, 480.00);

-- Затем добавим несколько авторов
INSERT INTO auth (id, name, age)
VALUES (1, 'Джек Лондон', 40);

INSERT INTO auth (id, name, age)
VALUES (2, 'Лев Толстой', 82);

INSERT INTO auth (id, name, age)
VALUES (3, 'Илья Ильф', 39);

INSERT INTO auth (id, name, age)
VALUES (4, 'Евгений Петров', 38);

-- Мы не стали наполнять таблицу-справочник, которая реализует связь многие ко многим
-- Давайте сперва напишем триггер, который будет проверять добавляемые значения

1 2

CREATE TRIGGER books_result BEFORE INSERT
ON auth_book
BEGIN
SELECT RAISE(FAIL, 'Произошла ошибка, вы неправильно связали автора и книгу') FROM auth_book
WHERE (NEW.auth_id = 1 AND books_id = 2) OR (NEW.auth_id = 1 AND NEW.books_id = 3) OR
(NEW.auth_id = 2 AND NEW.books_id = 3) OR (NEW.auth_id = 2 AND NEW.books_id = 3) OR
(NEW.auth_id = 3 AND NEW.books_id = 2) OR (NEW.auth_id = 3 AND NEW.books_id = 1) OR
(NEW.auth_id = 4 AND NEW.books_id = 2) OR (NEW.auth_id = 4 AND NEW.books_id = 1);
END;

-- Проверим работу триггера
INSERT INTO auth_book (auth_id, books_id)
VALUES (1, 1);

INSERT INTO auth_book (auth_id, books_id)
VALUES (2, 2);

INSERT INTO auth_book (auth_id, books_id)
VALUES (3, 3);

INSERT INTO auth_book (auth_id, books_id)
VALUES (4, 3);

INSERT INTO auth_book (auth_id, books_id)
VALUES (1, 2);


SELECT * FROM auth_book;

Помощь проекту:
WMR: R288272666982
WMZ: Z293550531456
Яндекс.Деньги: 410011531129223

Рубрика SQLite на сайте: http://zametkinapolyah.ru/zametki-o-mysql/sqlite/
Текстовая версия: http://zametkinapolyah.ru/zame....tki-o-mysql/chast-11
Паблик блога в ВК: https://vk.com/zametkinapolyah
Тематическая группа в ВК: https://vk.com/zametki_bd

1. Ускоряем выборку данных в SQL: для чего нужны индексы в базах данных.

2. Удаление и создание SQL индексов в базе данных.

3. Частичные и композитные индексы в базах данных: как создать индекс из нескольких столбцов.

4. Работа с индексами в базах данных SQLite. Уникальные индексы и переиндексация таблиц.

5. Когда не стоит индексировать таблицы базы данных.

6. SQL таблицы WITHOUT ROWID и столбец ROWID в базах данных SQLite

7. Алгоритм B-tree. За счет чего индексы ускоряют выборку данных из базы данных?

Помощь проекту:
WMR: R288272666982
WMZ: Z293550531456
Яндекс.Деньги: 410011531129223

Рубрика SQLite на сайте: http://zametkinapolyah.ru/zametki-o-mysql/sqlite/
Текстовая версия: http://zametkinapolyah.ru/zame....tki-o-mysql/chast-11
Паблик блога в ВК: https://vk.com/zametkinapolyah
Тематическая группа в ВК: https://vk.com/zametki_bd

-- Зачем нужны индексы?
/*


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

Типичным примером индекса в базе данных является первичный ключ (столбец PEIMARY KEY).

Поскольки SQL индекс - это объект базы данных, то к нему можно применять некоторые SQL команды, например,
мы можем создавать и удалять индексы.

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

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

Индексы очень ускоряют выполнения команд SELECT за счет того,
что реализованы они, на самом деле, при помощи быстрого алгоритма поиска информации,
одним из самых популярных таких алгоритмов является алгортим Б-дерева, но об этом мы
поговорим несколько позже. Но, поскольку индексы ускоряют выборку, вернее потому,
но, если где-то что-то прибыло, то обязательно где-то что-то убудет, в природе по-другому не бывает,
индексы ускоряя выборку данных, замедляют следующие SQL операции:

- добавление строк в таблицы;
- удаление данных из таблиц;
- и модификацию данных.

Если алгоритм индексов реализован в виде B-tree (чаще все именно этот алгоритм), то замеделние других
операций манипуляции данными происходит из-за того, что данный алгоритм требует, чтобы данные были
упорядочены, а само дерево сбалансированным, поэтому СУБД при выполнении этих операций приходится
поддерживать дерево в актуальном/сбалансированном состояние, а на это требуются вычислительные мощности.


Если говорить просто, то при создании индекса мы создаем еще одну таблицу, в которой хранятся значения
индексируемого столбца в упорядоченном виде и при этом СУБД вместо того, чтобы перебирать данные в исходной
таблице, пользуются таблицей индексов, в которой хранятся ссылки на исходные таблицы, за счет этого происходит
уменьшение количества операций по перебору и сравнению значений с искомым.

*/

Рубрика SQLite на сайте: http://zametkinapolyah.ru/zametki-o-m...
Текстовая версия: http://zametkinapolyah.ru/zame....tki-o-mysql/tema-15-
Паблик блога в ВК: https://vk.com/zametkinapolyah
Тематическая группа в ВК: https://vk.com/zametki_bd

Помощь проекту:
WMR: R288272666982
WMZ: Z293550531456
Яндекс.Деньги: 410011531129223

Ранее мы упоминали о том, что транзакции должны быть изолированы, а также мы сказали, что полная изоляция транзакции – очень дорогая операция, поэтому было принято разделение по степени изолированности. Чем выше уровень изолированности транзакции, тем выше точность и согласованность данных, но минус в том, что может уменьшится число параллельных транзакций, которые выполняются СУБД.

Также при выполнении параллельных транзакций в базе данных могут возникать некоторые проблемы.

1. Потерянное обновление (lost update). Если две или более, запущенных параллельно транзакции пытаются модифицировать одни и те же данные, то все вносимые изменения, кроме первой транзакции, будут потеряны.
2. Неповторяющееся чтение (non-repeatable read). При повторном чтении в рамках одной транзакции ранее прочитанные данные оказываются изменёнными.
3. Грязное чтение (dirty read). Данная проблема возникает в том случае, когда вы делаете выборку данных, которые были изменены транзакцией, но в дальнейшем произойдет откат транзакции и эти изменения не подтвердятся.
4. Фантомное чтение (phantom reads). Представим, что у нас запущено две транзакции, первая лишь читает данные из базы данных, вторая манипулирует данными, например: добавляет строки, удаляет данные или их модифицирует. Допустим, что в первой транзакции условия выборки данных всегда одинаковые, но результаты могут оказаться разными, так как вторая транзакция изменяет данные в таблицах.

Справиться с подобными проблемами при выполнении параллельных транзакций помогают блокировки и изолированность транзакций.

Рубрика SQLite на сайте: http://zametkinapolyah.ru/zametki-o-m...
Текстовая версия: http://zametkinapolyah.ru/zame....tki-o-mysql/tema-15-
Паблик блога в ВК: https://vk.com/zametkinapolyah
Тематическая группа в ВК: https://vk.com/zametki_bd

Помощь проекту:
WMR: R288272666982
WMZ: Z293550531456
Яндекс.Деньги: 410011531129223

В информатике есть такое понятие как уровни изолированности транзакций, которые помогаю справляться с вышеперечисленными проблемами. Стандарт SQL-92 выделяет четыре уровня изолированности транзакций в реляционных базах данных. Чем выше уровень изолированности, тем меньше проблем может возникнут, а это значит, что ваша база данных более надежна.

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

1. Read uncommitted (чтение незафиксированных данных). Уровень изолированности Read uncommitted или чтение незафиксированных данных – это самый низший уровень изолированности транзакций. Данный уровень справляется с проблемами потерянного обновления. Обычно этот уровень реализуется путем блокировки таблиц для других транзакций. Например, выполняется первая транзакция и, пока она выполняется, ни одна другая транзакция не может изменять данные в этой таблице, а может их только читать. При этом, как только завершится первая транзакция, таблица станет доступна для второй транзакции, которая может изменять данные. Таким образом при уровне изоляции Read uncommitted транзакции будут выполняться последовательно и ни одно изменение потеряно не будет. Но в то же самое время любая другая транзакция может выполнять чтение данных из этой таблицы (даже тех данных, которые еще не были подтверждены командой COMMIT).

2. Read committed (чтение фиксированных данных). Данный уровень изолированности транзакций решает проблему грязного чтения данных. Но уровень изолированности Read committed или чтение фиксированных данных может быть реализован двумя способами.

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

2.2 Второй подход или второй способ изоляции основан на версионности данных. СУБД создает новую версию строки для транзакции при каждом изменении данных строки. С этой новой версией продолжает работать та транзакция, которая ее создала, но любая другая транзакция видит строку в том, виде, в котором она была зафиксирована. Этот способ гораздо быстрее первого, но требует гораздо большего объема оперативной памяти, так как «новые версии строк» хранятся в оперативной памяти до тех пор, пока они не будут подтверждены.
3. Repeatable read (повторяемость чтения). Уровень изоляции транзакции Repeatable read или повторяемость чтения делает так, что транзакция, изменившая данные, не видит своих изменений, до тех пор, пока они не будут подтверждены. При этом никакая другая транзакция не сможет вносить изменения, пока не будет завершена первая транзакция.

4. Serializable (упорядочиваемость). Уровень изоляции транзакции Serializable или упорядочиваемость – самый высокий уровень изолированности транзакций, описанный в стандарте SQL-92. На этом уровне транзакции не подвержены проблемам фантомного чтения, так как уровень Serializable (упорядочиваемость) делает их, можно сказать, последовательными. На данном уровне транзакции максимально изолированы друг от друга.

Обратите внимание: четыре уровня изолированности транзакций описаны в стандарте SQL-92, каждая СУБД поддерживает разное количество уровней изолированности транзакций (какие-то имеют большее количество уровней, какие-то меньшее), а также в каждой СУБД реализован свой подход к изоляции транзакций. В дальнейшем мы несколько более подробно поговорим о том, как реализованы транзакции в базах данных под управлением библиотеки SQLite.

Рубрика SQLite на сайте: http://zametkinapolyah.ru/zametki-o-m...
Текстовая версия: http://zametkinapolyah.ru/zame....tki-o-mysql/tema-15-
Паблик блога в ВК: https://vk.com/zametkinapolyah
Тематическая группа в ВК: https://vk.com/zametki_bd

Помощь проекту:
WMR: R288272666982
WMZ: Z293550531456
Яндекс.Деньги: 410011531129223

-- Начать транзакцию

BEGIN TRANSACTION;
DEFERRED
IMMEDIATE
EXCLUSIVE

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

-- IMMEDIATE – в данном режим происходит блокировка базы данных, как только будет выполнена команда BEGIN. При это режим IMMEDIATE в SQLIte допускает, что другие транзакции могут читать данные из базы данных, но не записывать.

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



-- Подтверждение транзакций

COMMIT TRANSACTION;
END TRANSACTION;

-- Подтвердить изменения, внесенные транзакцией, позволяет ключевая фраза COMMIT TRANSACTION.

-- Откатить транзакцию

-- Откатить транзакции в базах данных под управлением SQLite позволяет ключевое слово ROLLBACK.

ROLLBACK TRANSACTION;

Рубрика SQLite на сайте: http://zametkinapolyah.ru/zametki-o-m...
Текстовая версия: http://zametkinapolyah.ru/zame....tki-o-mysql/tema-15-
Паблик блога в ВК: https://vk.com/zametkinapolyah
Тематическая группа в ВК: https://vk.com/zametki_bd

Помощь проекту:
WMR: R288272666982
WMZ: Z293550531456
Яндекс.Деньги: 410011531129223

SELECT * FROM City LIMIT 10;

-- Создаем транзакцию с именем при помощи команды SAVEPOINT
SAVEPOINT  transact1;
-- Посмотрим первых 10 записей из таблицы City
SELECT * FROM City LIMIT 10;
-- Удалим одну запись
DELETE FROM city WHERE  id = 2;
SELECT * FROM City LIMIT 10;



-- Из другого терминала:
-- Посмотрим на первых 10 записей
SELECT * FROM city LIMIT 10;
-- Пока вы не выполните команду RELEASE SAVEPOINT, строка не будет удалена

RELEASE transact1;

-- Посмотрим на первых 10 записей после подтверждения транзакции
SELECT * FROM city LIMIT 10;









--Начинаем транзакцию с именем transact1
SAVEPOINT  transact1;

-- Выберем первых 10 записей из таблицы City
SELECT * FROM city LIMIT 10;

-- Удаляем третью строку из таблицы city
DELETE FROM city WHERE  id = 3;

-- Посмотрим на первых 10 записей
SELECT * FROM city LIMIT 10;

-- Пока вы не выполните команду ROLLBACK, транзакция не будет завершена, а вы будете видеть все изменения
ROLLBACK;

-- Посмотрим на первых 10 записей после подтверждения транзакции, вы увидите
SELECT * FROM city LIMIT 10;


















--Начинаем транзакцию с именем transact1

SAVEPOINT  transact1;

-- Выберем первых 10 записей из таблицы City
SELECT * FROM city LIMIT 10;

-- Удаляем третью строку из таблицы city
DELETE FROM city WHERE  id = 3;

-- Посмотрим на первых 10 записей и увидим
SELECT * FROM city LIMIT 10;

-- Пока вы не выполните команду ROLLBACK TRANSACTION TO SAVEPOINT,
-- отката изменений не произойдет, и вы будете видеть все изменения

ROLLBACK TRANSACTION TO SAVEPOINT transact1;

-- Посмотрим на первых 10 записей
SELECT * FROM city LIMIT 10;

-- Хочу обратить ваше внимание на то, что последний запрос SELECT будет выполняться в рамках транзакции transact1, так как мы сделали откат до метки transact1, то есть отменили все запросы, начиная с SAVEPOINT transact1, но транзакцию не завершили.

Рубрика SQLite на сайте: http://zametkinapolyah.ru/zametki-o-m...
Текстовая версия: http://zametkinapolyah.ru/zame....tki-o-mysql/tema-15-
Паблик блога в ВК: https://vk.com/zametkinapolyah
Тематическая группа в ВК: https://vk.com/zametki_bd

Помощь проекту:
WMR: R288272666982
WMZ: Z293550531456
Яндекс.Деньги: 410011531129223

sqlite3 block.db3

-- Создадим таблицу и наполним ее
CREATE TABLE table1(a,b,c,d);
INSERT INTO table1 VALUES (1,2,3,4);
INSERT INTO table1 VALUES (5,6,7,8);
INSERT INTO table1 VALUES (9,10,11,12);
INSERT INTO table1 VALUES (13,14,15,16);
INSERT INTO table1 VALUES (17,18,19,20);
INSERT INTO table1 VALUES (21,22,23,24);
INSERT INTO table1 VALUES (25,26,27,28);
SELECT * FROM table1;


-- для примера запустим второй терминал и откроем нашу БД

sqlite3

.open block.db3

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

-- терминал 1

BEGIN DEFERRED TRANSACTION;


-- терминал 2
SELECT * FROM table1;
INSERT INTO table1 VALUES (33,34,35,36);

-- терминал 1
INSERT INTO table1 VALUES (29,30,31,32);
SELECT * FROM table1;

-- терминал 2
SELECT * FROM table1;
INSERT INTO table1 VALUES (33,34,35,36);

-- терминал 1
COMMIT;
SELECT * FROM table1;

-- терминал 2
SELECT * FROM table1;
INSERT INTO table1 VALUES (33,34,35,36);


----------------------------------------------------------------
-- IMMEDIATE – в данном режим происходит блокировка базы данных, как только будет выполнена команда BEGIN. При это режим IMMEDIATE в SQLIte допускает, что другие транзакции могут читать данные из базы данных, но не записывать.

-- терминал 1
BEGIN IMMEDIATE TRANSACTION;


-- терминал 2
SELECT * FROM table1;
INSERT INTO table1 VALUES (37,38,39,40);

-- терминал 1
INSERT INTO table1 VALUES (41,42,43,44);
SELECT * FROM table1;

-- терминал 2
SELECT * FROM table1;
INSERT INTO table1 VALUES (45,46,47,49);

-- терминал 1
COMMIT;
SELECT * FROM table1;

-- терминал 2
SELECT * FROM table1;
INSERT INTO table1 VALUES (50,51,51,52);




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

-- EXCLUSIVE – самый высокий уровень блокировки базы данных в SQLite. Режим EXCLUSIVE блокирует базу данных при выполнении команды BEGIN и при этом другие транзакции не могут ни читать данные из базы данных, ни уж тем более изменять данные.
-- терминал 1
BEGIN EXCLUSIVE TRANSACTION;

-- терминал 2
SELECT * FROM table1;
INSERT INTO table1 VALUES (53,54,55,56);

-- терминал 1
SELECT * FROM table1;
INSERT INTO table1 VALUES (53,54,55,56);

Рубрика SQLite на сайте: http://zametkinapolyah.ru/zametki-o-m...
Текстовая версия: http://zametkinapolyah.ru/zame....tki-o-mysql/tema-15-
Паблик блога в ВК: https://vk.com/zametkinapolyah
Тематическая группа в ВК: https://vk.com/zametki_bd

Помощь проекту:
WMR: R288272666982
WMZ: Z293550531456
Яндекс.Деньги: 410011531129223

sqlite3 block.db3

-- Создадим таблицу и наполним ее
CREATE TABLE table1(a,b,c,d);
INSERT INTO table1 VALUES (1,2,3,4);
INSERT INTO table1 VALUES (5,6,7,8);
INSERT INTO table1 VALUES (9,10,11,12);
INSERT INTO table1 VALUES (13,14,15,16);
INSERT INTO table1 VALUES (17,18,19,20);
INSERT INTO table1 VALUES (21,22,23,24);
INSERT INTO table1 VALUES (25,26,27,28);
SELECT * FROM table1;


-- для примера запустим второй терминал и откроем нашу БД

sqlite3

.open block.db3

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

-- терминал 1

BEGIN DEFERRED TRANSACTION;


-- терминал 2
SELECT * FROM table1;
INSERT INTO table1 VALUES (33,34,35,36);

-- терминал 1
INSERT INTO table1 VALUES (29,30,31,32);
SELECT * FROM table1;

-- терминал 2
SELECT * FROM table1;
INSERT INTO table1 VALUES (33,34,35,36);

-- терминал 1
COMMIT;
SELECT * FROM table1;

-- терминал 2
SELECT * FROM table1;
INSERT INTO table1 VALUES (33,34,35,36);


----------------------------------------------------------------
-- IMMEDIATE – в данном режим происходит блокировка базы данных, как только будет выполнена команда BEGIN. При это режим IMMEDIATE в SQLIte допускает, что другие транзакции могут читать данные из базы данных, но не записывать.

-- терминал 1
BEGIN IMMEDIATE TRANSACTION;


-- терминал 2
SELECT * FROM table1;
INSERT INTO table1 VALUES (37,38,39,40);

-- терминал 1
INSERT INTO table1 VALUES (41,42,43,44);
SELECT * FROM table1;

-- терминал 2
SELECT * FROM table1;
INSERT INTO table1 VALUES (45,46,47,49);

-- терминал 1
COMMIT;
SELECT * FROM table1;

-- терминал 2
SELECT * FROM table1;
INSERT INTO table1 VALUES (50,51,51,52);




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

-- EXCLUSIVE – самый высокий уровень блокировки базы данных в SQLite. Режим EXCLUSIVE блокирует базу данных при выполнении команды BEGIN и при этом другие транзакции не могут ни читать данные из базы данных, ни уж тем более изменять данные.
-- терминал 1
BEGIN EXCLUSIVE TRANSACTION;

-- терминал 2
SELECT * FROM table1;
INSERT INTO table1 VALUES (53,54,55,56);

-- терминал 1
SELECT * FROM table1;
INSERT INTO table1 VALUES (53,54,55,56);

Рубрика SQLite на сайте: http://zametkinapolyah.ru/zametki-o-mysql/sqlite/
Текстовая версия: http://zametkinapolyah.ru/zame....tki-o-mysql/tema-13-
Паблик блога в ВК: https://vk.com/zametkinapolyah
Тематическая группа в ВК: https://vk.com/zametki_bd

Помощь проекту:
WMR: R288272666982
WMZ: Z293550531456
Яндекс.Деньги: 410011531129223

/*
1. Что такое триггер и для чего нужны триггеры в реляционных базах данных?
2. Виды SQL триггеров в базах данных SQLite.
3. Удаление и создание триггеров: SQL команды DROP TRIGGER и CREATE TRIGGER.
4. SQL событие BEFORE или выполнение триггера перед выполнением SQL запроса.
5. SQL событие AFTER или выполнение триггера после выполнения SQL запроса.
6. INSERT и DELETE триггеры в базах данных SQLite.
7. UPDATE триггеры или триггеры модификации в базах данных SQLite.
8. Условие срабатывания SQL триггера: уточняющее выражение WHEN в базах данных SQLite.
9. Важные особенности использования триггеров в базах данных SQLite.
10. INSTEAD OF триггеры в базах данных SQLite и редактирование VIEW.
11. Устранение конфликтов в базах данных при помощи SQL функции RAIS и триггеров.
*/

Рубрика SQLite на сайте: http://zametkinapolyah.ru/zametki-o-mysql/sqlite/
Текстовая версия: http://zametkinapolyah.ru/zame....tki-o-mysql/tema-13-
Паблик блога в ВК: https://vk.com/zametkinapolyah
Тематическая группа в ВК: https://vk.com/zametki_bd

Помощь проекту:
WMR: R288272666982
WMZ: Z293550531456
Яндекс.Деньги: 410011531129223

Триггер – это особая разновидность хранимых процедур в базе данных. Особенность триггеров заключается в том, что SQL код, написанные в теле триггера, будет исполнен после того, как в базе данных произойдет какое-либо событие. События в базах данных происходят в результате выполнения DML команд или команд манипуляции данными. Если вы помните, то к командам манипуляции данными относятся: UPDATE, INSERT, DELETE и SELECT.

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

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

Для любой СУБД триггер – это в первую очередь объект базы данных, поэтому имя триггера должно быть уникальным во всей базе данных, SQLite в этом плане не исключение. У триггеров в SQL есть момент запуска. Момент запуска триггера можно разделить на два вида: BEFORE и AFTER. Момент запуска триггера AFTER говорит о том, что триггер будет запущен после выполнения какого-либо события в базе данных. Соответственно, момент запуска триггера BEFORE говорит о том, что триггер будет запущен до выполнения события в базе данных.

SQLite позволяет только читать данные из VIEW, в отличии, скажем, от MySQL или Oracle. Триггеры могут быть созданы для представлений с целью расширить набор операций манипуляции данными того или иного представления. Такой вид триггеров получил название INSTEAD OF триггер.

Рубрика SQLite на сайте: http://zametkinapolyah.ru/zametki-o-mysql/sqlite/
Текстовая версия: http://zametkinapolyah.ru/zame....tki-o-mysql/tema-13-
Паблик блога в ВК: https://vk.com/zametkinapolyah
Тематическая группа в ВК: https://vk.com/zametki_bd

Помощь проекту:
WMR: R288272666982
WMZ: Z293550531456
Яндекс.Деньги: 410011531129223

Итак, триггеры можно разделить на три вида по их применению:

триггер BEFORE, который срабатывает до выполнения какого-либо события в базе данных;
триггер AFTER, который срабатывает после выполнения события в базе данных;
INSTEAD OF триггер, который используется для манипуляции данными представлений.


Так же мы можем разделить триггеры по типам SQL команд:

DELETE триггер. Триггер DELETE запускается при попытке удаления данных/строк из таблицы базы данных;
UPDATE триггер. Триггер UPDATE будет запущен при попытке обновления/модификации данных в таблице базы данных;
INSERT триггер. Триггер INSERT будет запущен в том случае, если вы попытаетесь вставить/добавить строку в таблицу базы данных.
В некоторых СУБД триггер – это довольно мощное и полезное явление. Будьте аккуратны, используя триггеры, не используйте триггеры в рабочих базах данных. Перед тем, как реализовать триггер, создайте тестовую базу данных и посмотрите, что в итоге получится. Неправильный составленный триггер может навредить вашему проекту, повредив часть данных или удалив данные из базы данных.

Давайте перечислим самые распространенные функции триггеров:

Функция журнализации. Часто при помощи триггеров разработчики создают таблицы-журналы, в которых фиксируются различные изменения в базе данных. Обычно журналы создаются для фиксации изменений, которые вносят различные пользователи базы данных, таким образом можно отследить какой пользователь внес то или иное изменение в ту или иную таблицу базы данных.
Функция согласования данных. Мы уже упоминали, что триггеры используются для обеспечения целостности данных в базе данных. Мы можем связать триггер с той или иной SQL командой, таким образом, чтобы триггер проверял связанные таблицы на согласованность данных, тем самым мы обезопасим данные.
Функция очистки данных. Данная функция является подмножество функции из второго пункта. Например, вы выполняете каскадное удаление данных, в этом случае данные удаляются из таблиц, связанных ограничением внешнего ключа, но что если данные об одном объекте хранятся в несвязанных таблицах? В этом случае нас спасают триггеры. То же самое можно сказать и про операции каскадной модификации данных.
Другие функции триггеров. К сожалению, в SQLite3 нет хранимых процедур за исключением триггеров. В тех СУБД, у которых реализованы хранимые процедуры, мы можем создавать собственные процедуры в теле триггера, которые могут выполнять операции, не связанные с изменением данных.

Рубрика SQLite на сайте: http://zametkinapolyah.ru/zametki-o-mysql/sqlite/
Текстовая версия: http://zametkinapolyah.ru/zame....tki-o-mysql/tema-13-
Паблик блога в ВК: https://vk.com/zametkinapolyah
Тематическая группа в ВК: https://vk.com/zametki_bd

Помощь проекту:
WMR: R288272666982
WMZ: Z293550531456
Яндекс.Деньги: 410011531129223

CREATE TRIGGER trigger_name -- говорим СУБД о том, что хотим создать триггер
BEFORE -- триггер будет срабатывать до выполнения SQL запроса
INSERT ON table_name -- а конкретно до выполнения INSERT в таблицу table_name
BEGIN -- здесь начинается тело триггера
-- здесь хранится SQL код триггера
-- его СУБД будет выполнять, когда будет происходить
-- событие, по которому срабатывает триггер (оно описано выше)
-- SQL запросы в теле триггера разделяются точкой с запятой
INSERT... ;
INSERT... ;
DELETE... ;
END; -- а здесь тело заканчивается


CREATE TRIGGER trigger_name -- говорим СУБД о том, что хотим создать триггер
AFTER -- триггер будет срабатывать после выполнения SQL запроса
DELETE ON table_name -- а конкретно после выполнения DELETE из таблицы table_name
BEGIN -- здесь начинается тело триггера
-- здесь хранится SQL код триггера
-- его СУБД будет выполнять, когда будет происходить
-- событие, по которому срабатывает триггер (оно описано выше)
-- SQL запросы в теле триггера разделяются точкой с запятой
INSERT... ;
INSERT... ;
DELETE... ;
END; -- а здесь тело заканчивается

Мы рассмотрели внутреннее объединение таблиц, реализуемое с помощью INNER JOIN, мы рассмотрели внешнее объединение таблиц, реализуемое в базах данных SQLite при помощи LEFT JOIN, теперь перейдем к перекрестному объединению таблиц, которое реализуется при помощи ключевой фразы CROSS JOIN.

Перекрестное объединение таблиц или CROSS JOIN является симметричной операцией, как и INNER JOIN, а это означает, что порядок написания таблиц в запросе не имеет значения, результирующая таблица будет одинакова.

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

Рубрика SQLite на сайте: http://zametkinapolyah.ru/zametki-o-mysql/sqlite/
Текстовая версия: http://zametkinapolyah.ru/zame....tki-o-mysql/obedinen
Паблик блога в ВК: https://vk.com/zametkinapolyah
Тематическая группа в ВК: https://vk.com/zametki_bd

Помощь проекту:
WMR: R288272666982
WMZ: Z293550531456
Яндекс.Деньги: 410011531129223

Вы можете использовать для всех запросов на объединение таблиц ключевое слово NATURAL: NATURAL LEFT JOIN, NATURAL INNER JOIN и NATURAL CROSS JOIN. Принцип действия NATURAL JOIN. NATURAL JOIN автоматически осуществляет проверку на равенство между значениями каждого столбца, которые есть в обеих таблицах.

Вместе с ключевым словом NATURAL в базах данных SQLite вы не можете использовать предикаты ON и USING, если вы их будете использовать, то SQLite выдаст вам ошибку.

Проще говоря, СУБД сама определяет столбцы, по которым происходит объединение таблиц и делает она это очень просто: объединение происходит по столбцам с одинаковыми именами.

Рубрика SQLite на сайте: http://zametkinapolyah.ru/zametki-o-mysql/sqlite/
Текстовая версия: http://zametkinapolyah.ru/zame....tki-o-mysql/obedinen
Паблик блога в ВК: https://vk.com/zametkinapolyah
Тематическая группа в ВК: https://vk.com/zametki_bd

Помощь проекту:
WMR: R288272666982
WMZ: Z293550531456
Яндекс.Деньги: 410011531129223

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

Мы уже видели на примерах INNER JOIN, CROSS JOIN и LEFT JOIN, как объединяются две таблицы, а теперь давайте посмотрим: как с помощью JOIN объединить три и более таблиц в одну. На самом деле всё очень просто, сперва пример объединения трех SQL таблиц в одну с помощью INNER JOIN.

-- можно комбинировать вместе INNER JOIN, LEFTT JOIN и CROSS JOIN это обусловлено тем, что все действия по объединению нескольких таблиц делаются библиотекой SQLite3 по порядку (можно представить, что JOIN – это операторы сложения).
-- сначала объединятся первых две таблицы и получится результирующая, после чего результирующая таблица будет объединена с третьей таблицей и так можно до бесконечности, главное, чтобы ваши таблицы были связаны значениями.

Рубрика SQLite на сайте: http://zametkinapolyah.ru/zametki-o-mysql/sqlite/
Текстовая версия: http://zametkinapolyah.ru/zame....tki-o-mysql/obedinen
Паблик блога в ВК: https://vk.com/zametkinapolyah
Тематическая группа в ВК: https://vk.com/zametki_bd

Помощь проекту:
WMR: R288272666982
WMZ: Z293550531456
Яндекс.Деньги: 410011531129223

1. Для начала мы рассмотрели простые варианты запроса SELECT и разобрались с тем, что команда SELECT позволяет выводить данные на экран или делать выборку данных из базы данных, команда SELECT всегда нам возвращает таблицу.

2. Далее мы разобрались с уточняющей фразой (клаузлуой) WHERE, которая была введена в язык SQL для того, чтобы выполнять фильтрацию данных: с помощью WHERE мы можем задавать условия, по которым СУБД определяет выводить ей строку или нет.

3. Затем мы познакомились с группировкой данных и агрегатами или функциями агрегации. Группировка в SQL реализуется при помощи клазулы GROUP BY.

4. Далее мы узнали о том, как сделать дополнительную фильтрацию данных или как задать условие выборки данных после группировки, реализуется данная операция в SQL при помощи клазулы HAVING. HAVING работает точно так же как и WHERE, но используется после группировки.

5. Также мы узнали о запросе SELECT ORDER BY, который нам позволяет выполнять сортировку данных, сортировка выборки может происходить как в прямом пордяке, так и в обратном. Для опреления порядка используются модификаторы ASC и DESC. Также стоит заметить, что в базах данных SQLite запрос SELECT ORDER BY позволяет указать сортирующую последовательность при помощи ключевого слова COLLATE.

6. Следующим запросом был пример команды SELECT ORDER BY LIMIT. Ключевое слово LIMIT в SQL позволяет указать количество строк, которое будет выведено на экран. Также LIMIT имеет модификатор OFFSET, который позволяет сперва пропустить N-ое количество строк, а затем вывести на экран M-ое количество строк.

7. Также мы разобрались с SQL операторами AND и OR, которые позволяют усложнять и объединять условия выборки данных.

8. Еще один SQL оператор, который мы рассмотрели - это BETWEEN. Данный оператор вместе с командой SELECT позволяет указать диапазон значений, по которому будет осуществлена выборка данных из базы данных.

9. SQL оператор LIKE, который очень часто используется вместе с командой SELECT позволяет организовать поиск по шаблону или поиск подстроки в базе данных. К сожалению, в базах данных SQLite не реализованы регулярные выражения, поэтому шаблон лайк ограничен лишь двумя символами: % и _.

10. Также мы рассмотели как избавиться от дублирующихся / повторяющихся строк при помощи SQL запроса SELECT DISTINCT.

11. Еще мы поговорили как осуществить поиск в базе данных по опередленному набору значений, за это действие отвечает SQL оператор IN.

12. Далее мы посмотрели как составлять SQL подзапросы SELECT и узнали о том, что подзапросы могут быть связанными и обязательно должны быть написаны в круглых скобках. Также мы выяснили, что подзапросы в SQL очень часто составляются вместе с операторами IN и EXISTS, хотя никто не запрещает использовать подзапросы с другими операторами и даже вместо имени столбца или имени табилцы, из которой будет делаться выборка данных.

13. А здесь мы разобрались с тем как объединить результаты двух и более SQL запросов SELECT при помощи ключевого слова UNION.

14. Затем мы поговорили про сравнение результатов двух SQL запросов SELECT, познакомились с диаграмми Эйлера-Венна и узнали о ключевых словах INTERSECT и EXCEPT.

15. После чего мы уделили отдельное внимание ключевому слову INTERSECT, которое в SQL реализует операцию пересечения строк, а увидив диаграмму этого запроса, мы поняли, что такой запрос является симметричным.

16. Тут мы узнали про то как получить разность строк в базах данных SQLite при помощи ключевого слова EXCEPT и посмотрели диаграмму такого запроса для того, чтобы понять, что он несимметричный.

17. Далее мы поговорили о разновидностях запроса SELECT JOIN и узнали какими образом в SQL и в базах данных происходит объединение таблиц.

18. Поговорили про внутреннее объединение таблиц SELECT INNER JOIN и узнали о его особенностях.

19. Разобрались с внешними объединением таблиц LEFT OUTER JOIN.

20. Посмотрели на перекрестное объединение SQL таблиц CROSS JOIN.

21. Увидили особенности натурального объединения таблиц SELECT NATURAL JOIN.

22. И в завершении мы поговорили о том как объединить три и более таблицы в базах данных SQLite.

Рубрика SQLite на сайте: http://zametkinapolyah.ru/zametki-o-mysql/sqlite/
Текстовая версия: http://zametkinapolyah.ru/zame....tki-o-mysql/sql-zapr
Паблик блога в ВК: https://vk.com/zametkinapolyah
Тематическая группа в ВК: https://vk.com/zametki_bd

Помощь проекту:
WMR: R288272666982
WMZ: Z293550531456
Яндекс.Деньги: 410011531129223

Рубрика SQLite на сайте: http://zametkinapolyah.ru/zametki-o-m...Текстовая версия: http://zametkinapolyah.ru/zame....tki-o-mysql/tema-14- блога в ВК: https://vk.com/zametkinapolyahТематическая группа в ВК: https://vk.com/zametki_bdПомощь проекту:WMR: R288272666982WMZ: Z293550531456Яндекс.Деньги: 410011531129223Прежде чем переходить к работе с VIEW (представлением, вьюхой, просмотром), давайте разберемся что же такое VIEW в SQL и наших базах данных и чего в ней такого хорошего, почему рзаработчики баз данных любят их использовать? На мой взгляд в Вики определение VIEW не совсем корректное, поскольку VIEW - это не виртуальная таблица. Автор Вики взял эту информацию из документации MySQL (в ней в одном единственном месте написано:A view acts as a virtual table), в документациях других СУБД и в стандартах SQL слова VIEW и VIRTUAL TABLE в принципе близко не стоят (во всяком случае при беглом просмотре), кстати, многие сайты транслируют информацию о том, что представление в базе данных - это виртуальная таблица. К тому же, в базах данных SQLite есть специальные команды для создания виртуальных таблиц, но об этом позже. Мы разобрались с тем, чем VIEW не является. Давайте посмотрим на то, чем VIEW все-таки является. На самом деле, VIEW - это хранимый запрос в базе данных с именем. VIEW - это такой же объект базы данных, как и таблица, у представления должно быть уникальное имя во всей БД, но хранится VIEW в базе данных в виде SQL запроса. Когда мы создали представление, мы можем работать с ним как с обычной таблицей, многие СУБД позволяют модифицировать и изменять данные VIEW, а вот SQLite дает возможность только просматривать содержимое VIEW (хотя эта проблема решается при помощи триггеров). Но, например, в базах данных MySQL, вы не сможете использовать команды UPDATE, INSERT, DELETE, если SQL запрос создающий VIEW содержит: 1. Функции агрегации. 2. Ключевое слово LIMIT. 3. Клаузулу GROUP BY, позволяющую сделать группировку данных. 4. Клаузула HAVING, фильтрующая данные после группировки. 5. Операторы UNION и UNION ALL, объединяющие результаты двух запросов. 6. Любой подзапрос SELECT, даже подзапрос JOIN, объединяющий две таблицы. 7. Если запрос содержит пользовательские переменные. 8. Если нет базовой таблицы. 9. Поэтому рекомендую вам сперва ознакомиться с документацией той или иной СУБД, прежде чем начать создавать представления в базе данных.

Рубрика SQLite на сайте: http://zametkinapolyah.ru/zametki-o-m...
Текстовая версия: http://zametkinapolyah.ru/zame....tki-o-mysql/tema-14-
Паблик блога в ВК: https://vk.com/zametkinapolyah
Тематическая группа в ВК: https://vk.com/zametki_bd

Помощь проекту:
WMR: R288272666982
WMZ: Z293550531456
Яндекс.Деньги: 410011531129223

Первое и очевидное применение VIEW в базах данных заключается в том, чтобы упростить запросы на выборку данных. Ведь нам же не хочется писать полотно SELECT, которое объединяет три-четыре таблицы каждый раз, а потом еще задавать какие-нибудь условия выборки данных клаузулой WHERE? Итак, первое, для чего мы можем использовать представление – это для упрощения запросов выборки данных.

Второй пункт можно назвать безопасность. Во-первых, при помощи VIEW можно скрыть бизнес-логику и архитектуру базы данных от прикладных приложений, сделав так, что программа будет обращаться не к таблицам базы данных, а к представлениям. Во-вторых, так вы избавитесь от некоторых видов SQL-инъекций, плюсом к этому, особо талантливые программисты лишаться «чудесной возможности» конкатенировать SQL запросы (как только вы увидите, что программист конкатенирует SQL запрос, можете зарядить ему в щи с вертушки и прокричать: я угорел по базам данных, а ты не знаешь даже таких простых вещей), тем самым вы еще уменьшите вариативность атак на вашу базу данных.

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

Пожалуй, это три самых важных аспекта работы с базами данных, для которых можно и даже нужно использовать представления.