Examples with ForeignKeyViolationException herddb.model.ForeignKeyViolationException used on opensource projects

Example 1 with ForeignKeyViolationException

use of herddb.model.ForeignKeyViolationException in project herddb by diennea.

the class TableManager method checkForeignKeyConstraintsAsChildTable.

private void checkForeignKeyConstraintsAsChildTable(ForeignKeyDef fk, DataAccessor values, StatementEvaluationContext context, Transaction transaction) throws StatementExecutionException {
    // We are creating a SQL query and then using DBManager
    // using an SQL query will let us leverage the SQL Planner
    // and use the best index to perform the execution
    // the SQL Planner will cache the plan, and the plan will also be
    // invalidated consistently during DML operations.
    String query = childForeignKeyQueries.computeIfAbsent(fk.name, (l -> {
        Table parentTable = tableSpaceManager.getTableManagerByUUID(fk.parentTableId).getTable();
        // with '*' we are not going to perform projections or copies
        StringBuilder q = new StringBuilder("SELECT * FROM ");
        q.append(delimit(parentTable.tablespace));
        q.append(".");
        q.append(delimit(parentTable.name));
        q.append(" WHERE ");
        for (int i = 0; i < fk.parentTableColumns.length; i++) {
            if (i > 0) {
                q.append(" AND ");
            }
            q.append(delimit(fk.parentTableColumns[i]));
            q.append("=?");
        }
        return q.toString();
    }));
    final List<Object> valuesToMatch = new ArrayList<>(fk.columns.length);
    boolean allNulls = true;
    for (int i = 0; i < fk.columns.length; i++) {
        Object value = values.get(fk.columns[i]);
        allNulls = allNulls && value == null;
        valuesToMatch.add(value);
    }
    if (allNulls) {
        // all of the values are null, so no check on the parent table
        return;
    }
    TransactionContext tx = transaction != null ? new TransactionContext(transaction.transactionId) : TransactionContext.NO_TRANSACTION;
    boolean fkOk;
    try (DataScanner scan = tableSpaceManager.getDbmanager().executeSimpleQuery(tableSpaceManager.getTableSpaceName(), query, valuesToMatch, // only one record
    1, // keep read locks in TransactionContext
    true, tx, null)) {
        List<DataAccessor> resultSet = scan.consume();
        fkOk = !resultSet.isEmpty();
    } catch (DataScannerException err) {
        throw new StatementExecutionException(err);
    }
    if (!fkOk) {
        throw new ForeignKeyViolationException(fk.name, "foreignKey " + table.name + "." + fk.name + " violated");
    }
}

Example 2 with ForeignKeyViolationException

use of herddb.model.ForeignKeyViolationException in project herddb by diennea.

the class CreateTableTest method createTableWithForeignKeys.

@Test
public void createTableWithForeignKeys() throws Exception {
    String nodeId = "localhost";
    try (DBManager manager = new DBManager("localhost", new MemoryMetadataStorageManager(), new MemoryDataStorageManager(), new MemoryCommitLogManager(), null, null)) {
        manager.start();
        CreateTableSpaceStatement st1 = new CreateTableSpaceStatement("tblspace1", Collections.singleton(nodeId), nodeId, 1, 0, 0);
        manager.executeStatement(st1, StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
        manager.waitForTablespace("tblspace1", 10000);
        Table parentTable = Table.builder().tablespace("tblspace1").name("t1").column("id", ColumnTypes.STRING).column("name", ColumnTypes.STRING).primaryKey("id").build();
        Table childTable = Table.builder().tablespace("tblspace1").name("t2").column("id", ColumnTypes.STRING).column("name", ColumnTypes.STRING).column("parenttableid", ColumnTypes.NOTNULL_STRING).primaryKey("id").foreingKey(ForeignKeyDef.builder().name("myfk").onDeleteAction(ForeignKeyDef.ACTION_NO_ACTION).onUpdateAction(ForeignKeyDef.ACTION_NO_ACTION).column("parenttableid").parentTableId(parentTable.uuid).parentTableColumn("id").build()).build();
        CreateTableStatement st2 = new CreateTableStatement(parentTable);
        manager.executeStatement(st2, StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
        CreateTableStatement st3 = new CreateTableStatement(childTable);
        manager.executeStatement(st3, StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
        ForeignKeyViolationException err = expectThrows(ForeignKeyViolationException.class, () -> {
            execute(manager, "INSERT INTO tblspace1.t2(id,name,parentTableId) values('a','name','pvalue')", Collections.emptyList());
        });
        assertEquals("myfk", err.getForeignKeyName());
    }
}

Example 3 with ForeignKeyViolationException

use of herddb.model.ForeignKeyViolationException in project herddb by diennea.

the class ForeignKeySQLTest method alterTableCannotAddViolatedForeignKey.

@Test
public void alterTableCannotAddViolatedForeignKey() throws Exception {
    String nodeId = "localhost";
    try (DBManager manager = new DBManager("localhost", new MemoryMetadataStorageManager(), new MemoryDataStorageManager(), new MemoryCommitLogManager(), null, null)) {
        manager.start();
        CreateTableSpaceStatement st1 = new CreateTableSpaceStatement("tblspace1", Collections.singleton(nodeId), nodeId, 1, 0, 0);
        manager.executeStatement(st1, StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
        manager.waitForTablespace("tblspace1", 10000);
        execute(manager, "CREATE TABLE tblspace1.parent (k1 string primary key,n1 int,s1 string)", Collections.emptyList());
        execute(manager, "CREATE TABLE tblspace1.child (k2 string primary key,n2 int,s2 string)", Collections.emptyList());
        execute(manager, "INSERT INTO tblspace1.parent(k1,n1,s1) values('a',2,'pvalue')", Collections.emptyList(), TransactionContext.NO_TRANSACTION);
        // insert a record that could violates the new FK1
        execute(manager, "INSERT INTO tblspace1.child(k2,n2,s2) values('no',10,'a')", Collections.emptyList(), TransactionContext.NO_TRANSACTION);
        // add the FK, it must fail
        ForeignKeyViolationException err = expectThrows(ForeignKeyViolationException.class, () -> {
            execute(manager, "ALTER TABLE tblspace1.`CHILD` Add CONSTRAINT `fk1` FOREIGN KEY (s2,n2) REFERENCES parent(k1,n1) ON DELETE RESTRICT", Collections.emptyList());
        });
        assertEquals("fk1", err.getForeignKeyName());
        // check that the fk is not added
        Table parentTable = manager.getTableSpaceManager("tblspace1").getTableManager("parent").getTable();
        Table childTable = manager.getTableSpaceManager("tblspace1").getTableManager("child").getTable();
        assertNull(childTable.foreignKeys);
        // fix the record, in transaction
        long tx = beginTransaction(manager, "tblspace1");
        execute(manager, "UPDATE tblspace1.child set s2='a',n2=2", Collections.emptyList(), new TransactionContext(tx));
        assertNotNull(manager.getTableSpaceManager("tblspace1").getTransaction(tx));
        execute(manager, "ALTER TABLE tblspace1.`CHILD` Add CONSTRAINT `fk1` FOREIGN KEY (s2,n2) REFERENCES parent(k1,n1) ON DELETE RESTRICT", Collections.emptyList(), new TransactionContext(tx));
        // transaction is committed automatically after an ALTER TABLE operation
        assertNull(manager.getTableSpaceManager("tblspace1").getTransaction(tx));
        // check that the fk is not present
        childTable = manager.getTableSpaceManager("tblspace1").getTableManager("child").getTable();
        assertEquals(1, childTable.foreignKeys.length);
        assertEquals("fk1", childTable.foreignKeys[0].name);
        assertEquals(ForeignKeyDef.ACTION_NO_ACTION, childTable.foreignKeys[0].onUpdateAction);
        assertEquals(ForeignKeyDef.ACTION_NO_ACTION, childTable.foreignKeys[0].onDeleteAction);
        assertEquals(parentTable.uuid, childTable.foreignKeys[0].parentTableId);
        assertArrayEquals(new String[] { "s2", "n2" }, childTable.foreignKeys[0].columns);
        assertArrayEquals(new String[] { "k1", "n1" }, childTable.foreignKeys[0].parentTableColumns);
    }
}

Example 4 with ForeignKeyViolationException

use of herddb.model.ForeignKeyViolationException in project herddb by diennea.

the class ForeignKeySQLTest method testServerSideOfForeignKey.

private void testServerSideOfForeignKey(final DBManager manager, long tx, String fkName, String updateAction, String deleteAction) throws DataScannerException, StatementExecutionException {
    execute(manager, "INSERT INTO tblspace1.parent(k1,n1,s1) values('a',2,'pvalue')", Collections.emptyList(), new TransactionContext(tx));
    execute(manager, "INSERT INTO tblspace1.parent(k1,n1,s1) values('newvalue',2,'foo')", Collections.emptyList(), new TransactionContext(tx));
    execute(manager, "INSERT INTO tblspace1.child(k2,n2,s2) values('c1',2,'a')", Collections.emptyList(), new TransactionContext(tx));
    execute(manager, "INSERT INTO tblspace1.child(k2,n2,s2) values('c2',2,'newvalue')", Collections.emptyList(), new TransactionContext(tx));
    if ("SET NULL".equals(updateAction)) {
        // assert that we are setting null only on the expected record
        execute(manager, "UPDATE tblspace1.parent set n1=983 where k1='a'", Collections.emptyList(), new TransactionContext(tx));
        assertEquals(1, scan(manager, "SELECT * FROM tblspace1.child WHERE s2 is NULL and n2 is NULL", Collections.emptyList(), new TransactionContext(tx)).consumeAndClose().size());
        assertEquals(1, scan(manager, "SELECT * FROM tblspace1.child WHERE s2='newvalue'", Collections.emptyList(), new TransactionContext(tx)).consumeAndClose().size());
    } else if ("CASCADE".equals(updateAction)) {
    // not implemented
    } else if ("NO ACTION".equals(updateAction)) {
        ForeignKeyViolationException errOnUpdate = expectThrows(ForeignKeyViolationException.class, () -> {
            execute(manager, "UPDATE tblspace1.parent set n1=983", Collections.emptyList(), new TransactionContext(tx));
        });
        assertEquals(fkName, errOnUpdate.getForeignKeyName());
    } else {
        fail();
    }
    if ("CASCADE".equals(deleteAction)) {
        execute(manager, "DELETE FROM tblspace1.parent where k1='a'", Collections.emptyList(), new TransactionContext(tx));
        // assert that we are deleting only the expected record
        assertEquals(0, scan(manager, "SELECT * FROM tblspace1.child WHERE s2='a'", Collections.emptyList(), new TransactionContext(tx)).consumeAndClose().size());
        assertEquals(1, scan(manager, "SELECT * FROM tblspace1.child WHERE s2='newvalue'", Collections.emptyList(), new TransactionContext(tx)).consumeAndClose().size());
    } else if ("SET NULL".equals(deleteAction)) {
        execute(manager, "DELETE FROM tblspace1.parent where k1='a'", Collections.emptyList(), new TransactionContext(tx));
        // assert that we are setting null only on the expected record
        assertEquals(1, scan(manager, "SELECT * FROM tblspace1.child WHERE s2 is NULL and n2 is NULL", Collections.emptyList(), new TransactionContext(tx)).consumeAndClose().size());
        assertEquals(1, scan(manager, "SELECT * FROM tblspace1.child WHERE s2='newvalue'", Collections.emptyList(), new TransactionContext(tx)).consumeAndClose().size());
    } else if ("NO ACTION".equals(deleteAction)) {
        ForeignKeyViolationException errOnDelete = expectThrows(ForeignKeyViolationException.class, () -> {
            execute(manager, "DELETE FROM tblspace1.parent", Collections.emptyList(), new TransactionContext(tx));
        });
        assertEquals(fkName, errOnDelete.getForeignKeyName());
        execute(manager, "DELETE FROM tblspace1.child", Collections.emptyList(), new TransactionContext(tx));
        execute(manager, "UPDATE tblspace1.parent set n1=983", Collections.emptyList(), new TransactionContext(tx));
        execute(manager, "DELETE FROM tblspace1.parent", Collections.emptyList(), new TransactionContext(tx));
    } else {
        fail();
    }
}

Example 5 with ForeignKeyViolationException

use of herddb.model.ForeignKeyViolationException in project herddb by diennea.

the class TableManager method executeForeignKeyConstraintsAsParentTable.

private void executeForeignKeyConstraintsAsParentTable(Table childTable, DataAccessor previousValuesOnParentTable, StatementEvaluationContext context, Transaction transaction, boolean delete) throws StatementExecutionException {
    // invalidated consistently during DML operations.
    for (ForeignKeyDef fk : childTable.foreignKeys) {
        String query = parentForeignKeyQueries.computeIfAbsent(childTable.name + "." + fk.name + ".#" + delete, (l -> {
            if (fk.onDeleteAction == ForeignKeyDef.ACTION_CASCADE && delete) {
                StringBuilder q = new StringBuilder("DELETE FROM ");
                q.append(delimit(childTable.tablespace));
                q.append(".");
                q.append(delimit(childTable.name));
                q.append(" WHERE ");
                for (int i = 0; i < fk.columns.length; i++) {
                    if (i > 0) {
                        q.append(" AND ");
                    }
                    q.append(delimit(fk.columns[i]));
                    q.append("=?");
                }
                return q.toString();
            } else if (fk.onUpdateAction == ForeignKeyDef.ACTION_CASCADE && !delete) {
                // the change is more complex, let's keep it for a future work
                throw new StatementExecutionException("No supported ON UPDATE CASCADE");
            } else if ((fk.onDeleteAction == ForeignKeyDef.ACTION_SETNULL && delete) || (fk.onUpdateAction == ForeignKeyDef.ACTION_SETNULL && !delete)) {
                // delete or update it is the same for SET NULL
                StringBuilder q = new StringBuilder("UPDATE ");
                q.append(delimit(childTable.tablespace));
                q.append(".");
                q.append(delimit(childTable.name));
                q.append(" SET ");
                for (int i = 0; i < fk.columns.length; i++) {
                    if (i > 0) {
                        q.append(",");
                    }
                    q.append(delimit(fk.columns[i]));
                    q.append("= NULL ");
                }
                q.append(" WHERE ");
                for (int i = 0; i < fk.columns.length; i++) {
                    if (i > 0) {
                        q.append(" AND ");
                    }
                    q.append(delimit(fk.columns[i]));
                    q.append("=?");
                }
                return q.toString();
            } else {
                // NO ACTION case, check that there is no matching record in the child table that wouble be invalidated
                // with '*' we are not going to perform projections or copies
                StringBuilder q = new StringBuilder("SELECT * FROM ");
                q.append(delimit(childTable.tablespace));
                q.append(".");
                q.append(delimit(childTable.name));
                q.append(" WHERE ");
                for (int i = 0; i < fk.columns.length; i++) {
                    if (i > 0) {
                        q.append(" AND ");
                    }
                    q.append(delimit(fk.columns[i]));
                    q.append("=?");
                }
                return q.toString();
            }
        }));
        final List<Object> valuesToMatch = new ArrayList<>(fk.parentTableColumns.length);
        for (int i = 0; i < fk.parentTableColumns.length; i++) {
            valuesToMatch.add(previousValuesOnParentTable.get(fk.parentTableColumns[i]));
        }
        TransactionContext tx = transaction != null ? new TransactionContext(transaction.transactionId) : TransactionContext.NO_TRANSACTION;
        if (fk.onDeleteAction == ForeignKeyDef.ACTION_CASCADE && delete || fk.onUpdateAction == ForeignKeyDef.ACTION_CASCADE && !delete || fk.onUpdateAction == ForeignKeyDef.ACTION_SETNULL && !delete || fk.onDeleteAction == ForeignKeyDef.ACTION_SETNULL && delete) {
            tableSpaceManager.getDbmanager().executeSimpleStatement(tableSpaceManager.getTableSpaceName(), query, valuesToMatch, // every record
            -1, // keep read locks in TransactionContext
            true, tx, null);
        } else {
            boolean fkOk;
            try (DataScanner scan = tableSpaceManager.getDbmanager().executeSimpleQuery(tableSpaceManager.getTableSpaceName(), query, valuesToMatch, // only one record
            1, // keep read locks in TransactionContext
            true, tx, null)) {
                List<DataAccessor> resultSet = scan.consume();
                // we are on the parent side of the relation
                // we are okay if there is no matching record
                // TODO: return the list of PKs in order to implement CASCADE operations
                fkOk = resultSet.isEmpty();
            } catch (DataScannerException err) {
                throw new StatementExecutionException(err);
            }
            if (!fkOk) {
                throw new ForeignKeyViolationException(fk.name, "foreignKey " + childTable.name + "." + fk.name + " violated");
            }
        }
    }
}