Support table prefix for sql reporter

In some environments zuul operators may have to rely on external
database providers. In this case it can be cumbersome to get extra
databases for each test environment. Adding an optional prefix to the
table names makes it possible to gracefully run several zuul
deployments against the same database and ensure they're still
isolated against each other.

Change-Id: Ib9948d6d74f4dc2453738f5d441e233e39e7f944

12 files changed, 102 insertions, 34 deletions

diff --git a/doc/source/admin/drivers/sql.rst b/doc/source/admin/drivers/sql.rst
index a269f5d2e..b9ce24bc9 100644
--- a/doc/source/admin/drivers/sql.rst
+++ b/doc/source/admin/drivers/sql.rst
@@ -43,6 +43,14 @@ The connection options for the SQL driver are:
       <http://docs.sqlalchemy.org/en/latest/core/pooling.html#setting-pool-recycle>`_
       for more information.
 
+   .. attr:: table_prefix
+      :default: ''
+
+      The string to prefix the table names. This makes it possible to run
+      several zuul deployments against the same database. This can be useful
+      if you rely on external databases which you don't have under control.
+      The default is to have no prefix.
+
 Reporter Configuration
 ----------------------
 
diff --git a/tests/fixtures/zuul-sql-driver-prefix.conf b/tests/fixtures/zuul-sql-driver-prefix.conf
new file mode 100644
index 000000000..14064745e
--- /dev/null
+++ b/tests/fixtures/zuul-sql-driver-prefix.conf
@@ -0,0 +1,28 @@
+[gearman]
+server=127.0.0.1
+
+[scheduler]
+tenant_config=main.yaml
+
+[merger]
+git_dir=/tmp/zuul-test/merger-git
+git_user_email=zuul@example.com
+git_user_name=zuul
+
+[executor]
+git_dir=/tmp/zuul-test/executor-git
+
+[connection gerrit]
+driver=gerrit
+server=review.example.com
+user=jenkins
+sshkey=fake_id_rsa1
+
+[connection resultsdb]
+driver=sql
+dburi=$MYSQL_FIXTURE_DBURI$
+table_prefix=prefix_
+
+[connection resultsdb_failures]
+driver=sql
+dburi=$MYSQL_FIXTURE_DBURI$
diff --git a/tests/unit/test_connection.py b/tests/unit/test_connection.py
index c882d3a0a..054ee5f04 100644
--- a/tests/unit/test_connection.py
+++ b/tests/unit/test_connection.py
@@ -60,14 +60,19 @@ class TestConnections(ZuulTestCase):
 class TestSQLConnection(ZuulDBTestCase):
     config_file = 'zuul-sql-driver.conf'
     tenant_config_file = 'config/sql-driver/main.yaml'
+    expected_table_prefix = ''
 
-    def test_sql_tables_created(self, metadata_table=None):
+    def test_sql_tables_created(self):
         "Test the tables for storing results are created properly"
-        buildset_table = 'zuul_buildset'
-        build_table = 'zuul_build'
 
-        insp = sa.engine.reflection.Inspector(
-            self.connections.connections['resultsdb'].engine)
+        connection = self.connections.connections['resultsdb']
+        insp = sa.engine.reflection.Inspector(connection.engine)
+
+        table_prefix = connection.table_prefix
+        self.assertEqual(self.expected_table_prefix, table_prefix)
+
+        buildset_table = table_prefix + 'zuul_buildset'
+        build_table = table_prefix + 'zuul_build'
 
         self.assertEqual(13, len(insp.get_columns(buildset_table)))
         self.assertEqual(10, len(insp.get_columns(build_table)))
@@ -216,6 +221,11 @@ class TestSQLConnection(ZuulDBTestCase):
             'Build failed.', buildsets_resultsdb_failures[0]['message'])
 
 
+class TestSQLConnectionPrefix(TestSQLConnection):
+    config_file = 'zuul-sql-driver-prefix.conf'
+    expected_table_prefix = 'prefix_'
+
+
 class TestConnectionsBadSQL(ZuulDBTestCase):
     config_file = 'zuul-sql-driver-bad.conf'
     tenant_config_file = 'config/sql-driver/main.yaml'
diff --git a/zuul/driver/sql/alembic/env.py b/zuul/driver/sql/alembic/env.py
index 4542a2227..8cf2ecf2b 100644
--- a/zuul/driver/sql/alembic/env.py
+++ b/zuul/driver/sql/alembic/env.py
@@ -55,6 +55,13 @@ def run_migrations_online():
         prefix='sqlalchemy.',
         poolclass=pool.NullPool)
 
+    # we can get the table prefix via the tag object
+    tag = context.get_tag_argument()
+    if tag and isinstance(tag, dict):
+        table_prefix = tag.get('table_prefix', '')
+    else:
+        table_prefix = ''
+
     with connectable.connect() as connection:
         context.configure(
             connection=connection,
@@ -62,7 +69,7 @@ def run_migrations_online():
         )
 
         with context.begin_transaction():
-            context.run_migrations()
+            context.run_migrations(table_prefix=table_prefix)
 
 
 if context.is_offline_mode():
diff --git a/zuul/driver/sql/alembic/versions/1dd914d4a482_allow_score_to_be_null.py b/zuul/driver/sql/alembic/versions/1dd914d4a482_allow_score_to_be_null.py
index b153cabf7..f42c2f397 100644
--- a/zuul/driver/sql/alembic/versions/1dd914d4a482_allow_score_to_be_null.py
+++ b/zuul/driver/sql/alembic/versions/1dd914d4a482_allow_score_to_be_null.py
@@ -16,8 +16,8 @@ from alembic import op
 import sqlalchemy as sa
 
 
-def upgrade():
-    op.alter_column('zuul_buildset', 'score', nullable=True,
+def upgrade(table_prefix=''):
+    op.alter_column(table_prefix + 'zuul_buildset', 'score', nullable=True,
                     existing_type=sa.Integer)
 
 
diff --git a/zuul/driver/sql/alembic/versions/20126015a87d_add_indexes.py b/zuul/driver/sql/alembic/versions/20126015a87d_add_indexes.py
index 12e7c094a..906df2131 100644
--- a/zuul/driver/sql/alembic/versions/20126015a87d_add_indexes.py
+++ b/zuul/driver/sql/alembic/versions/20126015a87d_add_indexes.py
@@ -32,24 +32,28 @@ BUILDSET_TABLE = 'zuul_buildset'
 BUILD_TABLE = 'zuul_build'
 
 
-def upgrade():
+def upgrade(table_prefix=''):
+    prefixed_buildset = table_prefix + BUILDSET_TABLE
+    prefixed_build = table_prefix + BUILD_TABLE
+
     # To allow a dashboard to show a per-project view, optionally filtered
     # by pipeline.
     op.create_index(
-        'project_pipeline_idx', BUILDSET_TABLE, ['project', 'pipeline'])
+        'project_pipeline_idx', prefixed_buildset, ['project', 'pipeline'])
 
     # To allow a dashboard to show a per-project-change view
     op.create_index(
-        'project_change_idx', BUILDSET_TABLE, ['project', 'change'])
+        'project_change_idx', prefixed_buildset, ['project', 'change'])
 
     # To allow a dashboard to show a per-change view
-    op.create_index('change_idx', BUILDSET_TABLE, ['change'])
+    op.create_index('change_idx', prefixed_buildset, ['change'])
 
     # To allow a dashboard to show a job lib view. buildset_id is included
     # so that it's a covering index and can satisfy the join back to buildset
     # without an additional lookup.
     op.create_index(
-        'job_name_buildset_id_idx', BUILD_TABLE, ['job_name', 'buildset_id'])
+        'job_name_buildset_id_idx', prefixed_build,
+        ['job_name', 'buildset_id'])
 
 
 def downgrade():
diff --git a/zuul/driver/sql/alembic/versions/4d3ebd7f06b9_set_up_initial_reporter_tables.py b/zuul/driver/sql/alembic/versions/4d3ebd7f06b9_set_up_initial_reporter_tables.py
index 783196f06..b78f8305c 100644
--- a/zuul/driver/sql/alembic/versions/4d3ebd7f06b9_set_up_initial_reporter_tables.py
+++ b/zuul/driver/sql/alembic/versions/4d3ebd7f06b9_set_up_initial_reporter_tables.py
@@ -19,9 +19,9 @@ BUILDSET_TABLE = 'zuul_buildset'
 BUILD_TABLE = 'zuul_build'
 
 
-def upgrade():
+def upgrade(table_prefix=''):
     op.create_table(
-        BUILDSET_TABLE,
+        table_prefix + BUILDSET_TABLE,
         sa.Column('id', sa.Integer, primary_key=True),
         sa.Column('zuul_ref', sa.String(255)),
         sa.Column('pipeline', sa.String(255)),
@@ -34,10 +34,10 @@ def upgrade():
     )
 
     op.create_table(
-        BUILD_TABLE,
+        table_prefix + BUILD_TABLE,
         sa.Column('id', sa.Integer, primary_key=True),
         sa.Column('buildset_id', sa.Integer,
-                  sa.ForeignKey(BUILDSET_TABLE + ".id")),
+                  sa.ForeignKey(table_prefix + BUILDSET_TABLE + ".id")),
         sa.Column('uuid', sa.String(36)),
         sa.Column('job_name', sa.String(255)),
         sa.Column('result', sa.String(255)),
diff --git a/zuul/driver/sql/alembic/versions/5efb477fa963_add_ref_url_column.py b/zuul/driver/sql/alembic/versions/5efb477fa963_add_ref_url_column.py
index f9c353519..5502425a5 100644
--- a/zuul/driver/sql/alembic/versions/5efb477fa963_add_ref_url_column.py
+++ b/zuul/driver/sql/alembic/versions/5efb477fa963_add_ref_url_column.py
@@ -30,8 +30,9 @@ from alembic import op
 import sqlalchemy as sa
 
 
-def upgrade():
-    op.add_column('zuul_buildset', sa.Column('ref_url', sa.String(255)))
+def upgrade(table_prefix=''):
+    op.add_column(
+        table_prefix + 'zuul_buildset', sa.Column('ref_url', sa.String(255)))
 
 
 def downgrade():
diff --git a/zuul/driver/sql/alembic/versions/60c119eb1e3f_use_build_set_results.py b/zuul/driver/sql/alembic/versions/60c119eb1e3f_use_build_set_results.py
index 985eb0c39..67581a6f9 100644
--- a/zuul/driver/sql/alembic/versions/60c119eb1e3f_use_build_set_results.py
+++ b/zuul/driver/sql/alembic/versions/60c119eb1e3f_use_build_set_results.py
@@ -18,8 +18,9 @@ import sqlalchemy as sa
 BUILDSET_TABLE = 'zuul_buildset'
 
 
-def upgrade():
-    op.add_column(BUILDSET_TABLE, sa.Column('result', sa.String(255)))
+def upgrade(table_prefix=''):
+    op.add_column(
+        table_prefix + BUILDSET_TABLE, sa.Column('result', sa.String(255)))
 
     connection = op.get_bind()
     connection.execute(
@@ -29,9 +30,9 @@ def upgrade():
              SELECT CASE score
                 WHEN 1 THEN 'SUCCESS'
                 ELSE 'FAILURE' END)
-        """.format(buildset_table=BUILDSET_TABLE))
+        """.format(buildset_table=table_prefix + BUILDSET_TABLE))
 
-    op.drop_column(BUILDSET_TABLE, 'score')
+    op.drop_column(table_prefix + BUILDSET_TABLE, 'score')
 
 
 def downgrade():
diff --git a/zuul/driver/sql/alembic/versions/ba4cdce9b18c_add_rev_columns.py b/zuul/driver/sql/alembic/versions/ba4cdce9b18c_add_rev_columns.py
index dc75983a9..3e60866e0 100644
--- a/zuul/driver/sql/alembic/versions/ba4cdce9b18c_add_rev_columns.py
+++ b/zuul/driver/sql/alembic/versions/ba4cdce9b18c_add_rev_columns.py
@@ -16,9 +16,11 @@ from alembic import op
 import sqlalchemy as sa
 
 
-def upgrade():
-    op.add_column('zuul_buildset', sa.Column('oldrev', sa.String(255)))
-    op.add_column('zuul_buildset', sa.Column('newrev', sa.String(255)))
+def upgrade(table_prefix=''):
+    op.add_column(
+        table_prefix + 'zuul_buildset', sa.Column('oldrev', sa.String(255)))
+    op.add_column(
+        table_prefix + 'zuul_buildset', sa.Column('newrev', sa.String(255)))
 
 
 def downgrade():
diff --git a/zuul/driver/sql/alembic/versions/f86c9871ee67_add_tenant_column.py b/zuul/driver/sql/alembic/versions/f86c9871ee67_add_tenant_column.py
index 4087af368..84fd0efd4 100644
--- a/zuul/driver/sql/alembic/versions/f86c9871ee67_add_tenant_column.py
+++ b/zuul/driver/sql/alembic/versions/f86c9871ee67_add_tenant_column.py
@@ -30,8 +30,9 @@ from alembic import op
 import sqlalchemy as sa
 
 
-def upgrade():
-    op.add_column('zuul_buildset', sa.Column('tenant', sa.String(255)))
+def upgrade(table_prefix=''):
+    op.add_column(
+        table_prefix + 'zuul_buildset', sa.Column('tenant', sa.String(255)))
 
 
 def downgrade():
diff --git a/zuul/driver/sql/sqlconnection.py b/zuul/driver/sql/sqlconnection.py
index b964c0be3..413c9e636 100644
--- a/zuul/driver/sql/sqlconnection.py
+++ b/zuul/driver/sql/sqlconnection.py
@@ -15,6 +15,7 @@
 import logging
 
 import alembic
+import alembic.command
 import alembic.config
 import sqlalchemy as sa
 import sqlalchemy.pool
@@ -39,6 +40,8 @@ class SQLConnection(BaseConnection):
         self.engine = None
         self.connection = None
         self.tables_established = False
+        self.table_prefix = self.connection_config.get('table_prefix', '')
+
         try:
             self.dburi = self.connection_config.get('dburi')
             # Recycle connections if they've been idle for more than 1 second.
@@ -75,14 +78,16 @@ class SQLConnection(BaseConnection):
             config.set_main_option("sqlalchemy.url",
                                    self.connection_config.get('dburi'))
 
-            alembic.command.upgrade(config, 'head')
+            # Alembic lets us add arbitrary data in the tag argument. We can
+            # leverage that to tell the upgrade scripts about the table prefix.
+            tag = {'table_prefix': self.table_prefix}
+            alembic.command.upgrade(config, 'head', tag=tag)
 
-    @staticmethod
-    def _setup_tables():
+    def _setup_tables(self):
         metadata = sa.MetaData()
 
         zuul_buildset_table = sa.Table(
-            BUILDSET_TABLE, metadata,
+            self.table_prefix + BUILDSET_TABLE, metadata,
             sa.Column('id', sa.Integer, primary_key=True),
             sa.Column('zuul_ref', sa.String(255)),
             sa.Column('pipeline', sa.String(255)),
@@ -99,10 +104,11 @@ class SQLConnection(BaseConnection):
         )
 
         zuul_build_table = sa.Table(
-            BUILD_TABLE, metadata,
+            self.table_prefix + BUILD_TABLE, metadata,
             sa.Column('id', sa.Integer, primary_key=True),
             sa.Column('buildset_id', sa.Integer,
-                      sa.ForeignKey(BUILDSET_TABLE + ".id")),
+                      sa.ForeignKey(self.table_prefix +
+                                    BUILDSET_TABLE + ".id")),
             sa.Column('uuid', sa.String(36)),
             sa.Column('job_name', sa.String(255)),
             sa.Column('result', sa.String(255)),