#1 2015-08-23 09:15:49

edwinsn
Member
Registered: 2010-07-02
Posts: 1,215

BIG news: SQLite looks to be adding JSON support!

I'm been wanting an embedded document-based store  (don't confuse this with a key-value store) for a long time!


https://www.reddit.com/r/programming/co … n_support/

https://news.ycombinator.com/item?id=10103695


Delphi XE4 Pro on Windows 7 64bit.
Lazarus trunk built with fpcupdelux on Windows with cross-compile for Linux 64bit.

Offline

#2 2015-08-23 10:46:23

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,128
Website

Re: BIG news: SQLite looks to be adding JSON support!

This is already available in mORMot, with some additional features.

Just define a variant published field, and store a TDocVariant within.
See http://synopse.info/files/html/Synopse% … l#TITLE_76

We defined some JSON operations from SQLite3 code, just like Sqlite3 core did had in the current trunk, as extension.
See http://synopse.info/files/html/Synopse% … l#TITLE_78

Our implementation is very proven, since it uses the JSON core of mORMot, and is able to handle the "extended" JSON syntax of MongoDB: field names can be serialized without double quotes, which reduces a lot the storage size, and the transmission size, when the JSON is stored within a TEXT column, which would escape all double quote characters.

I guess the performance of our implementation may be faster than current SQlite3's extension.
We use a SAX-like fast approach, without any temporary memory allocation during its search, whereas SQlite3's extension is doing a lot of memory allocations, in some cases.

I've just written a blog article about those features, to show some light on them.
See http://blog.synopse.info/post/2015/08/23/SQLandNoSQL

Offline

#3 2015-08-23 12:25:38

edwinsn
Member
Registered: 2010-07-02
Posts: 1,215

Re: BIG news: SQLite looks to be adding JSON support!

HI Arnaud, before I read through the document you pointed out and the new blog post you just written (I guess it's a reaction of this forum post), I'd like to say that you are awesome, you are so fast! smile


Delphi XE4 Pro on Windows 7 64bit.
Lazarus trunk built with fpcupdelux on Windows with cross-compile for Linux 64bit.

Offline

#4 2015-08-24 09:34:59

edwinsn
Member
Registered: 2010-07-02
Posts: 1,215

Re: BIG news: SQLite looks to be adding JSON support!

Hi Arnaud,

It seems that  the current mORMot implementation only provides functions (which are accessible within a SQL statement) for reading JSON data stored in a DB, I think the SQLITE3's own functions also support updating the JSON data, eg. updating a property.


Delphi XE4 Pro on Windows 7 64bit.
Lazarus trunk built with fpcupdelux on Windows with cross-compile for Linux 64bit.

Offline

#5 2015-08-24 10:15:51

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,128
Website

Re: BIG news: SQLite looks to be adding JSON support!

Indeed.

You need to use an UPDATE statement to update a property, with a JsonSet() function.
Could you please create a feature request ticket?

Offline

#6 2015-08-25 06:38:06

edwinsn
Member
Registered: 2010-07-02
Posts: 1,215

Re: BIG news: SQLite looks to be adding JSON support!

@ab, in addtion to updating json properties, we'll also need  the ability to add property values.

I think you can investigate the recently added JSON support in postgresql, in order to plan a better support for mORMot.


Delphi XE4 Pro on Windows 7 64bit.
Lazarus trunk built with fpcupdelux on Windows with cross-compile for Linux 64bit.

Offline

#7 2015-08-25 07:10:11

edwinsn
Member
Registered: 2010-07-02
Posts: 1,215

Re: BIG news: SQLite looks to be adding JSON support!


Delphi XE4 Pro on Windows 7 64bit.
Lazarus trunk built with fpcupdelux on Windows with cross-compile for Linux 64bit.

Offline

#8 2015-09-18 09:26:45

ComingNine
Member
Registered: 2010-07-29
Posts: 294

Re: BIG news: SQLite looks to be adding JSON support!

Dear Arnaud,

Right now I could not find any compilable sample or test concerning the documented JsonGet / JsonSet / JsonHas. I wonder if you could add some ? They would be of great help ! Many thanks !

test_vm1@MICROSO-ABC3D4O /cygdrive/d/Dev/Lib/mORMot
$ find . -name \*.pas -print0 | xargs -0 grep -irw JsonGet
./SQLite3/mORMot.pas:    // - content may be searched using JsonGet/JsonHas SQL functions on a
./SQLite3/mORMot.pas:    // - content may be searched using JsonGet/JsonHas SQL functions on a
./SynSQLite3.pas:begin // JsonGet() would return the raw JSON for Delphi 5
./SynSQLite3.pas:begin // JsonGet(VariantField,'PropName') returns the value of a JSON object
./SynSQLite3.pas:      // JsonGet(VariantField,'Obj1.Obj2.PropName') to search by path
./SynSQLite3.pas:      // JsonGet(VariantField,0) returns the 1st item in the JSON array
./SynSQLite3.pas:      // JsonGet(VariantField,'Prop1,Prop2') returns the values as a JSON object
./SynSQLite3.pas:      // JsonGet(VariantField,'Prop*') returns the values as a JSON object
./SynSQLite3.pas:      // JsonGet(VariantField,'Obj1.Obj2.Prop1,Obj1.Prop2') to search by path
./SynSQLite3.pas:      // JsonGet(VariantField,'Obj1.Obj2.Prop*,Obj1.Prop2') to search by path
./SynSQLite3.pas:  sqlite3.create_function(DB,'JSONGET',2,SQLITE_ANY,nil,InternalJsonGet,nil,nil);

test_vm1@MICROSO-ABC3D4O /cygdrive/d/Dev/Lib/mORMot
$ find . -name \*.pas -print0 | xargs -0 grep -irw JsonSet
./SynSQLite3.pas:begin // JsonSet(VariantField,'PropName','abc') to set a value
./SynSQLite3.pas:      // JsonSet(VariantField,'Obj1.Obj2.PropName','def') to set by path
./SynSQLite3.pas:  sqlite3.create_function(DB,'JSONSET',3,SQLITE_ANY,nil,InternalJsonSet,nil,nil);

test_vm1@MICROSO-ABC3D4O /cygdrive/d/Dev/Lib/mORMot
$ find . -name \*.pas -print0 | xargs -0 grep -irw JsonHas
./SQLite3/mORMot.pas:    // - content may be searched using JsonGet/JsonHas SQL functions on a
./SQLite3/mORMot.pas:    // - content may be searched using JsonGet/JsonHas SQL functions on a
./SynSQLite3.pas:begin // JsonHas(VariantField,'PropName') returns TRUE if matches a JSON object property
./SynSQLite3.pas:      // JsonHas(VariantField,'Obj1.Obj2.PropName') to search by path
./SynSQLite3.pas:      // JsonHas(VariantField,0) returns TRUE if the JSON array has at least one item
./SynSQLite3.pas:  sqlite3.create_function(DB,'JSONHAS',2,SQLITE_ANY,nil,InternalJsonHas,nil,nil);

Last edited by ComingNine (2015-09-18 09:27:40)

Offline

#9 2015-09-23 06:02:09

edwinsn
Member
Registered: 2010-07-02
Posts: 1,215

Re: BIG news: SQLite looks to be adding JSON support!

Update: SQLite has already the json1 extension and it's expected to be integrated into the core once it's stabilized: https://www.sqlite.org/draft/json1.html

Obviously can be very useful for people who need very flexible, even nested database schema.


Delphi XE4 Pro on Windows 7 64bit.
Lazarus trunk built with fpcupdelux on Windows with cross-compile for Linux 64bit.

Offline

#10 2015-09-23 06:43:51

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,128
Website

Re: BIG news: SQLite looks to be adding JSON support!

And there are some additional features, very handy not only for JSON process but any other kind of iterative SQL.

See indexes on expressions http://www.sqlite.org/draft/lang_create … #indexexpr
and table-value functions http://www.sqlite.org/draft/vtab.html#tabfunc2

Offline

#11 2015-10-19 16:11:02

edwinsn
Member
Registered: 2010-07-02
Posts: 1,215

Re: BIG news: SQLite looks to be adding JSON support!

Great! Just noticed that the the json1 JSON extension has been integrated into sqlite 3.9.0 official release!

PS, there is a new 3.9.1 which includes a critical fix for a bug related to the newly added JSON features: https://www.sqlite.org/src/info/57eec374ae1d0a1d

Last edited by edwinsn (2015-10-20 06:40:39)


Delphi XE4 Pro on Windows 7 64bit.
Lazarus trunk built with fpcupdelux on Windows with cross-compile for Linux 64bit.

Offline

#12 2015-10-20 07:37:20

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,128
Website

Re: BIG news: SQLite looks to be adding JSON support!

I've updated the SQlite3 library.
See http://synopse.info/fossil/info/51b2cabaa9

BTW https://www.sqlite.org/src/info/bc9a9a60c31ebf9b was introduced from a request on our side.
smile

Offline

#13 2015-10-20 11:12:01

edwinsn
Member
Registered: 2010-07-02
Posts: 1,215

Re: BIG news: SQLite looks to be adding JSON support!

Hi Arnaud, You are always fast!


Delphi XE4 Pro on Windows 7 64bit.
Lazarus trunk built with fpcupdelux on Windows with cross-compile for Linux 64bit.

Offline

Board footer

Powered by FluxBB