diff options
| -rw-r--r-- | ext/misc/json1.c | 112 | ||||
| -rw-r--r-- | manifest | 13 | ||||
| -rw-r--r-- | manifest.uuid | 2 | ||||
| -rw-r--r-- | test/json103.test | 65 |
4 files changed, 184 insertions, 8 deletions
diff --git a/ext/misc/json1.c b/ext/misc/json1.c index 61d013ea4..0bd0baff1 100644 --- a/ext/misc/json1.c +++ b/ext/misc/json1.c @@ -1181,7 +1181,7 @@ static void jsonTest1Func( #endif /* SQLITE_DEBUG */ /**************************************************************************** -** SQL function implementations +** Scalar SQL function implementations ****************************************************************************/ /* @@ -1514,6 +1514,102 @@ static void jsonValidFunc( sqlite3_result_int(ctx, rc); } + +/**************************************************************************** +** Aggregate SQL function implementations +****************************************************************************/ +/* +** json_group_array(VALUE) +** +** Return a JSON array composed of all values in the aggregate. +*/ +static void jsonArrayStep( + sqlite3_context *ctx, + int argc, + sqlite3_value **argv +){ + JsonString *pStr; + pStr = (JsonString*)sqlite3_aggregate_context(ctx, sizeof(*pStr)); + if( pStr ){ + if( pStr->zBuf==0 ){ + jsonInit(pStr, ctx); + jsonAppendChar(pStr, '['); + }else{ + jsonAppendChar(pStr, ','); + pStr->pCtx = ctx; + } + jsonAppendValue(pStr, argv[0]); + } +} +static void jsonArrayFinal(sqlite3_context *ctx){ + JsonString *pStr; + pStr = (JsonString*)sqlite3_aggregate_context(ctx, 0); + if( pStr ){ + pStr->pCtx = ctx; + jsonAppendChar(pStr, ']'); + if( pStr->bErr ){ + sqlite3_result_error_nomem(ctx); + if( !pStr->bStatic ) sqlite3_free(pStr->zBuf); + }else{ + sqlite3_result_text(ctx, pStr->zBuf, pStr->nUsed, + pStr->bStatic ? SQLITE_TRANSIENT : sqlite3_free); + pStr->bStatic = 1; + } + }else{ + sqlite3_result_text(ctx, "[]", 2, SQLITE_STATIC); + } + sqlite3_result_subtype(ctx, JSON_SUBTYPE); +} + +/* +** json_group_obj(NAME,VALUE) +** +** Return a JSON object composed of all names and values in the aggregate. +*/ +static void jsonObjectStep( + sqlite3_context *ctx, + int argc, + sqlite3_value **argv +){ + JsonString *pStr; + const char *z; + u32 n; + pStr = (JsonString*)sqlite3_aggregate_context(ctx, sizeof(*pStr)); + if( pStr ){ + if( pStr->zBuf==0 ){ + jsonInit(pStr, ctx); + jsonAppendChar(pStr, '{'); + }else{ + jsonAppendChar(pStr, ','); + pStr->pCtx = ctx; + } + z = (const char*)sqlite3_value_text(argv[0]); + n = (u32)sqlite3_value_bytes(argv[0]); + jsonAppendString(pStr, z, n); + jsonAppendChar(pStr, ':'); + jsonAppendValue(pStr, argv[1]); + } +} +static void jsonObjectFinal(sqlite3_context *ctx){ + JsonString *pStr; + pStr = (JsonString*)sqlite3_aggregate_context(ctx, 0); + if( pStr ){ + jsonAppendChar(pStr, '}'); + if( pStr->bErr ){ + sqlite3_result_error_nomem(ctx); + if( !pStr->bStatic ) sqlite3_free(pStr->zBuf); + }else{ + sqlite3_result_text(ctx, pStr->zBuf, pStr->nUsed, + pStr->bStatic ? SQLITE_TRANSIENT : sqlite3_free); + pStr->bStatic = 1; + } + }else{ + sqlite3_result_text(ctx, "{}", 2, SQLITE_STATIC); + } + sqlite3_result_subtype(ctx, JSON_SUBTYPE); +} + + #ifndef SQLITE_OMIT_VIRTUALTABLE /**************************************************************************** ** The json_each virtual table @@ -2012,6 +2108,15 @@ int sqlite3Json1Init(sqlite3 *db){ { "json_test1", 1, 0, jsonTest1Func }, #endif }; + static const struct { + const char *zName; + int nArg; + void (*xStep)(sqlite3_context*,int,sqlite3_value**); + void (*xFinal)(sqlite3_context*); + } aAgg[] = { + { "json_group_array", 1, jsonArrayStep, jsonArrayFinal }, + { "json_group_object", 2, jsonObjectStep, jsonObjectFinal }, + }; #ifndef SQLITE_OMIT_VIRTUALTABLE static const struct { const char *zName; @@ -2027,6 +2132,11 @@ int sqlite3Json1Init(sqlite3 *db){ (void*)&aFunc[i].flag, aFunc[i].xFunc, 0, 0); } + for(i=0; i<sizeof(aAgg)/sizeof(aAgg[0]) && rc==SQLITE_OK; i++){ + rc = sqlite3_create_function(db, aAgg[i].zName, aAgg[i].nArg, + SQLITE_UTF8 | SQLITE_DETERMINISTIC, 0, + 0, aAgg[i].xStep, aAgg[i].xFinal); + } #ifndef SQLITE_OMIT_VIRTUALTABLE for(i=0; i<sizeof(aMod)/sizeof(aMod[0]) && rc==SQLITE_OK; i++){ rc = sqlite3_create_module(db, aMod[i].zName, aMod[i].pModule, 0); @@ -1,5 +1,5 @@ -C Avoid\sa\sharmless\scompiler\swarning\son\ssystems\swhere\sthe\sbyteorder\scannot\nbe\sdetermined\sat\scompile-time. -D 2015-12-24T14:53:27.528 +C Add\sthe\sjson_group_array()\sand\sjson_group_object()\saggregate\sfunctions\sto\nthe\sJSON1\sextension. +D 2015-12-30T01:07:02.009 F Makefile.in 28bcd6149e050dff35d4dcfd97e890cd387a499d F Makefile.linux-gcc 7bc79876b875010e8c8f9502eb935ca92aa3c434 F Makefile.msc 5fff077fcc46de7714ed6eebb6159a4c00eab751 @@ -191,7 +191,7 @@ F ext/misc/eval.c f971962e92ebb8b0a4e6b62949463ee454d88fa2 F ext/misc/fileio.c d4171c815d6543a9edef8308aab2951413cd8d0f F ext/misc/fuzzer.c 4c84635c71c26cfa7c2e5848cf49fe2d2cfcd767 F ext/misc/ieee754.c f190d0cc5182529acb15babd177781be1ac1718c -F ext/misc/json1.c 4f45afd9dbcd6feca8c528251efbb7fc09299a09 +F ext/misc/json1.c b7ed42db00f7429c0f0b5068209c95c41b531596 F ext/misc/nextchar.c 35c8b8baacb96d92abbb34a83a997b797075b342 F ext/misc/percentile.c bcbee3c061b884eccb80e21651daaae8e1e43c63 F ext/misc/regexp.c af92cdaa5058fcec1451e49becc7ba44dba023dc @@ -820,6 +820,7 @@ F test/jrnlmode2.test 81610545a4e6ed239ea8fa661891893385e23a1d F test/jrnlmode3.test 556b447a05be0e0963f4311e95ab1632b11c9eaa F test/json101.test f0178422b3a2418f423fd0d3caf3571c8d1b9863 F test/json102.test bf3fe7a706d30936a76a0f7a0375e1e8e73aff5a +F test/json103.test 923b288a0610ec86c0951778f7db19cbcca36ad1 F test/keyword1.test 37ef6bba5d2ed5b07ecdd6810571de2956599dff F test/lastinsert.test 42e948fd6442f07d60acbd15d33fb86473e0ef63 F test/laststmtchanges.test ae613f53819206b3222771828d024154d51db200 @@ -1405,7 +1406,7 @@ F tool/vdbe_profile.tcl 246d0da094856d72d2c12efec03250d71639d19f F tool/warnings-clang.sh f6aa929dc20ef1f856af04a730772f59283631d4 F tool/warnings.sh 48bd54594752d5be3337f12c72f28d2080cb630b F tool/win/sqlite.vsix deb315d026cc8400325c5863eef847784a219a2f -P 5d44d4a6cf5c6b983cbd846d9bc34251df8f4bc5 -R c0120dd70eca82393527f5b4b745caa6 +P 7c7b7f26306b6aa6ff35b871ad756f43f5db9838 +R 9a71d4f0e52af46677f6902fe927abd2 U drh -Z 6af8ad4397eb3ff04028cb96b6439a6e +Z d3d1e4137b6ba95a64ac245c7769bfd5 diff --git a/manifest.uuid b/manifest.uuid index 6d6b0b730..9a8ba9a33 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -7c7b7f26306b6aa6ff35b871ad756f43f5db9838
\ No newline at end of file +7f386a9332237100a345035ca213327e21d95855
\ No newline at end of file diff --git a/test/json103.test b/test/json103.test new file mode 100644 index 000000000..0f0241e6f --- /dev/null +++ b/test/json103.test @@ -0,0 +1,65 @@ +# 2015-12-30 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# This file implements tests for JSON aggregate SQL functions +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl + +ifcapable !json1 { + finish_test + return +} + +do_execsql_test json103-100 { + CREATE TABLE t1(a,b,c); + WITH RECURSIVE c(x) AS (VALUES(1) UNION SELECT x+1 FROM c WHERE x<100) + INSERT INTO t1(a,b,c) SELECT x, x%3, printf('n%d',x) FROM c; + UPDATE t1 SET a='orange' WHERE rowid=39; + UPDATE t1 SET a=32.5 WHERE rowid=31; + UPDATE t1 SET a=x'303132' WHERE rowid=29; + UPDATE t1 SET a=NULL WHERE rowid=37; + SELECT json_group_array(a) FROM t1 WHERE a<0 AND typeof(a)!='blob'; +} {{[]}} +do_catchsql_test json103-101 { + SELECT json_group_array(a) FROM t1; +} {1 {JSON cannot hold BLOB values}} +do_execsql_test json103-110 { + SELECT json_group_array(a) FROM t1 + WHERE rowid BETWEEN 31 AND 39; +} {{[32.5,32,33,34,35,36,null,38,"orange"]}} +do_execsql_test json103-111 { + SELECT json_array_length(json_group_array(a)) FROM t1 + WHERE rowid BETWEEN 31 AND 39; +} {9} +do_execsql_test json103-120 { + SELECT b, json_group_array(a) FROM t1 WHERE rowid<10 GROUP BY b ORDER BY b; +} {0 {[3,6,9]} 1 {[1,4,7]} 2 {[2,5,8]}} + +do_execsql_test json103-200 { + SELECT json_group_object(c,a) FROM t1 WHERE a<0 AND typeof(a)!='blob'; +} {{{}}} +do_catchsql_test json103-201 { + SELECT json_group_object(c,a) FROM t1; +} {1 {JSON cannot hold BLOB values}} + +do_execsql_test json103-210 { + SELECT json_group_object(c,a) FROM t1 + WHERE rowid BETWEEN 31 AND 39 AND rowid%2==1; +} {{{"n31":32.5,"n33":33,"n35":35,"n37":null,"n39":"orange"}}} +do_execsql_test json103-220 { + SELECT b, json_group_object(c,a) FROM t1 + WHERE rowid<7 GROUP BY b ORDER BY b; +} {0 {{"n3":3,"n6":6}} 1 {{"n1":1,"n4":4}} 2 {{"n2":2,"n5":5}}} + + + +finish_test |