Skip site navigation (1) Skip section navigation (2)

Reducing duplicated business rules

From: Michael Glaesemann <grzm(at)myrealbox(dot)com>
To: pgsql-php(at)postgresql(dot)org
Subject: Reducing duplicated business rules
Date: 2003-11-04 16:50:46
Message-ID: 096944BE-0EE7-11D8-B410-0005029FC1A7@myrealbox.com (view raw or flat)
Thread:
Lists: pgsql-php
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


Responses

pgsql-php by date

Next:From: Kelvin VarstDate: 2003-11-04 17:26:32
Subject: Dead sequence
Previous:From: ljbDate: 2003-11-02 01:30:34
Subject: Re: Support for prepared queries

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group