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