240 lines
7.1 KiB
Rust
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)
|
|
}
|
|
}
|