Skip to content
infiro edited this page Jul 30, 2012 · 10 revisions

The database schema is created when the com2pgsql project has been run. Below is a list of tables and information regarding the records that are stored in each along with their description.

##Items This table contains information of a communication Item. A communication Item is a basic structure of communication network. Everything is an Item. A Thread is an Item, an Email is an Item and an Issue is an item etc.

Column Type Modifiers
p_id integer not null
item_date timestamp with time zone not null
item_id integer not null auto increment
body text
title text
type varchar(255)

###Notes:

Indexes:
    "items_pkey" PRIMARY KEY, btree (item_id)
Foreign-key constraints:
    "items_p_id_fkey" FOREIGN KEY (p_id) REFERENCES people(p_id) ON DELETE CASCADE
Referenced by:
    TABLE "attachments" CONSTRAINT "attachments_item_id_fkey" FOREIGN KEY (item_id) REFERENCES items(item_id)
    TABLE "dependencies" CONSTRAINT "dependencies_depends_on_id_fkey" FOREIGN KEY (depends_on_id) REFERENCES items(item_id)
    TABLE "dependencies" CONSTRAINT "dependencies_item_id_fkey" FOREIGN KEY (item_id) REFERENCES items(item_id)
    TABLE "issues" CONSTRAINT "issues_item_id_fkey" FOREIGN KEY (item_id) REFERENCES items(item_id)
    TABLE "links" CONSTRAINT "links_item_id_fkey" FOREIGN KEY (item_id) REFERENCES items(item_id) ON DELETE CASCADE
    TABLE "silents" CONSTRAINT "silents_item_id_fkey" FOREIGN KEY (item_id) REFERENCES items(item_id)
    TABLE "threads" CONSTRAINT "threads_item_id_fkey" FOREIGN KEY (item_id) REFERENCES items(item_id) ON DELETE CASCADE
    TABLE "threads" CONSTRAINT "threads_thread_id_fkey" FOREIGN KEY (thread_id) REFERENCES items(item_id) ON DELETE CASCADE

##Threads This table contains one-to-many relationship between a Thread and other communication Items. A Thread is a communication Item, which can be an Issue or initial Email.

Column Type Modifiers
item_id integer not null
thread_id integer not null default auto increment

###Notes

Indexes:
     "threads_pkey" PRIMARY KEY, btree (item_id, thread_id)
Foreign-key constraints:
     "threads_item_id_fkey" FOREIGN KEY (item_id) REFERENCES items(item_id) ON DELETE CASCADE
     "threads_thread_id_fkey" FOREIGN KEY (thread_id) REFERENCES items(item_id) ON DELETE CASCADE

##Issues The table contains Issue information. An Issue is a Bug Thread which can link to one to many communication items.

Column Type Modifiers
item_id integer not null
status varchar(255)
assignee_id integer
creation_ts time stamp with timezone created time
last_modified_ts time stamp with timezone last modified time
title varchar(512)
description text
creator_id integer
keywords varchar(512) classification of the issue
issue_num varchar(64) unique identifier of an issue in the tracking system

###Notes

Foreign-key constraints:
    "issues_assignee_id_fkey" FOREIGN KEY (assignee_id) REFERENCES people(p_id)
    "issues_creator_id_fkey" FOREIGN KEY (creator_id) REFERENCES people(p_id)
    "issues_item_id_fkey" FOREIGN KEY (item_id) REFERENCES items(item_id)

##People This table contains the information of a person, name and email. Email is considered as unique identifier for a person when linking to technical network.

Column Type Modifiers
p_id integer not null default auto increment
name varchar(255) not null
email varchar(255) not null

###Notes

Indexes:
    "people_pkey" PRIMARY KEY, btree (p_id)
Referenced by:
    TABLE "issues" CONSTRAINT "issues_assignee_id_fkey" FOREIGN KEY (assignee_id) REFERENCES people(p_id)
    TABLE "issues" CONSTRAINT "issues_creator_id_fkey" FOREIGN KEY (creator_id) REFERENCES people(p_id)
    TABLE "items" CONSTRAINT "items_p_id_fkey" FOREIGN KEY (p_id) REFERENCES people(p_id) ON DELETE CASCADE
    TABLE "silents" CONSTRAINT "silents_p_id_fkey" FOREIGN KEY (p_id) REFERENCES people(p_id)

##Silents This table contains list of subscribers/followers of an item. These followers are not necessarily participate in the item discussion but they are aware of what's going on.

Column Type Modifiers
item_id integer not null
p_id integer not null

###Notes Not used for now

Foreign-key constraints:
    "silents_item_id_fkey" FOREIGN KEY (item_id) REFERENCES items(item_id)
    "silents_p_id_fkey" FOREIGN KEY (p_id) REFERENCES people(p_id)

##Links This table contains the result of the com2pgsql. It contains links between communication items and commits. This is a many-to-many relationship.

Column Type Modifiers
item_id integer not null
commit_id varchar(255) not null
confidence real not null

###Notes

Indexes:
    "links_pkey" PRIMARY KEY, btree (item_id, commit_id)
Foreign-key constraints:
    "links_item_id_fkey" FOREIGN KEY (item_id) REFERENCES items(item_id) ON DELETE CASCADE

##Attachments This table contains the attachments of an item. Item-Attachment is one to many relationship.

Column Type Modifiers
item_id integer not null
title varchar(512)
body text

###Notes Not used for now

Foreign-key constraints:
    "attachments_item_id_fkey" FOREIGN KEY (item_id) REFERENCES items(item_id)

##Dependencies This table contains the link between items. An item can be linked to other items in a parent-child relationship or sibling relationship.

Column Type Modifiers
item_id integer not null
depends_on_id integer not null

###Notes Not used for now

Foreign-key constraints:
     "dependencies_depends_on_id_fkey" FOREIGN KEY (depends_on_id) REFERENCES items(item_id)
     "dependencies_item_id_fkey" FOREIGN KEY (item_id) REFERENCES items(item_id)

##Frequency This table contains the link between an item and a commit id. This is many-to-many relationship.

Column Type Modifiers
item_id integer
commit_id varchar(255)

###Notes Not used for now

Clone this wiki locally