o

�N�g�:�@sxdZzd
dl
Z
dZWney


dZYn
wdd�Zddd�
Zdd	d
�
Zdd�Zdd
d�
Zddd�
Z	dd�Z
dd�ZdS)a�	
Management of SQLite3 databases
===============================

.. versionadded:: 2016.3.0

:depends:   - SQLite3 Python Module
:configuration: See :py:mod:`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:

.. code-block:: yaml

    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:

.. code-block:: yaml

    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:

.. code-block:: yaml

    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:

.. code-block:: yaml

    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:

.. code-block:: yaml

    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:

.. code-block:: yaml

    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
�NTFc
Cstrd
SdS)z6
    Only load if the sqlite3 module is available
    T)FzUnable to import sqlite3)
�HAS_SQLITE3�rr�G/opt/saltstack/salt/lib/python3.10/site-packages/salt/states/sqlite3.py�__virtual__gs

rc

Cs�
|id
dd�}d
}z�z�tj
|
tjd�}t|_d
}|d
ur(t|d|d|�}nt|d|d||�}t|�
dkrCd|d	<d
|d<ndt|�
dkr�tdr`d
|d	<d||d<|d|dd<nG|d
urp|�d|d|�
}n|�d|d||�}|�	�
|j
dkr�d
|d	<d|d<|d|dd<nd|d	<d|d<nd
|d	<d|d<Wnty�
}	
zd|d	<t|	�
|d<WYd
}	~	nd
}	~	w
wW|r�|�
�
|S|r�|�
�
ww)ax

    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
    N���name�changes�result�comment�
Zdetect_types�SELECT * FROM `�` WHERE �
Fr
�-More than one row matched the specified queryr�testTzRow will be removed in r
r	�oldz
DELETE FROM `zRow removedzUnable to remove rowz
Row is absent)�sqlite3�connect�PARSE_DECLTYPES�
_dict_factory�row_factory�_query�len�__opts__�execute�commit�rowcount�	Exception�str�close)
r�db�table�	where_sql�
where_argsr	�conn�rows�cursor�
errr�
row_absentps`







�









�
�







�

���
�

�r)c
CsD|id
dd�}d
}�
z��
zetj
|
tjd�}t|_d
}	|d
ur*t|d|d|�}	nt|d|d||�}	t|	�
dkrFd|d	<d
|d<�
n*t|	�
dkr�|��D]�\}
}|
|	dvr�|	d|
|kr�|r�td
rsd|d	<d||d<qPg}g}
|��D]\}
}|�	d|
d�

|
�	|�

q{|d
u
r�|
|7}
d|d}|d�
|�
7}|d7}||7}|�||
�}|��
|j
dkr�d|d	<d|d<|	d|dd<||dd<qPd|d	<d|d<qPd|d	<d|d<
n
qP|d	d
ur�d|d	<d|d<n|td
�
r
d|d	<||dd<d||d<nfg}g}g}|��D]\}
}|�	d�

|�	|�

|�	d|
d�

�
qd|d }|d�
|�
7}|d!7}|d�
|�
7}|d"7}|�||�}|��
|j
dk�
rhd|d	<||dd<d#|d<nd|d	<d$|d<Wnt�
y�
}
zd|d	<t|�
|d<WYd
}~nd
}~w
wW|�
r�|��
|S|�
r�|��
ww)%a�
    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
    Nrrrr
rrFr
rrr
rTzRow will be update in �
`z`=?zUPDATE `z` SET �
,z WHERE zRow updatedr	r�newzRow update failedz)Existing data doesnot match desired statez
Row existszRow will be inserted into �
?z
INSERT INTO `�` (z
) VALUES (�
)zInserted rowzUnable to insert data)rrrrrrr�itemsr�append�joinrrrrrr )rr!r"�datar#r$�updater	r%r&�key�value�columns�params�sqlr'Z
value_stmt�valuesr(rrr�row_present�s�!







�


























��

�























�

���
�

�r;c
CsB
|id
dd�}d
}z�zitj
|
tjd�}t|d|g
�}t|�
dkrStdr1d|d	<d
|d|d<nA|�d
|�

|��
|dd|dd<d|d	<d
|d|d<nt|�
dkrfd|d	<d
|d|d<nd|d	<d|d
|d<Wnty�
}
zd|d	<t	|�
|d<WYd
}~nd
}~w
wW|r�|�
�
|S|r�|�
�
ww)z�
    Make sure the specified table does not exist

    name
        The name of the table

    db
        The name of the database file
    Nrrrz<SELECT sql FROM sqlite_master  WHERE type='table' AND name=?rrTr
�
'z' will be droppedrzDROP TABLE r
r	rz
' was droppedz' is already absentFz$Multiple tables with the same name=')rrrrrrrrrrr )rr!r	r%�tablesr(rrr�table_absent=
sH







�










�

���
�

�r>c	
Cs`|id
dd�}d
}�
zz�tj
|
tjd�}t|d|g
�}t|�
dkr�d
}t|t�r-|��}nt||�}||ddkr�|r�t	dr]d	|d
<|dd|dd<||dd
<d|d|d<n�|�
d|d�

|�
|�

|��
d	|d
<|dd|dd<||dd
<d|d|d<nrd|d
<d|d|dd|d<n_d	|d
<d|d|d<nRt|�
dkr�d
}t|t�r�|}nt||�}t	dr�d	|d
<||dd
<d|d|d<n&|�
|�

|��
d	|d
<||dd
<d|d|d<n
d|d
<d||d<Wnt�
y
}
zd|d
<t|�
|d<WYd
}~nd
}~w
wW|�
r%|�
�
|S|�
r/|�
�
ww)a�

    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
    Nrrrz;SELECT sql FROM sqlite_master WHERE type='table' AND name=?rr
rTr
r	rr,r<z' will be replacedrzDROP TABLE `r*z
Replaced 'FzExpected schema=z
actual schema=z' exists with matching schemaz' will be createdzCreated table 'z#Multiple tables with the same name=)rrrrr�
isinstancer�strip�_get_sql_from_schemarrrrr )	rr!�schema�forcer	r%r=r9r(rrr�
table_presentm
s|






�


















�















�

���
�

�rDcCs2d}|dur|�|
�
}|�
�S|�|
|�}|�
�S�
N)rZfetchall)r%r9Z
parametersr'rrrr�
s



�
rcCsd
|dd�|
�
dS)NzCREATE TABLE `r.r+r/)
r2)rrBrrrrA�
s
rAcCs,i}t|j
�
D]\}}|
|||d
<q|S)Nr
)�	enumerate�description)r'�row�
d�idx�colrrrr�
s



rrE)NF)
F)�__doc__rr�ImportErrorrr)r;r>rDrrArrrrr�<module>
s ^


�
	
J
0
Z