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));
}