Marco Molteni marco-m

Recursive Queries Using Common Table Expressions

Common Table Expressions (CTEs) are a bit complex and difficult to understand at first blush. Many of the tutorials and examples on the net don't make it any easier for those just starting out. I thought I'd put together a quick gist that tries to simplify the concept and demonstrate how to do recursive queries using CTEs.

Keep in mind that CTEs have other uses besides just recursive queries but this gist is just about how they can be used to create recursive searches.

I'm using SQLite in this example but any SQL language that implements the WITH keyword should be able to do the same thing. If you've never used SQLite before, you are missing out on an amazing, open source, stand alone, SQL engine. I encourage you to check it out.

A practical example

Erlang QuickCheck

What is QuickCheck?

QuickCheck is a language for stating properties of programs.

?FORALL(X, nat(), X*X >= 0)

	@echo off

	nuget restore -Verbosity normal
	if %errorlevel% neq 0 exit /b %errorlevel%

	msbuild -p:Configuration=Release ^
	-p:WarningLevel=0
	if %errorlevel% neq 0 exit /b %errorlevel%

	dotnet.exe test TestProject.csproj --configuration Release --no-build -v n

	package main

	import (
	"context"
	"log"
	"net/http"
	"os"
	"os/signal"
	"time"
	)

	odessa(~/src/github.com/pkg/mach) % cat sys_darwin_amd64.s
	// Copyright 2009 The Go Authors. All rights reserved.
	// Use of this source code is governed by a BSD-style
	// license that can be found in the LICENSE file.

	//
	// System calls and other sys.stuff for AMD64, Darwin
	// See http://fxr.watson.org/fxr/source/bsd/kern/syscalls.c?v=xnu-1228
	// or /usr/include/sys/syscall.h (on a Mac) for system call numbers.
	//


	# Join multiple lines without new line
	value: >
	part 1
	part 2

	# Join with newline
	value2: \|
	line 1
	line 2

	-module(bench).
	-compile(export_all).

	-define(COUNT, 1000).
	-define(SIZE, 10000).
	-define(PORT, 9001).

	read_all(Socket, Limit) when Limit > 0 ->
	case gen_tcp:recv(Socket, 100*?SIZE) of
	{ok, Bin} ->

	; Ruby has an awesome feature -- string interpolation. Read about it on the internet.
	; On the other hand, Clojure only has cumbersome Java string formatting, which can not be
	; used without pain after you've tried Ruby.

	; So here's this simple macro that basically allows you to do most things you could do
	; with Ruby string interpolation in Clojure.

	(ns eis.stuff
	(:require [clojure.string]))

	#!/bin/bash

	# A simple test script to demonstrate how to find the
	# "absolute path" at which a script is running. Used
	# to avoid some of the pitfals of using 'pwd' or hard-
	# coded paths when running scripts from cron or another
	# directory.
	#
	# Try it out:
	# run the script from the current directory, then