mirror of
https://github.com/MariaDB/server.git
synced 2026-02-23 02:58:40 +01:00
84 lines
2.1 KiB
Text
84 lines
2.1 KiB
Text
#
|
|
# Testing changing table type (not in-place)
|
|
#
|
|
CREATE TABLE t1 (c INT NOT NULL, d CHAR(10) NOT NULL) ENGINE=CONNECT TABLE_TYPE=CSV HEADER=1 QUOTED=1;
|
|
Warnings:
|
|
Warning 1105 No file name. Table will use t1.csv
|
|
INSERT INTO t1 VALUES (1,'One'), (2,'Two'), (3,'Three');
|
|
SELECT * FROM t1;
|
|
c d
|
|
1 One
|
|
2 Two
|
|
3 Three
|
|
# This would fail if the top node name is not specified.
|
|
# This is because the XML top node name defaults to the table name.
|
|
# Sure enough the temporary table name begins with '#' and is rejected by XML.
|
|
# Therefore the top node name must be specified (along with the row nodes name).
|
|
ALTER TABLE t1 TABLE_TYPE=XML TABNAME=t1 OPTION_LIST='xmlsup=domdoc,rownode=row';
|
|
SELECT * FROM t1;
|
|
c d
|
|
1 One
|
|
2 Two
|
|
3 Three
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`c` int(11) NOT NULL,
|
|
`d` char(10) NOT NULL
|
|
) ENGINE=CONNECT DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci `HEADER`=1 `QUOTED`=1 `TABLE_TYPE`=XML `TABNAME`=t1 `OPTION_LIST`='xmlsup=domdoc,rownode=row'
|
|
# Let us see the XML file
|
|
CREATE TABLE t2 (line VARCHAR(100) NOT NULL) ENGINE=CONNECT FILE_NAME='t1.xml';
|
|
Warnings:
|
|
Warning 1105 No table_type. Will be set to DOS
|
|
SELECT * FROM t2;
|
|
line
|
|
<?xml version="1.0" encoding="UTF-8"?>
|
|
<!-- Created by the MariaDB CONNECT Storage Engine-->
|
|
<t1>
|
|
<row>
|
|
<TH>c</TH>
|
|
<TH>d</TH>
|
|
</row>
|
|
<row>
|
|
<c>1</c>
|
|
<d>One</d>
|
|
</row>
|
|
<row>
|
|
<c>2</c>
|
|
<d>Two</d>
|
|
</row>
|
|
<row>
|
|
<c>3</c>
|
|
<d>Three</d>
|
|
</row>
|
|
</t1>
|
|
# NOTE: The first (ignored) row is due to the remaining HEADER=1 option.
|
|
# Testing field option modification
|
|
ALTER TABLE t1 MODIFY d CHAR(10) NOT NULL XPATH='@', HEADER=0;
|
|
SELECT * FROM t1;
|
|
c d
|
|
1 One
|
|
2 Two
|
|
3 Three
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`c` int(11) NOT NULL,
|
|
`d` char(10) NOT NULL `XPATH`='@'
|
|
) ENGINE=CONNECT DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci `QUOTED`=1 `TABLE_TYPE`=XML `TABNAME`=t1 `OPTION_LIST`='xmlsup=domdoc,rownode=row' `HEADER`=0
|
|
SELECT * FROM t2;
|
|
line
|
|
<?xml version="1.0" encoding="UTF-8"?>
|
|
<!-- Created by the MariaDB CONNECT Storage Engine-->
|
|
<t1>
|
|
<row d="One">
|
|
<c>1</c>
|
|
</row>
|
|
<row d="Two">
|
|
<c>2</c>
|
|
</row>
|
|
<row d="Three">
|
|
<c>3</c>
|
|
</row>
|
|
</t1>
|
|
DROP TABLE t1, t2;
|