4 数据库创建和数据加载

4.1 视图

在酒店管理系统中，为了对账需要，所有历史订单都不能删除。但用户通常只关心还未退房的订单，系统在查询空房时，也只是对照那些还没退房的订单使用排除法进行查找。因此，保存一个仅包含还未退房的订单的视图能够方便数据库后台的管理和应用的实现，视图实现的代码如下。

create view VIEW_available_orders as
select *
  from `Order` o
  where o.status = 1 and o.check_out > CURDATE();

4.2 存储过程

4.2.1 查询可用房间

在酒店管理系统中，我们不可能存储未来每一天每间房间是否可用的状态。要查询某个指定时间段空闲的房间有哪些，必须根据当前有效的订单信息，查询并排除该时间段内已经被预定的房间。由于查询可用房间是酒店数据库最常用的功能，因此我们设计了存储过程：

create procedure PROC_find_avail_room
(in Arg_check_in Date, in Arg_check_out Date,
in Arg_capacity int, in Arg_wifi char(1), in Arg_breakfast char(1))
begin
select R.id,R.floor, R.room_num,R.price,
T.breakfast, T.wifi ,T.name, T.capacity
from Room as R, RoomType as T
where R.type_id = T.id and T.capacity >= Arg_capacity
and T.wifi like Arg_wifi and T.breakfast like Arg_breakfast and
(not exists (select * from VIEW_available_orders o
  where R.id = o.room_id and Arg_check_in <= o.check_out and
    Arg_check_out >= o.check_in
));
end;

该存储过程的参数说明可见表 4.1。

可以通过以下方式调用该存储过程，表示查询入住时间为 2019 年 1 月 22 日，退房时间也为 2019 年 1 月 22 日测情况下，有 wifi 且可住两人的空闲房间。

call PROC_find_avail_room('2019-01-22', '2019-01-22', 2, '1', '%');

4.2.2 预定房间

预定房间在系统中设计多个操作，首先要在数据库中查询该房间是否可被预定，然后如果没有被预定，则需要在Order表中生成一个新订单，然后在 Operation 表中生成一条该订单的生成记录，同时还需要扣除指定用户余额，这一连串的操作应当以事务的形式来实现，因此我们将整个复杂的过程，使用存储过程在数据库中实现，代码可见如下：

create or replace procedure PROC_order_room
(in Arg_room_id int, in Arg_user_id int, in Arg_check_in Date, in Arg_check_out Date)
begin
  declare L_price integer ;
  declare L_days integer ;
  declare L_already_occupied integer ;
  select (DATEDIFF(Arg_check_out, Arg_check_in)+1) into L_days;
  start transaction ;
  select count(o.id) from VIEW_available_orders o
  where Arg_room_id = o.room_id and Arg_check_in <= o.check_out and Arg_check_out >= o.check_in,
  into L_already_occupied;
  IF (L_already_occupied) THEN
  SIGNAL SQLSTATE '45000' SET
  MYSQL_ERRNO = 30004,
  MESSAGE_TEXT = 'Sorry, the room is already occupied';
  elseif (L_days < 0) THEN
  SIGNAL SQLSTATE '45000' SET
  MYSQL_ERRNO = 30001,
  MESSAGE_TEXT = 'Wrong order(Arg_check_in > Arg_check_out)';
  else
  select r.price from Room r
  where r.id = Arg_room_id
  into L_price;
  insert into `Order` (room_id, user_id, check_in, check_out, status,fee) value
  (Arg_room_id, Arg_user_id, Arg_check_in, Arg_check_out, 1, L_price * L_days);
  insert into Operation(time, detail, order_id) value( now(), 1, LAST_INSERT_ID());
  end if;
  commit;
end;

该存储过程的参数说明可见表 4.2。

4.2.3 退房

退房操作同样涉及到较多的操作，首先需要判断该房间是否已经预定，只有已经预定了的房间才能够被取消；在确定能够退房后，需要以事务的方式完成以下的一连串操作：在Order表中将指定订单的状态设置为取消，然后计算退房费用并返还到用户账户中，同时还需要在 Operation 表中生成一条新纪录，记录该订单的取消操作。以事务的方式完成上述一系列操作能够保证数据库中数据的一致性。

该存储过程的代码可见如下，只需要为该存储过程提供订单编号即可进行操作。

create procedure PROC_cancel_order(IN Arg_order_id int)
begin
  declare O_status int;
  declare O_check_in Date;
  declare O_check_out Date;
  declare O_refund_days int;
  select -1 into O_status;
  select O.status from VIEW_available_orders O
  where O.id = Arg_order_id
  into O_status;
  select O.check_in
  from VIEW_available_orders O
  where O.id = Arg_order_id
  into O_check_in;
  select O.check_out
  from VIEW_available_orders O
  where O.id = Arg_order_id
  into O_check_out;
  IF (CURDATE() >= O_check_out or O_status = 0 or O_status = -1) THEN
  SIGNAL SQLSTATE '45000' SET
  MYSQL_ERRNO = 30005,
  MESSAGE_TEXT = 'Sorry, cancelling this order is not available';
  elseif (CURDATE() < O_check_in) Then
  start transaction;
  update User, `Order`
  set User.balance = User.balance + `Order`.fee
  where `Order`.id = Arg_order_id and `Order`.user_id = User.id;
  update `Order`
  set `Order`.status = 0
  where `Order`.id = Arg_order_id;
  insert into Operation(time, detail, order_id)
  value (now(), 2, Arg_order_id );
  commit;
  else
  start transaction;
  select DATEDIFF(O_check_out, CURDATE())
  into O_refund_days;
  update User, `Order`
  set User.balance =
    User.balance + O_refund_days *
    `Order`.fee /(O_check_out - O_check_in + 1)
    where User.id = `Order`.user_id and `Order`.id = Arg_order_id;
  update `Order`
  set `Order`.status = 0
  where `Order`.id = Arg_order_id;
  insert into Operation(time, detail, order_id) value (now(), 2, Arg_order_id); 
  commit ;
  end if;
end;

4.3 建立触发器

用户每次订房时要从余额中扣取房费并给予积分奖励，这一操作必须在订单生成前完成，并对用户余额进行检查，避免用户余额不足，该触发器的实现可见以下代码：

create or replace trigger TRI_order_fee
  before insert on `Order`
  for each row
  begin
  declare fee integer;
  declare days integer; 
  declare old_balance integer ;
  select balance into old_balance
  from User u
  where u.id = New.user_id;
  IF (old_balance = null or old_balance - New.fee < 0) THEN
    SIGNAL SQLSTATE '45000' SET
    MYSQL_ERRNO = 30001,
    MESSAGE_TEXT = 'You dont have enough balance'; 
  else
    update User u
    set balance = old_balance - New.fee
    where u.id = NEW.user_id;
  END IF;
  end;

4.4 事务机制

出于安全性考虑，我们将用户的账户与用户的个人信息分在两个表中分开存储。而酒店一般要求用户实名制登记，因此在注册时不仅要在账户表中添加账户，而且一定要在用户信息表相应地记录真实姓名和证件号码。为了确保用户注册操作的原子性，我们采用了事务机制，将两次插入操作在一次事务中提交。 通过，对订单的操作同样也采用了事务机制，“修改用户余额”，“生成订单”，“生成订单操作明细”三个操作的执行必须一一整个事务的方式来执行，否则可能会出现不一致的情况。 这一部分的代码实现，可见节 4.2存储过程一节。

4.5 自主存取控制

为了防止用户账户失窃、账单等数据因操作不当丢失，我们设计了自主存取控制机制，将数据库后台人员分为管理员和审计员，对用户权限进行如下分配：

• 只有管理员有查看和修改 Account 表的权限
  
• 审计员对其他表只有查看权限
  
• 管理员对 Order 表有除了 delete、alter、deletehistory 之外的其他权限
  
• 管理员对其他表有所有权限

4.6 测试数据生成

在这一步，我使用了 python 编写生成数据的脚本，并在其中对表中已存在的约束做了适当的控制，保证初始化的数据在数据库中是一致的。具体而言，我关注到了以下细节：

• Room 表中的 type_id，受到 RoomType 表中 id 一列的外键约束
  
• Account 表中的 id，受到 User 表中 id 一列的外键约束
  
• Order 表中的 user_id，room_id，均受到 User 表 id 与 Room 表 id 的外键约束
  
• Order 表中预约成功的订单，在 Operation 表中应有一条对应的生成订单记录
  

• Order 表中预约失败的订单，在 Operation 表中应有两条对应的订单操作记录， 一条为生成，另一条为取消
  

测试数据示例可见图 4.1

关于生成数据相关的 python 代码，由于与该系统设计关系不大，因此不放在报告中呈现。

5 数据库应用软件设计开发与测试

5.1 系统整体架构

我们系统设计采用了前后端分离的模式，具体可见图 5.1。

5.2 后端 API 设计

关于后端的 API 设计，我们编写了一份完整且规范的 API 文档，其中含有每一个 API 的参数说明，返回值，以及错误信息的设置，文档将会在项目中另外附上一份文件进行说明，截图可见图 5.2。

5.3 系统界面说明

关于系统的界面，需要注意到，酒店经理所看到的系统界面与用户所看到的的系统界面应当是不同的，用户界面可见图 5.3，酒店经理使用界面可见图 5.4。

用户界面

酒店经理使用界面

6 系统测试

在这一部分，我演示了一个在本系统中比较典型的操作，用户预订房间与查询取消订单，更详细的说明可见报告附件中的屏幕录像文件。

6.1 用户预订房间

一个用户名为“123”的用户要预订“2019-01-01”-“2019-01-09”的至少能住 5 人的房间，因此他打开了该酒店房间预订系统，并使用用户名“123”，密码 “123”登录进入系统，并选择对应的房间。

当看到返回了‘ok’信息后，便是预订成功了，按下“Orders”按钮，选好指定日期之后，就可以查看刚刚下的订单，可见图 6.2。

可以看到，由于定了9天，因此系统生成的订单里也的确生成了9*1000=9000 元的订单，同时用户的余额少了 9000，可见图 6.3。

7 开发所遇到的问题与解决方案

在开发本数据库系统的过程中，我们遇到了很多问题，例如对可能改变数据库数据的一致性的用户请求该如何处理？在数据库增加一项如何自动生成 id 等。对这些开发细节的说明与解决，我们在下面分成了几个小节分别说明。

7.1 如何保证数据一致性？

在一些操作中，对于用户可能仅仅是一个点击的操作，但是在系统后台执行该请求时，往往需要涉及到多个对数据库的写操作。就以用户请求预定房间为例，该操作要求更新用户余额，更新 Order 表和 Operation 表等操作。

为了保证数据的一致性，需要将这些操作以事务的方式进行操作。在我们的实现中，有两种方式来实现事务，一种是在后端的代码中，使用数据库连接器提供的事务接口实现事务，可见图 7.1。

在另一种实现中，我们还编写了多个存储过程，在存储过程中使用“starttransaction”，与“commit”语句实现事务操作，可见节 4.2。

7.2 如何在查询订单时查询订单相关的操作明细？

本次实验中网站后端使用的 JavaScript 语言采用了內建多个消息循环的执行模式向数据库发送一个查询请求仅仅是将这一个查询请求“登记”到消息循环中，查询异步执行，程序将立即返回。因此，如果想在执行查询一个订单的查询语句中，直接嵌套另一个循环来查询操作明细是不可行的。解决方法有两种：

• 使用事务机制，确保执行完所有语句后程序才返回
  
• 前端首先调用一次查询订单操作，再依据订单 id 查询操作明细
  

第一种方法更适合用来保证一致性。因此我们采用了第二种方法。

7.3 用户权限的控制

关于不同用户权限控制的实现，我们曾经有过争论，是在前后端层面处理用户的权限，还是使用数据库的用户权限机制来对不同用户的访问做限制呢？最终我们的方案采取了在前后端对用户的权限进行处理。在后端，我们使用了 session 中的信息进行了用户信息的检查，代码可见图 7.2，通过检查用户信息，能够做到本用户只能够查看本用户的信息，在其他接口中，也是用了类似的方法对权限进行了检查，以防止没有登录的人，或者权限较低的用户通过直接访问api的方式获取数据库的数据。

7.4 存储过程的传参和返回值

存储过程相当于是存储并编译在数据库中的一段子程序。它在传参和返回值上和一般的查询语句都有所不同：

在查询房间时，用户如果不对是否提供 wifi、早餐做出要求，那么就应该既返回提供 wifi 的房间，又返回不提供 wifi 的房间。我们发现可以使用 like 谓词实现这个需求：where wifi like ’%’ 。但在把这条查询语句写入存储过程时发生了问题，实验发现：存储过程在传参过程中不允许参数发生隐式类型转换，如果把参数Arg_wifi声明为bool，那么刚刚这条查询语句就会出错，因此必须将参数Arg_wifi 声明为 char(1)。

此外存储过程的返回值在后端中是 list of list，而一般的查询语句的返回值是 list。这一点也要特殊处理。

实验发现：如果一个属性 num 的值为 null，那么它或它的与其他数字运算的结果出现在 if 语句中和其他数字比较，if 语句均会返回 false。因此在判断用户余额是否足够订房时最好要检查余额是否为 null，避免由于数据库数据上的失误出现部分用户可以无限订房的情况。

7.5 NULL 与数字比较

实验发现：如果一个属性 num 的值为 null，那么它或它的与其他数字运算的结果出现在 if 语句中和其他数字比较，if 语句均会返回 false。因此在判断用户余额是否足够订房时最好要检查余额是否为 null，避免由于数据库数据上的失误出现部分用户可以无限订房的情况。