From 9c9268b60b10b7322aea84704bc1f5f8fbb0bbb8 Mon Sep 17 00:00:00 2001 From: Ashelyn Rose Date: Sat, 19 Oct 2024 01:17:02 -0600 Subject: Migrations also refresh views and sprocs --- Cargo.lock | 118 ++++++++++++++++++++++++++++++++++++++++++++++++++++ Cargo.toml | 3 +- src/jobs/startup.rs | 92 +++++++++++++++++++++++++++++++++++++++- src/main.rs | 3 +- 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 @@ -2,6 +2,21 @@ # It is not intended for manual editing. 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" @@ -41,6 +56,21 @@ version = "1.4.0" 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" @@ -357,6 +387,12 @@ dependencies = [ "wasi", ] +[[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" @@ -388,6 +424,12 @@ version = "0.5.0" 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" @@ -548,6 +590,27 @@ version = "0.2.1" 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" @@ -605,6 +668,15 @@ dependencies = [ "libm", ] +[[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" @@ -667,6 +739,7 @@ version = "0.1.0" dependencies = [ "joinrs", "sqlx", + "tokio", ] [[package]] @@ -794,6 +867,12 @@ dependencies = [ "zeroize", ] +[[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" @@ -919,6 +998,16 @@ dependencies = [ "serde", ] +[[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" @@ -994,6 +1083,8 @@ dependencies = [ "smallvec", "sqlformat", "thiserror", + "tokio", + "tokio-stream", "tracing", "url", ] @@ -1033,6 +1124,7 @@ dependencies = [ "sqlx-sqlite", "syn", "tempfile", + "tokio", "url", ] @@ -1215,6 +1307,32 @@ version = "0.1.1" 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" 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 = 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 = 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