/ Check-in [d6cadbe9]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Updates to the sqlite3_value_subtype() and sqlite3_result_subtype() documentation and to test cases for json1 dealing with those interfaces.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: d6cadbe9fefce9a7af6b2d0cb83362f967d7d89a
User & Date: drh 2015-09-11 01:22:41
Context
2015-09-11
14:15
Fix some compiler warnings in fts5 code. check-in: 0dc43611 user: dan tags: trunk
05:06
Enhance mutex initialization to prevent possible race conditions between sqlite3_initialize() and sqlite3_config(). Also, re-check sqlite3GlobalConfig.isInit after the mutex subsystem has been initialized. check-in: f6a8f577 user: mistachkin tags: mutexInitCmpSwap
01:22
Updates to the sqlite3_value_subtype() and sqlite3_result_subtype() documentation and to test cases for json1 dealing with those interfaces. check-in: d6cadbe9 user: drh tags: trunk
00:26
Add new interfaces sqlite3_value_subtype() and sqlite3_result_subtype(). Update the json1.c extension to take advantages of those interfaces to avoid the goofy '$$' path syntax and to allow nested calls to json_array() and json_object() that work as expected. check-in: db4152ae user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/sqlite.h.in.

  4358   4358   int sqlite3_value_numeric_type(sqlite3_value*);
  4359   4359   
  4360   4360   /*
  4361   4361   ** CAPI3REF: Obtaining SQL Values
  4362   4362   ** METHOD: sqlite3_value
  4363   4363   **
  4364   4364   ** The sqlite3_value_subtype(V) function returns the subtype for
  4365         -** an application-defined SQL function argument V.  The subtype
         4365  +** an [application-defined SQL function] argument V.  The subtype
  4366   4366   ** information can be used to pass a limited amount of context from
  4367   4367   ** one SQL function to another.  Use the [sqlite3_result_subtype()]
  4368   4368   ** routine to set the subtype for the return value of an SQL function.
  4369   4369   **
  4370   4370   ** SQLite makes no use of subtype itself.  It merely passes the subtype
  4371         -** from the result of one application-defined function to the input of
  4372         -** another.
         4371  +** from the result of one [application-defined SQL function] into the
         4372  +** input of another.
  4373   4373   */
  4374   4374   unsigned int sqlite3_value_subtype(sqlite3_value*);
  4375   4375   
  4376   4376   /*
  4377   4377   ** CAPI3REF: Copy And Free SQL Values
  4378   4378   ** METHOD: sqlite3_value
  4379   4379   **
................................................................................
  4674   4674   
  4675   4675   
  4676   4676   /*
  4677   4677   ** CAPI3REF: Setting The Subtype Of An SQL Function
  4678   4678   ** METHOD: sqlite3_context
  4679   4679   **
  4680   4680   ** The sqlite3_result_subtype(C,T) function causes the subtype of
  4681         -** the result from the application-defined function with context C
  4682         -** to be T.  Only the lower 8 bits of the subtype T are preserved
  4683         -** in current versions of SQLite; higher order bits are discarded.
         4681  +** the result from the [application-defined SQL function] with 
         4682  +** [sqlite3_context] C to be the value T.  Only the lower 8 bits 
         4683  +** of the subtype T are preserved in current versions of SQLite;
         4684  +** higher order bits are discarded.
  4684   4685   ** The number of subtype bytes preserved by SQLite might increase
  4685   4686   ** in future releases of SQLite.
  4686   4687   */
  4687   4688   void sqlite3_result_subtype(sqlite3_context*,unsigned int);
  4688   4689   
  4689   4690   /*
  4690   4691   ** CAPI3REF: Define New Collating Sequences

Changes to test/json102.test.

    16     16   #
    17     17   
    18     18   set testdir [file dirname $argv0]
    19     19   source $testdir/tester.tcl
    20     20   
    21     21   load_static_extension db json
    22     22   do_execsql_test json102-100 {
           23  +  SELECT json(' { "this" : "is", "a": [ "test" ] } ');
           24  +} {{{"this":"is","a":["test"]}}}
           25  +do_execsql_test json102-110 {
    23     26     SELECT json_array(1,2,'3',4);
    24     27   } {{[1,2,"3",4]}}
    25         -do_execsql_test json102-110 {
           28  +do_execsql_test json102-120 {
    26     29     SELECT json_array('[1,2]');
    27     30   } {{["[1,2]"]}}
    28         -do_execsql_test json102-120 {
           31  +do_execsql_test json102-130 {
           32  +  SELECT json_array(json_array(1,2));
           33  +} {{[[1,2]]}}
           34  +do_execsql_test json102-140 {
    29     35     SELECT json_array(1,null,'3','[4,5]','{"six":7.7}');
    30     36   } {{[1,null,"3","[4,5]","{\"six\":7.7}"]}}
    31         -do_execsql_test json102-130 {
           37  +do_execsql_test json102-150 {
           38  +  SELECT json_array(1,null,'3',json('[4,5]'),json('{"six":7.7}'));
           39  +} {{[1,null,"3",[4,5],{"six":7.7}]}}
           40  +do_execsql_test json102-160 {
    32     41     SELECT json_array_length('[1,2,3,4]');
    33     42   } {{4}}
    34         -do_execsql_test json102-140 {
           43  +do_execsql_test json102-170 {
    35     44     SELECT json_array_length('{"one":[1,2,3]}');
    36     45   } {{0}}
    37         -do_execsql_test json102-150 {
           46  +do_execsql_test json102-180 {
    38     47     SELECT json_array_length('{"one":[1,2,3]}', '$.one');
    39     48   } {{3}}
    40         -do_execsql_test json102-160 {
           49  +do_execsql_test json102-190 {
    41     50     SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$');
    42     51   } {{{"a":2,"c":[4,5,{"f":7}]}}}
    43         -do_execsql_test json102-170 {
           52  +do_execsql_test json102-200 {
    44     53     SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c');
    45     54   } {{[4,5,{"f":7}]}}
    46         -do_execsql_test json102-180 {
           55  +do_execsql_test json102-210 {
    47     56     SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2]');
    48     57   } {{{"f":7}}}
    49         -do_execsql_test json102-190 {
           58  +do_execsql_test json102-220 {
    50     59     SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2].f');
    51     60   } {{7}}
    52         -do_execsql_test json102-200 {
           61  +do_execsql_test json102-230 {
    53     62     SELECT json_extract('{"a":2,"c":[4,5],"f":7}','$.c','$.a');
    54     63   } {{[[4,5],2]}}
    55         -do_execsql_test json102-210 {
           64  +do_execsql_test json102-240 {
    56     65     SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x');
    57     66   } {{}}
    58         -do_execsql_test json102-220 {
           67  +do_execsql_test json102-250 {
    59     68     SELECT json_insert('{"a":2,"c":4}', '$.a', 99);
    60     69   } {{{"a":2,"c":4}}}
    61         -do_execsql_test json102-230 {
           70  +do_execsql_test json102-260 {
    62     71     SELECT json_insert('{"a":2,"c":4}', '$.e', 99);
    63     72   } {{{"a":2,"c":4,"e":99}}}
    64         -do_execsql_test json102-240 {
           73  +do_execsql_test json102-270 {
    65     74     SELECT json_replace('{"a":2,"c":4}', '$.a', 99);
    66     75   } {{{"a":99,"c":4}}}
    67         -do_execsql_test json102-250 {
    68         -  SELECT json_replace('{"a":2,"c":4}', '$.e', 99);
    69         -} {{{"a":2,"c":4}}}
    70         -do_execsql_test json102-260 {
    71         -  SELECT json_set('{"a":2,"c":4}', '$.a', 99);
    72         -} {{{"a":99,"c":4}}}
    73         -do_execsql_test json102-270 {
    74         -  SELECT json_set('{"a":2,"c":4}', '$.e', 99);
    75         -} {{{"a":2,"c":4,"e":99}}}
    76     76   do_execsql_test json102-280 {
    77         -  SELECT json_object('a',2,'c',4);
           77  +  SELECT json_replace('{"a":2,"c":4}', '$.e', 99);
    78     78   } {{{"a":2,"c":4}}}
    79     79   do_execsql_test json102-290 {
           80  +  SELECT json_set('{"a":2,"c":4}', '$.a', 99);
           81  +} {{{"a":99,"c":4}}}
           82  +do_execsql_test json102-300 {
           83  +  SELECT json_set('{"a":2,"c":4}', '$.e', 99);
           84  +} {{{"a":2,"c":4,"e":99}}}
           85  +do_execsql_test json102-310 {
           86  +  SELECT json_set('{"a":2,"c":4}', '$.c', '[97,96]');
           87  +} {{{"a":2,"c":"[97,96]"}}}
           88  +do_execsql_test json102-320 {
           89  +  SELECT json_set('{"a":2,"c":4}', '$.c', json('[97,96]'));
           90  +} {{{"a":2,"c":[97,96]}}}
           91  +do_execsql_test json102-330 {
           92  +  SELECT json_set('{"a":2,"c":4}', '$.c', json_array(97,96));
           93  +} {{{"a":2,"c":[97,96]}}}
           94  +do_execsql_test json102-340 {
           95  +  SELECT json_object('a',2,'c',4);
           96  +} {{{"a":2,"c":4}}}
           97  +do_execsql_test json102-350 {
    80     98     SELECT json_object('a',2,'c','{e:5}');
    81     99   } {{{"a":2,"c":"{e:5}"}}}
    82         -do_execsql_test json102-300 {
          100  +do_execsql_test json102-360 {
          101  +  SELECT json_object('a',2,'c',json_object('e',5));
          102  +} {{{"a":2,"c":{"e":5}}}}
          103  +do_execsql_test json102-370 {
    83    104     SELECT json_remove('[0,1,2,3,4]','$[2]');
    84    105   } {{[0,1,3,4]}}
    85         -do_execsql_test json102-310 {
          106  +do_execsql_test json102-380 {
    86    107     SELECT json_remove('[0,1,2,3,4]','$[2]','$[0]');
    87    108   } {{[1,3,4]}}
    88         -do_execsql_test json102-320 {
          109  +do_execsql_test json102-390 {
    89    110     SELECT json_remove('[0,1,2,3,4]','$[0]','$[2]');
    90    111   } {{[1,2,4]}}
    91         -do_execsql_test json102-330 {
          112  +do_execsql_test json102-400 {
    92    113     SELECT json_remove('{"x":25,"y":42}');
    93    114   } {{{"x":25,"y":42}}}
    94         -do_execsql_test json102-340 {
          115  +do_execsql_test json102-410 {
    95    116     SELECT json_remove('{"x":25,"y":42}','$.z');
    96    117   } {{{"x":25,"y":42}}}
    97         -do_execsql_test json102-350 {
          118  +do_execsql_test json102-420 {
    98    119     SELECT json_remove('{"x":25,"y":42}','$.y');
    99    120   } {{{"x":25}}}
   100         -do_execsql_test json102-360 {
          121  +do_execsql_test json102-430 {
   101    122     SELECT json_remove('{"x":25,"y":42}','$');
   102    123   } {{}}
   103         -do_execsql_test json102-370 {
          124  +do_execsql_test json102-440 {
   104    125     SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}');
   105    126   } {{object}}
   106         -do_execsql_test json102-380 {
          127  +do_execsql_test json102-450 {
   107    128     SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$');
   108    129   } {{object}}
   109         -do_execsql_test json102-390 {
          130  +do_execsql_test json102-460 {
   110    131     SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a');
   111    132   } {{array}}
   112         -do_execsql_test json102-400 {
          133  +do_execsql_test json102-470 {
   113    134     SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[0]');
   114    135   } {{integer}}
   115         -do_execsql_test json102-410 {
          136  +do_execsql_test json102-480 {
   116    137     SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[1]');
   117    138   } {{real}}
   118         -do_execsql_test json102-420 {
          139  +do_execsql_test json102-490 {
   119    140     SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[2]');
   120    141   } {{true}}
   121         -do_execsql_test json102-430 {
          142  +do_execsql_test json102-500 {
   122    143     SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[3]');
   123    144   } {{false}}
   124         -do_execsql_test json102-440 {
          145  +do_execsql_test json102-510 {
   125    146     SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[4]');
   126    147   } {{null}}
   127         -do_execsql_test json102-450 {
          148  +do_execsql_test json102-520 {
   128    149     SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[5]');
   129    150   } {{text}}
   130         -do_execsql_test json102-460 {
          151  +do_execsql_test json102-530 {
   131    152     SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[6]');
   132    153   } {{}}
   133         -do_execsql_test json102-470 {
   134         -  SELECT json_valid('{"x":35}');
          154  +do_execsql_test json102-540 {
          155  +  SELECT json_valid(char(123)||'"x":35'||char(125));
   135    156   } {{1}}
   136         -do_execsql_test json102-480 {
   137         -  SELECT json_valid('{"x":35'); -- }
          157  +do_execsql_test json102-550 {
          158  +  SELECT json_valid(char(123)||'"x":35');
   138    159   } {{0}}
          160  +
   139    161   
   140    162   ifcapable vtab {
   141         -do_execsql_test json102-500 {
          163  +do_execsql_test json102-1000 {
   142    164     CREATE TABLE user(name,phone);
   143    165     INSERT INTO user(name,phone) VALUES
   144    166        ('Alice','["919-555-2345","804-555-3621"]'),
   145    167        ('Bob','["201-555-8872"]'),
   146    168        ('Cindy','["704-555-9983"]'),
   147    169        ('Dave','["336-555-8421","704-555-4321","803-911-4421"]');
   148    170     SELECT DISTINCT user.name
   149    171       FROM user, json_each(user.phone)
   150    172      WHERE json_each.value LIKE '704-%'
   151    173      ORDER BY 1;
   152    174   } {Cindy Dave}
   153    175   
   154         -do_execsql_test json102-510 {
          176  +do_execsql_test json102-1010 {
   155    177     UPDATE user
   156    178        SET phone=json_extract(phone,'$[0]')
   157    179      WHERE json_array_length(phone)<2;
   158    180     SELECT name, substr(phone,1,5) FROM user ORDER BY name;
   159    181   } {Alice {["919} Bob 201-5 Cindy 704-5 Dave {["336}}
   160         -do_execsql_test json102-511 {
          182  +do_execsql_test json102-1011 {
   161    183     SELECT name FROM user WHERE phone LIKE '704-%'
   162    184     UNION
   163    185     SELECT user.name
   164    186       FROM user, json_each(user.phone)
   165    187      WHERE json_valid(user.phone)
   166    188        AND json_each.value LIKE '704-%';
   167    189   } {Cindy Dave}
   168    190   
   169         -do_execsql_test json102-600 {
          191  +do_execsql_test json102-1100 {
   170    192     CREATE TABLE big(json JSON);
   171    193     INSERT INTO big(json) VALUES('{
   172    194       "id":123,
   173    195       "stuff":[1,2,3,4],
   174    196       "partlist":[
   175    197          {"uuid":"bb108722-572e-11e5-9320-7f3b63a4ca74"},
   176    198          {"uuid":"c690dc14-572e-11e5-95f9-dfc8861fd535"},
................................................................................
   199    221       1 {$.partlist[2].subassembly[0].uuid} 6fa5181e-5721-11e5-a04e-57f3d7b32808 \
   200    222       2 {$.id} 456 \
   201    223       2 {$.stuff[0]} hello \
   202    224       2 {$.stuff[1]} world \
   203    225       2 {$.stuff[2]} xyzzy \
   204    226       2 {$.partlist[0].uuid} 0 \
   205    227       2 {$.partlist[1].uuid} c690dc14-572e-11e5-95f9-dfc8861fd535]
   206         -do_execsql_test json102-610 {
          228  +do_execsql_test json102-1110 {
   207    229     SELECT big.rowid, fullkey, value
   208    230       FROM big, json_tree(big.json)
   209    231      WHERE json_tree.type NOT IN ('object','array')
   210    232      ORDER BY +big.rowid, +json_tree.id
   211    233   } $correct_answer
   212         -do_execsql_test json102-620 {
          234  +do_execsql_test json102-1120 {
   213    235     SELECT big.rowid, fullkey, atom
   214    236       FROM big, json_tree(big.json)
   215    237      WHERE atom IS NOT NULL
   216    238      ORDER BY +big.rowid, +json_tree.id
   217    239   } $correct_answer
   218    240   
   219         -do_execsql_test json102-630 {
          241  +do_execsql_test json102-1130 {
   220    242     SELECT DISTINCT json_extract(big.json,'$.id')
   221    243       FROM big, json_tree(big.json,'$.partlist')
   222    244      WHERE json_tree.key='uuid'
   223    245        AND json_tree.value='6fa5181e-5721-11e5-a04e-57f3d7b32808';
   224    246   } {123}
   225         -do_execsql_test json102-631 {
          247  +do_execsql_test json102-1131 {
   226    248     SELECT DISTINCT json_extract(big.json,'$.id')
   227    249       FROM big, json_tree(big.json,'$')
   228    250      WHERE json_tree.key='uuid'
   229    251        AND json_tree.value='6fa5181e-5721-11e5-a04e-57f3d7b32808';
   230    252   } {123}
   231         -do_execsql_test json102-632 {
          253  +do_execsql_test json102-1132 {
   232    254     SELECT DISTINCT json_extract(big.json,'$.id')
   233    255       FROM big, json_tree(big.json)
   234    256      WHERE json_tree.key='uuid'
   235    257        AND json_tree.value='6fa5181e-5721-11e5-a04e-57f3d7b32808';
   236    258   } {123}
   237    259   } ;# end ifcapable vtab
   238    260   
   239    261   finish_test