# Домашнее задание к занятию "6.2. SQL"
## Задача 1
Используя docker поднимите инстанс PostgreSQL (версию 12) c 2 volume,
в который будут складываться данные БД и бэкапы.
Приведите получившуюся команду или docker-compose манифест.
### Команда получилась такой.
`docker run --name postgre1 -d -e POSTGRES_HOST_AUTH_METHOD=trust -v 'C:\Users\vah\docker\07-sql:/var/lib/postgresql/data' -v 'C:\Users\vah\docker\07-sql-backup:/tmp/backup' postgres:12`
подключаюсь в ту же систему так:
`docker exec -it postgre1 psql -U postgres -d test_db`
## Задача 2
В БД из задачи 1:
- создайте пользователя test-admin-user и БД test_db
- в БД test_db создайте таблицу orders и clients (спeцификация таблиц ниже)
- предоставьте привилегии на все операции пользователю test-admin-user на таблицы БД test_db
- создайте пользователя test-simple-user
- предоставьте пользователю test-simple-user права на SELECT/INSERT/UPDATE/DELETE данных таблиц БД test_db
Таблица orders:
- id (serial primary key)
- наименование (string)
- цена (integer)
Таблица clients:
- id (serial primary key)
- фамилия (string)
- страна проживания (string, index)
- заказ (foreign key orders)
Приведите:
- итоговый список БД после выполнения пунктов выше,
- описание таблиц (describe)
- SQL-запрос для выдачи списка пользователей с правами над таблицами test_db
- список пользователей с правами над таблицами test_db
### Ответ:
Вот как я это делал:
```
CREATE DATABASE test_db;
\c test_db
CREATE USER "test-admin-user";
CREATE TABLE orders (id SERIAL PRIMARY KEY, name VARCHAR, price INT);
CREATE TABLE clients (id SERIAL PRIMARY KEY, fullname VARCHAR, country VARCHAR, zakaz_id SERIAL DROP NOT NULL, FOREIGN KEY (zakaz_id) REFERENCES orders (id));
CREATE INDEX country_idx ON clients (country);
GRANT ALL ON orders, clients TO "test-admin-user";
CREATE USER "test-simple-user";
GRANT SELECT,INSERT,UPDATE,DELETE ON orders, clients to "test-simple-user";
```
Вот список таблиц:
```
test_db=# SELECT table_name FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema','pg_catalog');
table_name
------------
orders
clients
(2 rows)
```
Вот список БД с описанием:
```
test_db=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+----------+----------+------------+------------+-----------------------+---------+------------+--------------------------------------------
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | 8113 kB | pg_default | default administrative connection database
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +| 7801 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +| 7801 kB | pg_default | default template for new databases
| | | | | postgres=CTc/postgres | | |
test_db | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | 7801 kB | pg_default |
(4 rows)
```
Список таблиц и полей:
```
test_db=# \d+
```
Детальная информация о таблице clients
```
test_db=# \d+ clients
```
Детальная информация о таблице orders
```
test_db=# \d+ orders
List of relations
Schema | Name | Type | Owner | Size | Description
--------+----------------------+----------+----------+------------+-------------
public | clients | table | postgres | 8192 bytes |
public | clients_id_seq | sequence | postgres | 8192 bytes |
public | clients_zakaz_id_seq | sequence | postgres | 8192 bytes |
public | orders | table | postgres | 8192 bytes |
public | orders_id_seq | sequence | postgres | 8192 bytes |
(5 rows)
```
Запрос и список пользователей с правами над таблицами test_db
```
SELECT table_name,grantee,privilege_type
FROM information_schema.table_privileges
WHERE table_schema NOT IN ('information_schema','pg_catalog');
table_name | grantee | privilege_type
------------+------------------+----------------
orders | postgres | INSERT
orders | postgres | SELECT
orders | postgres | UPDATE
orders | postgres | DELETE
orders | postgres | TRUNCATE
orders | postgres | REFERENCES
orders | postgres | TRIGGER
clients | postgres | INSERT
clients | postgres | SELECT
clients | postgres | UPDATE
clients | postgres | DELETE
clients | postgres | TRUNCATE
clients | postgres | REFERENCES
clients | postgres | TRIGGER
orders | test-admin-user | INSERT
orders | test-admin-user | SELECT
orders | test-admin-user | UPDATE
orders | test-admin-user | DELETE
orders | test-admin-user | TRUNCATE
orders | test-admin-user | REFERENCES
orders | test-admin-user | TRIGGER
clients | test-admin-user | INSERT
clients | test-admin-user | SELECT
clients | test-admin-user | UPDATE
clients | test-admin-user | DELETE
clients | test-admin-user | TRUNCATE
clients | test-admin-user | REFERENCES
clients | test-admin-user | TRIGGER
orders | test-simple-user | INSERT
orders | test-simple-user | SELECT
orders | test-simple-user | UPDATE
orders | test-simple-user | DELETE
clients | test-simple-user | INSERT
clients | test-simple-user | SELECT
clients | test-simple-user | UPDATE
clients | test-simple-user | DELETE
(36 rows)
```
Аналогичный запрос (видимо, в этих таблицах идентичные данные?)
```
test_db=# SELECT table_name,grantee,privilege_type
FROM information_schema.role_table_grants
WHERE table_schema NOT IN ('information_schema','pg_catalog');
```
## Задача 3
Используя SQL синтаксис - наполните таблицы следующими тестовыми данными:
Таблица orders
|Наименование|цена|
|------------|----|
|Шоколад| 10 |
|Принтер| 3000 |
|Книга| 500 |
|Монитор| 7000|
|Гитара| 4000|
Таблица clients
|ФИО|Страна проживания|
|------------|----|
|Иванов Иван Иванович| USA |
|Петров Петр Петрович| Canada |
|Иоганн Себастьян Бах| Japan |
|Ронни Джеймс Дио| Russia|
|Ritchie Blackmore| Russia|
Используя SQL синтаксис - вычислите количество записей в каждой таблице и
приведите в ответе запрос и получившийся результат.
### Ответ:
```
INSERT INTO orders (name,price) VALUES
('Шоколад',10),
('Принтер',3000),
('Книга',500),
('Монитор',7000),
('Гитара',4000);
INSERT 0 5
```
Наполнил, сначала пришлось сделать одну колонку NOT NULL
`ALTER TABLE clients ALTER COLUMN zakaz_id DROP NOT NULL;`
```
INSERT INTO clients (fullname,country,zakaz_id) VALUES
('Иванов Иван Иванович','USA',NULL),
('Петров Петр Петрович','Canada',NULL),
('Иоганн Себастьян Бах','Japan',NULL),
('Ронни Джеймс Дио','Russia',NULL),
('Ritchie Blackmore','Russia',NULL);
INSERT 0 5
```
Посчитал:
```
test_db=# select count(*) from clients;
count
-------
5
```
## Задача 4
Часть пользователей из таблицы clients решили оформить заказы из таблицы orders.
Используя foreign keys свяжите записи из таблиц, согласно таблице:
|ФИО|Заказ|
|------------|----|
|Иванов Иван Иванович| Книга |
|Петров Петр Петрович| Монитор |
|Иоганн Себастьян Бах| Гитара |
Приведите SQL-запросы для выполнения данных операций.
Приведите SQL-запрос для выдачи всех пользователей, которые совершили заказ, а также вывод данного запроса.
### Ответ:
Если я правильно понял, то вот запросы на изменение.
```
UPDATE clients
SET zakaz_id = (SELECT id FROM orders WHERE name = 'Книга')
WHERE fullname = 'Иванов Иван Иванович';
UPDATE clients
SET zakaz_id = (SELECT id FROM orders WHERE name = 'Монитор')
WHERE fullname = 'Петров Петр Петрович';
UPDATE clients
SET zakaz_id = (SELECT id FROM orders WHERE name = 'Гитара')
WHERE fullname = 'Иоганн Себастьян Бах';
```
```
select * from clients;
id | fullname | country | zakaz_id
----+----------------------+---------+----------
9 | Ронни Джеймс Дио | Russia |
10 | Ritchie Blackmore | Russia |
6 | Иванов Иван Иванович | USA | 3
7 | Петров Петр Петрович | Canada | 4
8 | Иоганн Себастьян Бах | Japan | 5
select * from orders;
id | name | price
----+---------+-------
1 | Шоколад | 10
2 | Принтер | 3000
3 | Книга | 500
4 | Монитор | 7000
5 | Гитара | 4000
```
## Задача 5
Получите полную информацию по выполнению запроса выдачи всех пользователей из задачи 4
(используя директиву EXPLAIN).
Приведите получившийся результат и объясните что значат полученные значения.
### Ответ:
```
EXPLAIN select * from clients;
QUERY PLAN
-----------------------------------------------------------
Seq Scan on clients (cost=0.00..18.10 rows=810 width=72)
(1 row)
```
**Seq Scan** — означает, что используется последовательное, блок за блоком, чтение данных таблицы `clients`
**Cost** - некая виртуальная величина призванная оценить затратность операции. Первое значение 0.00 — затраты на получение первой строки. Второе — 18.10 — затраты на получение всех строк.
Единица измерения cost – «извлечение одной страницы в последовательном (sequential) порядке». То есть оценивается и время, и использование ресурсов.
**rows** — приблизительное количество возвращаемых строк при выполнении операции Seq Scan. Это значение возвращает планировщик.
**width** - это оценка PostgreSQL того, сколько, в среднем, байт содержится в одной строке, возвращенной в рамках данной операции
Как я понял, вывод этой информации - ожидания планировщика.
А если дать команду `analyze` и повторить запрос, то количество строк будет более реалистичным, и cost поменяется - потому что БД проведёт анализ.
```
analyze clients;
EXPLAIN select * from clients;
QUERY PLAN
--------------------------------------------------------
Seq Scan on clients (cost=0.00..1.05 rows=5 width=47)
(1 row)
```
## Задача 6
Создайте бэкап БД test_db и поместите его в volume, предназначенный для бэкапов (см. Задачу 1).
Остановите контейнер с PostgreSQL (но не удаляйте volumes).
Поднимите новый пустой контейнер с PostgreSQL.
Восстановите БД test_db в новом контейнере.
Приведите список операций, который вы применяли для бэкапа данных и восстановления.
Ответ:
Бэкап
Роли
`docker exec -it postgre1 pg_dumpall -U postgres --roles-only -f /tmp/backup/roles.sql`
База
`docker exec -it postgre1 pg_dump -h localhost -U postgres -F t -f /tmp/backup/backup_1.tar test_db`
Восстановление
Запускаем новый контейнер
`docker run --name postgre2 -d -e POSTGRES_HOST_AUTH_METHOD=trust -v 'C:\Users\vah\docker\07-sql-2:/var/lib/postgresql/data' -v 'C:\Users\vah\docker\07-sql-backup:/tmp/backup' postgres:12`
Создаём базу
`docker exec -it postgre2 psql -U postgres -c "CREATE DATABASE test_db WITH ENCODING='UTF-8';"`
Восстанавливаем роли из бэкапа (если нужно)
`docker exec -it postgre2 psql -U postgres -f /tmp/backup/roles.sql`
Восстанавливаем базу из бэкапа
`docker exec -it postgre2 pg_restore -U postgres -Ft -v -d test_db /tmp/backup/backup_1.tar`
Sign in with Wallet
Connect another wallet