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

Jim C. Nasby wrote:
> 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.

Absolutely. That's what I was getting at.
>
> 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.

One of my ideas involves creating a class for each base datatype, with
the possibility for creating additional classes for domains and custom
datatypes. Then it becomes easier for the application framework to sort
out what types of constraints can apply to a given column. So far, I
have only toyed around with this in PHP a little, but I would be happy
to share this work.

Still, the hard work is in parsing constraint definitions. The
information_schema tables/views make this information more accessible,
but still, there is a certain amount of crazy reverse-engineering one
needs to do. It would be nice eventually for the pgsql modules of any
language to be able to derive this information, for example, starting
with the pg_meta_data() function in PHP (http://php.net/pg_meta_data).

>
> 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.
>
Unfortunately.

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

I know the feeling ;).

That approach has merit, but one possible drawback: the developers/DBAs
might circumvent the datadef and make database design changes directly
on the DB. Then your application is hosed. I much prefer to develop
something that allows the application layer to react automatically to
changes in the database design. (I know that is never *completely*
possible, but at least in the area of basic constraints and datatypes it
would be nice)

Regards,

Rick Morris