From a30b8e2d2ce14896e2a43304f684eb88876c8e8c Mon Sep 17 00:00:00 2001
From: Frederick Muriuki Muriithi
Date: Thu, 10 Nov 2022 10:20:02 +0300
Subject: Migrations: Add migration for 'resources' table

* gn3/migrations.py: Minor change
* migrations/auth/20221110_01_WtZ1I-create-resources-table.py: new migration
* tests/unit/auth/test_create_table_migrations.py: test new migration
---
 gn3/migrations.py                                  |  2 +-
 .../20221110_01_WtZ1I-create-resources-table.py    | 22 ++++++++++++++++++++++
 tests/unit/auth/test_create_table_migrations.py    | 16 ++++++++++------
 3 files changed, 33 insertions(+), 7 deletions(-)
 create mode 100644 migrations/auth/20221110_01_WtZ1I-create-resources-table.py

diff --git a/gn3/migrations.py b/gn3/migrations.py
index 7f8d694..3451e07 100644
--- a/gn3/migrations.py
+++ b/gn3/migrations.py
@@ -2,8 +2,8 @@
 from pathlib import Path
 from typing import Union
 
-from yoyo.backends import DatabaseBackend
 from yoyo import read_migrations
+from yoyo.backends import DatabaseBackend
 from yoyo.migrations import Migration, MigrationList
 
 class MigrationNotFound(Exception):
diff --git a/migrations/auth/20221110_01_WtZ1I-create-resources-table.py b/migrations/auth/20221110_01_WtZ1I-create-resources-table.py
new file mode 100644
index 0000000..f99d482
--- /dev/null
+++ b/migrations/auth/20221110_01_WtZ1I-create-resources-table.py
@@ -0,0 +1,22 @@
+"""
+Create 'resources' table
+"""
+
+from yoyo import step
+
+__depends__ = {'20221109_01_HbD5F-add-resource-meta-field-to-resource-categories-field'}
+
+steps = [
+    step(
+        """
+        CREATE TABLE IF NOT EXISTS resources(
+            group_id TEXT NOT NULL,
+            resource_id TEXT PRIMARY KEY,
+            resource_name TEXT NOT NULL,
+            resource_category_id TEXT NOT NULL,
+            FOREIGN KEY(group_id) REFERENCES groups(group_id),
+            FOREIGN KEY(resource_category_id) REFERENCES resource_categories(resource_category_id)
+        ) WITHOUT ROWID
+        """,
+        "DROP TABLE IF EXISTS resources")
+]
diff --git a/tests/unit/auth/test_create_table_migrations.py b/tests/unit/auth/test_create_table_migrations.py
index 97719eb..024f04b 100644
--- a/tests/unit/auth/test_create_table_migrations.py
+++ b/tests/unit/auth/test_create_table_migrations.py
@@ -14,7 +14,8 @@ migrations_and_tables = (
     ("20221103_02_sGrIs-create-user-credentials-table.py", "user_credentials"),
     ("20221108_01_CoxYh-create-the-groups-table.py", "groups"),
     ("20221108_02_wxTr9-create-privileges-table.py", "privileges"),
-    ("20221108_03_Pbhb1-create-resource-categories-table.py", "resource_categories"))
+    ("20221108_03_Pbhb1-create-resource-categories-table.py", "resource_categories"),
+    ("20221110_01_WtZ1I-create-resources-table.py", "resources"))
 
 @pytest.mark.unit_test
 @pytest.mark.parametrize("migration_file,the_table", migrations_and_tables)
@@ -31,13 +32,14 @@ def test_create_table(
     apply_migrations(backend, older_migrations)
     with closing(sqlite3.connect(auth_testdb_path)) as conn, closing(conn.cursor()) as cursor:
         cursor.execute("SELECT name FROM sqlite_schema WHERE type='table'")
-        result = cursor.fetchall()
-        assert the_table not in [row[0] for row in cursor.fetchall()]
+        result_before_migration = cursor.fetchall()
         apply_single_migration(backend, get_migration(migration_path))
         cursor.execute("SELECT name FROM sqlite_schema WHERE type='table'")
-        assert the_table in [row[0] for row in cursor.fetchall()]
+        result_after_migration = cursor.fetchall()
 
     rollback_migrations(backend, older_migrations)
+    assert the_table not in [row[0] for row in result_before_migration]
+    assert the_table in [row[0] for row in result_after_migration]
 
 @pytest.mark.unit_test
 @pytest.mark.parametrize("migration_file,the_table", migrations_and_tables)
@@ -55,9 +57,11 @@ def test_rollback_create_table(
     with closing(sqlite3.connect(auth_testdb_path)) as conn, closing(conn.cursor()) as cursor:
         apply_single_migration(backend, get_migration(migration_path))
         cursor.execute("SELECT name FROM sqlite_schema WHERE type='table'")
-        assert the_table in [row[0] for row in cursor.fetchall()]
+        result_after_migration = cursor.fetchall()
         rollback_single_migration(backend, get_migration(migration_path))
         cursor.execute("SELECT name FROM sqlite_schema WHERE type='table'")
-        assert the_table not in [row[0] for row in cursor.fetchall()]
+        result_after_rollback = cursor.fetchall()
 
     rollback_migrations(backend, older_migrations)
+    assert the_table in [row[0] for row in result_after_migration]
+    assert the_table not in [row[0] for row in result_after_rollback]
-- 
cgit v1.2.3