Debian Codegraph @

mysql-test/suite/innodb_gis/t/types.test

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