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