6 foreach my $name ( qw(Groups1 Groups2 Groups3) ) {
7 my ($status, $msg) = $RT::Handle->DropIndexIfExists(
8 Table => 'Groups', Name => $name,
10 my $method = $status ? 'debug' : 'warning';
11 RT->Logger->$method($msg);
14 my ($name, $msg) = $RT::Handle->CreateIndex(
16 Columns => [qw(Domain Type Instance)],
17 CaseInsensitive => { domain => 1, type => 1 },
19 my $method = $name ? 'debug' : 'warning';
20 RT->Logger->$method($msg);
22 ($name, $msg) = $RT::Handle->CreateIndex(
24 Columns => [qw(Domain Name Instance)],
25 CaseInsensitive => { domain => 1, name => 1 },
27 $method = $name ? 'debug' : 'warning';
28 RT->Logger->$method($msg);
30 ($name, $msg) = $RT::Handle->CreateIndex(
32 Columns => [qw(Instance)],
34 $method = $name ? 'debug' : 'warning';
35 RT->Logger->$method($msg);
39 my ($table, $column) = (@_);
41 my $collection_class = "RT::$table";
42 my $record_class = $collection_class;
43 $record_class =~ s/s$//;
47 my $cs = $RT::Handle->CaseSensitive;
49 $sql = "SELECT DISTINCT LOWER(t1.$column) FROM $table t1, $table t2"
50 ." WHERE LOWER(t1.$column) = LOWER(t2.$column)"
51 .' AND t1.id != t2.id';
53 $sql = "SELECT DISTINCT t1.$column FROM $table t1, $table t2"
54 ." WHERE t1.$column = t2.$column"
55 .' AND t1.id != t2.id';
58 my $dbh = $RT::Handle->dbh;
59 my $sth = $dbh->prepare($sql);
63 while ( my ($value) = $sth->fetchrow_array ) {
66 my $ids = $dbh->selectcol_arrayref(
67 "SELECT id FROM $table WHERE ". ($cs? "LOWER($column)" : $column) ." = LOWER(?)",
75 foreach my $id ( @$ids ) {
76 RT->Logger->debug("Changing $column of $record_class #". $id );
77 $dbh->do("UPDATE $table SET $column = ? WHERE id = ?", undef, $value . '-dup-'.$id, $id);
83 "Records in $table table had non-unique values in $column column."
84 ." $column has been changed for such records, and now matches '%-dup-%'"
89 # a few case insensitive and unique indexes
92 { Table => 'Queues', Column => 'Name' },
93 { Table => 'Users', Column => 'Name' },
95 foreach my $e (@list) {
96 RT->Logger->debug("Checking index on ". $e->{'Column'} ." in ". $e->{'Table'} );
97 my (@indexes) = $RT::Handle->IndexesThatBeginWith(
98 Table => $e->{'Table'}, Columns => [$e->{'Column'}]
100 @indexes = grep {@{$_->{'Columns'}} == 1} @indexes;
101 if (grep {$_->{Unique} && ($RT::Handle->CaseSensitive? $_->{'CaseInsensitive'}{ lc $e->{'Column'} } : 1 ) } @indexes
103 RT->Logger->debug("Required index exists. Skipping.");
107 $dedup->( $e->{'Table'}, $e->{'Column'} );
109 for my $index ( @indexes ) {
110 my ($status, $msg) = $RT::Handle->DropIndex(
111 Table => $e->{'Table'}, Name => $index->{'Name'},
113 my $method = $status ? 'debug' : 'warning';
114 RT->Logger->$method($msg);
117 my ($status, $msg) = $RT::Handle->CreateIndex(
118 Table => $e->{'Table'}, Columns => [$e->{'Column'}],
119 Unique => 1, CaseInsensitive => { lc $e->{'Column'} => 1 },
121 my $method = $status ? 'debug' : 'warning';
122 RT->Logger->$method($msg);
126 # cached group members
128 $RT::Handle->MakeSureIndexExists(
129 Table => 'CachedGroupMembers',
130 Columns => ['MemberId', 'ImmediateParentId'],
132 $RT::Handle->MakeSureIndexExists(
133 Table => 'CachedGroupMembers',
134 Columns => ['MemberId', 'GroupId'],
135 Optional => ['Disabled'],
137 $RT::Handle->DropIndexesThatArePrefix(
138 Table => 'CachedGroupMembers',
139 Columns => ['MemberId', 'GroupId', 'Disabled'],
141 $RT::Handle->MakeSureIndexExists(
142 Table => 'CachedGroupMembers',
143 Columns => ['GroupId', 'MemberId'],
144 Optional => ['Disabled'],
146 $RT::Handle->DropIndexesThatArePrefix(
147 Table => 'CachedGroupMembers',
148 Columns => ['GroupId', 'MemberId', 'Disabled'],
152 # drop indexes that start with 'id' column
153 foreach my $table ('Users', 'Tickets') {
154 my @list = $RT::Handle->IndexesThatBeginWith(
155 Table => $table, Columns => ['id'],
157 @list = grep @{ $_->{'Columns'} } > 1, @list;
159 foreach my $index (@list) {
160 my ($status, $msg) = $RT::Handle->DropIndex(
161 Table => $table, Name => $index->{'Name'},
163 my $method = $status ? 'debug' : 'warning';
164 RT->Logger->$method($msg);