General developer forum

Does this query run on Oracle and MSSQL?

 
Picture of Neill Magill
Does this query run on Oracle and MSSQL?
Core developersParticularly helpful MoodlersPlugin developers

If you have Moodle running on MSSQL or Oracle does the following query run:

SELECT ue.userid, ue.enrolid,
    MIN(xpue.status + xpe.status) AS pstatus,
    MIN(CASE WHEN (xpue.status + xpe.status = 0) THEN xpue.timestart ELSE 9999999999 END) AS ptimestart,
    MAX(CASE WHEN (xpue.status + xpe.status = 0) THEN
        (CASE WHEN xpue.timeend = 0 THEN 9999999999 ELSE xpue.timeend END)
        ELSE 0 END) AS ptimeend
FROM mdl_user_enrolments ue
    JOIN mdl_enrol e ON (e.id = ue.enrolid AND e.enrol = 'meta' )
    JOIN mdl_user_enrolments xpue ON (xpue.userid = ue.userid)
    JOIN mdl_enrol xpe ON (xpe.id = xpue.enrolid AND xpe.enrol <> 'meta'
        AND xpe.enrol IN ('manual', 'cohort', 'meta') AND xpe.courseid = e.customint1)
GROUP BY ue.userid, ue.enrolid
HAVING (MIN(xpue.status + xpe.status) = 0 AND MIN(ue.status) > 0)
    OR (MIN(xpue.status + xpe.status) > 0 AND MIN(ue.status) = 0)
    OR ((CASE WHEN MIN(CASE WHEN (xpue.status + xpe.status = 0)
         THEN xpue.timestart ELSE 9999999999 END) = 9999999999
         THEN 0 ELSE MIN(CASE WHEN (xpue.status + xpe.status = 0)
         THEN xpue.timestart ELSE 9999999999 END) END) <> MIN(ue.timestart))
    OR ((CASE 
         WHEN MAX(CASE WHEN (xpue.status + xpe.status = 0)
             THEN (CASE WHEN xpue.timeend = 0 THEN 9999999999 ELSE xpue.timeend END)
             ELSE 0 END) = 9999999999
         THEN 0 ELSE MAX(CASE WHEN (xpue.status + xpe.status = 0)
         THEN (CASE WHEN xpue.timeend = 0
             THEN 9999999999 ELSE xpue.timeend END)
         ELSE 0 END) END) <> MAX(ue.timeend))
I'm trying to verify it works on those two databases to hopefully improve performance of a meta enrollment query: https://tracker.moodle.org/browse/MDL-43176
 
Average of ratings: -
Picture of Matteo Scaramuccia
Re: Does this query run on Oracle and MSSQL? (MDL-43176 - enrol/meta slow database query)
Core developersParticularly helpful MoodlersPlugin developers

Hi Neill,
thanks for your hard working!

If you are interested to test if it will run and not by the actual results, you could generate some fake courses (https://docs.moodle.org/dev/Test_course_generator) and test it by your own via a docker instance thanks to https://github.com/danpoltawski/moodle-docker#example-usage .

HTH,
Matteo

 
Average of ratings: Useful (1)
Picture of Neill Magill
Re: Does this query run on Oracle and MSSQL? (MDL-43176 - enrol/meta slow database query)
Core developersParticularly helpful MoodlersPlugin developers

I got the MSSQL tests to run to prove it worked.

The Oracle image seems to hate me though :D

 
Average of ratings: -
Picture of Matteo Scaramuccia
Re: Does this query run on Oracle and MSSQL? (MDL-43176 - enrol/meta slow database query)
Core developersParticularly helpful MoodlersPlugin developers

Hi Neill,
some love for you here ;).

HTH,
Matteo

 
Average of ratings: Useful (1)
Picture of Neill Magill
Re: Does this query run on Oracle and MSSQL? (MDL-43176 - enrol/meta slow database query)
Core developersParticularly helpful MoodlersPlugin developers

Thanks for that Matteo.

 
Average of ratings: -