Elysia Tools

Explora herramientas, ejemplos, explicaciones visuales y guías temáticas en un solo espacio claro.

Categorías populares

Temas

Idioma
ZHESPTRUFRDEEN

Elysia Tools

Navegación

Rust

Ejemplos de Operaciones de Base de Datos Web Rust

Ejemplos de operaciones de base de datos Web Rust incluyendo conexión SQLite, consultas SQL y transacciones

Ejemplos

Entradas de esta colección

CódigorustSimple

Conexión SQLite

Conectar a la base de datos SQLite y realizar gestión básica de conexión

Dificultad

4/10

Tiempo estimado

20 min

Etiquetas

rust, web, database

Requisitos previos

Basic Rust, rusqlite crate

rust
// Web Rust SQLite Connection Examples
// Basic SQLite database connection and management
//
// Add to Cargo.toml:
// [dependencies]
// rusqlite = "0.30"

use rusqlite::{Connection, Result as SqliteResult};
use std::time::Duration;
use std::path::Path;

// 1. Basic Connection

/// Create or open in-memory database
fn create_memory_db() -> SqliteResult<Connection> {
    Connection::open_in_memory()
}

/// Create or open file-based database
fn create_file_db(db_path: &str) -> SqliteResult<Connection> {
    Connection::open(db_path)
}

/// Create database if it doesn't exist
fn open_or_create_db(db_path: &str) -> SqliteResult<Connection> {
    if !Path::new(db_path).exists() {
        println!("Creating new database: {}", db_path);
    }
    Connection::open(db_path)
}

// 2. Connection Information

/// Get database path
fn get_db_path(conn: &Connection) -> SqliteResult<String> {
    let path = conn.path()
        .and_then(|p| p.to_str())
        .unwrap_or(":memory:")
        .to_string();
    Ok(path)
}

/// Check if database is in-memory
fn is_in_memory(conn: &Connection) -> bool {
    conn.path().is_none()
}

/// Get database version
fn get_db_version(conn: &Connection) -> SqliteResult<String> {
    conn.query_row("SELECT sqlite_version()", [], |row| {
        row.get(0)
    })
}

// 3. Database Settings

/// Enable foreign keys
fn enable_foreign_keys(conn: &Connection) -> SqliteResult<()> {
    conn.execute("PRAGMA foreign_keys = ON", [])?;
    Ok(())
}

/// Set journal mode
fn set_journal_mode(conn: &Connection, mode: &str) -> SqliteResult<()> {
    conn.execute(&format!("PRAGMA journal_mode = {}", mode), [])?;
    Ok(())
}

/// Set synchronous mode
fn set_synchronous(conn: &Connection, mode: &str) -> SqliteResult<()> {
    conn.execute(&format!("PRAGMA synchronous = {}", mode), [])?;
    Ok(())
}

/// Set busy timeout (milliseconds)
fn set_busy_timeout(conn: &Connection, timeout_ms: u32) -> SqliteResult<()> {
    conn.busy_timeout(Duration::from_millis(timeout_ms as u64))
}

/// Set cache size (negative value = KB)
fn set_cache_size(conn: &Connection, size: i32) -> SqliteResult<()> {
    conn.execute(&format!("PRAGMA cache_size = {}", size), [])?;
    Ok(())
}

// 4. Connection Pool (Simple)

/// Simple connection wrapper
struct DatabasePool {
    connections: Vec<Connection>,
}

impl DatabasePool {
    /// Create a new connection pool
    fn new(db_path: &str, pool_size: usize) -> SqliteResult<Self> {
        let mut connections = Vec::new();
        for _ in 0..pool_size {
            connections.push(Connection::open(db_path)?);
        }
        Ok(DatabasePool { connections })
    }

    /// Get a connection from the pool
    fn get_connection(&mut self) -> Option<&Connection> {
        self.connections.first()
    }

    /// Get pool size
    fn size(&self) -> usize {
        self.connections.len()
    }
}

// 5. Database Creation

/// Create a new database with initial schema
fn create_initialized_db(db_path: &str) -> SqliteResult<Connection> {
    let conn = Connection::open(db_path)?;

    // Enable foreign keys
    enable_foreign_keys(&conn)?;

    // Create tables
    conn.execute(
        "CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            email TEXT UNIQUE NOT NULL,
            created_at TEXT DEFAULT CURRENT_TIMESTAMP
        )",
        [],
    )?;

    conn.execute(
        "CREATE TABLE IF NOT EXISTS posts (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            title TEXT NOT NULL,
            content TEXT,
            created_at TEXT DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id)
        )",
        [],
    )?;

    Ok(conn)
}

// 6. Backup Database

/// Create a backup of the database
fn backup_database(source_path: &str, backup_path: &str) -> SqliteResult<()> {
    let source = Connection::open(source_path)?;
    source.execute("VACUUM INTO ?1", [backup_path])?;
    Ok(())
}

// 7. Database Statistics

/// Get database size in bytes
fn get_db_size(conn: &Connection) -> SqliteResult<u64> {
    conn.query_row("SELECT page_count * page_size as size FROM pragma_page_count(), pragma_page_size()", [], |row| {
        row.get(0)
    })
}

/// Get table count
fn get_table_count(conn: &Connection) -> SqliteResult<usize> {
    conn.query_row("SELECT count(*) FROM sqlite_master WHERE type='table'", [], |row| {
        row.get(0)
    })
}

/// Get database page count
fn get_page_count(conn: &Connection) -> SqliteResult<usize> {
    conn.query_row("PRAGMA page_count", [], |row| {
        row.get(0)
    })
}

// 8. Integrity Check

/// Perform integrity check
fn check_integrity(conn: &Connection) -> SqliteResult<bool> {
    let result: String = conn.query_row("PRAGMA integrity_check", [], |row| {
        row.get(0)
    })?;
    Ok(result == "ok")
}

/// Perform quick check
fn quick_check(conn: &Connection) -> SqliteResult<bool> {
    let result: String = conn.query_row("PRAGMA quick_check", [], |row| {
        row.get(0)
    })?;
    Ok(result == "ok")
}

// 9. Optimize Database

/// Vacuum database to reclaim space
fn vacuum_database(conn: &Connection) -> SqliteResult<()> {
    conn.execute("VACUUM", [])
}

/// Analyze database for query optimization
fn analyze_database(conn: &Connection) -> SqliteResult<()> {
    conn.execute("ANALYZE", [])
}

/// Reindex database
fn reindex_database(conn: &Connection) -> SqliteResult<()> {
    conn.execute("REINDEX", [])
}

// 10. Close and Cleanup

/// Close database properly
fn close_database(conn: Connection) -> SqliteResult<()> {
    // Ensure all changes are committed
    conn.execute("PRAGMA optimize", [])?;
    drop(conn);
    Ok(())
}

// Usage Examples
fn main() -> SqliteResult<()> {
    println!("=== Web Rust SQLite Connection Examples ===\n");

    // 1. Create in-memory database
    println!("--- 1. In-Memory Database ---");
    let mem_conn = create_memory_db()?;
    println!("Created in-memory database");
    println!("Is in-memory: {}", is_in_memory(&mem_conn));

    // 2. Create file-based database
    println!("\n--- 2. File-Based Database ---");
    let db_path = "test.db";
    let file_conn = open_or_create_db(db_path)?;
    println!("Database path: {}", get_db_path(&file_conn)?);
    println!("SQLite version: {}", get_db_version(&file_conn)?);

    // 3. Configure database
    println!("\n--- 3. Database Configuration ---");
    enable_foreign_keys(&file_conn)?;
    set_journal_mode(&file_conn, "WAL")?;
    set_busy_timeout(&file_conn, 5000)?;
    println!("Database configured successfully");

    // 4. Create initialized database
    println!("\n--- 4. Create Database with Schema ---");
    let init_db_path = "initialized.db";
    let init_conn = create_initialized_db(init_db_path)?;
    println!("Database created with tables");
    println!("Table count: {}", get_table_count(&init_conn)?);

    // 5. Database statistics
    println!("\n--- 5. Database Statistics ---");
    println!("Size: {} bytes", get_db_size(&init_conn)?);
    println!("Page count: {}", get_page_count(&init_conn)?);

    // 6. Integrity check
    println!("\n--- 6. Integrity Check ---");
    println!("Integrity check passed: {}", check_integrity(&init_conn)?);
    println!("Quick check passed: {}", quick_check(&init_conn)?);

    // 7. Optimization
    println!("\n--- 7. Database Optimization ---");
    analyze_database(&init_conn)?;
    println!("Database analyzed for optimization");

    // 8. Connection pool
    println!("\n--- 8. Simple Connection Pool ---");
    let mut pool = DatabasePool::new("pool.db", 3)?;
    println!("Pool size: {}", pool.size());
    if let Some(conn) = pool.get_connection() {
        println!("Got connection from pool");
    }

    // 9. Backup
    println!("\n--- 9. Database Backup ---");
    let backup_path = "test_backup.db";
    backup_database(init_db_path, backup_path)?;
    println!("Database backed up to: {}", backup_path);

    // 10. Cleanup
    println!("\n--- 10. Cleanup ---");
    close_database(init_conn)?;
    println!("Database closed properly");

    // Note: In production, you'd want to remove these test files
    // std::fs::remove_file("test.db")?;
    // std::fs::remove_file("initialized.db")?;
    // std::fs::remove_file("test_backup.db")?;
    // std::fs::remove_file("pool.db")?;

    println!("\n=== All SQLite Connection Examples Completed ===");
    Ok(())
}
CódigorustIntermedio

Ejecución de Consultas SQL

Ejecutar consultas SELECT, INSERT, UPDATE y DELETE con enlace de parámetros apropiado

Dificultad

6/10

Tiempo estimado

25 min

Etiquetas

rust, web, database, sql

Requisitos previos

Intermediate Rust, rusqlite crate, SQL basics

rust
// Web Rust SQL Query Examples
// Execute various SQL queries with parameter binding
//
// Add to Cargo.toml:
// [dependencies]
// rusqlite = "0.30"

use rusqlite::{Connection, Result as SqliteResult, params};
use serde::Serialize;

// 1. INSERT Operations

/// User struct representing database record
#[derive(Debug, Serialize)]
struct User {
    id: i64,
    name: String,
    email: String,
    created_at: String,
}

/// Insert a single user
fn insert_user(conn: &Connection, name: &str, email: &str) -> SqliteResult<i64> {
    conn.execute(
        "INSERT INTO users (name, email) VALUES (?1, ?2)",
        params![name, email],
    )?;
    Ok(conn.last_insert_rowid())
}

/// Insert multiple users using transaction
fn insert_users_batch(conn: &Connection, users: &[(&str, &str)]) -> SqliteResult<Vec<i64>> {
    let mut ids = Vec::new();

    for (name, email) in users {
        conn.execute(
            "INSERT INTO users (name, email) VALUES (?1, ?2)",
            params![name, email],
        )?;
        ids.push(conn.last_insert_rowid());
    }

    Ok(ids)
}

/// Insert user with return value
fn insert_user_and_get(conn: &Connection, name: &str, email: &str) -> SqliteResult<User> {
    conn.execute(
        "INSERT INTO users (name, email) VALUES (?1, ?2)",
        params![name, email],
    )?;

    let id = conn.last_insert_rowid();
    get_user_by_id(conn, id)
}

/// Insert or replace (upsert)
fn insert_or_replace_user(conn: &Connection, id: i64, name: &str, email: &str) -> SqliteResult<()> {
    conn.execute(
        "INSERT OR REPLACE INTO users (id, name, email) VALUES (?1, ?2, ?3)",
        params![id, name, email],
    )?;
    Ok(())
}

// 2. SELECT Operations

/// Get user by ID
fn get_user_by_id(conn: &Connection, id: i64) -> SqliteResult<User> {
    conn.query_row(
        "SELECT id, name, email, created_at FROM users WHERE id = ?1",
        params![id],
        |row| {
            Ok(User {
                id: row.get(0)?,
                name: row.get(1)?,
                email: row.get(2)?,
                created_at: row.get(3)?,
            })
        },
    )
}

/// Get user by email
fn get_user_by_email(conn: &Connection, email: &str) -> SqliteResult<User> {
    conn.query_row(
        "SELECT id, name, email, created_at FROM users WHERE email = ?1",
        params![email],
        |row| {
            Ok(User {
                id: row.get(0)?,
                name: row.get(1)?,
                email: row.get(2)?,
                created_at: row.get(3)?,
            })
        },
    )
}

/// Get all users
fn get_all_users(conn: &Connection) -> SqliteResult<Vec<User>> {
    let mut stmt = conn.prepare(
        "SELECT id, name, email, created_at FROM users ORDER BY created_at DESC"
    )?;

    let user_iter = stmt.query_map([], |row| {
        Ok(User {
            id: row.get(0)?,
            name: row.get(1)?,
            email: row.get(2)?,
            created_at: row.get(3)?,
        })
    })?;

    user_iter.collect()
}

/// Get users with pagination
fn get_users_paginated(conn: &Connection, limit: usize, offset: usize) -> SqliteResult<Vec<User>> {
    let mut stmt = conn.prepare(
        "SELECT id, name, email, created_at FROM users ORDER BY id LIMIT ?1 OFFSET ?2"
    )?;

    let user_iter = stmt.query_map(params![limit, offset], |row| {
        Ok(User {
            id: row.get(0)?,
            name: row.get(1)?,
            email: row.get(2)?,
            created_at: row.get(3)?,
        })
    })?;

    user_iter.collect()
}

/// Search users by name
fn search_users_by_name(conn: &Connection, name_pattern: &str) -> SqliteResult<Vec<User>> {
    let mut stmt = conn.prepare(
        "SELECT id, name, email, created_at FROM users WHERE name LIKE ?1 ORDER BY name"
    )?;

    let user_iter = stmt.query_map(params![format!("%{}%", name_pattern)], |row| {
        Ok(User {
            id: row.get(0)?,
            name: row.get(1)?,
            email: row.get(2)?,
            created_at: row.get(3)?,
        })
    })?;

    user_iter.collect()
}

// 3. UPDATE Operations

/// Update user name
fn update_user_name(conn: &Connection, id: i64, new_name: &str) -> SqliteResult<usize> {
    conn.execute(
        "UPDATE users SET name = ?1 WHERE id = ?2",
        params![new_name, id],
    )
}

/// Update user email
fn update_user_email(conn: &Connection, id: i64, new_email: &str) -> SqliteResult<usize> {
    conn.execute(
        "UPDATE users SET email = ?1 WHERE id = ?2",
        params![new_email, id],
    )
}

/// Update multiple fields
fn update_user(conn: &Connection, id: i64, name: &str, email: &str) -> SqliteResult<usize> {
    conn.execute(
        "UPDATE users SET name = ?1, email = ?2 WHERE id = ?3",
        params![name, email, id],
    )
}

/// Increment user access counter
#[allow(dead_code)]
fn increment_user_counter(conn: &Connection, id: i64) -> SqliteResult<usize> {
    conn.execute(
        "UPDATE users SET access_count = COALESCE(access_count, 0) + 1 WHERE id = ?1",
        params![id],
    )
}

// 4. DELETE Operations

/// Delete user by ID
fn delete_user(conn: &Connection, id: i64) -> SqliteResult<usize> {
    conn.execute("DELETE FROM users WHERE id = ?1", params![id])
}

/// Delete users by condition
fn delete_users_by_name(conn: &Connection, name: &str) -> SqliteResult<usize> {
    conn.execute("DELETE FROM users WHERE name = ?1", params![name])
}

/// Delete all users (use with caution!)
fn delete_all_users(conn: &Connection) -> SqliteResult<usize> {
    conn.execute("DELETE FROM users", [])
}

// 5. COUNT and Aggregates

/// Count total users
fn count_users(conn: &Connection) -> SqliteResult<usize> {
    conn.query_row("SELECT COUNT(*) FROM users", [], |row| {
        row.get(0)
    })
}

/// Count users by name pattern
fn count_users_by_pattern(conn: &Connection, pattern: &str) -> SqliteResult<usize> {
    conn.query_row(
        "SELECT COUNT(*) FROM users WHERE name LIKE ?1",
        params![format!("%{}%", pattern)],
        |row| row.get(0)
    )
}

/// Get user statistics
#[derive(Debug)]
struct UserStats {
    total_users: usize,
    users_today: usize,
    avg_name_length: f64,
}

fn get_user_stats(conn: &Connection) -> SqliteResult<UserStats> {
    conn.query_row(
        "SELECT
            COUNT(*) as total,
            COUNT(CASE WHEN date(created_at) = date('now') THEN 1 END) as today,
            AVG(LENGTH(name)) as avg_length
        FROM users",
        [],
        |row| {
            Ok(UserStats {
                total_users: row.get(0)?,
                users_today: row.get(1)?,
                avg_name_length: row.get(2)?,
            })
        },
    )
}

// 6. Complex Queries

/// Get users with their post counts
#[derive(Debug)]
struct UserWithPostCount {
    id: i64,
    name: String,
    email: String,
    post_count: i64,
}

fn get_users_with_post_count(conn: &Connection) -> SqliteResult<Vec<UserWithPostCount>> {
    let mut stmt = conn.prepare(
        "SELECT u.id, u.name, u.email, COUNT(p.id) as post_count
         FROM users u
         LEFT JOIN posts p ON u.id = p.user_id
         GROUP BY u.id
         ORDER BY post_count DESC"
    )?;

    let user_iter = stmt.query_map([], |row| {
        Ok(UserWithPostCount {
            id: row.get(0)?,
            name: row.get(1)?,
            email: row.get(2)?,
            post_count: row.get(3)?,
        })
    })?;

    user_iter.collect()
}

/// Get recent activity
#[derive(Debug)]
struct RecentActivity {
    user_name: String,
    action_type: String,
    title: String,
    created_at: String,
}

fn get_recent_activity(conn: &Connection, limit: usize) -> SqliteResult<Vec<RecentActivity>> {
    let mut stmt = conn.prepare(
        "SELECT u.name, 'post' as type, p.title, p.created_at
         FROM posts p
         JOIN users u ON p.user_id = u.id
         ORDER BY p.created_at DESC
         LIMIT ?1"
    )?;

    let activity_iter = stmt.query_map(params![limit], |row| {
        Ok(RecentActivity {
            user_name: row.get(0)?,
            action_type: row.get(1)?,
            title: row.get(2)?,
            created_at: row.get(3)?,
        })
    })?;

    activity_iter.collect()
}

// Usage Examples
fn main() -> SqliteResult<()> {
    println!("=== Web Rust SQL Query Examples ===\n");

    let conn = Connection::open_in_memory()?;

    // Create tables
    conn.execute(
        "CREATE TABLE users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            email TEXT UNIQUE NOT NULL,
            created_at TEXT DEFAULT CURRENT_TIMESTAMP
        )",
        [],
    )?;

    conn.execute(
        "CREATE TABLE posts (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            title TEXT NOT NULL,
            content TEXT,
            created_at TEXT DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id)
        )",
        [],
    )?;

    // 1. INSERT examples
    println!("--- 1. INSERT Operations ---");
    let id1 = insert_user(&conn, "Alice", "[email protected]")?;
    println!("Inserted user with ID: {}", id1);

    let id2 = insert_user(&conn, "Bob", "[email protected]")?;
    println!("Inserted user with ID: {}", id2);

    let batch_ids = insert_users_batch(&conn, &[
        ("Charlie", "[email protected]"),
        ("Diana", "[email protected]"),
    ])?;
    println!("Batch inserted users: {:?}", batch_ids);

    // 2. SELECT examples
    println!("\n--- 2. SELECT Operations ---");
    match get_user_by_id(&conn, id1) {
        Ok(user) => println!("User by ID: {:?}", user),
        Err(e) => println!("Error: {}", e),
    }

    let all_users = get_all_users(&conn)?;
    println!("All users: {} found", all_users.len());
    for user in &all_users {
        println!("  - {} ({})", user.name, user.email);
    }

    // 3. SEARCH examples
    println!("\n--- 3. SEARCH Operations ---");
    let search_results = search_users_by_name(&conn, "a")?;
    println!("Users matching 'a': {} found", search_results.len());

    // 4. UPDATE examples
    println!("\n--- 4. UPDATE Operations ---");
    let updated = update_user_name(&conn, id1, "Alice Smith")?;
    println!("Updated {} row(s)", updated);

    // 5. COUNT and stats
    println!("\n--- 5. COUNT and Statistics ---");
    let count = count_users(&conn)?;
    println!("Total users: {}", count);

    let stats = get_user_stats(&conn)?;
    println!("User stats: {:?}", stats);

    // 6. DELETE examples
    println!("\n--- 6. DELETE Operations ---");
    let deleted = delete_user(&conn, id2)?;
    println!("Deleted {} user(s)", deleted);

    println!("\n=== All SQL Query Examples Completed ===");
    Ok(())
}
CódigorustIntermedio

Manejo de Transacciones

Gestionar transacciones de base de datos con commit, rollback y manejo de errores

Dificultad

7/10

Tiempo estimado

30 min

Etiquetas

rust, web, database, transaction

Requisitos previos

Intermediate Rust, rusqlite crate, transaction basics

rust
// Web Rust Transaction Handling Examples
// Transaction management with commit and rollback
//
// Add to Cargo.toml:
// [dependencies]
// rusqlite = "0.30"

use rusqlite::{Connection, Result as SqliteResult, Transaction, params};

// 1. Basic Transaction

/// Execute code within a transaction
fn execute_in_transaction<F>(conn: &Connection, mut operation: F) -> SqliteResult<()>
where
    F: FnMut(&Transaction) -> SqliteResult<()>,
{
    let tx = conn.unchecked_transaction()?;
    operation(&tx)?;
    tx.commit()?;
    Ok(())
}

/// Create tables within a transaction
fn create_tables_transaction(conn: &Connection) -> SqliteResult<()> {
    execute_in_transaction(conn, |tx| {
        tx.execute(
            "CREATE TABLE IF NOT EXISTS accounts (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                balance INTEGER NOT NULL DEFAULT 0
            )",
            [],
        )?;

        tx.execute(
            "CREATE TABLE IF NOT EXISTS transactions_log (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                from_account INTEGER,
                to_account INTEGER,
                amount INTEGER NOT NULL,
                timestamp TEXT DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (from_account) REFERENCES accounts(id),
                FOREIGN KEY (to_account) REFERENCES accounts(id)
            )",
            [],
        )?;

        Ok(())
    })
}

// 2. Transfer Money (Classic Transaction Example)

/// Transfer money between accounts with transaction
fn transfer_money(
    conn: &Connection,
    from_id: i64,
    to_id: i64,
    amount: i64,
) -> SqliteResult<()> {
    let tx = conn.unchecked_transaction()?;

    // Check from account has sufficient balance
    let balance: i64 = tx.query_row(
        "SELECT balance FROM accounts WHERE id = ?1",
        params![from_id],
        |row| row.get(0),
    )?;

    if balance < amount {
        return Err(rusqlite::Error::SqliteFailure(
            rusqlite::ffi::Error::new(rusqlite::ffi::SQLITE_CONSTRAINT),
            Some("Insufficient funds".to_string()),
        ));
    }

    // Deduct from sender
    tx.execute(
        "UPDATE accounts SET balance = balance - ?1 WHERE id = ?2",
        params![amount, from_id],
    )?;

    // Add to receiver
    tx.execute(
        "UPDATE accounts SET balance = balance + ?1 WHERE id = ?2",
        params![amount, to_id],
    )?;

    // Log transaction
    tx.execute(
        "INSERT INTO transactions_log (from_account, to_account, amount) VALUES (?1, ?2, ?3)",
        params![from_id, to_id, amount],
    )?;

    // Commit transaction
    tx.commit()?;
    Ok(())
}

/// Transfer with automatic rollback on error
fn transfer_safe(
    conn: &Connection,
    from_id: i64,
    to_id: i64,
    amount: i64,
) -> Result<String, Box<dyn std::error::Error>> {
    let mut tx = conn.unchecked_transaction()?;

    // Perform operations
    let result: SqliteResult<()> = (|| {
        // Deduct from sender
        tx.execute(
            "UPDATE accounts SET balance = balance - ?1 WHERE id = ?2",
            params![amount, from_id],
        )?;

        // Add to receiver
        tx.execute(
            "UPDATE accounts SET balance = balance + ?1 WHERE id = ?2",
            params![amount, to_id],
        )?;

        // Log
        tx.execute(
            "INSERT INTO transactions_log (from_account, to_account, amount) VALUES (?1, ?2, ?3)",
            params![from_id, to_id, amount],
        )?;

        Ok(())
    })();

    match result {
        Ok(_) => {
            tx.commit()?;
            Ok("Transfer successful".to_string())
        }
        Err(e) => {
            tx.rollback()?;
            Err(format!("Transfer failed: {}", e).into())
        }
    }
}

// 3. Nested Transactions (Savepoints)

/// Create nested transaction using savepoint
fn create_account_with_initial_balance(
    tx: &Transaction,
    name: &str,
    initial_balance: i64,
) -> SqliteResult<i64> {
    tx.execute(
        "INSERT INTO accounts (name, balance) VALUES (?1, ?2)",
        params![name, initial_balance],
    )?;
    Ok(tx.last_insert_rowid())
}

/// Batch transfer with savepoints
fn batch_transfer(
    conn: &Connection,
    transfers: Vec<(i64, i64, i64)>,
) -> SqliteResult<usize> {
    let tx = conn.unchecked_transaction()?;
    let mut success_count = 0;

    for (from, to, amount) in transfers {
        // Use savepoint for each transfer
        let sp = tx.savepoint()?;
        match sp.execute(
            "UPDATE accounts SET balance = balance - ?1 WHERE id = ?2",
            params![amount, from],
        ) {
            Ok(_) => {
                sp.execute(
                    "UPDATE accounts SET balance = balance + ?1 WHERE id = ?2",
                    params![amount, to],
                )?;
                sp.commit()?;
                success_count += 1;
            }
            Err(_) => {
                sp.rollback()?;
                continue;
            }
        }
    }

    tx.commit()?;
    Ok(success_count)
}

// 4. Transaction Isolation Levels

/// Set transaction isolation level
fn set_isolation_level(conn: &Connection, level: &str) -> SqliteResult<()> {
    conn.execute(&format!("PRAGMA read_unauthorized = {}", level), [])?;
    Ok(())
}

/// Read-only transaction
fn read_only_transaction<F, R>(conn: &Connection, operation: F) -> SqliteResult<R>
where
    F: FnOnce(&Transaction) -> SqliteResult<R>,
{
    let tx = conn.transaction()?;
    let result = operation(&tx)?;
    tx.commit()?;
    Ok(result)
}

// 5. Batch Operations

/// Insert multiple records in a transaction
fn batch_insert_accounts(
    conn: &Connection,
    accounts: Vec<(&str, i64)>,
) -> SqliteResult<Vec<i64>> {
    let tx = conn.unchecked_transaction()?;
    let mut ids = Vec::new();

    for (name, balance) in accounts {
        tx.execute(
            "INSERT INTO accounts (name, balance) VALUES (?1, ?2)",
            params![name, balance],
        )?;
        ids.push(tx.last_insert_rowid());
    }

    tx.commit()?;
    Ok(ids)
}

/// Batch update with transaction
fn batch_update_balances(
    conn: &Connection,
    updates: Vec<(i64, i64)>,
) -> SqliteResult<usize> {
    let tx = conn.unchecked_transaction()?;
    let mut total_updated = 0;

    for (id, new_balance) in updates {
        total_updated += tx.execute(
            "UPDATE accounts SET balance = ?1 WHERE id = ?2",
            params![new_balance, id],
        )?;
    }

    tx.commit()?;
    Ok(total_updated)
}

// 6. Transaction with Retry

/// Execute transaction with retry on busy
fn transaction_with_retry<F>(
    conn: &Connection,
    mut operation: F,
    max_retries: u32,
) -> SqliteResult<()>
where
    F: FnMut(&Transaction) -> SqliteResult<()>,
{
    let mut retries = 0;

    loop {
        let tx = conn.unchecked_transaction()?;

        match operation(&tx) {
            Ok(_) => {
                return tx.commit();
            }
            Err(rusqlite::Error::SqliteFailure(err, _))
                if err.code == rusqlite::ffi::ErrorCode::DatabaseBusy =>
            {
                retries += 1;
                if retries >= max_retries {
                    return Err(rusqlite::Error::SqliteFailure(err, None));
                }
                std::thread::sleep(std::time::Duration::from_millis(100));
                continue;
            }
            Err(e) => return Err(e),
        }
    }
}

// 7. Deferred Transaction

/// Begin deferred transaction (starts on first statement)
fn deferred_transaction<F>(conn: &Connection, operation: F) -> SqliteResult<()>
where
    F: FnOnce(&Transaction) -> SqliteResult<()>,
{
    let tx = conn.transaction_with_behavior(rusqlite::TransactionBehavior::Deferred)?;
    operation(&tx)?;
    tx.commit()?;
    Ok(())
}

// 8. Immediate Transaction

/// Begin immediate transaction (locks immediately)
fn immediate_transaction<F>(conn: &Connection, operation: F) -> SqliteResult<()>
where
    F: FnOnce(&Transaction) -> SqliteResult<()>,
{
    let tx = conn.transaction_with_behavior(rusqlite::TransactionBehavior::Immediate)?;
    operation(&tx)?;
    tx.commit()?;
    Ok(())
}

// 9. Exclusive Transaction

/// Begin exclusive transaction (exclusive lock)
fn exclusive_transaction<F>(conn: &Connection, operation: F) -> SqliteResult<()>
where
    F: FnOnce(&Transaction) -> SqliteResult<()>,
{
    let tx = conn.transaction_with_behavior(rusqlite::TransactionBehavior::Exclusive)?;
    operation(&tx)?;
    tx.commit()?;
    Ok(())
}

// 10. Transaction Status

/// Check if transaction is active
fn is_in_transaction(conn: &Connection) -> SqliteResult<bool> {
    conn.query_row("PRAGMA transaction_status", [], |row| {
        let status: String = row.get(0)?;
        Ok(status != "0")
    })
}

/// Get current transaction state
fn get_transaction_state(conn: &Connection) -> SqliteResult<String> {
    conn.query_row("PRAGMA transaction_status", [], |row| {
        row.get(0)
    })
}

// Usage Examples
fn main() -> SqliteResult<()> {
    println!("=== Web Rust Transaction Handling Examples ===\n");

    let conn = Connection::open_in_memory()?;

    // Create tables
    create_tables_transaction(&conn)?;
    println!("Created tables in transaction");

    // 1. Basic transaction
    println!("\n--- 1. Basic Transaction ---");
    execute_in_transaction(&conn, |tx| {
        tx.execute(
            "INSERT INTO accounts (name, balance) VALUES (?1, ?2)",
            params!["Alice", 1000],
        )?;
        tx.execute(
            "INSERT INTO accounts (name, balance) VALUES (?1, ?2)",
            params!["Bob", 500],
        )?;
        Ok(())
    })?;
    println!("Inserted initial accounts");

    // 2. Transfer money
    println!("\n--- 2. Transfer Money ---");
    let from_id = 1;
    let to_id = 2;
    match transfer_money(&conn, from_id, to_id, 200) {
        Ok(_) => println!("Transfer successful"),
        Err(e) => println!("Transfer failed: {}", e),
    }

    // 3. Safe transfer with rollback
    println!("\n--- 3. Safe Transfer ---");
    match transfer_safe(&conn, from_id, to_id, 100) {
        Ok(msg) => println!("{}", msg),
        Err(e) => println!("Error: {}", e),
    }

    // 4. Batch operations
    println!("\n--- 4. Batch Insert ---");
    let accounts = vec![
        ("Charlie", 750),
        ("Diana", 1200),
        ("Eve", 300),
    ];
    match batch_insert_accounts(&conn, accounts) {
        Ok(ids) => println!("Batch inserted {} accounts", ids.len()),
        Err(e) => println!("Batch insert failed: {}", e),
    }

    // 5. Batch transfer
    println!("\n--- 5. Batch Transfer ---");
    let transfers = vec![
        (1, 3, 50),   // Alice -> Charlie
        (2, 4, 100),  // Bob -> Diana
        (3, 5, 25),   // Charlie -> Eve
    ];
    match batch_transfer(&conn, transfers) {
        Ok(count) => println!("Completed {} transfers", count),
        Err(e) => println!("Batch transfer failed: {}", e),
    }

    // 6. Transaction status
    println!("\n--- 6. Transaction Status ---");
    println!("In transaction: {}", is_in_transaction(&conn)?);
    println!("Transaction state: {}", get_transaction_state(&conn)?);

    // 7. Read-only transaction
    println!("\n--- 7. Read-Only Transaction ---");
    let balance: i64 = read_only_transaction(&conn, |tx| {
        tx.query_row("SELECT SUM(balance) FROM accounts", [], |row| row.get(0))
    })?;
    println!("Total balance: {}", balance);

    // 8. Deferred transaction
    println!("\n--- 8. Deferred Transaction ---");
    deferred_transaction(&conn, |tx| {
        tx.execute(
            "INSERT INTO accounts (name, balance) VALUES (?1, ?2)",
            params!["Frank", 600],
        )?;
        Ok(())
    })?;
    println!("Inserted account with deferred transaction");

    println!("\n=== All Transaction Handling Examples Completed ===");
    Ok(())
}

Herramientas

Herramientas que suelen usarse con este ejemplo

Relacionado

Colecciones de ejemplos relacionadas

Relacionado

Temas relacionados

Guía de uso de la herramienta

Contexto para entender esta colección de ejemplos

Título

Ejemplos de Operaciones de Base de Datos Web Rust

Descripción

Ejemplos de operaciones de base de datos Web Rust incluyendo conexión SQLite, consultas SQL y transacciones

Resumen

Ejemplos de operaciones de base de datos Web Rust incluyendo conexión SQLite, consultas SQL y transacciones

Cuándo usarlo

  • Use this sample set when you want reference inputs for Ejemplos de Operaciones de Base de Datos Web Rust.
  • Use it to preview output formats and compare related sample variants.

Cómo funciona

  1. 1Open a sample entry to inspect the prepared example payload or asset.
  2. 2Compare it with related tools to understand how the workflow connects.

Casos de uso

  • Conexión SQLite
  • Ejecución de Consultas SQL
  • Manejo de Transacciones

Preguntas frecuentes

What is included in Ejemplos de Operaciones de Base de Datos Web Rust?

This page includes curated sample entries, related tools, and neighboring sample collections for Ejemplos de Operaciones de Base de Datos Web Rust.

Tema

Migracion de Esquema de Base de Datos

Planifica una migracion segura normalizando datos de origen, revisando diferencias de esquema, validando relaciones y preparando recuperacion.

Tema

Repare una importación CSV rechazada y cárguela en su base de datos

Averigüe por qué una importación CSV falla una y otra vez, alinee los encabezados caóticos con el esquema de destino, elimine duplicados bajo una política declarada y genere sentencias INSERT con tipos o un TSV para carga masiva.

Tema

Auditoría de fuentes web para reutilización y publicación

Extrae evidencia del código fuente, revisa enlaces y recursos, analiza metadatos y query strings, y comprueba RSS/Atom y robots.txt antes de reutilizar, migrar o publicar.

Tema

Flujos de calidad de datos e investigación de anomalías

Perfila datos tabulares, aísla duplicados, faltantes, rupturas referenciales, valores atípicos y anomalías temporales, y convierte los hallazgos en evidencia defendible.

Inicio
Herramientas3201
Ejemplos593
Visualizaciones465
Visores de archivos103
Temas233
Etiquetas333
Imágenes, audio y video657

Herramientas de procesamiento, conversión y optimización de imágenes, audio y video

Matemáticas, fechas y finanzas644

Calculadoras, herramientas numéricas, lógica de fechas, estadística y finanzas

Diseño y color297

Herramientas de color, maquetación, gráficos, estilo visual y apoyo al diseño

Texto y redacción209

Herramientas de redacción, limpieza, formato, extracción y análisis de texto

Desarrollo y Web193

Utilidades para desarrolladores, red, depuración web y automatización

Conversión y codificación175

Herramientas de conversión de formatos, archivos, unidades, archivos comprimidos y codificación

Revisión de fuentes FLAC/WAV y entrega de evidencias
Revisión e integración de un componente de sorteos autoalojado
Revisión de lotes de códigos Stripe y comprobación previa a la importación
Revisión del descarte fotográfico y entrega de candidatos para edición
Estructurar una captura de interfaz, revisarla y entregarla a desarrollo

© 2026 Elysia Tools. Todos los derechos reservados.

DocumentaciónPreciosAcerca deTérminos de servicioPolítica de privacidadCookiesContacto

Development

Generador de Respaldo de Base de Datos

Generar scripts de respaldo SQL para tablas

Development

Diff de Schema de Base de Datos

Compara dos schemas y genera un borrador de migracion

Development

Generador de Migración de Base de Datos

Generar scripts de migración SQL para cambios de esquema

Data Processing

Planificador de migracion CSV a base de datos

Infiere un schema relacional desde CSV y genera planes CREATE TABLE y ALTER para PostgreSQL, MySQL, SQLite o SQL Server

Format Conversion

Emisor de INSERT SQL desde CSV

Analiza CSV/Excel-CSV/TSV (detección automática, RFC 4180) y genera lotes INSERT para MySQL/PostgreSQL/SQLite/SQL Server con inferencia de tipos y ON CONFLICT/IGNORE

Data Processing

Unificador de Tablas Múltiples

Unir y fusionar múltiples tablas con varios tipos de unión (interno, izquierdo, derecho, completo)

Development

Puente de dialectos SQL

Traduce DDL, DML, funciones y tipos entre MySQL / PostgreSQL / SQLite / BigQuery / Snowflake / Redshift, señalando lo no soportado

Development

Visualizador de Plan de Ejecución SQL

Pega la salida de EXPLAIN / EXPLAIN ANALYZE (PostgreSQL/MySQL/SQLite) para renderizar un árbol de costos, marcar divergencias entre filas estimadas y reales, y sugerir índices

Java

Ejemplos de Base de Datos Android Java

Ejemplos de base de datos Android Java incluyendo conexión SQLite, consultas SQL y procesamiento de transacciones

Kotlin

Ejemplos de Base de Datos Android Kotlin

Ejemplos de base de datos Android Kotlin incluyendo conexión SQLite, ejecución de consultas SQL y procesamiento de transacciones

Objective-C

Base de Datos macOS Objective-C - Ejemplos

Ejemplos de base de datos macOS Objective-C incluyendo operaciones CRUD SQLite, consultas, transacciones y gestión de conexiones

Swift

Ejemplos de Base de Datos macOS Swift

Ejemplos de base de datos macOS Swift incluyendo conexión SQLite, ejecución de consultas y procesamiento de transacciones

Go

Ejemplos de Operaciones de Base de Datos Web Go

Ejemplos de operaciones de base de datos Web Go incluyendo conexiones SQLite, consultas SQL y manejo de transacciones

Python

Ejemplos de Base de Datos Web Python

Ejemplos de base de datos Web Python incluyendo conexión SQLite, consultas SQL y manejo de transacciones