sqlite原理分析和开发应用

Sqlite 前端之家
前端之家收集整理的这篇文章主要介绍了sqlite原理分析和开发应用，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。

概述

sqlite介绍

自几十年前出现的商业应用程序以来，数据库就成为软件应用程序的主要组成部分。正与数据库管理系统非常关键一样，它们也变得非常庞大，并占用了相当多的系统资源，增加了管理的复杂性。随着软件应用程序逐渐模块模块化，一种新型数据库会比大型复杂的传统数据库管理系统更适应。嵌入式数据库直接在应用程序进程中运行，提供了零配置（zero-configuration）运行模式，并且资源占用非常少。
sqlite是一个开源的嵌入式关系数据库，它在2000年由D. Richard Hipp发布，它的减少应用程序管理数据的开销，sqlite可移植性好，很容易使用，很小，高效而且可靠。
sqlite嵌入到使用它的应用程序中，它们共用相同的进程空间，而不是单独的一个进程。从外部看，它并不像一个RDBMS，但在进程内部，它却是完整的，自包含的数据库引擎。

嵌入式数据库的一大好处就是在你的程序内部不需要网络配置，也不需要管理。因为客户端和服务器在同一进程空间运行。sqlite 的数据库权限只依赖于文件系统，没有用户帐户的概念。sqlite 有数据库级锁定，没有网络服务器。它需要的内存，其它开销很小，适合用于嵌入式设备。你需要做的仅仅是把它正确的编译到你的程序。

架构(architecture)

sqlite采用了模块的设计，它由三个子系统，包括8个独立的模块构成。

sqlite1

接口(Interface)
接口由sqlite C API组成，也就是说不管是程序、脚本语言还是库文件，最终都是通过它与sqlite交互的(我们通常用得较多的ODBC/JDBC最后也会转化为相应C API的调用)。
编译器(Compiler)
在编译器中，分词器（Tokenizer）和分析器(Parser)对sql进行语法检查，然后把它转化为底层能更方便处理的分层的数据结构---语法树，然后把语法树传给代码生成器(code generator)进行处理。而代码生成器根据它生成一种针对sqlite的汇编代码，最后由虚拟机(Virtual Machine)执行。
虚拟机(Virtual Machine)
架构中最核心的部分是虚拟机，或者叫做虚拟数据库引擎(Virtual Database Engine,VDBE)。它和Java虚拟机相似，解释执行字节代码。VDBE的字节代码由128个操作码(opcodes)构成，它们主要集中在数据库操作。它的每一条指令都用来完成特定的数据库操作(比如打开一个表的游标)或者为这些操作栈空间的准备(比如压入参数)。总之，所有的这些指令都是为了满足sql命令的要求(关于VM，后面会做详细介绍)。
后端(Back-End)
后端由B-树(B-tree)，页缓存(page cache，pager)和操作系统接口(即系统调用)构成。B-tree和page cache共同对数据进行管理。B-tree的主要功能就是索引，它维护着各个页面之间的复杂的关系，便于快速找到所需数据。而pager的主要作用就是通过OS接口在B-tree和Disk之间传递页面。

sqlite的特点(sqlite’s Features and Philosophy)
零配置(Zero Configuration)
可移植(Portability)：
它是运行在Windows,Linux,BSD,Mac OS X和一些商用Unix系统，比如Sun的Solaris,IBM的AIX，同样，它也可以工作在许多嵌入式操作系统下，比如QNX,VxWorks,Palm OS,Symbin和Windows CE。
Compactness：
sqlite是被设计成轻量级，自包含的。one header file,one library,and you’re relational,no external database server required
简单(Simplicity)
灵活(Flexibility)
可靠(Reliability)：
sqlite的核心大约有3万行标准C代码，这些代码都是模块化的，很容易阅读。

事务(Transaction)

事务的周期(Transaction Lifecycles)
程序与事务之间有两件事值得注意：
A、哪些对象在事务下运行——这直接与API有关。
B、事务的生命周期，即什么时候开始，什么时候结束以及它在什么时候开始影响别的连接（这点对于并发性很重要）——这涉及到sqlite的具体实现。
一个连接（connection）可以包含多个(statement)，而且每个连接有一个与数据库关联的B-tree和一个pager。Pager在连接中起着很重要的作用，因为它管理事务、锁、内存缓存以及负责崩溃恢复(crash recovery)。当你进行数据库写操作时，记住最重要的一件事：在任何时候，只在一个事务下执行一个连接。这些回答了第一个问题。
一般来说，一个事务的生命和statement差不多，你也可以手动结束它。默认情况下，事务自动提交，当然你也可以通过BEGIN..COMMIT手动提交。接下来就是锁的问题。

sqlite02

关于这个图有以下几点值得注意：
A、一个事务可以在UNLOCKED，RESERVED或EXCLUSIVE三种状态下开始。默认情况下在UNLOCKED时开始。
B、白色框中的UNLOCKED,PENDING,SHARED和 RESERVED可以在一个数据库的同一时存在。
C、从灰色的PENDING开始，事情就变得严格起来，意味着事务想得到排斥锁(EXCLUSIVE)（注意与白色框中的区别）。
虽然锁有这么多状态，但是从体质上来说，只有两种情况：读事务和写事务。

读者可以从http://www.sqlite.org/下载sqlite 最新的版本
Cmd 进入命令行


创建数据库文件:
>sqlite3 d:\test.db 回车
就生成了一个test.db在d盘。
这样同时也sqlite3挂上了这个test.db

用.help可以看看有什么命令
>.help 回车即可

看看有创建了多少表
>.tables


看表结构
>.schema 表名


看看目前挂的数据库
>.database


如果要把查询输出到文件
>.output 文件名
> 查询语句；


把查询结果用屏幕输出
>.output stdout

把表结构输出，同时索引也会输出
> .dump 表名
退出
>.exit 或者.quit

从http://sqlite.phxsoftware.com/下载Ado.net驱动。
下载了安装，在安装目录中存在System.Data.sqlite.dll
我们只需要拷贝这个文件到引用目录，并添加引用即可对sqlite数据库操作了
所有的Ado.net对象都是以sqlite开头的，比如sqliteConnection
连接串只需要如下方式
Data Source=d:\test.db 或者DataSource=test.db--应用在和应用程序或者.net能够自动找到的目录
剩下的就很简单了~~

sql语法
由于以前用sqlServer或者ISeries，所以DDL的语法很汗颜
创建一个单个Primary Key的table
CREATE TABLE [Admin] (
[UserName] [nvarchar] (20) PRIMARY KEY NOT NULL,
[Password] [nvarchar] (50) NOT NULL,
[Rank] [smallint] NOT NULL,
[MailServer] [nvarchar] (50) NOT NULL,
[MailUser] [nvarchar] (50) NOT NULL,
[MailPassword] [nvarchar] (50) NOT NULL,
[Mail] [nvarchar] (50) NOT NULL
) ;
创建一个多个Primary Key的table
CREATE TABLE [CodeDetail] (
[CdType] [nvarchar] (10) NOT NULL,
[CdCode] [nvarchar] (20) NOT NULL,
[CdString1] [ntext] NOT NULL,
[CdString2] [ntext] NOT NULL,
[CdString3] [ntext] NOT NULL,
PRIMARY KEY (CdType,CdCode)
) ;
创建索引
CREATE INDEX [IX_Account] ON [Account]([IsCheck],[UserName]);
还可以视图等等。

sqlite 分页查询

写法1:

SELECT * FROM TABLE1 LIMIT 20 OFFSET 20 ;

写法2:

SELECT * FROM TABLE1 LIMIT 20,20;

sqlite 文件的压缩
在多次删除数据、插入数据、更新数据后，数据库体积增大，但实际有效数据量很小，则需要对数据库进行压缩、整理，把已经删除的数据从物理文件中移除。调用一下sql命令即可：

VACUUM

VACUUM的实现

sqlite3

数据插入与更新

使用REPLACE替代INSERT、UPDATE命令。在无满足条件记录，则执行Insert，有满足条件记录，则执行UPDATE。

?
1
REPLACE INTO TABLE1(col1,col2,col3) VALUES(val1,val2,val3);

Insert or Replace Into 和Replace Into 的效果是一样的上面这句话也可以这样写

?
1
Insert or Replace INTO TABLE1(col1,val3);

字符编码转换

sqlite3的源码中，提供了utf8ToUnicode()、unicodeToUtf8()、mbcsToUnicode()、unicodeToMbcs()、sqlite3_win32_mbcs_to_utf8 ()、utf8ToMbcs ()等8个函数进行字符在不同编码间的转换，但未在sqlite3.def、sqlite3.h文件中列出，即未对外公开。这些函数中，都使用了MultiByteToWideChar()、WideCharToMultiByte()两个函数实现字符间转换。

开发示例

?
1
2
3
@H_692_301@ 4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
@H_64_403@ 55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
@H_404_473@ 90
91
92
93
94
95
@H_403_485@ 96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
@H_404_519@ 113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
using System;
using System.Data;
using System.Data.sqlite;
@H_692_301@ using System.Collections.Generic;
using System.IO;
namespace DataHelper
{
public class sqliteHelper
{
/// <summary>
/// ConnectionString样例：Datasource=Test.db3;Pooling=true;FailIfMissing=false
/// </summary>
public static string ConnectionString
{
get
{
return @"Data source= "+DataBasePath+";";
}
set { throw new NotImplementedException(); }
}
public static string DataBasePath
{
get { return "SpringYang.db";};
}
private static object lockObject = new object();
private static void PrepareCommand(sqliteCommand cmd,sqliteConnection conn,string cmdText,List<sqliteParameter> parameters)
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Parameters.Clear();
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = 30;
foreach (var parameter in parameters)
{
cmd.Parameters.Add(parameter);
}
}
public static DataSet ExecuteQuery(string cmdText,List<sqliteParameter> parameters)
{
lock (lockObject)
{
using (sqliteConnection conn = new sqliteConnection(ConnectionString))
{
using (sqliteCommand command = new sqliteCommand())
{
DataSet ds = new DataSet();
PrepareCommand(command,conn,cmdText,parameters);
@H_64_403@ sqliteDataAdapter da = new sqliteDataAdapter(command);
da.Fill(ds);
return ds;
}
}
}
}
public static int ExecuteNonQuery(string cmdText,List<sqliteParameter> parameters)
{
lock (lockObject)
{
using (sqliteConnection conn = new sqliteConnection(ConnectionString))
{
using (sqliteCommand command = new sqliteCommand())
{
PrepareCommand(command,parameters);
return command.ExecuteNonQuery();
}
}
}
}
public static sqliteDataReader ExecuteReader(string cmdText,List<sqliteParameter> parameters)
{
lock (lockObject)
{
sqliteConnection conn = new sqliteConnection(ConnectionString);
sqliteCommand command = new sqliteCommand();
PrepareCommand(command,parameters);
sqliteDataReader sqliteDataReader = command.ExecuteReader();
return sqliteDataReader;
@H_404_473@ }
}
public static object ExecuteScalar(string cmdText,List<sqliteParameter> parameters)
{
lock (lockObject)
@H_403_485@ {
using (sqliteConnection conn = new sqliteConnection(ConnectionString))
{
using (sqliteCommand command = new sqliteCommand())
{
PrepareCommand(command,parameters);
return command.ExecuteScalar();
}
}
}
}
public static void CreateDataBase()
{
if (!File.Exists(DataBasePath))
sqliteConnection.CreateFile(DataBasePath);
CreateTable();
}
@H_404_519@
public static void CreateTable()
{
ExecuteNonQuery(CodeDetailTabale,null);
}
private static string CodeDetailTabale
{
get
{
return @"CREATE TABLE [CodeDetail] (
[CdType] [nvarchar] (10) NOT NULL,
[CdCode] [nvarchar] (20) NOT NULL,
[CdString1] [ntext] NOT NULL,
[CdString2] [ntext] NOT NULL,
[CdString3] [ntext] NOT NULL,
PRIMARY KEY (CdType,CdCode)
) ;";
}
}
}
}

示例讲解

A、使用到自己定义的锁private static object lockObject = new object();

B、使用完连接后都进行关闭操作。使用了using

C、创建数据库命令：sqliteConnection.CreateFile(DataBasePath);

最后再讲解个Insert or Replace into的经典用法

?
1
2
Insert or Replace INTO User(ID,Name,Age) Select old.ID,new.Name,new.Age From
(select 'Spring Yang' as Name,'25' as Age) as new left join (Select ID,Name from User where Name = 'Spring Yang' ) as old on old.Name = new.Name

猜你在找的Sqlite相关文章

SQLite学习笔记
安装 在Windows上安装SQLite。 访问官网下载下Precompliled Binaries for Windows的两个压...
windows上如何安装Sqlite
一、安装 下载地址：http://www.sqlite.org/download.html 将Precompiled Binaries for Wi...
【Android数据存储】SQLite使用实例(附源码)
实例: 会员信息管理 功能：1.查看数据库 2.清空数据库 3.增加会员 4.删除会员 5.更新会员 ...
SQLite数据库管理
SQLite3常用命令
加密你的SQLite
关于SQLite SQLite是一个轻量的、跨平台的、开源的数据库引擎，它的在读写效率、消耗总量、...
SQLite这么娇小可爱，不多了解点都不行啊
在我眼里，MySQL和Oracle是这样的 而SQLite在是这样的 所以这么萌的数据库，我真的应该多了...
SQLite这么娇小可爱，不多了解点都不行啊
在我眼里，MySQL和Oracle是这样的 而SQLite在是这样的 所以这么萌的数据库，我真的应该多了...
SQLite 这么娇小可爱，不多了解点都不行啊
（点击上方蓝字，快速关注我们) 作者：空之境界 网址：http://supermao.cn/sqlitezhe-yao-...
SQLITE3 使用总结
SQLITE3 使用总结 投稿：hebedich 字体：[ 增加  减小] 类型：转载 时间：2014-08-19 Sql...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问