
    g[L                        d dl Z d dlZd dlZd dlZd dlZd dlZd dlmZ d dlmZ dZ ej                         Z
dad ZddZddZd	 Zdd
Zd Zd Zd Zd ZddZddZddZd Zd Zd ZddZddZd Zd Z G d de      Z y)    N)helpers)loggerztautulli.dbFc                     | a y N)IS_IMPORTING)values     /opt/Tautulli/plexpy/database.pyset_is_importingr
   !   s    L    c                 @   	 t        j                  | d      }	 |j                  d       |j                          y# t         j                  t         j                  t        f$ r }t        j                  d|       Y d }~yd }~wt        $ r }t        j                  d|       Y d }~yd }~ww xY w# t         j                  t         j                  t        f$ r }t        j                  d|       Y d }~yd }~wt        $ r }t        j                  d|       Y d }~yd }~ww xY w)	N   timeoutz3Tautulli Database :: Invalid database specified: %szInvalid database specifiedz+Tautulli Database :: Uncaught exception: %szUncaught exceptionz#SELECT started from session_historysuccess)
sqlite3connectOperationalErrorDatabaseError
ValueErrorr   error	Exceptionexecuteclose)database
connectiones      r	   validate_databaser   &   s    $__Xr:
$@A # $$g&;&;ZH ,JAN+ $BAF#$ $$g&;&;ZH ,JAN+ $BAF#$sD   < !B. (B+$A??B+B&&B+.(DC11D=DDc           	      
   t         rt        j                  d       yt        |       }|dk(  st        j                  d|       y|dvrt        j                  d|       y|r5t        j
                  d       t               st        j                  d	       yt        j
                  d
| |       t        d       t               }|j                  j                  d       |j                  j                  d| g       	 |j                  d      }|d   }t        j
                  d|       t        j                   |      }|j                  d      }|j#                  dd      }d}	|dk(  rt        j
                  d       |	D ]o  }
|j%                  dj'                  |
             |j%                  dj'                  |
      |g       |
dk(  sM|j%                  dj'                  |
      |g       q |t        j                   d      k  rR|dk(  rd}d}nd }d!}|j%                  d"j'                  ||#             |j%                  d$j'                  ||#             i }|j)                  d%      }|D ]M  }|d&   }
|
d'k(  s|
d(k(  r|j)                  d)j'                  |
            }|s8t        j
                  d*|
       |d+k(  r4|j%                  d,j'                  |
             |j%                  d-|
g       |dk(  r|
|	v rd}|
dz   }nd }|
}|D cg c]  }|d&   	 }}|j)                  d.j'                  ||/            }|dk(  r%|
|	vr!|D cg c]  }|d&   |v s|d0   r|d&    }}n|D cg c]  }|d&   |v s|d&    }}|||
<   d1j+                  |      }|j%                  d2j'                  |
|||3             P |j                  j                  d4       |dk(  rt-        |j/                               D ]  \  }
}d1j+                  |D cg c]	  }|d5vs| c}      }t        j
                  d6|
       |
|	d7d  v r"|j%                  d8j'                  |
             i|j%                  d9j'                  |
|:              t        j
                  d;       |	D ]#  }
|j%                  d<j'                  |
             % t1                t        j
                  d=       t        d       t        j
                  d>|        t3        j4                  |        y # t        j                  t        f$ r d}Y %w xY wc c}w c c}w c c}w c c}w )?NzTautulli Database :: Another Tautulli database is currently being imported. Please wait until it is complete before importing another database.F)r   r   z;Tautulli Database :: Failed to import Tautulli database: %s)merge	overwritezSTautulli Database :: Failed to import Tautulli database: invalid import method '%s'zATautulli Database :: Creating a database backup before importing.zYTautulli Database :: Failed to import Tautulli database: failed to create database backupzPTautulli Database :: Importing Tautulli database '%s' with import method '%s'...TBEGIN IMMEDIATEzATTACH ? AS import_dbz:SELECT * FROM import_db.version_info WHERE key = 'version'r   zv2.6.10z9Tautulli Database :: Import Tautulli database version: %sz>SELECT seq FROM sqlite_sequence WHERE name = 'session_history'seqr   )session_historysession_history_metadatasession_history_media_infor   z\Tautulli Database :: Creating temporary database tables to re-index grouped session history.z<CREATE TABLE {table}_copy AS SELECT * FROM import_db.{table})tablez#UPDATE {table}_copy SET id = id + ?r#   z7UPDATE {table}_copy SET reference_id = reference_id + ?zv2.7.0main_copy	import_db zIALTER TABLE {from_db}.session_history{copy} ADD COLUMN section_id INTEGER)from_dbcopyzUPDATE {from_db}.session_history{copy} SET section_id = (SELECT section_id FROM {from_db}.session_history_metadata{copy} WHERE {from_db}.session_history_metadata{copy}.id = {from_db}.session_history{copy}.id)zgSELECT name FROM import_db.sqlite_master WHERE type = 'table' AND name NOT LIKE 'sqlite_%'ORDER BY namenamesessionsversion_infozPRAGMA main.table_info({table})z3Tautulli Database :: Importing database table '%s'.r    zDELETE FROM {table}z*DELETE FROM sqlite_sequence WHERE name = ?z)PRAGMA {from_db}.table_info({from_table}))r+   
from_tablepk, zVINSERT OR IGNORE INTO {table} ({columns}) SELECT {columns} FROM {from_db}.{from_table})r&   columnsr+   r0   zDETACH import_db)idreference_idzFTautulli Database :: Removing duplicate rows from database table '%s'.   zDDELETE FROM {table} WHERE id NOT IN (SELECT id FROM session_history)zTDELETE FROM {table} WHERE id NOT IN (SELECT MIN(id) FROM {table} GROUP BY {columns}))r&   r3   z8Tautulli Database :: Deleting temporary database tables.zDROP TABLE {table}_copyz7Tautulli Database :: Tautulli database import complete.z1Tautulli Database :: Deleting cached database: %s)r   r   warnr   r   infomake_backupr
   MonitorDatabaser   r   select_singler   r   KeyErrorr   version_to_tuplegetactionformatselectjoinsorteditemsvacuumosremove)r   methodbackupdb_validatedbr/   import_db_versionsession_history_seqsession_history_rowssession_history_tables
table_namefrom_db_namer,   table_columnstablesr&   current_tablefrom_table_nameccurrent_columnsimport_tableimport_columnsinsert_columnsr3   duplicate_columnss                            r	   import_tautulli_dbr\   =   s    Z 	[#X6K)#RT_`++jlrsWX}LLtu
KKbdlntuT		BMM+,MM1H:>&''(de(1 KKKM^_001BC **+kl.225!<jrs0 	2JIIT[[bl[mnII;BBBT+,...		SZZakZl/02	2 733H==W!LD&LD
		 228&>B 39 3D	E 			 8 9?|DH 9? 9J	K MYY ' (F  +e6]
#z^'C		"C"J"JQ["J"\]I:V[ II+222DEIIBZLQW/E!E!L(72O&L(O /<<1V9<<yy!L!S!S\h_n "T "p q W3I!I1=nA6oA]fghlfmainNn1=^A6oA]ai^N^$2j!>2 			 AAGjP^P\Sb BH Bd	eO+eZ MM,-#)-*=*=*?#@ 		pJ $		g*aJ`A`1*a bKK`blm3AB77		 ==CV*V=UW 		 MMSVZd\m NT Nop		p 	NO0 	JJII/66Z6HI	J H
KKIJU
KKCXNIIhe $$h/ &%&N =
 o^  +bsB   <T T;U U U -U:U	U

U

T87T8c                  <    t               } | j                  d      }|S )NzPRAGMA integrity_check)r:   r;   )
monitor_dbresults     r	   integrity_checkr`      s      "J%%&>?FMr   c                     | rBt               }t        j                  d| z         	 |j                  d| z         t	                yy # t
        $ r&}t        j                  d| d|d       Y d }~yd }~ww xY w)Nz2Tautulli Database :: Clearing database table '%s'.zDELETE FROM %sTz5Tautulli Database :: Failed to clear database table 'z': .F)r:   r   debugr?   rE   r   r   )r&   r^   r   s      r	   clear_tablerd      sm    $&
IEQR	.67H   	LL\acdef	s   A 	A5A00A5c                  B    t        j                  d       t        d      S )Nz?Tautulli Database :: Clearing temporary sessions from database.r.   r   r8   rd    r   r	   delete_sessionsrh      s    
KKQRz""r   c                  B    t        j                  d       t        d      S )NzATautulli Database :: Clearing recently added items from database.recently_addedrf   rg   r   r	   delete_recently_addedrk      s    
KKST'((r   c                  B    t        j                  d       t        d      S )Nz;Tautulli Database :: Clearing exported items from database.exportsrf   rg   r   r	   delete_exportsrn      s    
KKMNy!!r   c           	         |rBt        |t              r2t        t        t        j
                  |j                  d                  }|rt        j                  d||        d}t               }	 t	        j                  ||      D ];  }d| z   ddj                  dgt        |      z        z  z   }|j                  ||       = t                y
y
# t        $ r%}t        j                   d| d|       Y d }~y	d }~ww xY w)N,z?Tautulli Database :: Deleting row ids %s from %s database tablei  zDELETE FROM z WHERE id IN (%s) ?z0Tautulli Database :: Failed to delete rows from z database table: FT)
isinstancestrlistmapr   cast_to_intsplitr   r8   r:   chunkrB   lenr?   rE   r   r   )r&   row_idssqlite_max_variable_numberr^   row_ids_groupqueryr   s          r	   delete_rows_from_tabler~      s    :gs+s7..c0BCDUW^`ef &)"$&
	!(w8R!S 8&.1ERUQVY\]jYkQkHl1ll!!%78 H
 	  	LLdiklmn	s   +AC 	C9C44C9c                 b    g }dD ]  }|j                  t        ||                t        |      S )N)r#   r%   r$   )r&   rz   )appendr~   all)rz   r   r&   s      r	   delete_session_history_rowsr     s6    G^ M-E7KLMw<r   c                     t        |       j                         rSt               }|j                  d| g      }|D cg c]  }|d   	 }}t	        j
                  d| z         t        |      S y c c}w )Nz0SELECT id FROM session_history WHERE user_id = ?r4   zGTautulli Database :: Deleting all history for user_id %s from database.rz   rs   isdigitr:   rA   r   r8   r   )user_idr^   r_   rowrz   s        r	   delete_user_historyr     ss    
7|$&
 ""#U$+9.(./3t9//]`ggh*7;;  0   A.c                     t        |       j                         rSt               }|j                  d| g      }|D cg c]  }|d   	 }}t	        j
                  d| z         t        |      S y c c}w )Nz3SELECT id FROM session_history WHERE section_id = ?r4   zRTautulli Database :: Deleting all history for library section_id %s from database.r   r   )
section_idr^   r_   r   rz   s        r	   delete_library_historyr   !  ss    
: $&
 ""#X$.<1(./3t9//hkuuv*7;; ! 0r   c                      t               } t        j                  d       	 | j                  d       y # t        $ r"}t        j
                  d|z         Y d }~y d }~ww xY w)Nz(Tautulli Database :: Vacuuming database.VACUUMz2Tautulli Database :: Failed to vacuum database: %sr:   r   r8   r?   r   r   r^   r   s     r	   rE   rE   .  sR     "J
KK:;O(# OIAMNNO   3 	AAAc                      t               } t        j                  d       	 | j                  d       y # t        $ r"}t        j
                  d|z         Y d }~y d }~ww xY w)Nz)Tautulli Database :: Optimizing database.zPRAGMA optimizez4Tautulli Database :: Failed to optimize database: %sr   r   s     r	   optimizer   8  sS     "J
KK;<Q+, QKaOPPQr   c                  ,    t                t                y r   )rE   r   rg   r   r	   optimize_dbr   B  s    
HJr   c                 n    | 2t         j                  j                  t        j                  t
              S | S )z  Returns the filepath to the db )rF   pathrB   plexpyDATA_DIRFILENAME)filenames    r	   db_filenamer   G  s&    ww||FOOX66Or   c           	      l   t               d   dk(  }d}|s#d}t        j                  j                  ddi       |r%dj	                  t        j                         |      }n$dj	                  t        j                         |      }t        j                  j                  }t        j                  j                  ||      }t        j                  j                  |      st        j                  |       t               }|j                  j!                  d	       t#        j$                  t'               |       |j                  j)                          | r|rt+        j*                         }t        j,                  |      D ]  \  }	}
}|D cg c]4  }|j/                  d
      st        j                  j                  |	|      6 }}|D ]Y  }t        j0                  |      j2                  |t        j                  j4                  dz  z
  k  sD	 t        j6                  |       [  |t        j>                  |      v r$t;        j@                  dt'               d|       yt;        j<                  dt'               d|       yc c}w # t8        $ r%}t;        j<                  d|d|       Y d}~d}~ww xY w)z: Makes a backup of db, removes all but the last 5 backups r`   okr*   z.corruptnotify_actionon_plexpydbcorruptztautulli.backup-{}{}.sched.dbztautulli.backup-{}{}.dbr!   z	.sched.dbiQ z&Tautulli Database :: Failed to delete z from the backup folder: Nz,Tautulli Database :: Successfully backed up z to Tz&Tautulli Database :: Failed to backup F)!r`   r   NOTIFY_QUEUEputr@   r   nowCONFIG
BACKUP_DIRrF   r   rB   existsmakedirsr:   r   r   shutilcopyfiler   rollbacktimewalkendswithstatst_mtimeBACKUP_DAYSrG   OSErrorr   r   listdirrc   )cleanup	scheduler	integritycorruptbackup_filebackup_folderbackup_file_fprK   r   rootdirsfilesfdb_filesfile_r   s                   r	   r9   r9   N  s    !"#45=IG2F GH5<<W[[]GT/66w{{}gNMM,,MWW\\-=N 77>>-(
M"		BMM+,
OOKM>2MM 9iik!#!7 	yD$7<X!

;@WT1-XHX! y775>**S6==3L3Lu3T-TTy		%(y	y bjj//{}^ijkXcde Y
 # ynsuv%wxxys$   J "J J	J3J..J3c                  ~    t         j                  j                  syt        t         j                  j                        S Nr   )r   r   CACHE_SIZEMBintrg   r   r	   get_cache_sizer   ~  s'    ==%%v}}))**r   c                 Z    i }t        | j                        D ]  \  }}||   ||d   <    |S r   )	enumeratedescription)cursorr   didxcols        r	   dict_factoryr     s=    
Af001 SH#a&	 Hr   c                   >    e Zd Zd	dZd
dZd	dZd	dZd Zd Zd Z	y)r:   Nc                    t        |      | _        t        j                  | j                  d      | _        | j                  j                  dt        j                  j                  z         | j                  j                  dt        j                  j                  z         | j                  j                  dt               dz  z         t        | j                  _        y )Nr   r   zPRAGMA synchronous = %szPRAGMA journal_mode = %szPRAGMA cache_size = -%si   )r   r   r   r   r   r   r   r   SYNCHRONOUS_MODEJOURNAL_MODEr   r   row_factory)selfr   s     r	   __init__zMonitorDatabase.__init__  s    #H-!//$--D 9FMM<Z<Z Z[ :V]]=W=W WX 9^=MPT=T UV&2#r   c                 J   |y t         5  d }d}|dk  r=	 | j                  5 }||j                  |      }n|j                  ||      }d d d        	 |cd d d        S # 1 sw Y   xY w# t        j                  $ re}t        |      }d|v sd|v r1t        j                  d|       |dz  }t        j                  d       nt        j                  d|        Y d }~n6d }~wt        j                  $ r}t        j                  d||        d }~ww xY w|dk  r# 1 sw Y   y xY w)	Nr      zunable to open database filezdatabase is lockedz'Tautulli Database :: Database Error: %sr6   z'Tautulli Database :: Database error: %sz3Tautulli Database :: Fatal Error executing %s :: %s)db_lockr   r   r   r   rs   r   r7   r   sleepr   r   )r   r}   argsreturn_last_id
sql_resultattemptsrV   r   s           r	   r?   zMonitorDatabase.action  s)   = 	JHQ, @A<)*5)9J)*5$)?J	@   9	 	@ @ // AA5:>RVW>W$MqQ A

1%NPQR &
 ,, LL!VX]_`a) Q,		 	sX   
DA('AA(DA%	!A((D;ACDD1D		DDD"c                 Z    | j                  ||      j                         }||d gk(  rg S |S r   )r?   fetchallr   r}   r   sql_resultss       r	   rA   zMonitorDatabase.select  s5    kk%.779+$"7Ir   c                 X    | j                  ||      j                         }||dk(  ri S |S )Nr*   )r?   fetchoner   s       r	   r;   zMonitorDatabase.select_single  s3    kk%.779+"3Ir   c                    d}| j                   j                  }d }d|z   dz   dj                   ||            z   dz   dj                   ||            z   }| j                  |t	        |j                               t	        |j                               z          | j                   j                  |k(  rd}d	|z   d
z   dj                  t	        |j                               t	        |j                               z         z   dz   dz   dj                  dgt        t	        |j                               t	        |j                               z         z        z   dz   }	 | j                  |t	        |j                               t	        |j                               z          |S |S # t        j                  $ r t        j                  d||       Y |S w xY w)Nupdatec                 2    | D cg c]  }|dz   	 c}S c c}w )Nz = ?rg   )my_dictxs     r	   <lambda>z(MonitorDatabase.upsert.<locals>.<lambda>  s    '%BQa&j%B %Bs   zUPDATE z SET r2   z WHERE z AND insertzINSERT INTO z ()z	 VALUES (rq   z.Tautulli Database :: Queries failed: %s and %s)r   total_changesrB   r?   rt   valueskeysry   r   IntegrityErrorr   r8   )	r   rP   
value_dictkey_dict
trans_typechanges_before
gen_paramsupdate_queryinsert_querys	            r	   upsertzMonitorDatabase.upsert  s   
66B
 :-7$))JzDZ:[[ !#*<<
80D#EF 	L$z'8'8':";d8??CT>U"UV??((N:!J+d2TYYtJOODU?VY]^f^k^k^mYn?n5ooruu"iiD9J4KdS[S`S`SbNc4c0d(defhkl jL$z/@/@/B*Cd8??K\F]*]^
 z	 )) jLl\hi 	js   AF! !*GGc                 P    | j                  d      }|r|j                  dd       S y )Nz%SELECT last_insert_rowid() AS last_id)r}   last_id)r;   r>   )r   r_   s     r	   last_insert_idzMonitorDatabase.last_insert_id  s/    ##*Q#R::i.. r   c                 8    | j                   j                          y r   )r   r   )r   s    r	   __del__zMonitorDatabase.__del__  s    r   r   )NF)
__name__
__module____qualname__r   r?   rA   r;   r   r   r   rg   r   r	   r:   r:     s&    	3 D4/ r   r:   r   )NNF)FF)!rF   r   r   	threadingr   r   r   r   r   Lockr   r   r
   r   r\   r`   rd   rh   rk   rn   r~   r   r   r   rE   r   r   r   r9   r   r   objectr:   rg   r   r	   <module>r     s     
        
)..

.Rj#
)
"
0
<
<OQ
-`+c f c r   