diff options
21 files changed, 4226 insertions, 425 deletions
diff --git a/storage/connect/CMakeLists.txt b/storage/connect/CMakeLists.txt index 7eedba08bee..e8ffeebafcc 100644 --- a/storage/connect/CMakeLists.txt +++ b/storage/connect/CMakeLists.txt @@ -80,6 +80,19 @@ ELSE(NOT UNIX) ENDIF() ENDIF(UNIX) +# +# BSON: this the new version of JSON that is temporarily included here for testing +# When fully tested, it will replace the old support (and be renamed to JSON) +# + +OPTION(CONNECT_WITH_BSON "Compile CONNECT storage engine with BSON support" ON) + +IF(CONNECT_WITH_BSON) + SET(CONNECT_SOURCES ${CONNECT_SOURCES} + bson.cpp tabbson.cpp bsonudf.cpp bson.h tabbson.h bsonudf.h) + add_definitions(-DBSON_SUPPORT) +ENDIF(CONNECT_WITH_BSON) + # # VCT: the VEC format might be not supported in future versions diff --git a/storage/connect/bson.cpp b/storage/connect/bson.cpp index 2588657089f..f3ad919993f 100644 --- a/storage/connect/bson.cpp +++ b/storage/connect/bson.cpp @@ -544,9 +544,9 @@ fin: buf[n] = 0; if (has_dot || has_e) { - double dv = strtod(buf, NULL); + double dv = atof(buf); - if (nd > 5 || dv > FLT_MAX || dv < FLT_MIN) { + if (nd >= 6 || dv > FLT_MAX || dv < FLT_MIN) { double* dvp = (double*)PlugSubAlloc(G, NULL, sizeof(double)); *dvp = dv; @@ -557,7 +557,7 @@ fin: vlp->Type = TYPE_FLOAT; } // endif nd - vlp->Nd = nd; + vlp->Nd = MY_MIN(nd, 16); } else { longlong iv = strtoll(buf, NULL, 10); @@ -765,6 +765,8 @@ bool BDOC::SerializeValue(PBVAL jvp) return jp->WriteStr(buf); case TYPE_NULL: return jp->WriteStr("null"); + case TYPE_JVAL: + return SerializeValue(MVP(jvp->To_Val)); default: return jp->WriteStr("???"); // TODO } // endswitch Type @@ -793,7 +795,12 @@ void* BJSON::BsonSubAlloc(size_t size) "Not enough memory for request of %zd (used=%zd free=%zd)", size, pph->To_Free, pph->FreeBlk); xtrc(1, "BsonSubAlloc: %s\n", G->Message); - throw(1234); + + if (Throw) + throw(1234); + else + return NULL; + } /* endif size OS32 code */ // Do the suballocation the simplest way @@ -1066,7 +1073,7 @@ PBVAL BJSON::MergeObject(PBVAL bop1, PBVAL bop2) /***********************************************************************/ /* Delete a value corresponding to the given key. */ /***********************************************************************/ -void BJSON::DeleteKey(PBVAL bop, PCSZ key) +bool BJSON::DeleteKey(PBVAL bop, PCSZ key) { CheckType(bop, TYPE_JOB); PBPR brp, pbrp = NULL; @@ -1079,10 +1086,11 @@ void BJSON::DeleteKey(PBVAL bop, PCSZ key) bop->To_Val = brp->Vlp.Next; bop->Nd--; - break; + return true;; } else pbrp = brp; + return false; } // end of DeleteKey /***********************************************************************/ @@ -1247,24 +1255,25 @@ PSZ BJSON::GetArrayText(PGLOBAL g, PBVAL bap, PSTRG text) /***********************************************************************/ /* Delete a Value from the Arrays Value list. */ /***********************************************************************/ -void BJSON::DeleteValue(PBVAL bap, int n) +bool BJSON::DeleteValue(PBVAL bap, int n) { CheckType(bap, TYPE_JAR); int i = 0; PBVAL bvp, pvp = NULL; - for (bvp = GetArray(bap); bvp; i++, bvp = GetNext(bvp)) - if (i == n) { - if (pvp) - pvp->Next = bvp->Next; - else - bap->To_Val = bvp->Next; + for (bvp = GetArray(bap); bvp; i++, bvp = GetNext(bvp)) + if (i == n) { + if (pvp) + pvp->Next = bvp->Next; + else + bap->To_Val = bvp->Next; - bap->Nd--; - break; - } else - pvp = bvp; + bap->Nd--; + return true;; + } else + pvp = bvp; + return false; } // end of DeleteValue /***********************************************************************/ @@ -1510,7 +1519,7 @@ double BJSON::GetDouble(PBVAL vp) } // endswitch Type return d; -} // end of GetFloat +} // end of GetDouble /***********************************************************************/ /* Return the Value's String value. */ @@ -1603,61 +1612,64 @@ PBVAL BJSON::SetValue(PBVAL vlp, PVAL valp) if (!valp || valp->IsNull()) { vlp->Type = TYPE_NULL; } else switch (valp->GetType()) { - case TYPE_DATE: - if (((DTVAL*)valp)->IsFormatted()) - vlp->To_Val = DupStr(valp->GetCharValue()); - else { - char buf[32]; + case TYPE_DATE: + if (((DTVAL*)valp)->IsFormatted()) + vlp->To_Val = DupStr(valp->GetCharValue()); + else { + char buf[32]; - vlp->To_Val = DupStr(valp->GetCharString(buf)); - } // endif Formatted + vlp->To_Val = DupStr(valp->GetCharString(buf)); + } // endif Formatted - vlp->Type = TYPE_DTM; - break; - case TYPE_STRING: - vlp->To_Val = DupStr(valp->GetCharValue()); - vlp->Type = TYPE_STRG; - break; - case TYPE_DOUBLE: - case TYPE_DECIM: - vlp->Nd = (IsTypeNum(valp->GetType())) ? valp->GetValPrec() : 0; + vlp->Type = TYPE_DTM; + break; + case TYPE_STRING: + vlp->To_Val = DupStr(valp->GetCharValue()); + vlp->Type = TYPE_STRG; + break; + case TYPE_DOUBLE: + case TYPE_DECIM: + { double d = valp->GetFloatValue(); + int nd = (IsTypeNum(valp->GetType())) ? valp->GetValPrec() : 0; - if (vlp->Nd <= 6) { - vlp->F = (float)valp->GetFloatValue(); - vlp->Type = TYPE_FLOAT; - } else { - double *dp = (double*)BsonSubAlloc(sizeof(double)); + if (nd <= 6 && d >= FLT_MIN && d <= FLT_MAX) { + vlp->F = (float)valp->GetFloatValue(); + vlp->Type = TYPE_FLOAT; + } else { + double* dp = (double*)BsonSubAlloc(sizeof(double)); - *dp = valp->GetFloatValue(); - vlp->To_Val = MOF(dp); - vlp->Type = TYPE_DBL; - } // endif Nd + *dp = d; + vlp->To_Val = MOF(dp); + vlp->Type = TYPE_DBL; + } // endif Nd - break; - case TYPE_TINY: - vlp->B = valp->GetTinyValue() != 0; - vlp->Type = TYPE_BOOL; - case TYPE_INT: - vlp->N = valp->GetIntValue(); - vlp->Type = TYPE_INTG; - break; - case TYPE_BIGINT: - if (valp->GetBigintValue() >= INT_MIN32 && - valp->GetBigintValue() <= INT_MAX32) { + vlp->Nd = MY_MIN(nd, 16); + } break; + case TYPE_TINY: + vlp->B = valp->GetTinyValue() != 0; + vlp->Type = TYPE_BOOL; + break; + case TYPE_INT: vlp->N = valp->GetIntValue(); vlp->Type = TYPE_INTG; - } else { - longlong* llp = (longlong*)BsonSubAlloc(sizeof(longlong)); + break; + case TYPE_BIGINT: + if (valp->GetBigintValue() >= INT_MIN32 && + valp->GetBigintValue() <= INT_MAX32) { + vlp->N = valp->GetIntValue(); + vlp->Type = TYPE_INTG; + } else { + longlong* llp = (longlong*)BsonSubAlloc(sizeof(longlong)); - *llp = valp->GetBigintValue(); - vlp->To_Val = MOF(llp); - vlp->Type = TYPE_BINT; - } // endif BigintValue + *llp = valp->GetBigintValue(); + vlp->To_Val = MOF(llp); + vlp->Type = TYPE_BINT; + } // endif BigintValue - break; - default: - sprintf(G->Message, "Unsupported typ %d\n", valp->GetType()); - throw(777); + break; + default: + sprintf(G->Message, "Unsupported typ %d\n", valp->GetType()); + throw(777); } // endswitch Type return vlp; @@ -1702,16 +1714,44 @@ void BJSON::SetBigint(PBVAL vlp, longlong ll) /***********************************************************************/ /* Set the Value's value as the given DOUBLE. */ /***********************************************************************/ -void BJSON::SetFloat(PBVAL vlp, double f) { - vlp->F = (float)f; - vlp->Nd = 6; - vlp->Type = TYPE_FLOAT; +void BJSON::SetFloat(PBVAL vlp, double d, int nd) +{ + double* dp = (double*)BsonSubAlloc(sizeof(double)); + + *dp = d; + vlp->To_Val = MOF(dp); + vlp->Nd = MY_MIN(nd, 16); + vlp->Type = TYPE_DBL; } // end of SetFloat /***********************************************************************/ +/* Set the Value's value as the given DOUBLE representation. */ +/***********************************************************************/ +void BJSON::SetFloat(PBVAL vlp, PSZ s) +{ + char *p = strchr(s, '.'); + int nd = 0; + double d = atof(s); + + if (p) { + for (++p; isdigit(*p); nd++, p++); + for (--p; *p == '0'; nd--, p--); + } // endif p + + if (nd < 6 && d >= FLT_MIN && d <= FLT_MAX) { + vlp->F = (float)d; + vlp->Nd = nd; + vlp->Type = TYPE_FLOAT; + } else + SetFloat(vlp, d, nd); + +} // end of SetFloat + + /***********************************************************************/ /* Set the Value's value as the given string. */ /***********************************************************************/ -void BJSON::SetString(PBVAL vlp, PSZ s, int ci) { +void BJSON::SetString(PBVAL vlp, PSZ s, int ci) +{ vlp->To_Val = MOF(s); vlp->Nd = ci; vlp->Type = TYPE_STRG; @@ -1720,7 +1760,8 @@ void BJSON::SetString(PBVAL vlp, PSZ s, int ci) { /***********************************************************************/ /* True when its JSON or normal value is null. */ /***********************************************************************/ -bool BJSON::IsValueNull(PBVAL vlp) { +bool BJSON::IsValueNull(PBVAL vlp) +{ bool b; switch (vlp->Type) { diff --git a/storage/connect/bson.h b/storage/connect/bson.h index dd299c7c53e..235168a36ce 100644 --- a/storage/connect/bson.h +++ b/storage/connect/bson.h @@ -62,7 +62,8 @@ DllExport bool IsNum(PSZ s); class BJSON : public BLOCK { public: // Constructor - BJSON(PGLOBAL g, PBVAL vp = NULL) { G = g, Base = G->Sarea; Bvp = vp; } + BJSON(PGLOBAL g, PBVAL vp = NULL) + { G = g, Base = G->Sarea; Bvp = vp; Throw = true; } // Utility functions inline OFFSET MOF(void *p) {return MakeOff(Base, p);} @@ -73,6 +74,7 @@ public: inline longlong LLN(OFFSET o) {return *(longlong*)MakePtr(Base, o);} inline double DBL(OFFSET o) {return *(double*)MakePtr(Base, o);} + void Reset(void) {Base = G->Sarea;} void* GetBase(void) { return Base; } void SubSet(bool b = false); void MemSave(void) {G->Saved_Size = ((PPOOLHEADER)G->Sarea)->To_Free;} @@ -102,7 +104,7 @@ public: PBVAL GetArrayValue(PBVAL bap, int i); PSZ GetArrayText(PGLOBAL g, PBVAL bap, PSTRG text); void MergeArray(PBVAL bap1,PBVAL bap2); - void DeleteValue(PBVAL bap, int n); + bool DeleteValue(PBVAL bap, int n); void AddArrayValue(PBVAL bap, OFFSET nvp = NULL, int* x = NULL); inline void AddArrayValue(PBVAL bap, PBVAL nvp = NULL, int* x = NULL) {AddArrayValue(bap, MOF(nvp), x);} @@ -126,7 +128,7 @@ public: void SetKeyValue(PBVAL bop, OFFSET bvp, PSZ key); inline void SetKeyValue(PBVAL bop, PBVAL vlp, PSZ key) {SetKeyValue(bop, MOF(vlp), key);} - void DeleteKey(PBVAL bop, PCSZ k); + bool DeleteKey(PBVAL bop, PCSZ k); bool IsObjectNull(PBVAL bop); // Value functions @@ -147,17 +149,20 @@ public: void SetString(PBVAL vlp, PSZ s, int ci = 0); void SetInteger(PBVAL vlp, int n); void SetBigint(PBVAL vlp, longlong ll); - void SetFloat(PBVAL vlp, double f); + void SetFloat(PBVAL vlp, double f, int nd = 16); + void SetFloat(PBVAL vlp, PSZ s); void SetBool(PBVAL vlp, bool b); void Clear(PBVAL vlp) { vlp->N = 0; vlp->Nd = 0; vlp->Next = 0; } bool IsValueNull(PBVAL vlp); - bool IsJson(PBVAL vlp) - {return vlp ? vlp->Type == TYPE_JAR || vlp->Type == TYPE_JOB : false;} + bool IsJson(PBVAL vlp) {return vlp ? vlp->Type == TYPE_JAR || + vlp->Type == TYPE_JOB || + vlp->Type == TYPE_JVAL : false;} // Members PGLOBAL G; PBVAL Bvp; void *Base; + bool Throw; protected: // Default constructor not to be used diff --git a/storage/connect/bsonudf.cpp b/storage/connect/bsonudf.cpp index 9c80b881e52..4bdeafa0c33 100644 --- a/storage/connect/bsonudf.cpp +++ b/storage/connect/bsonudf.cpp @@ -1,6 +1,6 @@ /****************** bsonudf C++ Program Source Code File (.CPP) ******************/ /* PROGRAM NAME: bsonudf Version 1.0 */ -/* (C) Copyright to the author Olivier BERTRAND 2020 */ +/* (C) Copyright to the author Olivier BERTRAND 2020 - 2021 */ /* This program are the BSON User Defined Functions. */ /*********************************************************************************/ @@ -28,6 +28,7 @@ #define M 6 int IsArgJson(UDF_ARGS* args, uint i); +void SetChanged(PBSON bsp); /* --------------------------------- JSON UDF ---------------------------------- */ @@ -46,6 +47,29 @@ inline void JsonFreeMem(PGLOBAL g) { g = PlugExit(g); } /* end of JsonFreeMem */ +/*********************************************************************************/ +/* Allocate and initialize a BSON structure. */ +/*********************************************************************************/ +PBSON BbinAlloc(PGLOBAL g, ulong len, PBVAL jsp) +{ + PBSON bsp = (PBSON)PlgDBSubAlloc(g, NULL, sizeof(BSON)); + + if (bsp) { + strcpy(bsp->Msg, "Binary Json"); + bsp->Msg[BMX] = 0; + bsp->Filename = NULL; + bsp->G = g; + bsp->Pretty = 2; + bsp->Reslen = len; + bsp->Changed = false; + bsp->Top = bsp->Jsp = (PJSON)jsp; + bsp->Bsp = NULL; + } else + PUSH_WARNING(g->Message); + + return bsp; +} /* end of BbinAlloc */ + /* --------------------------- New Testing BJSON Stuff --------------------------*/ /*********************************************************************************/ @@ -97,6 +121,8 @@ BJNX::BJNX(PGLOBAL g) : BDOC(g) Found = false; Wr = false; Jb = false; + Changed = false; + Throw = false; } // end of BJNX constructor /*********************************************************************************/ @@ -126,6 +152,8 @@ BJNX::BJNX(PGLOBAL g, PBVAL row, int type, int len, int prec, my_bool wr) : BDOC Found = false; Wr = wr; Jb = false; + Changed = false; + Throw = false; } // end of BJNX constructor /*********************************************************************************/ @@ -209,9 +237,7 @@ my_bool BJNX::SetArrayOptions(PGLOBAL g, char* p, int i, PSZ nm) case '<': jnp->Op = OP_MIN; break; case '!': jnp->Op = OP_SEP; break; // Average case '#': jnp->Op = OP_NUM; break; - case '*': // Expand this array - strcpy(g->Message, "Expand not supported by this function"); - return true; + case '*': jnp->Op = OP_EXP; break; default: sprintf(g->Message, "Invalid function specification %c", *p); return true; @@ -521,6 +547,9 @@ PBVAL BJNX::GetRowValue(PGLOBAL g, PBVAL row, int i, my_bool b) Jb = b; // return DupVal(g, row); return row; // or last line ??? + } else if (Nodes[i].Op == OP_EXP) { + PUSH_WARNING("Expand not supported by this function"); + return NULL; } else switch (row->Type) { case TYPE_JOB: if (!Nodes[i].Key) { @@ -685,7 +714,10 @@ PBVAL BJNX::GetRow(PGLOBAL g) for (int i = 0; i < Nod - 1 && row; i++) { if (Nodes[i].Op == OP_XX) break; - else switch (row->Type) { + else if (Nodes[i].Op == OP_EXP) { + PUSH_WARNING("Expand not supported by this function"); + return NULL; + } else switch (row->Type) { case TYPE_JOB: if (!Nodes[i].Key) // Expected Array was not there, wrap the value @@ -790,6 +822,90 @@ my_bool BJNX::WriteValue(PGLOBAL g, PBVAL jvalp) } // end of WriteValue /*********************************************************************************/ +/* GetRowValue: */ +/*********************************************************************************/ +my_bool BJNX::DeleteItem(PGLOBAL g, PBVAL row) +{ + int n = -1; + my_bool b = false; + bool loop; + PBVAL vlp, pvp, rwp; + + do { + loop = false; + vlp = NULL; + pvp = rwp = row; + + for (int i = 0; i < Nod && rwp; i++) { + if (Nodes[i].Op == OP_XX) + break; + else switch (rwp->Type) { + case TYPE_JOB: + if (!Nodes[i].Key) { + vlp = NULL; + } else + vlp = GetKeyValue(rwp, Nodes[i].Key); + + break; + case TYPE_JAR: + if (!Nodes[i].Key) { + if (Nodes[i].Op == OP_EXP) { + if (loop) { + PUSH_WARNING("Only one expand can be handled"); + return b; + } // endif loop + n++; + loop = true; + } else + n = Nodes[i].Rank; + + vlp = GetArrayValue(rwp, n); + } else + vlp = NULL; + + break; + case TYPE_JVAL: + vlp = rwp; + break; + default: + vlp = NULL; + } // endswitch Type + + pvp = rwp; + rwp = vlp; + vlp = NULL; + } // endfor i + + if (rwp) { + if (Nodes[Nod - 1].Op == OP_XX) { + if (!IsJson(rwp)) + rwp->Type = TYPE_NULL; + + rwp->To_Val = 0; + } else switch (pvp->Type) { + case TYPE_JOB: + b = DeleteKey(pvp, Nodes[Nod - 1].Key); + break; + case TYPE_JAR: + if (Nodes[Nod - 1].Op == OP_EXP) { + pvp->To_Val = 0; + loop = false; + } else + b = DeleteValue(pvp, n); + + break; + default: + break; + } // endswitch Type + + } // endif rwp + + } while (loop); + + return b; +} // end of DeleteItem + +/*********************************************************************************/ /* CheckPath: Checks whether the path exists in the document. */ /*********************************************************************************/ my_bool BJNX::CheckPath(PGLOBAL g) @@ -1248,12 +1364,13 @@ my_bool BJNX::AddPath(void) /*********************************************************************************/ /* Make a JSON value from the passed argument. */ /*********************************************************************************/ -PBVAL BJNX::MakeValue(PGLOBAL g, UDF_ARGS *args, uint i, PBVAL *top) +PBVAL BJNX::MakeValue(UDF_ARGS *args, uint i, bool b, PBVAL *top) { char *sap = (args->arg_count > i) ? args->args[i] : NULL; int n, len; int ci; long long bigint; + PGLOBAL& g = G; PBVAL jvp = NewVal(); if (top) @@ -1267,15 +1384,25 @@ PBVAL BJNX::MakeValue(PGLOBAL g, UDF_ARGS *args, uint i, PBVAL *top) if (n) { if (n == 3) { -// if (top) -// *top = ((PBSON)sap)->Top; + if (i == 0) { + PBSON bsp = (PBSON)sap; + + if (top) + *top = (PBVAL)bsp->Top; + + jvp = (PBVAL)bsp->Jsp; + G = bsp->G; + Base = G->Sarea; + } else { + PUSH_WARNING("Only first argument can be binary"); + return jvp; + } // endelse i -// jvp = ((PBSON)sap)->Jsp; } else { if (n == 2) { if (!(sap = GetJsonFile(g, sap))) { PUSH_WARNING(g->Message); - return NewVal(); + return jvp; } // endif sap len = strlen(sap); @@ -1289,8 +1416,38 @@ PBVAL BJNX::MakeValue(PGLOBAL g, UDF_ARGS *args, uint i, PBVAL *top) } // endif's n } else { - ci = (strnicmp(args->attributes[i], "ci", 2)) ? 0 : 1; - SetString(jvp, sap, ci); + PBVAL bp = NULL; + + if (b) { + if (strchr("[{ \t\r\n", *sap)) { + // Check whether this string is a valid json string + JsonMemSave(g); + + if (!(bp = ParseJson(g, sap, strlen(sap)))) + JsonSubSet(g); // Recover suballocated memory + + g->Saved_Size = 0; + } else { + // Perhaps a file name + char* s = GetJsonFile(g, sap); + + if (s) + bp = ParseJson(g, s, strlen(s)); + + } // endif's + + } // endif b + + if (!bp) { + ci = (strnicmp(args->attributes[i], "ci", 2)) ? 0 : 1; + SetString(jvp, sap, ci); + } else { + if (top) + *top = bp; + + jvp = bp; + } // endif bp + } // endif n } // endif len @@ -1310,7 +1467,7 @@ PBVAL BJNX::MakeValue(PGLOBAL g, UDF_ARGS *args, uint i, PBVAL *top) SetFloat(jvp, *(double*)sap); break; case DECIMAL_RESULT: - SetFloat(jvp, atof(MakePSZ(g, args, i))); + SetFloat(jvp, MakePSZ(g, args, i)); break; case TIME_RESULT: case ROW_RESULT: @@ -1322,92 +1479,13 @@ PBVAL BJNX::MakeValue(PGLOBAL g, UDF_ARGS *args, uint i, PBVAL *top) } // end of MakeValue /*********************************************************************************/ -/* Make a BVAL value from the passed argument. */ -/*********************************************************************************/ -PBVAL BJNX::MakeBinValue(PGLOBAL g, UDF_ARGS* args, uint i) -{ - char* sap = (args->arg_count > i) ? args->args[i] : NULL; - int n, len; - int ci; - longlong bigint; - PBVAL bp, bvp = NewVal(); - - if (sap) { - if (args->arg_type[i] == STRING_RESULT) { - if ((len = args->lengths[i])) { - if ((n = IsArgJson(args, i)) < 3) - sap = MakePSZ(g, args, i); - - if (n) { - if (n == 2) { - if (!(sap = GetJsonFile(g, sap))) { - PUSH_WARNING(g->Message); - return NULL; - } // endif sap - - len = strlen(sap); - } // endif 2 - - if (!(bp = ParseJson(g, sap, strlen(sap)))) { - PUSH_WARNING(g->Message); - return NULL; - } else - bvp = bp; - - } else { - // Check whether this string is a valid json string - JsonMemSave(g); - - if (!(bp = ParseJson(g, sap, strlen(sap)))) { - // Recover suballocated memory - JsonSubSet(g); - ci = (strnicmp(args->attributes[i], "ci", 2)) ? 0 : 1; - SetString(bvp, sap, ci); - } else - bvp = bp; - - g->Saved_Size = 0; - } // endif n - - } // endif len - - } else switch (args->arg_type[i]) { - case INT_RESULT: - bigint = *(longlong*)sap; - - if ((bigint == 0LL && !strcmp(args->attributes[i], "FALSE")) || - (bigint == 1LL && !strcmp(args->attributes[i], "TRUE"))) - SetBool(bvp, (bool)bigint); - else - SetBigint(bvp, bigint); - - break; - case REAL_RESULT: - SetFloat(bvp, *(double*)sap); - break; - case DECIMAL_RESULT: - SetFloat(bvp, atof(MakePSZ(g, args, i))); - break; - case TIME_RESULT: - case ROW_RESULT: - default: - bvp->Type = TYPE_UNKNOWN; - break; - } // endswitch arg_type - - } // endif sap - - return bvp; -} // end of MakeBinValue - -/*********************************************************************************/ /* Try making a JSON value of the passed type from the passed argument. */ /*********************************************************************************/ PBVAL BJNX::MakeTypedValue(PGLOBAL g, UDF_ARGS *args, uint i, JTYP type, PBVAL *top) { char *sap; PBVAL jsp; - PBVAL jvp = MakeValue(g, args, i, top); + PBVAL jvp = MakeValue(args, i, false, top); //if (type == TYPE_JSON) { // if (jvp->GetValType() >= TYPE_JSON) @@ -1485,15 +1563,94 @@ PBVAL BJNX::ParseJsonFile(PGLOBAL g, char *fn, int& pty, size_t& len) return jsp; } // end of ParseJsonFile -/* -----------------------------Utility functions ------------------------------ */ +/*********************************************************************************/ +/* Make the result according to the first argument type. */ +/*********************************************************************************/ +char *BJNX::MakeResult(UDF_ARGS *args, PBVAL top, uint n) +{ + char *str = NULL; + PGLOBAL& g = G; + + if (IsArgJson(args, 0) == 2) { + // Make the change in the json file + PSZ fn = MakePSZ(g, args, 0); + + if (Changed) { + int pretty = 2; + + for (uint i = n; i < args->arg_count; i++) + if (args->arg_type[i] == INT_RESULT) { + pretty = (int)*(longlong*)args->args[i]; + break; + } // endif type + + if (!Serialize(g, top, fn, pretty)) + PUSH_WARNING(g->Message); + + Changed = false; + } // endif Changed + + str = fn; + } else if (IsArgJson(args, 0) == 3) { + PBSON bsp = (PBSON)args->args[0]; + + if (bsp->Filename) { + if (Changed) { + // Make the change in the json file + if (!Serialize(g, (PBVAL)top, bsp->Filename, bsp->Pretty)) + PUSH_WARNING(g->Message); + + Changed = false; + } // endif Changed + + str = bsp->Filename; + } else if (!(str = Serialize(g, (PBVAL)top, NULL, 0))) + PUSH_WARNING(g->Message); + + } else if (!(str = Serialize(g, top, NULL, 0))) + PUSH_WARNING(g->Message); + + return str; +} // end of MakeResult /*********************************************************************************/ -/* GetMemPtr: returns the memory pointer used by this argument. */ +/* Make the binary result according to the first argument type. */ /*********************************************************************************/ -static PGLOBAL GetMemPtr(PGLOBAL g, UDF_ARGS *args, uint i) +PBSON BJNX::MakeBinResult(PGLOBAL g, UDF_ARGS *args, PBVAL top, ulong len, int n) { - return (IsArgJson(args, i) == 3) ? ((PBSON)args->args[i])->G : g; -} // end of GetMemPtr + char* filename = NULL; + int pretty = 2; + PBSON bnp = NULL; + + if (IsArgJson(args, 0) == 3) { + bnp = (PBSON)args->args[0]; + + if (bnp->Top != (PJSON)top) + bnp->Top = bnp->Jsp = (PJSON)top; + + return bnp; + } // endif 3 + + if (IsArgJson(args, 0) == 2) { + for (uint i = n; i < args->arg_count; i++) + if (args->arg_type[i] == INT_RESULT) { + pretty = (int)*(longlong*)args->args[i]; + break; + } // endif type + + filename = (char*)args->args[0]; + } // endif 2 + + if ((bnp = BbinAlloc(g, len, top))) { + bnp->Filename = filename; + bnp->Pretty = pretty; + strcpy(bnp->Msg, "Json Binary item"); + } //endif bnp + + return bnp; +} // end of MakeBinResult + +/* -----------------------------Utility functions ------------------------------ */ /*********************************************************************************/ /* Returns a pointer to the first integer argument found from the nth argument. */ @@ -1559,49 +1716,6 @@ int IsArgJson(UDF_ARGS *args, uint i) } // end of IsArgJson /*********************************************************************************/ -/* Make the result according to the first argument type. */ -/*********************************************************************************/ -static char *MakeResult(PGLOBAL g, UDF_ARGS *args, PBVAL top, uint n = 2) -{ - char *str = NULL; - BDOC doc(g); - - if (IsArgJson(args, 0) == 2) { - // Make the change in the json file - int pretty = 2; - - for (uint i = n; i < args->arg_count; i++) - if (args->arg_type[i] == INT_RESULT) { - pretty = (int)*(longlong*)args->args[i]; - break; - } // endif type - - if (!doc.Serialize(g, top, MakePSZ(g, args, 0), pretty)) - PUSH_WARNING(g->Message); - - str = NULL; - } else if (IsArgJson(args, 0) == 3) { -#if 0 - PBSON bsp = (PBSON)args->args[0]; - - if (bsp->Filename) { - // Make the change in the json file - if (!Serialize(g, top, bsp->Filename, bsp->Pretty)) - PUSH_WARNING(g->Message); - - str = bsp->Filename; - } else if (!(str = Serialize(g, top, NULL, 0))) - PUSH_WARNING(g->Message); - - SetChanged(bsp); -#endif - } else if (!(str = doc.Serialize(g, top, NULL, 0))) - PUSH_WARNING(g->Message); - - return str; -} // end of MakeResult - -/*********************************************************************************/ /* GetFileLength: returns file size in number of bytes. */ /*********************************************************************************/ static long GetFileLength(char *fn) @@ -1649,7 +1763,7 @@ char* bsonvalue(UDF_INIT* initid, UDF_ARGS* args, char* result, if (!g->Xchk) { if (!CheckMemory(g, initid, args, 1, false)) { BJNX bnx(g); - PBVAL bvp = bnx.MakeBinValue(g, args, 0); + PBVAL bvp = bnx.MakeValue(args, 0, true); if (!(str = bnx.Serialize(g, bvp, NULL, 0))) str = strcpy(result, g->Message); @@ -1693,7 +1807,7 @@ char* bson_make_array(UDF_INIT* initid, UDF_ARGS* args, char* result, PBVAL bvp = NULL, arp = bnx.NewVal(TYPE_JAR); for (uint i = 0; i < args->arg_count; i++) - bnx.AddArrayValue(arp, bnx.MakeBinValue(g, args, i)); + bnx.AddArrayValue(arp, bnx.MakeValue(args, i, true)); if (!(str = bnx.Serialize(g, arp, NULL, 0))) str = strcpy(result, g->Message); @@ -1752,20 +1866,19 @@ char* bson_array_add_values(UDF_INIT* initid, UDF_ARGS* args, char* result, if (!g->Xchk) { if (!CheckMemory(g, initid, args, args->arg_count, true)) { - uint i = 0; BJNX bnx(g); - PBVAL arp, bvp = bnx.MakeBinValue(g, args, 0); + PBVAL arp = bnx.MakeValue(args, 0, true); - if (bvp->Type == TYPE_JAR) { - arp = bvp; - i = 1; - } else // First argument is not an array - arp = bnx.NewVal(TYPE_JAR); + if (arp->Type != TYPE_JAR) { + PUSH_WARNING("First argument is not an array"); + goto fin; + } // endif arp - for (; i < args->arg_count; i++) - bnx.AddArrayValue(arp, bnx.MakeBinValue(g, args, i)); + for (uint i = 1; i < args->arg_count; i++) + bnx.AddArrayValue(arp, bnx.MakeValue(args, i)); - str = bnx.Serialize(g, arp, NULL, 0); + bnx.SetChanged(true); + str = bnx.MakeResult(args, arp, INT_MAX); } // endif CheckMemory if (!str) { @@ -1778,6 +1891,7 @@ char* bson_array_add_values(UDF_INIT* initid, UDF_ARGS* args, char* result, } else str = (char*)g->Xchk; + fin: if (!str) { *res_length = 0; *is_null = 1; @@ -1840,7 +1954,7 @@ char *bson_array_add(UDF_INIT *initid, UDF_ARGS *args, char *result, uint n = 2; BJNX bnx(g, NULL, TYPE_STRING); PBVAL jsp, top; - PBVAL arp, jvp = bnx.MakeTypedValue(g, args, 0, TYPE_JAR, &top); + PBVAL arp, jvp = bnx.MakeValue(args, 0, true, &top); jsp = jvp; x = GetIntArgPtr(g, args, n); @@ -1848,8 +1962,6 @@ char *bson_array_add(UDF_INIT *initid, UDF_ARGS *args, char *result, if (bnx.CheckPath(g, args, jsp, jvp, 2)) PUSH_WARNING(g->Message); else if (jvp) { - PGLOBAL gb = GetMemPtr(g, args, 0); - if (jvp->Type != TYPE_JAR) { if ((arp = bnx.NewVal(TYPE_JAR))) { bnx.AddArrayValue(arp, jvp); @@ -1863,10 +1975,11 @@ char *bson_array_add(UDF_INIT *initid, UDF_ARGS *args, char *result, arp = jvp; if (arp) { - bnx.AddArrayValue(arp, bnx.MakeValue(gb, args, 1), x); - str = MakeResult(g, args, top, n); + bnx.AddArrayValue(arp, bnx.MakeValue(args, 1), x); + bnx.SetChanged(true); + str = bnx.MakeResult(args, top, n); } else - PUSH_WARNING(gb->Message); + PUSH_WARNING(g->Message); } else { PUSH_WARNING("Target is not an array"); @@ -1919,7 +2032,7 @@ my_bool bson_array_delete_init(UDF_INIT *initid, UDF_ARGS *args, char *message) g->N = (initid->const_item) ? 1 : 0; // This is to avoid double execution when using prepared statements - if (IsJson(args, 0) > 1) + if (IsArgJson(args, 0) > 1) initid->const_item = 0; return false; @@ -1943,9 +2056,9 @@ char *bson_array_delete(UDF_INIT *initid, UDF_ARGS *args, char *result, if (!CheckMemory(g, initid, args, 1, false, false, true)) { int *x; uint n = 1; - BJNX bnx(g, NULL, TYPE_STRING); + BJNX bnx(g); PBVAL arp, top; - PBVAL jvp = bnx.MakeTypedValue(g, args, 0, TYPE_JSON, &top); + PBVAL jvp = bnx.MakeValue(args, 0, true, &top); if (!(x = GetIntArgPtr(g, args, n))) PUSH_WARNING("Missing or null array index"); @@ -1953,7 +2066,8 @@ char *bson_array_delete(UDF_INIT *initid, UDF_ARGS *args, char *result, PUSH_WARNING(g->Message); else if (arp && arp->Type == TYPE_JAR) { bnx.DeleteValue(arp, *x); - str = MakeResult(g, args, top, n); + bnx.SetChanged(true); + str = bnx.MakeResult(args, top, n); } else { PUSH_WARNING("First argument target is not an array"); // if (g->Mrr) *error = 1; @@ -2009,7 +2123,7 @@ char *bson_make_object(UDF_INIT *initid, UDF_ARGS *args, char *result, if ((objp = bnx.NewVal(TYPE_JOB))) { for (uint i = 0; i < args->arg_count; i++) - bnx.SetKeyValue(objp, bnx.MakeValue(g, args, i), bnx.MakeKey(args, i)); + bnx.SetKeyValue(objp, bnx.MakeValue(args, i), bnx.MakeKey(args, i)); str = bnx.Serialize(g, objp, NULL, 0); } // endif objp @@ -2058,7 +2172,7 @@ char *bson_object_nonull(UDF_INIT *initid, UDF_ARGS *args, char *result, if ((objp = bnx.NewVal(TYPE_JOB))) { for (uint i = 0; i < args->arg_count; i++) - if (!bnx.IsValueNull(jvp = bnx.MakeValue(g, args, i))) + if (!bnx.IsValueNull(jvp = bnx.MakeValue(args, i))) bnx.SetKeyValue(objp, jvp, bnx.MakeKey(args, i)); str = bnx.Serialize(g, objp, NULL, 0); @@ -2112,7 +2226,7 @@ char *bson_object_key(UDF_INIT *initid, UDF_ARGS *args, char *result, if ((objp = bnx.NewVal(TYPE_JOB))) { for (uint i = 0; i < args->arg_count; i += 2) - bnx.SetKeyValue(objp, bnx.MakeValue(g, args, i + 1), MakePSZ(g, args, i)); + bnx.SetKeyValue(objp, bnx.MakeValue(args, i + 1), MakePSZ(g, args, i)); str = bnx.Serialize(g, objp, NULL, 0); } // endif objp @@ -2159,7 +2273,7 @@ my_bool bson_object_add_init(UDF_INIT *initid, UDF_ARGS *args, char *message) g->N = (initid->const_item) ? 1 : 0; // This is to avoid double execution when using prepared statements - if (IsJson(args, 0) > 1) + if (IsArgJson(args, 0) > 1) initid->const_item = 0; return false; @@ -2182,20 +2296,21 @@ char *bson_object_add(UDF_INIT *initid, UDF_ARGS *args, char *result, } // endif Xchk if (!CheckMemory(g, initid, args, 2, false, true, true)) { - BJNX bnx(g, NULL, TYPE_STRG); + BJNX bnx(g, NULL, TYPE_STRING); PBVAL jvp, objp; PBVAL jsp, top; - jsp = bnx.MakeValue(g, args, 0, &top); + jsp = bnx.MakeValue(args, 0, true, &top); if (bnx.CheckPath(g, args, jsp, jvp, 2)) PUSH_WARNING(g->Message); else if (jvp && jvp->Type == TYPE_JOB) { objp = jvp; - jvp = bnx.MakeValue(g, args, 1); + jvp = bnx.MakeValue(args, 1); key = bnx.MakeKey(args, 1); bnx.SetKeyValue(objp, jvp, key); - str = MakeResult(g, args, top); + bnx.SetChanged(true); + str = bnx.MakeResult(args, top); } else { PUSH_WARNING("First argument target is not an object"); // if (g->Mrr) *error = 1; (only if no path) @@ -2253,7 +2368,7 @@ my_bool bson_object_delete_init(UDF_INIT *initid, UDF_ARGS *args, char *message) g->N = (initid->const_item) ? 1 : 0; // This is to avoid double execution when using prepared statements - if (IsJson(args, 0) > 1) + if (IsArgJson(args, 0) > 1) initid->const_item = 0; return false; @@ -2275,21 +2390,22 @@ char *bson_object_delete(UDF_INIT *initid, UDF_ARGS *args, char *result, } // endif Xchk if (!CheckMemory(g, initid, args, 1, false, true, true)) { + bool chg; BJNX bnx(g, NULL, TYPE_STRG); PSZ key; PBVAL jsp, objp, top; - PBVAL jvp = bnx.MakeValue(g, args, 0, &top); + PBVAL jvp = bnx.MakeValue(args, 0, false, &top); jsp = jvp; if (bnx.CheckPath(g, args, jsp, jvp, 2)) PUSH_WARNING(g->Message); else if (jvp && jvp->Type == TYPE_JOB) { -// key = MakeKey(GetMemPtr(g, args, 0), args, 1); key = bnx.MakeKey(args, 1); objp = jvp; - bnx.DeleteKey(objp, key); - str = MakeResult(g, args, top); + chg = bnx.DeleteKey(objp, key); + bnx.SetChanged(chg); + str = bnx.MakeResult(args, top); } else { PUSH_WARNING("First argument target is not an object"); // if (g->Mrr) *error = 1; (only if no path) @@ -2349,18 +2465,8 @@ char *bson_object_list(UDF_INIT *initid, UDF_ARGS *args, char *result, if (!g->N) { if (!CheckMemory(g, initid, args, 1, true, true)) { BJNX bnx(g); - char *p; - PBVAL jsp, jarp; - PBVAL jvp = bnx.MakeValue(g, args, 0); - - if ((p = bnx.GetString(jvp))) { - if (!(jsp = bnx.ParseJson(g, p, strlen(p)))) { - PUSH_WARNING(g->Message); - return NULL; - } // endif jsp - - } else - jsp = jvp; + PBVAL jarp; + PBVAL jsp = bnx.MakeValue(args, 0, true); if (jsp->Type == TYPE_JOB) { jarp = bnx.GetKeyList(jsp); @@ -2408,7 +2514,7 @@ my_bool bson_object_values_init(UDF_INIT *initid, UDF_ARGS *args, char *message) if (args->arg_count != 1) { strcpy(message, "This function must have 1 argument"); return true; - } else if (!IsJson(args, 0) && args->arg_type[0] != STRING_RESULT) { + } else if (!IsArgJson(args, 0) && args->arg_type[0] != STRING_RESULT) { strcpy(message, "Argument must be a json object"); return true; } else @@ -2428,7 +2534,7 @@ char *bson_object_values(UDF_INIT *initid, UDF_ARGS *args, char *result, BJNX bnx(g); char *p; PBVAL jsp, jarp; - PBVAL jvp = bnx.MakeValue(g, args, 0); + PBVAL jvp = bnx.MakeValue(args, 0); if ((p = bnx.GetString(jvp))) { if (!(jsp = bnx.ParseJson(g, p, strlen(p)))) { @@ -2560,7 +2666,7 @@ void bson_array_grp_add(UDF_INIT *initid, UDF_ARGS *args, char*, char*) PBVAL arp = (PBVAL)g->Activityp; if (arp && g->N-- > 0) - bxp->AddArrayValue(arp, bxp->MakeValue(g, args, 0)); + bxp->AddArrayValue(arp, bxp->MakeValue(args, 0)); } // end of bson_array_grp_add @@ -2597,7 +2703,7 @@ my_bool bson_object_grp_init(UDF_INIT *initid, UDF_ARGS *args, char *message) if (args->arg_count != 2) { strcpy(message, "This function requires 2 arguments (key, value)"); return true; - } else if (IsJson(args, 0) == 3) { + } else if (IsArgJson(args, 0) == 3) { strcpy(message, "This function does not support Jbin arguments"); return true; } else @@ -2633,7 +2739,7 @@ void bson_object_grp_add(UDF_INIT *initid, UDF_ARGS *args, char*, char*) PBVAL bop = (PBVAL)g->Activityp; if (g->N-- > 0) - bxp->SetKeyValue(bop, bxp->MakeValue(g, args, 0), MakePSZ(g, args, 1)); + bxp->SetKeyValue(bop, bxp->MakeValue(args, 0), MakePSZ(g, args, 1)); } // end of bson_object_grp_add @@ -2699,7 +2805,12 @@ char* bson_test(UDF_INIT* initid, UDF_ARGS* args, char* result, PUSH_WARNING("CheckMemory error"); *error = 1; goto err; - } else if (!(bvp = bnx.MakeBinValue(g, args, 0))) { + } else // Sarea may have been reallocated + bnx.Reset(); + + bvp = bnx.MakeValue(args, 0, true); + + if (bvp->Type == TYPE_NULL) { PUSH_WARNING(g->Message); goto err; } // endif bvp @@ -2806,24 +2917,33 @@ char* bsonlocate(UDF_INIT* initid, UDF_ARGS* args, char* result, PUSH_WARNING("CheckMemory error"); *error = 1; goto err; - } else - bvp = bnx.MakeBinValue(g, args, 0); + } else { + bnx.Reset(); // Sarea may have been re-allocated + bvp = bnx.MakeValue(args, 0, true); - if (!bvp) { - PUSH_WARNING("First argument is not a valid JSON item"); - goto err; - } // endif bvp + if (!bvp) { + bnx.GetMsg(g); + PUSH_WARNING(g->Message); + goto err; + } else if (bvp->Type == TYPE_NULL) { + PUSH_WARNING("First argument is not a valid JSON item"); + goto err; + } // endif bvp - if (g->Mrr) { // First argument is a constant - g->Xchk = bvp; - JsonMemSave(g); - } // endif Mrr + if (g->Mrr) { // First argument is a constant + g->Xchk = bvp; + JsonMemSave(g); + } // endif Mrr + + } // endif CheckMemory } else bvp = (PBVAL)g->Xchk; // The item to locate - if (!(bvp2 = bnx.MakeBinValue(g, args, 1))) { + bvp2 = bnx.MakeValue(args, 1, true); + + if (bvp2->Type == TYPE_NULL) { PUSH_WARNING("Invalid second argument"); goto err; } // endif bvp @@ -2920,9 +3040,11 @@ char* bson_locate_all(UDF_INIT* initid, UDF_ARGS* args, char* result, *error = 1; goto err; } else - bvp = bnx.MakeBinValue(g, args, 0); + bnx.Reset(); - if (!bvp) { + bvp = bnx.MakeValue(args, 0, true); + + if (bvp->Type == TYPE_NULL) { PUSH_WARNING("First argument is not a valid JSON item"); goto err; } // endif bvp @@ -2936,7 +3058,9 @@ char* bson_locate_all(UDF_INIT* initid, UDF_ARGS* args, char* result, bvp = (PBVAL)g->Xchk; // The item to locate - if (!(bvp2 = bnx.MakeBinValue(g, args, 1))) { + bvp2 = bnx.MakeValue(args, 1, true); + + if (bvp2->Type == TYPE_NULL) { PUSH_WARNING("Invalid second argument"); goto err; } // endif bvp @@ -3006,7 +3130,7 @@ my_bool bson_contains_init(UDF_INIT *initid, UDF_ARGS *args, char *message) //memlen += more; // TODO: calculate this - more += (IsJson(args, 0) != 3 ? 1000 : 0); + more += (IsArgJson(args, 0) != 3 ? 1000 : 0); return JsonInit(initid, args, message, false, reslen, memlen, more); } // end of bson contains_init @@ -3055,7 +3179,7 @@ my_bool bsoncontains_path_init(UDF_INIT *initid, UDF_ARGS *args, char *message) //memlen += more; // TODO: calculate this - more += (IsJson(args, 0) != 3 ? 1000 : 0); + more += (IsArgJson(args, 0) != 3 ? 1000 : 0); return JsonInit(initid, args, message, true, reslen, memlen, more); } // end of bsoncontains_path_init @@ -3085,7 +3209,7 @@ long long bsoncontains_path(UDF_INIT *initid, UDF_ARGS *args, char *, char *erro } else { BJNX bnx(g); - jvp = bnx.MakeValue(g, args, 0); + jvp = bnx.MakeValue(args, 0); if ((p = bnx.GetString(jvp))) { if (!(jsp = bnx.ParseJson(g, p, strlen(p)))) { @@ -3193,7 +3317,7 @@ char *bson_item_merge(UDF_INIT *initid, UDF_ARGS *args, char *result, PBVAL jsp[2] = {NULL, NULL}; for (int i = 0; i < 2; i++) { - jvp = bnx.MakeBinValue(g, args, i); + jvp = bnx.MakeValue(args, i, true); if (i) { if (jvp->Type != type) { @@ -3220,7 +3344,8 @@ char *bson_item_merge(UDF_INIT *initid, UDF_ARGS *args, char *result, else bnx.MergeObject(jsp[0], jsp[1]); - str = MakeResult(g, args, top); + bnx.SetChanged(true); + str = bnx.MakeResult(args, top); } // endif CheckMemory // In case of error or file, return unchanged first argument @@ -3286,8 +3411,8 @@ my_bool bson_get_item_init(UDF_INIT *initid, UDF_ARGS *args, char *message) char *bson_get_item(UDF_INIT *initid, UDF_ARGS *args, char *result, unsigned long *res_length, char *is_null, char *) { - char *p, *path, *str = NULL; - PBVAL jsp, jvp; + char *path, *str = NULL; + PBVAL jvp; PBJNX bxp = NULL; PGLOBAL g = (PGLOBAL)initid->ptr; @@ -3304,29 +3429,20 @@ char *bson_get_item(UDF_INIT *initid, UDF_ARGS *args, char *result, } else { BJNX bnx(g); - jvp = bnx.MakeValue(g, args, 0); - - if ((p = bnx.GetString(jvp))) { - if (!(jsp = bnx.ParseJson(g, p, strlen(p)))) { - PUSH_WARNING(g->Message); - goto fin; - } // endif jsp - - } else - jsp = jvp; + jvp = bnx.MakeValue(args, 0, true); if (g->Mrr) { // First argument is a constant - g->Xchk = jsp; + g->Xchk = jvp; JsonMemSave(g); } // endif Mrr } // endelse CheckMemory } else - jsp = (PBVAL)g->Xchk; + jvp = (PBVAL)g->Xchk; path = MakePSZ(g, args, 1); - bxp = new(g) BJNX(g, jsp, TYPE_STRING, initid->max_length); + bxp = new(g) BJNX(g, jvp, TYPE_STRING, initid->max_length); if (bxp->SetJpath(g, path, true)) { PUSH_WARNING(g->Message); @@ -3420,7 +3536,7 @@ char *bsonget_string(UDF_INIT *initid, UDF_ARGS *args, char *result, } else { BJNX bnx(g); - jvp = bnx.MakeValue(g, args, 0); + jvp = bnx.MakeValue(args, 0); if ((p = bnx.GetString(jvp))) { if (!(jsp = bnx.ParseJson(g, p, strlen(p)))) { @@ -3504,7 +3620,7 @@ my_bool bsonget_int_init(UDF_INIT *initid, UDF_ARGS *args, char *message) CalcLen(args, false, reslen, memlen); // TODO: calculate this - more = (IsJson(args, 0) != 3) ? 1000 : 0; + more = (IsArgJson(args, 0) != 3) ? 1000 : 0; return JsonInit(initid, args, message, true, reslen, memlen, more); } // end of bsonget_int_init @@ -3537,7 +3653,7 @@ long long bsonget_int(UDF_INIT *initid, UDF_ARGS *args, } else { BJNX bnx(g); - jvp = bnx.MakeValue(g, args, 0); + jvp = bnx.MakeValue(args, 0); if ((p = bnx.GetString(jvp))) { if (!(jsp = bnx.ParseJson(g, p, strlen(p)))) { @@ -3626,7 +3742,7 @@ my_bool bsonget_real_init(UDF_INIT *initid, UDF_ARGS *args, char *message) CalcLen(args, false, reslen, memlen); // TODO: calculate this - more = (IsJson(args, 0) != 3) ? 1000 : 0; + more = (IsArgJson(args, 0) != 3) ? 1000 : 0; return JsonInit(initid, args, message, true, reslen, memlen, more); } // end of bsonget_real_init @@ -3659,7 +3775,7 @@ double bsonget_real(UDF_INIT *initid, UDF_ARGS *args, } else { BJNX bnx(g); - jvp = bnx.MakeValue(g, args, 0); + jvp = bnx.MakeValue(args, 0); if ((p = bnx.GetString(jvp))) { if (!(jsp = bnx.ParseJson(g, p, strlen(p)))) { @@ -3721,19 +3837,130 @@ void bsonget_real_deinit(UDF_INIT* initid) } // end of bsonget_real_deinit /*********************************************************************************/ +/* Delete items from a Json document. */ +/*********************************************************************************/ +my_bool bson_delete_item_init(UDF_INIT *initid, UDF_ARGS *args, char *message) +{ + unsigned long reslen, memlen; + + if (args->arg_count < 2) { + if (IsArgJson(args, 0) != 3) { + strcpy(message, "This function must have at least 2 arguments or one binary"); + return true; + } // endif args + + } // endif count + + CalcLen(args, false, reslen, memlen, true); + + if (!JsonInit(initid, args, message, true, reslen, memlen)) { + PGLOBAL g = (PGLOBAL)initid->ptr; + + // Is this a constant function + g->N = (initid->const_item) ? 1 : 0; + + // This is to avoid double execution when using prepared statements + if (IsArgJson(args, 0) > 1) + initid->const_item = 0; + + return false; + } else + return true; + +} // end of bson_delete_item_init + +char *bson_delete_item(UDF_INIT *initid, UDF_ARGS *args, char *result, + unsigned long *res_length, char *is_null, char *error) +{ + char *path, *str = NULL; + PGLOBAL g = (PGLOBAL)initid->ptr; + + if (g->Xchk) { + // This constant function was recalled + str = (char*)g->Xchk; + goto fin; + } // endif Xchk + + if (!CheckMemory(g, initid, args, 1, false, false, true)) { + BJNX bnx(g, NULL, TYPE_STRING); + PBVAL top, jar = NULL; + PBVAL jvp = bnx.MakeValue(args, 0, true, &top); + + if (args->arg_count == 1) { + // This should be coming from bbin_locate_all + jar = jvp; // This is the array of paths + jvp = top; // And this is the document + } else if(!bnx.IsJson(jvp)) { + PUSH_WARNING("First argument is not a JSON document"); + goto fin; + } else if (args->arg_count == 2) { + // Check whether this is an array of paths + jar = bnx.MakeValue(args, 1, true); + + if (jar && jar->Type != TYPE_JAR) + jar = NULL; + + } // endif arg_count + + if (jar) { + // Do the deletion in reverse order + for(int i = bnx.GetArraySize(jar) - 1; i >= 0; i--) { + path = bnx.GetString(bnx.GetArrayValue(jar, i)); + + if (bnx.SetJpath(g, path, false)) { + PUSH_WARNING(g->Message); + continue; + } // endif SetJpath + + bnx.SetChanged(bnx.DeleteItem(g, jvp)); + } // endfor i + + } else for (uint i = 1; i < args->arg_count; i++) { + path = MakePSZ(g, args, i); + + if (bnx.SetJpath(g, path, false)) { + PUSH_WARNING(g->Message); + continue; + } // endif SetJpath + + bnx.SetChanged(bnx.DeleteItem(g, jvp)); + } // endfor i + + str = bnx.MakeResult(args, top, INT_MAX); + } // endif CheckMemory + + if (g->N) + // Keep result of constant function + g->Xchk = str; + +fin: + if (!str) { + *is_null = 1; + *error = 1; + *res_length = 0; + } else + *res_length = strlen(str); + + return str; +} // end of bson_delete_item + +void bson_delete_item_deinit(UDF_INIT* initid) +{ + JsonFreeMem((PGLOBAL)initid->ptr); +} // end of bson_delete_item_deinit + +/*********************************************************************************/ /* This function is used by the json_set/insert/update_item functions. */ /*********************************************************************************/ static char *bson_handle_item(UDF_INIT *initid, UDF_ARGS *args, char *result, unsigned long *res_length, char *is_null, char *error) { - char *p, *path, *str = NULL; + char *path, *str = NULL; int w; my_bool b = true; PBJNX bxp; PBVAL jsp, jvp; PGLOBAL g = (PGLOBAL)initid->ptr; -//PGLOBAL gb = GetMemPtr(g, args, 0); - PGLOBAL gb = g; if (g->Alchecked) { str = (char*)g->Activityp; @@ -3760,21 +3987,14 @@ static char *bson_handle_item(UDF_INIT *initid, UDF_ARGS *args, char *result, } else { BJNX bnx(g); - jvp = bnx.MakeValue(g, args, 0); - - if ((p = bnx.GetString(jvp))) { - if (!(jsp = bnx.ParseJson(g, p, strlen(p)))) { - throw 2; - } // endif jsp - - } else - jsp = jvp; + jsp = bnx.MakeValue(args, 0, true); if (g->Mrr) { // First argument is a constant g->Xchk = jsp; JsonMemSave(g); } // endif Mrr - } // endelse CheckMemory + + } // endif CheckMemory } else jsp = (PBVAL)g->Xchk; @@ -3782,7 +4002,7 @@ static char *bson_handle_item(UDF_INIT *initid, UDF_ARGS *args, char *result, bxp = new(g)BJNX(g, jsp, TYPE_STRING, initid->max_length, 0, true); for (uint i = 1; i + 1 < args->arg_count; i += 2) { - jvp = bxp->MakeValue(gb, args, i); + jvp = bxp->MakeValue(args, i); path = MakePSZ(g, args, i + 1); if (bxp->SetJpath(g, path, false)) { @@ -3796,13 +4016,16 @@ static char *bson_handle_item(UDF_INIT *initid, UDF_ARGS *args, char *result, b = (w == 1) ? b : !b; } // endif w - if (b && bxp->WriteValue(gb, jvp)) + if (b && bxp->WriteValue(g, jvp)) { PUSH_WARNING(g->Message); + continue; + } // endif SetJpath + bxp->SetChanged(true); } // endfor i - // In case of error or file, return unchanged argument - if (!(str = MakeResult(g, args, jsp, INT_MAX32))) + // In case of error or file, return unchanged argument + if (!(str = bxp->MakeResult(args, jsp, INT_MAX32))) str = MakePSZ(g, args, 0); if (g->N) @@ -3866,7 +4089,7 @@ my_bool bson_set_item_init(UDF_INIT *initid, UDF_ARGS *args, char *message) g->N = (initid->const_item) ? 1 : 0; // This is to avoid double execution when using prepared statements - if (IsJson(args, 0) > 1) + if (IsArgJson(args, 0) > 1) initid->const_item = 0; g->Alchecked = 0; @@ -4083,24 +4306,25 @@ char *bfile_make(UDF_INIT *initid, UDF_ARGS *args, char *result, } else if (initid->const_item) g->N = 1; -// if ((n = IsArgJson(args, 0)) == 3) { + if ((n = IsArgJson(args, 0)) == 3) { // Get default file name and pretty -// PBSON bsp = (PBSON)args->args[0]; + PBSON bsp = (PBSON)args->args[0]; -// fn = bsp->Filename; -// pretty = bsp->Pretty; -// } else - if ((n = IsArgJson(args, 0)) == 2) + fn = bsp->Filename; + pretty = bsp->Pretty; + } else if ((n = IsArgJson(args, 0)) == 2) fn = args->args[0]; if (!g->Xchk) { if (CheckMemory(g, initid, args, 1, true)) { PUSH_WARNING("CheckMemory error"); goto fin; - } else - jvp = bnx.MakeValue(g, args, 0); + } else + bnx.Reset(); - if ((p = bnx.GetString(jvp))) { + jvp = bnx.MakeValue(args, 0); + + if (!n && (p = bnx.GetString(jvp))) { if (!strchr("[{ \t\r\n", *p)) { // Is this a file name? if (!(p = GetJsonFile(g, p))) { @@ -4345,4 +4569,1392 @@ void bfile_bjson_deinit(UDF_INIT* initid) { JsonFreeMem((PGLOBAL)initid->ptr); } // end of bfile_bjson_deinit +/*********************************************************************************/ +/* Serialize a Json document. . */ +/*********************************************************************************/ +my_bool bson_serialize_init(UDF_INIT *initid, UDF_ARGS *args, char *message) +{ + unsigned long reslen, memlen; + + if (args->args[0] && IsArgJson(args, 0) != 3) { + strcpy(message, "Argument must be a Jbin tree"); + return true; + } else + CalcLen(args, false, reslen, memlen); + + return JsonInit(initid, args, message, false, reslen, memlen); +} // end of bson_serialize_init + +char *bson_serialize(UDF_INIT *initid, UDF_ARGS *args, char *result, + unsigned long *res_length, char *, char *error) +{ + char *str; + PGLOBAL g = (PGLOBAL)initid->ptr; + + if (!g->Xchk) { + if (IsArgJson(args, 0) == 3) { + PBSON bsp = (PBSON)args->args[0]; + BJNX bnx(bsp->G); + PBVAL bvp = (args->arg_count == 1) ? (PBVAL)bsp->Jsp : (PBVAL)bsp->Top; + + if (!(str = bnx.Serialize(g, bvp, bsp->Filename, bsp->Pretty))) + str = strcpy(result, g->Message); + + // Keep result of constant function + g->Xchk = (initid->const_item) ? str : NULL; + } else { + // *error = 1; + str = strcpy(result, "Argument is not a Jbin tree"); + } // endif + + } else + str = (char*)g->Xchk; + + *res_length = strlen(str); + return str; +} // end of bson_serialize + +void bson_serialize_deinit(UDF_INIT* initid) +{ + JsonFreeMem((PGLOBAL)initid->ptr); +} // end of bson_serialize_deinit + +/*********************************************************************************/ +/* Make and return a binary Json array containing all the parameters. */ +/*********************************************************************************/ +my_bool bbin_make_array_init(UDF_INIT *initid, UDF_ARGS *args, char *message) +{ + unsigned long reslen, memlen; + + CalcLen(args, false, reslen, memlen); + return JsonInit(initid, args, message, true, reslen, memlen); +} // end of bbin_make_array_init + +char *bbin_make_array(UDF_INIT *initid, UDF_ARGS *args, char *result, + unsigned long *res_length, char *is_null, char *error) +{ + PGLOBAL g = (PGLOBAL)initid->ptr; + PBSON bsp = NULL; + + if (!g->Xchk) { + if (!CheckMemory(g, initid, args, args->arg_count, false)) { + BJNX bnx(g); + PBVAL arp; + + if ((arp = bnx.NewVal(TYPE_JAR))) { + for (uint i = 0; i < args->arg_count; i++) + bnx.AddArrayValue(arp, bnx.MakeValue(args, i)); + + if ((bsp = BbinAlloc(g, initid->max_length, arp))) { + strcat(bsp->Msg, " array"); + + // Keep result of constant function + g->Xchk = (initid->const_item) ? bsp : NULL; + } // endif bsp + + } // endif arp + + } // endif CheckMemory + + } else + bsp = (PBSON)g->Xchk; + + if (!bsp) { + *is_null = 1; + *error = 1; + *res_length = 0; + } else + *res_length = sizeof(BSON); + + return (char*)bsp; +} // end of bbin_make_array + +void bbin_make_array_deinit(UDF_INIT* initid) +{ + JsonFreeMem((PGLOBAL)initid->ptr); +} // end of bbin_make_array_deinit + +/*********************************************************************************/ +/* Add one value to a Json array. */ +/*********************************************************************************/ +my_bool bbin_array_add_init(UDF_INIT *initid, UDF_ARGS *args, char *message) +{ + unsigned long reslen, memlen; + + if (args->arg_count < 2) { + strcpy(message, "This function must have at least 2 arguments"); + return true; + } else + CalcLen(args, false, reslen, memlen, true); + + if (!JsonInit(initid, args, message, true, reslen, memlen)) { + PGLOBAL g = (PGLOBAL)initid->ptr; + + // This is a constant function + g->N = (initid->const_item) ? 1 : 0; + + // This is to avoid double execution when using prepared statements + if (IsArgJson(args, 0) > 1) + initid->const_item = 0; + + return false; + } else + return true; + +} // end of bbin_array_add_init + +char *bbin_array_add(UDF_INIT *initid, UDF_ARGS *args, char *result, + unsigned long *res_length, char *is_null, char *error) +{ + PGLOBAL g = (PGLOBAL)initid->ptr; + PBSON bsp = NULL; + + if (g->Xchk) { + // This constant function was recalled + bsp = (PBSON)g->Xchk; + *res_length = sizeof(BSON); + return (char*)bsp; + } else if (!CheckMemory(g, initid, args, 2, false, false, true)) { + uint n = 2; + int* x = GetIntArgPtr(g, args, n); + BJNX bnx(g, NULL, TYPE_STRING); + PBVAL jarp, top, jvp = NULL; + PBVAL jsp = bnx.MakeValue(args, 0, true, &top); + + if (bnx.CheckPath(g, args, jsp, jvp, 2)) + PUSH_WARNING(g->Message); + else if (jvp && jvp->Type != TYPE_JAR) { + if ((jarp = bnx.NewVal(TYPE_JAR))) { + bnx.AddArrayValue(jarp, jvp); + + if (!top) + top = jarp; + + } // endif jarp + + } else + jarp = jvp; + + if (jarp) { + bnx.AddArrayValue(jarp, bnx.MakeValue(args, 1), x); + bnx.SetChanged(true); + bsp = bnx.MakeBinResult(g, args, top, initid->max_length); + + if (initid->const_item) + // Keep result of constant function + g->Xchk = bsp; + + } else + PUSH_WARNING(g->Message); + + } // endif CheckMemory + + if (!bsp) { + *res_length = 0; + *is_null = 1; + *error = 1; + } else + *res_length = sizeof(BSON); + + return (char*)bsp; +} // end of bbin_array_add + +void bbin_array_add_deinit(UDF_INIT* initid) +{ + JsonFreeMem((PGLOBAL)initid->ptr); +} // end of bbin_array_add_deinit + +/*********************************************************************************/ +/* Add one or several values to a Bson array. */ +/*********************************************************************************/ +my_bool bbin_array_add_values_init(UDF_INIT* initid, UDF_ARGS* args, char* message) +{ + return bson_array_add_values_init(initid, args, message); +} // end of bbin_array_add_values_init + +char* bbin_array_add_values(UDF_INIT* initid, UDF_ARGS* args, char* result, + unsigned long* res_length, char* is_null, char* error) +{ + PGLOBAL g = (PGLOBAL)initid->ptr; + PBSON bsp = NULL; + + if (!g->Xchk) { + if (!CheckMemory(g, initid, args, args->arg_count, true)) { + uint i = 0; + BJNX bnx(g); + PBVAL arp, top, jvp = NULL; + PBVAL bvp = bnx.MakeValue(args, 0, true, &top); + + if (bvp->Type == TYPE_JAR) { + arp = bvp; + i = 1; + } else // First argument is not an array + arp = bnx.NewVal(TYPE_JAR); + + for (; i < args->arg_count; i++) + bnx.AddArrayValue(arp, bnx.MakeValue(args, i)); + + bnx.SetChanged(true); + bsp = bnx.MakeBinResult(g, args, top, initid->max_length); + } // endif CheckMemory + + // Keep result of constant function + g->Xchk = (g->N) ? bsp : NULL; + } else + bsp = (PBSON)g->Xchk; + + if (!bsp) { + *res_length = 0; + *is_null = 1; + *error = 1; + } else + *res_length = sizeof(BSON); + + return (char*)bsp; +} // end of bbin_array_add_values + +void bbin_array_add_values_deinit(UDF_INIT* initid) { + JsonFreeMem((PGLOBAL)initid->ptr); +} // end of bbin_array_add_values_deinit + +/*********************************************************************************/ +/* Make a Json array from values coming from rows. */ +/*********************************************************************************/ +my_bool bbin_array_grp_init(UDF_INIT *initid, UDF_ARGS *args, char *message) +{ + return bson_array_grp_init(initid, args, message); +} // end of bbin_array_grp_init + +void bbin_array_grp_clear(UDF_INIT *initid, char *a, char *b) +{ + bson_array_grp_clear(initid, a, b); +} // end of bbin_array_grp_clear + +void bbin_array_grp_add(UDF_INIT *initid, UDF_ARGS *args, char *a, char *b) +{ + bson_array_grp_add(initid, args, a, b); +} // end of bbin_array_grp_add + +char *bbin_array_grp(UDF_INIT *initid, UDF_ARGS *, char *result, + unsigned long *res_length, char *is_null, char *error) +{ + PBSON bsp = NULL; + PGLOBAL g = (PGLOBAL)initid->ptr; + PBVAL arp = (PBVAL)g->Activityp; + + if (g->N < 0) + PUSH_WARNING("Result truncated to json_grp_size values"); + + if (arp) + if ((bsp = BbinAlloc(g, initid->max_length, arp))) + strcat(bsp->Msg, " array"); + + if (!bsp) { + *res_length = 0; + *is_null = 1; + *error = 1; + } else + *res_length = sizeof(BSON); + + return (char*)bsp; +} // end of bbin_array_grp + +void bbin_array_grp_deinit(UDF_INIT* initid) +{ + JsonFreeMem((PGLOBAL)initid->ptr); +} // end of bbin_array_grp_deinit + +/*********************************************************************************/ +/* Make a Json object from values coming from rows. */ +/*********************************************************************************/ +my_bool bbin_object_grp_init(UDF_INIT *initid, UDF_ARGS *args, char *message) +{ + return bson_object_grp_init(initid, args, message); +} // end of bbin_object_grp_init + +void bbin_object_grp_clear(UDF_INIT *initid, char *a, char *b) +{ + bson_object_grp_clear(initid, a, b); +} // end of bbin_object_grp_clear + +void bbin_object_grp_add(UDF_INIT *initid, UDF_ARGS *args, char *a, char *b) +{ + bson_object_grp_add(initid, args, a, b); +} // end of bbin_object_grp_add + +char *bbin_object_grp(UDF_INIT *initid, UDF_ARGS *, char *result, + unsigned long *res_length, char *is_null, char *error) +{ + PBSON bsp = NULL; + PGLOBAL g = (PGLOBAL)initid->ptr; + PBVAL bop = (PBVAL)g->Activityp; + + if (g->N < 0) + PUSH_WARNING("Result truncated to json_grp_size values"); + + if (bop) + if ((bsp = BbinAlloc(g, initid->max_length, bop))) + strcat(bsp->Msg, " object"); + + if (!bsp) { + *res_length = 0; + *is_null = 1; + *error = 1; + } else + *res_length = sizeof(BSON); + + return (char*)bsp; +} // end of bbin_object_grp + +void bbin_object_grp_deinit(UDF_INIT* initid) +{ + JsonFreeMem((PGLOBAL)initid->ptr); +} // end of bbin_object_grp_deinit + +/*********************************************************************************/ +/* Make a Json Object containing all the parameters. */ +/*********************************************************************************/ +my_bool bbin_make_object_init(UDF_INIT *initid, UDF_ARGS *args, char *message) +{ + unsigned long reslen, memlen; + + CalcLen(args, true, reslen, memlen); + return JsonInit(initid, args, message, false, reslen, memlen); +} // end of bbin_make_object_init + +char *bbin_make_object(UDF_INIT *initid, UDF_ARGS *args, char *result, + unsigned long *res_length, char *is_null, char *error) +{ + PGLOBAL g = (PGLOBAL)initid->ptr; + PBSON bsp = (PBSON)g->Xchk; + + if (!bsp) { + if (!CheckMemory(g, initid, args, args->arg_count, true)) { + BJNX bnx(g); + PBVAL objp; + + if ((objp = bnx.NewVal(TYPE_JOB))) { + for (uint i = 0; i < args->arg_count; i++) + bnx.SetKeyValue(objp, bnx.MakeValue(args, i), bnx.MakeKey(args, i)); + + if ((bsp = BbinAlloc(g, initid->max_length, objp))) { + strcat(bsp->Msg, " object"); + + // Keep result of constant function + g->Xchk = (initid->const_item) ? bsp : NULL; + } // endif bsp + + } // endif objp + + } // endif CheckMemory + + } // endif Xchk + + if (!bsp) { + *is_null = 1; + *error = 1; + *res_length = 0; + } else + *res_length = sizeof(BSON); + + return (char*)bsp; +} // end of bbin_make_object + +void bbin_make_object_deinit(UDF_INIT* initid) +{ + JsonFreeMem((PGLOBAL)initid->ptr); +} // end of bbin_make_object_deinit + +/*********************************************************************************/ +/* Make a Json Object containing all not null parameters. */ +/*********************************************************************************/ +my_bool bbin_object_nonull_init(UDF_INIT *initid, UDF_ARGS *args, char *message) +{ + unsigned long reslen, memlen; + + CalcLen(args, true, reslen, memlen); + return JsonInit(initid, args, message, true, reslen, memlen); +} // end of bbin_object_nonull_init + +char *bbin_object_nonull(UDF_INIT *initid, UDF_ARGS *args, char *result, + unsigned long *res_length, char *is_null, char *error) +{ + PGLOBAL g = (PGLOBAL)initid->ptr; + PBSON bsp = (PBSON)g->Xchk; + + if (!bsp) { + if (!CheckMemory(g, initid, args, args->arg_count, false, true)) { + BJNX bnx(g); + PBVAL jvp, objp; + + if ((objp = bnx.NewVal(TYPE_JOB))) { + for (uint i = 0; i < args->arg_count; i++) + if (!bnx.IsValueNull(jvp = bnx.MakeValue(args, i))) + bnx.SetKeyValue(objp, jvp, bnx.MakeKey(args, i)); + + if ((bsp = BbinAlloc(g, initid->max_length, objp))) { + strcat(bsp->Msg, " object"); + + // Keep result of constant function + g->Xchk = (initid->const_item) ? bsp : NULL; + } // endif bsp + + } // endif objp + + } // endif CheckMemory + + } // endif Xchk + + if (!bsp) { + *is_null = 1; + *error = 1; + *res_length = 0; + } else + *res_length = sizeof(BSON); + + return (char*)bsp; +} // end of bbin_object_nonull + +void bbin_object_nonull_deinit(UDF_INIT* initid) +{ + JsonFreeMem((PGLOBAL)initid->ptr); +} // end of bbin_object_nonull_deinit + +/*********************************************************************************/ +/* Make a Json Object containing all the key/value parameters. */ +/*********************************************************************************/ +my_bool bbin_object_key_init(UDF_INIT *initid, UDF_ARGS *args, char *message) +{ + unsigned long reslen, memlen; + + if (args->arg_count % 2) { + strcpy(message, "This function must have an even number of arguments"); + return true; + } // endif arg_count + + CalcLen(args, true, reslen, memlen); + return JsonInit(initid, args, message, true, reslen, memlen); +} // end of bbin_object_key_init + +char *bbin_object_key(UDF_INIT *initid, UDF_ARGS *args, char *result, + unsigned long *res_length, char *is_null, char *error) +{ + PGLOBAL g = (PGLOBAL)initid->ptr; + PBSON bsp = (PBSON)g->Xchk; + + if (!bsp) { + if (!CheckMemory(g, initid, args, args->arg_count, false, true)) { + BJNX bnx(g); + PBVAL objp; + + if ((objp = bnx.NewVal(TYPE_JOB))) { + for (uint i = 0; i < args->arg_count; i += 2) + bnx.SetKeyValue(objp, bnx.MakeValue(args, i + 1), MakePSZ(g, args, i)); + + if ((bsp = BbinAlloc(g, initid->max_length, objp))) { + strcat(bsp->Msg, " object"); + + // Keep result of constant function + g->Xchk = (initid->const_item) ? bsp : NULL; + } // endif bsp + + } // endif objp + + } // endif CheckMemory + + } // endif Xchk + + if (!bsp) { + *is_null = 1; + *error = 1; + *res_length = 0; + } else + *res_length = sizeof(BSON); + + return (char*)bsp; +} // end of bbin_object_key + +void bbin_object_key_deinit(UDF_INIT* initid) +{ + JsonFreeMem((PGLOBAL)initid->ptr); +} // end of bbin_object_key_deinit + +/*********************************************************************************/ +/* Add or replace a value in a Json Object. */ +/*********************************************************************************/ +my_bool bbin_object_add_init(UDF_INIT *initid, UDF_ARGS *args, char *message) +{ + unsigned long reslen, memlen; + + if (args->arg_count < 2) { + strcpy(message, "This function must have at least 2 arguments"); + return true; + } else if (!IsArgJson(args, 0)) { + strcpy(message, "First argument must be a json item"); + return true; + } else + CalcLen(args, true, reslen, memlen, true); + + return JsonInit(initid, args, message, true, reslen, memlen); +} // end of bbin_object_add_init + +char *bbin_object_add(UDF_INIT *initid, UDF_ARGS *args, char *result, + unsigned long *res_length, char *is_null, char *error) +{ + PGLOBAL g = (PGLOBAL)initid->ptr; + PBSON bsp = NULL; + + if (g->Xchk) { + // This constant function was recalled + bsp = (PBSON)g->Xchk; + *res_length = sizeof(BSON); + return (char*)bsp; + } else if (!CheckMemory(g, initid, args, 2, false, true, true)) { + PSZ key; + BJNX bnx(g, NULL, TYPE_STRING); + PBVAL top; + PBVAL jobp = bnx.MakeValue(args, 0, true, &top); + PBVAL jvp = jobp; + + if (bnx.CheckPath(g, args, jvp, jobp, 2)) + PUSH_WARNING(g->Message); + else if (jobp && jobp->Type == TYPE_JOB) { + jvp = bnx.MakeValue(args, 1); + key = bnx.MakeKey(args, 1); + bnx.SetKeyValue(jobp, jvp, key); + bnx.SetChanged(true); + } else { + PUSH_WARNING("First argument target is not an object"); + // if (g->Mrr) *error = 1; (only if no path) + } // endif jobp + + // In case of error unchanged argument will be returned + bsp = bnx.MakeBinResult(g, args, top, initid->max_length); + + if (initid->const_item) + // Keep result of constant function + g->Xchk = bsp; + + } // endif CheckMemory + + if (!bsp) { + *is_null = 1; + *error = 1; + *res_length = 0; + } else + *res_length = sizeof(BSON); + + return (char*)bsp; +} // end of bbin_object_add + +void bbin_object_add_deinit(UDF_INIT* initid) +{ + JsonFreeMem((PGLOBAL)initid->ptr); +} // end of bbin_object_add_deinit + +/*********************************************************************************/ +/* Delete a value from a Json array. */ +/*********************************************************************************/ +my_bool bbin_array_delete_init(UDF_INIT *initid, UDF_ARGS *args, char *message) +{ + return bson_array_delete_init(initid, args, message); +} // end of bbin_array_delete_init + +char *bbin_array_delete(UDF_INIT *initid, UDF_ARGS *args, char *result, + unsigned long *res_length, char *is_null, char *error) +{ + PGLOBAL g = (PGLOBAL)initid->ptr; + PBSON bsp = NULL; + + if (g->Xchk) { + // This constant function was recalled + bsp = (PBSON)g->Xchk; + } else if (!CheckMemory(g, initid, args, 1, false, false, true)) { + int* x; + uint n = 1; + BJNX bnx(g); + PBVAL arp, top; + PBVAL jvp = bnx.MakeValue(args, 0, true, &top); + + if (!(x = GetIntArgPtr(g, args, n))) + PUSH_WARNING("Missing or null array index"); + else if (bnx.CheckPath(g, args, jvp, arp, 1)) + PUSH_WARNING(g->Message); + else if (arp && arp->Type == TYPE_JAR) { + bnx.SetChanged(bnx.DeleteValue(arp, *x)); + bsp = bnx.MakeBinResult(g, args, top, initid->max_length); + } else { + PUSH_WARNING("First argument target is not an array"); + // if (g->Mrr) *error = 1; + } // endif jvp + + if (g->N) + // Keep result of constant function + g->Xchk = bsp; + + } // endif CheckMemory + + if (!bsp) { + *is_null = 1; + *error = 1; + *res_length = 0; + } else + *res_length = sizeof(BSON); + + return (char*)bsp; +} // end of bbin_array_delete + +void bbin_array_delete_deinit(UDF_INIT* initid) +{ + JsonFreeMem((PGLOBAL)initid->ptr); +} // end of bbin_array_delete_deinit + +/*********************************************************************************/ +/* Delete a value from a Json object. */ +/*********************************************************************************/ +my_bool bbin_object_delete_init(UDF_INIT *initid, UDF_ARGS *args, char *message) +{ + unsigned long reslen, memlen; + + if (args->arg_count < 2) { + strcpy(message, "This function must have 2 or 3 arguments"); + return true; + } else if (!IsArgJson(args, 0)) { + strcpy(message, "First argument must be a json item"); + return true; + } else if (args->arg_type[1] != STRING_RESULT) { + strcpy(message, "Second argument must be a key string"); + return true; + } else + CalcLen(args, true, reslen, memlen, true); + + return JsonInit(initid, args, message, true, reslen, memlen); +} // end of bbin_object_delete_init + +char *bbin_object_delete(UDF_INIT *initid, UDF_ARGS *args, char *result, + unsigned long *res_length, char *is_null, char *error) +{ + PGLOBAL g = (PGLOBAL)initid->ptr; + PBSON bsp = NULL; + + if (g->Xchk) { + // This constant function was recalled + bsp = (PBSON)g->Xchk; + *res_length = sizeof(BSON); + return (char*)bsp; + } else if (!CheckMemory(g, initid, args, 1, false, true, true)) { + PCSZ key; + BJNX bnx(g, NULL, TYPE_STRING); + PBVAL top; + PBVAL jobp = bnx.MakeValue(args, 0, true, &top); + + if (bnx.CheckPath(g, args, top, jobp, 2)) + PUSH_WARNING(g->Message); + else if (jobp && jobp->Type == TYPE_JOB) { + key = bnx.MakeKey(args, 1); + bnx.SetChanged(bnx.DeleteKey(jobp, key)); + } else { + PUSH_WARNING("First argument target is not an object"); + // if (g->Mrr) *error = 1; (only if no path) + } // endif jvp + + // In case of error unchanged argument will be returned + bsp = bnx.MakeBinResult(g, args, top, initid->max_length); + + if (initid->const_item) + // Keep result of constant function + g->Xchk = bsp; + + } // endif CheckMemory + + if (!bsp) { + *is_null = 1; + *error = 1; + *res_length = 0; + } else + *res_length = sizeof(BSON); + + return (char*)bsp; +} // end of bbin_object_delete + +void bbin_object_delete_deinit(UDF_INIT* initid) +{ + JsonFreeMem((PGLOBAL)initid->ptr); +} // end of bbin_object_delete_deinit + +/*********************************************************************************/ +/* Returns an array of the Json object keys. */ +/*********************************************************************************/ +my_bool bbin_object_list_init(UDF_INIT *initid, UDF_ARGS *args, char *message) +{ + return bson_object_list_init(initid, args, message); +} // end of bbin_object_list_init + +char *bbin_object_list(UDF_INIT *initid, UDF_ARGS *args, char *result, + unsigned long *res_length, char *is_null, char *error) +{ + PGLOBAL g = (PGLOBAL)initid->ptr; + PBSON bsp = (PBSON)g->Xchk; + + if (!bsp) { + if (!CheckMemory(g, initid, args, 1, true, true)) { + BJNX bnx(g); + PBVAL top, jarp = NULL; + PBVAL jsp = bnx.MakeValue(args, 0, true, &top); + + if (jsp->Type == TYPE_JOB) { + jarp = bnx.GetKeyList(jsp); + } else { + PUSH_WARNING("First argument is not an object"); + if (g->Mrr) *error = 1; + } // endif jsp type + + // In case of error unchanged argument will be returned + bsp = bnx.MakeBinResult(g, args, top, initid->max_length); + bsp->Jsp = (PJSON)jarp; + + } // endif CheckMemory + + // Keep result of constant function + g->Xchk = (initid->const_item) ? bsp : NULL; + } // endif bsp + + if (!bsp) { + *is_null = 1; + *error = 1; + *res_length = 0; + } else + *res_length = sizeof(BSON); + + return (char*)bsp; +} // end of bbin_object_list + +void bbin_object_list_deinit(UDF_INIT* initid) +{ + JsonFreeMem((PGLOBAL)initid->ptr); +} // end of bbin_object_list_deinit + +/*********************************************************************************/ +/* Returns an array of the Json object values. */ +/*********************************************************************************/ +my_bool bbin_object_values_init(UDF_INIT *initid, UDF_ARGS *args, char *message) +{ + return bson_object_values_init(initid, args, message); +} // end of bbin_object_values_init + +char *bbin_object_values(UDF_INIT *initid, UDF_ARGS *args, char *result, + unsigned long *res_length, char *is_null, char *error) +{ + PGLOBAL g = (PGLOBAL)initid->ptr; + PBSON bsp = (PBSON)g->Xchk; + + if (!bsp) { + if (!CheckMemory(g, initid, args, 1, true, true)) { + BJNX bnx(g); + PBVAL top, jarp; + PBVAL jvp = bnx.MakeValue(args, 0, true, &top); + + if (jvp->Type == TYPE_JOB) { + jarp = bnx.GetObjectValList(jvp); + } else { + PUSH_WARNING("First argument is not an object"); + if (g->Mrr) *error = 1; + } // endif jvp + + // In case of error unchanged argument will be returned + bsp = bnx.MakeBinResult(g, args, top, initid->max_length); + bsp->Jsp = (PJSON)jarp; + + } // endif CheckMemory + + if (initid->const_item) { + // Keep result of constant function + g->Xchk = bsp; + } // endif const_item + + } // endif bsp + + if (!bsp) { + *is_null = 1; + *error = 1; + *res_length = 0; + } else + *res_length = sizeof(BSON); + + return (char*)bsp; +} // end of bbin_object_values + +void bbin_object_values_deinit(UDF_INIT* initid) +{ + JsonFreeMem((PGLOBAL)initid->ptr); +} // end of bbin_object_values_deinit + +/*********************************************************************************/ +/* Get a Json item from a Json document. */ +/*********************************************************************************/ +my_bool bbin_get_item_init(UDF_INIT *initid, UDF_ARGS *args, char *message) +{ + return bson_get_item_init(initid, args, message); +} // end of bbin_get_item_init + +char *bbin_get_item(UDF_INIT *initid, UDF_ARGS *args, char *result, + unsigned long *res_length, char *is_null, char *error) +{ + PBSON bsp = NULL; + PGLOBAL g = (PGLOBAL)initid->ptr; + + if (g->Xchk) { + bsp = (PBSON)g->Xchk; + } else if (!CheckMemory(g, initid, args, 1, true, true)) { + char *path = MakePSZ(g, args, 1); + BJNX bnx(g, NULL, TYPE_STRING, initid->max_length); + PBVAL top, jvp = NULL; + PBVAL jsp = bnx.MakeValue(args, 0, true, &top); + + if (bnx.CheckPath(g, args, jsp, jvp, 1)) + PUSH_WARNING(g->Message); + else if (jvp) { + bsp = bnx.MakeBinResult(g, args, top, initid->max_length); + bsp->Jsp = (PJSON)jvp; + + if (initid->const_item) + // Keep result of constant function + g->Xchk = bsp; + + } // endif jvp + + } else + PUSH_WARNING("CheckMemory error"); + + if (!bsp) { + *is_null = 1; + *res_length = 0; + } else + *res_length = sizeof(BSON); + + return (char*)bsp; +} // end of bbin_get_item + +void bbin_get_item_deinit(UDF_INIT* initid) +{ + JsonFreeMem((PGLOBAL)initid->ptr); +} // end of bbin_get_item_deinit + +/*********************************************************************************/ +/* Merge two arrays or objects. */ +/*********************************************************************************/ +my_bool bbin_item_merge_init(UDF_INIT *initid, UDF_ARGS *args, char *message) +{ + return bson_item_merge_init(initid, args, message); +} // end of bbin_item_merge_init + +char *bbin_item_merge(UDF_INIT *initid, UDF_ARGS *args, char *result, + unsigned long *res_length, char *is_null, char *error) +{ + PBSON bsp = NULL; + PGLOBAL g = (PGLOBAL)initid->ptr; + + if (g->Xchk) { + // This constant function was recalled + bsp = (PBSON)g->Xchk; + goto fin; + } // endif Xchk + + if (!CheckMemory(g, initid, args, 2, false, false, true)) { + JTYP type; + BJNX bnx(g); + PBVAL jvp, top = NULL; + PBVAL jsp[2] = {NULL, NULL}; + + for (int i = 0; i < 2; i++) { + if (i) { + jvp = bnx.MakeValue(args, i, true); + + if (jvp->Type != type) { + PUSH_WARNING("Argument types mismatch"); + goto fin; + } // endif type + + } else { + jvp = bnx.MakeValue(args, i, true, &top); + type = (JTYP)jvp->Type; + + if (type != TYPE_JAR && type != TYPE_JOB) { + PUSH_WARNING("First argument is not an array or object"); + goto fin; + } // endif type + + } // endif i + + jsp[i] = jvp; + } // endfor i + + if (type == TYPE_JAR) + bnx.MergeArray(jsp[0], jsp[1]); + else + bnx.MergeObject(jsp[0], jsp[1]); + + bnx.SetChanged(true); + bsp = bnx.MakeBinResult(g, args, top, initid->max_length); + } // endif CheckMemory + + if (g->N) + // Keep result of constant function + g->Xchk = bsp; + +fin: + if (!bsp) { + *res_length = 0; + *error = 1; + *is_null = 1; + } else + *res_length = sizeof(BSON); + + return (char*)bsp; +} // end of bbin_item_merge + +void bbin_item_merge_deinit(UDF_INIT* initid) +{ + JsonFreeMem((PGLOBAL)initid->ptr); +} // end of bbin_item_merge_deinit + +/*********************************************************************************/ +/* This function is used by the jbin_set/insert/update_item functions. */ +/*********************************************************************************/ +static char *bbin_handle_item(UDF_INIT *initid, UDF_ARGS *args, char *result, + unsigned long *res_length, char *is_null, char *error) +{ + char *path; + int w; + my_bool b = true; + PBJNX bxp; + PBVAL jsp, jvp, top; + PBSON bsp = NULL; + PGLOBAL g = (PGLOBAL)initid->ptr; + + if (g->Alchecked) { + bsp = (PBSON)g->Activityp; + goto fin; + } else if (g->N) + g->Alchecked = 1; + + if (!strcmp(result, "$set")) + w = 0; + else if (!strcmp(result, "$insert")) + w = 1; + else if (!strcmp(result, "$update")) + w = 2; + else { + PUSH_WARNING("Logical error, please contact CONNECT developer"); + goto fin; + } // endelse + + try { + if (!g->Xchk) { + if (CheckMemory(g, initid, args, 1, true, false, true)) { + throw 1; + } else { + BJNX bnx(g); + + jsp = bnx.MakeValue(args, 0, true, &top); + + if (g->Mrr) { // First argument is a constant + g->Xchk = jsp; + g->More = (size_t)top; + JsonMemSave(g); + } // endif Mrr + + } // endif CheckMemory + + } else { + jsp = (PBVAL)g->Xchk; + top = (PBVAL)g->More; + } // endif Xchk + + bxp = new(g)BJNX(g, jsp, TYPE_STRING, initid->max_length, 0, true); + + for (uint i = 1; i + 1 < args->arg_count; i += 2) { + jvp = bxp->MakeValue(args, i); + path = MakePSZ(g, args, i + 1); + + if (bxp->SetJpath(g, path, false)) + throw 2; + + if (w) { + bxp->ReadValue(g); + b = bxp->GetValue()->IsNull(); + b = (w == 1) ? b : !b; + } // endif w + + if (b && bxp->WriteValue(g, jvp)) + throw 3; + + bxp->SetChanged(true); + } // endfor i + + if (!(bsp = bxp->MakeBinResult(g, args, top, initid->max_length))) + throw 4; + + if (g->N) + // Keep result of constant function + g->Activityp = (PACTIVITY)bsp; + + } catch (int n) { + if (trace(1)) + htrc("Exception %d: %s\n", n, g->Message); + + PUSH_WARNING(g->Message); + } catch (const char *msg) { + strcpy(g->Message, msg); + PUSH_WARNING(g->Message); + } // end catch + +fin: + if (!bsp) { + *is_null = 1; + *res_length = 0; + } else + *res_length = sizeof(BSON); + + return (char*)bsp; +} // end of bbin_handle_item + +/*********************************************************************************/ +/* Set Json items of a Json document according to path. */ +/*********************************************************************************/ +my_bool bbin_set_item_init(UDF_INIT *initid, UDF_ARGS *args, char *message) +{ + return bson_set_item_init(initid, args, message); +} // end of bbin_set_item_init + +char *bbin_set_item(UDF_INIT *initid, UDF_ARGS *args, char *result, + unsigned long *res_length, char *is_null, char *p) +{ + strcpy(result, "$set"); + return bbin_handle_item(initid, args, result, res_length, is_null, p); +} // end of bbin_set_item + +void bbin_set_item_deinit(UDF_INIT* initid) +{ + JsonFreeMem((PGLOBAL)initid->ptr); +} // end of bbin_set_item_deinit + +/*********************************************************************************/ +/* Insert Json items of a Json document according to path. */ +/*********************************************************************************/ +my_bool bbin_insert_item_init(UDF_INIT *initid, UDF_ARGS *args, char *message) +{ + return bson_set_item_init(initid, args, message); +} // end of bbin_insert_item_init + +char *bbin_insert_item(UDF_INIT *initid, UDF_ARGS *args, char *result, + unsigned long *res_length, char *is_null, char *p) +{ + strcpy(result, "$insert"); + return bbin_handle_item(initid, args, result, res_length, is_null, p); +} // end of bbin_insert_item + +void bbin_insert_item_deinit(UDF_INIT* initid) +{ + JsonFreeMem((PGLOBAL)initid->ptr); +} // end of bbin_insert_item_deinit + +/*********************************************************************************/ +/* Update Json items of a Json document according to path. */ +/*********************************************************************************/ +my_bool bbin_update_item_init(UDF_INIT *initid, UDF_ARGS *args, char *message) +{ + return bson_set_item_init(initid, args, message); +} // end of bbin_update_item_init + +char *bbin_update_item(UDF_INIT *initid, UDF_ARGS *args, char *result, + unsigned long *res_length, char *is_null, char *p) +{ + strcpy(result, "$update"); + return bbin_handle_item(initid, args, result, res_length, is_null, p); +} // end of bbin_update_item + +void bbin_update_item_deinit(UDF_INIT* initid) +{ + JsonFreeMem((PGLOBAL)initid->ptr); +} // end of bbin_update_item_deinit + +/*********************************************************************************/ +/* Delete items from a Json document. */ +/*********************************************************************************/ +my_bool bbin_delete_item_init(UDF_INIT *initid, UDF_ARGS *args, char *message) +{ + return bson_delete_item_init(initid, args, message); +} // end of bbin_delete_item_init + +char *bbin_delete_item(UDF_INIT *initid, UDF_ARGS *args, char *result, + unsigned long *res_length, char *is_null, char *error) +{ + char *path; + PBSON bsp = NULL; + PGLOBAL g = (PGLOBAL)initid->ptr; + + if (g->Xchk) { + // This constant function was recalled + bsp = (PBSON)g->Xchk; + goto fin; + } // endif Xchk + + if (!CheckMemory(g, initid, args, 1, false, false, true)) { + BJNX bnx(g, NULL, TYPE_STRING); + PBVAL top, jar = NULL; + PBVAL jvp = bnx.MakeValue(args, 0, true, &top); + + if (args->arg_count == 1) { + // This should be coming from bbin_locate_all + jar = jvp; // This is the array of paths + jvp = top; // And this is the document + } else if(!bnx.IsJson(jvp)) { + PUSH_WARNING("First argument is not a JSON document"); + goto fin; + } else if (args->arg_count == 2) { + // Check whether this is an array of paths + jar = bnx.MakeValue(args, 1, true); + + if (jar && jar->Type != TYPE_JAR) + jar = NULL; + + } // endif arg_count + + if (jar) { + // Do the deletion in reverse order + for(int i = bnx.GetArraySize(jar) - 1; i >= 0; i--) { + path = bnx.GetString(bnx.GetArrayValue(jar, i)); + + if (bnx.SetJpath(g, path, false)) { + PUSH_WARNING(g->Message); + continue; + } // endif SetJpath + + bnx.SetChanged(bnx.DeleteItem(g, jvp)); + } // endfor i + + } else for (uint i = 1; i < args->arg_count; i++) { + path = MakePSZ(g, args, i); + + if (bnx.SetJpath(g, path, false)) { + PUSH_WARNING(g->Message); + continue; + } // endif SetJpath + + bnx.SetChanged(bnx.DeleteItem(g, jvp)); + } // endfor i + + bsp = bnx.MakeBinResult(g, args, top, initid->max_length); + + if (args->arg_count == 1) + // Here Jsp was not a sub-item of top + bsp->Jsp = (PJSON)top; + + } // endif CheckMemory + + if (g->N) + // Keep result of constant function + g->Xchk = bsp; + +fin: + if (!bsp) { + *is_null = 1; + *error = 1; + *res_length = 0; + } else + *res_length = sizeof(BSON); + + return (char*)bsp; +} // end of bbin_delete_item + +void bbin_delete_item_deinit(UDF_INIT* initid) +{ + JsonFreeMem((PGLOBAL)initid->ptr); +} // end of bbin_delete_item_deinit + +/*********************************************************************************/ +/* Returns a json file as a json binary tree. */ +/*********************************************************************************/ +my_bool bbin_file_init(UDF_INIT *initid, UDF_ARGS *args, char *message) +{ + return bson_file_init(initid, args, message); +} // end of bbin_file_init + +char *bbin_file(UDF_INIT *initid, UDF_ARGS *args, char *result, + unsigned long *res_length, char *is_null, char *error) +{ + char *fn; + int pretty = 3; + size_t len = 0; + PBVAL jsp, jvp = NULL; + PGLOBAL g = (PGLOBAL)initid->ptr; + BJNX bnx(g); + PBSON bsp = (PBSON)g->Xchk; + + if (bsp) + goto fin; + + fn = MakePSZ(g, args, 0); + + for (unsigned int i = 1; i < args->arg_count; i++) + if (args->arg_type[i] == INT_RESULT && *(longlong*)args->args[i] < 4) { + pretty = (int) * (longlong*)args->args[i]; + break; + } // endif type + + // Parse the json file and allocate its tree structure + if (!(jsp = bnx.ParseJsonFile(g, fn, pretty, len))) { + PUSH_WARNING(g->Message); + *error = 1; + goto fin; + } // endif jsp + +// if (pretty == 3) +// PUSH_WARNING("File pretty format cannot be determined"); +// else if (pretty == 3) +// pretty = pty; + + if ((bsp = BbinAlloc(g, len, jsp))) { + strcat(bsp->Msg, " file"); + bsp->Filename = fn; + bsp->Pretty = pretty; + } else { + *error = 1; + goto fin; + } // endif bsp + + // Check whether a path was specified + if (bnx.CheckPath(g, args, jsp, jvp, 1)) { + PUSH_WARNING(g->Message); + bsp = NULL; + goto fin; + } else if (jvp) + bsp->Jsp = (PJSON)jvp; + + if (initid->const_item) + // Keep result of constant function + g->Xchk = bsp; + +fin: + if (!bsp) { + *res_length = 0; + *is_null = 1; + } else + *res_length = sizeof(BSON); + + return (char*)bsp; +} // end of bbin_file + +void bbin_file_deinit(UDF_INIT* initid) +{ + JsonFreeMem((PGLOBAL)initid->ptr); +} // end of bbin_file_deinit + +/*********************************************************************************/ +/* Locate all occurences of a value in a Json tree. */ +/*********************************************************************************/ +my_bool bbin_locate_all_init(UDF_INIT* initid, UDF_ARGS* args, char* message) { + return bson_locate_all_init(initid, args, message); +} // end of bbin_locate_all_init + +char* bbin_locate_all(UDF_INIT* initid, UDF_ARGS* args, char* result, + unsigned long* res_length, char* is_null, char* error) { + char *path = NULL; + int mx = 10; + PBVAL bvp, bvp2; + PGLOBAL g = (PGLOBAL)initid->ptr; + PBSON bsp = NULL; + + if (g->N) { + if (g->Activityp) { + bsp = (PBSON)g->Activityp; + *res_length = sizeof(BSON); + return (char*)bsp; + } else { + *error = 1; + *res_length = 0; + *is_null = 1; + return NULL; + } // endif Activityp + + } else if (initid->const_item) + g->N = 1; + + try { + PBVAL top = NULL; + BJNX bnx(g); + + if (!g->Xchk) { + if (CheckMemory(g, initid, args, 1, true)) { + PUSH_WARNING("CheckMemory error"); + *error = 1; + goto err; + } else + bnx.Reset(); + + bvp = bnx.MakeValue(args, 0, true, &top); + + if (bvp->Type == TYPE_NULL) { + PUSH_WARNING("First argument is not a valid JSON item"); + goto err; + } // endif bvp + + if (g->Mrr) { // First argument is a constant + g->Xchk = bvp; + g->More = (size_t)top; + JsonMemSave(g); + } // endif Mrr + + } else { + bvp = (PBVAL)g->Xchk; + top = (PBVAL)g->More; + } // endif Xchk + + // The item to locate + bvp2 = bnx.MakeValue(args, 1, true); + + if (bvp2->Type == TYPE_NULL) { + PUSH_WARNING("Invalid second argument"); + goto err; + } // endif bvp2 + + if (args->arg_count > 2) + mx = (int)*(long long*)args->args[2]; + + if ((path = bnx.LocateAll(g, bvp, bvp2, mx))) { + bsp = bnx.MakeBinResult(g, args, top, initid->max_length); + bsp->Jsp = (PJSON)bnx.ParseJson(g, path, strlen(path)); + } // endif path + + if (initid->const_item) + // Keep result of constant function + g->Activityp = (PACTIVITY)bsp; + + } catch (int n) { + xtrc(1, "Exception %d: %s\n", n, g->Message); + PUSH_WARNING(g->Message); + *error = 1; + path = NULL; + } catch (const char* msg) { + strcpy(g->Message, msg); + PUSH_WARNING(g->Message); + *error = 1; + path = NULL; + } // end catch + +err: + if (!bsp) { + *res_length = 0; + *is_null = 1; + } else + *res_length = sizeof(BSON); + + return (char*)bsp; +} // end of bbin_locate_all + +void bbin_locate_all_deinit(UDF_INIT* initid) { + JsonFreeMem((PGLOBAL)initid->ptr); +} // end of bbin_locate_all_deinit + diff --git a/storage/connect/bsonudf.h b/storage/connect/bsonudf.h index 251af86a32b..7e743c8a72a 100644 --- a/storage/connect/bsonudf.h +++ b/storage/connect/bsonudf.h @@ -1,7 +1,7 @@ /******************** tabjson H Declares Source Code File (.H) *******************/ /* Name: bsonudf.h Version 1.0 */ /* */ -/* (C) Copyright to the author Olivier BERTRAND 2020 */ +/* (C) Copyright to the author Olivier BERTRAND 2020 - 2021 */ /* */ /* This file contains the BSON UDF function and class declares. */ /*********************************************************************************/ @@ -96,6 +96,7 @@ public: int GetPrecision(void) { return Prec; } PVAL GetValue(void) { return Value; } void SetRow(PBVAL vp) { Row = vp; } + void SetChanged(my_bool b) { Changed = b; } // Methods my_bool SetJpath(PGLOBAL g, char* path, my_bool jb = false); @@ -106,14 +107,16 @@ public: my_bool CheckPath(PGLOBAL g); my_bool CheckPath(PGLOBAL g, UDF_ARGS* args, PBVAL jsp, PBVAL& jvp, int n); my_bool WriteValue(PGLOBAL g, PBVAL jvalp); + my_bool DeleteItem(PGLOBAL g, PBVAL vlp); char *Locate(PGLOBAL g, PBVAL jsp, PBVAL jvp, int k = 1); char *LocateAll(PGLOBAL g, PBVAL jsp, PBVAL jvp, int mx = 10); PSZ MakeKey(UDF_ARGS* args, int i); - PBVAL MakeBinValue(PGLOBAL g, UDF_ARGS* args, uint i); - PBVAL MakeValue(PGLOBAL g, UDF_ARGS* args, uint i, PBVAL* top = NULL); + PBVAL MakeValue(UDF_ARGS* args, uint i, bool b = false, PBVAL* top = NULL); PBVAL MakeTypedValue(PGLOBAL g, UDF_ARGS* args, uint i, JTYP type, PBVAL* top = NULL); PBVAL ParseJsonFile(PGLOBAL g, char* fn, int& pty, size_t& len); + char *MakeResult(UDF_ARGS* args, PBVAL top, uint n = 2); + PBSON MakeBinResult(PGLOBAL g, UDF_ARGS* args, PBVAL top, ulong len, int n = 2); protected: my_bool SetArrayOptions(PGLOBAL g, char* p, int i, PSZ nm); @@ -159,6 +162,7 @@ protected: my_bool Found; // Item found by locate my_bool Wr; // Write mode my_bool Jb; // Must return json item + my_bool Changed; // True when contains was modified }; // end of class BJNX extern "C" { @@ -268,6 +272,10 @@ extern "C" { DllExport char *bson_object_grp(UDF_EXEC_ARGS); DllExport void bson_object_grp_deinit(UDF_INIT*); + DllExport my_bool bson_delete_item_init(UDF_INIT*, UDF_ARGS*, char*); + DllExport char *bson_delete_item(UDF_EXEC_ARGS); + DllExport void bson_delete_item_deinit(UDF_INIT*); + DllExport my_bool bson_set_item_init(UDF_INIT*, UDF_ARGS*, char*); DllExport char *bson_set_item(UDF_EXEC_ARGS); DllExport void bson_set_item_deinit(UDF_INIT*); @@ -295,4 +303,92 @@ extern "C" { DllExport my_bool bfile_bjson_init(UDF_INIT*, UDF_ARGS*, char*); DllExport char* bfile_bjson(UDF_EXEC_ARGS); DllExport void bfile_bjson_deinit(UDF_INIT*); + + DllExport my_bool bson_serialize_init(UDF_INIT*, UDF_ARGS*, char*); + DllExport char *bson_serialize(UDF_EXEC_ARGS); + DllExport void bson_serialize_deinit(UDF_INIT*); + + DllExport my_bool bbin_make_array_init(UDF_INIT*, UDF_ARGS*, char*); + DllExport char* bbin_make_array(UDF_EXEC_ARGS); + DllExport void bbin_make_array_deinit(UDF_INIT*); + + DllExport my_bool bbin_array_add_init(UDF_INIT*, UDF_ARGS*, char*); + DllExport char* bbin_array_add(UDF_EXEC_ARGS); + DllExport void bbin_array_add_deinit(UDF_INIT*); + + DllExport my_bool bbin_array_add_values_init(UDF_INIT*, UDF_ARGS*, char*); + DllExport char* bbin_array_add_values(UDF_EXEC_ARGS); + DllExport void bbin_array_add_values_deinit(UDF_INIT*); + + DllExport my_bool bbin_array_delete_init(UDF_INIT*, UDF_ARGS*, char*); + DllExport char* bbin_array_delete(UDF_EXEC_ARGS); + DllExport void bbin_array_delete_deinit(UDF_INIT*); + + DllExport my_bool bbin_array_grp_init(UDF_INIT*, UDF_ARGS*, char*); + DllExport void bbin_array_grp_clear(UDF_INIT *, char *, char *); + DllExport void bbin_array_grp_add(UDF_INIT *, UDF_ARGS *, char *, char *); + DllExport char *bbin_array_grp(UDF_EXEC_ARGS); + DllExport void bbin_array_grp_deinit(UDF_INIT*); + + DllExport my_bool bbin_object_grp_init(UDF_INIT*, UDF_ARGS*, char*); + DllExport void bbin_object_grp_clear(UDF_INIT *, char *, char *); + DllExport void bbin_object_grp_add(UDF_INIT *, UDF_ARGS *, char *, char *); + DllExport char *bbin_object_grp(UDF_EXEC_ARGS); + DllExport void bbin_object_grp_deinit(UDF_INIT*); + + DllExport my_bool bbin_make_object_init(UDF_INIT*, UDF_ARGS*, char*); + DllExport char* bbin_make_object(UDF_EXEC_ARGS); + DllExport void bbin_make_object_deinit(UDF_INIT*); + + DllExport my_bool bbin_object_nonull_init(UDF_INIT*, UDF_ARGS*, char*); + DllExport char* bbin_object_nonull(UDF_EXEC_ARGS); + DllExport void bbin_object_nonull_deinit(UDF_INIT*); + + DllExport my_bool bbin_object_key_init(UDF_INIT*, UDF_ARGS*, char*); + DllExport char* bbin_object_key(UDF_EXEC_ARGS); + DllExport void bbin_object_key_deinit(UDF_INIT*); + + DllExport my_bool bbin_object_add_init(UDF_INIT*, UDF_ARGS*, char*); + DllExport char *bbin_object_add(UDF_EXEC_ARGS); + DllExport void bbin_object_add_deinit(UDF_INIT*); + + DllExport my_bool bbin_object_delete_init(UDF_INIT*, UDF_ARGS*, char*); + DllExport char *bbin_object_delete(UDF_EXEC_ARGS); + DllExport void bbin_object_delete_deinit(UDF_INIT*); + + DllExport my_bool bbin_object_list_init(UDF_INIT*, UDF_ARGS*, char*); + DllExport char *bbin_object_list(UDF_EXEC_ARGS); + DllExport void bbin_object_list_deinit(UDF_INIT*); + + DllExport my_bool bbin_object_values_init(UDF_INIT*, UDF_ARGS*, char*); + DllExport char* bbin_object_values(UDF_EXEC_ARGS); + DllExport void bbin_object_values_deinit(UDF_INIT*); + + DllExport my_bool bbin_get_item_init(UDF_INIT*, UDF_ARGS*, char*); + DllExport char *bbin_get_item(UDF_EXEC_ARGS); + DllExport void bbin_get_item_deinit(UDF_INIT*); + + DllExport my_bool bbin_set_item_init(UDF_INIT*, UDF_ARGS*, char*); + DllExport char *bbin_set_item(UDF_EXEC_ARGS); + DllExport void bbin_set_item_deinit(UDF_INIT*); + + DllExport my_bool bbin_insert_item_init(UDF_INIT*, UDF_ARGS*, char*); + DllExport char *bbin_insert_item(UDF_EXEC_ARGS); + DllExport void bbin_insert_item_deinit(UDF_INIT*); + + DllExport my_bool bbin_update_item_init(UDF_INIT*, UDF_ARGS*, char*); + DllExport char *bbin_update_item(UDF_EXEC_ARGS); + DllExport void bbin_update_item_deinit(UDF_INIT*); + + DllExport my_bool bbin_delete_item_init(UDF_INIT*, UDF_ARGS*, char*); + DllExport char *bbin_delete_item(UDF_EXEC_ARGS); + DllExport void bbin_delete_item_deinit(UDF_INIT*); + + DllExport my_bool bbin_locate_all_init(UDF_INIT*, UDF_ARGS*, char*); + DllExport char* bbin_locate_all(UDF_EXEC_ARGS); + DllExport void bbin_locate_all_deinit(UDF_INIT*); + + DllExport my_bool bbin_file_init(UDF_INIT*, UDF_ARGS*, char*); + DllExport char *bbin_file(UDF_EXEC_ARGS); + DllExport void bbin_file_deinit(UDF_INIT*); } // extern "C" diff --git a/storage/connect/global.h b/storage/connect/global.h index d4a46e1c862..8774285e54b 100644 --- a/storage/connect/global.h +++ b/storage/connect/global.h @@ -185,7 +185,7 @@ typedef struct _global { /* Global structure */ size_t Sarea_Size; /* Work area size */ PACTIVITY Activityp; char Message[MAX_STR]; /* Message (result, error, trace) */ - ulong More; /* Used by jsonudf */ + size_t More; /* Used by jsonudf */ size_t Saved_Size; /* Saved work area to_free */ bool Createas; /* To pass multi to ext tables */ void *Xchk; /* indexes in create/alter */ diff --git a/storage/connect/json.cpp b/storage/connect/json.cpp index bcbd71b5031..0152a44fffa 100644 --- a/storage/connect/json.cpp +++ b/storage/connect/json.cpp @@ -333,25 +333,30 @@ bool JOUTSTR::WriteChr(const char c) { /***********************************************************************/ /* Escape and Concatenate a string to the Serialize string. */ /***********************************************************************/ -bool JOUTSTR::Escape(const char* s) { - WriteChr('"'); +bool JOUTSTR::Escape(const char* s) +{ + if (s) { + WriteChr('"'); - for (unsigned int i = 0; s[i]; i++) - switch (s[i]) { - case '"': - case '\\': - case '\t': - case '\n': - case '\r': - case '\b': - case '\f': WriteChr('\\'); - // fall through - default: - WriteChr(s[i]); - break; - } // endswitch s[i] + for (unsigned int i = 0; s[i]; i++) + switch (s[i]) { + case '"': + case '\\': + case '\t': + case '\n': + case '\r': + case '\b': + case '\f': WriteChr('\\'); + // fall through + default: + WriteChr(s[i]); + break; + } // endswitch s[i] + + WriteChr('"'); + } else + WriteStr("null"); - WriteChr('"'); return false; } // end of Escape @@ -360,7 +365,8 @@ bool JOUTSTR::Escape(const char* s) { /***********************************************************************/ /* Write a string to the Serialize file. */ /***********************************************************************/ -bool JOUTFILE::WriteStr(const char* s) { +bool JOUTFILE::WriteStr(const char* s) +{ // This is temporary fputs(s, Stream); return false; @@ -369,7 +375,8 @@ bool JOUTFILE::WriteStr(const char* s) { /***********************************************************************/ /* Write a character to the Serialize file. */ /***********************************************************************/ -bool JOUTFILE::WriteChr(const char c) { +bool JOUTFILE::WriteChr(const char c) +{ // This is temporary fputc(c, Stream); return false; @@ -378,25 +385,30 @@ bool JOUTFILE::WriteChr(const char c) { /***********************************************************************/ /* Escape and Concatenate a string to the Serialize string. */ /***********************************************************************/ -bool JOUTFILE::Escape(const char* s) { +bool JOUTFILE::Escape(const char* s) +{ // This is temporary - fputc('"', Stream); + if (s) { + fputc('"', Stream); - for (unsigned int i = 0; s[i]; i++) - switch (s[i]) { - case '"': fputs("\\\"", Stream); break; - case '\\': fputs("\\\\", Stream); break; - case '\t': fputs("\\t", Stream); break; - case '\n': fputs("\\n", Stream); break; - case '\r': fputs("\\r", Stream); break; - case '\b': fputs("\\b", Stream); break; - case '\f': fputs("\\f", Stream); break; - default: - fputc(s[i], Stream); - break; - } // endswitch s[i] + for (unsigned int i = 0; s[i]; i++) + switch (s[i]) { + case '"': fputs("\\\"", Stream); break; + case '\\': fputs("\\\\", Stream); break; + case '\t': fputs("\\t", Stream); break; + case '\n': fputs("\\n", Stream); break; + case '\r': fputs("\\r", Stream); break; + case '\b': fputs("\\b", Stream); break; + case '\f': fputs("\\f", Stream); break; + default: + fputc(s[i], Stream); + break; + } // endswitch s[i] + + fputc('"', Stream); + } else + fputs("null", Stream); - fputc('"', Stream); return false; } // end of Escape @@ -405,7 +417,8 @@ bool JOUTFILE::Escape(const char* s) { /***********************************************************************/ /* Write a string to the Serialize pretty file. */ /***********************************************************************/ -bool JOUTPRT::WriteStr(const char* s) { +bool JOUTPRT::WriteStr(const char* s) +{ // This is temporary if (B) { fputs(EL, Stream); @@ -424,7 +437,8 @@ bool JOUTPRT::WriteStr(const char* s) { /***********************************************************************/ /* Write a character to the Serialize pretty file. */ /***********************************************************************/ -bool JOUTPRT::WriteChr(const char c) { +bool JOUTPRT::WriteChr(const char c) +{ switch (c) { case ':': fputs(": ", Stream); diff --git a/storage/connect/jsonudf.cpp b/storage/connect/jsonudf.cpp index 3d6de7ab3d5..53818cbe00b 100644 --- a/storage/connect/jsonudf.cpp +++ b/storage/connect/jsonudf.cpp @@ -1155,7 +1155,7 @@ PBSON JbinAlloc(PGLOBAL g, UDF_ARGS *args, ulong len, PJSON jsp) /*********************************************************************************/ /* Set the BSON chain as changed. */ /*********************************************************************************/ -static void SetChanged(PBSON bsp) +void SetChanged(PBSON bsp) { if (bsp->Bsp) SetChanged(bsp->Bsp); diff --git a/storage/connect/mysql-test/connect/disabled.def b/storage/connect/mysql-test/connect/disabled.def index e1f6219f89f..5107de7a930 100644 --- a/storage/connect/mysql-test/connect/disabled.def +++ b/storage/connect/mysql-test/connect/disabled.def @@ -16,9 +16,12 @@ jdbc_postgresql : Variable settings depend on machine configuration json_mongo_c : Need MongoDB running and its C Driver installed json_java_2 : Need MongoDB running and its Java Driver installed json_java_3 : Need MongoDB running and its Java Driver installed +bson_mongo_c : Need MongoDB running and its C Driver installed +bson_java_2 : Need MongoDB running and its Java Driver installed +bson_java_3 : Need MongoDB running and its Java Driver installed mongo_c : Need MongoDB running and its C Driver installed mongo_java_2 : Need MongoDB running and its Java Driver installed mongo_java_3 : Need MongoDB running and its Java Driver installed tbl_thread : Bug MDEV-9844,10179,14214 03/01/2018 OB Option THREAD removed -bson : Development +#bson : Development #vcol : Different error code on different versions diff --git a/storage/connect/mysql-test/connect/r/bson.result b/storage/connect/mysql-test/connect/r/bson.result new file mode 100644 index 00000000000..fd15e020aac --- /dev/null +++ b/storage/connect/mysql-test/connect/r/bson.result @@ -0,0 +1,517 @@ +# +# Testing doc samples +# +CREATE TABLE t1 +( +ISBN CHAR(15), +LANG CHAR(2), +SUBJECT CHAR(32), +AUTHOR CHAR(64), +TITLE CHAR(32), +TRANSLATION CHAR(32), +TRANSLATOR CHAR(80), +PUBLISHER CHAR(32), +DATEPUB int(4) +) ENGINE=CONNECT TABLE_TYPE=BSON FILE_NAME='biblio.json'; +SELECT * FROM t1; +ISBN LANG SUBJECT AUTHOR TITLE TRANSLATION TRANSLATOR PUBLISHER DATEPUB +9782212090819 fr applications Jean-Christophe Bernadac, François Knab Construire une application XML NULL NULL Eyrolles Paris 1999 +9782840825685 fr applications William J. Pardi XML en Action adapté de l'anglais par James Guerin Microsoft Press Paris 1999 +DROP TABLE t1; +# +# Testing Jpath. Get the number of authors +# +CREATE TABLE t1 +( +ISBN CHAR(15), +Language CHAR(2) JPATH='$.LANG', +Subject CHAR(32) JPATH='$.SUBJECT', +Authors INT(2) JPATH='$.AUTHOR[#]', +Title CHAR(32) JPATH='$.TITLE', +Translation CHAR(32) JPATH='$.TRANSLATION', +Translator CHAR(80) JPATH='$.TRANSLATOR', +Publisher CHAR(20) JPATH='$.PUBLISHER.NAME', +Location CHAR(16) JPATH='$.PUBLISHER.PLACE', +Year int(4) JPATH='$.DATEPUB' +) +ENGINE=CONNECT TABLE_TYPE=BSON FILE_NAME='biblio.json'; +SELECT * FROM t1; +ISBN Language Subject Authors Title Translation Translator Publisher Location Year +9782212090819 fr applications 2 Construire une application XML NULL NULL Eyrolles Paris 1999 +9782840825685 fr applications 1 XML en Action adapté de l'anglais par James Guerin Microsoft Press Paris 1999 +DROP TABLE t1; +# +# Concatenates the authors +# +CREATE TABLE t1 +( +ISBN CHAR(15), +Language CHAR(2) JPATH='$.LANG', +Subject CHAR(32) JPATH='$.SUBJECT', +AuthorFN CHAR(128) JPATH='$.AUTHOR[" and "].FIRSTNAME', +AuthorLN CHAR(128) JPATH='$.AUTHOR[" and "].LASTNAME', +Title CHAR(32) JPATH='$.TITLE', +Translation CHAR(32) JPATH='$.TRANSLATION', +Translator CHAR(80) JPATH='$.TRANSLATOR', +Publisher CHAR(20) JPATH='$.PUBLISHER.NAME', +Location CHAR(16) JPATH='$.PUBLISHER.PLACE', +Year int(4) JPATH='$.DATEPUB' +) +ENGINE=CONNECT TABLE_TYPE=BSON FILE_NAME='biblio.json'; +SELECT * FROM t1; +ISBN Language Subject AuthorFN AuthorLN Title Translation Translator Publisher Location Year +9782212090819 fr applications Jean-Christophe and François Bernadac and Knab Construire une application XML NULL NULL Eyrolles Paris 1999 +9782840825685 fr applications William J. Pardi XML en Action adapté de l'anglais par James Guerin Microsoft Press Paris 1999 +DROP TABLE t1; +# +# Testing expanding authors +# +CREATE TABLE t1 +( +ISBN CHAR(15), +Language CHAR(2) JPATH='$.LANG', +Subject CHAR(32) JPATH='$.SUBJECT', +AuthorFN CHAR(128) JPATH='$.AUTHOR[*].FIRSTNAME', +AuthorLN CHAR(128) JPATH='$.AUTHOR[*].LASTNAME', +Title CHAR(32) JPATH='$.TITLE', +Translation CHAR(32) JPATH='$.TRANSLATION', +Translator CHAR(80) JPATH='$.TRANSLATOR', +Publisher CHAR(20) JPATH='$.PUBLISHER.NAME', +Location CHAR(16) JPATH='$.PUBLISHER.PLACE', +Year int(4) JPATH='$.DATEPUB' +) +ENGINE=CONNECT TABLE_TYPE=BSON FILE_NAME='biblio.json'; +SELECT * FROM t1; +ISBN Language Subject AuthorFN AuthorLN Title Translation Translator Publisher Location Year +9782212090819 fr applications Jean-Christophe Bernadac Construire une application XML NULL NULL Eyrolles Paris 1999 +9782212090819 fr applications François Knab Construire une application XML NULL NULL Eyrolles Paris 1999 +9782840825685 fr applications William J. Pardi XML en Action adapté de l'anglais par James Guerin Microsoft Press Paris 1999 +UPDATE t1 SET AuthorFN = 'Philippe' WHERE AuthorLN = 'Knab'; +SELECT * FROM t1 WHERE ISBN = '9782212090819'; +ISBN Language Subject AuthorFN AuthorLN Title Translation Translator Publisher Location Year +9782212090819 fr applications Jean-Christophe Bernadac Construire une application XML NULL NULL Eyrolles Paris 1999 +9782212090819 fr applications Philippe Knab Construire une application XML NULL NULL Eyrolles Paris 1999 +# +# To add an author a new table must be created +# +CREATE TABLE t2 ( +FIRSTNAME CHAR(32), +LASTNAME CHAR(32)) +ENGINE=CONNECT TABLE_TYPE=BSON FILE_NAME='biblio.json' OPTION_LIST='Object=$[1].AUTHOR'; +SELECT * FROM t2; +FIRSTNAME LASTNAME +William J. Pardi +INSERT INTO t2 VALUES('Charles','Dickens'); +SELECT * FROM t1; +ISBN Language Subject AuthorFN AuthorLN Title Translation Translator Publisher Location Year +9782212090819 fr applications Jean-Christophe Bernadac Construire une application XML NULL NULL Eyrolles Paris 1999 +9782212090819 fr applications Philippe Knab Construire une application XML NULL NULL Eyrolles Paris 1999 +9782840825685 fr applications William J. Pardi XML en Action adapté de l'anglais par James Guerin Microsoft Press Paris 1999 +9782840825685 fr applications Charles Dickens XML en Action adapté de l'anglais par James Guerin Microsoft Press Paris 1999 +DROP TABLE t1; +DROP TABLE t2; +# +# Check the biblio file has the good format +# +CREATE TABLE t1 +( +line char(255) +) +ENGINE=CONNECT TABLE_TYPE=DOS FILE_NAME='biblio.json'; +SELECT * FROM t1; +line +[ + { + "ISBN": "9782212090819", + "LANG": "fr", + "SUBJECT": "applications", + "AUTHOR": [ + { + "FIRSTNAME": "Jean-Christophe", + "LASTNAME": "Bernadac" + }, + { + "FIRSTNAME": "Philippe", + "LASTNAME": "Knab" + } + ], + "TITLE": "Construire une application XML", + "PUBLISHER": { + "NAME": "Eyrolles", + "PLACE": "Paris" + }, + "DATEPUB": 1999 + }, + { + "ISBN": "9782840825685", + "LANG": "fr", + "SUBJECT": "applications", + "AUTHOR": [ + { + "FIRSTNAME": "William J.", + "LASTNAME": "Pardi" + }, + { + "FIRSTNAME": "Charles", + "LASTNAME": "Dickens" + } + ], + "TITLE": "XML en Action", + "TRANSLATION": "adapté de l'anglais par", + "TRANSLATOR": { + "FIRSTNAME": "James", + "LASTNAME": "Guerin" + }, + "PUBLISHER": { + "NAME": "Microsoft Press", + "PLACE": "Paris" + }, + "DATEPUB": 1999 + } +] +DROP TABLE t1; +# +# Testing a pretty=0 file +# +CREATE TABLE t1 +( +ISBN CHAR(15) NOT NULL, +Language CHAR(2) JPATH='$.LANG', +Subject CHAR(32) JPATH='$.SUBJECT', +AuthorFN CHAR(128) JPATH='$.AUTHOR[*].FIRSTNAME', +AuthorLN CHAR(128) JPATH='$.AUTHOR[*].LASTNAME', +Title CHAR(32) JPATH='$.TITLE', +Translation CHAR(32) JPATH='$.TRANSLATED.PREFIX', +TranslatorFN CHAR(80) JPATH='$.TRANSLATED.TRANSLATOR.FIRSTNAME', +TranslatorLN CHAR(80) JPATH='$.TRANSLATED.TRANSLATOR.LASTNAME', +Publisher CHAR(20) JPATH='$.PUBLISHER.NAME', +Location CHAR(16) JPATH='$.PUBLISHER.PLACE', +Year int(4) JPATH='$.DATEPUB', +INDEX IX(ISBN) +) +ENGINE=CONNECT TABLE_TYPE=BSON FILE_NAME='bib0.json' LRECL=320 OPTION_LIST='Pretty=0'; +SHOW INDEX FROM t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment +t1 1 IX 1 ISBN A NULL NULL NULL XINDEX +SELECT * FROM t1; +ISBN Language Subject AuthorFN AuthorLN Title Translation TranslatorFN TranslatorLN Publisher Location Year +9782212090819 fr applications Jean-Michel Bernadac Construire une application XML NULL NULL NULL Eyrolles Paris 1999 +9782212090819 fr applications François Knab Construire une application XML NULL NULL NULL Eyrolles Paris 1999 +9782840825685 fr applications William J. Pardi XML en Action adapté de l'anglais par James Guerin Microsoft Press Paris 2001 +DESCRIBE SELECT * FROM t1 WHERE ISBN = '9782212090819'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref IX IX 15 const 1 Using where +UPDATE t1 SET AuthorFN = 'Philippe' WHERE ISBN = '9782212090819'; +ERROR HY000: Got error 122 'Cannot write expanded column when Pretty is not 2' from CONNECT +DROP TABLE t1; +# +# A file with 2 arrays +# +CREATE TABLE t1 ( +WHO CHAR(12), +WEEK INT(2) JPATH='$.WEEK[*].NUMBER', +WHAT CHAR(32) JPATH='$.WEEK[].EXPENSE["+"].WHAT', +AMOUNT DOUBLE(8,2) JPATH='$.WEEK[].EXPENSE[+].AMOUNT') +ENGINE=CONNECT TABLE_TYPE=BSON FILE_NAME='expense.json'; +SELECT * FROM t1; +WHO WEEK WHAT AMOUNT +Joe 3 Beer+Food+Food+Car 69.00 +Joe 4 Beer+Beer+Food+Food+Beer 83.00 +Joe 5 Beer+Food 26.00 +Beth 3 Beer 16.00 +Beth 4 Food+Beer 32.00 +Beth 5 Food+Beer 32.00 +Janet 3 Car+Food+Beer 55.00 +Janet 4 Car 17.00 +Janet 5 Beer+Car+Beer+Food 57.00 +DROP TABLE t1; +# +# Now it can be fully expanded +# +CREATE TABLE t1 ( +WHO CHAR(12), +WEEK INT(2) JPATH='$.WEEK[*].NUMBER', +WHAT CHAR(32) JPATH='$.WEEK[*].EXPENSE[*].WHAT', +AMOUNT DOUBLE(8,2) JPATH='$.WEEK[*].EXPENSE[*].AMOUNT') +ENGINE=CONNECT TABLE_TYPE=BSON FILE_NAME='expense.json'; +SELECT * FROM t1; +WHO WEEK WHAT AMOUNT +Joe 3 Beer 18.00 +Joe 3 Food 12.00 +Joe 3 Food 19.00 +Joe 3 Car 20.00 +Joe 4 Beer 19.00 +Joe 4 Beer 16.00 +Joe 4 Food 17.00 +Joe 4 Food 17.00 +Joe 4 Beer 14.00 +Joe 5 Beer 14.00 +Joe 5 Food 12.00 +Beth 3 Beer 16.00 +Beth 4 Food 17.00 +Beth 4 Beer 15.00 +Beth 5 Food 12.00 +Beth 5 Beer 20.00 +Janet 3 Car 19.00 +Janet 3 Food 18.00 +Janet 3 Beer 18.00 +Janet 4 Car 17.00 +Janet 5 Beer 14.00 +Janet 5 Car 12.00 +Janet 5 Beer 19.00 +Janet 5 Food 12.00 +DROP TABLE t1; +# +# A table showing many calculated results +# +CREATE TABLE t1 ( +WHO CHAR(12) NOT NULL, +WEEKS CHAR(12) NOT NULL JPATH='$.WEEK[", "].NUMBER', +SUMS CHAR(64) NOT NULL JPATH='$.WEEK["+"].EXPENSE[+].AMOUNT', +SUM DOUBLE(8,2) NOT NULL JPATH='$.WEEK[+].EXPENSE[+].AMOUNT', +AVGS CHAR(64) NOT NULL JPATH='$.WEEK["+"].EXPENSE[!].AMOUNT', +SUMAVG DOUBLE(8,2) NOT NULL JPATH='$.WEEK[+].EXPENSE[!].AMOUNT', +AVGSUM DOUBLE(8,2) NOT NULL JPATH='$.WEEK[!].EXPENSE[+].AMOUNT', +AVGAVG DOUBLE(8,2) NOT NULL JPATH='$.WEEK[!].EXPENSE[!].AMOUNT', +AVERAGE DOUBLE(8,2) NOT NULL JPATH='$.WEEK[!].EXPENSE[*].AMOUNT') +ENGINE=CONNECT TABLE_TYPE=BSON FILE_NAME='expense.json'; +SELECT * FROM t1; +WHO WEEKS SUMS SUM AVGS SUMAVG AVGSUM AVGAVG AVERAGE +Joe 3, 4, 5 69.00+83.00+26.00 178.00 17.25+16.60+13.00 46.85 59.33 15.62 16.18 +Beth 3, 4, 5 16.00+32.00+32.00 80.00 16.00+16.00+16.00 48.00 26.67 16.00 16.00 +Janet 3, 4, 5 55.00+17.00+57.00 129.00 18.33+17.00+14.25 49.58 43.00 16.53 16.12 +DROP TABLE t1; +# +# Expand expense in 3 one week tables +# +CREATE TABLE t2 ( +WHO CHAR(12), +WEEK INT(2) JPATH='$.WEEK[0].NUMBER', +WHAT CHAR(32) JPATH='$.WEEK[0].EXPENSE[*].WHAT', +AMOUNT DOUBLE(8,2) JPATH='$.WEEK[0].EXPENSE[*].AMOUNT') +ENGINE=CONNECT TABLE_TYPE=BSON FILE_NAME='expense.json'; +SELECT * FROM t2; +WHO WEEK WHAT AMOUNT +Joe 3 Beer 18.00 +Joe 3 Food 12.00 +Joe 3 Food 19.00 +Joe 3 Car 20.00 +Beth 3 Beer 16.00 +Janet 3 Car 19.00 +Janet 3 Food 18.00 +Janet 3 Beer 18.00 +CREATE TABLE t3 ( +WHO CHAR(12), +WEEK INT(2) JPATH='$.WEEK[1].NUMBER', +WHAT CHAR(32) JPATH='$.WEEK[1].EXPENSE[*].WHAT', +AMOUNT DOUBLE(8,2) JPATH='$.WEEK[1].EXPENSE[*].AMOUNT') +ENGINE=CONNECT TABLE_TYPE=BSON FILE_NAME='expense.json'; +SELECT * FROM t3; +WHO WEEK WHAT AMOUNT +Joe 4 Beer 19.00 +Joe 4 Beer 16.00 +Joe 4 Food 17.00 +Joe 4 Food 17.00 +Joe 4 Beer 14.00 +Beth 4 Food 17.00 +Beth 4 Beer 15.00 +Janet 4 Car 17.00 +CREATE TABLE t4 ( +WHO CHAR(12), +WEEK INT(2) JPATH='$.WEEK[2].NUMBER', +WHAT CHAR(32) JPATH='$.WEEK[2].EXPENSE[*].WHAT', +AMOUNT DOUBLE(8,2) JPATH='$.WEEK[2].EXPENSE[*].AMOUNT') +ENGINE=CONNECT TABLE_TYPE=BSON FILE_NAME='expense.json'; +SELECT * FROM t4; +WHO WEEK WHAT AMOUNT +Joe 5 Beer 14.00 +Joe 5 Food 12.00 +Beth 5 Food 12.00 +Beth 5 Beer 20.00 +Janet 5 Beer 14.00 +Janet 5 Car 12.00 +Janet 5 Beer 19.00 +Janet 5 Food 12.00 +# +# The expanded table is made as a TBL table +# +CREATE TABLE t1 ( +WHO CHAR(12), +WEEK INT(2), +WHAT CHAR(32), +AMOUNT DOUBLE(8,2)) +ENGINE=CONNECT TABLE_TYPE=TBL TABLE_LIST='t2,t3,t4'; +SELECT * FROM t1; +WHO WEEK WHAT AMOUNT +Joe 3 Beer 18.00 +Joe 3 Food 12.00 +Joe 3 Food 19.00 +Joe 3 Car 20.00 +Beth 3 Beer 16.00 +Janet 3 Car 19.00 +Janet 3 Food 18.00 +Janet 3 Beer 18.00 +Joe 4 Beer 19.00 +Joe 4 Beer 16.00 +Joe 4 Food 17.00 +Joe 4 Food 17.00 +Joe 4 Beer 14.00 +Beth 4 Food 17.00 +Beth 4 Beer 15.00 +Janet 4 Car 17.00 +Joe 5 Beer 14.00 +Joe 5 Food 12.00 +Beth 5 Food 12.00 +Beth 5 Beer 20.00 +Janet 5 Beer 14.00 +Janet 5 Car 12.00 +Janet 5 Beer 19.00 +Janet 5 Food 12.00 +DROP TABLE t1, t2, t3, t4; +# +# Three partial JSON tables +# +CREATE TABLE t2 ( +WHO CHAR(12), +WEEK INT(2), +WHAT CHAR(32) JPATH='$.EXPENSE[*].WHAT', +AMOUNT DOUBLE(8,2) JPATH='$.EXPENSE.[*].AMOUNT') +ENGINE=CONNECT TABLE_TYPE=BSON FILE_NAME='mulexp3.json'; +SELECT * FROM t2; +WHO WEEK WHAT AMOUNT +Joe 3 Beer 18.00 +Joe 3 Food 12.00 +Joe 3 Food 19.00 +Joe 3 Car 20.00 +Beth 3 Beer 16.00 +Janet 3 Car 19.00 +Janet 3 Food 18.00 +Janet 3 Beer 18.00 +CREATE TABLE t3 ( +WHO CHAR(12), +WEEK INT(2), +WHAT CHAR(32) JPATH='$.EXPENSE[*].WHAT', +AMOUNT DOUBLE(8,2) JPATH='$.EXPENSE.[*].AMOUNT') +ENGINE=CONNECT TABLE_TYPE=BSON FILE_NAME='mulexp4.json'; +SELECT * FROM t3; +WHO WEEK WHAT AMOUNT +Joe 4 Beer 19.00 +Joe 4 Beer 16.00 +Joe 4 Food 17.00 +Joe 4 Food 17.00 +Joe 4 Beer 14.00 +Beth 4 Food 17.00 +Beth 4 Beer 15.00 +Janet 4 Car 17.00 +CREATE TABLE t4 ( +WHO CHAR(12), +WEEK INT(2), +WHAT CHAR(32) JPATH='$.EXPENSE[*].WHAT', +AMOUNT DOUBLE(8,2) JPATH='$.EXPENSE.[*].AMOUNT') +ENGINE=CONNECT TABLE_TYPE=BSON FILE_NAME='mulexp5.json'; +SELECT * FROM t4; +WHO WEEK WHAT AMOUNT +Joe 5 Beer 14.00 +Joe 5 Food 12.00 +Beth 5 Food 12.00 +Beth 5 Beer 20.00 +Janet 5 Beer 14.00 +Janet 5 Car 12.00 +Janet 5 Beer 19.00 +Janet 5 Food 12.00 +# +# The complete table can be a multiple JSON table +# +CREATE TABLE t1 ( +WHO CHAR(12), +WEEK INT(2), +WHAT CHAR(32) JPATH='$.EXPENSE[*].WHAT', +AMOUNT DOUBLE(8,2) JPATH='$.EXPENSE.[*].AMOUNT') +ENGINE=CONNECT TABLE_TYPE=BSON FILE_NAME='mulexp*.json' MULTIPLE=1; +SELECT * FROM t1 ORDER BY WHO, WEEK, WHAT, AMOUNT; +WHO WEEK WHAT AMOUNT +Beth 3 Beer 16.00 +Beth 4 Beer 15.00 +Beth 4 Food 17.00 +Beth 5 Beer 20.00 +Beth 5 Food 12.00 +Janet 3 Beer 18.00 +Janet 3 Car 19.00 +Janet 3 Food 18.00 +Janet 4 Car 17.00 +Janet 5 Beer 14.00 +Janet 5 Beer 19.00 +Janet 5 Car 12.00 +Janet 5 Food 12.00 +Joe 3 Beer 18.00 +Joe 3 Car 20.00 +Joe 3 Food 12.00 +Joe 3 Food 19.00 +Joe 4 Beer 14.00 +Joe 4 Beer 16.00 +Joe 4 Beer 19.00 +Joe 4 Food 17.00 +Joe 4 Food 17.00 +Joe 5 Beer 14.00 +Joe 5 Food 12.00 +DROP TABLE t1; +# +# Or also a partition JSON table +# +CREATE TABLE t1 ( +WHO CHAR(12), +WEEK INT(2), +WHAT CHAR(32) JPATH='$.EXPENSE[*].WHAT', +AMOUNT DOUBLE(8,2) JPATH='$.EXPENSE.[*].AMOUNT') +ENGINE=CONNECT TABLE_TYPE=BSON FILE_NAME='mulexp%s.json'; +ALTER TABLE t1 +PARTITION BY LIST COLUMNS(WEEK) ( +PARTITION `3` VALUES IN(3), +PARTITION `4` VALUES IN(4), +PARTITION `5` VALUES IN(5)); +Warnings: +Warning 1105 Data repartition in 3 is unchecked +Warning 1105 Data repartition in 4 is unchecked +Warning 1105 Data repartition in 5 is unchecked +SHOW WARNINGS; +Level Code Message +Warning 1105 Data repartition in 3 is unchecked +Warning 1105 Data repartition in 4 is unchecked +Warning 1105 Data repartition in 5 is unchecked +SELECT * FROM t1; +WHO WEEK WHAT AMOUNT +Joe 3 Beer 18.00 +Joe 3 Food 12.00 +Joe 3 Food 19.00 +Joe 3 Car 20.00 +Beth 3 Beer 16.00 +Janet 3 Car 19.00 +Janet 3 Food 18.00 +Janet 3 Beer 18.00 +Joe 4 Beer 19.00 +Joe 4 Beer 16.00 +Joe 4 Food 17.00 +Joe 4 Food 17.00 +Joe 4 Beer 14.00 +Beth 4 Food 17.00 +Beth 4 Beer 15.00 +Janet 4 Car 17.00 +Joe 5 Beer 14.00 +Joe 5 Food 12.00 +Beth 5 Food 12.00 +Beth 5 Beer 20.00 +Janet 5 Beer 14.00 +Janet 5 Car 12.00 +Janet 5 Beer 19.00 +Janet 5 Food 12.00 +SELECT * FROM t1 WHERE WEEK = 4; +WHO WEEK WHAT AMOUNT +Joe 4 Beer 19.00 +Joe 4 Beer 16.00 +Joe 4 Food 17.00 +Joe 4 Food 17.00 +Joe 4 Beer 14.00 +Beth 4 Food 17.00 +Beth 4 Beer 15.00 +Janet 4 Car 17.00 +DROP TABLE t1, t2, t3, t4; diff --git a/storage/connect/mysql-test/connect/r/bson_java_2.result b/storage/connect/mysql-test/connect/r/bson_java_2.result new file mode 100644 index 00000000000..1c21fc7c54f --- /dev/null +++ b/storage/connect/mysql-test/connect/r/bson_java_2.result @@ -0,0 +1,385 @@ +set connect_enable_mongo=1; +set connect_json_all_path=0; +# +# Test the MONGO table type +# +CREATE TABLE t1 (Document varchar(1024) JPATH='*') +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME=restaurants CONNECTION='mongodb://localhost:27017' LRECL=4096 +OPTION_LIST='Driver=Java,Version=2' DATA_CHARSET=utf8; +SELECT * from t1 limit 3; +Document +{"_id":{"$oid":"58ada47de5a51ddfcd5ed51c"},"address":{"building":"1007","coord":[-73.856077,40.848447],"street":"Morris Park Ave","zipcode":"10462"},"borough":"Bronx","cuisine":"Bakery","grades":[{"date":{"$date":"2014-03-03T00:00:00.000Z"},"grade":"A","score":2},{"date":{"$date":"2013-09-11T00:00:00.000Z"},"grade":"A","score":6},{"date":{"$date":"2013-01-24T00:00:00.000Z"},"grade":"A","score":10},{"date":{"$date":"2011-11-23T00:00:00.000Z"},"grade":"A","score":9},{"date":{"$date":"2011-03-10T00:00:00.000Z"},"grade":"B","score":14}],"name":"Morris Park Bake Shop","restaurant_id":"30075445"} +{"_id":{"$oid":"58ada47de5a51ddfcd5ed51d"},"address":{"building":"469","coord":[-73.961704,40.662942],"street":"Flatbush Avenue","zipcode":"11225"},"borough":"Brooklyn","cuisine":"Hamburgers","grades":[{"date":{"$date":"2014-12-30T00:00:00.000Z"},"grade":"A","score":8},{"date":{"$date":"2014-07-01T00:00:00.000Z"},"grade":"B","score":23},{"date":{"$date":"2013-04-30T00:00:00.000Z"},"grade":"A","score":12},{"date":{"$date":"2012-05-08T00:00:00.000Z"},"grade":"A","score":12}],"name":"Wendy'S","restaurant_id":"30112340"} +{"_id":{"$oid":"58ada47de5a51ddfcd5ed51e"},"address":{"building":"351","coord":[-73.98513559999999,40.7676919],"street":"West 57 Street","zipcode":"10019"},"borough":"Manhattan","cuisine":"Irish","grades":[{"date":{"$date":"2014-09-06T00:00:00.000Z"},"grade":"A","score":2},{"date":{"$date":"2013-07-22T00:00:00.000Z"},"grade":"A","score":11},{"date":{"$date":"2012-07-31T00:00:00.000Z"},"grade":"A","score":12},{"date":{"$date":"2011-12-29T00:00:00.000Z"},"grade":"A","score":12}],"name":"Dj Reynolds Pub And Restaurant","restaurant_id":"30191841"} +DROP TABLE t1; +# +# Test catfunc +# +CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=BSON TABNAME=restaurants CATFUNC=columns +OPTION_LIST='Depth=1,Driver=Java,Version=2' DATA_CHARSET=utf8 CONNECTION='mongodb://localhost:27017' LRECL=4096; +SELECT * from t1; +Column_Name Data_Type Type_Name Column_Size Buffer_Length Decimal_Digits Nullable Jpath +_id 1 CHAR 24 24 0 0 _id +address_building 1 CHAR 10 10 0 0 address.building +address_coord 1 CHAR 1024 1024 0 1 address.coord +address_street 1 CHAR 38 38 0 0 address.street +address_zipcode 1 CHAR 5 5 0 0 address.zipcode +borough 1 CHAR 13 13 0 0 +cuisine 1 CHAR 64 64 0 0 +grades_date 1 CHAR 1024 1024 0 1 grades.0.date +grades_grade 1 CHAR 14 14 0 1 grades.0.grade +grades_score 7 INTEGER 2 2 0 1 grades.0.score +name 1 CHAR 98 98 0 0 +restaurant_id 1 CHAR 8 8 0 0 +DROP TABLE t1; +# +# Explicit columns +# +CREATE TABLE t1 ( +_id VARCHAR(24) NOT NULL, +name VARCHAR(255) NOT NULL, +cuisine VARCHAR(255) NOT NULL, +borough VARCHAR(255) NOT NULL, +restaurant_id VARCHAR(255) NOT NULL) +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME=restaurants +CONNECTION='mongodb://localhost:27017' LRECL=1024 DATA_CHARSET=utf8 +OPTION_LIST='Driver=Java,Version=2'; +SELECT * FROM t1 LIMIT 10; +_id name cuisine borough restaurant_id +58ada47de5a51ddfcd5ed51c Morris Park Bake Shop Bakery Bronx 30075445 +58ada47de5a51ddfcd5ed51d Wendy'S Hamburgers Brooklyn 30112340 +58ada47de5a51ddfcd5ed51e Dj Reynolds Pub And Restaurant Irish Manhattan 30191841 +58ada47de5a51ddfcd5ed51f Riviera Caterer American Brooklyn 40356018 +58ada47de5a51ddfcd5ed520 Tov Kosher Kitchen Jewish/Kosher Queens 40356068 +58ada47de5a51ddfcd5ed521 Brunos On The Boulevard American Queens 40356151 +58ada47de5a51ddfcd5ed522 Kosher Island Jewish/Kosher Staten Island 40356442 +58ada47de5a51ddfcd5ed523 Wilken'S Fine Food Delicatessen Brooklyn 40356483 +58ada47de5a51ddfcd5ed524 Regina Caterers American Brooklyn 40356649 +58ada47de5a51ddfcd5ed525 Taste The Tropics Ice Cream Ice Cream, Gelato, Yogurt, Ices Brooklyn 40356731 +DROP TABLE t1; +# +# Test discovery +# +CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=BSON TABNAME=restaurants +OPTION_LIST='Depth=1,Driver=Java,Version=2' CONNECTION='mongodb://localhost:27017' LRECL=4096 DATA_CHARSET=utf8; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `_id` char(24) NOT NULL `JPATH`='_id', + `address_building` char(10) NOT NULL `JPATH`='address.building', + `address_coord` varchar(1024) DEFAULT NULL `JPATH`='address.coord', + `address_street` char(38) NOT NULL `JPATH`='address.street', + `address_zipcode` char(5) NOT NULL `JPATH`='address.zipcode', + `borough` char(13) NOT NULL, + `cuisine` char(64) NOT NULL, + `grades_date` varchar(1024) DEFAULT NULL `JPATH`='grades.0.date', + `grades_grade` char(14) DEFAULT NULL `JPATH`='grades.0.grade', + `grades_score` int(2) DEFAULT NULL `JPATH`='grades.0.score', + `name` char(98) NOT NULL, + `restaurant_id` char(8) NOT NULL +) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='mongodb://localhost:27017' `TABLE_TYPE`='BSON' `TABNAME`='restaurants' `OPTION_LIST`='Depth=1,Driver=Java,Version=2' `DATA_CHARSET`='utf8' `LRECL`=4096 +SELECT * FROM t1 LIMIT 5; +_id address_building address_coord address_street address_zipcode borough cuisine grades_date grades_grade grades_score name restaurant_id +58ada47de5a51ddfcd5ed51c 1007 -73.856077, 40.848447 Morris Park Ave 10462 Bronx Bakery 2014-03-03T00:00:00.000Z A 2 Morris Park Bake Shop 30075445 +58ada47de5a51ddfcd5ed51d 469 -73.961704, 40.662942 Flatbush Avenue 11225 Brooklyn Hamburgers 2014-12-30T00:00:00.000Z A 8 Wendy'S 30112340 +58ada47de5a51ddfcd5ed51e 351 -73.98513559999999, 40.7676919 West 57 Street 10019 Manhattan Irish 2014-09-06T00:00:00.000Z A 2 Dj Reynolds Pub And Restaurant 30191841 +58ada47de5a51ddfcd5ed51f 2780 -73.98241999999999, 40.579505 Stillwell Avenue 11224 Brooklyn American 2014-06-10T00:00:00.000Z A 5 Riviera Caterer 40356018 +58ada47de5a51ddfcd5ed520 97-22 -73.8601152, 40.7311739 63 Road 11374 Queens Jewish/Kosher 2014-11-24T00:00:00.000Z Z 20 Tov Kosher Kitchen 40356068 +DROP TABLE t1; +# +# Dropping a column +# +CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=BSON TABNAME=restaurants DATA_CHARSET=utf8 +COLIST='{"grades":0}' OPTION_LIST='Driver=Java,Version=2,level=0' CONNECTION='mongodb://localhost:27017' LRECL=4096; +SELECT * FROM t1 LIMIT 10; +_id address borough cuisine name restaurant_id +58ada47de5a51ddfcd5ed51c 1007 (-73.856077, 40.848447) Morris Park Ave 10462 Bronx Bakery Morris Park Bake Shop 30075445 +58ada47de5a51ddfcd5ed51d 469 (-73.961704, 40.662942) Flatbush Avenue 11225 Brooklyn Hamburgers Wendy'S 30112340 +58ada47de5a51ddfcd5ed51e 351 (-73.98513559999999, 40.7676919) West 57 Street 10019 Manhattan Irish Dj Reynolds Pub And Restaurant 30191841 +58ada47de5a51ddfcd5ed51f 2780 (-73.98241999999999, 40.579505) Stillwell Avenue 11224 Brooklyn American Riviera Caterer 40356018 +58ada47de5a51ddfcd5ed520 97-22 (-73.8601152, 40.7311739) 63 Road 11374 Queens Jewish/Kosher Tov Kosher Kitchen 40356068 +58ada47de5a51ddfcd5ed521 8825 (-73.8803827, 40.7643124) Astoria Boulevard 11369 Queens American Brunos On The Boulevard 40356151 +58ada47de5a51ddfcd5ed522 2206 (-74.1377286, 40.6119572) Victory Boulevard 10314 Staten Island Jewish/Kosher Kosher Island 40356442 +58ada47de5a51ddfcd5ed523 7114 (-73.9068506, 40.6199034) Avenue U 11234 Brooklyn Delicatessen Wilken'S Fine Food 40356483 +58ada47de5a51ddfcd5ed524 6409 (-74.00528899999999, 40.628886) 11 Avenue 11219 Brooklyn American Regina Caterers 40356649 +58ada47de5a51ddfcd5ed525 1839 (-73.9482609, 40.6408271) Nostrand Avenue 11226 Brooklyn Ice Cream, Gelato, Yogurt, Ices Taste The Tropics Ice Cream 40356731 +DROP TABLE t1; +# +# Specifying Jpath +# +CREATE TABLE t1 ( +_id VARCHAR(24) NOT NULL, +name VARCHAR(64) NOT NULL, +cuisine CHAR(200) NOT NULL, +borough CHAR(16) NOT NULL, +street VARCHAR(65) JPATH='address.street', +building CHAR(16) JPATH='address.building', +zipcode CHAR(5) JPATH='address.zipcode', +grade CHAR(1) JPATH='grades.0.grade', +score INT(4) NOT NULL JPATH='grades.0.score', +`date` DATE JPATH='grades.0.date', +restaurant_id VARCHAR(255) NOT NULL) +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME='restaurants' DATA_CHARSET=utf8 +OPTION_LIST='Driver=Java,Version=2' CONNECTION='mongodb://localhost:27017' LRECL=4096; +SELECT * FROM t1 LIMIT 1; +_id 58ada47de5a51ddfcd5ed51c +name Morris Park Bake Shop +cuisine Bakery +borough Bronx +street Morris Park Ave +building 1007 +zipcode 10462 +grade A +score 2 +date 1970-01-01 +restaurant_id 30075445 +SELECT name, street, score, date FROM t1 LIMIT 5; +name street score date +Morris Park Bake Shop Morris Park Ave 2 1970-01-01 +Wendy'S Flatbush Avenue 8 1970-01-01 +Dj Reynolds Pub And Restaurant West 57 Street 2 1970-01-01 +Riviera Caterer Stillwell Avenue 5 1970-01-01 +Tov Kosher Kitchen 63 Road 20 1970-01-01 +SELECT name, cuisine, borough FROM t1 WHERE grade = 'A' LIMIT 10; +name cuisine borough +Morris Park Bake Shop Bakery Bronx +Wendy'S Hamburgers Brooklyn +Dj Reynolds Pub And Restaurant Irish Manhattan +Riviera Caterer American Brooklyn +Kosher Island Jewish/Kosher Staten Island +Wilken'S Fine Food Delicatessen Brooklyn +Regina Caterers American Brooklyn +Taste The Tropics Ice Cream Ice Cream, Gelato, Yogurt, Ices Brooklyn +Wild Asia American Bronx +C & C Catering Service American Brooklyn +SELECT COUNT(*) FROM t1 WHERE grade = 'A'; +COUNT(*) +20687 +SELECT * FROM t1 WHERE cuisine = 'English'; +_id name cuisine borough street building zipcode grade score date restaurant_id +58ada47de5a51ddfcd5ed83d Tea And Sympathy English Manhattan Greenwich Avenue 108 10011 A 8 1970-01-01 40391531 +58ada47de5a51ddfcd5ed85c Tartine English Manhattan West 11 Street 253 10014 A 11 1970-01-01 40392496 +58ada47de5a51ddfcd5ee1f3 The Park Slope Chipshop English Brooklyn 5 Avenue 383 11215 B 17 1970-01-01 40816202 +58ada47de5a51ddfcd5ee7e4 Pound And Pence English Manhattan Liberty Street 55 10005 A 7 1970-01-01 41022701 +58ada47de5a51ddfcd5ee999 Chip Shop English Brooklyn Atlantic Avenue 129 11201 A 9 1970-01-01 41076583 +58ada47ee5a51ddfcd5efe3f The Breslin Bar & Dining Room English Manhattan West 29 Street 16 10001 A 13 1970-01-01 41443706 +58ada47ee5a51ddfcd5efe99 Highlands Restaurant English Manhattan West 10 Street 150 10014 A 12 1970-01-01 41448559 +58ada47ee5a51ddfcd5f0413 The Fat Radish English Manhattan Orchard Street 17 10002 A 12 1970-01-01 41513545 +58ada47ee5a51ddfcd5f0777 Jones Wood Foundry English Manhattan East 76 Street 401 10021 A 12 1970-01-01 41557377 +58ada47ee5a51ddfcd5f0ea2 Whitehall English Manhattan Greenwich Avenue 19 10014 Z 15 1970-01-01 41625263 +58ada47ee5a51ddfcd5f1004 The Churchill Tavern English Manhattan East 28 Street 45 10016 A 13 1970-01-01 41633327 +58ada47ee5a51ddfcd5f13d5 The Monro English Brooklyn 5 Avenue 481 11215 A 7 1970-01-01 41660253 +58ada47ee5a51ddfcd5f1454 The Cock & Bull English Manhattan West 45 Street 23 10036 A 7 1970-01-01 41664704 +58ada47ee5a51ddfcd5f176e Dear Bushwick English Brooklyn Wilson Avenue 41 11237 A 12 1970-01-01 41690534 +58ada47ee5a51ddfcd5f1e91 Snowdonia Pub English Queens 32 Street 34-55 11106 A 12 1970-01-01 50000290 +58ada47ee5a51ddfcd5f2ddc Oscar'S Place English Manhattan Hudson Street 466 10014 A 10 1970-01-01 50011097 +SELECT * FROM t1 WHERE score = building; +_id name cuisine borough street building zipcode grade score date restaurant_id +DROP TABLE t1; +# +# Specifying Filter +# +CREATE TABLE t1 ( +_id CHAR(24) NOT NULL, +name CHAR(64) NOT NULL, +borough CHAR(16) NOT NULL, +restaurant_id CHAR(8) NOT NULL) +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME=restaurants DATA_CHARSET=utf8 +FILTER='{"cuisine":"French","borough":{"$ne":"Manhattan"}}' +OPTION_LIST='Driver=Java,Version=2' CONNECTION='mongodb://localhost:27017' LRECL=4096; +SELECT name FROM t1 WHERE borough = 'Queens'; +name +La Baraka Restaurant +Air France Lounge +Tournesol +Winegasm +Cafe Henri +Bistro 33 +Domaine Wine Bar +Cafe Triskell +Cannelle Patisserie +La Vie +Dirty Pierres Bistro +Fresca La Crepe +Bliss 46 Bistro +Bear +Cuisine By Claudette +Paris Baguette +The Baroness Bar +Francis Cafe +Madame Sou Sou +Crepe 'N' Tearia +Aperitif Bayside Llc +DROP TABLE t1; +# +# Testing pipeline +# +CREATE TABLE t1 ( +name VARCHAR(64) NOT NULL, +borough CHAR(16) NOT NULL, +date DATETIME NOT NULL, +grade CHAR(1) NOT NULL, +score INT(4) NOT NULL) +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME='restaurants' DATA_CHARSET=utf8 +COLIST='{"pipeline":[{"$match":{"cuisine":"French"}},{"$unwind":"$grades"},{"$project":{"_id":0,"name":1,"borough":1,"date":"$grades.date","grade":"$grades.grade","score":"$grades.score"}}]}' +OPTION_LIST='Driver=Java,Version=2,Pipeline=1' CONNECTION='mongodb://localhost:27017' LRECL=4096; +SELECT * FROM t1 LIMIT 10; +name borough date grade score +Tout Va Bien Manhattan 1970-01-01 01:33:34 B 15 +Tout Va Bien Manhattan 1970-01-01 01:33:34 A 13 +Tout Va Bien Manhattan 1970-01-01 01:33:33 C 36 +Tout Va Bien Manhattan 1970-01-01 01:33:33 B 22 +Tout Va Bien Manhattan 1970-01-01 01:33:32 C 36 +Tout Va Bien Manhattan 1970-01-01 01:33:32 C 7 +La Grenouille Manhattan 1970-01-01 01:33:34 A 10 +La Grenouille Manhattan 1970-01-01 01:33:33 A 9 +La Grenouille Manhattan 1970-01-01 01:33:32 A 13 +Le Perigord Manhattan 1970-01-01 01:33:34 B 14 +SELECT name, grade, score, date FROM t1 WHERE borough = 'Bronx'; +name grade score date +Bistro Sk A 10 1970-01-01 01:33:34 +Bistro Sk A 12 1970-01-01 01:33:34 +Bistro Sk B 18 1970-01-01 01:33:33 +DROP TABLE t1; +# +# try level 2 discovery +# +CREATE TABLE t1 +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME=restaurants +FILTER='{"cuisine":"French","borough":{"$ne":"Manhattan"}}' +COLIST='{"cuisine":0}' CONNECTION='mongodb://localhost:27017' LRECL=4096 +OPTION_LIST='Driver=Java,level=2,version=2'; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `_id` char(24) NOT NULL `JPATH`='_id', + `address_building` char(10) NOT NULL `JPATH`='address.building', + `address_coord` double(18,16) DEFAULT NULL `JPATH`='address.coord.0', + `address_street` char(38) NOT NULL `JPATH`='address.street', + `address_zipcode` char(5) NOT NULL `JPATH`='address.zipcode', + `borough` char(13) NOT NULL, + `grades_date` char(24) DEFAULT NULL `JPATH`='grades.0.date', + `grades_grade` char(14) DEFAULT NULL `JPATH`='grades.0.grade', + `grades_score` int(2) DEFAULT NULL `JPATH`='grades.0.score', + `name` char(98) NOT NULL, + `restaurant_id` char(8) NOT NULL +) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='mongodb://localhost:27017' `TABLE_TYPE`='BSON' `TABNAME`='restaurants' `COLIST`='{"cuisine":0}' `FILTER`='{"cuisine":"French","borough":{"$ne":"Manhattan"}}' `OPTION_LIST`='Driver=Java,level=2,version=2' `LRECL`=4096 +SELECT name, borough, address_street, grades_score AS score FROM t1 WHERE grades_grade = 'B'; +name borough address_street score +Le Gamin Brooklyn Vanderbilt Avenue 24 +Bistro 33 Queens Ditmars Boulevard 15 +Dirty Pierres Bistro Queens Station Square 22 +Santos Anne Brooklyn Union Avenue 26 +Le Paddock Brooklyn Prospect Avenue 17 +La Crepe Et La Vie Brooklyn Foster Avenue 24 +Francis Cafe Queens Ditmars Boulevard 19 +DROP TABLE t1; +# +# try CRUD operations +# +false +CREATE TABLE t1 (_id INT(4) NOT NULL, msg CHAR(64)) +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME='testcoll' +OPTION_LIST='Driver=Java,Version=2' CONNECTION='mongodb://localhost:27017' LRECL=4096; +DELETE FROM t1; +INSERT INTO t1 VALUES(0,NULL),(1,'One'),(2,'Two'),(3,'Three'); +SELECT * FROM t1; +_id msg +0 NULL +1 One +2 Two +3 Three +UPDATE t1 SET msg = 'Deux' WHERE _id = 2; +DELETE FROM t1 WHERE msg IS NULL; +SELECT * FROM t1; +_id msg +1 One +2 Deux +3 Three +DELETE FROM t1; +DROP TABLE t1; +true +# +# List states whose population is equal or more than 10 millions +# +false +CREATE TABLE t1 ( +_id char(5) NOT NULL, +city char(16) NOT NULL, +loc_0 double(12,6) NOT NULL `JPATH`='loc.0', +loc_1 char(12) NOT NULL `JPATH`='loc.1', +pop int(11) NOT NULL, +state char(2) NOT NULL) +ENGINE=CONNECT CONNECTION='mongodb://localhost:27017' TABLE_TYPE=BSON TABNAME='cities' +OPTION_LIST='Driver=Java,Version=2' CONNECTION='mongodb://localhost:27017' LRECL=4096 DATA_CHARSET='utf8'; +# Using SQL for grouping +SELECT state, sum(pop) AS totalPop FROM t1 GROUP BY state HAVING totalPop >= 10000000 ORDER BY totalPop DESC; +state totalPop +CA 29754890 +NY 17990402 +TX 16984601 +FL 12686644 +PA 11881643 +IL 11427576 +OH 10846517 +DROP TABLE t1; +# Using a pipeline for grouping +CREATE TABLE t1 (_id CHAR(2) NOT NULL, totalPop INT(11) NOT NULL) +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME='cities' DATA_CHARSET=utf8 +COLIST='{"pipeline":[{"$group":{"_id":"$state","totalPop":{"$sum":"$pop"}}},{"$match":{"totalPop":{"$gte":10000000}}},{"$sort":{"totalPop":-1}}]}' +OPTION_LIST='Driver=Java,Version=2,Pipeline=1' CONNECTION='mongodb://localhost:27017' LRECL=4096; +SELECT * FROM t1; +_id totalPop +CA 29754890 +NY 17990402 +TX 16984601 +FL 12686644 +PA 11881643 +IL 11427576 +OH 10846517 +DROP TABLE t1; +true +# +# Test making array +# +CREATE TABLE t1 ( +_id int(4) NOT NULL, +item CHAR(8) NOT NULL, +prices_0 INT(6) JPATH='prices.0', +prices_1 INT(6) JPATH='prices.1', +prices_2 INT(6) JPATH='prices.2', +prices_3 INT(6) JPATH='prices.3', +prices_4 INT(6) JPATH='prices.4') +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME='testcoll' DATA_CHARSET=utf8 +OPTION_LIST='Driver=Java,Version=2' CONNECTION='mongodb://localhost:27017' LRECL=4096; +INSERT INTO t1 VALUES +(1,'journal',87,45,63,12,78), +(2,'notebook',123,456,789,NULL,NULL), +(3,'paper',5,7,3,8,NULL), +(4,'planner',25,71,NULL,44,27), +(5,'postcard',5,7,3,8,NULL); +SELECT * FROM t1; +_id item prices_0 prices_1 prices_2 prices_3 prices_4 +1 journal 87 45 63 12 78 +2 notebook 123 456 789 NULL NULL +3 paper 5 7 3 8 NULL +4 planner 25 71 NULL 44 27 +5 postcard 5 7 3 8 NULL +DROP TABLE t1; +# +# Test array aggregation +# +CREATE TABLE t1 +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME='testcoll' +COLIST='{"pipeline":[{"$project":{"_id":0,"item":1,"total":{"$sum":"$prices"},"average":{"$avg":"$prices"}}}]}' +OPTION_LIST='Driver=Java,Version=2,Pipeline=YES' CONNECTION='mongodb://localhost:27017' LRECL=4096; +SELECT * FROM t1; +item total average +journal 285 57.00 +notebook 1368 456.00 +paper 23 5.75 +planner 167 41.75 +postcard 23 5.75 +DROP TABLE t1; +true +set connect_enable_mongo=0; diff --git a/storage/connect/mysql-test/connect/r/bson_java_3.result b/storage/connect/mysql-test/connect/r/bson_java_3.result new file mode 100644 index 00000000000..d198ee3faa4 --- /dev/null +++ b/storage/connect/mysql-test/connect/r/bson_java_3.result @@ -0,0 +1,385 @@ +set connect_enable_mongo=1; +set connect_json_all_path=0; +# +# Test the MONGO table type +# +CREATE TABLE t1 (Document varchar(1024) JPATH='*') +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME=restaurants CONNECTION='mongodb://localhost:27017' LRECL=4096 +OPTION_LIST='Driver=Java,Version=3' DATA_CHARSET=utf8; +SELECT * from t1 limit 3; +Document +{"_id":{"$oid":"58ada47de5a51ddfcd5ed51c"},"address":{"building":"1007","coord":[-73.856077,40.848447],"street":"Morris Park Ave","zipcode":"10462"},"borough":"Bronx","cuisine":"Bakery","grades":[{"date":{"$date":1393804800000},"grade":"A","score":2},{"date":{"$date":1378857600000},"grade":"A","score":6},{"date":{"$date":1358985600000},"grade":"A","score":10},{"date":{"$date":1322006400000},"grade":"A","score":9},{"date":{"$date":1299715200000},"grade":"B","score":14}],"name":"Morris Park Bake Shop","restaurant_id":"30075445"} +{"_id":{"$oid":"58ada47de5a51ddfcd5ed51d"},"address":{"building":"469","coord":[-73.961704,40.662942],"street":"Flatbush Avenue","zipcode":"11225"},"borough":"Brooklyn","cuisine":"Hamburgers","grades":[{"date":{"$date":1419897600000},"grade":"A","score":8},{"date":{"$date":1404172800000},"grade":"B","score":23},{"date":{"$date":1367280000000},"grade":"A","score":12},{"date":{"$date":1336435200000},"grade":"A","score":12}],"name":"Wendy'S","restaurant_id":"30112340"} +{"_id":{"$oid":"58ada47de5a51ddfcd5ed51e"},"address":{"building":"351","coord":[-73.98513559999999,40.7676919],"street":"West 57 Street","zipcode":"10019"},"borough":"Manhattan","cuisine":"Irish","grades":[{"date":{"$date":1409961600000},"grade":"A","score":2},{"date":{"$date":1374451200000},"grade":"A","score":11},{"date":{"$date":1343692800000},"grade":"A","score":12},{"date":{"$date":1325116800000},"grade":"A","score":12}],"name":"Dj Reynolds Pub And Restaurant","restaurant_id":"30191841"} +DROP TABLE t1; +# +# Test catfunc +# +CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=BSON TABNAME=restaurants CATFUNC=columns +OPTION_LIST='Depth=1,Driver=Java,Version=3' DATA_CHARSET=utf8 CONNECTION='mongodb://localhost:27017' LRECL=4096; +SELECT * from t1; +Column_Name Data_Type Type_Name Column_Size Buffer_Length Decimal_Digits Nullable Jpath +_id 1 CHAR 24 24 0 0 _id +address_building 1 CHAR 10 10 0 0 address.building +address_coord 1 CHAR 1024 1024 0 1 address.coord +address_street 1 CHAR 38 38 0 0 address.street +address_zipcode 1 CHAR 5 5 0 0 address.zipcode +borough 1 CHAR 13 13 0 0 +cuisine 1 CHAR 64 64 0 0 +grades_date 1 CHAR 1024 1024 0 1 grades.0.date +grades_grade 1 CHAR 14 14 0 1 grades.0.grade +grades_score 7 INTEGER 2 2 0 1 grades.0.score +name 1 CHAR 98 98 0 0 +restaurant_id 1 CHAR 8 8 0 0 +DROP TABLE t1; +# +# Explicit columns +# +CREATE TABLE t1 ( +_id VARCHAR(24) NOT NULL, +name VARCHAR(255) NOT NULL, +cuisine VARCHAR(255) NOT NULL, +borough VARCHAR(255) NOT NULL, +restaurant_id VARCHAR(255) NOT NULL) +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME=restaurants +CONNECTION='mongodb://localhost:27017' LRECL=1024 DATA_CHARSET=utf8 +OPTION_LIST='Driver=Java,Version=3'; +SELECT * FROM t1 LIMIT 10; +_id name cuisine borough restaurant_id +58ada47de5a51ddfcd5ed51c Morris Park Bake Shop Bakery Bronx 30075445 +58ada47de5a51ddfcd5ed51d Wendy'S Hamburgers Brooklyn 30112340 +58ada47de5a51ddfcd5ed51e Dj Reynolds Pub And Restaurant Irish Manhattan 30191841 +58ada47de5a51ddfcd5ed51f Riviera Caterer American Brooklyn 40356018 +58ada47de5a51ddfcd5ed520 Tov Kosher Kitchen Jewish/Kosher Queens 40356068 +58ada47de5a51ddfcd5ed521 Brunos On The Boulevard American Queens 40356151 +58ada47de5a51ddfcd5ed522 Kosher Island Jewish/Kosher Staten Island 40356442 +58ada47de5a51ddfcd5ed523 Wilken'S Fine Food Delicatessen Brooklyn 40356483 +58ada47de5a51ddfcd5ed524 Regina Caterers American Brooklyn 40356649 +58ada47de5a51ddfcd5ed525 Taste The Tropics Ice Cream Ice Cream, Gelato, Yogurt, Ices Brooklyn 40356731 +DROP TABLE t1; +# +# Test discovery +# +CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=BSON TABNAME=restaurants +OPTION_LIST='Depth=1,Driver=Java,Version=3' CONNECTION='mongodb://localhost:27017' LRECL=4096 DATA_CHARSET=utf8; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `_id` char(24) NOT NULL `JPATH`='_id', + `address_building` char(10) NOT NULL `JPATH`='address.building', + `address_coord` varchar(1024) DEFAULT NULL `JPATH`='address.coord', + `address_street` char(38) NOT NULL `JPATH`='address.street', + `address_zipcode` char(5) NOT NULL `JPATH`='address.zipcode', + `borough` char(13) NOT NULL, + `cuisine` char(64) NOT NULL, + `grades_date` varchar(1024) DEFAULT NULL `JPATH`='grades.0.date', + `grades_grade` char(14) DEFAULT NULL `JPATH`='grades.0.grade', + `grades_score` int(2) DEFAULT NULL `JPATH`='grades.0.score', + `name` char(98) NOT NULL, + `restaurant_id` char(8) NOT NULL +) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='mongodb://localhost:27017' `TABLE_TYPE`='BSON' `TABNAME`='restaurants' `OPTION_LIST`='Depth=1,Driver=Java,Version=3' `DATA_CHARSET`='utf8' `LRECL`=4096 +SELECT * FROM t1 LIMIT 5; +_id address_building address_coord address_street address_zipcode borough cuisine grades_date grades_grade grades_score name restaurant_id +58ada47de5a51ddfcd5ed51c 1007 -73.856077, 40.848447 Morris Park Ave 10462 Bronx Bakery 1393804800 A 2 Morris Park Bake Shop 30075445 +58ada47de5a51ddfcd5ed51d 469 -73.961704, 40.662942 Flatbush Avenue 11225 Brooklyn Hamburgers 1419897600 A 8 Wendy'S 30112340 +58ada47de5a51ddfcd5ed51e 351 -73.98513559999999, 40.7676919 West 57 Street 10019 Manhattan Irish 1409961600 A 2 Dj Reynolds Pub And Restaurant 30191841 +58ada47de5a51ddfcd5ed51f 2780 -73.98241999999999, 40.579505 Stillwell Avenue 11224 Brooklyn American 1402358400 A 5 Riviera Caterer 40356018 +58ada47de5a51ddfcd5ed520 97-22 -73.8601152, 40.7311739 63 Road 11374 Queens Jewish/Kosher 1416787200 Z 20 Tov Kosher Kitchen 40356068 +DROP TABLE t1; +# +# Dropping a column +# +CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=BSON TABNAME=restaurants DATA_CHARSET=utf8 +COLIST='{"grades":0}' OPTION_LIST='Driver=Java,Version=3,level=0' CONNECTION='mongodb://localhost:27017' LRECL=4096; +SELECT * FROM t1 LIMIT 10; +_id address borough cuisine name restaurant_id +58ada47de5a51ddfcd5ed51c 1007 (-73.856077, 40.848447) Morris Park Ave 10462 Bronx Bakery Morris Park Bake Shop 30075445 +58ada47de5a51ddfcd5ed51d 469 (-73.961704, 40.662942) Flatbush Avenue 11225 Brooklyn Hamburgers Wendy'S 30112340 +58ada47de5a51ddfcd5ed51e 351 (-73.98513559999999, 40.7676919) West 57 Street 10019 Manhattan Irish Dj Reynolds Pub And Restaurant 30191841 +58ada47de5a51ddfcd5ed51f 2780 (-73.98241999999999, 40.579505) Stillwell Avenue 11224 Brooklyn American Riviera Caterer 40356018 +58ada47de5a51ddfcd5ed520 97-22 (-73.8601152, 40.7311739) 63 Road 11374 Queens Jewish/Kosher Tov Kosher Kitchen 40356068 +58ada47de5a51ddfcd5ed521 8825 (-73.8803827, 40.7643124) Astoria Boulevard 11369 Queens American Brunos On The Boulevard 40356151 +58ada47de5a51ddfcd5ed522 2206 (-74.1377286, 40.6119572) Victory Boulevard 10314 Staten Island Jewish/Kosher Kosher Island 40356442 +58ada47de5a51ddfcd5ed523 7114 (-73.9068506, 40.6199034) Avenue U 11234 Brooklyn Delicatessen Wilken'S Fine Food 40356483 +58ada47de5a51ddfcd5ed524 6409 (-74.00528899999999, 40.628886) 11 Avenue 11219 Brooklyn American Regina Caterers 40356649 +58ada47de5a51ddfcd5ed525 1839 (-73.9482609, 40.6408271) Nostrand Avenue 11226 Brooklyn Ice Cream, Gelato, Yogurt, Ices Taste The Tropics Ice Cream 40356731 +DROP TABLE t1; +# +# Specifying Jpath +# +CREATE TABLE t1 ( +_id VARCHAR(24) NOT NULL, +name VARCHAR(64) NOT NULL, +cuisine CHAR(200) NOT NULL, +borough CHAR(16) NOT NULL, +street VARCHAR(65) JPATH='address.street', +building CHAR(16) JPATH='address.building', +zipcode CHAR(5) JPATH='address.zipcode', +grade CHAR(1) JPATH='grades.0.grade', +score INT(4) NOT NULL JPATH='grades.0.score', +`date` DATE JPATH='grades.0.date', +restaurant_id VARCHAR(255) NOT NULL) +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME='restaurants' DATA_CHARSET=utf8 +OPTION_LIST='Driver=Java,Version=3' CONNECTION='mongodb://localhost:27017' LRECL=4096; +SELECT * FROM t1 LIMIT 1; +_id 58ada47de5a51ddfcd5ed51c +name Morris Park Bake Shop +cuisine Bakery +borough Bronx +street Morris Park Ave +building 1007 +zipcode 10462 +grade A +score 2 +date 2014-03-03 +restaurant_id 30075445 +SELECT name, street, score, date FROM t1 LIMIT 5; +name street score date +Morris Park Bake Shop Morris Park Ave 2 2014-03-03 +Wendy'S Flatbush Avenue 8 2014-12-30 +Dj Reynolds Pub And Restaurant West 57 Street 2 2014-09-06 +Riviera Caterer Stillwell Avenue 5 2014-06-10 +Tov Kosher Kitchen 63 Road 20 2014-11-24 +SELECT name, cuisine, borough FROM t1 WHERE grade = 'A' LIMIT 10; +name cuisine borough +Morris Park Bake Shop Bakery Bronx +Wendy'S Hamburgers Brooklyn +Dj Reynolds Pub And Restaurant Irish Manhattan +Riviera Caterer American Brooklyn +Kosher Island Jewish/Kosher Staten Island +Wilken'S Fine Food Delicatessen Brooklyn +Regina Caterers American Brooklyn +Taste The Tropics Ice Cream Ice Cream, Gelato, Yogurt, Ices Brooklyn +Wild Asia American Bronx +C & C Catering Service American Brooklyn +SELECT COUNT(*) FROM t1 WHERE grade = 'A'; +COUNT(*) +20687 +SELECT * FROM t1 WHERE cuisine = 'English'; +_id name cuisine borough street building zipcode grade score date restaurant_id +58ada47de5a51ddfcd5ed83d Tea And Sympathy English Manhattan Greenwich Avenue 108 10011 A 8 2014-10-23 40391531 +58ada47de5a51ddfcd5ed85c Tartine English Manhattan West 11 Street 253 10014 A 11 2014-08-14 40392496 +58ada47de5a51ddfcd5ee1f3 The Park Slope Chipshop English Brooklyn 5 Avenue 383 11215 B 17 2014-09-29 40816202 +58ada47de5a51ddfcd5ee7e4 Pound And Pence English Manhattan Liberty Street 55 10005 A 7 2014-02-11 41022701 +58ada47de5a51ddfcd5ee999 Chip Shop English Brooklyn Atlantic Avenue 129 11201 A 9 2014-10-08 41076583 +58ada47ee5a51ddfcd5efe3f The Breslin Bar & Dining Room English Manhattan West 29 Street 16 10001 A 13 2014-06-09 41443706 +58ada47ee5a51ddfcd5efe99 Highlands Restaurant English Manhattan West 10 Street 150 10014 A 12 2014-10-22 41448559 +58ada47ee5a51ddfcd5f0413 The Fat Radish English Manhattan Orchard Street 17 10002 A 12 2014-07-26 41513545 +58ada47ee5a51ddfcd5f0777 Jones Wood Foundry English Manhattan East 76 Street 401 10021 A 12 2014-12-03 41557377 +58ada47ee5a51ddfcd5f0ea2 Whitehall English Manhattan Greenwich Avenue 19 10014 Z 15 2015-01-16 41625263 +58ada47ee5a51ddfcd5f1004 The Churchill Tavern English Manhattan East 28 Street 45 10016 A 13 2014-08-27 41633327 +58ada47ee5a51ddfcd5f13d5 The Monro English Brooklyn 5 Avenue 481 11215 A 7 2014-06-03 41660253 +58ada47ee5a51ddfcd5f1454 The Cock & Bull English Manhattan West 45 Street 23 10036 A 7 2014-08-07 41664704 +58ada47ee5a51ddfcd5f176e Dear Bushwick English Brooklyn Wilson Avenue 41 11237 A 12 2014-12-27 41690534 +58ada47ee5a51ddfcd5f1e91 Snowdonia Pub English Queens 32 Street 34-55 11106 A 12 2014-10-28 50000290 +58ada47ee5a51ddfcd5f2ddc Oscar'S Place English Manhattan Hudson Street 466 10014 A 10 2014-08-18 50011097 +SELECT * FROM t1 WHERE score = building; +_id name cuisine borough street building zipcode grade score date restaurant_id +DROP TABLE t1; +# +# Specifying Filter +# +CREATE TABLE t1 ( +_id CHAR(24) NOT NULL, +name CHAR(64) NOT NULL, +borough CHAR(16) NOT NULL, +restaurant_id CHAR(8) NOT NULL) +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME=restaurants DATA_CHARSET=utf8 +FILTER='{"cuisine":"French","borough":{"$ne":"Manhattan"}}' +OPTION_LIST='Driver=Java,Version=3' CONNECTION='mongodb://localhost:27017' LRECL=4096; +SELECT name FROM t1 WHERE borough = 'Queens'; +name +La Baraka Restaurant +Air France Lounge +Tournesol +Winegasm +Cafe Henri +Bistro 33 +Domaine Wine Bar +Cafe Triskell +Cannelle Patisserie +La Vie +Dirty Pierres Bistro +Fresca La Crepe +Bliss 46 Bistro +Bear +Cuisine By Claudette +Paris Baguette +The Baroness Bar +Francis Cafe +Madame Sou Sou +Crepe 'N' Tearia +Aperitif Bayside Llc +DROP TABLE t1; +# +# Testing pipeline +# +CREATE TABLE t1 ( +name VARCHAR(64) NOT NULL, +borough CHAR(16) NOT NULL, +date DATETIME NOT NULL, +grade CHAR(1) NOT NULL, +score INT(4) NOT NULL) +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME='restaurants' DATA_CHARSET=utf8 +COLIST='{"pipeline":[{"$match":{"cuisine":"French"}},{"$unwind":"$grades"},{"$project":{"_id":0,"name":1,"borough":1,"date":"$grades.date","grade":"$grades.grade","score":"$grades.score"}}]}' +OPTION_LIST='Driver=Java,Version=3,Pipeline=1' CONNECTION='mongodb://localhost:27017' LRECL=4096; +SELECT * FROM t1 LIMIT 10; +name borough date grade score +Tout Va Bien Manhattan 2014-11-10 01:00:00 B 15 +Tout Va Bien Manhattan 2014-04-03 02:00:00 A 13 +Tout Va Bien Manhattan 2013-07-17 02:00:00 C 36 +Tout Va Bien Manhattan 2013-02-06 01:00:00 B 22 +Tout Va Bien Manhattan 2012-07-16 02:00:00 C 36 +Tout Va Bien Manhattan 2012-03-08 01:00:00 C 7 +La Grenouille Manhattan 2014-04-09 02:00:00 A 10 +La Grenouille Manhattan 2013-03-05 01:00:00 A 9 +La Grenouille Manhattan 2012-02-02 01:00:00 A 13 +Le Perigord Manhattan 2014-07-14 02:00:00 B 14 +SELECT name, grade, score, date FROM t1 WHERE borough = 'Bronx'; +name grade score date +Bistro Sk A 10 2014-11-21 01:00:00 +Bistro Sk A 12 2014-02-19 01:00:00 +Bistro Sk B 18 2013-06-12 02:00:00 +DROP TABLE t1; +# +# try level 2 discovery +# +CREATE TABLE t1 +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME=restaurants +FILTER='{"cuisine":"French","borough":{"$ne":"Manhattan"}}' +COLIST='{"cuisine":0}' CONNECTION='mongodb://localhost:27017' LRECL=4096 +OPTION_LIST='Driver=Java,level=2,version=3'; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `_id` char(24) NOT NULL `JPATH`='_id', + `address_building` char(10) NOT NULL `JPATH`='address.building', + `address_coord` double(18,16) DEFAULT NULL `JPATH`='address.coord.0', + `address_street` char(38) NOT NULL `JPATH`='address.street', + `address_zipcode` char(5) NOT NULL `JPATH`='address.zipcode', + `borough` char(13) NOT NULL, + `grades_date` bigint(13) DEFAULT NULL `JPATH`='grades.0.date', + `grades_grade` char(14) DEFAULT NULL `JPATH`='grades.0.grade', + `grades_score` int(2) DEFAULT NULL `JPATH`='grades.0.score', + `name` char(98) NOT NULL, + `restaurant_id` char(8) NOT NULL +) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='mongodb://localhost:27017' `TABLE_TYPE`='BSON' `TABNAME`='restaurants' `COLIST`='{"cuisine":0}' `FILTER`='{"cuisine":"French","borough":{"$ne":"Manhattan"}}' `OPTION_LIST`='Driver=Java,level=2,version=3' `LRECL`=4096 +SELECT name, borough, address_street, grades_score AS score FROM t1 WHERE grades_grade = 'B'; +name borough address_street score +Le Gamin Brooklyn Vanderbilt Avenue 24 +Bistro 33 Queens Ditmars Boulevard 15 +Dirty Pierres Bistro Queens Station Square 22 +Santos Anne Brooklyn Union Avenue 26 +Le Paddock Brooklyn Prospect Avenue 17 +La Crepe Et La Vie Brooklyn Foster Avenue 24 +Francis Cafe Queens Ditmars Boulevard 19 +DROP TABLE t1; +# +# try CRUD operations +# +false +CREATE TABLE t1 (_id INT(4) NOT NULL, msg CHAR(64)) +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME='testcoll' +OPTION_LIST='Driver=Java,Version=3' CONNECTION='mongodb://localhost:27017' LRECL=4096; +DELETE FROM t1; +INSERT INTO t1 VALUES(0,NULL),(1,'One'),(2,'Two'),(3,'Three'); +SELECT * FROM t1; +_id msg +0 NULL +1 One +2 Two +3 Three +UPDATE t1 SET msg = 'Deux' WHERE _id = 2; +DELETE FROM t1 WHERE msg IS NULL; +SELECT * FROM t1; +_id msg +1 One +2 Deux +3 Three +DELETE FROM t1; +DROP TABLE t1; +true +# +# List states whose population is equal or more than 10 millions +# +false +CREATE TABLE t1 ( +_id char(5) NOT NULL, +city char(16) NOT NULL, +loc_0 double(12,6) NOT NULL `JPATH`='loc.0', +loc_1 char(12) NOT NULL `JPATH`='loc.1', +pop int(11) NOT NULL, +state char(2) NOT NULL) +ENGINE=CONNECT CONNECTION='mongodb://localhost:27017' TABLE_TYPE=BSON TABNAME='cities' +OPTION_LIST='Driver=Java,Version=3' CONNECTION='mongodb://localhost:27017' LRECL=4096 DATA_CHARSET='utf8'; +# Using SQL for grouping +SELECT state, sum(pop) AS totalPop FROM t1 GROUP BY state HAVING totalPop >= 10000000 ORDER BY totalPop DESC; +state totalPop +CA 29754890 +NY 17990402 +TX 16984601 +FL 12686644 +PA 11881643 +IL 11427576 +OH 10846517 +DROP TABLE t1; +# Using a pipeline for grouping +CREATE TABLE t1 (_id CHAR(2) NOT NULL, totalPop INT(11) NOT NULL) +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME='cities' DATA_CHARSET=utf8 +COLIST='{"pipeline":[{"$group":{"_id":"$state","totalPop":{"$sum":"$pop"}}},{"$match":{"totalPop":{"$gte":10000000}}},{"$sort":{"totalPop":-1}}]}' +OPTION_LIST='Driver=Java,Version=3,Pipeline=1' CONNECTION='mongodb://localhost:27017' LRECL=4096; +SELECT * FROM t1; +_id totalPop +CA 29754890 +NY 17990402 +TX 16984601 +FL 12686644 +PA 11881643 +IL 11427576 +OH 10846517 +DROP TABLE t1; +true +# +# Test making array +# +CREATE TABLE t1 ( +_id int(4) NOT NULL, +item CHAR(8) NOT NULL, +prices_0 INT(6) JPATH='prices.0', +prices_1 INT(6) JPATH='prices.1', +prices_2 INT(6) JPATH='prices.2', +prices_3 INT(6) JPATH='prices.3', +prices_4 INT(6) JPATH='prices.4') +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME='testcoll' DATA_CHARSET=utf8 +OPTION_LIST='Driver=Java,Version=3' CONNECTION='mongodb://localhost:27017' LRECL=4096; +INSERT INTO t1 VALUES +(1,'journal',87,45,63,12,78), +(2,'notebook',123,456,789,NULL,NULL), +(3,'paper',5,7,3,8,NULL), +(4,'planner',25,71,NULL,44,27), +(5,'postcard',5,7,3,8,NULL); +SELECT * FROM t1; +_id item prices_0 prices_1 prices_2 prices_3 prices_4 +1 journal 87 45 63 12 78 +2 notebook 123 456 789 NULL NULL +3 paper 5 7 3 8 NULL +4 planner 25 71 NULL 44 27 +5 postcard 5 7 3 8 NULL +DROP TABLE t1; +# +# Test array aggregation +# +CREATE TABLE t1 +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME='testcoll' +COLIST='{"pipeline":[{"$project":{"_id":0,"item":1,"total":{"$sum":"$prices"},"average":{"$avg":"$prices"}}}]}' +OPTION_LIST='Driver=Java,Version=3,Pipeline=YES' CONNECTION='mongodb://localhost:27017' LRECL=4096; +SELECT * FROM t1; +item total average +journal 285 57.00 +notebook 1368 456.00 +paper 23 5.75 +planner 167 41.75 +postcard 23 5.75 +DROP TABLE t1; +true +set connect_enable_mongo=0; diff --git a/storage/connect/mysql-test/connect/r/bson_mongo_c.result b/storage/connect/mysql-test/connect/r/bson_mongo_c.result new file mode 100644 index 00000000000..83bf7cd1974 --- /dev/null +++ b/storage/connect/mysql-test/connect/r/bson_mongo_c.result @@ -0,0 +1,385 @@ +set connect_enable_mongo=1; +set connect_json_all_path=0; +# +# Test the MONGO table type +# +CREATE TABLE t1 (Document varchar(1024) JPATH='*') +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME=restaurants CONNECTION='mongodb://localhost:27017' LRECL=1024 +OPTION_LIST='Driver=C,Version=0' DATA_CHARSET=utf8; +SELECT * from t1 limit 3; +Document +{"_id":{"$oid":"58ada47de5a51ddfcd5ed51c"},"address":{"building":"1007","coord":[-73.8560769999999991,40.8484470000000002],"street":"Morris Park Ave","zipcode":"10462"},"borough":"Bronx","cuisine":"Bakery","grades":[{"date":{"$date":1393804800000},"grade":"A","score":2},{"date":{"$date":1378857600000},"grade":"A","score":6},{"date":{"$date":1358985600000},"grade":"A","score":10},{"date":{"$date":1322006400000},"grade":"A","score":9},{"date":{"$date":1299715200000},"grade":"B","score":14}],"name":"Morris Park Bake Shop","restaurant_id":"30075445"} +{"_id":{"$oid":"58ada47de5a51ddfcd5ed51d"},"address":{"building":"469","coord":[-73.9617039999999974,40.6629420000000010],"street":"Flatbush Avenue","zipcode":"11225"},"borough":"Brooklyn","cuisine":"Hamburgers","grades":[{"date":{"$date":1419897600000},"grade":"A","score":8},{"date":{"$date":1404172800000},"grade":"B","score":23},{"date":{"$date":1367280000000},"grade":"A","score":12},{"date":{"$date":1336435200000},"grade":"A","score":12}],"name":"Wendy'S","restaurant_id":"30112340"} +{"_id":{"$oid":"58ada47de5a51ddfcd5ed51e"},"address":{"building":"351","coord":[-73.9851355999999925,40.7676919000000026],"street":"West 57 Street","zipcode":"10019"},"borough":"Manhattan","cuisine":"Irish","grades":[{"date":{"$date":1409961600000},"grade":"A","score":2},{"date":{"$date":1374451200000},"grade":"A","score":11},{"date":{"$date":1343692800000},"grade":"A","score":12},{"date":{"$date":1325116800000},"grade":"A","score":12}],"name":"Dj Reynolds Pub And Restaurant","restaurant_id":"30191841"} +DROP TABLE t1; +# +# Test catfunc +# +CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=BSON TABNAME=restaurants CATFUNC=columns +OPTION_LIST='Depth=1,Driver=C,Version=0' DATA_CHARSET=utf8 CONNECTION='mongodb://localhost:27017' LRECL=1024; +SELECT * from t1; +Column_Name Data_Type Type_Name Column_Size Buffer_Length Decimal_Digits Nullable Jpath +_id 1 CHAR 24 24 0 0 _id +address_building 1 CHAR 10 10 0 0 address.building +address_coord 1 CHAR 1024 1024 0 1 address.coord +address_street 1 CHAR 38 38 0 0 address.street +address_zipcode 1 CHAR 5 5 0 0 address.zipcode +borough 1 CHAR 13 13 0 0 +cuisine 1 CHAR 64 64 0 0 +grades_date 1 CHAR 1024 1024 0 1 grades.0.date +grades_grade 1 CHAR 14 14 0 1 grades.0.grade +grades_score 7 INTEGER 2 2 0 1 grades.0.score +name 1 CHAR 98 98 0 0 +restaurant_id 1 CHAR 8 8 0 0 +DROP TABLE t1; +# +# Explicit columns +# +CREATE TABLE t1 ( +_id VARCHAR(24) NOT NULL, +name VARCHAR(255) NOT NULL, +cuisine VARCHAR(255) NOT NULL, +borough VARCHAR(255) NOT NULL, +restaurant_id VARCHAR(255) NOT NULL) +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME=restaurants +CONNECTION='mongodb://localhost:27017' LRECL=1024 DATA_CHARSET=utf8 +OPTION_LIST='Driver=C,Version=0'; +SELECT * FROM t1 LIMIT 10; +_id name cuisine borough restaurant_id +58ada47de5a51ddfcd5ed51c Morris Park Bake Shop Bakery Bronx 30075445 +58ada47de5a51ddfcd5ed51d Wendy'S Hamburgers Brooklyn 30112340 +58ada47de5a51ddfcd5ed51e Dj Reynolds Pub And Restaurant Irish Manhattan 30191841 +58ada47de5a51ddfcd5ed51f Riviera Caterer American Brooklyn 40356018 +58ada47de5a51ddfcd5ed520 Tov Kosher Kitchen Jewish/Kosher Queens 40356068 +58ada47de5a51ddfcd5ed521 Brunos On The Boulevard American Queens 40356151 +58ada47de5a51ddfcd5ed522 Kosher Island Jewish/Kosher Staten Island 40356442 +58ada47de5a51ddfcd5ed523 Wilken'S Fine Food Delicatessen Brooklyn 40356483 +58ada47de5a51ddfcd5ed524 Regina Caterers American Brooklyn 40356649 +58ada47de5a51ddfcd5ed525 Taste The Tropics Ice Cream Ice Cream, Gelato, Yogurt, Ices Brooklyn 40356731 +DROP TABLE t1; +# +# Test discovery +# +CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=BSON TABNAME=restaurants +OPTION_LIST='Depth=1,Driver=C,Version=0' CONNECTION='mongodb://localhost:27017' LRECL=1024 DATA_CHARSET=utf8; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `_id` char(24) NOT NULL `JPATH`='_id', + `address_building` char(10) NOT NULL `JPATH`='address.building', + `address_coord` varchar(1024) DEFAULT NULL `JPATH`='address.coord', + `address_street` char(38) NOT NULL `JPATH`='address.street', + `address_zipcode` char(5) NOT NULL `JPATH`='address.zipcode', + `borough` char(13) NOT NULL, + `cuisine` char(64) NOT NULL, + `grades_date` varchar(1024) DEFAULT NULL `JPATH`='grades.0.date', + `grades_grade` char(14) DEFAULT NULL `JPATH`='grades.0.grade', + `grades_score` int(2) DEFAULT NULL `JPATH`='grades.0.score', + `name` char(98) NOT NULL, + `restaurant_id` char(8) NOT NULL +) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='mongodb://localhost:27017' `TABLE_TYPE`='BSON' `TABNAME`='restaurants' `OPTION_LIST`='Depth=1,Driver=C,Version=0' `DATA_CHARSET`='utf8' `LRECL`=1024 +SELECT * FROM t1 LIMIT 5; +_id address_building address_coord address_street address_zipcode borough cuisine grades_date grades_grade grades_score name restaurant_id +58ada47de5a51ddfcd5ed51c 1007 -73.8560769999999991, 40.8484470000000002 Morris Park Ave 10462 Bronx Bakery 1393804800 A 2 Morris Park Bake Shop 30075445 +58ada47de5a51ddfcd5ed51d 469 -73.9617039999999974, 40.6629420000000010 Flatbush Avenue 11225 Brooklyn Hamburgers 1419897600 A 8 Wendy'S 30112340 +58ada47de5a51ddfcd5ed51e 351 -73.9851355999999925, 40.7676919000000026 West 57 Street 10019 Manhattan Irish 1409961600 A 2 Dj Reynolds Pub And Restaurant 30191841 +58ada47de5a51ddfcd5ed51f 2780 -73.9824199999999905, 40.5795049999999975 Stillwell Avenue 11224 Brooklyn American 1402358400 A 5 Riviera Caterer 40356018 +58ada47de5a51ddfcd5ed520 97-22 -73.8601151999999956, 40.7311739000000017 63 Road 11374 Queens Jewish/Kosher 1416787200 Z 20 Tov Kosher Kitchen 40356068 +DROP TABLE t1; +# +# Dropping a column +# +CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=BSON TABNAME=restaurants DATA_CHARSET=utf8 +COLIST='{"projection":{"grades":0}}' OPTION_LIST='Driver=C,Version=0,level=0' CONNECTION='mongodb://localhost:27017' LRECL=1024; +SELECT * FROM t1 LIMIT 10; +_id address borough cuisine name restaurant_id +58ada47de5a51ddfcd5ed51c 1007 (-73.8560769999999991, 40.8484470000000002) Morris Park Ave 10462 Bronx Bakery Morris Park Bake Shop 30075445 +58ada47de5a51ddfcd5ed51d 469 (-73.9617039999999974, 40.6629420000000010) Flatbush Avenue 11225 Brooklyn Hamburgers Wendy'S 30112340 +58ada47de5a51ddfcd5ed51e 351 (-73.9851355999999925, 40.7676919000000026) West 57 Street 10019 Manhattan Irish Dj Reynolds Pub And Restaurant 30191841 +58ada47de5a51ddfcd5ed51f 2780 (-73.9824199999999905, 40.5795049999999975) Stillwell Avenue 11224 Brooklyn American Riviera Caterer 40356018 +58ada47de5a51ddfcd5ed520 97-22 (-73.8601151999999956, 40.7311739000000017) 63 Road 11374 Queens Jewish/Kosher Tov Kosher Kitchen 40356068 +58ada47de5a51ddfcd5ed521 8825 (-73.8803826999999984, 40.7643124000000014) Astoria Boulevard 11369 Queens American Brunos On The Boulevard 40356151 +58ada47de5a51ddfcd5ed522 2206 (-74.1377286000000026, 40.6119571999999991) Victory Boulevard 10314 Staten Island Jewish/Kosher Kosher Island 40356442 +58ada47de5a51ddfcd5ed523 7114 (-73.9068505999999985, 40.6199033999999983) Avenue U 11234 Brooklyn Delicatessen Wilken'S Fine Food 40356483 +58ada47de5a51ddfcd5ed524 6409 (-74.0052889999999906, 40.6288860000000014) 11 Avenue 11219 Brooklyn American Regina Caterers 40356649 +58ada47de5a51ddfcd5ed525 1839 (-73.9482608999999940, 40.6408271000000028) Nostrand Avenue 11226 Brooklyn Ice Cream, Gelato, Yogurt, Ices Taste The Tropics Ice Cream 40356731 +DROP TABLE t1; +# +# Specifying Jpath +# +CREATE TABLE t1 ( +_id VARCHAR(24) NOT NULL, +name VARCHAR(64) NOT NULL, +cuisine CHAR(200) NOT NULL, +borough CHAR(16) NOT NULL, +street VARCHAR(65) JPATH='address.street', +building CHAR(16) JPATH='address.building', +zipcode CHAR(5) JPATH='address.zipcode', +grade CHAR(1) JPATH='grades.0.grade', +score INT(4) NOT NULL JPATH='grades.0.score', +`date` DATE JPATH='grades.0.date', +restaurant_id VARCHAR(255) NOT NULL) +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME='restaurants' DATA_CHARSET=utf8 +OPTION_LIST='Driver=C,Version=0' CONNECTION='mongodb://localhost:27017' LRECL=1024; +SELECT * FROM t1 LIMIT 1; +_id 58ada47de5a51ddfcd5ed51c +name Morris Park Bake Shop +cuisine Bakery +borough Bronx +street Morris Park Ave +building 1007 +zipcode 10462 +grade A +score 2 +date 2014-03-03 +restaurant_id 30075445 +SELECT name, street, score, date FROM t1 LIMIT 5; +name street score date +Morris Park Bake Shop Morris Park Ave 2 2014-03-03 +Wendy'S Flatbush Avenue 8 2014-12-30 +Dj Reynolds Pub And Restaurant West 57 Street 2 2014-09-06 +Riviera Caterer Stillwell Avenue 5 2014-06-10 +Tov Kosher Kitchen 63 Road 20 2014-11-24 +SELECT name, cuisine, borough FROM t1 WHERE grade = 'A' LIMIT 10; +name cuisine borough +Morris Park Bake Shop Bakery Bronx +Wendy'S Hamburgers Brooklyn +Dj Reynolds Pub And Restaurant Irish Manhattan +Riviera Caterer American Brooklyn +Kosher Island Jewish/Kosher Staten Island +Wilken'S Fine Food Delicatessen Brooklyn +Regina Caterers American Brooklyn +Taste The Tropics Ice Cream Ice Cream, Gelato, Yogurt, Ices Brooklyn +Wild Asia American Bronx +C & C Catering Service American Brooklyn +SELECT COUNT(*) FROM t1 WHERE grade = 'A'; +COUNT(*) +20687 +SELECT * FROM t1 WHERE cuisine = 'English'; +_id name cuisine borough street building zipcode grade score date restaurant_id +58ada47de5a51ddfcd5ed83d Tea And Sympathy English Manhattan Greenwich Avenue 108 10011 A 8 2014-10-23 40391531 +58ada47de5a51ddfcd5ed85c Tartine English Manhattan West 11 Street 253 10014 A 11 2014-08-14 40392496 +58ada47de5a51ddfcd5ee1f3 The Park Slope Chipshop English Brooklyn 5 Avenue 383 11215 B 17 2014-09-29 40816202 +58ada47de5a51ddfcd5ee7e4 Pound And Pence English Manhattan Liberty Street 55 10005 A 7 2014-02-11 41022701 +58ada47de5a51ddfcd5ee999 Chip Shop English Brooklyn Atlantic Avenue 129 11201 A 9 2014-10-08 41076583 +58ada47ee5a51ddfcd5efe3f The Breslin Bar & Dining Room English Manhattan West 29 Street 16 10001 A 13 2014-06-09 41443706 +58ada47ee5a51ddfcd5efe99 Highlands Restaurant English Manhattan West 10 Street 150 10014 A 12 2014-10-22 41448559 +58ada47ee5a51ddfcd5f0413 The Fat Radish English Manhattan Orchard Street 17 10002 A 12 2014-07-26 41513545 +58ada47ee5a51ddfcd5f0777 Jones Wood Foundry English Manhattan East 76 Street 401 10021 A 12 2014-12-03 41557377 +58ada47ee5a51ddfcd5f0ea2 Whitehall English Manhattan Greenwich Avenue 19 10014 Z 15 2015-01-16 41625263 +58ada47ee5a51ddfcd5f1004 The Churchill Tavern English Manhattan East 28 Street 45 10016 A 13 2014-08-27 41633327 +58ada47ee5a51ddfcd5f13d5 The Monro English Brooklyn 5 Avenue 481 11215 A 7 2014-06-03 41660253 +58ada47ee5a51ddfcd5f1454 The Cock & Bull English Manhattan West 45 Street 23 10036 A 7 2014-08-07 41664704 +58ada47ee5a51ddfcd5f176e Dear Bushwick English Brooklyn Wilson Avenue 41 11237 A 12 2014-12-27 41690534 +58ada47ee5a51ddfcd5f1e91 Snowdonia Pub English Queens 32 Street 34-55 11106 A 12 2014-10-28 50000290 +58ada47ee5a51ddfcd5f2ddc Oscar'S Place English Manhattan Hudson Street 466 10014 A 10 2014-08-18 50011097 +SELECT * FROM t1 WHERE score = building; +_id name cuisine borough street building zipcode grade score date restaurant_id +DROP TABLE t1; +# +# Specifying Filter +# +CREATE TABLE t1 ( +_id CHAR(24) NOT NULL, +name CHAR(64) NOT NULL, +borough CHAR(16) NOT NULL, +restaurant_id CHAR(8) NOT NULL) +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME=restaurants DATA_CHARSET=utf8 +FILTER='{"cuisine":"French","borough":{"$ne":"Manhattan"}}' +OPTION_LIST='Driver=C,Version=0' CONNECTION='mongodb://localhost:27017' LRECL=1024; +SELECT name FROM t1 WHERE borough = 'Queens'; +name +La Baraka Restaurant +Air France Lounge +Tournesol +Winegasm +Cafe Henri +Bistro 33 +Domaine Wine Bar +Cafe Triskell +Cannelle Patisserie +La Vie +Dirty Pierres Bistro +Fresca La Crepe +Bliss 46 Bistro +Bear +Cuisine By Claudette +Paris Baguette +The Baroness Bar +Francis Cafe +Madame Sou Sou +Crepe 'N' Tearia +Aperitif Bayside Llc +DROP TABLE t1; +# +# Testing pipeline +# +CREATE TABLE t1 ( +name VARCHAR(64) NOT NULL, +borough CHAR(16) NOT NULL, +date DATETIME NOT NULL, +grade CHAR(1) NOT NULL, +score INT(4) NOT NULL) +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME='restaurants' DATA_CHARSET=utf8 +COLIST='{"pipeline":[{"$match":{"cuisine":"French"}},{"$unwind":"$grades"},{"$project":{"_id":0,"name":1,"borough":1,"date":"$grades.date","grade":"$grades.grade","score":"$grades.score"}}]}' +OPTION_LIST='Driver=C,Version=0,Pipeline=1' CONNECTION='mongodb://localhost:27017' LRECL=1024; +SELECT * FROM t1 LIMIT 10; +name borough date grade score +Tout Va Bien Manhattan 2014-11-10 01:00:00 B 15 +Tout Va Bien Manhattan 2014-04-03 02:00:00 A 13 +Tout Va Bien Manhattan 2013-07-17 02:00:00 C 36 +Tout Va Bien Manhattan 2013-02-06 01:00:00 B 22 +Tout Va Bien Manhattan 2012-07-16 02:00:00 C 36 +Tout Va Bien Manhattan 2012-03-08 01:00:00 C 7 +La Grenouille Manhattan 2014-04-09 02:00:00 A 10 +La Grenouille Manhattan 2013-03-05 01:00:00 A 9 +La Grenouille Manhattan 2012-02-02 01:00:00 A 13 +Le Perigord Manhattan 2014-07-14 02:00:00 B 14 +SELECT name, grade, score, date FROM t1 WHERE borough = 'Bronx'; +name grade score date +Bistro Sk A 10 2014-11-21 01:00:00 +Bistro Sk A 12 2014-02-19 01:00:00 +Bistro Sk B 18 2013-06-12 02:00:00 +DROP TABLE t1; +# +# try level 2 discovery +# +CREATE TABLE t1 +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME=restaurants +FILTER='{"cuisine":"French","borough":{"$ne":"Manhattan"}}' +COLIST='{"projection":{"cuisine":0}}' CONNECTION='mongodb://localhost:27017' LRECL=1024 +OPTION_LIST='Driver=C,level=2,version=0'; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `_id` char(24) NOT NULL `JPATH`='_id', + `address_building` char(10) NOT NULL `JPATH`='address.building', + `address_coord` double(21,16) DEFAULT NULL `JPATH`='address.coord.0', + `address_street` char(38) NOT NULL `JPATH`='address.street', + `address_zipcode` char(5) NOT NULL `JPATH`='address.zipcode', + `borough` char(13) NOT NULL, + `grades_date` bigint(13) DEFAULT NULL `JPATH`='grades.0.date', + `grades_grade` char(14) DEFAULT NULL `JPATH`='grades.0.grade', + `grades_score` int(2) DEFAULT NULL `JPATH`='grades.0.score', + `name` char(98) NOT NULL, + `restaurant_id` char(8) NOT NULL +) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='mongodb://localhost:27017' `TABLE_TYPE`='BSON' `TABNAME`='restaurants' `COLIST`='{"projection":{"cuisine":0}}' `FILTER`='{"cuisine":"French","borough":{"$ne":"Manhattan"}}' `OPTION_LIST`='Driver=C,level=2,version=0' `LRECL`=1024 +SELECT name, borough, address_street, grades_score AS score FROM t1 WHERE grades_grade = 'B'; +name borough address_street score +Le Gamin Brooklyn Vanderbilt Avenue 24 +Bistro 33 Queens Ditmars Boulevard 15 +Dirty Pierres Bistro Queens Station Square 22 +Santos Anne Brooklyn Union Avenue 26 +Le Paddock Brooklyn Prospect Avenue 17 +La Crepe Et La Vie Brooklyn Foster Avenue 24 +Francis Cafe Queens Ditmars Boulevard 19 +DROP TABLE t1; +# +# try CRUD operations +# +false +CREATE TABLE t1 (_id INT(4) NOT NULL, msg CHAR(64)) +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME='testcoll' +OPTION_LIST='Driver=C,Version=0' CONNECTION='mongodb://localhost:27017' LRECL=1024; +DELETE FROM t1; +INSERT INTO t1 VALUES(0,NULL),(1,'One'),(2,'Two'),(3,'Three'); +SELECT * FROM t1; +_id msg +0 NULL +1 One +2 Two +3 Three +UPDATE t1 SET msg = 'Deux' WHERE _id = 2; +DELETE FROM t1 WHERE msg IS NULL; +SELECT * FROM t1; +_id msg +1 One +2 Deux +3 Three +DELETE FROM t1; +DROP TABLE t1; +true +# +# List states whose population is equal or more than 10 millions +# +false +CREATE TABLE t1 ( +_id char(5) NOT NULL, +city char(16) NOT NULL, +loc_0 double(12,6) NOT NULL `JPATH`='loc.0', +loc_1 char(12) NOT NULL `JPATH`='loc.1', +pop int(11) NOT NULL, +state char(2) NOT NULL) +ENGINE=CONNECT CONNECTION='mongodb://localhost:27017' TABLE_TYPE=BSON TABNAME='cities' +OPTION_LIST='Driver=C,Version=0' CONNECTION='mongodb://localhost:27017' LRECL=1024 DATA_CHARSET='utf8'; +# Using SQL for grouping +SELECT state, sum(pop) AS totalPop FROM t1 GROUP BY state HAVING totalPop >= 10000000 ORDER BY totalPop DESC; +state totalPop +CA 29754890 +NY 17990402 +TX 16984601 +FL 12686644 +PA 11881643 +IL 11427576 +OH 10846517 +DROP TABLE t1; +# Using a pipeline for grouping +CREATE TABLE t1 (_id CHAR(2) NOT NULL, totalPop INT(11) NOT NULL) +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME='cities' DATA_CHARSET=utf8 +COLIST='{"pipeline":[{"$group":{"_id":"$state","totalPop":{"$sum":"$pop"}}},{"$match":{"totalPop":{"$gte":10000000}}},{"$sort":{"totalPop":-1}}]}' +OPTION_LIST='Driver=C,Version=0,Pipeline=1' CONNECTION='mongodb://localhost:27017' LRECL=1024; +SELECT * FROM t1; +_id totalPop +CA 29754890 +NY 17990402 +TX 16984601 +FL 12686644 +PA 11881643 +IL 11427576 +OH 10846517 +DROP TABLE t1; +true +# +# Test making array +# +CREATE TABLE t1 ( +_id int(4) NOT NULL, +item CHAR(8) NOT NULL, +prices_0 INT(6) JPATH='prices.0', +prices_1 INT(6) JPATH='prices.1', +prices_2 INT(6) JPATH='prices.2', +prices_3 INT(6) JPATH='prices.3', +prices_4 INT(6) JPATH='prices.4') +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME='testcoll' DATA_CHARSET=utf8 +OPTION_LIST='Driver=C,Version=0' CONNECTION='mongodb://localhost:27017' LRECL=1024; +INSERT INTO t1 VALUES +(1,'journal',87,45,63,12,78), +(2,'notebook',123,456,789,NULL,NULL), +(3,'paper',5,7,3,8,NULL), +(4,'planner',25,71,NULL,44,27), +(5,'postcard',5,7,3,8,NULL); +SELECT * FROM t1; +_id item prices_0 prices_1 prices_2 prices_3 prices_4 +1 journal 87 45 63 12 78 +2 notebook 123 456 789 NULL NULL +3 paper 5 7 3 8 NULL +4 planner 25 71 44 27 NULL +5 postcard 5 7 3 8 NULL +DROP TABLE t1; +# +# Test array aggregation +# +CREATE TABLE t1 +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME='testcoll' +COLIST='{"pipeline":[{"$project":{"_id":0,"item":1,"total":{"$sum":"$prices"},"average":{"$avg":"$prices"}}}]}' +OPTION_LIST='Driver=C,Version=0,Pipeline=YES' CONNECTION='mongodb://localhost:27017' LRECL=1024; +SELECT * FROM t1; +item total average +journal 285 57.00 +notebook 1368 456.00 +paper 23 5.75 +planner 167 41.75 +postcard 23 5.75 +DROP TABLE t1; +true +set connect_enable_mongo=0; diff --git a/storage/connect/mysql-test/connect/t/bson.test b/storage/connect/mysql-test/connect/t/bson.test new file mode 100644 index 00000000000..ab38cab73fc --- /dev/null +++ b/storage/connect/mysql-test/connect/t/bson.test @@ -0,0 +1,294 @@ +--source include/not_embedded.inc +--source include/have_partition.inc + +let $MYSQLD_DATADIR= `select @@datadir`; + +--copy_file $MTR_SUITE_DIR/std_data/biblio.json $MYSQLD_DATADIR/test/biblio.json +--copy_file $MTR_SUITE_DIR/std_data/bib0.json $MYSQLD_DATADIR/test/bib0.json +--copy_file $MTR_SUITE_DIR/std_data/expense.json $MYSQLD_DATADIR/test/expense.json +--copy_file $MTR_SUITE_DIR/std_data/mulexp3.json $MYSQLD_DATADIR/test/mulexp3.json +--copy_file $MTR_SUITE_DIR/std_data/mulexp4.json $MYSQLD_DATADIR/test/mulexp4.json +--copy_file $MTR_SUITE_DIR/std_data/mulexp5.json $MYSQLD_DATADIR/test/mulexp5.json + +--echo # +--echo # Testing doc samples +--echo # +CREATE TABLE t1 +( + ISBN CHAR(15), + LANG CHAR(2), + SUBJECT CHAR(32), + AUTHOR CHAR(64), + TITLE CHAR(32), + TRANSLATION CHAR(32), + TRANSLATOR CHAR(80), + PUBLISHER CHAR(32), + DATEPUB int(4) +) ENGINE=CONNECT TABLE_TYPE=BSON FILE_NAME='biblio.json'; +SELECT * FROM t1; +DROP TABLE t1; + + +--echo # +--echo # Testing Jpath. Get the number of authors +--echo # +CREATE TABLE t1 +( + ISBN CHAR(15), + Language CHAR(2) JPATH='$.LANG', + Subject CHAR(32) JPATH='$.SUBJECT', + Authors INT(2) JPATH='$.AUTHOR[#]', + Title CHAR(32) JPATH='$.TITLE', + Translation CHAR(32) JPATH='$.TRANSLATION', + Translator CHAR(80) JPATH='$.TRANSLATOR', + Publisher CHAR(20) JPATH='$.PUBLISHER.NAME', + Location CHAR(16) JPATH='$.PUBLISHER.PLACE', + Year int(4) JPATH='$.DATEPUB' +) +ENGINE=CONNECT TABLE_TYPE=BSON FILE_NAME='biblio.json'; +SELECT * FROM t1; +DROP TABLE t1; + +--echo # +--echo # Concatenates the authors +--echo # +CREATE TABLE t1 +( + ISBN CHAR(15), + Language CHAR(2) JPATH='$.LANG', + Subject CHAR(32) JPATH='$.SUBJECT', + AuthorFN CHAR(128) JPATH='$.AUTHOR[" and "].FIRSTNAME', + AuthorLN CHAR(128) JPATH='$.AUTHOR[" and "].LASTNAME', + Title CHAR(32) JPATH='$.TITLE', + Translation CHAR(32) JPATH='$.TRANSLATION', + Translator CHAR(80) JPATH='$.TRANSLATOR', + Publisher CHAR(20) JPATH='$.PUBLISHER.NAME', + Location CHAR(16) JPATH='$.PUBLISHER.PLACE', + Year int(4) JPATH='$.DATEPUB' +) +ENGINE=CONNECT TABLE_TYPE=BSON FILE_NAME='biblio.json'; +SELECT * FROM t1; +DROP TABLE t1; + +--echo # +--echo # Testing expanding authors +--echo # +CREATE TABLE t1 +( + ISBN CHAR(15), + Language CHAR(2) JPATH='$.LANG', + Subject CHAR(32) JPATH='$.SUBJECT', + AuthorFN CHAR(128) JPATH='$.AUTHOR[*].FIRSTNAME', + AuthorLN CHAR(128) JPATH='$.AUTHOR[*].LASTNAME', + Title CHAR(32) JPATH='$.TITLE', + Translation CHAR(32) JPATH='$.TRANSLATION', + Translator CHAR(80) JPATH='$.TRANSLATOR', + Publisher CHAR(20) JPATH='$.PUBLISHER.NAME', + Location CHAR(16) JPATH='$.PUBLISHER.PLACE', + Year int(4) JPATH='$.DATEPUB' +) +ENGINE=CONNECT TABLE_TYPE=BSON FILE_NAME='biblio.json'; +SELECT * FROM t1; +UPDATE t1 SET AuthorFN = 'Philippe' WHERE AuthorLN = 'Knab'; +SELECT * FROM t1 WHERE ISBN = '9782212090819'; + +--echo # +--echo # To add an author a new table must be created +--echo # +CREATE TABLE t2 ( +FIRSTNAME CHAR(32), +LASTNAME CHAR(32)) +ENGINE=CONNECT TABLE_TYPE=BSON FILE_NAME='biblio.json' OPTION_LIST='Object=$[1].AUTHOR'; +SELECT * FROM t2; +INSERT INTO t2 VALUES('Charles','Dickens'); +SELECT * FROM t1; +DROP TABLE t1; +DROP TABLE t2; + +--echo # +--echo # Check the biblio file has the good format +--echo # +CREATE TABLE t1 +( + line char(255) +) +ENGINE=CONNECT TABLE_TYPE=DOS FILE_NAME='biblio.json'; +SELECT * FROM t1; +DROP TABLE t1; + +--echo # +--echo # Testing a pretty=0 file +--echo # +CREATE TABLE t1 +( + ISBN CHAR(15) NOT NULL, + Language CHAR(2) JPATH='$.LANG', + Subject CHAR(32) JPATH='$.SUBJECT', + AuthorFN CHAR(128) JPATH='$.AUTHOR[*].FIRSTNAME', + AuthorLN CHAR(128) JPATH='$.AUTHOR[*].LASTNAME', + Title CHAR(32) JPATH='$.TITLE', + Translation CHAR(32) JPATH='$.TRANSLATED.PREFIX', + TranslatorFN CHAR(80) JPATH='$.TRANSLATED.TRANSLATOR.FIRSTNAME', + TranslatorLN CHAR(80) JPATH='$.TRANSLATED.TRANSLATOR.LASTNAME', + Publisher CHAR(20) JPATH='$.PUBLISHER.NAME', + Location CHAR(16) JPATH='$.PUBLISHER.PLACE', + Year int(4) JPATH='$.DATEPUB', + INDEX IX(ISBN) +) +ENGINE=CONNECT TABLE_TYPE=BSON FILE_NAME='bib0.json' LRECL=320 OPTION_LIST='Pretty=0'; +SHOW INDEX FROM t1; +SELECT * FROM t1; +DESCRIBE SELECT * FROM t1 WHERE ISBN = '9782212090819'; +--error ER_GET_ERRMSG +UPDATE t1 SET AuthorFN = 'Philippe' WHERE ISBN = '9782212090819'; +DROP TABLE t1; + +--echo # +--echo # A file with 2 arrays +--echo # +CREATE TABLE t1 ( +WHO CHAR(12), +WEEK INT(2) JPATH='$.WEEK[*].NUMBER', +WHAT CHAR(32) JPATH='$.WEEK[].EXPENSE["+"].WHAT', +AMOUNT DOUBLE(8,2) JPATH='$.WEEK[].EXPENSE[+].AMOUNT') +ENGINE=CONNECT TABLE_TYPE=BSON FILE_NAME='expense.json'; +SELECT * FROM t1; +DROP TABLE t1; + +--echo # +--echo # Now it can be fully expanded +--echo # +CREATE TABLE t1 ( +WHO CHAR(12), +WEEK INT(2) JPATH='$.WEEK[*].NUMBER', +WHAT CHAR(32) JPATH='$.WEEK[*].EXPENSE[*].WHAT', +AMOUNT DOUBLE(8,2) JPATH='$.WEEK[*].EXPENSE[*].AMOUNT') +ENGINE=CONNECT TABLE_TYPE=BSON FILE_NAME='expense.json'; +#--error ER_GET_ERRMSG +SELECT * FROM t1; +DROP TABLE t1; + +--echo # +--echo # A table showing many calculated results +--echo # +CREATE TABLE t1 ( +WHO CHAR(12) NOT NULL, +WEEKS CHAR(12) NOT NULL JPATH='$.WEEK[", "].NUMBER', +SUMS CHAR(64) NOT NULL JPATH='$.WEEK["+"].EXPENSE[+].AMOUNT', +SUM DOUBLE(8,2) NOT NULL JPATH='$.WEEK[+].EXPENSE[+].AMOUNT', +AVGS CHAR(64) NOT NULL JPATH='$.WEEK["+"].EXPENSE[!].AMOUNT', +SUMAVG DOUBLE(8,2) NOT NULL JPATH='$.WEEK[+].EXPENSE[!].AMOUNT', +AVGSUM DOUBLE(8,2) NOT NULL JPATH='$.WEEK[!].EXPENSE[+].AMOUNT', +AVGAVG DOUBLE(8,2) NOT NULL JPATH='$.WEEK[!].EXPENSE[!].AMOUNT', +AVERAGE DOUBLE(8,2) NOT NULL JPATH='$.WEEK[!].EXPENSE[*].AMOUNT') +ENGINE=CONNECT TABLE_TYPE=BSON FILE_NAME='expense.json'; +SELECT * FROM t1; +DROP TABLE t1; + +--echo # +--echo # Expand expense in 3 one week tables +--echo # +CREATE TABLE t2 ( +WHO CHAR(12), +WEEK INT(2) JPATH='$.WEEK[0].NUMBER', +WHAT CHAR(32) JPATH='$.WEEK[0].EXPENSE[*].WHAT', +AMOUNT DOUBLE(8,2) JPATH='$.WEEK[0].EXPENSE[*].AMOUNT') +ENGINE=CONNECT TABLE_TYPE=BSON FILE_NAME='expense.json'; +SELECT * FROM t2; + +CREATE TABLE t3 ( +WHO CHAR(12), +WEEK INT(2) JPATH='$.WEEK[1].NUMBER', +WHAT CHAR(32) JPATH='$.WEEK[1].EXPENSE[*].WHAT', +AMOUNT DOUBLE(8,2) JPATH='$.WEEK[1].EXPENSE[*].AMOUNT') +ENGINE=CONNECT TABLE_TYPE=BSON FILE_NAME='expense.json'; +SELECT * FROM t3; + +CREATE TABLE t4 ( +WHO CHAR(12), +WEEK INT(2) JPATH='$.WEEK[2].NUMBER', +WHAT CHAR(32) JPATH='$.WEEK[2].EXPENSE[*].WHAT', +AMOUNT DOUBLE(8,2) JPATH='$.WEEK[2].EXPENSE[*].AMOUNT') +ENGINE=CONNECT TABLE_TYPE=BSON FILE_NAME='expense.json'; +SELECT * FROM t4; + +--echo # +--echo # The expanded table is made as a TBL table +--echo # +CREATE TABLE t1 ( +WHO CHAR(12), +WEEK INT(2), +WHAT CHAR(32), +AMOUNT DOUBLE(8,2)) +ENGINE=CONNECT TABLE_TYPE=TBL TABLE_LIST='t2,t3,t4'; +SELECT * FROM t1; +DROP TABLE t1, t2, t3, t4; + +--echo # +--echo # Three partial JSON tables +--echo # +CREATE TABLE t2 ( +WHO CHAR(12), +WEEK INT(2), +WHAT CHAR(32) JPATH='$.EXPENSE[*].WHAT', +AMOUNT DOUBLE(8,2) JPATH='$.EXPENSE.[*].AMOUNT') +ENGINE=CONNECT TABLE_TYPE=BSON FILE_NAME='mulexp3.json'; +SELECT * FROM t2; + +CREATE TABLE t3 ( +WHO CHAR(12), +WEEK INT(2), +WHAT CHAR(32) JPATH='$.EXPENSE[*].WHAT', +AMOUNT DOUBLE(8,2) JPATH='$.EXPENSE.[*].AMOUNT') +ENGINE=CONNECT TABLE_TYPE=BSON FILE_NAME='mulexp4.json'; +SELECT * FROM t3; + +CREATE TABLE t4 ( +WHO CHAR(12), +WEEK INT(2), +WHAT CHAR(32) JPATH='$.EXPENSE[*].WHAT', +AMOUNT DOUBLE(8,2) JPATH='$.EXPENSE.[*].AMOUNT') +ENGINE=CONNECT TABLE_TYPE=BSON FILE_NAME='mulexp5.json'; +SELECT * FROM t4; + +--echo # +--echo # The complete table can be a multiple JSON table +--echo # +CREATE TABLE t1 ( +WHO CHAR(12), +WEEK INT(2), +WHAT CHAR(32) JPATH='$.EXPENSE[*].WHAT', +AMOUNT DOUBLE(8,2) JPATH='$.EXPENSE.[*].AMOUNT') +ENGINE=CONNECT TABLE_TYPE=BSON FILE_NAME='mulexp*.json' MULTIPLE=1; +SELECT * FROM t1 ORDER BY WHO, WEEK, WHAT, AMOUNT; +DROP TABLE t1; + +--echo # +--echo # Or also a partition JSON table +--echo # +CREATE TABLE t1 ( +WHO CHAR(12), +WEEK INT(2), +WHAT CHAR(32) JPATH='$.EXPENSE[*].WHAT', +AMOUNT DOUBLE(8,2) JPATH='$.EXPENSE.[*].AMOUNT') +ENGINE=CONNECT TABLE_TYPE=BSON FILE_NAME='mulexp%s.json'; +ALTER TABLE t1 +PARTITION BY LIST COLUMNS(WEEK) ( +PARTITION `3` VALUES IN(3), +PARTITION `4` VALUES IN(4), +PARTITION `5` VALUES IN(5)); +SHOW WARNINGS; +SELECT * FROM t1; +SELECT * FROM t1 WHERE WEEK = 4; +DROP TABLE t1, t2, t3, t4; + +# +# Clean up +# +--remove_file $MYSQLD_DATADIR/test/biblio.json +--remove_file $MYSQLD_DATADIR/test/bib0.dnx +--remove_file $MYSQLD_DATADIR/test/bib0.json +--remove_file $MYSQLD_DATADIR/test/expense.json +--remove_file $MYSQLD_DATADIR/test/mulexp3.json +--remove_file $MYSQLD_DATADIR/test/mulexp4.json +--remove_file $MYSQLD_DATADIR/test/mulexp5.json diff --git a/storage/connect/mysql-test/connect/t/bson_java_2.test b/storage/connect/mysql-test/connect/t/bson_java_2.test new file mode 100644 index 00000000000..2188d9c2c91 --- /dev/null +++ b/storage/connect/mysql-test/connect/t/bson_java_2.test @@ -0,0 +1,14 @@ +-- source jdbconn.inc +-- source mongo.inc + +--disable_query_log +eval SET GLOBAL connect_class_path='$MTR_SUITE_DIR/std_data/Mongo2.jar'; +set connect_json_all_path=0; +--enable_query_log +let $DRV= Java; +let $VERS= 2; +let $TYPE= BSON; +let $CONN= CONNECTION='mongodb://localhost:27017' LRECL=4096; + +-- source mongo_test.inc +-- source jdbconn_cleanup.inc diff --git a/storage/connect/mysql-test/connect/t/bson_java_3.test b/storage/connect/mysql-test/connect/t/bson_java_3.test new file mode 100644 index 00000000000..e7dd90b3563 --- /dev/null +++ b/storage/connect/mysql-test/connect/t/bson_java_3.test @@ -0,0 +1,14 @@ +-- source jdbconn.inc +-- source mongo.inc + +--disable_query_log +eval SET GLOBAL connect_class_path='$MTR_SUITE_DIR/std_data/Mongo3.jar'; +set connect_json_all_path=0; +--enable_query_log +let $DRV= Java; +let $VERS= 3; +let $TYPE= BSON; +let $CONN= CONNECTION='mongodb://localhost:27017' LRECL=4096; + +-- source mongo_test.inc +-- source jdbconn_cleanup.inc diff --git a/storage/connect/mysql-test/connect/t/bson_mongo_c.test b/storage/connect/mysql-test/connect/t/bson_mongo_c.test new file mode 100644 index 00000000000..938d77c7c95 --- /dev/null +++ b/storage/connect/mysql-test/connect/t/bson_mongo_c.test @@ -0,0 +1,10 @@ +-- source mongo.inc + +let $DRV= C; +let $VERS= 0; +let $PROJ= {"projection":; +let $ENDP= }; +let $TYPE= BSON; +let $CONN= CONNECTION='mongodb://localhost:27017' LRECL=1024; + +-- source mongo_test.inc diff --git a/storage/connect/mysql-test/connect/t/mongo_test.inc b/storage/connect/mysql-test/connect/t/mongo_test.inc index 0a9c80f5ba5..6e7c78e81ac 100644 --- a/storage/connect/mysql-test/connect/t/mongo_test.inc +++ b/storage/connect/mysql-test/connect/t/mongo_test.inc @@ -126,6 +126,10 @@ IF ($TYPE == JSON) { SELECT name, borough, address_street, grades_score AS score FROM t1 WHERE grades_grade = 'B'; } +IF ($TYPE == BSON) +{ +SELECT name, borough, address_street, grades_score AS score FROM t1 WHERE grades_grade = 'B'; +} DROP TABLE t1; --echo # diff --git a/storage/connect/plugutil.cpp b/storage/connect/plugutil.cpp index c33639bf744..69d258d9fd3 100644 --- a/storage/connect/plugutil.cpp +++ b/storage/connect/plugutil.cpp @@ -474,8 +474,10 @@ bool AllocSarea(PGLOBAL g, size_t size) if (!g->Sarea) { sprintf(g->Message, MSG(MALLOC_ERROR), "malloc"); g->Sarea_Size = 0; - } else - g->Sarea_Size = size; + } else { + g->Sarea_Size = size; + PlugSubSet(g->Sarea, size); + } // endif Sarea #if defined(DEVELOPMENT) if (true) { @@ -484,7 +486,6 @@ bool AllocSarea(PGLOBAL g, size_t size) #endif if (g->Sarea) { htrc("Work area of %zd allocated at %p\n", size, g->Sarea); - PlugSubSet(g->Sarea, size); } else htrc("SareaAlloc: %s\n", g->Message); @@ -624,7 +625,7 @@ size_t MakeOff(void* memp, void* ptr) #if defined(_DEBUG) || defined(DEVELOPMENT) if (ptr <= memp) { fprintf(stderr, "ptr %p <= memp %p", ptr, memp); - throw 999; + DoThrow(999); } // endif ptr #endif // _DEBUG || DEVELOPMENT return (size_t)((char*)ptr - (size_t)memp); @@ -633,4 +634,4 @@ size_t MakeOff(void* memp, void* ptr) } /* end of MakeOff */ - /*--------------------- End of PLUGUTIL program -----------------------*/ +/*---------------------- End of PLUGUTIL program ------------------------*/ diff --git a/storage/connect/tabbson.cpp b/storage/connect/tabbson.cpp index 69dd5749122..309eef2e292 100644 --- a/storage/connect/tabbson.cpp +++ b/storage/connect/tabbson.cpp @@ -246,7 +246,8 @@ int BSONDISC::GetColumns(PGLOBAL g, PCSZ db, PCSZ dsn, PTOS topt) return 0; bp = tjsp->Bp; - bdp = tjsp->GetDoc() ? bp->GetBson(tjsp->GetDoc()) : NULL; +// bdp = tjsp->GetDoc() ? bp->GetBson(tjsp->GetDoc()) : NULL; + bdp = tjsp->GetDoc(); jsp = bdp ? bp->GetArrayValue(bdp, 0) : NULL; } else { if (!((tdp->Lrecl = GetIntegerTableOption(g, topt, "Lrecl", 0)))) { @@ -312,7 +313,7 @@ int BSONDISC::GetColumns(PGLOBAL g, PCSZ db, PCSZ dsn, PTOS topt) case RC_FX: goto err; default: - jsp = bp->FindRow(g); + jsp = tjnp->Row; } // endswitch ReadDB } // endif pretty @@ -362,7 +363,7 @@ int BSONDISC::GetColumns(PGLOBAL g, PCSZ db, PCSZ dsn, PTOS topt) case RC_FX: goto err; default: - jsp = bp->FindRow(g); + jsp = tjnp->Row; } // endswitch ReadDB } else @@ -400,21 +401,25 @@ bool BSONDISC::Find(PGLOBAL g, PBVAL jvp, PCSZ key, int j) jcol.Type = (JTYP)jvp->Type; switch (jvp->Type) { - case TYPE_STRG: - case TYPE_DTM: - jcol.Len = (int)strlen(bp->GetString(jvp)); - break; - case TYPE_INTG: - case TYPE_BINT: - case TYPE_DBL: - jcol.Len = (int)strlen(bp->GetString(jvp, buf)); - break; - case TYPE_BOOL: - jcol.Len = 1; - break; - default: - jcol.Len = 0; - break; + case TYPE_STRG: + case TYPE_DTM: + jcol.Len = (int)strlen(bp->GetString(jvp)); + break; + case TYPE_INTG: + case TYPE_BINT: + jcol.Len = (int)strlen(bp->GetString(jvp, buf)); + break; + case TYPE_DBL: + case TYPE_FLOAT: + jcol.Len = (int)strlen(bp->GetString(jvp, buf)); + jcol.Scale = jvp->Nd; + break; + case TYPE_BOOL: + jcol.Len = 1; + break; + default: + jcol.Len = 0; + break; } // endswitch Type jcol.Scale = jvp->Nd; @@ -513,7 +518,8 @@ bool BSONDISC::Find(PGLOBAL g, PBVAL jvp, PCSZ key, int j) return false; } // end of Find -void BSONDISC::AddColumn(PGLOBAL g) { +void BSONDISC::AddColumn(PGLOBAL g) +{ bool b = fmt[bf] != 0; // True if formatted // Check whether this column was already found diff --git a/storage/connect/tabjson.cpp b/storage/connect/tabjson.cpp index 4bddef1940e..0ef281f2aae 100644 --- a/storage/connect/tabjson.cpp +++ b/storage/connect/tabjson.cpp @@ -310,7 +310,8 @@ int JSONDISC::GetColumns(PGLOBAL g, PCSZ db, PCSZ dsn, PTOS topt) case RC_FX: goto err; default: - jsp = tjnp->FindRow(g); +// jsp = tjnp->FindRow(g); // FindRow was done in ReadDB + jsp = tjnp->Row; } // endswitch ReadDB } // endif pretty @@ -360,7 +361,8 @@ int JSONDISC::GetColumns(PGLOBAL g, PCSZ db, PCSZ dsn, PTOS topt) case RC_FX: goto err; default: - jsp = tjnp->FindRow(g); +// jsp = tjnp->FindRow(g); + jsp = tjnp->Row; } // endswitch ReadDB } else @@ -397,26 +399,26 @@ bool JSONDISC::Find(PGLOBAL g, PJVAL jvp, PCSZ key, int j) jcol.Type = jvp->DataType; - switch (jvp->DataType) { - case TYPE_STRG: - case TYPE_DTM: - jcol.Len = (int)strlen(jvp->Strp); - break; - case TYPE_INTG: - case TYPE_BINT: - jcol.Len = (int)strlen(jvp->GetString(g)); - break; - case TYPE_DBL: - jcol.Len = (int)strlen(jvp->GetString(g)); - jcol.Scale = jvp->Nd; - break; - case TYPE_BOOL: - jcol.Len = 1; - break; - default: - jcol.Len = 0; - break; - } // endswitch Type + switch (jvp->DataType) { + case TYPE_STRG: + case TYPE_DTM: + jcol.Len = (int)strlen(jvp->Strp); + break; + case TYPE_INTG: + case TYPE_BINT: + jcol.Len = (int)strlen(jvp->GetString(g)); + break; + case TYPE_DBL: + jcol.Len = (int)strlen(jvp->GetString(g)); + jcol.Scale = jvp->Nd; + break; + case TYPE_BOOL: + jcol.Len = 1; + break; + default: + jcol.Len = 0; + break; + } // endswitch Type jcol.Scale = jvp->Nd; jcol.Cbn = jvp->DataType == TYPE_NULL; |