Manage Views

You can access complex query processing that the underlying database exposes using views. In some enterprise databases, base table access are restricted and views are often times the only way you can access data from these tables. API Creator discovers the views defined in your database by reading the schema and makes them available as endpoints. To access a view beyond a GET request, just as you can access table endpoints, you must add virtual primary keys to the view in API Creator.
lac32
You can access complex query processing that the underlying database exposes using views. In some enterprise databases, base table access are restricted and views are often times the only way you can access data from these tables. API Creator discovers the views defined in your database by reading the schema and makes them available as endpoints. To access a view beyond a GET request, just as you can access table endpoints, you must add virtual primary keys to the view in API Creator.
You can define security and control access to the level of individual tables and views within your database based on your security requirements.
For more information about how to assign security controls to views, see Role-Based Endpoint Access.
With views having virtual primary keys defined, you can:
  • Create a function, associate the view as a resource to the function, and call the function with access to the row variable of the view.
    For more information about functions, see Manage Functions.
  • Create a rule for the view.
    For more information about the rules you can create for entities, see Rule Types.
  • Access and invoke the view using Data Explorer.
    For more information about how to access a view endpoint, see Data Explorer.
In this article:
2
Manage Virtual Primary Keys in Views
You can add, update, and delete virtual primary keys to views. Virtual primary keys are required if you want to:
  • Invoke POST/PUT/DELETE requests on views.
  • Call functions on tables, views, or resources.
Add Virtual Primary Keys to Views
You can select one or more columns in the view to use in a defined virtual key. The virtual primary keys you add to views in API Creator are virtual keys.
  1. Under the Create menu, click 
    Schema
    ,
    Views
    .
    Your view endpoints are displayed on the Views tab.
  2. Click 
    Add
     in the
    Keys
    listbox.
    The Create Virtual Primary Key window opens.
  3. Complete the following and then click
    Add Key
    :
    • Select the columns that represent the unique row identifier for the view.
    • If the underlying column is an ident with autonumbering, select 
      Autonum
      .
The virtual primary key is added to the view.
Update the Column Used as Virtual Primary Key for a View
You can change the columns that are used as the virtual primary key for the view.
Prerequisite:
You have added a virtual primary key to the view.
  1. On the Views tab, select the virtual primary key you want to change and click 
    Update
    .
    The Update Virtual Primary Key window opens.
  2. Change the column that is used as the virtual primary key for the view and then click
    Update
    .
The column selection used as the virtual primary key for the view is changed.
Delete Virtual Primary Keys from Views
Prerequisite:
 The virtual primary key you want to delete has been added to the view.
On the Views tab, select the virtual primary key that you want to delete and click 
Delete
.
Invoke Views
Not all databases provide the system-required view composition for view/base table mapping. API Creator provides SQL database views as RESTful resource endpoints. These views supply results as JSON. The virtual primary keys you add to views display in the 
href
 for each row of the view in a GET.
Prerequisites:
  • You have added a virtual primary key to the view.
  • You have permissions to invoke views.
    For more information about how to assign permissions to roles to invoke views, see Role-Based Endpoint Access
You can invoke GET requests on views. If your database supports it, you can also invoke POST/PUT/DELETE requests on views.
Your ability to POST/PUT/DELETE requests on the view is also dependent on the complexity of the view definition and whether your database supports view updates.
If you are having issues invoking RESTful requests on a view, see your database's documentation.
You can invoke views using the following named filters and sort views:
  • sysorder
     and 
    userorder
    .
    For more information about named sorts, see Structured Sorts.
  • sysfilter
     and 
    userfilter
    .
    For more information about named filters, see Structured Filters.
You can also supply pagination and supply the optimistic locking value (or override this check by specifying the 
checksum
value).
For more information about pagination, see Pagination.
If you are connected to a Microsoft SQL Server database, you cannot insert into a view with a virtual primary key added to a column that has the
Identity
property set. Ensure that this view does not include columns that have this property set.
Example
For the view
demo:LineItemJoinProduct
with
LineItemId
 defined as the virtual primary key, you can invoke a GET request in the REST Lab:
http://localhost:8080/rest/default/demo/v1/demo:LineItemJoinProduct/1