Repos / pytaku / 852c867a44
commit 852c867a4471034f29660cbcc6dfd116827c7ebe
Author: Bùi Thành Nhân <hi@imnhan.com>
Date: Thu Aug 6 22:25:19 2020 +0700
implement read history
Also squashed migrations because I'm a fraud
diff --git a/src/mangoapi/__init__.py b/src/mangoapi/__init__.py
index 7bb4fa5..fbf49bd 100644
--- a/src/mangoapi/__init__.py
+++ b/src/mangoapi/__init__.py
@@ -49,7 +49,7 @@ def get_title(title_id):
"descriptions": md_json["manga"]["description"].split("\r\n\r\n"),
"chapters": [
{
- "id": chap_id,
+ "id": str(chap_id),
"name": chap["title"],
"volume": int(chap["volume"]) if chap["volume"] else None,
"groups": _extract_groups(chap),
diff --git a/src/pytaku/database/common.py b/src/pytaku/database/common.py
index 34d82bc..f9a36d8 100644
--- a/src/pytaku/database/common.py
+++ b/src/pytaku/database/common.py
@@ -5,26 +5,32 @@
_conn = None
+def _row_trace(cursor, row):
+ """
+ Customize each result row's representation:
+ - If query only asks for 1 field, return that result directly instead of tuple
+ - If more than 1 field, return dict instead of tuple
+ """
+ desc = cursor.getdescription()
+ if len(desc) == 1:
+ return row[0]
+ else:
+ return {k[0]: row[i] for i, k in enumerate(desc)}
+
+
def get_conn():
global _conn
-
if not _conn:
_conn = apsw.Connection(DBNAME)
-
- # Apparently you need to enable this pragma _per connection_
+ # Apparently you need to enable this pragma per connection:
_conn.cursor().execute("PRAGMA foreign_keys = ON;")
-
- # Return rows as dicts instead of tuples
- _conn.setrowtrace(
- lambda cursor, row: {
- k[0]: row[i] for i, k in enumerate(cursor.getdescription())
- }
- )
-
+ _conn.setrowtrace(_row_trace)
return _conn
def run_sql(*args, **kwargs):
- cursor = get_conn().cursor()
- results = cursor.execute(*args, **kwargs)
- return list(results)
+ return list(run_sql_on_demand(*args, **kwargs))
+
+
+def run_sql_on_demand(*args, **kwargs):
+ return get_conn().cursor().execute(*args, **kwargs)
diff --git a/src/pytaku/database/migrations/latest_schema.sql b/src/pytaku/database/migrations/latest_schema.sql
index cdff562..2d88116 100644
--- a/src/pytaku/database/migrations/latest_schema.sql
+++ b/src/pytaku/database/migrations/latest_schema.sql
@@ -26,6 +26,7 @@ CREATE TABLE chapter (
foreign key (title_id, site) references title (id, site),
unique(id, title_id, site),
+ unique(id, site),
unique(num_major, num_minor, title_id)
);
CREATE TABLE user (
@@ -44,3 +45,13 @@ CREATE TABLE follow (
foreign key (user_id) references user (id),
unique(user_id, title_id, site)
);
+CREATE TABLE read (
+ user_id integer not null,
+ chapter_id text not null,
+ site text not null,
+ updated_at text default (datetime('now')),
+
+ foreign key (user_id) references user (id),
+ foreign key (chapter_id, site) references chapter (id, site),
+ unique(user_id, chapter_id, site)
+);
diff --git a/src/pytaku/database/migrations/m0001.sql b/src/pytaku/database/migrations/m0001.sql
index 7fb82ee..700acf3 100644
--- a/src/pytaku/database/migrations/m0001.sql
+++ b/src/pytaku/database/migrations/m0001.sql
@@ -20,9 +20,39 @@ create table chapter (
name text,
pages text,
groups text,
- updated_at text default (datetime('now')),
+ updated_at text default (datetime('now')), is_webtoon boolean,
foreign key (title_id, site) references title (id, site),
unique(id, title_id, site),
+ unique(id, site),
unique(num_major, num_minor, title_id)
);
+
+create table user (
+ id integer primary key,
+ username text unique,
+ password text,
+ created_at text default (datetime('now'))
+);
+
+create table follow (
+ user_id integer not null,
+ title_id text not null,
+ site text not null,
+ created_at text default (datetime('now')),
+
+ foreign key (title_id, site) references title (id, site),
+ foreign key (user_id) references user (id),
+ unique(user_id, title_id, site)
+);
+
+create table read (
+ user_id integer not null,
+ chapter_id text not null,
+ site text not null,
+ updated_at text default (datetime('now')),
+
+ foreign key (user_id) references user (id),
+ foreign key (chapter_id, site) references chapter (id, site),
+ unique(user_id, chapter_id, site)
+);
diff --git a/src/pytaku/database/migrations/m0002.sql b/src/pytaku/database/migrations/m0002.sql
deleted file mode 100644
index 442f3d8..0000000
--- a/src/pytaku/database/migrations/m0002.sql
+++ /dev/null
@@ -1 +0,0 @@
-alter table chapter add column is_webtoon boolean;
diff --git a/src/pytaku/database/migrations/m0003.sql b/src/pytaku/database/migrations/m0003.sql
deleted file mode 100644
index 1cd8c9b..0000000
--- a/src/pytaku/database/migrations/m0003.sql
+++ /dev/null
@@ -1,18 +0,0 @@
-create table user (
- id integer primary key,
- username text unique,
- password text,
- created_at text default (datetime('now'))
-);
-
-
-create table follow (
- user_id integer not null,
- title_id text not null,
- site text not null,
- created_at text default (datetime('now')),
-
- foreign key (title_id, site) references title (id, site),
- foreign key (user_id) references user (id),
- unique(user_id, title_id, site)
-);
diff --git a/src/pytaku/database/migrator.py b/src/pytaku/database/migrator.py
index c63519c..b275d64 100644
--- a/src/pytaku/database/migrator.py
+++ b/src/pytaku/database/migrator.py
@@ -15,7 +15,7 @@
def _get_current_version():
- return run_sql("PRAGMA user_version;")[0]["user_version"]
+ return run_sql("PRAGMA user_version;")[0]
def _get_version(migration: Path):
diff --git a/src/pytaku/decorators.py b/src/pytaku/decorators.py
index ff5f4e2..8d7b0bc 100644
--- a/src/pytaku/decorators.py
+++ b/src/pytaku/decorators.py
@@ -2,6 +2,8 @@
from flask import redirect, request, session, url_for
+from .persistence import read
+
def require_login(f):
@wraps(f)
@@ -11,3 +13,22 @@ def decorated_function(*args, **kwargs):
return f(*args, **kwargs)
return decorated_function
+
+
+def trigger_has_read(f):
+ """
+ Augments a view with the ability to mark a chapter as read if there's a
+ `?has_read=<chapter_id>` url param.
+ """
+
+ @wraps(f)
+ def decorated_function(*args, **kwargs):
+ assert "site" in kwargs # only use on site-specific views
+ has_read_chapter_id = request.args.get("has_read")
+ if has_read_chapter_id:
+ if session.get("user"):
+ read(session["user"]["id"], kwargs["site"], has_read_chapter_id)
+ return redirect(request.url[: request.url.rfind("?")])
+ return f(*args, **kwargs)
+
+ return decorated_function
diff --git a/src/pytaku/main.py b/src/pytaku/main.py
index 170d40a..af04fb9 100644
--- a/src/pytaku/main.py
+++ b/src/pytaku/main.py
@@ -17,7 +17,7 @@
from . import mangadex
from .conf import config
-from .decorators import require_login
+from .decorators import require_login, trigger_has_read
from .persistence import (
follow,
get_followed_titles,
@@ -155,6 +155,7 @@ def auth_view():
@app.route("/title/<site>/<title_id>")
+@trigger_has_read
def title_view(site, title_id):
user = session.get("user", None)
user_id = user["id"] if user else None
@@ -171,6 +172,7 @@ def title_view(site, title_id):
@app.route("/chapter/<site>/<chapter_id>")
+@trigger_has_read
def chapter_view(site, chapter_id):
chapter = load_chapter(site, chapter_id)
if not chapter:
diff --git a/src/pytaku/persistence.py b/src/pytaku/persistence.py
index 6792d4a..a03efbd 100644
--- a/src/pytaku/persistence.py
+++ b/src/pytaku/persistence.py
@@ -3,7 +3,7 @@
import apsw
import argon2
-from .database.common import run_sql
+from .database.common import run_sql, run_sql_on_demand
def save_title(title):
@@ -74,6 +74,23 @@ def load_title(site, title_id, user_id=None):
(user_id, site, title["id"]),
)
)
+
+ chapters_i_read = run_sql(
+ """
+ SELECT r.chapter_id
+ FROM read r
+ INNER JOIN chapter c ON c.id = r.chapter_id AND c.site = r.site
+ WHERE r.user_id = ?
+ AND c.title_id = ?
+ AND c.site = ?
+ ORDER BY r.updated_at;
+ """,
+ (user_id, title["id"], title["site"]),
+ )
+
+ for ch in title["chapters"]:
+ if ch["id"] in chapters_i_read:
+ ch["is_read"] = True
return title
@@ -130,7 +147,10 @@ def load_chapter(site, chapter_id):
elif len(result) > 1:
raise Exception(f"Found multiple results for chapter_id {chapter_id}!")
else:
- return result[0]
+ chapter = result[0]
+ chapter["pages"] = json.loads(chapter["pages"])
+ chapter["groups"] = json.loads(chapter["groups"])
+ return chapter
def get_prev_next_chapters(title, chapter):
@@ -209,9 +229,51 @@ def get_followed_titles(user_id):
""",
(user_id,),
)
- title_dicts = []
+
for t in titles:
- t["chapters"] = json.loads(t["chapters"])
- title_dicts.append(t)
+ chapters = json.loads(t["chapters"])
+
+ # n+1 queries cuz I don't give a f- actually I do, but sqlite's cool with it:
+ # https://www.sqlite.org/np1queryprob.html
+ chapters_i_finished = run_sql_on_demand(
+ """
+ SELECT r.chapter_id
+ FROM read r
+ INNER JOIN chapter c ON c.id = r.chapter_id AND c.site = r.site
+ WHERE r.user_id = ?
+ AND c.title_id = ?
+ AND c.site = ?
+ ORDER BY c.num_major desc, c.num_minor desc;
+ """,
+ (user_id, t["id"], t["site"]),
+ )
+ # Cut off chapter list:
+ # only show chapters newer than the latest chapter that user has finished.
+ # Running a loop here instead of just picking the one latest finished chapter
+ # because source site may have deleted said chapter.
+ for finished_chapter_id in chapters_i_finished:
+ for i, ch in enumerate(chapters):
+ if finished_chapter_id == ch["id"]:
+ chapters = chapters[:i]
+ break
+
+ t["chapters"] = chapters
+
+ return sorted(titles, key=lambda t: len(t["chapters"]), reverse=True)
+
- return title_dicts
+def read(user_id, site, chapter_id):
+ run_sql(
+ """
+ INSERT INTO read (user_id, site, chapter_id) VALUES (?, ?, ?)
+ ON CONFLICT (user_id, site, chapter_id) DO UPDATE SET updated_at=datetime('now')
+ """,
+ (user_id, site, chapter_id),
+ )
+
+
+def unread(user_id, site, chapter_id):
+ run_sql(
+ "DELETE FROM read WHERE user_id=? AND site=? AND chapter_id=?;",
+ (user_id, site, chapter_id),
+ )
diff --git a/src/pytaku/templates/chapter.html b/src/pytaku/templates/chapter.html
index b36ffd0..05248bd 100644
--- a/src/pytaku/templates/chapter.html
+++ b/src/pytaku/templates/chapter.html
@@ -69,10 +69,21 @@ <h1>{{ self.title() }}</h1>
{{ ibutton(href=url_for('title_view', title_id=title_id, site=site), left_icon='list', text='Chapter list', color='blue') }}
{% if next_chapter %}
- {{ ibutton(href=url_for('chapter_view', site=site, chapter_id=next_chapter['id']), right_icon='chevrons-right', text='Next') }}
+
+ {% set next_url = url_for('chapter_view', site=site, chapter_id=next_chapter['id']) %}
+ {% if session['user'] %}
+ {% set next_url = next_url + '?has_read=' + id %}
+ {% endif %}
+ {{ ibutton(href=next_url, right_icon='chevrons-right', text='Next') }}
+
{% else %}
- {{ ibutton(right_icon='chevrons-right', text='Next', disabled=True) }}
+ {% if session['user'] %}
+ {{ ibutton(href=url_for('title_view', site=site, title_id=title_id) + '?has_read=' + id, text='✓ Finish reading', color='green') }}
+ {% else %}
+ {{ ibutton(right_icon='chevrons-right', text='Next', disabled=True) }}
+ {% endif %}
{% endif %}
+
</div>
{% endblock %}
diff --git a/src/pytaku/templates/title.html b/src/pytaku/templates/title.html
index 3ed3462..061a70a 100644
--- a/src/pytaku/templates/title.html
+++ b/src/pytaku/templates/title.html
@@ -44,11 +44,13 @@ <h1>{{ name }}</h1>
<table>
<tr>
+ <th>Finished?</th>
<th>Name</th>
<th>Group</th>
</tr>
{% for chapter in chapters %}
<tr>
+ <td>{% if chapter['is_read'] %}yes{% endif %}</td>
<td>
<a href="{{ url_for('chapter_view', chapter_id=chapter['id'], site=site) }}">
Chapter {{ chapter['number'] }}