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"]})
