# Задание 1

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

1. Дату в поле `birthday` лучше хранить в специализированных форматах для дат, например в `Date`, эти форматы больше оптимизированы для хранения и обработки дат, к тому же не получим проблему 2038 года (если приложение доживёт). К тому же, в поле день рождения нам не нужно хранить время с точностью до секунды. Формат `INT` занимает 4 байта, а `Date` -- 3 байта.

2. Поле `sex` лучше сделать с типом `BOOLEAN`, потому что биологический пол может быть только мужской или женский. И, наверное, для избежания путаницы можно назвать поле `is_male`, либо же сделать enum. Если же мы говорим про gender, то тут возможно и стоит заложить побольше вариантов: https://young.scot/get-informed/national/gender-identity-terms 😂

3. Для поля `has_work` тип данных тоже лучше сделать `BOOLEAN`.

4. В рамках данной задачи (мы не делаем поиск по имени) хранение ФИО в формате `VARCHAR` -- хорошее решение, оно экономит дисковое пространство, но если нам нужно делать частые поиски по имени, возможно, стоит использовать тип данных `CHAR`.

5. В поле `id` я бы добавил `AUTO_INCREMENT`. В этом случае задача генерации уникальной последовательности будет на стороне СУБД. Если мы, по какой-то причине не хотим использовать `AUTO_INCREMENT`, имеет смысл посмотреть в сторону типа данных `UUID`. В этом случае со стороны кода мы получим возможность знать какой будет идентификатор записи ДО добавления в базу данных.

6. В таблице `loan` нужно добавить `FOREIGN KEY` на поле `account_id` для консистентности данных.

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

CREATE TABLE `account` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`fio` VARCHAR(255),
`is_male` bool COMMENT '0 - не указан, 1 - мужчина, 2 - женщина.',
`birthdate` DATE COMMENT 'Дата в unixtime.',
`has_work` bool comment '1 - Есть работа, 0 – нет работы',
PRIMARY KEY (`id`)
);

CREATE TABLE `loan` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`account_id` INT(11),
`amount` INT(11),
CONSTRAINT loan_account_id_fk
FOREIGN KEY (account_id) REFERENCES account (id)
ON DELETE CASCADE ,
PRIMARY KEY (`id`)
);
***
> Напишите запрос, возвращающий имя, количество кредитов и общую сумму
> кредитов для работающих мужчин, имеющих не более 2-х кредитов на общую
> сумму не более 50000 т.р. Отсортируйте по возрастанию общей суммы
> кредитов.

SELECT fio,
count(l.id) AS loan_quantity,
SUM(l.amount) AS loan_sum
FROM account
JOIN loan l ON account.id = l.account_id
WHERE has_work = 1
AND is_male = 1
GROUP BY l.account_id
HAVING loan_quantity <= 2
AND loan_sum <= 50000
ORDER BY loan_sum;

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

Посмотрим как выполняется запрос без оптимизации.

mysql> explain select fio, count(l.amount) as loan_quantity, SUM(l.amount) as loan_sum from account
-> join loan l on account.id = l.account_id
-> where has_work = 1
-> group by l.account_id
-> having loan_quantity <= 2 and loan_sum <= 50000
-> order by loan_sum;
+----+-------------+---------+------------+------+--------------------+--------------------+---------+---------------------------+------+----------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+--------------------+--------------------+---------+---------------------------+------+----------+----------------------------------------------+
| 1 | SIMPLE | account | NULL | ALL | PRIMARY | NULL | NULL | NULL | 1000 | 10.00 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | l | NULL | ref | loan_account_id_fk | loan_account_id_fk | 5 | test_andromeda.account.id | 1 | 100.00 | NULL |
+----+-------------+---------+------------+------+--------------------+--------------------+---------+---------------------------+------+----------+----------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql>

Тут мы видим, что в поле `type` в первой таблице стоит значение `ALL`, это значит, что таблица полностью просканирована, так же в поле `rows` мы видим, что обработано 1000 записей. Добавим индексов.

Добавим индекс на поле `has_work`:

ALTER TABLE account ADD INDEX (has_work, is_female)
И снова проверим:

mysql> explain select fio, count(l.id) as loan_quantity, SUM(l.amount) as loan_sum from account join loan l on account.id = l.account_id where has_work = 1 group by l.account_id having loan_quantity <= 2 and loan_sum <= 50000 order by loan_sum;
+----+-------------+---------+------------+------+-----------------------+-----------------------+---------+---------------------------+------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+-----------------------+-----------------------+---------+---------------------------+------+----------+---------------------------------+
| 1 | SIMPLE | account | NULL | ref | PRIMARY,has_work | has_work | 2 | const | 317 | 100.00 | Using temporary; Using filesort |
| 1 | SIMPLE | l | NULL | ref | loan_account_id_index | loan_account_id_index | 5 | test_andromeda.account.id | 1 | 100.00 | NULL |
+----+-------------+---------+------------+------+-----------------------+-----------------------+---------+---------------------------+------+----------+---------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql>
То есть вместо полного сканирования всей таблицы мы сразу же отфильтровали примерно треть записей, при этом в колонке key появился используемый индекс.

# Задание 2

Этот код использует таблицу account из предыдущего примера

function load_account_data( $account_ids ) {
$account_ids = explode(',', $account_ids);
foreach ($account_ids as $user_id) {
$db = mysqli_connect("localhost", "test_user", "123123", "test_andromeda");
$sql = mysqli_query($db, "SELECT * FROM account WHERE id=$user_id");
while($obj = $sql->fetch_object()){
$data[$user_id] = $obj->fio;
}
mysqli_close($db);
}
return $data;
}

// Как правило, в $_GET['account_ids'] должна приходить строка
// с номерами заёмщиков через запятую, например: 1,2,17,48

$data = load_account_data( $_GET['account_ids'] );
foreach ($data as $user_id=>$name) {
echo "<a href=\"/show_user.php?id=$user_id\">$name</a>";
}

Если вам кажется, что с кодом что-то не так - проведите его рефакторинг по своему усмотрению. В комментариях опишите, что вы сделали и почему.

Отрефакторил код вот так:

<?php

CONST HOST = '172.19.0.2';
CONST USER = 'root';
CONST PASSWORD = 'password';
CONST DATABASE = 'test_andromeda';

function load_account_data(string $account_ids): array {
if (validate($account_ids) === false) {
throw new Exception('Invalid data.');
}

$sql = sprintf('SELECT id, fio FROM account WHERE id in (%s)', $account_ids);

$data = [];

foreach (get_data($sql) as $obj) {
$data[$obj->id] = $obj->fio;
}

return $data;
}

function validate(string $account_ids): bool {
if (!preg_match('/^\d+(,\d+)*$/', $account_ids)) {
return false;
}

return true;
}

function get_data(string $query): Generator {
$db = mysqli_connect(HOST, USER, PASSWORD, DATABASE);
$sql = mysqli_query($db, $query);

while($obj = $sql->fetch_object()){
yield $obj;
}

mysqli_close($db);
}

$data = load_account_data('1,3,5,6,123,56,456,12');

foreach ($data as $user_id=>$name) {
echo "<a href=\"/show_user.php?id=$user_id\">$name</a>";
}

Так как в `GET` параметре может быть максимум 2048 символов, то оператор `IN` нам подходит. Для начала отвалидируем данные, чтобы убедиться, что данные пришли именно в том формате, который нам нужен: цифра, запятая между цифрами и отсутствите запятой в конце строки. Проинициализируем переменную `$data`, чтобы в случае, если передался пустой массив мы пустой массив и вернули. Дальше отделим логику обращения в БД в другую функцию, дабы не загромождать основной код низкоуровневыми операциями. Так как мы отвалидировали входную переменную, можно вставлять её в запрос как есть, без экранирования специальных символов. Если не использовать валидацию, то можно вместо `mysqli` использовать `PDO` с возможность создания `PREPARED-STATEMENT`. Для возврата данных в основную функцию используем генераторы. Так, в том числе рекомендуется делать в Doctrine для итерирования по большим объёмам данным без загрузки в память.

# Задание 3

> Имеется урл:
>
> https://www.mydomain.com/controller/test.php?a=45&b=38&c=0&d=12&e=309&f=38&g=zed456&h=test2
>
> Напишите на PHP функцию, принимающую в качестве аргумента урл и
> возвращающую строку в качестве результата, которая:
>
> а) Удалит параметры со значением 38 б) Отсортирует параметры по
> значению в) Перенесет относительный путь к файлу
> (/controller/test.php) из исходной ссылки в новый параметр path
>
> Проверьте себя: в указанном примере функцией должно быть возвращено
>
> https://www.mydomain.com/?c=0&d=12&a=45&e=309&h=test2&g=zed456&path=%2Fcontroller%2Ftest.php

Мы предполагаем, что URL нам передаётся валидный. Поэтому код вот такой:

<?php

declare(strict_types=1);

function formatUrl(string $url): string {
$parsedUrl = parse_url($url);
$queryParams = explode('&', $parsedUrl['query']);
$arrayedQueryParams = [];

foreach ($queryParams as $theQueryParam) {
[$key, $value] = explode('=', $theQueryParam);

if ((int) $value === 38) {
continue;
}

if (is_numeric($value)) {
$value = (int) $value;
}

$arrayedQueryParams[$key] = $value;
}

uasort(
$arrayedQueryParams,
'string_num_sort'
);

$arrayedQueryParams['path'] = $parsedUrl['path'];

return sprintf(
'%s://%s/?%s',
$parsedUrl['scheme'],
$parsedUrl['host'],
http_build_query($arrayedQueryParams)
);
}

function string_num_sort($a, $b): int {
if (!is_numeric($a) && is_numeric($b)) {
return 1;
}

if (is_numeric($a) && !is_numeric($b)) {
return -1;
}

return $a <=> $b;
}

print_r(formatUrl('https://www.mydomain.com/controller/test.php?a=45&b=38&c=0&d=12&e=309&f=38&g=zed456&h=test2'));