346 lines
13 KiB
PL/PgSQL
346 lines
13 KiB
PL/PgSQL
/* ---------- 基础设置 ---------- */
|
||
CREATE SCHEMA IF NOT EXISTS library;
|
||
SET search_path TO library,public;
|
||
|
||
/* ---------- ENUM 类型 ---------- */
|
||
CREATE TYPE gender_enum AS ENUM ('M','F','O');
|
||
CREATE TYPE book_status_enum AS ENUM ('normal','lost','damaged','removed');
|
||
CREATE TYPE borrow_status_enum AS ENUM ('borrowed','returned','overdue','lost');
|
||
CREATE TYPE reserve_status_enum AS ENUM ('waiting','available','cancelled','expired');
|
||
CREATE TYPE acct_status_enum AS ENUM ('active','reported','frozen');
|
||
|
||
/* ---------- 系统参数表 ---------- */
|
||
CREATE TABLE system_settings (
|
||
setting_key text PRIMARY KEY,
|
||
setting_value text NOT NULL
|
||
);
|
||
/* 罚款费率、冻结阈值等默认值 */
|
||
INSERT INTO system_settings(setting_key,setting_value) VALUES
|
||
('fine_per_day', '0.50'), -- 每本书每日罚款金额
|
||
('freeze_threshold', '20.00'), -- 欠款 ≥ 此值自动冻结
|
||
('max_borrow_default', '10'); -- 默认最大借阅量
|
||
|
||
/* ---------- 图书信息 ---------- */
|
||
CREATE TABLE books (
|
||
book_id bigserial PRIMARY KEY,
|
||
isbn varchar(13) UNIQUE NOT NULL,
|
||
title text NOT NULL,
|
||
authors text[] NOT NULL,
|
||
publisher text,
|
||
publish_date date,
|
||
price numeric(10,2),
|
||
classification_no varchar(64),
|
||
location varchar(255),
|
||
total_copies int NOT NULL DEFAULT 1 CHECK(total_copies>0),
|
||
available_copies int NOT NULL DEFAULT 1 CHECK(available_copies>=0),
|
||
status book_status_enum NOT NULL DEFAULT 'normal',
|
||
description text,
|
||
cover_url text,
|
||
created_at timestamptz DEFAULT now(),
|
||
updated_at timestamptz DEFAULT now()
|
||
);
|
||
|
||
/* ---------- 帮助函数：按键名返回 int 型系统参数 ---------- */
|
||
CREATE OR REPLACE FUNCTION library.get_setting_int(p_key text)
|
||
RETURNS int
|
||
LANGUAGE sql
|
||
STABLE -- ← 允许做 DEFAULT
|
||
AS $$
|
||
SELECT setting_value::int
|
||
FROM library.system_settings
|
||
WHERE setting_key = p_key;
|
||
$$;
|
||
|
||
/* ---------- 学生信息 ---------- */
|
||
CREATE TABLE library.students (
|
||
student_id bigserial PRIMARY KEY,
|
||
stu_no varchar(20) UNIQUE NOT NULL, -- 学号
|
||
name varchar(64) NOT NULL,
|
||
gender gender_enum,
|
||
department varchar(128),
|
||
major varchar(128),
|
||
grade varchar(10),
|
||
class varchar(50),
|
||
phone varchar(20),
|
||
email varchar(255),
|
||
account_status acct_status_enum NOT NULL DEFAULT 'active',
|
||
max_borrow int NOT NULL
|
||
DEFAULT library.get_setting_int('max_borrow_default'),
|
||
current_borrow int NOT NULL DEFAULT 0,
|
||
created_at timestamptz DEFAULT now()
|
||
);
|
||
|
||
/* ---------- 管理员信息 ---------- */
|
||
CREATE TABLE admins (
|
||
admin_id bigserial PRIMARY KEY,
|
||
emp_no varchar(20) UNIQUE NOT NULL,
|
||
name varchar(64) NOT NULL,
|
||
position varchar(64),
|
||
phone varchar(20),
|
||
privilege_lv int NOT NULL DEFAULT 1,
|
||
created_at timestamptz DEFAULT now()
|
||
);
|
||
|
||
/* ---------- 借阅记录 ---------- */
|
||
CREATE TABLE borrow_records (
|
||
borrow_id bigserial PRIMARY KEY,
|
||
book_id bigint REFERENCES books(book_id) ON DELETE CASCADE,
|
||
student_id bigint REFERENCES students(student_id) ON DELETE CASCADE,
|
||
borrow_date date NOT NULL DEFAULT current_date,
|
||
due_date date NOT NULL,
|
||
return_date date,
|
||
renew_times int NOT NULL DEFAULT 0,
|
||
status borrow_status_enum NOT NULL DEFAULT 'borrowed',
|
||
fine_amount numeric(10,2) NOT NULL DEFAULT 0
|
||
);
|
||
|
||
/* ---------- 图书预约 ---------- */
|
||
CREATE TABLE reservations (
|
||
reservation_id bigserial PRIMARY KEY,
|
||
book_id bigint REFERENCES books(book_id) ON DELETE CASCADE,
|
||
student_id bigint REFERENCES students(student_id) ON DELETE CASCADE,
|
||
reserve_date date NOT NULL DEFAULT current_date,
|
||
status reserve_status_enum NOT NULL DEFAULT 'waiting'
|
||
);
|
||
|
||
/* ---------- 图书评价 ---------- */
|
||
CREATE TABLE reviews (
|
||
review_id bigserial PRIMARY KEY,
|
||
book_id bigint REFERENCES books(book_id) ON DELETE CASCADE,
|
||
student_id bigint REFERENCES students(student_id) ON DELETE CASCADE,
|
||
rating int NOT NULL CHECK(rating BETWEEN 1 AND 5),
|
||
content text,
|
||
review_time timestamptz DEFAULT now(),
|
||
UNIQUE(book_id,student_id) -- 一人一书仅一次评价
|
||
);
|
||
|
||
/* ---------- 罚款记录 ---------- */
|
||
CREATE TABLE fines (
|
||
fine_id bigserial PRIMARY KEY,
|
||
student_id bigint REFERENCES students(student_id) ON DELETE CASCADE,
|
||
amount numeric(10,2) NOT NULL CHECK(amount>0),
|
||
reason text,
|
||
status varchar(10) NOT NULL CHECK(status IN ('unpaid','paid')),
|
||
issue_date date NOT NULL DEFAULT current_date,
|
||
admin_id bigint REFERENCES admins(admin_id)
|
||
);
|
||
|
||
/* ---------- 拓展 ---------- */
|
||
CREATE EXTENSION IF NOT EXISTS pg_trgm WITH SCHEMA public;
|
||
|
||
/* ---------- 索引 ---------- */
|
||
CREATE INDEX idx_books_title_trgm
|
||
ON library.books USING gin (title gin_trgm_ops);
|
||
CREATE INDEX idx_books_authors_gin
|
||
ON library.books USING gin (authors);
|
||
CREATE INDEX idx_br_student_status ON borrow_records(student_id,status);
|
||
CREATE INDEX idx_reserve_book_wait ON reservations(status,book_id);
|
||
CREATE INDEX idx_fines_student_unpaid ON fines(student_id) WHERE status='unpaid';
|
||
|
||
/* 要使用 trigram GIN 索引需启用扩展 */
|
||
CREATE EXTENSION IF NOT EXISTS pg_trgm;
|
||
|
||
/* ---------- 触发器函数 ---------- */
|
||
|
||
/* 1. 更新可借数量 & 学生当前借阅量 */
|
||
CREATE OR REPLACE FUNCTION trg_sync_book_student() RETURNS TRIGGER AS $$
|
||
BEGIN
|
||
-- 更新图书可借册数
|
||
UPDATE books SET available_copies = total_copies -
|
||
(SELECT count(*) FROM borrow_records
|
||
WHERE book_id = COALESCE(NEW.book_id,OLD.book_id)
|
||
AND status IN ('borrowed','overdue'))
|
||
WHERE book_id = COALESCE(NEW.book_id,OLD.book_id);
|
||
|
||
-- 更新学生当前借阅量
|
||
UPDATE students SET current_borrow =
|
||
(SELECT count(*) FROM borrow_records
|
||
WHERE student_id = COALESCE(NEW.student_id,OLD.student_id)
|
||
AND status IN ('borrowed','overdue'))
|
||
WHERE student_id = COALESCE(NEW.student_id,OLD.student_id);
|
||
|
||
RETURN NULL;
|
||
END; $$ LANGUAGE plpgsql;
|
||
|
||
/* 2. 归还时自动计算并记账罚款 */
|
||
CREATE OR REPLACE FUNCTION trg_calc_fine() RETURNS TRIGGER AS $$
|
||
DECLARE
|
||
days_overdue int;
|
||
rate numeric(10,2) := (SELECT setting_value::numeric FROM system_settings WHERE setting_key='fine_per_day');
|
||
BEGIN
|
||
IF TG_OP='UPDATE' AND NEW.status='returned' AND OLD.status IN ('borrowed','overdue') THEN
|
||
days_overdue := GREATEST((NEW.return_date - NEW.due_date),0);
|
||
IF days_overdue > 0 THEN
|
||
INSERT INTO fines(student_id,amount,reason,status,admin_id)
|
||
VALUES (NEW.student_id, days_overdue*rate,
|
||
format('Overdue %s days for borrow_id=%s',days_overdue,NEW.borrow_id),
|
||
'unpaid', NULL);
|
||
END IF;
|
||
END IF;
|
||
RETURN NEW;
|
||
END; $$ LANGUAGE plpgsql;
|
||
|
||
/* 3. 欠款超阈值自动冻结账户 */
|
||
CREATE OR REPLACE FUNCTION library.trg_freeze_account() RETURNS TRIGGER AS $$
|
||
DECLARE
|
||
total_unpaid numeric(10,2);
|
||
threshold numeric(10,2);
|
||
v_relevant_student_id bigint;
|
||
v_current_status library.acct_status_enum;
|
||
v_new_status library.acct_status_enum;
|
||
BEGIN
|
||
-- 1. 获取最新的冻结阈值
|
||
SELECT setting_value::numeric INTO threshold
|
||
FROM library.system_settings
|
||
WHERE setting_key = 'freeze_threshold';
|
||
|
||
IF NOT FOUND THEN
|
||
RAISE WARNING 'System setting "freeze_threshold" not found. Account status will not be updated by trigger.';
|
||
RETURN NULL; -- 或者 RAISE EXCEPTION;
|
||
END IF;
|
||
|
||
-- 2. 确定相关的学生ID
|
||
IF TG_OP = 'DELETE' THEN
|
||
v_relevant_student_id := OLD.student_id;
|
||
ELSE -- INSERT or UPDATE
|
||
v_relevant_student_id := NEW.student_id;
|
||
END IF;
|
||
|
||
-- 如果没有相关的学生ID（理论上不应发生，因为student_id是FK且NOT NULL），则退出
|
||
IF v_relevant_student_id IS NULL THEN
|
||
RAISE WARNING 'trg_freeze_account: No relevant student_id found. TG_OP: %', TG_OP;
|
||
RETURN NULL;
|
||
END IF;
|
||
|
||
-- 3. 计算该学生未缴罚款总额
|
||
SELECT COALESCE(sum(amount),0) INTO total_unpaid
|
||
FROM library.fines
|
||
WHERE student_id = v_relevant_student_id AND status = 'unpaid';
|
||
|
||
-- 4. 获取学生当前账户状态
|
||
SELECT account_status INTO v_current_status
|
||
FROM library.students
|
||
WHERE student_id = v_relevant_student_id;
|
||
|
||
-- 5. 根据罚款确定新的目标状态
|
||
-- 注意：原始逻辑是，如果欠款低于阈值，则账户变为 'active'。
|
||
-- 这意味着如果账户之前是 'reported'（挂失），且欠款低于阈值，它也会被此触发器改为 'active'。
|
||
-- 如果希望 'reported' 状态不受此罚款逻辑影响（除非因欠款被冻结），则需要更复杂的判断。
|
||
-- 此处保持与你原触发器相似的逻辑，仅修复类型问题并优化。
|
||
IF total_unpaid >= threshold THEN
|
||
v_new_status := 'frozen'::library.acct_status_enum;
|
||
ELSE
|
||
-- 如果当前已经是 'frozen'，则解冻为 'active'
|
||
-- 如果当前不是 'frozen' (比如是 'active' 或 'reported')，且欠款未超限，则应保持其原状态，
|
||
-- 而不是都强制变为 'active'。
|
||
-- 为了更安全地处理 'reported' 状态，我们修改这里的逻辑：
|
||
IF v_current_status = 'frozen'::library.acct_status_enum THEN
|
||
v_new_status := 'active'::library.acct_status_enum;
|
||
ELSE
|
||
v_new_status := v_current_status; -- 保持现有状态 (active 或 reported)
|
||
END IF;
|
||
END IF;
|
||
|
||
-- 6. 如果计算出的新状态与当前状态不同，则更新学生账户状态
|
||
IF v_current_status IS DISTINCT FROM v_new_status THEN
|
||
UPDATE library.students
|
||
SET account_status = v_new_status
|
||
WHERE student_id = v_relevant_student_id;
|
||
END IF;
|
||
|
||
RETURN NULL; -- AFTER 触发器通常返回 NULL
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
/* ---------- 触发器绑定 ---------- */
|
||
CREATE TRIGGER trg_borrow_sync_aiud
|
||
AFTER INSERT OR UPDATE OR DELETE ON borrow_records
|
||
FOR EACH ROW EXECUTE FUNCTION trg_sync_book_student();
|
||
|
||
CREATE TRIGGER trg_borrow_calc_fine_upd
|
||
AFTER UPDATE ON borrow_records
|
||
FOR EACH ROW EXECUTE FUNCTION trg_calc_fine();
|
||
|
||
CREATE TRIGGER trg_fine_freeze_aiud
|
||
AFTER INSERT OR UPDATE OR DELETE ON fines
|
||
FOR EACH ROW EXECUTE FUNCTION trg_freeze_account();
|
||
|
||
/* ---------- 视图 ---------- */
|
||
|
||
/* (1) 当前热门图书：借阅量前 20 */
|
||
CREATE OR REPLACE VIEW v_hot_books AS
|
||
SELECT b.book_id, b.isbn, b.title,
|
||
COUNT(br.borrow_id) AS borrow_cnt
|
||
FROM books b
|
||
JOIN borrow_records br ON br.book_id = b.book_id
|
||
GROUP BY b.book_id
|
||
ORDER BY borrow_cnt DESC
|
||
LIMIT 20;
|
||
|
||
/* (2) 各院系借阅统计 */
|
||
CREATE OR REPLACE VIEW v_dept_borrow_stats AS
|
||
SELECT s.department,
|
||
COUNT(br.borrow_id) AS total_borrows,
|
||
COUNT(DISTINCT br.student_id) AS unique_readers
|
||
FROM borrow_records br
|
||
JOIN students s ON s.student_id = br.student_id
|
||
GROUP BY s.department
|
||
ORDER BY total_borrows DESC;
|
||
|
||
/* (3) 图书逾期情况 */
|
||
CREATE OR REPLACE VIEW v_overdue_details AS
|
||
SELECT br.borrow_id, br.book_id, br.student_id,
|
||
br.due_date, CURRENT_DATE - br.due_date AS days_overdue
|
||
FROM borrow_records br
|
||
WHERE br.status='overdue';
|
||
|
||
/* ---------- 存储过程（plpgsql 函数） ---------- */
|
||
|
||
/* 1. 学期初批量初始化学生账户
|
||
- 示例：EXECUTE library.sp_init_students('2025-Fall');
|
||
*/
|
||
CREATE OR REPLACE FUNCTION sp_init_students(term_code text)
|
||
RETURNS void LANGUAGE plpgsql AS $$
|
||
BEGIN
|
||
-- 示例逻辑：所有学生 current_borrow 清零、状态激活
|
||
UPDATE students SET current_borrow=0, account_status='active';
|
||
RAISE NOTICE 'Students initialized for %', term_code;
|
||
END; $$;
|
||
|
||
/* 2. 定期生成图书流通统计（存入自定义表） */
|
||
CREATE TABLE IF NOT EXISTS circulation_stats (
|
||
stat_date date PRIMARY KEY,
|
||
total_borrows bigint,
|
||
unique_readers bigint,
|
||
created_at timestamptz DEFAULT now()
|
||
);
|
||
|
||
CREATE OR REPLACE FUNCTION sp_generate_circulation_stats()
|
||
RETURNS void LANGUAGE plpgsql AS $$
|
||
BEGIN
|
||
INSERT INTO circulation_stats(stat_date,total_borrows,unique_readers)
|
||
SELECT CURRENT_DATE,
|
||
(SELECT COUNT(*) FROM borrow_records WHERE borrow_date=CURRENT_DATE),
|
||
(SELECT COUNT(DISTINCT student_id) FROM borrow_records WHERE borrow_date=CURRENT_DATE);
|
||
END; $$;
|
||
|
||
/* 3. 自动发送逾期提醒（示例写入提醒表） */
|
||
CREATE TABLE IF NOT EXISTS overdue_notices (
|
||
notice_id bigserial PRIMARY KEY,
|
||
borrow_id bigint,
|
||
student_id bigint,
|
||
notice_time timestamptz DEFAULT now()
|
||
);
|
||
|
||
CREATE OR REPLACE FUNCTION sp_send_overdue_notices()
|
||
RETURNS void LANGUAGE plpgsql AS $$
|
||
BEGIN
|
||
INSERT INTO overdue_notices(borrow_id,student_id)
|
||
SELECT br.borrow_id, br.student_id
|
||
FROM borrow_records br
|
||
WHERE br.status='overdue'
|
||
AND NOT EXISTS (SELECT 1 FROM overdue_notices onot WHERE onot.borrow_id=br.borrow_id);
|
||
END; $$;
|
||
|
||
/* ---------- 完成 ---------- */
|
||
COMMENT ON SCHEMA library IS '智能图书管理系统数据库架构（2025-06-21）'; |