When we run the CLI it's connected with the default database. The database name could be provided by the command line argument when running the cli or else it will use “mydb” as the database to connect to. At a time BangDB runs only one database, hence we can switch to different db from cli. Once we run the cli, we can perform certain activities in different areas, however this page is focusing on DB related activities.

DB Commands using CLI

To see help

help db

To see all the tables

show  tables

To see all the user tables

show tables only user

To see all the sys tables

show tables only sys

To describe the database, get the details of the db. (This will flush a json on the screen)

describe database

To see the pretty format of the json

describe database pretty

To describe any table (Let's say ml_bucket_info, which is a sys table)

describe table ml_bucket_info pretty

To create a table

To select default values, simply enter when prompted.

create table mytable
Table Type [NORMAL_TABLE, KV (0) | WIDE_TABLE, Documents (1) | LARGE_TABLE, large objects/files (2) | PREMITIVE_TABLE, like column (3)] (or Enter for default (0)):

Here as we see we need to select the table type.

Next set of workflow will be based on the selection here. Let's say we select 1 here, WIDE_TABLE

Is it a SW(Sliding Window) table? y/n (or Enter for default (n)):

We can put this table in a sliding window if we wish, good for use cases where we wish to discard data after some time, Let's say we select NO (n)

allow reverse index as well? y/n (or Enter for default (n)):

Do you want to enable reverse index for the table, let's select Yes (y) here

allow duplicate primary keys as well? y/n (or Enter for default (n)):

Primary keys are always indexed within BangDB, for all kinds of table. This helps in query using the primary keys. However, we can allow primary keys to be duplicated as well. Let's select Yes (y) here

Key type [NORMAL_KEY(string type) (0) | COMPOSITE_KEY(also string type) (1) | NORMAL_KEY_LONG (long type) (2)] (or Enter for default (0)):

We can define different kinds of keys for primary key.

Let's select NORMAL_KEY (0)

key size in num of bytes (at least 8 bytes, max 128 bytes, as low as possible but high enough for the key) (or Enter for default (24)):

Let's go with default 24 bytes

primary key arrangement (index) type [ BTREE (2) | EXTHASH (1) ] (or Enter for default (2)):

Primary keys could be arranged in sorted (BTREE, actually B+ExtTree) manner or hashed (EXTHASH). Let's go with BTREE as it's a good choice most of the time

Method for key sort [ Lexicographically (1) | Quasi Lexicographically (2) ] (or Enter for default (2)):

We can further tell how to arrange if sorted, let's go with default

Direction for key sort [ Ascending (3) | Descending (4) ] (or Enter for default (3)):

Let's go with ascending, default Now it will flush our selection on the terminal and ask for confirmation.

table config set is as follows;
db type = 1,
idx type = 2,
table type = 1,
key type = 1,
table_sub type = 7,
allow_duplicate = 1,
allow_rev_idx = 1, sort_method = 2,
sort_direction = 3,
key_sz = 24, wal_enabled = 1,
log_sz_mb = 128 
Please type 'a' for abort or 'c' for commit [ a | c ]:

Press c to commit, table should be created. You can check the details of the table by using "describe table mytable" command

To create index on the table

Let's create index on the firstname for the mytable table

create index mytable.firstname

Index creation is very similar to table creation, it also creates a similar workflow

Key Type [NORMAL_KEY (1) | NORMAL_KEY_LONG (2)] (or Enter to set default (1)):

Let's select NORMAL_KEY (1)

Key size (or Enter to set default (24)):

Select 24 as key size

Sort direction [SORT_ASCENDING(3) | SORT_DESCENDING(4)] (or Enter to set default (3)):

Select default 3

Allow duplicate indexes as well? y/n: (or Enter for default (n))

Select yes (y) for duplicate index finally it asks for confirmation before commit

Please type 'a' for abort or 'c' for commit [ a | c ]: 
// select c for commit and it will create the index

Insert few data into the table

insert into mytable values "user1" {
  "firstname":"sachin", 
  "org":"bangdb",
  "city":"bangalore"
}

select data from the table now

select * from mytable
// It will return something like this
+---------+------------------------------------------------------------------------------+
|key      |val                                                                           | 
+---------+------------------------------------------------------------------------------+
|user1    |{"firstname":"sachin","org":"bangdb","city":"bangalore","_pk":"user1","_v":1} | 
+---------+------------------------------------------------------------------------------+
total rows retrieved = 1 (1)

We can also scan using firstname

select * from mytable where firstname = "sachin"

+---------+-------------------------------------------------------------------------------+ 
|key      | val                                                                           |
+---------+-------------------------------------------------------------------------------+
|user1    | {"firstname":"sachin","org":"bangdb","city":"bangalore","_pk":"user1","_v":1} | 
+---------+-------------------------------------------------------------------------------+ 
total rows retrieved = 1 (1)

Even though we didn't create index on "org", still we can scan for this

select * from mytable where org = "bangdb"
+---------+-------------------------------------------------------------------------------+
|key      | val                                                                           | 
+---------+-------------------------------------------------------------------------------+
|user1    | {"firstname":"sachin","org":"bangdb","city":"bangalore","_pk":"user1","_v":1} | 
+---------+-------------------------------------------------------------------------------+ 
total rows retrieved = 1 (1)

We can use primary keys for select along with other filter

select * from mytable where _pk > "user" and org = "bangdb"

If we wish to limit the number of rows to be returned then we use "limit n" where n is number of rows Default value of limit is 10

select * from mytable where _pk > "user" and org = "bangdb" limit 20

Let's use reverse index now, since we enabled them during table creation. But for this let's insert few docs and reverse index few keys/fields

insert into mytable values "user1" {
  "firstname":"sachin", 
  "org":"bangdb",
  "city":"bangalore",
  "fav_quote":"Truth is ever to be found in simplicity, and not in the multiplicity and confusion of things"
  }
}
revidx fav_quote

Now we will use reverse index based search, the query again looks similar. We wish to select all the rows where "fav_quote" field contains "Truth", "confusion" and "simplicity" tokens.

select * from mytable where fav_quote = "Truth, confusion, simplicity" scanning for pk range [null : null] and query = {"query":[{"match_words":"Truth, confusion, simplicity","joinop":1,"field":"fav_quote"}]}
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
|key      |val                                                                                                                                                                                            | 
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
|user1    |{"firstname":"sachin","org":"bangdb","city":"bangalore","fav_quote":"Truth is ever to be found in simplicity, | | | and not in the multiplicity and confusion of things","_pk":"user1","_v":1} | 
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
total rows retrieved = 1 (1)

Count number of records

select count(*) from mytable

We can add all those filters that we can for select query, for example:

select count(*) from mytable where _pk > "user"

Update the record

update mytable set val = {"name":"sachin sinha","city":"delhi"} where _pk = "user1" and city = "delhi"

To delete a key (we can give all those filters that we can for select query)

delete from mytable where _pk = "user1"

Dump the table on disk (force to write it on disk)

dump table mytable table
mytable dumped successfully

Drop the index

drop index mytable.firstname
you are going to permanently drop and delete the index files do you still wish to drop the index...? [ yes | no ]: yes 
dropping index mytable.firstname ... 
Index mytable.firstname dropped successfully

Drop the table now, this will permanently delete the table

drop table mytable
you are going to permanently drop and delete the table files you may close the table and move the table files as archive instead do you still wish to drop the table...? [ yes | no ]: yes
dropping table mytable ... 
table mytable dropped successfully