GNOME.org
 

[odrs-web] Drop the duplicate user_hash table rows

commit 3272b880c34d62acab7488d2d3ef9cd98d078266
Author: Richard Hughes <richard hughsie com>
Date:   Tue Jul 2 11:15:10 2019 +0100

    Drop the duplicate user_hash table rows
    
    This should reduce database size considerably.

 app_data/migrations/versions/b63a028c3346_.py | 27 +++++++++++++++++++++++
 app_data/odrs/models.py                       | 18 +++++-----------
 app_data/odrs/templates/mods.html             |  2 +-
 app_data/odrs/util.py                         |  3 +--
 app_data/odrs/views_admin.py                  | 26 ++++++++--------------
 app_data/odrs/views_api.py                    | 31 ++++++++++++---------------
 6 files changed, 57 insertions(+), 50 deletions(-)
---
diff --git a/app_data/migrations/versions/b63a028c3346_.py b/app_data/migrations/versions/b63a028c3346_.py
new file mode 100644
index 0000000..8ed375b
--- /dev/null
+++ b/app_data/migrations/versions/b63a028c3346_.py
@@ -0,0 +1,27 @@
+"""
+
+Revision ID: b63a028c3346
+Revises: 19526c284b29
+Create Date: 2019-07-02 11:13:57.117376
+
+"""
+
+# revision identifiers, used by Alembic.
+revision = 'b63a028c3346'
+down_revision = '19526c284b29'
+
+from alembic import op
+import sqlalchemy as sa
+from sqlalchemy.dialects import mysql
+
+def upgrade():
+    op.drop_column('eventlog', 'user_hash')
+    op.drop_column('moderators', 'user_hash')
+    op.drop_column('reviews', 'user_hash')
+    op.drop_column('votes', 'user_hash')
+
+def downgrade():
+    op.add_column('votes', sa.Column('user_hash', mysql.TEXT(), nullable=True))
+    op.add_column('reviews', sa.Column('user_hash', mysql.TEXT(), nullable=True))
+    op.add_column('moderators', sa.Column('user_hash', mysql.TEXT(), nullable=True))
+    op.add_column('eventlog', sa.Column('user_hash', mysql.TEXT(), nullable=True))
diff --git a/app_data/odrs/models.py b/app_data/odrs/models.py
index ea100d3..9df1c7b 100644
--- a/app_data/odrs/models.py
+++ b/app_data/odrs/models.py
@@ -18,11 +18,11 @@ from odrs import db
 
 from .util import _password_hash, _get_user_key
 
-def _vote_exists(review_id, user_hash):
+def _vote_exists(review_id, user_id):
     """ Checks to see if a vote exists for the review+user """
     return db.session.query(Vote).\
                 filter(Vote.review_id == review_id).\
-                filter(Vote.user_hash == user_hash).\
+                filter(Vote.user_id == user_id).\
                 first()
 
 class Analytic(db.Model):
@@ -51,17 +51,15 @@ class Vote(db.Model):
 
     vote_id = Column(Integer, primary_key=True, nullable=False, unique=True)
     date_created = Column(DateTime, nullable=False, default=datetime.datetime.utcnow)
-    user_hash = Column(Text)
     user_id = Column(Integer, ForeignKey('users.user_id'), nullable=True)
     val = Column(Integer, default=0)
     review_id = Column(Integer, default=0)
 
     user = relationship('User')
 
-    def __init__(self, user_id, user_hash, val, review_id=0):
+    def __init__(self, user_id, val, review_id=0):
         self.review_id = review_id
         self.user_id = user_id
-        self.user_hash = user_hash
         self.val = val
 
     def __repr__(self):
@@ -107,7 +105,6 @@ class Review(db.Model):
     locale = Column(Text)
     summary = Column(Text)
     description = Column(Text)
-    user_hash = Column(Text)
     user_id = Column(Integer, ForeignKey('users.user_id'), nullable=True)
     user_addr = Column(Text)
     user_display = Column(Text)
@@ -130,7 +127,6 @@ class Review(db.Model):
         self.karma_up = 0
         self.karma_down = 0
         self.user_id = 0
-        self.user_hash = None
         self.user_display = None
         self.rating = 0
         self.reported = 0
@@ -149,7 +145,7 @@ class Review(db.Model):
             'review_id': self.review_id,
             'summary': self.summary,
             'user_display': self.user_display,
-            'user_hash': self.user_hash,
+            'user_hash': self.user.user_hash,
             'version': self.version,
         }
         if user_hash:
@@ -171,7 +167,6 @@ class Event(db.Model):
     eventlog_id = Column(Integer, primary_key=True, nullable=False, unique=True)
     date_created = Column(DateTime, nullable=False, default=datetime.datetime.utcnow)
     user_addr = Column(Text)
-    user_hash = Column(Text)
     user_id = Column(Integer, ForeignKey('users.user_id'), nullable=True)
     message = Column(Text)
     app_id = Column(Text)
@@ -179,10 +174,9 @@ class Event(db.Model):
 
     user = relationship('User')
 
-    def __init__(self, user_addr, user_id=None, user_hash=None, app_id=None, message=None, important=False):
+    def __init__(self, user_addr, user_id=None, app_id=None, message=None, important=False):
         self.user_addr = user_addr
         self.user_id = user_id
-        self.user_hash = user_hash
         self.message = message
         self.app_id = app_id
         self.important = important
@@ -202,7 +196,6 @@ class Moderator(db.Model):
     display_name = Column(Text)
     is_enabled = Column(Boolean, default=False)
     is_admin = Column(Boolean, default=False)
-    user_hash = Column(Text)
     user_id = Column(Integer, ForeignKey('users.user_id'), nullable=True)
     locales = Column(Text)
 
@@ -214,7 +207,6 @@ class Moderator(db.Model):
         self.is_enabled = False
         self.is_admin = False
         self.user_id = 0
-        self.user_hash = None
         self.locales = None
         self.locales = password
 
diff --git a/app_data/odrs/templates/mods.html b/app_data/odrs/templates/mods.html
index 7b14112..3df4ab8 100644
--- a/app_data/odrs/templates/mods.html
+++ b/app_data/odrs/templates/mods.html
@@ -20,7 +20,7 @@
     <td>{{u.display_name}}</td>
     <td>{{u.is_enabled}}</td>
     <td>{{u.is_admin}}</td>
-    <td>{{u.user_hash}}</td>
+    <td>{{u.user.user_hash}}</td>
   </tr>
 {% endfor %}
 </table>
diff --git a/app_data/odrs/util.py b/app_data/odrs/util.py
index e22352d..240046c 100644
--- a/app_data/odrs/util.py
+++ b/app_data/odrs/util.py
@@ -50,14 +50,13 @@ def _get_user_key(user_hash, app_id):
 
 def _eventlog_add(user_addr=None,
                   user_id=None,
-                  user_hash=None,
                   app_id=None,
                   message=None,
                   important=False):
     """ Adds a warning to the event log """
     from .models import Event
     from odrs import db
-    db.session.add(Event(user_addr, user_id, user_hash, app_id, message, important))
+    db.session.add(Event(user_addr, user_id, app_id, message, important))
     db.session.commit()
 
 def _get_rating_for_app_id(app_id, min_total=1):
diff --git a/app_data/odrs/views_admin.py b/app_data/odrs/views_admin.py
index 16cfe65..155661d 100644
--- a/app_data/odrs/views_admin.py
+++ b/app_data/odrs/views_admin.py
@@ -57,8 +57,6 @@ def _get_hash_for_user(user):
         return None
     if not getattr(user, 'user_hash', None):
         return None
-    if not user.user_hash:
-        return None
     return user.user_hash
 
 def _password_check(value):
@@ -188,7 +186,7 @@ def admin_show_stats():
     stats['Active reviews'] = rs.fetchone()[0]
 
     # unique reviewers
-    rs = db.session.execute("SELECT COUNT(DISTINCT(user_hash)) FROM reviews;") # pylint: disable=no-member
+    rs = db.session.execute("SELECT COUNT(DISTINCT(user_id)) FROM reviews;") # pylint: disable=no-member
     stats['Unique reviewers'] = rs.fetchone()[0]
 
     # total votes
@@ -198,7 +196,7 @@ def admin_show_stats():
     stats['User downvotes'] = rs.fetchone()[0]
 
     # unique voters
-    rs = db.session.execute("SELECT COUNT(DISTINCT(user_hash)) FROM votes;") # pylint: disable=no-member
+    rs = db.session.execute("SELECT COUNT(DISTINCT(user_id)) FROM votes;") # pylint: disable=no-member
     stats['Unique voters'] = rs.fetchone()[0]
 
     # unique languages
@@ -422,8 +420,7 @@ def odrs_show_unmoderated():
     """
     Return all the reviews on the server as HTML.
     """
-    user_hash = _get_hash_for_user(current_user)
-    if not user_hash:
+    if not current_user.user:
         flash('No user_hash for current user')
         return redirect(url_for('.odrs_index'))
 
@@ -434,7 +431,7 @@ def odrs_show_unmoderated():
         lang = r.locale.split('_')[0]
         if langs and lang not in langs:
             continue
-        if _vote_exists(r.review_id, user_hash):
+        if _vote_exists(r.review_id, current_user.user.user_id):
             continue
         if len(reviews) > 20:
             break
@@ -601,9 +598,8 @@ def admin_moderate_delete(moderator_id):
 @login_required
 def admin_vote(review_id, val_str):
     """ Up or downvote an existing review by @val karma points """
-    user_hash = _get_hash_for_user(current_user)
-    if not user_hash:
-        flash('No user_hash for current user')
+    if not current_user.user:
+        flash('No user for moderator')
         return redirect(url_for('.admin_show_review', review_id=review_id))
     if val_str == 'up':
         val = 1
@@ -616,16 +612,12 @@ def admin_vote(review_id, val_str):
         return redirect(url_for('.admin_show_review', review_id=review_id))
 
     # the user already has a review
-    if _vote_exists(review_id, user_hash):
+    if _vote_exists(review_id, current_user.user_id):
         flash('already voted on this app')
         return redirect(url_for('.admin_show_review', review_id=review_id))
 
-    user = db.session.query(User).filter(User.user_hash == user_hash).first()
-    if not user:
-        user = User(user_hash)
-        db.session.add(user)
-    user.karma += val
-    db.session.add(Vote(user.user_id, user_hash, val, review_id=review_id))
+    current_user.user.karma += val
+    db.session.add(Vote(current_user.user_id, val, review_id=review_id))
     db.session.commit()
     flash('Recorded vote')
     return redirect(url_for('.admin_show_review', review_id=review_id))
diff --git a/app_data/odrs/views_api.py b/app_data/odrs/views_api.py
index 8232e11..60dfc94 100644
--- a/app_data/odrs/views_api.py
+++ b/app_data/odrs/views_api.py
@@ -100,10 +100,9 @@ def api_submit():
     # user has already reviewed
     if db.session.query(Review).\
             filter(Review.app_id == item['app_id']).\
-            filter(Review.user_hash == item['user_hash']).first():
+            filter(Review.user_id == user.user_id).first():
         _eventlog_add(_get_client_address(),
                       user.user_id,
-                      item['user_hash'],
                       item['app_id'],
                       'already reviewed')
         return json_error('already reviewed this app')
@@ -115,7 +114,6 @@ def api_submit():
     review.summary = _sanitised_summary(item['summary'])
     review.description = _sanitised_description(item['description'])
     review.user_id = user.user_id
-    review.user_hash = item['user_hash']
     review.version = _sanitised_version(item['version'])
     review.distro = item['distro']
     review.rating = item['rating']
@@ -133,7 +131,6 @@ def api_submit():
     # log and add
     _eventlog_add(_get_client_address(),
                   review.user_id,
-                  review.user_hash,
                   review.app_id,
                   'reviewed')
     db.session.add(review)
@@ -220,7 +217,7 @@ def api_fetch():
         item_new['user_skey'] = _get_user_key(item['user_hash'], item['app_id'])
 
         # the UI can hide the vote buttons on reviews already voted on
-        if _vote_exists(review.review_id, item['user_hash']):
+        if _vote_exists(review.review_id, user.user_id):
             item_new['vote_id'] = 1
 
         items_new.append(item_new)
@@ -265,10 +262,13 @@ def api_moderate(user_hash, locale=None):
     """
     # only return reviews the user has not already voted on
     items = []
+    user = db.session.query(User).filter(User.user_hash == user_hash).first()
+    if not user:
+        return json_error('no user for {}'.format(user_hash))
     for review in db.session.query(Review).all():
         if locale and not _locale_is_compatible(review.locale, locale):
             continue
-        if _vote_exists(review.review_id, user_hash):
+        if _vote_exists(review.review_id, user.user_id):
             continue
         items.append(review.asdict(user_hash))
         if len(items) > 250:
@@ -314,14 +314,14 @@ def _vote(val):
             return json_error('all negative karma used up')
 
     if item['user_skey'] != _get_user_key(item['user_hash'], item['app_id']):
-        _eventlog_add(_get_client_address(), user.user_id, item['user_hash'], None,
+        _eventlog_add(_get_client_address(), user.user_id, None,
                       'invalid user_skey of %s' % item['user_skey'], important=True)
         #print('expected user_skey of %s' % _get_user_key(item['user_hash'], item['app_id']))
         return json_error('invalid user_skey')
 
     # the user already has a review
-    if _vote_exists(item['review_id'], item['user_hash']):
-        _eventlog_add(_get_client_address(), user.user_id, item['user_hash'], item['app_id'],
+    if _vote_exists(item['review_id'], user.user_id):
+        _eventlog_add(_get_client_address(), user.user_id, item['app_id'],
                       'duplicate vote')
         return json_error('already voted on this app')
 
@@ -330,7 +330,7 @@ def _vote(val):
 
     review = db.session.query(Review).filter(Review.app_id == item['app_id']).first()
     if not review:
-        _eventlog_add(_get_client_address(), user.user_id, item['user_hash'], None,
+        _eventlog_add(_get_client_address(), user.user_id, None,
                       'invalid review ID of %s' % item['app_id'], important=True)
         return json_error('invalid review ID')
 
@@ -345,9 +345,9 @@ def _vote(val):
     db.session.commit()
 
     # add the vote to the database
-    db.session.add(Vote(user.user_id, item['user_hash'], val, review_id=item['review_id']))
+    db.session.add(Vote(user.user_id, val, review_id=item['review_id']))
     db.session.commit()
-    _eventlog_add(_get_client_address(), user.user_id, item['user_hash'], item['app_id'],
+    _eventlog_add(_get_client_address(), user.user_id, item['app_id'],
                   'voted %i on review' % val)
 
     return json_success('voted #%i %i' % (item['review_id'], val))
@@ -401,15 +401,13 @@ def api_remove():
     if not len(item['user_skey']) == 40:
         return json_error('the user_skey is invalid')
 
-    # get user
+    # the user already has a review
     user = db.session.query(User).filter(User.user_hash == item['user_hash']).first()
     if not user:
         return json_error('no review')
-
-    # the user already has a review
     review = db.session.query(Review).\
                 filter(Review.review_id == item['review_id']).\
-                filter(Review.user_hash == item['user_hash']).first()
+                filter(Review.user_id == user.user_id).first()
     if not review:
         return json_error('no review')
     if review.app_id != item['app_id']:
@@ -424,7 +422,6 @@ def api_remove():
     db.session.commit()
     _eventlog_add(_get_client_address(),
                   user.user_id,
-                  item['user_hash'],
                   item['app_id'],
                   'removed review')
     return json_success('removed review #%i' % item['review_id'])
[Date Prev][Date Next]   [Thread Prev][Thread Next]   [Thread Index] [Date Index] [Author Index]