dbdoc.html

<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
>&#13;      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
>&#13;      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
>&#13;      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
>&#13;      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
>&#13;      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
>&#13;      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
>&#13;      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
>&#13;      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="AEN2340"
>C.2.1. Bugzilla Database Basics</A
></H2
><P
>&#13;	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
>&#13;	      To connect to your database:
	    </P
><P
>&#13;	      <TT
CLASS="prompt"
>bash#</TT
><B
CLASS="command"
>mysql</B
><TT
CLASS="parameter"
><I
>-u root</I
></TT
>
	    </P
><P
>&#13;	      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&#62;</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="100%"
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="AEN2369"
>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&#62;</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"
>&#13;+-------------------+
| 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>
<br>
&nbsp;&nbsp;Here's&nbsp;an&nbsp;overview&nbsp;of&nbsp;what&nbsp;each&nbsp;table&nbsp;does.&nbsp;&nbsp;Most&nbsp;columns&nbsp;in&nbsp;each&nbsp;table&nbsp;have<br>
descriptive&nbsp;names&nbsp;that&nbsp;make&nbsp;it&nbsp;fairly&nbsp;trivial&nbsp;to&nbsp;figure&nbsp;out&nbsp;their&nbsp;jobs.<br>
<br>
attachments:&nbsp;This&nbsp;table&nbsp;stores&nbsp;all&nbsp;attachments&nbsp;to&nbsp;bugs.&nbsp;&nbsp;It&nbsp;tends&nbsp;to&nbsp;be&nbsp;your<br>
largest&nbsp;table,&nbsp;yet&nbsp;also&nbsp;generally&nbsp;has&nbsp;the&nbsp;fewest&nbsp;entries&nbsp;because&nbsp;file<br>
attachments&nbsp;are&nbsp;so&nbsp;(relatively)&nbsp;large.<br>
<br>
bugs:&nbsp;&nbsp;This&nbsp;is&nbsp;the&nbsp;core&nbsp;of&nbsp;your&nbsp;system.&nbsp;&nbsp;The&nbsp;bugs&nbsp;table&nbsp;stores&nbsp;most&nbsp;of&nbsp;the<br>
current&nbsp;information&nbsp;about&nbsp;a&nbsp;bug,&nbsp;with&nbsp;the&nbsp;exception&nbsp;of&nbsp;the&nbsp;info&nbsp;stored&nbsp;in&nbsp;the<br>
other&nbsp;tables.<br>
<br>
bugs_activity:&nbsp;&nbsp;This&nbsp;stores&nbsp;information&nbsp;regarding&nbsp;what&nbsp;changes&nbsp;are&nbsp;made&nbsp;to&nbsp;bugs<br>
when&nbsp;--&nbsp;a&nbsp;history&nbsp;file.<br>
<br>
cc:&nbsp;&nbsp;This&nbsp;tiny&nbsp;table&nbsp;simply&nbsp;stores&nbsp;all&nbsp;the&nbsp;CC&nbsp;information&nbsp;for&nbsp;any&nbsp;bug&nbsp;which&nbsp;has<br>
any&nbsp;entries&nbsp;in&nbsp;the&nbsp;CC&nbsp;field&nbsp;of&nbsp;the&nbsp;bug.&nbsp;&nbsp;Note&nbsp;that,&nbsp;like&nbsp;most&nbsp;other&nbsp;tables&nbsp;in<br>
Bugzilla,&nbsp;it&nbsp;does&nbsp;not&nbsp;refer&nbsp;to&nbsp;users&nbsp;by&nbsp;their&nbsp;user&nbsp;names,&nbsp;but&nbsp;by&nbsp;their&nbsp;unique<br>
userid,&nbsp;stored&nbsp;as&nbsp;a&nbsp;primary&nbsp;key&nbsp;in&nbsp;the&nbsp;profiles&nbsp;table.<br>
<br>
components:&nbsp;This&nbsp;stores&nbsp;the&nbsp;programs&nbsp;and&nbsp;components&nbsp;(or&nbsp;products&nbsp;and<br>
components,&nbsp;in&nbsp;newer&nbsp;Bugzilla&nbsp;parlance)&nbsp;for&nbsp;Bugzilla.&nbsp;&nbsp;Curiously,&nbsp;the&nbsp;"program"<br>
(product)&nbsp;field&nbsp;is&nbsp;the&nbsp;full&nbsp;name&nbsp;of&nbsp;the&nbsp;product,&nbsp;rather&nbsp;than&nbsp;some&nbsp;other&nbsp;unique<br>
identifier,&nbsp;like&nbsp;bug_id&nbsp;and&nbsp;user_id&nbsp;are&nbsp;elsewhere&nbsp;in&nbsp;the&nbsp;database.<br>
<br>
dependencies:&nbsp;Stores&nbsp;data&nbsp;about&nbsp;those&nbsp;cool&nbsp;dependency&nbsp;trees.<br>
<br>
fielddefs:&nbsp;&nbsp;A&nbsp;nifty&nbsp;table&nbsp;that&nbsp;defines&nbsp;other&nbsp;tables.&nbsp;&nbsp;For&nbsp;instance,&nbsp;when&nbsp;you<br>
submit&nbsp;a&nbsp;form&nbsp;that&nbsp;changes&nbsp;the&nbsp;value&nbsp;of&nbsp;"AssignedTo"&nbsp;this&nbsp;table&nbsp;allows<br>
translation&nbsp;to&nbsp;the&nbsp;actual&nbsp;field&nbsp;name&nbsp;"assigned_to"&nbsp;for&nbsp;entry&nbsp;into&nbsp;MySQL.<br>
<br>
groups:&nbsp;&nbsp;defines&nbsp;bitmasks&nbsp;for&nbsp;groups.&nbsp;&nbsp;A&nbsp;bitmask&nbsp;is&nbsp;a&nbsp;number&nbsp;that&nbsp;can&nbsp;uniquely<br>
identify&nbsp;group&nbsp;memberships.&nbsp;&nbsp;For&nbsp;instance,&nbsp;say&nbsp;the&nbsp;group&nbsp;that&nbsp;is&nbsp;allowed&nbsp;to<br>
tweak&nbsp;parameters&nbsp;is&nbsp;assigned&nbsp;a&nbsp;value&nbsp;of&nbsp;"1",&nbsp;the&nbsp;group&nbsp;that&nbsp;is&nbsp;allowed&nbsp;to&nbsp;edit<br>
users&nbsp;is&nbsp;assigned&nbsp;a&nbsp;"2",&nbsp;and&nbsp;the&nbsp;group&nbsp;that&nbsp;is&nbsp;allowed&nbsp;to&nbsp;create&nbsp;new&nbsp;groups&nbsp;is<br>
assigned&nbsp;the&nbsp;bitmask&nbsp;of&nbsp;"4".&nbsp;&nbsp;By&nbsp;uniquely&nbsp;combining&nbsp;the&nbsp;group&nbsp;bitmasks&nbsp;(much<br>
like&nbsp;the&nbsp;chmod&nbsp;command&nbsp;in&nbsp;UNIX,)&nbsp;you&nbsp;can&nbsp;identify&nbsp;a&nbsp;user&nbsp;is&nbsp;allowed&nbsp;to&nbsp;tweak<br>
parameters&nbsp;and&nbsp;create&nbsp;groups,&nbsp;but&nbsp;not&nbsp;edit&nbsp;users,&nbsp;by&nbsp;giving&nbsp;him&nbsp;a&nbsp;bitmask&nbsp;of<br>
"5",&nbsp;or&nbsp;a&nbsp;user&nbsp;allowed&nbsp;to&nbsp;edit&nbsp;users&nbsp;and&nbsp;create&nbsp;groups,&nbsp;but&nbsp;not&nbsp;tweak<br>
parameters,&nbsp;by&nbsp;giving&nbsp;him&nbsp;a&nbsp;bitmask&nbsp;of&nbsp;"6"&nbsp;Simple,&nbsp;huh?<br>
&nbsp;&nbsp;If&nbsp;this&nbsp;makes&nbsp;no&nbsp;sense&nbsp;to&nbsp;you,&nbsp;try&nbsp;this&nbsp;at&nbsp;the&nbsp;mysql&nbsp;prompt:<br>
mysql&#62;&nbsp;select&nbsp;*&nbsp;from&nbsp;groups;<br>
&nbsp;&nbsp;You'll&nbsp;see&nbsp;the&nbsp;list,&nbsp;it&nbsp;makes&nbsp;much&nbsp;more&nbsp;sense&nbsp;that&nbsp;way.<br>
<br>
keyworddefs:&nbsp;&nbsp;Definitions&nbsp;of&nbsp;keywords&nbsp;to&nbsp;be&nbsp;used<br>
<br>
keywords:&nbsp;Unlike&nbsp;what&nbsp;you'd&nbsp;think,&nbsp;this&nbsp;table&nbsp;holds&nbsp;which&nbsp;keywords&nbsp;are<br>
associated&nbsp;with&nbsp;which&nbsp;bug&nbsp;id's.<br>
<br>
logincookies:&nbsp;This&nbsp;stores&nbsp;every&nbsp;login&nbsp;cookie&nbsp;ever&nbsp;assigned&nbsp;to&nbsp;you&nbsp;for&nbsp;every<br>
machine&nbsp;you've&nbsp;ever&nbsp;logged&nbsp;into&nbsp;Bugzilla&nbsp;from.&nbsp;&nbsp;Curiously,&nbsp;it&nbsp;never&nbsp;does&nbsp;any<br>
housecleaning&nbsp;--&nbsp;I&nbsp;see&nbsp;cookies&nbsp;in&nbsp;this&nbsp;file&nbsp;I've&nbsp;not&nbsp;used&nbsp;for&nbsp;months.&nbsp;&nbsp;However,<br>
since&nbsp;Bugzilla&nbsp;never&nbsp;expires&nbsp;your&nbsp;cookie&nbsp;(for&nbsp;convenience'&nbsp;sake),&nbsp;it&nbsp;makes<br>
sense.<br>
<br>
longdescs:&nbsp;&nbsp;The&nbsp;meat&nbsp;of&nbsp;bugzilla&nbsp;--&nbsp;here&nbsp;is&nbsp;where&nbsp;all&nbsp;user&nbsp;comments&nbsp;are&nbsp;stored!<br>
You've&nbsp;only&nbsp;got&nbsp;2^24&nbsp;bytes&nbsp;per&nbsp;comment&nbsp;(it's&nbsp;a&nbsp;mediumtext&nbsp;field),&nbsp;so&nbsp;speak<br>
sparingly&nbsp;--&nbsp;that's&nbsp;only&nbsp;the&nbsp;amount&nbsp;of&nbsp;space&nbsp;the&nbsp;Old&nbsp;Testament&nbsp;from&nbsp;the&nbsp;Bible<br>
would&nbsp;take&nbsp;(uncompressed,&nbsp;16&nbsp;megabytes).&nbsp;&nbsp;Each&nbsp;comment&nbsp;is&nbsp;keyed&nbsp;to&nbsp;the<br>
bug_id&nbsp;to&nbsp;which&nbsp;it's&nbsp;attached,&nbsp;so&nbsp;the&nbsp;order&nbsp;is&nbsp;necessarily&nbsp;chronological,&nbsp;for<br>
comments&nbsp;are&nbsp;played&nbsp;back&nbsp;in&nbsp;the&nbsp;order&nbsp;in&nbsp;which&nbsp;they&nbsp;are&nbsp;received.<br>
<br>
milestones:&nbsp;&nbsp;Interesting&nbsp;that&nbsp;milestones&nbsp;are&nbsp;associated&nbsp;with&nbsp;a&nbsp;specific&nbsp;product<br>
in&nbsp;this&nbsp;table,&nbsp;but&nbsp;Bugzilla&nbsp;does&nbsp;not&nbsp;yet&nbsp;support&nbsp;differing&nbsp;milestones&nbsp;by<br>
product&nbsp;through&nbsp;the&nbsp;standard&nbsp;configuration&nbsp;interfaces.<br>
<br>
namedqueries:&nbsp;&nbsp;This&nbsp;is&nbsp;where&nbsp;everybody&nbsp;stores&nbsp;their&nbsp;"custom&nbsp;queries".&nbsp;&nbsp;Very<br>
cool&nbsp;feature;&nbsp;it&nbsp;beats&nbsp;the&nbsp;tar&nbsp;out&nbsp;of&nbsp;having&nbsp;to&nbsp;bookmark&nbsp;each&nbsp;cool&nbsp;query&nbsp;you<br>
construct.<br>
<br>
products:&nbsp;&nbsp;What&nbsp;products&nbsp;you&nbsp;have,&nbsp;whether&nbsp;new&nbsp;bug&nbsp;entries&nbsp;are&nbsp;allowed&nbsp;for&nbsp;the<br>
product,&nbsp;what&nbsp;milestone&nbsp;you're&nbsp;working&nbsp;toward&nbsp;on&nbsp;that&nbsp;product,&nbsp;votes,&nbsp;etc.&nbsp;&nbsp;It<br>
will&nbsp;be&nbsp;nice&nbsp;when&nbsp;the&nbsp;components&nbsp;table&nbsp;supports&nbsp;these&nbsp;same&nbsp;features,&nbsp;so&nbsp;you<br>
could&nbsp;close&nbsp;a&nbsp;particular&nbsp;component&nbsp;for&nbsp;bug&nbsp;entry&nbsp;without&nbsp;having&nbsp;to&nbsp;close&nbsp;an<br>
entire&nbsp;product...<br>
<br>
profiles:&nbsp;&nbsp;Ahh,&nbsp;so&nbsp;you&nbsp;were&nbsp;wondering&nbsp;where&nbsp;your&nbsp;precious&nbsp;user&nbsp;information&nbsp;was<br>
stored?&nbsp;&nbsp;Here&nbsp;it&nbsp;is!&nbsp;&nbsp;With&nbsp;the&nbsp;passwords&nbsp;in&nbsp;plain&nbsp;text&nbsp;for&nbsp;all&nbsp;to&nbsp;see!&nbsp;(but<br>
sshh...&nbsp;don't&nbsp;tell&nbsp;your&nbsp;users!)<br>
<br>
profiles_activity:&nbsp;&nbsp;Need&nbsp;to&nbsp;know&nbsp;who&nbsp;did&nbsp;what&nbsp;when&nbsp;to&nbsp;who's&nbsp;profile?&nbsp;&nbsp;This'll<br>
tell&nbsp;you,&nbsp;it's&nbsp;a&nbsp;pretty&nbsp;complete&nbsp;history.<br>
<br>
shadowlog:&nbsp;&nbsp;I&nbsp;could&nbsp;be&nbsp;mistaken&nbsp;here,&nbsp;but&nbsp;I&nbsp;believe&nbsp;this&nbsp;table&nbsp;tells&nbsp;you&nbsp;when<br>
your&nbsp;shadow&nbsp;database&nbsp;is&nbsp;updated&nbsp;and&nbsp;what&nbsp;commands&nbsp;were&nbsp;used&nbsp;to&nbsp;update&nbsp;it.&nbsp;&nbsp;We<br>
don't&nbsp;use&nbsp;a&nbsp;shadow&nbsp;database&nbsp;at&nbsp;our&nbsp;site&nbsp;yet,&nbsp;so&nbsp;it's&nbsp;pretty&nbsp;empty&nbsp;for&nbsp;us.<br>
<br>
versions:&nbsp;&nbsp;Version&nbsp;information&nbsp;for&nbsp;every&nbsp;product<br>
<br>
votes:&nbsp;&nbsp;Who&nbsp;voted&nbsp;for&nbsp;what&nbsp;when<br>
<br>
watch:&nbsp;&nbsp;Who&nbsp;(according&nbsp;to&nbsp;userid)&nbsp;is&nbsp;watching&nbsp;who's&nbsp;bugs&nbsp;(according&nbsp;to&nbsp;their<br>
userid).<br>
<br>
<br>
===<br>
THE&nbsp;DETAILS<br>
===<br>
<br>
&nbsp;&nbsp;Ahh,&nbsp;so&nbsp;you're&nbsp;wondering&nbsp;just&nbsp;what&nbsp;to&nbsp;do&nbsp;with&nbsp;the&nbsp;information&nbsp;above?&nbsp;&nbsp;At&nbsp;the<br>
mysql&nbsp;prompt,&nbsp;you&nbsp;can&nbsp;view&nbsp;any&nbsp;information&nbsp;about&nbsp;the&nbsp;columns&nbsp;in&nbsp;a&nbsp;table&nbsp;with<br>
this&nbsp;command&nbsp;(where&nbsp;"table"&nbsp;is&nbsp;the&nbsp;name&nbsp;of&nbsp;the&nbsp;table&nbsp;you&nbsp;wish&nbsp;to&nbsp;view):<br>
<br>
mysql&#62;&nbsp;show&nbsp;columns&nbsp;from&nbsp;table;<br>
<br>
&nbsp;&nbsp;You&nbsp;can&nbsp;also&nbsp;view&nbsp;all&nbsp;the&nbsp;data&nbsp;in&nbsp;a&nbsp;table&nbsp;with&nbsp;this&nbsp;command:<br>
<br>
mysql&#62;&nbsp;select&nbsp;*&nbsp;from&nbsp;table;<br>
<br>
&nbsp;&nbsp;--&nbsp;note:&nbsp;this&nbsp;is&nbsp;a&nbsp;very&nbsp;bad&nbsp;idea&nbsp;to&nbsp;do&nbsp;on,&nbsp;for&nbsp;instance,&nbsp;the&nbsp;"bugs"&nbsp;table&nbsp;if<br>
you&nbsp;have&nbsp;50,000&nbsp;bugs.&nbsp;&nbsp;You'll&nbsp;be&nbsp;sitting&nbsp;there&nbsp;a&nbsp;while&nbsp;until&nbsp;you&nbsp;ctrl-c&nbsp;or<br>
50,000&nbsp;bugs&nbsp;play&nbsp;across&nbsp;your&nbsp;screen.<br>
<br>
&nbsp;&nbsp;You&nbsp;can&nbsp;limit&nbsp;the&nbsp;display&nbsp;from&nbsp;above&nbsp;a&nbsp;little&nbsp;with&nbsp;the&nbsp;command,&nbsp;where<br>
"column"&nbsp;is&nbsp;the&nbsp;name&nbsp;of&nbsp;the&nbsp;column&nbsp;for&nbsp;which&nbsp;you&nbsp;wish&nbsp;to&nbsp;restrict&nbsp;information:<br>
<br>
mysql&#62;&nbsp;select&nbsp;*&nbsp;from&nbsp;table&nbsp;where&nbsp;(column&nbsp;=&nbsp;"some&nbsp;info");<br>
<br>
&nbsp;&nbsp;--&nbsp;or&nbsp;the&nbsp;reverse&nbsp;of&nbsp;this<br>
<br>
mysql&#62;&nbsp;select&nbsp;*&nbsp;from&nbsp;table&nbsp;where&nbsp;(column&nbsp;!=&nbsp;"some&nbsp;info");<br>
<br>
&nbsp;&nbsp;Let's&nbsp;take&nbsp;our&nbsp;example&nbsp;from&nbsp;the&nbsp;introduction,&nbsp;and&nbsp;assume&nbsp;you&nbsp;need&nbsp;to&nbsp;change<br>
the&nbsp;word&nbsp;"verified"&nbsp;to&nbsp;"approved"&nbsp;in&nbsp;the&nbsp;resolution&nbsp;field.&nbsp;&nbsp;We&nbsp;know&nbsp;from&nbsp;the<br>
above&nbsp;information&nbsp;that&nbsp;the&nbsp;resolution&nbsp;is&nbsp;likely&nbsp;to&nbsp;be&nbsp;stored&nbsp;in&nbsp;the&nbsp;"bugs"<br>
table.&nbsp;Note&nbsp;we'll&nbsp;need&nbsp;to&nbsp;change&nbsp;a&nbsp;little&nbsp;perl&nbsp;code&nbsp;as&nbsp;well&nbsp;as&nbsp;this&nbsp;database<br>
change,&nbsp;but&nbsp;I&nbsp;won't&nbsp;plunge&nbsp;into&nbsp;that&nbsp;in&nbsp;this&nbsp;document.&nbsp;Let's&nbsp;verify&nbsp;the<br>
information&nbsp;is&nbsp;stored&nbsp;in&nbsp;the&nbsp;"bugs"&nbsp;table:<br>
<br>
mysql&#62;&nbsp;show&nbsp;columns&nbsp;from&nbsp;bugs<br>
<br>
&nbsp;&nbsp;(exceedingly&nbsp;long&nbsp;output&nbsp;truncated&nbsp;here)<br>
|&nbsp;bug_status|&nbsp;enum('UNCONFIRMED','NEW','ASSIGNED','REOPENED','RESOLVED','VERIFIED','CLOSED')||MUL&nbsp;|&nbsp;UNCONFIRMED||<br>
<br>
&nbsp;&nbsp;Sorry&nbsp;about&nbsp;that&nbsp;long&nbsp;line.&nbsp;&nbsp;We&nbsp;see&nbsp;from&nbsp;this&nbsp;that&nbsp;the&nbsp;"bug&nbsp;status"&nbsp;column&nbsp;is<br>
an&nbsp;"enum&nbsp;field",&nbsp;which&nbsp;is&nbsp;a&nbsp;MySQL&nbsp;peculiarity&nbsp;where&nbsp;a&nbsp;string&nbsp;type&nbsp;field&nbsp;can<br>
only&nbsp;have&nbsp;certain&nbsp;types&nbsp;of&nbsp;entries.&nbsp;&nbsp;While&nbsp;I&nbsp;think&nbsp;this&nbsp;is&nbsp;very&nbsp;cool,&nbsp;it's&nbsp;not<br>
standard&nbsp;SQL.&nbsp;&nbsp;Anyway,&nbsp;we&nbsp;need&nbsp;to&nbsp;add&nbsp;the&nbsp;possible&nbsp;enum&nbsp;field&nbsp;entry<br>
'APPROVED'&nbsp;by&nbsp;altering&nbsp;the&nbsp;"bugs"&nbsp;table.<br>
<br>
mysql&#62;&nbsp;ALTER&nbsp;table&nbsp;bugs&nbsp;CHANGE&nbsp;bug_status&nbsp;bug_status<br>
&nbsp;&nbsp;&nbsp;&nbsp;-&#62;&nbsp;enum("UNCONFIRMED",&nbsp;"NEW",&nbsp;"ASSIGNED",&nbsp;"REOPENED",&nbsp;"RESOLVED",<br>
&nbsp;&nbsp;&nbsp;&nbsp;-&#62;&nbsp;"VERIFIED",&nbsp;"APPROVED",&nbsp;"CLOSED")&nbsp;not&nbsp;null;<br>
<br>
&nbsp;&nbsp;&nbsp;&nbsp;(note&nbsp;we&nbsp;can&nbsp;take&nbsp;three&nbsp;lines&nbsp;or&nbsp;more&nbsp;--&nbsp;whatever&nbsp;you&nbsp;put&nbsp;in&nbsp;before&nbsp;the<br>
semicolon&nbsp;is&nbsp;evaluated&nbsp;as&nbsp;a&nbsp;single&nbsp;expression)<br>
<br>
Now&nbsp;if&nbsp;you&nbsp;do&nbsp;this:<br>
<br>
mysql&#62;&nbsp;show&nbsp;columns&nbsp;from&nbsp;bugs;<br>
<br>
&nbsp;&nbsp;you'll&nbsp;see&nbsp;that&nbsp;the&nbsp;bug_status&nbsp;field&nbsp;has&nbsp;an&nbsp;extra&nbsp;"APPROVED"&nbsp;enum&nbsp;that's<br>
available!&nbsp;&nbsp;Cool&nbsp;thing,&nbsp;too,&nbsp;is&nbsp;that&nbsp;this&nbsp;is&nbsp;reflected&nbsp;on&nbsp;your&nbsp;query&nbsp;page&nbsp;as<br>
well&nbsp;--&nbsp;you&nbsp;can&nbsp;query&nbsp;by&nbsp;the&nbsp;new&nbsp;status.&nbsp;&nbsp;But&nbsp;how's&nbsp;it&nbsp;fit&nbsp;into&nbsp;the&nbsp;existing<br>
scheme&nbsp;of&nbsp;things?<br>
&nbsp;&nbsp;Looks&nbsp;like&nbsp;you&nbsp;need&nbsp;to&nbsp;go&nbsp;back&nbsp;and&nbsp;look&nbsp;for&nbsp;instances&nbsp;of&nbsp;the&nbsp;word&nbsp;"verified"<br>
in&nbsp;the&nbsp;perl&nbsp;code&nbsp;for&nbsp;Bugzilla&nbsp;--&nbsp;wherever&nbsp;you&nbsp;find&nbsp;"verified",&nbsp;change&nbsp;it&nbsp;to<br>
"approved"&nbsp;and&nbsp;you're&nbsp;in&nbsp;business&nbsp;(make&nbsp;sure&nbsp;that's&nbsp;a&nbsp;case-insensitive&nbsp;search).<br>
Although&nbsp;you&nbsp;can&nbsp;query&nbsp;by&nbsp;the&nbsp;enum&nbsp;field,&nbsp;you&nbsp;can't&nbsp;give&nbsp;something&nbsp;a&nbsp;status<br>
of&nbsp;"APPROVED"&nbsp;until&nbsp;you&nbsp;make&nbsp;the&nbsp;perl&nbsp;changes.&nbsp;&nbsp;&nbsp;Note&nbsp;that&nbsp;this&nbsp;change&nbsp;I<br>
mentioned&nbsp;can&nbsp;also&nbsp;be&nbsp;done&nbsp;by&nbsp;editing&nbsp;checksetup.pl,&nbsp;which&nbsp;automates&nbsp;a&nbsp;lot&nbsp;of<br>
this.&nbsp;&nbsp;But&nbsp;you&nbsp;need&nbsp;to&nbsp;know&nbsp;this&nbsp;stuff&nbsp;anyway,&nbsp;right?<br>
<br>
&nbsp;&nbsp;I&nbsp;hope&nbsp;this&nbsp;database&nbsp;tutorial&nbsp;has&nbsp;been&nbsp;useful&nbsp;for&nbsp;you.&nbsp;&nbsp;If&nbsp;you&nbsp;have&nbsp;comments<br>
to&nbsp;add,&nbsp;questions,&nbsp;concerns,&nbsp;etc.&nbsp;please&nbsp;direct&nbsp;them&nbsp;to<br>
mbarnson@excitehome.net.&nbsp;&nbsp;Please&nbsp;direct&nbsp;flames&nbsp;to&nbsp;/dev/null&nbsp;:)&nbsp;&nbsp;Have&nbsp;a&nbsp;nice<br>
day!<br>
<br>
<br>
<br>
===<br>
LINKS<br>
===<br>
<br>
Great&nbsp;MySQL&nbsp;tutorial&nbsp;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 &#38; Grant Tables</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>