Add the Joiner utility and some new methods to IndexCursor to make it easier to join tables using pre-defined (index backed) relationships.

git-svn-id: https://svn.code.sf.net/p/jackcess/code/jackcess/trunk@565 f203690c-595d-4dc9-a70b-905162fa7fd2

14 files changed, 723 insertions, 90 deletions

diff --git a/src/changes/changes.xml b/src/changes/changes.xml
index 39758a1..a030d07 100644
--- a/src/changes/changes.xml
+++ b/src/changes/changes.xml
@@ -8,6 +8,10 @@
       <action dev="jahlborn" type="update">
         Try multiple classloaders when loading resources as streams.
       </action>
+      <action dev="jahlborn" type="add">
+        Add the Joiner utility and some new methods to IndexCursor to make it
+        easier to join tables using pre-defined (index backed) relationships.
+      </action>
     </release>
     <release version="1.2.4" date="2011-05-14">
       <action dev="jahlborn" type="update">
diff --git a/src/java/com/healthmarketscience/jackcess/Cursor.java b/src/java/com/healthmarketscience/jackcess/Cursor.java
index 81a2555..045fcea 100644
--- a/src/java/com/healthmarketscience/jackcess/Cursor.java
+++ b/src/java/com/healthmarketscience/jackcess/Cursor.java
@@ -447,7 +447,7 @@ public abstract class Cursor implements Iterable<Map<String, Object>>
 
   /**
    * Returns an Iterable whose iterator() method calls <code>afterLast</code>
-   * on this cursor and returns an unmodifiable Iterator which will iterate
+   * on this cursor and returns a modifiable Iterator which will iterate
    * through all the rows of this table in reverse order.  Use of the Iterator
    * follows the same restrictions as a call to <code>getPreviousRow</code>.
    * @throws IllegalStateException if an IOException is thrown by one of the
@@ -459,7 +459,7 @@ public abstract class Cursor implements Iterable<Map<String, Object>>
   
   /**
    * Returns an Iterable whose iterator() method calls <code>afterLast</code>
-   * on this table and returns an unmodifiable Iterator which will iterate
+   * on this table and returns a modifiable Iterator which will iterate
    * through all the rows of this table in reverse order, returning only the
    * given columns.  Use of the Iterator follows the same restrictions as a
    * call to <code>getPreviousRow</code>.
@@ -477,7 +477,7 @@ public abstract class Cursor implements Iterable<Map<String, Object>>
   }
   
   /**
-   * Calls <code>beforeFirst</code> on this cursor and returns an unmodifiable
+   * Calls <code>beforeFirst</code> on this cursor and returns a modifiable
    * Iterator which will iterate through all the rows of this table.  Use of
    * the Iterator follows the same restrictions as a call to
    * <code>getNextRow</code>.
@@ -506,7 +506,7 @@ public abstract class Cursor implements Iterable<Map<String, Object>>
   }
   
   /**
-   * Calls <code>beforeFirst</code> on this table and returns an unmodifiable
+   * Calls <code>beforeFirst</code> on this table and returns a modifiable
    * Iterator which will iterate through all the rows of this table, returning
    * only the given columns.  Use of the Iterator follows the same
    * restrictions as a call to <code>getNextRow</code>.
@@ -962,26 +962,27 @@ public abstract class Cursor implements Iterable<Map<String, Object>>
    */
   protected abstract DirHandler getDirHandler(boolean moveForward);
 
+
   /**
-   * Row iterator for this table, modifiable.
+   * Base implementation of iterator for this cursor, modifiable.
    */
-  private final class RowIterator implements Iterator<Map<String, Object>>
+  protected abstract class BaseIterator
+    implements Iterator<Map<String, Object>>
   {
-    private final Collection<String> _columnNames;
-    private final boolean _moveForward;
-    private Boolean _hasNext;
+    protected final Collection<String> _columnNames;
+    protected Boolean _hasNext;
+    protected boolean _validRow;
     
-    private RowIterator(Collection<String> columnNames, boolean moveForward)
+    protected BaseIterator(Collection<String> columnNames)
     {
       _columnNames = columnNames;
-      _moveForward = moveForward;
-      reset(_moveForward);
     }
 
     public boolean hasNext() {
       if(_hasNext == null) {
         try {
-          _hasNext = moveToAnotherRow(_moveForward);
+          _hasNext = findNext();
+          _validRow = _hasNext;
         } catch(IOException e) {
           throw new IllegalStateException(e);
         }
@@ -1003,12 +1004,40 @@ public abstract class Cursor implements Iterable<Map<String, Object>>
     }
 
     public void remove() {
-      try {
-        deleteCurrentRow();
-      } catch(IOException e) {
-        throw new IllegalStateException(e);
+      if(_validRow) {
+        try {
+          deleteCurrentRow();
+          _validRow = false;
+        } catch(IOException e) {
+          throw new IllegalStateException(e);
+        }
+      } else {
+        throw new IllegalStateException("Not at valid row");
       }
-    }    
+    }
+
+    protected abstract boolean findNext() throws IOException;
+  }
+
+  
+  /**
+   * Row iterator for this cursor, modifiable.
+   */
+  private final class RowIterator extends BaseIterator
+  {
+    private final boolean _moveForward;
+    
+    private RowIterator(Collection<String> columnNames, boolean moveForward)
+    {
+      super(columnNames);
+      _moveForward = moveForward;
+      reset(_moveForward);
+    }
+
+    @Override
+    protected boolean findNext() throws IOException {
+      return moveToAnotherRow(_moveForward);
+    }
   }
 
   /**
diff --git a/src/java/com/healthmarketscience/jackcess/IndexCursor.java b/src/java/com/healthmarketscience/jackcess/IndexCursor.java
index 2529c40..6d02fa2 100644
--- a/src/java/com/healthmarketscience/jackcess/IndexCursor.java
+++ b/src/java/com/healthmarketscience/jackcess/IndexCursor.java
@@ -20,7 +20,9 @@ USA
 package com.healthmarketscience.jackcess;
 
 import java.io.IOException;
+import java.util.Collection;
 import java.util.HashSet;
+import java.util.Iterator;
 import java.util.LinkedHashMap;
 import java.util.Map;
 import java.util.Set;
@@ -156,9 +158,8 @@ public class IndexCursor extends Cursor
 
   /**
    * Moves to the first row (as defined by the cursor) where the index entries
-   * match the given values (only valid for index-backed cursors).  If a match
-   * is not found (or an exception is thrown), the cursor is restored to its
-   * previous state.
+   * match the given values.  If a match is not found (or an exception is
+   * thrown), the cursor is restored to its previous state.
    *
    * @param entryValues the column values for the index's columns.
    * @return {@code true} if a valid row was found with the given values,
@@ -171,7 +172,7 @@ public class IndexCursor extends Cursor
     Position prevPos = _prevPos;
     boolean found = false;
     try {
-      found = findRowByEntryImpl(entryValues, true);
+      found = findRowByEntryImpl(toRowValues(entryValues), true);
       return found;
     } finally {
       if(!found) {
@@ -186,8 +187,8 @@ public class IndexCursor extends Cursor
 
   /**
    * Moves to the first row (as defined by the cursor) where the index entries
-   * are >= the given values (only valid for index-backed cursors).  If a an
-   * exception is thrown, the cursor is restored to its previous state.
+   * are >= the given values.  If a an exception is thrown, the cursor is
+   * restored to its previous state.
    *
    * @param entryValues the column values for the index's columns.
    */
@@ -198,7 +199,7 @@ public class IndexCursor extends Cursor
     Position prevPos = _prevPos;
     boolean found = false;
     try {
-      findRowByEntryImpl(entryValues, false);
+      findRowByEntryImpl(toRowValues(entryValues), false);
       found = true;
     } finally {
       if(!found) {
@@ -210,7 +211,69 @@ public class IndexCursor extends Cursor
       }
     }
   }
-    
+
+  /**
+   * Returns {@code true} if the current row matches the given index entries.
+   * 
+   * @param entryValues the column values for the index's columns.
+   */
+  public boolean currentRowMatchesEntry(Object... entryValues) 
+    throws IOException 
+  {
+    return currentRowMatchesEntryImpl(toRowValues(entryValues));
+  }
+  
+  /**
+   * Returns a modifiable Iterator which will iterate through all the rows of
+   * this table which match the given index entries.
+   * @throws IllegalStateException if an IOException is thrown by one of the
+   *         operations, the actual exception will be contained within
+   */
+  public Iterator<Map<String,Object>> entryIterator(Object... entryValues)
+  {
+    return entryIterator((Collection<String>)null, entryValues);
+  }
+  
+  /**
+   * Returns a modifiable Iterator which will iterate through all the rows of
+   * this table which match the given index entries, returning only the given
+   * columns.
+   * @throws IllegalStateException if an IOException is thrown by one of the
+   *         operations, the actual exception will be contained within
+   */
+  public Iterator<Map<String,Object>> entryIterator(
+      Collection<String> columnNames, Object... entryValues)
+  {
+    return new EntryIterator(columnNames, toRowValues(entryValues));
+  }
+  
+  /**
+   * Returns an Iterable whose iterator() method returns the result of a call
+   * to {@link #entryIterator(Object...)}
+   * @throws IllegalStateException if an IOException is thrown by one of the
+   *         operations, the actual exception will be contained within
+   */
+  public Iterable<Map<String,Object>> entryIterable(Object... entryValues)
+  {
+    return entryIterable((Collection<String>)null, entryValues);
+  }
+  
+  /**
+   * Returns an Iterable whose iterator() method returns the result of a call
+   * to {@link #entryIterator(Collection,Object...)}
+   * @throws IllegalStateException if an IOException is thrown by one of the
+   *         operations, the actual exception will be contained within
+   */
+  public Iterable<Map<String,Object>> entryIterable(
+      final Collection<String> columnNames, final Object... entryValues)
+  {
+    return new Iterable<Map<String, Object>>() {
+      public Iterator<Map<String, Object>> iterator() {
+        return new EntryIterator(columnNames, toRowValues(entryValues));
+      }
+    };
+  }
+  
   @Override
   protected IndexDirHandler getDirHandler(boolean moveForward) {
     return (moveForward ? _forwardDirHandler : _reverseDirHandler);
@@ -267,19 +330,15 @@ public class IndexCursor extends Cursor
    * Moves to the first row (as defined by the cursor) where the index entries
    * match the given values.  Caller manages save/restore on failure.
    *
-   * @param entryValues the column values for the index's columns.
+   * @param rowValues the column values built from the index column values
    * @param requireMatch whether or not an exact match is found
    * @return {@code true} if a valid row was found with the given values,
    *         {@code false} if no row was found
    */
-  protected boolean findRowByEntryImpl(Object[] entryValues,
-                                            boolean requireMatch) 
+  protected boolean findRowByEntryImpl(Object[] rowValues,
+                                       boolean requireMatch) 
     throws IOException 
   {
-    Object[] rowValues = _entryCursor.getIndexData()
-      .constructIndexRowFromEntry(entryValues);
-      
-    // sweet, we can use our index
     if(!findPotentialRow(rowValues, requireMatch)) {
       return false;
     } else if(!requireMatch) {
@@ -287,28 +346,7 @@ public class IndexCursor extends Cursor
       return true;
     }
 
-    if(_indexEntryPattern == null) {
-      // init our set of index column names
-      _indexEntryPattern = new HashSet<String>();
-      for(IndexData.ColumnDescriptor col : getIndex().getColumns()) {
-        _indexEntryPattern.add(col.getName());
-      }
-    }
-
-    // check the next row to see if it actually matches
-    Map<String,Object> row = getCurrentRow(_indexEntryPattern);
-
-    for(IndexData.ColumnDescriptor col : getIndex().getColumns()) {
-      String columnName = col.getName();
-      Object patValue = rowValues[col.getColumnIndex()];
-      Object rowValue = row.get(columnName);
-      if(!_columnMatcher.matches(getTable(), columnName,
-                                 patValue, rowValue)) {
-        return false;
-      }
-    }
-
-    return true;
+    return currentRowMatchesEntryImpl(rowValues);
   }
 
   @Override
@@ -338,8 +376,7 @@ public class IndexCursor extends Cursor
     } else {
       // the rowPattern has more columns than just the index, so we need to
       // do more work when testing below
-      indexRowPattern =
-        new LinkedHashMap<String,Object>();
+      indexRowPattern = new LinkedHashMap<String,Object>();
       for(IndexData.ColumnDescriptor idxCol : indexData.getColumns()) {
         indexRowPattern.put(idxCol.getName(),
                             rowValues[idxCol.getColumnIndex()]);
@@ -368,7 +405,34 @@ public class IndexCursor extends Cursor
     // none of the potential rows matched
     return false;
   }
-    
+
+  private boolean currentRowMatchesEntryImpl(Object[] rowValues)
+    throws IOException
+  {
+    if(_indexEntryPattern == null) {
+      // init our set of index column names
+      _indexEntryPattern = new HashSet<String>();
+      for(IndexData.ColumnDescriptor col : getIndex().getColumns()) {
+        _indexEntryPattern.add(col.getName());
+      }
+    }
+
+    // check the next row to see if it actually matches
+    Map<String,Object> row = getCurrentRow(_indexEntryPattern);
+
+    for(IndexData.ColumnDescriptor col : getIndex().getColumns()) {
+      String columnName = col.getName();
+      Object patValue = rowValues[col.getColumnIndex()];
+      Object rowValue = row.get(columnName);
+      if(!_columnMatcher.matches(getTable(), columnName,
+                                 patValue, rowValue)) {
+        return false;
+      }
+    }
+
+    return true;    
+  }
+  
   private boolean findPotentialRow(Object[] rowValues, boolean requireMatch)
     throws IOException
   {
@@ -383,6 +447,11 @@ public class IndexCursor extends Cursor
     return true;
   }
 
+  private Object[] toRowValues(Object[] entryValues)
+  {
+    return _entryCursor.getIndexData().constructIndexRowFromEntry(entryValues);
+  }
+  
   @Override
   protected Position findAnotherPosition(RowState rowState, Position curPos,
                                          boolean moveForward)
@@ -480,5 +549,31 @@ public class IndexCursor extends Cursor
       return "Entry = " + getEntry();
     }
   }
+
+  /**
+   * Row iterator (by matching entry) for this cursor, modifiable.
+   */
+  private final class EntryIterator extends BaseIterator
+  {
+    private final Object[] _rowValues;
+    
+    private EntryIterator(Collection<String> columnNames, Object[] rowValues)
+    {
+      super(columnNames);
+      _rowValues = rowValues;
+      try {
+        _hasNext = findRowByEntryImpl(rowValues, true);
+        _validRow = _hasNext;
+      } catch(IOException e) {
+          throw new IllegalStateException(e);
+      }
+    }
+
+    @Override
+    protected boolean findNext() throws IOException {
+      return (moveToNextRow() && currentRowMatchesEntryImpl(_rowValues));
+    }    
+  }
+
   
 }
diff --git a/src/java/com/healthmarketscience/jackcess/Joiner.java b/src/java/com/healthmarketscience/jackcess/Joiner.java
new file mode 100644
index 0000000..afe445d
--- /dev/null
+++ b/src/java/com/healthmarketscience/jackcess/Joiner.java
@@ -0,0 +1,196 @@
+/*
+Copyright (c) 2011 James Ahlborn
+
+This library is free software; you can redistribute it and/or
+modify it under the terms of the GNU Lesser General Public
+License as published by the Free Software Foundation; either
+version 2.1 of the License, or (at your option) any later version.
+
+This library is distributed in the hope that it will be useful,
+but WITHOUT ANY WARRANTY; without even the implied warranty of
+MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+Lesser General Public License for more details.
+
+You should have received a copy of the GNU Lesser General Public
+License along with this library; if not, write to the Free Software
+Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307
+USA
+*/
+
+package com.healthmarketscience.jackcess;
+
+import java.io.IOException;
+import java.util.Collection;
+import java.util.Iterator;
+import java.util.List;
+import java.util.Map;
+
+/**
+ * Utility for finding rows based on pre-defined, foreign-key table
+ * relationships.
+ *
+ * @author James Ahlborn
+ */
+public class Joiner 
+{
+  private final Index _fromIndex;
+  private final List<IndexData.ColumnDescriptor> _fromCols;
+  private final IndexCursor _toCursor;
+  private final Object[] _entryValues;
+  
+  private Joiner(Index fromIndex, IndexCursor toCursor)
+  {
+    _fromIndex = fromIndex;
+    _fromCols = _fromIndex.getColumns();
+    _entryValues = new Object[_fromCols.size()];
+    _toCursor = toCursor;
+  }
+
+  /**
+   * Creates a new Joiner based on the given index which backs a foreign-key
+   * relationship.  The table of the given index will be the "from" table and
+   * the table on the other end of the relationsip is the "to" table.
+   *
+   * @param fromIndex the index backing one side of a foreign-key relationship
+   */
+  public static Joiner create(Index fromIndex)
+    throws IOException
+  {
+    Index toIndex = fromIndex.getReferencedIndex();
+    IndexCursor toCursor = IndexCursor.createCursor(
+        toIndex.getTable(), toIndex);
+    // text lookups are always case-insensitive
+    toCursor.setColumnMatcher(CaseInsensitiveColumnMatcher.INSTANCE);
+    return new Joiner(fromIndex, toCursor);
+  }
+  
+  public Table getFromTable()
+  {
+    return getFromIndex().getTable();
+  }
+  
+  public Index getFromIndex()
+  {
+    return _fromIndex;
+  }
+  
+  public Table getToTable()
+  {
+    return getToCursor().getTable();
+  }
+  
+  public Index getToIndex()
+  {
+    return getToCursor().getIndex();
+  }
+  
+  public IndexCursor getToCursor()
+  {
+    return _toCursor;
+  }
+
+  /**
+   * Returns the first row in the "to" table based on the given columns in the
+   * "from" table if any, {@code null} if there is no matching row.
+   *
+   * @param fromRow row from the "from" table (which must include the relevant
+   *                columns for this join relationship)
+   */
+  public Map<String,Object> findFirstRow(Map<String,Object> fromRow)
+    throws IOException
+  {
+    return findFirstRow(fromRow, null);
+  }
+  
+  /**
+   * Returns selected columns from the first row in the "to" table based on
+   * the given columns in the "from" table if any, {@code null} if there is no
+   * matching row.
+   *
+   * @param fromRow row from the "from" table (which must include the relevant
+   *                columns for this join relationship)
+   * @param columnNames desired columns in the from table row
+   */
+  public Map<String,Object> findFirstRow(Map<String,Object> fromRow,
+                                         Collection<String> columnNames)
+    throws IOException
+  {
+    toEntryValues(fromRow);
+    return ((_toCursor.findRowByEntry(_entryValues) ?
+             _toCursor.getCurrentRow(columnNames) : null));
+  }
+
+  /**
+   * Returns an Iterator over all the rows in the "to" table based on the
+   * given columns in the "from" table.
+   *
+   * @param fromRow row from the "from" table (which must include the relevant
+   *                columns for this join relationship)
+   */
+  public Iterator<Map<String,Object>> findRows(Map<String,Object> fromRow)
+  {
+    return findRows(fromRow, null);
+  }
+  
+  /**
+   * Returns an Iterator with the selected columns over all the rows in the
+   * "to" table based on the given columns in the "from" table.
+   *
+   * @param fromRow row from the "from" table (which must include the relevant
+   *                columns for this join relationship)
+   * @param columnNames desired columns in the from table row
+   */
+  public Iterator<Map<String,Object>> findRows(Map<String,Object> fromRow,
+                                               Collection<String> columnNames)
+  {
+    toEntryValues(fromRow);
+    return _toCursor.entryIterator(columnNames, _entryValues);
+  }
+
+  /**
+   * Returns an Iterable whose iterator() method returns the result of a call
+   * to {@link #findRows(Map)}
+   * 
+   * @param fromRow row from the "from" table (which must include the relevant
+   *                columns for this join relationship)
+   * @throws IllegalStateException if an IOException is thrown by one of the
+   *         operations, the actual exception will be contained within
+   */
+  public Iterable<Map<String,Object>> findRowsIterable(
+      Map<String,Object> fromRow)
+  {
+    return findRowsIterable(fromRow, null);
+  }
+  
+  /**
+   * Returns an Iterable whose iterator() method returns the result of a call
+   * to {@link #findRows(Map,Collection)}
+   * 
+   * @param fromRow row from the "from" table (which must include the relevant
+   *                columns for this join relationship)
+   * @param columnNames desired columns in the from table row
+   * @throws IllegalStateException if an IOException is thrown by one of the
+   *         operations, the actual exception will be contained within
+   */
+  public Iterable<Map<String,Object>> findRowsIterable(
+      final Map<String,Object> fromRow, final Collection<String> columnNames)
+  {
+    return new Iterable<Map<String, Object>>() {
+      public Iterator<Map<String, Object>> iterator() {
+        return findRows(fromRow, columnNames);
+      }
+    };
+  }
+
+  /**
+   * Fills in the _entryValues with the relevant info from the given "from"
+   * table row.
+   */
+  private void toEntryValues(Map<String,Object> fromRow)
+  {
+    for(int i = 0; i < _entryValues.length; ++i) {
+      _entryValues[i] = fromRow.get(_fromCols.get(i).getName());
+    }
+  }
+  
+}
diff --git a/src/java/com/healthmarketscience/jackcess/Table.java b/src/java/com/healthmarketscience/jackcess/Table.java
index 8808728..1d1c7a3 100644
--- a/src/java/com/healthmarketscience/jackcess/Table.java
+++ b/src/java/com/healthmarketscience/jackcess/Table.java
@@ -795,7 +795,7 @@ public class Table
 
   
   /**
-   * Calls <code>reset</code> on this table and returns an unmodifiable
+   * Calls <code>reset</code> on this table and returns a modifiable
    * Iterator which will iterate through all the rows of this table.  Use of
    * the Iterator follows the same restrictions as a call to
    * <code>getNextRow</code>.
@@ -808,7 +808,7 @@ public class Table
   }
   
   /**
-   * Calls <code>reset</code> on this table and returns an unmodifiable
+   * Calls <code>reset</code> on this table and returns a modifiable
    * Iterator which will iterate through all the rows of this table, returning
    * only the given columns.  Use of the Iterator follows the same
    * restrictions as a call to <code>getNextRow</code>.
diff --git a/test/data/V1997/indexTestV1997.mdb b/test/data/V1997/indexTestV1997.mdb
index 3441d23..07583f9 100644
--- a/test/data/V1997/indexTestV1997.mdb
+++ b/test/data/V1997/indexTestV1997.mdb
diff --git a/test/data/V2000/indexTestV2000.mdb b/test/data/V2000/indexTestV2000.mdb
index 7e8bd98..facd246 100644
--- a/test/data/V2000/indexTestV2000.mdb
+++ b/test/data/V2000/indexTestV2000.mdb
diff --git a/test/data/V2003/indexTestV2003.mdb b/test/data/V2003/indexTestV2003.mdb
index 62a40e0..8662b8b 100644
--- a/test/data/V2003/indexTestV2003.mdb
+++ b/test/data/V2003/indexTestV2003.mdb
diff --git a/test/data/V2007/indexTestV2007.accdb b/test/data/V2007/indexTestV2007.accdb
index 4930831..0e4fd26 100644
--- a/test/data/V2007/indexTestV2007.accdb
+++ b/test/data/V2007/indexTestV2007.accdb
diff --git a/test/data/V2010/indexTestV2010.accdb b/test/data/V2010/indexTestV2010.accdb
index 48d73f6..6946b32 100644
--- a/test/data/V2010/indexTestV2010.accdb
+++ b/test/data/V2010/indexTestV2010.accdb
diff --git a/test/src/java/com/healthmarketscience/jackcess/CursorTest.java b/test/src/java/com/healthmarketscience/jackcess/CursorTest.java
index c8e5d7e..dc79685 100644
--- a/test/src/java/com/healthmarketscience/jackcess/CursorTest.java
+++ b/test/src/java/com/healthmarketscience/jackcess/CursorTest.java
@@ -30,15 +30,16 @@ package com.healthmarketscience.jackcess;
 import java.util.ArrayList;
 import java.util.Arrays;
 import java.util.Collections;
+import java.util.Iterator;
 import java.util.List;
 import java.util.Map;
+import java.util.NoSuchElementException;
 import java.util.TreeSet;
 
-import junit.framework.TestCase;
-
 import static com.healthmarketscience.jackcess.Database.*;
 import static com.healthmarketscience.jackcess.DatabaseTest.*;
 import static com.healthmarketscience.jackcess.JetFormatTest.*;
+import junit.framework.TestCase;
 
 /**
  * @author James Ahlborn
@@ -821,4 +822,144 @@ public class CursorTest extends TestCase {
     }
   }
 
+  public void testIndexCursor() throws Exception
+  { 
+    for (final TestDB testDB : TestDB.getSupportedForBasename(Basename.INDEX)) {
+
+      Database db = open(testDB);
+      Table t1 = db.getTable("Table1");
+      Index idx = t1.getIndex(IndexBuilder.PRIMARY_KEY_NAME);
+      IndexCursor cursor = IndexCursor.createCursor(t1, idx);
+
+      assertFalse(cursor.findRowByEntry(-1));
+      cursor.findClosestRowByEntry(-1);
+      assertEquals(0, cursor.getCurrentRow().get("id"));
+
+      assertTrue(cursor.findRowByEntry(1));
+      assertEquals(1, cursor.getCurrentRow().get("id"));
+      
+      cursor.findClosestRowByEntry(2);
+      assertEquals(2, cursor.getCurrentRow().get("id"));
+
+      assertFalse(cursor.findRowByEntry(4));
+      cursor.findClosestRowByEntry(4);
+      assertTrue(cursor.isAfterLast());
+
+      db.close();
+    }    
+  }
+  
+  public void testIndexCursorDelete() throws Exception
+  { 
+    for (final TestDB testDB : TestDB.getSupportedForBasename(Basename.INDEX)) {
+
+      Database db = openCopy(testDB);
+      Table t1 = db.getTable("Table1");
+      Index idx = t1.getIndex("Table2Table1");
+      IndexCursor cursor = IndexCursor.createCursor(t1, idx);
+
+      List<String> expectedData = new ArrayList<String>();
+      for(Map<String,Object> row : cursor.entryIterable(
+              Arrays.asList("data"), 1)) {
+        expectedData.add((String)row.get("data"));
+      }
+
+      assertEquals(Arrays.asList("baz11", "baz11-2"), expectedData);
+
+      expectedData = new ArrayList<String>();
+      for(Iterator<Map<String,Object>> iter = cursor.entryIterator(1);
+          iter.hasNext(); ) {
+        expectedData.add((String)iter.next().get("data"));
+        iter.remove();
+        try {
+          iter.remove();
+          fail("IllegalArgumentException should have been thrown");
+        } catch(IllegalStateException e) {
+          // success
+        }
+
+        if(!iter.hasNext()) {
+          try {
+            iter.next();
+            fail("NoSuchElementException should have been thrown");
+          } catch(NoSuchElementException e) {
+            // success
+          }
+        }
+      }
+
+      assertEquals(Arrays.asList("baz11", "baz11-2"), expectedData);
+      
+      expectedData = new ArrayList<String>();
+      for(Map<String,Object> row : cursor.entryIterable(
+              Arrays.asList("data"), 1)) {
+        expectedData.add((String)row.get("data"));
+      }
+
+      assertTrue(expectedData.isEmpty());
+      
+      db.close();
+    }    
+  }
+  
+  public void testCursorDelete() throws Exception
+  { 
+    for (final TestDB testDB : TestDB.getSupportedForBasename(Basename.INDEX)) {
+
+      Database db = openCopy(testDB);
+      Table t1 = db.getTable("Table1");
+      Cursor cursor = Cursor.createCursor(t1);
+
+      List<String> expectedData = new ArrayList<String>();
+      for(Map<String,Object> row : cursor.iterable(
+              Arrays.asList("otherfk1", "data"))) {
+        if(row.get("otherfk1").equals(1)) {
+          expectedData.add((String)row.get("data"));
+        }
+      }
+
+      assertEquals(Arrays.asList("baz11", "baz11-2"), expectedData);
+
+      expectedData = new ArrayList<String>();
+      for(Iterator<Map<String,Object>> iter = cursor.iterator();
+          iter.hasNext(); ) {
+        Map<String,Object> row = iter.next();
+        if(row.get("otherfk1").equals(1)) {
+          expectedData.add((String)row.get("data"));
+          iter.remove();
+          try {
+            iter.remove();
+            fail("IllegalArgumentException should have been thrown");
+          } catch(IllegalStateException e) {
+            // success
+          }
+        }
+
+        if(!iter.hasNext()) {
+          try {
+            iter.next();
+            fail("NoSuchElementException should have been thrown");
+          } catch(NoSuchElementException e) {
+            // success
+          }
+        }
+      }
+
+      assertEquals(Arrays.asList("baz11", "baz11-2"), expectedData);
+      
+      expectedData = new ArrayList<String>();
+      for(Map<String,Object> row : cursor.iterable(
+              Arrays.asList("otherfk1", "data"))) {
+        if(row.get("otherfk1").equals(1)) {
+          expectedData.add((String)row.get("data"));
+        }
+      }
+
+      assertTrue(expectedData.isEmpty());
+      
+      db.close();
+    }    
+  }
+  
 }
+  
diff --git a/test/src/java/com/healthmarketscience/jackcess/IndexTest.java b/test/src/java/com/healthmarketscience/jackcess/IndexTest.java
index 3ebfff6..21e4fd9 100644
--- a/test/src/java/com/healthmarketscience/jackcess/IndexTest.java
+++ b/test/src/java/com/healthmarketscience/jackcess/IndexTest.java
@@ -113,10 +113,8 @@ public class IndexTest extends TestCase {
       checkIndexColumns(table,
                         "id", "id",
                         "PrimaryKey", "id",
-                        getRelationshipName(testDB.getExpectedFormat(), 
-                                            "Table2Table1"), "otherfk1",
-                        getRelationshipName(testDB.getExpectedFormat(), 
-                                            "Table3Table1"), "otherfk2");
+                        "Table2Table1", "otherfk1",
+                        "Table3Table1", "otherfk2");
 
       table = mdb.getTable("Table2");
       for(Index idx : table.getIndexes()) {
@@ -474,20 +472,22 @@ public class IndexTest extends TestCase {
       Table t2 = db.getTable("Table2");
       Table t3 = db.getTable("Table3");
 
-      Index t2t1 = t1.getIndex(IndexTest.getRelationshipName(
-                                   db.getFormat(), "Table2Table1"));
-      Index t3t1 = t1.getIndex(IndexTest.getRelationshipName(
-                                   db.getFormat(), "Table3Table1"));
+      Index t2t1 = t1.getIndex("Table2Table1");
+      Index t3t1 = t1.getIndex("Table3Table1");
 
 
       assertTrue(t2t1.isForeignKey());
       assertNotNull(t2t1.getReference());
       assertFalse(t2t1.getReference().isPrimaryTable());
+      assertFalse(t2t1.getReference().isCascadeUpdates());
+      assertTrue(t2t1.getReference().isCascadeDeletes());
       doCheckForeignKeyIndex(t1, t2t1, t2);
 
       assertTrue(t3t1.isForeignKey());
       assertNotNull(t3t1.getReference());
       assertFalse(t3t1.getReference().isPrimaryTable());
+      assertTrue(t3t1.getReference().isCascadeUpdates());
+      assertFalse(t3t1.getReference().isCascadeDeletes());
       doCheckForeignKeyIndex(t1, t3t1, t3);
       
       Index t1pk = t1.getIndex(IndexBuilder.PRIMARY_KEY_NAME);
@@ -506,6 +506,7 @@ public class IndexTest extends TestCase {
 
     assertNotNull(ib.getReference());
     assertSame(ia, ib.getReferencedIndex());
+    assertTrue(ib.getReference().isPrimaryTable());
   }
 
   private void checkIndexColumns(Table table, String... idxInfo)
@@ -528,16 +529,4 @@ public class IndexTest extends TestCase {
     }
   }
 
-  static String getRelationshipName(JetFormat format, String name)
-  {
-    if(format == JetFormat.VERSION_3) {
-      if(name.equals("Table2Table1")) {
-        return "{150B6687-5C64-4DC0-B934-A8CF92FF73FF}";
-      }
-      if(name.equals("Table3Table1")) {
-        return "{D039E343-97A1-471F-B2E3-8DFCF1EEC597}";
-      }
-    }
-    return name;
-  }
 }
diff --git a/test/src/java/com/healthmarketscience/jackcess/JoinerTest.java b/test/src/java/com/healthmarketscience/jackcess/JoinerTest.java
new file mode 100644
index 0000000..1f2961c
--- /dev/null
+++ b/test/src/java/com/healthmarketscience/jackcess/JoinerTest.java
@@ -0,0 +1,181 @@
+/*
+Copyright (c) 2011 James Ahlborn
+
+This library is free software; you can redistribute it and/or
+modify it under the terms of the GNU Lesser General Public
+License as published by the Free Software Foundation; either
+version 2.1 of the License, or (at your option) any later version.
+
+This library is distributed in the hope that it will be useful,
+but WITHOUT ANY WARRANTY; without even the implied warranty of
+MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+Lesser General Public License for more details.
+
+You should have received a copy of the GNU Lesser General Public
+License along with this library; if not, write to the Free Software
+Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307
+USA
+*/
+
+package com.healthmarketscience.jackcess;
+
+import java.util.ArrayList;
+import java.util.Arrays;
+import java.util.HashMap;
+import java.util.HashSet;
+import java.util.List;
+import java.util.Map;
+import java.util.Set;
+
+import static com.healthmarketscience.jackcess.DatabaseTest.*;
+import static com.healthmarketscience.jackcess.JetFormatTest.*;
+import junit.framework.TestCase;
+
+/**
+ *
+ * @author James Ahlborn
+ */
+public class JoinerTest extends TestCase {
+
+  public JoinerTest(String name) {
+    super(name);
+  }
+
+  public void testJoiner() throws Exception
+  {
+    for (final TestDB testDB : TestDB.getSupportedForBasename(Basename.INDEX)) {
+
+      Database db = open(testDB);
+      Table t1 = db.getTable("Table1");
+      Table t2 = db.getTable("Table2");
+      Table t3 = db.getTable("Table3");
+
+      Index t1t2 = t1.getIndex("Table2Table1");
+      Index t1t3 = t1.getIndex("Table3Table1");
+
+      Index t2t1 = t1t2.getReferencedIndex();
+      assertSame(t2, t2t1.getTable());
+      Joiner t2t1Join = Joiner.create(t2t1);
+
+      assertSame(t2, t2t1Join.getFromTable());
+      assertSame(t2t1, t2t1Join.getFromIndex());
+      assertSame(t1, t2t1Join.getToTable());
+      assertSame(t1t2, t2t1Join.getToIndex());
+      
+      doTestJoiner(t2t1Join, createT2T1Data());
+      
+      Index t3t1 = t1t3.getReferencedIndex();
+      assertSame(t3, t3t1.getTable());
+      Joiner t3t1Join = Joiner.create(t3t1);
+
+      assertSame(t3, t3t1Join.getFromTable());
+      assertSame(t3t1, t3t1Join.getFromIndex());
+      assertSame(t1, t3t1Join.getToTable());
+      assertSame(t1t3, t3t1Join.getToIndex());
+      
+      doTestJoiner(t3t1Join, createT3T1Data());      
+    }    
+  }
+
+  private void doTestJoiner(Joiner join,
+                            Map<Integer,List<Map<String,Object>>> expectedData)
+    throws Exception
+  {
+    final Set<String> colNames = new HashSet<String>(
+        Arrays.asList("id", "data"));
+    
+    for(Map<String,Object> row : join.getFromTable()) {
+      Integer id = (Integer)row.get("id");
+
+      List<Map<String,Object>> joinedRows =
+        new ArrayList<Map<String,Object>>();
+      for(Map<String,Object> t1Row : join.findRowsIterable(row)) {
+        joinedRows.add(t1Row);
+      }
+
+      List<Map<String,Object>> expectedRows = expectedData.get(id);
+      assertEquals(expectedData.get(id), joinedRows);
+
+      if(!expectedRows.isEmpty()) {
+        assertEquals(expectedRows.get(0), join.findFirstRow(row));
+      } else {
+        assertNull(join.findFirstRow(row));
+      }
+      
+      List<Map<String,Object>> expectedRows2 = new
+        ArrayList<Map<String,Object>>();
+      for(Map<String,Object> tmpRow : expectedRows) {
+        Map<String,Object> tmpRow2 = new HashMap<String,Object>(tmpRow);
+        tmpRow2.keySet().retainAll(colNames);
+        expectedRows2.add(tmpRow2);
+      }
+      
+      joinedRows = new ArrayList<Map<String,Object>>();
+      for(Map<String,Object> t1Row : join.findRowsIterable(row, colNames)) {
+        joinedRows.add(t1Row);
+      }
+
+      assertEquals(expectedRows2, joinedRows);
+
+      if(!expectedRows2.isEmpty()) {
+        assertEquals(expectedRows2.get(0), join.findFirstRow(row, colNames));
+      } else {
+        assertNull(join.findFirstRow(row, colNames));
+      }      
+    }
+  }
+
+  private static Map<Integer,List<Map<String,Object>>> createT2T1Data()
+  {
+    Map<Integer,List<Map<String,Object>>> data = new
+      HashMap<Integer,List<Map<String,Object>>>();
+
+    data.put(0,
+             createExpectedTable(
+                 createExpectedRow("id", 0, "otherfk1", 0, "otherfk2", 10,
+                                   "data", "baz0", "otherfk3", 0)));
+
+    data.put(1,
+             createExpectedTable(
+                 createExpectedRow("id", 1, "otherfk1", 1, "otherfk2", 11,
+                                   "data", "baz11", "otherfk3", 0),
+                 createExpectedRow("id", 2, "otherfk1", 1, "otherfk2", 11,
+                                   "data", "baz11-2", "otherfk3", 0)));
+
+    data.put(2,
+             createExpectedTable(
+                 createExpectedRow("id", 3, "otherfk1", 2, "otherfk2", 13,
+                                   "data", "baz13", "otherfk3", 0)));
+
+    return data;
+  }
+  
+  private static Map<Integer,List<Map<String,Object>>> createT3T1Data()
+  {
+    Map<Integer,List<Map<String,Object>>> data = new
+      HashMap<Integer,List<Map<String,Object>>>();
+
+    data.put(10,
+             createExpectedTable(
+                 createExpectedRow("id", 0, "otherfk1", 0, "otherfk2", 10,
+                                   "data", "baz0", "otherfk3", 0)));
+
+    data.put(11,
+             createExpectedTable(
+                 createExpectedRow("id", 1, "otherfk1", 1, "otherfk2", 11,
+                                   "data", "baz11", "otherfk3", 0),
+                 createExpectedRow("id", 2, "otherfk1", 1, "otherfk2", 11,
+                                   "data", "baz11-2", "otherfk3", 0)));
+
+    data.put(12,
+             createExpectedTable());
+
+    data.put(13,
+             createExpectedTable(
+                 createExpectedRow("id", 3, "otherfk1", 2, "otherfk2", 13,
+                                   "data", "baz13", "otherfk3", 0)));
+
+    return data;
+  }
+  
+}
diff --git a/test/src/java/com/healthmarketscience/jackcess/RelationshipTest.java b/test/src/java/com/healthmarketscience/jackcess/RelationshipTest.java
index 81c1fb0..4f3175f 100644
--- a/test/src/java/com/healthmarketscience/jackcess/RelationshipTest.java
+++ b/test/src/java/com/healthmarketscience/jackcess/RelationshipTest.java
@@ -54,9 +54,7 @@ public class RelationshipTest extends TestCase {
       List<Relationship> rels = db.getRelationships(t1, t2);
       assertEquals(1, rels.size());
       Relationship rel = rels.get(0);
-      assertEquals(IndexTest.getRelationshipName(testDB.getExpectedFormat(),
-                                                 "Table2Table1"),
-                   rel.getName());
+      assertEquals("Table2Table1", rel.getName());
       assertEquals(t2, rel.getFromTable());
       assertEquals(Arrays.asList(t2.getColumn("id")),
                    rel.getFromColumns());
@@ -64,7 +62,8 @@ public class RelationshipTest extends TestCase {
       assertEquals(Arrays.asList(t1.getColumn("otherfk1")),
                    rel.getToColumns());
       assertTrue(rel.hasReferentialIntegrity());
-      assertEquals(0, rel.getFlags());
+      assertEquals(4096, rel.getFlags());
+      assertTrue(rel.cascadeDeletes());
       assertSameRelationships(rels, db.getRelationships(t2, t1));
 
       rels = db.getRelationships(t2, t3);
@@ -74,9 +73,7 @@ public class RelationshipTest extends TestCase {
       rels = db.getRelationships(t1, t3);
       assertEquals(1, rels.size());
       rel = rels.get(0);
-      assertEquals(IndexTest.getRelationshipName(testDB.getExpectedFormat(),
-                                                 "Table3Table1"),
-                   rel.getName());
+      assertEquals("Table3Table1", rel.getName());
       assertEquals(t3, rel.getFromTable());
       assertEquals(Arrays.asList(t3.getColumn("id")),
                    rel.getFromColumns());
@@ -84,7 +81,8 @@ public class RelationshipTest extends TestCase {
       assertEquals(Arrays.asList(t1.getColumn("otherfk2")),
                    rel.getToColumns());
       assertTrue(rel.hasReferentialIntegrity());
-      assertEquals(0, rel.getFlags());
+      assertEquals(256, rel.getFlags());
+      assertTrue(rel.cascadeUpdates());
       assertSameRelationships(rels, db.getRelationships(t3, t1));
 
       try {