Tables, Views and Sequences¶

The table and view modules define 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 Postgres pg_class catalog.

class pyrseas.dbobject.table.DbClass(name, schema, description, owner, privileges)¶

A table, sequence or view

The pg_class catalog also includes Postgres indexes, but for now, indexes have not been implemented as part of the DbClass hierarchy.

Sequence¶

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

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

{'sequence seq1':
    {'cache_value': 1,
     'data_type': 'integer',
     'increment_by': 1,
     'max_value': None,
     'min_value': None,
     'owner_column': 'c1',
     'owner_table': 't1',
     'start_value': 1
    }
}

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

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

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 ClassDict.from_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.

A Table can also represent a partitioned table or a partition of a partitioned table. The latter’s columns are all inherited from the parent (partitioned) table, so they are not shown in an output map (or expected on input).

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

Initialize 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 ClassDict.from_map() has a structure similar to the following:

{'view v1':
    {'columns': [{'c1': {'type': 'integer'}},
                 {'c2': {'type': 'date'}}],
     '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.

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

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

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

Return SQL statements to CREATE the materialized view

Returns:	SQL statements