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

# Number of tables - classificators
my $ntables  = 10;
# main table, classificators
#my @nitems   = ( 10, 50, 50, 50, 50, 50, 80,100,90 );
my @nitems   = ( 10, 10, 10, 10, 10, 10, 10,10,10, 10,10);
# Number of tables we're joining
my $njoins   = 6; 

my $SQL = '';
# classificators
for ( $i = 0; $i <= $ntables; $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]));
       }
    }
    $SQL .= join ('|',@cols)."\n";   
  }
  $SQL .= qq^\\.\n^;
}

print "$SQL\n";

# Query with joins
my $SELECT = '';
# Try GEQO
#$SELECT .= qq^set GEQO to 'ON=$njoins';^;
#$SELECT .= 'explain select a.a';
$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";