o =f@@sddlZddlZddlZddlZddlZddlZddlZddl m Z m Z m Z ddl mZddlmZddlmZejejdeeZGdddeZGd d d eZGd d d ZeZd6ddZeded efddZded efddZ ddZ!dededed dfddZ"d7dede ed ed!ed"ed e e e eff d#d$Z#d%e e e efd ej$fd&d'Z%d8dede eded!ed)e&f d*d+Z'd9dede eded!ed-ef d.d/Z(d0ed e&fd1d2Z)d3ed e&fd4d5Z*dS):N)ListTupleUnion)contextmanager)urlparse)datetime)levelc@ eZdZdS) DatabaseErrorN__name__ __module__ __qualname__rr/home/user/app/SQLite_DB.pyr r c@r ) InputErrorNr rrrrrrrc@s:eZdZd ddZeddZd deded dfd d ZdS)DatabaseNcCs"|ptdd|_g|_d|_dS)NZDB_NAMEzmedia_summary.db )osgetenvdb_namepoolZ pool_size)selfrrrr__init__s zDatabase.__init__c cs*d}d}d}|dkrzwz'|jr|jntj|jdd}|V|j|WW|r2|j|dSdStjyf}z&dt|vrUt d|d|d8}t |nt d |WYd}~nd}~wt yx}zt d |d}~wwW|r|j|n |r|j|ww|dks t d ) NrF)Zcheck_same_threadzdatabase is lockedz Database is locked, retrying in z seconds...Database error: Unexpected error: z2Database is locked and retries have been exhausted)rpopsqlite3connectrappendZOperationalErrorstrloggerwarningtimesleepr Exception)rZ retry_countZ retry_delayconnerrrget_connection"sD       zDatabase.get_connectionrqueryparamsreturnc Cs~|1}z|}||||Wntjy,}z td|d|d}~wwWddS1s8wYdS)Nrz , Query: )r+cursorexecutecommitr Errorr )rr,r-r)r/r*rrr execute_query<s   "zDatabase.execute_query)N)r) r r rrrr+r#rr3rrrrrs   rr.cCs gd}|D]}t|qdS)N)as CREATE TABLE IF NOT EXISTS Media ( id INTEGER PRIMARY KEY AUTOINCREMENT, url TEXT, title TEXT NOT NULL, type TEXT NOT NULL, content TEXT, author TEXT, ingestion_date TEXT, prompt TEXT, summary TEXT, transcription_model TEXT ) z CREATE TABLE IF NOT EXISTS Keywords ( id INTEGER PRIMARY KEY AUTOINCREMENT, keyword TEXT NOT NULL UNIQUE ) a> CREATE TABLE IF NOT EXISTS MediaKeywords ( id INTEGER PRIMARY KEY AUTOINCREMENT, media_id INTEGER NOT NULL, keyword_id INTEGER NOT NULL, FOREIGN KEY (media_id) REFERENCES Media(id), FOREIGN KEY (keyword_id) REFERENCES Keywords(id) ) aU CREATE TABLE IF NOT EXISTS MediaVersion ( id INTEGER PRIMARY KEY AUTOINCREMENT, media_id INTEGER NOT NULL, version INTEGER NOT NULL, prompt TEXT, summary TEXT, created_at TEXT NOT NULL, FOREIGN KEY (media_id) REFERENCES Media(id) ) a3 CREATE TABLE IF NOT EXISTS MediaModifications ( id INTEGER PRIMARY KEY AUTOINCREMENT, media_id INTEGER NOT NULL, prompt TEXT, summary TEXT, modification_date TEXT, FOREIGN KEY (media_id) REFERENCES Media(id) ) zZ CREATE VIRTUAL TABLE IF NOT EXISTS media_fts USING fts5(title, content); zU CREATE VIRTUAL TABLE IF NOT EXISTS keyword_fts USING fts5(keyword); zM CREATE INDEX IF NOT EXISTS idx_media_title ON Media(title); zK CREATE INDEX IF NOT EXISTS idx_media_type ON Media(type); zO CREATE INDEX IF NOT EXISTS idx_media_author ON Media(author); z_ CREATE INDEX IF NOT EXISTS idx_media_ingestion_date ON Media(ingestion_date); zW CREATE INDEX IF NOT EXISTS idx_keywords_keyword ON Keywords(keyword); zc CREATE INDEX IF NOT EXISTS idx_mediakeywords_media_id ON MediaKeywords(media_id); zg CREATE INDEX IF NOT EXISTS idx_mediakeywords_keyword_id ON MediaKeywords(keyword_id); zb CREATE INDEX IF NOT EXISTS idx_media_version_media_id ON MediaVersion(media_id); )dbr3)Z table_queriesr,rrr create_tablesIsR r5keywordc Cs |}tr}|}z5|d|f|d|f|d}|d||ftd|d|| |WWdSt j y`}zt d|t d|d}~wt jy{}zt d|t d|d}~ww1swYdS) N3INSERT OR IGNORE INTO Keywords (keyword) VALUES (?))SELECT id FROM Keywords WHERE keyword = ?rz@INSERT OR IGNORE INTO keyword_fts (rowid, keyword) VALUES (?, ?) Keyword 'z ' added to keyword_fts with ID: z Integrity error adding keyword: zError adding keyword: )striplowerr4r+r/r0fetchonelogginginfor1r IntegrityErrorerrorr r2r6r)r/ keyword_idr*rrr add_keywords,    rCc Cs|}tW}|}z=|d|f|}|r?|d|f|d|df|d|dWWdSd|dWWdStj y`}zt d|d}~ww1sdwYdS) Nr8z&DELETE FROM Keywords WHERE keyword = ?z'DELETE FROM keyword_fts WHERE rowid = ?rr9z' deleted successfully.z ' not found.zError deleting keyword: ) r:r;r4r+r/r0r<r1r r2r rArrrdelete_keywords&     rDc  CsD|pd}|pd}|p d}|pd}|pd}|pd}|pd}|pd}|p#d}| p,td} t|s3d}|d vr;td | rEt| sEtd | sNtd} td |td |td|td|td|td|td|td|td| td|zt } | } | d} | d|f| } | r| d}td|| d|||| ftdnOtd| d|||||| |f| j}| D]$}|}| d|f| d|f| d}| d ||fq| d!|||f| d|||| f| t|||d"|d#d$| WdWS1sFwYWdStjyj}ztd%|td&|d}~wtjy}ztd'|td(|d}~wty}ztd)|td*|d}~ww)+NUnknownZUntitledzNo content availabledefaultzNo prompt availablezNo summary available%Y-%m-%dZ localhost)documentvideoZarticlezINSERT INTO media_fts (rowid, title, content) VALUES (?, ?, ?)zMedia 'z$' added successfully with keywords: z, zIntegrity Error: z,Integrity error adding media with keywords: z SQL Error: z"Error adding media with keywords: zUnexpected Error: r)rnowstrftime is_valid_urlr is_valid_dater=r>r4r+r/splitr0r<r$Z lastrowidr:r;r1add_media_versionjoinr r?r@r r2r()urltitle media_typecontentkeywordspromptsummarytranscription_modelauthoringestion_dater)r/Z keyword_listZexisting_mediamedia_idr6rBr*rrradd_media_with_keywordss          *3r]r\rWrXc CszBt3}|}|d|f|dpd}|d||d||tdf|WdWdS1s;wYWdSt j yV}zt d|d}~ww)Nz8SELECT MAX(version) FROM MediaVersion WHERE media_id = ?rz INSERT INTO MediaVersion (media_id, version, prompt, summary, created_at) VALUES (?, ?, ?, ?, ?) rz%Y-%m-%d %H:%M:%SzError adding media version: ) r4r+r/r0r<rrKrLr1r r2r )r\rWrXr)r/Zcurrent_versionr*rrrrP1s  & rPrr search_query search_fieldsrVpageresults_per_pagec Cs@|dkrtddd|dD}t}|}|d|}g}|r2|ddd|D|rHg} |D]} | dq8|d | d |} d | d } z.t|gt||} | | | ||f| }|sxWWdd S|WWdSt j y}zt d |d}~ww1swYdS)Nrz!Page number must be 1 or greater.cSs g|] }|r|qSr)r:r;).0r6rrr Js zsearch_db..rJz OR cSsg|]}d|dqS)z media_fts.z MATCH ?r)rbZfieldrrrrcQszkeyword_fts.keyword MATCH ?z AND a SELECT Media.url, Media.title, Media.type, Media.content, Media.author, Media.ingestion_date, Media.prompt, Media.summary FROM Media JOIN media_fts ON Media.id = media_fts.rowid JOIN MediaKeywords ON Media.id = MediaKeywords.media_id JOIN Keywords ON MediaKeywords.keyword_id = Keywords.id JOIN keyword_fts ON Keywords.id = keyword_fts.rowid WHERE z" LIMIT ? OFFSET ? No results found.zError executing query: )rrOr4r+r/r"rQtuplelenr0Zfetchallr r2r )r^r_rVr`rar)r/offsetZsearch_conditionsZkeyword_conditionsr6Z where_clauser,r-resultsr*rrr search_dbEs@      !rirhcCs(t|tr tStj|gdd}|S)N)ZURLZTitleZTypeZContentZAuthorzIngestion DateZPromptZSummary)columns) isinstancer#pd DataFrame)rhdfrrrformat_resultsqs roFdummyc Csts gtjddfSz)|stdt||||}t|}|jr+|tjdddfWS|tjddfWSttfyT}zt tjt |ddfWYd}~Sd}~ww)NF)visiblez"Please enter a valid search query.rdT)valuerq) Zsubmitgrupdater:rriroemptyr rlrmr#)r^r_r6r`rprhrnr*rrrsearch_and_display{s&rvresults_per_filec Cspzt|||||}t|}d|d}|j|ddd|WSttfy7}z t|WYd}~Sd}~ww)NZsearch_results_page_z.csvF)indexzResults exported to )riroZto_csvr rr#) r^r_r6r`rxrhrnfilenamer*rrr export_to_csvs  r{rRcCstdtj}t||duS)Nz^(?:http|ftp)s?://(?:(?:[A-Z0-9](?:[A-Z0-9-]{0,61}[A-Z0-9])?\.)+(?:[A-Z]{2,6}\.?|[A-Z0-9-]{2,}\.?)|localhost|\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}|\[?[A-F0-9]*:[A-F0-9:]+\]?)(?::\d+)?(?:/?|[/?]\S+)$)recompile IGNORECASEmatch)rRZregexrrrrMs rM date_stringcCs(z t|dWdStyYdSw)NrGTF)rstrptime ValueError)rrrrrNs   rN)r.N)rr)F)rrw)+r ZgradiorsZpandasrlr=rr|r&typingrrr contextlibrZ urllib.parserr basicConfigINFO getLoggerr r$r(r rrr4r5r#intrCrDr]rPrirmroboolrvr{rMrNrrrrs:    ) Ve4 ,$ $