@alikon I've made suggestions for the mysql file regarding lft and rgt. The same should also be changed to the postgresql file.

@richard67
as we are adding a child to a node that already have childs

i've followed these steps:

1. identify the rgt value of the parent

SELECT @myRight := rgt FROM `#__assets` where name ='com_modules' 

result @myRight = 128

2. make room for the new child

UPDATE `#__assets` SET rgt = rgt + 2 WHERE rgt > @myRight;
UPDATE `#__assets` SET lft = lft + 2 WHERE lft > @myRight;

3. add the new child to the parent

INSERT INTO `#__assets` (`id`, `parent_id`, `lft`, `rgt`, `level`, `name`, `title`, `rules`)
VALUES(89, 18, @myRight + 1, @myRight + 2, 2, 'com_modules.module.90', 'Login Support', '{}');

@alikon The procedure seems right to me. Maybe I've made a mistake when checking manually. Will check again later.

Hmm, but shouldn't the rgt value of record 18 (com_modules) be changed, too, when adding new items with parent = 18?

I think step 2 should be:

UPDATE `#__assets` SET rgt = rgt + 2 WHERE rgt >= @myRight;
UPDATE `#__assets` SET lft = lft + 2 WHERE lft > @myRight;

I.e. for the rgt value use ">=" and for the lft value ">" in the condition.

Then the parent's rgt value will be updated in the right way, too.

Or you leave these conditions like you wrote but have to update that one record later.

Update: But the ">=" is only true if you insert a child of that record for which you got @myRight. If you insert on the same level 1 and not like here level 2, it would be the ">".

Ok, I think I have it. Will mark my previous suggestions as resolved and make a new one.

ok so let's review it for this pr and for future reference

as we are adding a child to a parent that already have childs

these steps needs to be done:

1. identify the rgt value of the parent and take note of the parent_id as well

SELECT @myRight := rgt FROM `#__assets` where name ='com_modules' 

result @myRight = 128

2. make room for the new child

UPDATE `#__assets` SET rgt = rgt + 2 WHERE rgt >= @myRight;
UPDATE `#__assets` SET lft = lft + 2 WHERE lft > @myRight;

3. add the new child to the parent

INSERT INTO `#__assets` (`id`, `parent_id`, `lft`, `rgt`, `level`, `name`, `title`, `rules`)
VALUES(89, 18, @myRight , @myRight + 1, 2, 'com_modules.module.90', 'Login Support', '{}');

4. check that the new child is a child of the parent

SELECT node.name, node.parent_id
FROM `#__assets` AS node,
        `#__assets` AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND parent.name = 'com_modules'
ORDER BY node.lft

@alikon Yes that looks ok to me. The result should be the same as my latest review suggestion.

@alikon Normally (in a world where the features of relational databases are really used), an insert trigger would do that for us.

Unfortunately as we support 2 kinds of databases, it would mean we either maintain triggers for each database kind in their programming language, or we implement the triggers in external libraries in the programming language of our choice and let the database triggers use these external procedures. Both would be possible.

i would prefer to go with a stored procedure, yeah maybe for J7 or J9 ?

I have tested this item ✅ successfully on d2a7bb1

Code review + real test.

Hint for other testers for real test: Just check the "Log" tab of the debug when having "Debug System" switched on on any 4.0 or 4.1 installation. You will see the 2 logs about the missing asset for module 90. Then make a new installation with the PR applied and check the same. The log is empty now.

Thanks!