Skip to contentSkip to conversation

rrs/models.py: Added raw SQL calls

PATCH New [email protected] 1 participant 2 messages Jun 8, 2015

lore
Share & tools
lore
#0 lore raw

PATCH 1/2 rrs/models.py: Added raw SQL calls

From: Mariano Lopez <[email protected]>

The raw calls are going to be used instead of Django's ORM. This improves the performance of the webpage.

---
 rrs/models.py | 102 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 102 insertions(+)
rrs/models.py +102 -0
index c909daf..320670c 100644
@@ -12,6 +12,7 @@ sys.path.insert(0, os.path.realpath(os.path.join(os.path.dirname(__file__), '../
 from datetime import date
 
 from django.db import models
+from django.db import connection
 from django.db.models.query import Q
 from layerindex.models import Recipe
 
@@ -353,3 +354,104 @@ class RecipeUpgrade(models.Model):
     def __unicode__(self):
         return '%s: (%s, %s)' % (self.recipe.pn, self.version,
                         self.commit_date)
+
+class Raw():
+
+    @staticmethod
+    def get_remahi_by_end_date(date):
+        cur = connection.cursor()
+
+        cur.execute("""SELECT id
+                FROM rrs_RecipeMaintainerHistory
+                WHERE date <= %s
+                ORDER BY date DESC
+                LIMIT 1;
+                """, [str(date)])
+
+        ret = cur.fetchone()
+
+        if not ret:
+            cur.execute("""SELECT id
+                FROM rrs_RecipeMaintainerHistory
+                ORDER BY date
+                LIMIT 1;""")
+            ret = cur.fetchone()
+
+        return ret
+
+    @staticmethod
+    def get_re_by_mantainer_and_date(maintainer, date_id):
+        recipes = []
+        cur = connection.cursor()
+
+        cur.execute("""SELECT DISTINCT rema.recipe_id
+                FROM rrs_RecipeMaintainer as rema
+                INNER JOIN rrs_maintainer AS ma
+                ON rema.maintainer_id = ma.id
+                WHERE rema.history_id = %s AND ma.name = %s;
+                """, [date_id, maintainer])
+
+        for re in cur.fetchall():
+            recipes.append(re[0])
+        return recipes
+
+    @staticmethod
+    def get_reup_by_recipes_and_date(recipes_id, date_id=None):
+        stats = []
+        recipes = str(recipes_id).strip('[]')
+
+        if date_id:
+            qry = """SELECT recipe_id, status, no_update_reason, version
+                    FROM rrs_RecipeUpstream"""
+            qry += "\nWHERE history_id = '%s' AND" % str(date_id)
+            qry += "\nrecipe_id IN (%s)\n" % recipes
+            cur = connection.cursor()
+            cur.execute(qry)
+            stats = Raw.dictfetchall(cur)
+
+        return stats
+
+    @staticmethod
+    def get_ma_by_recipes_and_date(recipes_id, date_id=None):
+        stats = []
+        recipes = str(recipes_id).strip('[]')
+
+        if date_id:
+            qry = """SELECT rema.recipe_id, ma.name
+                    FROM rrs_RecipeMaintainer AS rema
+                    INNER JOIN rrs_Maintainer AS ma
+                    ON rema.maintainer_id = ma.id"""
+            qry += "\nWHERE rema.history_id = '%s' AND" % str(date_id)
+            qry += "\nrema.recipe_id IN (%s)\n" % recipes
+            cur = connection.cursor()
+            cur.execute(qry)
+            stats = Raw.dictfetchall(cur)
+
+        return stats
+
+    @staticmethod
+    def get_reupg_by_date(date):
+        cur = connection.cursor()
+        cur.execute("""SELECT re.id, re.pn, re.summary, te.version, rownum FROM (
+                    SELECT recipe_id, version, commit_date,
+                    ROW_NUMBER() OVER(
+                        PARTITION BY recipe_id
+                        ORDER BY commit_date DESC
+                    ) AS rownum
+                    FROM rrs_RecipeUpgrade
+                    WHERE commit_date <= %s) AS te
+                INNER JOIN layerindex_Recipe AS re
+                ON te.recipe_id = re.id
+                WHERE rownum = 1
+                ORDER BY re.pn;
+                """, [date])
+        return Raw.dictfetchall(cur)
+
+    @staticmethod
+    def dictfetchall(cursor):
+        "Returns all rows from a cursor as a dict"
+        desc = cursor.description
+        return [
+            dict(zip([col[0] for col in desc], row))
+            for row in cursor.fetchall()
+        ]
Signature · 1 line
1.9.1
#1 lore raw

PATCH 2/2 rrs/views.py: Changed ORM to raw SQL calls

From: Mariano Lopez <[email protected]>

Allow to use the new raw SQL call to get better performance in the web page. Also removed SQL queries inside loops.
This is only for two views: recipes and maintainers

---
 rrs/views.py | 118 ++++++++++++++++++++++++++++++-----------------------------
 1 file changed, 61 insertions(+), 57 deletions(-)
rrs/views.py +61 -57
index c90fb37..ad741f6 100644
@@ -12,7 +12,7 @@ from django.core.urlresolvers import resolve
 from layerindex.models import Recipe
 from rrs.models import Release, Milestone, Maintainer, RecipeMaintainerHistory, \
         RecipeMaintainer, RecipeUpstreamHistory, RecipeUpstream, \
-        RecipeDistro, RecipeUpgrade
+        RecipeDistro, RecipeUpgrade, Raw
 
 def _check_url_params(upstream_status, maintainer_name):
     get_object_or_404(Maintainer, name=maintainer_name)
@@ -53,28 +53,27 @@ def _get_milestone_statistics(milestone, maintainer_name=None):
         milestone_statistics['unknown'] = \
             RecipeUpstream.get_recipes_unknown(recipe_upstream_history).count()
     else:
-        recipe_maintainer_history = RecipeMaintainerHistory.get_by_end_date(
-            milestone.end_date)
-        recipe_maintainer_all = RecipeMaintainer.objects.filter(history = recipe_maintainer_history,
-            maintainer__name = maintainer_name)
+        recipe_maintainer_history = Raw.get_remahi_by_end_date(
+                milestone.end_date)
+        recipe_maintainer_all = Raw.get_re_by_mantainer_and_date(
+                maintainer_name, recipe_maintainer_history[0])
         milestone_statistics['all'] = len(recipe_maintainer_all)
-
-        recipe_upstream_all = []
-        for rm in recipe_maintainer_all:
-            ru_qry = RecipeUpstream.objects.filter(recipe = rm.recipe, history =
-                        recipe_upstream_history)
-            if ru_qry:
-                recipe_upstream_all.append(ru_qry[0])
+        if recipe_upstream_history:
+            recipe_upstream_all = Raw.get_reup_by_recipes_and_date(
+                    recipe_maintainer_all, recipe_upstream_history.id)
+        else:
+            recipe_upstream_all = Raw.get_reup_by_recipes_and_date(
+                    recipe_maintainer_all)
 
         milestone_statistics['up_to_date'] = 0
         milestone_statistics['not_updated'] = 0
         milestone_statistics['cant_be_updated'] = 0
         milestone_statistics['unknown'] = 0
         for ru in recipe_upstream_all:
-            if ru.status == 'Y':
+            if ru['status'] == 'Y':
                 milestone_statistics['up_to_date'] += 1
-            elif ru.status == 'N':
-                if ru.no_update_reason == '':
+            elif ru['status'] == 'N':
+                if ru['no_update_reason'] == '':
                     milestone_statistics['not_updated'] += 1
                 else:
                     milestone_statistics['cant_be_updated'] += 1
@@ -106,8 +105,8 @@ class RecipeList():
         self.summary = summary
 
 def _get_recipe_list(milestone):
-    recipe_maintainer_history = RecipeMaintainerHistory.get_by_end_date(
-        milestone.end_date)
+    recipe_maintainer_history = Raw.get_remahi_by_end_date(
+                milestone.end_date)
 
     recipe_upstream_history = RecipeUpstreamHistory.get_last_by_date_range(
         milestone.start_date,
@@ -115,57 +114,62 @@ def _get_recipe_list(milestone):
     )
 
     recipe_list = []
+    recipes_ids = []
+    recipe_upstream_dict_all = {}
+    maintainers_dict_all = {}
     current_date = date.today()
-    for recipe in Recipe.objects.filter().order_by('pn'):
-        if current_date >= milestone.start_date and \
-            current_date <= milestone.end_date:
-            version = recipe.pv
-        else:
-            rup = RecipeUpgrade.get_by_recipe_and_date(recipe,
-                    milestone.end_date)
 
-            if rup is None: # Recipe don't exit in this Milestone
-                continue
-
-            version = rup.version
+    recipes = Raw.get_reupg_by_date(
+            milestone.end_date)
+    for recipe in recipes:
+        recipes_ids.append(recipe['id'])
+
+    if recipe_upstream_history:
+        recipe_upstream_all = Raw.get_reup_by_recipes_and_date(
+                recipes_ids, recipe_upstream_history.id)
+        maintainers_all = Raw.get_ma_by_recipes_and_date(
+                recipes_ids, recipe_maintainer_history[0])
+        for reup in recipe_upstream_all:
+            recipe_upstream_dict_all[reup['recipe_id']] = reup
+        for ma in maintainers_all:
+            maintainers_dict_all[ma['recipe_id']] = ma['name']
+    else:
+        recipe_upstream_all = None
 
+    for recipe in recipes:
         upstream_version = ''
         upstream_status = ''
         no_update_reason = ''
+
         if recipe_upstream_history:
-            recipe_upstream = RecipeUpstream.get_by_recipe_and_history(
-                    recipe, recipe_upstream_history)
-
-            if recipe_upstream is None:
-                recipe_upstream = RecipeUpstream()
-                recipe_upstream.history = recipe_upstream_history
-                recipe_upstream.recipe = recipe
-                recipe_upstream.version = ''
-                recipe_upstream.type = 'M' # Manual
-                recipe_upstream.status = 'U' # Unknown
-                recipe_upstream.no_update_reason = ''
-                recipe_upstream.date = recipe_upstream_history.end_date
-                recipe_upstream.save()
-
-            if recipe_upstream.status == 'N' and recipe_upstream.no_update_reason:
-                recipe_upstream.status = 'C'
+            recipe_upstream = recipe_upstream_dict_all.get(recipe['id'])
+            if not recipe_upstream:
+                recipe_add =  Recipe.objects.filter(id = recipe['id'])[0]
+                recipe_upstream_add = RecipeUpstream()
+                recipe_upstream_add.history = recipe_upstream_history
+                recipe_upstream_add.recipe = recipe_add
+                recipe_upstream_add.version = ''
+                recipe_upstream_add.type = 'M' # Manual
+                recipe_upstream_add.status = 'U' # Unknown
+                recipe_upstream_add.no_update_reason = ''
+                recipe_upstream_add.date = recipe_upstream_history.end_date
+                recipe_upstream_add.save()
+                recipe_upstream = {'version': '', 'status': 'U', 'type': 'M',
+                        'no_update_reason': ''}
+
+            if recipe_upstream['status'] == 'N' and recipe_upstream['no_update_reason']:
+                recipe_upstream['status'] = 'C'
             upstream_status = \
-                RecipeUpstream.RECIPE_UPSTREAM_STATUS_CHOICES_DICT[
-                         recipe_upstream.status]
+                    RecipeUpstream.RECIPE_UPSTREAM_STATUS_CHOICES_DICT[
+                        recipe_upstream['status']]
             if upstream_status == 'Downgrade':
                 upstream_status = 'Unknown' # Downgrade is displayed as Unknown
-            upstream_version = recipe_upstream.version
-            no_update_reason = recipe_upstream.no_update_reason
-
-        maintainer = RecipeMaintainer.get_maintainer_by_recipe_and_history(
-                recipe, recipe_maintainer_history)
-        if maintainer is None:
-            maintainer_name = ''
-        else:
-            maintainer_name = maintainer.name
+            upstream_version = recipe_upstream['version']
+            no_update_reason = recipe_upstream['no_update_reason']
 
-        recipe_list_item = RecipeList(recipe.id, recipe.pn, recipe.summary)
-        recipe_list_item.version = version
+        maintainer_name =  maintainers_dict_all.get(recipe['id'], '')
+        recipe_list_item = RecipeList(recipe['id'], recipe['pn'], recipe['summary'])
+        recipe_list_item.version = recipe['version']
         recipe_list_item.upstream_status = upstream_status
         recipe_list_item.upstream_version = upstream_version
         recipe_list_item.maintainer_name = maintainer_name
Signature · 1 line
1.9.1