Despot:Schema

Proposed Schema

`branches`

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

`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` enum('owner','peer','member') NOT NULL default 'member',
 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` mediumtext,
 `newvalue` mediumtext,
 `who` mediumint(8) unsigned NOT NULL,
 `ts` datetime NOT NULL,
 KEY `ts` (`ts`),
 KEY `partition_id` (`partition_id`)

`partitions`

 `id` mediumint(8) unsigned NOT NULL auto_increment,
 `name` varchar(255) NOT NULL,
 `state` enum('open','restricted','closed') NOT NULL default 'open',
 `description` text,
 `doclinks` mediumtext,
 `newsgroups` mediumtext,
 `parent_id` mediumint(8) unsigned NOT NULL default '0',
 `private` tinyint(1) NOT NULL default '0',
 PRIMARY KEY  (`id`),
 KEY `name` (`name`),
 KEY `parent_id` (`parent_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` mediumtext,
 `newvalue` mediumtext,
 `who` mediumint(8) unsigned NOT NULL,
 `ts` datetime NOT NULL,
 KEY `ts` (`ts`),
 KEY `repository_id` (`repository_id`)

`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'

`tags`

 `name` varchar(255) NOT NULL,
 `partition_id` mediumint(8) unsigned NOT NULL,
 KEY `name` (`name`)

`user_changes`

 `user_id` mediumint(8) unsigned NOT NULL,
 `field` varchar(255) NOT NULL,
 `oldvalue` mediumtext,
 `newvalue` mediumtext,
 `who` mediumint(8) unsigned NOT NULL,
 `ts` datetime NOT NULL,
 KEY `ts` (`ts`),
 KEY `user_id` (`user_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