Reading and writing Tuple format rows directly

queries.sql

SELECT * FROM t;

SELECT * FROM t WHERE id = 2;

SELECT * FROM t WHERE name = 'Joe';

ReadRows.java

package test;

import com.foundationdb.Database;
import com.foundationdb.FDB;
import com.foundationdb.KeyValue;
import com.foundationdb.Transaction;
import com.foundationdb.directory.DirectoryLayer;
import com.foundationdb.directory.DirectorySubspace;
import com.foundationdb.async.AsyncIterable;
import com.foundationdb.async.AsyncUtil;
import com.foundationdb.async.Function;
import com.foundationdb.async.Future;
import com.foundationdb.tuple.Tuple;

import java.util.Arrays;
import java.util.List;

public class ReadRows
{
    public static final String SCHEMA = "test";
    public static final String TABLE = "t";
    public static final String INDEX = "t_name";

    private Database database;
    private DirectorySubspace tableSubspace, indexSubspace;

    public static void main(String[] args) {
        new ReadRows(args).run();
    }

    public ReadRows(String[] args) {
        FDB fdb = FDB.selectAPIVersion(200);
        this.database = fdb.open();
    }

    public void run() {
        database.run(new Function<Transaction,Void> () {
                         @Override
                         public Void apply(Transaction tr) {
                             tableSubspace = DirectoryLayer.getDefault().open(tr, Arrays.asList("sql", "data", "table", SCHEMA, TABLE)).get();
                             indexSubspace = tableSubspace.open(tr, Arrays.asList(INDEX)).get();
                             return null;
                         }
                     });
        List<Object> row = database.run(new Function<Transaction,List<Object>> () {
                         @Override
                         public List<Object> apply(Transaction tr) {
                             Tuple t = readRow(tr, 2);
                             if (t == null) {
                                 return null;
                             }
                             else {
                                 return t.getItems();
                             }
                         }
                     });
        System.out.println(row);
        List<List<Object>> rows = database.run(new Function<Transaction,List<List<Object>>> () {
                         @Override
                         public List<List<Object>> apply(Transaction tr) {
                             return AsyncUtil.mapIterable(readIndexedRows(tr, "Joe"),
                                 new Function<Tuple,List<Object>>() {
                                     @Override
                                     public List<Object> apply(Tuple t) {
                                         return t.getItems();
                                     }
                             }).asList().get();
                         }
                     });
        System.out.println(rows);
    }

    protected Tuple readRow(Transaction tr, int pkey) {
        // 1 is ordinal for a group root; first column is primary key.
        byte[] rowKey = tableSubspace.pack(Tuple.from(1, pkey));
        byte[] row = tr.get(rowKey).get();
        if (row == null) {
            return null;
        }
        else {
            return Tuple.fromBytes(row);
        }
    }

    protected AsyncIterable<Tuple> readIndexedRows(final Transaction tr, String key) {
        // Key is indexed columns followed by any hkey columns not in already included.
        // For a single table group, the hkey is the primary keys.
        // The value is empty for simple indexes.
        return AsyncUtil.mapIterable(tr.getRange(indexSubspace.range(Tuple.from(key))),
            // A real implementation would need to do pipelining, collecting more
            // than one Future<Tuple>.
            // Cf. MoreAsyncUtil.mapIterablePipelined in graph layer, which does this.
            new Function<KeyValue,Tuple>() {
                @Override
                public Tuple apply(KeyValue kv) {
                    Tuple t = indexSubspace.unpack(kv.getKey());
                    // 0 is name, 1 is id.
                    return readRow(tr, (int)t.getLong(1));
                }
            });
    }

}

schema.sql

CREATE TABLE t(id INT PRIMARY KEY NOT NULL, name VARCHAR(32), address VARCHAR(128)) STORAGE_FORMAT tuple;

CREATE INDEX t_name ON t(name) STORAGE_FORMAT tuple;

WriteRows.java

package test;

import com.foundationdb.Database;
import com.foundationdb.FDB;
import com.foundationdb.Transaction;
import com.foundationdb.directory.DirectoryLayer;
import com.foundationdb.directory.DirectorySubspace;
import com.foundationdb.async.Function;
import com.foundationdb.async.Future;
import com.foundationdb.tuple.Tuple;

import java.util.Arrays;

public class WriteRows
{
    public static final String SCHEMA = "test";
    public static final String TABLE = "t";
    public static final String INDEX = "t_name";

    public static final Object[][] ROWS = {
        { 101, "Joe", "Boston" },
        { 102, "James", "Washington" }
    };

    private Database database;
    private DirectorySubspace tableSubspace, indexSubspace;

    public static void main(String[] args) {
        new WriteRows(args).run();
    }

    public WriteRows(String[] args) {
        FDB fdb = FDB.selectAPIVersion(200);
        this.database = fdb.open();
    }

    public void run() {
        database.run(new Function<Transaction,Void> () {
                         @Override
                         public Void apply(Transaction tr) {
                             tableSubspace = DirectoryLayer.getDefault().open(tr, Arrays.asList("sql", "data", "table", SCHEMA, TABLE)).get();
                             indexSubspace = tableSubspace.open(tr, Arrays.asList(INDEX)).get();
                             return null;
                         }
                     });
        database.run(new Function<Transaction,Void> () {
                         @Override
                         public Void apply(Transaction tr) {
                             for (Object[] row : ROWS) {
                                 writeRow(tr, row);
                             }
                             return null;
                         }
                     });
    }

    protected void writeRow(Transaction tr, Object... row) {
        // 1 is ordinal for a group root; first column is primary key.
        byte[] rowKey = tableSubspace.pack(Tuple.from(1, row[0]));
        // A real implementation might check that all the datatypes are compatible
        // with the SQL declaration, that the number of columns is correct, etc.
        byte[] rowValue = Tuple.from(row).pack();
        tr.set(rowKey, rowValue);
        // From second column (name) to primary key.
        byte[] indexKey = indexSubspace.pack(Tuple.from(row[1], row[0]));
        byte[] indexValue = new byte[0];
        tr.set(indexKey, indexValue);
        // A real implementation should maintain the PRIMARY index, too, although it
        // isn't used much for single table groups.
        // Or else there needs to be an option not to have one.
    }

}