[odrs-web/oscp] Remove the now-unused app_id column from the reviews table

[Richard Hughes <rhughes src gnome org>]

commit da37ca302825d57f1fbea3b5a17edffa8e19956c
Author: Richard Hughes <richard hughsie com>
Date:   Thu Jul 4 13:51:43 2019 +0100

    Remove the now-unused app_id column from the reviews table

 app_data/migrations/versions/a22c286d8094_.py | 22 +++++++++++++++
 app_data/odrs/models.py                       |  1 -
 app_data/odrs/util.py                         | 40 +++++++++++++--------------
 app_data/odrs/views_api.py                    |  1 -
 4 files changed, 42 insertions(+), 22 deletions(-)
---
diff --git a/app_data/migrations/versions/a22c286d8094_.py b/app_data/migrations/versions/a22c286d8094_.py
new file mode 100644
index 0000000..e65e9ba
--- /dev/null
+++ b/app_data/migrations/versions/a22c286d8094_.py
@@ -0,0 +1,22 @@
+"""
+
+Revision ID: a22c286d8094
+Revises: 6f54fde07d02
+Create Date: 2019-07-04 13:50:13.788206
+
+"""
+
+# revision identifiers, used by Alembic.
+revision = 'a22c286d8094'
+down_revision = '6f54fde07d02'
+
+from alembic import op
+import sqlalchemy as sa
+from sqlalchemy.dialects import mysql
+
+def upgrade():
+    op.drop_column('reviews', 'app_id')
+
+
+def downgrade():
+    op.add_column('reviews', sa.Column('app_id', mysql.TEXT(), nullable=True))
diff --git a/app_data/odrs/models.py b/app_data/odrs/models.py
index e2f3e0f..ea7ca53 100644
--- a/app_data/odrs/models.py
+++ b/app_data/odrs/models.py
@@ -164,7 +164,6 @@ class Review(db.Model):
     review_id = Column(Integer, primary_key=True, nullable=False, unique=True)
     date_created = Column(DateTime, nullable=False, default=datetime.datetime.utcnow)
     date_deleted = Column(DateTime)
-    _app_id = Column('app_id', Text)
     component_id = Column(Integer, ForeignKey('components.component_id'), nullable=False)
     locale = Column(Text)
     summary = Column(Text)
diff --git a/app_data/odrs/util.py b/app_data/odrs/util.py
index fb6dd23..34d1ea7 100644
--- a/app_data/odrs/util.py
+++ b/app_data/odrs/util.py
@@ -8,7 +8,7 @@
 import json
 import hashlib
 
-from sqlalchemy import text, or_
+from sqlalchemy import or_
 
 from flask import Response
 
@@ -62,26 +62,26 @@ def _eventlog_add(user_addr=None,
 def _get_rating_for_app_id(app_id, min_total=1):
     """ Gets the ratings information for the application """
     from odrs import db
-    stmt = text('SELECT COUNT(*) total,'
-                '       SUM(rating = 0) star0,'
-                '       SUM(rating = 20) star1,'
-                '       SUM(rating = 40) star2,'
-                '       SUM(rating = 60) star3,'
-                '       SUM(rating = 80) star4,'
-                '       SUM(rating = 100) star5 '
-                'FROM reviews WHERE app_id=:app_id;')
-    res = db.session.execute(stmt.bindparams(app_id=app_id)).fetchone() # pylint: disable=no-member
-    if not res:
+    from odrs.models import Review, Component
+
+    # get all ratings for app
+    array = [0] * 6
+    for rating in db.session.query(Review.rating).\
+                        join(Component).\
+                        filter(Component.app_id == app_id).all():
+        idx = int(rating[0] / 20)
+        if idx > 5:
+            continue
+        array[idx] += 1
+
+    # nothing found
+    if sum(array) < min_total:
         return []
-    item = {}
-    item['total'] = res[0]
-    if item['total'] < min_total:
-        return []
-    for i in range(6):
-        if res[i + 1]:
-            item['star%i' % i] = int(res[i + 1])
-        else:
-            item['star%i' % i] = 0
+
+    # return as dict
+    item = {'total': sum(array)}
+    for idx in range(6):
+        item['star{}'.format(idx)] = array[idx]
     return item
 
 def _password_hash(value):
diff --git a/app_data/odrs/views_api.py b/app_data/odrs/views_api.py
index d9f19d1..79ff4d2 100644
--- a/app_data/odrs/views_api.py
+++ b/app_data/odrs/views_api.py
@@ -132,7 +132,6 @@ def api_submit():
 
     # create new
     review = Review()
-    review._app_id = item['app_id'] # pylint: disable=protected-access
     review.locale = item['locale']
     review.summary = _sanitised_summary(item['summary'])
     review.description = _sanitised_description(item['description'])