面试题：复杂业务场景下iOS应用的SQLite数据库架构设计与迁移

Question

假设你正在开发一个具有复杂业务逻辑的iOS应用，涉及用户管理、订单处理、商品库存等多个模块，各模块数据相互关联。请设计一个SQLite数据库架构，并说明如何在应用升级时进行数据库迁移，确保数据的完整性和业务逻辑的连续性，需要详细阐述架构设计思路和迁移方案。

Answer 1

一、SQLite数据库架构设计思路

1. 用户表（Users）
   • 字段：
     • user_id（INTEGER PRIMARY KEY AUTOINCREMENT）：用户唯一标识，自增长。
     • username（TEXT NOT NULL UNIQUE）：用户名，非空且唯一。
     • password（TEXT NOT NULL）：用户密码。
     • email（TEXT UNIQUE）：用户邮箱，唯一。
     • phone（TEXT）：用户电话。
   • 设计思路：用于存储用户的基本信息，每个用户有唯一的user_id，方便在其他表中关联用户数据。username和email设置唯一性约束，确保用户登录信息的准确性。
2. 商品表（Products）
   • 字段：
     • product_id（INTEGER PRIMARY KEY AUTOINCREMENT）：商品唯一标识，自增长。
     • product_name（TEXT NOT NULL）：商品名称，非空。
     • description（TEXT）：商品描述。
     • price（REAL NOT NULL）：商品价格，非空。
     • stock（INTEGER NOT NULL）：商品库存数量，非空。
   • 设计思路：存储商品的详细信息，product_id作为主键，方便在订单处理等模块关联商品数据。stock字段用于记录商品库存，实时反映商品的可售数量。
3. 订单表（Orders）
   • 字段：
     • order_id（INTEGER PRIMARY KEY AUTOINCREMENT）：订单唯一标识，自增长。
     • user_id（INTEGER NOT NULL）：关联用户表的user_id，表示下单用户，通过外键约束关联。
     • order_date（TEXT NOT NULL）：订单创建日期，格式可设为YYYY - MM - DD HH:MM:SS。
     • total_amount（REAL NOT NULL）：订单总金额。
     • order_status（TEXT NOT NULL）：订单状态，如"pending"（待处理）、"completed"（已完成）、"cancelled"（已取消）。
   • 设计思路：记录订单的基本信息，通过user_id与Users表关联，确定下单用户。order_date记录订单创建时间，total_amount记录订单总金额，order_status跟踪订单状态。
4. 订单商品关联表（OrderProducts）
   • 字段：
     • order_product_id（INTEGER PRIMARY KEY AUTOINCREMENT）：关联记录唯一标识，自增长。
     • order_id（INTEGER NOT NULL）：关联订单表的order_id，通过外键约束关联。
     • product_id（INTEGER NOT NULL）：关联商品表的product_id，通过外键约束关联。
     • quantity（INTEGER NOT NULL）：该商品在订单中的数量。
   • 设计思路：此表用于建立订单与商品之间的多对多关系，一个订单可以包含多个商品，一个商品可以出现在多个订单中。通过记录quantity，可以知道每个订单中每种商品的具体数量。

二、数据库迁移方案

1. 版本控制
   • 在数据库中创建一个Version表，用于记录当前数据库版本号。
   • 字段：
     • version_id（INTEGER PRIMARY KEY AUTOINCREMENT）：版本记录唯一标识，自增长。
     • version_number（INTEGER NOT NULL）：数据库版本号。
2. 应用启动时检查版本
   • 在应用启动时，查询Version表获取当前数据库版本号。
   • 将当前版本号与应用期望的数据库版本号进行比较。
3. 迁移脚本
   • 针对每个版本升级，编写相应的SQL迁移脚本。
   • 示例：从版本1升级到版本2
     • 新增字段：假设在Users表中新增address字段。

     ALTER TABLE Users ADD COLUMN address TEXT;
     

     • 修改表结构：比如将Products表中的price字段的数据类型从REAL改为DECIMAL，需要先创建临时表，迁移数据，再删除原表并将临时表重命名。

     -- 创建临时表
     CREATE TABLE Products_temp (
         product_id INTEGER PRIMARY KEY AUTOINCREMENT,
         product_name TEXT NOT NULL,
         description TEXT,
         price DECIMAL NOT NULL,
         stock INTEGER NOT NULL
     );
     -- 迁移数据
     INSERT INTO Products_temp (product_id, product_name, description, price, stock)
     SELECT product_id, product_name, description, price, stock FROM Products;
     -- 删除原表
     DROP TABLE Products;
     -- 重命名临时表
     ALTER TABLE Products_temp RENAME TO Products;
     

     • 数据迁移：如果涉及数据结构变化导致数据需要转换，编写相应的SQL语句进行数据迁移。例如，假设订单状态字段order_status的值从"processing"改为"pending"，可以使用以下语句：

     UPDATE Orders SET order_status = "pending" WHERE order_status = "processing";
     

4. 执行迁移脚本
   • 如果当前数据库版本低于应用期望版本，按顺序执行相应的迁移脚本。
   • 每个迁移脚本执行完成后，更新Version表中的版本号。
5. 错误处理
   • 在执行迁移脚本过程中，捕获可能出现的SQL错误。
   • 如果发生错误，回滚当前正在执行的迁移操作，确保数据库状态不变，并向用户提示错误信息，引导用户采取相应措施（如联系技术支持）。