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

Chris Browne wrote:
> jnasby@pervasive.com ("Jim C. Nasby") writes:
>
>>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 don't much like that; that adds a layer that can fall out of date,
> and offers NOTHING in terms of tools to get things resynchronized.
>
> What would be the "nice to have" thing is some system that allows you
> to extract a set of field validation functions from the database.
>
> In effect, you do:
>
> select a.attname, field_validation_function(c.oid, a.attname, 'Perl')
> from pg_class c, pg_attribute a where a.attrelid = c.oid and c.relname
> = 'my_table');
>
> which returns a list of Perl 'validation functions', one for each
> attribute.
>
> Each validation function would take data passed in, validate it
> against some Perl-encoded form of the DBMS constraints, and either:
>
>  a) Return 0/NULL, indicating that all is OK, or
>
>  b) Return a matrix of error messages indicating the failures
>
> It would be neat (would it not? :-)) to allow passing in other names
> of languages, e.g. where scripting_language in ('Perl', 'Python',
> 'Ruby', 'Tcl', 'PHP', 'JavaScript'), where the result would be a set
> of functions in those respective languages.

Yes! That's exactly the kind of concept I was looking for.

>
> It would probably be ideal for what is to be returned to be, in each
> case, the local equivalent to a lambda function so that it could be
> transparently embedded inside the user's favorite application
> framework rather than forcing some Procrustean naming convention on
> them.

AFAIK each of these has some analogue to the lambda function--even PHP
with create_function(): www.php.net/create_function.

Another 'nice to have' would be a way to provide methods to extract a
value from each column datatype into a suitable variable in the
scripting language. That would be very nice for date columns, arrays, etc...

>
> If the result was some hunk of XML that could be automatically
> transformed into suitable lambda functions, that's OK too, although
> there is the demerit that it FORCES a further rewriting process.
>
> I'm not sure what to do about multicolumn constraints, but that's
> probably troublesome even in theory :-).