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。

insert or replace into 和replace into 的效果是一样的上面这句话也可以这样写

字符编码转换
sqlite3的源码中，提供了utf8tounicode()、unicodetoutf8()、mbcstounicode()、unicodetombcs()、sqlite3_win32_mbcs_to_utf8 ()、utf8tombcs ()等8个函数进行字符在不同编码间的转换，但未在sqlite3.def、sqlite3.h文件中列出，即未对外公开。这些函数中，都使用了multibytetowidechar()、widechartomultibyte()两个函数实现字符间转换。
开发示例

示例讲解
a、使用到自己定义的锁private static object lockobject = new object();
b、使用完连接后都进行关闭操作。使用了using
c、创建数据库命令：sqliteconnection.createfile(databasepath);
最后再讲解个insert or replace into的经典用法