-*- 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 - 2021 Ralph Amissah #+LANGUAGE: en #+STARTUP: content hideblocks hidestars noindent entitiespretty #+OPTIONS: H:3 num:nil toc:t \n:nil @:t ::t |:t ^:nil _:nil -:t f:t *:t <:t #+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 [[./spine.org][spine.org]] [[../org/][org/]] [[./spine_build_scaffold.org][make/build]] * 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" <> <> void cgi_function_intro(Cgi cgi) { <> <> <> <> <> <> <> <> <> <> <> <> <> <> <> <> <> <> <> <> <> <> <> <> <> <> } 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 *** document header including copyright & license #+NAME: doc_header_including_copyright_and_license #+BEGIN_SRC txt /+ - Name: Spine, Doc Reform [a part of] - Description: documents, structuring, processing, publishing, search - static content generator - Author: Ralph Amissah [ralph.amissah@gmail.com] - Copyright: (C) 2015 - 2021 Ralph Amissah, All Rights Reserved. - License: AGPL 3 or later: Spine (SiSU), a framework for document structuring, publishing and search Copyright (C) Ralph Amissah This program is free software: you can redistribute it and/or modify it under the terms of the GNU AFERO General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. You should have received a copy of the GNU General Public License along with this program. If not, see [https://www.gnu.org/licenses/]. If you have Internet connection, the latest version of the AGPL should be available at these locations: [https://www.fsf.org/licensing/licenses/agpl.html] [https://www.gnu.org/licenses/agpl.html] - Spine (by Doc Reform, related to SiSU) uses standard: - docReform markup syntax - standard SiSU markup syntax with modified headers and minor modifications - docReform object numbering - standard SiSU object citation numbering & system - Hompages: [https://www.doc_reform.org] [https://www.sisudoc.org] - Git [https://git.sisudoc.org/projects/?p=software/spine.git;a=summary] +/ #+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_0 #+BEGIN_SRC d string header; string table; string form; #+END_SRC **** config #+NAME: cgi_sqlite_initialize_1 #+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_2 #+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_3 #+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 ~ "
"); string[] pair = pair_str.split("="); canned_query[pair[0]] = pair[1]; } // foreach (field, content; canned_query) { // cgi.write(field ~ ": " ~ content ~ "
"); // } } #+END_SRC ***** rgx #+NAME: cgi_sqlite_initialize_4 #+BEGIN_SRC d static struct Rgx { // static canned_query = ctRegex!(`\A(?P.+)\Z`, "m"); static search_text_area = ctRegex!(`\A(?P.+)\Z`, "m"); // static fulltext = ctRegex!(`\A(?P.+)\Z`, "m"); static line = ctRegex!(`^(?P.+?)(?: ~|$)`, "m"); static text = ctRegex!(`(?:^|\s~\s*)text:\s+(?P.+?)(?: ~|$)`, "m"); static author = ctRegex!(`(?:^|\s~\s*)author:\s+(?P.+)$`, "m"); static title = ctRegex!(`(?:^|\s~\s*)title:\s+(?P.+)$`, "m"); static uid = ctRegex!(`(?:^|\s~\s*)uid:\s+(?P.+)$`, "m"); static fn = ctRegex!(`(?:^|\s~\s*)fn:\s+(?P.+)$`, "m"); static keywords = ctRegex!(`(?:^|\s~\s*)keywords:\s+(?P.+)$`, "m"); static topic_register = ctRegex!(`(?:^|\s~\s*)topic_register:\s+(?P.+)$`, "m"); static subject = ctRegex!(`(?:^|\s~\s*)subject:\s+(?P.+)$`, "m"); static description = ctRegex!(`(?:^|\s~\s*)description:\s+(?P.+)$`, "m"); static publisher = ctRegex!(`(?:^|\s~\s*)publisher:\s+(?P.+)$`, "m"); static editor = ctRegex!(`(?:^|\s~\s*)editor:\s+(?P.+)$`, "m"); static contributor = ctRegex!(`(?:^|\s~\s*)contributor:\s+(?P.+)$`, "m"); static date = ctRegex!(`(?:^|\s~\s*)date:\s+(?P.+)$`, "m"); static results_type = ctRegex!(`(?:^|\s~\s*)type:\s+(?P.+)$`, "m"); static format = ctRegex!(`(?:^|\s~\s*)format:\s+(?P.+)$`, "m"); static identifier = ctRegex!(`(?:^|\s~\s*)identifier:\s+(?P.+)$`, "m"); static source = ctRegex!(`(?:^|\s~\s*)source:\s+(?P.+)$`, "m"); static language = ctRegex!(`(?:^|\s~\s*)language:\s+(?P.+)$`, "m"); static relation = ctRegex!(`(?:^|\s~\s*)relation:\s+(?P.+)$`, "m"); static coverage = ctRegex!(`(?:^|\s~\s*)coverage:\s+(?P.+)$`, "m"); static rights = ctRegex!(`(?:^|\s~\s*)rights:\s+(?P.+)$`, "m"); static comment = ctRegex!(`(?:^|\s~\s*)comment:\s+(?P.+)$`, "m"); // static abstract_ = ctRegex!(`(?:^|\s~\s*)abstract:\s+(?P.+)$`, "m"); static src_filename_base = ctRegex!(`^src_filename_base:\s+(?P.+)$`, "m"); } #+END_SRC ***** searchfields #+NAME: cgi_sqlite_initialize_5 #+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_6 #+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 ~ "
"); #+END_SRC ****** GET #+NAME: cgi_sqlite_initialize_7 #+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 ~ "
"); 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 ~ "
"); } return got; #+END_SRC ***** tail #+NAME: cgi_sqlite_initialize_8 #+BEGIN_SRC d } auto tf = text_fields; // #+END_SRC **** SQL select #+NAME: cgi_sqlite_initialize_9 #+BEGIN_SRC d struct SQL_select { string the_body = ""; string the_range = ""; } auto sql_select = SQL_select(); #+END_SRC **** misc #+NAME: cgi_sqlite_initialize_10 #+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[&]smo=)(?P[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 = "" ~ "" ~ "<< prev" ~ " || "; } arrow_next = "" ~ "" ~ "next >>" ~ ""; _previous_next = "
" ~ arrow_previous ~ arrow_next; return _previous_next; } #+END_SRC ** cgi *** cgi html header #+NAME: cgi_sqlite_header #+BEGIN_SRC d { header = format(q"┃ SiSU spine search form (sample) ┃", conf.http_host, ); } #+END_SRC *** cgi html table #+NAME: cgi_sqlite_table #+BEGIN_SRC d { table = format(q"┃

SiSU
git
┃"); } #+END_SRC *** cgi html form #+NAME: cgi_sqlite_form_0 #+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_1 #+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 = "" ~ "POST: " ~ "" ~ method_post_url_construct ~ "" ~ "
"; } else if (conf.request_method == "GET") { _the_can = "" ~ "GET:  " ~ "" ~ method_get_url ~ ""; } 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_2 #+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"┃ database: %s; selected view: index search string: %s %s %s %s %s %s
%s %s %s %s %s %s ┃", cv.db_selected, (tf.text.empty ? "" : "\"text: " ~ tf.text ~ "; "), (tf.title.empty ? "" : "\"title: " ~ tf.title ~ "; "), (tf.author.empty ? "" : "\"author: " ~ tf.author ~ "; "), (tf.date.empty ? "" : "\"date " ~ tf.date ~ "; "), (tf.uid.empty ? "" : "\"uid: " ~ tf.uid ~ "; "), (tf.fn.empty ? "" : "\"fn: " ~ tf.fn ~ "; "), (tf.text.empty ? "" : "text: " ~ tf.text ~ "
"), (tf.title.empty ? "" : "title: " ~ tf.title ~ "
"), (tf.author.empty ? "" : "author: " ~ tf.author ~ "
"), (tf.date.empty ? "" : "date: " ~ tf.date ~ "
"), (tf.uid.empty ? "" : "\"uid: " ~ tf.uid ~ "; "), (tf.fn.empty ? "" : "\"fn: " ~ tf.fn ~ "; "), ); } return tip; } #+END_SRC **** the form ***** form html #+NAME: cgi_sqlite_form_3 #+BEGIN_SRC d form = format(q"┃
%s %s %s
to search: select which database to search (drop-down menu below); enter your search query (in the form above); and click on the search button (below)
index text / grep; match limit: 1,000 2,500
echo query result stats search url searched available fields sql statement
┃", #+END_SRC ***** form values #+NAME: cgi_sqlite_form_4 #+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_5 #+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 ~ "
"); // } } { // debug cgi info // cgi.write("db_selected: " ~ cv.db_selected ~ "
\n"); // cgi.write("search_text: " ~ cv.search_text ~ "
\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 ~ "
\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") ~ ";
\n"); // cgi.write("cv.checked_tip: " ~ (cv.checked_tip ? "checked" : "off") ~ "; \n"); // cgi.write("cv.checked_sql: " ~ (cv.checked_sql ? "checked" : "off") ~ "
\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_1 #+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_2 #+BEGIN_SRC d (cv.checked_sql) ? cgi.write(previous_next ~ "
" ~ sql_select.the_body.split("\n ").join(" ").split("\n").join("
") ~ "\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( "
\"" ~ row["title"].as!string ~ "\"" ~ " (" ~ m.hit ~ ") " ~ "[" ~ row["language_document_char"].as!string ~ "] " ~ row["creator_author_last_first"].as!string ~ ":
\n" ); } if (cv.results_type == "txt") { cgi.write( "
" ~ row["ocn"].as!string ~ "" ~ "
" ~ row["body"].as!string ); } else { cgi.write( "" ~ row["ocn"].as!string ~ ", " ); } } cgi.write( previous_next); } else { // offset_not_beyond_limit = false; cgi.write("select_query_results empty

\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"┃ ┃"); 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 © 2021, 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 "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