Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
Newer
Older
100644 51 lines (44 sloc) 1.998 kb
b4978db @kiall Moving Auth ORM driver from its own repo to the ORM module for consisten...
kiall authored
1 CREATE TABLE IF NOT EXISTS `roles` (
2 `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
3 `name` varchar(32) NOT NULL,
4 `description` varchar(255) NOT NULL,
5 PRIMARY KEY (`id`),
6 UNIQUE KEY `uniq_name` (`name`)
7 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
8
9 INSERT INTO `roles` (`id`, `name`, `description`) VALUES(1, 'login', 'Login privileges, granted after account confirmation');
10 INSERT INTO `roles` (`id`, `name`, `description`) VALUES(2, 'admin', 'Administrative user, has access to everything.');
11
12 CREATE TABLE IF NOT EXISTS `roles_users` (
13 `user_id` int(10) UNSIGNED NOT NULL,
14 `role_id` int(10) UNSIGNED NOT NULL,
15 PRIMARY KEY (`user_id`,`role_id`),
16 KEY `fk_role_id` (`role_id`)
17 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
18
19 CREATE TABLE IF NOT EXISTS `users` (
20 `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
605b625 @Zeelot updating the sql files to reflect the email length change (refs #4011)
Zeelot authored
21 `email` varchar(254) NOT NULL,
b4978db @kiall Moving Auth ORM driver from its own repo to the ORM module for consisten...
kiall authored
22 `username` varchar(32) NOT NULL DEFAULT '',
1eee586 @kiall Fixes #3579 - Sample schema is too "small" for a sha256
kiall authored
23 `password` varchar(64) NOT NULL,
b4978db @kiall Moving Auth ORM driver from its own repo to the ORM module for consisten...
kiall authored
24 `logins` int(10) UNSIGNED NOT NULL DEFAULT '0',
25 `last_login` int(10) UNSIGNED,
26 PRIMARY KEY (`id`),
27 UNIQUE KEY `uniq_username` (`username`),
28 UNIQUE KEY `uniq_email` (`email`)
29 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
30
31 CREATE TABLE IF NOT EXISTS `user_tokens` (
32 `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
33 `user_id` int(11) UNSIGNED NOT NULL,
34 `user_agent` varchar(40) NOT NULL,
9720af8 @isaiahdw Added token generation method, and make it check the database to enforce...
isaiahdw authored
35 `token` varchar(40) NOT NULL,
36 `type` varchar(100) NOT NULL,
b4978db @kiall Moving Auth ORM driver from its own repo to the ORM module for consisten...
kiall authored
37 `created` int(10) UNSIGNED NOT NULL,
38 `expires` int(10) UNSIGNED NOT NULL,
39 PRIMARY KEY (`id`),
40 UNIQUE KEY `uniq_token` (`token`),
71403b9 @Zeelot add KEY for expires column (refs #3031)
Zeelot authored
41 KEY `fk_user_id` (`user_id`),
42 KEY `expires` (`expires`)
b4978db @kiall Moving Auth ORM driver from its own repo to the ORM module for consisten...
kiall authored
43 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
44
45 ALTER TABLE `roles_users`
46 ADD CONSTRAINT `roles_users_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
47 ADD CONSTRAINT `roles_users_ibfk_2` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE;
48
49 ALTER TABLE `user_tokens`
1eee586 @kiall Fixes #3579 - Sample schema is too "small" for a sha256
kiall authored
50 ADD CONSTRAINT `user_tokens_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;
Something went wrong with that request. Please try again.