---
--- 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	VARCHAR2(30),
	DISPLAY	VARCHAR2(30),
	CONTENT	VARCHAR2(100)
);



---
--- 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				VARCHAR2(30),
	PARENTFIELD			VARCHAR2(30),
	CHILD				VARCHAR2(30),
	CHILDFIELD			VARCHAR2(30),
	CHILDORDER			VARCHAR2(30),
	REMOVECHILDONPARENTDELETE	INTEGER
);



---
--- 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	VARCHAR2(30),	--- name of table
	FIELDNAME	VARCHAR2(30),	--- name of the field that gets its specific design information here
	DISPLAYINFO	VARCHAR2(50),	--- (optional) display mode as follows:
					---   textarea height width
					---          displays the CONTENTs of this field in form of
					---          a HTML CLOBarea 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 CLOB 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	CLOB,		--- (optional) display string for use instead of the FIELDNAME
					--- when creating HTML dialogs
	DISPLAYORDER	INTEGER,	--- (optional) order in which fields are shown in HTML dialogs
	DISPLAYCOLUMN	INTEGER,	--- (optional) column in which fields are shown in HTML dialogs
	XDEFAULT	CLOB,		--- (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	CLOB		--- (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 at_
					--- 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 at 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';
delete from designinfo where tablename like 'RELATION';

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 ('TABLEDESIGN', 'NEEDSVIRTUALS', 'intasbool', NULL, NULL, NULL, NULL, NULL);
INSERT INTO DESIGNINFO VALUES ('TABLEDESIGN', 'MODIFYABLELIST', 'intasbool', 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);
INSERT INTO DESIGNINFO VALUES ('RELATION', 'REMOVECHILDONPARENTDELETE', 'intasbool', 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	VARCHAR2(30),	--- name of table
	NEEDSVIRTUALS	INTEGER,	--- flag that specifies whether or not the virtual fields
					--- have to be calculated prior to the execution of our
					--- Perl routine.
	MODIFYABLELIST	INTEGER,	--- the table display in list mode allows for editable fields
					--- and a multiple update feature for all of these is enabled
	XTABLESTART	CLOB,		--- (optional) Perl subroutine that returns the HTML string
					--- that has to be used prior to listing the records
	XTABLEITEM	CLOB,		--- Perl subroutine which has to return the HTML string that
					--- shall be used to represent the current record (held in
					--- the %values array)
	XTABLEEND	CLOB,		--- (optional) Perl subroutine that returns the HTML string
					--- that had to be used after listing the records
	XXQUERYSTRING	CLOB		--- (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', 0, 0, NULL, NULL, NULL, '$_.\"ORDER BY NAME,DISPLAY\"');
INSERT INTO TABLEDESIGN VALUES ('DESIGNINFO', 0, 0, NULL, NULL, NULL, '$_.\"ORDER BY TABLENAME,FIELDNAME\"');
INSERT INTO TABLEDESIGN VALUES ('EQUATION', 0, 0, NULL, NULL, NULL, '$_.\"ORDER BY EQNAME\"');
INSERT INTO TABLEDESIGN VALUES ('VIRTUAL', 0, 0, NULL, NULL, NULL, '$_.\"ORDER BY TABLENAME,XLEVEL,FIELDNAME\"');
INSERT INTO TABLEDESIGN VALUES ('RELATION', 0, 0, NULL, NULL, NULL, '$_.\"ORDER BY PARENT,CHILD\"');

INSERT INTO TABLEDESIGN VALUES ('TABLEDESIGN', 0, 0, '"<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', 0, 0, NULL, '$_.$values{\'name\'}.":</A> ".$values{\'display\'}."<BR>";', NULL, '$_.\"ORDER BY name,display\"');
---INSERT INTO TABLEDESIGN VALUES ('DESIGNINFO', 0, 0, NULL, '$values{\'TABLENAME\'}.": $_".$values{\'FIELDNAME\'}."</A><BR>";', NULL, '$_.\"ORDER BY tablename,FIELDNAME\"');
---INSERT INTO TABLEDESIGN VALUES ('EQUATION', 0, 0, NULL, '$_.$values{\'EQNAME\'}."</A><BR>";', NULL, '$_.\"ORDER BY EQNAME\"');
---INSERT INTO TABLEDESIGN VALUES ('VIRTUAL', 0, 0, NULL, '$values{\'TABLENAME\'}." (".$values{\'level\'}."): $_".$values{\'FIELDNAME\'}."</A><BR>";', NULL, '$_.\"ORDER BY tablename,level,FIELDNAME\"');
---INSERT INTO TABLEDESIGN VALUES ('RELATION', 0, 0, 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	VARCHAR2(30),	---	name of this EQUATION
	CONTENT	CLOB	---	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	VARCHAR2(30),	--- name of table which is extended by this virtual field
	FIELDNAME	VARCHAR2(30),	--- name of this virtual field
	FIELDTYPE	VARCHAR2(30),	--- type of this virtual field
	XLEVEL		INTEGER,	--- evaluation level of this virtual field (lower numbers are calculated first)
	XEQUATION	CLOB		--- 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', 'hidden', 'hidden');
INSERT INTO VALUELIST VALUES ('DISPLAYINFO', 'intasbool', 'intasbool');
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');


