#!/usr/bin/perl
#
# Test of creating the ATIS database and doing many different selects on it
################### Standard benchmark inits ##############################

use Mysql;
use Getopt::Long;
use Benchmark;

package main;

$opt_skip_create=$opt_skip_in=$opt_skip_delete=$opt_verbose=$opt_fast_insert=
  $opt_lock_tables=$opt_debug=$opt_skip_delete=$opt_fast=$opt_force=0;
$opt_host=""; $opt_db="test";

GetOptions("host=s","db=s","skip-create","skip-in","skip-delete","verbose","fast-insert","lock-tables","debug","fast","force") || die "Aborted";
$opt_skip_in=$opt_force=undef;	# Ignore warnings from these
####
####  Connect and start timeing
####

$start_time=new Benchmark;
$dbh = Mysql->Connect($opt_host, $opt_db) || die $Mysql::db_errstr;

####
#### Create needed tables
####

&init_data;

print "ATIS table test\n";

if (!$opt_skip_create)
{
  $loop_time= new Benchmark;
  for($ti = 0; $ti <= $#table_names; $ti++)
  {
    my $table_name = $table_names[$ti];
    my $array_ref = $tables[$ti];
    my @table = @$array_ref;
    my $fields_part = "";
    my $prefix = "";

    # This may fail if we have no table so do not check answer and tell
    # mysql to keep quiet about errors.
    $Mysql::QUIET = 1;
    $dbh->Query("drop table $table_name");
    $Mysql::QUIET = 0;

    for($i = 0; $i < $#table; $i += 2)
    {
      $fields_part .= $prefix . $table[$i] . " " . $table[$i + 1];
      $prefix = ", ";
    }
    print "Creating table $table_name\n" if ($opt_verbose);
    $command = "create table $table_name ($fields_part)";
    $dbh->Query($command) or die $Mysql::db_errstr;
  }
  $end_time=new Benchmark;
  print "Time to create_table $#tables tables: " . 
    timestr(timediff($end_time, $loop_time),"noc") . "\n";
}


####
#### Insert data
####

$loop_time= new Benchmark;
$row_count=0;

if ($opt_lock_tables)
{
  $dbh->Query("LOCK TABLES " . join(" WRITE,", @table_names) . " WRITE") || die $Mysql::db_errstr;
}

if ($opt_fast)
{
  $pwd=`pwd`; chop($pwd);
  for ($ti = 0; $ti <= $#table_names; $ti++)  
  {
    my $table_name = $table_names[$ti];
    my $command="load data infile '$pwd/data-ATIS/${table_name}.txt' into table $table_name columns optionally enclosed by '\"' terminated by ','";
    print "$command\n" if ($opt_debug);
    $sth=$dbh->Query($command) or die $Mysql::db_errstr;
    $row_count+=$sth->affected_rows;
    if ($opt_debug)
    {
      print "Info for load of $table_name: " . $sth->info() . "\n";
    }
  }
}
else
{
  for ($ti = 0; $ti <= $#table_names; $ti++)
  {
    my $table_name = $table_names[$ti];
    my $array_ref = $tables[$ti];
    my @table = @$array_ref;
    my $insert_start = "insert into $table_name";
    my $prefix = "";
    my $field_name;

    if (!$opt_fast_insert)
    {
      $insert_start .= " (";
      for($i = 0; $i < $#table; $i += 2)
      {
	$field_name = $table[$i];
	if (! ($field_name =~ /^PRIMARY KEY$|^KEY$|^UNIQUE$/))
	{
	  $insert_start .= $prefix . $field_name;
	}
	$prefix = ", ";
      }
      $insert_start .= ")";
    }
    $insert_start .= " values (";

    open(DATA, "data-ATIS/${table_name}.txt") || die "Can't open text file: data-ATIS/${table_name}.txt\n";
    while(<DATA>)
    {
      chop;
      $command = $insert_start . $_ . ")";
      print "$command\n" if ($opt_debug);
      $dbh->Query($command) or die $Mysql::db_errstr;
      $row_count++;
    }
  }
}
close(DATA);

$end_time=new Benchmark;
print "Time to insert $row_count rows: " . 
    timestr(timediff($end_time, $loop_time),"noc") . "\n";

####
#### Delete the tables
####

if (!$opt_skip_delete)
{
  $loop_time= new Benchmark;
  if ($opt_lock_tables)
  {
    $dbh->Query("UNLOCK TABLES") || die $Mysql::db_errstr;
  }
  for($ti = 0; $ti <= $#table_names; $ti++)
  {
    my $table_name = $table_names[$ti];
    $dbh->Query("drop table $table_name");
  }

  $end_time=new Benchmark;
  print "Time to drop_table $#table_names tables: " . 
    timestr(timediff($end_time, $loop_time),"noc") . "\n";
}

####
#### End of benchmark
####

$dbh=0;				# close connection
$end_time=new Benchmark;
	    
print "Total time: " .
  timestr(timediff($end_time, $start_time),"noc") . "\n";

exit 0;


sub init_data
{
  @aircraft = (
	       "aircraft_code", "char(3) NOT NULL",
	       "aircraft_type", "char(64)",
	       "engines", "tinyint(1)",
	       "category", "char(10)",
	       "wide_body", "char(3)",
	       "wing_span", "float(6,2)",
	       "length", "float(6,2)",
	       "weight", "integer(7)",
	       "capacity", "smallint(3)",
	       "pay_load", "integer(7)",
	       "cruising_speed", "mediumint(5)",
	       "range_miles", "mediumint(5)",
	       "pressurized", "char(3)",
	       "PRIMARY KEY", "(aircraft_code)",
	       );
  @airline = (
	      "airline_code", "char(2) NOT NULL",
	      "airline_name", "char(64)",
	      "note", "char(38)",
	      "PRIMARY KEY", "(airline_code)",
	      );
  @airport = (
	      "airport_code", "char(3) NOT NULL",
	      "airport_name", "char(40)",
	      "location", "char(36)",
	      "state_code", "char(2)",
	      "country_name", "char(25)",
	      "time_zone_code", "char(3)",
	      "PRIMARY KEY", "(airport_code)",
	      );
  @airport_service = (
		      "city_code", "char(4) NOT NULL",
		      "airport_code", "char(3) NOT NULL",
		      "miles_distant", "float(4,1)",
		      "direction", "char(3)",
		      "minutes_distant", "smallint(3)",
		      "PRIMARY KEY", "(city_code, airport_code)",
		      );
  @city = (
	   "city_code", "char(4) NOT NULL",
	   "city_name", "char(25)",
	   "state_code", "char(2)",
	   "country_name", "char(25)",
	   "time_zone_code", "char(3)",
	   "PRIMARY KEY", "(city_code)",
	   );
  @class_of_service = (
		       "class_code", "char(2) NOT NULL",
		       "rank", "tinyint(2)",
		       "class_description", "char(80)",
		       "PRIMARY KEY", "(class_code)",
		       );
  @code_description = (
		       "code", "char(5) NOT NULL",
		       "description", "char(110)",
		       "PRIMARY KEY", "(code)",
		       );
  @compound_class = (
		     "fare_class", "char(3) NOT NULL",
		     "base_class", "char(2) NOT NULL",
		     "class_type", "char(10) NOT NULL",
		     "premium", "char(3) NOT NULL",
		     "economy", "char(3) NOT NULL",
		     "discounted", "char(3) NOT NULL",
		     "night", "char(3) NOT NULL",
		     "season_fare", "char(4) NOT NULL",
		     "class_days", "char(7) NOT NULL",
		     "PRIMARY KEY", "(fare_class)",
		     );
  @connect_leg = (
		  "connect_code", "integer(8) NOT NULL",
		  "leg_number", "tinyint(1) NOT NULL",
		  "flight_code", "integer(8)",
		  "PRIMARY KEY", "(connect_code, leg_number, flight_code)",
		  );
  @connection = (
		 "connect_code", "integer(8) NOT NULL",
		 "from_airport", "char(3)",
		 "to_airport", "char(3)",
		 "departure_time", "smallint(4)",
		 "arrival_time", "smallint(4)",
		 "flight_days", "char(7)",
		 "stops", "tinyint(1)",
		 "connections", "tinyint(1)",
		 "time_elapsed", "smallint(4)",
		 "PRIMARY KEY", "(connect_code)",
		 "KEY", "(from_airport)",
		 "KEY", "(to_airport)",
		 );
  @day_name = (
	  "day_code", "tinyint(1) NOT NULL",
	  "day_name", "char(9)",
	  "PRIMARY KEY", "(day_code)",
	  );
  @dual_carrier = (
		   "main_airline", "char(2) NOT NULL",
		   "dual_airline", "char(2)",
		   "low_flight", "smallint(4) NOT NULL",
		   "high_flight", "smallint(4) NOT NULL",
		   "connection_name", "char(64)",
		   "PRIMARY KEY", "(main_airline, dual_airline, low_flight)",
		   "KEY", "(main_airline)",
		   );
  @fare = (
	   "fare_code", "char(8) NOT NULL",
	   "from_airport", "char(3) NOT NULL",
	   "to_airport", "char(3) NOT NULL",
	   "fare_class", "char(3) NOT NULL",
	   "fare_airline", "char(2)",
	   "restrict_code", "char(5)",
	   "one_way_cost", "float(7,2)",
	   "rnd_trip_cost", "float(8,2)",
	   "PRIMARY KEY", "(fare_code)",
	   "KEY", "(fare_code)",
	   "KEY", "(from_airport)",
	   "KEY", "(to_airport)",
	   );
  @flight = (
	     "flight_code", "integer(8) NOT NULL",
	     "flight_days", "char(7)",
	     "from_airport", "char(3)",
	     "to_airport", "char(3)",
	     "departure_time", "smallint(4)",
	     "arrival_time", "smallint(4)",
	     "airline_code", "char(2)",
	     "flight_number", "smallint(4)",
	     "class_string", "char(8)",
	     "aircraft_code", "char(3)",
	     "meal_code", "char(7)",
	     "stops", "tinyint(1)",
	     "dual_carrier", "char(1)",
	     "time_elapsed", "smallint(4)",
	     "PRIMARY KEY", "(flight_code)",
	     "KEY", "(from_airport)",
	     "KEY", "(to_airport)",
	     );
  @flight_class = (
		   "flight_code", "integer(8) NOT NULL",
		   "fare_class", "char(3) NOT NULL",
		   "PRIMARY KEY", "(flight_code, fare_class)",
		   );
  @flight_day = (
		 "day_mask", "char(7) NOT NULL",
		 "day_code", "tinyint(1) NOT NULL",
		 "day_name", "char(9)",
		 "PRIMARY KEY", "(day_mask, day_code)",
		 );
  @flight_fare = (
		  "flight_code", "integer(8) NOT NULL",
		  "fare_code", "char(8) NOT NULL",
		  "PRIMARY KEY", "(flight_code, fare_code)",
		  );
  @food_service = (
		   "meal_code", "char(4) NOT NULL",
		   "meal_number", "tinyint(1) NOT NULL",
		   "meal_class", "char(10)",
		   "meal_description", "char(10)",
		   "PRIMARY KEY", "(meal_code, meal_number, meal_class)",
		   );
  @ground_service = (
		     "city_code", "char(4) NOT NULL",
		     "airport_code", "char(3) NOT NULL",
		     "transport_code", "char(1) NOT NULL",
		     "ground_fare", "float(6,2)",
		     "PRIMARY KEY", "(city_code, airport_code, transport_code)",
		     );
  @time_interval = (
	       "period", "char(20) NOT NULL",
	       "begin_time", "smallint(4) NOT NULL",
	       "end_time", "smallint(4) NOT NULL",
	       "PRIMARY KEY", "(period, begin_time)",
	       );
  @month_name = (
	    "month_number", "tinyint(2) NOT NULL",
	    "month_name", "char(9) NOT NULL",
	    "PRIMARY KEY", "(month_number)",
	    );
  @restrict_carrier = (
		       "restrict_code", "char(5) NOT NULL",
		       "airline_code", "char(2)",
		       "PRIMARY KEY", "(restrict_code, airline_code)",
		       );
  @restrict_class = (
		     "restrict_code", "char(5) NOT NULL",
		     "ex_fare_class", "char(12)",
		     "PRIMARY KEY", "(restrict_code, ex_fare_class)",
		     );
  @restriction = (
		  "restrict_code", "char(5) NOT NULL",
		  "application", "char(80)",
		  "no_discounts", "char(80)",
		  "reserve_ticket", "smallint(3)",
		  "stopovers", "char(1)",
		  "return_min", "smallint(3)",
		  "return_max", "smallint(3)",
		  "PRIMARY KEY", "(restrict_code)",
		  );
  @state = (
	    "state_code", "char(2) NOT NULL",
	    "state_name", "char(25)",
	    "country_name", "char(25)",
	    "PRIMARY KEY", "(state_code)",
	    );
  @stop = (
	   "flight_code", "integer(8) NOT NULL",
	   "stop_number", "tinyint(1) NOT NULL",
	   "stop_flight", "integer(8)",
	   "PRIMARY KEY", "(flight_code, stop_number)",
	   );
  @time_zone = (
		"time_zone_code", "char(3) NOT NULL",
		"time_zone_name", "char(32) NOT NULL",
		"PRIMARY KEY", "(time_zone_code, time_zone_name)",
		);
  @transport = (
		"transport_code", "char(1) NOT NULL",
		"transport_description", "char(32)",
		"PRIMARY KEY", "(transport_code)",
		);

# Avoid not used warnings

  @tables =
    (\@aircraft, \@airline, \@airport, \@airport_service,
     \@city, \@class_of_service, \@code_description,
     \@compound_class, \@connect_leg, \@connection, \@day_name,
     \@dual_carrier, \@fare, \@flight, \@flight_class, \@flight_day,
     \@flight_fare, \@food_service, \@ground_service, \@time_interval,
     \@month_name,
     \@restrict_carrier, \@restrict_class, \@restriction, \@state, \@stop,
     \@time_zone, \@transport);

  @table_names =
    ("aircraft", "airline", "airport", "airport_service",
     "city", "class_of_service", "code_description", 
     "compound_class", "connect_leg", "connection", "day_name",
     "dual_carrier", "fare", "flight", "flight_class", "flight_day",
     "flight_fare", "food_service", "ground_service", "time_interval",
     "month_name",
     "restrict_carrier", "restrict_class", "restriction", "state", "stop",
     "time_zone", "transport");
}
