def _do_export(self, tables): # add data from each exported measurement; already sorted by protocol measures = self.selection.measurements.select_related( # add proteinidentifier so export does not repeatedly query for protein-specific stuff 'measurement_type__proteinidentifier', 'x_units', 'y_units', 'update_ref__mod_by', 'experimenter', 'assay__experimenter', 'assay__protocol', 'assay__line__contact', 'assay__line__experimenter', 'assay__line__study__contact', ).annotate( # eliminate some subqueries and/or repeated queries by collecting values in arrays strain_names=ArrayAgg('assay__line__strains__name', distinct=True), cs_names=ArrayAgg('assay__line__carbon_source__name', distinct=True), vids=ArrayAgg('measurementvalue'), # aggregating arrays instead of values, use JSONB vxs=JSONBAgg('measurementvalue__x'), vys=JSONBAgg('measurementvalue__y'), ) for measurement in measures: assay = measurement.assay protocol = assay.protocol line = assay.line if self.options.line_section: line_only = [models.Line, models.Study, ] other_only = [models.Assay, models.Measurement, models.Protocol, ] # add row to line table w/ Study, Line columns only if line.id not in tables['line']: row = self._output_row_with_measure(measurement, models=line_only) tables['line'][line.id] = row # create row for protocol/all table w/ Protocol, Assay, Measurement columns only row = self._output_row_with_measure(measurement, models=other_only) else: # create row for protocol/all table row = self._output_row_with_measure(measurement) table, table_key = self._init_tables_for_protocol(tables, protocol) values = sorted( zip(measurement.vids, measurement.vxs, measurement.vys), key=lambda a: a[1][0], ) if self.options.layout == ExportOption.DATA_COLUMN_BY_POINT: for value in values: arow = row[:] arow.append(value_str(value[1])) # x-values arow.append(value_str(value[2])) # y-values table[value[0]] = arow # value IDs else: # keep track of all x values encountered in the table xx = self._x_values[table_key] = self._x_values.get(table_key, {}) # do value_str to the float-casted version of x to eliminate 0-padding xx.update({value_str(v[1]): v[1] for v in values}) squashed = {value_str(v[1]): value_str(v[2]) for v in values} row.append(squashed) table[measurement.id] = row
def test_convert_value_deprecation(self): AggregateTestModel.objects.all().delete() queryset = AggregateTestModel.objects.all() with self.assertWarnsMessage( RemovedInDjango50Warning, ArrayAgg.deprecation_msg ): queryset.aggregate(aggregation=ArrayAgg("boolean_field")) with self.assertWarnsMessage( RemovedInDjango50Warning, JSONBAgg.deprecation_msg ): queryset.aggregate(aggregation=JSONBAgg("integer_field")) with self.assertWarnsMessage( RemovedInDjango50Warning, StringAgg.deprecation_msg ): queryset.aggregate(aggregation=StringAgg("char_field", delimiter=";")) # No warnings raised if default argument provided. self.assertEqual( queryset.aggregate(aggregation=ArrayAgg("boolean_field", default=None)), {"aggregation": None}, ) self.assertEqual( queryset.aggregate(aggregation=JSONBAgg("integer_field", default=None)), {"aggregation": None}, ) self.assertEqual( queryset.aggregate( aggregation=StringAgg("char_field", delimiter=";", default=None), ), {"aggregation": None}, ) self.assertEqual( queryset.aggregate( aggregation=ArrayAgg("boolean_field", default=Value([])) ), {"aggregation": []}, ) self.assertEqual( queryset.aggregate( aggregation=JSONBAgg("integer_field", default=Value("[]")) ), {"aggregation": []}, ) self.assertEqual( queryset.aggregate( aggregation=StringAgg("char_field", delimiter=";", default=Value("")), ), {"aggregation": ""}, )
def test_empty_result_set(self): AggregateTestModel.objects.all().delete() tests = [ (ArrayAgg("char_field"), []), (ArrayAgg("integer_field"), []), (ArrayAgg("boolean_field"), []), (BitAnd("integer_field"), None), (BitOr("integer_field"), None), (BoolAnd("boolean_field"), None), (BoolOr("boolean_field"), None), (JSONBAgg("integer_field"), []), (StringAgg("char_field", delimiter=";"), ""), ] if connection.features.has_bit_xor: tests.append((BitXor("integer_field"), None)) for aggregation, expected_result in tests: with self.subTest(aggregation=aggregation): # Empty result with non-execution optimization. with self.assertNumQueries(0): values = AggregateTestModel.objects.none().aggregate( aggregation=aggregation, ) self.assertEqual(values, {"aggregation": expected_result}) # Empty result when query must be executed. with self.assertNumQueries(1): values = AggregateTestModel.objects.aggregate( aggregation=aggregation, ) self.assertEqual(values, {"aggregation": expected_result})
def test_jsonb_agg_jsonfield_ordering(self): values = AggregateTestModel.objects.aggregate(jsonbagg=JSONBAgg( KeyTransform('lang', 'json_field'), filter=Q(json_field__lang__isnull=False), ordering=KeyTransform('lang', 'json_field'), ), ) self.assertEqual(values, {'jsonbagg': ['en', 'pl']})
def test_default_argument(self): AggregateTestModel.objects.all().delete() tests = [ (ArrayAgg("char_field", default=["<empty>"]), ["<empty>"]), (ArrayAgg("integer_field", default=[0]), [0]), (ArrayAgg("boolean_field", default=[False]), [False]), (BitAnd("integer_field", default=0), 0), (BitOr("integer_field", default=0), 0), (BoolAnd("boolean_field", default=False), False), (BoolOr("boolean_field", default=False), False), (JSONBAgg("integer_field", default=Value('["<empty>"]')), ["<empty>"]), ( StringAgg("char_field", delimiter=";", default=Value("<empty>")), "<empty>", ), ] if connection.features.has_bit_xor: tests.append((BitXor("integer_field", default=0), 0)) for aggregation, expected_result in tests: with self.subTest(aggregation=aggregation): # Empty result with non-execution optimization. with self.assertNumQueries(0): values = AggregateTestModel.objects.none().aggregate( aggregation=aggregation, ) self.assertEqual(values, {"aggregation": expected_result}) # Empty result when query must be executed. with self.assertNumQueries(1): values = AggregateTestModel.objects.aggregate( aggregation=aggregation, ) self.assertEqual(values, {"aggregation": expected_result})
def test_default_argument(self): AggregateTestModel.objects.all().delete() tests = [ (ArrayAgg('char_field', default=['<empty>']), ['<empty>']), (ArrayAgg('integer_field', default=[0]), [0]), (ArrayAgg('boolean_field', default=[False]), [False]), (BitAnd('integer_field', default=0), 0), (BitOr('integer_field', default=0), 0), (BoolAnd('boolean_field', default=False), False), (BoolOr('boolean_field', default=False), False), (JSONBAgg('integer_field', default=Value('["<empty>"]')), ['<empty>']), (StringAgg('char_field', delimiter=';', default=Value('<empty>')), '<empty>'), ] for aggregation, expected_result in tests: with self.subTest(aggregation=aggregation): # Empty result with non-execution optimization. with self.assertNumQueries(0): values = AggregateTestModel.objects.none().aggregate( aggregation=aggregation, ) self.assertEqual(values, {'aggregation': expected_result}) # Empty result when query must be executed. with self.assertNumQueries(1): values = AggregateTestModel.objects.aggregate( aggregation=aggregation, ) self.assertEqual(values, {'aggregation': expected_result})
def test_empty_result_set(self): AggregateTestModel.objects.all().delete() tests = [ (ArrayAgg('char_field'), []), (ArrayAgg('integer_field'), []), (ArrayAgg('boolean_field'), []), (BitAnd('integer_field'), None), (BitOr('integer_field'), None), (BoolAnd('boolean_field'), None), (BoolOr('boolean_field'), None), (JSONBAgg('integer_field'), []), (StringAgg('char_field', delimiter=';'), ''), ] for aggregation, expected_result in tests: with self.subTest(aggregation=aggregation): # Empty result with non-execution optimization. with self.assertNumQueries(0): values = AggregateTestModel.objects.none().aggregate( aggregation=aggregation, ) self.assertEqual(values, {'aggregation': expected_result}) # Empty result when query must be executed. with self.assertNumQueries(1): values = AggregateTestModel.objects.aggregate( aggregation=aggregation, ) self.assertEqual(values, {'aggregation': expected_result})
def test_jsonb_agg_key_index_transforms(self): room101 = Room.objects.create(number=101) room102 = Room.objects.create(number=102) datetimes = [ timezone.datetime(2018, 6, 20), timezone.datetime(2018, 6, 24), timezone.datetime(2018, 6, 28), ] HotelReservation.objects.create( datespan=(datetimes[0].date(), datetimes[1].date()), start=datetimes[0], end=datetimes[1], room=room102, requirements={ 'double_bed': True, 'parking': True }, ) HotelReservation.objects.create( datespan=(datetimes[1].date(), datetimes[2].date()), start=datetimes[1], end=datetimes[2], room=room102, requirements={ 'double_bed': False, 'sea_view': True, 'parking': False }, ) HotelReservation.objects.create( datespan=(datetimes[0].date(), datetimes[2].date()), start=datetimes[0], end=datetimes[2], room=room101, requirements={'sea_view': False}, ) values = Room.objects.annotate(requirements=JSONBAgg( 'hotelreservation__requirements', ordering='-hotelreservation__start', )).filter(requirements__0__sea_view=True).values( 'number', 'requirements') self.assertSequenceEqual(values, [ { 'number': 102, 'requirements': [ { 'double_bed': False, 'sea_view': True, 'parking': False }, { 'double_bed': True, 'parking': True }, ] }, ])
def test_jsonb_agg_jsonfield_ordering(self): values = AggregateTestModel.objects.aggregate( jsonbagg=JSONBAgg( KeyTransform("lang", "json_field"), filter=Q(json_field__lang__isnull=False), ordering=KeyTransform("lang", "json_field"), ), ) self.assertEqual(values, {"jsonbagg": ["en", "pl"]})
def test_jsonb_agg_booleanfield_ordering(self): ordering_test_cases = ( (F("boolean_field").asc(), [False, False, True, True]), (F("boolean_field").desc(), [True, True, False, False]), (F("boolean_field"), [False, False, True, True]), ) for ordering, expected_output in ordering_test_cases: with self.subTest(ordering=ordering, expected_output=expected_output): values = AggregateTestModel.objects.aggregate( jsonbagg=JSONBAgg("boolean_field", ordering=ordering), ) self.assertEqual(values, {"jsonbagg": expected_output})
def test_jsonb_agg_key_index_transforms(self): room101 = Room.objects.create(number=101) room102 = Room.objects.create(number=102) datetimes = [ timezone.datetime(2018, 6, 20), timezone.datetime(2018, 6, 24), timezone.datetime(2018, 6, 28), ] HotelReservation.objects.create( datespan=(datetimes[0].date(), datetimes[1].date()), start=datetimes[0], end=datetimes[1], room=room102, requirements={"double_bed": True, "parking": True}, ) HotelReservation.objects.create( datespan=(datetimes[1].date(), datetimes[2].date()), start=datetimes[1], end=datetimes[2], room=room102, requirements={"double_bed": False, "sea_view": True, "parking": False}, ) HotelReservation.objects.create( datespan=(datetimes[0].date(), datetimes[2].date()), start=datetimes[0], end=datetimes[2], room=room101, requirements={"sea_view": False}, ) values = ( Room.objects.annotate( requirements=JSONBAgg( "hotelreservation__requirements", ordering="-hotelreservation__start", ) ) .filter(requirements__0__sea_view=True) .values("number", "requirements") ) self.assertSequenceEqual( values, [ { "number": 102, "requirements": [ {"double_bed": False, "sea_view": True, "parking": False}, {"double_bed": True, "parking": True}, ], }, ], )
def get_for_object(self, obj, aggregate_data=False): """ Return all applicable ConfigContexts for a given object. Only active ConfigContexts will be included. Args: aggregate_data: If True, use the JSONBAgg aggregate function to return only the list of JSON data objects """ # `device_role` for Device; `role` for VirtualMachine role = getattr(obj, 'device_role', None) or obj.role # Device type assignment is relevant only for Devices device_type = getattr(obj, 'device_type', None) # Cluster assignment is relevant only for VirtualMachines cluster = getattr(obj, 'cluster', None) cluster_group = getattr(cluster, 'group', None) # Get the group of the assigned tenant, if any tenant_group = obj.tenant.group if obj.tenant else None # Match against the directly assigned region as well as any parent regions. region = getattr(obj.site, 'region', None) regions = region.get_ancestors(include_self=True) if region else [] # Match against the directly assigned site group as well as any parent site groups. sitegroup = getattr(obj.site, 'group', None) sitegroups = sitegroup.get_ancestors(include_self=True) if sitegroup else [] queryset = self.filter( Q(regions__in=regions) | Q(regions=None), Q(site_groups__in=sitegroups) | Q(site_groups=None), Q(sites=obj.site) | Q(sites=None), Q(device_types=device_type) | Q(device_types=None), Q(roles=role) | Q(roles=None), Q(platforms=obj.platform) | Q(platforms=None), Q(cluster_groups=cluster_group) | Q(cluster_groups=None), Q(clusters=cluster) | Q(clusters=None), Q(tenant_groups=tenant_group) | Q(tenant_groups=None), Q(tenants=obj.tenant) | Q(tenants=None), Q(tags__slug__in=obj.tags.slugs()) | Q(tags=None), is_active=True, ).order_by('weight', 'name').distinct() if aggregate_data: return queryset.aggregate( config_context_data=JSONBAgg('data', ordering=['weight', 'name']) )['config_context_data'] return queryset
def test_jsonb_agg_charfield_ordering(self): ordering_test_cases = ( (F("char_field").desc(), ["Foo4", "Foo3", "Foo2", "Foo1"]), (F("char_field").asc(), ["Foo1", "Foo2", "Foo3", "Foo4"]), (F("char_field"), ["Foo1", "Foo2", "Foo3", "Foo4"]), ("char_field", ["Foo1", "Foo2", "Foo3", "Foo4"]), ("-char_field", ["Foo4", "Foo3", "Foo2", "Foo1"]), (Concat("char_field", Value("@")), ["Foo1", "Foo2", "Foo3", "Foo4"]), (Concat("char_field", Value("@")).desc(), ["Foo4", "Foo3", "Foo2", "Foo1"]), ) for ordering, expected_output in ordering_test_cases: with self.subTest(ordering=ordering, expected_output=expected_output): values = AggregateTestModel.objects.aggregate( jsonbagg=JSONBAgg("char_field", ordering=ordering), ) self.assertEqual(values, {"jsonbagg": expected_output})
def test_jsonb_agg_charfield_ordering(self): ordering_test_cases = ( (F('char_field').desc(), ['Foo4', 'Foo3', 'Foo2', 'Foo1']), (F('char_field').asc(), ['Foo1', 'Foo2', 'Foo3', 'Foo4']), (F('char_field'), ['Foo1', 'Foo2', 'Foo3', 'Foo4']), ('char_field', ['Foo1', 'Foo2', 'Foo3', 'Foo4']), ('-char_field', ['Foo4', 'Foo3', 'Foo2', 'Foo1']), (Concat('char_field', Value('@')), ['Foo1', 'Foo2', 'Foo3', 'Foo4']), (Concat('char_field', Value('@')).desc(), ['Foo4', 'Foo3', 'Foo2', 'Foo1']), ) for ordering, expected_output in ordering_test_cases: with self.subTest(ordering=ordering, expected_output=expected_output): values = AggregateTestModel.objects.aggregate( jsonbagg=JSONBAgg('char_field', ordering=ordering), ) self.assertEqual(values, {'jsonbagg': expected_output})
def test_json_agg_integerfield_ordering(self): values = AggregateTestModel.objects.aggregate(jsonagg=JSONBAgg( 'integer_field', ordering=F('integer_field').desc()), ) self.assertEqual(values, {'jsonagg': [2, 1, 0, 0]})
def test_jsonb_agg_distinct_true(self): values = AggregateTestModel.objects.aggregate( jsonbagg=JSONBAgg("char_field", distinct=True), ) self.assertEqual(sorted(values["jsonbagg"]), ["Bar", "Foo"])
def test_jsonb_agg_integerfield_ordering(self): values = AggregateTestModel.objects.aggregate( jsonbagg=JSONBAgg("integer_field", ordering=F("integer_field").desc()), ) self.assertEqual(values, {"jsonbagg": [2, 1, 0, 0]})
def test_json_agg(self): values = AggregateTestModel.objects.aggregate( jsonagg=JSONBAgg('char_field')) self.assertEqual(values, {'jsonagg': ['Foo1', 'Foo2', 'Foo3', 'Foo4']})
def test_jsonb_agg_distinct_true(self): values = AggregateTestModel.objects.aggregate( jsonbagg=JSONBAgg('char_field', distinct=True), ) self.assertEqual(sorted(values['jsonbagg']), ['Bar', 'Foo'])
def test_json_agg_empty(self): values = AggregateTestModel.objects.none().aggregate( jsonagg=JSONBAgg('integer_field')) self.assertEqual(values, json.loads('{"jsonagg": []}'))
def test_jsonb_agg(self): values = AggregateTestModel.objects.aggregate(jsonbagg=JSONBAgg("char_field")) self.assertEqual(values, {"jsonbagg": ["Foo1", "Foo2", "Foo4", "Foo3"]})