#!/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 $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;
create table $table ( id int4 NOT NULL, a varchar(4));
COPY $table FROM STDIN USING DELIMITERS '|';
^;
  $SQL .= join ( "\n", map { $_.'|'.'a'.$table } (1..$nitems[$i]) );
  $SQL .= qq^\n\\.\n^;
  $SQL .= qq^create index id_$table on $table (id);\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 '. join( ', ', map { 't'.$_.'.a as t'.$_; } (0..$njoins) );
$SELECT .=  "\n where " . join( ' and ', map { 't'.$_.'.id=t0.id' } (1..$njoins) );
$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;
}