sq.ts

/** expressions */

declare class expr<t> { #witness: t; }

/** scopes */

type fields = {[name: string]: expr<any> | scope<any, any>};
type exprfields = {[name: string]: expr<any>};

type scope<f extends fields, a extends fields=never> = { [k in keyof f]: f[k]; }

type nullable<f extends fields> = { [k in keyof f]: toNullable<f[k]> }
type toNullable<t> =
      t extends scope<infer f, infer fa>
      ? scope<nullable<f>, nullable<fa>>
      : t extends expr<infer x>
      ? expr<x | null>
      : never

/** expression combinators */

declare function lit<t extends number|string|boolean>(n: t): expr<t>;
declare function eq<t>(e1: expr<t>, e2: expr<t>): expr<boolean>;

/** helper to define aggregate functions with a single arg */
type agg1<a, r> =
  <const f extends fields>(s: scope<any, f>, a: (s: scope<f>) => expr<a>)
  => f extends never ? never : expr<r>;

declare let count: agg1<number, number>;
declare let sum: agg1<number, number>;
declare let max: agg1<number, number>;

/** relation (this is FROM/JOIN structure) */
type rel<s1 extends fields> = {
  query(): query<s1>;
  join<const s2 extends fields>(other: rel<s2>, on: (s: s1 & s2) => expr<boolean>): rel<s1 & s2>;
  leftJoin<const s2 extends fields>(other: rel<s2>, on: (s: s1 & s2) => expr<boolean>): rel<s1 & nullable<s2>>;
}

/** query */
type query<f extends fields> = {
  /** SELECT ... */
  select<const s1 extends exprfields>(f: (s: scope<f>) => s1): rel<s1>;
  /** WITH ... */
  with<const s1 extends exprfields>(f: (s: scope<f>) => s1): query<f & s1>;
  /** WHERE ... */
  where(by: (s: scope<f>) => expr<boolean>): query<f>;
  /** GROUP BY ... converts a query to an aggregate query */
  groupBy<const f1 extends exprfields>(by: (s: scope<f>) => f1): aggquery<f1, f>;
  /** Wrap query into relation (...) AS name */
  toRel<const n extends string>(name: n): rel<{[k in n]: scope<f>}>;
};

/** aggregated query */
type aggquery<f extends fields, fa extends fields> = {
  /** SELECT ... */
  select<const s1 extends exprfields>(f: (s: scope<f, fa>) => s1): rel<s1>;
  /** WITH ... */
  with<const s1 extends exprfields>(f: (s: scope<f, fa>) => s1): aggquery<f & s1, fa>;
  having(by: (s: scope<f, fa>) => expr<boolean>): aggquery<f, fa>;
  /** Wrap query into relation (...) AS name */
  toRel<const n extends string>(name: n): rel<{[k in n]: scope<f, fa>}>;
};

/** planet schema inferred from DDL */
type planet = {
  id: expr<number>;
  name: expr<string>;
  alive: expr<boolean>;
  color: expr<string>;
};

/** people schema inferred from DDL */
type people = {
  id: expr<number>;
  name: expr<string>;
  planet_id: expr<number>;
};

/** planet relation */
declare let planet: rel<{planet: scope<planet>}>;

/** people relation */
declare let people: rel<{people: scope<people>}>;

/**
 * SELECT people.name, planet.name
 * FROM people
 * JOIN planet ON people.planet_id = planet.id
 */
let peoplePlanet = people
  .leftJoin(planet, (s) => eq(s.people.planet_id, s.planet.id))
  .query()
  .select(s => ({ name: s.people.name, planet: s.planet.name }))
  ;

/**
 * SELECT
 *   WITH people.count(1) AS numPeople,
 *   planet.color AS color,
 *   count() AS count,
 *   max(s.people.count(1)) AS maxPopulation,
 *   numPeople AS numPeople,
 *   sum(s.people.count(1)) AS numPeople2
 * FROM planet
 * LEFT JOIN (
 *   SELECT planet_id, ...
 *   FROM people
 *   GROUP BY people.planet_id) AS people
 * ON planet.id = people.planet_id
 * WHERE planet.alive
 * GROUP BY planet.color
 */
let planetWithPeopleStats = planet
  .leftJoin(
    people.query().groupBy(s => ({planet_id: s.people.planet_id})).toRel('people'),
    (s) => eq(s.planet.id, s.people.planet_id))
  .query()
  .with(s => ({numPeople: count(s.people, _ => lit(1))}))
  .where(s => s.planet.alive)
  .groupBy(s => ({color: s.planet.color}))
  .select(s => ({
    color: s.color,
    count: count(s, _ => lit(1)),
    maxPopulation: max(s, s => count(s.people, _ => lit(1))),
    numPeople: sum(s, s => s.numPeople),
    numPeople2: sum(s, s => count(s.people, _ => lit(1))),
  }))
  ;