Iterative thinking dulls the mind...

After having some more research online, I have come to the conclusion that developers are not always clever people. Maybe it's the iterative thinking that stands in the way of enlightenment..? Anyway, you can read some really stupid things online on this topic. Like this one:

"The problem with triggers is that they cannot be "seen." It is easy to view table relationships, constraints and indexes in the database. On the client-application side, it is also simple to analyze the code. On the other hand, triggers are automatic programs defined in the database that execute behind the scenes as part of the command that fired them. It is hard to follow their logic and in time, it is easy to forget about the triggers in SQL Server, especially if they are not well-documented.

Here are some trigger design tips to keep in mind:

1. Avoid using nested triggers
   By default, if a trigger is changing other tables, the triggers declared for these tables are not fired. The "allow nested triggers" server option sets databases to have the opposite behavior. Triggers are nested when a trigger performs an action that initiates another trigger, which can initiate another trigger and so on. Triggers can be nested up to 32 levels. It is very difficult to follow the logic of nested triggers and they can affect performance.
2. Avoid using recursive triggers
   There are two types of recursion:
   
   • Direct recursion occurs when a trigger fires and performs an action that causes the same trigger to fire again. You can prevent that from happening by setting the "recursive trigger" database option to OFF.
   
   
   • Indirect recursion occurs when a trigger fires and performs an action that causes a trigger on another table to fire. This second trigger causes an update to occur on the original table, which causes the original trigger to fire again. This can be prevented with the "nested triggers" server option."

I'm sorry, but that is just stupid. Ok, you get some of hte same issues as with threads using triggers, but if you plan it carfully, you should have no problem. Like in my example from my previous post, the creation of a transitive closure of a graph, nothing could logically go wrong. Worst case scenario, the TC will have been built for n-1 cases when the last trigger is executed. This means an entire run is done just because of one missing piece of the TC, but hey! Not a big problem, and really not dangerous. If SQLite featured "FOR EACH STATMENT" triggers, this would not even be a problem at all.

So why state that triggers should be avoided? Pointers are powerful and therefore also dangerous too. So, maybe they should not be used either? :-) Good luck developing software this way....

I think more functional programming thinking is needed, and less interative thinking.

Recursive triggers..

Iterative programming is too dominant. Some how, most programmers have been trained in using for/while loops, iterators, coroutines and similar constructs so sucessfully to see that they are not needed, or indeed not very elegant.

One case in point is computing the transitive closure of a graph in SQL. We can easilly find iterative solutions (like this one or even this one) which could easilly have been written in an interative language. SQL is not an interative language, and I see no point of using control constructs like that. All we really need is triggers.
(I'm using SQLite for this, other RDMS may work too :-) ):

So, this is what I am playing with:

PRAGMA recursive_triggers = 1;
--- SQlite verison > 3.6.18 is needed for this to work.

-- Clears the TC store and insert simple connections
-- This is the start point in the call sequence and is triggered by the user inserting data

create trigger ll_tcll_insert after insert on level_level for each row begin
delete from tc_level_level where depth > 1;
insert into tc_level_level select NEW.parent_id ancestor_id,NEW.child_id descendant_id,1 depth,NEW.parent_id || ',' ||NEW.child_id path from level_level;
end;

-- This trigger does the recursive TC building by merging of subgraphs
create trigger tcll_tcll_insert after insert on tc_level_level for each row begin
insert into tc_level_level select tc1.ancestor_id, tc2.descendant_id, tc1.depth + tc2.depth,tc1.path || substr(tc2.path, length(tc2.ancestor_id)+1) from tc_level_level tc1, tc_level_level tc2 where tc1.descendant_id = tc2.ancestor_id;
end;

-- This trigger clears the TC store and deletes the simple connection when a simple connection is deleted

create trigger ll_tcll_delete after delete on level_level for each row begin
delete from tc_level_level where depth > 1 or (OLD.parent_id = ancestor_id and OLD.child_id = descendant_id) ;
end;

-- This trigger is executed when something in the TC store is deleted. It runs exactly one TC run, so that teh main TC builing trigger is executed.

create trigger tcll_tcll_delete after delete on tc_level_level for each row when (select max(depth) from tc_level_level) = 1 begin
insert into tc_level_level select tc1.ancestor_id, tc2.descendant_id, tc1.depth + tc2.depth,tc1.path || substr(tc2.path, length(tc2.ancestor_id)+1) from tc_level_level tc1, tc_level_level tc2 where tc1.descendant_id = tc2.ancestor_id;
end;

These triggers combined results in the following example run (level_level links adjecent nodes together and the transitice closure (TC) is then stored in tc_level_level):

sqlite> select * from level_level;
sqlite> select * from tc_level_level;
sqlite> INSERT INTO "level_level" VALUES(1,2);
sqlite> INSERT INTO "level_level" VALUES(1,3);
sqlite> INSERT INTO "level_level" VALUES(3,4);
sqlite> INSERT INTO "level_level" VALUES(4,5);
sqlite> INSERT INTO "level_level" VALUES(2,5);
sqlite> select * from level_level;
parent_id child_id
---------- ----------
1 2
1 3
3 4
4 5
2 5
sqlite> select * from tc_level_level;
ancestor_id descendant_id depth path
----------- ------------- ---------- ----------
1 2 1 1,2
1 3 1 1,3
3 4 1 3,4
4 5 1 4,5
1 4 2 1,3,4
3 5 2 3,4,5
1 5 3 1,3,4,5
2 5 1 2,5
1 5 2 1,2,5
sqlite> delete from level_level where parent_id = 1 and child_id = 2;sqlite> select * from tc_level_level;
ancestor_id descendant_id depth path
----------- ------------- ---------- ----------
1 3 1 1,3
3 4 1 3,4
4 5 1 4,5
2 5 1 2,5
1 4 2 1,3,4
3 5 2 3,4,5
1 5 3 1,3,4,5
sqlite> delete from level_level where parent_id = 3 and child_id = 4;
sqlite> select * from tc_level_level;
ancestor_id descendant_id depth path
----------- ------------- ---------- ----------
1 3 1 1,3
4 5 1 4,5
2 5 1 2,5
sqlite> INSERT INTO "level_level" VALUES(1,2);
sqlite> select * from tc_level_level;
ancestor_id descendant_id depth path
----------- ------------- ---------- ----------
1 3 1 1,3
4 5 1 4,5
2 5 1 2,5
1 2 1 1,2
1 5 2 1,2,5

So, by using just a couple of triggers (actually, really just one for the TC computation, the rest of them are just maintenance triggers) we are able to get a TV computed. We even get the full path (which is the main reason for the complicated code sections above).

No for/while loops, and just simple clean code, written in SQLite SQL.

Quite neat really. Yay for recursion.

In the beginning, there was nothing..

The point of this blog is to record thougths that came up while developing. Not much happening in that area right now, hence the empty space.

Stay tuned.