4
start_time = time.time()
5
db_conn = psycopg2.connect("dbname=uf8-0rc3_SYNC_SERVER_LIGHT_WITH_MASTER_1")
7
ct = {'keep2': {}, 'delete': {}, 'single': {}}
10
# get id of HQ instances
11
cr.execute("select id from sync_server_entity where name in ('HQ_OCA', 'OCG_HQ', 'OCBHQ')")
12
hq_ids = tuple([x[0] for x in cr.fetchall()])
14
assert len(hq_ids) == 3
16
cr.execute("select min(last_sequence) from sync_server_entity where state != 'invalidated'")
17
min_seq = cr.fetchone()[0] - 1
19
print "Min seq", min_seq
21
# updates linked to inactive rules are dead
22
cr.execute("delete from sync_server_update where rule_id in (select id from sync_server_sync_rule where active='f')")
23
ct['inactive_rules'] = cr.rowcount
25
# delete update: delete previous updates
26
cr.execute("""select source, sdref, max(sequence), model from sync_server_update
27
where source in %s and
28
model in ('ir.translation', 'ir.model.access', 'msf_field_access_rights.field_access_rule_line', 'ir.rule', 'supplier.catalogue.line', 'product.list.line') and
31
group by source, sdref, model""", (hq_ids, min_seq))
32
for x in cr.fetchall():
33
ct['delete'].setdefault(x[3], 0)
34
# keep the delete update: for records created by data.xml and update not yet pulled by instances
35
cr.execute("delete from sync_server_update where source=%s and sdref=%s and sequence < %s and model=%s", (x[0], x[1], x[2], x[3]))
36
ct['delete'][x[3]] += cr.rowcount
39
# multiple updates for the same record: keep 1st (if other updates are linked to this one) and last
40
cr.execute("""select source, sdref, min(sequence), max(sequence), model from sync_server_update
41
where source in %s and
42
model in ('product.product', 'product.nomenclature', 'hr.employee', 'res.currency.rate') and
45
group by source, sdref, model
46
having(count(*) >2)""", (hq_ids, min_seq))
47
for x in cr.fetchall():
48
ct['keep2'].setdefault(x[4], 0)
49
cr.execute("delete from sync_server_update where source=%s and sdref=%s and sequence not in (%s, %s) and model=%s and is_deleted='f'", (x[0], x[1], x[2], x[3], x[4]))
50
ct['keep2'][x[4]] += cr.rowcount
52
# multiple updates, keep only the last update as we know these xmlids are not used in other sync updates
53
cr.execute("""select source, sdref, max(sequence), model from sync_server_update where
55
model in ('ir.translation', 'ir.model.access', 'msf_field_access_rights.field_access_rule_line', 'ir.rule', 'supplier.catalogue.line', 'product.list.line') and
58
group by source, sdref, source, model
59
having(count(*) >1)""", (hq_ids, min_seq))
60
for x in cr.fetchall():
61
ct['single'].setdefault(x[3], 0)
62
cr.execute("delete from sync_server_update where source=%s and sdref=%s and sequence != %s and model=%s", (x[0], x[1], x[2], x[3]))
63
ct['single'][x[3]] += cr.rowcount
65
# delete not-masters updates older than 18 months
66
cr.execute("""delete from sync_server_update u
68
(select id from sync_server_sync_rule where master_data='f')
69
and u.create_date < now() - interval '18 months'
71
ct['month18'] = cr.rowcount
73
# delete orphean "pulled by" records
74
cr.execute("delete from sync_server_entity_rel where update_id not in (select id from sync_server_update)")
75
ct['pulled_by'] = cr.rowcount
78
#print '****** rollback'
82
print 'Time', time.time() - start_time