sU%h3LddlZddlmZddlmZmZmZmZmZm Z GddZ y)N)asynccontextmanager)ListDictAnyTupleOptionalUnionc eZdZdefdZedZdZdedeeefde fdZ ded e ee fde efd Z dded eeefd eeeefde fdZ dded eeeefdeedee de ef dZded eeefd eeefde fdZded eeefde fdZded eeefde fdZdded eeeefde fdZddedede efdZy )DatabaseManagerdb_namec||_yN)r )selfr s MC:\Users\james\Desktop\root\other\Misc\Work\ledger\server\database\handler.py__init__zDatabaseManager.__init__s  cTKtj|j4d{}tj|_ ||j d{dddd{y7H7#|j d{7wxYw7'#1d{7swYyxYww)z/Async context manager for database connections.N) aiosqliteconnectr Row row_factorycloserconns rget_connectionzDatabaseManager.get_connection s$$T\\2 # #d(}}D  # jjl""  # # # #djjl""  # # # #sz$B(A/B(BA3BA1B B()B*B(1B3BB BBB(B%B B%!B(cK|j4d{}|jdd{|jdd{|jdd{|jdd{|jd{dddd{y77z7c7L7577#1d{7swYyxYww)z7Create database and initial tables if they don't exist.Nan CREATE TABLE IF NOT EXISTS visitors ( id INTEGER PRIMARY KEY AUTOINCREMENT, ip TEXT DEFAULT NULL, phrase TEXT DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) a] CREATE TABLE IF NOT EXISTS api_keys ( key_id INTEGER PRIMARY KEY AUTOINCREMENT, ip TEXT NOT NULL, api_key VARCHAR(64) UNIQUE, active BOOLEAN DEFAULT TRUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) z CREATE TABLE IF NOT EXISTS config ( id INTEGER PRIMARY KEY CHECK (id = 1), enabled INTEGER DEFAULT TRUE ) z INSERT OR IGNORE INTO config (rowid, enabled) SELECT 1, 1 WHERE NOT EXISTS (SELECT 1 FROM config) )rexecutecommitrs rinitialize_databasez#DatabaseManager.initialize_databases&&(  D,,   ,,   ,,    ,,   ++-  ;         ;    sC B)C B7B+B7 B- B7"B/#B7;B1<B7B3B7 C #B5$C +B7-B7/B71B73B75C 7C =C >C C  table_namecolumnsreturnc K |j4d{}dj|jDcgc] \}}|d|c}}}d|d|d}|j|d{|j d{ dddd{y7cc}}w767 7#1d{7swYyxYw#t j $r}td|Yd}~y d}~wwxYww) z*Create a new table with specified columns.N,  zCREATE TABLE IF NOT EXISTS  ()TzError creating table: F)rjoinitemsrrrErrorprint) rr r!rcoldtype columns_defqueryes r create_tablezDatabaseManager.create_table4s **,  "iiGMMO(\jc5C5%)9(\] 5j\K=PQRll5)))kkm##    (\)#        *1#. / sC1CB CB.B" %B.1B(2B. B* B. CB,CC1 C"B.*B.,C.C4B7 5C<C?C1CC.C)$C1)C..C1 search_valuecK |j4d{}|jd|dd{}|2cgc3d{}|d76776ncc}w}}|jd{7|sgcdddd{7St|tr(|Dcgc]}|d ncc}w}}|gt |z} n1|Dcgc]}|d ncc}w}}|D cgc]} d|d ncc} w} } dj |} d |d | } |j| | d{7}|2cgc3d{7}t|6ncc}w} }|jd{7| cdddd{7S#1d{7swYyxYw#tj$r}td |gcYd}~Sd}~wwxYww) a/ Search all columns in a table for a given value (string or int). Args: table_name: The table to search search_value: The value to look for in any column Returns: List of rows (as dicts) where the value appears in any column NzPRAGMA table_info(r' = ?z LIKE ?%z OR SELECT * FROM  WHERE zError searching table: ) rrr isinstanceintlenr(dictrr*r+)rr r2rcursorrowr!r, conditionsparams_ where_clauser/rowsr0s r search_tablezDatabaseManager.search_tableAs **,  #||.@ A,NOO39::C3q6 O::::lln$$    lC0:A!B3SE,!B!BJ!B*^c'l:F=D!EcSE/!E!EJ!E;BCa,q1CCFC%{{:6 ( GL>J#||E6:::39::CS :::lln$$+     .  +A3/ 0I sHGFAFE<AE<AAA AAFE<AAE<1A42E<: FB F G E<! B.-E< C E<! C/.6E<$D'% E<.E0E4D7 5E9EE<"E%#E<) F5E86F;G<FF F F GFF>$F93F>4G9F>>GNdata conditioncK |j4d{}|r(dj|jDcgc]}|d c}}t|j }d|d|}|j ||d{} | j d{} | ddkD} | rdj|jD cgc]} | d c} } t|j |z}d |d | d|}|j ||d{|jd{ dddd{y dj|j}dj|Dcgc]}d c}}t|j }d |d|d|d}|j ||d{|jd{ dddd{y 7cc}w7q7\cc} w777cc}w7L767'#1d{7swYyxYw#tj$r}td|Yd}~yd}~wwxYww)a Universal insert/update function. If the record matching the condition exists, it will be updated; otherwise, a new record will be inserted. Args: table_name: The table to operate on data: The data to insert or update condition: Key-value pairs to identify existing records. If None, will always insert. Returns: bool: Success or failure N AND r5SELECT COUNT(*) as count FROM r8countrr$UPDATE  SET T?z INSERT INTO r&z ) VALUES (r'zError in upsert operation: F) rr(keystuplevaluesrfetchonerrr*r+)rr rErFrkrB where_valuesr/r=result record_existsr, set_clause update_valuesr!rA placeholdersrPr0s rupsertzDatabaseManager.upserths* **,  #*<rCr0s rget_rowszDatabaseManager.get_rowssC  **,  ( 5#*<8E9E>EEcK |j4d{}dj|jDcgc]}|d c}}dj|jDcgc]}|d c}}t|j t|j z} d|d|d|} |j | | d{|j d{ dddd{y7cc}wcc}w7:7$7#1d{7swYyxYw#tj$r} td | Yd} ~ y d} ~ wwxYww) a6 Update rows in the specified table based on condition. Args: table_name: The table to update data: Key-value pairs of fields to update condition: Key-value pairs for WHERE clause Returns: Success or failure Nr$r5rHrKrLr8TzError updating rows: F rr(rNrOrPrrrr*r+) rr rErFrr,rVrRrBrPr/r0s r update_rowszDatabaseManager.update_rowss+ **,  !YY 'L3%t 'LM &||AQ,RAs$Z,RS t{{}-i6F6F6H0II!*U:,gl^Tll5&111kkm##   'L,R 2#      )!- . sED&C?D&D D $D* D 6ADD D(D )D. D&9D:D&>E?D& D DD&D#D D#D&"E#D&&E9E E EEcK |j4d{}dj|jDcgc]}|d c}}t|j }d|d|}|j ||d{|j d{ dddd{y7cc}w7577#1d{7swYyxYw#tj$r}td|Yd}~yd}~wwxYww) a Delete rows from the specified table based on condition. Args: table_name: The table to delete from condition: Key-value pairs for WHERE clause Returns: Success or failure NrHr5z DELETE FROM r8TzError deleting rows: Fr`) rr rFrrRrBr@r/r0s r delete_rowszDatabaseManager.delete_rowss **,  &||AQ,RAs$Z,RS y//12&zl',Hll5&111kkm##   ,R2#       )!- . sDCB2CB? B4 =B?B9B?B;B?! C,B=-C1D2C4B?;B?=C?CC C CDCC?'C:5D:C??DcJK |j4d{}dj|jDcgc]}|d c}}t|j }d|d|}|j ||d{}|j d{} |jd{| ddkDcdddd{S7cc}w7T7>7(7#1d{7swYyxYw#tj$r} td| Yd} ~ y d} ~ wwxYww) a  Check if a record exists based on condition. Args: table_name: The table to check condition: Key-value pairs for WHERE clause Returns: True if record exists, False otherwise NrHr5rIr8rJrzError checking existence: F rr(rNrOrPrrQrrr*r+) rr rFrrRrBr@r/r=rTr0s rexistszDatabaseManager.existss **, + +&||AQ,RAs$Z,RS y//128 GL>Z#||E6::%00lln$$g* + + +,R;0$ + + + +  .qc2 3 sD#C5CC5C  C =C CC CC 3C4 C ? C5 C C5D#C5C C C C5 C2&C) 'C2.C51D#2C55D DD#D  D#cVK |j4d{}d|}d}|rQdj|jDcgc]}|d c}}t|j }|d|z }|j ||d{}|j d{} |jd{| dcdddd{S7cc}w7Q7;7%7#1d{7swYyxYw#tj$r} td| Yd} ~ y d} ~ wwxYww) z Count records based on condition. Args: table_name: The table to count from condition: Key-value pairs for WHERE clause Returns: Number of matching records NrIr]rHr5r8rJzError counting records: rre) rr rFrr/r@rRrBr=rTr0s rrJzDatabaseManager.counts **, ' '8 E#*<rCr0s r execute_queryzDatabaseManager.execute_query/s **,  #||E6::;;=&&(33H=7=>>DI++-''   :?>>D> ,,.((   (      +A3/ 0I sD/C?B*C?C*B,5C*(B2*B0.B. /B02 B2?C*C&C* C?$C(%C?)D/*C?,C*.B00B21C* C C* C?C" C?%D/&C*(C?*C<0C3 1C<8C?;D/<C??D,D'!D,"D/'D,,D/r)NNN)r])__name__ __module__ __qualname__strrrrrrboolr1r r:rrDrrrYr^rarcrfrJrrmr]rrr r s## B S 4S> d %S%c3h%TXY]T^%P;?,s,$sCx.,'S#X7,CG,^=A04-1''!)$sCx.!9' ( '&c]'7;4j'RCtCH~$(cN7;:CDcNt2stCH~$6chtCH~6NZ]>eT$Zrr ) r contextlibrtypingrrrrrr r r]rrrus*::CCr