Re: [Sqlgrey-users] Add primary keys to tables?

[Lionel B. <lio...@bo...>]

Hi,

I didn't answer the original message at first because :
- I believed I reacted on a similar subject several years ago,
- the message title doesn't actually make sense to me.

I suspect some people are coming here with a basic ORM background and
don't fully understand the difference between primary keys and object
ids (PKs/OIDs in the following). So here is a complete answer..

Le 08/02/2016 23:09, Karl O. Pinc a écrit :
> On Mon, 08 Feb 2016 21:47:17 +0100
> Jean-Michel Pouré - GOOZE <jm...@go...> wrote:
>
>> Le mardi 26 janvier 2016 à 17:31 +0000, Holger Steinhauer a écrit :
>>> I am working on a small private project which also involves sqlgrey
>>> integration. Lately, I was playing around with the data and just
>>> came to the point I was before: The tables do not have real primary
>>> keys.

Sure they have at least for tables where it makes sense. The notable
exception is "connect" because enforcing unicity with a PK would both
slow down access to this very time-sensitive table and force SQLgrey to
handle collisions which it really doesn't need to.
I suspect that what you call "real primary keys" are in fact OIDs (not
in the "internal database value" sense, but in the "ORM object id"
sense). From the top of my head, OIDs are only useful in the following
cases :
- identifying objects that don't have any obvious primary key (for
example an user on a web application could want to change his login and
not break all relations with the data linked to him/her),
- simplifying ORMs code and especially how they manage foreign keys
(some ORMs can handle composite PKs though),
- making indexes on foreign keys accesses faster.
We don't use any ORM or foreign keys in SQLgrey and all the objects we
manage are uniquely defined with invariable properties so we are free to
avoid OIDs as they would serve no purpose at all and just slow us down.

>>>  Did anyone try to run sqlgrey with an altered database
>>> schema, where the ID is generic and generated for each entry?

I'm not sure why you would do such a thing.

>>>  
>>> Also, is sqlgrey still in development? Just asking if it is “worth”
>>> raising an Feature Request :)

There hasn't been any development for a while but I'm not aware of any
bug surfacing for a long time either. AFAIK it just works.
I'm not likely to put much effort in it. 99% of my developments are in
Ruby now and while Perl isn't bad it's not as comfortable for me. So the
latest changes have been implemented by other developers.
I thought about rewriting it in Ruby some years ago. This would have
been an opportunity to make the code more readable (I think I could do a
better job on this front) but as I said, it just works (I still use it
myself on several personal and corporate servers).

> You'd have to put forward a use-case. Otherwise it's wasted storage.

Not only would it be wasted storage but it would slow down the database
too (dead weight slows you down, for example not being able to keep the
whole database in RAM because of the additional weight would have a
significant impact on large sites).
Jean-Michel Pouré seemed to imply that it could have performance
benefits bat I can't see how. There's not a single access to the
database that could even use an OID, every object must be accessed based
on conditions on its attributes (parts of email addresses, which are the
only information relevant to SQLgrey's database a mail server can
transmit to it).

Best regards,

Lionel