Annotation of ETA/mysql-tables.batch, revision 1.12
1.1 frystyk 1: #
2: # Table layout for ETA tables in mysql
3: #
4: # Authors: Henrik Frystyk Nielsen, Eric Prud'hommeaux
5: #
1.12 ! hugo 6: # $Id: mysql-tables.batch,v 1.11 1999/06/08 21:03:32 frystyk Exp $
1.1 frystyk 7:
8: # TABLE: uris
9: # -----------
10: # The uris table is a mapping from any URI into an internal ID which we
11: # use as reference in other tables. There is exactly one entry for each
12: # uri referenced in the database.
13: #
14: # If the URI is relative then this means that it is a reference to a
15: # report (see the reports table). If the URI is absolute then it is an
16: # external reference.
17: #
18: # We optimize for the case that they are shorter than 255 chars but
19: # allow for longer strings if need be.
20:
21: create table uris (
22: id int unsigned not null auto_increment, # unique ID for all documents we know
23: uri varchar(255) binary not null, # For all URIs
24: luri blob, # Extra for URIs of more than 255 chars
1.2 eric 25:
1.1 frystyk 26: primary key (id),
27: unique (uri),
28: index uri_idx (uri(32))
29: );
30:
31: # TABLE: schemas
32: # --------------
33: # Entries which refer to %<something>, for example %categories, refer
34: # to a configurable enumeration specified by the type of forum we are
35: # describing. In order to make the database selfdescribing, we create
36: # a table consiting of the names of the definitions used to configure
37: # the database. This is essentially the name space of the issues!
38:
39: create table schemas (
40: id int unsigned not null auto_increment, # unique ID for all schemas we know
41: categories int unsigned not null, # URI of definition of categories
42: priorities int unsigned not null, # URI of definition of priorities
43: states int unsigned not null, # URI of definition of states
1.3 eric 44: event_status int unsigned not null, # URI of definition of states
1.7 frystyk 45: contact int unsigned not null, # Contact creating schema, see contacts table
46: description varchar(255), # Description of fora using these schemas
47:
48: primary key (id)
49: );
50:
51: # TABLE: forums
52: # -------------
53: # Issues are organized in fora - each forum has has a specific schema which
54: # describes the categories, priorities, etc. for that forum. Fora can be
55: # organized in hierarchies but need not inherit anything from their parent
56: # forum.
57:
58: create table forums (
59: id int unsigned not null auto_increment, # unique ID for all fora we know
60: path varchar(255) binary, # path of this forum
61: schema int unsigned not null, # schema used in this forum, see schemas table
62: contact int unsigned not null, # Contact creating forum, see contacts table
63: description varchar(255), # Description of this forum
1.2 eric 64:
1.1 frystyk 65: primary key (id)
66: );
67:
68: # TABLE: issues
69: # -------------
70: # An issue is a document, bug, request, annotation, discussion item, or anything
71: # else that we are dealing with in any particular instance of the
72: # database.
73: #
74: # We maintain one entry for each document. A document is uniquely
75: # identified by the id which in fact is a relative uri registered in the
76: # uris table. External documents are characterized by having absolute URIs
77: # in the uri table.
78:
79: create table issues (
1.7 frystyk 80: id int unsigned not null auto_increment, # unique key, see uris table
1.1 frystyk 81: schema int unsigned not null, # unique schema id, see schemas table
1.7 frystyk 82: forum int unsigned not null, # forum this issue is added to, see forums table
1.1 frystyk 83: category int unsigned not null, # Category number, see %categories
84: priority int unsigned not null, # Priority level, see %priorities
85: created datetime, # Date created
86: state int unsigned not null, # The current state of the report, see %states
87:
88: creator int unsigned not null, # Person or group, see contacts table
1.7 frystyk 89: summary varchar(255), # Creator's problem summary
1.1 frystyk 90: description text, # Creator's problem description
91:
92: owner int unsigned not null, # Person or pool, see contacts table
93: o_result text, # Owner's description of the fix for custumer consumption
94: o_notes text, # Technical notes about the fix for internal consumption
95: o_source int unsigned, # Fixers guess as to how problem was introduced, see %sources
96: o_effort int unsigned, # Guestimate of time required to fix in minutes
1.2 eric 97:
1.1 frystyk 98: primary key (id)
99: );
100:
101: # TABLE: links
102: # ------------
103: # Here we maintain relationships between resources identified by
104: # uris taken from the URI table. This means that it includes reports
105: # which are identifed by a relative URI.
106: #
107: # Link relationships can be of any type (string) including "subsumed
108: # by", "originating discussion", "follow-up", "solution", etc.
109:
110: create table links (
1.2 eric 111: id int unsigned not null auto_increment, # unique key for all links
1.1 frystyk 112: source int unsigned not null, # Source document from uris table
113: destination int unsigned not null, # Destination document from uris table
114: rel char(64) not null, # link relationship
1.9 frystyk 115: rel_type int unsigned not null, # URI (from uris table) defining the relationship schema, or
116: forum int unsigned not null, # forum (if any) this issue is added to, see forums table
1.7 frystyk 117: contact int unsigned not null, # Contact creating link, see contacts table
118: comment varchar(255), # Comments
1.2 eric 119:
120: primary key (id),
1.9 frystyk 121: unique (source,destination,rel,rel_type,forum)
1.1 frystyk 122: );
123:
124: # TABLE: events
125: # -------------
126: # We maintain a list of events of past and future events. Events can
127: # trigger emails to be sent and various other notifications. We add both
128: # where we come from and where we want to go in order to check that the
129: # expected state hasn't changed underneath us. If so then we fail gracefully.
130:
131: create table events (
1.2 eric 132: id int unsigned not null auto_increment, # unique key for all events
1.1 frystyk 133: issue int unsigned not null, # Document id from uris table
134: from_state int unsigned not null, # current state to jump from (check against cur state in doc)
135: to_state int unsigned not null, # next state to jump to
136: when datetime, # When state changes or changed
137: status int unsigned not null, # status of event, see %event_status
1.7 frystyk 138: contact int unsigned not null, # Contact creating event, see contacts table
139: comment varchar(255), # Comments
1.2 eric 140:
141: primary key (id)
1.1 frystyk 142: );
143:
144: # TABLE: contacts
145: # ---------------
146: # Contacts is a table of all the people or groups having created a document
147: # or fixes problems. Contacts can create issues as well as fix them.
148:
149: create table contacts (
1.2 eric 150: id int unsigned not null auto_increment, # unique key for all people and groups we know
1.5 eric 151: username char(64) binary not null, # Username for access to editing the database
152: password char(32) binary, # Password for access to editing the database
1.1 frystyk 153: realname char(64) not null, # Their full real name
154: location char(64), # Physical location, office etc
155: email int unsigned not null, # email from uris table used for notifications
156: homepage int unsigned not null, # Homepage address from uris table
1.7 frystyk 157: comment varchar(255), # detailed description - not indexed
1.12 ! hugo 158: modemFriendly int(1) default '0', # Modem friendliness (specifies the amount of information displayed)
1.2 eric 159:
1.5 eric 160: primary key (id),
161: unique (username)
1.1 frystyk 162: );
163:
164: # TABLE: skills
165: # -------------
166: # Here we associate contacts with the skills needed to "own" a document. Owning a
167: # document means tbat that group or person knows (and is responsible for) how to carry it
168: # forward to a/the final state. A group or person can have multiple skills - each
1.10 frystyk 169: # skill has its own entry.
1.1 frystyk 170:
171: create table skills (
1.2 eric 172: id int unsigned not null auto_increment, # unique key to allow editing by stupid clients (access)
1.4 frystyk 173: schema int unsigned not null, # unique schema id, see schemas table
1.8 frystyk 174: forum int unsigned not null, # forum this issue is added to, see forums table
1.1 frystyk 175: contact int unsigned not null, # Contact id, see contacts table
1.10 frystyk 176: issue int unsigned not null, # Issue number, see issues table. '0' means all issues in that forum
1.2 eric 177: category int unsigned not null, # Category number, see %categories. '0' means all
178: priority int unsigned not null, # Severity level, see %priority. '0' means all
179: state int unsigned not null, # The current state of the report, see %states. '0' means all
180:
181: primary key (id),
1.11 frystyk 182: unique (contact,forum,issue,category,priority,state)
1.1 frystyk 183: );
184:
Webmaster
![[BACK]](/cvsweb/icons/back.gif){kind=icon}
Return to mysql-tables.batch CVS log ![[TXT]](/cvsweb/icons/text.gif){kind=icon}
![[DIR]](/cvsweb/icons/dir.gif){kind=icon}
Up to [Public] / ETA