Merge "Add support for sqlalchemy reporter"

6 files changed, 369 insertions, 12 deletions

diff --git a/tests/base.py b/tests/base.py
index 2559eb444..9dc412b5f 100755
--- a/tests/base.py
+++ b/tests/base.py
@@ -34,16 +34,20 @@ import subprocess
 import swiftclient
 import threading
 import time
+import uuid
+
 
 import git
 import gear
 import fixtures
+import pymysql
 import statsd
 import testtools
 from git import GitCommandError
 
 import zuul.connection.gerrit
 import zuul.connection.smtp
+import zuul.connection.sql
 import zuul.scheduler
 import zuul.webapp
 import zuul.rpclistener
@@ -855,6 +859,43 @@ class FakeSwiftClientConnection(swiftclient.client.Connection):
         return endpoint, ''
 
 
+class MySQLSchemaFixture(fixtures.Fixture):
+    def setUp(self):
+        super(MySQLSchemaFixture, self).setUp()
+
+        random_bits = ''.join(random.choice(string.ascii_lowercase +
+                                            string.ascii_uppercase)
+                              for x in range(8))
+        self.name = '%s_%s' % (random_bits, os.getpid())
+        self.passwd = uuid.uuid4().hex
+        db = pymysql.connect(host="localhost",
+                             user="openstack_citest",
+                             passwd="openstack_citest",
+                             db="openstack_citest")
+        cur = db.cursor()
+        cur.execute("create database %s" % self.name)
+        cur.execute(
+            "grant all on %s.* to '%s'@'localhost' identified by '%s'" %
+            (self.name, self.name, self.passwd))
+        cur.execute("flush privileges")
+
+        self.dburi = 'mysql+pymysql://%s:%s@localhost/%s' % (self.name,
+                                                             self.passwd,
+                                                             self.name)
+        self.addDetail('dburi', testtools.content.text_content(self.dburi))
+        self.addCleanup(self.cleanup)
+
+    def cleanup(self):
+        db = pymysql.connect(host="localhost",
+                             user="openstack_citest",
+                             passwd="openstack_citest",
+                             db="openstack_citest")
+        cur = db.cursor()
+        cur.execute("drop database %s" % self.name)
+        cur.execute("drop user '%s'@'localhost'" % self.name)
+        cur.execute("flush privileges")
+
+
 class BaseTestCase(testtools.TestCase):
     log = logging.getLogger("zuul.test")
 
@@ -1039,6 +1080,8 @@ class ZuulTestCase(BaseTestCase):
         self.addCleanup(self.shutdown)
 
     def configure_connections(self):
+        # TODO(jhesketh): This should come from lib.connections for better
+        # coverage
         # Register connections from the config
         self.smtp_messages = []
 
@@ -1087,6 +1130,9 @@ class ZuulTestCase(BaseTestCase):
             elif con_driver == 'smtp':
                 self.connections[con_name] = \
                     zuul.connection.smtp.SMTPConnection(con_name, con_config)
+            elif con_driver == 'sql':
+                self.connections[con_name] = \
+                    zuul.connection.sql.SQLConnection(con_name, con_config)
             else:
                 raise Exception("Unknown driver, %s, for connection %s"
                                 % (con_config['driver'], con_name))
@@ -1429,3 +1475,20 @@ class ZuulTestCase(BaseTestCase):
 
         pprint.pprint(self.statsd.stats)
         raise Exception("Key %s not found in reported stats" % key)
+
+
+class ZuulDBTestCase(ZuulTestCase):
+    def setup_config(self, config_file='zuul-connections-same-gerrit.conf'):
+        super(ZuulDBTestCase, self).setup_config(config_file)
+        for section_name in self.config.sections():
+            con_match = re.match(r'^connection ([\'\"]?)(.*)(\1)$',
+                                 section_name, re.I)
+            if not con_match:
+                continue
+
+            if self.config.get(section_name, 'driver') == 'sql':
+                f = MySQLSchemaFixture()
+                self.useFixture(f)
+                if (self.config.get(section_name, 'dburi') ==
+                    '$MYSQL_FIXTURE_DBURI$'):
+                    self.config.set(section_name, 'dburi', f.dburi)
diff --git a/tests/fixtures/layout-sql-reporter.yaml b/tests/fixtures/layout-sql-reporter.yaml
new file mode 100644
index 000000000..c79a4328d
--- /dev/null
+++ b/tests/fixtures/layout-sql-reporter.yaml
@@ -0,0 +1,27 @@
+pipelines:
+  - name: check
+    manager: IndependentPipelineManager
+    source:
+        review_gerrit
+    trigger:
+      review_gerrit:
+        - event: patchset-created
+    success:
+      review_gerrit:
+        verified: 1
+      resultsdb:
+        score: 1
+    failure:
+      review_gerrit:
+        verified: -1
+      resultsdb:
+        score: -1
+      resultsdb_failures:
+        score: -1
+
+projects:
+  - name: org/project
+    check:
+      - project-merge:
+        - project-test1
+        - project-test2
diff --git a/tests/fixtures/zuul-connections-bad-sql.conf b/tests/fixtures/zuul-connections-bad-sql.conf
new file mode 100644
index 000000000..150643d0f
--- /dev/null
+++ b/tests/fixtures/zuul-connections-bad-sql.conf
@@ -0,0 +1,50 @@
+[gearman]
+server=127.0.0.1
+
+[zuul]
+layout_config=layout-connections-multiple-voters.yaml
+url_pattern=http://logs.example.com/{change.number}/{change.patchset}/{pipeline.name}/{job.name}/{build.number}
+job_name_in_report=true
+
+[merger]
+git_dir=/tmp/zuul-test/git
+git_user_email=zuul@example.com
+git_user_name=zuul
+zuul_url=http://zuul.example.com/p
+
+[swift]
+authurl=https://identity.api.example.org/v2.0/
+user=username
+key=password
+tenant_name=" "
+
+default_container=logs
+region_name=EXP
+logserver_prefix=http://logs.example.org/server.app/
+
+[connection review_gerrit]
+driver=gerrit
+server=review.example.com
+user=jenkins
+sshkey=none
+
+[connection alt_voting_gerrit]
+driver=gerrit
+server=alt_review.example.com
+user=civoter
+sshkey=none
+
+[connection outgoing_smtp]
+driver=smtp
+server=localhost
+port=25
+default_from=zuul@example.com
+default_to=you@example.com
+
+[connection resultsdb]
+driver=sql
+dburi=mysql+pymysql://bad:creds@host/db
+
+[connection resultsdb_failures]
+driver=sql
+dburi=mysql+pymysql://bad:creds@host/db
diff --git a/tests/fixtures/zuul-connections-same-gerrit.conf b/tests/fixtures/zuul-connections-same-gerrit.conf
index b3b0e3f92..2609d3091 100644
--- a/tests/fixtures/zuul-connections-same-gerrit.conf
+++ b/tests/fixtures/zuul-connections-same-gerrit.conf
@@ -40,3 +40,11 @@ server=localhost
 port=25
 default_from=zuul@example.com
 default_to=you@example.com
+
+[connection resultsdb]
+driver=sql
+dburi=$MYSQL_FIXTURE_DBURI$
+
+[connection resultsdb_failures]
+driver=sql
+dburi=$MYSQL_FIXTURE_DBURI$
diff --git a/tests/test_connection.py b/tests/test_connection.py
index c3458ac46..f9f54f389 100644
--- a/tests/test_connection.py
+++ b/tests/test_connection.py
@@ -15,9 +15,21 @@
 import logging
 import testtools
 
+import sqlalchemy as sa
+
 import zuul.connection.gerrit
+import zuul.connection.sql
+
+from tests.base import ZuulTestCase, ZuulDBTestCase
 
-from tests.base import ZuulTestCase
+
+def _get_reporter_from_connection_name(reporters, connection_name):
+    # Reporters are placed into lists for each action they may exist in.
+    # Search through the given list for the correct reporter by its conncetion
+    # name
+    for r in reporters:
+        if r.connection.connection_name == connection_name:
+            return r
 
 
 class TestGerritConnection(testtools.TestCase):
@@ -28,11 +40,18 @@ class TestGerritConnection(testtools.TestCase):
                          zuul.connection.gerrit.GerritConnection.driver_name)
 
 
-class TestConnections(ZuulTestCase):
-    def setup_config(self, config_file='zuul-connections-same-gerrit.conf'):
-        super(TestConnections, self).setup_config(config_file)
+class TestSQLConnection(testtools.TestCase):
+    log = logging.getLogger("zuul.test_connection")
+
+    def test_driver_name(self):
+        self.assertEqual(
+            'sql',
+            zuul.connection.sql.SQLConnection.driver_name
+        )
+
 
-    def test_multiple_connections(self):
+class TestConnections(ZuulDBTestCase):
+    def test_multiple_gerrit_connections(self):
         "Test multiple connections to the one gerrit"
 
         A = self.fake_review_gerrit.addFakeChange('org/project', 'master', 'A')
@@ -58,6 +77,178 @@ class TestConnections(ZuulTestCase):
         self.assertEqual(B.patchsets[-1]['approvals'][0]['by']['username'],
                          'civoter')
 
+    def _test_sql_tables_created(self, metadata_table=None):
+        "Test the tables for storing results are created properly"
+        buildset_table = 'zuul_buildset'
+        build_table = 'zuul_build'
+
+        insp = sa.engine.reflection.Inspector(
+            self.connections['resultsdb'].engine)
+
+        self.assertEqual(9, len(insp.get_columns(buildset_table)))
+        self.assertEqual(10, len(insp.get_columns(build_table)))
+
+    def test_sql_tables_created(self):
+        "Test the default table is created"
+        self.config.set('zuul', 'layout_config',
+                        'tests/fixtures/layout-sql-reporter.yaml')
+        self.sched.reconfigure(self.config)
+        self._test_sql_tables_created()
+
+    def _test_sql_results(self):
+        "Test results are entered into an sql table"
+        # Grab the sa tables
+        reporter = _get_reporter_from_connection_name(
+            self.sched.layout.pipelines['check'].success_actions,
+            'resultsdb'
+        )
+
+        # Add a success result
+        A = self.fake_review_gerrit.addFakeChange('org/project', 'master', 'A')
+        self.fake_review_gerrit.addEvent(A.getPatchsetCreatedEvent(1))
+        self.waitUntilSettled()
+
+        # Add a failed result for a negative score
+        B = self.fake_review_gerrit.addFakeChange('org/project', 'master', 'B')
+        self.worker.addFailTest('project-test1', B)
+        self.fake_review_gerrit.addEvent(B.getPatchsetCreatedEvent(1))
+        self.waitUntilSettled()
+
+        conn = self.connections['resultsdb'].engine.connect()
+        result = conn.execute(
+            sa.sql.select([reporter.connection.zuul_buildset_table]))
+
+        buildsets = result.fetchall()
+        self.assertEqual(2, len(buildsets))
+        buildset0 = buildsets[0]
+        buildset1 = buildsets[1]
+
+        self.assertEqual('check', buildset0['pipeline'])
+        self.assertEqual('org/project', buildset0['project'])
+        self.assertEqual(1, buildset0['change'])
+        self.assertEqual(1, buildset0['patchset'])
+        self.assertEqual(1, buildset0['score'])
+        self.assertEqual('Build succeeded.', buildset0['message'])
+
+        buildset0_builds = conn.execute(
+            sa.sql.select([reporter.connection.zuul_build_table]).
+            where(
+                reporter.connection.zuul_build_table.c.buildset_id ==
+                buildset0['id']
+            )
+        ).fetchall()
+
+        # Check the first result, which should be the project-merge job
+        self.assertEqual('project-merge', buildset0_builds[0]['job_name'])
+        self.assertEqual("SUCCESS", buildset0_builds[0]['result'])
+        self.assertEqual('http://logs.example.com/1/1/check/project-merge/0',
+                         buildset0_builds[0]['log_url'])
+
+        self.assertEqual('check', buildset1['pipeline'])
+        self.assertEqual('org/project', buildset1['project'])
+        self.assertEqual(2, buildset1['change'])
+        self.assertEqual(1, buildset1['patchset'])
+        self.assertEqual(-1, buildset1['score'])
+        self.assertEqual('Build failed.', buildset1['message'])
+
+        buildset1_builds = conn.execute(
+            sa.sql.select([reporter.connection.zuul_build_table]).
+            where(
+                reporter.connection.zuul_build_table.c.buildset_id ==
+                buildset1['id']
+            )
+        ).fetchall()
+
+        # Check the second last result, which should be the project-test1 job
+        # which failed
+        self.assertEqual('project-test1', buildset1_builds[-2]['job_name'])
+        self.assertEqual("FAILURE", buildset1_builds[-2]['result'])
+        self.assertEqual('http://logs.example.com/2/1/check/project-test1/4',
+                         buildset1_builds[-2]['log_url'])
+
+    def test_sql_results(self):
+        "Test results are entered into the default sql table"
+        self.config.set('zuul', 'layout_config',
+                        'tests/fixtures/layout-sql-reporter.yaml')
+        self.sched.reconfigure(self.config)
+        self._test_sql_results()
+
+    def test_multiple_sql_connections(self):
+        "Test putting results in different databases"
+        self.config.set('zuul', 'layout_config',
+                        'tests/fixtures/layout-sql-reporter.yaml')
+        self.sched.reconfigure(self.config)
+
+        # Add a successful result
+        A = self.fake_review_gerrit.addFakeChange('org/project', 'master', 'A')
+        self.fake_review_gerrit.addEvent(A.getPatchsetCreatedEvent(1))
+        self.waitUntilSettled()
+
+        # Add a failed result
+        B = self.fake_review_gerrit.addFakeChange('org/project', 'master', 'B')
+        self.worker.addFailTest('project-test1', B)
+        self.fake_review_gerrit.addEvent(B.getPatchsetCreatedEvent(1))
+        self.waitUntilSettled()
+
+        # Grab the sa tables for resultsdb
+        reporter1 = _get_reporter_from_connection_name(
+            self.sched.layout.pipelines['check'].success_actions,
+            'resultsdb'
+        )
+
+        conn = self.connections['resultsdb'].engine.connect()
+        buildsets_resultsdb = conn.execute(sa.sql.select(
+            [reporter1.connection.zuul_buildset_table])).fetchall()
+        # Should have been 2 buildset reported to the resultsdb (both success
+        # and failure report)
+        self.assertEqual(2, len(buildsets_resultsdb))
+
+        # The first one should have passed
+        self.assertEqual('check', buildsets_resultsdb[0]['pipeline'])
+        self.assertEqual('org/project', buildsets_resultsdb[0]['project'])
+        self.assertEqual(1, buildsets_resultsdb[0]['change'])
+        self.assertEqual(1, buildsets_resultsdb[0]['patchset'])
+        self.assertEqual(1, buildsets_resultsdb[0]['score'])
+        self.assertEqual('Build succeeded.', buildsets_resultsdb[0]['message'])
+
+        # Grab the sa tables for resultsdb_failures
+        reporter2 = _get_reporter_from_connection_name(
+            self.sched.layout.pipelines['check'].failure_actions,
+            'resultsdb_failures'
+        )
+
+        conn = self.connections['resultsdb_failures'].engine.connect()
+        buildsets_resultsdb_failures = conn.execute(sa.sql.select(
+            [reporter2.connection.zuul_buildset_table])).fetchall()
+        # The failure db should only have 1 buildset failed
+        self.assertEqual(1, len(buildsets_resultsdb_failures))
+
+        self.assertEqual('check', buildsets_resultsdb_failures[0]['pipeline'])
+        self.assertEqual(
+            'org/project', buildsets_resultsdb_failures[0]['project'])
+        self.assertEqual(2, buildsets_resultsdb_failures[0]['change'])
+        self.assertEqual(1, buildsets_resultsdb_failures[0]['patchset'])
+        self.assertEqual(-1, buildsets_resultsdb_failures[0]['score'])
+        self.assertEqual(
+            'Build failed.', buildsets_resultsdb_failures[0]['message'])
+
+
+class TestConnectionsBadSQL(ZuulDBTestCase):
+    def setup_config(self, config_file='zuul-connections-bad-sql.conf'):
+        super(TestConnectionsBadSQL, self).setup_config(config_file)
+
+    def test_unable_to_connect(self):
+        "Test the SQL reporter fails gracefully when unable to connect"
+        self.config.set('zuul', 'layout_config',
+                        'tests/fixtures/layout-sql-reporter.yaml')
+        self.sched.reconfigure(self.config)
+
+        # Trigger a reporter. If no errors are raised, the reporter has been
+        # disabled correctly
+        A = self.fake_review_gerrit.addFakeChange('org/project', 'master', 'A')
+        self.fake_review_gerrit.addEvent(A.getPatchsetCreatedEvent(1))
+        self.waitUntilSettled()
+
 
 class TestMultipleGerrits(ZuulTestCase):
     def setup_config(self,
diff --git a/tests/test_reporter.py b/tests/test_reporter.py
index 8d3090a5b..6a179d229 100644
--- a/tests/test_reporter.py
+++ b/tests/test_reporter.py
@@ -12,18 +12,18 @@
 # License for the specific language governing permissions and limitations
 # under the License.
 
+import fixtures
 import logging
 import testtools
 
-import zuul.reporter
+import zuul.reporter.gerrit
+import zuul.reporter.smtp
+import zuul.reporter.sql
 
 
 class TestSMTPReporter(testtools.TestCase):
     log = logging.getLogger("zuul.test_reporter")
 
-    def setUp(self):
-        super(TestSMTPReporter, self).setUp()
-
     def test_reporter_abc(self):
         # We only need to instantiate a class for this
         reporter = zuul.reporter.smtp.SMTPReporter({})  # noqa
@@ -35,12 +35,30 @@ class TestSMTPReporter(testtools.TestCase):
 class TestGerritReporter(testtools.TestCase):
     log = logging.getLogger("zuul.test_reporter")
 
-    def setUp(self):
-        super(TestGerritReporter, self).setUp()
-
     def test_reporter_abc(self):
         # We only need to instantiate a class for this
         reporter = zuul.reporter.gerrit.GerritReporter(None)  # noqa
 
     def test_reporter_name(self):
         self.assertEqual('gerrit', zuul.reporter.gerrit.GerritReporter.name)
+
+
+class TestSQLReporter(testtools.TestCase):
+    log = logging.getLogger("zuul.test_reporter")
+
+    def test_reporter_abc(self):
+        # We only need to instantiate a class for this
+        # First mock out _setup_tables
+        def _fake_setup_tables(self):
+            pass
+
+        self.useFixture(fixtures.MonkeyPatch(
+            'zuul.reporter.sql.SQLReporter._setup_tables',
+            _fake_setup_tables
+        ))
+
+        reporter = zuul.reporter.sql.SQLReporter()  # noqa
+
+    def test_reporter_name(self):
+        self.assertEqual(
+            'sql', zuul.reporter.sql.SQLReporter.name)