Allow specification of course's category id from external database

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