Improves backend schema

6 files changed, 20 insertions, 14 deletions

diff --git a/backend/migrations/2024-08-31-084439_initial_setup/down.sql b/backend/migrations/2024-08-31-084439_initial_setup/down.sql
index 5e68c99..8cf4b1e 100644
--- a/backend/migrations/2024-08-31-084439_initial_setup/down.sql
+++ b/backend/migrations/2024-08-31-084439_initial_setup/down.sql
@@ -1,6 +1,6 @@
 DROP TABLE accounts;
 DROP TABLE budget_updates;
 DROP TABLE categories;
-DROP TABLE category_transactions;
+DROP TABLE transaction_categorisations;
 DROP TABLE category_transfers;
 DROP TABLE transactions;
diff --git a/backend/migrations/2024-08-31-084439_initial_setup/up.sql b/backend/migrations/2024-08-31-084439_initial_setup/up.sql
index f2461a5..a43f6dd 100644
--- a/backend/migrations/2024-08-31-084439_initial_setup/up.sql
+++ b/backend/migrations/2024-08-31-084439_initial_setup/up.sql
@@ -26,11 +26,16 @@ CREATE TABLE categories(
     initial_period INTEGER NOT NULL
 );
 
-CREATE TABLE category_transactions(
+CREATE TABLE transaction_categorisations(
     id INTEGER NOT NULL PRIMARY KEY,
     description TEXT NOT NULL,
     quantity INTEGER NOT NULL,
+    transaction_id INTEGER NOT NULL,
     category_id INTEGER NOT NULL,
+    FOREIGN KEY (transaction_id)
+        REFERENCES transactions (id)
+        ON UPDATE CASCADE
+        ON DELETE RESTRICT,
     FOREIGN KEY (category_id)
         REFERENCES categories (id)
         ON UPDATE CASCADE
diff --git a/backend/src/models/mod.rs b/backend/src/models/mod.rs
index f70212a..68f5209 100644
--- a/backend/src/models/mod.rs
+++ b/backend/src/models/mod.rs
@@ -1,6 +1,6 @@
 pub mod account;
 pub mod budget_update;
 pub mod category;
-pub mod category_transaction;
 pub mod category_transfer;
 pub mod transaction;
+pub mod transaction_categorisation;
diff --git a/backend/src/models/category_transaction.rs b/backend/src/models/transaction_categorisation.rs
index 2163860..d2867f1 100644
--- a/backend/src/models/category_transaction.rs
+++ b/backend/src/models/transaction_categorisation.rs
@@ -1,10 +1,11 @@
 use diesel::prelude::*;
 
 #[derive(Queryable, Identifiable, Selectable, Debug, PartialEq)]
-#[diesel(table_name = crate::schema::category_transactions)]
-pub struct CategoryTransaction {
+#[diesel(table_name = crate::schema::transaction_categorisations)]
+pub struct TransactionCategorisations {
     pub id: i32,
     pub description: String,
     pub quantity: i32,
+    pub transaction_id: i32,
     pub category_id: i32,
 }
diff --git a/backend/src/routes/account.rs b/backend/src/routes/account.rs
index e7f000c..418ec96 100644
--- a/backend/src/routes/account.rs
+++ b/backend/src/routes/account.rs
@@ -1,8 +1,6 @@
 use diesel::prelude::*;
 use diesel::result::DatabaseErrorKind;
-use rocket::error::ErrorKind;
 use rocket::serde::json::Json;
-use rocket::tokio::sync::oneshot::error::RecvError;
 use rocket::{get, post, Responder};
 
 use crate::databases::UserDataDb;
diff --git a/backend/src/schema.rs b/backend/src/schema.rs
index d98a629..942e913 100644
--- a/backend/src/schema.rs
+++ b/backend/src/schema.rs
@@ -29,21 +29,22 @@ diesel::table! {
 }
 
 diesel::table! {
-    category_transactions (id) {
+    category_transfers (id) {
         id -> Integer,
         description -> Text,
         quantity -> Integer,
-        category_id -> Integer,
+        from_category_id -> Integer,
+        to_category_id -> Integer,
     }
 }
 
 diesel::table! {
-    category_transfers (id) {
+    transaction_categorisations (id) {
         id -> Integer,
         description -> Text,
         quantity -> Integer,
-        from_category_id -> Integer,
-        to_category_id -> Integer,
+        transaction_id -> Integer,
+        category_id -> Integer,
     }
 }
 
@@ -58,14 +59,15 @@ diesel::table! {
 }
 
 diesel::joinable!(budget_updates -> categories (category_id));
-diesel::joinable!(category_transactions -> categories (category_id));
+diesel::joinable!(transaction_categorisations -> categories (category_id));
+diesel::joinable!(transaction_categorisations -> transactions (transaction_id));
 diesel::joinable!(transactions -> accounts (account_id));
 
 diesel::allow_tables_to_appear_in_same_query!(
     accounts,
     budget_updates,
     categories,
-    category_transactions,
     category_transfers,
+    transaction_categorisations,
     transactions,
 );