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

Example 21 with DBManager

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

the class CalcitePlannerTest method showCreateTableTest_with_Indexes.

@Test
public void showCreateTableTest_with_Indexes() 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.test23 (k1 string not null ," + "s1 string not null, n1 int, primary key(k1,n1))", Collections.emptyList());
        execute(manager, "CREATE INDEX ixn1s1 on tblspace1.test23(n1,s1)", Collections.emptyList());
        execute(manager, "CREATE UNIQUE INDEX ixn1s2 on tblspace1.test23(n1,s1,k1)", Collections.emptyList());
        TranslatedQuery translatedQuery = manager.getPlanner().translate("tblspace1", "SHOW CREATE TABLE tblspace1.test23 WITH INDEXES", Collections.emptyList(), true, false, true, -1);
        assertTrue(translatedQuery.plan.mainStatement instanceof SQLPlannedOperationStatement || translatedQuery.plan.mainStatement instanceof ScanStatement);
        ScanResult scanResult = (ScanResult) manager.executePlan(translatedQuery.plan, translatedQuery.context, TransactionContext.NO_TRANSACTION);
        DataScanner dataScanner = scanResult.dataScanner;
        String[] columns = dataScanner.getFieldNames();
        List<DataAccessor> records = dataScanner.consumeAndClose();
        TuplesList tuplesList = new TuplesList(columns, records);
        assertTrue(tuplesList.columnNames[0].equalsIgnoreCase("tabledef"));
        Tuple values = (Tuple) records.get(0);
        assertEquals("CREATE TABLE tblspace1.test23(k1 string not null,s1 string not null,n1 integer,PRIMARY KEY(k1,n1),INDEX ixn1s1(n1,s1),UNIQUE KEY ixn1s2 (n1,s1,k1))", values.get("tabledef").toString());
        // Drop the table and indexes and recreate them again.
        String showCreateCommandOutput = values.get("tabledef").toString();
        // drop the table
        execute(manager, "DROP TABLE tblspace1.test23", Collections.emptyList());
        // ensure table has been dropped
        try (DataScanner scan = scan(manager, "SELECT * FROM tblspace1.systables where table_name='test23'", Collections.emptyList())) {
            assertTrue(scan.consume().isEmpty());
        }
        // ensure index has been dropped
        try (DataScanner scan = scan(manager, "SELECT * FROM tblspace1.sysindexes where index_name='ixn1s1'", Collections.emptyList())) {
            assertTrue(scan.consume().isEmpty());
        }
        try (DataScanner scan = scan(manager, "SELECT * FROM tblspace1.sysindexes where index_name='ixn1s2'", Collections.emptyList())) {
            assertTrue(scan.consume().isEmpty());
        }
        execute(manager, showCreateCommandOutput, Collections.emptyList());
        // Ensure the table is getting created
        try (DataScanner scan = scan(manager, "SELECT * FROM tblspace1.systables where table_name='test23'", Collections.emptyList())) {
            assertFalse(scan.consume().isEmpty());
        }
        // Ensure index got created as well.
        try (DataScanner scan = scan(manager, "SELECT * FROM tblspace1.sysindexes where index_name='ixn1s1'", Collections.emptyList())) {
            assertFalse(scan.consume().isEmpty());
        }
    }
}

Example 22 with DBManager

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

the class ForeignKeySQLTest method createTableWithForeignKey.

@Test
public void createTableWithForeignKey() 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 parentTable = manager.getTableSpaceManager("tblspace1").getTableManager("parent").getTable();
        Table 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);
        // test without transaction
        testChildSideOfForeignKey(manager, TransactionContext.NOTRANSACTION_ID, "fk1");
        execute(manager, "DELETE FROM tblspace1.child", Collections.emptyList());
        execute(manager, "DELETE FROM tblspace1.parent", Collections.emptyList());
        long tx = beginTransaction(manager, "tblspace1");
        // test with transaction
        testChildSideOfForeignKey(manager, tx, "fk1");
        TestUtils.commitTransaction(manager, "tblspace1", tx);
        execute(manager, "DELETE FROM tblspace1.child", Collections.emptyList());
        execute(manager, "DELETE FROM tblspace1.parent", Collections.emptyList());
        // test without transaction
        testServerSideOfForeignKey(manager, TransactionContext.NOTRANSACTION_ID, "fk1", "NO ACTION", "NO ACTION");
        execute(manager, "DELETE FROM tblspace1.child", Collections.emptyList());
        execute(manager, "DELETE FROM tblspace1.parent", Collections.emptyList());
        tx = beginTransaction(manager, "tblspace1");
        // test with transaction
        testServerSideOfForeignKey(manager, tx, "fk1", "NO ACTION", "NO ACTION");
        TestUtils.commitTransaction(manager, "tblspace1", tx);
    }
}

Example 23 with DBManager

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

the class ForeignKeySQLTest method alterTableDropAndForeignKey.

@Test
public void alterTableDropAndForeignKey() 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," + "CONSTRAINT fk2 FOREIGN KEY (s2) REFERENCES parent(k1) ON DELETE NO ACTION ON UPDATE NO ACTION)", Collections.emptyList());
        Table parentTable = manager.getTableSpaceManager("tblspace1").getTableManager("parent").getTable();
        Table childTable = manager.getTableSpaceManager("tblspace1").getTableManager("child").getTable();
        assertEquals(2, 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);
        assertEquals("fk2", childTable.foreignKeys[1].name);
        assertEquals(ForeignKeyDef.ACTION_NO_ACTION, childTable.foreignKeys[1].onUpdateAction);
        assertEquals(ForeignKeyDef.ACTION_NO_ACTION, childTable.foreignKeys[1].onDeleteAction);
        assertEquals(parentTable.uuid, childTable.foreignKeys[1].parentTableId);
        assertArrayEquals(new String[] { "s2" }, childTable.foreignKeys[1].columns);
        assertArrayEquals(new String[] { "k1" }, childTable.foreignKeys[1].parentTableColumns);
        // test FK is working
        testChildSideOfForeignKey(manager, TransactionContext.NOTRANSACTION_ID, "fk1");
        execute(manager, "DELETE FROM tblspace1.child", Collections.emptyList());
        execute(manager, "DELETE FROM tblspace1.parent", Collections.emptyList());
        testServerSideOfForeignKey(manager, TransactionContext.NOTRANSACTION_ID, "fk1", "NO ACTION", "NO ACTION");
        execute(manager, "ALTER TABLE tblspace1.child DROP CONSTRAINT fk1", Collections.emptyList());
        childTable = manager.getTableSpaceManager("tblspace1").getTableManager("child").getTable();
        assertEquals(1, childTable.foreignKeys.length);
        assertEquals("fk2", 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" }, childTable.foreignKeys[0].columns);
        assertArrayEquals(new String[] { "k1" }, childTable.foreignKeys[0].parentTableColumns);
        // TRUCATE requires a checkpoint lock, we are also testing that the tables are free from global locks
        execute(manager, "TRUNCATE TABLE tblspace1.child", Collections.emptyList(), TransactionContext.NO_TRANSACTION);
        execute(manager, "TRUNCATE TABLE tblspace1.parent", Collections.emptyList(), TransactionContext.NO_TRANSACTION);
        execute(manager, "INSERT INTO tblspace1.parent(k1,n1,s1) values('a',2,'pvalue')", Collections.emptyList(), TransactionContext.NO_TRANSACTION);
        // insert a record that could violate the old FK1 (but not FK2)
        execute(manager, "INSERT INTO tblspace1.child(k2,n2,s2) values('no',10,'a')", Collections.emptyList(), TransactionContext.NO_TRANSACTION);
        execute(manager, "TRUNCATE TABLE tblspace1.child", Collections.emptyList(), TransactionContext.NO_TRANSACTION);
        execute(manager, "TRUNCATE TABLE tblspace1.parent", Collections.emptyList(), TransactionContext.NO_TRANSACTION);
        // add the FK again
        execute(manager, "ALTER TABLE tblspace1.`CHILD` Add CONSTRAINT `fk3` FOREIGN KEY (s2,n2) REFERENCES parent(k1,n1) ON DELETE RESTRICT", Collections.emptyList());
        testChildSideOfForeignKey(manager, TransactionContext.NOTRANSACTION_ID, "fk2");
        execute(manager, "DELETE FROM tblspace1.child", Collections.emptyList());
        execute(manager, "DELETE FROM tblspace1.parent", Collections.emptyList());
        childTable = manager.getTableSpaceManager("tblspace1").getTableManager("child").getTable();
        assertEquals(2, childTable.foreignKeys.length);
        assertEquals("fk2", 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" }, childTable.foreignKeys[0].columns);
        assertArrayEquals(new String[] { "k1" }, childTable.foreignKeys[0].parentTableColumns);
        assertEquals("fk3", childTable.foreignKeys[1].name);
        assertEquals(ForeignKeyDef.ACTION_NO_ACTION, childTable.foreignKeys[1].onUpdateAction);
        assertEquals(ForeignKeyDef.ACTION_NO_ACTION, childTable.foreignKeys[1].onDeleteAction);
        assertEquals(parentTable.uuid, childTable.foreignKeys[1].parentTableId);
        assertArrayEquals(new String[] { "s2", "n2" }, childTable.foreignKeys[1].columns);
        assertArrayEquals(new String[] { "k1", "n1" }, childTable.foreignKeys[1].parentTableColumns);
        testServerSideOfForeignKey(manager, TransactionContext.NOTRANSACTION_ID, "fk2", "NO ACTION", "NO ACTION");
    }
}

Example 24 with DBManager

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

the class ForeignKeySQLTest method alterAddUnnamedForeignKey.

@Test
public void alterAddUnnamedForeignKey() 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, "ALTER TABLE tblspace1.child ADD FOREIGN KEY (s2,n2) REFERENCES parent(k1,n1)", Collections.emptyList());
        Table parentTable = manager.getTableSpaceManager("tblspace1").getTableManager("parent").getTable();
        Table childTable = manager.getTableSpaceManager("tblspace1").getTableManager("child").getTable();
        assertEquals(1, childTable.foreignKeys.length);
        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);
        // test FK is working
        testChildSideOfForeignKey(manager, TransactionContext.NOTRANSACTION_ID, childTable.foreignKeys[0].name);
    }
}

Example 25 with DBManager

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

the class ForeignKeySQLTest method createTableWithOnUpdateSetNull.

@Test
public void createTableWithOnUpdateSetNull() throws Exception {
    assumeTrue("only jsqlparser 4.x support ON UPDATE set NULL", false);
    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 SET NULL)", Collections.emptyList());
        Table parentTable = manager.getTableSpaceManager("tblspace1").getTableManager("parent").getTable();
        Table childTable = manager.getTableSpaceManager("tblspace1").getTableManager("child").getTable();
        assertEquals(1, childTable.foreignKeys.length);
        assertEquals("fk1", childTable.foreignKeys[0].name);
        assertEquals(ForeignKeyDef.ACTION_SETNULL, 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);
        // test without transaction
        testChildSideOfForeignKey(manager, TransactionContext.NOTRANSACTION_ID, "fk1");
        execute(manager, "DELETE FROM tblspace1.child", Collections.emptyList());
        execute(manager, "DELETE FROM tblspace1.parent", Collections.emptyList());
        long tx = beginTransaction(manager, "tblspace1");
        // test with transaction
        testChildSideOfForeignKey(manager, tx, "fk1");
        TestUtils.commitTransaction(manager, "tblspace1", tx);
        execute(manager, "DELETE FROM tblspace1.child", Collections.emptyList());
        execute(manager, "DELETE FROM tblspace1.parent", Collections.emptyList());
        // test without transaction
        testServerSideOfForeignKey(manager, TransactionContext.NOTRANSACTION_ID, "fk1", "SET NULL", "NO ACTION");
        execute(manager, "DELETE FROM tblspace1.child", Collections.emptyList());
        execute(manager, "DELETE FROM tblspace1.parent", Collections.emptyList());
        tx = beginTransaction(manager, "tblspace1");
        // test with transaction
        testServerSideOfForeignKey(manager, tx, "fk1", "SET NULL", "NO ACTION");
        TestUtils.commitTransaction(manager, "tblspace1", tx);
    }
}