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