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.