wiki:BluePrint/Synchronisation

Version 10 (modified by Fran Boon, 14 years ago) ( diff )

CSV export/import can fix references without the need for UUID!

This page hosts the detailed specification for the Blueprint for the Synchronisation.

We need to be able to support synchronising data between instances:

The UUIDs have already been configured: DeveloperGuidelinesDatabaseSynchronization

We need to extend this by Exporting the tables (CSV is best-supported within Web2Py currently)
"complete database backup/restore with db.export_to_csv_file(..),db.import_from_csv_file(...),
reimporting optionally fixes references without need for uuid"

Initially this can be done using appadmin, but we want to make a user-friendly way of dumping all relevant tables. (Need clear list of which tables to include - e.g. not lookup lists which are the same across sites, not site-specific stuff such as system_config, gis_keys, etc)

Export tables as CSV (in Controller):

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

Import tables at the other end:

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) 

Create an index manually to make the search by uuid faster.

other related threads:

There is a simple 1-table example appliance which has the ability to do syncs via XML-RPC:

In S2 the record ids are UUIDs built from each instance's 'base_uuid'

There is a sync_instance table:

CREATE TABLE sync_instance (
    base_uuid VARCHAR(4) NOT NULL, -- Instance id
    owner VARCHAR(100), -- Instance owner's name
    contact TEXT, -- Contact details of the instance owner
    url VARCHAR(100) DEFAULT NULL, -- Server url if exists
    last_update TIMESTAMP NOT NULL, -- Last Time sync with the instance
    sync_count INT DEFAULT 0, -- Number of times synchronized
    PRIMARY KEY(base_uuid)
);
Note: See TracWiki for help on using the wiki.