/ Check-in [dde8afdd]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Begin adding an extension that provides JSON SQL functions.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | json
Files: files | file ages | folders
SHA1: dde8afdd8dba1d92560326dca7c1cdfedbe5e070
User & Date: drh 2015-08-12 16:49:40
Context
2015-08-12
17:23
Add the json_object() function. check-in: 414a95f3 user: drh tags: json
16:49
Begin adding an extension that provides JSON SQL functions. check-in: dde8afdd user: drh tags: json
12:11
Add the xPhraseFirst() and xPhraseNext() fts5 APIs, for faster iteration through a single phrases position list. Also optimize xInst() and xInstCount() a bit. check-in: f7682435 user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to Makefile.in.

   413    413     $(TOP)/ext/misc/closure.c \
   414    414     $(TOP)/ext/misc/eval.c \
   415    415     $(TOP)/ext/misc/fileio.c \
   416    416     $(TOP)/ext/misc/fuzzer.c \
   417    417     $(TOP)/ext/fts5/fts5_tcl.c \
   418    418     $(TOP)/ext/fts5/fts5_test_mi.c \
   419    419     $(TOP)/ext/misc/ieee754.c \
          420  +  $(TOP)/ext/misc/json.c \
   420    421     $(TOP)/ext/misc/nextchar.c \
   421    422     $(TOP)/ext/misc/percentile.c \
   422    423     $(TOP)/ext/misc/regexp.c \
   423    424     $(TOP)/ext/misc/spellfix.c \
   424    425     $(TOP)/ext/misc/totype.c \
   425    426     $(TOP)/ext/misc/wholenumber.c
   426    427   

Changes to Makefile.msc.

  1079   1079     $(TOP)\ext\misc\eval.c \
  1080   1080     $(TOP)\ext\misc\fileio.c \
  1081   1081     $(TOP)\ext\misc\fuzzer.c \
  1082   1082     fts5.c \
  1083   1083     $(TOP)\ext\fts5\fts5_tcl.c \
  1084   1084     $(TOP)\ext\fts5\fts5_test_mi.c \
  1085   1085     $(TOP)\ext\misc\ieee754.c \
         1086  +  $(TOP)\ext\misc\json.c \
  1086   1087     $(TOP)\ext\misc\nextchar.c \
  1087   1088     $(TOP)\ext\misc\percentile.c \
  1088   1089     $(TOP)\ext\misc\regexp.c \
  1089   1090     $(TOP)\ext\misc\spellfix.c \
  1090   1091     $(TOP)\ext\misc\totype.c \
  1091   1092     $(TOP)\ext\misc\wholenumber.c
  1092   1093   

Added ext/misc/json.c.

            1  +/*
            2  +** 2015-08-12
            3  +**
            4  +** The author disclaims copyright to this source code.  In place of
            5  +** a legal notice, here is a blessing:
            6  +**
            7  +**    May you do good and not evil.
            8  +**    May you find forgiveness for yourself and forgive others.
            9  +**    May you share freely, never taking more than you give.
           10  +**
           11  +******************************************************************************
           12  +**
           13  +** This SQLite extension implements JSON functions.  The interface is
           14  +** modeled after MySQL JSON functions:
           15  +**
           16  +**     https://dev.mysql.com/doc/refman/5.7/en/json.html
           17  +**
           18  +** JSON is pure text.  JSONB is a binary encoding that is smaller and easier
           19  +** to parse but which holds the equivalent information.  Conversions between
           20  +** JSON and JSONB are lossless.
           21  +**
           22  +** Most of the functions here will accept either JSON or JSONB input.  The
           23  +** input is understood to be JSONB if it a BLOB and JSON if the input is
           24  +** of any other type.  Functions that begin with the "json_" prefix return
           25  +** JSON and functions that begin with "jsonb_" return JSONB.
           26  +*/
           27  +#include "sqlite3ext.h"
           28  +SQLITE_EXTENSION_INIT1
           29  +#include <assert.h>
           30  +#include <string.h>
           31  +
           32  +/* Unsigned integer types */
           33  +typedef sqlite3_uint64 u64;
           34  +typedef unsigned int u32;
           35  +typedef unsigned char u8;
           36  +
           37  +/* An instance of this object represents a JSON string under construction.
           38  +*/
           39  +typedef struct Json Json;
           40  +struct Json {
           41  +  sqlite3_context *pCtx;   /* Function context - put error messages here */
           42  +  char *zBuf;              /* Append JSON text here */
           43  +  u64 nAlloc;              /* Bytes of storage available in zBuf[] */
           44  +  u64 nUsed;               /* Bytes of zBuf[] currently used */
           45  +  u8 bStatic;              /* True if zBuf is static space */
           46  +  u8 mallocFailed;         /* True if an OOM has been encountered */
           47  +  char zSpace[100];        /* Initial static space */
           48  +};
           49  +
           50  +/* Set the Json object to an empty string
           51  +*/
           52  +static void jsonZero(Json *p){
           53  +  p->zBuf = p->zSpace;
           54  +  p->nAlloc = sizeof(p->zSpace);
           55  +  p->nUsed = 0;
           56  +  p->bStatic = 1;
           57  +}
           58  +
           59  +/* Initialize the Json object
           60  +*/
           61  +static void jsonInit(Json *p, sqlite3_context *pCtx){
           62  +  p->pCtx = pCtx;
           63  +  p->mallocFailed = 0;
           64  +  jsonZero(p);
           65  +}
           66  +
           67  +
           68  +/* Free all allocated memory and reset the Json object back to its
           69  +** initial state.
           70  +*/
           71  +static void jsonReset(Json *p){
           72  +  if( !p->bStatic ) sqlite3_free(p->zBuf);
           73  +  jsonZero(p);
           74  +}
           75  +
           76  +
           77  +/* Report an out-of-memory (OOM) condition 
           78  +*/
           79  +static void jsonOom(Json *p){
           80  +  p->mallocFailed = 1;
           81  +  sqlite3_result_error_nomem(p->pCtx);
           82  +  jsonReset(p);
           83  +}
           84  +
           85  +/* Enlarge pJson->zBuf so that it can hold at least N more bytes.
           86  +** Return zero on success.  Return non-zero on an OOM error
           87  +*/
           88  +static int jsonGrow(Json *p, u32 N){
           89  +  u64 nTotal = N<p->nAlloc ? p->nAlloc*2 : p->nAlloc+N+100;
           90  +  char *zNew;
           91  +  if( p->bStatic ){
           92  +    if( p->mallocFailed ) return SQLITE_NOMEM;
           93  +    zNew = sqlite3_malloc64(nTotal);
           94  +    if( zNew==0 ){
           95  +      jsonOom(p);
           96  +      return SQLITE_NOMEM;
           97  +    }
           98  +    memcpy(zNew, p->zBuf, p->nUsed);
           99  +    p->zBuf = zNew;
          100  +    p->bStatic = 0;
          101  +  }else{
          102  +    zNew = sqlite3_realloc64(p->zBuf, nTotal);
          103  +    if( zNew==0 ){
          104  +      jsonOom(p);
          105  +      return SQLITE_NOMEM;
          106  +    }
          107  +    p->zBuf = zNew;
          108  +  }
          109  +  p->nAlloc = nTotal;
          110  +  return SQLITE_OK;
          111  +}
          112  +
          113  +/* Append N bytes from zIn onto the end of the Json string.
          114  +*/
          115  +static void jsonAppendRaw(Json *p, const char *zIn, u32 N){
          116  +  if( (N+p->nUsed >= p->nAlloc) && jsonGrow(p,N)!=0 ) return;
          117  +  memcpy(p->zBuf+p->nUsed, zIn, N);
          118  +  p->nUsed += N;
          119  +}
          120  +
          121  +/* Append the N-byte string in zIn to the end of the Json string
          122  +** under construction.  Enclose the string in "..." and escape
          123  +** any double-quotes or backslash characters contained within the
          124  +** string.
          125  +*/
          126  +static void jsonAppendString(Json *p, const char *zIn, u32 N){
          127  +  u32 i;
          128  +  if( (N+p->nUsed+2 >= p->nAlloc) && jsonGrow(p,N+2)!=0 ) return;
          129  +  p->zBuf[p->nUsed++] = '"';
          130  +  for(i=0; i<N; i++){
          131  +    char c = zIn[i];
          132  +    if( c=='"' || c=='\\' ){
          133  +      if( (p->nUsed+N+1-i > p->nAlloc) && jsonGrow(p,N+1-i)!=0 ) return;
          134  +      p->zBuf[p->nUsed++] = '\\';
          135  +    }
          136  +    p->zBuf[p->nUsed++] = c;
          137  +  }
          138  +  p->zBuf[p->nUsed++] = '"';
          139  +}
          140  +
          141  +/* Make pJson the result of the SQL function.
          142  +*/
          143  +static void jsonResult(Json *p){
          144  +  if( p->mallocFailed==0 ){
          145  +    sqlite3_result_text64(p->pCtx, p->zBuf, p->nUsed, 
          146  +                          p->bStatic ? SQLITE_TRANSIENT : sqlite3_free,
          147  +                          SQLITE_UTF8);
          148  +    jsonZero(p);
          149  +  }
          150  +  assert( p->bStatic );
          151  +}
          152  +
          153  +/*
          154  +** Implementation of the json_array(VALUE,...) function.  Return a JSON
          155  +** array that contains all values given in arguments.  Or if any argument
          156  +** is a BLOB, throw an error.
          157  +*/
          158  +static void jsonArrayFunc(
          159  +  sqlite3_context *context,
          160  +  int argc,
          161  +  sqlite3_value **argv
          162  +){
          163  +  int i;
          164  +  Json jx;
          165  +  char cSep = '[';
          166  +
          167  +  jsonInit(&jx, context);
          168  +  for(i=0; i<argc; i++){
          169  +    jsonAppendRaw(&jx, &cSep, 1);
          170  +    cSep = ',';
          171  +    switch( sqlite3_value_type(argv[i]) ){
          172  +      case SQLITE_NULL: {
          173  +        jsonAppendRaw(&jx, "null", 4);
          174  +        break;
          175  +      }
          176  +      case SQLITE_INTEGER:
          177  +      case SQLITE_FLOAT: {
          178  +        const char *z = (const char*)sqlite3_value_text(argv[i]);
          179  +        u32 n = (u32)sqlite3_value_bytes(argv[i]);
          180  +        jsonAppendRaw(&jx, z, n);
          181  +        break;
          182  +      }
          183  +      case SQLITE_TEXT: {
          184  +        const char *z = (const char*)sqlite3_value_text(argv[i]);
          185  +        u32 n = (u32)sqlite3_value_bytes(argv[i]);
          186  +        jsonAppendString(&jx, z, n);
          187  +        break;
          188  +      }
          189  +      default: {
          190  +        jsonZero(&jx);
          191  +        sqlite3_result_error(context, "JSON cannot hold BLOB values", -1);
          192  +        return;
          193  +      }
          194  +    }
          195  +  }
          196  +  jsonAppendRaw(&jx, "]", 1);
          197  +  jsonResult(&jx);      
          198  +}
          199  +
          200  +#ifdef _WIN32
          201  +__declspec(dllexport)
          202  +#endif
          203  +int sqlite3_json_init(
          204  +  sqlite3 *db, 
          205  +  char **pzErrMsg, 
          206  +  const sqlite3_api_routines *pApi
          207  +){
          208  +  int rc = SQLITE_OK;
          209  +  int i;
          210  +  static const struct {
          211  +     const char *zName;
          212  +     int nArg;
          213  +     void (*xFunc)(sqlite3_context*,int,sqlite3_value**);
          214  +  } aFunc[] = {
          215  +    { "json_array",  -1,  jsonArrayFunc },
          216  +  };
          217  +  SQLITE_EXTENSION_INIT2(pApi);
          218  +  (void)pzErrMsg;  /* Unused parameter */
          219  +  for(i=0; i<sizeof(aFunc)/sizeof(aFunc[0]) && rc==SQLITE_OK; i++){
          220  +    rc = sqlite3_create_function(db, aFunc[i].zName, aFunc[i].nArg,
          221  +                                 SQLITE_UTF8 | SQLITE_DETERMINISTIC, 0,
          222  +                                 aFunc[i].xFunc, 0, 0);
          223  +  }
          224  +  return rc;
          225  +}

Changes to main.mk.

   293    293   TESTSRC += \
   294    294     $(TOP)/ext/misc/amatch.c \
   295    295     $(TOP)/ext/misc/closure.c \
   296    296     $(TOP)/ext/misc/eval.c \
   297    297     $(TOP)/ext/misc/fileio.c \
   298    298     $(TOP)/ext/misc/fuzzer.c \
   299    299     $(TOP)/ext/misc/ieee754.c \
          300  +  $(TOP)/ext/misc/json.c \
   300    301     $(TOP)/ext/misc/nextchar.c \
   301    302     $(TOP)/ext/misc/percentile.c \
   302    303     $(TOP)/ext/misc/regexp.c \
   303    304     $(TOP)/ext/misc/spellfix.c \
   304    305     $(TOP)/ext/misc/totype.c \
   305    306     $(TOP)/ext/misc/wholenumber.c \
   306    307     $(TOP)/ext/misc/vfslog.c \

Changes to src/test1.c.

  6373   6373   ){
  6374   6374     extern int sqlite3_amatch_init(sqlite3*,char**,const sqlite3_api_routines*);
  6375   6375     extern int sqlite3_closure_init(sqlite3*,char**,const sqlite3_api_routines*);
  6376   6376     extern int sqlite3_eval_init(sqlite3*,char**,const sqlite3_api_routines*);
  6377   6377     extern int sqlite3_fileio_init(sqlite3*,char**,const sqlite3_api_routines*);
  6378   6378     extern int sqlite3_fuzzer_init(sqlite3*,char**,const sqlite3_api_routines*);
  6379   6379     extern int sqlite3_ieee_init(sqlite3*,char**,const sqlite3_api_routines*);
         6380  +  extern int sqlite3_json_init(sqlite3*,char**,const sqlite3_api_routines*);
  6380   6381     extern int sqlite3_nextchar_init(sqlite3*,char**,const sqlite3_api_routines*);
  6381   6382     extern int sqlite3_percentile_init(sqlite3*,char**,const sqlite3_api_routines*);
  6382   6383     extern int sqlite3_regexp_init(sqlite3*,char**,const sqlite3_api_routines*);
  6383   6384     extern int sqlite3_spellfix_init(sqlite3*,char**,const sqlite3_api_routines*);
  6384   6385     extern int sqlite3_totype_init(sqlite3*,char**,const sqlite3_api_routines*);
  6385   6386     extern int sqlite3_wholenumber_init(sqlite3*,char**,const sqlite3_api_routines*);
  6386   6387     extern int sqlite3_fts5_init(sqlite3*,char**,const sqlite3_api_routines*);
................................................................................
  6393   6394       { "eval",                  sqlite3_eval_init                 },
  6394   6395   #ifdef SQLITE_ENABLE_FTS5
  6395   6396       { "fts5",                  sqlite3_fts5_init                 },
  6396   6397   #endif
  6397   6398       { "fileio",                sqlite3_fileio_init               },
  6398   6399       { "fuzzer",                sqlite3_fuzzer_init               },
  6399   6400       { "ieee754",               sqlite3_ieee_init                 },
         6401  +    { "json",                  sqlite3_json_init                 },
  6400   6402       { "nextchar",              sqlite3_nextchar_init             },
  6401   6403       { "percentile",            sqlite3_percentile_init           },
  6402   6404       { "regexp",                sqlite3_regexp_init               },
  6403   6405       { "spellfix",              sqlite3_spellfix_init             },
  6404   6406       { "totype",                sqlite3_totype_init               },
  6405   6407       { "wholenumber",           sqlite3_wholenumber_init          },
  6406   6408     };

Added test/json1.test.

            1  +# 2015-08-12
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# This file implements tests for JSON SQL functions extension to the
           12  +# SQLite library.
           13  +#
           14  +
           15  +set testdir [file dirname $argv0]
           16  +source $testdir/tester.tcl
           17  +
           18  +load_static_extension db json
           19  +do_execsql_test json1-1.1 {
           20  +  SELECT json_array(1,2.5,null,'hello');
           21  +} {[1,2.5,null,"hello"]}
           22  +do_execsql_test json1-1.2 {
           23  +  SELECT hex(json_array('String "\ Test'));
           24  +} {5B22537472696E67205C225C5C2054657374225D}
           25  +do_catchsql_test json1-1.3 {
           26  +  SELECT json_array(1,2,x'abcd',3);
           27  +} {1 {JSON cannot hold BLOB values}}
           28  +do_execsql_test json1-1.4 {
           29  +  SELECT json_array(-9223372036854775808,9223372036854775807,0,1,-1,
           30  +                    0.0, 1.0, -1.0, -1e99, +2e100,
           31  +                    'one','two','three',
           32  +                    4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18,
           33  +                    19, NULL, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31,
           34  +                    'abcdefghijklmnopqrstuvwyxzABCDEFGHIJKLMNOPQRSTUVWXYZ',
           35  +                    'abcdefghijklmnopqrstuvwyxzABCDEFGHIJKLMNOPQRSTUVWXYZ',
           36  +                    'abcdefghijklmnopqrstuvwyxzABCDEFGHIJKLMNOPQRSTUVWXYZ',
           37  +                    99);
           38  +} {[-9223372036854775808,9223372036854775807,0,1,-1,0.0,1.0,-1.0,-1.0e+99,2.0e+100,"one","two","three",4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,null,21,22,23,24,25,26,27,28,29,30,31,"abcdefghijklmnopqrstuvwyxzABCDEFGHIJKLMNOPQRSTUVWXYZ","abcdefghijklmnopqrstuvwyxzABCDEFGHIJKLMNOPQRSTUVWXYZ","abcdefghijklmnopqrstuvwyxzABCDEFGHIJKLMNOPQRSTUVWXYZ",99]}
           39  +
           40  +finish_test