#!/usr/local/bin/perl -w
#use strict;

# Number of tables - classificators
my $ntables  = 14;
# Number of joins, must be <= $ntables !
my $njoins   = 13;
# dimensions of tables
my @nitems   = ( 200, 200, 10, 10, 10, 10, 10,10,10, 10,10);


die ("njoins ($njoins) must be >= ntables ($ntables) !")  if ( $njoins > $ntables);
my $tables = ($njoins < $ntables ) ? $njoins : $ntables;
my $SQL = '';
# classificators
for ( $i = 0; $i <= $tables; $i++ ) {
  my $table = 't'. $i;
  $SQL .= qq^drop table $table;\ncreate table $table ( a_id int4 NOT NULL, a varchar(10)^;
    if ( $i == 0 ) { # main table
       for ( $k = 1; $k <= $ntables; $k++ ) {
          $SQL .= ', a_t'.$k.'_id int4';
       }
    }
   $SQL .= qq^);\ncreate index a_id_$table on $table (a_id);\n^;
   if ( $i == 0 ) { # main table - create add. indices
       for ( $k = 1; $k <= $ntables; $k++ ) {
          my $table = 't'.$k;
          $SQL .= 'create index a_'.$table.'_id_t0 on t0 (a_'.$table.'_id);';
          $SQL .= "\n";
       }
    }
  $SQL .= qq^COPY $table FROM STDIN USING DELIMITERS '|';\n^;
  srand($i);
  for ( $j = 1; $j <= $nitems[$i]; $j++ ) {  
    my @cols = ();
#    push @cols, (int(rand($nitems[$i])),'a'.$table.$j);
    push @cols, ($j,'a'.$table);
    if ( $i == 0 ) { # main table - add cols
       for ( $k = 1; $k <= $ntables; $k++ ) {
#          push @cols, int(rand($nitems[$k]));
          push @cols, $j;
       }
    }
    $SQL .= join ('|',@cols)."\n";   
  }
  $SQL .= qq^\\.\n^;
}

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

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

print $SELECT,"\n";