2 use strict; use warnings;
4 use OpenSRF::Utils qw/:datetime/;
6 use DateTime::Duration;
7 use DateTime::Format::ISO8601;
9 my $dt_parser = DateTime::Format::ISO8601->new;
10 my $log = 'OpenSRF::Utils::Logger';
21 my $parser = XML::LibXML->new;
22 my $doc = $parser->parse_file($ARGV[0]);
23 $parser->process_xincludes($doc);
26 for my $table ($doc->findnodes('/reporter/tables/table')) {
27 my $tname = $table->getElementsByTagName('tablename')->string_value;
29 (my $pkey_name = $tname) =~ s/\./_/gso;
30 $pkey_name .= '_pkey';
33 my (@primary,@other,@indexed);
34 for my $field ($table->findnodes('fields/field')) {
35 my $fname = $field->getAttribute('name');
36 my $fdatatype = $field->getAttribute('create-type') || $field->getAttribute('datatype');
39 if ($field->getAttribute('indexed')) {
40 my $itype = $field->getAttribute('index-type') || 'BTREE';
41 push @indexed, [$fname, $itype];
44 if ($field->getAttribute('primary')) {
45 push @primary, [$fname, $fdatatype];
47 push @other, [$fname, $fdatatype];
52 print "DROP TABLE $tname CASCADE;\n";
53 print "CREATE TABLE $tname (\n\t".
54 join(",\n\t", map { join("\t", @$_) } (@primary, @other))."\n".
57 ",\tCONSTRAINT $pkey_name PRIMARY KEY (".
58 join(", ", map { $$_[0] } @primary). ")\n" :
65 if ($table->getAttribute('partition')) {
66 my ($part) = $table->getElementsByTagName('partition')->get_nodelist;
67 my ($field) = $part->getElementsByTagName('field')->get_nodelist;
68 my ($chunk) = $part->getElementsByTagName('chunk')->get_nodelist;
69 my ($start) = $part->getElementsByTagName('start')->get_nodelist;
70 my ($end) = $part->getElementsByTagName('end')->get_nodelist;
72 $field = $field->textContent;
73 $chunk = $chunk->textContent;
74 $start = $dt_parser->parse_datetime( $start->textContent );
75 $end = $dt_parser->parse_datetime( $end->textContent );
78 while ( $start->epoch < $end->epoch ) {
80 my $chunk_end = $start->clone;
81 $chunk_end->add( DateTime::Duration->new( $chunk => 1 ) );
82 $chunk_end->subtract( DateTime::Duration->new( seconds => 1 ) );
84 my $tpart = $start->epoch;
86 my $where = "BETWEEN '".$start->strftime('%FT%T%z').
87 "' AND '".$chunk_end->strftime('%FT%T%z')."'";
89 print "CREATE TABLE ${tname}_${chunk}_$tpart () INHERITS ($tname);\n";
90 print "ALTER TABLE ${tname}_${chunk}_$tpart\n".
91 "\tADD CONSTRAINT \"${tname}_${chunk}_${tpart}_test\"\n".
92 "\tCHECK ($field $where);\n";
93 print "CREATE RULE \"${tname}_${chunk}_${tpart}_ins_rule\" AS\n\tON INSERT TO ".
94 "$tname \n\tWHERE NEW.$field $where".
95 "\n\tDO INSTEAD INSERT INTO ${tname}_${chunk}_$tpart VALUES (NEW.*);\n";
96 for my $i (@indexed) {
97 print "CREATE INDEX \"${tname}_${chunk}_${tpart}_$$i[0]_idx\" ".
98 "ON ${tname}_${chunk}_$tpart USING $$i[1] ($$i[0]);\n";
101 $start->add( DateTime::Duration->new( $chunk => 1 ) );
104 for my $i (@indexed) {
105 print "CREATE INDEX \"${tname}_$$i[0]_idx\" ON $tname USING $$i[1] ($$i[0]);\n";