wiki:development/database-abstraction
Last modified 4 years ago

Database Abstraction

Contact

  • Geoff Munn (geoff at silverstripe dot com)

Status

Rationale for existance

Currently SilverStripe? only supports MySQL, which is causing problems for projects and clients which require different database support. Also, other database providers offer features which could be handy, such as Postgres and geographical coordinates for example. We're going to be using PDO to achieve multi-database support.

The Plan

This is a big project which will touch most areas of Sapphire. Just about everything uses Sapphire in some form, so it affects every project in some way. In order to make this project achievable and a success, the plan is to break the abstraction down into many smaller steps. Some steps might not be identified right from the start, but will become obvious as things progress.

When each step is completed, it will be committed into Trunk where people can immediately test it and with a bit of luck it can be included as part of the next release, even before the abstraction project is fully completed. This way we can be sure that the abstraction works and bugs can be identified faster.

Ideally, all database-specific queries and functions will be moved into modules. There will be a MySQL module, and eventually a PostgreSQL module etc. This way, people can only include the database support they require, reducing the number of classes that get loaded each time.

The Steps

The first phase is to make all the existing MySQL queries to be database agnostic as much as possible. This involves:

  • Converting all inline IF... ELSE SQL statements to be CASE alternatives.
  • Removing all backtick escape characters and replacing them with an escape function to return a database-specific escape character. Initially, this will simply return a backtick.
  • Converting all the data types (field types) to be tokenised arrays rather than strings. This way, database-specific functions can build an appropriate data type (varchar for instance).
  • Moving the full text search fields into a MySQL module.
  • db/build runs some maintenance scripts which will need to be moved into the MySQL module.
  • Any more?

Phase 2 involves getting db/build working on MySQL, once MySQL support has moved to a separate module.

  • The specifics are unknown at this time.

Phase 3 involves creating a Postgres module. Hopefully, the amount of work required here can be estimated by looking at the size of the MySQL module, which contains MySQL-specific queries. We'll need to create a Postgres alternative to all of this. With a bit of luck, the Phase 1 modifications meant that everything else is plain vanilla SQL, and we don't need to create any more overrides.

  • The specifics are unknown at this time.

Testing

Database abstraction is a perfect candidate for unit testing. We'll need to create tests for each function that gets modified.