Re: [babel] [PATCH] Add support for MonetDB for SQL blocks

[Eric Schulte <eric.schulte@gmx.com>]

Applied, Thanks!

Viktor Rosenfeld <listuser36@googlemail.com> writes:

> Hi,
>
> the attached patch adds support for evaluating SQL blocks on MonetDB.
> The MonetDB client normally requires the password to be inputted on the
> console. To get around this, you have to use a dotfile ~/.monetdb with
> authentification data. Note that this file is ignored if you specify a
> user on the cmdline. See
> http://www.monetdb.org/Documentation/mclient-man-page for details.
>
> A usage example follows.
>
> Setup MonetDB test database and authentification data
>
> #+BEGIN_SRC sh :results output verbatim
> MONETDB_DIR=$HOME/unix/var/monetdb/demodb
> monetdbd create $MONETDB_DIR
> monetdbd start $MONETDB_DIR
> monetdb create demodb
> monetdb release demodb
> cat > ~/.monetdb <<EOF
> user=monetdb
> password=monetdb
> EOF
> #+END_SRC
>
> #+RESULTS:
> : created database in maintenance mode: demodb
> : taken database out of maintenance mode: demodb
>
> Data is returned without column names (the default return format
> cannot be parsed by Babel).
>
> #+BEGIN_SRC sql :engine monetdb :cmdline demodb
> CREATE TABLE foo ( bar INTEGER );
> SELECT 'Table count', count(*) FROM foo;
> #+END_SRC
>
> #+RESULTS:
> | Table count | 0 |
>
> The parameter "-i" is required on the command line in order to use
> special client commands. Also note that a newline is required at the
> end if the last line contains a special client command.
>
> #+BEGIN_SRC sql :engine monetdb :cmdline demodb -i :results output
> verbatim
> \d
> \?
>
> #+END_SRC
>
> #+RESULTS:
> #+begin_example
> TABLE  sys.foo
> \?      - show this message
> \<file  - read input from file
> \>file  - save response in file, or stdout if no file is given
> \|cmd   - pipe result to process, or stop when no command is given
> \h      - show the readline history
> \D table- dumps the table, or the complete database if none given.
> \d[Stvsfn]+ [obj] - list database objects, or describe if obj given
> \A      - enable auto commit
> \a      - disable auto commit
> \e      - echo the query in sql formatting mode
> \f      - format using a built-in renderer {csv,tab,raw,sql,xml}
> \w#     - set maximal page width (-1=unlimited, 0=terminal width,
>>0=limit to num)
> \r#     - set maximum rows per page (-1=raw)
> \L file - save client/server interaction
> \X      - trace mclient code
> \q      - terminate session
> #+end_example
>
> Have fun!
>
> Viktor
>
> diff --git a/lisp/ob-sql.el b/lisp/ob-sql.el
> index 68bd95a..20fbad3 100644
> --- a/lisp/ob-sql.el
> +++ b/lisp/ob-sql.el
> @@ -70,6 +70,10 @@ This function is called by `org-babel-execute-src-block'."
>                         (org-babel-temp-file "sql-out-")))
>  	 (header-delim "")
>           (command (case (intern engine)
> +                    ('monetdb (format "mclient -f tab %s < %s > %s"
> +                                      (or cmdline "")
> +                                      (org-babel-process-file-name in-file)
> +                                      (org-babel-process-file-name out-file)))
>                      ('msosql (format "osql %s -s \"\t\" -i %s -o %s"
>                                       (or cmdline "")
>                                       (org-babel-process-file-name in-file)

-- 
Eric Schulte
http://cs.unm.edu/~eschulte/