sqlite_orm学习笔记

git库

sqlite

从官网下载
https://www.sqlite.org/download.html
Source Code 里面下载
解压以后有四个文件：
Sqlite3基本需求使用sqlite3.h/.c ，其中shell.c可以编译出可以执行文件，另外一个头文件是用于扩展，外部接口导入。

运行sqliteshell 可以用于数据库的简单测试：
?	shell命令基本都是以.作为开始的，例如”.help” 查看帮助信息
?	SQL语句是以”;”结束的，例如”create table student(id integer,name text,age integer,score integer); ”

sqlite_orm

git
https://github.com/fnc12/sqlite_orm
函数api
https://github.com/fnc12/sqlite_orm/wiki/storage_t::transaction

使用前需要先准备好sqlite的文件
sqlite的文件：sqlite3.c sqlite3.h sqlite3ext.h
sqlite_orm只用到了一个sqlite_orm.h文件

调用的时候需要提供支持库 pthread和dl否则编译不过

	target_link_libraries ( ${CMAKE_PROJECT_NAME} PRIVATE pthread dl openxlsx)

使用笔记

适用于C++的SQLite数据库的orm,更多的用法参考git路径下的example里面的代码

以下笔记参考下面连接基本的增删查改

https://blog.csdn.net/sdut_jk17_zhangming/article/details/107610438

创建表

表结构

struct User{
    int id;
    std::string firstName;
    std::string lastName;
    int birthDate;
    std::unique_ptr<std::string> imageUrl;
    int typeId;
};

struct UserType {
    int id;
    std::string name;
};

SQL语句

SQL语句

CREATE TABLE users (id integer primary key autoincrement, first_name text not null, last_name text not null, birth_date integer not null, image_url text, type_id integer not null)

CREATE TABLE user_types (id integer primary key autoincrement, name text not null DEFAULT 'name_placeholder')

sqlite_orm语句

sqlite_orm语句

using namespace sqlite_orm;
auto storage = make_storage("db.sqlite",
                            make_table("users",
                                       make_column("id", &User::id, autoincrement(), primary_key()),
                                       make_column("first_name", &User::firstName),
                                       make_column("last_name", &User::lastName),
                                       make_column("birth_date", &User::birthDate),
                                       make_column("image_url", &User::imageUrl),
                                       make_column("type_id", &User::typeId)),
                            make_table("user_types",
                                       make_column("id", &UserType::id, autoincrement(), primary_key()),
                                       make_column("name", &UserType::name, default_value("name_placeholder"))));

在创建表时不需要指明表的列的变量类型，在创建时会通过传入的参数指针进行确定
对于私有数据类型，可以通过创建setter和getter进行赋值和访问
创建表时要指明存储的文件名，如“db.sqlite" 和表名 如 “users”，“user_types”，若要存储在内存里，文件名为":memory:"或者为空
可为列添加的属性，如自增autoincrement(),主键 primary_key(),初始值default_value(“0”)

CRUD

插入 insert

User user{-1, "Jonh", "Doe", 664416000, std::make_unique<std::string>("url_to_heaven"), 3 };
auto insertedId = storage.insert(user);

返回用户id或者抛出异常
如果要指明id，用replace（）

查找

try{
    auto user = storage.get<User>(insertedId);
    cout << "user = " << user.firstName << " " << user.lastName << endl;
}catch(std::system_error e) {
    cout << e.what() << endl;
}catch(...){
    cout << "unknown exeption" << endl;
}```

或者

if(auto user = storage.get_pointer<User>(insertedId)){
    cout << "user = " << user->firstName << " " << user->lastName << endl;
}else{
    cout << "no user with id " << insertedId << endl;

第一种失败会抛出异常，第二种成功返回std::unique_ptr 失败返回nullptr

修改 updata

user.firstName = “Nicholas”;
user.imageUrl = “https://cdn1.iconfinder.com/data/icons/man-icon-set/100/man_icon-21-512.png”
storage.update(user);

根据id修改某一行的为主属性值

storage.update_all(set(c(&User::lastName) = "Hardey",
                       c(&User::typeId) = 2),
                   where(c(&User::firstName) == "Tom"));

修改符合条件的行

删除 remove

根据id修改某一行的为主属性值
storage.update_all(set(c(&User::lastName) = “Hardey”,
c(&User::typeId) = 2),
where(c(&User::firstName) == “Tom”));

修改符合条件的行
删除 remove

storage.remove<User>(insertedId)

参数是id，而不是整个对象

遍历所有对象 get.all()

auto allUsers = storage.get_all<User>();
cout << "allUsers (" << allUsers.size() << "):" << endl;
for(auto &user : allUsers) {
    cout << storage.dump(user) << endl; 
}

默认返回一个vector容器，可以指明返回list auto allUsersList = storage.get_all<User, std::list>();
get.all() 内存开销较大，可以逐行遍历

for(auto &user : storage.iterate<User>()) {
    cout << storage.dump(user) << endl;
}

复杂查询

//  SELECT doctor_id
//  FROM visits
//  WHERE LENGTH(patient_name) > 8
auto selectStatement = storage.prepare(select(&Visit::doctor_id, where(length(&Visit::patient_name) > 8)));
cout << "selectStatement = " << selectStatement.sql() << endl;  //  prints "SELECT doctor_id FROM ..."
auto rows = storage.execute(selectStatement); //  rows is std::vector<decltype(Visit::doctor_id)>

//  SELECT doctor_id
//  FROM visits
//  WHERE LENGTH(patient_name) > 11
get<0>(selectStatement) = 11;
auto rows2 = storage.execute(selectStatement);

聚合函数

avg（） 平均值 .avg()
count（） 统计值 .count()
MAX（） 最大值 .max() return std::unique_ptr
MIN（） 最小值 min() return std::unique_ptr
SUM（） 求和 return std::unique_ptr
TOTAL()

条件查询

auto id5and7 = storage.get_all<User>(where(c(&User::id) <= 7 and c(&User::id) >= 5 and not (c(&User::id) == 6)));
cout << "id5and7 count = " << id5and7.size() << endl;
for(auto &user : id5and7) {
    cout << storage.dump(user) << endl;
}

可以使用=, !=, >, >=, <, <=, IN, BETWEEN ，LIKE，AND，OR
查询某列成员

//  SELECT id FROM users WHERE last_name = 'Doe'
auto doeIds = storage.select(&User::id, where(c(&User::lastName) == "Doe"));
cout << "doeIds count = " << doeIds.size() << endl; //  doeIds is std::vector<int>
for(auto &doeId : doeIds) {
    cout << doeId << " ";
}
cout << endl;

查询若干列

查询若干列

//  `SELECT first_name, last_name FROM users WHERE id > 250 ORDER BY id`
auto partialSelect = storage.select(columns(&User::firstName, &User::lastName),
                                    where(c(&User::id) > 250),
                                    order_by(&User::id));
cout << "partialSelect count = " << partialSelect.size() << endl;
for(auto &t : partialSelect) {
    auto &firstName = std::get<0>(t);
    auto &lastName = std::get<1>(t);
    cout << firstName << " " << lastName << endl;
}

ORDER BY

//  `SELECT * FROM users WHERE id < 250 ORDER BY first_name`
auto orderedUsers2 = storage.get_all<User>(where(c(&User::id) < 250), order_by(&User::firstName));
cout << "orderedUsers2 count = " << orderedUsers2.size() << endl;
for(auto &user : orderedUsers2) {
    cout << storage.dump(user) << endl;
}

Transactions （事物）

其保护作用
use transaction function which begins transaction implicitly and takes a lambda argument which returns true for commit and false for rollback. All storage calls performed in lambda can be commited or rollbacked by returning true or false.
最后一句话有说lambda表达式返回true就会自动提交
林哥测试过，提交与回滚都是自动生效的
新的疑问，那么在lambda表达式中，如果数据库操作出现报错，那么软件是否会闪退呢？

当前表格中没有指定ID2 。没有Transactions 保护直接运行,软件闪退。

    storage.sync_schema(); //这句话会在数据库中创建表格，如果没有sqlite文件则创建该文件
    auto secondUser = storage.get<Table_DEV_CMD_NAME_POLL>(2);
    secondUser.CMD_NAME = "RRRR1";
    storage.update(secondUser);

terminate called after throwing an instance of 'std::system_error'
  what():  Not found
./build_project.sh：行 63: 65589 已放弃               （核心已转储） ./Build/bin/antctrlproto

添加Transactions 效果如下，一样会闪退。

    auto commited = storage.transaction([&]() mutable
                                        {
        auto secondUser = storage.get<Table_DEV_CMD_NAME_POLL>(2);
        secondUser.CMD_NAME = "RRRR1";
        storage.update(secondUser);
        auto gottaRollback = bool(rand() % 2);
        if (gottaRollback)
        {                 //  dummy condition for test
            return false; //  exits lambda and calls ROLLBACK
        }
         //  exits lambda and calls COMMIT
        return true; });

terminate called after throwing an instance of 'std::system_error'
  what():  Not found

添加try catch 可以解决软件闪退

    auto commited = storage.transaction([&]() mutable
                                        {
        try
        {
            auto secondUser = storage.get<Table_DEV_CMD_NAME_POLL>(2);
            secondUser.CMD_NAME = "RRRR1";
            storage.update(secondUser);
        }
        catch (const std::system_error &e)
        {
            LOG(ERROR) << "get FAILE :" << e.what();
        }

        auto gottaRollback = bool(rand() % 2);
        if (gottaRollback)
        {                 //  dummy condition for test
            return false; //  exits lambda and calls ROLLBACK
        }
         //  exits lambda and calls COMMIT
        return true; });


press anykey to running antctrlproto!

2022-03-21 16:47:45,277:[ERROR] get FAILE :Not found
Commit failed, process an error
root@lkt-VirtualBox:~/gitlab/xdevice-platform-newbranch_test/xdevice-platform/test/OpenXlsx# 

性能测试

测试工程路径，放到xdevice工程下了

xdevice-platform/test/sqlite_orm_test

insert 插入

1.插入1000条数据，耗时6S

  TJ_GSCJ_DEVINFO.ID = 3;
    TJ_GSCJ_DEVINFO.DEV_NAME = "TJ_GSCJ";
    TJ_GSCJ_DEVINFO.DEV_CMD_TABLE_NAME = "TJ_GSCJ_CMDTABLE";
    TJ_GSCJ_DEVINFO.COMUNITATE_MODE = "TCP_SERVER";
    TJ_GSCJ_DEVINFO.TCP_IP = "127.0.0.1";
    TJ_GSCJ_DEVINFO.TCP_PORT = "5000";
    TJ_GSCJ_DEVINFO.UDP_IP_SRC = "Jameqs";
    TJ_GSCJ_DEVINFO.UDP_PORT_SRC = "Jameqs";
    TJ_GSCJ_DEVINFO.UDP_PORT_DST = "Jameqs";
    TJ_GSCJ_DEVINFO.UDP_IP_DST = "Jameqs";
    TJ_GSCJ_DEVINFO.UDPMC_IP = "Jameqs";
    TJ_GSCJ_DEVINFO.UDPMC_IP_RX = "Jameqs";
    TJ_GSCJ_DEVINFO.UDPMC_IP_TX = "Jameqs";
    TJ_GSCJ_DEVINFO.UDPMC_PORT_RX = "Jameqs";
    TJ_GSCJ_DEVINFO.UDPMC_PORT_TX = "Jameqs";


printf_init_log("start  sqlite_orm_write_test");
    int key = 1000; //写1000条大概，耗时6
    while (key != 0)
    {
        storage.insert(TJ_GSCJ_DEVINFO); //大概在4-8ms
        key--;
    }
    printf_init_log("#############################start  read sqlite_orm_write_test");

2.单条数据插入时间

  storage.replace(SQLORM_XPRO_DEVINFO{1, "James", "James", "Houston", "Houston", "Houston", "Houston", "Houston", "Houston", "Houston", "Houston", "Houston", "Houston", "Houston", "Houston"}); //大概在6-8ms

  storage.insert(TJ_GSCJ_DEVINFO);  //大概在4-8ms

读取

1.读取耗时

同一时间只有一个线程在做读取

        //  读取次数     耗时
        //  90000        57-58ms
        // 一次读6000    4-5ms
        // 1000          2m
        // 500           1m
printf_init_log("#############################start  read sqlite_orm_write_test");
auto simpleRows = storage.select(columns(&SQLORM_XPRO_DEVINFO::ID, &SQLORM_XPRO_DEVINFO::DEV_NAME, &SQLORM_XPRO_DEVINFO::DEV_CMD_TABLE_NAME)); //几乎不耗时 小于1ms
printf_init_log("#############################end read  sqlite_orm_write_test");

2.一对一的读取

循环读取,不带延时。读取正常，没有闪退

void *thread_GSCJQ1(void *arg)
{
    while (1)
    {
        LOG(INFO) << "#############################start  read sqlite_orm_write_test";
        try
        {
            auto simpleRows = storage1.select(columns(&SQLORM_XPRO_DEVINFO::ID, &SQLORM_XPRO_DEVINFO::DEV_NAME, &SQLORM_XPRO_DEVINFO::DEV_CMD_TABLE_NAME)); //几乎不耗时 小于1ms
        }
        catch (const std::system_error &e)
        {
            cout << "11111111111" << e.what() << endl;
        }
    }
}

3.多对一的读取

起两个线程，循环读取,不带延时。读取正常，没有闪退

注意，两个线程的句柄需要不同。

void *thread_GSCJQ1(void *arg)
{
    while (1)
    {
        LOG(INFO) << "#############################start  read sqlite_orm_write_test";
        try
        {
            auto simpleRows = storage1.select(columns(&SQLORM_XPRO_DEVINFO::ID, &SQLORM_XPRO_DEVINFO::DEV_NAME, &SQLORM_XPRO_DEVINFO::DEV_CMD_TABLE_NAME)); //几乎不耗时 小于1ms
        }
        catch (const std::system_error &e)
        {
            cout << "11111111111" << e.what() << endl;
        }
    }
}


void *thread_GSCJQ2(void *arg)
{
    while (1)
    {
        LOG(TRACE) << "__________________start  read sqlite_orm_write_test";
        try
        {
            auto simpleRows = storage2.select(columns(&SQLORM_XPRO_DEVINFO::ID, &SQLORM_XPRO_DEVINFO::DEV_NAME, &SQLORM_XPRO_DEVINFO::DEV_CMD_TABLE_NAME)); //几乎不耗时 小于1ms
        }
        catch (const std::system_error &e)
        {
            cout << "22222222" << e.what() << endl;
        }
    }
}

Q&A

Q1:如果同一个结构体生成了多份表格，如何指定表格名字进行查询

sqlite_orm看example中都是通过结构体名来对数据库进行增删查改。表格名字这个参数只在创建表格的时候用一下。
目前没有找到通过结构体名+表格名的方式进行指定查询的情况。
也没有看到明确的说法说不行。

把example下面的例子都看了，没有我想要的方法。
sqlite_orm  结构体名和数据库的表格是1：1对应的。

Q2: wiki上说了，在事务结束的时候让我们调用提交commit（） 或者回滚，但是林哥的代码里面没有调用提交。

use transaction function which begins transaction implicitly and takes a lambda argument which returns true for commit and false for rollback. All storage calls performed in lambda can be commited or rollbacked by returning true or false.
最后一句话有说lambda表达式返回true就会提交
林哥测试过，提交与回滚都是自动生效的
新的疑问，那么在lambda表达式中，如果数据库操作出现报错，那么软件是否会闪退呢？

## 附录

1.vscode 查看sqlite表格，还不错

https://blog.csdn.net/weixin_43739167/article/details/113843871