vteam #550 was formed to migrate the version of client’s application from Cake 1 to Cake 3. The application was connected with 2 databases; MySQL and MongoDB. These databases were required to be merged in a single database. After application’s analysis, it was found that all the data was stored in MySQL database except the logs data – stored in MongoDB because of NoSQL.
To merge them in a single database, a data storage consisted of both SQL and NoSQL was needed.
Proposed Solution:
A solution of MariaDB was proposed by vteam #550. Reason being all MySQL features existed in MariaDB. Also, the following features of NoSQL were supported that helped vteams engineer Mahmood Ali to migrate the logs data from MongoDB to MariaDB:
- Dynamic Columns
- Load_file
- Connect Storage Engine
.
Dynamic Columns:
These columns allowed the storage of different sets of columns for each row in a table. A set of columns was stored in a blob having a small set of functions to manipulate. Following were some functions provided by MariaDB for the operations on dynamic columns/blob data:
1- COLUMN_CREATE:
It is used to create a dynamic column that stores specified columns with values.
1 | COLUMN_CREATE(column_nr, value [as type], [column_nr, value [as type]]...); |
2- COLUMN_ADD:
It is used to add/update dynamic blob columns.
1 | COLUMN_ADD(dyncol_blob, column_nr, value [as type], [column_nr, value [as type]]...); |
3- COLUMN_GET:
It is used to get the value of a dynamic column by its name. If no column exists with the given name, NULL will be returned.
1 | COLUMN_GET(dyncol_blob, column_nr as type); |
4- COLUMN_CHECK:
Check if dyncol_blob is a valid packed dynamic columns blob or not. Return value – 1 means the blob is valid, Return value – 0 means not a valid blob.
5- COLUMN_DELETE:
Delete a dynamic column with the specified name. Multiple names can be given.
1 | COLUMN_DELETE(dyncol_blob, column_name, column_name...); |
6- COLUMN_EXISTS:
Check if a column with name column_name exists in dyncol_blob. If yes, it will return value – 1, otherwise return value will be 0.
7- COLUMN_LIST:
It is used to get a list of columns.
1 | COLUMN_LIST(dyncol_blob); |
8- COLUMN_JSON:
Return a JSON representation of data in dyncol_blob.
Load_File:
It would read the file and return file contents as a String. To use this function, file should be located on the host server.
1 | UPDATE t SET blob_col=LOAD_FILE('/tmp/picture') WHERE id=1; |
Connect Storage Engine:
It would enable MariaDB to access external, local or remote data. This was done by defining tables based on different data types (particular files in various formats), data extracted from other DBMS or products (such as Excel) via ODBC, or data retrieved from the environment (for example DIR, WMI, and MAC tables).
Comments are closed.