CREATE TABLE project (
  project_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  description TEXT NOT NULL,
  logo VARCHAR(50) NOT NULL,
  closed_source enum('true','false') NOT NULL default 'false',
  hidden enum('true','false') NOT NULL default 'false',
  KEY `name` (`name`),
  PRIMARY KEY(project_id)
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;

CREATE TABLE `user_information` (
  `user_id` bigint(20) unsigned NOT NULL,
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `gender` enum('male','female') NOT NULL default 'male',
  `icon` varchar(50) NOT NULL,
  `languages` varchar(250) NOT NULL,
  `registration_date` int(10) unsigned NOT NULL,
  `last_login` int(10) unsigned NOT NULL DEFAULT '0',
  `disable_date` int(10) unsigned NOT NULL,
  `birthday` date NOT NULL default '1900-01-01',
  `timezone` varchar(10) NOT NULL,
  `signature` text NOT NULL,
  `message` text NOT NULL,
  `homepage` varchar(100) NOT NULL,
  `blog` varchar(100) NOT NULL,
  `ohloh_profile` varchar(100) NOT NULL,
  `icq` varchar(10) NOT NULL,
  `aim` varchar(50) NOT NULL,
  `jabber` varchar(50) NOT NULL,
  `yahoo` varchar(50) NOT NULL,
  `msn` varchar(50) NOT NULL,
  `skype` varchar(50) NOT NULL,
  `sip` varchar(50) NOT NULL,
  UNIQUE KEY `user_information` (`user_id`),
  KEY `user_information_FKIndex1` (`last_login`),
  FOREIGN KEY (`user_id`)
    REFERENCES `user` (`user_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;

INSERT INTO `user_information` (`user_id` ,`first_name`, `last_name`)
VALUES ('1' , 'dummy', 'dummy');

CREATE TABLE `user_friendship` (
  `user_id` bigint(20) unsigned NOT NULL,
  `friend_id` bigint(20) unsigned NOT NULL,
  UNIQUE KEY `user_friendship` (`user_id`,`friend_id`),
  KEY `user_friendship_FKIndex1` (`user_id`),
  KEY `user_friendship_FKIndex2` (`friend_id`),
  FOREIGN KEY (`user_id`)
    REFERENCES `user` (`user_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
  FOREIGN KEY (`friend_id`)
    REFERENCES `user` (`user_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;


CREATE TABLE `user_friendship_request` (
  `requester_id` bigint(20) unsigned NOT NULL,
  `requestee_id` bigint(20) unsigned NOT NULL,
  `creation_time` int(10) unsigned NOT NULL,
  UNIQUE KEY `user_friendship` (`requester_id`,`requestee_id`),
  KEY `user_friendship_FKIndex1` (`requester_id`),
  KEY `user_friendship_FKIndex2` (`requestee_id`),
  FOREIGN KEY (`requester_id`)
    REFERENCES `user` (`user_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
  FOREIGN KEY (`requestee_id`)
    REFERENCES `user` (`user_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;

CREATE TABLE `project_bookmark` (
  `project_id` bigint(20) unsigned NOT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  KEY `project_bookmark_FKIndex1` (`project_id`),
  KEY `project_bookmark_FKIndex2` (`user_id`),
  FOREIGN KEY (`project_id`)
  	REFERENCES `project` (`project_id`)
  	  ON DELETE CASCADE
  	  ON UPDATE CASCADE,
  FOREIGN KEY (`user_id`)
    REFERENCES `user` (`user_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;

CREATE TABLE access_group (
  `group_id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL,
  `kind` ENUM('global', 'project', 'community') NOT NULL,
  PRIMARY KEY(`group_id`)
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;

INSERT INTO access_group (group_id, name, kind) VALUES(1, 'Origo Administrator', 'global');
INSERT INTO access_group (group_id, name, kind) VALUES(2, 'Origo User', 'global');
INSERT INTO access_group (group_id, name, kind) VALUES(3, 'Project Administrator', 'project');
INSERT INTO access_group (group_id, name, kind) VALUES(4, 'Project Developer', 'project');
INSERT INTO access_group (group_id, name, kind) VALUES(5, 'Disabled User', 'global');
INSERT INTO access_group (group_id, name, kind) VALUES(6, 'Community Administrator', 'community');
INSERT INTO access_group (group_id, name, kind) VALUES(7, 'Community Member', 'community');

CREATE TABLE policy (
  policy_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  instruction VARCHAR(30) NOT NULL,
  group_id TINYINT UNSIGNED NOT NULL,
  PRIMARY KEY(policy_id),
  KEY instruction(instruction),
  KEY policy_FKIndex1(group_id),
  FOREIGN KEY(group_id)
    REFERENCES access_group(group_id)
      ON DELETE CASCADE
      ON UPDATE CASCADE
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;

INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (1, 'website_edit_owner', 3);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (2, 'website_edit_member', 4);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (3, 'svn_write', 3);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (4, 'svn_write', 4);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (5, 'project_list', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (6, 'project_members_list', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (7, 'project_change_group', 3);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (8, 'project_change_group', 1);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (9, 'project_global_admin', 1);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (10, 'is_online', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (11, 'project_list_of_user', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (12, 'user_my_name', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (13, 'user_change_password', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (14, 'user_key', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (15, 'user_key_generate', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (16, 'user_my_password', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (17, 'system_status', 1);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (18, 'system_mail_all', 1);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (19, 'workitem_list', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (20, 'project_create', 1);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (21, 'release_add', 3);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (22, 'user_change_email', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (23, 'user_change_wi_set', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (24, 'user_list_wi_set', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (25, 'workitem_show', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (26, 'project_show', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (27, 'add_bookmark', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (28, 'remove_bookmark', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (29, 'blog_add', 3);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (30, 'blog_add', 4);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (31, 'wiki_add', 3);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (32, 'wiki_add', 4);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (33, 'list_bookmark', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (34, 'workitem_set_read_status', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (35, 'release_list', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (36, 'project_change_desc', 3);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (37, 'project_change_logo', 3);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (38, 'release_delete', 3);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (39, 'issue_list', 3);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (40, 'issue_list', 4);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (41, 'user_my_email', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (42, 'comment_add', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (43, 'project_request_add', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (44, 'project_request_retrieve', 1);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (45, 'user_retrieve_information', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (46, 'user_set_information', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (47, 'user_list_reported_issues', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (48, 'user_add_friendship', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (49, 'user_list_friends', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (50, 'user_send_message', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (51, 'user_disable_account', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (52, 'issue_add', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (53, 'issue_comment', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (54, 'issue_comment_extended', 1);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (55, 'issue_comment_extended', 3);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (56, 'issue_comment_extended', 4);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (57, 'issue_update', 1);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (58, 'issue_update', 3);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (59, 'issue_update', 4);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (60, 'release_delete', 1);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (61, 'community_list', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (62, 'community_retrieve', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (63, 'community_create', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (64, 'community_change_group', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (65, 'community_change_group_admin', 1);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (66, 'community_change_group_admin', 6);


CREATE TABLE user_association (
  user_id BIGINT UNSIGNED NOT NULL,
  group_id TINYINT UNSIGNED NOT NULL,
  KEY user_association_FKIndex1(user_id),
  KEY user_association_FKIndex2(group_id),
  FOREIGN KEY(user_id)
    REFERENCES user(user_id)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
  FOREIGN KEY(group_id)
    REFERENCES access_group(group_id)
      ON DELETE CASCADE
      ON UPDATE CASCADE
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;

CREATE TABLE user_project_association (
  project_id BIGINT UNSIGNED NOT NULL,
  group_id TINYINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  KEY user_association_FKIndex1(user_id),
  KEY user_association_FKIndex3(group_id),
  KEY user_project_association_FKIndex3(project_id),
  UNIQUE KEY project_user(project_id, user_id),
  FOREIGN KEY(user_id)
    REFERENCES user(user_id)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
  FOREIGN KEY(project_id)
    REFERENCES project(project_id)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
  FOREIGN KEY(group_id)
    REFERENCES access_group(group_id)
      ON DELETE CASCADE
      ON UPDATE CASCADE
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;

CREATE TABLE `release` (
  `release_id` bigint(20) unsigned NOT NULL auto_increment,
  `project_id` bigint(20) unsigned NOT NULL,
  `creation_time` int(10) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `version` varchar(255) NOT NULL,
  PRIMARY KEY  (`release_id`),
  KEY `release_FKIndex1` (`project_id`),
  FOREIGN KEY (`project_id`)
    REFERENCES `project` (`project_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;

CREATE TABLE `release_file` (
  `file_id` bigint(20) unsigned NOT NULL auto_increment,
  `release_id` bigint(20) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `platform` varchar(255) NOT NULL,
  PRIMARY KEY  (`file_id`),
  KEY `release_file_FKIndex1` (`release_id`),
  FOREIGN KEY (`release_id`)
    REFERENCES `release` (`release_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;


CREATE TABLE `workitem_type` (
  `type_id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY  (`type_id`)
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;

INSERT INTO `workitem_type` (`type_id`, `name`) VALUES 
(1, 'issue'),
(2, 'release'),
(3, 'commit'),
(4, 'wikipage'),
(5, 'blog'),
(6, 'comment');


CREATE TABLE `workitem` (
  `workitem_id` bigint(20) unsigned NOT NULL auto_increment,
  `type_id` int(10) unsigned NOT NULL,
  `creation_time` int(10) unsigned NOT NULL,
  `project_id` bigint(20) unsigned NOT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  PRIMARY KEY  (`workitem_id`),
  KEY `workitem_FKIndex1` (`type_id`),
  KEY `workitem_FKIndex2` (`project_id`),
  KEY `workitem_FKIndex3` (`user_id`),
  FOREIGN KEY (`user_id`)
    REFERENCES `user` (`user_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
  FOREIGN KEY (`type_id`)
    REFERENCES `workitem_type` (`type_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
  FOREIGN KEY (`project_id`)
    REFERENCES `project` (`project_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;


CREATE TABLE `user_workitem_subscription` (
  `user_id` bigint(20) unsigned NOT NULL,
  `project_id` bigint(20) unsigned NOT NULL,
  `type_id` int(10) unsigned NOT NULL,
  `subscription_type` int(11) NOT NULL,
  `enabled` tinyint(1) NOT NULL,
  UNIQUE KEY `user_workitem` (`user_id`,`project_id`,`type_id`,`subscription_type`),
  KEY `user_workitem_subscription_FKIndex1` (`user_id`),
  KEY `user_workitem_subscription_FKIndex2` (`project_id`),
  KEY `user_workitem_subscription_FKIndex3` (`type_id`),
  FOREIGN KEY (`user_id`)
    REFERENCES `user` (`user_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
  FOREIGN KEY (`project_id`)
    REFERENCES `project` (`project_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
  FOREIGN KEY (`type_id`)
    REFERENCES `workitem_type` (`type_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;


CREATE TABLE `user_read_workitem` (
  `user_id` bigint(20) unsigned NOT NULL,
  `workitem_id` bigint(20) unsigned NOT NULL,
  UNIQUE KEY `user_id` (`user_id`,`workitem_id`),
  KEY `user_read_workitem_FKIndex1` (`user_id`),
  KEY `user_read_workitem_FKIndex2` (`workitem_id`),
  FOREIGN KEY (`user_id`)
    REFERENCES `user` (`user_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
  FOREIGN KEY (`workitem_id`)
    REFERENCES `workitem` (`workitem_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;


CREATE TABLE `workitem_blog` (
  `workitem_id` bigint(20) unsigned NOT NULL,
  `title` varchar(255) NOT NULL,
  `diff` longtext NOT NULL,
  `revision` bigint(20) unsigned NOT NULL,
  `old_revision` bigint(20) unsigned NOT NULL,
  KEY `workitem_blog_FKIndex1` (`workitem_id`),
  FOREIGN KEY (`workitem_id`)
    REFERENCES `workitem` (`workitem_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;


CREATE TABLE `workitem_commit` (
  `workitem_id` bigint(20) unsigned NOT NULL,
  `revision` bigint(20) unsigned NOT NULL,
  `log` longtext NOT NULL,
  `diff` longtext NOT NULL,
  KEY `workitem_commit_FKIndex1` (`workitem_id`),
  FOREIGN KEY (`workitem_id`)
    REFERENCES `workitem` (`workitem_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;


CREATE TABLE `workitem_release` (
  `workitem_id` bigint(20) unsigned NOT NULL,
  `release_id` bigint(20) unsigned NOT NULL,
  KEY `workitem_release_FKIndex1` (`workitem_id`),
  KEY `workitem_release_FKIndex2` (`release_id`),
  FOREIGN KEY (`release_id`)
    REFERENCES `release` (`release_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
  FOREIGN KEY (`workitem_id`)
    REFERENCES `workitem` (`workitem_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;


CREATE TABLE `workitem_wiki` (
  `workitem_id` bigint(20) unsigned NOT NULL,
  `title` varchar(255) NOT NULL,
  `diff` longtext NOT NULL,
  `revision` bigint(20) unsigned NOT NULL,
  `old_revision` bigint(20) unsigned NOT NULL,
  `private` tinyint(1) NOT NULL,
  KEY `workitem_wiki_FKIndex1` (`workitem_id`),
  FOREIGN KEY (`workitem_id`)
    REFERENCES `workitem` (`workitem_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;


CREATE TABLE `workitem_comment` (
  `workitem_id` bigint(20) unsigned NOT NULL,
  `title` varchar(255) NOT NULL,
  `text` longtext NOT NULL,
  `reference_url` varchar(255) NOT NULL,
  `is_new` enum('true','false') NOT NULL default 'true',
  `comment_type` varchar(255) NOT NULL,
  KEY `workitem_comment_FKIndex1` (`workitem_id`),
  FOREIGN KEY (`workitem_id`)
    REFERENCES `workitem` (`workitem_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;


CREATE TABLE `tag_text` (
  `tag_id` bigint(20) unsigned NOT NULL auto_increment,
  `text` varchar(255) NOT NULL,
  PRIMARY KEY  (`tag_id`),
  UNIQUE KEY `text` (`text`)
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;


CREATE TABLE `issue` (
  `issue_id` bigint(20) unsigned NOT NULL auto_increment,
  `project_id` bigint(20) unsigned NOT NULL,
  `project_issue_id` bigint(20) unsigned NOT NULL,
  `title` varchar(255) NOT NULL,
  `private` tinyint(1) NOT NULL,
  PRIMARY KEY  (`issue_id`),
  UNIQUE KEY `project_id` (`project_id`,`project_issue_id`),
  KEY `issue_FKIndex1` (`project_id`),
  FOREIGN KEY (`project_id`)
    REFERENCES `project` (`project_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;


CREATE TABLE `issue_revision` (
  `issue_revision_id` bigint(20) unsigned NOT NULL auto_increment,
  `issue_id` bigint(20) unsigned NOT NULL,
  `creation_time` int(10) unsigned NOT NULL,
  `user_id` bigint(10) unsigned NOT NULL,
  `text` text NOT NULL,
  PRIMARY KEY  (`issue_revision_id`),
  KEY `issue_revision_FKIndex1` (`issue_id`),
  KEY `issue_revision_FKIndex2` (`user_id`),
  FOREIGN KEY (`user_id`)
    REFERENCES `user` (`user_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
  FOREIGN KEY (`user_id`)
    REFERENCES `user` (`user_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;


CREATE TABLE `issue_tag_association` (
  `issue_revision_id` bigint(20) unsigned NOT NULL,
  `tag_id` bigint(20) unsigned NOT NULL,
  KEY `issue_tag_association_FKIndex1` (`issue_revision_id`),
  KEY `issue_tag_association_FKIndex2` (`tag_id`),
  FOREIGN KEY (`issue_revision_id`)
    REFERENCES `issue_revision` (`issue_revision_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
  FOREIGN KEY (`tag_id`)
    REFERENCES `tag_text` (`tag_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;


CREATE TABLE `workitem_issue` (
  `workitem_id` bigint(20) unsigned NOT NULL,
  `issue_revision_id` bigint(20) unsigned NOT NULL,
  KEY `workitem_id_FKIndex1` (`workitem_id`),
  KEY `workitem_id_FKIndex2` (`issue_revision_id`),
  FOREIGN KEY (`issue_revision_id`)
    REFERENCES `issue_revision` (`issue_revision_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
  FOREIGN KEY (`workitem_id`)
    REFERENCES `workitem` (`workitem_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;

CREATE TABLE `project_request` (
  `request_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `name` VARCHAR(50) NOT NULL,
  `description` TEXT NOT NULL,
  `closed_source` enum('true','false') NOT NULL default 'false',
  `message` TEXT NOT NULL,
  PRIMARY KEY(request_id),
  FOREIGN KEY (`user_id`)
    REFERENCES `user` (`user_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;

INSERT INTO `project_request` (`request_id` ,`user_id` ,`name` ,`description` ,`closed_source` ,`message`)
VALUES ('1' , '1', 'api', 'Placeholder to prevent registration of that project name.', 'true', 'Placeholder to prevent 
registration of that project name.');

INSERT INTO `project_request` (`request_id` ,`user_id` ,`name` ,`description` ,`closed_source` ,`message`)
VALUES ('2' , '1', 'dev', 'Placeholder to prevent registration of that project name.', 'true', 'Placeholder to prevent
registration of that project name.');

INSERT INTO `project_request` (`request_id` ,`user_id` ,`name` ,`description` ,`closed_source` ,`message`)
VALUES ('3' , '1', 'download', 'Placeholder to prevent registration of that project name.', 'true', 'Placeholder to prevent
registration of that project name.');

INSERT INTO `project_request` (`request_id` ,`user_id` ,`name` ,`description` ,`closed_source` ,`message`)
VALUES ('4' , '1', 'login', 'Placeholder to prevent registration of that project name.', 'true', 'Placeholder to prevent
registration of that project name.');

INSERT INTO `project_request` (`request_id` ,`user_id` ,`name` ,`description` ,`closed_source` ,`message`)
VALUES ('5' , '1', 'pictures', 'Placeholder to prevent registration of that project name.', 'true', 'Placeholder to prevent
registration of that project name.');

INSERT INTO `project_request` (`request_id` ,`user_id` ,`name` ,`description` ,`closed_source` ,`message`)
VALUES ('6' , '1', 'static', 'Placeholder to prevent registration of that project name.', 'true', 'Placeholder to prevent
registration of that project name.');

INSERT INTO `project_request` (`request_id` ,`user_id` ,`name` ,`description` ,`closed_source` ,`message`)
VALUES ('7' , '1', 'svn', 'Placeholder to prevent registration of that project name.', 'true', 'Placeholder to prevent
registration of that project name.');

INSERT INTO `project_request` (`request_id` ,`user_id` ,`name` ,`description` ,`closed_source` ,`message`)
VALUES ('8' , '1', 'upload', 'Placeholder to prevent registration of that project name.', 'true', 'Placeholder to prevent
registration of that project name.');


CREATE TABLE `community` (
  `community_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL,
  `description` TEXT NOT NULL,
  `logo` VARCHAR(50) NOT NULL,
  KEY `name` (`name`),
  PRIMARY KEY(`community_id`)
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;


CREATE TABLE `user_community_association` (
  `community_id` BIGINT UNSIGNED NOT NULL,
  `group_id` TINYINT UNSIGNED NOT NULL,
  `user_id` BIGINT UNSIGNED NOT NULL,
  KEY user_community_association_FKIndex1(`user_id`),
  KEY user_community_association_FKIndex2(`group_id`),
  KEY user_community_association_FKIndex3(`community_id`),
  UNIQUE KEY community_user(`community_id`, `user_id`),
  FOREIGN KEY(`user_id`)
    REFERENCES user(`user_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
  FOREIGN KEY(`community_id`)
    REFERENCES community(`community_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
  FOREIGN KEY(`group_id`)
    REFERENCES access_group(`group_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;


CREATE TABLE `project_community_association` (
  `community_id` BIGINT UNSIGNED NOT NULL,
  `project_id` BIGINT UNSIGNED NOT NULL,
  KEY user_community_association_FKIndex1(`project_id`),
  KEY user_community_association_FKIndex2(`community_id`),
  UNIQUE KEY community_project(`community_id`, `project_id`),
  FOREIGN KEY(`project_id`)
    REFERENCES project(`project_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
  FOREIGN KEY(`community_id`)
    REFERENCES community(`community_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;



