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(**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(**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(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.diff_map(inseq)

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.

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(**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(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.diff_map(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.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(**attrs)

A database view definition

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

View.to_map(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 table

Returns:SQL statements

View.diff_map(inview)

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.table.MaterializedView(**attrs)

A materialized view definition

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

MaterializedView.to_map(opts)

Convert a materialized view to a YAML-suitable format

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

MaterializedView.diff_map(inview)

Generate SQL to transform an existing materialized 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.

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

ClassDict.link_refs(dbcolumns, dbconstrs, dbindexes, dbrules, dbtriggers)

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.

ClassDict.diff_map(intables)

Generate SQL to transform existing tables and sequences

Parameters:intables – a YAML map defining the new tables/sequences Returns:list of SQL statements

Compares the existing table/sequence definitions, as fetched from the catalogs, to the input map and generates SQL statements to transform the tables/sequences accordingly.