J

jdb

Name Last Update
debian Loading commit data...
sqlparser Loading commit data...
vendor Loading commit data...
LICENSE Loading commit data...
README.md Loading commit data...
format.go Loading commit data...
jdb.go Loading commit data...
jdb_test.go Loading commit data...
main.go Loading commit data...
main_test.go Loading commit data...
sql.go Loading commit data...
sql_test.go Loading commit data...

jdb

A simple, read-only database backed by JSON-encoded files on the filesystem, intended for human-editable information distributed by configuration management systems.

Installation

To compile from source using go to retrieve the sources:

$ go get -u git.autistici.org/ale/jdb

You will need the libsqlite libraries and include files to compile the binary. Otherwise a jdb Debian package is provided at the following repository (key ID 0xC0EAC2F9CE9ED9B0):

$ echo deb http://debian.incal.net/debian common/ \
    sudo tee -a /etc/apt/sources.list.d/incal.list
$ curl http://debian.incal.net/repo.key | sudo apt-key add -
$ sudo apt-get update
$ sudo apt-get install jdb

Both these options should install the jdb binary somewhere in your PATH.

Data structure

The database is a hierarchical associative map (dicts that can contain other dicts or lists), whose named values can be iterable containers or scalar values.

The data on the filesystem can be stored in either a simple JSON-encoded file, or a hierarchy of JSON-encoded files (that must end with a .json extension). In the directory case, files represent the contents of objects at the position in the hierarchy given by their relative path to the root.

An example might be clearer: the following directory structure

dbdir/hosts/host1.json:
    { "name": "host1" }
dbdir/hosts/host2.json:
    { "name": "host2" }
dbdir/global.json:
    { "env": "test" }

will result in the following data, if we pass the --db=dbdir argument to jdb:

{
    "hosts": {
        "host1": {
            "name": "host1"
        },
        "host2": {
            "name": "host2"
        }
    },
    "global": {
        "env": "test"
    }
}

Querying

jdb allows SQL complex queries on the data (using in-memory SQLite), but it also supports a simpler expression-based query language for simpler scenarios.

Every object in the database can be identified by its full path: the list of attributes, from the top down, used to find the specific object.

Let's consider a simple data structure (for instance in config.json):

{
    "prod": {
        "hosts": {
            "host1": {
                "name": "host1",
                "addr": {
                    "ipv4": "1.2.3.4",
                    "ipv6": "2001::1"
                }
            },
            "host2": {
                "name": "host2",
                "addr": {
                    "ipv4": "2.3.4.5"
                }
            }
        }
    }
}

get command

The simplest command is get, which can be used to retrieve an object from the database using its path (note that the default output format is JSON):

$ jdb --db=config.json get prod.hosts.host1.name
"host1"

$ jdb --db=config.json get prod.hosts.host1.addr
{
    "ipv4": "1.2.3.4",
    "ipv6": "2001::1"
}

query command

The query command allows us to find objects using a base path and a boolean expression, which will be evaluated for each object contained in the one identified by the path. To clarify with an example:

$ jdb --db=config.json query prod.hosts "name == 'host1'"
[
    {
        "name": "host1",
        "addr": {
            "ipv4": "1.2.3.4",
            "ipv6": "2001::1"
        }
    }
]

When evaluating the query expression, you can reference nested attributes using their relative path:

$ jdb --db=config.json query prod.hosts "addr.ipv4 == '2.3.4.5'"
[
    {
        "name": "host2",
        "addr": {
            "ipv4": "2.3.4.5"
        }
    }
]

The full expression syntax is documented at the (govaluate)[https://github.com/Knetic/govaluate#what-operators-and-types-does-this-support] website.

sql command

A more powerful way to query the database is using the SQL language: you can use the path of an object as a table, and it's possible to join multiple tables.

$ jdb --db=config.json sql \
    "SELECT name FROM prod.hosts WHERE addr_ipv4 = '2.3.4.5'"
[
    {
        "name": "host2"
    }
]

Note that due to limitations of the SQL parser you can use the dot as an object path separator in for tables in the FROM clause, but not for nested attributes: in this case you need to use an underscore instead (the dot has a special meaning in SQL).

When running a SELECT over a list of scalar values, a synthetic column is created with the name value.