PHP Classes

File: example.php

Recommend this page to a friend!
  Classes of Christian Vigh   DB String Store   example.php   Download  
File: example.php
Role: Example script
Content type: text/plain
Description: Example script
Class: DB String Store
Store and retrieve application strings in MySQL
Author: By
Last change:
Date: 8 years ago
Size: 6,128 bytes
 

Contents

Class file image Download
<?php

   
/***
        This example script performs the following :
        1) Take the log file data/example.log, which contains well-formatted entries such as :
            2016-01-01 13:20:01 httptracking[11776] Processing buffered http requests...
            2016-01-01 13:20:01 httptracking[11776] 0 http requests processed
            2016-01-01 13:25:02 httptracking[11908] Processing buffered http requests...
            2016-01-01 13:25:02 httptracking[11908] 2 http requests processed
            2016-01-01 13:30:01 httptracking[12043] Processing buffered http requests...
            2016-01-01 13:30:01 httptracking[12043] 0 http requests processed
           The various fields of this log file, which resembles Apache or ssh auth logs, are :
           - A timestamp
           - A process name ("httptracking")
           - A process id, within square brackets
           - A message
           The variable-length parts of this table are :
           - the process name
           - the message part
        2.1) Create a first table, httptracking_1, which will hold the various parts of the log file
        2.2) Create a second table, httptracking_2, where the "process" and "message" fields have
             been replaced with an id in a string store table
        3) Compare the results in size and number of records
     ***/
   
require ( 'DbStringStore.php' ) ;

   
// Customize here the access parameters to your local database
   
define ( MYSQL_HOST , 'localhost' ) ;
   
define ( MYSQL_USER , 'root' ) ;
   
define ( MYSQL_PASSWORD , '' ) ;
   
define ( MYSQL_DATABASE , 'phpclasses' ) ;
   
define ( LOGFILE , 'data/example.log' ) ;

   
// String store entry types - one for the process name, one for the message part
   
define ( STRING_STORE_PROCESS , 0 ) ;
   
define ( STRING_STORE_MESSAGE , 1 ) ;

   
// Connect to your local database
   
$dblink = mysqli_connect ( MYSQL_HOST, MYSQL_USER, MYSQL_PASSWORD ) ;

   
// Uncomment this if you want to create a brand new database for running this test
    /***
    $query = "CREATE DATABASE " . MYSQL_DATABASE . " DEFAULT CHARSET latin1" ;
    mysqli_query ( $dblink, $query ) ;
     ***/

    // Select our test database
   
mysqli_select_db ( $dblink, MYSQL_DATABASE ) ;

   
// Create the version with inline variable-length fields
   
create_standard_version ( $dblink, LOGFILE ) ;

   
// Create the version with a string store
   
create_string_store_version ( $dblink, LOGFILE, 'httptracking_string_store' ) ;


   
/********************************************************************************
     *
     * Helper functions.
     *
     ********************************************************************************/

    // Create the version with variable-length data stored in the same table
   
function create_standard_version ( $dblink, $logfile )
       {
       
// Recreate the httptracking_1 table if it already exists
       
mysqli_query ( $dblink, "DROP TABLE IF EXISTS httptracking_1" ) ;

       
$query = "
                CREATE TABLE httptracking_1
                   (
                        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                        timestamp DATETIME NOT NULL,
                        process VARCHAR(32) NOT NULL DEFAULT '',
                        process_id INT NOT NULL DEFAULT 0,
                        message VARCHAR(1024) NOT NULL DEFAULT '',

                        PRIMARY KEY ( id ),
                        KEY ( timestamp )
                    ) ENGINE = MyISAM ;
               "
;
       
mysqli_query ( $dblink, $query ) ;

       
// Read the logfile, split each record parts and insert a new row in the table
       
$fp = fopen ( $logfile, "r" ) ;

        while ( (
$line = fgets ( $fp ) ) !== false )
           {
            list (
$timestamp, $process, $pid, $message ) = get_log_parts ( $line ) ;
           
$process = mysqli_escape_string ( $dblink, $process ) ;
           
$message = mysqli_escape_string ( $dblink, $message ) ;
           
$query = "
                        INSERT INTO httptracking_1
                        SET
                            timestamp = '
$timestamp',
                            process = '
$process',
                            process_id =
$pid,
                            message = '
$message'
                       "
;
           
mysqli_query ( $dblink, $query ) ;
            }

       
fclose ( $fp ) ;
        }



   
// Create the version with variable-length data stored in the same table
   
function create_string_store_version ( $dblink, $logfile, $store_name )
       {
       
// Recreate the httptracking_2 table if it already exists
       
mysqli_query ( $dblink, "DROP TABLE IF EXISTS httptracking_2" ) ;

       
$query = "
                CREATE TABLE httptracking_2
                   (
                        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                        timestamp DATETIME NOT NULL,
                        process_ssid BIGINT UNSIGNED NOT NULL DEFAULT 0,
                        process_id INT NOT NULL DEFAULT 0,
                        message_ssid BIGINT UNSIGNED NOT NULL DEFAULT 0,

                        PRIMARY KEY ( id ),
                        KEY ( timestamp )
                    ) ENGINE = MyISAM ;
               "
;
       
mysqli_query ( $dblink, $query ) ;

       
// Create the string store (or instanciate it if it already exists)
        // Keep the default size of 1024 characters and don't index the string value part
       
$store = new DbStringStore ( $dblink, $store_name ) ;

       
// Read the logfile, split each record parts and insert a new row in the table
       
$fp = fopen ( $logfile, "r" ) ;

        while ( (
$line = fgets ( $fp ) ) !== false )
           {
            list (
$timestamp, $process, $pid, $message ) = get_log_parts ( $line ) ;
           
$process_id = $store -> Insert ( STRING_STORE_PROCESS, $process ) ;
           
$message_id = $store -> Insert ( STRING_STORE_MESSAGE, $message ) ;
           
$query = "
                        INSERT INTO httptracking_2
                        SET
                            timestamp = '
$timestamp',
                            process_ssid =
$process_id,
                            process_id =
$pid,
                            message_ssid =
$message_id
                       "
;
           
mysqli_query ( $dblink, $query ) ;
            }

       
fclose ( $fp ) ;
        }

   
// Get parts from one log entry, ie : timestamp, process name, process id (within square brackets) and message
   
function get_log_parts ( $line )
       {
       
$line = trim ( $line ) ;
       
$timestamp = substr ( $line, 0, 19 ) ;
       
$remainder = substr ( $line, 20 ) ;
       
$re = '/
                    (?P<process> [^\[]+)
                    \[
                    (?P<pid> [^\]]+)
                    \]
                    \s*
                    (?P<message> .*)
                    /imsx'
;
       
preg_match ( $re, $remainder, $match ) ;

        return ( [
$timestamp, $match [ 'process' ], $match [ 'pid' ], $match [ 'message' ] ] ) ;
        }