[postmodern-devel] Dash in Postgresql Column name - "query-dao"

[Phil]

Hi Marijn

The only reason I wanted to use the dashes is that it would have made my
life slightly easier. I have a semi-generic program that takes a list of
"descriptive names", "column names" and table and then produces a grid +
detail widget in html. I was just trying to keep all the code that it
generated lispy looking with the least amount of work. Usually I would
not use the underscore in my DB syntax, just because all the qouting in
stored procs/queries becomes a pain. This db will only be accessed via
lisp so I decided it was worth the break from convention.

For the future it might be worth it thow. 

Thanx for all the help.

Regards Phil


On Wed, 2008-12-03 at 19:05 +0100, Marijn Haverbeke wrote:
> Hello Phil,
> 
> Postmodern works this way because I wasn't even aware that one could
> use column names like that -- though actually, seeing the way quotes
> work, it's rather obvious. Currently the assumption 'dashes have to be
> converted to underscores' is present all through the system. Is
> support for such column names very important for you? My first
> recommendation would be to just rename the thing to use an
> underscore... but if you have a good reason to use a dash, I guess
> we'll have to look into another solution.
> 
> Best,
> Marijn
> 
> 
> On Wed, Dec 3, 2008 at 5:59 PM, Phil <zaries at global.co.za> wrote:
> > On Wed, 2008-12-03 at 17:16 +0100, Maciej Pasternacki wrote:
> >> On 2008-12-03, at 16:28, Phil wrote:
> >>
> >> >>> query-dao does not seem to like column names with a dash in them
> >> >>> like
> >> >>> column-name and I suspect it would have the same problem with
> >> >>> spaces.
> >> >>>
> >> >>> I know that the preffered for postgresql would be column_name but I
> >> >>> though that being able to use the dash would simplify going from
> >> >>> db to
> >> >>> dao object to html and back, well at least for me.
> >> >>>
> >> >>> Is column names with dashes just not supported, a bug or on the wish
> >> >>> list?
> >> >>
> >> >> Everything is supported with :RAW sql-op:  (select-dao 'foo (:= (:raw
> >> >> "\"foo-bar\"") 23)) calls query (SELECT * FROM foo WHERE ("foo-bar" =
> >> >> 23)) (SELECT-DAO is a shortcut for QUERY-DAO, where you supply only
> >> >> the WHERE clause).  There doesn't seem to exist any other way now,
> >> >> but
> >> >> it may be possible to add a bit of macro sugar.
> >> >
> >> > Ok maybe I should have been a bit more precise in my description. I
> >> > was
> >> > not using the columns names in the query.
> >> >
> >> > Here follows a quick repeatable test:
> >> >
> >> > CREATE TABLE test
> >> > (
> >> >  id integer NOT NULL,
> >> >  "some" character varying NOT NULL,
> >> >  "test-dash" character varying
> >> > )
> >> > WITH (OIDS=FALSE);
> >> > ALTER TABLE test OWNER TO postgres;
> >> >
> >> > insert into test (id,"some","test-dash") values(1, 'some','dash-1');
> >> >
> >> > (defclass test ()
> >> >  ((id :col-type string :initarg :id
> >> >   :accessor biographical-id)
> >> >   (some :col-type  (or db-null string) :initarg :some
> >> >     :accessor biographical-some)
> >> >   (test-dash :col-type  (or db-null string) :initarg :test-dash
> >> >    :accessor biographical-test-dash))
> >> >  (:metaclass postmodern:dao-class)
> >> >  (:keys id))
> >> >
> >> > (postmodern:query-dao 'test (:select '* :from 'test))
> >> >
> >> > That last bit produces the following error:
> >> >
> >> > No slot named test-dash in class TEST. DAO out of sync with table, or
> >> > incorrect query used.
> >> >   [Condition of type SIMPLE-ERROR]
> >>
> >>
> >> I see.  It was SELECT-DAO that made me think there's problem in the
> >> query.  What you write about is not supported at the moment, and while
> >> it's easy to add this, it's not trivial.
> >>
> >> I just looked into postmodern/table.lisp and I see that in class
> >> DIRECT-COLUMN-SLOT there is a slot SQL-NAME.  It would seem trivial to
> >> add an initarg, e.g. :COL-SQL-NAME, for this slot, and modify SHARED-
> >> INITIALIZE of DIRECT-COLUMN-SLOT to set this slot only if it's not
> >> already bound by an initarg.  This way it would be possible to
> >> add :COL-SQL-NAME initarg to the slot to explicitly set column name,
> >> with upside being it wouldn't need to be a symbol.
> >>
> >> However, this slot seems to be rarely used, and there are many cases
> >> where simple TO-SQL-NAME is called on slot name, which would need to
> >> be fixed.  Also, column name would need to be properly double-quoted
> >> everywhere.
> >>
> >> Your problem seems interesting and it looks like it would be useful to
> >> support your case.  If all that was needed would be adding the initarg
> >> to :COL-SQL-NAME, the patch would be probably already attached to this
> >> post ;) but the way it is, I can only give these starting points so
> >> that you can fix it yourself.  It shouldn't be hard, but testing the
> >> patch may be a bit time-consuming.
> >>
> >> Regards,
> >> Maciej.
> >>
> >
> > Maciej thanx for the reply. Unfortunatley what I know about LISP is
> > dangerous and what I know about CLOS is non existant (think I read to
> > many of Paul Grahams articles, not a fan of objects...lol). I had to
> > re-read your explanation a couple of times just to get the jist of
> > it...lol
> >
> > I would be more than willing to do the grunt work if it would be
> > something that could be incorperated into postmodern, but I would need a
> > lot more guidence. I dont want to go hacking postmodern for myself (with
> > bad lisp style) and then end up with a version that is broken if and
> > when postmodern is updated.
> >
> > Regards
> > Phil
> >
> >
> >
> >
> > _______________________________________________
> > postmodern-devel mailing list
> > postmodern-devel at common-lisp.net
> > http://common-lisp.net/cgi-bin/mailman/listinfo/postmodern-devel
> >
> 
> _______________________________________________
> postmodern-devel mailing list
> postmodern-devel at common-lisp.net
> http://common-lisp.net/cgi-bin/mailman/listinfo/postmodern-devel