commit 2094028b2567ac1f0dc65a2b7d022a2d1b9167b6
Author: Bùi Thành Nhân <hi@imnhan.com>
Date:   Sun Aug 2 11:39:02 2020 +0700

    correctly set journal_mode & foreign_keys pragmas

diff --git a/src/pytaku/database/common.py b/src/pytaku/database/common.py
index 3acae0c..6d51c1f 100644
--- a/src/pytaku/database/common.py
+++ b/src/pytaku/database/common.py
@@ -2,6 +2,13 @@
 
 DBNAME = "db.sqlite3"
 
+_conn = None
+
 
 def get_conn():
-    return apsw.Connection(DBNAME)
+    global _conn
+    if not _conn:
+        _conn = apsw.Connection(DBNAME)
+        # Apparently you need to enable this pragma _per connection_
+        _conn.cursor().execute("PRAGMA foreign_keys = ON;")
+    return _conn
diff --git a/src/pytaku/database/migrations/latest_schema.sql b/src/pytaku/database/migrations/latest_schema.sql
index 3f12ee9..8755f7d 100644
--- a/src/pytaku/database/migrations/latest_schema.sql
+++ b/src/pytaku/database/migrations/latest_schema.sql
@@ -1,12 +1,25 @@
 -- This file is auto-generated by the migration script
 -- for reference purposes only. DO NOT EDIT.
 
-CREATE TABLE user (
-    id integer primary key,
-    username text unique,
-    password text
+CREATE TABLE title (
+    id text,
+    site text,
+    chapters text,
+    alt_names text,
+    descriptions text,
+
+    unique(id, site)
 );
-CREATE TABLE token (
-    user_id integer,
-    content text
+CREATE TABLE chapter (
+    id text,
+    title_id text,
+    num_major integer,
+    num_minor integer,
+    name text,
+    pages text,
+    groups text,
+
+    foreign key (title_id) references title (id),
+    unique(id, title_id),
+    unique(num_major, num_minor, title_id)
 );
diff --git a/src/pytaku/database/migrations/m0001.sql b/src/pytaku/database/migrations/m0001.sql
index d80fe7d..045fa19 100644
--- a/src/pytaku/database/migrations/m0001.sql
+++ b/src/pytaku/database/migrations/m0001.sql
@@ -1,12 +1,23 @@
-PRAGMA journal_mode=WAL;
+create table title (
+    id text,
+    site text,
+    chapters text,
+    alt_names text,
+    descriptions text,
 
-create table user (
-    id integer primary key,
-    username text unique,
-    password text
+    unique(id, site)
 );
 
-create table token (
-    user_id integer,
-    content text
+create table chapter (
+    id text,
+    title_id text,
+    num_major integer,
+    num_minor integer,
+    name text,
+    pages text,
+    groups text,
+
+    foreign key (title_id) references title (id),
+    unique(id, title_id),
+    unique(num_major, num_minor, title_id)
 );
diff --git a/src/pytaku/database/migrator.py b/src/pytaku/database/migrator.py
index 507972f..670ed7d 100644
--- a/src/pytaku/database/migrator.py
+++ b/src/pytaku/database/migrator.py
@@ -19,7 +19,6 @@ def _get_current_version():
     cur = conn.cursor()
     cur.execute("PRAGMA user_version;")
     version = int(cur.fetchone()[0])
-    conn.close()
     return version
 
 
@@ -57,6 +56,11 @@ def _write_db_schema_script(migrations_dir: Path):
 
 
 def migrate(overwrite_latest_schema=True):
+    # If there's no existing db, create one with the correct pragmas
+    if not Path(DBNAME).is_file():
+        conn = get_conn()
+        conn.cursor().execute("PRAGMA journal_mode = WAL;")
+
     with resources.path(migrations, "") as migrations_dir:
         pending_migrations = _get_pending_migrations(migrations_dir)
         if not pending_migrations:
@@ -66,9 +70,8 @@ def migrate(overwrite_latest_schema=True):
         migration_contents = _read_migrations(pending_migrations)
 
         conn = get_conn()
-        cursor = conn.cursor()
-
         with conn:  # apsw provides automatic rollback for free here
+            cursor = conn.cursor()
             for version, sql in migration_contents:
                 print("Migrating version", version, "...")
                 cursor.execute(sql)