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: