Actually, looking at the code I have in the migration script, it looks
like it's meant to sort out artefact hierarchy - so I'll add it here:
// need to do special sortby as child artefacts can have a lower id
number than their parent
// due to one being able to move older files into newer folders
if (is_postgres()) {
$sourcedata = get_recordset_sql("; WITH RECURSIVE cte AS
(
SELECT *, id AS parentid, 1 AS hierarchy FROM artefact
WHERE parent IS NULL
UNION ALL
SELECT t.*, cte.parentid, hierarchy + 1 FROM artefact t INNER
JOIN cte ON t.parent = cte.id
) SELECT * FROM cte ORDER BY hierarchy");
}
else if(is_mysql()) {
// we need to do a trick as mysql doesn't have WITH RECURSIVE option
// 1) we need to add the WITH_EMULATOR proceedure to mysql database
// See the mysqli_with_recursive_alternative.txt in this dir
// 2) we call the prepared staement
$sourcedata = get_recordset_sql('CALL WITH_EMULATOR(\'cte\',
\'SELECT *, id AS parentid, 1 AS hierarchy FROM artefact WHERE parent IS
NULL\',\'SELECT t.*, cte.parentid, hierarchy + 1 FROM artefact t INNER JOIN cte
ON t.parent = cte.id\',\'SELECT * FROM cte ORDER BY hierarchy\', 0, \'\')');
}
--
You received this bug notification because you are a member of Mahara
Contributors, which is subscribed to Mahara.
Matching subscriptions: Subscription for all Mahara Contributors -- please ask
on #mahara-dev or mahara.org forum before editing or unsubscribing it!
https://bugs.launchpad.net/bugs/1427885
Title:
Change "artefact.path" column to use the "nested set" technique for
managing hierarchical data
Status in Mahara ePortfolio:
Triaged
Bug description:
Originally, we just had each artefact store its parent ID. This is
slow because it requires running multiple queries to find all the
descendants of a node.
Then, we added a "path" element to each artefact. This is better, but
you can't get a performance improvement by indexing the column,
because most of the queries rely on the "LIKE" operator. (See
https://bugs.launchpad.net/mahara/+bug/1423700 )
So if we want to squeeze more performance out of this, I think the one
remaining thing to look into is the "nested set" technique. This
technique results in very fast searches for descendants, with the cost
of somewhat slower writes. http://mikehillyer.com/articles/managing-
hierarchical-data-in-mysql/
There are even existing PHP libraries for using the technique, such as
this one: http://www.sideralis.org/baobab/
To manage notifications about this bug go to:
https://bugs.launchpad.net/mahara/+bug/1427885/+subscriptions
_______________________________________________
Mailing list: https://launchpad.net/~mahara-contributors
Post to : mahara-contributors@lists.launchpad.net
Unsubscribe : https://launchpad.net/~mahara-contributors
More help : https://help.launchpad.net/ListHelp
