gmnifaq

commit 4516bfc5a39f8ea8632cd1ed240d350454878c33
parent ac99b4092b057be8aeb3237c92a32329d931aa01
Author: René Wagner <rwagner@rw-net.de>
Date:   Tue, 19 Jan 2021 21:35:23 +0100

implement fts for searching

Diffstat:
M
data/data.sqlite.example
 | 
0

M
faqs.pl
 | 
16
++++++++++++----
A
scripts.sql
 | 
17
+++++++++++++++++
M
search.pl
 | 
12
++++++------
M
tags.pl
 | 
2
+-

5 files changed, 36 insertions(+), 11 deletions(-)
diff --git a/data/data.sqlite.example b/data/data.sqlite.example
Binary files differ.
diff --git a/faqs.pl b/faqs.pl
@@ -38,11 +38,11 @@ sub sql
 	}
 
 	if ( $query =~ /tag=([0-9]+)/i ) {
-		return "SELECT q.* FROM questions q JOIN tags_questions tq ON q.id = tq.q_id WHERE tq.t_id = $1";
+		return "SELECT q.* FROM questions q LEFT 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";
+		return "SELECT q.* FROM questions q LEFT JOIN tags_questions tq ON q.id = tq.q_id WHERE q.id = $1";
 	}
 	write_response('INTERNAL_SERVER_ERROR', 'CGI execution error', undef);
 }
@@ -53,17 +53,25 @@ sub faqs
 	my $dbh = DBI->connect($CONF{'dsn'}, '', '', { RaiseError => 1 }) or die $DBI::errstr;
 
 	my @rows = $dbh->selectall_array(sql());
-	$dbh->disconnect();
 
 	if ( !scalar @rows ) {
 		push @return, 'No faqs found!';
 	}
 	else {
 		foreach (@rows) {
-			push @return, sprintf("### %s", @$_[1]);
+			push @return, sprintf("## %s", @$_[1]);
+			my @tags = $dbh->selectall_array("SELECT id, name FROM tags t LEFT JOIN tags_questions tq ON tq.t_id = t.id WHERE tq.q_id = @$_[0];");
+			
+			if ( scalar @tags ) {
+				push @return, ('', 'Tags:');
+				foreach (@tags) {
+					push @return, sprintf("=> ./faqs.pl?tag=%d %s", @$_[0], @$_[1]);
+				}
+			}
 			push @return, ('', @$_[2], '');
 		}
 	}
+	$dbh->disconnect();
 	push @return, '';
 	return @return;
 }
diff --git a/scripts.sql b/scripts.sql
@@ -0,0 +1,17 @@
+select DISTINCT tags.*, count(tags_questions.q_id) as count from fts_data inner join tags on tags.id = fts_data.t_id
+left join tags_questions on tags_questions.t_id = tags.id
+where fts_data match '"text": "gemin"'
+ORDER BY rank
+
+select DISTINCT q_id, questions.question from fts_data inner join questions on questions.id = q_id
+where fts_data match '"text": "linux"'
+ORDER BY rank;
+
+-- fill fts_data
+INSERT INTO fts_data (q_id, text) SELECT id, question FROM questions;
+INSERT INTO fts_data (q_id, text) SELECT id, answer FROM questions;
+INSERT INTO fts_data (t_id, text) SELECT id, name FROM tags;
+
+-- create fts_data table
+CREATE VIRTUAL TABLE fts_data
+USING FTS5(t_id, q_id, text, tokenize = "porter unicode61")
diff --git a/search.pl b/search.pl
@@ -22,7 +22,7 @@ if (!defined($ENV{'SERVER_PROTOCOL'}) || $ENV{'SERVER_PROTOCOL'} ne 'GEMINI') {
 }
 
 my $query = lc(uri_unescape($ENV{'QUERY_STRING'}));
-if ($query eq '' || $query !~ /^[0-9a-z]*$/i)
+if ($query eq '' || $query !~ /^[0-9a-z\s]*$/i)
 {
 	write_response('INPUT', 'Search faqs for the following terms (separate by blank)', undef);
 }
@@ -42,15 +42,15 @@ sub search
 	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%'");
+	my @matchingtags = $dbh->selectall_array("SELECT DISTINCT t.*, count(tq.q_id) AS count FROM fts_data INNER JOIN tags t ON t.id = fts_data.t_id LEFT JOIN tags_questions tq ON tq.t_id = t.id WHERE fts_data MATCH '\"text\": \"$term\"' GROUP BY t.id ORDER BY rank");
+	my @matchingfaqs = $dbh->selectall_array("SELECT DISTINCT q_id, q.question FROM fts_data INNER JOIN questions q ON q.id = q_id WHERE fts_data MATCH '\"text\": \"$term\"' ORDER BY rank;");
 	$dbh->disconnect();
 
-	if ( !scalar(@matchingtags) && !scalar(@matchingfaqs)) {
+	if ( !scalar @matchingtags && !scalar @matchingfaqs) {
 		push @result, ('Sorry, we can\'t find an entry that matches your search data.', '');
 	}
 
-	if ( scalar(@matchingtags)) {
+	if ( scalar @matchingtags) {
 		push @result, ('## matching tags', '');
 		foreach (@matchingtags) {
 			push @result, sprintf("=> ./faqs.pl?tag=%d %s (%d entrys)", @$_[0], @$_[1], @$_[2]);
@@ -58,7 +58,7 @@ sub search
 		push @result, '';
 	}
 
-	if ( scalar(@matchingfaqs)) {
+	if ( scalar @matchingfaqs) {
 		push @result, ('## matching FAQs', '');
 		foreach (@matchingfaqs) {
 			push @result, sprintf("=> ./faqs.pl?faq=%d %s", @$_[0], @$_[1]);
diff --git a/tags.pl b/tags.pl
@@ -34,7 +34,7 @@ sub tags
 	my $dbh = DBI->connect($CONF{'dsn'}, '', '', { RaiseError => 1 }) or die $DBI::errstr;
 
 	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');
+	my @rows = $dbh->selectall_array('SELECT id, name, count(t_id) FROM tags t LEFT JOIN tags_questions tq ON tq.t_id = t.id GROUP BY t_id');
 	$dbh->disconnect();
 
 	if ( !scalar @rows ) {