rrs/models.py: Added raw SQL calls
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.
Signed-off-by Mariano Lopez <[email protected]>
--- rrs/models.py | 102 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 102 insertions(+)
rrs/models.py +102 -0
@@ -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
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
Signed-off-by Mariano Lopez <[email protected]>
--- rrs/views.py | 118 ++++++++++++++++++++++++++++++----------------------------- 1 file changed, 61 insertions(+), 57 deletions(-)
rrs/views.py +61 -57
@@ -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