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;