What is the best way to assign primary keys to views for OData

[sanjeev.gour]

Hi-

I am trying the expose the VDB as REST using OData. It works but I need advise for cases where I don't have primary keys. There are two cases-

1. There is a table in the database with a primary key column, this table is directly available in the VDB and it can accessed through OData. - No issues.
2. There is a table in the database with a primary key and there is a view in the VDB around this table with no primary key defined. There are issues around this-
   1. If there is no primary key on the view,it is not accessible through OData.
   2. If the same primary column in the database table is marked as primary key in the VDB, I get an error as "Primary already defined for the view". It happens for DB2 but worked fine for H2.
   3. If I add a new column to the view as select UUID() as ID, columns from the table....the view becomes accessible through OData.

I am looking for some advise about the best practice for defining the primary keys for almost every table that I need to access.

Is defining a view on every table with the additional UUID column a good option, possibly alter view in a central location? Or there are better ways to handle this case.

I am using Teiid 8.7.

Thanks.

Sanjeev.

[rareddy]

2.2 Should not be an issue, this may be something source specific as you are suggesting. Can you provide more details on this? stack trace etc.

For automatic key generation you can use

1. ROW_NUMBER [1] see example here [2]
2. Look under Oracle section on how to use database specific sequences [3] or "Sequence Native Query" at [4]

[1] BNF for SQL Grammar - Teiid 8.11 (draft) - Project Documentation Editor

[2] ROW_NUMBER function

[3] https://docs.jboss.org/author/display/TEIID/JDBC+Translator

[4] DDL Metadata - Teiid 8.11 (draft) - Project Documentation Editor

[sanjeev.gour]

The primary key already defined on this view was due to mistake I made in the view, I had mentioned two separate columns as primary key columns.

For the automatic key generation, the row_number seems Oracle specific, is there something that Teiid supports so that it can be done centrally?

[rareddy]

ROW_NUMBER is also supported by Teiid, I gave relevant links above. See example here Integrate Amazon SimpleDB with Teiid

[sanjeev.gour]

Thanks Ramesh, that should work for me, sorry, I misread your comment.

I am running into another issue now. I am generating the metadata dynamically and the primary keys as follows-

            List<String> pKeyColumnNames = (List<String>) pKeysMap.get(objectName);

            if(!pKeyColumnNames.isEmpty()){

                metadataFactory.addPrimaryKey("primaryKey",pKeyColumnNames , objectTable);

            }

When I query the data from Squirrel it works fine but when I access it through the REST url, it results in an error. There are two things to note-

1. When I use the url http://localhost:8080/odata/VDB.1/objectName Teiid adds the ORDER BY clause with primary key columns which I don't want.
2. The object has a lot of columns, so the column alias go into two digit like c_1, c_2...c_23.....and forms in incorrect query like the following-

     SELECT ...from .. ORDER BY "c_0", "c_1", "c_2", "c_3", "c_2"3, "c_3"5 - If you notice the quotes are improper. This seems like a bug.

Thanks.

[shawkins]

1) That behavior is effectively mandated by OData.  It requires that results are given with a predictable ordering which is typically taken to be the primary key.

2) You'd have to log that as an issue.  I don't recall the OData layer doing any particular string manipulation of the order by column names, so that seems odd.

[sanjeev.gour]

Thanks Steve.

Opened as issue https://issues.jboss.org/browse/TEIID-3401.