set up tests to run with sqlite

For most of the tests for storyboard sqlite is around 10 times faster
than MySQL (more for non-SSD systems). An sqlite database does not
support some operations, like modifying constraints or dropping
columns, so we cannot avoid testing with MySQL. We can however use
sqlite for local development to reduce the pain involved with running
tests as part of the development process.

This patch adds a tox environment for running the tests against
sqlite3. The new tox environment is intended to be used by developers
as well as the new check and gate job defined in .zuul.yaml.

The new job ensures that changes to alembic migration scripts continue
to work with sqlite.

This patch also modifies the existing alter scripts to skip steps not
supported under sqlite. Those steps aren't strictly needed, and they
are still tested when the CI system runs the tests with MySQL.

Change-Id: Icb979cb03e10c56519a90ea3976a4da2d9bddb05
Signed-off-by: Doug Hellmann <doug@doughellmann.com>

.zuul.yaml

@@ -0,0 +1,16 @@
+- job:
+    name: storyboard-tox-sqlite
+    parent: openstack-tox
+    description: |
+      Run tests using sqlite instead of mysql.
+    vars:
+      tox_envlist: sqlite
+
+- project:
+    name: openstack-infra/storyboard
+    check:
+      jobs:
+        - storyboard-tox-sqlite
+    gate:
+      jobs:
+        - storyboard-tox-sqlite

doc/source/contributing.rst

@@ -30,6 +30,11 @@ or for Python 3::
 
   $ tox -e py35
 
+For faster versions of the integration tests using only Python 3,
+run::
+
+  $ tox -e sqlite
+
 And to run the style-checker and static analysis tool::
 
   $ tox -e pep8

storyboard/db/migration/alembic_migrations/versions/050_add_detailed_permissions_to_boards_and_.py

@@ -82,10 +82,13 @@ def upgrade(active_plugins=None, options=None):
             worklist.id, move_permission, session=session)
         session.flush()
 
-    op.drop_constraint(u'boards_ibfk_2', 'boards', type_='foreignkey')
-    op.drop_column(u'boards', 'permission_id')
-    op.drop_constraint(u'worklists_ibfk_2', 'worklists', type_='foreignkey')
-    op.drop_column(u'worklists', 'permission_id')
+    dialect = op.get_bind().engine.dialect
+    if dialect.supports_alter:
+        op.drop_constraint(u'boards_ibfk_2', 'boards', type_='foreignkey')
+        op.drop_column(u'boards', 'permission_id')
+        op.drop_constraint(u'worklists_ibfk_2', 'worklists',
+                           type_='foreignkey')
+        op.drop_column(u'worklists', 'permission_id')
 
 
 def downgrade(active_plugins=None, options=None):

storyboard/db/migration/alembic_migrations/versions/053_add_due_dates_for_tasks_and_stories.py

@@ -82,8 +82,10 @@ def upgrade(active_plugins=None, options=None):
         'worklist_items',
         sa.Column('display_due_date', sa.Integer(), nullable=True)
     )
-    op.create_foreign_key(
-        None, 'worklist_items', 'due_dates', ['display_due_date'], ['id'])
+    dialect = op.get_bind().engine.dialect
+    if dialect.supports_alter:
+        op.create_foreign_key(
+            None, 'worklist_items', 'due_dates', ['display_due_date'], ['id'])
 
 
 def downgrade(active_plugins=None, options=None):

storyboard/db/migration/alembic_migrations/versions/055_allow_comments_to_be_replies_to_other_.py

@@ -31,8 +31,10 @@ import sqlalchemy as sa
 def upgrade(active_plugins=None, options=None):
     op.add_column(
         'comments', sa.Column('in_reply_to', sa.Integer(), nullable=True))
-    op.create_foreign_key(
-        'comments_ibfk_1', 'comments', 'comments', ['in_reply_to'], ['id'])
+    dialect = op.get_bind().engine.dialect
+    if dialect.supports_alter:
+        op.create_foreign_key(
+            'comments_ibfk_1', 'comments', 'comments', ['in_reply_to'], ['id'])
 
 
 def downgrade(active_plugins=None, options=None):

storyboard/db/migration/alembic_migrations/versions/057_allow_stories_and_tasks_to_be_made_.py

@@ -46,12 +46,16 @@ def upgrade(active_plugins=None, options=None):
         sa.ForeignKeyConstraint(['permission_id'], ['permissions.id'], ),
         sa.ForeignKeyConstraint(['story_id'], ['stories.id'], )
     )
-    op.add_column(
-        u'stories',
-        sa.Column('private', sa.Boolean(), default=False, nullable=False))
-    op.alter_column('worklist_items', 'list_id',
-               existing_type=mysql.INTEGER(display_width=11),
-               nullable=True)
+    dialect = op.get_bind().engine.dialect
+    if dialect.name == 'sqlite':
+        col = sa.Column('private', sa.Boolean(), default=False)
+    else:
+        col = sa.Column('private', sa.Boolean(), default=False, nullable=False)
+    op.add_column(u'stories', col)
+    if dialect.supports_alter:
+        op.alter_column('worklist_items', 'list_id',
+                        existing_type=mysql.INTEGER(display_width=11),
+                        nullable=True)
 
 
 def downgrade(active_plugins=None, options=None):

storyboard/db/migration/alembic_migrations/versions/058_allow_subscription_to_worklists.py

@@ -33,10 +33,12 @@ new_type_enum = sa.Enum(
 
 
 def upgrade(active_plugins=None, options=None):
-    op.alter_column('subscriptions',
-                    'target_type',
-                    existing_type=old_type_enum,
-                    type_=new_type_enum)
+    dialect = op.get_bind().engine.dialect
+    if dialect.supports_alter:
+        op.alter_column('subscriptions',
+                        'target_type',
+                        existing_type=old_type_enum,
+                        type_=new_type_enum)
 
 
 def downgrade(active_plugins=None, options=None):

storyboard/db/migration/alembic_migrations/versions/060_allow_timeline_events_to_be_related_to_.py

@@ -33,10 +33,13 @@ def upgrade(active_plugins=None, options=None):
         'events', sa.Column('board_id', sa.Integer(), nullable=True))
     op.add_column(
         'events', sa.Column('worklist_id', sa.Integer(), nullable=True))
-    op.create_foreign_key(
-        'fk_event_worklist', 'events', 'worklists', ['worklist_id'], ['id'])
-    op.create_foreign_key(
-        'fk_event_board', 'events', 'boards', ['board_id'], ['id'])
+    dialect = op.get_bind().engine.dialect
+    if dialect.supports_alter:
+        op.create_foreign_key(
+            'fk_event_worklist', 'events', 'worklists',
+            ['worklist_id'], ['id'])
+        op.create_foreign_key(
+            'fk_event_board', 'events', 'boards', ['board_id'], ['id'])
 
 
 def downgrade(active_plugins=None, options=None):

storyboard/db/migration/alembic_migrations/versions/061_extends_project_name_and_project_group_.py

@@ -29,9 +29,10 @@ import sqlalchemy as sa
 
 
 def upgrade(active_plugins=None, options=None):
-
-    op.alter_column('project_groups', 'name', type_=sa.Unicode(100))
-    op.alter_column('projects', 'name', type_=sa.Unicode(100))
+    dialect = op.get_bind().engine.dialect
+    if dialect.supports_alter:
+        op.alter_column('project_groups', 'name', type_=sa.Unicode(100))
+        op.alter_column('projects', 'name', type_=sa.Unicode(100))
 
 
 def downgrade(active_plugins=None, options=None):

storyboard/tests/base.py

@@ -16,6 +16,7 @@
 # under the License.
 
 import os
+import os.path
 import shutil
 import stat
 import uuid
@@ -142,34 +143,52 @@ class DbTestCase(WorkingDirTestCase):
         self.setup_db()
 
     def setup_db(self):
+
         self.db_name = "storyboard_test_db_%s" % uuid.uuid4()
         self.db_name = self.db_name.replace("-", "_")
-        LOG.info('creating database %s', self.db_name)
-
-        # The engine w/o db name
-        engine = sqlalchemy.create_engine(
-            self.test_connection)
-        engine.execute("CREATE DATABASE %s" % self.db_name)
-
-        alembic_config = get_alembic_config()
-        alembic_config.storyboard_config = CONF
         CONF.set_override(
             "connection",
             self.test_connection + "/%s"
             % self.db_name,
             group="database")
+        self._full_db_name = self.test_connection + '/' + self.db_name
+        LOG.info('using database %s', CONF.database.connection)
+
+        if self.test_connection.startswith('sqlite://'):
+            self.using_sqlite = True
+        else:
+            self.using_sqlite = False
+            # The engine w/o db name
+            engine = sqlalchemy.create_engine(
+                self.test_connection)
+            engine.execute("CREATE DATABASE %s" % self.db_name)
+
+        alembic_config = get_alembic_config()
+        alembic_config.storyboard_config = CONF
 
         command.upgrade(alembic_config, "head")
         self.addCleanup(self._drop_db)
 
     def _drop_db(self):
-        engine = sqlalchemy.create_engine(
-            self.test_connection)
-        try:
-            engine.execute("DROP DATABASE %s" % self.db_name)
-        except Exception as err:
-            LOG.error('failed to drop database %s: %s',
-                      self.db_name, err)
+        if self.test_connection.startswith('sqlite://'):
+            filename = self._full_db_name[9:]
+            if filename[:2] == '//':
+                filename = filename[1:]
+            if os.path.exists(filename):
+                LOG.info('removing database file %s', filename)
+                try:
+                    os.unlink(filename)
+                except OSError as err:
+                    LOG.error('could not remove %s: %s',
+                              filename, err)
+        else:
+            engine = sqlalchemy.create_engine(
+                self.test_connection)
+            try:
+                engine.execute("DROP DATABASE %s" % self.db_name)
+            except Exception as err:
+                LOG.error('failed to drop database %s: %s',
+                          self.db_name, err)
         db_api_base.cleanup()
 
 PATH_PREFIX = '/v1'

storyboard/tests/db/test_db_exceptions.py

@@ -49,8 +49,26 @@ class TestDBReferenceError(base.BaseDbTestCase):
             'story_id': 100
         }
 
-        self.assertRaises(exc.DBReferenceError,
-                          lambda: tasks.task_create(task))
+        # TODO(dhellmann): The SQLite database doesn't use foreign key
+        # constraints instead of getting an error from the database
+        # when we try to insert the task we get the error later when
+        # we try to update the story. The behavior difference doesn't
+        # seem all that important since it only affects this test, but
+        # at some point we should probably ensure that all database
+        # reference errors are turned into the same exception class
+        # for consistency. For now we just test slightly differently.
+        if self.using_sqlite:
+            self.assertRaises(
+                exc.NotFound,
+                tasks.task_create,
+                task,
+            )
+        else:
+            self.assertRaises(
+                exc.DBReferenceError,
+                tasks.task_create,
+                task,
+            )
 
 
 class TestDbInvalidSortKey(base.BaseDbTestCase):

tox.ini

@@ -1,5 +1,5 @@
 [tox]
-minversion = 1.6
+minversion = 2.9.1
 skipsdist = True
 envlist = py34,py27,pep8
 
@@ -10,12 +10,21 @@ setenv =
     VIRTUAL_ENV={envdir}
     OS_STDERR_CAPTURE=1
     OS_STDOUT_CAPTURE=1
-passenv = OS_TEST_TIMEOUT
+passenv =
+    OS_TEST_TIMEOUT
+    STORYBOARD_TEST_DB
 deps = -r{toxinidir}/requirements.txt
        -r{toxinidir}/test-requirements.txt
 commands = ostestr '{posargs}'
 whitelist_externals = bash
 
+[testenv:sqlite]
+basepython = python3
+setenv =
+    STORYBOARD_TEST_DB=sqlite:///{envtmpdir}
+    OS_STDERR_CAPTURE=1
+    OS_STDOUT_CAPTURE=1
+
 [testenv:pep8]
 commands = flake8