MySQL
Querying Overview
MySQL is a popular open-source relational database management system (RDBMS) licensed by Oracle. It uses structured query language (SQL) for managing and querying data. MySQL supports a wide variety of data types including JSON and the cloud version support vectors. Its features have made it widely adopted across different industries for storing and managing structured data.
A general reference for MySQL querying can be found at
https://dev.mysql.com/doc/mysql-tutorial-excerpt/5.7/en/examples.html
Handling JSON Data
For information on JSON functions see https://dev.mysql.com/doc/refman/8.4/en/json.html
A short tutorial for using JSON with MySQL can be found at https://www.digitalocean.com/community/tutorials/working-with-json-in-mysql
Consider a table defined as the following.
CREATE TABLE `e_store`.`products`(
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(250) NOT NULL ,
`brand_id` INT UNSIGNED NOT NULL ,
`category_id` INT UNSIGNED NOT NULL ,
`attributes` JSON NOT NULL ,
PRIMARY KEY(`id`) ,
INDEX `CATEGORY_ID`(`category_id` ASC) ,
INDEX `BRAND_ID`(`brand_id` ASC) ,
CONSTRAINT `brand_id` FOREIGN KEY(`brand_id`) REFERENCES `e_store`.`brands`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE ,
CONSTRAINT `category_id` FOREIGN KEY(`category_id`) REFERENCES `e_store`.`categories`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE
);
The table is populated with this statement.
INSERT INTO `e_store`.`products`(
`name` ,
`brand_id` ,
`category_id` ,
`attributes`
)
VALUES(
'Desire' ,
'2' ,
'2' ,
JSON_OBJECT(
"network" , JSON_ARRAY("GSM" , "CDMA" , "HSPA" , "EVDO") ,
"body" , "5.11 x 2.59 x 0.46 inches" ,
"weight" , "143 grams" ,
"sim" , "Micro-SIM" ,
"display" , "4.5 inches" ,
"resolution" , "720 x 1280 pixels" ,
"os" , "Android Jellybean v4.3"
)
);
Traditional mysql command line output is shown below.
Notice the attributes column data is just a string.
In Qarbine, the query specification below retrieves all the rows.
SELECT * FROM products
The results are shown below. Notice the attributes values are JSON objects.
Select the “Desire” row.
Notice the attributes column value is a real object. Conversions to JSON objects happen automatically. This enables much easier analytics than manually trying to deal with the simple string value and writing code to convert strings into objects and other burdensome activities.
Access to the result row values of interest in a template formula may use either
@current.attributes.os
or
#attributes.os
This example can be found at “example/MySQL/eStore/All products”.
Manipulating Row Shape
Qarbine can be directed to further modify the answer set elements This is done using ‘pragmas” which are discussed in the general Data Source Designer documentation. The most common pragma likely to be used is “pullFieldsUp CSV_list_of_fields”.
This answer set can be simplified by using Qarbine pragmas as shown below.
#pragma pullFieldsUp attributes
SELECT * FROM products
The results are shown below.
The fields that were previously within the data field object haven been pulled up a level. This makes it more convenient to reference field values in a template formula. Access to the result row values of interest in a template formula may now use either
@current.os
or
#os
This example can be found at “example/MySQL/eStore/All products with pragma”.
In this case the attributes field values across the answer set rows are very diverse so the Data Source Designer’s columns may be incomplete.
Timestamp Handling
MySQL has many functions to manipulate dates and times. A resource for this is
https://dev.mysql.com/doc/refman/8.4/en/date-and-time-functions.html
You also can mix regular JSON functions with Qarbine pragmas to achieve various results.
Consider a table than has a JSON column containing a field storing a timestamp as a string. A sample query is
select * from foo
Here is a sample row
The string_column can be converted into a Date using the pragma
#pragma convertToDate string_column
The updated results are shown below.
The pragams are evaluated in order upon the answer set. Consider a JSON object in the column ‘item’ that has a string date in the ‘firstPublished’ field. The string can be converted in place using.
#pragma convertToDate item.firstPublished
We can also combine pragmas such as shown below.
#pragma pullFieldsUp item
#pragma convertToDate firstPublished
Troubleshooting
If errors occur while using Qarbine then a good course of action is to run the query using your traditional MySQL tools.
To view the final SQL statement after variable substitution and macro block evaluation use “ALT” click for the run icon.