Skip to main content

JSON query expressions using SQLite

Project description

jx-sqlite

JSON query expressions using SQLite

PyPI Latest Release Build Status Coverage Status

Summary

This library will manage your database schema to store JSON documents. You get all the speed of a well-formed database schema without the schema migration headaches.

Status

Significant updates to the supporting libraries has broken this code. It still works for the simple cases that require it

Jan 2024 - 118 of 334 tests ignored

Installation

pip install jx-sqlite

Code Example

The smoke test, found in the tests is a simple example of how to use this library.

import jx_sqlite

table = (
    jx_sqlite
    .Container(filename="my.db")
    .get_or_create_facts("my_table")
    .insert([{"os": "linux", "value": 42}])
    .query({
        "select": "os",
        "where": {"gt": {"value": 0}}
    })
)

More

This project is an attempt to store JSON documents in SQLite so that they are accessible via SQL. The hope is this will serve a basis for a general document-relational map (DRM), and leverage the database's query optimizer. jx-sqlite is responsible for expanding the schema dynamically as new JSON documents are encountered. It also strives to ensure old queries against the new schema have the same meaning; the same results.

The most interesting, and most important feature is that we query nested object arrays as if they were just another table. This is important for two reasons:

  1. Inner objects {"a": {"b": 0}} are a shortcut for nested arrays {"a": [{"b": 0}]}, plus
  2. Schemas can be expanded from one-to-one to one-to-many {"a": [{"b": 0}, {"b": 1}]}.

Motivation

JSON is a nice format to store data, and it has become quite prevalent. Unfortunately, databases do not handle it well, often a human is required to declare a schema that can hold the JSON before it can be queried. If we are not overwhelmed by the diversity of JSON now, we soon will be. There will be more JSON, of more different shapes, as the number of connected devices( and the information they generate) continues to increase.

Contributing

Contributions are always welcome! The best thing to do is find a failing test, and try to fix it.

These instructions will get you a copy of the project up and running on your local machine for development and testing purposes.

$ git clone https://github.com/klahnakoski/jx-sqlite
$ cd jx-sqlite

Running tests

There are over 300 tests used to confirm the expected behaviour: They test a variety of JSON forms, and the queries that can be performed on them. Most tests are further split into three different output formats ( list, table and cube).

The requirements.lock file is the last successful combination that passed all tests, despite the version conflicts.

python.exe -m pip install --no-deps -r tests\requirements.lock

Linux

export PYTHONPATH=.:vendor
python -m unittest discover -v -s tests

Windows

set PYTHONPATH=.;vendor
python -m unittest discover -v -s tests

Technical Docs

License

This project is licensed under Mozilla Public License, v. 2.0. If a copy of the MPL was not distributed with this file, You can obtain one at http://mozilla.org/MPL/2.0/.

History

Jan 2024 - Attempt to resurrect this project (118 of 334 tests ignored)

Sep 2018 - Upgrade libs, start refactoring to work with other libs

Dec 2017 - A number of tests were added, but they do not pass.

Sep 2017 - GSoC work completed, all but a few tests pass.

GSOC

Good work by Rohit Kumar. You may see the end result on gsoc branch. Installation requires python2.7, and will require some version fixing to get running.

See the demonstration video

Work done up to the deadline of GSoC'17:

Project details


Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Source Distribution

jx_sqlite-5.673.25037.tar.gz (61.5 kB view details)

Uploaded Source

Built Distribution

jx_sqlite-5.673.25037-py3-none-any.whl (109.3 kB view details)

Uploaded Python 3

File details

Details for the file jx_sqlite-5.673.25037.tar.gz.

File metadata

  • Download URL: jx_sqlite-5.673.25037.tar.gz
  • Upload date:
  • Size: 61.5 kB
  • Tags: Source
  • Uploaded using Trusted Publishing? No
  • Uploaded via: twine/5.0.0 CPython/3.9.6

File hashes

Hashes for jx_sqlite-5.673.25037.tar.gz
Algorithm Hash digest
SHA256 b0f7d25a1edcf241fd67824da3146d3ac33e17c96f05996060aa73e75bc784ad
MD5 0f205a1d5ca070e2f0ac0be5ba58bb34
BLAKE2b-256 acd853b83599cf5b76cf1b0286f39de21d31392b01745ee987b5f92c603e2a7d

See more details on using hashes here.

File details

Details for the file jx_sqlite-5.673.25037-py3-none-any.whl.

File metadata

File hashes

Hashes for jx_sqlite-5.673.25037-py3-none-any.whl
Algorithm Hash digest
SHA256 4535cf9c4569a3dd6506af34886426d1bc21fc6b92090305aea83e273a7632be
MD5 2e5d0d292b2540f9ebfa754b6193468a
BLAKE2b-256 6baade17f837963435dca5abb61e781d2506f55db4a3e9ae53a734bf846dda1d

See more details on using hashes here.

Supported by

AWS Cloud computing and Security Sponsor Datadog Monitoring Fastly CDN Google Download Analytics Pingdom Monitoring Sentry Error logging StatusPage Status page