Moodle
  1. Moodle
  2. MDL-27286

SQL problem in "recent" repository using oracle database

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major 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
    • Rank:
      16957

      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

      1. lib.php
        8 kB
        Luca Mazzola

        Issue Links

          Activity

          Hide
          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 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
          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
          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
          Sam Hemelryk added a comment -

          Looks good to me thanks Eloy - integrated now.

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

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

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

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

          Closing.

          Show
          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: