Demo
Programs on Reading and Writing MDELite6 Databases
Start with these imports, not all of which are needed for each program
given below. The imports are the union required to run all
demo programs.
import java.io.File;
import java.util.List;
import java.util.stream.Collectors;
import java.util.stream.Stream;
import PrologDB.*;
Program
1: Printing the Contents of a Database Schema
This
program takes an ooschema declaration and flattens it into a schema
declaration. The distinction between ooschema files and
schema fiiles, as explained in class, is that attributes of supertables
are propagated to each of its subtables, recursively. Next,
the code prints out the contents of the database schema, getting one
table at a time, and printing the schema of the table.
Printing subtable declarations of the database conclude the
program.
void printSchema() throws Exception {
System.out.format("START print Schema\n\n");
File sFile = new File("TestData/Schema/starTrek.ooschema.pl");
DBSchema s = DBSchema.readSchema(sFile, System.err);
s.flatten();
// easy way
s.print(System.out);
// more detailed way
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");
}
System.out.format("\n\nEND print Schema\n");
}
Program
2 : Constructing Schemas Programmatically
This
program creates a starTrek ooschema programmatically and prints it out
using standard utilities.
DBSchema schemaBuild() throws Exception {
DBSchema trekSchema = new DBSchema("starTrek");
// create tables for ooschema
TableSchema crewman = new TableSchema("crewman");
crewman.addColumns(
new Column("cid", false), // false means non-quoted
new Column("fname", false),
new Column("lname", false));
TableSchema commander = new TableSchema("commander");
commander.addColumn(new Column("rank", false));
TableSchema lieutenant = new TableSchema("lieutenant");
lieutenant.addColumn(new Column("specialty", false));
trekSchema.addTableSchema(crewman);
trekSchema.addTableSchema(commander);
trekSchema.addTableSchema(lieutenant);
// now createe the lone subtable declaration, and add to schema
SubTableSchema sts = new SubTableSchema(crewman);
sts.addSubTableSchema(commander);
sts.addSubTableSchema(lieutenant);
trekSchema.addSubTableSchema(sts);
// now print the schema declaration
return trekSchema;
}
Program
3: Programmatically Reading a Database
This
program reads a database given its file, prints the database using
available utilities and also the hard way by extracting out its
individual tables and printing its tuples.
void DBread() throws Exception {
System.out.format("\n\n START database read \n\n");
File dbfile = new File("TestData/DB/enterprise.starTrek.pl");
DB db = DB.readDataBase(dbfile, System.err);
// easy way to print
db.print(System.out);
// more detailed 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());
for (Tuple tup : t.tuples()) {
tup.print(System.out);
}
System.out.format("\n");
}
System.out.format("\n\n END database read \n\n");
}
Program
4: Programmatically Creating a Database
The
following program creates the startrek database, which has 3 tuples,
one tuple per table. An ooschema for the database is created
and then converted into a true schema (via the flatten operation).
At this point, a database instance of the schema can be
created, one table and one tuple at a time.
void DBBuild() throws Exception {
System.out.format("\n\n\n START build enterprise database\n\n");
DBSchema trekSchema = schemaBuild();
trekSchema.flatten();
DB enterprise = new DB("enterprise", trekSchema);
// create spock tuple
Table crewman = enterprise.getTableEH("crewman");
Tuple spock = new Tuple(crewman);
spock.addColumnValuesEH("c1", "mr", "spock");
crewman.add(spock);
// create sulu tuple
Table lieutenant = enterprise.getTableEH("lieutenant");
Tuple sulu = new Tuple(lieutenant);
sulu.addColumnValuesEH("c3", "hikaru", "sulo", "navigation");
lieutenant.add(sulu);
// create kirk tupld
Table commander = enterprise.getTableEH("commander");
Tuple kirk = new Tuple(commander);
kirk.addColumnValuesEH("c2", "james", "kirk", "captain");
commander.add(kirk);
// now print database
enterprise.print(System.out);
System.out.format("\n\n END build enterprise database \n\n");
}
Program
5: Programmatically Reading a Table
This
program reads a dog-owner database, which has a table of dogs.
Using Java Streams, the stream of all aussie tuples ('aussie'
means Australian Shepherd). This stream is then
printed. A similar stream is reconstituted (remember: once
you use a stream, it's finished) and then filtered further, and printed.
void TableRetrieve() {
System.out.format("BEGIN Table Retrieval\n\n");
DB db = DB.readDataBase("TestData/DB/dogOwner.do.pl");
Table dog = db.getTable("dog");
// query 1: retrieve all aussies
dog.stream().filter(t -> t.is("breed", "aussie")).forEach(t -> t.print(System.out));
System.out.println();
// query 1': retrieve all aussies -- version 2
dog.filter(t -> t.is("breed", "aussie")).print(System.out);
// query 2: retrieve all aussies that are blacktri's
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();
// query 2: another version, this time using TupleLists
dog.filter(t -> t.is("breed", "aussie") && t.is("color", "blacktri"))
.print(System.out);
System.out.format("END Table Retrieval");
}
Program
6: Programmatically Reading Tuples of a Table and its Subtables
This
program reads the tuples of a table and all of its subtables, using the
toTupleList() method. If you want to retrieve tuples of only
that table (and not its subtables), you use the getLocalTuples()
method. A couple queries are illustrated.
void InheritanceRetrieve() {
System.out.format("BEGIN DBTable Retrieval\n\n");
DB db = DB.readDataBase("TestData/DB/pets.petdb.pl");
Table pet = db.getTableEH("pet");
// query 1: retrieve all pets
pet.stream().forEach(t -> t.print(System.out));
// query 2: all pets whose name begins with "l" -- first way
System.out.println("\nlist of all pets whose names start with 'l'");
pet.stream().filter(t -> t.get("name").startsWith("l")).forEach(t -> t.print(System.out));
// query 2': all pets whose name begins with "l" -- second way
System.out.println("\nversion 2");
Predicate p = t -> t.get("name").startsWith("l");
pet.stream().filter(p).forEach(t -> t.print(System.out));
// query 2'': all pets whose name begins with "l" -- third way
System.out.println("\nversion 3");
Predicate p1 = t -> t.get("name").startsWith("l");
pet.filter(p1).project("name").print(System.out);
System.out.format("END DBTable Retrieval\n\n");
}
Program 7: Talking Cross Products of Schemas
Table
joins take cross products of schemas. Occasionally you might need
to do this yourself. The following program illustrates how cross
products of schemas are taken and printed.
public void crossSchema() {
DB db = DB.readDataBase("TestData/DB/dogOwner.do.pl");
// Step 1: cross the dog and owner schemas
TableSchema dogSchema = db.getTableSchema("dog");
dogSchema.print(System.out);
TableSchema ownerSchema = db.getTableSchema("owner");
ownerSchema.print(System.out);
TableSchema dXoSchema = dogSchema.crossProduct(ownerSchema);
dXoSchema.print(System.out);
// Step 2: get the tables to cross
Table dog = db.getTable("dog");
Table when = db.getTable("when");
Table owner = db.getTable("owner");
// Step 3: take cross product of schemas
when.schema.print(System.out);
TableSchema dogXwhen = dog.schema.crossProduct(when.schema);
dogXwhen.print(System.out);
TableSchema dogXwhenXowner = dogXwhen.crossProduct(owner.schema);
dogXwhenXowner.print(System.out);
}
Program 8: 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() {
System.out.format("BEGIN cross schema tests \n\n");
DB db = DB.readDataBase("TestData/DB/dogOwner.do.pl");
// Step 1: get the tables to cross
Table dog = db.getTable("dog");
Table when = db.getTable("when");
Table owner = db.getTable("owner");
// Step 2: join tables
Table dogXwhen = Table.join(dog, "did", when, "did");
dogXwhen.print(System.out);
Table dogXwhenXowner = Table.join(dogXwhen, "when.oid", owner, "oid");
dogXwhenXowner.print(System.out);
}
Program 8: Self Joins
Self-joins
(joining a table with itself) has always been an obstacle in database
development. 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 allows you
to relate parents to grandchildren, and so on.
void selfJoin() {
System.out.format("BEGIN cross schema tests \n\n");
DB db = DB.readDataBase("TestData/DB/dogOwner.do.pl");
// Step 1: get the tables to cross
Table dog = db.getTable("dog");
Table dog2 = dog.copyForSelfJoins("dog2");
// Step 2: join tables
Table dogXdog2 = Table.join(dog, "did", dog2, "did");
dogXdog2.print(System.out);
}
Program 9: Checking Uniqueness Constraints on a Table
Checking
that the name field of a table has unique values for all tuples is a
typical constraint. Java Streams are stateless, so one has to
create an operation that updates a stateful object which also will do
the work of error checking. MDELite6 uses the following scheme.
An ErrorReport object maintains a list of errors that have been
accumulated across any number of constraints. When stateful
computations are needed, like remembering the name values of previous
tuples seen -- to check whether a name value has been seen before, one
needs another object that does the checking and then reports errors, as
it encounters them to an ErrorReport object. This program
illustrates this process. A database is read, and a particular
table (dog) is found. An error reporter is created, along with a
"unique" object that looks at, in this case, the "name" field of each
tuple and performs a test to see if this name has been seen before.
If it has, an error is reported to the ErrorReport object, and
the message includes another attribute of the tuple, namely the
atttribute of its identifier ("did") so that the offending tuple can be
tracked down later. When an ErrorReport object is printed (via
printReport()), if there are errors, a RuntimeException is thrown,
terminating the program.
void Unique() {
DB db = DB.readDataBase("TestData/DB/dogOwner.do.pl");
Table dog = db.getTable("dog");
ErrorReport er = new ErrorReport();
Unique u = new Unique("did", "name", er);
dog.stream().forEach(t -> u.add(t));
try {
er.printReport(System.out);
} catch (Exception e) {
}
}
Program 10: Filtering Tuples from Tables
This
program illustrates several ways in which you can filter tuples from
tables. The first way uses standard for loops with an
if-statement. The second way creates a stream of tuples that are
filtered and printed. The third way creates a TupleList of a
relation, filters the tuplelist and prints the list. You'll
notice that the stream and tuplelist versions are very similar.
They should be: the only difference between the two is that
stream implementations process one tuple at a time. TupleList
implementations process one TupleList at a time.
public void filter() {
try {
File dbfile = new File("TestData/DB/enterprise.starTrek.pl");
DB db = DB.readDataBase(dbfile, System.err);
System.out.println("first way");
Table crew = db.getTableEH("crewman");
crew.schema.print(System.out);
for (Tuple t : crew.tuples()) {
if (t.get("fname").startsWith("h")) {
t.print(System.out);
}
}
System.out.println("\n\nsecond way");
crew.schema.print(System.out);
crew.stream()
.filter(t -> t.get("fname").startsWith("h"))
.forEach(t -> t.print(System.out));
System.out.println("\n\nthird way");
Predicate p = t -> t.get("fname").startsWith("h");
crew.filter(p).print(System.out);
} catch (Exception e) {
System.out.println(e.getMessage());
}
}
Program 11: Conform Tests
This
program illustrates an example from class. A Contract is owned by
precisely one Person or one Company, but never both. The value of
a Contract held by a Company must exceed 500 (dollars); the value of a
Contract held by a Person must not be greater than 100 (dollars).
Note the use of a ErrorReport object that collects all
conformance failures.
public void conform() {
DB db = DB.readDataBase("TestData/DB/ex.con.pl");
Table contract = db.getTableEH("contract");
ErrorReport er = new ErrorReport();
// Botn Constraint
contract.stream()
.filter(t -> ((t.isNull("pid") && t.isNull("cid"))
|| (!t.isNull("pid") && !t.isNull("cid"))))
.forEach(t -> er.add("Both constraint violated " + t.get("kid")));
// Company Constraint
contract.stream()
.filter(t -> (!t.isNull("cid") && t.getInt("value") < 500))
.forEach(t -> er.add("company constraint violated " + t.get("kid")));
// Person Constraint
contract.stream()
.filter(t -> !t.isNull("pid") && t.getInt("value") > 100)
.forEach(t -> er.add("person constraint violated " + t.get("kid")));
try {
er.printReport(System.out);
} catch (Exception e) {
}
}
Program 12: Reading and Writing Individual Tables
MDELite6
allows you to read and write inidividual 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("TestData/CSV/MixinPass6.csv");
csv.writeTable("NormalProlog.pl");
csv.writeTable("AsCSVFile.csv");
}