sqlparser

sql <- set_query / select_query / update_query / insert_query / delete_query / show_query / desc_query / use_query /
    transaction_query / account_management_query ~;

% --------------- USE

transaction_query <- space? cmd:(begin_cmd / commit / rollback) space? `
    proplists:get_value(cmd,Node)
`;

use_query <- space? use space database space? `
    [_,_Use,_,Database,_] = Node,
    {use, Database}
`;


% --------------- DESC

desc_query <- space? describe space table space? `
    [_,_Desc,_,Table,_] = Node,
    #describe{table = Table}
`;

% --------------- SHOW

show_query <- show_status / show_create_table / show_tables_from / show_tables_like / show_tables / show_databases /
    show_collation_where / show_collation /
    show_variables_where / show_variables_like / show_variables / show_fields ~;

show_status <- space? show space table_keyword space status space like space like:string space? `
    #show{type=status, from=proplists:get_value(like, Node)}
`;

show_tables <- space? show (space full)? space (tables_keyword / schemas) space? `
    case Node of
        [_,show,[],_,_Tables,_] ->
            #show{type=tables, full=false, from=undefined};
        [_,show,[_,full],_,_Tables,_] ->
            #show{type=tables, full=true, from=undefined}
    end
`;

show_create_table <- space? show space #(?i)create# space table_keyword space key:key space? `
    #show{type = create_table, from=proplists:get_value(key,Node)}
`;

show_tables_from <- show_tables:show_tables space from space key:key space? `
    ShowTables = proplists:get_value(show_tables, Node),
    ShowTables#show{from=proplists:get_value(key,Node)}
`;

show_tables_like <- show_tables:show_tables space like space pattern:string space? `
    ShowTables = proplists:get_value(show_tables, Node),
    ShowTables#show{from={like,proplists:get_value(pattern,Node)}}
`;

show_databases <- space? show space databases space? `
    #show{type=databases}
`;

show_collation_where <- space? show space collation space where space conditions:conditions space? `
    #show{type=collation, conditions = proplists:get_value(conditions,Node)}
`;

show_collation <- space? show space collation space? `
    #show{type=collation}
`;


show_variables_where <- space? show space variables space where space conditions:conditions space? `
    #show{type=variables, conditions = proplists:get_value(conditions,Node)}
`;

show_variables_like <- space? show space variables space like space pattern:string space? `
    #show{type=variables, conditions = {like, proplists:get_value(pattern,Node)}}
`;

show_variables <- space? show space variables space? `
    #show{type=variables}
`;


show_fields <- space? show full:(space full)? space (fields_keyword) space from space key:key space? `
    Full = lists:member(full,proplists:get_value(full,Node)),
    #show{type=fields, full=Full, from = proplists:get_value(key,Node)}
`;


% --------------- SET

set_query <- set space head:system_set tail:(space? ',' space? s:system_set )* space? `
    Head = proplists:get_value(head,Node),
    Tail = [proplists:get_value(s,N) || N <- proplists:get_value(tail,Node)],
    #system_set{'query' = [Head|Tail]}
`;

system_set <- var:set_var space? '=' space? val:value / names:'NAMES' space val:string / names:'NAMES' space val:charset `
    case proplists:get_value(names,Node) of
        undefined ->
            {proplists:get_value(var,Node), proplists:get_value(val,Node)};
        N ->
            {#variable{name = N, scope = session}, proplists:get_value(val,Node)}
    end
`;

% --------------- SELECT

select_query <- select_limit / select_order / select_group / select_where / select_from / select_simple ~;

select_simple <- space? select space params space? `
    #select{params=lists:nth(4, Node)}
`;

select_from <- select_simple space from space tables space? `
    [#select{params=Query}, _, _From, _, Tables, _] = Node,
    #select{params=Query, tables=Tables}
`;

select_where <- select_from space where space conditions space? `
    [Select, _, _Where, _, Conditions, _] = Node,
    Select#select{conditions=Conditions}
`;
select_group <- ( select_where / select_from ) space group_by space group_datas space? `
    [Select, _, _GroupBy, _, Groups, _] = Node,
    Select#select{group=Groups}
`;
select_order <- ( select_group / select_where / select_from ) space order_by space order_datas space? `
    [Select, _, _OrderBy, _, Orders, _] = Node,
    Select#select{order=Orders}
`;

select_limit <- ( select_order / select_group / select_where / select_from / select_simple ) space limit space integer (space offset space integer)? space? `
    case Node of
        [Select,_,limit,_,Limit,[],_] ->
            Select#select{limit=Limit};
        [Select,_,limit,_,Limit,[_,offset,_,Offset],_] ->
            Select#select{limit=Limit, offset=Offset}
    end
`;

order_datas <- head:order_data tail:( space? ',' space? order_data )* `
    [proplists:get_value(head, Node)|[ lists:nth(4,I) || I <- proplists:get_value(tail, Node) ]]
`;
order_data <- (key '.' key / key / integer ) (space sort)? `
    case Node of
        [[_,<<".">>,Key], [_, Sort]] -> #order{key=Key, sort=Sort};
        [Key, [_, Sort]] -> #order{key=Key, sort=Sort};
        [Key, []] -> #order{key=Key, sort=asc}
    end
`;
sort <- asc / desc ~;

group_datas <- head:group_data tail:( space? ',' space? group_data )* `
    [proplists:get_value(head, Node)|[ lists:nth(4,I) || I <- proplists:get_value(tail, Node) ]]
`;
group_data <- key / integer ~;


% ---------------- UPDATE

update_query <- update_where / update_simple ~;
update_simple <- space? update space table_general space set space sets space? `
    #update{table=lists:nth(4, Node), set=lists:nth(8, Node)}
`;
update_where <- update_simple space where space conditions space? `
    [Update, _, _Where, _, Conditions, _] = Node,
    Update#update{conditions=Conditions}
`;
sets <- head:set_value tail:( space? ',' space? set_value )* `
    [proplists:get_value(head, Node)| [ lists:nth(4, I) || I <- proplists:get_value(tail, Node) ] ]
`;
set_value <- key space? '=' space? param_general `
    #set{key=lists:nth(1, Node), value=lists:nth(5, Node)}
`;

% ---------------- DELETE

delete_query <- delete_where / delete_simple ~;
delete_simple <- space? delete space from space table_general space? `
    #delete{table=lists:nth(6, Node)}
`;
delete_where <- delete_simple space where space conditions space? `
    [Delete, _, _Where, _, Conditions, _] = Node,
    Delete#delete{conditions=Conditions}
`;

% ---------------- INSERT

insert_query <- insert_values_keys / insert_values / insert_set ~;
insert_values_keys <- space? insert space into space table_general space? '(' space? keys space? ')' space values space? '(' space? params space? ')' space? `
    Values = lists:zipwith(fun(X,Y) ->
        #set{key=X, value=Y}
    end, lists:nth(10, Node), lists:nth(18, Node)),
    #insert{table=lists:nth(6, Node), values=Values}
`;
insert_values <- space? insert space into space table_general space values space? '(' space? params space? ')' space? `
    #insert{table=lists:nth(6, Node), values=lists:nth(12, Node)}
`;
insert_set <- space? insert space into space table_general space set space sets space? `
    #insert{table=lists:nth(6, Node), values=lists:nth(10, Node)}
`;

% ---------------- Account Management Statements

account_management_query <- insert_user  / grant_sql / drop_user / rename_sql / revoke_sql / set_password ~;
%
drop_user <- space? drop space user space user_at_host space? `
    #management{action = drop, data = #account{access = lists:nth(6, Node)} }
`;

insert_user <- space? create_user space user_at_host space identified password? space param space? `
    #value{name = undefined,value = Password} = lists:nth(9, Node),
    #management{action = create, data = #account{access = [#value{name = <<"password">>, value = Password}|lists:nth(4, Node)]}}
`;

grant_sql <- space? grant space permission space? on space priv_level ('.' priv_level space / space) to space user_at_host space? (with grant_options*)? space?`
    case lists:nth(14,Node) of
    [_|_] ->
      #management{action = grant, data = #permission{on = lists:nth(8,Node), account = lists:nth(12,Node), conditions = [lists:nth(4,Node)|lists:nth(2,lists:nth(14,Node))]}};
    _ ->
      #management{action = grant, data = #permission{on = lists:nth(8,Node), account = lists:nth(12,Node), conditions = lists:nth(4,Node)}}
    end
`;

rename_sql <- space? rename_user space user_at_host space to space user_at_host space? `
    #management{action = rename, data = [#account{access = lists:nth(4, Node)}|lists:nth(8, Node)]}
`;

revoke_sql <- space? revoke space permission space on space priv_level ('.' priv_level space / space) from space user_at_host space? `
    #management{action = revoke, data = #permission{on = lists:nth(8,Node), account = lists:nth(12,Node), conditions = lists:nth(4,Node)}}
`;

set_password <- space? set space password space for space user_at_host space '=' space param space? `
    case lists:nth(12, Node) of
      #value{name = undefined,value = Password} -> ok;
      #key{name = Password, alias = _} -> ok
    end,
    #management{action = setpasswd, data = #account{access =[#value{name = <<"password">>, value = Password}|lists:nth(8, Node)]}}
`;

% ---------------- COMMON TYPES

tables <- head:table tail:( space? ',' space? table )* `
    [proplists:get_value(head, Node)|[ lists:nth(4,I) || I <- proplists:get_value(tail, Node) ]]
`;

table_general <- table_alias / table_value ~;
table <- table_alias / table_value / param_sql ~;
table_alias <- key space as space key `
    #table{name=lists:nth(1, Node), alias=lists:nth(5, Node)}
`;
table_value <- key `
    #table{name=Node, alias=Node}
`;

comparator <- '<=' / '=<' / '=>' / '>=' / '<>' / '!=' / '<' / '>' / '=' / like `
case Node of
    <<"<=">> -> lte;
    <<"=<">> -> lte;
    <<">=">> -> gte;
    <<"=>">> -> gte;
    <<"!=">> -> neq;
    <<"<>">> -> neq;
    <<"<">> -> lt;
    <<">">> -> gt;
    <<"=">> -> eq;
    like -> like
end
`;

conditions <- conditions_normal_chain / conditions_normal / conditions_parens_chain / conditions_parens ~;
conditions_parens_chain <- space? '(' conditions ')' space? nexo space conditions space? `
    case Node of
        [_,_,Cond,_,_,Nexo,_,Next,_] -> #condition{nexo=Nexo, op1=Cond, op2=Next}
    end
`;
conditions_parens <- space? '(' first:conditions ')' space? `
    proplists:get_value(first, Node)
`;
conditions_normal_chain <- space? condition space nexo space conditions space? `
    case Node of
        [_,Cond,_,Nexo,_,Next,_] -> #condition{nexo=Nexo, op1=Cond, op2=Next}
    end
`;
conditions_normal <- space? condition space? `lists:nth(2, Node)`;
condition <- condition_comp / condition_set ~;
condition_set <- param space? set_comp subquery `
    #condition{nexo=lists:nth(3,Node), op1=lists:nth(1,Node), op2=lists:nth(4,Node)}
`;
condition_comp <- param space? comparator space? param `
    #condition{nexo=lists:nth(3,Node), op1=lists:nth(1,Node), op2=lists:nth(5,Node)}
`;

subquery <- space? '(' space? ( select_query / set_datas ) space? ')' space? `
    #subquery{subquery=lists:nth(4, Node)}
`;
set_datas <- head:set_data tail:( space? ',' space? set_data )* `
    [proplists:get_value(head, Node)|[ lists:nth(4,I) || I <- proplists:get_value(tail, Node) ]]
`;
set_data <- value ~;

nexo <- nexo_and / nexo_or ~;
set_comp <- not_in / in / exist / all / any ~;

params <- head:param tail:( space? ',' space? param )* `
    [proplists:get_value(head, Node)|[ lists:nth(4,I) || I <- proplists:get_value(tail, Node) ]]
`;

param_general <- param_var / param_function / param_value / param_key / param_sql ~;
param <- param_question / param_var / param_arithmetic / param_function / param_value / param_all / param_all_alias / param_key_alias / param_key / param_sql ~;

param_sql <- subquery (space as space key)? `
    case Node of
        [#subquery{subquery=Query}, [_,_As,_,Key]] -> #subquery{name=Key, subquery=Query};
        [#subquery{subquery=Query}, []] -> #subquery{subquery=Query}
    end
`;
param_key_alias <- key '\.' key (space as space key)? `
    case Node of
        [Alias, _, Val, [_, _As, _, Key]] -> #key{alias=Key, name=Val, table=Alias};
        [Alias, _, Val, []] -> #key{alias=Val, name=Val, table=Alias}
    end
`;
param_key <- key (space as space key)? `
    case Node of
        [Val, [_, _As, _, Key]] -> #key{alias=Key, name=Val};
        [Val, []] -> #key{alias=Val, name=Val}
    end
`;
param_value <- value (space as space key)? `
    case Node of
        [Val, [_, _As, _, Key]] -> #value{name=Key, value=Val};
        [Val, []] -> #value{value=Val}
    end
`;
param_var <- var (space as space key)? `
    case Node of
        [Var, [_, _As, _, Key]] -> Var#variable{label=Key};
        [Var, []] -> Var
    end
`;
param_all <- '*' `#all{}`;
param_all_alias <- key '\.' '*' `#all{table=lists:nth(1,Node)}`;
param_function <- key space? '(' space? params? space? ')' (space as space key)? `
    case Node of
        [Name, _, _, _, Params, _, _, [_, _As, _, Key]] ->
            #function{name=Name, params=Params, alias=Key};
        [Name, _, _, _, Params, _, _, []] ->
            #function{name=Name, params=Params}
    end
`;

% --------------- GRANT Syntax priv_type https://dev.mysql.com/doc/refman/5.0/en/grant.html
priv_level <- priv_part / priv_all / all_for_all / db_name_all / db_name_table / table ~;

priv_part <- '.' '*' `
  io:fwrite("Nodeall2 ~p  ~n ",[Node]),
  #all{}
`;

priv_all <- '*' `
  #all{}
`;

all_for_all <- '*' '.' '*' `
  #all{table = #all{}}
`;

db_name_all <- database '.' '*' `
    [DBName,_,_] = Node,
    #value{name = DBName, value = #all{}}
`;

db_name_table <- database '.' table `
    [DBName,_,TableName] = Node,
    %table could also be routine name
    #value{name = DBName, value = #table{name = TableName}}
`;

% --------------- Permissions
permission <- head:perms tail:( space? ',' space? perms )* `
  [proplists:get_value(head, Node)|[ lists:nth(4,I) || I <- proplists:get_value(tail, Node) ]]
`;
perms <- all / all_privileges / alter_routine / alter  / create_routine / create_temp_tables / create_user / create_view /
event / file / grant_option / index / lock_tables / process / references / reload / repl_client / repl_slave /
show_dbs / show_view / shutdown / super / trigger / update / usage / insert / create / delete / drop / execute / select / update ~;

% --------------- GRANT Syntax Account Names http://dev.mysql.com/doc/refman/5.6/en/account-names.html

user_at_host <- param '@' param `
    [{value,undefined,Username},<<"@">>,{value,undefined,Host}] = Node,
    [#value{name = <<"username">>, value = Username},
    #value{name = <<"host">>, value = Host}]
`;

% --------------- GRANT Options syntax

grant_options <- space? (max_queries_per_hour / max_updates_per_hour / max_connections_per_hour / max_user_connections) space? ('=' space? integer / integer) `
  case Node of
  [_,What,_,[_Operator,_,Value]] -> #value{name = What, value = Value};
  [_,What,_,Value] -> #value{name = What, value = Value}
  end
`;

% --------------- From arithmetic (extras in neotoma)

param_wa <- param_function / param_value / param_all / param_all_alias / param_key_alias / param_key / param_sql ~;
param_arithmetic <- additive ~;
additive <- multitive space? ( "+" / "-" ) space? additive / mul:multitive `
    case Node of
        [A, _, Type, _, B] -> #operation{type=Type,op1=A,op2=B};
        {mul,Param} -> Param
    end
`;
multitive <- primary space? ( "*" / "/" ) space? Mul:multitive / pri:primary `
    case Node of
        [A, _, Type, _, {'Mul', B}] -> #operation{type=Type, op1=A, op2=B};
        {pri,Param} -> Param
    end
`;
primary <- par:("(" space? add:additive space? ")") / dec:param_wa `
    case Node of
        {dec,Param} -> Param;
        {par,List} -> proplists:get_value(add,List)
    end
`;

% ------------- reserved words

status <- #(?i)status# `status`;
like <- #(?i)like# `like`;
use <- #(?i)use# `use`;
describe <- #(?i)describe# / #(?i)desc# `describe`;
limit <- #(?i)limit# `limit`;
offset <- #(?i)offset# `offset`;
full <- #(?i)full# `full`;
schemas <- #(?i)schemas# `schemas`;
show <- #(?i)show# `show`;
fields_keyword <- #(?i)fields# `fields`;
tables_keyword <- #(?i)tables# `tables`;
table_keyword <- #(?i)table# `table`;
databases <- #(?i)databases# `databases`;
collation <- #(?i)collation# `collation`;
variables <- #(?i)variables# `variables`;
update <- #(?i)update# `update`;
execute <- #(?i)execute# `execute`;
create <- #(?i)create# `create`;
select <- #(?i)select# `select`;
set <- #(?i)set# `set`;
from <- #(?i)from# `from`;
where <- #(?i)where# `where`;
as <- #(?i)as# `as`;
nexo_or <- #(?i)or# `nexo_or`;
nexo_and <- #(?i)and# `nexo_and`;
not_in <- #(?i)not +in# `not_in`;
in <- #(?i)in# `in`;
any <- #(?i)any# `in`;
exist <- #(?i)exist# `exist`;
all <- #(?i)all# `all`;
all_privileges <- #(?i)all +privileges# `all_privileges`;
group_by <- #(?i)group +by# `group_by`;
asc <- #(?i)asc# `asc`;
desc <- #(?i)desc# `desc`;
order_by <- #(?i)order +by# `order_by`;
delete <- #(?i)delete# `delete`;
insert <- #(?i)insert# `insert`;
into <- #(?i)into# `into`;
values <- #(?i)values# `values`;
begin_cmd <- #(?i)begin# `'begin'`;
commit <- #(?i)commit# `commit`;
rollback <- #(?i)rollback# `rollback`;
create_user <- #(?i)create +user# `create_user`;
create_view <- #(?i)create +view# `create_view`;
rename_user <- #(?i)rename +user# `rename_user`;
identified <- #(?i)identified +by # `identified`;
password <- #(?i)password# `password`;
grant <- #(?i)grant# `grant`;
usage <- #(?i)usage# `usage`;
on <- #(?i)on# `on`;
to <- #(?i)to# `to`;
user <- #(?i)user# `user`;
drop <- #(?i)drop# `drop`;
with <- #(?i)with# `with`;
database <- key ~;
revoke <- #(?i)revoke# `revoke`;
for <- #(?i)for# `for`;
max_queries_per_hour <- #(?i)max_queries_per_hour# `max_queries_per_hour`;
max_updates_per_hour <- #(?i)max_updates_per_hour# `max_updates_per_hour`;
max_connections_per_hour <- #(?i)max_connections_per_hour# `max_connections_per_hour`;
max_user_connections <- #(?i)max_user_connections# `max_user_connections`;
alter <- #(?i)alter# `alter`;
alter_routine <- #(?i)alter +routine# `alter_routine`;
create_routine <- #(?i)create +routine# `create_routine`;
create_temp_tables <- #(?i)create +temporary +tables# `create_temp_tables`;
event <- #(?i)event# `event`;
file <- #(?i)file# `file`;
grant_option <- #(?i)grant +option# `grant_option`;
index <- #(?i)index# `index`;
lock_tables <- #(?i)lock +tables# `lock_tables`;
references <- #(?i)references# `references`;
reload <- #(?i)reload# `reload`;
repl_client <- #(?i)replication +client# `repl_client`;
repl_slave <- #(?i)replication +slave# `repl_slave`;
show_dbs <- #(?i)show +databases# `show_dbs`;
show_view <- #(?i)show +view# `show_view`;
shutdown <- #(?i)shutdown# `shutdown`;
super <- #(?i)super# `super`;
trigger <- #(?i)trigger# `trigger`;
process <- #(?i)process# `process`;
param_question <- '?' `"?"`;

% complex types
keys <- head:key tail:( space? ',' space? key )* `
    [proplists:get_value(head, Node)|[ lists:nth(4,I) || I <- proplists:get_value(tail, Node) ]]
`;
key <- '`' chars:(!'`' ("\\\\" / '\\`' / .))+ '`' / [a-zA-Z] [A-zA-Z0-9_]* `
    case length(Node) of
        3 -> iolist_to_binary(proplists:get_value(chars, Node));
        2 -> iolist_to_binary([lists:nth(1,Node)|lists:nth(2,Node)])
    end
`;
value <- string / null / number ~;

var <- '@' '@'? key ('.' key)? `
    [L,G,Key1,Key2] = Node,
    Key = iolist_to_binary([Key1,Key2]),
    Scope = if
        L == [] andalso G == [] -> session;
        G == [] -> global;
        true -> local
    end,
    #variable{name=Key, scope=Scope}
`;

set_var <- '@'? '@'? key ('.' key)? `
    [L,G,Key1,Key2] = Node,
    Key = iolist_to_binary([Key1,Key2]),
    Scope = if
        L == [] andalso G == [] -> session;
        G == [] -> global;
        true -> local
    end,
    #variable{name=Key, scope=Scope}
`;


% Basic types
charset <- "utf8" / "latin1" ~;
string <- "'" ((!"'" ("\\\\" / "\\'" / .)) ("''")?)* "'" `binary:replace(iolist_to_binary(lists:nth(2, Node)), <<"''">>, <<"'">>)`;
number <- float / integer ~;
integer <- [0-9]+ `
    list_to_integer(lists:flatten([ binary_to_list(I) || I <- Node ]))
`;
float <- [0-9]* '.' [0-9]+ `
    case Node of
        [Int,_,Dec] when Int =/= [] ->
            list_to_float(
                lists:flatten([ binary_to_list(I) || I <- Int ]) ++ "." ++
                lists:flatten([ binary_to_list(D) || D <- Dec ])
            );
        [_,_,[Dec]] ->
            list_to_float("0." ++ lists:flatten([ binary_to_list(D) || D <- Dec ]))
    end
`;
space <- [ \t\n\s\r]* ~;
null <- [nN] [uU] [lL] [lL] `null`;

`
-include("../include/sql.hrl").
`