Database Synchronization Howto and UUID

69 views
Skip to first unread message

mdipierro

unread,
Dec 23, 2008, 4:45:18 AM12/23/08
to web2py Web Framework
Consider the following example:

db=SQLDB('sqlite:memory:')
db.define_table('person',
db.Field('name'))
db.define_table('dog',
db.Field('owner',db.person),
db.Field('name'))
db.dog.owner.requires=IS_IN_DB(db,'person.id','%(name)s')

id=db.person.insert(name="Massimo")
db.dog.insert(owner=id,name="Snoopy")

Each record is identified by an ID and referenced by that ID. If you
have two copies of the database used by distinct web2py installation,
the ID is unique only within each database and not across databases.
This is a problem when merging records from different databases.

In order to make a record uniquely identifiable across databases, they
must have: 1) a unique id (uuid), 2) a timestamp (to figure out with
one is more recent if multiple copies), 3) reference the uuid instead
of id.

This can be achieved without modifying web2py. Here is what to do:

A) change the model above into:

db.define_table('person',
db.Field('uuid',length=64,default=uuid.uuid4
()),
db.Field
('modified_on','datetime',default=now),
db.Field('name'))
db.define_table('dog',
db.Field('uuid',length=64,default=uuid.uuid4
()),
db.Field
('modified_on','datetime',default=now),
db.Field('owner',length=64),
db.Field('name'))
db.dog.owner.requires=IS_IN_DB(db,'person.uuid','%(name)s')

id=uuid.uuid4()
db.person.insert(name="Massimo",uuid=id)
db.dog.insert(owner=id,name="Snoopy")

B) create a controller to export the database

def export():
s=StringIO.StringIO()
db.export_to_csv_file(s)
response.headers['Content-Type']='text/csv'
return s.getvalue()

C) create a controller to import a saved copy of the other database
and sync records:

def import_and_sync():
form=FORM(INPUT(_type='file',_name='data'),INPUT
(_type='submit'))
if form.accepts(request.vars):
db.import_from_csv_file(form.vars.data.file)
# for every table
for table in db.tables:
# for every uuid, delete all but the most
recent
items=db(db[table].id>0).select(db[table].id,db
[table].uuid,
orderby=~db[table].modified_on,groupby=db
[table].uuid)
for item in items:
db((db[table].uuid==item.uuid)&(db[table].id!
=item.id)).delete() #### exercise
return dict(form=form)

D) create an index manually to make the search by uuid faster.

Notice that B and C work for every database model, they are not
specific for this example.

Exercise: make the line marked as exercise work on GAE.

Massimo

seeker

unread,
Dec 23, 2008, 5:19:15 AM12/23/08
to web2py Web Framework
Thanks Massimo. Great/useful example (for certain use cases).

Your example, at last, made me realize that we have made (I think...)
slightly different assumptions in the discussion of this issue thus
far:

In my case I am simply trying to aggregate multiple child databases
into one parent database. In other words the records in the child
databases are newly created there and then later uploaded to the
parent. Maybe an example will clarify:

1] a number of records are created in ChildDB_A and then uploaded to
ParentDB.
2] a number of OTHER records are created in ChildDB_B and then
uploaded to ParentDB.

In step 2 I do not wish to update the records previously inserted in
step 1; I simply wish to add more (different) records to ParentDB.
If I were to use standard integer id's though there will be a good
chance that the id's from step 1 and step 2 will overlap (and cause
chaos).

Hope that clarifies my problem (or highlights my lack of
understanding ;-)

mdipierro

unread,
Dec 23, 2008, 5:30:18 AM12/23/08
to web2py Web Framework
Then you do not need uuids at all.

You can just db(query).select().export_to_csv_file(...) the records
you want to export and db.table.import_from_csv_file(...) to import
them back.

There is a recent thread on how import_from_sv_file handles and fixes
references.

Massimo

seeker

unread,
Dec 23, 2008, 6:09:50 AM12/23/08
to web2py Web Framework
Had a look at the csv import thread. Excellent! Thanks for the help.

seeker

unread,
Dec 23, 2008, 6:16:06 AM12/23/08
to web2py Web Framework
Next question:

How can this be done via direct database read/write as opposed to
export/import of csv files?

mdipierro

unread,
Dec 23, 2008, 6:44:55 AM12/23/08
to web2py Web Framework
Sorry. I do not understand the question.

Massimo

seeker

unread,
Dec 23, 2008, 6:52:04 AM12/23/08
to web2py Web Framework
If I open both parent and child (x?) databases in a w2p application
and I wish to transfer data from the child db's to the parent db by
reading data from the child db's and then writing the data to the
parent db (i.e. not via a csv file export/import as you describe
above, but direct 'insert ...'); how would I achieve the clever id
substitution which you have provided in the csv import function?

seeker

unread,
Dec 23, 2008, 6:53:40 AM12/23/08
to web2py Web Framework
or would data transfer of this nature always have to happen via file
export import as oppose to direct database access?

Fran

unread,
Dec 23, 2008, 8:42:24 AM12/23/08
to web2py Web Framework
On Dec 23, 9:45 am, mdipierro <mdipie...@cs.depaul.edu> wrote:
> This can be achieved without modifying web2py. Here is what to do:

*Many* thanks for such a clear HOWTO :)

> db.Field('owner',length=64),
> db.dog.owner.requires=IS_IN_DB(db,'person.uuid','%(name)s')

Does replacing the Field definition's db.person with a simple
'length=64' not affect CASCADE DELETE functionality?
This basically pushes the onus of referential integraity onto us from
the framework, right?

F

mdipierro

unread,
Dec 23, 2008, 9:33:33 AM12/23/08
to web2py Web Framework
You are right. In the example it is the framework that knows about
relations, not the db. This will change when/if we will also
referencing fields other than id.

Massimo

mdipierro

unread,
Dec 23, 2008, 9:34:49 AM12/23/08
to web2py Web Framework
P.S. you can set migrate=False and create the table manually so that
dog references person(uuid).

Massimo

Fran

unread,
Dec 23, 2008, 11:11:31 AM12/23/08
to web2py Web Framework
On Dec 23, 2:34 pm, mdipierro <mdipie...@cs.depaul.edu> wrote:
> P.S. you can set migrate=False and create the table manually so that
> dog references person(uuid).

ok, a useful trick...although this then breaks database portability.

Another limitation is that the uuid field is visible within t2.create
() forms....being confusing for users & easy for them to overwrite.
It would be much nicer if it were hidden (like the modified_on field).
Not even db.table.displays works here (which would be limiting anyway
as requires more maintenance whenever fields are added/removed).
t2.display() could also usefully hide this field & for where it
references other fields have an option like t2.itemize()'s
db.table.represent or the IS_IN_DB(db,'table.uuid','table.name')

Many thanks,
Fran.

mdipierro

unread,
Dec 23, 2008, 1:39:56 PM12/23/08
to web2py Web Framework
with

db.table.exposed=[....]

you can set a list of fields that you want visible in T2.create/update

Massimo

Fran

unread,
Dec 24, 2008, 6:56:52 AM12/24/08
to web2py Web Framework
On Dec 23, 6:39 pm, mdipierro <mdipie...@cs.depaul.edu> wrote:
> db.table.exposed=[....]
> you can set a list of fields that you want visible in T2.create/update

db.table.exposes=[] does indeed work for t2.create() & t2.update() :)
With db.table.displays for t2.display() & db.table.represent for
t2.itemize(), this gives good control.

However what is still missing is t2.display() being able to lookup on
the other table from the UUID to a more meaningful field
Like we have for t2.create() & t2.update() via:
db.table.field.requires=IS_IN_DB(db,'table2.uuid','table2.name')

F

mdipierro

unread,
Dec 24, 2008, 7:25:28 AM12/24/08
to web2py Web Framework
Consider:

db.define_table('person',
db.Field('uuid',length=64,default=uuid.uuid4
()),
db.Field
('modified_on','datetime',default=now),
db.Field('name'))
db.define_table('dog',
db.Field('uuid',length=64,default=uuid.uuid4
()),
db.Field
('modified_on','datetime',default=now),
db.Field('owner',length=64),
db.Field('name'))
db.dog.owner.requires=IS_IN_DB(db,'person.uuid','%(name)s')

you can do

db.dog.owner.display=lambda id: db(db.person.id==id).select()[0].name

and t2.displays(db.dog) will show you the owner's name not his id.

Massimo

Fran

unread,
Dec 24, 2008, 9:51:41 AM12/24/08
to web2py Web Framework
On Dec 24, 12:25 pm, mdipierro <mdipie...@cs.depaul.edu> wrote:
> db.dog.owner.display=lambda id: db(db.person.id==id).select()[0].name
> and t2.displays(db.dog) will show you the owner's name not his id.

Perfect - all is now sorted :)

Many thanks,
F

Fran

unread,
Dec 24, 2008, 10:00:30 AM12/24/08
to web2py Web Framework
> On Dec 24, 12:25 pm, mdipierro <mdipie...@cs.depaul.edu> wrote:
> > db.dog.owner.display=lambda id: db(db.person.id==id).select()[0].name
> > and t2.displays(db.dog) will show you the owner's name not his id.

Oops, still have an issue here.
If the owner field is blank for a record then this fails:
SyntaxError: SQLRows: no such row

Is there a way to make this fail gracefully if the field is blank?
(I want many fields to be optional, but if-populated to display
meaningfully..)

Many thanks,
F

Fran

unread,
Dec 27, 2008, 8:20:39 AM12/27/08
to web2py Web Framework
On Dec 24, 3:00 pm, Fran <francisb...@googlemail.com> wrote:
> Is there a way to make this fail gracefully if the field is blank?
> (I want many fields to be optional, but if-populated to display
> meaningfully..)

Fixed it :)

db.dog.owner.display=lambda id: (id and [db(db.person.id==id).select()
[0].name] or ["None"])[0]

F

Fran

unread,
Dec 28, 2008, 10:23:19 AM12/28/08
to web2py Web Framework
On Dec 27, 1:20 pm, Fran <francisb...@googlemail.com> wrote:
> Fixed it :)

& if you need to display an image instead of just having the name of
the uploaded file, then can use the very long:

db.table.field.display=lambda uuid: DIV(A(IMG(_src=URL
(r=request,f='download',args=[db(db.table2.uuid==uuid).select()
[0].image]),_height=100),_class='zoom',_href='#zoom-table-field-%s' %
uuid),DIV(IMG(_src=URL(r=request,f='download',args=[db
(db.table2.uuid==uuid).select()[0].image]),_width=600),_id='zoom-table-
field-%s' % uuid,_class='hidden'))

F

INeedHelp

unread,
Feb 6, 2009, 1:34:31 AM2/6/09
to web2py Web Framework
HI
i using the code provide above and try to run but when i try to import
back the exported file. i have this error
ValueError: Mixing iteration and read methods would lose data

can help please

and is it possibel to set the file exported in notepad/text doc/ms
excel format??

thank in advance

INeedHelp

unread,
Feb 6, 2009, 1:49:12 AM2/6/09
to web2py Web Framework
i have this error also after i using the latest web2py
TypeError: long() argument must be a string or a number, not 'list'
> > F- Hide quoted text -
>
> - Show quoted text -
Reply all
Reply to author
Forward
0 new messages