Open Stack

Oslo.db devs :

We've developed a system by which CIDR math, such as that of detecting 
region overlaps, can be performed on a MySQL database within queries [1] 
[2].   This feature makes use of a custom stored function I helped to 
produce which provides functionality similar to that which Postgresql 
provides built in [3].   SQLite also supports a simple way to add CIDR 
math functions as well which I've demonstrated at [4].

Note that I use the term "function" and not "procedure" to stress that 
this is not a "stored procedure" in the traditional sense of performing 
complex business logic and persistence operations - this CIDR function 
performs a calculation that is not at all specific to Openstack, and is 
provided already by other databases as a built-in, and nothing else.

The rationale for network-math logic being performed in the relational 
database is so that SQL like SELECT, UPDATE, and INSERT can make use of 
CIDR overlaps and other network math, such as to locate records that 
correspond to network ranges in some way and of course to provide guards 
and constraints, like that of concurrent UPDATE statements against 
conflicting ranges as well as being able to produce INSERT constraints 
for similar reasons.   Both MySQL and Postgresql have support for 
network number functions, Postgresql just has a lot more.

The INSERT constraint problem is also addressed by our patch and makes 
use of an INSERT trigger on MySQL [5], but on Postgresql we use a GIST 
index which has been shown to be more reliable under concurrent use than 
a trigger on this backend [6].

Not surprisingly, there's a lot of verbosity to both the production of 
the MySQL CIDR overlap function and the corresponding trigger and 
constraint, as well as the fact that to support the addition of these 
functions / constraints at both the Alembic migration level as well as 
that of the model level (because we would like metadata.create_all() to 
work), they are currently stated twice within this patch within their 
full verbosity.    This is sub-optimal, and while the patch here makes 
use of an Alembic recipe [7] to aid in the maintenance of special DDL 
constructs, it's adding lots of burden to the Neutron codebase that 
could be better stated elsewhere.

The general verbosity and unfamiliarity of these well known SQL features 
is understandably being met with trepidation.  I've identified that this 
trepidation is likely rooted in the fact that unlike the many other 
elaborate SQL features we use like ALTER TABLE, savepoints, subqueries, 
SELECT FOR UPDATE, isolation levels, etc. etc., there is no warm and 
fuzzy abstraction layer here that is both greatly reducing the amount of 
explicit code needed to produce and upgrade the feature, as well as 
indicating that "someone else" will fix this system when it has problems.

Rather than hobbling the entire Openstack ecosystem to using a small 
subset of what our relational databases are capable of, I'd like to 
propose that preferably somewhere in oslo.db, or elsewhere, we begin 
providing the foundation for the use of SQL features that are rooted in 
mechanisms such as triggers and small use of stored functions, and more 
specifically begin to produce network-math SQL features as the public 
API, starting with this one.


[1] 
https://review.openstack.org/gitweb?p=openstack/neutron.git;a=blob;f=neutron/db/migration/alembic_migrations/versions/newton/expand/5bbf1e0b1774_add_stored_procedure_and_trigger_for_.py;h=8af394d319d119f57b224d391c844c0a87178856;hb=90f46e235672d3917015e5c49aa0513fb1de7ba9#l36

[2] https://review.openstack.org/#/c/314054/

[3] https://www.postgresql.org/docs/9.1/static/functions-net.html

[4] https://gist.github.com/zzzeek/a3bccad40610b9b69803531cc71a79b1

[5] 
https://review.openstack.org/gitweb?p=openstack/neutron.git;a=blob;f=neutron/db/migration/alembic_migrations/versions/newton/expand/5bbf1e0b1774_add_stored_procedure_and_trigger_for_.py;h=8af394d319d119f57b224d391c844c0a87178856;hb=90f46e235672d3917015e5c49aa0513fb1de7ba9#l92

[6] 
https://review.openstack.org/gitweb?p=openstack/neutron.git;a=blob;f=neutron/db/migration/alembic_migrations/versions/newton/expand/5bbf1e0b1774_add_stored_procedure_and_trigger_for_.py;h=8af394d319d119f57b224d391c844c0a87178856;hb=90f46e235672d3917015e5c49aa0513fb1de7ba9#l116

[7] 
http://alembic.zzzcomputing.com/en/latest/cookbook.html#replaceable-objects