Despot:Schema2

Proposed Schema

`branches`

 `id` mediumint(8) unsigned NOT NULL auto_increment,
 `name` varchar(255) NOT NULL,
 `description` varchar(255) 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` (removed)

 `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` (removed)

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

`logs` (incomplete)

 `id` int(11) unsigned NOT NULL auto_increment,
 `user_id` mediumint(8) unsigned NOT NULL,
 `action` varchar(50) NOT NULL,
 `field` varchar(30) NULL,
 `time` datetime NOT NULL DEFAULT NOW()
 PRIMARY KEY (`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` (incomplete)

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

`user_changes` (removed)

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

Notes

• Make tags object agnostic (i.e. allow tagging users, partitions, etc. together with a single tag).
• Consider using InnoDB for several tables where ACID compliance is important.
  • Also allows simplification of table relationships with real foreign keys and constraints.
• Replace *_changes tables with a unified and more comprehensive generic log table.
  • Decide which log items should expire over time.