#!/usr/local/bin/perl -w
# Crash Postgres with many joins 
# Oleg Bartunov, 1999

use strict;

$| = 1;
my $njoins = undef;
my $nrows = undef;
my @nitems = ();
my $option = undef;
my $vacuum = 0; 
my $explain = 0; 
my $geqo = 0; 
my $spec = 0; # 1 to create/use additional columns in join
my $bad_option = '';

while ( @ARGV ) {
 $option = shift @ARGV;
 if ( defined ($option) ) {
    if ( $option =~ /^--with-vacuum$/ ) {
        $vacuum = 1;
    } elsif ($option =~ /^--explain$/ ) {
        $explain = 1;
    } elsif ( $option =~ /^--with-geqo$/) {
        $geqo = 1;
    } elsif ( $option =~ /^--help$/) {
        &usage;
    } elsif ( $option =~ /^--joins$/) {
        $njoins = shift @ARGV;
        $njoins ||= '';
        $bad_option .= " \t$option must be followed by number: $njoins \n" unless $njoins =~ /^\d+$/
    } elsif ( $option =~ /^--rows$/) {
        $nrows = shift @ARGV;
        $nrows ||= '';
        $bad_option .=" \t$option must be followed by number: $nrows \n" unless $nrows =~ /^\d+$/;
    } else {
        $bad_option .=  "\Unrecognized option: $option !\n";
    }
  }
}

&usage ($bad_option) if ( $bad_option );
&usage ($bad_option) unless ( defined ($option) );

die (" Not enough parameters: ") unless ( defined($njoins) && defined($nrows) );
my $tables = $njoins;
my $ntables = $tables+1;
eval  '@nitems = (' . '$nrows,'x$ntables . ')';


my $SQL = '';
for ( my $i = 0; $i <= $tables; $i++ ) {
  my $table = 't'. $i;
  $SQL .= qq^drop table $table;\ncreate table $table ( id int4 NOT NULL, a varchar(10)^;
    if ( $i == 0 && $spec ) { # main table
       for ( my $k = 1; $k <= $tables; $k++ ) {
          $SQL .= ', t'.$k.'_id int4';
       }
    }
   $SQL .= qq^);\n^;
  $SQL .= qq^COPY $table FROM STDIN USING DELIMITERS '|';\n^;
  for ( my $j = 1; $j <= $nitems[$i]; $j++ ) {  
    my @cols = ();
    push @cols, ($j,'a'.$table);
    if ( $i == 0 && $spec ) { # main table - add cols
       for ( my $k = 1; $k <= $tables; $k++ ) {
         push @cols, $j;
       }
    }
    $SQL .= join ('|',@cols)."\n";   
  }
  $SQL .= qq^\\.\n^;

# create indices
   $SQL .= qq^create index id_$table on $table (id);\n^;
   if ( $i == 0 && $spec) { # main table - create add. indices
       for ( my $k = 1; $k <= $tables; $k++ ) {
          my $table = 't'.$k;
          $SQL .= 'create index '.$table.'_id_t0 on t0 ('.$table.'_id);';
          $SQL .= "\n";
       }
    }
}

# vacuum analyze
$SQL .= qq^vacuum analyze;\n^ if ( $vacuum );
print "$SQL\n";

# Query with joins
my $SELECT = '';
# Try GEQO
$SELECT .= qq^set GEQO to 'ON=$njoins';^ if ( $geqo );
$SELECT .= 'explain ' if ( $explain );
$SELECT .= 'select t0.a';
for ( my $i = 1; $i <= $njoins; $i++ ) {
  $SELECT .= ',t'.$i.'.a as t'.$i;
}
$SELECT .= "\n".' from t0  ';
for ( my $i = 1; $i <= $njoins; $i++ ) {
  $SELECT .= ',t'.$i;
}
$SELECT .= ( $spec ) ? "\n".' where t1.id = t0.t1_id ' : "\n".' where t1.id = t0.id ';
for ( my $i = 2; $i <= $njoins; $i++ ) {
  $SELECT .= ( $spec ) ? 'and t'.$i.'.id=t0.t'.$i.'_id ' : 'and t'.$i.'.id=t0.id ';
}
$SELECT .= ';';

print $SELECT,"\n";

sub usage {
  my $msg = shift;
  print " ERROR:\n$msg\n" if $msg;
print qq^
 Usage:

 $0  --joins Njoins --rows Nrows [ options ]

 Parameters:
   Njoins    - the number of joins (Njoins < Ntables)
   Nrows     - the number of rows

  Options:
   --help           - print usage
   --explain        - do not send query but explain
   --with-vacuum    - vacuum analyze before query
   --with-geqo      - use GEQO for query

  Example:
    $0  --joins 14 --rows 200 --explain | psql test
^;
print "\n";
exit;
}