UUID primary keys for Web2py

[rex]

UUIDs as primary keys have some advantage, e.g., avoiding collisions
when databases are merged.

The link below has an implementation for Django. How difficult would
it be to make UUIDs optional primary keys in Web2py?

http://www.ibm.com/developerworks/blogs/page/johnston?tag=Django

* A Database field to store UUID/GUID values and also support
the 'auto' property so that such a field can be used as an
auto-generated primary key value.

* A Database field to store regular expressions and while
these are just strings we would like to have a form validator
that ensures that the text you enter is a valid regular
expression.

The first was easy, we simply subclassed the standard Django
CharField model field class, fixed it's length at 36 characters
and used the uuid module to generate a value if the 'auto'
property is set. Note that the uuid module is included in Python
2.5 but not 2.4 or previous so you'll need to download it from
Ka-Ping Yee. We also ensured that if 'auto' is set then any such
property is not editable in the Django admin UI - this logic was
taken from the current implementation of auto properties in Django
itself. The code below shows the content of a module used in a
number of places in the project and specifically the class
UuidField is used by our model classes.

The second was also relatively easy, though it took a little
longer to find some code to crib from but the result is also shown
below in the isValidRegularExpression function. The approach is
pretty simple (simplistic?) and involves passing the field value
through the regular expression compile function and if that throws
an exception assume that the value is not a legal expression. This
seems to work pretty well, certainly well enough for our purposes
anyway.


import uuid

from django.db.models.fields import CharField

class UuidField(CharField):
""" A field which stores a UUID value, this may also have the
Boolean
attribute 'auto' which will set the value on initial save to a
new
UUID value (calculated using the UUID1 method). Note that
while all
UUIDs are expected to be unique we enforce this with a DB
constraint.
"""
def __init__(self, verbose_name=None, name=None, auto=False,
**kwargs):
self.auto = auto
# Set this as a fixed value, we store UUIDs in text.
kwargs['maxlength'] = 36
if auto:
# Do not let the user edit UUIDs if they are auto-
assigned.
kwargs['editable'] = False
kwargs['blank'] = True
CharField.__init__(self, verbose_name, name, **kwargs)

def get_internal_type(self):
""" see CharField.get_internal_type
Need to override this, or the type mapping for table
creation fails.
"""
return CharField.__name__

def pre_save(self, model_instance, add):
""" see CharField.pre_save
This is used to ensure that we auto-set values if
required.
"""
value = super(UuidField, self).pre_save(model_instance, add)
if (not value) and self.auto:
# Assign a new value for this attribute if required.
value = str(uuid.uuid1())
setattr(model_instance, self.attname, value)
return value

[yarko]

Does this need to replace id? Couldn't you _add_ a GUID to your
tables and use it?

What is the advantage of replacing database local table.id?

(I'm not saying there isn't - it's just not obvious to me...)

[DiPierro, Massimo]

Yarko is right, for most uses you can just a columns

SQLField('uuid',default=uuid.uuid4(),unique)

and each record will have a unique uuid. The problem is that if you reference the record you still reference it by id and not by uuid. You need to manually create an index for the uuid and handle the reference.

Massimo
________________________________________
From: web...@googlegroups.com [web...@googlegroups.com] On Behalf Of yarko [yar...@gmail.com]
Sent: Friday, September 12, 2008 4:53 PM
To: web2py Web Framework
Subject: [web2py:9066] Re: UUID primary keys for Web2py

[Timbo]

> UUIDs as primary keys have some advantage, e.g., avoiding collisions when databases are merged.

Massimo, I remember reading (a long time ago) that your position on
databases was that having an id field is perfectly acceptable and the
normal way of doing things. My university taught me differently but I
haven't said anything because there seemed little reason to.

Rex, the advantage you mentioned addresses a problem that should never
be (and aren't if your primary keys are data-intrinsic rather than
arbitrary id numbers).

I agree that:

SQLField('uuid',default=uuid.uuid4(),unique)

seems a decent quick solution for using UUIDs, but if web2py would be
moving away from integer id keys, I'd like to see it move toward being
able to use primary keys comprised of any column or combination of
columns. (This would also allow me to use the ORM with my legacy DBs
as well).

I'm not meaning to restart a religious war, just putting in my 2
cents. =)

On Sep 12, 5:00 pm, "DiPierro, Massimo" <MDiPie...@cs.depaul.edu>
wrote:

> Yarko is right, for most uses you can just a columns
>
> SQLField('uuid',default=uuid.uuid4(),unique)
>
> and each record will have a unique uuid. The problem is that if you reference the record you still reference it by id and not by uuid. You need to manually create an index for the uuid and handle the reference.
>
> Massimo
> ________________________________________

> From: web...@googlegroups.com [web...@googlegroups.com] On Behalf Of yarko [yark...@gmail.com]

[rex]

On Fri, Sep 12, 2008 at 3:00 PM, DiPierro, Massimo
<MDiP...@cs.depaul.edu> wrote:

Yarko is right, for most uses you can just a columns

SQLField('uuid',default=uuid.uuid4(),unique)

and each record will have a unique uuid. The problem is that if you
reference the record you still reference it by id and not by uuid. You
need to manually create an index for the uuid and handle the
reference.

Hello Massimo,

First, I'd like to thank you for creating Web2py. I'm just starting
with web frameworks and it appears to be an excellent tool.

The problem is worse than needing to create an index for the UUID. The
databases I'm working with need to be capable of being merged with
presently unknown databases without collisions on the primary keys.
Using autoincrement creates needless complexity for the type of
databases (animal ancestry records) I'm dealing with. The feature of
UUID primary keys is periodically requested in various database
forums, and the usual response is the same as yours and Yarko's, but
sometimes it is not sufficient.

How much effort would it be to adapt the posted Django solution of
using
uuids as primary keys to Web2py as an alternative for those who need
it?

Here's a rails implementation of uuid as a primary key.

http://codesnipers.com/?q=node/143&title=Using-UUID/GUID-as-Primary-Key-in-Rails

Here's a benchmark comparing uuid & autoincrement as primary keys

http://krow.livejournal.com/497839.html

-rex

[rex]

I forgot to include the URL of my studbook project where I'm using
UUIDs
as primary keys in a MySQL database. The code was done the hard way --
and it unfortunately shows -- using Python directly.

http://www.nosyntax.net/spwiki

Click on "Go to the studbooks" to see the demo that I hope to refactor
using Web2py. Feel free to enter a fake bird or two, as it's just a
demonstration. Note that if you enter a bird it's automatically added
to the drop-down menus as a potential parent for the next bird.

-rex
--
Time flies like wind. Fruit flies like pears.