Skip to content

Universal database log of data changes by parse the sql queries for PHP

Notifications You must be signed in to change notification settings

moledet/database-change-log

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

26 Commits
 
 
 
 
 
 
 
 

Repository files navigation

Universal database log of data changes by parse the sql queries for PHP

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.

id action table column newValue oldValue date system userId ip UserAgent columnReference operatorReference valueReference
1 update customers phone 77777 99999 2017-02-02 10:33:32 CRM 5 127.0.0.1 Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/56.0.2924.76 Safari/537.36 id = 289460
2 delete country null null 2017-02-03 11:33:22 API 1 127.1.1.7 Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/56.0.2924.76 Safari/537.36 countryId = 20
3 insert user name Bob null 2017-02-04 15:31:52 API 1 127.1.1.7 Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/56.0.2924.76 Safari/537.36 null null null
5 insert user phone 89898 null 2017-02-04 15:31:52 API 1 127.1.1.7 Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/56.0.2924.76 Safari/537.36 null null null
6 insert user password secret null 2017-02-04 15:31:52 API 1 127.1.1.7 Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/56.0.2924.76 Safari/537.36 null null null

About

Universal database log of data changes by parse the sql queries for PHP

Resources

Stars

Watchers

Forks

Packages

No packages published

Languages