DDC-169: Refactor DBAL DDL Code

[doctrinebot]

Jira issue originally created by user @beberlei:

SchemaTool currently has to do very complex reference management to get the datastructure for schema generation working. Its completely array based, so its not easy to understand what is going on and why.

I propose to change the DBAL DDL API in the following way:

-Introduce Objects for Schema, Table, Column, Index, Constraints, Views, Sequence/Identity Handling-

-Introduce SchemaDiff with algorithm that creates a diff between 2 schema instances and their subtypes.-

Refactor AbstractSchemaManager to accept only Schema Objects for DDL Schema Change execution.

-Return only Schema Objects from list*() Methods and introduce a createSchema()-

-Accept only Schema Objects in DDL Create Statements.-

Refactor SchemaTool to new SchemaManager API

-Refactor getCreateSql()-

-Refactor getDropSql()-

-Refactor getUpdateSql()-

Bugs still to fix:

• Enforcing in Schema that each Foreign Key HAS to have a corresponding Index on the Local Table
• Foreign Keys need special handling in both cases when changes of tables and such are to be executed without errors. Ideas?

There are massive benefits from this:

Cross-Platform DDL Generation is complex, an Object Oriented API for Schema's could hide the complexity of this options from the user. This would make SchemaManager a powerful API that can be easily used by users that don't use the ORM package.

It moves large parts of the logic from SchemaTool into the DBAL package and decouples responsibilities.

SchemaDiff allows to refactor the Create Schema command from a purely creating approach to a diff of an empty against the desired schema.

SchemaTool::update will be much easier to implement this way, and will not cluttter SchemaTool::update with details of Schema Comparison (which it does currently).

SchemaTools only task will then be to create a Schema instance from the metadata, the creation of DDL for create(), update(), drop() will then be generated by the according diff algorithms of the SchemaManager API.

Since DBAL Schema Creation is not a performance critical task there is no problem in going from array structures to massive object usage.

The approach is based on eZ Components DatabaseSchema component which has this view on Schema Generation and whose API is expectionally easy to use:
http://ezcomponents.org/docs/api/trunk/introduction_DatabaseSchema.html

[doctrinebot]

Comment created by lucasts:

As nowadays we had a lot of users using document-oriented databases and maybe interested in using Doctrine. I suggest at little to make possible to use DC2 DDL API to manage it.

To do that, we need to create objects :

• Document
• View
  and some hooks in platform API.

I'll deep some more on this, and back to comment here.

[doctrinebot]

Comment created by @beberlei:

But the DBAL package will the RMDBS specific, for document databases we need new packages, one for drivers, one for the ODM, so this shouldn't affect the DBAL refactoring.

[doctrinebot]

Comment created by lucasts:

Benjamin,
Yeah, we will need some new packages.

I'm just suggesting, as to be clear document dbs don't need at all to pre-create tables, just views and databases.

but, if DBAL will be only for relational databases, no problem.

As gblanco told you, I'm preparing a migration extension specific to dc2.
about this refactoring, did you had some patch or need some help?

[doctrinebot]

Comment created by @beberlei:

CouchDB for example needs to have some mechanism for automatic view generation, which will be used for the ODM package. Views in Couch work like Stored Procedures, so you have to have some mechanism to build them automatically.

Regarding DBAL Refactoring, yes i need help :-)

I poke you about it in IRC tonight.

[doctrinebot]

Comment created by @beberlei:

Update Ticket with the current status of this endaveour

[doctrinebot]

Comment created by @beberlei:

The refactoring is now almost finished, there is only some serious problem with foreign keys in updating/dropping. Anyone any ideas how to fix them?

[doctrinebot]

Comment created by @beberlei:

Implemented and added a rough documentation on the API with a DBAL section in the manual.

[doctrinebot]

Issue was closed with resolution "Fixed"