CRUD 操作

灵动桌面框架的数据库 CRUD 操作封装在 Database 层。

查询所有

impl Database {
    pub fn get_all_configs(&self) -> Result<Vec<AppConfig>, AppError> {
        let conn = self.conn.lock().map_err(|_| AppError::LockError)?;
        let mut stmt = conn.prepare(
            "SELECT id, key, value, created_at, updated_at FROM app_config ORDER BY id"
        )?;
        let configs = stmt.query_map([], |row| {
            Ok(AppConfig {
                id: row.get(0)?,
                key: row.get(1)?,
                value: row.get(2)?,
                created_at: row.get(3)?,
                updated_at: row.get(4)?,
            })
        })?.collect::<Result<Vec<_>, _>>()?;
        Ok(configs)
    }
}

查询单条

pub fn get_config(&self, key: &str) -> Result<Option<String>, AppError> {
    let conn = self.conn.lock().map_err(|_| AppError::LockError)?;
    match conn.query_row(
        "SELECT value FROM app_config WHERE key = ?1",
        [key],
        |row| row.get::<_, String>(0),
    ) {
        Ok(value) => Ok(Some(value)),
        Err(rusqlite::Error::QueryReturnedNoRows) => Ok(None),
        Err(e) => Err(AppError::from(e)),
    }
}

新增/更新（Upsert）

pub fn upsert_config(&self, key: &str, value: &str) -> Result<(), AppError> {
    let conn = self.conn.lock().map_err(|_| AppError::LockError)?;
    conn.execute(
        "INSERT INTO app_config (key, value)
         VALUES (?1, ?2)
         ON CONFLICT(key) DO UPDATE SET
           value = excluded.value,
           updated_at = datetime('now', 'localtime')",
        rusqlite::params![key, value],
    )?;
    Ok(())
}

删除

pub fn delete_config(&self, key: &str) -> Result<(), AppError> {
    let conn = self.conn.lock().map_err(|_| AppError::LockError)?;
    conn.execute(
        "DELETE FROM app_config WHERE key = ?1",
        [key],
    )?;
    Ok(())
}

完整调用链

前端: deleteConfig('theme')
  → Command: delete_config(state, key)
    → Service: config_service().delete(&key)
      → Database: delete_config(&key)
        → SQL: DELETE FROM app_config WHERE key = 'theme'

参数绑定

安全

始终使用参数化查询（?1, ?2），禁止字符串拼接 SQL，防止 SQL 注入。

// ✅ 正确：参数绑定
conn.execute("SELECT * FROM t WHERE key = ?1", [key])?;

// ❌ 错误：字符串拼接
conn.execute(&format!("SELECT * FROM t WHERE key = '{}'", key), [])?;