Как сообразить на двоих и куда спрятать третьего? -5


image

В самом начале своей программистской деятельности довольно часто пытался найти для себя ответ на весьма популярный вопрос. Хранить ли файлы в БД или же не стоит и почему, а если хранить то как? Не вдаваясь в подробности тернистого пути, скажу, что для себя я однозначно решил, что в MySQL(и ему подобных) этого лучше не делать. Как быть если программа-клиент подключается к удаленному серверу с MySQL, как тогда избежать хранения файлов в таблицах?

Однажды мне пришлось задаться этим вопросом, когда заказчик выкатил дополнительные требования к программе в виде незадачливого – «Ах, да! Нужно еще и файлы прикреплять». Поиск ничего интересного мне не выдал, разве что поведал о мифическом типе таблиц FileDB (для MySQL).А, ну еще там было очень много про архитектуру приложения из трех составляющих так называемую трехзвенку. Данная работа находилась тогда уже ближе к завершающему этапу и по этому, включать в архитектуру третье звено, было равносильно начинанию работы с начала. Честно говоря, абсолютно не рассчитывая на положительный результат стал пробовать решить задачу средствами самой СУБД. И так по порядку.

1) Как сохранить файл через MySQL?


Здесь приходит нам на помощь функция DUMPFILE. Попросту больше ничего из функций MySQL на диск не пишет. Данная функция, в первую очередь, предназначена для записи результатов запросов в файл. А что если мы подставим уже готовый результат в виде содержимого из нашего файла?

SELECT ‘наш_контент’ INTO DUMPFILE  ‘filedb/my_file’

И да это работает! Если путь указан относительный MySQL запишет файл относительно своего каталога «data», так же функция поддерживает указание полного пути к файлу. Вот только одно но, MySQL не может самостоятельно создавать каталоги, но об этом ниже.

2) Как сохранить связь?


Через MySQL мы не сможем получить ни размер, ни дату создания файла. Нужна вспомогательная таблица где мы будем хранить эти данные, а так же присвоим каждому файлу свой идентификатор. Я добавил таблицу ‘vfile’ и в нее такие поля: id, nm-оригинальное имя файла, len-размер,dt-дата добавления, del-метка на удаление. Зачем нужна метка на удаление, спросите Вы? Ну конечно же MySQL не умеет самостоятельно удалять файлы. Чуть позже мы с этим разберемся. Теперь для сохранения файла мы предварительно создаем запись в нашей таблице, после чего берем сгенерированный ID и подставляем его в наш запрос с DUMPFILE.

3) Организуем файлы.


Записывать все файлы в один каталог, по моему мнению, не желательно из-за ограничений файловой системы. Ограничения эти кажутся недостижимыми, но поверьте, моему опыту это только кажется и лучше быть предусмотрительным, чем воткнуться рогом в землю. По этому добавляя файл, необходимо ID последней записи из таблицы преобразовать к виду ‘filedb/1/2/3/f’ такой путь к файлу будет для записи с ID 123. Так же нам потребуется заранее подготовить каталоги и как я уже сказал, MySQL не может самостоятельно их создать. Доверим это скрипту:

#!/usr/bin/php
<?
include("/var/www/html/cor/cfg.php");
include("/var/www/html/cor/sql.php");
include("/var/www/html/cor/suit.php");
$mysqpath='/var/lib/mysql/';

$res=$mysqli->query('SELECT max(id) as mid FROM vfile');
if($row = mysqli_fetch_assoc($res))
  for($i=$row['mid'];$i<=$row['mid']+1000;$i++){
     $fpath=substr(GetFilePath($i), 0, -1);
     if(!file_exists($mysqpath.$fpath)) mkdir($mysqpath.$fpath, 0777, true);
   }
?>

И под покровом ночи наш cron будет запускать этот скрипт обеспечивая 1000 пустых каталогов на день. Здесь же мы видим функцию преобразования ID в путь к файлу GetFilePath(‘ID’). Код самой функции думаю приводить смысла нет, он достаточно прост.

4) Читаем файл.


В MySQL есть редко используемая функция LOAD _ FILE (file_name) позволяющая получить содержимое файла в наш запрос. Так же зная ID файла преобразуем его в путь и получаем содержимое запросом SELECT LOAD _FILE(file_name). Для удобства добавим пару пользовательских функций:

DELIMITER $$
CREATE FUNCTION `get_file_path`(fid INT(11)) RETURNS varchar(255) CHARSET utf8
BEGIN
DECLARE path,pathid VARCHAR(22);
DECLARE x,len INT(4);
SET path='filedb/';
SET x=1;
SET pathid=fid+'';
SET len=LENGTH(pathid);
WHILE x<=len DO
	SET path=CONCAT(path,SUBSTRING(pathid,x,1),'/');
SET x=x+1;
END WHILE;
RETURN concat(schema(),'/',path,'f');
END$$
DELIMITER ;

DELIMITER $$
CREATE FUNCTION `get_file_cont`(fid INT(11)) RETURNS longblob
BEGIN
RETURN load_file(get_file_path(fid));
END$$
DELIMITER ;

Теперь можем сразу получать содержимое файла по его ID, например:

SELECT get_file_cont(123)

5) Удаляем файлы.


У нас уже есть скрипт обслуживающий нашу структуру каталогов, просто добавим в него необходимый код.

//Пометка сирот на удаление
$mysqli->query('UPDATE vfile v SET del=1 WHERE
NOT EXISTS(SELECT * FROM event_rep WHERE v.id=f) and
NOT EXISTS(SELECT * FROM doc WHERE v.id=f) and
NOT EXISTS(SELECT * FROM docs WHERE v.id=f) and
NOT EXISTS(SELECT * FROM foto WHERE v.id=f) and
NOT EXISTS(SELECT * FROM pozdrav WHERE v.id=foto) and
NOT EXISTS(SELECT * FROM maket WHERE v.id=pdf or v.id=jpg or v.id=dop)');

//Удаление отмеченых файлов
$res=$mysqli->query('SELECT id FROM vfile WHERE del>0');
while($row = mysqli_fetch_assoc($res)) unlink($mysqpath.GetFilePath($row['id']));
$mysqli->query('DELETE FROM vfile WHERE del>0');

Т.е. чтобы удалить файл нам достаточно выставить атрибут del равным 1. Хоть удаление происходит не сразу, но и этого, на мой взгляд, вполне достаточно. Тут же, я привел пример как я удаляю «сирот» у которых нет записей в связанных таблицах. Как вариант при ручном удалении можно на время до запуска скрипта записать в файл пустое содержание уменьшив занимаемое им место.

Подводные камни


Их оказалось не много. Здесь нужно упомянуть о необходимых привилегиях пользователям СУБД. За доступ к вышеупомянутым функциям отвечает привилегия «FILE». Те кому приходилось работать с бэкапами MySQL, конечно же, наверняка узнали о директиве max_allowed_packet которая и определит максимальный размер файла который мы сможем прогнать через СУБД и положить на сервер. Изначально планировалось завернуть DUMPFILE в пользовательскую функцию, как это было сделано с LOAD_FILE(), но дампфаел наотрез отказался принимать путь, переданный в виде переменной, говорит: «Подавай мне только в кавычках сразу как есть, ни о каких переменных в качестве параметра я не слышал». «Ну и ладно!»- подумал я.




К сожалению, не доступен сервер mySQL