===================================================== SourceForge Research Data Warehouse: Tables ===================================================== Y. Gao and G. Madey (Feburary, 2006) The data warehouse has 76 tables in the schema sf0106 (January 2006). The tables are listed in alphabetic order. Table "SCHEMA.artifact" Column | Type | Modifiers -------------------+---------+-------------------------------------------------------------- artifact_id | integer | not null group_artifact_id | integer | not null status_id | integer | not null category_id | integer | not null artifact_group_id | integer | not null resolution_id | integer | not null priority | integer | not null submitted_by | integer | not null assigned_to | integer | not null open_date | integer | not null close_date | integer | not null summary | text | not null details | text | not null closed_by | integer | Table "SCHEMA.artifact_canned_responses" Column | Type | Modifiers -------------------+---------+--------------------------------------------------------------------------------------- id | integer | not null group_artifact_id | integer | not null title | text | not null body | text | not null state | integer | not null added_by | integer | not null mod_by | integer | not null last_updated | integer | not null Table "SCHEMA.artifact_category" Column | Type | Modifiers -------------------+---------+-------------------------------------------------------------- id | integer | not null group_artifact_id | integer | not null category_name | text | not null auto_assign_to | integer | not null Table "SCHEMA.artifact_counts_agg" Column | Type | Modifiers -------------------+---------+-------------------- group_artifact_id | integer | not null count | integer | not null open_count | integer | not null Table "SCHEMA.artifact_file" Column | Type | Modifiers --------------+---------+---------------------------------------------------------- id | integer | not null artifact_id | integer | not null description | text | not null bin_data | text | filename | text | not null filesize | integer | not null filetype | text | not null adddate | integer | not null submitted_by | integer | not null Table "SCHEMA.artifact_group" Column | Type | Modifiers -------------------+---------+----------------------------------------------------------- id | integer | not null group_artifact_id | integer | not null group_name | text | not null Table "SCHEMA.artifact_group_list" Column | Type | Modifiers ---------------------+---------+--------------------------------------------------------------------- group_artifact_id | integer | not null group_id | integer | not null name | text | description | text | is_public | integer | not null allow_anon | integer | not null email_all_updates | integer | not null due_period | integer | not null use_resolution | integer | not null submit_instructions | text | browse_instructions | text | datatype | integer | not null status_timeout | integer | due_period_initial | integer | not null due_period_update | integer | not null Table "SCHEMA.artifact_history" Column | Type | Modifiers -------------+---------+------------------------------------------------------------- id | integer | not null artifact_id | integer | not null field_name | text | not null old_value | text | not null mod_by | integer | not null entrydate | integer | not null Table "SCHEMA.artifact_message" Column | Type | Modifiers --------------+---------+------------------------------------------------------------- id | integer | not null artifact_id | integer | not null submitted_by | integer | not null from_email | text | not null adddate | integer | not null body | text | not null Table "SCHEMA.artifact_perm" Column | Type | Modifiers -------------------+---------+---------------------------------------------------------- id | integer | not null group_artifact_id | integer | not null user_id | integer | not null perm_level | integer | not null Table "SCHEMA.artifact_resolution" Column | Type | Modifiers -----------------+---------+---------------------------------------------------------------- id | integer | not null resolution_name | text | Table "SCHEMA.artifact_status" Column | Type | Modifiers -------------+---------+------------------------------------------------------------ id | integer | not null status_name | text | not null Table "SCHEMA.db_images" Column | Type | Modifiers -------------+---------+---------------------------------------------------- id | integer | not null group_id | integer | not null description | text | not null bin_data | text | not null filename | text | not null filesize | integer | not null filetype | text | not null width | integer | not null height | integer | not null upload_date | integer | version | integer | tool_id | integer | Table "SCHEMA.doc_data" Column | Type | Modifiers -------------+------------------------+--------------------------------------------------- docid | integer | not null stateid | integer | not null title | character varying(255) | not null data | text | not null updatedate | integer | not null createdate | integer | not null created_by | integer | not null doc_group | integer | not null description | text | language_id | integer | not null Table "SCHEMA.doc_groups" Column | Type | Modifiers -----------+------------------------+----------------------------------------------------- doc_group | integer | not null groupname | character varying(255) | not null group_id | integer | not null Table "SCHEMA.doc_states" Column | Type | Modifiers ---------+------------------------+----------------------------------------------------- stateid | integer | not null name | character varying(255) | not null Table "SCHEMA.forum" Column | Type | Modifiers ------------------+---------+------------------------------------------------ msg_id | integer | not null group_forum_id | integer | not null posted_by | integer | not null subject | text | not null body | text | not null date | integer | not null is_followup_to | integer | not null thread_id | integer | not null has_followups | integer | most_recent_date | integer | not null is_deleted | integer | Table "SCHEMA.forum_agg_msg_count" Column | Type | Modifiers ----------------+---------+-------------------- group_forum_id | integer | not null count | integer | not null Table "SCHEMA.forum_group_list" Column | Type | Modifiers -----------------+---------+----------------------------------------------------------- group_forum_id | integer | not null group_id | integer | not null forum_name | text | not null is_public | integer | not null description | text | allow_anonymous | integer | not null Table "SCHEMA.forum_threadinfo" Column | Type | Modifiers ------------------------+---------+------------------------------------------------------- threadinfo_id | integer | not null thread_id | integer | not null thread_topic | text | not null thread_starter_user_id | integer | not null num_replies | integer | most_recent_post_date | integer | group_forum_id | integer | not null msg_id | integer | not null Table "SCHEMA.frs_file" Column | Type | Modifiers --------------+-----------------------+--------------------------------------------------- file_id | integer | not null filename | text | release_id | integer | not null type_id | integer | not null processor_id | integer | not null release_time | integer | not null file_size | integer | not null post_date | integer | not null group_id | integer | package_id | integer | md5sum | character varying(32) | Table "SCHEMA.frs_filetype" Column | Type | Modifiers ---------+---------+------------------------------------------------------- type_id | integer | not null name | text | Table "SCHEMA.frs_package" Column | Type | Modifiers ------------+---------+------------------------------------------------------ package_id | integer | not null group_id | integer | not null name | text | status_id | integer | not null Table "SCHEMA.frs_processor" Column | Type | Modifiers --------------+---------+-------------------------------------------------------- processor_id | integer | not null name | text | Table "SCHEMA.frs_release" Column | Type | Modifiers --------------+---------+------------------------------------------------------ release_id | integer | not null package_id | integer | not null name | text | notes | text | changes | text | status_id | integer | not null preformatted | integer | not null release_date | integer | not null released_by | integer | not null Table "SCHEMA.frs_snapshots" Column | Type | Modifiers ----------------+---------+--------------------------- filename | text | not null group_id | integer | not null notes | text | not null file_size | integer | not null post_timestamp | integer | not null post_uid | integer | not null md5sum | text | status_id | integer | not null Table "SCHEMA.frs_status" Column | Type | Modifiers -----------+---------+----------------------------------------------------- status_id | integer | not null name | text | Table "SCHEMA.group_type" Column | Type | Modifiers ---------+---------+----------------------------------------------------- type_id | integer | not null name | text | Table "SCHEMA.groups" Column | Type | Modifiers ----------------------------+------------------------+------------------------------------------------- group_id | integer | not null group_name | character varying(40) | homepage | character varying(128) | is_public | integer | not null status | character(1) | not null unix_group_name | character varying(30) | not null http_domain | character varying(80) | short_description | character varying(255) | license | character varying(16) | register_time | integer | not null use_mail | integer | not null use_forum | integer | not null use_pm | integer | not null use_cvs | integer | not null use_news | integer | not null preferred_support_type | integer | not null preferred_support_resource | text | not null type | integer | not null use_docman | integer | not null not_open_source | integer | not null send_all_tasks | integer | not null use_pm_depend_box | integer | not null potm | integer | donation_request | text | donate_optin | integer | big_mirror | integer | project_submitter | integer | row_modtime | integer | use_screenshots | integer | not null use_snapshots | integer | not null use_svn | integer | not null Table "SCHEMA.groups_historical" Column | Type | Modifiers ----------+---------+-------------------- group_id | integer | not null date | integer | not null added_by | integer | not null status | integer | not null url | text | Table "SCHEMA.groups_registration" Column | Type | Modifiers -------------------+------------------------+----------------------------------------------------- id | integer | not null group_name | character varying(41) | unix_group_name | character varying(30) | not null status | character(1) | not null status_modtime | integer | not null entry_type | integer | not null short_description | character varying(256) | register_purpose | text | license | character varying(16) | license_other | text | register_time | integer | not null project_submitter | integer | not null suspicious | integer | not null issue_type | character varying(20) | not null project_type | character varying(20) | not null apt_type | character varying(20) | not null not_open_source | integer | not null review_response | text | copy_id | integer | not null row_modtime | integer | not null Table "SCHEMA.mail_group_list" Column | Type | Modifiers ---------------+---------+---------------------------------------------------------- group_list_id | integer | not null group_id | integer | not null list_name | text | is_public | integer | not null list_admin | integer | not null status | integer | not null description | text | row_modtime | integer | Table "SCHEMA.mllist_subscriber_count" Column | Type | Modifiers ------------------+---------+----------- ml_list_name | text | not null subscriber_count | integer | Table "SCHEMA.news_bytes" Column | Type | Modifiers --------------+---------+----------------------------------------------------- id | integer | not null group_id | integer | not null submitted_by | integer | not null is_approved | integer | not null date | integer | not null forum_id | integer | not null summary | text | details | text | Table "SCHEMA.people_job" Column | Type | Modifiers -------------+---------+----------------------------------------------------- job_id | integer | not null group_id | integer | not null created_by | integer | not null title | text | description | text | date | integer | not null status_id | integer | not null category_id | integer | not null Table "SCHEMA.people_job_category" Column | Type | Modifiers --------------+---------+-------------------------------------------------------------- category_id | integer | not null name | text | private_flag | integer | not null Table "SCHEMA.people_job_inventory" Column | Type | Modifiers ------------------+---------+--------------------------------------------------------------- job_inventory_id | integer | not null job_id | integer | not null skill_id | integer | not null skill_level_id | integer | not null skill_year_id | integer | not null Table "SCHEMA.people_job_status" Column | Type | Modifiers -----------+---------+------------------------------------------------------------ status_id | integer | not null name | text | Table "SCHEMA.people_skill_inventory" Column | Type | Modifiers --------------------+---------+----------------------------------------------------------------- skill_inventory_id | integer | not null user_id | integer | not null skill_id | integer | not null skill_level_id | integer | not null skill_year_id | integer | not null Table "SCHEMA.people_skill_level" Column | Type | Modifiers ----------------+---------+------------------------------------------------------------- skill_level_id | integer | not null name | text | Table "SCHEMA.people_skill_year" Column | Type | Modifiers ---------------+---------+------------------------------------------------------------ skill_year_id | integer | not null name | text | Table "SCHEMA.pg_autovac_skip" Column | Type | Modifiers ------------+------+----------- table_name | text | not null Table "SCHEMA.project_assigned_to" Column | Type | Modifiers ---------------------+---------+-------------------------------------------------------------- project_assigned_id | integer | not null project_task_id | integer | not null assigned_to_id | integer | not null Table "SCHEMA.project_dependencies" Column | Type | Modifiers -------------------------+---------+--------------------------------------------------------------- project_depend_id | integer | not null project_task_id | integer | not null is_dependent_on_task_id | integer | not null Table "SCHEMA.project_group_list" Column | Type | Modifiers ------------------+---------+------------------------------------------------------------- group_project_id | integer | not null group_id | integer | not null project_name | text | not null is_public | integer | not null description | text | Table "SCHEMA.project_history" Column | Type | Modifiers --------------------+---------+---------------------------------------------------------- project_history_id | integer | not null project_task_id | integer | not null field_name | text | not null old_value | text | not null mod_by | integer | not null date | integer | not null Table "SCHEMA.project_status" Column | Type | Modifiers -------------+---------+--------------------------------------------------------- status_id | integer | not null status_name | text | not null Table "SCHEMA.project_sums_agg" Column | Type | Modifiers ----------+--------------+-------------------- group_id | integer | not null type | character(4) | count | integer | not null Table "SCHEMA.project_task" Column | Type | Modifiers ------------------+------------------+------------------------------------------------------- project_task_id | integer | not null group_project_id | integer | not null summary | text | not null details | text | not null percent_complete | integer | not null priority | integer | not null hours | double precision | not null start_date | integer | not null end_date | integer | not null created_by | integer | not null status_id | integer | not null Table "SCHEMA.ref_timezones" Column | Type | Modifiers ---------------+---------+-------------------------------------------------------- timezone_id | integer | not null timezone_name | text | not null row_modtime | integer | Table "SCHEMA.scm_repo_trigger" Column | Type | Modifiers ----------------+---------+-------------------------------------------------------- id | integer | not null scm_trigger_id | integer | not null group_id | integer | not null params | text | added_by | integer | not null added_on | integer | not null Table "SCHEMA.scm_trigger" Column | Type | Modifiers ---------------+---------+--------------------------------------------------- id | integer | not null scm_type | integer | not null trigger_type | integer | not null status | integer | not null script | text | not null params_format | text | not null description | text | Table "SCHEMA.screenshots" Column | Type | Modifiers ----------------------+-----------------------+----------------------------------------------------- screenshot_id | integer | not null group_id | integer | not null main_image_id | integer | not null thumb_image_id | integer | not null main_width | integer | not null main_height | integer | not null thumb_width | integer | not null thumb_height | integer | not null description | character varying(60) | not null lastchange_timestamp | integer | not null lastchange_uid | integer | not null Table "SCHEMA.stats_cvs_group" Column | Type | Modifiers -----------+---------+-------------------- month | integer | not null day | integer | not null group_id | integer | not null checkouts | integer | not null commits | integer | not null adds | integer | not null Table "SCHEMA.stats_fileid_alltime_agg" Column | Type | Modifiers -----------+---------+------------------------------ file_id | integer | not null downloads | integer | not null bytes | bigint | not null Table "SCHEMA.stats_group_rank" Column | Type | Modifiers ------------+------------------+---------------------- group_id | integer | not null score | integer | not null ranking | integer | not null percentile | double precision | not null Table "SCHEMA.stats_group_rank_alltime" Column | Type | Modifiers ------------+------------------+-------------------- group_id | integer | not null ranking | integer | not null percentile | double precision | Table "SCHEMA.stats_group_rank_byday" Column | Type | Modifiers ------------+------------------+-------------------- rankdate | integer | not null group_id | integer | not null ranking | integer | not null percentile | double precision | score | integer | not null Table "SCHEMA.stats_group_rank_bymonth" Column | Type | Modifiers ------------+------------------+------------------------------ group_id | integer | not null rankdate | integer | not null ranking | integer | not null percentile | double precision | score | bigint | not null Table "SCHEMA.stats_groupid_alltime_agg" Column | Type | Modifiers -----------+---------+------------------------------ group_id | integer | not null downloads | integer | not null bytes | bigint | not null Table "SCHEMA.stats_multi_rank_history_byday" Column | Type | Modifiers ---------------------------+------------------+---------------------------------------- group_id | integer | not null timekey | integer | not null prweb_7day | integer | not null prweb_7day_rank | integer | not null prweb_7day_percentile | double precision | sflogo_7day | integer | not null sflogo_7day_rank | integer | not null sflogo_7day_percentile | double precision | sfweb_7day | integer | not null sfweb_7day_rank | integer | not null sfweb_7day_percentile | double precision | forum_7day | integer | not null forum_7day_rank | integer | not null forum_7day_percentile | double precision | downloads_7day | integer | not null downloads_7day_rank | integer | not null downloads_7day_percentile | double precision | tracker_7day | integer | not null tracker_7day_rank | integer | not null tracker_7day_percentile | double precision | cvs_7day | integer | not null cvs_7day_rank | integer | not null cvs_7day_percentile | double precision | not null mlist_7day | integer | mlist_7day_rank | integer | mlist_7day_percentile | integer | last_file_release | integer | last_admin_login | integer | Table "SCHEMA.stats_project_all" Column | Type | Modifiers ------------------+------------------+----------- group_id | integer | developers | integer | group_ranking | integer | group_metric | double precision | logo_showings | integer | downloads | integer | site_views | integer | subdomain_views | integer | page_views | integer | msg_posted | integer | msg_uniq_auth | integer | bugs_opened | integer | bugs_closed | integer | support_opened | integer | support_closed | integer | patches_opened | integer | patches_closed | integer | artifacts_opened | integer | artifacts_closed | integer | tasks_opened | integer | tasks_closed | integer | help_requests | integer | cvs_checkouts | integer | cvs_commits | integer | cvs_adds | integer | Table "SCHEMA.stats_rank_oldformula_byday" Column | Type | Modifiers ------------+------------------+---------------------------------------- rankdate | integer | not null group_id | integer | not null ranking | integer | not null percentile | double precision | not null Table "SCHEMA.stats_toplist_week" Column | Type | Modifiers ---------------------+------------------+-------------------- stats_type | integer | not null group_id | integer | not null tally_thisweek | integer | not null rank_thisweek | integer | not null tally_lastweek | integer | percentile_lastweek | double precision | rank_lastweek | integer | percentile_thisweek | double precision | Table "SCHEMA.supported_languages" Column | Type | Modifiers ---------------+--------------+-------------------------------------------------------------- language_id | integer | not null name | text | filename | text | classname | text | language_code | character(2) | Table "SCHEMA.top_group" Column | Type | Modifiers --------------------------+-----------------------+-------------------- group_id | integer | not null group_name | character varying(40) | downloads_all | integer | not null rank_downloads_all | integer | not null rank_downloads_all_old | integer | not null downloads_week | integer | not null rank_downloads_week | integer | not null rank_downloads_week_old | integer | not null userrank | integer | not null rank_userrank | integer | not null rank_userrank_old | integer | not null forumposts_week | integer | not null rank_forumposts_week | integer | not null rank_forumposts_week_old | integer | not null pageviews_proj | integer | not null rank_pageviews_proj | integer | not null rank_pageviews_proj_old | integer | not null Table "SCHEMA.trove_agg" Column | Type | Modifiers -----------------+-----------------------+----------- trove_cat_id | integer | group_id | integer | group_name | character varying(40) | unix_group_name | character varying(30) | status | character(1) | register_time | integer | donate_optin | integer | percentile | double precision | ranking | integer | Table "SCHEMA.trove_agg_counts" Column | Type | Modifiers --------------+---------+----------- trove_cat_id | integer | not null group_count | integer | Table "SCHEMA.trove_agg_minix" Column | Type | Modifiers --------------+---------+----------- trove_cat_id | integer | group_id | integer | Table "SCHEMA.trove_cat" Column | Type | Modifiers --------------+------------------------+---------------------------------------------------- trove_cat_id | integer | not null version | integer | not null parent | integer | not null root_parent | integer | not null shortname | character varying(80) | fullname | character varying(80) | description | character varying(255) | fullpath | text | not null fullpath_ids | text | parent_only | integer | not null people_skill | integer | not null Table "SCHEMA.trove_frontpage" Column | Type | Modifiers --------------+---------+----------- trove_cat_id | integer | not null description | text | not null Table "SCHEMA.trove_group_link" Column | Type | Modifiers -------------------+---------+----------------------------------------------------------- trove_group_id | integer | not null trove_cat_id | integer | not null trove_cat_version | integer | not null group_id | integer | not null trove_cat_root | integer | not null entity_type | integer | not null Table "SCHEMA.trove_treesums" Column | Type | Modifiers -------------------+---------+--------------------------------------------------------------------- trove_treesums_id | integer | not null trove_cat_id | integer | not null subprojects | integer | not null Table "SCHEMA.user_diary" Column | Type | Modifiers -------------+---------+----------------------------------------------------- id | integer | not null user_id | integer | not null date_posted | integer | not null summary | text | details | text | is_public | integer | not null Table "SCHEMA.user_group" Column | Type | Modifiers ------------------+---------------+----------------------------------------------------- user_group_id | integer | not null user_id | integer | not null group_id | integer | not null admin_flags | character(16) | not null forum_flags | integer | not null project_flags | integer | not null doc_flags | integer | not null member_role | integer | not null release_flags | integer | not null artifact_flags | integer | added_by | integer | not null grantcvs | integer | not null grantshell | integer | not null row_modtime | integer | news_flags | integer | not null screenshot_flags | integer | not null snapshot_flags | integer | not null grantsvn | integer | not null Table "SCHEMA.users" Column | Type | Modifiers ----------------------+-----------------------+------------------------------------------------ user_id | integer | not null user_name | text | not null email | text | not null realname | character varying(32) | not null status | character(1) | not null unix_uid | integer | add_date | integer | not null people_resume | text | not null timezone | character varying(64) | language | integer | not null cf_uid | integer | stay_anon | integer | donation_request | text | donate_optin | integer | last_sitestatus_view | integer | row_modtime | integer |