3 -- http://www.phpmyadmin.net
6 -- Generation Time: Feb 23, 2007 at 08:35 PM
7 -- Server version: 5.0.27
12 -- CREATE DATABASE `gforge` DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci;
15 -- --------------------------------------------------------
18 -- Table structure for table `canned_responses`
21 CREATE TABLE IF NOT EXISTS `canned_responses` (
22 `response_id` int(11) NOT NULL auto_increment,
23 `response_title` varchar(25) NOT NULL default '',
24 `response_text` varchar(255) NOT NULL default '',
25 PRIMARY KEY (`response_id`)
26 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
28 -- --------------------------------------------------------
31 -- Table structure for table `db_images`
34 CREATE TABLE IF NOT EXISTS `db_images` (
35 `id` int(11) NOT NULL auto_increment,
36 `group_id` int(11) NOT NULL default '0',
37 `description` varchar(255) NOT NULL default '',
38 `bin_data` text NOT NULL,
39 `filename` varchar(25) NOT NULL default '',
40 `filesize` int(11) NOT NULL default '0',
41 `filetype` varchar(10) NOT NULL default '',
42 `width` int(11) NOT NULL default '0',
43 `height` int(11) NOT NULL default '0',
44 `upload_date` int(11) default NULL,
45 `version` int(11) default NULL,
47 KEY `db_images_group` (`group_id`)
48 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
50 -- --------------------------------------------------------
53 -- Table structure for table `doc_data`
56 CREATE TABLE IF NOT EXISTS `doc_data` (
57 `docid` int(11) NOT NULL auto_increment,
58 `stateid` int(11) NOT NULL default '0',
59 `title` varchar(255) NOT NULL default '',
60 `data` varchar(255) NOT NULL default '',
61 `updatedate` int(11) NOT NULL default '0',
62 `createdate` int(11) NOT NULL default '0',
63 `created_by` int(11) NOT NULL default '0',
64 `doc_group` int(11) NOT NULL default '0',
65 `description` varchar(255) default NULL,
66 `language_id` int(11) NOT NULL default '1',
67 `filename` varchar(25) default NULL,
68 `filetype` varchar(10) default NULL,
69 `group_id` int(11) default NULL,
70 `filesize` int(11) NOT NULL default '0',
71 PRIMARY KEY (`docid`),
72 KEY `docdata_groupid` (`group_id`,`doc_group`)
73 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
75 -- --------------------------------------------------------
78 -- Table structure for table `doc_groups`
81 CREATE TABLE IF NOT EXISTS `doc_groups` (
82 `doc_group` int(11) NOT NULL auto_increment,
83 `groupname` varchar(25) NOT NULL default '',
84 `group_id` int(11) NOT NULL default '0',
85 `parent_doc_group` int(11) NOT NULL default '0',
86 PRIMARY KEY (`doc_group`),
87 KEY `doc_groups_group` (`group_id`),
88 KEY `docgroups_parentdocgroup` (`parent_doc_group`)
89 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
91 -- --------------------------------------------------------
94 -- Table structure for table `doc_states`
97 CREATE TABLE IF NOT EXISTS `doc_states` (
98 `stateid` int(11) NOT NULL auto_increment,
99 `name` varchar(25) NOT NULL default '',
100 PRIMARY KEY (`stateid`)
101 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
103 -- --------------------------------------------------------
106 -- Table structure for table `filemodule_monitor`
109 CREATE TABLE IF NOT EXISTS `filemodule_monitor` (
110 `id` int(11) NOT NULL auto_increment,
111 `filemodule_id` int(11) NOT NULL default '0',
112 `user_id` int(11) NOT NULL default '0',
114 KEY `filemodulemonitor_useridfilemoduleid` (`user_id`,`filemodule_id`)
115 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
117 -- --------------------------------------------------------
120 -- Table structure for table `forum`
123 CREATE TABLE IF NOT EXISTS `forum` (
124 `msg_id` int(11) NOT NULL auto_increment,
125 `group_forum_id` int(11) NOT NULL default '0',
126 `posted_by` int(11) NOT NULL default '0',
127 `subject` varchar(100) NOT NULL default '',
128 `body` text NOT NULL,
129 `post_date` int(11) NOT NULL default '0',
130 `is_followup_to` int(11) NOT NULL default '0',
131 `thread_id` int(11) NOT NULL default '0',
132 `has_followups` int(11) default '0',
133 `most_recent_date` int(11) NOT NULL default '0',
134 PRIMARY KEY (`msg_id`),
135 KEY `group_forum_id` (`group_forum_id`,`msg_id`),
136 KEY `forum_group_forum_id` (`group_forum_id`),
137 KEY `forum_forumid_threadid_mostrecent` (`group_forum_id`,`thread_id`,`most_recent_date`),
138 KEY `forum_threadid_isfollowupto` (`thread_id`,`is_followup_to`),
139 KEY `forum_forumid_isfollto_mostrecent` (`group_forum_id`,`is_followup_to`,`most_recent_date`)
140 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
142 -- --------------------------------------------------------
145 -- Table structure for table `forum_agg_msg_count`
148 CREATE TABLE IF NOT EXISTS `forum_agg_msg_count` (
149 `group_forum_id` int(11) NOT NULL default '0',
150 `count` int(11) NOT NULL default '0'
151 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
153 -- --------------------------------------------------------
156 -- Table structure for table `forum_group_list`
159 CREATE TABLE IF NOT EXISTS `forum_group_list` (
160 `group_forum_id` int(11) NOT NULL auto_increment,
161 `group_id` int(11) NOT NULL default '0',
162 `forum_name` varchar(25) NOT NULL default '',
163 `is_public` int(11) NOT NULL default '0',
164 `description` varchar(255) default NULL,
165 `allow_anonymous` int(11) NOT NULL default '0',
166 `send_all_posts_to` varchar(25) default NULL,
167 `moderation_level` int(11) default '0',
168 PRIMARY KEY (`group_forum_id`),
169 KEY `forum_group_list_group_id` (`group_id`)
170 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
172 -- --------------------------------------------------------
175 -- Table structure for table `forum_monitored_forums`
178 CREATE TABLE IF NOT EXISTS `forum_monitored_forums` (
179 `monitor_id` int(11) NOT NULL auto_increment,
180 `forum_id` int(11) NOT NULL default '0',
181 `user_id` int(11) NOT NULL default '0',
182 PRIMARY KEY (`monitor_id`),
183 KEY `forummonitoredforums_useridforumid` (`user_id`,`forum_id`)
184 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
186 -- --------------------------------------------------------
189 -- Table structure for table `forum_saved_place`
192 CREATE TABLE IF NOT EXISTS `forum_saved_place` (
193 `saved_place_id` int(11) NOT NULL auto_increment,
194 `user_id` int(11) NOT NULL default '0',
195 `forum_id` int(11) NOT NULL default '0',
196 `save_date` int(11) NOT NULL default '0',
197 PRIMARY KEY (`saved_place_id`)
198 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
200 -- --------------------------------------------------------
203 -- Table structure for table `frs_file`
206 CREATE TABLE IF NOT EXISTS `frs_file` (
207 `file_id` int(11) NOT NULL auto_increment,
208 `filename` varchar(25) default NULL,
209 `release_id` int(11) NOT NULL default '0',
210 `type_id` int(11) NOT NULL default '0',
211 `processor_id` int(11) NOT NULL default '0',
212 `release_time` int(11) NOT NULL default '0',
213 `file_size` int(11) NOT NULL default '0',
214 `post_date` int(11) NOT NULL default '0',
215 PRIMARY KEY (`file_id`),
216 KEY `frs_file_date` (`post_date`),
217 KEY `frs_file_release_id` (`release_id`)
218 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
220 -- --------------------------------------------------------
223 -- Table structure for table `frs_filetype`
226 CREATE TABLE IF NOT EXISTS `frs_filetype` (
227 `type_id` int(11) NOT NULL auto_increment,
228 `name` varchar(25) default NULL,
229 PRIMARY KEY (`type_id`)
230 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
232 -- --------------------------------------------------------
235 -- Table structure for table `frs_package`
238 CREATE TABLE IF NOT EXISTS `frs_package` (
239 `package_id` int(11) NOT NULL auto_increment,
240 `group_id` int(11) NOT NULL default '0',
241 `name` varchar(25) default NULL,
242 `status_id` int(11) NOT NULL default '0',
243 `is_public` int(11) default '1',
244 PRIMARY KEY (`package_id`),
245 KEY `package_group_id` (`group_id`)
246 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
248 -- --------------------------------------------------------
251 -- Table structure for table `frs_processor`
254 CREATE TABLE IF NOT EXISTS `frs_processor` (
255 `processor_id` int(11) NOT NULL auto_increment,
256 `name` varchar(25) default NULL,
257 PRIMARY KEY (`processor_id`)
258 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
260 -- --------------------------------------------------------
263 -- Table structure for table `frs_release`
266 CREATE TABLE IF NOT EXISTS `frs_release` (
267 `release_id` int(11) NOT NULL auto_increment,
268 `package_id` int(11) NOT NULL default '0',
269 `name` varchar(25) default NULL,
270 `notes` varchar(255) default NULL,
271 `changes` varchar(255) default NULL,
272 `status_id` int(11) NOT NULL default '0',
273 `preformatted` int(11) NOT NULL default '0',
274 `release_date` int(11) NOT NULL default '0',
275 `released_by` int(11) NOT NULL default '0',
276 PRIMARY KEY (`release_id`),
277 KEY `frs_release_package` (`package_id`)
278 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
280 -- --------------------------------------------------------
283 -- Table structure for table `frs_status`
286 CREATE TABLE IF NOT EXISTS `frs_status` (
287 `status_id` int(11) NOT NULL auto_increment,
288 `name` varchar(25) default NULL,
289 PRIMARY KEY (`status_id`)
290 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
292 -- --------------------------------------------------------
295 -- Table structure for table `group_history`
298 CREATE TABLE IF NOT EXISTS `group_history` (
299 `group_history_id` int(11) NOT NULL auto_increment,
300 `group_id` int(11) NOT NULL default '0',
301 `field_name` varchar(25) NOT NULL default '',
302 `old_value` varchar(100) NOT NULL default '',
303 `mod_by` int(11) NOT NULL default '0',
304 `adddate` int(11) default NULL,
305 PRIMARY KEY (`group_history_id`),
306 KEY `group_history_group_id` (`group_id`)
307 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
309 -- --------------------------------------------------------
312 -- Table structure for table `groups`
315 CREATE TABLE IF NOT EXISTS `groups` (
316 `group_id` int(11) NOT NULL auto_increment,
317 `group_name` varchar(40) default NULL,
318 `homepage` varchar(128) default NULL,
319 `is_public` int(11) NOT NULL default '0',
320 `status` char(1) NOT NULL default '',
321 `unix_group_name` varchar(30) NOT NULL default '',
322 `unix_box` varchar(20) NOT NULL default 'shell1',
323 `http_domain` varchar(80) default NULL,
324 `short_description` varchar(255) default NULL,
325 `register_purpose` varchar(100) default NULL,
326 `license_other` varchar(25) default NULL,
327 `register_time` int(11) NOT NULL default '0',
328 `rand_hash` varchar(32) default NULL,
329 `use_mail` int(11) NOT NULL default '1',
330 `use_survey` int(11) NOT NULL default '1',
331 `use_forum` int(11) NOT NULL default '1',
332 `use_pm` int(11) NOT NULL default '1',
333 `use_scm` int(11) NOT NULL default '1',
334 `use_news` int(11) NOT NULL default '1',
335 `type_id` int(11) NOT NULL default '1',
336 `use_docman` int(11) NOT NULL default '1',
337 `new_doc_address` varchar(100) NOT NULL default '',
338 `send_all_docs` int(11) NOT NULL default '0',
339 `use_pm_depend_box` int(11) NOT NULL default '1',
340 `use_ftp` int(11) default '1',
341 `use_tracker` int(11) default '1',
342 `use_frs` int(11) default '1',
343 `use_stats` int(11) default '1',
344 `enable_pserver` int(11) default '1',
345 `enable_anonscm` int(11) default '1',
346 `license` int(11) default '100',
347 `scm_box` varchar(25) default NULL,
348 PRIMARY KEY (`group_id`),
349 UNIQUE KEY `group_unix_uniq` (`unix_group_name`),
350 KEY `groups_type` (`type_id`),
351 KEY `groups_public` (`is_public`),
352 KEY `groups_status` (`status`)
353 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
355 -- --------------------------------------------------------
358 -- Table structure for table `mail_group_list`
361 CREATE TABLE IF NOT EXISTS `mail_group_list` (
362 `group_list_id` int(11) NOT NULL auto_increment,
363 `group_id` int(11) NOT NULL default '0',
364 `list_name` varchar(25) default NULL,
365 `is_public` int(11) NOT NULL default '0',
366 `password` varchar(16) default NULL,
367 `list_admin` int(11) NOT NULL default '0',
368 `status` int(11) NOT NULL default '0',
369 `description` varchar(255) default NULL,
370 PRIMARY KEY (`group_list_id`),
371 KEY `mail_group_list_group` (`group_id`)
372 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
374 -- --------------------------------------------------------
377 -- Table structure for table `news_bytes`
380 CREATE TABLE IF NOT EXISTS `news_bytes` (
381 `id` int(11) NOT NULL auto_increment,
382 `group_id` int(11) NOT NULL default '0',
383 `submitted_by` int(11) NOT NULL default '0',
384 `is_approved` int(11) NOT NULL default '0',
385 `post_date` int(11) NOT NULL default '0',
386 `forum_id` int(11) NOT NULL default '0',
390 KEY `news_bytes_group` (`group_id`),
391 KEY `news_bytes_approved` (`is_approved`),
392 KEY `news_bytes_forum` (`forum_id`),
393 KEY `news_group_date` (`group_id`,`post_date`),
394 KEY `news_approved_date` (`is_approved`,`post_date`)
395 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
397 -- --------------------------------------------------------
400 -- Table structure for table `people_job`
403 CREATE TABLE IF NOT EXISTS `people_job` (
404 `job_id` int(11) NOT NULL auto_increment,
405 `group_id` int(11) NOT NULL default '0',
406 `created_by` int(11) NOT NULL default '0',
407 `title` varchar(25) default NULL,
408 `description` varchar(255) default NULL,
409 `post_date` int(11) NOT NULL default '0',
410 `status_id` int(11) NOT NULL default '0',
411 `category_id` int(11) NOT NULL default '0',
412 PRIMARY KEY (`job_id`),
413 KEY `people_job_group_id` (`group_id`)
414 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
416 -- --------------------------------------------------------
419 -- Table structure for table `people_job_category`
422 CREATE TABLE IF NOT EXISTS `people_job_category` (
423 `category_id` int(11) NOT NULL auto_increment,
424 `name` varchar(25) default NULL,
425 `private_flag` int(11) NOT NULL default '0',
426 PRIMARY KEY (`category_id`)
427 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
429 -- --------------------------------------------------------
432 -- Table structure for table `people_job_inventory`
435 CREATE TABLE IF NOT EXISTS `people_job_inventory` (
436 `job_inventory_id` int(11) NOT NULL auto_increment,
437 `job_id` int(11) NOT NULL default '0',
438 `skill_id` int(11) NOT NULL default '0',
439 `skill_level_id` int(11) NOT NULL default '0',
440 `skill_year_id` int(11) NOT NULL default '0',
441 PRIMARY KEY (`job_inventory_id`)
442 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
444 -- --------------------------------------------------------
447 -- Table structure for table `people_job_status`
450 CREATE TABLE IF NOT EXISTS `people_job_status` (
451 `status_id` int(11) NOT NULL auto_increment,
452 `name` varchar(25) default NULL,
453 PRIMARY KEY (`status_id`)
454 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
456 -- --------------------------------------------------------
459 -- Table structure for table `people_skill`
462 CREATE TABLE IF NOT EXISTS `people_skill` (
463 `skill_id` int(11) NOT NULL auto_increment,
464 `name` varchar(25) default NULL,
465 PRIMARY KEY (`skill_id`)
466 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
468 -- --------------------------------------------------------
471 -- Table structure for table `people_skill_inventory`
474 CREATE TABLE IF NOT EXISTS `people_skill_inventory` (
475 `skill_inventory_id` int(11) NOT NULL auto_increment,
476 `user_id` int(11) NOT NULL default '0',
477 `skill_id` int(11) NOT NULL default '0',
478 `skill_level_id` int(11) NOT NULL default '0',
479 `skill_year_id` int(11) NOT NULL default '0',
480 PRIMARY KEY (`skill_inventory_id`)
481 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
483 -- --------------------------------------------------------
486 -- Table structure for table `people_skill_level`
489 CREATE TABLE IF NOT EXISTS `people_skill_level` (
490 `skill_level_id` int(11) NOT NULL auto_increment,
491 `name` varchar(25) default NULL,
492 PRIMARY KEY (`skill_level_id`)
493 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
495 -- --------------------------------------------------------
498 -- Table structure for table `people_skill_year`
501 CREATE TABLE IF NOT EXISTS `people_skill_year` (
502 `skill_year_id` int(11) NOT NULL auto_increment,
503 `name` varchar(25) default NULL,
504 PRIMARY KEY (`skill_year_id`)
505 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
507 -- --------------------------------------------------------
510 -- Table structure for table `project_assigned_to`
513 CREATE TABLE IF NOT EXISTS `project_assigned_to` (
514 `project_assigned_id` int(11) NOT NULL auto_increment,
515 `project_task_id` int(11) NOT NULL default '0',
516 `assigned_to_id` int(11) NOT NULL default '0',
517 PRIMARY KEY (`project_assigned_id`),
518 KEY `projectassigned_assignedtotaskid` (`assigned_to_id`,`project_task_id`)
519 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
521 -- --------------------------------------------------------
524 -- Table structure for table `project_dependencies`
527 CREATE TABLE IF NOT EXISTS `project_dependencies` (
528 `project_depend_id` int(11) NOT NULL auto_increment,
529 `project_task_id` int(11) NOT NULL default '0',
530 `is_dependent_on_task_id` int(11) NOT NULL default '0',
531 `link_type` char(2) default NULL,
532 PRIMARY KEY (`project_depend_id`),
533 KEY `projectdep_isdepon_projtaskid` (`is_dependent_on_task_id`,`project_task_id`)
534 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
536 -- --------------------------------------------------------
539 -- Table structure for table `project_group_list`
542 CREATE TABLE IF NOT EXISTS `project_group_list` (
543 `group_project_id` int(11) NOT NULL auto_increment,
544 `group_id` int(11) NOT NULL default '0',
545 `project_name` varchar(25) NOT NULL default '',
546 `is_public` int(11) NOT NULL default '0',
547 `description` varchar(255) default NULL,
548 `send_all_posts_to` varchar(25) default NULL,
549 PRIMARY KEY (`group_project_id`),
550 KEY `project_group_list_group_id` (`group_id`)
551 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
553 -- --------------------------------------------------------
556 -- Table structure for table `project_history`
559 CREATE TABLE IF NOT EXISTS `project_history` (
560 `project_history_id` int(11) NOT NULL auto_increment,
561 `project_task_id` int(11) NOT NULL default '0',
562 `field_name` varchar(25) NOT NULL default '',
563 `old_value` varchar(25) NOT NULL default '',
564 `mod_by` int(11) NOT NULL default '0',
565 `mod_date` int(11) NOT NULL default '0',
566 PRIMARY KEY (`project_history_id`),
567 KEY `project_history_task_id` (`project_task_id`)
568 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
570 -- --------------------------------------------------------
573 -- Table structure for table `project_metric`
576 CREATE TABLE IF NOT EXISTS `project_metric` (
577 `ranking` int(11) NOT NULL auto_increment,
578 `percentile` double default NULL,
579 `group_id` int(11) NOT NULL default '0',
580 PRIMARY KEY (`ranking`),
581 KEY `project_metric_group` (`group_id`)
582 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
584 -- --------------------------------------------------------
587 -- Table structure for table `project_metric_tmp1`
590 CREATE TABLE IF NOT EXISTS `project_metric_tmp1` (
591 `ranking` int(11) NOT NULL auto_increment,
592 `group_id` int(11) NOT NULL default '0',
593 `value` double default NULL,
594 PRIMARY KEY (`ranking`)
595 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
597 -- --------------------------------------------------------
600 -- Table structure for table `project_status`
603 CREATE TABLE IF NOT EXISTS `project_status` (
604 `status_id` int(11) NOT NULL auto_increment,
605 `status_name` varchar(25) NOT NULL default '',
606 PRIMARY KEY (`status_id`)
607 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
609 -- --------------------------------------------------------
612 -- Table structure for table `project_task`
615 CREATE TABLE IF NOT EXISTS `project_task` (
616 `project_task_id` int(11) NOT NULL auto_increment,
617 `group_project_id` int(11) NOT NULL default '0',
618 `summary` text NOT NULL,
619 `details` text NOT NULL,
620 `percent_complete` int(11) NOT NULL default '0',
621 `priority` int(11) NOT NULL default '3',
622 `hours` double NOT NULL default '0',
623 `start_date` int(11) NOT NULL default '0',
624 `end_date` int(11) NOT NULL default '0',
625 `created_by` int(11) NOT NULL default '0',
626 `status_id` int(11) NOT NULL default '0',
627 `category_id` int(11) default NULL,
628 `duration` int(11) default '0',
629 `parent_id` int(11) default '0',
630 `last_modified_date` int(11) default NULL,
631 PRIMARY KEY (`project_task_id`),
632 KEY `projecttask_projid_status` (`group_project_id`,`status_id`)
633 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
635 -- --------------------------------------------------------
638 -- Table structure for table `project_weekly_metric`
641 CREATE TABLE IF NOT EXISTS `project_weekly_metric` (
642 `ranking` int(11) NOT NULL auto_increment,
643 `percentile` double default NULL,
644 `group_id` int(11) NOT NULL default '0',
645 PRIMARY KEY (`ranking`),
646 KEY `projectweeklymetric_ranking` (`ranking`),
647 KEY `project_metric_weekly_group` (`group_id`)
648 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
650 -- --------------------------------------------------------
653 -- Table structure for table `user_session`
656 CREATE TABLE IF NOT EXISTS `user_session` (
657 `user_id` int(11) NOT NULL default '0',
658 `session_hash` char(32) NOT NULL default '',
659 `ip_addr` char(15) NOT NULL default '',
660 `time` int(11) NOT NULL default '0',
661 PRIMARY KEY (`user_id`),
662 KEY `session_user_id` (`user_id`),
663 KEY `session_time` (`time`)
664 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
666 -- --------------------------------------------------------
669 -- Table structure for table `snippet`
672 CREATE TABLE IF NOT EXISTS `snippet` (
673 `snippet_id` int(11) NOT NULL auto_increment,
674 `created_by` int(11) NOT NULL default '0',
675 `name` varchar(25) default NULL,
676 `description` varchar(255) default NULL,
677 `type` int(11) NOT NULL default '0',
678 `language` int(11) NOT NULL default '0',
679 `license` varchar(25) NOT NULL default '',
680 `category` int(11) NOT NULL default '0',
681 PRIMARY KEY (`snippet_id`),
682 KEY `snippet_language` (`language`),
683 KEY `snippet_category` (`category`)
684 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
686 -- --------------------------------------------------------
689 -- Table structure for table `snippet_package`
692 CREATE TABLE IF NOT EXISTS `snippet_package` (
693 `snippet_package_id` int(11) NOT NULL auto_increment,
694 `created_by` int(11) NOT NULL default '0',
695 `name` varchar(25) default NULL,
696 `description` varchar(255) default NULL,
697 `category` int(11) NOT NULL default '0',
698 `language` int(11) NOT NULL default '0',
699 PRIMARY KEY (`snippet_package_id`),
700 KEY `snippet_package_language` (`language`),
701 KEY `snippet_package_category` (`category`)
702 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
704 -- --------------------------------------------------------
707 -- Table structure for table `snippet_package_item`
710 CREATE TABLE IF NOT EXISTS `snippet_package_item` (
711 `snippet_package_item_id` int(11) NOT NULL auto_increment,
712 `snippet_package_version_id` int(11) NOT NULL default '0',
713 `snippet_version_id` int(11) NOT NULL default '0',
714 PRIMARY KEY (`snippet_package_item_id`),
715 KEY `snippet_package_item_pkg_ver` (`snippet_package_version_id`)
716 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
718 -- --------------------------------------------------------
721 -- Table structure for table `snippet_package_version`
724 CREATE TABLE IF NOT EXISTS `snippet_package_version` (
725 `snippet_package_version_id` int(11) NOT NULL auto_increment,
726 `snippet_package_id` int(11) NOT NULL default '0',
727 `changes` varchar(255) default NULL,
728 `version` varchar(25) default NULL,
729 `submitted_by` int(11) NOT NULL default '0',
730 `post_date` int(11) NOT NULL default '0',
731 PRIMARY KEY (`snippet_package_version_id`),
732 KEY `snippet_package_version_pkg_id` (`snippet_package_id`)
733 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
735 -- --------------------------------------------------------
738 -- Table structure for table `snippet_version`
741 CREATE TABLE IF NOT EXISTS `snippet_version` (
742 `snippet_version_id` int(11) NOT NULL auto_increment,
743 `snippet_id` int(11) NOT NULL default '0',
744 `changes` varchar(255) default NULL,
745 `version` varchar(25) default NULL,
746 `submitted_by` int(11) NOT NULL default '0',
747 `post_date` int(11) NOT NULL default '0',
749 PRIMARY KEY (`snippet_version_id`),
750 KEY `snippet_version_snippet_id` (`snippet_id`)
751 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
753 -- --------------------------------------------------------
756 -- Table structure for table `stats_agg_logo_by_day`
759 CREATE TABLE IF NOT EXISTS `stats_agg_logo_by_day` (
760 `day` int(11) default NULL,
761 `count` int(11) default NULL
762 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
764 -- --------------------------------------------------------
767 -- Table structure for table `stats_agg_pages_by_day`
770 CREATE TABLE IF NOT EXISTS `stats_agg_pages_by_day` (
771 `day` int(11) NOT NULL default '0',
772 `count` int(11) NOT NULL default '0',
773 KEY `pages_by_day_day` (`day`)
774 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
776 -- --------------------------------------------------------
779 -- Table structure for table `stats_site_pages_by_month`
782 CREATE TABLE IF NOT EXISTS `stats_site_pages_by_month` (
783 `month` int(11) default NULL,
784 `site_page_views` int(11) default NULL
785 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
787 -- --------------------------------------------------------
790 -- Table structure for table `survey_question_types`
793 CREATE TABLE IF NOT EXISTS `survey_question_types` (
794 `id` int(11) NOT NULL auto_increment,
795 `type` varchar(25) NOT NULL default '',
797 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
799 -- --------------------------------------------------------
802 -- Table structure for table `survey_questions`
805 CREATE TABLE IF NOT EXISTS `survey_questions` (
806 `question_id` int(11) NOT NULL auto_increment,
807 `group_id` int(11) NOT NULL default '0',
808 `question` varchar(100) NOT NULL default '',
809 `question_type` int(11) NOT NULL default '0',
810 PRIMARY KEY (`question_id`),
811 KEY `survey_questions_group` (`group_id`)
812 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
814 -- --------------------------------------------------------
817 -- Table structure for table `survey_rating_aggregate`
820 CREATE TABLE IF NOT EXISTS `survey_rating_aggregate` (
821 `type` int(11) NOT NULL default '0',
822 `id` int(11) NOT NULL default '0',
823 `response` double NOT NULL default '0',
824 `count` int(11) NOT NULL default '0',
825 KEY `survey_rating_aggregate_type_id` (`type`,`id`)
826 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
828 -- --------------------------------------------------------
831 -- Table structure for table `survey_rating_response`
834 CREATE TABLE IF NOT EXISTS `survey_rating_response` (
835 `user_id` int(11) NOT NULL default '0',
836 `type` int(11) NOT NULL default '0',
837 `id` int(11) NOT NULL default '0',
838 `response` int(11) NOT NULL default '0',
839 `post_date` int(11) NOT NULL default '0',
840 KEY `survey_rating_responses_user_ty` (`user_id`,`type`,`id`),
841 KEY `survey_rating_responses_type_id` (`type`,`id`)
842 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
844 -- --------------------------------------------------------
847 -- Table structure for table `survey_responses`
850 CREATE TABLE IF NOT EXISTS `survey_responses` (
851 `user_id` int(11) NOT NULL default '0',
852 `group_id` int(11) NOT NULL default '0',
853 `survey_id` int(11) NOT NULL default '0',
854 `question_id` int(11) NOT NULL default '0',
855 `response` varchar(100) NOT NULL default '',
856 `post_date` int(11) NOT NULL default '0',
857 KEY `survey_responses_group_id` (`group_id`),
858 KEY `survey_responses_user_survey_qu` (`user_id`,`survey_id`,`question_id`),
859 KEY `survey_responses_survey_questio` (`survey_id`,`question_id`)
860 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
862 -- --------------------------------------------------------
865 -- Table structure for table `surveys`
868 CREATE TABLE IF NOT EXISTS `surveys` (
869 `survey_id` int(11) NOT NULL auto_increment,
870 `group_id` int(11) NOT NULL default '0',
871 `survey_title` varchar(100) NOT NULL default '',
872 `survey_questions` varchar(100) NOT NULL default '',
873 `is_active` int(11) NOT NULL default '1',
874 PRIMARY KEY (`survey_id`),
875 KEY `surveys_group` (`group_id`)
876 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
878 -- --------------------------------------------------------
881 -- Table structure for table `trove_cat`
884 CREATE TABLE IF NOT EXISTS `trove_cat` (
885 `trove_cat_id` int(11) NOT NULL auto_increment,
886 `version` int(11) NOT NULL default '0',
887 `parent` int(11) NOT NULL default '0',
888 `root_parent` int(11) NOT NULL default '0',
889 `shortname` varchar(80) default NULL,
890 `fullname` varchar(80) default NULL,
891 `description` varchar(255) default NULL,
892 `count_subcat` int(11) NOT NULL default '0',
893 `count_subproj` int(11) NOT NULL default '0',
894 `fullpath` text NOT NULL,
896 PRIMARY KEY (`trove_cat_id`),
897 KEY `trovecat_parentid` (`parent`)
898 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
900 -- --------------------------------------------------------
903 -- Table structure for table `trove_group_link`
906 CREATE TABLE IF NOT EXISTS `trove_group_link` (
907 `trove_group_id` int(11) NOT NULL auto_increment,
908 `trove_cat_id` int(11) NOT NULL default '0',
909 `trove_cat_version` int(11) NOT NULL default '0',
910 `group_id` int(11) NOT NULL default '0',
911 `trove_cat_root` int(11) NOT NULL default '0',
912 PRIMARY KEY (`trove_group_id`),
913 KEY `trovegrouplink_groupidcatid` (`group_id`,`trove_cat_id`)
914 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
916 -- --------------------------------------------------------
919 -- Table structure for table `user_bookmarks`
922 CREATE TABLE IF NOT EXISTS `user_bookmarks` (
923 `bookmark_id` int(11) NOT NULL auto_increment,
924 `user_id` int(11) NOT NULL default '0',
925 `bookmark_url` varchar(100) default NULL,
926 `bookmark_title` varchar(25) default NULL,
927 PRIMARY KEY (`bookmark_id`),
928 KEY `user_bookmark_user_id` (`user_id`)
929 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
931 -- --------------------------------------------------------
934 -- Table structure for table `user_diary`
937 CREATE TABLE IF NOT EXISTS `user_diary` (
938 `id` int(11) NOT NULL auto_increment,
939 `user_id` int(11) NOT NULL default '0',
940 `date_posted` int(11) NOT NULL default '0',
943 `is_public` int(11) NOT NULL default '0',
945 KEY `user_diary_user_date` (`user_id`,`date_posted`)
946 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
948 -- --------------------------------------------------------
951 -- Table structure for table `user_diary_monitor`
954 CREATE TABLE IF NOT EXISTS `user_diary_monitor` (
955 `monitor_id` int(11) NOT NULL auto_increment,
956 `monitored_user` int(11) NOT NULL default '0',
957 `user_id` int(11) NOT NULL default '0',
958 PRIMARY KEY (`monitor_id`),
959 KEY `userdiarymon_useridmonitoredid` (`user_id`,`monitored_user`)
960 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
962 -- --------------------------------------------------------
965 -- Table structure for table `user_group`
968 CREATE TABLE IF NOT EXISTS `user_group` (
969 `user_group_id` int(11) NOT NULL auto_increment,
970 `user_id` int(11) NOT NULL default '0',
971 `group_id` int(11) NOT NULL default '0',
972 `admin_flags` char(16) NOT NULL default '',
973 `forum_flags` int(11) NOT NULL default '0',
974 `project_flags` int(11) NOT NULL default '2',
975 `doc_flags` int(11) NOT NULL default '0',
976 `cvs_flags` int(11) NOT NULL default '1',
977 `member_role` int(11) NOT NULL default '100',
978 `release_flags` int(11) NOT NULL default '0',
979 `artifact_flags` int(11) default NULL,
980 `role_id` int(11) default '1',
981 PRIMARY KEY (`user_group_id`),
982 KEY `usergroup_useridgroupid` (`user_id`,`group_id`)
983 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
985 -- --------------------------------------------------------
988 -- Table structure for table `user_metric`
991 CREATE TABLE IF NOT EXISTS `user_metric` (
992 `ranking` int(11) NOT NULL auto_increment,
993 `user_id` int(11) NOT NULL default '0',
994 `times_ranked` int(11) NOT NULL default '0',
995 `avg_raters_importance` double NOT NULL default '0',
996 `avg_rating` double NOT NULL default '0',
997 `metric` double NOT NULL default '0',
998 `percentile` double NOT NULL default '0',
999 `importance_factor` double NOT NULL default '0',
1000 PRIMARY KEY (`ranking`),
1001 UNIQUE KEY `usermetric_userid` (`user_id`)
1002 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1004 -- --------------------------------------------------------
1007 -- Table structure for table `user_metric0`
1010 CREATE TABLE IF NOT EXISTS `user_metric0` (
1011 `ranking` int(11) NOT NULL auto_increment,
1012 `user_id` int(11) NOT NULL default '0',
1013 `times_ranked` int(11) NOT NULL default '0',
1014 `avg_raters_importance` double NOT NULL default '0',
1015 `avg_rating` double NOT NULL default '0',
1016 `metric` double NOT NULL default '0',
1017 `percentile` double NOT NULL default '0',
1018 `importance_factor` double NOT NULL default '0',
1019 PRIMARY KEY (`ranking`),
1020 KEY `user_metric0_user_id` (`user_id`)
1021 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1023 -- --------------------------------------------------------
1026 -- Table structure for table `user_preferences`
1029 CREATE TABLE IF NOT EXISTS `user_preferences` (
1030 `user_id` int(11) NOT NULL default '0',
1031 `preference_name` varchar(20) NOT NULL default '',
1032 `dead1` varchar(20) default NULL,
1033 `set_date` int(11) NOT NULL default '0',
1034 `preference_value` varchar(255) default NULL,
1035 PRIMARY KEY (`user_id`)
1036 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1038 -- --------------------------------------------------------
1041 -- Table structure for table `user_ratings`
1044 CREATE TABLE IF NOT EXISTS `user_ratings` (
1045 `rated_by` int(11) NOT NULL default '0',
1046 `user_id` int(11) NOT NULL default '0',
1047 `rate_field` int(11) NOT NULL default '0',
1048 `rating` int(11) NOT NULL default '0',
1049 KEY `user_ratings_user_id` (`user_id`)
1050 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1052 -- --------------------------------------------------------
1055 -- Table structure for table `users`
1058 CREATE TABLE IF NOT EXISTS `users` (
1059 `user_id` int(11) NOT NULL auto_increment,
1060 `user_name` varchar(25) NOT NULL default '',
1061 `email` text NOT NULL,
1062 `user_pw` varchar(32) NOT NULL default '',
1063 `realname` varchar(32) NOT NULL default '',
1064 `status` char(1) NOT NULL default 'A',
1065 `shell` varchar(20) NOT NULL default '/bin/bash',
1066 `unix_pw` varchar(40) NOT NULL default '',
1067 `unix_status` char(1) NOT NULL default 'N',
1068 `unix_uid` int(11) NOT NULL default '0',
1069 `unix_box` varchar(10) NOT NULL default 'shell1',
1070 `add_date` int(11) NOT NULL default '0',
1071 `confirm_hash` varchar(32) default NULL,
1072 `mail_siteupdates` int(11) NOT NULL default '0',
1073 `mail_va` int(11) NOT NULL default '0',
1074 `authorized_keys` varchar(100) default NULL,
1075 `email_new` varchar(25) default NULL,
1076 `people_view_skills` int(11) NOT NULL default '0',
1077 `people_resume` varchar(255) NOT NULL default '',
1078 `timezone` varchar(64) default 'GMT',
1079 `language` int(11) NOT NULL default '1',
1080 `block_ratings` int(11) default '0',
1081 `jabber_address` varchar(100) default NULL,
1082 `jabber_only` int(11) default NULL,
1083 `address` varchar(100) default NULL,
1084 `phone` varchar(25) default NULL,
1085 `fax` varchar(25) default NULL,
1086 `title` varchar(25) default NULL,
1087 `firstname` varchar(60) default NULL,
1088 `lastname` varchar(60) default NULL,
1089 `address2` varchar(100) default NULL,
1090 `ccode` char(2) default 'US',
1091 `theme_id` int(11) default NULL,
1092 `type_id` int(11) default '1',
1093 `unix_gid` int(11) default '0',
1094 PRIMARY KEY (`user_id`),
1095 UNIQUE KEY `users_namename_uniq` (`user_name`),
1096 KEY `users_status` (`status`)
1097 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1099 -- --------------------------------------------------------
1102 -- Table structure for table `project_sums_agg`
1105 CREATE TABLE IF NOT EXISTS `project_sums_agg` (
1106 `group_id` int(11) NOT NULL default '0',
1107 `type` char(4) NOT NULL default '',
1108 `count` int(11) NOT NULL default '0'
1109 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1111 -- --------------------------------------------------------
1114 -- Table structure for table `prdb_dbs`
1117 CREATE TABLE IF NOT EXISTS `prdb_dbs` (
1118 `dbid` int(11) NOT NULL auto_increment,
1119 `group_id` int(11) NOT NULL default '0',
1120 `dbname` varchar(255) NOT NULL default '',
1121 `dbusername` varchar(25) NOT NULL default '',
1122 `dbuserpass` varchar(32) NOT NULL default '',
1123 `requestdate` int(11) NOT NULL default '0',
1124 `dbtype` int(11) NOT NULL default '0',
1125 `created_by` int(11) NOT NULL default '0',
1126 `state` int(11) NOT NULL default '0',
1127 PRIMARY KEY (`dbid`),
1128 UNIQUE KEY `idx_prdb_dbname` (`dbname`),
1129 KEY `prdbdbs_groupid` (`group_id`)
1130 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1132 -- --------------------------------------------------------
1135 -- Table structure for table `prdb_states`
1138 CREATE TABLE IF NOT EXISTS `prdb_states` (
1139 `stateid` int(11) NOT NULL default '0',
1141 KEY `prdbstates_stateid` (`stateid`)
1142 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1144 -- --------------------------------------------------------
1147 -- Table structure for table `prdb_types`
1150 CREATE TABLE IF NOT EXISTS `prdb_types` (
1151 `dbtypeid` int(11) NOT NULL default '0',
1152 `dbservername` varchar(25) NOT NULL default '',
1153 `dbsoftware` varchar(25) NOT NULL default ''
1154 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1156 -- --------------------------------------------------------
1159 -- Table structure for table `prweb_vhost`
1162 CREATE TABLE IF NOT EXISTS `prweb_vhost` (
1163 `vhostid` int(11) NOT NULL auto_increment,
1164 `vhost_name` varchar(255) default NULL,
1165 `docdir` varchar(255) default NULL,
1166 `cgidir` varchar(255) default NULL,
1167 `group_id` int(11) NOT NULL default '0',
1168 PRIMARY KEY (`vhostid`),
1169 UNIQUE KEY `idx_vhost_hostnames` (`vhost_name`),
1170 KEY `idx_vhost_groups` (`group_id`)
1171 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1173 -- --------------------------------------------------------
1176 -- Table structure for table `artifact_group_list`
1179 CREATE TABLE IF NOT EXISTS `artifact_group_list` (
1180 `group_artifact_id` int(11) NOT NULL auto_increment,
1181 `group_id` int(11) NOT NULL default '0',
1184 `is_public` int(11) NOT NULL default '0',
1185 `allow_anon` int(11) NOT NULL default '0',
1186 `email_all_updates` int(11) NOT NULL default '0',
1187 `email_address` text NOT NULL,
1188 `due_period` int(11) NOT NULL default '2592000',
1189 `submit_instructions` text,
1190 `browse_instructions` text,
1191 `datatype` int(11) NOT NULL default '0',
1192 `status_timeout` int(11) default NULL,
1193 `custom_status_field` int(11) NOT NULL default '0',
1194 `custom_renderer` text,
1195 PRIMARY KEY (`group_artifact_id`),
1196 KEY `artgrouplist_groupid_public` (`group_id`,`is_public`)
1197 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1199 -- --------------------------------------------------------
1202 -- Table structure for table `artifact_perm`
1205 CREATE TABLE IF NOT EXISTS `artifact_perm` (
1206 `id` int(11) NOT NULL auto_increment,
1207 `group_artifact_id` int(11) NOT NULL default '0',
1208 `user_id` int(11) NOT NULL default '0',
1209 `perm_level` int(11) NOT NULL default '0',
1211 UNIQUE KEY `artperm_groupartifactid_userid` (`group_artifact_id`,`user_id`)
1212 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1215 CREATE OR REPLACE VIEW `artifactperm_user_vw` AS
1216 SELECT ap.id, ap.group_artifact_id, ap.user_id, ap.perm_level, users.user_name, users.realname
1217 FROM artifact_perm AS ap, users
1218 WHERE users.user_id = ap.user_id;
1221 CREATE OR REPLACE VIEW `artifactperm_artgrouplist_vw` AS
1222 SELECT agl.group_artifact_id, agl.name, agl.description, agl.group_id, ap.user_id, ap.perm_level
1223 FROM artifact_perm AS ap, artifact_group_list AS agl
1224 WHERE ap.group_artifact_id = agl.group_artifact_id;
1227 -- --------------------------------------------------------
1230 -- Table structure for table `artifact_status`
1233 CREATE TABLE IF NOT EXISTS `artifact_status` (
1234 `id` int(11) NOT NULL auto_increment,
1235 `status_name` varchar(10) NOT NULL default '',
1237 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1239 -- --------------------------------------------------------
1242 -- Table structure for table `artifact`
1245 CREATE TABLE IF NOT EXISTS `artifact` (
1246 `artifact_id` int(11) NOT NULL auto_increment,
1247 `group_artifact_id` int(11) NOT NULL default '0',
1248 `status_id` int(11) NOT NULL default '1',
1249 `priority` int(11) NOT NULL default '3',
1250 `submitted_by` int(11) NOT NULL default '100',
1251 `assigned_to` int(11) NOT NULL default '100',
1252 `open_date` int(11) NOT NULL default '0',
1253 `close_date` int(11) NOT NULL default '0',
1254 `summary` text NOT NULL,
1255 `details` text NOT NULL,
1256 `last_modified_date` int(11) default NULL,
1257 PRIMARY KEY (`artifact_id`),
1258 KEY `art_groupartid` (`group_artifact_id`),
1259 KEY `art_groupartid_statusid` (`group_artifact_id`,`status_id`),
1260 KEY `art_groupartid_assign` (`group_artifact_id`,`assigned_to`),
1261 KEY `art_groupartid_submit` (`group_artifact_id`,`submitted_by`),
1262 KEY `art_submit_status` (`submitted_by`,`status_id`),
1263 KEY `art_assign_status` (`assigned_to`,`status_id`),
1264 KEY `art_groupartid_artifactid` (`group_artifact_id`,`artifact_id`)
1265 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1267 -- --------------------------------------------------------
1270 -- Table structure for table `artifact_history`
1273 CREATE TABLE IF NOT EXISTS `artifact_history` (
1274 `id` int(11) NOT NULL auto_increment,
1275 `artifact_id` int(11) NOT NULL default '0',
1276 `field_name` text NOT NULL,
1277 `old_value` text NOT NULL,
1278 `mod_by` int(11) NOT NULL default '0',
1279 `entrydate` int(11) NOT NULL default '0',
1281 KEY `arthistory_artid_entrydate` (`artifact_id`,`entrydate`)
1282 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1285 CREATE OR REPLACE VIEW `artifact_history_user_vw` AS
1286 SELECT ah.id, ah.artifact_id, ah.field_name, ah.old_value, ah.entrydate, users.user_name
1287 FROM artifact_history AS ah, users
1288 WHERE ah.mod_by = users.user_id;
1291 -- --------------------------------------------------------
1294 -- Table structure for table `artifact_file`
1297 CREATE TABLE IF NOT EXISTS `artifact_file` (
1298 `id` int(11) NOT NULL auto_increment,
1299 `artifact_id` int(11) NOT NULL default '0',
1300 `description` text NOT NULL,
1301 `bin_data` text NOT NULL,
1302 `filename` text NOT NULL,
1303 `filesize` int(11) NOT NULL default '0',
1304 `filetype` text NOT NULL,
1305 `adddate` int(11) NOT NULL default '0',
1306 `submitted_by` int(11) NOT NULL default '0',
1308 KEY `artfile_artid_adddate` (`artifact_id`,`adddate`)
1309 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1312 CREATE OR REPLACE VIEW `artifact_file_user_vw` AS
1313 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
1314 FROM artifact_file AS af, users
1315 WHERE af.submitted_by = users.user_id;
1318 -- --------------------------------------------------------
1321 -- Table structure for table `artifact_message`
1324 CREATE TABLE IF NOT EXISTS `artifact_message` (
1325 `id` int(11) NOT NULL auto_increment,
1326 `artifact_id` int(11) NOT NULL default '0',
1327 `submitted_by` int(11) NOT NULL default '0',
1328 `from_email` text NOT NULL,
1329 `adddate` int(11) NOT NULL default '0',
1330 `body` text NOT NULL,
1332 KEY `artmessage_artid_adddate` (`artifact_id`,`adddate`)
1333 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1336 CREATE OR REPLACE VIEW `artifact_message_user_vw` AS
1337 SELECT am.id, am.artifact_id, am.from_email, am.body, am.adddate, users.user_id, users.email, users.user_name, users.realname
1338 FROM artifact_message AS am, users
1339 WHERE am.submitted_by = users.user_id;
1342 -- --------------------------------------------------------
1345 -- Table structure for table `artifact_monitor`
1348 CREATE TABLE IF NOT EXISTS `artifact_monitor` (
1349 `id` int(11) NOT NULL auto_increment,
1350 `artifact_id` int(11) NOT NULL default '0',
1351 `user_id` int(11) NOT NULL default '0',
1354 KEY `artmonitor_useridartid` (`user_id`,`artifact_id`)
1355 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1357 -- --------------------------------------------------------
1360 -- Table structure for table `artifact_canned_responses`
1363 CREATE TABLE IF NOT EXISTS `artifact_canned_responses` (
1364 `id` int(11) NOT NULL auto_increment,
1365 `group_artifact_id` int(11) NOT NULL default '0',
1366 `title` text NOT NULL,
1367 `body` text NOT NULL,
1369 KEY `artifactcannedresponses_groupid` (`group_artifact_id`)
1370 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1372 -- --------------------------------------------------------
1375 -- Table structure for table `artifact_counts_agg`
1378 CREATE TABLE IF NOT EXISTS `artifact_counts_agg` (
1379 `group_artifact_id` int(11) NOT NULL default '0',
1380 `count` int(11) NOT NULL default '0',
1381 `open_count` int(11) default '0'
1382 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1384 -- --------------------------------------------------------
1387 -- Table structure for table `stats_site_pages_by_day`
1390 CREATE TABLE IF NOT EXISTS `stats_site_pages_by_day` (
1391 `month` int(11) default NULL,
1392 `day` int(11) default NULL,
1393 `site_page_views` int(11) default NULL,
1394 KEY `statssitepagesbyday_month_day` (`month`,`day`)
1395 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1397 -- --------------------------------------------------------
1400 -- Table structure for table `massmail_queue`
1403 CREATE TABLE IF NOT EXISTS `massmail_queue` (
1404 `id` int(11) NOT NULL auto_increment,
1405 `type` varchar(8) NOT NULL default '',
1406 `subject` varchar(100) NOT NULL default '',
1407 `message` varchar(255) NOT NULL default '',
1408 `queued_date` int(11) NOT NULL default '0',
1409 `last_userid` int(11) NOT NULL default '0',
1410 `failed_date` int(11) NOT NULL default '0',
1411 `finished_date` int(11) NOT NULL default '0',
1413 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1415 -- --------------------------------------------------------
1418 -- Table structure for table `stats_agg_site_by_group`
1421 CREATE TABLE IF NOT EXISTS `stats_agg_site_by_group` (
1422 `month` int(11) default NULL,
1423 `day` int(11) default NULL,
1424 `group_id` int(11) default NULL,
1425 `count` int(11) default NULL,
1426 UNIQUE KEY `statssitebygroup_month_day_group` (`month`,`day`,`group_id`)
1427 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1429 -- --------------------------------------------------------
1432 -- Table structure for table `stats_project_metric`
1435 CREATE TABLE IF NOT EXISTS `stats_project_metric` (
1436 `month` int(11) NOT NULL default '0',
1437 `day` int(11) NOT NULL default '0',
1438 `ranking` int(11) NOT NULL default '0',
1439 `percentile` double NOT NULL default '0',
1440 `group_id` int(11) NOT NULL default '0',
1441 UNIQUE KEY `statsprojectmetric_month_day_group` (`month`,`day`,`group_id`)
1442 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1444 -- --------------------------------------------------------
1447 -- Table structure for table `stats_agg_logo_by_group`
1450 CREATE TABLE IF NOT EXISTS `stats_agg_logo_by_group` (
1451 `month` int(11) default NULL,
1452 `day` int(11) default NULL,
1453 `group_id` int(11) default NULL,
1454 `count` int(11) default NULL,
1455 UNIQUE KEY `statslogobygroup_month_day_grou` (`month`,`day`,`group_id`)
1456 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1458 -- --------------------------------------------------------
1461 -- Table structure for table `stats_subd_pages`
1464 CREATE TABLE IF NOT EXISTS `stats_subd_pages` (
1465 `month` int(11) NOT NULL default '0',
1466 `day` int(11) NOT NULL default '0',
1467 `group_id` int(11) NOT NULL default '0',
1468 `pages` int(11) NOT NULL default '0',
1469 UNIQUE KEY `statssubdpages_month_day_group` (`month`,`day`,`group_id`)
1470 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1472 -- --------------------------------------------------------
1475 -- Table structure for table `stats_cvs_user`
1478 CREATE TABLE IF NOT EXISTS `stats_cvs_user` (
1479 `month` int(11) NOT NULL default '0',
1480 `day` int(11) NOT NULL default '0',
1481 `group_id` int(11) NOT NULL default '0',
1482 `user_id` int(11) NOT NULL default '0',
1483 `checkouts` int(11) NOT NULL default '0',
1484 `commits` int(11) NOT NULL default '0',
1485 `adds` int(11) NOT NULL default '0'
1486 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1488 -- --------------------------------------------------------
1491 -- Table structure for table `stats_cvs_group`
1494 CREATE TABLE IF NOT EXISTS `stats_cvs_group` (
1495 `month` int(11) NOT NULL default '0',
1496 `day` int(11) NOT NULL default '0',
1497 `group_id` int(11) NOT NULL default '0',
1498 `checkouts` int(11) NOT NULL default '0',
1499 `commits` int(11) NOT NULL default '0',
1500 `adds` int(11) NOT NULL default '0',
1501 UNIQUE KEY `statscvsgroup_month_day_group` (`month`,`day`,`group_id`)
1502 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1504 -- --------------------------------------------------------
1507 -- Table structure for table `stats_project_developers`
1510 CREATE TABLE IF NOT EXISTS `stats_project_developers` (
1511 `month` int(11) NOT NULL default '0',
1512 `day` int(11) NOT NULL default '0',
1513 `group_id` int(11) NOT NULL default '0',
1514 `developers` int(11) NOT NULL default '0',
1515 UNIQUE KEY `statsprojectdev_month_day_group` (`month`,`day`,`group_id`)
1516 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1518 -- --------------------------------------------------------
1521 -- Table structure for table `stats_project`
1524 CREATE TABLE IF NOT EXISTS `stats_project` (
1525 `month` int(11) NOT NULL default '0',
1526 `day` int(11) NOT NULL default '0',
1527 `group_id` int(11) NOT NULL default '0',
1528 `file_releases` int(11) default '0',
1529 `msg_posted` int(11) default '0',
1530 `msg_uniq_auth` int(11) default '0',
1531 `bugs_opened` int(11) default '0',
1532 `bugs_closed` int(11) default '0',
1533 `support_opened` int(11) default '0',
1534 `support_closed` int(11) default '0',
1535 `patches_opened` int(11) default '0',
1536 `patches_closed` int(11) default '0',
1537 `artifacts_opened` int(11) default '0',
1538 `artifacts_closed` int(11) default '0',
1539 `tasks_opened` int(11) default '0',
1540 `tasks_closed` int(11) default '0',
1541 `help_requests` int(11) default '0',
1542 UNIQUE KEY `statsproject_month_day_group` (`month`,`day`,`group_id`)
1543 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1545 -- --------------------------------------------------------
1548 -- Table structure for table `stats_site`
1551 CREATE TABLE IF NOT EXISTS `stats_site` (
1552 `month` int(11) default NULL,
1553 `day` int(11) default NULL,
1554 `uniq_users` int(11) default NULL,
1555 `sessions` int(11) default NULL,
1556 `total_users` int(11) default NULL,
1557 `new_users` int(11) default NULL,
1558 `new_projects` int(11) default NULL,
1559 UNIQUE KEY `statssite_month_day` (`month`,`day`)
1560 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1562 -- --------------------------------------------------------
1565 -- Table structure for table `activity_log`
1568 CREATE TABLE IF NOT EXISTS `activity_log` (
1569 `day` int(11) NOT NULL default '0',
1570 `hour` int(11) NOT NULL default '0',
1571 `group_id` int(11) NOT NULL default '0',
1572 `browser` varchar(8) NOT NULL default 'OTHER',
1573 `ver` double NOT NULL default '0',
1574 `platform` varchar(8) NOT NULL default 'OTHER',
1575 `time` int(11) NOT NULL default '0',
1577 `type` int(11) NOT NULL default '0'
1578 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1580 -- --------------------------------------------------------
1583 -- Table structure for table `user_metric_history`
1586 CREATE TABLE IF NOT EXISTS `user_metric_history` (
1587 `month` int(11) NOT NULL default '0',
1588 `day` int(11) NOT NULL default '0',
1589 `user_id` int(11) NOT NULL default '0',
1590 `ranking` int(11) NOT NULL default '0',
1591 `metric` double NOT NULL default '0',
1592 KEY `usermetrichistory_useridmonthday` (`user_id`,`month`,`day`)
1593 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1595 -- --------------------------------------------------------
1598 -- Table structure for table `frs_dlstats_filetotal_agg`
1601 CREATE TABLE IF NOT EXISTS `frs_dlstats_filetotal_agg` (
1602 `file_id` int(11) NOT NULL default '0',
1603 `downloads` int(11) default NULL
1604 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1606 -- --------------------------------------------------------
1609 -- Table structure for table `stats_project_months`
1612 CREATE TABLE IF NOT EXISTS `stats_project_months` (
1613 `month` int(11) default NULL,
1614 `group_id` int(11) default NULL,
1615 `developers` int(11) default NULL,
1616 `group_ranking` int(11) default NULL,
1617 `group_metric` double default NULL,
1618 `logo_showings` int(11) default NULL,
1619 `downloads` int(11) default NULL,
1620 `site_views` int(11) default NULL,
1621 `subdomain_views` int(11) default NULL,
1622 `page_views` int(11) default NULL,
1623 `file_releases` int(11) default NULL,
1624 `msg_posted` int(11) default NULL,
1625 `msg_uniq_auth` int(11) default NULL,
1626 `bugs_opened` int(11) default NULL,
1627 `bugs_closed` int(11) default NULL,
1628 `support_opened` int(11) default NULL,
1629 `support_closed` int(11) default NULL,
1630 `patches_opened` int(11) default NULL,
1631 `patches_closed` int(11) default NULL,
1632 `artifacts_opened` int(11) default NULL,
1633 `artifacts_closed` int(11) default NULL,
1634 `tasks_opened` int(11) default NULL,
1635 `tasks_closed` int(11) default NULL,
1636 `help_requests` int(11) default NULL,
1637 `cvs_checkouts` int(11) default NULL,
1638 `cvs_commits` int(11) default NULL,
1639 `cvs_adds` int(11) default NULL,
1640 KEY `statsprojectmonths_groupid` (`group_id`),
1641 KEY `statsprojectmonths_groupid_mont` (`group_id`,`month`)
1642 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1644 -- --------------------------------------------------------
1647 -- Table structure for table `stats_site_months`
1650 CREATE TABLE IF NOT EXISTS `stats_site_months` (
1651 `month` int(11) default NULL,
1652 `site_page_views` int(11) default NULL,
1653 `downloads` int(11) default NULL,
1654 `subdomain_views` int(11) default NULL,
1655 `msg_posted` int(11) default NULL,
1656 `bugs_opened` int(11) default NULL,
1657 `bugs_closed` int(11) default NULL,
1658 `support_opened` int(11) default NULL,
1659 `support_closed` int(11) default NULL,
1660 `patches_opened` int(11) default NULL,
1661 `patches_closed` int(11) default NULL,
1662 `artifacts_opened` int(11) default NULL,
1663 `artifacts_closed` int(11) default NULL,
1664 `tasks_opened` int(11) default NULL,
1665 `tasks_closed` int(11) default NULL,
1666 `help_requests` int(11) default NULL,
1667 `cvs_checkouts` int(11) default NULL,
1668 `cvs_commits` int(11) default NULL,
1669 `cvs_adds` int(11) default NULL,
1670 KEY `statssitemonths_month` (`month`)
1671 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1673 -- --------------------------------------------------------
1676 -- Table structure for table `trove_agg`
1679 CREATE TABLE IF NOT EXISTS `trove_agg` (
1680 `trove_cat_id` int(11) default NULL,
1681 `group_id` int(11) default NULL,
1682 `group_name` varchar(40) default NULL,
1683 `unix_group_name` varchar(30) default NULL,
1684 `status` char(1) default NULL,
1685 `register_time` int(11) default NULL,
1686 `short_description` varchar(255) default NULL,
1687 `percentile` double default NULL,
1688 `ranking` int(11) default NULL,
1689 KEY `troveagg_trovecatid_ranking` (`trove_cat_id`,`ranking`)
1690 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1692 -- --------------------------------------------------------
1695 -- Table structure for table `trove_treesums`
1698 CREATE TABLE IF NOT EXISTS `trove_treesums` (
1699 `trove_treesums_id` int(11) NOT NULL auto_increment,
1700 `trove_cat_id` int(11) NOT NULL default '0',
1701 `limit_1` int(11) NOT NULL default '0',
1702 `subprojects` int(11) NOT NULL default '0',
1703 PRIMARY KEY (`trove_treesums_id`)
1704 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1706 -- --------------------------------------------------------
1709 -- Table structure for table `frs_dlstats_file`
1712 CREATE TABLE IF NOT EXISTS `frs_dlstats_file` (
1713 `ip_address` varchar(25) default NULL,
1714 `file_id` int(11) default NULL,
1715 `month` int(11) default NULL,
1716 `day` int(11) default NULL,
1717 `user_id` int(11) default NULL
1718 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1720 -- --------------------------------------------------------
1723 -- Table structure for table `group_cvs_history`
1726 CREATE TABLE IF NOT EXISTS `group_cvs_history` (
1727 `id` int(11) NOT NULL auto_increment,
1728 `group_id` int(11) NOT NULL default '0',
1729 `user_name` varchar(80) NOT NULL default '',
1730 `cvs_commits` int(11) NOT NULL default '0',
1731 `cvs_commits_wk` int(11) NOT NULL default '0',
1732 `cvs_adds` int(11) NOT NULL default '0',
1733 `cvs_adds_wk` int(11) NOT NULL default '0',
1735 KEY `groupcvshistory_groupid` (`group_id`)
1736 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1738 -- --------------------------------------------------------
1741 -- Table structure for table `themes`
1744 CREATE TABLE IF NOT EXISTS `themes` (
1745 `theme_id` int(11) NOT NULL auto_increment,
1746 `dirname` varchar(80) default NULL,
1747 `fullname` varchar(80) default NULL,
1748 `enabled` tinyint(1) NOT NULL default '1',
1749 PRIMARY KEY (`theme_id`),
1750 UNIQUE KEY `themes_theme_id_key` (`theme_id`)
1751 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1753 -- --------------------------------------------------------
1756 -- Table structure for table `supported_languages`
1759 CREATE TABLE IF NOT EXISTS `supported_languages` (
1760 `language_id` int(11) NOT NULL auto_increment,
1762 `filename` varchar(25) default NULL,
1763 `classname` varchar(25) default NULL,
1764 `language_code` varchar(5) default NULL,
1765 PRIMARY KEY (`language_id`),
1766 UNIQUE KEY `supportedlanguage_code` (`language_code`)
1767 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1769 -- --------------------------------------------------------
1772 -- Table structure for table `skills_data_types`
1775 CREATE TABLE IF NOT EXISTS `skills_data_types` (
1776 `type_id` int(11) NOT NULL auto_increment,
1777 `type_name` varchar(25) NOT NULL default '',
1778 PRIMARY KEY (`type_id`)
1779 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1781 -- --------------------------------------------------------
1784 -- Table structure for table `skills_data`
1787 CREATE TABLE IF NOT EXISTS `skills_data` (
1788 `skills_data_id` int(11) NOT NULL auto_increment,
1789 `user_id` int(11) NOT NULL default '0',
1790 `type` int(11) NOT NULL default '0',
1791 `title` varchar(100) NOT NULL default '',
1792 `start` int(11) NOT NULL default '0',
1793 `finish` int(11) NOT NULL default '0',
1794 `keywords` varchar(255) NOT NULL default '',
1795 PRIMARY KEY (`skills_data_id`)
1796 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1799 CREATE OR REPLACE VIEW `frs_file_vw` AS
1800 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
1801 FROM frs_filetype, frs_processor, (frs_file LEFT JOIN frs_dlstats_filetotal_agg ON frs_dlstats_filetotal_agg.file_id = frs_file.file_id)
1802 WHERE frs_filetype.type_id = frs_file.type_id AND frs_processor.processor_id = frs_file.processor_id;
1805 -- --------------------------------------------------------
1808 -- Table structure for table `project_category`
1811 CREATE TABLE IF NOT EXISTS `project_category` (
1812 `category_id` int(11) NOT NULL auto_increment,
1813 `group_project_id` int(11) default NULL,
1814 `category_name` varchar(25) default NULL,
1815 PRIMARY KEY (`category_id`),
1816 UNIQUE KEY `project_categor_category_id_key` (`category_id`),
1817 KEY `projectcategory_groupprojectid` (`group_project_id`)
1818 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1820 -- --------------------------------------------------------
1823 -- Table structure for table `project_task_artifact`
1826 CREATE TABLE IF NOT EXISTS `project_task_artifact` (
1827 `project_task_id` int(11) NOT NULL default '0',
1828 `artifact_id` int(11) NOT NULL default '0',
1829 PRIMARY KEY (`project_task_id`),
1830 KEY `projecttaskartifact_artidprojtaskid` (`artifact_id`,`project_task_id`)
1831 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1834 CREATE OR REPLACE VIEW `project_history_user_vw` AS
1835 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
1836 FROM users, project_history
1837 WHERE project_history.mod_by = users.user_id;
1840 -- --------------------------------------------------------
1843 -- Table structure for table `project_messages`
1846 CREATE TABLE IF NOT EXISTS `project_messages` (
1847 `project_message_id` int(11) NOT NULL auto_increment,
1848 `project_task_id` int(11) NOT NULL default '0',
1849 `body` varchar(255) default NULL,
1850 `posted_by` int(11) NOT NULL default '0',
1851 `postdate` int(11) NOT NULL default '0',
1852 PRIMARY KEY (`project_message_id`),
1853 UNIQUE KEY `project_messa_project_messa_key` (`project_message_id`),
1854 KEY `projectmsgs_projtaskidpostdate` (`project_task_id`,`postdate`)
1855 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1858 CREATE OR REPLACE VIEW `project_message_user_vw` AS
1859 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
1860 FROM users, project_messages
1861 WHERE project_messages.posted_by = users.user_id;
1864 CREATE OR REPLACE VIEW `frs_dlstats_file_agg_vw` AS
1865 SELECT frs_dlstats_file.`month`, frs_dlstats_file.`day`, frs_dlstats_file.file_id, count(*) AS downloads
1866 FROM frs_dlstats_file
1867 GROUP BY frs_dlstats_file.`month`, frs_dlstats_file.`day`, frs_dlstats_file.file_id;
1870 CREATE OR REPLACE VIEW `frs_dlstats_grouptotal_vw` AS
1871 SELECT frs_package.group_id, sum(frs_dlstats_filetotal_agg.downloads) AS downloads
1872 FROM frs_package, frs_release, frs_file, frs_dlstats_filetotal_agg
1873 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
1874 GROUP BY frs_package.group_id;
1877 CREATE OR REPLACE VIEW `frs_dlstats_group_vw` AS
1878 SELECT frs_package.group_id, fdfa.`month`, fdfa.`day`, sum(fdfa.downloads) AS downloads
1879 FROM frs_package, frs_release, frs_file, frs_dlstats_file_agg_vw AS fdfa
1880 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
1881 GROUP BY frs_package.group_id, fdfa.`month`, fdfa.`day`;
1884 CREATE OR REPLACE VIEW `stats_project_vw` AS
1885 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
1886 FROM (((((((stats_project_developers AS spd
1887 LEFT JOIN stats_project AS sp USING (`month`, `day`, group_id))
1888 LEFT JOIN stats_project_metric AS spm USING (`month`, `day`, group_id))
1889 LEFT JOIN stats_cvs_group AS scg USING (`month`, `day`, group_id))
1890 LEFT JOIN stats_agg_site_by_group AS sasbg USING (`month`, `day`, group_id))
1891 LEFT JOIN stats_agg_logo_by_group AS salbg USING (`month`, `day`, group_id))
1892 LEFT JOIN stats_subd_pages AS ssp USING (`month`, `day`, group_id))
1893 LEFT JOIN frs_dlstats_group_vw AS fdga USING (`month`, `day`, group_id));
1896 CREATE OR REPLACE VIEW `stats_project_all_vw` AS
1898 stats_project_months.group_id,
1899 avg(stats_project_months.developers) AS developers,
1900 avg(stats_project_months.group_ranking) AS group_ranking,
1901 avg(stats_project_months.group_metric) AS group_metric,
1902 sum(stats_project_months.logo_showings) AS logo_showings,
1903 sum(stats_project_months.downloads) AS downloads,
1904 sum(stats_project_months.site_views) AS site_views,
1905 sum(stats_project_months.subdomain_views) AS subdomain_views,
1906 sum(stats_project_months.page_views) AS page_views,
1907 sum(stats_project_months.file_releases) AS file_releases,
1908 sum(stats_project_months.msg_posted) AS msg_posted,
1909 avg(stats_project_months.msg_uniq_auth) AS msg_uniq_auth,
1910 sum(stats_project_months.bugs_opened) AS bugs_opened,
1911 sum(stats_project_months.bugs_closed) AS bugs_closed,
1912 sum(stats_project_months.support_opened) AS support_opened,
1913 sum(stats_project_months.support_closed) AS support_closed,
1914 sum(stats_project_months.patches_opened) AS patches_opened,
1915 sum(stats_project_months.patches_closed) AS patches_closed,
1916 sum(stats_project_months.artifacts_opened) AS artifacts_opened,
1917 sum(stats_project_months.artifacts_closed) AS artifacts_closed,
1918 sum(stats_project_months.tasks_opened) AS tasks_opened,
1919 sum(stats_project_months.tasks_closed) AS tasks_closed,
1920 sum(stats_project_months.help_requests) AS help_requests,
1921 sum(stats_project_months.cvs_checkouts) AS cvs_checkouts,
1922 sum(stats_project_months.cvs_commits) AS cvs_commits,
1923 sum(stats_project_months.cvs_adds) AS cvs_adds
1924 FROM stats_project_months
1925 GROUP BY stats_project_months.group_id;
1928 CREATE OR REPLACE VIEW `stats_site_vw` AS
1929 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
1930 FROM stats_project_vw AS p, stats_site_pages_by_day AS sspbd
1931 WHERE p.`month` = sspbd.`month` AND p.`day` = sspbd.`day`
1932 GROUP BY p.`month`, p.`day`, sspbd.site_page_views;
1935 CREATE OR REPLACE VIEW `stats_site_all_vw` AS
1936 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
1937 FROM stats_site_months;
1940 -- --------------------------------------------------------
1943 -- Table structure for table `plugins`
1946 CREATE TABLE IF NOT EXISTS `plugins` (
1947 `plugin_id` int(11) NOT NULL auto_increment,
1948 `plugin_name` varchar(32) NOT NULL default '',
1949 `plugin_desc` varchar(255) default NULL,
1950 PRIMARY KEY (`plugin_id`),
1951 UNIQUE KEY `plugins_plugin_name_key` (`plugin_name`)
1952 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1954 -- --------------------------------------------------------
1957 -- Table structure for table `group_plugin`
1960 CREATE TABLE IF NOT EXISTS `group_plugin` (
1961 `group_plugin_id` int(11) NOT NULL auto_increment,
1962 `group_id` int(11) default NULL,
1963 `plugin_id` int(11) default NULL,
1964 PRIMARY KEY (`group_plugin_id`),
1965 KEY `groupplugin_groupid` (`group_id`)
1966 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1968 -- --------------------------------------------------------
1971 -- Table structure for table `user_plugin`
1974 CREATE TABLE IF NOT EXISTS `user_plugin` (
1975 `user_plugin_id` int(11) NOT NULL auto_increment,
1976 `user_id` int(11) NOT NULL default '0',
1977 `plugin_id` int(11) NOT NULL default '0',
1978 PRIMARY KEY (`user_plugin_id`)
1979 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1981 -- --------------------------------------------------------
1984 -- Table structure for table `cron_history`
1987 CREATE TABLE IF NOT EXISTS `cron_history` (
1988 `rundate` int(11) NOT NULL default '0',
1989 `job` varchar(255) default NULL,
1990 `output` varchar(255) default NULL,
1991 KEY `cronhist_rundate` (`rundate`),
1992 KEY `cronhist_jobrundate` (`job`,`rundate`)
1993 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1996 -- --------------------------------------------------------
1999 -- Table structure for table `country_code`
2002 CREATE TABLE IF NOT EXISTS `country_code` (
2003 `country_name` varchar(80) default NULL,
2004 `ccode` char(2) NOT NULL default ''
2005 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2007 -- --------------------------------------------------------
2010 -- Table structure for table `licenses`
2013 CREATE TABLE IF NOT EXISTS `licenses` (
2014 `license_id` varchar(10) NOT NULL default '',
2015 `license_name` varchar(100) default NULL
2016 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2018 -- --------------------------------------------------------
2021 -- Table structure for table `user_type`
2024 CREATE TABLE IF NOT EXISTS `user_type` (
2025 `type_id` int(11) NOT NULL auto_increment,
2026 `type_name` varchar(25) default NULL,
2027 PRIMARY KEY (`type_id`)
2028 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2030 -- --------------------------------------------------------
2033 -- Table structure for table `role`
2036 CREATE TABLE IF NOT EXISTS `role` (
2037 `role_id` int(10) NOT NULL auto_increment,
2038 `group_id` int(11) NOT NULL default '0',
2039 `role_name` varchar(25) default NULL,
2040 PRIMARY KEY (`role_id`),
2041 UNIQUE KEY `role_groupidroleid` (`group_id`,`role_id`)
2042 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2044 -- --------------------------------------------------------
2047 -- Table structure for table `project_perm`
2050 CREATE TABLE IF NOT EXISTS `project_perm` (
2051 `id` varchar(10) NOT NULL default '',
2052 `group_project_id` int(11) NOT NULL default '0',
2053 `user_id` int(11) NOT NULL default '0',
2054 `perm_level` int(11) NOT NULL default '0',
2055 KEY `projectperm_useridgroupprojid` (`user_id`,`group_project_id`)
2056 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2058 -- --------------------------------------------------------
2061 -- Table structure for table `forum_perm`
2064 CREATE TABLE IF NOT EXISTS `forum_perm` (
2065 `id` int(11) NOT NULL auto_increment,
2066 `group_forum_id` int(11) NOT NULL default '0',
2067 `user_id` int(11) NOT NULL default '0',
2068 `perm_level` int(11) NOT NULL default '0',
2070 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2072 -- --------------------------------------------------------
2075 -- Table structure for table `role_setting`
2078 CREATE TABLE IF NOT EXISTS `role_setting` (
2079 `role_id` int(11) NOT NULL default '0',
2080 `section_name` varchar(25) NOT NULL default '',
2081 `ref_id` int(11) NOT NULL default '0',
2082 `value` char(2) NOT NULL default ''
2083 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2085 -- --------------------------------------------------------
2088 -- Table structure for table `artifact_extra_field_list`
2091 CREATE TABLE IF NOT EXISTS `artifact_extra_field_list` (
2092 `extra_field_id` int(11) NOT NULL auto_increment,
2093 `group_artifact_id` int(11) NOT NULL default '0',
2094 `field_name` text NOT NULL,
2095 `field_type` int(11) default '1',
2096 `attribute1` int(11) default '0',
2097 `attribute2` int(11) default '0',
2098 `is_required` int(11) NOT NULL default '0',
2100 PRIMARY KEY (`extra_field_id`),
2101 KEY `artifactextrafieldlist_groupartid` (`group_artifact_id`)
2102 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2104 -- --------------------------------------------------------
2107 -- Table structure for table `artifact_extra_field_elements`
2110 CREATE TABLE IF NOT EXISTS `artifact_extra_field_elements` (
2111 `element_id` int(11) NOT NULL auto_increment,
2112 `extra_field_id` int(11) NOT NULL default '0',
2113 `element_name` text NOT NULL,
2114 `status_id` int(11) NOT NULL default '0',
2115 PRIMARY KEY (`element_id`),
2116 KEY `artifactextrafldlmts_extrafieldid` (`extra_field_id`)
2117 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2119 -- --------------------------------------------------------
2122 -- Table structure for table `artifact_extra_field_data`
2125 CREATE TABLE IF NOT EXISTS `artifact_extra_field_data` (
2126 `data_id` int(11) NOT NULL auto_increment,
2127 `artifact_id` int(11) NOT NULL default '0',
2129 `extra_field_id` int(11) default '0',
2130 PRIMARY KEY (`data_id`),
2131 KEY `artifactextrafielddata_artifactid` (`artifact_id`)
2132 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2134 -- --------------------------------------------------------
2137 -- Table structure for table `project_counts_agg`
2140 CREATE TABLE IF NOT EXISTS `project_counts_agg` (
2141 `group_project_id` int(11) NOT NULL default '0',
2142 `count` int(11) NOT NULL default '0',
2143 `open_count` int(11) default '0'
2144 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2147 CREATE OR REPLACE VIEW `project_group_list_vw` AS
2148 SELECT project_group_list.group_project_id, group_id, project_name, is_public, description, send_all_posts_to, `count`, open_count
2149 FROM (project_group_list
2150 LEFT JOIN project_counts_agg ON project_counts_agg.group_project_id = project_group_list.group_project_id);
2153 -- --------------------------------------------------------
2156 -- Table structure for table `project_task_external_order`
2159 CREATE TABLE IF NOT EXISTS `project_task_external_order` (
2160 `project_task_id` int(11) NOT NULL default '0',
2161 `external_id` int(11) NOT NULL default '0'
2162 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2165 CREATE OR REPLACE VIEW `project_depend_vw` AS
2166 SELECT pt.project_task_id, pd.is_dependent_on_task_id, pd.link_type, pt.end_date, pt.start_date
2167 FROM (project_task pt NATURAL JOIN project_dependencies pd);
2170 CREATE OR REPLACE VIEW `project_dependon_vw` AS
2171 SELECT pd.project_task_id, pd.is_dependent_on_task_id, pd.link_type, pt.end_date, pt.start_date
2172 FROM (project_task AS pt
2173 LEFT JOIN project_dependencies AS pd ON pd.is_dependent_on_task_id = pt.project_task_id)
2175 SELECT pd.project_task_id, pd.is_dependent_on_task_id, pd.link_type, pt.end_date, pt.start_date
2176 FROM (project_task AS pt
2177 RIGHT JOIN project_dependencies AS pd ON pd.is_dependent_on_task_id = pt.project_task_id);
2180 -- --------------------------------------------------------
2183 -- Table structure for table `group_join_request`
2186 CREATE TABLE IF NOT EXISTS `group_join_request` (
2187 `group_id` int(11) NOT NULL default '0',
2188 `user_id` int(11) NOT NULL default '0',
2189 `comments` varchar(255) default NULL,
2190 `request_date` int(11) default NULL
2191 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2194 CREATE OR REPLACE VIEW `project_task_vw` AS
2196 project_task.project_task_id,
2197 project_task.group_project_id,
2198 project_task.summary,
2199 project_task.details,
2200 project_task.percent_complete,
2201 project_task.priority,
2203 project_task.start_date,
2204 project_task.end_date,
2205 project_task.created_by,
2206 project_task.status_id,
2207 project_task.category_id,
2208 project_task.duration,
2209 project_task.parent_id,
2210 project_task.last_modified_date,
2211 project_category.category_name,
2212 project_status.status_name,
2216 LEFT JOIN project_category ON project_category.category_id = project_task.category_id)
2217 LEFT JOIN users ON users.user_id = project_task.created_by)
2218 LEFT JOIN project_status ON project_status.status_id = project_task.status_id;
2221 -- --------------------------------------------------------
2224 -- Table structure for table `artifact_type_monitor`
2227 CREATE TABLE IF NOT EXISTS `artifact_type_monitor` (
2228 `group_artifact_id` int(11) NOT NULL default '0',
2229 `user_id` int(11) NOT NULL default '0'
2230 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2232 -- --------------------------------------------------------
2235 -- Table structure for table `plugin_cvstracker_data_artifact`
2238 CREATE TABLE IF NOT EXISTS `plugin_cvstracker_data_artifact` (
2239 `id` int(11) NOT NULL auto_increment,
2240 `kind` int(11) NOT NULL default '0',
2241 `group_artifact_id` int(11) default NULL,
2242 `project_task_id` int(11) default NULL,
2244 KEY `plugin_cvstracker_group_artifact_id` (`group_artifact_id`)
2245 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2247 -- --------------------------------------------------------
2250 -- Table structure for table `plugin_cvstracker_data_master`
2253 CREATE TABLE IF NOT EXISTS `plugin_cvstracker_data_master` (
2254 `id` int(11) NOT NULL auto_increment,
2255 `holder_id` int(11) NOT NULL default '0',
2256 `log_text` varchar(255) default NULL,
2257 `file` varchar(25) NOT NULL default '',
2258 `prev_version` varchar(25) default NULL,
2259 `actual_version` varchar(25) default NULL,
2260 `author` varchar(25) NOT NULL default '',
2261 `cvs_date` int(11) NOT NULL default '0',
2263 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2265 -- --------------------------------------------------------
2267 CREATE OR REPLACE VIEW `nss_passwd` AS
2268 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
2270 WHERE users.unix_status = 'A';
2273 CREATE OR REPLACE VIEW `nss_shadow` AS
2274 SELECT users.user_name AS login, users.unix_pw AS passwd, 'n' AS expired, 'n' AS pwchange
2276 WHERE users.unix_status = 'A';
2279 -- Table structure for table `nss_groups`
2282 CREATE TABLE IF NOT EXISTS `nss_groups` (
2283 `user_id` int(11) default NULL,
2284 `group_id` int(11) default NULL,
2285 `name` varchar(30) default NULL,
2286 `gid` int(11) default NULL
2287 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2289 -- --------------------------------------------------------
2292 -- Table structure for table `nss_usergroups`
2295 CREATE TABLE IF NOT EXISTS `nss_usergroups` (
2296 `uid` int(11) default NULL,
2297 `gid` int(11) default NULL,
2298 `user_id` int(11) default NULL,
2299 `group_id` int(11) default NULL,
2300 `user_name` varchar(25) default NULL,
2301 `unix_group_name` varchar(30) default NULL
2302 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2304 -- --------------------------------------------------------
2307 -- Table structure for table `deleted_mailing_lists`
2310 CREATE TABLE IF NOT EXISTS `deleted_mailing_lists` (
2311 `mailing_list_name` varchar(30) default NULL,
2312 `delete_date` int(11) default NULL,
2313 `isdeleted` int(11) default NULL
2314 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2316 -- --------------------------------------------------------
2319 -- Table structure for table `deleted_groups`
2322 CREATE TABLE IF NOT EXISTS `deleted_groups` (
2323 `unix_group_name` varchar(30) default NULL,
2324 `delete_date` int(11) default NULL,
2325 `isdeleted` int(11) default NULL
2326 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2328 -- --------------------------------------------------------
2331 -- Table structure for table `artifact_query`
2334 CREATE TABLE IF NOT EXISTS `artifact_query` (
2335 `artifact_query_id` varchar(10) NOT NULL default '',
2336 `group_artifact_id` int(11) NOT NULL default '0',
2337 `user_id` int(11) NOT NULL default '0',
2338 `query_name` text NOT NULL
2339 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2341 -- --------------------------------------------------------
2344 -- Table structure for table `artifact_query_fields`
2347 CREATE TABLE IF NOT EXISTS `artifact_query_fields` (
2348 `artifact_query_id` int(11) NOT NULL default '0',
2349 `query_field_type` text NOT NULL,
2350 `query_field_id` int(11) NOT NULL default '0',
2351 `query_field_values` text NOT NULL
2352 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2355 CREATE OR REPLACE VIEW `artifact_group_list_vw` AS
2356 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
2357 FROM (artifact_group_list AS agl
2358 LEFT JOIN artifact_counts_agg AS aca USING (group_artifact_id));
2361 CREATE OR REPLACE VIEW `artifact_vw` AS
2362 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
2363 FROM users u, users u2, artifact_status, artifact
2364 WHERE artifact.assigned_to = u.user_id AND artifact.submitted_by = u2.user_id AND artifact.status_id = artifact_status.id;
2366 CREATE OR REPLACE VIEW `docdata_vw` AS
2384 doc_states.name AS state_name,
2385 doc_groups.groupname AS group_name,
2386 sl.name AS language_name
2388 NATURAL JOIN doc_states)
2389 NATURAL JOIN doc_groups)
2390 JOIN supported_languages sl ON sl.language_id = d.language_id)
2391 JOIN users ON users.user_id = d.created_by);
2394 CREATE TABLE IF NOT EXISTS `form_keys` (
2395 key_id int(11) NOT NULL auto_increment,
2396 `key` char(32) NOT NULL,
2397 creation_date int(11) NOT NULL,
2398 is_used int(11) default 0 NOT NULL,
2399 PRIMARY KEY (`key_id`),
2400 UNIQUE KEY `key` (`key`)
2401 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2405 CREATE TABLE IF NOT EXISTS `forum_attachment` (
2406 attachmentid int(11) NOT NULL,
2407 userid int(11) default 100 NOT NULL,
2408 dateline int(11) default 0 NOT NULL,
2409 filename character varying(100) DEFAULT '' NOT NULL,
2410 filedata text NOT NULL,
2411 visible smallint default 0 NOT NULL,
2412 counter smallint default 0 NOT NULL,
2413 filesize int(11) default 0 NOT NULL,
2414 msg_id int(11) default 0 NOT NULL,
2415 filehash character varying(32) DEFAULT '' NOT NULL,
2416 PRIMARY KEY (`attachmentid`)
2417 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2420 CREATE TABLE IF NOT EXISTS `forum_attachment_type` (
2421 extension character varying(20) DEFAULT '' NOT NULL,
2422 mimetype character varying(255) DEFAULT '' NOT NULL,
2423 size int(11) DEFAULT 0 NOT NULL,
2424 width smallint DEFAULT 0 NOT NULL,
2425 height smallint DEFAULT 0 NOT NULL,
2426 enabled smallint DEFAULT 1 NOT NULL,
2427 PRIMARY KEY (`extension`)
2428 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2431 CREATE OR REPLACE VIEW `forum_group_list_vw` AS
2433 forum_group_list.group_forum_id,
2434 forum_group_list.group_id,
2435 forum_group_list.forum_name,
2436 forum_group_list.is_public,
2437 forum_group_list.description,
2438 forum_group_list.allow_anonymous,
2439 forum_group_list.send_all_posts_to,
2440 forum_group_list.moderation_level,
2441 forum_agg_msg_count.count AS total,
2442 (SELECT max(forum.post_date) AS recent
2444 WHERE (forum.group_forum_id = forum_group_list.group_forum_id)) AS recent,
2445 (SELECT count(forum.thread_id) AS count
2447 WHERE (forum.group_forum_id = forum_group_list.group_forum_id)
2448 GROUP BY forum.thread_id) AS threads
2449 FROM (forum_group_list LEFT JOIN forum_agg_msg_count USING (group_forum_id));
2452 CREATE TABLE IF NOT EXISTS `forum_pending_messages` (
2453 msg_id int(11) NOT NULL auto_increment,
2454 group_forum_id int(11) DEFAULT 0 NOT NULL,
2455 posted_by int(11) DEFAULT 0 NOT NULL,
2456 subject text DEFAULT '' NOT NULL,
2457 body text DEFAULT '' NOT NULL,
2458 post_date int(11) DEFAULT 0 NOT NULL,
2459 is_followup_to int(11) DEFAULT 0 NOT NULL,
2460 thread_id int(11) DEFAULT 0 NOT NULL,
2461 has_followups int(11) DEFAULT 0,
2462 most_recent_date int(11) DEFAULT 0 NOT NULL,
2463 PRIMARY KEY (`msg_id`)
2464 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2468 CREATE TABLE IF NOT EXISTS `forum_pending_attachment` (
2469 attachmentid int(11) NOT NULL auto_increment,
2470 userid int(11) DEFAULT 100 NOT NULL,
2471 dateline int(11) DEFAULT 0 NOT NULL,
2472 filename character varying(100) DEFAULT '' NOT NULL,
2473 filedata text NOT NULL,
2474 visible smallint DEFAULT 0 NOT NULL,
2475 counter smallint DEFAULT 0 NOT NULL,
2476 filesize int(11) DEFAULT 0 NOT NULL,
2477 msg_id int(11) DEFAULT 0 NOT NULL,
2478 filehash character varying(32) DEFAULT '' NOT NULL,
2479 PRIMARY KEY (`attachmentid`)
2480 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2484 CREATE OR REPLACE VIEW `forum_user_vw` AS
2487 forum.group_forum_id,
2492 forum.is_followup_to,
2494 forum.has_followups,
2495 forum.most_recent_date,
2499 WHERE (forum.posted_by = users.user_id);
2503 CREATE OR REPLACE VIEW `forum_pending_user_vw` AS
2505 forum_pending_messages.msg_id,
2506 forum_pending_messages.group_forum_id,
2507 forum_pending_messages.posted_by,
2508 forum_pending_messages.subject,
2509 forum_pending_messages.body,
2510 forum_pending_messages.post_date,
2511 forum_pending_messages.is_followup_to,
2512 forum_pending_messages.thread_id,
2513 forum_pending_messages.has_followups,
2514 forum_pending_messages.most_recent_date,
2515 users.user_name, users.realname
2516 FROM forum_pending_messages, users
2517 WHERE (forum_pending_messages.posted_by = users.user_id);
2521 CREATE TABLE IF NOT EXISTS `group_activity_monitor` (
2522 group_id int(11) NOT NULL,
2523 user_id int(11) NOT NULL,
2525 PRIMARY KEY (`group_id`,`user_id`)
2526 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2530 CREATE OR REPLACE VIEW `activity_vw` AS
2533 `trackeropen` AS section,
2534 agl.group_artifact_id AS ref_id,
2535 a.artifact_id AS subref_id,
2536 a.summary AS description,
2537 a.open_date AS activity_date,
2538 u.user_id, u.user_name,
2540 FROM (artifact_group_list agl
2541 JOIN artifact a USING (group_artifact_id)), users u
2542 WHERE (u.user_id = a.submitted_by))
2546 `trackerclose` AS section,
2547 agl.group_artifact_id AS ref_id,
2548 a.artifact_id AS subref_id,
2549 a.summary AS description,
2550 a.close_date AS activity_date,
2554 FROM (artifact_group_list agl
2555 JOIN artifact a USING (group_artifact_id)), users u
2556 WHERE ((u.user_id = a.assigned_to) AND (a.close_date > 0)))
2560 `commit` AS section,
2561 agl.group_artifact_id AS ref_id,
2562 a.artifact_id AS subref_id,
2563 pcdm.log_text AS description,
2564 pcdm.cvs_date AS activity_date,
2565 u.user_id, u.user_name, u.realname
2566 FROM (artifact_group_list agl JOIN artifact a
2567 USING (group_artifact_id)), plugin_cvstracker_data_master pcdm, plugin_cvstracker_data_artifact pcda, users u
2568 WHERE (((pcdm.holder_id = pcda.id) AND (pcda.group_artifact_id = a.artifact_id)) AND (u.user_name = pcdm.author)))
2572 `frsrelease` AS section,
2573 frsp.package_id AS ref_id,
2574 frsr.release_id AS subref_id,
2575 frsr.name AS description,
2576 frsr.release_date AS activity_date,
2577 u.user_id, u.user_name,
2579 FROM (frs_package frsp JOIN frs_release frsr USING (package_id)), users u
2580 WHERE (u.user_id = frsr.released_by))
2584 `forumpost` AS section,
2585 fgl.group_forum_id AS ref_id,
2586 forum.msg_id AS subref_id,
2587 forum.subject AS description,
2588 forum.post_date AS activity_date,
2592 FROM (forum_group_list fgl
2593 JOIN forum USING (group_forum_id)), users u
2594 WHERE (u.user_id = forum.posted_by));
2598 -- --------------------------------------------------------
2601 -- Table structure for table `forum_thread_seq`
2604 CREATE TABLE IF NOT EXISTS `forum_thread_seq` (
2605 `value` int(11) NOT NULL default '1'
2606 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2608 DROP PROCEDURE IF EXISTS newval;
2610 CREATE PROCEDURE newval (IN tablename VARCHAR(64), OUT result INT)
2612 SET @s=CONCAT('SELECT value INTO @newvalue FROM ',tablename);
2613 SET @u=CONCAT('UPDATE ',tablename,' SET value=value+1 WHERE value=@newvalue;');
2614 PREPARE select_stmt FROM @s;
2615 PREPARE update_stmt FROM @u;
2618 EXECUTE select_stmt;
2619 IF @newvalue = NULL THEN
2622 EXECUTE update_stmt;
2623 IF row_count() = 1 THEN
2626 END LOOP update_loop;
2628 DEALLOCATE PREPARE select_stmt;
2629 DEALLOCATE PREPARE update_stmt;
2631 SET result=@newvalue;