module: pyrseas.database

The database module defines class Database.


A Database can be viewed as a tree of database objects. The tree may have one or two main branches. A tree with one main branch is used by dbtoyaml to hold the representation of the database, as read from the Postgres catalogs. yamltodb uses a second main branch to hold the representation as read from the YAML input specification.

Each main branch consists of multiple subtrees for different kinds of objects. For example, the Schemas (Postgres namespaces) subtree has all the Postgres schema objects, the Procedures subtree has all the Postgres functions and aggregates. The objects in the subtrees are connected in implicit or explicit manners to related objects. For example, the objects in the schema public are implicitly accessible from the corresponding Schema object because they all share public as the first part of their internal key (see DbObject.key). As another example, a table has explicit links to constraints and indexes defined on it.

A Database is initialized from a ~pyrseas.database.CatDbConnection object (a specialized class derived from ~pyrseas.lib.dbconn.DbConnection). It consists of one or two Dicts (the main branches in the above discussion). A Dicts object holds various dictionary objects derived from ~pyrseas.dbobject.DbObjectDict, e.g., ~pyrseas.dbobject.schema.SchemaDict, ~pyrseas.dbobject.table.ClassDict, and ~pyrseas.dbobject.column.ColumnDict. The key for each dictionary is a Python tuple (or a single value in the case of SchemaDict and other non-schema objects). For example, the ~pyrseas.dbobject.table.ClassDict dictionary is indexed by (schema name, table name)--in this context table name may actually be a sequence name, a view name or a materialized view name. In addition, object instances in each dictionary are linked to related objects in other dictionaries, e.g., columns are linked to the tables where they belong.

The db Dicts object --always present-- instantiates the database schemas, including their tables and other objects, by querying the system catalogs. The ndb Dicts object instantiates the schemas based on the input_map supplied to the diff_map method.

The to_map method returns and the diff_map method takes as input, a Python dictionary (equivalent to a YAML or JSON object) as shown below. It uses 'schema schema_name' as the key for each schema. The value corresponding to each 'schema schema_name' is another dictionary using 'sequences', 'tables', etc., as keys and more dictionaries as values. For example:

{'schema public':
    {'sequence seq1': { ... },
     'sequence seq2': { ... },
     'table t1': { ... },
     'table t2': { ... },
     'table t3': { ... },
     'view v1': { ... }
 'schema s1': { ... },
 'schema s2': { ... }

Refer to ~pyrseas.dbobject.table.Sequence, ~pyrseas.dbobject.table.Table and ~pyrseas.dbobject.table.View for details on the lower level dictionaries.

autoclass: Database

Methods from_catalog and from_map are for internal use. Methods to_map and diff_map are the external API.

automethod: Database.from_catalog

automethod: Database.from_map

automethod: Database.map_from_dir

automethod: Database.to_map

automethod: Database.diff_map