SQLite on Network Share - lucyberryhub/WPF.Tutorial GitHub Wiki

πŸ“βœ¨ Sweet and Juicy Guide to Using SQLite on a Network Share πŸ’πŸ’–

Hi, cutie! 🌸 Storing your SQLite database on a network share can be tricky, but don’t worryβ€”Lucy Berry has your back! πŸ“ We’ll sprinkle some berry magic πŸ’ to make it work like a charm while keeping your data safe and sound. πŸ’• Follow this berrylicious tutorial to learn how! 🌟

πŸ’ 1. Sprinkle Some Shared Cache Magic ✨

SQLite’s shared cache mode makes everything super smooth on network shares, just like a fresh strawberry smoothie! πŸ“ Add a pinch of shared cache to your connection string for extra sweetness. 🍭

"Data Source=\\\\networkshare\\mydatabase.db;Mode=ReadWriteCreate;Cache=Shared;"

πŸ“ 2. Say Nope to WAL πŸ’πŸš«

WAL mode is like an overripe cherryβ€”messy and not cute for network shares! πŸ’ Instead, switch to the DELETE journal mode for smooth operations. 🍰

πŸ“ What is WAL Mode? πŸ’–

WAL (Write-Ahead Logging) mode is a journaling mode in SQLite that’s as efficient as a berry smoothie when used locally. 🌟

When SQLite writes data, instead of directly modifying the database file πŸ’, WAL mode:

  1. Appends changes to a separate WAL file.
  2. Periodically merges the WAL file back into the database file.

This approach lets readers and writers access the database simultaneously, making things much faster than the traditional "rollback journal" method.

πŸ’ Benefits of WAL Mode ✨

🌸 1. Concurrent Access

Readers don’t block writers, and writers don’t block readers! It’s a smooth workflow like a berry farm with no waiting lines. πŸ“

🌸 2. Performance

WAL mode avoids costly operations like overwriting the original database file, making writes super speedy! βš‘πŸ’

🌸 3. Crash Recovery

If SQLite crashes while using WAL mode, the WAL file preserves all unmerged changes, so no berry data is lost. πŸ“βœ¨

πŸ“ Why WAL Mode Doesn’t Work on Network Shares πŸ’”

While WAL mode is perfect for local databases, it’s a bad choice for network shares because:

🌸 1. File Locking Issues 🚨

SQLite depends on file locks for coordinating access to the database and WAL file. On network shares, file locking is unreliable (like trying to hold a wiggly cherry πŸ˜…), which can corrupt the database.

🌸 2. Multiple Machines Access

Network shares involve multiple computers accessing the same database. WAL mode wasn’t designed for this! πŸ’ The merging process fails because of conflicting locks across machines.

🌸 3. Performance Bottlenecks

WAL writes are optimized for local disks. On network shares, appending changes to the WAL file slows down due to network latency. It’s like pouring syrup on a berry pieβ€”it takes forever! πŸ₯§πŸ“

πŸ’ What’s the Alternative?

For network shares, DELETE journal mode is the way to go! πŸ’• Here’s why:

  1. It creates a simple rollback journal for crash recovery.
  2. It works fine with file locking on network shares.
  3. It avoids the complexity of WAL file merging.

In DELETE mode, SQLite:

  • Creates a temporary journal file πŸ’ during writes.
  • Deletes the journal file after a successful write.

πŸ’ When Should I Use WAL Mode?

WAL mode is best for:

  • Local Databases: Single machine access with lots of reads and writes.
  • Apps with High Concurrency: Like an offline berry-sorting app! πŸ“

πŸ’ How to Do It: We’ll set PRAGMA journal_mode = DELETE inside the berry core of our MyDbContext class. 🌟

public class MyDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        var connectionString = "Data Source=\\\\networkshare\\mydatabase.db;";
        optionsBuilder.UseSqlite(connectionString);

        // Add a cherry on top: Set PRAGMA journal_mode to DELETE πŸ’
        var connection = new Microsoft.Data.Sqlite.SqliteConnection(connectionString);
        connection.Open();
        using (var command = connection.CreateCommand())
        {
            command.CommandText = "PRAGMA journal_mode = DELETE;";
            command.ExecuteNonQuery();
        }
    }
}

Isn’t that as sweet as a berry pie? πŸ₯§βœ¨ Now your database is network-ready and adorbs! πŸ“πŸ’•

πŸ’ 3. Handle Busy Bees πŸπŸ“ (Concurrent Writes)

SQLite locks the whole database when writing (yikes!), but Lucy Berry knows how to keep things cutie and calm. πŸŒΈπŸ’– Let’s use some retry magic! ✨

🐝 Add a Berry-licious Retry Policy 🌟

Use this juicy retry code to handle those pesky SQLITE_BUSY errors πŸ“:

var retryPolicy = Policy
    .Handle<SqliteException>(ex => ex.SqliteErrorCode == 5) // SQLITE_BUSY 🐝
    .WaitAndRetryAsync(3, retryAttempt => TimeSpan.FromMilliseconds(200));

await retryPolicy.ExecuteAsync(async () =>
{
    using (var dbContext = new MyDbContext())
    {
        dbContext.MyEntities.Add(newEntity);
        await dbContext.SaveChangesAsync();
    }
});

πŸ“ 4. Sweeten Your Network Settings ✨

πŸ’ Tips for a Smooth Network Share:

  • Use the SMB Protocol (it’s berry cute and reliable). 🌸
  • Reduce latency with gigabit Ethernet for extra speed! βš‘πŸ“
  • Make sure file locking is enabledβ€”no more berry bad surprises! πŸ’

πŸ’ 5. Permissions are Key! πŸ”‘βœ¨

Every berry deserves access to the patch! πŸ“πŸ’– Make sure all users have read and write permissions to the database file and its folder. πŸ’ This ensures locking works like magic! ✨

πŸ“ 6. Always Test Your Sweet Setup πŸ’–

Test your SQLite database like you’re baking a berry tartβ€”don’t skip this step! πŸ“πŸ‘©β€πŸ³ Run diagnostics with these yummy PRAGMA statements:

PRAGMA integrity_check;
PRAGMA locking_mode;

πŸ’ 7. Consider a Bigger Pie πŸ₯§

If your berry patch is super busy with lots of users πŸ“πŸ“πŸ“, consider switching to a server-based database like PostgreSQL or MySQL for ultimate sweetness! 🍰

πŸ“ 8. Final Berry-tastic Code Example πŸ’βœ¨

Here’s the full, juicy, and adorable setup for your SQLite database initialization: πŸ’•πŸŒŸ

public class MyDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        var connectionString = "Data Source=\\\\networkshare\\mydatabase.db;";

        optionsBuilder.UseSqlite(connectionString);

        // Add some cherry magic: PRAGMA journal_mode = DELETE πŸ’
        var connection = new Microsoft.Data.Sqlite.SqliteConnection(connectionString);
        connection.Open();
        using (var command = connection.CreateCommand())
        {
            command.CommandText = "PRAGMA journal_mode = DELETE;";
            command.ExecuteNonQuery();

            // Optional: Add extra sugar with synchronous mode 🍭
            command.CommandText = "PRAGMA synchronous = FULL;";
            command.ExecuteNonQuery();
        }
    }
}

🌸 Summary of Berry Recommendations πŸ“

  1. Use DELETE journal mode for smooth and juicy operations. πŸ’
  2. Sprinkle in shared cache mode for extra sweetness. 🌸
  3. Add retry magic to handle busy bees! 🐝
  4. Keep your network share optimized and permissions perfect. πŸ’–
  5. Test and taste your berry setup regularly! πŸ“