#! /usr/bin/env perl

=comment

Example program to convert 2012.1 P4D structured server log file contents via
'p4 logparse' output into a SQL database.  See p4logparse.pl for an example of
how to do it manually.

Requires P4-Perl, DBI and SQLite.

Note that this program creates a database file that stores the current Perforce
server log file offset in it, so if you re-run it, it'll update the db file with
records written to the log since the last invocation.  It does not follow
rotated logs.

$Author: jason_gibson $

=cut

=comment
Copyright (c) 2012, 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.
=cut

=comment

User contributed content on the Perforce Public Depot is not supported  
by Perforce, although it may be supported by its author. This applies  
to all contributions even those submitted by Perforce employees.

=cut

use strict;
use warnings FATAL => qw( all );
#use Data::Dumper;
use P4;
use DBI;

sub p4_emsg { join '', map { $_->GetText() } $_[ 0 ]->Errors, $_[ 0 ]->Warnings}
sub p4_errs { $_[ 0 ]->WarningCount() + $_[ 0 ]->ErrorCount() }
sub check { die p4_emsg $_[ 0 ] if p4_errs $_[ 0 ] }

my $c = new P4;

$c->SetUser( 'super' );

# Identify ourself in the Perforce server's log file via RCS keywords.

my $rcs_re = qr /^\$\S+: (.*) \$$/;

'$File: //guest/jason_gibson/misc/p4logtodb.pl $' =~ $rcs_re;
$c->SetProg( $1 );

'$Change: 8074 $' =~ $rcs_re;
$c->SetVersion( "\@$1" );

$c->Connect();
check $c;

my $db_name = @ARGV ? $ARGV[ 0 ] : 'log.db';

# The path/name of the log file to tail.  A quick way to play with structured
# logging is to run this on your test server:
#
# p4 -u super configure set serverlog.file.1=all.csv
#
# Note that all.csv is SUPER VERBOSE.  Don't say I didn't try to warn you.

my $logfile = 'all.csv';

my $dbh = DBI->connect( "dbi:SQLite:dbname=$db_name", '', '',
                        { RaiseError => 1, AutoCommit => 1 } )
  || die "Error opening database: $DBI::errstr";

$dbh->do( 'PRAGMA synchronous = OFF' );

my $q = $dbh->get_info( 29 ); # SQL_IDENTIFIER_QUOTE_CHAR

################################################################################

my @log_types;

# Build an array of arrays holding the field names for each record type.
# The position in the array is also the record type number.

$log_types[ $_->{ f_recordType } ][ $_->{ f_field } ] = $_->{ f_name }
  foreach $c->Run( 'logschema', '-a' );

check $c;

# Fields that we want to treat as integers in the SQL schema
#
# p4 logschema -a | grep f_name | cut -d ' ' -f 3 | sort | uniq
my %ints = map { ( $_, 1 ) }
  qw / cmdno date deletes event eventtype gets io_in io_out maxrss net_in
       net_out page_faults pagesCached pagesIn pagesOut pid positions puts
       readLocks recvBytes recvCount recvTime reorderIntl reorderLeaf rev
       rpc_hi_mark_fwd rpc_hi_mark_rev scans sendBytes sendCount sendTime
       severity stime subcode subsys timer timestamp timestamp2 utime
       writeLocks /;

# Names of the different record types.

my @etypes = qw / command_start command_compute command_end error_all
		  error_failed error_fatal audit track_rusage track_rpc
		  track_db user trigger event purge /;

# We create one table containing all record types, and separate tables for
# each individual one.  The huge table is sometimes useful for exploratory
# queries.

my $st_tab = 'logdb';
my $all_tab = 'allr';

my $init = ! defined( $dbh->table_info( undef, undef, $st_tab, undef )
		      ->fetchall_arrayref()->[ 0 ]->[ 2 ] );

my %uniq_cols = map { map { $_ => 1 } @$_ } @log_types;
my @all = sort keys %uniq_cols;

my $all_tab_ddl = "CREATE TABLE $all_tab ( " . join( ', ',
   map { "$q$_$q " . ( exists( $ints{ $_ } ) ? 'INT' : 'TEXT' ) } @all ) . ' )';

print( "$all_tab_ddl\n" ) and $dbh->do( $all_tab_ddl ) if $init;

$dbh->do( "CREATE TABLE $st_tab ( offset INT )" ) and
  $dbh->do( "INSERT INTO $st_tab VALUES( 0 )" ) if $init;

my ( @log_st, @log_all_st );

for( my $i = 0; $i < @log_types; $i++ ) {
  my $et = $log_types[ $i ];
  my $ddl = "CREATE TABLE $etypes[ $i ] ( ";
  $ddl .= exists $ints{ $_ } ? "$q$_$q INT, " : "$q$_$q TEXT, " foreach @$et;
  $ddl =~ s/..$//; # ", " -> ""
  $ddl .= ' );';

  my $idx = "CREATE INDEX et_$etypes[$i]_index on $etypes[ $i ] ( eventtype )";
  my $dml = "INSERT INTO $etypes[ $i ] VALUES( " . "?, " x ( @$et - 1 ) . "? )";
  # TODO: correctly sort arg_N for logappend
  my $dml_all = "INSERT INTO $all_tab ( " . join( ', ', map{ "$q$_$q" } @$et ) .
    ' ) VALUES( ' . "?, " x ( @$et - 1 ) . '? )';

  print( "$ddl\n$idx\n" ) and $dbh->do( $ddl ) and $dbh->do( $idx ) if $init;

  $log_st    [ $i ] = $dbh->prepare( $dml     );
  $log_all_st[ $i ] = $dbh->prepare( $dml_all );
}

################################################################################

my $offset = $dbh->selectrow_array( "SELECT offset FROM logdb" );
my $fetch_count = 10000;

while( 1 ) {
  print "Fetching from offset $offset\n";  
  my $l = $c->Run( 'logparse', '-m', "$fetch_count", '-s', "$offset", $logfile);
  my $t_offset = $offset;

  check $c;
  
  $dbh->do( "BEGIN TRANSACTION" );

  foreach my $d ( @$l ) {
    $t_offset = $d->{ f_offset };
    my $et = $d->{ f_eventtype };

    my @args = map {
      my $r = $d->{ "f_$_" };
      # 2011/10/31 11:38:33 352180623
      #      $r =~ s/.* // if $_ eq 'date';
      $r;
    } grep { $_ ne 'offset' } @{ $log_types[ $et ] };

    # P4Perl groups numbered elements?
    if( $et eq '10' ) {
      push @args, @{ $d->{ f_arg_ } };
      @args = grep { defined } @args;
      # logappend has 36 fields, 24 of which are user-supplied.
      push @args, (undef) x ( 24 - @{ $d->{ f_arg_ } } );
  }

    $log_st    [ $et ]->execute( @args );
    $log_all_st[ $et ]->execute( @args );
  }

  $dbh->do( "UPDATE $st_tab SET offset = $t_offset" );
  $dbh->do( "COMMIT" );

  $offset = $t_offset;

  last if @$l < $fetch_count;
}

################################################################################

print "Ending at offset     $offset\n";

  $c->Disconnect();
$dbh->disconnect();