This is a pre-production deployment of Warehouse. Changes made here affect the production instance of PyPI (pypi.python.org).
Help us improve Python packaging - Donate today!

An SQL query building specification to be used in conjunction with REST APIs and its implementation for peewee.

Project Description

For the following Table example:

CREATE TABLE article (
    id integer NOT NULL,
    created timestamp without time zone NOT NULL,
    title character varying(255) NOT NULL,
    type_id integer NOT NULL,
    topic_id integer NOT NULL,
    author_ids integer[] NOT NULL,
    category_ids integer[],
    tags character varying(255)[],
    keywords character varying(255)[],
    summary text,
    content text NOT NULL,
    cover jsonb NOT NULL,
    editors_pick boolean NOT NULL,
    pageviews bigint NOT NULL,
    updated timestamp without time zone NOT NULL,
    published timestamp without time zone,
    permalink character varying(255),
    cust_meta jsonb
);

Specifications

  • For all articles with type_id equal to 1 (type_id = 1): json    {   "EQ": {      "type_id": 1   }    } Same structure is for:
Condition JSON KEY Symbol JSON Query
Less than LT < {"LT": {"type_id": 2}}
Less than or Equal to LE <= {"LE": {"type_id": 2}}
Greater than GT > {"GT": {"type_id": 2}}
Greater than or Equal to GE >= {"GE": {"type_id": 2}}
Not equal NE != {"NE": {"type_id": 2}}

IN

For all articles where type_id is in [1, 2, 3], the JSON query will be:

{
   "IN": {
      "pageviews": [1, 2, 3]
   }
}

BETWEEN

For all articles with pageviews between 10000 and 15000, the JSON query will be:

{
   "BETWEEN": {
      "pageviews": [10000, 15000]
   }
}

CONTAINS_ANY

For all articles where author_ids contains any of 8, 9, 10, the JSON query will be:

{
   "CONTAINS_ANY": {
      "author_ids": [8, 9, 10]
   }
}

CONTAINS_ALL

For all articles where author_ids contains all of 8, 9, the JSON query will be:

{
   "CONTAINS_ALL": {
      "author_ids": [8, 9]
   }
}

STARTSWITH

  • For all articles where title starts with Film Review, the JSON query will be:

    {
       "STARTSWITH": {
      "title": "Film Review"
       }
    }
    

Complex Queries

  • Complex queryies can contain nested structures of OR or AND or both.

For all articles with pageviews between 10000 and 15000 and whose author_ids contains 8(the author’s ID) (in above schema, author_ids is an ArrayField in Postgres), the JSON query will be:

{
   "AND": [
      {
         "BETWEEN": {
            "pageviews": [10000, 15000]
         }
      },
      {
         "CONTAINS": {
            "author_ids": [8]
         }
      }
   ]
}

Requirements

  • If there is only one condition, like pageviews > 100, the query can directly contain outermost key as one of EQ, NE, GT, GE, LT, LE, STARTSWITH, CONTAINS, CONTAINS_ALL, CONTAINS_ANY, BETWEEN.

example:

{
   "STARTSWITH": {
      "title": "Film Review"
   }
}
  • But if there are more conditions involved, the outermost key must be one of `OR
Release History

Release History

This version
History Node

0.1.3

History Node

0.1.2

History Node

0.1.1

Supported By

WebFaction WebFaction Technical Writing Elastic Elastic Search Pingdom Pingdom Monitoring Dyn Dyn DNS Sentry Sentry Error Logging CloudAMQP CloudAMQP RabbitMQ Heroku Heroku PaaS Kabu Creative Kabu Creative UX & Design Fastly Fastly CDN DigiCert DigiCert EV Certificate Rackspace Rackspace Cloud Servers DreamHost DreamHost Log Hosting