salt.states.sqlite3

Management of SQLite3 databases

depends:
  • SQLite3 Python Module
configuration:

See salt.modules.sqlite3 for setup instructions

New in version 2016.3.0.

The sqlite3 module is used to create and manage sqlite3 databases and execute queries

Here is an example of creating a table using sql statements:

users:
  sqlite3.table_present:
    - db: /var/www/data/app.sqlite
    - schema: CREATE TABLE `users` (`username` TEXT COLLATE NOCASE UNIQUE NOT NULL, `password` BLOB NOT NULL, `salt` BLOB NOT NULL, `last_login` INT)

Here is an example of creating a table using yaml/jinja instead of sql:

users:
  sqlite3.table_present:
    - db: /var/www/app.sqlite
    - schema:
      - email TEXT COLLATE NOCASE UNIQUE NOT NULL
      - firstname TEXT NOT NULL
      - lastname TEXT NOT NULL
      - company TEXT NOT NULL
      - password BLOB NOT NULL
      - salt BLOB NOT NULL

Here is an example of making sure a table is absent:

badservers:
  sqlite3.table_absent:
    - db: /var/www/data/users.sqlite

Sometimes you would to have specific data in tables to be used by other services Here is an example of making sure rows with specific data exist:

user_john_doe_xyz:
  sqlite3.row_present:
    - db: /var/www/app.sqlite
    - table: users
    - where_sql: email='john.doe@companyxyz.com'
    - data:
        email: john.doe@companyxyz.com
        lastname: doe
        firstname: john
        company: companyxyz.com
        password: abcdef012934125
        salt: abcdef012934125
    - require:
      - sqlite3: users

Here is an example of removing a row from a table:

user_john_doe_abc:
  sqlite3.row_absent:
    - db: /var/www/app.sqlite
    - table: users
    - where_sql: email="john.doe@companyabc.com"
    - require:
      - sqlite3: users

Note that there is no explicit state to perform random queries, however, this can be approximated with sqlite3's module functions and module.run:

zone-delete:
  module.run:
    - name: sqlite3.modify
    - db: {{ db }}
    - sql: "DELETE FROM records WHERE id > {{ count[0] }} AND domain_id = {{ domain_id }}"
    - watch:
      - sqlite3: zone-insert-12
salt.states.sqlite3.row_absent(name, db, table, where_sql, where_args=None)

Makes sure the specified row is absent in db. If multiple rows match where_sql, then the state will fail.

name
Only used as the unique ID
db
The database file name
table
The table name to check
where_sql
The sql to select the row to check
where_args
The list parameters to substitute in where_sql
salt.states.sqlite3.row_present(name, db, table, data, where_sql, where_args=None, update=False)

Checks to make sure the given row exists. If row exists and update is True then row will be updated with data. Otherwise it will leave existing row unmodified and check it against data. If the existing data doesn't match data_check the state will fail. If the row doesn't exist then it will insert data into the table. If more than one row matches, then the state will fail.

name
Only used as the unique ID
db
The database file name
table
The table name to check the data
data
The dictionary of key/value pairs to check against if row exists, insert into the table if it doesn't
where_sql
The sql to select the row to check
where_args
The list parameters to substitute in where_sql
update
True will replace the existing row with data When False and the row exists and data does not equal the row data then the state will fail
salt.states.sqlite3.table_absent(name, db)

Make sure the specified table does not exist

name
The name of the table
db
The name of the database file
salt.states.sqlite3.table_present(name, db, schema, force=False)

Make sure the specified table exists with the specified schema

name
The name of the table
db
The name of the database file
schema
The dictionary containing the schema information
force
If the name of the table exists and force is set to False, the state will fail. If force is set to True, the existing table will be replaced with the new table