Files

240 lines
7.1 KiB
Rust

use sqlx::{sqlite::SqlitePool, Pool, Row};
use std::error::Error;
use serde::{Deserialize, Serialize};
use indoc::indoc;
pub struct Connection {
pool: Pool<sqlx::Sqlite>,
}
pub async fn connect() -> Result<Connection, Box<dyn Error>> {
let database_url = "sqlite::memory:"; // Use an in-memory SQLite database for testing
let pool = SqlitePool::connect(database_url).await?;
Ok(Connection { pool })
}
pub async fn init_db(conn: &Connection) -> Result<(), Box<dyn Error>> {
sqlx::query(indoc! {"
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT NOT NULL UNIQUE,
jwt_token TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS elections (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
username TEXT NOT NULL,
namespace TEXT NOT NULL,
description TEXT,
start_date TEXT NOT NULL,
end_date TEXT NOT NULL,
FOREIGN KEY (username) REFERENCES users(username)
);
CREATE TABLE IF NOT EXISTS authorizations (
id INTEGER PRIMARY KEY AUTOINCREMENT,
from_date TEXT NOT NULL,
to_date TEXT NOT NULL,
from_user TEXT NOT NULL,
to_user TEXT NOT NULL,
namespace TEXT NOT NULL,
FOREIGN KEY (from_user) REFERENCES users(username),
FOREIGN KEY (to_user) REFERENCES users(username)
);
CREATE TABLE IF NOT EXISTS votes (
id INTEGER PRIMARY KEY AUTOINCREMENT,
value INTEGER NOT NULL,
option_id INTEGER NOT NULL,
election_id INTEGER NOT NULL,
option_name TEXT NOT NULL,
user TEXT NOT NULL,
namespace TEXT NOT NULL,
date TEXT NOT NULL,
FOREIGN KEY (election_id) REFERENCES elections(id),
FOREIGN KEY (user) REFERENCES users(username)
);
"})
.execute(&conn.pool)
.await?;
Ok(())
}
// Users
#[derive(Serialize, Deserialize)]
pub struct User {
pub id: i64,
pub username: String,
pub jwt_token: String,
}
pub async fn insert_user(conn: &Connection, username: &str, jwt_token: &str) -> Result<(), Box<dyn Error>> {
sqlx::query("INSERT INTO users (username, jwt_token) VALUES (?, ?)")
.bind(username)
.bind(jwt_token)
.execute(&conn.pool)
.await?;
Ok(())
}
pub async fn get_user(conn: &Connection, username: &str) -> Result<Option<User>, Box<dyn Error>> {
let row = sqlx::query("SELECT id, username, jwt_token FROM users WHERE username = ?")
.bind(username)
.fetch_optional(&conn.pool)
.await?;
if let Some(row) = row {
Ok(Some(User {
id: row.get("id"),
username: row.get("username"),
jwt_token: row.get("jwt_token"),
}))
} else {
Ok(None)
}
}
// Elections
#[derive(Serialize, Deserialize)]
pub struct Election {
pub id: i64,
pub name: String,
pub username: String,
pub namespace: String,
pub description: String,
pub start_date: String,
pub end_date: String,
}
pub async fn insert_election(conn: &Connection, election: &Election) -> Result<(), Box<dyn Error>> {
sqlx::query(indoc! {"
INSERT INTO elections (name, username, namespace, description, start_date, end_date)
VALUES (?, ?, ?, ?, ?, ?)
"})
.bind(&election.name)
.bind(&election.username)
.bind(&election.namespace)
.bind(&election.description)
.bind(&election.start_date)
.bind(&election.end_date)
.execute(&conn.pool)
.await?;
Ok(())
}
pub async fn get_election(conn: &Connection, id: i64) -> Result<Option<Election>, Box<dyn Error>> {
let row = sqlx::query("SELECT id, name, username, namespace, description, start_date, end_date FROM elections WHERE id = ?")
.bind(id)
.fetch_optional(&conn.pool)
.await?;
if let Some(row) = row {
Ok(Some(Election {
id: row.get("id"),
name: row.get("name"),
username: row.get("username"),
namespace: row.get("namespace"),
description: row.get("description"),
start_date: row.get("start_date"),
end_date: row.get("end_date"),
}))
} else {
Ok(None)
}
}
// Authorizations
#[derive(Serialize, Deserialize)]
pub struct Authorization {
pub id: i64,
pub from_date: String,
pub to_date: String,
pub from_user: String,
pub to_user: String,
pub namespace: String,
}
pub async fn insert_authorization(conn: &Connection, authorization: &Authorization) -> Result<(), Box<dyn Error>> {
sqlx::query(indoc! {"
INSERT INTO authorizations (from_date, to_date, from_user, to_user, namespace)
VALUES (?, ?, ?, ?, ?)
"})
.bind(&authorization.from_date)
.bind(&authorization.to_date)
.bind(&authorization.from_user)
.bind(&authorization.to_user)
.bind(&authorization.namespace)
.execute(&conn.pool)
.await?;
Ok(())
}
pub async fn get_authorization(conn: &Connection, id: i64) -> Result<Option<Authorization>, Box<dyn Error>> {
let row = sqlx::query("SELECT id, from_date, to_date, from_user, to_user, namespace FROM authorizations WHERE id = ?")
.bind(id)
.fetch_optional(&conn.pool)
.await?;
if let Some(row) = row {
Ok(Some(Authorization {
id: row.get("id"),
from_date: row.get("from_date"),
to_date: row.get("to_date"),
from_user: row.get("from_user"),
to_user: row.get("to_user"),
namespace: row.get("namespace"),
}))
} else {
Ok(None)
}
}
// Votes
#[derive(Serialize, Deserialize)]
pub struct Vote {
pub id: i64,
pub value: i32,
pub option_id: i64,
pub election_id: i64,
pub option_name: String,
pub user: String,
pub namespace: String,
pub date: String,
}
pub async fn insert_vote(conn: &Connection, vote: &Vote) -> Result<(), Box<dyn Error>> {
sqlx::query(indoc! {"
INSERT INTO votes (value, option_id, election_id, option_name, user, namespace, date)
VALUES (?, ?, ?, ?, ?, ?, ?)
"})
.bind(vote.value)
.bind(vote.option_id)
.bind(vote.election_id)
.bind(&vote.option_name)
.bind(&vote.user)
.bind(&vote.namespace)
.bind(&vote.date)
.execute(&conn.pool)
.await?;
Ok(())
}
pub async fn get_vote(conn: &Connection, id: i64) -> Result<Option<Vote>, Box<dyn Error>> {
let row = sqlx::query("SELECT id, value, option_id, election_id, option_name, user, namespace, date FROM votes WHERE id = ?")
.bind(id)
.fetch_optional(&conn.pool)
.await?;
if let Some(row) = row {
Ok(Some(Vote {
id: row.get("id"),
value: row.get("value"),
option_id: row.get("option_id"),
election_id: row.get("election_id"),
option_name: row.get("option_name"),
user: row.get("user"),
namespace: row.get("namespace"),
date: row.get("date"),
}))
} else {
Ok(None)
}
}