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;
14 -- --------------------------------------------------------
17 -- Table structure for table `canned_responses`
20 CREATE TABLE IF NOT EXISTS `canned_responses` (
21 `response_id` int(11) NOT NULL auto_increment,
22 `response_title` varchar(25) NOT NULL default '',
23 `response_text` varchar(255) NOT NULL default '',
24 PRIMARY KEY (`response_id`)
25 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
27 -- --------------------------------------------------------
30 -- Table structure for table `db_images`
33 CREATE TABLE IF NOT EXISTS `db_images` (
34 `id` int(11) NOT NULL auto_increment,
35 `group_id` int(11) NOT NULL default '0',
36 `description` varchar(255) NOT NULL default '',
37 `bin_data` mediumblob NOT NULL,
38 `filename` varchar(25) NOT NULL default '',
39 `filesize` int(11) NOT NULL default '0',
40 `filetype` varchar(10) NOT NULL default '',
41 `width` int(11) NOT NULL default '0',
42 `height` int(11) NOT NULL default '0',
43 `upload_date` int(11) default NULL,
44 `version` int(11) default NULL,
46 KEY `db_images_group` (`group_id`)
47 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
49 -- --------------------------------------------------------
52 -- Table structure for table `doc_data`
55 CREATE TABLE IF NOT EXISTS `doc_data` (
56 `docid` int(11) NOT NULL auto_increment,
57 `stateid` int(11) NOT NULL default '0',
58 `title` varchar(255) NOT NULL default '',
59 `data` varchar(255) NOT NULL default '',
60 `updatedate` int(11) NOT NULL default '0',
61 `createdate` int(11) NOT NULL default '0',
62 `created_by` int(11) NOT NULL default '0',
63 `doc_group` int(11) NOT NULL default '0',
64 `description` varchar(255) default NULL,
65 `language_id` int(11) NOT NULL default '1',
66 `filename` varchar(25) default NULL,
67 `filetype` varchar(10) default NULL,
68 `group_id` int(11) default NULL,
69 `filesize` int(11) NOT NULL default '0',
70 PRIMARY KEY (`docid`),
71 KEY `docdata_groupid` (`group_id`,`doc_group`)
72 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
74 -- --------------------------------------------------------
77 -- Table structure for table `doc_groups`
80 CREATE TABLE IF NOT EXISTS `doc_groups` (
81 `doc_group` int(11) NOT NULL auto_increment,
82 `groupname` varchar(25) NOT NULL default '',
83 `group_id` int(11) NOT NULL default '0',
84 `parent_doc_group` int(11) NOT NULL default '0',
85 PRIMARY KEY (`doc_group`),
86 KEY `doc_groups_group` (`group_id`),
87 KEY `docgroups_parentdocgroup` (`parent_doc_group`)
88 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
90 -- --------------------------------------------------------
93 -- Table structure for table `doc_states`
96 CREATE TABLE IF NOT EXISTS `doc_states` (
97 `stateid` int(11) NOT NULL auto_increment,
98 `name` varchar(25) NOT NULL default '',
99 PRIMARY KEY (`stateid`)
100 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
102 -- --------------------------------------------------------
105 -- Table structure for table `filemodule_monitor`
108 CREATE TABLE IF NOT EXISTS `filemodule_monitor` (
109 `id` int(11) NOT NULL auto_increment,
110 `filemodule_id` int(11) NOT NULL default '0',
111 `user_id` int(11) NOT NULL default '0',
113 KEY `filemodulemonitor_useridfilemoduleid` (`user_id`,`filemodule_id`)
114 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
116 -- --------------------------------------------------------
119 -- Table structure for table `forum`
122 CREATE TABLE IF NOT EXISTS `forum` (
123 `msg_id` int(11) NOT NULL auto_increment,
124 `group_forum_id` int(11) NOT NULL default '0',
125 `posted_by` int(11) NOT NULL default '0',
126 `subject` varchar(100) NOT NULL default '',
127 `body` text NOT NULL,
128 `post_date` int(11) NOT NULL default '0',
129 `is_followup_to` tinyint(1) NOT NULL default '0',
130 `thread_id` int(11) NOT NULL default '0',
131 `has_followups` int(11) default '0',
132 `most_recent_date` int(11) NOT NULL default '0',
133 PRIMARY KEY (`msg_id`),
134 KEY `group_forum_id` (`group_forum_id`,`msg_id`),
135 KEY `forum_group_forum_id` (`group_forum_id`),
136 KEY `forum_forumid_threadid_mostrecent` (`group_forum_id`,`thread_id`,`most_recent_date`),
137 KEY `forum_threadid_isfollowupto` (`thread_id`,`is_followup_to`),
138 KEY `forum_forumid_isfollto_mostrecent` (`group_forum_id`,`is_followup_to`,`most_recent_date`)
139 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
141 -- --------------------------------------------------------
144 -- Table structure for table `forum_agg_msg_count`
147 CREATE TABLE IF NOT EXISTS `forum_agg_msg_count` (
148 `group_forum_id` int(11) NOT NULL default '0',
149 `count` int(11) NOT NULL default '0'
150 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
152 -- --------------------------------------------------------
155 -- Table structure for table `forum_group_list`
158 CREATE TABLE IF NOT EXISTS `forum_group_list` (
159 `group_forum_id` int(11) NOT NULL auto_increment,
160 `group_id` int(11) NOT NULL default '0',
161 `forum_name` varchar(25) NOT NULL default '',
162 `is_public` tinyint(1) NOT NULL default '0',
163 `description` varchar(255) default NULL,
164 `allow_anonymous` int(11) NOT NULL default '0',
165 `send_all_posts_to` varchar(25) default NULL,
166 `moderation_level` int(11) default '0',
167 PRIMARY KEY (`group_forum_id`),
168 KEY `forum_group_list_group_id` (`group_id`)
169 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
171 -- --------------------------------------------------------
174 -- Table structure for table `forum_monitored_forums`
177 CREATE TABLE IF NOT EXISTS `forum_monitored_forums` (
178 `monitor_id` int(11) NOT NULL auto_increment,
179 `forum_id` int(11) NOT NULL default '0',
180 `user_id` int(11) NOT NULL default '0',
181 PRIMARY KEY (`monitor_id`),
182 KEY `forummonitoredforums_useridforumid` (`user_id`,`forum_id`)
183 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
185 -- --------------------------------------------------------
188 -- Table structure for table `forum_saved_place`
191 CREATE TABLE IF NOT EXISTS `forum_saved_place` (
192 `saved_place_id` int(11) NOT NULL auto_increment,
193 `user_id` int(11) NOT NULL default '0',
194 `forum_id` int(11) NOT NULL default '0',
195 `save_date` int(11) NOT NULL default '0',
196 PRIMARY KEY (`saved_place_id`)
197 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
199 -- --------------------------------------------------------
202 -- Table structure for table `frs_file`
205 CREATE TABLE IF NOT EXISTS `frs_file` (
206 `file_id` int(11) NOT NULL auto_increment,
207 `filename` varchar(25) default NULL,
208 `release_id` int(11) NOT NULL default '0',
209 `type_id` int(11) NOT NULL default '0',
210 `processor_id` int(11) NOT NULL default '0',
211 `release_time` int(11) NOT NULL default '0',
212 `file_size` int(11) NOT NULL default '0',
213 `post_date` int(11) NOT NULL default '0',
214 PRIMARY KEY (`file_id`),
215 KEY `frs_file_date` (`post_date`),
216 KEY `frs_file_release_id` (`release_id`)
217 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
219 -- --------------------------------------------------------
222 -- Table structure for table `frs_filetype`
225 CREATE TABLE IF NOT EXISTS `frs_filetype` (
226 `type_id` int(11) NOT NULL auto_increment,
227 `name` varchar(25) default NULL,
228 PRIMARY KEY (`type_id`)
229 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
231 -- --------------------------------------------------------
234 -- Table structure for table `frs_package`
237 CREATE TABLE IF NOT EXISTS `frs_package` (
238 `package_id` int(11) NOT NULL auto_increment,
239 `group_id` int(11) NOT NULL default '0',
240 `name` varchar(25) default NULL,
241 `status_id` int(11) NOT NULL default '0',
242 `is_public` tinyint(1) default '1',
243 PRIMARY KEY (`package_id`),
244 KEY `package_group_id` (`group_id`)
245 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
247 -- --------------------------------------------------------
250 -- Table structure for table `frs_processor`
253 CREATE TABLE IF NOT EXISTS `frs_processor` (
254 `processor_id` int(11) NOT NULL auto_increment,
255 `name` varchar(25) default NULL,
256 PRIMARY KEY (`processor_id`)
257 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
259 -- --------------------------------------------------------
262 -- Table structure for table `frs_release`
265 CREATE TABLE IF NOT EXISTS `frs_release` (
266 `release_id` int(11) NOT NULL auto_increment,
267 `package_id` int(11) NOT NULL default '0',
268 `name` varchar(25) default NULL,
269 `notes` varchar(255) default NULL,
270 `changes` varchar(255) default NULL,
271 `status_id` int(11) NOT NULL default '0',
272 `preformatted` int(11) NOT NULL default '0',
273 `release_date` int(11) NOT NULL default '0',
274 `released_by` int(11) NOT NULL default '0',
275 PRIMARY KEY (`release_id`),
276 KEY `frs_release_package` (`package_id`)
277 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
279 -- --------------------------------------------------------
282 -- Table structure for table `frs_status`
285 CREATE TABLE IF NOT EXISTS `frs_status` (
286 `status_id` int(11) NOT NULL auto_increment,
287 `name` varchar(25) default NULL,
288 PRIMARY KEY (`status_id`)
289 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
291 -- --------------------------------------------------------
294 -- Table structure for table `group_history`
297 CREATE TABLE IF NOT EXISTS `group_history` (
298 `group_history_id` int(11) NOT NULL auto_increment,
299 `group_id` int(11) NOT NULL default '0',
300 `field_name` varchar(25) NOT NULL default '',
301 `old_value` varchar(100) NOT NULL default '',
302 `mod_by` int(11) NOT NULL default '0',
303 `adddate` int(11) default NULL,
304 PRIMARY KEY (`group_history_id`),
305 KEY `group_history_group_id` (`group_id`)
306 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
308 -- --------------------------------------------------------
311 -- Table structure for table `groups`
314 CREATE TABLE IF NOT EXISTS `groups` (
315 `group_id` int(11) NOT NULL auto_increment,
316 `group_name` varchar(40) default NULL,
317 `homepage` varchar(128) default NULL,
318 `is_public` tinyint(1) NOT NULL default '0',
319 `status` char(1) NOT NULL default '',
320 `unix_group_name` varchar(30) NOT NULL default '',
321 `unix_box` varchar(20) NOT NULL default 'shell1',
322 `http_domain` varchar(80) default NULL,
323 `short_description` varchar(255) default NULL,
324 `register_purpose` varchar(100) default NULL,
325 `license_other` varchar(25) default NULL,
326 `register_time` int(11) NOT NULL default '0',
327 `rand_hash` varchar(32) default NULL,
328 `use_mail` tinyint(1) NOT NULL default '1',
329 `use_survey` tinyint(1) NOT NULL default '1',
330 `use_forum` tinyint(1) NOT NULL default '1',
331 `use_pm` tinyint(1) NOT NULL default '1',
332 `use_scm` tinyint(1) NOT NULL default '1',
333 `use_news` tinyint(1) NOT NULL default '1',
334 `type_id` int(11) NOT NULL default '1',
335 `use_docman` tinyint(1) NOT NULL default '1',
336 `new_doc_address` varchar(100) NOT NULL default '',
337 `send_all_docs` tinyint(1) NOT NULL default '0',
338 `use_pm_depend_box` tinyint(1) NOT NULL default '1',
339 `use_ftp` tinyint(1) default '1',
340 `use_tracker` tinyint(1) default '1',
341 `use_frs` tinyint(1) default '1',
342 `use_stats` tinyint(1) default '1',
343 `enable_pserver` tinyint(1) default '1',
344 `enable_anonscm` tinyint(1) default '1',
345 `license` int(11) default '100',
346 `scm_box` varchar(80) default NULL,
347 PRIMARY KEY (`group_id`),
348 UNIQUE KEY `group_unix_uniq` (`unix_group_name`),
349 KEY `groups_type` (`type_id`),
350 KEY `groups_public` (`is_public`),
351 KEY `groups_status` (`status`)
352 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
354 -- --------------------------------------------------------
357 -- Table structure for table `mail_group_list`
360 CREATE TABLE IF NOT EXISTS `mail_group_list` (
361 `group_list_id` int(11) NOT NULL auto_increment,
362 `group_id` int(11) NOT NULL default '0',
363 `list_name` varchar(25) default NULL,
364 `is_public` tinyint(1) NOT NULL default '0',
365 `password` varchar(16) default NULL,
366 `list_admin` int(11) NOT NULL default '0',
367 `status` int(11) NOT NULL default '0',
368 `description` varchar(255) default NULL,
369 PRIMARY KEY (`group_list_id`),
370 KEY `mail_group_list_group` (`group_id`)
371 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
373 -- --------------------------------------------------------
376 -- Table structure for table `news_bytes`
379 CREATE TABLE IF NOT EXISTS `news_bytes` (
380 `id` int(11) NOT NULL auto_increment,
381 `group_id` int(11) NOT NULL default '0',
382 `submitted_by` int(11) NOT NULL default '0',
383 `is_approved` tinyint(1) NOT NULL default '0',
384 `post_date` int(11) NOT NULL default '0',
385 `forum_id` int(11) NOT NULL default '0',
389 KEY `news_bytes_group` (`group_id`),
390 KEY `news_bytes_approved` (`is_approved`),
391 KEY `news_bytes_forum` (`forum_id`),
392 KEY `news_group_date` (`group_id`,`post_date`),
393 KEY `news_approved_date` (`is_approved`,`post_date`)
394 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
396 -- --------------------------------------------------------
399 -- Table structure for table `people_job`
402 CREATE TABLE IF NOT EXISTS `people_job` (
403 `job_id` int(11) NOT NULL auto_increment,
404 `group_id` int(11) NOT NULL default '0',
405 `created_by` int(11) NOT NULL default '0',
406 `title` varchar(25) default NULL,
407 `description` varchar(255) default NULL,
408 `post_date` int(11) NOT NULL default '0',
409 `status_id` int(11) NOT NULL default '0',
410 `category_id` int(11) NOT NULL default '0',
411 PRIMARY KEY (`job_id`),
412 KEY `people_job_group_id` (`group_id`)
413 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
415 -- --------------------------------------------------------
418 -- Table structure for table `people_job_category`
421 CREATE TABLE IF NOT EXISTS `people_job_category` (
422 `category_id` int(11) NOT NULL auto_increment,
423 `name` varchar(25) default NULL,
424 `private_flag` int(11) NOT NULL default '0',
425 PRIMARY KEY (`category_id`)
426 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
428 -- --------------------------------------------------------
431 -- Table structure for table `people_job_inventory`
434 CREATE TABLE IF NOT EXISTS `people_job_inventory` (
435 `job_inventory_id` int(11) NOT NULL auto_increment,
436 `job_id` int(11) NOT NULL default '0',
437 `skill_id` int(11) NOT NULL default '0',
438 `skill_level_id` int(11) NOT NULL default '0',
439 `skill_year_id` int(11) NOT NULL default '0',
440 PRIMARY KEY (`job_inventory_id`)
441 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
443 -- --------------------------------------------------------
446 -- Table structure for table `people_job_status`
449 CREATE TABLE IF NOT EXISTS `people_job_status` (
450 `status_id` int(11) NOT NULL auto_increment,
451 `name` varchar(25) default NULL,
452 PRIMARY KEY (`status_id`)
453 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
455 -- --------------------------------------------------------
458 -- Table structure for table `people_skill`
461 CREATE TABLE IF NOT EXISTS `people_skill` (
462 `skill_id` int(11) NOT NULL auto_increment,
463 `name` varchar(25) default NULL,
464 PRIMARY KEY (`skill_id`)
465 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
467 -- --------------------------------------------------------
470 -- Table structure for table `people_skill_inventory`
473 CREATE TABLE IF NOT EXISTS `people_skill_inventory` (
474 `skill_inventory_id` int(11) NOT NULL auto_increment,
475 `user_id` int(11) NOT NULL default '0',
476 `skill_id` int(11) NOT NULL default '0',
477 `skill_level_id` int(11) NOT NULL default '0',
478 `skill_year_id` int(11) NOT NULL default '0',
479 PRIMARY KEY (`skill_inventory_id`)
480 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
482 -- --------------------------------------------------------
485 -- Table structure for table `people_skill_level`
488 CREATE TABLE IF NOT EXISTS `people_skill_level` (
489 `skill_level_id` int(11) NOT NULL auto_increment,
490 `name` varchar(25) default NULL,
491 PRIMARY KEY (`skill_level_id`)
492 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
494 -- --------------------------------------------------------
497 -- Table structure for table `people_skill_year`
500 CREATE TABLE IF NOT EXISTS `people_skill_year` (
501 `skill_year_id` int(11) NOT NULL auto_increment,
502 `name` varchar(25) default NULL,
503 PRIMARY KEY (`skill_year_id`)
504 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
506 -- --------------------------------------------------------
509 -- Table structure for table `project_assigned_to`
512 CREATE TABLE IF NOT EXISTS `project_assigned_to` (
513 `project_assigned_id` int(11) NOT NULL auto_increment,
514 `project_task_id` int(11) NOT NULL default '0',
515 `assigned_to_id` int(11) NOT NULL default '0',
516 PRIMARY KEY (`project_assigned_id`),
517 KEY `projectassigned_assignedtotaskid` (`assigned_to_id`,`project_task_id`)
518 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
520 -- --------------------------------------------------------
523 -- Table structure for table `project_dependencies`
526 CREATE TABLE IF NOT EXISTS `project_dependencies` (
527 `project_depend_id` int(11) NOT NULL auto_increment,
528 `project_task_id` int(11) NOT NULL default '0',
529 `is_dependent_on_task_id` int(11) NOT NULL default '0',
530 `link_type` char(2) default NULL,
531 PRIMARY KEY (`project_depend_id`),
532 KEY `projectdep_isdepon_projtaskid` (`is_dependent_on_task_id`,`project_task_id`)
533 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
535 -- --------------------------------------------------------
538 -- Table structure for table `project_group_list`
541 CREATE TABLE IF NOT EXISTS `project_group_list` (
542 `group_project_id` int(11) NOT NULL auto_increment,
543 `group_id` int(11) NOT NULL default '0',
544 `project_name` varchar(25) NOT NULL default '',
545 `is_public` tinyint(1) NOT NULL default '0',
546 `description` varchar(255) default NULL,
547 `send_all_posts_to` varchar(25) default NULL,
548 PRIMARY KEY (`group_project_id`),
549 KEY `project_group_list_group_id` (`group_id`)
550 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
552 -- --------------------------------------------------------
555 -- Table structure for table `project_history`
558 CREATE TABLE IF NOT EXISTS `project_history` (
559 `project_history_id` int(11) NOT NULL auto_increment,
560 `project_task_id` int(11) NOT NULL default '0',
561 `field_name` varchar(25) NOT NULL default '',
562 `old_value` varchar(25) NOT NULL default '',
563 `mod_by` int(11) NOT NULL default '0',
564 `mod_date` int(11) NOT NULL default '0',
565 PRIMARY KEY (`project_history_id`),
566 KEY `project_history_task_id` (`project_task_id`)
567 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
569 -- --------------------------------------------------------
572 -- Table structure for table `project_metric`
575 CREATE TABLE IF NOT EXISTS `project_metric` (
576 `ranking` int(11) NOT NULL auto_increment,
577 `percentile` double default NULL,
578 `group_id` int(11) NOT NULL default '0',
579 PRIMARY KEY (`ranking`),
580 KEY `project_metric_group` (`group_id`)
581 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
583 -- --------------------------------------------------------
586 -- Table structure for table `project_metric_tmp1`
589 CREATE TABLE IF NOT EXISTS `project_metric_tmp1` (
590 `ranking` int(11) NOT NULL auto_increment,
591 `group_id` int(11) NOT NULL default '0',
592 `value` double default NULL,
593 PRIMARY KEY (`ranking`)
594 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
596 -- --------------------------------------------------------
599 -- Table structure for table `project_status`
602 CREATE TABLE IF NOT EXISTS `project_status` (
603 `status_id` int(11) NOT NULL auto_increment,
604 `status_name` varchar(25) NOT NULL default '',
605 PRIMARY KEY (`status_id`)
606 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
608 -- --------------------------------------------------------
611 -- Table structure for table `project_task`
614 CREATE TABLE IF NOT EXISTS `project_task` (
615 `project_task_id` int(11) NOT NULL auto_increment,
616 `group_project_id` int(11) NOT NULL default '0',
617 `summary` text NOT NULL,
618 `details` text NOT NULL,
619 `percent_complete` int(11) NOT NULL default '0',
620 `priority` int(11) NOT NULL default '3',
621 `hours` double NOT NULL default '0',
622 `start_date` int(11) NOT NULL default '0',
623 `end_date` int(11) NOT NULL default '0',
624 `created_by` int(11) NOT NULL default '0',
625 `status_id` int(11) NOT NULL default '0',
626 `category_id` int(11) default NULL,
627 `duration` int(11) default '0',
628 `parent_id` int(11) default '0',
629 `last_modified_date` int(11) default NULL,
630 PRIMARY KEY (`project_task_id`),
631 KEY `projecttask_projid_status` (`group_project_id`,`status_id`)
632 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
634 -- --------------------------------------------------------
637 -- Table structure for table `project_weekly_metric`
640 CREATE TABLE IF NOT EXISTS `project_weekly_metric` (
641 `ranking` int(11) NOT NULL auto_increment,
642 `percentile` double default NULL,
643 `group_id` int(11) NOT NULL default '0',
644 PRIMARY KEY (`ranking`),
645 KEY `projectweeklymetric_ranking` (`ranking`),
646 KEY `project_metric_weekly_group` (`group_id`)
647 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
649 -- --------------------------------------------------------
652 -- Table structure for table `user_session`
655 CREATE TABLE IF NOT EXISTS `user_session` (
656 `user_id` int(11) NOT NULL default '0',
657 `session_hash` char(32) NOT NULL default '',
658 `ip_addr` char(15) NOT NULL default '',
659 `time` int(11) NOT NULL default '0',
660 PRIMARY KEY (`user_id`),
661 KEY `session_user_id` (`user_id`),
662 KEY `session_time` (`time`)
663 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
665 -- --------------------------------------------------------
668 -- Table structure for table `snippet`
671 CREATE TABLE IF NOT EXISTS `snippet` (
672 `snippet_id` int(11) NOT NULL auto_increment,
673 `created_by` int(11) NOT NULL default '0',
674 `name` varchar(25) default NULL,
675 `description` varchar(255) default NULL,
676 `type` int(11) NOT NULL default '0',
677 `language` int(11) NOT NULL default '0',
678 `license` varchar(25) NOT NULL default '',
679 `category` int(11) NOT NULL default '0',
680 PRIMARY KEY (`snippet_id`),
681 KEY `snippet_language` (`language`),
682 KEY `snippet_category` (`category`)
683 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
685 -- --------------------------------------------------------
688 -- Table structure for table `snippet_package`
691 CREATE TABLE IF NOT EXISTS `snippet_package` (
692 `snippet_package_id` int(11) NOT NULL auto_increment,
693 `created_by` int(11) NOT NULL default '0',
694 `name` varchar(25) default NULL,
695 `description` varchar(255) default NULL,
696 `category` int(11) NOT NULL default '0',
697 `language` int(11) NOT NULL default '0',
698 PRIMARY KEY (`snippet_package_id`),
699 KEY `snippet_package_language` (`language`),
700 KEY `snippet_package_category` (`category`)
701 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
703 -- --------------------------------------------------------
706 -- Table structure for table `snippet_package_item`
709 CREATE TABLE IF NOT EXISTS `snippet_package_item` (
710 `snippet_package_item_id` int(11) NOT NULL auto_increment,
711 `snippet_package_version_id` int(11) NOT NULL default '0',
712 `snippet_version_id` int(11) NOT NULL default '0',
713 PRIMARY KEY (`snippet_package_item_id`),
714 KEY `snippet_package_item_pkg_ver` (`snippet_package_version_id`)
715 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
717 -- --------------------------------------------------------
720 -- Table structure for table `snippet_package_version`
723 CREATE TABLE IF NOT EXISTS `snippet_package_version` (
724 `snippet_package_version_id` int(11) NOT NULL auto_increment,
725 `snippet_package_id` int(11) NOT NULL default '0',
726 `changes` varchar(255) default NULL,
727 `version` varchar(25) default NULL,
728 `submitted_by` int(11) NOT NULL default '0',
729 `post_date` int(11) NOT NULL default '0',
730 PRIMARY KEY (`snippet_package_version_id`),
731 KEY `snippet_package_version_pkg_id` (`snippet_package_id`)
732 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
734 -- --------------------------------------------------------
737 -- Table structure for table `snippet_version`
740 CREATE TABLE IF NOT EXISTS `snippet_version` (
741 `snippet_version_id` int(11) NOT NULL auto_increment,
742 `snippet_id` int(11) NOT NULL default '0',
743 `changes` varchar(255) default NULL,
744 `version` varchar(25) default NULL,
745 `submitted_by` int(11) NOT NULL default '0',
746 `post_date` int(11) NOT NULL default '0',
748 PRIMARY KEY (`snippet_version_id`),
749 KEY `snippet_version_snippet_id` (`snippet_id`)
750 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
752 -- --------------------------------------------------------
755 -- Table structure for table `stats_agg_logo_by_day`
758 CREATE TABLE IF NOT EXISTS `stats_agg_logo_by_day` (
759 `day` int(11) default NULL,
760 `count` int(11) default NULL
761 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
763 -- --------------------------------------------------------
766 -- Table structure for table `stats_agg_pages_by_day`
769 CREATE TABLE IF NOT EXISTS `stats_agg_pages_by_day` (
770 `day` int(11) NOT NULL default '0',
771 `count` int(11) NOT NULL default '0',
772 KEY `pages_by_day_day` (`day`)
773 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
775 -- --------------------------------------------------------
778 -- Table structure for table `stats_site_pages_by_month`
781 CREATE TABLE IF NOT EXISTS `stats_site_pages_by_month` (
782 `month` int(11) default NULL,
783 `site_page_views` int(11) default NULL
784 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
786 -- --------------------------------------------------------
789 -- Table structure for table `survey_question_types`
792 CREATE TABLE IF NOT EXISTS `survey_question_types` (
793 `id` int(11) NOT NULL auto_increment,
794 `type` varchar(25) NOT NULL default '',
796 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
798 -- --------------------------------------------------------
801 -- Table structure for table `survey_questions`
804 CREATE TABLE IF NOT EXISTS `survey_questions` (
805 `question_id` int(11) NOT NULL auto_increment,
806 `group_id` int(11) NOT NULL default '0',
807 `question` varchar(100) NOT NULL default '',
808 `question_type` int(11) NOT NULL default '0',
809 PRIMARY KEY (`question_id`),
810 KEY `survey_questions_group` (`group_id`)
811 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
813 -- --------------------------------------------------------
816 -- Table structure for table `survey_rating_aggregate`
819 CREATE TABLE IF NOT EXISTS `survey_rating_aggregate` (
820 `type` int(11) NOT NULL default '0',
821 `id` int(11) NOT NULL default '0',
822 `response` double NOT NULL default '0',
823 `count` int(11) NOT NULL default '0',
824 KEY `survey_rating_aggregate_type_id` (`type`,`id`)
825 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
827 -- --------------------------------------------------------
830 -- Table structure for table `survey_rating_response`
833 CREATE TABLE IF NOT EXISTS `survey_rating_response` (
834 `user_id` int(11) NOT NULL default '0',
835 `type` int(11) NOT NULL default '0',
836 `id` int(11) NOT NULL default '0',
837 `response` int(11) NOT NULL default '0',
838 `post_date` int(11) NOT NULL default '0',
839 KEY `survey_rating_responses_user_ty` (`user_id`,`type`,`id`),
840 KEY `survey_rating_responses_type_id` (`type`,`id`)
841 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
843 -- --------------------------------------------------------
846 -- Table structure for table `survey_responses`
849 CREATE TABLE IF NOT EXISTS `survey_responses` (
850 `user_id` int(11) NOT NULL default '0',
851 `group_id` int(11) NOT NULL default '0',
852 `survey_id` int(11) NOT NULL default '0',
853 `question_id` int(11) NOT NULL default '0',
854 `response` varchar(100) NOT NULL default '',
855 `post_date` int(11) NOT NULL default '0',
856 KEY `survey_responses_group_id` (`group_id`),
857 KEY `survey_responses_user_survey_qu` (`user_id`,`survey_id`,`question_id`),
858 KEY `survey_responses_survey_questio` (`survey_id`,`question_id`)
859 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
861 -- --------------------------------------------------------
864 -- Table structure for table `surveys`
867 CREATE TABLE IF NOT EXISTS `surveys` (
868 `survey_id` int(11) NOT NULL auto_increment,
869 `group_id` int(11) NOT NULL default '0',
870 `survey_title` varchar(100) NOT NULL default '',
871 `survey_questions` varchar(100) NOT NULL default '',
872 `is_active` tinyint(1) NOT NULL default '1',
873 PRIMARY KEY (`survey_id`),
874 KEY `surveys_group` (`group_id`)
875 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
877 -- --------------------------------------------------------
880 -- Table structure for table `trove_cat`
883 CREATE TABLE IF NOT EXISTS `trove_cat` (
884 `trove_cat_id` int(11) NOT NULL auto_increment,
885 `version` int(11) NOT NULL default '0',
886 `parent` int(11) NOT NULL default '0',
887 `root_parent` int(11) NOT NULL default '0',
888 `shortname` varchar(80) default NULL,
889 `fullname` varchar(80) default NULL,
890 `description` varchar(255) default NULL,
891 `count_subcat` int(11) NOT NULL default '0',
892 `count_subproj` int(11) NOT NULL default '0',
893 `fullpath` text NOT NULL,
895 PRIMARY KEY (`trove_cat_id`),
896 KEY `trovecat_parentid` (`parent`)
897 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
899 -- --------------------------------------------------------
902 -- Table structure for table `trove_group_link`
905 CREATE TABLE IF NOT EXISTS `trove_group_link` (
906 `trove_group_id` int(11) NOT NULL auto_increment,
907 `trove_cat_id` int(11) NOT NULL default '0',
908 `trove_cat_version` int(11) NOT NULL default '0',
909 `group_id` int(11) NOT NULL default '0',
910 `trove_cat_root` int(11) NOT NULL default '0',
911 PRIMARY KEY (`trove_group_id`),
912 KEY `trovegrouplink_groupidcatid` (`group_id`,`trove_cat_id`)
913 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
915 -- --------------------------------------------------------
918 -- Table structure for table `user_bookmarks`
921 CREATE TABLE IF NOT EXISTS `user_bookmarks` (
922 `bookmark_id` int(11) NOT NULL auto_increment,
923 `user_id` int(11) NOT NULL default '0',
924 `bookmark_url` varchar(100) default NULL,
925 `bookmark_title` varchar(25) default NULL,
926 PRIMARY KEY (`bookmark_id`),
927 KEY `user_bookmark_user_id` (`user_id`)
928 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
930 -- --------------------------------------------------------
933 -- Table structure for table `user_diary`
936 CREATE TABLE IF NOT EXISTS `user_diary` (
937 `id` int(11) NOT NULL auto_increment,
938 `user_id` int(11) NOT NULL default '0',
939 `date_posted` int(11) NOT NULL default '0',
942 `is_public` tinyint(1) NOT NULL default '0',
944 KEY `user_diary_user_date` (`user_id`,`date_posted`)
945 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
947 -- --------------------------------------------------------
950 -- Table structure for table `user_diary_monitor`
953 CREATE TABLE IF NOT EXISTS `user_diary_monitor` (
954 `monitor_id` int(11) NOT NULL auto_increment,
955 `monitored_user` int(11) NOT NULL default '0',
956 `user_id` int(11) NOT NULL default '0',
957 PRIMARY KEY (`monitor_id`),
958 KEY `userdiarymon_useridmonitoredid` (`user_id`,`monitored_user`)
959 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
961 -- --------------------------------------------------------
964 -- Table structure for table `user_group`
967 CREATE TABLE IF NOT EXISTS `user_group` (
968 `user_group_id` int(11) NOT NULL auto_increment,
969 `user_id` int(11) NOT NULL default '0',
970 `group_id` int(11) NOT NULL default '0',
971 `admin_flags` char(16) NOT NULL default '',
972 `forum_flags` int(11) NOT NULL default '0',
973 `project_flags` int(11) NOT NULL default '2',
974 `doc_flags` int(11) NOT NULL default '0',
975 `cvs_flags` int(11) NOT NULL default '1',
976 `member_role` int(11) NOT NULL default '100',
977 `release_flags` int(11) NOT NULL default '0',
978 `artifact_flags` int(11) default NULL,
979 `role_id` int(11) default '1',
980 PRIMARY KEY (`user_group_id`),
981 KEY `usergroup_useridgroupid` (`user_id`,`group_id`)
982 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
984 -- --------------------------------------------------------
987 -- Table structure for table `user_metric`
990 CREATE TABLE IF NOT EXISTS `user_metric` (
991 `ranking` int(11) NOT NULL auto_increment,
992 `user_id` int(11) NOT NULL default '0',
993 `times_ranked` int(11) NOT NULL default '0',
994 `avg_raters_importance` double NOT NULL default '0',
995 `avg_rating` double NOT NULL default '0',
996 `metric` double NOT NULL default '0',
997 `percentile` double NOT NULL default '0',
998 `importance_factor` double NOT NULL default '0',
999 PRIMARY KEY (`ranking`),
1000 UNIQUE KEY `usermetric_userid` (`user_id`)
1001 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1003 -- --------------------------------------------------------
1006 -- Table structure for table `user_metric0`
1009 CREATE TABLE IF NOT EXISTS `user_metric0` (
1010 `ranking` int(11) NOT NULL auto_increment,
1011 `user_id` int(11) NOT NULL default '0',
1012 `times_ranked` int(11) NOT NULL default '0',
1013 `avg_raters_importance` double NOT NULL default '0',
1014 `avg_rating` double NOT NULL default '0',
1015 `metric` double NOT NULL default '0',
1016 `percentile` double NOT NULL default '0',
1017 `importance_factor` double NOT NULL default '0',
1018 PRIMARY KEY (`ranking`),
1019 KEY `user_metric0_user_id` (`user_id`)
1020 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1022 -- --------------------------------------------------------
1025 -- Table structure for table `user_preferences`
1028 CREATE TABLE IF NOT EXISTS `user_preferences` (
1029 `user_id` int(11) NOT NULL default '0',
1030 `preference_name` varchar(20) NOT NULL default '',
1031 `dead1` varchar(20) default NULL,
1032 `set_date` int(11) NOT NULL default '0',
1033 `preference_value` varchar(255) default NULL,
1034 PRIMARY KEY (`user_id`)
1035 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1037 -- --------------------------------------------------------
1040 -- Table structure for table `user_ratings`
1043 CREATE TABLE IF NOT EXISTS `user_ratings` (
1044 `rated_by` int(11) NOT NULL default '0',
1045 `user_id` int(11) NOT NULL default '0',
1046 `rate_field` int(11) NOT NULL default '0',
1047 `rating` int(11) NOT NULL default '0',
1048 KEY `user_ratings_user_id` (`user_id`)
1049 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1051 -- --------------------------------------------------------
1054 -- Table structure for table `users`
1057 CREATE TABLE IF NOT EXISTS `users` (
1058 `user_id` int(11) NOT NULL auto_increment,
1059 `user_name` varchar(25) NOT NULL default '',
1060 `email` text NOT NULL,
1061 `user_pw` varchar(32) NOT NULL default '',
1062 `realname` varchar(32) NOT NULL default '',
1063 `status` char(1) NOT NULL default 'A',
1064 `shell` varchar(20) NOT NULL default '/bin/bash',
1065 `unix_pw` varchar(40) NOT NULL default '',
1066 `unix_status` char(1) NOT NULL default 'N',
1067 `unix_uid` int(11) NOT NULL default '0',
1068 `unix_box` varchar(10) NOT NULL default 'shell1',
1069 `add_date` int(11) NOT NULL default '0',
1070 `confirm_hash` varchar(32) default NULL,
1071 `mail_siteupdates` int(11) NOT NULL default '0',
1072 `mail_va` int(11) NOT NULL default '0',
1073 `authorized_keys` varchar(100) default NULL,
1074 `email_new` varchar(25) default NULL,
1075 `people_view_skills` int(11) NOT NULL default '0',
1076 `people_resume` varchar(255) NOT NULL default '',
1077 `timezone` varchar(64) default 'GMT',
1078 `language` int(11) NOT NULL default '1',
1079 `block_ratings` int(11) default '0',
1080 `jabber_address` varchar(100) default NULL,
1081 `jabber_only` int(11) default NULL,
1082 `address` varchar(100) default NULL,
1083 `phone` varchar(25) default NULL,
1084 `fax` varchar(25) default NULL,
1085 `title` varchar(25) default NULL,
1086 `firstname` varchar(60) default NULL,
1087 `lastname` varchar(60) default NULL,
1088 `address2` varchar(100) default NULL,
1089 `ccode` char(2) default 'US',
1090 `theme_id` int(11) default NULL,
1091 `type_id` int(11) default '1',
1092 `unix_gid` int(11) default '0',
1093 PRIMARY KEY (`user_id`),
1094 UNIQUE KEY `users_namename_uniq` (`user_name`),
1095 KEY `users_status` (`status`)
1096 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1098 -- --------------------------------------------------------
1101 -- Table structure for table `project_sums_agg`
1104 CREATE TABLE IF NOT EXISTS `project_sums_agg` (
1105 `group_id` int(11) NOT NULL default '0',
1106 `type` char(4) NOT NULL default '',
1107 `count` int(11) NOT NULL default '0'
1108 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1110 -- --------------------------------------------------------
1113 -- Table structure for table `prdb_dbs`
1116 CREATE TABLE IF NOT EXISTS `prdb_dbs` (
1117 `dbid` int(11) NOT NULL auto_increment,
1118 `group_id` int(11) NOT NULL default '0',
1119 `dbname` varchar(255) NOT NULL default '',
1120 `dbusername` varchar(25) NOT NULL default '',
1121 `dbuserpass` varchar(32) NOT NULL default '',
1122 `requestdate` int(11) NOT NULL default '0',
1123 `dbtype` int(11) NOT NULL default '0',
1124 `created_by` int(11) NOT NULL default '0',
1125 `state` int(11) NOT NULL default '0',
1126 PRIMARY KEY (`dbid`),
1127 UNIQUE KEY `idx_prdb_dbname` (`dbname`),
1128 KEY `prdbdbs_groupid` (`group_id`)
1129 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1131 -- --------------------------------------------------------
1134 -- Table structure for table `prdb_states`
1137 CREATE TABLE IF NOT EXISTS `prdb_states` (
1138 `stateid` int(11) NOT NULL default '0',
1140 KEY `prdbstates_stateid` (`stateid`)
1141 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1143 -- --------------------------------------------------------
1146 -- Table structure for table `prdb_types`
1149 CREATE TABLE IF NOT EXISTS `prdb_types` (
1150 `dbtypeid` int(11) NOT NULL default '0',
1151 `dbservername` varchar(25) NOT NULL default '',
1152 `dbsoftware` varchar(25) NOT NULL default ''
1153 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1155 -- --------------------------------------------------------
1158 -- Table structure for table `prweb_vhost`
1161 CREATE TABLE IF NOT EXISTS `prweb_vhost` (
1162 `vhostid` int(11) NOT NULL auto_increment,
1163 `vhost_name` varchar(255) default NULL,
1164 `docdir` varchar(255) default NULL,
1165 `cgidir` varchar(255) default NULL,
1166 `group_id` int(11) NOT NULL default '0',
1167 PRIMARY KEY (`vhostid`),
1168 UNIQUE KEY `idx_vhost_hostnames` (`vhost_name`),
1169 KEY `idx_vhost_groups` (`group_id`)
1170 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1172 -- --------------------------------------------------------
1175 -- Table structure for table `artifact_group_list`
1178 CREATE TABLE IF NOT EXISTS `artifact_group_list` (
1179 `group_artifact_id` int(11) NOT NULL auto_increment,
1180 `group_id` int(11) NOT NULL default '0',
1183 `is_public` tinyint(1) NOT NULL default '0',
1184 `allow_anon` tinyint(1) NOT NULL default '0',
1185 `email_all_updates` tinyint(1) NOT NULL default '0',
1186 `email_address` text NOT NULL,
1187 `due_period` int(11) NOT NULL default '2592000',
1188 `submit_instructions` text,
1189 `browse_instructions` text,
1190 `datatype` int(11) NOT NULL default '0',
1191 `status_timeout` int(11) default NULL,
1192 `custom_status_field` int(11) NOT NULL default '0',
1193 `custom_renderer` text,
1194 PRIMARY KEY (`group_artifact_id`),
1195 KEY `artgrouplist_groupid_public` (`group_id`,`is_public`)
1196 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1198 -- --------------------------------------------------------
1201 -- Table structure for table `artifact_perm`
1204 CREATE TABLE IF NOT EXISTS `artifact_perm` (
1205 `id` int(11) NOT NULL auto_increment,
1206 `group_artifact_id` int(11) NOT NULL default '0',
1207 `user_id` int(11) NOT NULL default '0',
1208 `perm_level` int(11) NOT NULL default '0',
1210 UNIQUE KEY `artperm_groupartifactid_userid` (`group_artifact_id`,`user_id`)
1211 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1214 CREATE OR REPLACE VIEW `artifactperm_user_vw` AS
1215 SELECT ap.id, ap.group_artifact_id, ap.user_id, ap.perm_level, users.user_name, users.realname
1216 FROM artifact_perm AS ap, users
1217 WHERE users.user_id = ap.user_id;
1220 CREATE OR REPLACE VIEW `artifactperm_artgrouplist_vw` AS
1221 SELECT agl.group_artifact_id, agl.name, agl.description, agl.group_id, ap.user_id, ap.perm_level
1222 FROM artifact_perm AS ap, artifact_group_list AS agl
1223 WHERE ap.group_artifact_id = agl.group_artifact_id;
1226 -- --------------------------------------------------------
1229 -- Table structure for table `artifact_status`
1232 CREATE TABLE IF NOT EXISTS `artifact_status` (
1233 `id` int(11) NOT NULL auto_increment,
1234 `status_name` varchar(10) NOT NULL default '',
1236 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1238 -- --------------------------------------------------------
1241 -- Table structure for table `artifact`
1244 CREATE TABLE IF NOT EXISTS `artifact` (
1245 `artifact_id` int(11) NOT NULL auto_increment,
1246 `group_artifact_id` int(11) NOT NULL default '0',
1247 `status_id` int(11) NOT NULL default '1',
1248 `priority` int(11) NOT NULL default '3',
1249 `submitted_by` int(11) NOT NULL default '100',
1250 `assigned_to` int(11) NOT NULL default '100',
1251 `open_date` int(11) NOT NULL default '0',
1252 `close_date` int(11) NOT NULL default '0',
1253 `summary` text NOT NULL,
1254 `details` text NOT NULL,
1255 `last_modified_date` int(11) default NULL,
1256 PRIMARY KEY (`artifact_id`),
1257 KEY `art_groupartid` (`group_artifact_id`),
1258 KEY `art_groupartid_statusid` (`group_artifact_id`,`status_id`),
1259 KEY `art_groupartid_assign` (`group_artifact_id`,`assigned_to`),
1260 KEY `art_groupartid_submit` (`group_artifact_id`,`submitted_by`),
1261 KEY `art_submit_status` (`submitted_by`,`status_id`),
1262 KEY `art_assign_status` (`assigned_to`,`status_id`),
1263 KEY `art_groupartid_artifactid` (`group_artifact_id`,`artifact_id`)
1264 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1266 -- --------------------------------------------------------
1269 -- Table structure for table `artifact_history`
1272 CREATE TABLE IF NOT EXISTS `artifact_history` (
1273 `id` int(11) NOT NULL auto_increment,
1274 `artifact_id` int(11) NOT NULL default '0',
1275 `field_name` text NOT NULL,
1276 `old_value` text NOT NULL,
1277 `mod_by` int(11) NOT NULL default '0',
1278 `entrydate` int(11) NOT NULL default '0',
1280 KEY `arthistory_artid_entrydate` (`artifact_id`,`entrydate`)
1281 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1284 CREATE OR REPLACE VIEW `artifact_history_user_vw` AS
1285 SELECT ah.id, ah.artifact_id, ah.field_name, ah.old_value, ah.entrydate, users.user_name
1286 FROM artifact_history AS ah, users
1287 WHERE ah.mod_by = users.user_id;
1290 -- --------------------------------------------------------
1293 -- Table structure for table `artifact_file`
1296 CREATE TABLE IF NOT EXISTS `artifact_file` (
1297 `id` int(11) NOT NULL auto_increment,
1298 `artifact_id` int(11) NOT NULL default '0',
1299 `description` text NOT NULL,
1300 `bin_data` mediumblob NOT NULL,
1301 `filename` text NOT NULL,
1302 `filesize` int(11) NOT NULL default '0',
1303 `filetype` text NOT NULL,
1304 `adddate` int(11) NOT NULL default '0',
1305 `submitted_by` int(11) NOT NULL default '0',
1307 KEY `artfile_artid_adddate` (`artifact_id`,`adddate`)
1308 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1311 CREATE OR REPLACE VIEW `artifact_file_user_vw` AS
1312 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
1313 FROM artifact_file AS af, users
1314 WHERE af.submitted_by = users.user_id;
1317 -- --------------------------------------------------------
1320 -- Table structure for table `artifact_message`
1323 CREATE TABLE IF NOT EXISTS `artifact_message` (
1324 `id` int(11) NOT NULL auto_increment,
1325 `artifact_id` int(11) NOT NULL default '0',
1326 `submitted_by` int(11) NOT NULL default '0',
1327 `from_email` text NOT NULL,
1328 `adddate` int(11) NOT NULL default '0',
1329 `body` text NOT NULL,
1331 KEY `artmessage_artid_adddate` (`artifact_id`,`adddate`)
1332 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1335 CREATE OR REPLACE VIEW `artifact_message_user_vw` AS
1336 SELECT am.id, am.artifact_id, am.from_email, am.body, am.adddate, users.user_id, users.email, users.user_name, users.realname
1337 FROM artifact_message AS am, users
1338 WHERE am.submitted_by = users.user_id;
1341 -- --------------------------------------------------------
1344 -- Table structure for table `artifact_monitor`
1347 CREATE TABLE IF NOT EXISTS `artifact_monitor` (
1348 `id` int(11) NOT NULL auto_increment,
1349 `artifact_id` int(11) NOT NULL default '0',
1350 `user_id` int(11) NOT NULL default '0',
1353 KEY `artmonitor_useridartid` (`user_id`,`artifact_id`)
1354 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1356 -- --------------------------------------------------------
1359 -- Table structure for table `artifact_canned_responses`
1362 CREATE TABLE IF NOT EXISTS `artifact_canned_responses` (
1363 `id` int(11) NOT NULL auto_increment,
1364 `group_artifact_id` int(11) NOT NULL default '0',
1365 `title` text NOT NULL,
1366 `body` text NOT NULL,
1368 KEY `artifactcannedresponses_groupid` (`group_artifact_id`)
1369 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1371 -- --------------------------------------------------------
1374 -- Table structure for table `artifact_counts_agg`
1377 CREATE TABLE IF NOT EXISTS `artifact_counts_agg` (
1378 `group_artifact_id` int(11) NOT NULL default '0',
1379 `count` int(11) NOT NULL default '0',
1380 `open_count` int(11) default '0'
1381 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1383 -- --------------------------------------------------------
1386 -- Table structure for table `stats_site_pages_by_day`
1389 CREATE TABLE IF NOT EXISTS `stats_site_pages_by_day` (
1390 `month` int(11) default NULL,
1391 `day` int(11) default NULL,
1392 `site_page_views` int(11) default NULL,
1393 KEY `statssitepagesbyday_month_day` (`month`,`day`)
1394 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1396 -- --------------------------------------------------------
1399 -- Table structure for table `massmail_queue`
1402 CREATE TABLE IF NOT EXISTS `massmail_queue` (
1403 `id` int(11) NOT NULL auto_increment,
1404 `type` varchar(8) NOT NULL default '',
1405 `subject` varchar(100) NOT NULL default '',
1406 `message` varchar(255) NOT NULL default '',
1407 `queued_date` int(11) NOT NULL default '0',
1408 `last_userid` int(11) NOT NULL default '0',
1409 `failed_date` int(11) NOT NULL default '0',
1410 `finished_date` int(11) NOT NULL default '0',
1412 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1414 -- --------------------------------------------------------
1417 -- Table structure for table `stats_agg_site_by_group`
1420 CREATE TABLE IF NOT EXISTS `stats_agg_site_by_group` (
1421 `month` int(11) default NULL,
1422 `day` int(11) default NULL,
1423 `group_id` int(11) default NULL,
1424 `count` int(11) default NULL,
1425 UNIQUE KEY `statssitebygroup_month_day_group` (`month`,`day`,`group_id`)
1426 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1428 -- --------------------------------------------------------
1431 -- Table structure for table `stats_project_metric`
1434 CREATE TABLE IF NOT EXISTS `stats_project_metric` (
1435 `month` int(11) NOT NULL default '0',
1436 `day` int(11) NOT NULL default '0',
1437 `ranking` int(11) NOT NULL default '0',
1438 `percentile` double NOT NULL default '0',
1439 `group_id` int(11) NOT NULL default '0',
1440 UNIQUE KEY `statsprojectmetric_month_day_group` (`month`,`day`,`group_id`)
1441 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1443 -- --------------------------------------------------------
1446 -- Table structure for table `stats_agg_logo_by_group`
1449 CREATE TABLE IF NOT EXISTS `stats_agg_logo_by_group` (
1450 `month` int(11) default NULL,
1451 `day` int(11) default NULL,
1452 `group_id` int(11) default NULL,
1453 `count` int(11) default NULL,
1454 UNIQUE KEY `statslogobygroup_month_day_grou` (`month`,`day`,`group_id`)
1455 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1457 -- --------------------------------------------------------
1460 -- Table structure for table `stats_subd_pages`
1463 CREATE TABLE IF NOT EXISTS `stats_subd_pages` (
1464 `month` int(11) NOT NULL default '0',
1465 `day` int(11) NOT NULL default '0',
1466 `group_id` int(11) NOT NULL default '0',
1467 `pages` int(11) NOT NULL default '0',
1468 UNIQUE KEY `statssubdpages_month_day_group` (`month`,`day`,`group_id`)
1469 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1471 -- --------------------------------------------------------
1474 -- Table structure for table `stats_cvs_user`
1477 CREATE TABLE IF NOT EXISTS `stats_cvs_user` (
1478 `month` int(11) NOT NULL default '0',
1479 `day` int(11) NOT NULL default '0',
1480 `group_id` int(11) NOT NULL default '0',
1481 `user_id` int(11) NOT NULL default '0',
1482 `checkouts` int(11) NOT NULL default '0',
1483 `commits` int(11) NOT NULL default '0',
1484 `adds` int(11) NOT NULL default '0'
1485 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1487 -- --------------------------------------------------------
1490 -- Table structure for table `stats_cvs_group`
1493 CREATE TABLE IF NOT EXISTS `stats_cvs_group` (
1494 `month` int(11) NOT NULL default '0',
1495 `day` int(11) NOT NULL default '0',
1496 `group_id` int(11) NOT NULL default '0',
1497 `checkouts` int(11) NOT NULL default '0',
1498 `commits` int(11) NOT NULL default '0',
1499 `adds` int(11) NOT NULL default '0',
1500 UNIQUE KEY `statscvsgroup_month_day_group` (`month`,`day`,`group_id`)
1501 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1503 -- --------------------------------------------------------
1506 -- Table structure for table `stats_project_developers`
1509 CREATE TABLE IF NOT EXISTS `stats_project_developers` (
1510 `month` int(11) NOT NULL default '0',
1511 `day` int(11) NOT NULL default '0',
1512 `group_id` int(11) NOT NULL default '0',
1513 `developers` int(11) NOT NULL default '0',
1514 UNIQUE KEY `statsprojectdev_month_day_group` (`month`,`day`,`group_id`)
1515 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1517 -- --------------------------------------------------------
1520 -- Table structure for table `stats_project`
1523 CREATE TABLE IF NOT EXISTS `stats_project` (
1524 `month` int(11) NOT NULL default '0',
1525 `day` int(11) NOT NULL default '0',
1526 `group_id` int(11) NOT NULL default '0',
1527 `file_releases` int(11) default '0',
1528 `msg_posted` int(11) default '0',
1529 `msg_uniq_auth` int(11) default '0',
1530 `bugs_opened` int(11) default '0',
1531 `bugs_closed` int(11) default '0',
1532 `support_opened` int(11) default '0',
1533 `support_closed` int(11) default '0',
1534 `patches_opened` int(11) default '0',
1535 `patches_closed` int(11) default '0',
1536 `artifacts_opened` int(11) default '0',
1537 `artifacts_closed` int(11) default '0',
1538 `tasks_opened` int(11) default '0',
1539 `tasks_closed` int(11) default '0',
1540 `help_requests` int(11) default '0',
1541 UNIQUE KEY `statsproject_month_day_group` (`month`,`day`,`group_id`)
1542 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1544 -- --------------------------------------------------------
1547 -- Table structure for table `stats_site`
1550 CREATE TABLE IF NOT EXISTS `stats_site` (
1551 `month` int(11) default NULL,
1552 `day` int(11) default NULL,
1553 `uniq_users` int(11) default NULL,
1554 `sessions` int(11) default NULL,
1555 `total_users` int(11) default NULL,
1556 `new_users` int(11) default NULL,
1557 `new_projects` int(11) default NULL,
1558 UNIQUE KEY `statssite_month_day` (`month`,`day`)
1559 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1561 -- --------------------------------------------------------
1564 -- Table structure for table `activity_log`
1567 CREATE TABLE IF NOT EXISTS `activity_log` (
1568 `day` int(11) NOT NULL default '0',
1569 `hour` int(11) NOT NULL default '0',
1570 `group_id` int(11) NOT NULL default '0',
1571 `browser` varchar(8) NOT NULL default 'OTHER',
1572 `ver` double NOT NULL default '0',
1573 `platform` varchar(8) NOT NULL default 'OTHER',
1574 `time` int(11) NOT NULL default '0',
1576 `type` int(11) NOT NULL default '0'
1577 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1579 -- --------------------------------------------------------
1582 -- Table structure for table `user_metric_history`
1585 CREATE TABLE IF NOT EXISTS `user_metric_history` (
1586 `month` int(11) NOT NULL default '0',
1587 `day` int(11) NOT NULL default '0',
1588 `user_id` int(11) NOT NULL default '0',
1589 `ranking` int(11) NOT NULL default '0',
1590 `metric` double NOT NULL default '0',
1591 KEY `usermetrichistory_useridmonthday` (`user_id`,`month`,`day`)
1592 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1594 -- --------------------------------------------------------
1597 -- Table structure for table `frs_dlstats_filetotal_agg`
1600 CREATE TABLE IF NOT EXISTS `frs_dlstats_filetotal_agg` (
1601 `file_id` int(11) NOT NULL default '0',
1602 `downloads` int(11) default NULL
1603 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1605 -- --------------------------------------------------------
1608 -- Table structure for table `stats_project_months`
1611 CREATE TABLE IF NOT EXISTS `stats_project_months` (
1612 `month` int(11) default NULL,
1613 `group_id` int(11) default NULL,
1614 `developers` int(11) default NULL,
1615 `group_ranking` int(11) default NULL,
1616 `group_metric` double default NULL,
1617 `logo_showings` int(11) default NULL,
1618 `downloads` int(11) default NULL,
1619 `site_views` int(11) default NULL,
1620 `subdomain_views` int(11) default NULL,
1621 `page_views` int(11) default NULL,
1622 `file_releases` int(11) default NULL,
1623 `msg_posted` int(11) default NULL,
1624 `msg_uniq_auth` int(11) default NULL,
1625 `bugs_opened` int(11) default NULL,
1626 `bugs_closed` int(11) default NULL,
1627 `support_opened` int(11) default NULL,
1628 `support_closed` int(11) default NULL,
1629 `patches_opened` int(11) default NULL,
1630 `patches_closed` int(11) default NULL,
1631 `artifacts_opened` int(11) default NULL,
1632 `artifacts_closed` int(11) default NULL,
1633 `tasks_opened` int(11) default NULL,
1634 `tasks_closed` int(11) default NULL,
1635 `help_requests` int(11) default NULL,
1636 `cvs_checkouts` int(11) default NULL,
1637 `cvs_commits` int(11) default NULL,
1638 `cvs_adds` int(11) default NULL,
1639 KEY `statsprojectmonths_groupid` (`group_id`),
1640 KEY `statsprojectmonths_groupid_mont` (`group_id`,`month`)
1641 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1643 -- --------------------------------------------------------
1646 -- Table structure for table `stats_site_months`
1649 CREATE TABLE IF NOT EXISTS `stats_site_months` (
1650 `month` int(11) default NULL,
1651 `site_page_views` int(11) default NULL,
1652 `downloads` int(11) default NULL,
1653 `subdomain_views` int(11) default NULL,
1654 `msg_posted` int(11) default NULL,
1655 `bugs_opened` int(11) default NULL,
1656 `bugs_closed` int(11) default NULL,
1657 `support_opened` int(11) default NULL,
1658 `support_closed` int(11) default NULL,
1659 `patches_opened` int(11) default NULL,
1660 `patches_closed` int(11) default NULL,
1661 `artifacts_opened` int(11) default NULL,
1662 `artifacts_closed` int(11) default NULL,
1663 `tasks_opened` int(11) default NULL,
1664 `tasks_closed` int(11) default NULL,
1665 `help_requests` int(11) default NULL,
1666 `cvs_checkouts` int(11) default NULL,
1667 `cvs_commits` int(11) default NULL,
1668 `cvs_adds` int(11) default NULL,
1669 KEY `statssitemonths_month` (`month`)
1670 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1672 -- --------------------------------------------------------
1675 -- Table structure for table `trove_agg`
1678 CREATE TABLE IF NOT EXISTS `trove_agg` (
1679 `trove_cat_id` int(11) default NULL,
1680 `group_id` int(11) default NULL,
1681 `group_name` varchar(40) default NULL,
1682 `unix_group_name` varchar(30) default NULL,
1683 `status` char(1) default NULL,
1684 `register_time` int(11) default NULL,
1685 `short_description` varchar(255) default NULL,
1686 `percentile` double default NULL,
1687 `ranking` int(11) default NULL,
1688 KEY `troveagg_trovecatid_ranking` (`trove_cat_id`,`ranking`)
1689 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1691 -- --------------------------------------------------------
1694 -- Table structure for table `trove_treesums`
1697 CREATE TABLE IF NOT EXISTS `trove_treesums` (
1698 `trove_treesums_id` int(11) NOT NULL auto_increment,
1699 `trove_cat_id` int(11) NOT NULL default '0',
1700 `limit_1` int(11) NOT NULL default '0',
1701 `subprojects` int(11) NOT NULL default '0',
1702 PRIMARY KEY (`trove_treesums_id`)
1703 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1705 -- --------------------------------------------------------
1708 -- Table structure for table `frs_dlstats_file`
1711 CREATE TABLE IF NOT EXISTS `frs_dlstats_file` (
1712 `ip_address` varchar(25) default NULL,
1713 `file_id` int(11) default NULL,
1714 `month` int(11) default NULL,
1715 `day` int(11) default NULL,
1716 `user_id` int(11) default NULL
1717 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1719 -- --------------------------------------------------------
1722 -- Table structure for table `group_cvs_history`
1725 CREATE TABLE IF NOT EXISTS `group_cvs_history` (
1726 `id` int(11) NOT NULL auto_increment,
1727 `group_id` int(11) NOT NULL default '0',
1728 `user_name` varchar(80) NOT NULL default '',
1729 `cvs_commits` int(11) NOT NULL default '0',
1730 `cvs_commits_wk` int(11) NOT NULL default '0',
1731 `cvs_adds` int(11) NOT NULL default '0',
1732 `cvs_adds_wk` int(11) NOT NULL default '0',
1734 KEY `groupcvshistory_groupid` (`group_id`)
1735 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1737 -- --------------------------------------------------------
1740 -- Table structure for table `themes`
1743 CREATE TABLE IF NOT EXISTS `themes` (
1744 `theme_id` int(11) NOT NULL auto_increment,
1745 `dirname` varchar(80) default NULL,
1746 `fullname` varchar(80) default NULL,
1747 `enabled` tinyint(1) NOT NULL default '1',
1748 PRIMARY KEY (`theme_id`),
1749 UNIQUE KEY `themes_theme_id_key` (`theme_id`)
1750 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1752 -- --------------------------------------------------------
1755 -- Table structure for table `supported_languages`
1758 CREATE TABLE IF NOT EXISTS `supported_languages` (
1759 `language_id` int(11) NOT NULL auto_increment,
1761 `filename` varchar(25) default NULL,
1762 `classname` varchar(25) default NULL,
1763 `language_code` varchar(5) default NULL,
1764 PRIMARY KEY (`language_id`),
1765 UNIQUE KEY `supportedlanguage_code` (`language_code`)
1766 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1768 -- --------------------------------------------------------
1771 -- Table structure for table `skills_data_types`
1774 CREATE TABLE IF NOT EXISTS `skills_data_types` (
1775 `type_id` int(11) NOT NULL auto_increment,
1776 `type_name` varchar(25) NOT NULL default '',
1777 PRIMARY KEY (`type_id`)
1778 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1780 -- --------------------------------------------------------
1783 -- Table structure for table `skills_data`
1786 CREATE TABLE IF NOT EXISTS `skills_data` (
1787 `skills_data_id` int(11) NOT NULL auto_increment,
1788 `user_id` int(11) NOT NULL default '0',
1789 `type` int(11) NOT NULL default '0',
1790 `title` varchar(100) NOT NULL default '',
1791 `start` int(11) NOT NULL default '0',
1792 `finish` int(11) NOT NULL default '0',
1793 `keywords` varchar(255) NOT NULL default '',
1794 PRIMARY KEY (`skills_data_id`)
1795 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1798 CREATE OR REPLACE VIEW `frs_file_vw` AS
1799 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
1800 FROM frs_filetype, frs_processor, (frs_file LEFT JOIN frs_dlstats_filetotal_agg ON frs_dlstats_filetotal_agg.file_id = frs_file.file_id)
1801 WHERE frs_filetype.type_id = frs_file.type_id AND frs_processor.processor_id = frs_file.processor_id;
1804 -- --------------------------------------------------------
1807 -- Table structure for table `project_category`
1810 CREATE TABLE IF NOT EXISTS `project_category` (
1811 `category_id` int(11) NOT NULL auto_increment,
1812 `group_project_id` int(11) default NULL,
1813 `category_name` varchar(25) default NULL,
1814 PRIMARY KEY (`category_id`),
1815 UNIQUE KEY `project_categor_category_id_key` (`category_id`),
1816 KEY `projectcategory_groupprojectid` (`group_project_id`)
1817 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1819 -- --------------------------------------------------------
1822 -- Table structure for table `project_task_artifact`
1825 CREATE TABLE IF NOT EXISTS `project_task_artifact` (
1826 `project_task_id` int(11) NOT NULL default '0',
1827 `artifact_id` int(11) NOT NULL default '0',
1828 PRIMARY KEY (`project_task_id`),
1829 KEY `projecttaskartifact_artidprojtaskid` (`artifact_id`,`project_task_id`)
1830 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1833 CREATE OR REPLACE VIEW `project_history_user_vw` AS
1834 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
1835 FROM users, project_history
1836 WHERE project_history.mod_by = users.user_id;
1839 -- --------------------------------------------------------
1842 -- Table structure for table `project_messages`
1845 CREATE TABLE IF NOT EXISTS `project_messages` (
1846 `project_message_id` int(11) NOT NULL auto_increment,
1847 `project_task_id` int(11) NOT NULL default '0',
1848 `body` varchar(255) default NULL,
1849 `posted_by` int(11) NOT NULL default '0',
1850 `postdate` int(11) NOT NULL default '0',
1851 PRIMARY KEY (`project_message_id`),
1852 UNIQUE KEY `project_messa_project_messa_key` (`project_message_id`),
1853 KEY `projectmsgs_projtaskidpostdate` (`project_task_id`,`postdate`)
1854 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1857 CREATE OR REPLACE VIEW `project_message_user_vw` AS
1858 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
1859 FROM users, project_messages
1860 WHERE project_messages.posted_by = users.user_id;
1863 CREATE OR REPLACE VIEW `frs_dlstats_file_agg_vw` AS
1864 SELECT frs_dlstats_file.`month`, frs_dlstats_file.`day`, frs_dlstats_file.file_id, count(*) AS downloads
1865 FROM frs_dlstats_file
1866 GROUP BY frs_dlstats_file.`month`, frs_dlstats_file.`day`, frs_dlstats_file.file_id;
1869 CREATE OR REPLACE VIEW `frs_dlstats_grouptotal_vw` AS
1870 SELECT frs_package.group_id, sum(frs_dlstats_filetotal_agg.downloads) AS downloads
1871 FROM frs_package, frs_release, frs_file, frs_dlstats_filetotal_agg
1872 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
1873 GROUP BY frs_package.group_id;
1876 CREATE OR REPLACE VIEW `frs_dlstats_group_vw` AS
1877 SELECT frs_package.group_id, fdfa.`month`, fdfa.`day`, sum(fdfa.downloads) AS downloads
1878 FROM frs_package, frs_release, frs_file, frs_dlstats_file_agg_vw AS fdfa
1879 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
1880 GROUP BY frs_package.group_id, fdfa.`month`, fdfa.`day`;
1883 CREATE OR REPLACE VIEW `stats_project_vw` AS
1884 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
1885 FROM (((((((stats_project_developers AS spd
1886 LEFT JOIN stats_project AS sp USING (`month`, `day`, group_id))
1887 LEFT JOIN stats_project_metric AS spm USING (`month`, `day`, group_id))
1888 LEFT JOIN stats_cvs_group AS scg USING (`month`, `day`, group_id))
1889 LEFT JOIN stats_agg_site_by_group AS sasbg USING (`month`, `day`, group_id))
1890 LEFT JOIN stats_agg_logo_by_group AS salbg USING (`month`, `day`, group_id))
1891 LEFT JOIN stats_subd_pages AS ssp USING (`month`, `day`, group_id))
1892 LEFT JOIN frs_dlstats_group_vw AS fdga USING (`month`, `day`, group_id));
1895 CREATE OR REPLACE VIEW `stats_project_all_vw` AS
1897 stats_project_months.group_id,
1898 avg(stats_project_months.developers) AS developers,
1899 avg(stats_project_months.group_ranking) AS group_ranking,
1900 avg(stats_project_months.group_metric) AS group_metric,
1901 sum(stats_project_months.logo_showings) AS logo_showings,
1902 sum(stats_project_months.downloads) AS downloads,
1903 sum(stats_project_months.site_views) AS site_views,
1904 sum(stats_project_months.subdomain_views) AS subdomain_views,
1905 sum(stats_project_months.page_views) AS page_views,
1906 sum(stats_project_months.file_releases) AS file_releases,
1907 sum(stats_project_months.msg_posted) AS msg_posted,
1908 avg(stats_project_months.msg_uniq_auth) AS msg_uniq_auth,
1909 sum(stats_project_months.bugs_opened) AS bugs_opened,
1910 sum(stats_project_months.bugs_closed) AS bugs_closed,
1911 sum(stats_project_months.support_opened) AS support_opened,
1912 sum(stats_project_months.support_closed) AS support_closed,
1913 sum(stats_project_months.patches_opened) AS patches_opened,
1914 sum(stats_project_months.patches_closed) AS patches_closed,
1915 sum(stats_project_months.artifacts_opened) AS artifacts_opened,
1916 sum(stats_project_months.artifacts_closed) AS artifacts_closed,
1917 sum(stats_project_months.tasks_opened) AS tasks_opened,
1918 sum(stats_project_months.tasks_closed) AS tasks_closed,
1919 sum(stats_project_months.help_requests) AS help_requests,
1920 sum(stats_project_months.cvs_checkouts) AS cvs_checkouts,
1921 sum(stats_project_months.cvs_commits) AS cvs_commits,
1922 sum(stats_project_months.cvs_adds) AS cvs_adds
1923 FROM stats_project_months
1924 GROUP BY stats_project_months.group_id;
1927 CREATE OR REPLACE VIEW `stats_site_vw` AS
1928 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
1929 FROM stats_project_vw AS p, stats_site_pages_by_day AS sspbd
1930 WHERE p.`month` = sspbd.`month` AND p.`day` = sspbd.`day`
1931 GROUP BY p.`month`, p.`day`, sspbd.site_page_views;
1934 CREATE OR REPLACE VIEW `stats_site_all_vw` AS
1935 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
1936 FROM stats_site_months;
1939 -- --------------------------------------------------------
1942 -- Table structure for table `plugins`
1945 CREATE TABLE IF NOT EXISTS `plugins` (
1946 `plugin_id` int(11) NOT NULL auto_increment,
1947 `plugin_name` varchar(32) NOT NULL default '',
1948 `plugin_desc` varchar(255) default NULL,
1949 PRIMARY KEY (`plugin_id`),
1950 UNIQUE KEY `plugins_plugin_name_key` (`plugin_name`)
1951 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1953 -- --------------------------------------------------------
1956 -- Table structure for table `group_plugin`
1959 CREATE TABLE IF NOT EXISTS `group_plugin` (
1960 `group_plugin_id` int(11) NOT NULL auto_increment,
1961 `group_id` int(11) default NULL,
1962 `plugin_id` int(11) default NULL,
1963 PRIMARY KEY (`group_plugin_id`),
1964 KEY `groupplugin_groupid` (`group_id`)
1965 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1967 -- --------------------------------------------------------
1970 -- Table structure for table `user_plugin`
1973 CREATE TABLE IF NOT EXISTS `user_plugin` (
1974 `user_plugin_id` int(11) NOT NULL auto_increment,
1975 `user_id` int(11) NOT NULL default '0',
1976 `plugin_id` int(11) NOT NULL default '0',
1977 PRIMARY KEY (`user_plugin_id`)
1978 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1980 -- --------------------------------------------------------
1983 -- Table structure for table `cron_history`
1986 CREATE TABLE IF NOT EXISTS `cron_history` (
1987 `rundate` int(11) NOT NULL default '0',
1988 `job` varchar(255) default NULL,
1989 `output` text default NULL,
1990 KEY `cronhist_rundate` (`rundate`),
1991 KEY `cronhist_jobrundate` (`job`,`rundate`)
1992 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
1995 -- --------------------------------------------------------
1998 -- Table structure for table `country_code`
2001 CREATE TABLE IF NOT EXISTS `country_code` (
2002 `country_name` varchar(80) default NULL,
2003 `ccode` char(2) NOT NULL default ''
2004 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2006 -- --------------------------------------------------------
2009 -- Table structure for table `licenses`
2012 CREATE TABLE IF NOT EXISTS `licenses` (
2013 `license_id` varchar(10) NOT NULL default '',
2014 `license_name` varchar(100) default NULL
2015 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2017 -- --------------------------------------------------------
2020 -- Table structure for table `user_type`
2023 CREATE TABLE IF NOT EXISTS `user_type` (
2024 `type_id` int(11) NOT NULL auto_increment,
2025 `type_name` varchar(25) default NULL,
2026 PRIMARY KEY (`type_id`)
2027 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2029 -- --------------------------------------------------------
2032 -- Table structure for table `role`
2035 CREATE TABLE IF NOT EXISTS `role` (
2036 `role_id` int(10) NOT NULL auto_increment,
2037 `group_id` int(11) NOT NULL default '0',
2038 `role_name` varchar(25) default NULL,
2039 PRIMARY KEY (`role_id`),
2040 UNIQUE KEY `role_groupidroleid` (`group_id`,`role_id`)
2041 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2043 -- --------------------------------------------------------
2046 -- Table structure for table `project_perm`
2049 CREATE TABLE IF NOT EXISTS `project_perm` (
2050 `id` varchar(10) NOT NULL default '',
2051 `group_project_id` int(11) NOT NULL default '0',
2052 `user_id` int(11) NOT NULL default '0',
2053 `perm_level` int(11) NOT NULL default '0',
2054 KEY `projectperm_useridgroupprojid` (`user_id`,`group_project_id`)
2055 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2057 -- --------------------------------------------------------
2060 -- Table structure for table `forum_perm`
2063 CREATE TABLE IF NOT EXISTS `forum_perm` (
2064 `id` int(11) NOT NULL auto_increment,
2065 `group_forum_id` int(11) NOT NULL default '0',
2066 `user_id` int(11) NOT NULL default '0',
2067 `perm_level` int(11) NOT NULL default '0',
2069 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2071 -- --------------------------------------------------------
2074 -- Table structure for table `role_setting`
2077 CREATE TABLE IF NOT EXISTS `role_setting` (
2078 `role_id` int(11) NOT NULL default '0',
2079 `section_name` varchar(25) NOT NULL default '',
2080 `ref_id` int(11) NOT NULL default '0',
2081 `value` char(2) NOT NULL default ''
2082 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2084 -- --------------------------------------------------------
2087 -- Table structure for table `artifact_extra_field_list`
2090 CREATE TABLE IF NOT EXISTS `artifact_extra_field_list` (
2091 `extra_field_id` int(11) NOT NULL auto_increment,
2092 `group_artifact_id` int(11) NOT NULL default '0',
2093 `field_name` varchar(255) NOT NULL,
2094 `field_type` int(11) default '1',
2095 `attribute1` int(11) default '0',
2096 `attribute2` int(11) default '0',
2097 `is_required` tinyint(1) NOT NULL default '0',
2099 PRIMARY KEY (`extra_field_id`),
2100 KEY `artifactextrafieldlist_groupartid` (`group_artifact_id`)
2101 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2103 -- --------------------------------------------------------
2106 -- Table structure for table `artifact_extra_field_elements`
2109 CREATE TABLE IF NOT EXISTS `artifact_extra_field_elements` (
2110 `element_id` int(11) NOT NULL auto_increment,
2111 `extra_field_id` int(11) NOT NULL default '0',
2112 `element_name` text NOT NULL,
2113 `status_id` int(11) NOT NULL default '0',
2114 PRIMARY KEY (`element_id`),
2115 KEY `artifactextrafldlmts_extrafieldid` (`extra_field_id`)
2116 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2118 -- --------------------------------------------------------
2121 -- Table structure for table `artifact_extra_field_data`
2124 CREATE TABLE IF NOT EXISTS `artifact_extra_field_data` (
2125 `data_id` int(11) NOT NULL auto_increment,
2126 `artifact_id` int(11) NOT NULL default '0',
2128 `extra_field_id` int(11) default '0',
2129 PRIMARY KEY (`data_id`),
2130 KEY `artifactextrafielddata_artifactid` (`artifact_id`)
2131 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2133 -- --------------------------------------------------------
2136 -- Table structure for table `project_counts_agg`
2139 CREATE TABLE IF NOT EXISTS `project_counts_agg` (
2140 `group_project_id` int(11) NOT NULL default '0',
2141 `count` int(11) NOT NULL default '0',
2142 `open_count` int(11) default '0'
2143 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2146 CREATE OR REPLACE VIEW `project_group_list_vw` AS
2147 SELECT project_group_list.group_project_id, group_id, project_name, is_public, description, send_all_posts_to, `count`, open_count
2148 FROM (project_group_list
2149 LEFT JOIN project_counts_agg ON project_counts_agg.group_project_id = project_group_list.group_project_id);
2152 -- --------------------------------------------------------
2155 -- Table structure for table `project_task_external_order`
2158 CREATE TABLE IF NOT EXISTS `project_task_external_order` (
2159 `project_task_id` int(11) NOT NULL default '0',
2160 `external_id` int(11) NOT NULL default '0'
2161 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2164 CREATE OR REPLACE VIEW `project_depend_vw` AS
2165 SELECT pt.project_task_id, pd.is_dependent_on_task_id, pd.link_type, pt.end_date, pt.start_date
2166 FROM (project_task pt NATURAL JOIN project_dependencies pd);
2169 CREATE OR REPLACE VIEW `project_dependon_vw` AS
2170 SELECT pd.project_task_id, pd.is_dependent_on_task_id, pd.link_type, pt.end_date, pt.start_date
2171 FROM (project_task AS pt
2172 LEFT JOIN project_dependencies AS pd ON pd.is_dependent_on_task_id = pt.project_task_id)
2174 SELECT pd.project_task_id, pd.is_dependent_on_task_id, pd.link_type, pt.end_date, pt.start_date
2175 FROM (project_task AS pt
2176 RIGHT JOIN project_dependencies AS pd ON pd.is_dependent_on_task_id = pt.project_task_id);
2179 -- --------------------------------------------------------
2182 -- Table structure for table `group_join_request`
2185 CREATE TABLE IF NOT EXISTS `group_join_request` (
2186 `group_id` int(11) NOT NULL default '0',
2187 `user_id` int(11) NOT NULL default '0',
2188 `comments` varchar(255) default NULL,
2189 `request_date` int(11) default NULL
2190 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2193 CREATE OR REPLACE VIEW `project_task_vw` AS
2195 project_task.project_task_id,
2196 project_task.group_project_id,
2197 project_task.summary,
2198 project_task.details,
2199 project_task.percent_complete,
2200 project_task.priority,
2202 project_task.start_date,
2203 project_task.end_date,
2204 project_task.created_by,
2205 project_task.status_id,
2206 project_task.category_id,
2207 project_task.duration,
2208 project_task.parent_id,
2209 project_task.last_modified_date,
2210 project_category.category_name,
2211 project_status.status_name,
2215 LEFT JOIN project_category ON project_category.category_id = project_task.category_id)
2216 LEFT JOIN users ON users.user_id = project_task.created_by)
2217 LEFT JOIN project_status ON project_status.status_id = project_task.status_id;
2220 -- --------------------------------------------------------
2223 -- Table structure for table `artifact_type_monitor`
2226 CREATE TABLE IF NOT EXISTS `artifact_type_monitor` (
2227 `group_artifact_id` int(11) NOT NULL default '0',
2228 `user_id` int(11) NOT NULL default '0'
2229 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2231 -- --------------------------------------------------------
2234 -- Table structure for table `plugin_cvstracker_data_artifact`
2237 CREATE TABLE IF NOT EXISTS `plugin_cvstracker_data_artifact` (
2238 `id` int(11) NOT NULL auto_increment,
2239 `kind` int(11) NOT NULL default '0',
2240 `group_artifact_id` int(11) default NULL,
2241 `project_task_id` int(11) default NULL,
2243 KEY `plugin_cvstracker_group_artifact_id` (`group_artifact_id`)
2244 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2246 -- --------------------------------------------------------
2249 -- Table structure for table `plugin_cvstracker_data_master`
2252 CREATE TABLE IF NOT EXISTS `plugin_cvstracker_data_master` (
2253 `id` int(11) NOT NULL auto_increment,
2254 `holder_id` int(11) NOT NULL default '0',
2255 `log_text` varchar(255) default NULL,
2256 `file` varchar(25) NOT NULL default '',
2257 `prev_version` varchar(25) default NULL,
2258 `actual_version` varchar(25) default NULL,
2259 `author` varchar(25) NOT NULL default '',
2260 `cvs_date` int(11) NOT NULL default '0',
2262 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2264 -- --------------------------------------------------------
2266 CREATE OR REPLACE VIEW `nss_passwd` AS
2267 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
2269 WHERE users.unix_status = 'A';
2272 CREATE OR REPLACE VIEW `nss_shadow` AS
2273 SELECT users.user_name AS login, users.unix_pw AS passwd, 'n' AS expired, 'n' AS pwchange
2275 WHERE users.unix_status = 'A';
2278 -- Table structure for table `nss_groups`
2281 CREATE TABLE IF NOT EXISTS `nss_groups` (
2282 `user_id` int(11) default NULL,
2283 `group_id` int(11) default NULL,
2284 `name` varchar(30) default NULL,
2285 `gid` int(11) default NULL
2286 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2288 -- --------------------------------------------------------
2291 -- Table structure for table `nss_usergroups`
2294 CREATE TABLE IF NOT EXISTS `nss_usergroups` (
2295 `uid` int(11) default NULL,
2296 `gid` int(11) default NULL,
2297 `user_id` int(11) default NULL,
2298 `group_id` int(11) default NULL,
2299 `user_name` varchar(25) default NULL,
2300 `unix_group_name` varchar(30) default NULL
2301 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2303 -- --------------------------------------------------------
2306 -- Table structure for table `deleted_mailing_lists`
2309 CREATE TABLE IF NOT EXISTS `deleted_mailing_lists` (
2310 `mailing_list_name` varchar(30) default NULL,
2311 `delete_date` int(11) default NULL,
2312 `isdeleted` int(11) default NULL
2313 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2315 -- --------------------------------------------------------
2318 -- Table structure for table `deleted_groups`
2321 CREATE TABLE IF NOT EXISTS `deleted_groups` (
2322 `unix_group_name` varchar(30) default NULL,
2323 `delete_date` int(11) default NULL,
2324 `isdeleted` int(11) default NULL
2325 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2327 -- --------------------------------------------------------
2330 -- Table structure for table `artifact_query`
2333 CREATE TABLE IF NOT EXISTS `artifact_query` (
2334 `artifact_query_id` varchar(10) NOT NULL default '',
2335 `group_artifact_id` int(11) NOT NULL default '0',
2336 `user_id` int(11) NOT NULL default '0',
2337 `query_name` text NOT NULL
2338 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2340 -- --------------------------------------------------------
2343 -- Table structure for table `artifact_query_fields`
2346 CREATE TABLE IF NOT EXISTS `artifact_query_fields` (
2347 `artifact_query_id` int(11) NOT NULL default '0',
2348 `query_field_type` text NOT NULL,
2349 `query_field_id` int(11) NOT NULL default '0',
2350 `query_field_values` text NOT NULL
2351 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2354 CREATE OR REPLACE VIEW `artifact_group_list_vw` AS
2355 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
2356 FROM (artifact_group_list AS agl
2357 LEFT JOIN artifact_counts_agg AS aca USING (group_artifact_id));
2360 CREATE OR REPLACE VIEW `artifact_vw` AS
2361 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
2362 FROM users u, users u2, artifact_status, artifact
2363 WHERE artifact.assigned_to = u.user_id AND artifact.submitted_by = u2.user_id AND artifact.status_id = artifact_status.id;
2365 CREATE OR REPLACE VIEW `docdata_vw` AS
2383 doc_states.name AS state_name,
2384 doc_groups.groupname AS group_name,
2385 sl.name AS language_name
2387 NATURAL JOIN doc_states)
2388 NATURAL JOIN doc_groups)
2389 JOIN supported_languages sl ON sl.language_id = d.language_id)
2390 JOIN users ON users.user_id = d.created_by);
2393 CREATE TABLE IF NOT EXISTS `form_keys` (
2394 key_id int(11) NOT NULL auto_increment,
2395 `key` char(32) NOT NULL,
2396 creation_date int(11) NOT NULL,
2397 is_used tinyint(1) default 0 NOT NULL,
2398 PRIMARY KEY (`key_id`),
2399 UNIQUE KEY `key` (`key`)
2400 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2404 CREATE TABLE IF NOT EXISTS `forum_attachment` (
2405 attachmentid INT(11) NOT NULL AUTO_INCREMENT,
2406 userid INT(11) default 100 NOT NULL,
2407 dateline INT(11) default 0 NOT NULL,
2408 filename VARCHAR(100) DEFAULT '' NOT NULL,
2409 filedata LONGBLOB NOT NULL,
2410 visible SMALLINT default 0 NOT NULL,
2411 counter SMALLINT default 0 NOT NULL,
2412 filesize INT(11) default 0 NOT NULL,
2413 msg_id INT(11) default 0 NOT NULL,
2414 filehash VARCHAR(32) DEFAULT '' NOT NULL,
2415 mimetype VARCHAR(255) 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) FROM forum
2443 WHERE (forum.group_forum_id = forum_group_list.group_forum_id)) AS recent,
2444 (SELECT count(distinct forum.thread_id) FROM forum
2445 WHERE (forum.group_forum_id = forum_group_list.group_forum_id)) AS threads
2446 FROM (forum_group_list LEFT JOIN forum_agg_msg_count USING (group_forum_id));
2449 CREATE TABLE IF NOT EXISTS `forum_pending_messages` (
2450 msg_id int(11) NOT NULL auto_increment,
2451 group_forum_id int(11) DEFAULT 0 NOT NULL,
2452 posted_by int(11) DEFAULT 0 NOT NULL,
2453 subject text DEFAULT '' NOT NULL,
2454 body text DEFAULT '' NOT NULL,
2455 post_date int(11) DEFAULT 0 NOT NULL,
2456 is_followup_to int(11) DEFAULT 0 NOT NULL,
2457 thread_id int(11) DEFAULT 0 NOT NULL,
2458 has_followups int(11) DEFAULT 0,
2459 most_recent_date int(11) DEFAULT 0 NOT NULL,
2460 PRIMARY KEY (`msg_id`)
2461 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2465 CREATE TABLE IF NOT EXISTS `forum_pending_attachment` (
2466 attachmentid int(11) NOT NULL auto_increment,
2467 userid int(11) DEFAULT 100 NOT NULL,
2468 dateline int(11) DEFAULT 0 NOT NULL,
2469 filename character varying(100) DEFAULT '' NOT NULL,
2470 filedata mediumblob NOT NULL,
2471 visible smallint DEFAULT 0 NOT NULL,
2472 counter smallint DEFAULT 0 NOT NULL,
2473 filesize int(11) DEFAULT 0 NOT NULL,
2474 msg_id int(11) DEFAULT 0 NOT NULL,
2475 filehash character varying(32) DEFAULT '' NOT NULL,
2476 PRIMARY KEY (`attachmentid`)
2477 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2481 CREATE OR REPLACE VIEW `forum_user_vw` AS
2484 forum.group_forum_id,
2489 forum.is_followup_to,
2491 forum.has_followups,
2492 forum.most_recent_date,
2496 WHERE (forum.posted_by = users.user_id);
2500 CREATE OR REPLACE VIEW `forum_pending_user_vw` AS
2502 forum_pending_messages.msg_id,
2503 forum_pending_messages.group_forum_id,
2504 forum_pending_messages.posted_by,
2505 forum_pending_messages.subject,
2506 forum_pending_messages.body,
2507 forum_pending_messages.post_date,
2508 forum_pending_messages.is_followup_to,
2509 forum_pending_messages.thread_id,
2510 forum_pending_messages.has_followups,
2511 forum_pending_messages.most_recent_date,
2512 users.user_name, users.realname
2513 FROM forum_pending_messages, users
2514 WHERE (forum_pending_messages.posted_by = users.user_id);
2518 CREATE TABLE IF NOT EXISTS `group_activity_monitor` (
2519 group_id int(11) NOT NULL,
2520 user_id int(11) NOT NULL,
2522 PRIMARY KEY (`group_id`,`user_id`)
2523 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2527 CREATE OR REPLACE VIEW `activity_vw` AS
2530 'trackeropen' AS section,
2531 agl.group_artifact_id AS ref_id,
2532 a.artifact_id AS subref_id,
2533 a.summary AS description,
2534 a.open_date AS activity_date,
2535 u.user_id, u.user_name,
2537 FROM (artifact_group_list agl
2538 JOIN artifact a USING (group_artifact_id)), users u
2539 WHERE (u.user_id = a.submitted_by))
2543 'trackerclose' AS section,
2544 agl.group_artifact_id AS ref_id,
2545 a.artifact_id AS subref_id,
2546 a.summary AS description,
2547 a.close_date AS activity_date,
2551 FROM (artifact_group_list agl
2552 JOIN artifact a USING (group_artifact_id)), users u
2553 WHERE ((u.user_id = a.assigned_to) AND (a.close_date > 0)))
2557 'commit' AS section,
2558 agl.group_artifact_id AS ref_id,
2559 a.artifact_id AS subref_id,
2560 pcdm.log_text AS description,
2561 pcdm.cvs_date AS activity_date,
2562 u.user_id, u.user_name, u.realname
2563 FROM (artifact_group_list agl JOIN artifact a
2564 USING (group_artifact_id)), plugin_cvstracker_data_master pcdm, plugin_cvstracker_data_artifact pcda, users u
2565 WHERE (((pcdm.holder_id = pcda.id) AND (pcda.group_artifact_id = a.artifact_id)) AND (u.user_name = pcdm.author)))
2569 'frsrelease' AS section,
2570 frsp.package_id AS ref_id,
2571 frsr.release_id AS subref_id,
2572 frsr.name AS description,
2573 frsr.release_date AS activity_date,
2574 u.user_id, u.user_name,
2576 FROM (frs_package frsp JOIN frs_release frsr USING (package_id)), users u
2577 WHERE (u.user_id = frsr.released_by))
2581 'forumpost' AS section,
2582 fgl.group_forum_id AS ref_id,
2583 forum.msg_id AS subref_id,
2584 forum.subject AS description,
2585 forum.post_date AS activity_date,
2589 FROM (forum_group_list fgl
2590 JOIN forum USING (group_forum_id)), users u
2591 WHERE (u.user_id = forum.posted_by))
2594 news_bytes.group_id,
2596 news_bytes.id AS ref_id,
2597 news_bytes.forum_id AS subref_id,
2598 news_bytes.summary AS description,
2599 news_bytes.post_date AS activity_date,
2603 FROM news_bytes, users u
2604 WHERE (u.user_id = news_bytes.submitted_by));
2608 -- --------------------------------------------------------
2611 -- Table structure for table `forum_thread_seq`
2614 CREATE TABLE IF NOT EXISTS `forum_thread_seq` (
2615 `value` int(11) NOT NULL default '1'
2616 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2618 DROP PROCEDURE IF EXISTS newval;
2620 CREATE PROCEDURE newval (IN tablename VARCHAR(64), OUT result INT)
2622 SET @s=CONCAT('SELECT value INTO @newvalue FROM ',tablename);
2623 SET @u=CONCAT('UPDATE ',tablename,' SET value=value+1 WHERE value=@newvalue;');
2624 PREPARE select_stmt FROM @s;
2625 PREPARE update_stmt FROM @u;
2628 EXECUTE select_stmt;
2629 IF @newvalue = NULL THEN
2632 EXECUTE update_stmt;
2633 IF row_count() = 1 THEN
2636 END LOOP update_loop;
2638 DEALLOCATE PREPARE select_stmt;
2639 DEALLOCATE PREPARE update_stmt;
2641 SET result=@newvalue;