· 8 years ago · Mar 22, 2018, 08:50 AM
1
2#include <stdio.h>
3#include <stdlib.h>
4#include <string.h>
5#include <pthread.h>
6#include <unistd.h>
7#include "sqlite3.h"
8
9char *orgName[] = {
10 "yun j ji yunji yunj yji yj z zh zha zhao q qi qia qian d du dua duan zhaoqian zhaoq zhqian zhq zqian zq qianduan qiand qduan qd zhaoqianduan zhaoqiand zhaoqduan zhaoqd zhqianduan zhqiand zhqduan zhqd zqianduan zqiand zqduan zqd",
11 "murren m mu l lu lun mulun mul mlun ml",
12 "l lu m ma man luman lum lman lm",
13 "d di din ding d di din ding f fu w wu dingding dingd dding dd dingfu dingf dfu df fuwu fuw fwu fw dingdingfu dingdingf dingdfu dingdf ddingfu ddingf ddfu ddf dingfuwu dingfuw dingfwu dingfw dfuwu dfuw dfwu dfw dingdingfuwu dingdingfuw dingdingfwu dingdingfw dingdfuwu dingdfuw dingdfwu dingdfw ddingfuwu ddingfuw ddingfwu ddingfw ddfuwu ddfuw ddfwu ddfw l li x xi xia lixia lix lxia lx",
14 "lucy y ya yan x xi yanxi yanx yxi yx",
15 "p pe pen peng l le lei penglei pengl plei pl lucy"
16};
17char *nickName[] = {
18 "ab",
19 "bc",
20 "cd",
21 "de",
22 "ef",
23 "fg"
24};
25int id[] = { 21078,21218,21125,53234,40824,164873 };
26
27void prepareSchema_prefix(sqlite3 *db) {
28 char *schemas[] = {
29 "PRAGMA journal_mode=WAL;",
30 "drop table if exists tbl;",
31 "drop table if exists tbl_fts;",
32 "create table if not exists tbl(name text, uid int primary key, nick text);",
33 "create virtual table if not exists tbl_fts USING fts5(name, uid);",
34 "create trigger if not exists trigger_insert_tbl after insert on tbl \
35 begin \
36 replace into tbl_fts(rowid, name, uid) values(new.rowid,new.name,new.uid); \
37 end;",
38 "create trigger if not exists trigger_update_tbl after update on tbl \
39 begin \
40 replace into tbl_fts(rowid, name, uid) values(new.rowid,new.name,new.uid);\
41 end;"
42 };
43 int i, j, rc, cnt;
44 cnt = sizeof(schemas) / sizeof(char*);
45 char *errMsg;
46 for (i = 0; i < cnt; i++) {
47 rc = sqlite3_exec(db, schemas[i], NULL, NULL, &errMsg);
48 if (rc != SQLITE_OK&&rc != SQLITE_DONE) {
49 printf("prepareSchema error: %s\n", errMsg);
50 return;
51 }
52 }
53 printf("prepareSchema over\n");
54}
55
56/*
57** Return a pseudo-random integer between 0 and (nMax-1), inclusive.
58*/
59int randomint(int nMax){
60 unsigned int x;
61 sqlite3_randomness(sizeof(unsigned int), (void*)&x);
62 return (int)(x % nMax);
63}
64
65void fts5_prefix_match_test() {
66 sqlite3 *db;
67 int i, j, k, rc;
68 char *errMsg;
69 char *append = "abcdefghijklmnopqrstuvwxyz";
70 sqlite3_open("a.db", &db);
71 prepareSchema_prefix(db);
72 int test_cnt = 300, rc_copy = 0;
73 char zSql[2048];
74 char *tmp;
75 const int len_zSql = 2047;
76 int len_left;
77 int nRow = 0, nCol = 0;
78 char **pazResult;
79 const int trx_cnt = 10000;
80 int trx_idx;
81 for (trx_idx = 0; trx_idx < 10000; trx_idx++) {
82 for (i = 0; i < test_cnt; i++) {
83 j = randomint(6);
84 len_left = len_zSql;
85 tmp = zSql;
86 if (j < 4) {
87 int uid = id[j] + randomint(300);
88 rc_copy = snprintf(tmp, len_left, "insert into tbl values(\"%s%c\",%d,\"%s%c\");",
89 orgName[j], append[randomint(26)], uid, nickName[j], append[randomint(26)]);
90 //printf("%s\n", zSql);
91 rc = sqlite3_exec(db, zSql, NULL, NULL, &errMsg);
92 if (rc != SQLITE_OK&&rc != SQLITE_DONE) {
93 if (rc != SQLITE_CONSTRAINT) {
94 printf("error %s: %s\n", zSql, errMsg);
95 continue;
96 }
97 else {
98 rc_copy = snprintf(tmp, len_left, "update tbl set name=\"%s%c\" where uid=%d\n",
99 orgName[j], append[randomint(26)], uid);
100 rc = sqlite3_exec(db, zSql, NULL, NULL, &errMsg);
101 if (rc != SQLITE_OK&&rc != SQLITE_DONE) {
102 printf("error %s: %s\n", zSql, errMsg);
103 continue;
104 }
105 }
106 }
107 }
108 else {
109 rc_copy = snprintf(zSql, len_zSql, "select * from tbl where uid=%d;", id[j]);
110 rc = sqlite3_get_table(db, zSql, &pazResult, &nRow, &nCol, &errMsg);
111 if (nRow == 0) {
112 rc_copy = snprintf(tmp, len_left, "insert into tbl(name, uid, nick) values(\"%s\",%d,\"%s\");",
113 orgName[j], id[j], nickName[j]);
114 }
115 else {
116 rc_copy = snprintf(zSql, len_zSql, "update tbl set name=\"%s\" where uid=%d;",
117 orgName[j], id[j]);
118 }
119 rc = sqlite3_exec(db, zSql, NULL, NULL, &errMsg);
120 if (rc != SQLITE_OK&&rc != SQLITE_DONE) {
121 printf("error %s: %s\n", zSql, errMsg);
122 continue;
123 }
124 }
125 }
126 printf("insert 300 records\n");
127 sqlite3_sleep(200);
128 }
129}
130
131static void *writeFunc(void *pIn) {
132 fts5_prefix_match_test();
133 printf("write over\n");
134 return 0;
135}
136
137static void *readFunc(void *pIn) {
138 sqlite3 *db;
139 int i, j, rc;
140 char *errMsg;
141 char **pazResult;
142 int nRow, nCol;
143 sqlite3_sleep(2000);
144 rc = sqlite3_open("a.db", &db);
145 if (rc != SQLITE_OK) {
146 printf("open db error\n");
147 return 0;
148 }
149 char *zSql = "select rowid, * from tbl_fts where tbl_fts match \'lucy*\';";
150 while (1) {
151 rc = sqlite3_get_table(db, zSql, &pazResult, &nRow, &nCol, &errMsg);
152 if (nRow > 2) {
153 printf("mis-match\n");
154 for (i = 0; i <= nRow; i++) {
155 for (j = 0; j < nCol; j++) {
156 printf("%s\t", pazResult[i*nCol + j]);
157 }
158 printf("\n");
159 }
160 return 0;
161 }
162 else {
163 printf("match cnt = %d\n", nRow);
164 }
165 sqlite3_free_table(pazResult);
166 sqlite3_sleep(3000);
167 }
168 return 0;
169}
170#define THREAD_NUM 2
171
172typedef void*(*ThreadFuncType)(void*);
173
174int thread(ThreadFuncType writeFunc, ThreadFuncType readFunc) {
175 pthread_t handle[THREAD_NUM];
176 memset(handle, 0, sizeof(handle));
177
178 if (writeFunc) {
179 pthread_create(&handle[0], 0, writeFunc, 0);
180 }
181 if (readFunc) {
182 for (int i = 1; i < THREAD_NUM; i++) {
183 pthread_create(&handle[1], 0, readFunc, 0);
184 sleep(5);
185 }
186 if (writeFunc) {
187 pthread_join(handle[0], 0);
188 }
189 else {
190 pthread_join(handle[1], 0);
191 }
192 printf("multithread are all over now.\n");
193 }
194 else {
195 pthread_join(handle[0], 0);
196 printf("multithread are all over now.\n");
197 }
198 return 0;
199}
200
201int main() {
202 thread(writeFunc, readFunc);
203 return 0;
204}