Skip to end of metadata
Go to start of metadata

<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

Proposed Component Name Zend_Db_Schema_Manager
Developer Notes http://framework.zend.com/wiki/display/ZFDEV/Zend_Db_Schema_Manager
Proposers Sean P. O. MacCath-Moran
Zend Liaison TBD
Revision 1.0 - 1 January 2008: Initial Draft. (wiki revision: 8)

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

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

UC-01

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).

UC-02

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).

UC-03

Create new PHP migration step files.

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

UC-04

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

9. Class Skeletons

]]></ac:plain-text-body></ac:macro>

]]></ac:plain-text-body></ac:macro>

Labels:
database database Delete
zend_db zend_db Delete
migrate migrate Delete
zend_db_schema zend_db_schema Delete
replication replication Delete
Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.
  1. Jun 25, 2009

    <p>Greetings All,</p>

    <p>FYI, I'm still working on the design of this; after having thought through this a little harder I realized I had overcomplicated some issues and not complicated others enough. More to come soon.</p>

    <p>Regards,</p>

    <p>Sean P. O. MacCath-Moran<br />
    www.emanaton.com</p>

  2. Jul 12, 2009

    <p>hello,</p>

    <p>have you looked at ezcomponents.org ezcDbSchema component? This is a very simple but powerful implementation.</p>

    <p>I cant really infer this from the requirements, but can you diff any two database schemas and get the required steps to equalize them?</p>

    <p>I am really looking forward for this component, Also i'd would really like to have an adapter for Zend_Entity Metadata, which allows to go from Metadata => Schema, Schema => Metadata <ac:emoticon ac:name="smile" /></p>

    1. Jul 12, 2009

      <p>Greetings Benjamin,</p>

      <p>Thanks for the notes!</p>

      <p>Indeed, I have looked at ez's component. It allows one to back up a database and to recreate entire schemas. It does not (or at least did not appear to when I looked at it) allow one create incremental changes that may be applied or backed out of, so was not sufficient to my needs. Does this seem accurate to you?</p>

      <p>In the proposal I'll be going forward with (once I complete it), I've limited the initial scope a bit to make it more manageable. I'll be concentrating on the upgrade / downgrade functionality, implementing this with a much flexibility as possible. My initial proposal will not include database replication or diff tools. The reasoning for this varied, but among them is that there are several proposals in development by others that will make such activities much, much easier, and I'm hoping to build on their successes. Also, my proposal was getting a bit big for it's britches, and I need to limit how much time I spend on such projects. =o)</p>

      <p>Unfortunately, I'm not able to work on the project this week as all of my work is on a hard drive I can't access; my laptop's GPU died and I don't have a proper drive interface on hand to mount it externally. However, the work on the updated proposal is nearly done and I hope to regain access and complete this within a week or so.</p>

      <p>Thanks again!</p>

      <p>Regards,</p>

      <p>Sean P. O. MacCath-Moran<br />
      www.emanaton.com</p>

  3. Oct 20, 2009

    <p>... I really need to get back to working on this. I keep needing this functionality. Someone get me a magic genie for me to wish on and get this work done for me. =o/</p>

    1. Oct 28, 2009

      <p>What is the actual need to be done for a full working component? I'm eager to have this feature, able to give some help (need to email my CLA tomorrow) and have some space (+svn) to host this component. There are six milestones, but none of them are marked as done.</p>

  4. Oct 22, 2009

    <p>This sounds interesting. I've been reading about migrations in Doctrine recently which is similar to your proposal - <a class="external-link" href="http://www.doctrine-project.org/documentation/manual/1_1/en/migrations">http://www.doctrine-project.org/documentation/manual/1_1/en/migrations</a></p>

    <p>Presumably this would be dependent on Zend_Db_Table and therefore make use of the table metadata querying functionality available? </p>

  5. Nov 04, 2009

    <p>This looks very nice!</p>

    <p>As stated by Simon in the previous comment, we are using Doctrine's migration classes right now for our schema updates. But since we <em>only</em> use the migration classes, I'd be more than happy to drop Doctrine in favor of Zend_Db_Schema_Manager.</p>

    <p>@Sean: Is there existing code? I'd be happy to help out, if needed.<br />
    Will it support the whole range of possible database schema manipulations? I'm thinking of stuff like:<br />
    createTable, dropTable, alterTable, createForeignKey, dropForeignKey, createIndex, dropIndex<br />
    Nice would be support for fixture data, as well (mass insert's, which is still lacking in Zend_Db AFAIK; update, delete, insertSelect).</p>

  6. Feb 05, 2011

    <p>Archiving this proposal, feel free to recover it when you want to work on it again. For more details see <a href="http://framework.zend.com/wiki/display/ZFDEV/Archiving+of+abandoned+proposals+(Feb+5+2011)">this email</a>.</p>