From 45addaf266725c78d17bbde0180c728a4220e4c3 Mon Sep 17 00:00:00 2001 From: Jacob O'Mara 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 | 5 ++++ reports/guided_reports.pl | 23 ++++++++++++++++--- 2 files changed, 25 insertions(+), 3 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 d30c76e58cf..9b863a2df77 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 @@ -1547,6 +1547,8 @@ [% IF ( create || editsql || save ) %] + let tables = JSON.parse('[% To.json(tables) | $raw %]'); + var editor = CodeMirror.fromTextArea(sql, { lineNumbers: true, mode: "text/x-sql", @@ -1558,6 +1560,9 @@ }, extraKeys: {"Tab": "autocomplete"}, //enable tab to accept auto-complete hint: CodeMirror.hint.sql, + hintOptions: { + tables: tables + } }); 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..9fe12d6f062 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( @@ -998,6 +998,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 +1009,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 +1027,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