* Greg Minshall <minsh...@umich.edu> [2022-07-22 19:14]:
> hi. does anyone have any code, or know of any existing package, that
> would allow for some sort of form-like (or "transient"-like) interface
> for adding rows to an org-mode table? in particular, that provides some
> sort of =completing-read= interface for a given set of choices for a
> given column, etc.
I have total understanding for this use case, as I do similar all the
time. Just that I don't use Org tables, I use database tables.
I would complete from database like
(rcd-completing-read-sql-hash "Choose person" "SELECT people_id,
people_name FROM people" cf-db)
That would give me person to choose from.
(defun rcd-completing-read-sql-hash (prompt sql pg &optional history
initial-input not-require-match auto-initial-input)
"Complete selection by using SQL.
First column shall be unique id, followed by text
representation. Example SQL query:
SELECT people_id, people_firstname || ' ' || people_lastname FROM people
PG is database handle. HISTORY is supported with INITIAL-INPUT
Argument PROMPT will be displayed to user."
(let* ((hash (rcd-sql-hash-with-key sql pg))
(completion-ignore-case t)
(require-match (if not-require-match nil t))
(history (or history (rcd-ask-history-variable prompt)))
(initial-input (or initial-input (when auto-initial-input (car
(symbol-value history)))))
(choice (completing-read prompt hash nil require-match initial-input
history))
(choice (string-trim choice))
(id (gethash choice hash)))
(if id id
(if not-require-match
choice))))
I find parsing text table rather difficult. If you have extensive
work, I would recommend keeping information in the database table, and
then inserting it into Org mode by using source blocks or functions.
Then I am thinking as following:
- psql command line tool has csv format among others
- pandoc can convert from csv to org
and here it is:
$ psql -A -t -X -q -c "select peoplenames_peoplename, peoplenames_peopleid as
people from peoplenames where peoplenames_peoplename ~* 'minsh' limit 5;"
Greg Minshall|320418
then that CSV may be converted by using pandoc:
psql -A -t -X -q -c "select peoplenames_peoplename, peoplenames_peopleid as
people from peoplenames where peoplenames_peoplename ~* 'minsh' limit 5;" |
pandoc -f csv -t org
| Greg Minshall|320418 |
|----------------------|
then that may be beautified... OK not moving so well, let me try with
HTML to Org:
psql -A -t -H -q -c "select peoplenames_peoplename, peoplenames_peopleid as
people from peoplenames where peoplenames_peoplename ~* 'min' limit 5;" |
pandoc -f html -t org
| Ali Amini | 131 |
| Jasmin Hatipović | 187 |
| Lamin Bangura | 211 |
| Errol Anthony Flemmings | 259 |
| Carlene Cummins | 302 |
So there it is, conversion from PostgreSQL to HTML to Org. 500 people
are converted with his timing:
real 0m0.558s
user 0m0.445s
sys 0m0.121s
Let us say I wish to enter new identity from which to send emails,
letters, then I have this function:
(defun rcd-db-table-identities-insert-new-row (&optional id)
"Add new identity."
(interactive)
(when-tabulated-id "identities"
(let* ((name (rcd-ask-get "Identity name: "))
(first-name (rcd-ask-get "First name: "))
(last-name (rcd-ask-get "Last name: "))
(e-mail (rcd-ask-get "E-mail: "))
(signature (rcd-ask-or-null "Signature: "))
(signature (if signature (sql-escape-string signature) "NULL"))
(signature-file (read-file-name "File name: "))
(signature-file (if signature-file (sql-escape-string
signature-file) "NULL"))
(organization (rcd-ask "Organization: "))
(organization (if organization (sql-escape-string organization)
"NULL"))
(account (cf-accounts-select))
(language (rcd-db-language-select))
(sql (format "INSERT INTO identities (identities_name,
identities_firstname, identities_lastname,
identities_email, identities_signature,
identities_signaturefile,
identities_organization,
identities_accounts, identities_languages)
VALUES ($1, $2, $3, $4, %s, %s, %s, $5, $6)"
signature signature-file organization)))
(rcd-sql sql cf-db name first-name last-name e-mail account language)
(rcd-tabulated-refresh))))
$ time psql -A -t -H -q -c "select peoplelisttypes_name from peoplelisttypes;"
| pandoc -f html -t org
| Competitor |
| Integrator |
| Investor |
| Partner |
| Reseller |
| Our company |
| Accounting |
| Mailing List |
| Mobile Network Provider |
| Family |
| Default |
| Managed Company or Business |
| Country |
| Company |
| Cooperative Society |
| Business |
| Press |
| Sales Leads |
real 0m0.317s
user 0m0.302s
sys 0m0.021s
For more help let me know. Keeping stuff in the ordered database is useful.
--
Jean
Take action in Free Software Foundation campaigns:
https://www.fsf.org/campaigns
In support of Richard M. Stallman
https://stallmansupport.org/
