salt.pillar.mysql

Retrieve Pillar data by doing a MySQL query

MariaDB provides Python support through the MySQL Python package. Therefore, you may use this module with both MySQL or MariaDB.

maturity:new
depends:python-mysqldb
platform:all

Theory of mysql ext_pillar

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

  • If there's a keyword arg of mysql_query, that'll go first.
  • Then any non-keyword args are processed in order.
  • Finally, 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 the mysql ext_pillar

First an example of how legacy queries were specified.

ext_pillar:
  - mysql:
      mysql_query: "SELECT pillar,value FROM pillars WHERE minion_id = %s"

Alternatively, a list of queries can be passed in

ext_pillar:
  - mysql:
      - "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

ext_pillar:
  - mysql:
      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

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

Or as a mapping

ext_pillar:
  - mysql:
      - 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)

The legacy compatibility translates to depth 1.

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

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.

ext_pillar:
  - mysql:
      - 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

{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.

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.

MySQL configuration of the MySQL returner is being used (mysql.db, mysql.user, mysql.pass, mysql.port, mysql.host)

Required python modules: MySQLdb

More complete example

mysql:
  user: 'salt'
  pass: 'super_secret_password'
  db: 'salt_db'

ext_pillar:
  - mysql:
      fromdb:
        query: 'SELECT col1,col2,col3,col4,col5,col6,col7
                  FROM some_random_table
                 WHERE minion_pattern LIKE %s'
        depth: 5
        as_list: True
        with_lists: [1,3]
salt.pillar.mysql.ext_pillar(minion_id, pillar, *args, **kwargs)

Execute queries, merge and return as a dict

class salt.pillar.mysql.merger

This class receives and processes the database rows in a database agnostic way.

as_list = False
depth = 0
enter_root(root)

Set self.focus for kwarg queries

extract_queries(args, kwargs)

This function normalizes the config block into a set of queries we can use. The return is a list of consistently laid out dicts.

field_names = None
focus = None
ignore_null = False
num_fields = 0
process_fields(field_names, depth)

The primary purpose of this function is to store the sql field list and the depth to which we process.

process_results(rows)

This function takes a list of database results and iterates over, merging them into a dict form.

result = None
with_lists = None