log2sql.rb #3

#!/usr/bin/ruby

#################################################################
#
# Copyright (c) 2008, Perforce Software, Inc.  All rights reserved.
#
# Redistribution and use in source and binary forms, with or without
# modification, are permitted provided that the following conditions are met:
#
# 1.  Redistributions of source code must retain the above copyright
#     notice, this list of conditions and the following disclaimer.
#
# 2.  Redistributions in binary form must reproduce the above copyright
#     notice, this list of conditions and the following disclaimer in the
#     documentation and/or other materials provided with the distribution.
#
# THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
# AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
# IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
# ARE DISCLAIMED. IN NO EVENT SHALL PERFORCE SOFTWARE, INC. BE LIABLE FOR ANY
# DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
# (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
# LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
# ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
# (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF
# THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
#  
# = Description
#
#   Parse a Perforce server log (-v server=3) and produces a SQL
#   file or a text with tab separator as output.
#   log2sql does not parse vtrack output. See track2sql:
#   http://kb.perforce.com/P4dServerReference/Performance/Track2sql 
#
#   log2sql populates the following tables:
#   +------------+---------------+------+-----+
#   | P R O C E S S                           |
#   +------------+---------------+------+-----+
#   | Field      | Type          | Null | Key |
#   +------------+---------------+------+-----+
#   | processKey | int(11)       | NO   | PRI |
#   | start      | int(11)       | NO   |     |
#   | completed  | int(11)       | YES  |     |
#   | compute    | FLOAT(10,2)   | YES  |     |
#   | lapse      | int(10)       | YES  |     |
#   | running    | int(10)       | YES  |     |
#   | pid        | int(11)       | NO   |     |
#   | user       | varchar(255)  | NO   |     |
#   | workspace  | varchar(255)  | NO   |     |
#   | ip         | varchar(255)  | NO   |     |
#   | client     | varchar(255)  | NO   |     |
#   | cmd        | varchar(255)  | NO   |     |
#   | args       | text          | YES  |     |
#   +------------+---------------+------+-----+
#
# = Usage
#
#   log2sql.rb [-m text] | [-m sql] -d <database_name> log_file
#
# = Requirements
#  
#   Ruby: http://www.ruby-lang.org
#   SQL database if using sql output
#
#################################################################

require "parsedate"
require "getoptlong"

class Command
    def initialize(key, pid, start, running, user, workspace, ip, client, cmd, args)
	@key = key
	@pid = pid
	@start = start
	@running = running
	@compute = 0.0
	@completed = 0
	@lapse = 0
	@user = user
	@workspace = workspace
	@ip = ip
	@client = client
	@cmd = cmd
	@args = args
    end

    attr_accessor :key
    attr_reader :pid
    attr_accessor :start
    attr_accessor :running
    attr_accessor :compute
    attr_accessor :completed
    attr_accessor :lapse
    attr_accessor :user
    attr_accessor :workspace
    attr_accessor :ip
    attr_accessor :client
    attr_accessor :cmd
    attr_accessor :args

    def to_sql
	if completed == 0
	   sprintf("INSERT INTO process VALUES (%d,%d,\"%s\",NULL,%f,%d,%d,\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\");\n",key,pid,start.strftime("%Y-%m-%d %H:%M:%S"),compute,lapse,running,user,workspace,ip,client,cmd,args)
        else
	   sprintf("INSERT INTO process VALUES (%d,%d,\"%s\",\"%s\",%f,%d,%d,\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\");\n",key,pid,start.strftime("%Y-%m-%d %H:%M:%S"),completed.strftime("%Y-%m-%d %H:%M:%S"),compute,lapse,running,user,workspace,ip,client,cmd,args)
	end
    end

    def to_text
        sprintf("%d\t%s\t%f\t%s\t%d\t%d\t%s\t%s\t%s\t%s\t%s\t%s\n",pid,start.strftime("%Y-%m-%d %H:%M:%S"),completed.strftime("%Y-%m-%d %H:%M:%S"),compute,lapse,running,user,workspace,ip,client,cmd,args)
    end
end

def db_create_output(dbname)
    puts(sprintf("CREATE DATABASE IF NOT EXISTS %s;",dbname))
    puts(sprintf("USE %s;",dbname))
    puts("DROP TABLE IF EXISTS process;")
    puts("CREATE TABLE process (")
    puts(" processkey INT(10) UNSIGNED NOT NULL,")
    puts(" pid INT(10) UNSIGNED NOT NULL,")
    puts(" start DATETIME NULL,")
    puts(" completed DATETIME NULL,")
    puts(" compute FLOAT(10,2) UNSIGNED NOT NULL,")
    puts(" lapse INT(10) UNSIGNED NOT NULL,")
    puts(" running INT(10) UNSIGNED NOT NULL,")
    puts(" user VARCHAR(255) NOT NULL,")
    puts(" workspace VARCHAR(255) NOT NULL,")
    puts(" ip VARCHAR(255) NOT NULL,")
    puts(" client VARCHAR(255) NOT NULL,")
    puts(" cmd VARCHAR(255) NOT NULL,")
    puts(" args TEXT NULL,")
    puts(" PRIMARY KEY (processkey)") 
    puts(");")
end

def flush_output(cmds,mode)
    cmds.values.each do
        |p|
	if mode=="sql"
    	   puts( p.to_sql )
        elsif mode=="text"
    	   puts( p.to_text )
        end
    end
end

def croakusage
    puts("Usage: log2sql.rb [-m text] | [-m sql] -d <database_name> log_file" )
    exit(0)
end

######################
# START OF MAIN SCRIPT
######################

opts = GetoptLong.new(  [ "--dbname", "-d",         GetoptLong::OPTIONAL_ARGUMENT   ],
                        [ "--mode", "-m",            GetoptLong::OPTIONAL_ARGUMENT   ],
                        [ "--help", "-h",            GetoptLong::NO_ARGUMENT   ]
                     )
mode = "sql"
dbname = ""
opts.each do
    |opt,arg|
    if opt == "--mode" || opt == "-m"
        mode = arg
    elsif opt == "--dbname" || opt == "-d" 
        dbname = arg
    elsif opt=="--help" || opt == "-h" 
        croakusage
    end
end
if (mode != "text" && mode != "sql") || (mode == "sql" && dbname=="")
  croakusage
end

re_starting = Regexp.new( '^\tPerforce Server starting')
re_cmd = Regexp.new( '^\t(\d+/\d+/\d+ \d+:\d+:\d+) pid (\d+) (.*)@(.*) (.*) \[(.*)\] \'(user-\w+) (.*)\'' )
re_cmd_noargs = Regexp.new( '^\t(\d+/\d+/\d+ \d+:\d+:\d+) pid (\d+) (.*)@(.*) (.*) \[(.*)\] \'(user-\w+)\'' )
re_compute = Regexp.new( '^\t(\d+/\d+/\d+ \d+:\d+:\d+) pid (\d+) compute end (.*)s .*s' )
re_completed = Regexp.new( '^\t(\d+/\d+/\d+ \d+:\d+:\d+) pid (\d+) completed' )
re_track = Regexp.new( '^---')
cmds = Hash.new
key = 0

#
# Initial queries for sql mode
#
db_create_output(dbname) if mode == "sql"

line = $<.gets
while line
    next_line = false
    #
    # Is current line a "starting" line?
    #  
    if (match = re_starting.match(line))
       # need to deal with commands not completed
       flush_output(cmds,mode)
    #
    # Is current line a new command line?
    #
    elsif (match = re_cmd.match(line)) || (match = re_cmd_noargs.match(line))
          line = $<.gets
          next_line = true
          #
          # Is current line part of a vtrack block?
          #
          if !re_track.match( line )
             t = ParseDate.parsedate( match[1] )
             start = Time.local( t[0], t[1], t[2], t[3], t[4], t[5] )
             pid = match[2]
             user = match[3]
             workspace = match[4]
             ip = match[5]
             client = match[6]
             cmd = match[7]
	     args = match[8]
	     if args
             	args = args.gsub("\\"){"\\\\"}
	     end
             cmds[pid] = Command.new(key, pid, start, cmds.length(), user, workspace, ip, client, cmd, args)
             key = key + 1
	  else 
		line
          end
    #
    # Is current line a compute line?
    #
    elsif match = re_compute.match(line)
          pid = match[2]
          if cmds.has_key?( pid )
             compute = match[3].to_f
             # just sum the all the compute values of a same command
             cmds[pid].compute = cmds[pid].compute + compute
          end
    #
    # Is current line a completed line?
    #
    elsif match = re_completed.match(line)
          pid = match[2]
          if cmds.has_key?( pid )
             t = ParseDate.parsedate( match[ 1 ] )
             completed = Time.local( t[0], t[1], t[2], t[3], t[4], t[5] )
             lapse = completed - cmds[pid].start
             cmds[pid].completed = completed
             cmds[pid].lapse = lapse
             if mode == "sql"
                puts( cmds[pid].to_sql )
             elsif mode == "text"
                puts( cmds[ pid ].to_text )
             end
             # command can be removed from the hash
             cmds.delete( pid )
          end
    end
    if !next_line
       line = $<.gets
    end
end
#
# need to deal with commands not completed
#
flush_output( cmds,mode )