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

SQL problem in "recent" repository using oracle database

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 2.0.2
    • Fix Version/s: 2.0.4
    • Component/s: Repositories
    • Environment:
      CentOS 5.5, Oracle 10g, PHP 5.3.5 with oci8 library
    • Database:
      Oracle
    • Testing Instructions:
      Hide

      This test must be performed against ALL DBs (mysql, postgresql, sqlsrv and oracle):

      1) Edit one course or activity intro
      2) Add 2/3 images by uploading them
      3) TEST: Check that, adding one more image and selecting the "recent files" repository, the files added in 2) are shown. No need to pick them, just they are displayed.
      4) Of course, feel free to pick them, both overwriting or renaming. It should work. But this point is not part of this issue.

      Show
      This test must be performed against ALL DBs (mysql, postgresql, sqlsrv and oracle): 1) Edit one course or activity intro 2) Add 2/3 images by uploading them 3) TEST: Check that, adding one more image and selecting the "recent files" repository, the files added in 2) are shown. No need to pick them, just they are displayed. 4) Of course, feel free to pick them, both overwriting or renaming. It should work. But this point is not part of this issue.
    • Difficulty:
      Easy
    • Affected Branches:
      MOODLE_20_STABLE
    • Fixed Branches:
      MOODLE_20_STABLE
    • Pull from Repository:
    • Pull Master Branch:
      MDL-27286_master

      Description

      The query for selecting the recent files doesn't work with Oracle 10g database. (a duplication of name in the columns returned makes the query fails, with no data returned).

      The file involved in the error is:

      {moodle_root}/repository/recent/lib.php around line 56, function 'get_recent_files'
      changing the select part of the SQL query from
      $sql = 'SELECT * FROM {files} files1
      JOIN (SELECT contenthash, filename, MAX(id) AS id
      FROM {files}
      WHERE userid = ? AND filename != ? AND ((filearea = ? AND itemid = ?) OR filearea != ?)
      GROUP BY contenthash, filename) files2 ON files1.id = files2.id
      ORDER BY files1.timemodified DESC';
      to:
      $sql = 'SELECT * FROM {files} files1
      JOIN (SELECT contenthash, MAX(id) AS id
      FROM {files}
      WHERE userid = ? AND filename != ? AND ((filearea = ? AND itemid = ?) OR filearea != ?)
      GROUP BY contenthash, filename) files2 ON files1.id = files2.id
      ORDER BY files1.timemodified DESC';
      solves the problem.

      Attached the modified version of the file {moodle_root}

      /repository/recent/lib.php

        Gliffy Diagrams

        1. lib.php
          8 kB
          Luca Mazzola

          Issue Links

            Activity

            Hide
            dongsheng Dongsheng Cai added a comment -

            Hi, Luca

            Thanks for the fix, I included your fix in https://github.com/dongsheng/moodle/compare/master...s10_MDL-27261_recentfiles_plugin_sql_fix_master.

            Mark made the same change for SQL serve.

            Regards,
            Dongsheng

            Show
            dongsheng Dongsheng Cai added a comment - Hi, Luca Thanks for the fix, I included your fix in https://github.com/dongsheng/moodle/compare/master...s10_MDL-27261_recentfiles_plugin_sql_fix_master . Mark made the same change for SQL serve. Regards, Dongsheng
            Hide
            stronk7 Eloy Lafuente (stronk7) added a comment -

            This patch does:

            1) fixing the query to work under Oracle
            2) format the SQL
            3) use named params

            I've tested it under mysql, postgresql, mssql and oracle and seems to be working ok, both under 20_STABLE and master, in case nobody can perform the tests.

            For sure, any feedback form Luca if he can test will be welcome.

            Ciao

            Show
            stronk7 Eloy Lafuente (stronk7) added a comment - This patch does: 1) fixing the query to work under Oracle 2) format the SQL 3) use named params I've tested it under mysql, postgresql, mssql and oracle and seems to be working ok, both under 20_STABLE and master, in case nobody can perform the tests. For sure, any feedback form Luca if he can test will be welcome. Ciao
            Hide
            samhemelryk Sam Hemelryk added a comment -

            Looks good to me thanks Eloy - integrated now.

            Show
            samhemelryk Sam Hemelryk added a comment - Looks good to me thanks Eloy - integrated now.
            Hide
            stronk7 Eloy Lafuente (stronk7) added a comment -

            I'm passing this as really it was tested against all DBs on development stage.

            Show
            stronk7 Eloy Lafuente (stronk7) added a comment - I'm passing this as really it was tested against all DBs on development stage.
            Hide
            stronk7 Eloy Lafuente (stronk7) added a comment -

            And now this is part of the best Moodle weeklies ever, thanks!

            Closing.

            Show
            stronk7 Eloy Lafuente (stronk7) added a comment - And now this is part of the best Moodle weeklies ever, thanks! Closing.

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Fix Release Date:
                  1/Aug/11