请选择 进入手机版 | 继续访问电脑版

sqlite3 多綫程/事務回滾/busy handler/簡單使用

菜鸡 发表于 2021-1-1 17:43:57 | 显示全部楼层 |阅读模式 打印 上一主题 下一主题
sqlite3 簡介及數據庫操纵

//SQL中文翻译为“结构化查询语言”,是单词"Structured Query Language"的缩写。SQL是一种操纵数据库的高级的非过程化编程语言,用户使用SQL下令通过DBMS对数据库内的数据举行访问。1987年,SQL得到国际标准组织ISO的支持成为数据库的国际标准,不外各种数据库的SQL下令略有差别,因此并不可以或许完全通用。
SQLite 是跨平台的,支持众多操纵系统,如 UNIX(Linux, Mac OS-X, Android, iOS)、Windows(Win32, WinCE, WinRT)
sqlite3 线程安全

(6) Is SQLite threadsafe?
Threads are evil. Avoid them.
SQLite is threadsafe. We make this concession since many users choose to ignore the advice given in the previous paragraph. But in order to be thread-safe, SQLite must be compiled with the SQLITE_THREADSAFE preprocessor macro set to 1. Both the Windows and Linux precompiled binaries in the distribution are compiled this way. If you are unsure if the SQLite library you are linking against is compiled to be threadsafe you can call the sqlite3_threadsafe() interface to find out.
SQLite is threadsafe because it uses mutexes to serialize access to common data structures. However, the work of acquiring and releasing these mutexes will slow SQLite down slightly. Hence, if you do not need SQLite to be threadsafe, you should disable the mutexes for maximum performance. See the threading mode documentation for additional information.
Under Unix, you should not carry an open SQLite database across a fork() system call into the child process.
** SQLite can be compiled with or without mutexes. When
** the [SQLITE_THREADSAFE] C preprocessor macro is 1 or 2, mutexes
** are enabled and SQLite is threadsafe. When the
** [SQLITE_THREADSAFE] macro is 0,
** the mutexes are omitted. Without the mutexes, it is not safe
** to use SQLite concurrently from more than one thread.
  多线程:这种模式下,只要一个数据库毗连不被多个线程同时使用就是安全的。源码中是启用bCoreMutex,禁用bFullMutex。实际上就是禁用数据库毗连和prepared statement(准备好的语句)上的锁,因此不能在多个线程中并发使用同一个数据库毗连或prepared statement。当SQLite编译时加了SQLITE_THREADSAFE=2参数时默认启用。若SQLITE_THREADSAFE不为0,可以在初始化SQLite前,调用sqlite3_config(SQLITE_CONFIG_MULTITHREAD)启用;大概在创建数据库毗连时,设置SQLITE_OPEN_NOMUTEX flag。
  串行:sqlite是线程安全的。启用所有的锁,包括bCoreMutex和bFullMutex 。因为数据库毗连和prepared statement都已加锁,所以多线程使用这些对象时没法并发,也就变成串行了。当SQLite编译时加了SQLITE_THREADSAFE =1参数时默认启用。若SQLITE_THREADSAFE不为0,可以在初始化SQLite前,调用sqlite3_config(SQLITE_CONFIG_SERIALIZED)启用;大概在创建数据库毗连时,设置SQLITE_OPEN_FULLMUTEX flag。
sqlite3 锁相关:https://www.sqlite.org/lockingv3.html
“线程安全”是指二个或三个线程可以同时调用独立的差别的sqlite3_open() 返回的"sqlite3"结构。而不是在多线程中同时使用同一个 sqlite3 结构指针。
一个sqlite3结构只能在调用 sqlite3_open创建它的谁人历程中使用。你不能在一个线程中打开一个数据库然后把指针传递给另一个线程使用。这是因为大多数多线程系统的限制(或 Bugs?)比方RedHat9上。在这些有问题的系统上,一个 线程创建的fcntl()锁不能由另一个线程删除或修改。由于SQLite依赖fcntl()锁来举行并发控制,当在线程间传递数据库毗连时会出现严重的问题。
在UNIX下,你不能通过一个 fork() 系统调用把一个打开的 SQLite 数据库放入子过程中,否则会出错。

  1. int sqlite3_open_v2(  const char *filename,   /* Database filename (UTF-8) */  sqlite3 **ppDb,         /* OUT: SQLite db handle */  int flags,              /* Flags */  const char *zVfs        /* Name of VFS module to use */);函数功能:These routines open an SQLite database file as specified by the filename argument. The filename argument is interpreted as UTF-8 for sqlite3_open() and sqlite3_open_v2() and as UTF-16 in the native byte order for sqlite3_open16(). A database connection handle is usually returned in *ppDb, even if an error occurs. The only exception is that if SQLite is unable to allocate memory to hold the sqlite3 object, a NULL will be written into *ppDb instead of a pointer to the sqlite3 object. If the database is opened (and/or created) successfully, then SQLITE_OK is returned. Otherwise an error code is returned. The sqlite3_errmsg() or sqlite3_errmsg16() routines can be used to obtain an English language description of the error following a failure of any of the sqlite3_open() routines.Whether or not an error occurs when it is opened, resources associated with the database connection handle should be released by passing it to sqlite3_close() when it is no longer required.The sqlite3_open_v2() interface works like sqlite3_open() except that it accepts two additional parameters for additional control over the new database connection. The flags parameter to sqlite3_open_v2() must include, at a minimum, one of the following three flag combinations:SQLITE_OPEN_READONLY    The database is opened in read-only mode. If the database does not already exist, an error is returned.SQLITE_OPEN_READWRITE    The database is opened for reading and writing if possible, or reading only if the file is write protected by the operating system. In either case the database must already exist, otherwise an error is returned.SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE    The database is opened for reading and writing, and is created if it does not already exist. This is the behavior that is always used for sqlite3_open() and sqlite3_open16().In addition to the required flags, the following optional flags are also supported:SQLITE_OPEN_URI    The filename can be interpreted as a URI if this flag is set.SQLITE_OPEN_MEMORY    The database will be opened as an in-memory database. The database is named by the "filename" argument for the purposes of cache-sharing, if shared cache mode is enabled, but the "filename" is otherwise ignored.SQLITE_OPEN_NOMUTEX    The new database connection will use the "multi-thread" threading mode. This means that separate threads are allowed to use SQLite at the same time, as long as each thread is using a different database connection.SQLITE_OPEN_FULLMUTEX    The new database connection will use the "serialized" threading mode. This means the multiple threads can safely attempt to use the same database connection at the same time. (Mutexes will block any actual concurrency, but in this mode there is no harm in trying.)SQLITE_OPEN_SHAREDCACHE    The database is opened shared cache enabled, overriding the default shared cache setting provided by sqlite3_enable_shared_cache().SQLITE_OPEN_PRIVATECACHE    The database is opened shared cache disabled, overriding the default shared cache setting provided by sqlite3_enable_shared_cache().SQLITE_OPEN_NOFOLLOW    The database filename is not allowed to be a symbolic linkIf the 3rd parameter to sqlite3_open_v2() is not one of the required combinations shown above optionally combined with other SQLITE_OPEN_* bits then the behavior is undefined.The fourth parameter to sqlite3_open_v2() is the name of the sqlite3_vfs object that defines the operating system interface that the new database connection should use. If the fourth parameter is a NULL pointer then the default sqlite3_vfs object is used.If the filename is ":memory:", then a private, temporary in-memory database is created for the connection. This in-memory database will vanish when the database connection is closed. Future versions of SQLite might make use of additional special filenames that begin with the ":" character. It is recommended that when a database filename actually does begin with a ":" character you should prefix the filename with a pathname such as "./" to avoid ambiguity.If the filename is an empty string, then a private, temporary on-disk database will be created. This private database will be automatically deleted as soon as the database connection is closed.
  1. int sqlite3_close(sqlite3*);int sqlite3_close_v2(sqlite3*);The sqlite3_close() and sqlite3_close_v2() routines are destructors for the sqlite3 object. Calls to sqlite3_close() and sqlite3_close_v2() return SQLITE_OK if the sqlite3 object is successfully destroyed and all associated resources are deallocated.
3.One-Step Execution Interface
  1. int sqlite3_exec(  sqlite3*,                                  /* An open database */  const char *sql,                           /* SQL to be evaluated */  int (*callback)(void*,int,char**,char**),  /* Callback function */  void *,                                    /* 1st argument to callback */  char **errmsg                              /* Error msg written here */);The sqlite3_exec() interface is a convenience wrapper around sqlite3_prepare_v2(), sqlite3_step(), and sqlite3_finalize(), that allows an application to run multiple statements of SQL without having to use a lot of C code.The sqlite3_exec() interface runs zero or more UTF-8 encoded, semicolon-separate SQL statements passed into its 2nd argument, in the context of the database connection passed in as its 1st argument. If the callback function of the 3rd argument to sqlite3_exec() is not NULL, then it is invoked for each result row coming out of the evaluated SQL statements. The 4th argument to sqlite3_exec() is relayed through to the 1st argument of each callback invocation. If the callback pointer to sqlite3_exec() is NULL, then no callback is ever invoked and result rows are ignored.If an error occurs while evaluating the SQL statements passed into sqlite3_exec(), then execution of the current statement stops and subsequent statements are skipped. If the 5th parameter to sqlite3_exec() is not NULL then any error message is written into memory obtained from sqlite3_malloc() and passed back through the 5th parameter. To avoid memory leaks, the application should invoke sqlite3_free() on error message strings returned through the 5th parameter of sqlite3_exec() after the error message string is no longer needed. If the 5th parameter to sqlite3_exec() is not NULL and no errors occur, then sqlite3_exec() sets the pointer in its 5th parameter to NULL before returning.If an sqlite3_exec() callback returns non-zero, the sqlite3_exec() routine returns SQLITE_ABORT without invoking the callback again and without running any subsequent SQL statements.If the 2nd parameter to sqlite3_exec() is a NULL pointer, a pointer to an empty string, or a pointer that contains only whitespace and/or SQL comments, then no SQL statements are evaluated and the database is not changed.Restrictions:The application must ensure that the 1st parameter to sqlite3_exec() is a valid and open database connection.The application must not close the database connection specified by the 1st parameter to sqlite3_exec() while sqlite3_exec() is running.The application must not modify the SQL statement text passed into the 2nd parameter of sqlite3_exec() while sqlite3_exec() is running.int (*callback)(void*,int,char**,char**),  /* Callback function */typedef int (*sqlite_callback)(void *para,int columnCount,char** columnValue, char** columnName);The 2nd argument to the sqlite3_exec() callback function is the number of columns in the result. The 3rd argument to the sqlite3_exec() callback is an array of pointers to strings obtained as if from sqlite3_column_text(), one for each column. If an element of a result row is NULL then the corresponding string pointer for the sqlite3_exec() callback is a NULL pointer. The 4th argument to the sqlite3_exec() callback is an array of pointers to strings where each entry represents the name of corresponding result column as obtained from sqlite3_column_name().para        :   由sqlite3_exec传入的参数指针,大概说是指针参数columnCount :   查询到的这一条纪录由多少个字段(多少列)columnValue :   该参数是双指针,查询出来的数据都保存在这里,它是一个1维数组,每一个元素都是一个char*,是一个字段内容,所以这个参数就可以不是单字节,而是可以为字符串等不定长度的数值,用字符串体现,以'\0'末端。别的需要特别注意的是:回调函数多数时候不是执行1次,而是会循环执行n次,当我们使用select举行sql功能时,往往输出的结果会是 多行,那么 有n行,就会执行n次的 回调函数。
4.Convenience Routines For Running Queries
  1. int sqlite3_get_table(  sqlite3 *db,          /* An open database */  const char *zSql,     /* SQL to be evaluated */  char ***pazResult,    /* Results of the query */  int *pnRow,           /* Number of result rows written here */  int *pnColumn,        /* Number of result columns written here */  char **pzErrmsg       /* Error msg written here */);void sqlite3_free_table(char **result);This is a legacy interface that is preserved for backwards compatibility. Use of this interface is not recommended.Definition: A result table is memory data structure created by the sqlite3_get_table() interface. A result table records the complete query results from one or more queries.The table conceptually has a number of rows and columns. But these numbers are not part of the result table itself. These numbers are obtained separately. Let N be the number of rows and M be the number of columns.A result table is an array of pointers to zero-terminated UTF-8 strings. There are (N+1)*M elements in the array. The first M pointers point to zero-terminated strings that contain the names of the columns. The remaining entries all point to query results. NULL values result in NULL pointers. All other values are in their UTF-8 zero-terminated string representation as returned by sqlite3_column_text().A result table might consist of one or more memory allocations. It is not safe to pass a result table directly to sqlite3_free(). A result table should be deallocated using sqlite3_free_table().As an example of the result table format, suppose a query result is as follows:Name        | Age-----------------------Alice       | 43Bob         | 28Cindy       | 21There are two columns (M==2) and three rows (N==3). Thus the result table has 8 entries. Suppose the result table is stored in an array named azResult. Then azResult holds this content:azResult[0] = "Name";azResult[1] = "Age";azResult[2] = "Alice";azResult[3] = "43";azResult[4] = "Bob";azResult[5] = "28";azResult[6] = "Cindy";azResult[7] = "21";The sqlite3_get_table() function evaluates one or more semicolon-separated SQL statements in the zero-terminated UTF-8 string of its 2nd parameter and returns a result table to the pointer given in its 3rd parameter.After the application has finished with the result from sqlite3_get_table(), it must pass the result table pointer to sqlite3_free_table() in order to release the memory that was malloced. Because of the way the sqlite3_malloc() happens within sqlite3_get_table(), the calling function must not try to call sqlite3_free() directly. Only sqlite3_free_table() is able to release the memory properly and safely.The sqlite3_get_table() interface is implemented as a wrapper around sqlite3_exec(). The sqlite3_get_table() routine does not have access to any internal data structures of SQLite. It uses only the public interface defined here. As a consequence, errors that occur in the wrapper layer outside of the internal sqlite3_exec() call are not reflected in subsequent calls to sqlite3_errcode() or sqlite3_errmsg().

  1. 常用语句/*创建表格*//*CREATE TABLE IFNOT EXISTS t_student(id INTEGER PRIMARY KEY AUTOINCREMENT,  name TEXT, score REAL);*//*CREATE TABLE IFNOT EXISTS t_student(id INTEGER PRIMARY KEY AUTOINCREMENT,  name TEXT, score REAL, PRIMARY KEY (id));*//*CREATE TABLE IFNOT EXISTS t_student(id INTEGER PRIMARY KEY AUTOINCREMENT,  name TEXT, score REAL);*//*删除数据*//*DROP TABLE IF EXISTS t_student;*//*插入数据*//*INSERT INTO t_student(name, score) VALUES(&#39;hxj&#39;,55);INSERT INTO t_student( score, name) VALUES(&#39;kk&#39;,55);INSERT INTO t_student(name, score) VALUES(&#39;gf&#39;,55);INSERT INTO t_student(name, score) VALUES(&#39;rr&#39;,55);*//*INSERT INTO t_student(name, score) VALUES(&#39;huangxiao0&#39;,70);INSERT INTO t_student(name, score) VALUES(&#39;huangxiao1&#39;,98);*//*更新数据*//*UPDATE t_student SET name = &#39;MJ&#39;, score = 30;*//*删除数据*//*DELETE FROM t_student;*//*条件语句的使用*//*UPDATE t_student SET score = 100 WHERE name = &#39;huangxiao0&#39;;UPDATE t_student SET name = &#39;jack&#39; WHERE score < 40;*//*DELETE FROM t_student WHERE score < 100;DELETE FROM t_student ;*/select max(score) from t_student;select name from sqlite_master where type =&#39;table&#39; and name = &#39;%s&#39;;/*查询语句*//*查询name,属性和score属性的*//*SELECT name, score FROM t_student;*//*SELECT * FROM t_student;*/ /*查询所有的语句*//*SELECT * FROM t_student WHERE score < 50;*//*起别名*//*SELECT name AS myname, score AS myscore FROM t_student;*//*SELECT name xxname, score yyscore FROM t_student;*//*SELECT stu.name, stu.score FROM t_student AS stu;*//*SELECT stu.name, stu.score FROM t_student  stu;*//* *号是统配符 *//*统计*//*SELECT count(*) FROM t_student WHERE score > 80;*//*排序*//*默认是升序 ASC是升序  DESC是降序 *//*SELECT  * FROM t_student ORDER BY score;*//*SELECT  * FROM t_student ORDER BY score DESC;*//*SELECT  * FROM t_student ORDER BY score ASC;*//*SELECT  * FROM t_student ORDER BY score DESC , id DESC;*//*分页*//*SELECT *FROM t_student LIMIT 10,10;*//*SELECT *FROM t_student LIMIT 10;*//*简朴约束*/CREATE TABLE IFNOT EXISTS t_student(id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,  name TEXT NOT NULL, score REAL DEFAULT 2);/*外键的使用*//*创建一个班级的表格*//*CREATE TABLE if NOT EXISTS t_class (id INTEGER PRIMARY KEY AUTOINCREMENT, nametext NOT NULL ); *//*创建学生与班级举行关联*//*CREATE TABLE if NOT EXISTS t_student (id INTEGER PRIMARY KEY AUTOINCREMENT, nametext NOT NULL, age integer NOT NULL ,class_id integer NOT NULL ,CONSTRAINT fk_student_class_id_class_id FOREIGN KEY (class_id) REFERENCES t_class(id)  );*//*多表查询,记得在背面在约束条件来查询,答案才正确*/SELECT s.name, c.name FROM t_student s, t_class c WHERE s.class_id = c.id;
busy handler

  1. #define SQLITE_BUSY_MAX_RETRY                 10#define SQLITE_BUSY_USLEEP_TIME          500000 /* 0.5s */staticint32_t_SqliteBusyHandler(    void* Udp,    int Count    ){    int32_t ret = 0;    if (Count commit transaction</p> [code]#define SQLITE_NO_ERR_MSG "No errMsg"        int ret = LW_SUCCESS;    bool needRollBack = false;    sqlite3 * db = NULL;    char * errMsg = NULL;        ret = sqlite3_open_v2("temp.db", &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);    if (ret != SQLITE_OK)    {        ret = -ret;        goto CommonReturn;    }    ret = sqlite3_busy_handler(db, _SqliteBusyHandler, (void *)db);    if (ret != SQLITE_OK)    {        ret = -ret;        goto CommonReturn;    }    needRollBack = TRUE;    ret = sqlite3_exec(db, "begin transaction", NULL, NULL, &errMsg);    if (ret != SQLITE_OK)    {        ret = -ret;        printf("Begin transaction failed (ret: %d %s)\n",            ret, errMsg == NULL ? LW_SQLITE_NO_ERR_MSG : errMsg);        goto CommonReturn;    }    ret = sqlite3_exec(db, "CREATE TABLE if not exists "xxx" ("enable" blob NOT NULL,"content" TEXT NOT NULL);", NULL, NULL, &errMsg);    if (ret != SQLITE_OK)    {        ret = -ret;        printf("create table failed (ret: %d %s)\n",            ret, errMsg == NULL ? LW_SQLITE_NO_ERR_MSG : errMsg);        goto CommonReturn;    }    ret = sqlite3_exec(db, xxx, NULL, NULL, &errMsg);    if (ret != SQLITE_OK)    {        ret = -ret;        printf("xxx failed (ret: %d %s)\n",            ret, errMsg == NULL ? LW_SQLITE_NO_ERR_MSG : errMsg);        goto CommonReturn;    }    ret = sqlite3_exec(db, "commit transaction", NULL, NULL, &errMsg);    if (ret != SQLITE_OK)    {        ret = -ret;        printf("commit transaction failed (ret: %d %s)\n",            ret, errMsg == NULL ? LW_SQLITE_NO_ERR_MSG : errMsg);        goto CommonReturn;    }    needRollBack = FALSE;CommonReturn:    if (needRollBack)    {        /*ignore the return value*/        sqlite3_exec(db, "rollback transaction", NULL, NULL, &errMsg);        printf("rollback transaction failed (%s)\n",            errMsg == NULL ? SQLITE_NO_ERR_MSG : errMsg);    }    {        /*Passing a NULL pointer to sqlite3_close_v2/sqlite3_close_v2 is harmless.*/        sqlite3_free(errMsg);        sqlite3_close_v2(db);    }

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册






27428564@qq.com 在线QQ咨询


Powered by Discuz! X3.4© 2001-2013 Comsenz Inc.( 蜀ICP备2021001884号-1 )