ikFdZddlZddlZddlZddlZddlZddlZddlZddl Z ddl m Z e jdZ ejZGddZGddej ZGd d eZy) aImplementations to make access to SQLite databases a little more convenient. :py:obj:`SQLiteAppl` Abstract class with which DB applications can be implemented. :py:obj:`SQLiteProperties`: Class to manage properties stored in a database. Examplarical implementations based on :py:obj:`SQLiteAppl`: :py:obj:`searx.cache.ExpireCacheSQLite` : Cache that manages key/value pairs in a SQLite DB, in which the key/value pairs are deleted after an "expire" time. This type of cache is used, for example, for the engines, see :py:obj:`searx.enginelib.EngineCache`. :py:obj:`searx.favicons.cache.FaviconCacheSQLite` : Favicon cache that manages the favicon BLOBs in a SQLite DB. ---- N)loggersqlitedbcveZdZdZedddej fdZd dZe dej fdZ dZ y ) DBSessionzA *thead-local* DB sessionapp SQLiteApplreturncttdd i}|t_tjj|j}|||}|j S)zkReturns a thread local DB connection. The connection is only established once per thread. DBSession_mapN)getattr THREAD_LOCALr getdb_urlconn)clsrurl_to_sessionsessions /root/searxng/searx/sqlitedb.py get_connectzDBSession.get_connect*sR <$ 7 ?35N)7L &$0$>$>$B$B3::$N ?#hG||ctj|_||_d|_t t dd i}|t _|t j |jj<y)Nr )uuiduuid4r_connr r r r)selfrrs r__init__zDBSession.__init__8sQ#zz| "04 <$ 7 ?35N)7L &6: ""488??3rcPdtjjd|jjj d|jj d}|j5|jj|_tjd||jS)N[z ] DBSession: ()z%s --> created new connection) threadingcurrent_threadidentr __class____name__rrconnectrdebugrmsgs rrzDBSession.connBs)**,223=TXXEWEWE`E`Daabcgckckcrcrbsstu :: ))+DJ LL8# >zzrcr |j|jjyy#t$rYywxYwN)rclose Exception)rs r__del__zDBSession.__del__Ms< zz%   "&   s &* 66N)rr) r% __module__ __qualname____doc__ classmethodsqlite3 Connectionrrpropertyrr.rrrr'sV$ l w/A/A  ;g(( rrceZdZUdZiZeeefed<dZe ed< dddde jZ eed < d Z eed < ee dk7d d Zeeee zezdzfed< defdZdZde j$fdZde j$fdZde j$fdZede j$fdZde j$defdZde j$fdZy)rzAbstract base class for implementing convenient DB access in SQLite applications. In the constructor, a :py:obj:`SQLiteProperties` instance is already aggregated under ``self.properties``.DDL_CREATE_TABLES DB_SCHEMAz single-threadz multi-thread serialized)rr9SQLITE_THREADING_MODEWALSQLITE_JOURNAL_MODEr)check_same_threadcached_statementsNSQLITE_CONNECT_ARGSrc`||_t||_d|_|j yNF)rSQLiteProperties properties _init_done_compatibilityrrs rrzSQLiteAppl.__init__s(! ,= 3.35)) r=_DBr! active_countrerrorwarningr3sqlite_version_infocriticalsqlite_versionr(s rrHzSQLiteAppl._compatibilitys  % % 526DH343M3M2NO\\ %%'!+ S!s#  & &' 1 OOVX_XnXn  2rr ctj|jfi|j}|j d|j |j ||S)NzPRAGMA journal_mode=)r3r4rrBexecuter?register_functionsrrs r_connectzSQLiteAppl._connectsO!!$++J1I1IJ +D,D,D+EFG % rc tjdkr|jjdddt j j d|jjd|jd|jd|j }tj||j5}|j|ddd|S#1swYSxYw) acCreates a new DB connection (:py:obj:`SQLITE_CONNECT_ARGS`). If not already done, the DB schema is set up. The caller must take care of closing the resource. Alternatively, :py:obj:`SQLiteAppl.DB` can also be used (the resource behind `self.DB` is automatically closed when the process or thread is terminated). )r< autocommitNr] rz) z // )sys version_inforBpopr!r"r#r$r%rr?rr'rWinit)rr)rs rr&zSQLiteAppl.connects   g %  $ $ ( (t < ((*001DNN4K4K3LAdkk][(()d.F.F-G I   S ]]_  IIdO    s 4CCrc0|jddddy)aCreate user-defined_ SQL functions. ``REGEXP(, )`` : 0 | 1 `re.search`_ returns (int) 1 for a match and 0 for none match of ```` in ````. .. code:: sql SELECT '12' AS field WHERE REGEXP('^[0-9][0-9]$', field) -- 12 SELECT REGEXP('[0-9][0-9]', 'X12Y') -- 1 SELECT REGEXP('[0-9][0-9]', 'X1Y') -- 0 .. _user-defined: https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.create_function .. _deterministic: https://sqlite.org/deterministic.html .. _re.search: https://docs.python.org/3/library/re.html#re.search regexpc6tj||rdSdS)Nr9r)research)xys rz/SQLiteAppl.register_functions..sBIIaOqQRrT) deterministicN)create_functionrVs rrUzSQLiteAppl.register_functionss, Xq*Rbfgrc|jdk(rtj|}ntj|}|j||S)a Provides a DB connection. The connection is a *singleton* and therefore well suited for read access. If :py:obj:`SQLITE_THREADING_MODE` is ``serialized`` only one DB connection is created for all threads. .. note:: For dedicated `transaction control`_, it is recommended to create a new connection (:py:obj:`SQLiteAppl.connect`). .. _transaction control: https://docs.python.org/3/library/sqlite3.html#sqlite3-controlling-transactions r;)r=rrr_rVs rDBz SQLiteAppl.DBsF$  % % 5((.D((.D $ rc|jryd|_tjd|j|jj ||j d}||5|j |dddyt|}||jk7r%tjd|jd|tjd|y#1swYyxYw) aQInitializes the DB schema and properties, is only executed once even if called several times. If the initialization has not yet taken place, it is carried out and a `True` is returned to the caller at the end. If the initialization has already been carried out in the past, `False` is returned. FTz init DB: %sr:NzExpected DB schema vz, DB schema is vzDB_SCHEMA = %s) rGrr'rrFr_ create_schemaintr:r3 DatabaseError)rrvers rr_zSQLiteAppl.init?s ?? ]DKK0 T"ook* ; )""4( ) c(Cdnn$++Z^ZhZhjm,noo LL)3 / )s %CCctjd|jjd|j|jjdd|5|j j D]6\}}|j||jjd|d|8 dddy#1swYyxYw)Nzcreate schema ..r:LAST_MAINTENANCEzTable z created)rr'rFsetr:r8itemsrT)rr table_namesqls rrnzSQLiteAppl.create_schema[s '( K8 .3  O#'#9#9#?#?#A O C S!##fZL$A:N O O O Os AB88C)r%r/r0r1r8dictstr__annotations__r:ror3 threadsafetyr=r?boolrBrrHr4rWr&rUr5rlr_rnr6rrrr[sD5)+tCH~*IsC   "!--"/3/  %$""7<"GH 8c#c'$,t"334:xs (',, ++*hw'9'9h0$G&&$$L++8O'"4"4OrrceZdZUdZdZeed<dZeed< dZeed<dZ eed <d Z eed <d Z eed <dZ eed<e ejZe eeezezdzfed<defdZdej(defdZd!dedej.dej.fdZdedeezfdZdedefdZd!dedej.fdZd"dededefdZdej(fdZdefd Zy)#rEaWSimple class to manage properties of a DB application in the DB. The object has its own DB connection and transaction area. .. code:: sql CREATE TABLE IF NOT EXISTS properties ( name TEXT, value TEXT, m_time INTEGER DEFAULT (strftime('%s', 'now')), PRIMARY KEY (name)) r>r?zCREATE TABLE IF NOT EXISTS properties ( name TEXT, value TEXT, m_time INTEGER DEFAULT (strftime('%s', 'now')), -- last modified (unix epoch) time in sec. PRIMARY KEY (name))DDL_PROPERTIESz+SELECT value FROM properties WHERE name = ?SQL_GETz,SELECT m_time FROM properties WHERE name = ? SQL_M_TIMEzINSERT INTO properties (name, value) VALUES (?, ?) ON CONFLICT(name) DO UPDATE SET value=excluded.value, m_time=strftime('%s', 'now')SQL_SETz%DELETE FROM properties WHERE name = ? SQL_DELETEzGSELECT name FROM sqlite_master WHERE type='table' AND name='properties'SQL_TABLE_EXISTSNrBrc@||_d|_|jyrD)rrGrHrIs rrzSQLiteProperties.__init__s! % rrr c|jryd|_tjd|j|j |j }|j |j|y)z2Initializes DB schema of the properties in the DB.FTzinit properties of DB: %s)rGrr'rrTrfetchonern)rrress rr_zSQLiteProperties.initsY ?? 0$++>ll4001 <<> !   t $rnamedefaultc~|jj|j|fj}||S|dS)z_Returns the value of the property ``name`` or ``default`` if property not exists in DB.r)rlrTrr)rrrrs r__call__zSQLiteProperties.__call__s:ggoodllTG4==? ;N1v rvaluec|j5|jj|j||fdddy#1swYyxYw)zuSet ``value`` of property ``name`` in DB. If property already exists, update the ``m_time`` (and the value).N)rlrTr)rrrs rruzSQLiteProperties.sets:WW 9 GGOODLL4- 8 9 9 9s )?Ac|j5|jj|j|f}ddd|jS#1swYjSxYw)z$Delete of property ``name`` from DB.N)rlrTrrowcount)rrcurs rdeletezSQLiteProperties.deletesG WW <''//$//D7;C <|| <||s (A  Ac|jjd|f}|j}||S|jDcgc]}|d }}t t ||Scc}w)z\Returns the DB row of property ``name`` or ``default`` if property not exists in DB.z'SELECT * FROM properties WHERE name = ?r)rlrTr descriptionryzip)rrrrrowcolumn col_namess rrzSQLiteProperties.rowsbggooG$Qlln ;N-0__=6VAY= =C 3'((>s A#c|jj|j|f}|j}||St |dS)z(Last modification time of this property.r)rlrTrrro)rrrrrs rm_timezSQLiteProperties.m_times?ggoodoow7lln ;N3q6{rch|5|j|jdddy#1swYyxYwr+)rTrrVs rrnzSQLiteProperties.create_schemas,  . LL,, - . . .s(1c g}|jjdD]Q}|\}}}tjj|j d}|j d|d|dd|Sdj |S)Nz*SELECT name, value, m_time FROM propertiesz%Y-%m-%d %H:%M:%Sz[last modified: r[20sz:  )rlrTdatetime fromtimestampstrftimeappendjoin)rlinesrrrrs r__str__zSQLiteProperties.__str__s77??#OP KC"% D%&&44V<EEFYZF LL+F82d3Zr%I J Kyyrr+)r)r%r/r0r1r?rzr{rrrrrrryrrBror}rr3r4r_tAnyrrurrrrnrr6rrrErEfs[  %$NC:@GS@DJD D S >J= 4c?C:CaCa>bc39t#3d#::;bs ++  S155AEE99C#I933 ) )aee )3S.'"4"4.  rrE)r1typingrabcrrdr3r\r!rsearxrgetChildlocalr rABCrrEr6rrrss*      $y 11hHOHOVn zn r