From f53d4eacd120cdcc4d64bcc06ac3a72f8277a1f4 Mon Sep 17 00:00:00 2001 From: "mkanat%kerio.com" <> Date: Wed, 9 Mar 2005 13:45:34 +0000 Subject: Bug 284348: Move initial table creation into the Bugzilla::DB modules Patch By Max Kanat-Alexander r=glob, r=Tomas.Kopal, a=justdave --- Bugzilla/DB.pm | 48 ++++- Bugzilla/DB/Mysql.pm | 36 ++++ Bugzilla/DB/Pg.pm | 3 +- checksetup.pl | 598 +-------------------------------------------------- 4 files changed, 84 insertions(+), 601 deletions(-) diff --git a/Bugzilla/DB.pm b/Bugzilla/DB.pm index 1dccf535a..59d19b48b 100644 --- a/Bugzilla/DB.pm +++ b/Bugzilla/DB.pm @@ -48,6 +48,7 @@ Exporter::export_ok_tags('deprecated'); use Bugzilla::Config qw(:DEFAULT :db); use Bugzilla::Util; use Bugzilla::Error; +use Bugzilla::DB::Schema; # All this code is backwards compat fu. As such, its a bit ugly. Note the # circular dependencies on Bugzilla.pm @@ -276,6 +277,31 @@ sub bz_get_field_defs { return(@fields); } +##################################################################### +# Database Setup +##################################################################### + +sub bz_setup_database { + my ($self) = @_; + + # Get a list of the existing tables (if any) in the database + my $table_sth = $self->table_info(undef, undef, undef, "TABLE"); + my @current_tables = + @{$self->selectcol_arrayref($table_sth, { Columns => [3] })}; + + my @desired_tables = $self->_bz_schema->get_table_list(); + + foreach my $table_name (@desired_tables) { + next if grep /^$table_name$/, @current_tables; + print "Creating table $table_name ...\n"; + + my @table_sql = $self->_bz_schema->get_table_ddl($table_name); + foreach my $sql_statement (@table_sql) { + $self->do($sql_statement); + } + } +} + ##################################################################### # Schema Modification Methods ##################################################################### @@ -383,6 +409,14 @@ sub bz_rename_field ($$$) { # Schema Information Methods ##################################################################### +sub _bz_schema { + my ($self) = @_; + return $self->{private_bz_schema} if exists $self->{private_bz_schema}; + $self->{private_bz_schema} = + Bugzilla::DB::Schema->new($self->MODULE_NAME); + return $self->{private_bz_schema}; +} + # XXX - Needs to be made cross-db compatible. sub bz_get_field_def ($$) { my ($self, $table, $field) = @_; @@ -417,7 +451,7 @@ sub bz_get_index_def ($$) { $sth->execute; while (my $ref = $sth->fetchrow_arrayref) { - next if $$ref[2] ne $field; + next if $$ref[4] ne $field; return $ref; } } @@ -449,7 +483,6 @@ sub bz_start_transaction { } else { # Turn AutoCommit off and start a new transaction $self->begin_work(); - $self->{privateprivate_bz_in_transaction} = 1; } } @@ -557,7 +590,7 @@ Bugzilla::DB - Database access routines, using L # Schema Information my @fields = $dbh->bz_get_field_defs(); my @fieldinfo = $dbh->bz_get_field_def($table, $column); - my @indexinfo = $dbh->bz_get_index_def($table, $index); + my @indexinfo = $dbh->bz_get_index_def($table, $field); my $exists = $dbh->bz_table_exists($table); =head1 DESCRIPTION @@ -595,6 +628,11 @@ would be 'MySQL.' You should not depend on this variable to know what type of database you are running on; this is intended merely for displaying to the admin to let them know what DB they're running. +=item C + +The name of the Bugzilla::DB module that we are. For example, for the MySQL +Bugzilla::DB module, this would be "Mysql." For PostgreSQL it would be "Pg." + =head1 CONNECTION A new database handle to the required database can be created using this @@ -898,11 +936,11 @@ These methods return info about the current Bugzilla database schema. Params: $table = the table that you want to count indexes on Returns: The number of indexes on the table. -=item C +=item C Description: Returns information about an index on a table in the database. Params: $table = name of table containing the index (scalar) - $index = name of the index (scalar) + $field = name of a field that the index is on (scalar) Returns: A reference to an array containing information about the index, with the following information in each array place: 0 = name of the table that the index is on diff --git a/Bugzilla/DB/Mysql.pm b/Bugzilla/DB/Mysql.pm index 76cd0966d..14230b188 100644 --- a/Bugzilla/DB/Mysql.pm +++ b/Bugzilla/DB/Mysql.pm @@ -49,6 +49,7 @@ use base qw(Bugzilla::DB); use constant REQUIRED_VERSION => '3.23.41'; use constant PROGRAM_NAME => 'MySQL'; +use constant MODULE_NAME => 'Mysql'; sub new { my ($class, $user, $pass, $host, $dbname, $port, $sock) = @_; @@ -189,4 +190,39 @@ sub bz_rollback_transaction { die("Attempt to rollback transaction on DB without transaction support"); } +##################################################################### +# Database Setup +##################################################################### + +sub bz_setup_database { + my ($self) = @_; + + # Figure out if any existing tables are of type ISAM and convert them + # to type MyISAM if so. ISAM tables are deprecated in MySQL 3.23, + # which Bugzilla now requires, and they don't support more than 16 + # indexes per table, which Bugzilla needs. + my $sth = $self->prepare("SHOW TABLE STATUS"); + $sth->execute; + my @isam_tables = (); + while (my ($name, $type) = $sth->fetchrow_array) { + push(@isam_tables, $name) if $type eq "ISAM"; + } + + if(scalar(@isam_tables)) { + print "One or more of the tables in your existing MySQL database are\n" + . "of type ISAM. ISAM tables are deprecated in MySQL 3.23 and\n" + . "don't support more than 16 indexes per table, which \n" + . "Bugzilla needs.\n Converting your ISAM tables to type" + . " MyISAM:\n\n"; + foreach my $table (@isam_tables) { + print "Converting table $table... "; + $self->do("ALTER TABLE $table TYPE = MYISAM"); + print "done.\n"; + } + print "\nISAM->MyISAM table conversion done.\n\n"; + } + + $self->SUPER::bz_setup_database(); +} + 1; diff --git a/Bugzilla/DB/Pg.pm b/Bugzilla/DB/Pg.pm index 5963f5308..2ec3000d5 100644 --- a/Bugzilla/DB/Pg.pm +++ b/Bugzilla/DB/Pg.pm @@ -47,8 +47,9 @@ use Carp; # This module extends the DB interface via inheritance use base qw(Bugzilla::DB); -use constant REQUIRED_VERSION => '7.02.0000'; +use constant REQUIRED_VERSION => '7.03.0000'; use constant PROGRAM_NAME => 'PostgreSQL'; +use constant MODULE_NAME => 'Pg'; sub new { my ($class, $user, $pass, $host, $dbname, $port) = @_; diff --git a/checksetup.pl b/checksetup.pl index ac60ff63a..7fda5c978 100755 --- a/checksetup.pl +++ b/checksetup.pl @@ -1617,604 +1617,12 @@ if( Param('webdotbase') && Param('webdotbase') !~ /^https?:/ ) { print "\n" unless $silent; - -########################################################################### -# Table definitions -########################################################################### - -# -# The following hash stores all --TABLE-- definitions. This will be used -# to automatically create those tables that don't exist. The code is -# safer than the make*.sh shell scripts used to be, because they won't -# delete existing tables. -# -# If you want to intentionally do this, you can always drop a table and re-run -# checksetup, e.g. like this: -# -# $ mysql bugs -# mysql> drop table votes; -# mysql> exit; -# $ ./checksetup.pl -# -# If you change one of the field definitions, then you must also go to the -# next occurrence of the string --TABLE-- (near the end of this file) and -# add in some conditional code that will automatically update an older -# installation. -# - - -my %table; - -$table{bugs_activity} = - 'bug_id mediumint not null, - attach_id mediumint null, - who mediumint not null, - bug_when datetime not null, - fieldid mediumint not null, - added tinytext, - removed tinytext, - - index (bug_id), - index (who), - index (bug_when), - index (fieldid)'; - - -$table{attachments} = - 'attach_id mediumint not null auto_increment primary key, - bug_id mediumint not null, - creation_ts datetime not null, - description mediumtext not null, - mimetype mediumtext not null, - ispatch tinyint, - filename varchar(100) not null, - thedata longblob not null, - submitter_id mediumint not null, - isobsolete tinyint not null default 0, - isprivate tinyint not null default 0, - - index(bug_id), - index(submitter_id), - index(creation_ts)'; - -# September 2002 myk@mozilla.org: Tables to support status flags, -# which replace attachment statuses and allow users to flag bugs -# or attachments with statuses (review+, approval-, etc.). -# -# "flags" stores one record for each flag on each bug/attachment. -# "flagtypes" defines the types of flags that can be set. -# "flaginclusions" and "flagexclusions" specify the products/components -# a bug/attachment must belong to in order for flags of a given type -# to be set for them. - -$table{flags} = - 'id MEDIUMINT NOT NULL PRIMARY KEY , - type_id SMALLINT NOT NULL , - status CHAR(1) NOT NULL , - - bug_id MEDIUMINT NOT NULL , - attach_id MEDIUMINT NULL , - - creation_date DATETIME NOT NULL , - modification_date DATETIME NULL , - - setter_id MEDIUMINT NULL , - requestee_id MEDIUMINT NULL , - - is_active TINYINT NOT NULL DEFAULT 1, - - INDEX(bug_id, attach_id) , - INDEX(setter_id) , - INDEX(requestee_id) - '; - -$table{flagtypes} = - 'id SMALLINT NOT NULL PRIMARY KEY , - name VARCHAR(50) NOT NULL , - description TEXT NULL , - cc_list VARCHAR(200) NULL , - - target_type CHAR(1) NOT NULL DEFAULT \'b\' , - - is_active TINYINT NOT NULL DEFAULT 1 , - is_requestable TINYINT NOT NULL DEFAULT 0 , - is_requesteeble TINYINT NOT NULL DEFAULT 0 , - is_multiplicable TINYINT NOT NULL DEFAULT 0 , - - sortkey SMALLINT NOT NULL DEFAULT 0 , - grant_group_id MEDIUMINT NULL , - request_group_id MEDIUMINT NULL - '; - -$table{flaginclusions} = - 'type_id SMALLINT NOT NULL , - product_id SMALLINT NULL , - component_id SMALLINT NULL , - - INDEX(type_id, product_id, component_id) - '; - -$table{flagexclusions} = - 'type_id SMALLINT NOT NULL , - product_id SMALLINT NULL , - component_id SMALLINT NULL , - - INDEX(type_id, product_id, component_id) - '; - -$table{bugs} = - 'bug_id mediumint not null auto_increment primary key, - assigned_to mediumint not null, # This is a comment. - bug_file_loc text, - bug_severity varchar(64) not null, - bug_status varchar(64) not null, - creation_ts datetime not null, - delta_ts datetime not null, - short_desc mediumtext not null, - op_sys varchar(64) not null, - priority varchar(64) not null, - product_id smallint not null, - rep_platform varchar(64), - reporter mediumint not null, - version varchar(64) not null, - component_id smallint not null, - resolution varchar(64) not null, - target_milestone varchar(20) not null default "---", - qa_contact mediumint not null, - status_whiteboard mediumtext not null, - votes mediumint not null, - keywords mediumtext not null, ' # Note: keywords field is only a cache; - # the real data comes from the keywords table. - . ' - lastdiffed datetime not null, - everconfirmed tinyint not null, - reporter_accessible tinyint not null default 1, - cclist_accessible tinyint not null default 1, - estimated_time decimal(5,2) not null default 0, - remaining_time decimal(5,2) not null default 0, - deadline datetime, - alias varchar(20), - - index (assigned_to), - index (creation_ts), - index (delta_ts), - index (bug_severity), - index (bug_status), - index (op_sys), - index (priority), - index (product_id), - index (reporter), - index (version), - index (component_id), - index (resolution), - index (target_milestone), - index (qa_contact), - index (votes), - - fulltext (short_desc), - - unique(alias)'; - - -$table{cc} = - 'bug_id mediumint not null, - who mediumint not null, - - index(who), - unique(bug_id,who)'; - -$table{watch} = - 'watcher mediumint not null, - watched mediumint not null, - - index(watched), - unique(watcher,watched)'; - - -$table{longdescs} = - 'bug_id mediumint not null, - who mediumint not null, - bug_when datetime not null, - work_time decimal(5,2) not null default 0, - thetext mediumtext, - isprivate tinyint not null default 0, - already_wrapped tinyint not null default 0, - index(bug_id), - index(who), - index(bug_when), - fulltext (thetext)'; - - -$table{components} = - 'id smallint not null auto_increment primary key, - name varchar(64) not null, - product_id smallint not null, - initialowner mediumint not null, - initialqacontact mediumint not null, - description mediumtext not null, - - unique(product_id,name), - index(name)'; - - -$table{dependencies} = - 'blocked mediumint not null, - dependson mediumint not null, - - index(blocked), - index(dependson)'; - - -# User regexp is which email addresses are put into this group. -# -# 2001-04-10 myk@mozilla.org: -# isactive determines whether or not a group is active. An inactive group -# cannot have bugs added to it. Deactivation is a much milder form of -# deleting a group that allows users to continue to work on bugs in the group -# without enabling them to extend the life of the group by adding bugs to it. -# http://bugzilla.mozilla.org/show_bug.cgi?id=75482 - -$table{groups} = - 'id mediumint not null auto_increment primary key, - name varchar(255) not null, - description text not null, - isbuggroup tinyint not null, - last_changed datetime not null, - userregexp tinytext not null, - isactive tinyint not null default 1, - - unique(name)'; - -$table{logincookies} = - 'cookie mediumint not null auto_increment primary key, - userid mediumint not null, - ipaddr varchar(40) NOT NULL, - lastused DATETIME NOT NULL, - - index(lastused)'; - -$table{classifications} = - 'id smallint not null auto_increment primary key, - name varchar(64) not null, - description mediumtext, - - unique(name)'; - -$table{products} = - 'id smallint not null auto_increment primary key, - name varchar(64) not null, - classification_id smallint not null default 1, - description mediumtext, - milestoneurl tinytext not null, - disallownew tinyint not null, - votesperuser smallint not null, - maxvotesperbug smallint not null default 10000, - votestoconfirm smallint not null, - defaultmilestone varchar(20) not null default "---", - - unique(name)'; - - -$table{profiles} = - 'userid mediumint not null auto_increment primary key, - login_name varchar(255) not null, - cryptpassword varchar(128), - realname varchar(255), - disabledtext mediumtext not null, - mybugslink tinyint not null default 1, - emailflags mediumtext, - refreshed_when datetime not null, - extern_id varchar(64) default null, - unique(login_name)'; - - -$table{profiles_activity} = - 'userid mediumint not null, - who mediumint not null, - profiles_when datetime not null, - fieldid mediumint not null, - oldvalue tinytext, - newvalue tinytext, - - index (userid), - index (profiles_when), - index (fieldid)'; - - -$table{namedqueries} = - 'userid mediumint not null, - name varchar(64) not null, - linkinfooter tinyint not null, - query mediumtext not null, - - unique(userid, name)'; - -$table{fielddefs} = - 'fieldid mediumint not null auto_increment primary key, - name varchar(64) not null, - description mediumtext not null, - mailhead tinyint not null default 0, - sortkey smallint not null, - - unique(name), - index(sortkey)'; - -$table{versions} = - 'value tinytext, - product_id smallint not null'; - - -$table{votes} = - 'who mediumint not null, - bug_id mediumint not null, - vote_count smallint not null, - - index(who), - index(bug_id)'; - -$table{keywords} = - 'bug_id mediumint not null, - keywordid smallint not null, - - index(keywordid), - unique(bug_id,keywordid)'; - -$table{keyworddefs} = - 'id smallint not null primary key, - name varchar(64) not null, - description mediumtext, - - unique(name)'; - - -$table{milestones} = - 'product_id smallint not null, - value varchar(20) not null, - sortkey smallint not null, - unique (product_id, value)'; - -# GRM -$table{duplicates} = - 'dupe_of mediumint(9) not null, - dupe mediumint(9) not null primary key'; - -# 2001-06-21, myk@mozilla.org, bug 77473: -# Stores the tokens users receive when a password or email change is -# requested. Tokens provide an extra measure of security for these changes. -$table{tokens} = - 'userid mediumint not null , - issuedate datetime not null , - token varchar(16) not null primary key , - tokentype varchar(8) not null , - eventdata tinytext null , - - index(userid)'; - -# group membership tables for tracking group and privilege -# -# This table determines the groups that a user belongs to -# directly or due to regexp and which groups can be blessed -# by a user. -# -# grant_type: -# if GRANT_DIRECT - record was explicitly granted -# if GRANT_DERIVED - record was derived from expanding a group hierarchy -# if GRANT_REGEXP - record was created by evaluating a regexp -$table{user_group_map} = - 'user_id mediumint not null, - group_id mediumint not null, - isbless tinyint not null default 0, - grant_type tinyint not null default 0, - - unique(user_id, group_id, grant_type, isbless)'; - -# This table determines which groups are made a member of another -# group, given the ability to bless another group, or given -# visibility to another groups existence and membership -# grant_type: -# if GROUP_MEMBERSHIP - member groups are made members of grantor -# if GROUP_BLESS - member groups may grant membership in grantor -# if GROUP_VISIBLE - member groups may see grantor group -$table{group_group_map} = - 'member_id mediumint not null, - grantor_id mediumint not null, - grant_type tinyint not null default 0, - - unique(member_id, grantor_id, grant_type)'; - -# This table determines which groups a user must be a member of -# in order to see a bug. -$table{bug_group_map} = - 'bug_id mediumint not null, - group_id mediumint not null, - unique(bug_id, group_id), - index(group_id)'; - -# Store quips in the db. -$table{quips} = - 'quipid mediumint not null auto_increment primary key, - userid mediumint null, - quip text not null, - approved tinyint(1) not null default 1'; - -$table{group_control_map} = - 'group_id mediumint not null, - product_id mediumint not null, - entry tinyint not null, - membercontrol tinyint not null, - othercontrol tinyint not null, - canedit tinyint not null, - - unique(product_id, group_id), - index(group_id)'; - -# Generic charting over time of arbitrary queries. -# Queries are disabled when frequency == 0. -$table{series} = - 'series_id mediumint auto_increment primary key, - creator mediumint not null, - category smallint not null, - subcategory smallint not null, - name varchar(64) not null, - frequency smallint not null, - last_viewed datetime default null, - query mediumtext not null, - public tinyint(1) not null default 0, - - index(creator), - unique(creator, category, subcategory, name)'; - -$table{series_data} = - 'series_id mediumint not null, - series_date datetime not null, - series_value mediumint not null, - - unique(series_id, series_date)'; - -$table{category_group_map} = - 'category_id smallint not null, - group_id mediumint not null, - - unique(category_id, group_id)'; - -$table{series_categories} = - 'id smallint auto_increment primary key, - name varchar(64) not null, - - unique(name)'; - - - -# whine system - -$table{whine_queries} = - 'id mediumint auto_increment primary key, - eventid mediumint not null, - query_name varchar(64) not null default \'\', - sortkey smallint not null default 0, - onemailperbug tinyint not null default 0, - title varchar(128) not null, - - index(eventid)'; - -$table{whine_schedules} = - 'id mediumint auto_increment primary key, - eventid mediumint not null, - run_day varchar(32), - run_time varchar(32), - run_next datetime, - mailto mediumint not null, - mailto_type smallint not null default 0, - - index(run_next), - index(eventid)'; - -$table{whine_events} = - 'id mediumint auto_increment primary key, - owner_userid mediumint not null, - subject varchar(128), - body mediumtext'; - -# mkanat@kerio.com - bug 17453 -# Below are all the old enumerations converted to tables -$table{bug_status} = - 'id smallint auto_increment primary key, - value varchar(64) not null, - sortkey smallint not null default 0, - isactive tinyint(1) not null default 1, - - unique(value), - index(sortkey, value)'; - -$table{rep_platform} = - 'id smallint auto_increment primary key, - value varchar(64) not null, - sortkey smallint not null default 0, - isactive tinyint(1) not null default 1, - - unique(value), - index(sortkey, value)'; - -$table{resolution} = - 'id smallint auto_increment primary key, - value varchar(64) not null, - sortkey smallint not null default 0, - isactive tinyint(1) not null default 1, - - unique(value), - index(sortkey, value)'; - -$table{op_sys} = - 'id smallint auto_increment primary key, - value varchar(64) not null, - sortkey smallint not null default 0, - isactive tinyint(1) not null default 1, - - unique(value), - index(sortkey, value)'; - -$table{bug_severity} = - 'id smallint auto_increment primary key, - value varchar(64) not null, - sortkey smallint not null default 0, - isactive tinyint(1) not null default 1, - - unique(value), - index(sortkey, value)'; - -$table{priority} = - 'id smallint auto_increment primary key, - value varchar(64) not null, - sortkey smallint not null default 0, - isactive tinyint(1) not null default 1, - - unique(value), - index(sortkey, value)'; - - ########################################################################### # Create tables ########################################################################### -# Figure out if any existing tables are of type ISAM and convert them -# to type MyISAM if so. ISAM tables are deprecated in MySQL 3.23, -# which Bugzilla now requires, and they don't support more than 16 -# indexes per table, which Bugzilla needs. -my $sth = $dbh->prepare("SHOW TABLE STATUS FROM `$::db_name`"); -$sth->execute; -my @isam_tables = (); -while (my ($name, $type) = $sth->fetchrow_array) { - push(@isam_tables, $name) if $type eq "ISAM"; -} - -if(scalar(@isam_tables)) { - print "One or more of the tables in your existing MySQL database are of\n" . - "type ISAM. ISAM tables are deprecated in MySQL 3.23 and don't \n " . - "support more than 16 indexes per table, which Bugzilla needs. \n" . - "Converting your ISAM tables to type MyISAM:\n\n"; - foreach my $table (@isam_tables) { - print "Converting table $table... "; - $dbh->do("ALTER TABLE $table TYPE = MYISAM"); - print "done.\n"; - } - print "\nISAM->MyISAM table conversion done.\n\n"; -} - - -# Get a list of the existing tables (if any) in the database -$sth = $dbh->table_info(undef, undef, undef, "TABLE"); -my @tables = @{$dbh->selectcol_arrayref($sth, { Columns => [3] })}; -#print 'Tables: ', join " ", @tables, "\n"; - -# go through our %table hash and create missing tables -while (my ($tabname, $fielddef) = each %table) { - next if grep /^$tabname$/, @tables; - print "Creating table $tabname ...\n"; - - $dbh->do("CREATE TABLE $tabname (\n$fielddef\n) TYPE = MYISAM") - or die "Could not create table '$tabname'. " . - "Please check your '$my_db_driver' access.\n"; -} +# Note: --TABLE-- definitions are now in Bugzilla::DB::Schema. +$dbh->bz_setup_database(); ########################################################################### # Populate groups table @@ -2416,7 +1824,7 @@ PopulateEnumTable('resolution', @resolutions); ########################################################################### # Create initial test product if there are no products present. ########################################################################### -$sth = $dbh->prepare("SELECT description FROM products"); +my $sth = $dbh->prepare("SELECT description FROM products"); $sth->execute; unless ($sth->rows) { print "Creating initial dummy product 'TestProduct' ...\n"; -- cgit v1.2.3-24-g4f1b