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 ()
(interactive)
(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,
hlinks_tags,
(SELECT hlinks_name FROM hlinks
WHERE hlinks_id = hlinks_parent),
get_accounts_name(hlinks_accounts),
get_full_contacts_name(hlinks_people),
hlinks_authorname, get_full_contacts_name(hlinks_author),
get_full_contacts_name(hlinks_assignedperson),
(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.
hyperscope full-text-search postgresql emacs-lisp emacs
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/.