Manage Views

You can access complex query processing that the underlying database exposes using views. In some enterprise databases, access to base tables is restricted. You can access data from these base tables using views.
lac40
You can access complex query processing that the underlying database exposes using views. In some enterprise databases, access to base tables is restricted. You can access data from these base tables using views.
CA Live 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 tables, 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.
  • Add to-parent and to-children relationships between views and/or tables.
    For more information about how to add relationships, see Manage Relationships.
In this article:
2
Manage Virtual Primary Keys in Views
You can add, update, and delete virtual primary keys to views. With views having virtual primary keys defined, you can:
  • Associate the view as a resource to a function, and then call the function with access to the 
    row
     variable of the view.
    For more information about how to create functions that are associated to views, see Manage Functions.
  • Create a rule for the view.
    For more information about how to create rules, see Rule Types.
  • Access and invoke the view using Data Explorer.
    For more information about how to access view endpoints, see Data Explorer.
  • 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. In the Create section, click 
    Schema
    .
    The Tables tab displays by default.
  2. Click the 
    Views
     tab.
    Your view endpoints are displayed on the Views page.
  3. Click 
    Add
     in the
    Keys
    listbox.
    The Create Virtual Primary Key window opens.
  4. 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 page, 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 page, 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