sqlite database add delete update show in rust

Pub Date: 2023-07-20

[dependencies]
rusqlite = "0.29.0"

sqlite.rs

#![allow(unused)]
fn main() {
use rusqlite::{params, Connection, Result};

#[derive(Debug)]
pub struct SqliteDB {
    pub connection: Connection,
}
#[allow(warnings)]
impl SqliteDB {
    pub fn connect() -> Result<Box<Self>, Box<dyn std::error::Error>> {
        // Establish a connection to the database file
        let conn = Connection::open("users.db")?;

        // Execute an SQL statement to create a table
        conn.execute(
            "CREATE TABLE IF NOT EXISTS users (
                id INTEGER PRIMARY KEY,
                name TEXT NOT NULL,
                password TEXT NOT NULL,
                filedata BLOB
            )",
            params![],
        )?;

        Ok(Box::from(Self { connection: conn }))
    }

    pub fn add_user(&self, name: &str, password: &str) -> Result<(), Box<dyn std::error::Error>> {
        let conn = &self.connection;

        // Check if the user already exists
        let query = "SELECT COUNT(*) FROM users WHERE name = ?1";
        let mut stmt = conn.prepare(query)?;
        let count: i64 = stmt.query_row(params![name], |row| row.get(0))?;

        // If the user does not exist, insert the user data
        if count == 0 {
            let insert_query = "INSERT INTO users (name, password) VALUES (?1, ?2)";
            conn.execute(insert_query, params![name, password])?;
        }

        Ok(())
    }

    pub fn get_all_users(&self) -> Result<Vec<(i32, String, String)>> {
        let mut stmt = self
            .connection
            .prepare("SELECT id, name, password FROM users")?;
        let user_rows =
            stmt.query_map(params![], |row| Ok((row.get(0)?, row.get(1)?, row.get(2)?)))?;

        let mut users = Vec::new();
        for user_row in user_rows {
            users.push(user_row?);
        }
        Ok(users)
    }
    pub fn get_user_filedata(&self,  name: &str) -> Result<Vec<u8>> {
        let mut stmt = self.connection.prepare("SELECT filedata FROM users WHERE name = ?")?;
        let mut rows = stmt.query(params![name])?;
    
        if let Some(row) = rows.next()? {
            let filedata: Vec<u8> = row.get(0)?;
            Ok(filedata)
        } else {
            Err(rusqlite::Error::QueryReturnedNoRows)
        }
    }
    pub fn change_user_name(&self, name: &str, password: &str, new_name: &str) -> Result<usize> {
        let update_query = "UPDATE users SET name = ?1 WHERE name = ?2 AND password = ?3";
        let result = self
            .connection
            .execute(update_query, params![new_name, name, password])?;
        Ok(result)
    }
    pub fn change_user_password(
        &self,
        name: &str,
        password: &str,
        new_password: &str,
    ) -> Result<usize> {
        let update_query = "UPDATE users SET password = ?1 WHERE name = ?2 AND password = ?3";
        let result = self
            .connection
            .execute(update_query, params![new_password, name, password])?;
        Ok(result)
    }
    pub fn delete_user(&self, name: &str, password: &str) -> Result<usize> {
        let delete_query = "DELETE FROM users WHERE name = ?1 AND password = ?2";
        let result = self
            .connection
            .execute(delete_query, params![name, password])?;
        Ok(result)
    }
    pub fn insert_users_file(&self, name: &str,file:Vec<u8>)->Result<usize> {
        let conn:&Connection = &self.connection;
        let mut stmt = conn.prepare("UPDATE users SET filedata = ? WHERE name = ?")?;
        let result = stmt.execute(params![file, name])?;
        Ok(result)
    }
}
}

main.rs

fn main() {
    let conn = sqlite::SqliteDB::connect().expect("connection error");
    conn.add_user("andrew","dnrops").unwrap();
    conn.insert_users_file("andrew","ok".as_bytes().to_vec()).unwrap();
    let f = conn.get_user_filedata("andrew").unwrap();
    println!("{:?}", String::from_utf8_lossy(&f));
}