-- sql5 Database Test Script
-- 測試：基本 CRUD 操作
-- 注意：不支援 JOIN、WITH 等複雜語法
-- ============================================
-- 1. 建立使用者表格
-- ============================================
CREATE TABLE users(id INTEGER PRIMARY KEY, username TEXT NOT NULL, email TEXT NOT NULL)
-- ============================================
-- 2. 建立文章表格
-- ============================================
CREATE TABLE posts(id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL, title TEXT NOT NULL, content TEXT, status TEXT)
-- ============================================
-- 3. 建立評論表格
-- ============================================
CREATE TABLE comments(id INTEGER PRIMARY KEY, post_id INTEGER NOT NULL, user_id INTEGER NOT NULL, content TEXT NOT NULL)
-- ============================================
-- 4. 建立分類表格
-- ============================================
CREATE TABLE categories(id INTEGER PRIMARY KEY, name TEXT NOT NULL, description TEXT)
-- ============================================
-- 5. 新增使用者
-- ============================================
INSERT INTO users VALUES(1, 'admin', 'admin@example.com')
INSERT INTO users VALUES(2, 'alice', 'alice@example.com')
INSERT INTO users VALUES(3, 'bob', 'bob@example.com')
INSERT INTO users VALUES(4, 'carol', 'carol@example.com')
-- ============================================
-- 6. 新增文章
-- ============================================
INSERT INTO posts VALUES(1, 2, 'Welcome to sql5', 'sql5 is awesome!', 'published')
INSERT INTO posts VALUES(2, 2, 'Getting Started', 'Learn in 5 minutes', 'published')
INSERT INTO posts VALUES(3, 3, 'Advanced Features', 'Exploring FTS5', 'published')
INSERT INTO posts VALUES(4, 4, 'My Draft', 'Still working on it', 'draft')
-- ============================================
-- 7. 新增評論
-- ============================================
INSERT INTO comments VALUES(1, 1, 3, 'Great article!')
INSERT INTO comments VALUES(2, 1, 4, 'Thanks for sharing!')
INSERT INTO comments VALUES(3, 2, 2, 'Very helpful!')
INSERT INTO comments VALUES(4, 3, 3, 'Looking forward to more!')
-- ============================================
-- 8. 新增分類
-- ============================================
INSERT INTO categories VALUES(1, 'Tutorial', 'Step-by-step guides')
INSERT INTO categories VALUES(2, 'News', 'Latest updates')
INSERT INTO categories VALUES(3, 'Tips', 'Useful tips')
-- ============================================
-- 查詢測試
-- ============================================
-- 查看所有使用者
SELECT * FROM users
-- 查看所有文章
SELECT * FROM posts
-- 查看某使用者的文章
SELECT * FROM posts WHERE user_id = 2
-- 查看已發布的文章
SELECT * FROM posts WHERE status = 'published'
-- 統計文章數
SELECT user_id, COUNT(*) FROM posts GROUP BY user_id
-- 修改狀態
UPDATE posts SET status = 'published' WHERE id = 4
-- 刪除評論
DELETE FROM comments WHERE id = 1
-- 查看統計
SELECT COUNT(*) FROM users
SELECT COUNT(*) FROM posts
SELECT COUNT(*) FROM comments
-- 測試完成
SELECT 'Test completed!' AS result