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
__________________________________________________________________________
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
