Implement adding a first parent to an element using SQL
After #1287 (closed), we can handle the second case, where the element does not already have a parent. This has two sub-cases:
Adding a first parent that only has zero or one grandparent
When the element does not have any existing parent, and the new parent only has zero or one grandparents, then only an UPDATE is required to prepend the new path to the existing paths of the element and its children:
Example: Y.add_parent(Z)
| Element | Path | Ordering |
|---|---|---|
| X | [] | 0 |
| Y |
|
0 |
| Z | [] | 0 |
| A | X | 0 |
| A |
|
0 |
| B | X A | 0 |
| B |
|
0 |
| C | X A | 1 |
| C |
|
1 |
@startmindmap
+ A
++ B
++ C
-- X
-- Y
--- Z
@endmindmapAdding a first parent with multiple grandparents
When the new parent has multiple paths, then we need to both run an UPDATE to update the existing paths, and an INSERT to add all the other paths. It might be easier to run the INSERT before the UPDATE — that decision will be made while implementing and does not make a difference in the result.
Example: B.add_parent(A)
| Element | Path | Ordering |
|---|---|---|
| X | [] | 0 |
| Y | [] | 0 |
| Z | [] | 0 |
| A | X | 0 |
| A | Y | 0 |
| A | Z | 0 |
| B |
|
0 |
| B |
|
0 |
| B |
|
0 |
| C |
|
0 |
| C |
|
0 |
| C |
|
0 |
@startmindmap
++ A
-- X
-- Y
-- Z
++ B
+++ C
@endmindmapThis change should remove the whole Python implementation of add_parent, making it fully done using raw SQL.