Translations
Info
All page names need to be in English.
en da  de  fr  it  ja  km  nl  ru  zh

Blueprints/DatabaseService

From TYPO3Wiki
Jump to: navigation, search

<- Back to blueprints overview

Blueprint: DatabaseService

Proposal Cross DBMS database service and query builder
Owner/Starter Jigal van Hemert
Participants/Members DBAL team
Status Discussion
Current Progress Unknown
Topic for Gerrit DatabaseService

Target Versions/Milestones

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

Concept

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.

Implementation Details

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.

Database service

Instead of the global variable with the database connection object the service can be retrieved as a singleton object.

Query builder

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.

Query execution

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.

Table definition

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.

ORM?

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.

Risks

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

None

External links for clarification of technologies