CVE-2017-16615 (Python, published 2017-11-08T03:29Z)
Description: value: An exploitable vulnerability exists in the YAML parsing functionality in the parse_yaml_query method in parser.py in MLAlchemy before 0.2.2. When processing YAML-Based queries for data, a YAML parser can execute arbitrary Python commands resulting in command execution because load is used where safe_load should have been used. An attacker can insert Python into loaded YAML to trigger this vulnerability.
Severity: HIGH (CVSS v3 base score: 9.8)
Weakness: NVD-CWE-noinfo \u2014 Insufficient Information
There is insufficient information about the issue to classify it; details are unkown or unspecified.
Repository: https://github.com/thanethomson/MLAlchemy
Files changed:
.gitignore
.travis.yml
LICENSE.md
MANIFEST.in
README.md
README.rst
mlalchemy/__init__.py
mlalchemy/constants.py
mlalchemy/errors.py
mlalchemy/parser.py
mlalchemy/structures.py
mlalchemy/testing.py
mlalchemy/utils.py
requirements.txt
scripts/pypi.sh
scripts/translate_readme.sh
setup.cfg
setup.py
tests/__init__.py
tests/test_basic_parsing.py
tests/test_complex_parsing.py
tests/test_sqlalchemy_querying.py
tests/test_yaml_security.py
Commit message: attempting to fix security flaw (issue #1)


Vulnerable code:

Fixed code:
# Byte-compiled / optimized / DLL files
__pycache__/
*.py[cod]
*$py.class

# C extensions
*.so

# Distribution / packaging
.Python
env/
build/
develop-eggs/
dist/
downloads/
eggs/
.eggs/
lib/
lib64/
parts/
sdist/
var/
*.egg-info/
.installed.cfg
*.egg

# PyInstaller
#  Usually these files are written by a python script from a template
#  before PyInstaller builds the exe, so as to inject date/other infos into it.
*.manifest
*.spec

# Installer logs
pip-log.txt
pip-delete-this-directory.txt

# Unit test / coverage reports
htmlcov/
.tox/
.coverage
.coverage.*
.cache
nosetests.xml
coverage.xml
*,cover
.hypothesis/

# Translations
*.mo
*.pot

# Django stuff:
*.log
local_settings.py

# Flask instance folder
instance/

# Scrapy stuff:
.scrapy

# Sphinx documentation
docs/_build/

# PyBuilder
target/

# IPython Notebook
.ipynb_checkpoints

# pyenv
.python-version

# celery beat schedule file
celerybeat-schedule

# dotenv
.env

# virtualenv
venv/
ENV/

# Spyder project settings
.spyderproject

# Rope project settings
.ropeproject

# Additional folders
bin/
include/
pip-selfcheck.json

# IntelliJ files
*.iml
/.idea/

# Generated folders
public/

*.bak

language: python
python:
  - "2.7"
  - "3.5"
  - "3.6"
install:
  - "pip install -r requirements.txt"
script:
  - "python -m unittest discover"
# The MIT License (MIT)

Copyright (c) 2017 Thane Thomson

Permission is hereby granted, free of charge, to any person obtaining a copy of
this software and associated documentation files (the "Software"), to deal in
the Software without restriction, including without limitation the rights to
use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies
of the Software, and to permit persons to whom the Software is furnished to do
so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
SOFTWARE.
include README.rst
include LICENSE.md
include requirements.txt
# MLAlchemy

[![Build Status](https://travis-ci.org/thanethomson/MLAlchemy.svg?branch=master)](https://travis-ci.org/thanethomson/MLAlchemy)
[![PyPI](https://img.shields.io/pypi/v/mlalchemy.svg)](https://pypi.python.org/pypi/mlalchemy)
[![PyPI](https://img.shields.io/pypi/pyversions/mlalchemy.svg)](https://pypi.python.org/pypi/mlalchemy)

## Overview
MLAlchemy is a Python-based utility library aimed at allowing relatively safe
conversion from YAML/JSON to SQLAlchemy read-only queries. One use case here is
to allow RESTful web applications (written in Python) to receive YAML- or
JSON-based queries for data, e.g. from a front-end JavaScript-based application.

The name "MLAlchemy" is an abbreviation for "Markup Language for
SQLAlchemy".

## Installation
Installation via PyPI:

```bash
> pip install mlalchemy
```

## Query Examples
To get a feel for what MLAlchemy queries look like, take a look at the
following. **Note**: All field names are converted from `camelCase` or `kebab-case`
to `snake_case` prior to query execution.

### Example YAML Queries
Fetching all the entries from a table called `Users`:

```yaml
from: Users
```

Limiting the users to only those with the last name "Michaels":

```yaml
from: Users
where:
  last-name: Michaels
```

A more complex YAML query:

```yaml
from: Users
where:
  $or:
    last-name: Michaels
    first-name: Michael
  $gt:
    date-of-birth: 1984-01-01
```

The raw SQL query for the above would look like:

```sql
SELECT * FROM users WHERE
  (last_name = "Michaels" OR first_name = "Michael") AND
  (date_of_birth > "1984-01-01")
```

### Example JSON Queries
The same queries as above, but in JSON format. To fetch all entries
in the `Users` table:

```json
{
    "from": "Users"
}
```

Limiting the users to only those with the last name "Michaels":

```json
{
    "from": "Users",
    "where": {
        "lastName": "Michaels"
    }
}
```

And finally, the more complex query:

```json
{
    "from": "Users",
    "where": {
        "$or": {
            "lastName": "Michaels",
            "firstName": "Michael"
        },
        "$gt": {
            "dateOfBirth": "1984-01-01"
        }
    }
}
```

## Usage
A simple example of how to use MLAlchemy:

```python
from sqlalchemy import create_engine, Column, Integer, String, Date
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

from mlalchemy import parse_yaml_query, parse_json_query

Base = declarative_base()


class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True)
    first_name = Column(String)
    last_name = Column(String)
    date_of_birth = Column(Date)


# use an in-memory SQLite database for this example
engine = create_engine("sqlite:///:memory:")
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()

# add a couple of dummy users
user1 = User(first_name="Michael", last_name="Anderson", date_of_birth=date(1980, 1, 1))
user2 = User(first_name="James", last_name="Michaels", date_of_birth=date(1976, 10, 23))
user3 = User(first_name="Andrew", last_name="Michaels", date_of_birth=date(1988, 8, 12))
session.add_all([user1, user2, user3])
session.commit()

# we need a lookup table for MLAlchemy
tables = {
    "User": User
}

# try a simple YAML-based query first
all_users = parse_yaml_query("from: User").to_sqlalchemy(session, tables).all()
print(all_users)

# same query, but this time in JSON
all_users = parse_json_query("""{"from": "User"}""").to_sqlalchemy(session, tables).all()
print(all_users)

# a slightly more complex query
young_users = parse_yaml_query("""from: User
where:
    $gt:
        date-of-birth: 1988-01-01
""").to_sqlalchemy(session, tables).all()
print(young_users)
```

## Query Language Syntax
As mentioned before, queries can either be supplied in YAML format or
in JSON format to one of the respective parsers.

### `from`
At present, MLAlchemy can only support selecting data from a single
table (multi-table support is planned in future). Here, the `from`
parameter allows you to specify the name of the table from which
to select data.

### `where`
The `where` parameter defines, in hierarchical fashion, the structure
of the logical query to perform. There are 3 kinds of key types in
the JSON/YAML structures, as described in the following table.

| Kind            | Description                                           | Options                                                                    |
| --------------- | ----------------------------------------------------- | -------------------------------------------------------------------------- |
| **Operators**   | Logical (boolean) operators for combining sub-clauses | `$and`, `$or`, `$not`                                                      |
| **Comparators** | Comparative operators for comparing fields to values  | `$eq`, `$gt`, `$gte`, `$lt`, `$lte`, `$like`, `$neq`, `$in`, `$nin`, `$is` |
| **Field Names** | The name of a field in the `from` table               | (Depends on table)                                                         |

### `order-by` (YAML) or `orderBy` (JSON)
Provides the ordering for the resulting query. Must either be a single
field name or a list of field names, with the direction specifier in
front of the field name. For example:

```yaml
# Order by "field2" in ascending order
order-by: field2
```

Another example:

```yaml
# Order by "field2" in *descending* order
order-by: "-field2"
```

A more complex example:

```yaml
# Order first by "field1" in ascending order, then by "field2" in
# descending order
order-by:
    - field1
    - "-field2"
```

### `offset`
Specifies the number of results to skip before providing results. If
not specified, no results are skipped.

### `limit`
Specifies the maximum number of results to return. If not specified,
there will be no limit to the number of returned results.

## Query Examples

### Example 1: Simple Query
The following is an example of a relatively simple query in YAML format:

```yaml
from: SomeTable
where:
    - $gt:
        field1: 5
    - $lt:
        field2: 3
order-by:
    - field1
offset: 2
limit: 10
```

This would translate into the following SQLAlchemy query:

```python
from sqlalchemy.sql.expression import and_

session.query(SomeTable).filter(
    and_(SomeTable.field1 > 5, SomeTable.field2 < 3)
) \
    .order_by(SomeTable.field1) \
    .offset(2) \
    .limit(10)
```

### Example 2: Slightly More Complex Query
The following is an example of a more complex query in YAML format:

```yaml
from: SomeTable
where:
    - $or:
        field1: 5
        field2: something
    - $not:
        $like:
            field3: "else%"
```

This would translate into the following SQLAlchemy query:

```python
from sqlalchemy.sql.expression import and_, or_, not_

session.query(SomeTable) \
    .filter(
        and_(
            or_(
                SomeTable.field1 == 5,
                SomeTable.field2 == "something"
            ),
            not_(
                SomeTable.field3.like("else%")
            )
        )
    )
```

### Example 3: Complex JSON Query
The following is an example of a relatively complex query in
JSON format:

```json
{
    "from": "SomeTable",
    "where": [
        {
            "$or": [
                {"field1": 10},
                {
                    "$gt": {
                        "field2": 5
                    }
                }
            ],
            "$and": [
                {"field3": "somevalue"},
                {"field4": "othervalue"},
                {
                    "$or": {
                        "field5": 5,
                        "field6": 6
                    }
                }
            ]
        }
    ],
    "orderBy": [
        "field1",
        "-field2"
    ],
    "offset": 2,
    "limit": 10
}
```

This query would be translated into the following SQLAlchemy code:

```python
from sqlalchemy.sql.expression import and_, or_, not_

session.query(SomeTable) \
    .filter(
        and_(
            or_(
                SomeTable.field1 == 10,
                SomeTable.field2 > 5
            ),
            and_(
                SomeTable.field3 == "somevalue",
                SomeTable.field4 == "othervalue",
                or_(
                    SomeTable.field5 == 5,
                    SomeTable.field6 == 6
                )
            )
        )
    ) \
    .order_by(SomeTable.field1, SomeTable.field2.desc()) \
    .offset(2) \
    .limit(10)
```

## License
**The MIT License (MIT)**

Copyright (c) 2017 Thane Thomson

Permission is hereby granted, free of charge, to any person obtaining a copy of
this software and associated documentation files (the "Software"), to deal in
the Software without restriction, including without limitation the rights to
use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies
of the Software, and to permit persons to whom the Software is furnished to do
so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
SOFTWARE.

MLAlchemy
=========

Overview
--------

MLAlchemy is a Python-based utility library aimed at allowing relatively
safe conversion from YAML/JSON to SQLAlchemy read-only queries. One use
case here is to allow RESTful web applications (written in Python) to
receive YAML- or JSON-based queries for data, e.g. from a front-end
JavaScript-based application.

The name "MLAlchemy" is an abbreviation for "Markup Language for
SQLAlchemy".

Installation
------------

Installation via PyPI:

.. code:: bash

    > pip install mlalchemy

Query Examples
--------------

To get a feel for what MLAlchemy queries look like, take a look at the
following. **Note**: All field names are converted from ``camelCase`` or
``kebab-case`` to ``snake_case`` prior to query execution.

Example YAML Queries
~~~~~~~~~~~~~~~~~~~~

Fetching all the entries from a table called ``Users``:

.. code:: yaml

    from: Users

Limiting the users to only those with the last name "Michaels":

.. code:: yaml

    from: Users
    where:
      last-name: Michaels

A more complex YAML query:

.. code:: yaml

    from: Users
    where:
      $or:
        last-name: Michaels
        first-name: Michael
      $gt:
        date-of-birth: 1984-01-01

The raw SQL query for the above would look like:

.. code:: sql

    SELECT * FROM users WHERE
      (last_name = "Michaels" OR first_name = "Michael") AND
      (date_of_birth > "1984-01-01")

Example JSON Queries
~~~~~~~~~~~~~~~~~~~~

The same queries as above, but in JSON format. To fetch all entries in
the ``Users`` table:

.. code:: json

    {
        "from": "Users"
    }

Limiting the users to only those with the last name "Michaels":

.. code:: json

    {
        "from": "Users",
        "where": {
            "lastName": "Michaels"
        }
    }

And finally, the more complex query:

.. code:: json

    {
        "from": "Users",
        "where": {
            "$or": {
                "lastName": "Michaels",
                "firstName": "Michael"
            },
            "$gt": {
                "dateOfBirth": "1984-01-01"
            }
        }
    }

Usage
-----

A simple example of how to use MLAlchemy:

.. code:: python

    from sqlalchemy import create_engine, Column, Integer, String, Date
    from sqlalchemy.ext.declarative import declarative_base
    from sqlalchemy.orm import sessionmaker

    from mlalchemy import parse_yaml_query, parse_json_query

    Base = declarative_base()


    class User(Base):
        __tablename__ = "users"

        id = Column(Integer, primary_key=True)
        first_name = Column(String)
        last_name = Column(String)
        date_of_birth = Column(Date)


    # use an in-memory SQLite database for this example
    engine = create_engine("sqlite:///:memory:")
    Base.metadata.create_all(engine)
    Session = sessionmaker(bind=engine)
    session = Session()

    # add a couple of dummy users
    user1 = User(first_name="Michael", last_name="Anderson", date_of_birth=date(1980, 1, 1))
    user2 = User(first_name="James", last_name="Michaels", date_of_birth=date(1976, 10, 23))
    user3 = User(first_name="Andrew", last_name="Michaels", date_of_birth=date(1988, 8, 12))
    session.add_all([user1, user2, user3])
    session.commit()

    # we need a lookup table for MLAlchemy
    tables = {
        "User": User
    }

    # try a simple YAML-based query first
    all_users = parse_yaml_query("from: User").to_sqlalchemy(session, tables).all()
    print(all_users)

    # same query, but this time in JSON
    all_users = parse_json_query("""{"from": "User"}""").to_sqlalchemy(session, tables).all()
    print(all_users)

    # a slightly more complex query
    young_users = parse_yaml_query("""from: User
    where:
        $gt:
            date-of-birth: 1988-01-01
    """).to_sqlalchemy(session, tables).all()
    print(young_users)

Query Language Syntax
---------------------

As mentioned before, queries can either be supplied in YAML format or in
JSON format to one of the respective parsers.

``from``
~~~~~~~~

At present, MLAlchemy can only support selecting data from a single
table (multi-table support is planned in future). Here, the ``from``
parameter allows you to specify the name of the table from which to
select data.

``where``
~~~~~~~~~

The ``where`` parameter defines, in hierarchical fashion, the structure
of the logical query to perform. There are 3 kinds of key types in the
JSON/YAML structures, as described in the following table.

+-----------------+----------------------------+---------------------------------------+
| Kind            | Description                | Options                               |
+=================+============================+=======================================+
| **Operators**   | Logical (boolean)          | ``$and``, ``$or``, ``$not``           |
|                 | operators for combining    |                                       |
|                 | sub-clauses                |                                       |
+-----------------+----------------------------+---------------------------------------+
| **Comparators** | Comparative operators for  | ``$eq``, ``$gt``, ``$gte``, ``$lt``,  |
|                 | comparing fields to values | ``$lte``, ``$like``, ``$neq``,        |
|                 |                            | ``$in``, ``$nin``, ``$is``            |
+-----------------+----------------------------+---------------------------------------+
| **Field Names** | The name of a field in the | (Depends on table)                    |
|                 | ``from`` table             |                                       |
+-----------------+----------------------------+---------------------------------------+

``order-by`` (YAML) or ``orderBy`` (JSON)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Provides the ordering for the resulting query. Must either be a single
field name or a list of field names, with the direction specifier in
front of the field name. For example:

.. code:: yaml

    # Order by "field2" in ascending order
    order-by: field2

Another example:

.. code:: yaml

    # Order by "field2" in *descending* order
    order-by: "-field2"

A more complex example:

.. code:: yaml

    # Order first by "field1" in ascending order, then by "field2" in
    # descending order
    order-by:
        - field1
        - "-field2"

``offset``
~~~~~~~~~~

Specifies the number of results to skip before providing results. If not
specified, no results are skipped.

``limit``
~~~~~~~~~

Specifies the maximum number of results to return. If not specified,
there will be no limit to the number of returned results.

Query Examples
--------------

Example 1: Simple Query
~~~~~~~~~~~~~~~~~~~~~~~

The following is an example of a relatively simple query in YAML format:

.. code:: yaml

    from: SomeTable
    where:
        - $gt:
            field1: 5
        - $lt:
            field2: 3
    order-by:
        - field1
    offset: 2
    limit: 10

This would translate into the following SQLAlchemy query:

.. code:: python

    from sqlalchemy.sql.expression import and_

    session.query(SomeTable).filter(
        and_(SomeTable.field1 > 5, SomeTable.field2 < 3)
    ) \
        .order_by(SomeTable.field1) \
        .offset(2) \
        .limit(10)

Example 2: Slightly More Complex Query
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The following is an example of a more complex query in YAML format:

.. code:: yaml

    from: SomeTable
    where:
        - $or:
            field1: 5
            field2: something
        - $not:
            $like:
                field3: "else%"

This would translate into the following SQLAlchemy query:

.. code:: python

    from sqlalchemy.sql.expression import and_, or_, not_

    session.query(SomeTable) \
        .filter(
            and_(
                or_(
                    SomeTable.field1 == 5,
                    SomeTable.field2 == "something"
                ),
                not_(
                    SomeTable.field3.like("else%")
                )
            )
        )

Example 3: Complex JSON Query
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The following is an example of a relatively complex query in JSON
format:

.. code:: json

    {
        "from": "SomeTable",
        "where": [
            {
                "$or": [
                    {"field1": 10},
                    {
                        "$gt": {
                            "field2": 5
                        }
                    }
                ],
                "$and": [
                    {"field3": "somevalue"},
                    {"field4": "othervalue"},
                    {
                        "$or": {
                            "field5": 5,
                            "field6": 6
                        }
                    }
                ]
            }
        ],
        "orderBy": [
            "field1",
            "-field2"
        ],
        "offset": 2,
        "limit": 10
    }

This query would be translated into the following SQLAlchemy code:

.. code:: python

    from sqlalchemy.sql.expression import and_, or_, not_

    session.query(SomeTable) \
        .filter(
            and_(
                or_(
                    SomeTable.field1 == 10,
                    SomeTable.field2 > 5
                ),
                and_(
                    SomeTable.field3 == "somevalue",
                    SomeTable.field4 == "othervalue",
                    or_(
                        SomeTable.field5 == 5,
                        SomeTable.field6 == 6
                    )
                )
            )
        ) \
        .order_by(SomeTable.field1, SomeTable.field2.desc()) \
        .offset(2) \
        .limit(10)

License
-------

**The MIT License (MIT)**

Copyright (c) 2017 Thane Thomson

Permission is hereby granted, free of charge, to any person obtaining a
copy of this software and associated documentation files (the
"Software"), to deal in the Software without restriction, including
without limitation the rights to use, copy, modify, merge, publish,
distribute, sublicense, and/or sell copies of the Software, and to
permit persons to whom the Software is furnished to do so, subject to
the following conditions:

The above copyright notice and this permission notice shall be included
in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS
OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.
IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY
CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT,
TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE
SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
# -*- coding:utf-8 -*-

from mlalchemy.constants import *
from mlalchemy.errors import *
from mlalchemy.structures import *
from mlalchemy.parser import *


__version__ = "0.2.1"
# -*- coding: utf-8 -*-

from __future__ import unicode_literals

__all__ = [
    "OP_AND",
    "OP_OR",
    "OP_NOT",
    "OPERATORS",
    "COMP_EQ",
    "COMP_GT",
    "COMP_GTE",
    "COMP_LT",
    "COMP_LTE",
    "COMP_NEQ",
    "COMP_LIKE",
    "COMP_IN",
    "COMP_NIN",
    "COMP_IS",
    "COMPARATORS",
    "ORDER_ASC",
    "ORDER_DESC",
    "QUERY_ORDERS"
]


# Supported logical SQL query operators
OP_AND = "$and"
OP_OR = "$or"
OP_NOT = "$not"
OPERATORS = {OP_AND, OP_OR, OP_NOT}

# Supported SQL query comparators
COMP_EQ = "$eq"
COMP_GT = "$gt"
COMP_GTE = "$gte"
COMP_LT = "$lt"
COMP_LTE = "$lte"
COMP_NEQ = "$neq"
COMP_LIKE = "$like"
COMP_IN = "$in"
COMP_NIN = "$nin"
COMP_IS = "$is"
COMPARATORS = {COMP_EQ, COMP_GT, COMP_GTE, COMP_LT, COMP_LTE, COMP_NEQ, COMP_LIKE, COMP_IN, COMP_NIN,
               COMP_IS}

ORDER_ASC = "asc"
ORDER_DESC = "desc"
QUERY_ORDERS = {ORDER_ASC, ORDER_DESC}
# -*- coding: utf-8 -*-

from __future__ import unicode_literals

__all__ = [
    "MLAlchemyError",
    "InvalidOperatorError",
    "InvalidComparatorError",
    "QuerySyntaxError",
    "InvalidTableError",
    "InvalidFieldError"
]


class MLAlchemyError(Exception):
    pass


class InvalidOperatorError(MLAlchemyError):
    pass


class InvalidComparatorError(MLAlchemyError):
    pass


class QuerySyntaxError(MLAlchemyError):
    pass


class InvalidTableError(MLAlchemyError):
    pass


class InvalidFieldError(MLAlchemyError):
    pass
# -*- coding: utf-8 -*-

from __future__ import unicode_literals

from future.utils import iteritems

import yaml
import json

from mlalchemy.errors import *
from mlalchemy.structures import *
from mlalchemy.constants import *
from mlalchemy.utils import *

import logging
logger = logging.getLogger(__name__)

__all__ = [
    "parse_yaml_query",
    "parse_json_query",
    "parse_query",
    "parse_query_fragment"
]


def parse_yaml_query(yaml_content):
    """Parses the given YAML string to attempt to extract a query.

    Args:
        yaml_content: A string containing YAML content.

    Returns:
        On success, the processed MLQuery object.
    """
    logger.debug("Attempting to parse YAML content:\n%s" % yaml_content)
    return parse_query(yaml.safe_load(yaml_content))


def parse_json_query(json_content):
    """Parses the given JSON string to attempt to extract a query.

    Args:
        json_content: A string containing JSON content.

    Returns:
        On success, the processed MLQuery object.
    """
    logger.debug("Attempting to parse JSON content:\n%s" % json_content)
    return parse_query(json.loads(json_content))


def parse_query(qd):
    """Parses the given query dictionary to produce an MLQuery object.

    Args:
        qd: A Python dictionary (pre-parsed from JSON/YAML) from which to extract the query.

    Returns:
        On success, the processed MLQuery object.
    """
    if not isinstance(qd, dict):
        raise TypeError("Argument for query parsing must be a Python dictionary")
    if 'from' not in qd:
        raise QuerySyntaxError("Missing \"from\" argument in query")

    logger.debug("Attempting to parse query dictionary:\n%s" % json_dumps(qd, indent=2))

    qf = parse_query_fragment(qd['where']).simplify() if 'where' in qd else None
    if isinstance(qf, MLClause):
        qf = MLQueryFragment(OP_AND, clauses=[qf])

    return MLQuery(
        qd['from'],
        query_fragment=qf,
        order_by=qd.get('orderBy', qd.get('order-by', qd.get('order_by', None))),
        offset=qd.get('offset', None),
        limit=qd.get('limit', None)
    )


def parse_query_fragment(q, op=OP_AND, comp=COMP_EQ):
    """Parses the given query object for its query fragment only."""
    if not isinstance(q, list) and not isinstance(q, dict):
        raise TypeError("\"Where\" clause in query fragment must either be a list or a dictionary")

    # ensure we're always dealing with a list
    if not isinstance(q, list):
        q = [q]

    clauses = []
    sub_fragments = []

    for sub_q in q:
        if not isinstance(sub_q, dict):
            raise TypeError("Sub-fragment must be a dictionary: %s" % sub_q)

        for k, v in iteritems(sub_q):
            # if v is a sub-fragment with a specific operator
            if k in OPERATORS:
                s = parse_query_fragment(v, op=k, comp=comp).simplify()
            elif k in COMPARATORS:
                # it's a sub-fragment, but its comparator is explicitly specified
                s = parse_query_fragment(v, op=op, comp=k).simplify()
            else:
                # it must be a clause
                s = MLClause(k, comp, v)

            if isinstance(s, MLQueryFragment):
                sub_fragments.append(s)
            elif isinstance(s, MLClause):
                clauses.append(s)

    return MLQueryFragment(op, clauses=clauses, sub_fragments=sub_fragments)
# -*- coding: utf-8 -*-

from __future__ import unicode_literals
from past.builtins import basestring

from sqlalchemy.sql.expression import and_, or_, not_
from sqlalchemy.orm.attributes import QueryableAttribute

from mlalchemy.constants import *
from mlalchemy.errors import *
from mlalchemy.utils import *

import logging
logger = logging.getLogger(__name__)

__all__ = [
    "MLQuery",
    "MLQueryFragment",
    "MLClause"
]


class MLQuery(object):
    """Broad data structure used to represent a selection query in its entirety."""

    def __init__(self, table, query_fragment=None, order_by=None, offset=None, limit=None):
        """Constructor.

        Args:
            table: The name of the table being queried.
            query_fragment: A single instance of MLQueryFragment that represents the top of the hierarchy of
                query fragments making up this query. This can be None (i.e. no query criteria; all entries of
                the specified table will be selected).
            order_by: A string or list indicating the name of the field by which to order results, including
                the direction of ordering. For example, if a field's name is "title", specifying order_by as
                "title" will sort by "title" in the *ascending* direction. Specifying "-title" will sort in the
                descending direction. This can also be an ordered list of field names/directions.
            offset: The number of entries to skip. Set to None if no offset is required.
            limit: The maximum number of entries to return. Set to None to specify no limit.
        """
        if not isinstance(table, basestring):
            raise TypeError("The table name supplied to an MLQuery object must be a string")
        if query_fragment is not None and not isinstance(query_fragment, MLQueryFragment):
            raise TypeError("Primary query fragment for MLQuery must be of type MLQueryFragment")
        if order_by is not None and not isinstance(order_by, basestring) and not isinstance(order_by, list):
            raise TypeError("Query ordering parameter must be a string or a list")

        self.unique_field_names = set()
        self.table = table
        self.query_fragment = query_fragment

        if self.query_fragment is not None:
            self.unique_field_names = self.unique_field_names.union(self.query_fragment.unique_field_names)

        self.order_by = []
        if order_by is not None:
            # make sure our order_by field is a list
            if not isinstance(order_by, list):
                order_by = [order_by]

            for ob in order_by:
                field_name = ob.strip("-")
                # make sure it's in snake_case
                if is_camelcase_string(field_name):
                    field_name = camelcase_to_snakecase(field_name)
                elif is_kebabcase_string(field_name):
                    field_name = kebabcase_to_snakecase(field_name)
                self.order_by.append({field_name: ORDER_DESC if ob[0] == "-" else ORDER_ASC})
                self.unique_field_names.add(field_name)

        self.offset = offset
        self.limit = limit

    def as_dict(self):
        return {
            "table": self.table,
            "query_fragment": self.query_fragment.as_dict() if self.query_fragment is not None else None,
            "order_by": self.order_by,
            "offset": self.offset,
            "limit": self.limit
        }

    def unpack(self):
        return self.table, self.query_fragment, self.order_by, self.offset, self.limit

    def __repr__(self):
        return json_dumps(self.as_dict(), indent=2)

    def to_sqlalchemy(self, session, tables):
        if not isinstance(tables, dict):
            raise TypeError("Supplied tables structure for MLQuery-to-SQLAlchemy query conversion must be a dictionary")
        if self.table not in tables:
            raise InvalidTableError("Table does not exist in tables dictionary: %s" % self.table)

        table = tables[self.table]
        query = session.query(table)

        logger.debug("Attempting to build SQLAlchemy query for table \"%s\":\n%s" % (self.table, self))

        if self.query_fragment is not None:
            query = query.filter(self.query_fragment.to_sqlalchemy(table))

        if self.order_by is not None:
            criteria = []
            for order_by in self.order_by:
                field, direction = [i for i in order_by.items()][0]
                criterion = getattr(table, field)
                if not isinstance(criterion, QueryableAttribute):
                    raise InvalidFieldError("Invalid field for specified table: %s" % field)

                if direction == ORDER_ASC:
                    criterion = criterion.asc()
                elif direction == ORDER_DESC:
                    criterion = criterion.desc()
                criteria.append(criterion)
            query = query.order_by(*criteria)

        if self.offset is not None:
            query = query.offset(self.offset)

        if self.limit is not None:
            query = query.limit(self.limit)

        return query


class MLQueryFragment(object):
    """Recursive object to allow for relatively complex data selection queries."""

    def __init__(self, op, clauses=None, sub_fragments=None):
        """Constructor.

        Args:
            op: The operator to join clauses and sub-queries.
            clauses: A list of clauses (MLClause objects) for the query. Set to None if there are none.
            sub_fragments: A list of sub-queries (MLQueryFragment objects) within this query. Set to None if there are
                none.
        """
        if op not in OPERATORS:
            raise InvalidOperatorError("Invalid operator: %s" % op)

        if clauses is not None and not isinstance(clauses, list):
            raise TypeError("MLQueryFragment clauses must either be None or a list")
        if sub_fragments is not None and not isinstance(sub_fragments, list):
            raise TypeError("MLQueryFragment sub-fragments must either be None or a list")

        # ensure an empty list if no clauses or sub-queries
        if clauses is None:
            clauses = []
        if sub_fragments is None:
            sub_fragments = []

        self.unique_field_names = set()

        for clause in clauses:
            if not isinstance(clause, MLClause):
                raise TypeError("All clauses within an MLQueryFragment must be of type MLClause")
            self.unique_field_names.add(clause.field)

        for sub_frag in sub_fragments:
            if not isinstance(sub_frag, MLQueryFragment):
                raise TypeError("All sub-fragments within an MLQueryFragment must be of type MLQueryFragment")

            self.unique_field_names = self.unique_field_names.union(sub_frag.unique_field_names)

        if op == OP_NOT and (len(clauses) + len(sub_fragments)) > 1:
            raise QuerySyntaxError("NOT operations can only contain a single clause or sub-query fragment")

        self.op = op
        self.clauses = clauses
        self.sub_fragments = sub_fragments

    def unpack(self):
        return self.op, self.clauses, self.sub_fragments

    def as_dict(self):
        return {
            "op": self.op,
            "clauses": [clause.as_dict() for clause in self.clauses],
            "sub_fragments": [sub_fragment.as_dict() for sub_fragment in self.sub_fragments]
        }

    def __repr__(self):
        return json_dumps(self.as_dict(), indent=2)

    def simplify(self):
        op = self.op
        clauses = [clause for clause in self.clauses]
        sub_fragments = []

        for sub_fragment in self.sub_fragments:
            s = sub_fragment.simplify()
            if isinstance(s, MLClause):
                clauses.append(s)
            elif isinstance(s, MLQueryFragment):
                sub_fragments.append(s)

        # if this query fragment is only made up of a single clause
        if len(clauses) == 1 and len(sub_fragments) == 0 and op == OP_AND:
            return clauses[0]

        # if this query fragment is just a single sub-fragment, collapse its properties into the simplified
        # fragment we're currently generating
        if len(clauses) == 0 and len(sub_fragments) == 1:
            op, clauses, sub_fragments = sub_fragments[0].unpack()

        return MLQueryFragment(op, clauses=clauses, sub_fragments=sub_fragments)

    def to_sqlalchemy(self, table):
        filter_criteria = [clause.to_sqlalchemy(table) for clause in self.clauses]
        filter_criteria.extend([sub_frag.to_sqlalchemy(table) for sub_frag in self.sub_fragments])

        if self.op == OP_OR:
            return or_(*filter_criteria)
        elif self.op == OP_NOT:
            return not_(*filter_criteria)

        return and_(*filter_criteria)


class MLClause(object):
    """A single clause in an MLQuery object."""

    def __init__(self, field, comp, value):
        """Constructor.

        Args:
            field: The name of the field relevant to this clause.
            comp: The comparator when performing the comparison.
            value: The value to which this field's value is being compared.
        """
        if comp not in COMPARATORS:
            raise InvalidComparatorError("Invalid comparator: %s" % comp)

        if not isinstance(field, basestring):
            raise TypeError("Clause field names must be strings")

        # ensure field name is in snake_case
        if is_kebabcase_string(field):
            field = kebabcase_to_snakecase(field)
        elif is_camelcase_string(field):
            field = camelcase_to_snakecase(field)

        self.field = field
        self.comp = comp
        self.value = value

    def as_dict(self):
        return {
            "field": self.field,
            "comp": self.comp,
            "value": self.value
        }

    def unpack(self):
        return self.field, self.comp, self.value

    def __repr__(self):
        return json_dumps(self.as_dict(), indent=2)

    def to_sqlalchemy(self, table):
        col = getattr(table, self.field)
        # make sure it's the right kind of field
        if not isinstance(col, QueryableAttribute):
            raise InvalidFieldError("Invalid field for specified table: %s" % self.field)

        if self.comp == COMP_EQ:
            return col == self.value
        elif self.comp == COMP_GT:
            return col > self.value
        elif self.comp == COMP_GTE:
            return col >= self.value
        elif self.comp == COMP_LT:
            return col < self.value
        elif self.comp == COMP_LTE:
            return col <= self.value
        elif self.comp == COMP_NEQ:
            return col != self.value
        elif self.comp == COMP_LIKE:
            return col.like(self.value)
        elif self.comp == COMP_IN:
            return col.in_(self.value)
        elif self.comp == COMP_NIN:
            return ~col.in_(self.value)
        elif self.comp == COMP_IS:
            return col.is_(self.value)

        # default to equals
        return col == self.value
# -*- coding: utf-8 -*-

from __future__ import unicode_literals

import unittest

from mlalchemy.structures import *

__all__ = [
    "MLAlchemyTestCase"
]


class MLAlchemyTestCase(unittest.TestCase):
    """Helper class to speed up the writing of relatively complex unit tests."""

    def assertQueryEquals(self, src, q):
        self.assertIsInstance(q, MLQuery)
        self.assertEqual(src.table, q.table)
        self.assertQueryFragmentEquals(src.query_fragment, q.query_fragment)
        self.assertEqual(src.order_by, q.order_by)
        self.assertEqual(src.offset, q.offset)
        self.assertEqual(src.limit, q.limit)

    def assertQueryFragmentEquals(self, src, qf):
        self.assertIsInstance(qf, MLQueryFragment)
        self.assertEqual(src.op, qf.op)
        self.assertEqual(len(src.clauses), len(qf.clauses))
        self.assertEqual(len(src.sub_fragments), len(qf.sub_fragments))

        for i in range(len(src.clauses)):
            self.assertClauseEquals(
                src.clauses[i],
                qf.clauses[i]
            )

        for i in range(len(src.sub_fragments)):
            self.assertQueryFragmentEquals(
                src.sub_fragments[i],
                qf.sub_fragments[i]
            )

    def assertClauseEquals(self, src, clause):
        self.assertIsInstance(clause, MLClause)
        self.assertEqual(src.field, clause.field)
        self.assertEqual(src.comp, clause.comp)
        self.assertEqual(src.value, clause.value)
# -*- coding: utf-8 -*-

from __future__ import unicode_literals

from datetime import date, datetime
import json
import re

__all__ = [
    "is_camelcase_string",
    "is_kebabcase_string",
    "camelcase_to_snakecase",
    "kebabcase_to_snakecase",
    "json_date_serializer",
    "json_dumps"
]

KEBABCASE_DETECT_RE = re.compile(r"^(([a-z][a-z0-9]+)\-)*([a-z][a-z0-9]+)$")
KEBABCASE_REPLACE_RE = re.compile(r"([a-z]+)\-")

CAMELCASE_DETECT_RE = re.compile(r"^([a-zA-Z][a-z0-9]*)([A-Z][a-z0-9]*)*$")
CAMELCASE_FIRST_CAP_RE = re.compile(r"(.)([A-Z][a-z]+)")
CAMELCASE_ALL_CAP_RE = re.compile(r"([a-z0-9])([A-Z])")


def is_camelcase_string(s):
    """Checks whether or not the given string is supplied in camelCase."""
    return CAMELCASE_DETECT_RE.match(s) is not None


def is_kebabcase_string(s):
    """Checks whether the given string is supplied in skewer-case."""
    return KEBABCASE_DETECT_RE.match(s) is not None


def camelcase_to_snakecase(s):
    s1 = CAMELCASE_FIRST_CAP_RE.sub(r"\1_\2", s)
    return CAMELCASE_ALL_CAP_RE.sub(r"\1_\2", s1).lower()


def kebabcase_to_snakecase(s):
    return KEBABCASE_REPLACE_RE.sub(r"\1_", s)


def json_date_serializer(obj):
    if isinstance(obj, (date, datetime)):
        return obj.isoformat()
    raise TypeError("Type not serializable")


def json_dumps(obj, indent=None):
    return json.dumps(obj, indent=indent, default=json_date_serializer)
sqlalchemy>=1.1
future>=0.16.0
PyYAML>=3.11
#!/bin/bash
rm dist/*
python setup.py sdist bdist_wheel
rm dist/*.egg
twine upload dist/*
#!/bin/bash
pandoc --from=markdown --to=rst --output=README.rst README.md

[metadata]
description-file = README.rst

[bdist_wheel]
universal=1

#!/usr/bin/env python
# -*- coding:utf-8 -*-

import re
from io import open
import os.path
from setuptools import setup


def read_file(filename):
    full_path = os.path.join(os.path.dirname(os.path.realpath(__file__)), filename)
    with open(full_path, "rt", encoding="utf-8") as f:
        lines = f.readlines()
    return lines


def get_version():
    pattern = re.compile(r"__version__ = \"(?P<version>[0-9.a-zA-Z-]+)\"")
    for line in read_file(os.path.join("mlalchemy", "__init__.py")):
        m = pattern.match(line)
        if m is not None:
            return m.group('version')
    raise ValueError("Cannot extract version number for MLAlchemy")


setup(
    name="mlalchemy",
    version=get_version(),
    description="Library for converting YAML/JSON to SQLAlchemy SELECT queries",
    long_description="".join(read_file("README.rst")),
    author="Thane Thomson",
    author_email="connect@thanethomson.com",
    url="https://github.com/thanethomson/MLAlchemy",
    install_requires=[r.strip() for r in read_file("requirements.txt") if len(r.strip()) > 0],
    license='MIT',
    packages=["mlalchemy"],
    include_package_data=True,
    classifiers=[
        "Development Status :: 4 - Beta",
        "Intended Audience :: Developers",
        "License :: OSI Approved :: MIT License",
        "Natural Language :: English",
        "Operating System :: POSIX",
        "Operating System :: MacOS",
        "Programming Language :: Python :: 2.7",
        "Programming Language :: Python :: 3.5",
        "Programming Language :: Python :: 3.6",
        "Topic :: Database",
        "Topic :: Utilities",
        "Topic :: Software Development :: Libraries"
    ]
)
# -*- coding: utf-8 -*-
# -*- coding: utf-8 -*-

from __future__ import unicode_literals

import unittest

from mlalchemy import *
from mlalchemy.testing import MLAlchemyTestCase


class TestBasicMLAlchemyParsing(MLAlchemyTestCase):

    def test_parse_basic_query1(self):
        query = parse_query({
            "from": "SomeTable",
            "where": {
                "field": "value"
            }
        })
        self.assertQueryEquals(
            MLQuery(
                "SomeTable",
                query_fragment=MLQueryFragment(
                    OP_AND,
                    clauses=[
                        MLClause(
                            "field",
                            COMP_EQ,
                            "value"
                        )
                    ]
                )
            ),
            query
        )

    def test_parse_basic_query2(self):
        query = parse_query({
            "from": "SomeTable",
            "where": [
                {"field1": "value"},
                {"field2": 1}
            ]
        })
        self.assertQueryEquals(
            MLQuery(
                "SomeTable",
                query_fragment=MLQueryFragment(
                    OP_AND,
                    clauses=[
                        MLClause(
                            "field1",
                            COMP_EQ,
                            "value"
                        ),
                        MLClause(
                            "field2",
                            COMP_EQ,
                            1
                        )
                    ]
                )
            ),
            query
        )

    def test_parse_basic_query3(self):
        query = parse_query({
            "from": "SomeTable",
            "where": {
                "$or": [
                    {"field1": "value"},
                    {"field2": 1}
                ]
            }
        })
        self.assertQueryEquals(
            MLQuery(
                "SomeTable",
                query_fragment=MLQueryFragment(
                    OP_OR,
                    clauses=[
                        MLClause(
                            "field1",
                            COMP_EQ,
                            "value"
                        ),
                        MLClause(
                            "field2",
                            COMP_EQ,
                            1
                        )
                    ]
                )
            ),
            query
        )

    def test_parse_basic_query4(self):
        query = parse_query({
            "from": "SomeTable",
            "where": [
                {
                    "$not": {
                        "field1": 1
                    }
                }
            ],
            "orderBy": "field1",
            "offset": 10,
            "limit": 50
        })
        self.assertQueryEquals(
            MLQuery(
                "SomeTable",
                query_fragment=MLQueryFragment(
                    OP_NOT,
                    clauses=[
                        MLClause("field1", COMP_EQ, 1)
                    ]
                ),
                order_by=["field1"],
                offset=10,
                limit=50
            ),
            query
        )

    def test_parse_basic_query5(self):
        query = parse_query({
            "from": "SomeTable",
            "where": {
                "$gt": {
                    "field1": 5
                }
            }
        })
        self.assertQueryEquals(
            MLQuery(
                "SomeTable",
                query_fragment=MLQueryFragment(
                    OP_AND,
                    clauses=[
                        MLClause("field1", COMP_GT, 5)
                    ]
                )
            ),
            query
        )

    def test_parse_basic_query6(self):
        query = parse_query({
            "from": "SomeTable",
            "where": [
                {
                    "$gt": {
                        "field1": 5
                    }
                },
                {
                    "$lt": {
                        "field2": 10
                    }
                }
            ]
        })
        self.assertQueryEquals(
            MLQuery(
                "SomeTable",
                query_fragment=MLQueryFragment(
                    OP_AND,
                    clauses=[
                        MLClause("field1", COMP_GT, 5),
                        MLClause("field2", COMP_LT, 10)
                    ]
                )
            ),
            query
        )

    def test_parse_basic_query7(self):
        query = parse_query({
            "from": "SomeTable",
            "where": {"field1": 1},
            "orderBy": "-field1"
        })
        self.assertQueryEquals(
            MLQuery(
                "SomeTable",
                query_fragment=MLQueryFragment(
                    OP_AND,
                    clauses=[
                        MLClause("field1", COMP_EQ, 1)
                    ]
                ),
                order_by=["-field1"]
            ),
            query
        )

    def test_parse_basic_query8(self):
        query = parse_query({
            "from": "SomeTable",
            "where": {"field1": 1},
            "orderBy": ["-field1", "field2", "-field3"]
        })
        self.assertQueryEquals(
            MLQuery(
                "SomeTable",
                query_fragment=MLQueryFragment(
                    OP_AND,
                    clauses=[
                        MLClause("field1", COMP_EQ, 1)
                    ]
                ),
                order_by=["-field1", "field2", "-field3"]
            ),
            query
        )

    def test_parse_invalid_basic_queries(self):
        with self.assertRaises(TypeError):
            parse_query([])
        with self.assertRaises(QuerySyntaxError):
            parse_query(dict())

    def test_parse_invalid_basic_not_op(self):
        with self.assertRaises(QuerySyntaxError):
            parse_query({
                "from": "SomeTable",
                "where": {
                    "$not": [
                        {"field1": 1},
                        {"field2": 2}
                    ]
                }
            })

    def test_parse_invalid_field_name(self):
        with self.assertRaises(TypeError):
            parse_query({
                "from": "SomeTable",
                "where": {1: 1}
            })

    def test_parse_invalid_sub_fragment(self):
        with self.assertRaises(TypeError):
            parse_query({
                "from": "SomeTable",
                "where": 1
            })


if __name__ == "__main__":
    unittest.main()
# -*- coding: utf-8 -*-

from __future__ import unicode_literals

import unittest

from mlalchemy import *
from mlalchemy.testing import MLAlchemyTestCase


class TestComplexMLAlchemyParsing(MLAlchemyTestCase):

    def test_parse_complex_query1(self):
        query = parse_query({
            "from": "SomeTable",
            "where": [
                {
                    "$or": [
                        {"field1": 1},
                        {"field2": "something"}
                    ]
                },
                {
                    "$not": {"field3": "else"}
                },
                {
                    "$and": [
                        {
                            "$gt": {"field1": 5}
                        },
                        {
                            "$like": {"field3": "hello%"}
                        }
                    ]
                }
            ]
        })
        self.assertQueryEquals(
            MLQuery(
                table="SomeTable",
                query_fragment=MLQueryFragment(
                    OP_AND,
                    sub_fragments=[
                        MLQueryFragment(
                            OP_OR,
                            clauses=[
                                MLClause("field1", COMP_EQ, 1),
                                MLClause("field2", COMP_EQ, "something")
                            ]
                        ),
                        MLQueryFragment(
                            OP_NOT,
                            clauses=[MLClause("field3", COMP_EQ, "else")]
                        ),
                        MLQueryFragment(
                            OP_AND,
                            clauses=[
                                MLClause("field1", COMP_GT, 5),
                                MLClause("field3", COMP_LIKE, "hello%")
                            ]
                        )
                    ]
                )
            ),
            query
        )


if __name__ == "__main__":
    unittest.main()
# -*- coding: utf-8 -*-

from __future__ import unicode_literals
from future.utils import iteritems

import os
import unittest
import logging

from sqlalchemy import create_engine, Column, Integer, String, Date
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

from datetime import date

from mlalchemy import parse_yaml_query, parse_json_query
from mlalchemy.constants import *

Base = declarative_base()


class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True)
    first_name = Column(String)
    last_name = Column(String)
    date_of_birth = Column(Date)
    children = Column(Integer)


YAML_QUERY_ALL_USERS = "from: User"
JSON_QUERY_ALL_USERS = """{"from": "User"}"""
YAML_QUERY_YOUNG_USERS = """from: User
where:
    $gt:
        date-of-birth: 1988-01-01
"""
JSON_QUERY_LASTNAME_MICHAEL = """{
    "from": "User",
    "where": {
        "$like": {
            "lastName": "Mich%"
        }
    }
}"""
YAML_LIMIT_QUERY = """from: User
limit: 2
"""

YAML_ORDERED_QUERY = """from: User
order-by: "-date-of-birth"
"""

YAML_COMPLEX_ORDERED_QUERY = """from: User
order-by:
    - last-name
    - "-date-of-birth"
"""

YAML_COMPARATOR_QUERIES = {
    COMP_EQ: ("from: User\n"
              "where:\n"
              "  first-name: Michael", {1,}),
    COMP_GT: ("from: User\n"
              "where:\n"
              "  $gt:\n"
              "    children: 2", {3,}),
    COMP_GTE: ("from: User\n"
               "where:\n"
               "  $gte:\n"
               "    children: 2", {2, 3, 4}),
    COMP_LT: ("from: User\n"
              "where:\n"
              "  $lt:\n"
              "    children: 2", {1,}),
    COMP_LTE: ("from: User\n"
               "where:\n"
               "  $lte:\n"
               "    children: 2", {1, 2, 4}),
    COMP_NEQ: ("from: User\n"
               "where:\n"
               "  $neq:\n"
               "    last-name: Michaels", {1,}),
    COMP_LIKE: ("from: User\n"
                "where:\n"
                "  $like:\n"
                "    first-name: Mich%", {1,}),
    COMP_IN: ("from: User\n"
              "where:\n"
              "  $in:\n"
              "    last-name:\n"
              "      - Anderson\n"
              "      - Michaels", {1, 2, 3}),
    COMP_NIN: ("from: User\n"
               "where:\n"
               "  $nin:\n"
               "    children:\n"
               "      - 2\n"
               "      - 3", {1,}),
    COMP_IS: ("from: User\n"
              "where:\n"
              "  $is:\n"
              "    last-name: null", {4,})
}


DEBUG_LOGGING = (os.environ.get("DEBUG", False) == "True")


class TestSqlAlchemyQuerying(unittest.TestCase):

    engine = None
    session = None
    tables = None
    data = None

    def setUp(self):
        self.engine = create_engine("sqlite:///:memory:", echo=DEBUG_LOGGING)
        Base.metadata.create_all(self.engine)
        Session = sessionmaker(bind=self.engine)
        self.session = Session()
        # add some initial data
        user1 = User(first_name="Michael", last_name="Anderson", date_of_birth=date(1980, 1, 1), children=0)
        user2 = User(first_name="James", last_name="Michaels", date_of_birth=date(1976, 10, 23), children=2)
        user3 = User(first_name="Andrew", last_name="Michaels", date_of_birth=date(1988, 8, 12), children=3)
        self.session.add_all([user1, user2, user3])
        self.session.commit()

        self.tables = {
            "User": User
        }
        self.data = {
            "User": {
                user1.id: user1,
                user2.id: user2,
                user3.id: user3
            }
        }
        if DEBUG_LOGGING:
            logging.basicConfig(
                level=logging.DEBUG,
                format='%(asctime)s\t%(name)s\t%(levelname)s\t%(message)s',
            )

    def tearDown(self):
        self.session.close()

    def test_all_comparators(self):
        user4 = User(first_name="Gary", last_name=None, date_of_birth=date(1985, 2, 3), children=2)
        self.session.add(user4)
        self.session.commit()
        for comp, crit in iteritems(YAML_COMPARATOR_QUERIES):
            qs, expected_ids = crit
            results = parse_yaml_query(qs).to_sqlalchemy(self.session, self.tables).all()
            seen_ids = set([result.id for result in results])
            self.assertEqual(expected_ids, seen_ids, "Failed with comparator: %s (expected %s, got %s)" % (
                comp, expected_ids, seen_ids
            ))

    def test_basic_querying(self):
        self.assertAllUsers(parse_yaml_query(YAML_QUERY_ALL_USERS))
        self.assertAllUsers(parse_json_query(JSON_QUERY_ALL_USERS))
        self.assertYoungUsers(parse_yaml_query(YAML_QUERY_YOUNG_USERS))
        self.assertMichaelsUsers(parse_json_query(JSON_QUERY_LASTNAME_MICHAEL))
        self.assertLimits(parse_yaml_query(YAML_LIMIT_QUERY))
        self.assertOrderedSimple(parse_yaml_query(YAML_ORDERED_QUERY))
        self.assertOrderedComplex(parse_yaml_query(YAML_COMPLEX_ORDERED_QUERY))

    def assertAllUsers(self, mlquery):
        seen_users = self.query_seen_users(mlquery)
        self.assertEqual(3, len(seen_users))
        for user_id, user in iteritems(self.data["User"]):
            self.assertIn(user_id, seen_users)

    def assertYoungUsers(self, mlquery):
        seen_users = self.query_seen_users(mlquery)
        self.assertEqual(1, len(seen_users))
        # ID 3
        self.assertIn(3, seen_users)

    def assertMichaelsUsers(self, mlquery):
        seen_users = self.query_seen_users(mlquery)
        self.assertEqual(2, len(seen_users))
        self.assertIn(2, seen_users)
        self.assertIn(3, seen_users)

    def assertLimits(self, mlquery):
        seen_users = self.query_seen_users(mlquery)
        self.assertEqual(2, len(seen_users))
        self.assertIn(1, seen_users)
        self.assertIn(2, seen_users)

    def assertOrderedSimple(self, mlquery):
        results = mlquery.to_sqlalchemy(self.session, self.tables).all()
        self.assertEqual(3, results[0].id)
        self.assertEqual(1, results[1].id)
        self.assertEqual(2, results[2].id)

    def assertOrderedComplex(self, mlquery):
        results = mlquery.to_sqlalchemy(self.session, self.tables).all()
        self.assertEqual(1, results[0].id)
        self.assertEqual(3, results[1].id)
        self.assertEqual(2, results[2].id)

    def query_seen_users(self, mlquery):
        results = mlquery.to_sqlalchemy(self.session, self.tables).all()
        seen_users = set()
        for result in results:
            seen_users.add(result.id)
        return seen_users


if __name__ == "__main__":
    unittest.main()
# -*- coding: utf-8 -*-

from __future__ import unicode_literals

import unittest
import yaml

from mlalchemy import *
from mlalchemy.testing import MLAlchemyTestCase


class TestYamlSecurity(MLAlchemyTestCase):

    def test_basic_yaml_security(self):
        with self.assertRaises(yaml.constructor.ConstructorError):
            parse_yaml_query('!!python/object/apply:os.system ["echo Hello"]')


if __name__ == "__main__":
    unittest.main()