---
--- Definition of the describing database


---
--- Entries into VALUELIST that have the same name
--- will be used to create popup menues
--- we'll use the string in field DISPLAY for display
--- as string and CONTENT when we're building
--- HTML pages
DROP TABLE valuelist;
CREATE TABLE valuelist (
	name	text,
	display	text,
	content	text
);



---
--- This table contains default relations between
--- tables for on-screen display.
--- Whenever a record out of table 'parent'
--- is displayed there will be created additional
--- frames that show all related eintries out of
--- table 'child' if 'childField' == 'parentField'.
--- additionally you can provide an order by statement
--- in the field 'childOrder'.
DROP TABLE relation;
CREATE TABLE relation (
	parent				text,
	parentField			text,
	child				text,
	childField			text,
	childOrder			text,
	removeChildOnParentDelete	bool
);



---
--- in the following table we store closer information
--- about specific fields of a table to build a better
--- view of their contents in HTML for editiong/display
DROP TABLE designinfo;
CREATE TABLE designinfo (
	tablename		text,	--- name of table
	fieldname		text,	--- name of the field that gets its specific design information here
	displayinfo		text,	--- (optional) display mode as follows:
					--- textarea height width
					---          displays the contents of this field in form of
					---          a HTML textarea in the specified size 
					---   popup
					---          displays the contents of this field in form of
					---          a popup menu whose contents are taken from the
					---          display field of the associated records in the
					---          valuelist table
					---   tablepopup
					---          displays the contents of this field in form of
					---          a popup menu that shows all valid tables of the
					---          current database
					---   text width maxlength
					---          displays the contents of this field in form of
					---          a text field of the specified size
					---   intasbool
					---          displays the contents of an "INTEGER" field
					---          as boolean, where the value 0 is interpreted
					---          as false while 1 means true.
					---   hidden
					---          fields marked hidden will not be shown
	displaystring		text,	--- (optional) display string for use instead of the fieldname
					--- when creating HTML dialogs
	displayorder		int,	--- (optional) order in which fields are shown in HTML dialogs
	displaycolumn		int,	--- (optional) column in which fields are shown in HTML dialogs
	xdefault		text,	--- (optional) Perl subroutine which is used to specify
					--- the content of the field in case of an empty dialog
					--- not representing any real record
	xevaluation		text	--- (optional) Perl subroutine which is used to verify the
					--- users input and return the value to be saved or undef.
					--- The original value is in @_;
					--- If undef is returned the users modifications will not be
					--- saved and he gets a chance to go back to his input dialog
					--- after the contents of $@ have been shown as error message
);

delete from designinfo where tablename like 'tabledesign';
delete from designinfo where tablename like 'designinfo';
delete from designinfo where tablename like 'equation';
delete from designinfo where tablename like 'virtual';

INSERT INTO designinfo VALUES ('tabledesign', 'xtablestart', 'textarea 10 45', NULL, NULL, NULL, NULL, NULL);
INSERT INTO designinfo VALUES ('tabledesign', 'xtableitem', 'textarea 10 45', NULL, NULL, NULL, NULL, NULL);
INSERT INTO designinfo VALUES ('tabledesign', 'xtableend', 'textarea 10 45', NULL, NULL, NULL, NULL, NULL);
INSERT INTO designinfo VALUES ('tabledesign', 'xxquerystring', 'textarea 10 45', NULL, NULL, NULL, NULL, NULL);
INSERT INTO designinfo VALUES ('designinfo', 'displayinfo', 'popup', NULL, NULL, NULL, NULL, NULL);
INSERT INTO designinfo VALUES ('designinfo', 'xdefault', 'textarea 10 45', NULL, NULL, NULL, NULL, NULL);
INSERT INTO designinfo VALUES ('designinfo', 'xevaluation', 'textarea 10 45', NULL, NULL, NULL, NULL, NULL);
INSERT INTO designinfo VALUES ('equation', 'content', 'textarea 50 100', NULL, NULL, NULL, NULL, NULL);
INSERT INTO designinfo VALUES ('virtual', 'fieldtype', 'popup', NULL, NULL, NULL, NULL, NULL);
INSERT INTO designinfo VALUES ('virtual', 'xequation', 'textarea 25 100', NULL, NULL, NULL, NULL, NULL);
INSERT INTO designinfo VALUES ('virtual', 'xlevel', 'popup', NULL, NULL, NULL, NULL, NULL);


---
--- in the following table we store closer information
--- about specific tables to create some smarter list
--- representation of their contents
DROP TABLE TABLEDESIGN;
CREATE TABLE tabledesign (
	tablename	text,	--- name of table
	needsVirtuals	bool,	--- flag that specifies whether or not the virtual fields
				--- have to be calculated prior to the execution of our
				--- Perl routine.
	modifyableList	bool,	--- the table display in list mode allows for editable fields
				--- and a multiple update feature for all of these is enabled
	xtablestart	text,	--- (optional) Perl subroutine that returns the HTML string
				--- that has to be used prior to listing the records
	xtableitem	text,	--- Perl subroutine which has to return the HTML string that
				--- shall be used to represent the current record (held in
				--- the %values array)
	xtableend	text,	--- (optional) Perl subroutine that returns the HTML string
				--- that had to be used after listing the records
	xxquerystring	text	--- (optional) Perl subroutine that returns the default
				--- where clause to use when this table shall be shown
				--- an eventually existing where clause is provided in $_
);

delete from tabledesign where tablename like 'valuelist';
delete from tabledesign where tablename like 'designinfo';
delete from tabledesign where tablename like 'equation';
delete from tabledesign where tablename like 'virtual';
delete from tabledesign where tablename like 'relation';
delete from tabledesign where tablename like 'tabledesign';

INSERT INTO tabledesign VALUES ('valuelist', 'f', 'f', NULL, NULL, NULL, '$_.\"ORDER BY name,display\"');
INSERT INTO tabledesign VALUES ('designinfo', 'f', 'f', NULL, NULL, NULL, '$_.\"ORDER BY tablename,fieldname\"');
INSERT INTO tabledesign VALUES ('equation', 'f', 'f', NULL, NULL, NULL, '$_.\"ORDER BY eqname\"');
INSERT INTO tabledesign VALUES ('virtual', 'f', 'f', NULL, NULL, NULL, '$_.\"ORDER BY tablename,xlevel,fieldname\"');
INSERT INTO tabledesign VALUES ('relation', 'f', 'f', NULL, NULL, NULL, '$_.\"ORDER BY parent,child\"');

INSERT INTO tabledesign VALUES ('tabledesign', 'f', 'f', '\"<TABLE BODER=1 WIDTH=50%><TH BGCOLOR=\".$headerbgcol.\">modify table</TH>\"', '\"<TR><TD BGCOLOR=\".$tablebgcol.\">\".$_.$values{\'tablename\'}."</A></TD>";', NULL, '$_.\"ORDER BY tablename\"');

---INSERT INTO tabledesign VALUES ('valuelist', 'f', 'f', NULL, '$_.$values{\'name\'}.":</A> ".$values{\'display\'}."<BR>";', NULL, '$_.\"ORDER BY name,display\"');
---INSERT INTO tabledesign VALUES ('designinfo', 'f', 'f', NULL, '$values{\'tablename\'}.": $_".$values{\'fieldname\'}."</A><BR>";', NULL, '$_.\"ORDER BY tablename,fieldname\"');
---INSERT INTO tabledesign VALUES ('equation', 'f', 'f', NULL, '$_.$values{\'eqname\'}."</A><BR>";', NULL, '$_.\"ORDER BY eqname\"');
---INSERT INTO tabledesign VALUES ('virtual', 'f', 'f', NULL, '$values{\'tablename\'}." (".$values{\'xlevel\'}."): $_".$values{\'fieldname\'}."</A><BR>";', NULL, '$_.\"ORDER BY tablename,xlevel,fieldname\"');
---INSERT INTO tabledesign VALUES ('relation', 'f', 'f', NULL, '$_.$values{\'parent\'}." <-> ".$values{\'child\'}."</A><BR>";', NULL, '$_.\"ORDER BY parent,child\"');





---
--- This table stores any perl subroutine you like
--- which later can be accessed from your virtual fields.
--- The CGI and Postgres library are accessable and there
--- is an open connection to the database in $conn.
--- You can also call subroutines which are stored in the table
--- equation through &callDBaseSub (<nameOfRoutine>, ...) where
--- ... stands for your subroutines argument list.
--- All real fields of the current record are stored in the ass.
--- array %values and can be accessed through $values{'nameOfField'}.
--- This is also true for virtual fields with their level set to a
--- lower value than the leven of the current virtual field-.
DROP TABLE EQUATION;
CREATE TABLE equation (
	eqname	text,	---	name of this equation
	content	text	---	Perl subroutine
);



---
--- This table stores the virtual fields for all of your tables.
--- Virtual fields are not stored in the database. Instead their
--- value is found at runtime by evaluting their equation field
--- which can hold any legal Perl subroutine.
--- The CGI and Postgres library are accessable and there
--- is an open connection to the database in $conn.
--- You can also call subroutines which are stored in the table
--- equation through &callDBaseSub (<nameOfRoutine>, ...) where
--- ... stands for your subroutines argument list.
--- All real fields of the current record are stored in the ass.
--- array %values and can be accessed through $values{'nameOfField'}.
--- This is also true for virtual fields with their level set to a
--- lower value than the leven of the current virtual field-.
DROP TABLE virtual;
CREATE TABLE virtual (
	tablename		text,	--- name of table which is extended by this virtual field
	fieldname		text,	--- name of this virtual field
	fieldtype		text,	--- type of this virtual field
	xlevel			int,	--- evaluation level of this virtual field (lower numbers are calculated first)
	xequation		text	--- Perl subroutine to calculate the fields value
);

delete from valuelist where name like 'displayinfo';
delete from valuelist where name like 'fieldtype';
delete from valuelist where name like 'xlevel';

INSERT INTO valuelist VALUES ('displayinfo', 'textarea 50 100', 'textarea 50 100');
INSERT INTO valuelist VALUES ('displayinfo', 'textarea 25 100', 'textarea 25 100');
INSERT INTO valuelist VALUES ('displayinfo', 'textarea 10 45', 'textarea 10 45');
INSERT INTO valuelist VALUES ('displayinfo', 'textarea 3 45', 'textarea 3 45');
INSERT INTO valuelist VALUES ('displayinfo', 'popup', 'popup');
INSERT INTO valuelist VALUES ('displayinfo', 'tablepopup', 'tablepopup');
INSERT INTO valuelist VALUES ('fieldtype', 'int', 'int');
INSERT INTO valuelist VALUES ('fieldtype', 'float', 'float');
INSERT INTO valuelist VALUES ('fieldtype', 'date', 'date');
INSERT INTO valuelist VALUES ('fieldtype', 'text', 'text');
INSERT INTO valuelist VALUES ('xlevel', '1', '1');
INSERT INTO valuelist VALUES ('xlevel', '2', '2');
INSERT INTO valuelist VALUES ('xlevel', '3', '3');
INSERT INTO valuelist VALUES ('xlevel', '4', '4');
INSERT INTO valuelist VALUES ('xlevel', '5', '5');



