Attachment 147557 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), 3.37 KB, created by Jacob O'Mara on 2023-03-01 11:31:15 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Jacob O'Mara

Created: 2023-03-01 11:31:15 UTC

Size: 3.37 KB

patch

obsolete

>From 45addaf266725c78d17bbde0180c728a4220e4c3 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   |  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

Actions: View | Diff | Splinter Review

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