[postmodern-devel] Dash in Postgresql Column name - "query-dao"
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? Thanx
On 2008-12-03, at 15:46, 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. Marijn, have you thought of exporting S-SQL::DEF-SQL-OP, so that users can create their own "S-SQL macros"? This may come useful in some cases, like above, or to create reusable parametrized query fragments. With complex queries, macros may come in handy. Regards, Maciej -- Maciej Pasternacki -><- http://www.pasternacki.net/ -><- http://www.3ofcoins.net/
On Wed, 2008-12-03 at 16:08 +0100, Maciej Pasternacki wrote:
On 2008-12-03, at 15:46, 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.
Marijn, have you thought of exporting S-SQL::DEF-SQL-OP, so that users can create their own "S-SQL macros"? This may come useful in some cases, like above, or to create reusable parametrized query fragments. With complex queries, macros may come in handy.
Regards, Maciej
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]
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 Pasternacki -><- http://www.pasternacki.net/ -><- http://www.3ofcoins.net/
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
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@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@common-lisp.net http://common-lisp.net/cgi-bin/mailman/listinfo/postmodern-devel
On Wed, Dec 03, 2008 at 07:05:52PM +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.
I can't comment on dashes specifically, but "foo" in SQL is like |foo| in Lisp. It's a sometimes-useful escape hatch to put all kinds of junk in table names, column names, etc. I can't say it's a *good* idea to make a table or column named "Hello, World %-)" but the double-quotes make it possible. Zach
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@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@common-lisp.net http://common-lisp.net/cgi-bin/mailman/listinfo/postmodern-devel
_______________________________________________ postmodern-devel mailing list postmodern-devel@common-lisp.net http://common-lisp.net/cgi-bin/mailman/listinfo/postmodern-devel
participants (4)
-
Maciej Pasternacki -
Marijn Haverbeke -
Phil -
Zach Beane