Esempio n. 1
0
 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__)
Esempio n. 2
0
    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)
Esempio n. 3
0
 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)
Esempio n. 4
0
    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
Esempio n. 5
0
    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)]
Esempio n. 6
0
    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})