Hyperscope full text search with PostgreSQL

LISP is like magic, when you have made many fundamental functions it becomes trivial to improve this or that. As of today 2021-04-23 these new function has been added to Hyperscope for Emacs.

One is to update the PostgreSQL database text search tokens. One can read from the SQL query below in this Emacs Lisp function which information is updated in the hlinks column. It is trivial to extend such information, for example, if it is related to people, it could be possible to add some SMS, notes, email contents to get better or more accurate results when searching for people.

As the function takes some time, maybe few minutes, it is good to first message the user that is is clear that some process is taking longer.

(defun hyperscope-update-tokens ()
  (let ((sql "UPDATE hlinks 
  SET hlinks_tokens = to_tsvector(concat_ws(' ', 
  (SELECT hlinktypes_name FROM hlinktypes 
  WHERE hlinktypes_id = hlinks_id), 
  hlinks_name, hlinks_link, hlinks_description, 
  (SELECT hlinks_name FROM hlinks 
  WHERE hlinks_id = hlinks_parent), 
  hlinks_authorname, get_full_contacts_name(hlinks_author), 
  (SELECT actionstatuses_name 
  FROM actionstatuses 
  WHERE actionstatuses_id = hlinks_actionstatuses), 
  (SELECT areas_name 
  FROM areas 
  WHERE areas_id = hlinks_areas), hlinks_slug, hlinks_report))"))
    (message "Updating PostgreSQL tokens for table `hlinks'")
    (rcd-sql sql cf-db)))

Once the database has been updated in the next step we need a search function and listing that user may operate on a Hyperscope object. This function maybe need some better PostgreSQL escaping. For now it works practically for words without symbols or quotes.

(defun hyperscope-search-ts-query (query)
  (interactive "sText search query: ")
  (let* ((query (split-string query))
     (query (string-join query " & "))
     (sql (format "SELECT hlinks_id 
     FROM hlinks 
     WHERE hlinks_tokens @@ to_tsquery('%s')" query))
     (id-list (rcd-sql sql *hs*)))
    (hyperscope nil nil nil nil nil id-list)))

The result is pretty quick. It is faster than other methods I have used, for example completing-read function is way slower then just finding or searching for few keywords and getting the best result.

Related hyperdocuments

Related hyperdocument tags

hyperscope full-text-search postgresql emacs-lisp emacs

GNU Affero General Public License Version 3

Copyright © 2021-03-29 11:09:33.494224+02 Jean Marc Louis

This program is free software: you can redistribute it and/or modify it under the terms of the GNU Affero 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 Affero General Public License for more details.

You should have received a copy of the GNU Affero General Public License along with this program. If not, see https://www.gnu.org/licenses/.