Skip to end of metadata
Go to start of metadata

Zend Framework: Zend_Db_Schema Component Proposal

Proposed Component Name Zend_Db_Schema
Developer Notes http://framework.zend.com/wiki/display/ZFDEV/Zend_Db_Schema
Proposers Pieter Kokx
Zend Liaison TBD
Revision 1.0 - 21 January 2010: Initial Draft.
1.1 - 15 February 2010: Added basic class skeletons.
1.2 - 15 February 2010: Component is Ready for Review (wiki revision: 16)

Table of Contents

1. Overview

Zend_Db_Schema is a component that provides abstraction for DDL statements.

2. References

None

3. Component Requirements, Constraints, and Acceptance Criteria

  • Zend_Db_Schema will abstract DDL statements for Zend_Db.
  • Zend_Db_Schema will not implement migrations.
  • Zend_Db_Schema will not use Zend_Db_Table for table structures.
  • Zend_Db_Schema will be able to use Zend_Config for table definitions.
  • Zend_Db_Schema will be able to edit an existing database.
  • Zend_Db_Schema will not abstract database-specific DDL statements like stored procedures, views and triggers.

4. Dependencies on Other Framework Components

  • Zend_Exception
  • Zend_Config
  • Zend_Db
  • Zend_Tool

5. Theory of Operation

If the database doesn't exist yet, Zend_Db_Schema will create it, and create all the tables in the database. However, if there is a database, Zend_Db_Schema will compare the database with the schema and change the database. Also, Zend_Db_Schema ensures that existing data won't be touched.

For field-types, Zend_Db_Schema will use meta-types, which translate to the best matching type on the used RDBMS.

A table of possible meta-types for Zend_Db_Schema, and their matching types on some database systems:

Zend_Db_Schema field meta-type MySQL field type Notes
string(50) varchar(50) -
clob/clob(16) text -
clob(24) mediumtext The clob meta-type argument is the power of two of how many characters minus one it should be able to store in the field
blob/blob(16) blob Works the same as clob but for binary data
int int -
float float -
double double -
bool bool -
date date -
datetime datetime -
time time -

6. Milestones / Tasks

  • Milestone 1: Finish the proposal
  • Milestone 2: Initial class design
  • Milestone 3: Submit the proposal for community review
  • Milestone 4: Create working prototype (see the bottom of this proposal)
  • Milestone 5: Create code-covering unit tests.

7. Class Index

  • Zend_Db_Schema
  • Zend_Db_Schema_Table
  • Zend_Db_Schema_Table_Field
  • Zend_Db_Schema_Table_Index
  • Zend_Db_Schema_Adapter_AdapterInterface
  • Zend_Db_Schema_Adapter_AdapterAbstract
  • Zend_Db_Schema_Adapter_Pdo_PdoAbtract
  • Zend_Db_Schema_Adapter_Pdo_Mysql
  • Zend_Db_Schema_Adapter_Mysqli

8. Use Cases

UC-01

Schema definition:

UC-02

Schema definition using Zend_Config array:

9. Class Skeletons

Labels:
None
Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.

17 Comments

comments.show.hide

  1. Feb 16, 2010

    Alvar Vilu

    I't seems pretty usable!

  2. Feb 16, 2010

    André de Camargo Fernandes

    I did like it! Its really easy to use and has a lot of use for me.

  3. Feb 16, 2010

    Yegor Bugayenko

    How about VIEW's, STORED PROCEDURES and TRIGGER's?

    1. Feb 16, 2010

      Pieter Kokx

      Thats something I'm still thinking about. Implementing view's isn't that hard (i will probably solve it in combination with Zend_Db_Select).

      But implementing stored procedures isn't that easy. Stored procedures are implemented differently across most database systems, which is very hard to abstract correctly.

      Triggers aren't implemented in the same way across most database systems, but triggers aren't that hard to abstract as stored procedures are. I will probably come up with a way to abstract this later.

  4. Feb 16, 2010

    Tim Fountain

    This looks good. Any chance of use cases for dropping/renaming tables and removing fields?

  5. Feb 16, 2010

    Jeroen Keppens

    Hi,

    I like what you're trying to do, but in the proposal I'm missing things to go from useful to mega-useful.

    1. Make a factory for creating your table (might be your intention, but it doesn't show in the skeletons; except for the config) :

    • load from a config object
    • load from an array
    • load a schema from the database to modify it
    • load a schema by providing Zend_Db_Table class
    • allow for specifying options for your table (engine, start increment, etc...)

    2. Allow for multi-field indexes (make the second param an array)

    3. Allow for modifying table / fields (ALTER TABLE) and indexes

    Probably some more stuff, which I don't think of right now.

    This would be a very good useful for doing things like DB versioning...

  6. Feb 16, 2010

    Ralph Schindler

    While I like the API, one question that comes to mind is how this will distinguish the database platform specific features? For example, the naming of specific column types, and the variations in DDL that each database platform might implement that is outside of the ANSI SQL spec?

    1. Feb 17, 2010

      Pieter Kokx

      For the variations in DDL statements, I'm mostly thinking of adding methods like createTable() alterTable() and such to Zend_Db adapters. Another way is to make different Zend_Db_Schema adapters that will handle the creation of the statements.

      For the types and indexes, i'm going to make a list of pseudo-types. Zend_Db_Schema will then match it to the best available type for the current RDBMS.

      1. Feb 19, 2010

        Benjamin Eberlei

        First, I know I have been guilty of re-inventing the wheel before myself, but ezcDbSchema and Doctrine\DBAL\Schema are already implementing this functionality.

        Now:

        adding DDL Abstraction to adapters is just a bad idea, there are adapters that work with the same database (pdo_oci, oci8) or (pdo_mysql and mysqli). For Doctrine DBAL package we added a specific class that abstracts dialects of sql vendors called AbstractPlatform, and each Driver has one platform.

        Views, Stored Procedures and Triggers can't be abstracted, they are just too specific to platforms, since they all three need vendor specific SQL as input.

        Additionally your approach is missing foreign keys, which are very important for any good database schema. Sequences are also missing, Oracle and Postgres work with them. Plus there needs to be an approach to simulate auto-increment with these platforms using triggers + sequences.

        There is also missing information on how you abstract the different datatypes. For example Oracle VARCHARs are only allowed to be max 4000 chars long, in MySQL they are allowed to be much larger. Some DBs support booleans, some don't. What is your common factor?

        1. Mar 13, 2010

          Pieter Kokx

          Well, NIH is something we all suffer at least a bit from I think .

          And yes you are right, adding DDL abstraction to the DB adapters isn't a good idea. I'm probably going to create 'schema adapters' for specific database vendors to address such problems.

          And sequences/auto_increment is something I'm still thinking about. Like you said: using triggers and sequences is the way to simulate auto_increment, this is something that should be implemented in the schema adapters.

          How to abstract the different datatypes? Well, I'm going to make a list of pseudo-types like I said before. These types will have some restrictions so they will apply nicely to all database vendors (like: maximum lenght for 'string' datatype will probably be 4000, because of Oracle).

          1. Apr 24, 2010

            Mark Murphy

            You could have a list of standardized data types, and allow for RDBMS specific data types for those columns that are non-standard. As long as people know which data types are standard, they can make a decision to use those exclusively, or allow non-standard type where necessary for their specific implementation. In general people will only be using a single RDBMS per application, and since this class will pull out all the definitions into a common place, it would not be terribly tedious to port to a different RDBMS if necessary.

  7. Feb 24, 2010

    Yegor Bugayenko

    Just consider an option of specifying DB schema by means of plain SQL statements. And Zend_Db_Schema will parse statements, if possible. Flex/bison tools are good possible methods of such a syntax/lexical parsing...

    1. Feb 24, 2010

      Pieter Kokx

      Any idea what impact this would have? I mean, of course it isn't that hard to parse some SQL, but it goes quite beyond the scope of Zend_Db_Schema. That way it will make Zend_Db_Schema a lot more complex than just specifying the schema.

      Adding plain SQL statements is not an option at all. To use them you need to parse them, and that would make Zend_Db_Schema really big and probably bloated too.

      1. Feb 24, 2010

        Yegor Bugayenko

        Well, it's a question what is more difficult - to create SQL from PHP arrays/objects. Or to create PHP objects from SQL. One thing is certain - if you allow developers to specify DB schema in SQL, they can start using Zend_Db_Schema right now, and you can improve it and extend it for a long time.

        Otherwise, developers should wait until you give them a tool that will satisfy all their needs. And who knows how much time will it take... See my point?

        We already implemented this mechanism, for MySQL only. But it works in many projects already, and works stable: http://code.google.com/p/fazend/source/browse/trunk/FaZend/Deployer.php. The approach is to use plain SQL first, and PHP objects/array second. I'm ready to help you with Zend_Db_Schema.

        1. Feb 24, 2010

          Pieter Kokx

          If you think porting an SQL schema to a Zend_Db_Schema object would be hard, then you need to wait yes. But I don't really see any difficulty in that.

          If you really think we need this, go ahead and propose some kind of API for this .

          But for my opinion, this goes completely against the ZF belief of KISS.

  8. May 01, 2010

    Bill Karwin

    This proposal is a good example of what I was talking about in a recent blog post: http://karwin.blogspot.com/2010/04/dont-put-cart-before-horse.html

    What is the need for this component? What problem does it solve? How does it solve that problem? Why is it a better solution than just using SQL DDL?

    For example, if we rely on $db->query() to execute literal SQL DDL, this would have advantages including the following:

    • The framework would support all features of each respective RDBMS. There would be no gaps.
    • The framework would require no new code to support DDL – none.

    In your proposal, you need to give examples of realistic scenarios are difficult with the current framework, explain why these scenarios need to be supported by the framework, and show how your component is a good solution for those scenarios.

  9. Feb 08, 2011

    Dolf Schimmel (Freeaqingme)

    Archiving this proposal, feel free to recover it when you want to work on it again. For more details see this email.