From 93c885bd36569b5b2bd7c85e497ecf9830e848de Mon Sep 17 00:00:00 2001 From: Alexander Barkov Date: Mon, 12 Oct 2009 11:22:53 +0500 Subject: WL#1397 convert XML -> SQL --- mysql-test/r/loadxml.result | 79 +++++++++++++++++++++++++++++ mysql-test/std_data/loadxml.dat | 64 +++++++++++++++++++++++ mysql-test/std_data/loadxml2.dat | 19 +++++++ mysql-test/t/loadxml.test | 106 +++++++++++++++++++++++++++++++++++++++ 4 files changed, 268 insertions(+) create mode 100644 mysql-test/r/loadxml.result create mode 100644 mysql-test/std_data/loadxml.dat create mode 100644 mysql-test/std_data/loadxml2.dat create mode 100644 mysql-test/t/loadxml.test (limited to 'mysql-test') diff --git a/mysql-test/r/loadxml.result b/mysql-test/r/loadxml.result new file mode 100644 index 00000000000..dc2392e5593 --- /dev/null +++ b/mysql-test/r/loadxml.result @@ -0,0 +1,79 @@ +drop table if exists t1, t2; +create table t1 (a int, b varchar(64)); +-- Load a static XML file +load xml infile '../../std_data/loadxml.dat' into table t1 +rows identified by ''; +select * from t1 order by a; +a b +1 b1 +2 b2 +3 b3 +11 b11 +111 b111 +112 b112 & < > " ' &unknown; -- check entities +212 b212 +213 b213 +214 b214 +215 b215 +216 &bb b; +delete from t1; +-- Load a static XML file with 'IGNORE num ROWS' +load xml infile '../../std_data/loadxml.dat' into table t1 +rows identified by '' ignore 4 rows; +select * from t1 order by a; +a b +111 b111 +112 b112 & < > " ' &unknown; -- check entities +212 b212 +213 b213 +214 b214 +215 b215 +216 &bb b; +-- Check 'mysqldump --xml' + 'LOAD XML' round trip +delete from t1; +load xml infile 'MYSQLTEST_VARDIR/tmp/loadxml-dump.xml' into table t1 rows identified by '';; +select * from t1 order by a; +a b +111 b111 +112 b112 & < > " ' &unknown; -- check entities +212 b212 +213 b213 +214 b214 +215 b215 +216 &bb b; +--Check that default row tag is ' +delete from t1; +load xml infile 'MYSQLTEST_VARDIR/tmp/loadxml-dump.xml' into table t1;; +select * from t1 order by a; +a b +111 b111 +112 b112 & < > " ' &unknown; -- check entities +212 b212 +213 b213 +214 b214 +215 b215 +216 &bb b; +-- Check that 'xml' is not a keyword +select 1 as xml; +xml +1 +create table t2(fl text); +LOAD XML LOCAL INFILE "$MYSQLTEST_VARDIR/tmp/loadxml-dump.xml" INTO TABLE t2 ROWS IDENTIFIED BY '';; +show processlist; +Id User Host db Command Time State Info +2 root localhost test Query 0 NULL show processlist +5 root localhost test Query 3 Reading from net LOAD XML LOCAL INFILE "$MYSQLTEST_VARDIR/tmp/loadxml-dump.xml" INTO TABLE t2 ROWS IDENTIFIED BY '

+ + + + + + + + + + 1 + b1 + + + 2 + b2 + + + 3 + b3 + + + 11 + b11 + + + + + 111 + b111 + + + + 112 + b112 & < > " ' &unknown; -- check entities + + + + + + + + + + + + + + + + + + + + + + + + + diff --git a/mysql-test/std_data/loadxml2.dat b/mysql-test/std_data/loadxml2.dat new file mode 100644 index 00000000000..f0d966cf9b6 --- /dev/null +++ b/mysql-test/std_data/loadxml2.dat @@ -0,0 +1,19 @@ + + + + + + + + + + + + 1 + line1 +line2 +line3 + + + + diff --git a/mysql-test/t/loadxml.test b/mysql-test/t/loadxml.test new file mode 100644 index 00000000000..bb088559ae2 --- /dev/null +++ b/mysql-test/t/loadxml.test @@ -0,0 +1,106 @@ +# +# Tests for "LOAD XML" - a contributed patch from Erik Wetterberg. +# + +# Running the $MYSQL_DUMP tool against an embedded server does not work. +--source include/not_embedded.inc + +--disable_warnings +drop table if exists t1, t2; +--enable_warnings + +create table t1 (a int, b varchar(64)); + + +--echo -- Load a static XML file +load xml infile '../../std_data/loadxml.dat' into table t1 +rows identified by ''; +select * from t1 order by a; +delete from t1; + + +--echo -- Load a static XML file with 'IGNORE num ROWS' +load xml infile '../../std_data/loadxml.dat' into table t1 +rows identified by '' ignore 4 rows; +select * from t1 order by a; + + +--echo -- Check 'mysqldump --xml' + 'LOAD XML' round trip +--exec $MYSQL_DUMP --xml test t1 > "$MYSQLTEST_VARDIR/tmp/loadxml-dump.xml" 2>&1 +delete from t1; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval load xml infile '$MYSQLTEST_VARDIR/tmp/loadxml-dump.xml' into table t1 rows identified by ''; +select * from t1 order by a; + +--echo --Check that default row tag is ' +delete from t1; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval load xml infile '$MYSQLTEST_VARDIR/tmp/loadxml-dump.xml' into table t1; +select * from t1 order by a; + +--echo -- Check that 'xml' is not a keyword +select 1 as xml; + + +# +# Bug #42520 killing load .. infile Assertion failed: ! is_set(), file .\sql_error.cc, line 8 +# + +--disable_query_log +delete from t1; +insert into t1 values (1, '12345678900987654321'), (2, 'asdfghjkl;asdfghjkl;'); +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +--exec $MYSQL_DUMP --xml test t1 > "$MYSQLTEST_VARDIR/tmp/loadxml-dump.xml" 2>&1 +--enable_query_log + +connect (addconroot, localhost, root,,); +connection addconroot; +create table t2(fl text); +--let $PSEUDO_THREAD_ID=`select @@pseudo_thread_id ` + +--send LOAD XML LOCAL INFILE "$MYSQLTEST_VARDIR/tmp/loadxml-dump.xml" INTO TABLE t2 ROWS IDENTIFIED BY ''; + +sleep 3; + + +connection default; +show processlist; + +--disable_query_log +--eval kill $PSEUDO_THREAD_ID +--enable_query_log + +disconnect addconroot; + +# +# Clean up +# +remove_file $MYSQLTEST_VARDIR/tmp/loadxml-dump.xml; +drop table t1; +drop table t2; + +# +# Bug #36750 LOAD XML doesn't understand new line (feed) characters in multi line text fields +# + +create table t1 ( + id int(11) not null, + text text, + primary key (id) +) engine=MyISAM default charset=latin1; +load xml infile '../../std_data/loadxml2.dat' into table t1; +select * from t1; +drop table t1; + -- cgit v1.2.1