github.com/masterhung0112/hk_server/v5@v5.0.0-20220302090640-ec71aef15e1c/db/migrations/mysql/000002_create_team_members.up.sql (about)

     1  CREATE TABLE IF NOT EXISTS TeamMembers (
     2      TeamId varchar(26) NOT NULL,
     3      UserId varchar(26) NOT NULL,
     4      Roles varchar(64),
     5      DeleteAt bigint(20),
     6      PRIMARY KEY (TeamId, UserId),
     7      KEY idx_teammembers_user_id (UserId),
     8      KEY idx_teammembers_delete_at (DeleteAt)
     9  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    10  
    11  SET @preparedStatement = (SELECT IF(
    12      (
    13          SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    14          WHERE table_name = 'TeamMembers'
    15          AND table_schema = DATABASE()
    16          AND column_name = 'SchemeUser'
    17      ) > 0,
    18      'SELECT 1',
    19      'ALTER TABLE TeamMembers ADD SchemeUser tinyint(4);'
    20  ));
    21  
    22  PREPARE alterIfNotExists FROM @preparedStatement;
    23  EXECUTE alterIfNotExists;
    24  DEALLOCATE PREPARE alterIfNotExists;
    25  
    26  SET @preparedStatement = (SELECT IF(
    27      (
    28          SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    29          WHERE table_name = 'TeamMembers'
    30          AND table_schema = DATABASE()
    31          AND column_name = 'SchemeAdmin'
    32      ) > 0,
    33      'SELECT 1',
    34      'ALTER TABLE TeamMembers ADD SchemeAdmin tinyint(4);'
    35  ));
    36  
    37  PREPARE alterIfNotExists FROM @preparedStatement;
    38  EXECUTE alterIfNotExists;
    39  DEALLOCATE PREPARE alterIfNotExists;
    40  
    41  SET @preparedStatement = (SELECT IF(
    42      (
    43          SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    44          WHERE table_name = 'TeamMembers'
    45          AND table_schema = DATABASE()
    46          AND column_name = 'SchemeGuest'
    47      ) > 0,
    48      'SELECT 1',
    49      'ALTER TABLE TeamMembers ADD SchemeGuest tinyint(4);'
    50  ));
    51  
    52  PREPARE alterIfNotExists FROM @preparedStatement;
    53  EXECUTE alterIfNotExists;
    54  DEALLOCATE PREPARE alterIfNotExists;
    55  
    56  
    57  SET @preparedStatement = (SELECT IF(
    58      (
    59          SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    60          WHERE table_name = 'TeamMembers'
    61          AND table_schema = DATABASE()
    62          AND column_name = 'DeleteAt'
    63      ) > 0,
    64      'SELECT 1',
    65      'ALTER TABLE TeamMembers ADD DeleteAt bigint(20);'
    66  ));
    67  
    68  PREPARE alterIfNotExists FROM @preparedStatement;
    69  EXECUTE alterIfNotExists;
    70  DEALLOCATE PREPARE alterIfNotExists;