query_opt.knot

-- Query optimization examples
with {
type Employee = {name: Text, dept: Text, salary: Int 1}
type Department = {name: Text, budget: Int 1}

*employees : [Employee]
*departments : [Department]

-- 1. Count pushdown: count *rel → SQL COUNT(*)
countDemo (do
  employees <- *employees
  base.println ("Employee count: " ++ base.show (base.count employees)))

-- 2. Filter pushdown: where clause → SQL WHERE
filterDemo (do
  employees <- *employees
  base.println "High earners (salary > 75):"
  with {filtered (do e <- employees; where e.salary > 75; yield ("  " ++ e.name ++ " ($" ++ base.show e.salary ++ ")"))} (do r <- filtered; base.println r; yield {}))

-- 3. Hash join: equi-join via hash index
joinDemo (do
  employees <- *employees
  departments <- *departments
  base.println "Employee departments:"
  with {joined (do e <- employees; d <- departments; where e.dept == d.name; yield ("  " ++ e.name ++ " works in " ++ d.name ++ " (budget: $" ++ base.show d.budget ++ ")"))} (do r <- joined; base.println r; yield {}))

-- 4. Full SQL: single-table filter → one SQL SELECT
filterQuery (do
  employees <- *employees
  with {result (do e <- employees; where e.salary > 75; yield {name e.name salary e.salary})} yield result)

-- 5. Full SQL: multi-table join + filter → one SQL SELECT
joinQuery (do
  employees <- *employees
  departments <- *departments
  with {result (do e <- employees; d <- departments; where e.dept == d.name; where e.salary > 75; yield {name e.name dept d.name budget d.budget})} yield result)

-- 6. Full SQL: yield entire row (single-table)
allEmployees (do
  employees <- *employees
  with {result (do e <- employees; yield e)} yield result)

printQuery (\label results -> do
  base.println (label ++ ":")
  r <- results
  base.println ("  " ++ base.show r)
  yield {})

-- 7. Pipe: filter → SQL WHERE
pipeFilter (do
  employees <- *employees
  yield (employees |> base.filter (\e -> e.salary > 75)))

-- 8. Pipe: filter + map → SQL WHERE + SELECT
pipeFilterMap (do
  employees <- *employees
  yield (employees |> base.filter (\e -> e.salary > 75) |> base.map (\e -> {name e.name salary e.salary})))

-- 9. Pipe: filter + count
pipeFilterCount (do
  employees <- *employees
  with {filtered (employees |> base.filter (\e -> e.salary > 75))} yield (base.count filtered))

-- 10. Pipe: map only → SQL SELECT
pipeMap (do
  employees <- *employees
  yield (employees |> base.map (\e -> {name e.name dept e.dept})))

-- 11. minOn/maxOn projection → SQL MIN/MAX
minMaxDemo (do
  employees <- *employees
  with {lo (base.minOn (\e -> e.salary) employees)} with {hi (base.maxOn (\e -> e.salary) employees)} base.println ("min salary: " ++ base.show lo ++ ", max salary: " ++ base.show hi))

-- 12. countWhere predicate → SQL COUNT(*) WHERE
countWhereDemo (do
  employees <- *employees
  with {n (base.countWhere (\e -> e.dept == "Eng") employees)} base.println ("eng headcount: " ++ base.show n))

-- 13. Pipe: filter + minOn/maxOn → SQL aggregate with WHERE
pipeFilterMin (do
  employees <- *employees
  yield (employees |> base.filter (\e -> e.dept == "Eng") |> base.minOn (\e -> e.salary)))

pipeFilterMax (do
  employees <- *employees
  yield (employees |> base.filter (\e -> e.dept == "Eng") |> base.maxOn (\e -> e.salary)))

pipeCountWhere (do
  employees <- *employees
  yield (employees |> base.countWhere (\e -> e.dept == "Sales")))
}
(do
  replace *employees = [
    {name "Alice" dept "Eng" salary 90} 
    {name "Bob" dept "Eng" salary 80} 
    {name "Carol" dept "Sales" salary 70} 
    {name "Dave" dept "Sales" salary 60} 
    {name "Eve" dept "Eng" salary 100}
  ]
  replace *departments = [{name "Eng" budget 500} {name "Sales" budget 200}]
  countDemo
  filterDemo
  joinDemo
  fq <- filterQuery
  printQuery "Filter query (salary > 75)" fq
  jq <- joinQuery
  printQuery "Join query (salary > 75, with dept)" jq
  ae <- allEmployees
  printQuery "All employees" ae
  pf <- pipeFilter
  printQuery "Pipe filter (salary > 75)" pf
  pfm <- pipeFilterMap
  printQuery "Pipe filter+map (salary > 75)" pfm
  pfc <- pipeFilterCount
  base.println ("Pipe filter+count (salary > 75): " ++ base.show pfc)
  pm <- pipeMap
  printQuery "Pipe map (name, dept)" pm
  minMaxDemo
  countWhereDemo
  pmin <- pipeFilterMin
  base.println ("Pipe Eng min salary: " ++ base.show pmin)
  pmax <- pipeFilterMax
  base.println ("Pipe Eng max salary: " ++ base.show pmax)
  pcw <- pipeCountWhere
  base.println ("Pipe count dept=Sales: " ++ base.show pcw)
  yield {})