Commits

cacol89 committed 5d0d66a

allowed group by clauses. Added tests.

Comments (0)

Files changed (4)

         | [] -> ""
         | _ -> "WHERE "^(String.concat " AND " constraints)
 
+(** Generates the SQL that correspond to aggregation in a rule,
+ * this corresponds to GROUP BY and HAVING clauses.
+ * The GROUP BY clause will be comprised of the columns in the
+ * resulting table that are not aggregates (obviously), nor constants.
+ * 
+ * If the predicate's head do not contain aggregation functions, nothing is
+ * returned. If this condition is met but aggregate functions appear on the
+ * rule's body, an error is raised.
+ * 
+ * PRECONDITION: it is assumed that NumberedVars in the rule's head correspond
+ * to constants.
+ * *)
+let get_aggregation_sql (cnt:colnamtab) rule =
+    let head = rule_head rule in
+    let vars = get_rterm_varlist head in
+    (*Check if the rule has aggregation*)
+    let is_agg = List.exists is_aggvar vars in
+    if not is_agg then "" else
+    let key = symtkey_of_rule rule in
+    let cols = Hashtbl.find cnt key in
+    (*Calculate the GROUP BY clause*)
+    let group_var acc col = function
+        | NamedVar _ -> col::acc
+        | _ -> acc in
+    let grp_cols = List.fold_left2 group_var [] cols vars in
+    let group_by_sql =
+        if grp_cols = [] then ""
+        else ("GROUP BY "^(String.concat ", " grp_cols)) in
+    group_by_sql
+
 (** Takes a list of terms and splits them in positive rterms,
  * negative terms, equalities, and inequalities*)
 let split_terms terms =
     let select_sql = get_select_clause vt eqt head in
     let from_sql = get_from_clause idb p_rt in
     let where_sql = get_where_clause idb vt cnt eqt ineq n_rt in
-    String.concat " " [select_sql;from_sql;where_sql]
+    let agg_sql = get_aggregation_sql cnt rule in
+    String.concat " " [select_sql;from_sql;where_sql;agg_sql]
 
 (**Takes a list of similar rules (same head) and generates the SQL statement
  * that calculates the union of them**)
     | Ineq (">=",v,c) -> Ineq ("<",v,c)
     | _ -> invalid_arg "function negate_eq called without an equation"
 
+let is_aggvar = function
+    | AggVar _ -> true
+    | _ -> false
+
 (****************************************************
  *
  *  String operations

test/expected/integration/payment.out

 | 14011.11 | 7456.435 | 20.5 | 1556.79 | 9    |
 -----------------------------------------------
 
+yadi$ 
+-----------------------------------------------------------------
+| col0     | col1      | col2       | col3   | col4      | col5 |
+-----------------------------------------------------------------
+| Ginny    | 33        | 12         | 10     | 11        | 3    |
+| Hermione | 10108.53  | 4352.23    | 143.43 | 2527.1325 | 4    |
+| Harry    | 14011.11  | 7456.435   | 20.5   | 1556.79   | 9    |
+| Ron      | 650266.21 | 432176.435 | 0.1    | 65026.621 | 10   |
+-----------------------------------------------------------------
+
+yadi$ 
+-----------------------------------------------------
+| col0     | col1                       | col2      |
+-----------------------------------------------------
+| Ron      | Wizard taxes               | 4644.19   |
+| Harry    | Daily Prophet subscription | 74.05     |
+| Ron      | Hogwarts tuition           | 645612.87 |
+| Harry    | Hogwarts tuition           | 10912.87  |
+| Ginny    | Daily Prophet subscription | 33        |
+| Hermione | Hogwarts tuition           | 5612.87   |
+| Hermione | Wizard taxes               | 4495.66   |
+| Harry    | Wizard taxes               | 3024.19   |
+| Ron      | Daily Prophet subscription | 9.15      |
+-----------------------------------------------------
+
+yadi$ 
+---------------
+| col0 | col1 |
+---------------
+| 4    | 3    |
+| 1    | 9    |
+| 3    | 4    |
+| 2    | 10   |
+---------------
+
+yadi$ 
+-------------------------------------
+| col0                | col1 | col2 |
+-------------------------------------
+| 2000-10-15 00:00:00 | 1    | 3    |
+| 1998-10-15 00:00:00 | 1    | 1    |
+| 1999-10-15 00:00:00 | 1    | 1    |
+| 2001-10-15 00:00:00 | 1    | 2    |
+| 2010-10-15 00:00:00 | 1    | 3    |
+| 2003-10-15 00:00:00 | 1    | 2    |
+| 2002-10-15 00:00:00 | 1    | 2    |
+-------------------------------------
+
+yadi$ YADI execution error: Aggregate function 'PROD' is not supported, allowed functions are: MAX, MIN, SUM, AVG, COUNT
+
 yadi$ YADI execution error: Goal Person/2 contains an aggregate function as a variable, which is only allowed in rule heads
 
 yadi$ 

test/integration/payment.datalog

 ?- Harry_stats(x,y,z,v,w).
 /
 
+%%%%%%%%%%%%%%%%%
+% Groupings
+%%%%%%%%%%%%%%%%%
+
+Person_stats(name,sum(a),max(a),min(a),avg(a),count(a)) :- Person(id,name), Payment(id,_,a,_).
+?- Person_stats(n,s,mx,mn,av,cnt).
+/
+
+Person_service_sum(p_name,s_name,sum(amount)) :- Person(p_id,p_name), Service(s_id,s_name), Payment(p_id,s_id,amount,_).
+?- Person_service_sum(p,s,t).
+/
+
+Payment_count(pid,count(pid)) :- Payment(pid,_,_,_).
+?- Payment_count(x,y).
+/
+
+%Aggregates on two different vars
+Q(day,min(pid),max(sid)) :- Payment(pid,sid,_,day).
+?-Q(x,y,z).
+/
+
+%%%%%%%%%%%%%%%%%
+% Non-supported aggregates
+%%%%%%%%%%%%%%%%%
+
+Prod(prod(p)) :- Payment(_,_,p,_).
+?- Prod(pp).
+/
 
 %%%%%%%%%%%%%%%%%
 % Aggregates in goals (not allowed)