So here is one that uses a "speed" column so it can optimize "cost" (distance/speed):
```
create table edges
(
fromNode integer not null,
toNode integer not null,
distance real not null default 1,
speed real not null default 1,
isOneWay integer not null check(isOneWay in (True, False)) default True,
primary key (fromNode, toNode)
) without rowid;
create unique index backedges on edges (toNode, fromNode, distance, speed) where not isOneWay;
insert into edges(fromNode, toNode, isOneWay, distance, speed) values
(1, 2, True, 100, 40),
(1, 3, True, 70, 50),
(2, 3, False, 20, 40),
(3, 4, True, 40, 50),
(2, 5, True, 30, 50),
(4, 5, True, 20, 40),
(5, 6, True, 20, 20),
(4, 6, True, 80, 100);
.parameter init
.parameter set :start 1
.parameter set :end 6
.parameter set :maxHops 10
.parameter set :maxCost 10
.parameter set :maxDistance 250
with paths (startAt, endAt, visited, hops, pathDistance, pathCost)
as (
select :start as startAt,
:start as endAt,
'/' || :start || '/' as visited,
0 as hops,
0 as pathDistance,
0 as pathCost
union all
select startAt as startAt,
toNode as endAt,
visited || toNode || '/' as visited,
hops + 1 as hops,
pathDistance + distance as pathDistance,
pathCost + distance / speed as pathCost
from paths, edges
where fromNode == endAt
and instr(visited, '/' || toNode || '/') == 0
and instr(visited, '/' || :end || '/') == 0
and hops < :maxHops
and pathCost < :maxCost
and pathDistance < :maxDistance
union all
select startAt as startAt,
fromNode as endAt,
visited || fromNode || '/' as visited,
hops + 1 as hops,
pathDistance + distance as pathDistance,
pathCost + distance / speed as pathCost
from paths, edges
where toNode == endAt
and not isOneWay
and instr(visited, '/' || fromNode || '/') == 0
and instr(visited, '/' || :end || '/') == 0
and hops < :maxHops
and pathCost < :maxCost
and pathDistance < :maxDistance
order by pathCost
)
select startAt, endAt, visited, hops, pathDistance, pathCost
from paths
where startAt == :start
and endAt == :end
;
```
And here is the same thing with the backedges in the edges table and using only a single recursive part:
```
create table edges
(
fromNode integer not null,
toNode integer not null,
distance real not null default 1,
speed real not null default 1,
primary key (fromNode, toNode)
) without rowid;
insert into edges(fromNode, toNode, distance, speed) values
(1, 2, 100, 40),
(1, 3, 70, 50),
(2, 3, 20, 40),
(3, 2, 20, 40),
(3, 4, 40, 50),
(2, 5, 30, 50),
(4, 5, 20, 40),
(5, 6, 20, 20),
(4, 6, 80, 100);
.parameter init
.parameter set :start 1
.parameter set :end 6
.parameter set :maxHops 10
.parameter set :maxCost 10
.parameter set :maxDistance 250
with paths (startAt, endAt, visited, hops, pathDistance, pathCost)
as (
select :start as startAt,
:start as endAt,
'/' || :start || '/' as visited,
0 as hops,
0 as pathDistance,
0 as pathCost
union all
select startAt as startAt,
toNode as endAt,
visited || toNode || '/' as visited,
hops + 1 as hops,
pathDistance + distance as pathDistance,
pathCost + distance / speed as pathCost
from paths, edges
where fromNode == endAt
and instr(visited, '/' || toNode || '/') == 0
and instr(visited, '/' || :end || '/') == 0
and hops < :maxHops
and pathCost < :maxCost
and pathDistance < :maxDistance
order by pathCost
)
select startAt, endAt, visited, hops, pathDistance, pathCost
from paths
where startAt == :start
and endAt == :end
;
```