服务器之家

服务器之家 > 正文

asp.net下SQLite(轻量级最佳数据库) 原理分析和开发应用

时间:2019-09-19 12:38     来源/作者:asp.net开发网

概述

SQLite介绍

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

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

架构(architecture)

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

 

asp.net下SQLite(轻量级最佳数据库) 原理分析和开发应用

 

接口(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手动提交。接下来就是锁的问题。

asp.net下SQLite(轻量级最佳数据库) 原理分析和开发应用

 

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

 

读者可以从http://www.sqlite.org/下载SQLite 3.3.4的版本 
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的实现

asp.net下SQLite(轻量级最佳数据库) 原理分析和开发应用

数据插入与更新

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

复制代码代码如下:

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


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

复制代码代码如下:

Insert or Replace INTO TABLE1(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()两个函数实现字符间转换。 
开发示例 

复制代码代码如下:

using System; 
using System.Data; 
using System.Data.SQLite; 
using System.Collections.Generic; 
using System.IO; 
namespace FileSystemWatcthrDemo.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); 
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, conn, cmdText, 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, conn, cmdText, parameters); 
SQLiteDataReader sqLiteDataReader = command.ExecuteReader(); 
return sqLiteDataReader; 


public static object ExecuteScalar(string cmdText, List<SQLiteParameter> parameters) 

lock (lockObject) 

using (SQLiteConnection conn = new SQLiteConnection(ConnectionString)) 

using (SQLiteCommand command = new SQLiteCommand()) 

PrepareCommand(command, conn, cmdText, parameters); 
return command.ExecuteScalar(); 




public static void CreateDataBase() 

if (!File.Exists(DataBasePath)) 
SQLiteConnection.CreateFile(DataBasePath); 
CreateTable(); 

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的经典用法 

复制代码代码如下:

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 
标签:

相关文章

热门资讯

玄元剑仙肉身有什么用 玄元剑仙肉身境界等级划分
玄元剑仙肉身有什么用 玄元剑仙肉身境界等级划分 2019-06-21
华为nova5pro和p30pro哪个好 华为nova5pro和华为p30pro对比详情
华为nova5pro和p30pro哪个好 华为nova5pro和华为p30pro对比详情 2019-06-22
抖音撒撒累累是什么歌 撒撒累累张艺兴歌曲名字
抖音撒撒累累是什么歌 撒撒累累张艺兴歌曲名字 2019-06-05
男生常说24816是什么意思?女生说13579是什么意思?
男生常说24816是什么意思?女生说13579是什么意思? 2019-09-17
超A是什么意思 你好a表达的是什么
超A是什么意思 你好a表达的是什么 2019-06-06
返回顶部