[odrs-web/oscp] Update the app_id fetch count when revews are requested

[Richard Hughes <rhughes src gnome org>]

commit eaa02298a5236a1196780dc577b23f5aacf2a9b8
Author: Richard Hughes <richard hughsie com>
Date:   Thu Jul 4 12:16:22 2019 +0100

    Update the app_id fetch count when revews are requested
    
    This means the stats page no longer takes 28 seconds to load.

 app_data/migrations/versions/6f54fde07d02_.py | 38 +++++++++++++++++++++++++++
 app_data/odrs/models.py                       |  2 ++
 app_data/odrs/templates/components.html       |  4 +--
 app_data/odrs/views_admin.py                  |  7 ++---
 app_data/odrs/views_api.py                    |  6 +++++
 5 files changed, 52 insertions(+), 5 deletions(-)
---
diff --git a/app_data/migrations/versions/6f54fde07d02_.py b/app_data/migrations/versions/6f54fde07d02_.py
new file mode 100644
index 0000000..3a55ae1
--- /dev/null
+++ b/app_data/migrations/versions/6f54fde07d02_.py
@@ -0,0 +1,38 @@
+"""
+
+Revision ID: 6f54fde07d02
+Revises: e6fa15874247
+Create Date: 2019-07-04 11:58:24.685366
+
+"""
+
+# revision identifiers, used by Alembic.
+revision = '6f54fde07d02'
+down_revision = 'e6fa15874247'
+
+from alembic import op
+import sqlalchemy as sa
+from sqlalchemy.dialects import mysql
+from sqlalchemy.exc import InternalError
+
+from odrs import db
+from odrs.models import Analytic, Component
+
+def upgrade():
+    try:
+        op.add_column('components', sa.Column('fetch_cnt', sa.Integer(), nullable=True))
+    except InternalError as e:
+        print(str(e))
+    for component in db.session.query(Component).\
+                        filter(Component.app_id != '').\
+                        order_by(Component.app_id.asc()).all():
+        fetch_cnt = 0
+        for val in db.session.query(Analytic.fetch_cnt).\
+                        filter(Analytic.app_id == component.app_id).all():
+            fetch_cnt += val[0]
+        component.fetch_cnt = fetch_cnt
+        print(component.app_id, fetch_cnt)
+    db.session.commit()
+
+def downgrade():
+    op.drop_column('components', 'fetch_cnt')
diff --git a/app_data/odrs/models.py b/app_data/odrs/models.py
index e2fa43f..e2f3e0f 100644
--- a/app_data/odrs/models.py
+++ b/app_data/odrs/models.py
@@ -142,12 +142,14 @@ class Component(db.Model):
 
     component_id = Column(Integer, primary_key=True, nullable=False, unique=True)
     app_id = Column(Text)
+    fetch_cnt = Column(Integer, default=0)
     review_cnt = Column(Integer, default=1)
 
     reviews = relationship('Review', back_populates='component')
 
     def __init__(self, app_id):
         self.app_id = app_id
+        self.fetch_cnt = 0
         self.review_cnt = 1
 
     def __repr__(self):
diff --git a/app_data/odrs/templates/components.html b/app_data/odrs/templates/components.html
index b299713..2dea5bd 100644
--- a/app_data/odrs/templates/components.html
+++ b/app_data/odrs/templates/components.html
@@ -14,13 +14,13 @@
   <tr class="row">
     <th class="col-sm-1">AppStream ID</th>
     <th class="col-sm-2">Review Count</th>
-    <th class="col-sm-2">&nbsp;</th>
+    <th class="col-sm-2">Fetch Count</th>
   </tr>
 {% for component in components %}
   <tr class="row">
     <td>{{component.app_id}}</td>
     <td>{{component.review_cnt}}</td>
-    <td>&nbsp;</td>
+    <td>{{component.fetch_cnt}}</td>
   </tr>
 {% endfor %}
 </table>
diff --git a/app_data/odrs/views_admin.py b/app_data/odrs/views_admin.py
index 46c1268..902982f 100644
--- a/app_data/odrs/views_admin.py
+++ b/app_data/odrs/views_admin.py
@@ -221,9 +221,10 @@ def admin_show_stats():
         stats['%i star reviews' % star] = rs.fetchone()[0]
 
     # popularity view
-    viewed = db.session.execute("SELECT DISTINCT app_id, SUM(fetch_cnt) AS total " # pylint: 
disable=no-member
-                                "FROM analytics WHERE app_id IS NOT NULL "
-                                "GROUP BY app_id ORDER BY total DESC LIMIT 50;")
+    viewed = db.session.query(Component.app_id, Component.fetch_cnt).\
+                                    filter(Component.app_id != None).\
+                                    order_by(Component.fetch_cnt.desc()).\
+                                    limit(50).all()
 
     # popularity reviews
     submitted = db.session.query(Component.app_id, Component.review_cnt).\
diff --git a/app_data/odrs/views_api.py b/app_data/odrs/views_api.py
index 5ec19f1..d9f19d1 100644
--- a/app_data/odrs/views_api.py
+++ b/app_data/odrs/views_api.py
@@ -214,6 +214,12 @@ def api_fetch():
     except IntegrityError as e:
         print('ignoring: {}'.format(str(e)))
 
+    # increment the counter for the stats
+    component = db.session.query(Component).filter(Component.app_id == item['app_id']).first()
+    if component:
+        component.fetch_cnt += 1
+        db.session.commit()
+
     # also add any compat IDs
     app_ids = [item['app_id']]
     if 'compat_ids' in item: