<?xml version="1.0" encoding="UTF-8"?>
<vdb name="MatViewsVdb" version="1">
	<description>For testing of materialized views</description>

	<model name="Source">
		<source name="Source" translator-name="h2-override"
			connection-jndi-name="java:/mat-views-ds" />
		<metadata type="DDL"><![CDATA[
		
		CREATE FOREIGN TABLE customers (
			id integer,
			name string(4000),
			address string(4000),
			CONSTRAINT PK_ID PRIMARY KEY(id)
		) OPTIONS(NAMEINSOURCE 'customer')
		
		CREATE FOREIGN TABLE orders (
			id integer,
			customer_id integer,
			amount integer,
			CONSTRAINT PK_ID PRIMARY KEY(id),
			CONSTRAINT FK_CUST FOREIGN KEY(customer_id) REFERENCES customers(id)
		) OPTIONS(NAMEINSOURCE 'order')
			]]>
		</metadata>
	</model>
	<model name="ViewModel" type="VIRTUAL">
		<metadata type="DDL"><![CDATA[
        CREATE VIEW internal_short_ttl (
           customer_id integer NOT NULL,
           total_amount integer
        ) OPTIONS (MATERIALIZED 'TRUE',
        	"teiid_rel:MATVIEW_BEFORE_LOAD_SCRIPT" 'execute Source.native(''INSERT INTO check_table(id,before_load) VALUES (''internal_short_ttl'',1) ON DUPLICATE KEY UPDATE before_load=before_load+1;'');',
             "teiid_rel:MATVIEW_AFTER_LOAD_SCRIPT" 'execute Source.native(''INSERT INTO check_table(id,after_load) VALUES (''internal_short_ttl'',1) ON DUPLICATE KEY UPDATE after_load=after_load+1;'')'
        	)
              AS /*+ cache(ttl:100)*/SELECT c.id AS customer_id, CONVERT(SUM(o.amount),biginteger) AS total_amount FROM customers c INNER JOIN orders o ON c.id = o.customer_id GROUP BY c.id;
     ]]>
              	</metadata>
	</model>
	<translator name="h2-override" type="h2">
		<property name="SupportsDirectQueryProcedure" value="true" />
	</translator>
</vdb>