github.com/tuingking/flamingo@v0.0.0-20220403134817-2796ae0e84ca/script/migrations/000001_user_schema.up.sql (about)

     1  CREATE TABLE IF NOT EXISTS `user` (
     2    `id`              varchar(36)     NOT NULL,
     3    `username`        varchar(32)     NOT NULL,
     4    `password`        varchar(60)     NOT NULL,
     5    `is_active`       tinyint(1)      NOT NULL DEFAULT 1,
     6    `is_superuser`    tinyint(1)      NOT NULL DEFAULT 0,
     7    `name`            varchar(250)    CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
     8    `phone`           varchar(250)    CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
     9    `email`           varchar(250)    CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
    10    `created_at`      timestamp(6)    NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    11    `updated_at`      timestamp(6)    NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    12    `last_login`      timestamp(6)    NULL,
    13    PRIMARY KEY (`id`),
    14    CONSTRAINT `username_unique` UNIQUE (username),
    15    KEY `user_email_idx` (`email`)
    16  ) ENGINE=InnoDB;
    17  
    18  -- group master
    19  CREATE TABLE IF NOT EXISTS `group` (
    20    `id`        int             NOT NULL AUTO_INCREMENT,
    21    `name`      varchar(100)    NOT NULL,
    22    PRIMARY KEY (`id`),
    23    KEY `group_name_idx` (`name`)
    24  ) ENGINE=InnoDB;
    25  
    26  -- permission master
    27  CREATE TABLE IF NOT EXISTS `permission` (
    28    `id`        int           NOT NULL AUTO_INCREMENT,
    29    `name`      varchar(250)  NOT NULL,
    30    PRIMARY KEY (`id`),
    31    KEY `permission_name_idx` (`name`)
    32  ) ENGINE=InnoDB;
    33  
    34  -- user group many to many relation
    35  CREATE TABLE IF NOT EXISTS `user_groups` (
    36    `user_id`     varchar(36)     NOT NULL,
    37    `group_id`    int             NOT NULL,
    38    PRIMARY KEY (`user_id`, `group_id`),
    39    CONSTRAINT `user_groups_user_id_FK` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE,
    40    CONSTRAINT `user_groups_group_id_FK` FOREIGN KEY (`group_id`) REFERENCES `group` (`id`) ON DELETE CASCADE
    41  ) ENGINE=InnoDB;
    42  
    43  CREATE TABLE IF NOT EXISTS `group_permissions` (
    44    `group_id`        int    NOT NULL,
    45    `permission_id`   int    NOT NULL,
    46    PRIMARY KEY (`group_id`, `permission_id`),
    47    CONSTRAINT `group_permissions_group_id_FK` FOREIGN KEY (`group_id`) REFERENCES `group` (`id`) ON DELETE CASCADE,
    48    CONSTRAINT `group_permissions_permission_id_FK` FOREIGN KEY (`permission_id`) REFERENCES `permission` (`id`) ON DELETE CASCADE
    49  ) ENGINE=InnoDB;