Initialise the Auth(entic|oris)ation packages

Initialise the authentication/authorisation system packages and set up the
initial database migrations to set up the system.

* README.md: Add documentation on migrations
* gn3/auth/__init__.py: init package
* gn3/auth/authentication/__init__.py: init package
* gn3/auth/authorisation/__init__.py: init package
* gn3/migrations.py: provide migration utilities
* migrations/auth/20221103_01_js9ub-initialise-the-auth-entic-oris-ation-database.py:
  new migration
* tests/unit/auth/test_init_database.py: test new migration applies and rolls
  back as expected
* tests/unit/conftest.py: fixtures for unit tests
* yoyo.auth.ini: basic configuration for yoyo-migration for auth system
  migrations

10 files changed, 169 insertions, 0 deletions

diff --git a/README.md b/README.md
index 8ddf757..36084c4 100644
--- a/README.md
+++ b/README.md
@@ -95,6 +95,36 @@ and try again. Also make sure your ~/guix-bioinformatics is up to date.
 
 See also instructions in [.guix.scm](.guix.scm).
 
+## Migrations
+
+**NOTE**: Do not create migration scripts manually. Use the processes indicated below.
+
+### Authentication/Authorisation Migrations
+
+The migration scripts for the authentication and authorisation system are in the *migrations/auth* folder in the root of the repository.
+
+To create an new migration, do:
+
+```bash
+$ yoyo new --config=yoyo.auth.ini -m "<description of the migration>"
+```
+
+That initialises an new migration script under the *migrations/auth* folder and gives it a name derived from the date, the sequence for that day, and the provided description.
+
+e.g.
+
+```bash
+$ yoyo new --config=yoyo.auth.ini -m "testing a new migration"
+Error: could not open editor!
+Created file ./migrations/auth/20221103_02_HBzwk-testing-a-new-migration.py
+```
+
+Now you can open and edit the scripts to provide the appropriate SQL statements to update or rollback your schema.
+
+### Running the Migrations
+
+**TODO**: Document how to run the migrations
+
 ## Running Tests
 
 (assuming you are in a guix container; otherwise use venv!)
diff --git a/gn3/auth/__init__.py b/gn3/auth/__init__.py
new file mode 100644
index 0000000..699d9e2
--- /dev/null
+++ b/gn3/auth/__init__.py
@@ -0,0 +1,3 @@
+"""Top-Level `Auth` module"""
+from . import authorisation
+from . import authentication
diff --git a/gn3/auth/authentication/__init__.py b/gn3/auth/authentication/__init__.py
new file mode 100644
index 0000000..8ad4cfd
--- /dev/null
+++ b/gn3/auth/authentication/__init__.py
@@ -0,0 +1 @@
+"""The authentication module"""
diff --git a/gn3/auth/authorisation/__init__.py b/gn3/auth/authorisation/__init__.py
new file mode 100644
index 0000000..cc370e2
--- /dev/null
+++ b/gn3/auth/authorisation/__init__.py
@@ -0,0 +1 @@
+"""The authorisation module."""
diff --git a/gn3/migrations.py b/gn3/migrations.py
new file mode 100644
index 0000000..7f8d694
--- /dev/null
+++ b/gn3/migrations.py
@@ -0,0 +1,33 @@
+"""Run the migrations in the app, rather than with yoyo CLI."""
+from pathlib import Path
+from typing import Union
+
+from yoyo.backends import DatabaseBackend
+from yoyo import read_migrations
+from yoyo.migrations import Migration, MigrationList
+
+class MigrationNotFound(Exception):
+    """Raised if a migration is not found at the given path."""
+    def __init__(self, migration_path: Path):
+        """Initialise the exception."""
+        super().__init__(f"Could not find migration '{migration_path}'")
+
+def apply_migrations(backend: DatabaseBackend, migrations: MigrationList):
+    "Apply the provided migrations."
+    with backend.lock():
+        backend.apply_migrations(backend.to_apply(migrations))
+
+def rollback_migrations(backend: DatabaseBackend, migrations: MigrationList):
+    "Rollback the provided migrations."
+    with backend.lock():
+        backend.rollback_migrations(backend.to_rollback(migrations))
+
+def get_migration(migration_path: Union[Path, str]) -> Migration:
+    """Retrieve a migration at thi given `migration_path`."""
+    migration_path = Path(migration_path)
+    if migration_path.exists():
+        for migration in read_migrations(str(migration_path.parent)):
+            if Path(migration.path) == migration_path:
+                return migration
+
+    raise MigrationNotFound(migration_path)
diff --git a/migrations/auth/20221103_01_js9ub-initialise-the-auth-entic-oris-ation-database.py b/migrations/auth/20221103_01_js9ub-initialise-the-auth-entic-oris-ation-database.py
new file mode 100644
index 0000000..d511f5d
--- /dev/null
+++ b/migrations/auth/20221103_01_js9ub-initialise-the-auth-entic-oris-ation-database.py
@@ -0,0 +1,19 @@
+"""
+Initialise the auth(entic|oris)ation database.
+"""
+
+from yoyo import step
+
+__depends__ = {} # type: ignore[var-annotated]
+
+steps = [
+    step(
+        """
+        CREATE TABLE IF NOT EXISTS users(
+            user_id TEXT PRIMARY KEY NOT NULL,
+            email TEXT UNIQUE NOT NULL,
+            name TEXT
+        ) WITHOUT ROWID
+        """,
+        "DROP TABLE IF EXISTS users")
+]
diff --git a/mypy.ini b/mypy.ini
index 0880695..89c5f30 100644
--- a/mypy.ini
+++ b/mypy.ini
@@ -48,3 +48,6 @@ ignore_missing_imports = True
 
 [mypy-xapian.*]
 ignore_missing_imports = True
+
+[mypy-yoyo.*]
+ignore_missing_imports = True
diff --git a/tests/unit/auth/test_init_database.py b/tests/unit/auth/test_init_database.py
new file mode 100644
index 0000000..bf1ed1d
--- /dev/null
+++ b/tests/unit/auth/test_init_database.py
@@ -0,0 +1,29 @@
+"""Test the auth database initialisation migration."""
+from contextlib import closing
+
+import pytest
+import sqlite3
+
+from gn3.migrations import get_migration, apply_migrations, rollback_migrations
+from tests.unit.conftest import (
+    apply_single_migration, rollback_single_migration)
+
+migration_path = "migrations/auth/20221103_01_js9ub-initialise-the-auth-entic-oris-ation-database.py"
+
+@pytest.mark.unit_test
+def test_initialise_the_database(auth_testdb):
+    with closing(sqlite3.connect(auth_testdb)) as conn, closing(conn.cursor()) as cursor:
+        cursor.execute("SELECT name FROM sqlite_schema WHERE type='table'")
+        result = cursor.fetchall()
+        assert "users" not in [row[0] for row in cursor.fetchall()]
+        apply_single_migration(auth_testdb, get_migration(migration_path))
+        cursor.execute("SELECT name FROM sqlite_schema WHERE type='table'")
+        assert "users" in [row[0] for row in cursor.fetchall()]
+
+@pytest.mark.unit_test
+def test_rollback_initialise_the_database(auth_testdb):
+    with closing(sqlite3.connect(auth_testdb)) as conn, closing(conn.cursor()) as cursor:
+        apply_single_migration(auth_testdb, get_migration(migration_path))
+        rollback_single_migration(auth_testdb, get_migration(migration_path))
+        cursor.execute("SELECT name FROM sqlite_schema WHERE type='table'")
+        assert "users" not in [row[0] for row in cursor.fetchall()]
diff --git a/tests/unit/conftest.py b/tests/unit/conftest.py
new file mode 100644
index 0000000..bc974d9
--- /dev/null
+++ b/tests/unit/conftest.py
@@ -0,0 +1,44 @@
+"""Fixtures for unit tests."""
+from typing import Union
+from pathlib import Path
+from datetime import datetime
+from tempfile import TemporaryDirectory
+
+import pytest
+from yoyo import get_backend
+from yoyo.migrations import Migration, MigrationList
+
+from gn3.app import create_app
+from gn3.migrations import apply_migrations, rollback_migrations
+
+@pytest.fixture(scope="session")
+def client():
+    """Create a test client fixture for tests"""
+    # Do some setup
+    with TemporaryDirectory() as testdir:
+        testdb = Path(testdir).joinpath(
+            f'testdb_{datetime.now().strftime("%Y%m%dT%H%M%S")}')
+        app = create_app({"AUTH_DB": testdb})
+        app.config.update({"TESTING": True})
+        app.testing = True
+        yield app.test_client()
+        # Clean up after ourselves
+        testdb.unlink(missing_ok=True)
+
+@pytest.fixture()
+def test_app_config(client): # pylint: disable=redefined-outer-name
+    """Return the test application's configuration object"""
+    return client.application.config
+
+@pytest.fixture()
+def auth_testdb(test_app_config): # pylint: disable=redefined-outer-name
+    """Get the test application's auth database file"""
+    return test_app_config["AUTH_DB"]
+
+def apply_single_migration(db_uri: Union[Path, str], migration: Migration):
+    """Utility to apply a single migration"""
+    apply_migrations(get_backend(f"sqlite:///{db_uri}"), MigrationList([migration]))
+
+def rollback_single_migration(db_uri: Union[Path, str], migration: Migration):
+    """Utility to rollback a single migration"""
+    rollback_migrations(get_backend(f"sqlite:///{db_uri}"), MigrationList([migration]))
diff --git a/yoyo.auth.ini b/yoyo.auth.ini
new file mode 100644
index 0000000..097c17b
--- /dev/null
+++ b/yoyo.auth.ini
@@ -0,0 +1,6 @@
+[DEFAULT]
+sources = ./migrations/auth/
+migration_table = _yoyo_migration
+batch_mode = off
+verbosity = 0
+