Metadata-Version: 2.1
Name: pglast
Version: 1.2
Summary: PostgreSQL Languages AST and statements prettifier
Home-page: https://github.com/lelit/pglast
Author: Lele Gaifax
Author-email: lele@metapensiero.it
License: GPLv3+
Keywords: postgresql parser sql prettifier
Classifier: Development Status :: 3 - Alpha
Classifier: Intended Audience :: Developers
Classifier: License :: OSI Approved :: GNU General Public License v3 or later (GPLv3+)
Classifier: Programming Language :: Python
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3 :: Only
Classifier: Programming Language :: Python :: 3.4
Classifier: Programming Language :: Python :: 3.5
Classifier: Programming Language :: Python :: 3.6
Classifier: Programming Language :: SQL
Classifier: Topic :: Database
Classifier: Topic :: Utilities
Provides-Extra: dev

.. -*- coding: utf-8 -*-
.. :Project:   pglast -- PostgreSQL Languages AST
.. :Created:   mer 02 ago 2017 14:49:24 CEST
.. :Author:    Lele Gaifax <lele@metapensiero.it>
.. :License:   GNU General Public License version 3 or later
.. :Copyright: © 2017, 2018, 2019 Lele Gaifax
..

========
 pglast
========

PostgreSQL Languages AST and statements prettifier
==================================================

:Author: Lele Gaifax
:Contact: lele@metapensiero.it
:License: `GNU General Public License version 3 or later`__
:Status: |build| |doc|

__ https://www.gnu.org/licenses/gpl.html
.. |build| image:: https://travis-ci.org/lelit/pglast.svg?branch=master
   :target: https://travis-ci.org/lelit/pglast
   :alt: Build status
.. |doc| image:: https://readthedocs.org/projects/pglast/badge/?version=latest
   :target: https://readthedocs.org/projects/pglast/builds/
   :alt: Documentation status

This is a Python 3 module that exposes the *parse tree* of a PostgreSQL__ statement (extracted
by the almost standard PG parser repackaged as a standalone static library by `libpg_query`__)
as set of interconnected *nodes*, usually called an *abstract syntax tree*.

__ https://www.postgresql.org/
__ https://github.com/lfittl/libpg_query

I needed a better SQL reformatter than the one implemented by `sqlparse`__, and was annoyed by
a few glitches (subselects__ in particular) that ruins the otherwise excellent job it does,
considering that it is a generic library that tries to swallow many different SQL dialects.

__ https://pypi.org/project/sqlparse/
__ https://github.com/andialbrecht/sqlparse/issues/334

When I found `psqlparse`__ I decided to try implementing a PostgreSQL `focused tool`__: at the
beginning it's been easier than I feared, but I quickly hit some shortcomings in that
implementation, so I opted for writing my own solution restarting from scratch, with the
following goals:

__ https://github.com/alculquicondor/psqlparse
__ https://github.com/alculquicondor/psqlparse/pull/22

- target only Python 3.4+

- target PostgreSQL 10

- use a more dynamic approach to represent the *parse tree*, with a twofold advantage:

  1. it is much less boring to code, because there's no need to write one Python class for each
     PostgreSQL node tag

  2. the representation is version agnostic, it can be adapted to newer/older Elephants in a
     snap

- allow exploration of parse tree in both directions, because I realized that some kinds of
  nodes require that knowledge to determine their textual representation

- avoid introducing arbitrary renames of tags and attributes, so what you read in PostgreSQL
  documentation/sources\ [*]_ is available without the hassle of guessing how a symbol has been
  mapped

- use a `zero copy`__ approach, keeping the original parse tree returned from the underlying
  libpg_query functions and have each node just borrow a reference to its own subtree

__ https://en.wikipedia.org/wiki/Zero-copy

.. [*] Currently what you can find in the following headers:

       - `lockoptions.h`__
       - `nodes.h`__
       - `parsenodes.h`__
       - `pg_class.h`__
       - `primnodes.h`__

__ https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/include/nodes/lockoptions.h;hb=HEAD
__ https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/include/nodes/nodes.h;hb=HEAD
__ https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/include/nodes/parsenodes.h;hb=HEAD
__ https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/include/catalog/pg_class.h;hb=HEAD
__ https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/include/nodes/primnodes.h;hb=HEAD

Introduction
------------

At the lower level the module exposes two libpg_query functions, ``parse_sql()`` and
``parse_plpgsql()``, that take respectively an ``SQL`` statement and a ``PLpgSQL`` statement
and return a *parse tree* as a hierarchy of Python dictionaries, lists and scalar values. In
some cases these scalars correspond to some C ``typedef enums``, that are automatically
extracted from the PostgreSQL headers mentioned above and are available as ``pglast.enums``.

At a higher level that tree is represented by three Python classes, a ``Node`` that represents
a single node, a ``List`` that wraps a sequence of nodes and a ``Scalar`` for plain values such
a *strings*, *integers*, *booleans* or *none*.

Every node is identified by a *tag*, a string label that characterizes its content that is
exposed as a set of *attributes* as well as with a dictionary-like interface (technically they
implements both a ``__getattr__`` method and a ``__getitem__`` method). When asked for an
attribute, the node returns an instance of the base classes, i.e. another ``Node``, or a
``List`` or a ``Scalar``, depending on the data type of that item. When the node does not
contain the requested attribute it returns a singleton ``Missing`` marker instance.

A ``List`` wraps a plain Python ``list`` and may contains a sequence of ``Node`` instances, or
in some cases other sub-lists, that can be accessed with the usual syntax, or iterated.

Finally, a ``Scalar`` carries a single value of some type, accessible through its ``value``
attribute.

On top of that, the module implements two serializations, one that transforms a ``Node`` into a
*raw* textual representation and another that returns a *prettified* representation. The latter
is exposed by the ``pgpp`` CLI tool, see below for an example.

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

As usual, the easiest way is with pip::

  $ pip install pglast

Alternatively you can clone the repository::

  $ git clone https://github.com/lelit/pglast.git --recursive

and install from there::

  $ pip install ./pglast

Development
-----------

There is a set of *makefiles* implementing the most common operations, a ``make help`` will
show a brief table of contents. A comprehensive test suite, based on pytest__, covers 98% of
the source lines.

__ https://docs.pytest.org/en/latest/

Examples of usage
-----------------

* Parse an ``SQL`` statement and get its *AST* root node::

   >>> from pglast import Node, parse_sql
   >>> root = Node(parse_sql('SELECT foo FROM bar'))
   >>> print(root)
   None=[1*{RawStmt}]

* Recursively traverse the parse tree::

   >>> for node in root.traverse():
   ...   print(node)
   ...
   None[0]={RawStmt}
   stmt={SelectStmt}
   fromClause[0]={RangeVar}
   inh=<True>
   location=<16>
   relname=<'bar'>
   relpersistence=<'p'>
   op=<0>
   targetList[0]={ResTarget}
   location=<7>
   val={ColumnRef}
   fields[0]={String}
   str=<'foo'>
   location=<7>

  As you can see, the ``repr``\ esentation of each value is mnemonic: ``{some_tag}`` means a
  ``Node`` with tag ``some_tag``, ``[X*{some_tag}]`` is a ``List`` containing `X` nodes of that
  particular kind\ [*]_ and ``<value>`` is a ``Scalar``.

* Get a particular node::

   >>> from_clause = root[0].stmt.fromClause
   >>> print(from_clause)
   fromClause=[1*{RangeVar}]

* Obtain some information about a node::

   >>> range_var = from_clause[0]
   >>> print(range_var.node_tag)
   RangeVar
   >>> print(range_var.attribute_names)
   dict_keys(['relname', 'inh', 'relpersistence', 'location'])
   >>> print(range_var.parent_node)
   stmt={SelectStmt}

* Iterate over nodes::

   >>> for a in from_clause:
   ...     print(a)
   ...     for b in a:
   ...         print(b)
   ...
   fromClause[0]={RangeVar}
   inh=<True>
   location=<16>
   relname=<'bar'>
   relpersistence=<'p'>

* Reformat a SQL statement\ [*]_ from the command line::

   $ echo "select a,b,c from sometable" | pgpp
   SELECT a
        , b
        , c
   FROM sometable

   $ echo "select a,b,c from sometable" | pgpp -c
   SELECT a,
          b,
          c
   FROM sometable

   $ echo 'update "table" set value=123 where value is null' | pgpp
   UPDATE "table"
   SET value = 123
   WHERE value IS NULL

   $ echo "
   insert into t (id, description)
   values (1, 'this is short enough'),
          (2, 'this is too long, and will be splitted')" | pgpp -s 20
   INSERT INTO t (id, description)
   VALUES (1, 'this is short enough')
        , (2, 'this is too long, an'
              'd will be splitted')

* Programmatically reformat a SQL statement::

   >>> from pglast import prettify
   >>> print(prettify('delete from sometable where value is null'))
   DELETE FROM sometable
   WHERE value IS NULL

Documentation
-------------

Latest documentation is hosted by `Read the Docs`__ at http://pglast.readthedocs.io/en/latest/

__ https://readthedocs.org/


.. [*] This is an approximation, because in principle a list could contain different kinds of
       nodes, or even sub-lists in some cases: the ``List`` representation arbitrarily shows
       the tag of the first object.

.. [*] Currently this covers most `DML` statements such as ``SELECT``\ s, ``INSERT``\ s,
       ``DELETE``\ s and ``UPDATE``\ s, fulfilling my needs, but I'd like to extend it to
       handle also `DDL` statements and, why not, `PLpgSQL` instructions too.


.. -*- coding: utf-8 -*-

Changes
-------

1.2 (2019-02-13)
~~~~~~~~~~~~~~~~

- Implement new `split()` function (see `PR #10`__)

  __ https://github.com/lelit/pglast/pull/10

- Implement ``BooleanTest`` printer (`issue #11`__)

  __ https://github.com/lelit/pglast/issues/11


1.1 (2018-07-20)
~~~~~~~~~~~~~~~~

- No visible changes, but now PyPI carries binary wheels for Python 3.7.


1.0 (2018-06-16)
~~~~~~~~~~~~~~~~

.. important:: The name of the package has been changed from ``pg_query`` to ``pglast``, to
               satisfy the request made by the author of ``libpg_query`` in `issue #9`__.

               This affects both the main repository on GitHub, that from now on is
               ``https://github.com/lelit/pglast``, and the ReadTheDocs project that hosts the
               documentation, ``http://pglast.readthedocs.io/en/latest/``.

               I'm sorry for any inconvenience this may cause.

__ https://github.com/lelit/pglast/issues/9


0.28 (2018-06-06)
~~~~~~~~~~~~~~~~~

- Update libpg_query to 10-1.0.2

- Support the '?'-style parameter placeholder variant allowed by libpg_query (details__)

__ https://github.com/lfittl/libpg_query/issues/45


0.27 (2018-04-15)
~~~~~~~~~~~~~~~~~

- Prettier JOINs representation, aligning them with the starting relation


0.26 (2018-04-03)
~~~~~~~~~~~~~~~~~

- Fix cosmetic issue with ANY() and ALL()


0.25 (2018-03-31)
~~~~~~~~~~~~~~~~~

- Fix issue in the safety belt check performed by ``pgpp`` (`issue #4`__)

__ https://github.com/lelit/pglast/issues/4


0.24 (2018-03-02)
~~~~~~~~~~~~~~~~~

- Implement ``Null`` printer


0.23 (2017-12-28)
~~~~~~~~~~~~~~~~~

- Implement some other DDL statements printers

- New alternative style to print *comma-separated-values* lists, activated by a new
  ``--comma-at-eoln`` option on ``pgpp``


0.22 (2017-12-03)
~~~~~~~~~~~~~~~~~

- Implement ``TransactionStmt`` and almost all ``DROP xxx`` printers


0.21 (2017-11-22)
~~~~~~~~~~~~~~~~~

- Implement ``NamedArgExpr`` printer

- New alternative printers for a set of *special functions*, activated by a new
  ``--special-functions`` option on ``pgpp`` (`issue #2`__)

__ https://github.com/lelit/pglast/issues/2


0.20 (2017-11-21)
~~~~~~~~~~~~~~~~~

- Handle special de-reference (``A_Indirection``) cases


0.19 (2017-11-16)
~~~~~~~~~~~~~~~~~

- Fix serialization of column labels containing double quotes

- Fix corner issues surfaced implementing some more DDL statement printers


0.18 (2017-11-14)
~~~~~~~~~~~~~~~~~

- Fix endless loop due to sloppy conversion of command line option

- Install the command line tool as ``pgpp``


0.17 (2017-11-12)
~~~~~~~~~~~~~~~~~

- Rename printers.sql to printers.dml (**backward incompatibility**)

- List printer functions in the documentation, referencing the definition of related node type

- Fix inconsistent spacing in JOIN condition inside a nested expression

- Fix representation of unbound arrays

- Fix representation of ``interval`` data type

- Initial support for DDL statements

- Fix representation of string literals containing single quotes


0.16 (2017-10-31)
~~~~~~~~~~~~~~~~~

- Update libpg_query to 10-1.0.0


0.15 (2017-10-12)
~~~~~~~~~~~~~~~~~

- Fix indentation of boolean expressions in SELECT's targets (`issue #3`__)

__ https://github.com/lelit/pglast/issues/3


0.14 (2017-10-09)
~~~~~~~~~~~~~~~~~

- Update to latest libpg_query's 10-latest branch, targeting PostgreSQL 10.0 final


0.13 (2017-09-17)
~~~~~~~~~~~~~~~~~

- Fix representation of subselects requiring surrounding parens


0.12 (2017-08-22)
~~~~~~~~~~~~~~~~~

- New option ``--version`` on the command line tool

- Better enums documentation

- Release the GIL while calling libpg_query functions


0.11 (2017-08-11)
~~~~~~~~~~~~~~~~~

- Nicer indentation for JOINs, making OUTER JOINs stand out

- Minor tweaks to lists rendering, with less spurious whitespaces

- New option ``--no-location`` on the command line tool


0.10 (2017-08-11)
~~~~~~~~~~~~~~~~~

- Support Python 3.4 and Python 3.5 as well as Python 3.6


0.9 (2017-08-10)
~~~~~~~~~~~~~~~~

- Fix spacing before the $ character

- Handle type modifiers

- New option ``--plpgsql`` on the command line tool, just for fun


0.8 (2017-08-10)
~~~~~~~~~~~~~~~~

- Add enums subpackages to the documentation with references to their related headers

- New ``compact_lists_margin`` option to produce a more compact representation when possible
  (see `issue #1`__)

__ https://github.com/lelit/pglast/issues/1


0.7 (2017-08-10)
~~~~~~~~~~~~~~~~

- Fix sdist including the Sphinx documentation


0.6 (2017-08-10)
~~~~~~~~~~~~~~~~

- New option ``--parse-tree`` on the command line tool to show just the parse tree

- Sphinx documentation, available online


0.5 (2017-08-09)
~~~~~~~~~~~~~~~~

- Handle some more cases when a name must be double-quoted

- Complete the serialization of the WindowDef node, handling its frame options


0.4 (2017-08-09)
~~~~~~~~~~~~~~~~

- Expose the actual PostgreSQL version the underlying libpg_query libray is built on thru a new
  ``get_postgresql_version()`` function

- New option `safety_belt` for the ``prettify()`` function, to protect the innocents

- Handle serialization of ``CoalesceExpr`` and ``MinMaxExpr``


0.3 (2017-08-07)
~~~~~~~~~~~~~~~~

- Handle serialization of ``ParamRef`` nodes

- Expose a ``prettify()`` helper function


0.2 (2017-08-07)
~~~~~~~~~~~~~~~~

- Test coverage at 99%

- First attempt at automatic wheel upload to PyPI, let's see...


0.1 (2017-08-07)
~~~~~~~~~~~~~~~~

- First release ("Hi daddy!", as my soul would tag it)
