#! /usr/bin/perl

# Copyright 2009 Perforce Software.  All rights reserved.
#
# This file is part of Perforce - the FAST SCM System.

################################################################################
# Script to:
#
# 1.  Create an SQL schema matching a given Perforce server's jobspec.
# 2.  Create MySQL triggers on that database to aid in replication.
# 3.  Create a sample one-to-one mapping between all job fields in the P4
#     instance and the MySQL database.
#
# It outputs to STDOUT, so you should redirect it to a file.  E.g.:
#
#    [] ./import.pl > jobs.sql
#
# Here are some testing hints:
#
# 1. Start the MySQL daemon with the "--binlog-ignore-db database_name" flag so
#    that if you drop/create the database repeatedly in the course of testing,
#    that the binary log files the server creates don't fill up your hard drive.
#
# 2. Create a database to replicate into.  "p4jobs" is the default.
#
#    mysqladmin --user=admin_user create database_name
#
# 3. Populate it with data from this script:
#
#    ./import.pl > jobs.sql 
#    mysql --user=admin_user --database=database_name --batch < jobs.sql
#
#    When replaying the data, there should be no errors printed.  For example,
#    if you see the following, you probably didn't configure this script to
#    use the correct modification date field in your jobspec.
#    
#    ERROR 1054 (42S22) at line 843934: Unknown column 'ModDate' in 'NEW'
#
#    This error means your P4 server hasn't been configured for DTG yet:
#
#    ERROR 1054 (42S22) at line 189: Unknown column 'dtg_dtissue' in 'NEW'
#
#    Once the database is initialized with tables, you can start DTG replication
#    with the "sync from start date" option in order to send all the P4
#    jobs to the MySQL database, and to update the P4 jobs with DTG_DTISSUE
#    fields.  Since this alters the P4 side of things, you could do it on a
#    copy of your repository to get a feel for the behavior.
#
# 4. Run the interactive MySQL prompt and examine your data.
#
#    mysql --user=admin_user --database=database_name
#    mysql> SHOW TABLES;
#    mysql> SELECT * FROM select_values;
#    mysql> SELECT * FROM typemap;
#    mysql> SELECT description FROM jobs ORDER BY reporteddate DESC LIMIT 1;
#
# 5. Delete the database if you need to restart from scratch:
#
#    mysqladmin --user=admin_user   drop database_name
#
# This script requires P4Perl, available here:
#
#    http://perforce.com/perforce/loadsupp.html#api

# blank values default to what's in your environment.
$p4port  = "";
$p4user  = "";
$p4pass  = "";

# jobspec "modified by"/"modified date" fields you selected in the config tool.
#$moddate = "ModDate";
#$modby   = "ModBy";

$moddate = "modifieddate";
$modby   = "modifiedby";


my $fetchJobs = 0;
if( $ARGV[ 0 ] =~ /-jobs/i ) { $fetchJobs = 1; }

################################################################################
# Connect to the server.

my $version = '$Change: 273860 $';
$version =~ /(\d+)/;
$version = $1;

use P4;
my $p4 = new P4;

$p4->SetPort     ( $p4port );
$p4->SetUser     ( $p4user );
$p4->SetPassword ( $p4pass );
$p4->SetProg     ( "P4DTG-MySQL-import.pl" );
$p4->SetVersion  ( $version );
$p4->Connect     (         ) or die( "Failed to connect to Perforce Server.\n");

################################################################################
# Process the jobspec.

$jobspec = $p4->FetchJobSpec();

# Mapping between jobspec type and SQL type.
# When the jobspec says line/word/select, it can be an arbitrarily long field as
# far as the Perforce server is concerned, so we can't just use VARCHAR(N).
#
# If types are added here that should have SQL indexes, don't forget to update
# the table creation logic.
%h = ( "date"   => "DATETIME" ,
       "select" => "TEXT"     ,
       "line"   => "TEXT"     ,
       "text"   => "LONGTEXT" ,
       "word"   => "TEXT"
    );

$seltable = "CREATE TABLE select_values ( field TEXT, val TEXT ) ;\n";
# holds all the SQL inserts for select fields.
$selects;

=comment  This is a sample jobspec:

Fields:
        101 Job word 32 required
        102 Status select 10 required
        103 User word 32 required
        104 Date date 20 always
        105 Description text 0 required
        106 DTG_FIXES text 0 optional
        107 DTG_DTISSUE word 32 optional
        108 DTG_ERROR text 0 optional
        109 DTG_MAPID word 32 optional
        110 ModDate date 20 always
        111 ModBy word 32 always

Values:
        Status open/suspended/closed

Presets:
        Status open
        User $user
        Date $now
        Description $blank

=cut

foreach( @$jobspec{ "Values" } )
{
    @f = @$_;
    for( $i = 0; $i < @f; $i++ )
    {
	# example:  Severity A/B/C
	@_ = split( /\s+/, $f[ $i ] );
	my $name = lc( $_[ 0 ] );
	@_ = split( /\//, "$_[ 1 ]" );
	my $options;

	for( $j = 0; $j < @_; $j++ )
	{
	    # need to lower case the values and escape single quotes.
	    $selects .= "INSERT INTO select_values VALUES ( '$name', ";
	    $selects .= "'$_[ $j ]' ) ;\n";
	}
    }
}
print "START TRANSACTION;\n";
print "\n$seltable\n$selects\n";

# SQL to create the jobs table.
$jobtable = "CREATE TABLE jobs ( \`_ModBy\` TEXT, \`_ModDate\` DATETIME, \`_job\` TEXT, ";

# Add the DTG "List of change numbers" and "Fix details", neither of which are
# in the jobspec, as it is data synthesized by DTG.

$jobtable .= "\`change_numbers\` TEXT, \`fix_details\` TEXT, ";

# Maps the p4 jobspec type to the SQL type.
$maptable = "CREATE TABLE typemap ( field TEXT, p4val TEXT, sqlval TEXT ) ;";

$typemaps;
foreach( @$jobspec{ "Fields" } )
{
    @f = @$_;
    for( $i = 0; $i < @f; $i++ )
    {
	# example:  108 Severity select 10 required
	@_ = split( /\s+/, $f[ $i ] );
	$name   = lc  $_[ 1 ]     ;
	$p4type = lc( $_[ 2 ] );
	$type   =  $h{ $p4type } ;

	$jobtable .= "\`$name\` $type ";
	# Add indexes to the table.  MySQL/InnoDB allow at most 767 bytes.
	if( $type =~ /TEXT|LONGTEXT/ )
	{ $jobtable .= ", INDEX(\`$name\`(767)) "; }

	if( $i != (@$_ - 1) )
	  { $jobtable .= ", "; }

	$typemaps .= "INSERT INTO typemap VALUES ( '$name', '$p4type', '$type' ) ;\n";
    }
}
$jobtable .= ") ;";

$typemaps .= "INSERT INTO typemap VALUES ( \'_ModBy\'  , 'word', 'TEXT'     ) ;\n";
$typemaps .= "INSERT INTO typemap VALUES ( \'_ModDate\', 'date', 'DATETIME' ) ;\n";
$typemaps .= "INSERT INTO typemap VALUES ( '_job', 'word', 'TEXT' ) ;\n";
$typemaps .= "INSERT INTO typemap VALUES ( \'change_numbers\', 'line', 'TEXT' );\n";
$typemaps .= "INSERT INTO typemap VALUES ( \'fix_details\', 'TEXT', 'TEXT' );\n";

print "\n$jobtable\n";
print "\n$maptable\n";
print "\n$typemaps\n";


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

print "\nINSERT INTO jobs ( job, _job ) VALUES ( 'new', 'new' ) ;\n";

# Triggers on UPDATE and INSERT to mirror data to non-replicated fields.
print qq[

DELIMITER //
CREATE TRIGGER jobs_update BEFORE UPDATE ON jobs FOR EACH ROW BEGIN

SET NEW.$moddate    = now();
SET NEW._ModDate    = now(); 
SET NEW._ModBy      = (SELECT SUBSTRING_INDEX( USER(), '\@', 1 ));
SET NEW.dtg_dtissue = new.job;

END//
DELIMITER ;

DELIMITER //
CREATE TRIGGER jobs_insert BEFORE INSERT ON jobs FOR EACH ROW BEGIN

SET new._ModDate    = new.$moddate; 
SET new._job        = new.job;
SET new._ModBy      = (SELECT SUBSTRING_INDEX( USER(), '\@', 1 ));
SET new.dtg_dtissue = new.job;

end//
DELIMITER ;
];

################################################################################
# Done.  Close server connection.

if( $p4->Errors() || $p4->Warnings() )
{
    print "ROLLBACK;\n";

    print "\nP4 Errors:  \n\n"   . $p4->Errors()   . "\n";
    print "\nP4 Warnings:  \n\n" . $p4->Warnings() . "\n";
    print "This is invalid SQL so you notice that something went wrong.\n";

    $p4->Disconnect();
    exit;
}
$p4->Disconnect();

print "COMMIT;\n";

exit;