Is there any way to add new constraints on foreign table after vdb deployment?

[praksah]

Is there any way to add new constraints on foreign table after vdb deployment?

[shawkins]

Not currently.  Can you provide the full scenario of what you are looking to do?  Would it suffice to be able to use additional ddl metadata at the vdb load time to issue an "alter table"?

[praksah]

I have situation like; I have deployed VDB for mysql database, I am able to execute sql queries on mysql table.(here I used a driver connection.)

My one of the table say "customer" have only primary constraint on physical table on "Name" column, I have another table "customerInfo" from which I need to pull data for customer. Here I have option like add foreign key on customer table to refer primary of " customerInfo " table. But I don't want to add any new constraint on any table as I do not have access.

So before executing a data insertion sql on customer table; I need to add one more constraint as foreign key of Teiid Virtual data table.(foraign table)

Could please let me know; With Teiid can I add any constraints of virtual table and use for my insertion?

<?xml version="1.0" encoding="UTF-8" standalone="no"?>

<vdb name="mySqlNonXA_METADATA" version="1">

<description/>

<model name="mySqlNonXA_METADATA">

<source connection-jndi-name="java:/MySqlDS" name="mySqlNonXA_METADATA" translator-name="mysql"/>

<metadata type="DDL"><![CDATA[

ALTER FOREIGN TABLE CUSTOMER (

ADD CONSTRAINT CID FOREIGN KEY(CUST_ID) REFERENCES CUSTOMERINFO(CUST_ID)

)

]]></metadata>

</model>

</vdb>

[rareddy]

Prakash,

Currently "ALTER TABLE CUSOTER [ADD|DROP] CONSTRAINT .." is not available currently as individual statement, only in the larger definition of the TABLE You can log feature request for this.

Ramesh..

[praksah]

Hi Ramesh,

Instead of adding/dropping a constraint with ALTER statement I tried below way, I have table as "META" which does not have any constraints on physical table, I deployed a below VDB

<?xml version="1.0" encoding="UTF-8" standalone="no"?>

<vdb name="mySqlNonXA_METADATA_2" version="2">

<description/>

<model name="mySqlNonXA_METADATA">

<source connection-jndi-name="java:/MySqlDS" name="mySqlNonXA_METADATA" translator-name="mysql"/>

<metadata type="DDL"><![CDATA[

CREATE FOREIGN TABLE "meta" (

  id long NOT NULL OPTIONS (ANNOTATION '', NAMEINSOURCE '`id`', NATIVE_TYPE 'INT UNSIGNED'),

  "value" string(45) NOT NULL DEFAULT '' OPTIONS (ANNOTATION '', NAMEINSOURCE '`value`', NATIVE_TYPE 'VARCHAR'),

CONSTRAINT "PRIMARY" PRIMARY KEY(id)

) OPTIONS (ANNOTATION '', NAMEINSOURCE '`meta`', UPDATABLE TRUE);

]]></metadata>

</model>

<model name="mySqlNonXA">

<source connection-jndi-name="java:/MySqlDS" name="mySqlNonXA" translator-name="mysql"/>

</model>

</vdb>

When I retrieved schema for above two models I get

1. For Model mySqlNonXA ( client.getSchema("mySqlNonXA_METADATA_2", 2, "mySqlNonXA", null, null); )

CREATE FOREIGN TABLE "aakash_test.meta" (

  id long OPTIONS (ANNOTATION '', NAMEINSOURCE '`id`', NATIVE_TYPE 'INT UNSIGNED'),

  "value" string(45) NOT NULL DEFAULT '' OPTIONS (ANNOTATION '', NAMEINSOURCE '`value`', NATIVE_TYPE 'VARCHAR')

) OPTIONS (ANNOTATION '', NAMEINSOURCE '`aakash_test`.`meta`', UPDATABLE TRUE);

2. For Model mySqlNonXA_METADATA (client.getSchema("mySqlNonXA_METADATA_2", 2, "mySqlNonXA_METADATA", null, null);  )

CREATE FOREIGN TABLE meta (

  id long NOT NULL OPTIONS (ANNOTATION '', NAMEINSOURCE '`id`', NATIVE_TYPE 'INT UNSIGNED'),

  "value" string(45) NOT NULL DEFAULT '' OPTIONS (ANNOTATION '', NAMEINSOURCE '`value`', NATIVE_TYPE 'VARCHAR'),

CONSTRAINT "PRIMARY" PRIMARY KEY(id)

) OPTIONS (ANNOTATION '', NAMEINSOURCE '`meta`', UPDATABLE TRUE);

In above schema, For mySqlNonXA_METADATA.meta table primary key constraint is present that is apply on foreign table.  But when I tried to insert records in meta table, I am able to insert a duplicate value in meta.id column as physical table does not have primary constraint. That means even though we add any new constraint in metadata, it always refer a constraint of physical table only.

Is there any way to add new local constraints/relations on virtual database table that actually not present on physical database table?

Please guide me

[rareddy]

The way you added is not modifying the schema coming from source RDBMS, but replace it your own version of it, which is fine. In this case you would have to define all columns, not just missing ones.

>Is there any way to add new local constraints/relations on virtual database table that actually not present on physical database table?

You would need to extend the current translator to add in a check to make sure the specified PK does not exist before the insert goes though. The caveat is you need to make sure that Teiid is only application who is doing the inserting of records, otherwise other applications will continue to insert duplicates.

Ramesh..

[praksah]

Ramesh,

Meta table is having only two columns, In above comment you said "but replace it your own version of it, " if it replace it my version (from vdb) Will it consider at the time of DML?

Actually Teiid is using Virtual table and If I added new constraints on virtual table and that constraint is not present on physical table. such constraint (constraints which added through VDB) is not consider while perform DML statements,

in above case(in my previous reply), I added primary constraint on meta table and expecting that duplicate Id should not inserted in meta table. I used a connection as

String url = "jdbc:teiid:" + vdb + "@mm://" + host + ":" + port + ";showplan=on";

Class.forName("org.teiid.jdbc.TeiidDriver");

return DriverManager.getConnection(url, "user", pwd);

I am not expecting the the new constraint is added on source RDBMS, but at least it consider at the time of performing DML like in insert, it should not allow to insert a duplicate id in meta table.  but this thing is not happen and it allow me insert a duplicate id. that means constraints which i added through VDB file(metadata tag) at the time of VDB deployment are not getting used in DML.

Please guide me

[rareddy]

Will it consider at the time of DML?

No, it is not. When you add to the VDB it is always at deployment time. I thought we already established this fact with my first comment on thread. The rest is how to do at deployment time, then reload (redeploy) VDB to simulate the dynamic nature.

Yes, we understand your requirement, this is captured at [TEIID-2578] add/remove schema elements - JBoss Issue Tracker

if you want to help contribute, that would be great.

[praksah]

Thanks Ramesh for your Quick Reply

[praksah]

Hi Ramesh,

As you mentioned above, If I add new constraint in VDB metadata that not present on physical table, it is not consider at the time of DML.

Could you please confirm as, same case apply for foreign temporary table as well. I tried below thing

1. Deploy below  VDB which has model for mySql database

<?xml version="1.0" encoding="UTF-8" standalone="no"?>

<vdb name="mySqlVdb" version="2">

<description/>

<model name="mySql">

<source connection-jndi-name="java:/MySqlDS" name="mySql" translator-name="mysql"/>

</model>

</vdb>

2. Schema of the test table as

CREATE FOREIGN TABLE "test1" (

  id long OPTIONS (ANNOTATION '', NAMEINSOURCE '`id`', NATIVE_TYPE 'INT'),

  rollNo long OPTIONS (ANNOTATION '', NAMEINSOURCE '`rollNo`', NATIVE_TYPE 'INT'),

  pin long OPTIONS (ANNOTATION '', NAMEINSOURCE '`pin`', NATIVE_TYPE 'INT')

) OPTIONS (ANNOTATION '', NAMEINSOURCE '`test1`', UPDATABLE TRUE);

3. In java code created connection,

4. Execute below queries with the connection object. Here I created temporary table run time.

execute(connection,

  "CREATE FOREIGN TEMPORARY TABLE test11 ( "

  + "id long OPTIONS (ANNOTATION '', NAMEINSOURCE '`id`', NATIVE_TYPE 'INT'),"

  + "rollNo long OPTIONS (ANNOTATION '', NAMEINSOURCE '`rollNo`', NATIVE_TYPE 'INT'),"

  + "pin long OPTIONS (ANNOTATION '', NAMEINSOURCE '`pin`', NATIVE_TYPE 'INT'), "

  + "CONSTRAINT test1_PK PRIMARY KEY(id) "

  + ") OPTIONS (ANNOTATION '', NAMEINSOURCE '`test1`', UPDATABLE TRUE) on mySql");

execute(connection, "INSERT INTO test11 VALUES (9,6,7)");

execute(connection, "INSERT INTO test11 VALUES (9,6,7)");

execute(connection, "SELECT * FROM test1");

I am created test11 temporary table which linked with the "test1' table from mysql database. When I inserted any row in "test11" it inserted into test1 table. 

here I added primary key constraint on test11 FOREIGN TEMPORARY TABLE and expecting duplicate should not allow, but this thing not happened, it allow me to insert duplicate.

Please guide

[rareddy]

Teiid does not enforce referential constraints in its layer no matter it is in virtual view or physical view. Only source systems do. However, as I mentioned before if one wants to amend the current JDBC translator that code can be added to check.