Mailing List Archive

This is an automated email from the git hooks/post-receive script. It was
generated because a ref change was pushed to the repository containing
the project "rt".

The branch, 5.0/WIP-improve-text-indexes has been created
at cb3450154f87558803c1c7910366f617815af868 (commit)

- Log -----------------------------------------------------------------
commit cb3450154f87558803c1c7910366f617815af868
Author: Brian Conry <bconry@bestpractical.com>
Date: Wed Mar 30 14:03:10 2022 -0500

Oracle: Update indexes on char columns to use LOWER(column)

Now that we're searching char columns with LOWER(), if the indexes in
Oracle aren't built using LOWER() then they won't be used.

diff --git a/etc/schema.Oracle b/etc/schema.Oracle
index 0f47aee45e..09f0edc103 100644
--- a/etc/schema.Oracle
+++ b/etc/schema.Oracle
@@ -17,7 +17,7 @@ CREATE TABLE Attachments (
);
CREATE INDEX Attachments2 ON Attachments (TransactionId);
CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId);
-CREATE INDEX Attachments4 ON Attachments (Filename);
+CREATE INDEX Attachments4 ON Attachments (LOWER(Filename));


CREATE SEQUENCE QUEUES_seq;
@@ -56,10 +56,10 @@ CREATE TABLE Links (
Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
Created DATE
);
-CREATE UNIQUE INDEX Links1 ON Links (Base, Target, Type);
-CREATE INDEX Links2 ON Links (Base, Type);
-CREATE INDEX Links3 ON Links (Target, Type);
-CREATE INDEX Links4 ON Links(Type,LocalBase);
+CREATE UNIQUE INDEX Links1 ON Links (LOWER(Base), LOWER(Target), LOWER(Type));
+CREATE INDEX Links2 ON Links (LOWER(Base), LOWER(Type));
+CREATE INDEX Links3 ON Links (LOWER(Target), LOWER(Type));
+CREATE INDEX Links4 ON Links(LOWER(Type), LocalBase);


CREATE SEQUENCE PRINCIPALS_seq;
@@ -122,7 +122,7 @@ CREATE TABLE Transactions (
Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
Created DATE
);
-CREATE INDEX Transactions1 ON Transactions (ObjectType, ObjectId);
+CREATE INDEX Transactions1 ON Transactions (LOWER(ObjectType), ObjectId);


CREATE SEQUENCE SCRIPS_seq;
@@ -172,7 +172,7 @@ CREATE TABLE ACL (
LastUpdatedBy NUMBER(11,0) DEFAULT 0 NOT NULL,
LastUpdated DATE
);
-CREATE INDEX ACL1 ON ACL(RightName, ObjectType, ObjectId, PrincipalType, PrincipalId);
+CREATE INDEX ACL1 ON ACL(LOWER(RightName), LOWER(ObjectType), ObjectId, LOWER(PrincipalType), PrincipalId);


CREATE SEQUENCE GROUPMEMBERS_seq;
@@ -273,7 +273,7 @@ CREATE TABLE Tickets (
);
CREATE INDEX Tickets1 ON Tickets (Queue, LOWER(Status));
CREATE INDEX Tickets2 ON Tickets (Owner);
-CREATE INDEX Tickets6 ON Tickets (EffectiveId, Type);
+CREATE INDEX Tickets6 ON Tickets (EffectiveId, LOWER(Type));


CREATE SEQUENCE SCRIPACTIONS_seq;
@@ -342,8 +342,8 @@ CREATE TABLE ObjectCustomFieldValues (
Disabled NUMBER(11,0) DEFAULT 0 NOT NULL
);

-CREATE INDEX ObjectCustomFieldValues1 ON ObjectCustomFieldValues (Content);
-CREATE INDEX ObjectCustomFieldValues2 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId);
+CREATE INDEX ObjectCustomFieldValues1 ON ObjectCustomFieldValues (LOWER(Content));
+CREATE INDEX ObjectCustomFieldValues2 ON ObjectCustomFieldValues (CustomField,LOWER(ObjectType),ObjectId);

CREATE SEQUENCE CUSTOMFIELDS_seq;
CREATE TABLE CustomFields (
@@ -402,8 +402,8 @@ CREATE TABLE Attributes (
LastUpdated DATE
);

-CREATE INDEX Attributes1 on Attributes(Name);
-CREATE INDEX Attributes2 on Attributes(ObjectType, ObjectId);
+CREATE INDEX Attributes1 on Attributes(LOWER(Name));
+CREATE INDEX Attributes2 on Attributes(LOWER(ObjectType), ObjectId);


CREATE TABLE sessions (
diff --git a/etc/upgrade/5.0.3/schema.Oracle b/etc/upgrade/5.0.3/schema.Oracle
index 2d76c9de8b..390b4f222c 100644
--- a/etc/upgrade/5.0.3/schema.Oracle
+++ b/etc/upgrade/5.0.3/schema.Oracle
@@ -2,3 +2,27 @@ DROP INDEX Tickets1;
CREATE INDEX Tickets1 ON Tickets (Queue, LOWER(Status));
DROP INDEX AssetsStatus;
CREATE INDEX AssetsStatus ON Assets (LOWER(Status));
+DROP INDEX ACL1;
+CREATE INDEX ACL1 ON ACL(LOWER(RightName), LOWER(ObjectType), ObjectId, LOWER(PrincipalType), PrincipalId);
+DROP INDEX Attachments4;
+CREATE INDEX Attachments4 ON Attachments (LOWER(Filename));
+DROP INDEX Attributes1;
+CREATE INDEX Attributes1 on Attributes(LOWER(Name));
+DROP INDEX Attributes2;
+CREATE INDEX Attributes2 on Attributes(LOWER(ObjectType), ObjectId);
+DROP INDEX Links1;
+CREATE UNIQUE INDEX Links1 ON Links (LOWER(Base), LOWER(Target), LOWER(Type));
+DROP INDEX Links2;
+CREATE INDEX Links2 ON Links (LOWER(Base)), LOWER(Type));
+DROP INDEX Links3;
+CREATE INDEX Links3 ON Links (LOWER(Target), LOWER(Type));
+DROP INDEX Links4;
+CREATE INDEX Links4 ON Links(LOWER(Type), LocalBase);
+DROP INDEX ObjectCustomFieldValues1;
+CREATE INDEX ObjectCustomFieldValues1 ON ObjectCustomFieldValues (LOWER(Content));
+DROP INDEX ObjectCustomFieldValues2;
+CREATE INDEX ObjectCustomFieldValues2 ON ObjectCustomFieldValues (CustomField, LOWER(ObjectType), ObjectId);
+DROP INDEX Tickets6;
+CREATE INDEX Tickets6 ON Tickets (EffectiveId, LOWER(Type));
+DROP INDEX Transactions1;
+CREATE INDEX Transactions1 ON Transactions (LOWER(ObjectType), ObjectId);

commit 9251e1355e5c779046ea63eacc5526d97f0bd072
Author: Brian Conry <bconry@bestpractical.com>
Date: Wed Mar 30 13:10:45 2022 -0500

Pg: Update indexes on char columns to use LOWER(column)

Now that we're searching char columns with LOWER(), if the indexes in
PostgreSQL aren't built using LOWER() then they won't be used.

diff --git a/etc/schema.Pg b/etc/schema.Pg
index c86a710342..fe69bfd9f3 100644
--- a/etc/schema.Pg
+++ b/etc/schema.Pg
@@ -33,7 +33,7 @@ CREATE TABLE Attachments (
CREATE INDEX Attachments1 ON Attachments (Parent) ;
CREATE INDEX Attachments2 ON Attachments (TransactionId) ;
CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId) ;
-CREATE INDEX Attachments4 ON Attachments (Filename) ;
+CREATE INDEX Attachments4 ON Attachments (LOWER(Filename));



@@ -91,8 +91,8 @@ CREATE TABLE Links (
PRIMARY KEY (id)

);
-CREATE UNIQUE INDEX Links1 ON Links (Base, Target, Type) ;
-CREATE INDEX Links4 ON Links(Type,LocalBase);
+CREATE UNIQUE INDEX Links1 ON Links (LOWER(Base), LOWER(Target), LOWER(Type));
+CREATE INDEX Links4 ON Links(LOWER(Type), LocalBase);



@@ -202,7 +202,7 @@ CREATE TABLE Transactions (
PRIMARY KEY (id)

);
-CREATE INDEX Transactions1 ON Transactions (ObjectType, ObjectId);
+CREATE INDEX Transactions1 ON Transactions (LOWER(ObjectType), ObjectId);



@@ -278,7 +278,7 @@ CREATE TABLE ACL (

);

-CREATE INDEX ACL1 on ACL(RightName, ObjectType, ObjectId,PrincipalType,PrincipalId);
+CREATE INDEX ACL1 on ACL(LOWER(RightName), LOWER(ObjectType), ObjectId, LOWER(PrincipalType), PrincipalId);



@@ -514,8 +514,8 @@ CREATE TABLE ObjectCustomFieldValues (

);

-CREATE INDEX ObjectCustomFieldValues1 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId,Content);
-CREATE INDEX ObjectCustomFieldValues2 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId);
+CREATE INDEX ObjectCustomFieldValues1 ON ObjectCustomFieldValues (CustomField, LOWER(ObjectType), ObjectId, LOWER(Content));
+CREATE INDEX ObjectCustomFieldValues2 ON ObjectCustomFieldValues (CustomField, LOWER(ObjectType), ObjectId);



@@ -629,8 +629,8 @@ CREATE TABLE Attributes (

);

-CREATE INDEX Attributes1 on Attributes(Name);
-CREATE INDEX Attributes2 on Attributes(ObjectType, ObjectId);
+CREATE INDEX Attributes1 on Attributes(LOWER(Name));
+CREATE INDEX Attributes2 on Attributes(LOWER(ObjectType), ObjectId);



diff --git a/etc/upgrade/5.0.3/schema.Pg b/etc/upgrade/5.0.3/schema.Pg
index 01b4a93bb5..d2f6e856d3 100644
--- a/etc/upgrade/5.0.3/schema.Pg
+++ b/etc/upgrade/5.0.3/schema.Pg
@@ -4,3 +4,21 @@ DROP INDEX Tickets1;
CREATE INDEX Tickets1 ON Tickets (Queue, LOWER(Status));
DROP INDEX AssetsStatus;
CREATE INDEX AssetsStatus ON Assets (LOWER(Status));
+DROP INDEX ACL1;
+CREATE INDEX ACL1 on ACL(LOWER(RightName), LOWER(ObjectType), ObjectId, LOWER(PrincipalType), PrincipalId);
+DROP INDEX Attachments4;
+CREATE INDEX Attachments4 ON Attachments (LOWER(Filename));
+DROP INDEX Attributes1;
+CREATE INDEX Attributes1 on Attributes(LOWER(Name));
+DROP INDEX Attributes2;
+CREATE INDEX Attributes2 on Attributes(LOWER(ObjectType), ObjectId);
+DROP INDEX Links1;
+CREATE UNIQUE INDEX Links1 ON Links (LOWER(Base), LOWER(Target), LOWER(Type));
+DROP INDEX Links4;
+CREATE INDEX Links4 ON Links(LOWER(Type), LocalBase);
+DROP INDEX ObjectCUstomFieldValues1;
+CREATE INDEX ObjectCustomFieldValues1 ON ObjectCustomFieldValues (CustomField, LOWER(ObjectType), ObjectId, LOWER(Content));
+DROP INDEX ObjectCUstomFieldValues2;
+CREATE INDEX ObjectCustomFieldValues2 ON ObjectCustomFieldValues (CustomField, LOWER(ObjectType), ObjectId);
+DROP INDEX Transactions1;
+CREATE INDEX Transactions1 ON Transactions (LOWER(ObjectType), ObjectId);

commit 2bfe0538474e45acf817b21b162ad84d59c8f17c
Author: Brian Conry <bconry@bestpractical.com>
Date: Wed Mar 30 13:10:45 2022 -0500

Tickets/Assets Status always use case-insensitive

The code previously sometimes searched these columns case-sensitive and
sometimes case-insensitive. Both Oracle and Pg have case-sensitive
semantics, so sometimes the indexes were usable and sometimes they
weren't.

This change standardizes all searches using these columns to be
explicitly case-insensitive.

diff --git a/etc/schema.Oracle b/etc/schema.Oracle
index 324f790d19..0f47aee45e 100644
--- a/etc/schema.Oracle
+++ b/etc/schema.Oracle
@@ -271,7 +271,7 @@ CREATE TABLE Tickets (
Creator NUMBER(11,0) DEFAULT 0 NOT NULL,
Created DATE
);
-CREATE INDEX Tickets1 ON Tickets (Queue, Status);
+CREATE INDEX Tickets1 ON Tickets (Queue, LOWER(Status));
CREATE INDEX Tickets2 ON Tickets (Owner);
CREATE INDEX Tickets6 ON Tickets (EffectiveId, Type);

@@ -493,7 +493,7 @@ CREATE TABLE Assets (
);

CREATE INDEX AssetsName ON Assets (LOWER(Name));
-CREATE INDEX AssetsStatus ON Assets (Status);
+CREATE INDEX AssetsStatus ON Assets (LOWER(Status));
CREATE INDEX AssetsCatalog ON Assets (Catalog);

CREATE SEQUENCE Catalogs_seq;
diff --git a/etc/schema.Pg b/etc/schema.Pg
index 9f34ec4b0a..c86a710342 100644
--- a/etc/schema.Pg
+++ b/etc/schema.Pg
@@ -429,7 +429,7 @@ CREATE TABLE Tickets (

);

-CREATE INDEX Tickets1 ON Tickets (Queue, Status) ;
+CREATE INDEX Tickets1 ON Tickets (Queue, LOWER(Status));
CREATE INDEX Tickets2 ON Tickets (Owner) ;
CREATE INDEX Tickets3 ON Tickets (EffectiveId) ;

@@ -725,7 +725,7 @@ CREATE TABLE Assets (
);

CREATE INDEX AssetsName ON Assets (LOWER(Name));
-CREATE INDEX AssetsStatus ON Assets (Status);
+CREATE INDEX AssetsStatus ON Assets (LOWER(Status));
CREATE INDEX AssetsCatalog ON Assets (Catalog);

CREATE SEQUENCE catalogs_id_seq;
diff --git a/etc/upgrade/5.0.3/schema.Oracle b/etc/upgrade/5.0.3/schema.Oracle
new file mode 100644
index 0000000000..2d76c9de8b
--- /dev/null
+++ b/etc/upgrade/5.0.3/schema.Oracle
@@ -0,0 +1,4 @@
+DROP INDEX Tickets1;
+CREATE INDEX Tickets1 ON Tickets (Queue, LOWER(Status));
+DROP INDEX AssetsStatus;
+CREATE INDEX AssetsStatus ON Assets (LOWER(Status));
diff --git a/etc/upgrade/5.0.3/schema.Pg b/etc/upgrade/5.0.3/schema.Pg
index 628b446a75..01b4a93bb5 100644
--- a/etc/upgrade/5.0.3/schema.Pg
+++ b/etc/upgrade/5.0.3/schema.Pg
@@ -1,2 +1,6 @@
DROP INDEX Users4;
CREATE INDEX Users4 ON Users (LOWER(EmailAddress));
+DROP INDEX Tickets1;
+CREATE INDEX Tickets1 ON Tickets (Queue, LOWER(Status));
+DROP INDEX AssetsStatus;
+CREATE INDEX AssetsStatus ON Assets (LOWER(Status));
diff --git a/lib/RT/Assets.pm b/lib/RT/Assets.pm
index dad7d054ca..7ffa5afb64 100644
--- a/lib/RT/Assets.pm
+++ b/lib/RT/Assets.pm
@@ -317,6 +317,9 @@ sub Limit {
$self->{_sql_looking_at}{ lc $args{FIELD} } = 1
if $args{FIELD} and (not $args{ALIAS} or $args{ALIAS} eq "main");

+ $args{CASESENSITIVE} = 0
+ if $args{FIELD} and lc $args{FIELD} eq 'status' and (not $args{ALIAS} or $args{ALIAS} eq 'main');
+
$self->SUPER::Limit(%args);
}

diff --git a/lib/RT/Tickets.pm b/lib/RT/Tickets.pm
index 4ffbd6ab61..0a870df762 100644
--- a/lib/RT/Tickets.pm
+++ b/lib/RT/Tickets.pm
@@ -1649,6 +1649,9 @@ sub Limit {
$self->{_sql_looking_at}{ lc $args{FIELD} } = 1
if $args{FIELD} and (not $args{ALIAS} or $args{ALIAS} eq "main");

+ $args{CASESENSITIVE} = 0
+ if $args{FIELD} and lc $args{FIELD} eq 'status' and (not $args{ALIAS} or $args{ALIAS} eq 'main');
+
$self->SUPER::Limit(%args);
}

@@ -1757,10 +1760,11 @@ sub LimitStatus {
@_
);
$self->LimitField(
- FIELD => 'Status',
- VALUE => $args{'VALUE'},
- OPERATOR => $args{'OPERATOR'},
- DESCRIPTION => join( ' ',
+ FIELD => 'Status',
+ VALUE => $args{'VALUE'},
+ OPERATOR => $args{'OPERATOR'},
+ CASESENSITIVE => 0,
+ DESCRIPTION => join( ' ',
$self->loc('Status'), $args{'OPERATOR'},
$self->loc( $args{'VALUE'} ) ),
);

commit f782e929b38611132b5347d13209adf3bfaec226
Author: sunnavy <sunnavy@bestpractical.com>
Date: Thu Mar 31 05:27:48 2022 +0800

Support to search CLOB fields for Oracle

This is initially for RT::Dashboard::CurrentUserCanDelete, where we
check if current dashboard is used as system default by searching
Content field, which is a CLOB in Oracle. It's to fix errors like:

ORA-00932: inconsistent datatypes: expected - got CLOB

diff --git a/lib/RT/SearchBuilder.pm b/lib/RT/SearchBuilder.pm
index caf1d035b1..6c8c3d05cc 100644
--- a/lib/RT/SearchBuilder.pm
+++ b/lib/RT/SearchBuilder.pm
@@ -999,6 +999,16 @@ sub Limit {
$ARGS{'CASESENSITIVE'} = 1;
}

+ # Oracle doesn't support to directly compare CLOB with VARCHAR/INTEGER.
+ # DefaultDashboard search in RT::Dashboard::CurrentUserCanDelete needs this
+ if ( $ARGS{OPERATOR} !~ /IS/i
+ && !$ARGS{FUNCTION}
+ && RT->Config->Get('DatabaseType') eq 'Oracle'
+ && $self->RecordClass->_Accessible( $ARGS{FIELD}, 'is_blob' ) )
+ {
+ $ARGS{FUNCTION} = 'TO_CHAR(?)';
+ }
+
return $self->SUPER::Limit( %ARGS );
}


-----------------------------------------------------------------------


hooks/post-receive
--
rt
_______________________________________________
rt-commit mailing list
rt-commit@lists.bestpractical.com
https://lists.bestpractical.com/mailman/listinfo/rt-commit