Indexed data modification and query help documentation

OnceDB by gh_newghost on 1579357060684


OnceDB is a high-performance full-text search database based on Redis. OnceDB can greatly improve the performance of data queries through auxiliary indexes. Like SQL databases, you don't need to care about the details of index creation. OnceDB has very good performance on low-end ARM devices.

OnceDB uses operators to dynamically define indexes. OnceDB do not changing the data storage structure of Redis. You can use existing Redis tools to view and manage the data in OnceDB.

Data modify

upsert schema field operator value ...

insert: Insert non-existent hash records
update: Update existing hash records
upsert: Update hash record, add if not exist

Operator

OnceDB uses operators to create secondary indexes.

@ : Primary Key

Primary key is required when inserting data

insert article id @ 001 poster = kris
> OK

Inserted data, cannot be duplicated

insert article id @ 001 poster = kris
> "ERR article:001 already exist"

The new data created using insert / upsert will automatically create a primary key index:

keys *
1) *article
2) article:001

*article is the primary key index

zrange *article 0 1000 withscores
1) 001
2) 1579691881040

article:001 is HASH data

hgetall article:001
1) id
2) 001
3) poster
4) kris

The weight score is an integer value of when the data was updated. It can be converted into a datetime.

new Date(1577851291213).toDateString()
> "Wed Jan 22 2020"

/ : Sorting

Sort fields.

upsert article id @ 001 visit / 10
> 10
upsert article id @ 002 visit / 20
> 10

*article.visit index will be created automatically:

zrange *article.visit 0 10 withscores
1) 001
2) 10
3) 002
4) 20

Weight score is field value, value must be number:

upsert article id @ 001 visit / abc
> ERR not a numeric type abc

? : Grouping

Group by field

upsert article id @ 001 title = test1 poster ? kris

*article.poster:kris index is created automatically

zrange *article.poster:kris 0 -1 withscores
1) 001
2) 1577887802767

The weight score is an integer value of when the data was updated.

* : Keyword grouping

Keywords separated by commas: ","

upsert article id @ 002 title = "Testing article" keys * python,go,node.js

Indexes: *article.keys:python, *article.keys:go, *article.keys:node.js are created automatically

zrange *article.keys:python 0 -1 withscores
1) 002
2) 1577890088545

^ : Unique key

Non-repeatable field

upsert user username @ dota email ^ god@like.com
> OK

Data duplication validate

upsert user username @ test email ^ god@like.com
> "ERR ^ unique key error *user.email"

*user.email HASH record is created automatically:

hgetall *user.email
1) god@like.com
2) dota

Unique field is not an index.

Weight score update method

"-" : Do not update score if exist

By default, the weight score of index is automatically updated with each update. You can use "*-" to not update the existing score.

upsert employee id @ 003 keys * js
> OK

zrange *employee.keys:js 0 -1 withscores
1) 003
2) 1577953569271

Using "*-" to update

upsert employee id @ 003 keys *- go,js
> OK

"go" is new added keyword, the score will be different with "js"

zrange *employee.keys:go 0 -1 withscores
1) 003
2) 1577953576085

zrange *employee.keys:js 0 -1 withscores
1) 003
2) 1577953569271

The score of "js" remains the same.

You can use "-" in the following cases:

@- Primary key
?- Grouping
*- Keyword grouping

"=" : Custom weight score

Use the given value instead of the default timestamp as the weight score

upsert blog id @=1000 TEST poster ?=2000 kris keys *=3000 go,js

zrange *blog 0 -1 withscores
1) TEST
2) 10000

zrange *blog.poster:kris 0 -1 withscores
1) TEST
2) 2000

zrange *blog.keys:go 0 -1 withscores
1) TEST
2) 3000

@-: Dot not update pirmary key index

upsert blog id @-=3002 TEST

zrange *blog 0 -1 withscores
1) TEST
2) 1000

"@" : Custom index name

Use @ to specify index name in operator

upsert group name @@groups oncedb owner ?@groups_owner kris visit /@groups_visits 1

zrange groups 0 -1 withscores
1) oncedb
2) 1578031816226

zrange groups_owner:kris 0 -1 withscores
1) oncedb
2) 1578031816226

zrange groups_visits 0 -1 withscores
1) oncedb
2) 1

Data deletion

remove schema @ id field operator ...

OnceDB does not store index definitions and you need to manually indicate which fields have indexes when deleting.

Remove data by id

Data needs to be deleted by ID

upsert people id @ 001
> ok

remove people @ 001
> 1

Remove data and indexes

You created the indexed data

upsert people id @ 001 role ? DEV groups * "sun,earth" age / 20    
> ok

Remove this way

remove people @ 001 role ? groups * age /
> 1

Remove the custom index

upsert article id @article 001 poster ?@auser kris keys *@akeys go,js ctime /@actime 20200101
> ok

remove article @article 001 poster ?@auser keys *@akeys ctime /@actime
> 1

Data query

find schema from to field operator value ...

Querying data with the FIND instruction

Create test data

upsert issue id @ 1 summary = "This is issue 1" creator ? dota keys * go,node.js ctime / 20200101 email ^ go@1.cn
upsert issue id @ 2 summary = "This is issue 2" creator ? like keys * c,node.js ctime / 20200102 email ^ go@2.cn
upsert issue id @ 3 summary = "This is issue 3" creator ? dota keys * c,node.js ctime / 20200103 email ^ go@3.cn
upsert issue id @ 4 summary = "This is issue 4" creator ? dota keys * go,c,node.js ctime / 20200104 email ^ go@4.cn

By range

Select the first two data of "issue", select the summary, creator, keys fields

find issue 0 1 summary = * creator = * keys = *
1)  "4"                 # 4 means that there are 4 data in the "issue".
2)  "issue:1"           # Hash key name
3)  "This is issue 1"
4)  "dota"
5)  "go,node.js"
6)  "issue:2"
7)  "This is issue 2"
8)  "like"
9)  "c,node.js"

If you query by index, the first number returned is the total number of data, not the number of data returned.

Descending

The first parameter is negative, which means use reverse order

find issue -1 -2 summary = * keys = *
1)  "4"
2)  "issue:4"
3)  "This is issue 4"
4)  "go,c,node.js"
5)  "issue:3"
6)  "This is issue 3"
7)  "c,node.js"

-1 is the first from the bottom, 0 is the first from the positive. Output all elements in reverse order:

find issue -1 0  summary = * keys = *
1) 4
2) issue:4
3) This is issue 4
4) go,c,node.js
5) issue:3
6) This is issue 3
7) c,node.js
8) issue:2
9) This is issue 2
10) c,node.js
11) issue:1
12) This is issue 1
13) go,node.js

Fulltext search

Use "=" exact match or "~" partial match.

find issue 0 -1 id = 1 keys ~ go
1)  "-1"
2)  "issue:1"
3)  "1"
4)  "go,node.js"

The first parameter in the result is always -1 if you use fulltext search.

By primary key

Select by primary key and summary field

find issue 0 1 id @ 2 summary = *
1)  "-1"
2)  "issue:2"
3)  "2"

By unique key

Select by unique key

find issue 0 1 email ^ go@3.cn summary = *
1)  "-1"
2)  "issue:3"
3)  "go@3.cn"
4)  "This is issue 3"

"@": Score range

Use @score for score range

find schema @min @max field operator value ...

Example:

find issue @20200102 @20200103 ctime / * summary = *
1)  "2"
2)  "issue:2"
3)  "20200102"
4)  "This is issue 2"
5)  "issue:3"
6)  "20200103"
7)  "This is issue 3"

Select range in score

find issue 1@20200102 1@20200103 ctime / * summary = *
1)  "2"
2)  "issue:3"
3)  "20200103"
4)  "This is issue 3"

By multiple indexes

find issue 0 10 creator ? dota keys * go ctime / *
1)  "2"
2)  "issue:1"
3)  "dota"
4)  "go,node.js"
5)  "20200101"
6)  "issue:4"
7)  "dota"
8)  "go,c,node.js"
9)  "20200104"

"+": Specify which fields' score weight to use

find issue 0@20200102 10@20200105 creator ? dota keys * go ctime /+ *
1)  "1"
2)  "issue:4"
3)  "dota"
4)  "go,c,node.js"
5)  "20200104"

"-": Specify which fields' score weight not to use

find issue 0@20200102 10@20200105 creator ?- dota keys *- go ctime / *
1)  "1"
2)  "issue:4"
3)  "dota"
4)  "go,c,node.js"
5)  "20200104"

"=>": Output to index

Save search results(ID) to an ordered index

find schema=>[index] from to field operator value ...

Temporary index

If no index name is specified, a temporary index is automatically generated

find issue=> 0@20200102 10@20200105 creator ?- dota keys *- go ctime / *
> "*issue.1583844132294743"

zrange *issue.1583844132294743 0 -1 withscores
 1)  "4"
 2)  "20200104"

The temporary index will be deleted after one hour

Specify the index name

Specify the name of the index, the index is permanent

find issue=>issue_dota_go 0@20200102 10@20200105 creator ?- dota keys *- go ctime / *
> "issue_dota_go"

Does not generate indexes

Query only one condition, it will return the existing index

find issue=>issue_dota_go 0 -1 creator ? dota
> "*issue.creator:dota"

Reverse order (desc)

from is negative, use reverse order

find issue -1 0 ctime / *
1) 4
2) issue:4
3) 20200104
4) issue:3
5) 20200103
6) issue:2
7) 20200102
8) issue:1
9) 20200101

find issue -1@20200102 0@20200104 ctime / *
1) 3
2) issue:4
3) 20200104
4) issue:3
5) 20200103
6) issue:2
7) 20200102

Previous article: OnceDB quick start Next acticle: Search, query, calculate, and sum instructions