From a19c5368c4d7463803ae068ea092f3584e657b9e Mon Sep 17 00:00:00 2001
From: miker <miker@dcc99617-32d9-48b4-a31d-7c20da2025e4>
Date: Tue, 20 Sep 2005 20:06:25 +0000
Subject: [PATCH 1/1] initial "report class" xml and schema generator script

git-svn-id: svn://svn.open-ils.org/ILS/trunk@1841 dcc99617-32d9-48b4-a31d-7c20da2025e4
---
 Open-ILS/src/reporter/report_base.example.xml | 98 +++++++++++++++++++
 Open-ILS/src/reporter/reporter.schema-gen.pl  | 87 ++++++++++++++++
 2 files changed, 185 insertions(+)
 create mode 100644 Open-ILS/src/reporter/report_base.example.xml
 create mode 100755 Open-ILS/src/reporter/reporter.schema-gen.pl

diff --git a/Open-ILS/src/reporter/report_base.example.xml b/Open-ILS/src/reporter/report_base.example.xml
new file mode 100644
index 0000000000..619d23b265
--- /dev/null
+++ b/Open-ILS/src/reporter/report_base.example.xml
@@ -0,0 +1,98 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<reporter xmlns:xi="http://www.w3.org/2001/XInclude">
+	<classes>
+		<class
+		  name="silly-base"
+		  fact-table="silly.fact">
+			<description>Silly Report base</description>
+			<dims>
+				<dim include="silly_word_dim"/>
+				<dim include="silly_sayer_dim"/>
+			</dims>
+		</class>
+	</classes>
+
+	<tables>
+
+		<table id="silly.fact" partition="true">
+			<tablename>stats.silly_fact</tablename>
+
+			<partition>
+				<field>said_when</field>
+				<!-- Valid options:  month, year, woy (week of year), doy (day of year) -->
+				<chunk>month</chunk>
+				<start>2005-09-01</start>
+				<end>2005-12-31</end>
+			</partition>
+
+			<fields>
+				<field
+				  name="silly_word_dim"
+				  datatype="int"
+				  indexed="true">
+					<label>Silly word dim</label>
+					<description>Silly word entry dimension link</description>
+				</field>
+				<field
+				  name="silly_sayer_dim"
+				  datatype="int"
+				  indexed="true">
+					<label>Silly sayer dim</label>
+					<description>Silly sayer entry dimension link</description>
+				</field>
+				<field
+				  name="said_when"
+				  datatype="timestamptz"
+				  core="true"
+				  indexed="true"
+				  default="now()">
+					<label>Silly word timestamp</label>
+					<description>Silly word entry timestamp</description>
+				</field>
+			</fields>
+			<links>
+				<link
+				  field="silly_word_dim"
+				  table="silly.word_dim"
+				  type="has_a"/>
+				<link
+				  field="silly_sayer_dim"
+				  table="silly.sayer_dim"
+				  type="has_a"/>
+			</links>
+		</table>
+
+		<table id="silly.word_dim">
+			<tablename>stats.words</tablename>
+			<fields>
+				<field
+				  name="id"
+				  primary="1"
+				  create-type="serial"
+				  datatype="int"/>
+				<field
+				  name="word"
+				  datatype="text"
+				  indexed="true"
+				  index-type="BTREE"/>
+			</fields>
+		</table>
+
+		<table id="silly.sayer_dim">
+			<tablename>stats.silly_sayers</tablename>
+			<fields>
+				<field
+				  name="id"
+				  primary="1"
+				  create-type="serial"
+				  datatype="int"/>
+				<field
+				  name="sayer"
+				  datatype="text"
+				  indexed="true"
+				  index-type="BTREE"/>
+			</fields>
+		</table>
+
+	</tables>
+</reporter>
diff --git a/Open-ILS/src/reporter/reporter.schema-gen.pl b/Open-ILS/src/reporter/reporter.schema-gen.pl
new file mode 100755
index 0000000000..bb49773d40
--- /dev/null
+++ b/Open-ILS/src/reporter/reporter.schema-gen.pl
@@ -0,0 +1,87 @@
+#!/usr/bin/perl
+use strict; use warnings;
+use XML::LibXML;
+use Date::Manip;
+
+my %chunkmap =
+	(	doy => '%j',
+		woy => '%U',
+		month => '%m',
+		year => '%Y',
+	);
+
+
+my $parser = XML::LibXML->new;
+my $doc = $parser->parse_file($ARGV[0]);
+$parser->process_xincludes($doc);
+
+print "BEGIN;\n\n";
+for my $table ($doc->findnodes('/reporter/tables/table')) {
+	my $tname = $table->getElementsByTagName('tablename')->string_value;
+	
+	(my $pkey_name = $tname) =~ s/\./_/gso;
+	$pkey_name .= '_pkey';
+	warn "$tname\n";
+
+	my (@primary,@other,@indexed);
+	for my $field ($table->findnodes('fields/field')) {
+		my $fname = $field->getAttribute('name');
+		my $fdatatype = $field->getAttribute('create-type') || $field->getAttribute('datatype');
+		warn "\t$fname\n";
+		
+		if ($field->getAttribute('indexed')) {
+			my $itype = $field->getAttribute('index-type') || 'BTREE';
+			push @indexed, [$fname, $itype];
+		}
+
+		if ($field->getAttribute('primary')) {
+			push @primary, [$fname, $fdatatype];
+		} else {
+			push @other, [$fname, $fdatatype];
+		}
+	}
+
+	warn "\n";
+	print	"DROP TABLE $tname CASCADE;\n";
+	print	"CREATE TABLE $tname (\n\t".
+		join(",\n\t",
+			map { join("\t", @$_) } (@primary, @other)
+		). ",\n\tCONSTRAINT $pkey_name PRIMARY KEY (".join(", ", map { $$_[0] } @primary).
+		")\n);\n";
+
+	for my $i (@indexed) {
+		print	"CREATE INDEX \"${tname}_$$i[0]_idx\" ON $tname USING $$i[1] ($$i[0]);\n";
+	}
+	print "\n";
+
+	if ($table->getAttribute('partition')) {
+		my ($part) = $table->getElementsByTagName('partition')->get_nodelist;
+		my ($field) = $part->getElementsByTagName('field')->get_nodelist;
+		my ($chunk) = $part->getElementsByTagName('chunk')->get_nodelist;
+		my ($start) = $part->getElementsByTagName('start')->get_nodelist;
+		my ($end) = $part->getElementsByTagName('end')->get_nodelist;
+
+		$field = $field->textContent;
+		$chunk = $chunk->textContent;
+		$start = UnixDate(ParseDate($start->textContent),$chunkmap{$chunk});
+		$end = UnixDate(ParseDate($end->textContent),$chunkmap{$chunk});
+
+		for my $tpart ( $start .. $end ) {
+			print	"CREATE TABLE ${tname}_${chunk}_$tpart () INHERITS ($tname);\n";
+			print	"ALTER TABLE ${tname}_${chunk}_$tpart\n".
+				"\tADD CONSTRAINT \"${tname}_${chunk}_${tpart}_test\"\n".
+				"\tCHECK (EXTRACT('$chunk' FROM $field) = $tpart);\n";
+			print	"CREATE RULE \"${tname}_${chunk}_${tpart}_ins_rule\" AS\n\tON INSERT TO ".
+				"$tname \n\tWHERE EXTRACT('$chunk' FROM NEW.$field) = $tpart ".
+				"\n\tDO INSTEAD INSERT INTO ${tname}_${chunk}_$tpart VALUES (NEW.*);\n";
+			for my $i (@indexed) {
+				print	"CREATE INDEX \"${tname}_${chunk}_${tpart}_$$i[0]_idx\" ".
+					"ON ${tname}_${chunk}_$tpart USING $$i[1] ($$i[0]);\n";
+			}
+			print "\n";
+		}
+	}
+	print "\n";
+
+}
+print "COMMIT;\n";
-- 
2.43.2