add new sqlite persistent storage creation and migration

1 files changed, 49 insertions, 0 deletions

diff --git a/schema.sql b/schema.sql
new file mode 100644
index 0000000..5c2e2f0
--- /dev/null
+++ b/schema.sql
@@ -0,0 +1,49 @@
+CREATE TABLE IF NOT EXISTS news (
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    date TEXT NOT NULL,
+    identifier TEXT NOT NULL,
+    type TEXT,
+    timestamp INTEGER NOT NULL,
+    headline TEXT,
+    content TEXT NOT NULL,
+    url TEXT,
+    is_ai_summary INTEGER NOT NULL DEFAULT 0,
+    en_headline TEXT,
+    en_content TEXT
+);
+
+CREATE TABLE IF NOT EXISTS news_images (
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    news_id INTEGER NOT NULL,
+    image_url TEXT NOT NULL,
+    link_url TEXT,
+    FOREIGN KEY (news_id) REFERENCES news(id) ON DELETE CASCADE
+);
+
+CREATE TABLE IF NOT EXISTS summarization (
+    id VARCHAR(255) PRIMARY KEY,
+    headline TEXT NOT NULL,
+    content TEXT NOT NULL
+);
+
+CREATE TABLE IF NOT EXISTS translation (
+    id VARCHAR(255) PRIMARY KEY,
+    source TEXT NOT NULL,
+    result TEXT NOT NULL,
+    source_lang TEXT NOT NULL,
+    target_lang TEXT NOT NULL
+);
+
+CREATE TABLE IF NOT EXISTS wacplus (
+    id VARCHAR(255) PRIMARY KEY,
+    isNews INTEGER NOT NULL,
+    type TEXT NOT NULL
+);
+
+-- Indexes for common queries
+CREATE INDEX IF NOT EXISTS idx_news_date ON news(date);
+CREATE INDEX IF NOT EXISTS idx_news_identifier ON news(identifier);
+CREATE INDEX IF NOT EXISTS idx_news_timestamp ON news(timestamp);
+CREATE INDEX IF NOT EXISTS idx_news_type ON news(type);
+CREATE INDEX IF NOT EXISTS idx_news_is_ai_summary ON news(is_ai_summary);
+CREATE INDEX IF NOT EXISTS idx_news_images_news_id ON news_images(news_id);