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

     1  create database if not exists snapshot_read;
     2  use snapshot_read;
     3  create table test_snapshot_read (a int);
     4  INSERT INTO test_snapshot_read (a) VALUES(1), (2), (3), (4), (5),(6), (7), (8), (9), (10), (11), (12),(13), (14), (15), (16), (17), (18), (19), (20),(21), (22), (23), (24), (25), (26), (27), (28), (29), (30),(31), (32), (33), (34), (35), (36), (37), (38), (39), (40),(41), (42), (43), (44), (45), (46), (47), (48), (49), (50),(51), (52), (53), (54), (55), (56), (57), (58), (59), (60),(61), (62), (63), (64), (65), (66), (67), (68), (69), (70),(71), (72), (73), (74), (75), (76), (77), (78), (79), (80), (81), (82), (83), (84), (85), (86), (87), (88), (89), (90),(91), (92), (93), (94), (95), (96), (97), (98), (99), (100);
     5  select count(*) from test_snapshot_read;
     6  select sleep(1);
     7  create snapshot snapshot_01 for account sys;
     8  delete from test_snapshot_read where a <= 50;
     9  select count(*) from test_snapshot_read;
    10  select count(*) from test_snapshot_read {snapshot = 'snapshot_01'};
    11  select sleep(1);
    12  create snapshot snapshot_02 for account sys;
    13  INSERT INTO test_snapshot_read (a) VALUES(1), (2), (3), (4), (5),(6), (7), (8), (9), (10), (11), (12),(13), (14), (15), (16), (17), (18), (19), (20),(21), (22), (23), (24), (25), (26), (27), (28), (29), (30),(31), (32), (33), (34), (35), (36), (37), (38), (39), (40);
    14  select count(*) from test_snapshot_read;
    15  select count(*) from test_snapshot_read{snapshot = 'snapshot_02'};
    16  create table test_snapshot_read2(b int);
    17  INSERT INTO test_snapshot_read2 select * from test_snapshot_read{snapshot = 'snapshot_01'} where a <= 30;
    18  select count(*) test_snapshot_read2;
    19  select count(*) from mo_catalog.mo_tables where reldatabase = 'snapshot_read';
    20  select sleep(1);
    21  create snapshot snapshot_03 for account sys;
    22  drop table if exists test_snapshot_read2;
    23  select count(*) from mo_catalog.mo_tables where reldatabase = 'snapshot_read';
    24  select count(*) from mo_catalog.mo_tables{snapshot = 'snapshot_03'} where reldatabase = 'snapshot_read';
    25  drop table if exists test_snapshot_read;
    26  drop database if exists snapshot_read;
    27  drop snapshot snapshot_01;
    28  drop snapshot snapshot_02;
    29  drop snapshot snapshot_03;
    30  
    31  create database if not exists snapshot_read;
    32  use snapshot_read;
    33  create table test_snapshot_read (a int);
    34  INSERT INTO test_snapshot_read (a) VALUES(1), (2), (3), (4), (5),(6), (7), (8), (9), (10), (11), (12),(13), (14), (15), (16), (17), (18), (19), (20),(21), (22), (23), (24), (25), (26), (27), (28), (29), (30),(31), (32), (33), (34), (35), (36), (37), (38), (39), (40),(41), (42), (43), (44), (45), (46), (47), (48), (49), (50),(51), (52), (53), (54), (55), (56), (57), (58), (59), (60),(61), (62), (63), (64), (65), (66), (67), (68), (69), (70),(71), (72), (73), (74), (75), (76), (77), (78), (79), (80), (81), (82), (83), (84), (85), (86), (87), (88), (89), (90),(91), (92), (93), (94), (95), (96), (97), (98), (99), (100);
    35  select count(*) from test_snapshot_read;
    36  create snapshot snapshot_01 for account sys;
    37  delete from test_snapshot_read where a <= 50;
    38  select count(*) from test_snapshot_read;
    39  select count(*) from test_snapshot_read {snapshot = 'snapshot_01'};
    40  create snapshot snapshot_02 for account sys;
    41  INSERT INTO test_snapshot_read (a) VALUES(1), (2), (3), (4), (5),(6), (7), (8), (9), (10), (11), (12),(13), (14), (15), (16), (17), (18), (19), (20),(21), (22), (23), (24), (25), (26), (27), (28), (29), (30),(31), (32), (33), (34), (35), (36), (37), (38), (39), (40);
    42  select count(*) from test_snapshot_read;
    43  select count(*) from test_snapshot_read{snapshot = 'snapshot_02'};
    44  create table test_snapshot_read2(b int);
    45  INSERT INTO test_snapshot_read2 select * from test_snapshot_read{snapshot = 'snapshot_01'} where a <= 30;
    46  select count(*) test_snapshot_read2;
    47  select count(*) from mo_catalog.mo_tables where reldatabase = 'snapshot_read';
    48  create snapshot snapshot_03 for account sys;
    49  drop table if exists test_snapshot_read2;
    50  select count(*) from mo_catalog.mo_tables where reldatabase = 'snapshot_read';
    51  select count(*) from mo_catalog.mo_tables{snapshot = 'snapshot_03'} where reldatabase = 'snapshot_read';
    52  drop table if exists test_snapshot_read;
    53  drop database if exists snapshot_read;
    54  drop snapshot snapshot_01;
    55  drop snapshot snapshot_02;
    56  drop snapshot snapshot_03;
    57  
    58  drop account if exists test_account;
    59  create account test_account admin_name = 'test_user' identified by '111';
    60  -- @session:id=2&user=test_account:test_user&password=111
    61  create database if not exists snapshot_read;
    62  use snapshot_read;
    63  create table test_snapshot_read (a int);
    64  INSERT INTO test_snapshot_read (a) VALUES(1), (2), (3), (4), (5),(6), (7), (8), (9), (10), (11), (12),(13), (14), (15), (16), (17), (18), (19), (20),(21), (22), (23), (24), (25), (26), (27), (28), (29), (30),(31), (32), (33), (34), (35), (36), (37), (38), (39), (40),(41), (42), (43), (44), (45), (46), (47), (48), (49), (50),(51), (52), (53), (54), (55), (56), (57), (58), (59), (60),(61), (62), (63), (64), (65), (66), (67), (68), (69), (70),(71), (72), (73), (74), (75), (76), (77), (78), (79), (80), (81), (82), (83), (84), (85), (86), (87), (88), (89), (90),(91), (92), (93), (94), (95), (96), (97), (98), (99), (100);
    65  select count(*) from test_snapshot_read;
    66  create snapshot snapshot_01 for account test_account;
    67  delete from test_snapshot_read where a <= 50;
    68  select count(*) from test_snapshot_read;
    69  select count(*) from test_snapshot_read {snapshot = 'snapshot_01'};
    70  create snapshot snapshot_02 for account test_account;
    71  INSERT INTO test_snapshot_read (a) VALUES(1), (2), (3), (4), (5),(6), (7), (8), (9), (10), (11), (12),(13), (14), (15), (16), (17), (18), (19), (20),(21), (22), (23), (24), (25), (26), (27), (28), (29), (30),(31), (32), (33), (34), (35), (36), (37), (38), (39), (40);
    72  select count(*) from test_snapshot_read;
    73  select count(*) from test_snapshot_read{snapshot = 'snapshot_02'};
    74  create table test_snapshot_read2(b int);
    75  INSERT INTO test_snapshot_read2 select * from test_snapshot_read{snapshot = 'snapshot_01'} where a <= 30;
    76  select count(*) test_snapshot_read2;
    77  select count(*) from mo_catalog.mo_tables where reldatabase = 'snapshot_read';
    78  create snapshot snapshot_03 for account test_account;
    79  drop table if exists test_snapshot_read2;
    80  select count(*) from mo_catalog.mo_tables where reldatabase = 'snapshot_read';
    81  select count(*) from mo_catalog.mo_tables{snapshot = 'snapshot_03'} where reldatabase = 'snapshot_read';
    82  select count(*) from test_snapshot_read{timestamp = '3020-01-01 00:00:00'};
    83  drop table if exists test_snapshot_read;
    84  drop database if exists snapshot_read;
    85  drop snapshot snapshot_01;
    86  drop snapshot snapshot_02;
    87  drop snapshot snapshot_03;
    88  -- @session
    89  drop account if exists test_account;
    90  
    91  -- unique index table
    92  create database if not exists snapshot_read;
    93  use snapshot_read;
    94  CREATE TABLE users (
    95      id INT AUTO_INCREMENT PRIMARY KEY,
    96      username VARCHAR(255) NOT NULL,
    97      email VARCHAR(255) NOT NULL UNIQUE,
    98      password VARCHAR(255) NOT NULL,
    99      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
   100  );
   101  INSERT INTO users (username, email, password) VALUES ('john_doe', 'john@example.com', 'securepassword123');
   102  INSERT INTO users (username, email, password) VALUES ('jane_smith', 'jane.smith@example.com', 'password123'),('alice_jones', 'alice.jones@gmail.com', 'ilovecats'),('bob_brown', 'bob.brown@yahoo.com', 'mysecretpassword'),('charlie_lee', 'charlie.lee@protonmail.ch', 'secure123'),('diana_wilson', 'diana.wilson@outlook.com', 'D1anaPass');
   103  INSERT INTO users (username, email, password) VALUES ('emily_adams', 'emily.adams@icloud.com', 'Em1Ly123'), ('francis_nguyen', 'francis.nguyen@domain.com', 'fNguyenPass'), ('grace_parker', 'grace.parker@server.com', 'G1race123'), ('henry_miller', 'henry.miller@company.org', 'hMillerSecret'), ('isabella_grant', 'isabella.grant@university.edu', 'iGrantPass');
   104  
   105  select id, username, email from users where email = 'john@example.com';
   106  select id, username, email from users where email = 'alice.jones@gmail.com';
   107  
   108  create snapshot sp_01 for account sys;
   109  
   110  DELETE FROM  users where email = 'john@example.com';
   111  UPDATE users SET password = 'newsecurepassword123' WHERE email = 'alice.jones@gmail.com';
   112  
   113  select id, username, email from users where email = 'john@example.com';
   114  select id, username, email from users where email = 'alice.jones@gmail.com';
   115  
   116  select id, username, email from users{snapshot = 'sp_01'} where email = 'john@example.com';
   117  select id, username, email from users {snapshot = 'sp_01'} where email = 'alice.jones@gmail.com';
   118  
   119  
   120  CREATE TABLE new_users (
   121      id INT AUTO_INCREMENT PRIMARY KEY,
   122      username VARCHAR(255) NOT NULL,
   123      email VARCHAR(255) NOT NULL UNIQUE,
   124      password VARCHAR(255) NOT NULL,
   125      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
   126  );
   127  
   128  insert into new_users select * from users{snapshot = 'sp_01'} where email = 'john@example.com';
   129  insert into new_users select * from users {snapshot = 'sp_01'} where email = 'alice.jones@gmail.com';
   130  
   131  select id, username, email from new_users;
   132  
   133  drop snapshot sp_01;
   134  drop database if exists snapshot_read;
   135  
   136  drop account if exists test_account;
   137  create account test_account admin_name = 'test_user' identified by '111';
   138  -- @session:id=3&user=test_account:test_user&password=111
   139  create database if not exists snapshot_read;
   140  use snapshot_read;
   141  CREATE TABLE users (
   142      id INT AUTO_INCREMENT PRIMARY KEY,
   143      username VARCHAR(255) NOT NULL,
   144      email VARCHAR(255) NOT NULL UNIQUE,
   145      password VARCHAR(255) NOT NULL,
   146      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
   147  );
   148  INSERT INTO users (username, email, password) VALUES ('john_doe', 'john@example.com', 'securepassword123');
   149  INSERT INTO users (username, email, password) VALUES ('jane_smith', 'jane.smith@example.com', 'password123'),('alice_jones', 'alice.jones@gmail.com', 'ilovecats'),('bob_brown', 'bob.brown@yahoo.com', 'mysecretpassword'),('charlie_lee', 'charlie.lee@protonmail.ch', 'secure123'),('diana_wilson', 'diana.wilson@outlook.com', 'D1anaPass');
   150  INSERT INTO users (username, email, password) VALUES ('emily_adams', 'emily.adams@icloud.com', 'Em1Ly123'), ('francis_nguyen', 'francis.nguyen@domain.com', 'fNguyenPass'), ('grace_parker', 'grace.parker@server.com', 'G1race123'), ('henry_miller', 'henry.miller@company.org', 'hMillerSecret'), ('isabella_grant', 'isabella.grant@university.edu', 'iGrantPass');
   151  
   152  select id, username, email from users where email = 'john@example.com';
   153  select id, username, email from users where email = 'alice.jones@gmail.com';
   154  
   155  create snapshot sp_01 for account test_account;
   156  
   157  DELETE FROM  users where email = 'john@example.com';
   158  UPDATE users SET password = 'newsecurepassword123' WHERE email = 'alice.jones@gmail.com';
   159  
   160  select id, username, email from users where email = 'john@example.com';
   161  select id, username, email from users where email = 'alice.jones@gmail.com';
   162  
   163  select id, username, email from users{snapshot = 'sp_01'} where email = 'john@example.com';
   164  select id, username, email from users {snapshot = 'sp_01'} where email = 'alice.jones@gmail.com';
   165  
   166  
   167  CREATE TABLE new_users (
   168      id INT AUTO_INCREMENT PRIMARY KEY,
   169      username VARCHAR(255) NOT NULL,
   170      email VARCHAR(255) NOT NULL UNIQUE,
   171      password VARCHAR(255) NOT NULL,
   172      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
   173  );
   174  
   175  insert into new_users select * from users{snapshot = 'sp_01'} where email = 'john@example.com';
   176  insert into new_users select * from users {snapshot = 'sp_01'} where email = 'alice.jones@gmail.com';
   177  
   178  select id, username, email from new_users;
   179  
   180  drop snapshot sp_01;
   181  drop database if exists snapshot_read;
   182  -- @session
   183  drop account if exists test_account;