1.. -*- coding: utf-8 -*-
2.. :Project:   pglast -- Usage
3.. :Created:   gio 10 ago 2017 10:06:38 CEST
4.. :Author:    Lele Gaifax <lele@metapensiero.it>
5.. :License:   GNU General Public License version 3 or later
6.. :Copyright: © 2017, 2018, 2019 Lele Gaifax
7..
8
9===================
10 Examples of usage
11===================
12
13Here are some example of how the module can be used.
14
15Parse an ``SQL`` statement and get its *AST* root node
16======================================================
17
18.. doctest::
19
20   >>> from pglast import Node, parse_sql
21   >>> root = Node(parse_sql('SELECT foo FROM bar'))
22   >>> print(root)
23   None=[1*{RawStmt}]
24
25Recursively :meth:`traverse <pglast.node.Node.traverse>` the parse tree
26=========================================================================
27
28.. doctest::
29
30   >>> for node in root.traverse():
31   ...   print(node)
32   ...
33   None[0]={RawStmt}
34   stmt={SelectStmt}
35   fromClause[0]={RangeVar}
36   inh=<True>
37   location=<16>
38   relname=<'bar'>
39   relpersistence=<'p'>
40   op=<0>
41   targetList[0]={ResTarget}
42   location=<7>
43   val={ColumnRef}
44   fields[0]={String}
45   str=<'foo'>
46   location=<7>
47
48As you can see, the ``repr``\ esentation of each value is mnemonic: ``{some_tag}`` means a
49``Node`` with tag ``some_tag``, ``[X*{some_tag}]`` is a ``List`` containing `X` nodes of that
50particular kind\ [*]_ and ``<value>`` is a ``Scalar``.
51
52Get a particular node
53=====================
54
55.. doctest::
56
57   >>> from_clause = root[0].stmt.fromClause
58   >>> print(from_clause)
59   fromClause=[1*{RangeVar}]
60
61Obtain some information about a node
62====================================
63
64.. doctest::
65
66   >>> range_var = from_clause[0]
67   >>> print(range_var.node_tag)
68   RangeVar
69   >>> print(range_var.attribute_names)
70   dict_keys(['relname', 'inh', 'relpersistence', 'location'])
71   >>> print(range_var.parent_node)
72   stmt={SelectStmt}
73
74Iterate over nodes
75==================
76
77.. doctest::
78
79   >>> for a in from_clause:
80   ...     print(a)
81   ...     for b in a:
82   ...         print(b)
83   ...
84   fromClause[0]={RangeVar}
85   inh=<True>
86   location=<16>
87   relname=<'bar'>
88   relpersistence=<'p'>
89
90Programmatically :func:`reformat <pglast.prettify>` a ``SQL`` statement\ [*]_
91===============================================================================
92
93.. doctest::
94
95   >>> from pglast import prettify
96   >>> print(prettify('delete from sometable where value is null'))
97   DELETE FROM sometable
98   WHERE value IS NULL
99
100.. doctest::
101
102   >>> print(prettify('select a,b,c from sometable where value is null'))
103   SELECT a
104        , b
105        , c
106   FROM sometable
107   WHERE value IS NULL
108
109.. doctest::
110
111   >>> print(prettify('select a,b,c from sometable'
112   ...                ' where value is null or value = 1',
113   ...                comma_at_eoln=True))
114   SELECT a,
115          b,
116          c
117   FROM sometable
118   WHERE (value IS NULL
119      OR (value = 1))
120
121Customize a :func:`node printer <pglast.printer.node_printer>`
122================================================================
123
124.. doctest::
125
126   >>> sql = 'update translations set italian=$2 where word=$1'
127   >>> print(prettify(sql))
128   UPDATE translations
129   SET italian = $2
130   WHERE word = $1
131   >>> from pglast.printer import node_printer
132   >>> @node_printer('ParamRef', override=True)
133   ... def replace_param_ref(node, output):
134   ...     output.write(repr(args[node.number.value - 1]))
135   ...
136   >>> args = ['Hello', 'Ciao']
137   >>> print(prettify(sql, safety_belt=False))
138   UPDATE translations
139   SET italian = 'Ciao'
140   WHERE word = 'Hello'
141
142:func:`Iterate <pglast.split>` over each statement
143==================================================
144
145.. doctest::
146
147   >>> from pglast import split
148   >>> for statement in split('select 1; select 2'):
149   ...     print(statement)
150   ...
151   SELECT 1
152   SELECT 2
153
154Reformat a ``SQL`` statement from the command line
155==================================================
156
157.. code-block:: shell
158
159   $ echo "select a,b,c from sometable" | pgpp
160   SELECT a
161        , b
162        , c
163   FROM sometable
164
165   $ echo 'update "table" set value=123 where value is null' | pgpp
166   UPDATE "table"
167   SET value = 123
168   WHERE value IS NULL
169
170   $ echo "
171   insert into t (id, description)
172   values (1, 'this is short enough'),
173          (2, 'this is too long, and will be splitted')" | pgpp -s 20
174   INSERT INTO t (id, description)
175   VALUES (1, 'this is short enough')
176        , (2, 'this is too long, an'
177              'd will be splitted')
178
179Get a more compact representation
180=================================
181
182.. code-block:: shell
183
184   $ echo "select a,b,c from st where a='longvalue1' and b='longvalue2'" \
185          | pgpp --compact 30
186   SELECT a, b, c
187   FROM st
188   WHERE (a = 'longvalue1')
189     AND (b = 'longvalue2')
190
191.. code-block:: shell
192
193   $ echo "select a,b,c from st where a='longvalue1' and b='longvalue2'" \
194          | pgpp --compact 60
195   SELECT a, b, c
196   FROM st
197   WHERE (a = 'longvalue1') AND (b = 'longvalue2')
198
199Obtain the *parse tree* of a ``SQL`` statement from the command line
200====================================================================
201
202.. code-block:: shell
203
204   $ echo "select 1" | pgpp --parse-tree
205   [
206     {
207       "RawStmt": {
208         "stmt": {
209           "SelectStmt": {
210             "op": 0,
211             "targetList": [
212               {
213                 "ResTarget": {
214                   "location": 7,
215                   "val": {
216                     "A_Const": {
217                       "location": 7,
218                       "val": {
219                         "Integer": {
220                           "ival": 1
221                         }
222                       }
223                     }
224                   }
225                 }
226               }
227             ]
228           }
229         }
230       }
231     }
232   ]
233
234
235---
236
237.. [*] This is an approximation, because in principle a list could contain different kinds of
238       nodes, or even sub-lists in some cases: the ``List`` representation arbitrarily shows
239       the tag of the first object.
240
241.. [*] Currently this covers most `DML` statements such as ``SELECT``\ s, ``INSERT``\ s,
242       ``DELETE``\ s and ``UPDATE``\ s, fulfilling my needs, but I'd like to extend it to
243       handle also `DDL` statements and, why not, `PLpgSQL` instructions too.
244