feat: add initial schema migration files

2 files changed, 46 insertions, 0 deletions

diff --git a/internal/db/migrations/000001_init.down.sql b/internal/db/migrations/000001_init.down.sql
new file mode 100644
index 0000000..0a59d39
--- /dev/null
+++ b/internal/db/migrations/000001_init.down.sql
@@ -0,0 +1,5 @@
+DROP TABLE IF EXISTS pages_fts;
+DROP TABLE IF EXISTS settings;
+DROP TABLE IF EXISTS redirects;
+DROP TABLE IF EXISTS posts;
+DROP TABLE IF EXISTS pages;
diff --git a/internal/db/migrations/000001_init.up.sql b/internal/db/migrations/000001_init.up.sql
new file mode 100644
index 0000000..a28d2f8
--- /dev/null
+++ b/internal/db/migrations/000001_init.up.sql
@@ -0,0 +1,41 @@
+CREATE TABLE pages (
+    id         INTEGER PRIMARY KEY AUTOINCREMENT,
+    path       TEXT NOT NULL UNIQUE,
+    html_path  TEXT NOT NULL,
+    title      TEXT NOT NULL DEFAULT '',
+    date       TEXT DEFAULT '',
+    tags       TEXT DEFAULT '[]',
+    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
+);
+CREATE INDEX idx_pages_path ON pages(path);
+CREATE INDEX idx_pages_date ON pages(date);
+
+CREATE TABLE posts (
+    id         INTEGER PRIMARY KEY AUTOINCREMENT,
+    slug       TEXT NOT NULL UNIQUE,
+    title      TEXT NOT NULL DEFAULT '',
+    date       TEXT DEFAULT '',
+    tags       TEXT DEFAULT '[]',
+    draft      INTEGER NOT NULL DEFAULT 0,
+    blocks     TEXT NOT NULL DEFAULT '[]',
+    updated_at INTEGER NOT NULL DEFAULT (cast(strftime('%s','now') * 1000000 as integer))
+);
+CREATE INDEX idx_posts_slug ON posts(slug);
+CREATE INDEX idx_posts_date ON posts(date);
+
+CREATE TABLE redirects (
+    from_slug TEXT PRIMARY KEY,
+    to_slug   TEXT NOT NULL
+);
+
+CREATE TABLE settings (
+    key   TEXT PRIMARY KEY,
+    value TEXT NOT NULL DEFAULT ''
+);
+
+CREATE VIRTUAL TABLE pages_fts USING fts5(
+    path      UNINDEXED,
+    title,
+    content,
+    tokenize = 'porter unicode61'
+);