Examples with HDBClient herddb.client.HDBClient used on opensource projects

Example 61 with HDBClient

use of herddb.client.HDBClient in project herddb by diennea.

the class StreamBasedWritesTest method test.

@Test
public void test() throws Exception {
    try (Server server = new Server(TestUtils.newServerConfigurationWithAutoPort(folder.newFolder().toPath()))) {
        server.start();
        server.waitForStandaloneBoot();
        try (HDBClient client = new HDBClient(new ClientConfiguration(folder.newFolder().toPath()))) {
            client.setClientSideMetadataProvider(new StaticClientSideMetadataProvider(server));
            try (BasicHerdDBDataSource dataSource = new BasicHerdDBDataSource(client);
                Connection con = dataSource.getConnection();
                Statement statement = con.createStatement()) {
                statement.execute("CREATE TABLE mytable (" + "key int primary key," + "valstring string," + "valbinary blob" + ")");
                String myString = "mystring";
                byte[] myBinary = "mybinary".getBytes(StandardCharsets.UTF_8);
                String myShortString = "my";
                byte[] myShortBinary = "my".getBytes(StandardCharsets.UTF_8);
                Set<Integer> shorterValues = new HashSet<>();
                try (PreparedStatement insert = con.prepareStatement("INSERT INTO mytable (key,valstring,valbinary ) values(?,?,?)")) {
                    int i = 0;
                    insert.setInt(1, i);
                    insert.setString(2, myString);
                    insert.setBytes(3, myBinary);
                    insert.executeUpdate();
                    i++;
                    insert.setInt(1, i);
                    insert.setCharacterStream(2, new StringReader(myString));
                    insert.setBinaryStream(3, new ByteArrayInputStream(myBinary));
                    insert.executeUpdate();
                    i++;
                    insert.setInt(1, i);
                    insert.setNCharacterStream(2, new StringReader(myString));
                    insert.setBinaryStream(3, new ByteArrayInputStream(myBinary));
                    insert.executeUpdate();
                    i++;
                    insert.setInt(1, i);
                    insert.setCharacterStream(2, new StringReader(myString), 2);
                    insert.setBinaryStream(3, new ByteArrayInputStream(myBinary), 2);
                    insert.executeUpdate();
                    shorterValues.add(i);
                    i++;
                    insert.setInt(1, i);
                    insert.setNCharacterStream(2, new StringReader(myString), 2);
                    insert.setBinaryStream(3, new ByteArrayInputStream(myBinary), 2L);
                    insert.executeUpdate();
                    shorterValues.add(i);
                    i++;
                    insert.setInt(1, i);
                    insert.setClob(2, new StringReader(myString));
                    insert.setBlob(3, new ByteArrayInputStream(myBinary));
                    insert.executeUpdate();
                    i++;
                    insert.setInt(1, i);
                    insert.setClob(2, new StringReader(myString), 2);
                    insert.setBlob(3, new ByteArrayInputStream(myBinary), 2);
                    insert.executeUpdate();
                    shorterValues.add(i);
                    i++;
                    insert.setInt(1, i);
                    insert.setAsciiStream(2, new ByteArrayInputStream(myString.getBytes(StandardCharsets.US_ASCII)), 2);
                    insert.setBlob(3, new ByteArrayInputStream(myBinary), 2);
                    insert.executeUpdate();
                    shorterValues.add(i);
                    i++;
                    insert.setInt(1, i);
                    insert.setAsciiStream(2, new ByteArrayInputStream(myString.getBytes(StandardCharsets.US_ASCII)));
                    insert.setBlob(3, new ByteArrayInputStream(myBinary));
                    insert.executeUpdate();
                    i++;
                    insert.setInt(1, i);
                    insert.setAsciiStream(2, new ByteArrayInputStream(myString.getBytes(StandardCharsets.US_ASCII)), 2L);
                    insert.setBlob(3, new ByteArrayInputStream(myBinary), 2);
                    insert.executeUpdate();
                    shorterValues.add(i);
                    i++;
                    insert.setInt(1, i);
                    insert.setUnicodeStream(2, new ByteArrayInputStream(myString.getBytes(StandardCharsets.US_ASCII)), 2);
                    insert.setBlob(3, new ByteArrayInputStream(myBinary), 2);
                    insert.executeUpdate();
                    shorterValues.add(i);
                }
                try (PreparedStatement ps = con.prepareStatement("SELECT key,valstring,valbinary" + " FROM mytable ORDER BY key");
                    ResultSet rs = ps.executeQuery()) {
                    while (rs.next()) {
                        int key = rs.getInt(1);
                        if (shorterValues.contains(key)) {
                            checkString(rs, 2, myShortString, key);
                            checkBlob(rs, 3, myShortBinary, key);
                        } else {
                            checkString(rs, 2, myString, key);
                            checkBlob(rs, 3, myBinary, key);
                        }
                    }
                }
            }
        }
    }
}

Example 62 with HDBClient

use of herddb.client.HDBClient in project herddb by diennea.

the class SystemTablesTest method ensureWeThrowAppropriateExceptionWhenNotNullConstraintViolated_AddBatch_ExecuteBatch.

@Test
public void ensureWeThrowAppropriateExceptionWhenNotNullConstraintViolated_AddBatch_ExecuteBatch() throws Exception {
    try (Server server = new Server(TestUtils.newServerConfigurationWithAutoPort(folder.newFolder().toPath()))) {
        server.start();
        server.waitForStandaloneBoot();
        try (HDBClient client = new HDBClient(new ClientConfiguration(folder.newFolder().toPath()))) {
            client.setClientSideMetadataProvider(new StaticClientSideMetadataProvider(server));
            try {
                BasicHerdDBDataSource dataSource = new BasicHerdDBDataSource(client);
                Connection con = dataSource.getConnection();
                Statement statement = con.createStatement();
                statement.execute("CREATE TABLE mytable (n1 int primary key auto_increment, name string not null)");
                PreparedStatement insertPs = con.prepareStatement("INSERT INTO mytable (name) values(?)");
                for (int i = 0; i < 10; i++) {
                    insertPs.setString(1, null);
                    insertPs.addBatch();
                }
                insertPs.executeBatch();
            } catch (SQLException ex) {
                assertTrue(ex.getMessage().contains("StatementExecutionException"));
                assertTrue(ex.getMessage().contains("Cannot have null value in non-NULL type string"));
            }
        }
    }
}

Example 63 with HDBClient

use of herddb.client.HDBClient in project herddb by diennea.

the class SystemTablesTest method test.

@Test
public void test() throws Exception {
    try (Server server = new Server(TestUtils.newServerConfigurationWithAutoPort(folder.newFolder().toPath()))) {
        server.start();
        server.waitForStandaloneBoot();
        try (HDBClient client = new HDBClient(new ClientConfiguration(folder.newFolder().toPath()))) {
            client.setClientSideMetadataProvider(new StaticClientSideMetadataProvider(server));
            try (BasicHerdDBDataSource dataSource = new BasicHerdDBDataSource(client);
                Connection con = dataSource.getConnection();
                Statement statement = con.createStatement()) {
                statement.execute("CREATE TABLE mytable (key string primary key, name string)");
                statement.execute("CREATE INDEX mytableindex ON mytable(name)");
                statement.execute("CREATE UNIQUE INDEX mytableindex2 ON mytable(name, key)");
                statement.execute("CREATE TABLE mytable2 (n2 int primary key auto_increment, name string not null, ts timestamp)");
                statement.execute("CREATE TABLE mytable3 (n2 int primary key auto_increment, age int not null default 99, phone long not null, salary double, married bool)");
                try (ResultSet rs = statement.executeQuery("SELECT * FROM SYSTABLES")) {
                    Set<String> tables = new HashSet<>();
                    while (rs.next()) {
                        String name = rs.getString("table_name");
                        tables.add(name);
                    }
                    assertTrue(tables.contains("mytable"));
                    assertTrue(tables.contains("mytable2"));
                    assertTrue(tables.contains("mytable3"));
                }
                DatabaseMetaData metaData = con.getMetaData();
                try (ResultSet rs = metaData.getTables(null, null, null, null)) {
                    List<List<String>> records = new ArrayList<>();
                    while (rs.next()) {
                        List<String> record = new ArrayList<>();
                        for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
                            String value = rs.getString(i + 1);
                            record.add(value);
                        }
                        records.add(record);
                    }
                    assertTrue(records.stream().filter(s -> s.contains("mytable")).findAny().isPresent());
                    assertTrue(records.stream().filter(s -> s.contains("mytable2")).findAny().isPresent());
                    assertTrue(records.stream().filter(s -> s.contains("mytable3")).findAny().isPresent());
                    assertTrue(records.stream().filter(s -> s.contains("systables")).findAny().isPresent());
                    assertTrue(records.stream().filter(s -> s.contains("syscolumns")).findAny().isPresent());
                    assertTrue(records.stream().filter(s -> s.contains("sysstatements")).findAny().isPresent());
                }
                try (ResultSet rs = metaData.getTables(null, null, "m%table", null)) {
                    List<List<String>> records = new ArrayList<>();
                    while (rs.next()) {
                        List<String> record = new ArrayList<>();
                        for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
                            String value = rs.getString(i + 1);
                            record.add(value);
                        }
                        records.add(record);
                    }
                    assertTrue(records.stream().filter(s -> s.contains("mytable")).findAny().isPresent());
                    assertEquals(1, records.size());
                }
                try (ResultSet rs = metaData.getTables(null, null, "m_table%", null)) {
                    List<List<String>> records = new ArrayList<>();
                    while (rs.next()) {
                        List<String> record = new ArrayList<>();
                        for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
                            String value = rs.getString(i + 1);
                            record.add(value);
                        }
                        records.add(record);
                    }
                    assertTrue(records.stream().filter(s -> s.contains("mytable")).findAny().isPresent());
                    assertTrue(records.stream().filter(s -> s.contains("mytable2")).findAny().isPresent());
                    assertTrue(records.stream().filter(s -> s.contains("mytable3")).findAny().isPresent());
                    assertEquals(3, records.size());
                }
                List<List<String>> records1 = new ArrayList<>();
                validateColumnsOfTable2(metaData, "mytable2", records1);
                List<List<String>> records2 = new ArrayList<>();
                validateColumnsOfTable2(metaData, "MyTable2", records2);
                // getColumns must be non case sensitive
                assertEquals(records1, records2);
                try (ResultSet rs = metaData.getColumns(null, null, "mytable3", null)) {
                    List<List<String>> records = new ArrayList<>();
                    while (rs.next()) {
                        List<String> record = new ArrayList<>();
                        for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
                            String value = rs.getString(i + 1);
                            record.add(value);
                        }
                        records.add(record);
                    }
                    assertEquals(5, records.size());
                    assertTrue(records.stream().filter(s -> s.contains("n2") && s.contains("integer")).findAny().isPresent());
                    assertTrue(records.stream().filter(s -> s.contains("age") && s.contains(Types.INTEGER + "") && s.contains("integer not null") && s.contains("99")).findAny().isPresent());
                    assertTrue(records.stream().filter(s -> s.contains("phone") && s.contains(Types.BIGINT + "") && s.contains("long not null")).findAny().isPresent());
                    assertTrue(records.stream().filter(s -> s.contains("salary") && s.contains(Types.DOUBLE + "") && s.contains("double")).findAny().isPresent());
                    assertTrue(records.stream().filter(s -> s.contains("married") && s.contains(Types.BOOLEAN + "") && s.contains("boolean")).findAny().isPresent());
                }
                try (ResultSet rs = metaData.getIndexInfo(null, null, "mytable", false, false)) {
                    List<List<String>> records = new ArrayList<>();
                    while (rs.next()) {
                        List<String> record = new ArrayList<>();
                        for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
                            String value = rs.getString(i + 1);
                            record.add(value);
                        }
                        if (rs.getString("INDEX_NAME").equals("mytableindex2")) {
                            assertFalse(rs.getBoolean("NON_UNIQUE"));
                        }
                        if (rs.getString("INDEX_NAME").equals("mytableindex")) {
                            assertTrue(rs.getBoolean("NON_UNIQUE"));
                        }
                        records.add(record);
                    }
                    // pk + secondary indexed
                    assertEquals(4, records.size());
                }
                try (ResultSet rs = metaData.getPrimaryKeys(null, null, "mytable")) {
                    List<List<String>> records = new ArrayList<>();
                    while (rs.next()) {
                        List<String> record = new ArrayList<>();
                        for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
                            String value = rs.getString(i + 1);
                            record.add(value);
                        }
                        records.add(record);
                    }
                    assertEquals(1, records.size());
                }
                try (ResultSet rs = metaData.getIndexInfo(null, null, "mytable", true, false)) {
                    List<List<String>> records = new ArrayList<>();
                    while (rs.next()) {
                        List<String> record = new ArrayList<>();
                        for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
                            String value = rs.getString(i + 1);
                            record.add(value);
                        }
                        records.add(record);
                    }
                    // only pk + 1 unique index with two columns
                    assertEquals(3, records.size());
                }
                try (ResultSet rs = metaData.getIndexInfo(null, null, null, true, false)) {
                    List<List<String>> records = new ArrayList<>();
                    while (rs.next()) {
                        List<String> record = new ArrayList<>();
                        for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
                            String value = rs.getString(i + 1);
                            record.add(value);
                        }
                        records.add(record);
                    }
                    // this is to be incremented at every new systable
                    assertEquals(28, records.size());
                }
                try (ResultSet rs = metaData.getSchemas()) {
                    List<List<String>> records = new ArrayList<>();
                    while (rs.next()) {
                        List<String> record = new ArrayList<>();
                        for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
                            String value = rs.getString(i + 1);
                            record.add(value);
                        }
                        records.add(record);
                    }
                    assertTrue(records.stream().filter(s -> s.contains(TableSpace.DEFAULT)).findAny().isPresent());
                    assertEquals(1, records.size());
                }
                try (ResultSet rs = metaData.getSchemas(null, "%" + TableSpace.DEFAULT.substring(2))) {
                    List<List<String>> records = new ArrayList<>();
                    while (rs.next()) {
                        List<String> record = new ArrayList<>();
                        for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
                            String value = rs.getString(i + 1);
                            record.add(value);
                        }
                        records.add(record);
                    }
                    assertTrue(records.stream().filter(s -> s.contains(TableSpace.DEFAULT)).findAny().isPresent());
                    assertEquals(1, records.size());
                }
                try (ResultSet rs = metaData.getSchemas(null, "no")) {
                    List<List<String>> records = new ArrayList<>();
                    assertFalse(rs.next());
                }
            }
        }
    }
}

Example 64 with HDBClient

use of herddb.client.HDBClient in project herddb by diennea.

the class MysqlCompatilityTest method test2.

@Test
public void test2() throws Exception {
    try (Server server = new Server(TestUtils.newServerConfigurationWithAutoPort(folder.newFolder().toPath()))) {
        server.start();
        server.waitForStandaloneBoot();
        try (HDBClient client = new HDBClient(new ClientConfiguration(folder.newFolder().toPath()))) {
            client.setClientSideMetadataProvider(new StaticClientSideMetadataProvider(server));
            try (BasicHerdDBDataSource dataSource = new BasicHerdDBDataSource(client);
                Connection con = dataSource.getConnection();
                Connection con2 = dataSource.getConnection();
                Statement statement = con.createStatement()) {
                con.setAutoCommit(false);
                statement.execute("CREATE TABLE `queuebouncecategory_history` (\n" + "  `queueid` int(11) NOT NULL,\n" + "  `idbouncecategory` int(6) NOT NULL,\n" + "  `refdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,\n" + "  `messagecount` bigint(20) NOT NULL,\n" + "  PRIMARY KEY (`queueid`,`refdate`,`idbouncecategory`)\n" + ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;");
                statement.executeUpdate("INSERT INTO `queuebouncecategory_history` VALUES (1,3,'2015-03-29 01:00:00',1)");
                statement.executeUpdate("INSERT INTO `queuebouncecategory_history` VALUES (1,3,'2015-03-29 02:00:00',1)");
                statement.executeUpdate("INSERT INTO `queuebouncecategory_history` VALUES (1,3,'2015-03-29 03:00:00',1)");
            }
        }
    }
}

Example 65 with HDBClient

use of herddb.client.HDBClient in project herddb by diennea.

the class MysqlCompatilityTest method test.

@Test
public void test() throws Exception {
    try (Server server = new Server(TestUtils.newServerConfigurationWithAutoPort(folder.newFolder().toPath()))) {
        server.start();
        server.waitForStandaloneBoot();
        // assumeTrue(server.getManager().getPlanner() instanceof SQLPlanner);
        try (HDBClient client = new HDBClient(new ClientConfiguration(folder.newFolder().toPath()))) {
            client.setClientSideMetadataProvider(new StaticClientSideMetadataProvider(server));
            try (BasicHerdDBDataSource dataSource = new BasicHerdDBDataSource(client);
                Connection con = dataSource.getConnection();
                Connection con2 = dataSource.getConnection();
                Statement statement = con.createStatement()) {
                statement.execute("DROP TABLE IF EXISTS `sm_machine`;");
                statement.execute("CREATE TABLE `sm_machine` (\n" + "  `ip` varchar(20) NOT NULL DEFAULT '',\n" + "  `firefox_version` int(50) DEFAULT NULL,\n" + "  `chrome_version` int(50) DEFAULT NULL,\n" + "  `ie_version` int(50) DEFAULT NULL,\n" + "  `log` varchar(2000) DEFAULT NULL,\n" + "  `offset` int(50) DEFAULT NULL,\n" + "  PRIMARY KEY (`ip`)\n" + ") ENGINE=InnoDB DEFAULT CHARSET=utf8;");
                int multiInsertCount = statement.executeUpdate("INSERT INTO `sm_machine` VALUES" + "('10.168.10.106',26,36,9,NULL,1)," + "('10.168.10.107',26,31,10,NULL,1)," + "('10.168.10.108',26,36,11,NULL,2)," + "('10.168.10.109',33,38,10,NULL,3)," + "('10.168.10.110',33,38,10,NULL,4)");
                assertEquals(5, multiInsertCount);
                try (PreparedStatement ps = statement.getConnection().prepareStatement("INSERT INTO `sm_machine` VALUES" + "(?,?,?,?,?,?)," + "(?,?,?,?,?,?)," + "(?,?,?,?,?,?)," + "(?,?,?,?,?,?)," + "(?,?,?,?,?,?)")) {
                    int i = 1;
                    ps.setString(i++, "11.168.10.106");
                    ps.setInt(i++, 1);
                    ps.setInt(i++, 1);
                    ps.setString(i++, null);
                    ps.setInt(i++, 1);
                    ps.setInt(i++, 1);
                    ps.setString(i++, "11.168.10.107");
                    ps.setInt(i++, 1);
                    ps.setInt(i++, 1);
                    ps.setString(i++, null);
                    ps.setInt(i++, 1);
                    ps.setInt(i++, 1);
                    ps.setString(i++, "11.168.10.108");
                    ps.setInt(i++, 1);
                    ps.setInt(i++, 1);
                    ps.setString(i++, null);
                    ps.setInt(i++, 1);
                    ps.setInt(i++, 1);
                    ps.setString(i++, "11.168.10.109");
                    ps.setInt(i++, 1);
                    ps.setInt(i++, 1);
                    ps.setString(i++, null);
                    ps.setInt(i++, 1);
                    ps.setInt(i++, 1);
                    ps.setString(i++, "11.168.10.110");
                    ps.setInt(i++, 1);
                    ps.setInt(i++, 1);
                    ps.setString(i++, null);
                    ps.setInt(i++, 1);
                    ps.setInt(i++, 1);
                    ps.execute();
                    int multiInsertCount2 = ps.getUpdateCount();
                    assertEquals(5, multiInsertCount2);
                }
                statement.executeQuery("SELECT ip, `offset` FROM sm_machine WHERE `offset` = 1").close();
                statement.execute("DROP TABLE sm_machine;");
                try {
                    statement.executeQuery("SELECT COUNT(*) FROM sm_machine").close();
                    fail();
                } catch (SQLException err) {
                    assertTrue(err.getMessage().contains(herddb.model.TableDoesNotExistException.class.getName()) || err.getMessage().contains("SM_MACHINE"));
                }
                statement.execute("CREATE TABLE test_odd_names (\n" + "  value varchar(20) NOT NULL DEFAULT '',\n" + "  PRIMARY KEY (`value`)\n" + ")");
                statement.executeUpdate("INSERT INTO test_odd_names VALUES" + "('10.168.10.106')");
                try (ResultSet rs = statement.executeQuery("SELECT `value` FROM test_odd_names WHERE `value` = '10.168.10.106'")) {
                    assertTrue(rs.next());
                    assertEquals("10.168.10.106", rs.getString(1));
                    assertEquals("10.168.10.106", rs.getString("value"));
                }
            }
        }
    }
}