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

SQL problem in "recent" repository using oracle database

    XMLWordPrintable

    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

        Attachments

          Issue Links

            Activity

              People

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

                Dates

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