README.md
1# EJDB 2.0
2
3[![Join Telegram](https://img.shields.io/badge/join-ejdb2%20telegram-0088cc.svg)](https://tlg.name/ejdb2)
4[![license](https://img.shields.io/github/license/Softmotions/ejdb.svg)](https://github.com/Softmotions/ejdb/blob/master/LICENSE)
5![maintained](https://img.shields.io/maintenance/yes/2021.svg)
6
7EJDB2 is an embeddable JSON database engine published under MIT license.
8
9[The Story of the IT-depression, birds and EJDB 2.0](https://medium.com/@adamansky/ejdb2-41670e80897c)
10
11* C11 API
12* Single file database
13* Online backups support
14* 500K library size for Android
15* [iOS](https://github.com/Softmotions/EJDB2Swift) / [Android](https://github.com/Softmotions/ejdb/tree/master/src/bindings/ejdb2_android/test) / [React Native](https://github.com/Softmotions/ejdb/tree/master/src/bindings/ejdb2_react_native) / [Flutter](https://github.com/Softmotions/ejdb/tree/master/src/bindings/ejdb2_flutter) integration
16* Simple but powerful query language (JQL) as well as support of the following standards:
17 * [rfc6902](https://tools.ietf.org/html/rfc6902) JSON Patch
18 * [rfc7386](https://tools.ietf.org/html/rfc7386) JSON Merge patch
19 * [rfc6901](https://tools.ietf.org/html/rfc6901) JSON Path
20* [Support of collection joins](#jql-collection-joins)
21* Powered by [iowow.io](http://iowow.io) - The persistent key/value storage engine
22* Provides HTTP REST/Websockets network endpoints with help of [facil.io](http://facil.io)
23* JSON documents are stored in using fast and compact [binn](https://github.com/liteserver/binn) binary format
24
25---
26* [Native language bindings](#native-language-bindings)
27* Supported platforms
28 * [OSX](#osx)
29 * [iOS](https://github.com/Softmotions/EJDB2Swift)
30 * [Linux](#linux)
31 * [Android](#android)
32 * [Windows](#windows)
33* **[JQL query language](#jql)**
34 * [Grammar](#jql-grammar)
35 * [Quick into](#jql-quick-introduction)
36 * [Data modification](#jql-data-modification)
37 * [Projections](#jql-projections)
38 * [Collection joins](#jql-collection-joins)
39 * [Sorting](#jql-sorting)
40 * [Query options](#jql-options)
41* [Indexes and performance](#jql-indexes-and-performance-tips)
42* [Network API](#http-restwebsocket-api-endpoint)
43 * [HTTP API](#http-api)
44 * [Websockets API](#websocket-api)
45* [C API](#c-api)
46* [License](#license)
47---
48
49## EJDB2 platforms matrix
50
51| | Linux | macOS | iOS | Android | Windows |
52| --- | --- | --- | --- | --- | --- |
53| C library | :heavy_check_mark: | :heavy_check_mark: | :heavy_check_mark: | :heavy_check_mark: | :heavy_check_mark:<sup>1</sup> |
54| NodeJS | :heavy_check_mark: | :heavy_check_mark: | | | :x:<sup>3</sup> |
55| DartVM | :heavy_check_mark: | :heavy_check_mark:<sup>2</sup> | | | :x:<sup>3</sup> |
56| Flutter | | | :heavy_check_mark: | :heavy_check_mark: | |
57| React Native | | | :x:<sup>4</sup> | :heavy_check_mark: | |
58| Swift | :heavy_check_mark: | :heavy_check_mark: | :heavy_check_mark: | | |
59| Java | :heavy_check_mark: | :heavy_check_mark: | | :heavy_check_mark: | :heavy_check_mark:<sup>2</sup> |
60
61
62<br> `[1]` No HTTP/Websocket support [#257](https://github.com/Softmotions/ejdb/issues/257)
63<br> `[2]` Binaries are not distributed with dart `pub.` You can build it [manually](https://github.com/Softmotions/ejdb/tree/master/src/bindings/ejdb2_node#how-build-it-manually)
64<br> `[3]` Can be build, but needed a linkage with windows node/dart `libs`.
65<br> `[4]` Porting in progress [#273](https://github.com/Softmotions/ejdb/issues/273)
66
67
68## Native language bindings
69
70* [NodeJS](https://www.npmjs.com/package/ejdb2_node)
71* [Dart](https://pub.dartlang.org/packages/ejdb2_dart)
72* [Java](https://github.com/Softmotions/ejdb/blob/master/src/bindings/ejdb2_jni/README.md)
73* [Android support](#android)
74* [Swift | iOS](https://github.com/Softmotions/EJDB2Swift)
75* [React Native](https://github.com/Softmotions/ejdb/tree/master/src/bindings/ejdb2_react_native)
76* [Flutter](https://github.com/Softmotions/ejdb/tree/master/src/bindings/ejdb2_flutter)
77
78### Unofficial EJDB2 language bindings
79
80* .Net
81 * https://github.com/kmvi/ejdb2-csharp
82* Haskell
83 * https://github.com/cescobaz/ejdb2haskell
84 * https://hackage.haskell.org/package/ejdb2-binding
85* [Pharo](https://pharo.org)
86 * https://github.com/pharo-nosql/pharo-ejdb
87* Lua
88 * https://github.com/chriku/ejdb-lua
89
90## Status
91
92* **EJDB 2.0 core engine is well tested and used in various heavily loaded deployments**
93* Tested on `Linux` and `OSX` platforms. [Limited Windows support](./WINDOWS.md)
94* Old EJDB 1.x version can be found in separate [ejdb_1.x](https://github.com/Softmotions/ejdb/tree/ejdb_1.x) branch.
95 We are not maintaining ejdb 1.x.
96
97## Use cases
98
99* Softmotions trading robots platform
100* [Gimme - a social toy tokens exchange mobile application.](https://play.google.com/store/apps/details?id=com.softmotions.gimme) EJDB2 is used both on mobile and server sides.
101
102Are you using EJDB? [Let me know!](mailto:info@softmotions.com)
103
104## macOS / OSX
105
106EJDB2 code ported and tested on `High Sierra` / `Mojave` / `Catalina`
107
108See also [EJDB2 Swift binding](https://github.com/Softmotions/EJDB2Swift) for OSX, iOS and Linux
109
110```
111brew install ejdb
112```
113
114or
115
116```
117mkdir build && cd build
118cmake .. -DCMAKE_BUILD_TYPE=Release
119make install
120```
121
122## Linux
123### Ubuntu/Debian
124#### PPA repository
125
126```sh
127sudo add-apt-repository ppa:adamansky/ejdb2
128sudo apt-get update
129sudo apt-get install ejdb2
130```
131
132#### Building debian packages
133
134cmake v3.15 or higher required
135
136```sh
137mkdir build && cd build
138cmake .. -DCMAKE_BUILD_TYPE=Release -DPACKAGE_DEB=ON
139make package
140```
141
142#### RPM based Linux distributions
143```sh
144mkdir build && cd build
145cmake .. -DCMAKE_BUILD_TYPE=Release -DPACKAGE_RPM=ON
146make package
147```
148
149## Windows
150EJDB2 can be cross-compiled for windows
151
152**Note:** HTTP/Websocket network API is disabled and not supported
153on Windows until port of http://facil.io library (#257)
154
155Nodejs/Dart bindings not yet ported to Windows.
156
157**[Cross-compilation Guide for Windows](./WINDOWS.md)**
158
159
160
161# Android
162
163* [Flutter binding](https://github.com/Softmotions/ejdb/tree/master/src/bindings/ejdb2_flutter)
164* [React Native binding](https://github.com/Softmotions/ejdb/tree/master/src/bindings/ejdb2_react_native)
165
166## Sample Android application
167
168* https://github.com/Softmotions/ejdb/tree/master/src/bindings/ejdb2_android/test
169
170* https://github.com/Softmotions/ejdb_android_todo_app
171
172
173# JQL
174
175EJDB query language (JQL) syntax inspired by ideas behind XPath and Unix shell pipes.
176It designed for easy querying and updating sets of JSON documents.
177
178## JQL grammar
179
180JQL parser created created by
181[peg/leg — recursive-descent parser generators for C](http://piumarta.com/software/peg/) Here is the formal parser grammar: https://github.com/Softmotions/ejdb/blob/master/src/jql/jqp.leg
182
183## Non formal JQL grammar adapted for brief overview
184
185Notation used below is based on SQL syntax description:
186
187Rule | Description
188--- | ---
189`' '` | String in single quotes denotes unquoted string literal as part of query.
190<code>{ a | b }</code> | Curly brackets enclose two or more required alternative choices, separated by vertical bars.
191<code>[ ]</code> | Square brackets indicate an optional element or clause. Multiple elements or clauses are separated by vertical bars.
192<code>|</code> | Vertical bars separate two or more alternative syntax elements.
193<code>...</code> | Ellipses indicate that the preceding element can be repeated. The repetition is unlimited unless otherwise indicated.
194<code>( )</code> | Parentheses are grouping symbols.
195Unquoted word in lower case| Denotes semantic of some query part. For example: `placeholder_name` - name of any placeholder.
196```
197QUERY = FILTERS [ '|' APPLY ] [ '|' PROJECTIONS ] [ '|' OPTS ];
198
199STR = { quoted_string | unquoted_string };
200
201JSONVAL = json_value;
202
203PLACEHOLDER = { ':'placeholder_name | '?' }
204
205FILTERS = FILTER [{ and | or } [ not ] FILTER];
206
207 FILTER = [@collection_name]/NODE[/NODE]...;
208
209 NODE = { '*' | '**' | NODE_EXPRESSION | STR };
210
211 NODE_EXPRESSION = '[' NODE_EXPR_LEFT OP NODE_EXPR_RIGHT ']'
212 [{ and | or } [ not ] NODE_EXPRESSION]...;
213
214 OP = [ '!' ] { '=' | '>=' | '<=' | '>' | '<' | ~ }
215 | [ '!' ] { 'eq' | 'gte' | 'lte' | 'gt' | 'lt' }
216 | [ not ] { 'in' | 'ni' | 're' };
217
218 NODE_EXPR_LEFT = { '*' | '**' | STR | NODE_KEY_EXPR };
219
220 NODE_KEY_EXPR = '[' '*' OP NODE_EXPR_RIGHT ']'
221
222 NODE_EXPR_RIGHT = JSONVAL | STR | PLACEHOLDER
223
224APPLY = { 'apply' | 'upsert' } { PLACEHOLDER | json_object | json_array } | 'del'
225
226OPTS = { 'skip' n | 'limit' n | 'count' | 'noidx' | 'inverse' | ORDERBY }...
227
228 ORDERBY = { 'asc' | 'desc' } PLACEHOLDER | json_path
229
230PROJECTIONS = PROJECTION [ {'+' | '-'} PROJECTION ]
231
232 PROJECTION = 'all' | json_path
233
234```
235
236* `json_value`: Any valid JSON value: object, array, string, bool, number.
237* `json_path`: Simplified JSON pointer. Eg.: `/foo/bar` or `/foo/"bar with spaces"/`
238* `*` in context of `NODE`: Any JSON object key name at particular nesting level.
239* `**` in context of `NODE`: Any JSON object key name at arbitrary nesting level.
240* `*` in context of `NODE_EXPR_LEFT`: Key name at specific level.
241* `**` in context of `NODE_EXPR_LEFT`: Nested array value of array element under specific key.
242
243## JQL quick introduction
244
245Lets play with some very basic data and queries.
246For simplicity we will use ejdb websocket network API which provides us a kind of interactive CLI. The same job can be done using pure `C` API too (`ejdb2.h jql.h`).
247
248NOTE: Take a look into [JQL test cases](https://github.com/Softmotions/ejdb/blob/master/src/jql/tests/jql_test1.c) for more examples.
249
250```json
251{
252 "firstName": "John",
253 "lastName": "Doe",
254 "age": 28,
255 "pets": [
256 {"name": "Rexy rex", "kind": "dog", "likes": ["bones", "jumping", "toys"]},
257 {"name": "Grenny", "kind": "parrot", "likes": ["green color", "night", "toys"]}
258 ]
259}
260```
261Save json as `sample.json` then upload it the `family` collection:
262
263```sh
264# Start HTTP/WS server protected by some access token
265./jbs -a 'myaccess01'
2668 Mar 16:15:58.601 INFO: HTTP/WS endpoint at localhost:9191
267```
268
269Server can be accessed using HTTP or Websocket endpoint. [More info](https://github.com/Softmotions/ejdb/blob/master/src/jbr/README.md)
270
271```sh
272curl -d '@sample.json' -H'X-Access-Token:myaccess01' -X POST http://localhost:9191/family
273```
274
275We can play around using interactive [wscat](https://www.npmjs.com/package/@softmotions/wscat) websocket client.
276
277```sh
278wscat -H 'X-Access-Token:myaccess01' -c http://localhost:9191
279connected (press CTRL+C to quit)
280> k info
281< k {
282 "version": "2.0.0",
283 "file": "db.jb",
284 "size": 8192,
285 "collections": [
286 {
287 "name": "family",
288 "dbid": 3,
289 "rnum": 1,
290 "indexes": []
291 }
292 ]
293}
294
295> k get family 1
296< k 1 {
297 "firstName": "John",
298 "lastName": "Doe",
299 "age": 28,
300 "pets": [
301 {
302 "name": "Rexy rex",
303 "kind": "dog",
304 "likes": [
305 "bones",
306 "jumping",
307 "toys"
308 ]
309 },
310 {
311 "name": "Grenny",
312 "kind": "parrot",
313 "likes": [
314 "green color",
315 "night",
316 "toys"
317 ]
318 }
319 ]
320}
321```
322
323Note about the `k` prefix before every command; It is an arbitrary key chosen by client and designated to identify particular
324websocket request, this key will be returned with response to request and allows client to
325identify that response for his particular request. [More info](https://github.com/Softmotions/ejdb/blob/master/src/jbr/README.md)
326
327Query command over websocket has the following format:
328
329```
330<key> query <collection> <query>
331```
332
333So we will consider only `<query>` part in this document.
334
335### Get all elements in collection
336```
337k query family /*
338```
339or
340```
341k query family /**
342```
343or specify collection name in query explicitly
344```
345k @family/*
346```
347
348We can execute query by HTTP `POST` request
349```
350curl --data-raw '@family/[firstName = John]' -H'X-Access-Token:myaccess01' -X POST http://localhost:9191
351
3521 {"firstName":"John","lastName":"Doe","age":28,"pets":[{"name":"Rexy rex","kind":"dog","likes":["bones","jumping","toys"]},{"name":"Grenny","kind":"parrot","likes":["green color","night","toys"]}]}
353```
354
355### Set the maximum number of elements in result set
356
357```
358k @family/* | limit 10
359```
360
361### Get documents where specified json path exists
362
363Element at index `1` exists in `likes` array within a `pets` sub-object
364```
365> k query family /pets/*/likes/1
366< k 1 {"firstName":"John"...
367```
368
369Element at index `1` exists in `likes` array at any `likes` nesting level
370```
371> k query family /**/likes/1
372< k 1 {"firstName":"John"...
373```
374
375**From this point and below I will omit websocket specific prefix `k query family` and
376consider only JQL queries.**
377
378
379### Get documents by primary key
380
381In order to get documents by primary key the following options are available:
382
3831. Use API call `ejdb_get()`
384 ```ts
385 const doc = await db.get('users', 112);
386 ```
387
3881. Use the special query construction: `/=:?` or `@collection/=:?`
389
390Get document from `users` collection with primary key `112`
391```
392> k @users/=112
393```
394
395Update tags array for document in `jobs` collection (TypeScript):
396```ts
397 await db.createQuery('@jobs/ = :? | apply :? | count')
398 .setNumber(0, id)
399 .setJSON(1, { tags })
400 .completionPromise();
401```
402
403Array of primary keys can also be used for matching:
404
405```ts
406 await db.createQuery('@jobs/ = :?| apply :? | count')
407 .setJSON(0, [23, 1, 2])
408 .setJSON(1, { tags })
409 .completionPromise();
410```
411
412### Matching JSON entry values
413
414Below is a set of self explaining queries:
415
416```
417/pets/*/[name = "Rexy rex"]
418
419/pets/*/[name eq "Rexy rex"]
420
421/pets/*/[name = "Rexy rex" or name = Grenny]
422```
423Note about quotes around words with spaces.
424
425Get all documents where owner `age` greater than `20` and have some pet who like `bones` or `toys`
426```
427/[age > 20] and /pets/*/likes/[** in ["bones", "toys"]]
428```
429Here `**` denotes some element in `likes` array.
430
431`ni` is the inverse operator to `in`.
432Get documents where `bones` somewhere in `likes` array.
433```
434/pets/*/[likes ni "bones"]
435```
436
437We can create more complicated filters
438```
439( /[age <= 20] or /[lastName re "Do.*"] )
440 and /pets/*/likes/[** in ["bones", "toys"]]
441```
442Note about grouping parentheses and regular expression matching using `re` operator.
443
444`~` is a prefix matching operator (Since ejdb `v2.0.53`).
445Prefix matching can benefit from using indexes.
446
447Get documents where `/lastName` starts with `"Do"`.
448```
449/[lastName ~ Do]
450```
451
452### Arrays and maps can be matched as is
453
454Filter documents with `likes` array exactly matched to `["bones","jumping","toys"]`
455```
456/**/[likes = ["bones","jumping","toys"]]
457```
458Matching algorithms for arrays and maps are different:
459
460* Array elements are matched from start to end. In equal arrays
461 all values at the same index should be equal.
462* Object maps matching consists of the following steps:
463 * Lexicographically sort object keys in both maps.
464 * Do matching keys and its values starting from the lowest key.
465 * If all corresponding keys and values in one map are fully matched to ones in other
466 and vice versa, maps considered to be equal.
467 For example: `{"f":"d","e":"j"}` and `{"e":"j","f":"d"}` are equal maps.
468
469### Conditions on key names
470
471Find JSON document having `firstName` key at root level.
472```
473/[* = "firstName"]
474```
475I this context `*` denotes a key name.
476
477You can use conditions on key name and key value at the same time:
478```
479/[[* = "firstName"] = John]
480```
481
482Key name can be either `firstName` or `lastName` but should have `John` value in any case.
483```
484/[[* in ["firstName", "lastName"]] = John]
485```
486
487It may be useful in queries with dynamic placeholders (C API):
488```
489/[[* = :keyName] = :keyValue]
490```
491
492## JQL data modification
493
494`APPLY` section responsible for modification of documents content.
495
496```
497APPLY = ({'apply' | `upsert`} { PLACEHOLDER | json_object | json_array }) | 'del'
498```
499
500JSON patch specs conformed to `rfc7386` or `rfc6902` specifications followed after `apply` keyword.
501
502Let's add `address` object to all matched document
503```
504/[firstName = John] | apply {"address":{"city":"New York", "street":""}}
505```
506
507If JSON object is an argument of `apply` section it will be treated as merge match (`rfc7386`) otherwise
508it should be array which denotes `rfc6902` JSON patch. Placeholders also supported by `apply` section.
509```
510/* | apply :?
511```
512
513Set the street name in `address`
514```
515/[firstName = John] | apply [{"op":"replace", "path":"/address/street", "value":"Fifth Avenue"}]
516```
517
518Add `Neo` fish to the set of John's `pets`
519```
520/[firstName = John]
521| apply [{"op":"add", "path":"/pets/-", "value": {"name":"Neo", "kind":"fish"}}]
522```
523
524`upsert` updates existing document by given json argument used as merge patch
525 or inserts provided json argument as new document instance.
526
527```
528/[firstName = John] | upsert {"firstName": "John", "address":{"city":"New York"}}
529```
530
531### Non standard JSON patch extensions
532
533#### increment
534
535Increments numeric value identified by JSON path by specified value.
536
537Example:
538```
539 Document: {"foo": 1}
540 Patch: [{"op": "increment", "path": "/foo", "value": 2}]
541 Result: {"foo": 3}
542```
543#### add_create
544
545Same as JSON patch `add` but creates intermediate object nodes for missing JSON path segments.
546
547Example:
548```
549Document: {"foo": {"bar": 1}}
550Patch: [{"op": "add_create", "path": "/foo/zaz/gaz", "value": 22}]
551Result: {"foo":{"bar":1,"zaz":{"gaz":22}}}
552```
553
554Example:
555```
556Document: {"foo": {"bar": 1}}
557Patch: [{"op": "add_create", "path": "/foo/bar/gaz", "value": 22}]
558Result: Error since element pointed by /foo/bar is not an object
559```
560
561#### swap
562
563Swaps two values of JSON document starting from `from` path.
564
565Swapping rules
566
5671. If value pointed by `from` not exists error will be raised.
5681. If value pointed by `path` not exists it will be set by value from `from` path,
569 then object pointed by `from` path will be removed.
5701. If both values pointed by `from` and `path` are presented they will be swapped.
571
572Example:
573
574```
575Document: {"foo": ["bar"], "baz": {"gaz": 11}}
576Patch: [{"op": "swap", "from": "/foo/0", "path": "/baz/gaz"}]
577Result: {"foo": [11], "baz": {"gaz": "bar"}}
578```
579
580Example (Demo of rule 2):
581
582```
583Document: {"foo": ["bar"], "baz": {"gaz": 11}}
584Patch: [{"op": "swap", "from": "/foo/0", "path": "/baz/zaz"}]
585Result: {"foo":[],"baz":{"gaz":11,"zaz":"bar"}}
586```
587
588### Removing documents
589
590Use `del` keyword to remove matched elements from collection:
591```
592/FILTERS | del
593```
594
595Example:
596```
597> k add family {"firstName":"Jack"}
598< k 2
599> k query family /[firstName re "Ja.*"]
600< k 2 {"firstName":"Jack"}
601
602# Remove selected elements from collection
603> k query family /[firstName=Jack] | del
604< k 2 {"firstName":"Jack"}
605```
606
607## JQL projections
608
609```
610PROJECTIONS = PROJECTION [ {'+' | '-'} PROJECTION ]
611
612 PROJECTION = 'all' | json_path | join_clause
613```
614
615Projection allows to get only subset of JSON document excluding not needed data.
616
617Lets add one more document to our collection:
618
619```sh
620$ cat << EOF | curl -d @- -H'X-Access-Token:myaccess01' -X POST http://localhost:9191/family
621{
622"firstName":"Jack",
623"lastName":"Parker",
624"age":35,
625"pets":[{"name":"Sonic", "kind":"mouse", "likes":[]}]
626}
627EOF
628```
629Now query only pet owners firstName and lastName from collection.
630
631```
632> k query family /* | /{firstName,lastName}
633
634< k 3 {"firstName":"Jack","lastName":"Parker"}
635< k 1 {"firstName":"John","lastName":"Doe"}
636< k
637```
638
639Add `pets` array for every document
640```
641> k query family /* | /{firstName,lastName} + /pets
642
643< k 3 {"firstName":"Jack","lastName":"Parker","pets":[...
644< k 1 {"firstName":"John","lastName":"Doe","pets":[...
645```
646
647Exclude only `pets` field from documents
648```
649> k query family /* | all - /pets
650
651< k 3 {"firstName":"Jack","lastName":"Parker","age":35}
652< k 1 {"firstName":"John","lastName":"Doe","age":28,"address":{"city":"New York","street":"Fifth Avenue"}}
653< k
654```
655Here `all` keyword used denoting whole document.
656
657Get `age` and the first pet in `pets` array.
658```
659> k query family /[age > 20] | /age + /pets/0
660
661< k 3 {"age":35,"pets":[{"name":"Sonic","kind":"mouse","likes":[]}]}
662< k 1 {"age":28,"pets":[{"name":"Rexy rex","kind":"dog","likes":["bones","jumping","toys"]}]}
663< k
664```
665
666## JQL collection joins
667
668Join materializes reference to document to a real document objects which will replace reference inplace.
669
670Documents are joined by their primary keys only.
671
672Reference keys should be stored in referrer document as number or string field.
673
674Joins can be specified as part of projection expression
675in the following form:
676
677```
678/.../field<collection
679```
680Where
681
682* `field` ‐ JSON field contains primary key of joined document.
683* `<` ‐ The special mark symbol which instructs EJDB engine to replace `field` key by body of joined document.
684* `collection` ‐ name of DB collection where joined documents located.
685
686A referrer document will be untouched if associated document is not found.
687
688Here is the simple demonstration of collection joins in our interactive websocket shell:
689
690```
691> k add artists {"name":"Leonardo Da Vinci", "years":[1452,1519]}
692< k 1
693> k add paintings {"name":"Mona Lisa", "year":1490, "origin":"Italy", "artist": 1}
694< k 1
695> k add paintings {"name":"Madonna Litta - Madonna And The Child", "year":1490, "origin":"Italy", "artist": 1}
696< k 2
697
698# Lists paintings documents
699
700> k @paintings/*
701< k 2 {"name":"Madonna Litta - Madonna And The Child","year":1490,"origin":"Italy","artist":1}
702< k 1 {"name":"Mona Lisa","year":1490,"origin":"Italy","artist":1}
703< k
704>
705
706# Do simple join with artists collection
707
708> k @paintings/* | /artist<artists
709< k 2 {"name":"Madonna Litta - Madonna And The Child","year":1490,"origin":"Italy",
710 "artist":{"name":"Leonardo Da Vinci","years":[1452,1519]}}
711
712< k 1 {"name":"Mona Lisa","year":1490,"origin":"Italy",
713 "artist":{"name":"Leonardo Da Vinci","years":[1452,1519]}}
714< k
715
716
717# Strip all document fields except `name` and `artist` join
718
719> k @paintings/* | /artist<artists + /name + /artist/*
720< k 2 {"name":"Madonna Litta - Madonna And The Child","artist":{"name":"Leonardo Da Vinci","years":[1452,1519]}}
721< k 1 {"name":"Mona Lisa","artist":{"name":"Leonardo Da Vinci","years":[1452,1519]}}
722< k
723>
724
725# Same results as above:
726
727> k @paintings/* | /{name, artist<artists} + /artist/*
728< k 2 {"name":"Madonna Litta - Madonna And The Child","artist":{"name":"Leonardo Da Vinci","years":[1452,1519]}}
729< k 1 {"name":"Mona Lisa","artist":{"name":"Leonardo Da Vinci","years":[1452,1519]}}
730< k
731
732```
733
734Invalid references:
735
736```
737> k add paintings {"name":"Mona Lisa2", "year":1490, "origin":"Italy", "artist": 9999}
738< k 3
739> k @paintings/* | /artist<artists
740< k 3 {"name":"Mona Lisa2","year":1490,"origin":"Italy","artist":9999}
741< k 2 {"name":"Madonna Litta - Madonna And The Child","year":1490,"origin":"Italy","artist":{"name":"Leonardo Da Vinci","years":[1452,1519]}}
742< k 1 {"name":"Mona Lisa","year":1490,"origin":"Italy","artist":{"name":"Leonardo Da Vinci","years":[1452,1519]}}
743
744```
745
746## JQL results ordering
747
748```
749 ORDERBY = ({ 'asc' | 'desc' } PLACEHOLDER | json_path)...
750```
751
752Lets add one more document then sort documents in collection according to `firstName` ascending and `age` descending order.
753
754```
755> k add family {"firstName":"John", "lastName":"Ryan", "age":39}
756< k 4
757```
758
759```
760> k query family /* | /{firstName,lastName,age} | asc /firstName desc /age
761< k 3 {"firstName":"Jack","lastName":"Parker","age":35}
762< k 4 {"firstName":"John","lastName":"Ryan","age":39}
763< k 1 {"firstName":"John","lastName":"Doe","age":28}
764< k
765```
766
767`asc, desc` instructions may use indexes defined for collection to avoid a separate documents sorting stage.
768
769## JQL Options
770
771```
772OPTS = { 'skip' n | 'limit' n | 'count' | 'noidx' | 'inverse' | ORDERBY }...
773```
774
775* `skip n` Skip first `n` records before first element in result set
776* `limit n` Set max number of documents in result set
777* `count` Returns only `count` of matched documents
778 ```
779 > k query family /* | count
780 < k 3
781 < k
782 ```
783* `noidx` Do not use any indexes for query execution.
784* `inverse` By default query scans documents from most recently added to older ones.
785 This option inverts scan direction to opposite and activates `noidx` mode.
786 Has no effect if query has `asc/desc` sorting clauses.
787
788## JQL Indexes and performance tips
789
790Database index can be build for any JSON field path containing values of number or string type.
791Index can be an `unique` ‐ not allowing value duplication and `non unique`.
792The following index mode bit mask flags are used (defined in `ejdb2.h`):
793
794Index mode | Description
795--- | ---
796<code>0x01 EJDB_IDX_UNIQUE</code> | Index is unique
797<code>0x04 EJDB_IDX_STR</code> | Index for JSON `string` field value type
798<code>0x08 EJDB_IDX_I64</code> | Index for `8 bytes width` signed integer field values
799<code>0x10 EJDB_IDX_F64</code> | Index for `8 bytes width` signed floating point field values.
800
801For example unique index of string type will be specified by `EJDB_IDX_UNIQUE | EJDB_IDX_STR` = `0x05`.
802Index can be defined for only one value type located under specific path in json document.
803
804Lets define non unique string index for `/lastName` path:
805```
806> k idx family 4 /lastName
807< k
808```
809Index selection for queries based on set of heuristic rules.
810
811You can always check index usage by issuing `explain` command in WS API:
812```
813> k explain family /[lastName=Doe] and /[age!=27]
814< k explain [INDEX] MATCHED STR|3 /lastName EXPR1: 'lastName = Doe' INIT: IWKV_CURSOR_EQ
815[INDEX] SELECTED STR|3 /lastName EXPR1: 'lastName = Doe' INIT: IWKV_CURSOR_EQ
816 [COLLECTOR] PLAIN
817```
818
819The following statements are taken into account when using EJDB2 indexes:
820* Only one index can be used for particular query execution
821* If query consist of `or` joined part at top level or contains `negated` expressions at the top level
822 of query expression - indexes will not be in use at all.
823 So no indexes below:
824 ```
825 /[lastName != Andy]
826
827 /[lastName = "John"] or /[lastName = Peter]
828
829 ```
830 But will be used `/lastName` index defined above
831 ```
832 /[lastName = Doe]
833
834 /[lastName = Doe] and /[age = 28]
835
836 /[lastName = Doe] and not /[age = 28]
837
838 /[lastName = Doe] and /[age != 28]
839 ```
840* The following operators are supported by indexes (ejdb 2.0.x):
841 * `eq, =`
842 * `gt, >`
843 * `gte, >=`
844 * `lt, <`
845 * `lte, <=`
846 * `in`
847 * `~` (Prefix matching since ejdb 2.0.53)
848
849* `ORDERBY` clauses may use indexes to avoid result set sorting.
850* Array fields can also be indexed. Let's outline typical use case: indexing of some entity tags:
851 ```
852 > k add books {"name":"Mastering Ultra", "tags":["ultra", "language", "bestseller"]}
853 < k 1
854 > k add books {"name":"Learn something in 24 hours", "tags":["bestseller"]}
855 < k 2
856 > k query books /*
857 < k 2 {"name":"Learn something in 24 hours","tags":["bestseller"]}
858 < k 1 {"name":"Mastering Ultra","tags":["ultra","language","bestseller"]}
859 < k
860 ```
861 Create string index for `/tags`
862 ```
863 > k idx books 4 /tags
864 < k
865 ```
866 Filter books by `bestseller` tag and show index usage in query:
867 ```
868 > k explain books /tags/[** in ["bestseller"]]
869 < k explain [INDEX] MATCHED STR|4 /tags EXPR1: '** in ["bestseller"]' INIT: IWKV_CURSOR_EQ
870 [INDEX] SELECTED STR|4 /tags EXPR1: '** in ["bestseller"]' INIT: IWKV_CURSOR_EQ
871 [COLLECTOR] PLAIN
872
873 < k 1 {"name":"Mastering Ultra","tags":["ultra","language","bestseller"]}
874 < k 2 {"name":"Learn something in 24 hours","tags":["bestseller"]}
875 < k
876 ```
877
878### Performance tip: Physical ordering of documents
879
880All documents in collection are sorted by their primary key in `descending` order.
881So if you use auto generated keys (`ejdb_put_new`) you may be sure what documents fetched as result of
882full scan query will be ordered according to the time of insertion in descendant order,
883unless you don't use query sorting, indexes or `inverse` keyword.
884
885### Performance tip: Brute force scan vs indexed access
886
887In many cases, using index may drop down the overall query performance.
888Because index collection contains only document references (`id`) and engine may perform
889an addition document fetching by its primary key to finish query matching.
890So for not so large collections a brute scan may perform better than scan using indexes.
891However, exact matching operations: `eq`, `in` and `sorting` by natural index order
892will benefit from index in most cases.
893
894
895### Performance tip: Get rid of unnecessary document data
896
897If you'd like update some set of documents with `apply` or `del` operations
898but don't want fetching all of them as result of query - just add `count`
899modifier to the query to get rid of unnecessary data transferring and json data conversion.
900
901
902
903# HTTP REST/Websocket API endpoint
904
905EJDB engine provides the ability to start a separate HTTP/Websocket endpoint worker exposing network API for quering and data modifications.
906
907The easiest way to expose database over the network is using the standalone `jbs` server. (Of course if you plan to avoid `C API` integration).
908
909## jbs server
910
911```
912jbs -h
913
914EJDB 2.0.0 standalone REST/Websocket server. http://ejdb.org
915
916 --file <> Database file path. Default: db.jb
917 -f <> (same as --file)
918 --port ## HTTP port number listen to. Default: 9191
919 -p ## (same as --port)
920 --bind <> Address server listen. Default: localhost
921 -b <> (same as --bind)
922 --access <> Server access token matched to 'X-Access-Token' HTTP header value
923 -a <> (same as --access)
924 --trunc Cleanup existing database file on open
925 -t (same as --trunc)
926 --wal Use write ahead logging (WAL). Must be set for data durability.
927 -w (same as --wal)
928
929Advanced options
930 --sbz ## Max sorting buffer size. If exceeded, an overflow temp file for data will be created. Default: 16777216, min: 1048576
931 --dsz ## Initial size of buffer to process/store document on queries. Preferable average size of document. Default: 65536, min: 16384
932 --bsz ## Max HTTP/WS API document body size. Default: 67108864, min: 524288
933
934Use any of the following input formats:
935 -arg <value> -arg=<value> -arg<value>
936
937Use the -h, -help or -? to get this information again.
938```
939
940## HTTP API
941
942Access to HTTP endpoint can be protected by a token specified with `--access`
943command flag or by C API `EJDB_HTTP` options. If access token specified on server, client must provide `X-Access-Token` HTTP header value. If token is required and not provided by client the `401` HTTP code will be reported. If access token is not matched to the token provided the `403` HTTP code will be returned.
944For any other errors server will respond with `500` error code.
945
946## REST API
947
948### POST /{collection}
949Add a new document to the `collection`.
950* `200` success. Body: a new document identifier as `int64` number
951
952### PUT /{collection}/{id}
953Replaces/store document under specific numeric `id`
954* `200` on success. Empty body
955
956### DELETE /{collection}/{id}
957Removes document identified by `id` from a `collection`
958* `200` on success. Empty body
959* `404` if document not found
960
961### PATCH /{collection}/{id}
962Patch a document identified by `id` by [rfc7396](https://tools.ietf.org/html/rfc7396),
963[rfc6902](https://tools.ietf.org/html/rfc6902) data.
964* `200` on success. Empty body
965
966### GET | HEAD /{collections}/{id}
967Retrieve document identified by `id` from a `collection`.
968* `200` on success. Body: JSON document text.
969 * `content-type:application/json`
970 * `content-length:`
971* `404` if document not found
972
973### POST /
974Query a collection by provided query as POST body.
975Body of query should contains collection name in use in the first filter element: `@collection_name/...`
976Request headers:
977* `X-Hints` comma separated extra hints to ejdb2 database engine.
978 * `explain` Show query execution plan before first element in result set separated by `--------------------` line.
979Response:
980* Response data transfered using [HTTP chunked transfer encoding](https://en.wikipedia.org/wiki/Chunked_transfer_encoding)
981* `200` on success.
982* JSON documents separated by `\n` in the following format:
983 ```
984 \r\n<document id>\t<document JSON body>
985 ...
986 ```
987
988Example:
989
990```
991curl -v --data-raw '@family/[age > 18]' -H 'X-Access-Token:myaccess01' http://localhost:9191
992* Rebuilt URL to: http://localhost:9191/
993* Trying 127.0.0.1...
994* TCP_NODELAY set
995* Connected to localhost (127.0.0.1) port 9191 (#0)
996> POST / HTTP/1.1
997> Host: localhost:9191
998> User-Agent: curl/7.58.0
999> Accept: */*
1000> X-Access-Token:myaccess01
1001> Content-Length: 18
1002> Content-Type: application/x-www-form-urlencoded
1003>
1004* upload completely sent off: 18 out of 18 bytes
1005< HTTP/1.1 200 OK
1006< connection:keep-alive
1007< content-type:application/json
1008< transfer-encoding:chunked
1009<
1010
10114 {"firstName":"John","lastName":"Ryan","age":39}
10123 {"firstName":"Jack","lastName":"Parker","age":35,"pets":[{"name":"Sonic","kind":"mouse","likes":[]}]}
10131 {"firstName":"John","lastName":"Doe","age":28,"pets":[{"name":"Rexy rex","kind":"dog","likes":["bones","jumping","toys"]},{"name":"Grenny","kind":"parrot","likes":["green color","night","toys"]}],"address":{"city":"New York","street":"Fifth Avenue"}}
1014* Connection #0 to host localhost left intact
1015```
1016
1017```
1018curl --data-raw '@family/[lastName = "Ryan"]' -H 'X-Access-Token:myaccess01' -H 'X-Hints:explain' http://localhost:9191
1019[INDEX] MATCHED STR|3 /lastName EXPR1: 'lastName = "Ryan"' INIT: IWKV_CURSOR_EQ
1020[INDEX] SELECTED STR|3 /lastName EXPR1: 'lastName = "Ryan"' INIT: IWKV_CURSOR_EQ
1021 [COLLECTOR] PLAIN
1022--------------------
10234 {"firstName":"John","lastName":"Ryan","age":39}
1024```
1025
1026### OPTIONS /
1027Fetch ejdb JSON metadata and available HTTP methods in `Allow` response header.
1028Example:
1029```
1030curl -X OPTIONS -H 'X-Access-Token:myaccess01' http://localhost:9191/
1031{
1032 "version": "2.0.0",
1033 "file": "db.jb",
1034 "size": 16384,
1035 "collections": [
1036 {
1037 "name": "family",
1038 "dbid": 3,
1039 "rnum": 3,
1040 "indexes": [
1041 {
1042 "ptr": "/lastName",
1043 "mode": 4,
1044 "idbf": 64,
1045 "dbid": 4,
1046 "rnum": 3
1047 }
1048 ]
1049 }
1050 ]
1051}
1052```
1053
1054## Websocket API
1055
1056EJDB supports simple text based protocol over HTTP websocket protocol.
1057You can use interactive websocket CLI tool [wscat](https://www.npmjs.com/package/@softmotions/wscat) to communicate with server by hands.
1058
1059### Commands
1060
1061#### ?
1062Will respond with the following help text message:
1063```
1064wscat -H 'X-Access-Token:myaccess01' -c http://localhost:9191
1065> ?
1066<
1067<key> info
1068<key> get <collection> <id>
1069<key> set <collection> <id> <document json>
1070<key> add <collection> <document json>
1071<key> del <collection> <id>
1072<key> patch <collection> <id> <patch json>
1073<key> idx <collection> <mode> <path>
1074<key> rmi <collection> <mode> <path>
1075<key> rmc <collection>
1076<key> query <collection> <query>
1077<key> explain <collection> <query>
1078<key> <query>
1079>
1080```
1081
1082Note about `<key>` prefix before every command; It is an arbitrary key chosen by client and designated to identify particular websocket request, this key will be returned with response to request and allows client to identify that response for his particular request.
1083
1084Errors are returned in the following format:
1085```
1086<key> ERROR: <error description>
1087```
1088
1089#### `<key> info`
1090Get database metadatas as JSON document.
1091
1092#### `<key> get <collection> <id>`
1093Retrieve document identified by `id` from a `collection`.
1094If document is not found `IWKV_ERROR_NOTFOUND` will be returned.
1095
1096Example:
1097```
1098> k get family 3
1099< k 3 {
1100 "firstName": "Jack",
1101 "lastName": "Parker",
1102 "age": 35,
1103 "pets": [
1104 {
1105 "name": "Sonic",
1106 "kind": "mouse",
1107 "likes": []
1108 }
1109 ]
1110}
1111```
1112If document not found we will get error:
1113```
1114> k get family 55
1115< k ERROR: Key not found. (IWKV_ERROR_NOTFOUND)
1116>
1117```
1118
1119#### `<key> set <collection> <id> <document json>`
1120Replaces/add document under specific numeric `id`.
1121`Collection` will be created automatically if not exists.
1122
1123#### `<key> add <collection> <document json>`
1124Add new document to `<collection>` New `id` of document will be generated
1125and returned as response. `Collection> will be created automatically if not exists.
1126
1127Example:
1128```
1129> k add mycollection {"foo":"bar"}
1130< k 1
1131> k add mycollection {"foo":"bar"}
1132< k 2
1133>
1134```
1135
1136#### `<key> del <collection> <id>`
1137Remove document identified by `id` from the `collection`.
1138If document is not found `IWKV_ERROR_NOTFOUND` will be returned.
1139
1140#### `<key> patch <collection> <id> <patch json>`
1141Apply [rfc7396](https://tools.ietf.org/html/rfc7396) or
1142[rfc6902](https://tools.ietf.org/html/rfc6902) patch to the document identified by `id`.
1143If document is not found `IWKV_ERROR_NOTFOUND` will be returned.
1144
1145#### `<key> query <collection> <query>`
1146Execute query on documents in specified `collection`.
1147**Response:** A set of WS messages with document boidies terminated by the last
1148message with empty body.
1149```
1150> k query family /* | /firstName
1151< k 4 {"firstName":"John"}
1152< k 3 {"firstName":"Jack"}
1153< k 1 {"firstName":"John"}
1154< k
1155```
1156Note about last message: `<key>` with no body.
1157
1158#### `<key> explain <collection> <query>`
1159Same as `<key> query <collection> <query>` but the first response message will
1160be prefixed by `<key> explain` and contains query execution plan.
1161
1162Example:
1163```
1164> k explain family /* | /firstName
1165< k explain [INDEX] NO [COLLECTOR] PLAIN
1166
1167< k 4 {"firstName":"John"}
1168< k 3 {"firstName":"Jack"}
1169< k 1 {"firstName":"John"}
1170< k
1171```
1172
1173#### <key> <query>
1174Execute query text. Body of query should contains collection name in use in the first filter element: `@collection_name/...`. Behavior is the same as for: `<key> query <collection> <query>`
1175
1176#### `<key> idx <collection> <mode> <path>`
1177Ensure index with specified `mode` (bitmask flag) for given json `path` and `collection`.
1178Collection will be created if not exists.
1179
1180Index mode | Description
1181--- | ---
1182<code>0x01 EJDB_IDX_UNIQUE</code> | Index is unique
1183<code>0x04 EJDB_IDX_STR</code> | Index for JSON `string` field value type
1184<code>0x08 EJDB_IDX_I64</code> | Index for `8 bytes width` signed integer field values
1185<code>0x10 EJDB_IDX_F64</code> | Index for `8 bytes width` signed floating point field values.
1186
1187##### Example
1188Set unique string index `(0x01 & 0x04) = 5` on `/name` JSON field:
1189```
1190k idx mycollection 5 /name
1191```
1192
1193#### `<key> rmi <collection> <mode> <path>`
1194Remove index with specified `mode` (bitmask flag) for given json `path` and `collection`.
1195Return error if given index not found.
1196
1197#### `<key> rmc <collection>`
1198Remove collection and all of its data.
1199Note: If `collection` is not found no errors will be reported.
1200
1201
1202
1203
1204# Docker support
1205
1206If you have [Docker]("https://www.docker.com/") installed, you can build a Docker image and run it in a container
1207
1208```
1209cd docker
1210docker build -t ejdb2 .
1211docker run -d -p 9191:9191 --name myEJDB ejdb2 --access myAccessKey
1212```
1213
1214or get an image of `ejdb2` directly from the Docker Hub
1215
1216```
1217docker run -d -p 9191:9191 --name myEJDB softmotions/ejdb2 --access myAccessKey
1218```
1219
1220
1221# C API
1222
1223EJDB can be embedded into any `C/C++` application.
1224`C API` documented in the following headers:
1225
1226* [ejdb.h](https://github.com/Softmotions/ejdb/blob/master/src/ejdb2.h) Main API functions
1227* [jbl.h](https://github.com/Softmotions/ejdb/blob/master/src/jbl/jbl.h) JSON documents management API
1228* [jql.h](https://github.com/Softmotions/ejdb/blob/master/src/jql/jql.h) Query building API
1229
1230Example application:
1231```c
1232#include <ejdb2/ejdb2.h>
1233
1234#define CHECK(rc_) \
1235 if (rc_) { \
1236 iwlog_ecode_error3(rc_); \
1237 return 1; \
1238 }
1239
1240static iwrc documents_visitor(EJDB_EXEC *ctx, const EJDB_DOC doc, int64_t *step) {
1241 // Print document to stderr
1242 return jbl_as_json(doc->raw, jbl_fstream_json_printer, stderr, JBL_PRINT_PRETTY);
1243}
1244
1245int main() {
1246
1247 EJDB_OPTS opts = {
1248 .kv = {
1249 .path = "example.db",
1250 .oflags = IWKV_TRUNC
1251 }
1252 };
1253 EJDB db; // EJDB2 storage handle
1254 int64_t id; // Document id placeholder
1255 JQL q = 0; // Query instance
1256 JBL jbl = 0; // Json document
1257
1258 iwrc rc = ejdb_init();
1259 CHECK(rc);
1260
1261 rc = ejdb_open(&opts, &db);
1262 CHECK(rc);
1263
1264 // First record
1265 rc = jbl_from_json(&jbl, "{\"name\":\"Bianca\", \"age\":4}");
1266 RCGO(rc, finish);
1267 rc = ejdb_put_new(db, "parrots", jbl, &id);
1268 RCGO(rc, finish);
1269 jbl_destroy(&jbl);
1270
1271 // Second record
1272 rc = jbl_from_json(&jbl, "{\"name\":\"Darko\", \"age\":8}");
1273 RCGO(rc, finish);
1274 rc = ejdb_put_new(db, "parrots", jbl, &id);
1275 RCGO(rc, finish);
1276 jbl_destroy(&jbl);
1277
1278 // Now execute a query
1279 rc = jql_create(&q, "parrots", "/[age > :age]");
1280 RCGO(rc, finish);
1281
1282 EJDB_EXEC ux = {
1283 .db = db,
1284 .q = q,
1285 .visitor = documents_visitor
1286 };
1287
1288 // Set query placeholder value.
1289 // Actual query will be /[age > 3]
1290 rc = jql_set_i64(q, "age", 0, 3);
1291 RCGO(rc, finish);
1292
1293 // Now execute the query
1294 rc = ejdb_exec(&ux);
1295
1296finish:
1297 jql_destroy(&q);
1298 jbl_destroy(&jbl);
1299 ejdb_close(&db);
1300 CHECK(rc);
1301 return 0;
1302}
1303```
1304
1305Compile and run:
1306```
1307gcc -std=gnu11 -Wall -pedantic -c -o example1.o example1.c
1308gcc -o example1 example1.o -lejdb2
1309
1310./example1
1311{
1312 "name": "Darko",
1313 "age": 8
1314}{
1315 "name": "Bianca",
1316 "age": 4
1317}
1318```
1319
1320# License
1321```
1322
1323MIT License
1324
1325Copyright (c) 2012-2021 Softmotions Ltd <info@softmotions.com>
1326
1327Permission is hereby granted, free of charge, to any person obtaining a copy
1328of this software and associated documentation files (the "Software"), to deal
1329in the Software without restriction, including without limitation the rights
1330to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
1331copies of the Software, and to permit persons to whom the Software is
1332furnished to do so, subject to the following conditions:
1333
1334The above copyright notice and this permission notice shall be included in all
1335copies or substantial portions of the Software.
1336
1337THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
1338IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
1339FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
1340AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
1341LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
1342OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
1343SOFTWARE.
1344```
1345
1346