Database Objects

The dbobject module defines two low-level classes and an intermediate class. Most Pyrseas classes are derived from either DbObject or DbObjectDict.

Database Object

A DbObject represents a database object such as a schema, table, or column, defined in a Postgres system catalog. It is initialized from a dictionary of attributes. Derived classes should define a keylist that is a list of attribute names that uniquely identify each object instance within the database.

class pyrseas.dbobject.DbObject(name, description=None, **attrs)

A single object in a database catalog, e.g., a schema, a table, a column

DbObject.objtype

Type of object as an uppercase string, for SQL syntax generation

This is used in most CREATE, ALTER and DROP statements. It is also used by extern_key() in lowercase form.

DbObject.keylist = ['name']

List of attributes that uniquely identify the object in the catalogs

See description of key() for further details.

DbObject.key()

Return a tuple that identifies the database object

Returns:a single string or a tuple of strings

This is used as key for all internal maps. The first-level objects (schemas, languages and casts) use the object name as the key. Second-level (schema-owned) objects usually use the schema name and the object name as the key. Some object types need longer keys, e.g., operators need schema name, operator symbols, left argument and right argument.

Each class implementing an object type specifies a keylist attribute, i.e., a list giving the names of attributes making up the key.

The following methods are generally used to map objects for external output:

DbObject.extern_key()

Return the key to be used in external maps for this object

Returns:string

This is used for the first two levels of external maps. The first level is the one that includes schemas, as well as extensions, languages, casts and foreign data wrappers. The second level includes all schema-owned objects, i.e., tables, functions, operators, etc. All subsequent levels, e.g., primary keys, indexes, etc., currently use the object name as the external identifier, appearing in the map after an object grouping header, such as primary_key.

The common format for an external key is object-type non-schema-qualified-name, where object-type is the lowercase version of objtype, e.g., table tablename. Some object types require more, e.g., functions need the signature, so they override this implementation.

static DbObject.query(dbversion=None)

The SQL SELECT query to fetch object instances from the catalogs

Parameters:dbversion – Postgres version identifier

This is used by the method fetch(). The dbversion parameter is used in descendant classes to customize the queries according to the target Postgres version.

DbObject.extern_filename(ext='yaml', truncate=False)

Return a filename to be used to output external files

Parameters:

• ext – file extension
• truncate – truncate filename to MAX_IDENT_LEN

Returns:

filename string

This is used for the first two levels of external (metadata) files. The first level is the one that includes schemas, as well as extensions, languages, casts and FDWs. The second level includes all schema-owned objects, i.e., tables, functions, operators, etc.

The common format for the filename is objtype.objname.yaml, e.g., for a table t1 the filename is “table.t1.yaml”. For an object name that has characters not allowed in filesystems, the characters are replaced by underscores.

DbObject.identifier()

Returns a full identifier for the database object

Returns:string

This is used by comment(), alter_owner() and drop() to generate SQL syntax referring to the object. It does not include the object type, but it may include (in overridden methods) other elements, e.g., the arguments to a function.

DbObject.to_map(db, no_owner=False, no_privs=False, deepcopy=True)

Convert an object to a YAML-suitable format

Parameters:

• db – db used to tie the objects together
• no_owner – exclude object owner information
• no_privs – exclude privilege information

Returns:

dictionary

The return value, a Python dictionary, is equivalent to a YAML or JSON object.

DbObject.map_privs()

Return a list of access privileges on the current object

Returns:list

The following methods generate SQL statements from the object properties and sometimes from a second object:

DbObject.comment()

Return SQL statement to create a COMMENT on the object

Returns:SQL statement

DbObject.alter_owner(owner=None)

Return ALTER statement to set the OWNER of an object

Returns:SQL statement

DbObject.drop()

Generate SQL to drop the current object

Returns:list of SQL statements

DbObject.rename(oldname)

Return SQL statement to RENAME the object

Parameters:oldname – the old name for the object Returns:SQL statement

DbObject.alter(inobj, no_owner=False)

Generate SQL to transform an existing database object

Parameters:inobj – a YAML map defining the new object Returns:list of SQL statements

Compares the current object to an input object and generates SQL statements to transform it into the one represented by the input.

DbObject.diff_privileges(inobj)

Generate SQL statements to grant or revoke privileges

Parameters:inobj – a YAML map defining the input object Returns:list of SQL statements

DbObject.diff_description(inobj)

Generate SQL statements to add or change COMMENTs

Parameters:inobj – a YAML map defining the input object Returns:list of SQL statements

Database Object Dictionary

A DbObjectDict represents a collection of DbObject’s and is derived from the Python built-in type dict. If a DbConnection object is used for initialization, an internal method is called to initialize the dictionary from the database catalogs. The DbObjectDict fetch() method fetches all objects using the query method defined by derived classes. Derived classes should also define a cls attribute for the associated DbObject class, e.g., SchemaDict sets cls to Schema.

class pyrseas.dbobject.DbObjectDict(dbconn=None)

A dictionary of database objects, all of the same type.

However, note that “type” sometimes refers to a polymorphic class. For example, a ConstraintDict holds objects of type Constraint, but the actual objects may be of class CheckConstraint, PrimaryKey, etc.

DbObjectDict.cls = <class 'pyrseas.dbobject.DbObject'>

The possibly-polymorphic class, derived from DbObject that the objects belong to.

DbObjectDict.to_map(db, opts)

Convert the object dictionary to a regular dictionary

Parameters:

• db – db used to tie the objects together
• opts – options to include/exclude information, etc.

Returns:

dictionary

Invokes the to_map method of each object to construct the dictionary. If opts specifies a directory, the objects are written to files in that directory.

DbObjectDict.fetch()

Fetch all objects from the catalogs using the associated query() methods.

Returns:list of self.cls (polymorphic) objects

Schema Object

A DbSchemaObject is derived from DbObject. It is used as a base class for objects owned by a schema and to define certain common methods. This is different from the Schema that represents the schema (Postgres namespace) itself.

class pyrseas.dbobject.DbSchemaObject(name, schema='public', description=None, **attrs)

A database object that is owned by a certain schema

DbSchemaObject.identifier()

Return a full identifier for a schema object

Returns:string

DbSchemaObject.qualname(schema=None, objname=None)

Return the schema-qualified name of self or a related object

Returns:string

DbSchemaObject.unqualify(objname)

Adjust the object name if it is qualified

Parameters:objname – object name Returns:unqualified object name

DbSchemaObject.rename(oldname)

Return SQL statement to RENAME the schema object

Parameters:oldname – the old name for the schema object Returns:SQL statement