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