Show Advanced KnowledgeHide Advanced Knowledge

ilPDO

This Request is the Result of the Concept proposed due to the Requirement given by the JF in this FR: Replace Replace ilDB/MDB2 with PDO

1 Requirements

1.1 Motivation

ILIAS uses the MDB2 library for database access and authentication and is thus strongly coupled to the PEAR libraries. Most PEAR libraries aren't maintaned in years and the support for PHP 7 is not secured yet. These issues and a potential gain in performance by using PDO for database access motivates the replacement of MDB2 with PDO in the ILIAS core.

1.2 ilDB

A short analysis of the current situation leads to the following class UML for the database access within ILIAS. A factory delivers an instance for the connection to the database at the beginning of each request based on the configurations in the client.ini file.

A prototype was created enabling the navigation within about 80% of ILIAS functionality; without the setup or the initialisation of the database. The prototype does NOT extend ilDB. ilDB itself extends the PEAR library and thus has coupling with several PEAR libraries and functionality that is not needed for a PDO based database accesss. Thus we have to extract a minimal interface of ilDB that needs to be implemented in order for ILIAS to run with. Furthermore we need to extract the interface of the returned objects given when calling a ilDB query (namely MDB2_Result_Common). Step by step we implement the API of the extracted interface using PDO and the ilDBStatements. This results in the following class diagram. 

We change the ilDBWrapperFactory to return a class of type "ilDBInterface" and we enter an additional database type: "pdo-mysql". The Interface of ilDB and ilDBInterface are identical; the interface for ilDBStatement and MDB2_Result_Common is identical too. This allows a seamless transition from MDB2 to PDO for the ILIAS Core. Furthermore it allows the usage of PDO and MDB in parallel. In parallel here means: ILIAS can deliver both methods for database access and which one is used is up to the configuration.

We disencourage the usage of an additional wrapper for PDO, for example the doctrine library. The databaseabstraction is already handled by the usage of the ilDBInterface and as an ORM the ILIAS Active Record can be used.

1.3 Oracle and additional databases

In a first implementation PDO does not support Oracle databases. But as MDB2 can be delivered in parallel the support of ILIAS for Oracle stays unchanged. As the interface for database classes is extracted additional databases can easily be added with the usage of PDO or other libraries. Thus we can implement the access of Oracle databases without the need for MDB2 libraries in a later stage.

1.4 Delegation of querystring utilities

Currently the methods for querystring utilities (for example the creation of an IN-Statement by using $ilDB->in($array)) are implemented on the database classes. These querystring utilities are dependend on the sql dialect. Thus MySql querystring utilities in the MDB2 implementation and the PDO implementation are the same. We plan to delegate these functionalities to a seperate class that is shared by  ilDBMySql and ilDBPdo. This is not yet shown in the UML above and is optional.

1.5 ilAuth

The authentication in ILIAS is strongly coupled with the PEAR libraries. The current implementation of the authentication leads to unneccessary load on the database. Through the creation of the AuthContainers a second connection to the database is created by the MDB2 library independent of the global $ilDB. Thus authentication needs to be refactored in order to get rid of MDB2 for all database interactions and use PDO throughout ILIAS.

Gernerally there are two options to do so:

1. Patching MDB2_Auth
The patch will replace the separate connection of the AuthContainer with the access to the database using ilDB. We need to search for every database access and refactor it to use the interface of ilDB. A prototype of the patched authentication exists and ILIAS is usable with it.

Advantages:

• The current implementation of the authentication can be kept as it is for all authentication cases.
• The solution is implementable with little financial/time effort.

Disadvantages:

• The PEAR library MDB2_Auth is kept. It will most likely not be compatible with PHP 7.
• IF PEAR MDB2_Auth is getting maintentance we will not be able to update our dependency as it is patched.
• We maintain MDB2_Auth foreign code.

2. Refactoring of the Authentication
Refactoring the authentication will be necessary as soon as we want to support PHP 7. While introducing PDO the authentication could be refactored/rewritten in order not to depend on any PEAR libraries. A prototype already exists  for local authentication and ILIAS is usable with it.

Advantages:

• The founding can be justified with the introduction of PDO. We have the chance to find found for a refactoring task that is otherwise hard to find.
• With a new authentication an additional dependency to the PEAR libraries will be removed. PHP 7 will be easier to support.
• ILIAS could have it's own authentication with less unused features, this may be easier to understand.

Disadvantages:

• A refactoring/replacement of the authentication is more expensive in time and money.
• Almost all authentication contexts and container need to be refactored and tested. This can be expensive for some contexts (e.g. LDAP Authentication).

Furthermore the security of the authentication lies within the maintenance of the ILIAS community instead of the MDB2 community.

1.6 Performance

By having an authentication that does not use a separate database connection as well as by using PDO for database access we expect a performance increase. We used a prototype for the authentication as well as for PDO and made perfomance tests for a database intensive request (displaying a data collection). With a high load the median for a page request went down from 960ms to 810ms which is an increase in performance of about 15%.

We tested with apache bench and a data collection with ~800 entries where 30 entries were displayed. Furthermore on every request the login process was handled. There were 1000 requests fired with a concurrency level of 8 (in average 8 concurrent requests). 

	Requests/Second	Time per request (mean)	Time per request (mean, across all concurrent requests)	Longest request
MDB2 + standard Authentifizierung	7.66 [#/sec]	1044.001 [ms]	130.500 [ms]	1591 [ms]
MDB2 + gepatchte Authentifizierung	8.32 [#/sec]	962.005 [ms]	120.251 [ms]	1716 [ms]
PDO + gepatchte Authentifizierung	9.91[#/sec]	807.041[ms]	100.880[ms]	1258 [ms]
PDO + neue Authentifizierung	10.03 [#/sec]	797.914 [ms]	99.739 [ms]	1459 [ms]

As usual performance tests, especially those using prototypes, have to be taken with a grain of salt. Perfomance improvements are though not a main motviation for the implementaion of PDO.

1.7 Prototypes

The prototype for ilPDO with a patched MDB2 authentication can be found here.

A testinstallation can be found here: https://test.studer-raimann.ch/ilpdo. Username: test, Password: password.

The prototype for ilPDO with a new authentication can be found here.

2 Additional Information

• Idea / concept: Oskar Truffer sr.solutions
• Interest in funding: Universität Bern
• Maintainer: Schmid, Fabian [fschmid]
• Implementation of the feature is done by …

3 Discussion

JourFixe, ILIAS [jourfixe], Dec 21, 2015: We discussed the concept for introducing ilPDO and patching or refactoring the authentication. But because several component maintainers were not attending the meeting, we decided to postpone a decision to January 18, 2016. We please all component maintainers to read this suggestion and to add comments to this page.

Kiegel, Colin [kiegel], 2015-12-22: We should drop Oracle support - there is virtually no benefit in keeping Oracle, but a lot of trouble. Running ILIAS with Oracle is a performance desaster, everyone who tried this combination had huge performance issues and is now replacing ILIAS with another LMS. I see no point in making our lifes harder such that other institutions can repeat this "wonderful" user experience of ILIAS + Oracle. This is the right time to drop oracle support. And I find it hard to understand, that we are still discussing this...?

Jansen, Michael [mjansen], Jan 4, 2016:

• Regarding Oracle: I agree with Colin. But it is worth mentioning that using native PHP functions for Oracle is much faster than connecting with MDB2 (we did that in an ILIAS project some years ago, with a reduced feature set compared to MDB2).
• Regarding the UML: I appreciate the introduction of the new interfaces. This would be a huge step towards a more maintainable component (in terms of clean code, testability, interchangeability) especially if PDO will be deprecated in the far future.
• Regarding the authentication: I personally prefer a clean revision/refactoring of the ILIAS authentication service, but I am of course aware it will be hard to raise the required funding. We should present this to the board and apply for society funds.

Killing, Alexander [alex] 5 Jan 2016: Having these interfaces is without any doubt a very good idea, I fully support this. What should be worked out is the way we deal with the different SQL implementations and capabilities of different storage engines. Even if we drop Oracle support, we have InnoDB/MyISAM and PostgreSQL, which come with some differences e.g. in SQL syntax, support of fulltext indices, reserved words, way of locking tables etc. This is slightly tackled by the "Delegation of Query String" paragraph above. But I think a concept must clearly define how different storage engine implementations are supported, even if we only start with MySQL related implementations. Or does PDO fully take care of this?

Truffer, Oskar [otruffer], Jan 17, 2016:
PDO does not fully take care of this. I think the decision is up to the implementer of the new database connection. Let's say we have a PDO implementaition for MySQL InnoDB. If we want to implement MySQL MyISAM then we will most likely inherit from the existing PDO Database. On the other hand if we want to reimplement Oracle using native PHP functions we will most likely implement a new class using the database interface, without any inheritance. If we get a testsuite for the database, as we should seek funding for, then any implementation of the database interface is easily testable.

I think the architecture should quite simply be: Implement the interface and delegate query string utilities to a different class for other implementations to use it. If implemented add it to the factory with an ID like: pdo-mysql-myisam, php-oracle, pdo-postgresql. "library-sqldialect[-subsqldialect]".

The issue left is: What to do with capabilities that one database engine has and another doesn't? Locking, Fulltext indizes, etc. My suggestion would be to keep the implemented features of the database at the current database interface. If an sql dialect does not support any of the features you have to implement a workaround, in order to still support the database interface. If we later want to add any functionality to the interface, we will have to make sure that all given dialects can support the feature or that a workaround is implementable.

JourFixe, ILIAS [jourfixe], Jan 18, 2016: We would like to have the topic ‘Authentication’ as a separate FW page. We decided for a redesign of ilDB based on PDO that keeps all interfaces and supports all database engines that currently can already be used (PostgreSQL, Oracle, MariaDB, InnoDB, MyISAM). We continue the discussion tomorrow as part of the PHP7 workshop. We schedule an ilPDO implementation with patched authentication for 5.2.

4 Implementation

Interface
With the Implementation of the PDO-Support in ILIAS 5.2, we introduced the interface "ilDBInterface" in Services/Database/interfaces/interface.ilDBInterface.php which descripes the public interface of both the PDO-based and the MDB2-based Database-Implementations.

MDB2
The old DB-Implementations using MDB2 are still part of the core-code but – with the exception of Oracle – none of the implementaions are used.

Authentication
The Authentication has been refactored using the DB-Interface and was not part of this Feature. It's tacked by Compatibility to PHP7

Testing
To make sure the old implementation and the new with PDO are acting the same way, implementaion tests have been written in
Services/Database/test/Implementations

Test Cases

This Feature will be tested using any of the Testrail-Testcases

Approval

Approved at 25.08.2016 by Amstutz, Timon [amstutz].