Skip to content

README.md

Latest commit

 

History

History
170 lines (121 loc) · 4.99 KB

README.md

File metadata and controls

170 lines (121 loc) · 4.99 KB

Birko.Data.SQL.View

SQL view generation framework for creating database views from C# entity attributes.

Features

  • Attribute-based view definition
  • Automatic view SQL generation
  • Join and filter support
  • Materialized view support (PostgreSQL)
  • Cross-database compatibility (SQL Server, PostgreSQL, MySQL, SQLite)

Installation

dotnet add package Birko.Data.SQL.View

Dependencies

  • Birko.Data.Core (AbstractModel)
  • Birko.Data.Stores (store interfaces)
  • Birko.Data.SQL

Usage

Define a View

using Birko.Data.SQL.View.Attributes;

[View("customer_orders_view")]
[ViewJoin("Orders", "Id", "CustomerId")]
public class CustomerOrderView
{
    [ViewColumn("customer_id", "Id")]
    public Guid CustomerId { get; set; }

    [ViewColumn("customer_name", "Name")]
    public string CustomerName { get; set; }

    [ViewColumn("order_count")]
    public int OrderCount { get; set; }
}

Generate the View

var generator = new ViewGenerator<CustomerOrderView>();
var sql = generator.GenerateCreateView(connection);

Filtered View

[View("active_users_view")]
[ViewFilter("IsActive = true")]
[ViewFilter("DeletedAt IS NULL")]
public class ActiveUsersView { /* ... */ }

Materialized View (PostgreSQL)

[View("user_stats", Materialized = true, RefreshInterval = "1 hour")]
public class UserStatsView { /* ... */ }

API Reference

Attributes

  • ViewAttribute - Marks entity for view generation (name, materialized, refresh)
  • ViewColumnAttribute - Defines view column mapping
  • ViewJoinAttribute - Defines table joins
  • ViewFilterAttribute - Defines WHERE filters

Classes

  • ViewGenerator<T> - Generates CREATE VIEW SQL
  • ViewBuilder - View query builder
  • ViewDefinition - View metadata

View Query Modes

The framework supports multiple query modes via the ViewQueryMode enum:

Mode Description
OnTheFly Generates a SELECT statement at query time without creating a database view object. Default mode.
Persistent Queries an existing database VIEW object (must be created first via DDL methods).
Auto Uses the persistent view if it exists, otherwise falls back to on-the-fly generation. 
// Query a persistent view directly
var results = connector.SelectView<CustomerOrderView>(ViewQueryMode.Persistent);

// Auto mode: uses persistent view if available, on-the-fly otherwise
var results = connector.SelectView<CustomerOrderView>(ViewQueryMode.Auto);

Materialized View Types

The MaterializedViewType enum identifies the materialization strategy across providers:

Type Provider Description
None All Standard (non-materialized) view
Materialized PostgreSQL PostgreSQL MATERIALIZED VIEW with explicit refresh
Indexed SQL Server Indexed view with SCHEMABINDING and clustered index

Persistent View DDL

In addition to on-the-fly SELECT generation, the framework supports creating and managing actual database VIEW objects.

Create / Drop Views

// Sync
connector.CreateView(typeof(CustomerOrderView));
connector.CreateViewIfNotExists(typeof(CustomerOrderView));
connector.DropView("customer_orders_view");
connector.RecreateView(typeof(CustomerOrderView));

// Async
await connector.CreateViewAsync(typeof(CustomerOrderView));
await connector.CreateViewIfNotExistsAsync(typeof(CustomerOrderView));
await connector.DropViewAsync("customer_orders_view");
await connector.RecreateViewAsync(typeof(CustomerOrderView));

Check View Existence

bool exists = connector.ViewExists("customer_orders_view");
bool exists = await connector.ViewExistsAsync("customer_orders_view");

Batch Operations

connector.CreateViews(new[] { typeof(View1), typeof(View2) });
connector.DropViews(new[] { "view1", "view2" });

Database-Specific Behavior

Each SQL provider has a separate view project with DDL overrides:

Provider CREATE syntax ViewExists catalog
MSSql CREATE OR ALTER VIEW sys.views
PostgreSQL CREATE OR REPLACE VIEW information_schema.views
MySQL CREATE OR REPLACE VIEW information_schema.VIEWS
SQLite CREATE VIEW IF NOT EXISTS sqlite_master

PostgreSQL also supports materialized views via CreateMaterializedView, RefreshMaterializedView, and DropMaterializedView.

Related Projects

License

Part of the Birko Framework.