JSQL Sketch

gistfile1.js

// Quick sketch / spec for how a JSQL module might work

var people = [{
 'name': 'Mary Jane Watson',
 'job': 'Actress',
 'alignment': 'Good',
 'age': 23
}, {
  'name': 'Peter Parker',
  'job': 'Photographer',
  'alignment': 'Good',
  'age': 25
}, {
  'name': 'Norman Osborn',
  'job': 'Rich guy',
  'alignment': 'Evil'
  'age': 40
}, {
  'name': 'Harry Osborn',
  'job': 'Rich kid',
  'aligment': 'Evil',
  'age': 25
}];

var incomes = [{
  {
    'name': 'Peter Parker',
    'income': 2000
  },
  {
    'name': 'Mary Jane Watson',
    'income': 4000
  },
  {
    'name': 'Norman Osborn',
    'income': 10000
  },
  {
    'name': 'Harry Osborn',
    'income': 6000
  }];

/* 
Support: 
 join (join superpeople.real_name with people?)
 table aliasing in joins (and 'from'?)
 grouping by attributes or functions
 where
 aggregation functions (sum, avg, max, groupby)
 select (via .value() ?)
 arithmetic on columns, i.e. add, sub, div, mul or arbitrary functions (select?)
*/

// TODO: Check our code and see what we need

// TODO: Optional 'select' list in the value() method?

// TODO: Supply multiple values to aggregation functions, e.g. avg('income', 'age')

// What is the average income of bad guys in the Marvel universe?
// TODO: What should the aggregate name be? Same as the field?
new JSQL(people)
  .join(incomes, 'name')
  .avg('income')
  .equals('alignment', 'Evil')
  .value()

// Should return something like: [{'avg(income)': xxx}, {'avg(income)': yyy}]

// Should support chained statements? Eliminates the need for 'having' clauses, for example
new JSQL(people)
  .join(incomes, 'name')
  .avg('income')
  .groupBy('alignment')
  .then()
  .greaterThan('income', 5000)
  .value()

/* It would be cool if we could guarantee groupBy to be stable, i.e. 
to select the latest item for each type of item you could sort by 
date and then just group by item type */