Mercurial > ~dholland > hg > swallowtail > index.cgi
comparison database/schema/users.sql @ 8:68cc276ac118
SQL material from old tree, split up for accessibility.
| author | David A. Holland |
|---|---|
| date | Sun, 19 Feb 2012 19:54:48 -0500 |
| parents | |
| children | d4c3bd255653 |
comparison
equal
deleted
inserted
replaced
| 7:c013fb703183 | 8:68cc276ac118 |
|---|---|
| 1 -- | |
| 2 -- Users. | |
| 3 -- | |
| 4 -- Rather than trying to have different kinds of users (as in the last | |
| 5 -- rev of this stuff) I think we will just have user permissions. | |
| 6 -- Otherwise we end up in various kinds of trouble if users change | |
| 7 -- type. This means we cannot use foreign key constraints to e.g. | |
| 8 -- ensure that non-developers are not responsible for bugs; however, | |
| 9 -- I guess we can cope. | |
| 10 -- | |
| 11 -- The permissions are: | |
| 12 -- mailto we can mail to the user's e-mail address on file | |
| 13 -- oldresponsible allowed to be responsible for closed PRs | |
| 14 -- responsible allowed to be responsible for PRs | |
| 15 -- editpr can do edit-pr things | |
| 16 -- admin can do destructive things | |
| 17 -- | |
| 18 -- The following types of users should have permissions as follows: | |
| 19 -- peon none or mailto | |
| 20 -- submitter mailto | |
| 21 -- role mailto|oldresponsible|responsible | |
| 22 -- developer mailto|oldresponsible|responsible|editpr | |
| 23 -- administrator mailto|oldresponsible|responsible|editpr|admin | |
| 24 -- retired devel mailto|oldresponsible | |
| 25 -- deceased devel oldresponsible | |
| 26 -- | |
| 27 -- However, this may vary; e.g. users whose mail address bounces might | |
| 28 -- have their mailto permission revoked. | |
| 29 -- | |
| 30 -- The "webpassword" is for use logging in over unsecured HTTP to do | |
| 31 -- things submitters can do. It is assumed that developers (and | |
| 32 -- administrators, who should all be developers) authenticate some | |
| 33 -- other way. | |
| 34 -- | |
| 35 | |
| 36 CREATE SEQUENCE next_user; | |
| 37 | |
| 38 CREATE TABLE users ( | |
| 39 id bigint primary key default nextval('next_user'), | |
| 40 username text , | |
| 41 realname text , | |
| 42 | |
| 43 -- permissions -- | |
| 44 mailto boolean not null, | |
| 45 oldresponsible boolean not null, | |
| 46 responsible boolean not null, | |
| 47 editpr boolean not null, | |
| 48 admin boolean not null | |
| 49 | |
| 50 -- responsible implies oldresponsible | |
| 51 check NOT (responsible AND NOT oldresponsible) | |
| 52 ) | |
| 53 WITHOUT OIDS; | |
| 54 | |
| 55 CREATE TABLE mailaddresses ( | |
| 56 id bigint not null references users (id), | |
| 57 selected boolean not null, | |
| 58 email text not null, | |
| 59 organization text , | |
| 60 webpassword text , | |
| 61 lastheardfrom date | |
| 62 ) | |
| 63 WITHOUT OIDS; | |
| 64 | |
| 65 CREATE VIEW usermail AS | |
| 66 SELECT id, username, realname, email, | |
| 67 mailto, responsible, editpr, admin | |
| 68 FROM users, mailaddresses | |
| 69 WHERE users.id = mailaddresses.id AND mailaddresses.selected | |
| 70 ; | |
| 71 | |
| 72 -- Intended constraint: | |
| 73 -- (oldresponsible OR responsible OR editpr OR admin) | |
| 74 -- implies username not null. | |
| 75 |