diff options
Diffstat (limited to 'server')
36 files changed, 2714 insertions, 246 deletions
diff --git a/server/config.yaml.dist b/server/config.yaml.dist index b55afad..9a41295 100644 --- a/server/config.yaml.dist +++ b/server/config.yaml.dist @@ -117,6 +117,7 @@ privileges: 'posts:favorite': regular 'posts:bulk-edit:tags': power 'posts:bulk-edit:safety': power + 'posts:view:similar': regular 'posts:bulk-edit:delete': power 'tags:create': regular @@ -139,6 +140,12 @@ privileges: 'tag_categories:delete': moderator 'tag_categories:set_default': moderator + 'metrics:create': power + 'metrics:edit:bounds': power + 'metrics:edit:posts': regular + 'metrics:list': regular + 'metrics:delete': moderator + 'pools:create': regular 'pools:edit:names': power 'pools:edit:category': power diff --git a/server/szurubooru/api/__init__.py b/server/szurubooru/api/__init__.py index d9b7ecb..99c9524 100644 --- a/server/szurubooru/api/__init__.py +++ b/server/szurubooru/api/__init__.py @@ -1,5 +1,6 @@ import szurubooru.api.comment_api import szurubooru.api.info_api +import szurubooru.api.metric_api import szurubooru.api.password_reset_api import szurubooru.api.pool_api import szurubooru.api.pool_category_api diff --git a/server/szurubooru/api/metric_api.py b/server/szurubooru/api/metric_api.py new file mode 100644 index 0000000..2bba66f --- /dev/null +++ b/server/szurubooru/api/metric_api.py @@ -0,0 +1,94 @@ +from math import ceil +from typing import Optional, List, Dict +from szurubooru import db, model, search, rest +from szurubooru.func import ( + auth, metrics, snapshots, serialization, tags, versions +) + + +_search_executor_config = search.configs.PostMetricSearchConfig() +_search_executor = search.Executor(_search_executor_config) + + +def _serialize_metric( + ctx: rest.Context, metric: model.Metric) -> rest.Response: + return metrics.serialize_metric( + metric, options=serialization.get_serialization_options(ctx) + ) + + +def _serialize_post_metric( + ctx: rest.Context, post_metric: model.PostMetric) -> rest.Response: + return metrics.serialize_post_metric( + post_metric, options=serialization.get_serialization_options(ctx) + ) + + +def _get_metric(params: Dict[str, str]) -> model.Metric: + return metrics.get_metric_by_tag_name(params["tag_name"]) + + +@rest.routes.get("/metrics/?") +def get_metrics( + ctx: rest.Context, params: Dict[str, str] = {}) -> rest.Response: + auth.verify_privilege(ctx.user, "metrics:list") + all_metrics = metrics.get_all_metrics() + return { + "results": [_serialize_metric(ctx, metric) for metric in all_metrics] + } + + +@rest.routes.post("/metrics/?") +def create_metric( + ctx: rest.Context, params: Dict[str, str] = {}) -> rest.Response: + auth.verify_privilege(ctx.user, "metrics:create") + tag_name = ctx.get_param_as_string("tag_name") + tag = tags.get_tag_by_name(tag_name) + min = ctx.get_param_as_float("min") + max = ctx.get_param_as_float("max") + + metric = metrics.create_metric(tag, min, max) + ctx.session.flush() + # snapshots.create(metric, ctx.user) + ctx.session.commit() + return _serialize_metric(ctx, metric) + + +@rest.routes.delete("/metric/(?P<tag_name>.+)") +def delete_metric(ctx: rest.Context, params: Dict[str, str]) -> rest.Response: + metric = _get_metric(params) + versions.verify_version(metric, ctx) + auth.verify_privilege(ctx.user, "metrics:delete") + # snapshots.delete(metric, ctx.user) + metrics.delete_metric(metric) + ctx.session.commit() + return {} + + +@rest.routes.get("/post-metrics/?") +def get_post_metrics( + ctx: rest.Context, params: Dict[str, str] = {}) -> rest.Response: + auth.verify_privilege(ctx.user, "metrics:list") + return _search_executor.execute_and_serialize( + ctx, lambda post_metric: _serialize_post_metric(ctx, post_metric)) + + +@rest.routes.get("/post-metrics/median/(?P<tag_name>.+)") +def get_post_metrics_median( + ctx: rest.Context, params: Dict[str, str] = {}) -> rest.Response: + auth.verify_privilege(ctx.user, "metrics:list") + metric = _get_metric(params) + tag_name = params["tag_name"] + query_text = ctx.get_param_as_string( + "query", + default="%s:%f..%f" % (tag_name, metric.min, metric.max)) + total_count = _search_executor.count(query_text) + offset = ceil(total_count/2) - 1 + _, results = _search_executor.execute(query_text, offset, 1) + return { + "query": query_text, + "offset": offset, + "limit": 1, + "total": len(results), + "results": list([_serialize_post_metric(ctx, pm) for pm in results]) + } diff --git a/server/szurubooru/api/post_api.py b/server/szurubooru/api/post_api.py index 7883f5e..4e89f54 100644 --- a/server/szurubooru/api/post_api.py +++ b/server/szurubooru/api/post_api.py @@ -1,3 +1,4 @@ +from math import ceil from datetime import datetime from typing import Dict, List, Optional @@ -5,13 +6,15 @@ from szurubooru import db, errors, model, rest, search from szurubooru.func import ( auth, favorites, + metrics, mime, posts, scores, serialization, + similar, snapshots, tags, - versions, + versions, image_hash, ) _search_executor_config = search.configs.PostSearchConfig() @@ -167,6 +170,14 @@ def update_post(ctx: rest.Context, params: Dict[str, str]) -> rest.Response: if ctx.has_file("thumbnail"): auth.verify_privilege(ctx.user, "posts:edit:thumbnail") posts.update_post_thumbnail(post, ctx.get_file("thumbnail")) + if ctx.has_param("metrics"): + auth.verify_privilege(ctx.user, "metrics:edit:posts") + metrics.update_or_create_post_metrics( + post, ctx.get_param_as_list("metrics")) + if ctx.has_param("metricRanges"): + auth.verify_privilege(ctx.user, "metrics:edit:posts") + metrics.update_or_create_post_metric_ranges( + post, ctx.get_param_as_list("metricRanges")) post.last_edit_time = datetime.utcnow() ctx.session.flush() snapshots.modify(post, ctx.user) @@ -310,3 +321,72 @@ def get_posts_by_image( for distance, post in lookalikes ], } + + +@rest.routes.get("/post/(?P<post_id>[^/]+)/reverse-search/?") +def get_posts_lookalikes( + ctx: rest.Context, params: Dict[str, str] = {} +) -> rest.Response: + auth.verify_privilege(ctx.user, "posts:reverse_search") + limit = ctx.get_param_as_int("limit", default=10, min=1, max=100) + threshold = ctx.get_param_as_float("threshold", default=1, min=0, max=100) + query_text = ctx.get_param_as_string("query", default="") + post_id = _get_post_id(params) + post = posts.get_post_by_id(post_id) + if post.signature is None: + return {"similarPosts": []} + + sig = image_hash.unpack_signature(post.signature.signature) + # limit + 1 because the original post will be excluded + lookalikes = posts.search_by_signature(sig, limit + 1, threshold, query_text) + # exclude the original post: + lookalikes = filter(lambda la: la[1].post_id != post_id, lookalikes) + lookalikes = sorted(lookalikes, key=lambda la: la[0]) + return { + "similarPosts": [ + { + "distance": distance, + "post": _serialize_post(ctx, post), + } + for distance, post in lookalikes + ], + } + + +@rest.routes.get("/posts/median/?") +def get_posts_median( + ctx: rest.Context, _params: Dict[str, str] = {} +) -> rest.Response: + auth.verify_privilege(ctx.user, "posts:list") + _search_executor_config.user = ctx.user + query_text = ctx.get_param_as_string("query", default="") + total_count = _search_executor.count(query_text) + offset = ceil(total_count / 2) - 1 + _, results = _search_executor.execute(query_text, offset, 1) + return { + "query": query_text, + "offset": offset, + "limit": 1, + "total": len(results), + "results": list([_serialize_post(ctx, post) for post in results]) + } + + +@rest.routes.get("/post/(?P<post_id>[^/]+)/similar-by-tags/?") +def get_posts_similar_by_tags( + ctx: rest.Context, params: Dict[str, str] +) -> rest.Response: + auth.verify_privilege(ctx.user, "posts:view:similar") + _search_executor_config.user = ctx.user + query_text = ctx.get_param_as_string("query", default="") + post_id = _get_post_id(params) + post = posts.get_post_by_id(post_id) + limit = ctx.get_param_as_int("limit", default=10, min=1, max=100) + results = similar.find_similar_posts(post, limit, query_text) + return { + "query": query_text, + "limit": limit, + "results": list([ + posts.serialize_micro_post(result, ctx.user) for result in results + ]) + } diff --git a/server/szurubooru/api/tag_api.py b/server/szurubooru/api/tag_api.py index 6b4c807..5f54324 100644 --- a/server/szurubooru/api/tag_api.py +++ b/server/szurubooru/api/tag_api.py @@ -2,7 +2,14 @@ from datetime import datetime from typing import Dict, List, Optional from szurubooru import db, model, rest, search -from szurubooru.func import auth, serialization, snapshots, tags, versions +from szurubooru.func import ( + auth, + metrics, + serialization, + snapshots, + tags, + versions +) _search_executor = search.Executor(search.configs.TagSearchConfig()) @@ -93,6 +100,14 @@ def update_tag(ctx: rest.Context, params: Dict[str, str]) -> rest.Response: implications = ctx.get_param_as_string_list("implications") _create_if_needed(implications, ctx.user) tags.update_tag_implications(tag, implications) + if ctx.has_param("metric"): + auth.verify_privilege(ctx.user, "metrics:edit:bounds") + new_metric = metrics.update_or_create_metric(tag, ctx.get_param("metric")) + if new_metric is not None: + auth.verify_privilege(ctx.user, "metrics:create") + db.session.flush() + # snapshots.create(new_metric, ctx.user) + tag.last_edit_time = datetime.utcnow() ctx.session.flush() snapshots.modify(tag, ctx.user) diff --git a/server/szurubooru/func/metrics.py b/server/szurubooru/func/metrics.py new file mode 100644 index 0000000..944c4d5 --- /dev/null +++ b/server/szurubooru/func/metrics.py @@ -0,0 +1,273 @@ +import sqlalchemy as sa +from typing import Any, Optional, List, Dict, Callable +from szurubooru import db, model, errors, rest +from szurubooru.func import serialization, tags, util, versions + + +class MetricDoesNotExistsError(errors.ValidationError): + pass + + +class MetricAlreadyExistsError(errors.ValidationError): + pass + + +class InvalidMetricError(errors.ValidationError): + pass + + +class PostMissingTagError(errors.ValidationError): + pass + + +class MetricValueOutOfRangeError(errors.ValidationError): + pass + + +class MetricSerializer(serialization.BaseSerializer): + def __init__(self, metric: model.Metric): + self.metric = metric + + def _serializers(self) -> Dict[str, Callable[[], Any]]: + return { + "version": lambda: self.metric.version, + "min": lambda: self.metric.min, + "max": lambda: self.metric.max, + "exact_count": lambda: self.metric.post_metric_count, + "range_count": lambda: self.metric.post_metric_range_count, + "tag": lambda: tags.serialize_tag(self.metric.tag, [ + "names", "category", "description", "usages"]) + } + + +class PostMetricSerializer(serialization.BaseSerializer): + def __init__(self, post_metric: model.PostMetric): + self.post_metric = post_metric + + def _serializers(self) -> Dict[str, Callable[[], Any]]: + return { + "tag_name": lambda: self.post_metric.metric.tag_name, + "post_id": lambda: self.post_metric.post_id, + "value": lambda: self.post_metric.value, + } + + +class PostMetricRangeSerializer(serialization.BaseSerializer): + def __init__(self, post_metric_range: model.PostMetricRange): + self.post_metric_range = post_metric_range + + def _serializers(self) -> Dict[str, Callable[[], Any]]: + return { + "tag_name": lambda: self.post_metric_range.metric.tag_name, + "post_id": lambda: self.post_metric_range.post_id, + "low": lambda: self.post_metric_range.low, + "high": lambda: self.post_metric_range.high, + } + + +def serialize_metric( + metric: model.Metric, + options: List[str] = []) -> Optional[rest.Response]: + if not metric: + return None + return MetricSerializer(metric).serialize(options) + + +def serialize_post_metric( + post_metric: model.PostMetric, + options: List[str] = []) -> Optional[rest.Response]: + if not post_metric: + return None + return PostMetricSerializer(post_metric).serialize(options) + + +def serialize_post_metric_range( + post_metric_range: model.PostMetricRange, + options: List[str] = []) -> Optional[rest.Response]: + if not post_metric_range: + return None + return PostMetricRangeSerializer(post_metric_range).serialize(options) + + +def try_get_metric_by_tag_name(tag_name: str) -> Optional[model.Metric]: + return ( + db.session + .query(model.Metric) + .filter(sa.func.lower(model.Metric.tag_name) == tag_name.lower()) + .one_or_none()) + + +def get_metric_by_tag_name(tag_name: str) -> model.Metric: + metric = try_get_metric_by_tag_name(tag_name) + if not metric: + raise MetricDoesNotExistsError("Metric %r not found." % tag_name) + return metric + + +def get_all_metrics() -> List[model.Metric]: + return db.session.query(model.Metric).all() + + +def get_all_metric_tag_names() -> List[str]: + return [ + tag_name.name for tag_name in util.flatten_list( + [metric.tag.names for metric in get_all_metrics()] + ) + ] + + +def try_get_post_metric( + post: model.Post, + metric: model.Metric) -> Optional[model.PostMetric]: + return ( + db.session + .query(model.PostMetric) + .filter(model.PostMetric.metric == metric) + .filter(model.PostMetric.post == post) + .one_or_none()) + + +def try_get_post_metric_range( + post: model.Post, + metric: model.Metric) -> Optional[model.PostMetricRange]: + return ( + db.session + .query(model.PostMetricRange) + .filter(model.PostMetricRange.metric == metric) + .filter(model.PostMetricRange.post == post) + .one_or_none()) + + +def create_metric( + tag: model.Tag, + min: float, + max: float) -> model.Metric: + assert tag + if tag.metric: + raise MetricAlreadyExistsError("Tag already has a metric.") + if min >= max: + raise InvalidMetricError("Metric min(%r) >= max(%r)" % (min, max)) + metric = model.Metric(tag=tag, min=min, max=max) + db.session.add(metric) + return metric + + +def update_or_create_metric( + tag: model.Tag, + metric_data: Any) -> Optional[model.Metric]: + assert tag + for field in ("min", "max"): + if field not in metric_data: + raise InvalidMetricError("Metric is missing %r field." % field) + + min, max = metric_data["min"], metric_data["max"] + if min >= max: + raise InvalidMetricError("Metric min(%r) >= max(%r)" % (min, max)) + if tag.metric: + tag.metric.min = min + tag.metric.max = max + versions.bump_version(tag.metric) + return None + else: + return create_metric(tag=tag, min=min, max=max) + + +def update_or_create_post_metric( + post: model.Post, + metric: model.Metric, + value: float) -> model.PostMetric: + assert post + assert metric + if metric.tag not in post.tags: + raise PostMissingTagError( + "Post doesn\"t have this tag.") + if value < metric.min or value > metric.max: + raise MetricValueOutOfRangeError( + "Metric value %r out of range." % value) + post_metric = try_get_post_metric(post, metric) + if not post_metric: + post_metric = model.PostMetric(post=post, metric=metric, value=value) + db.session.add(post_metric) + else: + post_metric.value = value + versions.bump_version(post_metric) + return post_metric + + +def update_or_create_post_metrics(post: model.Post, metrics_data: Any) -> None: + """ + Overwrites any existing post metrics, deletes other existing post metrics. + """ + assert post + post.metrics = [] + for metric_data in metrics_data: + for field in ("tag_name", "value"): + if field not in metric_data: + raise InvalidMetricError("Metric is missing %r field." % field) + value = float(metric_data["value"]) + tag_name = metric_data["tag_name"] + tag = tags.get_tag_by_name(tag_name) + if not tag.metric: + raise MetricDoesNotExistsError( + "Tag %r has no metric." % tag_name) + post_metric = update_or_create_post_metric(post, tag.metric, value) + post.metrics.append(post_metric) + + +def update_or_create_post_metric_range( + post: model.Post, + metric: model.Metric, + low: float, + high: float) -> model.PostMetricRange: + assert post + assert metric + if metric.tag not in post.tags: + raise PostMissingTagError( + "Post doesn\"t have this tag.") + for value in (low, high): + if value < metric.min or value > metric.max: + raise MetricValueOutOfRangeError( + "Metric value %r out of range." % value) + if low >= high: + raise InvalidMetricError( + "Metric range low(%r) >= high(%r)" % (low, high)) + post_metric_range = try_get_post_metric_range(post, metric) + if not post_metric_range: + post_metric_range = model.PostMetricRange( + post=post, metric=metric, low=low, high=high) + db.session.add(post_metric_range) + else: + post_metric_range.low = low + post_metric_range.high = high + versions.bump_version(post_metric_range) + return post_metric_range + + +def update_or_create_post_metric_ranges( + post: model.Post, + metric_ranges_data: Any) -> None: + """ + Overwrites any existing post metrics, deletes other existing post metrics. + """ + assert post + post.metric_ranges = [] + for metric_data in metric_ranges_data: + for field in ("tag_name", "low", "high"): + if field not in metric_data: + raise InvalidMetricError( + "Metric range is missing %r field." % field) + low = float(metric_data["low"]) + high = float(metric_data["high"]) + tag_name = metric_data["tag_name"] + tag = tags.get_tag_by_name(tag_name) + if not tag.metric: + raise MetricDoesNotExistsError( + "Tag %r has no metric." % tag_name) + post_metric_range = update_or_create_post_metric_range( + post, tag.metric, low, high) + post.metric_ranges.append(post_metric_range) + + +def delete_metric(metric: model.Metric) -> None: + assert metric + db.session.delete(metric) diff --git a/server/szurubooru/func/posts.py b/server/szurubooru/func/posts.py index be2259c..a7c111a 100644 --- a/server/szurubooru/func/posts.py +++ b/server/szurubooru/func/posts.py @@ -1,5 +1,6 @@ import hmac import logging +import re from datetime import datetime from typing import Any, Callable, Dict, List, Optional, Tuple @@ -11,6 +12,7 @@ from szurubooru.func import ( files, image_hash, images, + metrics, mime, pools, scores, @@ -20,6 +22,8 @@ from szurubooru.func import ( users, util, ) +from szurubooru.func.image_hash import NpMatrix +from szurubooru.search import parser, criteria logger = logging.getLogger(__name__) @@ -198,6 +202,8 @@ class PostSerializer(serialization.BaseSerializer): "hasCustomThumbnail": self.serialize_has_custom_thumbnail, "notes": self.serialize_notes, "comments": self.serialize_comments, + "metrics": self.serialize_metrics, + "metricRanges": self.serialize_metric_ranges, "pools": self.serialize_pools, } @@ -255,6 +261,10 @@ class PostSerializer(serialization.BaseSerializer): "names": [name.name for name in tag.names], "category": tag.category.name, "usages": tag.post_count, + "metric": { + "min": tag.metric.min, + "max": tag.metric.max + } if tag.metric else None, } for tag in tags.sort_tags(self.post.tags) ] @@ -344,6 +354,24 @@ class PostSerializer(serialization.BaseSerializer): ) ] + def serialize_metrics(self) -> Any: + return [ + metrics.serialize_post_metric(metric) + for metric in sorted( + self.post.metrics, + key=lambda metric: metric.metric.tag_name + ) + ] + + def serialize_metric_ranges(self) -> Any: + return [ + metrics.serialize_post_metric_range(metric_range) + for metric_range in sorted( + self.post.metric_ranges, + key=lambda metric_range: metric_range.metric.tag_name + ) + ] + def serialize_post( post: Optional[model.Post], auth_user: model.User, options: List[str] = [] @@ -929,8 +957,16 @@ def search_by_image_exact(image_content: bytes) -> Optional[model.Post]: def search_by_image(image_content: bytes) -> List[Tuple[float, model.Post]]: query_signature = image_hash.generate_signature(image_content) - query_words = image_hash.generate_words(query_signature) + return search_by_signature(query_signature) + +def search_by_signature( + signature: NpMatrix, + limit: int = 100, + distance_cutoff: float = image_hash.DISTANCE_CUTOFF, + query_text: str = '' +) -> List[Tuple[float, model.Post]]: + query_words = image_hash.generate_words(signature) """ The unnest function is used here to expand one row containing the 'words' array into multiple rows each containing a singular word. @@ -939,15 +975,33 @@ def search_by_image(image_content: bytes) -> List[Tuple[float, model.Post]]: https://www.postgresql.org/docs/9.2/functions-array.html """ - dbquery = """ - SELECT s.post_id, s.signature, count(a.query) AS score - FROM post_signature AS s, unnest(s.words, :q) AS a(word, query) - WHERE a.word = a.query - GROUP BY s.post_id - ORDER BY score DESC LIMIT 100; - """ + # optimization: don't join if safety is not queried: + if len(query_text) > 0: + dbquery = """ + SELECT s.post_id, s.signature, count(a.query) AS score + FROM post_signature AS s + CROSS JOIN unnest(s.words, :q) AS a(word, query) + INNER JOIN post ON post.id = s.post_id + WHERE a.word = a.query + AND post.safety in :safety + GROUP BY s.post_id + ORDER BY score DESC LIMIT :limit; + """ + else: + dbquery = """ + SELECT s.post_id, s.signature, count(a.query) AS score + FROM post_signature AS s, unnest(s.words, :q) AS a(word, query) + WHERE a.word = a.query + GROUP BY s.post_id + ORDER BY score DESC LIMIT :limit; + """ + allowed_rating = _get_safety_list(query_text) - candidates = db.session.execute(dbquery, {"q": query_words}) + candidates = db.session.execute(dbquery, { + "q": query_words, + "limit": limit, + "safety": tuple(allowed_rating), + }) data = tuple( zip( *[ @@ -958,13 +1012,51 @@ def search_by_image(image_content: bytes) -> List[Tuple[float, model.Post]]: ) if data: candidate_post_ids, sigarray = data - distances = image_hash.normalized_distance(sigarray, query_signature) + distances = image_hash.normalized_distance(sigarray, signature) return [ (distance, try_get_post_by_id(candidate_post_id)) for candidate_post_id, distance in zip( candidate_post_ids, distances ) - if distance < image_hash.DISTANCE_CUTOFF + if distance < distance_cutoff ] else: return [] + + +def _get_safety_list(query_text: str = '') -> List[str]: + """Will output a list of safety options matched by the query""" + # TODO(hunternif): searching by signature should be done in executor, + # together with all other tokens, but as a quick fix for safety rating, + # we can parse it here. + # Assuming format: -rating:safe,sketchy,unsafe + query_parser = parser.Parser() + search_query = query_parser.parse(query_text) + safety_map = util.flip(SAFETY_MAP) + allowed = [] + disallowed = [] + + def process_safety(safety_value: str): + safety = safety_map.get(safety_value, None) + if safety: + if token.negated: + disallowed.append(safety) + else: + allowed.append(safety) + + for token in search_query.named_tokens: + if token.name == "rating": + criterion = token.criterion + if isinstance(criterion, criteria.PlainCriterion): + process_safety(criterion.value) + elif isinstance(criterion, criteria.ArrayCriterion): + for value in criterion.values: + process_safety(value) + + if len(allowed) == 0: + allowed = [ + model.Post.SAFETY_SAFE, + model.Post.SAFETY_SKETCHY, + model.Post.SAFETY_UNSAFE, + ] + return [x for x in allowed if x not in disallowed]
\ No newline at end of file diff --git a/server/szurubooru/func/similar.py b/server/szurubooru/func/similar.py new file mode 100644 index 0000000..a19b7d1 --- /dev/null +++ b/server/szurubooru/func/similar.py @@ -0,0 +1,32 @@ +from typing import List + +import sqlalchemy as sa + +from szurubooru import db, model, search + +_search_executor_config = search.configs.PostSearchConfig() +_search_executor = search.Executor(_search_executor_config) + + +# TODO(hunternif): this ignores the query, e.g. rating. +# (But we're actually using a "similar" search query on the client anyway.) +def find_similar_posts( + source_post: model.Post, limit: int, query_text: str = '' +) -> List[model.Post]: + post_alias = sa.orm.aliased(model.Post) + pt_alias = sa.orm.aliased(model.PostTag) + result = ( + db.session.query(post_alias) + .join(pt_alias, pt_alias.post_id == post_alias.post_id) + .filter( + sa.sql.or_( + pt_alias.tag_id == tag.tag_id for tag in source_post.tags + ) + ) + .filter(pt_alias.post_id != source_post.post_id) + .group_by(post_alias.post_id) + .order_by(sa.func.count(pt_alias.tag_id).desc()) + .order_by(post_alias.post_id.desc()) + .limit(limit) + ) + return result diff --git a/server/szurubooru/func/tags.py b/server/szurubooru/func/tags.py index 28a2a76..4981c5d 100644 --- a/server/szurubooru/func/tags.py +++ b/server/szurubooru/func/tags.py @@ -98,6 +98,7 @@ class TagSerializer(serialization.BaseSerializer): "usages": self.serialize_usages, "suggestions": self.serialize_suggestions, "implications": self.serialize_implications, + "metric": self.serialize_metric, } def serialize_names(self) -> Any: @@ -133,6 +134,16 @@ class TagSerializer(serialization.BaseSerializer): for relation in sort_tags(self.tag.implications) ] + def serialize_metric(self) -> Any: + if not self.tag.metric: + return None + else: + return { + "version": self.tag.metric.version, + "min": self.tag.metric.min, + "max": self.tag.metric.max, + } + def serialize_tag( tag: model.Tag, options: List[str] = [] @@ -241,6 +252,8 @@ def merge_tags(source_tag: model.Tag, target_tag: model.Tag) -> None: assert target_tag if source_tag.tag_id == target_tag.tag_id: raise InvalidTagRelationError("Cannot merge tag with itself.") + if source_tag.metric or target_tag.metric: + raise InvalidTagRelationError("Cannot merge tags with metrics.") def merge_posts(source_tag_id: int, target_tag_id: int) -> None: alias1 = model.PostTag diff --git a/server/szurubooru/func/util.py b/server/szurubooru/func/util.py index 453e121..dc5ced0 100644 --- a/server/szurubooru/func/util.py +++ b/server/szurubooru/func/util.py @@ -62,6 +62,10 @@ def unalias_dict(source: List[Tuple[List[str], T]]) -> Dict[str, T]: return output_dict +def flatten_list(source: List[List[T]]) -> List[T]: + return [item for sublist in source for item in sublist] + + def get_md5(source: Union[str, bytes]) -> str: if not isinstance(source, bytes): source = source.encode("utf-8") diff --git a/server/szurubooru/migrations/versions/0061c5c3299f_postmetric_depends_on_posttag.py b/server/szurubooru/migrations/versions/0061c5c3299f_postmetric_depends_on_posttag.py new file mode 100644 index 0000000..be8fc08 --- /dev/null +++ b/server/szurubooru/migrations/versions/0061c5c3299f_postmetric_depends_on_posttag.py @@ -0,0 +1,35 @@ +''' +PostMetric depends on PostTag + +Revision ID: 0061c5c3299f +Created at: 2019-04-20 14:02:23.229492 +''' + +import sqlalchemy as sa +from alembic import op + + +revision = '0061c5c3299f' +down_revision = 'aae2050fb28c' +branch_labels = None +depends_on = None + + +def upgrade(): + op.create_foreign_key( + 'post_metric_post_tag_fkey', 'post_metric', 'post_tag', + ['post_id', 'tag_id'], ['post_id', 'tag_id'], + ondelete='cascade') + op.create_foreign_key( + 'post_metric_range_post_tag_fkey', 'post_metric_range', 'post_tag', + ['post_id', 'tag_id'], ['post_id', 'tag_id'], + ondelete='cascade') + + +def downgrade(): + op.drop_constraint( + 'post_metric_post_tag_fkey', 'post_metric', + type_='foreignKey') + op.drop_constraint( + 'post_metric_range_post_tag_fkey', 'post_metric', + type_='foreignKey') diff --git a/server/szurubooru/migrations/versions/3c1f0316fa7f_resize_post_columns.py b/server/szurubooru/migrations/versions/3c1f0316fa7f_resize_post_columns.py index 17e30d5..c668baf 100644 --- a/server/szurubooru/migrations/versions/3c1f0316fa7f_resize_post_columns.py +++ b/server/szurubooru/migrations/versions/3c1f0316fa7f_resize_post_columns.py @@ -9,7 +9,7 @@ import sqlalchemy as sa from alembic import op revision = "3c1f0316fa7f" -down_revision = "1cd4c7b22846" +down_revision = "0061c5c3299f" branch_labels = None depends_on = None diff --git a/server/szurubooru/migrations/versions/51ac43760440_create_metric_tables.py b/server/szurubooru/migrations/versions/51ac43760440_create_metric_tables.py new file mode 100644 index 0000000..dd47bc7 --- /dev/null +++ b/server/szurubooru/migrations/versions/51ac43760440_create_metric_tables.py @@ -0,0 +1,50 @@ +''' +Create metric tables + +Revision ID: 51ac43760440 +Created at: 2019-04-16 17:38:47.176916 +''' + +import sqlalchemy as sa +from alembic import op + + +revision = '51ac43760440' +down_revision = '1cd4c7b22846' +branch_labels = None +depends_on = None + + +def upgrade(): + op.create_table( + 'metric', + sa.Column('tag_id', sa.Integer(), nullable=False), + sa.Column('min', sa.Float(), nullable=False), + sa.Column('max', sa.Float(), nullable=False), + sa.ForeignKeyConstraint(['tag_id'], ['tag.id']), + sa.PrimaryKeyConstraint('tag_id')) + + op.create_table( + 'post_metric', + sa.Column('post_id', sa.Integer(), nullable=False), + sa.Column('tag_id', sa.Integer(), nullable=False), + sa.Column('value', sa.Float(), nullable=False), + sa.ForeignKeyConstraint(['post_id'], ['post.id']), + sa.ForeignKeyConstraint(['tag_id'], ['metric.tag_id']), + sa.PrimaryKeyConstraint('post_id', 'tag_id')) + + op.create_table( + 'post_metric_range', + sa.Column('post_id', sa.Integer(), nullable=False), + sa.Column('tag_id', sa.Integer(), nullable=False), + sa.Column('low', sa.Float(), nullable=False), + sa.Column('high', sa.Float(), nullable=False), + sa.ForeignKeyConstraint(['post_id'], ['post.id']), + sa.ForeignKeyConstraint(['tag_id'], ['metric.tag_id']), + sa.PrimaryKeyConstraint('post_id', 'tag_id')) + + +def downgrade(): + op.drop_table('post_metric_range') + op.drop_table('post_metric') + op.drop_table('metric') diff --git a/server/szurubooru/migrations/versions/aae2050fb28c_add_version_to_metric_tables.py b/server/szurubooru/migrations/versions/aae2050fb28c_add_version_to_metric_tables.py new file mode 100644 index 0000000..c8be348 --- /dev/null +++ b/server/szurubooru/migrations/versions/aae2050fb28c_add_version_to_metric_tables.py @@ -0,0 +1,32 @@ +''' +Add version to metric tables + +Revision ID: aae2050fb28c +Created at: 2019-04-16 22:15:03.656192 +''' + +import sqlalchemy as sa +from alembic import op + + +revision = 'aae2050fb28c' +down_revision = '51ac43760440' +branch_labels = None +depends_on = None + +tables = ['metric', 'post_metric', 'post_metric_range'] + + +def upgrade(): + for table in tables: + op.add_column(table, sa.Column('version', sa.Integer(), nullable=True)) + op.execute( + sa.table(table, sa.column('version')) + .update() + .values(version=1)) + op.alter_column(table, 'version', nullable=False) + + +def downgrade(): + for table in tables: + op.drop_column(table, 'version') diff --git a/server/szurubooru/model/__init__.py b/server/szurubooru/model/__init__.py index 21a178e..2befe74 100644 --- a/server/szurubooru/model/__init__.py +++ b/server/szurubooru/model/__init__.py @@ -1,6 +1,7 @@ import szurubooru.model.util from szurubooru.model.base import Base from szurubooru.model.comment import Comment, CommentScore +from szurubooru.model.metric import Metric, PostMetric, PostMetricRange from szurubooru.model.pool import Pool, PoolName, PoolPost from szurubooru.model.pool_category import PoolCategory from szurubooru.model.post import ( diff --git a/server/szurubooru/model/metric.py b/server/szurubooru/model/metric.py new file mode 100644 index 0000000..530165a --- /dev/null +++ b/server/szurubooru/model/metric.py @@ -0,0 +1,141 @@ +import sqlalchemy as sa +from szurubooru.model.base import Base +from szurubooru.model.post import PostTag +from szurubooru.model.tag import TagName + + +class PostMetric(Base): + __tablename__ = 'post_metric' + + post_id = sa.Column( + 'post_id', + sa.Integer, + sa.ForeignKey('post.id'), + primary_key=True, + nullable=False, + index=True) + tag_id = sa.Column( + 'tag_id', + sa.Integer, + sa.ForeignKey('metric.tag_id'), + primary_key=True, + nullable=False, + index=True) + version = sa.Column('version', sa.Integer, default=1, nullable=False) + value = sa.Column('value', sa.Float, nullable=False, index=True) + + post = sa.orm.relationship('Post') + metric = sa.orm.relationship('Metric', back_populates='post_metrics') + + __table_args__ = (sa.ForeignKeyConstraint( + (post_id, tag_id), + (PostTag.post_id, PostTag.tag_id), + ondelete='cascade'), + ) + __mapper_args__ = { + 'version_id_col': version, + 'version_id_generator': False, + # when deleting tag or post, cascade will ensure this post metric is + # also deleted, but sqlalchemy will try to delete it twice because of + # the cascade on foreign key into PostTag. This silences the error: + 'confirm_deleted_rows': False, + } + + +class PostMetricRange(Base): + """ + Could be a metric in the process of finding its exact value, e.g. by sorting. + It has upper and lower boundaries that will converge at the final value. + """ + __tablename__ = 'post_metric_range' + + post_id = sa.Column( + 'post_id', + sa.Integer, + sa.ForeignKey('post.id'), + primary_key=True, + nullable=False, + index=True) + tag_id = sa.Column( + 'tag_id', + sa.Integer, + sa.ForeignKey('metric.tag_id'), + primary_key=True, + nullable=False, + index=True) + version = sa.Column('version', sa.Integer, default=1, nullable=False) + low = sa.Column('low', sa.Float, nullable=False) + high = sa.Column('high', sa.Float, nullable=False) + + post = sa.orm.relationship('Post') + metric = sa.orm.relationship('Metric', back_populates='post_metric_ranges') + + __table_args__ = (sa.ForeignKeyConstraint( + (post_id, tag_id), + (PostTag.post_id, PostTag.tag_id), + ondelete='cascade'), + ) + __mapper_args__ = { + 'version_id_col': version, + 'version_id_generator': False, + # when deleting tag or post, cascade will ensure this post metric is + # also deleted, but sqlalchemy will try to delete it twice because of + # the cascade on foreign key into PostTag. This silences the error: + 'confirm_deleted_rows': False, + } + + +class Metric(Base): + """ + Must be attached to a tag, tag_id is primary key. + """ + __tablename__ = 'metric' + + tag_id = sa.Column( + 'tag_id', + sa.Integer, + sa.ForeignKey('tag.id'), + primary_key=True, + nullable=False, + index=True) + version = sa.Column('version', sa.Integer, default=1, nullable=False) + min = sa.Column('min', sa.Float, nullable=False) + max = sa.Column('max', sa.Float, nullable=False) + + tag = sa.orm.relationship('Tag') + post_metrics = sa.orm.relationship( + 'PostMetric', back_populates='metric', cascade='all, delete-orphan') + post_metric_ranges = sa.orm.relationship( + 'PostMetricRange', back_populates='metric', cascade='all, delete-orphan') + + tag_name = sa.orm.column_property( + ( + sa.sql.expression.select([TagName.name]) + .where(TagName.tag_id == tag_id) + .order_by(TagName.order) + .limit(1) + .as_scalar() + )) + + post_metric_count = sa.orm.column_property( + ( + sa.sql.expression.select( + [sa.sql.expression.func.count(PostMetric.post_id)]) + .where(PostMetric.tag_id == tag_id) + .correlate_except(PostMetric) + ), + deferred=True) + + post_metric_range_count = sa.orm.column_property( + ( + sa.sql.expression.select( + [sa.sql.expression.func.count(PostMetricRange.post_id)]) + .where(PostMetricRange.tag_id == tag_id) + .correlate_except(PostMetricRange) + ), + deferred=True) + + __mapper_args__ = { + 'version_id_col': version, + 'version_id_generator': False, + } diff --git a/server/szurubooru/model/post.py b/server/szurubooru/model/post.py index 49e748d..deb3cc8 100644 --- a/server/szurubooru/model/post.py +++ b/server/szurubooru/model/post.py @@ -253,6 +253,12 @@ class Post(Base): "PostNote", cascade="all, delete-orphan", lazy="joined" ) comments = sa.orm.relationship("Comment", cascade="all, delete-orphan") + metrics = sa.orm.relationship( + "PostMetric", cascade="all, delete-orphan", lazy="joined" + ) + metric_ranges = sa.orm.relationship( + "PostMetricRange", cascade="all, delete-orphan", lazy="joined" + ) _pools = sa.orm.relationship( "PoolPost", cascade="all,delete-orphan", diff --git a/server/szurubooru/model/tag.py b/server/szurubooru/model/tag.py index 61dbf83..65dabb1 100644 --- a/server/szurubooru/model/tag.py +++ b/server/szurubooru/model/tag.py @@ -110,6 +110,11 @@ class Tag(Base): secondaryjoin=tag_id == TagImplication.child_id, lazy="joined", ) + metric = sa.orm.relationship( + "Metric", + uselist=False, + cascade="all, delete-orphan" + ) post_count = sa.orm.column_property( sa.sql.expression.select( diff --git a/server/szurubooru/rest/context.py b/server/szurubooru/rest/context.py index 40ba0bc..a75ca60 100644 --- a/server/szurubooru/rest/context.py +++ b/server/szurubooru/rest/context.py @@ -78,6 +78,9 @@ class Context: def has_param(self, name: str) -> bool: return name in self._params + def get_param(self, name: str) -> Any: + return self._params[name] + def get_param_as_list( self, name: str, default: Union[object, List[Any]] = MISSING ) -> List[Any]: @@ -176,6 +179,32 @@ class Context: "Parameter %r must be an integer value." % name ) + def get_param_as_float( + self, + name: str, + default: Union[object, float] = MISSING, + min: Optional[float] = None, + max: Optional[float] = None) -> float: + if name not in self._params: + if default is not MISSING: + return cast(float, default) + raise errors.MissingRequiredParameterError( + "Required parameter %r is missing." % name) + value = self._params[name] + try: + value = float(value) + if min is not None and value < min: + raise errors.InvalidParameterError( + "Parameter %r must be at least %r." % (name, min)) + if max is not None and value > max: + raise errors.InvalidParameterError( + "Parameter %r may not exceed %r." % (name, max)) + return value + except (ValueError, TypeError): + pass + raise errors.InvalidParameterError( + "Parameter %r must be a float value." % name) + def get_param_as_bool( self, name: str, default: Union[object, bool] = MISSING ) -> bool: diff --git a/server/szurubooru/search/configs/__init__.py b/server/szurubooru/search/configs/__init__.py index c721813..72bd5bc 100644 --- a/server/szurubooru/search/configs/__init__.py +++ b/server/szurubooru/search/configs/__init__.py @@ -1,4 +1,5 @@ from .comment_search_config import CommentSearchConfig +from .post_metric_search_config import PostMetricSearchConfig from .pool_search_config import PoolSearchConfig from .post_search_config import PostSearchConfig from .snapshot_search_config import SnapshotSearchConfig diff --git a/server/szurubooru/search/configs/post_metric_search_config.py b/server/szurubooru/search/configs/post_metric_search_config.py new file mode 100644 index 0000000..0cdb1ea --- /dev/null +++ b/server/szurubooru/search/configs/post_metric_search_config.py @@ -0,0 +1,44 @@ +from typing import Dict + +import sqlalchemy as sa + +from szurubooru import db, model +from szurubooru.func import metrics, util +from szurubooru.search.configs import util as search_util +from szurubooru.search.configs.base_search_config import ( + BaseSearchConfig, Filter) +from szurubooru.search.typing import SaQuery + + +class PostMetricSearchConfig(BaseSearchConfig): + def __init__(self) -> None: + self.all_metric_names = [] + + def refresh_metrics(self) -> None: + self.all_metric_names = metrics.get_all_metric_tag_names() + + def create_filter_query(self, _disable_eager_loads: bool) -> SaQuery: + self.refresh_metrics() + return db.session.query(model.PostMetric).options(sa.orm.lazyload('*')) + + def create_count_query(self, disable_eager_loads: bool) -> SaQuery: + return self.create_filter_query(disable_eager_loads) + + def create_around_query(self) -> SaQuery: + return self.create_filter_query() + + def finalize_query(self, query: SaQuery) -> SaQuery: + return query.order_by(model.PostMetric.value.asc()) + + @property + def anonymous_filter(self) -> Filter: + return search_util.create_subquery_filter( + model.PostMetric.tag_id, + model.TagName.tag_id, + model.TagName.name, + search_util.create_str_filter) + + @property + def named_filters(self) -> Dict[str, Filter]: + num_filter = search_util.create_float_filter(model.PostMetric.value) + return {tag_name: num_filter for tag_name in self.all_metric_names} diff --git a/server/szurubooru/search/configs/post_search_config.py b/server/szurubooru/search/configs/post_search_config.py index 8c2b5b9..9065539 100644 --- a/server/szurubooru/search/configs/post_search_config.py +++ b/server/szurubooru/search/configs/post_search_config.py @@ -3,7 +3,7 @@ from typing import Any, Dict, Optional, Tuple import sqlalchemy as sa from szurubooru import db, errors, model -from szurubooru.func import auth, util +from szurubooru.func import auth, metrics, util from szurubooru.search import criteria, tokens from szurubooru.search.configs import util as search_util from szurubooru.search.configs.base_search_config import ( @@ -122,6 +122,103 @@ def _pool_filter( )(query, criterion, negated) +# includes the given post itself, also applies sort +def _similar_filter( + query: SaQuery, criterion: Optional[criteria.BaseCriterion], negated: bool +) -> SaQuery: + assert criterion + filter_func_tag = search_util.create_num_filter(model.PostTag.post_id) + pt_alias = sa.orm.aliased(model.PostTag) + + # subquery for tags of the given post (post id in criterion) + tag_query = db.session.query(model.PostTag.tag_id) + tag_query = filter_func_tag(tag_query, criterion, False) + tag_query = tag_query.subquery("source_tags") + + if negated: + # negated query runs normally, doesn't apply sort + subquery = ( + db.session.query(pt_alias.post_id) + .filter(pt_alias.tag_id.in_(tag_query)) + .group_by(pt_alias.post_id) + .subquery("similar_posts") + ) + expr = model.Post.post_id.in_(subquery) + return query.filter(~expr) + else: + # direct query applies sort + subquery = query.subquery("main_query") + return ( + db.session.query(model.Post) + .join(pt_alias, pt_alias.post_id == model.Post.post_id) + .filter(pt_alias.tag_id.in_(tag_query)) + .group_by(model.Post.post_id) + .join(subquery, pt_alias.post_id == subquery.c.id) + .order_by(sa.func.count(pt_alias.tag_id).desc()) + ) + + +def _create_metric_num_filter(name: str): + def wrapper( + query: SaQuery, + criterion: Optional[criteria.BaseCriterion], + negated: bool, + ) -> SaQuery: + assert criterion + t = sa.orm.aliased(model.TagName) + pm = sa.orm.aliased(model.PostMetric) + expr = t.name == name + expr = expr & search_util.apply_num_criterion_to_column( + pm.value, criterion, search_util.float_transformer + ) + if negated: + expr = ~expr + ret = ( + query.join(pm, pm.post_id == model.Post.post_id) + .join(t, t.tag_id == pm.tag_id) + .filter(expr) + ) + return ret + + return wrapper + + +def _metric_presence_filter( + query: SaQuery, + criterion: Optional[criteria.BaseCriterion], + negated: bool, +) -> SaQuery: + assert criterion + t = sa.orm.aliased(model.TagName) + tag_name_filter = search_util.apply_str_criterion_to_column( + t.name, criterion + ) + pm = sa.orm.aliased(model.PostMetric) + subquery = ( + db.session.query(pm.post_id) + .join(t, t.tag_id == pm.tag_id) + .filter(tag_name_filter) + .subquery() + ) + post_filter = model.Post.post_id.in_(subquery) + if negated: + post_filter = ~post_filter + return query.filter(post_filter) + + +def _create_metric_sort_column(metric_name: str): + t = sa.orm.aliased(model.TagName) + pm = sa.orm.aliased(model.PostMetric) + ret = ( + db.session.query(pm.value) + .filter(pm.post_id == model.Post.post_id) + .join(t, t.tag_id == pm.tag_id) + .filter(t.name == metric_name) + .as_scalar() + ) + return ret + + def _category_filter( query: SaQuery, criterion: Optional[criteria.BaseCriterion], negated: bool ) -> SaQuery: @@ -162,6 +259,10 @@ def _safety_filter( class PostSearchConfig(BaseSearchConfig): def __init__(self) -> None: self.user = None # type: Optional[model.User] + self.all_metric_names = [] + + def refresh_metrics(self) -> None: + self.all_metric_names = metrics.get_all_metric_tag_names() def on_search_query_parsed(self, search_query: SearchQuery) -> SaQuery: new_special_tokens = [] @@ -188,9 +289,11 @@ class PostSearchConfig(BaseSearchConfig): search_query.special_tokens = new_special_tokens def create_around_query(self) -> SaQuery: + self.refresh_metrics() return db.session.query(model.Post).options(sa.orm.lazyload("*")) def create_filter_query(self, disable_eager_loads: bool) -> SaQuery: + self.refresh_metrics() strategy = ( sa.orm.lazyload if disable_eager_loads else sa.orm.subqueryload ) @@ -217,7 +320,9 @@ class PostSearchConfig(BaseSearchConfig): return db.session.query(model.Post) def finalize_query(self, query: SaQuery) -> SaQuery: - if self.user and not auth.has_privilege(self.user, "posts:list:unsafe"): + if self.user and not auth.has_privilege( + self.user, "posts:list:unsafe" + ): # exclude unsafe posts: query = _safety_filter( query, @@ -244,221 +349,268 @@ class PostSearchConfig(BaseSearchConfig): @property def named_filters(self) -> Dict[str, Filter]: - return util.unalias_dict( - [ - (["id"], search_util.create_num_filter(model.Post.post_id)), - ( - ["tag"], - search_util.create_subquery_filter( - model.Post.post_id, - model.PostTag.post_id, - model.TagName.name, - search_util.create_str_filter, - lambda subquery: subquery.join(model.Tag).join( - model.TagName + filters = { + "metric-" + name: _create_metric_num_filter(name) + for name in self.all_metric_names + } + filters.update( + util.unalias_dict( + [ + ( + ["id"], + search_util.create_num_filter(model.Post.post_id), + ), + ( + ["tag"], + search_util.create_subquery_filter( + model.Post.post_id, + model.PostTag.post_id, + model.TagName.name, + search_util.create_str_filter, + lambda subquery: subquery.join(model.Tag).join( + model.TagName + ), ), ), - ), - (["score"], search_util.create_num_filter(model.Post.score)), - (["uploader", "upload", "submit"], _user_filter), - ( - ["comment"], - search_util.create_subquery_filter( - model.Post.post_id, - model.Comment.post_id, - model.User.name, - search_util.create_str_filter, - lambda subquery: subquery.join(model.User), + (["metric"], _metric_presence_filter), + ( + ["score"], + search_util.create_num_filter(model.Post.score), ), - ), - ( - ["fav"], - search_util.create_subquery_filter( - model.Post.post_id, - model.PostFavorite.post_id, - model.User.name, - search_util.create_str_filter, - lambda subquery: subquery.join(model.User), + (["uploader", "upload", "submit"], _user_filter), + ( + ["comment"], + search_util.create_subquery_filter( + model.Post.post_id, + model.Comment.post_id, + model.User.name, + search_util.create_str_filter, + lambda subquery: subquery.join(model.User), + ), ), - ), - (["liked"], _create_score_filter(1)), - (["disliked"], _create_score_filter(-1)), - ( - ["source"], - search_util.create_str_filter( - model.Post.source, _source_transformer + ( + ["fav"], + search_util.create_subquery_filter( + model.Post.post_id, + model.PostFavorite.post_id, + model.User.name, + search_util.create_str_filter, + lambda subquery: subquery.join(model.User), + ), ), - ), - ( - ["tag-count"], - search_util.create_num_filter(model.Post.tag_count), - ), - ( - ["comment-count"], - search_util.create_num_filter(model.Post.comment_count), - ), - ( - ["fav-count"], - search_util.create_num_filter(model.Post.favorite_count), - ), - ( - ["note-count"], - search_util.create_num_filter(model.Post.note_count), - ), - ( - ["relation-count"], - search_util.create_num_filter(model.Post.relation_count), - ), - ( - ["feature-count"], - search_util.create_num_filter(model.Post.feature_count), - ), - ( - ["type"], - search_util.create_str_filter( - model.Post.type, _type_transformer + (["liked"], _create_score_filter(1)), + (["disliked"], _create_score_filter(-1)), + ( + ["source"], + search_util.create_str_filter( + model.Post.source, _source_transformer + ), ), - ), - ( - ["content-checksum", "sha1"], - search_util.create_str_filter(model.Post.checksum), - ), - ( - ["md5"], - search_util.create_str_filter(model.Post.checksum_md5), - ), - ( - ["file-size"], - search_util.create_num_filter(model.Post.file_size), - ), - ( - ["image-width", "width"], - search_util.create_num_filter(model.Post.canvas_width), - ), - ( - ["image-height", "height"], - search_util.create_num_filter(model.Post.canvas_height), - ), - ( - ["image-area", "area"], - search_util.create_num_filter(model.Post.canvas_area), - ), - ( - ["image-aspect-ratio", "image-ar", "aspect-ratio", "ar"], - search_util.create_num_filter( - model.Post.canvas_aspect_ratio, - transformer=search_util.float_transformer, + ( + ["tag-count"], + search_util.create_num_filter(model.Post.tag_count), ), - ), - ( - ["creation-date", "creation-time", "date", "time"], - search_util.create_date_filter(model.Post.creation_time), - ), - ( - [ - "last-edit-date", - "last-edit-time", - "edit-date", - "edit-time", - ], - search_util.create_date_filter(model.Post.last_edit_time), - ), - ( - ["comment-date", "comment-time"], - search_util.create_date_filter( - model.Post.last_comment_creation_time + ( + ["comment-count"], + search_util.create_num_filter( + model.Post.comment_count + ), ), - ), - ( - ["fav-date", "fav-time"], - search_util.create_date_filter( - model.Post.last_favorite_time + ( + ["fav-count"], + search_util.create_num_filter( + model.Post.favorite_count + ), ), - ), - ( - ["feature-date", "feature-time"], - search_util.create_date_filter( - model.Post.last_feature_time + ( + ["note-count"], + search_util.create_num_filter(model.Post.note_count), ), - ), - (["safety", "rating"], _safety_filter), - (["note-text"], _note_filter), - ( - ["flag"], - search_util.create_str_filter( - model.Post.flags_string, _flag_transformer + ( + ["relation-count"], + search_util.create_num_filter( + model.Post.relation_count + ), ), - ), - (["pool"], _pool_filter), - (["category"], _category_filter), - ] + ( + ["feature-count"], + search_util.create_num_filter( + model.Post.feature_count + ), + ), + ( + ["type"], + search_util.create_str_filter( + model.Post.type, _type_transformer + ), + ), + ( + ["content-checksum", "sha1"], + search_util.create_str_filter(model.Post.checksum), + ), + ( + ["md5"], + search_util.create_str_filter(model.Post.checksum_md5), + ), + ( + ["file-size"], + search_util.create_num_filter(model.Post.file_size), + ), + ( + ["image-width", "width"], + search_util.create_num_filter(model.Post.canvas_width), + ), + ( + ["image-height", "height"], + search_util.create_num_filter( + model.Post.canvas_height + ), + ), + ( + ["image-area", "area"], + search_util.create_num_filter(model.Post.canvas_area), + ), + ( + [ + "image-aspect-ratio", + "image-ar", + "aspect-ratio", + "ar", + ], + search_util.create_num_filter( + model.Post.canvas_aspect_ratio, + transformer=search_util.float_transformer, + ), + ), + ( + ["creation-date", "creation-time", "date", "time"], + search_util.create_date_filter( + model.Post.creation_time + ), + ), + ( + [ + "last-edit-date", + "last-edit-time", + "edit-date", + "edit-time", + ], + search_util.create_date_filter( + model.Post.last_edit_time + ), + ), + ( + ["comment-date", "comment-time"], + search_util.create_date_filter( + model.Post.last_comment_creation_time + ), + ), + ( + ["fav-date", "fav-time"], + search_util.create_date_filter( + model.Post.last_favorite_time + ), + ), + ( + ["feature-date", "feature-time"], + search_util.create_date_filter( + model.Post.last_feature_time + ), + ), + (["safety", "rating"], _safety_filter), + (["note-text"], _note_filter), + ( + ["flag"], + search_util.create_str_filter( + model.Post.flags_string, _flag_transformer + ), + ), + (["pool"], _pool_filter), + (["similar"], _similar_filter), + (["category"], _category_filter), + ] + ) ) + return filters @property def sort_columns(self) -> Dict[str, Tuple[SaColumn, str]]: - return util.unalias_dict( - [ - ( - ["random"], - (sa.sql.expression.func.random(), self.SORT_NONE), - ), - (["id"], (model.Post.post_id, self.SORT_DESC)), - (["score"], (model.Post.score, self.SORT_DESC)), - (["tag-count"], (model.Post.tag_count, self.SORT_DESC)), - ( - ["comment-count"], - (model.Post.comment_count, self.SORT_DESC), - ), - (["fav-count"], (model.Post.favorite_count, self.SORT_DESC)), - (["note-count"], (model.Post.note_count, self.SORT_DESC)), - ( - ["relation-count"], - (model.Post.relation_count, self.SORT_DESC), - ), - ( - ["feature-count"], - (model.Post.feature_count, self.SORT_DESC), - ), - (["file-size"], (model.Post.file_size, self.SORT_DESC)), - ( - ["image-width", "width"], - (model.Post.canvas_width, self.SORT_DESC), - ), - ( - ["image-height", "height"], - (model.Post.canvas_height, self.SORT_DESC), - ), - ( - ["image-area", "area"], - (model.Post.canvas_area, self.SORT_DESC), - ), - ( - ["creation-date", "creation-time", "date", "time"], - (model.Post.creation_time, self.SORT_DESC), - ), - ( - [ - "last-edit-date", - "last-edit-time", - "edit-date", - "edit-time", - ], - (model.Post.last_edit_time, self.SORT_DESC), - ), - ( - ["comment-date", "comment-time"], - (model.Post.last_comment_creation_time, self.SORT_DESC), - ), - ( - ["fav-date", "fav-time"], - (model.Post.last_favorite_time, self.SORT_DESC), - ), - ( - ["feature-date", "feature-time"], - (model.Post.last_feature_time, self.SORT_DESC), - ), - ] + filters = { + "metric-" + name: (_create_metric_sort_column(name), self.SORT_ASC) + for name in self.all_metric_names + } + filters.update( + util.unalias_dict( + [ + ( + ["random"], + (sa.sql.expression.func.random(), self.SORT_NONE), + ), + (["id"], (model.Post.post_id, self.SORT_DESC)), + (["score"], (model.Post.score, self.SORT_DESC)), + (["tag-count"], (model.Post.tag_count, self.SORT_DESC)), + ( + ["comment-count"], + (model.Post.comment_count, self.SORT_DESC), + ), + ( + ["fav-count"], + (model.Post.favorite_count, self.SORT_DESC), + ), + (["note-count"], (model.Post.note_count, self.SORT_DESC)), + ( + ["relation-count"], + (model.Post.relation_count, self.SORT_DESC), + ), + ( + ["feature-count"], + (model.Post.feature_count, self.SORT_DESC), + ), + (["file-size"], (model.Post.file_size, self.SORT_DESC)), + ( + ["image-width", "width"], + (model.Post.canvas_width, self.SORT_DESC), + ), + ( + ["image-height", "height"], + (model.Post.canvas_height, self.SORT_DESC), + ), + ( + ["image-area", "area"], + (model.Post.canvas_area, self.SORT_DESC), + ), + ( + ["creation-date", "creation-time", "date", "time"], + (model.Post.creation_time, self.SORT_DESC), + ), + ( + [ + "last-edit-date", + "last-edit-time", + "edit-date", + "edit-time", + ], + (model.Post.last_edit_time, self.SORT_DESC), + ), + ( + ["comment-date", "comment-time"], + ( + model.Post.last_comment_creation_time, + self.SORT_DESC, + ), + ), + ( + ["fav-date", "fav-time"], + (model.Post.last_favorite_time, self.SORT_DESC), + ), + ( + ["feature-date", "feature-time"], + (model.Post.last_feature_time, self.SORT_DESC), + ), + ] + ) ) + return filters @property def special_filters(self) -> Dict[str, Filter]: diff --git a/server/szurubooru/search/configs/util.py b/server/szurubooru/search/configs/util.py index 58e6ebe..659f546 100644 --- a/server/szurubooru/search/configs/util.py +++ b/server/szurubooru/search/configs/util.py @@ -118,6 +118,10 @@ def create_num_filter( return wrapper +def create_float_filter(column: Any) -> SaQuery: + return create_num_filter(column, float_transformer) + + def apply_str_criterion_to_column( column: SaColumn, criterion: criteria.BaseCriterion, diff --git a/server/szurubooru/search/executor.py b/server/szurubooru/search/executor.py index a5ef962..5302b14 100644 --- a/server/szurubooru/search/executor.py +++ b/server/szurubooru/search/executor.py @@ -31,6 +31,8 @@ class Executor: Class for search parsing and execution. Handles plaintext parsing and delegates sqlalchemy filter decoration to SearchConfig instances. """ + AROUND_NEXT = "up" + AROUND_PREV = "down" def __init__(self, search_config: BaseSearchConfig) -> None: self.config = search_config @@ -38,30 +40,29 @@ class Executor: def get_around( self, query_text: str, entity_id: int - ) -> Tuple[model.Base, model.Base]: + ) -> Tuple[model.Base, model.Base, model.Base]: search_query = self.parser.parse(query_text) self.config.on_search_query_parsed(search_query) - filter_query = self.config.create_around_query().options( - sa.orm.lazyload("*") - ) - filter_query = self._prepare_db_query( - filter_query, search_query, False - ) + filter_query = ( + self.config + .create_around_query() + .options(sa.orm.lazyload("*"))) prev_filter_query = ( - filter_query.filter(self.config.id_column > entity_id) - .order_by(None) - .order_by(sa.func.abs(self.config.id_column - entity_id).asc()) - .limit(1) - ) + self._prepare_sorted_around_query( + filter_query, search_query, entity_id, self.AROUND_PREV + ).limit(1)) next_filter_query = ( - filter_query.filter(self.config.id_column < entity_id) - .order_by(None) - .order_by(sa.func.abs(self.config.id_column - entity_id).asc()) - .limit(1) - ) + self._prepare_sorted_around_query( + filter_query, search_query, entity_id, self.AROUND_NEXT + ).limit(1)) + # random post + if "sort:random" not in query_text: + query_text = "sort:random " + query_text + count, random_entities = self.execute(query_text, 0, 1) return ( prev_filter_query.one_or_none(), next_filter_query.one_or_none(), + random_entities[0] if random_entities else None ) def get_around_and_serialize( @@ -76,6 +77,7 @@ class Executor: return { "prev": serializer(entities[0]), "next": serializer(entities[1]), + "random": serializer(entities[2]), } def execute( @@ -94,7 +96,7 @@ class Executor: disable_eager_loads = True key = (id(self.config), hash(search_query), offset, limit) - if cache.has(key): + if not disable_eager_loads and cache.has(key): return cache.get(key) filter_query = self.config.create_filter_query(disable_eager_loads) @@ -131,6 +133,20 @@ class Executor: "results": list([serializer(entity) for entity in entities]), } + def count(self, query_text:str) -> int: + search_query = self.parser.parse(query_text) + self.config.on_search_query_parsed(search_query) + count_query = self.config.create_count_query(True) + count_query = count_query.options(sa.orm.lazyload("*")) + count_query = self._prepare_db_query(count_query, search_query, False) + count_statement = ( + count_query + .statement + .with_only_columns([sa.func.count()]) + .order_by(None)) + count = db.session.execute(count_statement).scalar() + return count + def _prepare_db_query( self, db_query: SaQuery, search_query: SearchQuery, use_sort: bool ) -> SaQuery: @@ -192,3 +208,75 @@ class Executor: db_query = self.config.finalize_query(db_query) return db_query + + def _prepare_sorted_around_query( + self, + db_query: SaQuery, + search_query: SearchQuery, + entity_id: int, + direction: str): + db_query = self._prepare_db_query(db_query, search_query, False) + db_query = db_query.order_by(None) + found_sort_column = False + + for sort_token in search_query.sort_tokens: + if sort_token.name == "random": + continue + if sort_token.name not in self.config.sort_columns: + raise errors.SearchError( + "Unknown sort token: %r. " + "Available sort tokens: %r." % ( + sort_token.name, + _format_dict_keys(self.config.sort_columns))) + column, default_order = ( + self.config.sort_columns[sort_token.name]) + order = _get_order(sort_token.order, default_order) + + # the order column may be joined, so we need to query its value: + column_query = ( + db.session.query(self.config.id_column, column) + .options(sa.orm.lazyload("*"))) + column_query = ( + # empty search query because we already know entity id + self._prepare_db_query(column_query, SearchQuery(), False) + .filter(self.config.id_column == entity_id)) + id, column_value = column_query.one_or_none() + # it's possible that this entity doesn't have the column + if not column_value: + continue + found_sort_column = True + + if order == sort_token.SORT_ASC: + if direction == self.AROUND_NEXT: + db_query = ( + db_query + .order_by(column.asc()) + .filter(column > column_value)) + elif direction == self.AROUND_PREV: + db_query = ( + db_query + .order_by(column.desc()) + .filter(column < column_value)) + elif order == sort_token.SORT_DESC: + if direction == self.AROUND_NEXT: + db_query = ( + db_query + .order_by(column.desc()) + .filter(column < column_value)) + elif direction == self.AROUND_PREV: + db_query = ( + db_query + .order_by(column.asc()) + .filter(column > column_value)) + + if not found_sort_column: + # no sorting, use default sorting by id + if direction == self.AROUND_NEXT: + db_query = db_query.filter(self.config.id_column < entity_id) + elif direction == self.AROUND_PREV: + db_query = db_query.filter(self.config.id_column > entity_id) + db_query = db_query.order_by( + sa.func.abs(self.config.id_column - entity_id).asc()) + return db_query + + return db_query diff --git a/server/szurubooru/tests/api/test_metric_retrieving.py b/server/szurubooru/tests/api/test_metric_retrieving.py new file mode 100644 index 0000000..81e29cd --- /dev/null +++ b/server/szurubooru/tests/api/test_metric_retrieving.py @@ -0,0 +1,63 @@ +from szurubooru import api, db, model + +import pytest + + +@pytest.fixture(autouse=True) +def inject_config(config_injector): + config_injector( + { + "privileges": { + "metrics:list": model.User.RANK_REGULAR, + }, + } + ) + +@pytest.mark.parametrize('query,expected_value', [ + ('', 5), + ('mytag:0..', 5), + ('mytag:..10', 5), + ('mytag:0..10', 5), + ('mytag:2..8', 5), + ('mytag:0..8', 4), + ('mytag:0..6', 4), + ('mytag:0..5.5', 4), + ('mytag:0..4', 1), + ('mytag:1..4', 1), + ('mytag:2..3', None), +]) +def test_median( + query, + expected_value, + tag_factory, + post_factory, + metric_factory, + post_metric_factory, + context_factory, + user_factory): + tag = tag_factory(names=['mytag']) + post1 = post_factory(tags=[tag]) + post4 = post_factory(tags=[tag]) + post5 = post_factory(tags=[tag]) + post6 = post_factory(tags=[tag]) + post10 = post_factory(tags=[tag]) + metric = metric_factory(tag=tag, min=0, max=10) + pm1 = post_metric_factory(metric=metric, post=post1, value=1) + pm4 = post_metric_factory(metric=metric, post=post4, value=4) + pm5 = post_metric_factory(metric=metric, post=post5, value=5) + pm6 = post_metric_factory(metric=metric, post=post6, value=6) + pm10 = post_metric_factory(metric=metric, post=post10, value=10) + db.session.add_all([tag, metric, pm1, pm4, pm5, pm6, pm10, + post1, post4, post5, post6, post10]) + db.session.flush() + response = api.metric_api.get_post_metrics_median( + context_factory( + params={'query': query}, + user=user_factory(rank=model.User.RANK_REGULAR)), + {'tag_name': 'mytag'}) + if not expected_value: + assert response['total'] == 0 + assert len(response['results']) == 0 + else: + assert response['total'] == 1 + assert response['results'][0]['value'] == expected_value diff --git a/server/szurubooru/tests/api/test_post_retrieving.py b/server/szurubooru/tests/api/test_post_retrieving.py index a40ab0e..b2d5c8e 100644 --- a/server/szurubooru/tests/api/test_post_retrieving.py +++ b/server/szurubooru/tests/api/test_post_retrieving.py @@ -11,6 +11,8 @@ from szurubooru.func import posts def inject_config(config_injector): config_injector( { + "data_url": "http://example.com/", + "secret": "test", "privileges": { "posts:list": model.User.RANK_REGULAR, "posts:view": model.User.RANK_REGULAR, @@ -131,6 +133,53 @@ def test_trying_to_retrieve_single_without_privileges( ) +@pytest.mark.parametrize( + "query,expected_id", + [ + ("sort:id,asc", 2), + ("sort:id,asc id:2..", 2), + ("sort:id,desc id:2..", 3), + ("sort:id,asc id:3..", 3), + ("sort:id,desc id:3..", 3), + ("sort:id id:4..", None), + ("sort:tag-count", 3), + ("sort:tag-count,asc id:..2", 1), + ("sort:tag-count,desc id:..2", 2), + ], +) +def test_median( + query, + expected_id, + post_factory, + tag_factory, + context_factory, + user_factory, +): + tag1 = tag_factory() + tag2 = tag_factory() + tag3 = tag_factory() + post1 = post_factory(id=1, tags=[tag1]) + post2 = post_factory(id=2, tags=[tag1, tag2, tag3]) + post3 = post_factory(id=3, tags=[tag1, tag2]) + db.session.add_all([tag1, tag2, tag3, post1, post2, post3]) + db.session.flush() + with patch("szurubooru.func.comments.serialize_comment"), patch( + "szurubooru.func.users.serialize_micro_user" + ), patch("szurubooru.func.posts.files.has"): + response = api.post_api.get_posts_median( + context_factory( + params={"query": query}, + user=user_factory(rank=model.User.RANK_REGULAR), + ) + ) + if not expected_id: + assert response["total"] == 0 + assert len(response["results"]) == 0 + else: + assert response["total"] == 1 + assert response["results"][0]["id"] == expected_id + + def test_trying_to_retrieve_unsafe_without_privileges( user_factory, context_factory, post_factory, config_injector ): diff --git a/server/szurubooru/tests/api/test_post_updating.py b/server/szurubooru/tests/api/test_post_updating.py index e4a606d..7d830c9 100644 --- a/server/szurubooru/tests/api/test_post_updating.py +++ b/server/szurubooru/tests/api/test_post_updating.py @@ -4,7 +4,7 @@ from unittest.mock import patch import pytest from szurubooru import api, db, errors, model -from szurubooru.func import net, posts, snapshots, tags +from szurubooru.func import metrics, net, posts, snapshots, tags @pytest.fixture(autouse=True) @@ -21,6 +21,7 @@ def inject_config(config_injector): "posts:edit:flags": model.User.RANK_REGULAR, "posts:edit:thumbnail": model.User.RANK_REGULAR, "tags:create": model.User.RANK_MODERATOR, + "metrics:edit:posts": model.User.RANK_REGULAR, "uploads:use_downloader": model.User.RANK_REGULAR, }, "allow_broken_uploads": False, @@ -54,6 +55,10 @@ def test_post_updating( "szurubooru.func.posts.serialize_post" ), patch( "szurubooru.func.snapshots.modify" + ), patch( + "szurubooru.func.metrics.update_or_create_post_metrics" + ), patch( + "szurubooru.func.metrics.update_or_create_post_metric_ranges" ), fake_datetime( "1997-01-01" ): @@ -69,6 +74,8 @@ def test_post_updating( "source": "source", "notes": ["note1", "note2"], "flags": ["flag1", "flag2"], + "metrics": [{"tag_name": "tag1", "value": 1.2}], + "metricRanges": [{"tag_name": "tag2", "low": 1, "high": 2}], }, files={ "content": "post-content", @@ -99,6 +106,10 @@ def test_post_updating( post, auth_user, options=[] ) snapshots.modify.assert_called_once_with(post, auth_user) + metrics.update_or_create_post_metrics.assert_called_once_with( + post, [{"tag_name": "tag1", "value": 1.2}]) + metrics.update_or_create_post_metric_ranges.assert_called_once_with( + post, [{"tag_name": "tag2", "low": 1, "high": 2}]) assert post.last_edit_time == datetime(1997, 1, 1) @@ -184,6 +195,8 @@ def test_trying_to_update_non_existing(context_factory, user_factory): ({}, {"flags": "..."}), ({"content": "..."}, {}), ({"thumbnail": "..."}, {}), + ({}, {"metrics": "..."}), + ({}, {"metricRanges": "..."}), ], ) def test_trying_to_update_field_without_privileges( diff --git a/server/szurubooru/tests/api/test_tag_updating.py b/server/szurubooru/tests/api/test_tag_updating.py index be5f485..66939a4 100644 --- a/server/szurubooru/tests/api/test_tag_updating.py +++ b/server/szurubooru/tests/api/test_tag_updating.py @@ -3,7 +3,7 @@ from unittest.mock import patch import pytest from szurubooru import api, db, errors, model -from szurubooru.func import snapshots, tags +from szurubooru.func import metrics, snapshots, tags @pytest.fixture(autouse=True) @@ -17,6 +17,8 @@ def inject_config(config_injector): "tags:edit:description": model.User.RANK_REGULAR, "tags:edit:suggestions": model.User.RANK_REGULAR, "tags:edit:implications": model.User.RANK_REGULAR, + "metrics:create": model.User.RANK_REGULAR, + "metrics:edit:bounds": model.User.RANK_REGULAR, }, } ) @@ -40,6 +42,8 @@ def test_simple_updating(user_factory, tag_factory, context_factory): ), patch( "szurubooru.func.tags.serialize_tag" ), patch( + "szurubooru.func.metrics.update_or_create_metric" + ), patch( "szurubooru.func.snapshots.modify" ): tags.get_or_create_tags_by_names.return_value = ([], []) @@ -53,6 +57,7 @@ def test_simple_updating(user_factory, tag_factory, context_factory): "description": "desc", "suggestions": ["sug1", "sug2"], "implications": ["imp1", "imp2"], + "metric": {"min": -1, "max": 1}, }, user=auth_user, ), @@ -70,6 +75,8 @@ def test_simple_updating(user_factory, tag_factory, context_factory): tag, ["imp1", "imp2"] ) tags.serialize_tag.assert_called_once_with(tag, options=[]) + metrics.update_or_create_metric.assert_called_once_with( + tag, {"min": -1, "max": 1}) snapshots.modify.assert_called_once_with(tag, auth_user) @@ -128,6 +135,7 @@ def test_trying_to_update_non_existing(user_factory, context_factory): {"category": "whatever"}, {"suggestions": ["whatever"]}, {"implications": ["whatever"]}, + {"metric": ["whatever"]}, ], ) def test_trying_to_update_without_privileges( @@ -145,6 +153,20 @@ def test_trying_to_update_without_privileges( ) +def test_trying_to_create_metric_without_privileges( + user_factory, tag_factory, context_factory +): + db.session.add(tag_factory(names=["tag"])) + db.session.commit() + with pytest.raises(errors.AuthError): + api.tag_api.update_tag( + context_factory( + params={"metric": {"min": 0, "max": 10}, **{"version": 1}}, + user=user_factory(rank=model.User.RANK_ANONYMOUS)), + {"tag_name": "tag"} + ) + + @pytest.mark.parametrize("type", ["suggestions", "implications"]) def test_trying_to_create_tags_without_privileges( config_injector, context_factory, tag_factory, user_factory, type diff --git a/server/szurubooru/tests/conftest.py b/server/szurubooru/tests/conftest.py index 280987c..45113f5 100644 --- a/server/szurubooru/tests/conftest.py +++ b/server/szurubooru/tests/conftest.py @@ -151,7 +151,7 @@ def tag_category_factory(): @pytest.fixture def tag_factory(): - def factory(names=None, category=None): + def factory(names=None, category=None, metric=None): if not category: category = model.TagCategory(get_unique_name()) db.session.add(category) @@ -161,6 +161,8 @@ def tag_factory(): tag.names.append(model.TagName(name, i)) tag.category = category tag.creation_time = datetime(1996, 1, 1) + if metric: + tag.metric = metric return tag return factory @@ -173,6 +175,7 @@ def post_factory(): safety=model.Post.SAFETY_SAFE, type=model.Post.TYPE_IMAGE, checksum="...", + tags=[], ): post = model.Post() post.post_id = id @@ -182,6 +185,7 @@ def post_factory(): post.flags = [] post.mime_type = "application/octet-stream" post.creation_time = datetime(1996, 1, 1) + post.tags = tags return post return factory @@ -286,6 +290,53 @@ def pool_post_factory(pool_factory, post_factory): @pytest.fixture +def metric_factory(tag_factory): + def factory(tag=None, min=0, max=10): + if not tag: + tag = tag_factory() + return model.Metric(tag=tag, min=min, max=max) + return factory + + +@pytest.fixture +def post_metric_factory(post_factory, tag_factory, metric_factory): + def factory(post=None, metric=None, value=None, tag=None, tag_name=None): + if not post: + post = post_factory() + if tag_name: + tag = tag_factory(names=[tag_name]) + if tag: + metric = metric_factory(tag=tag) + elif not metric: + metric = metric_factory() + if not value: + value = (metric.min + metric.max)/2 + return model.PostMetric(post=post, metric=metric, value=value) + return factory + + +@pytest.fixture +def post_metric_range_factory(post_factory, tag_factory, metric_factory): + def factory(post=None, metric=None, low=None, high=None, tag=None, + tag_name=None): + if not post: + post = post_factory() + if tag_name: + tag = tag_factory(names=[tag_name]) + if tag: + metric = metric_factory(tag=tag) + elif not metric: + metric = metric_factory() + if not low: + low = metric.min + if not high: + high = metric.max + return model.PostMetricRange( + post=post, metric=metric, low=low, high=high) + return factory + + +@pytest.fixture def read_asset(): def get(path): path = os.path.join(os.path.dirname(__file__), "assets", path) diff --git a/server/szurubooru/tests/func/test_metrics.py b/server/szurubooru/tests/func/test_metrics.py new file mode 100644 index 0000000..c33be62 --- /dev/null +++ b/server/szurubooru/tests/func/test_metrics.py @@ -0,0 +1,459 @@ +import pytest +from szurubooru import db, model +from szurubooru.func import metrics + + +def test_serialize_metric(tag_category_factory, tag_factory): + cat = tag_category_factory(name="cat") + tag = tag_factory(names=["tag1"], category=cat) + metric = model.Metric(tag=tag, min=1, max=2) + db.session.add(metric) + db.session.flush() + result = metrics.serialize_metric(metric) + assert result == { + "version": 1, + "min": 1, + "max": 2, + "exact_count": 0, + "range_count": 0, + "tag": { + "names": ["tag1"], + "category": "cat", + "description": None, + "usages": 0, + }, + } + + +def test_serialize_post_metric(post_factory, tag_factory, metric_factory): + tag = tag_factory(names=["mytag"]) + post = post_factory(id=456, tags=[tag]) + metric = metric_factory(tag) + post_metric = model.PostMetric(post=post, metric=metric, value=-12.3) + db.session.add_all([post, tag, metric, post_metric]) + db.session.flush() + result = metrics.serialize_post_metric(post_metric) + assert result == { + "tag_name": "mytag", + "post_id": 456, + "value": -12.3, + } + + +def test_serialize_post_metric_range(post_factory, tag_factory, metric_factory): + tag = tag_factory(names=["mytag"]) + post = post_factory(id=456, tags=[tag]) + metric = metric_factory(tag) + post_metric_range = model.PostMetricRange( + post=post, metric=metric, low=-1.2, high=3.4) + db.session.add_all([post, tag, metric, post_metric_range]) + db.session.flush() + result = metrics.serialize_post_metric_range(post_metric_range) + assert result == { + "tag_name": "mytag", + "post_id": 456, + "low": -1.2, + "high": 3.4 + } + + +def test_try_get_metric_by_tag_name(tag_factory, metric_factory): + tag = tag_factory(names=["mytag"]) + metric = metric_factory(tag) + db.session.add_all([tag, metric]) + db.session.flush() + assert metrics.try_get_metric_by_tag_name("unknown") is None + assert metrics.try_get_metric_by_tag_name("mytag") is metric + + +def test_try_get_post_metric( + post_factory, metric_factory, post_metric_factory): + metric1 = metric_factory() + metric2 = metric_factory() + post = post_factory(tags=[metric1.tag, metric2.tag]) + post_metric = post_metric_factory(post=post, metric=metric1) + db.session.add_all([post, metric1, metric2, post_metric]) + db.session.flush() + assert metrics.try_get_post_metric(post, metric2) is None + assert metrics.try_get_post_metric(post, metric1) is post_metric + + +def test_try_get_post_metric_range( + post_factory, metric_factory, post_metric_range_factory): + metric1 = metric_factory() + metric2 = metric_factory() + post = post_factory(tags=[metric1.tag, metric2.tag]) + post_metric_range = post_metric_range_factory(post=post, metric=metric1) + db.session.add_all([post, metric1, metric2, post_metric_range]) + db.session.flush() + assert metrics.try_get_post_metric_range(post, metric2) is None + assert metrics.try_get_post_metric_range(post, metric1) is post_metric_range + + +def test_get_all_metrics(metric_factory): + metric1 = metric_factory() + metric2 = metric_factory() + metric3 = metric_factory() + db.session.add_all([metric1, metric2, metric3]) + db.session.flush() + all_metrics = metrics.get_all_metrics() + assert len(all_metrics) == 3 + assert metric1 in all_metrics + assert metric2 in all_metrics + assert metric3 in all_metrics + + +def test_get_all_metric_tag_names(tag_factory, metric_factory): + tag1 = tag_factory(names=["abc", "def"]) + tag2 = tag_factory(names=["ghi"]) + metric1 = metric_factory(tag=tag1) + metric2 = metric_factory(tag=tag2) + db.session.add_all([metric1, metric2]) + db.session.flush() + assert metrics.get_all_metric_tag_names() == ["abc", "def", "ghi"] + + +def test_create_metric(tag_factory): + tag = tag_factory() + db.session.add(tag) + new_metric = metrics.create_metric(tag, 1, 2) + assert new_metric is not None + db.session.flush() + assert tag.metric is not None + assert tag.metric.min == 1 + assert tag.metric.max == 2 + + +def test_create_metric_with_existing_metric(tag_factory): + tag = tag_factory() + tag.metric = model.Metric() + with pytest.raises(metrics.MetricAlreadyExistsError): + metrics.create_metric(tag, 1, 2) + + +def test_create_metric_with_invalid_params(tag_factory): + tag = tag_factory() + with pytest.raises(metrics.InvalidMetricError): + metrics.create_metric(tag, 2, 1) + + +def test_update_or_create_metric(tag_factory): + tag = tag_factory() + db.session.add(tag) + new_metric = metrics.update_or_create_metric(tag, {"min": 1, "max": 2}) + assert new_metric is not None + db.session.flush() + assert tag.metric is not None + assert tag.metric.min == 1 + assert tag.metric.max == 2 + assert tag.metric.version == 1 + + new_metric = metrics.update_or_create_metric(tag, {"min": 3, "max": 4}) + assert new_metric is None + db.session.flush() + assert tag.metric.min == 3 + assert tag.metric.max == 4 + assert tag.metric.version == 2 + + +@pytest.mark.parametrize("params", [ + {"min": 1}, {"max": 2}, {"min": 2, "max": 1} +]) +def test_update_or_create_metric_with_invalid_params(tag_factory, params): + tag = tag_factory() + with pytest.raises(metrics.InvalidMetricError): + metrics.update_or_create_metric(tag, params) + + +# Post metrics + +def test_update_or_create_post_metric_without_tag(post_factory, metric_factory): + post = post_factory() + metric = metric_factory() + with pytest.raises(metrics.PostMissingTagError): + metrics.update_or_create_post_metric(post, metric, 1.5) + + +def test_update_or_create_post_metric_with_value_out_of_range( + post_factory, metric_factory): + metric = metric_factory() + post = post_factory(tags=[metric.tag]) + with pytest.raises(metrics.MetricValueOutOfRangeError): + metrics.update_or_create_post_metric(post, metric, -99) + + +def test_update_or_create_post_metric_create(post_factory, metric_factory): + metric = metric_factory() + post = post_factory(tags=[metric.tag]) + db.session.add(metric) + db.session.flush() + post_metric = metrics.update_or_create_post_metric(post, metric, 1.5) + assert post_metric.value == 1.5 + + +def test_update_or_create_post_metric_update(post_factory, metric_factory): + metric = metric_factory() + post1 = post_factory(tags=[metric.tag]) + post2 = post_factory(tags=[metric.tag]) + post_metric1 = model.PostMetric(post=post1, metric=metric, value=1.2) + post_metric2 = model.PostMetric(post=post2, metric=metric, value=5.6) + db.session.add_all([post1, post2, post_metric1, post_metric2]) + db.session.flush() + assert post_metric1.version == 1 + assert post_metric2.version == 1 + + metrics.update_or_create_post_metric(post1, metric, 3.4) + db.session.flush() + + assert db.session.query(model.PostMetric).count() == 2 + assert post_metric1.value == 3.4 + assert post_metric1.version == 2 + assert post_metric2.value == 5.6 + assert post_metric2.version == 1 + + +def test_update_or_create_post_metrics_missing_tag( + post_factory, tag_factory, metric_factory): + post = post_factory() + tag = tag_factory(names=["tag1"]) + metric = metric_factory(tag) + db.session.add(metric) + db.session.flush() + data = [{"tag_name": "tag1", "value": 1.5}] + with pytest.raises(metrics.PostMissingTagError): + metrics.update_or_create_post_metrics(post, data) + + +@pytest.mark.parametrize("params", [ + [{}], + [{"tag_name": "tag"}], + [{"value": 1.5}] +]) +def test_update_or_create_post_metrics_with_missing_fields( + params, post_factory): + post = post_factory() + with pytest.raises(metrics.InvalidMetricError): + metrics.update_or_create_post_metrics(post, params) + + +def test_update_or_create_post_metrics_with_invalid_tag( + post_factory, tag_factory): + tag = tag_factory(names=["tag1"]) + post = post_factory(tags=[tag]) + db.session.add(tag) + db.session.flush() + data = [{"tag_name": "tag1", "value": 2}] + with pytest.raises(metrics.MetricDoesNotExistsError): + metrics.update_or_create_post_metrics(post, data) + + +def test_update_or_create_post_metrics( + post_factory, tag_factory, metric_factory): + tag1 = tag_factory(names=["tag1"]) + tag2 = tag_factory(names=["tag2"]) + post = post_factory(tags=[tag1, tag2]) + metric1 = metric_factory(tag1) + metric2 = metric_factory(tag2) + db.session.add_all([metric1, metric2]) + db.session.flush() + + data = [ + {"tag_name": "tag1", "value": 1.2}, + {"tag_name": "tag2", "value": 3.4}, + ] + metrics.update_or_create_post_metrics(post, data) + db.session.flush() + + assert len(post.metrics) == 2 + assert post.metrics[0].value == 1.2 + assert post.metrics[1].value == 3.4 + + +def test_update_or_create_post_metrics_with_trim( + post_factory, tag_factory, metric_factory, post_metric_factory): + tag1 = tag_factory(names=["tag1"]) + tag2 = tag_factory(names=["tag2"]) + post = post_factory(tags=[tag1, tag2]) + metric1 = metric_factory(tag1) + metric2 = metric_factory(tag2) + post_metric = post_metric_factory(post=post, metric=metric1, value=1.2) + db.session.add_all([post, tag1, tag2, metric1, metric2, post_metric]) + db.session.flush() + assert len(post.metrics) == 1 + assert post.metrics[0].metric == metric1 + assert post.metrics[0].value == 1.2 + + data = [ + {"tag_name": "tag2", "value": 3.4}, + ] + metrics.update_or_create_post_metrics(post, data) + db.session.flush() + + assert len(post.metrics) == 1 + assert post.metrics[0].metric == metric2 + assert post.metrics[0].value == 3.4 + + +# Post metric ranges + +def test_update_or_create_post_metric_range_without_tag( + post_factory, metric_factory): + post = post_factory() + metric = metric_factory() + with pytest.raises(metrics.PostMissingTagError): + metrics.update_or_create_post_metric_range(post, metric, 2, 3) + + +@pytest.mark.parametrize("low, high", [ + (-99, 1), (1, 99), +]) +def test_update_or_create_post_metric_range_with_values_out_of_range( + low, high, post_factory, metric_factory): + metric = metric_factory() + post = post_factory(tags=[metric.tag]) + with pytest.raises(metrics.MetricValueOutOfRangeError): + metrics.update_or_create_post_metric_range(post, metric, low, high) + + +def test_update_or_create_post_metric_range_create( + post_factory, metric_factory): + metric = metric_factory() + post = post_factory(tags=[metric.tag]) + db.session.add(metric) + db.session.flush() + post_metric_range = metrics.update_or_create_post_metric_range( + post, metric, 2, 3) + assert post_metric_range.low == 2 + assert post_metric_range.high == 3 + + +def test_update_or_create_post_metric_range_update( + post_factory, metric_factory): + metric = metric_factory() + post = post_factory(tags=[metric.tag]) + post_metric_range = model.PostMetricRange( + post=post, metric=metric, low=2, high=3) + db.session.add(post_metric_range) + db.session.flush() + assert post_metric_range.version == 1 + + metrics.update_or_create_post_metric_range(post, metric, 4, 5) + db.session.flush() + + assert post_metric_range.low == 4 + assert post_metric_range.high == 5 + assert post_metric_range.version == 2 + + +def test_update_or_create_post_metric_ranges_missing_tag( + post_factory, tag_factory, metric_factory): + post = post_factory() + tag = tag_factory(names=["tag1"]) + metric = metric_factory(tag) + db.session.add(metric) + db.session.flush() + data = [{"tag_name": "tag1", "low": 2, "high": 3}] + with pytest.raises(metrics.PostMissingTagError): + metrics.update_or_create_post_metric_ranges(post, data) + + +@pytest.mark.parametrize("params", [ + [{}], + [{"tag_name": "tag"}], + [{"tag_name": "tag", "low": 2}], + [{"low": 2, "high": 3}], +]) +def test_update_or_create_post_metric_ranges_with_missing_fields( + params, post_factory, tag_factory): + tag = tag_factory(names=["tag"]) + post = post_factory(tags=[tag]) + with pytest.raises(metrics.InvalidMetricError): + metrics.update_or_create_post_metric_ranges(post, params) + + +def test_update_or_create_post_metric_ranges_with_invalid_tag( + post_factory, tag_factory): + tag = tag_factory(names=["tag1"]) + post = post_factory(tags=[tag]) + db.session.add(tag) + db.session.flush() + data = [{"tag_name": "tag1", "low": 2, "high": 3}] + with pytest.raises(metrics.MetricDoesNotExistsError): + metrics.update_or_create_post_metric_ranges(post, data) + + +def test_update_or_create_post_metric_ranges_with_invalid_values( + post_factory, tag_factory, metric_factory): + tag = tag_factory(names=["tag1"]) + post = post_factory(tags=[tag]) + metric = metric_factory(tag=tag) + db.session.add_all([metric, tag]) + db.session.flush() + data = [ + {"tag_name": "tag1", "low": 4, "high": 2}, + ] + with pytest.raises(metrics.InvalidMetricError): + metrics.update_or_create_post_metric_ranges(post, data) + + +def test_update_or_create_post_metric_ranges( + post_factory, tag_factory, metric_factory): + tag1 = tag_factory(names=["tag1"]) + tag2 = tag_factory(names=["tag2"]) + post = post_factory(tags=[tag1, tag2]) + metric1 = metric_factory(tag1) + metric2 = metric_factory(tag2) + db.session.add_all([metric1, metric2]) + db.session.flush() + + data = [ + {"tag_name": "tag1", "low": 2, "high": 3}, + {"tag_name": "tag2", "low": 4, "high": 5}, + ] + metrics.update_or_create_post_metric_ranges(post, data) + db.session.flush() + + assert len(post.metric_ranges) == 2 + assert post.metric_ranges[0].low == 2 + assert post.metric_ranges[0].high == 3 + assert post.metric_ranges[1].low == 4 + assert post.metric_ranges[1].high == 5 + + +def test_update_or_create_post_metric_ranges_with_trim( + post_factory, tag_factory, metric_factory, post_metric_range_factory): + tag1 = tag_factory(names=["tag1"]) + tag2 = tag_factory(names=["tag2"]) + post = post_factory(tags=[tag1, tag2]) + metric1 = metric_factory(tag1) + metric2 = metric_factory(tag2) + post_metric_range = post_metric_range_factory( + post=post, metric=metric1, low=1, high=2) + db.session.add_all([post, tag1, tag2, metric1, metric2, post_metric_range]) + db.session.flush() + assert len(post.metric_ranges) == 1 + assert post.metric_ranges[0].metric == metric1 + assert post.metric_ranges[0].low == 1 + assert post.metric_ranges[0].high == 2 + + data = [ + {"tag_name": "tag2", "low": 3, "high": 4}, + ] + metrics.update_or_create_post_metric_ranges(post, data) + db.session.flush() + + assert len(post.metric_ranges) == 1 + assert post.metric_ranges[0].metric == metric2 + assert post.metric_ranges[0].low == 3 + assert post.metric_ranges[0].high == 4 + + +def test_delete_metric(metric_factory): + metric1 = metric_factory() + metric2 = metric_factory() + db.session.add_all([metric1, metric2]) + db.session.flush() + assert db.session.query(model.Metric).count() == 2 + metrics.delete_metric(metric2) + db.session.flush() + assert db.session.query(model.Metric).count() == 1 diff --git a/server/szurubooru/tests/func/test_posts.py b/server/szurubooru/tests/func/test_posts.py index fa1b3bb..168a74a 100644 --- a/server/szurubooru/tests/func/test_posts.py +++ b/server/szurubooru/tests/func/test_posts.py @@ -8,13 +8,13 @@ from szurubooru import db, model from szurubooru.func import ( comments, files, - image_hash, images, posts, tags, users, util, ) +from szurubooru.func.posts import _get_safety_list @pytest.mark.parametrize( @@ -101,6 +101,9 @@ def test_serialize_post( comment_factory, tag_factory, tag_category_factory, + metric_factory, + post_metric_factory, + post_metric_range_factory, pool_factory, pool_category_factory, config_injector, @@ -122,14 +125,23 @@ def test_serialize_post( post.post_id = 1 post.creation_time = datetime(1997, 1, 1) post.last_edit_time = datetime(1998, 1, 1) - post.tags = [ - tag_factory( - names=["tag1", "tag2"], - category=tag_category_factory("test-cat1"), - ), - tag_factory( - names=["tag3"], category=tag_category_factory("test-cat2") - ), + tag1 = tag_factory( + names=["tag1", "tag2"], + category=tag_category_factory("test-cat1") + ) + tag1.metric = metric_factory(tag=tag1, min=-2.5, max=2.5) + tag3 = tag_factory( + names=["tag3"], + category=tag_category_factory("test-cat2") + ) + post.tags = [tag1, tag3] + post.metrics = [ + post_metric_factory(post=post, metric=tag1.metric, value=-1.2) + ] + post.metric_ranges = [ + post_metric_range_factory( + post=post, metric=tag1.metric, low=2, high=3 + ) ] post.safety = model.Post.SAFETY_SAFE post.source = "4gag" @@ -233,11 +245,16 @@ def test_serialize_post( "names": ["tag1", "tag2"], "category": "test-cat1", "usages": 1, + "metric": { + "min": -2.5, + "max": 2.5 + }, }, { "names": ["tag3"], "category": "test-cat2", "usages": 1, + "metric": None, }, ], "relations": [], @@ -273,6 +290,21 @@ def test_serialize_post( "hasCustomThumbnail": True, "mimeType": "image/jpeg", "comments": ["commenter1", "commenter2"], + "metrics": [ + { + "tag_name": "tag1", + "post_id": 1, + "value": -1.2 + } + ], + "metricRanges": [ + { + "tag_name": "tag1", + "post_id": 1, + "low": 2, + "high": 3 + } + ], } @@ -1221,3 +1253,12 @@ def test_search_by_image(post_factory, config_injector, read_asset): result2 = posts.search_by_image(read_asset("png.png")) assert not result2 + + +def test_get_safety_list(): + assert _get_safety_list('') == ['safe', 'sketchy', 'unsafe'] + assert _get_safety_list('abc') == ['safe', 'sketchy', 'unsafe'] + assert _get_safety_list('abc rating:lol -def') ==\ + ['safe', 'sketchy', 'unsafe'] + assert _get_safety_list('abc -rating:sketchy,lol def') == ['safe', 'unsafe'] + assert _get_safety_list('rating:safe,unsafe -rating:safe') == ['unsafe'] diff --git a/server/szurubooru/tests/func/test_similar.py b/server/szurubooru/tests/func/test_similar.py new file mode 100644 index 0000000..5346cb0 --- /dev/null +++ b/server/szurubooru/tests/func/test_similar.py @@ -0,0 +1,60 @@ +import pytest +from szurubooru import db +from szurubooru.func import similar + + +@pytest.fixture +def verify_posts(): + def verify(actual_posts, expected_posts): + actual_post_ids = list([p.post_id for p in actual_posts]) + expected_post_ids = list([p.post_id for p in expected_posts]) + assert actual_post_ids == expected_post_ids + + return verify + + +def test_find_similar_posts(post_factory, tag_factory, verify_posts): + tagA = tag_factory(names=["a"]) + tagB = tag_factory(names=["b"]) + tagC = tag_factory(names=["c"]) + postA = post_factory(id=1, tags=[tagA]) + postAB = post_factory(id=2, tags=[tagA, tagB]) + postAC = post_factory(id=3, tags=[tagA, tagC]) + postABC = post_factory(id=4, tags=[tagA, tagB, tagC]) + postBC = post_factory(id=5, tags=[tagB, tagC]) + db.session.add_all([tagA, tagB, tagC, postA, postAB, postAC, postABC, postBC]) + db.session.flush() + + results = similar.find_similar_posts(postBC, 10) + verify_posts(results, [postABC, postAC, postAB]) + + results = similar.find_similar_posts(postBC, 2) + verify_posts(results, [postABC, postAC]) + + results = similar.find_similar_posts(postABC, 10) + verify_posts(results, [postBC, postAC, postAB, postA]) + + results = similar.find_similar_posts(postA, 10) + verify_posts(results, [postABC, postAC, postAB]) # sorted by id + + results = similar.find_similar_posts(postAB, 10) + verify_posts(results, [postABC, postBC, postAC, postA]) + + results = similar.find_similar_posts(postAC, 10) + verify_posts(results, [postABC, postBC, postAB, postA]) + + +def test_find_similar_posts_with_limit(post_factory, tag_factory, verify_posts): + tagA = tag_factory(names=["a"]) + tagB = tag_factory(names=["b"]) + tagC = tag_factory(names=["c"]) + tagD = tag_factory(names=["d"]) + tagE = tag_factory(names=["e"]) + postA = post_factory(id=111, tags=[tagA]) + postAB = post_factory(id=112, tags=[tagA, tagB]) + postABCDE = post_factory(id=113, tags=[tagA, tagB, tagC, tagD, tagE]) + db.session.add_all([tagA, tagB, tagC, tagD, tagE, postA, postAB, postABCDE]) + db.session.flush() + + results = similar.find_similar_posts(postABCDE, 10) + verify_posts(results, [postAB, postA]) diff --git a/server/szurubooru/tests/func/test_tags.py b/server/szurubooru/tests/func/test_tags.py index 60df122..79376f4 100644 --- a/server/szurubooru/tests/func/test_tags.py +++ b/server/szurubooru/tests/func/test_tags.py @@ -66,7 +66,12 @@ def test_serialize_tag_when_empty(): assert tags.serialize_tag(None, None) is None -def test_serialize_tag(post_factory, tag_factory, tag_category_factory): +def test_serialize_tag( + post_factory, + tag_factory, + tag_category_factory, + metric_factory, +): cat = tag_category_factory(name="cat") tag = tag_factory(names=["tag1", "tag2"], category=cat) # tag.tag_id = 1 @@ -81,6 +86,8 @@ def test_serialize_tag(post_factory, tag_factory, tag_category_factory): ] tag.last_edit_time = datetime(1998, 1, 1) + tag.metric = metric_factory(tag, min=1.5, max=10) + post1 = post_factory() post1.tags = [tag] post2 = post_factory() @@ -106,6 +113,11 @@ def test_serialize_tag(post_factory, tag_factory, tag_category_factory): {"names": ["impl1"], "category": "cat", "usages": 0}, {"names": ["impl2"], "category": "cat", "usages": 0}, ], + "metric": { + "version": 1, + "min": 1.5, + "max": 10 + }, "usages": 2, } @@ -318,6 +330,22 @@ def test_merge_tags_with_itself(tag_factory): tags.merge_tags(source_tag, source_tag) +def test_merge_tags_with_metrics(tag_factory, metric_factory): + tag_with_metric1 = tag_factory() + tag_with_metric2 = tag_factory() + tag_no_metric = tag_factory() + tag_with_metric1.metric = metric_factory() + tag_with_metric2.metric = metric_factory() + db.session.add_all([tag_no_metric, tag_with_metric1, tag_with_metric2]) + db.session.flush() + with pytest.raises(tags.InvalidTagRelationError): + tags.merge_tags(tag_no_metric, tag_with_metric2) + with pytest.raises(tags.InvalidTagRelationError): + tags.merge_tags(tag_with_metric1, tag_no_metric) + with pytest.raises(tags.InvalidTagRelationError): + tags.merge_tags(tag_with_metric1, tag_with_metric2) + + def test_merge_tags_moves_usages(tag_factory, post_factory): source_tag = tag_factory(names=["source"]) target_tag = tag_factory(names=["target"]) diff --git a/server/szurubooru/tests/model/test_metric.py b/server/szurubooru/tests/model/test_metric.py new file mode 100644 index 0000000..cf0fa56 --- /dev/null +++ b/server/szurubooru/tests/model/test_metric.py @@ -0,0 +1,235 @@ +from szurubooru import db, model + +import pytest + +@pytest.fixture(autouse=True) +def inject_config(config_injector): + config_injector( + {"secret": "secret", "data_dir": "", "delete_source_files": False} + ) + +def test_saving_metric(post_factory, tag_factory): + tag = tag_factory() + post = post_factory(tags=[tag]) + metric = model.Metric(tag=tag, min=1., max=10.) + post_metric = model.PostMetric(metric=metric, post=post, value=5.5) + post_metric_range = model.PostMetricRange(metric=metric, post=post, + low=2., high=8.) + db.session.add_all([post, tag, metric, post_metric, post_metric_range]) + db.session.commit() + + assert metric.tag_id is not None + assert post_metric.tag_id is not None + assert post_metric.post_id is not None + assert post_metric_range.tag_id is not None + assert post_metric_range.post_id is not None + assert tag.metric.tag_id == tag.tag_id + assert tag.metric.min == 1. + assert tag.metric.max == 10. + + metric = ( + db.session + .query(model.Metric) + .filter(model.Metric.tag_id == tag.tag_id) + .one()) + assert metric.min == 1. + assert metric.max == 10. + + post_metric = ( + db.session + .query(model.PostMetric) + .filter(model.PostMetric.tag_id == tag.tag_id and + model.PostMetric.post_id == post.post_id) + .one()) + assert post_metric.value == 5.5 + + post_metric_range = ( + db.session + .query(model.PostMetricRange) + .filter(model.PostMetricRange.tag_id == tag.tag_id and + model.PostMetricRange.post_id == post.post_id) + .one()) + assert post_metric_range.low == 2. + assert post_metric_range.high == 8. + + tag = ( + db.session + .query(model.Tag) + .filter(model.Tag.tag_id == metric.tag_id) + .one()) + assert tag.metric == metric + + +def test_cascade_delete_metric(post_factory, tag_factory): + tag = tag_factory() + post1 = post_factory(tags=[tag]) + post2 = post_factory(tags=[tag]) + metric = model.Metric(tag=tag, min=1., max=10.) + post_metric1 = model.PostMetric(metric=metric, post=post1, value=2.3) + post_metric2 = model.PostMetric(metric=metric, post=post2, value=4.5) + post_metric_range = model.PostMetricRange( + metric=metric, post=post2, low=2, high=8) + db.session.add_all([post1, post2, tag, metric, post_metric1, post_metric2, + post_metric_range]) + db.session.flush() + + assert not db.session.dirty + assert db.session.query(model.Post).count() == 2 + assert db.session.query(model.Tag).count() == 1 + assert db.session.query(model.Metric).count() == 1 + assert db.session.query(model.PostMetric).count() == 2 + assert db.session.query(model.PostMetricRange).count() == 1 + + db.session.delete(metric) + db.session.commit() + + assert not db.session.dirty + assert db.session.query(model.Post).count() == 2 + assert db.session.query(model.Tag).count() == 1 + assert db.session.query(model.Metric).count() == 0 + assert db.session.query(model.PostMetric).count() == 0 + assert db.session.query(model.PostMetricRange).count() == 0 + + +def test_cascade_delete_tag(post_factory, tag_factory): + tag1 = tag_factory() + tag2 = tag_factory() + post = post_factory(tags=[tag1, tag2]) + metric1 = model.Metric(tag=tag1, min=1., max=10.) + metric2 = model.Metric(tag=tag2, min=2., max=20.) + post_metric1 = model.PostMetric(metric=metric1, post=post, value=2.3) + post_metric2 = model.PostMetric(metric=metric2, post=post, value=4.5) + post_metric_range1 = model.PostMetricRange( + metric=metric1, post=post, low=2, high=8) + post_metric_range2 = model.PostMetricRange( + metric=metric2, post=post, low=2, high=8) + db.session.add_all([post, tag1, tag2, metric1, metric2, post_metric1, + post_metric2, post_metric_range1, post_metric_range2]) + db.session.commit() + + assert not db.session.dirty + assert db.session.query(model.Post).count() == 1 + assert db.session.query(model.Tag).count() == 2 + assert db.session.query(model.Metric).count() == 2 + assert db.session.query(model.PostMetric).count() == 2 + assert db.session.query(model.PostMetricRange).count() == 2 + + db.session.delete(tag2) + db.session.commit() + + assert not db.session.dirty + assert db.session.query(model.Post).count() == 1 + assert db.session.query(model.Tag).count() == 1 + assert db.session.query(model.Metric).count() == 1 + assert db.session.query(model.PostMetric).count() == 1 + assert db.session.query(model.PostMetricRange).count() == 1 + + +def test_cascade_delete_post(post_factory, tag_factory): + tag = tag_factory() + post1 = post_factory(tags=[tag]) + post2 = post_factory(tags=[tag]) + metric = model.Metric(tag=tag, min=1., max=10.) + post_metric1 = model.PostMetric(metric=metric, post=post1, value=2.3) + post_metric2 = model.PostMetric(metric=metric, post=post2, value=4.5) + post_metric_range1 = model.PostMetricRange( + metric=metric, post=post1, low=2, high=8) + post_metric_range2 = model.PostMetricRange( + metric=metric, post=post2, low=2, high=8) + db.session.add_all([post1, post2, tag, metric, post_metric1, post_metric2, + post_metric_range1, post_metric_range2]) + db.session.commit() + + assert not db.session.dirty + assert db.session.query(model.Post).count() == 2 + assert db.session.query(model.Tag).count() == 1 + assert db.session.query(model.Metric).count() == 1 + assert db.session.query(model.PostMetric).count() == 2 + assert db.session.query(model.PostMetricRange).count() == 2 + + db.session.delete(post2) + db.session.commit() + + assert not db.session.dirty + assert db.session.query(model.Post).count() == 1 + assert db.session.query(model.Tag).count() == 1 + assert db.session.query(model.Metric).count() == 1 + assert db.session.query(model.PostMetric).count() == 1 + assert db.session.query(model.PostMetricRange).count() == 1 + + +def test_delete_post_metric_no_cascade( + post_factory, tag_factory, metric_factory, + post_metric_factory, post_metric_range_factory): + tag = tag_factory() + post = post_factory(tags=[tag]) + metric = metric_factory(tag=tag) + post_metric = post_metric_factory(post=post, metric=metric) + post_metric_range = post_metric_range_factory(post=post, metric=metric) + db.session.add(metric) + db.session.commit() + assert len(metric.post_metrics) == 1 + + db.session.delete(post_metric) + db.session.delete(post_metric_range) + db.session.commit() + assert len(metric.post_metrics) == 0 + assert len(metric.post_metric_ranges) == 0 + + +def test_tag_without_metric(tag_factory): + tag = tag_factory(names=['mytag']) + assert tag.metric is None + db.session.add(tag) + db.session.commit() + tag = ( + db.session + .query(model.Tag) + .join(model.TagName) + .filter(model.TagName.name == 'mytag') + .one()) + assert tag.metric is None + + +def test_metric_counts(post_factory, metric_factory): + metric = metric_factory() + post1 = post_factory(tags=[metric.tag]) + post2 = post_factory(tags=[metric.tag]) + post_metric1 = model.PostMetric(post=post1, metric=metric, value=1.2) + post_metric2 = model.PostMetric(post=post2, metric=metric, value=3.4) + post_metric_range = model.PostMetricRange(post=post1, metric=metric, low=5.6, high=7.8) + db.session.add_all([metric, post_metric1, post_metric2, post_metric_range]) + db.session.flush() + assert metric.post_metric_count == 2 + assert metric.post_metric_range_count == 1 + + +def test_cascade_on_remove_tag_from_post( + post_factory, tag_factory, metric_factory, + post_metric_factory, post_metric_range_factory): + tag = tag_factory() + post = post_factory(tags=[tag]) + metric = metric_factory(tag=tag) + post_metric = post_metric_factory(post=post, metric=metric) + post_metric_range = post_metric_range_factory(post=post, metric=metric) + db.session.add_all([post, tag, metric, post_metric, post_metric_range]) + db.session.commit() + + assert not db.session.dirty + assert db.session.query(model.Post).count() == 1 + assert db.session.query(model.Tag).count() == 1 + assert db.session.query(model.PostTag).count() == 1 + assert db.session.query(model.Metric).count() == 1 + assert db.session.query(model.PostMetric).count() == 1 + assert db.session.query(model.PostMetricRange).count() == 1 + + post.tags.clear() + db.session.commit() + + assert not db.session.dirty + assert db.session.query(model.Post).count() == 1 + assert db.session.query(model.Tag).count() == 1 + assert db.session.query(model.PostTag).count() == 0 + assert db.session.query(model.Metric).count() == 1 + assert db.session.query(model.PostMetric).count() == 0 + assert db.session.query(model.PostMetricRange).count() == 0 diff --git a/server/szurubooru/tests/search/configs/test_post_metric_search_config.py b/server/szurubooru/tests/search/configs/test_post_metric_search_config.py new file mode 100644 index 0000000..4eba809 --- /dev/null +++ b/server/szurubooru/tests/search/configs/test_post_metric_search_config.py @@ -0,0 +1,89 @@ +import pytest +from szurubooru import db, model, errors, search + + +@pytest.fixture +def executor(): + return search.Executor(search.configs.PostMetricSearchConfig()) + + +@pytest.fixture +def verify_unpaged(executor): + def verify(input, expected_values): + actual_count, actual_post_metrics = executor.execute( + input, offset=0, limit=100) + actual_values = ['%s:%r' % (u.metric.tag_name, u.value) + for u in actual_post_metrics] + assert actual_count == len(expected_values) + assert actual_values == expected_values + return verify + + +def test_refresh_metrics(tag_factory, metric_factory): + tag1 = tag_factory(names=['tag1']) + tag2 = tag_factory(names=['tag2']) + metric1 = metric_factory(tag1) + metric2 = metric_factory(tag2) + db.session.add_all([tag1, tag2, metric1, metric2]) + db.session.flush() + + config = search.configs.PostMetricSearchConfig() + config.refresh_metrics() + + assert config.all_metric_names == ['tag1', 'tag2'] + + +@pytest.mark.parametrize('input,expected_tag_names', [ + ('', ['t1:10', 't2:20.5', 't1:30', 't2:40']), + ('*', ['t1:10', 't2:20.5', 't1:30', 't2:40']), + ('t1', ['t1:10', 't1:30']), + ('t2', ['t2:20.5', 't2:40']), + ('t*', ['t1:10', 't2:20.5', 't1:30', 't2:40']), + ('t1,t2', ['t1:10', 't2:20.5', 't1:30', 't2:40']), + ('T1,T2', ['t1:10', 't2:20.5', 't1:30', 't2:40']), +]) +def test_filter_anonymous( + verify_unpaged, input, expected_tag_names, + post_factory, tag_factory, metric_factory, post_metric_factory): + tag1 = tag_factory(names=['t1']) + tag2 = tag_factory(names=['t2']) + post1 = post_factory(tags=[tag1, tag2]) + post2 = post_factory(tags=[tag1, tag2]) + metric1 = metric_factory(tag1) + metric2 = metric_factory(tag2) + t1_10 = post_metric_factory(post=post1, metric=metric1, value=10) + t1_30 = post_metric_factory(post=post2, metric=metric1, value=30) + t2_20 = post_metric_factory(post=post1, metric=metric2, value=20.5) + t2_40 = post_metric_factory(post=post2, metric=metric2, value=40) + db.session.add_all([tag1, tag2, metric1, metric2, + t1_10, t1_30, t2_20, t2_40]) + db.session.flush() + verify_unpaged(input, expected_tag_names) + + +@pytest.mark.parametrize('input,expected_tag_names', [ + ('t:13', []), + ('t:10', ['t:10']), + ('t:20.5', ['t:20.5']), + ('t:18.6..', ['t:20.5', 't:30', 't:40']), + ('t-min:18.6', ['t:20.5', 't:30', 't:40']), + ('t:..21.4', ['t:10', 't:20.5']), + ('t-max:21.4', ['t:10', 't:20.5']), + ('t:17..33', ['t:20.5', 't:30']), +]) +def test_filter_by_value( + verify_unpaged, input, expected_tag_names, + post_factory, tag_factory, metric_factory, post_metric_factory): + tag = tag_factory(names=['t']) + post1 = post_factory(tags=[tag]) + post2 = post_factory(tags=[tag]) + post3 = post_factory(tags=[tag]) + post4 = post_factory(tags=[tag]) + metric = metric_factory(tag) + t1 = post_metric_factory(post=post1, metric=metric, value=10) + t2 = post_metric_factory(post=post2, metric=metric, value=30) + t3 = post_metric_factory(post=post3, metric=metric, value=20.5) + t4 = post_metric_factory(post=post4, metric=metric, value=40) + db.session.add_all([tag, metric, t1, t2, t3, t4]) + db.session.flush() + verify_unpaged(input, expected_tag_names) diff --git a/server/szurubooru/tests/search/configs/test_post_search_config.py b/server/szurubooru/tests/search/configs/test_post_search_config.py index e726f31..cdd60ec 100644 --- a/server/szurubooru/tests/search/configs/test_post_search_config.py +++ b/server/szurubooru/tests/search/configs/test_post_search_config.py @@ -86,6 +86,14 @@ def verify_unpaged(executor): return verify +@pytest.fixture +def verify_around(executor): + def verify(input, post_id, expected_prev_id, expected_next_id): + actual_prev, actual_next, r = executor.get_around(input, post_id) + assert actual_prev.post_id == expected_prev_id + assert actual_next.post_id == expected_next_id + return verify + @pytest.mark.parametrize( "input,expected_post_ids", @@ -871,6 +879,157 @@ def test_tumbleweed( verify_unpaged("-special:tumbleweed", [1, 2, 3]) +@pytest.mark.parametrize("input,expected_post_ids", [ + ("sort:id,asc metric-a:1..3", [1, 2, 3]), + ("sort:id,asc metric-a-min:2", [2, 3]), + ("sort:id,asc metric-a:1.5..", [2, 3]), + ("sort:id,asc metric-a:1..3 metric-b:2..", [2]), + ("sort:id,asc c metric-a:3..", [3]), + ("sort:id,asc metric-b:..2", [1, 2]), + ("sort:id,asc metric-b:..1.9", [1]), + ("sort:metric-a", [1, 2, 3, 5, 4]), + ("sort:metric-a,desc", [3, 2, 1, 5, 4]), + ("metric-a:1..3 metric-b:1..3 sort:metric-b,desc", [2, 1]), + ("metric-a:1..3 sort:metric-b,desc", [2, 1, 3]), + ("metric-a:2..3 metric-b:1..3 sort:metric-b,desc", [2]), + ("metric-a:2..3 sort:metric-b,desc", [2, 3]), + ("sort:id,asc metric:a", [1, 2, 3]), + ("sort:id,asc -metric:a", [4, 5]), + ("sort:id,asc metric:a -metric:b", [3]), +]) +def test_metrics( + input, + expected_post_ids, + post_factory, + tag_factory, + metric_factory, + post_metric_factory, + post_metric_range_factory, + verify_unpaged): + tag_a = tag_factory(names=["a"]) + tag_b = tag_factory(names=["b"]) + tag_c = tag_factory(names=["c"]) + post1 = post_factory(id=1, tags=[tag_a, tag_b, tag_c]) + post2 = post_factory(id=2, tags=[tag_a, tag_b, tag_c]) + post3 = post_factory(id=3, tags=[tag_a, tag_b, tag_c]) + post4 = post_factory(id=4, tags=[tag_a, tag_b, tag_c]) + post5 = post_factory(id=5, tags=[tag_a, tag_b, tag_c]) + metric_a = metric_factory(tag=tag_a) + metric_b = metric_factory(tag=tag_b) + metric_c = metric_factory(tag=tag_c) + a1 = post_metric_factory(post=post1, metric=metric_a, value=1) + b1 = post_metric_factory(post=post1, metric=metric_b, value=1) + c1 = post_metric_factory(post=post1, metric=metric_c, value=1) + a2 = post_metric_factory(post=post2, metric=metric_a, value=2) + b2 = post_metric_factory(post=post2, metric=metric_b, value=2) + a3 = post_metric_factory(post=post3, metric=metric_a, value=3) + c3 = post_metric_factory(post=post3, metric=metric_c, value=3) + r_a4 = post_metric_range_factory(post=post4, metric=metric_a, + low=1.5, high=2.5) + db.session.add_all([tag_a, tag_b, tag_c, + post1, post2, post3, post4, post5, + metric_a, metric_b, metric_c, + a1, b1, c1, a2, b2, a3, c3, r_a4]) + db.session.flush() + verify_unpaged(input, expected_post_ids, True) + + +@pytest.mark.parametrize("input,expected_prev_id,expected_next_id", [ + ("", 3, 1), # default order is actually descending + ("sort:id,asc", 1, 3), + ("sort:id,desc", 3, 1), + ("sort:tag-count,asc", 1, 3), + ("sort:tag-count,desc", 3, 1), + ("metric-a:0..2 sort:metric-a", 3, 1), + ("metric-a:0..2 sort:metric-a,desc", 1, 3), + ("sort:metric-b", 3, 1), +]) +def test_around_query( + input, + expected_prev_id, + expected_next_id, + post_factory, + tag_factory, + metric_factory, + post_metric_factory, + verify_around): + tag_a = tag_factory(names=["a"]) + tag_b = tag_factory(names=["b"]) + tag_c = tag_factory(names=["c"]) + tag_d = tag_factory(names=["d"]) + post1 = post_factory(id=1, tags=[tag_a]) + post2 = post_factory(id=2, tags=[tag_a, tag_b]) + post3 = post_factory(id=3, tags=[tag_a, tag_b, tag_c]) + metric_a = metric_factory(tag=tag_a) + metric_b = metric_factory(tag=tag_b) + pm1 = post_metric_factory(post=post1, metric=metric_a, value=1.4) + pm2 = post_metric_factory(post=post2, metric=metric_a, value=1) + pm3 = post_metric_factory(post=post3, metric=metric_a, value=0.3) + db.session.add_all([tag_a, tag_b, tag_c, + post1, post2, post3, + metric_a, metric_b, pm1, pm2, pm3]) + db.session.add_all([tag_a, tag_b, tag_c, post1, post2, post3]) + db.session.flush() + verify_around(input, 2, expected_prev_id, expected_next_id) + + +@pytest.mark.parametrize("input,expected_post_ids", [ + ("similar:1", [6, 4, 1]), + ("similar:2", [6, 5, 4, 2]), + ("similar:3", [6, 5, 3]), + ("similar:4", [6, 4, 5, 2, 1]), + ("similar:5", [6, 5, 4, 3, 2]), + ("similar:6", [6, 5, 4, 3, 2, 1]), + ("-similar:1", [5, 3, 2]), + ("-similar:2", [3, 1]), + ("-similar:3", [4, 2, 1]), + ("-similar:4", [3]), + ("-similar:5", [1]), + ("-similar:6", []), + ("similar:4 sort:id,asc", [4, 6, 1, 2, 5]), + ("similar:4 b", [6, 4, 5, 2]), + ("similar:4 c", [6, 5]), +]) +def test_filter_by_similar( + post_factory, tag_factory, verify_unpaged, input, expected_post_ids +): + tagA = tag_factory(names=["a"]) + tagB = tag_factory(names=["b"]) + tagC = tag_factory(names=["c"]) + postA = post_factory(id=1, tags=[tagA]) + postB = post_factory(id=2, tags=[tagB]) + postC = post_factory(id=3, tags=[tagC]) + postAB = post_factory(id=4, tags=[tagA, tagB]) + postBC = post_factory(id=5, tags=[tagB, tagC]) + postABC = post_factory(id=6, tags=[tagA, tagB, tagC]) + db.session.add_all( + [tagA, tagB, tagC, postA, postB, postC, postAB, postBC, postABC] + ) + db.session.flush() + verify_unpaged(input, expected_post_ids, True) + + +@pytest.mark.parametrize("input,expected_post_ids", [ + ("similar:1", [3, 1, 2]), + ("similar:2", [3, 2, 1]), + ("similar:3", [3, 1, 2]), +]) +def test_sort_by_similar( + post_factory, tag_factory, verify_unpaged, input, expected_post_ids +): + tagA = tag_factory(names=["a"]) + tagB = tag_factory(names=["b"]) + tagC = tag_factory(names=["c"]) + postAB = post_factory(id=1, tags=[tagA, tagB]) + postA = post_factory(id=2, tags=[tagA]) + postABC = post_factory(id=3, tags=[tagA, tagB, tagC]) + db.session.add_all( + [tagA, tagB, tagC, postA,postAB, postABC] + ) + db.session.flush() + verify_unpaged(input, expected_post_ids, True) + + @pytest.mark.parametrize( "input,expected_post_ids", [ |