Python execute_sql Beispiele

Beispiel #1

Datei: map_feature.py Projekt: recklessromeo/otm-core

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;
""")

Beispiel #2

Datei: map_feature.py Projekt: dkosmidis/BuzzTheTrees

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;
""")

Beispiel #3

Datei: map_feature.py Projekt: dkosmidis/BuzzTheTrees

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;""")

Beispiel #4

Datei: map_feature.py Projekt: OpenTreeMap/otm-core

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;""")

Beispiel #5

Datei: test_cached_audit_info.py Projekt: dkosmidis/BuzzTheTrees

 def clear_updated_at(self):
     # to_timestamp(0) is the unix epoch 1970-1-1 00:00
     execute_sql(
         "UPDATE treemap_mapfeature SET updated_at = to_timestamp(0);")

Beispiel #6

Datei: test_cached_audit_info.py Projekt: dkosmidis/BuzzTheTrees

 def clear_updated_by(self):
     execute_sql("UPDATE treemap_mapfeature SET updated_by_id = {};".format(
         self.default_user.pk))

Beispiel #7

Datei: test_updated_at.py Projekt: HackMichiana/otm-core

 def clear_updated_at(self):
     # to_timestamp(0) is the unix epoch 1970-1-1 00:00
     execute_sql(
         "UPDATE treemap_mapfeature SET updated_at = to_timestamp(0);")

Beispiel #8

Datei: test_cached_audit_info.py Projekt: OpenTreeMap/otm-core

 def clear_updated_by(self):
     execute_sql(
         "UPDATE treemap_mapfeature SET updated_by_id = {};".format(
             self.default_user.pk))