以下数据库操作说明以test_app组件的t_account表为例，定义如下 model.json配置:

{
    "Account": {
        "tableName": "t_account",
        "tableType": "PoweroffPer",
        "properties": {
            "Id": {
                "baseType": "U8",
                "primaryKey": true
            },
            "UserName": {
                "baseType": "String"
            },
            "Items": {
                "baseType": "Array",
                "items": {
                    "baseType": "String"
                }
            }
        }
    }
}

表格结构:

1 使用Statement对象操作数据库 ​

使用db:select, db:insert, db:update, db:delete方法可以创建Statement对象

1.1 查询 select ​

1.1.1 获取结果方式 ​

1.1.1.1 :first() ​

通过db:select(Table对象):first()查询符合条件的第一条数据： 数据存在时返回row对象，可以直接通过.字段名获取字段值； 数据不存在时返回nil。

local user1 = db:select(db.Account):where(db.Account.Id:eq(1)):first()
print(user1.UserName) -- name1

1.1.1.2 :all() ​

通过db:select(Table对象):all()查询所有符合条件的数据： 返回值是row对象数组（数据不存在时为空）。

local users = db:select(db.Account):all()
print(#users) -- 2
print(users[1].UserName) -- name1
print(users[2].UserName) -- name2

1.1.1.3 :fold() ​

通过db:select(Table对象):fold(回调函数)对查询到的所有row对象执行回调：

local users = {}
db:select(db.Account):fold(function(user)
    print(user.UserName)
    users[#users + 1] = user
end)

1.1.2 对查询结果进行排序 ​

:order_by(列对象, 是否降序) 默认是升序（从小到大），可以按多个列排序

-- 按用户Id升序、用户名降序排序
local users = db:select(db.Account):order_by(db.Account.Id):order_by(db.Account.UserName, true):all()

1.1.3 对查询结果进行截取 ​

跳过x条数据之后截取前y条 :limit(y):offset(x) limit必须在offset前面；limit可以单独使用，offset不能单独使用

-- 查询Id排列第3到第5的用户
local users = db:select(db.Account):order_by(db.Account.Id):limit(3):offset(2):all()

1.1.4 查询表格当前数据行数 ​

框架已对SELECT COUNT(*) FROM t_xxx查询表格数据量的逻辑进行封装，组件直接调用表对象的get_count方法就可以获取到当前有多少条数据

local row_count = db.Account:get_count()

1.2 插入 insert ​

-- 插入一条数据
db:insert(db.Account):value({Id = 3, UserName = 'name3'}):exec()
-- 插入多条数据
local users = {
    {Id = 3, UserName = 'name3'},
    {Id = 4, UserName = 'name4'}
}
db:insert(db.Account):values(users):exec()

1.3 修改 update ​

-- 将Id为1的用户名称改为new_name
db:update(db.Account):value({UserName = 'new_name'}):where({Id = 1}):exec()
-- 将所有用户名称改为abc
db:update(db.Account):value({UserName = 'abc'}):exec()

1.4 删除 delete ​

-- 删除Id为1的用户
db:delete(db.Account):where({Id = 1}):exec()

1.4.1 整表删除 truncate ​

-- 删除用户表所有数据
db.Account:truncate()

• 本地持久化场景下，建议调用truncate进行整表删除，提高性能。 由于框架使用sqlite的钩子机制对数据库操作进行检查（flash写保护状态、数据库大小上限和分区余量），而钩子是逐条数据触发的。 调用delete进行删除时，没删除一条数据就会触发一遍钩子的处理；而truncate接口通过删除过程中给表打标记的方式跳过钩子，只对整个操作做一次检查，能缩短整表删除的耗时，在数据量大的情况下缩短耗时效果显著。

1.5 where条件 ​

通过调用Statement对象的:where()方法可以添加条件。支持字典模式、数组模式和Condition模式

1.5.1 字典模式 ​

where方法传入{字段名 = 字段值}形式的lua表，表示数据需要与传入的所有字段值匹配

local users = db:select(db.Account):where({UserName = 'name1'}):all() -- 查询所有UserName为name1的用户
db:delete(db.Account):where({Id = 1, UserName = 'name1'}):exec() -- 删除所有Id为1, UserName为name1的用户

1.5.2 数组模式 ​

where方法传入{{字段名, 字段值}}形式的lua表，表示数据需要与传入的所有字段值匹配

local users = db:select(db.Account):where({{'UserName', 'name1'}}):all() -- 查询所有UserName为name1的用户
db:delete(db.Account):where({{'Id', 1}, {'UserName', 'name1'}}):exec() -- 删除所有Id为1, UserName为name1的用户

1.5.3 Condition模式 ​

where方法传入一个或多个Condition对象，表示数据需要满足所有传入的条件

local statement = require 'database.statement'
local or_ = statement.or_
-- 将Id为1的用户名称修改为new_name: UPDATE t_account SET UserName='new_name' WHERE Id=1
local users = db:update(db.Account):value({UserName = 'new_name'}):where(db.Account.UserName:eq('name1')):exec()
-- 查询Id在2和5之间的用户: SELECT * FROM t_account WHERE Id >= 2 AND Id <= 5
local users = db:select(db.Account):where(db.Account.Id:ge(2), db.Account.Id:le(5)):all()
-- 查询Id为1,3,5的用户: SELECT * FROM t_account WHERE Id IN (1, 3, 5)
local users = db:select(db.Account):where(db.Account.Id:in_(1, 3, 5)):all()
-- 删除UserName以name开头或者Id大于3的用户: DELETE FROM t_account WHERE UserName LIKE 'name%' OR Id > 3
db:delete(db.Account):where(or_(db.Account.UserName:like('name%'), db.Account.Id:gt(3))):exec()

• 使用:like()进行模糊匹配时，如果数据量庞大（超过上万条）可能会耗时较长，并且导致CPU占用率升高

2 使用row对象操作数据库 ​

2.1 创建row对象 ​

通过Table对象({字段名 = 字段值})创建row对象。 对象创建时会从数据库查询与入参匹配的数据，不存在时新增一条数据。 匹配逻辑：如果入参包含数据表所有主键字段，则按主键值进行匹配；否则按入参的所有字段值进行匹配。

row对象通过__newindex元方法捕获字段赋值操作，并自动把字段值更新到数据库。

local user = db.Account({Id = 3}) -- 如果不存在Id=3的用户，这里会插入一条Id=3的新数据
print(user.UserName) -- 如果存在Id=3的用户，可以直接访问它的字段值;如果不存在，这里获取到的是nil
user.UserName = 'abc' -- 字段赋值会自动执行update，更新到数据库

• 判断数据库中是否存在某条数据时，应该用db:select，不应该用此方法。这种方式创建的row对象永远不可能是nil，而且创建时可能会有插入数据操作

• 如果字段值是lua表类型，需要在修改后赋值给row对象，否则修改不会生效

local user = db.Account({Id = 3})
local items = user.Items
items[1] = "new_item"
user.Items = items -- 修改items后需要重新赋值给user.Items，否则修改不会生效

2.2 :save() ​

用以上方法触发的插入和更新操作，是通过发送on_need_save信号，由ORM事务机制每隔1秒周期性保存到数据库。 对数据库落盘即时性要求高的场景，还需要调用:save()方法确保数据已经保存

local user = db.Account({Id = 3})
user.UserName = 'name3'
user:save() -- 如果调用save则会等待ORM事务处理完毕

• 如果对很多字段进行修改，建议在对象所有字段修改完毕后再调用save，每个字段赋值后都调用save会导致增加耗时
• 调用save时，如果出现违反数据库约束会抛错（例如主键值冲突、插入数据非空无默认值字段没有赋值、flash写保护打开等场景）

2.3 :delete() ​

local user = db.Account({Id = 3})
user:delete() -- 删除Id=3的用户

3. 对象属性赋值自动持久化 ​

对于配置了持久化的对象属性，使用ORM机制可以在设置属性值后自动持久化

obj.prop = value

【注意事项】

• 如果属性值是lua表，修改表内元素后必须重新把表重新赋值给属性，否则不会触发持久化

因为框架通过lua表的__newindex元方法捕获属性值的修改，必须要有直接赋值的动作才能被感知到

obj.prop = {'test1'} -- obj对象的prop属性值是lua表

-- 直接修改表内元素不会持久化!
table.insert(obj.prop, 'test2')

-- 建议写成这样：
local value = obj.prop
table.insert(value, 'test2')
obj.prop = value -- 必须把修改后的表赋值给属性，才会触发持久化

3.1 自发现对象 ​

如果对象至少有一个属性在MDS定义的usage包含CSR，则该对象是自发现对象

自发现对象需要在注册对象添加回调之前开启ORM特性，对象属性赋值才会自动持久化

在调用mc.mdb.object_manage的on_add_object之前，添加以下代码

local orm_object_manage = require 'mc.orm.object_manage'
orm_object_manage.get_instance(self.db, self.bus):start()

组件调用on_add_object注册回调时，第2个参数是对象添加回调，第3个参数是预处理回调

local object_manage = require 'mc.mdb.object_manage'
object_manage.on_add_object(self.bus, function(class_name, object, position)
    -- 对象添加回调
    ...
end, function(object)
    -- 预处理回调
    ...
end)

【注意事项】

• 业务组件需要在预处理回调中给对象补全主键属性值，框架根据主键值从持久化数据库中找到与对象匹配的数据进行恢复

• 预处理回调中的属性赋值动作不会持久化

• libmc4lua/1.70.38以上版本，对象添加回调中的属性赋值会持久化（在对象成功保存到内存数据库后触发）

如果因为不同对象之间主键值冲突等原因，导致对象添加失败（日志打印AddObject XXX failed），则对象添加回调中的属性赋值不会持久化

3.2 非自发现对象 ​

非自发现对象除了开启ORM特性之外，还需要需要自行定义ORM对象的创建逻辑： 在组件初始化时查询内存数据库并根据查询结果创建资源树对象；实现create_mdb_object方法，在每次插入数据库时创建对象 代码大致如下，其中CreateAccount是自动生成在service.lua中的函数

local app_service = require 'test_app.service'
local c_object = require 'mc.orm.object'
local test_app = Class(app_service)
local c_account = c_object('Account')
function c_account.create_mdb_object(user)
    return test_app:CreateAccount(function(obj)
        obj.Id = user.Id
        obj.UserName = user.UserName
        obj.Items = user.Items
    end)
end

test_app.db:select(test_app.db.Account):fold(function(user)
    local mdb_obj = c_account.create_mdb_object(user)
    c_account.new(mdb_obj, 0, user.__datas)
end)

4 注意事项 ​

4.1 涉及一次性大于5条的数据操作，建议使用事务机制批量提交 ​

批量提交指的是用db:tx(回调函数)的方式，将操作放到回调函数中。 db:tx使用sqlite的事务机制，将回调中的所有数据库操作作为一个事务处理，具体实现为： 先执行BEGIN，再执行回调函数。如果回调中的数据库操作执行成功，则执行COMMIT将修改写入数据库；如果执行失败则执行ROLLBACK回滚操作。

用这种方法可以减少批量操作的耗时，在批量插入的场景也可以减少flash写入量。 db:tx()方法在操作失败时会抛错，建议使用pcall调用

local ok, err = pcall(db.db.tx, db.db, function()
    for i = 1, 100 do
        db.Account({Id = i, UserName = 'name' .. i}):save()
    end
end)

• 用这种方式批量操作中如果有一个操作失败会回滚全部操作，建议将相关性强的操作放到一起

4.2 远程持久化不应该修改主键的值 ​

远程持久化是按主键值进行数据存取的，修改了主键值相当于新增了一条数据，原来那条数据还会存在 （远程持久化指的是MDS类定义没有配置tableLocation: Local）

4.3 数据库操作的异常处理 ​

在lua代码中调用数据库操作函数（包括db:prepare, db:exec, db:insert, db:update, db:delete, db:tx）时，操作不成功会抛错。所以组件在使用时需要根据使用场景做好异常处理。 如果不希望数据库操作失败中断当前代码流程，则需要确保在调用处或者外层函数使用pcall捕获异常。特别需要注意以下场景：

• 组件初始化过程中的数据库操作必须在pcall中调用，避免操作失败导致整个组件服务启动异常

• 数据库操作失败后需要做一些善后处理，例如关闭文件句柄

• 处理业务功能的常驻协程，在数据库操作失败后不希望协程退出

4.3.1 异常场景 ​

4.3.1.1 调用sqlite库函数返回的异常 ​

1. 违反数据库约束 sql语句的执行违反数据库约束时，框架会抛出sqlite库报的错误信息，常见错误信息包括：

• UNIQUE constraint failed：违反主键/唯一键约束，通常是因为插入的数据中配置了primaryKey/uniqueKey的字段值与表中已有数据重复
• NOT NULL constraint failed：违反非空键约束，通常是因为插入的数据中配置了notAllowNull的字段值为空，或者主键值为空
• no such table 或 no such column：表或字段在数据库中不存在
• database is locked： 数据库被锁，通常是因为多处打开了同一个数据库造成的访问冲突
• datatype mismatch：数据类型不匹配，例如插入的数据中，数字类型的字段设置了字符串类型的值
• syntax error： 调用db:prepare时出现的sql语法错误
• Incorrect number of bindings supplied： 绑定参数数量不对，通常是where条件使用不当，:eq()等条件传入了nil值
• invalid data type for bind： 绑定参数数据类型不对，通常是where条件使用不当，:eq()等条件传入了数据类型错误的值 where条件写法参考本篇wiki的1.5章节

2. 访问数据库文件的权限不足，或者数据库损坏 访问数据库文件的权限不足时，sqlite数据库报错attempt to write a readonly database； 数据库损坏时，sqlite数据库报错database disk image is malformed或者file is not a database

3. 文件系统读写错误 文件系统读写异常时，sqlite数据库报错disk I/O error

4.3.1.2 数据库写操作被框架检查机制拦截 ​

框架通过sqlite的钩子机制对数据库写操作进行检查，检查不通过时拒绝写入并返回异常。检查项如下：

4.3.1.2.1 flash写保护开启 ​

flash写保护开启时，为了保护flash寿命，框架通过sqlite的钩子机制拦截掉电持久化数据库的写入，这时数据库操作会抛错constraint fail

4.3.1.2.2 数据库文件大小达到上限 ​

当数据库大小达到上限时，框架通过sqlite的钩子机制拦截数据库的写入，这时数据库操作会抛错。 不同持久化类型的数据库大小限制如下（本地持久化是计算同一目录下所有本地持久化db文件大小总和）：

4.3.1.2.3 分区空间余量不足 ​

当数据库所在分区的空间剩余容量小于10%时，框架通过sqlite的钩子机制拦截数据库的写入，这时数据库操作会抛错。 不同持久化类型对应的分区见上表。

4. 数据表存储的记录行数达到tableMaxRows配置的上限 如果组件在MDS中配置了tableMaxRows字段（表中允许存储的最大记录数量），并且没有注册回调，或者注册的回调没有将数据量清理到配置的上限之下，插入数据时会抛错。 tableMaxRows机制的具体介绍可在文档《MDS持久化配置说明》中查看

5. 常见问题 ​

5.1 能否使用rowid判断数据的插入先后顺序 ​

不能。因为sqlite默认会复用删掉的rowid，rowid大的记录不一定是后插入的。参考文档 框架目前没有跟踪每条数据插入时间和更新时间的机制，业务有需要时须自行增加字段进行标记

6. 数据库可靠性 ​

6.1 远程持久化 ​

1. 远程掉电持久化数据库会周期性进行备份

备份数据库路径为/data/backup/persistence/per_poweroff.db

备份周期根据数据库大小进行动态调整如下：

< 200KB 2小时
>= 200KB  4小时
>= 500KB  8小时
>= 1MB 12小时
>= 2MB 24小时

2. 对于MDS配置了critical: true的远程掉电持久化属性，每次更新会实时同步备份。
3. 打开数据库前先检查文件头部（sqlite数据库头部16字节为固定序列，与序列不匹配时进行恢复）。
4. 远程掉电持久化主数据库丢失或者损坏时，从备份数据库进行恢复。

6.2 本地持久化 ​

1. 打开数据库前先检查文件头部（sqlite数据库头部16字节为固定序列，与序列不匹配时进行恢复）。
2. 如果数据库损坏，则使用sqlite的recover机制尝试进行修复。recover机制会尽量恢复原有数据，但根据数据库损坏程度，可能存在部分数据丢失
3. 如果修复失败则删除原有数据库，重新创建空的数据库，避免数据库损坏导致组件启动异常。