Hi,
Has anybody started or tried implementing DB2 as a database for moodle?
If yes, please tell me about your efforts and where you are.
If not, who might be interested in using DB2 as a database for moodle?
Thanks for your comments!
Christian
because moodle uses adodb and adodb has db2 support it should be doable.
We are planning to implement this.
Has anyone made any progress towards this front that they would be willing to share?
We will post our mods back to the community to use.
Has anyone made any progress towards this front that they would be willing to share?
We will post our mods back to the community to use.
Hi guys,
I'm also interested in implementing moodle with DB2. I am a novice moodle developer.
We can get started guys. Is there any way i can help u out. I am working on fedora with DB2 and XAMPP.
I'm also interested in implementing moodle with DB2. I am a novice moodle developer.
We can get started guys. Is there any way i can help u out. I am working on fedora with DB2 and XAMPP.
You need to decide if you need to work on Moodle 1.9, or whether you can wait for Moodle 2.0, the reason being that that DB abstraction layer changed a bit in Moodle 2.0, so you would need to do slightly different things in each place.
Most of the SQL in Moodle is already quite cross-db compatible. It all works on MySQL and Postgres, and most of it works on MS SQL, and Oracle. To achive that, we have a few compatibility methods of the moodle_database class to generate some bits of SQL, like concatenate expressions. You would need to create a new subclass. (It is methods of the moodle_database class in Moodle 2.0. In Moodle 1.9 they are functions in dmllib.php containing switch statements. You would need to add additional cases.)
Then there is the install/upgrade system, which we call XMLDB. You would need to define a SQL DDL generator class for DB2 in that framwork. That is conceptually similar between 1.9 and 2.0, but there are differences again.
Anyway, it should be quite possible to do, most of the work will be testing all the funcitonality to ensure it all works.
Most of the SQL in Moodle is already quite cross-db compatible. It all works on MySQL and Postgres, and most of it works on MS SQL, and Oracle. To achive that, we have a few compatibility methods of the moodle_database class to generate some bits of SQL, like concatenate expressions. You would need to create a new subclass. (It is methods of the moodle_database class in Moodle 2.0. In Moodle 1.9 they are functions in dmllib.php containing switch statements. You would need to add additional cases.)
Then there is the install/upgrade system, which we call XMLDB. You would need to define a SQL DDL generator class for DB2 in that framwork. That is conceptually similar between 1.9 and 2.0, but there are differences again.
Anyway, it should be quite possible to do, most of the work will be testing all the funcitonality to ensure it all works.
Thanks Tim, I would like to start working on with v1.9 itself. Let me do those modifications when v2.0 is out.
I will get started with the current release itself. Whats the exact steps i need to do to acheive this. Tim, Are there any changes which needs to be made for the code to be executed in Linux(Fedora 10) with xampp. I'd like to test the functionalities in linux with db2v9.5 and mysql.
I will get started with the current release itself. Whats the exact steps i need to do to acheive this. Tim, Are there any changes which needs to be made for the code to be executed in Linux(Fedora 10) with xampp. I'd like to test the functionalities in linux with db2v9.5 and mysql.
Moodle runs fine under any xampp platform. I would say that the first step is to got a Moodle + MySQL install running on a server where you can safely hack around with the code using your favourite PHP development tools.
Then, to really get a picture of what you need to do, I would do some full-text searches (grep) of the code for things like 'mysql' and 'postgres' to make sure you have found all the odd corners where database specific code lurk. In theory it should all be in lib/dmllib.php, lib/ddllib.php and lib/xmldb/classes/generators/. And you can expect a few hooks in the install system where the database choice is set and checked.
Also, read the xmldb docs on Moodle docs, which explains how it is supposed to work.
However, I would not be suprised if there were a few odd corners to be dealt with.
The person who built most of the database abstraction layer is Eloy (Stronk7). He is normally helpful.
Then, to really get a picture of what you need to do, I would do some full-text searches (grep) of the code for things like 'mysql' and 'postgres' to make sure you have found all the odd corners where database specific code lurk. In theory it should all be in lib/dmllib.php, lib/ddllib.php and lib/xmldb/classes/generators/. And you can expect a few hooks in the install system where the database choice is set and checked.
Also, read the xmldb docs on Moodle docs, which explains how it is supposed to work.
However, I would not be suprised if there were a few odd corners to be dealt with.
The person who built most of the database abstraction layer is Eloy (Stronk7). He is normally helpful.
Hi Rama,
if you are interested into writing such DB2 implementation, I'd suggest you to do that straight for Moodle 2.0.
In Moodle 2.0 we (Petr Skoda mainly) have rewritten 120% (is that possible?
) of the XMLDB stuff, separating DML and DDL stuff, and, most important, going away from ADOdb and using native drivers instead when possible. Also 2.0 includes one complete battery of tests that will allow you to check how your implementation is going and where it's failing.
So, sincerely I think that any work performed in 1.9 (ADOdb dependent, old DML functions...) will be a waste of time, from the perspective of having to rewrite it again for 2.0.
You'll find some interesting information about XMLDB in general here:
http://docs.moodle.org/en/XML_database_schema
And up-to-date information about the new DB abstraction layer (and changes) coming with 2.0 here:
http://docs.moodle.org/en/Development:DB_layer_2.0
Note that the 2.0 layer is pretty stable right now (it's being used by all developers daily), so you can consider it as final if you want to ignite any sort of development with it.
Hope this helps, ciao
if you are interested into writing such DB2 implementation, I'd suggest you to do that straight for Moodle 2.0.
In Moodle 2.0 we (Petr Skoda mainly) have rewritten 120% (is that possible?
So, sincerely I think that any work performed in 1.9 (ADOdb dependent, old DML functions...) will be a waste of time, from the perspective of having to rewrite it again for 2.0.
You'll find some interesting information about XMLDB in general here:
http://docs.moodle.org/en/XML_database_schema
And up-to-date information about the new DB abstraction layer (and changes) coming with 2.0 here:
http://docs.moodle.org/en/Development:DB_layer_2.0
Note that the 2.0 layer is pretty stable right now (it's being used by all developers daily), so you can consider it as final if you want to ignite any sort of development with it.
Hope this helps, ciao
In Moodle 2.0 we (Petr Skoda mainly) have rewritten 120% (...) using native drivers instead
Wooohoo! Great! Good performance? I'll want to test-drive that asap...
think that any work performed in 1.9...
...maybe their schedule is for before 2.0 is ready
As far as I know, the DB2 dialect of SQL is relatively similar to Oracle / Postgres, so in a few days of work someone could get an almost fully working 1.9 on DB2. And then test test test to make sure no corner cases or odd bits of code cause trouble.
It'd be a waste from a 2.0 PoV, but if they want to deploy in a month's time on DB2... good for them!
In the same spirit as this thread, we, a team of four undergraduate Computer Science students at California State University, Long Beach, are currently undertaking to add support to Moodle for IBM DB2. The DB2 Express-c platform is our target database, and we wish to extend, based on the 1.9 branch, the database functionality of Moodle so that it can use DB2.
We welcome input on how best to approach this task, and how our work might be included in the Moodle core. Based on our exploratory research, we have learned that the bulk of the modifications will be in the DML, DDL, and XMLDB.
Our approach, so far, has been to first obtain a DB2 mirror of a working MySQL instance (via migration tools), and then re-configure Moodle manually to try to use the pre-populated DB2 database (this was reasonable to us given that ADOdb supports DB2, and that is Moodle's present database abstraction layer, to our knowledge). This task has been accomplished, in a very rudimentary way, so we now plan to check unit tests to direct our "bug"-fixing of the Moodle database libraries with respect to DB2, iron out any remaining kinks that hinder full running functionality, and finally ensure that the installer functions properly. We recognize that any modifications to the ADOdb DB2 driver will need to be contributed "upstream" to its authors, with whom we have already made contact.
Our project timeline is limited to the next couple months, primarily, as our project class ends December 17, 2009. It is possible that we can continue to contribute, in a lesser capacity, after that date, but we hope that if we can finish the task, or mostly finish it, that the community and any interested team members will finish the work to make DB2 compatible with Moodle.
A question of concern to us at this time, looking forward, is how to include our code into the Moodle core. We are aware of the Tracker/forum process; what else should we know about contributing to the core?
We welcome input on how best to approach this task, and how our work might be included in the Moodle core. Based on our exploratory research, we have learned that the bulk of the modifications will be in the DML, DDL, and XMLDB.
Our approach, so far, has been to first obtain a DB2 mirror of a working MySQL instance (via migration tools), and then re-configure Moodle manually to try to use the pre-populated DB2 database (this was reasonable to us given that ADOdb supports DB2, and that is Moodle's present database abstraction layer, to our knowledge). This task has been accomplished, in a very rudimentary way, so we now plan to check unit tests to direct our "bug"-fixing of the Moodle database libraries with respect to DB2, iron out any remaining kinks that hinder full running functionality, and finally ensure that the installer functions properly. We recognize that any modifications to the ADOdb DB2 driver will need to be contributed "upstream" to its authors, with whom we have already made contact.
Our project timeline is limited to the next couple months, primarily, as our project class ends December 17, 2009. It is possible that we can continue to contribute, in a lesser capacity, after that date, but we hope that if we can finish the task, or mostly finish it, that the community and any interested team members will finish the work to make DB2 compatible with Moodle.
A question of concern to us at this time, looking forward, is how to include our code into the Moodle core. We are aware of the Tracker/forum process; what else should we know about contributing to the core?
I think that we will not be interested in DB2 in core unless it is for Moodle 2.0?
Why are you choosing to work on 1.9?
(And, did you see User_talk:Paul_Nguyen.)
Why are you choosing to work on 1.9?
(And, did you see User_talk:Paul_Nguyen.)
Why hack on 1.9.x patches...? Maybe because it's useful for ends users nowish
Yes, crazy people have valid reasons for doing major new feature work on Moodle 1.9 
However, the default position is that major new features go into HEAD, which is why I thought it was worth asking the question.
Now why don't you go back to hacking major features into 1.9 for XS, and I'll go back to hacking them into 1.9 for the OU
And, the real issue here is that major hacking on the DB layer in 1.9 really is a waste. It would by like major hacking on the wiki or workshop modules, both of which are also being thrown away.
However, the default position is that major new features go into HEAD, which is why I thought it was worth asking the question.
Now why don't you go back to hacking major features into 1.9 for XS, and I'll go back to hacking them into 1.9 for the OU
And, the real issue here is that major hacking on the DB layer in 1.9 really is a waste. It would by like major hacking on the wiki or workshop modules, both of which are also being thrown away.
Mr. Hunt and Mr. Langhoff, thank you both for your prompt replies. Unfortunately, I was not watching my own talk page carefully enough to see the comment from Tim Hunt a few days before.
We received this request for DB2 compatibility indirectly from Moodlerooms, and it has been our understanding in the last month that this work should be done on v1.9, rather than 2.0. We were also strongly encouraged to work to get DB2's ADOdb compliance level raised from 'C' to 'A' - a move which, in the 1.9 database architecture, would likely solve the majority of the roadblocks to DB2 compatibility with Moodle.
The team has two questions, which follow from this, and are related:
(1) Is it possible that work on DB2 support in Moodle 1.9 will ever be included?
(2) We noticed that there is still a directory for ADOdb in the 2.0 source; how proper or hacked-together would our 1.9 DB2 work appear if it were used to then make Moodle 2 compatible with DB2? Or, is ADOdb on its way out at this point?
Again, our investigative work and the circumstances of our project have led us to pursue a 1.9 contribution, as opposed to 2.0, and to change the scope and direction of our project would affect several other groups out there.
Do our concerns merit an exception to what seems to be a prevailing 'no' to 1.9 changes, specifically in the database layer?
Why are you choosing to work on 1.9?
We received this request for DB2 compatibility indirectly from Moodlerooms, and it has been our understanding in the last month that this work should be done on v1.9, rather than 2.0. We were also strongly encouraged to work to get DB2's ADOdb compliance level raised from 'C' to 'A' - a move which, in the 1.9 database architecture, would likely solve the majority of the roadblocks to DB2 compatibility with Moodle.
The team has two questions, which follow from this, and are related:
(1) Is it possible that work on DB2 support in Moodle 1.9 will ever be included?
(2) We noticed that there is still a directory for ADOdb in the 2.0 source; how proper or hacked-together would our 1.9 DB2 work appear if it were used to then make Moodle 2 compatible with DB2? Or, is ADOdb on its way out at this point?
Again, our investigative work and the circumstances of our project have led us to pursue a 1.9 contribution, as opposed to 2.0, and to change the scope and direction of our project would affect several other groups out there.
Do our concerns merit an exception to what seems to be a prevailing 'no' to 1.9 changes, specifically in the database layer?
Hello,
1) the chance is close to zero - new features are not added into stable branches (well sometimes there are exceptions, but they never include changes all over the codebase)
2) adodb is not used in our new DML/DDL layers at all, soon all the current legacy adodb drivers will be deleted
The main problem here is that adodb does not have all the features we need in the new DB drivers - such as session locking, table column info, bound query params.
Implementing DB2 support in 1.9 looks like a waste of time because you can reuse only a tiny fraction of that in 2.0, also implementing new drivers in 2.0 is much, much, much easier - in fact it was one of the main goals there.
I personally think you should start with DB2 support in 2.0dev because we have much nicer abstraction there, we have all the needed unittests. I think it should take you roughly a week to get something that works. The biggest danger is that the DB2 engine will not understand our SQL queries which would be a show stopper - we are not going to reduce the SQL subset even more just to get support for another DB engine, sorry (it was really painful to get the *** Oracle to work).
If the 2.0 driver works you may start thinking about backporting to 1.9.x, you would probably need to hack very many places, so I would advise you to use git if you want to maintain it for a longer time. Please do not expect your changes will be included in stable branch.
Good luck!
Petr
1) the chance is close to zero - new features are not added into stable branches (well sometimes there are exceptions, but they never include changes all over the codebase)
2) adodb is not used in our new DML/DDL layers at all, soon all the current legacy adodb drivers will be deleted
The main problem here is that adodb does not have all the features we need in the new DB drivers - such as session locking, table column info, bound query params.
Implementing DB2 support in 1.9 looks like a waste of time because you can reuse only a tiny fraction of that in 2.0, also implementing new drivers in 2.0 is much, much, much easier - in fact it was one of the main goals there.
I personally think you should start with DB2 support in 2.0dev because we have much nicer abstraction there, we have all the needed unittests. I think it should take you roughly a week to get something that works. The biggest danger is that the DB2 engine will not understand our SQL queries which would be a show stopper - we are not going to reduce the SQL subset even more just to get support for another DB engine, sorry (it was really painful to get the *** Oracle to work).
If the 2.0 driver works you may start thinking about backporting to 1.9.x, you would probably need to hack very many places, so I would advise you to use git if you want to maintain it for a longer time. Please do not expect your changes will be included in stable branch.
Good luck!
Petr
Well, upgrading ADOdb's support for DB2 from C to A (whatever that means ) is a valuable contribution to the world of open source, irrespective of any thing else, so don't let us put you off doing that.
I don't thing we would take DB2 support into the official release of Moodle 1.9 unless there was also a working version of 2.0. Otherwise people who installed 1.9 on DB2 would be screwed when they wanted to upgrade.
However, a patch that people could apply if they really wanted 1.9 on DB2, added to the Modules and Plugins database, would be worth something.
I guess that adding support for a new database really comprises the following tasks (I will discuss 1.9/2.0 differences in each one):
1. Make the install process support the new database type.
This is basically a matter of creating a new generator class in http://cvs.moodle.org/moodle/lib/ddl/. The only differences between 1.9 and 2.0 is that some related classes were renamed to better match the Moodle coding guidelines, so XmldbTable -> xmldb_table. So work done for 1.9 should be easy to carry over to 2.0.
2. Make the database abstraction layer (dmllib) support the new database type.
In 1.9 this means making sure ADOdb supports the new DB type well enough, and probably changing where the DB connection is initialised to accept 'db2' as a valid option.
In 2.0 this means writing new subclasses db2_moodle_native_database.php and db2_moodle_native_recordset.php in http://cvs.moodle.org/moodle/lib/dml/. Note that Moodle 2.0 now has an extensive set of unit tests to support writing a new database-specific subclass of moodle_database, so it should be easy to know when you are done with this.
While there are still classes like postgres7_adodb_moodle_database.php in that folder, they are scheduled for removal. (We may keep ADOdb around, but only for things like the database enrolment plugin that needs to connect to other systems to synchronise other data with Moodle.) Basically, Eloy got bored of finding and fixing bugs in ADOdb, and also are new native classes are a small but significant bit faster that using ADOdb.
Actually, the more important reason was that ADOdb did not let us implement temporary tables, or session locking for database sessions. So adodb_moodle_database.php and its subclasses probably do not pass all the unit tests, will not be maintained, indeed will probably be deleted soon, and so are not something to build on.
So, in this area there is little commonality between 1.9 and 2.0, but the extra work to support 2.0 is easy to quantify (implement db2_moodle_native_database.php to pass all tests).
3. Review and test all SQL throughout Moodle and ensure that it works on DB2.
This is probably the biggest amount of work. As you can tell from the fact that Moodle has supported MSSQL and Oracle since Moodle 1.7, but we are still finding SQL in obscure corners of Moodle that breaks on one or the other and needs to be fixed.
The one thing you can say for certain about the SQL standard is that all databases implement it differently.
And as the MSSQL experience shows, it is impossible to test all of Moodle, I guess you need to aim at a core subset (admin, course, user, mod/forum, ...). Since Moodle already works on four databases, and we already have a lot of sql_... functions to encapsulate the differences we have found so far, one would hope that once you have extended the sql_... functions to support DB2, then most things will work. However, if DB2 has different foibles, and needs new sql_... functions, introducing them everywhere necessary will be a lot of work.
One example is the AS keyword. The rules on Development:Database are the only ones that work on all 4 of our currently supported databases.
Anyway, work in this area done in 1.9 can (and should) be merged to 2.0, but this will never be a straight merge because the database API has changed in a way that breaks automatic merges (for good reason but very annoying!) Fortunately, it is easy to merge by hand.
Phew! that was long. I hope it makes sense.
I don't thing we would take DB2 support into the official release of Moodle 1.9 unless there was also a working version of 2.0. Otherwise people who installed 1.9 on DB2 would be screwed when they wanted to upgrade.
However, a patch that people could apply if they really wanted 1.9 on DB2, added to the Modules and Plugins database, would be worth something.
I guess that adding support for a new database really comprises the following tasks (I will discuss 1.9/2.0 differences in each one):
1. Make the install process support the new database type.
This is basically a matter of creating a new generator class in http://cvs.moodle.org/moodle/lib/ddl/. The only differences between 1.9 and 2.0 is that some related classes were renamed to better match the Moodle coding guidelines, so XmldbTable -> xmldb_table. So work done for 1.9 should be easy to carry over to 2.0.
2. Make the database abstraction layer (dmllib) support the new database type.
In 1.9 this means making sure ADOdb supports the new DB type well enough, and probably changing where the DB connection is initialised to accept 'db2' as a valid option.
In 2.0 this means writing new subclasses db2_moodle_native_database.php and db2_moodle_native_recordset.php in http://cvs.moodle.org/moodle/lib/dml/. Note that Moodle 2.0 now has an extensive set of unit tests to support writing a new database-specific subclass of moodle_database, so it should be easy to know when you are done with this.
While there are still classes like postgres7_adodb_moodle_database.php in that folder, they are scheduled for removal. (We may keep ADOdb around, but only for things like the database enrolment plugin that needs to connect to other systems to synchronise other data with Moodle.) Basically, Eloy got bored of finding and fixing bugs in ADOdb, and also are new native classes are a small but significant bit faster that using ADOdb.
Actually, the more important reason was that ADOdb did not let us implement temporary tables, or session locking for database sessions. So adodb_moodle_database.php and its subclasses probably do not pass all the unit tests, will not be maintained, indeed will probably be deleted soon, and so are not something to build on.
So, in this area there is little commonality between 1.9 and 2.0, but the extra work to support 2.0 is easy to quantify (implement db2_moodle_native_database.php to pass all tests).
3. Review and test all SQL throughout Moodle and ensure that it works on DB2.
This is probably the biggest amount of work. As you can tell from the fact that Moodle has supported MSSQL and Oracle since Moodle 1.7, but we are still finding SQL in obscure corners of Moodle that breaks on one or the other and needs to be fixed.
The one thing you can say for certain about the SQL standard is that all databases implement it differently.
And as the MSSQL experience shows, it is impossible to test all of Moodle, I guess you need to aim at a core subset (admin, course, user, mod/forum, ...). Since Moodle already works on four databases, and we already have a lot of sql_... functions to encapsulate the differences we have found so far, one would hope that once you have extended the sql_... functions to support DB2, then most things will work. However, if DB2 has different foibles, and needs new sql_... functions, introducing them everywhere necessary will be a lot of work.
One example is the AS keyword. The rules on Development:Database are the only ones that work on all 4 of our currently supported databases.
Anyway, work in this area done in 1.9 can (and should) be merged to 2.0, but this will never be a straight merge because the database API has changed in a way that breaks automatic merges (for good reason but very annoying!) Fortunately, it is easy to merge by hand.
Phew! that was long. I hope it makes sense.
Thank you, all. We have decided to go with the patch approach and have been working on it slowly for a couple weeks now. We hope to be done with most of the coding by the first week of November and proceed with testing and fixing any problems in the month that follows.
Well, some time has passed since my last post; we lost a team member, but have completed what we think may be a majority of the code required for Moodle to operate with DB2.
We are completing three remaining failures indicated in unit tests, and we would like to begin testing the working functionality of Moodle. What would you suggest we use to verify operational functionality, besides the information provided in http://docs.moodle.org/en/Development:Tests? We have heard about some JavaScript tools that may allow us to perform more automated "driving" of the application... any thoughts about these?
Thanks in advance for your help.
-Paul Nguyen
We are completing three remaining failures indicated in unit tests, and we would like to begin testing the working functionality of Moodle. What would you suggest we use to verify operational functionality, besides the information provided in http://docs.moodle.org/en/Development:Tests? We have heard about some JavaScript tools that may allow us to perform more automated "driving" of the application... any thoughts about these?
Thanks in advance for your help.
-Paul Nguyen
Selenium is quite a nice tool for testing web applications. However, to automate the testing of an application as complex as Moodle must be quite tricky, judging by the number of people who have tried to start using Selenium on Moodle in a systematic way and failing.
For the purposes of your project, you may decide to do these integration tests manually.
For the purposes of your project, you may decide to do these integration tests manually.
Hi Tim,
apart from DevelopmentTests that Paul mentioned, do you know of any other tests that can be used to further verify the work that has been done
thanks
Mario
apart from DevelopmentTests that Paul mentioned, do you know of any other tests that can be used to further verify the work that has been done
thanks
Mario
As I said above, test Moodle manually, to see if it actually works.
Well, that's what we do...
Of course, as Paul is asking, the point is to make it work on 1.9.x for immediate needs (and paying customers!) and also make a patch for 2.0.
With the major changes in 2.0, it is not a trivial thing, but definitely worth doing.
Of course, as Paul is asking, the point is to make it work on 1.9.x for immediate needs (and paying customers!) and also make a patch for 2.0.
With the major changes in 2.0, it is not a trivial thing, but definitely worth doing.