-Contributor(s): Matthew P. Barnson (mbarnson@excitehome.net)
-
-Last update: May 16, 2000
-
-Changes:
-Version 1.0: Initial public release (May 16, 2000)
-
-Maintainer: Matthew P. Barnson (mbarnson@excitehome.net)
-
-
-===
-Table Of Contents
-===
-
-FOREWORD
-INTRODUCTION
-THE BASICS
-THE TABLES
-THE DETAILS
-
-
-
-===
-FOREWORD
-===
-
- This information comes straight from my life. I was forced to learn how
-Bugzilla organizes database because of nitpicky requests from users for tiny
-changes in wording, rather than having people re-educate themselves or
-figure out how to work our procedures around the tool. It sucks, but it can
-and will happen to you, so learn how the schema works and deal with it when it
-comes.
-
- I'm sorry this version is plain text. I can whip this info out a lot faster
-if I'm not concerned about complex formatting. I'll get it into sgml for easy
-portability as time permits.
-
- The Bugzilla Database Schema has a home! In addition to availability via CVS
-and released versions 2.12 and higher of Bugzilla, you can find the latest &
-greatest version of the Bugzilla Database Schema at
-http://www.trilobyte.net/barnsons/. This is a living document; please be sure
-you are up-to-date with the latest version before mirroring.
-
- The Bugzilla Database Schema is designed to provide vital information
-regarding the structure of the MySQL database. Where appropriate, this
-document will refer to URLs rather than including documents in their entirety
-to ensure completeness even should this paper become out of date.
-
- This document is not maintained by Netscape or Netscape employees, so please
-do not contact them regarding errors or omissions contained herein. Please
-direct all questions, comments, updates, flames, etc. to Matthew P. Barnson
-mbarnson@excitehome.net) (barnboy or barnhome on irc.mozilla.org in
-#mozwebtools).
-
- I'm sure I've made some glaring errors or omissions in this paper -- please
-email me corrections or post corrections to the
-netscape.public.mozilla.webtools newsgroup.
-
-
-
-===
-INTRODUCTION
-===
-
-
-
- So, here you are with your brand-new installation of Bugzilla. You've got
-MySQL set up, Apache working right, Perl DBI and DBD talking to the database
-flawlessly. Maybe you've even entered a few test bugs to make sure email's
-working; people seem to be notified of new bugs and changes, and you can
-enter and edit bugs to your heart's content. Perhaps you've gone through the
-trouble of setting up a gateway for people to submit bugs to your database via
-email, have had a few people test it, and received rave reviews from your beta
-testers.
-
- What's the next thing you do? Outline a training strategy for your
-development team, of course, and bring them up to speed on the new tool you've
-labored over for hours.
-
- Your first training session starts off very well! You have a captive
-audience which seems enraptured by the efficiency embodied in this thing called
-"Bugzilla". You are caught up describing the nifty features, how people can
-save favorite queries in the database, set them up as headers and footers on
-their pages, customize their layouts, generate reports, track status with
-greater efficiency than ever before, leap tall buildings with a single bound
-and rescue Jane from the clutches of Certain Death!
-
- But Certain Death speaks up -- a tiny voice, from the dark corners of the
-conference room. "I have a concern," the voice hisses from the darkness,
-"about the use of the word 'verified'.
-
- The room, previously filled with happy chatter, lapses into reverential
-silence as Certain Death (better known as the Vice President of Software
-Engineering) continues. "You see, for two years we've used the word 'verified'
-to indicate that a developer or quality assurance engineer has confirmed that,
-in fact, a bug is valid. I don't want to lose two years of training to a
-new software product. You need to change the bug status of 'verified' to
-'approved' as soon as possible. To avoid confusion, of course."
-
- Oh no! Terror strikes your heart, as you find yourself mumbling "yes, yes, I
-don't think that would be a problem," You review the changes with Certain
-Death, and continue to jabber on, "no, it's not too big a change. I mean, we
-have the source code, right? You know, 'Use the Source, Luke' and all that...
-no problem," All the while you quiver inside like a beached jellyfish bubbling,
-burbling, and boiling on a hot Jamaican sand dune...
-
- Thus begins your adventure into the heart of Bugzilla. You've been forced
-to learn about non-portable enum() fields, varchar columns, and tinyint
-definitions. The Adventure Awaits You!
-
-
-
-===
-The Basics
-===
-
- If you were like me, at this point you're totally clueless about the
-internals of MySQL, and if it weren't for this executive order from the Vice
-President you couldn't care less about the difference between a "bigint" and a
-"tinyint" entry in MySQL. I'd refer you first to the MySQL documentation,
-available at http://www.mysql.com/doc.html, but that's mostly a confusing
-morass of high-level database jargon. Here are the basics you need to know
-about the database to proceed:
-
-1. To connect to your database, type "mysql -u root" at the command prompt as
-any user. If this works without asking you for a password, SHAME ON YOU! You
-should have locked your security down like the README told you to. You can
-find details on locking down your database in the Bugzilla FAQ in this
-directory (under "Security"), or more robust security generalities in the
-MySQL searchable documentation at
-http://www.mysql.com/php/manual.php3?section=Privilege_system .
-
-2. You should now be at a prompt that looks like this:
-
-mysql>
-
- At the prompt, if "bugs" is the name of your Bugzilla database, type:
-
-mysql> use bugs;
-
- (don't forget the ";" at the end of each line, or you'll be kicking yourself
-all the way through this documentation)
- Young Grasshopper, you are now ready for the unveiling of the Bugzilla
-database, in the next section...
-
-
-
-===
-THE TABLES
-===
-
- Imagine your MySQL database as a series of spreadsheets, and you won't be too
-far off. If you use this command:
-
-mysql> show tables from bugs;
-
- you'll be able to see all the "spreadsheets" (tables) in your database. Cool,
-huh? It's kinda' like a filesystem, only much faster and more robust. Come
-on, I'll show you more!
-
- From the command issued above, you should now have some output that looks
-like this:
-
-+-------------------+
-| Tables in bugs |
-+-------------------+
-| attachments |
-| bugs |
-| bugs_activity |
-| cc |
-| components |
-| dependencies |
-| fielddefs |
-| groups |
-| keyworddefs |
-| keywords |
-| logincookies |
-| longdescs |
-| milestones |
-| namedqueries |
-| products |
-| profiles |
-| profiles_activity |
-| shadowlog |
-| versions |
-| votes |
-| watch |
-+-------------------+
-
-
- If it doesn't look quite the same, that probably means it's time to
-update this documentation :)
-
- Here's an overview of what each table does. Most columns in each table have
-descriptive names that make it fairly trivial to figure out their jobs.
-
-attachments: This table stores all attachments to bugs. It tends to be your
-largest table, yet also generally has the fewest entries because file
-attachments are so (relatively) large.
-
-bugs: This is the core of your system. The bugs table stores most of the
-current information about a bug, with the exception of the info stored in the
-other tables.
-
-bugs_activity: This stores information regarding what changes are made to bugs
-when -- a history file.
-
-cc: This tiny table simply stores all the CC information for any bug which has
-any entries in the CC field of the bug. Note that, like most other tables in
-Bugzilla, it does not refer to users by their user names, but by their unique
-userid, stored as a primary key in the profiles table.
-
-components: This stores the programs and components (or products and
-components, in newer Bugzilla parlance) for Bugzilla. Curiously, the "program"
-(product) field is the full name of the product, rather than some other unique
-identifier, like bug_id and user_id are elsewhere in the database.
-
-dependencies: Stores data about those cool dependency trees.
-
-fielddefs: A nifty table that defines other tables. For instance, when you
-submit a form that changes the value of "AssignedTo" this table allows
-translation to the actual field name "assigned_to" for entry into MySQL.
-
-groups: defines bitmasks for groups. A bitmask is a number that can uniquely
-identify group memberships. For instance, say the group that is allowed to
-tweak parameters is assigned a value of "1", the group that is allowed to edit
-users is assigned a "2", and the group that is allowed to create new groups is
-assigned the bitmask of "4". By uniquely combining the group bitmasks (much
-like the chmod command in UNIX,) you can identify a user is allowed to tweak
-parameters and create groups, but not edit users, by giving him a bitmask of
-"5", or a user allowed to edit users and create groups, but not tweak
-parameters, by giving him a bitmask of "6" Simple, huh?
- If this makes no sense to you, try this at the mysql prompt:
-mysql> select * from groups;
- You'll see the list, it makes much more sense that way.
-
-keyworddefs: Definitions of keywords to be used
-
-keywords: Unlike what you'd think, this table holds which keywords are
-associated with which bug id's.
-
-logincookies: This stores every login cookie ever assigned to you for every
-machine you've ever logged into Bugzilla from. Curiously, it never does any
-housecleaning -- I see cookies in this file I've not used for months. However,
-since Bugzilla never expires your cookie (for convenience' sake), it makes
-sense.
-
-longdescs: The meat of bugzilla -- here is where all user comments are stored!
-You've only got 2^24 bytes per comment (it's a mediumtext field), so speak
-sparingly -- that's only the amount of space the Old Testament from the Bible
-would take (uncompressed, 16 megabytes). Each comment is keyed to the
-bug_id to which it's attached, so the order is necessarily chronological, for
-comments are played back in the order in which they are received.
-
-milestones: Interesting that milestones are associated with a specific product
-in this table, but Bugzilla does not yet support differing milestones by
-product through the standard configuration interfaces.
-
-namedqueries: This is where everybody stores their "custom queries". Very
-cool feature; it beats the tar out of having to bookmark each cool query you
-construct.
-
-products: What products you have, whether new bug entries are allowed for the
-product, what milestone you're working toward on that product, votes, etc. It
-will be nice when the components table supports these same features, so you
-could close a particular component for bug entry without having to close an
-entire product...
-
-profiles: Ahh, so you were wondering where your precious user information was
-stored? Here it is! With the passwords in plain text for all to see! (but
-sshh... don't tell your users!)
-
-profiles_activity: Need to know who did what when to who's profile? This'll
-tell you, it's a pretty complete history.
-
-shadowlog: I could be mistaken here, but I believe this table tells you when
-your shadow database is updated and what commands were used to update it. We
-don't use a shadow database at our site yet, so it's pretty empty for us.
-
-versions: Version information for every product
-
-votes: Who voted for what when
-
-watch: Who (according to userid) is watching who's bugs (according to their
-userid).
-
-
-===
-THE DETAILS
-===
-
- Ahh, so you're wondering just what to do with the information above? At the
-mysql prompt, you can view any information about the columns in a table with
-this command (where "table" is the name of the table you wish to view):
-
-mysql> show columns from table;
-
- You can also view all the data in a table with this command:
-
-mysql> select * from table;
-
- -- note: this is a very bad idea to do on, for instance, the "bugs" table if
-you have 50,000 bugs. You'll be sitting there a while until you ctrl-c or
-50,000 bugs play across your screen.
-
- You can limit the display from above a little with the command, where
-"column" is the name of the column for which you wish to restrict information:
-
-mysql> select * from table where (column = "some info");
-
- -- or the reverse of this
-
-mysql> select * from table where (column != "some info");
-
- Let's take our example from the introduction, and assume you need to change
-the word "verified" to "approved" in the resolution field. We know from the
-above information that the resolution is likely to be stored in the "bugs"
-table. Note we'll need to change a little perl code as well as this database
-change, but I won't plunge into that in this document. Let's verify the
-information is stored in the "bugs" table:
-
-mysql> show columns from bugs
-
- (exceedingly long output truncated here)
-| bug_status| enum('UNCONFIRMED','NEW','ASSIGNED','REOPENED','RESOLVED','VERIFIED','CLOSED')||MUL | UNCONFIRMED||
-
- Sorry about that long line. We see from this that the "bug status" column is
-an "enum field", which is a MySQL peculiarity where a string type field can
-only have certain types of entries. While I think this is very cool, it's not
-standard SQL. Anyway, we need to add the possible enum field entry
-'APPROVED' by altering the "bugs" table.
-
-mysql> ALTER table bugs CHANGE bug_status bug_status
- -> enum("UNCONFIRMED", "NEW", "ASSIGNED", "REOPENED", "RESOLVED",
- -> "VERIFIED", "APPROVED", "CLOSED") not null;
-
- (note we can take three lines or more -- whatever you put in before the
-semicolon is evaluated as a single expression)
-
-Now if you do this:
-
-mysql> show columns from bugs;
-
- you'll see that the bug_status field has an extra "APPROVED" enum that's
-available! Cool thing, too, is that this is reflected on your query page as
-well -- you can query by the new status. But how's it fit into the existing
-scheme of things?
- Looks like you need to go back and look for instances of the word "verified"
-in the perl code for Bugzilla -- wherever you find "verified", change it to
-"approved" and you're in business (make sure that's a case-insensitive search).
-Although you can query by the enum field, you can't give something a status
-of "APPROVED" until you make the perl changes. Note that this change I
-mentioned can also be done by editing checksetup.pl, which automates a lot of
-this. But you need to know this stuff anyway, right?
-
- I hope this database tutorial has been useful for you. If you have comments
-to add, questions, concerns, etc. please direct them to
-mbarnson@excitehome.net. Please direct flames to /dev/null :) Have a nice
-day!
-
-
-
-===
-LINKS
-===
-
-Great MySQL tutorial site:
-http://www.devshed.com/Server_Side/MySQL/
-
-