#This file is part of SECONDO.
#
#Copyright (C) 2004, University in Hagen, Department of Computer Science,
#Database Systems for New Applications.
#
#SECONDO is free software; you can redistribute it and/or modify
#it under the terms of the GNU General Public License as published by
#the Free Software Foundation; either version 2 of the License, or
#(at your option) any later version.
#
#SECONDO is distributed in the hope that it will be useful,
#but WITHOUT ANY WARRANTY; without even the implied warranty of
#MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#GNU General Public License for more details.
#
#You should have received a copy of the GNU General Public License
#along with SECONDO; if not, write to the Free Software
#Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
#
# a test for the mrelational algebra
# run with : Runner -c MyConfig.config <mrelalgtest
# (Runner can be found in directory UserInterfaces)

# 28.08.2006, M. Spiekermann
# Remark: The operator groupby causes a SIGSEGV!
# Currently it seems not important to fix this bug
# since the OldRelationAlgebra is not frequently used.

# clean up what may be left over from e.g. a segfault
delete database mrelalgtest;

#setup oldrelalg OldRelationAlgebra
create database mrelalgtest;
open database mrelalgtest;

(create empty : (mrel(mtuple((no int)))));
(update empty := ((mrel(mtuple((no int))))()));

(create two : (mrel(mtuple((no int)))));
(update two := ((mrel(mtuple((no int))))((1)(2))));

(create three : (mrel(mtuple((no int)))));
(update three := ((mrel(mtuple((no int))))((1)(1)(1))));

(create ten : (mrel(mtuple((no int)))));
(update ten := ((mrel(mtuple((no int))))((1)(2)(3)(4)(5)(6)(7)(8)(9)(10))));

(create twenty : (mrel(mtuple((no int)))));
(update twenty := ((mrel(mtuple((no int))))((1)(2)(3)(4)(5)(6)(7)(8)(9)(10)(11)(12)(13)(14)(15)(16)(17)(18)(19)(20))));

(create even : (mrel(mtuple((no int)))));
(update even := ((mrel(mtuple((no int))))((2)(4)(6)(8)(10)(12)(14)(16)(18)(20))));

(create odd : (mrel(mtuple((no int)))));
(update odd := ((mrel(mtuple((no int))))((1)(3)(5)(7)(9)(11)(13)(15)(17)(19))));

(create duplicates : (mrel(mtuple((no int)))));
(update duplicates := ((mrel(mtuple((no int))))((1)(1)(1)(6)(6)(7)(9)(9)(9)(9))));

(create real : (mrel(mtuple((no real)))));
(update real := ((mrel(mtuple((no real))))((1.0)(3.0)(-6.0)(7.0)(9.0)(0.875)(-9.875)(-23.625))));

(create Employee :
	(mrel
                (mtuple
                    (
                        (EName string)
                        (EmpNr int)
                        (DeptNr int)))));

(update Employee :=
	((mrel
                (mtuple
                    (
                        (EName string)
                        (EmpNr int)
                        (DeptNr int))))
		(
                ("Smith" 12 3)
                ("Myers" 13 2)
                ("Bush" 11 1)
                ("Jones" 14 2)
                ("Smith" 16 1)
                ("Langdon" 9 3)
                ("Lambert" 4 3)
                ("Callahan" 1 2)
                ("Myers" 17 2)
                ("Simpson" 8 3))));

(create Dept : (mrel
                (mtuple
                    (
                        (Leader string)
                        (DeptNr int)))));

(update Dept := ((mrel
                (mtuple
                    (
                        (Leader string)
                        (DeptNr int))))
            (
                ("Smith" 3)
                ("Myers" 2)
                ("Bush" 1))));


(create Staedte : (mrel
                    (mtuple
                      (
                        (SName string)
                        (Bev int)
                        (PLZ int)
                        (Vorwahl string)
                        (Kennzeichen string)))));



(update Staedte := ((mrel
                (mtuple
                    (
                        (SName string)
                        (Bev int)
                        (PLZ int)
                        (Vorwahl string)
                        (Kennzeichen string))))
            (
                ("Aachen" 239000 5100 "0241" "AC")
                ("Berlin" 1859000 1000 "030" "B")
                ("Bielefeld" 300000 4800 "0521" "BI")
                ("Bochum" 382000 4630 "0234" "BO")
                ("Bonn" 291000 5300 "0228" "BN")
                ("Braunschweig" 248000 3300 "0531" "BS")
                ("Bremen" 526000 2800 "0421" "HB")
                ("Bremerhaven" 134000 2850 "0471" "HB")
                ("Darmstadt" 134000 6100 "06151" "DA")
                ("Dortmund" 572000 4600 "0231" "DO")
                ("Duesseldorf" 562000 4000 "0211" "D")
                ("Duisburg" 518000 4100 "0203" "DU")
                ("Erlangen" 100000 8520 "09131" "ER")
                ("Essen" 620000 4300 "0201" "E")
                ("Frankfurt(Main)" 595000 6000 "069" "F")
                ("Freiburg" 184000 7800 "0761" "FR")
                ("Gelsenkirchen" 285000 4650 "0209" "GE")
                ("Goettingen" 133000 3400 "0551" "GOE")
                ("Hagen" 206000 5800 "02331" "HA")
                ("Hamburg" 1580000 2000 "040" "HH")
                ("Hamm" 166000 4700 "02381" "HAM")
                ("Hannover" 508000 3000 "0511" "H")
                ("Heidelberg" 135000 6900 "06221" "HD")
                ("Heilbronn" 111000 7100 "07131" "HN")
                ("Hildesheim" 101000 3200 "05121" "HI")
                ("Karlsruhe" 268000 7500 "0721" "KA")
                ("Kassel" 184000 3500 "0561" "KS")
                ("Kiel" 246000 2300 "0431" "KI")
                ("Koblenz" 111000 5400 "0261" "KO")
                ("Koeln" 916000 5000 "0221" "K")
                ("Krefeld" 217000 4150 "02151" "KR")
                ("Leverkusen" 155000 5090 "0214" "LEV")
                ("Ludwigshafen" 154000 6700 "0621" "LU")
                ("Luebeck" 210000 2400 "0451" "HL")
                ("Mainz" 189000 6500 "06131" "MZ")
                ("Mannheim" 295000 6800 "0621" "MA")
                ("Moenchengladbach" 254000 4050 "02161" "MG")
                ("Muehlheim(Ruhr)" 172000 4330 "0208" "MH")
                ("Muenchen" 1267000 8000 "089" "M")
                ("Muenster" 270000 4400 "0251" "MS")
                ("Nuernberg" 465000 8500 "0911" "N")
                ("Offenbach(Main)" 107000 6050 "069" "OF")
                ("Oldenburg" 138000 2900 "0441" "OL")
                ("Osnabrueck" 153000 4500 "0541" "OS")
                ("Paderborn" 110000 4790 "05251" "PB")
                ("Pforzheim" 104000 7530 "07231" "PF")
                ("Recklinghausen" 118000 4350 "02361" "RE")
                ("Regensburg" 124000 8400 "0941" "R")
                ("Remscheid" 121000 5630 "02191" "RS")
                ("Saarbruecken" 187000 6600 "0681" "SB")
                ("Salzgitter" 106000 3320 "05341" "SZ")
                ("Siegen" 107000 5900 "0271" "SI")
                ("Solingen" 158000 5650 "0212" "SG")
                ("Stuttgart" 562000 7000 "0711" "S")
                ("Wiesbaden" 267000 6200 "06121" "WI")
                ("Wolfsburg" 122000 3180 "05361" "WOB")
                ("Wuerzburg" 128000 8700 "0931" "WUE")
                ("Wuppertal" 377000 5600 "0202" "W"))));

#testcase Filter1
#yields (int 5)
query ten feed filter[.no > 5] consume count;

#testcase Filter2
#yields (int 0)
query ten feed head[0] filter[.no > 5] consume count;

#testcase Filter3
#yields error
query Staedte feed filter[.SName > 5] consume count;

#testcase Average1
#yields (real 325017.2413793103)
query Staedte feed avg[Bev];

#testcase Average2
#yields (real -2.328125)
query real feed avg[no];

#testcase Average3
#yields error
query Staedte feed avg[SName];

#testcase Average4
#yields (real undefined)
query real feed head[0] avg[no];

#testcase Head1
#yields ((mrel(mtuple((no int))))((1)(2)(3)(4)(5)))
query ten feed head[5] consume;

#testcase Head2
#yields ((mrel(mtuple((no int))))())
query ten feed head[0] consume;

#testcase Concat1
#yields ((mrel(mtuple((no int))))((1)(2)(3)(1)(2)(3)))
query ten feed head[3] twenty feed head[3] concat consume;

#testcase Concat2
#yields ((mrel(mtuple((no int))))((1)(2)(3)))
query ten feed head[0] twenty feed head[3] concat consume;

#testcase Extend1
#yields ((mrel(mtuple((no int)(mod2 int))))((1 1)(2 0)(3 1)))
query ten feed extend[mod2 : .no mod 2] head[3] consume;

#testcase Extend2
#yields ((mrel(mtuple((no int)(mod2 int))))())
query ten feed head[0] extend[mod2 : .no mod 2] head[3] consume;

#testcase Extract1
#yields (int 239000)
query Staedte feed extract[Bev];

#testcase Extract2
#yields (int undefined)
query Staedte feed head[0] extract[Bev];

#-testcase GroupBy1
#-yields ((mrel (mtuple ((DeptNr int) (anz int))))((1 2) (2 4) (3 4)))
#-query Employee feed sortby[DeptNr asc] groupby[DeptNr; anz : group feed count] consume;

#-testcase GroupBy2
#-yields ((mrel (mtuple ((DeptNr int) (anz int))))((1 2) (2 4) (3 4)))
#-query Employee feed sortby[DeptNr asc] groupby[DeptNr; anz : group count] consume;

#-testcase GroupBy3
#-yields ((mrel (mtuple ((DeptNr int) (anz int))))())
#-query Employee feed head[0] sortby[DeptNr asc] groupby[DeptNr; anz : group feed count] consume;

#testcase HashJoin1
#yields ((mrel(mtuple((EName string)(EmpNr int)(DeptNr int)\
    (Leader_A string)(DeptNr_A int))))\
    (("Bush" 11 1 "Bush" 1)("Callahan" 1 2 "Myers" 2)("Jones" 14 2 "Myers" 2)))
query Employee feed Dept feed rename[A] hashjoin[DeptNr, DeptNr_A, 17] sort head[3] consume;

#testcase HashJoin2
#yields ((mrel(mtuple((no int)(no_A int))))((1 1)(6 6)(7 7)(9 9)))
query duplicates feed ten feed rename[A] hashjoin[no, no_A, 23] sort rdup consume

#testcase HashJoin3
#yields (int 0)
query Employee feed head[0] Dept feed rename[A] hashjoin[DeptNr, DeptNr_A, 17] count;

#testcase MergeJoin1
#yields ((mrel(mtuple((EName string)(EmpNr int)(DeptNr int)\
    (Leader_A string)(DeptNr_A int))))\
    (("Bush" 11 1 "Bush" 1)("Callahan" 1 2 "Myers" 2)("Jones" 14 2 "Myers" 2)))
query Employee feed Dept feed rename[A] sortmergejoin[DeptNr, DeptNr_A] sort head[3] consume;

#testcase MergeJoin2
#yields ((mrel(mtuple((no int)(no_A int))))((1 1)(6 6)(7 7)(9 9)))
query duplicates feed ten feed rename[A] mergejoin[no, no_A] sort rdup consume

#testcase MergeJoin3
#yields (int 0)
query Employee feed head[0] Dept feed rename[A] sortmergejoin[DeptNr, DeptNr_A] count;

#testcase Max1
#yields (string "Wuppertal")
query Staedte feed max[SName];

#testcase Max2
#yields (string undefined)
query Staedte feed head[0] max[SName];

#testcase Max3
#yields (real 9.0)
query real feed max[no];

#testcase Min1
#yields (string "Aachen")
query Staedte feed min[SName];

#testcase Min2
#yields (real -23.625)
query real feed min[no];

#testcase Sum1
#yields (int 18851000)
query Staedte feed sum[Bev];

#testcase Sum2
#yields (real -18.625)
query real feed sum[no];

#testcase Sum3
#yields (int undefined)
query Staedte feed head[0] sum[Bev];

#testcase Product1
#yields error
query ten feed twenty feed product count

#testcase Product2
#yields (int 200)
query ten feed twenty feed rename[A] product count

#testcase Product3
#yields (int 0)
query ten feed twenty feed head[0] rename[A] product count

#testcase Sort1
#yields (int 200)
query ten feed twenty feed rename[A] product sort count

#testcase Sort2
#yields (int 0)
query ten feed head[0] twenty feed rename[A] product sort count

#testcase Rdup1
#yields (int 20)
query twenty feed ten feed concat sort rdup count

#testcase Rdup2
#yields (int 0)
query twenty feed ten feed concat sort head[0] rdup count

#testcase Project1
#yields (int 20)
query ten feed twenty feed rename[A] product project[no_A] sort rdup count

#testcase Project2
#yields (int 0)
query ten feed head[0] twenty feed rename[A] product project[no_A] sort rdup count

#testcase Diff1
#yields (int 10)
query twenty feed odd feed mergediff count

#testcase Diff2
#yields (int 100)
query twenty feed odd feed rename[A] product sort ten feed odd feed rename[A] product sort mergediff count

#testcase Diff3
#yields (int 20)
query twenty feed odd feed head[0] mergediff count

#testcase Diff4
#yields (int 0)
query twenty feed head[0] odd feed mergediff count

#testcase Sec1
#yields (int 10)
query twenty feed odd feed mergesec count

#testcase Sec2
#yields (int 100)
query twenty feed odd feed rename[A] product sort ten feed odd feed rename[A] product sort mergesec count

#testcase Sec3
#yields (int 0)
query twenty feed head[0] odd feed mergesec count

#testcase Union1
#yields (int 20)
query twenty feed odd feed mergeunion count

#testcase Union2
#yields (int 200)
query twenty feed odd feed rename[A] product sort ten feed odd feed rename[A] product sort mergeunion count

#testcase Union3
#yields (int 10)
query twenty feed head[0] odd feed mergeunion count

#testcase Sample1
#yields error
query Staedte sample[a, 0.1] count

#testcase Sample2
#yields error
query 1 sample[10, 0.1] count

#testcase Sample3
#yields (int 58)
query Staedte sample[100, 0.01] count

#testcase Sample4
#yields (int 0)
query Staedte feed head[0] consume sample[100, 0.01] count

#testcase Sample5
#yields (int 58)
query Staedte sample[-2345, 200.0] count

#testcase Sample6
#yields (int 10)
query Staedte sample[10, 0.0] count

#testcase Sample7
#yields (int 58)
query Staedte sample[0, 1.0] count


#teardown
close database;
delete database mrelalgtest;