From 792d46d8ea9c3b8ba46c44f5c8748ebacdac70e2 Mon Sep 17 00:00:00 2001 From: James Ahlborn Date: Tue, 20 Nov 2012 04:54:19 +0000 Subject: [PATCH] initial support for optionally enforcing foreign-key constraints (fixes feature request #22) git-svn-id: https://svn.code.sf.net/p/jackcess/code/jackcess/trunk@655 f203690c-595d-4dc9-a70b-905162fa7fd2 --- src/changes/changes.xml | 6 + .../jackcess/Database.java | 56 +++- .../jackcess/FKEnforcer.java | 314 ++++++++++++++++++ .../jackcess/IndexData.java | 3 +- .../healthmarketscience/jackcess/Joiner.java | 122 ++++++- .../healthmarketscience/jackcess/Table.java | 144 +++++--- .../jackcess/FKEnforcerTest.java | 138 ++++++++ 7 files changed, 708 insertions(+), 75 deletions(-) create mode 100644 src/java/com/healthmarketscience/jackcess/FKEnforcer.java create mode 100644 test/src/java/com/healthmarketscience/jackcess/FKEnforcerTest.java diff --git a/src/changes/changes.xml b/src/changes/changes.xml index 6e241d2..addd5d1 100644 --- a/src/changes/changes.xml +++ b/src/changes/changes.xml @@ -8,6 +8,12 @@ Add info to the Column to support MEMO columns which are HYPERLINKS. + + Add optional support for enforcing foreign-key constraints/cascading. + This is disabled by default (for backwards compatibility), but can be + controlled globally via a system property and/or on a per-Database + basis using setEnforceForeignKeys() method. + diff --git a/src/java/com/healthmarketscience/jackcess/Database.java b/src/java/com/healthmarketscience/jackcess/Database.java index 5f3505a..d9fe435 100644 --- a/src/java/com/healthmarketscience/jackcess/Database.java +++ b/src/java/com/healthmarketscience/jackcess/Database.java @@ -164,6 +164,13 @@ public class Database public static final String COLUMN_ORDER_PROPERTY = "com.healthmarketscience.jackcess.columnOrder"; + /** system property which can be used to set the default enforcement of + * foreign-key relationships. Defaults to {@code false}. + * @usage _general_field_ + */ + public static final String FK_ENFORCE_PROPERTY = + "com.healthmarketscience.jackcess.enforceForeignKeys"; + /** * default error handler used if none provided (just rethrows exception) * @usage _general_field_ @@ -462,6 +469,8 @@ public class Database private Short _defaultCodePage; /** the ordering used for table columns */ private Table.ColumnOrder _columnOrder; + /** whether or not enforcement of foreign-keys is enabled */ + private boolean _enforceForeignKeys; /** cache of in-use tables */ private final TableCache _tableCache = new TableCache(); /** handler for reading/writing properteies */ @@ -478,7 +487,9 @@ public class Database private LinkResolver _linkResolver; /** any linked databases which have been opened */ private Map _linkedDbs; - + /** shared state used when enforcing foreign keys */ + private final FKEnforcer.SharedState _fkEnforcerSharedState = + FKEnforcer.initSharedState(); /** * Open an existing Database. If the existing file is not writeable, the @@ -894,6 +905,7 @@ public class Database _format = JetFormat.getFormat(channel); _charset = ((charset == null) ? getDefaultCharset(_format) : charset); _columnOrder = getDefaultColumnOrder(); + _enforceForeignKeys = getDefaultEnforceForeignKeys(); _fileFormat = fileFormat; _pageChannel = new PageChannel(channel, closeChannel, _format, autoSync); _timeZone = ((timeZone == null) ? getDefaultTimeZone() : timeZone); @@ -1095,6 +1107,33 @@ public class Database _columnOrder = newColumnOrder; } + /** + * Gets currently foreign-key enforcement policy. + * @usage _intermediate_method_ + */ + public boolean isEnforceForeignKeys() { + return _enforceForeignKeys; + } + + /** + * Sets a new foreign-key enforcement policy. If {@code null}, resets to + * the value returned by {@link #isEnforceForeignKeys}. + * @usage _intermediate_method_ + */ + public void setEnforceForeignKeys(Boolean newEnforceForeignKeys) { + if(newEnforceForeignKeys == null) { + newEnforceForeignKeys = getDefaultEnforceForeignKeys(); + } + _enforceForeignKeys = newEnforceForeignKeys; + } + + /** + * @usage _advanced_method_ + */ + FKEnforcer.SharedState getFKEnforcerSharedState() { + return _fkEnforcerSharedState; + } + /** * @returns the current handler for reading/writing properties, creating if * necessary @@ -2220,6 +2259,21 @@ public class Database return DEFAULT_COLUMN_ORDER; } + /** + * Returns the default enforce foreign-keys policy. This defaults to + * {@code false}, but can be overridden using the system + * property {@value #FK_ENFORCE_PROPERTY}. + * @usage _advanced_method_ + */ + public static boolean getDefaultEnforceForeignKeys() + { + String prop = System.getProperty(FK_ENFORCE_PROPERTY); + if(prop != null) { + return Boolean.TRUE.toString().equalsIgnoreCase(prop); + } + return false; + } + /** * Copies the given InputStream to the given channel using the most * efficient means possible. diff --git a/src/java/com/healthmarketscience/jackcess/FKEnforcer.java b/src/java/com/healthmarketscience/jackcess/FKEnforcer.java new file mode 100644 index 0000000..b5ce3ec --- /dev/null +++ b/src/java/com/healthmarketscience/jackcess/FKEnforcer.java @@ -0,0 +1,314 @@ +/* +Copyright (c) 2012 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.ArrayList; +import java.util.Arrays; +import java.util.Collections; +import java.util.Iterator; +import java.util.List; +import java.util.Map; +import java.util.Set; +import java.util.TreeSet; + + +/** + * Utility class used by Table to enforce foreign-key relationships (if + * enabled). + * + * @author James Ahlborn + * @usage _advanced_class_ + */ +final class FKEnforcer +{ + // fk constraints always work with indexes, which are always + // case-insensitive + private static final ColumnMatcher MATCHER = + CaseInsensitiveColumnMatcher.INSTANCE; + + private final Table _table; + private final List _cols; + private List _primaryJoinersChkUp; + private List _primaryJoinersChkDel; + private List _primaryJoinersDoUp; + private List _primaryJoinersDoDel; + private List _secondaryJoiners; + + FKEnforcer(Table table) { + _table = table; + + // at this point, only init the index columns + Set cols = new TreeSet(); + for(Index idx : _table.getIndexes()) { + Index.ForeignKeyReference ref = idx.getReference(); + if(ref != null) { + // compile an ordered list of all columns in this table which are + // involved in foreign key relationships with other tables + for(IndexData.ColumnDescriptor iCol : idx.getColumns()) { + cols.add(iCol.getColumn()); + } + } + } + _cols = !cols.isEmpty() ? + Collections.unmodifiableList(new ArrayList(cols)) : + Collections.emptyList(); + } + + /** + * Does secondary initialization, if necessary. + */ + private void initialize() throws IOException { + if(_secondaryJoiners != null) { + // already initialized + return; + } + + // initialize all the joiners + _primaryJoinersChkUp = new ArrayList(1); + _primaryJoinersChkDel = new ArrayList(1); + _primaryJoinersDoUp = new ArrayList(1); + _primaryJoinersDoDel = new ArrayList(1); + _secondaryJoiners = new ArrayList(1); + + for(Index idx : _table.getIndexes()) { + Index.ForeignKeyReference ref = idx.getReference(); + if(ref != null) { + + Joiner joiner = Joiner.create(idx); + if(ref.isPrimaryTable()) { + if(ref.isCascadeUpdates()) { + _primaryJoinersDoUp.add(joiner); + } else { + _primaryJoinersChkUp.add(joiner); + } + if(ref.isCascadeDeletes()) { + _primaryJoinersDoDel.add(joiner); + } else { + _primaryJoinersChkDel.add(joiner); + } + } else { + _secondaryJoiners.add(joiner); + } + } + } + } + + /** + * Handles foregn-key constraints when adding a row. + * + * @param row new row in the Table's row format, including all values used + * in any foreign-key relationships + */ + public void addRow(Object[] row) throws IOException { + if(!enforcing()) { + return; + } + initialize(); + + for(Joiner joiner : _secondaryJoiners) { + requirePrimaryValues(joiner, row); + } + } + + /** + * Handles foregn-key constraints when updating a row. + * + * @param oldRow old row in the Table's row format, including all values + * used in any foreign-key relationships + * @param newRow new row in the Table's row format, including all values + * used in any foreign-key relationships + */ + public void updateRow(Object[] oldRow, Object[] newRow) throws IOException { + if(!enforcing()) { + return; + } + + if(!anyUpdates(oldRow, newRow)) { + // no changes were made to any relevant columns + return; + } + + initialize(); + + SharedState ss = _table.getDatabase().getFKEnforcerSharedState(); + + if(ss.isUpdating()) { + // we only check the primary relationships for the "top-level" of an + // update operation. in nested levels we are only ever changing the fk + // values themselves, so we always know the new values are valid. + for(Joiner joiner : _secondaryJoiners) { + if(anyUpdates(joiner, oldRow, newRow)) { + requirePrimaryValues(joiner, newRow); + } + } + } + + ss.pushUpdate(); + try { + + // now, check the tables for which we are the primary table in the + // relationship (but not cascading) + for(Joiner joiner : _primaryJoinersChkUp) { + if(anyUpdates(joiner, oldRow, newRow)) { + requireNoSecondaryValues(joiner, oldRow); + } + } + + // lastly, update the tables for which we are the primary table in the + // relationship + for(Joiner joiner : _primaryJoinersDoUp) { + if(anyUpdates(joiner, oldRow, newRow)) { + updateSecondaryValues(joiner, oldRow, newRow); + } + } + + } finally { + ss.popUpdate(); + } + } + + /** + * Handles foregn-key constraints when deleting a row. + * + * @param row old row in the Table's row format, including all values used + * in any foreign-key relationships + */ + public void deleteRow(Object[] row) throws IOException { + if(!enforcing()) { + return; + } + initialize(); + + // first, check the tables for which we are the primary table in the + // relationship (but not cascading) + for(Joiner joiner : _primaryJoinersChkDel) { + requireNoSecondaryValues(joiner, row); + } + + // lastly, delete from the tables for which we are the primary table in + // the relationship + for(Joiner joiner : _primaryJoinersDoDel) { + joiner.deleteRows(row); + } + } + + private static void requirePrimaryValues(Joiner joiner, Object[] row) + throws IOException + { + // ensure that the relevant rows exist in the primary tables for which + // this table is a secondary table. + if(!joiner.hasRows(row)) { + throw new IOException("Adding new row " + Arrays.asList(row) + + " violates constraint " + joiner.toFKString()); + } + } + + private static void requireNoSecondaryValues(Joiner joiner, Object[] row) + throws IOException + { + // ensure that no rows exist in the secondary table for which this table is + // the primary table. + if(joiner.hasRows(row)) { + throw new IOException("Removing old row " + Arrays.asList(row) + + " violates constraint " + joiner.toFKString()); + } + } + + private static void updateSecondaryValues(Joiner joiner, Object[] oldFromRow, + Object[] newFromRow) + throws IOException + { + IndexCursor toCursor = joiner.getToCursor(); + List fromCols = joiner.getColumns(); + List toCols = joiner.getToIndex().getColumns(); + Object[] toRow = new Object[joiner.getToTable().getColumnCount()]; + + for(Iterator> iter = joiner.findRows( + oldFromRow, Collections.emptySet()); iter.hasNext(); ) { + iter.next(); + + // create update row for "to" table + Arrays.fill(toRow, Column.KEEP_VALUE); + for(int i = 0; i < fromCols.size(); ++i) { + Object val = fromCols.get(i).getColumn().getRowValue(newFromRow); + toCols.get(i).getColumn().setRowValue(toRow, val); + } + + toCursor.updateCurrentRow(toRow); + } + } + + private boolean anyUpdates(Object[] oldRow, Object[] newRow) { + for(Column col : _cols) { + if(!MATCHER.matches(_table, col.getName(), + col.getRowValue(oldRow), col.getRowValue(newRow))) { + return true; + } + } + return false; + } + + private static boolean anyUpdates(Joiner joiner,Object[] oldRow, + Object[] newRow) + { + Table fromTable = joiner.getFromTable(); + for(IndexData.ColumnDescriptor iCol : joiner.getColumns()) { + Column col = iCol.getColumn(); + if(!MATCHER.matches(fromTable, col.getName(), + col.getRowValue(oldRow), col.getRowValue(newRow))) { + return true; + } + } + return false; + } + + private boolean enforcing() { + return _table.getDatabase().isEnforceForeignKeys(); + } + + static SharedState initSharedState() { + return new SharedState(); + } + + /** + * Shared state used by all FKEnforcers for a given Database. + */ + static final class SharedState + { + /** current depth of cascading update calls across one or more tables */ + private int _updateDepth; + + private SharedState() { + } + + public boolean isUpdating() { + return (_updateDepth == 0); + } + + public void pushUpdate() { + ++_updateDepth; + } + + public void popUpdate() { + --_updateDepth; + } + } +} diff --git a/src/java/com/healthmarketscience/jackcess/IndexData.java b/src/java/com/healthmarketscience/jackcess/IndexData.java index c51a016..2c24a2d 100644 --- a/src/java/com/healthmarketscience/jackcess/IndexData.java +++ b/src/java/com/healthmarketscience/jackcess/IndexData.java @@ -566,8 +566,7 @@ public abstract class IndexData { newEntry.equalsEntryBytes(nextPos.getEntry())) || ((prevPos != null) && newEntry.equalsEntryBytes(prevPos.getEntry()))); - if(isUnique() && !isNullEntry && isDupeEntry) - { + if(isUnique() && !isNullEntry && isDupeEntry) { throw new IOException( "New row " + Arrays.asList(row) + " violates uniqueness constraint for index " + this); diff --git a/src/java/com/healthmarketscience/jackcess/Joiner.java b/src/java/com/healthmarketscience/jackcess/Joiner.java index 8e5bf7c..dc3f4ba 100644 --- a/src/java/com/healthmarketscience/jackcess/Joiner.java +++ b/src/java/com/healthmarketscience/jackcess/Joiner.java @@ -90,31 +90,31 @@ public class Joiner return create(getToTable(), getFromTable()); } - public Table getFromTable() - { + public Table getFromTable() { return getFromIndex().getTable(); } - public Index getFromIndex() - { + public Index getFromIndex() { return _fromIndex; } - public Table getToTable() - { + public Table getToTable() { return getToCursor().getTable(); } - public Index getToIndex() - { + public Index getToIndex() { return getToCursor().getIndex(); } - public IndexCursor getToCursor() - { + public IndexCursor getToCursor() { return _toCursor; } + public List getColumns() { + // note, this list is already unmodifiable, no need to re-wrap + return _fromCols; + } + /** * Returns {@code true} if the "to" table has any rows based on the given * columns in the "from" table, {@code false} otherwise. @@ -124,6 +124,15 @@ public class Joiner return _toCursor.findFirstRowByEntry(_entryValues); } + /** + * Returns {@code true} if the "to" table has any rows based on the given + * columns in the "from" table, {@code false} otherwise. + */ + boolean hasRows(Object[] fromRow) throws IOException { + toEntryValues(fromRow); + return _toCursor.findFirstRowByEntry(_entryValues); + } + /** * 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. @@ -180,6 +189,21 @@ public class Joiner return _toCursor.entryIterator(columnNames, _entryValues); } + /** + * 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 + */ + Iterator> findRows(Object[] fromRow, + Collection columnNames) + { + toEntryValues(fromRow); + return _toCursor.entryIterator(columnNames, _entryValues); + } + /** * Returns an Iterable whose iterator() method returns the result of a call * to {@link #findRows(Map)} @@ -224,25 +248,89 @@ public class Joiner * otherwise */ public boolean deleteRows(Map fromRow) throws IOException { + return deleteRowsImpl(findRows(fromRow, Collections.emptySet())); + } + + /** + * Deletes any 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) + * @return {@code true} if any "to" rows were deleted, {@code false} + * otherwise + */ + boolean deleteRows(Object[] fromRow) throws IOException { + return deleteRowsImpl(findRows(fromRow, Collections.emptySet())); + } + + /** + * Deletes all the rows and returns whether or not any "to"" rows were + * deleted. + */ + private static boolean deleteRowsImpl(Iterator> iter) + throws IOException + { boolean removed = false; - for(Iterator> iter = findRows( - fromRow, Collections.emptySet()); iter.hasNext(); ) { + while(iter.hasNext()) { iter.next(); iter.remove(); removed = true; } - return removed; + return removed; } /** * Fills in the _entryValues with the relevant info from the given "from" * table row. */ - private void toEntryValues(Map fromRow) - { + private void toEntryValues(Map fromRow) { for(int i = 0; i < _entryValues.length; ++i) { - _entryValues[i] = fromRow.get(_fromCols.get(i).getName()); + _entryValues[i] = _fromCols.get(i).getColumn().getRowValue(fromRow); } } - + + /** + * Fills in the _entryValues with the relevant info from the given "from" + * table row. + */ + private void toEntryValues(Object[] fromRow) { + for(int i = 0; i < _entryValues.length; ++i) { + _entryValues[i] = _fromCols.get(i).getColumn().getRowValue(fromRow); + } + } + + /** + * Returns a pretty string describing the foreign key relationship backing + * this Joiner. + */ + public String toFKString() { + StringBuilder sb = new StringBuilder(); + sb.append("Foreign Key from "); + + String fromType = "] (primary)"; + String toType = "] (secondary)"; + if(!_fromIndex.getReference().isPrimaryTable()) { + fromType = "] (secondary)"; + toType = "] (primary)"; + } + + sb.append(getFromTable().getName()).append("["); + + sb.append(_fromCols.get(0).getName()); + for(int i = 1; i < _fromCols.size(); ++i) { + sb.append(",").append(_fromCols.get(i).getName()); + } + sb.append(fromType); + + sb.append(" to ").append(getToTable().getName()).append("["); + List toCols = _toCursor.getIndex().getColumns(); + sb.append(toCols.get(0).getName()); + for(int i = 1; i < toCols.size(); ++i) { + sb.append(",").append(toCols.get(i).getName()); + } + sb.append(toType); + + return sb.toString(); + } } diff --git a/src/java/com/healthmarketscience/jackcess/Table.java b/src/java/com/healthmarketscience/jackcess/Table.java index 6a6b807..67b31f9 100644 --- a/src/java/com/healthmarketscience/jackcess/Table.java +++ b/src/java/com/healthmarketscience/jackcess/Table.java @@ -38,8 +38,10 @@ import java.util.Comparator; import java.util.HashMap; import java.util.Iterator; import java.util.LinkedHashMap; +import java.util.LinkedHashSet; import java.util.List; import java.util.Map; +import java.util.Set; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; @@ -138,15 +140,17 @@ public class Table /** List of columns in this table, ordered by column number */ private List _columns = new ArrayList(); /** List of variable length columns in this table, ordered by offset */ - private List _varColumns = new ArrayList(); + private final List _varColumns = new ArrayList(); /** List of autonumber columns in this table, ordered by column number */ private List _autoNumColumns; /** List of indexes on this table (multiple logical indexes may be backed by the same index data) */ - private List _indexes = new ArrayList(); + private final List _indexes = new ArrayList(); /** List of index datas on this table (the actual backing data for an index) */ - private List _indexDatas = new ArrayList(); + private final List _indexDatas = new ArrayList(); + /** List of columns in this table which are in one or more indexes */ + private final Set _indexColumns = new LinkedHashSet(); /** Table name as stored in Database */ private final String _name; /** Usage map of pages that this table owns */ @@ -179,6 +183,8 @@ public class Table private PropertyMap _props; /** properties group for this table (and columns) */ private PropertyMaps _propertyMaps; + /** foreign-key enforcer for this table */ + private final FKEnforcer _fkEnforcer; /** common cursor for iterating through the table, kept here for historic reasons */ @@ -197,6 +203,7 @@ public class Table _name = null; _useBigIndex = true; setColumns(columns); + _fkEnforcer = null; } /** @@ -216,23 +223,8 @@ public class Table _name = name; _flags = flags; _useBigIndex = useBigIndex; - int nextPage = tableBuffer.getInt(getFormat().OFFSET_NEXT_TABLE_DEF_PAGE); - ByteBuffer nextPageBuffer = null; - while (nextPage != 0) { - if (nextPageBuffer == null) { - nextPageBuffer = getPageChannel().createPageBuffer(); - } - getPageChannel().readPage(nextPageBuffer, nextPage); - nextPage = nextPageBuffer.getInt(getFormat().OFFSET_NEXT_TABLE_DEF_PAGE); - ByteBuffer newBuffer = getPageChannel().createBuffer( - tableBuffer.capacity() + getFormat().PAGE_SIZE - 8); - newBuffer.put(tableBuffer); - newBuffer.put(nextPageBuffer.array(), 8, getFormat().PAGE_SIZE - 8); - tableBuffer = newBuffer; - tableBuffer.flip(); - } - readTableDefinition(tableBuffer); - tableBuffer = null; + readTableDefinition(loadCompleteTableDefinitionBuffer(tableBuffer)); + _fkEnforcer = new FKEnforcer(this); } /** @@ -542,10 +534,28 @@ public class Table int pageNumber = rowState.getHeaderRowId().getPageNumber(); int rowNumber = rowState.getHeaderRowId().getRowNumber(); - // use any read rowValues to help update the indexes - Object[] rowValues = (!_indexDatas.isEmpty() ? - rowState.getRowValues() : null); - + // attempt to fill in index column values + Object[] rowValues = null; + if(!_indexDatas.isEmpty()) { + + // move to row data to get index values + rowBuffer = positionAtRowData(rowState, rowId); + + for(Column idxCol : _indexColumns) { + getRowColumn(getFormat(), rowBuffer, idxCol, rowState, null); + } + + // use any read rowValues to help update the indexes + rowValues = rowState.getRowValues(); + + // check foreign keys before proceeding w/ deletion + _fkEnforcer.deleteRow(rowValues); + + // move back to the header + rowBuffer = positionAtRowHeader(rowState, rowId); + } + + // finally, pull the trigger int rowIndex = getRowStartOffset(rowNumber, getFormat()); rowBuffer.putShort(rowIndex, (short)(rowBuffer.getShort(rowIndex) | DELETED_ROW_MASK | OVERFLOW_ROW_MASK)); @@ -1197,6 +1207,31 @@ public class Table pageChannel.writePage(rtn, umapPageNumber); } + + /** + * Returns a single ByteBuffer which contains the entire table definition + * (which may span multiple database pages). + */ + private ByteBuffer loadCompleteTableDefinitionBuffer(ByteBuffer tableBuffer) + throws IOException + { + int nextPage = tableBuffer.getInt(getFormat().OFFSET_NEXT_TABLE_DEF_PAGE); + ByteBuffer nextPageBuffer = null; + while (nextPage != 0) { + if (nextPageBuffer == null) { + nextPageBuffer = getPageChannel().createPageBuffer(); + } + getPageChannel().readPage(nextPageBuffer, nextPage); + nextPage = nextPageBuffer.getInt(getFormat().OFFSET_NEXT_TABLE_DEF_PAGE); + ByteBuffer newBuffer = getPageChannel().createBuffer( + tableBuffer.capacity() + getFormat().PAGE_SIZE - 8); + newBuffer.put(tableBuffer); + newBuffer.put(nextPageBuffer.array(), 8, getFormat().PAGE_SIZE - 8); + tableBuffer = newBuffer; + tableBuffer.flip(); + } + return tableBuffer; + } /** * Read the table definition @@ -1268,7 +1303,12 @@ public class Table // read index column information for (int i = 0; i < _indexCount; i++) { - _indexDatas.get(i).read(tableBuffer, _columns); + IndexData idxData = _indexDatas.get(i); + idxData.read(tableBuffer, _columns); + // keep track of all columns involved in indexes + for(IndexData.ColumnDescriptor iCol : idxData.getColumns()) { + _indexColumns.add(iCol.getColumn()); + } } // read logical index info (may be more logical indexes than index datas) @@ -1462,6 +1502,10 @@ public class Table for (int i = 0; i < rowData.length; i++) { int rowSize = rowData[i].remaining(); + Object[] row = rows.get(i); + + // handle foreign keys before adding to table + _fkEnforcer.addRow(row); // get page with space dataPage = findFreeRowSpace(rowSize, dataPage, pageNumber); @@ -1474,7 +1518,7 @@ public class Table // update the indexes RowId rowId = new RowId(pageNumber, rowNum); for(IndexData indexData : _indexDatas) { - indexData.addRow(rows.get(i), rowId); + indexData.addRow(row, rowId); } } @@ -1523,29 +1567,32 @@ public class Table row = dupeRow(row, _columns.size()); } - // fill in any auto-numbers (we don't allow autonumber values to be - // modified) - handleAutoNumbersForUpdate(row, rowBuffer, rowState); - // hang on to the raw values of var length columns we are "keeping". this // will allow us to re-use pre-written var length data, which can save // space for things like long value columns. - Map rawVarValues = + Map keepRawVarValues = (!_varColumns.isEmpty() ? new HashMap() : null); - // fill in any "keep value" fields for(Column column : _columns) { - if(column.getRowValue(row) == Column.KEEP_VALUE) { - column.setRowValue( - row, getRowColumn(getFormat(), rowBuffer, column, rowState, - rawVarValues)); + if(_autoNumColumns.contains(column)) { + // fill in any auto-numbers (we don't allow autonumber values to be + // modified) + column.setRowValue(row, getRowColumn(getFormat(), rowBuffer, column, + rowState, null)); + } else if(column.getRowValue(row) == Column.KEEP_VALUE) { + // fill in any "keep value" fields + column.setRowValue(row, getRowColumn(getFormat(), rowBuffer, column, + rowState, keepRawVarValues)); + } else if(_indexColumns.contains(column)) { + // read row value to help update indexes + getRowColumn(getFormat(), rowBuffer, column, rowState, null); } } // generate new row bytes ByteBuffer newRowData = createRow( row, _singleRowBufferH.getPageBuffer(getPageChannel()), oldRowSize, - rawVarValues); + keepRawVarValues); if (newRowData.limit() > getFormat().MAX_ROW_SIZE) { throw new IOException("Row size " + newRowData.limit() + @@ -1553,8 +1600,12 @@ public class Table } if(!_indexDatas.isEmpty()) { + Object[] oldRowValues = rowState.getRowValues(); + // check foreign keys before actually updating + _fkEnforcer.updateRow(oldRowValues, row); + // delete old values from indexes for(IndexData indexData : _indexDatas) { indexData.deleteRow(oldRowValues, rowId); @@ -1888,23 +1939,6 @@ public class Table return buffer; } - /** - * Autonumber columns may not be modified on update. - */ - private void handleAutoNumbersForUpdate( - Object[] row, ByteBuffer rowBuffer, RowState rowState) - throws IOException - { - if(_autoNumColumns.isEmpty()) { - return; - } - - for(Column col : _autoNumColumns) { - col.setRowValue(row, getRowColumn(getFormat(), rowBuffer, col, rowState, - null)); - } - } - /** * Fill in all autonumber column values. */ @@ -2196,7 +2230,7 @@ public class Table autoCols.add(c); } } - return autoCols; + return (!autoCols.isEmpty() ? autoCols : Collections.emptyList()); } /** diff --git a/test/src/java/com/healthmarketscience/jackcess/FKEnforcerTest.java b/test/src/java/com/healthmarketscience/jackcess/FKEnforcerTest.java new file mode 100644 index 0000000..9dd0c88 --- /dev/null +++ b/test/src/java/com/healthmarketscience/jackcess/FKEnforcerTest.java @@ -0,0 +1,138 @@ +/* +Copyright (c) 2012 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.Iterator; +import java.util.List; +import java.util.Map; + +import static com.healthmarketscience.jackcess.DatabaseTest.*; +import static com.healthmarketscience.jackcess.JetFormatTest.*; +import junit.framework.TestCase; + +/** + * + * @author James Ahlborn + */ +public class FKEnforcerTest extends TestCase +{ + + public FKEnforcerTest(String name) throws Exception { + super(name); + } + + public void testNoEnforceForeignKeys() throws Exception { + for (final TestDB testDB : TestDB.getSupportedForBasename(Basename.INDEX)) { + + Database db = openCopy(testDB); + Table t1 = db.getTable("Table1"); + Table t2 = db.getTable("Table2"); + Table t3 = db.getTable("Table3"); + + t1.addRow(20, 0, 20, "some data", 20); + + Cursor c = Cursor.createCursor(t2); + c.moveToNextRow(); + c.updateCurrentRow(30, "foo30"); + + c = Cursor.createCursor(t3); + c.moveToNextRow(); + c.deleteCurrentRow(); + + db.close(); + } + + } + + public void testEnforceForeignKeys() throws Exception { + for (final TestDB testDB : TestDB.getSupportedForBasename(Basename.INDEX)) { + + Database db = openCopy(testDB); + db.setEnforceForeignKeys(true); + Table t1 = db.getTable("Table1"); + Table t2 = db.getTable("Table2"); + Table t3 = db.getTable("Table3"); + + try { + t1.addRow(20, 0, 20, "some data", 20); + fail("IOException should have been thrown"); + } catch(IOException ignored) { + // success + assertTrue(ignored.getMessage().contains("Table1[otherfk2]")); + } + + try { + Cursor c = Cursor.createCursor(t2); + c.moveToNextRow(); + c.updateCurrentRow(30, "foo30"); + fail("IOException should have been thrown"); + } catch(IOException ignored) { + // success + assertTrue(ignored.getMessage().contains("Table2[id]")); + } + + try { + Cursor c = Cursor.createCursor(t3); + c.moveToNextRow(); + c.deleteCurrentRow(); + fail("IOException should have been thrown"); + } catch(IOException ignored) { + // success + assertTrue(ignored.getMessage().contains("Table3[id]")); + } + + Cursor c = Cursor.createCursor(t3); + Column col = t3.getColumn("id"); + for(Map row : c) { + int id = (Integer)row.get("id"); + id += 20; + c.setCurrentRowValue(col, id); + } + + List> expectedRows = + createExpectedTable( + createT1Row(0, 0, 30, "baz0", 0), + createT1Row(1, 1, 31, "baz11", 0), + createT1Row(2, 1, 31, "baz11-2", 0), + createT1Row(3, 2, 33, "baz13", 0)); + + assertTable(expectedRows, t1); + + c = Cursor.createCursor(t2); + for(Iterator iter = c.iterator(); iter.hasNext(); ) { + iter.next(); + iter.remove(); + } + + assertEquals(0, t1.getRowCount()); + + db.close(); + } + + } + + private static Map createT1Row( + int id1, int fk1, int fk2, String data, int fk3) + { + return createExpectedRow("id", id1, "otherfk1", fk1, "otherfk2", fk2, + "data", data, "otherfk3", fk3); + } +} -- 2.39.5