1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
|
<HTML
><HEAD
><TITLE
>MySQL Bugzilla Database Introduction</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.61
"><LINK
REL="HOME"
TITLE="The Bugzilla Guide"
HREF="index.html"><LINK
REL="UP"
TITLE="The Bugzilla Database"
HREF="database.html"><LINK
REL="PREVIOUS"
TITLE="Database Schema Chart"
HREF="dbschema.html"><LINK
REL="NEXT"
TITLE="MySQL Permissions & Grant Tables"
HREF="granttables.html"></HEAD
><BODY
CLASS="SECTION"
BGCOLOR="#FFFFFF"
TEXT="#000000"
LINK="#0000FF"
VLINK="#840084"
ALINK="#0000FF"
><DIV
CLASS="NAVHEADER"
><TABLE
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="3"
ALIGN="center"
>The Bugzilla Guide</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="bottom"
><A
HREF="dbschema.html"
>Prev</A
></TD
><TD
WIDTH="80%"
ALIGN="center"
VALIGN="bottom"
>Appendix B. The Bugzilla Database</TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="bottom"
><A
HREF="granttables.html"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECTION"
><H1
CLASS="SECTION"
><A
NAME="DBDOC"
>B.2. MySQL Bugzilla Database Introduction</A
></H1
><P
CLASS="LITERALLAYOUT"
><br>
Contributor(s): Matthew P. Barnson (mbarnson@excitehome.net)<br>
<br>
Last update: May 16, 2000<br>
<br>
Changes:<br>
Version 1.0: Initial public release (May 16, 2000)<br>
<br>
Maintainer: Matthew P. Barnson (mbarnson@excitehome.net)<br>
<br>
<br>
===<br>
Table Of Contents<br>
===<br>
<br>
FOREWORD<br>
INTRODUCTION<br>
THE BASICS<br>
THE TABLES<br>
THE DETAILS<br>
<br>
<br>
<br>
===<br>
FOREWORD<br>
===<br>
<br>
This information comes straight from my life. I was forced to learn how<br>
Bugzilla organizes database because of nitpicky requests from users for tiny<br>
changes in wording, rather than having people re-educate themselves or<br>
figure out how to work our procedures around the tool. It sucks, but it can<br>
and will happen to you, so learn how the schema works and deal with it when it<br>
comes.<br>
<br>
I'm sorry this version is plain text. I can whip this info out a lot faster<br>
if I'm not concerned about complex formatting. I'll get it into sgml for easy<br>
portability as time permits.<br>
<br>
The Bugzilla Database Schema has a home! In addition to availability via CVS<br>
and released versions 2.12 and higher of Bugzilla, you can find the latest &<br>
greatest version of the Bugzilla Database Schema at<br>
http://www.trilobyte.net/barnsons/. This is a living document; please be sure<br>
you are up-to-date with the latest version before mirroring.<br>
<br>
The Bugzilla Database Schema is designed to provide vital information<br>
regarding the structure of the MySQL database. Where appropriate, this<br>
document will refer to URLs rather than including documents in their entirety<br>
to ensure completeness even should this paper become out of date.<br>
<br>
This document is not maintained by Netscape or Netscape employees, so please<br>
do not contact them regarding errors or omissions contained herein. Please<br>
direct all questions, comments, updates, flames, etc. to Matthew P. Barnson<br>
mbarnson@excitehome.net) (barnboy or barnhome on irc.mozilla.org in<br>
#mozwebtools).<br>
<br>
I'm sure I've made some glaring errors or omissions in this paper -- please<br>
email me corrections or post corrections to the<br>
netscape.public.mozilla.webtools newsgroup.<br>
<br>
<br>
<br>
===<br>
INTRODUCTION<br>
===<br>
<br>
<br>
<br>
So, here you are with your brand-new installation of Bugzilla. You've got<br>
MySQL set up, Apache working right, Perl DBI and DBD talking to the database<br>
flawlessly. Maybe you've even entered a few test bugs to make sure email's<br>
working; people seem to be notified of new bugs and changes, and you can<br>
enter and edit bugs to your heart's content. Perhaps you've gone through the<br>
trouble of setting up a gateway for people to submit bugs to your database via<br>
email, have had a few people test it, and received rave reviews from your beta<br>
testers.<br>
<br>
What's the next thing you do? Outline a training strategy for your<br>
development team, of course, and bring them up to speed on the new tool you've<br>
labored over for hours.<br>
<br>
Your first training session starts off very well! You have a captive<br>
audience which seems enraptured by the efficiency embodied in this thing called<br>
"Bugzilla". You are caught up describing the nifty features, how people can<br>
save favorite queries in the database, set them up as headers and footers on<br>
their pages, customize their layouts, generate reports, track status with<br>
greater efficiency than ever before, leap tall buildings with a single bound<br>
and rescue Jane from the clutches of Certain Death!<br>
<br>
But Certain Death speaks up -- a tiny voice, from the dark corners of the<br>
conference room. "I have a concern," the voice hisses from the darkness,<br>
"about the use of the word 'verified'.<br>
<br>
The room, previously filled with happy chatter, lapses into reverential<br>
silence as Certain Death (better known as the Vice President of Software<br>
Engineering) continues. "You see, for two years we've used the word 'verified'<br>
to indicate that a developer or quality assurance engineer has confirmed that,<br>
in fact, a bug is valid. I don't want to lose two years of training to a<br>
new software product. You need to change the bug status of 'verified' to<br>
'approved' as soon as possible. To avoid confusion, of course."<br>
<br>
Oh no! Terror strikes your heart, as you find yourself mumbling "yes, yes, I<br>
don't think that would be a problem," You review the changes with Certain<br>
Death, and continue to jabber on, "no, it's not too big a change. I mean, we<br>
have the source code, right? You know, 'Use the Source, Luke' and all that...<br>
no problem," All the while you quiver inside like a beached jellyfish bubbling,<br>
burbling, and boiling on a hot Jamaican sand dune...<br>
<br>
Thus begins your adventure into the heart of Bugzilla. You've been forced<br>
to learn about non-portable enum() fields, varchar columns, and tinyint<br>
definitions. The Adventure Awaits You!<br>
<br>
<br>
<br>
===<br>
The Basics<br>
===<br>
<br>
If you were like me, at this point you're totally clueless about the<br>
internals of MySQL, and if it weren't for this executive order from the Vice<br>
President you couldn't care less about the difference between a "bigint" and a<br>
"tinyint" entry in MySQL. I'd refer you first to the MySQL documentation,<br>
available at http://www.mysql.com/doc.html, but that's mostly a confusing<br>
morass of high-level database jargon. Here are the basics you need to know<br>
about the database to proceed:<br>
<br>
1. To connect to your database, type "mysql -u root" at the command prompt as<br>
any user. If this works without asking you for a password, SHAME ON YOU! You<br>
should have locked your security down like the README told you to. You can<br>
find details on locking down your database in the Bugzilla FAQ in this<br>
directory (under "Security"), or more robust security generalities in the<br>
MySQL searchable documentation at<br>
http://www.mysql.com/php/manual.php3?section=Privilege_system .<br>
<br>
2. You should now be at a prompt that looks like this:<br>
<br>
mysql><br>
<br>
At the prompt, if "bugs" is the name of your Bugzilla database, type:<br>
<br>
mysql> use bugs;<br>
<br>
(don't forget the ";" at the end of each line, or you'll be kicking yourself<br>
all the way through this documentation)<br>
Young Grasshopper, you are now ready for the unveiling of the Bugzilla<br>
database, in the next section...<br>
<br>
<br>
<br>
===<br>
THE TABLES<br>
===<br>
<br>
Imagine your MySQL database as a series of spreadsheets, and you won't be too<br>
far off. If you use this command:<br>
<br>
mysql> show tables from bugs;<br>
<br>
you'll be able to see all the "spreadsheets" (tables) in your database. Cool,<br>
huh? It's kinda' like a filesystem, only much faster and more robust. Come<br>
on, I'll show you more!<br>
<br>
From the command issued above, you should now have some output that looks<br>
like this:<br>
<br>
+-------------------+<br>
| Tables in bugs |<br>
+-------------------+<br>
| attachments |<br>
| bugs |<br>
| bugs_activity |<br>
| cc |<br>
| components |<br>
| dependencies |<br>
| fielddefs |<br>
| groups |<br>
| keyworddefs |<br>
| keywords |<br>
| logincookies |<br>
| longdescs |<br>
| milestones |<br>
| namedqueries |<br>
| products |<br>
| profiles |<br>
| profiles_activity |<br>
| shadowlog |<br>
| versions |<br>
| votes |<br>
| watch |<br>
+-------------------+<br>
<br>
<br>
If it doesn't look quite the same, that probably means it's time to<br>
update this documentation :)<br>
<br>
Here's an overview of what each table does. Most columns in each table have<br>
descriptive names that make it fairly trivial to figure out their jobs.<br>
<br>
attachments: This table stores all attachments to bugs. It tends to be your<br>
largest table, yet also generally has the fewest entries because file<br>
attachments are so (relatively) large.<br>
<br>
bugs: This is the core of your system. The bugs table stores most of the<br>
current information about a bug, with the exception of the info stored in the<br>
other tables.<br>
<br>
bugs_activity: This stores information regarding what changes are made to bugs<br>
when -- a history file.<br>
<br>
cc: This tiny table simply stores all the CC information for any bug which has<br>
any entries in the CC field of the bug. Note that, like most other tables in<br>
Bugzilla, it does not refer to users by their user names, but by their unique<br>
userid, stored as a primary key in the profiles table.<br>
<br>
components: This stores the programs and components (or products and<br>
components, in newer Bugzilla parlance) for Bugzilla. Curiously, the "program"<br>
(product) field is the full name of the product, rather than some other unique<br>
identifier, like bug_id and user_id are elsewhere in the database.<br>
<br>
dependencies: Stores data about those cool dependency trees.<br>
<br>
fielddefs: A nifty table that defines other tables. For instance, when you<br>
submit a form that changes the value of "AssignedTo" this table allows<br>
translation to the actual field name "assigned_to" for entry into MySQL.<br>
<br>
groups: defines bitmasks for groups. A bitmask is a number that can uniquely<br>
identify group memberships. For instance, say the group that is allowed to<br>
tweak parameters is assigned a value of "1", the group that is allowed to edit<br>
users is assigned a "2", and the group that is allowed to create new groups is<br>
assigned the bitmask of "4". By uniquely combining the group bitmasks (much<br>
like the chmod command in UNIX,) you can identify a user is allowed to tweak<br>
parameters and create groups, but not edit users, by giving him a bitmask of<br>
"5", or a user allowed to edit users and create groups, but not tweak<br>
parameters, by giving him a bitmask of "6" Simple, huh?<br>
If this makes no sense to you, try this at the mysql prompt:<br>
mysql> select * from groups;<br>
You'll see the list, it makes much more sense that way.<br>
<br>
keyworddefs: Definitions of keywords to be used<br>
<br>
keywords: Unlike what you'd think, this table holds which keywords are<br>
associated with which bug id's.<br>
<br>
logincookies: This stores every login cookie ever assigned to you for every<br>
machine you've ever logged into Bugzilla from. Curiously, it never does any<br>
housecleaning -- I see cookies in this file I've not used for months. However,<br>
since Bugzilla never expires your cookie (for convenience' sake), it makes<br>
sense.<br>
<br>
longdescs: The meat of bugzilla -- here is where all user comments are stored!<br>
You've only got 2^24 bytes per comment (it's a mediumtext field), so speak<br>
sparingly -- that's only the amount of space the Old Testament from the Bible<br>
would take (uncompressed, 16 megabytes). Each comment is keyed to the<br>
bug_id to which it's attached, so the order is necessarily chronological, for<br>
comments are played back in the order in which they are received.<br>
<br>
milestones: Interesting that milestones are associated with a specific product<br>
in this table, but Bugzilla does not yet support differing milestones by<br>
product through the standard configuration interfaces.<br>
<br>
namedqueries: This is where everybody stores their "custom queries". Very<br>
cool feature; it beats the tar out of having to bookmark each cool query you<br>
construct.<br>
<br>
products: What products you have, whether new bug entries are allowed for the<br>
product, what milestone you're working toward on that product, votes, etc. It<br>
will be nice when the components table supports these same features, so you<br>
could close a particular component for bug entry without having to close an<br>
entire product...<br>
<br>
profiles: Ahh, so you were wondering where your precious user information was<br>
stored? Here it is! With the passwords in plain text for all to see! (but<br>
sshh... don't tell your users!)<br>
<br>
profiles_activity: Need to know who did what when to who's profile? This'll<br>
tell you, it's a pretty complete history.<br>
<br>
shadowlog: I could be mistaken here, but I believe this table tells you when<br>
your shadow database is updated and what commands were used to update it. We<br>
don't use a shadow database at our site yet, so it's pretty empty for us.<br>
<br>
versions: Version information for every product<br>
<br>
votes: Who voted for what when<br>
<br>
watch: Who (according to userid) is watching who's bugs (according to their<br>
userid).<br>
<br>
<br>
===<br>
THE DETAILS<br>
===<br>
<br>
Ahh, so you're wondering just what to do with the information above? At the<br>
mysql prompt, you can view any information about the columns in a table with<br>
this command (where "table" is the name of the table you wish to view):<br>
<br>
mysql> show columns from table;<br>
<br>
You can also view all the data in a table with this command:<br>
<br>
mysql> select * from table;<br>
<br>
-- note: this is a very bad idea to do on, for instance, the "bugs" table if<br>
you have 50,000 bugs. You'll be sitting there a while until you ctrl-c or<br>
50,000 bugs play across your screen.<br>
<br>
You can limit the display from above a little with the command, where<br>
"column" is the name of the column for which you wish to restrict information:<br>
<br>
mysql> select * from table where (column = "some info");<br>
<br>
-- or the reverse of this<br>
<br>
mysql> select * from table where (column != "some info");<br>
<br>
Let's take our example from the introduction, and assume you need to change<br>
the word "verified" to "approved" in the resolution field. We know from the<br>
above information that the resolution is likely to be stored in the "bugs"<br>
table. Note we'll need to change a little perl code as well as this database<br>
change, but I won't plunge into that in this document. Let's verify the<br>
information is stored in the "bugs" table:<br>
<br>
mysql> show columns from bugs<br>
<br>
(exceedingly long output truncated here)<br>
| bug_status| enum('UNCONFIRMED','NEW','ASSIGNED','REOPENED','RESOLVED','VERIFIED','CLOSED')||MUL | UNCONFIRMED||<br>
<br>
Sorry about that long line. We see from this that the "bug status" column is<br>
an "enum field", which is a MySQL peculiarity where a string type field can<br>
only have certain types of entries. While I think this is very cool, it's not<br>
standard SQL. Anyway, we need to add the possible enum field entry<br>
'APPROVED' by altering the "bugs" table.<br>
<br>
mysql> ALTER table bugs CHANGE bug_status bug_status<br>
-> enum("UNCONFIRMED", "NEW", "ASSIGNED", "REOPENED", "RESOLVED",<br>
-> "VERIFIED", "APPROVED", "CLOSED") not null;<br>
<br>
(note we can take three lines or more -- whatever you put in before the<br>
semicolon is evaluated as a single expression)<br>
<br>
Now if you do this:<br>
<br>
mysql> show columns from bugs;<br>
<br>
you'll see that the bug_status field has an extra "APPROVED" enum that's<br>
available! Cool thing, too, is that this is reflected on your query page as<br>
well -- you can query by the new status. But how's it fit into the existing<br>
scheme of things?<br>
Looks like you need to go back and look for instances of the word "verified"<br>
in the perl code for Bugzilla -- wherever you find "verified", change it to<br>
"approved" and you're in business (make sure that's a case-insensitive search).<br>
Although you can query by the enum field, you can't give something a status<br>
of "APPROVED" until you make the perl changes. Note that this change I<br>
mentioned can also be done by editing checksetup.pl, which automates a lot of<br>
this. But you need to know this stuff anyway, right?<br>
<br>
I hope this database tutorial has been useful for you. If you have comments<br>
to add, questions, concerns, etc. please direct them to<br>
mbarnson@excitehome.net. Please direct flames to /dev/null :) Have a nice<br>
day!<br>
<br>
<br>
<br>
===<br>
LINKS<br>
===<br>
<br>
Great MySQL tutorial site:<br>
http://www.devshed.com/Server_Side/MySQL/<br>
<br>
</P
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="dbschema.html"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="granttables.html"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>Database Schema Chart</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="database.html"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>MySQL Permissions & Grant Tables</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>
|
