Sqlite3 瞎捣鼓笔记

目录

首先我要安利一个免费开源的工具软件,sqlitestudio.用来查看sql数据库的db文件的.
界面是Qt做的,漂亮,而且功能丰富实用.中文化程度高.
里面的sql编辑器可以直接写sql操作数据库.带语法检查.
这样你执行之后得到了什么结果就能一眼看出来.
(记得点击刷新按钮,不然看到的是上次的结果)
这年头,没个可视化还算什么数据库工具.有图:

##################正文#####################

一个朋友的一个课后作业,从文本文件依次读n阶字符串,写入数据库,统计每个字符串出现的次数与条件概率和联合概率 只统计文件中的英文字符,并统一转成大写字母

假如样本为:abcd9abcdefg

则统计的字符串为
0阶 a b c d a b c d e f g
1阶 ab bc cd da ab bc cd de ef fg
2阶 abc bcd cda dab abc bcd cde def efg
….
联合概率: 该字符串在文件中出现的次数/所有字符串的统计次数
条件概率: 该字符串在本阶的联合概率/ 该字符串前n-1个字符组成的字符串,在上一阶的联合概率

另一个用于概率计算演示的样本: aB2cA4b9789

由样本生成的0阶表

key	count	joinprobility	conprobility
A	2	0.4	NULL(0阶没有)
B	2	0.4	NULL(0阶没有)
C	1	0.2	NULL(0阶没有)

由样本生成的1阶表

key	count	joinprobility	conprobility
AB	2	0.5 (2/(2+1+1))	1.25 (0.5/0.4)
BC	1	0.25 (1/(2+1+1))	0.625 (0.25/0.4)
CA	1	0.25 (同上)	1.25 (0.25/0.2)

* 括号里的内容是不在数据库里的

Sqlite3 数据库SELECT INSERT UPDATE

初步方案是,先将文本文件解析并写到数据库里,只写key(主键)和count字段.
然后通过sql语句,由数据库计算联合概率和条件概率.
首先想到的是先 SELECT 查找该记录是否已存在,如果已存在则执行 UPDATE 否则执行 INSERT.

const char* table_name = "mytable"; // 表名前缀

/*查找记录回调函数
 *有查找结果时才会触发该回调函数,用于显示查询结果
 *这里没有使用具体的查询结果,只要该函数被调用就说明该记录已存在.直接置1
 */
int find_key_callback(void* data, int count, char** value, char** name)
{
    *(int*)data = 1;
    return 0;
}

/*查找记录是否已经存在
 *db 数据库句柄
 *order 当前阶数
 *str 当前阶数长度的字符串
 */
int find_key(sqlite3* db, int order, char* str)
{
    int is_exist = 0;   // 该字符串是否存在数据库中(1,存在. 0,不存在)
    char* err = NULL;
    char sql[1024] = { 0 };
    sprintf(sql, "SELECT key FROM %s%d WHERE key='%s'", table_name, order, str);
    int res = sqlite3_exec(db, sql, find_key_callback, &is_exist, &err);
    if (res != SQLITE_OK)
    {
        ERROR_MESSAGE(sqlite3_errstr(res));
        return -1;
    }
    return is_exist;
}

/*更新对应阶数(order)的表中的数据(str)*/
int update_key(sqlite3* db, int order, char* str)
{
    char* err = NULL;
    char sql[1024] = { 0 };
    sprintf(sql, "UPDATE %s%d SET count=count+1 WHERE key='%s'", table_name, order, str);
    int res = sqlite3_exec(db, sql, NULL, NULL, &err);
    if (res != SQLITE_OK)
    {
        ERROR_MESSAGE(sqlite3_errstr(res));
        return -1;
    }
    return 0;
}
/*向对应阶数(order)的表里插入新数据(str)*/
int insert_key(sqlite3* db, int order, char* str)
{
    char* err = NULL;
    char sql[1024] = { 0 };
    sprintf(sql, "INSERT INTO %s%d (key,count) VALUES ('%s',1)", table_name, order, str);
    int res = sqlite3_exec(db, sql, NULL, NULL, &err);
    if (res != SQLITE_OK)
    {
        ERROR_MESSAGE(sqlite3_errstr(res));
        return -6;
    }
    return 0;
}

因此我们向数据库写入一条记录的程序就变成了这样

int input_record(sqlite3* db, size_t order, char* str)
{
    if (find_key(db, order, str))    // 该记录是否已经存在数据库中
    {
        update_key(db, order, str);  // 已存在,则更新count = count+1
    }
    else
    {
        insert_key(db, order, str);  // 不存在,则插入str,同时将count设为1
    }
    return 0;
}

这个方法有个弊端,无论记录存不存在,都执行了两条sql语句.
下面针对sqlite3的C语言接口函数进行优化:
直接 INSERT 如果记录已存在,则sqlite3_exec函数会返回一个错误码,那我们只要在执行完 INSERT之后,根据sqlite3_exec的返回值来决定是否执行 UPDATE
如此一来就不需要 SELECT 了,新的代码为

int input_record(sqlite3* db, size_t order, char* str)
{
    char* err = NULL;
    char sql[256] = { 0 };
    sprintf(sql, "INSERT INTO %s%d (key,count) VALUES ('%s',1)", table_name, order, str);
    int res = sqlite3_exec(db, sql, NULL, NULL, &err);
    if (res == SQLITE_CONSTRAINT)  // 已有记录,则 INSERT 失败,改为执行 UPDATE 操作
    {
        sprintf(sql, "UPDATE %s%d SET count=count+1 WHERE key ='%s'", table_name, order, str);
        res = sqlite3_exec(db, sql, NULL, NULL, &err);
    }
    if (res != SQLITE_OK)
    {
        ERROR_MESSAGE(sqlite3_errstr(res));
        return -3;
    }
    return 0;
}

如此一来,在最佳情况下,只会执行一条 INSERT 语句.

Sqlite3 数据库UPDATE OF 触发器,INSERT 触发器

Sqlite3 sum函数求某记录的比例

联合概率的计算可以通过sqlite的触发器来完成

/* 更新触发器
 * 检测到 count字段更新动作,自动计算联合概率,更新joinprobility字段
 */
int update_trigger(sqlite3* db, int order)
{
    char* err = NULL;
    char sql[1024] = { 0 };
    char table[256] = { 0 };
    // 需要注意一点,更新触发器有两种模式,一种是对任何记录的更新都会触发
    // 另一种是 只有指定的字段被更新才会触发
    // 这里使用第二种,只有count被更新才会触发 触发器
    // 因为我们在更新触发器里执行了更新动作,
    // 使用第一种触发方式会导致触发器自触发,sqlite对此会提供相关的报错信息.
    sprintf(table, "%s%d", table_name, order);
    sprintf(sql, "CREATE TRIGGER update_trigger%d "\
            "AFTER UPDATE OF count "\
            "ON %s "\
            "BEGIN "\
            "UPDATE %s "\
            "SET joinprobility = (count * 1.0 / (SELECT sum(count)FROM %s)); "\
            "END;", order, table, table, table);
    int res = sqlite3_exec(db, sql, NULL, NULL, &err);
    if (res != SQLITE_OK)
    {
        ERROR_MESSAGE(sqlite3_errstr(res));
        return -6;
    }
    return 0;
}

/* 插入触发器*/
int insert_trigger(sqlite3* db, int order)
{
    char* err = NULL;
    char sql[1024] = { 0 };
    char table[256] = { 0 };

    sprintf(table, "%s%d", table_name, order);
    sprintf(sql, "CREATE TRIGGER insert_trigger%d "\
            "AFTER INSERT "\
            "ON %s "\
            "BEGIN "\
            "UPDATE %s "\
            "SET joinprobility = (count * 1.0 / (SELECT sum(count)FROM %s)); "\
            "END;", order, table, table, table);
    int res = sqlite3_exec(db, sql, NULL, NULL, &err);
    if (res != SQLITE_OK)
    {
        ERROR_MESSAGE(sqlite3_errstr(res));
        return -6;
    }
    return 0;
}

Sqlite3 substr字符串截取函数,获取未知长度的前n-1个字符

最后计算条件概率,条件概率需要一个sql字符串截取函数,截取一串长度为n的字符的前n-1位,n未知.而且涉及两个表的数据

int update_conprobility(sqlite3* db, int order)
{
    char* err = NULL;
    char sql[256] = { 0 };

    if (order != 0) // 不计算第0阶的联合概率
    {
        sprintf(sql, "UPDATE %s%d SET conprobility ="\
                "(joinprobility*1.0/"\
                "(SELECT joinprobility FROM %s%d WHERE"\
                "key = substr(%s%d.key,1,length(%s%d.key)-1)))", \
                table_name, order, table_name, order - 1, \
                table_name, order, table_name, order);
        int res = sqlite3_exec(db, sql, NULL, NULL, &err);
        if (res != SQLITE_OK)
        {
            ERROR_MESSAGE(sqlite3_errstr(res));
            return -1;
        }

        sprintf(sql, "UPDATE %s%d SET conprobility = 1.0 "\
                    "WHERE conprobility > 1", table_name, order);// 数据修复,大于1的置1 
        res = sqlite3_exec(db, sql, NULL, NULL, &err);
        if (res != SQLITE_OK)
        {
            ERROR_MESSAGE(sqlite3_errstr(res));
            return -1;
        }
    }
    return 0;
}

至此基本功能完成.
结果一个388KB的txt文件, 获取0~10阶的数据足足跑了一晚上.
这让我很不开心

于是我去掉了触发器,因为每一个插入,更新记录,都会执行触发器计算,速度肯定会有影响.
同时改成了多线程,
首先通过fopen打开同一个文件10次,获取10个只读的文件句柄
然后建立10个线程同时操作一个数据库句柄,负责向该数据库中的10个表同时写数据
结果处理速度并没有明显提高,因为数据库都是线程安全的,内部肯定是有加锁处理的.
就在苦恼之时,发现了sqlite3有内存数据库

Sqlite3 创建内存数据库

获取内存数据库句柄可以按照和文件数据库一样的方法来操作.

sqlite3_open(":memory:",db);

而且内存数据库是没有锁的,而我这个需求每个线程操作一个表,表与表之间基本没有关联性,而且线程只负责写,因次,不需要线程锁.内存数据库换上之后,速度飞快.
原来需要一晚上的数据 现在50分钟就跑完了.
需要注意的一点是,内存数据库在内存里,退出就没了.

Sqlite3 导出内存数据库到文件

需要将sqlite3的内存数据库导出到文件数据库,
在sqlite3的官网有一段例程,去看网页右下角的 loadOrSaveDb 函数,
网上有人中文翻译过,可以去搜一下
第一个参数是内存数据库句柄,第二个参数是文件地址,第三个参数用来设置是导出还是导入.