Brief Introduction to the operation log design of the Management System (with operation logs)

How to make the operation logs of the management system into a common module has always been a headache for me, but I have read an article in the blogArticleNow, it is basically solved.

Link to related articles: System Operation Log Design

Before you start, you must clearly split the two logs, that isCommon Operation LogAndBusiness Operation LogWhat is the difference between the two?

In my understanding, common operation logs are the operation records of a single table, while business operation logs are a collection of common operation logs.

For example, if a user needs to buy the same item, the Order has reached the order step. The order is a business, and a series of services are behind this business, such:

Generate an order → generate a product snapshot → send an insite email → Delete the corresponding baby in the shopping cart

In this way, the order operation contains four parts. We can regard these four parts as four tables, and perform corresponding operations on these four tables respectively to implement the business.

But what I want to talk about today is not the business operation log. Because the services of different projects are different, it cannot be made into a common module. What I want to talk about is the common operation log.

The above explains a large paragraph, the following is about to appear, first wash your face awake.

......

First, where do I need to record operation logs? When performing the insert, update, and delete operations, logs are required. The log execution sequence is as follows:

Insert	Run after insert
Update	You must execute the update operation before and after the update operation. You can obtain the data before and after the operation.
Delete	Run before Delete

After the order is clear, let's take a look at the log operation class I wrote. The first version is just a thank you. Repeat it.CodeThere are a lot of optimizations.

Class log {protected $ primaryid; protected $ tbid; protected $ tbname; protected $ keys; protected $ values; /*** parameter description * int $ tbid: query the ID of the specified table * string $ tbname database table name */Public Function insert ($ tbid, $ tbname) {Global $ dB; // query table comment $ db-> query ('show table status where name = "'. $ tbname. '"'); $ TB = $ db-> fetch (); // Insert the log master table $ returnid = $ db-> insert (0, 2, 'tb _ log', array ('adminid = '. $ _ session ['admin'] ['id'], 'Type = 1', 'tableid = '. $ Tbid, 'tablename = "'. $ tbname. '"', 'comment = "'. $ TB ['comment']. '"', 'dt = now () '); // query field comments $ db-> query ('show full columns from '. $ tbname); $ TB = $ db-> fetchall (); foreach ($ TB as $ V) {$ commentarray [$ V ['field'] = $ V ['comment'];} // query information about all fields, insert logs from the table $ rs = $ db-> select (0, 1, $ tbname, '*', 'and tbid = '. $ tbid); $ keys = array_keys ($ RS); $ values = array_values ($ RS); For ($ I = 0; $ I <count ($ keys ); $ I ++) {$ db-> insert (0, 0, 'tb _ log_content ', array ('logid = '. $ returnid, 'tbkey = "'. $ keys [$ I]. '"', 'tbvalue = "'. $ values [$ I]. '"', 'comment = "'. $ commentarray [$ keys [$ I]. '"') ;}} public function updatestart ($ tbid, $ tbname) {Global $ dB; // query table comment $ db-> query ('show table status where name = "'. $ tbname. '"'); $ TB = $ db-> fetch (); // Insert the log master table $ returnid = $ db-> insert (0, 2, 'tb _ log', array ('adminid = '. $ _ session ['admin'] ['id'], 'Type = 2', 'ta Bleid = '. $ tbid, 'tablename = "'. $ tbname. '"', 'comment = "'. $ TB ['comment']. '"', 'dt = now () '); // query the data information before modification $ rs = $ db-> select (0, 1, $ tbname, '*', 'and tbid = '. $ tbid); $ keys = array_keys ($ RS); $ values = array_values ($ RS); $ this-> primaryid = $ returnid; $ this-> tbid = $ tbid; $ this-> tbname = $ tbname; $ this-> keys = $ keys; $ this-> values = $ values;} public function updateend () {Global $ dB; // query field comment $ db-> query ('show full Columns from '. $ this-> tbname); $ TB = $ db-> fetchall (); foreach ($ TB as $ V) {$ commentarray [$ V ['field'] = $ V ['comment'];} // query the modified data information $ rs = $ db-> select (0, 1, $ this-> tbname, '*', 'and tbid = '. $ this-> tbid); $ currentvalues = array_values ($ RS); // compare the information before and after for ($ I = 0; $ I <count ($ currentvalues ); $ I ++) {if ($ this-> values [$ I]! ==$ Currentvalues [$ I]) {$ db-> insert (0, 0, 'tb _ log_content ', array ('logid = '. $ this-> primaryid, 'tbkey = "'. $ this-> keys [$ I]. '"', 'tbvalue = "'. $ this-> values [$ I]. '"', 'currenttbvalue = "'. $ currentvalues [$ I]. '"', 'comment = "'. $ commentarray [$ this-> keys [$ I]. '"') ;}} public function Delete ($ tbid, $ tbname) {Global $ dB; // query table comment $ db-> query ('show table status where name = "'. $ tbname. '"'); $ TB = $ db-> fetch (); // Insert the log master table $ returnid = $ db-> insert (0, 2, 'tb _ log', array ('adminid = '. $ _ session ['admin'] ['id'], 'Type = 3', 'tableid = '. $ tbid, 'tablename = "'. $ tbname. '"', 'comment = "'. $ TB ['comment']. '"', 'dt = now () '); // query field comments $ db-> query ('show full columns from '. $ tbname); $ TB = $ db-> fetchall (); foreach ($ TB as $ V) {$ commentarray [$ V ['field'] = $ V ['comment'];} // query information about all fields, insert logs from the table $ rs = $ db-> select (0, 1, $ tbname, '*', 'and tbid = '. $ tbid); $ keys = array_keys ($ RS); $ values = array_values ($ RS); For ($ I = 0; $ I <count ($ keys ); $ I ++) {$ db-> insert (0, 0, 'tb _ log_content ', array ('logid = '. $ returnid, 'tbkey = "'. $ keys [$ I]. '"', 'tbvalue = "'. $ values [$ I]. '"', 'comment = "'. $ commentarray [$ keys [$ I]. '"'));}}}

Before use, you need to introduce the database operation class. This is a copy I wrote earlier. For more information, see 《Brand new PDO database operation class (for MySQL only).

After the introduction, you can start to use it.

Select

 
$ Log-> insert (82, 'tb _ member ');
 

Update

 
$ Log-> updatestart (82, 'tb _ member '); // The update operation code in the middle. $ log-> updateend ();
 

Delete

 
$ Log-> Delete (82, 'tb _ member ');
 

We can see that only two parameters are required, namely the table ID (primary key) and table name.

Note that table and field annotations must be complete, because the recorded information contains annotations, so that you can see which field is used for reference.

Let's take a look at the finished product.

Finally, share the table structure. There are two tables in total. One master table is a slave table, and the master table records information such as the operation table and operator, and the field information of the operation table is recorded from the table.

-- ---------------------------- Table structure for 'tb _ log' -- ---------------------------- create table 'tb _ log' ('tbid' bigint (20) not null auto_increment, 'adminid' bigint (20) default null comment 'administrator id', 'type' tinyint (4) default '1' comment' operation type: 1 add 2 modify 3 Delete ', 'tableid' bigint (20) default null, 'tablename' varchar (255) Collate utf8_unicode_ci default null comment 'table name', 'comment' varchar (255) Collate utf8_unicode_ci default null, 'dt 'datetime default null, primary Key ('tbid') engine = InnoDB auto_increment = 27 default charset = utf8 collate = utf8_unicode_ci; -- Optimize table structure for 'tb _ log_content '-- -------------------------- create table 'tb _ log_content' ('tbid' bigint (20) not null auto_increment, 'logid' bigint (20) default null, 'tbkey' longtext collate utf8_unicode_ci, 'tbvalue' longtext collate utf8_unicode_ci, 'currenttbvalue' longtext collate limit, 'comment' varchar (255) Collate limit default null, primary Key ('tbid') engine = InnoDB auto_increment = 109 default charset = utf8 collate = utf8_unicode_ci;