github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/publication_subscription/pub_sub_improvment.sql (about) 1 drop database if exists database01; 2 drop database if exists test02; 3 drop database if exists test03; 4 drop database if exists procedure_test; 5 -- create tenant 6 drop account if exists test_tenant_1; 7 create account test_tenant_1 admin_name 'test_account' identified by '111'; 8 9 -- test sys tenant database publication to test_tenant_1 tenant 10 show databases; 11 create database database01; 12 use database01; 13 create table table01(col1 int, col2 decimal); 14 insert into table01 values (1, 2); 15 insert into table01 values (234, 2413242); 16 select * from table01; 17 18 -- publish to tenant test_tenant_1 19 create publication publication01 database database01 account test_tenant_1 comment 'publish database to account01'; 20 -- @ignore:2,3 21 show publications; 22 23 -- subscribe database01 24 -- @session:id=2&user=test_tenant_1:test_account&password=111 25 create database sub_database01 from sys publication publication01; 26 show databases; 27 use sub_database01; 28 show tables; 29 select * from table01; 30 truncate table table01; 31 delete from table01 where col1 = 1; 32 update table01 set col1 = 100 where col2 = 2413242; 33 -- @ignore:10,11,12 34 show table status; 35 drop table table01; 36 -- @ignore:3,5 37 show subscriptions; 38 -- @session 39 40 drop publication publication01; 41 drop database database01; 42 43 -- sys、acc1、acc2 44 drop database if exists database01; 45 create database database01; 46 use database01; 47 create table t1(a int, b int); 48 insert into t1 values (1, 1), (2, 2), (3, 3); 49 create publication publication01 database database01; 50 -- @ignore:2,3 51 show publications; 52 53 drop account if exists test_tenant_1; 54 drop account if exists test_tenant_2; 55 create account test_tenant_1 admin_name 'test_account' identified by '111'; 56 create account test_tenant_2 admin_name 'test_account' identified by '111'; 57 58 -- @session:id=3&user=test_tenant_1:test_account&password=111 59 drop database if exists sub_database01; 60 create database sub_database01 from sys publication publication01; 61 -- @ignore:3,5 62 show subscriptions; 63 use sub_database01; 64 show tables; 65 select * from t1; 66 -- @session 67 68 drop table if exists t2; 69 create table t2(col1 int primary key ); 70 insert into t2 values (1),(2),(3); 71 72 -- @session:id=4&user=test_tenant_1:test_account&password=111 73 use sub_database01; 74 show tables; 75 select * from t2; 76 -- @session 77 78 -- @session:id=5&user=test_tenant_2:test_account&password=111 79 drop database if exists sub_database01; 80 create database sub_database01 from sys publication publication01; 81 -- @ignore:3,5 82 show subscriptions; 83 use sub_database01; 84 show tables; 85 select * from t1; 86 select * from t2; 87 -- @session 88 drop publication publication01; 89 90 drop database if exists database02; 91 create database database02; 92 use database02; 93 create table table03(col1 char, col2 varchar(100)); 94 insert into table03 values ('1', 'database'); 95 insert into table03 values ('a', 'data warehouse'); 96 create publication publication02 database database02; 97 -- @ignore:2,3 98 show publications; 99 100 -- @session:id=4&user=test_tenant_1:test_account&password=111 101 drop database if exists sub_database02; 102 create database sub_database02 from sys publication publication02; 103 -- @ignore:3,5 104 show subscriptions all; 105 use sub_database02; 106 show tables; 107 select * from table03; 108 -- @session 109 110 -- @session:id=7&user=test_tenant_2:test_account&password=111 111 drop database if exists sub_database02; 112 create database sub_database02 from sys publication publication02; 113 -- @ignore:3,5 114 show subscriptions all; 115 use sub_database02; 116 show tables; 117 select * from table03; 118 -- @ignore:10,11,12 119 show table status; 120 -- @session 121 drop publication publication02; 122 123 drop database if exists database03; 124 create database database03; 125 use database03; 126 drop table if exists table01; 127 create table table01(col1 int); 128 insert into table01 values (-1),(1),(2); 129 create publication publication03 database database03 account test_tenant_1; 130 -- @ignore:2,3 131 show publications; 132 133 -- @session:id=8&user=test_tenant_1:test_account&password=111 134 drop database if exists sub_database03; 135 create database sub_database03 from sys publication publication03; 136 -- @ignore:3,5 137 show subscriptions all; 138 use sub_database03; 139 show tables; 140 select * from table01; 141 desc table01; 142 -- @ignore:10,11,12 143 show table status; 144 -- @session 145 146 -- @session:id=9&user=test_tenant_2:test_account&password=111 147 -- @ignore:3,5 148 show subscriptions all; 149 -- @session 150 151 -- sys modify sub to all tanant 152 alter publication publication03 account all; 153 -- @ignore:2,3 154 show publications; 155 156 -- @session:id=10&user=test_tenant_2:test_account&password=111 157 -- @ignore:3,4,5 158 show subscriptions all; 159 create database sub_database03 from sys publication publication03; 160 use sub_database03; 161 show tables; 162 show columns from table01; 163 desc table01; 164 select * from table01; 165 -- @session 166 167 drop publication publication03; 168 -- @ignore:2,3 169 show publications; 170 171 -- @session:id=11&user=test_tenant_1:test_account&password=111 172 use sub_database01; 173 drop database sub_database01; 174 drop database sub_database02; 175 drop database sub_database03; 176 -- @session 177 178 -- @session:id=12&user=test_tenant_2:test_account&password=111 179 drop database sub_database01; 180 drop database sub_database02; 181 -- @session 182 183 drop database database01; 184 drop database database02; 185 drop database database03; 186 drop account test_tenant_1; 187 drop account test_tenant_2;