32 COLORS = { |
32 COLORS = { |
33 '+' : BOLD | GREEN, |
33 '+' : BOLD | GREEN, |
34 '-' : BOLD | RED, |
34 '-' : BOLD | RED, |
35 '*' : BOLD | YELLOW, |
35 '*' : BOLD | YELLOW, |
36 } |
36 } |
37 |
37 |
38 COMMANDS = { |
38 COMMANDS = { |
39 '+' : 'CREATE', |
39 '+' : 'CREATE', |
40 '-' : 'DROP', |
40 '-' : 'DROP', |
41 '*' : 'ALTER', |
41 '*' : 'ALTER', |
42 } |
42 } |
43 |
43 |
44 def __init__(self): |
44 def __init__(self): |
45 self.changes = None |
45 self.changes = None |
46 |
46 |
47 def format(self): |
47 def format(self): |
48 out = [' ' * self.level] |
48 out = [' ' * self.level] |
49 |
49 |
50 out.append(highlight(1, self.COLORS[self.change])) |
50 out.append(highlight(1, self.COLORS[self.change])) |
51 out.append(self.change) |
51 out.append(self.change) |
52 |
52 |
53 out += [' ', self.type, ' ', self.name, highlight(0)] |
53 out += [' ', self.type, ' ', self.name, highlight(0)] |
54 |
54 |
55 if self.changes: |
55 if self.changes: |
56 out += [highlight(1, WHITE), ' (', self._formatchanges(), ')', highlight(0)] |
56 out += [highlight(1, WHITE), ' (', self._formatchanges(), ')', highlight(0)] |
57 |
57 |
58 return ''.join(out) |
58 return ''.join(out) |
59 |
59 |
60 def _formatnotnull(self, notnull): |
60 def _formatnotnull(self, notnull): |
61 if notnull: |
61 if notnull: |
62 return 'NOT NULL' |
62 return 'NOT NULL' |
63 else: |
63 else: |
64 return None |
64 return None |
65 |
65 |
66 def _formatchanges(self): |
66 def _formatchanges(self): |
67 res = [] |
67 res = [] |
68 for x in self.changes: |
68 for x in self.changes: |
69 type, a, b = x |
69 type, a, b = x |
70 if type == 'notnull': |
70 if type == 'notnull': |
71 type = '' |
71 type = '' |
72 a = self._formatnotnull(a) |
72 a = self._formatnotnull(a) |
73 b = self._formatnotnull(b) |
73 b = self._formatnotnull(b) |
74 |
74 |
75 if a and b: |
75 if a and b: |
76 s = ''.join(['Changed ', type, ' from ', |
76 s = ''.join(['Changed ', type, ' from ', |
77 highlight(1,15), a, highlight(0), ' to ', |
77 highlight(1,15), a, highlight(0), ' to ', |
78 highlight(1,15), b, highlight(0), '.']) |
78 highlight(1,15), b, highlight(0), '.']) |
79 elif a and not b: |
79 elif a and not b: |
88 l += [type, ' '] |
88 l += [type, ' '] |
89 l += [highlight(1,15), b, highlight(0), '.'] |
89 l += [highlight(1,15), b, highlight(0), '.'] |
90 s = ''.join(l) |
90 s = ''.join(l) |
91 res.append(s) |
91 res.append(s) |
92 return ' '.join(res) |
92 return ' '.join(res) |
93 |
93 |
94 def format_patch(self): |
94 def format_patch(self): |
95 if self.change == '*' and self.type in ('schema', 'table'): |
95 if self.change == '*' and self.type in ('schema', 'table'): |
96 return None |
96 return None |
97 return '%s %s %s;' % (self.COMMANDS[self.change], self.type.upper(), self.name) |
97 return ['%s %s %s;' % (self.COMMANDS[self.change], self.type.upper(), self.name)] |
98 |
98 |
99 |
99 |
100 class DiffSchema(DiffBase): |
100 class DiffSchema(DiffBase): |
101 def __init__(self, change, schema): |
101 def __init__(self, change, schema): |
102 DiffBase.__init__(self) |
102 DiffBase.__init__(self) |
135 self.column = column |
135 self.column = column |
136 self.columntype = columntype |
136 self.columntype = columntype |
137 self.columndefault = columndefault |
137 self.columndefault = columndefault |
138 self.name = column |
138 self.name = column |
139 self.changes = changes |
139 self.changes = changes |
140 |
140 |
141 def format_patch(self): |
141 def format_patch(self): |
142 out = 'ALTER TABLE %s.%s %s COLUMN %s %s' % ( |
142 if self.change == '*': |
|
143 type_statement = ' TYPE' |
|
144 else: |
|
145 type_statement = '' |
|
146 if self.columntype is not None: |
|
147 type_statement += ' ' + self.columntype; |
|
148 out = [] |
|
149 out += ['ALTER TABLE %s.%s %s COLUMN %s%s;' % ( |
143 self.schema, |
150 self.schema, |
144 self.table, |
151 self.table, |
145 self.ALTER_COMMANDS[self.change], |
152 self.ALTER_COMMANDS[self.change], |
146 self.name, |
153 self.name, |
147 self.columntype |
154 type_statement |
148 ) |
155 )] |
149 if self.columndefault: |
156 if self.columndefault: |
150 out += ' DEFAULT ' + self.columndefault |
157 out += ['ALTER TABLE %s.%s ALTER COLUMN %s SET DEFAULT %s;' % ( |
151 out += ';' |
158 self.schema, |
|
159 self.table, |
|
160 self.name, |
|
161 self.columndefault |
|
162 )] |
152 return out |
163 return out |
153 |
164 |
154 |
165 |
155 class DiffConstraint(DiffBase): |
166 class DiffConstraint(DiffBase): |
156 def __init__(self, change, schema, table, constraint, changes=None): |
167 def __init__(self, change, schema, table, constraint, definition, changes=None): |
157 DiffBase.__init__(self) |
168 DiffBase.__init__(self) |
158 self.level = 2 |
169 self.level = 2 |
159 self.type = 'constraint' |
170 self.type = 'constraint' |
160 self.change = change |
171 self.change = change |
161 self.schema = schema |
172 self.schema = schema |
162 self.table = table |
173 self.table = table |
163 self.constraint = constraint |
174 self.constraint = constraint |
164 self.name = constraint |
175 self.name = constraint |
|
176 self.definition = definition |
165 self.changes = changes |
177 self.changes = changes |
|
178 |
|
179 def format_patch(self): |
|
180 q_alter = 'ALTER TABLE %s.%s' % (self.schema, self.table) |
|
181 q_drop = '%s DROP CONSTRAINT %s;' % (q_alter, self.constraint) |
|
182 q_add = '%s ADD CONSTRAINT %s %s;' % (q_alter, self.constraint, self.definition) |
|
183 if self.change == '*': |
|
184 out = [q_drop, q_add] |
|
185 if self.change == '+': |
|
186 out = [q_add] |
|
187 if self.change == '-': |
|
188 out = [q_drop] |
|
189 return out |
166 |
190 |
167 |
191 |
168 class PgDiff: |
192 class PgDiff: |
169 def __init__(self, srcbrowser=None, dstbrowser=None): |
193 def __init__(self, srcbrowser=None, dstbrowser=None): |
170 self.allowcolor = False |
194 self.allowcolor = False |
172 self.dst = dstbrowser |
196 self.dst = dstbrowser |
173 self.include_schemas = set() # if not empty, consider only these schemas for diff |
197 self.include_schemas = set() # if not empty, consider only these schemas for diff |
174 self.exclude_schemas = set() # exclude these schemas from diff |
198 self.exclude_schemas = set() # exclude these schemas from diff |
175 self.include_tables = set() |
199 self.include_tables = set() |
176 self.exclude_tables = set() |
200 self.exclude_tables = set() |
177 |
201 |
178 def _test_schema(self, schema): |
202 def _test_schema(self, schema): |
179 if self.include_schemas and schema not in self.include_schemas: |
203 if self.include_schemas and schema not in self.include_schemas: |
180 return False |
204 return False |
181 if schema in self.exclude_schemas: |
205 if schema in self.exclude_schemas: |
182 return False |
206 return False |
183 return True |
207 return True |
184 |
208 |
185 def _test_table(self, table): |
209 def _test_table(self, table): |
186 if self.include_tables and table not in self.include_tables: |
210 if self.include_tables and table not in self.include_tables: |
187 return False |
211 return False |
188 if table in self.exclude_tables: |
212 if table in self.exclude_tables: |
189 return False |
213 return False |
190 return True |
214 return True |
191 |
215 |
192 def _diff_names(self, src, dst): |
216 def _diff_names(self, src, dst): |
193 for x in src: |
217 for x in src: |
194 if x in dst: |
218 if x in dst: |
195 yield ('*', x) |
219 yield ('*', x) |
196 else: |
220 else: |
206 if a.notnull != b.notnull: |
230 if a.notnull != b.notnull: |
207 diff.append(('notnull', a.notnull, b.notnull)) |
231 diff.append(('notnull', a.notnull, b.notnull)) |
208 if a.default != b.default: |
232 if a.default != b.default: |
209 diff.append(('default', a.default, b.default)) |
233 diff.append(('default', a.default, b.default)) |
210 return diff |
234 return diff |
211 |
235 |
212 def _compare_constraints(self, a, b): |
236 def _compare_constraints(self, a, b): |
213 diff = [] |
237 diff = [] |
214 if a.type != b.type: |
238 if a.type != b.type: |
215 diff.append(('type', a.type, b.type)) |
239 diff.append(('type', a.type, b.type)) |
216 if a.definition != b.definition: |
240 if a.definition != b.definition: |
217 diff.append(('definition', a.definition, b.definition)) |
241 diff.append(('definition', a.definition, b.definition)) |
218 return diff |
242 return diff |
219 |
243 |
220 def _diff_columns(self, schema, table, src_columns, dst_columns): |
244 def _diff_columns(self, schema, table, src_columns, dst_columns): |
221 for nd in self._diff_names(src_columns, dst_columns): |
245 for nd in self._diff_names(src_columns, dst_columns): |
|
246 if nd[1] in dst_columns: |
|
247 dst_type = dst_columns[nd[1]].type |
|
248 dst_default = dst_columns[nd[1]].default |
|
249 else: |
|
250 dst_type = None |
|
251 dst_default = None |
222 cdo = DiffColumn(change=nd[0], schema=schema, table=table, column=nd[1], |
252 cdo = DiffColumn(change=nd[0], schema=schema, table=table, column=nd[1], |
223 columntype=dst_columns[nd[1]].type, columndefault=dst_columns[nd[1]].default) |
253 columntype=dst_type, columndefault=dst_default) |
224 if nd[0] == '*': |
254 if nd[0] == '*': |
225 a = src_columns[nd[1]] |
255 a = src_columns[nd[1]] |
226 b = dst_columns[nd[1]] |
256 b = dst_columns[nd[1]] |
227 cdo.changes = self._compare_columns(a, b) |
257 cdo.changes = self._compare_columns(a, b) |
228 if cdo.changes: |
258 if cdo.changes: |
230 else: |
260 else: |
231 yield cdo |
261 yield cdo |
232 |
262 |
233 def _diff_constraints(self, schema, table, src_constraints, dst_constraints): |
263 def _diff_constraints(self, schema, table, src_constraints, dst_constraints): |
234 for nd in self._diff_names(src_constraints, dst_constraints): |
264 for nd in self._diff_names(src_constraints, dst_constraints): |
235 cdo = DiffConstraint(change=nd[0], schema=schema, table=table, constraint=nd[1]) |
265 if nd[1] in dst_constraints: |
|
266 dst_definition = dst_constraints[nd[1]].definition |
|
267 else: |
|
268 dst_definition = None |
|
269 cdo = DiffConstraint(change=nd[0], schema=schema, table=table, constraint=nd[1], |
|
270 definition=dst_definition) |
236 if nd[0] == '*': |
271 if nd[0] == '*': |
237 a = src_constraints[nd[1]] |
272 a = src_constraints[nd[1]] |
238 b = dst_constraints[nd[1]] |
273 b = dst_constraints[nd[1]] |
239 cdo.changes = self._compare_constraints(a, b) |
274 cdo.changes = self._compare_constraints(a, b) |
240 if cdo.changes: |
275 if cdo.changes: |
267 else: |
302 else: |
268 yield tdo |
303 yield tdo |
269 |
304 |
270 def iter_diff(self): |
305 def iter_diff(self): |
271 '''Return diff between src and dst database schema. |
306 '''Return diff between src and dst database schema. |
272 |
307 |
273 Yields one line at the time. Each line is in form of object |
308 Yields one line at the time. Each line is in form of object |
274 iherited from DiffBase. This object contains all information |
309 iherited from DiffBase. This object contains all information |
275 about changes. See format() method. |
310 about changes. See format() method. |
276 |
311 |
277 ''' |
312 ''' |
278 src_schemas = self.src.schemas |
313 src_schemas = self.src.schemas |
279 dst_schemas = self.dst.schemas |
314 dst_schemas = self.dst.schemas |
280 src = [x.name for x in src_schemas.values() if not x.system and self._test_schema(x.name)] |
315 src = [x.name for x in src_schemas.values() if not x.system and self._test_schema(x.name)] |
281 dst = [x.name for x in dst_schemas.values() if not x.system and self._test_schema(x.name)] |
316 dst = [x.name for x in dst_schemas.values() if not x.system and self._test_schema(x.name)] |
292 else: |
327 else: |
293 yield sdo |
328 yield sdo |
294 |
329 |
295 def print_diff(self): |
330 def print_diff(self): |
296 '''Print diff between src and dst database schema. |
331 '''Print diff between src and dst database schema. |
297 |
332 |
298 The output is in human readable form. |
333 The output is in human readable form. |
299 |
334 |
300 Set allowcolor=True of PgDiff instance to get colored output. |
335 Set allowcolor=True of PgDiff instance to get colored output. |
301 |
336 |
302 ''' |
337 ''' |
303 for ln in self.iter_diff(): |
338 for ln in self.iter_diff(): |
304 print(ln.format()) |
339 print(ln.format()) |
305 |
340 |
306 def print_patch(self): |
341 def print_patch(self): |
307 '''Print patch for updating from src schema to dst schema. |
342 '''Print patch for updating from src schema to dst schema. |
308 |
343 |
309 Supports table drop, add, column drop, add and following |
344 Supports table drop, add, column drop, add and following |
310 changes of columns: |
345 changes of columns: |
311 - type |
346 - type |
312 - set/remove not null |
347 - set/remove not null |
313 - default value |
348 - default value |
314 |
349 |
315 This is experimental, not tested very much. |
350 This is experimental, not tested very much. |
316 Do not use without checking the commands. |
351 Do not use without checking the commands. |
317 Even if it works as intended, it can cause table lock ups |
352 Even if it works as intended, it can cause table lock ups |
318 and/or loss of data. You have been warned. |
353 and/or loss of data. You have been warned. |
319 |
354 |
320 ''' |
355 ''' |
321 for ln in self.iter_diff(): |
356 for ln in self.iter_diff(): |
322 patch = ln.format_patch() |
357 patch = ln.format_patch() |
323 if patch: |
358 if patch: |
324 print(patch) |
359 print('\n'.join(patch)) |
325 |
360 |
326 def filter_schemas(self, include=[], exclude=[]): |
361 def filter_schemas(self, include=[], exclude=[]): |
327 '''Modify list of schemas which are used for computing diff. |
362 '''Modify list of schemas which are used for computing diff. |
328 |
363 |
329 include (list) -- if not empty, consider only these schemas for diff |
364 include (list) -- if not empty, consider only these schemas for diff |
330 exclude (list) -- exclude these schemas from diff |
365 exclude (list) -- exclude these schemas from diff |
331 |
366 |
332 Order: include, exclude |
367 Order: include, exclude |
333 include=[] means include everything |
368 include=[] means include everything |
334 ''' |
369 ''' |
335 self.include_schemas.clear() |
370 self.include_schemas.clear() |
336 self.include_schemas.update(include) |
371 self.include_schemas.update(include) |