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.
lac42
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 Authorization and 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.
Follow these steps:
  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 Add Virtual Primary Key window opens.
  4. Complete the following, and then click
    Add Key
    :
    • For each column that represents the unique row identifier for the view, select 
      Key
      .
    • If the underlying column is an autonum (identity) column, 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.
Follow these steps:
  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 that 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 then click 
Delete
.
Invoke Views
Not all databases provide the system-required view composition for view/base table mapping. 
CA Live API Creator
 provides SQL database views as RESTful resource endpoints. These views supply results as JSON. The virtual primary keys that you add to views display in the 
href
 for each row of the view in a GET.
Prerequisites:
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