'Merge a duplicate record into its original and remove the
duplicate, updating all references in the database.
@param original_id: the ID of the original record
@param duplicate_id: the ID of the duplicate record
@param replace: list fields names for which to replace the
values in the original record with the values
of the duplicate
@param update: dict of {field:value} to update the final record
@param main: internal indicator for recursive calls
@status: work in progress
@todo: de-duplicate components and link table entries
@note: virtual references (i.e. non-SQL, without foreign key
constraints) must be declared in the table configuration
of the referenced table like:
s3db.configure(tablename, referenced_by=[(tablename, fieldname)])
This does not apply for list:references which will be found
automatically.
@note: this method can only be run from master resources (in order
to find all components). To merge component records, you have
to re-define the component as a master resource.
@note: CLI calls must db.commit()'

def merge(self, original_id, duplicate_id, replace=None, update=None, main=True):

    self.main = main
    db = current.db
    resource = self.resource
    table = resource.table
    tablename = resource.tablename
    if resource.parent:
        self.raise_error('Must    not    merge    from    component', SyntaxError)
    auth = current.auth
    has_permission = auth.s3_has_permission
    permitted = (has_permission('update', table, record_id=original_id) and has_permission('delete', table, record_id=duplicate_id))
    if (not permitted):
        self.raise_error('Operation    not    permitted', auth.permission.error)
    s3db = current.s3db
    if main:
        s3db.load_all_models()
    if db._lazy_tables:
        for tn in db._LAZY_TABLES.keys():
            db[tn]
    original = None
    duplicate = None
    query = table._id.belongs([original_id, duplicate_id])
    if ('deleted' in table.fields):
        query &= (table.deleted != True)
    rows = db(query).select(table.ALL, limitby=(0, 2))
    for row in rows:
        record_id = row[table._id]
        if (str(record_id) == str(original_id)):
            original = row
            original_id = row[table._id]
        elif (str(record_id) == str(duplicate_id)):
            duplicate = row
            duplicate_id = row[table._id]
    msg = 'Record    not    found:    %s.%s'
    if (original is None):
        self.raise_error((msg % (tablename, original_id)), KeyError)
    if (duplicate is None):
        self.raise_error((msg % (tablename, duplicate_id)), KeyError)
    single = {}
    for component in resource.components.values():
        if (not component.multiple):
            ctablename = component.tablename
            if (ctablename in single):
                single[ctablename].append(component)
            else:
                single[ctablename] = [component]
    is_super_entity = ((table._id.name != 'id') and ('instance_type' in table.fields))
    referenced_by = list(table._referenced_by)
    virtual_references = s3db.get_config(tablename, 'referenced_by')
    if virtual_references:
        referenced_by.extend(virtual_references)
    for t in db:
        for f in t:
            ftype = str(f.type)
            if ((ftype[:14] == 'list:reference') and (ftype[15:(15 + len(tablename))] == tablename)):
                referenced_by.append((t._tablename, f.name))
    update_record = self.update_record
    delete_record = self.delete_record
    fieldname = self.fieldname
    define_resource = s3db.resource
    for referee in referenced_by:
        if isinstance(referee, Field):
            (tn, fn) = (referee.tablename, referee.name)
        else:
            (tn, fn) = referee
        se = s3db.get_config(tn, 'super_entity')
        if (is_super_entity and ((isinstance(se, (list, tuple)) and (tablename in se)) or (se == tablename))):
            continue
        if ((tn not in db) or (fn not in db[tn].fields)):
            continue
        rtable = db[tn]
        if (tn in single):
            for component in single[tn]:
                if (component.link is not None):
                    component = component.link
                if (fn == component.fkey):
                    join = component.get_join()
                    pkey = component.pkey
                    lkey = (component.lkey or component.fkey)
                    query = ((table[pkey] == original[pkey]) & join)
                    osub = db(query).select(limitby=(0, 1)).first()
                    query = ((table[pkey] == duplicate[pkey]) & join)
                    dsub = db(query).select(limitby=(0, 1)).first()
                    ctable = component.table
                    ctable_id = ctable._id
                    if (dsub is None):
                        continue
                    elif (not osub):
                        dsub_id = dsub[ctable_id]
                        data = {lkey: original[pkey]}
                        success = update_record(ctable, dsub_id, dsub, data)
                    elif (component.linked is not None):
                        dsub_id = dsub[ctable_id]
                        delete_record(ctable, dsub_id)
                    else:
                        osub_id = osub[ctable_id]
                        dsub_id = dsub[ctable_id]
                        cresource = define_resource(component.tablename)
                        cresource.merge(osub_id, dsub_id, replace=replace, update=update, main=False)
        rfield = rtable[fn]
        (ktablename, key, multiple) = s3_get_foreign_key(rfield)
        if (not ktablename):
            if (str(rfield.type) == 'integer'):
                key = table._id.name
            else:
                continue
        if multiple:
            query = rtable[fn].contains(duplicate[key])
        else:
            query = (rtable[fn] == duplicate[key])
        rows = db(query).select(rtable._id, rtable[fn])
        for row in rows:
            if (not multiple):
                data = {fn: original[key]}
            else:
                keys = [k for k in row[fn] if (k != duplicate[key])]
                if (original[key] not in keys):
                    keys.append(original[key])
                data = {fn: keys}
            update_record(rtable, row[rtable._id], row, data)
    super_entities = resource.get_config('super_entity')
    if (super_entities is not None):
        if (not isinstance(super_entities, (list, tuple))):
            super_entities = [super_entities]
        for super_entity in super_entities:
            super_table = s3db.table(super_entity)
            if (not super_table):
                continue
            superkey = super_table._id.name
            skey_o = original[superkey]
            if (not skey_o):
                msg = ('No    %s    found    in    %s.%s' % (superkey, tablename, original_id))
                current.log.warning(msg)
                s3db.update_super(table, original)
                skey_o = original[superkey]
            if (not skey_o):
                continue
            skey_d = duplicate[superkey]
            if (not skey_d):
                msg = ('No    %s    found    in    %s.%s' % (superkey, tablename, duplicate_id))
                current.log.warning(msg)
                continue
            sresource = define_resource(super_entity)
            sresource.merge(skey_o, skey_d, replace=replace, update=update, main=False)
    data = Storage()
    if replace:
        for k in replace:
            fn = fieldname(k)
            if (fn and (fn in duplicate)):
                data[fn] = duplicate[fn]
    if update:
        for (k, v) in update.items():
            fn = fieldname(k)
            if (fn in table.fields):
                data[fn] = v
    if len(data):
        r = None
        p = Storage([(fn, ('__deduplicate_%s__' % fn)) for fn in data if (table[fn].unique and (table[fn].type == 'string') and (data[fn] == duplicate[fn]))])
        if p:
            r = Storage([(fn, original[fn]) for fn in p])
            update_record(table, duplicate_id, duplicate, p)
        update_record(table, original_id, original, data)
        if r:
            update_record(table, duplicate_id, duplicate, r)
    if (not is_super_entity):
        self.merge_realms(table, original, duplicate)
        delete_record(table, duplicate_id, replaced_by=original_id)
    return True
