actualbudget queries integration tests

12 files changed, 199 insertions, 16 deletions

diff --git a/rust/actualbudget_models/src/actualbudget_schema.rs b/rust/actualbudget_models/src/actualbudget_schema.rs
index a872231..02907c0 100644
--- a/rust/actualbudget_models/src/actualbudget_schema.rs
+++ b/rust/actualbudget_models/src/actualbudget_schema.rs
@@ -13,22 +13,29 @@ diesel::table! {
 }
 
 diesel::table! {
-    transactions (id) {
+    v_transactions (id) {
         id -> Text,
-        #[sql_name = "isParent"]
         is_parent -> Bool,
-        #[sql_name = "isChild"]
         is_child -> Bool,
-        #[sql_name = "acct"]
+        parent_id -> Nullable<Text>,
+        #[sql_name = "account"]
         account_id -> Text,
         #[sql_name = "category"]
-        category_id -> Text,
+        category_id -> Nullable<Text>,
         amount -> Integer,
-        #[sql_name = "description"]
-        payee -> Text,
-        notes -> Text,
+        payee -> Nullable<Text>,
+        notes -> Nullable<Text>,
         date -> Integer,
-        parent_id -> Nullable<Text>,
+        // imported_id -> Text,
+        // error -> Text,
+        // imported_payee -> Text,
+        // starting_balance_flag -> Integer,
+        // transfer_id -> Text,
+        // sort_order -> Real,
+        // cleared -> Integer,
+        // reconciled -> Integer,
+        // tombstone -> Integer,
+        // schedule -> Text,
     }
 }
 
diff --git a/rust/actualbudget_models/src/actualbudget_transaction.rs b/rust/actualbudget_models/src/actualbudget_transaction.rs
index 2b2f67b..04ece16 100644
--- a/rust/actualbudget_models/src/actualbudget_transaction.rs
+++ b/rust/actualbudget_models/src/actualbudget_transaction.rs
@@ -3,16 +3,16 @@ use diesel::prelude::{Identifiable, Queryable, QueryableByName, Selectable};
 use crate::actualbudget_date::ActualbudgetDate;
 
 #[derive(Identifiable, QueryableByName, Queryable, Selectable, Clone)]
-#[diesel(table_name = crate::actualbudget_schema::transactions)]
+#[diesel(table_name = crate::actualbudget_schema::v_transactions)]
 pub struct ActualbudgetTransaction {
     pub id: String,
     pub is_parent: bool,
     pub is_child: bool,
+    pub parent_id: Option<String>,
     pub account_id: String,
-    pub category_id: String,
+    pub category_id: Option<String>,
     pub amount: i32,
-    pub payee: String,
-    pub notes: String,
+    pub payee: Option<String>,
+    pub notes: Option<String>,
     pub date: ActualbudgetDate,
-    pub parent_id: Option<String>,
 }
diff --git a/rust/actualbudget_queries/Cargo.toml b/rust/actualbudget_queries/Cargo.toml
index 6108461..78fd603 100644
--- a/rust/actualbudget_queries/Cargo.toml
+++ b/rust/actualbudget_queries/Cargo.toml
@@ -8,3 +8,6 @@ actualbudget_models = { path = "../actualbudget_models" }
 actualbudget_schema = { path = "../actualbudget_schema" }
 anyhow = { workspace = true }
 diesel = { workspace = true }
+
+[dev-dependencies]
+libsqlite3-sys = { workspace = true, features = ["bundled"] }
diff --git a/rust/actualbudget_queries/README.md b/rust/actualbudget_queries/README.md
new file mode 100644
index 0000000..d16fa30
--- /dev/null
+++ b/rust/actualbudget_queries/README.md
@@ -0,0 +1,71 @@
+# Actualbudget Queries
+
+This is a Rust library defining queries for querying an Actualbudget database
+export.
+
+## Creating the test database
+
+To re-create the test database from scratch:
+
+1. Open an instance of Actualbudget
+2. Start a new file
+3. Add a new local account, called 'My bank account', with an initial balance of
+   1200.00
+4. Add a new local account, called 'My cash account', with an initial balance of
+   45.10
+5. Add a new local account, called 'Category transfers', with an initial balance
+   of nil
+6. Move the Starting Balance transaction for both to 1 Aug 2024
+6. Go to the Budgets tab
+7. Adjust the budgets for the existing categories for August 2024:
+  - Usual Expenses, under which:
+    - Food: 200
+    - General: 100
+    - Bills: 125
+    - Bills (flexible): 25
+  - Investments and Savings, under which:
+    - Savings: 400
+8. Add transactions for Income for 846.46 in the 'My bank account' on 21 Aug,
+   Sept, Oct and Nov 2024, with the 'Note' column set to 'Salary', ticking the
+   'cleared' box
+9. On 10 Sept 2024, add a transaction in 'My bank account' transferring 80.00 to
+   'My cash account' with no Note, and tick the 'cleared' box
+10. Add transactions for 200.00 to 'Tesco' in 'My bank account', categorised as
+    'Food', for 8 Aug, Sept, Oct, and Nov 2024
+11. In the 'Category transfers' account, add a transaction for 23rd Oct 2024,
+    transferring 120.00 from 'Savings' to 'General'
+12.
+12. Export the database by going to Settings > Export data
+13. Download the resulting ZIP archive
+14. Extract `db.sqlite` and `metadata.json`
+15. Move them to
+    `actualbudget_queries/tests/resources/actualbudget_test_db.sqlite` and
+    `actualbudget_queries/tests/resources/actualbudget_test_db_metadata.json`
+    respectively, overwriting the files which are already there
+16. Format the JSON in `actualbudget_test_db_metadata.json` to minimise diffs
+
+## Updating the test database
+
+To make a change to the test database:
+
+1. Make a copy of `actualbudget_test_db_metadata.json` and rename it to
+   `metadata.json`
+2. Make a copy of `actualbudget_test_db.sqlite` and rename it to `db.sqlite`
+3. Compress these files together in a `.zip` archive
+4. Open an instance of Actualbudget
+5. If you have a file open, close it
+6. Choose 'Import file'
+7. Choose to import an 'Actual' file
+8. Choose the ZIP archive you just made
+9. This should open the file
+10. Make your edits
+11. Document your edits in
+    [the database creation guide](#creating-the-test-database)
+12. Export the database by going to Settings > Export data
+13. Download the resulting ZIP archive
+14. Extract the `.sqlite` database file
+15. Move them to
+    `actualbudget_queries/tests/resources/actualbudget_test_db.sqlite` and
+    `actualbudget_queries/tests/resources/actualbudget_test_db_metadata.json`
+    respectively, overwriting the files which are already there
+16. Format the JSON in `actualbudget_test_db_metadata.json` to minimise diffs
diff --git a/rust/actualbudget_queries/src/actualbudget_transactions.rs b/rust/actualbudget_queries/src/actualbudget_transactions.rs
index 44d3730..32eebd8 100644
--- a/rust/actualbudget_queries/src/actualbudget_transactions.rs
+++ b/rust/actualbudget_queries/src/actualbudget_transactions.rs
@@ -1,7 +1,7 @@
 use actualbudget_models::{actualbudget_date::ActualbudgetDate, actualbudget_transaction::ActualbudgetTransaction};
-use actualbudget_schema::actualbudget_schema::transactions::{
+use actualbudget_schema::actualbudget_schema::v_transactions::{
     self as transactions_schema,
-    dsl::transactions as transactions_table,
+    dsl::v_transactions as transactions_table,
 };
 use anyhow::{Context, Result};
 use diesel::{dsl::min, result::Error, QueryDsl, RunQueryDsl, SelectableHelper, SqliteConnection};
diff --git a/rust/actualbudget_queries/tests/actualbudget_accounts.rs b/rust/actualbudget_queries/tests/actualbudget_accounts.rs
new file mode 100644
index 0000000..a761c4f
--- /dev/null
+++ b/rust/actualbudget_queries/tests/actualbudget_accounts.rs
@@ -0,0 +1,32 @@
+mod common;
+
+use actualbudget_queries::actualbudget_accounts::{get_all_actualbudget_accounts, get_category_transfers_account};
+use common::DB_URL;
+use diesel::{Connection, SqliteConnection};
+
+#[test]
+fn given_test_database_when_get_all_accounts_then_return_accounts() {
+    let connection = &mut SqliteConnection::establish(DB_URL).unwrap();
+
+    let accounts = get_all_actualbudget_accounts(connection);
+
+    assert!(accounts.is_ok());
+    let accounts = accounts.unwrap();
+    assert_eq!(3, accounts.len());
+    assert!(accounts.iter().any(|a| a.name == "My bank account"));
+    assert!(accounts.iter().any(|a| a.name == "My cash account"));
+    assert!(accounts.iter().any(|a| a.name == "Category transfers"));
+}
+
+#[test]
+fn given_test_database_when_get_category_transfer_account_then_return_category_transfer_account() {
+    let connection = &mut SqliteConnection::establish(DB_URL).unwrap();
+
+    let account = get_category_transfers_account(connection);
+
+    assert!(account.is_ok());
+    let account = account.unwrap();
+    assert!(account.is_some());
+    let account = account.unwrap();
+    assert!(account.name == "Category transfers");
+}
diff --git a/rust/actualbudget_queries/tests/actualbudget_categories.rs b/rust/actualbudget_queries/tests/actualbudget_categories.rs
new file mode 100644
index 0000000..5cc619f
--- /dev/null
+++ b/rust/actualbudget_queries/tests/actualbudget_categories.rs
@@ -0,0 +1,23 @@
+mod common;
+
+use actualbudget_queries::actualbudget_categories::get_all_actualbudget_categories;
+use common::DB_URL;
+use diesel::{Connection, SqliteConnection};
+
+#[test]
+fn given_test_database_when_get_all_categories_then_return_categories() {
+    let connection = &mut SqliteConnection::establish(DB_URL).unwrap();
+
+    let categories = get_all_actualbudget_categories(connection);
+
+    assert!(categories.is_ok());
+    let categories = categories.unwrap();
+    assert_eq!(7, categories.len());
+    assert!(categories.iter().any(|a| a.name == "Food"));
+    assert!(categories.iter().any(|a| a.name == "General"));
+    assert!(categories.iter().any(|a| a.name == "Bills"));
+    assert!(categories.iter().any(|a| a.name == "Bills (Flexible)"));
+    assert!(categories.iter().any(|a| a.name == "Savings"));
+    assert!(categories.iter().any(|a| a.name == "Starting Balances"));
+    assert!(categories.iter().any(|a| a.name == "Income"));
+}
diff --git a/rust/actualbudget_queries/tests/actualbudget_transactions.rs b/rust/actualbudget_queries/tests/actualbudget_transactions.rs
new file mode 100644
index 0000000..0321d73
--- /dev/null
+++ b/rust/actualbudget_queries/tests/actualbudget_transactions.rs
@@ -0,0 +1,29 @@
+mod common;
+
+use actualbudget_queries::actualbudget_transactions::{get_all_actualbudget_transactions, get_first_actualbudget_transaction_date};
+use common::DB_URL;
+use diesel::{Connection, SqliteConnection};
+
+#[test]
+fn given_test_database_when_get_all_transactions_then_return_transactions() {
+    let connection = &mut SqliteConnection::establish(DB_URL).unwrap();
+
+    let transactions = get_all_actualbudget_transactions(connection);
+
+    assert!(transactions.is_ok());
+    let transactions = transactions.unwrap();
+    assert_eq!(15, transactions.len());
+}
+
+#[test]
+fn given_test_database_when_get_first_transaction_date_then_return_01_aug_2024() {
+    let connection = &mut SqliteConnection::establish(DB_URL).unwrap();
+
+    let date = get_first_actualbudget_transaction_date(connection);
+
+    assert!(date.is_ok());
+    let date = date.unwrap();
+    assert_eq!("01", date.day());
+    assert_eq!("08", date.month());
+    assert_eq!("2024", date.year());
+}
diff --git a/rust/actualbudget_queries/tests/actualbudget_zero_budgets.rs b/rust/actualbudget_queries/tests/actualbudget_zero_budgets.rs
new file mode 100644
index 0000000..c4e79be
--- /dev/null
+++ b/rust/actualbudget_queries/tests/actualbudget_zero_budgets.rs
@@ -0,0 +1,16 @@
+mod common;
+
+use actualbudget_queries::actualbudget_zero_budgets::get_all_actualbudget_zero_budgets;
+use common::DB_URL;
+use diesel::{Connection, SqliteConnection};
+
+#[test]
+fn given_test_database_when_get_all_zero_budgets_then_return_zero_budgets() {
+    let connection = &mut SqliteConnection::establish(DB_URL).unwrap();
+
+    let zero_budgets = get_all_actualbudget_zero_budgets(connection);
+
+    assert!(zero_budgets.is_ok());
+    let zero_budgets = zero_budgets.unwrap();
+    assert_eq!(25, zero_budgets.len());
+}
diff --git a/rust/actualbudget_queries/tests/common.rs b/rust/actualbudget_queries/tests/common.rs
new file mode 100644
index 0000000..ff778a8
--- /dev/null
+++ b/rust/actualbudget_queries/tests/common.rs
@@ -0,0 +1 @@
+pub const DB_URL: &str = "tests/resources/actualbudget_test_db.sqlite";
diff --git a/rust/actualbudget_queries/tests/resources/actualbudget_test_db.sqlite b/rust/actualbudget_queries/tests/resources/actualbudget_test_db.sqlite
new file mode 100644
index 0000000..e22762f
--- /dev/null
+++ b/rust/actualbudget_queries/tests/resources/actualbudget_test_db.sqlite
diff --git a/rust/actualbudget_queries/tests/resources/actualbudget_test_db_metadata.json b/rust/actualbudget_queries/tests/resources/actualbudget_test_db_metadata.json
new file mode 100644
index 0000000..c323ad6
--- /dev/null
+++ b/rust/actualbudget_queries/tests/resources/actualbudget_test_db_metadata.json
@@ -0,0 +1 @@
+{"id":"My-Finances-1-36ec11a","budgetName":"My Finances 1","userId":"fec9ba17-c260-4f55-8d44-676ef0ec5da9","lastScheduleRun":"2024-11-17","resetClock":true}
\ No newline at end of file