###############################################################################
#                                                                             #
# This test is aimed to focus on the properties and attributes of the         #
# Mutator function ST_SWAPXY.                                                 #
#                                                                             #
#                                                                             #
# Creation Date: 2016-08-16                                                   #
# Author : Jens Even Berg Blomsoy                                             #
#                                                                             #
###############################################################################


###############################################################################
# Creating the spatial objects                                                #
###############################################################################

--echo # Creating the spatial objects

CREATE TABLE gis_point (fid INTEGER NOT NULL PRIMARY KEY, g POINT);
CREATE TABLE gis_linestring (fid INTEGER NOT NULL PRIMARY KEY, g LINESTRING);
CREATE TABLE gis_polygon (fid INTEGER NOT NULL PRIMARY KEY, g POLYGON);
CREATE TABLE gis_multi_point (fid INTEGER NOT NULL PRIMARY KEY, g MULTIPOINT);
CREATE TABLE gis_multi_linestring (fid INTEGER NOT NULL PRIMARY KEY,
g MULTILINESTRING);
CREATE TABLE gis_multi_polygon (fid INTEGER NOT NULL PRIMARY KEY,
g MULTIPOLYGON);
CREATE TABLE gis_geometrycollection (fid INTEGER NOT NULL PRIMARY KEY,
g GEOMETRYCOLLECTION);


###############################################################################
# Inserting POINT Geometry values                                             #
###############################################################################

--echo # Inserting POINT Geometry Values

INSERT INTO gis_point VALUES
    (101, POINT(1.1111,-2.2222)),
    (102, POINT(10.0000,20.0000));

INSERT INTO gis_point VALUES
    (103, POINT(1e308, 1e202));

--echo # Displaying the inserted POINT Geometry Data
SELECT fid, ST_ASTEXT(g) FROM gis_point;

###############################################################################
# Inserting LINESTRING Geometry values                                        #
###############################################################################

--echo # Inserting LINESTRING Geometry Values

INSERT INTO gis_linestring VALUES
    (201, ST_LINEFROMTEXT('LINESTRING(0 2,2 4,4 8)'));

INSERT INTO gis_linestring VALUES
    (202, ST_LINESTRINGFROMTEXT('LINESTRING(0 12,12 24,24 48)'));

INSERT INTO gis_linestring VALUES
    (203, LINESTRING(POINT(0,0), POINT(-5,7),
POINT(-10,70)));

--echo # Displaying the inserted LINESTRING Geometry Data
SELECT fid, ST_ASTEXT(g) FROM gis_linestring;

###############################################################################
# Inserting POLYGON Geometry values                                           #
###############################################################################

--echo # Inserting POLYGON Geometry Values

INSERT INTO gis_polygon VALUES
    (301, ST_POLYFROMTEXT('POLYGON((0 0,5 0,5 5, 0 0))'));

INSERT INTO gis_polygon VALUES
    (302, ST_POLYGONFROMTEXT('POLYGON((0 0,10 0,10 10,0 10,0 0),
(2 2,2 4,4 4,4 2,2 2),(6 6,6 8,8 8,8 6,6 6))'));

INSERT INTO gis_polygon VALUES
    (303, POLYGON(LINESTRING(POINT(0,0), POINT(10,0),
POINT(10,10), POINT(0,10), POINT(0,0)), LINESTRING(POINT(4,4), POINT(4,6),
POINT(6,6), POINT(6,4), POINT(4,4))));

--echo # Displaying the inserted POLYGON Geometry Data
SELECT fid, ST_ASTEXT(g) FROM gis_polygon;

###############################################################################
# Inserting MULTIPOINT Geometry values                                        #
###############################################################################

--echo # Inserting MULTIPOINT Geometry Values

INSERT INTO gis_multi_point VALUES
    (401, ST_MPOINTFROMTEXT('MULTIPOINT(0 0,5 5,10 10)'));

INSERT INTO gis_multi_point VALUES
    (402, ST_MULTIPOINTFROMTEXT('MULTIPOINT(1e308 1e308,1e308 -1e308,-1e308
1e308,-1e308 -1e308)'));

INSERT INTO gis_multi_point VALUES
    (403, ST_MPOINTFROMTEXT('MULTIPOINT(1e308
1e308, 1e308 -1e308, -1e308 1e308, -1e308 -1e308, 1e308 1e308)'));

--echo # Displaying the inserted MULTIPOINT Geometry Data
SELECT fid, ST_ASTEXT(g) FROM gis_multi_point;

###############################################################################
# Inserting MULTILINESTRING Geometry values                                   #
###############################################################################

--echo # Inserting MULTILINESTRING Geometry Values

INSERT INTO gis_multi_linestring VALUES
    (501, ST_MLINEFROMTEXT('MULTILINESTRING((0 0,2 2,4 4),(6 6,8 8,10 10))'));

INSERT INTO gis_multi_linestring VALUES
    (502, MULTILINESTRING(LINESTRING(POINT(0,0),
POINT(12,12), POINT(24,24)),
LINESTRING(POINT(36,36), POINT(48,48), POINT(50,50))));

INSERT INTO gis_multi_linestring VALUES
    (503, ST_MULTILINESTRINGFROMTEXT
('MULTILINESTRING((0 0,0 100),(0 0,100 0),(0 0,0 -100),(0 0,-100 0))'));

--echo # Displaying the inserted MULTILINESTRING Geometry Data
SELECT fid, ST_ASTEXT(g) FROM gis_multi_linestring;

###############################################################################
# Inserting MULTIPOLYGON Geometry values                                      #
###############################################################################

--echo # Inserting MULTIPOLGYON Geometry Values

INSERT INTO gis_multi_polygon VALUES
    (601, MULTIPOLYGON(POLYGON(LINESTRING(POINT(0,0),
POINT(5,0), POINT(5,5), POINT(0,5), POINT(0,0))))),
    (602, MULTIPOLYGON(POLYGON(LINESTRING(POINT(0,0),
POINT(10, 0), POINT(10,10), POINT(10, 0), POINT(0,0)),
                                               LINESTRING(POINT(4,4),
                                               POINT(4,6), POINT(6,6),
                                               POINT(6,4), POINT(4,4))))),
    (603, MULTIPOLYGON(POLYGON(LINESTRING(POINT(0,0),
POINT(5,0), POINT(5,5), POINT(0,5), POINT(0,0))),
                                       POLYGON(LINESTRING(POINT(0,0),
                                       POINT(-5, 0), POINT(-5,-5), POINT(0 ,-5),
                                       POINT(0,0)))));

--echo # Displaying the inserted MULTIPOLYGON Geometry Data
SELECT fid, ST_ASTEXT(g) FROM gis_multi_polygon;

###############################################################################
# Inserting GEOMETRYCOLLECTION Geometry values                                #
###############################################################################

INSERT INTO gis_geometrycollection VALUES
    (701, ST_GEOMCOLLFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0))')),
    (702, ST_GEOMCOLLFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0),
    LINESTRING(0 0,10 10))')),
    (703, ST_GEOMCOLLFROMTEXT('GEOMETRYCOLLECTION(POINT(5 5),
    POLYGON((0 0,10 0,10 10,0 10,0 0)))'));

--echo # Displaying the inserted GEOMETRYCOLLECTION Geometry Data
SELECT fid, ST_ASTEXT(g) FROM gis_geometrycollection;

###############################################################################
# Geometry call to ST_SWAPXY()                                                #
###############################################################################

--echo #=======================================================================
--echo # ST_SWAPXY(point)
--echo #=======================================================================

SELECT ST_ASTEXT(ST_SWAPXY(g)) FROM gis_point WHERE fid = 101;
SELECT ST_ASTEXT(ST_SWAPXY(g)) FROM gis_point WHERE fid = 102;
SELECT ST_ASTEXT(ST_SWAPXY(g)) FROM gis_point WHERE fid = 103;

SELECT ST_ASTEXT(ST_SWAPXY(ST_POINTFROMTEXT(
'POINT(1.1111 -2.2222)')));
SELECT ST_ASTEXT(ST_SWAPXY(ST_POINTFROMTEXT(
'POINT(10.0000 20.0000)')));
SELECT ST_ASTEXT(ST_SWAPXY(ST_POINTFROMTEXT(
'POINT(1e308 1e202)')));


--echo #=======================================================================
--echo # ST_SWAPXY(linestring)
--echo #=======================================================================

SELECT ST_ASTEXT(ST_SWAPXY(g)) FROM gis_linestring WHERE fid = 201;
SELECT ST_ASTEXT(ST_SWAPXY(g)) FROM gis_linestring WHERE fid = 202;
SELECT ST_ASTEXT(ST_SWAPXY(g)) FROM gis_linestring WHERE fid = 203;

SELECT ST_ASTEXT(ST_SWAPXY(ST_LINEFROMTEXT('LINESTRING(0 2,2 4,4 8)')));
SELECT ST_ASTEXT(ST_SWAPXY(ST_LINESTRINGFROMTEXT('LINESTRING(0 12,12 24,24 48)')));
SELECT ST_ASTEXT(ST_SWAPXY(LINESTRING(POINT(0,2), POINT(-5,7),
POINT(-10,70))));


--echo #=======================================================================
--echo # ST_SWAPXY(polygon)
--echo #=======================================================================

SELECT ST_ASTEXT(ST_SWAPXY(g)) FROM gis_polygon WHERE fid = 301;
SELECT ST_ASTEXT(ST_SWAPXY(g)) FROM gis_polygon WHERE fid = 302;
SELECT ST_ASTEXT(ST_SWAPXY(g)) FROM gis_polygon WHERE fid = 303;

SELECT ST_ASTEXT(ST_SWAPXY(ST_POLYFROMTEXT('POLYGON((0 0,5 0,5 5, 0 0))')));
SELECT ST_ASTEXT(ST_SWAPXY(ST_POLYGONFROMTEXT('POLYGON((0 0,10 0,10 10,0 10,0 0),
(2 2,2 4,4 4,4 2,2 2),(6 6,6 8,8 8,8 6,6 6))')));
SELECT ST_ASTEXT(ST_SWAPXY(POLYGON(LINESTRING(POINT(0,0), POINT(10,0),
POINT(10,10), POINT(0,10), POINT(0,0)), LINESTRING(POINT(4,4), POINT(4,6),
POINT(6,6), POINT(6,4), POINT(4,4)))));

--echo #=======================================================================
--echo # ST_SWAPXY(multipoint)
--echo #=======================================================================

SELECT ST_ASTEXT(ST_SWAPXY(g)) FROM gis_multi_point WHERE fid = 401;
SELECT ST_ASTEXT(ST_SWAPXY(g)) FROM gis_multi_point WHERE fid = 402;
SELECT ST_ASTEXT(ST_SWAPXY(g)) FROM gis_multi_point WHERE fid = 403;

SELECT ST_ASTEXT(ST_SWAPXY(ST_MPOINTFROMTEXT('MULTIPOINT(0 5,5 10,10 20)')));
SELECT ST_ASTEXT(ST_SWAPXY(ST_MPOINTFROMTEXT('MULTIPOINT(
1e308 1e208,1e308 -1e208,-1e308 1e208,-1e308 -1e208)')));
SELECT ST_ASTEXT(ST_SWAPXY(ST_MPOINTFROMTEXT(
'MULTIPOINT(1e308 1e308,1e308 -1e308,-1e308 1e308,
-1e308 -1e308,1e308 1e308)')));


--echo #=======================================================================
--echo # ST_SWAPXY(multilinestring)
--echo #=======================================================================

SELECT ST_ASTEXT(ST_SWAPXY(g)) FROM gis_multi_linestring WHERE fid = 501;
SELECT ST_ASTEXT(ST_SWAPXY(g)) FROM gis_multi_linestring WHERE fid = 502;
SELECT ST_ASTEXT(ST_SWAPXY(g)) FROM gis_multi_linestring WHERE fid = 503;

SELECT ST_ASTEXT(ST_SWAPXY(ST_MLINEFROMTEXT('MULTILINESTRING((0 0,2 2,4 4),
(6 6,8 8,10 10))')));

SELECT ST_ASTEXT(ST_SWAPXY(MULTILINESTRING(
LINESTRING(POINT(0,0), POINT(12,12), POINT(24,24)), LINESTRING(POINT(36,36),
POINT(48,48), POINT(50,50)))));

SELECT ST_ASTEXT(ST_SWAPXY(
ST_MULTILINESTRINGFROMTEXT('MULTILINESTRING((0 0,0 100),(0 0,100 0),
(0 0,0 -100),(0 0,-100 0))')));


--echo #=======================================================================
--echo # ST_SWAPXY(multipolygon)
--echo #=======================================================================

SELECT ST_ASTEXT(ST_SWAPXY(g)) FROM gis_multi_polygon WHERE fid = 601;
SELECT ST_ASTEXT(ST_SWAPXY(g)) FROM gis_multi_polygon WHERE fid = 602;
SELECT ST_ASTEXT(ST_SWAPXY(g)) FROM gis_multi_polygon WHERE fid = 603;

SELECT ST_ASTEXT(ST_SWAPXY(MULTIPOLYGON(POLYGON(LINESTRING(POINT(0,0),
POINT(5,0), POINT(5,5), POINT(0,5), POINT(0,0))))));

SELECT ST_ASTEXT(ST_SWAPXY(MULTIPOLYGON(POLYGON(LINESTRING(POINT(0,0),
POINT(10,0), POINT(10,10), POINT(0,10), POINT(0,0)), LINESTRING(POINT(4,4),
POINT(4,6), POINT(6,6), POINT(6,4), POINT(4,4))))));

SELECT ST_ASTEXT(ST_SWAPXY(MULTIPOLYGON(POLYGON(LINESTRING(POINT(0,0),
POINT(5,0), POINT(5,5), POINT(0,5), POINT(0,0))),
POLYGON(LINESTRING(POINT(0,0), POINT(-5, 0), POINT(-5,-5), POINT(0, -5),
POINT(0,0))))));


--echo #=======================================================================
--echo # ST_SWAPXY(geometrycollection)
--echo #=======================================================================

SELECT ST_ASTEXT(ST_SWAPXY(g)) FROM gis_geometrycollection WHERE fid = 701;
SELECT ST_ASTEXT(ST_SWAPXY(g)) FROM gis_geometrycollection WHERE fid = 702;
SELECT ST_ASTEXT(ST_SWAPXY(g)) FROM gis_geometrycollection WHERE fid = 703;

SELECT ST_ASTEXT(ST_SWAPXY(ST_GEOMCOLLFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0))')));
SELECT ST_ASTEXT(ST_SWAPXY(ST_GEOMCOLLFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0),
LINESTRING(0 0,10 10))')));
SELECT ST_ASTEXT(ST_SWAPXY(ST_GEOMCOLLFROMTEXT('GEOMETRYCOLLECTION(POINT(5 5),
POLYGON((0 0,10 0,10 10,0 10,0 0)))')));

########################################
# Null value testing                   #
########################################

SELECT ST_ASTEXT(ST_SWAPXY(NULL));

###############################################################################
# Invalid function calls                                                      #
###############################################################################

--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
SELECT ST_ASTEXT(ST_SWAPXY());

--error ER_GIS_INVALID_DATA
SELECT ST_SWAPXY('foobar');

# Point without coordinates
--error ER_GIS_INVALID_DATA
SELECT ST_SWAPXY(x'0101000000');

# Linestring with zero points
--error ER_GIS_INVALID_DATA
SELECT ST_SWAPXY(x'010200000000000000');

--echo # Final cleanup
DROP TABLE gis_point;
DROP TABLE gis_linestring;
DROP TABLE gis_polygon;
DROP TABLE gis_multi_point;
DROP TABLE gis_multi_linestring;
DROP TABLE gis_multi_polygon;
DROP TABLE gis_geometrycollection;

--echo #
--echo # WL#11096 Don't do Cartesian computations on geographic geometries
--echo #

--error ER_SRS_NOT_FOUND
DO ST_SWAPXY(x'0A000000010100000000000000000000000000000000000000');