1/* user-related tables */ 2 3/* various things that may hang off the users table in the future: 4 * ownership of bugs and bug comments 5 * 'subscription' to bugs 6 * 'subscription' to notes updates of manual sections 7 * cvs acls 8*/ 9 10/* the users table is the main one. it contains the name, email, and 11 crypted password for each user. the password is crypted using the 12 standard unix DES-based crypt (for interop with cvs) */ 13/* we have a full-text index on name, username and email for searching, and we 14 require unique email addresses for each account. the username must also 15 be unique (when present). */ 16/* a user will be able to change the email address associated with 17 their account if they know the password. */ 18/* the cvsaccess field requires more thought. we might want to expand 19 it to a more general flags field or something. it already implies 20 an email alias in addition to cvs access. */ 21/* dns_allow states whether or not a user gets a <username>.people.php.net hostname. 22 Abusive users can have their dns privilidges revoked using this field. 23 dns_type is (currently) one of 'A','NS','CNAME' or 'NONE'. 24 dns_target is dependent on dns_type and should be self explanatory */ 25CREATE TABLE users ( 26 userid int(11) NOT NULL auto_increment, 27 passwd varchar(16) NOT NULL default '', 28 svnpasswd varchar(32) NOT NULL default '', 29 md5passwd varchar(32) NOT NULL default '', 30 name varchar(255) NOT NULL default '', 31 email varchar(255) NOT NULL default '', 32 username varchar(16) default NULL, 33 cvsaccess int(1) NOT NULL default '0', 34 spamprotect int(1) NOT NULL default '1', 35 forgot varchar(16) default NULL, 36 dns_allow int(1) NOT NULL default '1', 37 dns_type varchar(5) NOT NULL default 'NONE', 38 dns_target varchar(255) NOT NULL default '', 39 last_commit datetime default NULL, 40 num_commits int(11) NOT NULL default '0', 41 verified int(1) NOT NULL default '0', 42 use_sa int(11) default '5', 43 greylist int(11) NOT NULL default '0', 44 enable int(1) NOT NULL default '0', 45 pchanged int(11) default '0', 46 ssh_keys TEXT default NULL, 47 PRIMARY KEY (userid), 48 UNIQUE KEY email (email), 49 UNIQUE KEY username (username), 50 FULLTEXT KEY name (name,email,username) 51) ENGINE=MyISAM; 52 53/* the user_note table just contains notes about each user. */ 54CREATE TABLE users_note ( 55 noteid int(11) NOT NULL auto_increment, 56 userid int(11) NOT NULL default '0', 57 entered datetime NOT NULL, 58 note text, 59 PRIMARY KEY (noteid), 60 FULLTEXT KEY note (note) 61) ENGINE=MyISAM; 62 63/* the users_profile table contains up to one profile row for each user */ 64CREATE TABLE users_profile ( 65 userid int(11) NOT NULL, 66 markdown TEXT NOT NULL, 67 html TEXT NOT NULL, 68 PRIMARY KEY (userid) 69) ENGINE=MyISAM DEFAULT CHARSET=utf8; 70
