Hi group,
I'm interested to see that other people have hit this problem. In the last
few months I've been writing and re-writing PHP classes to access a
postgreSQL database for a CMS.
Here is a rough overview of the way I've dealt with the validation problem.
tuple class
array of attribute class objects
initialisation fetches metadata about the table/view the tuple (row)
belongs to (attribute types, domains & built in CHECK constraints, foriegn
keys, not null)
this data comes from a set of attribute metadata views (built from
system catalogs and optimised - further optimisation is envisaged for PG
7.4)
the tuple / attribute metadata allows us to do the following things
foreign key attributes can auto-build a list of options for a <select>
dropdown (either multiple selection using a join table, or single selection)
the options in the list come from following the foreign key definitions.
Specially named views (schema.table/view.attribute) are used to get labels
for meaningful display,
so you don't have drop down lists with your externally meaningless
primary keys, but nice values (e.g. value = doc_id and label = doc_title)
if no view is found for the table/column, then just the values are used
as labels.
validation is doen using the folowing procedure
for each attribute in the tuple
"SELECT 'newvalue'::datatype"
if this fails, then it is a parse error, and the message "invalid
[attribute name]" is registered with the object against that attribute
then for each check constraint (both single and multiple column) defined
against the table (including where domains are used as attribute data types)
we run the following query
"SELECT [check constraint definition] AS [check constraint name] FROM
([for each attribute in the CHECK, output 'value'::type AS name,]) AS
values"
the result will be true or false, and the name of the only reutrned
column is the name of the check that passed/failed
if you have named your check contstraints sensibly, then the name is
used (if it failed) as an error message against the attribute(s) involved in
the check.
if all the values parse correctly, and all the check constraints are
passed - THEN we try to do the INSERT/UPDATE
other jobs the objects can do is insert parsed default values (from the
metadata) to display initially in the form.
Sadly, all this 'niceness' comes at a cost, because of all the extra queries
that must be executed in order to get back full validation failure messages
from PG.
Once we have released, then it might be possible to try and go back and make
some of this functionality available inside PHP, by adding to the pg_xxx
commands
available in PHP (particularly pg_meta_data())
This tuple object is then renderable as XHTML form elements (with
session/db/table/attirbute unique element names md5'd) so that multiple
tables can be exposed on a single form.
I'd be interested to hear from any other people who've built similar
systems, or about any database abstraction projects which attempt to do the
'possible value lookup' and full validation
descripbed above. Our code was working with 7.3.4 fairly well, and should be
back up and finished soon with 7.4. Once the project is compelted, I'll see
about releasig a more generic set
of classes (our are currently tied to a project-specific inheritance layer)
or expanding this into a gBorg project or similar.
--
--
Tom Hebbron
www.hebbron.com
+39 0444540626 (Vicenza, Italy)
