Despot:Schema

Proposed Schema (as text)

partitions

- id (int, auto_increment, primary key)
- name (varchar)
- state (enum - open, restricted, closed)
- description (mediumtext)
- doclinks (mediumtext)
- newsgroups (mediumtext)
- category_id
- private (boolean)
* index(name)

repositories

- id (int, auto_increment, primary key)
- name (varchar)
- type (integer - cvs, svn, hg)
- root (varchar)
- vouchers_req (smallint; number of vouchers needed for access)
- sr_req (boolean; is an SR required to approve all accounts?)
- private (boolean)
* unique(name,type)

branches

- id (int, auto_increment, primary key)
- name (varchar)
* unique(name)

files

- partition_id (int -> partitions)
- repository_id (int -> repositories)
- branch_id (int -> branches; only used for cvs)
- pattern (varchar or text)
* index(partition_id)
* index(repository_id)
* index(branch_id)

members

- user_id (int)
- partition_id (int)
- class (enum - owner, peer, member)
* unique(user_id,partition_id)
* index(partition_id)
* index(class)

users

- id (int, auto_increment, primary key)
- ldap_id (int; refers to LDAP account)
- disabled (boolean)
- signedform (boolean)
- superreviewer (boolean)
- bugzilla_id (int)
* unique(account)
* unique(bugzilla_id)

sessions

- id (varchar(255), primary key, not null, default '')
- data (text)
- expires (int(11), default null)

restrictions

- user_id (int)
- partition_id (int)
* index(user_id)
* index(partition_id)

categories

- id (int)
- name (varchar)

vouchers

- user_id (int)
- partition_id (int)
- voucher_id (int)
- sr (boolean)
* index(user_id)
* index(voucher_id)
* index(partition_id)

user_changes

- user_id (int)
- field (varchar)
- oldvalue (varchar)
- newvalue (varchar)
- who (int)
- ts (timestamp)
* index(ts)

partition_changes

- partition_id (int)
- field (varchar)
- oldvalue (varchar)
- newvalue (varchar)
- who (int)
- ts (timestamp)
* index(ts)

repository_changes

- repository_id (int)
- field (varchar)
- oldvalue (varchar)
- newvalue (varchar)
- who (int)
- ts (timestamp)
* index(ts)

syncneeded (is this needed now that authorizations is all handled in LDAP?)

- needed (boolean)
- repository_id (int)

Fields that need to be decided if they belong in LDAP or mysql

- despot (for if the user is a despotadmin or not)
- neednewpassword (has the user reset his/her password from what was originally mailed?)

Other thoughts

- LDAP will have all the main user data in it (name, ssh keys, passwords, etc.)
- LDAP groups will determine access to repositories in general