From 208adcf3d16469c3d3f323458e30593faa17f990 Mon Sep 17 00:00:00 2001 From: Paul Holden Date: Fri, 19 Aug 2022 18:50:42 +0100 Subject: [PATCH] MDL-73938 reportbuilder: better Oracle support of long text columns. Specifically during column grouping/aggregation, we need to avoid CLOB datatype inconsistencies (ORA-00932). --- .../classes/reportbuilder/local/entities/badge.php | 11 +++++++++-- cohort/classes/local/entities/cohort.php | 9 ++++++++- course/classes/local/entities/course_category.php | 9 ++++++++- reportbuilder/classes/local/entities/course.php | 14 +++++++++++--- reportbuilder/classes/local/entities/user.php | 9 ++++++++- .../classes/local/helpers/user_profile_fields.php | 10 ++++++++-- tag/classes/reportbuilder/local/entities/tag.php | 9 ++++++++- 7 files changed, 60 insertions(+), 11 deletions(-) diff --git a/badges/classes/reportbuilder/local/entities/badge.php b/badges/classes/reportbuilder/local/entities/badge.php index 5fceb3980bc..339d5404358 100644 --- a/badges/classes/reportbuilder/local/entities/badge.php +++ b/badges/classes/reportbuilder/local/entities/badge.php @@ -92,6 +92,8 @@ class badge extends base { * @return column[] */ protected function get_all_columns(): array { + global $DB; + $badgealias = $this->get_table_alias('badge'); $contextalias = $this->get_table_alias('context'); @@ -107,6 +109,10 @@ class badge extends base { ->set_is_sortable(true); // Description (note, this column contains plaintext so requires no post-processing). + $descriptionfieldsql = "{$badgealias}.description"; + if ($DB->get_dbfamily() === 'oracle') { + $descriptionfieldsql = $DB->sql_order_by_text($descriptionfieldsql, 1024); + } $columns[] = (new column( 'description', new lang_string('description', 'core_badges'), @@ -114,7 +120,7 @@ class badge extends base { )) ->add_joins($this->get_joins()) ->set_type(column::TYPE_LONGTEXT) - ->add_field("{$badgealias}.description"); + ->add_field($descriptionfieldsql, 'description'); // Criteria. $columns[] = (new column( @@ -146,7 +152,8 @@ class badge extends base { ON {$contextalias}.contextlevel = " . CONTEXT_COURSE . " AND {$contextalias}.instanceid = {$badgealias}.courseid") ->set_type(column::TYPE_INTEGER) - ->add_fields("{$badgealias}.id, {$badgealias}.type, {$badgealias}.courseid, {$badgealias}.imagecaption") + ->add_fields("{$badgealias}.id, {$badgealias}.type, {$badgealias}.courseid") + ->add_field($DB->sql_cast_to_char("{$badgealias}.imagecaption"), 'imagecaption') ->add_fields(context_helper::get_preload_record_columns_sql($contextalias)) ->set_disabled_aggregation_all() ->add_callback(static function(int $badgeid, stdClass $badge): string { diff --git a/cohort/classes/local/entities/cohort.php b/cohort/classes/local/entities/cohort.php index df8f11a8cb5..a086ca81b03 100644 --- a/cohort/classes/local/entities/cohort.php +++ b/cohort/classes/local/entities/cohort.php @@ -88,6 +88,8 @@ class cohort extends base { * @return column[] */ protected function get_all_columns(): array { + global $DB; + $tablealias = $this->get_table_alias('cohort'); $contextalias = $this->get_table_alias('context'); @@ -130,6 +132,10 @@ class cohort extends base { ->set_is_sortable(true); // Description column. + $descriptionfieldsql = "{$tablealias}.description"; + if ($DB->get_dbfamily() === 'oracle') { + $descriptionfieldsql = $DB->sql_order_by_text($descriptionfieldsql, 1024); + } $columns[] = (new column( 'description', new lang_string('description'), @@ -138,7 +144,8 @@ class cohort extends base { ->add_joins($this->get_joins()) ->add_join("JOIN {context} {$contextalias} ON {$contextalias}.id = {$tablealias}.contextid") ->set_type(column::TYPE_LONGTEXT) - ->add_fields("{$tablealias}.description, {$tablealias}.descriptionformat, {$tablealias}.id, {$tablealias}.contextid") + ->add_field($descriptionfieldsql, 'description') + ->add_fields("{$tablealias}.descriptionformat, {$tablealias}.id, {$tablealias}.contextid") ->add_fields(context_helper::get_preload_record_columns_sql($contextalias)) ->add_callback(static function(?string $description, stdClass $cohort): string { global $CFG; diff --git a/course/classes/local/entities/course_category.php b/course/classes/local/entities/course_category.php index 53aa1452a37..4794cd2f154 100644 --- a/course/classes/local/entities/course_category.php +++ b/course/classes/local/entities/course_category.php @@ -88,6 +88,8 @@ class course_category extends base { * @return column[] */ protected function get_all_columns(): array { + global $DB; + $tablealias = $this->get_table_alias('course_categories'); $tablealiascontext = $this->get_table_alias('context'); @@ -157,6 +159,10 @@ class course_category extends base { ->set_is_sortable(true); // Description column (note we need to join/select from the context table in order to format the column). + $descriptionfieldsql = "{$tablealias}.description"; + if ($DB->get_dbfamily() === 'oracle') { + $descriptionfieldsql = $DB->sql_order_by_text($descriptionfieldsql, 1024); + } $columns[] = (new column( 'description', new lang_string('description'), @@ -165,7 +171,8 @@ class course_category extends base { ->add_joins($this->get_joins()) ->add_join($this->get_context_join()) ->set_type(column::TYPE_LONGTEXT) - ->add_fields("{$tablealias}.description, {$tablealias}.descriptionformat, {$tablealias}.id") + ->add_field($descriptionfieldsql, 'description') + ->add_fields("{$tablealias}.descriptionformat, {$tablealias}.id") ->add_fields(context_helper::get_preload_record_columns_sql($tablealiascontext)) ->add_callback(static function(?string $description, stdClass $category): string { global $CFG; diff --git a/reportbuilder/classes/local/entities/course.php b/reportbuilder/classes/local/entities/course.php index 3a01a16b436..aabadc09362 100644 --- a/reportbuilder/classes/local/entities/course.php +++ b/reportbuilder/classes/local/entities/course.php @@ -223,7 +223,8 @@ class course extends base { * @return column[] */ protected function get_all_columns(): array { - $columns = []; + global $DB; + $coursefields = $this->get_course_fields(); $tablealias = $this->get_table_alias('course'); $contexttablealias = $this->get_table_alias('context'); @@ -269,14 +270,21 @@ class course extends base { } foreach ($coursefields as $coursefield => $coursefieldlang) { + $columntype = $this->get_course_field_type($coursefield); + + $columnfieldsql = "{$tablealias}.{$coursefield}"; + if ($columntype === column::TYPE_LONGTEXT && $DB->get_dbfamily() === 'oracle') { + $columnfieldsql = $DB->sql_order_by_text($columnfieldsql, 1024); + } + $column = (new column( $coursefield, $coursefieldlang, $this->get_entity_name() )) ->add_joins($this->get_joins()) - ->set_type($this->get_course_field_type($coursefield)) - ->add_field("$tablealias.$coursefield") + ->set_type($columntype) + ->add_field($columnfieldsql, $coursefield) ->add_callback([$this, 'format'], $coursefield) ->set_is_sortable($this->is_sortable($coursefield)); diff --git a/reportbuilder/classes/local/entities/user.php b/reportbuilder/classes/local/entities/user.php index fa9e371f4f1..119f099d21f 100644 --- a/reportbuilder/classes/local/entities/user.php +++ b/reportbuilder/classes/local/entities/user.php @@ -164,6 +164,8 @@ class user extends base { * @return column[] */ protected function get_all_columns(): array { + global $DB; + $usertablealias = $this->get_table_alias('user'); $contexttablealias = $this->get_table_alias('context'); @@ -275,14 +277,19 @@ class user extends base { foreach ($userfields as $userfield => $userfieldlang) { $columntype = $this->get_user_field_type($userfield); + $columnfieldsql = "{$usertablealias}.{$userfield}"; + if ($columntype === column::TYPE_LONGTEXT && $DB->get_dbfamily() === 'oracle') { + $columnfieldsql = $DB->sql_order_by_text($columnfieldsql, 1024); + } + $column = (new column( $userfield, $userfieldlang, $this->get_entity_name() )) ->add_joins($this->get_joins()) - ->add_field("{$usertablealias}.{$userfield}") ->set_type($columntype) + ->add_field($columnfieldsql, $userfield) ->set_is_sortable($this->is_sortable($userfield)) ->add_callback([$this, 'format'], $userfield); diff --git a/reportbuilder/classes/local/helpers/user_profile_fields.php b/reportbuilder/classes/local/helpers/user_profile_fields.php index cea9e225f64..119529213b7 100644 --- a/reportbuilder/classes/local/helpers/user_profile_fields.php +++ b/reportbuilder/classes/local/helpers/user_profile_fields.php @@ -118,13 +118,19 @@ class user_profile_fields { * @return column[] */ public function get_columns(): array { - $columns = []; + global $DB; + $columns = []; foreach ($this->userprofilefields as $profilefield) { $userinfotablealias = database::generate_alias(); $columntype = $this->get_user_field_type($profilefield->field->datatype); + $columnfieldsql = "{$userinfotablealias}.data"; + if ($DB->get_dbfamily() === 'oracle') { + $columnfieldsql = $DB->sql_order_by_text($columnfieldsql, 1024); + } + $column = (new column( 'profilefield_' . core_text::strtolower($profilefield->field->shortname), new lang_string('customfieldcolumn', 'core_reportbuilder', @@ -136,7 +142,7 @@ class user_profile_fields { ->add_join("LEFT JOIN {user_info_data} {$userinfotablealias} " . "ON {$userinfotablealias}.userid = {$this->usertablefieldalias} " . "AND {$userinfotablealias}.fieldid = {$profilefield->fieldid}") - ->add_field("{$userinfotablealias}.data") + ->add_field($columnfieldsql, 'data') ->set_type($columntype) ->set_is_sortable($columntype !== column::TYPE_LONGTEXT) ->add_callback([$this, 'format_profile_field'], $profilefield); diff --git a/tag/classes/reportbuilder/local/entities/tag.php b/tag/classes/reportbuilder/local/entities/tag.php index f15d18691f7..ea93d2a46b6 100644 --- a/tag/classes/reportbuilder/local/entities/tag.php +++ b/tag/classes/reportbuilder/local/entities/tag.php @@ -83,6 +83,8 @@ class tag extends base { * @return column[] */ protected function get_all_columns(): array { + global $DB; + $tagalias = $this->get_table_alias('tag'); // Name. @@ -121,6 +123,10 @@ class tag extends base { }); // Description. + $descriptionfieldsql = "{$tagalias}.description"; + if ($DB->get_dbfamily() === 'oracle') { + $descriptionfieldsql = $DB->sql_order_by_text($descriptionfieldsql, 1024); + } $columns[] = (new column( 'description', new lang_string('tagdescription', 'core_tag'), @@ -128,7 +134,8 @@ class tag extends base { )) ->add_joins($this->get_joins()) ->set_type(column::TYPE_LONGTEXT) - ->add_fields("{$tagalias}.description, {$tagalias}.descriptionformat, {$tagalias}.id") + ->add_field($descriptionfieldsql, 'description') + ->add_fields("{$tagalias}.descriptionformat, {$tagalias}.id") ->add_callback(static function(?string $description, stdClass $tag): string { global $CFG; require_once("{$CFG->libdir}/filelib.php"); -- 2.43.0