-*- mode: org -*-
#+TITLE: spine (doc_reform) hub
#+DESCRIPTION: documents - structuring, various output representations & search
#+FILETAGS: :spine:hub:
#+AUTHOR: Ralph Amissah
#+EMAIL: [[mailto:ralph.amissah@gmail.com][ralph.amissah@gmail.com]]
#+COPYRIGHT: Copyright (C) 2015 - 2020 Ralph Amissah
#+LANGUAGE: en
#+STARTUP: content hideblocks hidestars noindent entitiespretty
#+PROPERTY: header-args :exports code
#+PROPERTY: header-args+ :noweb yes
#+PROPERTY: header-args+ :eval no
#+PROPERTY: header-args+ :results no
#+PROPERTY: header-args+ :cache no
#+PROPERTY: header-args+ :padline no
[[../maker.org][maker.org makefile]] [[./][org/]]
* cgi search
cd util/d/cgi/search
dub --force --compiler=ldc2 && sudo cp -v cgi-bin/spine-search /usr/lib/cgi-bin/.
** 0. set program tangle
#+BEGIN_SRC d :tangle "../misc/util/d/cgi/search/src/spine_cgi_sqlite_search.d"
<<cgi_sqlite_head>>
<<cgi_sqlite_imports>>
void cgi_function_intro(Cgi cgi) {
<<cgi_sqlite_initialize>>
<<cgi_sqlite_header>>
<<cgi_sqlite_table>>
<<cgi_sqlite_form>>
<<cgi_sqlite_write>>
<<cgi_sqlite_set_db>>
<<cgi_sqlite_select_statement_0>>
<<cgi_sqlite_db_close>>
<<cgi_sqlite_db_tail>>
}
mixin GenericMain!cgi_function_intro;
#+END_SRC
** header
#+NAME: cgi_sqlite_head
#+BEGIN_SRC d
/+ dub.sdl
name "spine search"
description "spine cgi search"
+/
#+END_SRC
** imports
#+NAME: cgi_sqlite_imports
#+BEGIN_SRC d
import std.format;
import std.range;
import std.regex;
import arsd.cgi;
import d2sqlite3;
import std.process : environment;
#+END_SRC
** void main
*** initialize
#+NAME: cgi_sqlite_initialize
#+BEGIN_SRC d
string header;
string table;
string form;
#+END_SRC
**** config
#+NAME: cgi_sqlite_initialize
#+BEGIN_SRC d
struct Config {
string http_request_type;
string http_host;
// string server_name;
string doc_root;
string cgi_root;
string cgi_script;
string data_path_html;
string db_path;
string query_string;
string http_url;
string request_method;
}
auto conf = Config();
conf.http_request_type = environment.get("REQUEST_SCHEME", "http");
conf.http_host = environment.get("HTTP_HOST", "localhost");
// conf.server_name = environment.get("SERVER_NAME", "localhost");
conf.doc_root = environment.get("DOCUMENT_ROOT", "/var/www/html");
conf.cgi_root = environment.get("CONTEXT_DOCUMENT_ROOT", "/usr/lib/cgi-bin/");
// conf.cgi_script = environment.get("SCRIPT_NAME", "/cgi-bin/spine-search");
conf.query_string = environment.get("QUERY_STRING", "");
conf.http_url = environment.get("HTTP_REFERER", conf.http_request_type ~ "://" ~ conf.http_host ~ conf.cgi_script ~ "?" ~ conf.query_string);
conf.db_path = "/var/www/html/sqlite/"; // conf.http_host ~ "/sqlite/";
conf.request_method = environment.get("REQUEST_METHOD", "POST");
#+END_SRC
# REQUEST_URI QUERY_STRING
**** cgi val
#+NAME: cgi_sqlite_initialize
#+BEGIN_SRC d
struct CGI_val {
string db_selected = "";
string sql_match_limit = ""; // radio: ( 1000 | 2500 )
string sql_match_offset = "";
string search_text = "";
string results_type = ""; // index
bool checked_echo = false;
bool checked_stats = false;
bool checked_url = false;
bool checked_searched = false;
bool checked_tip = false;
bool checked_sql = false;
}
auto cv = CGI_val();
cv.db_selected = "spine.search.db"; // config, set db name
#+END_SRC
**** text fields
***** open
#+NAME: cgi_sqlite_initialize
#+BEGIN_SRC d
auto text_fields() {
string canned_query_str = environment.get("QUERY_STRING", "");
if ("query_string" in cgi.post) {
canned_query_str = environment.get("QUERY_STRING", "");
}
string[string] canned_query;
if (conf.request_method == "POST") {
} else if (conf.request_method == "GET") {
foreach (pair_str; canned_query_str.split("&")) {
// cgi.write(pair_str ~ "<br>");
string[] pair = pair_str.split("=");
canned_query[pair[0]] = pair[1];
}
// foreach (field, content; canned_query) {
// cgi.write(field ~ ": " ~ content ~ "<br>");
// }
}
#+END_SRC
***** rgx
#+NAME: cgi_sqlite_initialize
#+BEGIN_SRC d
static struct Rgx {
// static canned_query = ctRegex!(`\A(?P<matched>.+)\Z`, "m");
static search_text_area = ctRegex!(`\A(?P<matched>.+)\Z`, "m");
// static fulltext = ctRegex!(`\A(?P<matched>.+)\Z`, "m");
static line = ctRegex!(`^(?P<matched>.+?)(?: ~|$)`, "m");
static text = ctRegex!(`(?:^|\s~\s*)text:\s+(?P<matched>.+?)(?: ~|$)`, "m");
static author = ctRegex!(`(?:^|\s~\s*)author:\s+(?P<matched>.+)$`, "m");
static title = ctRegex!(`(?:^|\s~\s*)title:\s+(?P<matched>.+)$`, "m");
static uid = ctRegex!(`(?:^|\s~\s*)uid:\s+(?P<matched>.+)$`, "m");
static fn = ctRegex!(`(?:^|\s~\s*)fn:\s+(?P<matched>.+)$`, "m");
static keywords = ctRegex!(`(?:^|\s~\s*)keywords:\s+(?P<matched>.+)$`, "m");
static topic_register = ctRegex!(`(?:^|\s~\s*)topic_register:\s+(?P<matched>.+)$`, "m");
static subject = ctRegex!(`(?:^|\s~\s*)subject:\s+(?P<matched>.+)$`, "m");
static description = ctRegex!(`(?:^|\s~\s*)description:\s+(?P<matched>.+)$`, "m");
static publisher = ctRegex!(`(?:^|\s~\s*)publisher:\s+(?P<matched>.+)$`, "m");
static editor = ctRegex!(`(?:^|\s~\s*)editor:\s+(?P<matched>.+)$`, "m");
static contributor = ctRegex!(`(?:^|\s~\s*)contributor:\s+(?P<matched>.+)$`, "m");
static date = ctRegex!(`(?:^|\s~\s*)date:\s+(?P<matched>.+)$`, "m");
static results_type = ctRegex!(`(?:^|\s~\s*)type:\s+(?P<matched>.+)$`, "m");
static format = ctRegex!(`(?:^|\s~\s*)format:\s+(?P<matched>.+)$`, "m");
static identifier = ctRegex!(`(?:^|\s~\s*)identifier:\s+(?P<matched>.+)$`, "m");
static source = ctRegex!(`(?:^|\s~\s*)source:\s+(?P<matched>.+)$`, "m");
static language = ctRegex!(`(?:^|\s~\s*)language:\s+(?P<matched>.+)$`, "m");
static relation = ctRegex!(`(?:^|\s~\s*)relation:\s+(?P<matched>.+)$`, "m");
static coverage = ctRegex!(`(?:^|\s~\s*)coverage:\s+(?P<matched>.+)$`, "m");
static rights = ctRegex!(`(?:^|\s~\s*)rights:\s+(?P<matched>.+)$`, "m");
static comment = ctRegex!(`(?:^|\s~\s*)comment:\s+(?P<matched>.+)$`, "m");
// static abstract_ = ctRegex!(`(?:^|\s~\s*)abstract:\s+(?P<matched>.+)$`, "m");
static src_filename_base = ctRegex!(`^src_filename_base:\s+(?P<matched>.+)$`, "m");
}
#+END_SRC
***** searchfields
#+NAME: cgi_sqlite_initialize
#+BEGIN_SRC d
struct searchFields {
string canned_query = ""; // GET canned_query == cq
string search_text_area = ""; // POST search_text_area == tsa
string text = ""; // text == txt
string author = ""; // author == au
string title = ""; // title == ti
string uid = ""; // uid == uid
string fn = ""; // fn == fn
string keywords = ""; // keywords == kw
string topic_register = ""; // topic_register == tr
string subject = ""; // subject == su
string description = ""; // description == de
string publisher = ""; // publisher == pb
string editor = ""; // editor == ed
string contributor = ""; // contributor == ct
string date = ""; // date == dt
string format = ""; // format == fmt
string identifier = ""; // identifier == id
string source = ""; // source == src sfn
string language = ""; // language == lng
string relation = ""; // relation == rl
string coverage = ""; // coverage == cv
string rights = ""; // rights == rgt
string comment = ""; // comment == cmt
// string abstract = "";
string src_filename_base = ""; // src_filename_base == bfn
string results_type = ""; // results_type == rt radio
string sql_match_limit = ""; // sql_match_limit == sml radio
string sql_match_offset = ""; // sql_match_offset == smo
string stats = ""; // stats == sts checked
string echo = ""; // echo == ec checked
string url = ""; // url == url checked
string searched = ""; // searched == se checked
string sql = ""; // sql == sql checked
}
auto rgx = Rgx();
auto got = searchFields();
#+END_SRC
***** env
****** POST
#+NAME: cgi_sqlite_initialize
#+BEGIN_SRC d
if (environment.get("REQUEST_METHOD", "POST") == "POST") {
if ("sf" in cgi.post) {
got.search_text_area = cgi.post["sf"];
if (auto m = got.search_text_area.matchFirst(rgx.text)) {
got.text = m["matched"];
got.canned_query ~= "sf=" ~ m["matched"];
} else if (auto m = got.search_text_area.matchFirst(rgx.line)) {
if (
!(m["matched"].matchFirst(rgx.author))
&& !(m["matched"].matchFirst(rgx.title))
) {
got.text = m["matched"];
got.canned_query ~= "sf=" ~ m["matched"];
}
}
if (auto m = got.search_text_area.matchFirst(rgx.author)) {
got.author = m["matched"];
got.canned_query ~= "&au=" ~ m["matched"];
}
if (auto m = got.search_text_area.matchFirst(rgx.title)) {
got.title = m["matched"];
got.canned_query ~= "&ti=" ~ m["matched"];
}
if (auto m = got.search_text_area.matchFirst(rgx.uid)) {
got.uid = m["matched"];
got.canned_query ~= "&uid=" ~ m["matched"];
}
if (auto m = got.search_text_area.matchFirst(rgx.fn)) {
got.fn = m["matched"];
got.canned_query ~= "&fn=" ~ m["matched"];
}
if (auto m = got.search_text_area.matchFirst(rgx.keywords)) {
got.keywords = m["matched"];
got.canned_query ~= "&kw=" ~ m["matched"];
}
if (auto m = got.search_text_area.matchFirst(rgx.topic_register)) {
got.topic_register = m["matched"];
got.canned_query ~= "&tr=" ~ m["matched"];
}
if (auto m = got.search_text_area.matchFirst(rgx.subject)) {
got.subject = m["matched"];
got.canned_query ~= "&su=" ~ m["matched"];
}
if (auto m = got.search_text_area.matchFirst(rgx.description)) {
got.description = m["matched"];
got.canned_query ~= "&de=" ~ m["matched"];
}
if (auto m = got.search_text_area.matchFirst(rgx.publisher)) {
got.publisher = m["matched"];
got.canned_query ~= "&pb=" ~ m["matched"];
}
if (auto m = got.search_text_area.matchFirst(rgx.editor)) {
got.editor = m["matched"];
got.canned_query ~= "&ed=" ~ m["matched"];
}
if (auto m = got.search_text_area.matchFirst(rgx.contributor)) {
got.contributor = m["matched"];
got.canned_query ~= "&ct=" ~ m["matched"];
}
if (auto m = got.search_text_area.matchFirst(rgx.date)) {
got.date = m["matched"];
got.canned_query ~= "&dt=" ~ m["matched"];
}
// if (auto m = got.search_text_area.matchFirst(rgx.results_type)) {
// got.results_type = m["matched"];
// got.canned_query ~= "&rt=" ~ m["matched"];
// }
if (auto m = got.search_text_area.matchFirst(rgx.format)) {
got.format = m["matched"];
got.canned_query ~= "&fmt=" ~ m["matched"];
}
if (auto m = got.search_text_area.matchFirst(rgx.identifier)) {
got.identifier = m["matched"];
got.canned_query ~= "&id=" ~ m["matched"];
}
if (auto m = got.search_text_area.matchFirst(rgx.source)) {
got.source = m["matched"];
got.canned_query ~= "&src=" ~ m["matched"];
}
if (auto m = got.search_text_area.matchFirst(rgx.language)) {
got.language = m["matched"];
got.canned_query ~= "&lng=" ~ m["matched"];
}
if (auto m = got.search_text_area.matchFirst(rgx.relation)) {
got.relation = m["matched"];
got.canned_query ~= "&rl=" ~ m["matched"];
}
if (auto m = got.search_text_area.matchFirst(rgx.coverage)) {
got.coverage = m["matched"];
got.canned_query ~= "&cv=" ~ m["matched"];
}
if (auto m = got.search_text_area.matchFirst(rgx.rights)) {
got.rights = m["matched"];
got.canned_query ~= "&rgt=" ~ m["matched"];
}
if (auto m = got.search_text_area.matchFirst(rgx.comment)) {
got.comment = m["matched"];
got.canned_query ~= "&cmt=" ~ m["matched"];
}
// if (auto m = search_text_area.matchFirst(rgx.abstract)) {
// got.abstract = m["matched"];
// }
if (auto m = got.search_text_area.matchFirst(rgx.src_filename_base)) {
got.src_filename_base = m["matched"];
got.canned_query ~= "&bfn=" ~ m["matched"];
}
}
if ("fn" in cgi.post) {
got.fn = cgi.post["fn"];
got.canned_query ~= "&fn=" ~ cgi.post["fn"];
}
if ("rt" in cgi.post) {
got.results_type = cgi.post["rt"];
got.canned_query ~= "&rt=" ~ cgi.post["rt"];
}
if ("sts" in cgi.post) {
got.stats = cgi.post["sts"];
got.canned_query ~= "&sts=" ~ cgi.post["sts"];
}
if ("ec" in cgi.post) {
got.echo = cgi.post["ec"];
got.canned_query ~= "&ec=" ~ cgi.post["ec"];
}
if ("url" in cgi.post) {
got.url = cgi.post["url"];
got.canned_query ~= "&url=" ~ cgi.post["url"];
}
if ("se" in cgi.post) {
got.searched = cgi.post["se"];
got.canned_query ~= "&se=" ~ cgi.post["se"];
}
if ("sql" in cgi.post) {
got.sql = cgi.post["sql"];
got.canned_query ~= "&sql=" ~ cgi.post["sql"];
}
if ("sml" in cgi.post) {
got.sql_match_limit = cgi.post["sml"];
got.canned_query ~= "&sml=" ~ cgi.post["sml"];
}
if ("smo" in cgi.post) {
got.sql_match_offset = "0"; // cgi.post["smo"];
got.canned_query ~= "&smo=0"; // ~ cgi.post["smo"];
}
got.canned_query = got.canned_query.strip.split(" ").join("%20");
conf.query_string = got.canned_query;
// cgi.write("f.canned_query: " ~ got.canned_query ~ "<br>");
#+END_SRC
****** GET
#+NAME: cgi_sqlite_initialize
#+BEGIN_SRC d
} else if (environment.get("REQUEST_METHOD", "POST") == "GET") {
got.canned_query = environment.get("QUERY_STRING", "");
// cgi.write("f.canned_query: " ~ got.canned_query ~ "<br>");
got.search_text_area = "";
if ("sf" in canned_query && !(canned_query["sf"]).empty) {
got.text = canned_query["sf"].split("%20").join(" ");
got.search_text_area ~= "text: " ~ got.text ~ "\n";
}
if ("au" in canned_query && !(canned_query["au"]).empty) {
got.author = canned_query["au"].split("%20").join(" ");
got.search_text_area ~= "author: " ~ got.author ~ "\n";
}
if ("ti" in canned_query && !(canned_query["ti"]).empty) {
got.title = canned_query["ti"].split("%20").join(" ");
got.search_text_area ~= "title: " ~ got.title ~ "\n";
}
if ("uid" in canned_query && !(canned_query["uid"]).empty) {
got.uid = canned_query["uid"].split("%20").join(" ");
got.search_text_area ~= "uid: " ~ got.uid ~ "\n";
}
if ("fn" in canned_query && !(canned_query["fn"]).empty) {
got.fn = canned_query["fn"].split("%20").join(" ");
got.search_text_area ~= "fn: " ~ got.fn ~ "\n";
}
if ("kw" in canned_query && !(canned_query["kw"]).empty) {
got.keywords = canned_query["kw"].split("%20").join(" ");
got.search_text_area ~= "keywords: " ~ got.keywords ~ "\n";
}
if ("tr" in canned_query && !(canned_query["tr"]).empty) {
got.topic_register = canned_query["tr"].split("%20").join(" ");
got.search_text_area ~= "topic_register: " ~ got.topic_register ~ "\n";
}
if ("su" in canned_query && !(canned_query["su"]).empty) {
got.subject = canned_query["su"].split("%20").join(" ");
got.search_text_area ~= "subject: " ~ got.subject ~ "\n";
}
if ("de" in canned_query && !(canned_query["de"]).empty) {
got.description = canned_query["de"].split("%20").join(" ");
got.search_text_area ~= "description: " ~ got.description ~ "\n";
}
if ("pb" in canned_query && !(canned_query["pb"]).empty) {
got.publisher = canned_query["pb"].split("%20").join(" ");
got.search_text_area ~= "publisher: " ~ got.publisher ~ "\n";
}
if ("ed" in canned_query && !(canned_query["ed"]).empty) {
got.editor = canned_query["ed"].split("%20").join(" ");
got.search_text_area ~= "editor: " ~ got.editor ~ "\n";
}
if ("ct" in canned_query && !(canned_query["ct"]).empty) {
got.contributor = canned_query["ct"].split("%20").join(" ");
got.search_text_area ~= "contributor: " ~ got.contributor ~ "\n";
}
if ("dt" in canned_query && !(canned_query["dt"]).empty) {
got.date = canned_query["dt"].split("%20").join(" ");
got.search_text_area ~= "date: " ~ got.date ~ "\n";
}
if ("rt" in canned_query && !(canned_query["rt"]).empty) {
got.results_type = canned_query["rt"].split("%20").join(" ");
// got.search_text_area ~= "results_type: " ~ got.results_type ~ "\n";
}
if ("fmt" in canned_query && !(canned_query["fmt"]).empty) {
got.format = canned_query["fmt"].split("%20").join(" ");
got.search_text_area ~= "format: " ~ got.format ~ "\n";
}
if ("id" in canned_query && !(canned_query["id"]).empty) {
got.identifier = canned_query["id"].split("%20").join(" ");
got.search_text_area ~= "identifier: " ~ got.identifier ~ "\n";
}
if ("src" in canned_query && !(canned_query["src"]).empty) {
got.source = canned_query["src"].split("%20").join(" ");
got.search_text_area ~= "source: " ~ got.source ~ "\n";
}
if ("lng" in canned_query && !(canned_query["lng"]).empty) {
got.language = canned_query["lng"].split("%20").join(" ");
got.search_text_area ~= "language: " ~ got.language ~ "\n";
}
if ("rl" in canned_query && !(canned_query["rl"]).empty) {
got.relation = canned_query["rl"].split("%20").join(" ");
got.search_text_area ~= "relation: " ~ got.relation ~ "\n";
}
if ("cv" in canned_query && !(canned_query["cv"]).empty) {
got.coverage = canned_query["cv"].split("%20").join(" ");
got.search_text_area ~= "coverage: " ~ got.coverage ~ "\n";
}
if ("rgt" in canned_query && !(canned_query["rgt"]).empty) {
got.rights = canned_query["rgt"].split("%20").join(" ");
got.search_text_area ~= "rights: " ~ got.rights ~ "\n";
}
if ("cmt" in canned_query && !(canned_query["cmt"]).empty) {
got.comment = canned_query["cmt"].split("%20").join(" ");
got.search_text_area ~= "comment: " ~ got.comment ~ "\n";
}
// if ("abstract" in canned_query && !(canned_query["abstract"]).empty) {
// got.abstract = canned_query["abstract"];
// }
if ("bfn" in canned_query && !(canned_query["bfn"]).empty) { // search_field
got.src_filename_base = canned_query["bfn"].split("%20").join(" ");
got.search_text_area ~= "src_filename_base: " ~ got.src_filename_base ~ "\n";
}
if ("sml" in canned_query && !(canned_query["sml"]).empty) {
got.sql_match_limit = canned_query["sml"].split("%20").join(" ");
// got.search_text_area ~= "sql_match_limit: " ~ got.sql_match_limit ~ "\n";
}
// cgi.write("f.search_text_area: " ~ got.search_text_area ~ "<br>");
}
return got;
#+END_SRC
***** tail
#+NAME: cgi_sqlite_initialize
#+BEGIN_SRC d
}
auto tf = text_fields; //
#+END_SRC
**** SQL select
#+NAME: cgi_sqlite_initialize
#+BEGIN_SRC d
struct SQL_select {
string the_body = "";
string the_range = "";
}
auto sql_select = SQL_select();
#+END_SRC
**** misc
#+NAME: cgi_sqlite_initialize
#+BEGIN_SRC d
string base ; // = "";
string tip ; // = "";
string search_note ; // = "";
uint sql_match_offset_count = 0;
string previous_next () {
static struct Rgx {
static track_offset = ctRegex!(`(?P<offset_key>[&]smo=)(?P<offset_val>[0-9]+)`, "m");
}
auto rgx = Rgx();
string _previous_next = "";
int _current_offset_value = 0;
string _set_offset_next = "";
string _set_offset_previous = "";
string _url = "";
string _url_previous = "";
string _url_next = "";
string arrow_previous = "";
string arrow_next = "";
if (environment.get("REQUEST_METHOD", "POST") == "POST") {
_url = conf.http_request_type ~ "://" ~ conf.http_host ~ conf.cgi_script ~ "?" ~ tf.canned_query;
} else if (environment.get("REQUEST_METHOD", "POST") == "GET") {
_url = conf.http_request_type ~ "://" ~ conf.http_host ~ conf.cgi_script ~ "?" ~ environment.get("QUERY_STRING", "");
}
if (auto m = _url.matchFirst(rgx.track_offset)) {
_current_offset_value = m.captures["offset_val"].to!int;
_set_offset_next = m.captures["offset_key"] ~ ((m.captures["offset_val"]).to!int + cv.sql_match_limit.to!int).to!string;
_url_next = _url.replace(rgx.track_offset, _set_offset_next);
if (_current_offset_value < cv.sql_match_limit.to!int) {
_url_previous = "";
} else {
_url_previous = "";
_set_offset_previous = m.captures["offset_key"] ~ ((m.captures["offset_val"]).to!int - cv.sql_match_limit.to!int).to!string;
_url_previous = _url.replace(rgx.track_offset, _set_offset_previous);
}
} else {// _current_offset_value = 0;
_url_next = _url ~= "&smo=" ~ cv.sql_match_limit.to!string;
}
if (_url_previous.empty) {
arrow_previous = "";
} else {
arrow_previous =
"<font size=\"2\" color=\"#666666\">"
~ "<a href=\""
~ _url_previous
~ "\">"
~ "<< prev"
~ "</a> || </font>";
}
arrow_next =
"<font size=\"2\" color=\"#666666\">"
~ "<a href=\""
~ _url_next
~ "\">"
~ "next >>"
~ "</a></font>";
_previous_next = "<hr>" ~ arrow_previous ~ arrow_next;
return _previous_next;
}
#+END_SRC
** cgi
*** cgi html header
#+NAME: cgi_sqlite_header
#+BEGIN_SRC d
{
header = format(q"┃
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>
SiSU spine search form (sample)
</title>
<meta name="sourcefile" content="SiSU.sst" />
<link rel="generator" href="sisudoc.org" />
<link rel="shortcut icon" href="http://%s/image_sys/spine.ico" />
<style media = "all">
*{
padding : 0px;
margin : 2px;
}
body {
height : 100vh;
background-color : #FFFFFF;
}
body {
color : #000000;
background : #FFFFFF;
background-color : #FFFFFF;
}
a:link {
color : #003399;
text-decoration : none;
}
a:visited {
color : #003399;
text-decoration : none;
}
a:hover {
color : #000000;
background-color : #F9F9AA;
}
a.lnkocn:link {
color : #777777;
text-decoration : none;
}
a.lnkocn:visited {
color : #32CD32;
text-decoration : none;
}
a.lnkocn:hover {
color : #777777;
font-size : 15px;
}
a:hover img {
background-color : #FFFFFF;
}
a:active {
color : #003399;
text-decoration : underline;
}
hr {
width : 100%%;
margin-left : 0%%;
margin-right : 0em;
margin-top : 0.5em;
margin-bottom : 0.5em;
}
</style>
</head>
<body lang="en" xml:lang="en">
┃",
conf.http_host,
);
}
#+END_SRC
*** cgi html table
#+NAME: cgi_sqlite_table
#+BEGIN_SRC d
{
table = format(q"┃
<table summary="band" border="0" cellpadding="2" cellspacing="0">
<tr><td width="20%%">
<table summary="home button / home information" border="0" cellpadding="2" cellspacing="0">
<tr><td align="left">
<br /><a href="http://sisudoc.org/" target="_top">
<b>SiSU</b>
</a>
<br /><a href="http://git.sisudoc.org/" target="_top">
git
</a>
</td></tr>
</table>
</td>
<td>
<label for="find"><b>SiSU spine (generated) search form</b></label>
</td></tr>
</table>
┃");
}
#+END_SRC
*** cgi html form
#+NAME: cgi_sqlite_form
#+BEGIN_SRC d
{
string post_value(string field_name, string type="box", string set="on") {
string val = "";
switch (type) {
case "field":
val = ((field_name in cgi.post && !(cgi.post[field_name]).empty)
? cgi.post[field_name]
: (field_name in cgi.get)
? cgi.get[field_name]
: "");
val = tf.search_text_area;
break;
case "box": // generic for checkbox or radio; checkbox set == "on" radio set == "name set"
val = ((field_name in cgi.post && !(cgi.post[field_name]).empty)
? (cgi.post[field_name] == set ? "checked" : "off")
: (field_name in cgi.get)
? (cgi.get[field_name] == set ? "checked" : "off")
: "off");
break;
case "radio": // used generic bo
val = ((field_name in cgi.post && !(cgi.post[field_name]).empty)
? (cgi.post[field_name] == set ? "checked" : "off")
: (field_name in cgi.get)
? (cgi.get[field_name] == set ? "checked" : "off")
: "checked");
break;
case "checkbox": // used generic bo
val = ((field_name in cgi.post && !(cgi.post[field_name]).empty)
? (cgi.post[field_name] == set ? "checked" : "off")
: (field_name in cgi.get)
? (cgi.get[field_name] == set ? "checked" : "off")
: "checked");
break;
default:
}
return val;
}
#+END_SRC
**** canned search
#+NAME: cgi_sqlite_form
#+BEGIN_SRC d
string the_can(string fv) {
string show_the_can = post_value("url");
string _the_can = "";
if (show_the_can == "checked") {
tf = text_fields;
string method_get_url = conf.http_request_type ~ "://" ~ conf.http_host ~ conf.cgi_script ~ "?" ~ environment.get("QUERY_STRING", "");
string method_post_url_construct = conf.http_request_type ~ "://" ~ conf.http_host ~ conf.cgi_script ~ "?" ~ tf.canned_query;
// assert(method_get_url == environment.get("HTTP_REFERER", conf.http_request_type ~ "://" ~ conf.http_host ~ conf.cgi_script ~ "?" ~ conf.query_string));
if (conf.request_method == "POST") {
_the_can =
"<font size=\"2\" color=\"#666666\">"
~ "POST: "
~ "<a href=\""
~ method_post_url_construct
~ "\">"
~ method_post_url_construct
~ "</a></font>"
~ "<br>";
} else if (conf.request_method == "GET") {
_the_can =
"<font size=\"2\" color=\"#666666\">"
~ "GET: "
~ "<a href=\""
~ method_get_url
~ "\">"
~ method_get_url
~ "</a></font>";
}
conf.http_url = conf.http_request_type ~ "://" ~ conf.http_host ~ conf.cgi_script ~ tf.canned_query;
}
return _the_can;
}
#+END_SRC
**** provide tip
#+NAME: cgi_sqlite_form
#+BEGIN_SRC d
string provide_tip() {
string searched_tip = post_value("se");
string tip = "";
if (searched_tip == "checked") {
string search_field = post_value("sf", "field");
tf = text_fields;
tip = format(q"┃
<font size="2" color="#666666">
<b>database:</b> <font size="2" color="#004000">%s</font>; <b>selected view:</b> <font size="2" color="#004000">index</font>
<b>search string:</b> %s %s %s %s %s %s<br />
%s %s %s %s %s %s
</font>
┃",
cv.db_selected,
(tf.text.empty ? "" : "\"text: <font size=\"2\" color=\"#004000\">" ~ tf.text ~ "</font>; "),
(tf.title.empty ? "" : "\"title: <font size=\"2\" color=\"#004000\">" ~ tf.title ~ "</font>; "),
(tf.author.empty ? "" : "\"author: <font size=\"2\" color=\"#004000\">" ~ tf.author ~ "</font>; "),
(tf.date.empty ? "" : "\"date <font size=\"2\" color=\"#004000\">" ~ tf.date ~ "</font>; "),
(tf.uid.empty ? "" : "\"uid: <font size=\"2\" color=\"#004000\">" ~ tf.uid ~ "</font>; "),
(tf.fn.empty ? "" : "\"fn: <font size=\"2\" color=\"#004000\">" ~ tf.fn ~ "</font>; "),
(tf.text.empty ? "" : "text: <font size=\"2\" color=\"#004000\">" ~ tf.text ~ "</font><br />"),
(tf.title.empty ? "" : "title: <font size=\"2\" color=\"#004000\">" ~ tf.title ~ "</font><br />"),
(tf.author.empty ? "" : "author: <font size=\"2\" color=\"#004000\">" ~ tf.author ~ "</font><br />"),
(tf.date.empty ? "" : "date: <font size=\"2\" color=\"#004000\">" ~ tf.date ~ "</font><br />"),
(tf.uid.empty ? "" : "\"uid: <font size=\"2\" color=\"#004000\">" ~ tf.uid ~ "</font>; "),
(tf.fn.empty ? "" : "\"fn: <font size=\"2\" color=\"#004000\">" ~ tf.fn ~ "</font>; "),
);
}
return tip;
}
#+END_SRC
**** the form
***** form html
#+NAME: cgi_sqlite_form
#+BEGIN_SRC d
form = format(q"┃
<form action="%s" id="SubmitForm" method="post" accept-charset="UTF-8">
<table cellpadding="2">
<tr><td valign=\"top\">
<textarea id="find" name="sf" type="text" rows="6" cols="40" maxlength="256" wrap="virtual">%s</textarea>
</td>
<td valign=\"top\">
%s
%s
%s
</td></tr></table>
<td valign=\"top\"><tr><td>
<font size="2" color="#222222">
<b>to search:</b> select which database to search (drop-down menu below); enter your search query (in the form above); and <b>click on the search button</b> (below)
<br />
<select name="db" size="1">
%s
<option value="%s">spine</option>
</select>
<input type="submit" value="SiSU search" />
<input type="radio" name="rt" id="results_type_index" value="idx" %s> index
<input type="radio" name="rt" id="results_type_text" value="txt" %s> text / grep;
match limit:
<input type="radio" name="sml" id="sql_match_limit_1000" value="1000" %s> 1,000
<input type="radio" name="sml" id="sql_match_limit_2500" value="2500" %s> 2,500
<br />
<input type="checkbox" name="ec" %s> echo query
<input type="checkbox" name="sts" %s> result stats
<input type="checkbox" name="url" %s> search url
<input type="checkbox" name="se" %s> searched
<input type="checkbox" name="tip" %s> available fields
<input type="checkbox" name="sql" %s> sql statement
<input type="hidden" name="smo" value="0">
<br />
</font>
</td></tr>
</table>
</form>
┃",
#+END_SRC
***** form values
#+NAME: cgi_sqlite_form
#+BEGIN_SRC d
"spine-search",
(post_value("ec") == "checked") ? post_value("sf", "field") : "",
provide_tip,
search_note,
the_can(post_value("sf", "field")),
cv.db_selected,
cv.db_selected,
post_value("rt", "box", "idx"),
post_value("rt", "box", "txt"),
post_value("sml", "box", "1000"),
post_value("sml", "box", "2500"),
post_value("ec"),
post_value("sts"),
post_value("url"),
post_value("se"),
post_value("tip"),
post_value("sql"),
);
#+END_SRC
**** set value (debug)
#+NAME: cgi_sqlite_form
#+BEGIN_SRC d
{
string set_value(string field_name, string default_val) {
string val;
if (field_name in cgi.post) {
val = cgi.post[field_name];
} else if (field_name in cgi.get) {
val = cgi.get[field_name];
} else { val = default_val; }
return val;
}
bool set_bool(string field_name) {
bool val;
if (field_name in cgi.post
&& cgi.post[field_name] == "on") {
val = true;
} else if (field_name in cgi.get
&& cgi.get[field_name] == "on") {
val = true;
} else { val = false; }
return val;
}
cv.db_selected = set_value("selected_db", "spine.search.db"); // selected_db == db
cv.sql_match_limit = set_value("sml", "1000");
cv.sql_match_offset = set_value("smo", "0");
cv.search_text = set_value("sf", "test"); // remove test
cv.results_type = set_value("rt", "idx");
cv.checked_echo = set_bool("ec");
cv.checked_stats = set_bool("sts");
cv.checked_url = set_bool("url");
cv.checked_searched = set_bool("se");
cv.checked_tip = set_bool("tip");
cv.checked_sql = set_bool("sql");
tf = text_fields;
}
}
#+END_SRC
*** cgi write
#+NAME: cgi_sqlite_write
#+BEGIN_SRC d
{
cgi.write(header);
cgi.write(table);
cgi.write(form);
// cgi.write(previous_next);
{ // debug environment
// foreach (k, d; environment.toAA) {
// cgi.write(k ~ ": " ~ d ~ "<br>");
// }
}
{ // debug cgi info
// cgi.write("db_selected: " ~ cv.db_selected ~ "<br>\n");
// cgi.write("search_text: " ~ cv.search_text ~ "<br>\n");
// cgi.write("sql_match_limit: " ~ cv.sql_match_limit ~ ";\n");
// cgi.write("sql_match_offset: " ~ cv.sql_match_offset ~ ";\n");
// cgi.write("results_type: " ~ cv.results_type ~ "<br>\n");
// cgi.write("cv.checked_echo: " ~ (cv.checked_echo ? "checked" : "off") ~ "; \n");
// cgi.write("cv.checked_stats: " ~ (cv.checked_stats ? "checked" : "off") ~ "; \n");
// cgi.write("cv.checked_url: " ~ (cv.checked_url ? "checked" : "off") ~ "; \n");
// cgi.write("cv.checked_searched: " ~ (cv.checked_searched ? "checked" : "off") ~ ";<br>\n");
// cgi.write("cv.checked_tip: " ~ (cv.checked_tip ? "checked" : "off") ~ "; \n");
// cgi.write("cv.checked_sql: " ~ (cv.checked_sql ? "checked" : "off") ~ "<br>\n");
}
}
#+END_SRC
** db
*** db set
#+NAME: cgi_sqlite_set_db
#+BEGIN_SRC d
auto db = Database(conf.db_path ~ cv.db_selected);
#+END_SRC
*** db select statement
**** select where
#+NAME: cgi_sqlite_select_statement_0
#+BEGIN_SRC d
{
uint sql_match_offset_counter(T)(T cv) {
sql_match_offset_count += cv.sql_match_limit.to!uint;
return sql_match_offset_count;
}
void sql_search_query() {
string select_field_like(string db_field, string search_field) {
string where_ = "";
if (!(search_field.empty)) {
string _sf = search_field.strip.split("%20").join(" ");
if (_sf.match(r" OR ")) {
_sf = _sf.split(" OR ").join("%' OR " ~ db_field ~ " LIKE '%");
}
if (_sf.match(r" AND ")) {
_sf = _sf.split(" AND ").join("%' AND " ~ db_field ~ " LIKE '%");
}
_sf = "( " ~ db_field ~ " LIKE\n '%" ~ _sf ~ "%' )";
where_ ~= format(q"┃
%s
┃",
_sf
);
}
return where_;
}
string[] _fields;
_fields ~= select_field_like("doc_objects.clean", tf.text);
_fields ~= select_field_like("metadata_and_text.title", tf.title);
_fields ~= select_field_like("metadata_and_text.creator_author", tf.author);
_fields ~= select_field_like("metadata_and_text.uid", tf.uid);
_fields ~= select_field_like("metadata_and_text.src_filename_base", tf.fn);
_fields ~= select_field_like("metadata_and_text.src_filename_base", tf.src_filename_base);
_fields ~= select_field_like("metadata_and_text.language_document_char", tf.language);
_fields ~= select_field_like("metadata_and_text.date_published", tf.date);
_fields ~= select_field_like("metadata_and_text.classify_keywords", tf.keywords);
_fields ~= select_field_like("metadata_and_text.classify_topic_register", tf.topic_register);
string[] fields;
foreach (f; _fields) {
if (!(f.empty)) { fields ~= f; }
}
string fields_str = "";
fields_str ~= fields.join(" AND ");
#+END_SRC
**** db select statement
***** the body
#+NAME: cgi_sqlite_select_statement_0
#+BEGIN_SRC d
sql_select.the_body ~= format(q"┃
SELECT
metadata_and_text.uid,
metadata_and_text.title,
metadata_and_text.creator_author_last_first,
metadata_and_text.creator_author,
metadata_and_text.src_filename_base,
metadata_and_text.language_document_char,
metadata_and_text.date_published,
metadata_and_text.classify_keywords,
metadata_and_text.classify_topic_register,
doc_objects.body,
doc_objects.seg_name,
doc_objects.ocn,
metadata_and_text.uid
FROM
doc_objects,
metadata_and_text
WHERE (
%s
)
AND
doc_objects.uid_metadata_and_text = metadata_and_text.uid
ORDER BY
metadata_and_text.creator_author_last_first,
metadata_and_text.date_published DESC,
metadata_and_text.title,
metadata_and_text.language_document_char,
metadata_and_text.src_filename_base,
doc_objects.ocn
LIMIT %s OFFSET %s
;┃",
fields_str,
cv.sql_match_limit,
cv.sql_match_offset,
);
#+END_SRC
***** html write selected
****** ocn index
#+NAME: cgi_sqlite_select_statement_0
#+BEGIN_SRC d
(cv.checked_sql)
? cgi.write(previous_next ~ "<hr><font size=\"2\" color=\"#666666\">" ~ sql_select.the_body.split("\n ").join(" ").split("\n").join("<br>") ~ "</font>\n")
: "";
cgi.write(previous_next);
auto select_query_results = db.execute(sql_select.the_body).cached;
string _old_uid = "";
if (!select_query_results.empty) {
foreach (row; select_query_results) {
if (row["uid"].as!string != _old_uid) {
_old_uid = row["uid"].as!string;
auto m = (row["date_published"].as!string).match(regex(r"^([0-9]{4})")); // breaks if row missing or no match?
cgi.write(
"<hr><a href=\""
~ "http://" ~ conf.http_host ~ "/"
~ row["language_document_char"].as!string ~ "/html/"
~ row["src_filename_base"].as!string ~ "/"
~ "toc.html"
~ "\">\""
~ row["title"].as!string ~ "\""
~ "</a> ("
~ m.hit
~ ") "
~ "["
~ row["language_document_char"].as!string
~ "] "
~ row["creator_author_last_first"].as!string
~ ":<br>\n"
);
}
if (cv.results_type == "txt") {
cgi.write(
"<hr><a href=\""
~ "http://" ~ conf.http_host ~ "/"
~ row["language_document_char"].as!string ~ "/html/"
~ row["src_filename_base"].as!string ~ "/"
~ row["seg_name"].as!string ~ ".html#" ~ row["ocn"].as!string
~ "\">"
~ row["ocn"].as!string
~ "</a>"
~ "<br>"
~ row["body"].as!string
);
} else {
cgi.write(
"<a href=\""
~ "http://" ~ conf.http_host ~ "/"
~ row["language_document_char"].as!string ~ "/html/"
~ row["src_filename_base"].as!string ~ "/"
~ row["seg_name"].as!string ~ ".html#" ~ row["ocn"].as!string
~ "\">"
~ row["ocn"].as!string
~ "</a>, "
);
}
}
cgi.write( previous_next);
} else { // offset_not_beyond_limit = false;
cgi.write("select_query_results empty<p>\n");
}
}
sql_search_query;
}
#+END_SRC
****** text found
*** db close
#+NAME: cgi_sqlite_db_close
#+BEGIN_SRC d
{
db.close;
}
#+END_SRC
** tail
*** cgi tail
#+NAME: cgi_sqlite_db_tail
#+BEGIN_SRC d
{
string tail = format(q"┃
</body>
┃");
cgi.write(tail);
}
#+END_SRC
* cgi-search dub.sdl
#+BEGIN_SRC d :tangle "../misc/util/d/cgi/search/dub.sdl"
name "spine_search"
description "A minimal D application."
authors "ralph"
copyright "Copyright © 2020, ralph"
license "GPL-3.0+"
dependency "d2sqlite3" version="~>0.18.3"
dependency "arsd-official": "7.2.0"
subConfiguration "arsd-official:cgi" "cgi"
targetType "executable"
targetPath "./cgi-bin"
mainSourceFile "src/spine_cgi_sqlite_search.d"
configuration "default" {
targetType "executable"
targetName "spine-search"
postGenerateCommands "/usr/bin/notify-send -t 0 'D executable ready' 'spine cgi sqlite search d'"
}
#+END_SRC
* cgi.d arsd Adam Ruppe
used for cgi
https://dlang.org/phobos/std_net_curl.html
https://dlang.org/library/std/net/curl.html
curl https://raw.githubusercontent.com/adamdruppe/arsd/master/cgi.d -o cgi.d
wget https://raw.githubusercontent.com/adamdruppe/arsd/master/cgi.d
aria2c https://raw.githubusercontent.com/adamdruppe/arsd/master/cgi.d
* cgi-search README
#+BEGIN_SRC text :tangle "../misc/util/d/cgi/search/README"
change db name to match name of db you create
cv.db_selected = "spine.search.sql.db";
~dr/bin/spine-ldc -v --sqlite-db-create --sqlite-db-filename="spine.search.db" --cgi-sqlite-search-filename="spine-search" --output=/var/www ~grotto/repo/git.repo/code/project-spine/doc-reform-markup/markup_samples/markup/pod/*
~dr/bin/spine-ldc -v --sqlite-update --sqlite-db-filename="spine.search.db" --output=/var/www ~grotto/repo/git.repo/code/project-spine/doc-reform-markup/markup_samples/markup/pod/*
cd util/d/cgi/search/src
dub --force --compiler=ldc2 && sudo cp -v cgi-bin/spine-search /usr/lib/cgi-bin/.
http://localhost/cgi-bin/spine-search?
#+END_SRC