Despot:Schema: Difference between revisions

From MozillaWiki
Jump to navigation Jump to search
No edit summary
 
(5 intermediate revisions by 2 users not shown)
Line 1: Line 1:
== Proposed Schema (as text) ==
== Proposed Schema ==
partitions
`branches`
- id (int, auto_increment, primary key)
  `id` mediumint(8) unsigned NOT NULL auto_increment,
- name (varchar)
  `name` varchar(255) NOT NULL,
- state (enum - open, restricted, closed)
  PRIMARY KEY (`id`)
  - description (mediumtext)
- doclinks (mediumtext)
- newsgroups (mediumtext)
- category_id
- private (boolean)
* index(name)


repositories
`files`
- id (int, auto_increment, primary key)
  `partition_id` mediumint(8) unsigned NOT NULL,
- name (varchar)
  `repository_id` mediumint(8) unsigned NOT NULL,
- type (integer - cvs, svn, hg)
  `branch_id` mediumint(8) unsigned NOT NULL default '0',
- root (varchar)
  `pattern` varchar(255) NOT NULL,
- vouchers_req (smallint; number of vouchers needed for access)
  KEY `partition_id` (`partition_id`),
- sr_req (boolean; is an SR required to approve all accounts?)
  KEY `repository_id` (`repository_id`),
- private (boolean)
  KEY `branch_id` (`branch_id`)
* unique(name,type)


branches
`members`
- id (int, auto_increment, primary key)
  `user_id` mediumint(8) unsigned NOT NULL,
- name (varchar)
  `partition_id` mediumint(8) unsigned NOT NULL,
* unique(name)
  `class` enum('owner','peer','member') NOT NULL default 'member',
  UNIQUE KEY `user_id` (`user_id`,`partition_id`),
  KEY `class` (`class`)


files
`partition_changes`
- partition_id (int -> partitions)
  `partition_id` mediumint(8) unsigned NOT NULL,
- repository_id (int -> repositories)
  `field` varchar(255) NOT NULL,
- branch_id (int -> branches; only used for cvs)
  `oldvalue` mediumtext,
- pattern (varchar or text)
  `newvalue` mediumtext,
* index(partition_id)
  `who` mediumint(8) unsigned NOT NULL,
* index(repository_id)
  `ts` datetime NOT NULL,
* index(branch_id)
  KEY `ts` (`ts`),
  KEY `partition_id` (`partition_id`)


members
`partitions`
- user_id (int)
  `id` mediumint(8) unsigned NOT NULL auto_increment,
- partition_id (int)
  `name` varchar(255) NOT NULL,
- class (enum - owner, peer, member)
  `state` enum('open','restricted','closed') NOT NULL default 'open',
* unique(user_id,partition_id)
  `description` text,
  * index(partition_id)
  `doclinks` mediumtext,
* index(class)
  `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`)


users
`repositories`
- id (int, auto_increment, primary key)
  `id` mediumint(8) unsigned NOT NULL auto_increment,
- ldap_id (int; refers to LDAP account)
  `name` varchar(255) NOT NULL,
- disabled (boolean)
  `type` tinyint(3) unsigned NOT NULL,
- signedform (boolean)
  `root` varchar(255) NOT NULL,
- superreviewer (boolean)
  `vouchers_req` tinyint(3) unsigned NOT NULL default '2',
- bugzilla_id (int)
  `sr_req` tinyint(3) unsigned NOT NULL default '1',
  * unique(account)
  `private` tinyint(1) NOT NULL default '0',
* unique(bugzilla_id)
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`,`type`)


sessions
`repository_changes`
- id (varchar)
  `repository_id` mediumint(8) unsigned NOT NULL,
- data (text)
  `field` varchar(255) NOT NULL,
- expires (int)
  `oldvalue` mediumtext,
  `newvalue` mediumtext,
  `who` mediumint(8) unsigned NOT NULL,
  `ts` datetime NOT NULL,
  KEY `ts` (`ts`),
  KEY `repository_id` (`repository_id`)


restrictions
`restrictions`
- user_id (int)
  `user_id` mediumint(8) unsigned NOT NULL,
- partition_id (int)
  `partition_id` mediumint(8) unsigned NOT NULL,
* index(user_id)
  UNIQUE KEY `user_id` (`user_id`,`partition_id`)
* index(partition_id)


categories
`sessions`
- id (int)
  `id` varchar(255) NOT NULL default '',
  - name (varchar)
  `data` text,
  `expires` int(11) default NULL,
  PRIMARY KEY (`id`)


vouchers
`syncneeded`
- user_id (int)
  `repository_id` mediumint(8) unsigned NOT NULL,
- partition_id (int)
  `needed` tinyint(1) NOT NULL default '0'
- voucher_id (int)
- sr (boolean)
* index(user_id)
* index(voucher_id)
* index(partition_id)


user_changes
`tags`
- user_id (int)
  `name` varchar(255) NOT NULL,
- field (varchar)
  `partition_id` mediumint(8) unsigned NOT NULL,
- oldvalue (varchar)
  KEY `name` (`name`)
- newvalue (varchar)
- who (int)
- ts (timestamp)
* index(ts)


partition_changes
`user_changes`
- partition_id (int)
  `user_id` mediumint(8) unsigned NOT NULL,
- field (varchar)
  `field` varchar(255) NOT NULL,
- oldvalue (varchar)
  `oldvalue` mediumtext,
- newvalue (varchar)
  `newvalue` mediumtext,
- who (int)
  `who` mediumint(8) unsigned NOT NULL,
- ts (timestamp)
  `ts` datetime NOT NULL,
* index(ts)
  KEY `ts` (`ts`),
  KEY `user_id` (`user_id`)


repository_changes
`vouchers`
- repository_id (int)
  `user_id` mediumint(8) unsigned NOT NULL,
- field (varchar)
  `partition_id` mediumint(8) unsigned NOT NULL,
- oldvalue (varchar)
  `voucher_id` mediumint(8) unsigned NOT NULL,
- newvalue (varchar)
  `sr` tinyint(1) NOT NULL default '0',
- who (int)
  KEY `user_id` (`user_id`,`partition_id`,`voucher_id`)
- 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 ==
== Fields that need to be decided if they belong in LDAP or mysql ==

Latest revision as of 06:37, 14 March 2007

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
Retrieved from "https://wiki.mozilla.org/index.php?title=Despot:Schema&oldid=51801"