18 May 2010, 10:21

Hi all,

I appreciate this question has been asked before (e.g. http://mahara.org/interaction/forum/topic.php?id=392) but I was wondering if anyone since then had installed Mahara on a purely Windows server?

The issue is that the database server we have is MS SQL Server (Express) and it doesn't seem as though Mahara can access an MS SQL database?

Is that still the case?

Cheers,

Ian.

18 May 2010, 17:29

Hi Ian,

You can certainly install Mahara in an all Windows environment, others have done it.

You would have to install MySQL or Postgres on your Windows server however, since we do not yet support MSSQL.

There have been a few people asking about Oracle and MSSQL support for Mahara, but until someone comes forward with funding, it won't happen. Feel free to let Microsoft know that you'd like to be able to use Mahara with their database, they were the ones who funded MSSQL support in Moodle ;)

Cheers,

Francois

19 May 2010, 3:05

Hi Francois,

Thanks for your reply. This is probably a silly question, but surely Mahara is written in PHP, and it will be PHP that needs to talk to MS SQL? Microsoft have already implemented an API for SQL, although Moodle uses FreeTDS - which is slightly different, I suppose.

Ian.

19 May 2010, 4:38

We may attempt to add MS SQL support with our install (1.1.8), because we are considering using Microsoft's Azure SQL cloud service (depending on what the exact benefits we get with out Microsoft BizSpark membership are and what the cost breakdown will be after the benefit period is up).

So, if we decide to go that route, I can probably provide at least some code for implementing it in 1.1.8.  Our install is a major fork off the original code, but we haven't modified any of the basic database functions.

19 May 2010, 5:37

Thanks Stephen. I'm still a little bit confused with Mahara's architecture . Am I right in thinking that Mahara will only have to talk to a database via PHP? In that case don't we just need to select which type of database Mahara uses to store its data? I'm thinking that you'd do that when you install Mahara in the same way as you do when you install Moodle (I'm much more familiar with Moodle, by the way).

I'm probably asking these questions in the wrong forum .

Thanks,

Ian.

19 May 2010, 9:11

adodb does make it much easier to support multiple database types, but not all SQL statements work across all database types. SQL isn't universal. There are a few places in Mahara where there are different SQL statements for MySQL and Postgres, since each has quite a few functions that are specific to that database type.  So, someone would need to go through all the source code, check everything, and make any necessary MS SQL specific additions to the code.  It should be easy (I think), but time consuming.

And, now that I think about it... since we did a major fork off the original source, a lot of our changes wouldn't apply to the original source.  So, there goes the idea of sharing the modifications.

But, like I said, it should be easy if you want to put in the time to do it.

19 May 2010, 11:07

Ouch . To have database-specific code in there sounds horrible. Surely that was some mistake?

Ian.

19 May 2010, 21:57

Search specifically needs varying SQL statements for each database type. MySQL and PostgreSQL both use the LIKE operator, but in PostgreSQL it is case sensitive. So, for the PostgreSQL query, the ILIKE operator needs to be used to make it case insensitive, but ILIKE isn't supported by MySQL.

Though it isn't really done in Mahara, you could modify the search capabilities to really maximize the power of the database being used.  For example, in MySQL you can't sort results by relevance when using the InnoDB table type (InnoDB supports transactions, though), but in PostgreSQL and MS SQL you should be able to sort results by relevance if you build your query the right way. When doing complex queries you'll almost always need to use some database-specific functions unless you really want to add a lot more work and greatly slow down the process.

It looks like we are going to attempt to add MS SQL support (so we can use the Azure SQL cloud). So, we'll see how that goes.

27 May 2010, 10:20

Going through the code, adding MS SQL support doesn't seem to be that much work, but you have to know MS SQL really well to do it.  Most of the queries will work fine as is, but there are places, like where tables are altered, that use database specific code.  Each database type has its own syntax for doing such things that has nothing to do with how Mahara is coded.

I, unfortunately, do not know MS SQL well enough to add the functionality quickly, but hopefully someone on my development team does.

19 May 2010, 17:51

Looks like Stephen already answered with most of what I wanted to say :) Thanks!

As he said, despite SQL being a standard, there are enough incompatibilities between different databases to make this a non-trivial problem. So yes, because we are using adodb, it makes things easier, however, supporting another database would require a lot of testing and tweaking of SQL statements in many places.

Here is someone's attemt at porting Mahara to Firebird:

https://bugs.launchpad.net/mahara/+bug/572189

Ultimately, what we'd like to do is spend some time to switch to the standard PHP database abstraction layer, PDO. This will reduce the amount of database code we have to maintain and probably speed up Mahara given that PDO is implemented within PHP, in C. In the process of doing that, we would clean up all SQL statements and try make them compatible with other databases.

This is unfortunately not going to happen anytime soon though, unless someone is willing to fund it (it's a big job).

Francois