Despot:Schema

Proposed Schema

`branches`

 `id` mediumint(8) unsigned NOT NULL auto_increment,
 `name` varchar(255) NOT NULL,
 PRIMARY KEY  (`id`)

`categories`

 `id` mediumint(8) unsigned NOT NULL auto_increment,
 `name` varchar(255) NOT NULL,
 PRIMARY KEY  (`id`),
 UNIQUE KEY `name` (`name`)

`files`

 `partition_id` mediumint(8) unsigned NOT NULL,
 `repository_id` mediumint(8) unsigned NOT NULL,
 `branch_id` mediumint(8) unsigned NOT NULL default '0',
 `pattern` varchar(255) NOT NULL,
 KEY `partition_id` (`partition_id`),
 KEY `repository_id` (`repository_id`),
 KEY `branch_id` (`branch_id`)

`members`

 `user_id` mediumint(8) unsigned NOT NULL,
 `partition_id` mediumint(8) unsigned NOT NULL,
 `class` smallint(1) unsigned NOT NULL default '3',
 UNIQUE KEY `user_id` (`user_id`,`partition_id`),
 KEY `class` (`class`)

`partition_changes`

 `partition_id` mediumint(8) unsigned NOT NULL,
 `field` varchar(255) NOT NULL,
 `oldvalue` varchar(255) default NULL,
 `newvalue` varchar(255) default NULL,
 `who` mediumint(8) unsigned NOT NULL,
 `ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
 KEY `ts` (`ts`)

`partitions`

 `id` mediumint(9) unsigned NOT NULL auto_increment,
 `name` varchar(255) NOT NULL,
 `state` tinyint(1) unsigned NOT NULL default '1',
 `description` text,
 `doclinks` mediumtext,
 `newsgroups` mediumtext,
 `category_id` smallint(5) unsigned NOT NULL default '0',
 `private` tinyint(1) NOT NULL default '0',
 PRIMARY KEY  (`id`),

`repositories`

 `id` mediumint(8) unsigned NOT NULL auto_increment,
 `name` varchar(255) NOT NULL,
 `type` tinyint(3) unsigned NOT NULL,
 `root` varchar(255) NOT NULL,
 `vouchers_req` tinyint(3) unsigned NOT NULL default '2',
 `sr_req` tinyint(3) unsigned NOT NULL default '1',
 `private` tinyint(1) NOT NULL default '0',
 PRIMARY KEY  (`id`),
 UNIQUE KEY `name` (`name`,`type`)

`repository_changes`

 `repository_id` mediumint(8) unsigned NOT NULL,
 `field` varchar(255) NOT NULL,
 `oldvalue` varchar(255) default NULL,
 `newvalue` varchar(255) default NULL,
 `who` mediumint(8) unsigned NOT NULL,
 `ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
 KEY `ts` (`ts`)

`restrictions`

 `user_id` mediumint(8) unsigned NOT NULL,
 `partition_id` mediumint(8) unsigned NOT NULL,
 UNIQUE KEY `user_id` (`user_id`,`partition_id`)

`sessions`

 `id` varchar(255) NOT NULL default ,
 `data` text,
 `expires` int(11) default NULL,
 PRIMARY KEY  (`id`)

`syncneeded`

 `repository_id` mediumint(8) unsigned NOT NULL,
 `needed` tinyint(1) NOT NULL default '0'

`user_changes`

 `user_id` mediumint(8) unsigned NOT NULL,
 `field` varchar(255) NOT NULL,
 `oldvalue` varchar(255) default NULL,
 `newvalue` varchar(255) default NULL,
 `who` mediumint(8) unsigned NOT NULL,
 `ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
 KEY `ts` (`ts`)

`users`

 `id` mediumint(8) unsigned NOT NULL auto_increment,
 `ldap_id` mediumint(8) unsigned NOT NULL,
 `disabled` tinyint(1) NOT NULL default '0',
 `signedform` tinyint(1) NOT NULL default '0',
 `superreviewer` tinyint(1) NOT NULL default '0',
 `bugzilla_id` mediumint(8) unsigned default NULL,
 `despot` tinyint(1) NOT NULL default '0',
 `partitionlord` tinyint(1) NOT NULL default '0',
 PRIMARY KEY  (`id`),
 UNIQUE KEY `ldap_id` (`ldap_id`,`bugzilla_id`)

`vouchers`

 `user_id` mediumint(8) unsigned NOT NULL,
 `partition_id` mediumint(8) unsigned NOT NULL,
 `voucher_id` mediumint(8) unsigned NOT NULL,
 `sr` tinyint(1) NOT NULL default '0',
 KEY `user_id` (`user_id`,`partition_id`,`voucher_id`)

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