Hi all!
Eric Pugh has offered a very useful extension to dbforms
regarding the support of foreign keys. This post summarizes
what he has contributed and hopefully starts a discussion
about several questions that might arise. Everyone is welcome
to send comments. This is a important extension, it opens
the way to automatically create lookups, nested forms,
navigation between tables etc.
I surely misunderstood or forgot the one or other thing,
but Eric might correct me. I think it is important to
document what is going on and start a discussion. And we
should do that better now than in two weeks.
Erics contributions:
1) extend dbforms-config.xml to support the
declaration of foreign keys. Here is an excerpt
from eric's summary from developer's list:
<table name="TRAFFIC">
...
<field name="EMAIL_ID" fieldType="int" size="10"/>
<field name="CAMPAIGN_ID" fieldType="int" size="10"/>
...<foreign-key foreignTable="EMAIL">
<reference local="EMAIL_ID" foreign="EMAIL_ID"/>
</foreign-key>
<foreign-key foreignTable="CAMPAIGN_ID">
<reference local="CAMPAIGN_ID" foreign="CAMPAIGN_ID"/>
</foreign-key>
<!-- add "granted-privileges" element for security constraints -->
</table>
Eric has oriented the syntax at torque, this is an excerpt from torque
database dtd (taken from
http://cvs.apache.org/viewcvs/jakarta-turbine-torque/src/dtd/database.dtd?rev=1.13&content-type=text/vnd.viewcvs-markup):
<!ELEMENT foreign-key (reference+)>
<!ATTLIST foreign-key
foreignTable CDATA #REQUIRED
name CDATA #IMPLIED
onUpdate (cascade|setnull|restrict|none) "none"
onDelete (cascade|setnull|restrict|none) "none"
>
<!ELEMENT reference EMPTY>
<!ATTLIST reference
local CDATA #REQUIRED
foreign CDATA #REQUIRED
>
I would agree to Eric to use that syntax, it supports:
- multiple columns within one key
- giving names to the constraints
- specifying rules what should happen in case of deletion, updates
An sql foreign key
alter table order
constraint customer_in_order
add foreign key (area_id, cust_id)
references customer (area_id, cust_id)
on delete cascade
on update restrict
could be written as
<foreign-key foreignTable="customer" name="customer_in_order"
onDelete="cascade" onUpdate="restrict">
<reference local="area_id" foreign="area_id"/>
<reference local="cust_id" foreign="cust_id"/>
</foreign-key>
And within torque there is already a module that reads database
metadata and creates such a xml section (isn't there?). It should be possible (Eric
already made some work some time ago) to adapt that for dbforms
and add this functionality to devgui.
Some of these attributes might not be used currently within dbforms,
but i think it would be a good idea to agree on that format and also
to update dbforms to read this information into internal
DbFormsConfig for later use.
I hope we can quickly agree on that.
2) Eric's idea:
a) Extend xsl stylesheets used by devgui to generate initial
JSPs to use that foreign key information. Instead of
including a textual input field, e.g. a select field should appear
automatically filled with column values from referenced
table.
b) do _not_ automatically create a select tag whereever
a reference is. That would be no good idea if referenced
tables contain millions of rows.
b) Instead of presenting the user an internal identifier
that might be used for internal references, but does not mean
anything to end users, other columns are presented within
the select field. That corresponds e.g. to the visibleFields
attribute within the tableData tag.
So we need an extension within config file to specify which
columns should be rendered as select tag instead of simple
input fields and which column(s) should be presented to the
end user when filling the select tag. Here is Eric's
suggestion: A new Tag
<layout-config>
<col name="EMAIL_ID" displayField="EMAIL" displayType="select"/>
...
</layout-config>
within dbforms config file. Eric's stylesheets transform this to
<db:select fieldName="EMAIL_ID">
<db:tableData ... foreignTable="EMAIL"
visibleFields="EMAIL" storeField="EMAIL_ID" />
</db:select>
I see following topics to talk about:
1) The column name is used to state that a select tag
has to be created. What if we have several columns with
same name but different semantics within the database?
wouldn't it be better to use the reference name instead?
That should be unique at least within one config file...
2) What if a reference consists of several columns?
That is a base problem: Current select tag does not support
this. Theoretically that should be possible. Have the
select tag separated from the other tags and whenever it is
set, use a bit of Javascript to update several form
fields with the values of the key.
But currently that is not possible. Would be ok for me
it it just works for single-column references and generates
an error in other cases. But must be documented.
3) tableData tag supports multiple visibleFields, why not
do the same here and also rename the attribute to use
the same name as within tableData?
4) tableData supports a format tag that might be used
to do something like
.. visibleFields="firstName,name,phone"
format="%s %s (Phone %s)"
(or similar, it is currently a bit in discussion)
I'd suggest to support that here, too. Should be just passed...
So it would become something like (format attribute not used):
<layout-config>
<ref name="customer_in_order" visibleFields="first_name,name,phone"
displayType="select"/>
...
</layout-config>
Another example:
<ref name="customer_ref2"
visibleFields="firstName,name,phone" format="%s %s (Phone %s)"
displayType="select"/>
5) Last question:
Would it make sense to include this information nested within
the foreign-key tag itself? Something like:
<foreign-key foreignTable="EMAIL" name="email_in_order"
onDelete="cascade" onUpdate="restrict">
<reference local="email_id" foreign="email_id"/>
<display visibleFields="EMAIL" displayType="select"/>
</foreign-key>
I must admit, I cannot currently oversee what that would mean
for the xsl stylesheets, if that would be difficult or not. It is
just a question of what syntax we would like to use within
config file.
Regards
Dirk
-------------------------------------------------------
This sf.net email is sponsored by:ThinkGeek
Welcome to geek heaven.
http://thinkgeek.com/sf
_______________________________________________
DbForms Mailing List
http://www.wap-force.net/dbforms
