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>

Examples

Creating a code-based view programmatically:

using (Manager manager = new Manager(connection))
{
    Schema schema = manager.CreateSchema("my_schema");
    Table ordersTable = schema.CreateTable("orders");
    ordersTable.AddColumn("id", DataType.Integer);
    ordersTable.AddColumn("user_id", DataType.Integer);
    ordersTable.AddColumn("total", DataType.Decimal, precision: 10, scale: 2);
    ordersTable.AddColumn("status", DataType.VarChar, size: 50);
    ordersTable.AddColumn("created_at", DataType.DateTime);

    // Create a view for active orders
    View activeOrdersView = schema.CreateView("active_orders", ViewType.Code);

    Query viewQuery = new Query();
    viewQuery.AddFromItem(ordersTable);
    viewQuery.AddSelectItem(ordersTable["id"]);
    viewQuery.AddSelectItem(ordersTable["user_id"]);
    viewQuery.AddSelectItem(ordersTable["total"]);
    viewQuery.AddSelectItem(ordersTable["created_at"]);
    viewQuery.SetWhereFilter(
        new Filter(new Criterion<string>(ordersTable["status"], "active"))
    );

    activeOrdersView.Query = viewQuery;

    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))
{
    manager.LoadSchema("my_schema");
    Schema schema = manager.GetSchema("my_schema");

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

    Query query = new Query();
    query.AddFromItem(activeOrdersView);
    query.AddSelectItem(activeOrdersView["user_id"]);
    query.AddSelectItem(new Function(FunctionType.Sum, activeOrdersView["total"]));
    query.AddGroupClause(activeOrdersView["user_id"]);

    Result result = manager.Retrieve(query);
}

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

Creating a view with JOINs:

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

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

// Create view with JOIN
View userOrdersView = schema.CreateView("user_orders", ViewType.Code);

JoinCondition joinCondition = new JoinCondition(
    ordersTable["user_id"],
    usersTable["id"]
);
Join join = new Join(ordersTable, usersTable, joinCondition, JoinType.Inner);

Query viewQuery = new Query();
viewQuery.AddFromItem(join);
viewQuery.AddSelectItem(usersTable["username"]);
viewQuery.AddSelectItem(ordersTable["total"]);
viewQuery.AddSelectItem(ordersTable["id"]);

userOrdersView.Query = 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
    manager.LoadSchema("my_schema");
    Schema schema = manager.GetSchema("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
    }
}

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.

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

Equals(View)

Indicates whether the current object is equal to another object of the same type.

ValidateColumnDataType(string, DataType)