track2sql.php #3

#!/usr/local/bin/php
<?php


/**
 *
 *  TRACK2SQL IS PROVIDED "AS IS", WITHOUT A WARRANTY OF ANY 
 *  KIND. ALL EXPRESS OR IMPLIED REPRESENTATIONS AND WARRANTIES, 
 *  INCLUDING ANY IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS 
 *  FOR A PARTICULAR PURPOSE, OR NON-INFRINGEMENT, ARE HEREBY 
 *  EXCLUDED.
 *
 *
 *  T R A C K 2 S Q L . P H P 
 * ---------------------------
 *
 * Takes a VTRACK log file (server version 2005.2 or greater) as input and 
 * produces an SQL file as output. Requires PHP and an SQL database, such as 
 * MySQL. For the best results, use a vtrack=1 log.
 *
 *
 *  U S A G E
 * -----------
 *
 *   php track2sql.php [ logFile | - [ sqlFile | - ] ] [ -d dbName ]
 *    
 *   Examples:
 *     $ track2sql.php log track.sql -d track
 *     $ cat log | track2sql.php | mysql
 *     $ tail -F log | track2sql.php | mysql
 *
 *
 *  O U T P U T
 * -------------
 *
 * Produces an SQL file that will create and populate the following
 * tables:
 *
 *  +------------+---------------+------+-----+
 *  | P R O C E S S                           |
 *  +------------+---------------+------+-----+
 *  | Field      | Type          | Null | Key |
 *  +------------+---------------+------+-----+
 *  | processKey | int(11)       | NO   | PRI |
 *  | time       | int(11)       | NO   |     |
 *  | pid        | int(11)       | NO   |     |
 *  | user       | varchar(255)  | NO   |     |
 *  | client     | varchar(255)  | NO   |     |
 *  | ip         | varchar(255)  | NO   |     |
 *  | app        | varchar(255)  | NO   |     |
 *  | cmd        | varchar(255)  | NO   |     |
 *  | args       | text          | YES  |     |
 *  | lapse      | decimal(10,3) | YES  |     |
 *  | uCpu       | int(11)       | YES  |     |
 *  | sCpu       | int(11)       | YES  |     |
 *  | diskIn     | int(11)       | YES  |     |
 *  | diskOut    | int(11)       | YES  |     |
 *  | ipcIn      | int(11)       | YES  |     |
 *  | ipcOut     | int(11)       | YES  |     |
 *  | maxRss     | int(11)       | YES  |     |
 *  | pageFaults | int(11)       | YES  |     |
 *  | rpcMsgsIn  | int(11)       | YES  |     |
 *  | rpcMsgsOut | int(11)       | YES  |     |
 *  | rpcSizeIn  | int(11)       | YES  |     |
 *  | rpcSizeOut | int(11)       | YES  |     |
 *  +------------+---------------+------+-----+
 * 
 *  +-------------+--------------+------+-----+
 *  | T A B L E   U S E                       |
 *  +-------------+--------------+------+-----+
 *  | Field       | Type         | Null | Key |
 *  +-------------+--------------+------+-----+
 *  | processKey  | int(11)      | NO   | PRI |
 *  | tableName   | varchar(255) | NO   | PRI |
 *  | pagesIn     | int(11)      | YES  |     |
 *  | pagesOut    | int(11)      | YES  |     |
 *  | pagesCached | int(11)      | YES  |     |
 *  | readLocks   | int(11)      | YES  |     |
 *  | writeLocks  | int(11)      | YES  |     |
 *  | getRows     | int(11)      | YES  |     |
 *  | posRows     | int(11)      | YES  |     |
 *  | scanRows    | int(11)      | YES  |     |
 *  | putRows     | int(11)      | YES  |     |
 *  | delRows     | int(11)      | YES  |     |
 *  | readWait    | int(11)      | YES  |     |
 *  | readHeld    | int(11)      | YES  |     |
 *  | writeWait   | int(11)      | YES  |     |
 *  | writeHeld   | int(11)      | YES  |     |
 *  +-------------+--------------+------+-----+
 *
 */


// run.
main( $argv, $argc );


/**
 * The main function. 
 * loops over input file - writes to output file.
 *
 * @param   array   argv   the options given to dictate behavior.
 * @param   int     argc   the number of arguments.
 */
function main( $argv, $argc )
{

    // relax error reporting to suppress notices.
    error_reporting( E_ALL & ~E_NOTICE );

    // parse args:
    //  -> if arg looks like '-d', then take next piece for db name.
    //  -> take first (non -d) arg to be inputFile.
    //  -> take second (non -d) arg to be outputFile.
    for ( $i = 1; $i < $argc; $i++ )
    {
        if ( substr( $argv[ $i ], 0, 2 ) == "-d" )
        {
            if ( strlen( $argv[ $i ] ) == 2 )
                $dbName = $argv[ ++$i ];
            else
                $dbName = substr( $argv[ $i ], 2 );
        }
        else if ( !$inputFile )
        {
            $inputFile = $argv[ $i ];
        }
        else if ( !$outputFile )
        {
            $outputFile = $argv[ $i ];
        }
    }

    // input and output file arguments are optional.
    //  -> if no input file given, then use stdin.
    //  -> if no output file given, then use stdin.
    //  -> if input or output file is '-', then use stdin/out.
    if ( !$inputFile || $inputFile == "-" ) $inputFile = "php://stdin";
    if ( !$outputFile || $outputFile == "-" ) $outputFile = "php://stdout";
        
    // open the files and test handles.
    $in  = fopen( $inputFile,  'r' );
    $out = fopen( $outputFile, 'w' );
    if ( !is_resource( $in ) )
        die( "Error: Unable to open input file." );
    if ( !is_resource( $out ) )
        die( "Error: Unable to open output file." );

    // if dbName given, write out the create/use db statements.
    if ( trim( $dbName ) )
    {
        fwrite( $out,
            "CREATE DATABASE IF NOT EXISTS " . $dbName . "; \n" .
            "USE " . $dbName . "; \n" 
        );
    }

    // write out the create table statements.
    fwrite( $out, getCreateStatements() );

    // start reading lines - recognize 'info' blocks, buffer and parse them.
    while ( $line = fgets( $in ) )
    {

        // if not inside info block, look for start of block.
        if ( !$inInfo )
        {
            if ( isInfoStart( $line ) )
                $inInfo = TRUE;
        }
        
        // if inside info block, buffer and look for end of block.
        else if ( $inInfo )
        {
            if ( isInfoEnd( $line ) )
            {
                $sql = parseInfo( $buffer );
                if ( $sql ) fwrite( $out, $sql );
                if ( !isInfoStart( $line ) )
                    $inInfo = FALSE;            
                $buffer = NULL;
            } else {
                $buffer .= $line;
            }    
        }
        
    }
    
    // clean-up.
    fclose( $in );
    fclose( $out );

}


/**
 * Quick test for the beginning of an info block.
 *
 * @param   string  line    the line to test.
 * @return  bool    the result of the test.
 */
function isInfoStart( $line )
{
    if ( ( $line === "Perforce server info:\n" ) or
         ( $line === "Perforce server info:\r\n" ) )
        return TRUE;
}


/**
 * Quick test for the end of an info block.
 *
 * @param   string  line    the line to test.
 * @return  bool    the result of the test.
 */
function isInfoEnd( $line )
{
    if ( !trim( $line ) || isInfoStart( $line ) )
        return TRUE;
}


/**
 * Extract track information from info block.
 *
 * @param   string  info    the info block to parse.
 */
function parseInfo( $info )
{

    // early exit if not track output.
    if ( !isTrackOutput( $info ) ) return;

    // manufacture a key for joining process
    // and table records.
    $key = makeProcessKey();

    // build the process record.
    $processInfo  =  extractProcessInfo( $info );
    $sql          =   
        "INSERT INTO process " .
        "VALUES ( " . $key . "," . implode( ",", $processInfo ) . " );\n";
        
    // build the table records.
    $tablesInfo   =  extractTablesInfo( $info );
    if ( !is_array( $tablesInfo ) ) return $sql;
    foreach ( $tablesInfo as $tableInfo )
    {
        $sql .= 
            "INSERT INTO tableUse " .
            "VALUES ( " . $key . "," . implode( ",", $tableInfo ) . ");\n";
    }
 
    // return the sql.
    return $sql;
    
}


/**
 * Test if the given string looks like vtrack output.
 *
 * @param   string  info    the info block to test.
 * @return  bool    the result of the test.
 */
function isTrackOutput( $info )
{
    return strstr( $info, "\n--- " );
}


/**
 * Make a numeric key for joining processes and table use records.
 *
 * @return  int the key.
 */
$PROCESS_KEY = 0;
function makeProcessKey()
{
    global $PROCESS_KEY;
    return ++$PROCESS_KEY;
}


/**
 * Take a vtrack block, and extract process information.
 * Resulting array contains 21 elements (quoted where appropriate).
 *
 * @param   string  track   the vtrack output to parse.
 * @return  array   the exploded process information.
 */
function extractProcessInfo( $track )
{

    // break into lines.
    $lines = explode( "\n", $track );

    // pull apart the first line.
    $parts = explode( " ", trim( $lines[0] ), 7 );
    list( $user, $client ) = explode( "@", $parts[4], 2 );
    list( $app, $command ) = explode( "] '", $parts[6], 2 );
    list( $cmd, $args )    = explode( " ", $command, 2 ); 
    $process[] = strtotime( $parts[0] . " " . $parts[1] );
    $process[] = intval( $parts[3] );
    $process[] = "'" . addslashes( trim( $user ) ) . "'";
    $process[] = "'" . addslashes( trim( $client ) ) . "'";
    $process[] = "'" . trim( $parts[5] ) . "'";
    $process[] = "'" . addslashes( substr( $app, 1 ) ) . "'";
    if ( $args )
    {
        $process[] = "'" . $cmd . "'";
        $process[] = "'" . addslashes( substr( $args, 0, -1 ) ) . "'";
    } else {
        $process[] = "'" . substr( $cmd, 0, -1 ) . "'";
	$process[] = "NULL";
    }

    // search the next three lines for lapse, usage and rpc.
    //  -> only first could be lapse.
    //  -> first or second could be usage.
    //  -> anyone could be rpc.
    for ( $i = 1; ( $i < 4 ) && ( $i < count( $lines ) ); $i++ )
    {
        if ( $i == 1 && substr( $lines[ $i ], 0, 10 ) == "--- lapse " )
            $lapseTime = trim( $lines[ $i ] );
        else if ( $i < 3 && substr( $lines[ $i ], 0, 10 ) == "--- usage " )
            $usageStats = trim( $lines[ $i ] );
        else if ( substr( $lines[ $i ], 0, 8 ) == "--- rpc " )
            $rpcStats = trim( $lines[ $i ] );
    }
    
    // insert lapse time into process info array - or pad with 'NULL'.
    if ( $lapseTime )
    {
        $process[] = floatval( substr( $lapseTime, 10, -1 ) );
    } else {
        $process[] = "NULL";
    }

    // insert usage stats into process info array - or pad with 'NULL'.
    if ( $usageStats )
    {
        $parts = split( " |\+", $usageStats );
        for ( $i = 2; $i < 10; $i++ )
            $process[] = intval( $parts[ $i ] );
    } else {
        for ( $i = 0; $i < 8; $i++ )
            $process[] = "NULL";
    }    

    // insert rcp stats into process info array - or pad with 'NULL'.
    if ( $rpcStats )
    {
        $parts = split( " |\+|/", $rpcStats );
        for ( $i = 6; $i < 10; $i++ )
            $process[] = intval( $parts[ $i ] );
    } else {
        for ( $i = 0; $i < 4; $i++ )
            $process[] = "NULL";
    }    

    // return process info array.
    return $process;

}


/**
 * Take a vtrack block, and extract tables usage information.
 *
 * @param   string  track   the vtrack output to parse.
 * @return  array   the exploded tables information.
 */
function extractTablesInfo( $track )
{

    // break apart track output on tables.
    $blocks = explode( "--- db.", $track );
    for ( $i = 1; $i < count( $blocks ); $i++ )
    {
        $tables[] = extractTableInfo( $blocks[ $i ] );
    }
    return $tables;

}


/**
 * Take a snippet of vtrack, and extract single table usage information.
 * Returns an array containing 15 elements.
 *
 * @param   string  track   the vtrack output to parse.
 * @return  array   the exploded table information.
 */
function extractTableInfo( $track )
{

    // break into lines.
    $lines = explode( "\n", $track );

    // first line is table name.
    $table[] = "'" . trim( $lines[0] ) . "'";

    // search the next three lines for pages, rows and locks.
    //  -> only first could be pages.
    //  -> first or second could be rows.
    //  -> anyone could be locks.
    for ( $i = 1; ( $i < 4 ) && ( $i < count( $lines ) ); $i++ )
    {
        if ( $i == 1 && substr( $lines[ $i ], 0, 12 ) == "---   pages " )
            $pageStats = trim( $lines[ $i ] );
        else if ( $i < 3 && substr( $lines[ $i ], 0, 12 ) == "---   locks " &&
            substr( $lines[ $i ], 0, 16 ) != "---   locks wait" )
            $rowStats = trim( $lines[ $i ] );
        else if ( substr( $lines[ $i ], 0, 16 ) == "---   locks wait" )
            $lockStats = trim( $lines[ $i ] );
    }

    // insert page stats into table info array - or pad with 'NULL'.
    if ( $pageStats )
    {
        $parts = split( "[ |\+]+", $pageStats );
        for ( $i = 5; $i < 8; $i++ )
            $table[] = intval( $parts[ $i ] );
    } else {
        for ( $i = 0; $i < 3; $i++ )
            $table[] = "NULL";
    }    

    // insert row stats into table info array - or pad with 'NULL'.
    if ( $rowStats )
    {    
        
        // switch for server version.
        //  -> 14 parts in 2005.2 track output.
        //     *read/write locks aren't differentiated.
        //  -> 17 parts in 2006.1+ track output.
        //     *read/write locks are broken out.
        $parts = split( "[ |\/|\+]+", $rowStats );
        if ( count( $parts ) == 14 )
        {
            $table[] = intval( $parts[2] );
            $table[] = intval( $parts[2] );
            for ( $i = 9; $i < 14; $i++ )
                $table[] = intval( $parts[ $i ] );
        } else {
            $table[] = intval( $parts[4] );
            $table[] = intval( $parts[5] );
            for ( $i = 12; $i < 17; $i++ )
                $table[] = intval( $parts[ $i ] );
        }
        
    } else {
        for ( $i = 0; $i < 7; $i++ )
            $table[] = "NULL";
    }    

    // insert lock stats into table info array - or pad with 'NULL'.
    if ( $lockStats )
    {
        $parts = split( "[ |\/|\+]+", $lockStats );
        for ( $i = 6; $i < 10; $i++ )
            $table[] = intval( $parts[ $i ] );
    } else {
        for ( $i = 0; $i < 4; $i++ )
            $table[] = "NULL";
    }    

    // return table info array.
    return $table;

}


/**
 * Create process and tableUse tables.
 *
 * @return  string  the sql to create the tables.
 */
function getCreateStatements()
{

    // create syntax for process table.
    $sql  =
        "DROP TABLE IF EXISTS process; \n" .
        "CREATE TABLE process ( \n" .
        " processKey int NOT NULL, \n" .
        "       time int NOT NULL, \n" .
        "        pid int NOT NULL, \n" . 
        "       user varchar(255) NOT NULL, \n" . 
        "     client varchar(255) NOT NULL, \n" . 
        "         ip varchar(255) NOT NULL, \n" . 
        "        app varchar(255) NOT NULL, \n" . 
        "        cmd varchar(255) NOT NULL, \n" . 
        "       args text NULL, \n" .
        "      lapse decimal(10,3) NULL, \n" .
        "       uCpu int NULL, \n" .
        "       sCpu int NULL, \n" .
        "     diskIn int NULL, \n" .
        "    diskOut int NULL, \n" .
        "      ipcIn int NULL, \n" .
        "     ipcOut int NULL, \n" .
        "     maxRss int NULL, \n" .
        " pageFaults int NULL, \n" .
        "  rpcMsgsIn int NULL, \n" .
        " rpcMsgsOut int NULL, \n" .
        "  rpcSizeIn int NULL, \n" .
        " rpcSizeOut int NULL, \n" .
        " PRIMARY KEY ( processKey ) \n" .
        "); \n";
        
    // create syntax for tableUse table.
    $sql .=
        "DROP TABLE IF EXISTS tableUse; \n" .
        "CREATE TABLE tableUse ( \n" .
        "  processKey int NOT NULL, \n" .
        "   tableName varchar(255) NOT NULL, \n" . 
        "     pagesIn int NULL, \n" .
        "    pagesOut int NULL, \n" .
        " pagesCached int NULL, \n" .
        "   readLocks int NULL, \n" .
        "  writeLocks int NULL, \n" .
        "     getRows int NULL, \n" .
        "     posRows int NULL, \n" .
        "    scanRows int NULL, \n" .
        "     putRows int NULL, \n" .
        "     delRows int NULL, \n" .
        "    readWait int NULL, \n" .
        "    readHeld int NULL, \n" .
        "   writeWait int NULL, \n" .
        "   writeHeld int NULL, \n" .
        " PRIMARY KEY ( processKey, tableName ) \n" .
        "); \n";        
    
    return $sql;
    
}


?>