recursion tool - gist example

group: Flights simple
description[[
Example 1 is a simple recursion to calculate all airports that can be reached from Vienna.  
Example 2 is basically the same query that additionally counts the steps and calculates the path taken.
]]
example[[
/* This query calculates all airports that can be reached from Vienna. */
WITH RecRel(departure,destination) AS
(
	SELECT departure, destination FROM flight WHERE departure = "VIE"
	UNION ALL
	SELECT step.departure, step.destination
	FROM RecRel AS rec JOIN flight AS step ON (rec.destination = step.departure) 
)
SELECT * FROM RecRel;
/* Calculates all airports reachable from Vienna, counts the steps and calculates the path taken. */
WITH RecRel(step,path,departure,destination) AS
(
	SELECT 1, concat(departure," - ",destination),departure, destination FROM flight WHERE departure = "VIE"
	UNION ALL
	SELECT rec.step+1, concat(rec.path," - ",step.destination),step.departure, step.destination
	FROM RecRel AS rec JOIN flight AS step ON (rec.destination = step.departure) 
)
SELECT * FROM RecRel
]]
flight = 
{
	departure:string,	destination:string,	id:number
	'VIE',			'FRA',			0
	'FRA',			'LHR',			1
	'VIE',			'BUD',			2
	'VIE',			'VCE',			3
	'VCE',			'FCO',			4
	'VIE',			'ZRH',			5
	'ZRH',			'CDG',			6
	'CDG',			'MAD',			7
	'MAD',			'LIS',			8
}

airport = 
{
	location:string,	country:string,	iata:string,	id:number
	'Vienna',			'Austria',		'VIE',			0
	'Frankfurt',			'Germany',		'FRA',			1
	'London',			'England',		'LHR',			2
	'Budapest',			'Hungary',		'BUD',			3
	'Venice',			'Italy',		'VCE',			4
	'Rome',				'Italy',		'FCO',			5
	'Zurich',			'Switzerland',		'ZRH',			6
	'Paris',			'France',		'CDG',			7
	'Madrid',			'Spain',		'MAD',			8
	'Lisbon',			'Portugal',		'LIS',			9
}