pgui_api/
routes.rs

1use crate::connect;
2use actix_web::{get, post, web, HttpResponse, Responder};
3use base64::{engine::general_purpose, Engine as _};
4use regex::Regex;
5use sqlx::postgres::PgRow;
6use sqlx::{Error, Pool, Postgres, Row};
7
8#[get("/")]
9pub async fn running() -> impl Responder {
10    HttpResponse::Ok().body("API is up and running!")
11}
12
13#[post("/connection")]
14pub async fn connection(conn_str: String, conn: web::Data<Pool<Postgres>>) -> impl Responder {
15    // Validate connection string format
16    // TODO(ianstanton) regex needs to be tweaked a bit (password)
17    let re = Regex::new(r"(postgres|postgresql)://[a-zA-Z][0-9a-zA-Z_-]*:[a-zA-Z][0-9a-zA-Z_-]*@[a-zA-Z][0-9a-zA-Z_-]*:[0-9]*/[a-zA-Z][0-9a-zA-Z_-]*$").unwrap();
18    if !re.is_match(&conn_str) {
19        println!("Connection string is improperly formatted");
20        HttpResponse::BadRequest().body("")
21    } else {
22        // Connect to postgres
23        let mut tx = conn.begin().await.unwrap();
24        // Ensure connection string table exists
25        sqlx::query("CREATE TABLE IF NOT EXISTS conn_str (id int, conn text);")
26            .execute(&mut tx)
27            .await
28            .unwrap();
29        // base64 encode connection string
30        // TODO(ianstanton) Properly encrypt connection string
31        let conn_b64 = general_purpose::STANDARD.encode(conn_str);
32        // Create identifier for conn string
33        // Write connection info to table
34        // TODO(ianstanton) Accept user provided ID and use in INSERT
35        sqlx::query(format!("INSERT INTO conn_str VALUES (1, '{}');", conn_b64).as_str())
36            .execute(&mut tx)
37            .await
38            .unwrap();
39        tx.commit().await.unwrap();
40        HttpResponse::Ok().body("Connection string saved")
41    }
42}
43
44#[get("/get-queries")]
45pub async fn get_queries(conn: web::Data<Pool<Postgres>>) -> impl Responder {
46    let mut queries: Vec<(f64, f64, String)> = Vec::new();
47    // Connect to backend postgresql server and query for connection string
48    let mut tx = conn.begin().await.unwrap();
49    // TODO(ianstanton) Query conn_str for a connection string with a given ID
50    let row: Result<PgRow, Error> = sqlx::query("SELECT * FROM conn_str;")
51        .fetch_one(&mut tx)
52        .await;
53    tx.commit().await.unwrap();
54    // Connect to postgres instance
55    let conn_str_b64: String = row.unwrap().get(1);
56    // Decode connection string
57    let conn_str = b64_decode(&conn_str_b64);
58    let new_conn = connect(&conn_str).await.unwrap();
59    tx = new_conn.begin().await.unwrap();
60    let query = "SELECT (total_exec_time / 1000 / 60) as total, (total_exec_time/calls) as avg, query FROM pg_stat_statements ORDER BY 1 DESC LIMIT 10;";
61    let rows: Result<Vec<PgRow>, Error> = sqlx::query(query).fetch_all(&mut tx).await;
62    for row in rows.unwrap().iter() {
63        queries.push((row.get(0), row.get(1), row.get(2)));
64    }
65    // Return results in response
66    HttpResponse::Ok().body(format!("Queries... {:?}", queries))
67}
68
69fn b64_decode(b64_encoded: &str) -> String {
70    let bytes = general_purpose::STANDARD.decode(b64_encoded).unwrap();
71    std::str::from_utf8(&bytes).unwrap().to_owned()
72}
pgui_api/routes.rs

pgui_api/
routes.rs
