diff options
Diffstat (limited to 'storage/connect/mysql-test/connect/t/xml.test')
-rw-r--r-- | storage/connect/mysql-test/connect/t/xml.test | 321 |
1 files changed, 321 insertions, 0 deletions
diff --git a/storage/connect/mysql-test/connect/t/xml.test b/storage/connect/mysql-test/connect/t/xml.test new file mode 100644 index 00000000..e837ec79 --- /dev/null +++ b/storage/connect/mysql-test/connect/t/xml.test @@ -0,0 +1,321 @@ +--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); +--replace_regex /.*iso-8859-1.*/warning about characters outside of iso-8859-1/ +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 |