[babel] Is this supported?

[babel] Is this supported?

[Sébastien Vauban]

Hi Eric and Dan,

I'm not sure whether what I'm searching for does already exist (or will ever)
-- that's why I'm asking.

I have to create multiple times a new column in a DB. Being LP-minded, I wanna
describe the code, tangle it, and be able to execute it at the client side.

Here my generic code for create a new column:

--8<---------------cut here---------------start------------->8---
#+srcname: add-column-in-table.sql(pTable,pColumn,pDatatype,pAcceptnullvalues)
    #+begin_src sql
        -- add column `pfiDossierSentToSecteur' (if column does not exist yet)
        IF NOT EXISTS (SELECT *
                       FROM INFORMATION_SCHEMA.COLUMNS
                       WHERE TABLE_NAME = 'pTable'
                       AND COLUMN_NAME = 'pColumn)
        BEGIN
            ALTER TABLE pTable
            ADD pColumn pDatatype pAcceptnullvalues
        END
        GO
    #+end_src
--8<---------------cut here---------------end--------------->8---

Is there a way to tangle it with some string replacements being made, such as:

| pTable            | dossier |
| pColumn           | pfiNew  |
| pDatatype         | string  |
| pAcceptnullvalues | NULL    |

I've tried the following, with no success:

--8<---------------cut here---------------start------------->8---
#+srcname: add-column-in-table(pTable=dossier,pColumn=pfiNew,pDatatype=string,pAcceptnullvalues=NULL)
    #+begin_src sql :tangle dossier.sql
        -- add column `pfiDossierSentToSecteur' (if column does not exist yet)
        IF NOT EXISTS (SELECT *
                       FROM INFORMATION_SCHEMA.COLUMNS
                       WHERE TABLE_NAME = 'pTable'
                       AND COLUMN_NAME = 'pColumn)
        BEGIN
            ALTER TABLE pTable
            ADD pColumn pDatatype pAcceptnullvalues
        END
        GO
    #+end_src
--8<---------------cut here---------------end--------------->8---

Is such a feature supported, or another way to come down to the same result?

Best regards,
  Seb

-- 
Sébastien Vauban



_______________________________________________
Emacs-orgmode mailing list
Please use `Reply All' to send replies to the list.
Emacs-orgmode-mXXj517/zsQ@public.gmane.org
http://lists.gnu.org/mailman/listinfo/emacs-orgmode

Re: [babel] Is this supported?

[Dan Davison]

Sébastien Vauban <wxhgmqzgwmuf@spammotel.com> writes:
<...>
> Is there a way to tangle it with some string replacements being made, such as:
>
> | pTable            | dossier |
> | pColumn           | pfiNew  |
> | pDatatype         | string  |
> | pAcceptnullvalues | NULL    |

>
> I've tried the following, with no success:
>
> #+srcname: add-column-in-table(pTable=dossier,pColumn=pfiNew,pDatatype=string,pAcceptnullvalues=NULL)

Hi Seb,

Those function-arguments / variable assignments won't affect the
*tangled* output. Afaik the only method for making substitutions in the
tangled output is the <<source block name>> block references. So one way
to achieve what you want would be to create a block for each string
replacement (Example [1] below; I don't know if I've got the quoting
right in the sql output). But perhaps Tom/Eric will have a better
answer.

Incidentally, it seems that we do not currently support variables when
*evaluating* an sql block. I.e. the function-arguments that you used:

> #+srcname: add-column-in-table(pTable=dossier,pColumn=pfiNew,pDatatype=string,pAcceptnullvalues=NULL)

will have no effect. The patch below[2] implements that. I'll let Eric
decide whether it's appropriate as I don't know anything about sql. Note
that you would need to quote the strings in your srcname line
(i.e. (pTable="dossier", ...))

Dan


>     #+begin_src sql :tangle dossier.sql
>         -- add column `pfiDossierSentToSecteur' (if column does not exist yet)
>         IF NOT EXISTS (SELECT *
>                        FROM INFORMATION_SCHEMA.COLUMNS
>                        WHERE TABLE_NAME = 'pTable'
>                        AND COLUMN_NAME = 'pColumn)
>         BEGIN
>             ALTER TABLE pTable
>             ADD pColumn pDatatype pAcceptnullvalues
>         END
>         GO
>     #+end_src
>
> Is such a feature supported, or another way to come down to the same result?

Footnotes:

[1] 
----------------------------------------------------------------
#+srcname: pTable
#+begin_src emacs-lisp
"dossier"
#+end_src

#+srcname: pColumn
#+begin_src emacs-lisp
"pfiNew"
#+end_src

#+srcname: pDatatype
#+begin_src emacs-lisp
"string"
#+end_src

#+srcname: pAcceptnullvalues
#+begin_src emacs-lisp
"NULL"
#+end_src

#+begin_src sql :tangle dossier.sql :engine mysql
        -- add column `pfiDossierSentToSecteur' (if column does not exist yet)
        IF NOT EXISTS (SELECT *
                       FROM INFORMATION_SCHEMA.COLUMNS
                       WHERE TABLE_NAME = <<pTable>>
                       AND COLUMN_NAME = <<pColumn>>)
        BEGIN
            ALTER TABLE <<pTable>>
            ADD <<pColumn>> <<pDatatype>> <<pAcceptnullvalues>>
        END
        GO
#+end_src
----------------------------------------------------------------

[2] 
----------------------------------------------------------------
diff --git a/contrib/babel/lisp/langs/org-babel-sql.el b/contrib/babel/lisp/langs/org-babel-sql.el
index 837c5fd..7e37fee 100644
--- a/contrib/babel/lisp/langs/org-babel-sql.el
+++ b/contrib/babel/lisp/langs/org-babel-sql.el
@@ -55,7 +55,8 @@
   "Execute a block of Sql code with org-babel.  This function is
 called by `org-babel-execute-src-block'."
   (message "executing Sql source code block")
-  (let* ((result-params (split-string (or (cdr (assoc :results params)) "")))
+  (let* ((processed-params (org-babel-process-params params))
+        (vars (second processed-params))
          (cmdline (cdr (assoc :cmdline params)))
          (engine (cdr (assoc :engine params)))
          (in-file (make-temp-file "org-babel-sql-in"))
@@ -66,6 +67,9 @@ called by `org-babel-execute-src-block'."
                                     (or cmdline "") in-file out-file))
                    ('nil (error "sql engine not specified"))
                     (t (error "no support for the %s sql engine" engine)))))
+    (mapc (lambda (pair)
+           (setq body (replace-regexp-in-string (format "%s" (car pair)) (cdr pair) body)))
+         vars)
     (with-temp-file in-file (insert body))
     (message command)
     (shell-command command)
----------------------------------------------------------------

Re: [babel] Is this supported?

[Sébastien Vauban]

Hi Dan,

Dan Davison wrote:
> Sébastien Vauban <wxhgmqzgwmuf-geNee64TY+gS+FvcfC7Uqw@public.gmane.org> writes:
>>
>> Is there a way to tangle it with some string replacements being made, such as:
>>
>> | pTable            | dossier |
>> | pColumn           | pfiNew  |
>> | pDatatype         | string  |
>> | pAcceptnullvalues | NULL    |
>>
>> I've tried the following, with no success:
>>
>> #+srcname: add-column-in-table(pTable=dossier,pColumn=pfiNew,pDatatype=string,pAcceptnullvalues=NULL)
>
> Those function-arguments / variable assignments won't affect the
> *tangled* output.

In fact, what I'm asking for, here, is some sort of "m4 preprocessing" with
stupid string replacement.

This would certainly be a "nice to have", for easing some tasks (be it
scripts, commands or functions) that look to each other... but for a tiny
detail.


> Afaik the only method for making substitutions in the tangled output is the
> <<source block name>> block references. So one way to achieve what you want
> would be to create a block for each string replacement (Example [1] below; I
> don't know if I've got the quoting right in the sql output). But perhaps
> Tom/Eric will have a better answer.
>
>>     #+begin_src sql :tangle dossier.sql
>>         -- add column `pfiDossierSentToSecteur' (if column does not exist yet)
>>         IF NOT EXISTS (SELECT *
>>                        FROM INFORMATION_SCHEMA.COLUMNS
>>                        WHERE TABLE_NAME = 'pTable'
>>                        AND COLUMN_NAME = 'pColumn)
>>         BEGIN
>>             ALTER TABLE pTable
>>             ADD pColumn pDatatype pAcceptnullvalues
>>         END
>>         GO
>>     #+end_src
>>
>> Is such a feature supported, or another way to come down to the same result?
>
> [1] 
> ----------------------------------------------------------------
> #+srcname: pTable
> #+begin_src emacs-lisp
> "dossier"
> #+end_src
>
> #+srcname: pColumn
> #+begin_src emacs-lisp
> "pfiNew"
> #+end_src
>
> #+srcname: pDatatype
> #+begin_src emacs-lisp
> "string"
> #+end_src
>
> #+srcname: pAcceptnullvalues
> #+begin_src emacs-lisp
> "NULL"
> #+end_src
>
> #+begin_src sql :tangle dossier.sql :engine mysql
>         -- add column `pfiDossierSentToSecteur' (if column does not exist yet)
>         IF NOT EXISTS (SELECT *
>                        FROM INFORMATION_SCHEMA.COLUMNS
>                        WHERE TABLE_NAME = <<pTable>>
>                        AND COLUMN_NAME = <<pColumn>>)
>         BEGIN
>             ALTER TABLE <<pTable>>
>             ADD <<pColumn>> <<pDatatype>> <<pAcceptnullvalues>>
>         END
>         GO
> #+end_src
> ----------------------------------------------------------------

A bit verbose, but this is exactly the workaround I asked for.

Excellent!  Thanks...

The "nice to have" feature would then be a shorter notation for such tiny
string replacements.


> Incidentally, it seems that we do not currently support variables when
> *evaluating* an sql block. I.e. the function-arguments that you used:
>
>> #+srcname: add-column-in-table(pTable=dossier,pColumn=pfiNew,pDatatype=string,pAcceptnullvalues=NULL)
>
> will have no effect. The patch below[2] implements that. I'll let Eric
> decide whether it's appropriate as I don't know anything about sql.

I currently cannot test it, as I'm using a MS SQL database (with command-line
program called `isql' -- for "interactive SQL") and play with databases in a
Windows VM (only used for that, what a pity!).

I'll let you know as soon as I can make such direct requests against a MySQL
DB (the one supported by your code).


> [2] 
> ----------------------------------------------------------------
> diff --git a/contrib/babel/lisp/langs/org-babel-sql.el b/contrib/babel/lisp/langs/org-babel-sql.el
> index 837c5fd..7e37fee 100644
> --- a/contrib/babel/lisp/langs/org-babel-sql.el
> +++ b/contrib/babel/lisp/langs/org-babel-sql.el
> @@ -55,7 +55,8 @@
>    "Execute a block of Sql code with org-babel.  This function is
>  called by `org-babel-execute-src-block'."
>    (message "executing Sql source code block")
> -  (let* ((result-params (split-string (or (cdr (assoc :results params)) "")))
> +  (let* ((processed-params (org-babel-process-params params))
> +        (vars (second processed-params))
>           (cmdline (cdr (assoc :cmdline params)))
>           (engine (cdr (assoc :engine params)))
>           (in-file (make-temp-file "org-babel-sql-in"))
> @@ -66,6 +67,9 @@ called by `org-babel-execute-src-block'."
>                                      (or cmdline "") in-file out-file))
>                     ('nil (error "sql engine not specified"))
>                      (t (error "no support for the %s sql engine" engine)))))
> +    (mapc (lambda (pair)
> +           (setq body (replace-regexp-in-string (format "%s" (car pair)) (cdr pair) body)))
> +         vars)
>      (with-temp-file in-file (insert body))
>      (message command)
>      (shell-command command)
> ----------------------------------------------------------------

Anyway, if you think it should behave better this way, then yes, commit it...


> Note that you would need to quote the strings in your srcname line (i.e.
> (pTable="dossier", ...))

I'm a bit confused (as you may have seen in my last posts) about when we do
have to quote strings and when we do have to avoid doing it. Would you have a
one-liner explanation about when we have to use quotes?

Best regards,
  Seb

-- 
Sébastien Vauban



_______________________________________________
Emacs-orgmode mailing list
Please use `Reply All' to send replies to the list.
Emacs-orgmode-mXXj517/zsQ@public.gmane.org
http://lists.gnu.org/mailman/listinfo/emacs-orgmode