""" Simple Text Archive Minimal text management with searchable table and SQLite persistence. """ import gradio as gr import sqlite3 import pandas as pd import os DB_PATH = "simple_archive.db" def init_db(): conn = sqlite3.connect(DB_PATH) cursor = conn.cursor() cursor.execute( """ CREATE TABLE IF NOT EXISTS items ( id INTEGER PRIMARY KEY AUTOINCREMENT, category TEXT NOT NULL, section TEXT NOT NULL, title TEXT NOT NULL, content TEXT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) """ ) cursor.execute("CREATE INDEX IF NOT EXISTS idx_category ON items(category)") cursor.execute("CREATE INDEX IF NOT EXISTS idx_section ON items(section)") cursor.execute("CREATE INDEX IF NOT EXISTS idx_title ON items(title)") conn.commit() conn.close() def add_item(category, section, title, content): if not all([category, section, title, content]): return "❌ All fields required!", load_table("", "", "") conn = sqlite3.connect(DB_PATH) cursor = conn.cursor() cursor.execute( "INSERT INTO items (category, section, title, content) VALUES (?, ?, ?, ?)", (category.strip(), section.strip(), title.strip(), content.strip()), ) conn.commit() conn.close() return "✅ Added!", load_table("", "", "") def load_table(category_filter, section_filter, title_filter): conn = sqlite3.connect(DB_PATH) query = """ SELECT id, category, section, title, content, datetime(updated_at, 'localtime') as updated FROM items WHERE 1=1 """ params = [] if category_filter: query += " AND category LIKE ?" params.append(f"%{category_filter}%") if section_filter: query += " AND section LIKE ?" params.append(f"%{section_filter}%") if title_filter: query += " AND title LIKE ?" params.append(f"%{title_filter}%") query += " ORDER BY updated_at DESC" df = pd.read_sql_query(query, conn, params=params) conn.close() if not df.empty: df["content"] = df["content"].apply( lambda x: (x[:100] + "...") if isinstance(x, str) and len(x) > 100 else x ) return df def update_item(item_id, category, section, title, content): if item_id is None or item_id == "": return "❌ Select an item first!", load_table("", "", "") if not all([category, section, title, content]): return "❌ All fields required!", load_table("", "", "") conn = sqlite3.connect(DB_PATH) cursor = conn.cursor() cursor.execute( """ UPDATE items SET category=?, section=?, title=?, content=?, updated_at=CURRENT_TIMESTAMP WHERE id=? """, (category.strip(), section.strip(), title.strip(), content.strip(), int(item_id)), ) conn.commit() conn.close() return "✅ Updated!", load_table("", "", "") def delete_item(item_id): if item_id is None or item_id == "": return "❌ Select an item first!", load_table("", "", "") conn = sqlite3.connect(DB_PATH) cursor = conn.cursor() cursor.execute("DELETE FROM items WHERE id=?", (int(item_id),)) conn.commit() conn.close() return "✅ Deleted!", load_table("", "", "") def load_item_by_id(item_id): if item_id is None or item_id == "": return "", "", "", "" conn = sqlite3.connect(DB_PATH) cursor = conn.cursor() cursor.execute( "SELECT category, section, title, content FROM items WHERE id=?", (int(item_id),), ) result = cursor.fetchone() conn.close() if result: return result[0], result[1], result[2], result[3] return "", "", "", "" def get_char_count(text): return f"📊 {len(text)} chars" if text else "📊 0 chars" def get_stats(): if not os.path.exists(DB_PATH): return "💾 Database: not created yet" conn = sqlite3.connect(DB_PATH) cursor = conn.cursor() cursor.execute("SELECT COUNT(*) FROM items") total = cursor.fetchone()[0] cursor.execute("SELECT COUNT(DISTINCT category) FROM items") categories = cursor.fetchone()[0] conn.close() file_size_kb = os.path.getsize(DB_PATH) / 1024 return f"💾 Database: {total} items, {categories} categories ({file_size_kb:.1f} KB)" def handle_table_select(df: pd.DataFrame, evt: gr.SelectData): if df is None or len(df) == 0: return None row_idx = evt.index[0] try: return int(df.iloc[row_idx, 0]) except Exception: return None # Initialize init_db() # UI with gr.Blocks(title="Text Archive") as app: gr.Markdown("# 📝 Simple Text Archive") with gr.Row(): db_stats = gr.Markdown(get_stats()) download_btn = gr.DownloadButton("💾 Download DB", value=DB_PATH, size="sm", scale=0) gr.Markdown("---") # Add Form with gr.Row(): with gr.Column(scale=3): gr.Markdown("### ➕ Add New Item") with gr.Row(): add_category = gr.Textbox(label="Category", placeholder="e.g., Python, Notes, SQL", scale=1) add_section = gr.Textbox(label="Section", placeholder="e.g., Functions, Snippets", scale=1) add_title = gr.Textbox(label="Title", placeholder="Short description", scale=2) add_content = gr.Textbox(label="Content", placeholder="Your text here...", lines=4) add_char_count = gr.Markdown("📊 0 chars") with gr.Row(): add_btn = gr.Button("➕ Add Item", variant="primary", size="sm") add_status = gr.Textbox(label="", container=False, show_label=False, interactive=False) gr.Markdown("---") # Search/Filter gr.Markdown("### 🔍 Search & Filter") with gr.Row(): search_category = gr.Textbox(label="🏷️ Category", placeholder="Filter...", scale=1) search_section = gr.Textbox(label="📁 Section", placeholder="Filter...", scale=1) search_title = gr.Textbox(label="📌 Title", placeholder="Filter...", scale=1) clear_btn = gr.Button("🔄 Clear", size="sm", scale=1) # Table gr.Markdown("### 📋 All Items. Click a row to load it into the edit form.") table = gr.Dataframe( value=load_table("", "", ""), label="", interactive=False, column_widths=["5%", "12%", "12%", "20%", "40%", "11%"], ) gr.Markdown("---") # Edit Form gr.Markdown("### ✏️ Edit Selected Item") with gr.Row(): edit_id = gr.Number(label="Item ID", precision=0, scale=1) load_id_btn = gr.Button("🔄 Load by ID", size="sm", scale=0) with gr.Row(): edit_category = gr.Textbox(label="Category", scale=1) edit_section = gr.Textbox(label="Section", scale=1) edit_title = gr.Textbox(label="Title", scale=2) edit_content = gr.Textbox(label="Content", lines=6, max_lines=20) edit_char_count = gr.Markdown("📊 0 chars") with gr.Row(): update_btn = gr.Button("💾 Save Changes", variant="primary", size="sm") delete_btn = gr.Button("🗑️ Delete", variant="stop", size="sm") edit_status = gr.Textbox(label="", container=False, show_label=False, interactive=False) # Events add_content.change(fn=get_char_count, inputs=add_content, outputs=add_char_count) edit_content.change(fn=get_char_count, inputs=edit_content, outputs=edit_char_count) add_btn.click( fn=add_item, inputs=[add_category, add_section, add_title, add_content], outputs=[add_status, table], ).then( fn=lambda: ("", "", "", "", "📊 0 chars"), outputs=[add_category, add_section, add_title, add_content, add_char_count], ).then( fn=get_stats, outputs=db_stats, ) for search_box in [search_category, search_section, search_title]: search_box.change( fn=load_table, inputs=[search_category, search_section, search_title], outputs=table, ) clear_btn.click( fn=lambda: ("", "", "", load_table("", "", "")), outputs=[search_category, search_section, search_title, table], ) load_id_btn.click( fn=load_item_by_id, inputs=edit_id, outputs=[edit_category, edit_section, edit_title, edit_content], ).then( fn=get_char_count, inputs=edit_content, outputs=edit_char_count, ) table.select( fn=handle_table_select, inputs=table, outputs=edit_id, ).then( fn=load_item_by_id, inputs=edit_id, outputs=[edit_category, edit_section, edit_title, edit_content], ).then( fn=get_char_count, inputs=edit_content, outputs=edit_char_count, ) update_btn.click( fn=update_item, inputs=[edit_id, edit_category, edit_section, edit_title, edit_content], outputs=[edit_status, table], ).then( fn=get_stats, outputs=db_stats, ) delete_btn.click( fn=delete_item, inputs=[edit_id], outputs=[edit_status, table], ).then( fn=lambda: (None, "", "", "", "", "📊 0 chars"), outputs=[edit_id, edit_category, edit_section, edit_title, edit_content, edit_char_count], ).then( fn=get_stats, outputs=db_stats, ) app.load(fn=get_stats, outputs=db_stats) if __name__ == "__main__": app.launch()