[teiid-issues] [JBoss JIRA] (TEIID-4121) Enhancing the External Materialization

I want to create a global STATUS table, which save all View's status, no matter where materialized table located, status will always goes to that global STATUS table. It should be better if STATUS table can be created automatically in deployment, that the datasource should be verified in advance, like H2, Mysql, Infinspan.

From user-friendly aspect, I feel not good for configuring lots of SQL scripts and create tables manually each time if I want to use external mat. Especially, different data sources are behave differently, for example, the STATUS table's Column "Cardinality long", in latest MariaDB, the 'long' type will silently change to 'MEDIUMTEXT', so we have to use 'bigint' instead. The same as other truncate table, alter table syntax, etc, different data source also behave differently.

IMO, we should enhance external in this term, made it more automatically, the materialized data source should be verified in advance, during configuring one option can make all things work. If not enhance in this way, at least, the document should be enhanced.

Enhancing the External Materialization -------------------------------------- Key: TEIID-4121 URL: https://issues.jboss.org/browse/TEIID-4121 Project: Teiid Issue Type: Sub-task Components: Query Engine Affects Versions: 9.x Reporter: Kylin Soong Assignee: Steven Hawkins Fix For: 9.0 The intention of move "status" table to physical database is to increase durable and fully control refresh and loading, but it increase the complexity. The "status" table by design should unique for whole VDB, if you look the https://teiid.gitbooks.io/documents/content/caching/External_Materializat..., the table structure: {code:sql} CREATE TABLE status ( VDBName varchar(50) not null, VDBVersion integer not null, SchemaName varchar(50) not null, Name varchar(256) not null, TargetSchemaName varchar(50), TargetName varchar(256) not null, Valid boolean not null, LoadState varchar(25) not null, Cardinality long, Updated timestamp not null, LoadNumber long not null, PRIMARY KEY (VDBName, VDBVersion, SchemaName, Name) ); {code} but currently, one VDB may have multiple "status" table, each view may have it's own "status" table. Further more, we can consider create status table automatically, which like internal, status create once VDB start, and configured in VDB scope. From finishedDeployment logic in MaterializationManager, MATERIALIZED_TABLE be used to determine whether the Mat is internal or external, But we lack the validation in metadata loading, in my previous test, the Internal Mat view configured lots of external view's properties like "status" table, the validation not throw excepton.