diff options
| -rw-r--r-- | server/szurubooru/func/metrics.py | 2 | ||||
| -rw-r--r-- | server/szurubooru/migrations/versions/0061c5c3299f_postmetric_depends_on_posttag.py | 35 | ||||
| -rw-r--r-- | server/szurubooru/model/metric.py | 35 | ||||
| -rw-r--r-- | server/szurubooru/tests/conftest.py | 11 | ||||
| -rw-r--r-- | server/szurubooru/tests/func/test_metrics.py | 141 | ||||
| -rw-r--r-- | server/szurubooru/tests/model/test_metric.py | 94 |
6 files changed, 211 insertions, 107 deletions
diff --git a/server/szurubooru/func/metrics.py b/server/szurubooru/func/metrics.py index ab5a0c4..80ea073 100644 --- a/server/szurubooru/func/metrics.py +++ b/server/szurubooru/func/metrics.py @@ -237,7 +237,7 @@ def update_or_create_post_metric_ranges( Overwrites any existing post metrics, deletes other existing post metrics. """ assert post - post.metrics = [] + post.metric_ranges = [] for metric_data in metric_ranges_data: for field in ('tag_name', 'low', 'high'): if field not in metric_data: 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/model/metric.py b/server/szurubooru/model/metric.py index c3b5f11..530165a 100644 --- a/server/szurubooru/model/metric.py +++ b/server/szurubooru/model/metric.py @@ -1,5 +1,6 @@ import sqlalchemy as sa from szurubooru.model.base import Base +from szurubooru.model.post import PostTag from szurubooru.model.tag import TagName @@ -26,9 +27,18 @@ class PostMetric(Base): 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, } @@ -60,9 +70,18 @@ class PostMetricRange(Base): 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, } @@ -92,18 +111,18 @@ class Metric(Base): 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() + .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) + .where(PostMetric.tag_id == tag_id) + .correlate_except(PostMetric) ), deferred=True) @@ -111,8 +130,8 @@ class Metric(Base): ( sa.sql.expression.select( [sa.sql.expression.func.count(PostMetricRange.post_id)]) - .where(PostMetricRange.tag_id == tag_id) - .correlate_except(PostMetricRange) + .where(PostMetricRange.tag_id == tag_id) + .correlate_except(PostMetricRange) ), deferred=True) diff --git a/server/szurubooru/tests/conftest.py b/server/szurubooru/tests/conftest.py index 084e6e5..c61e7c3 100644 --- a/server/szurubooru/tests/conftest.py +++ b/server/szurubooru/tests/conftest.py @@ -32,11 +32,18 @@ class QueryCounter: return self._statements +def _set_sqlite_pragma(dbapi_connection, connection_record): + cursor = dbapi_connection.cursor() + cursor.execute('PRAGMA foreign_keys=ON') + cursor.close() + + if not config.config['test_database']: raise RuntimeError('Test database not configured.') _query_counter = QueryCounter() _engine = sa.create_engine(config.config['test_database']) +sa.event.listen(_engine, 'connect', _set_sqlite_pragma) model.Base.metadata.drop_all(bind=_engine) model.Base.metadata.create_all(bind=_engine) sa.event.listen( @@ -198,7 +205,8 @@ def post_factory(skip_post_hashing): id=None, safety=model.Post.SAFETY_SAFE, type=model.Post.TYPE_IMAGE, - checksum='...'): + checksum='...', + tags=[]): post = model.Post() post.post_id = id post.safety = safety @@ -207,6 +215,7 @@ def post_factory(skip_post_hashing): post.flags = [] post.mime_type = 'application/octet-stream' post.creation_time = datetime(1996, 1, 1) + post.tags = tags return post return factory diff --git a/server/szurubooru/tests/func/test_metrics.py b/server/szurubooru/tests/func/test_metrics.py index 0db76c4..d8d0cbd 100644 --- a/server/szurubooru/tests/func/test_metrics.py +++ b/server/szurubooru/tests/func/test_metrics.py @@ -42,26 +42,26 @@ def test_serialize_post_metric_range(tag_factory, metric_factory): def test_try_get_post_metric( post_factory, metric_factory, post_metric_factory): - post = post_factory() - metric = metric_factory() + metric1 = metric_factory() metric2 = metric_factory() - post_metric = post_metric_factory(post=post, metric=metric) - db.session.add_all([post, metric, metric2, post_metric]) + 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, metric) is post_metric + 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): - post = post_factory() - metric = metric_factory() + metric1 = metric_factory() metric2 = metric_factory() - post_metric_range = post_metric_range_factory(post=post, metric=metric) - db.session.add_all([post, metric, metric2, post_metric_range]) + 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, metric) is post_metric_range + assert metrics.try_get_post_metric_range(post, metric1) is post_metric_range def test_get_all_metrics(metric_factory): @@ -137,33 +137,25 @@ def test_update_or_create_post_metric_without_tag(post_factory, metric_factory): def test_update_or_create_post_metric_with_value_out_of_range( - post_factory, tag_factory, metric_factory): - post = post_factory() - tag = tag_factory() - post.tags = [tag] - metric = metric_factory(tag) + 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, tag_factory, metric_factory): - post = post_factory() - tag = tag_factory() - post.tags = [tag] - metric = metric_factory(tag) +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, tag_factory, metric_factory): - post = post_factory() - tag = tag_factory() - post.tags = [tag] - metric = metric_factory(tag) +def test_update_or_create_post_metric_update(post_factory, metric_factory): + metric = metric_factory() + post = post_factory(tags=[metric.tag]) post_metric = model.PostMetric(post=post, metric=metric, value=1.2) db.session.add(post_metric) db.session.flush() @@ -200,9 +192,8 @@ def test_update_or_create_post_metrics_with_missing_fields( def test_update_or_create_post_metrics_with_invalid_tag( post_factory, tag_factory): - post = post_factory() tag = tag_factory(names=['tag1']) - post.tags = [tag] + post = post_factory(tags=[tag]) db.session.add(tag) db.session.flush() data = [{'tag_name': 'tag1', 'value': 2}] @@ -212,10 +203,9 @@ def test_update_or_create_post_metrics_with_invalid_tag( def test_update_or_create_post_metrics( post_factory, tag_factory, metric_factory): - post = post_factory() tag1 = tag_factory(names=['tag1']) tag2 = tag_factory(names=['tag2']) - post.tags = [tag1, tag2] + post = post_factory(tags=[tag1, tag2]) metric1 = metric_factory(tag1) metric2 = metric_factory(tag2) db.session.add_all([metric1, metric2]) @@ -233,6 +223,31 @@ def test_update_or_create_post_metrics( 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( @@ -247,21 +262,17 @@ def test_update_or_create_post_metric_range_without_tag( (-99, 1), (1, 99), ]) def test_update_or_create_post_metric_range_with_values_out_of_range( - low, high, post_factory, tag_factory, metric_factory): - post = post_factory() - tag = tag_factory() - post.tags = [tag] - metric = metric_factory(tag) + 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, tag_factory, metric_factory): - post = post_factory() - tag = tag_factory() - post.tags = [tag] - metric = metric_factory(tag) + 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( @@ -271,11 +282,9 @@ def test_update_or_create_post_metric_range_create( def test_update_or_create_post_metric_range_update( - post_factory, tag_factory, metric_factory): - post = post_factory() - tag = tag_factory() - post.tags = [tag] - metric = metric_factory(tag) + 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) @@ -308,18 +317,16 @@ def test_update_or_create_post_metric_ranges_missing_tag( ]) def test_update_or_create_post_metric_ranges_with_missing_fields( params, post_factory, tag_factory): - post = post_factory() tag = tag_factory(names=['tag']) - post.tags = [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): - post = post_factory() tag = tag_factory(names=['tag1']) - post.tags = [tag] + post = post_factory(tags=[tag]) db.session.add(tag) db.session.flush() data = [{'tag_name': 'tag1', 'low': 2, 'high': 3}] @@ -329,9 +336,8 @@ def test_update_or_create_post_metric_ranges_with_invalid_tag( def test_update_or_create_post_metric_ranges_with_invalid_values( post_factory, tag_factory, metric_factory): - post = post_factory() tag = tag_factory(names=['tag1']) - post.tags = [tag] + post = post_factory(tags=[tag]) metric = metric_factory(tag=tag) db.session.add_all([metric, tag]) db.session.flush() @@ -344,10 +350,9 @@ def test_update_or_create_post_metric_ranges_with_invalid_values( def test_update_or_create_post_metric_ranges( post_factory, tag_factory, metric_factory): - post = post_factory() tag1 = tag_factory(names=['tag1']) tag2 = tag_factory(names=['tag2']) - post.tags = [tag1, tag2] + post = post_factory(tags=[tag1, tag2]) metric1 = metric_factory(tag1) metric2 = metric_factory(tag2) db.session.add_all([metric1, metric2]) @@ -365,3 +370,31 @@ def test_update_or_create_post_metric_ranges( 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 diff --git a/server/szurubooru/tests/model/test_metric.py b/server/szurubooru/tests/model/test_metric.py index 2b0a13a..69d7bfa 100644 --- a/server/szurubooru/tests/model/test_metric.py +++ b/server/szurubooru/tests/model/test_metric.py @@ -2,11 +2,12 @@ from szurubooru import db, model def test_saving_metric(post_factory, tag_factory): - post = post_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.) + 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() @@ -53,9 +54,9 @@ def test_saving_metric(post_factory, tag_factory): def test_cascade_delete_metric(post_factory, tag_factory): - post1 = post_factory() - post2 = post_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) @@ -84,9 +85,9 @@ def test_cascade_delete_metric(post_factory, tag_factory): def test_cascade_delete_tag(post_factory, tag_factory): - post = post_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) @@ -97,7 +98,7 @@ def test_cascade_delete_tag(post_factory, tag_factory): 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.flush() + db.session.commit() assert not db.session.dirty assert db.session.query(model.Post).count() == 1 @@ -118,9 +119,9 @@ def test_cascade_delete_tag(post_factory, tag_factory): def test_cascade_delete_post(post_factory, tag_factory): - post1 = post_factory() - post2 = post_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) @@ -130,7 +131,7 @@ def test_cascade_delete_post(post_factory, tag_factory): 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.flush() + db.session.commit() assert not db.session.dirty assert db.session.query(model.Post).count() == 2 @@ -151,11 +152,14 @@ def test_cascade_delete_post(post_factory, tag_factory): def test_delete_post_metric_no_cascade( - metric_factory, post_metric_factory, post_metric_range_factory): - metric = metric_factory() - post_metric = post_metric_factory(metric=metric) - post_metric_range = post_metric_range_factory(metric=metric) - db.session.add_all([metric, post_metric, post_metric_range]) + 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 @@ -166,33 +170,6 @@ def test_delete_post_metric_no_cascade( assert len(metric.post_metric_ranges) == 0 -def test_cascade_delete_on_remove_metric_from_post( - post_factory, post_metric_factory, post_metric_range_factory): - post = post_factory() - post_metric = post_metric_factory(post=post) - post_metric_range = post_metric_range_factory(post=post) - db.session.add_all([post, 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() == 2 - assert db.session.query(model.Metric).count() == 2 - assert db.session.query(model.PostMetric).count() == 1 - assert db.session.query(model.PostMetricRange).count() == 1 - - post.metrics.clear() - post.metric_ranges.clear() - 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() == 0 - assert db.session.query(model.PostMetricRange).count() == 0 - - def test_tag_without_metric(tag_factory): tag = tag_factory(names=['mytag']) assert tag.metric is None @@ -208,9 +185,9 @@ def test_tag_without_metric(tag_factory): def test_metric_counts(post_factory, metric_factory): - post1 = post_factory() - post2 = post_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) @@ -218,3 +195,34 @@ def test_metric_counts(post_factory, metric_factory): 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 |