artefact licences feature not configurable on mysql

[Melissa Draper]

http://<your url>/admin/site/licenses.php fails on a mysql installation because the EXCEPT clause is not in mysql.

Instead of except, something like "LEFT JOIN, IS NULL" needs to be used.

[WAR] c9 (lib/errors.php:749) Failed to get a recordset: mysql error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXCEPT
    SELECT name FROM artefact_license' at line 2] in EXECUTE("
    SELECT license FROM artefact WHERE license IS NOT NULL and license <> ''
    EXCEPT
    SELECT name FROM artefact_license
")
Command was:
    SELECT license FROM artefact WHERE license IS NOT NULL and license <> ''
    EXCEPT
    SELECT name FROM artefact_license
Call stack (most recent first):

    log_message("Failed to get a recordset: mysql error: [1064: You...", 8, true, true) at /var/www/mahara-mysql/code/htdocs/lib/errors.php:109
    log_warn("Failed to get a recordset: mysql error: [1064: You...") at /var/www/mahara-mysql/code/htdocs/lib/errors.php:749
    SQLException->__construct("Failed to get a recordset: mysql error: [1064: You...") at /var/www/mahara-mysql/code/htdocs/lib/dml.php:846
    get_column_sql(" SELECT license FROM artefact WHERE license IS...") at /var/www/mahara-mysql/code/htdocs/admin/site/licenses.php:57

[WAR] c9 (lib/dml.php:846) Failed to get a recordset: mysql error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXCEPT
    SELECT name FROM artefact_license' at line 2] in EXECUTE("
    SELECT license FROM artefact WHERE license IS NOT NULL and license <> ''
    EXCEPT
    SELECT name FROM artefact_license
")
Command was:
    SELECT license FROM artefact WHERE license IS NOT NULL and license <> ''
    EXCEPT
    SELECT name FROM artefact_license
Call stack (most recent first):

    get_column_sql(" SELECT license FROM artefact WHERE license IS...") at /var/www/mahara-mysql/code/htdocs/admin/site/licenses.php:57

Mahara: Site unavailable
A nonrecoverable error occurred. This probably means you have encountered a bug in the system

[Son Nguyen (ngson2000)]

https://reviews.mahara.org/2049

[Son Nguyen (ngson2000)]

I did also test Mahara upgrade from 1.6 using MySQL. This test did NOT perform when installing the artefact license feature.

Test cases for this bug

1. Login as site admin
2. Click Administration on main menu
3. Click Configure site/Licenses
4. Make sure there is no warning when you create, update, and delete a license

[Son Nguyen (ngson2000)]

A important note for testing is you have to install Mahara database using MySQL.

[Mahara Bot (dev-mahara)]

Reviewed: https://reviews.mahara.org/2049
Committed: http://gitorious.org/mahara/mahara/commit/8941c0e8647c1756c438709616a195c3fbed70aa
Submitter: Aaron Wells (<email address hidden>)
Branch: master

commit 8941c0e8647c1756c438709616a195c3fbed70aa
Author: Son Nguyen <email address hidden>
Date: Fri Mar 15 08:58:01 2013 +1300

    Replace SQL-EXCEPT clause by NOT IN operator (Bug 1131508)

    MySQL does NOT support EXCEPT clause. This should be replaced by NOT IN
    operator.

    Change-Id: I5ffbca8fb2c9aa2608cf426d0afc977cd2df09e0
    Signed-off-by: Son Nguyen <email address hidden>