def _from_catalog(self): """Initialize the dictionary of constraints by querying the catalogs""" for constr in self.fetch(): constr.unqualify() sch, tbl, cns = constr.key() constr_type = constr.type del constr.type if constr_type != 'f': del constr.ref_table del constr.on_update del constr.on_delete if constr_type == 'c': self[(sch, tbl, cns)] = CheckConstraint(**constr.__dict__) elif constr_type == 'p': self[(sch, tbl, cns)] = PrimaryKey(**constr.__dict__) elif constr_type == 'f': # normalize reference schema/table: # if reftbl is qualified, split the schema out, # otherwise it's in the 'public' schema (set as default # when connecting) if constr.on_update == 'a': del constr.on_update else: constr.on_update = ACTIONS[constr.on_update] if constr.on_delete == 'a': del constr.on_delete else: constr.on_delete = ACTIONS[constr.on_delete] reftbl = constr.ref_table (constr.ref_schema, constr.ref_table) = split_schema_table( reftbl) self[(sch, tbl, cns)] = ForeignKey(**constr.__dict__) elif constr_type == 'u': self[(sch, tbl, cns)] = UniqueConstraint(**constr.__dict__)
def get_dependent_table(self, dbconn): """Get the table and column name that uses or owns the sequence :param dbconn: a DbConnection object """ data = dbconn.fetchone( """SELECT refobjid::regclass, refobjsubid FROM pg_depend WHERE objid = '%s'::regclass AND refclassid = 'pg_class'::regclass""" % self.qualname()) if data: (sch, self.owner_table) = split_schema_table(data[0], self.schema) self.owner_column = data[1] return data = dbconn.fetchone( """SELECT adrelid::regclass FROM pg_attrdef a JOIN pg_depend ON (a.oid = objid) WHERE refobjid = '%s'::regclass AND classid = 'pg_attrdef'::regclass""" % self.qualname()) if data: (sch, self.dependent_table) = split_schema_table( data[0], self.schema)
def _from_catalog(self): """Initialize the dictionary of tables by querying the catalogs""" for table in self.fetch(): sch, tbl = table.key() kind = table.kind del table.kind if kind == 'r': self[(sch, tbl)] = Table(**table.__dict__) elif kind == 'S': self[(sch, tbl)] = inst = Sequence(**table.__dict__) inst.get_attrs(self.dbconn) inst.get_dependent_table(self.dbconn) elif kind == 'v': self[(sch, tbl)] = View(**table.__dict__) for (tbl, partbl, num) in self.dbconn.fetchall(self.inhquery): (sch, tbl) = split_schema_table(tbl) table = self[(sch, tbl)] if not hasattr(table, 'inherits'): table.inherits = [] table.inherits.append(partbl)
def diff_map(self, intables): """Generate SQL to transform existing tables and sequences :param intables: a YAML map defining the new tables/sequences :return: list of SQL statements Compares the existing table/sequence definitions, as fetched from the catalogs, to the input map and generates SQL statements to transform the tables/sequences accordingly. """ stmts = [] # first pass: sequences owned by a table for (sch, seq) in intables.keys(): inseq = intables[(sch, seq)] if not isinstance(inseq, Sequence) or \ not hasattr(inseq, 'owner_table'): continue if (sch, seq) not in self: if hasattr(inseq, 'oldname'): stmts.append(self._rename(inseq, "sequence")) else: # create new sequence stmts.append(inseq.create()) # check input tables inhstack = [] for (sch, tbl) in intables.keys(): intable = intables[(sch, tbl)] if not isinstance(intable, Table): continue # does it exist in the database? if (sch, tbl) not in self: if not hasattr(intable, 'oldname'): # create new table if hasattr(intable, 'inherits'): inhstack.append(intable) else: stmts.append(intable.create()) else: stmts.append(self._rename(intable, "table")) while len(inhstack): intable = inhstack.pop() createit = True for partbl in intable.inherits: if intables[split_schema_table(partbl)] in inhstack: createit = False if createit: stmts.append(intable.create()) else: inhstack.insert(0, intable) # check input views for (sch, tbl) in intables.keys(): intable = intables[(sch, tbl)] if not isinstance(intable, View): continue # does it exist in the database? if (sch, tbl) not in self: if hasattr(intable, 'oldname'): stmts.append(self._rename(intable, "view")) else: # create new view stmts.append(intable.create()) # second pass: input sequences not owned by tables for (sch, seq) in intables.keys(): inseq = intables[(sch, seq)] if not isinstance(inseq, Sequence): continue # does it exist in the database? if (sch, seq) not in self: if hasattr(inseq, 'oldname'): stmts.append(self._rename(inseq, "sequence")) elif hasattr(inseq, 'owner_table'): stmts.append(inseq.add_owner()) else: # create new sequence stmts.append(inseq.create()) # check database tables, sequences and views for (sch, tbl) in self.keys(): table = self[(sch, tbl)] # if missing, mark it for dropping if (sch, tbl) not in intables: table.dropped = False else: # check table/sequence/view objects stmts.append(table.diff_map(intables[(sch, tbl)])) # now drop the marked tables for (sch, tbl) in self.keys(): table = self[(sch, tbl)] if isinstance(table, Sequence) and hasattr(table, 'owner_table'): continue if hasattr(table, 'dropped') and not table.dropped: # first, drop all foreign keys if hasattr(table, 'foreign_keys'): for fgn in table.foreign_keys: stmts.append(table.foreign_keys[fgn].drop()) # and drop the triggers if hasattr(table, 'triggers'): for trg in table.triggers: stmts.append(table.triggers[trg].drop()) if hasattr(table, 'rules'): for rul in table.rules: stmts.append(table.rules[rul].drop()) # drop views if isinstance(table, View): stmts.append(table.drop()) inhstack = [] for (sch, tbl) in self.keys(): table = self[(sch, tbl)] if (isinstance(table, Sequence) \ and (hasattr(table, 'owner_table') \ or hasattr(table, 'dependent_table'))) \ or isinstance(table, View): continue if hasattr(table, 'dropped') and not table.dropped: # next, drop other subordinate objects if hasattr(table, 'check_constraints'): for chk in table.check_constraints: stmts.append(table.check_constraints[chk].drop()) if hasattr(table, 'unique_constraints'): for unq in table.unique_constraints: stmts.append(table.unique_constraints[unq].drop()) if hasattr(table, 'indexes'): for idx in table.indexes: stmts.append(table.indexes[idx].drop()) if hasattr(table, 'rules'): for rul in table.rules: stmts.append(table.rules[rul].drop()) if hasattr(table, 'primary_key'): # TODO there can be more than one referred_by if hasattr(table, 'referred_by'): stmts.append(table.referred_by.drop()) stmts.append(table.primary_key.drop()) # finally, drop the table itself if hasattr(table, 'descendants'): inhstack.append(table) else: stmts.append(table.drop()) while len(inhstack): table = inhstack.pop() dropit = True for childtbl in table.descendants: if self[(childtbl.schema, childtbl.name)] in inhstack: dropit = False if dropit: stmts.append(table.drop()) else: inhstack.insert(0, table) for (sch, tbl) in self.keys(): table = self[(sch, tbl)] if isinstance(table, Sequence) \ and hasattr(table, 'dependent_table') \ and hasattr(table, 'dropped') and not table.dropped: stmts.append(table.drop()) # last pass to deal with nextval DEFAULTs for (sch, tbl) in intables.keys(): intable = intables[(sch, tbl)] if not isinstance(intable, Table): continue if (sch, tbl) not in self: for col in intable.columns: if hasattr(col, 'default') \ and col.default.startswith('nextval'): stmts.append(col.set_sequence_default()) return stmts
def link_refs(self, dbcolumns, dbconstrs, dbindexes, dbrules, dbtriggers): """Connect columns, constraints, etc. to their respective tables :param dbcolumns: dictionary of columns :param dbconstrs: dictionary of constraints :param dbindexes: dictionary of indexes :param dbrules: dictionary of rules :param dbtriggers: dictionary of triggers Links each list of table columns in `dbcolumns` to the corresponding table. Fills the `foreign_keys`, `unique_constraints`, `indexes` and `triggers` dictionaries for each table by traversing the `dbconstrs`, `dbindexes` and `dbtriggers` dictionaries, which are keyed by schema, table and constraint, index or trigger name. """ for (sch, tbl) in dbcolumns.keys(): if (sch, tbl) in self: assert isinstance(self[(sch, tbl)], Table) self[(sch, tbl)].columns = dbcolumns[(sch, tbl)] for col in dbcolumns[(sch, tbl)]: col._table = self[(sch, tbl)] for (sch, tbl) in self.keys(): table = self[(sch, tbl)] if isinstance(table, Sequence) and hasattr(table, 'owner_table'): if isinstance(table.owner_column, int): table.owner_column = self[(sch, table.owner_table)]. \ column_names()[table.owner_column - 1] elif isinstance(table, Table) and hasattr(table, 'inherits'): for partbl in table.inherits: (parsch, partbl) = split_schema_table(partbl) assert self[(parsch, partbl)] parent = self[(parsch, partbl)] if not hasattr(parent, 'descendants'): parent.descendants = [] parent.descendants.append(table) for (sch, tbl, cns) in dbconstrs.keys(): constr = dbconstrs[(sch, tbl, cns)] if hasattr(constr, 'target'): continue assert self[(sch, tbl)] table = self[(sch, tbl)] if isinstance(constr, CheckConstraint): if not hasattr(table, 'check_constraints'): table.check_constraints = {} table.check_constraints.update({cns: constr}) elif isinstance(constr, PrimaryKey): table.primary_key = constr elif isinstance(constr, ForeignKey): if not hasattr(table, 'foreign_keys'): table.foreign_keys = {} # link referenced and referrer constr.references = self[(constr.ref_schema, constr.ref_table)] # TODO: there can be more than one self[(constr.ref_schema, constr.ref_table)].referred_by = \ constr table.foreign_keys.update({cns: constr}) elif isinstance(constr, UniqueConstraint): if not hasattr(table, 'unique_constraints'): table.unique_constraints = {} table.unique_constraints.update({cns: constr}) for (sch, tbl, idx) in dbindexes.keys(): assert self[(sch, tbl)] table = self[(sch, tbl)] if not hasattr(table, 'indexes'): table.indexes = {} table.indexes.update({idx: dbindexes[(sch, tbl, idx)]}) for (sch, tbl, rul) in dbrules.keys(): assert self[(sch, tbl)] table = self[(sch, tbl)] if not hasattr(table, 'rules'): table.rules = {} table.rules.update({rul: dbrules[(sch, tbl, rul)]}) dbrules[(sch, tbl, rul)]._table = self[(sch, tbl)] for (sch, tbl, trg) in dbtriggers.keys(): assert self[(sch, tbl)] table = self[(sch, tbl)] if not hasattr(table, 'triggers'): table.triggers = {} table.triggers.update({trg: dbtriggers[(sch, tbl, trg)]}) dbtriggers[(sch, tbl, trg)]._table = self[(sch, tbl)]
def link_refs(self, dbtypes, dbtables, dbfunctions, dbopers, dbconvs): """Connect types, tables and functions to their respective schemas :param dbtypes: dictionary of types and domains :param dbtables: dictionary of tables, sequences and views :param dbfunctions: dictionary of functions :param dbopers: dictionary of operators :param dbconvs: dictionary of conversions Fills in the `domains` dictionary for each schema by traversing the `dbtypes` dictionary. Fills in the `tables`, `sequences`, `views` dictionaries for each schema by traversing the `dbtables` dictionary. Fills in the `functions` dictionary by traversing the `dbfunctions` dictionary. """ for (sch, typ) in dbtypes.keys(): dbtype = dbtypes[(sch, typ)] assert self[sch] schema = self[sch] if isinstance(dbtype, Domain): if not hasattr(schema, "domains"): schema.domains = {} schema.domains.update({typ: dbtypes[(sch, typ)]}) elif isinstance(dbtype, Enum) or isinstance(dbtype, Composite): if not hasattr(schema, "types"): schema.types = {} schema.types.update({typ: dbtypes[(sch, typ)]}) for (sch, tbl) in dbtables.keys(): table = dbtables[(sch, tbl)] assert self[sch] schema = self[sch] if isinstance(table, Table): if not hasattr(schema, "tables"): schema.tables = {} schema.tables.update({tbl: table}) elif isinstance(table, Sequence): if not hasattr(schema, "sequences"): schema.sequences = {} schema.sequences.update({tbl: table}) elif isinstance(table, View): if not hasattr(schema, "views"): schema.views = {} schema.views.update({tbl: table}) for (sch, fnc, arg) in dbfunctions.keys(): func = dbfunctions[(sch, fnc, arg)] assert self[sch] schema = self[sch] if not hasattr(schema, "functions"): schema.functions = {} schema.functions.update({(fnc, arg): func}) if hasattr(func, "returns"): rettype = func.returns if rettype.upper().startswith("SETOF "): rettype = rettype[6:] (retsch, rettyp) = split_schema_table(rettype, sch) if (retsch, rettyp) in dbtables.keys(): deptbl = dbtables[(retsch, rettyp)] if not hasattr(func, "dependent_table"): func.dependent_table = deptbl if not hasattr(deptbl, "dependent_funcs"): deptbl.dependent_funcs = [] deptbl.dependent_funcs.append(func) for (sch, opr, lft, rgt) in dbopers.keys(): oper = dbopers[(sch, opr, lft, rgt)] assert self[sch] schema = self[sch] if not hasattr(schema, "operators"): schema.operators = {} schema.operators.update({(opr, lft, rgt): oper}) for (sch, cnv) in dbconvs.keys(): conv = dbconvs[(sch, cnv)] assert self[sch] schema = self[sch] if not hasattr(schema, "conversions"): schema.conversions = {} schema.conversions.update({cnv: conv})