Changeset: 731be97d7c41 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/731be97d7c41
Added Files:
sql/test/nested/Tests/bluesky.sql
sql/test/nested/Tests/bluesky_nd.json
Modified Files:
sql/backends/monet5/sql.c
sql/test/nested/Tests/bluesky.test.in
Branch: nested
Log Message:
wip making bluesky queries work
diffs (231 lines):
diff --git a/sql/backends/monet5/sql.c b/sql/backends/monet5/sql.c
--- a/sql/backends/monet5/sql.c
+++ b/sql/backends/monet5/sql.c
@@ -5746,10 +5746,10 @@ jsonv2local(const ValPtr t, char *v)
// TODO add remaining types
switch (t->vtype) {
case TYPE_int: /* todo handle extra double quotes */
- t->val.ival = strtol(v, NULL, 10);
+ t->val.ival = atoi(v);
break;
case TYPE_lng:
- t->val.lval = strtol(v, NULL, 10);
+ t->val.lval = (lng) strtol(v, NULL, 10);
break;
case TYPE_flt:
t->val.fval = (flt) strtod(v, NULL);
@@ -5758,7 +5758,7 @@ jsonv2local(const ValPtr t, char *v)
t->val.dval = strtod(v, NULL);
break;
case TYPE_str:
- t->val.sval = v;
+ t->val.sval = _STRDUP(v);
break;
case TYPE_timestamp:
sql_timestamp_fromstr(v, &t->val.lval, 0, 0);
@@ -5769,10 +5769,44 @@ jsonv2local(const ValPtr t, char *v)
return t;
}
+static str
+insert_json_value(JSONterm *jt, sql_subtype *t, BAT *b, const char *kname,
size_t klen)
+{
+ char *msg = MAL_SUCCEED;
+ size_t vsize = jt->valuelen;
+ char *val = (char *)jt->value;
+
+ ValPtr v = NULL;
+ ValRecord vr = (ValRecord) {.bat=false, .vtype=TYPE_void};
+ for(node *n = t->type->d.fields->h; n; n = n->next) {
+ sql_arg *a = n->data;
+ size_t alen = strlen(a->name);
+ if (klen == alen && strncmp(kname, a->name, klen) == 0) {
+ vr.vtype = a->type.type->localtype;
+ if (vr.vtype == ATOMindex("json")) // if json
+ vr.vtype = TYPE_str;
+ char eos = val[vsize];
+ val[vsize] = '\0';
+ v = jsonv2local(&vr, val);
+ val[vsize] = eos;
+ if (v == NULL)
+ msg = createException(SQL,
"sql.insert_json_value", "jsonv2local failed");
+ break;
+ }
+ }
+ if (v) {
+ if (BUNappend(b, VALget(v), false) != GDK_SUCCEED)
+ msg = createException(SQL, "sql.insert_json_value",
"BUNappend failed");
+ } else {
+ msg = (msg == MAL_SUCCEED) ? createException(SQL,
"sql.insert_json_value", "missing field") : msg;
+ }
+ return msg;
+}
+
static int
insert_json_object(char **msg, JSON *js, BAT **bats, int *BO, int nr, int elm,
sql_subtype *t)
{
- char buf[128]; /* TODO use proper buffer */
+ // char buf[128]; /* TODO use proper buffer */
int bat_offset = *BO;
node *n;
JSONterm *ja = js->elm+elm;
@@ -5781,7 +5815,7 @@ insert_json_object(char **msg, JSON *js,
return -1;
}
const char *name = NULL;
- int nlen = 0, pos = -1, w = list_length(t->type->d.fields), i = 0;
+ int nlen = 0;
/* TODO check if full object is there */
for (elm++; elm > 0 && elm <= ja->tail+1; elm++) {
JSONterm *jt = js->elm+elm;
@@ -5802,7 +5836,15 @@ insert_json_object(char **msg, JSON *js,
}
}
assert(nt && !nt->multiset);
- elm = insert_json_object(msg, js, bats,
&bat_offset, nr, elm, nt);
+ if (nt->type->composite)
+ elm = insert_json_object(msg, js, bats,
&bat_offset, nr, elm, nt);
+ else {
+ // json string value
+ insert_json_value(jt, t,
bats[bat_offset], name, nlen);
+ // set term offset
+ elm = ((jt - 1)->next) - 1; // ? is
this right
+ bat_offset ++;
+ }
} else {
assert(0);
}
@@ -5833,38 +5875,39 @@ insert_json_object(char **msg, JSON *js,
case JSON_VALUE:
break;
case JSON_STRING:
-
- jt->value ++;
- jt->valuelen --;
- jt->valuelen --;
+ //jt->value ++;
+ //jt->valuelen --;
+ //jt->valuelen --;
/* fall through */
case JSON_NUMBER:
case JSON_BOOL:
case JSON_NULL:
- pos = -1;
- ValPtr v = NULL;
- ValRecord vr = (ValRecord) {.bat=false,
.vtype=TYPE_void};
- if (jt->valuelen > 128-1)
- return -8;
- strncpy(buf, jt->value, jt->valuelen);
- buf[jt->valuelen] = 0;
- for(i = 0, n = t->type->d.fields->h; i < w && n && pos
< 0; i++, n = n->next) {
- sql_arg *a = n->data;
- int alen = (int)strlen(a->name);
- if (nlen == alen && strncmp(name, a->name,
nlen) == 0) {
- pos = i;
- vr.vtype = a->type.type->localtype;
- v = jsonv2local(&vr, buf);
- break;
- }
- }
- if (pos < 0 || v == NULL) {
- *msg = "field name missing";
- return -8;
- }
- if (elm > 0 && BUNappend(bats[bat_offset], VALget(v),
false) != GDK_SUCCEED) {
- return -5;
- }
+ //pos = -1;
+ //ValPtr v = NULL;
+ //ValRecord vr = (ValRecord) {.bat=false,
.vtype=TYPE_void};
+ //if (jt->valuelen > 128-1)
+ // return -8;
+ //strncpy(buf, jt->value, jt->valuelen);
+ //buf[jt->valuelen] = 0;
+ //for(i = 0, n = t->type->d.fields->h; i < w && n &&
pos < 0; i++, n = n->next) {
+ // sql_arg *a = n->data;
+ // int alen = (int)strlen(a->name);
+ // if (nlen == alen && strncmp(name, a->name,
nlen) == 0) {
+ // pos = i;
+ // vr.vtype = a->type.type->localtype;
+ // v = jsonv2local(&vr, buf);
+ // break;
+ // }
+ //}
+ //if (pos < 0 || v == NULL) {
+ // *msg = "field name missing";
+ // return -8;
+ //}
+ //if (elm > 0 && BUNappend(bats[bat_offset], VALget(v),
false) != GDK_SUCCEED) {
+ // return -5;
+ //}
+ if ((*msg = insert_json_value(jt, t, bats[bat_offset],
name, nlen)) != MAL_SUCCEED)
+ return -1;
bat_offset ++;
}
}
diff --git a/sql/test/nested/Tests/bluesky.sql
b/sql/test/nested/Tests/bluesky.sql
new file mode 100644
--- /dev/null
+++ b/sql/test/nested/Tests/bluesky.sql
@@ -0,0 +1,27 @@
+--create type "subject" as ("cid" varchar, "uri" varchar);
+--
+--create type "commit" as ("rev" varchar, "operation" varchar, "collection"
varchar, "rkey" varchar, "record" json, "cid" varchar);
+--
+--create type event as ("did" varchar, "time_us" int, "kind" varchar, "commit"
"commit");
+--
+--create table bluesky (e event);
+
+-- this works
+--insert into bluesky select cast(t.json as event) from (select json from
read_nd_json(r'/tmp/bluesky_nd.json')) t;
+
+-- column mapping lost
+-- select e."commit"."collection" from bluesky;
+
+-- this works
+--select e."commit"."collection" as ev from (select cast(t.json as event) as e
from (select json from read_nd_json(r'/tmp/bluesky_nd.json')) t);
+
+-- eats up first selection, only cnt column
+--select e."commit"."collection" as ev, count(*) as cnt from (select
cast(t.json as event) as e from (select json from
read_nd_json(r'/tmp/bluesky_nd.json')) t) group by ev order by cnt desc;
+
+
+-- column mapping lost
+--SELECT e."commit"."collection" AS ev, count(*) AS cnt, count(distinct
e."did") AS users FROM bluesky WHERE e."kind" = 'commit' AND
e."commit"."operation" = 'create' GROUP BY ev ORDER BY cnt DESC;
+
+-- this crash server
+-- SELECT e."commit"."collection" AS ev, count(*) AS cnt, count(distinct
e."did") AS users FROM (select cast(t.json as event) as e from (select json
from read_nd_json(r'/tmp/bluesky_nd.json')) t) WHERE e."kind" = 'commit' AND
e."commit"."operation" = 'create' GROUP BY ev ORDER BY cnt DESC;
+
diff --git a/sql/test/nested/Tests/bluesky.test.in
b/sql/test/nested/Tests/bluesky.test.in
--- a/sql/test/nested/Tests/bluesky.test.in
+++ b/sql/test/nested/Tests/bluesky.test.in
@@ -14,12 +14,12 @@ statement ok
create table bluesky ("event" event)
query I
-select count(*) from read_nd_json(r'$TSTSRCDIR/bluesky.json')
+select count(*) from read_nd_json(r'$TSTSRCDIR/bluesky_nd.json')
----
-5
+10
statement ok
-select cast(t.json as event) as e from (select json from
read_nd_json(r'$TSTSRCDIR/bluesky.json')) t
+select cast(t.json as event) as e from (select json from
read_nd_json(r'$TSTSRCDIR/bluesky_nd.json')) t
statement ok
drop table bluesky
diff --git a/sql/test/nested/Tests/bluesky_nd.json
b/sql/test/nested/Tests/bluesky_nd.json
new file mode 100644
--- /dev/null
+++ b/sql/test/nested/Tests/bluesky_nd.json
@@ -0,0 +1,10 @@
+{"did":"did:plc:yj3sjq3blzpynh27cumnp5ks","time_us":1732206349000167,"kind":"commit","commit":{"rev":"3lbhtytnn2k2f","operation":"create","collection":"app.bsky.feed.post","rkey":"3lbhtyteurk2y","record":{"$type":"app.bsky.feed.post","createdAt":"2024-11-21T16:09:27.095Z","langs":["en"],"reply":{"parent":{"cid":"bafyreibfglofvqou2yiqvwzk4rcgkhhxrbunyemshdjledgwymimqkg24e","uri":"at://did:plc:6tr6tuzlx2db3rduzr2d6r24/app.bsky.feed.post/3lbhqo2rtys2z"},"root":{"cid":"bafyreibfglofvqou2yiqvwzk4rcgkhhxrbunyemshdjledgwymimqkg24e","uri":"at://did:plc:6tr6tuzlx2db3rduzr2d6r24/app.bsky.feed.post/3lbhqo2rtys2z"}},"text":"aaaaah.
LIght shines in a corner of
WTF...."},"cid":"bafyreidblutgvj75o4q4akzyyejedjj6l3it6hgqwee6jpwv2wqph5fsgm"}}
+{"did":"did:plc:3i4xf2v4wcnyktgv6satke64","time_us":1732206349000644,"kind":"commit","commit":{"rev":"3lbhuvzds6d2a","operation":"create","collection":"app.bsky.feed.like","rkey":"3lbhuvzdked2a","record":{"$type":"app.bsky.feed.like","createdAt":"2024-11-21T16:25:46.221Z","subject":{"cid":"bafyreidjvrcmckkm765mct5fph36x7kupkfo35rjklbf2k76xkzwyiauge","uri":"at://did:plc:azrv4rcbws6kmcga4fsbphg2/app.bsky.feed.post/3lbgjdpbiec2l"}},"cid":"bafyreia5l5vrkh5oj4cjyhcqby2dprhyvcyofo2q5562tijlae2pzih23m"}}
+{"did":"did:plc:gccfnqqizz4urhchsaie6jft","time_us":1732206349001108,"kind":"commit","commit":{"rev":"3lbhuvze3gi2u","operation":"create","collection":"app.bsky.graph.follow","rkey":"3lbhuvzdtmi2u","record":{"$type":"app.bsky.graph.follow","createdAt":"2024-11-21T16:27:40.923Z","subject":"did:plc:r7cdh4sgzqbfdc6wcdxxti7c"},"cid":"bafyreiew2p6cgirfaj45qoenm4fgumib7xoloclrap3jgkz5es7g7kby3i"}}
+{"did":"did:plc:msxqf3twq7abtdw7dbfskphk","time_us":1732206349001372,"kind":"commit","commit":{"rev":"3lbhueija5p22","operation":"create","collection":"app.bsky.feed.like","rkey":"3lbhueiizcx22","record":{"$type":"app.bsky.feed.like","createdAt":"2024-11-21T16:15:58.232Z","subject":{"cid":"bafyreiavpshyqzrlo5m7fqodjhs6jevweqnif4phasiwimv4a7mnsqi2fe","uri":"at://did:plc:fusulxqc52zbrc75fi6xrcof/app.bsky.feed.post/3lbhskq5zn22f"}},"cid":"bafyreidjix4dauj2afjlbzmhj3a7gwftcevvmmy6edww6vrjdbst26rkby"}}
+{"did":"did:plc:l5o3qjrmfztir54cpwlv2eme","time_us":1732206349001905,"kind":"commit","commit":{"rev":"3lbhtytohxc2o","operation":"create","collection":"app.bsky.feed.post","rkey":"3lbhtytjqzk2q","record":{"$type":"app.bsky.feed.post","createdAt":"2024-11-21T16:09:27.254Z","langs":["en"],"reply":{"parent":{"cid":"bafyreih35fe2jj3gchmgk4amold4l6sfxd2sby5wrg3jrws5fkdypxrbg4","uri":"at://did:plc:6wx2gg5yqgvmlu35r6y3bk6d/app.bsky.feed.post/3lbhtj2eb4s2o"},"root":{"cid":"bafyreifipyt3vctd4ptuoicvio7rbr5xvjv4afwuggnd2prnmn55mu6luu","uri":"at://did:plc:474ldquxwzrlcvjhhbbk2wte/app.bsky.feed.post/3lbhdzrynik27"}},"text":"okay
i take mine back because I hadn’t heard this one
yet^^"},"cid":"bafyreigzdsdne3z2xxcakgisieyj7y47hj6eg7lj6v4q25ah5q2qotu5ku"}}
+{"did":"did:plc:jkaaf5j2yb2pvpx3ualm3vbh","time_us":1732206349002758,"kind":"commit","commit":{"rev":"3lbhudfo3yi2w","operation":"create","collection":"app.bsky.graph.follow","rkey":"3lbhudfnw4y2w","record":{"$type":"app.bsky.graph.follow","createdAt":"2024-11-21T16:15:21.495Z","subject":"did:plc:amsdn2tbjxo3xrwqneqhh4cm"},"cid":"bafyreiaa2vsdr4ckwjg4jq47zfd7mewidywfz3qh3dmglcd6ozi4xwdega"}}
+{"did":"did:plc:tdwz2h4id5dxezvohftsmffu","time_us":1732206349003106,"kind":"commit","commit":{"rev":"3lbhujcp4ix2n","operation":"create","collection":"app.bsky.graph.follow","rkey":"3lbhujcoxmp2n","record":{"$type":"app.bsky.graph.follow","createdAt":"2024-11-21T16:18:39.913Z","subject":"did:plc:gf3vum7insztt5rxrpxdz2id"},"cid":"bafyreihaatlpar3abtx6ck3kde2ksic6zzflk4ppduhf6dxurytqrv33ni"}}
+{"did":"did:plc:cdsd346mwow7aj3tgfkwsct3","time_us":1732206349003461,"kind":"commit","commit":{"rev":"3lbhus5vior2t","operation":"create","collection":"app.bsky.feed.repost","rkey":"3lbhus5vbtz2t","record":{"$type":"app.bsky.feed.repost","createdAt":"2024-11-21T16:23:36.714Z","subject":{"cid":"bafyreieaacfiobnuqvjhhsndyi5s3fd6krbzdduxsyrzfv43kczpcmkl6y","uri":"at://did:plc:o5q6dynpme4ndolc3heztasm/app.bsky.feed.post/3lbfli3qsoc2o"}},"cid":"bafyreid5ycocp5zq2g7fcx2xxzxrbafuh7b5qhtwuwiomzo6vqila2cbpu"}}
+{"did":"did:plc:s4bwqchfzm6gjqfeb6mexgbu","time_us":1732206349003907,"kind":"commit","commit":{"rev":"3lbhuvzeccx2w","operation":"create","collection":"app.bsky.feed.post","rkey":"3lbhuvxf4qs2m","record":{"$type":"app.bsky.feed.post","createdAt":"2024-11-21T16:25:44.376Z","langs":["en"],"reply":{"parent":{"cid":"bafyreiaev27cfcxxvn2pdhrwwquzwgclujnulzbcfnn4p4fwgb6migjhw4","uri":"at://did:plc:zec6cslvgc3hhdatrhk6pq5p/app.bsky.feed.post/3lbhujvds4c2b"},"root":{"cid":"bafyreif7qjxhvecwnhlynijj6pf47jwvtkahsz3zh2kaipwu2bw2dxwaqq","uri":"at://did:plc:s4bwqchfzm6gjqfeb6mexgbu/app.bsky.feed.post/3lbhug53kkk2m"}},"text":"ㅤ\nㅤㅤ⌜
Blinking. She hadn't realized she spoke out loud. ⌟\n\nㅤ‘ It was nothing
like that — . I was only thinking . . . ’\n\nㅤㅤ⌜ Trailing off, her mind
occupied.
⌟\nㅤ"},"cid":"bafyreibugobcike72y4zxvdyz2oopyt6ywwqfielcwojkb27p7s6rlomgm"}}
+{"did":"did:plc:hbc74dlsxhq53kp5oxges6d7","time_us":1732206349004769,"kind":"commit","commit":{"rev":"3lbhuvzedg52j","operation":"create","collection":"app.bsky.feed.like","rkey":"3lbhuvzdyof2j","record":{"$type":"app.bsky.feed.like","createdAt":"2024-11-21T16:25:46.167Z","subject":{"cid":"bafyreiaumopip75nzx2xjbugtwemdppsyx54bd2odf6q45f3o7xkocgari","uri":"at://did:plc:ig2jv6gqup4t7gdq2pmanknw/app.bsky.feed.post/3lbhuvtlaec2c"}},"cid":"bafyreidjk2svg2fdjiiwohmfmvp3hdxhpb33ycnixzbkyib5m6cocindxq"}}
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
