XMLWordPrintable

    Details

    • Type: Sub-task
    • Status: Closed
    • Priority: Minor
    • Resolution: Duplicate
    • Affects Version/s: 2.1, 2.2, 2.3
    • Fix Version/s: None
    • Component/s: Enrolments
    • Labels:
    • Testing Instructions:
      Hide

      To test this:

      At present it is not possible to synchronise course categories from an external database in moodle.

      After applying this patch, create an external database. The following assumes that you're using postgres, and have a user 'moodle'.:


      sudo su - postgres
      createdb -O moodle -EUTF8 externaldb

      cat <<EOF | psql externaldb
      CREATE TABLE categories (
      id integer NOT NULL,
      title character varying(255) NOT NULL,
      description text,
      parent integer
      );

      ALTER TABLE public.categories OWNER TO moodle;


      -- Name: courses; Type: TABLE; Schema: public; Owner: moodle; Tablespace:

      CREATE TABLE courses (
      id integer,
      fullname character varying(100),
      shortname character varying(100),
      categoryid integer
      );

      ALTER TABLE public.courses OWNER TO moodle;

      EOF

        1. Create some data in the categories
          cat <<EOF | php5 | psql externaldb
          <?php
          echo "INSERT INTO categories (id, title, description, parent) VALUES (1, 'Top Level', 'Description for Top Level', null);\n";

      \$i = 2;
      while (\$i <= 6) {
      echo "INSERT INTO categories (id, title, description, parent) VALUES (\$i, 'Faculty \$i', 'Description for Faculty \$i', 1);\n";
      echo "INSERT INTO courses (id, fullname, shortname, categoryid) VALUES (\$i, 'Sample Course in Faculty \$i', 'course_faculty_{\$i}', {\$i});\n";
      \$j = 1;
      while (\$j <= 5) {
      echo "INSERT INTO categories (id, title, description, parent) VALUES ({\$i}00{\$j}, 'Department {\$j}', 'Description for Department {\$i}', {\$i});\n";
      echo "INSERT INTO courses (id, fullname, shortname, categoryid) VALUES ({\$i}00{\$j}, 'Sample Course in Faculty \$i Department \$j', 'course_faculty_{\$i}dept{\$j}', {\$i}00{\$j});\n";
      \$j++;
      }
      \$i++;
      }
      EOF


      Now configure the enrolment/database module as follows:
      Site administration
      -> Plugins
      -> Enrolments
      -> Manage enrol plugins

      Enable "External database"
      Navigate to Settings page for External database

      Set database driver, host, user, password, and name as you've set them for your database

      Under "Creation of new Course Categories"
      Set "External course categories table" to:
      categories

      Set "Course Category id field" to:
      id

      Set "Course Category name field" to:
      title

      Set "Course Category parent field" to:
      parent

      Set "Course Category description field" to:
      description

      Now run a cli sync:

        1. Test Initial sync
          Open a shell, and switch to your web server user. Under debian I do:
          sudo su - www-data
          bash

      cd ~/git/moodle
      php enrol/database/cli/sync.php

      In Moodle, you should now have one new top level category ("Top Level"), five subcategories, with five subcategories

      Open one of the categories in the category editor and verify that it has:

      • the correct name;
      • a category id number as appears in the database (e.g. Faculty 2 has idnumber 2; Faculty 2 -> Department 3 has idnumber 2003); and

        1. Test syncing courses
          The database we created earlier has should create one course in each category except the top level category
          Navigate back to the plugin's settings page

      Under "Creation of new courses"

      Set "Remote new courses table" to:
      courses

      Set "New course full name field" to:
      fullname

      Set "New course short name field" to:
      shortname

      Set "New course ID number field" to:
      id

      Set "New course category ID" to:
      categoryid

      Set "Type of course category ID" to:
      external

      Run the cli sync again

      You should now have one course in each category

      1. Testing Deletion

      echo "DELETE FROM categories;" | psql externaldb

      Run the cli sync again

      All categories should be deleted with the exception of the "Top Level" category. It is not possible to delete this using the standard moodle category_delete_move.

      All courses should have been re-parented to the "Top Level" category.

        1. Check that we haven't broken anything with the default action of using the Moodle database's course category id when creating a course

      This assumes that the 'Miscellaneous' course has an id of 1 (default install)
      echo "UPDATE courses SET categoryid = 1;" | psql externaldb

      1. This is a bit dirty but should suffice for our requirements
      2. The courese sync only creates courses – it never updates or deletes them so we must do so manually
        echo "DELETE FROM mdl_course WHERE id != 1;" | psql moodle

      Navigate back to the plugin's settings page

      Under "Creation of new courses"

      Set "Type of course category ID" to:
      moodle

      Run the cli sync again

      You should now have 30 courses in the 'Miscellaneous' category

      Show
      To test this: At present it is not possible to synchronise course categories from an external database in moodle. After applying this patch, create an external database. The following assumes that you're using postgres, and have a user 'moodle'.: – sudo su - postgres createdb -O moodle -EUTF8 externaldb cat <<EOF | psql externaldb CREATE TABLE categories ( id integer NOT NULL, title character varying(255) NOT NULL, description text, parent integer ); ALTER TABLE public.categories OWNER TO moodle; – -- Name: courses; Type: TABLE; Schema: public; Owner: moodle; Tablespace: – CREATE TABLE courses ( id integer, fullname character varying(100), shortname character varying(100), categoryid integer ); ALTER TABLE public.courses OWNER TO moodle; EOF Create some data in the categories cat <<EOF | php5 | psql externaldb <?php echo "INSERT INTO categories (id, title, description, parent) VALUES (1, 'Top Level', 'Description for Top Level', null);\n"; \$i = 2; while (\$i <= 6) { echo "INSERT INTO categories (id, title, description, parent) VALUES (\$i, 'Faculty \$i', 'Description for Faculty \$i', 1);\n"; echo "INSERT INTO courses (id, fullname, shortname, categoryid) VALUES (\$i, 'Sample Course in Faculty \$i', 'course_faculty_{\$i}', {\$i});\n"; \$j = 1; while (\$j <= 5) { echo "INSERT INTO categories (id, title, description, parent) VALUES ({\$i}00{\$j}, 'Department {\$j}', 'Description for Department {\$i}', {\$i});\n"; echo "INSERT INTO courses (id, fullname, shortname, categoryid) VALUES ({\$i}00{\$j}, 'Sample Course in Faculty \$i Department \$j', 'course_faculty_{\$i} dept {\$j}', {\$i}00{\$j});\n"; \$j++; } \$i++; } EOF – Now configure the enrolment/database module as follows: Site administration -> Plugins -> Enrolments -> Manage enrol plugins Enable "External database" Navigate to Settings page for External database Set database driver, host, user, password, and name as you've set them for your database Under "Creation of new Course Categories" Set "External course categories table" to: categories Set "Course Category id field" to: id Set "Course Category name field" to: title Set "Course Category parent field" to: parent Set "Course Category description field" to: description Now run a cli sync: — Test Initial sync Open a shell, and switch to your web server user. Under debian I do: sudo su - www-data bash cd ~/git/moodle php enrol/database/cli/sync.php In Moodle, you should now have one new top level category ("Top Level"), five subcategories, with five subcategories Open one of the categories in the category editor and verify that it has: the correct name; a category id number as appears in the database (e.g. Faculty 2 has idnumber 2; Faculty 2 -> Department 3 has idnumber 2003); and — Test syncing courses The database we created earlier has should create one course in each category except the top level category Navigate back to the plugin's settings page Under "Creation of new courses" Set "Remote new courses table" to: courses Set "New course full name field" to: fullname Set "New course short name field" to: shortname Set "New course ID number field" to: id Set "New course category ID" to: categoryid Set "Type of course category ID" to: external Run the cli sync again You should now have one course in each category — Testing Deletion echo "DELETE FROM categories;" | psql externaldb Run the cli sync again All categories should be deleted with the exception of the "Top Level" category. It is not possible to delete this using the standard moodle category_delete_move. All courses should have been re-parented to the "Top Level" category. — Check that we haven't broken anything with the default action of using the Moodle database's course category id when creating a course This assumes that the 'Miscellaneous' course has an id of 1 (default install) echo "UPDATE courses SET categoryid = 1;" | psql externaldb This is a bit dirty but should suffice for our requirements The courese sync only creates courses – it never updates or deletes them so we must do so manually echo "DELETE FROM mdl_course WHERE id != 1;" | psql moodle Navigate back to the plugin's settings page Under "Creation of new courses" Set "Type of course category ID" to: moodle Run the cli sync again You should now have 30 courses in the 'Miscellaneous' category
    • Affected Branches:
      MOODLE_21_STABLE, MOODLE_22_STABLE, MOODLE_23_STABLE
    • Pull from Repository:
    • Pull Master Branch:

      Description

      The current implementation of the database enrolment method allows for specification of a course category. This course category must exist in Moodle already and be the category id stored in the course_category table.

      With the parent task adding the ability to create course categories from an external database it would make sense to offer the option of specifying the course category id based on the category id in the external database too

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                dobedobedoh Andrew Nicols
                Reporter:
                dobedobedoh Andrew Nicols
                Integrator:
                Petr Skoda
                Participants:
                Component watchers:
                Amaia Anabitarte, Bas Brands, Carlos Escobedo, Sara Arjona (@sarjona), Víctor Déniz Falcón
              • Votes:
                2 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: