Details
-
Sub-task
-
Status: Closed
-
Minor
-
Resolution: Duplicate
-
2.1, 2.2, 2.3
-
None
-
MOODLE_21_STABLE, MOODLE_22_STABLE, MOODLE_23_STABLE
-
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 externaldbcat <<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";
- Create some data in the categories
\$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 pluginsEnable "External database"
Navigate to Settings page for External databaseSet 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:
categoriesSet "Course Category id field" to:
idSet "Course Category name field" to:
titleSet "Course Category parent field" to:
parentSet "Course Category description field" to:
descriptionNow 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
- Test Initial sync
cd ~/git/moodle
php enrol/database/cli/sync.phpIn 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
- Test syncing courses
Under "Creation of new courses"
Set "Remote new courses table" to:
coursesSet "New course full name field" to:
fullnameSet "New course short name field" to:
shortnameSet "New course ID number field" to:
idSet "New course category ID" to:
categoryidSet "Type of course category ID" to:
externalRun 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:
moodleRun the cli sync again
You should now have 30 courses in the 'Miscellaneous' category
ShowTo 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 -
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