diff options
17 files changed, 992 insertions, 687 deletions
diff --git a/.gitattributes b/.gitattributes index cdfedef671f..bb22871094a 100644 --- a/.gitattributes +++ b/.gitattributes @@ -1,3 +1,23 @@ +# Set the default behavior, in case people don't have core.autocrlf set. +* text=auto + +# Explicitly declare text files you want to always be normalized and converted +# to native line endings on checkout. +*.c text +*.cc text +*.cpp text +*.h text +*.test text + +# Declare files that will always have LF line endings on checkout. +*.result text eol=lf +storage/connect/mysql-test/connect/std_data/*.txt text eol=lf +storage/connect/mysql-test/connect/std_data/*.dat text eol=lf + +# Denote all files that are truly binary and should not be modified. +*.png binary +*.jpg binary + *.c diff=cpp *.h diff=cpp *.cc diff=cpp diff --git a/storage/connect/ha_connect.cc b/storage/connect/ha_connect.cc index 1cf18a94d22..d310ff55980 100644 --- a/storage/connect/ha_connect.cc +++ b/storage/connect/ha_connect.cc @@ -2173,12 +2173,12 @@ int ha_connect::CheckRecord(PGLOBAL g, const uchar *oldbuf, uchar *newbuf) /***********************************************************************/ /* Return the where clause for remote indexed read. */ /***********************************************************************/ -bool ha_connect::MakeKeyWhere(PGLOBAL g, char *qry, OPVAL op, char *q, +bool ha_connect::MakeKeyWhere(PGLOBAL g, PSTRG qry, OPVAL op, char q, const void *key, int klen) { const uchar *ptr; uint rem, len, stlen; //, prtlen; - bool nq, b= false; + bool nq, oom, b= false; Field *fp; KEY *kfp; KEY_PART_INFO *kpart; @@ -2190,7 +2190,7 @@ bool ha_connect::MakeKeyWhere(PGLOBAL g, char *qry, OPVAL op, char *q, return true; } // endif key - strcat(qry, " WHERE ("); + oom= qry->Append(" WHERE ("); kfp= &table->key_info[active_index]; rem= kfp->user_defined_key_parts, len= klen, @@ -2203,24 +2203,26 @@ bool ha_connect::MakeKeyWhere(PGLOBAL g, char *qry, OPVAL op, char *q, nq= fp->str_needs_quotes(); if (b) - strcat(qry, " AND "); + oom|= qry->Append(" AND "); else b= true; - strcat(strncat(strcat(qry, q), fp->field_name, strlen(fp->field_name)), q); + oom|= qry->Append(q); + oom|= qry->Append((PSZ)fp->field_name); + oom|= qry->Append(q); switch (op) { case OP_EQ: case OP_GT: case OP_GE: - strcat(qry, GetValStr(op, false)); + oom|= qry->Append((PSZ)GetValStr(op, false)); break; default: - strcat(qry, " ??? "); + oom|= qry->Append(" ??? "); } // endwitch op if (nq) - strcat(qry, "'"); + oom|= qry->Append('\''); if (kpart->key_part_flag & HA_VAR_LENGTH_PART) { String varchar; @@ -2228,17 +2230,17 @@ bool ha_connect::MakeKeyWhere(PGLOBAL g, char *qry, OPVAL op, char *q, varchar.set_quick((char*) ptr+HA_KEY_BLOB_LENGTH, var_length, &my_charset_bin); - strncat(qry, varchar.ptr(), varchar.length()); + oom|= qry->Append(varchar.ptr(), varchar.length()); } else { char strbuff[MAX_FIELD_WIDTH]; String str(strbuff, sizeof(strbuff), kpart->field->charset()), *res; res= fp->val_str(&str, ptr); - strncat(qry, res->ptr(), res->length()); + oom|= qry->Append(res->ptr(), res->length()); } // endif flag if (nq) - strcat(qry, "'"); + oom|= qry->Append('\''); if (stlen >= len) break; @@ -2251,8 +2253,10 @@ bool ha_connect::MakeKeyWhere(PGLOBAL g, char *qry, OPVAL op, char *q, ptr+= stlen - MY_TEST(kpart->null_bit); } // endfor kpart - strcat(qry, ")"); - return false; + if ((oom|= qry->Append(")"))) + strcpy(g->Message, "Out of memory"); + + return oom; } // end of MakeKeyWhere @@ -2714,7 +2718,12 @@ PCFIL ha_connect::CheckCond(PGLOBAL g, PCFIL filp, AMT tty, Item *cond) case MYSQL_TYPE_DATETIME: if (tty == TYPE_AM_ODBC) { strcat(body, "{ts '"); - strcat(strncat(body, res->ptr(), res->length()), "'}"); + strncat(body, res->ptr(), res->length()); + + if (res->length() < 19) + strcat(body, "1970-01-01 00:00:00" + res->length()); + + strcat(body, "'}"); break; } // endif ODBC @@ -2739,6 +2748,10 @@ PCFIL ha_connect::CheckCond(PGLOBAL g, PCFIL filp, AMT tty, Item *cond) case MYSQL_TYPE_DATETIME: strcat(body, "{ts '"); strncat(body, res->ptr(), res->length()); + + if (res->length() < 19) + strcat(body, "1970-01-01 00:00:00" + res->length()); + strcat(body, "'}"); break; case MYSQL_TYPE_DATE: @@ -4134,6 +4147,10 @@ MODE ha_connect::CheckMode(PGLOBAL g, THD *thd, break; // } // endif partitioned + case SQLCOM_END: + // Met in procedures: IF(EXISTS(SELECT... + newmode= MODE_READ; + break; default: htrc("Unsupported sql_command=%d\n", thd_sql_command(thd)); strcpy(g->Message, "CONNECT Unsupported command"); diff --git a/storage/connect/ha_connect.h b/storage/connect/ha_connect.h index abe8fa079fe..c3b911710b8 100644 --- a/storage/connect/ha_connect.h +++ b/storage/connect/ha_connect.h @@ -235,7 +235,7 @@ public: int CheckRecord(PGLOBAL g, const uchar *oldbuf, uchar *newbuf); int ReadIndexed(uchar *buf, OPVAL op, const uchar* key= NULL, uint key_len= 0); - bool MakeKeyWhere(PGLOBAL g, char *qry, OPVAL op, char *q, + bool MakeKeyWhere(PGLOBAL g, PSTRG qry, OPVAL op, char q, const void *key, int klen); inline char *Strz(LEX_STRING &ls); diff --git a/storage/connect/mysql-test/connect/r/json.result b/storage/connect/mysql-test/connect/r/json.result index 094bb669d18..ebcad699759 100644 --- a/storage/connect/mysql-test/connect/r/json.result +++ b/storage/connect/mysql-test/connect/r/json.result @@ -12,7 +12,7 @@ TRANSLATION CHAR(32), TRANSLATOR CHAR(80), PUBLISHER CHAR(32), DATEPUB int(4) -) ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.jsn'; +) ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.json'; SELECT * FROM t1; ISBN LANG SUBJECT AUTHOR TITLE TRANSLATION TRANSLATOR PUBLISHER DATEPUB 9782212090819 fr applications Jean-Christophe Bernadac Construire une application XML Eyrolles Paris 1999 @@ -34,7 +34,7 @@ Publisher CHAR(20) FIELD_FORMAT='PUBLISHER:NAME', Location CHAR(16) FIELD_FORMAT='PUBLISHER:PLACE', Year int(4) FIELD_FORMAT='DATEPUB' ) -ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.jsn'; +ENGINE=CONNECT TABLE_TYPE=JSON 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 Eyrolles Paris 1999 @@ -57,7 +57,7 @@ Publisher CHAR(20) FIELD_FORMAT='PUBLISHER:NAME', Location CHAR(16) FIELD_FORMAT='PUBLISHER:PLACE', Year int(4) FIELD_FORMAT='DATEPUB' ) -ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.jsn'; +ENGINE=CONNECT TABLE_TYPE=JSON 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 Eyrolles Paris 1999 @@ -80,7 +80,7 @@ Publisher CHAR(20) FIELD_FORMAT='PUBLISHER:NAME', Location CHAR(16) FIELD_FORMAT='PUBLISHER:PLACE', Year int(4) FIELD_FORMAT='DATEPUB' ) -ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.jsn'; +ENGINE=CONNECT TABLE_TYPE=JSON 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 Eyrolles Paris 1999 @@ -89,23 +89,23 @@ ISBN Language Subject AuthorFN AuthorLN Title Translation Translator Publisher L 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 Eyrolles Paris 1999 -9782212090819 fr applications Philippe Knab Construire une application XML Eyrolles Paris 1999 +9782212090819 fr applications Philippe Bernadac Construire une application XML Eyrolles Paris 1999 +9782212090819 fr applications François Knab Construire une application XML 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=JSON FILE_NAME='biblio.jsn' OPTION_LIST='Object=[2]:AUTHOR'; +ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.json' OPTION_LIST='Object=[2]: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 Eyrolles Paris 1999 -9782212090819 fr applications Philippe Knab Construire une application XML Eyrolles Paris 1999 +9782212090819 fr applications Philippe Bernadac Construire une application XML Eyrolles Paris 1999 +9782212090819 fr applications François Knab Construire une application XML 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; @@ -117,7 +117,7 @@ CREATE TABLE t1 ( line char(255) ) -ENGINE=CONNECT TABLE_TYPE=DOS FILE_NAME='biblio.jsn'; +ENGINE=CONNECT TABLE_TYPE=DOS FILE_NAME='biblio.json'; SELECT * FROM t1; line [ @@ -127,11 +127,11 @@ line "SUBJECT": "applications", "AUTHOR": [ { - "FIRSTNAME": "Jean-Christophe", + "FIRSTNAME": "Philippe", "LASTNAME": "Bernadac" }, { - "FIRSTNAME": "Philippe", + "FIRSTNAME": "François", "LASTNAME": "Knab" } ], @@ -178,7 +178,7 @@ WHO CHAR(12), WEEK INT(2) FIELD_FORMAT='WEEK:[X]:NUMBER', WHAT CHAR(32) FIELD_FORMAT='WEEK::EXPENSE:["+"]:WHAT', AMOUNT DOUBLE(8,2) FIELD_FORMAT='WEEK::EXPENSE:[+]:AMOUNT') -ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.jsn'; +ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json'; SELECT * FROM t1; WHO WEEK WHAT AMOUNT Joe 3 Beer+Food+Food+Car 69.00 @@ -192,16 +192,60 @@ Janet 4 Car 17.00 Janet 5 Beer+Car+Beer+Food 57.00 DROP TABLE t1; # -# Cannot be fully expanded +# Now it can be fully expanded # CREATE TABLE t1 ( WHO CHAR(12), WEEK INT(2) FIELD_FORMAT='WEEK:[X]:NUMBER', WHAT CHAR(32) FIELD_FORMAT='WEEK:[X]:EXPENSE:[X]:WHAT', AMOUNT DOUBLE(8,2) FIELD_FORMAT='WEEK:[X]:EXPENSE:[X]:AMOUNT') -ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.jsn'; +ENGINE=CONNECT TABLE_TYPE=JSON 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 FIELD_FORMAT='WEEK:[", "]:NUMBER', +SUMS CHAR(64) NOT NULL FIELD_FORMAT='WEEK:["+"]:EXPENSE:[+]:AMOUNT', +SUM DOUBLE(8,2) NOT NULL FIELD_FORMAT='WEEK:[+]:EXPENSE:[+]:AMOUNT', +AVGS CHAR(64) NOT NULL FIELD_FORMAT='WEEK:["+"]:EXPENSE:[!]:AMOUNT', +SUMAVG DOUBLE(8,2) NOT NULL FIELD_FORMAT='WEEK:[+]:EXPENSE:[!]:AMOUNT', +AVGSUM DOUBLE(8,2) NOT NULL FIELD_FORMAT='WEEK:[!]:EXPENSE:[+]:AMOUNT', +AVGAVG DOUBLE(8,2) NOT NULL FIELD_FORMAT='WEEK:[!]:EXPENSE:[!]:AMOUNT', +AVERAGE DOUBLE(8,2) NOT NULL FIELD_FORMAT='WEEK:[!]:EXPENSE:[X]:AMOUNT') +ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json'; SELECT * FROM t1; -ERROR HY000: Got error 174 'Cannot expand more than one array' from CONNECT +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 @@ -211,7 +255,7 @@ WHO CHAR(12), WEEK INT(2) FIELD_FORMAT='WEEK:[1]:NUMBER', WHAT CHAR(32) FIELD_FORMAT='WEEK:[1]:EXPENSE:[X]:WHAT', AMOUNT DOUBLE(8,2) FIELD_FORMAT='WEEK:[1]:EXPENSE:[X]:AMOUNT') -ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.jsn'; +ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json'; SELECT * FROM t2; WHO WEEK WHAT AMOUNT Joe 3 Beer 18.00 @@ -227,7 +271,7 @@ WHO CHAR(12), WEEK INT(2) FIELD_FORMAT='WEEK:[2]:NUMBER', WHAT CHAR(32) FIELD_FORMAT='WEEK:[2]:EXPENSE:[X]:WHAT', AMOUNT DOUBLE(8,2) FIELD_FORMAT='WEEK:[2]:EXPENSE:[X]:AMOUNT') -ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.jsn'; +ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json'; SELECT * FROM t3; WHO WEEK WHAT AMOUNT Joe 4 Beer 19.00 @@ -243,7 +287,7 @@ WHO CHAR(12), WEEK INT(2) FIELD_FORMAT='WEEK:[3]:NUMBER', WHAT CHAR(32) FIELD_FORMAT='WEEK:[3]:EXPENSE:[X]:WHAT', AMOUNT DOUBLE(8,2) FIELD_FORMAT='WEEK:[3]:EXPENSE:[X]:AMOUNT') -ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.jsn'; +ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json'; SELECT * FROM t4; WHO WEEK WHAT AMOUNT Joe 5 Beer 14.00 @@ -298,7 +342,7 @@ WHO CHAR(12), WEEK INT(2), WHAT CHAR(32) FIELD_FORMAT='EXPENSE:[X]:WHAT', AMOUNT DOUBLE(8,2) FIELD_FORMAT='EXPENSE:[X]:AMOUNT') -ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp3.jsn'; +ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp3.json'; SELECT * FROM t2; WHO WEEK WHAT AMOUNT Joe 3 Beer 18.00 @@ -314,7 +358,7 @@ WHO CHAR(12), WEEK INT(2), WHAT CHAR(32) FIELD_FORMAT='EXPENSE:[X]:WHAT', AMOUNT DOUBLE(8,2) FIELD_FORMAT='EXPENSE:[X]:AMOUNT') -ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp4.jsn'; +ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp4.json'; SELECT * FROM t3; WHO WEEK WHAT AMOUNT Joe 4 Beer 19.00 @@ -330,7 +374,7 @@ WHO CHAR(12), WEEK INT(2), WHAT CHAR(32) FIELD_FORMAT='EXPENSE:[X]:WHAT', AMOUNT DOUBLE(8,2) FIELD_FORMAT='EXPENSE:[X]:AMOUNT') -ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp5.jsn'; +ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp5.json'; SELECT * FROM t4; WHO WEEK WHAT AMOUNT Joe 5 Beer 14.00 @@ -349,7 +393,7 @@ WHO CHAR(12), WEEK INT(2), WHAT CHAR(32) FIELD_FORMAT='EXPENSE:[X]:WHAT', AMOUNT DOUBLE(8,2) FIELD_FORMAT='EXPENSE:[X]:AMOUNT') -ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp*.jsn' MULTIPLE=1; +ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp*.json' MULTIPLE=1; SELECT * FROM t1 ORDER BY WHO, WEEK, WHAT, AMOUNT; WHO WEEK WHAT AMOUNT Beth 3 Beer 16.00 @@ -385,7 +429,7 @@ WHO CHAR(12), WEEK INT(2), WHAT CHAR(32) FIELD_FORMAT='EXPENSE:[X]:WHAT', AMOUNT DOUBLE(8,2) FIELD_FORMAT='EXPENSE:[X]:AMOUNT') -ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp%s.jsn'; +ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp%s.json'; ALTER TABLE t1 PARTITION BY LIST COLUMNS(WEEK) ( PARTITION `3` VALUES IN(3), diff --git a/storage/connect/mysql-test/connect/r/json_udf.result b/storage/connect/mysql-test/connect/r/json_udf.result new file mode 100644 index 00000000000..1455bac9017 --- /dev/null +++ b/storage/connect/mysql-test/connect/r/json_udf.result @@ -0,0 +1,167 @@ +# +# Test UDF's with constant arguments +# +SELECT Json_Array(); +Json_Array() +[] +SELECT Json_Object(56,3.1416,'foo',NULL); +Json_Object(56,3.1416,'foo',NULL) +{"56":56,"3.1416":3.141600,"foo":"foo","NULL":null} +SELECT Json_Object(56 qty,3.1416 price,'foo' truc, NULL garanty); +Json_Object(56 qty,3.1416 price,'foo' truc, NULL garanty) +{"qty":56,"price":3.141600,"truc":"foo","garanty":null} +SELECT Json_Array(56,3.1416,'My name is "Foo"',NULL); +Json_Array(56,3.1416,'My name is "Foo"',NULL) +[56,3.141600,"My name is \"Foo\"",null] +SELECT Json_Array_Add(Json_Array(56,3.1416,'foo',NULL)) Array; +ERROR HY000: Can't initialize function 'Json_Array_Add'; Json_Value_Add must have at least 2 arguments +SELECT Json_Array_Add(Json_Array(56,3.1416,'foo',NULL),'One more') Array; +Array +[56,3.141600,"foo",null,"One more"] +SELECT Json_Array_Add(Json_Value('one value'),'One more'); +Json_Array_Add(Json_Value('one value'),'One more') +["one value","One more"] +SELECT Json_Array_Add('one value','One more'); +ERROR HY000: Can't initialize function 'Json_Array_Add'; Json_Value_Add first argument must be a json item +SELECT Json_Array_Add('one value' json_,'One more'); +Json_Array_Add('one value' json_,'One more') +[null,"One more"] +Warnings: +Warning 1105 Bad 'o' character near one value +SELECT Json_Value(56,3.1416,'foo',NULL); +ERROR HY000: Can't initialize function 'Json_Value'; Json_Value cannot accept more than 1 argument +SELECT Json_Value(3.1416); +Json_Value(3.1416) +3.141600 +SELECT Json_Value('foo'); +Json_Value('foo') +"foo" +SELECT Json_Value(NULL); +Json_Value(NULL) +null +SELECT Json_Value(); +Json_Value() +null +SELECT Json_Object(); +Json_Object() +{} +SELECT Json_Object(Json_Array(56,3.1416,'foo'),NULL); +Json_Object(Json_Array(56,3.1416,'foo'),NULL) +{"Array(56,3.1416,'foo')":[56,3.141600,"foo"],"NULL":null} +SELECT Json_Array(Json_Array(56,3.1416,'foo'),NULL); +Json_Array(Json_Array(56,3.1416,'foo'),NULL) +[[56,3.141600,"foo"],null] +SELECT Json_Array(Json_Object(56 "qty",3.1416 "price",'foo'),NULL); +Json_Array(Json_Object(56 "qty",3.1416 "price",'foo'),NULL) +[{"qty":56,"price":3.141600,"foo":"foo"},null] +# +# Test UDF's with column arguments +# +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=JSON FILE_NAME='biblio.json'; +SELECT Json_Array(AUTHOR, TITLE, DATEPUB) FROM t1; +Json_Array(AUTHOR, TITLE, DATEPUB) +["Jean-Christophe Bernadac","Construire une application XML",1999] +["William J. Pardi","XML en Action",1999] +SELECT Json_Object(AUTHOR, TITLE, DATEPUB) FROM t1; +Json_Object(AUTHOR, TITLE, DATEPUB) +{"AUTHOR":"Jean-Christophe Bernadac","TITLE":"Construire une application XML","DATEPUB":1999} +{"AUTHOR":"William J. Pardi","TITLE":"XML en Action","DATEPUB":1999} +SELECT Json_Array_Grp(TITLE, DATEPUB) FROM t1; +ERROR HY000: Can't initialize function 'Json_Array_Grp'; Json_Array_Grp can only accept 1 argument +SELECT Json_Array_Grp(TITLE) FROM t1; +Json_Array_Grp(TITLE) +["Construire une application XML","XML en Action"] +DROP TABLE t1; +CREATE TABLE t1 ( +SERIALNO CHAR(5) NOT NULL, +NAME VARCHAR(12) NOT NULL FLAG=6, +SEX SMALLINT(1) NOT NULL, +TITLE VARCHAR(15) NOT NULL FLAG=20, +MANAGER CHAR(5) DEFAULT NULL, +DEPARTMENT CHAr(4) NOT NULL FLAG=41, +SECRETARY CHAR(5) DEFAULT NULL FLAG=46, +SALARY DOUBLE(8,2) NOT NULL FLAG=52 +) ENGINE=CONNECT TABLE_TYPE=FIX BLOCK_SIZE=8 FILE_NAME='employee.dat' ENDING=1; +SELECT Json_Object(SERIALNO, NAME, TITLE, SALARY) FROM t1 WHERE NAME = 'MERCHANT'; +Json_Object(SERIALNO, NAME, TITLE, SALARY) +{"SERIALNO":"78943","NAME":"MERCHANT","TITLE":"SALESMAN","SALARY":8700.000000} +SELECT DEPARTMENT, Json_Array_Grp(NAME) FROM t1 GROUP BY DEPARTMENT; +DEPARTMENT Json_Array_Grp(NAME) +0021 ["STRONG","SHORTSIGHT"] +0318 ["BANCROFT","PLUMHEAD","HONEY","TONGHO","WALTER","SHRINKY","WERTHER","MERCHANT","WHEELFOR"] +0319 ["BULLOZER","QUINN","BROWNY","KITTY","MONAPENNY","MARTIN","FUNNIGUY","BUGHAPPY","FODDERMAN","MESSIFUL"] +2452 ["BIGHEAD","ORELLY","BIGHORN","SMITH","CHERRY"] +Warnings: +Warning 1105 Result truncated to json_grp_size values +set connect_json_grp_size=30; +SELECT Json_Array(DEPARTMENT, Json_Array_Grp(NAME)) FROM t1 GROUP BY DEPARTMENT; +Json_Array(DEPARTMENT, Json_Array_Grp(NAME)) +["0021",["STRONG","SHORTSIGHT"]] +["0318",["BANCROFT","PLUMHEAD","HONEY","TONGHO","WALTER","SHRINKY","WERTHER","MERCHANT","WHEELFOR"]] +["0319",["BULLOZER","QUINN","BROWNY","KITTY","MONAPENNY","MARTIN","FUNNIGUY","BUGHAPPY","FODDERMAN","MESSIFUL","GOOSEPEN"]] +["2452",["BIGHEAD","ORELLY","BIGHORN","SMITH","CHERRY"]] +SELECT Json_Object(DEPARTMENT, Json_Array_Grp(NAME) json_NAMES) FROM t1 GROUP BY DEPARTMENT; +Json_Object(DEPARTMENT, Json_Array_Grp(NAME) json_NAMES) +{"DEPARTMENT":"0021","NAMES":["STRONG","SHORTSIGHT"]} +{"DEPARTMENT":"0318","NAMES":["BANCROFT","PLUMHEAD","HONEY","TONGHO","WALTER","SHRINKY","WERTHER","MERCHANT","WHEELFOR"]} +{"DEPARTMENT":"0319","NAMES":["BULLOZER","QUINN","BROWNY","KITTY","MONAPENNY","MARTIN","FUNNIGUY","BUGHAPPY","FODDERMAN","MESSIFUL","GOOSEPEN"]} +{"DEPARTMENT":"2452","NAMES":["BIGHEAD","ORELLY","BIGHORN","SMITH","CHERRY"]} +SELECT Json_Object(DEPARTMENT, Json_Array_Grp(Json_Object(SERIALNO, NAME, TITLE, SALARY)) json_EMPLOYES) FROM t1 GROUP BY DEPARTMENT; +Json_Object(DEPARTMENT, Json_Array_Grp(Json_Object(SERIALNO, NAME, TITLE, SALARY)) json_EMPLOYES) +{"DEPARTMENT":"0021","EMPLOYES":[{"SERIALNO":"87777","NAME":"STRONG","TITLE":"DIRECTOR","SALARY":23000.000000},{"SERIALNO":"22222","NAME":"SHORTSIGHT","TITLE":"SECRETARY","SALARY":5500.000000}]} +{"DEPARTMENT":"0318","EMPLOYES":[{"SERIALNO":"74200","NAME":"BANCROFT","TITLE":"SALESMAN","SALARY":9600.000000},{"SERIALNO":"24888","NAME":"PLUMHEAD","TITLE":"TYPIST","SALARY":2800.000000},{"SERIALNO":"27845","NAME":"HONEY","TITLE":"SECRETARY","SALARY":4900.000000},{"SERIALNO":"73452","NAME":"TONGHO","TITLE":"ENGINEER","SALARY":6800.000000},{"SERIALNO":"74234","NAME":"WALTER","TITLE":"ENGINEER","SALARY":7400.000000},{"SERIALNO":"77777","NAME":"SHRINKY","TITLE":"ADMINISTRATOR","SALARY":7500.000000},{"SERIALNO":"70012","NAME":"WERTHER","TITLE":"DIRECTOR","SALARY":14500.000000},{"SERIALNO":"78943","NAME":"MERCHANT","TITLE":"SALESMAN","SALARY":8700.000000},{"SERIALNO":"73111","NAME":"WHEELFOR","TITLE":"SALESMAN","SALARY":10030.000000}]} +{"DEPARTMENT":"0319","EMPLOYES":[{"SERIALNO":"76543","NAME":"BULLOZER","TITLE":"SALESMAN","SALARY":14800.000000},{"SERIALNO":"40567","NAME":"QUINN","TITLE":"DIRECTOR","SALARY":14000.000000},{"SERIALNO":"00137","NAME":"BROWNY","TITLE":"ENGINEER","SALARY":10500.000000},{"SERIALNO":"12345","NAME":"KITTY","TITLE":"TYPIST","SALARY":3000.450000},{"SERIALNO":"33333","NAME":"MONAPENNY","TITLE":"SECRETARY","SALARY":3800.000000},{"SERIALNO":"00023","NAME":"MARTIN","TITLE":"ENGINEER","SALARY":10000.000000},{"SERIALNO":"07654","NAME":"FUNNIGUY","TITLE":"ADMINISTRATOR","SALARY":8500.000000},{"SERIALNO":"45678","NAME":"BUGHAPPY","TITLE":"PROGRAMMER","SALARY":8500.000000},{"SERIALNO":"56789","NAME":"FODDERMAN","TITLE":"SALESMAN","SALARY":7000.000000},{"SERIALNO":"55555","NAME":"MESSIFUL","TITLE":"SECRETARY","SALARY":5000.500000},{"SERIALNO":"98765","NAME":"GOOSEPEN","TITLE":"ADMINISTRATOR","SALARY":4700.000000}]} +{"DEPARTMENT":"2452","EMPLOYES":[{"SERIALNO":"34567","NAME":"BIGHEAD","TITLE":"SCIENTIST","SALARY":8000.000000},{"SERIALNO":"31416","NAME":"ORELLY","TITLE":"ENGINEER","SALARY":13400.000000},{"SERIALNO":"36666","NAME":"BIGHORN","TITLE":"SCIENTIST","SALARY":11000.000000},{"SERIALNO":"02345","NAME":"SMITH","TITLE":"ENGINEER","SALARY":9000.000000},{"SERIALNO":"11111","NAME":"CHERRY","TITLE":"SECRETARY","SALARY":4500.000000}]} +SELECT Json_Object(DEPARTMENT, TITLE, Json_Array_Grp(Json_Object(SERIALNO, NAME, SALARY)) json_EMPLOYES) FROM t1 GROUP BY DEPARTMENT, TITLE; +Json_Object(DEPARTMENT, TITLE, Json_Array_Grp(Json_Object(SERIALNO, NAME, SALARY)) json_EMPLOYES) +{"DEPARTMENT":"0021","TITLE":"DIRECTOR","EMPLOYES":[{"SERIALNO":"87777","NAME":"STRONG","SALARY":23000.000000}]} +{"DEPARTMENT":"0021","TITLE":"SECRETARY","EMPLOYES":[{"SERIALNO":"22222","NAME":"SHORTSIGHT","SALARY":5500.000000}]} +{"DEPARTMENT":"0318","TITLE":"ADMINISTRATOR","EMPLOYES":[{"SERIALNO":"77777","NAME":"SHRINKY","SALARY":7500.000000}]} +{"DEPARTMENT":"0318","TITLE":"DIRECTOR","EMPLOYES":[{"SERIALNO":"70012","NAME":"WERTHER","SALARY":14500.000000}]} +{"DEPARTMENT":"0318","TITLE":"ENGINEER","EMPLOYES":[{"SERIALNO":"73452","NAME":"TONGHO","SALARY":6800.000000},{"SERIALNO":"74234","NAME":"WALTER","SALARY":7400.000000}]} +{"DEPARTMENT":"0318","TITLE":"SALESMAN","EMPLOYES":[{"SERIALNO":"74200","NAME":"BANCROFT","SALARY":9600.000000},{"SERIALNO":"78943","NAME":"MERCHANT","SALARY":8700.000000},{"SERIALNO":"73111","NAME":"WHEELFOR","SALARY":10030.000000}]} +{"DEPARTMENT":"0318","TITLE":"SECRETARY","EMPLOYES":[{"SERIALNO":"27845","NAME":"HONEY","SALARY":4900.000000}]} +{"DEPARTMENT":"0318","TITLE":"TYPIST","EMPLOYES":[{"SERIALNO":"24888","NAME":"PLUMHEAD","SALARY":2800.000000}]} +{"DEPARTMENT":"0319","TITLE":"ADMINISTRATOR","EMPLOYES":[{"SERIALNO":"98765","NAME":"GOOSEPEN","SALARY":4700.000000},{"SERIALNO":"07654","NAME":"FUNNIGUY","SALARY":8500.000000}]} +{"DEPARTMENT":"0319","TITLE":"DIRECTOR","EMPLOYES":[{"SERIALNO":"40567","NAME":"QUINN","SALARY":14000.000000}]} +{"DEPARTMENT":"0319","TITLE":"ENGINEER","EMPLOYES":[{"SERIALNO":"00023","NAME":"MARTIN","SALARY":10000.000000},{"SERIALNO":"00137","NAME":"BROWNY","SALARY":10500.000000}]} +{"DEPARTMENT":"0319","TITLE":"PROGRAMMER","EMPLOYES":[{"SERIALNO":"45678","NAME":"BUGHAPPY","SALARY":8500.000000}]} +{"DEPARTMENT":"0319","TITLE":"SALESMAN","EMPLOYES":[{"SERIALNO":"76543","NAME":"BULLOZER","SALARY":14800.000000},{"SERIALNO":"56789","NAME":"FODDERMAN","SALARY":7000.000000}]} +{"DEPARTMENT":"0319","TITLE":"SECRETARY","EMPLOYES":[{"SERIALNO":"33333","NAME":"MONAPENNY","SALARY":3800.000000},{"SERIALNO":"55555","NAME":"MESSIFUL","SALARY":5000.500000}]} +{"DEPARTMENT":"0319","TITLE":"TYPIST","EMPLOYES":[{"SERIALNO":"12345","NAME":"KITTY","SALARY":3000.450000}]} +{"DEPARTMENT":"2452","TITLE":"ENGINEER","EMPLOYES":[{"SERIALNO":"31416","NAME":"ORELLY","SALARY":13400.000000},{"SERIALNO":"02345","NAME":"SMITH","SALARY":9000.000000}]} +{"DEPARTMENT":"2452","TITLE":"SCIENTIST","EMPLOYES":[{"SERIALNO":"34567","NAME":"BIGHEAD","SALARY":8000.000000},{"SERIALNO":"36666","NAME":"BIGHORN","SALARY":11000.000000}]} +{"DEPARTMENT":"2452","TITLE":"SECRETARY","EMPLOYES":[{"SERIALNO":"11111","NAME":"CHERRY","SALARY":4500.000000}]} +SELECT Json_Object_Grp(SALARY) FROM t1; +ERROR HY000: Can't initialize function 'Json_Object_Grp'; Json_Array_Grp can only accept 2 arguments +SELECT Json_Object_Grp(SALARY, NAME) FROM t1; +Json_Object_Grp(SALARY, NAME) +{"BANCROFT":9600.000000,"SMITH":9000.000000,"MERCHANT":8700.000000,"FUNNIGUY":8500.000000,"BUGHAPPY":8500.000000,"BIGHEAD":8000.000000,"SHRINKY":7500.000000,"WALTER":7400.000000,"FODDERMAN":7000.000000,"TONGHO":6800.000000,"SHORTSIGHT":5500.000000,"MESSIFUL":5000.500000,"HONEY":4900.000000,"GOOSEPEN":4700.000000,"CHERRY":4500.000000,"MONAPENNY":3800.000000,"KITTY":3000.450000,"PLUMHEAD":2800.000000,"STRONG":23000.000000,"BULLOZER":14800.000000,"WERTHER":14500.000000,"QUINN":14000.000000,"ORELLY":13400.000000,"BIGHORN":11000.000000,"BROWNY":10500.000000,"WHEELFOR":10030.000000,"MARTIN":10000.000000} +SELECT Json_Object(DEPARTMENT, Json_Object_Grp(SALARY, NAME) "Json_SALARIES") FROM t1 GROUP BY DEPARTMENT; +Json_Object(DEPARTMENT, Json_Object_Grp(SALARY, NAME) "Json_SALARIES") +{"DEPARTMENT":"0021","SALARIES":{"STRONG":23000.000000,"SHORTSIGHT":5500.000000}} +{"DEPARTMENT":"0318","SALARIES":{"BANCROFT":9600.000000,"PLUMHEAD":2800.000000,"HONEY":4900.000000,"TONGHO":6800.000000,"WALTER":7400.000000,"SHRINKY":7500.000000,"WERTHER":14500.000000,"MERCHANT":8700.000000,"WHEELFOR":10030.000000}} +{"DEPARTMENT":"0319","SALARIES":{"BULLOZER":14800.000000,"QUINN":14000.000000,"BROWNY":10500.000000,"KITTY":3000.450000,"MONAPENNY":3800.000000,"MARTIN":10000.000000,"FUNNIGUY":8500.000000,"BUGHAPPY":8500.000000,"FODDERMAN":7000.000000,"MESSIFUL":5000.500000,"GOOSEPEN":4700.000000}} +{"DEPARTMENT":"2452","SALARIES":{"BIGHEAD":8000.000000,"ORELLY":13400.000000,"BIGHORN":11000.000000,"SMITH":9000.000000,"CHERRY":4500.000000}} +SELECT Json_Array_Grp(NAME) from t1; +Json_Array_Grp(NAME) +["BANCROFT","SMITH","MERCHANT","FUNNIGUY","BUGHAPPY","BIGHEAD","SHRINKY","WALTER","FODDERMAN","TONGHO","SHORTSIGHT","MESSIFUL","HONEY","GOOSEPEN","CHERRY","MONAPENNY","KITTY","PLUMHEAD","STRONG","BULLOZER","WERTHER","QUINN","ORELLY","BIGHORN","BROWNY","WHEELFOR","MARTIN"] +DROP TABLE t1; +DROP FUNCTION Json_Array; +DROP FUNCTION Json_Array_Add; +DROP FUNCTION Json_Object; +DROP FUNCTION Json_Object_Nonull; +DROP FUNCTION Json_Value; +DROP FUNCTION Json_Array_Grp; +DROP FUNCTION Json_Object_Grp; diff --git a/storage/connect/mysql-test/connect/r/odbc_oracle.result b/storage/connect/mysql-test/connect/r/odbc_oracle.result index fff2f192184..96d8e53b8e5 100644 --- a/storage/connect/mysql-test/connect/r/odbc_oracle.result +++ b/storage/connect/mysql-test/connect/r/odbc_oracle.result @@ -126,7 +126,7 @@ TABNAME='T1'; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `A` decimal(38,0) DEFAULT NULL, + `A` decimal(40,0) DEFAULT NULL, `B` double(40,0) DEFAULT NULL ) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='DSN=ConnectEngineOracle;UID=mtr;PWD=mtr' `TABLE_TYPE`='ODBC' `TABNAME`='T1' SELECT * FROM t1 ORDER BY A; @@ -138,7 +138,7 @@ CREATE TABLE t2 AS SELECT * FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( - `A` decimal(38,0) DEFAULT NULL, + `A` decimal(40,0) DEFAULT NULL, `B` double(40,0) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM t2; @@ -162,7 +162,7 @@ TABNAME='MTR.T1'; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `A` decimal(38,0) DEFAULT NULL, + `A` decimal(40,0) DEFAULT NULL, `B` double(40,0) DEFAULT NULL ) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='DSN=ConnectEngineOracle;UID=mtr;PWD=mtr' `TABLE_TYPE`='ODBC' `TABNAME`='MTR.T1' SELECT * FROM t1; @@ -178,7 +178,7 @@ TABNAME='MTR.V1'; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `A` decimal(38,0) DEFAULT NULL, + `A` decimal(40,0) DEFAULT NULL, `B` double(40,0) DEFAULT NULL ) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='DSN=ConnectEngineOracle;UID=mtr;PWD=mtr' `TABLE_TYPE`='ODBC' `TABNAME`='MTR.V1' SELECT * FROM t1; @@ -190,7 +190,7 @@ CREATE TABLE t2 AS SELECT * FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( - `A` decimal(38,0) DEFAULT NULL, + `A` decimal(40,0) DEFAULT NULL, `B` double(40,0) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM t2; diff --git a/storage/connect/mysql-test/connect/std_data/biblio.jsn b/storage/connect/mysql-test/connect/std_data/biblio.json index bab8fd24305..cc24b162cd9 100644 --- a/storage/connect/mysql-test/connect/std_data/biblio.jsn +++ b/storage/connect/mysql-test/connect/std_data/biblio.json @@ -1,45 +1,45 @@ -[
- {
- "ISBN": "9782212090819",
- "LANG": "fr",
- "SUBJECT": "applications",
- "AUTHOR": [
- {
- "FIRSTNAME": "Jean-Christophe",
- "LASTNAME": "Bernadac"
- },
- {
- "FIRSTNAME": "François",
- "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"
- }
- ],
- "TITLE": "XML en Action",
- "TRANSLATION": "adapté de l'anglais par",
- "TRANSLATOR": {
- "FIRSTNAME": "James",
- "LASTNAME": "Guerin"
- },
- "PUBLISHER": {
- "NAME": "Microsoft Press",
- "PLACE": "Paris"
- },
- "DATEPUB": 1999
- }
-]
+[ + { + "ISBN": "9782212090819", + "LANG": "fr", + "SUBJECT": "applications", + "AUTHOR": [ + { + "FIRSTNAME": "Jean-Christophe", + "LASTNAME": "Bernadac" + }, + { + "FIRSTNAME": "François", + "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" + } + ], + "TITLE": "XML en Action", + "TRANSLATION": "adapté de l'anglais par", + "TRANSLATOR": { + "FIRSTNAME": "James", + "LASTNAME": "Guerin" + }, + "PUBLISHER": { + "NAME": "Microsoft Press", + "PLACE": "Paris" + }, + "DATEPUB": 1999 + } +] diff --git a/storage/connect/mysql-test/connect/std_data/expense.jsn b/storage/connect/mysql-test/connect/std_data/expense.json index e65ad5261f1..f9373ef1a80 100644 --- a/storage/connect/mysql-test/connect/std_data/expense.jsn +++ b/storage/connect/mysql-test/connect/std_data/expense.json @@ -1,158 +1,158 @@ -[
- {
- "WHO": "Joe",
- "WEEK": [
- {
- "NUMBER": 3,
- "EXPENSE": [
- {
- "WHAT": "Beer",
- "AMOUNT": 18.00
- },
- {
- "WHAT": "Food",
- "AMOUNT": 12.00
- },
- {
- "WHAT": "Food",
- "AMOUNT": 19.00
- },
- {
- "WHAT": "Car",
- "AMOUNT": 20.00
- }
- ]
- },
- {
- "NUMBER": 4,
- "EXPENSE": [
- {
- "WHAT": "Beer",
- "AMOUNT": 19.00
- },
- {
- "WHAT": "Beer",
- "AMOUNT": 16.00
- },
- {
- "WHAT": "Food",
- "AMOUNT": 17.00
- },
- {
- "WHAT": "Food",
- "AMOUNT": 17.00
- },
- {
- "WHAT": "Beer",
- "AMOUNT": 14.00
- }
- ]
- },
- {
- "NUMBER": 5,
- "EXPENSE": [
- {
- "WHAT": "Beer",
- "AMOUNT": 14.00
- },
- {
- "WHAT": "Food",
- "AMOUNT": 12.00
- }
- ]
- }
- ]
- },
- {
- "WHO": "Beth",
- "WEEK": [
- {
- "NUMBER": 3,
- "EXPENSE": [
- {
- "WHAT": "Beer",
- "AMOUNT": 16.00
- }
- ]
- },
- {
- "NUMBER": 4,
- "EXPENSE": [
- {
- "WHAT": "Food",
- "AMOUNT": 17.00
- },
- {
- "WHAT": "Beer",
- "AMOUNT": 15.00
- }
- ]
- },
- {
- "NUMBER": 5,
- "EXPENSE": [
- {
- "WHAT": "Food",
- "AMOUNT": 12.00
- },
- {
- "WHAT": "Beer",
- "AMOUNT": 20.00
- }
- ]
- }
- ]
- },
- {
- "WHO": "Janet",
- "WEEK": [
- {
- "NUMBER": 3,
- "EXPENSE": [
- {
- "WHAT": "Car",
- "AMOUNT": 19.00
- },
- {
- "WHAT": "Food",
- "AMOUNT": 18.00
- },
- {
- "WHAT": "Beer",
- "AMOUNT": 18.00
- }
- ]
- },
- {
- "NUMBER": 4,
- "EXPENSE": [
- {
- "WHAT": "Car",
- "AMOUNT": 17.00
- }
- ]
- },
- {
- "NUMBER": 5,
- "EXPENSE": [
- {
- "WHAT": "Beer",
- "AMOUNT": 14.00
- },
- {
- "WHAT": "Car",
- "AMOUNT": 12.00
- },
- {
- "WHAT": "Beer",
- "AMOUNT": 19.00
- },
- {
- "WHAT": "Food",
- "AMOUNT": 12.00
- }
- ]
- }
- ]
- }
-]
+[ + { + "WHO": "Joe", + "WEEK": [ + { + "NUMBER": 3, + "EXPENSE": [ + { + "WHAT": "Beer", + "AMOUNT": 18.00 + }, + { + "WHAT": "Food", + "AMOUNT": 12.00 + }, + { + "WHAT": "Food", + "AMOUNT": 19.00 + }, + { + "WHAT": "Car", + "AMOUNT": 20.00 + } + ] + }, + { + "NUMBER": 4, + "EXPENSE": [ + { + "WHAT": "Beer", + "AMOUNT": 19.00 + }, + { + "WHAT": "Beer", + "AMOUNT": 16.00 + }, + { + "WHAT": "Food", + "AMOUNT": 17.00 + }, + { + "WHAT": "Food", + "AMOUNT": 17.00 + }, + { + "WHAT": "Beer", + "AMOUNT": 14.00 + } + ] + }, + { + "NUMBER": 5, + "EXPENSE": [ + { + "WHAT": "Beer", + "AMOUNT": 14.00 + }, + { + "WHAT": "Food", + "AMOUNT": 12.00 + } + ] + } + ] + }, + { + "WHO": "Beth", + "WEEK": [ + { + "NUMBER": 3, + "EXPENSE": [ + { + "WHAT": "Beer", + "AMOUNT": 16.00 + } + ] + }, + { + "NUMBER": 4, + "EXPENSE": [ + { + "WHAT": "Food", + "AMOUNT": 17.00 + }, + { + "WHAT": "Beer", + "AMOUNT": 15.00 + } + ] + }, + { + "NUMBER": 5, + "EXPENSE": [ + { + "WHAT": "Food", + "AMOUNT": 12.00 + }, + { + "WHAT": "Beer", + "AMOUNT": 20.00 + } + ] + } + ] + }, + { + "WHO": "Janet", + "WEEK": [ + { + "NUMBER": 3, + "EXPENSE": [ + { + "WHAT": "Car", + "AMOUNT": 19.00 + }, + { + "WHAT": "Food", + "AMOUNT": 18.00 + }, + { + "WHAT": "Beer", + "AMOUNT": 18.00 + } + ] + }, + { + "NUMBER": 4, + "EXPENSE": [ + { + "WHAT": "Car", + "AMOUNT": 17.00 + } + ] + }, + { + "NUMBER": 5, + "EXPENSE": [ + { + "WHAT": "Beer", + "AMOUNT": 14.00 + }, + { + "WHAT": "Car", + "AMOUNT": 12.00 + }, + { + "WHAT": "Beer", + "AMOUNT": 19.00 + }, + { + "WHAT": "Food", + "AMOUNT": 12.00 + } + ] + } + ] + } +] diff --git a/storage/connect/mysql-test/connect/std_data/mulexp3.jsn b/storage/connect/mysql-test/connect/std_data/mulexp3.json index c228448b073..5edd2ab1e76 100644 --- a/storage/connect/mysql-test/connect/std_data/mulexp3.jsn +++ b/storage/connect/mysql-test/connect/std_data/mulexp3.json @@ -1,52 +1,52 @@ -[
- {
- "WHO": "Joe",
- "WEEK": 3,
- "EXPENSE": [
- {
- "WHAT": "Beer",
- "AMOUNT": 18.00
- },
- {
- "WHAT": "Food",
- "AMOUNT": 12.00
- },
- {
- "WHAT": "Food",
- "AMOUNT": 19.00
- },
- {
- "WHAT": "Car",
- "AMOUNT": 20.00
- }
- ]
- },
- {
- "WHO": "Beth",
- "WEEK": 3,
- "EXPENSE": [
- {
- "WHAT": "Beer",
- "AMOUNT": 16.00
- }
- ]
- },
- {
- "WHO": "Janet",
- "WEEK": 3,
- "EXPENSE": [
- {
- "WHAT": "Car",
- "AMOUNT": 19.00
- },
- {
- "WHAT": "Food",
- "AMOUNT": 18.00
- },
- {
- "WHAT": "Beer",
- "AMOUNT": 18.00
- }
- ]
- }
-]
+[ + { + "WHO": "Joe", + "WEEK": 3, + "EXPENSE": [ + { + "WHAT": "Beer", + "AMOUNT": 18.00 + }, + { + "WHAT": "Food", + "AMOUNT": 12.00 + }, + { + "WHAT": "Food", + "AMOUNT": 19.00 + }, + { + "WHAT": "Car", + "AMOUNT": 20.00 + } + ] + }, + { + "WHO": "Beth", + "WEEK": 3, + "EXPENSE": [ + { + "WHAT": "Beer", + "AMOUNT": 16.00 + } + ] + }, + { + "WHO": "Janet", + "WEEK": 3, + "EXPENSE": [ + { + "WHAT": "Car", + "AMOUNT": 19.00 + }, + { + "WHAT": "Food", + "AMOUNT": 18.00 + }, + { + "WHAT": "Beer", + "AMOUNT": 18.00 + } + ] + } +] diff --git a/storage/connect/mysql-test/connect/std_data/mulexp4.jsn b/storage/connect/mysql-test/connect/std_data/mulexp4.json index 0e43ffec07b..7f7b88d6afd 100644 --- a/storage/connect/mysql-test/connect/std_data/mulexp4.jsn +++ b/storage/connect/mysql-test/connect/std_data/mulexp4.json @@ -1,52 +1,52 @@ -[
- {
- "WHO": "Joe",
- "WEEK": 4,
- "EXPENSE": [
- {
- "WHAT": "Beer",
- "AMOUNT": 19.00
- },
- {
- "WHAT": "Beer",
- "AMOUNT": 16.00
- },
- {
- "WHAT": "Food",
- "AMOUNT": 17.00
- },
- {
- "WHAT": "Food",
- "AMOUNT": 17.00
- },
- {
- "WHAT": "Beer",
- "AMOUNT": 14.00
- }
- ]
- },
- {
- "WHO": "Beth",
- "WEEK": 4,
- "EXPENSE": [
- {
- "WHAT": "Food",
- "AMOUNT": 17.00
- },
- {
- "WHAT": "Beer",
- "AMOUNT": 15.00
- }
- ]
- },
- {
- "WHO": "Janet",
- "WEEK": 4,
- "EXPENSE": [
- {
- "WHAT": "Car",
- "AMOUNT": 17.00
- }
- ]
- }
-]
+[ + { + "WHO": "Joe", + "WEEK": 4, + "EXPENSE": [ + { + "WHAT": "Beer", + "AMOUNT": 19.00 + }, + { + "WHAT": "Beer", + "AMOUNT": 16.00 + }, + { + "WHAT": "Food", + "AMOUNT": 17.00 + }, + { + "WHAT": "Food", + "AMOUNT": 17.00 + }, + { + "WHAT": "Beer", + "AMOUNT": 14.00 + } + ] + }, + { + "WHO": "Beth", + "WEEK": 4, + "EXPENSE": [ + { + "WHAT": "Food", + "AMOUNT": 17.00 + }, + { + "WHAT": "Beer", + "AMOUNT": 15.00 + } + ] + }, + { + "WHO": "Janet", + "WEEK": 4, + "EXPENSE": [ + { + "WHAT": "Car", + "AMOUNT": 17.00 + } + ] + } +] diff --git a/storage/connect/mysql-test/connect/std_data/mulexp5.jsn b/storage/connect/mysql-test/connect/std_data/mulexp5.json index 7a707506c2f..b1713040c4b 100644 --- a/storage/connect/mysql-test/connect/std_data/mulexp5.jsn +++ b/storage/connect/mysql-test/connect/std_data/mulexp5.json @@ -1,52 +1,52 @@ -[
- {
- "WHO": "Joe",
- "WEEK": 5,
- "EXPENSE": [
- {
- "WHAT": "Beer",
- "AMOUNT": 14.00
- },
- {
- "WHAT": "Food",
- "AMOUNT": 12.00
- }
- ]
- },
- {
- "WHO": "Beth",
- "WEEK": 5,
- "EXPENSE": [
- {
- "WHAT": "Food",
- "AMOUNT": 12.00
- },
- {
- "WHAT": "Beer",
- "AMOUNT": 20.00
- }
- ]
- },
- {
- "WHO": "Janet",
- "WEEK": 5,
- "EXPENSE": [
- {
- "WHAT": "Beer",
- "AMOUNT": 14.00
- },
- {
- "WHAT": "Car",
- "AMOUNT": 12.00
- },
- {
- "WHAT": "Beer",
- "AMOUNT": 19.00
- },
- {
- "WHAT": "Food",
- "AMOUNT": 12.00
- }
- ]
- }
-]
+[ + { + "WHO": "Joe", + "WEEK": 5, + "EXPENSE": [ + { + "WHAT": "Beer", + "AMOUNT": 14.00 + }, + { + "WHAT": "Food", + "AMOUNT": 12.00 + } + ] + }, + { + "WHO": "Beth", + "WEEK": 5, + "EXPENSE": [ + { + "WHAT": "Food", + "AMOUNT": 12.00 + }, + { + "WHAT": "Beer", + "AMOUNT": 20.00 + } + ] + }, + { + "WHO": "Janet", + "WEEK": 5, + "EXPENSE": [ + { + "WHAT": "Beer", + "AMOUNT": 14.00 + }, + { + "WHAT": "Car", + "AMOUNT": 12.00 + }, + { + "WHAT": "Beer", + "AMOUNT": 19.00 + }, + { + "WHAT": "Food", + "AMOUNT": 12.00 + } + ] + } +] diff --git a/storage/connect/mysql-test/connect/t/json.test b/storage/connect/mysql-test/connect/t/json.test index a7e630ed0cf..f89f58534e7 100644 --- a/storage/connect/mysql-test/connect/t/json.test +++ b/storage/connect/mysql-test/connect/t/json.test @@ -1,247 +1,264 @@ ---source include/not_embedded.inc
---source include/have_partition.inc
-
-let $MYSQLD_DATADIR= `select @@datadir`;
-
---copy_file $MTR_SUITE_DIR/std_data/biblio.jsn $MYSQLD_DATADIR/test/biblio.jsn
---copy_file $MTR_SUITE_DIR/std_data/expense.jsn $MYSQLD_DATADIR/test/expense.jsn
---copy_file $MTR_SUITE_DIR/std_data/mulexp3.jsn $MYSQLD_DATADIR/test/mulexp3.jsn
---copy_file $MTR_SUITE_DIR/std_data/mulexp4.jsn $MYSQLD_DATADIR/test/mulexp4.jsn
---copy_file $MTR_SUITE_DIR/std_data/mulexp5.jsn $MYSQLD_DATADIR/test/mulexp5.jsn
-
---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=JSON FILE_NAME='biblio.jsn';
-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) FIELD_FORMAT='LANG',
- Subject CHAR(32) FIELD_FORMAT='SUBJECT',
- Authors INT(2) FIELD_FORMAT='AUTHOR:[#]',
- Title CHAR(32) FIELD_FORMAT='TITLE',
- Translation CHAR(32) FIELD_FORMAT='TRANSLATION',
- Translator CHAR(80) FIELD_FORMAT='TRANSLATOR',
- Publisher CHAR(20) FIELD_FORMAT='PUBLISHER:NAME',
- Location CHAR(16) FIELD_FORMAT='PUBLISHER:PLACE',
- Year int(4) FIELD_FORMAT='DATEPUB'
-)
-ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.jsn';
-SELECT * FROM t1;
-DROP TABLE t1;
-
---echo #
---echo # Concatenates the authors
---echo #
-CREATE TABLE t1
-(
- ISBN CHAR(15),
- Language CHAR(2) FIELD_FORMAT='LANG',
- Subject CHAR(32) FIELD_FORMAT='SUBJECT',
- AuthorFN CHAR(128) FIELD_FORMAT='AUTHOR:[" and "]:FIRSTNAME',
- AuthorLN CHAR(128) FIELD_FORMAT='AUTHOR:[" and "]:LASTNAME',
- Title CHAR(32) FIELD_FORMAT='TITLE',
- Translation CHAR(32) FIELD_FORMAT='TRANSLATION',
- Translator CHAR(80) FIELD_FORMAT='TRANSLATOR',
- Publisher CHAR(20) FIELD_FORMAT='PUBLISHER:NAME',
- Location CHAR(16) FIELD_FORMAT='PUBLISHER:PLACE',
- Year int(4) FIELD_FORMAT='DATEPUB'
-)
-ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.jsn';
-SELECT * FROM t1;
-DROP TABLE t1;
-
---echo #
---echo # Testing expanding authors
---echo #
-CREATE TABLE t1
-(
- ISBN CHAR(15),
- Language CHAR(2) FIELD_FORMAT='LANG',
- Subject CHAR(32) FIELD_FORMAT='SUBJECT',
- AuthorFN CHAR(128) FIELD_FORMAT='AUTHOR:[X]:FIRSTNAME',
- AuthorLN CHAR(128) FIELD_FORMAT='AUTHOR:[X]:LASTNAME',
- Title CHAR(32) FIELD_FORMAT='TITLE',
- Translation CHAR(32) FIELD_FORMAT='TRANSLATION',
- Translator CHAR(80) FIELD_FORMAT='TRANSLATOR',
- Publisher CHAR(20) FIELD_FORMAT='PUBLISHER:NAME',
- Location CHAR(16) FIELD_FORMAT='PUBLISHER:PLACE',
- Year int(4) FIELD_FORMAT='DATEPUB'
-)
-ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.jsn';
-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=JSON FILE_NAME='biblio.jsn' OPTION_LIST='Object=[2]: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.jsn';
-SELECT * FROM t1;
-DROP TABLE t1;
-
---echo #
---echo # A file with 2 arrays
---echo #
-CREATE TABLE t1 (
-WHO CHAR(12),
-WEEK INT(2) FIELD_FORMAT='WEEK:[X]:NUMBER',
-WHAT CHAR(32) FIELD_FORMAT='WEEK::EXPENSE:["+"]:WHAT',
-AMOUNT DOUBLE(8,2) FIELD_FORMAT='WEEK::EXPENSE:[+]:AMOUNT')
-ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.jsn';
-SELECT * FROM t1;
-DROP TABLE t1;
-
---echo #
---echo # Cannot be fully expanded
---echo #
-CREATE TABLE t1 (
-WHO CHAR(12),
-WEEK INT(2) FIELD_FORMAT='WEEK:[X]:NUMBER',
-WHAT CHAR(32) FIELD_FORMAT='WEEK:[X]:EXPENSE:[X]:WHAT',
-AMOUNT DOUBLE(8,2) FIELD_FORMAT='WEEK:[X]:EXPENSE:[X]:AMOUNT')
-ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.jsn';
---error ER_GET_ERRMSG
-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) FIELD_FORMAT='WEEK:[1]:NUMBER',
-WHAT CHAR(32) FIELD_FORMAT='WEEK:[1]:EXPENSE:[X]:WHAT',
-AMOUNT DOUBLE(8,2) FIELD_FORMAT='WEEK:[1]:EXPENSE:[X]:AMOUNT')
-ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.jsn';
-SELECT * FROM t2;
-
-CREATE TABLE t3 (
-WHO CHAR(12),
-WEEK INT(2) FIELD_FORMAT='WEEK:[2]:NUMBER',
-WHAT CHAR(32) FIELD_FORMAT='WEEK:[2]:EXPENSE:[X]:WHAT',
-AMOUNT DOUBLE(8,2) FIELD_FORMAT='WEEK:[2]:EXPENSE:[X]:AMOUNT')
-ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.jsn';
-SELECT * FROM t3;
-
-CREATE TABLE t4 (
-WHO CHAR(12),
-WEEK INT(2) FIELD_FORMAT='WEEK:[3]:NUMBER',
-WHAT CHAR(32) FIELD_FORMAT='WEEK:[3]:EXPENSE:[X]:WHAT',
-AMOUNT DOUBLE(8,2) FIELD_FORMAT='WEEK:[3]:EXPENSE:[X]:AMOUNT')
-ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.jsn';
-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) FIELD_FORMAT='EXPENSE:[X]:WHAT',
-AMOUNT DOUBLE(8,2) FIELD_FORMAT='EXPENSE:[X]:AMOUNT')
-ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp3.jsn';
-SELECT * FROM t2;
-
-CREATE TABLE t3 (
-WHO CHAR(12),
-WEEK INT(2),
-WHAT CHAR(32) FIELD_FORMAT='EXPENSE:[X]:WHAT',
-AMOUNT DOUBLE(8,2) FIELD_FORMAT='EXPENSE:[X]:AMOUNT')
-ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp4.jsn';
-SELECT * FROM t3;
-
-CREATE TABLE t4 (
-WHO CHAR(12),
-WEEK INT(2),
-WHAT CHAR(32) FIELD_FORMAT='EXPENSE:[X]:WHAT',
-AMOUNT DOUBLE(8,2) FIELD_FORMAT='EXPENSE:[X]:AMOUNT')
-ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp5.jsn';
-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) FIELD_FORMAT='EXPENSE:[X]:WHAT',
-AMOUNT DOUBLE(8,2) FIELD_FORMAT='EXPENSE:[X]:AMOUNT')
-ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp*.jsn' 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) FIELD_FORMAT='EXPENSE:[X]:WHAT',
-AMOUNT DOUBLE(8,2) FIELD_FORMAT='EXPENSE:[X]:AMOUNT')
-ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp%s.jsn';
-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.jsn
---remove_file $MYSQLD_DATADIR/test/expense.jsn
---remove_file $MYSQLD_DATADIR/test/mulexp3.jsn
---remove_file $MYSQLD_DATADIR/test/mulexp4.jsn
---remove_file $MYSQLD_DATADIR/test/mulexp5.jsn
+--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/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=JSON 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) FIELD_FORMAT='LANG', + Subject CHAR(32) FIELD_FORMAT='SUBJECT', + Authors INT(2) FIELD_FORMAT='AUTHOR:[#]', + Title CHAR(32) FIELD_FORMAT='TITLE', + Translation CHAR(32) FIELD_FORMAT='TRANSLATION', + Translator CHAR(80) FIELD_FORMAT='TRANSLATOR', + Publisher CHAR(20) FIELD_FORMAT='PUBLISHER:NAME', + Location CHAR(16) FIELD_FORMAT='PUBLISHER:PLACE', + Year int(4) FIELD_FORMAT='DATEPUB' +) +ENGINE=CONNECT TABLE_TYPE=JSON 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) FIELD_FORMAT='LANG', + Subject CHAR(32) FIELD_FORMAT='SUBJECT', + AuthorFN CHAR(128) FIELD_FORMAT='AUTHOR:[" and "]:FIRSTNAME', + AuthorLN CHAR(128) FIELD_FORMAT='AUTHOR:[" and "]:LASTNAME', + Title CHAR(32) FIELD_FORMAT='TITLE', + Translation CHAR(32) FIELD_FORMAT='TRANSLATION', + Translator CHAR(80) FIELD_FORMAT='TRANSLATOR', + Publisher CHAR(20) FIELD_FORMAT='PUBLISHER:NAME', + Location CHAR(16) FIELD_FORMAT='PUBLISHER:PLACE', + Year int(4) FIELD_FORMAT='DATEPUB' +) +ENGINE=CONNECT TABLE_TYPE=JSON 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) FIELD_FORMAT='LANG', + Subject CHAR(32) FIELD_FORMAT='SUBJECT', + AuthorFN CHAR(128) FIELD_FORMAT='AUTHOR:[X]:FIRSTNAME', + AuthorLN CHAR(128) FIELD_FORMAT='AUTHOR:[X]:LASTNAME', + Title CHAR(32) FIELD_FORMAT='TITLE', + Translation CHAR(32) FIELD_FORMAT='TRANSLATION', + Translator CHAR(80) FIELD_FORMAT='TRANSLATOR', + Publisher CHAR(20) FIELD_FORMAT='PUBLISHER:NAME', + Location CHAR(16) FIELD_FORMAT='PUBLISHER:PLACE', + Year int(4) FIELD_FORMAT='DATEPUB' +) +ENGINE=CONNECT TABLE_TYPE=JSON 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=JSON FILE_NAME='biblio.json' OPTION_LIST='Object=[2]: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 # A file with 2 arrays +--echo # +CREATE TABLE t1 ( +WHO CHAR(12), +WEEK INT(2) FIELD_FORMAT='WEEK:[X]:NUMBER', +WHAT CHAR(32) FIELD_FORMAT='WEEK::EXPENSE:["+"]:WHAT', +AMOUNT DOUBLE(8,2) FIELD_FORMAT='WEEK::EXPENSE:[+]:AMOUNT') +ENGINE=CONNECT TABLE_TYPE=JSON 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) FIELD_FORMAT='WEEK:[X]:NUMBER', +WHAT CHAR(32) FIELD_FORMAT='WEEK:[X]:EXPENSE:[X]:WHAT', +AMOUNT DOUBLE(8,2) FIELD_FORMAT='WEEK:[X]:EXPENSE:[X]:AMOUNT') +ENGINE=CONNECT TABLE_TYPE=JSON 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 FIELD_FORMAT='WEEK:[", "]:NUMBER', +SUMS CHAR(64) NOT NULL FIELD_FORMAT='WEEK:["+"]:EXPENSE:[+]:AMOUNT', +SUM DOUBLE(8,2) NOT NULL FIELD_FORMAT='WEEK:[+]:EXPENSE:[+]:AMOUNT', +AVGS CHAR(64) NOT NULL FIELD_FORMAT='WEEK:["+"]:EXPENSE:[!]:AMOUNT', +SUMAVG DOUBLE(8,2) NOT NULL FIELD_FORMAT='WEEK:[+]:EXPENSE:[!]:AMOUNT', +AVGSUM DOUBLE(8,2) NOT NULL FIELD_FORMAT='WEEK:[!]:EXPENSE:[+]:AMOUNT', +AVGAVG DOUBLE(8,2) NOT NULL FIELD_FORMAT='WEEK:[!]:EXPENSE:[!]:AMOUNT', +AVERAGE DOUBLE(8,2) NOT NULL FIELD_FORMAT='WEEK:[!]:EXPENSE:[X]:AMOUNT') +ENGINE=CONNECT TABLE_TYPE=JSON 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) FIELD_FORMAT='WEEK:[1]:NUMBER', +WHAT CHAR(32) FIELD_FORMAT='WEEK:[1]:EXPENSE:[X]:WHAT', +AMOUNT DOUBLE(8,2) FIELD_FORMAT='WEEK:[1]:EXPENSE:[X]:AMOUNT') +ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json'; +SELECT * FROM t2; + +CREATE TABLE t3 ( +WHO CHAR(12), +WEEK INT(2) FIELD_FORMAT='WEEK:[2]:NUMBER', +WHAT CHAR(32) FIELD_FORMAT='WEEK:[2]:EXPENSE:[X]:WHAT', +AMOUNT DOUBLE(8,2) FIELD_FORMAT='WEEK:[2]:EXPENSE:[X]:AMOUNT') +ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json'; +SELECT * FROM t3; + +CREATE TABLE t4 ( +WHO CHAR(12), +WEEK INT(2) FIELD_FORMAT='WEEK:[3]:NUMBER', +WHAT CHAR(32) FIELD_FORMAT='WEEK:[3]:EXPENSE:[X]:WHAT', +AMOUNT DOUBLE(8,2) FIELD_FORMAT='WEEK:[3]:EXPENSE:[X]:AMOUNT') +ENGINE=CONNECT TABLE_TYPE=JSON 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) FIELD_FORMAT='EXPENSE:[X]:WHAT', +AMOUNT DOUBLE(8,2) FIELD_FORMAT='EXPENSE:[X]:AMOUNT') +ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp3.json'; +SELECT * FROM t2; + +CREATE TABLE t3 ( +WHO CHAR(12), +WEEK INT(2), +WHAT CHAR(32) FIELD_FORMAT='EXPENSE:[X]:WHAT', +AMOUNT DOUBLE(8,2) FIELD_FORMAT='EXPENSE:[X]:AMOUNT') +ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp4.json'; +SELECT * FROM t3; + +CREATE TABLE t4 ( +WHO CHAR(12), +WEEK INT(2), +WHAT CHAR(32) FIELD_FORMAT='EXPENSE:[X]:WHAT', +AMOUNT DOUBLE(8,2) FIELD_FORMAT='EXPENSE:[X]:AMOUNT') +ENGINE=CONNECT TABLE_TYPE=JSON 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) FIELD_FORMAT='EXPENSE:[X]:WHAT', +AMOUNT DOUBLE(8,2) FIELD_FORMAT='EXPENSE:[X]:AMOUNT') +ENGINE=CONNECT TABLE_TYPE=JSON 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) FIELD_FORMAT='EXPENSE:[X]:WHAT', +AMOUNT DOUBLE(8,2) FIELD_FORMAT='EXPENSE:[X]:AMOUNT') +ENGINE=CONNECT TABLE_TYPE=JSON 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/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/tabmul.cpp b/storage/connect/tabmul.cpp index e903bad91c0..36f3fc73210 100644 --- a/storage/connect/tabmul.cpp +++ b/storage/connect/tabmul.cpp @@ -238,7 +238,7 @@ bool TDBMUL::InitFileNames(PGLOBAL g) continue; // Not a match strcat(strcpy(filename, direc), entry->d_name); - pfn[n] = PlugDup(g, filename); + pfn[n++] = PlugDup(g, filename); if (trace) htrc("Adding pfn[%d] %s\n", n, filename); diff --git a/storage/connect/tabmysql.cpp b/storage/connect/tabmysql.cpp index 33a4dd67d38..e4befc8bfde 100644 --- a/storage/connect/tabmysql.cpp +++ b/storage/connect/tabmysql.cpp @@ -1078,8 +1078,7 @@ bool TDBMYSQL::ReadKey(PGLOBAL g, OPVAL op, const void *key, int len) if (Myc.m_Res) Myc.FreeResult(); - To_Def->GetHandler()->MakeKeyWhere(g, Query->GetStr(), - op, "`", key, len); + To_Def->GetHandler()->MakeKeyWhere(g, Query, op, '`', key, len); if (To_CondFil) { oom = Query->Append(" AND ("); diff --git a/storage/connect/value.cpp b/storage/connect/value.cpp index e012e12d00b..7d3d5463129 100644 --- a/storage/connect/value.cpp +++ b/storage/connect/value.cpp @@ -627,13 +627,16 @@ int TYPVAL<double>::GetValLen(void) template <class TYPE> bool TYPVAL<TYPE>::SetValue_pval(PVAL valp, bool chktype) { - if (chktype && Type != valp->GetType()) - return true; + if (valp != this) { + if (chktype && Type != valp->GetType()) + return true; - if (!(Null = valp->IsNull() && Nullable)) - Tval = GetTypedValue(valp); - else - Reset(); + if (!(Null = valp->IsNull() && Nullable)) + Tval = GetTypedValue(valp); + else + Reset(); + + } // endif valp return false; } // end of SetValue @@ -1319,15 +1322,18 @@ ulonglong TYPVAL<PSZ>::GetUBigintValue(void) /***********************************************************************/ bool TYPVAL<PSZ>::SetValue_pval(PVAL valp, bool chktype) { - if (chktype && (valp->GetType() != Type || valp->GetSize() > Len)) - return true; + if (valp != this) { + if (chktype && (valp->GetType() != Type || valp->GetSize() > Len)) + return true; - char buf[64]; + char buf[64]; - if (!(Null = valp->IsNull() && Nullable)) - strncpy(Strp, valp->GetCharString(buf), Len); - else - Reset(); + if (!(Null = valp->IsNull() && Nullable)) + strncpy(Strp, valp->GetCharString(buf), Len); + else + Reset(); + + } // endif valp return false; } // end of SetValue_pval @@ -2063,18 +2069,21 @@ double BINVAL::GetFloatValue(void) /***********************************************************************/ bool BINVAL::SetValue_pval(PVAL valp, bool chktype) { - if (chktype && (valp->GetType() != Type || valp->GetSize() > Clen)) - return true; - bool rc = false; - - if (!(Null = valp->IsNull() && Nullable)) { - if ((rc = (Len = valp->GetSize()) > Clen)) - Len = Clen; + + if (valp != this) { + if (chktype && (valp->GetType() != Type || valp->GetSize() > Clen)) + return true; - memcpy(Binp, valp->GetTo_Val(), Len); - } else - Reset(); + if (!(Null = valp->IsNull() && Nullable)) { + if ((rc = (Len = valp->GetSize()) > Clen)) + Len = Clen; + + memcpy(Binp, valp->GetTo_Val(), Len); + } else + Reset(); + + } // endif valp return rc; } // end of SetValue_pval @@ -2629,21 +2638,24 @@ bool DTVAL::MakeDate(PGLOBAL g, int *val, int nval) /***********************************************************************/ bool DTVAL::SetValue_pval(PVAL valp, bool chktype) { - if (chktype && Type != valp->GetType()) - return true; + if (valp != this) { + if (chktype && Type != valp->GetType()) + return true; - if (!(Null = valp->IsNull() && Nullable)) { - if (Pdtp && !valp->IsTypeNum()) { - int ndv; - int dval[6]; + if (!(Null = valp->IsNull() && Nullable)) { + if (Pdtp && !valp->IsTypeNum()) { + int ndv; + int dval[6]; + + ndv = ExtractDate(valp->GetCharValue(), Pdtp, DefYear, dval); + MakeDate(NULL, dval, ndv); + } else + Tval = valp->GetIntValue(); - ndv = ExtractDate(valp->GetCharValue(), Pdtp, DefYear, dval); - MakeDate(NULL, dval, ndv); } else - Tval = valp->GetIntValue(); + Reset(); - } else - Reset(); + } // endif valp return false; } // end of SetValue diff --git a/storage/connect/xobject.cpp b/storage/connect/xobject.cpp index 817acb561fe..92bf039c07c 100644 --- a/storage/connect/xobject.cpp +++ b/storage/connect/xobject.cpp @@ -290,6 +290,34 @@ bool STRING::Set(char *s, uint n) } // end of Set /***********************************************************************/ +/* Append a char* to a STRING. */ +/***********************************************************************/ +bool STRING::Append(const char *s, uint ln) +{ + if (!s) + return false; + + uint len = Length + ln + 1; + + if (len > Size) { + char *p = Realloc(len); + + if (!p) + return true; + else if (p != Strp) { + strcpy(p, Strp); + Strp = p; + } // endif p + + } // endif n + + strncpy(Strp + Length, s, ln); + Length = len - 1; + Strp[Length] = 0; + return false; +} // end of Append + +/***********************************************************************/ /* Append a PSZ to a STRING. */ /***********************************************************************/ bool STRING::Append(PSZ s) diff --git a/storage/connect/xobject.h b/storage/connect/xobject.h index 3660ee86918..8e2358dd526 100644 --- a/storage/connect/xobject.h +++ b/storage/connect/xobject.h @@ -134,6 +134,7 @@ class DllExport STRING : public BLOCK { inline void Reset(void) {*Strp = 0;} bool Set(PSZ s); bool Set(char *s, uint n); + bool Append(const char *s, uint ln); bool Append(PSZ s); bool Append(STRING &str); bool Append(char c); |