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 Rob Allen
Thomas Weidner
Revision 0.1 - 25 October 2006: Initial release.
0.2 - 30 November 2007: Additional features (wiki revision: 13)

Table of Contents

1. Overview

ACTUAL BEING REWORKED

Zend_Db_Schema_Manager maintains a database schema in a database agnostic format.
This ensures that the same change can be applied to multiple databases and all changes can be stored in
a version control system.
The schema itself can be given in several formats like XML, PHP. Additional input formats could be integrated.

2. References

3. Component Requirements, Constraints, and Acceptance Criteria

  • Zend_Db_Schema_Manager requires a database specific adapter for each supported database.

4. Dependencies on Other Framework Components

  • Zend_Exception
  • Zend_Db

5. Theory of Operation

Zend_Db_SchemaManager enables easy changes to a database schema without destroying
data. They can also be rolled back if required. The schema manager executes database
migration scripts which are collections of database operations such as adding a field to
a table.

Each migration script is a class extending from Zend_Db_Schema_Change which contains two
abstract methods: up() and down(). Migration script files are named like 001_initialSchema.php
so that they can be operated upon in order. The class within 001_initialSchema.php is called
initialSchema.

Note that for Zend_Db_Schema_Manager to work, a new table is required within the the database
being managed. This table, schema_info, holds the current version of the schema and is used
by the Manager to decide if the current operation is an upgrade or a rollback.

In order to work, a database independent structure to describe a table is used.

6. Milestones / Tasks

  • Milestone 1: Completed design on wiki
  • Milestone 2: Working prototype with unit tests checked into the incubator.
  • Milestone 3: Documentation exists

7. Class Index

  • Zend_Db_Schema_Exception - exception handler
  • Zend_Db_Schema_Manager - class to manage migrating from one schema version to another
  • Zend_Db_Schema_Change - abstract class to inherit each migrations script from
  • Zend_Db_Schema_Table - database independant table creation and altering
  • Zend_Db_Schema_Adapter - abstract class for database specific schema-change adapters
  • Zend_Db_Schema_Adapter_Mysql - MySQL adapter for adding/deleting/editing tables
  • Zend_Db_Schema_Adapter_Mssql - MSSQL adapter for adding/deleting/editing tables

8. Use Cases

UC-01

The main use case is when you need to change the database schema of a live application.
Obviously this is done on the development machine first and the Schema_Manager will
ensure that the same changes to the database will occur on the live site.

Example migration script: 001_AddSecondEmailAddressColumn.php

Example CLI script to run the Manager: updateSchema.php

9. Class Skeletons

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

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

Labels:
zend_db_schema zend_db_schema Delete
zend_db zend_db Delete
Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.
  1. Feb 07, 2007

    <p>Why not to send this proposal for review? It is very useful.</p>

  2. Jul 11, 2007

    <p>While I <strong>love</strong> this idea, I wonder if it's necessary to name files with revision numbers in front? It breaks the naming conventions because the classes can't be named properly, and it just looks weird. <ac:emoticon ac:name="smile" /> </p>

    <p>Why not make revisionNum a property of Zend_Db_Schema_Change and then use that to check against the db table?</p>

    <p>Other than that, I think it's a great idea and long-overdue for the PHP community.</p>

    1. Nov 28, 2007

      <p>To have revisionNum as a property of Zend_Db_Schema_Change would it make very unhandy to get the latest revision - You have to open every Schema Change File to know what the latest change was. In this case it`s also needed that Zend_Db_Schema_Manager parses and initiates all Zend_Db_Schema_Change Classes first to get the right order. Thats not a real problem but i think to name the files is a good convention for fast development.</p>

      <p>But anyway - Is there any active work on this proposal? It is a must-have for web applications.</p>

  3. Dec 27, 2007

    <p>I have a few point from the perspective of one who has used rails db:migrations in the past. BTW, correct me if I'm wrong, but I see this as a relatively straightforward adaptation of migrations in Rails to PHP; it would help me immensely if you summarized the similarities and differences so I can see how we might differentiate ZF's solution.</p>

    <p>1) I guess I just never saw the value in creating a whole abstraction around DDL statements for non-trivial schemas. I mean, yeah, they're portable to different databases, but then a lot of the simple ddl that you might cover with such a system is often portable in its regular string DDL format. That said, for a non-trivial schema in Rails, one generally (by necessity) disposes of this abstraction fairly quickly, which fortunately still allows the developer to take advantage of the versioning without necessarily using the built-in database agnosticism. At the very least, this proposal would have to include such a mechanism, since it would be nearly impossible to support all database DDL/features in the abstraction. In fact, these different feature sets make complete db agnosticism completely impossible, so I'm left wondering 'Why can't we just use DDL again?' I can see the value in simple cases, but is it enough to add a significant abstraction to the framework to expose?<br />
    2) I like database versioning, which I fully agree that we need to support in some way for framework. I haven't done a whole lot of complex versioning with db:migrations, but I'd want to be sure that it would work well even in less-than-straightforward circumstances. This versioning is the real value in db:migrations from my point of view.<br />
    3) This should definitely reuse <ac:link><ri:page ri:content-title="Zend_Build" /><ac:link-body>Zend_Build - Wil Sinclair</ac:link-body></ac:link>.</p>

    <p>Also, I believe Cake has a db:migrations port to PHP, and I don't see much point in redoing their work. I would challenge you to see where we can add value to these tools- especially in the context of non-trivial or legacy schemas.</p>

    <p>Hope this all makes sense given my limited background on this proposal.</p>

    <p>Thanks.<br />
    ,Wil</p>

    1. Dec 27, 2007

      <p>Until now I did not have time to complete the rework of this proposal.<br />
      It will still take me some time to finish it, based on how much work I will have with the other proposals.</p>

      <p>to 1)<br />
      DB Abstraction has it's benefits if you are porting an application from one server to another, or change the RDBMS, or even if you are importing a new version which has another schema. I have created my own in the past ( pre-ZF <ac:emoticon ac:name="smile" /> ), and it's very usefull when you have a working environment and want to update it.</p>

      <p>Generally there are two set's of work...<br />
      1.) The DB-Layer itself... here documented as set of PHP files. This one I would like to have seperated in XML or any other text-based format. It simplifies the port between systems.<br />
      2.) The Update procedure... from version x to y or backwards... these are sets of works... could be textbased (limited) or other.</p>

      <p>But I have not been ready to write down the whole thing.</p>

      <p>to 2)<br />
      This is a must for update/downgrade. But the way should not be fixed but loosely coupled.</p>

      <p>to 3)<br />
      I did not know this component for now nor did I see it end of november when I had some time for rethinking of this component. Is it already finished ?</p>

      <p>to Cake)<br />
      Do we want to say "use Cake for database versioning", we don't have one ?<br />
      I will have to look what they have done before I can say what's the difference. But I think there is one. I am basically an enterprise application developer so my point of view is possibly not that strict as a normal web-dev. And I think especially this component has much value for enterprise applications.</p>

      <p>Generally speaking:<br />
      This proposal has not been forgotten... there's just much work to do and to rethink before doing it.</p>

      <p>Greetings<br />
      Thomas</p>

      1. Jan 10, 2008

        <p>I'd like to reserve judgment on the DDL abstraction layer until this proposal is fully fleshed out, so Thomas/Rob- the ball is fully in your court. <ac:emoticon ac:name="smile" /><br />
        RE:Cake- What we really want to say is "Zend Framework has database versioning, and it is the only logical choice for you given your needs". <ac:emoticon ac:name="wink" /> My team at Zend has heard it a lot lately, and it's about time we started spreading the message to the whole community- we're looking for best-by-far solutions for a set of users that has not been adequately served by other frameworks in the <strong>very</strong> crowded field of PHP frameworks. The fact is, we can't be the best framework for every application, but I believe that we are the best framework for non-trivial apps in the Enterprise or other environments where "opinionated frameworks" just don't cut it and that we should continue building our components to further this lead. So, in that context, I would ask myself for any proposal- including this one- "Can we bring value to functional areas and more demanding users where other frameworks have not yet ventured or fallen short?"<br />
        I personally feel that RoR migrations are great in some ways but fall short in more complex cases. I can't wait to see how your proposal will address some of those shortcomings. <ac:emoticon ac:name="laugh" /></p>

    2. Dec 28, 2007

      <p>I think the primary purpose should <em>not</em> be that the application is portable to different databases. I think the benefit is that the schema-change-files are easy to maintain and seperate through different versions of your application. I can see this at my job, where we have every month a new release of our application. In most releases we do not have much DDL and less non-trivial DDL, but although we see that its not the best way to maintain the plain sql files through different versions. So i fully agree with Thomas Weidner.</p>

      <p>Regarding to Zend_Build and this proposal. Perhaps it would be good to have different adapters for the DDL. Which allows to be more compatible to existing build tools.<br />
      Although adapters for converting or use of existing db-change files, for instance from phing (I know phing is using creole).</p>

      <p>Cakes tools is php4.</p>

      <p>kind regards,<br />
      Thomas (the other one) ;o)</p>

      1. Jan 10, 2008

        <p>Hmm... for some reason, the wiki has lost my watch on this page <ac:emoticon ac:name="sad" /></p>

        <p>I had two key reasons for writing this proposal, before all my time got sucked away:</p>

        <p>1. Version control of database schema changes.<br />
        2. Database independence so that I can manage both MSSQL and MySQL schemas. <br />
        3. Bonus points for being able to use the same set of schema changes in both MSSQL and MySQL.</p>

        <p>I still have these needs and I'm sorry that I haven't been able to push this forward as I had intended.</p>

        <p>Regards,</p>

        <p>Rob...</p>

        1. Jan 11, 2008

          <p>That's exactly what I have developed for my company.<br />
          But that's closed source and this was the reason why I wanted to help you.</p>

          <p>My own time is also melted away... too much opened proposals or ideas to implement <ac:emoticon ac:name="wink" /></p>

          <p>I think after 1.5 I will have time to add my ideas to this proposal.<br />
          My ideas cover:</p>

          <ul>
          <li>Database Versioning</li>
          <li>Schema independence and portability</li>
          <li>Schema checking and repairing</li>
          <li>Integrating of default values</li>
          <li>Adding of an portable schema format (xml) others are also possible</li>
          <li>Support of multiple RDBMS through Zend_Db</li>
          </ul>

  4. Jan 09, 2008

    <p>With regards to the use of DDL, I imagine Enterprise Applications as such would require much finer tweaking of the database than an abstraction layer could provide. I struggled with Rails Migrations because I wanted to store large integers (billions) in my MySQL database, at the time there was no way of specifying BIGINT, so I had to use strings and cast in the model.</p>

    <p>Versioning is very useful, I use phing and dbdeploy at the minute and find it very effective. The only problems I've had to overcome is managing the migrations between branches of the applications. </p>

  5. Apr 10, 2008

    <p>Hi there, </p>

    <p>The last comments here were in Jan saying that people didn't want to forget this proposal...</p>

    <p>May I offer my help in a general 'do lots of rubbish jobs' kind of way? My thought is that if someone other than you were to put some effort in it might get things rolling, i.e. inspire you. <ac:emoticon ac:name="wink" /></p>

    <p>I'm happy to do whatever...</p>

    <p>Regards, <br />
    Carlton</p>

    1. Apr 10, 2008

      <p>This proposal is not forgotten...<br />
      Actually I have 6 proposals in queue which are worked on additionally some issues.</p>

      <p>And I am forcing to integrate a coding standard with ralph which is in my eyes more important to be finished as it has effect to the complete framework.</p>

      <p>Related to this proposal my idea was to have it more abstract.<br />
      I want it to be more generic so you can eighter use a sort of db description file which could be xml what I would prefer, or php what rob has proposed original.</p>

      <p>What I also had in mind was a way to have default values integrated, and checked.<br />
      Also the db structure should be able to be checked and repaired.</p>

      <p>I've already done such a class for my company but it's only mssql and it's closed source.</p>

      <p>So what we want to do is a generic class which uses Zend_Db and is able to handle different layout input files.</p>

      <p>Some of my other ideas which shall be integrated were already described by me in my previous comments.</p>

      <p>Regards,<br />
      Thomas, I18N Team Leader</p>

      1. Apr 10, 2008

        <p>Hi Thomas, </p>

        <p>"This proposal is not forgotten..." – I never thought for a moment that it was <ac:emoticon ac:name="smile" /></p>

        <p>My thought was just that if you and Rob are both busy, which given all you activity on ZF I imagine you are, it might be that there were tasks waiting to be done which weren't that hard but would still take time.</p>

        <p>I am still beginning with ZF - I have only been using PHP for 2 years - but I want to help. So IF THERE IS such a task on this proposal, please let me know and I will be happy to put my thought into it. </p>

        <p>In the meantime I will think on the proposal myself and perhaps start to come up with some test cases and the like. </p>

        <p>All the best, <br />
        Carlton</p>

  6. Jun 03, 2008

    <p>+1<br />
    Very much looking forward to this!</p>

  7. Jun 17, 2008

    <p>I think a slightly different approach should be taken to where/how DDL is generated. I'd suggest placing a number of abstract methods into Zend_Db_Adapter_Abstract (createTable, dropTable, addColumn, etc.), and each database adapter should implement those methods.</p>

    <p>If methods are the same across (some of) the adapters (such as dropping a table), they could even be implemented in Zend_Db_Adapter_Abstract and those that are different can override them.</p>

    <p>All in all, I would really love to see this functionality in the Zend Framework.</p>

  8. Jul 05, 2008

    <p>Looks good! Can't wait to get my hands on it <ac:emoticon ac:name="laugh" /><br />
    Is there an implementation available somewhere?</p>

    1. Jul 05, 2008

      <p>You're not the only one. <ac:emoticon ac:name="wink" /></p>

      <p>Actually myself is working on the file transfer component and the new coding standards.<br />
      I had no time until now to complete this proposal.</p>

      <p>It's far away from being ready for review.<br />
      There are necessary features missing and some are not defined complete.</p>

      <p>For example:<br />
      There will be a sort of definition schema which will allow to define a db schema per xml or ini file. Also there will be a way of defining default values in tables.</p>

      <p>These are just a few of what I've already done for my company where I created a DB Update manager which allows what Rob and I created here and a lot more.</p>

      <p>So all I can say for now is:<br />
      ... Please be patient and stay tuned <ac:emoticon ac:name="smile" /> ...</p>

  9. Oct 27, 2008

    <p>+1<br />
    Very much looking forward to this!</p>

  10. Oct 27, 2008

    fc

    <p>Just recently, my company open-sourced a db migration tool: <br />
    <a href="http://code.google.com/p/dmigrations/">http://code.google.com/p/dmigrations/</a>. I would definitely like to see something like that implemented in the ZF. The fact that it generates the DDL for you without writing any code, apart from your models, makes it very simple, flexible and easy to use. It not only speeds up the development process, but speaks the DBAs language, allowing them to refactor and optimize the SQL before migrating. This is essential in big development teams or medium to large organizations.</p>

  11. Jan 27, 2009

    <p>Please, see the ezcDbSchema class (<a class="external-link" href="http://svn.ez.no/svn/ezcomponents/trunk/DatabaseSchema/src/">http://svn.ez.no/svn/ezcomponents/trunk/DatabaseSchema/src/</a>) from eZ Components.<br />
    It has all required features <ac:emoticon ac:name="wink" /></p>

    <p>Please see on other components too.</p>

    1. Jun 01, 2009

      <p>Greetings,</p>

      <p>Well... I've looked through the ezcompenents database schema, and it does not (AFAIK) provide the functionality speced here. It DOES provide a means for migrating from one database schema to another. It does NOT provide the means of versioning migrations with the ability to perform any arbitrary task during that migration (e.g. performing data transformations, etc.) as is possible in a RoR like db management schema. It certainly does not provide the level of integration with ZF beig proposed here. If I'm wrong in this assessment, please tell us so.</p>

      <p>Regards,</p>

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

  12. Jun 01, 2009

    <p>Greetings All,</p>

    <p>Ok - I'm going to start banging this code out as I have an immediate need for it. Coincidentally, I've just received my CLA verification. As I'm write this, I'm also seeking a mentor to tutor me through the process of proposing and submitting code to the Zend Framework project, so if you're qualified and up for that, please do drop me a line.</p>

    <p>Regards,</p>

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

    1. Jul 01, 2009

      <p>Hi Sean,</p>

      <p>Great to hear you're being active on this proposal. What's the latest on this? I've seen you're alternative proposal for the schema manager, is there any progress on that one?</p>

      <p>The reason I ask is because I myself have a working implementation of the schema manager which I've done for work. It is loosely based on this proposal and I was considering contributing it to the framework. However because you've started an alternative proposal and there is very little activity here, I am unsure how to progress.</p>

      <p>Perhaps the Zend Proposal administrator/moderator/staff could provide some guidance and feedback.</p>

      <p>Either way, I'm interested in contributing code and/or coding and/or time to this component.</p>

      1. Jul 05, 2009

        <p>Greetings Luke,</p>

        <p>I'll send you an email to elaborate, but the short of is that I'm actually trying to bang out the last of my code today to update my proposal and move it into the commenting phase. More to come!</p>

        <p>Regards,</p>

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

  13. Feb 03, 2010

    <p>I would very much like to see true migrations. I should add that Doctrine also does this. Also I wrote a forwards only migration mechanism that supports .sql and .php files</p>

    <p>Here's a very messy example:</p>

    <p><a class="external-link" href="http://pastebin.com/f4fa2dcc6">http://pastebin.com/f4fa2dcc6</a></p>

    <p>The script just checks the version # in the version table, and runs any scripts that are numbered with a version <strong>after</strong> that. There is no rollback support yet since that requires good db schema abstraction in order to diffs (of which you are working on)</p>

  14. Sep 19, 2010

    <p>Archiving per Rob's request</p>

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