<ac:macro ac:name="unmigrated-inline-wiki-markup"><ac:plain-text-body><![CDATA[

<ac:macro ac:name="unmigrated-inline-wiki-markup"><ac:plain-text-body><![CDATA[

Zend Framework: Zend_Db_Schema_Manager Component Proposal

Table of Contents

1. Overview

Zend_Db_Schema_Manager provides the means for one to structure database upgrades / downgrades (i.e. "migrations") and copies (i.e. "replications") while being database agnostic and remaining flexible with regard to how db changes are defined, how the migration status is stored, and how db changes are executed.

2. References

• Original proposal by Rob Allen and Thomas Weidner
• Ruby On Rails Class ActiveRecord::Migration
• Phing DBDeploy

3. Component Requirements, Constraints, and Acceptance Criteria

• This component will execute defined 'migration steps' (upgrade / downgrade database).
• This component will examine a database and create a schema map of it which may be used to create a copy of that database (i.e. "replicate") in a database agnostic fashion.
• This component will provide extended versions of every supported database adapter with various database manipulation functions available (e.g. addTable(), renameColumn(), deleteIndex(), etc.). Users of custom adapters can easily extend the given abstract class.
• This component will allow database changes to be created as PHP classes, or config files (ini, xml, and soon I hope, yaml), or through any means that implements the given interface/abstract.
• This component will allow migration status information to be stored in a DB, or config file, or through any means that implements the given interface/abstract.
• This component will pull in migrations to be executed from a given path, of from the include path (with some search criteria defined), or from a class prefix (using the autoloader), or through any means that implements the given interface/abstract.
• This component will allow database change steps to be ordered by
  • numbering (either pulled from the class/file name or by a property set within the source file).
  • datetime (either pulled from the class/file name or by a property set within the source file).
• This component will provide classes to generate database change step source files in all supported formats.
• This component will allow default columns to be automatically added to all tables, or to prevent the default columns from being added to a partcular table.

4. Dependencies on Other Framework Components

• Zend_Exception
• Zend_Db
• Zend_Config
• Zend_Loader
• Zend_CodeGenerator

5. Theory of Operation

The Zend Database Schema Manager provides the means to organize and execute database upgrades, downgrades, and migrations.

Each database 'migration step', if you will, is stored in a file that contains the 'upgrade' operation and its reversing 'downgrade' operation; e.g. if a step creates a table on an 'upgrade', it is deleted in the 'downgrade'. This provides the means to build up and tear down a database in a controlled fashion, allowing steps to be arbitrarily applied and un-applied. Each file may a PHP class (the extends the provided abstract class), a Zend_Config readable file (that follows a prescribed format), or a class which implements the provided interface/abstract.

Each step contains within it the means to sort it among its peers. The sorting mechanism may be based on a numeric sequence or on date-time stamps, but all the steps must use the same mechanism; the same set of steps cannot mix sorting schemes. The sort schema to be used may either be set explicitly in the Db_Schema_Manager or it may be detected by the system. The sort values may either be set via properties internal to the step definition or encoded into the name class and/or file name of the step source.

The Db_Schema_Manager must have means defined for storing status information. By default, this is done within the database being maintained. Unless configured to do otherwise, the manager will automatically create a table for this purpose. Other storage options include providing the manager with a Zend_Config object or defining a custom storage mechanism by implementing the provided interface/abstract.

Upon initializing the storage location, all of the currently defined steps are read, their ordering determined, and references to each are added to the storage. The Db Schema Manager can refresh the storage on command, loading all the available steps and adding those that are missing (or restarting from scratch if so directed).

The Database Schema Manager can be directed to up/down-grade to a specific step number (or datetime) or to upgrade to the latest available. Each step's up/down-grade code is called in sequence according to the sort order. The process can be configured to halt on errors, to ignore errors and return a list of issues when completed, and/or to back out of the upgrade when an error occurs by rolling back the current transaction (if rollback is available) executing all the reversing down/up-grade steps.

A database can be replicated, copying all the table structures with their columns and indexes from one database (e.g. MySQL) to another (e.g. Oracle). This provides the means to easily implement the Db_Schema_Manager on a project that is already in progress. The database scheme may be stored in any of the formats defined for storing migration steps; when the directive to copy the database is given, the destination may be another database, a PHP class file, or a Zend_Config compatible file (i.e. ini or xml for now).

6. Milestones / Tasks

• Milestone 1: Proposal finished
• Milestone 2: Proposal accepted
• Milestone 3: Working implementation
• Milestone 4: Unit tests
• Milestone 5: Documentation
• Milestone 6: Moved to core

7. Class Index

Zend_Db_Schema_Exception
Zend_Db_Schema_Manager
Zend_Db_Schema_Replicate

Zend_Db_Schema_Adapter (factory class)
Zend_Db_Schema_Adapter_Abstract
Zend_Db_Schema_Adapter_Exception

Zend_Db_Schema_Adapter_Db2
Zend_Db_Schema_Adapter_Mysql
Zend_Db_Schema_Adapter_Oracle
Zend_Db_Schema_Adapter_Pdo_Ibm
Zend_Db_Schema_Adapter_Pdo_Mssql
Zend_Db_Schema_Adapter_Pdo_Mysql
Zend_Db_Schema_Adapter_Pdo_Oci
Zend_Db_Schema_Adapter_Pdo_Pgsql
Zend_Db_Schema_Adapter_Pdo_Sqlite

Zend_Db_Schema_Makestep (factory class)
Zend_Db_Schema_Makestep_Abstract
Zend_Db_Schema_Makestep_Exception

Zend_Db_Schema_Makestep_Config
Zend_Db_Schema_Makestep_Db
Zend_Db_Schema_Makestep_Php

Zend_Db_Schema_Migrate (factory class)
Zend_Db_Schema_Migrate_Exception
Zend_Db_Schema_Migrate_Abstract
Zend_Db_Schema_Migrate_Config
Zend_Db_Schema_Migrate_Step

Zend_Db_Schema_Source (factory class)
Zend_Db_Schema_Source_Abstract
Zend_Db_Schema_Source_Exception

Zend_Db_Schema_Source_Autoloader
Zend_Db_Schema_Source_IncludePath
Zend_Db_Schema_Source_Path

Zend_Db_Schema_Storage (factory class)
Zend_Db_Schema_Storage_Exception

Zend_Db_Schema_Storage_Abstract
Zend_Db_Schema_Storage_Cache
Zend_Db_Schema_Storage_Config
Zend_Db_Schema_Storage_Db

8. Use Cases

The simple use case could be starting project and building up the user table over several steps. In this case PHP classes are used so that data transformations can be easily accomplished, and the classes are named as though they were in the "Schema" directory of a module (i.e. much like the "form" or "table" directories). While it would be odd to mix sort designation conventions in a file, it is being done so here for the sake of demonstration (i.e. some sorts are designated in the class name while others are set via properties).

A project that requires developers and DBMs to modify the same database, but the DBMs are contractors and don't need to (or want to) know how the code works. The developers create database updates that effect their internal program data using PHP class extensions. A directory is setup for the DBMs to add changes in Zend_Config compatible files. Both parties can make their changes independently of each other while still maintaining an integrated sequence of changes to the same database(s).

Create new PHP migration step files.

Or the following, which would create the xml in UC-02

Run migration from current step up (retrieved from the schema storage) up to the latest available step

9. Class Skeletons