oslo.db test suite is too rigidly hardcoded to a small number of potential database backends

[Mike Bayer]

I would like to propose improvements to the oslo.db test suite regarding alternate backends.

Right now, tests are hardcoded to a specific backend, which at the moment is usually SQLite, and in the case of a so-called "opportunistic" test, either MySQL or Postgresql.

When an "opportunistic" test runs, the system is hardcoded to a URL that is: "<driver>://openstack_citest:openstack_citest@localhost/openstack_citest". This hardcoding is both in OpportunisticFixture as well as utils.get_connect_string().

The approach has the following limitations:

1. it is not possible to write a test that itself can run against multiple backends - OpportunisticFixture has a single "DRIVER" member.

2. It is not possible to test against backends that don't use a hostname-based configuration. This includes Oracle, IBM DB2, as well as any system that uses an ODBC driver of any kind.

3. for backends that are hostname-based, they must be present on "localhost". It is not possible to run the tests against a test database, such as an Oracle, DB2 or other backend that is on a remote host.

4. it is not possible to run the tests on even a local workstation without the inconvenient necessity of creating a database name/user/password named "openstack_citest".

Note that the current system as it runs on CI servers is *entirely fine*, I am not proposing any change to any default behavior. I'm putting up this bug report as a TODO for myself to write a blueprint for a more open-ended system.

[Mike Bayer (zzzeek)]

notes: "Every test run in its temporary database, citest credentials is just an entry point". Ok this is interesting in that the openstack_citest user needs "CREATE DATABASE" privs. Running on a "big iron" system like a DB2 or Oracle, this might not be feasible. We should look into provision.py ->create_database and work on abstracting this out. A test suite should be able to arbitrarily provision a "test bed", and the system should be able to choose different strategies: 1. the existing "make a random schema" system, 2. choose between a pool of existing schemas, when all are "checked out" then block additional concurrent tests until complete (or raise an error that the pool needs to be bigger) 3. tests must be serialized (pool size of one).

[Mike Bayer (zzzeek)]

roman - what do you think of, that provision would make N number of schemas based on how many concurrent tests are run, and re-use those same N schemas over and over ? I'm considering that plus some API that abstracts how we do "transactional" tests vs. "drop the schema" tests:

    provisioner = provisioning.Provisioner("postgresql://some_db", "mysql://some_other_db", ...)
    schema = provisioner.provision("mysql") # gets some space where we can put tables
    self.addCleanup(schema.clear) # drops all tables/symbols unconditionally

    some_metadata.create_all(schema.bind) # create a schema (note we are not using migrations in the typical case)

    with schema.temp_transaction as trans: # temp transaction
        trans.associate_with(SomeSession)

        # do test

I want the system to work at that kind of level.

[Mike Bayer (zzzeek)]

oh and of course, provisioner.dispose() at the very end! this implies that having suite-level cleanup would be very useful.

[Roman Podoliaka (rpodolyaka)]

I'm all for it!

This is pretty much how I tried to run Nova db methods unit tests on PostgreSQL and MySQL - https://review.openstack.org/#/c/33236/ (at the same time, I did provisioning at testrepository runner level, putting it to oslo.db would definitely make it easier to reuse this code).

A couple of comments:

1. some_metadata.create_all(schema.bind) - this is optimal for the unit tests we have in oslo.db, but in projects like Nova we could probably run migration scripts/do metadata.create_all() just once, as the upgrade to the latest version of the schema is rather slow.

2. As you might have seen, in some projects (e.g. Nova and Neutron) we have a problem that model definitions don't correspond to migration scripts. I believe, we've fixed most of the differences, but a few might still exist.

[Joshua Harlow (harlowja)]

A question, why does this need to be in oslo.db? It seems like a generic provisioning testing toolkit, mini-library. Seems like it could be useful outside of openstack as a general (but likely small) test utility.

[Angus Lees (gus)]

Note that a small part of the original concerns are addressed by:
* https://review.openstack.org/#/c/104428/
* https://review.openstack.org/#/c/104447/

The latter in particular (OS_TEST_DBAPI_ADMIN_CONNECTION) was a feature of the DB unittests in oslo-incubator that failed to make it into oslo.db when that code was split out.

[Mike Bayer (zzzeek)]

zzzeek: devananda: the SQLA tests, I can run them like py.test —dburi mysql+mysqldb://… —dburi mysql+oursql://… —dburi mysql+mysqlconnector://.. and tests marked as “backend” will run for all three
[12:55pm] dhellmann: zzzeek: expanding the testing options sounds like a good idea, too
[12:55pm] devananda: zzzeek: ++
[12:55pm] zzzeek: dhellmann: so i might have to use some metaclass magic to make it happen in oslo.db’s tests
[12:56pm] dhellmann: zzzeek: maybe we can use testtools scenarios and environment variables to achieve the same thing
[12:56pm] zzzeek: dhellmann: OK maybe, ill look into that...
[12:56pm] zzzeek: dhellmann: basically the discovery has to come up wiht more tests than are actually listed out
[12:56pm] dhellmann: zzzeek: there should be some examples of using scenarios in the ceilometer test suite

[Mike Bayer (zzzeek)]

So current thinking:

1. I think testr's "instance_provision" thing gives us a clean/guaranteed way of creating and dropping databases surrounding a test run completely, so we probably need to use it.

2. we will use testscenarios: https://pypi.python.org/pypi/testscenarios/ to generate per-DB scenarios. I've poked into how this works, it augments TestCase.run() so that it multiplies a single run() into many. It's not my style (I prefer multiplying tests at discovery time) but it's what we use in openstack land and it will work so OK!

3. some of the monkeypatching etc. concepts from https://review.openstack.org/#/c/33236/ will be used, e.g. provision.py will be mocking out any number of get_session(), get_engine() calls here and there as instructed.

4. for "transactional" tests, we will adapt my recipe from https://gist.github.com/zzzeek/8443477 which does transaction-per-test and also accommodates test code that calls session.rollback()

5. database connectivity will be set up using an env var, such as:

    OSLO_DBS=postgresql+psycopg2://foo:bar@host/test;mysql+mysqlconnector://foo:bar@host/test;mysql+mysqldb://foo:bar@host/test;

the system will always add a "sqlite://" url to this list regardless

6. i've captured very raw and scratch notes for the whole thing at https://gist.github.com/zzzeek/001ec0e32cefe2d4e1a1. I'm going to try prototyping and/or blueprinting on this

[Mike Bayer (zzzeek)]

also, within oslo.db and any project using the oslo.db "way", this system is it for testing on a database - we take out the stuff from DbFixture, 'OS_TEST_DBAPI_CONNECTION' whatever (if we need that for backwards compat, provisioning can make use of it). All the DB tests will extend from TransactionalTestBase or SchemaTestBase. Tests that only need sqlite will just establish "sqlite" as the "allowed scenario".

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: master
Review: https://review.openstack.org/110486

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: master
Review: https://review.openstack.org/113153

[Mike Bayer (zzzeek)]

most of what this bug called for is now merged; issues #2, #3, and #4 are solved. #1 is only partially solved in that a suite can define per-backends suites, but there's not yet the ability to run a single suite against any arbitrary backend not otherwise specified.