Constraints

The constraint module defines six classes: Constraint derived from DbSchemaObject, classes CheckConstraint, PrimaryKey, ForeignKey and UniqueConstraint derived from Constraint, and ConstraintDict derived from DbObjectDict.

Constraint

Class Constraint is derived from DbSchemaObject and represents a constraint on a database table. Its keylist attributes are the schema name, the table name and the constraint name.

class pyrseas.dbobject.constraint.Constraint(**attrs)

A constraint definition, such as a primary key, foreign key or unique constraint

Constraint.key_columns()

Return comma-separated list of key column names

Returns:string

Constraint.add(obj, *args, **kwargs)

Return string to add the constraint via ALTER TABLE

Returns:SQL statement

Works as is for primary keys and unique constraints but has to be overridden for check constraints and foreign keys.

Constraint.drop()

Return string to drop the constraint via ALTER TABLE

Returns:SQL statement

Constraint.comment()

Return SQL statement to create COMMENT on constraint

Returns:SQL statement

Check Constraint

CheckConstraint is derived from Constraint and represents a CHECK constraint.

class pyrseas.dbobject.constraint.CheckConstraint(**attrs)

A check constraint definition

CheckConstraint.to_map(dbcols)

Convert a check constraint definition to a YAML-suitable format

Parameters:dbcols – dictionary of dbobject columns Returns:dictionary

CheckConstraint.add(obj, *args, **kwargs)

Return string to add the CHECK constraint via ALTER TABLE

Returns:SQL statement

CheckConstraint.diff_map(inchk)

Generate SQL to transform an existing CHECK constraint

Parameters:inchk – a YAML map defining the new CHECK constraint Returns:list of SQL statements

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

Primary Key

PrimaryKey is derived from Constraint and represents a primary key constraint.

class pyrseas.dbobject.constraint.PrimaryKey(**attrs)

A primary key constraint definition

PrimaryKey.to_map(dbcols)

Convert a primary key definition to a YAML-suitable format

Parameters:dbcols – dictionary of dbobject columns Returns:dictionary

PrimaryKey.diff_map(inpk)

Generate SQL to transform an existing primary key

Parameters:inpk – a YAML map defining the new primary key Returns:list of SQL statements

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

Foreign Key

ForeignKey is derived from Constraint and represents a foreign key constraint.

The following shows a foreign key segment of a map returned by to_map() and expected as argument by ConstraintDict.diff_map() exemplifying various possibilities:

{'t1_fgn_key1':
    {
    'columns': ['c2', 'c3'],
    'on_delete': 'restrict',
    'on_update': 'set null',
    'references':
        {'columns': ['pc2', 'pc1'], 'schema': 's1', 'table': 't2'}
    }
}

class pyrseas.dbobject.constraint.ForeignKey(**attrs)

A foreign key constraint definition

ForeignKey.ref_columns()

Return comma-separated list of reference column names

Returns:string

ForeignKey.to_map(dbcols, refcols)

Convert a foreign key definition to a YAML-suitable format

Parameters:dbcols – dictionary of dbobject columns Returns:dictionary

ForeignKey.add(obj, *args, **kwargs)

Return string to add the foreign key via ALTER TABLE

Returns:SQL statement

ForeignKey.diff_map(infk)

Generate SQL to transform an existing foreign key

Parameters:infk – a YAML map defining the new foreign key Returns:list of SQL statements

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

Unique Constraint

UniqueConstraint is derived from Constraint and represents a UNIQUE, non-primary key constraint.

class pyrseas.dbobject.constraint.UniqueConstraint(**attrs)

A unique constraint definition

UniqueConstraint.to_map(dbcols)

Convert a unique constraint definition to a YAML-suitable format

Parameters:dbcols – dictionary of dbobject columns Returns:dictionary

UniqueConstraint.diff_map(inuc)

Generate SQL to transform an existing unique constraint

Parameters:inuc – a YAML map defining the new unique constraint Returns:list of SQL statements

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

Constraint Dictionary

Class ConstraintDict is a dictionary derived from DbObjectDict and represents the collection of constraints in a database.

class pyrseas.dbobject.constraint.ConstraintDict(dbconn=None)

The collection of table or column constraints in a database

ConstraintDict.from_map(table, inconstrs, target='')

Initialize the dictionary of constraints by converting the input map

Parameters:

• table – table affected by the constraints
• inconstrs – YAML map defining the constraints

ConstraintDict.diff_map(inconstrs)

Generate SQL to transform existing constraints

Parameters:inconstrs – a YAML map defining the new constraints Returns:list of SQL statements

Compares the existing constraint definitions, as fetched from the catalogs, to the input map and generates SQL statements to transform the constraints accordingly.