diff options
| -rw-r--r-- | uploader/__init__.py | 8 | ||||
| -rw-r--r-- | uploader/publications/datatables.py | 52 | ||||
| -rw-r--r-- | uploader/publications/models.py | 19 | ||||
| -rw-r--r-- | uploader/publications/views.py | 25 | ||||
| -rw-r--r-- | uploader/static/css/styles.css | 6 | ||||
| -rw-r--r-- | uploader/templates/phenotypes/add-phenotypes-base.html | 3 |
6 files changed, 81 insertions, 32 deletions
diff --git a/uploader/__init__.py b/uploader/__init__.py index 97b9af5..8b49ad5 100644 --- a/uploader/__init__.py +++ b/uploader/__init__.py @@ -67,7 +67,13 @@ def setup_logging(app: Flask) -> Flask: def setup_modules_logging(app_logger): """Setup module-level loggers to the same log-level as the application.""" loglevel = logging.getLevelName(app_logger.getEffectiveLevel()) - logging.getLogger("uploader.publications.models").setLevel(loglevel) + + def __setup__(logger_name): + _logger = logging.getLogger(logger_name) + _logger.setLevel(loglevel) + + __setup__("uploader.publications.models") + __setup__("uploader.publications.datatables") def create_app(config: Optional[dict] = None): diff --git a/uploader/publications/datatables.py b/uploader/publications/datatables.py new file mode 100644 index 0000000..e07fafd --- /dev/null +++ b/uploader/publications/datatables.py @@ -0,0 +1,52 @@ +"""Fetch data for datatables.""" +import logging +from typing import Optional + +from MySQLdb.cursors import DictCursor + +from gn_libs.mysqldb import Connection, debug_query + +logger = logging.getLogger(__name__) + +def fetch_publications( + conn: Connection, + search: Optional[str] = None, + offset: int = 0, + limit: int = -1 +) -> tuple[dict, int, int, int]: + """Fetch publications from the database.""" + _query = "SELECT * FROM Publication" + _count_query = "SELECT COUNT(*) FROM Publication" + _params = None + _where_clause = "" + _limit_clause = "" + if search is not None and bool(search): + _where_clause = ("WHERE PubMed_ID LIKE %s " + "OR Authors LIKE %s " + "OR Title LIKE %s") + _params = (f"%{search}%",) * 3 + + if limit > 0: + _limit_clause = f"LIMIT {limit} OFFSET {offset}" + + with conn.cursor(cursorclass=DictCursor) as cursor: + cursor.execute("SELECT COUNT(*) FROM Publication") + _total_rows = int(cursor.fetchone()["COUNT(*)"]) + + cursor.execute(f"{_count_query} {_where_clause}", _params) + debug_query(cursor, logger) + _result = cursor.fetchone() + _total_filtered = int(_result["COUNT(*)"] if bool(_result) else 0) + + cursor.execute(f"{_query} {_where_clause} {_limit_clause}", _params) + debug_query(cursor, logger) + _current_filtered = tuple( + {**dict(row), "index": idx} + for idx, row + in enumerate(cursor.fetchall(), start=offset+1)) + + return ( + _current_filtered, + len(_current_filtered), + _total_filtered, + _total_rows) diff --git a/uploader/publications/models.py b/uploader/publications/models.py index 992ebfa..b199991 100644 --- a/uploader/publications/models.py +++ b/uploader/publications/models.py @@ -69,25 +69,6 @@ def update_publications(conn: Connection , publications: tuple[dict, ...]) -> tu return tuple() -def fetch_publications( - conn: Connection, - search: Optional[str] = None -) -> Iterable[dict]: - """Fetch publications from the database.""" - _query = "SELECT * FROM Publication" - _params = None - if search is not None and bool(search): - _query = (f"{_query} " - "WHERE CONCAT(PubMed_ID, ' ', Authors, ' ', Title) " - "LIKE %s") - _params = (f"%{search}%",) - with conn.cursor(cursorclass=DictCursor) as cursor: - cursor.execute(_query, _params) - debug_query(_query, logger) - for row in cursor.fetchall(): - yield dict(row) - - def fetch_publication_by_id(conn: Connection, publication_id: int) -> dict: """Fetch a specific publication from the database.""" with conn.cursor(cursorclass=DictCursor) as cursor: diff --git a/uploader/publications/views.py b/uploader/publications/views.py index 63acf1b..0608a35 100644 --- a/uploader/publications/views.py +++ b/uploader/publications/views.py @@ -15,11 +15,12 @@ from flask import ( from uploader.authorisation import require_login from .models import ( - fetch_publications, fetch_publication_by_id, create_new_publications, fetch_publication_phenotypes) +from .datatables import fetch_publications + from gn_libs.debug import __pk__ pubbp = Blueprint("publications", __name__) @@ -38,20 +39,22 @@ def index(): def list_publications(): # request breakdown: # https://datatables.net/manual/server-side + _page = int(request.args.get("draw")) + _length = int(request.args.get("length") or '-1') + _start = int(request.args.get("start") or '0') + _search = request.args["search[value]"] with (database_connection(app.config["SQL_URI"]) as conn, conn.cursor(cursorclass=DictCursor) as cursor): - cursor.execute("SELECT COUNT(*) FROM Publication") - _totalrows = int(cursor.fetchone()["COUNT(*)"]) - _publications = tuple({ - **row, "index": idx - } for idx,row in enumerate( - fetch_publications( - conn, request.args["search[value]"]), start=1)) + _publications, _current_rows, _totalfiltered, _totalrows = fetch_publications( + conn, + _search, + offset=_start, + limit=_length) return json.dumps({ - "draw": request.args.get("draw"), + "draw": _page, "recordsTotal": _totalrows, - "recordsFiltered": len(_publications), + "recordsFiltered": _totalfiltered, "publications": _publications, "status": "success" }) @@ -83,7 +86,7 @@ def create_publication(): with database_connection(app.config["SQL_URI"]) as conn: publications = create_new_publications(conn, ({ - "pubmed_id": form.get("pubmed-id"), + "pubmed_id": form.get("pubmed-id") or None, "abstract": form.get("publication-abstract").encode("utf8") or None, "authors": authors, "title": form.get("publication-title").encode("utf8") or None, diff --git a/uploader/static/css/styles.css b/uploader/static/css/styles.css index 826ac41..df50dec 100644 --- a/uploader/static/css/styles.css +++ b/uploader/static/css/styles.css @@ -179,3 +179,9 @@ table.dataTable thead th, table.dataTable tfoot th{ table.dataTable tbody tr.selected td { background-color: #ffee99 !important; } + +.form-group { + margin-bottom: 2em; + padding-bottom: 0.2em; + border-bottom: solid gray 1px; +} diff --git a/uploader/templates/phenotypes/add-phenotypes-base.html b/uploader/templates/phenotypes/add-phenotypes-base.html index 928fb84..01cd0fe 100644 --- a/uploader/templates/phenotypes/add-phenotypes-base.html +++ b/uploader/templates/phenotypes/add-phenotypes-base.html @@ -135,9 +135,10 @@ dataSrc: "publications" }, select: "single", + paging: true, scrollY: 700, - paging: false, deferRender: true, + scroller: true, layout: { topStart: "info", topEnd: "search" |