- 1 Blueprint: DatabaseService
- 1.1 Target Versions/Milestones
- 1.2 Goals / Motivation
- 1.3 Concept
- 1.4 Implementation Details
- 1.5 Risks
- 1.6 Issues and reviews
- 1.7 Dependencies upon other Blueprints
- 1.8 External links for clarification of technologies
|Proposal||Cross DBMS database service and query builder|
|Owner/Starter||Jigal van Hemert|
|Topic for Gerrit||DatabaseService|
- Started during TYPO3 CMS 7.0 development
Goals / Motivation
An enterprise level CMS needs to run on multiple DBMS. Current DBAL implementation rewrites queries for systems other than MySQL. Rewriting queries has several disadvantages:
- reduced performance because query needs to be analyzed
- query is created twice
- limited functionality
- developers need to be aware of the supported features
The current DBAL implementation also has unique features which need to be kept:
- transparent renaming of tables and column
- automatic selection of DBMS based on table
The new system should offer a query generator which deals with the features and can execute the query in a way that is completely transparent to the rest of the code.
The core should offer a database service which can build and execute queries. By using a service the actual implementation can easily be replaced in the future.
The query builder will abstract the construction of the actual query. Before executing a query the actual query is created based on the DBMS. Table and column “mappings” are taken into consideration.
Execution of the query and retrieving the results is handled by a layer which abstracts the interaction with the DBMS.
The last area which needs a solution is the table definition. This also need to be abstracted to be completely cross database. These four abstraction layers result in hiding the details of the database and the interaction with the database completely. As a result there is a uniform API to interact with the database with the possibility to implement other database types (noSQL?) without the need to change anything in the core or extensions.
This is a completely different concept than the current DatabaseConnection object combined with DBAL. In order to be fully used it requires changes in the entire core and many extensions. One of the advantages of the new service is that it can co-exist with the old methods. Parts of the core can slowly be adapted to the new service.
Instead of the global variable with the database connection object the service can be retrieved as a singleton object.
The query builder takes care of :
- applying the table and column mapping
- constructing expressions (cross database)
- combining the parts of the query into an SQL statement
The expressions need to take the DBMS which will execute the final query into account. The expression builder can for example support “find_in_set” and create an expression for DBMS which do not know this function natively.
Doctrine has a query builder, but this does not take differences between DBMS into consideration.
Different drivers can handle the implementation details between database engines. Doctrine has a light weight layer to handle this. Most of the drivers use PDO, for Oracle a specific driver is available. This is an extendable solution which can support other systems once drivers are available (or created). If doctrine is not suitable a better solution must be found or built. The same is valid for the PDO drivers; if these drivers are too slow we should look for alternatives or create them.
Doctrine also features a solution to abstract table schema definitions. For backwards compatibility we'll need to offer a way to generate schemas from a .sql file. Perhaps this can be auto-generated in some way.
Although this is one of the strengths of doctrine, this will not be useful for general use in the core. Quite a few database operations are not suitable for this.
None. The database service will co-exist with the current database connection object and DBAL. After all parts of the core are modified to use the new service, the old database handling will be deprecated. Finally extensions will have to use the new database service.
Although doctrine features are mentioned quite a few times in this blueprint, it's not the holy grail to integrate doctrine. Currently it looks like certain parts of doctrine are useful for the implementation of a properly abstracted database layer in the core. If during the built of this new database service tests show that doctrine is not suitable or that it misses features we desperately need, an alternative must be found or created.
Issues and reviews
Dependencies upon other Blueprints
- Doctrine DBAL http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/
- Doctrine Schema Manager http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/schema-manager.html
- Doctrine Schema Representation http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/schema-representation.html
- Thesis by Stefano Kowalke about integrating doctrine in TYPO3 core https://github.com/Konafets/thesis
- Code by Stefano Kowalke to replace current database connection object by doctrine in TYPO3 core https://github.com/Konafets/ext-doctrine_dbal