Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-7312

Kill all the REGEXP SQL sentences under Oracle and MSSQL

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.7
    • Fix Version/s: 1.7, 1.8.4, 1.9, 2.0
    • Component/s: Database SQL/XMLDB
    • Labels:
      None
    • Environment:
      MSSQL and Oracle
    • Database:
      Microsoft SQL, Oracle
    • Affected Branches:
      MOODLE_17_STABLE
    • Fixed Branches:
      MOODLE_17_STABLE, MOODLE_18_STABLE, MOODLE_19_STABLE, MOODLE_20_STABLE

      Description

      Such DBs doen't support REGEXP properly (Oracle only under 10gR2 and MSSQL via external package).

      If possible, convert them to standard LIKE expressions. This will change some day. I hope.

        Gliffy Diagrams

          Attachments

            Activity

            Hide
            stronk7 Eloy Lafuente (stronk7) added a comment -
            • Prevented +/- operators (REGEXP) on course searches.
            Show
            stronk7 Eloy Lafuente (stronk7) added a comment - Prevented +/- operators (REGEXP) on course searches.
            Hide
            stronk7 Eloy Lafuente (stronk7) added a comment -
            • Transformed TOKEN searches into STRING searches in searchlib.
            Show
            stronk7 Eloy Lafuente (stronk7) added a comment - Transformed TOKEN searches into STRING searches in searchlib.
            Hide
            stronk7 Eloy Lafuente (stronk7) added a comment -
            • Prevented +/- operators (REGEXP) on message searches
            Show
            stronk7 Eloy Lafuente (stronk7) added a comment - Prevented +/- operators (REGEXP) on message searches
            Hide
            stronk7 Eloy Lafuente (stronk7) added a comment -
            • Hidden the searchfullwords under mssql and oracle
            Show
            stronk7 Eloy Lafuente (stronk7) added a comment - Hidden the searchfullwords under mssql and oracle
            Hide
            stronk7 Eloy Lafuente (stronk7) added a comment -
            • Prevented +/- operations in glossary too.

            Marking this as resolved now! B-)

            Show
            stronk7 Eloy Lafuente (stronk7) added a comment - Prevented +/- operations in glossary too. Marking this as resolved now! B-)
            Hide
            donal@catalyst.net.nz Donal McMullan added a comment -

            Hi Eloy

            I was looking at the "get_courses_search" function, and it looks like there might be a problem with the fix for this bug.

            If an Oracle or MSSql user tries to exclude a phrase from the search with a minus-prefix like so:
            -badword

            ...Oracle and MSSQL users will find that terms that they try to Exclude are actually Included, so instead of filtering out results that contain 'badword', Moodle actively seeks them out and adds them to the result set.

            I might not be understanding this properly, so I thought I'd bat it back to you for review...

            Cheers Eloy - Donal.

            Show
            donal@catalyst.net.nz Donal McMullan added a comment - Hi Eloy I was looking at the "get_courses_search" function, and it looks like there might be a problem with the fix for this bug. If an Oracle or MSSql user tries to exclude a phrase from the search with a minus-prefix like so: -badword ...Oracle and MSSQL users will find that terms that they try to Exclude are actually Included, so instead of filtering out results that contain 'badword', Moodle actively seeks them out and adds them to the result set. I might not be understanding this properly, so I thought I'd bat it back to you for review... Cheers Eloy - Donal.
            Hide
            stronk7 Eloy Lafuente (stronk7) added a comment -

            Done.

            Now under oracle and mssql the "-" performs a "NOT LIKE" statement. Not perfect, but better than previous approach. Well spotted!

            Ciao

            Show
            stronk7 Eloy Lafuente (stronk7) added a comment - Done. Now under oracle and mssql the "-" performs a "NOT LIKE" statement. Not perfect, but better than previous approach. Well spotted! Ciao

              People

              • Votes:
                0 Vote for this issue
                Watchers:
                0 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Fix Release Date:
                  7/Nov/06