[JBoss JIRA] (TEIID-5763) GENERATED_KEY returns NULL if used in INSTEAD OF INSERT Triggers in DDL files
by Steven Hawkins (Jira)
[ https://issues.jboss.org/browse/TEIID-5763?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-5763:
---------------------------------------
> Does it always represent the key value of the table on which the last insert was done?
It represents whatever the auto increment value for the view is, so v1.k in your example above - regardless of how many logical inserts you actually perform for that row of v1.
> GENERATED_KEY returns NULL if used in INSTEAD OF INSERT Triggers in DDL files
> -----------------------------------------------------------------------------
>
> Key: TEIID-5763
> URL: https://issues.jboss.org/browse/TEIID-5763
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 12.2
> Reporter: Christoph John
> Assignee: Steven Hawkins
> Priority: Blocker
>
> The issue arrised in:
> https://developer.jboss.org/message/989700#989700
> Following sceanarios are given:
> Variant 1:
> Table Product(id), primary key = autoincrement;
> Table QuicklyAddedProduct, primary key is foreign key on Product.id
> Table Diary_SRC
> View Diary on Diary_SRC
> INSTEAD OF INSERT Trigger on Diary should :
> -create new record on Product,
> -get autoincremented Product.id of new record,
> - create new QuicklyAddedProduct with returned Product.id as primary key
> - create new Diary record with QuicklyAddedProduct
> {
> INSERT a new record on Product;
> idProduct = CONVERT(GENERATED_KEY('idProduct'),long); // fails
> }
> Variant 2:
> INSTEAD OF INSERT trigger on Diary should :
> - create new QuicklyAddedProduct
> - add Quickly added prodcut to Diary
> additionally a INSTEAD OF trigger on QuicklyAddedProduct exists which:
> - creates new Product record
> - uses returned GENERATED_KEY(Product.id) to add record on QuicklyAddedProduct with Product.id as primary key
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
5 years, 6 months
[JBoss JIRA] (TEIID-5763) GENERATED_KEY returns NULL if used in INSTEAD OF INSERT Triggers in DDL files
by Christoph John (Jira)
[ https://issues.jboss.org/browse/TEIID-5763?page=com.atlassian.jira.plugin... ]
Christoph John edited comment on TEIID-5763 at 6/17/19 5:32 PM:
----------------------------------------------------------------
Hello Steven,
great. But I do have one further question regarding the key variable. Does it always represent the key value of the table on which the last insert was done? I mean, in case I have multiple inserts in an insert trigger on different tables, is then the key variable always associated with the record on the table of the last insert?
for example:
{code:java}
CREATE FOREIGN TABLE tableA(i integer ,k integer not null auto_increment primary key);
CREATE FOREIGN TABLE tableB(i integer ,k integer not null auto_increment primary key)
CREATE FOREIGN TABLE tableC(i integer ,k integer not null auto_increment primary key)
create view v1 (i integer, k integer not null auto_increment primary key)
OPTIONS (UPDATABLE true) as
select x, y from tableC;
create trigger on v1 instead of insert as
for each row begin atomic
insert into tableA (i) values (new.i);
--key.k references tableA.k?
insert into tableB (i) values (new.i);
--key.k references tableB.k?
insert into tableC (i) values(new.i);
--key.k references tableC.k?
{code}
was (Author: cjohn001):
Hello Steven,
great. But I do have one further question regarding the key variable. Does it always represent the key value of the table on which the last insert was done? I mean, in case I have multiple inserts in an insert trigger on different tables, is then the key variable always associated with the record on the table of the last insert?
for example:
CREATE FOREIGN TABLE tableA(i integer ,k integer not null auto_increment primary key);
CREATE FOREIGN TABLE tableB(i integer ,k integer not null auto_increment primary key)
CREATE FOREIGN TABLE tableC(i integer ,k integer not null auto_increment primary key)
create view v1 (i integer, k integer not null auto_increment primary key)
OPTIONS (UPDATABLE true) as
select x, y from tableC;
create trigger on v1 instead of insert as
for each row begin atomic
insert into tableA (i) values (new.i);
--key.k references tableA.k?
insert into tableB (i) values (new.i);
--key.k references tableB.k?
insert into tableC (i) values(new.i);
--key.k references tableC.k?
> GENERATED_KEY returns NULL if used in INSTEAD OF INSERT Triggers in DDL files
> -----------------------------------------------------------------------------
>
> Key: TEIID-5763
> URL: https://issues.jboss.org/browse/TEIID-5763
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 12.2
> Reporter: Christoph John
> Assignee: Steven Hawkins
> Priority: Blocker
>
> The issue arrised in:
> https://developer.jboss.org/message/989700#989700
> Following sceanarios are given:
> Variant 1:
> Table Product(id), primary key = autoincrement;
> Table QuicklyAddedProduct, primary key is foreign key on Product.id
> Table Diary_SRC
> View Diary on Diary_SRC
> INSTEAD OF INSERT Trigger on Diary should :
> -create new record on Product,
> -get autoincremented Product.id of new record,
> - create new QuicklyAddedProduct with returned Product.id as primary key
> - create new Diary record with QuicklyAddedProduct
> {
> INSERT a new record on Product;
> idProduct = CONVERT(GENERATED_KEY('idProduct'),long); // fails
> }
> Variant 2:
> INSTEAD OF INSERT trigger on Diary should :
> - create new QuicklyAddedProduct
> - add Quickly added prodcut to Diary
> additionally a INSTEAD OF trigger on QuicklyAddedProduct exists which:
> - creates new Product record
> - uses returned GENERATED_KEY(Product.id) to add record on QuicklyAddedProduct with Product.id as primary key
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
5 years, 6 months
[JBoss JIRA] (TEIID-5763) GENERATED_KEY returns NULL if used in INSTEAD OF INSERT Triggers in DDL files
by Christoph John (Jira)
[ https://issues.jboss.org/browse/TEIID-5763?page=com.atlassian.jira.plugin... ]
Christoph John edited comment on TEIID-5763 at 6/17/19 5:31 PM:
----------------------------------------------------------------
Hello Steven,
great. But I do have one further question regarding the key variable. Does it always represent the key value of the table on which the last insert was done? I mean, in case I have multiple inserts in an insert trigger on different tables, is then the key variable always associated with the record on the table of the last insert?
for example:
CREATE FOREIGN TABLE tableA(i integer ,k integer not null auto_increment primary key);
CREATE FOREIGN TABLE tableB(i integer ,k integer not null auto_increment primary key)
CREATE FOREIGN TABLE tableC(i integer ,k integer not null auto_increment primary key)
create view v1 (i integer, k integer not null auto_increment primary key)
OPTIONS (UPDATABLE true) as
select x, y from tableC;
create trigger on v1 instead of insert as
for each row begin atomic
insert into tableA (i) values (new.i);
--key.k references tableA.k?
insert into tableB (i) values (new.i);
--key.k references tableB.k?
insert into tableC (i) values(new.i);
--key.k references tableC.k?
was (Author: cjohn001):
Hello Steven,
great. But I do have one further question regarding the key variable. Does it always represent the key value of the table on which the last insert was done? I mean, in case I have multiple inserts in an insert trigger on different tables, is then the key variable always associated with the record on the table of the last insert?
for example:
CREATE FOREIGN TABLE tableA(integer ,k integer not null auto_increment primary key);
CREATE FOREIGN TABLE tableB(integer ,k integer not null auto_increment primary key)
CREATE FOREIGN TABLE tableC(integer ,k integer not null auto_increment primary key)
create view v1 (i integer, k integer not null auto_increment primary key)
OPTIONS (UPDATABLE true) as
select x, y from tableC;
create trigger on v1 instead of insert as
for each row begin atomic
insert into tableA (x) values (new.i);
--key.k references tableA.k?
insert into tableB (x) values (new.i);
--key.k references tableB.k?
insert into tableC (x) values(new.i);
--key.k references tableC.k?
> GENERATED_KEY returns NULL if used in INSTEAD OF INSERT Triggers in DDL files
> -----------------------------------------------------------------------------
>
> Key: TEIID-5763
> URL: https://issues.jboss.org/browse/TEIID-5763
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 12.2
> Reporter: Christoph John
> Assignee: Steven Hawkins
> Priority: Blocker
>
> The issue arrised in:
> https://developer.jboss.org/message/989700#989700
> Following sceanarios are given:
> Variant 1:
> Table Product(id), primary key = autoincrement;
> Table QuicklyAddedProduct, primary key is foreign key on Product.id
> Table Diary_SRC
> View Diary on Diary_SRC
> INSTEAD OF INSERT Trigger on Diary should :
> -create new record on Product,
> -get autoincremented Product.id of new record,
> - create new QuicklyAddedProduct with returned Product.id as primary key
> - create new Diary record with QuicklyAddedProduct
> {
> INSERT a new record on Product;
> idProduct = CONVERT(GENERATED_KEY('idProduct'),long); // fails
> }
> Variant 2:
> INSTEAD OF INSERT trigger on Diary should :
> - create new QuicklyAddedProduct
> - add Quickly added prodcut to Diary
> additionally a INSTEAD OF trigger on QuicklyAddedProduct exists which:
> - creates new Product record
> - uses returned GENERATED_KEY(Product.id) to add record on QuicklyAddedProduct with Product.id as primary key
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
5 years, 6 months
[JBoss JIRA] (TEIID-5763) GENERATED_KEY returns NULL if used in INSTEAD OF INSERT Triggers in DDL files
by Christoph John (Jira)
[ https://issues.jboss.org/browse/TEIID-5763?page=com.atlassian.jira.plugin... ]
Christoph John commented on TEIID-5763:
---------------------------------------
Hello Steven,
great. But I do have one further question regarding the key variable. Does it always represent the key value of the table on which the last insert was done? I mean, in case I have multiple inserts in an insert trigger on different tables, is then the key variable always associated with the record on the table of the last insert?
for example:
CREATE FOREIGN TABLE tableA(integer ,k integer not null auto_increment primary key);
CREATE FOREIGN TABLE tableB(integer ,k integer not null auto_increment primary key)
CREATE FOREIGN TABLE tableC(integer ,k integer not null auto_increment primary key)
create view v1 (i integer, k integer not null auto_increment primary key)
OPTIONS (UPDATABLE true) as
select x, y from tableC;
create trigger on v1 instead of insert as
for each row begin atomic
insert into tableA (x) values (new.i);
--key.k references tableA.k?
insert into tableB (x) values (new.i);
--key.k references tableB.k?
insert into tableC (x) values(new.i);
--key.k references tableC.k?
> GENERATED_KEY returns NULL if used in INSTEAD OF INSERT Triggers in DDL files
> -----------------------------------------------------------------------------
>
> Key: TEIID-5763
> URL: https://issues.jboss.org/browse/TEIID-5763
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 12.2
> Reporter: Christoph John
> Assignee: Steven Hawkins
> Priority: Blocker
>
> The issue arrised in:
> https://developer.jboss.org/message/989700#989700
> Following sceanarios are given:
> Variant 1:
> Table Product(id), primary key = autoincrement;
> Table QuicklyAddedProduct, primary key is foreign key on Product.id
> Table Diary_SRC
> View Diary on Diary_SRC
> INSTEAD OF INSERT Trigger on Diary should :
> -create new record on Product,
> -get autoincremented Product.id of new record,
> - create new QuicklyAddedProduct with returned Product.id as primary key
> - create new Diary record with QuicklyAddedProduct
> {
> INSERT a new record on Product;
> idProduct = CONVERT(GENERATED_KEY('idProduct'),long); // fails
> }
> Variant 2:
> INSTEAD OF INSERT trigger on Diary should :
> - create new QuicklyAddedProduct
> - add Quickly added prodcut to Diary
> additionally a INSTEAD OF trigger on QuicklyAddedProduct exists which:
> - creates new Product record
> - uses returned GENERATED_KEY(Product.id) to add record on QuicklyAddedProduct with Product.id as primary key
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
5 years, 6 months
[JBoss JIRA] (TEIIDSB-108) Add support for swagger/openapi sources
by Steven Hawkins (Jira)
Steven Hawkins created TEIIDSB-108:
--------------------------------------
Summary: Add support for swagger/openapi sources
Key: TEIIDSB-108
URL: https://issues.jboss.org/browse/TEIIDSB-108
Project: Teiid Spring Boot
Issue Type: Feature Request
Reporter: Steven Hawkins
Fix For: 1.3.0
There should be source support for swagger/openapi. If we don't want swagger 1 support, then this can just add support for the openapi translator.
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
5 years, 6 months
[JBoss JIRA] (TEIID-5769) Provide a conversion from string to json
by Steven Hawkins (Jira)
[ https://issues.jboss.org/browse/TEIID-5769?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-5769.
-----------------------------------
Resolution: Done
Added via a new transform that reflectively calls jsonparse. That is a bit of a hack, but easier than introducing a new approach to conversion functions - for example like pg we could associate conversion function names via the datatype. However there are quite a few assumptions built into the code about the datatypemanager transformation framework that make that more difficult.
> Provide a conversion from string to json
> ----------------------------------------
>
> Key: TEIID-5769
> URL: https://issues.jboss.org/browse/TEIID-5769
> Project: Teiid
> Issue Type: Sub-task
> Components: Query Engine
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Priority: Major
> Fix For: 12.3
>
>
> For convenience there should be a conversion of string to json. This is complicated as we don't want to add too much into common-core, so we may need some reflective loading.
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
5 years, 6 months