1 /**
2 * Copyright (c) 2014, Timothy Stack
3 *
4 * All rights reserved.
5 *
6 * Redistribution and use in source and binary forms, with or without
7 * modification, are permitted provided that the following conditions are met:
8 *
9 * * Redistributions of source code must retain the above copyright notice, this
10 * list of conditions and the following disclaimer.
11 * * Redistributions in binary form must reproduce the above copyright notice,
12 * this list of conditions and the following disclaimer in the documentation
13 * and/or other materials provided with the distribution.
14 * * Neither the name of Timothy Stack nor the names of its contributors
15 * may be used to endorse or promote products derived from this software
16 * without specific prior written permission.
17 *
18 * THIS SOFTWARE IS PROVIDED BY THE REGENTS AND CONTRIBUTORS ''AS IS'' AND ANY
19 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
20 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
21 * DISCLAIMED. IN NO EVENT SHALL THE REGENTS OR CONTRIBUTORS BE LIABLE FOR ANY
22 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
23 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
24 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON
25 * ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
26 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
27 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
28 *
29 * @file json-extension-functions.cc
30 */
31
32 #include "config.h"
33
34 #include <string.h>
35
36 #include <string>
37
38 #include "sqlite3.h"
39
40 #include "yajlpp/yajlpp.hh"
41 #include "yajlpp/json_op.hh"
42 #include "mapbox/variant.hpp"
43 #include "vtab_module.hh"
44 #include "vtab_module_json.hh"
45
46 #include "lnav_util.hh"
47 #include "yajl/api/yajl_gen.h"
48 #include "sqlite-extension-func.hh"
49
50 using namespace std;
51 using namespace mapbox;
52
53 #define JSON_SUBTYPE 74 /* Ascii for "J" */
54
55 class sql_json_op : public json_op {
56 public:
sql_json_op(json_ptr & ptr)57 sql_json_op(json_ptr &ptr) : json_op(ptr) { };
58
59 int sjo_type{-1};
60 string sjo_str;
61 int64_t sjo_int{0};
62 double sjo_float{0.0};
63 };
64
null_or_default(sqlite3_context * context,int argc,sqlite3_value ** argv)65 static void null_or_default(sqlite3_context *context, int argc, sqlite3_value **argv)
66 {
67 if (argc > 2) {
68 sqlite3_result_value(context, argv[2]);
69 }
70 else {
71 sqlite3_result_null(context);
72 }
73 }
74
75 struct contains_userdata {
76 util::variant<const char *, sqlite3_int64, bool> cu_match_value{false};
77 size_t cu_depth{0};
78 bool cu_result{false};
79 };
80
contains_string(void * ctx,const unsigned char * str,size_t len)81 static int contains_string(void *ctx, const unsigned char *str, size_t len)
82 {
83 auto &cu = *((contains_userdata *) ctx);
84
85 if (cu.cu_depth <= 1 &&
86 strncmp((const char *) str, cu.cu_match_value.get<const char *>(), len) == 0) {
87 cu.cu_result = true;
88 }
89
90 return 1;
91 }
92
contains_integer(void * ctx,long long value)93 static int contains_integer(void *ctx, long long value)
94 {
95 auto &cu = *((contains_userdata *) ctx);
96
97 if (cu.cu_depth <= 1 && cu.cu_match_value.get<sqlite3_int64>() == value) {
98 cu.cu_result = true;
99 }
100
101 return 1;
102 }
103
contains_null(void * ctx)104 static int contains_null(void *ctx)
105 {
106 auto &cu = *((contains_userdata *) ctx);
107
108 cu.cu_result = true;
109
110 return 1;
111 }
112
json_contains(vtab_types::nullable<const char> nullable_json_in,sqlite3_value * value)113 static bool json_contains(vtab_types::nullable<const char> nullable_json_in, sqlite3_value *value)
114 {
115 if (nullable_json_in.n_value == nullptr || nullable_json_in.n_value[0] == '\0') {
116 return false;
117 }
118
119 auto json_in = nullable_json_in.n_value;
120 auto_mem<yajl_handle_t> handle(yajl_free);
121 yajl_callbacks cb;
122 contains_userdata cu;
123
124 memset(&cb, 0, sizeof(cb));
125 handle = yajl_alloc(&cb, nullptr, &cu);
126
127 cb.yajl_start_array = +[](void *ctx) {
128 auto &cu = *((contains_userdata *) ctx);
129
130 cu.cu_depth += 1;
131
132 return 1;
133 };
134 cb.yajl_end_array = +[](void *ctx) {
135 auto &cu = *((contains_userdata *) ctx);
136
137 cu.cu_depth -= 1;
138
139 return 1;
140 };
141 cb.yajl_start_map = +[](void *ctx) {
142 auto &cu = *((contains_userdata *) ctx);
143
144 cu.cu_depth += 2;
145
146 return 1;
147 };
148 cb.yajl_end_map = +[](void *ctx) {
149 auto &cu = *((contains_userdata *) ctx);
150
151 cu.cu_depth -= 2;
152
153 return 1;
154 };
155
156 switch (sqlite3_value_type(value)) {
157 case SQLITE3_TEXT:
158 cb.yajl_string = contains_string;
159 cu.cu_match_value = (const char *) sqlite3_value_text(value);
160 break;
161 case SQLITE_INTEGER:
162 cb.yajl_integer = contains_integer;
163 cu.cu_match_value = sqlite3_value_int64(value);
164 break;
165 case SQLITE_NULL:
166 cb.yajl_null = contains_null;
167 break;
168 }
169
170 if (yajl_parse(handle.in(), (const unsigned char *) json_in, strlen(json_in)) != yajl_status_ok ||
171 yajl_complete_parse(handle.in()) != yajl_status_ok) {
172 throw yajlpp_error(handle.in(), json_in, strlen(json_in));
173 }
174
175 return cu.cu_result;
176 }
177
gen_handle_null(void * ctx)178 static int gen_handle_null(void *ctx)
179 {
180 sql_json_op *sjo = (sql_json_op *)ctx;
181 yajl_gen gen = (yajl_gen)sjo->jo_ptr_data;
182
183 if (sjo->jo_ptr.jp_state == json_ptr::MS_DONE) {
184 sjo->sjo_type = SQLITE_NULL;
185 }
186 else {
187 sjo->jo_ptr_error_code = yajl_gen_null(gen);
188 }
189
190 return sjo->jo_ptr_error_code == yajl_gen_status_ok;
191 }
192
gen_handle_boolean(void * ctx,int boolVal)193 static int gen_handle_boolean(void *ctx, int boolVal)
194 {
195 sql_json_op *sjo = (sql_json_op *)ctx;
196 yajl_gen gen = (yajl_gen)sjo->jo_ptr_data;
197
198 if (sjo->jo_ptr.jp_state == json_ptr::MS_DONE) {
199 sjo->sjo_type = SQLITE_INTEGER;
200 sjo->sjo_int = boolVal;
201 }
202 else {
203 sjo->jo_ptr_error_code = yajl_gen_bool(gen, boolVal);
204 }
205
206 return sjo->jo_ptr_error_code == yajl_gen_status_ok;
207 }
208
gen_handle_string(void * ctx,const unsigned char * stringVal,size_t len)209 static int gen_handle_string(void *ctx, const unsigned char * stringVal, size_t len)
210 {
211 sql_json_op *sjo = (sql_json_op *)ctx;
212 yajl_gen gen = (yajl_gen)sjo->jo_ptr_data;
213
214 if (sjo->jo_ptr.jp_state == json_ptr::MS_DONE) {
215 sjo->sjo_type = SQLITE3_TEXT;
216 sjo->sjo_str = string((char *)stringVal, len);
217 }
218 else {
219 sjo->jo_ptr_error_code = yajl_gen_string(gen, stringVal, len);
220 }
221
222 return sjo->jo_ptr_error_code == yajl_gen_status_ok;
223 }
224
gen_handle_number(void * ctx,const char * numval,size_t numlen)225 static int gen_handle_number(void *ctx, const char *numval, size_t numlen)
226 {
227 sql_json_op *sjo = (sql_json_op *)ctx;
228 yajl_gen gen = (yajl_gen)sjo->jo_ptr_data;
229
230 if (sjo->jo_ptr.jp_state == json_ptr::MS_DONE) {
231 if (strtonum(sjo->sjo_int, numval, numlen) == numlen) {
232 sjo->sjo_type = SQLITE_INTEGER;
233 } else {
234 auto numstr = std::string(numval, numlen);
235
236 sjo->sjo_float = std::stod(numstr);
237 sjo->sjo_type = SQLITE_FLOAT;
238 }
239 }
240 else {
241 sjo->jo_ptr_error_code = yajl_gen_number(gen, numval, numlen);
242 }
243
244 return sjo->jo_ptr_error_code == yajl_gen_status_ok;
245 }
246
sql_jget(sqlite3_context * context,int argc,sqlite3_value ** argv)247 static void sql_jget(sqlite3_context *context,
248 int argc, sqlite3_value **argv)
249 {
250 if (argc < 2) {
251 sqlite3_result_error(context, "expecting JSON value and pointer", -1);
252 return;
253 }
254
255 if (sqlite3_value_type(argv[0]) == SQLITE_NULL) {
256 null_or_default(context, argc, argv);
257 return;
258 }
259
260 const char *json_in = (const char *)sqlite3_value_text(argv[0]);
261
262 if (sqlite3_value_type(argv[1]) == SQLITE_NULL) {
263 sqlite3_result_text(context, json_in, -1, SQLITE_TRANSIENT);
264 return;
265 }
266
267 const char *ptr_in = (const char *)sqlite3_value_text(argv[1]);
268 json_ptr jp(ptr_in);
269 sql_json_op jo(jp);
270 auto_mem<yajl_handle_t> handle(yajl_free);
271 unsigned char *err;
272 yajlpp_gen gen;
273
274 yajl_gen_config(gen, yajl_gen_beautify, false);
275
276 jo.jo_ptr_callbacks = json_op::gen_callbacks;
277 jo.jo_ptr_callbacks.yajl_null = gen_handle_null;
278 jo.jo_ptr_callbacks.yajl_boolean = gen_handle_boolean;
279 jo.jo_ptr_callbacks.yajl_string = gen_handle_string;
280 jo.jo_ptr_callbacks.yajl_number = gen_handle_number;
281 jo.jo_ptr_data = gen.get_handle();
282
283 handle.reset(yajl_alloc(&json_op::ptr_callbacks, nullptr, &jo));
284 switch (yajl_parse(handle.in(), (const unsigned char *)json_in, strlen(json_in))) {
285 case yajl_status_error: {
286 err = yajl_get_error(handle.in(), 0, (const unsigned char *) json_in,
287 strlen(json_in));
288 sqlite3_result_error(context, (const char *) err, -1);
289 yajl_free_error(handle.in(), err);
290 return;
291 }
292 case yajl_status_client_canceled:
293 if (jo.jo_ptr.jp_state == json_ptr::MS_ERR_INVALID_ESCAPE) {
294 sqlite3_result_error(context, jo.jo_ptr.error_msg().c_str(), -1);
295 }
296 else {
297 null_or_default(context, argc, argv);
298 }
299 return;
300 default:
301 break;
302 }
303
304 switch (yajl_complete_parse(handle.in())) {
305 case yajl_status_error: {
306 err = yajl_get_error(handle.in(), 0, (const unsigned char *) json_in,
307 strlen(json_in));
308 sqlite3_result_error(context, (const char *) err, -1);
309 yajl_free_error(handle.in(), err);
310 return;
311 }
312 case yajl_status_client_canceled:
313 if (jo.jo_ptr.jp_state == json_ptr::MS_ERR_INVALID_ESCAPE) {
314 sqlite3_result_error(context, jo.jo_ptr.error_msg().c_str(), -1);
315 }
316 else {
317 null_or_default(context, argc, argv);
318 }
319 return;
320 default:
321 break;
322 }
323
324 switch (jo.sjo_type) {
325 case SQLITE3_TEXT:
326 sqlite3_result_text(context, jo.sjo_str.c_str(), jo.sjo_str.size(), SQLITE_TRANSIENT);
327 return;
328 case SQLITE_NULL:
329 sqlite3_result_null(context);
330 return;
331 case SQLITE_INTEGER:
332 sqlite3_result_int(context, jo.sjo_int);
333 return;
334 case SQLITE_FLOAT:
335 sqlite3_result_double(context, jo.sjo_float);
336 return;
337 }
338
339 string_fragment result = gen.to_string_fragment();
340
341 if (result.empty()) {
342 null_or_default(context, argc, argv);
343 return;
344 }
345
346 sqlite3_result_text(context, result.data(), result.length(), SQLITE_TRANSIENT);
347 #ifdef HAVE_SQLITE3_VALUE_SUBTYPE
348 sqlite3_result_subtype(context, JSON_SUBTYPE);
349 #endif
350 }
351
352 struct concat_context {
concat_contextconcat_context353 concat_context(yajl_gen gen_handle)
354 : cc_gen_handle(gen_handle) {
355 }
356
357 yajl_gen cc_gen_handle;
358 int cc_depth{0};
359 };
360
concat_gen_null(void * ctx)361 static int concat_gen_null(void *ctx)
362 {
363 auto cc = static_cast<concat_context *>(ctx);
364
365 if (cc->cc_depth > 0) {
366 return yajl_gen_null(cc->cc_gen_handle) == yajl_gen_status_ok;
367 }
368
369 return 1;
370 }
371
concat_gen_boolean(void * ctx,int val)372 static int concat_gen_boolean(void *ctx, int val)
373 {
374 auto cc = static_cast<concat_context *>(ctx);
375
376 return yajl_gen_bool(cc->cc_gen_handle, val) == yajl_gen_status_ok;
377 }
378
concat_gen_number(void * ctx,const char * val,size_t len)379 static int concat_gen_number(void *ctx, const char *val, size_t len)
380 {
381 auto cc = static_cast<concat_context *>(ctx);
382
383 return yajl_gen_number(cc->cc_gen_handle, val, len) == yajl_gen_status_ok;
384 }
385
concat_gen_string(void * ctx,const unsigned char * val,size_t len)386 static int concat_gen_string(void *ctx, const unsigned char *val, size_t len)
387 {
388 auto cc = static_cast<concat_context *>(ctx);
389
390 return yajl_gen_string(cc->cc_gen_handle, val, len) == yajl_gen_status_ok;
391 }
392
concat_gen_start_map(void * ctx)393 static int concat_gen_start_map(void *ctx)
394 {
395 auto cc = static_cast<concat_context *>(ctx);
396
397 cc->cc_depth += 1;
398 return yajl_gen_map_open(cc->cc_gen_handle) == yajl_gen_status_ok;
399 }
400
concat_gen_end_map(void * ctx)401 static int concat_gen_end_map(void *ctx)
402 {
403 auto cc = static_cast<concat_context *>(ctx);
404
405 cc->cc_depth -= 1;
406 return yajl_gen_map_close(cc->cc_gen_handle) == yajl_gen_status_ok;
407 }
408
concat_gen_map_key(void * ctx,const unsigned char * key,size_t len)409 static int concat_gen_map_key(void *ctx, const unsigned char *key, size_t len)
410 {
411 auto cc = static_cast<concat_context *>(ctx);
412
413 return yajl_gen_string(cc->cc_gen_handle, key, len) == yajl_gen_status_ok;
414 }
415
concat_gen_start_array(void * ctx)416 static int concat_gen_start_array(void *ctx)
417 {
418 auto cc = static_cast<concat_context *>(ctx);
419
420 cc->cc_depth += 1;
421 if (cc->cc_depth == 1) {
422 return 1;
423 }
424 return yajl_gen_array_open(cc->cc_gen_handle) == yajl_gen_status_ok;
425 }
426
concat_gen_end_array(void * ctx)427 static int concat_gen_end_array(void *ctx)
428 {
429 auto cc = static_cast<concat_context *>(ctx);
430
431 cc->cc_depth -= 1;
432 if (cc->cc_depth == 0) {
433 return 1;
434 }
435 return yajl_gen_array_close(cc->cc_gen_handle) == yajl_gen_status_ok;
436 }
437
concat_gen_elements(yajl_gen gen,const unsigned char * text,size_t len)438 static void concat_gen_elements(yajl_gen gen, const unsigned char *text, size_t len)
439 {
440 auto_mem<yajl_handle_t> handle(yajl_free);
441 yajl_callbacks callbacks = {nullptr};
442 concat_context cc{gen};
443
444 callbacks.yajl_null = concat_gen_null;
445 callbacks.yajl_boolean = concat_gen_boolean;
446 callbacks.yajl_number = concat_gen_number;
447 callbacks.yajl_string = concat_gen_string;
448 callbacks.yajl_start_map = concat_gen_start_map;
449 callbacks.yajl_end_map = concat_gen_end_map;
450 callbacks.yajl_map_key = concat_gen_map_key;
451 callbacks.yajl_start_array = concat_gen_start_array;
452 callbacks.yajl_end_array = concat_gen_end_array;
453
454 handle = yajl_alloc(&callbacks, nullptr, &cc);
455 yajl_config(handle, yajl_allow_comments, 1);
456 if (yajl_parse(handle, (const unsigned char *) text, len) != yajl_status_ok ||
457 yajl_complete_parse(handle) != yajl_status_ok) {
458 unique_ptr<unsigned char, decltype(&free)> err_msg(
459 yajl_get_error(handle, 1, (const unsigned char *) text, len), free);
460
461 throw sqlite_func_error("Invalid JSON: {}", (const char *) err_msg.get());
462 }
463 }
464
json_concat(nonstd::optional<const char * > json_in,const vector<sqlite3_value * > & values)465 static json_string json_concat(nonstd::optional<const char *> json_in, const vector<sqlite3_value *> &values)
466 {
467 yajlpp_gen gen;
468
469 yajl_gen_config(gen, yajl_gen_beautify, false);
470
471 {
472 yajlpp_array array(gen);
473
474 if (json_in) {
475 concat_gen_elements(gen,
476 (const unsigned char *) json_in.value(),
477 strlen(json_in.value()));
478 }
479
480 for (const auto val: values) {
481 switch (sqlite3_value_type(val)) {
482 case SQLITE_NULL:
483 array.gen();
484 break;
485 case SQLITE_INTEGER:
486 array.gen(sqlite3_value_int64(val));
487 break;
488 case SQLITE_FLOAT:
489 array.gen(sqlite3_value_double(val));
490 break;
491 case SQLITE3_TEXT: {
492 auto text_val = sqlite3_value_text(val);
493
494 if (sqlite3_value_subtype(val) == JSON_SUBTYPE) {
495 concat_gen_elements(gen,
496 text_val,
497 strlen((const char *) text_val));
498 } else {
499 array.gen((const char *) text_val);
500 }
501 break;
502 }
503 }
504 }
505 }
506
507 return json_string(gen);
508 }
509
510 struct json_agg_context {
511 yajl_gen_t *jac_yajl_gen;
512 };
513
sql_json_group_object_step(sqlite3_context * context,int argc,sqlite3_value ** argv)514 static void sql_json_group_object_step(sqlite3_context *context,
515 int argc,
516 sqlite3_value **argv)
517 {
518 if ((argc % 2) == 1) {
519 sqlite3_result_error(
520 context,
521 "Uneven number of arguments to json_group_object(), "
522 "expecting key and value pairs",
523 -1);
524 return;
525 }
526
527 json_agg_context *jac = (json_agg_context *) sqlite3_aggregate_context(
528 context, sizeof(json_agg_context));
529
530
531 if (jac->jac_yajl_gen == nullptr) {
532 jac->jac_yajl_gen = yajl_gen_alloc(nullptr);
533 yajl_gen_config(jac->jac_yajl_gen, yajl_gen_beautify, false);
534
535 yajl_gen_map_open(jac->jac_yajl_gen);
536 }
537
538 for (int lpc = 0; (lpc + 1) < argc; lpc += 2) {
539 log_debug("arg type %d", sqlite3_value_type(argv[lpc]));
540 if (sqlite3_value_type(argv[lpc]) == SQLITE_NULL) {
541 continue;
542 }
543
544 const unsigned char *key = sqlite3_value_text(argv[lpc]);
545
546 yajl_gen_string(jac->jac_yajl_gen, key, strlen((const char *) key));
547
548 switch (sqlite3_value_type(argv[lpc + 1])) {
549 case SQLITE_NULL:
550 yajl_gen_null(jac->jac_yajl_gen);
551 break;
552 case SQLITE3_TEXT: {
553 const unsigned char *value = sqlite3_value_text(argv[lpc + 1]);
554 #ifdef HAVE_SQLITE3_VALUE_SUBTYPE
555 int subtype = sqlite3_value_subtype(argv[lpc + 1]);
556
557 if (subtype == JSON_SUBTYPE) {
558 yajl_gen_number(jac->jac_yajl_gen,
559 (const char *) value,
560 strlen((const char *)value));
561 }
562 else {
563 #endif
564 yajl_gen_string(jac->jac_yajl_gen,
565 value,
566 strlen((const char *) value));
567 #ifdef HAVE_SQLITE3_VALUE_SUBTYPE
568 }
569 #endif
570 break;
571 }
572 case SQLITE_INTEGER: {
573 const unsigned char *value = sqlite3_value_text(argv[lpc + 1]);
574
575 yajl_gen_number(jac->jac_yajl_gen,
576 (const char *) value,
577 strlen((const char *) value));
578 break;
579 }
580 case SQLITE_FLOAT: {
581 double value = sqlite3_value_double(argv[lpc + 1]);
582
583 yajl_gen_double(jac->jac_yajl_gen, value);
584 break;
585 }
586 }
587 }
588
589 }
590
sql_json_group_object_final(sqlite3_context * context)591 static void sql_json_group_object_final(sqlite3_context *context)
592 {
593 json_agg_context *jac = (json_agg_context *) sqlite3_aggregate_context(
594 context, 0);
595
596 if (jac == NULL) {
597 sqlite3_result_text(context, "{}", -1, SQLITE_STATIC);
598 }
599 else {
600 const unsigned char *buf;
601 size_t len;
602
603 yajl_gen_map_close(jac->jac_yajl_gen);
604 yajl_gen_get_buf(jac->jac_yajl_gen, &buf, &len);
605 sqlite3_result_text(context, (const char *) buf, len, SQLITE_TRANSIENT);
606 #ifdef HAVE_SQLITE3_VALUE_SUBTYPE
607 sqlite3_result_subtype(context, JSON_SUBTYPE);
608 #endif
609 yajl_gen_free(jac->jac_yajl_gen);
610 }
611 }
612
sql_json_group_array_step(sqlite3_context * context,int argc,sqlite3_value ** argv)613 static void sql_json_group_array_step(sqlite3_context *context,
614 int argc,
615 sqlite3_value **argv)
616 {
617 json_agg_context *jac = (json_agg_context *) sqlite3_aggregate_context(
618 context, sizeof(json_agg_context));
619
620 if (jac->jac_yajl_gen == NULL) {
621 jac->jac_yajl_gen = yajl_gen_alloc(NULL);
622 yajl_gen_config(jac->jac_yajl_gen, yajl_gen_beautify, false);
623
624 yajl_gen_array_open(jac->jac_yajl_gen);
625 }
626
627 for (int lpc = 0; lpc < argc; lpc++) {
628 switch (sqlite3_value_type(argv[lpc])) {
629 case SQLITE_NULL:
630 yajl_gen_null(jac->jac_yajl_gen);
631 break;
632 case SQLITE3_TEXT: {
633 const unsigned char *value = sqlite3_value_text(argv[lpc]);
634 #ifdef HAVE_SQLITE3_VALUE_SUBTYPE
635 int subtype = sqlite3_value_subtype(argv[lpc]);
636
637 if (subtype == JSON_SUBTYPE) {
638 yajl_gen_number(jac->jac_yajl_gen,
639 (const char *) value,
640 strlen((const char *)value));
641 }
642 else {
643 #endif
644 yajl_gen_string(jac->jac_yajl_gen,
645 value,
646 strlen((const char *) value));
647 #ifdef HAVE_SQLITE3_VALUE_SUBTYPE
648 }
649 #endif
650 break;
651 }
652 case SQLITE_INTEGER: {
653 const unsigned char *value = sqlite3_value_text(argv[lpc]);
654
655 yajl_gen_number(jac->jac_yajl_gen,
656 (const char *) value,
657 strlen((const char *) value));
658 break;
659 }
660 case SQLITE_FLOAT: {
661 double value = sqlite3_value_double(argv[lpc]);
662
663 yajl_gen_double(jac->jac_yajl_gen, value);
664 break;
665 }
666 }
667 }
668
669 }
670
sql_json_group_array_final(sqlite3_context * context)671 static void sql_json_group_array_final(sqlite3_context *context)
672 {
673 json_agg_context *jac = (json_agg_context *) sqlite3_aggregate_context(
674 context, 0);
675
676 if (jac == NULL) {
677 sqlite3_result_text(context, "{}", -1, SQLITE_STATIC);
678 }
679 else {
680 const unsigned char *buf;
681 size_t len;
682
683 yajl_gen_array_close(jac->jac_yajl_gen);
684 yajl_gen_get_buf(jac->jac_yajl_gen, &buf, &len);
685 sqlite3_result_text(context, (const char *) buf, len, SQLITE_TRANSIENT);
686 #ifdef HAVE_SQLITE3_VALUE_SUBTYPE
687 sqlite3_result_subtype(context, JSON_SUBTYPE);
688 #endif
689 yajl_gen_free(jac->jac_yajl_gen);
690 }
691 }
692
json_extension_functions(struct FuncDef ** basic_funcs,struct FuncDefAgg ** agg_funcs)693 int json_extension_functions(struct FuncDef **basic_funcs,
694 struct FuncDefAgg **agg_funcs)
695 {
696 static struct FuncDef json_funcs[] = {
697 sqlite_func_adapter<decltype(&json_concat), json_concat>::builder(
698 help_text("json_concat",
699 "Returns an array with the given values concatenated onto the end. "
700 "If the initial value is null, the result will be an array with "
701 "the given elements. If the initial value is an array, the result "
702 "will be an array with the given values at the end. If the initial "
703 "value is not null or an array, the result will be an array with "
704 "two elements: the initial value and the given value.")
705 .sql_function()
706 .with_parameter({"json", "The initial JSON value."})
707 .with_parameter(help_text("value", "The value(s) to add to the end of the array.")
708 .one_or_more())
709 .with_tags({"json"})
710 .with_example({
711 "To append the number 4 to null",
712 "SELECT json_concat(NULL, 4)"
713 })
714 .with_example({
715 "To append 4 and 5 to the array [1, 2, 3]",
716 "SELECT json_concat('[1, 2, 3]', 4, 5)"
717 })
718 .with_example({
719 "To concatenate two arrays together",
720 "SELECT json_concat('[1, 2, 3]', json('[4, 5]'))"
721 })
722 ),
723
724 sqlite_func_adapter<decltype(&json_contains), json_contains>::builder(
725 help_text("json_contains", "Check if a JSON value contains the given element.")
726 .sql_function()
727 .with_parameter({"json", "The JSON value to query."})
728 .with_parameter({"value", "The value to look for in the first argument"})
729 .with_tags({"json"})
730 .with_example({
731 "To test if a JSON array contains the number 4",
732 "SELECT json_contains('[1, 2, 3]', 4)"
733 })
734 .with_example({
735 "To test if a JSON array contains the string 'def'",
736 "SELECT json_contains('[\"abc\", \"def\"]', 'def')"
737 })
738 ),
739
740 {
741 "jget", -1, SQLITE_UTF8, 0, sql_jget,
742 help_text("jget",
743 "Get the value from a JSON object using a JSON-Pointer.")
744 .sql_function()
745 .with_parameter({"json", "The JSON object to query."})
746 .with_parameter({"ptr", "The JSON-Pointer to lookup in the object."})
747 .with_parameter(help_text("default", "The default value if the value was not found")
748 .optional())
749 .with_tags({"json"})
750 .with_example({
751 "To get the root of a JSON value",
752 "SELECT jget('1', '')"
753 })
754 .with_example({
755 "To get the property named 'b' in a JSON object",
756 "SELECT jget('{ \"a\": 1, \"b\": 2 }', '/b')"
757 })
758 .with_example({
759 "To get the 'msg' property and return a default if it does not exist",
760 "SELECT jget(null, '/msg', 'Hello')"
761 })
762 },
763
764 { nullptr }
765 };
766
767 static struct FuncDefAgg json_agg_funcs[] = {
768 { "json_group_object", -1, 0,
769 sql_json_group_object_step, sql_json_group_object_final,
770 help_text("json_group_object")
771 .sql_function()
772 .with_summary("Collect the given values from a query into a JSON object")
773 .with_parameter(help_text("name", "The property name for the value"))
774 .with_parameter(help_text("value", "The value to add to the object")
775 .one_or_more())
776 .with_tags({"json"})
777 .with_example({
778 "To create an object from arguments",
779 "SELECT json_group_object('a', 1, 'b', 2)"
780 })
781 .with_example({
782 "To create an object from a pair of columns",
783 "SELECT json_group_object(column1, column2) FROM (VALUES ('a', 1), ('b', 2))"
784 })},
785 { "json_group_array", -1, 0,
786 sql_json_group_array_step, sql_json_group_array_final,
787 help_text("json_group_array")
788 .sql_function()
789 .with_summary("Collect the given values from a query into a JSON array")
790 .with_parameter(help_text("value", "The values to append to the array")
791 .one_or_more())
792 .with_tags({"json"})
793 .with_example({
794 "To create an array from arguments",
795 "SELECT json_group_array('one', 2, 3.4)"
796 })
797 .with_example({
798 "To create an array from a column of values",
799 "SELECT json_group_array(column1) FROM (VALUES (1), (2), (3))"
800 })},
801
802 { nullptr }
803 };
804
805 *basic_funcs = json_funcs;
806 *agg_funcs = json_agg_funcs;
807
808 return SQLITE_OK;
809 }
810