Reducing duplicated business rules

Hi all

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

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

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

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

Along similar lines, often I have a requirement in another table, for=20
example the start and end dates of a registration period. Of course=20
registration dates should fall between these two. As far as I can tell,=20
this kind of constraint isn't possible in PostgreSQL via CHECK=20
constraints. (I'm guessing it's possible via before triggers, but I=20
haven't looked at triggers yet. I'm still quite wet behind the ears!)=20
In this case I grab the start and end dates in a select, and use them=20
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=20
opinions, suggestions, helpful advice, or pointers in the direction of=20
further knowledge gratefully accepted.

Regards,
Michael
grzm myrealbox com


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

Re: Reducing duplicated business rules

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"
To:
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


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: Reducing duplicated business rules

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

I am right now also looking how to get a feedback from a postgrsql-check ba=
ck 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 lik=
e to test])
);

Next, I would check in php if an error occured in the sql-query, if the err=
or includes "!!!" in the error message from postgresql I would display the =
formular again with the values already entered and I would display as a fee=
dback to the user the 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

--=20
Retrovirology Laboratory Luxembourg
Centre Hospitalier de Luxembourg
4, rue E. Barbl=E9
L-1210 Luxembourg

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: Reducing duplicated business rules

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

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

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

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

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

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

> It would also allow the PHP creating the input form in the first place=20
> to provide extra information in the form so that the pre-submit=20
> JavaScript function could also be
> generic - the same validation script would then read the validation=20
> rules -
> perhaps from hidden input fields and validate the corresponding input=20
> 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=20
language to learn!) and explore the system tables a little more. It=20
seems like there might be a way to make a layer between the PHP and=20
PostgreSQL that handles the constraint interpretation=97in some generic=20
fashion that would allow it to talk to a variety of clients. (Maybe=20
there's already one out there somewhere.)

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

Michael
grzm myrealbox com

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: Reducing duplicated business rules

am 06.11.2003 18:55:43 von Michael Glaesemann

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:

> CONSTRAINT "!!!#Please check XYZ in the formular#" CHECK ([whatever=20
> you like to test])
> );
>
> Next, I would check in php if an error occured in the sql-query, if=20
> the error includes "!!!" in the error message from postgresql I would=20
> display the formular again with the values already entered and I would=20
> display as a feedback to the user the string between the two "##":

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

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

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


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Re: Reducing duplicated business rules

am 06.11.2003 20:20:38 von Bruno Wolff III

On Fri, Nov 07, 2003 at 01:16:54 +0900,
Michael Glaesemann wrote:
>=20
> what you've written, I'm guessing this is exactly it. Using JavaScript=20
> allows the client=97rather than the server=97do the checking. It seems =
to=20
> me you'd pay an initial hit (downloading more into the client), but you=
=20
> don't have to keep using the server to process/validate/check the data.=
=20
> 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.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: Reducing duplicated business rules

am 08.11.2003 20:30:28 von Bruce Young

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

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

Re: Reducing duplicated business rules

am 09.11.2003 16:37:16 von Daniel Struck

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


I think for normal purposes, the name you can give a constraint should be s=
ufficient.
Else one can think of giving an error number as a name for the constraint a=
nd keep the description of the error number in a different table.



> I don't know if this is the best way to do this, but it might be=20
> workable in the short term. It would be nice to be able to ask the=20
> database, in effect "Okay, yeah, that's not a good piece of data. But=20
> let's assume that part were okay. Any other problems? Besides that?"=20
> 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 on=
e be one.

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


Daniel

--=20
Retrovirology Laboratory Luxembourg
Centre Hospitalier de Luxembourg
4, rue E. Barbl=E9
L-1210 Luxembourg

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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: Reducing duplicated business rules

am 20.11.2003 20:36:00 von Tom Hebbron

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

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

Re: Reducing duplicated business rules

--=-G51eToX8oYDcg3zIKE4Y
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

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
> 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)

--=-G51eToX8oYDcg3zIKE4Y
Content-Disposition: attachment; filename=pa_discounts.xml
Content-Type: text/xml; name=pa_discounts.xml; charset=us-ascii
Content-Transfer-Encoding: quoted-printable



discount_id


discount_id

Discount ID

serial_type
4
false


8











display_html_text_form
admin_text_form
true




discount_name

Discount Name

varchar_type
200
true


50


200








display_html_text_form
admin_text_form
false




discount_code

Discount Code

varchar_type
32
false


32











display_html_text_form
admin_text_form





discount_expiration

Expiration Date

calendar_type

false














display_html_calendar_form
admin_text_form





discount_amount
Discount Amount

double_type

false


5











display_html_text_form
admin_text_form





last_modified

Last Modified

timestamp_type

false














display_html_date_form
admin_text_form
true
_POSTGRES.CURRENT_TIMESTAMP



last_modifying_user

Last Modifying User

int_type
4
false














display_html_text_form
admin_text_form
true
_SESSION.user_id



modified_ip_address

Modified IP Address

varchar_type
20
false














display_html_text_form
admin_text_form
true
_SERVER.REMOTE_ADDR



discount_minimum

Discount Mininum

double_type

false


5











display_html_text_form
admin_text_form





discount_type

Discount Type

varchar_type
10
true
















display_html_select_form
admin_text_form








discount_id
discount_name
discount_type
discount_amount


pa_discounts





lower(discount_name)







=09


--=-G51eToX8oYDcg3zIKE4Y
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0


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

--=-G51eToX8oYDcg3zIKE4Y--