def get_udfc_search_fields(self, user):
from treemap.models import InstanceUser
from treemap.udf import UDFModel
from treemap.util import to_object_name, leaf_models_of_class
from treemap.lib.perms import udf_write_level, READ, WRITE
try:
iu = self.instanceuser_set.get(user__pk=user.pk)
except InstanceUser.DoesNotExist:
iu = None
data = DotDict({'models': set(), 'udfc': {}})
for clz in (leaf_models_of_class(UDFModel)):
model_name = clz.__name__
items = (
(k, v) for k, v
in getattr(clz, 'collection_udf_settings', {}).iteritems())
for k, v in items:
udfds = (u for u in udf_defs(self, model_name) if u.name == k)
for udfd in udfds:
if udf_write_level(iu, udfd) in (READ, WRITE):
nest_path = ('udfc.%s.models.%s' %
(to_object_name(k),
to_object_name(model_name)))
data[nest_path] = {
'udfd': udfd,
'fields': udfd.datatype_dict[0]['choices']
}
p = 'udfc.%s.' % to_object_name(k)
data[p + 'action_verb'] = v['action_verb']
data[p + 'range_field_key'] = v['range_field_key']
data[p + 'action_field_key'] = v['action_field_key']
data['models'] |= {clz}
return data
def get_udfc_search_fields(self, user):
from treemap.models import InstanceUser
from treemap.udf import UDFModel
from treemap.util import to_object_name, leaf_models_of_class
from treemap.lib.perms import udf_write_level, READ, WRITE
try:
iu = self.instanceuser_set.get(user__pk=user.pk)
except InstanceUser.DoesNotExist:
iu = None
data = DotDict({"models": set(), "udfc": {}})
for clz in leaf_models_of_class(UDFModel):
model_name = clz.__name__
for k, v in clz.collection_udf_settings.items():
udfds = (u for u in udf_defs(self, model_name) if u.name == k)
for udfd in udfds:
if udf_write_level(iu, udfd) in (READ, WRITE):
_base_nest_path = "udfc.%s." % (to_object_name(k))
ids_nest_path = "%sids.%s" % (_base_nest_path, to_object_name(model_name))
models_nest_path = "%smodels.%s" % (_base_nest_path, to_object_name(model_name))
data[ids_nest_path] = udfd.pk
data[models_nest_path] = {"udfd": udfd, "fields": udfd.datatype_dict[0]["choices"]}
p = "udfc.%s." % to_object_name(k)
data[p + "action_verb"] = v["action_verb"]
data[p + "range_field_key"] = v["range_field_key"]
data[p + "action_field_key"] = v["action_field_key"]
data["models"] |= {clz}
return data
def add_map_info_to_context(context, instance):
all_polygon_types = {
c.map_feature_type
for c in leaf_models_of_class(PolygonalMapFeature)
}
my_polygon_types = set(instance.map_feature_types) & all_polygon_types
context['has_polygons'] = len(my_polygon_types) > 0
context['has_boundaries'] = instance.boundaries.exists()
def _make_permissions(field_permission):
def make_model_perms(Model):
return tuple((Model._meta.object_name, field_name, field_permission)
for field_name in Model().tracked_fields)
models = leaf_models_of_class(Authorizable)
model_permissions = [make_model_perms(Model) for Model in models]
permissions = sum(model_permissions, ()) # flatten
return permissions
def editable_udf_models(self):
from treemap.models import Tree, Plot
from treemap.udf import UDFModel
from treemap.util import leaf_models_of_class
models = {clz for clz in leaf_models_of_class(UDFModel)
if clz.__name__ in self.map_feature_types
and getattr(clz, 'is_editable', False)}
if Plot in models:
models |= {Tree}
return models
def _make_permissions(field_permission):
def make_model_perms(Model):
return tuple(
(Model._meta.object_name, field_name, field_permission)
for field_name in Model().tracked_fields)
models = leaf_models_of_class(Authorizable)
model_permissions = [make_model_perms(Model) for Model in models]
permissions = sum(model_permissions, ()) # flatten
return permissions
def make_tweaker_role(instance, name='tweaker'):
"""
The tweaker role has permission to modify all model fields
directly for all models under test, but not create or delete.
"""
permissions = []
models = leaf_models_of_class(Authorizable)
for Model in models:
permissions += [(Model.__name__, fieldname,
FieldPermission.WRITE_DIRECTLY)
for fieldname in Model.requires_authorization]
return _make_loaded_role(instance, name, FieldPermission.NONE, permissions)
def make_tweaker_role(instance, name='tweaker'):
"""
The tweaker role has permission to modify all model fields
directly for all models under test, but not create or delete.
"""
permissions = []
models = leaf_models_of_class(Authorizable)
for Model in models:
permissions += [
(Model.__name__, fieldname,
FieldPermission.WRITE_DIRECTLY)
for fieldname in Model.requires_authorization]
return _make_loaded_role(instance, name, FieldPermission.NONE,
permissions)
def _get_replaceable_models(instance):
all_classes = leaf_models_of_class(MapFeature) - {Plot}
root_classes = {Cls for Cls in all_classes
if not Cls.__name__.endswith('LA')}
la_classes = {Cls for Cls in all_classes
if Cls.__name__.endswith('LA')}
if instance.url_name == 'latreemap':
classes = la_classes | {Cls for Cls in root_classes
if not
{LACls for LACls in la_classes
if LACls.__name__.startswith(Cls.__name__)}}
else:
classes = root_classes
return sorted(classes, key=lambda Cls: Cls.terminology()['singular'])
def editable_udf_models(self):
from treemap.plugin import feature_enabled
from treemap.models import Tree, Plot
from treemap.udf import UDFModel
from treemap.util import leaf_models_of_class
gsi_enabled = feature_enabled(self, 'green_infrastructure')
core_models = {Tree, Plot}
gsi_models = {clz for clz in leaf_models_of_class(UDFModel)
if gsi_enabled
and clz.__name__ in self.map_feature_types
and getattr(clz, 'is_editable', False)
and clz not in core_models}
all_models = core_models | gsi_models
return {'core': core_models, 'gsi': gsi_models, 'all': all_models}
def editable_udf_models(self):
from treemap.plugin import feature_enabled
from treemap.models import Tree, Plot
from treemap.udf import UDFModel
from treemap.util import leaf_models_of_class
gsi_enabled = feature_enabled(self, 'green_infrastructure')
core_models = {Tree, Plot}
gsi_models = {clz for clz in leaf_models_of_class(UDFModel)
if gsi_enabled
and clz.__name__ in self.map_feature_types
and getattr(clz, 'is_editable', False)
and clz not in core_models}
all_models = core_models | gsi_models
return {'core': core_models, 'gsi': gsi_models, 'all': all_models}
def make_officer_role(instance):
"""
The officer role has permission to modify only a few fields,
and only a few models under test, but the officer is permitted to
modify them directly without moderation.
"""
permissions = (
('Plot', 'length', FieldPermission.WRITE_DIRECTLY),
('RainBarrel', 'capacity', FieldPermission.WRITE_DIRECTLY),
('Tree', 'diameter', FieldPermission.WRITE_DIRECTLY),
('Tree', 'height', FieldPermission.WRITE_DIRECTLY))
officer = _make_loaded_role(instance, 'officer', FieldPermission.NONE,
permissions)
models = [Model for Model in leaf_models_of_class(Authorizable)
if Model.__name__ in {'Plot', 'RainBarrel', 'Tree'}]
officer.instance_permissions.add(*Role.model_permissions(models))
officer.save()
return officer
def _get_replaceable_models(instance):
all_classes = leaf_models_of_class(MapFeature) - {Plot}
root_classes = {
Cls
for Cls in all_classes if not Cls.__name__.endswith('LA')
}
la_classes = {Cls for Cls in all_classes if Cls.__name__.endswith('LA')}
if instance.url_name == 'latreemap':
classes = la_classes | {
Cls
for Cls in root_classes if not {
LACls
for LACls in la_classes
if LACls.__name__.startswith(Cls.__name__)
}
}
else:
classes = root_classes
return sorted(classes, key=lambda Cls: Cls.terminology()['singular'])
def make_conjurer_role(instance):
"""
The conjurer role has permission to create and delete all models
under test and their related photo types,
but limited permission to read or write fields in them.
"""
permissions = (
('Plot', 'length', FieldPermission.WRITE_DIRECTLY),
('Tree', 'height', FieldPermission.WRITE_DIRECTLY))
conjurer = _make_loaded_role(instance, 'conjurer', FieldPermission.NONE,
permissions)
models = [Model for Model in leaf_models_of_class(Authorizable)
if Model.__name__ in {'Plot', 'RainBarrel', 'Tree'}]
ThroughModel = Role.instance_permissions.through
model_permissions = Role.model_permissions(models)
role_perms = [ThroughModel(role_id=conjurer.id, permission_id=perm.id)
for perm in model_permissions]
ThroughModel.objects.bulk_create(role_perms)
return conjurer
def get_udfc_search_fields(instance, user):
from treemap.models import InstanceUser
from treemap.udf import UDFModel
from treemap.util import to_object_name, leaf_models_of_class
from treemap.lib.perms import udf_write_level, READ, WRITE
try:
iu = instance.instanceuser_set.get(user__pk=user.pk)
except InstanceUser.DoesNotExist:
iu = None
data = DotDict({'models': set(), 'udfc': {}})
for clz in leaf_models_of_class(UDFModel):
model_name = clz.__name__
if model_name not in ['Tree'] + instance.map_feature_types:
continue
for k, v in clz.collection_udf_settings.items():
udfds = (u for u in udf_defs(instance, model_name) if u.name == k)
for udfd in udfds:
if udf_write_level(iu, udfd) in (READ, WRITE):
_base_nest_path = 'udfc.%s.' % (to_object_name(k))
ids_nest_path = (
'%sids.%s' %
(_base_nest_path, to_object_name(model_name)))
models_nest_path = (
'%smodels.%s' %
(_base_nest_path, to_object_name(model_name)))
data[ids_nest_path] = udfd.pk
data[models_nest_path] = {
'udfd': udfd,
'fields': udfd.datatype_dict[0]['choices']
}
p = 'udfc.%s.' % to_object_name(k)
data[p + 'action_verb'] = v['action_verb']
data[p + 'range_field_key'] = v['range_field_key']
data[p + 'action_field_key'] = v['action_field_key']
data['models'] |= {clz}
return data
def get_udfc_search_fields(instance, user):
from treemap.models import InstanceUser
from treemap.udf import UDFModel
from treemap.util import to_object_name, leaf_models_of_class
from treemap.lib.perms import udf_write_level, READ, WRITE
try:
iu = instance.instanceuser_set.get(user__pk=user.pk)
except InstanceUser.DoesNotExist:
iu = None
data = DotDict({'models': set(), 'udfc': {}})
for clz in leaf_models_of_class(UDFModel):
model_name = clz.__name__
if model_name not in ['Tree'] + instance.map_feature_types:
continue
for k, v in clz.collection_udf_settings.items():
udfds = (u for u in udf_defs(instance, model_name) if u.name == k)
for udfd in udfds:
if udf_write_level(iu, udfd) in (READ, WRITE):
_base_nest_path = 'udfc.%s.' % (to_object_name(k))
ids_nest_path = ('%sids.%s'
% (_base_nest_path,
to_object_name(model_name)))
models_nest_path = ('%smodels.%s' %
(_base_nest_path,
to_object_name(model_name)))
data[ids_nest_path] = udfd.pk
data[models_nest_path] = {
'udfd': udfd,
'fields': udfd.datatype_dict[0]['choices']
}
p = 'udfc.%s.' % to_object_name(k)
data[p + 'action_verb'] = v['action_verb']
data[p + 'range_field_key'] = v['range_field_key']
data[p + 'action_field_key'] = v['action_field_key']
data['models'] |= {clz}
return data
def add_map_info_to_context(context, instance):
all_polygon_types = {c.map_feature_type
for c in leaf_models_of_class(PolygonalMapFeature)}
my_polygon_types = set(instance.map_feature_types) & all_polygon_types
context['has_polygons'] = len(my_polygon_types) > 0
context['has_boundaries'] = instance.boundaries.exists()
def set_map_feature_updated_at():
models = [Model.map_feature_type for Model in
leaf_models_of_class(MapFeature)]
if not models:
raise Exception("Could not find any map_feature subclasses")
models_in = "('%s')" % "','".join(models)
# For a baseline, pull the most recent change to the MapFeature itself.
# This depends on the fact that all the MapFeature subclasses share the
# same id pool and ids do not overlap.
# NOTE: This MUST be run first. Additional update queries compare
# dates against the updated_at values set by this statement.
execute_sql("""
UPDATE treemap_mapfeature
SET updated_at = a.updated_at
FROM (
SELECT model_id as id, MAX(created) AS updated_at
FROM treemap_audit
WHERE treemap_audit.model IN %s
GROUP BY model_id
) a
WHERE a.id = treemap_mapfeature.id;""" % models_in)
# If the tree associated with a MapFeature has been updated more
# recently than the MapFeature, copy the tree's most recent
# update date to the MapFeature
execute_sql("""
UPDATE treemap_mapfeature
SET updated_at = GREATEST(treemap_mapfeature.updated_at, b.updated_at)
FROM (
SELECT plot_id as id, a.updated_at
FROM treemap_tree
JOIN (
SELECT model_id as tree_id, MAX(created) AS updated_at
FROM treemap_audit
WHERE treemap_audit.model = 'Tree'
GROUP BY model_id
) a
ON a.tree_id = treemap_tree.id
) b
WHERE b.id = treemap_mapfeature.id;""")
# If a photo associated with a Tree or MapFeature has been updated more
# recently than the MapFeature, copy the photo's most recent
# update date to the MapFeature. TreePhoto is a subclass of
# MapFeaturePhoto so they share the same id range.
execute_sql("""
UPDATE treemap_mapfeature
SET updated_at = GREATEST(treemap_mapfeature.updated_at, b.updated_at)
FROM (
SELECT map_feature_id as id, a.updated_at
FROM treemap_mapfeaturephoto
JOIN (
SELECT model_id as photo_id, MAX(created) AS updated_at
FROM treemap_audit
WHERE treemap_audit.model in ('TreePhoto', 'MapFeaturePhoto')
GROUP BY model_id
) a
ON a.photo_id = treemap_mapfeaturephoto.id
) b
WHERE b.id = treemap_mapfeature.id;
""")
def subclass_dict(cls):
return {
C.map_feature_type: C
for C in leaf_models_of_class(MapFeature)
}
def global_settings(request):
last_instance = get_last_visited_instance(request)
if hasattr(request, 'user') and request.user.is_authenticated():
last_effective_instance_user =\
request.user.get_effective_instance_user(last_instance)
_update_last_seen(last_effective_instance_user)
else:
if hasattr(request, 'instance'):
instance = request.instance
default_role = instance.default_role
last_effective_instance_user = InstanceUser(
role=default_role, instance=instance)
else:
last_effective_instance_user = None
if hasattr(request, 'instance') and request.instance.logo:
logo_url = request.instance.logo.url
else:
logo_url = settings.STATIC_URL + "img/logo.png"
try:
comment_file_path = finders.find('version.txt')
with open(comment_file_path, 'r') as f:
header_comment = f.read()
except:
header_comment = "Version information not available\n"
term = copy.copy(REPLACEABLE_TERMS)
if hasattr(request, 'instance'):
term.update(request.instance.config.get('terms', {}))
# config.get('terms') above populates the term context variable with
# model terminology provided it has been customized for the treemap
# instance, but fails to populate it with the default terminology. The
# for loop below ensures that term is populated with model terminology
# whether it has been customized or not.
# Convertible is the base class where the terminology class property is
# defined, so its leaf subclasses are the ones with default terminology
# we might care about.
# leaf_models_of_class uses recursive descent through the
# clz.__subclasses__ attributes, but it only iterates through a total
# of around ten nodes at present, so it is unlikely to be a performance
# problem.
for clz in leaf_models_of_class(Convertible):
term.update({
clz.__name__: clz.terminology(request.instance)})
ctx = {
'SITE_ROOT': settings.SITE_ROOT,
'settings': settings,
'last_instance': last_instance,
'last_effective_instance_user': last_effective_instance_user,
'logo_url': logo_url,
'header_comment': header_comment,
'term': term,
'embed': request_is_embedded(request),
'datepicker_start_date': datetime.min.replace(year=1900),
}
return ctx
def subclass_dict(cls):
return {C.map_feature_type: C
for C in leaf_models_of_class(MapFeature)}
def set_map_feature_updated_by():
models = [
Model.map_feature_type for Model in leaf_models_of_class(MapFeature)
]
if not models:
raise Exception("Could not find any map_feature subclasses")
models_in = "('%s')" % "','".join(models)
# For a baseline, pull the most recent change to the MapFeature itself.
# This depends on the fact that all the MapFeature subclasses share the
# same id pool and ids do not overlap.
# NOTE: This MUST be run first. Additional update queries compare
# dates against the updated_at values set by this statement.
execute_sql("""
UPDATE treemap_mapfeature
SET updated_by_id = m.updated_by
FROM (
SELECT DISTINCT ON (a.model_id)
a.model_id, a.user_id AS updated_by, a.updated AS updated_at
FROM treemap_audit a
WHERE a.model IN %s
GROUP BY a.model_id, a.id
ORDER BY a.model_id, updated_at DESC
) m
WHERE m.model_id = treemap_mapfeature.id;""" % models_in)
# If the tree associated with a MapFeature has been updated more
# recently than the MapFeature, override the MapFeature's updated_by_id
# with the user_id of the tree's most recent audit.
execute_sql("""
UPDATE treemap_mapfeature
SET updated_by_id = mfja.tree_by_user
FROM (
-- MapFeature has the same id as the Plot referenced by the Tree
-- from the immediate subquery,
-- with update information from innermost subquery
-- but only if the Tree update is more recent than the MapFeature update
SELECT mf.id AS plot_id, tja.updated_by AS tree_by_user
FROM treemap_mapfeature mf
JOIN (
-- Tree's plot with update information from innermost subquery
SELECT t.plot_id, ta.updated_at, ta.updated_by
FROM treemap_tree t
JOIN (
-- Most recent audit records of Trees
SELECT DISTINCT ON (a.model_id)
a.model_id, a.user_id AS updated_by, a.updated AS updated_at
FROM treemap_audit a
WHERE a.model = 'Tree'
GROUP BY a.model_id, a.id
ORDER BY a.model_id, updated_at DESC
) ta -- ta for tree audit
ON ta.model_id = t.id
) tja -- tja for tree joined with audit
ON mf.id = tja.plot_id
WHERE mf.updated_at < tja.updated_at
) mfja -- mfja for mapfeature joined with audit
WHERE mfja.plot_id = treemap_mapfeature.id;""")
# If a photo associated with a Tree or MapFeature has been updated more
# recently than the MapFeature, override the MapFeeature's updated_by_id
# with the user_id of the photo's most recent audit.
# TreePhoto is a subclass of MapFeaturePhoto
# so they share the same id range.
execute_sql("""
UPDATE treemap_mapfeature
SET updated_by_id = mfja.photo_by_user
FROM (
-- MapFeature referenced by the photo from the immediate subquery,
-- with update information from innermost subquery
-- but only if the photo update is more recent than the MapFeature update
SELECT mf.id AS map_feature_id, phja.updated_by AS photo_by_user
FROM treemap_mapfeature mf
JOIN (
-- Photo's MapFeature with update information from innermost subquery
SELECT mfph.map_feature_id, pha.updated_at, pha.updated_by
FROM treemap_mapfeaturephoto mfph
JOIN (
-- Most recent audit records of Photos,
-- where the model id is the same for a TreePhoto and its
-- MapFeaturePhoto superclass
SELECT DISTINCT ON (a.model_id)
a.model_id, a.user_id AS updated_by, a.updated AS updated_at
FROM treemap_audit a
WHERE a.model in ('TreePhoto', 'MapFeaturePhoto')
GROUP BY a.model_id, a.id
ORDER BY a.model_id, updated_at DESC
) pha -- pha for photo audit
ON pha.model_id = mfph.id
) phja -- phja for photo joined with audit
ON mf.id = phja.map_feature_id
WHERE mf.updated_at < phja.updated_at
) mfja -- mfja for mapfeature joined with audit
WHERE mfja.map_feature_id = treemap_mapfeature.id;""")
def set_map_feature_updated_at():
models = [
Model.map_feature_type for Model in leaf_models_of_class(MapFeature)
]
if not models:
raise Exception("Could not find any map_feature subclasses")
models_in = "('%s')" % "','".join(models)
# For a baseline, pull the most recent change to the MapFeature itself.
# This depends on the fact that all the MapFeature subclasses share the
# same id pool and ids do not overlap.
# NOTE: This MUST be run first. Additional update queries compare
# dates against the updated_at values set by this statement.
execute_sql("""
UPDATE treemap_mapfeature
SET updated_at = a.updated_at
FROM (
SELECT model_id as id, MAX(created) AS updated_at
FROM treemap_audit
WHERE treemap_audit.model IN %s
GROUP BY model_id
) a
WHERE a.id = treemap_mapfeature.id;""" % models_in)
# If the tree associated with a MapFeature has been updated more
# recently than the MapFeature, copy the tree's most recent
# update date to the MapFeature
execute_sql("""
UPDATE treemap_mapfeature
SET updated_at = GREATEST(treemap_mapfeature.updated_at, b.updated_at)
FROM (
SELECT plot_id as id, a.updated_at
FROM treemap_tree
JOIN (
SELECT model_id as tree_id, MAX(created) AS updated_at
FROM treemap_audit
WHERE treemap_audit.model = 'Tree'
GROUP BY model_id
) a
ON a.tree_id = treemap_tree.id
) b
WHERE b.id = treemap_mapfeature.id;""")
# If a photo associated with a Tree or MapFeature has been updated more
# recently than the MapFeature, copy the photo's most recent
# update date to the MapFeature. TreePhoto is a subclass of
# MapFeaturePhoto so they share the same id range.
execute_sql("""
UPDATE treemap_mapfeature
SET updated_at = GREATEST(treemap_mapfeature.updated_at, b.updated_at)
FROM (
SELECT map_feature_id as id, a.updated_at
FROM treemap_mapfeaturephoto
JOIN (
SELECT model_id as photo_id, MAX(created) AS updated_at
FROM treemap_audit
WHERE treemap_audit.model in ('TreePhoto', 'MapFeaturePhoto')
GROUP BY model_id
) a
ON a.photo_id = treemap_mapfeaturephoto.id
) b
WHERE b.id = treemap_mapfeature.id;
""")
def set_map_feature_updated_by():
models = [Model.map_feature_type for Model in
leaf_models_of_class(MapFeature)]
if not models:
raise Exception("Could not find any map_feature subclasses")
models_in = "('%s')" % "','".join(models)
# For a baseline, pull the most recent change to the MapFeature itself.
# This depends on the fact that all the MapFeature subclasses share the
# same id pool and ids do not overlap.
# NOTE: This MUST be run first. Additional update queries compare
# dates against the updated_at values set by this statement.
execute_sql("""
UPDATE treemap_mapfeature
SET updated_by_id = m.updated_by
FROM (
SELECT DISTINCT ON (a.model_id)
a.model_id, a.user_id AS updated_by, a.updated AS updated_at
FROM treemap_audit a
WHERE a.model IN %s
GROUP BY a.model_id, a.id
ORDER BY a.model_id, updated_at DESC
) m
WHERE m.model_id = treemap_mapfeature.id;""" % models_in)
# If the tree associated with a MapFeature has been updated more
# recently than the MapFeature, override the MapFeature's updated_by_id
# with the user_id of the tree's most recent audit.
execute_sql("""
UPDATE treemap_mapfeature
SET updated_by_id = mfja.tree_by_user
FROM (
-- MapFeature has the same id as the Plot referenced by the Tree
-- from the immediate subquery,
-- with update information from innermost subquery
-- but only if the Tree update is more recent than the MapFeature update
SELECT mf.id AS plot_id, tja.updated_by AS tree_by_user
FROM treemap_mapfeature mf
JOIN (
-- Tree's plot with update information from innermost subquery
SELECT t.plot_id, ta.updated_at, ta.updated_by
FROM treemap_tree t
JOIN (
-- Most recent audit records of Trees
SELECT DISTINCT ON (a.model_id)
a.model_id, a.user_id AS updated_by, a.updated AS updated_at
FROM treemap_audit a
WHERE a.model = 'Tree'
GROUP BY a.model_id, a.id
ORDER BY a.model_id, updated_at DESC
) ta -- ta for tree audit
ON ta.model_id = t.id
) tja -- tja for tree joined with audit
ON mf.id = tja.plot_id
WHERE mf.updated_at < tja.updated_at
) mfja -- mfja for mapfeature joined with audit
WHERE mfja.plot_id = treemap_mapfeature.id;""")
# If a photo associated with a Tree or MapFeature has been updated more
# recently than the MapFeature, override the MapFeeature's updated_by_id
# with the user_id of the photo's most recent audit.
# TreePhoto is a subclass of MapFeaturePhoto
# so they share the same id range.
execute_sql("""
UPDATE treemap_mapfeature
SET updated_by_id = mfja.photo_by_user
FROM (
-- MapFeature referenced by the photo from the immediate subquery,
-- with update information from innermost subquery
-- but only if the photo update is more recent than the MapFeature update
SELECT mf.id AS map_feature_id, phja.updated_by AS photo_by_user
FROM treemap_mapfeature mf
JOIN (
-- Photo's MapFeature with update information from innermost subquery
SELECT mfph.map_feature_id, pha.updated_at, pha.updated_by
FROM treemap_mapfeaturephoto mfph
JOIN (
-- Most recent audit records of Photos,
-- where the model id is the same for a TreePhoto and its
-- MapFeaturePhoto superclass
SELECT DISTINCT ON (a.model_id)
a.model_id, a.user_id AS updated_by, a.updated AS updated_at
FROM treemap_audit a
WHERE a.model in ('TreePhoto', 'MapFeaturePhoto')
GROUP BY a.model_id, a.id
ORDER BY a.model_id, updated_at DESC
) pha -- pha for photo audit
ON pha.model_id = mfph.id
) phja -- phja for photo joined with audit
ON mf.id = phja.map_feature_id
WHERE mf.updated_at < phja.updated_at
) mfja -- mfja for mapfeature joined with audit
WHERE mfja.map_feature_id = treemap_mapfeature.id;""")
