PREV TOC NEXT

dbengines describing database

Now you have full access to all your databases and tables through dbengine, but there's more dbengine can do for you.
I hope you've already installed the describing database cisinfo as mentioned earlier, so that you should be able to access this database via http://localhost/cgi-bin/dbengine/dbengine.cgi?dbase=cisinfo [Oracle: http://localhost/cgi-bin/dbengine/dbengine.cgi?dbase=cisinfo/zwerg, working sample for your convenience: http://cis-computer.com/cgi-bin/dbengine/dbengine.cgi?dbase=cisinfo].
This will give you access to a couple of tables that -when available- are used to adjust the automated form generation, output of search-result lists and create virtual fields.

For ALL the following keep in mind that table- and fieldnames referring to other parts of the database have to be written all lowercase for PostgreSQL and all UPPERCASE FOR ORACLE.

designinfo

This table is used for nothing but selecting how and where to show a field of your database in any automated form. You have to enter a record for each field whose appereance shall be changed. Here's what you can specify:
fieldname content
tablename name of the table in which the field lives whose appereance shall be changed
fieldname name of the field that gets its specific design information here
displayinfo (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
relationpopup table fieldName [displayFieldName] displays the result of a SELECT DISTINCT fieldName, displayFieldName FROM table in form of a popup menu where the HTML values are set to fieldName while the displayed string is set to displayFieldName.
displayFieldName however is optional and will be set to fieldName if not used.
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 Oracle INTEGER field as boolean, where the value 0 is interpreted as false while 1 means true and any other value undef
hidden fields marked hidden will not be shown
displaystring (optional) display string for use instead of the fieldname when creating HTML dialogs
displayorder (optional) order in which fields are shown in HTML dialogs
displaycolumn (optional) column in which fields are shown in HTML dialogs
xdefault (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 (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.

Sample

If we want the fields in our test database to be named diffently (for example "Part No" instead of "articleNr") and re-order them we could use the following specifications that have to be added to the cisinfo database:
INSERT INTO designinfo VALUES ('articles', 'articleNr',  NULL,                      'Part No',  1, 1, NULL);
INSERT INTO designinfo VALUES ('articles', 'name',       NULL,                      'Name',     2, 1, NULL);
INSERT INTO designinfo VALUES ('articles', 'info',      'textarea 3 45',            'Info',     3, 1, NULL);
INSERT INTO designinfo VALUES ('articles', 'category',  'popup',                    'Category', 4, 1, NULL);
INSERT INTO designinfo VALUES ('articles', 'margin',     NULL,                      'margin',   4, 2, NULL);

INSERT INTO designinfo VALUES ('prices', 'articleNr',	  NULL,                        'Part No', 1, 1, NULL);
INSERT INTO designinfo VALUES ('prices', 'vendor',    'relationpopup vendors name', 'Vendor',   2, 1, NULL);
INSERT INTO designinfo VALUES ('prices', 'inStock',    NULL,                         NULL,      2, 2, NULL);
INSERT INTO designinfo VALUES ('prices', 'price',      NULL,                        'Price',    3, 1, NULL);
This also enlarges the field info, so that it becomes a 3x45 textarea and makes a valuelist popup out of the field category.
Have a look at http://localhost/cgi-bin/dbengine/dbengine.cgi?dbase=cis&mode=plain&table=articles [Oracle: http://localhost/cgi-bin/dbengine/dbengine.cgi?dbase=cis/zwerg&mode=plain&table=ARTICLES, working sample http://cis-computer.com/cgi-bin/dbengine/dbengine.cgi?dbase=cis&mode=plain&table=articles] to see the effect.

equation

This table stores any perl subroutine you like which later can be accessed from your virtual fields or other Perl subroutines that are specified anywhere in this database.
The CGI and Postgres library plus alle the subroutines of dbengine 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 (, ...) 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'}.
fieldname content
eqname name of this equation if referred to by some call to &callDBaseSub
content Perl subroutine

Sample

We can put the body of any Perl subroutine into our contents field and call it from anywhere in our system later on, so I'll go ahead and show one of the easiest:
INSERT INTO equation VALUES ('max', '\
#\
# return the maximum of two given values\
# has to be called as &callDBaseSub ("max", $firstValue, $secondValue)\
local($valueA, $valueB) = @_;\
\
$valueA>$valueB?$valueA:$valueB;\
');

relation

This table conatains all your standard relations for the representation of your database in your Web browser.
Always when records out of the table 'parent' are displayed on the screen alle those records out of the table 'child' shall be shown simultaniously for which 'childField' eq 'parentField'.

There's also a field called removeChildOnParentDelete which, if set to true, causes all associated child records to be deleted when their parent record is removed from the database.

Sample

As for our test database it would definetely make sense to show all the related price informations for each article below it in a seperate frame, which can be donw with:
INSERT INTO relation VALUES ('articles', 'articleNr', 'prices', 'articleNr');

tabledesign

The table tabedesign is used to store closer information for the customized display of specific tables, wich allow to create some smarter list representation of their contents.
fieldname content
tablename name of the table for which this information is used
needsVirtuals Flag that specifies whether or not the virtual fields have to be calculated prior to the execution of our Perl routine for each item.
Keep in mind that the calculation of virtual fields might take quite a lot of time.
modifyableList The table display in list mode allows for editable fields and a multiple update feature for all of these is enabled.
If this feature is enabled the user will get a chance to select a "multiple update" button which may result in multiple update calls to the database.
Therefore you'll have to create editable fields in your xtableitem subroutine and make sure that the name of these fields is composed as a combination of the objects id $oid and the name of the field, so that dbengine knows which record to update (for example use "NAME=".$oid."price" to refer to the price field of the current record).
xtablestart (optional) Perl subroutine that returns the HTML string that has to be used prior to listing the records
xtableitem Perl subroutine which has to return the HTML string that shall be used to represent the current record (held in the %values array). A ready built HREF tag is given in $_ and the oid of the cureent record is provided in $oid.
xtableend (optional) Perl subroutine that returns the HTML string that had to be used after listing the records
xxquerystring (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 $_ );

Sample

The use of this configuaration table is best shown with a sample. Lets create an entry for our price table which allows us to modify prices directly in the search-result list and only lists some of the tables fields.
Open your description database [Oracle: use this]and add the following contents to your tabledesign database:
fieldname content
tablename prices
needsVirtuals false
modifyableList true
other fields look here...

valuelist

The table valuelist is used to store the contents of popup menues that are accessed using the popup entry in the displayinfo field of a desinginfo record.
You may add as many entries as you like for each fieldname name.
fieldname content
name name of the field for which this record represents a popup item
display the string displayed in the popup
content the real content -if any- that shall be associated with the given display string for this field

Sample

As for our test database it would definetely make sense to show all the related price informations for each article below it in a seperate frame, which can be donw with:
INSERT INTO valuelist VALUES ('category', 'hardware');
INSERT INTO valuelist VALUES ('category', 'software');
Have a look at http://localhost/cgi-bin/dbengine.cgi?dbase=test&mode=plain&table=articles after adding this record and you'll see that now the category popup is filled with the above options.

virtual

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 plus alle the subroutines of dbengine 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 (, ...) 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.
fieldname content
tablename name of table which is extended by this virtual field
fieldname name of this virtual field
fieldtype type of this virtual field - select from the popup
level evaluation level of this virtual field (lower numbers are calculated first, so that fields with higher numbers can access their results)
xequation Perl subroutine to calculate the fields value

Sample

I've created a virtual field for our articles database that finds the cheapest price of article. Open your description database and add the following contents to your tabledesign database:
fieldname content
tablename articles
fieldname cheapest
fieldtype float
level 1
other fields look here...
Have a look at http://localhost/dbengine/cgi-bin/dbengine.cgi?dbase=test&mode=search&table=articles&articleNr=x15r1201 after adding this record.

PREV TOC NEXT