Re: [sqlite] Possibly missing feature in json1 extension

2016-06-19 Thread David Empson 
Thanks Richard.

Tested and it works for me. I tried it with text, integer and null types as 
input.

It also removes one step in storing JSON-encoded integer values, since I no 
longer need to convert them to text.

> On 18/06/2016, at 1:04 AM, Richard Hipp  wrote:
> 
> New json_quote() function added on a branch
> (https://www.sqlite.org/src/info/2c3714aebf5e40e3).  If there is no
> pushback, and if this fixes David's problem, then this will get merged
> to trunk and appear in the next release.
> 
> On 6/16/16, David Empson  wrote:
>> I'm working with SQLite 3.13.0, and am the process of adding a new table to
>> a database:
>> 
>> CREATE TABLE settings(key TEXT PRIMARY KEY NOT NULL, value TEXT)
>> 
>> This table will hold arbitrarily named application defined settings. For the
>> value column I’d like to use JSON for every row, as some of the settings
>> will be structured (either as arrays or objects, possibly with nested
>> substructures). The json1 extension seems to cover most of what I need
>> (without having a separate JSON library outside of SQLite), but I've run
>> into a problem which might point to a missing function.
>> 
>> I can easily deal with arrays and objects using functions like json_array(),
>> but I'm having trouble with simple values, particularly strings: there
>> appears to be no function to turn an SQLite text value into a simple JSON
>> text value, without putting it in an array or object.
>> 
>> e.g. this is fine:
>> 
>> sqlite> SELECT json_array('one',2,'we"ird');
>> ["one",2,"we\"ird"]
>> 
>> but I can't find a way to convert just the SQLite text 'one' into the JSON
>> text "one" (or 'we"ird' into "we\"ird") without the array (or object)
>> wrapper.
>> 
>> A function like json_value(value) would solve the problem. It would be a
>> single argument function based on the implementation of json_array(), which
>> doesn't output the square brackets. It should also support NULL and numeric
>> arguments, like json_array().
>> 
>> Converting a simple JSON value back to native SQLite types is easy:
>> json_extract(json,'$'), or I can use json_each() or json_tree() to parse the
>> JSON values without knowing their structure.
>> 
>> Perhaps json_set() should be able to handle this? I tried something like
>> this:
>> 
>> sqlite> SELECT json_set('null', '$', 'test');
>> test
>> 
>> It copies the string in the third parameter, but doesn't output valid JSON
>> because the quotes haven't been added. Is this a bug?
>> 
>> 
>> As an interim solution, I can modify a local copy of the json1 extension to
>> add my proposed function, but it would be nice if this was standard in a
>> later version.
>> 
>> Here is a draft implementation:
>> 
>> static void jsonValueFunc(
>>  sqlite3_context *ctx,
>>  int argc,
>>  sqlite3_value **argv
>> ){
>>  JsonString jx;
>> 
>>  jsonInit(, ctx);
>>  jsonAppendValue(, argv[0]);
>>  jsonResult();
>>  sqlite3_result_subtype(ctx, JSON_SUBTYPE);
>> }
>> with this definition in the aFunc[] array:
>> 
>>{ "json_value",  -1, 0,   jsonValueFunc },
>> 
>> 
>> A workaround I've found is to use  json_array('text') wrapped in SQLite
>> functions to strip the square brackets off the array, but that seems ugly.
>> 
>> An alternative solution would be that I only use JSON for the complex
>> settings and leave the simple ones stored using SQLite native types, but
>> that either means adding a column to track which ones are JSON, or having
>> inherent knowledge for each setting, which could lead to compatibility
>> problems and potential ambiguity if a future update changes to using JSON
>> for an existing setting, and an existing value happens to be valid JSON,
>> such as the word 'true'.
>> 
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> 
> 
> 
> -- 
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Article & HN discussion - The beets blog: we’re pretty happy with SQLite & not urgently interested in a fancier DBMS

2016-06-19 Thread Richard Hipp 
On 6/19/16, Andreas Kupries  wrote:
>
> https://news.ycombinator.com/item?id=11934826
> http://beets.io/blog/sqlite-performance.html
>

Thanks, Andreas.  I've been following the discussion.

Hope you are doing well!


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Article & HN discussion - The beets blog: we’re pretty happy with SQLite & not urgently interested in a fancier DBMS

2016-06-19 Thread Andreas Kupries 

https://news.ycombinator.com/item?id=11934826
http://beets.io/blog/sqlite-performance.html

-- 
So long,
Andreas Kupries 

Developer @ Hewlett Packard Enterprise

Tcl'2016, Nov 14-18, Houston, TX, USA. http://www.tcl.tk/community/tcl2016/
---




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users