When we talk about extending a database server, we usually think about features that could benefit a very large number of users.
A new authentication mechanism, a storage engine, an audit plugin, a new generic data type such as UUID or INET, additional JSON functions… these are relatively easy to justify because their potential audience is large.
But plugins don’t necessarily need to solve generic problems.
In fact, I think one of the most interesting aspects of the MariaDB Server plugin architecture is exactly the opposite:
you can extend the database with something extremely specific to your own domain without requiring everybody else to carry that functionality.
This is exactly what I did during my holidays (to be geek or not to be).
From Generic Plugins to Domain-Specific Plugins
Most of the plugins I’ve been experimenting with so far solve fairly generic problems.
Types such as CIDR, MAC addresses, URLs, or semantic versions may make sense for applications worldwide.
So last week, I had to pay some bills (phone, electricity, healthcare, …) and I did so with my youngest daughter. She was dictating to me the structured communication to enter on the phone while making the payment.
Of course, she asked me what those numbers meant, and some of my IT lessons from 1994-95 came back to mind when our teacher explained how these numbers are validated.
Very Belgian Data Type: BEL_PAY_REF
So in a Belgian invoice, you can see something like this:
+++123/4567/89002+++
This is a Belgian structured payment reference.
It contains 12 digits. The first ten digits form the base, while the final two digits are check digits calculated using modulo 97.
Outside Belgium, almost nobody would need it. This would make absolutely no sense to have such a data type as a standard built-in in MariaDB.
Inside Belgium, however, it is everywhere.
So I created the BEL_PAY_REF data type plugin.
With the plugin installed, instead of doing this:
CREATE TABLE invoices (
id BIGINT UNSIGNED PRIMARY KEY,
payment_reference VARCHAR(20)
);we can do this:
CREATE TABLE invoices (
id BIGINT UNSIGNED PRIMARY KEY,
payment_reference BEL_PAY_REF NOT NULL
);The difference looks small, but semantically it is huge.
The plugin accepts both the canonical representation and its compact 12-digit representation, validates the checksum when values cross storage or cast boundaries, and stores the reference in canonical form. The type is implemented as a fixed-size value and can be indexed directly.
For example:
INSERT INTO invoices
VALUES (1, '+++123/4567/89002+++');
INSERT INTO invoices
VALUES (2, '123456789002');Both result in:
+++123/4567/89002+++The database now understands what this value is.
And an invalid reference:
INSERT INTO invoices
VALUES (3, '+++123/4567/89003+++');is rejected in strict mode because the checksum is incorrect.
That validation no longer needs to be duplicated in PHP, Java, Python, Go, or any other application that writes to the database.
The rule lives with the data.
Functions Come Naturally With the Type
Once MariaDB understands the domain, useful operations can also be implemented as SQL functions.
The plugin provides functions such as:
SELECT BEL_PAY_REF_IS_VALID('+++123/4567/89002+++');+----------------------------------------------+
| BEL_PAY_REF_IS_VALID('+++123/4567/89002+++') |
+----------------------------------------------+
| 1 |
+----------------------------------------------+
1 row in set (0.000 sec)Extracting the base:
SELECT BEL_PAY_REF_BASE('+++123/4567/89002+++');
+------------------------------------------+
| BEL_PAY_REF_BASE('+++123/4567/89002+++') |
+------------------------------------------+
| 1234567890 |
+------------------------------------------+
1 row in set (0.000 sec)Or generating the complete structured reference from its base:
SELECT BEL_PAY_REF_GENERATE('1234567890');
+------------------------------------+
| BEL_PAY_REF_GENERATE('1234567890') |
+------------------------------------+
| +++123/4567/89002+++ |
+------------------------------------+
1 row in set (0.000 sec)There is even a helper for applications that generate references from a prefix and sequence:
SELECT BEL_PAY_REF_GENERATE_PARTS(123, 42);which generates:
+-------------------------------------+
| BEL_PAY_REF_GENERATE_PARTS(123, 42) |
+-------------------------------------+
| +++123/0000/04278+++ |
+-------------------------------------+
1 row in set (0.000 sec)The plugin also exposes validation details as JSON when an application needs to know why a value is invalid.
Again, none of this belongs in MariaDB Server for everybody.
But for applications processing Belgian payments, it can be very useful.
And that’s precisely why a plugin is such a good fit.
If you are interested, you can find the plugin on this GitHub repository: mariadb-plugin-type-bel-pay-ref
Going Even More Belgian: The BCE Number
My second experiment goes further. Once again, it comes from a real-life situation. We just fixed our roof at home. I asked ChatGPT to verify the estimate and the invoice to be sure everything was as expected.
And ChatGPT pointed out that the estimate and the invoice had two different enterprise numbers (just for info, this was correct; the business owner changed the company’s legal structure in the meantime).
So I decided that this could also be a good idea for a MariaDB plugin.
Every Belgian enterprise is identified by the Crossroads Bank for Enterprises (BCE in French, KBO in Dutch).
An enterprise number looks like:
0417.497.106Once again, most applications probably store this as:
VARCHAR(12)Maybe the application validates it, maybe it doesn’t, and maybe one application stores:
0417497106another:
0417.497.106and yet another:
BE0417497106This is exactly the kind of inconsistency a domain-specific data type can eliminate.
My BCE plugin allows:
CREATE TABLE companies (
id BIGINT UNSIGNED PRIMARY KEY,
enterprise_number BCE NOT NULL UNIQUE
);Then:
INSERT INTO companies VALUES
(1, '0417497106'),
(2, 'BE 1000.123.448');MariaDB normalizes those values to their canonical dotted notation.
The type also verifies the BCE modulo-97 checksum during assignment.
BCE Goes One Step Further
The BCE experiment also made me wonder how far such a type should go.
Checksum validation is deterministic and belongs naturally close to the type.
But a BCE number represents an actual registered entity.
So being syntactically valid doesn’t necessarily mean that the enterprise exists.
This led to some additional functions:
SELECT BCE_NUMBER_EXISTS('0417.497.106');SELECT BCE_NUMBER_IS_ACTIVE('0417.497.106');and:
SELECT BCE_NUMBER_INFO('0417.497.106');The plugin supports a local registry provider backed by a MariaDB table, and it can optionally use CBEAPI for remote registry queries. The local provider can be populated from Belgium’s official BCE/KBO open-data files.
The plugin is also available on GitHub: mariadb-plugin-type-bce
Not Every Useful Plugin Needs One Million Users
Open source projects naturally tend to focus on features that benefit large numbers of people.
That makes perfect sense for the core server.
Adding every country’s tax identifiers, payment references, and administrative numbers directly into MariaDB Server would obviously be ridiculous.
Imagine:
BEL_PAY_REF
BCE
FRENCH_SIRET
...in the standard server distribution. No thanks. 😉
But plugins change the equation.
The core server can remain generic while allowing users to make their specific MariaDB installation highly specialized.
That is one of the architectural advantages of extensibility.
A plugin doesn’t have to justify its existence to every MariaDB user.
It only has to solve a real problem for the people installing it.
Conclusion
The beauty of a plugin architecture is that MariaDB Server doesn’t need to know everybody’s domain — it just needs to provide us with the hooks to teach it ours.
You can find a large list of plugins in our MariaDB Server Ecosystem Hub.
Enjoy MariaDB Server, enjoy writing or using plugins, and enjoy MariaDB Server in Belgium! 🇧🇪