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, description, owner, privileges)

A table, sequence, index 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, description, owner, privileges, start_value=1, increment_by=1, max_value=9223372036854775807, min_value=1, cache_value=1, data_type='bigint', owner_table=None, owner_column=None, oid=None)

A sequence generator definition

static Sequence.from_map(name, schema, inobj)

Initialize a sequence instance from a YAML map

Parameters:
  • name – sequence name
  • name – schema map
  • inobj – YAML map of the sequence
Returns:

sequence instance

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
Sequence.alter(inseq, no_owner=False)

Generate SQL to transform an existing sequence

Parameters:inseq – a YAML map defining the new sequence
Returns:list of SQL statements

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

Sequence.drop()

Generate SQL to drop the current sequence

Returns:list of SQL statements

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, description, owner, privileges, tablespace=None, unlogged=False, options=None, partition_bound_spec=None, partition_by=None, partition_cols=None, partition_exprs=None, oid=None)

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.

static Table.from_map(name, schema, inobj)

Initialize a table instance from a YAML map

Parameters:
  • name – table name
  • name – schema map
  • inobj – YAML map of the table
Returns:

table instance

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(dbversion=None)

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.alter(intable)

Generate SQL to transform an existing table

Parameters:intable – a YAML map defining the new table
Returns:list of SQL statements

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

Table.alter_drop_columns(intable)

Generate SQL to drop columns from an existing table

Parameters:intable – a YAML map defining the new table
Returns:list of SQL statements

Compares the table to an input table and generates SQL statements to drop any columns missing from the one represented by the input.

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

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

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.view.View(name, schema, description, owner, privileges, definition, oid=None)

A database view definition

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

static View.from_map(name, schema, inobj)

Initialize a view instance from a YAML map

Parameters:
  • name – view name
  • name – schema map
  • inobj – YAML map of the view
Returns:

view instance

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
View.alter(inview, dbversion=None)

Generate SQL to transform an existing view

Parameters:inview – a YAML map defining the new view
Returns:list of SQL statements

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

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.view.MaterializedView(name, schema, description, owner, privileges, definition, with_data=False, oid=None)

A materialized view definition

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

static MaterializedView.from_map(name, schema, inobj)

Initialize a materialized view instance from a YAML map

Parameters:
  • name – view name
  • name – schema map
  • inobj – YAML map of the view
Returns:

materialized view instance

MaterializedView.to_map(db, opts)

Convert a materialized view to a YAML-suitable format

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

Return SQL statements to CREATE the materialized view

Returns:SQL statements