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 action 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 action 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()
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.
In alternative you can use XML-RPC to export/import the file.
If the records reference uploaded files, you also need to export/import the content of the upload folders. Notice that files therein are already labeled by UUIDs so you do not need to worry about naming conflicts and references.