Ok, time for a first draft of the documentation
about foreign key support within dbforms. The following
might be a base for a new section within the manual.
I hope it contains a summary of what we have agreed to.
If something can be misunderstood or I have forgotten stuff,
please correct it. Same for any kind of mistakes.
Eric, what do you say?
--------------------------------------------------------------------------
(section) Foreign Key support within dbforms
(subsection) Introduction
Contributed by Eric Pugh, DbForms contains limited support for
foreign keys within database schemas.
DbForms does currently not check foreign key constraints
or start cascading actions like updating foreign keys within a
referencing table whenever the key of a referenced row is
updated. But:
- There is support to express foreign keys within
dbforms config file.
- Foreign key information is available within
running web application to be used by servlets or JSPs.
- Devgui tries to include foreign key information automatically
within dbforms config file by using JDBC metadata methods
- Some of the stylesheets used by devgui to automatically generate
JSPs use foreign key information from config file. So they are
able to generate select tags etc. to allow the user to choose
from rows within another table.
(subsection) Foreign-Key tag within dbforms-config.xml
Foreign key infomation within config file has to be expressed using
the foreign-key tag that has to be nested within the table tag
belonging to the referencing tables. Here is an excerpt from the DTD:
<!ELEMENT foreign-key (reference+)>
<!ATTLIST foreign-key
foreignTable CDATA #REQUIRED
name CDATA #IMPLIED
visibleFields CDATA #IMPLIED
format CDATA #IMPLIED
displayType (select|radio|none) "none"
>
<!ELEMENT reference EMPTY>
<!ATTLIST reference
local CDATA #REQUIRED
foreign CDATA #REQUIRED
>
Let's say you have created a database containing tables:
create table customer
(cno integer not null primary key,
first_name char(20) not null,
last_name char(20) not null
phone char(20) not null,
...
)
create table order
(orderno integer not null primary key,
cust_no integer not null,
order_date date not null,
...
constraint customer_known_in_order
foreign key (cust_no)
references customer(cno)
)
Then within dbforms-config.xml you could write:
<table name="customer">
<field name="cno" fieldType="integer" isKey="true"/>
<field name="first_name" fieldType="char" size="20"/>
<field name="last_name" fieldType="char" size="20"/>
<field name="phone" fieldType="char" size="20"/>
...
<table>
<table name="order">
<field name="cno" fieldType="integer" isKey="true"/>
<field name="cust_no" fieldType="integer"/>
<field name="order_date" fieldType="date"/>
...
<foreign-key foreignTable="customer"
name="customer_in_order"
displayType="select"
visibleFields="first_name,last_name,phone"
format="%s %s (Phone %s)" >
<reference local="cust_no" foreign="cno"/>
</foreign-key>
</table>
- the foreign-key has to be nested within the referencing table
- attributes:
- foreignTable: name of referenced table (required)
- name: name of this constraint (optional, currently not used)
- displayType: This is a suggestion how to present this
reference within an automatically generated page
- select: create a select tag
- radio : create a set of radio fields
- none : ignore reference, do nothing (default if not set)
- visibleFields: which columns of the referenced table
shall be presented to the user? This gets directly passed
to corresponding attribute within tableData tag, see
docs there
- format: which format pattern shall be used to format
the viviblsFields? See docs for tag tableData for details.
- nested tags reference: models pairs of columns that take part
in the reference:
- local: name of the referencing column
- foreign: name of the referenced column
If a key consists of more than one column, there will be
more than one nested tag, e.g. if the customer is just
unique within an area, we'd have:
<foreign-key foreignTable="customer" ...>
<reference local="area_id" foreign="area_id"/>
<reference local="cust_no" foreign="cno"/>
</foreign-key>
(subsection) Support within XSL Stylesheets
Some of the stylesheets used by devgui use the information
about foreign keys to create automatically tags allowing
to select from a referenced table. In the above example, we
might get a:
<db:select fieldName="cust_no">
<db:tableData
name="customer_in_order"
foreignTable="customer"
visibleFields="first_name,last_name,phone"
format="%s %s (Phone %s)"
storeField="cno" />
</db:select>
QUESTION---------------------------------------------------------
What else will we have?
- automatically generated master-detail forms?
- automatically generated navigation?
END OF QUESTION--------------------------------------------------
(subsection) New Attributes for table tag: defaultVisibleFields,
defaultVisibleFieldsFormat
If a table is referenced from lots of other tables, it might
be neccessary to repeat the attribute values for visibleFields
and format within each foreign-key tag again and again. To avoid
this, two new attributes for the table tag have been introduced:
- defaultVisibleFields sets the default for attribute visibleFields
whenever referencing to this table
- defaultVisibleFieldsFormat does the same for format
So the above example could have been rewritten as:
<table name="customer"
defaultVisibleFields="first_name,last_name,phone"
defaultVisibleFieldsFormat="%s %s (Phone %s)">
<field name="cno" fieldType="integer" isKey="true"/>
<field name="first_name" fieldType="char" size="20"/>
<field name="last_name" fieldType="char" size="20"/>
<field name="phone" fieldType="char" size="20"/>
...
<table>
<table name="order">
<field name="cno" fieldType="integer" isKey="true"/>
<field name="cust_no" fieldType="integer"/>
<field name="order_date" fieldType="date"/>
...
<foreign-key foreignTable="customer"
name="customer_in_order"
displayType="select" >
<reference local="cust_no" foreign="cno"/>
</foreign-key>
</table>
(subsection) Detection of references within DevGui
DevGui uses JDBC metadata methods to detect foreign keys and
will automatically insert corresponding foreign-key tags into
generated dbforms-config.xml. This will surely only work if
the JDBC driver has support for this feature, otherwise the
file has to be edited manually. DevGui sets several default
values, which might be edited before applying xsl stylesheets:
- if a key contains just one column, the displayType is set to "select",
if it contains more, displayType is set to "none". This is a problem
of the underlying tags that will later be used within web application.
- devgui does not set the visibleFields attribute within foreign-key
tags, instead it sets the defaultVisibleFields attribute within each
table. The attribute will initially be set to the primary key.
The displayType or defaultVisibleFields attribute might be changed before applying
the xsl stylesheets. It might also be a good idea to set a corresponding
defaultVisibleFieldsFormat.
-------------------------------------------------------
This sf.net email is sponsored by: viaVerio will pay you up to
$1,000 for every account that you consolidate with us.
http://ad.doubleclick.net/clk;4749864;7604308;v?
http://www.viaverio.com/consolidator/osdn.cfm
_______________________________________________
DbForms Mailing List
http://www.wap-force.net/dbforms
