AlgebraToSpreadsheet

This work was sponsored by the Polish National Science Centre

Installation instructions

The latest version of the AlgebraToSpreadsheet can be downloaded from here. After unzipping (assuming one can run Java programs) it can be executed with the command: java" -cp ./lib -jar AlgebraToSpreadsheet.jar input.xml outout.xlsx where the input.xml points to a file with an XML description of the relational algebra term/expression and the output.xlsx is the file name under which to save the resulting spreadsheet.
Documentation/Examples

We have prepared several examples for each algebra operator which at the same time constitute the user documentation. Below we list the Java code used to generate the examples. It shows how:
the Java API is used,
to create algebra expressions/terms,
to store algebra into XML and read it from XML and
produce spreadsheets with formulae implementing the algebra.
The links to the XML with algebra and produced spreadsheets are clickable and lead to the real documents. At the end we have also published extended examples that show how the operators can be used in combination to execute complex queries.
Term t;
Result r;
AlgebraToSpreadsheet ats = new AlgebraToSpreadsheet();

//GroupBy
Utils.reset();
t = groupBy(rel(1), 1);
System.out.println(t);
r = t.execute();
System.out.println(r.getResultRelation());
System.out.println(r.getFormulas());
ats.storeAlgebra("examples/test_groupBy1.xml", t);
ats.writeExcel("examples/excel_groupBy1.xlsx", r);
t = ats.readAlgebra("examples/test_groupBy1.xml");
System.out.println(t);
System.out.println("----------------------------------------------------");

Utils.reset();
t = groupBy(rel(2), 1, 2).countDistinct(1);
System.out.println(t);
r = t.execute();
System.out.println(r.getResultRelation());
System.out.println(r.getFormulas());
ats.storeAlgebra("examples/test_groupBy2.xml", t);
ats.writeExcel("examples/excel_groupBy2.xlsx", r);
t = ats.readAlgebra("examples/test_groupBy2.xml");
System.out.println(t);
System.out.println("----------------------------------------------------");

Utils.reset();
t = groupBy(rel(10), 1, 2);
System.out.println(t);
r = t.execute();
System.out.println(r.getResultRelation());
System.out.println(r.getFormulas());
ats.storeAlgebra("examples/test_groupBy3.xml", t);
ats.writeExcel("examples/excel_groupBy3.xlsx", r);
t = ats.readAlgebra("examples/test_groupBy3.xml");
System.out.println(t);
System.out.println("----------------------------------------------------");

Utils.reset();
t = groupBy(rel(10), 1, 2).min(3).max(4).sum(5).count(6, 7).avg(8).countDistinct(9, 10);
System.out.println(t);
r = t.execute();
System.out.println(r.getResultRelation());
System.out.println(r.getFormulas());
ats.storeAlgebra("examples/test_groupBy4.xml", t);
ats.writeExcel("examples/excel_groupBy4.xlsx", r);
t = ats.readAlgebra("examples/test_groupBy4.xml");
System.out.println(t);
System.out.println("----------------------------------------------------");

//CartProd
Utils.reset();
t = cartProd(rel(1), rel(1));
System.out.println(t);
r = t.execute();
System.out.println(r.getResultRelation());
System.out.println(r.getFormulas());
ats.storeAlgebra("examples/test_cartProd1.xml", t);
ats.writeExcel("examples/excel_cartProd1.xlsx", r);
t = ats.readAlgebra("examples/test_cartProd1.xml");
System.out.println(t);
System.out.println("----------------------------------------------------");

Utils.reset();
t = cartProd(rel(2), rel(2));
System.out.println(t);
r = t.execute();
System.out.println(r.getResultRelation());
System.out.println(r.getFormulas());
ats.storeAlgebra("examples/test_cartProd2.xml", t);
ats.writeExcel("examples/excel_cartProd2.xlsx", r);
t = ats.readAlgebra("examples/test_cartProd2.xml");
System.out.println(t);
System.out.println("----------------------------------------------------");

Utils.reset();
t = cartProd(rel(3), rel(3));
System.out.println(t);
r = t.execute();
System.out.println(r.getResultRelation());
System.out.println(r.getFormulas());
ats.storeAlgebra("examples/test_cartProd1.xml", t);
ats.writeExcel("examples/excel_cartProd1.xlsx", r);
t = ats.readAlgebra("examples/test_cartProd1.xml");
System.out.println(t);
System.out.println("----------------------------------------------------");

//Difference
Utils.reset();
t = diffBag(rel(1), rel(1));
System.out.println(t);
r = t.execute();
System.out.println(r.getResultRelation());
System.out.println(r.getFormulas());
ats.storeAlgebra("examples/test_difference_bag1.xml", t);
ats.writeExcel("examples/excel_difference_bag1.xlsx", r);
t = ats.readAlgebra("examples/test_difference_bag1.xml");
System.out.println(t);
System.out.println("----------------------------------------------------");

Utils.reset();
t = diffBag(rel(2), rel(2));
System.out.println(t);
r = t.execute();
System.out.println(r.getResultRelation());
System.out.println(r.getFormulas());
ats.storeAlgebra("examples/test_difference_bag2.xml", t);
ats.writeExcel("examples/excel_difference_bag2.xlsx", r);
t = ats.readAlgebra("examples/test_difference_bag2.xml");
System.out.println(t);
System.out.println("----------------------------------------------------");

Utils.reset();
t = diffSet(rel(1), rel(1));
System.out.println(t);
r = t.execute();
System.out.println(r.getResultRelation());
System.out.println(r.getFormulas());
ats.storeAlgebra("examples/test_difference_set1.xml", t);
ats.writeExcel("examples/excel_difference_set1.xlsx", r);
t = ats.readAlgebra("examples/test_difference_set1.xml");
System.out.println(t);
System.out.println("----------------------------------------------------");

Utils.reset();
t = diffSet(rel(2), rel(2));
System.out.println(t);
r = t.execute();
System.out.println(r.getResultRelation());
System.out.println(r.getFormulas());
ats.storeAlgebra("examples/test_difference_set2.xml", t);
ats.writeExcel("examples/excel_difference_set2.xlsx", r);
t = ats.readAlgebra("examples/test_difference_set2.xml");
System.out.println(t);
System.out.println("----------------------------------------------------");

//Duplicate removal
Utils.reset();
t = dupRem(rel(1));
System.out.println(t);
r = t.execute();
System.out.println(r.getResultRelation());
System.out.println(r.getFormulas());
ats.storeAlgebra("examples/test_duprem1.xml", t);
ats.writeExcel("examples/excel_duprem1.xlsx", r);
t = ats.readAlgebra("examples/test_duprem1.xml");
System.out.println(t);
System.out.println("----------------------------------------------------");

Utils.reset();
t = dupRem(rel(3));
System.out.println(t);
r = t.execute();
System.out.println(r.getResultRelation());
System.out.println(r.getFormulas());
ats.storeAlgebra("examples/test_duprem2.xml", t);
ats.writeExcel("examples/excel_duprem2.xlsx", r);
t = ats.readAlgebra("examples/test_duprem2.xml");
System.out.println(t);
System.out.println("----------------------------------------------------");


//Equality Join
Utils.reset();
t = eqJoin(rel(1), rel(1), 1, 1);
System.out.println(t);
r = t.execute();
System.out.println(r.getResultRelation());
System.out.println(r.getFormulas());
ats.storeAlgebra("examples/test_eqjoin1.xml", t);
ats.writeExcel("examples/excel_eqjoin1.xlsx", r);
t = ats.readAlgebra("examples/test_eqjoin1.xml");
System.out.println(t);
System.out.println("----------------------------------------------------");

Utils.reset();
t = eqJoin(rel(2), rel(2), 1, 1);
System.out.println(t);
r = t.execute();
System.out.println(r.getResultRelation());
System.out.println(r.getFormulas());
ats.storeAlgebra("examples/test_eqjoin2.xml", t);
ats.writeExcel("examples/excel_eqjoin2.xlsx", r);
t = ats.readAlgebra("examples/test_eqjoin2.xml");
System.out.println(t);
System.out.println("----------------------------------------------------");

Utils.reset();
t = eqJoin(rel(4), rel(4), 2, 3);
System.out.println(t);
r = t.execute();
System.out.println(r.getResultRelation());
System.out.println(r.getFormulas());
ats.storeAlgebra("examples/test_eqjoin3.xml", t);
ats.writeExcel("examples/excel_eqjoin3.xlsx", r);
t = ats.readAlgebra("examples/test_eqjoin3.xml");
System.out.println(t);
System.out.println("----------------------------------------------------");

//Filter
Utils.reset();
t = filter(rel(1), "TRUE()");
System.out.println(t);
r = t.execute();
System.out.println(r.getResultRelation());
System.out.println(r.getFormulas());
ats.storeAlgebra("examples/test_filter1.xml", t);
ats.writeExcel("examples/excel_filter1.xlsx", r);
t = ats.readAlgebra("examples/test_filter1.xml");
System.out.println(t);
System.out.println("----------------------------------------------------");

Utils.reset();
t = filter(rel(1), "1=1");
System.out.println(t);
r = t.execute();
System.out.println(r.getResultRelation());
System.out.println(r.getFormulas());
ats.storeAlgebra("examples/test_filter2.xml", t);
ats.writeExcel("examples/excel_filter2.xlsx", r);
t = ats.readAlgebra("examples/test_filter2.xml");
System.out.println(t);
System.out.println("----------------------------------------------------");

Utils.reset();
t = filter(rel(1), "#1=\"X\"");
System.out.println(t);
r = t.execute();
System.out.println(r.getResultRelation());
System.out.println(r.getFormulas());
ats.storeAlgebra("examples/test_filter3.xml", t);
ats.writeExcel("examples/excel_filter3.xlsx", r);
t = ats.readAlgebra("examples/test_filter3.xml");
System.out.println(t);
System.out.println("----------------------------------------------------");

Utils.reset();
t = filter(rel(3), "#2=2");
System.out.println(t);
r = t.execute();
System.out.println(r.getResultRelation());
System.out.println(r.getFormulas());
ats.storeAlgebra("examples/test_filter4.xml", t);
ats.writeExcel("examples/excel_filter4.xlsx", r);
t = ats.readAlgebra("examples/test_filter4.xml");
System.out.println(t);
System.out.println("----------------------------------------------------");

//General Join
Utils.reset();
t = genJoin(rel(1), rel(1), "TRUE()");
System.out.println(t);
r = t.execute();
System.out.println(r.getResultRelation());
System.out.println(r.getFormulas());
ats.storeAlgebra("examples/test_genJoin1.xml", t);
ats.writeExcel("examples/excel_genJoin1.xlsx", r);
t = ats.readAlgebra("examples/test_genJoin1.xml");
System.out.println(t);
System.out.println("----------------------------------------------------");

Utils.reset();
t = genJoin(rel(2), rel(2), "#2<#4");
System.out.println(t);
r = t.execute();
System.out.println(r.getResultRelation());
System.out.println(r.getFormulas());
ats.storeAlgebra("examples/test_genJoin2.xml", t);
ats.writeExcel("examples/excel_genJoin2.xlsx", r);
t = ats.readAlgebra("examples/test_genJoin2.xml");
System.out.println(t);
System.out.println("----------------------------------------------------");

//Projection
Utils.reset();
t = project(rel(1), 1);
System.out.println(t);
r = t.execute();
System.out.println(r.getResultRelation());
System.out.println(r.getFormulas());
ats.storeAlgebra("examples/test_projection1.xml", t);
ats.writeExcel("examples/excel_projection1.xlsx", r);
t = ats.readAlgebra("examples/test_projection1.xml");
System.out.println(t);
System.out.println("----------------------------------------------------");

Utils.reset();
t = project(rel(5), 2, 3, 5);
System.out.println(t);
r = t.execute();
System.out.println(r.getResultRelation());
System.out.println(r.getFormulas());
ats.storeAlgebra("examples/test_projection2.xml", t);
ats.writeExcel("examples/excel_projection2.xlsx", r);
t = ats.readAlgebra("examples/test_projection2.xml");
System.out.println(t);
System.out.println("----------------------------------------------------");

//SemiJoin
Utils.reset();
t = semiJoin(rel(1), rel(1), 1, 1);
System.out.println(t);
r = t.execute();
System.out.println(r.getResultRelation());
System.out.println(r.getFormulas());
ats.storeAlgebra("examples/test_semijoin1.xml", t);
ats.writeExcel("examples/excel_semijoin1.xlsx", r);
t = ats.readAlgebra("examples/test_semijoin1.xml");
System.out.println(t);
System.out.println("----------------------------------------------------");

Utils.reset();
t = semiJoin(rel(2), rel(2), 1, 2);
System.out.println(t);
r = t.execute();
System.out.println(r.getResultRelation());
System.out.println(r.getFormulas());
ats.storeAlgebra("examples/test_semijoin2.xml", t);
ats.writeExcel("examples/excel_semijoin2.xlsx", r);
t = ats.readAlgebra("examples/test_semijoin2.xml");
System.out.println(t);
System.out.println("----------------------------------------------------");

//Sort
Utils.reset();
t = sortAsc(rel(3), 1);
System.out.println(t);
r = t.execute();
System.out.println(r.getResultRelation());
System.out.println(r.getFormulas());
ats.storeAlgebra("examples/test_sortAsc1.xml", t);
ats.writeExcel("examples/excel_sortAsc1.xlsx", r);
t = ats.readAlgebra("examples/test_sortAsc1.xml");
System.out.println(t);
System.out.println("----------------------------------------------------");

Utils.reset();
t = sortDesc(rel(3), 1);
System.out.println(t);
r = t.execute();
System.out.println(r.getResultRelation());
System.out.println(r.getFormulas());
ats.storeAlgebra("examples/test_sortDesc1.xml", t);
ats.writeExcel("examples/excel_sortDesc1.xlsx", r);
t = ats.readAlgebra("examples/test_sortDesc1.xml");
System.out.println(t);
System.out.println("----------------------------------------------------");

//Standarization
Utils.reset();
t = stand(rel(3));
System.out.println(t);
r = t.execute();
System.out.println(r.getResultRelation());
System.out.println(r.getFormulas());
ats.storeAlgebra("examples/test_standarization1.xml", t);
ats.writeExcel("examples/excel_standarization1.xlsx", r);
t = ats.readAlgebra("examples/test_standarization1.xml");
System.out.println(t);
System.out.println("----------------------------------------------------");

//Union
Utils.reset();
t = union(rel(2), rel(2));
System.out.println(t);
r = t.execute();
System.out.println(r.getResultRelation());
System.out.println(r.getFormulas());
ats.storeAlgebra("examples/test_union1.xml", t);
ats.writeExcel("examples/excel_union1.xlsx", r);
t = ats.readAlgebra("examples/test_union1.xml");
System.out.println(t);
System.out.println("----------------------------------------------------");

//Reference
Utils.reset();
t = union(rel("tab", 1), ref("tab"));
System.out.println(t);
r = t.execute();
System.out.println(r.getResultRelation());
System.out.println(r.getFormulas());
ats.storeAlgebra("examples/test_reference1.xml", t);
ats.writeExcel("examples/excel_reference1.xlsx", r);
t = ats.readAlgebra("examples/test_reference1.xml");
System.out.println(t);
System.out.println("----------------------------------------------------");

//Examples
//select x.a, y.b from tab x, tab y where x.b=y.a minus select a, b from tab;
//tab(a,b,dl)
Utils.reset();
t = diffSet(project(eqJoin(rel("tab", 3), ref("tab"), 2, 1), 2, 4), project(ref("tab"), 1, 2));
System.out.println(t);
r = t.execute();
System.out.println(r.getResultRelation());
System.out.println(r.getFormulas());
ats.storeAlgebra("examples/test_example1.xml", t);
ats.writeExcel("examples/excel_example1.xlsx", r);
t = ats.readAlgebra("examples/test_example1.xml");
System.out.println(t);
System.out.println("----------------------------------------------------");

//select x.a, y.b from tab x, tab y where x.b=y.a and x.dl+y.dl > (select min(dl) from tab z where z.a = x.a and z.b = y.b)
//tab(a,b,dl)
//after eqjoin: x.b, x.a, x.dl, y.c, y.dl
//after genjoin: x.b, x.a, x.dl, y.b, y.dl, z.a, z.b, z.dl {x.a=z.a i y.b=z.b}
//select: x.a, y.b, x.dl+y.dl, z.dl
//groupby: x.a, y.b, z.dl, min(x.dl+y.dl)
//filter: x.a, y.b, z.dl, min(x.dl+y.dl) {z.dl > min(x.dl+y.dl)}
Utils.reset();
t = filter(groupBy(select(genJoin(eqJoin(rel("tab", 3), ref("tab"), 2, 1), ref("tab"), "And(#2=#6,#4=#7)"), "#2", "#4", "#3+#5", "#8"), 1, 2, 4).min(3), "#3>#4");
System.out.println(t);
r = t.execute();
System.out.println(r.getResultRelation());
System.out.println(r.getFormulas());
ats.storeAlgebra("examples/test_example2.xml", t);
ats.writeExcel("examples/excel_example2.xlsx", r);
t = ats.readAlgebra("examples/test_example2.xml");
System.out.println(t);
System.out.println("----------------------------------------------------");
Console output

The code generating the examples produces the following console output.