[Mahara-contributors] [Bug 1427885] Re: Change "artefact.path" column to use the "nested set" technique for managing hierarchical data

** Changed in: mahara Milestone: 19.04.0 => None -- 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

[Mahara-contributors] [Bug 1427885] Re: Change "artefact.path" column to use the "nested set" technique for managing hierarchical data

Note: We'll need to check if MySQL moves nested sets better. Postgres does, but MySQL couldn't so far. ** Changed in: mahara Milestone: 18.10.0 => 19.04.0 -- You received this bug notification because you are a member of Mahara Contributors, which is subscribed to Mahara. Matching

[Mahara-contributors] [Bug 1427885] Re: Change "artefact.path" column to use the "nested set" technique for managing hierarchical data

** Changed in: mahara Milestone: 18.04.0 => 18.10.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

[Mahara-contributors] [Bug 1427885] Re: Change "artefact.path" column to use the "nested set" technique for managing hierarchical data

Regarding nested sets, there is one problem - it's not just slower on write, it gets progressively slower the more nodes in the tree since in the usual implementation you need to update the left/right values for every single set in the tree... which potentially amounts to every row in the table.

[Mahara-contributors] [Bug 1427885] Re: Change "artefact.path" column to use the "nested set" technique for managing hierarchical data

I see that 'WITH RECURSIVE' looks like it's going to be added to mysql 8.0 https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions-recursive But until then a stored procedure would be needed

[Mahara-contributors] [Bug 1427885] Re: Change "artefact.path" column to use the "nested set" technique for managing hierarchical data

** Changed in: mahara Milestone: 17.10.0 => 18.04.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

[Mahara-contributors] [Bug 1427885] Re: Change "artefact.path" column to use the "nested set" technique for managing hierarchical data

** Changed in: mahara Milestone: 17.04.0 => None ** Changed in: mahara Milestone: None => 17.10.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 --

[Mahara-contributors] [Bug 1427885] Re: Change "artefact.path" column to use the "nested set" technique for managing hierarchical data

** Changed in: mahara Milestone: 16.10.1 => 17.04.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

[Mahara-contributors] [Bug 1427885] Re: Change "artefact.path" column to use the "nested set" technique for managing hierarchical data

** Changed in: mahara Milestone: 16.10.0 => 16.10.1 -- 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

[Mahara-contributors] [Bug 1427885] Re: Change "artefact.path" column to use the "nested set" technique for managing hierarchical data

** Changed in: mahara Milestone: 16.04.0 => 16.10.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

[Mahara-contributors] [Bug 1427885] Re: Change "artefact.path" column to use the "nested set" technique for managing hierarchical data

** Changed in: mahara Milestone: 15.10.0 => 16.04.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

[Mahara-contributors] [Bug 1427885] Re: Change artefact.path column to use the nested set technique for managing hierarchical data

There is a emulator procedure that mimics 'WITH RECURSIVE' for mysql I'll attach it ** Attachment added: mysqli_with_recursive_alternative.txt https://bugs.launchpad.net/mahara/+bug/1427885/+attachment/4333821/+files/mysqli_with_recursive_alternative.txt -- You received this bug

[Mahara-contributors] [Bug 1427885] Re: Change artefact.path column to use the nested set technique for managing hierarchical data

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

[Mahara-contributors] [Bug 1427885] Re: Change artefact.path column to use the nested set technique for managing hierarchical data

Another idea would be to sniff the DB, and if they're using Postgres 8.4 or later, we use a recursive query on the artefact.parent column. Which, apparently, will probably be more performant than examining the path column or using the nested set technique.