summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorZuul <zuul@review.openstack.org>2018-11-28 21:28:11 +0000
committerGerrit Code Review <review@openstack.org>2018-11-28 21:28:11 +0000
commit7ce90a8a7f1f8bda43fc542825da1ef713a74311 (patch)
treef6365b55ac00c8879c5e06fb4eab8170b2d23cbf
parent85c8794095103edeea0e778dcd4a709943285d39 (diff)
parent3b82885e5c08d9af9fc0b7127d100d807b3db995 (diff)
Merge "Use the SQLAlchemy ORM"
-rw-r--r--zuul/driver/sql/sqlconnection.py182
-rw-r--r--zuul/driver/sql/sqlreporter.py58
-rwxr-xr-xzuul/web/__init__.py76
3 files changed, 181 insertions, 135 deletions
diff --git a/zuul/driver/sql/sqlconnection.py b/zuul/driver/sql/sqlconnection.py
index d7f70d1..ffde917 100644
--- a/zuul/driver/sql/sqlconnection.py
+++ b/zuul/driver/sql/sqlconnection.py
@@ -19,9 +19,8 @@ import alembic.command
19import alembic.config 19import alembic.config
20import sqlalchemy as sa 20import sqlalchemy as sa
21from sqlalchemy.ext.declarative import declarative_base 21from sqlalchemy.ext.declarative import declarative_base
22from sqlalchemy.orm import relationship 22from sqlalchemy import orm
23import sqlalchemy.pool 23import sqlalchemy.pool
24from sqlalchemy.sql import select
25import voluptuous 24import voluptuous
26 25
27from zuul.connection import BaseConnection 26from zuul.connection import BaseConnection
@@ -30,6 +29,75 @@ BUILDSET_TABLE = 'zuul_buildset'
30BUILD_TABLE = 'zuul_build' 29BUILD_TABLE = 'zuul_build'
31 30
32 31
32class DatabaseSession(object):
33 def __init__(self, connection):
34 self.connection = connection
35 self.session = connection.session
36
37 def __enter__(self):
38 return self
39
40 def __exit__(self, etype, value, tb):
41 if etype:
42 self.session().rollback()
43 else:
44 self.session().commit()
45 self.session().close()
46 self.session = None
47
48 def listFilter(self, query, column, value):
49 if value is None:
50 return query
51 if isinstance(value, list):
52 return query.filter(column.in_(value))
53 return query.filter(column == value)
54
55 def getBuilds(self, tenant=None, project=None, pipeline=None,
56 change=None, branch=None, patchset=None, ref=None,
57 newrev=None, uuid=None, job_name=None, voting=None,
58 node_name=None, result=None, limit=50, offset=0):
59
60 build_table = self.connection.zuul_build_table
61 buildset_table = self.connection.zuul_buildset_table
62
63 # contains_eager allows us to perform eager loading on the
64 # buildset *and* use that table in filters (unlike
65 # joinedload).
66 q = self.session().query(self.connection.buildModel).\
67 join(self.connection.buildSetModel).\
68 options(orm.contains_eager(self.connection.buildModel.buildset)).\
69 with_hint(build_table, 'USE INDEX (PRIMARY)', 'mysql')
70
71 q = self.listFilter(q, buildset_table.c.tenant, tenant)
72 q = self.listFilter(q, buildset_table.c.project, project)
73 q = self.listFilter(q, buildset_table.c.pipeline, pipeline)
74 q = self.listFilter(q, buildset_table.c.change, change)
75 q = self.listFilter(q, buildset_table.c.branch, branch)
76 q = self.listFilter(q, buildset_table.c.patchset, patchset)
77 q = self.listFilter(q, buildset_table.c.ref, ref)
78 q = self.listFilter(q, buildset_table.c.newrev, newrev)
79 q = self.listFilter(q, build_table.c.uuid, uuid)
80 q = self.listFilter(q, build_table.c.job_name, job_name)
81 q = self.listFilter(q, build_table.c.voting, voting)
82 q = self.listFilter(q, build_table.c.node_name, node_name)
83 q = self.listFilter(q, build_table.c.result, result)
84
85 q = q.order_by(build_table.c.id.desc()).\
86 limit(limit).\
87 offset(offset)
88
89 try:
90 return q.all()
91 except sqlalchemy.orm.exc.NoResultFound:
92 return []
93
94 def createBuildSet(self, *args, **kw):
95 bs = self.connection.buildSetModel(*args, **kw)
96 self.session().add(bs)
97 self.session().flush()
98 return bs
99
100
33class SQLConnection(BaseConnection): 101class SQLConnection(BaseConnection):
34 driver_name = 'sql' 102 driver_name = 'sql'
35 log = logging.getLogger("zuul.SQLConnection") 103 log = logging.getLogger("zuul.SQLConnection")
@@ -58,6 +126,18 @@ class SQLConnection(BaseConnection):
58 poolclass=sqlalchemy.pool.QueuePool, 126 poolclass=sqlalchemy.pool.QueuePool,
59 pool_recycle=self.connection_config.get('pool_recycle', 1)) 127 pool_recycle=self.connection_config.get('pool_recycle', 1))
60 self._migrate() 128 self._migrate()
129
130 # If we want the objects returned from query() to be
131 # usable outside of the session, we need to expunge them
132 # from the session, and since the DatabaseSession always
133 # calls commit() on the session when the context manager
134 # exits, we need to inform the session not to expire
135 # objects when it does so.
136 self.session_factory = orm.sessionmaker(bind=self.engine,
137 expire_on_commit=False,
138 autoflush=False)
139 self.session = orm.scoped_session(self.session_factory)
140
61 self.tables_established = True 141 self.tables_established = True
62 except sa.exc.NoSuchModuleError: 142 except sa.exc.NoSuchModuleError:
63 self.log.exception( 143 self.log.exception(
@@ -68,6 +148,9 @@ class SQLConnection(BaseConnection):
68 "Unable to connect to the database or establish the required " 148 "Unable to connect to the database or establish the required "
69 "tables. Reporter %s is disabled" % self) 149 "tables. Reporter %s is disabled" % self)
70 150
151 def getSession(self):
152 return DatabaseSession(self)
153
71 def _migrate(self): 154 def _migrate(self):
72 """Perform the alembic migrations for this connection""" 155 """Perform the alembic migrations for this connection"""
73 with self.engine.begin() as conn: 156 with self.engine.begin() as conn:
@@ -89,24 +172,9 @@ class SQLConnection(BaseConnection):
89 def _setup_models(self): 172 def _setup_models(self):
90 Base = declarative_base(metadata=sa.MetaData()) 173 Base = declarative_base(metadata=sa.MetaData())
91 174
92 class BuildModel(Base):
93 __tablename__ = self.table_prefix + BUILD_TABLE
94 id = sa.Column(sa.Integer, primary_key=True)
95 buildset_id = sa.Column(sa.String, sa.ForeignKey(
96 self.table_prefix + BUILDSET_TABLE + ".id"))
97 uuid = sa.Column(sa.String(36))
98 job_name = sa.Column(sa.String(255))
99 result = sa.Column(sa.String(255))
100 start_time = sa.Column(sa.DateTime)
101 end_time = sa.Column(sa.DateTime)
102 voting = sa.Column(sa.Boolean)
103 log_url = sa.Column(sa.String(255))
104 node_name = sa.Column(sa.String(255))
105
106 class BuildSetModel(Base): 175 class BuildSetModel(Base):
107 __tablename__ = self.table_prefix + BUILDSET_TABLE 176 __tablename__ = self.table_prefix + BUILDSET_TABLE
108 id = sa.Column(sa.Integer, primary_key=True) 177 id = sa.Column(sa.Integer, primary_key=True)
109 builds = relationship(BuildModel, lazy="subquery")
110 zuul_ref = sa.Column(sa.String(255)) 178 zuul_ref = sa.Column(sa.String(255))
111 pipeline = sa.Column(sa.String(255)) 179 pipeline = sa.Column(sa.String(255))
112 project = sa.Column(sa.String(255)) 180 project = sa.Column(sa.String(255))
@@ -121,6 +189,30 @@ class SQLConnection(BaseConnection):
121 message = sa.Column(sa.TEXT()) 189 message = sa.Column(sa.TEXT())
122 tenant = sa.Column(sa.String(255)) 190 tenant = sa.Column(sa.String(255))
123 191
192 def createBuild(self, *args, **kw):
193 session = orm.session.Session.object_session(self)
194 b = BuildModel(*args, **kw)
195 b.buildset_id = self.id
196 self.builds.append(b)
197 session.add(b)
198 session.flush()
199 return b
200
201 class BuildModel(Base):
202 __tablename__ = self.table_prefix + BUILD_TABLE
203 id = sa.Column(sa.Integer, primary_key=True)
204 buildset_id = sa.Column(sa.String, sa.ForeignKey(
205 self.table_prefix + BUILDSET_TABLE + ".id"))
206 uuid = sa.Column(sa.String(36))
207 job_name = sa.Column(sa.String(255))
208 result = sa.Column(sa.String(255))
209 start_time = sa.Column(sa.DateTime)
210 end_time = sa.Column(sa.DateTime)
211 voting = sa.Column(sa.Boolean)
212 log_url = sa.Column(sa.String(255))
213 node_name = sa.Column(sa.String(255))
214 buildset = orm.relationship(BuildSetModel, backref="builds")
215
124 self.buildModel = BuildModel 216 self.buildModel = BuildModel
125 self.buildSetModel = BuildSetModel 217 self.buildSetModel = BuildSetModel
126 return self.buildSetModel.__table__, self.buildModel.__table__ 218 return self.buildSetModel.__table__, self.buildModel.__table__
@@ -129,58 +221,10 @@ class SQLConnection(BaseConnection):
129 self.log.debug("Stopping SQL connection %s" % self.connection_name) 221 self.log.debug("Stopping SQL connection %s" % self.connection_name)
130 self.engine.dispose() 222 self.engine.dispose()
131 223
132 def query(self, args): 224 def getBuilds(self, *args, **kw):
133 build = self.zuul_build_table 225 """Return a list of Build objects"""
134 buildset = self.zuul_buildset_table 226 with self.getSession() as db:
135 query = select([ 227 return db.getBuilds(*args, **kw)
136 buildset.c.project,
137 buildset.c.branch,
138 buildset.c.pipeline,
139 buildset.c.change,
140 buildset.c.patchset,
141 buildset.c.ref,
142 buildset.c.newrev,
143 buildset.c.ref_url,
144 build.c.result,
145 build.c.uuid,
146 build.c.job_name,
147 build.c.voting,
148 build.c.node_name,
149 build.c.start_time,
150 build.c.end_time,
151 build.c.log_url]).select_from(build.join(buildset))
152 for table in ('build', 'buildset'):
153 for key, val in args['%s_filters' % table].items():
154 if table == 'build':
155 column = build.c
156 else:
157 column = buildset.c
158 query = query.where(getattr(column, key).in_(val))
159 return query.\
160 limit(args['limit']).\
161 offset(args['skip']).\
162 order_by(build.c.id.desc()).\
163 with_hint(build, 'USE INDEX (PRIMARY)', 'mysql')
164
165 def get_builds(self, args):
166 """Return a list of build"""
167 builds = []
168 with self.engine.begin() as conn:
169 for row in conn.execute(self.query(args)):
170 build = dict(row)
171 # Convert date to iso format
172 if row.start_time:
173 build['start_time'] = row.start_time.strftime(
174 '%Y-%m-%dT%H:%M:%S')
175 if row.end_time:
176 build['end_time'] = row.end_time.strftime(
177 '%Y-%m-%dT%H:%M:%S')
178 # Compute run duration
179 if row.start_time and row.end_time:
180 build['duration'] = (row.end_time -
181 row.start_time).total_seconds()
182 builds.append(build)
183 return builds
184 228
185 229
186def getSchema(): 230def getSchema():
diff --git a/zuul/driver/sql/sqlreporter.py b/zuul/driver/sql/sqlreporter.py
index 8e73f93..4b80923 100644
--- a/zuul/driver/sql/sqlreporter.py
+++ b/zuul/driver/sql/sqlreporter.py
@@ -32,42 +32,31 @@ class SQLReporter(BaseReporter):
32 self.log.warn("SQL reporter (%s) is disabled " % self) 32 self.log.warn("SQL reporter (%s) is disabled " % self)
33 return 33 return
34 34
35 with self.connection.engine.begin() as conn: 35 with self.connection.getSession() as db:
36 change = getattr(item.change, 'number', None) 36 db_buildset = db.createBuildSet(
37 patchset = getattr(item.change, 'patchset', None) 37 tenant=item.pipeline.tenant.name,
38 ref = getattr(item.change, 'ref', '')
39 oldrev = getattr(item.change, 'oldrev', '')
40 newrev = getattr(item.change, 'newrev', '')
41 branch = getattr(item.change, 'branch', '')
42 buildset_ins = self.connection.zuul_buildset_table.insert().values(
43 zuul_ref=item.current_build_set.ref,
44 pipeline=item.pipeline.name, 38 pipeline=item.pipeline.name,
45 project=item.change.project.name, 39 project=item.change.project.name,
46 change=change, 40 change=getattr(item.change, 'number', None),
47 patchset=patchset, 41 patchset=getattr(item.change, 'patchset', None),
48 ref=ref, 42 ref=getattr(item.change, 'ref', ''),
49 oldrev=oldrev, 43 oldrev=getattr(item.change, 'oldrev', ''),
50 newrev=newrev, 44 newrev=getattr(item.change, 'newrev', ''),
45 branch=getattr(item.change, 'branch', ''),
46 zuul_ref=item.current_build_set.ref,
51 ref_url=item.change.url, 47 ref_url=item.change.url,
52 result=item.current_build_set.result, 48 result=item.current_build_set.result,
53 message=self._formatItemReport( 49 message=self._formatItemReport(item, with_jobs=False),
54 item, with_jobs=False),
55 tenant=item.pipeline.tenant.name,
56 branch=branch,
57 ) 50 )
58 buildset_ins_result = conn.execute(buildset_ins)
59 build_inserts = []
60
61 for job in item.getJobs(): 51 for job in item.getJobs():
62 build = item.current_build_set.getBuild(job.name) 52 build = item.current_build_set.getBuild(job.name)
63 if not build: 53 if not build:
64 # build hasn't began. The sql reporter can only send back 54 # build hasn't begun. The sql reporter can only send back
65 # stats about builds. It doesn't understand how to store 55 # stats about builds. It doesn't understand how to store
66 # information about the change. 56 # information about the change.
67 continue 57 continue
68 58
69 (result, url) = item.formatJobResult(job) 59 (result, url) = item.formatJobResult(job)
70
71 start = end = None 60 start = end = None
72 if build.start_time: 61 if build.start_time:
73 start = datetime.datetime.fromtimestamp( 62 start = datetime.datetime.fromtimestamp(
@@ -78,19 +67,16 @@ class SQLReporter(BaseReporter):
78 build.end_time, 67 build.end_time,
79 tz=datetime.timezone.utc) 68 tz=datetime.timezone.utc)
80 69
81 build_inserts.append({ 70 db_buildset.createBuild(
82 'buildset_id': buildset_ins_result.inserted_primary_key[0], 71 uuid=build.uuid,
83 'uuid': build.uuid, 72 job_name=build.job.name,
84 'job_name': build.job.name, 73 result=result,
85 'result': result, 74 start_time=start,
86 'start_time': start, 75 end_time=end,
87 'end_time': end, 76 voting=build.job.voting,
88 'voting': build.job.voting, 77 log_url=url,
89 'log_url': url, 78 node_name=build.node_name,
90 'node_name': build.node_name, 79 )
91 })
92 conn.execute(self.connection.zuul_build_table.insert(),
93 build_inserts)
94 80
95 81
96def getSchema(): 82def getSchema():
diff --git a/zuul/web/__init__.py b/zuul/web/__init__.py
index b5a3e63..1b49e8f 100755
--- a/zuul/web/__init__.py
+++ b/zuul/web/__init__.py
@@ -374,6 +374,44 @@ class ZuulWebAPI(object):
374 resp.headers['Content-Type'] = 'text/plain' 374 resp.headers['Content-Type'] = 'text/plain'
375 return job.data[0] + '\n' 375 return job.data[0] + '\n'
376 376
377 def buildToDict(self, build):
378 start_time = build.start_time
379 if build.start_time:
380 start_time = start_time.strftime(
381 '%Y-%m-%dT%H:%M:%S')
382 end_time = build.end_time
383 if build.end_time:
384 end_time = end_time.strftime(
385 '%Y-%m-%dT%H:%M:%S')
386 if build.start_time and build.end_time:
387 duration = (build.end_time -
388 build.start_time).total_seconds()
389 else:
390 duration = None
391
392 buildset = build.buildset
393 ret = {
394 'uuid': build.uuid,
395 'job_name': build.job_name,
396 'result': build.result,
397 'start_time': start_time,
398 'end_time': end_time,
399 'duration': duration,
400 'voting': build.voting,
401 'log_url': build.log_url,
402 'node_name': build.node_name,
403
404 'project': buildset.project,
405 'branch': buildset.branch,
406 'pipeline': buildset.pipeline,
407 'change': buildset.change,
408 'patchset': buildset.patchset,
409 'ref': buildset.ref,
410 'newrev': buildset.newrev,
411 'ref_url': buildset.ref_url,
412 }
413 return ret
414
377 @cherrypy.expose 415 @cherrypy.expose
378 @cherrypy.tools.save_params() 416 @cherrypy.tools.save_params()
379 @cherrypy.tools.json_out(content_type='application/json; charset=utf-8') 417 @cherrypy.tools.json_out(content_type='application/json; charset=utf-8')
@@ -391,31 +429,15 @@ class ZuulWebAPI(object):
391 429
392 connection = self.zuulweb.connections.connections[connection_name] 430 connection = self.zuulweb.connections.connections[connection_name]
393 431
394 args = { 432 builds = connection.getBuilds(
395 'buildset_filters': {'tenant': [tenant]}, 433 tenant=tenant, project=project, pipeline=pipeline, change=change,
396 'build_filters': {}, 434 branch=branch, patchset=patchset, ref=ref, newrev=newrev,
397 'limit': limit, 435 uuid=uuid, job_name=job_name, voting=voting, node_name=node_name,
398 'skip': skip, 436 result=result, limit=limit, offset=skip)
399 }
400 437
401 for k in ("project", "pipeline", "change", "branch",
402 "patchset", "ref", "newrev"):
403 v = locals()[k]
404 if v:
405 if not isinstance(v, list):
406 v = [v]
407 args['buildset_filters'].setdefault(k, []).extend(v)
408 for k in ("uuid", "job_name", "voting", "node_name",
409 "result"):
410 v = locals()[k]
411 if v:
412 if not isinstance(v, list):
413 v = [v]
414 args['build_filters'].setdefault(k, []).extend(v)
415 data = connection.get_builds(args)
416 resp = cherrypy.response 438 resp = cherrypy.response
417 resp.headers['Access-Control-Allow-Origin'] = '*' 439 resp.headers['Access-Control-Allow-Origin'] = '*'
418 return data 440 return [self.buildToDict(b) for b in builds]
419 441
420 @cherrypy.expose 442 @cherrypy.expose
421 @cherrypy.tools.save_params() 443 @cherrypy.tools.save_params()
@@ -431,16 +453,10 @@ class ZuulWebAPI(object):
431 453
432 connection = self.zuulweb.connections.connections[connection_name] 454 connection = self.zuulweb.connections.connections[connection_name]
433 455
434 args = { 456 data = connection.getBuilds(tenant=tenant, uuid=uuid, limit=1)
435 'buildset_filters': {'tenant': [tenant]},
436 'build_filters': {'uuid': [uuid]},
437 'limit': 1,
438 'skip': 0,
439 }
440 data = connection.get_builds(args)
441 if not data: 457 if not data:
442 raise cherrypy.HTTPError(404, "Build not found") 458 raise cherrypy.HTTPError(404, "Build not found")
443 data = data[0] 459 data = self.buildToDict(data[0])
444 resp = cherrypy.response 460 resp = cherrypy.response
445 resp.headers['Access-Control-Allow-Origin'] = '*' 461 resp.headers['Access-Control-Allow-Origin'] = '*'
446 return data 462 return data