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");
    }