To effectively utilize OData with CData Virtuality, it's essential to meet specific requirements, including proper user permissions and the presence of primary keys in virtual views. This guide outlines these prerequisites and provides a sample SQL procedure to update existing views by defining a primary key column.
Assigning user permissions
Access to OData services is secured using HTTP Basic Authentication. Only users assigned the odata-role can access these services. You can assign roles using UI or directly by running a SQL query:
CALL SYSADMIN.addUserRole(user_name => 'test', role_name => 'odata-role');;
Keep in mind that the user still needs regular permissions assigned to one of his roles in order to access the views or tables whether the protocol used is OData or any of the others.
Adding Primary keys to existing views
Creating a virtual view with a primary key in CData Virtuality is simple and works exactly as you would expect it:
CREATE VIEW views.view2 (a integer, b integer, PRIMARY KEY(a)) AS SELECT 1 a, 2 b;;
The problem arises when you have to modify a large number of existing views but you don’t feel like copy/pasting view definitions all day long is the way to go. Luckily, CData Virtuality offers a powerful and versatile toolset so you can always find a way to automate and make your tasks simpler. Here is an example procedure that will take an existing view and modify it by declaring a given column as a primary key.
CREATE PROCEDURE views.addPkToView(
IN schemaName STRING NOT NULL OPTIONS (ANNOTATION 'Virtual schema name'),
IN viewName STRING NOT NULL OPTIONS (ANNOTATION 'View name'),
IN PK STRING NOT NULL OPTIONS (ANNOTATION 'Primary key column'),
IN executeImmediately BOOLEAN DEFAULT 'true' OPTIONS (ANNOTATION 'Alter the view immediately (default) or show the SQL only.')
) RETURNS (result STRING NOT NULL) OPTIONS (
ANNOTATION 'Alter the given virtual view by adding the specified column as a primary key'
) AS
BEGIN
DECLARE STRING command;
DECLARE STRING definition;
DECLARE STRING tail;
definition = SELECT view_definition FROM "INFORMATION_SCHEMA"."views" WHERE table_schema = schemaName AND table_name = viewName;
tail = SUBSTRING(definition, LOCATE('AS', definition));
command = 'ALTER VIEW ' || schemaName || '.' || viewName || '(';
IF ( LOCATE('PRIMARY KEY', UCASE(definition)) > 0 ) ERROR 'PRIMARY KEY already present in View definition';
LOOP ON (
SELECT "column_name", "udt_name"
FROM "INFORMATION_SCHEMA.columns"
WHERE "table_schema" = schemaName AND "table_name" = viewName
ORDER BY "ordinal_position" ASC
) AS cols
BEGIN
command = command || '"' || cols.column_name || '" ' || cols.udt_name || ', ';
END
command = command || ' PRIMARY KEY(' || '"' || PK || '")) ' || tail || ';;';
IF (executeImmediately) EXECUTE IMMEDIATE command;
SELECT command;
END;;
You can run the script as follows, passing the parameters:
schemaName: Name of the virtual schema containing the view
viewName: Name of the view you wish to alter
PK: Name of the view column that is to become a Primary key
executeImmediately: If set to false, will not alter the view but will return the corresponding ‘ALTER VIEW’ SQL statement.
CALL VIEWS.addPkToView (
"schemaName" => 'myschema' /* Mandatory: Virtual schema name */,
"viewName" => 'myview' /* Mandatory: View name */,
"PK" => 'myKeyColumn' /* Mandatory: Primary key column */,
"executeImmediately" => TRUE /* Optional: Alter the view immediately (default) or show the SQL only. */
);
Important Considerations
- Unique Identifier: The column set as the primary key (id in this case) must have unique values for each row.
- Data Integrity: Before defining a primary key, verify that the data does not contain duplicates in the key column.
By following the above steps, you can configure CData Virtuality to expose your data through OData services securely and efficiently.
