##########################
gb_sql: JSON and SQL tools
##########################

This is library of bash scripts to manipulate and copy
data between `JSON <https://json.org>`_ files,
bash variables, the Postgresql database, and Scilab scripts.

************
Installation
************

Get the `git` repository. Assuming you keep your git repos
in `~/git` then::

  cd ~/git
  git clone lilo.science.ru.nl:/vol/thchem/git/gb_sql
  # If you already have it, you may want to update it:
  git pull

Next, make sure this directory is in your `GB_PATH`.
This can be done with commands from the `gb_bash` library (described
in :ref:`gb_bash: managing bash functions` ) to achieve this::

  gb_addpath -n GB_PATH "$HOME"/git/gb_sql

**********
Jq command
**********

The ``Jq`` bash function extends the features of the linux
``jq`` (`jqlang.github.io <https://jqlang.github.io/jq>`_)
command for processing `JSON <http://json.org>`_ datastructures. It can input
JSON data from its argument, from file, and from bash
environment variables. The data can be written to standard output, to
file, or to bash environment variables.

First load the ``Jq`` bash function with::

  gb_load gb_jq.sh
  # Or, inside a bash script:
  gb_load gb_jq.sh || { echo "$0 ERROR: failed to load gb_jq.sh, exit = $?"; exit 1;}

Jq, input JSON
==============

The ``Jq`` bash function can take JSON input as arguments::

  Jq city:Nijmegen number:42 ok:true vector:[1,2,3] par:{university:Radboud,number:134}

This returns a `JSON <http://json.org>`_ data structure to ``stdout``:

.. code-block:: json

  {"city":"Nijmegen","number":42,"ok":true,"vector":[1,2,3],"name":{"university":"Radboud"}}

The following data types are detected:

  * numbers: non-negative integers and reals, e.g. ``0, 1, 9, 1.2e-5``
  * logicals ``true`` and ``false``
  * arrays, start with ``[``, e.g, ``[a,b,c]``
  * JSON-objects, start with ``{``
  * strings, otherwise. May alos be forced with double quotes, ``key:\"7a\"``

In other cases, an explicit JSON string can be used with the ``-s`` flag::

  Jq -s {txt:\"7.3a\",b:-42}

Gives:

.. code-block:: json

   {"txt":"7.3a","b":-42}

It is also possible to read from files::

   Jq par1.json ../par2.json

Jq, process JSON with ``jq`` filter
===================================
By default, arguments will overwrite values set before it::

   Jq par:{T:273,unit:K} par:{T:291}

gives:

.. code-block:: json

   {"par:":{"T":273,"unit":"K"},"par":{"T":291}}

An argument starting with a dot ``.`` is used as a
filter of the ``jq`` command (see `https://jqlang.github.io/jq) <https://jqlang.github.io/jq>`_ ::

   Jq a:3 b:7  .c=.a*.b+1

gives:

.. code-block:: json

   {"a":3,"b":7,"c":22}

Jq, output to file
==================
Output may be "pretty printed" with the ``-pp`` flag::

    Jq a:40 par:{fac:0.2} .c=.a*.par.fac -pp

.. code-block:: json

    {
      "a": 40,
      "par": {
        "fac": 0.2
      },
      "c": 8
    }

Output is written to a file with with ``-w <filename>`` option::

    Jq a:41 -w par.json
    # To also write to stdout use -p or -pp
    Jq a:41 -w par.json -p

Jq, i/o with environment variables
==================================
To export the JSON data as environment variable ``DB_CURRENT``::

    Jq DB_CURRENT a:42
    echo $DB_CURRENT
    # {"a":42}
    # The same can be done with the -e (--export) flag
    Jq -e DB_CURRENT a:42
    # The -S flag is equivalent to ``-e DB_CURRENT``
    Jq -S a:42

The ``DB_CURRENT`` environment variable is
used by the ``db_get`` function in the ``gsci_lib6/lib_api``
Scilab library to input data.

To assign a specific element of the JSON data to a bash
environment variable::

    Jq par:{Etot:42,unit:eV}  E=.par.Etot
    echo $E
    # 42


To import JSON from a bash variable, simply expand it as string::

    export par={a:42}
    Jq  "$par" -pp

To import data from the ``DB_CURRENT`` bash environment variable
use the ``-G`` (``--get``) flag::

    Jq -S a:42
    Jq -G -p
    # {"a":42}

**********************
Pg command: PostgreSQL
**********************

First, setup database access using `Db` (see `gb_sql.sh`).

To create a table::

    Pg -t mytable init host varchar UNIQUE, load numeric

Here `mytable` is the name of the PostgreSQL table. The `Pg` command
will export the table name as `$PG_TABLE`, so subsequent `Pg` commands
given in the same terminal for the same table do not require
the `-t table_name` argument.

To list the table use::

    Pg ls

The above example gives::

     id | host | load | created_at | updated_at 
    ----+------+------+------------+-----------
    (0 rows)

The column `id` is automatically included, it gives each record
a unique number (1,2,3,...). The columns `created_at` and `updated_at`
are also always included.


*****
gb_sc
*****
New project::

  Sc init HeCO2fm_v24

After run copy results for plotting on PC or laptop::

  Sc get HeCO2fm_v24
  cd HeCO2fm_v24/run1
  Sc cp