From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from mp11.migadu.com ([2001:41d0:2:4a6f::]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits)) by ms5.migadu.com with LMTPS id 2JajM7l0X2OubwEAbAwnHQ (envelope-from ) for ; Mon, 31 Oct 2022 08:09:45 +0100 Received: from aspmx1.migadu.com ([2001:41d0:2:4a6f::]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits)) by mp11.migadu.com with LMTPS id wGSsM7l0X2NKKgAA9RJhRA (envelope-from ) for ; Mon, 31 Oct 2022 08:09:45 +0100 Received: from lists.gnu.org (lists.gnu.org [209.51.188.17]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by aspmx1.migadu.com (Postfix) with ESMTPS id 6B1AB11FDC for ; Mon, 31 Oct 2022 08:09:45 +0100 (CET) Received: from localhost ([::1] helo=lists1p.gnu.org) by lists.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1opOuf-0004M1-Lw; Mon, 31 Oct 2022 03:08:49 -0400 Received: from eggs.gnu.org ([2001:470:142:3::10]) by lists.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1opOue-0004KE-8v for emacs-orgmode@gnu.org; Mon, 31 Oct 2022 03:08:48 -0400 Received: from mout-p-101.mailbox.org ([2001:67c:2050:0:465::101]) by eggs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_CHACHA20_POLY1305:256) (Exim 4.90_1) (envelope-from ) id 1opOub-00055b-2C for emacs-orgmode@gnu.org; Mon, 31 Oct 2022 03:08:47 -0400 Received: from smtp202.mailbox.org (smtp202.mailbox.org [IPv6:2001:67c:2050:b231:465::202]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange ECDHE (P-384) server-signature RSA-PSS (4096 bits) server-digest SHA256) (No client certificate requested) by mout-p-101.mailbox.org (Postfix) with ESMTPS id 4N144l3cK8z9sQD; Mon, 31 Oct 2022 08:08:39 +0100 (CET) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=kraus.my; s=MBO0001; t=1667200119; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version:content-type:content-type: in-reply-to:in-reply-to:references:references; bh=7krRkvKry8Y20lUOJixEzJExUwhV82XRnSFSbOZi14s=; b=amIGne1i0uiA7qUlOgxzILLTB30x/W2mca0y0PkhCzMO/VO02rbPwAbopxVdUvOZrCYzd6 E36+Bc22DX7fGSoOJNwURNB7psshwp+KJ5xMZGmYPM9VMbv2jGP6ynjS4p/+gNG4U6Lnmt twgR+dlcpe/56k6qPlbtfdPAqnbnol7Yozepiy2ppgKF/rQM2cT0Ah47R1MlxY6nww+F4A SgKgmhilp2sgeCZv9lgxpPkM2MWDK7s1jtLXa6QfomYIJ7gAAXGNf9TgQLeT+R4MoB+ibD 1HLP1sZlWXtxGnU82GBFN1ZCASKJQJCC/o3ZeMWrV2va2MD52AyK33y32BopIQ== References: <87a71s9jio.fsf@kraus.my> <87v9kbttsp.fsf@kyleam.com> From: Daniel Kraus To: Stefano Rodighiero Cc: Kyle Meyer , emacs-orgmode@gnu.org Subject: Re: [PATCH] ob-sql: Respect database param when using dbconnection Date: Mon, 31 Oct 2022 08:02:42 +0100 In-reply-to: Message-ID: <8735b4lbbl.fsf@kraus.my> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="=-=-=" X-Rspamd-Queue-Id: 4N144l3cK8z9sQD Received-SPF: none client-ip=2001:67c:2050:0:465::101; envelope-from=daniel@kraus.my; helo=mout-p-101.mailbox.org X-Spam_score_int: -27 X-Spam_score: -2.8 X-Spam_bar: -- X-Spam_report: (-2.8 / 5.0 requ) BAYES_00=-1.9, DKIM_SIGNED=0.1, DKIM_VALID=-0.1, DKIM_VALID_AU=-0.1, DKIM_VALID_EF=-0.1, RCVD_IN_DNSWL_LOW=-0.7, SPF_HELO_NONE=0.001, SPF_NONE=0.001 autolearn=ham autolearn_force=no X-Spam_action: no action X-BeenThere: emacs-orgmode@gnu.org X-Mailman-Version: 2.1.29 Precedence: list List-Id: "General discussions about Org-mode." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Sender: "Emacs-orgmode" Errors-To: emacs-orgmode-bounces+larch=yhetil.org@gnu.org X-Migadu-Flow: FLOW_IN X-Migadu-Country: US ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=yhetil.org; s=key1; t=1667200185; h=from:from:sender:sender:reply-to:subject:subject:date:date: message-id:message-id:to:to:cc:cc:mime-version:mime-version: content-type:content-type:in-reply-to:in-reply-to: references:references:list-id:list-help:list-unsubscribe: list-subscribe:list-post:dkim-signature; bh=7krRkvKry8Y20lUOJixEzJExUwhV82XRnSFSbOZi14s=; b=rWaUjTo9vvebC+HDHaoTwlYb6rW7V79LY8RVqy1jN6H3D2l6yEVCGcJmnZXvKBW76Sf05l O8i4Whrp/TYbXc454OMb+HBdYLkcuk+WKCY239slSyda8mSIObJOubo2i2Cm1rPZvjtlnI Vf4peznLx3UOGEOaaJ3R0LrKNtIPLHlxF1hZxqJ64QXDv/TzQOr83gkYS1PRGCl4YHEiT9 mR7msVMBduB/a3FJtc7u00LTHc2Z3fBfQUUbrG3l6CaNTb36ZlLt5/2qFHvaFMV/9EEl5e k8ptYaPoI9ut2ffVlEKzMW7BuSaYkXhRcSEhUuRoqNlYPZFnW9NMzg0vp4bOZw== ARC-Seal: i=1; s=key1; d=yhetil.org; t=1667200185; a=rsa-sha256; cv=none; b=IZ5bOpMO0Q7in8cd7nhShaFHRKCK5vKJrKYdxQFKLkuHvSeqZPRhTM3YXwjxTK+fEWeteU RVYjIoSamLMliJSGH8jRKQRRUQRSFMSsWXL8PGGqkhlh8L30QQ/lMqK3+Z1NlqoQveMW5C g663udDk97c0zGX6YfhCfFiB6ztMtGeAMwd86ZwD5LvIMDGalO2J5K/gyBhMaoEiCbRCYn PlIXdXKTWeUfbGSuLJIBPRYJvnj5KVkieGWC8d9h51K8fqDrKP5p6DRu5CJ0O0cKunn03X /cft9nEs8QZwzDBbnwKX1sxyaARLAeKkDQ3WWA/7KqcJOYncZULSB2XYLFlZRQ== ARC-Authentication-Results: i=1; aspmx1.migadu.com; dkim=pass header.d=kraus.my header.s=MBO0001 header.b=amIGne1i; dmarc=none; spf=pass (aspmx1.migadu.com: domain of "emacs-orgmode-bounces+larch=yhetil.org@gnu.org" designates 209.51.188.17 as permitted sender) smtp.mailfrom="emacs-orgmode-bounces+larch=yhetil.org@gnu.org" X-Migadu-Spam-Score: -7.16 Authentication-Results: aspmx1.migadu.com; dkim=pass header.d=kraus.my header.s=MBO0001 header.b=amIGne1i; dmarc=none; spf=pass (aspmx1.migadu.com: domain of "emacs-orgmode-bounces+larch=yhetil.org@gnu.org" designates 209.51.188.17 as permitted sender) smtp.mailfrom="emacs-orgmode-bounces+larch=yhetil.org@gnu.org" X-Migadu-Queue-Id: 6B1AB11FDC X-Spam-Score: -7.16 X-Migadu-Scanner: scn0.migadu.com X-TUID: fZ5mjzfg6cBq --=-=-= Content-Type: text/plain Hi, sorry for replying to a 2 year old thread. Attached is a new patch with the change from Kyle and I also added a news entry. Not sure if the text for the news entry is any good, or if there should be an example etc. Feedback welcome. Otherwise, the patch itself is not much and I use it daily. Thanks, Daniel --=-=-= Content-Type: text/x-patch Content-Disposition: attachment; filename=0001-ob-sql.el-Respect-all-params-when-using-dbconnection.patch >From cd170dd691ba12ec81ef5c71db2868b33cd63ddf Mon Sep 17 00:00:00 2001 From: Daniel Kraus Date: Mon, 31 Oct 2022 07:52:09 +0100 Subject: [PATCH] ob-sql.el: Respect all params when using dbconnection * etc/ORG-NEWS (Miscellaneous): Document change * lisp/ob-sql.el (org-babel-find-db-connection-param): Make it possible to overwrite parameters that are set from :dbconnection --- etc/ORG-NEWS | 14 ++++++++++++++ lisp/ob-sql.el | 24 ++++++++++++------------ 2 files changed, 26 insertions(+), 12 deletions(-) diff --git a/etc/ORG-NEWS b/etc/ORG-NEWS index 6e875deb6..b542da34b 100644 --- a/etc/ORG-NEWS +++ b/etc/ORG-NEWS @@ -457,6 +457,20 @@ The new variable name is =org-plantuml-args=. It now applies to both jar PlantUML file and executable. ** Miscellaneous +*** SQL Babel ~:dbconnection~ parameter can be mixed with other SQL Babel parameters + +Before you could either specify SQL parameters like ~:dbhost~, +~:dbuser~, ~:database~, etc or a ~:dbconnection~ parameter which looks +up all other parameters from the ~sql-connection-alist~ variable. Now +it's possible to specify a ~:dbconnection~ and additionally other +parameters that will add or overwrite the parameters coming from +~sql-connection-alist~. + +E.g. if you have a connection in your ~sql-connection-alist~ to a +server that has many databases, you don't need an entry for every +database but instead can just specify ~:database~ next to your +~:dbconnection~ parameter. + *** Post-processing code blocks can return an empty list When the result of a regular code block is nil, then that was already diff --git a/lisp/ob-sql.el b/lisp/ob-sql.el index d1256bf83..626d595c9 100644 --- a/lisp/ob-sql.el +++ b/lisp/ob-sql.el @@ -222,18 +222,18 @@ then look for the parameter into the corresponding connection defined in `sql-connection-alist', otherwise look into PARAMS. See `sql-connection-alist' (part of SQL mode) for how to define database connections." - (if (assq :dbconnection params) - (let* ((dbconnection (cdr (assq :dbconnection params))) - (name-mapping '((:dbhost . sql-server) - (:dbport . sql-port) - (:dbuser . sql-user) - (:dbpassword . sql-password) - (:dbinstance . sql-dbinstance) - (:database . sql-database))) - (mapped-name (cdr (assq name name-mapping)))) - (cadr (assq mapped-name - (cdr (assoc dbconnection sql-connection-alist))))) - (cdr (assq name params)))) + (or (cdr (assq name params)) + (and (assq :dbconnection params) + (let* ((dbconnection (cdr (assq :dbconnection params))) + (name-mapping '((:dbhost . sql-server) + (:dbport . sql-port) + (:dbuser . sql-user) + (:dbpassword . sql-password) + (:dbinstance . sql-dbinstance) + (:database . sql-database))) + (mapped-name (cdr (assq name name-mapping)))) + (cadr (assq mapped-name + (cdr (assoc dbconnection sql-connection-alist)))))))) (defun org-babel-execute:sql (body params) "Execute a block of Sql code with Babel. -- 2.38.1 --=-=-= Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Stefano Rodighiero writes: > On Mon, Jun 1, 2020 at 4:16 AM Kyle Meyer wrote: > > Daniel Kraus writes: >> >> > I use ob-sql with the :dbconnection param so I don't have my username >> and password in my org file. >> > But often I don't want to use the default database from the dbconnecti= on >> alist but >> > rather specify it explicitly with :database. >> > Attached is a patch that fixes this. >> > > Thank you @Daniel > > >> [=E2=80=A6] >> From what I can gather from your description, this looks reasonable. >> I'm not an ob-sql user, so perhaps I missing something, but would it >> make sense for any connection parameter to take precedence if explicitly >> given in the source block header (i.e. something like the patch below)? >> [+cc Stefano, who added the :dbconneciton feature.] >> > > I think it makes sense. > > (I personally handle cases like those described by Daniel differently, > keeping distinct sql-connection-alist entries for each DB > param combination I might need, but I can imagine why someone > would want to "override" the database or the host params. > For port, user and password I have more difficulties imagining a > case where combinations of those params would need override, > but I think @Kyle's generic solution is better) > > s. --=-=-=--