Add pool CRUD operations/pages

18 files changed, 1156 insertions, 76 deletions

diff --git a/server/alembic.ini b/server/alembic.ini
index 6a4e5ff..98ab83d 100644
--- a/server/alembic.ini
+++ b/server/alembic.ini
@@ -3,6 +3,7 @@ script_location = szurubooru/migrations
 
 # overriden by szurubooru's config
 sqlalchemy.url =
+revision_environment = true
 
 [loggers]
 keys = root,sqlalchemy,alembic
diff --git a/server/config.yaml.dist b/server/config.yaml.dist
index 4f8f769..6807623 100644
--- a/server/config.yaml.dist
+++ b/server/config.yaml.dist
@@ -4,7 +4,7 @@
 # shown in the website title and on the front page
 name: szurubooru
 # full url to the homepage of this szurubooru site, with no trailing slash
-domain: # example: http://example.com
+domain: localhost # example: http://example.com
 # used to salt the users' password hashes and generate filenames for static content
 secret: change
 
@@ -49,6 +49,9 @@ enable_safety: yes
 tag_name_regex: ^\S+$
 tag_category_name_regex: ^[^\s%+#/]+$
 
+pool_name_regex: ^\S+$
+pool_category_name_regex: ^[^\s%+#/]+$
+
 # don't make these more restrictive unless you want to annoy people; if you do
 # customize them, make sure to update the instructions in the registration form
 # template as well.
@@ -58,86 +61,100 @@ user_name_regex: '^[a-zA-Z0-9_-]{1,32}$'
 default_rank: regular
 
 privileges:
-    'users:create:self':            anonymous # Registration permission
-    'users:create:any':             administrator
-    'users:list':                   regular
-    'users:view':                   regular
-    'users:edit:any:name':          moderator
-    'users:edit:any:pass':          moderator
-    'users:edit:any:email':         moderator
-    'users:edit:any:avatar':        moderator
-    'users:edit:any:rank':          moderator
-    'users:edit:self:name':         regular
-    'users:edit:self:pass':         regular
-    'users:edit:self:email':        regular
-    'users:edit:self:avatar':       regular
-    'users:edit:self:rank':         moderator # one can't promote themselves or anyone to upper rank than their own.
-    'users:delete:any':             administrator
-    'users:delete:self':            regular
+    'users:create:self':             anonymous # Registration permission
+    'users:create:any':              administrator
+    'users:list':                    regular
+    'users:view':                    regular
+    'users:edit:any:name':           moderator
+    'users:edit:any:pass':           moderator
+    'users:edit:any:email':          moderator
+    'users:edit:any:avatar':         moderator
+    'users:edit:any:rank':           moderator
+    'users:edit:self:name':          regular
+    'users:edit:self:pass':          regular
+    'users:edit:self:email':         regular
+    'users:edit:self:avatar':        regular
+    'users:edit:self:rank':          moderator # one can't promote themselves or anyone to upper rank than their own.
+    'users:delete:any':              administrator
+    'users:delete:self':             regular
+
+    'user_tokens:list:any':          administrator
+    'user_tokens:list:self':         regular
+    'user_tokens:create:any':        administrator
+    'user_tokens:create:self':       regular
+    'user_tokens:edit:any':          administrator
+    'user_tokens:edit:self':         regular
+    'user_tokens:delete:any':        administrator
+    'user_tokens:delete:self':       regular
+
+    'posts:create:anonymous':        regular
+    'posts:create:identified':       regular
+    'posts:list':                    anonymous
+    'posts:reverse_search':          regular
+    'posts:view':                    anonymous
+    'posts:view:featured':           anonymous
+    'posts:edit:content':            power
+    'posts:edit:flags':              regular
+    'posts:edit:notes':              regular
+    'posts:edit:relations':          regular
+    'posts:edit:safety':             power
+    'posts:edit:source':             regular
+    'posts:edit:tags':               regular
+    'posts:edit:thumbnail':          power
+    'posts:feature':                 moderator
+    'posts:delete':                  moderator
+    'posts:score':                   regular
+    'posts:merge':                   moderator
+    'posts:favorite':                regular
+    'posts:bulk-edit:tags':          power
+    'posts:bulk-edit:safety':        power
 
-    'user_tokens:list:any':         administrator
-    'user_tokens:list:self':        regular
-    'user_tokens:create:any':       administrator
-    'user_tokens:create:self':      regular
-    'user_tokens:edit:any':         administrator
-    'user_tokens:edit:self':        regular
-    'user_tokens:delete:any':       administrator
-    'user_tokens:delete:self':      regular
+    'tags:create':                   regular
+    'tags:edit:names':               power
+    'tags:edit:category':            power
+    'tags:edit:description':         power
+    'tags:edit:implications':        power
+    'tags:edit:suggestions':         power
+    'tags:list':                     regular
+    'tags:view':                     anonymous
+    'tags:merge':                    moderator
+    'tags:delete':                   moderator
 
-    'posts:create:anonymous':       regular
-    'posts:create:identified':      regular
-    'posts:list':                   anonymous
-    'posts:reverse_search':         regular
-    'posts:view':                   anonymous
-    'posts:view:featured':          anonymous
-    'posts:edit:content':           power
-    'posts:edit:flags':             regular
-    'posts:edit:notes':             regular
-    'posts:edit:relations':         regular
-    'posts:edit:safety':            power
-    'posts:edit:source':            regular
-    'posts:edit:tags':              regular
-    'posts:edit:thumbnail':         power
-    'posts:feature':                moderator
-    'posts:delete':                 moderator
-    'posts:score':                  regular
-    'posts:merge':                  moderator
-    'posts:favorite':               regular
-    'posts:bulk-edit:tags':         power
-    'posts:bulk-edit:safety':       power
+    'tag_categories:create':         moderator
+    'tag_categories:edit:name':      moderator
+    'tag_categories:edit:color':     moderator
+    'tag_categories:list':           anonymous
+    'tag_categories:view':           anonymous
+    'tag_categories:delete':         moderator
+    'tag_categories:set_default':    moderator
 
-    'tags:create':                  regular
-    'tags:edit:names':              power
-    'tags:edit:category':           power
-    'tags:edit:description':        power
-    'tags:edit:implications':       power
-    'tags:edit:suggestions':        power
-    'tags:list':                    regular
-    'tags:view':                    anonymous
-    'tags:merge':                   moderator
-    'tags:delete':                  moderator
+    'pools:create':                  regular
+    'pools:list':                    regular
+    'pools:view':                    anonymous
+    'pools:merge':                   moderator
+    'pools:delete':                  moderator
 
-    'tag_categories:create':        moderator
-    'tag_categories:edit:name':     moderator
-    'tag_categories:edit:color':    moderator
-    'tag_categories:list':          anonymous
-    'tag_categories:view':          anonymous
-    'tag_categories:delete':        moderator
-    'tag_categories:set_default':   moderator
+    'pool_categories:create':        moderator
+    'pool_categories:edit:name':     moderator
+    'pool_categories:edit:color':    moderator
+    'pool_categories:list':          anonymous
+    'pool_categories:view':          anonymous
+    'pool_categories:delete':        moderator
+    'pool_categories:set_default':   moderator
 
-    'comments:create':              regular
-    'comments:delete:any':          moderator
-    'comments:delete:own':          regular
-    'comments:edit:any':            moderator
-    'comments:edit:own':            regular
-    'comments:list':                regular
-    'comments:view':                regular
-    'comments:score':               regular
+    'comments:create':               regular
+    'comments:delete:any':           moderator
+    'comments:delete:own':           regular
+    'comments:edit:any':             moderator
+    'comments:edit:own':             regular
+    'comments:list':                 regular
+    'comments:view':                 regular
+    'comments:score':                regular
 
-    'snapshots:list':               power
+    'snapshots:list':                power
 
-    'uploads:create':               regular
-    'uploads:use_downloader':       power
+    'uploads:create':                regular
+    'uploads:use_downloader':        power
 
 ## ONLY SET THESE IF DEPLOYING OUTSIDE OF DOCKER
 #debug: 0 # generate server logs?
diff --git a/server/requirements.txt b/server/requirements.txt
index 810452e..8a337f9 100644
--- a/server/requirements.txt
+++ b/server/requirements.txt
@@ -9,3 +9,4 @@ pillow>=4.3.0
 pynacl>=1.2.1
 pytz>=2018.3
 pyRFC3339>=1.0
+youtube_dl>=2020.5.3
diff --git a/server/szurubooru/api/__init__.py b/server/szurubooru/api/__init__.py
index 0d7f75f..28f0e98 100644
--- a/server/szurubooru/api/__init__.py
+++ b/server/szurubooru/api/__init__.py
@@ -4,6 +4,8 @@ import szurubooru.api.user_token_api
 import szurubooru.api.post_api
 import szurubooru.api.tag_api
 import szurubooru.api.tag_category_api
+import szurubooru.api.pool_api
+import szurubooru.api.pool_category_api
 import szurubooru.api.comment_api
 import szurubooru.api.password_reset_api
 import szurubooru.api.snapshot_api
diff --git a/server/szurubooru/api/pool_api.py b/server/szurubooru/api/pool_api.py
new file mode 100644
index 0000000..b2ec11b
--- /dev/null
+++ b/server/szurubooru/api/pool_api.py
@@ -0,0 +1,127 @@
+from typing import Optional, List, Dict
+from datetime import datetime
+from szurubooru import db, model, search, rest
+from szurubooru.func import auth, pools, snapshots, serialization, versions
+
+
+_search_executor = search.Executor(search.configs.PoolSearchConfig())
+
+
+def _serialize(ctx: rest.Context, pool: model.Pool) -> rest.Response:
+    return pools.serialize_pool(
+        pool, options=serialization.get_serialization_options(ctx))
+
+
+def _get_pool(params: Dict[str, str]) -> model.Pool:
+    return pools.get_pool_by_id(params['pool_id'])
+
+
+# def _create_if_needed(pool_names: List[str], user: model.User) -> None:
+#     if not pool_names:
+#         return
+#     _existing_pools, new_pools = pools.get_or_create_pools_by_names(pool_names)
+#     if len(new_pools):
+#         auth.verify_privilege(user, 'pools:create')
+#     db.session.flush()
+#     for pool in new_pools:
+#         snapshots.create(pool, user)
+
+
+@rest.routes.get('/pools/?')
+def get_pools(ctx: rest.Context, _params: Dict[str, str] = {}) -> rest.Response:
+    auth.verify_privilege(ctx.user, 'pools:list')
+    return _search_executor.execute_and_serialize(
+        ctx, lambda pool: _serialize(ctx, pool))
+
+
+@rest.routes.post('/pools/?')
+def create_pool(
+        ctx: rest.Context, _params: Dict[str, str] = {}) -> rest.Response:
+    auth.verify_privilege(ctx.user, 'pools:create')
+
+    names = ctx.get_param_as_string_list('names')
+    category = ctx.get_param_as_string('category')
+    description = ctx.get_param_as_string('description', default='')
+    # TODO
+    # suggestions = ctx.get_param_as_string_list('suggestions', default=[])
+    # implications = ctx.get_param_as_string_list('implications', default=[])
+
+    # _create_if_needed(suggestions, ctx.user)
+    # _create_if_needed(implications, ctx.user)
+
+    pool = pools.create_pool(names, category)
+    pools.update_pool_description(pool, description)
+    ctx.session.add(pool)
+    ctx.session.flush()
+    snapshots.create(pool, ctx.user)
+    ctx.session.commit()
+    return _serialize(ctx, pool)
+
+
+@rest.routes.get('/pool/(?P<pool_id>[^/]+)/?')
+def get_pool(ctx: rest.Context, params: Dict[str, str]) -> rest.Response:
+    auth.verify_privilege(ctx.user, 'pools:view')
+    pool = _get_pool(params)
+    return _serialize(ctx, pool)
+
+
+@rest.routes.put('/pool/(?P<pool_id>[^/]+)/?')
+def update_pool(ctx: rest.Context, params: Dict[str, str]) -> rest.Response:
+    pool = _get_pool(params)
+    versions.verify_version(pool, ctx)
+    versions.bump_version(pool)
+    if ctx.has_param('names'):
+        auth.verify_privilege(ctx.user, 'pools:edit:names')
+        pools.update_pool_names(pool, ctx.get_param_as_string_list('names'))
+    if ctx.has_param('category'):
+        auth.verify_privilege(ctx.user, 'pools:edit:category')
+        pools.update_pool_category_name(
+            pool, ctx.get_param_as_string('category'))
+    if ctx.has_param('description'):
+        auth.verify_privilege(ctx.user, 'pools:edit:description')
+        pools.update_pool_description(
+            pool, ctx.get_param_as_string('description'))
+    # TODO
+    # if ctx.has_param('suggestions'):
+    #     auth.verify_privilege(ctx.user, 'pools:edit:suggestions')
+    #     suggestions = ctx.get_param_as_string_list('suggestions')
+    #     _create_if_needed(suggestions, ctx.user)
+    #     pools.update_pool_suggestions(pool, suggestions)
+    # if ctx.has_param('implications'):
+    #     auth.verify_privilege(ctx.user, 'pools:edit:implications')
+    #     implications = ctx.get_param_as_string_list('implications')
+    #     _create_if_needed(implications, ctx.user)
+    #     pools.update_pool_implications(pool, implications)
+    pool.last_edit_time = datetime.utcnow()
+    ctx.session.flush()
+    snapshots.modify(pool, ctx.user)
+    ctx.session.commit()
+    return _serialize(ctx, pool)
+
+
+@rest.routes.delete('/pool/(?P<pool_id>[^/]+)/?')
+def delete_pool(ctx: rest.Context, params: Dict[str, str]) -> rest.Response:
+    pool = _get_pool(params)
+    versions.verify_version(pool, ctx)
+    auth.verify_privilege(ctx.user, 'pools:delete')
+    snapshots.delete(pool, ctx.user)
+    pools.delete(pool)
+    ctx.session.commit()
+    return {}
+
+
+@rest.routes.post('/pool-merge/?')
+def merge_pools(
+        ctx: rest.Context, _params: Dict[str, str] = {}) -> rest.Response:
+    source_pool_id = ctx.get_param_as_string('remove')
+    target_pool_id = ctx.get_param_as_string('mergeTo')
+    source_pool = pools.get_pool_by_id(source_pool_id)
+    target_pool = pools.get_pool_by_id(target_pool_id)
+    versions.verify_version(source_pool, ctx, 'removeVersion')
+    versions.verify_version(target_pool, ctx, 'mergeToVersion')
+    versions.bump_version(target_pool)
+    auth.verify_privilege(ctx.user, 'pools:merge')
+    pools.merge_pools(source_pool, target_pool)
+    snapshots.merge(source_pool, target_pool, ctx.user)
+    ctx.session.commit()
+    return _serialize(ctx, target_pool)
diff --git a/server/szurubooru/api/pool_category_api.py b/server/szurubooru/api/pool_category_api.py
new file mode 100644
index 0000000..f293724
--- /dev/null
+++ b/server/szurubooru/api/pool_category_api.py
@@ -0,0 +1,89 @@
+from typing import Dict
+from szurubooru import model, rest
+from szurubooru.func import (
+    auth, pools, pool_categories, snapshots, serialization, versions)
+
+
+def _serialize(
+        ctx: rest.Context, category: model.PoolCategory) -> rest.Response:
+    return pool_categories.serialize_category(
+        category, options=serialization.get_serialization_options(ctx))
+
+
+@rest.routes.get('/pool-categories/?')
+def get_pool_categories(
+        ctx: rest.Context, _params: Dict[str, str] = {}) -> rest.Response:
+    auth.verify_privilege(ctx.user, 'pool_categories:list')
+    categories = pool_categories.get_all_categories()
+    return {
+        'results': [_serialize(ctx, category) for category in categories],
+    }
+
+
+@rest.routes.post('/pool-categories/?')
+def create_pool_category(
+        ctx: rest.Context, _params: Dict[str, str] = {}) -> rest.Response:
+    auth.verify_privilege(ctx.user, 'pool_categories:create')
+    name = ctx.get_param_as_string('name')
+    color = ctx.get_param_as_string('color')
+    category = pool_categories.create_category(name, color)
+    ctx.session.add(category)
+    ctx.session.flush()
+    snapshots.create(category, ctx.user)
+    ctx.session.commit()
+    return _serialize(ctx, category)
+
+
+@rest.routes.get('/pool-category/(?P<category_name>[^/]+)/?')
+def get_pool_category(
+        ctx: rest.Context, params: Dict[str, str]) -> rest.Response:
+    auth.verify_privilege(ctx.user, 'pool_categories:view')
+    category = pool_categories.get_category_by_name(params['category_name'])
+    return _serialize(ctx, category)
+
+
+@rest.routes.put('/pool-category/(?P<category_name>[^/]+)/?')
+def update_pool_category(
+        ctx: rest.Context, params: Dict[str, str]) -> rest.Response:
+    category = pool_categories.get_category_by_name(
+        params['category_name'], lock=True)
+    versions.verify_version(category, ctx)
+    versions.bump_version(category)
+    if ctx.has_param('name'):
+        auth.verify_privilege(ctx.user, 'pool_categories:edit:name')
+        pool_categories.update_category_name(
+            category, ctx.get_param_as_string('name'))
+    if ctx.has_param('color'):
+        auth.verify_privilege(ctx.user, 'pool_categories:edit:color')
+        pool_categories.update_category_color(
+            category, ctx.get_param_as_string('color'))
+    ctx.session.flush()
+    snapshots.modify(category, ctx.user)
+    ctx.session.commit()
+    return _serialize(ctx, category)
+
+
+@rest.routes.delete('/pool-category/(?P<category_name>[^/]+)/?')
+def delete_pool_category(
+        ctx: rest.Context, params: Dict[str, str]) -> rest.Response:
+    category = pool_categories.get_category_by_name(
+        params['category_name'], lock=True)
+    versions.verify_version(category, ctx)
+    auth.verify_privilege(ctx.user, 'pool_categories:delete')
+    pool_categories.delete_category(category)
+    snapshots.delete(category, ctx.user)
+    ctx.session.commit()
+    return {}
+
+
+@rest.routes.put('/pool-category/(?P<category_name>[^/]+)/default/?')
+def set_pool_category_as_default(
+        ctx: rest.Context, params: Dict[str, str]) -> rest.Response:
+    auth.verify_privilege(ctx.user, 'pool_categories:set_default')
+    category = pool_categories.get_category_by_name(
+        params['category_name'], lock=True)
+    pool_categories.set_default_category(category)
+    ctx.session.flush()
+    snapshots.modify(category, ctx.user)
+    ctx.session.commit()
+    return _serialize(ctx, category)
diff --git a/server/szurubooru/func/pool_categories.py b/server/szurubooru/func/pool_categories.py
new file mode 100644
index 0000000..eca934a
--- /dev/null
+++ b/server/szurubooru/func/pool_categories.py
@@ -0,0 +1,199 @@
+import re
+from typing import Any, Optional, Dict, List, Callable
+import sqlalchemy as sa
+from szurubooru import config, db, model, errors, rest
+from szurubooru.func import util, serialization, cache
+
+
+DEFAULT_CATEGORY_NAME_CACHE_KEY = 'default-pool-category'
+
+
+class PoolCategoryNotFoundError(errors.NotFoundError):
+    pass
+
+
+class PoolCategoryAlreadyExistsError(errors.ValidationError):
+    pass
+
+
+class PoolCategoryIsInUseError(errors.ValidationError):
+    pass
+
+
+class InvalidPoolCategoryNameError(errors.ValidationError):
+    pass
+
+
+class InvalidPoolCategoryColorError(errors.ValidationError):
+    pass
+
+
+def _verify_name_validity(name: str) -> None:
+    name_regex = config.config['pool_category_name_regex']
+    if not re.match(name_regex, name):
+        raise InvalidPoolCategoryNameError(
+            'Name must satisfy regex %r.' % name_regex)
+
+
+class PoolCategorySerializer(serialization.BaseSerializer):
+    def __init__(self, category: model.PoolCategory) -> None:
+        self.category = category
+
+    def _serializers(self) -> Dict[str, Callable[[], Any]]:
+        return {
+            'name': self.serialize_name,
+            'version': self.serialize_version,
+            'color': self.serialize_color,
+            'usages': self.serialize_usages,
+            'default': self.serialize_default,
+        }
+
+    def serialize_name(self) -> Any:
+        return self.category.name
+
+    def serialize_version(self) -> Any:
+        return self.category.version
+
+    def serialize_color(self) -> Any:
+        return self.category.color
+
+    def serialize_usages(self) -> Any:
+        return self.category.pool_count
+
+    def serialize_default(self) -> Any:
+        return self.category.default
+
+
+def serialize_category(
+        category: Optional[model.PoolCategory],
+        options: List[str] = []) -> Optional[rest.Response]:
+    if not category:
+        return None
+    return PoolCategorySerializer(category).serialize(options)
+
+
+def create_category(name: str, color: str) -> model.PoolCategory:
+    category = model.PoolCategory()
+    update_category_name(category, name)
+    update_category_color(category, color)
+    if not get_all_categories():
+        category.default = True
+    return category
+
+
+def update_category_name(category: model.PoolCategory, name: str) -> None:
+    assert category
+    if not name:
+        raise InvalidPoolCategoryNameError('Name cannot be empty.')
+    expr = sa.func.lower(model.PoolCategory.name) == name.lower()
+    if category.pool_category_id:
+        expr = expr & (
+            model.PoolCategory.pool_category_id != category.pool_category_id)
+    already_exists = (
+        db.session.query(model.PoolCategory).filter(expr).count() > 0)
+    if already_exists:
+        raise PoolCategoryAlreadyExistsError(
+            'A category with this name already exists.')
+    if util.value_exceeds_column_size(name, model.PoolCategory.name):
+        raise InvalidPoolCategoryNameError('Name is too long.')
+    _verify_name_validity(name)
+    category.name = name
+    cache.remove(DEFAULT_CATEGORY_NAME_CACHE_KEY)
+
+
+def update_category_color(category: model.PoolCategory, color: str) -> None:
+    assert category
+    if not color:
+        raise InvalidPoolCategoryColorError('Color cannot be empty.')
+    if not re.match(r'^#?[0-9a-z]+$', color):
+        raise InvalidPoolCategoryColorError('Invalid color.')
+    if util.value_exceeds_column_size(color, model.PoolCategory.color):
+        raise InvalidPoolCategoryColorError('Color is too long.')
+    category.color = color
+
+
+def try_get_category_by_name(
+        name: str, lock: bool = False) -> Optional[model.PoolCategory]:
+    query = (
+        db.session
+        .query(model.PoolCategory)
+        .filter(sa.func.lower(model.PoolCategory.name) == name.lower()))
+    if lock:
+        query = query.with_for_update()
+    return query.one_or_none()
+
+
+def get_category_by_name(name: str, lock: bool = False) -> model.PoolCategory:
+    category = try_get_category_by_name(name, lock)
+    if not category:
+        raise PoolCategoryNotFoundError('Pool category %r not found.' % name)
+    return category
+
+
+def get_all_category_names() -> List[str]:
+    return [cat.name for cat in get_all_categories()]
+
+
+def get_all_categories() -> List[model.PoolCategory]:
+    return db.session.query(model.PoolCategory).order_by(
+        model.PoolCategory.name.asc()).all()
+
+
+def try_get_default_category(
+        lock: bool = False) -> Optional[model.PoolCategory]:
+    query = (
+        db.session
+        .query(model.PoolCategory)
+        .filter(model.PoolCategory.default))
+    if lock:
+        query = query.with_for_update()
+    category = query.first()
+    # if for some reason (e.g. as a result of migration) there's no default
+    # category, get the first record available.
+    if not category:
+        query = (
+            db.session
+            .query(model.PoolCategory)
+            .order_by(model.PoolCategory.pool_category_id.asc()))
+        if lock:
+            query = query.with_for_update()
+        category = query.first()
+    return category
+
+
+def get_default_category(lock: bool = False) -> model.PoolCategory:
+    category = try_get_default_category(lock)
+    if not category:
+        raise PoolCategoryNotFoundError('No pool category created yet.')
+    return category
+
+
+def get_default_category_name() -> str:
+    if cache.has(DEFAULT_CATEGORY_NAME_CACHE_KEY):
+        return cache.get(DEFAULT_CATEGORY_NAME_CACHE_KEY)
+    default_category = get_default_category()
+    default_category_name = default_category.name
+    cache.put(DEFAULT_CATEGORY_NAME_CACHE_KEY, default_category_name)
+    return default_category_name
+
+
+def set_default_category(category: model.PoolCategory) -> None:
+    assert category
+    old_category = try_get_default_category(lock=True)
+    if old_category:
+        db.session.refresh(old_category)
+        old_category.default = False
+    db.session.refresh(category)
+    category.default = True
+    cache.remove(DEFAULT_CATEGORY_NAME_CACHE_KEY)
+
+
+def delete_category(category: model.PoolCategory) -> None:
+    assert category
+    if len(get_all_category_names()) == 1:
+        raise PoolCategoryIsInUseError('Cannot delete the last category.')
+    if (category.pool_count or 0) > 0:
+        raise PoolCategoryIsInUseError(
+            'Pool category has some usages and cannot be deleted. ' +
+            'Please remove this category from relevant pools first..')
+    db.session.delete(category)
diff --git a/server/szurubooru/func/pools.py b/server/szurubooru/func/pools.py
new file mode 100644
index 0000000..d35fd49
--- /dev/null
+++ b/server/szurubooru/func/pools.py
@@ -0,0 +1,302 @@
+import re
+from typing import Any, Optional, Tuple, List, Dict, Callable
+from datetime import datetime
+import sqlalchemy as sa
+from szurubooru import config, db, model, errors, rest
+from szurubooru.func import util, pool_categories, serialization
+
+
+
+class PoolNotFoundError(errors.NotFoundError):
+    pass
+
+
+class PoolAlreadyExistsError(errors.ValidationError):
+    pass
+
+
+class PoolIsInUseError(errors.ValidationError):
+    pass
+
+
+class InvalidPoolNameError(errors.ValidationError):
+    pass
+
+
+class InvalidPoolRelationError(errors.ValidationError):
+    pass
+
+
+class InvalidPoolCategoryError(errors.ValidationError):
+    pass
+
+
+class InvalidPoolDescriptionError(errors.ValidationError):
+    pass
+
+
+def _verify_name_validity(name: str) -> None:
+    if util.value_exceeds_column_size(name, model.PoolName.name):
+        raise InvalidPoolNameError('Name is too long.')
+    name_regex = config.config['pool_name_regex']
+    if not re.match(name_regex, name):
+        raise InvalidPoolNameError('Name must satisfy regex %r.' % name_regex)
+
+
+def _get_names(pool: model.Pool) -> List[str]:
+    assert pool
+    return [pool_name.name for pool_name in pool.names]
+
+
+def _lower_list(names: List[str]) -> List[str]:
+    return [name.lower() for name in names]
+
+
+def _check_name_intersection(
+        names1: List[str], names2: List[str], case_sensitive: bool) -> bool:
+    if not case_sensitive:
+        names1 = _lower_list(names1)
+        names2 = _lower_list(names2)
+    return len(set(names1).intersection(names2)) > 0
+
+
+def sort_pools(pools: List[model.Pool]) -> List[model.Pool]:
+    default_category_name = pool_categories.get_default_category_name()
+    return sorted(
+        pools,
+        key=lambda pool: (
+            default_category_name == pool.category.name,
+            pool.category.name,
+            pool.names[0].name)
+    )
+
+
+class PoolSerializer(serialization.BaseSerializer):
+    def __init__(self, pool: model.Pool) -> None:
+        self.pool = pool
+
+    def _serializers(self) -> Dict[str, Callable[[], Any]]:
+        return {
+            'id': self.serialize_id,
+            'names': self.serialize_names,
+            'category': self.serialize_category,
+            'version': self.serialize_version,
+            'description': self.serialize_description,
+            'creationTime': self.serialize_creation_time,
+            'lastEditTime': self.serialize_last_edit_time,
+            'postCount': self.serialize_post_count
+        }
+
+    def serialize_id(self) -> Any:
+        return self.pool.pool_id
+
+    def serialize_names(self) -> Any:
+        return [pool_name.name for pool_name in self.pool.names]
+
+    def serialize_category(self) -> Any:
+        return self.pool.category.name
+
+    def serialize_version(self) -> Any:
+        return self.pool.version
+
+    def serialize_description(self) -> Any:
+        return self.pool.description
+
+    def serialize_creation_time(self) -> Any:
+        return self.pool.creation_time
+
+    def serialize_last_edit_time(self) -> Any:
+        return self.pool.last_edit_time
+
+    def serialize_post_count(self) -> Any:
+        return self.pool.post_count
+
+
+def serialize_pool(
+        pool: model.Pool, options: List[str] = []) -> Optional[rest.Response]:
+    if not pool:
+        return None
+    return PoolSerializer(pool).serialize(options)
+
+
+def try_get_pool_by_id(pool_id: int) -> Optional[model.Pool]:
+    return (
+        db.session
+        .query(model.Pool)
+        .filter(model.Pool.pool_id == pool_id)
+        .one_or_none())
+
+
+def get_pool_by_id(pool_id: int) -> model.Pool:
+    pool = try_get_pool_by_id(pool_id)
+    if not pool:
+        raise PoolNotFoundError('Pool %r not found.' % pool_id)
+    return pool
+
+
+def try_get_pool_by_name(name: str) -> Optional[model.Pool]:
+    return (
+        db.session
+        .query(model.Pool)
+        .join(model.PoolName)
+        .filter(sa.func.lower(model.PoolName.name) == name.lower())
+        .one_or_none())
+
+
+def get_pool_by_name(name: str) -> model.Pool:
+    pool = try_get_pool_by_name(name)
+    if not pool:
+        raise PoolNotFoundError('Pool %r not found.' % name)
+    return pool
+
+
+def get_pools_by_names(names: List[str]) -> List[model.Pool]:
+    names = util.icase_unique(names)
+    if len(names) == 0:
+        return []
+    return (
+        db.session.query(model.Pool)
+        .join(model.PoolName)
+        .filter(
+            sa.sql.or_(
+                sa.func.lower(model.PoolName.name) == name.lower()
+                for name in names))
+        .all())
+
+
+def get_or_create_pools_by_names(
+        names: List[str]) -> Tuple[List[model.Pool], List[model.Pool]]:
+    names = util.icase_unique(names)
+    existing_pools = get_pools_by_names(names)
+    new_pools = []
+    pool_category_name = pool_categories.get_default_category_name()
+    for name in names:
+        found = False
+        for existing_pool in existing_pools:
+            if _check_name_intersection(
+                    _get_names(existing_pool), [name], False):
+                found = True
+                break
+        if not found:
+            new_pool = create_pool(
+                names=[name],
+                category_name=pool_category_name)
+            db.session.add(new_pool)
+            new_pools.append(new_pool)
+    return existing_pools, new_pools
+
+
+def delete(source_pool: model.Pool) -> None:
+    assert source_pool
+    db.session.delete(source_pool)
+
+
+def merge_pools(source_pool: model.Pool, target_pool: model.Pool) -> None:
+    assert source_pool
+    assert target_pool
+    if source_pool.pool_id == target_pool.pool_id:
+        raise InvalidPoolRelationError('Cannot merge pool with itself.')
+
+    def merge_posts(source_pool_id: int, target_pool_id: int) -> None:
+        pass
+        # alias1 = model.PostPool
+        # alias2 = sa.orm.util.aliased(model.PostPool)
+        # update_stmt = (
+        #     sa.sql.expression.update(alias1)
+        #     .where(alias1.pool_id == source_pool_id))
+        # update_stmt = (
+        #     update_stmt
+        #     .where(
+        #         ~sa.exists()
+        #         .where(alias1.post_id == alias2.post_id)
+        #         .where(alias2.pool_id == target_pool_id)))
+        # update_stmt = update_stmt.values(pool_id=target_pool_id)
+        # db.session.execute(update_stmt)
+
+    def merge_relations(
+            table: model.Base, source_pool_id: int, target_pool_id: int) -> None:
+        alias1 = table
+        alias2 = sa.orm.util.aliased(table)
+        update_stmt = (
+            sa.sql.expression.update(alias1)
+            .where(alias1.parent_id == source_pool_id)
+            .where(alias1.child_id != target_pool_id)
+            .where(
+                ~sa.exists()
+                .where(alias2.child_id == alias1.child_id)
+                .where(alias2.parent_id == target_pool_id))
+            .values(parent_id=target_pool_id))
+        db.session.execute(update_stmt)
+
+        update_stmt = (
+            sa.sql.expression.update(alias1)
+            .where(alias1.child_id == source_pool_id)
+            .where(alias1.parent_id != target_pool_id)
+            .where(
+                ~sa.exists()
+                .where(alias2.parent_id == alias1.parent_id)
+                .where(alias2.child_id == target_pool_id))
+            .values(child_id=target_pool_id))
+        db.session.execute(update_stmt)
+
+    merge_posts(source_pool.pool_id, target_pool.pool_id)
+    delete(source_pool)
+
+
+def create_pool(
+        names: List[str],
+        category_name: str) -> model.Pool:
+    pool = model.Pool()
+    pool.creation_time = datetime.utcnow()
+    update_pool_names(pool, names)
+    update_pool_category_name(pool, category_name)
+    return pool
+
+
+def update_pool_category_name(pool: model.Pool, category_name: str) -> None:
+    assert pool
+    pool.category = pool_categories.get_category_by_name(category_name)
+
+
+def update_pool_names(pool: model.Pool, names: List[str]) -> None:
+    # sanitize
+    assert pool
+    names = util.icase_unique([name for name in names if name])
+    if not len(names):
+        raise InvalidPoolNameError('At least one name must be specified.')
+    for name in names:
+        _verify_name_validity(name)
+
+    # check for existing pools
+    expr = sa.sql.false()
+    for name in names:
+        expr = expr | (sa.func.lower(model.PoolName.name) == name.lower())
+    if pool.pool_id:
+        expr = expr & (model.PoolName.pool_id != pool.pool_id)
+    existing_pools = db.session.query(model.PoolName).filter(expr).all()
+    if len(existing_pools):
+        raise PoolAlreadyExistsError(
+            'One of names is already used by another pool.')
+
+    # remove unwanted items
+    for pool_name in pool.names[:]:
+        if not _check_name_intersection([pool_name.name], names, True):
+            pool.names.remove(pool_name)
+    # add wanted items
+    for name in names:
+        if not _check_name_intersection(_get_names(pool), [name], True):
+            pool.names.append(model.PoolName(name, -1))
+
+    # set alias order to match the request
+    for i, name in enumerate(names):
+        for pool_name in pool.names:
+            if pool_name.name.lower() == name.lower():
+                pool_name.order = i
+
+
+def update_pool_description(pool: model.Pool, description: str) -> None:
+    assert pool
+    if util.value_exceeds_column_size(description, model.Pool.description):
+        raise InvalidPoolDescriptionError('Description is too long.')
+    pool.description = description or None
+    
diff --git a/server/szurubooru/func/snapshots.py b/server/szurubooru/func/snapshots.py
index eade3a1..e9ce07c 100644
--- a/server/szurubooru/func/snapshots.py
+++ b/server/szurubooru/func/snapshots.py
@@ -24,6 +24,24 @@ def get_tag_snapshot(tag: model.Tag) -> Dict[str, Any]:
     }
 
 
+def get_pool_category_snapshot(category: model.PoolCategory) -> Dict[str, Any]:
+    assert category
+    return {
+        'name': category.name,
+        'color': category.color,
+        'default': True if category.default else False,
+    }
+
+
+def get_pool_snapshot(pool: model.Pool) -> Dict[str, Any]:
+    assert pool
+    return {
+        'names': [pool_name.name for pool_name in pool.names],
+        'category': pool.category.name,
+        # TODO
+    }
+
+
 def get_post_snapshot(post: model.Post) -> Dict[str, Any]:
     assert post
     return {
@@ -47,6 +65,8 @@ _snapshot_factories = {
     'tag_category': lambda entity: get_tag_category_snapshot(entity),
     'tag': lambda entity: get_tag_snapshot(entity),
     'post': lambda entity: get_post_snapshot(entity),
+    'pool_category': lambda entity: get_pool_category_snapshot(entity),
+    'pool': lambda entity: get_pool_snapshot(entity),
 }  # type: Dict[model.Base, Callable[[model.Base], Dict[str ,Any]]]
 
 
diff --git a/server/szurubooru/func/tags.py b/server/szurubooru/func/tags.py
index 7d92f1e..60eda50 100644
--- a/server/szurubooru/func/tags.py
+++ b/server/szurubooru/func/tags.py
@@ -1,5 +1,3 @@
-import json
-import os
 import re
 from typing import Any, Optional, Tuple, List, Dict, Callable
 from datetime import datetime
diff --git a/server/szurubooru/migrations/versions/54de8acc6cef_add_default_pool_category.py b/server/szurubooru/migrations/versions/54de8acc6cef_add_default_pool_category.py
new file mode 100644
index 0000000..782833c
--- /dev/null
+++ b/server/szurubooru/migrations/versions/54de8acc6cef_add_default_pool_category.py
@@ -0,0 +1,60 @@
+'''
+add default pool category
+
+Revision ID: 54de8acc6cef
+Created at: 2020-05-03 14:57:46.825766
+'''
+
+import sqlalchemy as sa
+from alembic import op
+
+
+revision = '54de8acc6cef'
+down_revision = '6a2f424ec9d2'
+branch_labels = None
+depends_on = None
+
+
+Base = sa.ext.declarative.declarative_base()
+
+
+class PoolCategory(Base):
+    __tablename__ = 'pool_category'
+    __table_args__ = {'extend_existing': True}
+
+    pool_category_id = sa.Column('id', sa.Integer, primary_key=True)
+    version = sa.Column('version', sa.Integer, nullable=False)
+    name = sa.Column('name', sa.Unicode(32), nullable=False)
+    color = sa.Column('color', sa.Unicode(32), nullable=False)
+    default = sa.Column('default', sa.Boolean, nullable=False)
+
+    __mapper_args__ = {
+        'version_id_col': version,
+        'version_id_generator': False,
+    }
+
+def upgrade():
+    session = sa.orm.session.Session(bind=op.get_bind())
+    if session.query(PoolCategory).count() == 0:
+        category = PoolCategory()
+        category.name = 'default'
+        category.color = 'default'
+        category.version = 1
+        category.default = True
+        session.add(category)
+    session.commit()
+
+
+def downgrade():
+    session = sa.orm.session.Session(bind=op.get_bind())
+    default_category = (
+        session
+        .query(PoolCategory)
+        .filter(PoolCategory.name == 'default')
+        .filter(PoolCategory.color == 'default')
+        .filter(PoolCategory.version == 1)
+        .filter(PoolCategory.default == 1)
+        .one_or_none())
+    if default_category:
+        session.delete(default_category)
+    session.commit()
diff --git a/server/szurubooru/migrations/versions/6a2f424ec9d2_create_pool_tables.py b/server/szurubooru/migrations/versions/6a2f424ec9d2_create_pool_tables.py
new file mode 100644
index 0000000..9b663ac
--- /dev/null
+++ b/server/szurubooru/migrations/versions/6a2f424ec9d2_create_pool_tables.py
@@ -0,0 +1,52 @@
+'''
+create pool tables
+
+Revision ID: 6a2f424ec9d2
+Created at: 2020-05-03 14:47:59.136410
+'''
+
+import sqlalchemy as sa
+from alembic import op
+
+
+revision = '6a2f424ec9d2'
+down_revision = '1e280b5d5df1'
+branch_labels = None
+depends_on = None
+
+def upgrade():
+    op.create_table(
+        'pool_category',
+        sa.Column('id', sa.Integer(), nullable=False),
+        sa.Column('version', sa.Integer(), nullable=False, default=1),
+        sa.Column('name', sa.Unicode(length=32), nullable=False),
+        sa.Column('color', sa.Unicode(length=32), nullable=False),
+        sa.Column('default', sa.Boolean(), nullable=False, default=False),
+        sa.PrimaryKeyConstraint('id'))
+
+    op.create_table(
+        'pool',
+        sa.Column('id', sa.Integer(), nullable=False),
+        sa.Column('version', sa.Integer(), nullable=False, default=1),
+        sa.Column('description', sa.UnicodeText(), nullable=True),
+        sa.Column('category_id', sa.Integer(), nullable=False),
+        sa.Column('creation_time', sa.DateTime(), nullable=False),
+        sa.Column('last_edit_time', sa.DateTime(), nullable=True),
+        sa.ForeignKeyConstraint(['category_id'], ['pool_category.id']),
+        sa.PrimaryKeyConstraint('id'))
+
+    op.create_table(
+        'pool_name',
+        sa.Column('pool_name_id', sa.Integer(), nullable=False),
+        sa.Column('pool_id', sa.Integer(), nullable=False),
+        sa.Column('name', sa.Unicode(length=256), nullable=False),
+        sa.Column('ord', sa.Integer(), nullable=False, index=True),
+        sa.ForeignKeyConstraint(['pool_id'], ['pool.id']),
+        sa.PrimaryKeyConstraint('pool_name_id'),
+        sa.UniqueConstraint('name'))
+
+def downgrade():
+    op.drop_index(op.f('ix_pool_name_ord'), table_name='pool_name')
+    op.drop_table('pool_name')
+    op.drop_table('pool')
+    op.drop_table('pool_category')
diff --git a/server/szurubooru/model/__init__.py b/server/szurubooru/model/__init__.py
index a043bd1..cf18b0f 100644
--- a/server/szurubooru/model/__init__.py
+++ b/server/szurubooru/model/__init__.py
@@ -11,6 +11,8 @@ from szurubooru.model.post import (
     PostNote,
     PostFeature,
     PostSignature)
+from szurubooru.model.pool import Pool, PoolName
+from szurubooru.model.pool_category import PoolCategory
 from szurubooru.model.comment import Comment, CommentScore
 from szurubooru.model.snapshot import Snapshot
 import szurubooru.model.util
diff --git a/server/szurubooru/model/pool.py b/server/szurubooru/model/pool.py
new file mode 100644
index 0000000..2505150
--- /dev/null
+++ b/server/szurubooru/model/pool.py
@@ -0,0 +1,70 @@
+import sqlalchemy as sa
+from szurubooru.model.base import Base
+
+
+class PoolName(Base):
+    __tablename__ = 'pool_name'
+
+    pool_name_id = sa.Column('pool_name_id', sa.Integer, primary_key=True)
+    pool_id = sa.Column(
+        'pool_id',
+        sa.Integer,
+        sa.ForeignKey('pool.id'),
+        nullable=False,
+        index=True)
+    name = sa.Column('name', sa.Unicode(128), nullable=False, unique=True)
+    order = sa.Column('ord', sa.Integer, nullable=False, index=True)
+
+    def __init__(self, name: str, order: int) -> None:
+        self.name = name
+        self.order = order
+
+class Pool(Base):
+    __tablename__ = 'pool'
+
+    pool_id = sa.Column('id', sa.Integer, primary_key=True)
+    category_id = sa.Column(
+        'category_id',
+        sa.Integer,
+        sa.ForeignKey('pool_category.id'),
+        nullable=False,
+        index=True)
+    version = sa.Column('version', sa.Integer, default=1, nullable=False)
+    creation_time = sa.Column('creation_time', sa.DateTime, nullable=False)
+    last_edit_time = sa.Column('last_edit_time', sa.DateTime)
+    description = sa.Column('description', sa.UnicodeText, default=None)
+
+    category = sa.orm.relationship('PoolCategory', lazy='joined')
+    names = sa.orm.relationship(
+        'PoolName',
+        cascade='all,delete-orphan',
+        lazy='joined',
+        order_by='PoolName.order')
+
+    # post_count = sa.orm.column_property(
+    #     sa.sql.expression.select(
+    #         [sa.sql.expression.func.count(PostPool.post_id)])
+    #     .where(PostPool.pool_id == pool_id)
+    #     .correlate_except(PostPool))
+    # TODO
+    from random import randint
+    post_count = sa.orm.column_property(
+        sa.sql.expression.select([randint(1, 1000)])
+            .limit(1)
+            .as_scalar())
+
+    first_name = sa.orm.column_property(
+        (
+            sa.sql.expression.select([PoolName.name])
+            .where(PoolName.pool_id == pool_id)
+            .order_by(PoolName.order)
+            .limit(1)
+            .as_scalar()
+        ),
+        deferred=True)
+
+
+    __mapper_args__ = {
+        'version_id_col': version,
+        'version_id_generator': False,
+    }
diff --git a/server/szurubooru/model/pool_category.py b/server/szurubooru/model/pool_category.py
new file mode 100644
index 0000000..5af0b11
--- /dev/null
+++ b/server/szurubooru/model/pool_category.py
@@ -0,0 +1,28 @@
+from typing import Optional
+import sqlalchemy as sa
+from szurubooru.model.base import Base
+from szurubooru.model.pool import Pool
+
+
+class PoolCategory(Base):
+    __tablename__ = 'pool_category'
+
+    pool_category_id = sa.Column('id', sa.Integer, primary_key=True)
+    version = sa.Column('version', sa.Integer, default=1, nullable=False)
+    name = sa.Column('name', sa.Unicode(32), nullable=False)
+    color = sa.Column(
+        'color', sa.Unicode(32), nullable=False, default='#000000')
+    default = sa.Column('default', sa.Boolean, nullable=False, default=False)
+
+    def __init__(self, name: Optional[str] = None) -> None:
+        self.name = name
+
+    pool_count = sa.orm.column_property(
+        sa.sql.expression.select([sa.sql.expression.func.count('Pool.pool_id')])
+        .where(Pool.category_id == pool_category_id)
+        .correlate_except(sa.table('Pool')))
+
+    __mapper_args__ = {
+        'version_id_col': version,
+        'version_id_generator': False,
+    }
diff --git a/server/szurubooru/model/util.py b/server/szurubooru/model/util.py
index e82539f..ab86100 100644
--- a/server/szurubooru/model/util.py
+++ b/server/szurubooru/model/util.py
@@ -10,6 +10,8 @@ def get_resource_info(entity: Base) -> Tuple[Any, Any, Union[str, int]]:
         'tag_category': lambda category: category.name,
         'comment': lambda comment: comment.comment_id,
         'post': lambda post: post.post_id,
+        'pool': lambda pool: pool.pool_id,
+        'pool_category': lambda category: category.name,
     }  # type: Dict[str, Callable[[Base], Any]]
 
     resource_type = entity.__table__.name
diff --git a/server/szurubooru/search/configs/__init__.py b/server/szurubooru/search/configs/__init__.py
index c7e3102..c6a3ea4 100644
--- a/server/szurubooru/search/configs/__init__.py
+++ b/server/szurubooru/search/configs/__init__.py
@@ -3,3 +3,4 @@ from .tag_search_config import TagSearchConfig
 from .post_search_config import PostSearchConfig
 from .snapshot_search_config import SnapshotSearchConfig
 from .comment_search_config import CommentSearchConfig
+from .pool_search_config import PoolSearchConfig
diff --git a/server/szurubooru/search/configs/pool_search_config.py b/server/szurubooru/search/configs/pool_search_config.py
new file mode 100644
index 0000000..ddc325a
--- /dev/null
+++ b/server/szurubooru/search/configs/pool_search_config.py
@@ -0,0 +1,109 @@
+from typing import Tuple, Dict
+import sqlalchemy as sa
+from szurubooru import db, model
+from szurubooru.func import util
+from szurubooru.search.typing import SaColumn, SaQuery
+from szurubooru.search.configs import util as search_util
+from szurubooru.search.configs.base_search_config import (
+    BaseSearchConfig, Filter)
+
+
+class PoolSearchConfig(BaseSearchConfig):
+    def create_filter_query(self, _disable_eager_loads: bool) -> SaQuery:
+        strategy = (
+            sa.orm.lazyload
+            if _disable_eager_loads
+            else sa.orm.subqueryload)
+        return (
+            db.session.query(model.Pool)
+            .join(model.PoolCategory)
+            .options(
+                strategy(model.Pool.names)))
+
+    def create_count_query(self, _disable_eager_loads: bool) -> SaQuery:
+        return db.session.query(model.Pool)
+
+    def create_around_query(self) -> SaQuery:
+        raise NotImplementedError()
+
+    def finalize_query(self, query: SaQuery) -> SaQuery:
+        return query.order_by(model.Pool.first_name.asc())
+
+    @property
+    def anonymous_filter(self) -> Filter:
+        return search_util.create_subquery_filter(
+            model.Pool.pool_id,
+            model.PoolName.pool_id,
+            model.PoolName.name,
+            search_util.create_str_filter)
+
+    @property
+    def named_filters(self) -> Dict[str, Filter]:
+        return util.unalias_dict([
+            (
+                ['name'],
+                search_util.create_subquery_filter(
+                    model.Pool.pool_id,
+                    model.PoolName.pool_id,
+                    model.PoolName.name,
+                    search_util.create_str_filter)
+            ),
+
+            (
+                ['category'],
+                search_util.create_subquery_filter(
+                    model.Pool.category_id,
+                    model.PoolCategory.pool_category_id,
+                    model.PoolCategory.name,
+                    search_util.create_str_filter)
+            ),
+
+            (
+                ['creation-date', 'creation-time'],
+                search_util.create_date_filter(model.Pool.creation_time)
+            ),
+
+            (
+                ['last-edit-date', 'last-edit-time', 'edit-date', 'edit-time'],
+                search_util.create_date_filter(model.Pool.last_edit_time)
+            ),
+
+            (
+                ['post-count'],
+                search_util.create_num_filter(model.Pool.post_count)
+            ),
+        ])
+
+    @property
+    def sort_columns(self) -> Dict[str, Tuple[SaColumn, str]]:
+        return util.unalias_dict([
+            (
+                ['random'],
+                (sa.sql.expression.func.random(), self.SORT_NONE)
+            ),
+
+            (
+                ['name'],
+                (model.Pool.first_name, self.SORT_ASC)
+            ),
+
+            (
+                ['category'],
+                (model.PoolCategory.name, self.SORT_ASC)
+            ),
+
+            (
+                ['creation-date', 'creation-time'],
+                (model.Pool.creation_time, self.SORT_DESC)
+            ),
+
+            (
+                ['last-edit-date', 'last-edit-time', 'edit-date', 'edit-time'],
+                (model.Pool.last_edit_time, self.SORT_DESC)
+            ),
+
+            (
+                ['post-count'],
+                (model.Pool.post_count, self.SORT_DESC)
+            ),
+        ])