Gerald Schema API

Introduction

Gerald is a general purpose database schema toolkit written in Python. It can be used for cataloguing, managing and deploying schemas in relational databases.

Gerald can read, store and manipulate information about collections of database objects. These collections of objects are call schemas. This document describes the format that Gerlad uses to store information about these objects and is valid for version 0.3.5 of Gerald and above.

The top level of a specific implementation of the API should provide a method which returns a Schema object.

Schema Objects

Attributes

Schemas must have the following attributes:

Key	Description	Data Type
name	A name for the schema, usually the same as the database (or schema owner)	Text
api_version	schema api version	Integer
schema	A collection of objects which form this schema made of Table, View, Sequence, and Code_Object objects.	Collection

Methods

Name	Description
dump(file_name, sort)	Output this schema to file_name in a nice to read format. If sort is specified the objects will be sorted by their name attributes.
to_xml(file_name)	Output this schema to file_name in XML format. If file_name is omitted then the XML is sent to standard output.
get_ddl(file_name)	Output the DDL necessary to recreate this schema to file_name. If file_name is ommitted then the DDL is output to standard output.
compare(other_schema)	Utility method to compare two schemas.

User Objects

Attributes

Users must have the following attributes:

Key	Description	Data Type
name	A name for the user, must be the database user name	Text
api_version	schema api version	Integer
schema	A collection of objects which this user has access to. Can include Table, View, Sequence and Code_Object objects.	Collection

Methods

Name	Description
dump(file_name, sort)	Output this user to file_name in a nice to read format. If sort is specified the objects will be sorted by their name attributes.
to_xml(file_name)	Output this user to file_name in XML format. If file_name is omitted then the XML is sent to standard output.
get_ddl(file_name)	Output the DDL necessary to recreate the objects this user can access to file_name. If file_name is ommitted then the DDL is output to standard output.
compare(other_schema)	Utility method to compare two users.

Table Objects

A table is made up of columns and will also have indexes, triggers, constraints, primary and foreign keys.

Attributes

Tables must have the following attributes:

Key	Description	Data Type
name	the name of the table	Text
columns	A dictionary (keyed on column name) of Column dictionaries that make up this table	Dictionary
indexes	A dictionary (keyed on index name) of Index dictionaries	Dictionary
constraints	A dictionary (keyed on constraint name) of Constraint dictionaries	Dictionary
triggers	A dictionary (keyed on trigger name) of Trigger dictionaries	Dictionary

Tables may optionally have one or more of the following attributes:

Key	Description	Data Type
tablespace_name	The tablespace this table is stored in	Text
table_type	The type of storage engine used for this table Only populated for MySQL tables	Text
comments	A comment on the table	Text
schema	The name of the schema this table belongs to. Used by get_ddl methods	Text

Methods

Name	Description
dump()	Return a description of this table in a nice to read format
to_xml()	Return a description of this table as an XML fragment
get_ddl()	Return the DDL necessary to create this table
compare(other_table)	Utility method to compare two tables

View Objects

Attributes

Views must have the following attributes:

Key	Description	Data Type
name	Name of this view	Text
columns	A dictionary (keyed on column name) of Column dictionaries that make up this view	Dictionary
sql	The SQL statement that will create the view	Text

They may optionally have one or more of the following attributes:

Key	Description	Data Type
triggers	A dictionary (keyed on name) of Trigger objects that are associated with this view	Dictionary
schema	The name of the schema this View is part of	Text

Methods

Name	Description
dump()	Return a description of this view in a nice to read format
to_xml()	Return a description of this view as an XML fragment
get_ddl()	Return the DDL necessary to create this view
compare(other_table)	Utility method to compare two views

Sequence Objects

Attributes

Sequence objects must have the following attributes:

Key	Description	Data Type
name	Sequence name	Text
min_value	Minimum value	Integer
max_value	Maximum value	Integer
increment_by	Interval to use when incrementing the sequence	Integer
curr_value	Current value of this sequence. Only used for informational reasons.	Integer

They may optionally have the following attribute:

Key	Description	Data Type
schema	Name of the schema this sequence belongs to	Text

Methods

Name	Description
dump()	Return a description of this sequence in a nice to read format
to_xml()	Return a description of this sequence as an XML fragment
get_ddl()	Return the DDL to create this sequence
compare(other_seq)	Utility method to compare two sequences

Code Objects

Attributes

Code Objects must have the following attributes:

Key	Description	Data Type
name	Name of this code object	Text
object_type	The type of this code object, one of function, procedure or package	Text
source	A sequence of (line number, code) sequences	Text

They may optionally have the following attribute:

Key	Description	Data Type
schema	Name of the schema this code object belongs to	Text

Methods

Name	Description
dump()	Return a description of this code object in a nice to read format
to_xml()	Return a description of this code object as an XML fragment
get_ddl()	Return the DDL to create this code object
compare(other_co)	Utility method to compare two code objects

Triggers

Attributes

Key	Description	Data Type
name	Trigger name	Text
scope	Scope of this trigger (before, after, instead of)	Text
events	A list of the events that cause this trigger to fire (insert, update, delete)	Text
level	Is this a row or statement level trigger?	Text
sql	The SQL executed when this trigger fires	Text

Methods

Name	Description
dump()	Return a description of this trigger in a nice to read format
to_xml()	Return a description of this trigger as an XML fragment
get_ddl()	Return the DDL to create this trigger
compare(other_trig)	Utility method to compare two triggers

Columns

A column is not a stand alone class, just a simple dictionary. They must have the following elements:

Key	Description	Data Type
sequence	The order of this column in the table	Integer
name	Column name	Text
type	Native data type, will vary by database	Text
nullable	Can this column contain NULL values?	Boolean

Columns may optionally have one or more of the following elements:

Key	Description	Data Type
length	Maximum length of column	Integer
precision	Maximum number of digits before the decimal point only valid for numeric columns	Integer
scale	Maximum number of digits after the decimal point	Integer
default	Default value to be inserted if this column is NULL on insert	Any
special	Only used by MySQL to indicate if a column has auto_increment set	Boolean
comment	Column comment	Text

Indexes

Like a column an index is just a simple dictionary. They must have the following elements:

Key	Description	Data Type
name	Index name	Text
type	Index type. Database specific	Text
unique	Flag to indicate if index elements must be unique	Boolean
columns	A sequence of column names in the index	Sequence

Constraints

Constraints are represented by simple dictionaries with the following elements:

Key	Description	Data Type
name	Constraint name	Text
type	One of ‘Primary’, ‘Foreign’, ‘Check’ or ‘Unique’	Text
enabled	Is the constraint enabled?	Boolean

They may optionally have the following elements:

Key	Description	Data Type
columns	Column names in this constraint	Sequence
reftable	Reference table (only used for Foreign keys)	Text
refpk	Reference primary key this foreign key is checked against (only used for Foreign keys)	Text
refcolumns	List of reference columns (Foreign keys only)	Sequence
condition	Search condition (Check constraints only)	Text