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

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Fri, Mar 16, 2007 at 09:56:23AM -0400, Andrew Dunstan wrote: > [EMAIL PROTECTED] wrote: > > > >>Does hstore nest? [...] > > > >If what you mean is to have "mappings of mappings" then no. > > [...] think "hash" for perl folks [...] > As a perl folk

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

[EMAIL PROTECTED] wrote: Does hstore nest? My impression is that it doesn't. Which might well not matter, of course. If what you mean is to have "mappings of mappings" then no. Hstore implements a data type for a (finite) mapping (a set of key -> value pairs, think "hash" for perl folks

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

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Thu, Mar 15, 2007 at 12:49:10PM -0400, Andrew Dunstan wrote: > Gregory Stark wrote: > >"Ron Mayer" <[EMAIL PROTECTED]> writes: > > > > > >>I have a system with many essentially user-defined fields, and was > >>thinking of creating something simila

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

Gregory Stark wrote: "Ron Mayer" <[EMAIL PROTECTED]> writes: 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 tse

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

On Thu, 2007-03-15 at 17:01, A.M. wrote: > It seems to me that postgresql is especially well-suited to run DDL > at runtime, so what's the issue? The issue is that some applications are not well suited to run DDL at runtime :-) As I already mentioned in another post in this thread, our applicat

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

"Ron Mayer" <[EMAIL PROTECTED]> writes: > 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

Re: [HACKERS] 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

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

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Thu, Mar 15, 2007 at 08:31:24AM -0700, 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 se

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

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 t

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

On Wednesday 2007-03-14 08:26, Csaba Nagy wrote: > On Wed, 2007-03-14 at 16:08, [EMAIL PROTECTED] wrote: > > On Wed, Mar 14, 2007 at 02:28:03PM +, Gregory Stark wrote: > > > "David Fetter" <[EMAIL PROTECTED]> writes: > > > > CREATE TABLE symptom ( > > > > symptom_id SERIAL PRIMARY KEY, /* S

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

David Fetter wrote: On Wed, Mar 14, 2007 at 12:07:45PM +1300, Edward Stanley wrote: On Wed, 14 Mar 2007, David Fetter wrote: On Tue, Mar 13, 2007 at 05:54:34PM +, Richard Huxton wrote: David Fetter wrote: On Tue, Mar 13, 2007 at 02:21:37PM +, Richard Huxton wro

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

On Wed, 14 Mar 2007, David Fetter wrote: > On Tue, Mar 13, 2007 at 05:54:34PM +, Richard Huxton wrote: > > David Fetter wrote: > > >On Tue, Mar 13, 2007 at 02:21:37PM +, Richard Huxton wrote: > > >>David Fetter wrote: > > >>>On Tue, Mar 13, 2007 at 09:31:45AM +, Richard Huxton wrote: >

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

On Wed, 2007-03-14 at 16:50, David Fetter wrote: > On Wed, Mar 14, 2007 at 02:28:03PM +, Gregory Stark wrote: > > "David Fetter" <[EMAIL PROTECTED]> writes: > > > > > CREATE TABLE symptom ( > > > symptom_id SERIAL PRIMARY KEY, /* See above. */ > > > ... > > > ); > > > > > > CREATE TABL

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

David Fetter wrote: On Wed, Mar 14, 2007 at 02:28:03PM +, Gregory Stark wrote: "David Fetter" <[EMAIL PROTECTED]> writes: CREATE TABLE symptom ( symptom_id SERIAL PRIMARY KEY, /* See above. */ ... ); CREATE TABLE patient_presents_with ( patient_id INTEGER NOT NULL REFERENCES p

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

On Wed, Mar 14, 2007 at 02:28:03PM +, Gregory Stark wrote: > "David Fetter" <[EMAIL PROTECTED]> writes: > > > CREATE TABLE symptom ( > > symptom_id SERIAL PRIMARY KEY, /* See above. */ > > ... > > ); > > > > CREATE TABLE patient_presents_with ( > > patient_id INTEGER NOT NULL REFER

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

<[EMAIL PROTECTED]> writes: > On Wed, Mar 14, 2007 at 03:25:48PM +, Gregory Stark wrote: >> <[EMAIL PROTECTED]> writes: >> > On Wed, Mar 14, 2007 at 02:28:03PM +, Gregory Stark wrote: >> >> "David Fetter" <[EMAIL PROTECTED]> writes: >> >> > CREATE TABLE symptom ( >> >> > symptom_id SER

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

On Wed, Mar 14, 2007 at 03:25:48PM +, Gregory Stark wrote: > <[EMAIL PROTECTED]> writes: > > On Wed, Mar 14, 2007 at 02:28:03PM +, Gregory Stark wrote: > >> "David Fetter" <[EMAIL PROTECTED]> writes: > >> > CREATE TABLE symptom ( > >> > symptom_id SERIAL PRIMARY KEY, /* See above. */ >

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

Joshua D. Drake wrote: Gregory Stark wrote: "David Fetter" <[EMAIL PROTECTED]> writes: CREATE TABLE symptom ( symptom_id SERIAL PRIMARY KEY, /* See above. */ ... ); CREATE TABLE patient_presents_with ( patient_id INTEGER NOT NULL REFERENCES patient(patient_id), symptom

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

On Wed, 2007-03-14 at 16:08, [EMAIL PROTECTED] wrote: > On Wed, Mar 14, 2007 at 02:28:03PM +, Gregory Stark wrote: > > "David Fetter" <[EMAIL PROTECTED]> writes: > > > CREATE TABLE symptom ( > > > symptom_id SERIAL PRIMARY KEY, /* See above. */ > > > ... > > > ); > > > > > > CREATE TABL

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

<[EMAIL PROTECTED]> writes: > On Wed, Mar 14, 2007 at 02:28:03PM +, Gregory Stark wrote: >> "David Fetter" <[EMAIL PROTECTED]> writes: >> > CREATE TABLE symptom ( >> > symptom_id SERIAL PRIMARY KEY, /* See above. */ >> > ... >> > ); >> > >> > CREATE TABLE patient_presents_with ( >> >

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

Gregory Stark wrote: > "David Fetter" <[EMAIL PROTECTED]> writes: > >> CREATE TABLE symptom ( >> symptom_id SERIAL PRIMARY KEY, /* See above. */ >> ... >> ); >> >> CREATE TABLE patient_presents_with ( >> patient_id INTEGER NOT NULL REFERENCES patient(patient_id), >> symptom_id INTE

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

On Wed, Mar 14, 2007 at 02:28:03PM +, Gregory Stark wrote: > "David Fetter" <[EMAIL PROTECTED]> writes: > > CREATE TABLE symptom ( > > symptom_id SERIAL PRIMARY KEY, /* See above. */ > > ... > > ); > > > > CREATE TABLE patient_presents_with ( > > patient_id INTEGER NOT NULL REFERENC

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

"David Fetter" <[EMAIL PROTECTED]> writes: > CREATE TABLE symptom ( > symptom_id SERIAL PRIMARY KEY, /* See above. */ > ... > ); > > CREATE TABLE patient_presents_with ( > patient_id INTEGER NOT NULL REFERENCES patient(patient_id), > symptom_id INTEGER NOT NULL REFERENCES symptom(s

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

On Wed, Mar 14, 2007 at 12:07:45PM +1300, Edward Stanley wrote: > On Wed, 14 Mar 2007, David Fetter wrote: > > On Tue, Mar 13, 2007 at 05:54:34PM +, Richard Huxton wrote: > > > David Fetter wrote: > > > >On Tue, Mar 13, 2007 at 02:21:37PM +, Richard Huxton wrote: > > > >>David Fetter wrote:

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

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tue, Mar 13, 2007 at 05:39:05PM +0300, Teodor Sigaev wrote: > Hmm, hstore + (optionally) functional indexes. Is it answer? I have used it in a (yet) test system. It works surprisingly well. Thanks - -- tomás -BEGIN PGP SIGNATURE- Version:

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

On Tue, Mar 13, 2007 at 05:54:34PM +, Richard Huxton wrote: > David Fetter wrote: > >On Tue, Mar 13, 2007 at 02:21:37PM +, Richard Huxton wrote: > >>David Fetter wrote: > >>>On Tue, Mar 13, 2007 at 09:31:45AM +, Richard Huxton wrote: > * Another good example is the "questionnaire".

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

David Fetter wrote: On Tue, Mar 13, 2007 at 02:21:37PM +, Richard Huxton wrote: David Fetter wrote: On Tue, Mar 13, 2007 at 09:31:45AM +, Richard Huxton wrote: * Another good example is the "questionnaire". With all due respect, this is a solved problem *without EAV or run-time DDL*.

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

On Tue, Mar 13, 2007 at 02:21:37PM +, Richard Huxton wrote: > David Fetter wrote: > >On Tue, Mar 13, 2007 at 09:31:45AM +, Richard Huxton wrote: > >>* Another good example is the "questionnaire". > > > >With all due respect, this is a solved problem *without EAV or > >run-time DDL*. The UR

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

Hmm, hstore + (optionally) functional indexes. Is it answer? Edward Stanley wrote: Hi, Was wondering if people would mind having a read over what I plan to do for my undergraduate honours project - you can get the proposal here: http://www.mcs.vuw.ac.nz/~eddie/489_Proposal.pdf What I'd bas

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

David Fetter wrote: I think the main discussion has been around: 1. Whether Edward's final-year project is basically EAV (in which case he'll probably need to work hard to get good marks). As Josh mentioned, I'm on of the people who says EAV is never justified. We do have a way of expres

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

David Fetter wrote: On Tue, Mar 13, 2007 at 09:31:45AM +, Richard Huxton wrote: * Another good example is the "questionnaire". With all due respect, this is a solved problem *without EAV or run-time DDL*. The URL below has one excellent approach to this.

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

On Tue, Mar 13, 2007 at 09:31:45AM +, Richard Huxton wrote: > Sean Utt wrote: > >And then what? Make the search box on www.postgresql.org able to > >handle an email address as search text without throwing a shoe? > > > >Search for [EMAIL PROTECTED] or any other 'email' address from the > >postg

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

Sean Utt wrote: And then what? Make the search box on www.postgresql.org able to handle an email address as search text without throwing a shoe? Search for [EMAIL PROTECTED] or any other 'email' address from the postgres home page. Barfage every time. Easy for some isn't easy for all, apparent

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

On Tue, 2007-03-13 at 00:43, Richard Huxton wrote: > Josh Berkus wrote: > > I really don't see any way you could implement UDFs other than EAV that > > wouldn't be immensely awkward, or result in executing DDL at runtime. > > What's so horrible about DDL at runtime? Obviously, you're only going t

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

On Mon, Mar 12, 2007 at 10:05:58PM -0700, Sean Utt wrote: > And then what? Make the search box on www.postgresql.org able to handle an > email address as search text without throwing a shoe? > > Search for [EMAIL PROTECTED] or any other 'email' address from the > postgres home page. Barfage ever

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

n email address? Sarcastically yours, Sean - Original Message - From: "Richard Huxton" To: "Gregory Stark" <[EMAIL PROTECTED]> Cc: "Andrew Hammond" <[EMAIL PROTECTED]>; ; Sent: Monday, March 12, 2007 7:30 PM Subject: Re: [HACKERS] My hon

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

Gregory Stark wrote: "Richard Huxton" writes: Well the cost depends on where/how complex the extra fields are. If you're just talking about adding columns usercol01..NN with different types and possibly a lookup to a single client_attributes table, it's not difficult. And then what? dynamica

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

> 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, norm

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

"Richard Huxton" writes: > Well the cost depends on where/how complex the extra fields are. If you're > just > talking about adding columns usercol01..NN with different types and possibly a > lookup to a single client_attributes table, it's not difficult. And then what? dynamically construct al

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

Andrew Hammond wrote: On 3/12/07, Richard Huxton wrote: Josh Berkus wrote: > I really don't see any way you could implement UDFs other than EAV that > wouldn't be immensely awkward, or result in executing DDL at runtime. What's so horrible about DDL at runtime? Obviously, you're only going to

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

On 3/12/07, Richard Huxton wrote: Josh Berkus wrote: > I really don't see any way you could implement UDFs other than EAV that > wouldn't be immensely awkward, or result in executing DDL at runtime. What's so horrible about DDL at runtime? Obviously, you're only going to allow specific addition

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

Josh Berkus wrote: I really don't see any way you could implement UDFs other than EAV that wouldn't be immensely awkward, or result in executing DDL at runtime. What's so horrible about DDL at runtime? Obviously, you're only going to allow specific additions to specific schemas/tables, but why

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

Josh Berkus wrote: > Amdrew, > >> I have yet to encounter one, but >> Josh has more experience, and more varied experience than I do. To me, >> EAV is a perfect example of ignoring the YAGNI principal. > > I've done plenty of applications where part of the specification for the > application was

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

Amdrew, > I have yet to encounter one, but > Josh has more experience, and more varied experience than I do. To me, > EAV is a perfect example of ignoring the YAGNI principal. I've done plenty of applications where part of the specification for the application was "User Defined Fields" allowing

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

On Mar 11, 12:47 pm, [EMAIL PROTECTED] (Josh Berkus) wrote: > No matter how much Heikki hates them, I think he'd agree that EAV tables are > better than having the application execute DDL at runtime. EAV moves the structure that is typically in the design of the tables into the contents of the tab

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

Edward, Heikki, > In my experience, EAV > schemas are usually result of improper database design by someone not > understanding the relational theory and the principles of normalization. Edward, you should be aware that EAV schema are a source of disagreement among database designers. Some deve

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

Edward Stanley wrote: Was wondering if people would mind having a read over what I plan to do for my undergraduate honours project - you can get the proposal here: http://www.mcs.vuw.ac.nz/~eddie/489_Proposal.pdf ISTM that what you're calling "dynamically attached properties" are generally c