Thread: Reducing duplicated business rules

Hi all

I've been coding in PHP and PostgreSQL for a while now—nothing
complicated but what I've put together has gotten the job done. I
haven't employed templating yet, but have managed to separate a good
deal of the application logic from the presentation by calling
functions, and am happily building libraries of functions I commonly
use. One thing I'm particularly pleased about is that I've reached a
point where I can look back at the first implementations and say to
myself that I know I can code and refactor it better now. Nice to be
able to see improvement.

One area I'd definitely like to improve is filtering form data. I don't
mean preventing SQL injection or other security issues—those are
definitely important and something I'm working on as well—but rather
type and constraint checking before inserting or updating data into the
database. I've been including as much business logic as I can into the
database to maintain data integrity (regardless of what I might
foolishly allow a user to do via one of the scripts I write :) ), but I
find myself duplicating these same checks in my code so I can (a)
prevent getting errors from the database when inserting/updating and
(b) give feedback to the user, letting them know what it is I'd like
them to correct.

A simple example is when I'd like to make sure a given date is before
or after another. For example, an examination date must follow a
registration date. This is enforced by the database (check
registration_date < examination_date) but I will do a similar check in
the PHP code as well, providing feedback to the user that the
examination date must be after the registration date.

Of course PostgreSQL will throw back an error if I tried to insert or
update data that will make the check constraint untrue, which is as it
should be. Is there any way to use this feedback from Postgres instead
of running my own checks in PHP, and still provide useful feedback to
the user (rather than the naked PostgreSQL error code)?

Along similar lines, often I have a requirement in another table, for
example the start and end dates of a registration period. Of course
registration dates should fall between these two. As far as I can tell,
this kind of constraint isn't possible in PostgreSQL via CHECK
constraints. (I'm guessing it's possible via before triggers, but I
haven't looked at triggers yet. I'm still quite wet behind the ears!)
In this case I grab the start and end dates in a select, and use them
as bounds to check the submitted registration date in the PHP code.

Are there other ways to handle these situations that I'm missing? Any
opinions, suggestions, helpful advice, or pointers in the direction of
further knowledge gratefully accepted.

Regards,
Michael
grzm myrealbox com

My preference would be towards identifying bad data as soon as possible and
thus provide the user a better (more interactive) experience.  For me this
means more clevers on the client - ie some JavaScript to check dates,
formats, etc.  This is relatively simple to implement as a function invoked
by the "SEND" or "OK" button and which eventually does a form.submit() if
everything is okay.  If there is a problem somewhere, you can alert the user
to the problem, set the form focus to the offending input value and then
allow the user to re-enter data.  Even better would be to call validation
after each field has been changed so the user doesn't have to fill in the
entire form before his or her errors are identified.

The other idea is to create some PostgreSQL-stored metadata with the form so
that the rules for validation can be read from the database.  This would
allow PHP to read the validation rules for an input field (eg date must be
in the future) and validate the field generically. It would also allow the
PHP creating the input form in the first place to provide extra information
in the form so that the pre-submit JavaScript function could also be
generic - the same validation script would then read the validation rules -
perhaps from hidden input fields and validate the corresponding input value.
The metadata could include a PROMPT message that tells the user what is
needed in a particular field.

Hope this helps

Peter

----- Original Message -----
From: "Michael Glaesemann" <grzm@myrealbox.com>
To: <pgsql-php@postgresql.org>
Sent: Wednesday, 05 November, 2003 3:50 AM
Subject: [PHP] Reducing duplicated business rules


Hi all

I've been coding in PHP and PostgreSQL for a while now—nothing
complicated but what I've put together has gotten the job done. I
haven't employed templating yet, but have managed to separate a good
deal of the application logic from the presentation by calling
functions, and am happily building libraries of functions I commonly
use. One thing I'm particularly pleased about is that I've reached a
point where I can look back at the first implementations and say to
myself that I know I can code and refactor it better now. Nice to be
able to see improvement.

One area I'd definitely like to improve is filtering form data. I don't
mean preventing SQL injection or other security issues—those are
definitely important and something I'm working on as well—but rather
type and constraint checking before inserting or updating data into the
database. I've been including as much business logic as I can into the
database to maintain data integrity (regardless of what I might
foolishly allow a user to do via one of the scripts I write :) ), but I
find myself duplicating these same checks in my code so I can (a)
prevent getting errors from the database when inserting/updating and
(b) give feedback to the user, letting them know what it is I'd like
them to correct.

A simple example is when I'd like to make sure a given date is before
or after another. For example, an examination date must follow a
registration date. This is enforced by the database (check
registration_date < examination_date) but I will do a similar check in
the PHP code as well, providing feedback to the user that the
examination date must be after the registration date.

Of course PostgreSQL will throw back an error if I tried to insert or
update data that will make the check constraint untrue, which is as it
should be. Is there any way to use this feedback from Postgres instead
of running my own checks in PHP, and still provide useful feedback to
the user (rather than the naked PostgreSQL error code)?

Along similar lines, often I have a requirement in another table, for
example the start and end dates of a registration period. Of course
registration dates should fall between these two. As far as I can tell,
this kind of constraint isn't possible in PostgreSQL via CHECK
constraints. (I'm guessing it's possible via before triggers, but I
haven't looked at triggers yet. I'm still quite wet behind the ears!)
In this case I grab the start and end dates in a select, and use them
as bounds to check the submitted registration date in the PHP code.

Are there other ways to handle these situations that I'm missing? Any
opinions, suggestions, helpful advice, or pointers in the direction of
further knowledge gratefully accepted.

Regards,
Michael
grzm myrealbox com


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

> Of course PostgreSQL will throw back an error if I tried to insert or
> update data that will make the check constraint untrue, which is as it
> should be. Is there any way to use this feedback from Postgres instead
> of running my own checks in PHP, and still provide useful feedback to
> the user (rather than the naked PostgreSQL error code)?

I am right now also looking how to get a feedback from a postgrsql-check back to php.

I thought about using named constraints:

CREATE TABLE test (
id                            SERIAL,
[whatever fields you need]

CONSTRAINT "!!!#Please check XYZ in the formular#" CHECK ([whatever you like to test])
);

Next, I would check in php if an error occured in the sql-query, if the error includes "!!!" in the error message from
postgresqlI would display the formular again with the values already entered and I would display as a feedback to the
userthe string between the two "##": 

Please check XYZ in the formular

One drawback is of course that it will only give feedback from one error at a time.


greetings,
Daniel

--
Retrovirology Laboratory Luxembourg
Centre Hospitalier de Luxembourg
4, rue E. Barblé
L-1210 Luxembourg

phone: +352-44116105
fax:   +352-44116113
web: http://www.retrovirology.lu
e-mail: struck.d@retrovirology.lu

Peter

Thanks for your reply. It's good to hear others thoughts on this.

On Thursday, November 6, 2003, at 10:46 AM, Peter Bayley wrote:

> My preference would be towards identifying bad data as soon as
> possible and
> thus provide the user a better (more interactive) experience.  For me
> this
> means more clevers on the client -


> ie some JavaScript to check dates, formats, etc.

I've seen elsewhere people using JavaScript for checking these things.
I do the same thing within PHP, primarily because I don't know
JavaScript and have been able to get the job done (I think) in PHP
alone. I ask this from a position of ignorance, not of questioning your
judgement: What is the advantage of using JavaScript over PHP for
checking? This is completely a wild guess on my part, but does it have
to do with server load? I know to use PHP to check these things I end
up sending the form back to the server for processing. Looking again at
what you've written, I'm guessing this is exactly it. Using JavaScript
allows the client—rather than the server—do the checking. It seems to
me you'd pay an initial hit (downloading more into the client), but you
don't have to keep using the server to process/validate/check the data.
Am I close?

> Even better would be to call validation after each field has been
> changed so the user doesn't have to fill in the entire form before his
> or her errors are identified.

This is something I've been doing as well, or at least whenever a user
triggers some action (like a <select onchange="submit()">). I've
definitely noticed the delay in reloading the page, especially when I
have a long select list that's generated based on previously submitted
data (e.g., modifying select b's options depending on the choice in
select b).

> The other idea is to create some PostgreSQL-stored metadata with the
> form so
> that the rules for validation can be read from the database.

I've thought about this as well, but not enough to go about
implementing it. A lot of this thinking came from (as I mentioned in my
original post) finding I was writing a lot of validation code—actually
it seems *most* of my form code is validation :)—and reading about
Dataphor from material available from their site (www.alphora.com). It
appears that all of the information necessary for validation is housed
within the Dataphor DAE (Data Access Engine), and applications are able
to query the DAE to find out whether something would be accepted,
rather than just trying to insert or update the database and finding it
wouldn't work. Then whatever clients/apps accessed the DAE would have
access to the validation abilities, rather than including it in every
client/app. I'd like to give it a try, but I don't have a machine I can
run .NET on (even if I wanted to). Interesting stuff.

>  This would allow PHP to read the validation rules for an input field
> (eg date must be
> in the future) and validate the field generically.

You know, this information has to be in the database already, right?
PostgreSQL itself has to store the integrity information somewhere (I'm
guessing the system catalogs). Just finding a way to make this more
accessible to the script. From what I've read (don't remember where),
one of the things that makes PostgreSQL special is it's extensive
system catalogs. Looking briefly, there's a system catalog called
pg_constraint which includes attributes such as consrc ("If a check
constraint, a human-readable representation of the expression"), and
contypid ("The domain this constraint is on; 0 if not a domain
constraint"). An immediate thought is that I don't see a way to do
domain checking. That of course doesn't mean there necessarily isn't
one—just thinking out loud here.

> It would also allow the PHP creating the input form in the first place
> to provide extra information in the form so that the pre-submit
> JavaScript function could also be
> generic - the same validation script would then read the validation
> rules -
> perhaps from hidden input fields and validate the corresponding input
> value.
> The metadata could include a PROMPT message that tells the user what is
> needed in a particular field.

It appears I should take a look at JavaScript (there's always another
language to learn!) and explore the system tables a little more. It
seems like there might be a way to make a layer between the PHP and
PostgreSQL that handles the constraint interpretation—in some generic
fashion that would allow it to talk to a variety of clients. (Maybe
there's already one out there somewhere.)

Thanks again for your comments. You've given me things to think about
and ideas about directions to go.

Michael
grzm myrealbox com

Hi Daniel,

On Friday, November 7, 2003, at 12:52 AM, Daniel Struck wrote:

>> Of course PostgreSQL will throw back an error if I tried to insert or
>> update data that will make the check constraint untrue
>
> I thought about using named constraints:
<snip/>
> CONSTRAINT "!!!#Please check XYZ in the formular#" CHECK ([whatever
> you like to test])
> );
>
> Next, I would check in php if an error occured in the sql-query, if
> the error includes "!!!" in the error message from postgresql I would
> display the formular again with the values already entered and I would
> display as a feedback to the user the string between the two "##":

Interesting idea. Then you can include more specific information rather
than just the PostgreSQL error. I wonder if there wouldn't be a way to
use COMMENT information on the constraint as well—grab the COMMENT for
whatever named constraint caused it to fail.

> One drawback is of course that it will only give feedback from one
> error at a time.

I don't know if this is the best way to do this, but it might be
workable in the short term. It would be nice to be able to ask the
database, in effect "Okay, yeah, that's not a good piece of data. But
let's assume that part were okay. Any other problems? Besides that?"
Then you could possibly get more feedback from the database.

On Fri, Nov 07, 2003 at 01:16:54 +0900,
  Michael Glaesemann <grzm@myrealbox.com> wrote:
>
> what you've written, I'm guessing this is exactly it. Using JavaScript
> allows the client—rather than the server—do the checking. It seems to
> me you'd pay an initial hit (downloading more into the client), but you
> don't have to keep using the server to process/validate/check the data.
> Am I close?

The server still has to do the validity checking. Often pages set up
this way don't work when javascript isn't available. What you save
are round trips when the data is messed up. That may or may not be
better depending on how large the form is wtih and without javascript,
what the rate of mistakes is, what the available bandwidth is and
how loaded the server is.

i use this validation function. works really well for me

http://validator.munk.nu/validator.phps

__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree

> Interesting idea. Then you can include more specific information rather
> than just the PostgreSQL error. I wonder if there wouldn't be a way to
> use COMMENT information on the constraint as well_grab the COMMENT for
> whatever named constraint caused it to fail.


I think for normal purposes, the name you can give a constraint should be sufficient.
Else one can think of giving an error number as a name for the constraint and keep the description of the error number
ina different table. 



> I don't know if this is the best way to do this, but it might be
> workable in the short term. It would be nice to be able to ask the
> database, in effect "Okay, yeah, that's not a good piece of data. But
> let's assume that part were okay. Any other problems? Besides that?"
> Then you could possibly get more feedback from the database.

True, that would be nice.
Maybe a mode where the database would should all the constraints and not one be one.

By the, this way of handling the format of the data makes your database more independent from a script language. In
factyou could use it in perl, java, asp, etc. and don't have to rewrite the whole constraints for submitting data
again.


Daniel

--
Retrovirology Laboratory Luxembourg
Centre Hospitalier de Luxembourg
4, rue E. Barblé
L-1210 Luxembourg

phone: +352-44116105
fax:   +352-44116113
web: http://www.retrovirology.lu
e-mail: struck.d@retrovirology.lu

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)

We have done the same thing that you are doing - but we don't read the
database metadata at all, we just create an xml file when we create the
table that describes the datatype and html display type so we can have
custom types such as email address type, etc.

I thought about doing this the way you have, but ultimately I decided
that depending on the database metadata would be too limited for my
needs.

Attached is an xml file that describes one of our tables.  We have a
custom application that automatically build interfaces to add, edit,
list, view, and delete data based on this xml file with validation.

-r

On Thu, 2003-11-20 at 14:36, Tom Hebbron wrote:
> 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)
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
--
Ryan Mahoney
ryan@flowlabs.com
http://www.flowlabs.com (updated)