nlt_codilog/database-change-log_fork

A PHP class to log all changes of table data from raw SQL or with PDO statement

V1.0.0 2021-10-19 18:45 UTC

Requires

• php: >=5.3
• greenlion/php-sql-parser: 4.3.0

MIT

• lepetit

• log
• database
• php
• sql
• table
• column

README

Note: This repository is a fork of https://github.com/moledet/database-change-log

Installation

###Download From Git
###Clone

git clone https://github.com/nltCodilog/database-change-log_fork.git

###Composer

php composer.phar require nlt_codilog/database-change-log_fork

or add to yours composer.json see the documentation.

 {
     "repositories": [
         {
             "url": "https://github.com/nltCodilog/database-change-log_fork.git",
             "type": "git"
         }
     ],
     "require": {
         "nltCodilog/database-change-log": "main"
     }
 }

###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