How to create dao class with identity column as primary key?
Hi, I'm learning to use postmodern as part of my hobby project but I've hit a small bump in the process. I can have the identity column defined for table without dao with something like: (s-sql:sql (:create-table (:if-not-exists 'user) ((id :type int :identity-always t) (username :type text) (:primary-key id))) But I intent to use dao classes so it would be nice to have identity column specified as part to defclass slot options. Is there some way to create dao class with identity column? Br, Timo M
Hello Timo, A couple of notes here. First "user" is a restricted word for postgresql, so I suggest naming the table "users" instead. Second, your s-sql sample create table misplaced a paren. You need another paren after the username column and before the primary key I agree that daos do not yet have identity columns, but that really only prevents you from creating a table using the dao. So consider the following where we create a table using s-sql, insert some items, demonstrate that we can retrieve an item using a dao, then demonstrate we can create a dao item, insert it in the table and then retrieve it. Postgresql handles all the identity stuff. Also note that I used "users" as the table, but I can create a dao class named "user". (query (:create-table (:if-not-exists 'users) ((id :type integer :identity-always t) (username :type text)) (:primary-key id))) (query (:insert-rows-into 'users :columns 'username :values '(("Jason") ("Tim") ("Karolyn")))) (defclass user () ((id :col-type integer :accessor id) (username :col-type text :initarg :username :accessor username)) (:metaclass dao-class) (:table-name users) (:keys id)) (username (get-dao 'user 1)) "Jason" (let ((item (make-instance 'user :username "Zenya"))) (insert-dao item)) (username (get-dao 'user 4)) "Zenya" Does this help? Sabra Crolleton On Tue, Jun 9, 2020 at 12:41 PM Timo Myyrä <timo.myyra@bittivirhe.fi> wrote:
Hi,
I'm learning to use postmodern as part of my hobby project but I've hit a small bump in the process. I can have the identity column defined for table without dao with something like: (s-sql:sql (:create-table (:if-not-exists 'user) ((id :type int :identity-always t) (username :type text) (:primary-key id)))
But I intent to use dao classes so it would be nice to have identity column specified as part to defclass slot options. Is there some way to create dao class with identity column?
Br, Timo M
Hi, So the best bet would be to just use the create-table to create the database structure and then define dao classes separately. I was trying to avoid that as I'd guess it will be easy to have definitions given on create-table and dao class to drift apart. I was trying to avoid that by using just dao so database stuff would be given on just one place. But now that we're talking daos, how should I handle references to other tables with dao class? If we take the User dao class for example, I add the users table and define the user dao. I have then separate passwords table that stores user passwords. It doesn't feel right to make passwords an dao class. The passwords are tied very tighly to user so it would seeem logical to query the passwords with user dao. What would be 'idiomatic way' to do this with postmodern? Just use the user dao class and add an method to it, which makes normal sql query for users passwords? Just define password dao and query it with user id? A bit basic questions. The documents and examples show small cases so its hard to see the big picture from them. timo Sabra Crolleton <sabra.crolleton@gmail.com> writes:
Hello Timo,
A couple of notes here. First "user" is a restricted word for postgresql, so I suggest naming the table "users" instead.
Second, your s-sql sample create table misplaced a paren. You need another paren after the username column and before the primary key
I agree that daos do not yet have identity columns, but that really only prevents you from creating a table using the dao.
So consider the following where we create a table using s-sql, insert some items, demonstrate that we can retrieve an item using a dao, then demonstrate we can create a dao item, insert it in the table and then retrieve it. Postgresql handles all the identity stuff. Also note that I used "users" as the table, but I can create a dao class named "user".
(query (:create-table (:if-not-exists 'users) ((id :type integer :identity-always t) (username :type text)) (:primary-key id)))
(query (:insert-rows-into 'users :columns 'username :values '(("Jason") ("Tim") ("Karolyn"))))
(defclass user () ((id :col-type integer :accessor id) (username :col-type text :initarg :username :accessor username)) (:metaclass dao-class) (:table-name users) (:keys id))
(username (get-dao 'user 1)) "Jason"
(let ((item (make-instance 'user :username "Zenya"))) (insert-dao item))
(username (get-dao 'user 4))
"Zenya"
Does this help?
Sabra Crolleton
On Tue, Jun 9, 2020 at 12:41 PM Timo Myyrä <timo.myyra@bittivirhe.fi> wrote:
Hi,
I'm learning to use postmodern as part of my hobby project but I've hit a small bump in the process. I can have the identity column defined for table without dao with something like: (s-sql:sql (:create-table (:if-not-exists 'user) ((id :type int :identity-always t) (username :type text) (:primary-key id)))
But I intent to use dao classes so it would be nice to have identity column specified as part to defclass slot options. Is there some way to create dao class with identity column?
Br, Timo M
From a design standpoint, I agree that I would not include user passwords in a dao class with the rest of the user information. (I do hope those are salted passwords as well.) That also implies that passwords should not be in the same table as the rest of the user information. However, your
Hi Timo, I can probably add the identity column stuff to daos this weekend and that would solve your first issue. I have to admit that I do not use daos (just sql or s-sql), so they have never been a high priority for me. With respect to references and foreign keys to other tables, I can also try to write up an explanation this weekend, but please understand that postmodern daos are really simple. They are not full ORMs. Maybe someone else on the mailing list can write up how they use them. password validation function just needs the user id and the password to be tested. Why pass around an entire user dao? (This shows my biases as a dba rather than a developer.) But I am confused by your comment about adding a method to a user dao class. Common lisp classes do not have methods. Methods are generic and are specialized on their parameters. So I could write a validate-password generic function that takes two parameters, a user-id and a password string. Then I would write two methods, one that accepted an integer as the user-id parameter and a second method that accepted a user-dao as the user-id parameter (and then internally extracted the user-id out of the user dao). See, e.g. the explanations here: http://www.gigamonkeys.com/book/object-reorientation-generic-functions.html Sabra On Tue, Jun 9, 2020 at 4:01 PM Timo Myyrä <timo.myyra@bittivirhe.fi> wrote:
Hi,
So the best bet would be to just use the create-table to create the database structure and then define dao classes separately. I was trying to avoid that as I'd guess it will be easy to have definitions given on create-table and dao class to drift apart. I was trying to avoid that by using just dao so database stuff would be given on just one place.
But now that we're talking daos, how should I handle references to other tables with dao class? If we take the User dao class for example, I add the users table and define the user dao. I have then separate passwords table that stores user passwords. It doesn't feel right to make passwords an dao class. The passwords are tied very tighly to user so it would seeem logical to query the passwords with user dao. What would be 'idiomatic way' to do this with postmodern? Just use the user dao class and add an method to it, which makes normal sql query for users passwords? Just define password dao and query it with user id?
A bit basic questions. The documents and examples show small cases so its hard to see the big picture from them.
timo
Sabra Crolleton <sabra.crolleton@gmail.com> writes:
Hello Timo,
A couple of notes here. First "user" is a restricted word for postgresql, so I suggest naming the table "users" instead.
Second, your s-sql sample create table misplaced a paren. You need another paren after the username column and before the primary key
I agree that daos do not yet have identity columns, but that really only prevents you from creating a table using the dao.
So consider the following where we create a table using s-sql, insert some items, demonstrate that we can retrieve an item using a dao, then demonstrate we can create a dao item, insert it in the table and then retrieve it. Postgresql handles all the identity stuff. Also note that I used "users" as the table, but I can create a dao class named "user".
(query (:create-table (:if-not-exists 'users) ((id :type integer :identity-always t) (username :type text)) (:primary-key id)))
(query (:insert-rows-into 'users :columns 'username :values '(("Jason") ("Tim") ("Karolyn"))))
(defclass user () ((id :col-type integer :accessor id) (username :col-type text :initarg :username :accessor username)) (:metaclass dao-class) (:table-name users) (:keys id))
(username (get-dao 'user 1)) "Jason"
(let ((item (make-instance 'user :username "Zenya"))) (insert-dao item))
(username (get-dao 'user 4))
"Zenya"
Does this help?
Sabra Crolleton
On Tue, Jun 9, 2020 at 12:41 PM Timo Myyrä <timo.myyra@bittivirhe.fi> wrote:
Hi,
I'm learning to use postmodern as part of my hobby project but I've hit a small bump in the process. I can have the identity column defined for table without dao with something like: (s-sql:sql (:create-table (:if-not-exists 'user) ((id :type int :identity-always t) (username :type text) (:primary-key id)))
But I intent to use dao classes so it would be nice to have identity column specified as part to defclass slot options. Is there some way to create dao class with identity column?
Br, Timo M
Sabra Crolleton <sabra.crolleton@gmail.com> writes:
Hi Timo,
I can probably add the identity column stuff to daos this weekend and that would solve your first issue. I have to admit that I do not use daos (just sql or s-sql), so they have never been a high priority for me.
With respect to references and foreign keys to other tables, I can also try to write up an explanation this weekend, but please understand that postmodern daos are really simple. They are not full ORMs. Maybe someone else on the mailing list can write up how they use them.
Yeah, my use cases seem bit cleaner with few daos and some more direct sql. Daos seem nice until you get to more complex queries and then they seem to add just confusion. At least thats been my experience from moving to manually mapping raw sql query results to objects vs. heavy use of ORM library. It would be great if identity gets added to daos. Would make things a bit more consistent.
From a design standpoint, I agree that I would not include user passwords in a dao class with the rest of the user information. (I do hope those are salted passwords as well.) That also implies that passwords should not be in the same table as the rest of the user information. However, your password validation function just needs the user id and the password to be tested. Why pass around an entire user dao? (This shows my biases as a dba rather than a developer.)
But I am confused by your comment about adding a method to a user dao class. Common lisp classes do not have methods. Methods are generic and are specialized on their parameters. So I could write a validate-password generic function that takes two parameters, a user-id and a password string. Then I would write two methods, one that accepted an integer as the user-id parameter and a second method that accepted a user-dao as the user-id parameter (and then internally extracted the user-id out of the user dao). See, e.g. the explanations here: http://www.gigamonkeys.com/book/object-reorientation-generic-functions.html
Sabra
By "adding method to class" I meant adding generic function method specializing to user dao. But as pointed out, probably seem a bit too 'complex' when regular function will work as well. Well, perhaps the code structure starts to become clearer once my project progresses. timo
On Tue, Jun 9, 2020 at 4:01 PM Timo Myyrä <timo.myyra@bittivirhe.fi> wrote:
Hi,
So the best bet would be to just use the create-table to create the database structure and then define dao classes separately. I was trying to avoid that as I'd guess it will be easy to have definitions given on create-table and dao class to drift apart. I was trying to avoid that by using just dao so database stuff would be given on just one place.
But now that we're talking daos, how should I handle references to other tables with dao class? If we take the User dao class for example, I add the users table and define the user dao. I have then separate passwords table that stores user passwords. It doesn't feel right to make passwords an dao class. The passwords are tied very tighly to user so it would seeem logical to query the passwords with user dao. What would be 'idiomatic way' to do this with postmodern? Just use the user dao class and add an method to it, which makes normal sql query for users passwords? Just define password dao and query it with user id?
A bit basic questions. The documents and examples show small cases so its hard to see the big picture from them.
timo
Sabra Crolleton <sabra.crolleton@gmail.com> writes:
Hello Timo,
A couple of notes here. First "user" is a restricted word for postgresql, so I suggest naming the table "users" instead.
Second, your s-sql sample create table misplaced a paren. You need another paren after the username column and before the primary key
I agree that daos do not yet have identity columns, but that really only prevents you from creating a table using the dao.
So consider the following where we create a table using s-sql, insert some items, demonstrate that we can retrieve an item using a dao, then demonstrate we can create a dao item, insert it in the table and then retrieve it. Postgresql handles all the identity stuff. Also note that I used "users" as the table, but I can create a dao class named "user".
(query (:create-table (:if-not-exists 'users) ((id :type integer :identity-always t) (username :type text)) (:primary-key id)))
(query (:insert-rows-into 'users :columns 'username :values '(("Jason") ("Tim") ("Karolyn"))))
(defclass user () ((id :col-type integer :accessor id) (username :col-type text :initarg :username :accessor username)) (:metaclass dao-class) (:table-name users) (:keys id))
(username (get-dao 'user 1)) "Jason"
(let ((item (make-instance 'user :username "Zenya"))) (insert-dao item))
(username (get-dao 'user 4))
"Zenya"
Does this help?
Sabra Crolleton
On Tue, Jun 9, 2020 at 12:41 PM Timo Myyrä <timo.myyra@bittivirhe.fi> wrote:
Hi,
I'm learning to use postmodern as part of my hobby project but I've hit a small bump in the process. I can have the identity column defined for table without dao with something like: (s-sql:sql (:create-table (:if-not-exists 'user) ((id :type int :identity-always t) (username :type text) (:primary-key id)))
But I intent to use dao classes so it would be nice to have identity column specified as part to defclass slot options. Is there some way to create dao class with identity column?
Br, Timo M
I will add identities and some of the other standard references and constraints as well this weekend as well as the requisite tests and documentation. I might actually also get testing finalized on easy creation of read-only roles, editor roles and admin roles where you can limit access to certain databases, certain schemas or certain tables. Sabra. On Wed, Jun 10, 2020 at 10:43 AM Timo Myyrä <timo.myyra@bittivirhe.fi> wrote:
Sabra Crolleton <sabra.crolleton@gmail.com> writes:
Hi Timo,
I can probably add the identity column stuff to daos this weekend and that would solve your first issue. I have to admit that I do not use daos (just sql or s-sql), so they have never been a high priority for me.
With respect to references and foreign keys to other tables, I can also try to write up an explanation this weekend, but please understand that postmodern daos are really simple. They are not full ORMs. Maybe someone else on the mailing list can write up how they use them.
Yeah, my use cases seem bit cleaner with few daos and some more direct sql. Daos seem nice until you get to more complex queries and then they seem to add just confusion. At least thats been my experience from moving to manually mapping raw sql query results to objects vs. heavy use of ORM library. It would be great if identity gets added to daos. Would make things a bit more consistent.
From a design standpoint, I agree that I would not include user passwords in a dao class with the rest of the user information. (I do hope those are salted passwords as well.) That also implies that passwords should not be in the same table as the rest of the user information. However, your password validation function just needs the user id and the password to be tested. Why pass around an entire user dao? (This shows my biases as a dba rather than a developer.)
But I am confused by your comment about adding a method to a user dao class. Common lisp classes do not have methods. Methods are generic and are specialized on their parameters. So I could write a validate-password generic function that takes two parameters, a user-id and a password string. Then I would write two methods, one that accepted an integer as the user-id parameter and a second method that accepted a user-dao as the user-id parameter (and then internally extracted the user-id out of the user dao). See, e.g. the explanations here:
http://www.gigamonkeys.com/book/object-reorientation-generic-functions.html
Sabra
By "adding method to class" I meant adding generic function method specializing to user dao. But as pointed out, probably seem a bit too 'complex' when regular function will work as well.
Well, perhaps the code structure starts to become clearer once my project progresses.
timo
On Tue, Jun 9, 2020 at 4:01 PM Timo Myyrä <timo.myyra@bittivirhe.fi>
wrote:
Hi,
So the best bet would be to just use the create-table to create the database structure and then define dao classes separately. I was trying to avoid that as I'd guess it will be easy to have definitions given on create-table and dao class to drift apart. I was trying to avoid that by using just dao so database stuff would be given on just one place.
But now that we're talking daos, how should I handle references to other tables with dao class? If we take the User dao class for example, I add the
table and define the user dao. I have then separate passwords table that stores user passwords. It doesn't feel right to make passwords an dao class. The passwords are tied very tighly to user so it would seeem logical to query the passwords with user dao. What would be 'idiomatic way' to do this with postmodern? Just use the user dao class and add an method to it, which makes normal sql query for users passwords? Just define password dao and query it with user id?
A bit basic questions. The documents and examples show small cases so its hard to see the big picture from them.
timo
Sabra Crolleton <sabra.crolleton@gmail.com> writes:
Hello Timo,
A couple of notes here. First "user" is a restricted word for
users postgresql,
so I suggest naming the table "users" instead.
Second, your s-sql sample create table misplaced a paren. You need another paren after the username column and before the primary key
I agree that daos do not yet have identity columns, but that really only prevents you from creating a table using the dao.
So consider the following where we create a table using s-sql, insert some items, demonstrate that we can retrieve an item using a dao, then demonstrate we can create a dao item, insert it in the table and then retrieve it. Postgresql handles all the identity stuff. Also note that I used "users" as the table, but I can create a dao class named "user".
(query (:create-table (:if-not-exists 'users) ((id :type integer :identity-always t) (username :type text)) (:primary-key id)))
(query (:insert-rows-into 'users :columns 'username :values '(("Jason") ("Tim") ("Karolyn"))))
(defclass user () ((id :col-type integer :accessor id) (username :col-type text :initarg :username :accessor username)) (:metaclass dao-class) (:table-name users) (:keys id))
(username (get-dao 'user 1)) "Jason"
(let ((item (make-instance 'user :username "Zenya"))) (insert-dao item))
(username (get-dao 'user 4))
"Zenya"
Does this help?
Sabra Crolleton
On Tue, Jun 9, 2020 at 12:41 PM Timo Myyrä <timo.myyra@bittivirhe.fi> wrote:
Hi,
I'm learning to use postmodern as part of my hobby project but I've hit a small bump in the process. I can have the identity column defined for table without dao with something like: (s-sql:sql (:create-table (:if-not-exists 'user) ((id :type int :identity-always t) (username :type text) (:primary-key id)))
But I intent to use dao classes so it would be nice to have identity column specified as part to defclass slot options. Is there some way to create dao class with identity column?
Br, Timo M
participants (2)
-
Sabra Crolleton -
Timo Myyrä