<HTML
><HEAD
><TITLE
>MySQL Bugzilla Database Introduction</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.64
"><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 C. 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"
>C.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
>