From 9c9268b60b10b7322aea84704bc1f5f8fbb0bbb8 Mon Sep 17 00:00:00 2001
From: Ashelyn Rose <git@ashen.earth>
Date: Sat, 19 Oct 2024 01:17:02 -0600
Subject: Migrations also refresh views and sprocs

---
 src/jobs/startup.rs | 92 ++++++++++++++++++++++++++++++++++++++++++++++++++++-
 src/main.rs         |  3 +-
 2 files changed, 92 insertions(+), 3 deletions(-)

(limited to 'src')

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();
 }
-- 
cgit 1.4.1