What's the current status of SQLite support?
There seems to have been a fair bit of work done on it in the past and it seems like it might be useful for Desktop Moodle, particularly now that PHP includes SQLite and a server out of the box.
Was there some impassible roadblock reached which caused it to never get out of "experimental status" and led to it being abandoned, or did people just stop keeping it up to date as the code moved on?
Is anyone else interested in getting it working again?
I think the main problem is that Moodle uses complex JOINs which SQLite cannot cope with.
There is no easy solution to that.
Do you mean "RIGHT OUTER JOIN and FULL OUTER JOIN", which they list as stuff they don't support?
http://www.sqlite.org/omitted.html
I thought they could both be emulated with other SQL? (Which isn't ideal, but it seems like Moodle already has the infrastructure to do that kind of DB specific SQL munging).
http://www.sqlite.org/omitted.html
I thought they could both be emulated with other SQL? (Which isn't ideal, but it seems like Moodle already has the infrastructure to do that kind of DB specific SQL munging).
I don't know the specific details.
The fact remain that it would be quite a lot of effort to get this working, and no-one has needed this badly enough to invest the effort.
If it can be done without increasing the complexity of the code too much, then there is no issue with anyone who wants to doing it.
It seems pretty much totally broken at first, but it's actually not that bad.
For example, with a couple of minor tweaks it seems to get all the way through the install fine. It bombs out right at the end, but actually that appears to be when it's trying to display the calendar block on the first page, the actual install has worked fine. If you then go to the admin area of the site then I'd say that about half the links you click on work fine, though obviously that means half are broken with an not very helpful message.
With a bit more fiddling to actually get the error bubbled up to the surface and you get this quite scary looking thing back:
General error: 1 near "(": syntax error
(SELECT ctx.path, rc.roleid, rc.capability, rc.permission FROM role_capabilities rc JOIN context ctx ON (ctx.id = rc.contextid) JOIN context pctx ON (pctx.id = :c1_7 AND (ctx.id = pctx.id OR ctx.path LIKE pctx.path||'/%' OR pctx.path LIKE ctx.path||'/%')) LEFT JOIN block_instances bi ON (ctx.contextlevel = 80 AND bi.id = ctx.instanceid) LEFT JOIN context bpctx ON (bpctx.id = bi.parentcontextid) WHERE rc.roleid = :r1 AND (ctx.contextlevel <= 50 OR bpctx.contextlevel < 50) ) UNION (SELECT ctx.path, rc.roleid, rc.capability, rc.permission FROM role_capabilities rc JOIN context ctx ON (ctx.id = rc.contextid) JOIN context pctx ON (pctx.id = :c2_8 AND (ctx.id = pctx.id OR ctx.path LIKE pctx.path||'/%' OR pctx.path LIKE ctx.path||'/%')) LEFT JOIN block_instances bi ON (ctx.contextlevel = 80 AND bi.id = ctx.instanceid) LEFT JOIN context bpctx ON (bpctx.id = bi.parentcontextid) WHERE rc.roleid = :r2 AND (ctx.contextlevel <= 50 OR bpctx.contextlevel < 50) )ORDER BY capability
So it would appear that while you can do stuff (like install Moodle) as an Admin, it's breaking when anything needs to check your roles, but (crucially) it seems to be the exact same error every time. So although it looks a bit scary it might just need that one fix to have it up and running in general.
I'll try breaking that down and feeding it directly to Sqlite to see what it's not liking. My first guess would be those :r1 bits.
For example, with a couple of minor tweaks it seems to get all the way through the install fine. It bombs out right at the end, but actually that appears to be when it's trying to display the calendar block on the first page, the actual install has worked fine. If you then go to the admin area of the site then I'd say that about half the links you click on work fine, though obviously that means half are broken with an not very helpful message.
With a bit more fiddling to actually get the error bubbled up to the surface and you get this quite scary looking thing back:
General error: 1 near "(": syntax error
(SELECT ctx.path, rc.roleid, rc.capability, rc.permission FROM role_capabilities rc JOIN context ctx ON (ctx.id = rc.contextid) JOIN context pctx ON (pctx.id = :c1_7 AND (ctx.id = pctx.id OR ctx.path LIKE pctx.path||'/%' OR pctx.path LIKE ctx.path||'/%')) LEFT JOIN block_instances bi ON (ctx.contextlevel = 80 AND bi.id = ctx.instanceid) LEFT JOIN context bpctx ON (bpctx.id = bi.parentcontextid) WHERE rc.roleid = :r1 AND (ctx.contextlevel <= 50 OR bpctx.contextlevel < 50) ) UNION (SELECT ctx.path, rc.roleid, rc.capability, rc.permission FROM role_capabilities rc JOIN context ctx ON (ctx.id = rc.contextid) JOIN context pctx ON (pctx.id = :c2_8 AND (ctx.id = pctx.id OR ctx.path LIKE pctx.path||'/%' OR pctx.path LIKE ctx.path||'/%')) LEFT JOIN block_instances bi ON (ctx.contextlevel = 80 AND bi.id = ctx.instanceid) LEFT JOIN context bpctx ON (bpctx.id = bi.parentcontextid) WHERE rc.roleid = :r2 AND (ctx.contextlevel <= 50 OR bpctx.contextlevel < 50) )ORDER BY capability
So it would appear that while you can do stuff (like install Moodle) as an Admin, it's breaking when anything needs to check your roles, but (crucially) it seems to be the exact same error every time. So although it looks a bit scary it might just need that one fix to have it up and running in general.
I'll try breaking that down and feeding it directly to Sqlite to see what it's not liking. My first guess would be those :r1 bits.
That is the query from get_user_access_sitewide, which, as you guess, is about checking permissions. Acutally it is run when you log in, to load the data that will be needed to check permissions.
Therefore, that is a somewhat performance-critical function. Any changes to that SQL should be analysed carefully for performance regressions.
The :r1 bits are placeholders. Moodle uses prepared statements. Those placeholders will get replaced by the actual values before the query is run. For sqlite that is done by get_recordset_sql in lib/dml/pdo_moodle_database.php, the lines
$sth = $this->pdb->prepare($sql);
$sth->execute($params);
My best guess is that the syntax for union with order by is very slightly wrong at the outer brackets for grouping what you want to "order by", but i'm not sure yet where to fix it:
After substituting in the relevant values manually this works with what looks like correct results if I give it to the DB directly:
SELECT * FROM
(
SELECT ctx.path, rc.roleid, rc.capability, rc.permission
FROM role_capabilities rc
JOIN context ctx ON (ctx.id = rc.contextid)
JOIN context pctx ON (pctx.id = 1 AND (ctx.id = pctx.id OR ctx.path LIKE pctx.path||'/%' OR pctx.path LIKE ctx.path||'/%'))
LEFT JOIN block_instances bi ON (ctx.contextlevel = 80 AND bi.id = ctx.instanceid)
LEFT JOIN context bpctx ON (bpctx.id = bi.parentcontextid) WHERE rc.roleid = 7 AND (ctx.contextlevel <= 50 OR bpctx.contextlevel < 50)
UNION
SELECT ctx.path, rc.roleid, rc.capability, rc.permission
FROM role_capabilities rc
JOIN context ctx ON (ctx.id = rc.contextid)
JOIN context pctx ON (pctx.id = 2 AND (ctx.id = pctx.id OR ctx.path LIKE pctx.path||'/%' OR pctx.path LIKE ctx.path||'/%'))
LEFT JOIN block_instances bi ON (ctx.contextlevel = 80 AND bi.id = ctx.instanceid)
LEFT JOIN context bpctx ON (bpctx.id = bi.parentcontextid) WHERE rc.roleid = 8 AND (ctx.contextlevel <= 50 OR bpctx.contextlevel < 50)
)
ORDER BY capability;
but this is what is being produced at the moment:
(
SELECT ctx.path, rc.roleid, rc.capability, rc.permission
FROM role_capabilities rc
JOIN context ctx ON (ctx.id = rc.contextid)
JOIN context pctx ON (pctx.id = 1 AND (ctx.id = pctx.id OR ctx.path LIKE pctx.path||'/%' OR pctx.path LIKE ctx.path||'/%'))
LEFT JOIN block_instances bi ON (ctx.contextlevel = 80 AND bi.id = ctx.instanceid)
LEFT JOIN context bpctx ON (bpctx.id = bi.parentcontextid) WHERE rc.roleid = 7 AND (ctx.contextlevel <= 50 OR bpctx.contextlevel < 50)
) UNION (
SELECT ctx.path, rc.roleid, rc.capability, rc.permission
FROM role_capabilities rc
JOIN context ctx ON (ctx.id = rc.contextid)
JOIN context pctx ON (pctx.id = 2 AND (ctx.id = pctx.id OR ctx.path LIKE pctx.path||'/%' OR pctx.path LIKE ctx.path||'/%'))
LEFT JOIN block_instances bi ON (ctx.contextlevel = 80 AND bi.id = ctx.instanceid)
LEFT JOIN context bpctx ON (bpctx.id = bi.parentcontextid) WHERE rc.roleid = 8 AND (ctx.contextlevel <= 50 OR bpctx.contextlevel < 50)
)
ORDER BY capability;
I changed a couple of lines in accesslib and it seems to have fixed this error. Now pretty much everything seems to work, though I'm still not able to see the site homepage due to an error with the calendar block. But every admin link I tried worked and I was able to create a course and add some activities.
https://github.com/ds125v/gumoodle/commit/d5aacf71656f7edaa0ee4012aff6d2a924e2d1a9
I've not yet tested to see whether the fix is sqlite specific and breaks other databases.
The unit tests don't seem to work at all with an sqlite database but I think that's perhaps an error with how they are being set up. The test database doesn't seem to get created at all during the unit test setup script (which unsurprisingly causes a bunch of failures), yet the same DB creation steps seems to happen with no problems during installation.
Running just the DML unit tests gets errors about "General error: 10 disk I/O error" which I think means it can't find and/or access the database file.
https://github.com/ds125v/gumoodle/commit/d5aacf71656f7edaa0ee4012aff6d2a924e2d1a9
I've not yet tested to see whether the fix is sqlite specific and breaks other databases.
The unit tests don't seem to work at all with an sqlite database but I think that's perhaps an error with how they are being set up. The test database doesn't seem to get created at all during the unit test setup script (which unsurprisingly causes a bunch of failures), yet the same DB creation steps seems to happen with no problems during installation.
Running just the DML unit tests gets errors about "General error: 10 disk I/O error" which I think means it can't find and/or access the database file.
Even with the change in place MySQL seems to be functioning. It might just be my imagination since this is a dev server but it seems slower. Maybe it's just slow compared with sqlite? Could those brackets really have a performance impact, and how would I got about measuring it if they do? I suppose I could just switch the change back and forth with MySQL and Postgres and see if there's any notable change, but that' doesn't seem very scientific.
There appears to be a in the Calendar Block and Upcoming Events block that blows up if it returns nothing from the database, since that get turned into a "false" which is later treated like an array. Yet other calls go through the same code and return nothing and function as expected (maybe they all check for false), and stranger still the same code works for MySQL (actually, that's not true, it's not the same code, it's just equivalent code, so I guess there's a difference somewhere that can be found and fixed). If you hide those two blocks though you appear to have a fully functioning Moodle site on sqlite.
There appears to be a in the Calendar Block and Upcoming Events block that blows up if it returns nothing from the database, since that get turned into a "false" which is later treated like an array. Yet other calls go through the same code and return nothing and function as expected (maybe they all check for false), and stranger still the same code works for MySQL (actually, that's not true, it's not the same code, it's just equivalent code, so I guess there's a difference somewhere that can be found and fixed). If you hide those two blocks though you appear to have a fully functioning Moodle site on sqlite.
A few years ago I played with sqlite support and fixed a few bugs ( MDL-19642).
But I ran into more and more little queries which were incompatible so gave up. BTW we should make sure we have unit tests which demonstrate each of these queries which don't work.
Running just the DML unit tests gets errors about "General error: 10 disk I/O error" which I think means it can't find and/or access the database file.
I ran into this myself playing with it quite recently, thats because phpunit cleans up the data directory on every run. You need to stop the phpunit reset from removing the db too.
I think it's worth having a go to get it working completely again, I'd appreciate any further help (that unit test thing has already save me a bunch of time). At the very least we can document what doesn't work with tests in case future versions of SQLite can fix those issues, as I think your work shows that many of them have fixed themselves just by waiting a while and upgrading PHP and the version of SQLite used.
I also think it might be an idea to convert all the Moodle database connections to use PDO rather than the native interfaces. One of the bugs I've hit is because the PDO driver has different behavior from the MySQLi one (returning an empty array for no results versus considering that an error and returning a boolean false). It's possible there's other such differences/bugs between the various native drivers. It seems sensible to converge on PDO if Moodle plans on supporting the variety of DBs that it does, though I admit I don't know what other considerations might make that a bad idea.
One strategy for getting SQLite working properly might be to write a PDO interface for Postgres or MySQL in parallel to figure out what's a bug in the PDO layer versus what isn't supported by SQLite.
Currently most of the database unit tests seem be passing, though there's one in the DML and one in DDL that seem to bring PHPUnit to a complete halt, some kind of infinite loop or so. Just need to figure out which of the tests that is and see how far we can take it.
I also think it might be an idea to convert all the Moodle database connections to use PDO rather than the native interfaces. One of the bugs I've hit is because the PDO driver has different behavior from the MySQLi one (returning an empty array for no results versus considering that an error and returning a boolean false). It's possible there's other such differences/bugs between the various native drivers. It seems sensible to converge on PDO if Moodle plans on supporting the variety of DBs that it does, though I admit I don't know what other considerations might make that a bad idea.
One strategy for getting SQLite working properly might be to write a PDO interface for Postgres or MySQL in parallel to figure out what's a bug in the PDO layer versus what isn't supported by SQLite.
Currently most of the database unit tests seem be passing, though there's one in the DML and one in DDL that seem to bring PHPUnit to a complete halt, some kind of infinite loop or so. Just need to figure out which of the tests that is and see how far we can take it.
I've created a new tracking bug for anyone that wants to help:
http://tracker.moodle.org/browse/MDL-36920
http://tracker.moodle.org/browse/MDL-36920
I think I've got it all mostly working. It was all just a series of little odd things missing, no fundamental barrier as far as I can tell.
Currently I'm trying to get temptables working. It seems just to be an empty object that stores some links to objects, but I'm not sure when and where it should be created. If anyone understands the moodle_temptables family of classes any hints would be welcome.
And I've got a weird one where the test passes if I run it by itself, but consistently fails when I run it as part of the full test suite. Which shouldn't really happen.
But overall I think I've got one failure and two errors in the Database tests, and about 15 or so errors in the full test suit of 1350 so looking in pretty good shape. (Normally, the non-database tests wouldn't give you much faith that the database bits were working, but Moodle's test suite is more like functional tests than unit tests, so I think the database layer is getting a reasonable workout as a side-effect of these other tests. I'm going to run some code coverage tests to check that assumption once I get the full suit running to completion).
Edit: after telling it to skip the mod_data tests it completed:
Tests: 1361, Assertions: 13139, Failures: 3, Errors: 13, Incomplete: 6, Skipped: 1.
I've also got six tests marked as incomplete, as they seem to be testing stuff that SQLite doesn't do, like throwing errors if you put an string in an int column, but that aren't actually required by Moodle (since any code that did that wouldn't work on the other DBs) and a bunch more like that commented out as they were just one assertion in a longer test.
So I'll be working through the rest of these, but if you've got a specific use for SQLite, I'd say it's worth at least investigating further at this stage.
Currently I'm trying to get temptables working. It seems just to be an empty object that stores some links to objects, but I'm not sure when and where it should be created. If anyone understands the moodle_temptables family of classes any hints would be welcome.
And I've got a weird one where the test passes if I run it by itself, but consistently fails when I run it as part of the full test suite. Which shouldn't really happen.
But overall I think I've got one failure and two errors in the Database tests, and about 15 or so errors in the full test suit of 1350 so looking in pretty good shape. (Normally, the non-database tests wouldn't give you much faith that the database bits were working, but Moodle's test suite is more like functional tests than unit tests, so I think the database layer is getting a reasonable workout as a side-effect of these other tests. I'm going to run some code coverage tests to check that assumption once I get the full suit running to completion).
Edit: after telling it to skip the mod_data tests it completed:
Tests: 1361, Assertions: 13139, Failures: 3, Errors: 13, Incomplete: 6, Skipped: 1.
I've also got six tests marked as incomplete, as they seem to be testing stuff that SQLite doesn't do, like throwing errors if you put an string in an int column, but that aren't actually required by Moodle (since any code that did that wouldn't work on the other DBs) and a bunch more like that commented out as they were just one assertion in a longer test.
So I'll be working through the rest of these, but if you've got a specific use for SQLite, I'd say it's worth at least investigating further at this stage.
I ran the tests and recorded code coverage stats and it's looking pretty good, pretty much complete coverage. The things skipped are the usual exceptions and error states that can be quite tricky to test.
And in the process I noticed one test that doesn't look like it's being run because it doesn't have the right name (tests starting with "test" get picked up automatically, someone has typed sql_sql_etc. rather than test_sql_etc.).
And as another bonus, because of the code coverage report I think I may also have figured out why the moodle_temptables stuff wasn't working. None of the DB setup calls were run during the tests. They get called as part of the CLI init script instead, so no wonder my changes weren't being picked up. I'll try deleting the DB and starting from scratch and see if I can get it working that way.
And in the process I noticed one test that doesn't look like it's being run because it doesn't have the right name (tests starting with "test" get picked up automatically, someone has typed sql_sql_etc. rather than test_sql_etc.).
And as another bonus, because of the code coverage report I think I may also have figured out why the moodle_temptables stuff wasn't working. None of the DB setup calls were run during the tests. They get called as part of the CLI init script instead, so no wonder my changes weren't being picked up. I'll try deleting the DB and starting from scratch and see if I can get it working that way.
Got closer with the temptables stuff, it turns out the create_database function isn't called at all, instead PDO itself handles creating the database based on the DSN. Still not sure where to create the sqlite3_pdo_moodle_temptables object though. Whatever I do it seems to be null by the time it gets used.