Tables, Views and Sequences

The table module defines six classes, DbClass derived from DbSchemaObject, classes Sequence, Table and View derived from DbClass, MaterializedView derived from View, and ClassDict, derived from DbObjectDict.

Database Class

Class DbClass is derived from DbSchemaObject and represents a table, view or sequence as defined in the PostgreSQL pg_class catalog.

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

A table, sequence or view

Sequence

Class Sequence is derived from DbClass and represents a sequence generator. Its keylist attributes are the schema name and the sequence name.

A Sequence has the following attributes: start_value, increment_by, max_value, min_value and cache_value.

The map returned by to_map() and expected as argument by diff_map() has the following structure:

{'sequence seq1':
    {'start_value': 1,
     'increment_by': 1,
     'max_value': None,
     'min_value': None,
     'cache_value': 1
    }
}

Only the inner dictionary is passed to diff_map(). The values are defaults so in practice an empty dictionary is also acceptable.

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

A sequence generator definition

Sequence.get_attrs(dbconn)

Get the attributes for the sequence

Parameters:dbconn – a DbConnection object
Sequence.get_dependent_table(dbconn)

Get the table and column name that uses or owns the sequence

Parameters:dbconn – a DbConnection object
Sequence.to_map(db, opts)

Convert a sequence definition to a YAML-suitable format

Parameters:opts – options to include/exclude tables, etc.
Returns:dictionary
Sequence.create(obj, *args, **kwargs)

Return a SQL statement to CREATE the sequence

Returns:SQL statements
Sequence.add_owner()

Return statement to ALTER the sequence to indicate its owner table

Returns:SQL statement

Table

Class Table is derived from DbClass and represents a database table. Its keylist attributes are the schema name and the table name.

The map returned by to_map() and expected as argument by diff_map() has a structure similar to the following:

{'table t1':
    {'columns':
        [
        {'c1': {'type': 'integer', 'not_null': True}},
        {'c2': {'type': 'text'}},
        {'c3': {'type': 'smallint'}},
        {'c4': {'type': 'date', 'default': 'now()'}}
        ],
     'description': "this is the comment for table t1",
     'primary_key':
        {'t1_prim_key':
            {'columns': ['c1', 'c2']}
        },
     'foreign_keys':
        {'t1_fgn_key1':
            {'columns': ['c2', 'c3'],
              'references':
                  {'table': 't2', 'columns': ['pc2', 'pc1']}
            },
         't1_fgn_key2':
            {'columns': ['c2'],
             'references': {'table': 't3', 'columns': ['qc1']}
            }
        },
     'unique_constraints': {...},
     'indexes': {...}
    }
}

The values for unique_constraints and indexes follow a pattern similar to primary_key, but there can be more than one such specification.

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

A database table definition

A table is identified by its schema name and table name. It should have a list of columns. It may have a primary_key, zero or more foreign_keys, zero or more unique_constraints, and zero or more indexes.

Table.column_names()

Return a list of column names in the table

Returns:list
Table.to_map(db, dbschemas, opts)

Convert a table to a YAML-suitable format

Parameters:
  • dbschemas – database dictionary of schemas
  • opts – options to include/exclude tables, etc.
Returns:

dictionary

Table.create()

Return SQL statements to CREATE the table

Returns:SQL statements
Table.drop()

Return a SQL DROP statement for the table

Returns:SQL statement
Table.diff_options(newopts)

Compare options lists and generate SQL SET or RESET clause

Newopts:list of new options
Returns:SQL SET / RESET clauses

Generate ([SET|RESET storage_parameter=value) clauses from two lists in the form of ‘key=value’ strings.

Table.data_export(dbconn, dirpath)

Copy table data out to a file

Parameters:
  • dbconn – database connection to use
  • dirpath – full path to the directory for the file to be created
Table.data_import(dirpath)

Generate SQL to import data into a table

Parameters:dirpath – full path for the directory for the file
Returns:list of SQL statements

View

Class View is derived from DbClass and represents a database view. Its keylist attributes are the schema name and the view name.

The map returned by to_map() and expected as argument by diff_map() has a structure similar to the following:

{'view v1':
    {'definition': " SELECT ...;",
     'description': "this is the comment for view v1"
    }
}
class pyrseas.dbobject.table.View(name, schema='public', description=None, **attrs)

A database view definition

A view is identified by its schema name and view name.

View.to_map(db, opts)

Convert a view to a YAML-suitable format

Parameters:opts – options to include/exclude tables, etc.
Returns:dictionary
View.create(obj, *args, **kwargs)

Return SQL statements to CREATE the view

Returns:SQL statements

Materialized View

Class MaterializedView is derived from View and represents a materialized view, available from PostgreSQL 9.3 onwards. Its keylist attributes are the schema name and the view name.

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

A materialized view definition

A materialized view is identified by its schema name and view name.

MaterializedView.to_map(db, opts)

Convert a materialized view to a YAML-suitable format

Parameters:opts – options to include/exclude tables, etc.
Returns:dictionary

Class Dictionary

Class ClassDict is derived from DbObjectDict and represents the collection of tables, views and sequences in a database.

class pyrseas.dbobject.table.ClassDict(dbconn=None)

The collection of tables and similar objects in a database

ClassDict.from_map(schema, inobjs, newdb)

Initalize the dictionary of tables by converting the input map

Parameters:
  • schema – schema owning the tables
  • inobjs – YAML map defining the schema objects
  • newdb – collection of dictionaries defining the database

Connect columns, constraints, etc. to their respective tables

Parameters:
  • dbcolumns – dictionary of columns
  • dbconstrs – dictionary of constraints
  • dbindexes – dictionary of indexes
  • dbrules – dictionary of rules
  • dbtriggers – dictionary of triggers

Links each list of table columns in dbcolumns to the corresponding table. Fills the foreign_keys, unique_constraints, indexes and triggers dictionaries for each table by traversing the dbconstrs, dbindexes and dbtriggers dictionaries, which are keyed by schema, table and constraint, index or trigger name.