[postmodern-devel] Using postgresql's default option for column values
Hi, new postmodern user here. I'd like to be able to use postgresql's 'default' column value feature instead of an :initform in deftable. Is that possible? I haven't had any luck getting it to work. I have a couple of reasons for wanting to do this, first I have several tables that get their primary keys from a single postgresql sequence, e.g.: create table fooA ( id int8 default nextval( 'id_source' ) primary key, ... ) create table fooB ( id int8 default nextval( 'id_source' ) primary key, ... ) Additionally, other table columns use the 'default' option to create creation time stamps and so forth. I can't remove these options from the table definitions because that would break the other applications that use the database. So, what I'm really after is the ability to define fields with deftable so that those fields are not initialized by (make-instance) but read into the dao after creation, similarly to how auto-id appears to work. I looked through the archives but didn't see anything that looked related to this. thanks, Peter
Hello Peter, Your problem sounds entirely valid... however, I do not see a straightforward way to make the DAO classes support this in a particularly elegant way. In some cases, you can just give the fields an :initform like (query (:select (:nextval 'my-sequence))). This has the disadvantage of generating extra queries whenever you create a dao object. If you think it would be useful, I can add some exception to the save-dao code to make sure it does not try to save slots that are unbound -- that way you could just not initialise the fields that have default values, and wait until insert time for them to get a value. Of course, if you need access to these values before saving the record, this will not help. Let me know what you think. Marijn On Feb 10, 2008 7:28 PM, Peter Eddy <peter.eddy@gmail.com> wrote:
Hi, new postmodern user here.
I'd like to be able to use postgresql's 'default' column value feature instead of an :initform in deftable. Is that possible? I haven't had any luck getting it to work.
I have a couple of reasons for wanting to do this, first I have several tables that get their primary keys from a single postgresql sequence, e.g.:
create table fooA ( id int8 default nextval( 'id_source' ) primary key, ... )
create table fooB ( id int8 default nextval( 'id_source' ) primary key, ... )
Additionally, other table columns use the 'default' option to create creation time stamps and so forth.
I can't remove these options from the table definitions because that would break the other applications that use the database.
So, what I'm really after is the ability to define fields with deftable so that those fields are not initialized by (make-instance) but read into the dao after creation, similarly to how auto-id appears to work.
I looked through the archives but didn't see anything that looked related to this.
thanks, Peter _______________________________________________ postmodern-devel mailing list postmodern-devel@common-lisp.net http://common-lisp.net/cgi-bin/mailman/listinfo/postmodern-devel
Hi Marijn, I think an option to not try to save unbound slots would solve my problem perfectly. In the mean time I'll use your :initform query suggestion. We have a very 'involved' DBA on this project and I'm not sure that this double-querying will be acceptable to her in the long run, but it'll certainly be fine for the development DB. So, no great urgency. Thanks very much! - Peter On Feb 10, 2008 2:04 PM, Marijn Haverbeke <marijnh@gmail.com> wrote:
particularly elegant way. In some cases, you can just give the fields an :initform like (query (:select (:nextval 'my-sequence))). This has the disadvantage of generating extra queries whenever you create a dao object. If you think it would be useful, I can add some exception to the save-dao code to make sure it does not try to save slots that are unbound -- that way you could just not initialise the fields that have default values, and wait until insert time for them to get a value. Of course, if you need access to these values before saving the record, this will not help.
Hey Peter, Attached is a patch that will make it possible to pass a (:skip-unbound t) option to defclass, which will make the insert-dao method for that class check for unbound slots when it inserts a new record. You'll have to pull the repository and apply it with 'darcs apply' if you want to use it. I'm rather doubting whether I want to include it in the main repository -- defclass is already an extravaganza of crazy macroism as it is, and this change is just a piece of duct tape around the general issues that it has. I'm hoping to simplify and generalise some of its functionality with a metaclass and some MOP magic, but I haven't gotten around to that yet. So consider this patch a temporary solution. Drew's trick also looks nice, only it introduces even more queries at object-creation time. I will probably end up adding :sql-default slot options to defclass, which act like :initforms, but contain s-sql expressions instead. That way, table creation can add the proper defaults to the fields, and the object initializer can pull in all the default values in one go. Regards, Marijn On Feb 11, 2008 12:47 AM, Peter Eddy <peter.eddy@gmail.com> wrote:
Hi Marijn,
I think an option to not try to save unbound slots would solve my problem perfectly. In the mean time I'll use your :initform query suggestion. We have a very 'involved' DBA on this project and I'm not sure that this double-querying will be acceptable to her in the long run, but it'll certainly be fine for the development DB. So, no great urgency.
Thanks very much!
- Peter
On Feb 10, 2008 2:04 PM, Marijn Haverbeke <marijnh@gmail.com> wrote:
particularly elegant way. In some cases, you can just give the fields an :initform like (query (:select (:nextval 'my-sequence))). This has the disadvantage of generating extra queries whenever you create a dao object. If you think it would be useful, I can add some exception to the save-dao code to make sure it does not try to save slots that are unbound -- that way you could just not initialise the fields that have default values, and wait until insert time for them to get a value. Of course, if you need access to these values before saving the record, this will not help.
_______________________________________________ postmodern-devel mailing list postmodern-devel@common-lisp.net http://common-lisp.net/cgi-bin/mailman/listinfo/postmodern-devel
Thank you Marijn and Drew, very much appreciated! 2008/2/11 Marijn Haverbeke <marijnh@gmail.com>:
Hey Peter,
Attached is a patch that will make it possible to pass a (:skip-unbound t) option to defclass, which will make the insert-dao method for that class check for unbound slots when it inserts a new record. You'll have to pull the repository and apply it with 'darcs apply' if you want to use it. I'm rather doubting whether I want to include it in the main repository -- defclass is already an extravaganza of crazy macroism as it is, and this change is just a piece of duct tape around the general issues that it has. I'm hoping to simplify and generalise some of its functionality with a metaclass and some MOP magic, but I haven't gotten around to that yet.
So consider this patch a temporary solution. Drew's trick also looks nice, only it introduces even more queries at object-creation time. I will probably end up adding :sql-default slot options to defclass, which act like :initforms, but contain s-sql expressions instead. That way, table creation can add the proper defaults to the fields, and the object initializer can pull in all the default values in one go.
Regards, Marijn
On Feb 11, 2008 12:47 AM, Peter Eddy <peter.eddy@gmail.com> wrote:
Hi Marijn,
I think an option to not try to save unbound slots would solve my problem perfectly. In the mean time I'll use your :initform query suggestion. We have a very 'involved' DBA on this project and I'm not sure that this double-querying will be acceptable to her in the long run, but it'll certainly be fine for the development DB. So, no great urgency.
Thanks very much!
- Peter
On Feb 10, 2008 2:04 PM, Marijn Haverbeke <marijnh@gmail.com> wrote:
particularly elegant way. In some cases, you can just give the fields an :initform like (query (:select (:nextval 'my-sequence))). This has the disadvantage of generating extra queries whenever you create a dao object. If you think it would be useful, I can add some exception to the save-dao code to make sure it does not try to save slots that are unbound -- that way you could just not initialise the fields that have default values, and wait until insert time for them to get a value. Of course, if you need access to these values before saving the record, this will not help.
_______________________________________________ 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
On 10/02/2008, Peter Eddy <peter.eddy@gmail.com> wrote:
Hi, new postmodern user here.
I'd like to be able to use postgresql's 'default' column value feature instead of an :initform in deftable. Is that possible? I haven't had any luck getting it to work.
I use a trick to do this in some older software. It would be quite trivial to make in work in postmodern. Basically, the idea is to get the actual default value expression from the system tables and run a SELECT on it. This code is postrgresql, but it should be trivial to modify for postmodern. (defun set-default-values (model) (labels ((sym->sql (sym) (string-downcase (substitute #\_ #\- (string sym)))) (get-def (slot) (caar (query (format nil " SELECT DISTINCT adsrc FROM pg_attrdef join pg_attribute on attnum = adnum WHERE adrelid = (select oid from pg_class where relname = '~A') AND attname = '~A'" (sym->sql (class-name (class-of model))) (sym->sql slot))))) (get-default-value (slot) (let ((def (get-def slot))) (if def (caar (query (format nil "SELECT ~A" def))))))) (dolist (slot (list-slots model)) (when (and (primary-key-p model slot) (or (not (slot-boundp model slot)) (equal (slot-value model slot) nil))) (setf (slot-value model slot) (get-default-value slot)) (when (and (primary-key-p model slot) (not (slot-value model slot)) (error "No default value for primary key : ~A" slot)))))) model) have used this without problems for over 4 years. Cheers, drewc
I have a couple of reasons for wanting to do this, first I have several tables that get their primary keys from a single postgresql sequence, e.g.:
create table fooA ( id int8 default nextval( 'id_source' ) primary key, ... )
create table fooB ( id int8 default nextval( 'id_source' ) primary key, ... )
Additionally, other table columns use the 'default' option to create creation time stamps and so forth.
I can't remove these options from the table definitions because that would break the other applications that use the database.
So, what I'm really after is the ability to define fields with deftable so that those fields are not initialized by (make-instance) but read into the dao after creation, similarly to how auto-id appears to work.
I looked through the archives but didn't see anything that looked related to this.
thanks, Peter _______________________________________________ postmodern-devel mailing list postmodern-devel@common-lisp.net http://common-lisp.net/cgi-bin/mailman/listinfo/postmodern-devel
participants (3)
-
Drew Crampsie -
Marijn Haverbeke -
Peter Eddy