Re: My honours project - databases using dynamically attached entity-properties

On Mar 15, 2007, at 11:31 , Ron Mayer wrote:

> Josh Berkus wrote:
>>> And then what? dynamically construct all your SQL queries?
>>> Sure, sounds like a simple solution to me...
>>
>> Not to mention DB security issues. How do you secure your
>> database when
>> your web client has DDL access?
>>
>> So, Edward, the really *interesting* idea would be to come up with a
>> secure, normalized way to do UDFs *without* EAV tables. People
>> would be
>> very impressed.
>>
>
> I have a system with many essentially user-defined fields, and was
> thinking of creating something similar to an Array type and writing
> some GIST indexes for it.
>
> My current workaround is to store them as a YAML document and use
> tsearch to index it (with application logic to further refine the
> results) - but a EAV datatype that could be put in tables and
> effectively indexed would be of quite a bit of interest here.
> And yes, a better say to do UDFs would be even cooler.

Out of all the databases that I have used, postgresql offers the most
flexible DDL- mostly for one reason: they can operate within
transactions.

To handle arbitrary strings as column identifiers, the column names
could actually be stripped down to lower-case letters and the "real
title" could be stored in a separate table or as column comments.

Mr. Berkus' concern regarding the security implications is already
handled by privilege separation or security-definer functions.

The OP's concern about the difficulty about querying a schema
structure is alleviated via any number of APIs in Perl, JDBC, etc.

It seems to me that postgresql is especially well-suited to run DDL
at runtime, so what's the issue?

-M