|
I can try compile Sveta's UDF's for MariaDB.but where can I get the source code?
|
|
It is still available at Oracle website: http://downloads.mysql.com/snapshots/pb/mysql-json-udfs-0.4.0/mysql-json-udfs-0.4.0-labs-json-udfs-src.tar.gz
|
|
Thanks Sveta. After downloading the source code, as was able to compile and test it on MariaDB 10.1 (I could also do it on MariaDB 10.0) It was not necessary to compile pcre it was already included in the MariaDB solution.
Because I work principally on Windows with Visual Studio I did not use CMake or any such tool. Here is what I did:
I added a new project in the Visual Studio MariaDB solution, then manually added the source files of json_udf to it. I had to manually set some properties because not using CMake:
1 - Set the Additional Include Directories (where are the source code and pcre code)
2 - Set the required preprocessor variables:
_CRT_SECURE_NO_WARNINGS
_SCL_SECURE_NO_WARNINGS
HAVE_CONFIG_H
VERSION_MAJOR=0
VERSION_MINOR=4
VERSION_PATCH=0
JSON_VERBOSE_LEVEL=3
3 - Set the Additional Library Directories (for pcre)
4 - Add the additional libraries:
Debug\pcreposixd.lib
Debug\pcred.lib
Finally, to avoid some compile or link errors, I was obliged to do some changes/additions in the source code:
In my_json.cc line 232:
In JCallback.cc line 808:
bool treat_percent_as_real= false;
|
In JParser.cc before line 17:
#define PCRE_STATIC 1 /* Important on Windows */
|
Once done it worked beautifully, some examples:
create table assets (
|
item_name varchar(32) primary key, /* A common attribute for all items */
|
dynamic_cols blob /* Dynamic columns will be stored here */
|
);
|
INSERT INTO assets VALUES('MariaDB T-shirt', COLUMN_CREATE('color', 'blue', 'size', 'XL'));
|
INSERT INTO assets VALUES('Thinkpad Laptop', COLUMN_CREATE('color', 'black', 'price', 500));
|
SELECT json_extract((select COLUMN_JSON(dynamic_cols) FROM assets where item_name='Thinkpad Laptop'),'$.price');
|
SELECT item_name, json_extract(dyncol ,'$.color') color from (select item_name, COLUMN_JSON(dynamic_cols) as dyncol FROM assets) x;
|
The last query replies:
| item_name |
color |
| MariaDB T-shirt |
blue |
| Thinkpad Laptop |
black |
|
|
The query:
select json_search('{"ISBN":"9782212090819","AUTHOR":[{"FIRSTNAME":"Jean-Michel","LASTNAME":"Bernadac"},{"FIRSTNAME":"François","LASTNAME":"Knab"}],"TITLE":"Construire une application XML"}','"Knab"');
|
returns:
Shouldn't it be:
Is it a bug or my mis-interpreting this function result?
|
|
This is a bug
|
|
It occurs both with the old and new path versions.
Array indexes are ok only for an array in last position. For instance this is Ok:
+--------------------------------------+
|
| json_search('[45,28,36,45,89]','36') |
|
+--------------------------------------+
|
| $[2] |
|
+--------------------------------------+
|
but this is not:
+------------------------------------------+
|
| json_search('[[45,28],[36,45,89]]','36') |
|
+------------------------------------------+
|
| $[6][3] |
|
+------------------------------------------+
|
Same errors with json_extract:
Ok:
+-----------------------------------------+
|
| json_extract('[45,28,36,45,89]','$[2]') |
|
+-----------------------------------------+
|
| 36 |
|
+-----------------------------------------+
|
Not Ok:
+------------------------------------------------+
|
| json_extract('[[45,28],[36,45,89]]','$[1][0]') |
|
+------------------------------------------------+
|
| NULL |
|
+------------------------------------------------+
|
It printed these messages:
UDF-level error: In make_jeargs_from_path [
|
UDF-level error: In make_jeargs_from_path [
|
UDF-level error: In make_jeargs_from_path [
|
UDF-level error: Path parse error
|
UDF-level error: In make_jeargs_from_path [
|
UDF-level error: In make_jeargs_from_path [
|
UDF-level error: In make_jeargs_from_path [
|
UDF-level error: In make_jeargs_from_path [
|
UDF-level error: Path parse error
|
UDF-level error: In make_jeargs_from_path [
|
UDF-level error: In make_jeargs_from_path [
|
UDF-level error: In make_jeargs_from_path [
|
UDF-level error: Path parse error
|
|
|
Patch for JSON_EXTRACT bug:
diff -ur /home/sveta/src/json_udf/mysql-json-udfs-0.4.0-labs-json-udfs-src_orig/my_json_udf_path.cc /home/sveta/src/json_udf/mysql-json-udfs-0.4.0-labs-json-udfs-src/my_json_udf_path.cc
|
--- /home/sveta/src/json_udf/mysql-json-udfs-0.4.0-labs-json-udfs-src_orig/my_json_udf_path.cc 2015-03-05 22:29:34.000000000 +0200
|
+++ /home/sveta/src/json_udf/mysql-json-udfs-0.4.0-labs-json-udfs-src/my_json_udf_path.cc 2015-08-18 13:58:58.603175861 +0300
|
@@ -1097,7 +1097,6 @@
|
}
|
break;
|
case '[':
|
- jdump(JSON_ERROR, JSON_UDF_ERROR, "In make_jeargs_from_path [");
|
if (1 < i)
|
{
|
if (is_key)
|
@@ -1119,6 +1118,10 @@
|
is_array= true;
|
}
|
}
|
+ else if (!is_array)
|
+ {
|
+ is_array= true;
|
+ }
|
else
|
{
|
jdump(JSON_ERROR, JSON_UDF_ERROR, "Path parse error");
|
|
|
|
|
Fix for case
select json_search('{"ISBN":"9782212090819","AUTHOR":[{"FIRSTNAME":"Jean-Michel","LASTNAME":"Bernadac"},{"FIRSTNAME":"François","LASTNAME":"Knab"}],"TITLE":"Construire une application XML"}','"Knab"');
|
:
diff -ur /home/sveta/src/json_udf/mysql-json-udfs-0.4.0-labs-json-udfs-src_orig/JCallback.cc /home/sveta/src/json_udf/mysql-json-udfs-0.4.0-labs-json-udfs-src/JCallback.cc
|
--- /home/sveta/src/json_udf/mysql-json-udfs-0.4.0-labs-json-udfs-src_orig/JCallback.cc 2015-03-05 22:29:34.000000000 +0200
|
+++ /home/sveta/src/json_udf/mysql-json-udfs-0.4.0-labs-json-udfs-src/JCallback.cc 2015-08-18 15:14:22.129083555 +0300
|
@@ -868,13 +868,13 @@
|
|
memcpy(&(path[path_len]->key[0]), &doc[key_start + 1], key_len - 2);
|
path_len++;
|
- arr_ind= -1;
|
}
|
else if (is_array_elem)
|
{
|
char* key= new char[33];
|
sprintf(key, "%d", this->arr_ind);
|
path[path_len++]= new JPath(key, strlen(key), true);
|
+ arr_ind= -1;
|
}
|
cur_pos= pos;
|
}
|
|
|
I applied your patches and most errors are gone.
However, this one remains:
+------------------------------------------+
|
| json_search('[[45,28],[36,45,89]]','36') |
|
+------------------------------------------+
|
| $[2][3] |
|
+------------------------------------------+
|
|
|
Yes, this one was not fixed.
|