SQLITE3学习笔记1

发布时间：2020-12-12 19:34:43 所属栏目：百科来源：网络整理

导读：SQLITE是一款轻量级的开源数据库，在嵌入式设备中得到了广泛的应用。整个数据库引擎都是用纯c实现，因此具有很好的跨平台型，虽然他的整个代码编译后只是200k左右，但是却可以操作高达2T的数据，紧凑而高效。SQLITE的一大好处就是在程序内部不需要网络配置，

SQLITE是一款轻量级的开源数据库，在嵌入式设备中得到了广泛的应用。整个数据库引擎都是用纯c实现，因此具有很好的跨平台型，
虽然他的整个代码编译后只是200k左右，但是却可以操作高达2T的数据，紧凑而高效。SQLITE的一大好处就是在程序内部不需要网络配置，
也不需要管理,因为客户端和服务器在同一进程空间运行。并且它的权限只依赖于文件系统，没有用户帐户的概念。
它允许所需要的内存和其它开销都很很小，十分适合于资源紧张的嵌入式设备。虽然sqlite是一个开源数据库引擎，但是它的每一个发布版本，
都经过了严格的测试，例如压力测试、100%的分之测试、上百万的测试用例、内存溢出测试、I/O出错测试、系统崩溃和断电测试、边界值测试、
回归测试、异常数据库测试、大量的使用assert()和runtime检查等。并且SQLite3支持事务，即使在系统崩溃或者突然断电的情况下SQLite仍然可以
保持原子性、一致性、隔离性和持久性（ACID）。因此你需要做的仅仅是把它正确的编译到你的程序。
SQLITE的获取
登陆sqlite的官方网站 http://www.sqlite.org/，大家根据需要，下载自己想要的版本，我所用的是未编译的带有源代码的最新版本的sqlite-amalgamation-3071000，
下载下来之后，将解压后的sqlite.h/c两个文件加入自己的工程当中（我所用的开发环境VS2008），重新编译，顺利通过。

SQLITE数据类型
虽然SQLITE是一个开源数据引擎，我们可以得到它的全部源代码，但是在使用之前，对SQLITE所支持的数据有必要进行一个大概的了解，这样才能够更好的使用它。
每个存放在sqlite数据库中（或者由这个数据库引擎操作）的值都有下面中的一个存储类：
NULL，值是NULL
INTEGER，值是有符号整形，根据值的大小以1,2,3,4,6或8字节存放
REAL，值是浮点型值，以8字节IEEE浮点数存放
TEXT，值是文本字符串，使用数据库编码（UTF-8，UTF-16BE或者UTF-16LE）存放
BLOB，只是一个数据块，完全按照输入存放（即没有准换）
从SQLITE2.0开始，除了INTEGER PRIMARY KEY之外的任何列都插入任何类型的数据。至于原因，我想sqilte官方网站上的这句话可以很好的解释。
The datatype you assign to a column in the CREATE TABLE command does not restrict what data can be put into that column.
Every column is able to hold an arbitrary length string. (There is one exception: Columns of type INTEGER PRIMARY KEY may
only hold a 64-bit signed integer. An error will result if you try to put anything other than an integer into an INTEGER PRIMARY KEY column.)

打开和关闭数据库操作
SQLITE3版本有三个打开数据库的函数，分别是
int sqlite3_open(
const char *filename,/* Database filename (UTF-8) */
sqlite3 **ppDb /* OUT: SQLite db handle */
);
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 */
);
int sqlite3_open16(
const void *filename,/* Database filename (UTF-16) */
sqlite3 **ppDb /* OUT: SQLite db handle */
);
若是数据库的编码方式是UTF-8的话，那么我们需要通过前面两个函数予以打开，若是UTF-16的话，则用最后一个函数打开。若是成功打开数据库，那么三个函数的
返回值都是SQLITE_OK,并且将数据库句柄保存在第二个入口参数里面。

但是无论调用哪一个函数打开的数据库，
最后关闭数据库的时候，调用都是同一个函数sqlite3_close来关闭数据库。
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.

The default encoding for the database will be UTF-8 if sqlite3_open() or sqlite3_open_v2() is called and UTF-16 in the native byte order if sqlite3_open16() is used.

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() can take one of the following three values,optionally combined with the SQLITE_OPEN_NOMUTEX,SQLITE_OPEN_FULLMUTEX,SQLITE_OPEN_SHAREDCACHE,SQLITE_OPEN_PRIVATECACHE,and/or SQLITE_OPEN_URI flags:

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().

If the 3rd parameter to sqlite3_open_v2() is not one of the combinations shown above optionally combined with other SQLITE_OPEN_* bits then the behavior is undefined.

If the SQLITE_OPEN_NOMUTEX flag is set,then the database connection opens in the multi-thread threading mode as long as the single-thread mode has not been set at compile-time or start-time. If the SQLITE_OPEN_FULLMUTEX flag is set then the database connection opens in the serialized threading mode unless single-thread was previously selected at compile-time or start-time. The SQLITE_OPEN_SHAREDCACHE flag causes the database connection to be eligible to use shared cache mode,regardless of whether or not shared cache is enabled using sqlite3_enable_shared_cache(). The SQLITE_OPEN_PRIVATECACHE flag causes the database connection to not participate in shared cache mode even if it is enabled.

关闭数据库

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!