We saw in the previous post how we can deal with data stored in the new VECTOR datatype that was released with MySQL 9.0.

We implemented the 4 basic mathematical operations between two vectors. To do so we created JavaScript functions. MySQL JavaScript functions are available in MySQL HeatWave and MySQL Enterprise Edition (you can use MySQL EE for free while learning, developing, and prototyping as mentioned here).

For the MySQL Community Users, extending the operations dealing with Vectors can be done by implementing User Defined Functions (UDFs) in C++ as a component.

In this article, we will see how we can create a component to add our 4 mathematical operations to MySQL.

Of course, you need to have the source code of MySQL and be able to compile it. For more information please refer to these blog posts:

• Build MySQL 8 from the source rpm in OL9
• Extending MySQL using the Component Infrastructure – part 1
• Extending MySQL using the Component Infrastructure – part 13: FAQ

The Code

The code of the component consists of 3 files that should be placed in a dedicated folder in the components directory of the source code:

mysql-server
├──components
   └──vector_operations
      ├── CMakeLists.txt
      ├── vector_operations.cc
      └── vector_operations.h

Disclaimer:

This code is not intended for production use and is provided solely for illustrative purposes

The code is available on GitHub.

Once compiled, you get the component file to load:

Testing

We can load the component and test it:

In the image above, we can see how we loaded the component and then we can see that the UDFs for all four operations are available.

SQL> select vector_to_string(
       vector_addition(
          string_to_vector('[1,2,3]'),
          string_to_vector('[4,5,6]')
       )
     ) sum;
+---------------------------------------+
| sum                                   |
+---------------------------------------+
| [5.00000e+00,7.00000e+00,9.00000e+00] |
+---------------------------------------+
1 row in set (0.0002 sec)

In comparison to the function developed in JavaScript in the previous article, this time we utilize the VECTOR datatype as both the input and output, we don’t use the string representation.

We can test with the same table as in the previous post:

SQL> select id, vector_addition(vec1, vec2) vec_sum from t1;
+----+----------------------------+
| id | vec_sum                    |
+----+----------------------------+
|  1 | 0x0000C0400000404100000040 |
|  2 | 0x0000004066662A4200001C43 |
+----+----------------------------+
2 rows in set (0.0008 sec)

SQL> select id, vector_to_string(vector_addition(vec1, vec2)) vec_sum
     from t1;
+----+---------------------------------------+
| id | vec_sum                               |
+----+---------------------------------------+
|  1 | [6.00000e+00,1.20000e+01,2.00000e+00] |
|  2 | [2.00000e+00,4.26000e+01,1.56000e+02] |
+----+---------------------------------------+
2 rows in set (0.0003 sec)

Conclusion

The VECTOR datatype is a valuable feature of MySQL 9.0 and can be extended with ease, much like other datatypes in MySQL, using your UDFs.

Extending MySQL using the Component Architecture is relatively straightforward but more complex to build and deploy in the cloud, where creating JavaScript functions is easier with MySQL HeatWave.

Enjoy MySQL, Vectors, and coding components in C++!