github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/tenant/clustertable2.sql (about)

     1  set global enable_privilege_cache = off;
     2  drop database if exists db1;
     3  create database db1;
     4  -- table b
     5  create table db1.b(b int);
     6  insert into db1.b values (0),(1),(2),(3);
     7  
     8  drop account if exists account_test;
     9  create account account_test admin_name = 'root' identified by '111' open comment 'account_test';
    10  
    11  use mo_catalog;
    12  drop table if exists a;
    13  
    14  --------------------
    15  -- one attribute
    16  --------------------
    17  
    18  -- cluster table a
    19  create cluster table a(a int);
    20  
    21  -- insert into cluster table
    22  insert into a values(0,0),(1,0),(2,0),(3,0);
    23  insert into a values(0,1),(1,1),(2,1),(3,1);
    24  update a set account_id=(select account_id from mo_account where account_name="account_test") where account_id=1;
    25  select a from a;
    26  
    27  -- check it in the non-sys account
    28  -- @session:id=2&user=account_test:root&password=111
    29  use mo_catalog;
    30  select * from a;
    31  -- @session
    32  
    33  -- delete data from a
    34  delete from a;
    35  
    36  -- check it in the non-sys account
    37  -- @session:id=2&user=account_test:root&password=111
    38  use mo_catalog;
    39  select * from a;
    40  -- @session
    41  
    42  -- insert into cluster table
    43  insert into a select b,0 from db1.b;
    44  insert into a select b,1 from db1.b;
    45  update a set account_id=(select account_id from mo_account where account_name="account_test") where account_id=1;
    46  select a from a;
    47  
    48  -- check it in the non-sys account
    49  -- @session:id=2&user=account_test:root&password=111
    50  use mo_catalog;
    51  select * from a;
    52  -- @session
    53  
    54  delete from a;
    55  
    56  -- load into cluster table
    57  load data infile '$resources/load_data/cluster_table1.csv' into table a fields terminated by ',';
    58  update a set account_id=(select account_id from mo_account where account_name="account_test") where account_id=1;
    59  select a from a;
    60  
    61  -- check it in the non-sys account
    62  -- @session:id=2&user=account_test:root&password=111
    63  use mo_catalog;
    64  select * from a;
    65  -- @session
    66  
    67  delete from a;
    68  
    69  -- check it in the non-sys account
    70  -- @session:id=2&user=account_test:root&password=111
    71  use mo_catalog;
    72  select * from a;
    73  -- @session
    74  
    75  delete from a;
    76  truncate table a;
    77  
    78  -- non-sys account operate the cluster table
    79  -- @session:id=2&user=account_test:root&password=111
    80  use mo_catalog;
    81  delete from a;
    82  drop table a;
    83  truncate table a;
    84  -- @session
    85  
    86  -- drop account
    87  drop account if exists account_test;
    88  
    89  select a from a;
    90  drop table if exists a;
    91  drop account if exists account_test;
    92  drop database if exists db1;
    93  
    94  use mo_catalog;
    95  CREATE CLUSTER TABLE `mo_instance` (`id` varchar(128) NOT NULL,`name` VARCHAR(255) NOT NULL,`account_name` varchar(128) NOT NULL,`provider` longtext NOT NULL,`provider_id` longtext,`region` longtext NOT NULL,`plan_type` longtext NOT NULL,`version` longtext,`status` longtext,`quota` longtext,`network_policy` longtext,`created_by` longtext,`created_at` datetime(3) NULL,PRIMARY KEY (`id`, `account_id`),UNIQUE INDEX `uniq_acc` (`account_name`));
    96  desc mo_catalog.mo_instance;
    97  create account acc_idx ADMIN_NAME 'root' IDENTIFIED BY '123456';
    98  -- @session:id=3&user=acc_idx:root&password=123456
    99  desc mo_catalog.mo_instance;
   100  -- @session
   101  drop table mo_instance;
   102  drop account if exists acc_idx;
   103  
   104  use mo_catalog;
   105  CREATE CLUSTER TABLE `mo_instance` (`id` varchar(128) NOT NULL,`name` VARCHAR(255) NOT NULL,`account_name` varchar(128) NOT NULL,`provider` longtext NOT NULL,`provider_id` longtext,`region` longtext NOT NULL,`plan_type` longtext NOT NULL,`version` longtext,`status` longtext,`quota` longtext,`network_policy` longtext,`created_by` longtext,`created_at` datetime(3) NULL,PRIMARY KEY (`id`, `account_id`),UNIQUE INDEX `uniq_acc` (`account_name`));
   106  create account acc_idx ADMIN_NAME 'root' IDENTIFIED BY '123456';
   107  -- @session:id=4&user=acc_idx:root&password=123456
   108  SELECT mo_table_rows('mo_catalog', 'mo_instance') as t;
   109  SELECT mo_table_size('mo_catalog', 'mo_instance') as t;
   110  -- @session
   111  drop table mo_instance;
   112  drop account if exists acc_idx;
   113  set global enable_privilege_cache = on;