jamesql 0.2.0

A JameSQL database implemented in Python.

JameSQL

A JameSQL database implemented in Python.

This project has support for:

• Inserting records
• Deleting records
• Searching for records that match a query
• Searching for records that match multiple query conditions

This database does not enforce a schema, so you can insert records with different fields.

Here is an example of a query run in the JameSQL web interface:

Installation

To install this project, run:

pip install jamesql

Usage

Create a database

To create a database, use the following code:

from nosql import NoSQL

index = JameSQL()

Add documents to a database

To add documents to a database, use the following code:

index.add({"title": "tolerate it", "artist": "Taylor Swift"})
index.insert({"title": "betty", "artist": "Taylor Swift"})

When documents are added, a uuid key is added for use in uniquely identifying the document.

Search for documents

A query has the following format:

{
    "query": {},
    "limit": 2,
    "sort_by": "song",
    "skip": 1
}

• query is a dictionary that contains the fields to search for.
• limit is the maximum number of documents to return. (default 10)
• sort_by is the field to sort by. (default None)
• skip is the number of documents to skip. This is useful for implementing pagination. (default 0)

limit, sort_by, and skip are optional.

Within the query key you can query for documents that match one or more conditions.

An empty query returns no documents.

You can retrieve all documents by using a catch-all query, which uses the following syntax:

{
    "query": "*",
    "limit": 2,
    "sort_by": "song",
    "skip": 1
}

This is useful if you want to page through documents. You should supply a sort_by field to ensure the order of documents is consistent.

Document ranking

By default, documents are ranked in no order. If you provide a sort_by field, documents are sorted by that field.

For more advanced ranking, you can use the boost feature. This feature lets you boost the value of a field in a document to calculate a final score.

The default score for each field is 1.

To use this feature, you must use boost on fields that have an index.

Here is an example of a query that uses the boost feature:

{
    "query": {
        "or": {
            "post": {
                "contains": "taylor swift",
                "strict": False,
                "boost": 1
            },
            "title": {
                "contains": "desk",
                "strict": True,
                "boost": 25
            }
        }
    },
    "limit": 4,
    "sort_by": "_score",
}

This query would search for documents whose post field contains taylor swift or whose title field contains desk. The title field is boosted by 25, so documents that match the title field are ranked higher.

The score for each document before boosting is equal to the number of times the query condition is satisfied. For example, if a post contains taylor swift twice, the score for that document is 2; if a title contains desk once, the score for that document is 1.

Documents are then ranked in decreasing order of score.

Condition matching

There are three operators you can use for condition matching:

• equals
• contains
• starts_with

Here is an example of a query that searches for documents that have the artist field set to Taylor Swift:

query = {
    "query": {
        "artist": {
            "equals": "Taylor Swift"
        }
    }
}

You can also search for documents that have the artist field set to Taylor Swift and the title field set to tolerate it:

query = {
    "query": {
        "and": [
            {
                "artist": {
                    "equals": "Taylor Swift"
                }
            },
            {
                "title": {
                    "equals": "tolerate it"
                }
            }
        ]
    }
}

You can nest conditions to create complex queries, like:

query = {
    "query": {
        "or": {
            "and": [
                {"title": {"starts_with": "tolerate"}},
                {"title": {"contains": "it"}},
            ],
            "lyric": {"contains": "kiss"},
        }
    },
    "limit": 2,
    "sort_by": "title",
}

To search for documents that match a query, use the following code:

result = index.search(query)

This will return a list of documents that match the query.

Strict matching

By default, a search query on a text field will find any document where the field contains any word in the query string. For example, a query for tolerate it on a title field will match any document whose title that contains tolerate or it. This is called a non-strict match.

Non-strict matches are the default because they are faster to compute than strict matches.

If you want to find documents where terms appear next to each other in a field, you can do so with a strict match. Here is an example of a strict match:

query = {
    "query": {
        "title": {
            "contains": "tolerate it",
            "strict": True
        }
    }
}

This will return documents whose title contains tolerate it as a single phrase.

Fuzzy matching

By default, search queries look for the exact string provided. This means that if a query contains a typo (i.e. searching for tolerate ip instead of tolerate it), no documents will be returned.

JameSQL implements a limited form of fuzzy matching. This means that if a query contains a typo, JameSQL will still return documents that match the query.

The fuzzy matching feature matches documents that contain one typo. If a document contains more than one typo, it will not be returned. A typo is an incorrectly typed character. JameSQL does not support fuzzy matching that accounts for missing or additional characters (i.e. tolerate itt will not match tolerate it).

You can enable fuzzy matching by setting the fuzzy key to True in the query. Here is an example of a query that uses fuzzy matching:

query = {
    "query": {
        "title": {
            "contains": "tolerate ip",
            "fuzzy": True
        }
    }
}

Update documents

You need a document UUID to update a document. You can retrieve a UUID by searching for a document.

Here is an example showing how to update a document:

response = index.search(
    {
        "query": {"title": {"equals": "tolerate it"}},
        "limit": 10,
        "sort_by": "title",
    }
)

uuid = response["documents"][0]["uuid"]

index.update(uuid, {"title": "tolerate it (folklore)", "artist": "Taylor Swift"})

update is an override operation. This means you must provide the full document that you want to save, instead of only the fields you want to update.

Delete documents

You need a document UUID to delete a document. You can retrieve a UUID by searching for a document.

Here is an example showing how to delete a document:

response = index.search(
    {
        "query": {"title": {"equals": "tolerate it"}},
        "limit": 10,
        "sort_by": "title",
    }
)

uuid = response["documents"][0]["uuid"]

index.remove(uuid)

You can validate the document has been deleted using this code:

response = index.search(
    {
        "query": {"title": {"equals": "tolerate it"}},
        "limit": 10,
        "sort_by": "title",
    }
)

assert len(response["documents"]) == 0

Web Interface

JameSQL comes with a limited web interface designed for use in testing queries.

Note: You should not use the web interface if you are extending the query engine. Full error messages are only available in the console when you run the query engine.

To start the web interface, run:

python3 web.py

The web interface will run on localhost:5000.

Testing

This project comes with two test suites:

• Unit tests
• A benchmarking test

You can run the unit tests using:

pytest tests/test.py

You can run the benchmark test using:

pytest tests/stress.py

The benchmark test evaluates how long it takes to run a query in a database with 300,000 records.

License

This project is licensed under an MIT license.