SQLite Learning Note (11) &&sqlite Virtual Machine principle

Objective
We know that any kind of relational database management system support SQL (structured Query Language), relative to the file management system, the user does not care how the data in the database access, do not need to know the underlying storage structure, familiar with SQL, you can use the database skillfully. The introduction of SQL makes the database system need to transform SQL into internal data structure, and then connect with the underlying storage structure to achieve the user access data. The so-called SQL corresponds to the data structure, which we often call the execution plan, before each SQL executes, it needs to generate the execution plan and then execute. How does SQL change to an equivalent execution plan? We are familiar with the database, Oracle,sqlserver,mysql, etc. by the SQL for lexical analysis, grammar analysis, semantic analysis, generate execution plan and other steps, the final generation of execution plan, this plan is generally a complex data structure. SQLite also builds the execution plan through the steps above, but in particular, the SQLite execution plan is a sequence of instruction flows, generated by the code generator, which translates the syntax tree into a sqlite-specific internal instruction that parses execution through a virtual machine. The instruction flow is equivalent to the SQL and virtual machine mediation, because the instruction flow is flat, the SQLite provides the method (PRAGMA vdbe_trace=on) lets the user see executes the SQL each instruction, clearly knows the data in the SQLite inside how flows. This paper mainly deals with the principles of SQLite virtual machine (VDBE) and related internal directives.

Virtual machines
The so-called virtual machine refers to an abstraction of the real computer resource environment, which provides a complete set of computer interfaces for the language program. For example, we are familiar with the Java language, when we run the Java program, actually run in the JVM (Java Virtual Machine) environment, All Java programs are first compiled into a. class file, which executes on the virtual machine, meaning that the class file does not correspond to the operating system directives, but rather that the virtual machine interacts with the operating system indirectly. The same is true for SQLite virtual machines, where the instruction flow generated by the compiled SQL is recognized by the SQLite virtual machine (virtual engine, or vdbe), which interacts with the underlying storage (table, index). This approach makes the internal module of SQLite very clear, the coupling is very low. As shown, we can see the location of Vdbe, which is in the middle of the compiler and Btree module, is the core of SQLite, responsible for SQL to data access interaction. The virtual machines I mentioned later refer to the SQLite virtual machine (MACHINE,VM), where the VM module considers the underlying storage to be the file system of the record dimension and reads and writes the records on the table by executing the instruction stream.

VDBE Data Structures and APIs

structVdbe{sqlite3*db;/*The database connection that owns this statement*/Op*AOP;/*Space to the "virtual machine" program*/intNOp;/*Number of instructions in the program*/Mem**aparg;/*Arguments to currently executing user function*/Parse*pparse;/*parsing context used to create this vdbe*/intPc/*The Program counter*/Mem*amem;/*The memory Locations*/intNmem;/*Number of memory locations currently allocated*/Mem*acolname;/*Column names to return*/U16 Nrescolumn;/*Number of columns in one row of the result set*/Char*zsql;/*Text of the SQL statement that generated this*/}

I choose the most important object from the source, including database object (db), instruction flow object (AOP,NOP), binding input parameter value (APARG), parsing SQL Object (pparse), Instruction stream counter (PC), storing temporary variable's register (AMEM, NMEM), returns the column name and column information (acolname,nrescolumn) of the result set set, and the SQL (ZSQL) that executes the virtual machine directive. These basic is the virtual machine object all, has the instruction, has the register, has the instruction counter, is very similar with the assembly language, but vdbe inside the instruction is the SQLite internal recognition instruction, but the assembly language instruction is corresponds with the machine instruction. If you want to know all VDBE objects, you can refer to vdbeInt.h for the definition of the structure, as well as the SQLITE3 structure and the parse structure can refer to the SqliteInt.h file.
knowing the VDBE data structure, let's take a look at how our usual API is exchanging data with vdbe. Usually we execute a statement that performs the following steps.
1. Call sqlite3_prepare_* to compile the generated instruction stream and return a Sqlite3_stmt object, which is actually the Vdbe object.
2. Call sqlite3_bind_* to pass parameters to Vdbe,
3. Call sqlite3_step for execution, which will start the virtual machine to execute a command until an interrupt or stop command is encountered
4. Call sqlite3_column_* to get a prepared result set in the previous step
5. Call Sqlite3_finalize, destroy the Vdbe object, and end this execution.
In addition we may also use the Sqlite3_reset interface, which returns the instruction stream to the first instruction, which the user can call Sqlite3_step to execute again. For a detailed description of the API, refer to file VDBEAPI.C.

Virtual machine directives
Virtual machine core is flat instructions, SQLite defines a series of instruction language, each instruction to do a small number of actions, the virtual machine by executing some column instructions to reach the query, modify the database. Each instruction contains one operator and 5 operands, in the form of the following:<opcode,p1,p2,p3,p4,p5>. P1,P2,P3 is a 32-bit signed integer, P1 is generally the cursor number, P2 is usually the instruction to jump command location, P4 is a 32-bit/64-bit integer, 64-bit floating point, or a pointer to a string, or binary, etc., P5 is an unnumbered character. Not every instruction uses all 5 operands, and some instructions require only 2 to 3 operands. In the following article I will combine examples to explain the role of instructions and the meaning of the corresponding operands.

Virtual Machine Execution Process
The core process of the virtual machine is in the Sqlite3vdbeexec function, which is called when we call Sqlite3_step. Since this function is relatively large, there are about 6000 lines of code, which contains the execution of each instruction, in order to facilitate the explanation, I will simplify the function of the content to illustrate the logic of this function, the abstract code is as follows. From the code flow, the logic is very simple, by looping through each instruction in the instruction array to execute one after the other until an interrupt or termination instruction is encountered. If you need to understand the meaning of each instruction, you need to read the code carefully.

Sqlite3vdbeexec (VDBE *2) {Op*AOP = p->aop;/*Copy of P->aop*/Op*pop = aOp;/*Current Operation*/ 　　  for(pop=&aop[p->pc]; Rc==sqlite_ok; pop++) {Switch(pop->opcode) { CaseOp_goto://the command that the jump to P2 points to 　　　　 {pOp= &AOP[POP->P2-1];  Break; } CaseOp_integer://value P1 is written into register P2. 　　　　 {POut=out2prerelease (P, pOp); POut->U.I = pop->P1;  Break; } Caseop_real: {... Break; } CaseOp_halt: {... Break; } ...}//End of Switch}//End of For
}

Summary
This article describes the SQLite virtual machine and the corresponding instruction stream. By introducing the storage structure of VDBE, we understand the contents of the Vdbe object, we understand the relationship between the API and the virtual machine by introducing the API, and by introducing the implementation of the function sqlite3vdbeexec, we know that the virtual machine execution process is very clear, by executing a series of instruction flows, You can implement the query and update the data.

SQLite Learning Note (11) &&sqlite Virtual Machine principle