Views

The View class in Weasel.Sqlite.Views provides CREATE/DROP support with automatic delta detection for SQLite views.

Creating a View

var view = new View("active_users",
    "SELECT id, name, email FROM users WHERE active = 1");

^{snippet source | anchor}

Generating DDL

Views use a drop-then-create strategy since SQLite has no ALTER VIEW support:

var view = new View("active_users",
    "SELECT id, name, email FROM users WHERE active = 1");

var migrator = new SqliteMigrator();
var writer = new StringWriter();
view.WriteCreateStatement(migrator, writer);

// Output:
// DROP VIEW IF EXISTS "active_users";
// CREATE VIEW "active_users" AS SELECT id, name, email FROM users WHERE active = 1;

^{snippet source | anchor}

Schema Support

Views support main (default) and temp schemas:

// Temporary view (connection-scoped)
var tempView = new View(
    new SqliteObjectName("temp", "session_summary"),
    "SELECT session_id, COUNT(*) as event_count FROM temp.session_data GROUP BY session_id");

// DDL: DROP VIEW IF EXISTS "temp"."session_summary";
// CREATE VIEW "temp"."session_summary" AS SELECT ...

^{snippet source | anchor}

Complex View Examples

Views can use JOINs, aggregations, and JSON extraction:

// Aggregation with JOIN
var orderSummary = new View("user_order_summary", @"
SELECT u.id, u.name, COUNT(o.id) as order_count, SUM(o.amount) as total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name");

// JSON extraction
var productDetails = new View("product_details", @"
SELECT id, name,
json_extract(metadata, '$.category') as category,
json_extract(metadata, '$.price') as price
FROM products");

^{snippet source | anchor}

Delta Detection

The ViewDelta class detects changes between expected and actual view definitions using whitespace-insensitive SQL comparison:

var connection = new SqliteConnection("Data Source=myapp.db");
await connection.OpenAsync();

var view = new View("active_users",
    "SELECT id, name, email FROM users WHERE active = 1");

// Check if view exists
var exists = await view.ExistsInDatabaseAsync(connection);

// Fetch current definition from sqlite_master
var existing = await view.FetchExistingAsync(connection);

// Compare expected vs actual
var expectedView = view;
var actualView = existing;
var delta = new ViewDelta(expectedView, actualView);

switch (delta.Difference)
{
    case SchemaPatchDifference.None:
        // View matches expected definition
        break;
    case SchemaPatchDifference.Create:
        // View does not exist yet
        break;
    case SchemaPatchDifference.Update:
        // View SQL changed, will drop and recreate
        break;
}

^{snippet source | anchor}

Limitations

• SQLite views are read-only (no INSERT, UPDATE, or DELETE)
• No materialized views (use tables with triggers as an alternative)
• Temporary views cannot reference main schema tables
• Changes always require DROP + CREATE (handled automatically by ViewDelta)