-- http://www.phpmyadmin.net
--
--
-- Database: `gforge`
--
-- CREATE DATABASE `gforge` DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `canned_responses`
--

CREATE TABLE IF NOT EXISTS `canned_responses` (
  `response_id` int(11) NOT NULL auto_increment,
  `response_title` varchar(25) NOT NULL default '',
  `response_text` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`response_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `db_images`
--

CREATE TABLE IF NOT EXISTS `db_images` (
  `id` int(11) NOT NULL auto_increment,
  `group_id` int(11) NOT NULL default '0',
  `description` varchar(255) NOT NULL default '',
  `bin_data` mediumblob NOT NULL,
  `filename` varchar(25) NOT NULL default '',
  `filesize` int(11) NOT NULL default '0',
  `filetype` varchar(10) NOT NULL default '',
  `width` int(11) NOT NULL default '0',
  `height` int(11) NOT NULL default '0',
  `upload_date` int(11) default NULL,
  `version` int(11) default NULL,
  PRIMARY KEY  (`id`),
  KEY `db_images_group` (`group_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `doc_data`
--

CREATE TABLE IF NOT EXISTS `doc_data` (
  `docid` int(11) NOT NULL auto_increment,
  `stateid` int(11) NOT NULL default '0',
  `title` varchar(255) NOT NULL default '',
  `data` varchar(255) NOT NULL default '',
  `updatedate` int(11) NOT NULL default '0',
  `createdate` int(11) NOT NULL default '0',
  `created_by` int(11) NOT NULL default '0',
  `doc_group` int(11) NOT NULL default '0',
  `description` varchar(255) default NULL,
  `language_id` int(11) NOT NULL default '1',
  `filename` varchar(25) default NULL,
  `filetype` varchar(10) default NULL,
  `group_id` int(11) default NULL,
  `filesize` int(11) NOT NULL default '0',
  PRIMARY KEY  (`docid`),
  KEY `docdata_groupid` (`group_id`,`doc_group`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `doc_groups`
--

CREATE TABLE IF NOT EXISTS `doc_groups` (
  `doc_group` int(11) NOT NULL auto_increment,
  `groupname` varchar(25) NOT NULL default '',
  `group_id` int(11) NOT NULL default '0',
  `parent_doc_group` int(11) NOT NULL default '0',
  PRIMARY KEY  (`doc_group`),
  KEY `doc_groups_group` (`group_id`),
  KEY `docgroups_parentdocgroup` (`parent_doc_group`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `doc_states`
--

CREATE TABLE IF NOT EXISTS `doc_states` (
  `stateid` int(11) NOT NULL auto_increment,
  `name` varchar(25) NOT NULL default '',
  PRIMARY KEY  (`stateid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `filemodule_monitor`
--

CREATE TABLE IF NOT EXISTS `filemodule_monitor` (
  `id` int(11) NOT NULL auto_increment,
  `filemodule_id` int(11) NOT NULL default '0',
  `user_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `filemodulemonitor_useridfilemoduleid` (`user_id`,`filemodule_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `forum`
--

CREATE TABLE IF NOT EXISTS `forum` (
  `msg_id` int(11) NOT NULL auto_increment,
  `group_forum_id` int(11) NOT NULL default '0',
  `posted_by` int(11) NOT NULL default '0',
  `subject` varchar(100) NOT NULL default '',
  `body` text NOT NULL,
  `post_date` int(11) NOT NULL default '0',
  `is_followup_to` tinyint(1) NOT NULL default '0',
  `thread_id` int(11) NOT NULL default '0',
  `has_followups` int(11) default '0',
  `most_recent_date` int(11) NOT NULL default '0',
  PRIMARY KEY  (`msg_id`),
  KEY `group_forum_id` (`group_forum_id`,`msg_id`),
  KEY `forum_group_forum_id` (`group_forum_id`),
  KEY `forum_forumid_threadid_mostrecent` (`group_forum_id`,`thread_id`,`most_recent_date`),
  KEY `forum_threadid_isfollowupto` (`thread_id`,`is_followup_to`),
  KEY `forum_forumid_isfollto_mostrecent` (`group_forum_id`,`is_followup_to`,`most_recent_date`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `forum_agg_msg_count`
--

CREATE TABLE IF NOT EXISTS `forum_agg_msg_count` (
  `group_forum_id` int(11) NOT NULL default '0',
  `count` int(11) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `forum_group_list`
--

CREATE TABLE IF NOT EXISTS `forum_group_list` (
  `group_forum_id` int(11) NOT NULL auto_increment,
  `group_id` int(11) NOT NULL default '0',
  `forum_name` varchar(25) NOT NULL default '',
  `is_public` tinyint(1) NOT NULL default '0',
  `description` varchar(255) default NULL,
  `allow_anonymous` int(11) NOT NULL default '0',
  `send_all_posts_to` varchar(25) default NULL,
  `moderation_level` int(11) default '0',
  PRIMARY KEY  (`group_forum_id`),
  KEY `forum_group_list_group_id` (`group_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `forum_monitored_forums`
--

CREATE TABLE IF NOT EXISTS `forum_monitored_forums` (
  `monitor_id` int(11) NOT NULL auto_increment,
  `forum_id` int(11) NOT NULL default '0',
  `user_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`monitor_id`),
  KEY `forummonitoredforums_useridforumid` (`user_id`,`forum_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `forum_saved_place`
--

CREATE TABLE IF NOT EXISTS `forum_saved_place` (
  `saved_place_id` int(11) NOT NULL auto_increment,
  `user_id` int(11) NOT NULL default '0',
  `forum_id` int(11) NOT NULL default '0',
  `save_date` int(11) NOT NULL default '0',
  PRIMARY KEY  (`saved_place_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `frs_file`
--

CREATE TABLE IF NOT EXISTS `frs_file` (
  `file_id` int(11) NOT NULL auto_increment,
  `filename` varchar(25) default NULL,
  `release_id` int(11) NOT NULL default '0',
  `type_id` int(11) NOT NULL default '0',
  `processor_id` int(11) NOT NULL default '0',
  `release_time` int(11) NOT NULL default '0',
  `file_size` int(11) NOT NULL default '0',
  `post_date` int(11) NOT NULL default '0',
  PRIMARY KEY  (`file_id`),
  KEY `frs_file_date` (`post_date`),
  KEY `frs_file_release_id` (`release_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `frs_filetype`
--

CREATE TABLE IF NOT EXISTS `frs_filetype` (
  `type_id` int(11) NOT NULL auto_increment,
  `name` varchar(25) default NULL,
  PRIMARY KEY  (`type_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `frs_package`
--

CREATE TABLE IF NOT EXISTS `frs_package` (
  `package_id` int(11) NOT NULL auto_increment,
  `group_id` int(11) NOT NULL default '0',
  `name` varchar(25) default NULL,
  `status_id` int(11) NOT NULL default '0',
  `is_public` tinyint(1) default '1',
  PRIMARY KEY  (`package_id`),
  KEY `package_group_id` (`group_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `frs_processor`
--

CREATE TABLE IF NOT EXISTS `frs_processor` (
  `processor_id` int(11) NOT NULL auto_increment,
  `name` varchar(25) default NULL,
  PRIMARY KEY  (`processor_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `frs_release`
--

CREATE TABLE IF NOT EXISTS `frs_release` (
  `release_id` int(11) NOT NULL auto_increment,
  `package_id` int(11) NOT NULL default '0',
  `name` varchar(25) default NULL,
  `notes` varchar(255) default NULL,
  `changes` varchar(255) default NULL,
  `status_id` int(11) NOT NULL default '0',
  `preformatted` int(11) NOT NULL default '0',
  `release_date` int(11) NOT NULL default '0',
  `released_by` int(11) NOT NULL default '0',
  PRIMARY KEY  (`release_id`),
  KEY `frs_release_package` (`package_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `frs_status`
--

CREATE TABLE IF NOT EXISTS `frs_status` (
  `status_id` int(11) NOT NULL auto_increment,
  `name` varchar(25) default NULL,
  PRIMARY KEY  (`status_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `group_history`
--

CREATE TABLE IF NOT EXISTS `group_history` (
  `group_history_id` int(11) NOT NULL auto_increment,
  `group_id` int(11) NOT NULL default '0',
  `field_name` varchar(25) NOT NULL default '',
  `old_value` varchar(100) NOT NULL default '',
  `mod_by` int(11) NOT NULL default '0',
  `adddate` int(11) default NULL,
  PRIMARY KEY  (`group_history_id`),
  KEY `group_history_group_id` (`group_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `groups`
--

CREATE TABLE IF NOT EXISTS `groups` (
  `group_id` int(11) NOT NULL auto_increment,
  `group_name` varchar(40) default NULL,
  `homepage` varchar(128) default NULL,
  `is_public` tinyint(1) NOT NULL default '0',
  `status` char(1) NOT NULL default '',
  `unix_group_name` varchar(30) NOT NULL default '',
  `unix_box` varchar(20) NOT NULL default 'shell1',
  `http_domain` varchar(80) default NULL,
  `short_description` varchar(255) default NULL,
  `register_purpose` varchar(100) default NULL,
  `license_other` varchar(25) default NULL,
  `register_time` int(11) NOT NULL default '0',
  `rand_hash` varchar(32) default NULL,
  `use_mail` tinyint(1) NOT NULL default '1',
  `use_survey` tinyint(1) NOT NULL default '1',
  `use_forum` tinyint(1) NOT NULL default '1',
  `use_pm` tinyint(1) NOT NULL default '1',
  `use_scm` tinyint(1) NOT NULL default '1',
  `use_news` tinyint(1) NOT NULL default '1',
  `type_id` int(11) NOT NULL default '1',
  `use_docman` tinyint(1) NOT NULL default '1',
  `new_doc_address` varchar(100) NOT NULL default '',
  `send_all_docs` tinyint(1) NOT NULL default '0',
  `use_pm_depend_box` tinyint(1) NOT NULL default '1',
  `use_ftp` tinyint(1) default '1',
  `use_tracker` tinyint(1) default '1',
  `use_frs` tinyint(1) default '1',
  `use_stats` tinyint(1) default '1',
  `enable_pserver` tinyint(1) default '1',
  `enable_anonscm` tinyint(1) default '1',
  `license` int(11) default '100',
  `scm_box` varchar(80) default NULL,
  PRIMARY KEY  (`group_id`),
  UNIQUE KEY `group_unix_uniq` (`unix_group_name`),
  KEY `groups_type` (`type_id`),
  KEY `groups_public` (`is_public`),
  KEY `groups_status` (`status`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `mail_group_list`
--

CREATE TABLE IF NOT EXISTS `mail_group_list` (
  `group_list_id` int(11) NOT NULL auto_increment,
  `group_id` int(11) NOT NULL default '0',
  `list_name` varchar(25) default NULL,
  `is_public` tinyint(1) NOT NULL default '0',
  `password` varchar(16) default NULL,
  `list_admin` int(11) NOT NULL default '0',
  `status` int(11) NOT NULL default '0',
  `description` varchar(255) default NULL,
  PRIMARY KEY  (`group_list_id`),
  KEY `mail_group_list_group` (`group_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `news_bytes`
--

CREATE TABLE IF NOT EXISTS `news_bytes` (
  `id` int(11) NOT NULL auto_increment,
  `group_id` int(11) NOT NULL default '0',
  `submitted_by` int(11) NOT NULL default '0',
  `is_approved` tinyint(1) NOT NULL default '0',
  `post_date` int(11) NOT NULL default '0',
  `forum_id` int(11) NOT NULL default '0',
  `summary` text,
  `details` text,
  PRIMARY KEY  (`id`),
  KEY `news_bytes_group` (`group_id`),
  KEY `news_bytes_approved` (`is_approved`),
  KEY `news_bytes_forum` (`forum_id`),
  KEY `news_group_date` (`group_id`,`post_date`),
  KEY `news_approved_date` (`is_approved`,`post_date`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `people_job`
--

CREATE TABLE IF NOT EXISTS `people_job` (
  `job_id` int(11) NOT NULL auto_increment,
  `group_id` int(11) NOT NULL default '0',
  `created_by` int(11) NOT NULL default '0',
  `title` varchar(25) default NULL,
  `description` varchar(255) default NULL,
  `post_date` int(11) NOT NULL default '0',
  `status_id` int(11) NOT NULL default '0',
  `category_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`job_id`),
  KEY `people_job_group_id` (`group_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `people_job_category`
--

CREATE TABLE IF NOT EXISTS `people_job_category` (
  `category_id` int(11) NOT NULL auto_increment,
  `name` varchar(25) default NULL,
  `private_flag` int(11) NOT NULL default '0',
  PRIMARY KEY  (`category_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `people_job_inventory`
--

CREATE TABLE IF NOT EXISTS `people_job_inventory` (
  `job_inventory_id` int(11) NOT NULL auto_increment,
  `job_id` int(11) NOT NULL default '0',
  `skill_id` int(11) NOT NULL default '0',
  `skill_level_id` int(11) NOT NULL default '0',
  `skill_year_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`job_inventory_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `people_job_status`
--

CREATE TABLE IF NOT EXISTS `people_job_status` (
  `status_id` int(11) NOT NULL auto_increment,
  `name` varchar(25) default NULL,
  PRIMARY KEY  (`status_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `people_skill`
--

CREATE TABLE IF NOT EXISTS `people_skill` (
  `skill_id` int(11) NOT NULL auto_increment,
  `name` varchar(25) default NULL,
  PRIMARY KEY  (`skill_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `people_skill_inventory`
--

CREATE TABLE IF NOT EXISTS `people_skill_inventory` (
  `skill_inventory_id` int(11) NOT NULL auto_increment,
  `user_id` int(11) NOT NULL default '0',
  `skill_id` int(11) NOT NULL default '0',
  `skill_level_id` int(11) NOT NULL default '0',
  `skill_year_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`skill_inventory_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `people_skill_level`
--

CREATE TABLE IF NOT EXISTS `people_skill_level` (
  `skill_level_id` int(11) NOT NULL auto_increment,
  `name` varchar(25) default NULL,
  PRIMARY KEY  (`skill_level_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `people_skill_year`
--

CREATE TABLE IF NOT EXISTS `people_skill_year` (
  `skill_year_id` int(11) NOT NULL auto_increment,
  `name` varchar(25) default NULL,
  PRIMARY KEY  (`skill_year_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `project_assigned_to`
--

CREATE TABLE IF NOT EXISTS `project_assigned_to` (
  `project_assigned_id` int(11) NOT NULL auto_increment,
  `project_task_id` int(11) NOT NULL default '0',
  `assigned_to_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`project_assigned_id`),
  KEY `projectassigned_assignedtotaskid` (`assigned_to_id`,`project_task_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `project_dependencies`
--

CREATE TABLE IF NOT EXISTS `project_dependencies` (
  `project_depend_id` int(11) NOT NULL auto_increment,
  `project_task_id` int(11) NOT NULL default '0',
  `is_dependent_on_task_id` int(11) NOT NULL default '0',
  `link_type` char(2) default NULL,
  PRIMARY KEY  (`project_depend_id`),
  KEY `projectdep_isdepon_projtaskid` (`is_dependent_on_task_id`,`project_task_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `project_group_list`
--

CREATE TABLE IF NOT EXISTS `project_group_list` (
  `group_project_id` int(11) NOT NULL auto_increment,
  `group_id` int(11) NOT NULL default '0',
  `project_name` varchar(25) NOT NULL default '',
  `is_public` tinyint(1) NOT NULL default '0',
  `description` varchar(255) default NULL,
  `send_all_posts_to` varchar(25) default NULL,
  PRIMARY KEY  (`group_project_id`),
  KEY `project_group_list_group_id` (`group_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `project_history`
--

CREATE TABLE IF NOT EXISTS `project_history` (
  `project_history_id` int(11) NOT NULL auto_increment,
  `project_task_id` int(11) NOT NULL default '0',
  `field_name` varchar(25) NOT NULL default '',
  `old_value` varchar(25) NOT NULL default '',
  `mod_by` int(11) NOT NULL default '0',
  `mod_date` int(11) NOT NULL default '0',
  PRIMARY KEY  (`project_history_id`),
  KEY `project_history_task_id` (`project_task_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `project_metric`
--

CREATE TABLE IF NOT EXISTS `project_metric` (
  `ranking` int(11) NOT NULL auto_increment,
  `percentile` double default NULL,
  `group_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`ranking`),
  KEY `project_metric_group` (`group_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `project_metric_tmp1`
--

CREATE TABLE IF NOT EXISTS `project_metric_tmp1` (
  `ranking` int(11) NOT NULL auto_increment,
  `group_id` int(11) NOT NULL default '0',
  `value` double default NULL,
  PRIMARY KEY  (`ranking`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `project_status`
--

CREATE TABLE IF NOT EXISTS `project_status` (
  `status_id` int(11) NOT NULL auto_increment,
  `status_name` varchar(25) NOT NULL default '',
  PRIMARY KEY  (`status_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `project_task`
--

CREATE TABLE IF NOT EXISTS `project_task` (
  `project_task_id` int(11) NOT NULL auto_increment,
  `group_project_id` int(11) NOT NULL default '0',
  `summary` text NOT NULL,
  `details` text NOT NULL,
  `percent_complete` int(11) NOT NULL default '0',
  `priority` int(11) NOT NULL default '3',
  `hours` double NOT NULL default '0',
  `start_date` int(11) NOT NULL default '0',
  `end_date` int(11) NOT NULL default '0',
  `created_by` int(11) NOT NULL default '0',
  `status_id` int(11) NOT NULL default '0',
  `category_id` int(11) default NULL,
  `duration` int(11) default '0',
  `parent_id` int(11) default '0',
  `last_modified_date` int(11) default NULL,
  PRIMARY KEY  (`project_task_id`),
  KEY `projecttask_projid_status` (`group_project_id`,`status_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `project_weekly_metric`
--

CREATE TABLE IF NOT EXISTS `project_weekly_metric` (
  `ranking` int(11) NOT NULL auto_increment,
  `percentile` double default NULL,
  `group_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`ranking`),
  KEY `projectweeklymetric_ranking` (`ranking`),
  KEY `project_metric_weekly_group` (`group_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `user_session`
--

CREATE TABLE IF NOT EXISTS `user_session` (
  `user_id` int(11) NOT NULL default '0',
  `session_hash` char(32) NOT NULL default '',
  `ip_addr` char(15) NOT NULL default '',
  `time` int(11) NOT NULL default '0',
  PRIMARY KEY  (`user_id`),
  KEY `session_user_id` (`user_id`),
  KEY `session_time` (`time`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `snippet`
--

CREATE TABLE IF NOT EXISTS `snippet` (
  `snippet_id` int(11) NOT NULL auto_increment,
  `created_by` int(11) NOT NULL default '0',
  `name` varchar(25) default NULL,
  `description` varchar(255) default NULL,
  `type` int(11) NOT NULL default '0',
  `language` int(11) NOT NULL default '0',
  `license` varchar(25) NOT NULL default '',
  `category` int(11) NOT NULL default '0',
  PRIMARY KEY  (`snippet_id`),
  KEY `snippet_language` (`language`),
  KEY `snippet_category` (`category`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `snippet_package`
--

CREATE TABLE IF NOT EXISTS `snippet_package` (
  `snippet_package_id` int(11) NOT NULL auto_increment,
  `created_by` int(11) NOT NULL default '0',
  `name` varchar(25) default NULL,
  `description` varchar(255) default NULL,
  `category` int(11) NOT NULL default '0',
  `language` int(11) NOT NULL default '0',
  PRIMARY KEY  (`snippet_package_id`),
  KEY `snippet_package_language` (`language`),
  KEY `snippet_package_category` (`category`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `snippet_package_item`
--

CREATE TABLE IF NOT EXISTS `snippet_package_item` (
  `snippet_package_item_id` int(11) NOT NULL auto_increment,
  `snippet_package_version_id` int(11) NOT NULL default '0',
  `snippet_version_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`snippet_package_item_id`),
  KEY `snippet_package_item_pkg_ver` (`snippet_package_version_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `snippet_package_version`
--

CREATE TABLE IF NOT EXISTS `snippet_package_version` (
  `snippet_package_version_id` int(11) NOT NULL auto_increment,
  `snippet_package_id` int(11) NOT NULL default '0',
  `changes` varchar(255) default NULL,
  `version` varchar(25) default NULL,
  `submitted_by` int(11) NOT NULL default '0',
  `post_date` int(11) NOT NULL default '0',
  PRIMARY KEY  (`snippet_package_version_id`),
  KEY `snippet_package_version_pkg_id` (`snippet_package_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `snippet_version`
--

CREATE TABLE IF NOT EXISTS `snippet_version` (
  `snippet_version_id` int(11) NOT NULL auto_increment,
  `snippet_id` int(11) NOT NULL default '0',
  `changes` varchar(255) default NULL,
  `version` varchar(25) default NULL,
  `submitted_by` int(11) NOT NULL default '0',
  `post_date` int(11) NOT NULL default '0',
  `code` text,
  PRIMARY KEY  (`snippet_version_id`),
  KEY `snippet_version_snippet_id` (`snippet_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `stats_agg_logo_by_day`
--

CREATE TABLE IF NOT EXISTS `stats_agg_logo_by_day` (
  `day` int(11) default NULL,
  `count` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `stats_agg_pages_by_day`
--

CREATE TABLE IF NOT EXISTS `stats_agg_pages_by_day` (
  `day` int(11) NOT NULL default '0',
  `count` int(11) NOT NULL default '0',
  KEY `pages_by_day_day` (`day`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `stats_site_pages_by_month`
--

CREATE TABLE IF NOT EXISTS `stats_site_pages_by_month` (
  `month` int(11) default NULL,
  `site_page_views` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `survey_question_types`
--

CREATE TABLE IF NOT EXISTS `survey_question_types` (
  `id` int(11) NOT NULL auto_increment,
  `type` varchar(25) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `survey_questions`
--

CREATE TABLE IF NOT EXISTS `survey_questions` (
  `question_id` int(11) NOT NULL auto_increment,
  `group_id` int(11) NOT NULL default '0',
  `question` varchar(100) NOT NULL default '',
  `question_type` int(11) NOT NULL default '0',
  PRIMARY KEY  (`question_id`),
  KEY `survey_questions_group` (`group_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `survey_rating_aggregate`
--

CREATE TABLE IF NOT EXISTS `survey_rating_aggregate` (
  `type` int(11) NOT NULL default '0',
  `id` int(11) NOT NULL default '0',
  `response` double NOT NULL default '0',
  `count` int(11) NOT NULL default '0',
  KEY `survey_rating_aggregate_type_id` (`type`,`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `survey_rating_response`
--

CREATE TABLE IF NOT EXISTS `survey_rating_response` (
  `user_id` int(11) NOT NULL default '0',
  `type` int(11) NOT NULL default '0',
  `id` int(11) NOT NULL default '0',
  `response` int(11) NOT NULL default '0',
  `post_date` int(11) NOT NULL default '0',
  KEY `survey_rating_responses_user_ty` (`user_id`,`type`,`id`),
  KEY `survey_rating_responses_type_id` (`type`,`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `survey_responses`
--

CREATE TABLE IF NOT EXISTS `survey_responses` (
  `user_id` int(11) NOT NULL default '0',
  `group_id` int(11) NOT NULL default '0',
  `survey_id` int(11) NOT NULL default '0',
  `question_id` int(11) NOT NULL default '0',
  `response` varchar(100) NOT NULL default '',
  `post_date` int(11) NOT NULL default '0',
  KEY `survey_responses_group_id` (`group_id`),
  KEY `survey_responses_user_survey_qu` (`user_id`,`survey_id`,`question_id`),
  KEY `survey_responses_survey_questio` (`survey_id`,`question_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `surveys`
--

CREATE TABLE IF NOT EXISTS `surveys` (
  `survey_id` int(11) NOT NULL auto_increment,
  `group_id` int(11) NOT NULL default '0',
  `survey_title` varchar(100) NOT NULL default '',
  `survey_questions` varchar(100) NOT NULL default '',
  `is_active` tinyint(1) NOT NULL default '1',
  PRIMARY KEY  (`survey_id`),
  KEY `surveys_group` (`group_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `trove_cat`
--

CREATE TABLE IF NOT EXISTS `trove_cat` (
  `trove_cat_id` int(11) NOT NULL auto_increment,
  `version` int(11) NOT NULL default '0',
  `parent` int(11) NOT NULL default '0',
  `root_parent` int(11) NOT NULL default '0',
  `shortname` varchar(80) default NULL,
  `fullname` varchar(80) default NULL,
  `description` varchar(255) default NULL,
  `count_subcat` int(11) NOT NULL default '0',
  `count_subproj` int(11) NOT NULL default '0',
  `fullpath` text NOT NULL,
  `fullpath_ids` text,
  PRIMARY KEY  (`trove_cat_id`),
  KEY `trovecat_parentid` (`parent`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `trove_group_link`
--

CREATE TABLE IF NOT EXISTS `trove_group_link` (
  `trove_group_id` int(11) NOT NULL auto_increment,
  `trove_cat_id` int(11) NOT NULL default '0',
  `trove_cat_version` int(11) NOT NULL default '0',
  `group_id` int(11) NOT NULL default '0',
  `trove_cat_root` int(11) NOT NULL default '0',
  PRIMARY KEY  (`trove_group_id`),
  KEY `trovegrouplink_groupidcatid` (`group_id`,`trove_cat_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `user_bookmarks`
--

CREATE TABLE IF NOT EXISTS `user_bookmarks` (
  `bookmark_id` int(11) NOT NULL auto_increment,
  `user_id` int(11) NOT NULL default '0',
  `bookmark_url` varchar(100) default NULL,
  `bookmark_title` varchar(25) default NULL,
  PRIMARY KEY  (`bookmark_id`),
  KEY `user_bookmark_user_id` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `user_diary`
--

CREATE TABLE IF NOT EXISTS `user_diary` (
  `id` int(11) NOT NULL auto_increment,
  `user_id` int(11) NOT NULL default '0',
  `date_posted` int(11) NOT NULL default '0',
  `summary` text,
  `details` text,
  `is_public` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `user_diary_user_date` (`user_id`,`date_posted`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `user_diary_monitor`
--

CREATE TABLE IF NOT EXISTS `user_diary_monitor` (
  `monitor_id` int(11) NOT NULL auto_increment,
  `monitored_user` int(11) NOT NULL default '0',
  `user_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`monitor_id`),
  KEY `userdiarymon_useridmonitoredid` (`user_id`,`monitored_user`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `user_group`
--

CREATE TABLE IF NOT EXISTS `user_group` (
  `user_group_id` int(11) NOT NULL auto_increment,
  `user_id` int(11) NOT NULL default '0',
  `group_id` int(11) NOT NULL default '0',
  `admin_flags` char(16) NOT NULL default '',
  `forum_flags` int(11) NOT NULL default '0',
  `project_flags` int(11) NOT NULL default '2',
  `doc_flags` int(11) NOT NULL default '0',
  `cvs_flags` int(11) NOT NULL default '1',
  `member_role` int(11) NOT NULL default '100',
  `release_flags` int(11) NOT NULL default '0',
  `artifact_flags` int(11) default NULL,
  `role_id` int(11) default '1',
  PRIMARY KEY  (`user_group_id`),
  KEY `usergroup_useridgroupid` (`user_id`,`group_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `user_metric`
--

CREATE TABLE IF NOT EXISTS `user_metric` (
  `ranking` int(11) NOT NULL auto_increment,
  `user_id` int(11) NOT NULL default '0',
  `times_ranked` int(11) NOT NULL default '0',
  `avg_raters_importance` double NOT NULL default '0',
  `avg_rating` double NOT NULL default '0',
  `metric` double NOT NULL default '0',
  `percentile` double NOT NULL default '0',
  `importance_factor` double NOT NULL default '0',
  PRIMARY KEY  (`ranking`),
  UNIQUE KEY `usermetric_userid` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `user_metric0`
--

CREATE TABLE IF NOT EXISTS `user_metric0` (
  `ranking` int(11) NOT NULL auto_increment,
  `user_id` int(11) NOT NULL default '0',
  `times_ranked` int(11) NOT NULL default '0',
  `avg_raters_importance` double NOT NULL default '0',
  `avg_rating` double NOT NULL default '0',
  `metric` double NOT NULL default '0',
  `percentile` double NOT NULL default '0',
  `importance_factor` double NOT NULL default '0',
  PRIMARY KEY  (`ranking`),
  KEY `user_metric0_user_id` (`user_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `user_preferences`
--

CREATE TABLE IF NOT EXISTS `user_preferences` (
  `user_id` int(11) NOT NULL default '0',
  `preference_name` varchar(20) NOT NULL default '',
  `dead1` varchar(20) default NULL,
  `set_date` int(11) NOT NULL default '0',
  `preference_value` varchar(255) default NULL,
  PRIMARY KEY  (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `user_ratings`
--

CREATE TABLE IF NOT EXISTS `user_ratings` (
  `rated_by` int(11) NOT NULL default '0',
  `user_id` int(11) NOT NULL default '0',
  `rate_field` int(11) NOT NULL default '0',
  `rating` int(11) NOT NULL default '0',
  KEY `user_ratings_user_id` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `user_id` int(11) NOT NULL auto_increment,
  `user_name` varchar(25) NOT NULL default '',
  `email` text NOT NULL,
  `user_pw` varchar(32) NOT NULL default '',
  `realname` varchar(32) NOT NULL default '',
  `status` char(1) NOT NULL default 'A',
  `shell` varchar(20) NOT NULL default '/bin/bash',
  `unix_pw` varchar(40) NOT NULL default '',
  `unix_status` char(1) NOT NULL default 'N',
  `unix_uid` int(11) NOT NULL default '0',
  `unix_box` varchar(10) NOT NULL default 'shell1',
  `add_date` int(11) NOT NULL default '0',
  `confirm_hash` varchar(32) default NULL,
  `mail_siteupdates` int(11) NOT NULL default '0',
  `mail_va` int(11) NOT NULL default '0',
  `authorized_keys` varchar(100) default NULL,
  `email_new` varchar(25) default NULL,
  `people_view_skills` int(11) NOT NULL default '0',
  `people_resume` varchar(255) NOT NULL default '',
  `timezone` varchar(64) default 'GMT',
  `language` int(11) NOT NULL default '1',
  `block_ratings` int(11) default '0',
  `jabber_address` varchar(100) default NULL,
  `jabber_only` int(11) default NULL,
  `address` varchar(100) default NULL,
  `phone` varchar(25) default NULL,
  `fax` varchar(25) default NULL,
  `title` varchar(25) default NULL,
  `firstname` varchar(60) default NULL,
  `lastname` varchar(60) default NULL,
  `address2` varchar(100) default NULL,
  `ccode` char(2) default 'US',
  `theme_id` int(11) default NULL,
  `type_id` int(11) default '1',
  `unix_gid` int(11) default '0',
  PRIMARY KEY  (`user_id`),
  UNIQUE KEY `users_namename_uniq` (`user_name`),
  KEY `users_status` (`status`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `project_sums_agg`
--

CREATE TABLE IF NOT EXISTS `project_sums_agg` (
  `group_id` int(11) NOT NULL default '0',
  `type` char(4) NOT NULL default '',
  `count` int(11) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `prdb_dbs`
--

CREATE TABLE IF NOT EXISTS `prdb_dbs` (
  `dbid` int(11) NOT NULL auto_increment,
  `group_id` int(11) NOT NULL default '0',
  `dbname` varchar(255) NOT NULL default '',
  `dbusername` varchar(25) NOT NULL default '',
  `dbuserpass` varchar(32) NOT NULL default '',
  `requestdate` int(11) NOT NULL default '0',
  `dbtype` int(11) NOT NULL default '0',
  `created_by` int(11) NOT NULL default '0',
  `state` int(11) NOT NULL default '0',
  PRIMARY KEY  (`dbid`),
  UNIQUE KEY `idx_prdb_dbname` (`dbname`),
  KEY `prdbdbs_groupid` (`group_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `prdb_states`
--

CREATE TABLE IF NOT EXISTS `prdb_states` (
  `stateid` int(11) NOT NULL default '0',
  `statename` text,
  KEY `prdbstates_stateid` (`stateid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `prdb_types`
--

CREATE TABLE IF NOT EXISTS `prdb_types` (
  `dbtypeid` int(11) NOT NULL default '0',
  `dbservername` varchar(25) NOT NULL default '',
  `dbsoftware` varchar(25) NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `prweb_vhost`
--

CREATE TABLE IF NOT EXISTS `prweb_vhost` (
  `vhostid` int(11) NOT NULL auto_increment,
  `vhost_name` varchar(255) default NULL,
  `docdir` varchar(255) default NULL,
  `cgidir` varchar(255) default NULL,
  `group_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`vhostid`),
  UNIQUE KEY `idx_vhost_hostnames` (`vhost_name`),
  KEY `idx_vhost_groups` (`group_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `artifact_group_list`
--

CREATE TABLE IF NOT EXISTS `artifact_group_list` (
  `group_artifact_id` int(11) NOT NULL auto_increment,
  `group_id` int(11) NOT NULL default '0',
  `name` text,
  `description` text,
  `is_public` tinyint(1) NOT NULL default '0',
  `allow_anon` tinyint(1) NOT NULL default '0',
  `email_all_updates` tinyint(1) NOT NULL default '0',
  `email_address` text NOT NULL,
  `due_period` int(11) NOT NULL default '2592000',
  `submit_instructions` text,
  `browse_instructions` text,
  `datatype` int(11) NOT NULL default '0',
  `status_timeout` int(11) default NULL,
  `custom_status_field` int(11) NOT NULL default '0',
  `custom_renderer` text,
  PRIMARY KEY  (`group_artifact_id`),
  KEY `artgrouplist_groupid_public` (`group_id`,`is_public`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `artifact_perm`
--

CREATE TABLE IF NOT EXISTS `artifact_perm` (
  `id` int(11) NOT NULL auto_increment,
  `group_artifact_id` int(11) NOT NULL default '0',
  `user_id` int(11) NOT NULL default '0',
  `perm_level` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `artperm_groupartifactid_userid` (`group_artifact_id`,`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


CREATE OR REPLACE VIEW `artifactperm_user_vw` AS
    SELECT ap.id, ap.group_artifact_id, ap.user_id, ap.perm_level, users.user_name, users.realname
	FROM artifact_perm AS ap, users
	WHERE users.user_id = ap.user_id;


CREATE OR REPLACE VIEW `artifactperm_artgrouplist_vw` AS
    SELECT agl.group_artifact_id, agl.name, agl.description, agl.group_id, ap.user_id, ap.perm_level
	FROM artifact_perm AS ap, artifact_group_list AS agl
	WHERE ap.group_artifact_id = agl.group_artifact_id;


-- --------------------------------------------------------

--
-- Table structure for table `artifact_status`
--

CREATE TABLE IF NOT EXISTS `artifact_status` (
  `id` int(11) NOT NULL auto_increment,
  `status_name` varchar(10) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `artifact`
--

CREATE TABLE IF NOT EXISTS `artifact` (
  `artifact_id` int(11) NOT NULL auto_increment,
  `group_artifact_id` int(11) NOT NULL default '0',
  `status_id` int(11) NOT NULL default '1',
  `priority` int(11) NOT NULL default '3',
  `submitted_by` int(11) NOT NULL default '100',
  `assigned_to` int(11) NOT NULL default '100',
  `open_date` int(11) NOT NULL default '0',
  `close_date` int(11) NOT NULL default '0',
  `summary` text NOT NULL,
  `details` text NOT NULL,
  `last_modified_date` int(11) default NULL,
  PRIMARY KEY  (`artifact_id`),
  KEY `art_groupartid` (`group_artifact_id`),
  KEY `art_groupartid_statusid` (`group_artifact_id`,`status_id`),
  KEY `art_groupartid_assign` (`group_artifact_id`,`assigned_to`),
  KEY `art_groupartid_submit` (`group_artifact_id`,`submitted_by`),
  KEY `art_submit_status` (`submitted_by`,`status_id`),
  KEY `art_assign_status` (`assigned_to`,`status_id`),
  KEY `art_groupartid_artifactid` (`group_artifact_id`,`artifact_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `artifact_history`
--

CREATE TABLE IF NOT EXISTS `artifact_history` (
  `id` int(11) NOT NULL auto_increment,
  `artifact_id` int(11) NOT NULL default '0',
  `field_name` text NOT NULL,
  `old_value` text NOT NULL,
  `mod_by` int(11) NOT NULL default '0',
  `entrydate` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `arthistory_artid_entrydate` (`artifact_id`,`entrydate`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


CREATE OR REPLACE VIEW `artifact_history_user_vw` AS
    SELECT ah.id, ah.artifact_id, ah.field_name, ah.old_value, ah.entrydate, users.user_name
	FROM artifact_history AS ah, users
	WHERE ah.mod_by = users.user_id;


-- --------------------------------------------------------

--
-- Table structure for table `artifact_file`
--

CREATE TABLE IF NOT EXISTS `artifact_file` (
  `id` int(11) NOT NULL auto_increment,
  `artifact_id` int(11) NOT NULL default '0',
  `description` text NOT NULL,
  `bin_data` mediumblob NOT NULL,
  `filename` text NOT NULL,
  `filesize` int(11) NOT NULL default '0',
  `filetype` text NOT NULL,
  `adddate` int(11) NOT NULL default '0',
  `submitted_by` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `artfile_artid_adddate` (`artifact_id`,`adddate`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


CREATE OR REPLACE VIEW `artifact_file_user_vw` AS
    SELECT af.id, af.artifact_id, af.description, af.bin_data, af.filename, af.filesize, af.filetype, af.adddate, af.submitted_by, users.user_name, users.realname
	FROM artifact_file AS af, users
	WHERE af.submitted_by = users.user_id;


-- --------------------------------------------------------

--
-- Table structure for table `artifact_message`
--

CREATE TABLE IF NOT EXISTS `artifact_message` (
  `id` int(11) NOT NULL auto_increment,
  `artifact_id` int(11) NOT NULL default '0',
  `submitted_by` int(11) NOT NULL default '0',
  `from_email` text NOT NULL,
  `adddate` int(11) NOT NULL default '0',
  `body` text NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `artmessage_artid_adddate` (`artifact_id`,`adddate`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


CREATE OR REPLACE VIEW `artifact_message_user_vw` AS
    SELECT am.id, am.artifact_id, am.from_email, am.body, am.adddate, users.user_id, users.email, users.user_name, users.realname
	FROM artifact_message AS am, users
	WHERE am.submitted_by = users.user_id;


-- --------------------------------------------------------

--
-- Table structure for table `artifact_monitor`
--

CREATE TABLE IF NOT EXISTS `artifact_monitor` (
  `id` int(11) NOT NULL auto_increment,
  `artifact_id` int(11) NOT NULL default '0',
  `user_id` int(11) NOT NULL default '0',
  `email` text,
  PRIMARY KEY  (`id`),
  KEY `artmonitor_useridartid` (`user_id`,`artifact_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `artifact_canned_responses`
--

CREATE TABLE IF NOT EXISTS `artifact_canned_responses` (
  `id` int(11) NOT NULL auto_increment,
  `group_artifact_id` int(11) NOT NULL default '0',
  `title` text NOT NULL,
  `body` text NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `artifactcannedresponses_groupid` (`group_artifact_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `artifact_counts_agg`
--

CREATE TABLE IF NOT EXISTS `artifact_counts_agg` (
  `group_artifact_id` int(11) NOT NULL default '0',
  `count` int(11) NOT NULL default '0',
  `open_count` int(11) default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `stats_site_pages_by_day`
--

CREATE TABLE IF NOT EXISTS `stats_site_pages_by_day` (
  `month` int(11) default NULL,
  `day` int(11) default NULL,
  `site_page_views` int(11) default NULL,
  KEY `statssitepagesbyday_month_day` (`month`,`day`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `massmail_queue`
--

CREATE TABLE IF NOT EXISTS `massmail_queue` (
  `id` int(11) NOT NULL auto_increment,
  `type` varchar(8) NOT NULL default '',
  `subject` varchar(100) NOT NULL default '',
  `message` varchar(255) NOT NULL default '',
  `queued_date` int(11) NOT NULL default '0',
  `last_userid` int(11) NOT NULL default '0',
  `failed_date` int(11) NOT NULL default '0',
  `finished_date` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `stats_agg_site_by_group`
--

CREATE TABLE IF NOT EXISTS `stats_agg_site_by_group` (
  `month` int(11) default NULL,
  `day` int(11) default NULL,
  `group_id` int(11) default NULL,
  `count` int(11) default NULL,
  UNIQUE KEY `statssitebygroup_month_day_group` (`month`,`day`,`group_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `stats_project_metric`
--

CREATE TABLE IF NOT EXISTS `stats_project_metric` (
  `month` int(11) NOT NULL default '0',
  `day` int(11) NOT NULL default '0',
  `ranking` int(11) NOT NULL default '0',
  `percentile` double NOT NULL default '0',
  `group_id` int(11) NOT NULL default '0',
  UNIQUE KEY `statsprojectmetric_month_day_group` (`month`,`day`,`group_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `stats_agg_logo_by_group`
--

CREATE TABLE IF NOT EXISTS `stats_agg_logo_by_group` (
  `month` int(11) default NULL,
  `day` int(11) default NULL,
  `group_id` int(11) default NULL,
  `count` int(11) default NULL,
  UNIQUE KEY `statslogobygroup_month_day_grou` (`month`,`day`,`group_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `stats_subd_pages`
--

CREATE TABLE IF NOT EXISTS `stats_subd_pages` (
  `month` int(11) NOT NULL default '0',
  `day` int(11) NOT NULL default '0',
  `group_id` int(11) NOT NULL default '0',
  `pages` int(11) NOT NULL default '0',
  UNIQUE KEY `statssubdpages_month_day_group` (`month`,`day`,`group_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `stats_cvs_user`
--

CREATE TABLE IF NOT EXISTS `stats_cvs_user` (
  `month` int(11) NOT NULL default '0',
  `day` int(11) NOT NULL default '0',
  `group_id` int(11) NOT NULL default '0',
  `user_id` int(11) NOT NULL default '0',
  `checkouts` int(11) NOT NULL default '0',
  `commits` int(11) NOT NULL default '0',
  `adds` int(11) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `stats_cvs_group`
--

CREATE TABLE IF NOT EXISTS `stats_cvs_group` (
  `month` int(11) NOT NULL default '0',
  `day` int(11) NOT NULL default '0',
  `group_id` int(11) NOT NULL default '0',
  `checkouts` int(11) NOT NULL default '0',
  `commits` int(11) NOT NULL default '0',
  `adds` int(11) NOT NULL default '0',
  UNIQUE KEY `statscvsgroup_month_day_group` (`month`,`day`,`group_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `stats_project_developers`
--

CREATE TABLE IF NOT EXISTS `stats_project_developers` (
  `month` int(11) NOT NULL default '0',
  `day` int(11) NOT NULL default '0',
  `group_id` int(11) NOT NULL default '0',
  `developers` int(11) NOT NULL default '0',
  UNIQUE KEY `statsprojectdev_month_day_group` (`month`,`day`,`group_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `stats_project`
--

CREATE TABLE IF NOT EXISTS `stats_project` (
  `month` int(11) NOT NULL default '0',
  `day` int(11) NOT NULL default '0',
  `group_id` int(11) NOT NULL default '0',
  `file_releases` int(11) default '0',
  `msg_posted` int(11) default '0',
  `msg_uniq_auth` int(11) default '0',
  `bugs_opened` int(11) default '0',
  `bugs_closed` int(11) default '0',
  `support_opened` int(11) default '0',
  `support_closed` int(11) default '0',
  `patches_opened` int(11) default '0',
  `patches_closed` int(11) default '0',
  `artifacts_opened` int(11) default '0',
  `artifacts_closed` int(11) default '0',
  `tasks_opened` int(11) default '0',
  `tasks_closed` int(11) default '0',
  `help_requests` int(11) default '0',
  UNIQUE KEY `statsproject_month_day_group` (`month`,`day`,`group_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `stats_site`
--

CREATE TABLE IF NOT EXISTS `stats_site` (
  `month` int(11) default NULL,
  `day` int(11) default NULL,
  `uniq_users` int(11) default NULL,
  `sessions` int(11) default NULL,
  `total_users` int(11) default NULL,
  `new_users` int(11) default NULL,
  `new_projects` int(11) default NULL,
  UNIQUE KEY `statssite_month_day` (`month`,`day`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `activity_log`
--

CREATE TABLE IF NOT EXISTS `activity_log` (
  `day` int(11) NOT NULL default '0',
  `hour` int(11) NOT NULL default '0',
  `group_id` int(11) NOT NULL default '0',
  `browser` varchar(8) NOT NULL default 'OTHER',
  `ver` double NOT NULL default '0',
  `platform` varchar(8) NOT NULL default 'OTHER',
  `time` int(11) NOT NULL default '0',
  `page` text,
  `type` int(11) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `user_metric_history`
--

CREATE TABLE IF NOT EXISTS `user_metric_history` (
  `month` int(11) NOT NULL default '0',
  `day` int(11) NOT NULL default '0',
  `user_id` int(11) NOT NULL default '0',
  `ranking` int(11) NOT NULL default '0',
  `metric` double NOT NULL default '0',
  KEY `usermetrichistory_useridmonthday` (`user_id`,`month`,`day`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `frs_dlstats_filetotal_agg`
--

CREATE TABLE IF NOT EXISTS `frs_dlstats_filetotal_agg` (
  `file_id` int(11) NOT NULL default '0',
  `downloads` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `stats_project_months`
--

CREATE TABLE IF NOT EXISTS `stats_project_months` (
  `month` int(11) default NULL,
  `group_id` int(11) default NULL,
  `developers` int(11) default NULL,
  `group_ranking` int(11) default NULL,
  `group_metric` double default NULL,
  `logo_showings` int(11) default NULL,
  `downloads` int(11) default NULL,
  `site_views` int(11) default NULL,
  `subdomain_views` int(11) default NULL,
  `page_views` int(11) default NULL,
  `file_releases` int(11) default NULL,
  `msg_posted` int(11) default NULL,
  `msg_uniq_auth` int(11) default NULL,
  `bugs_opened` int(11) default NULL,
  `bugs_closed` int(11) default NULL,
  `support_opened` int(11) default NULL,
  `support_closed` int(11) default NULL,
  `patches_opened` int(11) default NULL,
  `patches_closed` int(11) default NULL,
  `artifacts_opened` int(11) default NULL,
  `artifacts_closed` int(11) default NULL,
  `tasks_opened` int(11) default NULL,
  `tasks_closed` int(11) default NULL,
  `help_requests` int(11) default NULL,
  `cvs_checkouts` int(11) default NULL,
  `cvs_commits` int(11) default NULL,
  `cvs_adds` int(11) default NULL,
  KEY `statsprojectmonths_groupid` (`group_id`),
  KEY `statsprojectmonths_groupid_mont` (`group_id`,`month`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `stats_site_months`
--

CREATE TABLE IF NOT EXISTS `stats_site_months` (
  `month` int(11) default NULL,
  `site_page_views` int(11) default NULL,
  `downloads` int(11) default NULL,
  `subdomain_views` int(11) default NULL,
  `msg_posted` int(11) default NULL,
  `bugs_opened` int(11) default NULL,
  `bugs_closed` int(11) default NULL,
  `support_opened` int(11) default NULL,
  `support_closed` int(11) default NULL,
  `patches_opened` int(11) default NULL,
  `patches_closed` int(11) default NULL,
  `artifacts_opened` int(11) default NULL,
  `artifacts_closed` int(11) default NULL,
  `tasks_opened` int(11) default NULL,
  `tasks_closed` int(11) default NULL,
  `help_requests` int(11) default NULL,
  `cvs_checkouts` int(11) default NULL,
  `cvs_commits` int(11) default NULL,
  `cvs_adds` int(11) default NULL,
  KEY `statssitemonths_month` (`month`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `trove_agg`
--

CREATE TABLE IF NOT EXISTS `trove_agg` (
  `trove_cat_id` int(11) default NULL,
  `group_id` int(11) default NULL,
  `group_name` varchar(40) default NULL,
  `unix_group_name` varchar(30) default NULL,
  `status` char(1) default NULL,
  `register_time` int(11) default NULL,
  `short_description` varchar(255) default NULL,
  `percentile` double default NULL,
  `ranking` int(11) default NULL,
  KEY `troveagg_trovecatid_ranking` (`trove_cat_id`,`ranking`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `trove_treesums`
--

CREATE TABLE IF NOT EXISTS `trove_treesums` (
  `trove_treesums_id` int(11) NOT NULL auto_increment,
  `trove_cat_id` int(11) NOT NULL default '0',
  `limit_1` int(11) NOT NULL default '0',
  `subprojects` int(11) NOT NULL default '0',
  PRIMARY KEY  (`trove_treesums_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `frs_dlstats_file`
--

CREATE TABLE IF NOT EXISTS `frs_dlstats_file` (
  `ip_address` varchar(25) default NULL,
  `file_id` int(11) default NULL,
  `month` int(11) default NULL,
  `day` int(11) default NULL,
  `user_id` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `group_cvs_history`
--

CREATE TABLE IF NOT EXISTS `group_cvs_history` (
  `id` int(11) NOT NULL auto_increment,
  `group_id` int(11) NOT NULL default '0',
  `user_name` varchar(80) NOT NULL default '',
  `cvs_commits` int(11) NOT NULL default '0',
  `cvs_commits_wk` int(11) NOT NULL default '0',
  `cvs_adds` int(11) NOT NULL default '0',
  `cvs_adds_wk` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `groupcvshistory_groupid` (`group_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `themes`
--

CREATE TABLE IF NOT EXISTS `themes` (
  `theme_id` int(11) NOT NULL auto_increment,
  `dirname` varchar(80) default NULL,
  `fullname` varchar(80) default NULL,
  `enabled` tinyint(1) NOT NULL default '1',
  PRIMARY KEY  (`theme_id`),
  UNIQUE KEY `themes_theme_id_key` (`theme_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `supported_languages`
--

CREATE TABLE IF NOT EXISTS `supported_languages` (
  `language_id` int(11) NOT NULL auto_increment,
  `name` text,
  `filename` varchar(25) default NULL,
  `classname` varchar(25) default NULL,
  `language_code` varchar(5) default NULL,
  PRIMARY KEY  (`language_id`),
  UNIQUE KEY `supportedlanguage_code` (`language_code`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `skills_data_types`
--

CREATE TABLE IF NOT EXISTS `skills_data_types` (
  `type_id` int(11) NOT NULL auto_increment,
  `type_name` varchar(25) NOT NULL default '',
  PRIMARY KEY  (`type_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `skills_data`
--

CREATE TABLE IF NOT EXISTS `skills_data` (
  `skills_data_id` int(11) NOT NULL auto_increment,
  `user_id` int(11) NOT NULL default '0',
  `type` int(11) NOT NULL default '0',
  `title` varchar(100) NOT NULL default '',
  `start` int(11) NOT NULL default '0',
  `finish` int(11) NOT NULL default '0',
  `keywords` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`skills_data_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


CREATE OR REPLACE VIEW `frs_file_vw` AS
    SELECT frs_file.file_id, frs_file.filename, frs_file.release_id, frs_file.type_id, frs_file.processor_id, frs_file.release_time, frs_file.file_size, frs_file.post_date, frs_filetype.name AS filetype, frs_processor.name AS processor, frs_dlstats_filetotal_agg.downloads
	FROM frs_filetype, frs_processor, (frs_file LEFT JOIN frs_dlstats_filetotal_agg ON frs_dlstats_filetotal_agg.file_id = frs_file.file_id)
	WHERE frs_filetype.type_id = frs_file.type_id AND frs_processor.processor_id = frs_file.processor_id;


-- --------------------------------------------------------

--
-- Table structure for table `project_category`
--

CREATE TABLE IF NOT EXISTS `project_category` (
  `category_id` int(11) NOT NULL auto_increment,
  `group_project_id` int(11) default NULL,
  `category_name` varchar(25) default NULL,
  PRIMARY KEY  (`category_id`),
  UNIQUE KEY `project_categor_category_id_key` (`category_id`),
  KEY `projectcategory_groupprojectid` (`group_project_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `project_task_artifact`
--

CREATE TABLE IF NOT EXISTS `project_task_artifact` (
  `project_task_id` int(11) NOT NULL default '0',
  `artifact_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`project_task_id`),
  KEY `projecttaskartifact_artidprojtaskid` (`artifact_id`,`project_task_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


CREATE OR REPLACE VIEW `project_history_user_vw` AS
    SELECT users.realname, users.email, users.user_name, project_history.project_history_id, project_history.project_task_id, project_history.field_name, project_history.old_value, project_history.mod_by, project_history.mod_date
	FROM users, project_history
	WHERE project_history.mod_by = users.user_id;


-- --------------------------------------------------------

--
-- Table structure for table `project_messages`
--

CREATE TABLE IF NOT EXISTS `project_messages` (
  `project_message_id` int(11) NOT NULL auto_increment,
  `project_task_id` int(11) NOT NULL default '0',
  `body` varchar(255) default NULL,
  `posted_by` int(11) NOT NULL default '0',
  `postdate` int(11) NOT NULL default '0',
  PRIMARY KEY  (`project_message_id`),
  UNIQUE KEY `project_messa_project_messa_key` (`project_message_id`),
  KEY `projectmsgs_projtaskidpostdate` (`project_task_id`,`postdate`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


CREATE OR REPLACE VIEW `project_message_user_vw` AS
    SELECT users.realname, users.email, users.user_name, project_messages.project_message_id, project_messages.project_task_id, project_messages.body, project_messages.posted_by, project_messages.postdate
	FROM users, project_messages
	WHERE project_messages.posted_by = users.user_id;


CREATE OR REPLACE VIEW `frs_dlstats_file_agg_vw` AS
    SELECT frs_dlstats_file.`month`, frs_dlstats_file.`day`, frs_dlstats_file.file_id, count(*) AS downloads
	FROM frs_dlstats_file
	GROUP BY frs_dlstats_file.`month`, frs_dlstats_file.`day`, frs_dlstats_file.file_id;


CREATE OR REPLACE VIEW `frs_dlstats_grouptotal_vw` AS
    SELECT frs_package.group_id, sum(frs_dlstats_filetotal_agg.downloads) AS downloads
	FROM frs_package, frs_release, frs_file, frs_dlstats_filetotal_agg
	WHERE frs_package.package_id = frs_release.package_id AND frs_release.release_id = frs_file.release_id AND frs_file.file_id = frs_dlstats_filetotal_agg.file_id
	GROUP BY frs_package.group_id;


CREATE OR REPLACE VIEW `frs_dlstats_group_vw` AS
    SELECT frs_package.group_id, fdfa.`month`, fdfa.`day`, sum(fdfa.downloads) AS downloads
	FROM frs_package, frs_release, frs_file, frs_dlstats_file_agg_vw AS fdfa
	WHERE frs_package.package_id = frs_release.package_id AND frs_release.release_id = frs_file.release_id AND frs_file.file_id = fdfa.file_id
	GROUP BY frs_package.group_id, fdfa.`month`, fdfa.`day`;


CREATE OR REPLACE VIEW `stats_project_vw` AS
    SELECT spd.group_id, spd.`month`, spd.`day`, spd.developers, spm.ranking AS group_ranking, spm.percentile AS group_metric, salbg.count AS logo_showings, fdga.downloads, sasbg.count AS site_views, ssp.pages AS subdomain_views, (CASE WHEN (sasbg.count IS NOT NULL) THEN sasbg.count WHEN (0 IS NOT NULL) THEN 0 ELSE NULL END + CASE WHEN (ssp.pages IS NOT NULL) THEN ssp.pages WHEN (0 IS NOT NULL) THEN 0 ELSE NULL END) AS page_views, sp.file_releases, sp.msg_posted, sp.msg_uniq_auth, sp.bugs_opened, sp.bugs_closed, sp.support_opened, sp.support_closed, sp.patches_opened, sp.patches_closed, sp.artifacts_opened, sp.artifacts_closed, sp.tasks_opened, sp.tasks_closed, sp.help_requests, scg.checkouts AS cvs_checkouts, scg.commits AS cvs_commits, scg.adds AS cvs_adds
	FROM (((((((stats_project_developers AS spd
		LEFT JOIN stats_project AS sp USING (`month`, `day`, group_id))
		LEFT JOIN stats_project_metric AS spm USING (`month`, `day`, group_id))
		LEFT JOIN stats_cvs_group AS scg USING (`month`, `day`, group_id))
		LEFT JOIN stats_agg_site_by_group AS sasbg USING (`month`, `day`, group_id))
		LEFT JOIN stats_agg_logo_by_group AS salbg USING (`month`, `day`, group_id))
		LEFT JOIN stats_subd_pages AS ssp USING (`month`, `day`, group_id))
		LEFT JOIN frs_dlstats_group_vw AS fdga USING (`month`, `day`, group_id));


CREATE OR REPLACE VIEW `stats_project_all_vw` AS
    SELECT
		stats_project_months.group_id,
		avg(stats_project_months.developers) AS developers,
		avg(stats_project_months.group_ranking) AS group_ranking,
		avg(stats_project_months.group_metric) AS group_metric,
		sum(stats_project_months.logo_showings) AS logo_showings,
		sum(stats_project_months.downloads) AS downloads,
		sum(stats_project_months.site_views) AS site_views,
		sum(stats_project_months.subdomain_views) AS subdomain_views,
		sum(stats_project_months.page_views) AS page_views,
		sum(stats_project_months.file_releases) AS file_releases,
		sum(stats_project_months.msg_posted) AS msg_posted,
		avg(stats_project_months.msg_uniq_auth) AS msg_uniq_auth,
		sum(stats_project_months.bugs_opened) AS bugs_opened,
		sum(stats_project_months.bugs_closed) AS bugs_closed,
		sum(stats_project_months.support_opened) AS support_opened,
		sum(stats_project_months.support_closed) AS support_closed,
		sum(stats_project_months.patches_opened) AS patches_opened,
		sum(stats_project_months.patches_closed) AS patches_closed,
		sum(stats_project_months.artifacts_opened) AS artifacts_opened,
		sum(stats_project_months.artifacts_closed) AS artifacts_closed,
		sum(stats_project_months.tasks_opened) AS tasks_opened,
		sum(stats_project_months.tasks_closed) AS tasks_closed,
		sum(stats_project_months.help_requests) AS help_requests,
		sum(stats_project_months.cvs_checkouts) AS cvs_checkouts,
		sum(stats_project_months.cvs_commits) AS cvs_commits,
		sum(stats_project_months.cvs_adds) AS cvs_adds
	FROM stats_project_months
	GROUP BY stats_project_months.group_id;


CREATE OR REPLACE VIEW `stats_site_vw` AS
    SELECT p.`month`, p.`day`, sspbd.site_page_views, sum(p.downloads) AS downloads, sum(p.subdomain_views) AS subdomain_views, sum(p.msg_posted) AS msg_posted, sum(p.bugs_opened) AS bugs_opened, sum(p.bugs_closed) AS bugs_closed, sum(p.support_opened) AS support_opened, sum(p.support_closed) AS support_closed, sum(p.patches_opened) AS patches_opened, sum(p.patches_closed) AS patches_closed, sum(p.artifacts_opened) AS artifacts_opened, sum(p.artifacts_closed) AS artifacts_closed, sum(p.tasks_opened) AS tasks_opened, sum(p.tasks_closed) AS tasks_closed, sum(p.help_requests) AS help_requests, sum(p.cvs_checkouts) AS cvs_checkouts, sum(p.cvs_commits) AS cvs_commits, sum(p.cvs_adds) AS cvs_adds
	FROM stats_project_vw AS p, stats_site_pages_by_day AS sspbd
	WHERE p.`month` = sspbd.`month` AND p.`day` = sspbd.`day`
	GROUP BY p.`month`, p.`day`, sspbd.site_page_views;


CREATE OR REPLACE VIEW `stats_site_all_vw` AS
    SELECT sum(stats_site_months.site_page_views) AS site_page_views, sum(stats_site_months.downloads) AS downloads, sum(stats_site_months.subdomain_views) AS subdomain_views, sum(stats_site_months.msg_posted) AS msg_posted, sum(stats_site_months.bugs_opened) AS bugs_opened, sum(stats_site_months.bugs_closed) AS bugs_closed, sum(stats_site_months.support_opened) AS support_opened, sum(stats_site_months.support_closed) AS support_closed, sum(stats_site_months.patches_opened) AS patches_opened, sum(stats_site_months.patches_closed) AS patches_closed, sum(stats_site_months.artifacts_opened) AS artifacts_opened, sum(stats_site_months.artifacts_closed) AS artifacts_closed, sum(stats_site_months.tasks_opened) AS tasks_opened, sum(stats_site_months.tasks_closed) AS tasks_closed, sum(stats_site_months.help_requests) AS help_requests, sum(stats_site_months.cvs_checkouts) AS cvs_checkouts, sum(stats_site_months.cvs_commits) AS cvs_commits, sum(stats_site_months.cvs_adds) AS cvs_adds
	FROM stats_site_months;


-- --------------------------------------------------------

--
-- Table structure for table `plugins`
--

CREATE TABLE IF NOT EXISTS `plugins` (
  `plugin_id` int(11) NOT NULL auto_increment,
  `plugin_name` varchar(32) NOT NULL default '',
  `plugin_desc` varchar(255) default NULL,
  PRIMARY KEY  (`plugin_id`),
  UNIQUE KEY `plugins_plugin_name_key` (`plugin_name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `group_plugin`
--

CREATE TABLE IF NOT EXISTS `group_plugin` (
  `group_plugin_id` int(11) NOT NULL auto_increment,
  `group_id` int(11) default NULL,
  `plugin_id` int(11) default NULL,
  PRIMARY KEY  (`group_plugin_id`),
  KEY `groupplugin_groupid` (`group_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `user_plugin`
--

CREATE TABLE IF NOT EXISTS `user_plugin` (
  `user_plugin_id` int(11) NOT NULL auto_increment,
  `user_id` int(11) NOT NULL default '0',
  `plugin_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`user_plugin_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `cron_history`
--

CREATE TABLE IF NOT EXISTS `cron_history` (
  `rundate` int(11) NOT NULL default '0',
  `job` varchar(255) default NULL,
  `output` text default NULL,
  KEY `cronhist_rundate` (`rundate`),
  KEY `cronhist_jobrundate` (`job`,`rundate`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


-- --------------------------------------------------------

--
-- Table structure for table `country_code`
--

CREATE TABLE IF NOT EXISTS `country_code` (
  `country_name` varchar(80) default NULL,
  `ccode` char(2) NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `licenses`
--

CREATE TABLE IF NOT EXISTS `licenses` (
  `license_id` varchar(10) NOT NULL default '',
  `license_name` varchar(100) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `user_type`
--

CREATE TABLE IF NOT EXISTS `user_type` (
  `type_id` int(11) NOT NULL auto_increment,
  `type_name` varchar(25) default NULL,
  PRIMARY KEY  (`type_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `role`
--

CREATE TABLE IF NOT EXISTS `role` (
  `role_id` int(10) NOT NULL auto_increment,
  `group_id` int(11) NOT NULL default '0',
  `role_name` varchar(25) default NULL,
  PRIMARY KEY  (`role_id`),
  UNIQUE KEY `role_groupidroleid` (`group_id`,`role_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `project_perm`
--

CREATE TABLE IF NOT EXISTS `project_perm` (
  `id` varchar(10) NOT NULL default '',
  `group_project_id` int(11) NOT NULL default '0',
  `user_id` int(11) NOT NULL default '0',
  `perm_level` int(11) NOT NULL default '0',
  KEY `projectperm_useridgroupprojid` (`user_id`,`group_project_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `forum_perm`
--

CREATE TABLE IF NOT EXISTS `forum_perm` (
  `id` int(11) NOT NULL auto_increment,
  `group_forum_id` int(11) NOT NULL default '0',
  `user_id` int(11) NOT NULL default '0',
  `perm_level` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `role_setting`
--

CREATE TABLE IF NOT EXISTS `role_setting` (
  `role_id` int(11) NOT NULL default '0',
  `section_name` varchar(25) NOT NULL default '',
  `ref_id` int(11) NOT NULL default '0',
  `value` char(2) NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `artifact_extra_field_list`
--

CREATE TABLE IF NOT EXISTS `artifact_extra_field_list` (
  `extra_field_id` int(11) NOT NULL auto_increment,
  `group_artifact_id` int(11) NOT NULL default '0',
  `field_name` varchar(255) NOT NULL,
  `field_type` int(11) default '1',
  `attribute1` int(11) default '0',
  `attribute2` int(11) default '0',
  `is_required` tinyint(1) NOT NULL default '0',
  `alias` text,
  PRIMARY KEY  (`extra_field_id`),
  KEY `artifactextrafieldlist_groupartid` (`group_artifact_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `artifact_extra_field_elements`
--

CREATE TABLE IF NOT EXISTS `artifact_extra_field_elements` (
  `element_id` int(11) NOT NULL auto_increment,
  `extra_field_id` int(11) NOT NULL default '0',
  `element_name` text NOT NULL,
  `status_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`element_id`),
  KEY `artifactextrafldlmts_extrafieldid` (`extra_field_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `artifact_extra_field_data`
--

CREATE TABLE IF NOT EXISTS `artifact_extra_field_data` (
  `data_id` int(11) NOT NULL auto_increment,
  `artifact_id` int(11) NOT NULL default '0',
  `field_data` text,
  `extra_field_id` int(11) default '0',
  PRIMARY KEY  (`data_id`),
  KEY `artifactextrafielddata_artifactid` (`artifact_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `project_counts_agg`
--

CREATE TABLE IF NOT EXISTS `project_counts_agg` (
  `group_project_id` int(11) NOT NULL default '0',
  `count` int(11) NOT NULL default '0',
  `open_count` int(11) default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


CREATE OR REPLACE VIEW `project_group_list_vw` AS
    SELECT project_group_list.group_project_id, group_id, project_name, is_public, description, send_all_posts_to, `count`, open_count
	FROM (project_group_list
	LEFT JOIN project_counts_agg ON project_counts_agg.group_project_id = project_group_list.group_project_id);


-- --------------------------------------------------------

--
-- Table structure for table `project_task_external_order`
--

CREATE TABLE IF NOT EXISTS `project_task_external_order` (
  `project_task_id` int(11) NOT NULL default '0',
  `external_id` int(11) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


CREATE OR REPLACE VIEW `project_depend_vw` AS
    SELECT pt.project_task_id, pd.is_dependent_on_task_id, pd.link_type, pt.end_date, pt.start_date
	FROM (project_task pt NATURAL JOIN project_dependencies pd);


CREATE OR REPLACE VIEW `project_dependon_vw` AS
    SELECT pd.project_task_id, pd.is_dependent_on_task_id, pd.link_type, pt.end_date, pt.start_date
	FROM (project_task AS pt
		LEFT JOIN project_dependencies AS pd ON pd.is_dependent_on_task_id = pt.project_task_id)
	UNION
    SELECT pd.project_task_id, pd.is_dependent_on_task_id, pd.link_type, pt.end_date, pt.start_date
	FROM (project_task AS pt
		RIGHT JOIN project_dependencies AS pd ON pd.is_dependent_on_task_id = pt.project_task_id);


-- --------------------------------------------------------

--
-- Table structure for table `group_join_request`
--

CREATE TABLE IF NOT EXISTS `group_join_request` (
  `group_id` int(11) NOT NULL default '0',
  `user_id` int(11) NOT NULL default '0',
  `comments` varchar(255) default NULL,
  `request_date` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


CREATE OR REPLACE VIEW `project_task_vw` AS
    SELECT
		project_task.project_task_id,
		project_task.group_project_id,
		project_task.summary,
		project_task.details,
		project_task.percent_complete,
		project_task.priority,
		project_task.hours,
		project_task.start_date,
		project_task.end_date,
		project_task.created_by,
		project_task.status_id,
		project_task.category_id,
		project_task.duration,
		project_task.parent_id,
		project_task.last_modified_date,
		project_category.category_name,
		project_status.status_name,
		users.user_name,
		users.realname
	FROM ((project_task
		LEFT JOIN project_category ON project_category.category_id = project_task.category_id)
		LEFT JOIN users ON users.user_id = project_task.created_by)
		LEFT JOIN project_status ON project_status.status_id = project_task.status_id;


-- --------------------------------------------------------

--
-- Table structure for table `artifact_type_monitor`
--

CREATE TABLE IF NOT EXISTS `artifact_type_monitor` (
  `group_artifact_id` int(11) NOT NULL default '0',
  `user_id` int(11) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `plugin_cvstracker_data_artifact`
--

CREATE TABLE IF NOT EXISTS `plugin_cvstracker_data_artifact` (
  `id` int(11) NOT NULL auto_increment,
  `kind` int(11) NOT NULL default '0',
  `group_artifact_id` int(11) default NULL,
  `project_task_id` int(11) default NULL,
  PRIMARY KEY  (`id`),
  KEY `plugin_cvstracker_group_artifact_id` (`group_artifact_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `plugin_cvstracker_data_master`
--

CREATE TABLE IF NOT EXISTS `plugin_cvstracker_data_master` (
  `id` int(11) NOT NULL auto_increment,
  `holder_id` int(11) NOT NULL default '0',
  `log_text` varchar(255) default NULL,
  `file` varchar(25) NOT NULL default '',
  `prev_version` varchar(25) default NULL,
  `actual_version` varchar(25) default NULL,
  `author` varchar(25) NOT NULL default '',
  `cvs_date` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

CREATE OR REPLACE VIEW `nss_passwd` AS
    SELECT users.unix_uid AS uid, users.unix_gid AS gid, users.user_name AS login, users.unix_pw AS passwd, users.realname AS gecos, users.shell, users.user_name AS homedir, users.status
	FROM users
	WHERE users.unix_status = 'A';


CREATE OR REPLACE VIEW `nss_shadow` AS
    SELECT users.user_name AS login, users.unix_pw AS passwd, 'n' AS expired, 'n' AS pwchange
	FROM users
	WHERE users.unix_status = 'A';

--
-- Table structure for table `nss_groups`
--

CREATE TABLE IF NOT EXISTS `nss_groups` (
  `user_id` int(11) default NULL,
  `group_id` int(11) default NULL,
  `name` varchar(30) default NULL,
  `gid` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `nss_usergroups`
--

CREATE TABLE IF NOT EXISTS `nss_usergroups` (
  `uid` int(11) default NULL,
  `gid` int(11) default NULL,
  `user_id` int(11) default NULL,
  `group_id` int(11) default NULL,
  `user_name` varchar(25) default NULL,
  `unix_group_name` varchar(30) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `deleted_mailing_lists`
--

CREATE TABLE IF NOT EXISTS `deleted_mailing_lists` (
  `mailing_list_name` varchar(30) default NULL,
  `delete_date` int(11) default NULL,
  `isdeleted` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `deleted_groups`
--

CREATE TABLE IF NOT EXISTS `deleted_groups` (
  `unix_group_name` varchar(30) default NULL,
  `delete_date` int(11) default NULL,
  `isdeleted` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `artifact_query`
--

CREATE TABLE IF NOT EXISTS `artifact_query` (
  `artifact_query_id` varchar(10) NOT NULL default '',
  `group_artifact_id` int(11) NOT NULL default '0',
  `user_id` int(11) NOT NULL default '0',
  `query_name` text NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `artifact_query_fields`
--

CREATE TABLE IF NOT EXISTS `artifact_query_fields` (
  `artifact_query_id` int(11) NOT NULL default '0',
  `query_field_type` text NOT NULL,
  `query_field_id` int(11) NOT NULL default '0',
  `query_field_values` text NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


CREATE OR REPLACE VIEW `artifact_group_list_vw` AS
    SELECT agl.group_artifact_id, agl.group_id, agl.name, agl.description, agl.is_public, agl.allow_anon, agl.email_all_updates, agl.email_address, agl.due_period, agl.submit_instructions, agl.browse_instructions, agl.datatype, agl.status_timeout, agl.custom_status_field, agl.custom_renderer, aca.count, aca.open_count
	FROM (artifact_group_list AS agl
		LEFT JOIN artifact_counts_agg AS aca USING (group_artifact_id));


CREATE OR REPLACE VIEW `artifact_vw` AS
    SELECT artifact.artifact_id, artifact.group_artifact_id, artifact.status_id, artifact.priority, artifact.submitted_by, artifact.assigned_to, artifact.open_date, artifact.close_date, artifact.summary, artifact.details, u.user_name AS assigned_unixname, u.realname AS assigned_realname, u.email AS assigned_email, u2.user_name AS submitted_unixname, u2.realname AS submitted_realname, u2.email AS submitted_email, artifact_status.status_name, artifact.last_modified_date
	FROM users u, users u2, artifact_status, artifact
	WHERE artifact.assigned_to = u.user_id AND artifact.submitted_by = u2.user_id AND artifact.status_id = artifact_status.id;

CREATE OR REPLACE VIEW `docdata_vw` AS
    SELECT
		users.user_name,
		users.realname,
		users.email,
		d.group_id,
		d.docid,
		d.stateid,
		d.title,
		d.updatedate,
		d.createdate,
		d.created_by,
		d.doc_group,
		d.description,
		d.language_id,
		d.filename,
		d.filetype,
		d.filesize,
		doc_states.name AS state_name,
		doc_groups.groupname AS group_name,
		sl.name AS language_name
	FROM ((((doc_data d
		NATURAL JOIN doc_states)
			NATURAL JOIN doc_groups)
				JOIN supported_languages sl ON sl.language_id = d.language_id)
					JOIN users ON users.user_id = d.created_by);


CREATE TABLE IF NOT EXISTS `form_keys` (
    key_id int(11) NOT NULL auto_increment,
    `key` char(32) NOT NULL,
    creation_date int(11) NOT NULL,
    is_used tinyint(1) default 0 NOT NULL,
	PRIMARY KEY  (`key_id`),
	UNIQUE KEY `key` (`key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;



CREATE TABLE IF NOT EXISTS `forum_attachment` (
    attachmentid INT(11) NOT NULL AUTO_INCREMENT,
    userid INT(11) default 100 NOT NULL,
    dateline INT(11) default 0 NOT NULL,
    filename VARCHAR(100) DEFAULT '' NOT NULL,
    filedata LONGBLOB NOT NULL,
    visible SMALLINT default 0 NOT NULL,
    counter SMALLINT default 0 NOT NULL,
    filesize INT(11) default 0 NOT NULL,
    msg_id INT(11) default 0 NOT NULL,
    filehash VARCHAR(32) DEFAULT '' NOT NULL,
    mimetype VARCHAR(255) DEFAULT '' NOT NULL,
    PRIMARY KEY  (`attachmentid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


CREATE TABLE IF NOT EXISTS `forum_attachment_type` (
    extension character varying(20) DEFAULT '' NOT NULL,
    mimetype character varying(255) DEFAULT '' NOT NULL,
    size int(11) DEFAULT 0 NOT NULL,
    width smallint DEFAULT 0 NOT NULL,
    height smallint DEFAULT 0 NOT NULL,
    enabled smallint DEFAULT 1 NOT NULL,
	PRIMARY KEY  (`extension`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


CREATE OR REPLACE VIEW `forum_group_list_vw` AS
    SELECT
		forum_group_list.group_forum_id,
		forum_group_list.group_id,
		forum_group_list.forum_name,
		forum_group_list.is_public,
		forum_group_list.description,
		forum_group_list.allow_anonymous,
		forum_group_list.send_all_posts_to,
		forum_group_list.moderation_level,
		forum_agg_msg_count.count AS total,
		(SELECT max(forum.post_date) FROM forum
		 WHERE (forum.group_forum_id = forum_group_list.group_forum_id)) AS recent,
		(SELECT count(distinct forum.thread_id) FROM forum
		 WHERE (forum.group_forum_id = forum_group_list.group_forum_id)) AS threads
	FROM (forum_group_list LEFT JOIN forum_agg_msg_count USING (group_forum_id));


CREATE TABLE IF NOT EXISTS `forum_pending_messages` (
    msg_id int(11) NOT NULL auto_increment,
    group_forum_id int(11) DEFAULT 0 NOT NULL,
    posted_by int(11) DEFAULT 0 NOT NULL,
    subject text DEFAULT '' NOT NULL,
    body text DEFAULT '' NOT NULL,
    post_date int(11) DEFAULT 0 NOT NULL,
    is_followup_to int(11) DEFAULT 0 NOT NULL,
    thread_id int(11) DEFAULT 0 NOT NULL,
    has_followups int(11) DEFAULT 0,
    most_recent_date int(11) DEFAULT 0 NOT NULL,
	PRIMARY KEY (`msg_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;



CREATE TABLE IF NOT EXISTS `forum_pending_attachment` (
    attachmentid int(11) NOT NULL auto_increment,
    userid int(11) DEFAULT 100 NOT NULL,
    dateline int(11) DEFAULT 0 NOT NULL,
    filename character varying(100) DEFAULT '' NOT NULL,
    filedata mediumblob NOT NULL,
    visible smallint DEFAULT 0 NOT NULL,
    counter smallint DEFAULT 0 NOT NULL,
    filesize int(11) DEFAULT 0 NOT NULL,
    msg_id int(11) DEFAULT 0 NOT NULL,
    filehash character varying(32) DEFAULT '' NOT NULL,
	PRIMARY KEY (`attachmentid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;



CREATE OR REPLACE VIEW `forum_user_vw` AS
    SELECT
		forum.msg_id,
		forum.group_forum_id,
		forum.posted_by,
		forum.subject,
		forum.body,
		forum.post_date,
		forum.is_followup_to,
		forum.thread_id,
		forum.has_followups,
		forum.most_recent_date,
		users.user_name,
		users.realname
	FROM forum, users
	WHERE (forum.posted_by = users.user_id);



CREATE OR REPLACE VIEW `forum_pending_user_vw` AS
    SELECT
		forum_pending_messages.msg_id,
		forum_pending_messages.group_forum_id,
		forum_pending_messages.posted_by,
		forum_pending_messages.subject,
		forum_pending_messages.body,
		forum_pending_messages.post_date,
		forum_pending_messages.is_followup_to,
		forum_pending_messages.thread_id,
		forum_pending_messages.has_followups,
		forum_pending_messages.most_recent_date,
		users.user_name, users.realname
		FROM forum_pending_messages, users
		WHERE (forum_pending_messages.posted_by = users.user_id);



CREATE TABLE IF NOT EXISTS `group_activity_monitor` (
    group_id int(11) NOT NULL,
    user_id int(11) NOT NULL,
    filter text,
	PRIMARY KEY  (`group_id`,`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;



CREATE OR REPLACE VIEW `activity_vw` AS
	(SELECT
		agl.group_id,
		'trackeropen' AS section,
		agl.group_artifact_id AS ref_id,
		a.artifact_id AS subref_id,
		a.summary AS description,
		a.open_date AS activity_date,
		u.user_id, u.user_name,
		u.realname
		FROM (artifact_group_list agl
			JOIN artifact a USING (group_artifact_id)), users u
		WHERE (u.user_id = a.submitted_by))
	UNION
	(SELECT
		agl.group_id,
		'trackerclose' AS section,
		agl.group_artifact_id AS ref_id,
		a.artifact_id AS subref_id,
		a.summary AS description,
		a.close_date AS activity_date,
		u.user_id,
		u.user_name,
		u.realname
	FROM (artifact_group_list agl
	JOIN artifact a USING (group_artifact_id)), users u
	WHERE ((u.user_id = a.assigned_to) AND (a.close_date > 0)))
	UNION
	(SELECT
		agl.group_id,
		'commit' AS section,
		agl.group_artifact_id AS ref_id,
		a.artifact_id AS subref_id,
		pcdm.log_text AS description,
		pcdm.cvs_date AS activity_date,
		u.user_id, u.user_name, u.realname
	FROM (artifact_group_list agl JOIN artifact a
		USING (group_artifact_id)), plugin_cvstracker_data_master pcdm, plugin_cvstracker_data_artifact pcda, users u
	WHERE (((pcdm.holder_id = pcda.id) AND (pcda.group_artifact_id = a.artifact_id)) AND (u.user_name = pcdm.author)))
	UNION
	(SELECT
		frsp.group_id,
		'frsrelease' AS section,
		frsp.package_id AS ref_id,
		frsr.release_id AS subref_id,
		frsr.name AS description,
		frsr.release_date AS activity_date,
		u.user_id, u.user_name,
		u.realname
	FROM (frs_package frsp JOIN frs_release frsr USING (package_id)), users u
	WHERE (u.user_id = frsr.released_by))
	UNION
	(SELECT
		fgl.group_id,
		'forumpost' AS section,
		fgl.group_forum_id AS ref_id,
		forum.msg_id AS subref_id,
		forum.subject AS description,
		forum.post_date AS activity_date,
		u.user_id,
		u.user_name,
		u.realname
	FROM (forum_group_list fgl
	JOIN forum USING (group_forum_id)), users u
	WHERE (u.user_id = forum.posted_by))
	UNION
	(SELECT
		news_bytes.group_id,
		'news' AS section,
		news_bytes.id AS ref_id,
		news_bytes.forum_id AS subref_id,
		news_bytes.summary AS description,
		news_bytes.post_date AS activity_date,
		u.user_id,
		u.user_name,
		u.realname
	FROM news_bytes, users u
	WHERE (u.user_id = news_bytes.submitted_by));



-- --------------------------------------------------------

--
-- Table structure for table `forum_thread_seq`
--

CREATE TABLE IF NOT EXISTS `forum_thread_seq` (
  `value` int(11) NOT NULL default '1'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP PROCEDURE IF EXISTS newval;
DELIMITER //
CREATE PROCEDURE newval (IN tablename VARCHAR(64), OUT result INT)
  BEGIN
    SET @s=CONCAT('SELECT value INTO @newvalue FROM ',tablename);
    SET @u=CONCAT('UPDATE ',tablename,' SET value=value+1 WHERE value=@newvalue;');
    PREPARE select_stmt FROM @s;
    PREPARE update_stmt FROM @u;

    update_loop: LOOP
      EXECUTE select_stmt;
      IF @newvalue = NULL THEN
          LEAVE update_loop;
      END IF;
      EXECUTE update_stmt;
      IF row_count() = 1 THEN
        LEAVE update_loop;
      END IF;
    END LOOP update_loop;

    DEALLOCATE PREPARE select_stmt;
    DEALLOCATE PREPARE update_stmt;

    SET result=@newvalue;
  END;
//
DELIMITER ;

