SQLite学习(九) - 虚拟机指令2

本文介绍SQLite虚拟机指令的版本是2.8.0. SQLite3 以及以后版本的虚拟机概念上是一样的。 但是一些操作码,操作数用法,个数有所改变,算法也有所不同。VDBE虚拟机语言,类似汇编语言,VDBE的每天指令由一个opcode 3operand组成(SQLite3以后有5) 下面看个例子来学习。

1 INSERT SQL

CREATE TABLE examp(one text, two int);

INSERT INTO examp VALUES('Hello, World!',99);
通过
explain指令, 我们可以得到这条INSERT SQLVDBE指令

sqlite test_database_1
sqlite> CREATE TABLE examp(one text, two int);
sqlite> .explain
sqlite> EXPLAIN INSERT INTO examp VALUES('Hello, World!',99);
addr  opcode        p1     p2     p3                                      
----  ------------  -----  -----  -----------------------------------
0     Transaction   0      0                                         
1     VerifyCookie  0      81                                        
2     Transaction   1      0                                         
3     Integer       0      0                                         
4     OpenWrite     0      3      examp                              
5     NewRecno      0      0                                         
6     String        0      0      Hello, World!                      
7     Integer       99     0      99                                 
8     MakeRecord    2      0                                         
9     PutIntKey     0      1                                         
10    Close         0      0                                         
11    Commit        0      0                                         
12    Halt          0      0

这个INSERT12条指令完成,头3条指令和最后2条指令时标准指令,每个SQL都会有这几个指令。 实际完成INSERT操作的是中间的7条指令。下面我们来逐条分析。

(1)开始执行
0     Transaction   0      0                                          
1     VerifyCookie  0      81                                      
2     Transaction   1      0


Transaction
开始一个事务。 P1数作用的数据库的id 0表述main database .databases指令可以查看数据库的情况。第二个Transaction指令在rollback journal上创建一个事务。

sqlite> .databases
seq  name             file
---  ---------------  ----------------------------------------------------------
0    main

VerifyCookie指令验证下P1数据的cookie值与P2值相同,保证修改之前这个数据库没有被其他线程修改过。以免读脏数据或覆盖已更新数据。

(2)打开游标
3     Integer       0      0                                    
4     OpenWrite     0      3      examp

Integer指令将P1=database id pushVDB Stack中。 如果P3不为NULL,它的值就是database名字的string
OpenWrite
指令打开一个read/write的游标。P1=0 table id P2=3 table所在页page id

(3) 写入db文件
5     NewRecno      0      0 //创建一个新的record id, 并压入栈
6     String        0      0      Hello, World! // P3值压入栈
7     Integer       99     0      99 //将值99 压入栈
8     MakeRecord    2      0   //将栈中的top P1=2个元素弹出,转换成二进制(用于写入db文件),压入栈。
9     PutIntKey     0      1
 //pop 栈中的2个元素,写入db文件,P2=1表示改变的元素个数。

(4)
结束
10    Close         0      0       
//关闭游标                                  
11    Commit        0      0       
//提交所有update, deletes the journal file and releases the write lock on the database. A read lock continues to be held if there are still cursors open.                          
12    Halt          0      0
   //VDB engine exit

 

2 SELECT 查询

sqlite> EXPLAIN SELECT * FROM examp;
addr  opcode        p1     p2     p3                                 
----  ------------  -----  -----  -----------------------------------
0     ColumnName    0      0      one                                
1     ColumnName    1      0      two                                
2     Integer       0      0                                         
3     OpenRead      0      3      examp                              
4     VerifyCookie  0      81                                        
5     Rewind        0      10                                        
6     Column        0      0                                         
7     Column        0      1                                         
8     Callback      2      0                                         
9     Next          0      6                                         
10    Close         0      0                                         
11    Halt          0      0

 

如果熟悉SQLite API就知道有一个callback函数
int Callback(void *pUserData, int nColumn, char *azData[], char *azColumnName[]);

VDBE的任务就是传入后三个参数
int nColumn  返回的column个数
char *azData[]  返回data
char*
azColumnName[] column名数组

(
1)开始执行 准备要返回的column 
0     ColumnName    0      0      one                                
1     ColumnName    1      0      two


ColumnName指令将column名字传入azColumnName字符串数组, 然后后面会有相应的column指令将值传入

(2) 打开游标

       
2     Integer       0      0                                         
3     OpenRead      0      3   examp
//打开一个只读的游标                              
4     VerifyCookie  0      81


(3)
循环

5     Rewind        0      10 初始化一个循环,将游标移到P1指向的数据库的第一个entery 然后Column and Next指令会用这个游标来轮询整个table。如果是空表,就直接跳转到P2=10指向的地址。

6     Column        0      0          P2=0th column 压入栈                               
7     Column        0      1  
 P2=1th column 压入栈                                         
8     Callback      2      0
    //从栈中弹出P1=2个元素,传入azData
9     Next          0      6   //
循环跳转到第p2=6条指令,继续执行

 

3. SQLite3版本的生成的指令
指令稍有改动,基于上面的分析还是可以看懂的,具体每个指令用法可以看指令参考http://www.sqlite.org/opcode.html

sqlite> EXPLAIN INSERT INTO examp VALUES('Hello, World!',99);

addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Trace          0     0     0                    00
1     Goto           0     10    0                    00
2     OpenWrite      0     2     0     2              00
3     NewRowid       0     1     0                    00
4     String8        0     2     0     Hello, World!  00
5     Integer        99    3     0                    00
6     MakeRecord     2     2     4     ad             00
7     Insert         0     4     1     examp          1b
8     Close          0     0     0                    00
9     Halt           0     0     0                    00
10    Transaction    0     1     0                    00
11    VerifyCookie   0     1     0                    00
12    TableLock      0     2     1     examp          00
13    Goto           0     2     0                    00

 

sqlite> EXPLAIN SELECT * FROM examp;
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Trace          0     0     0                    00
1     Goto           0     10    0                    00
2     OpenRead       0     2     0     2              00
3     Rewind         0     8     0                    00
4     Column         0     0     1                    00
5     Column         0     1     2                    00
6     ResultRow      1     2     0                    00
7     Next           0     4     0                    01
8     Close          0     0     0                    00
9     Halt           0     0     0                    00
10    Transaction    0     0     0                    00
11    VerifyCookie   0     1     0                    00
12    TableLock      0     2     0     examp          00
13    Goto           0     2     0                    00

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值