diff options
Diffstat (limited to 'docs/sgml/database.sgml')
| -rw-r--r-- | docs/sgml/database.sgml | 468 |
1 files changed, 200 insertions, 268 deletions
diff --git a/docs/sgml/database.sgml b/docs/sgml/database.sgml index 4cba46a48..d758259da 100644 --- a/docs/sgml/database.sgml +++ b/docs/sgml/database.sgml @@ -123,10 +123,10 @@ ! 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 - .</para> + "Security"), or more robust security generalities in the + <ulink url="http://www.mysql.com/php/manual.php3?section=Privilege_system">MySQL + searchable documentation</ulink>. + </para> </listitem> <listitem> @@ -150,13 +150,6 @@ <command>use bugs;</command> </para> - <note> - <para>Don't forget the - <quote>;</quote> - - at the end of each line, or you'll be kicking yourself - later.</para> - </note> </listitem> </orderedlist> </para> @@ -169,273 +162,212 @@ <para> <prompt>mysql></prompt> - <command>show tables from bugs;</command> </para> - <para>you'll be able to see all the + <para>you'll be able to see the names of all the <quote>spreadsheets</quote> + (tables) in your database.</para> + + <para>From the command issued above, ou should have some + output that looks like this: +<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 | ++-------------------+ +</programlisting> +</para> + +<literallayout> + 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). - (tables) in your database. It is similar to a file system, only - faster and more robust for certain types of operations.</para> - - <para>From the command issued above, ou should have some output that - looks like this: - <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 | +-------------------+</programlisting> - </para> - <literallayout>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/</literallayout> +=== +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? + </literallayout> </section> </section> </section> - <section id="granttables"> - <title>MySQL Permissions & Grant Tables</title> - - <note> - <para>The following portion of documentation comes from my answer to an - old discussion of Keystone, a cool product that does trouble-ticket - tracking for IT departments. I wrote this post to the Keystone support - group regarding MySQL grant table permissions, and how to use them - effectively. It is badly in need of updating, as I believe MySQL has - added a field or two to the grant tables since this time, but it serves - as a decent introduction and troubleshooting document for grant table - issues. I used Keynote to track my troubles until I discovered - Bugzilla, which gave me a whole new set of troubles to work on : ) - Although it is of limited use, it still has SOME use, thus it's still - included.</para> - - <para>Please note, however, that I was a relatively new user to MySQL - at the time. Some of my suggestions, particularly in how to set up - security, showed a terrible lack of security-related database - experience.</para> - </note> - - <literallayout>From matt_barnson@singletrac.com Wed Jul 7 09:00:07 1999 - Date: Mon, 1 Mar 1999 21:37:04 -0700 From: Matthew Barnson - matt_barnson@singletrac.com To: keystone-users@homeport.org Subject: - [keystone-users] Grant Tables FAQ [The following text is in the - "iso-8859-1" character set] [Your display is set for the "US-ASCII" - character set] [Some characters may be displayed incorrectly] Maybe we - can include this rambling message in the Keystone FAQ? It gets asked a - lot, and the only option current listed in the FAQ is - "--skip-grant-tables". Really, you can't go wrong by reading section 6 of - the MySQL manual, at http://www.mysql.com/Manual/manual.html. I am sure - their description is better than mine. MySQL runs fine without - permissions set up correctly if you run the mysql daemon with the - "--skip-grant-tables" option. Running this way denies access to nobody. - Unfortunately, unless you've got yourself firewalled it also opens the - potential for abuse if someone knows you're running it. Additionally, the - default permissions for MySQL allow anyone at localhost access to the - database if the database name begins with "test_" or is named "test" - (i.e. "test_keystone"). You can change the name of your database in the - keystone.conf file ($sys_dbname). This is the way I am doing it for some - of my databases, and it works fine. The methods described below assume - you're running MySQL on the same box as your webserver, and that you - don't mind if your $sys_dbuser for Keystone has superuser access. See - near the bottom of this message for a description of what each field - does. Method #1: 1. cd /var/lib #location where you'll want to run - /usr/bin/mysql_install_db shell script from to get it to work. 2. ln -s - mysql data # soft links the "mysql" directory to "data", which is what - mysql_install_db expects. Alternately, you can edit mysql_install_db and - change all the "./data" references to "./mysql". 3. Edit - /usr/bin/mysql_install_db with your favorite text editor (vi, emacs, jot, - pico, etc.) A) Copy the "INSERT INTO db VALUES - ('%','test\_%','','Y','Y','Y','Y','Y','Y');" and paste it immediately - after itself. Chage the 'test\_%' value to 'keystone', or the value of - $sys_dbname in keystone.conf. B) If you are running your keystone - database with any user, you'll need to copy the "INSERT INTO user VALUES - ('localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');" line - after itself and change 'root' to the name of the keystone database user - ($sys_dbuser) in keystone.conf. # adds entries to the script to create - grant tables for specific hosts and users. The user you set up has - super-user access ($sys_dbuser) -- you may or may not want this. The - layout of mysql_install_db is really very uncomplicated. 4. - /usr/bin/mysqladmin shutdown # ya gotta shut it down before you can - reinstall the grant tables! 5. rm -i /var/lib/mysql/mysql/*.IS?' and - answer 'Y' to the deletion questions. # nuke your current grant tables. - This WILL NOT delete any other databases than your grant tables. 6. - /usr/bin/mysql_install_db # run the script you just edited to install - your new grant tables. 7. mysqladmin -u root password (new_password) # - change the root MySQL password, or else anyone on localhost can login to - MySQL as root and make changes. You can skip this step if you want - keystone to connect as root with no password. 8. mysqladmin -u - (webserver_user_name) password (new_password) # change the password of - the $sys_dbuser. Note that you will need to change the password in the - keystone.conf file as well in $sys_dbpasswd, and if your permissions are - set up incorrectly anybody can type the URL to your keystone.conf file - and get the password. Not that this will help them much if your - permissions are set to @localhost. Method #2: easier, but a pain - reproducing if you have to delete your grant tables. This is the - "recommended" method for altering grant tables in MySQL. I don't use it - because I like the other way :) shell> mysql --user=root keystone - mysql> GRANT - SELECT,INSERT,UPDATE,DELETE,INDEX,ALTER,CREATE,DROP,RELOAD,SHUTDOWN,PROCESS, - FILE, ON keystone.* TO <$sys_dbuser name>@localhost IDENTIFIED BY - '(password)' WITH GRANT OPTION; OR mysql> GRANT ALL PRIVILEGES ON - keystone.* TO <$sys_dbuser name>@localhost IDENTIFIED BY - '(password)' WITH GRANT OPTION; # this grants the required permissions to - the keystone ($sys_dbuser) account defined in keystone.conf. However, if - you are runnning many different MySQL-based apps, as we are, it's - generally better to edit the mysql_install_db script to be able to - quickly reproduce your permissions structure again. Note that the FILE - privelege and WITH GRANT OPTION may not be in your best interest to - include. GRANT TABLE FIELDS EXPLANATION: Quick syntax summary: "%" in - MySQL is a wildcard. I.E., if you are defining your DB table and in the - 'host' field and enter '%', that means that any host can access that - database. Of course, that host must also have a valid db user in order to - do anything useful. 'db'=name of database. In our case, it should be - "keystone". "user" should be your "$sys_dbuser" defined in keystone.conf. - Note that you CANNOT add or change a password by using the "INSERT INTO - db (X)" command -- you must change it with the mysql -u command as - defined above. Passwords are stored encrypted in the MySQL database, and - if you try to enter it directly into the table they will not match. - TABLE: USER. Everything after "password" is a privelege granted (Y/N). - This table controls individual user global access rights. - 'host','user','password','select','insert','update','delete','index','alter' - ,'create','drop','grant','reload','shutdown','process','file' TABLE: DB. - This controls access of USERS to databases. - 'host','db','user','select','insert','update','delete','index','alter','crea - te','drop','grant' TABLE: HOST. This controls which HOSTS are allowed - what global access rights. Note that the HOST table, USER table, and DB - table are very closely connected -- if an authorized USER attempts an SQL - request from an unauthorized HOST, she's denied. If a request from an - authorized HOST is not an authorized USER, it is denied. If a globally - authorized USER does not have rights to a certain DB, she's denied. Get - the picture? - 'host','db','select','insert','update','delete','index','alter','create','dr - op','grant' You should now have a working knowledge of MySQL grant - tables. If there is anything I've left out of this answer that you feel - is pertinent, or if my instructions don't work for you, please let me - know and I'll re-post this letter again, corrected. I threw it together - one night out of exasperation for all the newbies who don't know squat - about MySQL yet, so it is almost guaranteed to have errors. Once again, - you can't go wrong by reading section 6 of the MySQL manual. It is more - detailed than I! http://www.mysql.com/Manual/manual.html.</literallayout> - </section> </appendix> <!-- Keep this comment at the end of the file |