SQL-Recursion-Tool

Help

Tutorial - user

Introduction
Recursion in SQL and in the tool

Tutorial - user

Introduction

The goal of this tool is to provide a platform to help practicing the recursion in SQL. All calculations are done directly in the browser, therefore the tool does not require a connection to a database. It supports most of the commonly used functions and commands of SQL, including the recursion. For a list of all supported functions and an explanation on how to use them, please refer to the SQL reference below.

Recursion in SQL and in the tool

SQL:1999 introduced Common Table Expressions (CTE). A CTE is a temporary set which contains the result of a query. You can refer to this set with a query or it can refer to itself respectively. By referring to itself it is possible to make recursive queries. For a more detailed look at recursive queries in SQL, please refer to the official documentation by Microsoft.

In this tool, a recursive query can be executed step by step. While doing so, the current step, the corresponding code as well as the involved tables will be highlighted. This should help provide the user with a better understanding on how such a query works. It should also highlight the fact, that a recursion should not be considered as an incomprehensible construct but rather as a combination of its separate components, which are, usually, simple queries.

A query needs to meet a certain criteria to be recognized as a recursive query by the tool. If any of the following points is not fulfilled, the tool will not recognize the query as recursive and thus the step-by-step solution will not be available.

With-Clause
A CTE is necessary because it is the only way to make recursive queries in SQL.
Union All
In SQL, a recursion will only return the desired result, if initialization and step case are combined by using the Union All operator.
Recursive Reference and Join in step case
The seconds Select-Statement in the CTE has to refer to itself in some way (the actual recursion). Additionally, this statement also has to contain a Join to guarantee that the result can actually grow with every step
Selecting With-Clause
The outer query has to select at least one column of the just calculated recursion. In reality, this point would not be necessary - it is rather to make the user aware, that the final result of the recursion can only be displayed with this query.

Tutorial - maintainer

Anybody can create and share data sets that can be used within the tool. This can be done via GitHub Gists, a free platform to share code snippets. Once created, such a data set can be imported into the tool by using its unique ID. It can be found at the end of the URL or at the top of the page as gist:xxxxxxxxxxxxxx.

Creating a data set

A data set is specified as a simple text file that is created as a Github Gist (the name of the gist does not matter). The RelaX - relational algebra calculator uses the same syntax for its data, therefore gists can be used in both tools.

The already included flight-database will be used as an example on how to create a compatible gist file for this tool. The full file can also be found here.

First, a group attribute has to be defined. It serves as the title of the database and is displayed in the 'Select Database' menu.

This attribute can consist of any character and is read until the first line break.

group: Flights simple

The next attribute is the description. It is optional and does not have to be included.

It is displayed below the editor and should be used to further describe your data set and to provide additional links to sources.

It can either be a single-line description, with syntax just like the in group attribute, or a multi-line description where brackets have to be used to indicate the start and end of the description.

Furthermore, the Markdown-Syntax is supported to easily convert the description to HTML code

			
			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.

			]]

Next, one or more examples can be defined. This attribute is optional as well and can be omitted

If a data set includes examples, they can be inserted into the editor by using the respective button on the bottom right of the editor

The syntax with the brackets for multi-line descriptions is used again. To separate multiple examples, a semi-colon is used

The examples are inserted into the editor with the same format as used in the gist-file, including line breaks and tabulators

				
			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

			]]

Finally, the actual data has to be defined. This is similar to defining variables in several programming languages.

The name of the variable is in this case the name of the table and on the inside, the columns are defined.

The first line defines the name and the data type of the column. The data type is optional, so each column is either defined as name or name:data type.

Note that only 'string' and 'number' are supported data types, everything else is treated as a string.

In the following rows the entries of the table can be defined, where single quotes should be used for strings

			
			flight =
 
			{

				departure:string,	destination:string,	id:number

				'VIE',			'FRA',			0

				'FRA',			'LHR',			1

				...
	
			}

			airport = 

			{

				location:string,	country:string,	iata:string,	id:number

				'Vienna',			'Austria',		'VIE',			0

				'Frankfurt',		'Germany',		'FRA',			1

				...

			}

SQL reference

This tool calculates queries by itself, therefore not all functions from SQL can be used within the tool. In the following, every possible function and command will be listed and explained further, if necessary.

Select (Distinct) - From - Where
And and Or
Order By
Like
Wildcards
Between
Not
Aliases with As
Joins with Using/On
Left/Right/Full/Cross/Natural Join
Group By and Having
Limit
Union/Except/Intersect (All)
Aggregate functions (AVG, COUNT, MIN, MAX, SUM)
String concatenation with concat() in Select
Basic calculations and modulo in Select
Recursive Queries
Comments

Further Explanation

In order to avoid any lack of clarity, some functions and characteristics of the tool will be further explained:

Referencing
It is not necessary to always specify the table in queries. If the table can clearly be determined, the name of the column is enough. However, if a column does not have a unique name, it can't be assign definitely to a table automatically and it has to be specified as table.column.
Joins
Joins of any form will be executed from left to right. This is important when making queries with multiple joins because a different type of join might return different column names and furthermore lead to a wrong result. For example, a Join with Using only returns one column while a Join with On returns two columns with the respective prefix of its table. It is advised to mix different types of joins when making queries, if possible.
Group By and Having
Having can only be used in combination with Group By and only if it follows directly after the Group By statement.
String concatenation with concat()
Any combination of columns and strings can be concatenated in the Select part of the query. This is done by using the concat() function.
SELECT concat("This is flight ",id," to ",destination) FROM flight
Basic calculations and modulo
Calculations with decimal numbers can directly be declared as a column. Alternatively, a calc() function, similar to concat(), can be used.
SELECT 2*id FROM flight SELECT calc(2+id%2) FROM flight
Comments
Comments can be used just like in SQL.
-- single-line comment /* multi-line comment */

Unsupported SQL

For completeness, here is a list of functions that are not supported by the tool. Mostly because they are not needed or due to technical reasons

Subqueries
In
Is (Not) Null
Complex data types (e.g. DATE)
Data Definition Language (e.g. CREATE TABLE)
Data Manipulation Language (e.g. INSERT INTO or UPDATE)

External resources

Changelog

10-06-16

Added limit for recursive steps (100) and a message if limit is reached

07-06-16

Calculations for decimal numbers now also work without using calc() function

24-05-16

Added navigation bar for feedback and help
Updated step-by-step-solution
Amount of rows are now displayed for every table (and increases in step-by-step solution automatically)
Current state of recursion now displayed below buttons
Slight changes to the buttons, eg 'initialization'-button at the beginning

21-05-16

Updated 'show full tables': smaller button, max. 10 rows per table, amounts of rows displayed
Example-buttons are now displayed for each dataset separately
Updated gist-parser to also include example-queries
Gist-parser: group, description (optional), example (optional), tables
Same scheme as multi-line description with double-brackets to open and close
Multiple examples are separated by a semicolon
Example of examples:
example[[select * from flight; select * from flight where id > 2]]
Step-by-step solution no longer disappears when changing query (before clicking submit)

19-05-16

Multiple joins should now work as intended
Translated example datasets and example queries from German to English
Queries with limit >= 10 now work correctly
Multi-line description for gist-files now finally working (also with markdown-syntax)

28-04-16

Tables don't have to be specified in queries anymore
Tool now looks at corresponding 'from'-part of query and tries to add missing tables, if possible
Works for any query size -> single-select, union (2 selects), recursion (3 selects), etc...
Added error messages for non-existing columns in selected tables, and for non-unique columns in selected tables

20-04-16

Alerts for valid non-recursive input are now yellow to better distinguish between recursive/non-recursive input

15-04-16

Added another step before step-by-step solution shows the final result
Step-by-step solution now keeps linebreaks of query

13-04-16

Like and Limit now work correctly again
Fixed error which made certain Where-conditions not work
Added success/error alerts when importing gist-files
Updated gist-parser to parse every datatype (if unknown, its treated as string)
Added warning when importing dataset with unknown type (example: 2923a30a474fdcb46bee)

12-04-16

Updated gist-parser to use description with markdown
Updated db-description below editor to correctly work again

07-04-16

Extended step-by-step solution by highlighting corresponding part of the code
Calculations in select not limited in order anymore, ie 1+id and id+1 should work the same

06-04-16

Aggregate functions are no longer case-sensitive
Step-by-step solution now ends correctly
Added SQL-part to the result

05-04-16

Selecting number now works correctly with 'as'
Added possibility to select signed numbers (+/-)
SQL comments can now be used in the editor
Single-line: -- my comment
Note: the editor only highlights single-line comments if there is a space after --, but it also works without
/* my comment */ for multi-line

04-04-16

Added datatype (if available) to columns
Minor fixes in SQL- and gist-parser

29-03-16

Multiple Where-conditions now work again as intended

28-03-16

Loading gists (same/similar pattern as gists for relax-tool) now possible
Gists also stored temporarily once loaded
Tested with gist id 7d1871f79a8bcb4788de
Displays db-information (if available) below editor

23-03-16

Changing datasets should no longer cause problems with results

22-03-16

Added parser for gist/local datasets
Possible to choose from different datasets

21-03-16

Improved semantic-check of query (covers alias and with-clauses correctly now)
Added information on why step-by-step solution is not available

20-03-16

Implemented more complex step-by-step functionality

18-03-16

Added possibility to make calculations with +-*/% in select
Calculate by using numbers or columns
Start calculation with a number or use calc() (calc() should always work)
example: calc(1+flug.id) or calc(flug.id+2*flug.id)
Error messages now with error position
Buttons for simple step-by-step solution (only for recursion)

17-03-16

Added concat() function for select, combination of string (double quotes) and table.column as possible parameters
Added another example to show how concat() works

16-03-16

Parser update:
- Valid query no longer requires semicolon at the end
- Selecting a string-literal is now valid - double quotes necessary
- selecting a numeric-literal is now valid - decimal numbers combined with +,-,*,/

10-03-16

Recursive queries should now work

09-03-16

Fixed error where join-operators were still case-sensitive
Query result now gets display directly on the page

08-03-16

Fixed error that caused 'as' not to properly work when used in 'from'
Non-recursive with-clauses should now work
Parser now distinguishes between recursive and non-recursive queries

22-02-16

Extended syntax- and semantic-checks to also include specific cases for with-clauses

20-02-16

Added basic with-syntax to parser

18-02-16

Parser: aggregate-functions now also for groupby/having
Fixed error in SQLike that caused group by not to work on table.column
groupby/having now usable in queries (having only works if followed directly after group by)

16-02-16

Parser fixes for groupby/having
Added aggregate-functions (avg,sum,count,min,max) to parser
Aggregate function now usable in select

04-02-16

Parser fixes for like/between
'(not) between/like' can now be used in queries

Help

Tutorial - user

Tutorial - maintainer

SQL reference

Unsupported SQL

External Resources

Changelog

Tutorial - user

Introduction

Recursion in SQL and in the tool

Tutorial - maintainer

Creating a data set

SQL reference

Further Explanation

Unsupported SQL

External resources

Changelog

10-06-16

07-06-16

24-05-16

21-05-16

19-05-16

28-04-16

20-04-16

15-04-16

13-04-16

12-04-16

07-04-16

06-04-16

05-04-16

04-04-16

29-03-16

28-03-16

23-03-16

22-03-16

21-03-16

20-03-16

18-03-16

17-03-16

16-03-16

10-03-16

09-03-16

08-03-16

22-02-16

20-02-16

18-02-16

16-02-16

04-02-16