А что если к такой замечательной возможности MySQL как создание триггеров, способных записывать старые и новые значения данных при вставке, изменении и удалении записей добавить информацию, которой обладает php скрипт?
Триггеры MySQL знают:
* момент в который происходит изменение
* старое и новое значение
PHP знает:
* кто в данный момент залогинен
* какая страница открыта, с какой перешли
* бравзер
* IP адрес
* POST, GET
* Cookie
Каким же способом можно умудриться записать всю необходимую информацию?
Воспользуемся особенностями PHP и MySQL!
MySQL умеет создавать временные таблицы, которые живут только пока коннект к бд не закроется, а PHP как раз при открытии каждой страницы — создаёт новый коннект (в абсолютном большинстве конфигураций вебсервера и php).
Таким образом, если триггер будет писать ещё и во временную таблицу — мы сможем узнать кто или что в ответе за конкретные изменения.
Чуть подробнее — мы можем вначале страницы создавать временную таблицу, если триггер срабатывает, он записывает информацию в таблицу предназначенную для слежения за данными, а айдишники, которые возвращает last_insert_id() записывать во временную таблицу. При завершении работы обращаемся к временной таблице, и если она не пуста — записываем в таблицу изменений всё что хотим из того что знает загруженная PHP страница.
Далее — вариант реализации.
1) Начнём с временной таблицы — мы можем её создавать, когда триггер выполняется на странице в первый раз! Для этого нужно всего лишь написать CREATE TEMPORARY TABLE temp_watch_changes IF NOT EXISTS. Есть только небольшая проблема — в текущих версиях MySQL невозможно узнать существует ли временная таблица каким-либо запросом. Поэтому, она обязательно должна быть создана, чтобы не было ошибки, когда через php мы будем выбирать значения.
Мы может потрюкачить, а можем всё сделать прямолинейно.
В качестве небольшого трюка — в MySQL может одновременно существовать таблица обычная и временная с одинаковым именем. Если есть временная — то будет использоваться именно она. И каждый раз из пхп проверяя есть ли записи внутри таблицы temp_watch_changes там будет либо пусто, либо айдишники записанные триггером и никаких ошибок.
Более прямолинейный метод — просто создавать временную таблицу каждый раз при загрузке страницы. На нашем сервере это занимает 0.0008 секунд, что впринципе приемлемо :)
2) Создаём таблицу, содержащую сами изменения
3) Создаём триггер. Выяснить, можно ли динамически использовать названия колонок в триггерах — не удалось. Наверное, просто нельзя, но нам это не сильно и нужно. Ведь есть PHP.
Сама функция создания могла быть по-лучше в плане читаемости. Она создаёт 3 триггера — на update, insert, delete. Принимает название таблицы, колонок за которыми следить и ключ по которому можно найти эту запись (может быть и несколько ключей).
Вызываться может так:
4) Теперь сделаем так, чтобы после того, как PHP страница отработает обработались данные из временной таблицы.
Воспользуемся register_shutdown_function, которая позволяет выполнить любую функцию по завершении скрипта. На любом проекте есть файл, который инклюдится всегда — туда её и разместим.
Вот и всё.
Триггеры MySQL знают:
* момент в который происходит изменение
* старое и новое значение
PHP знает:
* кто в данный момент залогинен
* какая страница открыта, с какой перешли
* бравзер
* IP адрес
* POST, GET
* Cookie
Каким же способом можно умудриться записать всю необходимую информацию?
Воспользуемся особенностями PHP и MySQL!
MySQL умеет создавать временные таблицы, которые живут только пока коннект к бд не закроется, а PHP как раз при открытии каждой страницы — создаёт новый коннект (в абсолютном большинстве конфигураций вебсервера и php).
Таким образом, если триггер будет писать ещё и во временную таблицу — мы сможем узнать кто или что в ответе за конкретные изменения.
Чуть подробнее — мы можем вначале страницы создавать временную таблицу, если триггер срабатывает, он записывает информацию в таблицу предназначенную для слежения за данными, а айдишники, которые возвращает last_insert_id() записывать во временную таблицу. При завершении работы обращаемся к временной таблице, и если она не пуста — записываем в таблицу изменений всё что хотим из того что знает загруженная PHP страница.
Далее — вариант реализации.
1) Начнём с временной таблицы — мы можем её создавать, когда триггер выполняется на странице в первый раз! Для этого нужно всего лишь написать CREATE TEMPORARY TABLE temp_watch_changes IF NOT EXISTS. Есть только небольшая проблема — в текущих версиях MySQL невозможно узнать существует ли временная таблица каким-либо запросом. Поэтому, она обязательно должна быть создана, чтобы не было ошибки, когда через php мы будем выбирать значения.
Мы может потрюкачить, а можем всё сделать прямолинейно.
В качестве небольшого трюка — в MySQL может одновременно существовать таблица обычная и временная с одинаковым именем. Если есть временная — то будет использоваться именно она. И каждый раз из пхп проверяя есть ли записи внутри таблицы temp_watch_changes там будет либо пусто, либо айдишники записанные триггером и никаких ошибок.
Более прямолинейный метод — просто создавать временную таблицу каждый раз при загрузке страницы. На нашем сервере это занимает 0.0008 секунд, что впринципе приемлемо :)
CREATE TEMPORARY TABLE temp_watch_changes ( id_change INTEGER NOT NULL )
2) Создаём таблицу, содержащую сами изменения
CREATE TABLE `watch_changes` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`table_name` varchar(255) DEFAULT NULL,
`column_name` varchar(255) DEFAULT '',
`key_name` varchar(255) DEFAULT NULL,
`key_value` varchar(1000) DEFAULT NULL,
`old_value` text,
`new_value` text,
`type` enum('insert','update','delete') DEFAULT 'update',
`date` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`ip` varchar(255) DEFAULT NULL,
`id_user` int(11) DEFAULT '0',
`user_email` varchar(255) DEFAULT '',
`post` text,
`get` text,
`session` text,
`server` text,
`user_agent` varchar(1000) DEFAULT '',
`url` text,
`referer` text,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
3) Создаём триггер. Выяснить, можно ли динамически использовать названия колонок в триггерах — не удалось. Наверное, просто нельзя, но нам это не сильно и нужно. Ведь есть PHP.
function createWatchTrigger($table,$columns,$primaryKey){
if(!is_array($primaryKey)){
$primaryKey=array($primaryKey);
}
$types=array('update','insert','delete');
foreach($types as $type){
db::$used->internalQuery("drop trigger IF EXISTS {$table}_t_$type");
$triggerContent="CREATE TRIGGER {$table}_t_$type
AFTER $type ON {$table}
FOR EACH ROW
BEGIN
CREATE TEMPORARY TABLE IF NOT EXISTS temp_watch_changes (
id_change INTEGER NOT NULL
);
";
foreach($columns as $columnTitle){
if($type=='update'){
$triggerContent.="
IF NEW.{$columnTitle} != OLD.$columnTitle
THEN ";
}
$triggerContent.="INSERT INTO watch_changes (table_name,column_name,old_value,new_value,type,key_name,key_value) ";
if($type=='insert'){
$triggerContent.="VALUES('{$table}','$columnTitle','', NEW.$columnTitle,'$type','".implode(',',$primaryKey)."',CONCAT('',NEW.".implode(",',',NEW.",$primaryKey)."));";
}else if($type=='update'){
$triggerContent.="VALUES('{$table}','$columnTitle',OLD.$columnTitle, NEW.$columnTitle,'$type','".implode(',',$primaryKey)."',CONCAT('',NEW.".implode(",',',NEW.",$primaryKey)."));";
}else if($type=='delete'){
$triggerContent.="VALUES('{$table}','$columnTitle',OLD.$columnTitle,'','$type','".implode(',',$primaryKey)."',CONCAT('',OLD.".implode(",',',OLD.",$primaryKey)."));";
}
$triggerContent.="
set @last_id=last_insert_id();
INSERT INTO temp_watch_changes (id_change) values (@last_id);";
if($type=='update'){
$triggerContent.="END IF;";
}
}
$triggerContent.="\nEND;";
db::$used->internalQuery($triggerContent);
}
}
Сама функция создания могла быть по-лучше в плане читаемости. Она создаёт 3 триггера — на update, insert, delete. Принимает название таблицы, колонок за которыми следить и ключ по которому можно найти эту запись (может быть и несколько ключей).
Вызываться может так:
createWatchTrigger('employees',array('salary','job_title'),'id');
4) Теперь сделаем так, чтобы после того, как PHP страница отработает обработались данные из временной таблицы.
Воспользуемся register_shutdown_function, которая позволяет выполнить любую функцию по завершении скрипта. На любом проекте есть файл, который инклюдится всегда — туда её и разместим.
function shutdown(){
$affectedRows=db::$used->fetchRows("select * from temp_watch_changes");
if($affectedRows){
if(User::isLogged()){
$userId=User::getCurrent()->getId();
$email=User::getCurrent()->getEmail();
}else{
$userId=0;
$email='';
}
$updateData=array(
'ip'=>$_SERVER['REMOTE_ADDR'],
'id_user'=>$userId,
'user_email'=>$email,
'post'=>serialize($_POST),
'get'=>serialize($_GET),
'session'=>serialize($_SESSION),
'server'=>serialize($_SERVER),
'user_agent'=>$_SERVER['HTTP_USER_AGENT'],
'url'=>serialize($_SERVER['REQUEST_URI']),
'referer'=>$_SERVER['HTTP_REFERER']
);
foreach($affectedRows as $row){
db::$used->update('watch_changes',$updateData,array('id'=>$row['id_change']));
}
}
}
register_shutdown_function('shutdown');
Вот и всё.