EVOLUTION-MANAGER

Edit File: xml.test

--source windows.inc

let $MYSQLD_DATADIR= `select @@datadir`;

SET NAMES utf8;

--vertical_results

--copy_file $MTR_SUITE_DIR/std_data/xsample.xml $MYSQLD_DATADIR/test/xsample.xml
--copy_file $MTR_SUITE_DIR/std_data/latin1.xml $MYSQLD_DATADIR/test/latin1.xml
--copy_file $MTR_SUITE_DIR/std_data/cp1251.xml $MYSQLD_DATADIR/test/cp1251.xml

#--echo $MYSQL_TEST_DIR
#--exec pwd
#SELECT LOAD_FILE('test/xsample.xml');

--echo #
--echo # Testing tag values
--echo #
CREATE TABLE t1
(
  AUTHOR CHAR(50),
  TITLE CHAR(32),
  TRANSLATOR CHAR(40),
  PUBLISHER CHAR(40),
  DATEPUB INT(4)
) ENGINE=CONNECT TABLE_TYPE=XML FILE_NAME='xsample.xml'
  OPTION_LIST='xmlsup=domdoc';
SELECT * FROM t1;
DROP TABLE t1;

--echo #
--echo # Testing that tag names are case sensitive
--echo #
CREATE TABLE t1
(
  author CHAR(50),
  TITLE CHAR(32),
  TRANSLATOR CHAR(40),
  PUBLISHER CHAR(40),
  DATEPUB INT(4)
) ENGINE=CONNECT TABLE_TYPE=XML FILE_NAME='xsample.xml'
  OPTION_LIST='xmlsup=domdoc';
SELECT * FROM t1;
DROP TABLE t1;

--echo #
--echo # Testing attribute values
--echo #
CREATE TABLE t1 (
  ISBN CHAR(15),
  LANG CHAR(2),
  SUBJECT CHAR(32)
) ENGINE=CONNECT TABLE_TYPE=XML FILE_NAME='xsample.xml'
  OPTION_LIST='Coltype=@,xmlsup=domdoc';
SELECT * FROM t1;
DROP TABLE t1;

--echo #
--echo # Testing that attribute names are case sensitive
--echo #
CREATE TABLE t1 (
  isbn CHAR(15),
  LANG CHAR(2),
  SUBJECT CHAR(32)
) ENGINE=CONNECT TABLE_TYPE=XML FILE_NAME='xsample.xml'
  OPTION_LIST='Coltype=@,xmlsup=domdoc';
SELECT * FROM t1;
DROP TABLE t1;

--echo #
--echo # Testing mixed tag and attribute values
--echo #
CREATE TABLE t1 (
  ISBN CHAR(15) FIELD_FORMAT='@',
  LANG CHAR(2)  FIELD_FORMAT='@',
  SUBJECT CHAR(32) FIELD_FORMAT='@',
  AUTHOR CHAR(50),
  TITLE CHAR(32),
  TRANSLATOR CHAR(40),
  PUBLISHER CHAR(40),
  DATEPUB INT(4)
) ENGINE=CONNECT TABLE_TYPE=XML FILE_NAME='xsample.xml'
  TABNAME='BIBLIO' OPTION_LIST='rownode=BOOK'
  OPTION_LIST='xmlsup=domdoc';
SELECT * FROM t1;
DROP TABLE t1;

--echo #
--echo # Testing INSERT on mixed tag and attribute values
--echo #
--copy_file $MTR_SUITE_DIR/std_data/xsample.xml $MYSQLD_DATADIR/test/xsample2.xml
--chmod 0644 $MYSQLD_DATADIR/test/xsample2.xml
CREATE TABLE t1 (
  ISBN CHAR(15) FIELD_FORMAT='@',
  LANG CHAR(2)  FIELD_FORMAT='@',
  SUBJECT CHAR(32) FIELD_FORMAT='@',
  AUTHOR CHAR(50),
  TITLE CHAR(32),
  TRANSLATOR CHAR(40),
  PUBLISHER CHAR(40),
  DATEPUB INT(4)
) ENGINE=CONNECT TABLE_TYPE=XML FILE_NAME='xsample2.xml'
  TABNAME='BIBLIO'
  OPTION_LIST='rownode=BOOK,xmlsup=domdoc';
INSERT INTO t1 (ISBN, LANG, SUBJECT, AUTHOR, TITLE, PUBLISHEr, DATEPUB)
VALUES('9782212090529','fr','général','Alain Michard',
'XML, Langage et Applications','Eyrolles Paris',1998);
SELECT * FROM t1;
--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
--eval SELECT LOAD_FILE('$MYSQLD_DATADIR/test/xsample2.xml') AS xml
DROP TABLE t1;
--remove_file $MYSQLD_DATADIR/test/xsample2.xml

--echo #
--echo # Testing XPath
--echo #
CREATE TABLE t1 (
  isbn       CHAR(15) FIELD_FORMAT='@ISBN',
  language   CHAR(2)  FIELD_FORMAT='@LANG',
  subject    CHAR(32) FIELD_FORMAT='@SUBJECT',
  authorfn   CHAR(20) FIELD_FORMAT='AUTHOR/FIRSTNAME',
  authorln   CHAR(20) FIELD_FORMAT='AUTHOR/LASTNAME',
  title      CHAR(32) FIELD_FORMAT='TITLE',
  translated CHAR(32) FIELD_FORMAT='TRANSLATOR/@PREFIX',
  tranfn     CHAR(20) FIELD_FORMAT='TRANSLATOR/FIRSTNAME',
  tranln     CHAR(20) FIELD_FORMAT='TRANSLATOR/LASTNAME',
  publisher  CHAR(20) FIELD_FORMAT='PUBLISHER/NAME',
  location   CHAR(20) FIELD_FORMAT='PUBLISHER/PLACE',
  year       INT(4)   FIELD_FORMAT='DATEPUB'
) ENGINE=CONNECT TABLE_TYPE=XML FILE_NAME='xsample.xml'
  TABNAME='BIBLIO' OPTION_LIST='rownode=BOOK,skipnull=1,xmlsup=domdoc';
SELECT * FROM t1;
SELECT isbn, title, translated, tranfn, tranln, location FROM t1
WHERE translated <> '';
DROP TABLE t1;

#
# TODO: Connect.pdf says nodes with variable depth are not supported
#
#--echo #
#--echo # Relative paths are not supported
#--echo #
#CREATE TABLE t1 (
#  authorfn   CHAR(20) FIELD_FORMAT='//FIRSTNAME',
#  authorln   CHAR(20) FIELD_FORMAT='//LASTNAME'
#) ENGINE=CONNECT TABLE_TYPE=XML FILE_NAME='xsample.xml'
#  TABNAME='BIBLIO' OPTION_LIST='rownode=BOOK,skipnull=1';
#SELECT * FROM t1;
#DROP TABLE t1;

#
# TODO: Connect.pdf says absolute paths are not supported
#
#--echo #
#--echo # Absolute path is not supported
#--echo #
#CREATE TABLE t1 (
#  authorfn   CHAR(20) FIELD_FORMAT='/BIBLIO/BOOK/AUTHOR/FIRSTNAME',
#  authorln   CHAR(20) FIELD_FORMAT='/BIBLIO/BOOK/AUTHOR/LASTNAME'
#) ENGINE=CONNECT TABLE_TYPE=XML FILE_NAME='xsample.xml'
#  TABNAME='BIBLIO' OPTION_LIST='rownode=BOOK,skipnull=1';
#SELECT * FROM t1;
#DROP TABLE t1;

--echo #
--echo # Testing that XPath is case sensitive
--echo #
CREATE TABLE t1
(
  isbn       CHAR(15) FIELD_FORMAT='@isbn'
) ENGINE=CONNECT TABLE_TYPE=XML FILE_NAME='xsample.xml'
  TABNAME='BIBLIO' OPTION_LIST='rownode=BOOK,skipnull=1,xmlsup=domdoc';
SELECT * FROM t1;
DROP TABLE t1;

--echo #
--echo # Testing character sets
--echo #

--error ER_UNKNOWN_ERROR
CREATE TABLE t1
(
  c CHAR(16)
) ENGINE=CONNECT TABLE_TYPE=XML FILE_NAME='latin1.xml'
  OPTION_LIST='xmlsup=domdoc'
  DATA_CHARSET=latin1;

CREATE TABLE t1
(
  c CHAR(16)
) ENGINE=CONNECT TABLE_TYPE=XML FILE_NAME='latin1.xml'
  OPTION_LIST='xmlsup=domdoc'
  DATA_CHARSET=utf8;
SHOW CREATE TABLE t1;
SELECT c, HEX(c) FROM t1;
DROP TABLE t1;

CREATE TABLE t1
(
  c CHAR(16)
) ENGINE=CONNECT TABLE_TYPE=XML FILE_NAME='latin1.xml'
  OPTION_LIST='xmlsup=domdoc';
SELECT c, HEX(c) FROM t1;
DROP TABLE t1;

CREATE TABLE t1
(
  c CHAR(16) CHARACTER SET utf8
) ENGINE=CONNECT TABLE_TYPE=XML FILE_NAME='latin1.xml'
  OPTION_LIST='xmlsup=domdoc';
SELECT c, HEX(c) FROM t1;
DROP TABLE t1;

--echo #
--echo # Conversion from latin1 to cp1251 produces a warning.
--echo # Question marks are returned.
--echo #
CREATE TABLE t1
(
  c CHAR(16) CHARACTER SET cp1251
) ENGINE=CONNECT TABLE_TYPE=XML FILE_NAME='latin1.xml'
  OPTION_LIST='xmlsup=domdoc';
SELECT c, HEX(c) FROM t1;
DROP TABLE t1;

--echo #
--echo # Testing Cyrillic
--echo #
#CREATE TABLE t1
#(
#  c CHAR(16) CHARACTER SET utf8
#) ENGINE=CONNECT TABLE_TYPE=XML FILE_NAME='cp1251.xml'
#  OPTION_LIST='xmlsup=domdoc,rownode=b';
#SELECT * FROM t1;
#INSERT INTO t1 VALUES ('ИКЛМН');
#SELECT c, HEX(c) FROM t1;
#DROP TABLE t1;
#CREATE TABLE t1
#(
#  c CHAR(16) CHARACTER SET cp1251
#) ENGINE=CONNECT TABLE_TYPE=XML FILE_NAME='cp1251.xml'
#  OPTION_LIST='xmlsup=domdoc,rownode=b';
#SELECT * FROM t1;
#INSERT INTO t1 VALUES ('ОПРСТ');
#SELECT c, HEX(c) FROM t1;
#DROP TABLE t1;

--echo #
--echo # Testing that the underlying file is created with a proper Encoding
--echo #
CREATE TABLE t1 (node VARCHAR(50))
  CHARACTER SET latin1
  ENGINE=connect TABLE_TYPE=xml FILE_NAME='t1.xml'
  OPTION_LIST='xmlsup=domdoc,rownode=line,encoding=utf-8';
INSERT INTO t1 VALUES (_latin1 0xC0C1C2C3);
SELECT node, hex(node) FROM t1;
DROP TABLE t1;
#--chmod 0777 $MYSQLD_DATADIR/test/t1.xml
--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
--eval SET @a=LOAD_FILE('$MYSQLD_DATADIR/test/t1.xml')
SELECT LEFT(@a,38);
SELECT HEX(EXTRACTVALUE(@a,'/t1/line/node'));
--remove_file $MYSQLD_DATADIR/test/t1.xml

CREATE TABLE t1 (node VARCHAR(50))
  CHARACTER SET latin1
  ENGINE=connect TABLE_TYPE=xml FILE_NAME='t1.xml'
  OPTION_LIST='xmlsup=domdoc,rownode=line,encoding=iso-8859-1';
INSERT INTO t1 VALUES (_latin1 0xC0C1C2C3);
SELECT node, hex(node) FROM t1;
DROP TABLE t1;
#--chmod 0777 $MYSQLD_DATADIR/test/t1.xml
--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
--eval SET @a=LOAD_FILE('$MYSQLD_DATADIR/test/t1.xml')
SELECT LEFT(@a,43);
SELECT HEX(EXTRACTVALUE(@a,'/t1/line/node'));
--remove_file $MYSQLD_DATADIR/test/t1.xml

--echo #
--echo # Testing XML entities
--echo #
CREATE TABLE t1 (node VARCHAR(50))
  CHARACTER SET utf8
  ENGINE=connect TABLE_TYPE=xml FILE_NAME='t1.xml'
  OPTION_LIST='xmlsup=domdoc,rownode=line,encoding=iso-8859-1';
INSERT INTO t1 VALUES (_latin1 0xC0C1C2C3);
INSERT INTO t1 VALUES (_cp1251 0xC0C1C2C3);
INSERT INTO t1 VALUES ('&<>"\'');
SELECT node, hex(node) FROM t1;
DROP TABLE t1;
#--chmod 0777 $MYSQLD_DATADIR/test/t1.xml
--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
--eval SET @a=LOAD_FILE('$MYSQLD_DATADIR/test/t1.xml')
SELECT CAST(@a AS CHAR CHARACTER SET latin1);
--remove_file $MYSQLD_DATADIR/test/t1.xml

#
# Clean up
#
--remove_file $MYSQLD_DATADIR/test/xsample.xml
--remove_file $MYSQLD_DATADIR/test/latin1.xml
--remove_file $MYSQLD_DATADIR/test/cp1251.xml