Commits

cacol89 committed ffe31c2 Merge

Merge branch 'feature/agregate_functions' into develop

Comments (0)

Files changed (10)

 
 ------------------------------------------------------
 
-Current maintainters for YADI project :
+Past contributors of the project:
 
-Carlos Colmenares   <carlos.a.colmenares.r@gmail.com>
 Mark Abspoel        <mail@markabspoel.nl>
 Jose Robles Noriega <atease@vista.aero>
 Hasan Saygin Arkan  <sayginarkan@gmail.com>
 
+------------------------------------------------------
+
+Current maintainters for YADI project:
+
+Carlos Colmenares   <carlos.a.colmenares.r@gmail.com>
+
 <https://bitbucket.org/cacol89/yadi/>
 
 ------------------------------------------------------
     let (aliases,_) = List.fold_right set_alias rterms ([],len-1) in
     "FROM "^(String.concat ", " aliases)
 
+(** Given an aggregate function name, checks if it is supported by YADI and
+ * returns it*)
+let check_agg_function fn =
+    let allowed = ["MAX";"MIN";"SUM";"AVG";"COUNT"] in
+    if List.mem fn allowed then fn
+    else raise (Yadi_error (
+        "Aggregate function '"^fn^"' is not supported, "^
+        "allowed functions are: "^(String.concat ", " allowed)
+    ))
+
+(*Given a variable name, returns the name of a EDB/IDB column
+ * that defines it, or if it is equal to a constant, the
+ * value of the constant.*)
+let vname_to_col (vt:vartab) (eqt:eqtab) key vname =
+    (*If the variable appears in a positive rterm, the value
+     * is the name of the respective rterm's table column*)
+    if Hashtbl.mem vt vname
+        then List.hd (Hashtbl.find vt vname)
+    (*If the variable does not appear in a positive rterm, but
+     * it does in an equality value, then the value is the eq's
+     * constant, the var has to be removed from the eqtab*)
+    else if Hashtbl.mem eqt vname
+        then string_of_const (eqt_extract eqt vname)
+    (*Else, the query is unsafe or inclomplete*)
+    else raise (Yadi_error (
+            "Predicate "^(string_of_symtkey key)^
+            " is unsafe, variable "^vname^" is not in a positive "^
+            "goal or strict equality relation."
+        )
+    )
+
 (** Given the head of the rule, the vartab, and te eqtab, returns the code that
  * must be in the select clause. All columns are aliased as col0, col1, ...*)
 let get_select_clause (vt:vartab) (eqt:eqtab) rterm =
     let vlst = get_rterm_varlist rterm in 
+    let key = symtkey_of_rterm rterm in
     if vlst = [] then
         raise (Yadi_error
             ("Predicate "^(get_rterm_predname rterm)^
             " has arity 0, which is not allowed"))
     else
-    let vnames = List.map string_of_var vlst in
-    let var_value vname =
-        (*If the variable appears in a positive rterm, the value
-         * is the name of the respective rterm's table column*)
-        if Hashtbl.mem vt vname
-            then List.hd (Hashtbl.find vt vname)
-        (*If the variable does not appear in a positive rterm, but
-         * it does in an equality value, then the value is the eq's
-         * constant, the var has to be removed from the eqtab*)
-        else if Hashtbl.mem eqt vname
-            then string_of_const (eqt_extract eqt vname)
-        (*Else, the query is unsafe or inclomplete*)
-        else raise (Yadi_error (
-                "Predicate "^(string_of_symtkey (symtkey_of_rterm rterm))^
-                " is unsafe, variable "^vname^" is not in a positive "^
-                "goal or strict equality relation."
-            )
-        )
+    (*Transform variables to column names. Treat namedVars and
+     * aggregates differently*)
+    let var_value v = match v with
+        NamedVar _ | NumberedVar _ ->
+            vname_to_col vt eqt key (string_of_var v)
+        | AggVar (fn,vn) ->
+            (check_agg_function fn)^"("^(vname_to_col vt eqt key vn)^")"
+        | _ -> invalid_arg ("not-expected vartype in head of predicate"^
+            (string_of_symtkey key))
     in
-    let cols = List.map var_value vnames in
+    let cols = List.map var_value vlst in
+    (*Create aliases*)
     let rec alias ind = function
         | [] -> ""
         | [col] -> col^" AS col"^(string_of_int ind)
                 )) 
             | ConstVar c -> (eq_to^(string_of_const c))::acc
             | AnonVar -> acc
-            | NumberedVar _ -> invalid_arg "There is a numbered var in a negated rterm"
+            | _ -> invalid_arg "There is a non-expected type of var in a negated rterm"
         in
         let const_lst = List.fold_left2 build_const [] cnames vlst in
         let where_sql =
     let feqt = Hashtbl.fold eq_const eqt [] in
     (*Transform the inequalities in the list for strings of the form
      * "CName op value" *)
+    let ineq_tuples = List.map extract_ineq_tuple ineq in
     let ineq_const (op,var,value) acc =
         let vname = string_of_var var in
         let cname = List.hd (Hashtbl.find vt vname) in
         (cname^" "^op^" "^(string_of_const value))::acc in
-    let fineq = List.fold_right ineq_const ineq [] in
+    let fineq = List.fold_right ineq_const ineq_tuples [] in
     (*Transform the negated rterms into SQL*)
     let fnrt = sql_of_negated_rterms idb vt cnt eqt neg_rt in
     (*merge all constraints*)
         | [] -> ""
         | _ -> "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.
+ * The HAVING clause will correspond to comparissons with aggregates.
+ *
+ * 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.
+ * 
+ * If there are comparissons with respect to aggregates that do not appear
+ * in the rule head, an error is also raised.
+ *
+ * PRECONDITION: it is assumed that NumberedVars in the rule's head correspond
+ * to constants.
+ * *)
+let get_aggregation_sql (vt:vartab) (cnt:colnamtab) head agg_eqs agg_ineqs =
+    let vars = get_rterm_varlist head in
+    let key = symtkey_of_rterm head in
+    (*Merge the equalities and inequalities in a simple list*)
+    let eq_t = List.map extract_eq_tuple agg_eqs in
+    let aug_eq_t = List.map (fun (x,y) -> ("=",x,y)) eq_t in
+    let ieq_t = List.map extract_ineq_tuple agg_ineqs in
+    let comparisons = aug_eq_t@ieq_t in
+    (*Check if the rule has aggregation*)
+    let is_agg = List.exists is_aggvar vars in
+    if not is_agg then
+        if comparisons = [] then ""
+        else raise (Yadi_error (
+            "Predicate "^(string_of_symtkey key)^
+            " contains comparisons of aggregates but defines no "^
+            "aggregations in its head"))
+    else
+    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
+    (*Calculate the HAVING clause*)
+    (*Extract the available aggregations in the head, and place them
+     * in a list, which values will be the function applied to a column-name*)
+    let av_aggs = Hashtbl.create 100 in
+    let fake_eqt:eqtab = Hashtbl.create 100 in
+    let insert_agg = function
+        | AggVar (fn,vn) ->
+            let col = vname_to_col vt fake_eqt key vn in
+            Hashtbl.add av_aggs (fn,vn) (fn^"("^col^")")
+        | _ -> () in
+    List.iter insert_agg vars;
+    (*Build the contraints and check for unavailable aggregates*)
+    let agg_var_col agv =
+        let tuple = extract_aggvar_tuple agv in
+        if Hashtbl.mem av_aggs tuple then Hashtbl.find av_aggs tuple
+        else raise (Yadi_error (
+            "Predicate "^(string_of_symtkey key)^" contains comparisons of "^
+            "aggregates that are not defined in its head"
+        )) in
+    let comp_const (op,var,const) =
+        (agg_var_col var)^" "^op^" "^(string_of_const const) in 
+    let comp_sql = List.map comp_const comparisons in
+    let having_sql = if comp_sql = [] then "" else
+        "HAVING "^(String.concat " AND " comp_sql) in
+    group_by_sql^" "^having_sql
+
 (** Takes a list of terms and splits them in positive rterms,
  * negative terms, equalities, and inequalities*)
 let split_terms terms =
     let rec split t (pos,neg,eq,inq) = match t with
         | Rel rt -> (rt::pos,neg,eq,inq)
         | Not rt -> (pos,rt::neg,eq,inq)
-        | Equal (x,y) -> (pos,neg,(x,y)::eq,inq) 
-        | Ineq (op,x,y) -> (pos,neg,eq,(op,x,y)::inq) in
+        | Equal _ -> (pos,neg,t::eq,inq) 
+        | Ineq _ -> (pos,neg,eq,t::inq) in
     List.fold_right split terms ([],[],[],[])
 
 (** Takes a rule and makes a SQL query that calculates its result*)
 let sql_of_rule (idb:symtable) (cnt:colnamtab) rule =
     let head = rule_head rule in
     let body = rule_body rule in
-    (*Extract positive rterms from the rule*)
-    let (p_rt,n_rt,equalities,ineq) = split_terms body in
+    (*Split terms in the rule's body. Separate equalities
+     * and inequalities in variable and aggregates relations.*)
+    let (p_rt,n_rt,all_eqs,all_ineqs) = split_terms body in
+    let (agg_eqs,eqs) = List.partition is_agg_equality all_eqs in
+    let (agg_ineqs,ineqs) = List.partition is_agg_inequality all_ineqs in
     (*Build vartab, and eqtab for select and where clauses*)
     let vt = build_vartab cnt p_rt in
-    let eqt = build_eqtab equalities in
-    let select_sql = get_select_clause vt eqt head in
+    let eqtb = build_eqtab eqs in
+    let select_sql = get_select_clause vt eqtb 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 where_sql = get_where_clause idb vt cnt eqtb ineqs n_rt in
+    let agg_sql = get_aggregation_sql vt cnt head agg_eqs agg_ineqs 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**)
     | NumberedVar of int
     | ConstVar of const
     | AnonVar
+    | AggVar of string * string
 and stt =
     | Rule of rterm * term list    
     | Query of rterm                 
         List.fold_left extract_rterm [] t
     | Query _    -> invalid_arg "function get_all_rule_rterms called with a query"
 
+let extract_eq_tuple = function
+    | Equal (v,c) -> (v,c)
+    | _ -> invalid_arg "function extract_eq_tuple called without an equality"
+
+let extract_ineq_tuple = function
+    | Ineq (s,v,c) -> (s,v,c)
+    | _ -> invalid_arg "function extract_ineq_tuple called without an inequality"
+
+let extract_aggvar_tuple = function
+    | AggVar (fn,vn) -> (fn,vn)
+    | _ -> invalid_arg "function extract_aggvar_tuple called without an aggregated var"
+
+(****************************************************
+ *
+ *  AST check / transformation functions
+ *
+ ****************************************************)
+
 (*Given an equation, returns the equivalent of a negation of it*)
 let negate_eq = function
     | Equal (v,c) -> Ineq ("<>",v,c)
     | Ineq (">=",v,c) -> Ineq ("<",v,c)
     | _ -> invalid_arg "function negate_eq called without an equation"
 
+let is_aggvar = function
+    | AggVar _ -> true
+    | _ -> false
+
+let is_agg_equality = function
+    | Equal (AggVar _ , _ ) -> true
+    | Equal _ -> false
+    | _ -> invalid_arg "function is_agg_equality called without an equality"
+
+let is_agg_inequality = function
+    | Ineq (_ , AggVar _ , _) -> true
+    | Ineq _ -> false
+    | _ -> invalid_arg "function is_agg_inequality called without an equality"
+
 (****************************************************
  *
  *  String operations
     | NumberedVar x -> "_" ^ string_of_int x
     | AnonVar    -> "_"
     | ConstVar x -> string_of_const x
+    | AggVar (fn,vn) -> fn^"("^vn^")"
 ;;
 
 (** support function for smart stringify of the AST - see to_string below *)
   ;
 
   equation:	
-  | VARNAME EQ constant						{ Equal (NamedVar $1, $3) }
-  | VARNAME NE constant						{ Ineq ("<>", NamedVar $1, $3) }
-  | VARNAME LT constant						{ Ineq ("<", NamedVar $1, $3) }
-  | VARNAME GT constant						{ Ineq (">", NamedVar $1, $3) }
-  | VARNAME LE constant						{ Ineq ("<=", NamedVar $1, $3) }
-  | VARNAME GE constant						{ Ineq (">=", NamedVar $1, $3) }
+  | var_or_agg EQ constant	{ Equal ($1, $3) }
+  | var_or_agg NE constant	{ Ineq ("<>", $1, $3) }
+  | var_or_agg LT constant	{ Ineq ( "<", $1, $3) }
+  | var_or_agg GT constant	{ Ineq ( ">", $1, $3) }
+  | var_or_agg LE constant	{ Ineq ("<=", $1, $3) }
+  | var_or_agg GE constant	{ Ineq (">=", $1, $3) }
+  ;
+
+  var_or_agg:
+  | VARNAME     { NamedVar $1 }
+  | aggregate   { $1 }
   ;
 
   constant:
   | VARNAME     { NamedVar $1 }
   | ANONVAR     { AnonVar }
   | constant    { ConstVar $1 }
+  | aggregate    { $1 }
+  ;
+
+  aggregate:
+  | VARNAME LPAREN VARNAME RPAREN       { AggVar (String.uppercase $1,$3) }
   ;

src/yadi_utils.ml

                 "_"^(string_of_int n)^"."^cn
             in
             match v with
-            | NamedVar _ ->
-                vt_insert vt (string_of_var v) comp_cn
-            | NumberedVar _ ->
+            NamedVar _ | NumberedVar _ ->
                 vt_insert vt (string_of_var v) comp_cn
+            | AggVar _ -> raise (Yadi_error (
+                    "Goal "^(string_of_symtkey key)^
+                    " contains an aggregate function as a variable, "^
+                    "which is only allowed in rule heads"
+                ))
             | _ -> ()
         in
         List.iter2 in_v cols vlst;
  * must be satisfied by the variables*) 
 type eqtab = (string,const) Hashtbl.t
 
-(** Given a list of (var,const) tuples, returns an eqtab with
- * the equality relations as var = value*)
-let build_eqtab tuples =
+(** Given a list of equality ASTs, returns an eqtab with
+ * the equality relations as var = value.
+ * PRECONDITION: There should not be aggregate equalities
+ * in the provided list.*)
+let build_eqtab eqs =
+    let tuples = List.map extract_eq_tuple eqs in
     let hs:eqtab = Hashtbl.create 100 in
     let add_rel (var,c) = match var with
-        | NamedVar _ -> Hashtbl.add hs (string_of_var var) c
-        | NumberedVar _ -> Hashtbl.add hs (string_of_var var) c
+        NamedVar _ | NumberedVar _ -> Hashtbl.add hs (string_of_var var) c
         | _ -> invalid_arg "Trying to build_eqtab with equalities not of the form var = const" in
     List.iter add_rel tuples;
     hs

test/expected/integration/payment.out

+
+yadi$ 
+-------------
+| col0      |
+-------------
+| 674418.85 |
+-------------
+
+yadi$ 
+-----------------------------------------------------------
+| col0      | col1       | col2 | col3             | col4 |
+-----------------------------------------------------------
+| 674418.85 | 432176.435 | 0.1  | 25939.1865384615 | 26   |
+-----------------------------------------------------------
+
+yadi$ 
+-----------------------------------------------
+| col0     | col1     | col2 | col3    | col4 |
+-----------------------------------------------
+| 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$ 
+-----------------------
+| col0     | col1     |
+-----------------------
+| Ginny    | 33       |
+| Hermione | 10108.53 |
+-----------------------
+
+yadi$ 
+-----------------------------------
+| col0     | col1     | col2      |
+-----------------------------------
+| Hermione | 10108.53 | 2527.1325 |
+-----------------------------------
+
+yadi$ 
+-------------------------------------
+| col0                | col1 | col2 |
+-------------------------------------
+| 2001-10-15 00:00:00 | 3    | 2    |
+-------------------------------------
+
+yadi$ 

test/expected/integration/payment2.out

+
+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$ YADI parse error
+
+yadi$ YADI parse error
+
+yadi$ YADI parse error
+
+yadi$ YADI parse error
+
+yadi$ YADI parse error
+
+yadi$ YADI execution error: Incomplete program, predicate Q/1 not defined
+
+yadi$ YADI execution error: Predicate Q/2 is unsafe, variable x is not in a positive goal or strict equality relation.
+
+yadi$ YADI execution error: Predicate Q/2 contains comparisons of aggregates but defines no aggregations in its head
+
+yadi$ YADI execution error: Predicate Q/1 contains comparisons of aggregates that are not defined in its head
+
+yadi$ YADI execution error: Predicate Q/1 contains comparisons of aggregates that are not defined in its head
+
+yadi$ 

test/integration/payment.datalog

+%%%%%%%%%%%%%%%%%
+% Whole-table aggregates 
+%%%%%%%%%%%%%%%%%
+
+Total(sum(p)) :- Payment(_,_,p,_).
+?- Total(sp).
+/
+
+Stats(sum(p),max(p),min(p),avg(p),count(p)) :- Payment(_,_,p,_).
+?- Stats(x,y,z,v,w).
+/
+
+Harry_stats(sum(p),max(p),min(p),avg(p),count(p)) :- Payment(x,_,p,_), Person(x,'Harry').
+?- 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).
+/
+
+
+%%%%%%%%%%%%%%%%%%
+% Having clauses
+%%%%%%%%%%%%%%%%%%
+
+Total(name,sum(x)) :- Person(pid,name), Payment(pid,_,x,_), sum(x) < 12000.
+?- Total(name,sum).
+/
+
+Total(name,sum(x),avg(x)) :- Person(pid,name), Payment(pid,_,x,_), sum(x) < 12000, avg(x) > 20.
+?- Total(name,sum,avg).
+/
+
+Q(d,max(y),min(z)) :- Payment(y,z,_,d), max(y) = 3, min(z) <> 3.
+?- Q(d,mx,mn).
+/

test/integration/payment2.datalog

+%............................................
+%............................................
+%............................................
+% All the test cases in this file contain
+% sintactic/semantic errors: they test if
+% yadi discovers and reports the errors.
+%............................................
+%............................................
+%............................................
+
+%%%%%%%%%%%%%%%%%
+% Non-supported aggregates
+%%%%%%%%%%%%%%%%%
+
+Q(prod(p)) :- Payment(_,_,p,_).
+?- Q(pp).
+/
+
+%%%%%%%%%%%%%%%%%
+% Aggregates in goals (not allowed)
+%%%%%%%%%%%%%%%%%
+
+Q(x,y) :- Person(x,avg(y)).
+?- Q(x,y).
+/
+
+%%%%%%%%%%%%%%%%%
+% Aggregates of constants (parse error, not allowed)
+%%%%%%%%%%%%%%%%%
+
+Q(sum(1)) :- .
+?- Q(x).
+/
+
+%%%%%%%%%%%%%%%%%
+% Safety analysis
+%%%%%%%%%%%%%%%%%
+
+Q(sum(x),y) :- Payment(y,_,_,_).
+?- Q(x,y).
+/
+
+%%%%%%%%%%%%%%%%%
+% Comparisons of undefined aggregates
+%%%%%%%%%%%%%%%%%
+
+Q(x,y) :- Payment(x,_,y,_), sum(y) < 100.
+?- Q(x,y).
+/
+
+Q(sum(x)) :- Person(x,_), sum(y) < 5.
+?- Q(x).
+/
+
+Q(sum(x)) :- Person(x,_), max(x) < 5.
+?- Q(x).
+/
+

test/sql/payments.sql

+DROP TABLE IF EXISTS Person CASCADE;
+DROP TABLE IF EXISTS Service CASCADE;
+DROP TABLE IF EXISTS Payment CASCADE;
+
+CREATE TABLE Person (
+    Id INTEGER PRIMARY KEY NOT NULL,
+    Name TEXT NOT NULL
+);
+
+CREATE TABLE Service (
+    Id INTEGER PRIMARY KEY NOT NULL,
+    Description TEXT NOT NULL
+);
+
+CREATE TABLE Payment (
+    PersonId INTEGER REFERENCES Person(Id),
+    ServiceId INTEGER REFERENCES Service(Id),
+    Amount FLOAT,
+    Date TIMESTAMP
+);
+
+INSERT INTO Person(Id,Name) VALUES(1,'Harry');
+INSERT INTO Person(Id,Name) VALUES(2,'Ron');
+INSERT INTO Person(Id,Name) VALUES(3,'Hermione');
+INSERT INTO Person(Id,Name) VALUES(4,'Ginny');
+
+INSERT INTO Service(Id,Description) VALUES(1,'Daily Prophet subscription');
+INSERT INTO Service(Id,Description) VALUES(2,'Wizard taxes');
+INSERT INTO Service(Id,Description) VALUES(3,'Hogwarts tuition');
+
+INSERT INTO Payment(PersonId,ServiceId,Amount,Date) VALUES(1,1,23.45,'1998-10-15');
+INSERT INTO Payment(PersonId,ServiceId,Amount,Date) VALUES(1,1,20.50,'1999-10-15');
+INSERT INTO Payment(PersonId,ServiceId,Amount,Date) VALUES(1,1,30.10,'2000-10-15');
+INSERT INTO Payment(PersonId,ServiceId,Amount,Date) VALUES(1,2,123.43,'2000-10-15');
+INSERT INTO Payment(PersonId,ServiceId,Amount,Date) VALUES(1,2,432.23,'2001-10-15');
+INSERT INTO Payment(PersonId,ServiceId,Amount,Date) VALUES(1,2,1234.33,'2002-10-15');
+INSERT INTO Payment(PersonId,ServiceId,Amount,Date) VALUES(1,2,1234.2,'2003-10-15');
+INSERT INTO Payment(PersonId,ServiceId,Amount,Date) VALUES(1,3,3456.435,'2000-10-15');
+INSERT INTO Payment(PersonId,ServiceId,Amount,Date) VALUES(1,3,7456.435,'2010-10-15');
+
+INSERT INTO Payment(PersonId,ServiceId,Amount,Date) VALUES(2,1,3.45,'1998-10-15');
+INSERT INTO Payment(PersonId,ServiceId,Amount,Date) VALUES(2,1,2.50,'1999-10-15');
+INSERT INTO Payment(PersonId,ServiceId,Amount,Date) VALUES(2,1,3.10,'2000-10-15');
+INSERT INTO Payment(PersonId,ServiceId,Amount,Date) VALUES(2,1,0.10,'2000-10-15');
+INSERT INTO Payment(PersonId,ServiceId,Amount,Date) VALUES(2,2,143.43,'2000-10-15');
+INSERT INTO Payment(PersonId,ServiceId,Amount,Date) VALUES(2,2,4352.23,'2001-10-15');
+INSERT INTO Payment(PersonId,ServiceId,Amount,Date) VALUES(2,2,134.33,'2002-10-15');
+INSERT INTO Payment(PersonId,ServiceId,Amount,Date) VALUES(2,2,14.2,'2003-10-15');
+INSERT INTO Payment(PersonId,ServiceId,Amount,Date) VALUES(2,3,213436.435,'2000-10-15');
+INSERT INTO Payment(PersonId,ServiceId,Amount,Date) VALUES(2,3,432176.435,'2010-10-15');
+
+INSERT INTO Payment(PersonId,ServiceId,Amount,Date) VALUES(3,2,143.43,'2000-10-15');
+INSERT INTO Payment(PersonId,ServiceId,Amount,Date) VALUES(3,2,4352.23,'2001-10-15');
+INSERT INTO Payment(PersonId,ServiceId,Amount,Date) VALUES(3,3,3436.435,'2000-10-15');
+INSERT INTO Payment(PersonId,ServiceId,Amount,Date) VALUES(3,3,2176.435,'2010-10-15');
+
+INSERT INTO Payment(PersonId,ServiceId,Amount,Date) VALUES(4,1,10,'1998-10-15');
+INSERT INTO Payment(PersonId,ServiceId,Amount,Date) VALUES(4,1,11,'1999-10-15');
+INSERT INTO Payment(PersonId,ServiceId,Amount,Date) VALUES(4,1,12,'2000-10-15');