66 lines
1.7 KiB
JavaScript
66 lines
1.7 KiB
JavaScript
const Database = require('better-sqlite3');
|
|
const path = require('path');
|
|
const fs = require('fs');
|
|
|
|
const DATA_DIR = process.env.DATA_DIR || path.join(__dirname, '..', 'data');
|
|
if (!fs.existsSync(DATA_DIR)) {
|
|
fs.mkdirSync(DATA_DIR, { recursive: true });
|
|
}
|
|
|
|
const db = new Database(path.join(DATA_DIR, 'dispatch.db'));
|
|
db.pragma('journal_mode = WAL');
|
|
db.pragma('foreign_keys = ON');
|
|
|
|
db.exec(`
|
|
CREATE TABLE IF NOT EXISTS folders (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL,
|
|
sort_order INTEGER DEFAULT 0,
|
|
created_at TEXT DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS feeds (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
folder_id INTEGER REFERENCES folders(id) ON DELETE SET NULL,
|
|
title TEXT NOT NULL,
|
|
url TEXT NOT NULL UNIQUE,
|
|
site_url TEXT,
|
|
favicon TEXT,
|
|
description TEXT,
|
|
last_fetched_at TEXT,
|
|
last_error TEXT,
|
|
fetch_interval_minutes INTEGER DEFAULT 30,
|
|
sort_order INTEGER DEFAULT 0,
|
|
created_at TEXT DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS articles (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
feed_id INTEGER NOT NULL REFERENCES feeds(id) ON DELETE CASCADE,
|
|
guid TEXT NOT NULL,
|
|
title TEXT,
|
|
link TEXT,
|
|
author TEXT,
|
|
content TEXT,
|
|
summary TEXT,
|
|
image_url TEXT,
|
|
published_at TEXT,
|
|
fetched_at TEXT DEFAULT (datetime('now')),
|
|
is_read INTEGER DEFAULT 0,
|
|
is_starred INTEGER DEFAULT 0,
|
|
UNIQUE(feed_id, guid)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_articles_feed ON articles(feed_id);
|
|
CREATE INDEX IF NOT EXISTS idx_articles_published ON articles(published_at);
|
|
CREATE INDEX IF NOT EXISTS idx_articles_read ON articles(is_read);
|
|
CREATE INDEX IF NOT EXISTS idx_articles_starred ON articles(is_starred);
|
|
|
|
CREATE TABLE IF NOT EXISTS settings (
|
|
key TEXT PRIMARY KEY,
|
|
value TEXT
|
|
);
|
|
`);
|
|
|
|
module.exports = db;
|