SQLite+Qt 图书管理系统设计（具体实现）

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 +"'");