<model name="MnomMaterialized" type="PHYSICAL">
		<property name="importer.useFullSchemaName" value="false"/>
		<property name="query-timeout" value="600000"/>

		<source name="MnomMaterializedView" translator-name="mysql-override" connection-jndi-name="java:/mnomDs"/>
	</model>
	
	<model name="NumberingPlanCsvData">
		<source name="numberingPlanCsv-connector" translator-name="file" connection-jndi-name="java:/numberingPlanCsvDs"/>
	</model>	

	<model name="NumberingPlan" type="VIRTUAL">
		<metadata type="DDL"><![CDATA[

				CREATE VIEW numbering_plan (
					id integer PRIMARY KEY,
					global_title varchar(20)
				)
				OPTIONS(
					MATERIALIZED 'TRUE',
					UPDATABLE 'TRUE',
					MATERIALIZED_TABLE 'MnomMaterialized.numbering_plan_cache',
					"teiid_rel:MATVIEW_TTL" 86400000,
					"teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',
					"teiid_rel:MATVIEW_LOADNUMBER_COLUMN" 'LoadNumber',
					"teiid_rel:MATVIEW_STATUS_TABLE" 'MnomMaterialized.status'
				)
				AS
				SELECT	ROW_NUMBER() OVER (ORDER BY cns) as id,
						cns
					FROM (EXEC NumberingPlanCsvData.getTextFiles('NumberingPlan.csv')) AS f,
					TEXTTABLE(f.file COLUMNS cns string DELIMITER ';' SKIP 1) AS A;
						  
			]]>
		</metadata>
	</model>
			
	<model name="NumberingPlanRaw" type="VIRTUAL">
		<metadata type="DDL">
			<![CDATA[

				CREATE VIEW numbering_plan_raw (
					id integer PRIMARY KEY,
					global_title varchar(20)
				)
				OPTIONS(
					MATERIALIZED 'TRUE',
					UPDATABLE 'FALSE',
					MATERIALIZED_TABLE 'MnomMaterialized.numbering_plan_raw_cache',
					"teiid_rel:MATVIEW_TTL" 86400000,
					"teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',
					"teiid_rel:MATVIEW_LOADNUMBER_COLUMN" 'LoadNumber',
					"teiid_rel:MATVIEW_STATUS_TABLE" 'MnomMaterialized.status'
				)
				AS
				SELECT 	ROW_NUMBER() OVER (ORDER BY network) as id,
						global_title
				FROM numbering_plan np;			
				
			]]>
		</metadata>
	</model>
</model>	

CREATE TABLE status (
    VDBName VARCHAR(50) NOT NULL,
    VDBVersion VARCHAR(50) 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 BIGINT,
    Updated TIMESTAMP NOT NULL,
    LoadNumber BIGINT NOT NULL,
    NodeName varchar(25) not null,
    StaleCount BIGINT,
    PRIMARY KEY (VDBName , VDBVersion , SchemaName , Name)
);

CREATE TABLE numbering_plan_cache (
	id integer,
	global_title varchar(20),
    LoadNumber BIGINT,
	PRIMARY KEY(id)
);