Search in sources :

Example 31 with CreateTableStatement

use of herddb.model.commands.CreateTableStatement in project herddb by diennea.

the class IndexScanRangeTest method noIndexOperation.

private void noIndexOperation(String indexType) 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 table = Table.builder().tablespace("tblspace1").name("t1").column("id", ColumnTypes.STRING).column("n1", ColumnTypes.INTEGER).column("n2", ColumnTypes.INTEGER).column("name", ColumnTypes.STRING).primaryKey("id").build();
        CreateTableStatement st2 = new CreateTableStatement(table);
        manager.executeStatement(st2, StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
        Index index = Index.builder().onTable(table).type(indexType).column("n1", ColumnTypes.INTEGER).build();
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,n2,name) values('a',1,5,'n1')", Collections.emptyList());
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,n2,name) values('b',2,5,'n1')", Collections.emptyList());
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,n2,name) values('c',2,5,'n2')", Collections.emptyList());
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,n2,name) values('d',2,5,'n2')", Collections.emptyList());
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,n2,name) values('e',3,5,'n2')", Collections.emptyList());
        // create index, it will be built using existing data
        CreateIndexStatement st3 = new CreateIndexStatement(index);
        manager.executeStatement(st3, StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
        {
            TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT * FROM tblspace1.t1 WHERE n1<n2", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
            System.out.println("indexOperation:" + scan.getPredicate().getIndexOperation());
            assertNull(scan.getPredicate().getIndexOperation());
            try (DataScanner scan1 = manager.scan(scan, translated.context, TransactionContext.NO_TRANSACTION)) {
                assertEquals(5, scan1.consume().size());
            }
        }
    }
}
Also used : Table(herddb.model.Table) TranslatedQuery(herddb.sql.TranslatedQuery) MemoryDataStorageManager(herddb.mem.MemoryDataStorageManager) CreateTableStatement(herddb.model.commands.CreateTableStatement) CreateIndexStatement(herddb.model.commands.CreateIndexStatement) Index(herddb.model.Index) CreateTableSpaceStatement(herddb.model.commands.CreateTableSpaceStatement) DataScanner(herddb.model.DataScanner) MemoryCommitLogManager(herddb.mem.MemoryCommitLogManager) MemoryMetadataStorageManager(herddb.mem.MemoryMetadataStorageManager) ScanStatement(herddb.model.commands.ScanStatement)

Example 32 with CreateTableStatement

use of herddb.model.commands.CreateTableStatement in project herddb by diennea.

the class IndexScanRangeTest method secondaryIndexPrefixScan.

private void secondaryIndexPrefixScan(String indexType) 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 table = Table.builder().tablespace("tblspace1").name("t1").column("id", ColumnTypes.STRING).column("n1", ColumnTypes.INTEGER).column("n2", ColumnTypes.INTEGER).column("name", ColumnTypes.STRING).primaryKey("id").build();
        CreateTableStatement st2 = new CreateTableStatement(table);
        manager.executeStatement(st2, StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
        Index index = Index.builder().onTable(table).type(indexType).column("n1", ColumnTypes.INTEGER).column("n2", ColumnTypes.INTEGER).build();
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,n2,name) values('a',1,5,'n1')", Collections.emptyList());
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,n2,name) values('b',2,5,'n1')", Collections.emptyList());
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,n2,name) values('c',2,5,'n2')", Collections.emptyList());
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,n2,name) values('d',2,5,'n2')", Collections.emptyList());
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,n2,name) values('e',3,5,'n2')", Collections.emptyList());
        // create index, it will be built using existing data
        CreateIndexStatement st3 = new CreateIndexStatement(index);
        manager.executeStatement(st3, StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
        {
            TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT * FROM tblspace1.t1 WHERE n1=2", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
            assertTrue(scan.getPredicate().getIndexOperation() instanceof SecondaryIndexPrefixScan);
            try (DataScanner scan1 = manager.scan(scan, translated.context, TransactionContext.NO_TRANSACTION)) {
                assertEquals(3, scan1.consume().size());
            }
        }
    }
}
Also used : Table(herddb.model.Table) TranslatedQuery(herddb.sql.TranslatedQuery) MemoryDataStorageManager(herddb.mem.MemoryDataStorageManager) CreateTableStatement(herddb.model.commands.CreateTableStatement) CreateIndexStatement(herddb.model.commands.CreateIndexStatement) Index(herddb.model.Index) CreateTableSpaceStatement(herddb.model.commands.CreateTableSpaceStatement) DataScanner(herddb.model.DataScanner) MemoryCommitLogManager(herddb.mem.MemoryCommitLogManager) SecondaryIndexPrefixScan(herddb.index.SecondaryIndexPrefixScan) MemoryMetadataStorageManager(herddb.mem.MemoryMetadataStorageManager) ScanStatement(herddb.model.commands.ScanStatement)

Example 33 with CreateTableStatement

use of herddb.model.commands.CreateTableStatement in project herddb by diennea.

the class TablespaceReplicasStateTest method test_leader_online_log_available.

@Test
public void test_leader_online_log_available() throws Exception {
    ServerConfiguration serverconfig_1 = new ServerConfiguration(folder.newFolder().toPath());
    serverconfig_1.set(ServerConfiguration.PROPERTY_NODEID, "server1");
    serverconfig_1.set(ServerConfiguration.PROPERTY_PORT, 7867);
    serverconfig_1.set(ServerConfiguration.PROPERTY_MODE, ServerConfiguration.PROPERTY_MODE_CLUSTER);
    serverconfig_1.set(ServerConfiguration.PROPERTY_ZOOKEEPER_ADDRESS, testEnv.getAddress());
    serverconfig_1.set(ServerConfiguration.PROPERTY_ZOOKEEPER_PATH, testEnv.getPath());
    serverconfig_1.set(ServerConfiguration.PROPERTY_ZOOKEEPER_SESSIONTIMEOUT, testEnv.getTimeout());
    serverconfig_1.set(ServerConfiguration.PROPERTY_ENFORCE_LEADERSHIP, false);
    ServerConfiguration serverconfig_2 = serverconfig_1.copy().set(ServerConfiguration.PROPERTY_NODEID, "server2").set(ServerConfiguration.PROPERTY_BASEDIR, folder.newFolder().toPath().toAbsolutePath()).set(ServerConfiguration.PROPERTY_PORT, 7868);
    try (Server server_1 = new Server(serverconfig_1)) {
        server_1.start();
        server_1.waitForStandaloneBoot();
        Table table = Table.builder().name("t1").column("c", ColumnTypes.INTEGER).primaryKey("c").build();
        server_1.getManager().executeStatement(new CreateTableStatement(table), StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
        try (Server server_2 = new Server(serverconfig_2)) {
            server_2.start();
            server_1.getManager().executeStatement(new AlterTableSpaceStatement(TableSpace.DEFAULT, new HashSet<>(Arrays.asList("server1", "server2")), "server1", 2, 0), StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
            assertTrue(server_2.getManager().waitForTablespace(TableSpace.DEFAULT, 60000, false));
            try (DataScanner scan = scan(server_1.getManager(), "SELECT * FROM systablespacereplicastate", Collections.emptyList())) {
                List<DataAccessor> results = scan.consume();
                assertEquals(2, results.size());
            }
            try (DataScanner scan = scan(server_1.getManager(), "SELECT * FROM systablespacereplicastate " + "where nodeId='" + server_2.getNodeId() + "' and mode='follower'", Collections.emptyList())) {
                assertEquals(1, scan.consume().size());
            }
            try (DataScanner scan = scan(server_2.getManager(), "SELECT * FROM systablespacereplicastate " + "where nodeId='" + server_2.getNodeId() + "' and mode='follower'", Collections.emptyList())) {
                assertEquals(1, scan.consume().size());
            }
            try (DataScanner scan = scan(server_1.getManager(), "SELECT * FROM systablespacereplicastate " + "where nodeId='" + server_1.getNodeId() + "' and mode='leader'", Collections.emptyList())) {
                assertEquals(1, scan.consume().size());
            }
            server_1.getManager().executeStatement(new AlterTableSpaceStatement(TableSpace.DEFAULT, new HashSet<>(Arrays.asList("server1")), "server1", 1, 0), StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
            for (int i = 0; i < 100; i++) {
                try (DataScanner scan = scan(server_1.getManager(), "SELECT * FROM systablespacereplicastate where nodeId='" + server_2.getNodeId() + "' and mode='stopped'", Collections.emptyList())) {
                    if (scan.consume().size() > 0) {
                        break;
                    }
                }
            }
            try (DataScanner scan = scan(server_1.getManager(), "SELECT * FROM systablespacereplicastate where nodeId='" + server_2.getNodeId() + "' and mode='stopped'", Collections.emptyList())) {
                assertEquals(1, scan.consume().size());
            }
        }
    }
}
Also used : AlterTableSpaceStatement(herddb.model.commands.AlterTableSpaceStatement) Table(herddb.model.Table) DataScanner(herddb.model.DataScanner) DataAccessor(herddb.utils.DataAccessor) CreateTableStatement(herddb.model.commands.CreateTableStatement) HashSet(java.util.HashSet) Test(org.junit.Test)

Example 34 with CreateTableStatement

use of herddb.model.commands.CreateTableStatement in project herddb by diennea.

the class CollectionsManager method createTable.

private Table createTable(String tmpTableName, int pkType) throws StatementExecutionException {
    Table table = Table.builder().name(tmpTableName).column("pk", pkType).column("v", ColumnTypes.INTEGER).primaryKey("pk").tablespace(TableSpace.DEFAULT).build();
    CreateTableStatement createTable = new CreateTableStatement(table);
    tableSpaceManager.executeStatement(createTable, StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
    return table;
}
Also used : Table(herddb.model.Table) CreateTableStatement(herddb.model.commands.CreateTableStatement)

Example 35 with CreateTableStatement

use of herddb.model.commands.CreateTableStatement in project herddb by diennea.

the class JSQLParserPlanner method buildCreateTableStatement.

private Statement buildCreateTableStatement(String defaultTableSpace, CreateTable s) throws StatementExecutionException {
    String tableSpace = fixMySqlBackTicks(s.getTable().getSchemaName());
    String tableName = fixMySqlBackTicks(s.getTable().getName());
    if (tableSpace == null) {
        tableSpace = defaultTableSpace;
    }
    if (s.getColumnDefinitions() == null) {
        throw new StatementExecutionException("A table must have at least 1 column");
    }
    final boolean isNotExsists = s.isIfNotExists();
    try {
        boolean foundPk = false;
        Table.Builder tablebuilder = Table.builder().uuid(UUID.randomUUID().toString()).name(tableName).tablespace(tableSpace);
        Set<String> primaryKey = new HashSet<>();
        Set<String> simpleUniqueFields = new HashSet<>();
        if (s.getIndexes() != null) {
            for (Index index : s.getIndexes()) {
                if (index.getType().equalsIgnoreCase("PRIMARY KEY")) {
                    for (String n : index.getColumnsNames()) {
                        n = fixMySqlBackTicks(n.toLowerCase());
                        tablebuilder.primaryKey(n);
                        primaryKey.add(n);
                        foundPk = true;
                    }
                }
            }
        }
        int position = 0;
        for (ColumnDefinition cf : s.getColumnDefinitions()) {
            String columnName = fixMySqlBackTicks(cf.getColumnName().toLowerCase());
            int type;
            String dataType = cf.getColDataType().getDataType();
            List<String> columnSpecs = decodeColumnSpecs(cf.getColumnSpecs());
            type = sqlDataTypeToColumnType(dataType, cf.getColDataType().getArgumentsStringList(), columnSpecs);
            Bytes defaultValue = decodeDefaultValue(cf, type);
            if (!columnSpecs.isEmpty()) {
                boolean auto_increment = decodeAutoIncrement(columnSpecs);
                if (columnSpecs.contains("PRIMARY")) {
                    foundPk = true;
                    tablebuilder.primaryKey(columnName, auto_increment);
                }
                if (auto_increment && primaryKey.contains(columnName)) {
                    tablebuilder.primaryKey(columnName, auto_increment);
                }
                boolean isUnique = columnSpecs.contains("UNIQUE");
                if (isUnique) {
                    simpleUniqueFields.add(columnName);
                }
            }
            tablebuilder.column(columnName, type, position++, defaultValue);
        }
        if (!foundPk) {
            tablebuilder.column("_pk", ColumnTypes.LONG, position++, null);
            tablebuilder.primaryKey("_pk", true);
        }
        Table table = tablebuilder.build();
        List<herddb.model.Index> otherIndexes = new ArrayList<>();
        List<herddb.model.ForeignKeyDef> foreignKeys = new ArrayList<>();
        if (s.getIndexes() != null) {
            for (Index index : s.getIndexes()) {
                if (index.getType().equalsIgnoreCase("PRIMARY KEY")) {
                } else if (index.getType().equalsIgnoreCase("INDEX") || index.getType().equalsIgnoreCase("KEY") || index.getType().equalsIgnoreCase("UNIQUE KEY")) {
                    String indexName = fixMySqlBackTicks(index.getName().toLowerCase());
                    String indexType = convertIndexType(null);
                    boolean unique = index.getType().equalsIgnoreCase("UNIQUE KEY");
                    herddb.model.Index.Builder builder = herddb.model.Index.builder().onTable(table).name(indexName).unique(unique).type(indexType).uuid(UUID.randomUUID().toString());
                    for (String columnName : index.getColumnsNames()) {
                        columnName = fixMySqlBackTicks(columnName.toLowerCase());
                        Column column = table.getColumn(columnName);
                        if (column == null) {
                            throw new StatementExecutionException("no such column " + columnName + " on table " + tableName + " in tablespace " + tableSpace);
                        }
                        builder.column(column.name, column.type);
                    }
                    otherIndexes.add(builder.build());
                } else if (index.getType().equals("FOREIGN KEY")) {
                    ForeignKeyIndex fk = (ForeignKeyIndex) index;
                    ForeignKeyDef fkDef = parseForeignKeyIndex(fk, table, tableName, tableSpace);
                    foreignKeys.add(fkDef);
                } else {
                    throw new StatementExecutionException("Unsupported index type " + index.getType());
                }
            }
        }
        for (String col : simpleUniqueFields) {
            herddb.model.Index.Builder builder = herddb.model.Index.builder().onTable(table).name(table.name + "_unique_" + col).unique(true).type(herddb.model.Index.TYPE_BRIN).uuid(UUID.randomUUID().toString()).column(col, table.getColumn(col).type);
            otherIndexes.add(builder.build());
        }
        if (!foreignKeys.isEmpty()) {
            table = table.withForeignKeys(foreignKeys.toArray(new ForeignKeyDef[0]));
        }
        CreateTableStatement statement = new CreateTableStatement(table, otherIndexes, isNotExsists);
        return statement;
    } catch (IllegalArgumentException err) {
        throw new StatementExecutionException("bad table definition: " + err.getMessage(), err);
    }
}
Also used : Table(herddb.model.Table) ShowCreateTableCalculator.calculateShowCreateTable(herddb.sql.functions.ShowCreateTableCalculator.calculateShowCreateTable) CreateTable(net.sf.jsqlparser.statement.create.table.CreateTable) CreateTableStatement(herddb.model.commands.CreateTableStatement) ArrayList(java.util.ArrayList) CreateIndex(net.sf.jsqlparser.statement.create.index.CreateIndex) ForeignKeyIndex(net.sf.jsqlparser.statement.create.table.ForeignKeyIndex) Index(net.sf.jsqlparser.statement.create.table.Index) StatementExecutionException(herddb.model.StatementExecutionException) ColumnDefinition(net.sf.jsqlparser.statement.create.table.ColumnDefinition) Bytes(herddb.utils.Bytes) Column(herddb.model.Column) ForeignKeyDef(herddb.model.ForeignKeyDef) ForeignKeyIndex(net.sf.jsqlparser.statement.create.table.ForeignKeyIndex) HashSet(java.util.HashSet)

Aggregations

CreateTableStatement (herddb.model.commands.CreateTableStatement)183 Table (herddb.model.Table)180 Test (org.junit.Test)164 CreateTableSpaceStatement (herddb.model.commands.CreateTableSpaceStatement)111 InsertStatement (herddb.model.commands.InsertStatement)102 DataScanner (herddb.model.DataScanner)82 CreateIndexStatement (herddb.model.commands.CreateIndexStatement)72 GetStatement (herddb.model.commands.GetStatement)72 Index (herddb.model.Index)71 GetResult (herddb.model.GetResult)68 ScanStatement (herddb.model.commands.ScanStatement)68 TranslatedQuery (herddb.sql.TranslatedQuery)68 Path (java.nio.file.Path)61 MemoryCommitLogManager (herddb.mem.MemoryCommitLogManager)57 MemoryMetadataStorageManager (herddb.mem.MemoryMetadataStorageManager)57 TransactionContext (herddb.model.TransactionContext)56 MemoryDataStorageManager (herddb.mem.MemoryDataStorageManager)54 Bytes (herddb.utils.Bytes)51 SecondaryIndexSeek (herddb.index.SecondaryIndexSeek)50 Record (herddb.model.Record)44