4.21 数据库

CREATE TABLE users (
user_id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT NOT NULL UNIQUE,
password TEXT NOT NULL,
email TEXT,
created_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE categories (
category_id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL UNIQUE,
type INTEGER NOT NULL CHECK (type IN (1, 2))
);
CREATE TABLE budgets (
budget_id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
month TEXT NOT NULL CHECK (month GLOB '????-??'),
amount REAL NOT NULL CHECK (amount > 0),
FOREIGN KEY (user_id) REFERENCES users(user_id),
UNIQUE(user_id, month)
);
CREATE TABLE incomes (
income_id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
amount REAL NOT NULL CHECK (amount > 0),
category TEXT NOT NULL,
description TEXT,
date TEXT NOT NULL CHECK (date GLOB '????-??-??'),
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(user_id),
FOREIGN KEY (category) REFERENCES categories(name)
);
CREATE TABLE expenses (
expense_id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
amount REAL NOT NULL CHECK (amount > 0),
category TEXT NOT NULL,
description TEXT,
date TEXT NOT NULL CHECK (date GLOB '????-??-??'),
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(user_id),
FOREIGN KEY (category) REFERENCES categories(name)
);
-- 收入类别
INSERT INTO categories (name, type) VALUES
('工资', 1), ('奖金', 1), ('投资', 1), ('兼职', 1), ('其他', 1);

-- 支出类别
INSERT INTO categories (name, type) VALUES
('餐饮', 2), ('交通', 2), ('购物', 2), ('娱乐', 2),
('住房', 2), ('医疗', 2), ('教育', 2), ('其他', 2);