RT 4.2.11, ticket#13852

[freeside.git] / rt / etc / upgrade / 4.1.23 / indexes

diff --git a/rt/etc/upgrade/4.1.23/indexes b/rt/etc/upgrade/4.1.23/indexes
new file mode 100644 (file)
index 0000000..78db4ae
--- /dev/null
+++ b/rt/etc/upgrade/4.1.23/indexes
@@ -0,0 +1,168 @@
+use strict;
+use warnings;
+
+# groups table
+{
+    foreach my $name ( qw(Groups1 Groups2 Groups3) ) {
+        my ($status, $msg) = $RT::Handle->DropIndexIfExists(
+            Table => 'Groups', Name => $name,
+        );
+        my $method = $status ? 'debug' : 'warning';
+        RT->Logger->$method($msg);
+    }
+
+    my ($name, $msg) = $RT::Handle->CreateIndex(
+        Table => 'Groups',
+        Columns => [qw(Domain Type Instance)],
+        CaseInsensitive => { domain => 1, type => 1 },
+    );
+    my $method = $name ? 'debug' : 'warning';
+    RT->Logger->$method($msg);
+
+    ($name, $msg) = $RT::Handle->CreateIndex(
+        Table => 'Groups',
+        Columns => [qw(Domain Name Instance)],
+        CaseInsensitive => { domain => 1, name => 1 },
+    );
+    $method = $name ? 'debug' : 'warning';
+    RT->Logger->$method($msg);
+
+    ($name, $msg) = $RT::Handle->CreateIndex(
+        Table => 'Groups',
+        Columns => [qw(Instance)],
+    );
+    $method = $name ? 'debug' : 'warning';
+    RT->Logger->$method($msg);
+}
+
+my $dedup = sub {
+    my ($table, $column) = (@_);
+
+    my $collection_class = "RT::$table";
+    my $record_class = $collection_class;
+    $record_class =~ s/s$//;
+
+    my $sql;
+
+    my $cs = $RT::Handle->CaseSensitive;
+    if ($cs) {
+        $sql = "SELECT DISTINCT LOWER(t1.$column) FROM $table t1, $table t2"
+        ." WHERE LOWER(t1.$column) = LOWER(t2.$column)"
+        .' AND t1.id != t2.id';
+    } else {
+        $sql = "SELECT DISTINCT t1.$column FROM $table t1, $table t2"
+        ." WHERE t1.$column = t2.$column"
+        .' AND t1.id != t2.id';
+    }
+
+    my $dbh = $RT::Handle->dbh;
+    my $sth = $dbh->prepare($sql);
+    $sth->execute;
+
+    my $found = 0;
+    while ( my ($value) = $sth->fetchrow_array ) {
+        $found = 1;
+
+        my $ids = $dbh->selectcol_arrayref(
+            "SELECT id FROM $table WHERE ". ($cs? "LOWER($column)" : $column) ." = LOWER(?)",
+            undef,
+            $value
+        );
+
+        # skip first
+        shift @$ids;
+
+        foreach my $id ( @$ids ) {
+            RT->Logger->debug("Changing $column of $record_class #". $id );
+            $dbh->do("UPDATE $table SET $column = ? WHERE id = ?", undef,  $value . '-dup-'.$id, $id);
+        }
+    }
+
+    if ( $found ) {
+        RT->Logger->warning(
+            "Records in $table table had non-unique values in $column column."
+            ." $column has been changed for such records, and now matches '%-dup-%'"
+        );
+    }
+};
+
+# a few case insensitive and unique indexes
+{
+    my @list = (
+        { Table => 'Queues', Column => 'Name' },
+        { Table => 'Users', Column => 'Name' },
+    );
+    foreach my $e (@list) {
+        RT->Logger->debug("Checking index on ". $e->{'Column'} ." in ". $e->{'Table'} );
+        my (@indexes) = $RT::Handle->IndexesThatBeginWith(
+            Table => $e->{'Table'}, Columns => [$e->{'Column'}]
+        );
+        @indexes = grep {@{$_->{'Columns'}} == 1} @indexes;
+        if (grep {$_->{Unique} && ($RT::Handle->CaseSensitive? $_->{'CaseInsensitive'}{ lc $e->{'Column'} } : 1 ) } @indexes
+        ) {
+            RT->Logger->debug("Required index exists. Skipping.");
+            next;
+        }
+
+        $dedup->( $e->{'Table'}, $e->{'Column'} );
+
+        for my $index ( @indexes ) {
+            my ($status, $msg) = $RT::Handle->DropIndex(
+                Table => $e->{'Table'}, Name => $index->{'Name'},
+            );
+            my $method = $status ? 'debug' : 'warning';
+            RT->Logger->$method($msg);
+        }
+
+        my ($status, $msg) = $RT::Handle->CreateIndex(
+            Table => $e->{'Table'}, Columns => [$e->{'Column'}],
+            Unique => 1, CaseInsensitive => { lc $e->{'Column'} => 1 },
+        );
+        my $method = $status ? 'debug' : 'warning';
+        RT->Logger->$method($msg);
+    }
+}
+
+# cached group members
+{
+    $RT::Handle->MakeSureIndexExists(
+        Table => 'CachedGroupMembers',
+        Columns => ['MemberId', 'ImmediateParentId'],
+    );
+    $RT::Handle->MakeSureIndexExists(
+        Table => 'CachedGroupMembers',
+        Columns => ['MemberId', 'GroupId'],
+        Optional => ['Disabled'],
+    );
+    $RT::Handle->DropIndexesThatArePrefix(
+        Table => 'CachedGroupMembers',
+        Columns => ['MemberId', 'GroupId', 'Disabled'],
+    );
+    $RT::Handle->MakeSureIndexExists(
+        Table => 'CachedGroupMembers',
+        Columns => ['GroupId', 'MemberId'],
+        Optional => ['Disabled'],
+    );
+    $RT::Handle->DropIndexesThatArePrefix(
+        Table => 'CachedGroupMembers',
+        Columns => ['GroupId', 'MemberId', 'Disabled'],
+    );
+}
+
+# drop indexes that start with 'id' column
+foreach my $table ('Users', 'Tickets') {
+    my @list = $RT::Handle->IndexesThatBeginWith(
+        Table => $table, Columns => ['id'],
+    );
+    @list = grep @{ $_->{'Columns'} } > 1, @list;
+
+    foreach my $index (@list) {
+        my ($status, $msg) = $RT::Handle->DropIndex(
+            Table => $table, Name => $index->{'Name'},
+        );
+        my $method = $status ? 'debug' : 'warning';
+        RT->Logger->$method($msg);
+    }
+}
+
+1;