showard | 3544486 | 2008-08-07 22:35:30 +0000 | [diff] [blame] | 1 | from django.db import models as dbmodels, connection |
| 2 | from django.utils import datastructures |
| 3 | from autotest_lib.frontend.afe import model_logic, readonly_connection |
| 4 | |
| 5 | class TempManager(model_logic.ExtendedManager): |
| 6 | _GROUP_COUNT_NAME = 'group_count' |
| 7 | |
| 8 | def _get_key_unless_is_function(self, field): |
| 9 | if '(' in field: |
| 10 | return field |
| 11 | return self._get_key_on_this_table(field) |
| 12 | |
| 13 | |
| 14 | def _get_field_names(self, fields): |
| 15 | return [self._get_key_unless_is_function(field) for field in fields] |
| 16 | |
| 17 | |
| 18 | def _get_group_query_sql(self, query, group_by, extra_select_fields): |
| 19 | group_fields = self._get_field_names(group_by) |
| 20 | if query._distinct: |
| 21 | pk_field = self._get_key_on_this_table(self.model._meta.pk.name) |
| 22 | count_sql = 'COUNT(DISTINCT %s)' % pk_field |
| 23 | else: |
| 24 | count_sql = 'COUNT(1)' |
| 25 | select_fields = (group_fields + |
| 26 | [count_sql + ' AS ' + self._GROUP_COUNT_NAME] + |
| 27 | extra_select_fields) |
| 28 | |
| 29 | # add the count field and all group fields to the query selects, so |
| 30 | # they'll be sortable and Django won't mess with any of them |
| 31 | #for field in group_fields + [self._GROUP_COUNT_NAME]: |
| 32 | # query._select[field] = '' |
| 33 | query._select[self._GROUP_COUNT_NAME] = count_sql |
| 34 | |
| 35 | # Inject the GROUP_BY clause into the query by adding it to the end of |
| 36 | # the queries WHERE clauses. We need it to come before the ORDER BY and |
| 37 | # LIMIT clauses. |
| 38 | num_real_where_clauses = len(query._where) |
| 39 | query._where.append('GROUP BY ' + ', '.join(group_fields)) |
| 40 | _, where, params = query._get_sql_clause() |
| 41 | if num_real_where_clauses == 0: |
| 42 | # handle the special case where there were no actual WHERE clauses |
| 43 | where = where.replace('WHERE GROUP BY', 'GROUP BY') |
| 44 | else: |
| 45 | where = where.replace('AND GROUP BY', 'GROUP BY') |
| 46 | |
| 47 | return ('SELECT ' + ', '.join(select_fields) + where), params |
| 48 | |
| 49 | |
| 50 | def get_group_counts(self, query, group_by, extra_select_fields=[]): |
| 51 | """ |
| 52 | Performs the given query grouped by the fields in group_by. Returns a |
| 53 | list of rows, where each row is a list containing the value of each |
| 54 | field in group_by, followed by the group count. |
| 55 | """ |
| 56 | sql, params = self._get_group_query_sql(query, group_by, |
| 57 | extra_select_fields) |
| 58 | cursor = readonly_connection.connection.cursor() |
| 59 | num_rows = cursor.execute(sql, params) |
| 60 | return cursor.fetchall() |
| 61 | |
| 62 | |
| 63 | def _get_num_groups_sql(self, query, group_by): |
| 64 | group_fields = self._get_field_names(group_by) |
| 65 | query._order_by = None # this can mess up the query is isn't needed |
| 66 | _, where, params = query._get_sql_clause() |
| 67 | return ('SELECT COUNT(DISTINCT %s) %s' % (','.join(group_fields), |
| 68 | where), |
| 69 | params) |
| 70 | |
| 71 | |
| 72 | def get_num_groups(self, query, group_by): |
| 73 | """ |
| 74 | Returns the number of distinct groups for the given query grouped by the |
| 75 | fields in group_by. |
| 76 | """ |
| 77 | sql, params = self._get_num_groups_sql(query, group_by) |
| 78 | cursor = readonly_connection.connection.cursor() |
| 79 | cursor.execute(sql, params) |
| 80 | return cursor.fetchone()[0] |
| 81 | |
| 82 | |
| 83 | class Machine(dbmodels.Model): |
| 84 | machine_idx = dbmodels.IntegerField(primary_key=True) |
| 85 | hostname = dbmodels.CharField(unique=True, maxlength=300) |
| 86 | machine_group = dbmodels.CharField(blank=True, maxlength=240) |
| 87 | owner = dbmodels.CharField(blank=True, maxlength=240) |
| 88 | |
| 89 | class Meta: |
| 90 | db_table = 'machines' |
| 91 | |
| 92 | |
| 93 | class Kernel(dbmodels.Model): |
| 94 | kernel_idx = dbmodels.IntegerField(primary_key=True) |
| 95 | kernel_hash = dbmodels.CharField(maxlength=105, editable=False) |
| 96 | base = dbmodels.CharField(maxlength=90) |
| 97 | printable = dbmodels.CharField(maxlength=300) |
| 98 | |
| 99 | class Meta: |
| 100 | db_table = 'kernels' |
| 101 | |
| 102 | |
| 103 | class Patch(dbmodels.Model): |
| 104 | kernel = dbmodels.ForeignKey(Kernel, db_column='kernel_idx') |
| 105 | name = dbmodels.CharField(blank=True, maxlength=240) |
| 106 | url = dbmodels.CharField(blank=True, maxlength=900) |
| 107 | hash_ = dbmodels.CharField(blank=True, maxlength=105, db_column='hash') |
| 108 | |
| 109 | class Meta: |
| 110 | db_table = 'patches' |
| 111 | |
| 112 | |
| 113 | class Status(dbmodels.Model): |
| 114 | status_idx = dbmodels.IntegerField(primary_key=True) |
| 115 | word = dbmodels.CharField(maxlength=30) |
| 116 | |
| 117 | class Meta: |
| 118 | db_table = 'status' |
| 119 | |
| 120 | |
| 121 | class Job(dbmodels.Model): |
| 122 | job_idx = dbmodels.IntegerField(primary_key=True) |
| 123 | tag = dbmodels.CharField(unique=True, maxlength=300) |
| 124 | label = dbmodels.CharField(maxlength=300) |
| 125 | username = dbmodels.CharField(maxlength=240) |
| 126 | machine = dbmodels.ForeignKey(Machine, db_column='machine_idx') |
| 127 | queued_time = dbmodels.DateTimeField(null=True, blank=True) |
| 128 | started_time = dbmodels.DateTimeField(null=True, blank=True) |
| 129 | finished_time = dbmodels.DateTimeField(null=True, blank=True) |
| 130 | |
| 131 | class Meta: |
| 132 | db_table = 'jobs' |
| 133 | |
| 134 | |
showard | 3544486 | 2008-08-07 22:35:30 +0000 | [diff] [blame] | 135 | class Test(dbmodels.Model): |
| 136 | test_idx = dbmodels.IntegerField(primary_key=True) |
| 137 | job = dbmodels.ForeignKey(Job, db_column='job_idx') |
| 138 | test = dbmodels.CharField(maxlength=90) |
| 139 | subdir = dbmodels.CharField(blank=True, maxlength=180) |
| 140 | kernel = dbmodels.ForeignKey(Kernel, db_column='kernel_idx') |
| 141 | status = dbmodels.ForeignKey(Status, db_column='status') |
| 142 | reason = dbmodels.CharField(blank=True, maxlength=3072) |
| 143 | machine = dbmodels.ForeignKey(Machine, db_column='machine_idx') |
| 144 | finished_time = dbmodels.DateTimeField(null=True, blank=True) |
| 145 | started_time = dbmodels.DateTimeField(null=True, blank=True) |
| 146 | |
showard | 3544486 | 2008-08-07 22:35:30 +0000 | [diff] [blame] | 147 | class Meta: |
| 148 | db_table = 'tests' |
| 149 | |
| 150 | |
| 151 | class TestAttribute(dbmodels.Model): |
| 152 | test = dbmodels.ForeignKey(Test, db_column='test_idx') |
| 153 | attribute = dbmodels.CharField(maxlength=90) |
| 154 | value = dbmodels.CharField(blank=True, maxlength=300) |
| 155 | |
| 156 | class Meta: |
| 157 | db_table = 'test_attributes' |
| 158 | |
| 159 | |
| 160 | class IterationAttribute(dbmodels.Model): |
| 161 | test = dbmodels.ForeignKey(Test, db_column='test_idx') |
| 162 | iteration = dbmodels.IntegerField() |
| 163 | attribute = dbmodels.CharField(maxlength=90) |
| 164 | value = dbmodels.CharField(blank=True, maxlength=300) |
| 165 | |
| 166 | class Meta: |
| 167 | db_table = 'iteration_attributes' |
| 168 | |
| 169 | |
| 170 | class IterationResult(dbmodels.Model): |
| 171 | test = dbmodels.ForeignKey(Test, db_column='test_idx') |
| 172 | iteration = dbmodels.IntegerField() |
| 173 | attribute = dbmodels.CharField(maxlength=90) |
| 174 | value = dbmodels.FloatField(null=True, max_digits=12, decimal_places=31, |
| 175 | blank=True) |
| 176 | |
| 177 | class Meta: |
| 178 | db_table = 'iteration_result' |
| 179 | |
| 180 | |
| 181 | class TestLabel(dbmodels.Model, model_logic.ModelExtensions): |
| 182 | name = dbmodels.CharField(maxlength=80) |
| 183 | description = dbmodels.TextField(blank=True) |
| 184 | tests = dbmodels.ManyToManyField(Test, blank=True, |
| 185 | filter_interface=dbmodels.HORIZONTAL) |
| 186 | |
| 187 | name_field = 'name' |
| 188 | |
| 189 | class Meta: |
| 190 | db_table = 'test_labels' |
| 191 | |
| 192 | |
| 193 | class SavedQuery(dbmodels.Model, model_logic.ModelExtensions): |
| 194 | # TODO: change this to foreign key once DBs are merged |
| 195 | owner = dbmodels.CharField(maxlength=80) |
| 196 | name = dbmodels.CharField(maxlength=100) |
| 197 | url_token = dbmodels.TextField() |
| 198 | |
| 199 | class Meta: |
| 200 | db_table = 'saved_queries' |
| 201 | |
| 202 | |
| 203 | # views |
| 204 | |
| 205 | class TestViewManager(TempManager): |
| 206 | class _JoinQ(dbmodels.Q): |
| 207 | def __init__(self): |
| 208 | self._joins = datastructures.SortedDict() |
| 209 | |
| 210 | |
| 211 | def add_join(self, table, condition, join_type, alias=None): |
| 212 | if alias is None: |
| 213 | alias = table |
| 214 | self._joins[alias] = (table, join_type, condition) |
| 215 | |
| 216 | |
| 217 | def get_sql(self, opts): |
| 218 | return self._joins, [], [] |
| 219 | |
| 220 | |
| 221 | def get_query_set(self): |
| 222 | query = super(TestViewManager, self).get_query_set() |
showard | 5bf7c50 | 2008-08-20 01:22:22 +0000 | [diff] [blame] | 223 | |
showard | 3544486 | 2008-08-07 22:35:30 +0000 | [diff] [blame] | 224 | # add extra fields to selects, using the SQL itself as the "alias" |
| 225 | extra_select = dict((sql, sql) |
| 226 | for sql in self.model.extra_fields.iterkeys()) |
| 227 | return query.extra(select=extra_select) |
| 228 | |
| 229 | |
showard | 0281350 | 2008-08-20 20:52:56 +0000 | [diff] [blame^] | 230 | def _add_label_joins(self, query_set): |
| 231 | table_name = self.model._meta.db_table |
| 232 | filter_object = self._JoinQ() |
| 233 | filter_object.add_join( |
| 234 | 'test_labels_tests', |
| 235 | 'test_labels_tests.test_id = %s.test_idx' % table_name, |
| 236 | 'LEFT JOIN') |
| 237 | filter_object.add_join( |
| 238 | 'test_labels', |
| 239 | 'test_labels.id = test_labels_tests.testlabel_id', |
| 240 | 'LEFT JOIN') |
| 241 | return query_set.complex_filter(filter_object).distinct() |
| 242 | |
| 243 | |
showard | 3544486 | 2008-08-07 22:35:30 +0000 | [diff] [blame] | 244 | def get_query_set_with_labels(self, filter_data): |
| 245 | query_set = self.get_query_set() |
| 246 | # TODO: make this check more thorough if necessary |
| 247 | if 'test_labels' in filter_data.get('extra_where', ''): |
showard | 0281350 | 2008-08-20 20:52:56 +0000 | [diff] [blame^] | 248 | query_set = self._add_label_joins(query_set) |
showard | 3544486 | 2008-08-07 22:35:30 +0000 | [diff] [blame] | 249 | else: |
| 250 | filter_data['no_distinct'] = True |
| 251 | return query_set |
| 252 | |
| 253 | |
showard | 0281350 | 2008-08-20 20:52:56 +0000 | [diff] [blame^] | 254 | def query_test_ids(self, filter_data): |
| 255 | dicts = self.model.query_objects(filter_data).values('test_idx') |
| 256 | return [item['test_idx'] for item in dicts] |
| 257 | |
| 258 | |
| 259 | def _custom_select_query(self, query_set, selects): |
| 260 | query_selects, where, params = query_set._get_sql_clause() |
| 261 | if query_set._distinct: |
| 262 | distinct = 'DISTINCT ' |
| 263 | else: |
| 264 | distinct = '' |
| 265 | sql_query = 'SELECT ' + distinct + ','.join(selects) + where |
| 266 | cursor = readonly_connection.connection.cursor() |
| 267 | cursor.execute(sql_query, params) |
| 268 | return cursor.fetchall() |
| 269 | |
| 270 | |
| 271 | def query_test_label_ids(self, filter_data): |
| 272 | query_set = self._add_label_joins(self.get_query_set()).distinct() |
| 273 | rows = self._custom_select_query(query_set, ['test_labels.id']) |
| 274 | return [row[0] for row in rows] # flatten rows to a list of ids |
| 275 | |
| 276 | |
showard | 3544486 | 2008-08-07 22:35:30 +0000 | [diff] [blame] | 277 | class TestView(dbmodels.Model, model_logic.ModelExtensions): |
| 278 | extra_fields = { |
| 279 | 'DATE(test_finished_time)' : 'test finished day', |
| 280 | } |
| 281 | |
| 282 | group_fields = [ |
| 283 | 'test_name', |
| 284 | 'status', |
| 285 | 'kernel', |
| 286 | 'hostname', |
| 287 | 'job_tag', |
| 288 | 'job_name', |
| 289 | 'platform', |
| 290 | 'reason', |
| 291 | 'job_owner', |
| 292 | 'test_finished_time', |
| 293 | 'DATE(test_finished_time)', |
| 294 | ] |
| 295 | |
| 296 | test_idx = dbmodels.IntegerField('test index', primary_key=True) |
| 297 | job_idx = dbmodels.IntegerField('job index', null=True, blank=True) |
| 298 | test_name = dbmodels.CharField(blank=True, maxlength=90) |
| 299 | subdir = dbmodels.CharField('subdirectory', blank=True, maxlength=180) |
| 300 | kernel_idx = dbmodels.IntegerField('kernel index') |
| 301 | status_idx = dbmodels.IntegerField('status index') |
| 302 | reason = dbmodels.CharField(blank=True, maxlength=3072) |
| 303 | machine_idx = dbmodels.IntegerField('host index') |
| 304 | test_started_time = dbmodels.DateTimeField(null=True, blank=True) |
| 305 | test_finished_time = dbmodels.DateTimeField(null=True, blank=True) |
| 306 | job_tag = dbmodels.CharField(blank=True, maxlength=300) |
| 307 | job_name = dbmodels.CharField(blank=True, maxlength=300) |
| 308 | job_owner = dbmodels.CharField('owner', blank=True, maxlength=240) |
| 309 | job_queued_time = dbmodels.DateTimeField(null=True, blank=True) |
| 310 | job_started_time = dbmodels.DateTimeField(null=True, blank=True) |
| 311 | job_finished_time = dbmodels.DateTimeField(null=True, blank=True) |
| 312 | hostname = dbmodels.CharField(blank=True, maxlength=300) |
| 313 | platform = dbmodels.CharField(blank=True, maxlength=240) |
| 314 | machine_owner = dbmodels.CharField(blank=True, maxlength=240) |
| 315 | kernel_hash = dbmodels.CharField(blank=True, maxlength=105) |
| 316 | kernel_base = dbmodels.CharField(blank=True, maxlength=90) |
| 317 | kernel = dbmodels.CharField(blank=True, maxlength=300) |
| 318 | status = dbmodels.CharField(blank=True, maxlength=30) |
| 319 | |
| 320 | objects = TestViewManager() |
| 321 | |
| 322 | def save(self): |
| 323 | raise NotImplementedError('TestView is read-only') |
| 324 | |
| 325 | |
| 326 | def delete(self): |
| 327 | raise NotImplementedError('TestView is read-only') |
| 328 | |
| 329 | |
| 330 | @classmethod |
| 331 | def query_objects(cls, filter_data, initial_query=None): |
| 332 | if initial_query is None: |
| 333 | initial_query = cls.objects.get_query_set_with_labels(filter_data) |
| 334 | return super(TestView, cls).query_objects(filter_data, |
| 335 | initial_query=initial_query) |
| 336 | |
| 337 | |
| 338 | @classmethod |
| 339 | def list_objects(cls, filter_data, initial_query=None): |
| 340 | """ |
| 341 | Django's ValuesQuerySet (used when you call query.values()) doesn't |
| 342 | support custom select fields, so we have to basically reimplement it |
| 343 | here. |
| 344 | TODO: merge this up to ModelExtensions after some settling time. |
| 345 | """ |
| 346 | query = cls.query_objects(filter_data, initial_query=initial_query) |
| 347 | object_dicts = [] |
| 348 | for model_object in query: |
| 349 | object_dict = model_object.get_object_dict() |
| 350 | for sql in cls.extra_fields.iterkeys(): |
| 351 | object_dict[sql] = getattr(model_object, sql) |
| 352 | object_dicts.append(object_dict) |
| 353 | return object_dicts |
| 354 | |
| 355 | |
| 356 | class Meta: |
| 357 | db_table = 'test_view_2' |