Migrations also refresh views and sprocs

4 files changed, 212 insertions, 4 deletions

diff --git a/Cargo.lock b/Cargo.lock
index 2f05b5f..c2c4344 100644
--- a/Cargo.lock
+++ b/Cargo.lock
@@ -3,6 +3,21 @@
 version = 3
 
 [[package]]
+name = "addr2line"
+version = "0.24.2"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "dfbe277e56a376000877090da837660b4427aad530e3028d44e0bffe4f89a1c1"
+dependencies = [
+ "gimli",
+]
+
+[[package]]
+name = "adler2"
+version = "2.0.0"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "512761e0bb2578dd7380c6baaa0f4ce03e84f95e960231d1dec8bf4d7d6e2627"
+
+[[package]]
 name = "ahash"
 version = "0.8.11"
 source = "registry+https://github.com/rust-lang/crates.io-index"
@@ -42,6 +57,21 @@ source = "registry+https://github.com/rust-lang/crates.io-index"
 checksum = "ace50bade8e6234aa140d9a2f552bbee1db4d353f69b8217bc503490fc1a9f26"
 
 [[package]]
+name = "backtrace"
+version = "0.3.74"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "8d82cb332cdfaed17ae235a638438ac4d4839913cc2af585c3c6746e8f8bee1a"
+dependencies = [
+ "addr2line",
+ "cfg-if",
+ "libc",
+ "miniz_oxide",
+ "object",
+ "rustc-demangle",
+ "windows-targets 0.52.6",
+]
+
+[[package]]
 name = "base64"
 version = "0.22.1"
 source = "registry+https://github.com/rust-lang/crates.io-index"
@@ -358,6 +388,12 @@ dependencies = [
 ]
 
 [[package]]
+name = "gimli"
+version = "0.31.1"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "07e28edb80900c19c28f1072f2e8aeca7fa06b23cd4169cefe1af5aa3260783f"
+
+[[package]]
 name = "hashbrown"
 version = "0.14.5"
 source = "registry+https://github.com/rust-lang/crates.io-index"
@@ -389,6 +425,12 @@ source = "registry+https://github.com/rust-lang/crates.io-index"
 checksum = "2304e00983f87ffb38b55b444b5e3b60a884b5d30c0fca7d82fe33449bbe55ea"
 
 [[package]]
+name = "hermit-abi"
+version = "0.3.9"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "d231dfb89cfffdbc30e7fc41579ed6066ad03abda9e567ccafae602b97ec5024"
+
+[[package]]
 name = "hex"
 version = "0.4.3"
 source = "registry+https://github.com/rust-lang/crates.io-index"
@@ -549,6 +591,27 @@ source = "registry+https://github.com/rust-lang/crates.io-index"
 checksum = "68354c5c6bd36d73ff3feceb05efa59b6acb7626617f4962be322a825e61f79a"
 
 [[package]]
+name = "miniz_oxide"
+version = "0.8.0"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "e2d80299ef12ff69b16a84bb182e3b9df68b5a91574d3d4fa6e41b65deec4df1"
+dependencies = [
+ "adler2",
+]
+
+[[package]]
+name = "mio"
+version = "1.0.2"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "80e04d1dcff3aae0704555fe5fee3bcfaf3d1fdf8a7e521d5b9d2b42acb52cec"
+dependencies = [
+ "hermit-abi",
+ "libc",
+ "wasi",
+ "windows-sys 0.52.0",
+]
+
+[[package]]
 name = "nom"
 version = "7.1.3"
 source = "registry+https://github.com/rust-lang/crates.io-index"
@@ -606,6 +669,15 @@ dependencies = [
 ]
 
 [[package]]
+name = "object"
+version = "0.36.5"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "aedf0a2d09c573ed1d8d85b30c119153926a2b36dce0ab28322c09a117a4683e"
+dependencies = [
+ "memchr",
+]
+
+[[package]]
 name = "once_cell"
 version = "1.20.2"
 source = "registry+https://github.com/rust-lang/crates.io-index"
@@ -667,6 +739,7 @@ version = "0.1.0"
 dependencies = [
  "joinrs",
  "sqlx",
+ "tokio",
 ]
 
 [[package]]
@@ -795,6 +868,12 @@ dependencies = [
 ]
 
 [[package]]
+name = "rustc-demangle"
+version = "0.1.24"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "719b953e2095829ee67db738b3bfa9fa368c94900df327b3f07fe6e794d2fe1f"
+
+[[package]]
 name = "rustix"
 version = "0.38.37"
 source = "registry+https://github.com/rust-lang/crates.io-index"
@@ -920,6 +999,16 @@ dependencies = [
 ]
 
 [[package]]
+name = "socket2"
+version = "0.5.7"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "ce305eb0b4296696835b71df73eb912e0f1ffd2556a501fcede6e0c50349191c"
+dependencies = [
+ "libc",
+ "windows-sys 0.52.0",
+]
+
+[[package]]
 name = "spin"
 version = "0.9.8"
 source = "registry+https://github.com/rust-lang/crates.io-index"
@@ -994,6 +1083,8 @@ dependencies = [
  "smallvec",
  "sqlformat",
  "thiserror",
+ "tokio",
+ "tokio-stream",
  "tracing",
  "url",
 ]
@@ -1033,6 +1124,7 @@ dependencies = [
  "sqlx-sqlite",
  "syn",
  "tempfile",
+ "tokio",
  "url",
 ]
 
@@ -1216,6 +1308,32 @@ source = "registry+https://github.com/rust-lang/crates.io-index"
 checksum = "1f3ccbac311fea05f86f61904b462b55fb3df8837a366dfc601a0161d0532f20"
 
 [[package]]
+name = "tokio"
+version = "1.40.0"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "e2b070231665d27ad9ec9b8df639893f46727666c6767db40317fbe920a5d998"
+dependencies = [
+ "backtrace",
+ "bytes",
+ "libc",
+ "mio",
+ "pin-project-lite",
+ "socket2",
+ "windows-sys 0.52.0",
+]
+
+[[package]]
+name = "tokio-stream"
+version = "0.1.15"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "267ac89e0bec6e691e5813911606935d77c476ff49024f98abcea3e7b15e37af"
+dependencies = [
+ "futures-core",
+ "pin-project-lite",
+ "tokio",
+]
+
+[[package]]
 name = "tracing"
 version = "0.1.40"
 source = "registry+https://github.com/rust-lang/crates.io-index"
diff --git a/Cargo.toml b/Cargo.toml
index 0878923..2529b4a 100644
--- a/Cargo.toml
+++ b/Cargo.toml
@@ -5,4 +5,5 @@ edition = "2021"
 
 [dependencies]
 joinrs = { git = "https://git.tempest.dev/ashe/joinrs", version = "0.0.1" }
-sqlx = "0.8.2"
+sqlx = { version = "0.8.2", features = ["postgres", "runtime-tokio"] }
+tokio = { version = "1.40.0", features = ["fs", "io-std", "rt"] }
diff --git a/src/jobs/startup.rs b/src/jobs/startup.rs
index 36046f4..05eaa94 100644
--- a/src/jobs/startup.rs
+++ b/src/jobs/startup.rs
@@ -1,3 +1,93 @@
-pub fn migrate_db() {
+use tokio::runtime;
+use std::{fs, path::Path};
 
+use sqlx::{migrate::Migrator, Connection, PgConnection, Row};
+
+pub fn run_startup_jobs() {
+    let runtime = runtime::Builder::new_current_thread()
+        .enable_all()
+        .build().unwrap();
+
+    runtime.block_on(async {
+        migrate_db().await
+    })
+}
+
+async fn migrate_db() {
+    println!(" - Migrating database");
+
+    let migrator = Migrator::new(Path::new("./src/db/migrations/"))
+        .await.expect("Could not initialize migrations");
+
+    let mut database_connection = PgConnection::connect("postgres://localhost/photoxide").await.expect("Could not connect to DB");
+    let mut transaction = database_connection.begin().await.expect("Could not start transaction");
+
+    let procs : Vec<String> = sqlx::query("
+        SELECT proname || '(' || oidvectortypes(proargtypes) || ')' as func_name
+            FROM pg_proc INNER JOIN pg_namespace ns ON (pg_proc.pronamespace = ns.oid)
+            WHERE ns.nspname = 'phtx'
+        order by proname;
+    ").fetch_all(&mut *transaction)
+        .await.unwrap()
+        .into_iter().map(|row| {
+            row.try_get_unchecked("func_name").unwrap()
+        }).collect();
+
+    let views : Vec<String> = sqlx::query("select table_name from information_schema.views where table_schema = 'phtx'")
+        .fetch_all(&mut *transaction)
+        .await.unwrap()
+        .into_iter().map(|row| {
+            row.try_get_unchecked("table_name").unwrap()
+        }).collect();
+
+    println!("  - Clearing stored procedures");
+    for proc in procs {
+        sqlx::query(format!("drop function phtx.{proc}").as_str())
+            .execute(&mut *transaction)
+            .await
+            .unwrap();
+    }
+
+    println!("  - Clearing views");
+    for view in views {
+        sqlx::query(format!("drop view phtx.{view}").as_str())
+            .execute(&mut *transaction)
+            .await
+            .unwrap();
+    }
+
+    let num_migrations = migrator.iter().len();
+    let suffix = if num_migrations == 1 {""} else {"s"};
+    println!("  - Running {num_migrations} table migration{suffix}");
+    migrator.run(&mut transaction).await.unwrap();
+    
+    let view_scripts : Vec<(String, String)> = fs::read_dir(Path::new("./src/db/views/")).unwrap()
+        .into_iter().map(|dir_entry| {
+            (dir_entry.as_ref().unwrap().file_name().to_str().unwrap().to_string(), fs::read_to_string(dir_entry.unwrap().path()).unwrap())
+        }).collect();
+
+    let proc_scripts : Vec<(String, String)> = fs::read_dir(Path::new("./src/db/functions/")).unwrap()
+        .into_iter().map(|dir_entry| {
+            (dir_entry.as_ref().unwrap().file_name().to_str().unwrap().to_string(), fs::read_to_string(dir_entry.unwrap().path()).unwrap())
+        }).collect();
+
+    println!("  - Re-initializing views");
+    for (path, script) in view_scripts {
+        println!("   - Running ./src/db/views/{path}");
+        sqlx::raw_sql(script.as_str())
+            .execute(&mut *transaction)
+            .await.unwrap();
+    }
+
+    println!("  - Re-initializing stored procedures");
+    for (path, script) in proc_scripts {
+        println!("   - Running ./src/db/functions/{path}");
+        sqlx::raw_sql(script.as_str())
+            .execute(&mut *transaction)
+            .await.unwrap();
+    }
+
+    println!("  - Committing transaction");
+    transaction.commit().await.expect("Could not commit migrate transaction");
 }
+
diff --git a/src/main.rs b/src/main.rs
index 27313c6..ba41b2d 100644
--- a/src/main.rs
+++ b/src/main.rs
@@ -1,7 +1,6 @@
 mod jobs;
 
-
 fn main() {
     println!("{}_{} starting up", env!("CARGO_PKG_NAME"), env!("CARGO_PKG_VERSION"));
-    jobs::startup::migrate_db();
+    jobs::startup::run_startup_jobs();
 }