18 lines
2.3 KiB
SQL
18 lines
2.3 KiB
SQL
SET FOREIGN_KEY_CHECKS = 0;
|
|
DROP TABLE IF EXISTS `roles_users`;
|
|
DROP TABLE IF EXISTS `roles`;
|
|
DROP TABLE IF EXISTS `users`;
|
|
DROP TABLE IF EXISTS `user_tokens`;
|
|
SET FOREIGN_KEY_CHECKS = 1;
|
|
CREATE TABLE IF NOT EXISTS `roles` (`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,`name` varchar(32) NOT NULL,`description` varchar(255) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uniq_name` (`name`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
|
INSERT INTO `roles` (`id`, `name`, `description`) VALUES(1, 'login', 'Login privileges, granted after account confirmation');
|
|
INSERT INTO `roles` (`id`, `name`, `description`) VALUES(2, 'admin', 'Administrative user, has access to everything.');
|
|
INSERT INTO `roles` (`id`, `name`, `description`) VALUES(3, 'author', 'Can create and edit own articles');
|
|
INSERT INTO `roles` (`id`, `name`, `description`) VALUES(4, 'editor', 'Can create and edit all articles');
|
|
INSERT INTO `roles` (`id`, `name`, `description`) VALUES(5, 'publisher', 'Can publish or revoke publication');
|
|
CREATE TABLE IF NOT EXISTS `roles_users` (`user_id` int(10) UNSIGNED NOT NULL,`role_id` int(10) UNSIGNED NOT NULL, PRIMARY KEY (`user_id`,`role_id`), KEY `fk_role_id` (`role_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
|
CREATE TABLE IF NOT EXISTS `users` (`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,`email` varchar(127) NOT NULL, `username` varchar(32) NOT NULL DEFAULT '', `password` char(50) NOT NULL, `logins` int(10) UNSIGNED NOT NULL DEFAULT '0', `last_login` int(10) UNSIGNED, PRIMARY KEY (`id`), UNIQUE KEY `uniq_username` (`username`), UNIQUE KEY `uniq_email` (`email`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
|
CREATE TABLE IF NOT EXISTS `user_tokens` ( `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, `user_id` int(11) UNSIGNED NOT NULL, `user_agent` varchar(40) NOT NULL, `token` varchar(32) NOT NULL, `created` int(10) UNSIGNED NOT NULL, `expires` int(10) UNSIGNED NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uniq_token` (`token`), KEY `fk_user_id` (`user_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
|
ALTER TABLE `roles_users` ADD CONSTRAINT `roles_users_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `roles_users_ibfk_2` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE;
|
|
ALTER TABLE `user_tokens` ADD CONSTRAINT `user_tokens_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;
|