Class View

Namespace: YndigoBlue.Velocity.Model

Assembly: YndigoBlue.Velocity.dll

Represents a database view - a virtual table defined by a query.

public class View : Table, IEquatable<Table>, IComparable<Table>, IFromItem, IEquatable<View>

Inheritance: object

Table

View

Implements: IEquatable<Table>

IComparable<Table>

IFromItem

IEquatable<View>

Remarks

Views are saved queries that can be used like tables in other queries. Velocity supports two types of views:

Code Views Defined using Velocity's Query objects. Portable across all database types. Created programmatically or loaded from XML/JSON/YAML schema files with ViewType.Code.
SQL Views Defined using raw SQL text. Database-specific and not portable. Created when loading schemas from databases using LoadSchema(string).

Views inherit from Table and can be used in the FROM clause of queries. When you set a view's Query property, the view automatically creates columns matching the query's SELECT items.

Examples

Creating a code-based view programmatically:

Schema schema = Schema.NewSchema("my_schema");
Table ordersTable = schema.CreateTable("orders");
ordersTable.CreateColumn("id", DataType.Integer);
ordersTable.CreateColumn("user_id", DataType.Integer);
ordersTable.CreateColumn("total", DataType.Decimal, precision: 10, scale: 2);
ordersTable.CreateColumn("status", DataType.VarChar, size: 50);
ordersTable.CreateColumn("created_at", DataType.DateTime);

// Create a view for active orders
Query viewQuery = new Query()
    .Select([ ordersTable["id"], ordersTable["user_id"], ordersTable["total"], ordersTable["created_at"] ])
    .From(ordersTable)
    .Where(new Criterion<string>(ordersTable["status"], ConditionalType.Equals, "active"));

schema.CreateView("active_orders", ViewType.Code, viewQuery);

using (Manager manager = new Manager(connection))
{
    manager.BuildSchema(schema);
}

// SQL: CREATE VIEW active_orders AS
//      SELECT id, user_id, total, created_at
//      FROM orders
//      WHERE status = 'active'

Using a view in queries:

using (Manager manager = new Manager(connection))
{
    Schema schema = manager.LoadSchema("my_schema");

    // Views can be used like tables
    View activeOrdersView = schema.GetView("active_orders");

    var query = new Query()
        .Select([ activeOrdersView["user_id"], new Expression("total_spent", new Sum(activeOrdersView["total"])) ])
        .From(activeOrdersView)
        .GroupBy(activeOrdersView["user_id"]);

    using (ResultSet results = manager.Retrieve(query))
    {
        // process results
    }
}

// SQL: SELECT user_id, SUM(total)
//      FROM active_orders
//      GROUP BY user_id

Creating a view with JOINs:

Schema schema = Schema.NewSchema("my_schema");
Table usersTable = schema.CreateTable("users");
usersTable.CreateColumn("id", DataType.Integer);
usersTable.CreateColumn("username", DataType.VarChar, size: 100);

Table ordersTable = schema.CreateTable("orders");
ordersTable.CreateColumn("id", DataType.Integer);
ordersTable.CreateColumn("user_id", DataType.Integer);
ordersTable.CreateColumn("total", DataType.Decimal, precision: 10, scale: 2);

// Create view with JOIN
Join join = new Join(ordersTable, usersTable, "user_id", JoinType.Inner);

Query viewQuery = new Query()
    .Select([ usersTable["username"], ordersTable["total"], ordersTable["id"] ])
    .From(join);

schema.CreateView("user_orders", ViewType.Code, viewQuery);

// SQL: CREATE VIEW user_orders AS
//      SELECT users.username, orders.total, orders.id
//      FROM orders
//      INNER JOIN users ON orders.user_id = users.id

Loading SQL views from database:

using (Manager manager = new Manager(connection))
{
    // LoadSchema loads existing views as SQL type
    Schema schema = manager.LoadSchema("my_schema");

    foreach (View view in schema.Views)
    {
        Console.WriteLine($"View: {view.Name}");
        Console.WriteLine($"Type: {view.ViewType}"); // ViewType.Sql
        Console.WriteLine($"SQL: {view.Sql}");

        // SQL views contain the database-specific view definition
        // Code views contain null for Sql property
    }
}

Constructors

View(string, string, ViewType): Creates a new view with the specified name, schema, and type.

Properties

Code: Gets or sets the code representation of the view (for future use).

Query: Gets or sets the query that defines this view.

Sql: Gets or sets the raw SQL definition of the view (for SQL-type views only).

ViewType: Gets the type of this view (Code or Sql).

Methods