diff options
Diffstat (limited to 'docs/html/dbdoc.html')
| -rw-r--r-- | docs/html/dbdoc.html | 573 |
1 files changed, 573 insertions, 0 deletions
diff --git a/docs/html/dbdoc.html b/docs/html/dbdoc.html new file mode 100644 index 000000000..f952c1751 --- /dev/null +++ b/docs/html/dbdoc.html @@ -0,0 +1,573 @@ +<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 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 +> 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. + </P +><P +> 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. + </P +><P +> 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. + </P +><P +> 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! + </P +><P +> 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'. + </P +><P +> 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." + </P +><P +> 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... + </P +><P +> 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! + </P +><DIV +CLASS="SECTION" +><H2 +CLASS="SECTION" +><A +NAME="AEN2089" +>C.2.1. Bugzilla Database Basics</A +></H2 +><P +> 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 <SPAN +CLASS="QUOTE" +>"bigint"</SPAN +> and a + <SPAN +CLASS="QUOTE" +>"tinyint"</SPAN +> entry in MySQL. I recommend you refer + to the MySQL documentation, available at <A +HREF="http://www.mysql.com/doc.html" +TARGET="_top" +>MySQL.com</A +>. Below are the basics you need to know about the Bugzilla database. Check the chart above for more details. + </P +><P +><P +></P +><OL +TYPE="1" +><LI +><P +> To connect to your database: + </P +><P +> <TT +CLASS="PROMPT" +>bash#</TT +><B +CLASS="COMMAND" +>mysql</B +><TT +CLASS="PARAMETER" +><I +>-u root</I +></TT +> + </P +><P +> If this works without asking you for a password, + <EM +>shame on you</EM +>! You should have + locked your security down like the installation + instructions 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 . + </P +></LI +><LI +><P +>You should now be at a prompt that looks like + this:</P +><P +><TT +CLASS="PROMPT" +>mysql></TT +></P +><P +>At the prompt, if <SPAN +CLASS="QUOTE" +>"bugs"</SPAN +> is the name + you chose in the<TT +CLASS="FILENAME" +>localconfig</TT +> file + for your Bugzilla database, type:</P +><P +><TT +CLASS="PROMPT" +>mysql</TT +><B +CLASS="COMMAND" +>use bugs;</B +></P +><DIV +CLASS="NOTE" +><P +></P +><TABLE +CLASS="NOTE" +WIDTH="90%" +BORDER="0" +><TR +><TD +WIDTH="25" +ALIGN="CENTER" +VALIGN="TOP" +><IMG +SRC="../images/note.gif" +HSPACE="5" +ALT="Note"></TD +><TD +ALIGN="LEFT" +VALIGN="TOP" +><P +>Don't forget the <SPAN +CLASS="QUOTE" +>";"</SPAN +> at the end of + each line, or you'll be kicking yourself later.</P +></TD +></TR +></TABLE +></DIV +></LI +></OL +> + </P +><DIV +CLASS="SECTION" +><H3 +CLASS="SECTION" +><A +NAME="AEN2118" +>C.2.1.1. Bugzilla Database Tables</A +></H3 +><P +> Imagine your MySQL database as a series of + spreadsheets, and you won't be too far off. If you use this + command:</P +><P +><TT +CLASS="PROMPT" +>mysql></TT +><B +CLASS="COMMAND" +>show tables from bugs;</B +></P +><P +>you'll be able to see all the + <SPAN +CLASS="QUOTE" +>"spreadsheets"</SPAN +> (tables) in your database. It + is similar to a file system, only faster and more robust for + certain types of operations.</P +><P +>From the command issued above, ou should have some + output that looks like this: + <TABLE +BORDER="0" +BGCOLOR="#E0E0E0" +WIDTH="100%" +><TR +><TD +><FONT +COLOR="#000000" +><PRE +CLASS="PROGRAMLISTING" +>+-------------------+ +| Tables in bugs | ++-------------------+ +| attachments | +| bugs | +| bugs_activity | +| cc | +| components | +| dependencies | +| fielddefs | +| groups | +| keyworddefs | +| keywords | +| logincookies | +| longdescs | +| milestones | +| namedqueries | +| products | +| profiles | +| profiles_activity | +| shadowlog | +| tokens | +| versions | +| votes | +| watch | ++-------------------+ + </PRE +></FONT +></TD +></TR +></TABLE +></P +><P +CLASS="LITERALLAYOUT" +><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 +></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 +>
\ No newline at end of file |