I just read about the DDL support to create a vdb (Developing a Virtual Database · Teiid Documentation ). I think this will be a great feature. When 9.2 CR1 is out I'll try to convert our current VDB file to a DDL version.
I have some questions about this:
1) I always configure my jdbc connections in the standalone-teiid.xml. The jdbc drivers are added as wildfly modules. e.g.
<xa-datasource jndi-name="java:jboss/datasources/CosaraDS" pool-name="Cosara" enabled="true" use-java-context="true"> <xa-datasource-property name="URL"> jdbc:mysql://... </xa-datasource-property> <driver>mysql</driver> <security> <user-name>xxx</user-name> <password>xxx</password> </security> </xa-datasource>
<drivers> <driver name="mysql" module="com.mysql.jdbc"> <driver-class>com.mysql.jdbc.Driver</driver-class> <xa-datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</xa-datasource-class> </driver> </driver>
I see that in the CREATE SERVER statement I need to specify the jdbc driver file name. Can this also be the driver name? As here just "mysql"?
2) We have 2 source databases that mostly contain the same schema's and tables. In the past I've always changed the table names to reflect from whch source they are comming.
e.g. prod_P_GeneralData that refers to Patient.dbo.P_GeneralData in database1 (that we call production) and wh_P_GeneralData that refers to Patient.dbo.P_generalData in database 2 (that we call warehouse).
I could possibly now just create two schema's in the vbd named production and warehouse but then our old scripts would not work anymore.
Would it be possible to add something as a table prefix when importing the tables?
3) Is there a use statement to select a certain virtual schema when writing views/procedures? I assume that views and/or procedures that call each other must always be specified before the view/procedure that calls them?
-- this will probably throw an error since b does not exist yet? CREATE VIEW a .... as select 1 from b CREATE VIEW b .... as select 1 from table_x
4) Can we combine both DDL and XML definitions? I can imagine that in some cases it might be easier to do some things in the Teiid designer (like setting roles and privileges). But then again do some other things (like creating view and procedures) in a DDL format. Can these DDL scripts then be added to a vbd file?
5) Can the DDL be written over multiple files? I can image that this would make things more maintainable. Is there a way to tell teiid in which order to deploy the DDL scripts?
6) Is there a way to tell the importer when executing "IMPORT FOREIGN SCHEMA public FROM SERVER ..." that only a selection of the tables should be imported?
7) I think this is not in the scope of this feature but would it be possible in the future to create physical tables (alternative to temporary foreign tables) in some existing databases? We use this quite often to store some big temporary tables. Maybe in a DDL script there could be native create statement code followed by teiid import table statements?