Databases

The database module defines class Database.

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 CatDbConnection object (a specialized class derived from DbConnection). It consists of one or two Dicts (the main branches in the above discussion). A Dicts object holds various dictionary objects derived from DbObjectDict, e.g., SchemaDict, ClassDict, and 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 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 Sequence, Table and View for details on the lower level dictionaries.

class pyrseas.database.Database(config)

A database definition, from its catalogs and/or a YAML spec.

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

Database.from_catalog(single_db=False)

Populate the database objects by querying the catalogs

Parameters:single_db – populating only this database?

The db holder is populated by various DbObjectDict-derived classes by querying the catalogs. A dependency graph is constructed by querying the pg_depend catalog. The objects in the dictionary are then linked to related objects, e.g., columns are linked to the tables they belong.

Database.from_map(input_map, langs=None)

Populate the new database objects from the input map

Parameters:

• input_map – a YAML map defining the new database
• langs – list of language templates

The ndb holder is populated by various DbObjectDict-derived classes by traversing the YAML input map. The objects in the dictionary are then linked to related objects, e.g., columns are linked to the tables they belong.

Database.map_from_dir()

Read the database maps starting from the metadata directory

Returns:dictionary

Database.to_map(quote_reserved=True)

Convert the db maps to a single hierarchy suitable for YAML

Parameters:quote_reserved – fetch reserved words Returns:a YAML-suitable dictionary (without any Python objects)

Database.diff_map(input_map, quote_reserved=True)

Generate SQL to transform an existing database

Parameters:

• input_map – a YAML map defining the new database
• quote_reserved – fetch reserved words

Returns:

list of SQL statements

Compares the existing database definition, as fetched from the catalogs, to the input YAML map and generates SQL statements to transform the database into the one represented by the input.