Examples with DBManager herddb.core.DBManager used on opensource projects

Example 26 with DBManager

use of herddb.core.DBManager 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 27 with DBManager

use of herddb.core.DBManager in project herddb by diennea.

the class ForeignKeySQLTest method cannotAlterColumnsWithChildTableRefs.

@Test
public void cannotAlterColumnsWithChildTableRefs() 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, " + "CONSTRAINT fk1 FOREIGN KEY (s2,n2) REFERENCES parent(k1,n1) ON DELETE NO ACTION ON UPDATE NO ACTION)", Collections.emptyList());
        Table childTable = manager.getTableSpaceManager("tblspace1").getTableManager("child").getTable();
        assertEquals(1, childTable.foreignKeys.length);
        StatementExecutionException errCannotDrop = expectThrows(StatementExecutionException.class, () -> {
            execute(manager, "DROP TABLE tblspace1.parent", Collections.emptyList());
        });
        assertEquals("Cannot drop table tblspace1.parent because it has children tables: child", errCannotDrop.getMessage());
        StatementExecutionException errCannotDropColumn = expectThrows(StatementExecutionException.class, () -> {
            execute(manager, "ALTER TABLE tblspace1.parent DROP COLUMN n1", Collections.emptyList());
        });
        assertEquals("Cannot drop column parent.n1 because of foreign key constraint fk1 on table child", errCannotDropColumn.getMessage());
    }
}

Example 28 with DBManager

use of herddb.core.DBManager in project herddb by diennea.

the class SimpleSubqueryTest method subQueryOnWhereTest.

@Test
public void subQueryOnWhereTest() 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.table1 (k1 string primary key,n1 int)", Collections.emptyList());
        execute(manager, "CREATE TABLE tblspace1.table2 (k2 string primary key,fk string)", Collections.emptyList());
        assertEquals(1, executeUpdate(manager, "INSERT INTO tblspace1.table1(k1,n1) values(?,?)", Arrays.asList("mykey", Integer.valueOf(1234))).getUpdateCount());
        assertEquals(1, executeUpdate(manager, "INSERT INTO tblspace1.table1(k1,n1) values(?,?)", Arrays.asList("mykey2", Integer.valueOf(1234))).getUpdateCount());
        assertEquals(1, executeUpdate(manager, "INSERT INTO tblspace1.table1(k1,n1) values(?,?)", Arrays.asList("mykey3", Integer.valueOf(1234))).getUpdateCount());
        assertEquals(1, executeUpdate(manager, "INSERT INTO tblspace1.table1(k1,n1) values(?,?)", Arrays.asList("mykey4", Integer.valueOf(1234))).getUpdateCount());
        assertEquals(1, executeUpdate(manager, "INSERT INTO tblspace1.table2(k2,fk) values(?,?)", Arrays.asList("subkey1", "mykey2")).getUpdateCount());
        assertEquals(1, executeUpdate(manager, "INSERT INTO tblspace1.table2(k2,fk) values(?,?)", Arrays.asList("subkey2", "mykey2")).getUpdateCount());
        assertEquals(1, executeUpdate(manager, "INSERT INTO tblspace1.table2(k2,fk) values(?,?)", Arrays.asList("subkey3", "mykey3")).getUpdateCount());
        assertEquals(1, executeUpdate(manager, "INSERT INTO tblspace1.table2(k2,fk) values(?,?)", Arrays.asList("subkey4", "mykey4")).getUpdateCount());
        assertEquals(2, scan(manager, "SELECT * " + "FROM tblspace1.table1 t1 " + "WHERE t1.k1 in ('mykey','mykey3')" + "", Collections.emptyList()).consumeAndClose().size());
        if (manager.isFullSQLSupportEnabled()) {
            assertEquals(1, scan(manager, "SELECT * " + "FROM tblspace1.table1 t1 " + "WHERE t1.k1 in (SELECT fk FROM tblspace1.table2 WHERE k2='subkey4')" + "", Collections.emptyList()).consumeAndClose().size());
            assertEquals(1, scan(manager, "SELECT * " + "FROM tblspace1.table1 t1 " + "WHERE t1.n1 = ? and t1.k1 in (SELECT fk FROM tblspace1.table2 WHERE k2=?)" + "", Arrays.asList(1234, "subkey4")).consumeAndClose().size());
            assertEquals(0, scan(manager, "SELECT * " + "FROM tblspace1.table1 t1 " + "WHERE t1.n1 = ? and t1.k1 in (SELECT fk FROM tblspace1.table2 WHERE k2=?)" + "", Arrays.asList(1234, "subkey5")).consumeAndClose().size());
        }
    }
}

Example 29 with DBManager

use of herddb.core.DBManager in project herddb by diennea.

the class UpdateTest method updateMultiRowsWithValidationError.

@Test
public void updateMultiRowsWithValidationError() throws Exception {
    final int inserts = 10;
    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(), NO_TRANSACTION);
        manager.waitForTablespace("tblspace1", 10000);
        execute(manager, "CREATE TABLE tblspace1.tsql (k1 int, n1 int not null, primary key(k1))", Collections.emptyList());
        for (int i = 0; i < inserts; i++) {
            assertEquals(1, executeUpdate(manager, "INSERT INTO tblspace1.tsql(k1,n1) values(?,?)", Arrays.asList(Integer.valueOf(i), Integer.valueOf(1))).getUpdateCount());
        }
        long tx = beginTransaction(manager, "tblspace1");
        TransactionContext ctx = new TransactionContext(tx);
        // single record failed update
        StatementExecutionException error = herddb.utils.TestUtils.expectThrows(StatementExecutionException.class, () -> {
            executeUpdate(manager, "UPDATE tblspace1.tsql set n1 = null WHERE n1=1", Collections.emptyList(), ctx);
        });
        assertEquals("error on column n1 (integer not null):Cannot have null value in non-NULL type integer", error.getMessage());
        // multi record failed update
        StatementExecutionException errors = herddb.utils.TestUtils.expectThrows(StatementExecutionException.class, () -> {
            executeUpdate(manager, "UPDATE tblspace1.tsql set n1 = null", Collections.emptyList(), ctx);
        });
        assertEquals("error on column n1 (integer not null):Cannot have null value in non-NULL type integer", errors.getMessage());
        commitTransaction(manager, "tblspace1", tx);
    }
}

Example 30 with DBManager

use of herddb.core.DBManager in project herddb by diennea.

the class UpdateTest method simpleManualSequence.

@Test
public void simpleManualSequence() 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.myseq (k1 int, k2 int, current_value int, primary key(k1, k2))", Collections.emptyList());
        DMLStatementExecutionResult insertSequence1 = executeUpdate(manager, "INSERT INTO tblspace1.myseq(k1,k2,current_value) values(?,?,?)", Arrays.asList(Integer.valueOf(78), Integer.valueOf(0), Integer.valueOf(1)), TransactionContext.AUTOTRANSACTION_TRANSACTION);
        assertTrue(insertSequence1.transactionId > 0);
        try (DataScanner scan = scan(manager, "SELECT current_value from tblspace1.myseq where k1=?", Arrays.asList(78), new TransactionContext(insertSequence1.transactionId))) {
            List<DataAccessor> recordSet = scan.consumeAndClose();
            assertEquals(1, recordSet.size());
            assertEquals(1, recordSet.get(0).get(0));
        }
        // set current_value = 2 if current_value = 1
        DMLStatementExecutionResult updateSequence1 = executeUpdate(manager, "UPDATE tblspace1.myseq set current_value=? where k1=78 and k2=0 and current_value=?", Arrays.asList(Integer.valueOf(2), Integer.valueOf(1)), new TransactionContext(insertSequence1.transactionId));
        assertEquals(insertSequence1.transactionId, updateSequence1.transactionId);
        assertEquals(1, updateSequence1.getUpdateCount());
        try (DataScanner scan = scan(manager, "SELECT current_value from tblspace1.myseq where k1=?", Arrays.asList(78), new TransactionContext(updateSequence1.transactionId))) {
            List<DataAccessor> recordSet = scan.consumeAndClose();
            assertEquals(1, recordSet.size());
            assertEquals(2, recordSet.get(0).get(0));
        }
        DMLStatementExecutionResult updateSequence2 = executeUpdate(manager, "UPDATE tblspace1.myseq set current_value=? where k1=78 and k2=0 and current_value=?", Arrays.asList(Integer.valueOf(3), Integer.valueOf(2)), new TransactionContext(updateSequence1.transactionId));
        assertEquals(updateSequence2.transactionId, updateSequence1.transactionId);
        assertEquals(1, updateSequence2.getUpdateCount());
        try (DataScanner scan = scan(manager, "SELECT current_value from tblspace1.myseq where k1=?", Arrays.asList(78), new TransactionContext(updateSequence1.transactionId))) {
            List<DataAccessor> recordSet = scan.consumeAndClose();
            assertEquals(1, recordSet.size());
            assertEquals(3, recordSet.get(0).get(0));
        }
        DMLStatementExecutionResult deleteSequence2 = executeUpdate(manager, "DELETE FROM tblspace1.myseq where k1=78 and k2=0 ", Arrays.asList(), new TransactionContext(updateSequence1.transactionId));
        assertEquals(deleteSequence2.transactionId, updateSequence1.transactionId);
        assertEquals(1, deleteSequence2.getUpdateCount());
        try (DataScanner scan = scan(manager, "SELECT current_value from tblspace1.myseq where k1=?", Arrays.asList(78), new TransactionContext(updateSequence1.transactionId))) {
            List<DataAccessor> recordSet = scan.consumeAndClose();
            assertEquals(0, recordSet.size());
        }
    }
}