Next Generation Open Source LMS

The ability to have structured course content stored in the database and maintain efficient lookups of what files are in what directories as well as traversing the path to the tree root is a little complicated.

Each entry in the database has left and right counts as well as a tag indicating whether or not it is a directory (which isn’t entirely necessary). For example, if you have a root node and two children, the database might look like this:

ID / NODE NAME / LEFT / RIGHT / DIR
1 / Root / 1 / 6 / 1
2 / Left Child / 2 / 3 / 0
3 / Right Child / 4 / 5 / 0


This requires modifying existing rows when adding new data, but given that the changing of content is a much less common occurrence than accessing data, it makes sense for the cost to be up front. This is essentially a modified preorder traversal data structure. One side effect is the queries are a bit harder to write than an adjacency list data structure. For instance, returning just the immediate child nodes of any parent node requires the following SQL query:

SELECT node.name, node.id, node.lft, node.file_name, node.file_ext, node.dir, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM section_content AS node,
section_content AS parent,
section_content AS sub_parent,
(
SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM section_content AS node,
section_content AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.lft = ?
AND node.section_id = ?
AND parent.section_id = ?
GROUP BY node.name
ORDER BY node.lft
)AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
AND sub_parent.name = sub_tree.name
AND node.section_id = ?
AND parent.section_id = ?
AND node.lft != '1'
GROUP BY node.name
HAVING depth = 1
ORDER BY node.lft


Issue 15 noted that uploading course content did not work. This has been resolved for the simple case (uploading to a single section). Uploading to all sections in a course will require the addition of a loop.