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
diff --git a/doc/source/admin/drivers/sql.rst b/doc/source/admin/drivers/sql.rst
index a269f5d..b9ce24b 100644
--- a/doc/source/admin/drivers/sql.rst
+++ b/doc/source/admin/drivers/sql.rst
@@ -43,6 +43,14 @@
<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 0000000..1406474
--- /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 c882d3a..054ee5f 100644
--- a/tests/unit/test_connection.py
+++ b/tests/unit/test_connection.py
@@ -60,14 +60,19 @@
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 @@
'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 4542a22..8cf2ecf 100644
--- a/zuul/driver/sql/alembic/env.py
+++ b/zuul/driver/sql/alembic/env.py
@@ -55,6 +55,13 @@
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 @@
)
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 b153cab..f42c2f3 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 @@
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 12e7c09..906df21 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 @@
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 783196f..b78f830 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 @@
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 @@
)
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 f9c3535..5502425 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 @@
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 985eb0c..67581a6 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 @@
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 @@
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 dc75983..3e60866 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 @@
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 4087af3..84fd0ef 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 @@
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 b964c0b..413c9e6 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 @@
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 @@
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 @@
)
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)),