Attachment 147661 Details for Bug 32613 – Bug 32613: Add database tables to autocomplete

[patch] Bug 32613: Add database tables to autocomplete

Bug-32613-Add-database-tables-to-autocomplete.patch (text/plain), 5.39 KB, created by Jacob O'Mara on 2023-03-02 17:24:49 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Jacob O'Mara

Created: 2023-03-02 17:24:49 UTC

Size: 5.39 KB

patch

obsolete

>From 85dfb5eb40a47f4a706ab6230bf7ab18981af584 Mon Sep 17 00:00:00 2001
>From: Jacob O'Mara <jacob.omara@ptfs-europe.com>
>Date: Wed, 1 Mar 2023 11:02:34 +0000
>Subject: [PATCH] Bug 32613: Add database tables to autocomplete
>
>This patch adds a database call to get the table and column names in the
>database and parse them into the autocomplete engine.
>
>Test Plan:
>As previous commit but also check for column/table names in the
>autocomplete popup
>---
> .../modules/reports/guided_reports_start.tt   |  3 ++
> reports/guided_reports.pl                     | 33 ++++++++++++++++---
> 2 files changed, 31 insertions(+), 5 deletions(-)
>
>diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt
>index 79e5df8fd0a..0733fca6f47 100644
>--- a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt
>+++ b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt
>@@ -1563,6 +1563,9 @@
>                 },
>                 extraKeys: {"Tab": "autocomplete"}, //enable tab to accept auto-complete
>                 hint: CodeMirror.hint.sql,
>+                hintOptions: {
>+                    tables: [% To.json(tables) | $raw %]
>+                }
>             });
>             var ExcludedTriggerKeys = { //key-code combinations of keys that will not fire the auto-complete script
>                 "8": "backspace",
>diff --git a/reports/guided_reports.pl b/reports/guided_reports.pl
>index ab79b0f4638..fcd3cf22985 100755
>--- a/reports/guided_reports.pl
>+++ b/reports/guided_reports.pl
>@@ -163,16 +163,16 @@ elsif ( $phase eq 'Delete Multiple') {
> }
> 
> elsif ( $phase eq 'Delete Saved') {
>-	
>+
> 	# delete a report from the saved reports list
>     my $ids = $input->param('reports');
>     delete_report($ids);
>     print $input->redirect("/cgi-bin/koha/reports/guided_reports.pl?phase=Use%20saved");
> 	exit;
>-}		
>+}
> 
> elsif ( $phase eq 'Show SQL'){
>-	
>+
>     my $id = $input->param('reports');
>     my $report = Koha::Reports->find($id);
>     $template->param(
>@@ -192,6 +192,7 @@ elsif ( $phase eq 'Edit SQL'){
>     my $report = Koha::Reports->find($id);
>     my $group = $report->report_group;
>     my $subgroup  = $report->report_subgroup;
>+    my $tables = get_tables();
>     $template->param(
>         'sql'        => $report->savedsql,
>         'reportname' => $report->report_name,
>@@ -203,7 +204,8 @@ elsif ( $phase eq 'Edit SQL'){
>         'usecache' => $usecache,
>         'editsql'    => 1,
>         'mana_id' => $report->{mana_id},
>-        'mana_comments' => $report->{comments}
>+        'mana_comments' => $report->{comments},
>+        'tables' => $tables
>     );
> }
> 
>@@ -219,6 +221,7 @@ elsif ( $phase eq 'Update SQL'){
>     my $public = $input->param('public');
>     my $save_anyway = $input->param('save_anyway');
>     my @errors;
>+    my $tables = get_tables();
> 
>     # if we have the units, then we came from creating a report from SQL and thus need to handle converting units
>     if( $cache_expiry_units ){
>@@ -263,7 +266,7 @@ elsif ( $phase eq 'Update SQL'){
>                 'public' => $public,
>                 'problematic_authvals' => $problematic_authvals,
>                 'warn_authval_problem' => 1,
>-                'phase_update' => 1
>+                'phase_update' => 1,
>             );
> 
>         } else {
>@@ -288,6 +291,7 @@ elsif ( $phase eq 'Update SQL'){
>                 'cache_expiry'          => $cache_expiry,
>                 'public'                => $public,
>                 'usecache'              => $usecache,
>+                'tables'                => $tables
>             );
>             logaction( "REPORTS", "MODIFY", $id, "$reportname | $sql" ) if C4::Context->preference("ReportsLog");
>         }
>@@ -556,6 +560,7 @@ elsif ( $phase eq 'Save Report' ) {
>     my $cache_expiry_units = $input->param('cache_expiry_units');
>     my $public = $input->param('public');
>     my $save_anyway = $input->param('save_anyway');
>+    my $tables = get_tables();
> 
> 
>     # if we have the units, then we came from creating a report from SQL and thus need to handle converting units
>@@ -640,6 +645,7 @@ elsif ( $phase eq 'Save Report' ) {
>                 'cache_expiry' => $cache_expiry,
>                 'public' => $public,
>                 'usecache' => $usecache,
>+                'tables' => $tables
>             );
>         }
>     }
>@@ -998,6 +1004,8 @@ elsif ( $phase eq 'Create report from SQL' || $phase eq 'Create report from exis
>         $notes      = $report->notes // '';
>     }
> 
>+    my $tables = get_tables();
>+
>     $template->param(
>         sql        => $sql,
>         reportname => $reportname,
>@@ -1007,6 +1015,7 @@ elsif ( $phase eq 'Create report from SQL' || $phase eq 'Create report from exis
>         'public' => '0',
>         'cache_expiry' => 300,
>         'usecache' => $usecache,
>+        'tables' => $tables,
> 
>     );
> }
>@@ -1024,6 +1033,20 @@ sub header_cell_loop {
>     return \@headers;
> }
> 
>+#get a list of available tables for auto-complete
>+sub get_tables {
>+    my $result = {};
>+    my $tables = C4::Reports::Guided->get_all_tables();
>+    for my $table (@{$tables}) {
>+        my $sql = "SHOW COLUMNS FROM $table";
>+        my $rows = C4::Context->dbh->selectall_arrayref($sql, { Slice => {} });
>+        for my $row (@{$rows}) {
>+            push @{$result->{$table}}, $row->{Field};
>+        }
>+    }
>+    return $result;
>+}
>+
> foreach (1..6) {
>      $template->{VARS}->{'build' . $_} and last;
> }
>-- 
>2.39.2

Actions: View | Diff | Splinter Review

Attachments on bug 32613: 147556 | 147557 | 147610 | 147611 | 147612 | 147660 | 147661 | 147672 | 147673 | 147674 | 147715 | 147716 | 147717 | 147795