147 lines
3.6 KiB
SQL
147 lines
3.6 KiB
SQL
create table bs_meta_permission
|
|
(
|
|
permission_level integer primary key,
|
|
name varchar(10)
|
|
);
|
|
insert into bs_meta_permission values (0,'超级管理员'),(1,'管理员'),(2,'图书馆借阅处'),(3,'读者');
|
|
|
|
|
|
create table bs_meta_account
|
|
(
|
|
account_status integer primary key,
|
|
name varchar(10)
|
|
);
|
|
insert into bs_meta_account values (0,'隐藏'),(1,'禁止登陆'),(2,'待验证'),(3,'正常');
|
|
|
|
create table bs_meta_book
|
|
(
|
|
status integer primary key,
|
|
name varchar(10)
|
|
);
|
|
insert into bs_meta_book values (0,'隐藏'),(1,'禁用'),(2,'正常');
|
|
|
|
create table bs_meta_bookstatus
|
|
(
|
|
status integer primary key,
|
|
name varchar(10)
|
|
);
|
|
insert into bs_meta_bookstatus values (0,'借出'),(1,'丢失'),(2,'在馆');
|
|
|
|
create table bs_user
|
|
(
|
|
username varchar(10) primary key,
|
|
password char(32),
|
|
nickname varchar(10),
|
|
permission_level integer references bs_meta_permission(permission_level),
|
|
account_status integer references bs_meta_account(account_status)
|
|
);
|
|
create table bs_level
|
|
(
|
|
user_lv integer primary key,
|
|
min_amount_limit integer,
|
|
max_amount_limit integer,
|
|
min_time_limit integer,
|
|
max_time_limit integer,
|
|
min_balance decimal(10,2)
|
|
);
|
|
create table bs_reader
|
|
(
|
|
username varchar(10) references bs_user(username),
|
|
realname varchar(10),
|
|
realid char(18),
|
|
phonenum varchar(15),
|
|
user_lv integer references bs_level(user_lv),
|
|
user_exp integer,
|
|
borrow_limit integer,
|
|
borrow_used integer,
|
|
borrow_time_limit integer,
|
|
bonus integer,
|
|
balance decimal(10,2)
|
|
);
|
|
create table bs_booktype
|
|
(
|
|
book_type varchar(10) primary key
|
|
);
|
|
create table bs_book
|
|
(
|
|
class_id integer primary key,
|
|
isbn varchar(13) unique,
|
|
name varchar(20),
|
|
book_type varchar(10) references bs_booktype(book_type),
|
|
author varchar(10),
|
|
publisher varchar(20),
|
|
publish_time date,
|
|
status integer references bs_meta_book(status)
|
|
);
|
|
create table bs_bookstatus
|
|
(
|
|
book_id integer primary key,
|
|
class_id integer references bs_book(class_id),
|
|
position varchar(30),
|
|
status integer references bs_meta_bookstatus(status)
|
|
);
|
|
create table bs_borrow
|
|
(
|
|
username varchar(10) references bs_user(username),
|
|
book_id integer references bs_bookstatus(book_id),
|
|
borrow_time date not null,
|
|
return_deadline date not null,
|
|
return_time date
|
|
);
|
|
create table bs_session
|
|
(
|
|
id char(32) primary key,
|
|
last_time integer,
|
|
username varchar(10) references bs_user(username)
|
|
);
|
|
|
|
DROP PROCEDURE IF EXISTS newbook;
|
|
DELIMITER //
|
|
create procedure newbook(
|
|
IN isbn varchar(13),
|
|
IN name varchar(20),
|
|
IN book_type varchar(10),
|
|
IN author varchar(10),
|
|
IN publisher varchar(20),
|
|
IN publish_time date,
|
|
IN status integer
|
|
)
|
|
begin
|
|
declare maxid integer;
|
|
declare idcnt integer;
|
|
declare result_code integer default 0;
|
|
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET result_code = 1;
|
|
|
|
start transaction;
|
|
select count(class_id) into idcnt from bs_book ;
|
|
select max(class_id) into maxid from bs_book;
|
|
|
|
if (idcnt=0) then
|
|
set maxid=1;
|
|
else
|
|
set maxid=maxid+1;
|
|
end if;
|
|
insert into bs_book values (maxid,isbn,name,book_type,author,publisher,publish_time,status);
|
|
if result_code=1 then
|
|
rollback;
|
|
else
|
|
commit;
|
|
end if;
|
|
|
|
select result_code;
|
|
end
|
|
//
|
|
DELIMITER ;
|
|
|
|
DELIMITER //
|
|
create trigger clean_up after insert on bs_session
|
|
for each row
|
|
begin
|
|
delete from bs_session where last_time+600<unix_timestamp(now());
|
|
end
|
|
//
|
|
DELIMITER ;
|
|
|
|
create view bs_userreader as select * from bs_user natural join bs_reader;
|
|
create view bs_bookdup as (select name,book_id from bs_book A inner join bs_bookstatus B on A.class_id=B.class_id)
|
|
create view bs_userborrow as select username,book_id from bs_user natural join bs_borrow where return_time is null |