commit 6cc21c44f0e1ca40fcd3408ed4a3edae0c6c7b47
parent 4102d2a37da9349f1842a15a1c161e69a4cb2d16
Author: René Wagner <rwagner@rw-net.de>
Date: Tue, 24 Nov 2020 20:36:12 +0100
implement basic search
Diffstat:
6 files changed, 91 insertions(+), 27 deletions(-)
diff --git a/TODO.md b/TODO.md
@@ -1,10 +1,7 @@
# initial todo
- avoid serving of data files?!
-- implement display of question
- - single question
-- implement search
- - check soundslike search
-- URI:encode
+- search using FTS
+- URI:encode/decode for params
- built admin interface
- consolidate things
- header generation
diff --git a/data/data.sqlite.example b/data/data.sqlite.example
Binary files differ.
diff --git a/faqs.pl b/faqs.pl
@@ -40,35 +40,35 @@ sub sql
return "SELECT q.* FROM questions q JOIN tags_questions tq ON q.id = tq.q_id WHERE tq.t_id = $1";
}
+ if ( $query =~ /faq=([0-9]+)/i ) {
+ return "SELECT q.* FROM questions q JOIN tags_questions tq ON q.id = tq.q_id WHERE q.id = $1";
+ }
write_response('INTERNAL_SERVER_ERROR', 'CGI execution error', undef);
}
sub faqs
{
+ my @return;
my $dbh = DBI->connect($CONF{'dsn'}, '', '', { RaiseError => 1 }) or die $DBI::errstr;
- my @return;
- my $stmt = $dbh->prepare(sql());
- $stmt->execute();
-
- my $rows = $stmt->fetchall_arrayref;
+ my @rows = $dbh->selectall_array(sql());
$dbh->disconnect();
- if ( !scalar @$rows ) {
+ if ( !scalar @rows ) {
push @return, 'No faqs found!';
}
else {
- foreach (@$rows) {
+ foreach (@rows) {
push @return, sprintf("### %s", @$_[1]);
push @return, '';
push @return, @$_[2];
- push @return, '';
}
}
+ push @return, '';
return @return;
}
sub header
{
- return ( '# FAQs on '. $CONF{'name'}, '');
+ return ( '# '. $CONF{'name'}, '');
}
diff --git a/index.pl b/index.pl
@@ -30,7 +30,7 @@ exit;
sub body
{
- return ('## Meta', '', 'Search', '=> tags.pl Tags', '=> faqs.pl View all');
+ return ('## Meta', '', '=> search.pl Search', '=> tags.pl Tags', '=> faqs.pl View all', '');
}
sub header
@@ -41,5 +41,5 @@ sub header
my $faqcount = $dbh->selectrow_array("SELECT count(id) from questions");
$dbh->disconnect();
- return ('# Welcome to '. $CONF{'name'}, '', $CONF{'intro'}, '', sprintf('We are currently serving %d FAQs categorized with %d tags!', $faqcount, $tagcount), '');
+ return ('# '. $CONF{'name'}, '', $CONF{'intro'}, '', sprintf('We are currently serving %d FAQs categorized with %d tags!', $faqcount, $tagcount), '');
}
diff --git a/search.pl b/search.pl
@@ -0,0 +1,69 @@
+#!/usr/bin/perl
+# Copyright René Wagner 2020
+# licenced under BSD 3-Clause licence
+# https://git.sr.ht/~rwa/gmni-perl-cgi-demo
+
+use strict;
+use DBI;
+use lib 'lib/';
+use gmnifaq;
+
+config();
+
+# enable UTF-8 mode for everything
+use utf8;
+binmode STDOUT, ':utf8';
+binmode STDERR, ':utf8';
+
+if (!defined($ENV{'SERVER_PROTOCOL'}) || $ENV{'SERVER_PROTOCOL'} ne 'GEMINI') {
+ write_response('CGI_ERROR', 'CGI execution error', undef);
+}
+
+my $query = lc($ENV{'QUERY_STRING'});
+if ($query eq '')
+{
+ write_response('INPUT', 'Search faqs for the following terms (blank-space separated):', undef);
+}
+
+my @body = ();
+push @body, header();
+push @body, search($query);
+push @body, footer();
+
+write_response('SUCCESS', 'text/gemini', @body);
+
+exit;
+
+sub search
+{
+ my ($term) = @_;
+ my @result = ();
+
+ my $dbh = DBI->connect($CONF{'dsn'}, '', '', { RaiseError => 1 }) or die $DBI::errstr;
+ my @matchingtags = $dbh->selectall_array("SELECT id, name, count(t_id) FROM tags LEFT JOIN tags_questions ON tags_questions.t_id = tags.id WHERE name LIKE '%$term%' GROUP BY t_id");
+ my @matchingfaqs = $dbh->selectall_array("SELECT * FROM questions WHERE question LIKE '%$term%' OR answer LIKE '%$term%'");
+ $dbh->disconnect();
+
+ if ( scalar(@matchingtags)) {
+ push @result, '## matching tags';
+ push @result, '';
+ foreach (@matchingtags) {
+ push @result, sprintf("=> faqs.pl?tag=%d %s (%d entrys)", @$_[0], @$_[1], @$_[2]);
+ }
+ }
+
+ if ( scalar(@matchingfaqs)) {
+ push @result, '## matching tags';
+ push @result, '';
+ foreach (@matchingfaqs) {
+ push @result, sprintf("=> faqs.pl?faq=%d %s", @$_[0], @$_[1]);
+ }
+ }
+ push @result, '';
+ return @result;
+}
+
+sub header
+{
+ return ('# '. $CONF{'name'}, '', "Matching results for your search term '$query':", '');
+}
diff --git a/tags.pl b/tags.pl
@@ -32,25 +32,23 @@ sub tags
{
my $dbh = DBI->connect($CONF{'dsn'}, '', '', { RaiseError => 1 }) or die $DBI::errstr;
- my @tags;
- my $stmt = $dbh->prepare('SELECT id, name, count(t_id) FROM tags LEFT JOIN tags_questions ON tags_questions.t_id = tags.id GROUP BY t_id');
- $stmt->execute();
-
- my $rows = $stmt->fetchall_arrayref;
+ my @result;
+ my @rows = $dbh->selectall_array('SELECT id, name, count(t_id) FROM tags LEFT JOIN tags_questions ON tags_questions.t_id = tags.id GROUP BY t_id');
$dbh->disconnect();
- if ( !scalar @$rows ) {
- push @tags, 'No tags found!';
+ if ( !scalar @rows ) {
+ push @result, 'No tags found!';
}
else {
- foreach (@$rows) {
- push @tags, sprintf("=> faqs.pl?tag=%d %s (%d entrys)", @$_[0], @$_[1], @$_[2]);
+ foreach (@rows) {
+ push @result, sprintf("=> faqs.pl?tag=%d %s (%d entrys)", @$_[0], @$_[1], @$_[2]);
}
}
- return @tags;
+ push @result, '';
+ return @result;
}
sub header
{
- return ('# Welcome to '. $CONF{'name'}, '', 'Select a tag to browse the questions associated with this tag.', '', '## Tags', '');
+ return ('# '. $CONF{'name'}, '', 'Select a tag to browse the questions associated with this tag.', '', '## Tags', '');
}
