How to create dao class with identity column as primary key?

[Sabra Crolleton]

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 at bittivirhe.fi>
wrote:

> Sabra Crolleton <sabra.crolleton at 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 at 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 at 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 at 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
> >> >>
> >> >>
> >>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://mailman.common-lisp.net/pipermail/postmodern-devel/attachments/20200610/83db6ef8/attachment-0001.htm>