<commit_before>"""
Retrieve Pillar data by doing a SQL query

This module is not meant to be used directly as an ext_pillar.
It is a place to put code common to PEP 249 compliant SQL database adapters.
It exposes a python ABC that can be subclassed for new database providers.

:maturity: new
:platform: all

Theory of sql_base ext_pillar
=============================

Ok, here's the theory for how this works...

- First, any non-keyword args are processed in order.
- Then, remaining keywords are processed.

We do this so that it's backward compatible with older configs.
Keyword arguments are sorted before being appended, so that they're predictable,
but they will always be applied last so overall it's moot.

For each of those items we process, it depends on the object type:

- Strings are executed as is and the pillar depth is determined by the number
  of fields returned.
- A list has the first entry used as the query, the second as the pillar depth.
- A mapping uses the keys "query" and "depth" as the tuple

You can retrieve as many fields as you like, how they get used depends on the
exact settings.

Configuring a sql_base ext_pillar
=================================

The sql_base ext_pillar cannot be used directly, but shares query configuration
with its implementations. These examples use a fake 'sql_base' adapter, which
should be replaced with the name of the adapter you are using.

A list of queries can be passed in

.. code-block:: yaml

  ext_pillar:
    - sql_base:
        - "SELECT pillar,value FROM pillars WHERE minion_id = %s"
        - "SELECT pillar,value FROM more_pillars WHERE minion_id = %s"

Or you can pass in a mapping

.. code-block:: yaml

  ext_pillar:
    - sql_base:
        main: "SELECT pillar,value FROM pillars WHERE minion_id = %s"
        extras: "SELECT pillar,value FROM more_pillars WHERE minion_id = %s"

The query can be provided as a string as we have just shown, but they can be
provided as lists

.. code-block:: yaml

  ext_pillar:
    - sql_base:
        - "SELECT pillar,value FROM pillars WHERE minion_id = %s"
          2

Or as a mapping

.. code-block:: yaml

  ext_pillar:
    - sql_base:
        - query: "SELECT pillar,value FROM pillars WHERE minion_id = %s"
          depth: 2

The depth defines how the dicts are constructed.
Essentially if you query for fields a,b,c,d for each row you'll get:

- With depth 1: {a: {"b": b, "c": c, "d": d}}
- With depth 2: {a: {b: {"c": c, "d": d}}}
- With depth 3: {a: {b: {c: d}}}

Depth greater than 3 wouldn't be different from 3 itself.
Depth of 0 translates to the largest depth needed, so 3 in this case.
(max depth == key count - 1)

Then they are merged in a similar way to plain pillar data, in the order
returned by the SQL database.

Thus subsequent results overwrite previous ones when they collide.

The ignore_null option can be used to change the overwrite behavior so that
only non-NULL values in subsequent results will overwrite.  This can be used
to selectively overwrite default values.

.. code-block:: yaml

  ext_pillar:
    - sql_base:
        - query: "SELECT pillar,value FROM pillars WHERE minion_id = 'default' and minion_id != %s"
          depth: 2
        - query: "SELECT pillar,value FROM pillars WHERE minion_id = %s"
          depth: 2
          ignore_null: True

If you specify `as_list: True` in the mapping expression it will convert
collisions to lists.

If you specify `with_lists: '...'` in the mapping expression it will
convert the specified depths to list.  The string provided is a sequence
numbers that are comma separated.  The string '1,3' will result in::

    a,b,c,d,e,1  # field 1 same, field 3 differs
    a,b,c,f,g,2  # ^^^^
    a,z,h,y,j,3  # field 1 same, field 3 same
    a,z,h,y,k,4  # ^^^^
      ^   ^

These columns define list grouping

.. code-block:: python

    {a: [
          {c: [
              {e: 1},
              {g: 2}
              ]
          },
          {h: [
              {j: 3, k: 4 }
              ]
          }
    ]}

The range for with_lists is 1 to number_of_fields, inclusive.
Numbers outside this range are ignored.

If you specify `as_json: True` in the mapping expression and query only for
single value, returned data are considered in JSON format and will be merged
directly.

.. code-block:: yaml

  ext_pillar:
    - sql_base:
        - query: "SELECT json_pillar FROM pillars WHERE minion_id = %s"
          as_json: True

The processed JSON entries are recursively merged in a single dictionary.
Additionnaly if `as_list` is set to `True` the lists will be merged in case of collision.

For instance the following rows:

    {"a": {"b": [1, 2]}, "c": 3}
    {"a": {"b": [1, 3]}, "d": 4}

will result in the following pillar with `as_list=False`

    {"a": {"b": [1, 3], "c": 3, "d": 4}

and in with `as_list=True`

    {"a": {"b": [1, 2, 3], "c": 3, "d": 4}

Finally, if you pass the queries in via a mapping, the key will be the
first level name where as passing them in as a list will place them in the
root.  This isolates the query results into their own subtrees.
This may be a help or hindrance to your aims and can be used as such.

You can basically use any SELECT query that gets you the information, you
could even do joins or subqueries in case your minion_id is stored elsewhere.
It is capable of handling single rows or multiple rows per minion.

Configuration of the connection depends on the adapter in use.<commit_msg>Add versionadded for sql_base as_json<commit_after>"""
Retrieve Pillar data by doing a SQL query

This module is not meant to be used directly as an ext_pillar.
It is a place to put code common to PEP 249 compliant SQL database adapters.
It exposes a python ABC that can be subclassed for new database providers.

:maturity: new
:platform: all

Theory of sql_base ext_pillar
=============================

Ok, here's the theory for how this works...

- First, any non-keyword args are processed in order.
- Then, remaining keywords are processed.

We do this so that it's backward compatible with older configs.
Keyword arguments are sorted before being appended, so that they're predictable,
but they will always be applied last so overall it's moot.

For each of those items we process, it depends on the object type:

- Strings are executed as is and the pillar depth is determined by the number
  of fields returned.
- A list has the first entry used as the query, the second as the pillar depth.
- A mapping uses the keys "query" and "depth" as the tuple

You can retrieve as many fields as you like, how they get used depends on the
exact settings.

Configuring a sql_base ext_pillar
=================================

The sql_base ext_pillar cannot be used directly, but shares query configuration
with its implementations. These examples use a fake 'sql_base' adapter, which
should be replaced with the name of the adapter you are using.

A list of queries can be passed in

.. code-block:: yaml

  ext_pillar:
    - sql_base:
        - "SELECT pillar,value FROM pillars WHERE minion_id = %s"
        - "SELECT pillar,value FROM more_pillars WHERE minion_id = %s"

Or you can pass in a mapping

.. code-block:: yaml

  ext_pillar:
    - sql_base:
        main: "SELECT pillar,value FROM pillars WHERE minion_id = %s"
        extras: "SELECT pillar,value FROM more_pillars WHERE minion_id = %s"

The query can be provided as a string as we have just shown, but they can be
provided as lists

.. code-block:: yaml

  ext_pillar:
    - sql_base:
        - "SELECT pillar,value FROM pillars WHERE minion_id = %s"
          2

Or as a mapping

.. code-block:: yaml

  ext_pillar:
    - sql_base:
        - query: "SELECT pillar,value FROM pillars WHERE minion_id = %s"
          depth: 2

The depth defines how the dicts are constructed.
Essentially if you query for fields a,b,c,d for each row you'll get:

- With depth 1: {a: {"b": b, "c": c, "d": d}}
- With depth 2: {a: {b: {"c": c, "d": d}}}
- With depth 3: {a: {b: {c: d}}}

Depth greater than 3 wouldn't be different from 3 itself.
Depth of 0 translates to the largest depth needed, so 3 in this case.
(max depth == key count - 1)

Then they are merged in a similar way to plain pillar data, in the order
returned by the SQL database.

Thus subsequent results overwrite previous ones when they collide.

The ignore_null option can be used to change the overwrite behavior so that
only non-NULL values in subsequent results will overwrite.  This can be used
to selectively overwrite default values.

.. code-block:: yaml

  ext_pillar:
    - sql_base:
        - query: "SELECT pillar,value FROM pillars WHERE minion_id = 'default' and minion_id != %s"
          depth: 2
        - query: "SELECT pillar,value FROM pillars WHERE minion_id = %s"
          depth: 2
          ignore_null: True

If you specify `as_list: True` in the mapping expression it will convert
collisions to lists.

If you specify `with_lists: '...'` in the mapping expression it will
convert the specified depths to list.  The string provided is a sequence
numbers that are comma separated.  The string '1,3' will result in::

    a,b,c,d,e,1  # field 1 same, field 3 differs
    a,b,c,f,g,2  # ^^^^
    a,z,h,y,j,3  # field 1 same, field 3 same
    a,z,h,y,k,4  # ^^^^
      ^   ^

These columns define list grouping

.. code-block:: python

    {a: [
          {c: [
              {e: 1},
              {g: 2}
              ]
          },
          {h: [
              {j: 3, k: 4 }
              ]
          }
    ]}

The range for with_lists is 1 to number_of_fields, inclusive.
Numbers outside this range are ignored.

If you specify `as_json: True` in the mapping expression and query only for
single value, returned data are considered in JSON format and will be merged
directly.

.. code-block:: yaml

  ext_pillar:
    - sql_base:
        - query: "SELECT json_pillar FROM pillars WHERE minion_id = %s"
          as_json: True

The processed JSON entries are recursively merged in a single dictionary.
Additionnaly if `as_list` is set to `True` the lists will be merged in case of collision.

For instance the following rows:

    {"a": {"b": [1, 2]}, "c": 3}
    {"a": {"b": [1, 3]}, "d": 4}

will result in the following pillar with `as_list=False`

    {"a": {"b": [1, 3], "c": 3, "d": 4}

and in with `as_list=True`

    {"a": {"b": [1, 2, 3], "c": 3, "d": 4}

Finally, if you pass the queries in via a mapping, the key will be the
first level name where as passing them in as a list will place them in the
root.  This isolates the query results into their own subtrees.
This may be a help or hindrance to your aims and can be used as such.

You can basically use any SELECT query that gets you the information, you
could even do joins or subqueries in case your minion_id is stored elsewhere.
It is capable of handling single rows or multiple rows per minion.

Configuration of the connection depends on the adapter in use.

.. versionadded:: 3005
   The *as_json* parameter.
{'fraction_non_alphanumeric': 0.0898394746442904, 'fraction_numerical': 0.008573513316307917, 'mean_word_length': 3.076208178438662, 'pattern_counts': {'":': 38, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3272158', 'n_tokens_mistral': 3434, 'n_tokens_neox': 3186, 'n_words': 1622}