def get_top_raters(self, video): video_obj = self.get_video_object(video) request = self.context.get("request", {}) username = search_username_from_request(request) if not video_obj: qs = UserInformation.objects.none() elif username: qs = UserInformation.objects.filter(user__username=username) else: qs = video_obj.certified_top_raters() # annotating with whether the rating is public pref_privacy = 'user__userpreferences__videoratingprivacy' qs = VideoRatingPrivacy._annotate_privacy( qs=qs, prefix=pref_privacy, field_user=None, filter_add={f'{pref_privacy}__video': video_obj}) qs = qs.annotate( n_public_rating=Case(When(_is_public=True, then=Value(1)), default=Value(0), output_field=IntegerField())) return qs
def test_privacy(self): VideoRatingPrivacy.objects.all().delete() qs = Video.objects.all() qs = VideoRatingPrivacy._annotate_privacy(qs, prefix='videoratingprivacy', field_user=self.user_prefs, default_value=True) assert len(qs) == 1 assert qs[0]._is_public is True qs = Video.objects.all() qs = VideoRatingPrivacy._annotate_privacy(qs, prefix='videoratingprivacy', field_user=self.user_prefs, default_value=False) assert len(qs) == 1 assert qs[0]._is_public is False VideoRatingPrivacy.objects.all().delete() VideoRatingPrivacy.objects.create(video=self.video, user=self.user_prefs, is_public=True) qs = Video.objects.all() qs = VideoRatingPrivacy._annotate_privacy(qs, prefix='videoratingprivacy', field_user=self.user_prefs, default_value=True) assert len(qs) == 1 assert qs[0]._is_public is True qs = Video.objects.all() qs = VideoRatingPrivacy._annotate_privacy(qs, prefix='videoratingprivacy', field_user=self.user_prefs, default_value=False) assert len(qs) == 1 assert qs[0]._is_public is True VideoRatingPrivacy.objects.all().delete() VideoRatingPrivacy.objects.create(video=self.video, user=self.user_prefs, is_public=False) qs = Video.objects.all() qs = VideoRatingPrivacy._annotate_privacy(qs, prefix='videoratingprivacy', field_user=self.user_prefs, default_value=True) assert len(qs) == 1 assert qs[0]._is_public is False qs = Video.objects.all() qs = VideoRatingPrivacy._annotate_privacy(qs, prefix='videoratingprivacy', field_user=self.user_prefs, default_value=False) assert len(qs) == 1 assert qs[0]._is_public is False
def video_rating_statistics(self, request): """Get statistical data on video ratings.""" qs = VideoRating.objects.all() # filtering according to the query parameters qs = self.filter_queryset(qs) # annotate: total score given the preferences vector = get_user_preferences(self.request).features_as_vector_centered vector = update_preferences_vector_from_request(vector, self.request.query_params) qs = qs.annotate(score=get_score_annotation(vector)) # annotate: public/private rating qs = VideoRatingPrivacy._annotate_privacy( qs, prefix='video__videoratingprivacy', field_user=F('user'), default_value=None, annotate_bool=True, annotate_n=False) # either public, or myself qs = qs.annotate(_is_public_or_myself=Case( When(_is_public=True, then=Value(True)), When(user__user__username=request.user.username, then=Value(True)), default=Value(False), output_field=BooleanField(), )) # total number of pairwise comparisons by this video by this user qs = qs.annotate(n_cmp_1=Count('video__expertrating_video_1', distinct=True, filter=Q(video__expertrating_video_1__user=F('user')))) qs = qs.annotate(n_cmp_2=Count('video__expertrating_video_2', distinct=True, filter=Q(video__expertrating_video_2__user=F('user')))) qs = qs.annotate(n_comparisons=F('n_cmp_1') + F('n_cmp_2')) # annotate: for public ones, give the username, for the rest, give None qs = qs.annotate(public_username=Case( When(_is_public_or_myself=True, then=F('user__user__username')), default=Value(None), output_field=CharField())) # deterministic ordering qs = qs.order_by('pk') page = self.paginate_queryset(qs) if page is not None: serializer = VideoRatingsStatisticsSerializerV2(page, many=True) return self.get_paginated_response(serializer.data) serializer = VideoRatingsStatisticsSerializerV2(qs, many=True) return Response(serializer.data)
def search_username_from_request(request): """Get the username to use the scores from.""" if not hasattr(request, 'query_params'): return False if not isinstance(request.query_params, dict): return False username = request.query_params.get('search_model', None) if username: qs = VideoRating.objects.filter(user__user__username=username) qs = VideoRatingPrivacy._annotate_privacy(qs=qs) qs = qs.filter(_is_public=True) n_public_videos = qs.count() if username != request.user.username and n_public_videos == 0: raise PermissionDenied() return username return False
def get_public_append_only_database_as_pd(): """Get the public append-only database.""" # a horrible hack to make django-pandas work with annotations # see https://github.com/chrisdev/django-pandas/blob/master/django_pandas/io.py # see https://github.com/chrisdev/django-pandas/issues/124 # TODO: fix it import django django.db.models.fields.FieldDoesNotExist = django.core.exceptions.FieldDoesNotExist result_df = {} default_features = [constants['DEFAULT_PREFS_VAL'] for _ in VIDEO_FIELDS] # all videos with the tournesol score and all criteria video_df = read_frame(Video.objects.all().annotate( score=get_score_annotation(default_features)), fieldnames=['id', 'video_id', 'score'] + VIDEO_FIELDS) result_df['all_video_scores'] = video_df # all history for ratings, with both videos rated publicly qs = HistoricalExpertRating.objects.all() for v in '12': qs = VideoRatingPrivacy._annotate_privacy( qs, prefix=f'video_{v}__videoratingprivacy', output_prefix=f"_v{v}") qs = qs.filter(_v1_is_public=True, _v2_is_public=True) result_df['comparison_database'] = read_frame( qs, fieldnames=[ 'id', 'duration_ms', 'datetime_lastedit', 'datetime_add', *VIDEO_FIELDS, *[x + '_weight' for x in VIDEO_FIELDS], 'user__user__username', 'video_1__video_id', 'video_2__video_id', 'history_id', 'history_date', 'history_change_reason', 'history_type' ]) # getting all user data (without demo accounts) qs = UserInformation.objects.all().filter(is_demo=False) # adding _is_certified field qs = UserInformation._annotate_is_certified(qs) # Even if 'show my profile' is false, export 'username'. # If 'show my profile' is true, export 'First name', # 'Last name', 'Title', 'Bio', # If 'show online presence' is true, export 'Website', # 'Linkedin', 'Youtube', 'Google scholar', 'Orcid', 'Researchgate', 'Twitter'. # Do NOT share demographic data. # only username fields_basic = UserInformation.BASIC_FIELDS + ['_is_certified'] qs1 = qs.filter(show_my_profile=False) df1 = read_frame(qs1, fieldnames=fields_basic) # username and info fields_profile = UserInformation.PROFILE_FIELDS qs2 = qs.filter(show_my_profile=True, show_online_presence=False) df2 = read_frame(qs2, fieldnames=fields_basic + fields_profile) # username, info and online fields fields_online = UserInformation.ONLINE_FIELDS qs3 = qs.filter(show_my_profile=True, show_online_presence=True) df3 = read_frame(qs3, fieldnames=fields_basic + fields_profile + fields_online) # all contributors df = pd.concat([df1, df2, df3], axis=0, ignore_index=True) result_df['contributors_public'] = df return result_df
def test_download_privacy_public_database(driver, django_db_blocker): """Test that public database is a zip archive, and it only contains public info.""" create_toy_data(django_db_blocker=django_db_blocker, driver=driver, n_users=30, n_videos=100, n_ratings=30) open_tournesol(driver) WebDriverWait(driver, TIME_WAIT).until( EC.presence_of_element_located((By.ID, "id_public_database_download"))) link = driver.find_element_by_id('id_public_database_download').get_attribute('href') data = get(link) assert data.ok assert data.content assert data.headers['content-type'] == 'application/zip' # with open('data.zip', 'wb') as f: # f.write(data.content) # reading dataframes zip_file = BytesIO(data.content) dfs = {} with zipfile.ZipFile(zip_file, 'r') as zf: for fileinfo in zf.infolist(): content = zf.read(fileinfo).decode('ascii') df = pd.read_csv(StringIO(content)) dfs[fileinfo.filename] = df # print(data.content) assert set(dfs.keys()) == set( ['comparison_database.csv', 'contributors_public.csv', 'all_video_scores.csv'] ), f"Wrong files in archive: {dfs.keys()}" # checking comparisons privacy df = dfs['comparison_database.csv'] for _, row in df.iterrows(): username = row['user__user__username'] vid1 = row['video_1__video_id'] vid2 = row['video_2__video_id'] # both videos must be rated publicly! with django_db_blocker.unblock(): for vid in [vid1, vid2]: qs = Video.objects.filter(video_id=vid) assert qs.count() == 1, (qs, qs.count()) up = UserPreferences.objects.get(user__username=username) qs = VideoRatingPrivacy._annotate_privacy(qs, prefix="videoratingprivacy", field_user=up) assert qs.count() == 1, (qs, qs.count()) assert qs.get()._is_public, qs.values() print("Check for", username, vid1, vid2, "successful") # checking user information privacy df = dfs['contributors_public.csv'] for _, row in df.iterrows(): username = row['user__username'] # checking certification status with django_db_blocker.unblock(): qs = UserInformation.objects.filter(user__username=username) assert qs.count() == 1, qs qs = UserInformation._annotate_is_certified(qs) assert qs.count() == 1, qs ui = qs.get() assert ui._is_certified == row['_is_certified'], (dict(row), ui) # checking show_my_profile if not ui.show_my_profile: for f in UserInformation.PROFILE_FIELDS: assert pd.isna(row[f]), row[f] # checking online presence if not ui.show_online_presence or not ui.show_my_profile: for f in UserInformation.ONLINE_FIELDS: assert pd.isna(row[f]), row[f] # checking that protected fields are not included for f in UserInformation.PROTECTED_FIELDS: assert f not in row, (f, row) print("Check for", username, "successful")
def get_queryset(self, pk=None): """All videos except for null ones.""" queryset = Video.objects.filter(is_unlisted=False).values() request = self.request fields = [x.name for x in Video._meta.fields] for f in VIDEO_FIELDS: fields.remove(f) def get_score_annotation(user_preferences_vector): """Returns an sql object annotating queries with the video ratings (sclar product).""" return sum( [F(f) * v for f, v in zip(VIDEO_FIELDS, user_preferences_vector)]) features = self.get_features_from_request() default_features = [constants['DEFAULT_PREFS_VAL'] for _ in VIDEO_FIELDS] search_username = self.need_scores_for_username() # computing score inside the database if search_username: fields_exclude = set(Video.COMPUTED_PROPERTIES) fields = [f for f in fields if f not in fields_exclude] queryset = queryset.values(*fields) queryset = queryset.annotate(**{key: F(f'videorating__{key}') for key in VIDEO_FIELDS}, user=F( 'videorating__user__user__username')).filter( user=search_username) # for myself, allow showing public/non-public videos if search_username == request.user.username: is_public = request.query_params.get('show_all_my_videos', 'true') == 'false' print(is_public) else: # for other people, only show public videos is_public = True # keeping only public videos if is_public: queryset = VideoRatingPrivacy._annotate_privacy( queryset, prefix='videoratingprivacy', field_user=None, filter_add={'videoratingprivacy__user__user__username': search_username} ) queryset = queryset.filter(_is_public=True) queryset = queryset.annotate(rating_n_experts=Value(1, IntegerField())) q1 = Q(expertrating_video_1__user__user__username=search_username) q2 = Q(expertrating_video_2__user__user__username=search_username) c1 = Count('expertrating_video_1', q1, distinct=True) c2 = Count('expertrating_video_2', q2, distinct=True) queryset = queryset.annotate(rating_n_ratings=c1 + c2) queryset = queryset.annotate(n_public_experts=Value(1, IntegerField())) queryset = queryset.annotate(n_private_experts=Value(0, IntegerField())) # TODO: a hack. improve this queryset = queryset.annotate( public_experts=Value("", CharField())) # logging model usage in search if self.request.user.is_authenticated: RepresentativeModelUsage.objects.get_or_create( viewer=UserPreferences.objects.get(user__username=self.request.user.username), model=UserPreferences.objects.get(user__username=search_username) ) queryset = queryset.annotate( score_preferences_term=get_score_annotation(features)) queryset = queryset.annotate( tournesol_score=get_score_annotation(default_features)) queryset = queryset.annotate( score_search_term_=Value( 0.0, FloatField())) if request.query_params.get('search'): # computing the postgres score for search if connection.vendor.startswith('postgres'): s_query = request.query_params.get('search', '') def word_to_query(w): """Convert one word into a query.""" queries = [] queries.append(SearchQuery(w, search_type='raw')) queries.append(SearchQuery(w + ':*', search_type='raw')) return reduce(lambda x, y: x | y, queries) def words_to_query(s_query, max_len=100, max_word_len=20): """Convert a string with words into a SearchQuery.""" s_query = s_query[:max_len] s_query = s_query.split(' ') s_query = [''.join(filter(str.isalnum, x)) for x in s_query] s_query = [x for x in s_query if 1 <= len(x) <= max_word_len] s_query = [word_to_query(x) for x in s_query] if not s_query: return SearchQuery('') return reduce(lambda x, y: x & y, s_query) s_query = words_to_query(s_query) s_vectors = [SearchVector(f, weight=w) for f, w in zip(self.search_fields, self.search_weights)] s_vector = reduce(lambda x, y: x + y, s_vectors) queryset = queryset.annotate( score_search_term_=SearchRank(s_vector, s_query)) else: # in other databases, using basic filtering queryset = filters_.SearchFilter().filter_queryset(self.request, queryset, self) queryset = queryset.annotate( score_search_term_=Value( 1.0, FloatField())) queryset = queryset.annotate( score_search_term=F('score_search_term_') * VideoSearchEngine.VIDEO_SEARCH_COEFF) queryset = queryset.annotate( score=F('score_preferences_term') + F('score_search_term')) return queryset