/* -------------------------------------------------------------------------------- */

/* Nastaveni oddelovacu cisel */
alter session set nls_numeric_characters='.,';

/* Vypnuti funkcionality Recycle Bin */
alter session set recyclebin=off;

/* -------------------------------------------------------------------------------- */

/* Registrace vychoziho schematu */
BEGIN
  DBMS_XMLSCHEMA.registerSchema(
    SCHEMAURL=>'http://www.ksi.mff.cuni.cz/~svoboda/courses/171-NPRG039/labs/Lab-02-Oracle/schema.xsd',
    SCHEMADOC=>HTTPURIType('http://www.ksi.mff.cuni.cz/~svoboda/courses/171-NPRG039/labs/Lab-02-Oracle/schema.xsd'),
    LOCAL=>TRUE,
    GENTYPES=>TRUE,
    GENTABLES=>FALSE);
END;
/

/* -------------------------------------------------------------------------------- */

/* Vytvoreni tabulky pro objednavky */
CREATE TABLE purchaseorder OF XMLTYPE
xmlschema "http://www.ksi.mff.cuni.cz/~svoboda/courses/171-NPRG039/labs/Lab-02-Oracle/schema.xsd"
element "purchaseOrder";

/* Trigger pro plnou kontrolu validity */
CREATE TRIGGER emp_trig BEFORE INSERT OR UPDATE ON purchaseorder FOR EACH ROW
DECLARE 
  newxml XMLTYPE;
BEGIN
  newxml := :new.OBJECT_VALUE;
  XMLTYPE.schemavalidate(newxml);
END;
/

/* Vlozeni validnich dat */
INSERT INTO purchaseorder VALUES (HTTPURIType('http://www.ksi.mff.cuni.cz/~svoboda/courses/171-NPRG039/labs/Lab-02-Oracle/data.xml').getXML());

/* Vlozeni nevalidnich dat */
INSERT INTO purchaseorder VALUES (HTTPURIType('http://www.ksi.mff.cuni.cz/~svoboda/courses/171-NPRG039/labs/Lab-02-Oracle/dataNew1.xml').getXML());

/* -------------------------------------------------------------------------------- */

/* Copy-based evoluce schematu */
BEGIN
  DBMS_XMLSCHEMA.copyEvolve(
    xdb$string_list_t('http://www.ksi.mff.cuni.cz/~svoboda/courses/171-NPRG039/labs/Lab-02-Oracle/schema.xsd'),
    XMLSequenceType(HTTPURIType('http://www.ksi.mff.cuni.cz/~svoboda/courses/171-NPRG039/labs/Lab-02-Oracle/schemaNew1.xsd').getXML()),
    XMLSequenceType(HTTPURIType('http://www.ksi.mff.cuni.cz/~svoboda/courses/171-NPRG039/labs/Lab-02-Oracle/trans.xsl').getXML()));
END;
/

/* Vlozeni nove validnich dat */
INSERT INTO purchaseorder VALUES (HTTPURIType('http://www.ksi.mff.cuni.cz/~svoboda/courses/171-NPRG039/labs/Lab-02-Oracle/dataNew1.xml').getXML());

/* -------------------------------------------------------------------------------- */

/* In-place evoluce schematu - aktualni schema je nejdrive nutne odstranit a puvodni znovu registrovat */
DECLARE
  difffile XMLTYPE;
BEGIN
  SELECT XMLDIFF(
           HTTPURIType('http://www.ksi.mff.cuni.cz/~svoboda/courses/171-NPRG039/labs/Lab-02-Oracle/schema.xsd').getXML(),
           HTTPURIType('http://www.ksi.mff.cuni.cz/~svoboda/courses/171-NPRG039/labs/Lab-02-Oracle/schemaNew2.xsd').getXML())
  INTO difffile FROM DUAL;
  
  DBMS_XMLSCHEMA.inPlaceEvolve(
    'http://www.ksi.mff.cuni.cz/~svoboda/courses/171-NPRG039/labs/Lab-02-Oracle/schema.xsd',
    difffile
  );
END;
/

/* Vlozeni nove validnich dat */
INSERT INTO purchaseorder VALUES (HTTPURIType('http://www.ksi.mff.cuni.cz/~svoboda/courses/171-NPRG039/labs/Lab-02-Oracle/dataNew2.xml').getXML());

/* -------------------------------------------------------------------------------- */
