Programs that Read and Write MDELite Databases
Start with these imports, not all of which are needed for every program below. They represent the union required to run all demo programs.
import MDELite.Marquee2Arguments;
import PrologDB.Column;
import PrologDB.ColumnCorrespondence;
import PrologDB.DB;
import PrologDB.DBSchema;
import PrologDB.ErrorReport;
import PrologDB.SubTableSchema;
import PrologDB.Table;
import PrologDB.toTable;
import PrologDB.TableSchema;
import PrologDB.Tuple;
import java.util.List;
import java.util.stream.Collectors;
import java.util.stream.Stream;
Later we will use the dog-owner database, shown below:
dbase(do,[dog,owner,when]).
table(dog,[did,'name','breed', color]).
dog(d1,'kelsey','aussie',bluemerle).
dog(d2,'lassie','collie',sable).
dog(d3,'scarlett','aussie',blacktri).
dog(d4,'duke','hound dog',brown).
dog(d5,'scarlett','aussie',bluemerle).
table(owner,[oid,'name']).
owner(o1,'timmy').
owner(o2,'don').
owner(o3,'helen').
owner(o4,'jed').
table(when,[wid,did,oid,'date']).
when(w1,d1,o2,'88-95').
when(w2,d2,o1,'58-71').
when(w3,d3,o3,'07-12').
when(w4,d3,o2,'12-').
when(w5,d4,o4,'58-69').
Table of Contents
- Writing an M2M Transformation
- Printing a Database Schema
- Constructing Schemas
- Creating a Database
- Reading a Database
- Reading a Table
- Reading Tuples of a Table and its Subtables
- Filtering Tuples from Tables
- Group By Queries
- Taking Cross Products of Schemas
- Table Joins
- Self Joins
- Reading and Writing Individual Prolog and CSV Tables
- Collecting Streamed tuples into a Table
- Copying data from a Tuple of one Schema to Another
- Sorting Tables
1: Writing a M2M Transformation
All MDELite Model-to-Model (or database-to-database) transformation programs have the following prelude and structure:
public class DocExamplesTest {
public static void main(String... args) throws Exception {
// Step 1: boilerplate initialization
Marquee2Arguments mark = new Marquee2Arguments(DocExamplesTest.class, ".do.pl", ".pet.pl", args);
String inputFileName = mark.getInputFileName();
String outputFileName = mark.getOutputFileName();
String appName = mark.getAppName(outputFileName);
// Step 2: read database
DB db = DB.readDataBase(inputFileName);
Table dog = db.getTableEH("dog");
Table own = db.getTableEH("owner");
//...
// Step 3: read schema
DBSchema dbs = DBSchema.readSchema("pet.schema.pl");
DB petdb = new DB(appName, dbs);
Table pet = petdb.getTableEH("pet");
Table owner = petdb.getTableEH("owner");
//...
// Step 4:now translate databasses
//...
// Finally, write out pet database
petdb.print(outputFileName);
}
The first step executes boilerplate code to process the command line. This particular M2M program translates ".do.pl" database to ".pet.pl" databases. That is, to invoke the above program you would type:
> java DocExamplesTest X.do.pl
or
> java DocExamplesTest X.do.pl output.txt
The first call will produce pet database file X.pet.pl. That is, given X.do.pl as input, the translated database will be named X.pet.pl. The second call will ignore the standard output (X.pet.pl) and will produce its contents in file output.txt. Other points worth noting are variables:
- inputFileName -- the name of the input database (X.do.pl)
- outputFileName -- the name of the output database (X.pet.pl) or whatever is specified on the command line (e.g. output.txt)
- appName -- if "X.do.pl" is the input database, appName = "X".
The second step reads the input database (whose name is in variable inputFileName), and extracts its tables.
The third step is to read the schema for the pet database, in this case it is file "pet.schema.pl" which is local file or a file that is hardwired to a particular directory. It is also possible to hardwire the contents of a schema as amultiline String which is in the program itself. To do this, use:
String defOfSchema = "dbase(a,[b,c]).\ntable(b,[id,x,y]).\ntable(c,[z]).\nsubtable(b,[c]).";
DBSchema sch = DBSchema.readSchema("a",defOfSchema);
Also part of the third step is to create an empty pet database.
The rest of the program is code to translate a ".do.pl" database into a pet database. The end of the program writes the produced database to its output file.
The following examples illustrate how to read, write, and create MDELite databases.
2: Printing a Database Schema
This program takes an ooschema declaration and flattens it into a schema declaration. The distinction between ooschemas and schemas is that attributes of supertables are propagated to each of its subtables, recursively. The program prints a database schema one table at a time. Printing subtable declarations of the database conclude the program.
void printSchema() throws Exception {
// Step 1: read in ooschema and propagate attributes to convert it
// into a .schema file, so that it can be instantiated
DBSchema s = DBSchema.readSchema(testdir + "starTrek.ooschema.pl");
s.finishAndPropagateAttributes();
// Step 2: easy way to print a schema
s.print(System.out);
// Step 3: harder, but customized
System.out.format("\n=========\n\n");
System.out.format("database %s has \n", s.getName());
for (TableSchema t : s.getTableSchemas()) {
System.out.format(" table %10s with columns ", t.getName());
for (Column c : t.getColumns()) {
String quote = c.isQuoted() ? "\'" : "";
System.out.format("%s%s%s ", quote, c.getName(), quote);
}
System.out.format("\n");
}
System.out.format("\n");
for (SubTableSchema st : s.getSubTableSchemas()) {
TableSchema supr = st.getSuper();
System.out.format(" table %10s has subtables ", supr.getName());
for (TableSchema chld : st.getSubTableSchemas()) {
System.out.format("%s ", chld.getName());
}
System.out.format("\n");
}
}
Here is the output of this program:
dbase(starTrek,[crewman,commander,lieutenant]).
table(crewman,[cid,fname,lname]).
table(commander,[cid,fname,lname,rank]).
table(lieutenant,[cid,fname,lname,specialty]).
subtable(crewman,[commander,lieutenant]).
=========
database starTrek has
table crewman with columns cid fname lname
table commander with columns cid fname lname rank
table lieutenant with columns cid fname lname specialty
table crewman has subtables commander lieutenant
3: Constructing Schemas
This program creates a starTrek ooschema programmatically and prints it out using standard utilities. Different ways of creating a table schema are shown.
DBSchema schemaBuild() throws Exception {
// Step 1: when a schema is initially created, and until it is
// "finished", it is an .ooschema
DBSchema trekSchema = new DBSchema("starTrek");
// Step 2: add tables
TableSchema crewman = new TableSchema("crewman");
crewman.addColumns("cid", "fname", "lname");
TableSchema commander = new TableSchema("commander").addColumn("rank");
TableSchema lieutenant = new TableSchema("lieutenant").addColumn("specialty");
trekSchema.addTableSchemas(crewman, commander, lieutenant);
// Step 3: create the lone subtable declaration, and add to schema
SubTableSchema sts = new SubTableSchema(crewman);
sts.addSubTableSchemas(commander, lieutenant);
trekSchema.addSubTableSchema(sts);
// Step 4: seal or "finish" the schema so that no further editing of it
// is possible; propagate supertable attributes to subtables.
trekSchema.finishAndPropagateAttributes();
// return the DBSchema "starTrek.schema.pl"
return trekSchema;
}
4: Creating a Database
This program creates the enterprise database which has 3 tuples, one tuple per table. An ooschema for the database is created and then converted into a true schema (shown above). At this point, a database instance of the schema can be created, one table and one tuple at a time. Different ways of creating a tuple are shown.
void DBBuild() throws Exception {
// Step 1: build the starTrek schema and then instantiate it with
// an empty database
DBSchema trekSchema = schemaBuild(); // see schemaBuild() defn above
DB enterprise = new DB("enterprise", trekSchema);
// Step 2: create the spock tuple
Table crewman = enterprise.getTableEH("crewman");
Tuple spock = new Tuple(crewman);
spock.setValues("c1", "mr", "spock");
crewman.add(spock);
// Step 3: create the sulu tuple
Table lieutenant = enterprise.getTableEH("lieutenant");
Tuple sulu = new Tuple(lieutenant).setValues("c3", "hikaru", "sulo", "navigation");
lieutenant.add(sulu);
// Step 4: create the kirk tuple
Table commander = enterprise.getTableEH("commander");
commander.add(new Tuple(commander).setValues("c2", "james", "kirk", "captain"));
// Step 5: now print database
enterprise.print(System.out);
}
Here is the output of this program:
dbase(starTrek,[crewman,commander,lieutenant]).
table(crewman,[cid,fname,lname]).
crewman(c1,mr,spock).
table(commander,[cid,fname,lname,rank]).
commander(c2,james,kirk,captain).
table(lieutenant,[cid,fname,lname,specialty]).
lieutenant(c3,hikaru,sulo,navigation).
subtable(crewman,[commander,lieutenant]).
5: Reading a Database
This program reads a database given its file, prints the database using available utilities and also a harder way by extracting out its individual tables and printing its tuples.
void DBread() throws Exception {
// Step 1: read a database given its file
DB db = DB.readDataBase(testdata + "/enterprise.starTrek.pl");
// Step 2: easy way to print a database
db.print(System.out);
// Step 3: a more customized way
System.out.format("\n\n ===== \n\n");
System.out.format("database %s contains:\n", db.getName());
for (Table t : db.getTables()) {
System.out.format("Table %s\n", t.getName());
t.stream().forEach(tup -> tup.print(System.out));
System.out.format("\n");
}
}
Here is the output of this program:
dbase(starTrek,[crewman,commander,lieutenant]).
table(crewman,[cid,fname,lname]).
crewman(c1,mr,spock).
table(commander,[cid,fname,lname,rank]).
commander(c2,james,kirk,captain).
table(lieutenant,[cid,fname,lname,specialty]).
lieutenant(c3,hikaru,sulu,navigation).
subtable(crewman,[commander,lieutenant]).
=====
database enterprise contains:
Table crewman
crewman(c1,mr,spock).
commander(c2,james,kirk,captain).
lieutenant(c3,hikaru,sulu,navigation).
Table commander
commander(c2,james,kirk,captain).
Table lieutenant
lieutenant(c3,hikaru,sulu,navigation).
6: Reading a Table
This program reads a dog-owner database, which has a table of dogs. Using Java Streams, the stream of all aussie (Australian Shepherd) tuples is created and then printed. A similar stream is reconstituted (remember: once you use a stream, it's done and has to be recreated) and then filtered further, and printed.
void TableRetrieve() {
// Step 1: read the database containing desired table, and get the table
DB db = DB.readDataBase(testdata + "/dogOwner.do.pl");
Table dog = db.getTable("dog");
// Step 2: retrieve all aussies -- version 1 tuple streams
dog.stream().filter(t -> t.is("breed", "aussie"))
.forEach(t -> t.print(System.out));
System.out.println();
// Step 3: retrieve all aussies -- version 2 tables
dog.filter(t -> t.is("breed", "aussie")).print(System.out);
// Step 4: retrieve all aussies that are blacktri's -- version 1
Stream aussies = dog.stream().filter(t -> t.is("breed", "aussie"));
Stream cuteAussies = aussies.filter(t -> t.is("color", "blacktri"));
cuteAussies.forEach(t -> t.print(System.out));
System.out.println();
// Step 5: another version, this time using Tables -- version 2
dog.filter(t -> t.is("breed", "aussie") && t.is("color", "blacktri"))
.print(System.out);
}
7: Reading Tuples of a Table and its Subtables
This program reads the tuples of the table pet and all of its subtables. If you want to retrieve tuples of only that table (and not its subtables), use the getLocalTuples() method. A couple queries are illustrated.
void InheritanceRetrieve() {
// Step 1 : read the database and get the pet table
DB db = DB.readDataBase(testdata + "/pets.petdb.pl");
Table pet = db.getTableEH("pet");
// Step 1: easy way: print all pets in pet table and subtables
pet.print(System.out);
// Step 2: a more verbose way:
pet.getSchema().print(System.out);
pet.tuples().forEach(t -> t.print(System.out));
// Step 3: all pets whose name begins with "l" -- streaming version
System.out.println("\nlist of all pets whose name starts with 'l'");
pet.filter(t -> t.get("name").startsWith("l")).print(System.out);
// Step 4: all pets whose name begins with "l" -- table version
System.out.println("\nversion 2");
Table shorterPet = pet.filter(t -> t.get("name").startsWith("l"));
shorterPet.print(System.out);
}
8: Filtering Tuples from Tables
This program illustrates several ways in which you can filter tuples from tables. The first uses a loop with an if-statement. The second creates a stream of tuples that are filtered and printed. The third creates a table of only filtered tuples that is then printed. The only difference between the latter two is that stream implementations process one tuple at a time as opposed to one table at a time.
public void filter() {
DB db = DB.readDataBase(testdata + "/enterprise.starTrek.pl");
Table crew = db.getTableEH("crewman");
// Step 1: easy way using tables
Predicate p = t -> t.get("fname").startsWith("h");
crew.filter(p).print(System.out);
// Step 2: more manual via loops
crew.getSchema().print(System.out);
for (Tuple t : crew.tuples()) {
if (t.get("fname").startsWith("h")) {
t.print(System.out);
}
}
System.out.println();
// Step 3: another way using streams
crew.getSchema().print(System.out);
crew.stream()
.filter(t -> t.get("fname").startsWith("h"))
.forEach(t -> t.print(System.out));
}
Here is the output of this program:
table(crewman,[cid,fname,lname]).
lieutenant(c3,hikaru,sulu,navigation).
table(crewman,[cid,fname,lname]).
lieutenant(c3,hikaru,sulu,navigation).
table(crewman,[cid,fname,lname]).
lieutenant(c3,hikaru,sulu,navigation).
9: Group By Queries
This program illustrates group-by facilities. A table is "grouped by" a particular column, producing a stream of subtables (of the same type as the original table) containing all tuples that have the same column value.
void GroupBy() {
DB db = DB.readDataBase(testdata + "dogOwner.do.pl");
Table dog = db.getTableEH("dog");
// Step 1: one way -- print directly to standard out
dog.groupBy("breed").forEach(tb -> System.out.format("%d %s\n", tb.count(), tb.getFirst(p -> true).get("breed")));
// Step 2: collect into a separate table and print that table
Table result = new Table(new TableSchema("result").addColumns("count", "breed"));
dog.groupBy("breed")
.forEach(tb -> result.addTuple(tb.count() + "", tb.getFirst(p -> true).get("breed")));
result.print(System.out);
}
10: Taking Cross Products of SchemasCopying data from a Tuple of one Schema to Another
Table joins take cross products of schemas automatically. Occasionally you might need to do this yourself. The following program illustrates how cross products of schemas are taken and printed.
public void crossSchema() {
// Step 1: although this is a database file, you can still use it
// to read its schema. Then read and print the dog and
// owner table schemas
DBSchema dbs = DBSchema.readSchema(testdata + "/dogOwner.do.pl");
TableSchema dog = dbs.getTableSchema("dog");
TableSchema owner = dbs.getTableSchema("owner");
dog.print(System.out);
owner.print(System.out);
// Step 2: here is where the cross-product of two schemas is taken
TableSchema dXoSchema = dog.crossProduct(owner);
dXoSchema.print(System.out);
// Step 3: take cross product of all 3 schemas, 2 at a time
TableSchema when = dbs.getTableSchema("when");
when.print(System.out);
TableSchema dogXwhen = dog.crossProduct(when);
dogXwhen.print(System.out);
TableSchema dogXwhenXowner = dogXwhen.crossProduct(owner);
dogXwhenXowner.print(System.out);
}
Here is the output of this program:
BEGIN cross schema tests
table(dog,[did,"name","breed",color]).
table(owner,[oid,"name"]).
table(dog_x_owner,[dog.did,"dog.name","dog.breed",dog.color,owner.oid,"owner.name"]).
table(when,[wid,did,oid,"date"]).
table(dog_x_when,[dog.did,"dog.name","dog.breed",dog.color,when.wid,when.did,when.oid,"when.date"]).
table(dog_x_when_x_owner,[dog.did,"dog.name","dog.breed",dog.color,when.wid,when.did,when.oid,"when.date",owner.oid,"owner.name"]).
END cross schema tests
11: Table Joins
This program reads a dog-owner database, finds the dog, when, and owner tables, joins the dog table with the when table over dog.did = when.did join predicate (i.e., the did columns of both tuples must have the same value) to produce a new table dogXwhen.
Note: the attributes of the joined relation are renamed. What used to be attribute T in the dog table is now renamed dog.T. Same for all other attributes, so that one can always distinguish attributes with similar names. Continuing, this table is printed, and then it is joined with the owner table over predicate when.oid = owner.oid, and then the table is printed out.
void TableJoins() {
// Step 1: read the dog-owner database and get its tables to join
DB db = DB.readDataBase(testdata + "/dogOwner.do.pl");
Table dog = db.getTable("dog");
Table when = db.getTable("when");
Table owner = db.getTable("owner");
// Step 2: join dog with when (over fields named "did"). Note: the
// fields of dogXwhen table are renamed to when.did, when.oid,
// when.date, dog.did, dog.name, dog.breed, and dog.color
Table dogXwhen = dog.join("did", when, "did");
dogXwhen.print(System.out);
// Step 3: join dogXwhen with the ownere table
Table dogXwhenXowner = dogXwhen.join("when.oid", owner, "oid");
dogXwhenXowner.print(System.out);
}
Here is the output of this program:
table(dog_x_when,[dog.did,"dog.name","dog.breed",dog.color,when.wid,when.did,when.oid,"when.date"]).
dog_x_when(d1,'kelsey','aussie',bluemerle,w1,d1,o2,'88-95').
dog_x_when(d2,'lassie','collie',sable,w2,d2,o1,'58-71').
dog_x_when(d3,'scarlett','aussie',blacktri,w3,d3,o3,'07-12').
dog_x_when(d3,'scarlett','aussie',blacktri,w4,d3,o2,'12-').
dog_x_when(d4,'duke','hound dog',brown,w5,d4,o4,'58-69').
table(dog_x_when_x_owner,[dog.did,"dog.name","dog.breed",dog.color,when.wid,when.did,when.oid,"when.date",owner.oid,"owner.name"]).
dog_x_when_x_owner(d1,'kelsey','aussie',bluemerle,w1,d1,o2,'88-95',o2,'don').
dog_x_when_x_owner(d2,'lassie','collie',sable,w2,d2,o1,'58-71',o1,'timmy').
dog_x_when_x_owner(d3,'scarlett','aussie',blacktri,w3,d3,o3,'07-12',o3,'helen').
dog_x_when_x_owner(d3,'scarlett','aussie',blacktri,w4,d3,o2,'12-',o2,'don').
dog_x_when_x_owner(d4,'duke','hound dog',brown,w5,d4,o4,'58-69',o4,'jed').
12: Self Joins
Here is a program that joins the dog table with itself over dog identifiers (did). The key to self-joins is that a copy of a table for each self-join must be made. If a dog table is to be joined with itself, the dog table is really joined with a copy. If a dog table is to be joined with itself, followed by itself, the dog table and two distinct copies of the dog table must be joined. Now, this particular example, of joining a dog table with itself isn't particularly useful, but when you have a table that encodes (parent, child) relationships, joining such tables multiple times allows you to relate parents to grandchildren, and so on.
void selfJoin() {
// Step 1: as usual, read a database and get the dog table and
// copy it.
DB db = DB.readDataBase(testdata + "/dogOwner.do.pl");
Table dog = db.getTable("dog");
Table dog2 = dog.copyForSelfJoins("dog2");
// Step 2: join tables. The resulting table has 8 fields:
// dog.did, dog.name, dog.breed, dog.color and
// dog2.did, dog2.name, dog2.breed, and dog2.color
Table dogXdog2 = dog.join("did", dog2, "did");
dogXdog2.print(System.out);
}
13: Reading and Writing Individual Prolog and CSV Tables
You can read and write individual tables, either in the standard Prolog format or as a CSV (comma separated value) file. The name of the file (whether it ends or not in ".csv") determines whether the file is to be read/written as a CSV file.
public void csvfile() {
Table csv = Table.readTable("test/DML/PrologDB/TestData/simple.csv");
csv.writeTable("simple.pl");
csv.writeTable("simple.csv");
}
Here is the simple.csv file -- all CSV tables are assumed to have quoted attributes (and hence quoted values):
Id,ab,cd
i1,ab,cd
i2,ef,gh
i3,ij,kl
And here it's Prolog formatted output:
table(simple,["Id","ab","cd"]).
simple('i1','ab','cd').
simple('i2','ef','gh').
simple('i3','ij','kl').
And what it looks like written out:
Id,ab,cd
i1,ab,cd
i2,ef,gh
i3,ij,kl
14: Collecting Streamed Tuples into a Table
This program shows how a stream of tuples can be collected into table, and then the collected table is compared with the original (and found to be the same).
void toTable() {
DB db = DB.readDataBase(testdata + "dogOwner.do.pl");
Table dog = db.getTableEH("dog");
// Step 1: stream dog, and collect into a new dog table
Table dog2 = dog.stream().collect(new toTable(dog));
System.out.format("dog2 %s dog", dog2.equals(dog) ? "=" : "!=");
}
15: Copying Data from a Tuple of one Schema to Another
A common task is copying data from one tuple of one schema to a tuple of another. It's not hard, but tedious. Look at the first step of the code below -- it is a series of assignment statements. The second step defines a ColumnCorrespondence among the fields of the left schema with fields or computations in the right. Not all fields of the left schema need be considered/updated in a correspondence. Also, when a correspondence is defined, it can be subsequently changed by adding and deleting column correspondences.
boolean debug = true;
// Step 1: manual way
for (Tuple b : buckeyes.tuples()) {
Tuple l = new Tuple(longhorns);
l.set("lid", b.get("id").replace("b", "l"));
l.set("name", b.get("fname") + " " + b.get("lname"));
l.set("pos", b.get("position"));
if (debug) {
l.isComplete();
}
longhorns.add(l);
}
longhorns.print();
// Step 2: using correspondences
longhorns.deleteAll();
ColumnCorrespondence c = new ColumnCorrespondence()
.add("lid", b -> b.get("id").replace("b", "l"))
.add("name", b -> b.get("fname") + " " + b.get("lname"))
.add("pos", "position");
longhorns.addTuples(buckeyes, c);
longhorns.print();
As an aside, the manual way has a debug option that tests if a tuple is complete -- this means that a tuple has its full complement of column-value pairs. In this example all longhorn tuples do. If a tuple is incomplete, an RuntimeException is thrown if not all attributes of a tuple have values. This is a way that you can debug a program -- and once you're convinced you have all columns, you can remove the isComplete test.
16: Sorting Tables
Sorting is obvious. Let T be a table with column "key". What does T.sort("key") produce? Clearly a sorted table. But do we want a sorted copy of T to be output, or do we want T itself to be sorted? That's the purpose of sort(table,boolean). If the boolean is true, the input table is updated by being sorted. If false, a sorted copy of T is produced.
// sort the longhorn table (in place)
longhorns.sort("lid", true);