SQLite轻量级最佳数据库 原理分析和开发应用.docx

SQLite轻量级最佳数据库 原理分析和开发应用.docx

《SQLite轻量级最佳数据库 原理分析和开发应用.docx》由会员分享，可在线阅读，更多相关《SQLite轻量级最佳数据库 原理分析和开发应用.docx（14页珍藏版）》请在冰点文库上搜索。

SQLite轻量级最佳数据库原理分析和开发应用

SQLite（轻量级最佳数据库）原理分析和开发应用

概述

SQLite介绍

自几十年前出现的商业应用程序以来，数据库就成为软件应用程序的主要组成部分。

正与数据库管理系统非常关键一样，它们也变得非常庞大，并占用了相当多的系统资源，增加了管理的复杂性。

随着软件应用程序逐渐模块模块化，一种新型数据库会比大型复杂的传统数据库管理系统更适应。

嵌入式数据库直接在应用程序进程中运行，提供了零配置（zero-configuration）运行模式，并且资源占用非常少。

SQLite是一个开源的嵌入式关系数据库，它在2000年由D.RichardHipp发布，它的减少应用程序管理数据的开销，SQLite可移植性好，很容易使用，很小，高效而且可靠。

SQLite嵌入到使用它的应用程序中，它们共用相同的进程空间，而不是单独的一个进程。

从外部看，它并不像一个RDBMS，但在进程内部，它却是完整的，自包含的数据库引擎。

嵌入式数据库的一大好处就是在你的程序内部不需要网络配置，也不需要管理。

因为客户端和服务器在同一进程空间运行。

SQLite的数据库权限只依赖于文件系统，没有用户帐户的概念。

SQLite有数据库级锁定，没有网络服务器。

它需要的内存，其它开销很小，适合用于嵌入式设备。

你需要做的仅仅是把它正确的编译到你的程序。

架构（architecture）

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

接口（Interface）

接口由SQLiteCAPI组成，也就是说不管是程序、脚本语言还是库文件，最终都是通过它与SQLite交互的（我们通常用得较多的ODBC/JDBC最后也会转化为相应CAPI的调用）。

编译器（Compiler）

在编译器中，分词器（Tokenizer）和分析器（Parser）对SQL进行语法检查，然后把它转化为底层能更方便处理的分层的数据结构---语法树，然后把语法树传给代码生成器（codegenerator）进行处理。

而代码生成器根据它生成一种针对SQLite的汇编代码，最后由虚拟机（VirtualMachine）执行。

虚拟机（VirtualMachine）

架构中最核心的部分是虚拟机，或者叫做虚拟数据库引擎（VirtualDatabaseEngine,VDBE）。

它和Java虚拟机相似，解释执行字节代码。

VDBE的字节代码由128个操作码（opcodes）构成，它们主要集中在数据库操作。

它的每一条指令都用来完成特定的数据库操作（比如打开一个表的游标）或者为这些操作栈空间的准备（比如压入参数）。

总之，所有的这些指令都是为了满足SQL命令的要求（关于VM，后面会做详细介绍）。

后端（Back-End）

后端由B-树（B-tree），页缓存（pagecache，pager）和操作系统接口（即系统调用）构成。

B-tree和pagecache共同对数据进行管理。

B-tree的主要功能就是索引，它维护着各个页面之间的复杂的关系，便于快速找到所需数据。

而pager的主要作用就是通过OS接口在B-tree和Disk之间传递页面。

SQLite的特点（SQLite’sFeaturesandPhilosophy）

零配置（ZeroConfiguration）

可移植（Portability）：

它是运行在Windows,Linux,BSD,MacOSX和一些商用Unix系统，比如Sun的Solaris,IBM的AIX，同样，它也可以工作在许多嵌入式操作系统下，比如QNX,VxWorks,PalmOS,Symbin和WindowsCE。

Compactness：

SQLite是被设计成轻量级，自包含的。

oneheaderfile,onelibrary,andyou’rerelational,noexternaldatabaseserverrequired

简单（Simplicity）

灵活（Flexibility）

可靠（Reliability）：

SQLite的核心大约有3万行标准C代码，这些代码都是模块化的，很容易阅读。

事务（Transaction）

事务的周期（TransactionLifecycles）

程序与事务之间有两件事值得注意：

A、哪些对象在事务下运行——这直接与API有关。

B、事务的生命周期，即什么时候开始，什么时候结束以及它在什么时候开始影响别的连接（这点对于并发性很重要）——这涉及到SQLite的具体实现。

一个连接（connection）可以包含多个（statement），而且每个连接有一个与数据库关联的B-tree和一个pager。

Pager在连接中起着很重要的作用，因为它管理事务、锁、内存缓存以及负责崩溃恢复（crashrecovery）。

当你进行数据库写操作时，记住最重要的一件事：

在任何时候，只在一个事务下执行一个连接。

这些回答了第一个问题。

一般来说，一个事务的生命和statement差不多，你也可以手动结束它。

默认情况下，事务自动提交，当然你也可以通过BEGIN..COMMIT手动提交。

接下来就是锁的问题。

关于这个图有以下几点值得注意：

A、一个事务可以在UNLOCKED，RESERVED或EXCLUSIVE三种状态下开始。

默认情况下在UNLOCKED时开始。

B、白色框中的UNLOCKED,PENDING,SHARED和RESERVED可以在一个数据库的同一时存在。

C、从灰色的PENDING开始，事情就变得严格起来，意味着事务想得到排斥锁（EXCLUSIVE）（注意与白色框中的区别）。

虽然锁有这么多状态，但是从体质上来说，只有两种情况：

读事务和写事务。

读者可以从http:

//www.sqlite.org/下载SQLite3.3.4的版本

Cmd进入命令行

   创建数据库文件:

  >SQLite3d:

\test.db回车

  就生成了一个test.db在d盘。

  这样同时也SQLite3挂上了这个test.db

  用.help可以看看有什么命令

  >.help回车即可

  看看有创建了多少表

  >.tables

  看表结构

  >.schema表名

 看看目前挂的数据库

  >.database

  如果要把查询输出到文件

  >.output文件名

  >查询语句；

  把查询结果用屏幕输出

  >.outputstdout

  把表结构输出，同时索引也会输出

   >.dump表名

  退出

  >.exit或者.quit

从

  下载了安装，在安装目录中存在System.Data.SQLite.dll

   我们只需要拷贝这个文件到引用目录，并添加引用即可对SQLite数据库操作了

  所有的A对象都是以SQLite开头的，比如SQLiteConnection

  连接串只需要如下方式

  DataSource=d:

\test.db或者DataSource=test.db--应用在和应用程序或者.net能够自动找到的目录

  剩下的就很简单了~~

SQL语法

  由于以前用SQLServer或者ISeries，所以DDL的语法很汗颜

  创建一个单个PrimaryKey的table

  CREATETABLE [Admin]（

[UserName][nvarchar]（20）  PRIMARYKEYNOTNULL,

[Password][nvarchar]（50）  NOTNULL,

[Rank][smallint]NOTNULL,

[MailServer][nvarchar]（50）  NOTNULL,

[MailUser][nvarchar]（50）  NOTNULL,

[MailPassword][nvarchar]（50）  NOTNULL,

[Mail][nvarchar]（50）  NOTNULL

  ）;

 创建一个多个PrimaryKey的table

  CREATETABLE [CodeDetail]（

[CdType][nvarchar]（10） NOTNULL,

[CdCode][nvarchar]（20） NOTNULL,

[CdString1][ntext]  NOTNULL,

[CdString2][ntext]  NOTNULL,

[CdString3][ntext]  NOTNULL,

 PRIMARYKEY（CdType,CdCode）

  ）;

 创建索引

  CREATE INDEX[IX_Account]ON [Account]（[IsCheck],[UserName]）;

  还可以视图等等。

SQLite分页查询

写法1:

SELECT*FROMTABLE1LIMIT 20OFFSET20;

写法2:

SELECT*FROMTABLE1LIMIT20,20;

SQLite文件的压缩

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

调用一下SQL命令即可：

VACUUM

VACUUM的实现

数据插入与更新

使用REPLACE替代INSERT、UPDATE命令。

在无满足条件记录，则执行Insert，有满足条件记录，则执行UPDATE。

REPLACEINTOTABLE1（col1,col2,col3）VALUES（val1,val2,val3）;

InsertorReplaceInto和ReplaceInto的效果是一样的上面这句话也可以这样写

InsertorReplaceINTOTABLE1（col1,col2,col3）VALUES（val1,val2,val3）;

字符编码转换

sqlite3的源码中，提供了utf8ToUnicode（）、unicodeToUtf8（）、mbcsToUnicode（）、unicodeToMbcs（）、sqlite3_win32_mbcs_to_utf8（）、utf8ToMbcs（）等8个函数进行字符在不同编码间的转换，但未在sqlite3.def、sqlite3.h文件中列出，即未对外公开。

这些函数中，都使用了MultiByteToWideChar（）、WideCharToMultiByte（）两个函数实现字符间转换。

开发示例

usingSystem;

usingSystem.Data;

usingSystem.Data.SQLite;

usingSystem.Collections.Generic;

usingSystem.IO;

namespaceFileSystemWatcthrDemo.DataHelper

{

publicclassSqLiteHelper

{

///

publicstaticstringConnectionString

{

get

{

return@"Datasource="+DataBasePath+";";

}

set{thrownewNotImplementedException（）;}

}

publicstaticstringDataBasePath

{

get{return"SpringYang.db";};

}

privatestaticobjectlockObject=newobject（）;

privatestaticvoidPrepareCommand（SQLiteCommandcmd,SQLiteConnectionconn,stringcmdText,Listparameters）

{

if（conn.State!

=ConnectionState.Open）

conn.Open（）;

cmd.Parameters.Clear（）;

cmd.Connection=conn;

cmd.CommandText=cmdText;

cmd.CommandType=CommandType.Text;

cmd.CommandTimeout=30;

foreach（varparameterinparameters）

{

cmd.Parameters.Add（parameter）;

}

}

publicstaticDataSetExecuteQuery（stringcmdText,Listparameters）

{

lock（lockObject）

{

using（SQLiteConnectionconn=newSQLiteConnection（ConnectionString））

{

using（SQLiteCommandcommand=newSQLiteCommand（））

{

DataSetds=newDataSet（）;

PrepareCommand（command,conn,cmdText,parameters）;

SQLiteDataAdapterda=newSQLiteDataAdapter（command）;

da.Fill（ds）;

returnds;

}

}

}

}

publicstaticintExecuteNonQuery（stringcmdText,Listparameters）

{

lock（lockObject）

{

using（SQLiteConnectionconn=newSQLiteConnection（ConnectionString））

{

using（SQLiteCommandcommand=newSQLiteCommand（））

{

PrepareCommand（command,conn,cmdText,parameters）;

returncommand.ExecuteNonQuery（）;

}

}

}

}

publicstaticSQLiteDataReaderExecuteReader（stringcmdText,Listparameters）

{

lock（lockObject）

{

SQLiteConnectionconn=newSQLiteConnection（ConnectionString）;

SQLiteCommandcommand=newSQLiteCommand（）;

PrepareCommand（command,conn,cmdText,parameters）;

SQLiteDataReadersqLiteDataReader=command.ExecuteReader（）;

returnsqLiteDataReader;

}

}

publicstaticobjectExecuteScalar（stringcmdText,Listparameters）

{

lock（lockObject）

{

using（SQLiteConnectionconn=newSQLiteConnection（ConnectionString））

{

using（SQLiteCommandcommand=newSQLiteCommand（））

{

PrepareCommand（command,conn,cmdText,parameters）;

returncommand.ExecuteScalar（）;

}

}

}

}

publicstaticvoidCreateDataBase（）

{

if（!

File.Exists（DataBasePath））

SQLiteConnection.CreateFile（DataBasePath）;

CreateTable（）;

}

publicstaticvoidCreateTable（）

{

ExecuteNonQuery（CodeDetailTabale,null）;

}

privatestaticstringCodeDetailTabale

{

get

{

return@"CREATETABLE[CodeDetail]（

[CdType][nvarchar]（10）NOTNULL,

[CdCode][nvarchar]（20）NOTNULL,

[CdString1][ntext]NOTNULL,

[CdString2][ntext]NOTNULL,

[CdString3][ntext]NOTNULL,

PRIMARYKEY（CdType,CdCode）

）;";

}

}

}

}

示例讲解

A、使用到自己定义的锁private staticobjectlockObject=newobject（）;

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

使用了using

C、创建数据库命令：

SQLiteConnection.CreateFile（DataBasePath）;

最后再讲解个InsertorReplaceinto的经典用法

InsertorReplaceINTOUser（ID,Name,Age）Selectold.ID,new.Name,new.AgeFrom

（select'SpringYang'asName,'25'asAge）asnewleftjoin（SelectID,NamefromUserwhereName='SpringYang'）asoldonold.Name=new.Name