35 '-' : BOLD | RED, |
35 '-' : BOLD | RED, |
36 '*' : BOLD | YELLOW, |
36 '*' : BOLD | YELLOW, |
37 'V' : BOLD | WHITE, |
37 'V' : BOLD | WHITE, |
38 'K' : BOLD | BLUE} |
38 'K' : BOLD | BLUE} |
39 |
39 |
40 def __init__(self, change, cols1, cols2, id=None): |
40 def __init__(self, change, cols1, cols2, key=None): |
|
41 ''' |
|
42 |
|
43 change - one of '+', '-', '*' (add, remove, update) |
|
44 cols1 - original column values (OrderedDict) |
|
45 cols2 - new column values (OrderedDict) |
|
46 key - primary key columns (OrderedDict) |
|
47 |
|
48 ''' |
41 self.change = change |
49 self.change = change |
42 self.cols1 = cols1 |
50 self.cols1 = cols1 |
43 self.cols2 = cols2 |
51 self.cols2 = cols2 |
44 self.id = id |
52 self.key = key |
45 |
53 |
46 def format(self): |
54 def format(self): |
47 out = [] |
55 out = [] |
48 |
56 |
49 out.append(highlight(1, self.COLORS[self.change])) |
57 out.append(highlight(1, self.COLORS[self.change])) |
68 return [', '.join([self._format_value_del(*x) for x in self.cols1.items()])] |
76 return [', '.join([self._format_value_del(*x) for x in self.cols1.items()])] |
69 if not self.cols1 and self.cols2: |
77 if not self.cols1 and self.cols2: |
70 return [', '.join([self._format_value_add(*x) for x in self.cols2.items()])] |
78 return [', '.join([self._format_value_add(*x) for x in self.cols2.items()])] |
71 |
79 |
72 out = [] |
80 out = [] |
73 if self.id: |
81 if self.key: |
74 out.extend([highlight(1, self.COLORS['*']), self.id[0], ': ', highlight(0), self.id[1], ', ']) |
82 for colname in self.key: |
|
83 out.extend([highlight(1, self.COLORS['*']), colname, ': ', highlight(0), self.key[colname], ', ']) |
75 |
84 |
76 items = [] |
85 items = [] |
77 for i in range(len(self.cols1)): |
86 for i in range(len(self.cols1)): |
78 items.append(( |
87 items.append(( |
79 list(self.cols1.keys())[i], |
88 list(self.cols1.keys())[i], |
120 def _format_set(self, k, v): |
129 def _format_set(self, k, v): |
121 return '{} = {}'.format(k, v) |
130 return '{} = {}'.format(k, v) |
122 |
131 |
123 def _format_where(self): |
132 def _format_where(self): |
124 out = [' WHERE '] |
133 out = [' WHERE '] |
125 out.extend([self.id[0], ' = ']) |
134 for colname in self.key: |
126 out.append(self.id[1]) |
135 out.extend([colname, ' = ', self.key[colname], ' AND ']) |
127 out.append(';') |
136 out[-1] = ';' |
128 return out |
137 return out |
129 |
138 |
130 class PgDataDiff: |
139 class PgDataDiff: |
131 def __init__(self, conn1, conn2): |
140 def __init__(self, conn1, conn2): |
132 self.allowcolor = False |
141 self.allowcolor = False |
194 for ln in self.iter_diff(): |
203 for ln in self.iter_diff(): |
195 print(ln.format_patch(self.fulltable1)) |
204 print(ln.format_patch(self.fulltable1)) |
196 |
205 |
197 def _select(self): |
206 def _select(self): |
198 browser = pgbrowser.PgBrowser(self.conn1) |
207 browser = pgbrowser.PgBrowser(self.conn1) |
|
208 |
199 columns = browser.list_columns(schema=self.schema1, table=self.table1, order=1) |
209 columns = browser.list_columns(schema=self.schema1, table=self.table1, order=1) |
200 if not columns: |
210 if not columns: |
201 raise Exception('Table %s.%s not found.' % (self.schema1, self.table1)) |
211 raise Exception('Table %s.%s not found.' % (self.schema1, self.table1)) |
202 columns_sel = ', '.join(['"' + x['name'] + '"' for x in columns]) |
212 columns_sel = ', '.join(['"' + x['name'] + '"' for x in columns]) |
203 self.colnames = [x['name'] for x in columns] |
213 self.colnames = [x['name'] for x in columns] |
204 |
214 |
205 query1 = 'SELECT ' + columns_sel + ' FROM ' + self.fulltable1 + ' ORDER BY 1;' |
215 pkey = [ind for ind in browser.list_indexes(schema=self.schema1, table=self.table1) if ind['primary']] |
206 query2 = 'SELECT ' + columns_sel + ' FROM ' + self.fulltable2 + ' ORDER BY 1;' |
216 if not pkey: |
|
217 raise Exception('Table %s.%s has no primary key.' % (self.schema1, self.table1)) |
|
218 pkey = pkey[0] |
|
219 pkey_sel = ', '.join(['"' + x + '"' for x in pkey['columns']]) |
|
220 self.pkeycolnames = pkey['columns'] |
|
221 |
|
222 query1 = 'SELECT ' + columns_sel + ' FROM ' + self.fulltable1 + ' ORDER BY ' + pkey_sel |
|
223 query2 = 'SELECT ' + columns_sel + ' FROM ' + self.fulltable2 + ' ORDER BY ' + pkey_sel |
207 |
224 |
208 curs1 = self.conn1.cursor() |
225 curs1 = self.conn1.cursor() |
209 curs2 = self.conn2.cursor() |
226 curs2 = self.conn2.cursor() |
210 |
227 |
211 curs1.execute(query1) |
228 curs1.execute(query1) |
214 return curs1, curs2 |
231 return curs1, curs2 |
215 |
232 |
216 def _compare_data(self, row1, row2): |
233 def _compare_data(self, row1, row2): |
217 cols1 = OrderedDict() |
234 cols1 = OrderedDict() |
218 cols2 = OrderedDict() |
235 cols2 = OrderedDict() |
219 for i in range(len(row1)): |
236 for name in row1: |
220 if row1[i] != row2[i]: |
237 if row1[name] != row2[name]: |
221 cols1[self.colnames[i]] = row1[i] |
238 cols1[name] = row1[name] |
222 cols2[self.colnames[i]] = row2[i] |
239 cols2[name] = row2[name] |
223 if cols1: |
240 if cols1: |
224 id = (self.colnames[0], row1[0]) |
241 key = OrderedDict(zip(self.pkeycolnames, [row1[colname] for colname in self.pkeycolnames])) |
225 return DiffData('*', cols1, cols2, id=id) |
242 return DiffData('*', cols1, cols2, key=key) |
226 |
243 |
227 return None |
244 return None |
228 |
245 |
229 def _compare_row(self, row1, row2): |
246 def _compare_row(self, row1, row2): |
230 if row2 is None: |
247 if row2 is None: |
231 cols1 = OrderedDict(zip(self.colnames, row1)) |
248 key = OrderedDict(zip(self.pkeycolnames, [row1[colname] for colname in self.pkeycolnames])) |
232 id = (self.colnames[0], row1[0]) |
249 return DiffData('-', row1, None, key=key) |
233 return DiffData('-', cols1, None, id=id) |
|
234 if row1 is None: |
250 if row1 is None: |
235 cols2 = OrderedDict(zip(self.colnames, row2)) |
251 return DiffData('+', None, row2) |
236 return DiffData('+', None, cols2) |
252 |
237 |
253 |
238 if row1[0] < row2[0]: |
254 for keyname in self.pkeycolnames: |
239 cols1 = OrderedDict(zip(self.colnames, row1)) |
255 if row1[keyname] < row2[keyname]: |
240 id = (self.colnames[0], row1[0]) |
256 key = OrderedDict(zip(self.pkeycolnames, [row1[colname] for colname in self.pkeycolnames])) |
241 return DiffData('-', cols1, None, id=id) |
257 return DiffData('-', row1, None, key=key) |
242 if row1[0] > row2[0]: |
258 for keyname in self.pkeycolnames: |
243 cols2 = OrderedDict(zip(self.colnames, row2)) |
259 if row1[keyname] > row2[keyname]: |
244 return DiffData('+', None, cols2) |
260 return DiffData('+', None, row2) |
245 |
261 |
246 return self._compare_data(row1, row2) |
262 return self._compare_data(row1, row2) |
247 |
263 |