网站建设功能需求文档,wordpress 绿色版,长沙人才招聘市场,四川建设网共享平台git库
sqlite
从官网下载
https://www.sqlite.org/download.html
Source Code 里面下载
解压以后有四个文件#xff1a;
Sqlite3基本需求使用sqlite3.h/.c #xff0c;其中shell.c可以编译出可以执行文件#xff0c;另外一个头文件是用于扩展#xff0c;外部接口导入。运…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_ptrstd::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_uniquestd::string(url_to_heaven), 3 };
auto insertedId storage.insert(user);返回用户id或者抛出异常 如果要指明id用replace
查找
try{auto user storage.getUser(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_pointerUser(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.removeUser(insertedId)参数是id而不是整个对象
遍历所有对象 get.all()
auto allUsers storage.get_allUser();
cout allUsers ( allUsers.size() ): endl;
for(auto user : allUsers) {cout storage.dump(user) endl;
}默认返回一个vector容器可以指明返回list auto allUsersList storage.get_allUser, std::list(); get.all() 内存开销较大可以逐行遍历
for(auto user : storage.iterateUser()) {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::vectordecltype(Visit::doctor_id)// SELECT doctor_id
// FROM visits
// WHERE LENGTH(patient_name) 11
get0(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_allUser(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 LIKEANDOR 查询某列成员
// 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::vectorint
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::get0(t);auto lastName std::get1(t);cout firstName lastName endl;
}ORDER BY
// SELECT * FROM users WHERE id 250 ORDER BY first_name
auto orderedUsers2 storage.get_allUser(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.getTable_DEV_CMD_NAME_POLL(2);secondUser.CMD_NAME RRRR1;storage.update(secondUser);terminate called after throwing an instance of std::system_errorwhat(): Not found
./build_project.sh行 63: 65589 已放弃 核心已转储 ./Build/bin/antctrlproto添加Transactions 效果如下一样会闪退。 auto commited storage.transaction([]() mutable{auto secondUser storage.getTable_DEV_CMD_NAME_POLL(2);secondUser.CMD_NAME RRRR1;storage.update(secondUser);auto gottaRollback bool(rand() % 2);if (gottaRollback){ // dummy condition for testreturn false; // exits lambda and calls ROLLBACK}// exits lambda and calls COMMITreturn true; });terminate called after throwing an instance of std::system_errorwhat(): Not found添加try catch 可以解决软件闪退 auto commited storage.transaction([]() mutable{try{auto secondUser storage.getTable_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 testreturn false; // exits lambda and calls ROLLBACK}// exits lambda and calls COMMITreturn true; });press anykey to running antctrlproto!2022-03-21 16:47:45,277:[ERROR] get FAILE :Not found
Commit failed, process an error
rootlkt-VirtualBox:~/gitlab/xdevice-platform-newbranch_test/xdevice-platform/test/OpenXlsx# 性能测试
测试工程路径放到xdevice工程下了
xdevice-platform/test/sqlite_orm_testinsert 插入
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条大概耗时6while (key ! 0){storage.insert(TJ_GSCJ_DEVINFO); //大概在4-8mskey--;}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-8msstorage.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;}}
}QA
Q1:如果同一个结构体生成了多份表格如何指定表格名字进行查询
sqlite_orm看example中都是通过结构体名来对数据库进行增删查改。表格名字这个参数只在创建表格的时候用一下。
目前没有找到通过结构体名表格名的方式进行指定查询的情况。
也没有看到明确的说法说不行。把example下面的例子都看了没有我想要的方法。
sqlite_orm 结构体名和数据库的表格是11对应的。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
