1 /*
  2     build in VS2013
  3     1.open_db 2.create_table 3.start transaction 
  4     4.1 sqlite3_prepare_v2() 4.2 sqlite3_bind_XXX() 4.3 sqlite3_step 4.4 sqlite3_reset 
  5     6.end transation 
  6     4.5 sqlite3_finalize(stmt);
  7     7.close_db
  8 */
  9 extern "C"
 10 {
 11 #include "../../sqlite-amalgamation-3320100/sqlite3.h"
 12 }
 13 #include <stdio.h>
 14 #include <stdlib.h>
 15 #include <windows.h>
 16 
 17 #define TRANSACTION_ON
 18 
 19 #ifdef TRANSACTION_ON
 20 #define START_TRANSACTION() sqlite3_exec(db,"begin transaction;",NULL,NULL,NULL)
 21 #define END_TRANSACTION() sqlite3_exec(db,"commit transaction;",NULL,NULL,NULL)
 22 #else
 23 #define START_TRANSACTION()
 24 #define END_TRANSACTION()
 25 #endif
 26 
 27 typedef enum{
 28     INSERT,
 29     SELECT,
 30     UPDATE,
 31     DEL
 32 }SQL_OPTYPE;
 33 
 34 typedef struct stEmployee{
 35     unsigned int id;
 36     unsigned int age;
 37     char registertime[26];
 38     float salary;
 39 }Employee;
 40 
 41 static unsigned int g_employeeId = 0;
 42 
 43 sqlite3* open_db(const char* dbName){
 44     sqlite3* db = nullptr;
 45     if (dbName){
 46         if (SQLITE_OK != sqlite3_open(dbName, &db)){    //SQLITE_OK is 0
 47             printf("Open %s fail\r\n", dbName);
 48             return nullptr;
 49         }
 50     }
 51     return db;
 52 }
 53 
 54 int close_db(sqlite3* db){
 55     if (db) return sqlite3_close(db);
 56     return -1;
 57 }
 58 
 59 int create_table(sqlite3* db, const char* tableName){
 60     int ret = -1;    
 61     char* errMsg;
 62 
 63     char* sqlFmt = "CREATE TABLE %s (id INTEGER PRIMARY KEY,\
 64                                      age INTEGER, 65                                      registertime VARCHAR(26), 66                                      salary REAL);";
 67     int cmdLen = strlen(sqlFmt) + strlen(tableName) + 1;
 68     char* sqlCmd = (char*)malloc(cmdLen);
 69     if (!sqlCmd) return -1;
 70     memset(sqlCmd, 0, cmdLen);
 71     sprintf_s(sqlCmd, cmdLen, sqlFmt, tableName);
 72     ret = sqlite3_exec(db, sqlCmd, NULL, NULL, &errMsg);
 73     if (SQLITE_OK != ret){
 74         printf("Create table fail %s \r\n", errMsg);        
 75     }
 76     free(sqlCmd);
 77     return ret;
 78 }
 79 
 80 void init_employee(Employee* pEmployee)
 81 {
 82     memset(pEmployee, 0, sizeof(*pEmployee));
 83     
 84     pEmployee->id = g_employeeId++;
 85     pEmployee->age = 20;
 86     sprintf_s(pEmployee->registertime, sizeof("2020-05-28"), "2020-05-28");
 87     pEmployee->salary = float(1234.56);
 88 }
 89 
 90 char * get_sqlCmd(const char* tableName,SQL_OPTYPE opType,Employee employee)
 91 {
 92     char * insertFmt = "INSERT INTO %s values(%d, %d, ‘%s‘,%10.6f);";
 93     char * selectFmt = "SELECT * FROM %s where id <= %d;";
 94     char * updateFmt = "UPDATE %s set age=%d,registerTime=‘%s‘,salary=%10.6f where id=%d;";
 95     char * deleteFmt = "DELETE FROM %s where id=%d;";
 96 
 97     int cmdLen = strlen(insertFmt) + strlen(tableName) + sizeof(employee);
 98     char * sqlCmd = (char*)malloc(cmdLen);
 99 
100     switch (opType)
101     {
102     case INSERT:
103         sprintf_s(sqlCmd, cmdLen, insertFmt, tableName, employee.id, employee.age, employee.registertime, employee.salary);
104         break;
105     default:
106         break;
107     }
108 
109     return sqlCmd;
110 }
111 int test_transaction(sqlite3* db ,const char* tableName,SQL_OPTYPE optType, int count)
112 {
113     int ret = -1;
114     int failCount = 0;
115 
116     Employee employee;
117     char* sqlcmd = NULL;
118     char* errMsg = NULL;
119 
120     //start transaction
121     START_TRANSACTION();
122     for (int i = 0; i < count; i++){
123         init_employee(&employee);
124         sqlcmd = get_sqlCmd(tableName, optType, employee);
125         if (!sqlcmd){
126             failCount++;
127             continue;
128         }
129         ret = sqlite3_exec(db, sqlcmd, NULL, NULL, &errMsg);
130         if (SQLITE_OK != ret){
131             failCount++;
132             printf("Execute sql fail%s", sqlcmd);
133         }
134     }
135     if (sqlcmd)    free(sqlcmd);
136     if (errMsg) sqlite3_free(errMsg);
137 
138     //close transation
139     END_TRANSACTION();
140     return ret;
141 }
142 int test(const char* dbName, const char* tableName,143          SQL_OPTYPE optType, unsigned int count, int isTableExist)
144 {
145     int ret = -1;    
146     
147     long t1 = GetTickCount();
148     //open db
149     sqlite3* db = open_db(dbName);
150     if (!db) return ret;
151 
152     //create table
153     if (!isTableExist){
154         ret = create_table(db, tableName);
155         if (SQLITE_OK != ret){
156             close_db(db);
157             return ret;
158         }
159     }
160     //ret = test_transaction(db, tableName, optType, count);
161     START_TRANSACTION();
162     char buffer[] = "INSERT INTO testTable VALUES(?1,?2,?3,?4)";
163     sqlite3_stmt* stmt;
164     sqlite3_prepare_v2(db, buffer, strlen(buffer), &stmt, NULL);
165     for (unsigned int i = 0; i < count; i++){
166         sqlite3_bind_int(stmt, 1, g_employeeId++);
167         sqlite3_bind_int(stmt, 2, 20);
168         sqlite3_bind_text(stmt, 3, "2020-5-29", strlen("2020-5-29"), SQLITE_STATIC);
169         sqlite3_bind_double(stmt, 4, 12345.67);
170     
171         if (sqlite3_step(stmt) != SQLITE_DONE){            
172             printf("Commit failed!\r\n");
173         }
174         sqlite3_reset(stmt);
175     }
176     ret = END_TRANSACTION();
177     sqlite3_finalize(stmt);
178 
179     //close db
180     close_db(db);
181     long t2 = GetTickCount();
182     printf("Time cost:%dms\r\n",t2 - t1);
183 
184     return ret;
185 }
186 
187 int main()
188 {
189     int ret               = -1;
190     unsigned int count = 10000;    
191     bool isTableExist  = true;
192 
193     char* dbName    = "testDB.db";
194     char* tableName = "testTable";
195 
196     ret = test(dbName, tableName, SQL_OPTYPE::INSERT, count, isTableExist);
197     if (SQLITE_OK != ret)
198     {
199         printf("Test failed!\r\n");
200     }
201     system("pause");
202     
203     return ret;
204 }