How can we manually update ModelData diagram(.xmi)

[rangag]

Hi Ramesh,

I could see that, we can correlate the columns by linking them with mouse, which inturn updates the .xmi file of course vice-versa.

My requirement is to build the relations from text file, could please provide the standard way of doing it

Thanks

Ranganath.

[blafond]

Teiid Designer does have an importer wizard (Import > Teiid Designer > Metadata from Text Files on file system) that has a Relational Tables option.

This feature, however, does not yet include the ability to read PK/FK/UC rows and create your relationships.

The DDL import feature (Import > Teiid Designer > DDL into Relational Model) does have this ability.

The example below represents a simple PK/FK relationship in DDL. Importing this DDL should create the structure & relationships you want.

CREATE TABLE AUTHPOLICIES
(
  POLICYUID    NUMERIC(10) NOT NULL PRIMARY KEY,
  DESCRIPTION  VARCHAR(250),
  POLICYNAME   VARCHAR(250) NOT NULL
);

CREATE TABLE AUTHPRINCIPALS
(
  PRINCIPALTYPE  NUMERIC(10) NOT NULL,
  PRINCIPALNAME  VARCHAR(255) NOT NULL,
  POLICYUID      NUMERIC(10) NOT NULL CONSTRAINT FK_ATHPLCY_PLCYUID REFERENCES AUTHPOLICIES (POLICYUID) ,
  GRANTOR        VARCHAR(255) NOT NULL,
  CONSTRAINT PK_AUTHPOLICYPRINCIPALS UNIQUE (PRINCIPALNAME, POLICYUID)
);

If your are looking to just "update" your model to add PK/FK info, you can use standard ALTER TABLE statements like shown below.

ALTER TABLE table_name ADD CONSTRAINT pk_name PRIMARY KEY (column_name)

ALTER TABLE table_name ADD CONSTRAINT pk_name PRIMARY KEY (column1_name, column2_name)

ALTER TABLE table_name ADD CONSTRAINT fk_name FOREIGN KEY (ref_col_name) REFERENCES ref_table_name(ref_table_column_name)

[rareddy]

Ranganath,

Your question is little ambiguous.  I do not completely understand what you are trying to do. If you are trying to automate some parts of VDB creation using the Teiid Designer, Barry's suggestion is spot on. Teiid Designer supports "importers" to create source and view tables from text files. However, you would need to build the VDB manually using the models created with above process.

Teiid also supports what is called as "Dynamic VDB". Here the VDB is simple text file, however using this scenario, it still does not give you ability to define the "view" layer. You can access the source layers and integrate data between them, but not define a abstraction layer. In future versions we will support this feature. This request has been recorded here.

https://jira.jboss.org/browse/TEIID-1328

Ramesh..

[rangag]

Hi Barry LaFord,

My requirement is to build logical relations between  the tables in teiid layer not on actual database, though we can build PK  / FK using ALTER statements, but it's a legacy database on which we  can't make alterations on DB levels, probably it will take couple of  years for that.. so we just want go head with PK / FK relations on Teiid  layer in future versions, we would alter the table..

To access the database we d't need the table relation as we do with nested queries or similar kind of this...

For  this I want to update the logical relations in *-xmi file where in  we  have PK / FK relations.. I guess we need not to change the VDB for   this, as we just tell the relation to teiid.based on this relation from teiid we build the relations between tables in databse  in later versions .

Thanks

Ranganath.

[rareddy]

Ranganath,

My requirement is to build logical relations between  the tables in teiid layer not on actual database,

What you are asking is to build view layer in the Teiid. So, now tell us the usecase why is that you need to build this layer in using a text file. Are you automating something? and/or is the script generated from some other process?

As Barry mentioned above, you can use importer wizard (Import > Teiid Designer > Metadata from Text Files on file system) to create a view layer, check the drop down boxes for more options. However this requires the text file be specified in a custom format that is understood by the importer. See the importer wizard for sample format. Obviously this needs human intervention to run the wizard and create a view.

Also when he talked about PK/FK, we are not saying that they will be created in underlying DB, it is in the Teiid source or view layer. Teiid does NOT issue DDL commands to source systems. Also when you talk about changing PK/FK that means you are changing view model in Teiid, i.e. changing a VDB.

Hope this helps.