{ "info": { "author": "Jim Fulton", "author_email": "jim@jimfulton.info", "bugtrack_url": null, "classifiers": [ "Framework :: ZODB", "Intended Audience :: Developers", "License :: OSI Approved :: MIT License", "Operating System :: Microsoft :: Windows", "Operating System :: Unix", "Programming Language :: Python", "Programming Language :: Python :: 2", "Programming Language :: Python :: 2.7", "Programming Language :: Python :: 3", "Programming Language :: Python :: 3.4", "Programming Language :: Python :: 3.5", "Programming Language :: Python :: 3.6", "Programming Language :: Python :: Implementation :: CPython", "Programming Language :: Python :: Implementation :: PyPy", "Topic :: Database", "Topic :: Software Development :: Libraries :: Python Modules" ], "description": "==============================\nNewt Query by Example\n==============================\n\nNewt QBE is a small `Newt DB `_ add-on that\nprovides help building SQL ``WHERE`` clauses for searching Newt DB\ndatabases, especially when search is driven by form input. It also\nprovides some abstraction of JSONB-search-expression details.\n\nIt is **not a goal** of of newt.qbe to replace use of SQL. PostgreSQL is\npowerful and well documented. You'll get more out of Newt DB if you\nunderstand how to apply it when searching Newt databases. To that end,\nthis package can provide some SQL construction hints.\n\n.. contents::\n\nOverview\n=========\n\nTo set up Newt QBE, you'll create a QBE object, add some items to it,\nand add it to your database:\n\n >>> import newt.qbe\n >>> qbe = newt.qbe.QBE()\n >>> qbe['email'] = newt.qbe.scalar('email')\n >>> qbe['stars'] = newt.qbe.scalar(\"state->'rating'->'stars'\", type='int')\n >>> qbe['keywords'] = newt.qbe.text_array('keywords')\n >>> qbe['path'] = newt.qbe.prefix('path', delimiter='/')\n >>> qbe['text'] = newt.qbe.fulltext('content_text(state)', 'english')\n\nThen, you can generate SQL using the ``sql`` method, which takes a\ndictionary of item names and values:\n\n >>> import newt.db\n >>> conn = newt.db.connection(dsn)\n >>> sql = qbe.sql(conn, dict(path='/foo', text='newt'))\n >>> result = conn.where(sql)\n\nIn addition to a criteria mapping, you can supply an ``order_by``\nkeyword argument to specify sorting information.\n\nThe items in a QBE are search helpers. There are several built-in\nhelpers:\n\nmatch\n Exact match scalar values, leveraging the standard JSONB GIN index\n\nscalar\n Search scalar data, including range search\n\ntext_array\n Search text-array data\n\nprefix\n Search string scalars by prefix.\n\nfulltext\n Full-text search of text fields\n\nsql\n Searcher that uses provided SQL\n\nYou can also define your own helpers by implementing a fairly simple\nAPI. A search helper provides the following methods, only one of\nwhich is required:\n\n``__call__(cursor, query)``\n Compute the helper's contribution to s search ``WHERE`` clause.\n\n This required method returns a bytes SQL expression.\n\n The required format of the query is up to the helper.\n\n The helper must use the `mogrify\n `_ method\n of the cursor argument to substitute data from the query.\n\n``order_by(cursor, query)``\n Compute a PostgreSQL expression to be used in an ``ORDER BY`` clause.\n\n This required method returns a bytes SQL expression.\n\n This method is optional. If it is not provided, then ordering on the\n helper won't be allowed.\n\n The helper must use the `mogrify\n `_ method\n of the cursor argument to substitute data from the query, if necessary.\n\n``index_sql(name)``\n Return a PostgreSQL string to be used to create a corresponding index.\n\n This method is optional.\n\nThe constructor arguments and search criteria are specific to each helper.\n\nQBE methods\n===========\n\nQBE objects provide the following methods:\n\n``sql(query, order_by=())``\n---------------------------\n\nReturn contents of a PostgreSQL ``WHERE`` clause (as bytes).\n\nAn SQL boolean expression is returned by combining expressions given\nin the query. (If the query is empty, then ``'true'`` is returned.)\n\nThe query argument must be a mapping object. The keys must also\nexist in the QBE. The values, who's format is helper specific, are\npassed to helper's ``__call__`` methods.\n\nThe ``order_by`` argument is an iterable of ordering criteria. The items may\nbe helper names or two-tuples containing helper names and descending flags.\n\nTo illustrate the usage, here are some examples using the QBE object\ncreated in the overview section:\n\n >>> qbe.sql(conn, dict())\n b'true'\n\n >>> print(qbe.sql(conn, dict(text='database', path='/wiki'),\n ... order_by=[('stars', True), 'text']).decode('ascii'))\n (((state ->> 'path') || '/') like '/wiki' || '/%') AND\n content_text(state) @@ to_tsquery('english', 'database')\n ORDER BY (state->'rating'->>'stars')::int DESC,\n ts_rank_cd(array[0.1, 0.2, 0.4, 1], content_text(state), to_tsquery('english', 'database'))\n\n``index_sql(*names)``\n---------------------\n\nReturn a list of PostgreSQL texts to create indexes for the given\nhelpers. If no helpers are specified, then statements for all of the\nhelpers (that implement the optional ``index_sql`` method) are\nreturned).\n\n >>> for sql in qbe.index_sql():\n ... print(sql)\n CREATE INDEX CONCURRENTLY newt_email_idx ON newt ((state ->> 'email'))\n CREATE INDEX CONCURRENTLY newt_keywords_idx ON newt USING GIN ((state -> 'keywords'))\n CREATE INDEX CONCURRENTLY newt_path_idx ON newt (((state ->> 'path') || '/') text_pattern_ops)\n CREATE INDEX CONCURRENTLY newt_stars_idx ON newt (((state->'rating'->>'stars')::int))\n CREATE INDEX CONCURRENTLY newt_text_idx ON newt USING GIN (content_text(state))\n\nA list is returned because the statements need to be executed\nindividually (because of the user of ``CONCURRENTLY``).\n\nBuilt-in helpers\n================\n\n``match(name, convert=None)``\n-----------------------------\n\nMatch named scalar values (using the JSONB ``@>`` operator). This\nleverages the JSON GIN index that's created by default for Newt\ndatabases. It doesn't support range searches.\n\n``scalar(expr, type=None, convert=None)``\n-----------------------------------------\n\nThe ``scalar`` helper searches based on scalar values. The constructor\ntakes an expression that yields a text result. For convenience, if an\nidentifier (for example ``'email'``) is given, then it will be\ncomputed to an expression for accessing a top-level property. Also,\nfor convenience, if a simple JSON accessor expression, like::\n\n state -> 'x' -> 0\n\nit will be modified to produce a text result::\n\n state -> 'x' ->> 0\n\nYou can supply an optional second argument giving the name of a\nPostgreSQL data type to convert the text value to.\n\nThe optional ``convert`` argument provides callable to be used to\nconvert query values to values that may be passed to psycopg2 cursor\n``mogrify`` methods.\n\n``text_array(expr, convert=None)``\n----------------------------------\n\nThe ``array`` helper searches based on text-array values. The constructor takes\nan expression that yields a PostgreSQL array of text.\n\nSearches are based on overlap. Search criteria are satisfied if\nsearched values have elements in common with the given query\nvalue. For example, a query: ``['a', 'b']`` matches a PostgreSQL array\n``['a', 'c']``.\n\nThe optional ``convert`` argument provides callable to be used to\nconvert query values to values that may be passed to psycopg2 cursor\n``mogrify`` methods.\n\n``prefix(expr, delimiter=None, convert=None)``\n----------------------------------------------\n\nThe ``prefix`` helper supports prefix queries against scalar text values.\nThis will often be used for path searches.\n\nThe constructor takes an expression that yields a text result. As\nwith the scalar helper, an identifier or JSON accessor will be\nconverted to an expression, if necessary.\n\nAn optional second argument may be provided giving a path delimiter.\nIf provided, the delimiter will be included in ``like`` queries. If\nan expression is generated from an identifier or simpler JSON\naccessor, then the delimiter will be included in the generated\nexpression as well.\n\nThe optional ``convert`` argument provides callable to be used to\nconvert query values to values that may be passed to psycopg2 cursor\n``mogrify`` methods.\n\n``fulltext(expr, config, parer=None, weights=(.1, .2, .4, 1.0))``\n-----------------------------------------------------------------\n\nThe ``fulltext`` helper supports full-text search. The constructor\ntakes an expression that evaluates to a PostgreSQL `ts_vector\n`_\nand the name of a `test-search configuration\n`_.\n\nFor convenience, if an identifier or a JSON accessor (like ``state ->\n'x' -> 0``) is given, a tsvector expression is generated.\n\nWhen searching, queries are provided as strings that are passed\n`to_tsquery\n`_. An\noptional query parser function may be provided to transform the search\nqueries.\n\nIf a text helper is used for ordering, the `ts_rank_cd function\n`_\nwill be called with the supplied weights.\n\n``sql(cond, order=None, convert=None)``\n---------------------------------------\n\nThe ``sql`` helper provides a way to encapsulate more or less arbitrary\nSQL as a search helper. The constructor takes an string SQL\nexpression to use when searching. The string should contain a single\n`placeholder\n`_\nfor substituting query data.\n\nAn optional second argument provides an SQL expression to use for\nordering.\n\nThe optional ``convert`` argument provides callable to be used to\nconvert query values to values that may be passed to psycopg2 cursor\n``mogrify`` methods.\n\nStatus\n======\n\nThis project is in an early stage of development. The built-in\nhelpers cover common cases. Initial helpers are convenient for the\ninitial application for which this is being developed. It's easy to\nimagine future enhancements. Contributions and suggestions are\nwelcome, especially when motivated by specific needs.\n\nIt's worth noting that the ``sql`` helper can cover a lot of gaps.\nFor example the initial applications needs to search against\nPostgreSQL arrays returned from functions, rather than JSON arrays.\nThis is easily handled by the ``sql`` helper::\n\n sql(\"allowed_to_view(state) && %s\")\n\n\nChange history\n==============\n\n\n0.1.1 (2017-06-21)\n------------------\n\n- Fixed an sql-generation bug that caused invalid SQL to be generated\n when a full-text index was used for ordering.\n\n\n0.1.0 (2017-04-26)\n------------------\n\nInitial release", "description_content_type": null, "docs_url": null, "download_url": "", "downloads": { "last_day": -1, "last_month": -1, "last_week": -1 }, "home_page": "https://github.com/newtdb/qbe", "keywords": "database nosql python postgresql postgres search", "license": "MIT", "maintainer": "", "maintainer_email": "", "name": "newt.qbe", "package_url": "https://pypi.org/project/newt.qbe/", "platform": "", "project_url": "https://pypi.org/project/newt.qbe/", "project_urls": { "Homepage": "https://github.com/newtdb/qbe" }, "release_url": "https://pypi.org/project/newt.qbe/0.1.1/", "requires_dist": null, "requires_python": "", "summary": "Newt Query by Example", "version": "0.1.1" }, "last_serial": 2966023, "releases": { "0.1.0": [ { "comment_text": "", "digests": { "md5": "9ea1fe69ce100a65e10afd80cee0a2fc", "sha256": "647164b91b20e3ce199ad5302b14984eae8c053fca432e408f895a7207fc6060" }, "downloads": -1, "filename": "newt.qbe-0.1.0.tar.gz", "has_sig": false, "md5_digest": "9ea1fe69ce100a65e10afd80cee0a2fc", "packagetype": "sdist", "python_version": "source", "requires_python": null, "size": 13920, "upload_time": "2017-04-26T14:51:43", "url": "https://files.pythonhosted.org/packages/56/a0/8a2106277e25572daf8ca166abce46f1dee9ace230bbfdf87b7c0731aae2/newt.qbe-0.1.0.tar.gz" } ], "0.1.1": [ { "comment_text": "", "digests": { "md5": "fb0d368bbdb0c4f4c7d4ca077741684d", "sha256": "b685f969cda84aaf968283a29fc09a153176da23efe749250e7c38ab4c06c04e" }, "downloads": -1, "filename": "newt.qbe-0.1.1.tar.gz", "has_sig": false, "md5_digest": "fb0d368bbdb0c4f4c7d4ca077741684d", "packagetype": "sdist", "python_version": "source", "requires_python": null, "size": 14069, "upload_time": "2017-06-21T20:37:35", "url": "https://files.pythonhosted.org/packages/96/c2/817e233b34c8dd6398d857f29e3c951a2145260d3eb005b584a30ddae888/newt.qbe-0.1.1.tar.gz" } ] }, "urls": [ { "comment_text": "", "digests": { "md5": "fb0d368bbdb0c4f4c7d4ca077741684d", "sha256": "b685f969cda84aaf968283a29fc09a153176da23efe749250e7c38ab4c06c04e" }, "downloads": -1, "filename": "newt.qbe-0.1.1.tar.gz", "has_sig": false, "md5_digest": "fb0d368bbdb0c4f4c7d4ca077741684d", "packagetype": "sdist", "python_version": "source", "requires_python": null, "size": 14069, "upload_time": "2017-06-21T20:37:35", "url": "https://files.pythonhosted.org/packages/96/c2/817e233b34c8dd6398d857f29e3c951a2145260d3eb005b584a30ddae888/newt.qbe-0.1.1.tar.gz" } ] }