README

The DatabaseChangeLog class help to log all update, delete, insert SQL queries into table data_change_log. The class parse raw SQL or PDO query with statement only by add one string to code. In log will be save change of data and the userID, ip, userAgent and system(configurable).
The tables and columns that need log may be configurable by each action (update, delete, insert).

Installation

###Download From Git
###Clone

git clone https://github.com/moledet/database-change-log.git Log

###Composer

php composer.phar require moledet/database-change-log

or add to yours composer.json see the documentation.

 {
     "repositories": [
         {
             "url": "https://github.com/moledet/database-change-log",
             "type": "vcs"
         }
     ],
     "require": {
         "moledet/database-change-log": "dev-master"
     }
 }

###Dependency This class depends on PHP-SQL-Parser.

Usage

###Config You must config a database connection.

 $config = array(
    'database'=>'mysql',
    'host'=>'localhost',
    'port'=>3306,
    'dbname'=>'test',
    'charset'=>'utf8',
    'user'=>'admin',
    'password'=>'secret'
 );

       
 DatabaseChangeLog::getInstance()->setConnection($config);

May config current user id (default 0), system name(default CRM) or list of tables|columns|actions that need log. If not config the tables list - all tables changes will be logged.

DatabaseChangeLog::getInstance()->setUserId(7);
DatabaseChangeLog::getInstance()->setSystemName('API');

$config = [
                    'user'=>[
                        'insert'=>['login','name','password']
                        'delete'=>'all',
                        'update'=>['login','name']
                     ],
                     'customers'=>'all',
           ];
           
DatabaseChangeLog::getInstance()->setLogTablesConfig($config);                 

###How to use Need put call of log sql before run. You may override framework or ORM connection to run it before query.

 $sql = "UPDATE user SET password='secret' WHERE id=7;";
 DatabaseChangeLog::getInstance()->log($sql);
 
 $framework->getConnection()->runSQL($sql);

Or PDO:

 $query = 'UPDATE users SET bonus = bonus + ? WHERE id = ?';
 $stmt = $pdo->prepare($query);
 foreach ($data as $id => $bonus)
 {
    DatabaseChangeLog::getInstance()->log($query,[$bonus,$id]);
    $stmt->execute([$bonus,$id]);
 }

###Result In table data_change_log will be save the log of changes.