Re: Thoughs after discussions at OSCON - Mailing list pgsql-advocacy

On Tue, Aug 16, 2005 at 10:15:38AM -0400, Rick Morris wrote:
> John DeSoi wrote:
> >I don't disagree but after doing quite a bit of PHP the last few  weeks
> >(using Drupal) I see more clearly why most people don't go to  the
> >trouble. I can create all kinds of constraints in my database but  when
> >I go to save a row that might violate several of them, I'll only  get
> >one error back. This won't work in a web form interface where I  should
> >provide feedback on all of the errors at once rather than one  at a
> >time. So if I want this validation logic to be available at both  the
> >application and database level, I have to somehow parse it from  the
> >database or create some superset of the specification that will  work in
> >the application and create the constraints in the database.  Otherwise,
> >I need to maintain the constraints in both places and keep  them in sync.
>
> IMHO, this is exactly where there needs to be more work done on
> application frameworks: automated ways to propagate constraints and
> business logic back into the application layer.
>
> I explored those concepts to a small extent (with code examples) in a
> couple articles for PHP|Architect. I think it is an area that would
> involve some serious work, but would bring some serious benefits.

There's at least 3 ways this can happen. You can define the
logic/constraints in the application and push them to the database, you
can define them in the database and push them to the application, or you
can use a seperate framework to drive both.

Personally, I'm in favor of #2, because it means you should be able to
have any application use the constraints in the database.

I think this is something that could possibly be added to PostgreSQL via
a pgfoundry project. My initial thought is to provide a means to
associate certain constraints/triggers with 'human readable' conditions.
So for example, in a table that has username, you could link the unique
constraint on username to a message that says 'That username is already
in use.' Unfortunately this still doesn't allow for checking multiple
constraints at once in the database, and uniqueness can really only be
checked by the database at insert/update time. But other constraints
could be checked ahead of time.

Another possibility is improving on the existing frameworks. Personally,
I'm not terribly impressed with the frameworks I've looked at because
they seem to divorce themselves from the database too much. They
generally put a much greater load on the database because they want to
do as much as possible in the application. For example, if you mark a
field as being unique, many of them will do a select before trying to
insert or update to see if a record already exists. Now you've got the
database running 2 queries instead of 1.

So far, the best solution I've seen is the XML-based 'datadef' that a
friend of mine created at a former job. It was database-centric enough
so that it could drive the entire database creation process (including
triggers, stored procs, etc) and do it well, while also creating
application code (C/C++ in this case). Since it was XML/XSLT based it
was pretty easy to add new features, and more importantly, it could
support any programming language out there. Everything else I've seen is
tied to a specific language, which is a big downside. Unfortunately, he
never wanted to release it to the community because he felt the
implementation was ugly.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software        http://pervasive.com        512-569-9461