#!perl -w
use strict;
no utf8;
use Encode;
use Unicode::String;
use XML::Simple;
<readmore>
my %primKeys;
my $schema = XMLin("./TEST_Baseline_0.99.xml",
                  forcearray => [ qw(zsql:database zsql:table zsql:column zsql:index zsql:field) ],
                  keyattr    => { "zsql:database" => 'name',
                                  "zsql:table" => 'name',
                                  "zsql:column" => 'name',
                                  "zsql:index" => 'name',
                                  "zsql:field" => 'column'}
                  );

foreach my $db_table (sort keys %{$schema->{"zsql:table"}} ) {
  if ($db_table ne '') {
    #print "\t\t$table\n";
    my $create_statement = "create table $db_table (";
    my $primarykey= ", PRIMARY KEY (";
    my $counter = 1;
    my $countto = keys %{$schema->{"zsql:table"}->{$db_table}->{'zsql:column'}};
    #print "Counter = $counter, count-to = $countto\n";
    foreach my $tableColumn (sort keys %{$schema->{"zsql:table"}->{$db_table}->{'zsql:column'}} ) {
      my $col_name = remCol($tableColumn);
      my $col_type = $schema->{'zsql:table'}->{$db_table}->{'zsql:column'}->{$tableColumn}->{type};
      $create_statement .= "$col_name $col_type";
      if (defined $schema->{"zsql:table"}->{$db_table}->{'zsql:column'}->{$tableColumn}->{notnull} ) {
        $create_statement .= " NOT NULL";
      } else {
        $create_statement .= " NULL";
      }
      $counter++;
      if ( $counter <= $countto ) {
        $create_statement .= ", ";
      }
      if (defined $schema->{"zsql:table"}->{$db_table}->{'zsql:column'}->{$tableColumn}->{primarykey} ) {
         push (@{$primKeys{$db_table}{key}}, $col_name);
         if (length($primarykey) > 15) {
           $primarykey .= ", $col_name";
         } else {
           $primarykey .= "$col_name";
         }
      }
    }
    if (length($primarykey) > 15) {
      $primarykey .= " )";
      $create_statement .= $primarykey;
    }
    $create_statement .= ")";
    print "Creating table $db_table with statement\n\t$create_statement\n\n";
    foreach my $db_index (sort keys %{$schema->{'zsql:table'}{$db_table}->{'zsql:index'}} ){
      if (exists $schema->{"zsql:table"}->{$db_table}->{'zsql:index'}->{$db_index}->{'zsql:field'}) {
        my @thisIndex;
        my $sqlStatemnt = "CREATE INDEX $db_index on $db_table (";
        my $fld_cnt = scalar(keys %{$schema->{"zsql:table"}->{$db_table}->{'zsql:index'}->{$db_index}->{'zsql:field'}});
        my $fld_add = 1;
        foreach my $index_col (keys %{$schema->{"zsql:table"}->{$db_table}->{'zsql:index'}->{$db_index}->{'zsql:field'}} ) {
          my $col_name = remCol($index_col);
          push (@thisIndex, $col_name);
          $sqlStatemnt .= "$col_name";
          if ($fld_add < $fld_cnt) { $sqlStatemnt .= ", "; }
          else { $sqlStatemnt .= ") "; }
          $fld_add++;
        }
        if (@thisIndex ne @{$primKeys{$db_table}{key}}) {
          print "Adding index $db_index to table $db_table with \n";
          print "\t$sqlStatemnt\n\n";
          }
        }
    }
  }
}

sub remCol {
  my ($retval) = @_;
  $retval =~ s/Col\d+\-\-//;
  return $retval;
}


##</code><code>##

<?xml version="1.0" encoding="ISO-8859-1"?>
<?xml-stylesheet type="text/xsl" href="../zsql_common.xsl"?>
<!DOCTYPE zsql:database SYSTEM "zsql.dtd">
<zsql:database
  xmlns:zsql="zenark.com/zsql"
  name="TEST_Baseline_0.99"
  desc="A simple test zsql schema"
  >

  <zsql:table name="TEST">
   <zsql:documentation>
     read/write status: read
   </zsql:documentation>
   <zsql:column name="Col0001--TEST_ID" type="INT" primarykey="Y" notnull="Y"/>
   <zsql:column name="Col0002--Name" type="VARCHAR (255)"/>
   <zsql:column name="Col0003--ItemID" type="VARCHAR (60)"/>
   <zsql:column name="Col0004--REF_ID" type="VARCHAR (32)"/>
   <zsql:column name="Col0005--Entry_Point" type="CHAR (1)"/>
   <zsql:column name="Col0006--Entry_Point_QA" type="CHAR (1)"/>
   <zsql:column name="Col0007--Action" type="CHAR (1)"/>
   <zsql:column name="Col0008--Agent" type="CHAR (1)"/>
   <zsql:index name="TEST_KEY" unique="Y">
    <zsql:field column="Col0001--TEST_ID"/>
   </zsql:index>
   <zsql:index name="TEST_2ND_KEY" unique="N">
    <zsql:field column="Col0004--REF_ID"/>
    <zsql:field column="Col0001--TEST_ID"/>
    <zsql:field column="Col0003--ItemID"/>
    <zsql:field column="Col0005--Entry_Point"/>
   </zsql:index>
   <zsql:index name="TEST_4TH_KEY" unique="N">
    <zsql:field column="Col0003--ItemID"/>
    <zsql:field column="Col0004--REF_ID"/>
    <zsql:field column="Col0001--TEST_ID"/>
    <zsql:field column="Col0005--Entry_Point"/>
   </zsql:index>
   <zsql:index name="TEST_3RD_KEY" unique="N">
    <zsql:field column="Col0001--TEST_ID"/>
    <zsql:field column="Col0004--REF_ID"/>
    <zsql:field column="Col0003--ItemID"/>
    <zsql:field column="Col0005--Entry_Point"/>
   </zsql:index>
  </zsql:table>

</zsql:database>

##</code><code>##

Creating table TEST with statement
        create table TEST (TEST_ID INT NOT NULL, Name VARCHAR (255) NULL, ItemID VARCHAR (60) NULL, REF_ID VARCHAR (32)
NULL, Entry_Point CHAR (1) NULL, Entry_Point_QA CHAR (1) NULL, Action CHAR (1) NULL, Agent CHAR (1) NULL, PRIMARY KEY (T
EST_ID ))

Adding index TEST_2ND_KEY to table TEST with
        CREATE INDEX TEST_2ND_KEY on TEST (Entry_Point, ItemID, TEST_ID, REF_ID)

Adding index TEST_3RD_KEY to table TEST with
        CREATE INDEX TEST_3RD_KEY on TEST (Entry_Point, ItemID, TEST_ID, REF_ID)

Adding index TEST_4TH_KEY to table TEST with
        CREATE INDEX TEST_4TH_KEY on TEST (Entry_Point, ItemID, TEST_ID, REF_ID)