Creating a View in a Database

You can exploit the principle of embedded views (queries) in a database, therefore not having to rewrite long, complex queries in a configuration file.

To create a view in a database

1. Log on to your database with a database editor/client.

   Note: Make sure you have the appropriate read/write rights or administrator privileges for the database.

2. On the command line of the client, create your view. If you are using an access-like database editor, create a view only using the SELECT and FROM parts of the queries provided below.

   • CES_Orders

     Security permissions for documents should be defined using one of the methods explained in Indexing a Database Using a View.

     CREATE view "CES_Orders" AS
     SELECT
     'http://www.coveo.com/Orders/details.aspx?Id=' + convert(varchar, Orders.OrderID) AS Uri,
     'http://www.coveo.com' AS ClickableUri,
     Orders.OrderID AS Title,
     'OrderDate: ' + convert(varchar, OrderDate) + CHAR(10) + 'RequiredDate: ' + convert(varchar, RequiredDate) + CHAR(10) + 'ShippedDate: ' + convert(varchar, ShippedDate) + CHAR(10) + 'Shipped via: ' + Shippers.CompanyName AS Body,
     'Order' AS Type,
     OrderDate,
     RequiredDate,
     ShippedDate,
     Shippers.CompanyName AS Shipper,
     FirstName + ' ' + LastName AS sysAuthor
     FROM
      Orders
     INNER JOIN Shippers ON Orders.ShipVia = Shippers.ShipperID
     INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID
     INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
     
     CES_Products  
     

   • CES_Products

     Permissions are defined directly as fields inside the view (allowed for all documents is coveo/hford).

     CREATE view "CES_Products" AS
     SELECT
     'http://www.coveo.com/Products/details.aspx?Id=' + convert(varchar, Products.ProductID) AS Uri,
      'http://www.coveo.com' AS ClickableUri,
     ProductName + ' (' + convert(varchar, Products.ProductID )+ ')' AS Title,
     'Name: ' + ProductName + CHAR(10) + 'Category: ' + CategoryName + CHAR(10) + 'Supplier: ' + Suppliers.CompanyName AS Body,
     'Product' AS Type,
     ProductName AS Product,
     Discontinued,
     UnitPrice,
     QuantityPerUnit,
     CategoryName AS Category,
     Suppliers.CompanyName AS Supplier
     'hford' AS AllowedWindowsName,
     'coveo' AS AllowedWindowsServer
     FROM
     Categories
     INNER JOIN Products ON Categories.CategoryID = Products.CategoryID
     INNER JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID