Awaiting flames
Why do we support (or do we?) non-mysql database installations?? I just don't get it. Apache-MySql-PHP is a very good industry standard - it even has an acronym. There are mountains of other PHP projects that only support this configuration. It seems to me that it is a considerable waste of effort going down the route of supporting anything other than MySql. Interestingly I have noticed in the support forums that there is a steady number of complaints about problems with postgres (usually unanswered), so even that seems to be getting a bit shaky.
Awaiting flames
Awaiting flames
I wrote this in the middle of the night and on a re-read it sounds a bit bitchy, so may I take the opportunity to backtrack a bit. I think I am concerned that supporting multiple databases spreads resources around for questionable benefit. I keep reading questions like "my boss says we must use msql, or interbase, or whatever" and that just strikes me that there is a lack of understanding somewhere. Moodle does not require an enterprise grade database installation, even a huge Moodle install will not put much demand on a mysql database, so it is unlikely to require specialist administration (witness the very very low number of mysql related support requests). I wonder if we have seriously though about the benefit/risks of going mysql *only*?
I won't take it as a flame, more a chance to expound a view on what I take the position to be from reading between the lines (and some of the code)
Some of the reasons for cross database support are "political" and some practical. On the "political" front, the ability to support commercial databases simply adds credibility. Despite its undoubted merits MySQL still has a slight aura of the less professional hackerish web projects. I am not positing that as my own argument, just a comment on how it is perceived in some quarters. Also many organisations are extreemly reluctant to add another database to their portfolio of supported systems. For example where I suspect that where I work they support MS SQL and Oracel. No matter how good/free MySQL is they would see supporting it as an additional barrier to adoption.
Even if they did decide to adopt MySQL the data in moodle would then be relativly isolated in a different database, by comparison with having it all in Oracle/MS SQL or whatever. Note that the purchase price of database software is often only a minor component of the total cost of ownership.
One of the reasons Moodle can support multiple databases without extreeme pain is that it uses the php adoDB database abstraction layer which in theory means it is possible to use alternative databases in a "drop in manner". As I am sure Martin would confirm,
"in theory there is no difference between theory and practice, but in practice there is".
Postgres is popular in the higher education sector and I suspect its support will improve as Moodle becomes the de-facto standard VLE in that area. By the way Postgres is currently in beta with a native Windows version and a very nice admin tool which should boost its popularity.
MySQL is still generally considered to be somewhat short on the high end features of databse systems such as stored procedures, full SQL compliance and transaction processing (commit/rollback support). In the last year or so MySQL has addressed some of these issues. However if these features are important you can look at MySQL where the minority of installations use these features which have existed for a year or so or you can go the Oracle/PostGres/MS SQL route where they have been supported for a decade or so.
Don't take any of the above as an argument against using MySQL. I recently had the choice on some hosting of selecting PostGres or MySQL, both of which I am familiar from working in professional environments. For a variety of reasons I went the MySQL path.
Some of the reasons for cross database support are "political" and some practical. On the "political" front, the ability to support commercial databases simply adds credibility. Despite its undoubted merits MySQL still has a slight aura of the less professional hackerish web projects. I am not positing that as my own argument, just a comment on how it is perceived in some quarters. Also many organisations are extreemly reluctant to add another database to their portfolio of supported systems. For example where I suspect that where I work they support MS SQL and Oracel. No matter how good/free MySQL is they would see supporting it as an additional barrier to adoption.
Even if they did decide to adopt MySQL the data in moodle would then be relativly isolated in a different database, by comparison with having it all in Oracle/MS SQL or whatever. Note that the purchase price of database software is often only a minor component of the total cost of ownership.
One of the reasons Moodle can support multiple databases without extreeme pain is that it uses the php adoDB database abstraction layer which in theory means it is possible to use alternative databases in a "drop in manner". As I am sure Martin would confirm,
"in theory there is no difference between theory and practice, but in practice there is".
Postgres is popular in the higher education sector and I suspect its support will improve as Moodle becomes the de-facto standard VLE in that area. By the way Postgres is currently in beta with a native Windows version and a very nice admin tool which should boost its popularity.
MySQL is still generally considered to be somewhat short on the high end features of databse systems such as stored procedures, full SQL compliance and transaction processing (commit/rollback support). In the last year or so MySQL has addressed some of these issues. However if these features are important you can look at MySQL where the minority of installations use these features which have existed for a year or so or you can go the Oracle/PostGres/MS SQL route where they have been supported for a decade or so.
Don't take any of the above as an argument against using MySQL. I recently had the choice on some hosting of selecting PostGres or MySQL, both of which I am familiar from working in professional environments. For a variety of reasons I went the MySQL path.
I'd like to concur with Marcus here. Supporting 'heavyweight' database backends is a must. On our campus we use Oracle & Postgres and Moodle would just have been a 'no' if the Sysadmin had had to install, configure & maintain Mysql. Besides which Mysql is undoubtedly "immature" - it's only recently gained A.C.I.D - and database backends are where rock solidness is paramount.
And btw, it does irk when we find that 'such & such module only works with Mysql'. Can't think of an example right now but it's occurred a couple of times.
Come to think of it, why support anything other than Internet Explorer? After all, it's the most popular browser .....
And btw, it does irk when we find that 'such & such module only works with Mysql'. Can't think of an example right now but it's occurred a couple of times.
Come to think of it, why support anything other than Internet Explorer? After all, it's the most popular browser .....
There must be a consensus among the developers to support a specific database. The problem is that the Moodle core and all modules must be tested with each database, because some sql code is very specific, have a look at Glossary module for example. Of course all developers must have the database installed on their test boxes.
At present most of the developers use MySQL, not everybody has access to PostgreSQL. This may change after the release of version 8.0.
What you can do, if you want support for Oracle? Find somebody who will test all the new code and make the necessary tweaks. It should not take more than several hours a week after the initial conversion, which of course will be his/her task too
skodak
At present most of the developers use MySQL, not everybody has access to PostgreSQL. This may change after the release of version 8.0.
What you can do, if you want support for Oracle? Find somebody who will test all the new code and make the necessary tweaks. It should not take more than several hours a week after the initial conversion, which of course will be his/her task too
skodak
Yes! This is a very good point very well made.
Mysql 'immature'? No, sorry, very wrong...
The fact that it is not ACID compliant is neither here nor there in the current context. Moodle does not use these fancy features! Yes, if I where running a vital financial application that required transactions et. al. then, of course, MySql does not fit the bill, but it's not what MySql was designed for. It is optimised for relative simple (and I use that with caution) applications that require high speed and excellent reliability (eg NASA uses it).
In my previous life I was responsible for a MySql installation for a huge database at the centre of a multi $million project for a household name company. The MySql database never (and I understand still hasn't) put a foot wrong - ever.
My argument is simply that Moodle does not really use a database in the true sense of the word at all. The database is just a convenient local data store. For this mysql is ideal and the out-of-the-box configuration (that was probably preinstalled on your Linux server) will do perfectly fine. Benefits - ease of development and an increase in the reliability of Moodle as a whole.
Your remark about Internet Explorer reinforces the fact that you appear to have missed my point, which I could probably have made better. This has nothing to do with popularity, you are not asked to choose just one browser when you install Moodle but you are required to choose just one database.
However, all just my $00.02.
The fact that it is not ACID compliant is neither here nor there in the current context. Moodle does not use these fancy features! Yes, if I where running a vital financial application that required transactions et. al. then, of course, MySql does not fit the bill, but it's not what MySql was designed for. It is optimised for relative simple (and I use that with caution) applications that require high speed and excellent reliability (eg NASA uses it).
In my previous life I was responsible for a MySql installation for a huge database at the centre of a multi $million project for a household name company. The MySql database never (and I understand still hasn't) put a foot wrong - ever.
My argument is simply that Moodle does not really use a database in the true sense of the word at all. The database is just a convenient local data store. For this mysql is ideal and the out-of-the-box configuration (that was probably preinstalled on your Linux server) will do perfectly fine. Benefits - ease of development and an increase in the reliability of Moodle as a whole.
Your remark about Internet Explorer reinforces the fact that you appear to have missed my point, which I could probably have made better. This has nothing to do with popularity, you are not asked to choose just one browser when you install Moodle but you are required to choose just one database.
However, all just my $00.02.
I can concur, I would not call MySQL immature. It is very mature and stable at what it does. What it does could be described as putting a limited SQL front end onto a file storage system. If that meet your needs there is just about no alternative.
For the small to medium installations then mySQl is fast and efficient. However using mySQL in larger installations will create issues. That is not within my/your/our control. It is the usual politics associated with large IT departments. This will also occur whatever the database is.
IMHO it's bext to develop a cross-platform (for OS), cross database strategy. I believe that Martin has done well so far, and with plans in place, will address most of the issues that will arise.
Perhaps any new Module could be distributed to a "Database forum" specifically set up to test any dedicated SQL.
How large is large I wonder? We have over 5000 employees and an Oracle site licence (amongst other big-time DBs). However, when we installed Moodle we stuck MySql on the box along with the Moodle software - because it works!
The issue of "large", as Howard mentions, can have many dimensions. Most Moodle installs are quite new, and the amount of material uploaded quite small. I'd be interested to see how mySQL performs when the database size grows beyond 2G, 4G ... etc.
There is also the issue of how well it performs in HA (Highly Available) configurations. Wearing a systems hat, my initial assessment is that mySQL is still a new kid on the block with many "essential" features yet to be implemented.
There are issues such as online backups etc, which get to be an issue for IT support, which more mature databases handle better. So it's not just design considerations that come into the discussion.
There is also the issue of how well it performs in HA (Highly Available) configurations. Wearing a systems hat, my initial assessment is that mySQL is still a new kid on the block with many "essential" features yet to be implemented.
There are issues such as online backups etc, which get to be an issue for IT support, which more mature databases handle better. So it's not just design considerations that come into the discussion.
I used to work* for http://www.ananova.com. They claim to be (and I do not doubt it) the second largest purveyor of web based news from the UK, after the BBC, who rank up there on a global scale. One of their claims to fame is that when the events of Sept 11 2001 placed huge demands on their web servers, they were one of the very few sites that did not collapse under the load.
Ananova use mysql for public facing web requests. I think that you can take it that MySQL can deal with larger installations in terms of request load.
(*well I was employed by them anyway)
p.s. Technically I still prefer Postgresql
Ananova use mysql for public facing web requests. I think that you can take it that MySQL can deal with larger installations in terms of request load.
(*well I was employed by them anyway)
p.s. Technically I still prefer Postgresql
Slashdot serves about 3 million page views per day with mySQL.
I don't want this discussion to degenerate into a debate on which database is better ... etc, however I feel that it should be recognised that, as the expression goes, there are "horses for courses".
In the web server arena, mySQL has claimed the territory fair and square. It's speed comes from a simple, effective design, and minimal features. It is rapidly gaining in it's feature set, and is well supported. For the most that's it, end of story.
However, for Moodle to be accepted easily worldwide, and in a variety of environments, there has to be flexibility in database access. Even if the reasons are purely political, they are still reasons that must be considered.
There is also a philisophical issue of "strength in diversity". If Moodle was tied at the umbilical cord to mySQL alone (and it's feature set), then there would be a danger of being encumbered by any underlying fault or failure in the mySQL design. It could well become "inbred". The world realised this in genetics eons ago, and the same principle applies to software design.
There are exciting developments in all database projects, and I know that the Firebird project for one has some things in the oven that will knock y'er socks off. PostgreSQL likewise. And hey, who knows what MS has cooking with MSSQL?
I fully endorse the decision to use the ADODB library library which supports most database popular engines, and the use of XML metadata (under development) which will allow people to use their DB of choice.
well you could also throw it the other way around.. why not develop moodle for an enterprise level database like Postgres or Oracle? Why develop for a database that is now trying to play catchup to the big guns?
Of course moodle right now might not need all those features but what if in the future there is a need would you want to wait untill mysql comes out with these features or would you want to be able to do what you want when you want it..
By keeping the doors open to other databases you keep your options open.
anyways lets stop the fighting about what database to use.. what would be cool is if we can get moodle to be database independent and hopefully use something like this:
cjdbc rux !!
Of course moodle right now might not need all those features but what if in the future there is a need would you want to wait untill mysql comes out with these features or would you want to be able to do what you want when you want it..
By keeping the doors open to other databases you keep your options open.
anyways lets stop the fighting about what database to use.. what would be cool is if we can get moodle to be database independent and hopefully use something like this:
cjdbc rux !!
In reply to Alexis Maldonado
Re: Databases: Non MySql databases - why bother??
by Martin Dougiamas -
Moodle does do this already (always has). We use ADOdb as an abstraction layer. The only place that new databases need individual support is in the creation of new tables (from the schema), and there is even a solution for that (XML Data dictionaries) we have just not got around to implementing yet.
I couldn't remember if it was ADOdb or PEAR::DB. They are both good abstraction alternatives, IMO. And PEAR is getting a lot more attention these days especially with Daniel Convissor involved. Also, PDO (PHP Data Objects) are just around the corner
Thanks for making an apparent "issue" the non-issue it really is.
Thanks for making an apparent "issue" the non-issue it really is.
What just happened to wikipedia has got me thinking about moving to postgres or oracle.
January or February 2005 issue of MacTech, the magazine for Mac developers, has a nice comparison of MySQL and ProgreSQL. They are not trying to decide which is better but go through pros and cons, strengths and weaknesses of each, to help developers decide which is better for their specific projects.
I really agree with those who say that Moodle should stay database-agnostic. That is the way to go even if it means more work.
I really agree with those who say that Moodle should stay database-agnostic. That is the way to go even if it means more work.
This thread is already quite long and old but I'll throw in my two cents. The reason we need to run Moodle on something other than MySQL and PostgreSQL is interoperability and enterprise standards. At our company our standard database is SQL Server. This is the mandate from on high and in the corporate world you can work to change standards but you don't do so at the drop of a hat.
In our case we needed Moodle to integrate with a system running on SQL Server. Originally I had setup a job to update certification scores by querying the MySQL server every 15 minutes. Unfortunately the best ODBC driver available had memory/resource leak issues and every 2-3 weeks SQL Server would no-longer be able to spawn new threads internally, causing one of our production databases to go down in the middle of the day. The problem was entirely resolved when we stopped using the MySQL ODBC driver.
I then tried PostgreSQL but it couldn't get through a DTS transfer from MSSQL to PostgreSQL without the ODBC driver crashing multiple times. MySQL and postgreSQL may work wonderfully with some configurations but others are just not stable.
I'm sure both systems are great if you remove Microsoft from the loop but that's just not possible in this environment.