Yii 1.1: Log Activity in DataBase

17 followers

In this article i will explain how to customize log activity in database, Imaging you need to save IP user, Url request and user_name in database,But table created by default with Yii only have id,level,category,logtime and message

1: create class file in /protected/extensions/LogDb.php

class LogDb extends CDbLogRoute
{
 
    protected function createLogTable($db,$tableName)
    {
        $db->createCommand()->createTable($tableName, array(
            'id'=>'pk',
            'level'=>'varchar(128)',
            'category'=>'varchar(128)',
            'logtime'=>'timestamp with time zone', 
            'IP_User'=>'varchar(50)', //For IP 
            'user_name'=>'varchar(50)',
            'request_URL'=>'text',
            'message'=>'text',
        ));
    }
    protected function processLogs($logs)
    {
        $command=$this->getDbConnection()->createCommand();
        $logTime=date('Y-m-d H:i:s'); //Get Current Date
 
        foreach($logs as $log)
        {
            $command->insert($this->logTableName,array(
                'level'=>$log[1],
                'category'=>$log[2],
                'logtime'=>$logTime,
                'IP_User'=> Yii::app()->request->userHostAddress, //Get Ip 
                'user_name'=>Yii::app()->user->name , //Get name
                'request_URL'=>Yii::app()->request->url, // Get Url
                'message'=>$log[0]
            ));
        }
    }
 
}

and in config/main.php:

'log'=>array(
            'class'=>'CLogRouter',
            'routes'=>array(
                array(
                    'class'=>'CFileLogRoute',
                    'levels'=>'error',
                    'categories'=>'system.*',
                ),
                array(
                    'class'=>'ext.LogDb',
                    'autoCreateLogTable'=>true,
                    'connectionID'=>'db',
                    'enabled'=>true,
                    'levels'=>'error',//You can replace trace,info,warning,error
                ),
 
                // uncomment the following to show log messages on web pages
 
                /*
                array(
                    'class'=>'CWebLogRoute',
                ),
                */
            ),
        ),

Total 6 comments

#16884 report it
Trejder at 2014/04/08 10:04am
'enabled' property doesn't have to be set

Two things to consider:

  1. LogDb extends CDbLogRoute, where enabled property is set by default to true. So, you don't have to set it in your configuration.

  2. Default implementation of levels property is empty string and it means, that it will log every kind (level) of message.

To disable automatic logging of anything (use particular log route only for manual logging), you have to set this property to your "own" value (for example custom) and also use the same, custom level in Yii:log() function. Details can be found in this forum thread.

#14224 report it
Stepan Selyuk at 2013/07/27 07:01pm
Another solution

I solved the same problem, but more extensible:

class DbLogRoute extends CDbLogRoute {
 
    protected $ext_fields = array ();
 
    /**
     * Creates the DB table for storing log messages.
     * @param CDbConnection $db the database connection
     * @param string $tableName the name of the table to be created
     */
    protected function createLogTable( $db, $tableName ) {
 
        $db->createCommand( )->createTable( $tableName, $this->getTableConfiguration( ) );
 
    }
 
    // --------------------------------
    protected function getTableConfiguration( ) {
 
        $list = array ( 
 
                'id' => 'pk', 
                'level' => 'varchar(128)', 
                'category' => 'varchar(128)', 
                'logtime' => 'datetime', 
                'message' => 'mediumtext' 
        );
 
        if ( $this->ext_fields ) $list = array_merge( $list, $this->ext_fields );
 
        return $list;
 
    }
 
    // --------------------------------------
    /**
     * Stores log messages into database.
     * @param array $logs list of log messages
     */
    protected function processLogs( $logs ) {
 
        $command = $this->getDbConnection( )->createCommand( );
 
        foreach ( $logs as $log ) {
 
            $command->insert( $this->logTableName, $this->getLogParsingResult( $log ) );
 
        }
 
    }
 
    // --------------------------------------
    protected function getLogParsingResult( $log ) {
 
        if ( $this->ext_fields ) {
 
            // explode data
            $_tmp = CJSON::decode( $log[ 0 ], true );
 
            if ( is_array( $_tmp ) ) {
                // return message to original position
                $log[ 0 ] = $_tmp[ 'message' ];
            }
 
        }
 
        $list = array ( 
 
                'level' => $log[ 1 ], 
                'category' => $log[ 2 ], 
                'logtime' => new CDbExpression( 'FROM_UNIXTIME(' . intval( $log[ 3 ] ) . ')' ), 
                'message' => $log[ 0 ] 
        );
 
        if ( $this->ext_fields && is_array( $_tmp ) ) {
 
            foreach ( array_keys( $this->ext_fields ) as $field ) {
 
                if ( isset( $_tmp[ $field ] ) ) $list[ $field ] = $_tmp[ $field ];
            }
        }
 
        return $list;
 
    }
 
}

And child class with configuration only:

class ConsoleLogRoute extends DbLogRoute {
 
    protected $ext_fields = array ( 
 
            'command' => 'varchar(128)', 
            'method' => 'varchar(128)' 
    );
 
}

Like this any other classes (table names and etc have specified in log routes configuration):

class ActionsLogRoute extends DbLogRoute {
 
    protected $ext_fields = array ( 
 
            'action_id' => 'int(10)' 
    );
 
}

Example of log route:

// console log in db
        array ( 
 
                'class' => 'ConsoleLogRoute', 
                'connectionID' => 'db', 
                'autoCreateLogTable' => ! YII_PRODUCTION, 
                'logTableName' => '{{console_logs}}', 
                'levels' => 'error, warning, trace, profile, info',
                'categories' => 'console.*', 
                'enabled' => true 
        ),

How to add logs from your code:

// encode data to JSON (or serialize)
$data = CJSON::encode( array ( 
 
                'message' => $message, // original message
                'command' => $debug[ 1 ][ 'class' ], // extra field
                'method' => $debug[ 1 ][ 'function' ] // extra field
        ) );
 
        // to console log router
        Yii::log( $data, $level, 'console.' . $debug[ 1 ][ 'class' ] );
#14222 report it
mahdi1986 at 2013/07/27 02:46pm
Re:'autoCreateLogTable'=>true, Means

@Nisanth thulasi:

'autoCreateLogTable'=>true, Means

This mean: you donot need to create table in dataBase, if table not exist app automtic create table in your DataBase, you don not need method Progreess(Log), this parametr automatic in your dataBase, if you want explicit use LOG in use this command:

Yii::log('Message',CLogger::LEVEL_TRACE,'Category');
#14188 report it
Nisanth thulasi at 2013/07/25 03:48am
'autoCreateLogTable'=>true, Means

How calling the function

processLogs($logs)

and what means the

'autoCreateLogTable'=>true,
#14125 report it
mahdi1986 at 2013/07/21 04:55pm
Error create table

@Bertho Joris, Thx for check, I use this command for create table in postgerSql

#14104 report it
Bertho Joris at 2013/07/18 11:38pm
Error create table

I Found error when use :

'logtime'=>'timestamp with time zone',

Change to :

'logtime'=>'timestamp',

FIX

Leave a comment

Please to leave your comment.

Write new article