1
# -*- Mode: perl; indent-tabs-mode: nil -*-
3
# The contents of this file are subject to the Mozilla Public
4
# License Version 1.1 (the "License"); you may not use this file
5
# except in compliance with the License. You may obtain a copy of
6
# the License at http://www.mozilla.org/MPL/
8
# Software distributed under the License is distributed on an "AS
9
# IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or
10
# implied. See the License for the specific language governing
11
# rights and limitations under the License.
13
# The Original Code is the Bugzilla Bug Tracking System.
15
# The Initial Developer of the Original Code is Netscape Communications
16
# Corporation. Portions created by Netscape are
17
# Copyright (C) 1998 Netscape Communications Corporation. All
20
# Contributor(s): Andrew Dunstan <andrew@dunslane.net>,
21
# Edward J. Sabol <edwardjsabol@iname.com>
22
# Max Kanat-Alexander <mkanat@bugzilla.org>
23
# Lance Larsh <lance.larsh@oracle.com>
24
# Dennis Melentyev <dennis.melentyev@infopulse.com.ua>
25
# Akamai Technologies <bugzilla-dev@akamai.com>
27
package Bugzilla::DB::Schema;
29
###########################################################################
31
# Purpose: Object-oriented, DBMS-independent database schema for Bugzilla
33
# This is the base class implementing common methods and abstract schema.
35
###########################################################################
41
use Bugzilla::Constants;
43
use Hash::Util qw(lock_value unlock_hash lock_keys unlock_keys);
45
# Historical, needed for SCHEMA_VERSION = '1.00'
46
use Storable qw(dclone freeze thaw);
48
# New SCHEMA_VERSION (2.00) use this
53
Bugzilla::DB::Schema - Abstract database schema for Bugzilla
57
# Obtain MySQL database schema.
58
# Do not do this. Use Bugzilla::DB instead.
59
use Bugzilla::DB::Schema;
60
my $mysql_schema = new Bugzilla::DB::Schema('Mysql');
62
# Recommended way to obtain database schema.
64
my $dbh = Bugzilla->dbh;
65
my $schema = $dbh->_bz_schema();
67
# Get the list of tables in the Bugzilla database.
68
my @tables = $schema->get_table_list();
70
# Get the SQL statements need to create the bugs table.
71
my @statements = $schema->get_table_ddl('bugs');
73
# Get the database-specific SQL data type used to implement
74
# the abstract data type INT1.
75
my $db_specific_type = $schema->sql_type('INT1');
79
This module implements an object-oriented, abstract database schema.
80
It should be considered package-private to the Bugzilla::DB module.
81
That means that CGI scripts should never call any function in this
82
module directly, but should instead rely on methods provided by
85
=head1 NEW TO SCHEMA.PM?
87
If this is your first time looking at Schema.pm, especially if
88
you are making changes to the database, please take a look at
89
L<http://www.bugzilla.org/docs/developer.html#sql-schema> to learn
90
more about how this integrates into the rest of Bugzilla.
94
#--------------------------------------------------------------------------
95
# Define the Bugzilla abstract database schema and version as constants.
101
=item C<SCHEMA_VERSION>
103
The 'version' of the internal schema structure. This version number
104
is incremented every time the the fundamental structure of Schema
107
This is NOT changed every time a table or a column is added. This
108
number is incremented only if the internal structures of this
109
Schema would be incompatible with the internal structures of a
110
previous Schema version.
112
In general, unless you are messing around with serialization
113
and deserialization of the schema, you don't need to worry about
118
An example of the use of the version number:
120
Today, we store all individual columns like this:
122
column_name => { TYPE => 'SOMETYPE', NOTNULL => 1 }
124
Imagine that someday we decide that NOTNULL => 1 is bad, and we want
125
to change it so that the schema instead uses NULL => 0.
127
But we have a bunch of Bugzilla installations around the world with a
128
serialized schema that has NOTNULL in it! When we deserialize that
129
structure, it just WILL NOT WORK properly inside of our new Schema object.
130
So, immediately after deserializing, we need to go through the hash
131
and change all NOTNULLs to NULLs and so on.
133
We know that we need to do that on deserializing because we know that
134
version 1.00 used NOTNULL. Having made the change to NULL, we would now
139
=item C<ABSTRACT_SCHEMA>
141
The abstract database schema structure consists of a hash reference
142
in which each key is the name of a table in the Bugzilla database.
144
The value for each key is a hash reference containing the keys
145
C<FIELDS> and C<INDEXES> which in turn point to array references
146
containing information on the table's fields and indexes.
148
A field hash reference should must contain the key C<TYPE>. Optional field
149
keys include C<PRIMARYKEY>, C<NOTNULL>, and C<DEFAULT>.
151
The C<INDEXES> array reference contains index names and information
152
regarding the index. If the index name points to an array reference,
153
then the index is a regular index and the array contains the indexed
154
columns. If the index name points to a hash reference, then the hash
155
must contain the key C<FIELDS>. It may also contain the key C<TYPE>,
156
which can be used to specify the type of index such as UNIQUE or FULLTEXT.
162
use constant SCHEMA_VERSION => '2.00';
163
use constant ABSTRACT_SCHEMA => {
168
# General Bug Information
169
# -----------------------
172
bug_id => {TYPE => 'MEDIUMSERIAL', NOTNULL => 1,
174
assigned_to => {TYPE => 'INT3', NOTNULL => 1},
175
bug_file_loc => {TYPE => 'TEXT'},
176
bug_severity => {TYPE => 'varchar(64)', NOTNULL => 1},
177
bug_status => {TYPE => 'varchar(64)', NOTNULL => 1},
178
creation_ts => {TYPE => 'DATETIME'},
179
delta_ts => {TYPE => 'DATETIME', NOTNULL => 1},
180
short_desc => {TYPE => 'varchar(255)', NOTNULL => 1},
181
op_sys => {TYPE => 'varchar(64)', NOTNULL => 1},
182
priority => {TYPE => 'varchar(64)', NOTNULL => 1},
183
product_id => {TYPE => 'INT2', NOTNULL => 1},
184
rep_platform => {TYPE => 'varchar(64)', NOTNULL => 1},
185
reporter => {TYPE => 'INT3', NOTNULL => 1},
186
version => {TYPE => 'varchar(64)', NOTNULL => 1},
187
component_id => {TYPE => 'INT2', NOTNULL => 1},
188
resolution => {TYPE => 'varchar(64)',
189
NOTNULL => 1, DEFAULT => "''"},
190
target_milestone => {TYPE => 'varchar(20)',
191
NOTNULL => 1, DEFAULT => "'---'"},
192
qa_contact => {TYPE => 'INT3'},
193
status_whiteboard => {TYPE => 'MEDIUMTEXT', NOTNULL => 1,
195
votes => {TYPE => 'INT3', NOTNULL => 1,
197
# Note: keywords field is only a cache; the real data
198
# comes from the keywords table
199
keywords => {TYPE => 'MEDIUMTEXT', NOTNULL => 1,
201
lastdiffed => {TYPE => 'DATETIME'},
202
everconfirmed => {TYPE => 'BOOLEAN', NOTNULL => 1},
203
reporter_accessible => {TYPE => 'BOOLEAN',
204
NOTNULL => 1, DEFAULT => 'TRUE'},
205
cclist_accessible => {TYPE => 'BOOLEAN',
206
NOTNULL => 1, DEFAULT => 'TRUE'},
207
estimated_time => {TYPE => 'decimal(5,2)',
208
NOTNULL => 1, DEFAULT => '0'},
209
remaining_time => {TYPE => 'decimal(5,2)',
210
NOTNULL => 1, DEFAULT => '0'},
211
deadline => {TYPE => 'DATETIME'},
212
alias => {TYPE => 'varchar(20)'},
215
bugs_alias_idx => {FIELDS => ['alias'],
217
bugs_assigned_to_idx => ['assigned_to'],
218
bugs_creation_ts_idx => ['creation_ts'],
219
bugs_delta_ts_idx => ['delta_ts'],
220
bugs_bug_severity_idx => ['bug_severity'],
221
bugs_bug_status_idx => ['bug_status'],
222
bugs_op_sys_idx => ['op_sys'],
223
bugs_priority_idx => ['priority'],
224
bugs_product_id_idx => ['product_id'],
225
bugs_reporter_idx => ['reporter'],
226
bugs_version_idx => ['version'],
227
bugs_component_id_idx => ['component_id'],
228
bugs_resolution_idx => ['resolution'],
229
bugs_target_milestone_idx => ['target_milestone'],
230
bugs_qa_contact_idx => ['qa_contact'],
231
bugs_votes_idx => ['votes'],
237
bug_id => {TYPE => 'INT3', NOTNULL => 1},
238
attach_id => {TYPE => 'INT3'},
239
who => {TYPE => 'INT3', NOTNULL => 1},
240
bug_when => {TYPE => 'DATETIME', NOTNULL => 1},
241
fieldid => {TYPE => 'INT3', NOTNULL => 1},
242
added => {TYPE => 'TINYTEXT'},
243
removed => {TYPE => 'TINYTEXT'},
246
bugs_activity_bug_id_idx => ['bug_id'],
247
bugs_activity_who_idx => ['who'],
248
bugs_activity_bug_when_idx => ['bug_when'],
249
bugs_activity_fieldid_idx => ['fieldid'],
255
bug_id => {TYPE => 'INT3', NOTNULL => 1},
256
who => {TYPE => 'INT3', NOTNULL => 1},
259
cc_bug_id_idx => {FIELDS => [qw(bug_id who)],
261
cc_who_idx => ['who'],
267
comment_id => {TYPE => 'MEDIUMSERIAL', NOTNULL => 1,
269
bug_id => {TYPE => 'INT3', NOTNULL => 1},
270
who => {TYPE => 'INT3', NOTNULL => 1},
271
bug_when => {TYPE => 'DATETIME', NOTNULL => 1},
272
work_time => {TYPE => 'decimal(5,2)', NOTNULL => 1,
274
thetext => {TYPE => 'MEDIUMTEXT', NOTNULL => 1},
275
isprivate => {TYPE => 'BOOLEAN', NOTNULL => 1,
277
already_wrapped => {TYPE => 'BOOLEAN', NOTNULL => 1,
279
type => {TYPE => 'INT2', NOTNULL => 1,
281
extra_data => {TYPE => 'varchar(255)'}
284
longdescs_bug_id_idx => ['bug_id'],
285
longdescs_who_idx => [qw(who bug_id)],
286
longdescs_bug_when_idx => ['bug_when'],
287
longdescs_thetext_idx => {FIELDS => ['thetext'],
294
blocked => {TYPE => 'INT3', NOTNULL => 1},
295
dependson => {TYPE => 'INT3', NOTNULL => 1},
298
dependencies_blocked_idx => ['blocked'],
299
dependencies_dependson_idx => ['dependson'],
305
who => {TYPE => 'INT3', NOTNULL => 1},
306
bug_id => {TYPE => 'INT3', NOTNULL => 1},
307
vote_count => {TYPE => 'INT2', NOTNULL => 1},
310
votes_who_idx => ['who'],
311
votes_bug_id_idx => ['bug_id'],
317
attach_id => {TYPE => 'MEDIUMSERIAL', NOTNULL => 1,
319
bug_id => {TYPE => 'INT3', NOTNULL => 1},
320
creation_ts => {TYPE => 'DATETIME', NOTNULL => 1},
321
description => {TYPE => 'MEDIUMTEXT', NOTNULL => 1},
322
mimetype => {TYPE => 'MEDIUMTEXT', NOTNULL => 1},
323
ispatch => {TYPE => 'BOOLEAN'},
324
filename => {TYPE => 'varchar(100)', NOTNULL => 1},
325
submitter_id => {TYPE => 'INT3', NOTNULL => 1},
326
isobsolete => {TYPE => 'BOOLEAN', NOTNULL => 1,
328
isprivate => {TYPE => 'BOOLEAN', NOTNULL => 1,
330
isurl => {TYPE => 'BOOLEAN', NOTNULL => 1,
334
attachments_bug_id_idx => ['bug_id'],
335
attachments_creation_ts_idx => ['creation_ts'],
336
attachments_submitter_id_idx => ['submitter_id', 'bug_id'],
341
id => {TYPE => 'INT3', NOTNULL => 1,
343
thedata => {TYPE => 'LONGBLOB', NOTNULL => 1},
349
dupe_of => {TYPE => 'INT3', NOTNULL => 1},
350
dupe => {TYPE => 'INT3', NOTNULL => 1,
360
id => {TYPE => 'SMALLSERIAL', NOTNULL => 1,
362
name => {TYPE => 'varchar(64)', NOTNULL => 1},
363
description => {TYPE => 'MEDIUMTEXT'},
366
keyworddefs_name_idx => {FIELDS => ['name'],
373
bug_id => {TYPE => 'INT3', NOTNULL => 1},
374
keywordid => {TYPE => 'INT2', NOTNULL => 1},
377
keywords_bug_id_idx => {FIELDS => [qw(bug_id keywordid)],
379
keywords_keywordid_idx => ['keywordid'],
386
# "flags" stores one record for each flag on each bug/attachment.
389
id => {TYPE => 'MEDIUMSERIAL', NOTNULL => 1,
391
type_id => {TYPE => 'INT2', NOTNULL => 1},
392
status => {TYPE => 'char(1)', NOTNULL => 1},
393
bug_id => {TYPE => 'INT3', NOTNULL => 1},
394
attach_id => {TYPE => 'INT3'},
395
creation_date => {TYPE => 'DATETIME', NOTNULL => 1},
396
modification_date => {TYPE => 'DATETIME'},
397
setter_id => {TYPE => 'INT3'},
398
requestee_id => {TYPE => 'INT3'},
401
flags_bug_id_idx => [qw(bug_id attach_id)],
402
flags_setter_id_idx => ['setter_id'],
403
flags_requestee_id_idx => ['requestee_id'],
404
flags_type_id_idx => ['type_id'],
408
# "flagtypes" defines the types of flags that can be set.
411
id => {TYPE => 'SMALLSERIAL', NOTNULL => 1,
413
name => {TYPE => 'varchar(50)', NOTNULL => 1},
414
description => {TYPE => 'TEXT'},
415
cc_list => {TYPE => 'varchar(200)'},
416
target_type => {TYPE => 'char(1)', NOTNULL => 1,
418
is_active => {TYPE => 'BOOLEAN', NOTNULL => 1,
420
is_requestable => {TYPE => 'BOOLEAN', NOTNULL => 1,
422
is_requesteeble => {TYPE => 'BOOLEAN', NOTNULL => 1,
424
is_multiplicable => {TYPE => 'BOOLEAN', NOTNULL => 1,
426
sortkey => {TYPE => 'INT2', NOTNULL => 1,
428
grant_group_id => {TYPE => 'INT3'},
429
request_group_id => {TYPE => 'INT3'},
433
# "flaginclusions" and "flagexclusions" specify the products/components
434
# a bug/attachment must belong to in order for flags of a given type
435
# to be set for them.
438
type_id => {TYPE => 'INT2', NOTNULL => 1},
439
product_id => {TYPE => 'INT2'},
440
component_id => {TYPE => 'INT2'},
443
flaginclusions_type_id_idx =>
444
[qw(type_id product_id component_id)],
450
type_id => {TYPE => 'INT2', NOTNULL => 1},
451
product_id => {TYPE => 'INT2'},
452
component_id => {TYPE => 'INT2'},
455
flagexclusions_type_id_idx =>
456
[qw(type_id product_id component_id)],
460
# General Field Information
461
# -------------------------
465
id => {TYPE => 'MEDIUMSERIAL', NOTNULL => 1,
467
name => {TYPE => 'varchar(64)', NOTNULL => 1},
468
type => {TYPE => 'INT2', NOTNULL => 1,
469
DEFAULT => FIELD_TYPE_UNKNOWN},
470
custom => {TYPE => 'BOOLEAN', NOTNULL => 1,
472
description => {TYPE => 'MEDIUMTEXT', NOTNULL => 1},
473
mailhead => {TYPE => 'BOOLEAN', NOTNULL => 1,
475
sortkey => {TYPE => 'INT2', NOTNULL => 1},
476
obsolete => {TYPE => 'BOOLEAN', NOTNULL => 1,
478
enter_bug => {TYPE => 'BOOLEAN', NOTNULL => 1,
482
fielddefs_name_idx => {FIELDS => ['name'],
484
fielddefs_sortkey_idx => ['sortkey'],
488
# Per-product Field Values
489
# ------------------------
493
id => {TYPE => 'MEDIUMSERIAL', NOTNULL => 1,
495
value => {TYPE => 'varchar(64)', NOTNULL => 1},
496
product_id => {TYPE => 'INT2', NOTNULL => 1},
499
versions_product_id_idx => {FIELDS => [qw(product_id value)],
506
id => {TYPE => 'MEDIUMSERIAL', NOTNULL => 1,
508
product_id => {TYPE => 'INT2', NOTNULL => 1},
509
value => {TYPE => 'varchar(20)', NOTNULL => 1},
510
sortkey => {TYPE => 'INT2', NOTNULL => 1,
514
milestones_product_id_idx => {FIELDS => [qw(product_id value)],
519
# Global Field Values
520
# -------------------
524
id => {TYPE => 'SMALLSERIAL', NOTNULL => 1,
526
value => {TYPE => 'varchar(64)', NOTNULL => 1},
527
sortkey => {TYPE => 'INT2', NOTNULL => 1, DEFAULT => 0},
528
isactive => {TYPE => 'BOOLEAN', NOTNULL => 1,
532
bug_status_value_idx => {FIELDS => ['value'],
534
bug_status_sortkey_idx => ['sortkey', 'value'],
540
id => {TYPE => 'SMALLSERIAL', NOTNULL => 1,
542
value => {TYPE => 'varchar(64)', NOTNULL => 1},
543
sortkey => {TYPE => 'INT2', NOTNULL => 1, DEFAULT => 0},
544
isactive => {TYPE => 'BOOLEAN', NOTNULL => 1,
548
resolution_value_idx => {FIELDS => ['value'],
550
resolution_sortkey_idx => ['sortkey', 'value'],
556
id => {TYPE => 'SMALLSERIAL', NOTNULL => 1,
558
value => {TYPE => 'varchar(64)', NOTNULL => 1},
559
sortkey => {TYPE => 'INT2', NOTNULL => 1, DEFAULT => 0},
560
isactive => {TYPE => 'BOOLEAN', NOTNULL => 1,
564
bug_severity_value_idx => {FIELDS => ['value'],
566
bug_severity_sortkey_idx => ['sortkey', 'value'],
572
id => {TYPE => 'SMALLSERIAL', NOTNULL => 1,
574
value => {TYPE => 'varchar(64)', NOTNULL => 1},
575
sortkey => {TYPE => 'INT2', NOTNULL => 1, DEFAULT => 0},
576
isactive => {TYPE => 'BOOLEAN', NOTNULL => 1,
580
priority_value_idx => {FIELDS => ['value'],
582
priority_sortkey_idx => ['sortkey', 'value'],
588
id => {TYPE => 'SMALLSERIAL', NOTNULL => 1,
590
value => {TYPE => 'varchar(64)', NOTNULL => 1},
591
sortkey => {TYPE => 'INT2', NOTNULL => 1, DEFAULT => 0},
592
isactive => {TYPE => 'BOOLEAN', NOTNULL => 1,
596
rep_platform_value_idx => {FIELDS => ['value'],
598
rep_platform_sortkey_idx => ['sortkey', 'value'],
604
id => {TYPE => 'SMALLSERIAL', NOTNULL => 1,
606
value => {TYPE => 'varchar(64)', NOTNULL => 1},
607
sortkey => {TYPE => 'INT2', NOTNULL => 1, DEFAULT => 0},
608
isactive => {TYPE => 'BOOLEAN', NOTNULL => 1,
612
op_sys_value_idx => {FIELDS => ['value'],
614
op_sys_sortkey_idx => ['sortkey', 'value'],
621
# General User Information
622
# ------------------------
626
userid => {TYPE => 'MEDIUMSERIAL', NOTNULL => 1,
628
login_name => {TYPE => 'varchar(255)', NOTNULL => 1},
629
cryptpassword => {TYPE => 'varchar(128)'},
630
realname => {TYPE => 'varchar(255)', NOTNULL => 1,
632
disabledtext => {TYPE => 'MEDIUMTEXT', NOTNULL => 1,
634
disable_mail => {TYPE => 'BOOLEAN', NOTNULL => 1,
636
mybugslink => {TYPE => 'BOOLEAN', NOTNULL => 1,
638
extern_id => {TYPE => 'varchar(64)'},
641
profiles_login_name_idx => {FIELDS => ['login_name'],
646
profiles_activity => {
648
userid => {TYPE => 'INT3', NOTNULL => 1},
649
who => {TYPE => 'INT3', NOTNULL => 1},
650
profiles_when => {TYPE => 'DATETIME', NOTNULL => 1},
651
fieldid => {TYPE => 'INT3', NOTNULL => 1},
652
oldvalue => {TYPE => 'TINYTEXT'},
653
newvalue => {TYPE => 'TINYTEXT'},
656
profiles_activity_userid_idx => ['userid'],
657
profiles_activity_profiles_when_idx => ['profiles_when'],
658
profiles_activity_fieldid_idx => ['fieldid'],
664
user_id => {TYPE => 'INT3', NOTNULL => 1},
665
relationship => {TYPE => 'INT1', NOTNULL => 1},
666
event => {TYPE => 'INT1', NOTNULL => 1},
669
email_setting_user_id_idx =>
670
{FIELDS => [qw(user_id relationship event)],
677
watcher => {TYPE => 'INT3', NOTNULL => 1},
678
watched => {TYPE => 'INT3', NOTNULL => 1},
681
watch_watcher_idx => {FIELDS => [qw(watcher watched)],
683
watch_watched_idx => ['watched'],
689
id => {TYPE => 'MEDIUMSERIAL', NOTNULL => 1,
691
userid => {TYPE => 'INT3', NOTNULL => 1},
692
name => {TYPE => 'varchar(64)', NOTNULL => 1},
693
query => {TYPE => 'MEDIUMTEXT', NOTNULL => 1},
694
query_type => {TYPE => 'BOOLEAN', NOTNULL => 1},
697
namedqueries_userid_idx => {FIELDS => [qw(userid name)],
702
namedqueries_link_in_footer => {
704
namedquery_id => {TYPE => 'INT3', NOTNULL => 1},
705
user_id => {TYPE => 'INT3', NOTNULL => 1},
708
namedqueries_link_in_footer_id_idx => {FIELDS => [qw(namedquery_id user_id)],
710
namedqueries_link_in_footer_userid_idx => ['user_id'],
717
user_id => {TYPE => 'INT3', NOTNULL => 1},
718
component_id => {TYPE => 'INT2', NOTNULL => 1},
721
component_cc_user_id_idx => {FIELDS => [qw(component_id user_id)],
731
cookie => {TYPE => 'varchar(16)', NOTNULL => 1,
733
userid => {TYPE => 'INT3', NOTNULL => 1},
734
ipaddr => {TYPE => 'varchar(40)', NOTNULL => 1},
735
lastused => {TYPE => 'DATETIME', NOTNULL => 1},
738
logincookies_lastused_idx => ['lastused'],
742
# "tokens" stores the tokens users receive when a password or email
743
# change is requested. Tokens provide an extra measure of security
747
userid => {TYPE => 'INT3'},
748
issuedate => {TYPE => 'DATETIME', NOTNULL => 1} ,
749
token => {TYPE => 'varchar(16)', NOTNULL => 1,
751
tokentype => {TYPE => 'varchar(8)', NOTNULL => 1} ,
752
eventdata => {TYPE => 'TINYTEXT'},
755
tokens_userid_idx => ['userid'],
764
id => {TYPE => 'MEDIUMSERIAL', NOTNULL => 1,
766
name => {TYPE => 'varchar(255)', NOTNULL => 1},
767
description => {TYPE => 'TEXT', NOTNULL => 1},
768
isbuggroup => {TYPE => 'BOOLEAN', NOTNULL => 1},
769
userregexp => {TYPE => 'TINYTEXT', NOTNULL => 1,
771
isactive => {TYPE => 'BOOLEAN', NOTNULL => 1,
775
groups_name_idx => {FIELDS => ['name'], TYPE => 'UNIQUE'},
779
group_control_map => {
781
group_id => {TYPE => 'INT3', NOTNULL => 1},
782
product_id => {TYPE => 'INT3', NOTNULL => 1},
783
entry => {TYPE => 'BOOLEAN', NOTNULL => 1},
784
membercontrol => {TYPE => 'BOOLEAN', NOTNULL => 1},
785
othercontrol => {TYPE => 'BOOLEAN', NOTNULL => 1},
786
canedit => {TYPE => 'BOOLEAN', NOTNULL => 1},
787
editcomponents => {TYPE => 'BOOLEAN', NOTNULL => 1,
789
editbugs => {TYPE => 'BOOLEAN', NOTNULL => 1,
791
canconfirm => {TYPE => 'BOOLEAN', NOTNULL => 1,
795
group_control_map_product_id_idx =>
796
{FIELDS => [qw(product_id group_id)], TYPE => 'UNIQUE'},
797
group_control_map_group_id_idx => ['group_id'],
801
# "user_group_map" determines the groups that a user belongs to
802
# directly or due to regexp and which groups can be blessed by a user.
805
# if GRANT_DIRECT - record was explicitly granted
806
# if GRANT_DERIVED - record was derived from expanding a group hierarchy
807
# if GRANT_REGEXP - record was created by evaluating a regexp
810
user_id => {TYPE => 'INT3', NOTNULL => 1},
811
group_id => {TYPE => 'INT3', NOTNULL => 1},
812
isbless => {TYPE => 'BOOLEAN', NOTNULL => 1,
814
grant_type => {TYPE => 'INT1', NOTNULL => 1,
815
DEFAULT => GRANT_DIRECT},
818
user_group_map_user_id_idx =>
819
{FIELDS => [qw(user_id group_id grant_type isbless)],
824
# This table determines which groups are made a member of another
825
# group, given the ability to bless another group, or given
826
# visibility to another groups existence and membership
828
# if GROUP_MEMBERSHIP - member groups are made members of grantor
829
# if GROUP_BLESS - member groups may grant membership in grantor
830
# if GROUP_VISIBLE - member groups may see grantor group
833
member_id => {TYPE => 'INT3', NOTNULL => 1},
834
grantor_id => {TYPE => 'INT3', NOTNULL => 1},
835
grant_type => {TYPE => 'INT1', NOTNULL => 1,
836
DEFAULT => GROUP_MEMBERSHIP},
839
group_group_map_member_id_idx =>
840
{FIELDS => [qw(member_id grantor_id grant_type)],
845
# This table determines which groups a user must be a member of
846
# in order to see a bug.
849
bug_id => {TYPE => 'INT3', NOTNULL => 1},
850
group_id => {TYPE => 'INT3', NOTNULL => 1},
853
bug_group_map_bug_id_idx =>
854
{FIELDS => [qw(bug_id group_id)], TYPE => 'UNIQUE'},
855
bug_group_map_group_id_idx => ['group_id'],
859
# This table determines which groups a user must be a member of
860
# in order to see a named query somebody else shares.
861
namedquery_group_map => {
863
namedquery_id => {TYPE => 'INT3', NOTNULL => 1},
864
group_id => {TYPE => 'INT3', NOTNULL => 1},
867
namedquery_group_map_namedquery_id_idx =>
868
{FIELDS => [qw(namedquery_id)], TYPE => 'UNIQUE'},
869
namedquery_group_map_group_id_idx => ['group_id'],
873
category_group_map => {
875
category_id => {TYPE => 'INT2', NOTNULL => 1},
876
group_id => {TYPE => 'INT3', NOTNULL => 1},
879
category_group_map_category_id_idx =>
880
{FIELDS => [qw(category_id group_id)], TYPE => 'UNIQUE'},
890
id => {TYPE => 'SMALLSERIAL', NOTNULL => 1,
892
name => {TYPE => 'varchar(64)', NOTNULL => 1},
893
description => {TYPE => 'MEDIUMTEXT'},
894
sortkey => {TYPE => 'INT2', NOTNULL => 1, DEFAULT => '0'},
897
classifications_name_idx => {FIELDS => ['name'],
904
id => {TYPE => 'SMALLSERIAL', NOTNULL => 1,
906
name => {TYPE => 'varchar(64)', NOTNULL => 1},
907
classification_id => {TYPE => 'INT2', NOTNULL => 1,
909
description => {TYPE => 'MEDIUMTEXT'},
910
milestoneurl => {TYPE => 'TINYTEXT', NOTNULL => 1,
912
disallownew => {TYPE => 'BOOLEAN', NOTNULL => 1,
914
votesperuser => {TYPE => 'INT2', NOTNULL => 1,
916
maxvotesperbug => {TYPE => 'INT2', NOTNULL => 1,
918
votestoconfirm => {TYPE => 'INT2', NOTNULL => 1,
920
defaultmilestone => {TYPE => 'varchar(20)',
921
NOTNULL => 1, DEFAULT => "'---'"},
924
products_name_idx => {FIELDS => ['name'],
931
id => {TYPE => 'SMALLSERIAL', NOTNULL => 1,
933
name => {TYPE => 'varchar(64)', NOTNULL => 1},
934
product_id => {TYPE => 'INT2', NOTNULL => 1},
935
initialowner => {TYPE => 'INT3', NOTNULL => 1},
936
initialqacontact => {TYPE => 'INT3'},
937
description => {TYPE => 'MEDIUMTEXT', NOTNULL => 1},
940
components_product_id_idx => {FIELDS => [qw(product_id name)],
942
components_name_idx => ['name'],
951
series_id => {TYPE => 'MEDIUMSERIAL', NOTNULL => 1,
953
creator => {TYPE => 'INT3'},
954
category => {TYPE => 'INT2', NOTNULL => 1},
955
subcategory => {TYPE => 'INT2', NOTNULL => 1},
956
name => {TYPE => 'varchar(64)', NOTNULL => 1},
957
frequency => {TYPE => 'INT2', NOTNULL => 1},
958
last_viewed => {TYPE => 'DATETIME'},
959
query => {TYPE => 'MEDIUMTEXT', NOTNULL => 1},
960
is_public => {TYPE => 'BOOLEAN', NOTNULL => 1,
964
series_creator_idx =>
965
{FIELDS => [qw(creator category subcategory name)],
972
series_id => {TYPE => 'INT3', NOTNULL => 1},
973
series_date => {TYPE => 'DATETIME', NOTNULL => 1},
974
series_value => {TYPE => 'INT3', NOTNULL => 1},
977
series_data_series_id_idx =>
978
{FIELDS => [qw(series_id series_date)],
983
series_categories => {
985
id => {TYPE => 'SMALLSERIAL', NOTNULL => 1,
987
name => {TYPE => 'varchar(64)', NOTNULL => 1},
990
series_categories_name_idx => {FIELDS => ['name'],
1000
id => {TYPE => 'MEDIUMSERIAL', PRIMARYKEY => 1,
1002
eventid => {TYPE => 'INT3', NOTNULL => 1},
1003
query_name => {TYPE => 'varchar(64)', NOTNULL => 1,
1005
sortkey => {TYPE => 'INT2', NOTNULL => 1,
1007
onemailperbug => {TYPE => 'BOOLEAN', NOTNULL => 1,
1008
DEFAULT => 'FALSE'},
1009
title => {TYPE => 'varchar(128)', NOTNULL => 1,
1013
whine_queries_eventid_idx => ['eventid'],
1017
whine_schedules => {
1019
id => {TYPE => 'MEDIUMSERIAL', PRIMARYKEY => 1,
1021
eventid => {TYPE => 'INT3', NOTNULL => 1},
1022
run_day => {TYPE => 'varchar(32)'},
1023
run_time => {TYPE => 'varchar(32)'},
1024
run_next => {TYPE => 'DATETIME'},
1025
mailto => {TYPE => 'INT3', NOTNULL => 1},
1026
mailto_type => {TYPE => 'INT2', NOTNULL => 1, DEFAULT => '0'},
1029
whine_schedules_run_next_idx => ['run_next'],
1030
whine_schedules_eventid_idx => ['eventid'],
1036
id => {TYPE => 'MEDIUMSERIAL', PRIMARYKEY => 1,
1038
owner_userid => {TYPE => 'INT3', NOTNULL => 1},
1039
subject => {TYPE => 'varchar(128)'},
1040
body => {TYPE => 'MEDIUMTEXT'},
1049
quipid => {TYPE => 'MEDIUMSERIAL', NOTNULL => 1,
1051
userid => {TYPE => 'INT3'},
1052
quip => {TYPE => 'TEXT', NOTNULL => 1},
1053
approved => {TYPE => 'BOOLEAN', NOTNULL => 1,
1060
# setting - each global setting will have exactly one entry
1062
# setting_value - stores the list of acceptable values for each
1063
# setting, and a sort index that controls the order
1064
# in which the values are displayed.
1065
# profile_setting - If a user has chosen to use a value other than the
1066
# global default for a given setting, it will be
1067
# stored in this table. Note: even if a setting is
1068
# later changed so is_enabled = false, the stored
1069
# value will remain in case it is ever enabled again.
1073
name => {TYPE => 'varchar(32)', NOTNULL => 1,
1075
default_value => {TYPE => 'varchar(32)', NOTNULL => 1},
1076
is_enabled => {TYPE => 'BOOLEAN', NOTNULL => 1,
1078
subclass => {TYPE => 'varchar(32)'},
1084
name => {TYPE => 'varchar(32)', NOTNULL => 1},
1085
value => {TYPE => 'varchar(32)', NOTNULL => 1},
1086
sortindex => {TYPE => 'INT2', NOTNULL => 1},
1089
setting_value_nv_unique_idx => {FIELDS => [qw(name value)],
1091
setting_value_ns_unique_idx => {FIELDS => [qw(name sortindex)],
1096
profile_setting => {
1098
user_id => {TYPE => 'INT3', NOTNULL => 1},
1099
setting_name => {TYPE => 'varchar(32)', NOTNULL => 1},
1100
setting_value => {TYPE => 'varchar(32)', NOTNULL => 1},
1103
profile_setting_value_unique_idx => {FIELDS => [qw(user_id setting_name)],
1113
schema_data => {TYPE => 'LONGBLOB', NOTNULL => 1},
1114
version => {TYPE => 'decimal(3,2)', NOTNULL => 1},
1120
use constant FIELD_TABLE_SCHEMA => {
1122
id => {TYPE => 'SMALLSERIAL', NOTNULL => 1,
1124
value => {TYPE => 'varchar(64)', NOTNULL => 1},
1125
sortkey => {TYPE => 'INT2', NOTNULL => 1, DEFAULT => 0},
1126
isactive => {TYPE => 'BOOLEAN', NOTNULL => 1,
1129
# Note that bz_add_field_table should prepend the table name
1130
# to these index names.
1132
value_idx => {FIELDS => ['value'], TYPE => 'UNIQUE'},
1133
sortkey_idx => ['sortkey', 'value'],
1136
#--------------------------------------------------------------------------
1140
Note: Methods which can be implemented generically for all DBs are
1141
implemented in this module. If needed, they can be overridden with
1142
DB-specific code in a subclass. Methods which are prefixed with C<_>
1143
are considered protected. Subclasses may override these methods, but
1144
other modules should not invoke these methods directly.
1148
#--------------------------------------------------------------------------
1155
Description: Public constructor method used to instantiate objects of this
1156
class. However, it also can be used as a factory method to
1157
instantiate database-specific subclasses when an optional
1158
driver argument is supplied.
1159
Parameters: $driver (optional) - Used to specify the type of database.
1160
This routine C<die>s if no subclass is found for the specified
1162
$schema (optional) - A reference to a hash. Callers external
1163
to this package should never use this parameter.
1164
Returns: new instance of the Schema class or a database-specific subclass
1169
my $class = ref($this) || $this;
1173
(my $subclass = $driver) =~ s/^(\S)/\U$1/;
1174
$class .= '::' . $subclass;
1175
eval "require $class;";
1176
die "The $class class could not be found ($subclass " .
1177
"not supported?): $@" if ($@);
1179
die "$class is an abstract base class. Instantiate a subclass instead."
1180
if ($class eq __PACKAGE__);
1183
bless $self, $class;
1184
$self = $self->_initialize(@_);
1189
#--------------------------------------------------------------------------
1192
=item C<_initialize>
1194
Description: Protected method that initializes an object after
1195
instantiation with the abstract schema. All subclasses should
1196
override this method. The typical subclass implementation
1197
should first call the C<_initialize> method of the superclass,
1198
then do any database-specific initialization (especially
1199
define the database-specific implementation of the all
1200
abstract data types), and then call the C<_adjust_schema>
1202
Parameters: $abstract_schema (optional) - A reference to a hash. If
1203
provided, this hash will be used as the internal
1204
representation of the abstract schema instead of our
1205
default abstract schema. This is intended for internal
1206
use only by deserialize_abstract.
1207
Returns: the instance of the Schema class
1212
my $abstract_schema = shift;
1214
if (!$abstract_schema) {
1215
# While ABSTRACT_SCHEMA cannot be modified, $abstract_schema can be.
1216
# So, we dclone it to prevent anything from mucking with the constant.
1217
$abstract_schema = dclone(ABSTRACT_SCHEMA);
1219
# Let extensions add tables, but make sure they can't modify existing
1220
# tables. If we don't lock/unlock keys, lock_value complains.
1221
lock_keys(%$abstract_schema);
1222
foreach my $table (keys %{ABSTRACT_SCHEMA()}) {
1223
lock_value(%$abstract_schema, $table)
1224
if exists $abstract_schema->{$table};
1226
unlock_keys(%$abstract_schema);
1227
Bugzilla::Hook::process('db_schema-abstract_schema',
1228
{ schema => $abstract_schema });
1229
unlock_hash(%$abstract_schema);
1232
$self->{schema} = dclone($abstract_schema);
1233
$self->{abstract_schema} = $abstract_schema;
1237
} #eosub--_initialize
1238
#--------------------------------------------------------------------------
1239
sub _adjust_schema {
1241
=item C<_adjust_schema>
1243
Description: Protected method that alters the abstract schema at
1244
instantiation-time to be database-specific. It is a generic
1245
enough routine that it can be defined here in the base class.
1246
It takes the abstract schema and replaces the abstract data
1247
types with database-specific data types.
1249
Returns: the instance of the Schema class
1255
# The _initialize method has already set up the db_specific hash with
1256
# the information on how to implement the abstract data types for the
1257
# instantiated DBMS-specific subclass.
1258
my $db_specific = $self->{db_specific};
1260
# Loop over each table in the abstract database schema.
1261
foreach my $table (keys %{ $self->{schema} }) {
1262
my %fields = (@{ $self->{schema}{$table}{FIELDS} });
1263
# Loop over the field definitions in each table.
1264
foreach my $field_def (values %fields) {
1265
# If the field type is an abstract data type defined in the
1266
# $db_specific hash, replace it with the DBMS-specific data type
1267
# that implements it.
1268
if (exists($db_specific->{$field_def->{TYPE}})) {
1269
$field_def->{TYPE} = $db_specific->{$field_def->{TYPE}};
1271
# Replace abstract default values (such as 'TRUE' and 'FALSE')
1272
# with their database-specific implementations.
1273
if (exists($field_def->{DEFAULT})
1274
&& exists($db_specific->{$field_def->{DEFAULT}})) {
1275
$field_def->{DEFAULT} = $db_specific->{$field_def->{DEFAULT}};
1282
} #eosub--_adjust_schema
1283
#--------------------------------------------------------------------------
1286
=item C<get_type_ddl>
1288
Description: Public method to convert abstract (database-generic) field
1289
specifiers to database-specific data types suitable for use
1290
in a C<CREATE TABLE> or C<ALTER TABLE> SQL statment. If no
1291
database-specific field type has been defined for the given
1292
field type, then it will just return the same field type.
1293
Parameters: a hash or a reference to a hash of a field containing the
1294
following keys: C<TYPE> (required), C<NOTNULL> (optional),
1295
C<DEFAULT> (optional), C<PRIMARYKEY> (optional), C<REFERENCES>
1297
Returns: a DDL string suitable for describing a field in a
1298
C<CREATE TABLE> or C<ALTER TABLE> SQL statement
1303
my $finfo = (@_ == 1 && ref($_[0]) eq 'HASH') ? $_[0] : { @_ };
1305
my $type = $finfo->{TYPE};
1306
die "A valid TYPE was not specified for this column." unless ($type);
1308
my $default = $finfo->{DEFAULT};
1309
# Replace any abstract default value (such as 'TRUE' or 'FALSE')
1310
# with its database-specific implementation.
1311
if ( defined $default && exists($self->{db_specific}->{$default}) ) {
1312
$default = $self->{db_specific}->{$default};
1315
my $fkref = $self->{enable_references} ? $finfo->{REFERENCES} : undef;
1316
my $type_ddl = $self->convert_type($type);
1317
# DEFAULT attribute must appear before any column constraints
1318
# (e.g., NOT NULL), for Oracle
1319
$type_ddl .= " DEFAULT $default" if (defined($default));
1320
$type_ddl .= " NOT NULL" if ($finfo->{NOTNULL});
1321
$type_ddl .= " PRIMARY KEY" if ($finfo->{PRIMARYKEY});
1322
$type_ddl .= "\n\t\t\t\tREFERENCES $fkref" if $fkref;
1326
} #eosub--get_type_ddl
1330
=item C<convert_type>
1332
Converts a TYPE from the L</ABSTRACT_SCHEMA> format into the real SQL type.
1336
my ($self, $type) = @_;
1337
return $self->{db_specific}->{$type} || $type;
1341
=item C<get_column($table, $column)>
1343
Description: Public method to get the abstract definition of a column.
1344
Parameters: $table - the table name
1345
$column - a column in the table
1346
Returns: a hashref containing information about the column, including its
1347
type (C<TYPE>), whether or not it can be null (C<NOTNULL>),
1348
its default value if it has one (C<DEFAULT), etc.
1349
Returns undef if the table or column does not exist.
1353
my($self, $table, $column) = @_;
1355
# Prevent a possible dereferencing of an undef hash, if the
1356
# table doesn't exist.
1357
if (exists $self->{schema}->{$table}) {
1358
my %fields = (@{ $self->{schema}{$table}{FIELDS} });
1359
return $fields{$column};
1362
} #eosub--get_column
1364
sub get_table_list {
1366
=item C<get_table_list>
1368
Description: Public method for discovering what tables should exist in the
1371
Returns: an array of table names
1377
return(sort(keys %{ $self->{schema} }));
1379
} #eosub--get_table_list
1380
#--------------------------------------------------------------------------
1381
sub get_table_columns {
1383
=item C<get_table_columns>
1385
Description: Public method for discovering what columns are in a given
1386
table in the Bugzilla database.
1387
Parameters: $table - the table name
1388
Returns: array of column names
1392
my($self, $table) = @_;
1395
my $thash = $self->{schema}{$table};
1396
die "Table $table does not exist in the database schema."
1397
unless (ref($thash));
1400
my @fields = @{ $thash->{FIELDS} };
1402
push(@columns, shift(@fields));
1408
} #eosub--get_table_columns
1410
sub get_table_indexes_abstract {
1411
my ($self, $table) = @_;
1412
my $table_def = $self->get_table_abstract($table);
1413
my %indexes = @{$table_def->{INDEXES} || []};
1417
sub get_create_database_sql {
1418
my ($self, $name) = @_;
1419
return ("CREATE DATABASE $name");
1424
=item C<get_table_ddl>
1426
Description: Public method to generate the SQL statements needed to create
1427
the a given table and its indexes in the Bugzilla database.
1428
Subclasses may override or extend this method, if needed, but
1429
subclasses probably should override C<_get_create_table_ddl>
1430
or C<_get_create_index_ddl> instead.
1431
Parameters: $table - the table name
1432
Returns: an array of strings containing SQL statements
1436
my($self, $table) = @_;
1439
die "Table $table does not exist in the database schema."
1440
unless (ref($self->{schema}{$table}));
1442
my $create_table = $self->_get_create_table_ddl($table);
1443
push(@ddl, $create_table) if $create_table;
1445
my @indexes = @{ $self->{schema}{$table}{INDEXES} || [] };
1447
my $index_name = shift(@indexes);
1448
my $index_info = shift(@indexes);
1449
my $index_sql = $self->get_add_index_ddl($table, $index_name,
1451
push(@ddl, $index_sql) if $index_sql;
1454
push(@ddl, @{ $self->{schema}{$table}{DB_EXTRAS} })
1455
if (ref($self->{schema}{$table}{DB_EXTRAS}));
1459
} #eosub--get_table_ddl
1460
#--------------------------------------------------------------------------
1461
sub _get_create_table_ddl {
1463
=item C<_get_create_table_ddl>
1465
Description: Protected method to generate the "create table" SQL statement
1467
Parameters: $table - the table name
1468
Returns: a string containing the DDL statement for the specified table
1472
my($self, $table) = @_;
1474
my $thash = $self->{schema}{$table};
1475
die "Table $table does not exist in the database schema."
1476
unless (ref($thash));
1478
my $create_table = "CREATE TABLE $table \(\n";
1480
my @fields = @{ $thash->{FIELDS} };
1482
my $field = shift(@fields);
1483
my $finfo = shift(@fields);
1484
$create_table .= "\t$field\t" . $self->get_type_ddl($finfo);
1485
$create_table .= "," if (@fields);
1486
$create_table .= "\n";
1489
$create_table .= "\)";
1491
return($create_table)
1493
} #eosub--_get_create_table_ddl
1494
#--------------------------------------------------------------------------
1495
sub _get_create_index_ddl {
1497
=item C<_get_create_index_ddl>
1499
Description: Protected method to generate a "create index" SQL statement
1500
for a given table and index.
1501
Parameters: $table_name - the name of the table
1502
$index_name - the name of the index
1503
$index_fields - a reference to an array of field names
1504
$index_type (optional) - specify type of index (e.g., UNIQUE)
1505
Returns: a string containing the DDL statement
1509
my ($self, $table_name, $index_name, $index_fields, $index_type) = @_;
1511
my $sql = "CREATE ";
1512
$sql .= "$index_type " if ($index_type && $index_type eq 'UNIQUE');
1513
$sql .= "INDEX $index_name ON $table_name \(" .
1514
join(", ", @$index_fields) . "\)";
1518
} #eosub--_get_create_index_ddl
1519
#--------------------------------------------------------------------------
1521
sub get_add_column_ddl {
1523
=item C<get_add_column_ddl($table, $column, \%definition, $init_value)>
1525
Description: Generate SQL to add a column to a table.
1526
Params: $table - The table containing the column.
1527
$column - The name of the column being added.
1528
\%definition - The new definition for the column,
1529
in standard C<ABSTRACT_SCHEMA> format.
1530
$init_value - (optional) An initial value to set
1531
the column to. Should already be SQL-quoted
1533
Returns: An array of SQL statements.
1537
my ($self, $table, $column, $definition, $init_value) = @_;
1539
push(@statements, "ALTER TABLE $table ADD COLUMN $column " .
1540
$self->get_type_ddl($definition));
1542
# XXX - Note that although this works for MySQL, most databases will fail
1543
# before this point, if we haven't set a default.
1544
(push(@statements, "UPDATE $table SET $column = $init_value"))
1545
if defined $init_value;
1547
return (@statements);
1550
sub get_add_index_ddl {
1552
=item C<get_add_index_ddl>
1554
Description: Gets SQL for creating an index.
1555
NOTE: Subclasses should not override this function. Instead,
1556
if they need to specify a custom CREATE INDEX statement,
1557
they should override C<_get_create_index_ddl>
1558
Params: $table - The name of the table the index will be on.
1559
$name - The name of the new index.
1560
$definition - An index definition. Either a hashref
1561
with FIELDS and TYPE or an arrayref
1562
containing a list of columns.
1563
Returns: An array of SQL statements that will create the
1568
my ($self, $table, $name, $definition) = @_;
1570
my ($index_fields, $index_type);
1571
# Index defs can be arrays or hashes
1572
if (ref($definition) eq 'HASH') {
1573
$index_fields = $definition->{FIELDS};
1574
$index_type = $definition->{TYPE};
1576
$index_fields = $definition;
1580
return $self->_get_create_index_ddl($table, $name, $index_fields,
1584
sub get_alter_column_ddl {
1586
=item C<get_alter_column_ddl($table, $column, \%definition)>
1588
Description: Generate SQL to alter a column in a table.
1589
The column that you are altering must exist,
1590
and the table that it lives in must exist.
1591
Params: $table - The table containing the column.
1592
$column - The name of the column being changed.
1593
\%definition - The new definition for the column,
1594
in standard C<ABSTRACT_SCHEMA> format.
1595
$set_nulls_to - A value to set NULL values to, if
1596
your new definition is NOT NULL and contains
1597
no DEFAULT, and when there is a possibility
1598
that the column could contain NULLs. $set_nulls_to
1599
should be already SQL-quoted if necessary.
1600
Returns: An array of SQL statements.
1604
my ($self, $table, $column, $new_def, $set_nulls_to) = @_;
1607
my $old_def = $self->get_column_abstract($table, $column);
1608
my $specific = $self->{db_specific};
1610
# If the types have changed, we have to deal with that.
1611
if (uc(trim($old_def->{TYPE})) ne uc(trim($new_def->{TYPE}))) {
1612
push(@statements, $self->_get_alter_type_sql($table, $column,
1613
$new_def, $old_def));
1616
my $default = $new_def->{DEFAULT};
1617
my $default_old = $old_def->{DEFAULT};
1618
# This first condition prevents "uninitialized value" errors.
1619
if (!defined $default && !defined $default_old) {
1622
# If we went from having a default to not having one
1623
elsif (!defined $default && defined $default_old) {
1624
push(@statements, "ALTER TABLE $table ALTER COLUMN $column"
1627
# If we went from no default to a default, or we changed the default.
1628
elsif ( (defined $default && !defined $default_old) ||
1629
($default ne $default_old) )
1631
$default = $specific->{$default} if exists $specific->{$default};
1632
push(@statements, "ALTER TABLE $table ALTER COLUMN $column "
1633
. " SET DEFAULT $default");
1636
# If we went from NULL to NOT NULL.
1637
if (!$old_def->{NOTNULL} && $new_def->{NOTNULL}) {
1639
# Handle any fields that were NULL before, if we have a default,
1640
$setdefault = $new_def->{DEFAULT} if exists $new_def->{DEFAULT};
1641
# But if we have a set_nulls_to, that overrides the DEFAULT
1642
# (although nobody would usually specify both a default and
1644
$setdefault = $set_nulls_to if defined $set_nulls_to;
1645
if (defined $setdefault) {
1646
push(@statements, "UPDATE $table SET $column = $setdefault"
1647
. " WHERE $column IS NULL");
1649
push(@statements, "ALTER TABLE $table ALTER COLUMN $column"
1652
# If we went from NOT NULL to NULL
1653
elsif ($old_def->{NOTNULL} && !$new_def->{NOTNULL}) {
1654
push(@statements, "ALTER TABLE $table ALTER COLUMN $column"
1655
. " DROP NOT NULL");
1658
# If we went from not being a PRIMARY KEY to being a PRIMARY KEY.
1659
if (!$old_def->{PRIMARYKEY} && $new_def->{PRIMARYKEY}) {
1660
push(@statements, "ALTER TABLE $table ADD PRIMARY KEY ($column)");
1662
# If we went from being a PK to not being a PK
1663
elsif ( $old_def->{PRIMARYKEY} && !$new_def->{PRIMARYKEY} ) {
1664
push(@statements, "ALTER TABLE $table DROP PRIMARY KEY");
1670
sub get_drop_index_ddl {
1672
=item C<get_drop_index_ddl($table, $name)>
1674
Description: Generates SQL statements to drop an index.
1675
Params: $table - The table the index is on.
1676
$name - The name of the index being dropped.
1677
Returns: An array of SQL statements.
1681
my ($self, $table, $name) = @_;
1683
# Although ANSI SQL-92 doesn't specify a method of dropping an index,
1684
# many DBs support this syntax.
1685
return ("DROP INDEX $name");
1688
sub get_drop_column_ddl {
1690
=item C<get_drop_column_ddl($table, $column)>
1692
Description: Generate SQL to drop a column from a table.
1693
Params: $table - The table containing the column.
1694
$column - The name of the column being dropped.
1695
Returns: An array of SQL statements.
1699
my ($self, $table, $column) = @_;
1700
return ("ALTER TABLE $table DROP COLUMN $column");
1703
=item C<get_drop_table_ddl($table)>
1705
Description: Generate SQL to drop a table from the database.
1706
Params: $table - The name of the table to drop.
1707
Returns: An array of SQL statements.
1711
sub get_drop_table_ddl {
1712
my ($self, $table) = @_;
1713
return ("DROP TABLE $table");
1716
sub get_rename_column_ddl {
1718
=item C<get_rename_column_ddl($table, $old_name, $new_name)>
1720
Description: Generate SQL to change the name of a column in a table.
1721
NOTE: ANSI SQL contains no simple way to rename a column,
1722
so this function is ABSTRACT and must be implemented
1724
Params: $table - The table containing the column to be renamed.
1725
$old_name - The name of the column being renamed.
1726
$new_name - The name the column is changing to.
1727
Returns: An array of SQL statements.
1731
die "ANSI SQL has no way to rename a column, and your database driver\n"
1732
. " has not implemented a method.";
1736
sub get_rename_table_sql {
1738
=item C<get_rename_table_sql>
1742
=item B<Description>
1744
Gets SQL to rename a table in the database.
1750
=item C<$old_name> - The current name of the table.
1752
=item C<$new_name> - The new name of the table.
1756
=item B<Returns>: An array of SQL statements to rename a table.
1762
my ($self, $old_name, $new_name) = @_;
1763
return ("ALTER TABLE $old_name RENAME TO $new_name");
1766
=item C<delete_table($name)>
1768
Description: Deletes a table from this Schema object.
1769
Dies if you try to delete a table that doesn't exist.
1770
Params: $name - The name of the table to delete.
1776
my ($self, $name) = @_;
1778
die "Attempted to delete nonexistent table '$name'." unless
1779
$self->get_table_abstract($name);
1781
delete $self->{abstract_schema}->{$name};
1782
delete $self->{schema}->{$name};
1785
sub get_column_abstract {
1787
=item C<get_column_abstract($table, $column)>
1789
Description: A column definition from the abstract internal schema.
1790
cross-database format.
1791
Params: $table - The name of the table
1792
$column - The name of the column that you want
1793
Returns: A hash reference. For the format, see the docs for
1795
Returns undef if the column or table does not exist.
1799
my ($self, $table, $column) = @_;
1801
# Prevent a possible dereferencing of an undef hash, if the
1802
# table doesn't exist.
1803
if ($self->get_table_abstract($table)) {
1804
my %fields = (@{ $self->{abstract_schema}{$table}{FIELDS} });
1805
return $fields{$column};
1810
=item C<get_indexes_on_column_abstract($table, $column)>
1812
Description: Gets a list of indexes that are on a given column.
1813
Params: $table - The table the column is on.
1814
$column - The name of the column.
1815
Returns: Indexes in the standard format of an INDEX
1816
entry on a table. That is, key-value pairs
1817
where the key is the index name and the value
1818
is the index definition.
1819
If there are no indexes on that column, we return
1824
sub get_indexes_on_column_abstract {
1825
my ($self, $table, $column) = @_;
1828
my $table_def = $self->get_table_abstract($table);
1829
if ($table_def && exists $table_def->{INDEXES}) {
1830
my %indexes = (@{ $table_def->{INDEXES} });
1831
foreach my $index_name (keys %indexes) {
1833
# Get the column list, depending on whether the index
1834
# is in hashref or arrayref format.
1835
if (ref($indexes{$index_name}) eq 'HASH') {
1836
$col_list = $indexes{$index_name}->{FIELDS};
1838
$col_list = $indexes{$index_name};
1841
if(grep($_ eq $column, @$col_list)) {
1842
$ret_hash{$index_name} = dclone($indexes{$index_name});
1850
sub get_index_abstract {
1852
=item C<get_index_abstract($table, $index)>
1854
Description: Returns an index definition from the internal abstract schema.
1855
Params: $table - The table the index is on.
1856
$index - The name of the index.
1857
Returns: A hash reference representing an index definition.
1858
See the C<ABSTRACT_SCHEMA> docs for details.
1859
Returns undef if the index does not exist.
1863
my ($self, $table, $index) = @_;
1865
# Prevent a possible dereferencing of an undef hash, if the
1866
# table doesn't exist.
1867
my $index_table = $self->get_table_abstract($table);
1868
if ($index_table && exists $index_table->{INDEXES}) {
1869
my %indexes = (@{ $index_table->{INDEXES} });
1870
return $indexes{$index};
1875
=item C<get_table_abstract($table)>
1877
Description: Gets the abstract definition for a table in this Schema
1879
Params: $table - The name of the table you want a definition for.
1880
Returns: An abstract table definition, or undef if the table doesn't
1885
sub get_table_abstract {
1886
my ($self, $table) = @_;
1887
return $self->{abstract_schema}->{$table};
1890
=item C<add_table($name, \%definition)>
1892
Description: Creates a new table in this Schema object.
1893
If you do not specify a definition, we will
1894
simply create an empty table.
1895
Params: $name - The name for the new table.
1896
\%definition (optional) - An abstract definition for
1903
my ($self, $name, $definition) = @_;
1904
(die "Table already exists: $name")
1905
if exists $self->{abstract_schema}->{$name};
1907
$self->{abstract_schema}->{$name} = dclone($definition);
1908
$self->{schema} = dclone($self->{abstract_schema});
1909
$self->_adjust_schema();
1912
$self->{abstract_schema}->{$name} = {FIELDS => []};
1913
$self->{schema}->{$name} = {FIELDS => []};
1921
=item C<rename_table>
1923
Renames a table from C<$old_name> to C<$new_name> in this Schema object.
1928
my ($self, $old_name, $new_name) = @_;
1929
my $table = $self->get_table_abstract($old_name);
1930
$self->delete_table($old_name);
1931
$self->add_table($new_name, $table);
1936
=item C<delete_column($table, $column)>
1938
Description: Deletes a column from this Schema object.
1939
Params: $table - Name of the table that the column is in.
1940
The table must exist, or we will fail.
1941
$column - Name of the column to delete.
1946
my ($self, $table, $column) = @_;
1948
my $abstract_fields = $self->{abstract_schema}{$table}{FIELDS};
1949
my $name_position = lsearch($abstract_fields, $column);
1950
die "Attempted to delete nonexistent column ${table}.${column}"
1951
if $name_position == -1;
1952
# Delete the key/value pair from the array.
1953
splice(@$abstract_fields, $name_position, 2);
1955
$self->{schema} = dclone($self->{abstract_schema});
1956
$self->_adjust_schema();
1961
=item C<rename_column($table, $old_name, $new_name)>
1963
Description: Renames a column on a table in the Schema object.
1964
The column that you are renaming must exist.
1965
Params: $table - The table the column is on.
1966
$old_name - The current name of the column.
1967
$new_name - The new name of hte column.
1972
my ($self, $table, $old_name, $new_name) = @_;
1973
my $def = $self->get_column_abstract($table, $old_name);
1974
die "Renaming a column that doesn't exist" if !$def;
1975
$self->delete_column($table, $old_name);
1976
$self->set_column($table, $new_name, $def);
1981
=item C<set_column($table, $column, \%new_def)>
1983
Description: Changes the definition of a column in this Schema object.
1984
If the column doesn't exist, it will be added.
1985
The table that you specify must already exist in the Schema.
1986
NOTE: This does not affect the database on the disk.
1987
Use the C<Bugzilla::DB> "Schema Modification Methods"
1988
if you want to do that.
1989
Params: $table - The name of the table that the column is on.
1990
$column - The name of the column.
1991
\%new_def - The new definition for the column, in
1992
C<ABSTRACT_SCHEMA> format.
1997
my ($self, $table, $column, $new_def) = @_;
1999
my $fields = $self->{abstract_schema}{$table}{FIELDS};
2000
$self->_set_object($table, $column, $new_def, $fields);
2005
=item C<set_index($table, $name, $definition)>
2007
Description: Changes the definition of an index in this Schema object.
2008
If the index doesn't exist, it will be added.
2009
The table that you specify must already exist in the Schema.
2010
NOTE: This does not affect the database on the disk.
2011
Use the C<Bugzilla::DB> "Schema Modification Methods"
2012
if you want to do that.
2013
Params: $table - The table the index is on.
2014
$name - The name of the index.
2015
$definition - A hashref or an arrayref. An index
2016
definition in C<ABSTRACT_SCHEMA> format.
2021
my ($self, $table, $name, $definition) = @_;
2023
if ( exists $self->{abstract_schema}{$table}
2024
&& !exists $self->{abstract_schema}{$table}{INDEXES} ) {
2025
$self->{abstract_schema}{$table}{INDEXES} = [];
2028
my $indexes = $self->{abstract_schema}{$table}{INDEXES};
2029
$self->_set_object($table, $name, $definition, $indexes);
2032
# A private helper for set_index and set_column.
2033
# This does the actual "work" of those two functions.
2034
# $array_to_change is an arrayref.
2036
my ($self, $table, $name, $definition, $array_to_change) = @_;
2038
my $obj_position = lsearch($array_to_change, $name) + 1;
2039
# If the object doesn't exist, then add it.
2040
if (!$obj_position) {
2041
push(@$array_to_change, $name);
2042
push(@$array_to_change, $definition);
2044
# We're modifying an existing object in the Schema.
2046
splice(@$array_to_change, $obj_position, 1, $definition);
2049
$self->{schema} = dclone($self->{abstract_schema});
2050
$self->_adjust_schema();
2053
=item C<delete_index($table, $name)>
2055
Description: Removes an index definition from this Schema object.
2056
If the index doesn't exist, we will fail.
2057
The table that you specify must exist in the Schema.
2058
NOTE: This does not affect the database on the disk.
2059
Use the C<Bugzilla::DB> "Schema Modification Methods"
2060
if you want to do that.
2061
Params: $table - The table the index is on.
2062
$name - The name of the index that we're removing.
2068
my ($self, $table, $name) = @_;
2070
my $indexes = $self->{abstract_schema}{$table}{INDEXES};
2071
my $name_position = lsearch($indexes, $name);
2072
die "Attempted to delete nonexistent index $name on the $table table"
2073
if $name_position == -1;
2074
# Delete the key/value pair from the array.
2075
splice(@$indexes, $name_position, 2);
2076
$self->{schema} = dclone($self->{abstract_schema});
2077
$self->_adjust_schema();
2082
=item C<columns_equal($col_one, $col_two)>
2084
Description: Tells you if two columns have entirely identical definitions.
2085
The TYPE field's value will be compared case-insensitive.
2086
However, all other fields will be case-sensitive.
2087
Params: $col_one, $col_two - The columns to compare. Hash
2088
references, in C<ABSTRACT_SCHEMA> format.
2089
Returns: C<1> if the columns are identical, C<0> if they are not.
2096
my $col_one = dclone(shift);
2097
my $col_two = dclone(shift);
2099
$col_one->{TYPE} = uc($col_one->{TYPE});
2100
$col_two->{TYPE} = uc($col_two->{TYPE});
2102
my @col_one_array = %$col_one;
2103
my @col_two_array = %$col_two;
2105
my ($removed, $added) = diff_arrays(\@col_one_array, \@col_two_array);
2107
# If there are no differences between the arrays,
2108
# then they are equal.
2109
return !scalar(@$removed) && !scalar(@$added) ? 1 : 0;
2113
=head1 SERIALIZATION/DESERIALIZATION
2117
=item C<serialize_abstract()>
2119
Description: Serializes the "abstract" schema into a format
2120
that deserialize_abstract() can read in. This is
2121
a method, called on a Schema instance.
2123
Returns: A scalar containing the serialized, abstract schema.
2124
Do not attempt to manipulate this data directly,
2125
as the format may change at any time in the future.
2126
The only thing you should do with the returned value
2127
is either store it somewhere (coupled with appropriate
2128
SCHEMA_VERSION) or deserialize it.
2132
sub serialize_abstract {
2135
# Make it ok to eval
2136
local $Data::Dumper::Purity = 1;
2139
local $Data::Dumper::Deepcopy = 1;
2141
# Always sort keys to allow textual compare
2142
local $Data::Dumper::Sortkeys = 1;
2144
return Dumper($self->{abstract_schema});
2147
=item C<deserialize_abstract($serialized, $version)>
2149
Description: Used for when you've read a serialized Schema off the disk,
2150
and you want a Schema object that represents that data.
2151
Params: $serialized - scalar. The serialized data.
2152
$version - A number in the format X.YZ. The "version"
2153
of the Schema that did the serialization.
2154
See the docs for C<SCHEMA_VERSION> for more details.
2155
Returns: A Schema object. It will have the methods of (and work
2156
in the same fashion as) the current version of Schema.
2157
However, it will represent the serialized data instead of
2161
sub deserialize_abstract {
2162
my ($class, $serialized, $version) = @_;
2165
if (int($version) < 2) {
2166
$thawed_hash = thaw($serialized);
2170
$cpt->reval($serialized) ||
2171
die "Unable to restore cached schema: " . $@;
2172
$thawed_hash = ${$cpt->varglob('VAR1')};
2175
return $class->new(undef, $thawed_hash);
2178
#####################################################################
2180
#####################################################################
2184
=head1 CLASS METHODS
2186
These methods are generally called on the class instead of on a specific
2191
=item C<get_empty_schema()>
2193
Description: Returns a Schema that has no tables. In effect, this
2194
Schema is totally "empty."
2196
Returns: A "empty" Schema object.
2202
sub get_empty_schema {
2204
return $class->deserialize_abstract(Dumper({}), SCHEMA_VERSION);
2211
=head1 ABSTRACT DATA TYPES
2213
The size and range data provided here is only
2214
intended as a guide. See your database's Bugzilla
2215
module (in this directory) for the most up-to-date
2216
values for these data types. The following
2217
abstract data types are used:
2223
Logical value 0 or 1 where 1 is true, 0 is false.
2227
Integer values (-128 - 127 or 0 - 255 unsigned).
2231
Integer values (-32,768 - 32767 or 0 - 65,535 unsigned).
2235
Integer values (-8,388,608 - 8,388,607 or 0 - 16,777,215 unsigned)
2239
Integer values (-2,147,483,648 - 2,147,483,647 or 0 - 4,294,967,295
2242
=item C<SMALLSERIAL>
2244
An auto-increment L</INT1>
2246
=item C<MEDIUMSERIAL>
2248
An auto-increment L</INT3>
2252
An auto-increment L</INT4>
2256
Variable length string of characters up to 255 (2^8 - 1) characters wide
2257
or more depending on the character set used.
2261
Variable length string of characters up to 16M (2^24 - 1) characters wide
2262
or more depending on the character set used.
2266
Variable length string of characters up to 64K (2^16 - 1) characters wide
2267
or more depending on the character set used.
2271
Variable length string of binary data up to 4M (2^32 - 1) bytes wide
2275
DATETIME support varies from database to database, however, it's generally
2276
safe to say that DATETIME entries support all date/time combinations greater
2277
than 1900-01-01 00:00:00. Note that the format used is C<YYYY-MM-DD hh:mm:ss>
2278
to be safe, though it's possible that your database may not require
2279
leading zeros. For greatest compatibility, however, please make sure dates
2280
are formatted as above for queries to guarantee consistent results.
2284
Database-specific subclasses should define the implementation for these data
2285
types as a hash reference stored internally in the schema object as
2286
C<db_specific>. This is typically done in overridden L<_initialize> method.
2288
The following abstract boolean values should also be defined on a
2289
database-specific basis:
2303
L<http://www.bugzilla.org/docs/developer.html#sql-schema>
added
removed
Bugzilla/DB/Schema.pm
