asp.net下SQLite(轻量级最佳数据库) 原理分析和开发应用
概述
SQLite介绍
自几十年前出现的商业应用程序以来，数据库就成为软件应用程序的主要组成部分。正与数据库管理系统非常关键一样，它们也变得非常庞大，并占用了相当多的系统资源，增加了管理的复杂性。随着软件应用程序逐渐模块模块化，一种新型数据库会比大型复杂的传统数据库管理系统更适应。嵌入式数据库直接在应用程序进程中运行，提供了零配置（zero-configuration）运行模式，并且资源占用非常少。
SQLite是一个开源的嵌入式关系数据库，它在2000年由D. Richard Hipp发布，它的减少应用程序管理数据的开销，SQLite可移植性好，很容易使用，很小，高效而且可靠。
SQLite嵌入到使用它的应用程序中，它们共用相同的进程空间，而不是单独的一个进程。从外部看，它并不像一个RDBMS，但在进程内部，它却是完整的，自包含的数据库引擎。
嵌入式数据库的一大好处就是在你的程序内部不需要网络配置，也不需要管理。因为客户端和服务器在同一进程空间运行。SQLite 的数据库权限只依赖于文件系统，没有用户帐户的概念。SQLite 有数据库级锁定，没有网络服务器。它需要的内存，其它开销很小，适合用于嵌入式设备。你需要做的仅仅是把它正确的编译到你的程序。
架构(architecture)
SQLite采用了模块的设计，它由三个子系统，包括8个独立的模块构成。
接口(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手动提交。接下来就是锁的问题。
关于这个图有以下几点值得注意：
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的实现
数据插入与更新
使用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