rrs/models.py: Added raw SQL calls

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 <mariano.lopez@linux.intel.com>
Signed-off-by: Aníbal Limón <anibal.limon@linux.intel.com>

1 files changed, 102 insertions, 0 deletions

diff --git a/rrs/models.py b/rrs/models.py
index 814a9691a9..e9cf787cca 100644
--- a/rrs/models.py
+++ b/rrs/models.py
@@ -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)])
+

@media only all and (prefers-color-scheme: dark) {
.highlight .hll { background-color: #49483e }
.highlight .c { color: #75715e } /* Comment */
.highlight .err { color: #960050; background-color: #1e0010 } /* Error */
.highlight .k { color: #66d9ef } /* Keyword */
.highlight .l { color: #ae81ff } /* Literal */
.highlight .n { color: #f8f8f2 } /* Name */
.highlight .o { color: #f92672 } /* Operator */
.highlight .p { color: #f8f8f2 } /* Punctuation */
.highlight .ch { color: #75715e } /* Comment.Hashbang */
.highlight .cm { color: #75715e } /* Comment.Multiline */
.highlight .cp { color: #75715e } /* Comment.Preproc */
.highlight .cpf { color: #75715e } /* Comment.PreprocFile */
.highlight .c1 { color: #75715e } /* Comment.Single */
.highlight .cs { color: #75715e } /* Comment.Special */
.highli
+                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()
+        ]