[teiid-issues] [JBoss JIRA] (TEIID-5397) Make RETURN_GENERATED_KEYS work with views

[Steven Hawkins (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-5397?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13609971#comment-13609971 ] 

Steven Hawkins commented on TEIID-5397:
---------------------------------------

>  I think it is very uncommon and I did not find any information about it in docs.

Oracle also allows constraints on views, but has a more verbose syntax which allows for the constraints to be enforced or disabled.  We'll make sure the dos mention more about constraints on views.

It looks like another change would be to allow for the view attribute types to be inferred from the query expression.  We're just reusing the parsing routine from create table, but we should just allow:

create view v (a, b, c) as ...

> Make RETURN_GENERATED_KEYS work with views
> ------------------------------------------
>
>                 Key: TEIID-5397
>                 URL: https://issues.jboss.org/browse/TEIID-5397
>             Project: Teiid
>          Issue Type: Feature Request
>    Affects Versions: 10.2.1
>            Reporter: Lukáš Svačina
>            Assignee: Steven Hawkins
>             Fix For: 11.1
>
>
> *+VDB:+*
> {code}
> <vdb name="_GENERATED_form_shoes" version="2">
>     <model name="_INTERNAL_internalModel" type="PHYSICAL">
>         <source name="internal_postgresql" translator-name="postgresql" connection-jndi-name="java:/internal"/>
>     </model>
>     <model name="GEN_view" type="VIRTUAL">
>         <metadata type="DDL">
>             <![CDATA[ CREATE VIEW "_view_workflow_data" OPTIONS (UPDATABLE 'true') 
>                          AS SELECT "public"."form_shoes_2"."size" AS "size", "public"."form_shoes_2"."model" AS "model", 
>                          "public"."form_shoes_2"."id" AS "id" FROM "public"."form_shoes_2" ]]>
>         </metadata>
>     </model>
> </vdb>
> {code}
> *+TABLE:+*
> name: form_shoes_2
> columns: id (SERIAL) | size (INTEGER *NULLABLE*) | model (VARCHAR *NULLABLE*)
> *+PROBLEM:+*
> Connected into VDB using JDBC like:
> {code:java}
> final PreparedStatement statement = c.prepareStatement(...INSERT..., Statement.RETURN_GENERATED_KEYS);
> statement.executeUpdate();
> final ResultSet generatedKeys = statement.getGeneratedKeys();
> {code}
> *generatedKeys is empty if:*
> # INSERT INTO "form_shoes_2" ( "model" ) VALUES ( 'adidas x1' ) ...... e.g. not all columns are enumerated ... if so, you can provide NULL values to optional columns and generated keys WORK!
> # INSERT INTO "_view_workflow_data" (id, name, size) VALUES (42, 'adidas x2', 12 ) ....... e.g. insering into view (1:1, no joins involved) even when all columns ARE enumerated ... probably no way how to get generated keys here?
> *+QUESTIONS:+*
> # How to get last_insert_id() when inserting into foreign table/views (with/without joins)?
> # How to get updated rows (UPDATE ... RETURNING *)? At least primary keys of affected rows?
> Thanks for fixing/adding this functionality.



--
This message was sent by Atlassian JIRA
(v7.5.0#75005)