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 |
showard | 7c199df | 2008-10-03 10:17:15 +0000 | [diff] [blame] | 11 | return self.get_key_on_this_table(field) |
showard | 3544486 | 2008-08-07 22:35:30 +0000 | [diff] [blame] | 12 | |
| 13 | |
showard | f248952 | 2008-10-23 23:08:00 +0000 | [diff] [blame] | 14 | def _get_field_names(self, fields, extra_select_fields={}): |
| 15 | field_names = [] |
| 16 | for field in fields: |
| 17 | if field in extra_select_fields: |
| 18 | field_names.append(field) |
| 19 | else: |
| 20 | field_names.append(self._get_key_unless_is_function(field)) |
| 21 | return field_names |
showard | 3544486 | 2008-08-07 22:35:30 +0000 | [diff] [blame] | 22 | |
| 23 | |
| 24 | def _get_group_query_sql(self, query, group_by, extra_select_fields): |
showard | f248952 | 2008-10-23 23:08:00 +0000 | [diff] [blame] | 25 | group_fields = self._get_field_names(group_by, extra_select_fields) |
showard | 3544486 | 2008-08-07 22:35:30 +0000 | [diff] [blame] | 26 | |
showard | a5288b4 | 2009-07-28 20:06:08 +0000 | [diff] [blame] | 27 | # Clone the queryset, so that we don't change the original |
| 28 | query = query.all() |
showard | 3544486 | 2008-08-07 22:35:30 +0000 | [diff] [blame] | 29 | |
showard | a5288b4 | 2009-07-28 20:06:08 +0000 | [diff] [blame] | 30 | # In order to use query.extra(), we need to first clear the limits |
| 31 | # and then add them back in after the extra |
| 32 | low = query.query.low_mark |
| 33 | high = query.query.high_mark |
| 34 | query.query.clear_limits() |
| 35 | |
| 36 | select_fields = dict( |
| 37 | (field_name, self._get_key_unless_is_function(field_sql)) |
| 38 | for field_name, field_sql in extra_select_fields.iteritems()) |
| 39 | query = query.extra(select=select_fields) |
| 40 | |
| 41 | query.query.set_limits(low=low, high=high) |
| 42 | |
| 43 | sql, params = query.query.as_sql() |
showard | d50ffb4 | 2008-09-04 02:47:45 +0000 | [diff] [blame] | 44 | |
| 45 | # insert GROUP BY clause into query |
showard | 7c199df | 2008-10-03 10:17:15 +0000 | [diff] [blame] | 46 | group_by_clause = ' GROUP BY ' + ', '.join(group_fields) |
showard | a5288b4 | 2009-07-28 20:06:08 +0000 | [diff] [blame] | 47 | group_by_position = sql.rfind('ORDER BY') |
showard | d50ffb4 | 2008-09-04 02:47:45 +0000 | [diff] [blame] | 48 | if group_by_position == -1: |
showard | a5288b4 | 2009-07-28 20:06:08 +0000 | [diff] [blame] | 49 | group_by_position = len(sql) |
| 50 | sql = (sql[:group_by_position] + |
| 51 | group_by_clause + ' ' + |
| 52 | sql[group_by_position:]) |
showard | 3544486 | 2008-08-07 22:35:30 +0000 | [diff] [blame] | 53 | |
showard | a5288b4 | 2009-07-28 20:06:08 +0000 | [diff] [blame] | 54 | return sql, params |
showard | 3544486 | 2008-08-07 22:35:30 +0000 | [diff] [blame] | 55 | |
| 56 | |
showard | 06b82fc | 2009-06-30 01:59:42 +0000 | [diff] [blame] | 57 | def _get_column_names(self, cursor): |
| 58 | """\ |
| 59 | Gets the column names from the cursor description. This method exists |
| 60 | so that it can be mocked in the unit test for sqlite3 compatibility." |
| 61 | """ |
| 62 | return [column_info[0] for column_info in cursor.description] |
| 63 | |
| 64 | |
showard | 8a6eb0c | 2008-10-01 11:38:59 +0000 | [diff] [blame] | 65 | def execute_group_query(self, query, group_by, extra_select_fields=[]): |
showard | 3544486 | 2008-08-07 22:35:30 +0000 | [diff] [blame] | 66 | """ |
showard | 8a6eb0c | 2008-10-01 11:38:59 +0000 | [diff] [blame] | 67 | Performs the given query grouped by the fields in group_by with the |
showard | 7c199df | 2008-10-03 10:17:15 +0000 | [diff] [blame] | 68 | given extra select fields added. extra_select_fields should be a dict |
| 69 | mapping field alias to field SQL. Usually, the extra fields will use |
showard | 8a6eb0c | 2008-10-01 11:38:59 +0000 | [diff] [blame] | 70 | group aggregation functions. Returns a list of dicts, where each dict |
| 71 | corresponds to single row and contains a key for each grouped field as |
| 72 | well as all of the extra select fields. |
showard | 3544486 | 2008-08-07 22:35:30 +0000 | [diff] [blame] | 73 | """ |
| 74 | sql, params = self._get_group_query_sql(query, group_by, |
| 75 | extra_select_fields) |
showard | 56e9377 | 2008-10-06 10:06:22 +0000 | [diff] [blame] | 76 | cursor = readonly_connection.connection().cursor() |
showard | 8a6eb0c | 2008-10-01 11:38:59 +0000 | [diff] [blame] | 77 | cursor.execute(sql, params) |
showard | 06b82fc | 2009-06-30 01:59:42 +0000 | [diff] [blame] | 78 | field_names = self._get_column_names(cursor) |
showard | 8a6eb0c | 2008-10-01 11:38:59 +0000 | [diff] [blame] | 79 | row_dicts = [dict(zip(field_names, row)) for row in cursor.fetchall()] |
| 80 | return row_dicts |
| 81 | |
| 82 | |
| 83 | def get_count_sql(self, query): |
| 84 | """ |
| 85 | Get the SQL to properly select a per-group count of unique matches for |
showard | 7c199df | 2008-10-03 10:17:15 +0000 | [diff] [blame] | 86 | a grouped query. Returns a tuple (field alias, field SQL) |
showard | 8a6eb0c | 2008-10-01 11:38:59 +0000 | [diff] [blame] | 87 | """ |
showard | a5288b4 | 2009-07-28 20:06:08 +0000 | [diff] [blame] | 88 | if query.query.distinct: |
showard | 7c199df | 2008-10-03 10:17:15 +0000 | [diff] [blame] | 89 | pk_field = self.get_key_on_this_table() |
showard | 8a6eb0c | 2008-10-01 11:38:59 +0000 | [diff] [blame] | 90 | count_sql = 'COUNT(DISTINCT %s)' % pk_field |
| 91 | else: |
| 92 | count_sql = 'COUNT(1)' |
showard | 7c199df | 2008-10-03 10:17:15 +0000 | [diff] [blame] | 93 | return self._GROUP_COUNT_NAME, count_sql |
showard | 3544486 | 2008-08-07 22:35:30 +0000 | [diff] [blame] | 94 | |
| 95 | |
| 96 | def _get_num_groups_sql(self, query, group_by): |
| 97 | group_fields = self._get_field_names(group_by) |
showard | a5288b4 | 2009-07-28 20:06:08 +0000 | [diff] [blame] | 98 | query = query.order_by() # this can mess up the query and isn't needed |
| 99 | |
| 100 | sql, params = query.query.as_sql() |
| 101 | from_ = sql[sql.find(' FROM'):] |
showard | 3544486 | 2008-08-07 22:35:30 +0000 | [diff] [blame] | 102 | return ('SELECT COUNT(DISTINCT %s) %s' % (','.join(group_fields), |
showard | a5288b4 | 2009-07-28 20:06:08 +0000 | [diff] [blame] | 103 | from_), |
showard | 3544486 | 2008-08-07 22:35:30 +0000 | [diff] [blame] | 104 | params) |
| 105 | |
| 106 | |
| 107 | def get_num_groups(self, query, group_by): |
| 108 | """ |
| 109 | Returns the number of distinct groups for the given query grouped by the |
| 110 | fields in group_by. |
| 111 | """ |
| 112 | sql, params = self._get_num_groups_sql(query, group_by) |
showard | 56e9377 | 2008-10-06 10:06:22 +0000 | [diff] [blame] | 113 | cursor = readonly_connection.connection().cursor() |
showard | 3544486 | 2008-08-07 22:35:30 +0000 | [diff] [blame] | 114 | cursor.execute(sql, params) |
| 115 | return cursor.fetchone()[0] |
| 116 | |
| 117 | |
| 118 | class Machine(dbmodels.Model): |
showard | f8b1904 | 2009-05-12 17:22:49 +0000 | [diff] [blame] | 119 | machine_idx = dbmodels.AutoField(primary_key=True) |
showard | a5288b4 | 2009-07-28 20:06:08 +0000 | [diff] [blame] | 120 | hostname = dbmodels.CharField(unique=True, max_length=300) |
| 121 | machine_group = dbmodels.CharField(blank=True, max_length=240) |
| 122 | owner = dbmodels.CharField(blank=True, max_length=240) |
showard | 3544486 | 2008-08-07 22:35:30 +0000 | [diff] [blame] | 123 | |
| 124 | class Meta: |
| 125 | db_table = 'machines' |
| 126 | |
| 127 | |
| 128 | class Kernel(dbmodels.Model): |
showard | f8b1904 | 2009-05-12 17:22:49 +0000 | [diff] [blame] | 129 | kernel_idx = dbmodels.AutoField(primary_key=True) |
showard | a5288b4 | 2009-07-28 20:06:08 +0000 | [diff] [blame] | 130 | kernel_hash = dbmodels.CharField(max_length=105, editable=False) |
| 131 | base = dbmodels.CharField(max_length=90) |
| 132 | printable = dbmodels.CharField(max_length=300) |
showard | 3544486 | 2008-08-07 22:35:30 +0000 | [diff] [blame] | 133 | |
| 134 | class Meta: |
| 135 | db_table = 'kernels' |
| 136 | |
| 137 | |
| 138 | class Patch(dbmodels.Model): |
| 139 | kernel = dbmodels.ForeignKey(Kernel, db_column='kernel_idx') |
showard | a5288b4 | 2009-07-28 20:06:08 +0000 | [diff] [blame] | 140 | name = dbmodels.CharField(blank=True, max_length=240) |
| 141 | url = dbmodels.CharField(blank=True, max_length=900) |
| 142 | the_hash = dbmodels.CharField(blank=True, max_length=105, db_column='hash') |
showard | 3544486 | 2008-08-07 22:35:30 +0000 | [diff] [blame] | 143 | |
| 144 | class Meta: |
| 145 | db_table = 'patches' |
| 146 | |
| 147 | |
| 148 | class Status(dbmodels.Model): |
showard | f8b1904 | 2009-05-12 17:22:49 +0000 | [diff] [blame] | 149 | status_idx = dbmodels.AutoField(primary_key=True) |
showard | a5288b4 | 2009-07-28 20:06:08 +0000 | [diff] [blame] | 150 | word = dbmodels.CharField(max_length=30) |
showard | 3544486 | 2008-08-07 22:35:30 +0000 | [diff] [blame] | 151 | |
| 152 | class Meta: |
| 153 | db_table = 'status' |
| 154 | |
| 155 | |
| 156 | class Job(dbmodels.Model): |
showard | f8b1904 | 2009-05-12 17:22:49 +0000 | [diff] [blame] | 157 | job_idx = dbmodels.AutoField(primary_key=True) |
showard | a5288b4 | 2009-07-28 20:06:08 +0000 | [diff] [blame] | 158 | tag = dbmodels.CharField(unique=True, max_length=300) |
| 159 | label = dbmodels.CharField(max_length=300) |
| 160 | username = dbmodels.CharField(max_length=240) |
showard | 3544486 | 2008-08-07 22:35:30 +0000 | [diff] [blame] | 161 | machine = dbmodels.ForeignKey(Machine, db_column='machine_idx') |
| 162 | queued_time = dbmodels.DateTimeField(null=True, blank=True) |
| 163 | started_time = dbmodels.DateTimeField(null=True, blank=True) |
| 164 | finished_time = dbmodels.DateTimeField(null=True, blank=True) |
| 165 | |
| 166 | class Meta: |
| 167 | db_table = 'jobs' |
| 168 | |
| 169 | |
showard | f8b1904 | 2009-05-12 17:22:49 +0000 | [diff] [blame] | 170 | class Test(dbmodels.Model, model_logic.ModelExtensions, |
| 171 | model_logic.ModelWithAttributes): |
| 172 | test_idx = dbmodels.AutoField(primary_key=True) |
showard | 3544486 | 2008-08-07 22:35:30 +0000 | [diff] [blame] | 173 | job = dbmodels.ForeignKey(Job, db_column='job_idx') |
showard | a5288b4 | 2009-07-28 20:06:08 +0000 | [diff] [blame] | 174 | test = dbmodels.CharField(max_length=90) |
| 175 | subdir = dbmodels.CharField(blank=True, max_length=180) |
showard | 3544486 | 2008-08-07 22:35:30 +0000 | [diff] [blame] | 176 | kernel = dbmodels.ForeignKey(Kernel, db_column='kernel_idx') |
| 177 | status = dbmodels.ForeignKey(Status, db_column='status') |
showard | a5288b4 | 2009-07-28 20:06:08 +0000 | [diff] [blame] | 178 | reason = dbmodels.CharField(blank=True, max_length=3072) |
showard | 3544486 | 2008-08-07 22:35:30 +0000 | [diff] [blame] | 179 | machine = dbmodels.ForeignKey(Machine, db_column='machine_idx') |
| 180 | finished_time = dbmodels.DateTimeField(null=True, blank=True) |
| 181 | started_time = dbmodels.DateTimeField(null=True, blank=True) |
| 182 | |
showard | f8b1904 | 2009-05-12 17:22:49 +0000 | [diff] [blame] | 183 | objects = model_logic.ExtendedManager() |
| 184 | |
| 185 | def _get_attribute_model_and_args(self, attribute): |
| 186 | return TestAttribute, dict(test=self, attribute=attribute, |
| 187 | user_created=True) |
| 188 | |
| 189 | |
| 190 | def set_attribute(self, attribute, value): |
| 191 | # ensure non-user-created attributes remain immutable |
| 192 | try: |
| 193 | TestAttribute.objects.get(test=self, attribute=attribute, |
| 194 | user_created=False) |
| 195 | raise ValueError('Attribute %s already exists for test %s and is ' |
| 196 | 'immutable' % (attribute, self.test_idx)) |
| 197 | except TestAttribute.DoesNotExist: |
| 198 | super(Test, self).set_attribute(attribute, value) |
| 199 | |
| 200 | |
showard | 3544486 | 2008-08-07 22:35:30 +0000 | [diff] [blame] | 201 | class Meta: |
| 202 | db_table = 'tests' |
| 203 | |
| 204 | |
showard | e732ee7 | 2008-09-23 19:15:43 +0000 | [diff] [blame] | 205 | class TestAttribute(dbmodels.Model, model_logic.ModelExtensions): |
showard | f8b1904 | 2009-05-12 17:22:49 +0000 | [diff] [blame] | 206 | test = dbmodels.ForeignKey(Test, db_column='test_idx') |
showard | a5288b4 | 2009-07-28 20:06:08 +0000 | [diff] [blame] | 207 | attribute = dbmodels.CharField(max_length=90) |
| 208 | value = dbmodels.CharField(blank=True, max_length=300) |
showard | f8b1904 | 2009-05-12 17:22:49 +0000 | [diff] [blame] | 209 | user_created = dbmodels.BooleanField(default=False) |
| 210 | |
| 211 | objects = model_logic.ExtendedManager() |
showard | 3544486 | 2008-08-07 22:35:30 +0000 | [diff] [blame] | 212 | |
| 213 | class Meta: |
| 214 | db_table = 'test_attributes' |
| 215 | |
| 216 | |
jadmanski | 430dca9 | 2008-12-16 20:56:53 +0000 | [diff] [blame] | 217 | class IterationAttribute(dbmodels.Model, model_logic.ModelExtensions): |
showard | f8b1904 | 2009-05-12 17:22:49 +0000 | [diff] [blame] | 218 | # this isn't really a primary key, but it's necessary to appease Django |
| 219 | # and is harmless as long as we're careful |
showard | e732ee7 | 2008-09-23 19:15:43 +0000 | [diff] [blame] | 220 | test = dbmodels.ForeignKey(Test, db_column='test_idx', primary_key=True) |
showard | 3544486 | 2008-08-07 22:35:30 +0000 | [diff] [blame] | 221 | iteration = dbmodels.IntegerField() |
showard | a5288b4 | 2009-07-28 20:06:08 +0000 | [diff] [blame] | 222 | attribute = dbmodels.CharField(max_length=90) |
| 223 | value = dbmodels.CharField(blank=True, max_length=300) |
showard | 3544486 | 2008-08-07 22:35:30 +0000 | [diff] [blame] | 224 | |
showard | f8b1904 | 2009-05-12 17:22:49 +0000 | [diff] [blame] | 225 | objects = model_logic.ExtendedManager() |
| 226 | |
showard | 3544486 | 2008-08-07 22:35:30 +0000 | [diff] [blame] | 227 | class Meta: |
| 228 | db_table = 'iteration_attributes' |
| 229 | |
| 230 | |
jadmanski | 430dca9 | 2008-12-16 20:56:53 +0000 | [diff] [blame] | 231 | class IterationResult(dbmodels.Model, model_logic.ModelExtensions): |
showard | f8b1904 | 2009-05-12 17:22:49 +0000 | [diff] [blame] | 232 | # see comment on IterationAttribute regarding primary_key=True |
jadmanski | 430dca9 | 2008-12-16 20:56:53 +0000 | [diff] [blame] | 233 | test = dbmodels.ForeignKey(Test, db_column='test_idx', primary_key=True) |
showard | 3544486 | 2008-08-07 22:35:30 +0000 | [diff] [blame] | 234 | iteration = dbmodels.IntegerField() |
showard | a5288b4 | 2009-07-28 20:06:08 +0000 | [diff] [blame] | 235 | attribute = dbmodels.CharField(max_length=90) |
| 236 | value = dbmodels.DecimalField(null=True, max_digits=12, decimal_places=31, |
| 237 | blank=True) |
showard | 3544486 | 2008-08-07 22:35:30 +0000 | [diff] [blame] | 238 | |
showard | f8b1904 | 2009-05-12 17:22:49 +0000 | [diff] [blame] | 239 | objects = model_logic.ExtendedManager() |
| 240 | |
showard | 3544486 | 2008-08-07 22:35:30 +0000 | [diff] [blame] | 241 | class Meta: |
| 242 | db_table = 'iteration_result' |
| 243 | |
| 244 | |
| 245 | class TestLabel(dbmodels.Model, model_logic.ModelExtensions): |
showard | a5288b4 | 2009-07-28 20:06:08 +0000 | [diff] [blame] | 246 | name = dbmodels.CharField(max_length=80, unique=True) |
showard | 3544486 | 2008-08-07 22:35:30 +0000 | [diff] [blame] | 247 | description = dbmodels.TextField(blank=True) |
showard | a5288b4 | 2009-07-28 20:06:08 +0000 | [diff] [blame] | 248 | tests = dbmodels.ManyToManyField(Test, blank=True) |
showard | 3544486 | 2008-08-07 22:35:30 +0000 | [diff] [blame] | 249 | |
| 250 | name_field = 'name' |
showard | f8b1904 | 2009-05-12 17:22:49 +0000 | [diff] [blame] | 251 | objects = model_logic.ExtendedManager() |
showard | 3544486 | 2008-08-07 22:35:30 +0000 | [diff] [blame] | 252 | |
| 253 | class Meta: |
| 254 | db_table = 'test_labels' |
| 255 | |
| 256 | |
| 257 | class SavedQuery(dbmodels.Model, model_logic.ModelExtensions): |
| 258 | # TODO: change this to foreign key once DBs are merged |
showard | a5288b4 | 2009-07-28 20:06:08 +0000 | [diff] [blame] | 259 | owner = dbmodels.CharField(max_length=80) |
| 260 | name = dbmodels.CharField(max_length=100) |
showard | 3544486 | 2008-08-07 22:35:30 +0000 | [diff] [blame] | 261 | url_token = dbmodels.TextField() |
| 262 | |
| 263 | class Meta: |
| 264 | db_table = 'saved_queries' |
| 265 | |
| 266 | |
showard | ce12f55 | 2008-09-19 00:48:59 +0000 | [diff] [blame] | 267 | class EmbeddedGraphingQuery(dbmodels.Model, model_logic.ModelExtensions): |
| 268 | url_token = dbmodels.TextField(null=False, blank=False) |
showard | a5288b4 | 2009-07-28 20:06:08 +0000 | [diff] [blame] | 269 | graph_type = dbmodels.CharField(max_length=16, null=False, blank=False) |
showard | ce12f55 | 2008-09-19 00:48:59 +0000 | [diff] [blame] | 270 | params = dbmodels.TextField(null=False, blank=False) |
| 271 | last_updated = dbmodels.DateTimeField(null=False, blank=False, |
| 272 | editable=False) |
| 273 | # refresh_time shows the time at which a thread is updating the cached |
| 274 | # image, or NULL if no one is updating the image. This is used so that only |
| 275 | # one thread is updating the cached image at a time (see |
| 276 | # graphing_utils.handle_plot_request) |
| 277 | refresh_time = dbmodels.DateTimeField(editable=False) |
| 278 | cached_png = dbmodels.TextField(editable=False) |
| 279 | |
| 280 | class Meta: |
| 281 | db_table = 'embedded_graphing_queries' |
| 282 | |
| 283 | |
showard | 3544486 | 2008-08-07 22:35:30 +0000 | [diff] [blame] | 284 | # views |
| 285 | |
| 286 | class TestViewManager(TempManager): |
showard | 3544486 | 2008-08-07 22:35:30 +0000 | [diff] [blame] | 287 | def get_query_set(self): |
| 288 | query = super(TestViewManager, self).get_query_set() |
showard | 5bf7c50 | 2008-08-20 01:22:22 +0000 | [diff] [blame] | 289 | |
showard | 3544486 | 2008-08-07 22:35:30 +0000 | [diff] [blame] | 290 | # add extra fields to selects, using the SQL itself as the "alias" |
| 291 | extra_select = dict((sql, sql) |
| 292 | for sql in self.model.extra_fields.iterkeys()) |
| 293 | return query.extra(select=extra_select) |
| 294 | |
| 295 | |
showard | f248952 | 2008-10-23 23:08:00 +0000 | [diff] [blame] | 296 | def _get_include_exclude_suffix(self, exclude): |
| 297 | if exclude: |
showard | 2aa318e | 2009-08-20 23:43:10 +0000 | [diff] [blame^] | 298 | return '_exclude' |
| 299 | return '_include' |
| 300 | |
| 301 | |
| 302 | def _add_attribute_join(self, query_set, join_condition, |
| 303 | suffix=None, exclude=False): |
| 304 | if suffix is None: |
| 305 | suffix = self._get_include_exclude_suffix(exclude) |
| 306 | return self.add_join(query_set, 'test_attributes', join_key='test_idx', |
| 307 | join_condition=join_condition, |
| 308 | suffix=suffix, exclude=exclude) |
| 309 | |
| 310 | |
| 311 | def _add_label_pivot_table_join(self, query_set, suffix, join_condition='', |
| 312 | exclude=False, force_left_join=False): |
| 313 | return self.add_join(query_set, 'test_labels_tests', join_key='test_id', |
| 314 | join_condition=join_condition, |
| 315 | suffix=suffix, exclude=exclude, |
| 316 | force_left_join=force_left_join) |
showard | f248952 | 2008-10-23 23:08:00 +0000 | [diff] [blame] | 317 | |
| 318 | |
showard | 64aeecd | 2008-09-19 21:32:58 +0000 | [diff] [blame] | 319 | def _add_label_joins(self, query_set, suffix=''): |
showard | 2aa318e | 2009-08-20 23:43:10 +0000 | [diff] [blame^] | 320 | query_set = self._add_label_pivot_table_join( |
| 321 | query_set, suffix=suffix, force_left_join=True) |
showard | d50ffb4 | 2008-09-04 02:47:45 +0000 | [diff] [blame] | 322 | |
showard | 2aa318e | 2009-08-20 23:43:10 +0000 | [diff] [blame^] | 323 | # since we're not joining from the original table, we can't use |
| 324 | # self.add_join() again |
showard | d50ffb4 | 2008-09-04 02:47:45 +0000 | [diff] [blame] | 325 | second_join_alias = 'test_labels' + suffix |
| 326 | second_join_condition = ('%s.id = %s.testlabel_id' % |
showard | 64aeecd | 2008-09-19 21:32:58 +0000 | [diff] [blame] | 327 | (second_join_alias, |
| 328 | 'test_labels_tests' + suffix)) |
| 329 | filter_object = self._CustomSqlQ() |
showard | d50ffb4 | 2008-09-04 02:47:45 +0000 | [diff] [blame] | 330 | filter_object.add_join('test_labels', |
| 331 | second_join_condition, |
showard | a5288b4 | 2009-07-28 20:06:08 +0000 | [diff] [blame] | 332 | query_set.query.LOUTER, |
showard | d50ffb4 | 2008-09-04 02:47:45 +0000 | [diff] [blame] | 333 | alias=second_join_alias) |
showard | a5288b4 | 2009-07-28 20:06:08 +0000 | [diff] [blame] | 334 | return self._add_customSqlQ(query_set, filter_object) |
showard | d50ffb4 | 2008-09-04 02:47:45 +0000 | [diff] [blame] | 335 | |
showard | 64aeecd | 2008-09-19 21:32:58 +0000 | [diff] [blame] | 336 | |
showard | d50ffb4 | 2008-09-04 02:47:45 +0000 | [diff] [blame] | 337 | def _get_label_ids_from_names(self, label_names): |
showard | 2aa318e | 2009-08-20 23:43:10 +0000 | [diff] [blame^] | 338 | assert label_names |
| 339 | label_ids = list( # listifying avoids a double query below |
| 340 | TestLabel.objects.filter(name__in=label_names).values('id')) |
| 341 | if len(label_ids) < len(set(label_names)): |
| 342 | raise ValueError('Not all labels found: %s' % |
| 343 | ', '.join(label_names)) |
| 344 | return [str(label['id']) for label in label_ids] |
| 345 | |
| 346 | |
| 347 | def _include_or_exclude_labels(self, query_set, label_names, exclude=False): |
| 348 | label_ids = self._get_label_ids_from_names(label_names) |
| 349 | suffix = self._get_include_exclude_suffix(exclude) |
| 350 | condition = ('test_labels_tests%s.testlabel_id IN (%s)' % |
| 351 | (suffix, ','.join(label_ids))) |
| 352 | return self._add_label_pivot_table_join(query_set, |
| 353 | join_condition=condition, |
| 354 | suffix=suffix, |
| 355 | exclude=exclude) |
showard | 0281350 | 2008-08-20 20:52:56 +0000 | [diff] [blame] | 356 | |
| 357 | |
showard | f248952 | 2008-10-23 23:08:00 +0000 | [diff] [blame] | 358 | def get_query_set_with_joins(self, filter_data, include_host_labels=False): |
showard | fc8c6ae | 2008-11-11 19:06:01 +0000 | [diff] [blame] | 359 | include_labels = filter_data.pop('include_labels', []) |
showard | d50ffb4 | 2008-09-04 02:47:45 +0000 | [diff] [blame] | 360 | exclude_labels = filter_data.pop('exclude_labels', []) |
showard | 3544486 | 2008-08-07 22:35:30 +0000 | [diff] [blame] | 361 | query_set = self.get_query_set() |
showard | d50ffb4 | 2008-09-04 02:47:45 +0000 | [diff] [blame] | 362 | joined = False |
showard | 2aa318e | 2009-08-20 23:43:10 +0000 | [diff] [blame^] | 363 | |
| 364 | # TODO: make this feature obsolete in favor of include_labels and |
| 365 | # exclude_labels |
showard | f248952 | 2008-10-23 23:08:00 +0000 | [diff] [blame] | 366 | extra_where = filter_data.get('extra_where', '') |
| 367 | if 'test_labels' in extra_where: |
showard | 0281350 | 2008-08-20 20:52:56 +0000 | [diff] [blame] | 368 | query_set = self._add_label_joins(query_set) |
showard | d50ffb4 | 2008-09-04 02:47:45 +0000 | [diff] [blame] | 369 | joined = True |
| 370 | |
showard | 2aa318e | 2009-08-20 23:43:10 +0000 | [diff] [blame^] | 371 | if include_labels: |
| 372 | query_set = self._include_or_exclude_labels(query_set, |
| 373 | include_labels) |
showard | fc8c6ae | 2008-11-11 19:06:01 +0000 | [diff] [blame] | 374 | joined = True |
showard | 2aa318e | 2009-08-20 23:43:10 +0000 | [diff] [blame^] | 375 | if exclude_labels: |
| 376 | query_set = self._include_or_exclude_labels(query_set, |
| 377 | exclude_labels, |
| 378 | exclude=True) |
showard | 64aeecd | 2008-09-19 21:32:58 +0000 | [diff] [blame] | 379 | joined = True |
| 380 | |
| 381 | include_attributes_where = filter_data.pop('include_attributes_where', |
| 382 | '') |
| 383 | exclude_attributes_where = filter_data.pop('exclude_attributes_where', |
| 384 | '') |
| 385 | if include_attributes_where: |
| 386 | query_set = self._add_attribute_join( |
showard | 2aa318e | 2009-08-20 23:43:10 +0000 | [diff] [blame^] | 387 | query_set, |
| 388 | join_condition=self.escape_user_sql(include_attributes_where)) |
showard | 64aeecd | 2008-09-19 21:32:58 +0000 | [diff] [blame] | 389 | joined = True |
| 390 | if exclude_attributes_where: |
| 391 | query_set = self._add_attribute_join( |
showard | 2aa318e | 2009-08-20 23:43:10 +0000 | [diff] [blame^] | 392 | query_set, |
| 393 | join_condition=self.escape_user_sql(exclude_attributes_where), |
showard | 64aeecd | 2008-09-19 21:32:58 +0000 | [diff] [blame] | 394 | exclude=True) |
| 395 | joined = True |
showard | d50ffb4 | 2008-09-04 02:47:45 +0000 | [diff] [blame] | 396 | |
| 397 | if not joined: |
showard | 3544486 | 2008-08-07 22:35:30 +0000 | [diff] [blame] | 398 | filter_data['no_distinct'] = True |
showard | d50ffb4 | 2008-09-04 02:47:45 +0000 | [diff] [blame] | 399 | |
showard | 2aa318e | 2009-08-20 23:43:10 +0000 | [diff] [blame^] | 400 | # TODO: make test_attributes_host_labels obsolete too |
showard | f248952 | 2008-10-23 23:08:00 +0000 | [diff] [blame] | 401 | if include_host_labels or 'test_attributes_host_labels' in extra_where: |
| 402 | query_set = self._add_attribute_join( |
| 403 | query_set, suffix='_host_labels', |
| 404 | join_condition='test_attributes_host_labels.attribute = ' |
| 405 | '"host-labels"') |
| 406 | |
showard | 3544486 | 2008-08-07 22:35:30 +0000 | [diff] [blame] | 407 | return query_set |
| 408 | |
| 409 | |
showard | 0281350 | 2008-08-20 20:52:56 +0000 | [diff] [blame] | 410 | def query_test_ids(self, filter_data): |
| 411 | dicts = self.model.query_objects(filter_data).values('test_idx') |
| 412 | return [item['test_idx'] for item in dicts] |
| 413 | |
| 414 | |
showard | 0281350 | 2008-08-20 20:52:56 +0000 | [diff] [blame] | 415 | def query_test_label_ids(self, filter_data): |
showard | d50ffb4 | 2008-09-04 02:47:45 +0000 | [diff] [blame] | 416 | query_set = self.model.query_objects(filter_data) |
| 417 | query_set = self._add_label_joins(query_set, suffix='_list') |
| 418 | rows = self._custom_select_query(query_set, ['test_labels_list.id']) |
| 419 | return [row[0] for row in rows if row[0] is not None] |
showard | 0281350 | 2008-08-20 20:52:56 +0000 | [diff] [blame] | 420 | |
| 421 | |
showard | eaccf8f | 2009-04-16 03:11:33 +0000 | [diff] [blame] | 422 | def escape_user_sql(self, sql): |
| 423 | sql = super(TestViewManager, self).escape_user_sql(sql) |
| 424 | return sql.replace('test_idx', self.get_key_on_this_table('test_idx')) |
| 425 | |
| 426 | |
showard | 3544486 | 2008-08-07 22:35:30 +0000 | [diff] [blame] | 427 | class TestView(dbmodels.Model, model_logic.ModelExtensions): |
| 428 | extra_fields = { |
showard | f4c702e | 2009-07-08 21:14:27 +0000 | [diff] [blame] | 429 | 'DATE(job_queued_time)': 'job queued day', |
| 430 | 'DATE(test_finished_time)': 'test finished day', |
showard | 3544486 | 2008-08-07 22:35:30 +0000 | [diff] [blame] | 431 | } |
| 432 | |
| 433 | group_fields = [ |
showard | f4c702e | 2009-07-08 21:14:27 +0000 | [diff] [blame] | 434 | 'test_name', |
| 435 | 'status', |
| 436 | 'kernel', |
| 437 | 'hostname', |
| 438 | 'job_tag', |
| 439 | 'job_name', |
| 440 | 'platform', |
| 441 | 'reason', |
| 442 | 'job_owner', |
| 443 | 'job_queued_time', |
| 444 | 'DATE(job_queued_time)', |
| 445 | 'test_started_time', |
| 446 | 'test_finished_time', |
| 447 | 'DATE(test_finished_time)', |
showard | 3544486 | 2008-08-07 22:35:30 +0000 | [diff] [blame] | 448 | ] |
| 449 | |
| 450 | test_idx = dbmodels.IntegerField('test index', primary_key=True) |
| 451 | job_idx = dbmodels.IntegerField('job index', null=True, blank=True) |
showard | a5288b4 | 2009-07-28 20:06:08 +0000 | [diff] [blame] | 452 | test_name = dbmodels.CharField(blank=True, max_length=90) |
| 453 | subdir = dbmodels.CharField('subdirectory', blank=True, max_length=180) |
showard | 3544486 | 2008-08-07 22:35:30 +0000 | [diff] [blame] | 454 | kernel_idx = dbmodels.IntegerField('kernel index') |
| 455 | status_idx = dbmodels.IntegerField('status index') |
showard | a5288b4 | 2009-07-28 20:06:08 +0000 | [diff] [blame] | 456 | reason = dbmodels.CharField(blank=True, max_length=3072) |
showard | 3544486 | 2008-08-07 22:35:30 +0000 | [diff] [blame] | 457 | machine_idx = dbmodels.IntegerField('host index') |
| 458 | test_started_time = dbmodels.DateTimeField(null=True, blank=True) |
| 459 | test_finished_time = dbmodels.DateTimeField(null=True, blank=True) |
showard | a5288b4 | 2009-07-28 20:06:08 +0000 | [diff] [blame] | 460 | job_tag = dbmodels.CharField(blank=True, max_length=300) |
| 461 | job_name = dbmodels.CharField(blank=True, max_length=300) |
| 462 | job_owner = dbmodels.CharField('owner', blank=True, max_length=240) |
showard | 3544486 | 2008-08-07 22:35:30 +0000 | [diff] [blame] | 463 | job_queued_time = dbmodels.DateTimeField(null=True, blank=True) |
| 464 | job_started_time = dbmodels.DateTimeField(null=True, blank=True) |
| 465 | job_finished_time = dbmodels.DateTimeField(null=True, blank=True) |
showard | a5288b4 | 2009-07-28 20:06:08 +0000 | [diff] [blame] | 466 | hostname = dbmodels.CharField(blank=True, max_length=300) |
| 467 | platform = dbmodels.CharField(blank=True, max_length=240) |
| 468 | machine_owner = dbmodels.CharField(blank=True, max_length=240) |
| 469 | kernel_hash = dbmodels.CharField(blank=True, max_length=105) |
| 470 | kernel_base = dbmodels.CharField(blank=True, max_length=90) |
| 471 | kernel = dbmodels.CharField(blank=True, max_length=300) |
| 472 | status = dbmodels.CharField(blank=True, max_length=30) |
showard | 3544486 | 2008-08-07 22:35:30 +0000 | [diff] [blame] | 473 | |
| 474 | objects = TestViewManager() |
| 475 | |
| 476 | def save(self): |
| 477 | raise NotImplementedError('TestView is read-only') |
| 478 | |
| 479 | |
| 480 | def delete(self): |
| 481 | raise NotImplementedError('TestView is read-only') |
| 482 | |
| 483 | |
| 484 | @classmethod |
| 485 | def query_objects(cls, filter_data, initial_query=None): |
| 486 | if initial_query is None: |
showard | 64aeecd | 2008-09-19 21:32:58 +0000 | [diff] [blame] | 487 | initial_query = cls.objects.get_query_set_with_joins(filter_data) |
showard | 3544486 | 2008-08-07 22:35:30 +0000 | [diff] [blame] | 488 | return super(TestView, cls).query_objects(filter_data, |
| 489 | initial_query=initial_query) |
| 490 | |
| 491 | |
| 492 | @classmethod |
showard | e732ee7 | 2008-09-23 19:15:43 +0000 | [diff] [blame] | 493 | def list_objects(cls, filter_data, initial_query=None, fields=None): |
| 494 | # include extra fields |
| 495 | if fields is None: |
| 496 | fields = cls.get_field_dict().keys() + cls.extra_fields.keys() |
| 497 | return super(TestView, cls).list_objects(filter_data, initial_query, |
| 498 | fields) |
showard | 3544486 | 2008-08-07 22:35:30 +0000 | [diff] [blame] | 499 | |
| 500 | |
| 501 | class Meta: |
| 502 | db_table = 'test_view_2' |