i$ddlZddlZddlZddlZddlmZddlmZmZm Z ddl m Z ddl m Z e e eZGddZGd d ZeZdd Zd Zed k(r[ed ej-dZer6edddk(r+ededej0dej2yedyy#e$rKZedeeededededededYdZ[ydZ[wwxYw)N)contextmanager)ListDictAny) load_dotenv) get_loggerc:eZdZdZd dedefdZdZdZdZ dZ y ) ConnectionPoolzv Thread-safe connection pool for pyodbc. Reuses connections to avoid connect/teardown overhead per query. connection_string pool_sizec||_||_tj||_t j |_d|_y)N)maxsizer) r r queueQueue_pool threadingLock_lock_created)selfr r s A/var/www/html/userprofiledev.eatanceapp.com/core/db_connection.py__init__zConnectionPool.__init__s6!2""'++i"@ ^^%  c@tj|jSN)pyodbcconnectr )rs r_create_connectionz!ConnectionPool._create_connections~~d4455rcd} |j}|jd|| |jSS#t$rYSwxYw#t$rW |jn#t$rYnwxYw|j cY|! |jS#t$rYSwxYwSwxYw#|! |jw#t$rYwwxYwwxYw)z^Verify connection is alive; replace with new one if not. Cursor is closed to avoid idle state.NSELECT 1)cursorexecuteclose Exceptionr)rconncurs r_check_connection_alivez&ConnectionPool._check_connection_alives ++-C KK #IIK! -    **, ,IIK  -IIK s"A ; AA B*A%$B*% A1.B*0A11B*B-B B%$B%)B**B--C1CC C C CCc8 |jj}|j|S#tj$rYnwxYwd}|j 5|j |jkr|xj dz c_d}dddn #1swYnxYw|rU |jS#t$r8|j 5|xj dzc_ddd#1swYxYwwxYw|jj}|j|S)NFrT) r get_nowaitr(rEmptyrrr rr%get)rr& should_creates rget_connectionzConnectionPool.get_connection2s ::((*D//5 5{{    ZZ}}t~~- " $ ZZ  ..00 ZZMMQ&M    zz~~++D11s?*-AA1BBB--C.C! C.!C* &C.c|y |jj|y#tj$rJ|j |j 5|xj dzc_dddYy#1swYYyxYwwxYw)Nr)r put_nowaitrFullr$rrrr&s rreturn_connectionz ConnectionPool.return_connectionHs\ <  # JJ ! !$ 'zz # JJL "  #s'!/A>A0&A>0A: 5A>:A>N)) __name__ __module__ __qualname____doc__strintrrr(r.r3rrr r s. ##6(2,#rr c beZdZdZdZfdZdZedZd de de de e e e ffd ZxZS) DatabaseConnectoraD Centralized MS SQL Database Connector with connection pooling. Uses pyodbc; connections are taken from and returned to a pool for reuse. Requires ``DB_USER`` and ``DB_PASSWORD`` (no defaults). Optional: ``DB_TRUST_SERVER_CERTIFICATE=true`` to set ``TrustServerCertificate=yes`` (dev/self-signed only). Nc|j)tt|||_d|j_|jS)NF) _instancesuperr=__new__ _initialized)cls __class__s rrAzDatabaseConnector.__new__^s7 == !"3SA#FCM).CMM &}}rc F|jrytjdd|_tjdd|_tjdd|_t tjdd}tjd }tjd }|t|js td | td t|j|_ ||_ tjd djj}|dv}d|j d|jd|jd|jd|jd |_ t|j||_d|_y)N DB_SERVER localhostDB_NAME profiling_db DB_DRIVERz{ODBC Driver 18 for SQL Server} DB_POOL_SIZE7DB_USER DB_PASSWORDzeDB_USER must be set to a non-empty value (no default credentials). Set it in the environment or .env.zHDB_PASSWORD must be set (no default). Set it in the environment or .env.DB_TRUST_SERVER_CERTIFICATE)1trueyesonzDRIVER=z;SERVER=z ;DATABASE=z;UID=z;PWD=z;TrustServerCertificate=yes;)r T)rBosgetenvserverdatabasedriverr:r9strip ValueErrorusernamepasswordlowerr r r)rr raw_user raw_password trust_raw trust_certs rrzDatabaseConnector.__init__dsu    ii [9  )^< ii -NO  .#67 99Y'yy/  3x=#6#6#85   Z H ++- $ II;R@FFHNNP "<< dkk]#kk]# '==/"==/"*  + $D$:$:iP  rc#BK|jj} | |j|jj |y#t$rY'wxYw# |jn#t$rYnwxYw|jj |wxYwwr)rr.rollbackr%r3r2s rr.z DatabaseConnector.get_connectionszz((* /J   JJ ( ( .       JJ ( ( .s\BAAB ABABB A10B1 A=:B<A==BBqueryparamsreturnc |j}|r#|jdddjnd}tj d|t ||j 5}|j} |j|||jr|jDcgc]}|d }}g} |jD]&} | jtt|| (tjdt | | |jcdddS|j!tj dg|jcdddScc}w#|jwxYw#1swYyxYw)zz Executes a query and returns results as a list of dictionaries. Uses a connection from the pool. Nrr?z(SQL execute: statement=%s param_count=%szFetched %s rowsz;Query executed (no results returned), connection committed.)rZsplitupperloggerdebuglenr.r"r# descriptionfetchallappenddictzipinfor$commit) rrerf_q_stmtr&r"columncolumnsresultsrows rr#zDatabaseConnector.executes@[[]02q!!$**, ?F T  "d[[]F uf-%%7=7I7IJ7IVvay7IGJ G%0tC,='>? 1KK 13w<@#  -# "$KKMLL!^_ -# "K -# "sC(F:,E.& E)2AE.F)&E.F)E..FFF )r;)r5r6r7r8r?rArrr.r9tuplerrrr# __classcell__)rDs@rr=r=SsUI $!L / / S % d38n9M rr=cLtjd}|r|djddk7r tdddlm}ddlm}|j5}|j|dj}d d d dk7r td y #1swYxYw) z Ensures both the pyodbc pool (orchestrator / raw SQL) and the SQLAlchemy engine (profiling tables) can reach the database. Raises on failure so the process can exit during application startup. zSELECT 1 AS connectivity_checkrconnectivity_checkrzGDatabase connectivity check failed (pyodbc): unexpected SELECT 1 result)text) get_enginer!NzKDatabase connectivity check failed (SQLAlchemy): unexpected SELECT 1 result) db_sessionr#r, RuntimeError sqlalchemyrstorage.db_reporr scalar_one)rzrrr&ones rverify_database_connectivityrs   !ABG gajnn%9:a? U   *    4ll4 +,779  ax Y   s &BB#ctS)z2Returns the centralized database session provider.)rr;rrget_db_sessionrs r__main__z$Testing DB Connection (with pool)...zSELECT 1 AS testtestu✅ Connection Successful!zConnected to: z on u)❌ Connection failed (Unexpected result)u❌ Connection Error: z Possible issues:z41. DB_USER and DB_PASSWORD must be set (no defaults)zL2. For dev/self-signed SQL: set DB_TRUST_SERVER_CERTIFICATE=true if requiredz%3. Check DB_SERVER, DB_NAME, and .envz&4. Ensure the ODBC Driver is installedz35. Verify network/firewall access to the SQL Server)rgN)rrUrr contextlibrtypingrrrdotenvr logger_configr r5rlr r=rrrprintr#rzrXrWr%er9r;rrrs5  %""% H B#B#JccN  .  z 01E$$%78 wqz&)Q. . / N:#6#6"7tJ E &s1vh/0 "# DE \] 56 67 CDDEs ,AB?5B??DAD  D