[3] | 1 | """ |
---|
| 2 | Schema differencing support. |
---|
| 3 | """ |
---|
| 4 | import sqlalchemy |
---|
| 5 | |
---|
| 6 | |
---|
| 7 | def getDiffOfModelAgainstDatabase(model, conn, excludeTables=None): |
---|
| 8 | """ |
---|
| 9 | Return differences of model against database. |
---|
| 10 | |
---|
| 11 | :return: object which will evaluate to :keyword:`True` if there \ |
---|
| 12 | are differences else :keyword:`False`. |
---|
| 13 | """ |
---|
| 14 | return SchemaDiff(model, conn, excludeTables) |
---|
| 15 | |
---|
| 16 | |
---|
| 17 | def getDiffOfModelAgainstModel(oldmodel, model, conn, excludeTables=None): |
---|
| 18 | """ |
---|
| 19 | Return differences of model against another model. |
---|
| 20 | |
---|
| 21 | :return: object which will evaluate to :keyword:`True` if there \ |
---|
| 22 | are differences else :keyword:`False`. |
---|
| 23 | """ |
---|
| 24 | return SchemaDiff(model, conn, excludeTables, oldmodel=oldmodel) |
---|
| 25 | |
---|
| 26 | |
---|
| 27 | class SchemaDiff(object): |
---|
| 28 | """ |
---|
| 29 | Differences of model against database. |
---|
| 30 | """ |
---|
| 31 | |
---|
| 32 | def __init__(self, model, conn, excludeTables=None, oldmodel=None): |
---|
| 33 | """ |
---|
| 34 | :param model: Python model's metadata |
---|
| 35 | :param conn: active database connection. |
---|
| 36 | """ |
---|
| 37 | self.model = model |
---|
| 38 | self.conn = conn |
---|
| 39 | if not excludeTables: |
---|
| 40 | # [] can't be default value in Python parameter |
---|
| 41 | excludeTables = [] |
---|
| 42 | self.excludeTables = excludeTables |
---|
| 43 | if oldmodel: |
---|
| 44 | self.reflected_model = oldmodel |
---|
| 45 | else: |
---|
| 46 | self.reflected_model = sqlalchemy.MetaData(conn, reflect=True) |
---|
| 47 | self.tablesMissingInDatabase, self.tablesMissingInModel, \ |
---|
| 48 | self.tablesWithDiff = [], [], [] |
---|
| 49 | self.colDiffs = {} |
---|
| 50 | self.compareModelToDatabase() |
---|
| 51 | |
---|
| 52 | def compareModelToDatabase(self): |
---|
| 53 | """ |
---|
| 54 | Do actual comparison. |
---|
| 55 | """ |
---|
| 56 | # Setup common variables. |
---|
| 57 | cc = self.conn.contextual_connect() |
---|
| 58 | schemagenerator = self.conn.dialect.schemagenerator( |
---|
| 59 | self.conn.dialect, cc) |
---|
| 60 | |
---|
| 61 | # For each in model, find missing in database. |
---|
| 62 | for modelName, modelTable in self.model.tables.items(): |
---|
| 63 | if modelName in self.excludeTables: |
---|
| 64 | continue |
---|
| 65 | reflectedTable = self.reflected_model.tables.get(modelName, None) |
---|
| 66 | if reflectedTable: |
---|
| 67 | # Table exists. |
---|
| 68 | pass |
---|
| 69 | else: |
---|
| 70 | self.tablesMissingInDatabase.append(modelTable) |
---|
| 71 | |
---|
| 72 | # For each in database, find missing in model. |
---|
| 73 | for reflectedName, reflectedTable in \ |
---|
| 74 | self.reflected_model.tables.items(): |
---|
| 75 | if reflectedName in self.excludeTables: |
---|
| 76 | continue |
---|
| 77 | modelTable = self.model.tables.get(reflectedName, None) |
---|
| 78 | if modelTable: |
---|
| 79 | # Table exists. |
---|
| 80 | |
---|
| 81 | # Find missing columns in database. |
---|
| 82 | for modelCol in modelTable.columns: |
---|
| 83 | databaseCol = reflectedTable.columns.get(modelCol.name, |
---|
| 84 | None) |
---|
| 85 | if databaseCol: |
---|
| 86 | pass |
---|
| 87 | else: |
---|
| 88 | self.storeColumnMissingInDatabase(modelTable, modelCol) |
---|
| 89 | |
---|
| 90 | # Find missing columns in model. |
---|
| 91 | for databaseCol in reflectedTable.columns: |
---|
| 92 | modelCol = modelTable.columns.get(databaseCol.name, None) |
---|
| 93 | if modelCol: |
---|
| 94 | # Compare attributes of column. |
---|
| 95 | modelDecl = \ |
---|
| 96 | schemagenerator.get_column_specification( |
---|
| 97 | modelCol) |
---|
| 98 | databaseDecl = \ |
---|
| 99 | schemagenerator.get_column_specification( |
---|
| 100 | databaseCol) |
---|
| 101 | if modelDecl != databaseDecl: |
---|
| 102 | # Unfortunately, sometimes the database |
---|
| 103 | # decl won't quite match the model, even |
---|
| 104 | # though they're the same. |
---|
| 105 | mc, dc = modelCol.type.__class__, \ |
---|
| 106 | databaseCol.type.__class__ |
---|
| 107 | if (issubclass(mc, dc) \ |
---|
| 108 | or issubclass(dc, mc)) \ |
---|
| 109 | and modelCol.nullable == \ |
---|
| 110 | databaseCol.nullable: |
---|
| 111 | # Types and nullable are the same. |
---|
| 112 | pass |
---|
| 113 | else: |
---|
| 114 | self.storeColumnDiff( |
---|
| 115 | modelTable, modelCol, databaseCol, |
---|
| 116 | modelDecl, databaseDecl) |
---|
| 117 | else: |
---|
| 118 | self.storeColumnMissingInModel(modelTable, databaseCol) |
---|
| 119 | else: |
---|
| 120 | self.tablesMissingInModel.append(reflectedTable) |
---|
| 121 | |
---|
| 122 | def __str__(self): |
---|
| 123 | ''' Summarize differences. ''' |
---|
| 124 | |
---|
| 125 | def colDiffDetails(): |
---|
| 126 | colout = [] |
---|
| 127 | for table in self.tablesWithDiff: |
---|
| 128 | tableName = table.name |
---|
| 129 | missingInDatabase, missingInModel, diffDecl = \ |
---|
| 130 | self.colDiffs[tableName] |
---|
| 131 | if missingInDatabase: |
---|
| 132 | colout.append( |
---|
| 133 | ' %s missing columns in database: %s' % \ |
---|
| 134 | (tableName, ', '.join( |
---|
| 135 | [col.name for col in missingInDatabase]))) |
---|
| 136 | if missingInModel: |
---|
| 137 | colout.append( |
---|
| 138 | ' %s missing columns in model: %s' % \ |
---|
| 139 | (tableName, ', '.join( |
---|
| 140 | [col.name for col in missingInModel]))) |
---|
| 141 | if diffDecl: |
---|
| 142 | colout.append( |
---|
| 143 | ' %s with different declaration of columns\ |
---|
| 144 | in database: %s' % (tableName, str(diffDecl))) |
---|
| 145 | return colout |
---|
| 146 | |
---|
| 147 | out = [] |
---|
| 148 | if self.tablesMissingInDatabase: |
---|
| 149 | out.append( |
---|
| 150 | ' tables missing in database: %s' % \ |
---|
| 151 | ', '.join( |
---|
| 152 | [table.name for table in self.tablesMissingInDatabase])) |
---|
| 153 | if self.tablesMissingInModel: |
---|
| 154 | out.append( |
---|
| 155 | ' tables missing in model: %s' % \ |
---|
| 156 | ', '.join( |
---|
| 157 | [table.name for table in self.tablesMissingInModel])) |
---|
| 158 | if self.tablesWithDiff: |
---|
| 159 | out.append( |
---|
| 160 | ' tables with differences: %s' % \ |
---|
| 161 | ', '.join([table.name for table in self.tablesWithDiff])) |
---|
| 162 | |
---|
| 163 | if out: |
---|
| 164 | out.insert(0, 'Schema diffs:') |
---|
| 165 | out.extend(colDiffDetails()) |
---|
| 166 | return '\n'.join(out) |
---|
| 167 | else: |
---|
| 168 | return 'No schema diffs' |
---|
| 169 | |
---|
| 170 | def __len__(self): |
---|
| 171 | """ |
---|
| 172 | Used in bool evaluation, return of 0 means no diffs. |
---|
| 173 | """ |
---|
| 174 | return len(self.tablesMissingInDatabase) + \ |
---|
| 175 | len(self.tablesMissingInModel) + len(self.tablesWithDiff) |
---|
| 176 | |
---|
| 177 | def storeColumnMissingInDatabase(self, table, col): |
---|
| 178 | if table not in self.tablesWithDiff: |
---|
| 179 | self.tablesWithDiff.append(table) |
---|
| 180 | missingInDatabase, missingInModel, diffDecl = \ |
---|
| 181 | self.colDiffs.setdefault(table.name, ([], [], [])) |
---|
| 182 | missingInDatabase.append(col) |
---|
| 183 | |
---|
| 184 | def storeColumnMissingInModel(self, table, col): |
---|
| 185 | if table not in self.tablesWithDiff: |
---|
| 186 | self.tablesWithDiff.append(table) |
---|
| 187 | missingInDatabase, missingInModel, diffDecl = \ |
---|
| 188 | self.colDiffs.setdefault(table.name, ([], [], [])) |
---|
| 189 | missingInModel.append(col) |
---|
| 190 | |
---|
| 191 | def storeColumnDiff(self, table, modelCol, databaseCol, modelDecl, |
---|
| 192 | databaseDecl): |
---|
| 193 | if table not in self.tablesWithDiff: |
---|
| 194 | self.tablesWithDiff.append(table) |
---|
| 195 | missingInDatabase, missingInModel, diffDecl = \ |
---|
| 196 | self.colDiffs.setdefault(table.name, ([], [], [])) |
---|
| 197 | diffDecl.append((modelCol, databaseCol, modelDecl, databaseDecl)) |
---|