# -*- Mode: perl; indent-tabs-mode: nil -*-## The contents of this file are subject to the Mozilla Public# License Version 1.1 (the "License"); you may not use this file# except in compliance with the License. You may obtain a copy of# the License at http://www.mozilla.org/MPL/## Software distributed under the License is distributed on an "AS# IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or# implied. See the License for the specific language governing# rights and limitations under the License.## The Original Code is the Bugzilla Bug Tracking System.## The Initial Developer of the Original Code is Netscape Communications# Corporation. Portions created by Netscape are# Copyright (C) 1998 Netscape Communications Corporation. All# Rights Reserved.## Contributor(s): Andrew Dunstan <andrew@dunslane.net>,#                 Edward J. Sabol <edwardjsabol@iname.com>#                 Max Kanat-Alexander <mkanat@bugzilla.org>#                 Lance Larsh <lance.larsh@oracle.com>#                 Dennis Melentyev <dennis.melentyev@infopulse.com.ua>#                 Akamai Technologies <bugzilla-dev@akamai.com>package Bugzilla::DB::Schema;############################################################################# Purpose: Object-oriented, DBMS-independent database schema for Bugzilla## This is the base class implementing common methods and abstract schema.############################################################################use strict;use Bugzilla::Error;use Bugzilla::Hook;use Bugzilla::Util;use Bugzilla::Constants;use Hash::Util qw(lock_value unlock_hash lock_keys unlock_keys);use Safe;# Historical, needed for SCHEMA_VERSION = '1.00'use Storable qw(dclone freeze thaw);# New SCHEMA_VERSION (2.00) use thisuse Data::Dumper;=head1 NAMEBugzilla::DB::Schema - Abstract database schema for Bugzilla=head1 SYNOPSIS  # Obtain MySQL database schema.  # Do not do this. Use Bugzilla::DB instead.  use Bugzilla::DB::Schema;  my $mysql_schema = new Bugzilla::DB::Schema('Mysql');  # Recommended way to obtain database schema.  use Bugzilla::DB;  my $dbh = Bugzilla->dbh;  my $schema = $dbh->_bz_schema();  # Get the list of tables in the Bugzilla database.  my @tables = $schema->get_table_list();  # Get the SQL statements need to create the bugs table.  my @statements = $schema->get_table_ddl('bugs');  # Get the database-specific SQL data type used to implement  # the abstract data type INT1.  my $db_specific_type = $schema->sql_type('INT1');=head1 DESCRIPTIONThis module implements an object-oriented, abstract database schema.It should be considered package-private to the Bugzilla::DB module.That means that CGI scripts should never call any function in thismodule directly, but should instead rely on methods provided byBugzilla::DB.=head1 NEW TO SCHEMA.PM?If this is your first time looking at Schema.pm, especially ifyou are making changes to the database, please take a look atL<http://www.bugzilla.org/docs/developer.html#sql-schema> to learnmore about how this integrates into the rest of Bugzilla.=cut#--------------------------------------------------------------------------# Define the Bugzilla abstract database schema and version as constants.=head1 CONSTANTS=over 4=item C<SCHEMA_VERSION>The 'version' of the internal schema structure. This version numberis incremented every time the the fundamental structure of Schemainternals changes.This is NOT changed every time a table or a column is added. This number is incremented only if the internal structures of this Schema would be incompatible with the internal structures of a previous Schema version.In general, unless you are messing around with serializationand deserialization of the schema, you don't need to worry aboutthis constant.=begin privateAn example of the use of the version number:Today, we store all individual columns like this:column_name => { TYPE => 'SOMETYPE', NOTNULL => 1 }Imagine that someday we decide that NOTNULL => 1 is bad, and we wantto change it so that the schema instead uses NULL => 0.But we have a bunch of Bugzilla installations around the world with aserialized schema that has NOTNULL in it! When we deserialize that structure, it just WILL NOT WORK properly inside of our new Schema object.So, immediately after deserializing, we need to go through the hash and change all NOTNULLs to NULLs and so on.We know that we need to do that on deserializing because we know thatversion 1.00 used NOTNULL. Having made the change to NULL, we would nowbe version 1.01.=end private=item C<ABSTRACT_SCHEMA>The abstract database schema structure consists of a hash referencein which each key is the name of a table in the Bugzilla database.The value for each key is a hash reference containing the keysC<FIELDS> and C<INDEXES> which in turn point to array referencescontaining information on the table's fields and indexes. A field hash reference should must contain the key C<TYPE>. Optional fieldkeys include C<PRIMARYKEY>, C<NOTNULL>, and C<DEFAULT>. The C<INDEXES> array reference contains index names and information regarding the index. If the index name points to an array reference,then the index is a regular index and the array contains the indexedcolumns. If the index name points to a hash reference, then the hashmust contain the key C<FIELDS>. It may also contain the key C<TYPE>,which can be used to specify the type of index such as UNIQUE or FULLTEXT.=back=cutuse constant SCHEMA_VERSION  => '2.00';use constant ABSTRACT_SCHEMA => {    # BUG-RELATED TABLES    # ------------------    # General Bug Information    # -----------------------    bugs => {        FIELDS => [            bug_id              => {TYPE => 'MEDIUMSERIAL', NOTNULL => 1,                                    PRIMARYKEY => 1},            assigned_to         => {TYPE => 'INT3', NOTNULL => 1},            bug_file_loc        => {TYPE => 'TEXT'},            bug_severity        => {TYPE => 'varchar(64)', NOTNULL => 1},            bug_status          => {TYPE => 'varchar(64)', NOTNULL => 1},            creation_ts         => {TYPE => 'DATETIME'},            delta_ts            => {TYPE => 'DATETIME', NOTNULL => 1},            short_desc          => {TYPE => 'varchar(255)', NOTNULL => 1},            op_sys              => {TYPE => 'varchar(64)', NOTNULL => 1},            priority            => {TYPE => 'varchar(64)', NOTNULL => 1},            product_id          => {TYPE => 'INT2', NOTNULL => 1},            rep_platform        => {TYPE => 'varchar(64)', NOTNULL => 1},            reporter            => {TYPE => 'INT3', NOTNULL => 1},            version             => {TYPE => 'varchar(64)', NOTNULL => 1},            component_id        => {TYPE => 'INT2', NOTNULL => 1},            resolution          => {TYPE => 'varchar(64)',                                    NOTNULL => 1, DEFAULT => "''"},            target_milestone    => {TYPE => 'varchar(20)',                                    NOTNULL => 1, DEFAULT => "'---'"},            qa_contact          => {TYPE => 'INT3'},            status_whiteboard   => {TYPE => 'MEDIUMTEXT', NOTNULL => 1,                                    DEFAULT => "''"},            votes               => {TYPE => 'INT3', NOTNULL => 1,                                    DEFAULT => '0'},            # Note: keywords field is only a cache; the real data            # comes from the keywords table            keywords            => {TYPE => 'MEDIUMTEXT', NOTNULL => 1,                                    DEFAULT => "''"},            lastdiffed          => {TYPE => 'DATETIME'},            everconfirmed       => {TYPE => 'BOOLEAN', NOTNULL => 1},            reporter_accessible => {TYPE => 'BOOLEAN',                                    NOTNULL => 1, DEFAULT => 'TRUE'},            cclist_accessible   => {TYPE => 'BOOLEAN',                                    NOTNULL => 1, DEFAULT => 'TRUE'},            estimated_time      => {TYPE => 'decimal(5,2)',                                    NOTNULL => 1, DEFAULT => '0'},            remaining_time      => {TYPE => 'decimal(5,2)',                                    NOTNULL => 1, DEFAULT => '0'},            deadline            => {TYPE => 'DATETIME'},            alias               => {TYPE => 'varchar(20)'},        ],        INDEXES => [            bugs_alias_idx            => {FIELDS => ['alias'],                                          TYPE => 'UNIQUE'},            bugs_assigned_to_idx      => ['assigned_to'],            bugs_creation_ts_idx      => ['creation_ts'],            bugs_delta_ts_idx         => ['delta_ts'],            bugs_bug_severity_idx     => ['bug_severity'],            bugs_bug_status_idx       => ['bug_status'],            bugs_op_sys_idx           => ['op_sys'],            bugs_priority_idx         => ['priority'],            bugs_product_id_idx       => ['product_id'],            bugs_reporter_idx         => ['reporter'],            bugs_version_idx          => ['version'],            bugs_component_id_idx     => ['component_id'],            bugs_resolution_idx       => ['resolution'],            bugs_target_milestone_idx => ['target_milestone'],            bugs_qa_contact_idx       => ['qa_contact'],            bugs_votes_idx            => ['votes'],        ],    },    bugs_activity => {        FIELDS => [            bug_id    => {TYPE => 'INT3', NOTNULL => 1},            attach_id => {TYPE => 'INT3'},            who       => {TYPE => 'INT3', NOTNULL => 1},            bug_when  => {TYPE => 'DATETIME', NOTNULL => 1},            fieldid   => {TYPE => 'INT3', NOTNULL => 1},            added     => {TYPE => 'TINYTEXT'},            removed   => {TYPE => 'TINYTEXT'},        ],        INDEXES => [            bugs_activity_bug_id_idx  => ['bug_id'],            bugs_activity_who_idx     => ['who'],            bugs_activity_bug_when_idx => ['bug_when'],            bugs_activity_fieldid_idx => ['fieldid'],        ],    },    cc => {        FIELDS => [            bug_id => {TYPE => 'INT3', NOTNULL => 1},            who    => {TYPE => 'INT3', NOTNULL => 1},        ],        INDEXES => [            cc_bug_id_idx => {FIELDS => [qw(bug_id who)],                              TYPE => 'UNIQUE'},            cc_who_idx    => ['who'],        ],    },    longdescs => {        FIELDS => [            comment_id      => {TYPE => 'MEDIUMSERIAL',  NOTNULL => 1,                                PRIMARYKEY => 1},            bug_id          => {TYPE => 'INT3',  NOTNULL => 1},            who             => {TYPE => 'INT3', NOTNULL => 1},            bug_when        => {TYPE => 'DATETIME', NOTNULL => 1},            work_time       => {TYPE => 'decimal(5,2)', NOTNULL => 1,                                DEFAULT => '0'},            thetext         => {TYPE => 'MEDIUMTEXT', NOTNULL => 1},            isprivate       => {TYPE => 'BOOLEAN', NOTNULL => 1,                                DEFAULT => 'FALSE'},            already_wrapped => {TYPE => 'BOOLEAN', NOTNULL => 1,                                DEFAULT => 'FALSE'},            type            => {TYPE => 'INT2', NOTNULL => 1,                                DEFAULT => '0'},            extra_data      => {TYPE => 'varchar(255)'}        ],        INDEXES => [            longdescs_bug_id_idx   => ['bug_id'],            longdescs_who_idx     => [qw(who bug_id)],            longdescs_bug_when_idx => ['bug_when'],            longdescs_thetext_idx => {FIELDS => ['thetext'],                                      TYPE => 'FULLTEXT'},        ],    },    dependencies => {        FIELDS => [            blocked   => {TYPE => 'INT3', NOTNULL => 1},            dependson => {TYPE => 'INT3', NOTNULL => 1},        ],        INDEXES => [            dependencies_blocked_idx   => ['blocked'],            dependencies_dependson_idx => ['dependson'],        ],    },    votes => {        FIELDS => [            who        => {TYPE => 'INT3', NOTNULL => 1},            bug_id     => {TYPE => 'INT3', NOTNULL => 1},            vote_count => {TYPE => 'INT2', NOTNULL => 1},        ],        INDEXES => [            votes_who_idx    => ['who'],            votes_bug_id_idx => ['bug_id'],        ],    },    attachments => {        FIELDS => [            attach_id    => {TYPE => 'MEDIUMSERIAL', NOTNULL => 1,                             PRIMARYKEY => 1},            bug_id       => {TYPE => 'INT3', NOTNULL => 1},            creation_ts  => {TYPE => 'DATETIME', NOTNULL => 1},            description  => {TYPE => 'MEDIUMTEXT', NOTNULL => 1},            mimetype     => {TYPE => 'MEDIUMTEXT', NOTNULL => 1},            ispatch      => {TYPE => 'BOOLEAN'},            filename     => {TYPE => 'varchar(100)', NOTNULL => 1},            submitter_id => {TYPE => 'INT3', NOTNULL => 1},            isobsolete   => {TYPE => 'BOOLEAN', NOTNULL => 1,                             DEFAULT => 'FALSE'},            isprivate    => {TYPE => 'BOOLEAN', NOTNULL => 1,                             DEFAULT => 'FALSE'},            isurl        => {TYPE => 'BOOLEAN', NOTNULL => 1,                             DEFAULT => 'FALSE'},        ],        INDEXES => [            attachments_bug_id_idx => ['bug_id'],            attachments_creation_ts_idx => ['creation_ts'],            attachments_submitter_id_idx => ['submitter_id', 'bug_id'],        ],    },    attach_data => {        FIELDS => [            id      => {TYPE => 'INT3', NOTNULL => 1,                        PRIMARYKEY => 1},            thedata => {TYPE => 'LONGBLOB', NOTNULL => 1},        ],    },    duplicates => {        FIELDS => [            dupe_of => {TYPE => 'INT3', NOTNULL => 1},            dupe    => {TYPE => 'INT3', NOTNULL => 1,                       PRIMARYKEY => 1},        ],    },    # Keywords    # --------    keyworddefs => {        FIELDS => [            id          => {TYPE => 'SMALLSERIAL', NOTNULL => 1,                            PRIMARYKEY => 1},