Слежение за изменениями данных в MySQL при помощи PHP / Хабр

А что если к такой замечательной возможности 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 секунд, что впринципе приемлемо :)

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');

Вот и всё.