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

     1  CREATE TABLE IF NOT EXISTS Teams (
     2      Id varchar(26) NOT NULL,
     3      CreateAt bigint(20) DEFAULT NULL,
     4      UpdateAt bigint(20) DEFAULT NULL,
     5      DeleteAt bigint(20) DEFAULT NULL,
     6      DisplayName varchar(64) DEFAULT NULL,
     7      Name varchar(64) DEFAULT NULL,
     8      Description varchar(255) DEFAULT NULL,
     9      Email varchar(128) DEFAULT NULL,
    10      Type varchar(255) DEFAULT NULL,
    11      CompanyName varchar(64) DEFAULT NULL,
    12      AllowedDomains text,
    13      InviteId varchar(32) DEFAULT NULL,
    14      SchemeId varchar(26) DEFAULT NULL,
    15      PRIMARY KEY (Id),
    16      UNIQUE KEY Name (Name),
    17      KEY idx_teams_invite_id (InviteId),
    18      KEY idx_teams_update_at (UpdateAt),
    19      KEY idx_teams_create_at (CreateAt),
    20      KEY idx_teams_delete_at (DeleteAt),
    21      KEY idx_teams_scheme_id (SchemeId)
    22  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    23  
    24  SET @preparedStatement = (SELECT IF(
    25      (
    26          SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    27          WHERE table_name = 'Teams'
    28          AND table_schema = DATABASE()
    29          AND column_name = 'AllowOpenInvite'
    30      ) > 0,
    31      'SELECT 1',
    32      'ALTER TABLE Teams ADD AllowOpenInvite bool;'
    33  ));
    34  
    35  PREPARE alterIfNotExists FROM @preparedStatement;
    36  EXECUTE alterIfNotExists;
    37  DEALLOCATE PREPARE alterIfNotExists;
    38  
    39  SET @preparedStatement = (SELECT IF(
    40      (
    41          SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    42          WHERE table_name = 'Teams'
    43          AND table_schema = DATABASE()
    44          AND column_name = 'LastTeamIconUpdate'
    45      ) > 0,
    46      'SELECT 1',
    47      'ALTER TABLE Teams ADD LastTeamIconUpdate bigint;'
    48  ));
    49  
    50  PREPARE alterIfNotExists FROM @preparedStatement;
    51  EXECUTE alterIfNotExists;
    52  DEALLOCATE PREPARE alterIfNotExists;
    53  
    54  SET @preparedStatement = (SELECT IF(
    55      (
    56          SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    57          WHERE table_name = 'Teams'
    58          AND table_schema = DATABASE()
    59          AND column_name = 'Description'
    60      ) > 0,
    61      'SELECT 1',
    62      'ALTER TABLE Teams ADD Description varchar(255);'
    63  ));
    64  
    65  PREPARE alterIfNotExists FROM @preparedStatement;
    66  EXECUTE alterIfNotExists;
    67  DEALLOCATE PREPARE alterIfNotExists;
    68  
    69  SET @preparedStatement = (SELECT IF(
    70      (
    71          SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    72          WHERE table_name = 'Teams'
    73          AND table_schema = DATABASE()
    74          AND column_name = 'GroupConstrained'
    75      ) > 0,
    76      'SELECT 1',
    77      'ALTER TABLE Teams ADD GroupConstrained tinyint(1);'
    78  ));
    79  
    80  PREPARE alterIfNotExists FROM @preparedStatement;
    81  EXECUTE alterIfNotExists;
    82  DEALLOCATE PREPARE alterIfNotExists;