[Orgmode] Re: [babel] Is this supported?

[Sébastien Vauban]

Hi Dan,

Dan Davison wrote:
> Sébastien Vauban <address@hidden> 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