PGSchemaKit

Introduction

This framework can be used for managing database schemas. For example, you may want to define a set of types, tables, views, functions and triggers for one set of functionalty, and then add additional functionality. You may also want to make the second set of functionality dependent on the creation of the first set. We call these sets of functionality 'products'.

Here's an example .schema.xml file which defines a type and a table within a single 'product':

<?xml version="1.0" encoding="UTF-8"?>
<product name="com.mutablelogic.addressbook" version="1">
	<comment>Address book functionality</comment>
	<requires name="pgschema.base" version="1"/>
	<create>
		<create-type name="e_entrytype">
			ENUM('work-email','work-phone','home-phone','work-address')
		</create-type>
		<create-table name="t_person" observe="insert update delete">
			uuid SERIAL NOT NULL PRIMARY KEY,
			name VARCHAR(200) NOT NULL
		</create-table>
		<create-table name="t_detail" observe="insert update delete">
			person INT REFERENCES t_person (uuid),
			CHAR(100) NOT NULL,
			entry_type e_entrytype
		</create-table>
	</create>
	<drop>
		<drop-table name="t_detail"/>
		<drop-table name="t_person"/>
		<drop-type name="e_entrytype"/>
	</drop>
</product>

The framework also defines mechanisms for keeping track of changes to table data. You can add 'observers' to be notified when tables have rows updated, inserted or deleted. This would allow you to reflect data changes in your client as soon as databases are changed, from any source. This is an important requirement when developing applications which use data sources shared amongst several parties.

The application PGSchemaManager demonstrates how to implement management of database schemas, and a future one will demonstrate being notified on changes.

Using the framework

To use this framework, include it in your application's Frameworks folder, and include your schema product files (with extension .schema.xml) in your application's Resources folder. Import the header file <PGSchemaKit/PGSchemaKit.h> in any code.

Using a PGSchemaManager object

The PGSchemaManager object can be used to manage database schemas. Within your application startup, you will then need to create a PGSchemaManager object. For example,

#import <PGClientKit/PGClientKit.h>
#import <PGSchemaKit/PGSchemaKit.h>

@synthesize schema = _schema;

-(id)init {
	self = [super init];
	if(self) {
		_name = @"my_schema";
		_connection = [[PGConnection alloc] init];
		_schemamanager = [[PGSchemaManager alloc] initWithConnection:_connection name:_name];
	}
	return self;
}

The constructor method requires a database connection with appropriate permissions. The name argument of the constructor is used to override the schema name, so it is possible to install the same product more than once in a database catalog. If the name argument is nil, then the public schema is used. If the schema doesn't yet exist, if will be created when the create or update methods are called.

Adding a search path

The addSearchPath method is used to trigger the search for schema product files. These need to have the extension .schema.xml:

@interface PGSchemaManager (Methods)
-(BOOL)addSearchPath:(NSString* )path error:(NSError** )error;
-(BOOL)addSearchPath:(NSString* )path recursive:(BOOL)isRecursive error:(NSError** )error;
@end

Search paths can be added so that the search for schema product files can be recursive into sub-folders. The method will return NO and return an error object if an error occured. Errors can occur if:

• The search path is invalid
• The database connection is invalid, or no valid database was selected
• Any schema file is not readable, or invalid

Typically, you would want to include your schema product files in the Resources subfolder of your application. Then you could use the following method to add the resources folder:

-(void)addSearchPath {
	NSError* error = nil;
	[[self schema] addSearchPath:[[NSBundle mainBundle] resourcePath] error:&error];
	if(error) {
		// TODO: handle error condition
	}
}

Enumerating available and installed products

Once a search path is added, the products property can be used to enumerate available products to install. More than one search path can be added by calling the method subsequently. The installed property can be used to enumerate currently installed products, again of type PGSchemaProduct:

@interface PGSchemaManager (Properties)
@property (readonly) NSArray* products;
@property (readonly) NSArray* installed;
@end

Each product is of type PGSchemaProduct and includes the folowing methods:

@interface PGSchemaProduct (Properties)
@property (readonly) NSString* name;
@property (readonly) NSUInteger version;
@property (readonly) NSString* comment;
@end

Creating schema products

There are two phases to installing a product. The first is to do a 'dry run' to ensure a product can be installed. The second is to run through the instructions actually create the types, tables, indexes, views, functions and triggers which comprise the product.

@interface PGSchemaManager (Methods)
-(BOOL)create:(PGSchemaProduct* )product dryrun:(BOOL)isDryrun error:(NSError** )error;
-(BOOL)create:(PGSchemaProduct* )product dryrun:(BOOL)isDryrun recursive:(BOOL)isRecursive error:(NSError** )error;
@end

If the recursive argument is YES then the manager also attempts to install dependent products (this is the default). By switching this to NO, then the method will return an error condition if any dependencies can't be satisfied and no attempt is made to satisfy them.

Typically the following checks are made during a dry run:

• Ensure the product's .schema.xml file exists and is readable
• Ensure the required dependencies have already been satisfied, or can be satisfied.
• Ensure the product has not already been installed, in any version

If an error occurs, the method returns NO and an error object is returned. During a dry run, the database catalog is not altered at all, which means a subsequent attempt can be made. If an error occurs without the dryrun flag set to YES, some issue will likely have occurred which cannot be repaired.

You would typically use the following example code:

-(BOOL)create:(PGSchemaProduct* )product {
	NSError* error = nil;

	[[self schemamanager] create:product dryrun:YES error:error];
	if(error) {
		// TODO: handle NSError
		return NO;
	}

	[[self schemamanager] create:product dryrun:NO error:error];
	if(error) {
		// TODO: handle NSError
		return NO;
	}
	return YES;
}

Dropping schema products

The ability to drop installed schema prouct versions is not yet implemented.

Updating schema product versions

The ability to update installed schema prouct versions is not yet implemented.

Adding observers for table data changes

The ability to be notified on database table changes is not yet implemented.

Schema Product Files

A "schema product" is a set of instructions for creating and dropping a single schema product version. Each is an XML file including the following elements:

• The root node <product> includes the name and version of the schema product.
• The <comment> node describes the product.
• The <requires> nodes describe the products (and necessary versions) which need to exist as dependents of this product version.
• The <create> node includes instructions for creating the schema for this product and version.
• The <drop> node includes instructions for removing the schema for this product and version.

Here is a template for a typical schema product file:

<?xml version="1.0" encoding="UTF-8"?>
<product name="[product name]" version="[product version]">

	<comment>[comment about the product]</comment>

	<requires name="[name of dependent product]" version="[version of product required]"/>
	...
	<requires name="[name of dependent product]" version="[version of product required]"/>

	<create>
		...create statements...
	</create>

	<drop>
		...drop statements...
	</drop>

	<update from="[version]">
		...update statements...
	</update>
</product>

Limitations