DB Commands

DB Commands / Queries USING CLI

DB Commands – 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

To see help

help db

To see all the tables

show tables

To see all the user tables

show tables only user

Similarly to see all the sys tables

show tables only sys

DB Commands – 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

DB Commands – To describe any table

Let’s say ml_bucket_info, which is a sys table

describe table ml_bucket_info pretty

DB Commands – To create a table

BangDB provides plenty of different options to create a table.
Therefore table creation is a workflow which gets initiated when we issue the “create table …” command.

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)

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

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 keyes/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