| 1 |
# WL#6455 GEOMETRY datatypes support |
| 2 |
# In order to implement GIS indexing in InnoDB, |
| 3 |
# InnoDB needs to support GEOMETRY datatypes, |
| 4 |
# so that InnoDB stores them properly and understands them. |
| 5 |
|
| 6 |
--source include/have_debug.inc |
| 7 |
|
| 8 |
# Avoid CrashReporter popup on Mac |
| 9 |
--source include/not_crashrep.inc |
| 10 |
|
| 11 |
# Turn on the geometry data print. |
| 12 |
SET SESSION debug="+d,row_print_geometry_data"; |
| 13 |
|
| 14 |
# Test GEOMETRY datatype. |
| 15 |
CREATE TABLE t_wl6455 ( i INT, g GEOMETRY NOT NULL) ENGINE=InnoDB; |
| 16 |
SHOW CREATE TABLE t_wl6455; |
| 17 |
|
| 18 |
# Insert Point. |
| 19 |
INSERT INTO t_wl6455 VALUES(1, POINT(1,1)); |
| 20 |
|
| 21 |
INSERT INTO t_wl6455 VALUES(2, POINT(2,2)); |
| 22 |
|
| 23 |
# Insert MultiPoint. |
| 24 |
SET @mp = 'MULTIPOINT(0 0, 20 20, 60 60)'; |
| 25 |
INSERT INTO t_wl6455 VALUES(3, ST_GeomFromText(@mp)); |
| 26 |
|
| 27 |
# Insert LineString. |
| 28 |
INSERT INTO t_wl6455 VALUES(4, LINESTRING(POINT(1,1), POINT(4, 4))); |
| 29 |
|
| 30 |
INSERT INTO t_wl6455 VALUES(5, LINESTRING(POINT(2,2), POINT(5, 5))); |
| 31 |
|
| 32 |
# Insert MultiLineString. |
| 33 |
SET @mls = 'MultiLineString((1 1,2 2,3 3),(4 4,5 5))'; |
| 34 |
INSERT INTO t_wl6455 VALUES(6, ST_GeomFromText(@mls)); |
| 35 |
|
| 36 |
# Insert Polygon. |
| 37 |
SET @poly = 'Polygon((0 0,0 3,3 0,0 0),(1 1,1 2,2 1,1 1))'; |
| 38 |
INSERT INTO t_wl6455 VALUES(7, ST_GeomFromText(@poly)); |
| 39 |
|
| 40 |
# Insert MultiPolygon. |
| 41 |
SET @mpoly = 'MultiPolygon(((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1)))'; |
| 42 |
INSERT INTO t_wl6455 VALUES(8, ST_GeomFromText(@mpoly)); |
| 43 |
|
| 44 |
# Insert GeometryCollection. |
| 45 |
SET @gc = 'GeometryCollection(Point(1 1),LineString(2 2, 3 3))'; |
| 46 |
INSERT INTO t_wl6455 VALUES(9, ST_GeomFromText(@gc)); |
| 47 |
|
| 48 |
# Show result. |
| 49 |
SELECT ST_AsText(g) FROM t_wl6455; |
| 50 |
|
| 51 |
# Test create prefix index. |
| 52 |
--error ER_WRONG_SUB_KEY |
| 53 |
CREATE INDEX i_p ON t_wl6455 (g(10)); |
| 54 |
|
| 55 |
# Select on prefix index. |
| 56 |
--replace_column 10 # |
| 57 |
EXPLAIN SELECT ST_AsText(g) FROM t_wl6455 WHERE g = POINT(2,2); |
| 58 |
SELECT ST_AsText(g) FROM t_wl6455 WHERE g = POINT(2,2); |
| 59 |
|
| 60 |
# Test rollback/commit |
| 61 |
SET AUTOCOMMIT = 0; |
| 62 |
INSERT INTO t_wl6455 VALUES(10, POINT(10,10)); |
| 63 |
SELECT ST_AsText(g) FROM t_wl6455 WHERE g = POINT(10,10); |
| 64 |
ROLLBACK; |
| 65 |
SELECT ST_AsText(g) FROM t_wl6455 WHERE g = POINT(10,10); |
| 66 |
|
| 67 |
INSERT INTO t_wl6455 VALUES(10, POINT(10,10)); |
| 68 |
COMMIT; |
| 69 |
|
| 70 |
INSERT INTO t_wl6455 VALUES(11, POINT(11,11)); |
| 71 |
BEGIN; |
| 72 |
INSERT INTO t_wl6455 VALUES(1, POINT(1,1)); |
| 73 |
|
| 74 |
--source include/kill_and_restart_mysqld.inc |
| 75 |
|
| 76 |
CHECK TABLE t_wl6455; |
| 77 |
SELECT ST_AsText(g) FROM t_wl6455; |
| 78 |
|
| 79 |
# Check information_schema, the mtype of GEOMETRY datatype is 14. |
| 80 |
SELECT sc.name, sc.pos, sc.mtype |
| 81 |
FROM information_schema.innodb_columns sc |
| 82 |
INNER JOIN information_schema.innodb_tables st |
| 83 |
ON sc.TABLE_ID=st.TABLE_ID |
| 84 |
WHERE st.NAME='test/t_wl6455' AND sc.NAME='g'; |
| 85 |
|
| 86 |
# Clean up |
| 87 |
DROP TABLE t_wl6455; |