salt.states.sqlite3

Management of SQLite3 databases

depends

• SQLite3 Python Module

configuration

See salt.modules.sqlite3 for setup instructions

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='[email protected]'
    - data:
        email: [email protected]
        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="[email protected]"
    - 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