« back to all changes in this revision
Viewing changes to nf-stats-service/nfss/database.py
- browse files at revision 629.2.1
- compare with another revision
- download diff
- download tarball
- view history from revision 629.2.1
- Committer: Thomi Richards
- Date: 2014-06-27 20:02:44 UTC
- mto: (629.2.9 nfss)
- mto: This revision was merged to the branch mainline in revision 636.
- Revision ID: thomi.richards@canonical.com-20140627200244-zi7dwxnyw38ypr2f
Initial version.
- files added:
- juju-deployer/configs/nfss_http_vhost
- nf-stats-service
- nf-stats-service/db_patches
- nf-stats-service/nfss
- nf-stats-service/nfss/api
- nf-stats-service/nfss/tests
- nf-stats-service/nfss/tests/functional
- nf-stats-service/nfss/tests/unit
- nf-stats-service/web_static
- nf-stats-service/web_static/graphs
- nf-stats-service/web_static/v
- files modified:
- charms/precise/restish/hooks/hooks.py
added
removed
nf-stats-service/nfss/database.py
1
# Ubuntu CI Engine
2
# Copyright 2014 Canonical Ltd.
3
4
# This program is free software: you can redistribute it and/or modify it
5
# under the terms of the GNU Affero General Public License version 3, as
6
# published by the Free Software Foundation.
7
8
# This program is distributed in the hope that it will be useful, but
9
# WITHOUT ANY WARRANTY; without even the implied warranties of
10
# MERCHANTABILITY, SATISFACTORY QUALITY, or FITNESS FOR A PARTICULAR
11
# PURPOSE. See the GNU Affero General Public License for more details.
12
13
# You should have received a copy of the GNU Affero General Public License
14
# along with this program. If not, see <http://www.gnu.org/licenses/>.
15
16
"""Functions that access the database."""
17
18
import contextlib
19
import os.path
20
import functools
21
import json
22
import logging
23
24
logger = logging.getLogger(__name__)
25
26
import psycopg2.pool
27
28
29
def get_settings_path():
30
return os.path.abspath(
31
os.path.join(
32
os.path.dirname(__file__),
33
'..',
34
'..',
35
'pgsql.json'
36
)
37
)
38
39
40
@functools.lru_cache()
41
def get_db_settings(settings_path):
42
"""Return a dictionary containing the database settings, as provided by
43
juju.
44
45
If the settings file does not exist an IOError is returned.
46
If the settings file exists, but is not valid JSON, a ValueError is
47
returned.
48
49
"""
50
51
with open(settings_path, 'r') as settings_file:
52
return json.load(settings_file)
53
54
55
def translate_juju_db_settings(db_settings):
56
"""Given a dictionary of juju database settings, return a dictionary of
57
psycopg2 database connection settings.
58
59
"""
60
return dict(
61
database=db_settings['database'],
62
user=db_settings['schema_user'],
63
password=db_settings['schema_password'],
64
host=db_settings['host'],
65
port=db_settings['port'],
66
)
67
68
69
def get_connection_for_request(request=None):
70
"""Get a database connection.
71
72
This is bound to every request object we recieve as 'request.database'.
73
74
Note: If this argument is not called with the 'request' parameter then the
75
caller *must* call '.close()' on the returned connection object.
76
77
"""
78
juju_db_settings = get_db_settings(get_settings_path())
79
db_kwargs = translate_juju_db_settings(juju_db_settings)
80
connection = psycopg2.connect(**db_kwargs)
81
if request is not None:
82
def cleanup(request):
83
connection.close()
84
request.add_finished_callback(cleanup)
85
return connection
86
87
88
@contextlib.contextmanager
89
def get_scoped_connection():
90
"""Get a database connection.
91
92
This context manager will return a database connection, and will
93
automatically close the connection when the context manager exits.
94
95
Use like so::
96
97
with nfss.database.get_scoped_connection() as db:
98
cursor = db.cursor()
99
cursor.execute('SELECT 1+1;')
100
db.commit()
101
102
"""
103
connection = get_connection_for_request()
104
try:
105
yield connection
106
finally:
107
connection.close()
108
109
110
def autocommit(fn):
111
"""A simple utility decorator that wraps a method that gets content from
112
the database. Using this will ensure that:
113
114
1) If the wrapped function returns without raising an exception, the
115
connection's 'commit()' method is called.
116
2) If the wrapped function raises an exception, the connection's
117
'rollback()' method will be called.
118
119
This is important since by default, any operation (including simple
120
SELECT queries) starts a new transaction. Without calling 'commit' or
121
'rollback' these transactions gradually use up more and more resources.
122
123
"""
124
def worker(connection, *args, **kwargs):
125
try:
126
result = fn(connection, *args, **kwargs)
127
connection.commit()
128
return result
129
except Exception:
130
connection.rollback()
131
raise
132
return worker
133
134
135
@autocommit
136
def get_details_for_all_projects(connection):
137
"""Get high level details for all projects in the system.
138
139
The return value will be a list of lists. Each inner list will contain:
140
141
[project_name, test_array, max_date]
142
143
* project_name is obvious - it's the name of the project.
144
* test_array is a json formatted array of test names that have had data
145
entered for this project.
146
* max_date is the date of the last data point for this project in any test.
147
148
"""
149
cursor = connection.cursor()
150
cursor.execute('''
151
SELECT
152
project.name,
153
json_agg(distinct test.name),
154
max(data.date_entered)
155
FROM project
156
INNER JOIN test ON project.id = test.project_id
157
INNER JOIN data ON test.id = data.test_id
158
GROUP BY project.name;
159
''')
160
return cursor.fetchall()
161
162
163
@autocommit
164
def get_details_for_project(connection, project_name):
165
"""Get high-level details for a single project.
166
167
The return value will be a json string containing the test name, first and
168
last data point dates, and a count of data points, in an array, for each
169
test.
170
171
"""
172
cursor = connection.cursor()
173
cursor.execute(
174
'''
175
SELECT
176
test.name as test_name,
177
min(data.date_entered) as first_data,
178
max(data.date_entered) as last_data,
179
count(data.id) as data_points
180
FROM test
181
INNER JOIN data on data.test_id = test.id
182
INNER JOIN project on test.project_id = project.id
183
WHERE project.name = %s
184
GROUP BY test.name;
185
''',
186
(project_name,),
187
)
188
data = {}
189
for row in cursor:
190
data[row[0]] = dict(
191
first_data=row[1].isoformat(),
192
last_data=row[2].isoformat(),
193
data_points=row[3]
194
)
195
return data
196
197
198
@autocommit
199
def get_details_for_test(connection, project_name, test_name, start_time=None,
200
end_time=None):
201
"""Get data points for a particular project / test combo.
202
203
If start_time is specified, end time must also be specified. If either are
204
specified, they must be datetime objects. If neither are specified, then
205
the data range will be 30 days from the last data point.
206
207
This functions returns a tuple that contins, in order:
208
209
* A json string containing the test data points.
210
* A datetime object representing the point at which the returned data
211
range starts.
212
* A datetime object representing the point at which the returned data
213
range ends.
214
* A datetime object representing the first data point in the test data.
215
* A datetime object represending the last data point in the test data.
216
217
"""
218
cursor = connection.cursor()
219
220
if all((start_time, end_time)):
221
cursor.execute(
222
'''
223
SELECT
224
min(data.date_entered),
225
max(data.date_entered)
226
FROM data
227
INNER JOIN test on data.test_id = test.id
228
INNER JOIN project on test.project_id = project.id
229
WHERE project.name = %s
230
AND test.name = %s;
231
''',
232
(project_name, test_name)
233
)
234
first, last = cursor.fetchone()
235
cursor.execute(
236
'''
237
SELECT json_agg(d)
238
FROM (
239
SELECT
240
data.date_entered AS date_entered,
241
data.id AS id,
242
data.data AS data
243
FROM data
244
INNER JOIN test on data.test_id = test.id
245
INNER JOIN project on test.project_id = project.id
246
WHERE project.name = %s
247
AND test.name = %s
248
AND data.date_entered >= %s
249
AND data.date_entered <= %s
250
) as d;
251
''',
252
(project_name, test_name, start_time, end_time)
253
)
254
return (
255
cursor.fetchone()[0],
256
first,
257
last,
258
start_time,
259
end_time,
260
)
261
else:
262
# user didn't specify dates.
263
cursor.execute(
264
'''
265
SELECT
266
min(data.date_entered),
267
max(data.date_entered) - interval '30 days',
268
max(data.date_entered)
269
FROM data
270
INNER JOIN test on data.test_id = test.id
271
INNER JOIN project on test.project_id = project.id
272
WHERE project.name = %s
273
AND test.name = %s;
274
''',
275
(project_name, test_name)
276
)
277
first, data_first, last = cursor.fetchone()
278
cursor.execute(
279
'''
280
SELECT json_agg(d)
281
FROM (
282
SELECT
283
data.date_entered AS date_entered,
284
data.id AS id,
285
data.data AS data
286
FROM data
287
INNER JOIN test on data.test_id = test.id
288
INNER JOIN project on test.project_id = project.id
289
WHERE project.name = %s
290
AND test.name = %s
291
AND data.date_entered >= %s
292
AND data.date_entered <= %s
293
) as d;
294
''',
295
(project_name, test_name, data_first, last)
296
)
297
return (
298
cursor.fetchone()[0],
299
first,
300
last,
301
data_first,
302
last,
303
)
304
305
306
@autocommit
307
def insert_test_data(connection, project_name, test_name, data, client_key):
308
# TODO; also link client that inserted it...
309
project_id = _maybe_create_project(connection, project_name)
310
test_id = _maybe_create_test(connection, project_id, test_name)
311
cursor = connection.cursor()
312
cursor.execute(
313
"INSERT INTO data (date_entered, data, test_id, client_id) "
314
"VALUES ('now', %s, %s, (SELECT id FROM client where access_key = %s))"
315
" RETURNING id;",
316
(data, test_id, client_key)
317
)
318
return cursor.fetchone()[0]
319
320
321
def _maybe_create_project(connection, project_name):
322
"""If the project exists, return it's id. If it doesn't, create it and
323
return it's id.
324
325
"""
326
cursor = connection.cursor()
327
cursor.execute('SELECT id FROM project WHERE name = %s;', (project_name,))
328
if cursor.rowcount > 0:
329
return cursor.fetchone()[0]
330
cursor.execute(
331
'INSERT INTO project (name) VALUES (%s) RETURNING id;',
332
(project_name,)
333
)
334
return cursor.fetchone()[0]
335
336
337
def _maybe_create_test(connection, project_id, test_name):
338
cursor = connection.cursor()
339
cursor.execute(
340
'SELECT id FROM test WHERE name = %s AND project_id = %s;',
341
(test_name, project_id)
342
)
343
if cursor.rowcount > 0:
344
return cursor.fetchone()[0]
345
cursor.execute(
346
'INSERT INTO test (name, project_id) VALUES (%s, %s) RETURNING id;',
347
(test_name, project_id)
348
)
349
return cursor.fetchone()[0]
350
351
352
@autocommit
353
def get_auth_client_key_exists(connection, client_key):
354
cursor = connection.cursor()
355
cursor.execute(
356
'SELECT id FROM client WHERE access_key = %s AND valid = true;',
357
(client_key,)
358
)
359
return cursor.rowcount != 0
360
361
362
@autocommit
363
def get_auth_client_key_list(connection):
364
"""Get a list of valid client keys."""
365
cursor = connection.cursor()
366
cursor.execute('SELECT access_key FROM client WHERE valid = true;')
367
return [r[0] for r in cursor.fetchall()]
368
369
370
@autocommit
371
def invalidate_client_key(connection, client_key):
372
cursor = connection.cursor()
373
cursor.execute(
374
'UPDATE client SET valid = false WHERE access_key = %s;',
375
(client_key,)
376
)
377
378
379
@autocommit
380
def get_auth_resource_owner_key_for_client_key(connection, client_key):
381
cursor = connection.cursor()
382
cursor.execute(
383
'SELECT resource_owner_key FROM client WHERE access_key = %s;',
384
(client_key,)
385
)
386
return cursor.fetchone()[0]
387
388
389
@autocommit
390
def auth_add_client_details(connection, name, description, poc, access_key,
391
owner_key, owner_secret):
392
cursor = connection.cursor()
393
cursor.execute(
394
'''
395
INSERT INTO client (access_key, name, description, point_of_contact,
396
resource_owner_key, resource_owner_secret)
397
VALUES (%s, %s, %s, %s, %s, %s);
398
''',
399
(access_key, name, description, poc, owner_key, owner_secret)
400
)
401
402
403
@autocommit
404
def get_auth_resource_owner_secret_for_client_key(connection, client_key):
405
cursor = connection.cursor()
406
cursor.execute(
407
'SELECT resource_owner_secret FROM client where access_key = %s;',
408
(client_key,)
409
)
410
return cursor.fetchone()[0]
411
412
413
@autocommit
414
def get_auth_nonce_already_used(conn, client_key, timestamp, nonce, owner_key):
415
cursor = conn.cursor()
416
cursor.execute(
417
'''
418
SELECT * FROM nonce
419
WHERE access_key = %s
420
AND timestamp = to_timestamp(%s)
421
AND nonce = %s
422
AND resource_owner_key = %s;
423
''',
424
(client_key, timestamp, nonce, owner_key)
425
)
426
return cursor.rowcount != 0
427
428
429
@autocommit
430
def store_nonce(connection, client_key, timestamp, nonce, owner_key):
431
cursor = connection.cursor()
432
cursor.execute(
433
'''
434
INSERT INTO nonce (access_key, timestamp, nonce, resource_owner_key)
435
VALUES (%s, to_timestamp(%s), %s, %s);
436
''',
437
(client_key, timestamp, nonce, owner_key)
438
)
439
return
440
441
442
@autocommit
443
def clean_old_nonces(connection):
444
cursor = connection.cursor()
445
cursor.execute(
446
'''
447
DELETE FROM nonce
448
WHERE timestamp <
449
CURRENT_TIMESTAMP AT TIME ZONE 'UTC' - CAST('1 day' AS interval);
450
'''
451
)
Loggerhead is a web-based interface for Breezy
Version: 2.0.1