/ The idea of this code is that an aquery statement comes in and 
/ q code comes out.
/ We will start with single table queries.
/ For join queries, we will first form the join based on an equi-join
/ between the two tables if posible and then select on that using the 
/ single table query setup.

/ Here is the basic setup:
/ select [func] c1, ... cn from tab [assuming c1, c2...] 
/ where where_clause group by group_clause 
/ --> select 
/ --> for each ci if just a column then print the column, but if ci as x
/ then translate to x: ci
/ we are assuming the same functions are used
/ --> if group_clause is not empty, then ("by "), group_clause
/ --> from tab doesn't change but if there is an assuming then xasc or xdesc
/ --> where_clause -- add an extra level of parens around each subclause
/ where a subclause is delimited by an and/or and replace and by comma.
/ I think that having is a separate query.

/ Find lines that begin with a) for aquery.
/ Break down into projections, table formation (select correct columns
/ and sort if there is an assuming)

/ find the clauses of a line that begins with 
/ "a)" from aquery syntax into kdb syntax
/ should be in a single line without subqueries for now
findclauses:{[line]
  if[2 > count line; :line];
  if[not ("a)") ~ line[til 2]; :line];
  myline: 2 _ line;
  iselect: myline ss "select";
  iselect,: myline ss "SELECT";
  ifrom: myline ss "from" ;
  ifrom,: myline ss "FROM" ;
  iwhere: myline ss "where";
  iwhere,: myline ss "WHERE";
  igroup: myline ss "group by";
  igroup,: myline ss "GROUP BY";
  if[0 = count iselect; :("a) No select: "), myline];
  if[0 = count ifrom; :("a) No from: "), myline];
  selectclause: myline[(7 + iselect[0]) + til ((ifrom[0]) - (7 + iselect[0]))];
  if[0 < count iwhere;
   fromclause: myline[(5 + ifrom[0]) + til ((iwhere[0]) - (5 + ifrom[0]))]];
  if[0 = count iwhere;
   fromclause: (5 + ifrom[0]) _ myline];
  whereclause: " ";
  groupclause: " ";
  if[(0 < count iwhere) & (0 < count igroup);
   whereclause: myline[(6 + iwhere[0]) + til ((igroup[0]) - (6 + iwhere[0]))];
   groupclause: (9 + igroup[0]) _ myline;];
  if[(0 < count iwhere) & (0 = count igroup);
   whereclause: (6 + iwhere[0]) _ myline];
  createkdb[selectclause; fromclause; whereclause; groupclause]}

/ generate a kdb phrase
createkdb:{[selectclause; fromclause; whereclause; groupclause]
 outline: buildselect[selectclause];
 if[2 < count groupclause; outline,: buildgroup[groupclause];];
 outline,: buildfrom[fromclause];
 if[2 < count whereclause; outline,: buildwhere[whereclause];];
 outline}


/ everything between select and from
buildselect:{[selectclause]
 out: "select ";
 if[("*") in selectclause; :out];
 myclause: selectclause;
 j: myclause ? ",";
 while[j < count myclause;
	out,: (processcol myclause[til j]),(", ");
	myclause: (j+1) _ myclause;
 	j: myclause ? ",";];
 out,: processcol myclause;
 out}

/ a single column expression e.g. mins a as x
processcol:{[col]
  ias: col ss "as";
  if[0 = count ias; :col]
  afteras: rtrim (3 + ias[0]) _ col;
  out: afteras, (": "), rtrim col[til ias[0]];
  out}

intersect:{[x;y] x[where x in y]}


/ deletes all blanks
delblanks:{[x]
 ii: where not x = " ";
 x[ii] }

/ separate into words
sepwords:{[x]
  ii: where x = " ";
  ii: distinct 0, ii;
  y: delblanks each ii _ x;
  c: count each y;
  ii: where not c = 0;
  y[ii]}



/ everything between from and either the end or the where
buildfrom:{[fromclause]
  out: " from ";
  iassume: fromclause ss "assuming";
  iassume,: fromclause ss "ASSUMING";
  if[0 = count iassume; :fromclause];
  afterassuming: ltrim rtrim (9 + iassume[0]) _ fromclause;
  icomma: where afterassuming = ",";
  if[0 < count icomma; afterassuming[icomma]: "`"];
  afterassuming: delblanks afterassuming;
  out,:("(`"),afterassuming,(" xasc "),(rtrim fromclause[til iassume[0]]),(")");
  out}

/ everything after the group by clause
buildgroup:{[groupclause]
  (" by "), groupclause}

/ just replace and or AND by ,
buildwhere:{[whereclause]
 whereclause: ssr[whereclause;" and";","];
 whereclause: ssr[whereclause;" AND";","];
 (" where "),whereclause}
 

  

	
  
/ EXECUTION


n: 100000;
stocks: `ibm`hp`amaz`goog`aapl;
rantrade:([]stock: n?stocks; price: 20 + n?380.0;amount: 100*(1+n?1000);
  time: 10:00:00.000 + n?06:00:00.000);


line: "a) select price as myprice, time as mytime  from rantrade assuming time where price > 200 and amount < 400 group by stock";
x: findclauses line