DBFunctor-0.1.0.0: test/DBFTests.hs
{-
main :: IO ()
main = putStrLn "Test suite not yet implemented"
-}
{-# LANGUAGE OverloadedStrings #-}
{-# LANGUAGE ViewPatterns #-}
--{-# LANGUAGE BangPatterns #-}
module Main where
import qualified RTable.Data.CSV as C
import qualified RTable.Core as T
import qualified Etl.Internal.Core as E
import Etl.Julius
--import RTable
--import QProcessor
--import System.IO
import System.Environment (getArgs, setEnv)
import Data.Char (toLower)
import Data.HashMap.Strict ((!))
-- Data.Maybe
import Data.Maybe (fromJust)
-- Text
import Data.Text (Text,stripSuffix, pack, unpack, append,take, takeEnd)
-- Vector
import Data.Vector (toList)
-- List
import Data.List (groupBy)
-- Test command: stack exec -- csvdb ./misc/test.csv 20 ./misc/testo.csv
-- or, if you want to test with non-default csv options:
-- stack exec -- csvdb ./misc/test_options.csv 20 ./misc/testo.csv
main :: IO ()
main = do
--setEnv "NLS_LANG" "Greek_Greece.UTF8"
{-args <- getArgs
let fi = args!!0
n = args!!1
fo = args!!2-}
let fi = "./test/data/test_options.csv"
n = "20"
fo = "./test/data/testo.csv"
--csv <- C.readCSVFile fi
--csv <- C.readCSV fi
let myoptions = C.CSVOptions { C.delimiter = ';', C.hasHeader = C.Yes}
csv <- C.readCSVwithOptions myoptions fi
-- debug
{--
C.writeCSV fo csv
C.printCSV csv
--}
--let newcsv = selectNrows (read n) csv
let rtmdata = T.createRTableMData ( "TestTable",
[ ("Name", T.Varchar),
("MyDate", T.Date "DD/MM/YYYY"),
("MyTime", T.Timestamp "DD/MM/YYYY HH24:MI:SS"),
("Number", T.Integer),
("DNumber", T.Double)
]
)
[] -- primary key
[] -- list of unique keys
-- *** test CSV to RTable conversion
rtab = C.toRTable rtmdata csv
rtabNew = T.limit (read n) rtab
csvNew = C.fromRTable rtmdata rtabNew
-- *** test RFilter & RProjection operation
rtabNew2 = let
myfilter = T.f (\t -> t!"Name" == T.RText {T.rtext = "Karagiannidis"})
myprojection = T.p ["Name","MyTime","Number"]
myfilter2 = T.f (\t -> t!"Number" > T.RInt {T.rint = 30 })
myprojection2 = T.p ["Name","Number"]
in myprojection2.myfilter2.myprojection.myfilter $ rtab -- ***** THIS IS AN ETL PIPELINE IMPLEMENTED AS FUNCTION COMPOSITION !!!! *****
rtmdata2 = T.createRTableMData ( "TestTable2",
[ ("Name", T.Varchar),
--("MyDate", T.Date "DD/MM/YYYY"),
-- ("MyTime", T.Timestamp "DD/MM/YYYY HH24:MI:SS"),
("Number", T.Integer)
--("DNumber", T.Double)
]
)
[] -- primary key
[] -- list of unique keys
csvNew2 = C.fromRTable rtmdata2 rtabNew2
-- Test Julius
rtabNew2_J = E.etl $ evalJulius $
EtlMapStart
:-> (EtlR $
ROpStart
:. (Filter (From $ Tab rtab) $ FilterBy (\t -> t!"Name" == T.RText {T.rtext = "Karagiannidis"}))
:. (Select ["Name","MyTime","Number"] $ From Previous)
:. (Filter (From Previous) $ FilterBy (\t -> t!"Number" > T.RInt {T.rint = 30 }))
:. (Select ["Name","Number"] $ From Previous)
)
{- rtabNew2_J = E.etl $ evalJulius $
(EtlR $
(Select ["Name","Number"] $ From Previous)
:. (Filter (From Previous) $ FilterBy (\t -> t!"Number" > T.RInt {T.rint = 30 }))
:. (Select ["Name","MyTime","Number"] $ From Previous)
:. (Filter (From $ Tab rtab) $ FilterBy (\t -> t!"Name" == T.RText {T.rtext = "Karagiannidis"}) )
:. ROpEmpty)
:-> EtlMapEmpty
-}
csvNew2_J = C.fromRTable rtmdata2 rtabNew2_J
--print / write to file
C.writeCSV fo csvNew
C.printCSV csvNew
T.printRTable rtabNew
let foName2 = (fromJust (stripSuffix ".csv" (pack fo))) `mappend` "_t2.csv"
C.writeCSV (unpack foName2) csvNew2
C.printCSV csvNew2
let foName2_J = (fromJust (stripSuffix ".csv" (pack fo))) `mappend` "_t2_J.csv"
C.writeCSV (unpack foName2_J) csvNew2_J
C.printCSV csvNew2_J
T.printRTable rtabNew2_J
-- *** test Column Mapping
-- create a new column holding the doubled value from the source column
let cmap1 = E.RMap1x1 {E.srcCol = "Number", E.removeSrcCol = E.No, E.trgCol = "NewNumber", E.transform1x1 = \x -> 2*x, E.srcRTupleFilter = \_ -> True}
rtabNew3 = E.runCM cmap1 rtabNew2
rtmdata3 = T.createRTableMData ( "TestTable3",
[ ("Name", T.Varchar)
,("Number", T.Integer)
,("NewNumber", T.Integer)
]
)
[] -- primary key
[] -- list of unique keys
-- Test Julius
rtabNew3_J = E.etl $ evalJulius $
EtlMapStart
:-> (EtlC $
Source ["Number"] $
Target ["NewNumber"] $
By (\[x] -> [2*x]) (On $ Tab rtabNew2)
DontRemoveSrc $
FilterBy (\_ -> True) )
{- rtabNew3_J = E.etl $ evalJulius $
(EtlC $
Source ["Number"] $
Target ["NewNumber"] $
By (\[x] -> [2*x]) (On $ Tab rtabNew2)
DontRemoveSrc $
FilterBy (\_ -> True) )
:-> EtlMapEmpty
-}
writeResult fo "_t3.csv" rtmdata3 rtabNew3
writeResult fo "_t3_J.csv" rtmdata3 rtabNew3_J
T.printRTable rtabNew3_J
-- foName3 = (fromJust (stripSuffix ".csv" (pack fo))) `mappend` "_t3.csv"
-- csvNew3 = C.fromRTable rtmdata3 rtabNew3
-- C.writeCSV (unpack foName3) csvNew3
-- C.printCSV csvNew3
-- *** test inner join operation
let rtabNew4 = T.iJ (\t1 t2 -> t1!"Number" == t2!"Number") rtabNew3 rtab
rtmdata4 = T.createRTableMData ( "TestTable4",
[ ("Name", T.Varchar),
("MyDate", T.Date "DD/MM/YYYY"),
("MyTime", T.Timestamp "DD/MM/YYYY HH24:MI:SS"),
("Number", T.Integer),
("DNumber", T.Double),
("NewNumber", T.Integer)
]
)
[] -- primary key
[] -- list of unique keys
-- Test Julius
rtabNew4_J = E.etl $ evalJulius $
EtlMapStart
:-> (EtlR $
ROpStart
:. (Join (TabL rtabNew3) (Tab rtab) $ JoinOn (\t1 t2 -> t1!"Number" == t2!"Number"))
)
{- rtabNew4_J = E.etl $ evalJulius $
(EtlR $
(Join (TabL rtabNew3) (Tab rtab) $ JoinOn (\t1 t2 -> t1!"Number" == t2!"Number"))
:. ROpEmpty)
:-> EtlMapEmpty
-}
writeResult fo "_t4.csv" rtmdata4 rtabNew4
writeResult fo "_t4_J.csv" rtmdata4 rtabNew4_J
putStrLn "TEST INNER JOIN"
T.printRTable rtabNew4_J
-- *** Test union, interesection, diff
let -- change the value in column NewNumber
cmap2 = E.RMap1x1 {E.srcCol = "NewNumber", E.removeSrcCol = E.No, E.trgCol = "NewNumber", E.transform1x1 = \x -> x + 100, E.srcRTupleFilter = \_ -> True}
-- now union the two rtables
rtabNew5 = rtabNew4 `T.u` (E.runCM cmap2 rtabNew4)
rtmdata5 = T.createRTableMData ( "TestTable5",
[ ("Name", T.Varchar),
("MyDate", T.Date "DD/MM/YYYY"),
("MyTime", T.Timestamp "DD/MM/YYYY HH24:MI:SS"),
("Number", T.Integer),
("DNumber", T.Double),
("NewNumber", T.Integer)
]
)
[] -- primary key
[] -- list of unique keys
rtabNew6 = rtabNew5 `T.i` rtabNew4
rtabNew7 = rtabNew5 `T.d` rtabNew6
-- Test Julius
rtabNew7_J = E.etl $ evalJulius $
EtlMapStart
:-> (EtlC $
Source ["NewNumber"] $
Target ["NewNumber"] $
By (\[x] -> [x + 100]) (On $ Tab rtabNew4)
DontRemoveSrc $
FilterBy (\_ -> True))
:-> (EtlR $ -- rtabNew5
ROpStart
:. (Union (TabL rtabNew4) (Previous))
)
:-> (EtlR $ -- rtabNew6
ROpStart
:. (Intersect (TabL rtabNew4) (Previous))
)
:-> (EtlR $
ROpStart
:. (Minus
(TabL $ -- this is the rtabNew5 repeated
E.etl $ evalJulius $
EtlMapStart
:-> (EtlC $
Source ["NewNumber"] $
Target ["NewNumber"] $
By (\[x] -> [x + 100]) (On $ Tab rtabNew4)
DontRemoveSrc $
FilterBy (\_ -> True))
:-> (EtlR $ -- rtabNew5
ROpStart
:. (Union (TabL rtabNew4) (Previous))
)
)
(Previous))
)
rtabNew7_J2 = E.etl $ evalJulius $
EtlMapStart
:-> (EtlR $ -- rtabNew5
ROpStart
:. (Minus (TabL rtabNew5) (Tab rtabNew6))
)
rtabNew7_J3 = E.etl $ evalJulius $
EtlMapStart
:-> (EtlR $ -- rtabNew5
ROpStart
:. (MinusP (Tab rtabNew5) (TabL rtabNew6))
)
-- test Intermediate named results
rtabNew7a_J = E.etl $ evalJulius $ etlXpression
etlXpression =
EtlMapStart
:-> (EtlC $
Source ["NewNumber"] $
Target ["NewNumber"] $
By (\[x] -> [x + 100]) (On $ Tab rtabNew4)
DontRemoveSrc $
FilterBy (\_ -> True))
:=> NamedResult "rtabNew5" (EtlR $ -- rtabNew5
ROpStart
:. (Union (TabL rtabNew4) (Previous))
)
:-> (EtlR $ -- rtabNew6
ROpStart
:. (Intersect (TabL rtabNew4) (Previous))
)
:-> (EtlR $
ROpStart
:. (Minus
(TabL $ -- this is the rtabNew5 repeated
juliusToRTable $ takeNamedResult "rtabNew5" etlXpression --- THIS IS THE POINT TO BE TESTED!!!
)
(Previous))
)
writeResult fo "_t7a_J.csv" rtmdata5 rtabNew7a_J
T.printRTable rtabNew7a_J
{- rtabNew5_J = E.etl $ evalJulius $
(EtlR $ -- rtabNew5
(Union (TabL rtabNew4) (Previous))
:. ROpEmpty)
:-> (EtlC $
Source ["NewNumber"] $
Target ["NewNumber"] $
By (\[x] -> [x + 100]) (On $ Tab rtabNew4)
DontRemoveSrc $
FilterBy (\_ -> True))
:-> EtlMapEmpty
rtabNew6_J = E.etl $ evalJulius $
(EtlR $ -- rtabNew6
(Intersect (TabL rtabNew4) (Tab rtabNew5))
:. ROpEmpty)
:-> EtlMapEmpty
-- Build rtabNew7 with a single ETL Mapping
rtabNew7_J = E.etl $ evalJulius $
(EtlR $
(Minus (TabL rtabNew4) (Previous))
:. ROpEmpty)
:-> (EtlR $ -- rtabNew6
(Intersect (TabL rtabNew4) (Previous))
:. ROpEmpty)
:-> (EtlR $ -- rtabNew5
(Union (TabL rtabNew4) (Previous))
:. ROpEmpty)
:-> (EtlC $
Source ["NewNumber"] $
Target ["NewNumber"] $
By (\[x] -> [x + 100]) (On $ Tab rtabNew4)
DontRemoveSrc $
FilterBy (\_ -> True))
:-> EtlMapEmpty
-}
---- DEBUG
-- putStrLn "------rtabNew3-------"
-- print rtabNew3
-- putStrLn "------rtab-------"
-- print rtab
-- putStrLn "------rtabNew4-------"
-- print rtabNew4
-- putStrLn "------rtmdata5-------"
-- print rtmdata5
-- putStrLn "------rtabNew5-------"
-- print rtabNew5
----
writeResult fo "_t5.csv" rtmdata5 rtabNew5
--writeResult fo "_t5_J.csv" rtmdata5 rtabNew5_J
writeResult fo "_t6.csv" rtmdata5 rtabNew6
--writeResult fo "_t6_J.csv" rtmdata5 rtabNew6_J
writeResult fo "_t7.csv" rtmdata5 rtabNew7
writeResult fo "_t7_J.csv" rtmdata5 rtabNew7_J
writeResult fo "_t7_J2.csv" rtmdata5 rtabNew7_J2
writeResult fo "_t7_J3.csv" rtmdata5 rtabNew7_J3
-- *** Change existing column name
let -- change the value in column NewNumber
cmap3 = E.RMap1x1 {E.srcCol = "NewNumber", E.removeSrcCol = E.Yes, E.trgCol = "NewNewNumber", E.transform1x1 = \x -> x, E.srcRTupleFilter = \_ -> True}
rtabNew8 = E.runCM cmap3 rtabNew5
rtmdata8 = T.createRTableMData ( "TestTable8",
[ ("Name", T.Varchar),
("MyDate", T.Date "DD/MM/YYYY"),
("MyTime", T.Timestamp "DD/MM/YYYY HH24:MI:SS"),
("Number", T.Integer),
("DNumber", T.Double),
("NewNewNumber", T.Integer)
]
)
[] -- primary key
[] -- list of unique keys
-- Test Julius
rtabNew8_J = E.etl $ evalJulius $
EtlMapStart
:-> (EtlC $
Source ["NewNumber"] $
Target ["NewNewNumber"] $
By (\[x] -> [x]) (On $ Tab rtabNew5)
RemoveSrc $
FilterBy (\_ -> True)
)
writeResult fo "_t8.csv" rtmdata8 rtabNew8
writeResult fo "_t8_J.csv" rtmdata8 rtabNew8_J
T.printRTable rtabNew8_J
-- Test a RMapNx1 column mapping
let
cmap4 = E.RMapNx1 {E.srcColGrp = ["Name","MyTime"], E.removeSrcCol = E.Yes, E.trgCol = "New_Nx1_Col", E.transformNx1 = (\[n, T.RTime{T.rtime = t}] -> n `rdtappend` (T.RText "<---->") `rdtappend` (T.rTimestampToRText T.stdTimestampFormat t)), E.srcRTupleFilter = \_ -> True}
rtabNew9 = E.runCM cmap4 rtabNew8
rtmdata9 = T.createRTableMData ( "TestTable9",
[ -- ("Name", T.Varchar),
("New_Nx1_Col", T.Varchar),
("MyDate", T.Date "DD/MM/YYYY"),
-- ("MyTime", T.Timestamp "DD/MM/YYYY HH24:MI:SS"),
("Number", T.Integer),
("DNumber", T.Double),
("NewNewNumber", T.Integer)
]
)
[] -- primary key
[] -- list of unique keys
-- Test Julius
rtabNew9_J = E.etl $ evalJulius $
EtlMapStart
:-> (EtlC $
Source ["Name","MyTime"] $
Target ["New_Nx1_Col"] $
By (\[n, T.RTime{T.rtime = t}] -> [n `rdtappend` (T.RText "<---->") `rdtappend` (T.rTimestampToRText T.stdTimestampFormat t)]) (On $ Tab rtabNew8)
RemoveSrc $
FilterBy (\_ -> True)
)
writeResult fo "_t9.csv" rtmdata9 rtabNew9
writeResult fo "_t9_J.csv" rtmdata9 rtabNew9_J
T.printRTable rtabNew9_J
-- Test a RMap1xN column mapping
let
cmap5 = E.RMap1xN {E.srcCol = "New_Nx1_Col", E.removeSrcCol = E.No, E.trgColGrp = ["1xN_A","1xN_B","1xN_C"], E.transform1xN = (\(T.RText txt) -> [T.RText (Data.Text.take 5 txt), T.RText "<-|-|-|->", T.RText (Data.Text.takeEnd 10 txt)]), E.srcRTupleFilter = \_ -> True}
rtabNew10 = E.runCM cmap5 rtabNew9
rtmdata10 = T.createRTableMData ( "TestTable10",
[ -- ("Name", T.Varchar),
("New_Nx1_Col", T.Varchar)
,("MyDate", T.Date "DD/MM/YYYY")
-- ,("MyTime", T.Timestamp "DD/MM/YYYY HH24:MI:SS")
,("Number", T.Integer)
,("DNumber", T.Double)
,("NewNewNumber", T.Integer)
,("1xN_A", T.Varchar)
,("1xN_B", T.Varchar)
,("1xN_C", T.Varchar)
]
)
[] -- primary key
[] -- list of unique keys
-- Test Julius
rtabNew10_J = E.etl $ evalJulius $
EtlMapStart
:-> (EtlC $
Source ["New_Nx1_Col"] $
Target ["1xN_A","1xN_B","1xN_C"] $
By (\[(T.RText txt)] -> [T.RText (Data.Text.take 5 txt), T.RText "<-|-|-|->", T.RText (Data.Text.takeEnd 10 txt)]) (On $ Tab rtabNew9)
DontRemoveSrc $
FilterBy (\_ -> True)
)
writeResult fo "_t10.csv" rtmdata10 rtabNew10
writeResult fo "_t10_J.csv" rtmdata10 rtabNew10_J
T.printRTable rtabNew10_J
-- Test a RMapNxM column mapping
let
cmap6 = E.RMapNxM {E.srcColGrp = ["1xN_A","1xN_B","1xN_C"], E.removeSrcCol = E.Yes, E.trgColGrp = ["ColNew1","ColNew2"], E.transformNxM = transformation, E.srcRTupleFilter = \_ -> True}
where
transformation [T.RText t1, T.RText t2, T.RText t3] = [T.RText (t1 `append` t3), T.RText t2]
rtabNew11 = E.runCM cmap6 rtabNew10
rtmdata11 = T.createRTableMData ( "TestTable11",
[ -- ("Name", T.Varchar),
("New_Nx1_Col", T.Varchar)
,("MyDate", T.Date "DD/MM/YYYY")
-- ,("MyTime", T.Timestamp "DD/MM/YYYY HH24:MI:SS")
,("Number", T.Integer)
,("DNumber", T.Double)
,("NewNewNumber", T.Integer)
,("ColNew1", T.Varchar)
,("ColNew2", T.Varchar)
]
)
[] -- primary key
[] -- list of unique keys
-- Test Julius
rtabNew11_J = E.etl $ evalJulius $
EtlMapStart
:-> (EtlC $
Source ["1xN_A","1xN_B","1xN_C"] $
Target ["ColNew1","ColNew2"] $
By transformation (On $ Tab rtabNew10)
RemoveSrc $
FilterBy (\_ -> True)
)
where
transformation [T.RText t1, T.RText t2, T.RText t3] = [T.RText (t1 `append` t3), T.RText t2]
writeResult fo "_t11.csv" rtmdata11 rtabNew11
writeResult fo "_t11_J.csv" rtmdata11 rtabNew11_J
T.printRTable rtabNew11_J
-- Test removeColumn operation
let
rtabNew12 = T.removeColumn "NewNewNumber" (T.removeColumn "MyDate" rtabNew11)
rtmdata12 = T.createRTableMData ( "TestTable12",
[ -- ("Name", T.Varchar),
("New_Nx1_Col", T.Varchar)
-- ,("MyDate", T.Date "DD/MM/YYYY")
-- ,("MyTime", T.Timestamp "DD/MM/YYYY HH24:MI:SS")
,("Number", T.Integer)
,("DNumber", T.Double)
-- ,("NewNewNumber", T.Integer)
,("ColNew1", T.Varchar)
,("ColNew2", T.Varchar)
]
)
[] -- primary key
[] -- list of unique keys
-- Test Julius
rtabNew12_J = E.etl $ evalJulius $
EtlMapStart
:-> (EtlR $
ROpStart
:. (GenUnaryOp (On $ Tab rtabNew11) (ByUnaryOp myfunc))
)
where
myfunc = (T.removeColumn "NewNewNumber") . (T.removeColumn "MyDate")
writeResult fo "_t12.csv" rtmdata12 rtabNew12 -- this calls internally C.fromRTable and thus it cannot actually test the column removal (because the column removal is hidden
-- by the RTable metadata). An explicit print of the new RTable is required in order to check correctness.
writeResult fo "_t12_J.csv" rtmdata12 rtabNew12_J
print rtabNew12
print rtabNew12_J
T.printRTable rtabNew12_J
-- Test combined Roperations
let
myfilter = T.f (\t -> t!"Name" == T.RText {T.rtext = "Karagiannidis"})
myprojection = T.p ["Name","MyTime","Number", "ColNew2"]
myjoin = T.iJ (\t1 t2 -> t1!"Number" == t2!"Number") rtabNew12 -- !!! Check that the other table participating in the join is embedded in the myjoin operation
rcombined = myprojection . myjoin . myfilter
rtabNew13 = T.rComb rcombined rtab
rtmdata13 = T.createRTableMData ( "TestTable13",
[ ("Name", T.Varchar)
--,("MyDate", T.Date "DD/MM/YYYY")
,("MyTime", T.Timestamp "DD/MM/YYYY HH24:MI:SS")
,("Number", T.Integer)
--,("DNumber", T.Double)
,("ColNew2", T.Varchar)
]
)
[] -- primary key
[] -- list of unique keys
-- Test Julius
rtabNew13_J = E.etl $ evalJulius $
EtlMapStart
:-> (EtlR $
ROpStart
:. (Filter (From $ Tab rtab) $ FilterBy (\t -> t!"Name" == T.RText {T.rtext = "Karagiannidis"}))
:. (Join (TabL rtabNew12) (Previous) $ JoinOn (\t1 t2 -> t1!"Number" == t2!"Number"))
:. (Select ["Name","MyTime","Number", "ColNew2"] $ From Previous)
)
writeResult fo "_t13.csv" rtmdata13 rtabNew13
writeResult fo "_t13_J.csv" rtmdata13 rtabNew13_J
print rtabNew13
print rtabNew13_J
T.printRTable rtabNew13_J
-- Test Left Outer Join
let rtabNew14 = T.lJ (\t1 t2 -> t1!"Number" == t2!"Number") rtab rtabNew3
rtmdata14 = T.createRTableMData ( "TestTable14",
[ ("Name", T.Varchar),
("MyDate", T.Date "DD/MM/YYYY"),
("MyTime", T.Timestamp "DD/MM/YYYY HH24:MI:SS"),
("Number", T.Integer),
("DNumber", T.Double),
("NewNumber", T.Integer)
]
)
[] -- primary key
[] -- list of unique keys
-- debug left join
-- the first part is the join
let rtabNew14_dbg_1part = T.iJ (\t1 t2 -> t1!"Number" == t2!"Number") rtab rtabNew3 -- these are the rows of the left tab (enhanced with new columns) that satisfy the join
-- project only left tab's columns
rtabNew14_dbg_1part_proj = T.p (T.getColumnNamesfromRTab rtab) rtabNew14_dbg_1part
-- enhance the left tab with the new columns with NULL values
-- left_tab_enhanced = T.iJ (\t1 t2 -> True) rtab (T.createSingletonRTable $ T.createNullRTuple ["Name","Number","NewNumber"])
-- the second part are the rows from the preserving table that dont join
rtabNew14_dbg_2part_a =
let
leftTab = rtab --T.nvlRTable "DNumber" (T.RText "x") rtab -- need to eliminate the Null values because Null == Null gives False.
rightTab = rtabNew14_dbg_1part_proj --T.nvlRTable "DNumber" (T.RText "x") rtabNew14_dbg_1part_proj
in T.d leftTab rightTab --diffTab = T.d leftTab rightTab -- get the rows from left tab that dont join
-- in -- remove "x" and turn it back to Null
-- T.decodeRTable "DNumber" (T.RText "x") T.Null T.Null T.Ignore diffTab
-- enhance this with null columns from the non-preserving table
rtabNew14_dbg_2part = T.iJ (\t1 t2 -> True) rtabNew14_dbg_2part_a (T.createSingletonRTable $ T.createNullRTuple $ (T.getColumnNamesfromRTab rtabNew3))
-- finally, union the two parts
rtabNew14_dbg = T.u rtabNew14_dbg_1part rtabNew14_dbg_2part
-- Test Julius
rtabNew14_J = E.etl $ evalJulius $
EtlMapStart
:-> (EtlR $
ROpStart
:. (LJoin (TabL rtab) (Tab rtabNew3) $ JoinOn (\t1 t2 -> t1!"Number" == t2!"Number"))
)
writeResult fo "_t14.csv" rtmdata14 rtabNew14
writeResult fo "_t14_dbg_1part.csv" rtmdata14 rtabNew14_dbg_1part
writeResult fo "_t14_dbg_1part_proj.csv" rtmdata rtabNew14_dbg_1part_proj
writeResult fo "_t14_dbg_dbg_2part_a.csv" rtmdata rtabNew14_dbg_2part_a
--writeResult fo "_t14_left_tab_enhanced.csv" rtmdata14 left_tab_enhanced
writeResult fo "_t14_dbg_2part.csv" rtmdata14 rtabNew14_dbg_2part
writeResult fo "_t14_dbg.csv" rtmdata14 rtabNew14_dbg
writeResult fo "_t14_J.csv" rtmdata14 rtabNew14_J
putStrLn "Test LEFT JOIN"
T.printRTable rtabNew14_J
-- Test Right Outer Join
let rtabNew15 = T.rJ (\t1 t2 -> t1!"Number" == t2!"Number") rtab rtabNew3
rtmdata15 = T.createRTableMData ( "TestTable15",
[ ("Name", T.Varchar),
("MyDate", T.Date "DD/MM/YYYY"),
("MyTime", T.Timestamp "DD/MM/YYYY HH24:MI:SS"),
("Number", T.Integer),
("DNumber", T.Double),
("NewNumber", T.Integer)
]
)
[] -- primary key
[] -- list of unique keys
-- Test Julius
rtabNew15_J = E.etl $ evalJulius $
EtlMapStart
:-> (EtlR $
ROpStart
:. (RJoin (TabL rtab) (Tab rtabNew3) $ JoinOn (\t1 t2 -> t1!"Number" == t2!"Number"))
)
writeResult fo "_t15.csv" rtmdata15 rtabNew15
writeResult fo "_t15_J.csv" rtmdata15 rtabNew15_J
putStrLn "Test RIGHT JOIN"
T.printRTable rtabNew15_J
-- Test Full Outer Join
let rtabNew15fo = T.foJ (\t1 t2 -> t1!"Number" == t2!"Number") rtab rtabNew3
rtabNew15fo2 = T.foJ (\t1 t2 -> t1!"Number" == t2!"Number") rtabNew3 rtab
rtmdata15 = T.createRTableMData ( "TestTable15",
[ ("Name", T.Varchar),
("MyDate", T.Date "DD/MM/YYYY"),
("MyTime", T.Timestamp "DD/MM/YYYY HH24:MI:SS"),
("Number", T.Integer),
("DNumber", T.Double),
("NewNumber", T.Integer)
]
)
[] -- primary key
[] -- list of unique keys
-- Test Julius
rtabNew15fo_J = E.etl $ evalJulius $
EtlMapStart
:-> (EtlR $
ROpStart
:. (FOJoin (TabL rtab) (Tab rtabNew3) $ JoinOn (\t1 t2 -> t1!"Number" == t2!"Number"))
)
rtabNew15fo2_J = E.etl $ evalJulius $
EtlMapStart
:-> (EtlR $
ROpStart
:. (FOJoin (TabL rtabNew3) (Tab rtab) $ JoinOn (\t1 t2 -> t1!"Number" == t2!"Number"))
)
writeResult fo "_t15_fo.csv" rtmdata15 rtabNew15fo
writeResult fo "_t15_fo2.csv" rtmdata15 rtabNew15fo2
writeResult fo "_t15_fo_J.csv" rtmdata15 rtabNew15fo_J
writeResult fo "_t15_fo2_J.csv" rtmdata15 rtabNew15fo2_J
putStrLn "Test FULL OUTER JOIN"
T.printRTable rtabNew15fo2_J
-- Test Aggregation
--
let rtabNew16 = T.rAgg [T.raggSum "Number" "SumNumber"
, T.raggCount "Number" "CountNumber"
, T.raggAvg "Number" "AvgNumber"
, T.raggSum "Name" "SumName"
,T.raggMax "DNumber" "maxDNumber"
,T.raggMax "Number" "maxNumber"
,T.raggMax "Name" "maxName"
,T.raggMin "DNumber" "minDNumber"
,T.raggMin "Number" "minNumber"
,T.raggMin "Name" "minName"
,T.raggAvg "Name" "AvgName"
] rtabNew
rtmdata16 = T.createRTableMData ( "TestTable16",
[ --("Name", T.Varchar),
--("MyDate", T.Date "DD/MM/YYYY"),
--("MyTime", T.Timestamp "DD/MM/YYYY HH24:MI:SS"),
("SumNumber", T.Integer)
,("CountNumber", T.Integer)
,("AvgNumber", T.Double)
,("SumName", T.Integer)
,("maxDNumber", T.Double)
,("maxNumber", T.Integer)
,("maxName", T.Varchar)
,("minDNumber", T.Double)
,("minNumber", T.Integer)
,("minName", T.Varchar)
,("AvgName", T.Double)
--,("NewNumber", T.Integer)
]
)
[] -- primary key
[] -- list of unique keys
-- Test Julius
rtabNew16_J = E.etl $ evalJulius $
EtlMapStart
:-> (EtlR $
ROpStart
:. (Agg $
AggOn [ Sum "Number" $ As "SumNumber"
,Count "Number" $ As "CountNumber"
,Avg "Number" $ As "AvgNumber"
,Sum "Name" $ As "SumName"
,Max "DNumber" $ As "maxDNumber"
,Max "Number" $ As "maxNumber"
,Max "Name" $ As "maxName"
,Min "DNumber" $ As "minDNumber"
,Min "Number" $ As "minNumber"
,Min "Name" $ As "minName"
,Avg "Name" $ As "AvgName"] $ From $ Tab rtabNew)
)
writeResult fo "_t16.csv" rtmdata16 rtabNew16
writeResult fo "_t16_J.csv" rtmdata16 rtabNew16_J
T.printRTable rtabNew16_J
-- Test GroupBy
--
let rtabNew17 = T.rG (\t1 t2 -> t1!"Name" == t2!"Name" && t1!"MyTime" == t2!"MyTime")
[ T.raggSum "Number" "SumNumber"
, T.raggCount "Name" "CountName"
-- , T.raggAvg "Number" "AvgNumber"
, T.raggSum "Name" "SumName"
, T.raggCount "DNumber" "CountDNumber"
, T.raggMax "DNumber" "maxDNumber"
-- ,T.raggMax "Number" "maxNumber"
, T.raggMax "Name" "maxName"
-- ,T.raggMin "DNumber" "minDNumber"
-- ,T.raggMin "Number" "minNumber"
-- ,T.raggMin "Name" "minName"
-- ,T.raggAvg "Name" "AvgName"
]
["Name", "MyTime"]
rtabNew
-- debugging
-- rtupList = toList rtabNew
-- listOfRTupGroupLists = Data.List.groupBy (\t1 t2 -> t1!"Name" == t2!"Name") rtupList
rtmdata17 = T.createRTableMData ( "TestTable17",
[ ("Name", T.Varchar)
--,("MyDate", T.Date "DD/MM/YYYY"),
,("MyTime", T.Timestamp "DD/MM/YYYY HH24:MI:SS")
,("SumNumber", T.Integer)
,("CountName", T.Integer)
-- ,("AvgNumber", T.Double)
,("SumName", T.Integer)
,("CountDNumber", T.Integer)
,("maxDNumber", T.Double)
-- ,("maxNumber", T.Integer)
,("maxName", T.Varchar)
-- ,("minDNumber", T.Double)
-- ,("minNumber", T.Integer)
-- ,("minName", T.Varchar)
-- ,("AvgName", T.Double)
--,("NewNumber", T.Integer)
]
)
[] -- primary key
[] -- list of unique keys
-- Test Julius
rtabNew17_J = E.etl $ evalJulius $
EtlMapStart
:-> (EtlR $
ROpStart
:. (GroupBy ["Name", "MyTime"]
(AggOn [ Sum "Number" $ As "SumNumber"
,Count "Name" $ As "CountName"
--,Avg "Number" $ As "AvgNumber"
,Sum "Name" $ As "SumName"
,Count "DNumber" $ As "CountDNumber"
,Max "DNumber" $ As "maxDNumber"
--,Max "Number" $ As "maxNumber"
,Max "Name" $ As "maxName"] $ From $ Tab rtabNew)
$ GroupOn (\t1 t2 -> t1!"Name" == t2!"Name" && t1!"MyTime" == t2!"MyTime"))
)
rtabNew17_J2 = E.etl $ evalJulius $
EtlMapStart
:-> (EtlR $
ROpStart
:. (GroupBy ["Name", "MyTime"]
(AggOn [] {-[ Sum "Number" $ As "SumNumber"
,Count "Name" $ As "CountName"
--,Avg "Number" $ As "AvgNumber"
,Sum "Name" $ As "SumName"
,Count "DNumber" $ As "CountDNumber"
,Max "DNumber" $ As "maxDNumber"
--,Max "Number" $ As "maxNumber"
,Max "Name" $ As "maxName"]-} $ From $ Tab rtabNew)
$ GroupOn (\t1 t2 -> t1!"Name" == t2!"Name" && t1!"MyTime" == t2!"MyTime"))
)
-- print listOfRTupGroupLists
writeResult fo "_t17.csv" rtmdata17 rtabNew17
writeResult fo "_t17_J.csv" rtmdata17 rtabNew17_J
putStrLn "#### TEST GROUP BY ###"
T.printRTable rtabNew
T.printRTable rtabNew17_J
-- test groupNoAgg function
putStrLn "GroupBy with [] AggOp list"
T.printRTable rtabNew17_J2
putStrLn "Test groupNoAgg function"
T.printRTable $
groupNoAgg (\t1 t2 -> t1!"Name" == t2!"Name" && t1!"MyTime" == t2!"MyTime")
["Name", "MyTime"]
rtabNew
-- test groupNoAggList function
putStrLn "Test groupNoAggList function"
mapM_ (T.printRTable) $
groupNoAggList (\t1 t2 -> t1!"Name" == t2!"Name" && t1!"MyTime" == t2!"MyTime")
["Name", "MyTime"]
rtabNew
putStrLn "#### TEST CUSTOM Aggregation ###"
putStrLn "Implement a custom listagg()"
printRTable $ juliusToRTable $
EtlMapStart
:-> (EtlR $
ROpStart
:. (Select ["Name"] (From $ Tab rtabNew))
)
rtabNew18 <- runJulius $
EtlMapStart
:-> (EtlR $
ROpStart
:. (GroupBy ["Name"] -- , "MyTime"]
(AggOn [ {-Sum "Number" $ As "SumNumber"
,Count "Name" $ As "CountName"
--,Avg "Number" $ As "AvgNumber"
,Sum "Name" $ As "SumName"
,Count "DNumber" $ As "CountDNumber"
,Max "DNumber" $ As "maxDNumber"
--,Max "Number" $ As "maxNumber"
,Max "Name" $ As "maxName"
,-}
Count "Name" $ As "CountName"
,GenAgg "Name" (As "ListAggName") $ AggBy $ myListAgg (pack ";")
] $ From $ Tab rtabNew)
$ GroupOn (\t1 t2 -> t1!"Name" == t2!"Name" )) -- && t1!"MyTime" == t2!"MyTime"))
)
-- T.printRTable rtabNew18
T.printfRTable (genRTupleFormat [ "Name", "ListAggName", "CountName"] genDefaultColFormatMap) $ rtabNew18
where
myListAgg :: Text -> AggFunction
myListAgg delimiter col rtab =
rdatatypeFoldr' ( \rtup accValue ->
if isNotNull (rtup <!> col) && (isNotNull accValue)
then
rtup <!> col `rdtappend` (RText delimiter) `rdtappend` accValue
else
--if (getRTupColValue src) rtup == Null && accValue /= Null
if isNull (rtup <!> col) && (isNotNull accValue)
then
accValue -- ignore Null value
else
--if (getRTupColValue src) rtup /= Null && accValue == Null
if isNotNull (rtup <!> col) && (isNull accValue)
then
case isText (rtup <!> col) of
True -> (rtup <!> col)
False -> Null
else
Null -- agg of Nulls is Null
)
Null
rtab
--print csvNew2
--return ()
--C.writeCSVFile fo newcsv
--C.printCSVFile newcsv
writeResult ::
String -- File name
-> Text -- new file suffix
-> T.RTableMData
-> T.RTable
-> IO()
writeResult fname sfx md rt = do
let foNameNew = (fromJust (stripSuffix ".csv" (pack fname))) `mappend` sfx
csvNew = C.fromRTable md rt
C.writeCSV (unpack foNameNew) csvNew
C.printCSV csvNew