Debian Codegraph @

mysql-test/suite/memcached/include/sdi_create_alter.inc

raw
1 --echo # Case 1. Test with CREATE TABLES
2 CREATE TABLE t1(c1 VARCHAR(32),
3 c2 VARCHAR(1024),
4 c3 TINYBLOB,
5 c4 BLOB,
6 c5 MEDIUMBLOB,
7 c6 LONGBLOB,
8 c7 TEXT,
9 c8 INT,
10 c9 FLOAT,
11 c10 DOUBLE,
12 primary key(c1),
13 KEY idx1(c4(20)),
14 UNIQUE KEY idx2(c6(50)))
15 CHARSET latin1 ENGINE = INNODB;
16
17 perl;
18 use strict;
19 use warnings;
20 my $dest_dir = $ENV{'MYSQLTEST_VARDIR'};
21 open(DATA, ">$dest_dir/tmp/create_table.sql") or die "Couldn't open file
22 $dest_dir/tmp/create_table.sql, $!";
23 print DATA "CREATE TABLE t2(";
24 my $cols = 999;
25 for (my $i=1; $i <= $cols; $i++) {
26 if ($i == 1) {
27 print DATA "c1 INT PRIMARY KEY";
28 } else {
29 print DATA "c$i INT";
30 }
31 if ($i != $cols) {
32 print DATA ", ";
33 }
34 }
35 print DATA ");";
36 close DATA;
37 EOF
38
39 --echo # Create table t2 with large schema (huge number of columns)
40 --exec $MYSQL -uroot test <$MYSQLTEST_VARDIR/tmp/create_table.sql
41
42 --echo # Create table with small schema
43 CREATE TABLE t3(c1 VARCHAR(32),
44 c2 VARCHAR(1024),
45 PRIMARY KEY(c1))
46 ENGINE = INNODB;
47
48 --echo # Create tables with constraints
49 CREATE TABLE t4 (c1 INT PRIMARY KEY,
50 c2 BLOB)
51 ENGINE = INNODB;
52
53 CREATE TABLE t5 (c1 INT PRIMARY KEY,
54 c2 INT,
55 FOREIGN KEY (c2) REFERENCES t4(c1) ON UPDATE CASCADE)
56 ENGINE = INNODB;
57
58 --echo # Create table with spatial index
59 CREATE TABLE t7(c1 INT PRIMARY KEY,
60 c2 INT,
61 c3 CHAR(255),
62 c4 GEOMETRY NOT NULL SRID 0,
63 SPATIAL INDEX(c4))
64 ENGINE = INNODB;
65
66 # Tables must exist before plugin can be started!
67 --source ../include/load_daemon_memcached_expecting_success.inc
68
69 let TABLE_NAME = t1;
70 let MEMCACHED_TABLE_MAPPING = desc_t1;
71 --source ../include/sdi_insert_get_row.inc
72
73 let TABLE_NAME = t2;
74 let MEMCACHED_TABLE_MAPPING = desc_t2;
75 --source ../include/sdi_insert_get_row.inc
76
77 let TABLE_NAME = t3;
78 let MEMCACHED_TABLE_MAPPING = desc_t3;
79 --source ../include/sdi_insert_get_row.inc
80
81 let TABLE_NAME = t4;
82 let MEMCACHED_TABLE_MAPPING = desc_t4;
83 --source ../include/sdi_insert_get_row.inc
84
85 let TABLE_NAME = t5;
86 let MEMCACHED_TABLE_MAPPING = desc_t5;
87 --source ../include/sdi_insert_get_row.inc
88
89 let TABLE_NAME = t7;
90 let MEMCACHED_TABLE_MAPPING = desc_t7;
91 --source ../include/sdi_insert_get_row.inc
92
93 --echo # 2. Test with alter table
94
95 # Alter columns
96 ALTER TABLE t1 ADD COLUMN c11 INT;
97 let TABLE_NAME = t1;
98 let MEMCACHED_TABLE_MAPPING = desc_t1;
99 --source ../include/sdi_insert_get_row.inc
100
101 ALTER TABLE t3 ADD COLUMN c3 INT;
102 let TABLE_NAME = t3;
103 let MEMCACHED_TABLE_MAPPING = desc_t3;
104 --source ../include/sdi_insert_get_row.inc
105
106 ALTER TABLE t2 DROP COLUMN c100;
107 ALTER TABLE t2 DROP COLUMN c101;
108 ALTER TABLE t2 DROP COLUMN c102;
109 ALTER TABLE t2 DROP COLUMN c103;
110 ALTER TABLE t2 DROP COLUMN c104;
111 let TABLE_NAME = t2;
112 let MEMCACHED_TABLE_MAPPING = desc_t2;
113 --source ../include/sdi_insert_get_row.inc
114
115 ALTER TABLE t7 MODIFY c2 BIGINT NOT NULL;
116 let TABLE_NAME = t7;
117 let MEMCACHED_TABLE_MAPPING = desc_t7;
118 --source ../include/sdi_insert_get_row.inc
119
120 # Alter constraints
121
122 ALTER TABLE t3 ADD FOREIGN KEY(c3) REFERENCES t2(c1);
123 SHOW CREATE TABLE t3;
124 let TABLE_NAME = t3;
125 let MEMCACHED_TABLE_MAPPING = desc_t3;
126 --source ../include/sdi_insert_get_row.inc
127
128 ALTER TABLE t5 DROP FOREIGN KEY t5_ibfk_1;
129 let TABLE_NAME = t5;
130 let MEMCACHED_TABLE_MAPPING = desc_t5;
131 --source ../include/sdi_insert_get_row.inc
132
133 # Alter indexes
134 ALTER TABLE t7 ADD INDEX idx3(c2,c3(50));
135 let TABLE_NAME = t7;
136 let MEMCACHED_TABLE_MAPPING = desc_t7;
137 --source ../include/sdi_insert_get_row.inc
138
139 ALTER TABLE t1 DROP PRIMARY KEY, DROP COLUMN c1, ADD c1 BIGINT PRIMARY
140 KEY;
141 let TABLE_NAME = t1;
142 let MEMCACHED_TABLE_MAPPING = desc_t1;
143 --source ../include/sdi_insert_get_row.inc
144
145 # Alter Fulltext Indexes
146 ALTER TABLE t1 ADD FULLTEXT INDEX(c2), ALGORITHM=INPLACE;
147 let TABLE_NAME = t1;
148 let MEMCACHED_TABLE_MAPPING = desc_t1;
149 --source ../include/sdi_insert_get_row.inc
150
151 ALTER TABLE t1 DROP INDEX c2;
152 let TABLE_NAME = t1;
153 let MEMCACHED_TABLE_MAPPING = desc_t1;
154 --source ../include/sdi_insert_get_row.inc
155
156 # Test with SDI after a TRUNCATE TABLE operation
157 TRUNCATE TABLE t7;
158 let TABLE_NAME = t7;
159 let MEMCACHED_TABLE_MAPPING = desc_t7;
160 --source ../include/sdi_insert_get_row.inc
161
162 DROP TABLE t1, t3, t2, t4, t5, t7;
163 # Stop plugin before innodb_memcached configuration
164 UNINSTALL PLUGIN daemon_memcached;
165 --remove_file $MYSQLTEST_VARDIR/tmp/create_table.sql
166