https://github.com/birdy-C/BookSystem.git
详细设计
SQLite是一款轻型的数据库,占用资源低,与Qt的连接便易。
数据库逻辑结构设计
创建
PRAGMA foreign_keys = ON;
----------------------------------------------------------------------------------------------- create table type( Book_type nvarchar(10) primary key , Type_number int NOT NULL UNIQUE , check (Type_number >0) ); ---------------------------------------------------------------------------------------------- create table book( Book_ID int primary key , Type nvarchar(10) REFERENCES type(Book_type) , Title nvarchar , Publisher nvarchar , Publish_year int check(publish_year>1800 ) , Author NVARCHAR , Price decimal(6,2) , Number int check(Number>=0) , Stocks int check(Stocks>=0) ); --------------------------临时表,处理批量入库------------------------------------- create table book_temp( Type nvarchar(10) , Title nvarchar , Publisher nvarchar , Publish_year int , Author NVARCHAR , Price decimal(6,2) , Number int ); ------------------------------------------------------------------------------------------------ create table library_card( card_ID int primary key , Username varchar(10) , Company nvarchar , Type varchar(10) check (Type in ( 'student', 'teacher' ) )); ----------------------------------------------------------------------------------------------- create table manager( manager_ID int primary key , Password varchar(10) not null , Name varchar(10) , Connection int ); ----------------------------------------------------------------------------------------------- create table record ( Book_ID int REFERENCES book(Book_ID) , card_ID int REFERENCES Library_card(card_ID) , borrow_data data , return_data data check ( return_data >= borrow_data ) , manager_ID int REFERENCES manager(manager_ID) , primary key(Book_ID,card_ID) );
|
触发器
--------------检查出版年份-----------------
CREATE TRIGGER Tr_check_date AFTER insert ON book FOR EACH ROW WHEN( (select Book_ID from book where publish_year > (SELECT strftime('%Y',date('now')) ) )is not null) BEGIN SELECT RAISE(ROLLBACK, 'IMPOSSIBLE DATE') ; END;
--------------检查输入时 库存与数量是否相等-----------------
CREATE TRIGGER Tr_check_number BEFORE insert ON book FOR EACH ROW WHEN ( new.Number <> new.Stocks ) BEGIN
SELECT RAISE(ROLLBACK, 'ANY WRONG ABOUT NUMBER?');
END;
--------------借出检查是否有余量----------------- CREATE TRIGGER Tr_borrow_check BEFORE insert ON record FOR EACH ROW WHEN 0=(select Stocks from book where Book_ID=new.Book_ID) BEGIN SELECT RAISE(ROLLBACK, 'NO REMAINING') ; END;
--------------借出检查借书证 老师5学生3----------------- CREATE TRIGGER Tr_borrow_card AFTER insert ON record FOR EACH ROW WHEN ( (select count(*) from record join (select card_ID as S from library_card where card_ID = new.card_ID and Type = 'student') on record.card_ID = S ) >3 --学生 or (select count(*) from record join (select card_ID as T from library_card where card_ID = new.card_ID and Type = 'teacher') on record.card_ID = T ) >5 --老师
) BEGIN SELECT RAISE(ROLLBACK, 'MORE THAN PERMITTED') ; END;
--------------借出更新数据----------------- CREATE TRIGGER Tr_borrow AFTER insert ON record FOR EACH ROW BEGIN UPDATE Book SET Stocks = ( select Stocks - 1 FROM Book WHERE Book_ID = new.Book_ID ) WHERE Book_ID = new.Book_ID ; END;
--------------返回更新数据-----------------
CREATE TRIGGER Tr_return AFTER delete ON record FOR EACH ROW BEGIN UPDATE Book SET Stocks = ( select Stocks + 1 FROM Book WHERE Book_ID = old.Book_ID ) WHERE Book_ID = old.Book_ID ; END;
--------------插入检查是否已经存在----------------- ---------这一段大概有很多可以优化的地方吧……
CREATE TRIGGER Tr_check_insert AFTER insert ON book FOR EACH ROW WHEN ( ( select count(Book_ID) from book where Type = new.Type and Title = new.Title and Publisher = new.Publisher and Publish_year = new.Publish_year and Author = new.Author and Price = new.Price ) >1--except for new ) BEGIN UPDATE Book SET Stocks = ( select Stocks + new.Stocks FROM Book where Type = new.Type and Title = new.Title and Publisher = new.Publisher and Publish_year = new.Publish_year and Author = new.Author and Price = new.Price
) where Type = new.Type and Title = new.Title and Publisher = new.Publisher and Publish_year = new.Publish_year and Author = new.Author and Price = new.Price; UPDATE Book SET Number = ( select Number + new.Number FROM Book where Type = new.Type and Title = new.Title and Publisher = new.Publisher and Publish_year = new.Publish_year and Author = new.Author and Price = new.Price ) where Type = new.Type and Title = new.Title and Publisher = new.Publisher and Publish_year = new.Publish_year and Author = new.Author and Price = new.Price;
DELETE FROM Book WHERE Book_ID = new.Book_ID; END; |
图书管理系统设计
一、主要类说明(SQL相关)以及主要使用
QsqlDatabase提供了一个Qt和DB的连接口。
以下代码为数据库连接代码,并且打开数据库文件DBSYSTEN.db
/*---- LOAD IN DASTABASE ----*/ db = QSqlDatabase::addDatabase("QSQLITE"); db.setDatabaseName("DBSYSTEM.db"); if (!db.open()) { QMessageBox::warning(0, QObject::tr("Database Error"),db.lastError().text()); qDebug() << QString("LOAD_IN"); returnfalse; } |
此外可以通过boolQSqlDatabase::transaction(),bool QSqlDatabase::commit(),bool QSqlDatabase::rollback()来实现相当于撤销和保存的操作。
QsqlQuery提供了一个SQL语句的运行接口。
如下面语句,实现的是读取Book_id对应书的库存和数量。
QSqlQuery query_getStocks("select Stocks,Number from book where Book_id = '"+ ID +"'"); |