from collections import namedtuple
from sql_exec import Select

env = {}

def select(columns, tables, condition=None, order=None):
    for row in Select(columns, tables, condition, order).execute(env):
        print('|'.join(map(str, row)))

########
# Dogs #
########

"""
create table dogs as
  select 'abraham' as name, 'long' as fur, 26 as height union
  select 'barack'         , 'short'      , 52           union
  select 'clinton'        , 'long'       , 47           union
  select 'delano'         , 'long'       , 46           union
  select 'eisenhower'     , 'short'      , 35           union
  select 'fillmore'       , 'curly'      , 32           union
  select 'grover'         , 'short'      , 28           union
  select 'herbert'        , 'curly'      , 31;
"""

Dog = namedtuple("Dog", ["name", "height"])

dogs = [Dog("abraham"   , 26),
        Dog("barack"    , 52),
        Dog("clinton"   , 47),
        Dog("delano"    , 46),
        Dog("eisenhower", 35),
        Dog("fillmore"  , 32),
        Dog("grover"    , 28),
        Dog("herbert"   , 31)]

env['dogs'] = dogs

def dogs_examples():
    select("name", "dogs", None, "height")
    select("a.name, b.name", "dogs as a, dogs as b")

##########
# Stacks #
##########

"""
with recursive
  stacks(names, n, total, tallest) as (
    select name, 1, height, height from dogs union
    select names || ',' || name, n+1, total+height, height
      from stacks, dogs
      where n < 4 and tallest < height
  )
select names, total from stacks where n=4 and total>=170 order by total;
"""

Stack = namedtuple("Stack", ["names", "n", "total", "tallest"])

base_stacks = [Stack(dog.name, 1, dog.height, dog.height) for dog in dogs]

def recursive_stack(stack, dog):
    """Yield a new stack from a joined stack and dog."""
    if stack.n < 4 and stack.tallest < dog.height:
        yield Stack(stack.names + "," + dog.name, stack.n+1,
                    stack.total + dog.height, dog.height)

def stack_select():
    """Create the recursive stacks table."""
    stacks, agenda = [], base_stacks
    while agenda:
        input = agenda.pop()
        for dog in dogs:
            for output in recursive_stack(input, dog):
                agenda.append(output)
        stacks.append(input)
    return stacks

env['stacks'] = stack_select()

def stacks_examples():
    select("names,total", "stacks", "n==4 and total>170", "total")

########
# Sums #
########

"""
Note: This SQL query will not execute because it is tree recursive.

with recursive
  ints(n) as (
    select 1 union select n+1 from ints where n < 5
  ),
  sums(exp, value) as (
    select n, n from ints union
    select '(' || a.exp || '+' || b.exp || ')', a.value + b.value
      from sums as a, sums as b where a.value + b.value <= 5
  )
select exp from sums where value=5;
"""

Sum = namedtuple("Exp", ["exp", "value"])

target = 5

base_sums = [Sum(str(i), i) for i in range(1, target+1)]

def recursive_sum(first, second):
    """Yield a new sum by adding first and second."""
    value = first.value + second.value
    if value <= target:
        yield Sum("(" + first.exp + "+" + second.exp + ")", value)

def sum_select():
    """Create the recursive sums table."""
    sums = []
    new = base_sums
    while new:
        sums, new = sums+new, []
        for first in sums:
            for second in sums:
                for output in recursive_sum(first, second):
                    if output not in sums:
                        new.append(output)
    return sums

env['sums'] = sum_select()

def sums_examples():
    select("value", "sums", "exp=='(1+1)'")
    select("value", "sums", "exp=='((1+1)+2)'")
    select("value", "sums", "exp=='((1+2)+2)'")
    select("exp", "sums", "value==2")
    select("exp", "sums", "value==5")