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 PostgreSQL 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(**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 FDWs. 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.

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 maps. 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 overriden methods) other elements, e.g., the arguments to a function.

DbObject.to_map(no_owner=False, no_privs=False)

Convert an object to a YAML-suitable format

Parameters:

• no_owner – exclude object owner information
• no_privs – exclude privilege information

Returns:

dictionary

This base implementation simply copies the internal Python dictionary, removes the keylist attributes, and returns a new dictionary using the extern_key() result as the key.

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()

Return SQL statement to DROP the object

Returns:SQL statement

DbObject.rename(newname)

Return SQL statement to RENAME the object

Parameters:newname – the new name for the object Returns:SQL statement

DbObject.diff_map(inobj, no_owner=False)

Generate SQL to transform an existing object

Parameters:

• inobj – a YAML map defining the new object
• no_owner – exclude object owner information

Returns:

list of SQL statements

Compares the object to an input object and generates SQL statements to transform it into the one represented by the input. This base implementation simply deals with owners and comments.

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 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

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

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

DbObjectDict.query = ''

The SQL SELECT query to fetch object instances from the catalogs

This is used by the method fetch().

DbObjectDict.to_map(opts)

Convert the object dictionary to a regular dictionary

Parameters: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 class query

Returns:list of self.cls 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 itself.

class pyrseas.dbobject.DbSchemaObject(**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(objname=None)

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

Returns:string

No qualification is used if the schema is ‘public’.

DbSchemaObject.unqualify()

Adjust the schema and table name if the latter is qualified

DbSchemaObject.drop()

Return a SQL DROP statement for the schema object

Returns:SQL statement

DbSchemaObject.rename(newname)

Return a SQL ALTER statement to RENAME the schema object

Parameters:newname – the new name of the object Returns:SQL statement