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

Источник: habrahabr

Aleks_ja

А что если к такой замечательной возможности 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');

Вот и всё.

Ссылки по теме