SQLite Virtual Machine Opcodes
(This page was last modified on 2002/08/11 20:10:49 UTC)
Introduction
In order to execute an SQL statement, the SQLite library first parses the SQL, analyzes the statement, then generates a short program to execute the statement. The program is generated for a "virtual machine" implemented by the SQLite library. This document describes the operation of that virtual machine.
This document is intended as a reference, not a tutorial. A separate Virtual Machine Tutorial is available. If you are looking for a narrative description of how the virtual machine works, you should read the tutorial and not this document. Once you have a basic idea of what the virtual machine does, you can refer back to this document for the details on a particular opcode. Unfortunately, the virtual machine tutorial was written for SQLite version 1.0. There are substantial changes in the virtual machine for version 2.0 and the document has not been updated.
The source code to the virtual machine is in the vdbe.c source file. All of the opcode definitions further down in this document are contained in comments in the source file. In fact, the opcode table in this document was generated by scanning the vdbe.c source file and extracting the necessary information from comments. So the source code comments are really the canonical source of information about the virtual macchine. When in doubt, refer to the source code.
Each instruction in the virtual machine consists of an opcode and up to three operands named P1, P2 and P3. P1 may be an arbitrary integer. P2 must be a non-negative integer. P2 is always the jump destination in any operation that might cause a jump. P3 is a null-terminated string or NULL. Some operators use all three operands. Some use one or two. Some operators use none of the operands.
The virtual machine begins execution on instruction number 0. Execution continues until (1) a Halt instruction is seen, or (2) the program counter becomes one greater than the address of last instruction, or (3) there is an execution error. When the virtual machine halts, all memory that it allocated is released and all database cursors it may have had open are closed. If the execution stopped due to an error, any pending transactions are terminated and changes made to the database are rolled back.
The virtual machine also contains an operand stack of unlimited depth. Many of the opcodes use operands from the stack. See the individual opcode descriptions for details.
The virtual machine can have zero or more cursors. Each cursor is a pointer into a single table or index within the database. There can be multiple cursors pointing at the same index or table. All cursors operate independently, even cursors pointing to the same indices or tables. The only way for the virtual machine to interact with a database file is through a cursor. Instructions in the virtual machine can create a new cursor (Open), read data from a cursor (Column), advance the cursor to the next entry in the table (Next) or index (NextIdx), and many other operations. All cursors are automatically closed when the virtual machine terminates.
The virtual machine contains an arbitrary number of fixed memory locations with addresses beginning at zero and growing upward. Each memory location can hold an arbitrary string. The memory cells are typically used to hold the result of a scalar SELECT that is part of a larger expression.
The virtual machine contains a single sorter. The sorter is able to accumulate records, sort those records, then play the records back in sorted order. The sorter is used to implement the ORDER BY clause of a SELECT statement.
The virtual machine contains a single "List". The list stores a list of integers. The list is used to hold the rowids for records of a database table that needs to be modified. The WHERE clause of an UPDATE or DELETE statement scans through the table and writes the rowid of every record to be modified into the list. Then the list is played back and the table is modified in a separate step.
The virtual machine can contain an arbitrary number of "Sets". Each set holds an arbitrary number of strings. Sets are used to implement the IN operator with a constant right-hand side.
The virtual machine can open a single external file for reading. This external read file is used to implement the COPY command.
Finally, the virtual machine can have a single set of aggregators. An aggregator is a device used to implement the GROUP BY clause of a SELECT. An aggregator has one or more slots that can hold values being extracted by the select. The number of slots is the same for all aggregators and is defined by the AggReset operation. At any point in time a single aggregator is current or "has focus". There are operations to read or write to memory slots of the aggregator in focus. There are also operations to change the focus aggregator and to scan through all aggregators.
Viewing Programs Generated By SQLite
Every SQL statement that SQLite interprets results in a program for the virtual machine. But if you precede the SQL statement with the keyword "EXPLAIN" the virtual machine will not execute the program. Instead, the instructions of the program will be returned like a query result. This feature is useful for debugging and for learning how the virtual machine operates.
You can use the sqlite command-line tool to see the instructions generated by an SQL statement. The following is an example:
$ sqlite ex1 sqlite> .explain sqlite> explain delete from tbl1 where two<20; addr opcode p1 p2 p3 ---- ------------ ----- ----- ---------------------------------------- 0 Transaction 0 0 1 VerifyCookie 219 0 2 ListOpen 0 0 3 Open 0 3 tbl1 4 Rewind 0 0 5 Next 0 12 6 Column 0 1 7 Integer 20 0 8 Ge 0 5 9 Recno 0 0 10 ListWrite 0 0 11 Goto 0 5 12 Close 0 0 13 ListRewind 0 0 14 OpenWrite 0 3 15 ListRead 0 19 16 MoveTo 0 0 17 Delete 0 0 18 Goto 0 15 19 ListClose 0 0 20 Commit 0 0
All you have to do is add the "EXPLAIN" keyword to the front of the SQL statement. But if you use the ".explain" command to sqlite first, it will set up the output mode to make the program more easily viewable.
If sqlite has been compiled without the "-DNDEBUG=1" option (that is, with the NDEBUG preprocessor macro not defined) then you can put the SQLite virtual machine in a mode where it will trace its execution by writing messages to standard output. The non-standard SQL "PRAGMA" comments can be used to turn tracing on and off. To turn tracing on, enter:
PRAGMA vdbe_trace=on;
You can turn tracing back off by entering a similar statement but changing the value "on" to "off".
The Opcodes
There are currently 129 opcodes defined by the virtual machine. All currently defined opcodes are described in the table below. This table was generated automatically by scanning the source code from the file vdbe.c.
| Opcode Name | Description |
|---|---|
| AbsValue | Treat the top of the stack as a numeric quantity. Replace it with its absolute value. If the top of the stack is NULL its value is unchanged. |
| Add | Pop the top two elements from the stack, add them together, and push the result back onto the stack. If either element is a string then it is converted to a double using the atof() function before the addition. If either operand is NULL, the result is NULL. |
| AddImm | Add the value P1 to whatever is on top of the stack. The result is always an integer. To force the top of the stack to be an integer, just add 0. |
| AggFocus | Pop the top of the stack and use that as an aggregator key. If an aggregator with that same key already exists, then make the aggregator the current aggregator and jump to P2. If no aggregator with the given key exists, create one and make it current but do not jump. The order of aggregator opcodes is important. The order is: AggReset AggFocus AggNext. In other words, you must execute AggReset first, then zero or more AggFocus operations, then zero or more AggNext operations. You must not execute an AggFocus in between an AggNext and an AggReset. |
| AggFunc | Execute the step function for an aggregate. The function has P2 arguments. P3 is a pointer to the FuncDef structure that specifies the function. The top of the stack must be an integer which is the index of the aggregate column that corresponds to this aggregate function. Ideally, this index would be another parameter, but there are no free parameters left. The integer is popped from the stack. |
| AggGet | Push a new entry onto the stack which is a copy of the P2-th field of the current aggregate. Strings are not duplicated so string values will be ephemeral. |
| AggInit | Initialize the function parameters for an aggregate function. The aggregate will operate out of aggregate column P2. P3 is a pointer to the FuncDef structure for the function. |
| AggNext | Make the next aggregate value the current aggregate. The prior aggregate is deleted. If all aggregate values have been consumed, jump to P2. The order of aggregator opcodes is important. The order is: AggReset AggFocus AggNext. In other words, you must execute AggReset first, then zero or more AggFocus operations, then zero or more AggNext operations. You must not execute an AggFocus in between an AggNext and an AggReset. |
| AggReset | Reset the aggregator so that it no longer contains any data. Future aggregator elements will contain P2 values each. |
| AggSet | Move the top of the stack into the P2-th field of the current aggregate. String values are duplicated into new memory. |
| And | Pop two values off the stack. Take the logical AND of the two values and push the resulting boolean value back onto the stack. |
| BitAnd | Pop the top two elements from the stack. Convert both elements to integers. Push back onto the stack the bit-wise AND of the two elements. If either operand is NULL, the result is NULL. |
| BitNot | Interpret the top of the stack as an value. Replace it with its ones-complement. If the top of the stack is NULL its value is unchanged. |
| BitOr | Pop the top two elements from the stack. Convert both elements to integers. Push back onto the stack the bit-wise OR of the two elements. If either operand is NULL, the result is NULL. |
| Callback | Pop P1 values off the stack and form them into an array. Then invoke the callback function using the newly formed array as the 3rd parameter. |
| Checkpoint | Begin a checkpoint. A checkpoint is the beginning of a operation that is part of a larger transaction but which might need to be rolled back itself without effecting the containing transaction. A checkpoint will be automatically committed or rollback when the VDBE halts. |
| Clear | Delete all contents of the database table or index whose root page in the database file is given by P1. But, unlike Destroy, do not remove the table or index from the database file. The table being clear is in the main database file if P2==0. If P2==1 then the table to be clear is in the auxiliary database file that is used to store tables create using CREATE TEMPORARY TABLE. See also: Destroy |
| Close | Close a cursor previously opened as P1. If P1 is not currently open, this instruction is a no-op. |
| Column | Interpret the data that cursor P1 points to as a structure built using the MakeRecord instruction. (See the MakeRecord opcode for additional information about the format of the data.) Push onto the stack the value of the P2-th column contained in the data. If the KeyAsData opcode has previously executed on this cursor, then the field might be extracted from the key rather than the data. If P1 is negative, then the record is stored on the stack rather than in a table. For P1==-1, the top of the stack is used. For P1==-2, the next on the stack is used. And so forth. The value pushed is always just a pointer into the record which is stored further down on the stack. The column value is not copied. |
| ColumnCount | Specify the number of column values that will appear in the array passed as the 4th parameter to the callback. No checking is done. If this value is wrong, a coredump can result. |
| ColumnName | P3 becomes the P1-th column name (first is 0). An array of pointers to all column names is passed as the 4th parameter to the callback. The ColumnCount opcode must be executed first to allocate space to hold the column names. Failure to do this will likely result in a coredump. |
| Commit | Cause all modifications to the database that have been made since the last Transaction to actually take effect. No additional modifications are allowed until another transaction is started. The Commit instruction 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. |
| Concat | Look at the first P1 elements of the stack. Append them all together with the lowest element first. Use P3 as a separator. Put the result on the top of the stack. The original P1 elements are popped from the stack if P2==0 and retained if P2==1. If any element of the stack is NULL, then the result is NULL. If P3 is NULL, then use no separator. When P1==1, this routine makes a copy of the top stack element into memory obtained from sqliteMalloc(). |
| CreateIndex | Allocate a new index in the main database file if P2==0 or in the auxiliary database file if P2==1. Push the page number of the root page of the new index onto the stack. See documentation on OP_CreateTable for additional information. |
| CreateTable | Allocate a new table in the main database file if P2==0 or in the auxiliary database file if P2==1. Push the page number for the root page of the new table onto the stack. The root page number is also written to a memory location that P3 points to. This is the mechanism is used to write the root page number into the parser's internal data structures that describe the new table. The difference between a table and an index is this: A table must have a 4-byte integer key and can have arbitrary data. An index has an arbitrary key but no data. See also: CreateIndex |
| Delete | Delete the record at which the P1 cursor is currently pointing. The cursor will be left pointing at either the next or the previous record in the table. If it is left pointing at the next record, then the next Next instruction will be a no-op. Hence it is OK to delete a record from within an Next loop. The row change counter is incremented if P2==1 and is unmodified if P2==0. |
| Destroy | Delete an entire database table or index whose root page in the database file is given by P1. The table being destroyed is in the main database file if P2==0. If P2==1 then the table to be clear is in the auxiliary database file that is used to store tables create using CREATE TEMPORARY TABLE. See also: Clear |
| Distinct | Use the top of the stack as a string key. If a record with that key does not exist in the table of cursor P1, then jump to P2. If the record does already exist, then fall thru. The cursor is left pointing at the record if it exists. The key is not popped from the stack. This operation is similar to NotFound except that this operation does not pop the key from the stack. See also: Found, NotFound, MoveTo, IsUnique, NotExists |
| Divide | Pop the top two elements from the stack, divide the first (what was on top of the stack) from the second (the next on stack) and push the result back onto the stack. If either element is a string then it is converted to a double using the atof() function before the division. Division by zero returns NULL. If either operand is NULL, the result is NULL. |
| Dup | A copy of the P1-th element of the stack is made and pushed onto the top of the stack. The top of the stack is element 0. So the instruction "Dup 0 0 0" will make a copy of the top of the stack. If the content of the P1-th element is a dynamically allocated string, then a new copy of that string is made if P2==0. If P2!=0, then just a pointer to the string is copied. Also see the Pull instruction. |
| Eq | Pop the top two elements from the stack. If they are equal, then jump to instruction P2. Otherwise, continue to the next instruction. If either operand is NULL (and thus if the result is unknown) then take the jump if P1 is true. If both values are numeric, they are converted to doubles using atof() and compared for equality that way. Otherwise the strcmp() library routine is used for the comparison. For a pure text comparison use OP_StrEq. If P2 is zero, do not jump. Instead, push an integer 1 onto the stack if the jump would have been taken, or a 0 if not. Push a NULL if either operand was NULL. |
| FileColumn | Push onto the stack the P1-th column of the most recently read line from the input file. |
| FileOpen | Open the file named by P3 for reading using the FileRead opcode. If P3 is "stdin" then open standard input for reading. |
| FileRead | Read a single line of input from the open file (the file opened using FileOpen). If we reach end-of-file, jump immediately to P2. If we are able to get another line, split the line apart using P3 as a delimiter. There should be P1 fields. If the input line contains more than P1 fields, ignore the excess. If the input line contains fewer than P1 fields, assume the remaining fields contain NULLs. Input ends if a line consists of just "\.". A field containing only "\N" is a null field. The backslash \ character can be used be used to escape newlines or the delimiter. |
| Found | Use the top of the stack as a string key. If a record with that key does exist in table of P1, then jump to P2. If the record does not exist, then fall thru. The cursor is left pointing to the record if it exists. The key is popped from the stack. See also: Distinct, NotFound, MoveTo, IsUnique, NotExists |
| FullKey | Extract the complete key from the record that cursor P1 is currently pointing to and push the key onto the stack as a string. Compare this opcode to Recno. The Recno opcode extracts the first 4 bytes of the key and pushes those bytes onto the stack as an integer. This instruction pushes the entire key as a string. |
| Function | Invoke a user function (P3 is a pointer to a Function structure that defines the function) with P1 string arguments taken from the stack. Pop all arguments from the stack and push back the result. See also: AggFunc |
| Ge | Pop the top two elements from the stack. If second element (the next on stack) is greater than or equal to the first (the top of stack), then jump to instruction P2. In other words, jump if NOS>=TOS. If either operand is NULL (and thus if the result is unknown) then take the jump if P1 is true. If both values are numeric, they are converted to doubles using atof() and compared in that format. Numeric values are always less than non-numeric values. If both operands are non-numeric, the strcmp() library routine is used for the comparison. For a pure text comparison use OP_StrGe. If P2 is zero, do not jump. Instead, push an integer 1 onto the stack if the jump would have been taken, or a 0 if not. Push a NULL if either operand was NULL. |
| Gosub | Push the current address plus 1 onto the return address stack and then jump to address P2. The return address stack is of limited depth. If too many OP_Gosub operations occur without intervening OP_Returns, then the return address stack will fill up and processing will abort with a fatal error. |
| Goto | An unconditional jump to address P2. The next instruction executed will be the one at index P2 from the beginning of the program. |
| Gt | Pop the top two elements from the stack. If second element (the next on stack) is greater than the first (the top of stack), then jump to instruction P2. In other words, jump if NOS>TOS. If either operand is NULL (and thus if the result is unknown) then take the jump if P1 is true. If both values are numeric, they are converted to doubles using atof() and compared in that format. Numeric values are always less than non-numeric values. If both operands are non-numeric, the strcmp() library routine is used for the comparison. For a pure text comparison use OP_StrGt. If P2 is zero, do not jump. Instead, push an integer 1 onto the stack if the jump would have been taken, or a 0 if not. Push a NULL if either operand was NULL. |
| Halt | Exit immediately. All open cursors, Lists, Sorts, etc are closed automatically. P1 is the result code returned by sqlite_exec(). For a normal halt, this should be SQLITE_OK (0). For errors, it can be some other value. If P1!=0 then P2 will determine whether or not to rollback the current transaction. Do not rollback if P2==OE_Fail. Do the rollback if P2==OE_Rollback. If P2==OE_Abort, then back out all changes that have occurred during this execution of the VDBE, but do not rollback the transaction. There is an implied "Halt 0 0 0" instruction inserted at the very end of every program. So a jump past the last instruction of the program is the same as executing Halt. |
| IdxDelete | The top of the stack is an index key built using the MakeIdxKey opcode. This opcode removes that entry from the index. |
| IdxGE | Compare the top of the stack against the key on the index entry that cursor P1 is currently pointing to. Ignore the last 4 bytes of the index entry. If the index entry is greater than or equal to the top of the stack then jump to P2. Otherwise fall through to the next instruction. In either case, the stack is popped once. |
| IdxGT | Compare the top of the stack against the key on the index entry that cursor P1 is currently pointing to. Ignore the last 4 bytes of the index entry. If the index entry is greater than the top of the stack then jump to P2. Otherwise fall through to the next instruction. In either case, the stack is popped once. |
| IdxLT | Compare the top of the stack against the key on the index entry that cursor P1 is currently pointing to. Ignore the last 4 bytes of the index entry. If the index entry is less than the top of the stack then jump to P2. Otherwise fall through to the next instruction. In either case, the stack is popped once. |
| IdxPut | The top of the stack hold an SQL index key made using the MakeIdxKey instruction. This opcode writes that key into the index P1. Data for the entry is nil. If P2==1, then the key must be unique. If the key is not unique, the program aborts with a SQLITE_CONSTRAINT error and the database is rolled back. If P3 is not null, then it because part of the error message returned with the SQLITE_CONSTRAINT. |
| IdxRecno | Push onto the stack an integer which is the last 4 bytes of the the key to the current entry in index P1. These 4 bytes should be the record number of the table entry to which this index entry points. See also: Recno, MakeIdxKey. |
| If | Pop a single boolean from the stack. If the boolean popped is true, then jump to p2. Otherwise continue to the next instruction. An integer is false if zero and true otherwise. A string is false if it has zero length and true otherwise. If the value popped of the stack is NULL, then take the jump if P1 is true and fall through if P1 is false. |
| IfNot | Pop a single boolean from the stack. If the boolean popped is false, then jump to p2. Otherwise continue to the next instruction. An integer is false if zero and true otherwise. A string is false if it has zero length and true otherwise. If the value popped of the stack is NULL, then take the jump if P1 is true and fall through if P1 is false. |
| IncrKey | The top of the stack should contain an index key generated by The MakeKey opcode. This routine increases the least significant byte of that key by one. This is used so that the MoveTo opcode will move to the first entry greater than the key rather than to the key itself. |
| Integer | The integer value P1 is pushed onto the stack. If P3 is not zero then it is assumed to be a string representation of the same integer. |
| IntegrityCk | Do an analysis of the currently open database. Push onto the stack the text of an error message describing any problems. If there are no errors, push a "ok" onto the stack. P1 is the index of a set that contains the root page numbers for all tables and indices in the main database file. If P2 is not zero, the check is done on the auxiliary database file, not the main database file. This opcode is used for testing purposes only. |
| IsNull | If any of the top abs(P1) values on the stack are NULL, then jump to P2. The stack is popped P1 times if P1>0. If P1<0 then all values are left unchanged on the stack. |
| IsUnique | The top of the stack is an integer record number. Call this record number R. The next on the stack is an index key created using MakeIdxKey. Call it K. This instruction pops R from the stack but it leaves K unchanged. P1 is an index. So all but the last four bytes of K are an index string. The last four bytes of K are a record number. This instruction asks if there is an entry in P1 where the index string matches K but the record number is different from R. If there is no such entry, then there is an immediate jump to P2. If any entry does exist where the index string matches K but the record number is not R, then the record number for that entry is pushed onto the stack and control falls through to the next instruction. See also: Distinct, NotFound, NotExists, Found |
| KeyAsData | Turn the key-as-data mode for cursor P1 either on (if P2==1) or off (if P2==0). In key-as-data mode, the Field opcode pulls data off of the key rather than the data. This is useful for processing compound selects. |
| Last | The next use of the Recno or Column or Next instruction for P1 will refer to the last entry in the database table or index. If the table or index is empty and P2>0, then jump immediately to P2. If P2 is 0 or if the table or index is not empty, fall through to the following instruction. |
| Le | Pop the top two elements from the stack. If second element (the next on stack) is less than or equal to the first (the top of stack), then jump to instruction P2. In other words, jump if NOS<=TOS. If either operand is NULL (and thus if the result is unknown) then take the jump if P1 is true. If both values are numeric, they are converted to doubles using atof() and compared in that format. Numeric values are always less than non-numeric values. If both operands are non-numeric, the strcmp() library routine is used for the comparison. For a pure text comparison use OP_StrLe. If P2 is zero, do not jump. Instead, push an integer 1 onto the stack if the jump would have been taken, or a 0 if not. Push a NULL if either operand was NULL. |
| ListPop | Restore the Vdbe list to the state it was in when ListPush was last executed. |
| ListPush | Save the current Vdbe list such that it can be restored by a ListPop opcode. The list is empty after this is executed. |
| ListRead | Attempt to read an integer from the temporary storage buffer and push it onto the stack. If the storage buffer is empty, push nothing but instead jump to P2. |
| ListReset | Reset the temporary storage buffer so that it holds nothing. |
| ListRewind | Rewind the temporary buffer back to the beginning. |
| ListWrite | Write the integer on the top of the stack into the temporary storage list. |
| Lt |
Pop the top two elements from the stack. If second element (the
next on stack) is less than the first (the top of stack), then
jump to instruction P2. Otherwise, continue to the next instruction.
In other words, jump if NOS
If both values are numeric, they are converted to doubles using atof()
and compared in that format. Numeric values are always less than
non-numeric values. If both operands are non-numeric, the strcmp() library
routine is used for the comparison. For a pure text comparison
use OP_StrLt.
If P2 is zero, do not jump. Instead, push an integer 1 onto the
stack if the jump would have been taken, or a 0 if not. Push a
NULL if either operand was NULL. |
| MakeIdxKey | Convert the top P1 entries of the stack into a single entry suitable for use as the key in an index. In addition, take one additional integer off of the stack, treat that integer as a four-byte record number, and append the four bytes to the key. Thus a total of P1+1 entries are popped from the stack for this instruction and a single entry is pushed back. The first P1 entries that are popped are strings and the last entry (the lowest on the stack) is an integer record number. The converstion of the first P1 string entries occurs just like in MakeKey. Each entry is separated from the others by a null. The entire concatenation is null-terminated. The lowest entry in the stack is the first field and the top of the stack becomes the last. If P2 is not zero and one or more of the P1 entries that go into the generated key is NULL, then jump to P2 after the new key has been pushed on the stack. In other words, jump to P2 if the key is guaranteed to be unique. This jump can be used to skip a subsequent uniqueness test. P3 is a string that is P1 characters long. Each character is either an 'n' or a 't' to indicates if the argument should be numeric or text. The first character corresponds to the lowest element on the stack. If P3 is null then all arguments are assumed to be numeric. See also: MakeKey, SortMakeKey |
| MakeKey | Convert the top P1 entries of the stack into a single entry suitable for use as the key in an index. The top P1 records are converted to strings and merged. The null-terminators are retained and used as separators. The lowest entry in the stack is the first field and the top of the stack becomes the last. If P2 is not zero, then the original entries remain on the stack and the new key is pushed on top. If P2 is zero, the original data is popped off the stack first then the new key is pushed back in its place. P3 is a string that is P1 characters long. Each character is either an 'n' or a 't' to indicates if the argument should be numeric or text. The first character corresponds to the lowest element on the stack. If P3 is NULL then all arguments are assumed to be numeric. The key is a concatenation of fields. Each field is terminated by a single 0x00 character. A NULL field is introduced by an 'a' and is followed immediately by its 0x00 terminator. A numeric field is introduced by a single character 'b' and is followed by a sequence of characters that represent the number such that a comparison of the character string using memcpy() sorts the numbers in numerical order. The character strings for numbers are generated using the sqliteRealToSortable() function. A text field is introduced by a 'c' character and is followed by the exact text of the field. The use of an 'a', 'b', or 'c' character at the beginning of each field guarantees that NULL sort before numbers and that numbers sort before text. 0x00 characters do not occur except as separators between fields. See also: MakeIdxKey, SortMakeKey |
| MakeRecord | Convert the top P1 entries of the stack into a single entry suitable for use as a data record in a database table. The details of the format are irrelavant as long as the OP_Column opcode can decode the record later. Refer to source code comments for the details of the record format. If P2 is true (non-zero) and one or more of the P1 entries that go into building the record is NULL, then add some extra bytes to the record to make it distinct for other entries created during the same run of the VDBE. The extra bytes added are a counter that is reset with each run of the VDBE, so records created this way will not necessarily be distinct across runs. But they should be distinct for transient tables (created using OP_OpenTemp) which is what they are intended for. (Later:) The P2==1 option was intended to make NULLs distinct for the UNION operator. But I have since discovered that NULLs are indistinct for UNION. So this option is never used. |
| MemIncr | Increment the integer valued memory cell P1 by 1. If P2 is not zero and the result after the increment is greater than zero, then jump to P2. This instruction throws an error if the memory cell is not initially an integer. |
| MemLoad | Push a copy of the value in memory location P1 onto the stack. If the value is a string, then the value pushed is a pointer to the string that is stored in the memory location. If the memory location is subsequently changed (using OP_MemStore) then the value pushed onto the stack will change too. |
| MemStore | Write the top of the stack into memory location P1. P1 should be a small integer since space is allocated for all memory locations between 0 and P1 inclusive. After the data is stored in the memory location, the stack is popped once if P2 is 1. If P2 is zero, then the original data remains on the stack. |
| MoveLt | Pop the top of the stack and use its value as a key. Reposition cursor P1 so that it points to the entry with the largest key that is less than the key popped from the stack. If there are no records less than than the key and P2 is not zero then an immediate jump to P2 is made. See also: MoveTo |
| MoveTo | Pop the top of the stack and use its value as a key. Reposition cursor P1 so that it points to an entry with a matching key. If the table contains no record with a matching key, then the cursor is left pointing at the first record that is greater than the key. If there are no records greater than the key and P2 is not zero, then an immediate jump to P2 is made. See also: Found, NotFound, Distinct, MoveLt |
| Multiply | Pop the top two elements from the stack, multiply them together, and push the result back onto the stack. If either element is a string then it is converted to a double using the atof() function before the multiplication. If either operand is NULL, the result is NULL. |
| MustBeInt | Force the top of the stack to be an integer. If the top of the stack is not an integer and cannot be converted into an integer with out data loss, then jump immediately to P2, or if P2==0 raise an SQLITE_MISMATCH exception. If the top of the stack is not an integer and P2 is not zero and P1 is 1, then the stack is popped. In all other cases, the depth of the stack is unchanged. |
| Ne | Pop the top two elements from the stack. If they are not equal, then jump to instruction P2. Otherwise, continue to the next instruction. If either operand is NULL (and thus if the result is unknown) then take the jump if P1 is true. If both values are numeric, they are converted to doubles using atof() and compared in that format. Otherwise the strcmp() library routine is used for the comparison. For a pure text comparison use OP_StrNe. If P2 is zero, do not jump. Instead, push an integer 1 onto the stack if the jump would have been taken, or a 0 if not. Push a NULL if either operand was NULL. |
| Negative | Treat the top of the stack as a numeric quantity. Replace it with its additive inverse. If the top of the stack is NULL its value is unchanged. |
| NewRecno | Get a new integer record number used as the key to a table. The record number is not previously used as a key in the database table that cursor P1 points to. The new record number is pushed onto the stack. |
| Next | Advance cursor P1 so that it points to the next key/data pair in its table or index. If there are no more key/value pairs then fall through to the following instruction. But if the cursor advance was successful, jump immediately to P2. See also: Prev |
| Noop | Do nothing. This instruction is often useful as a jump destination. |
| Not | Interpret the top of the stack as a boolean value. Replace it with its complement. If the top of the stack is NULL its value is unchanged. |
| NotExists | Use the top of the stack as a integer key. If a record with that key does not exist in table of P1, then jump to P2. If the record does exist, then fall thru. The cursor is left pointing to the record if it exists. The integer key is popped from the stack. The difference between this operation and NotFound is that this operation assumes the key is an integer and NotFound assumes it is a string. See also: Distinct, Found, MoveTo, NotFound, IsUnique |
| NotFound | Use the top of the stack as a string key. If a record with that key does not exist in table of P1, then jump to P2. If the record does exist, then fall thru. The cursor is left pointing to the record if it exists. The key is popped from the stack. The difference between this operation and Distinct is that Distinct does not pop the key from the stack. See also: Distinct, Found, MoveTo, NotExists, IsUnique |
| NotNull | Jump to P2 if the top value on the stack is not NULL. Pop the stack if P1 is greater than zero. If P1 is less than or equal to zero then leave the value on the stack. |
| NullCallback | Invoke the callback function once with the 2nd argument (the number of columns) equal to P1 and with the 4th argument (the names of the columns) set according to prior OP_ColumnName and OP_ColumnCount instructions. This is all like the regular OP_Callback or OP_SortCallback opcodes. But the 3rd argument which normally contains a pointer to an array of pointers to data is NULL. The callback is only invoked if there have been no prior calls to OP_Callback or OP_SortCallback. This opcode is used to report the number and names of columns in cases where the result set is empty. |
| NullRow | Move the cursor P1 to a null row. Any OP_Column operations that occur while the cursor is on the null row will always push a NULL onto the stack. |
| Open | Open a read-only cursor for the database table whose root page is P2 in the main database file. Give the new cursor an identifier of P1. The P1 values need not be contiguous but all P1 values should be small integers. It is an error for P1 to be negative. If P2==0 then take the root page number from the top of the stack. There will be a read lock on the database whenever there is an open cursor. If the database was unlocked prior to this instruction then a read lock is acquired as part of this instruction. A read lock allows other processes to read the database but prohibits any other process from modifying the database. The read lock is released when all cursors are closed. If this instruction attempts to get a read lock but fails, the script terminates with an SQLITE_BUSY error code. The P3 value is the name of the table or index being opened. The P3 value is not actually used by this opcode and may be omitted. But the code generator usually inserts the index or table name into P3 to make the code easier to read. See also OpenAux and OpenWrite. |
| OpenAux | Open a read-only cursor in the auxiliary table set. This opcode works exactly like OP_Open except that it opens the cursor on the auxiliary table set (the file used to store tables created using CREATE TEMPORARY TABLE) instead of in the main database file. See OP_Open for additional information. |
| OpenTemp | Open a new cursor that points to a table or index in a temporary database file. The temporary file is opened read/write even if the main database is read-only. The temporary file is deleted when the cursor is closed. The cursor points to a BTree table if P2==0 and to a BTree index if P2==1. A BTree table must have an integer key and can have arbitrary data. A BTree index has no data but can have an arbitrary key. This opcode is used for tables that exist for the duration of a single SQL statement only. Tables created using CREATE TEMPORARY TABLE are opened using OP_OpenAux or OP_OpenWrAux. "Temporary" in the context of this opcode means for the duration of a single SQL statement whereas "Temporary" in the context of CREATE TABLE means for the duration of the connection to the database. Same word; different meanings. |
| OpenWrAux | Open a read/write cursor in the auxiliary table set. This opcode works just like OpenWrite except that the auxiliary table set (the file used to store tables created using CREATE TEMPORARY TABLE) is used in place of the main database file. |
| OpenWrite | Open a read/write cursor named P1 on the table or index whose root page is P2. If P2==0 then take the root page number from the stack. This instruction works just like Open except that it opens the cursor in read/write mode. For a given table, there can be one or more read-only cursors or a single read/write cursor but not both. See also OpWrAux. |
| Or | Pop two values off the stack. Take the logical OR of the two values and push the resulting boolean value back onto the stack. |
| Pop | P1 elements are popped off of the top of stack and discarded. |
| Prev | Back up cursor P1 so that it points to the previous key/data pair in its table or index. If there is no previous key/value pairs then fall through to the following instruction. But if the cursor backup was successful, jump immediately to P2. |
| Pull | The P1-th element is removed from its current location on the stack and pushed back on top of the stack. The top of the stack is element 0, so "Pull 0 0 0" is a no-op. "Pull 1 0 0" swaps the top two elements of the stack. See also the Dup instruction. |
| Push | Overwrite the value of the P1-th element down on the stack (P1==0 is the top of the stack) with the value of the top of the stack. Then pop the top of the stack. |
| PutIntKey | Write an entry into the database file P1. A new entry is created if it doesn't already exist or the data for an existing entry is overwritten. The data is the value on the top of the stack. The key is the next value down on the stack. The key must be an integer. The stack is popped twice by this instruction. If P2==1 then the row change count is incremented. If P2==0 the row change count is unmodified. |
| PutStrKey | Write an entry into the database file P1. A new entry is created if it doesn't already exist or the data for an existing entry is overwritten. The data is the value on the top of the stack. The key is the next value down on the stack. The key must be a string. The stack is popped twice by this instruction. |
| ReadCookie | When P2==0, read the schema cookie from the database file and push it onto the stack. The schema cookie is an integer that is used like a version number for the database schema. Everytime the schema changes, the cookie changes to a new random value. This opcode is used during initialization to read the initial cookie value so that subsequent database accesses can verify that the cookie has not changed. If P2>0, then read global database parameter number P2. There is a small fixed number of global database parameters. P2==1 is the database version number. P2==2 is the recommended pager cache size. Other parameters are currently unused. There must be a read-lock on the database (either a transaction must be started or there must be an open cursor) before executing this instruction. |
| Recno | Push onto the stack an integer which is the first 4 bytes of the the key to the current entry in a sequential scan of the database file P1. The sequential scan should have been started using the Next opcode. |
| Remainder | Pop the top two elements from the stack, divide the first (what was on top of the stack) from the second (the next on stack) and push the remainder after division onto the stack. If either element is a string then it is converted to a double using the atof() function before the division. Division by zero returns NULL. If either operand is NULL, the result is NULL. |
| Return | Jump immediately to the next instruction after the last unreturned OP_Gosub. If an OP_Return has occurred for all OP_Gosubs, then processing aborts with a fatal error. |
| Rewind | The next use of the Recno or Column or Next instruction for P1 will refer to the first entry in the database table or index. If the table or index is empty and P2>0, then jump immediately to P2. If P2 is 0 or if the table or index is not empty, fall through to the following instruction. |
| Rollback | Cause all modifications to the database that have been made since the last Transaction to be undone. The database is restored to its state before the Transaction opcode was executed. No additional modifications are allowed until another transaction is started. This instruction automatically closes all cursors and releases both the read and write locks on the database. |
| SetCookie | When P2==0, this operation changes the value of the schema cookie on the database. The new value is top of the stack. When P2>0, the value of global database parameter number P2 is changed. See ReadCookie for more information about global database parametes. The schema cookie changes its value whenever the database schema changes. That way, other processes can recognize when the schema has changed and reread it. A transaction must be started before executing this opcode. |
| SetFirst | Read the first element from set P1 and push it onto the stack. If the set is empty, push nothing and jump immediately to P2. This opcode is used in combination with OP_SetNext to loop over all elements of a set. |
| SetFound | Pop the stack once and compare the value popped off with the contents of set P1. If the element popped exists in set P1, then jump to P2. Otherwise fall through. |
| SetInsert | If Set P1 does not exist then create it. Then insert value P3 into that set. If P3 is NULL, then insert the top of the stack into the set. |
| SetNext | Read the next element from set P1 and push it onto the stack. If there are no more elements in the set, do not do the push and fall through. Otherwise, jump to P2 after pushing the next set element. |
| SetNotFound | Pop the stack once and compare the value popped off with the contents of set P1. If the element popped does not exists in set P1, then jump to P2. Otherwise fall through. |
| ShiftLeft | Pop the top two elements from the stack. Convert both elements to integers. Push back onto the stack the top element shifted left by N bits where N is the second element on the stack. If either operand is NULL, the result is NULL. |
| ShiftRight | Pop the top two elements from the stack. Convert both elements to integers. Push back onto the stack the top element shifted right by N bits where N is the second element on the stack. If either operand is NULL, the result is NULL. |
| Sort | Sort all elements on the sorter. The algorithm is a mergesort. |
| SortCallback | The top of the stack contains a callback record built using the SortMakeRec operation with the same P1 value as this instruction. Pop this record from the stack and invoke the callback on it. |
| SortMakeKey | Convert the top few entries of the stack into a sort key. The number of stack entries consumed is the number of characters in the string P3. One character from P3 is prepended to each entry. The first character of P3 is prepended to the element lowest in the stack and the last character of P3 is prepended to the top of the stack. All stack entries are separated by a \000 character in the result. The whole key is terminated by two \000 characters in a row. "N" is substituted in place of the P3 character for NULL values. See also the MakeKey and MakeIdxKey opcodes. |
| SortMakeRec | The top P1 elements are the arguments to a callback. Form these elements into a single data entry that can be stored on a sorter using SortPut and later fed to a callback using SortCallback. |
| SortNext | Push the data for the topmost element in the sorter onto the stack, then remove the element from the sorter. If the sorter is empty, push nothing on the stack and instead jump immediately to instruction P2. |
| SortPut | The TOS is the key and the NOS is the data. Pop both from the stack and put them on the sorter. The key and data should have been made using SortMakeKey and SortMakeRec, respectively. |
| SortReset | Remove any elements that remain on the sorter. |
| StrEq | Pop the top two elements from the stack. If they are equal, then jump to instruction P2. Otherwise, continue to the next instruction. If either operand is NULL (and thus if the result is unknown) then take the jump if P1 is true. The strcmp() library routine is used for the comparison. For a numeric comparison, use OP_Eq. If P2 is zero, do not jump. Instead, push an integer 1 onto the stack if the jump would have been taken, or a 0 if not. Push a NULL if either operand was NULL. |
| StrGe | Pop the top two elements from the stack. If second element (the next on stack) is greater than or equal to the first (the top of stack), then jump to instruction P2. In other words, jump if NOS>=TOS. If either operand is NULL (and thus if the result is unknown) then take the jump if P1 is true. The strcmp() library routine is used for the comparison. For a numeric comparison, use OP_Ge. If P2 is zero, do not jump. Instead, push an integer 1 onto the stack if the jump would have been taken, or a 0 if not. Push a NULL if either operand was NULL. |
| StrGt | Pop the top two elements from the stack. If second element (the next on stack) is greater than the first (the top of stack), then jump to instruction P2. In other words, jump if NOS>TOS. If either operand is NULL (and thus if the result is unknown) then take the jump if P1 is true. The strcmp() library routine is used for the comparison. For a numeric comparison, use OP_Gt. If P2 is zero, do not jump. Instead, push an integer 1 onto the stack if the jump would have been taken, or a 0 if not. Push a NULL if either operand was NULL. |
| String | The string value P3 is pushed onto the stack. If P3==0 then a NULL is pushed onto the stack. |
| StrLe | Pop the top two elements from the stack. If second element (the next on stack) is less than or equal to the first (the top of stack), then jump to instruction P2. In other words, jump if NOS<=TOS. If either operand is NULL (and thus if the result is unknown) then take the jump if P1 is true. The strcmp() library routine is used for the comparison. For a numeric comparison, use OP_Le. If P2 is zero, do not jump. Instead, push an integer 1 onto the stack if the jump would have been taken, or a 0 if not. Push a NULL if either operand was NULL. |
| StrLt |
Pop the top two elements from the stack. If second element (the
next on stack) is less than the first (the top of stack), then
jump to instruction P2. Otherwise, continue to the next instruction.
In other words, jump if NOS
The strcmp() library routine is used for the comparison. For a
numeric comparison, use OP_Lt.
If P2 is zero, do not jump. Instead, push an integer 1 onto the
stack if the jump would have been taken, or a 0 if not. Push a
NULL if either operand was NULL. |
| StrNe | Pop the top two elements from the stack. If they are not equal, then jump to instruction P2. Otherwise, continue to the next instruction. If either operand is NULL (and thus if the result is unknown) then take the jump if P1 is true. The strcmp() library routine is used for the comparison. For a numeric comparison, use OP_Ne. If P2 is zero, do not jump. Instead, push an integer 1 onto the stack if the jump would have been taken, or a 0 if not. Push a NULL if either operand was NULL. |
| Subtract | Pop the top two elements from the stack, subtract the first (what was on top of the stack) from the second (the next on stack) and push the result back onto the stack. If either element is a string then it is converted to a double using the atof() function before the subtraction. If either operand is NULL, the result is NULL. |
| Transaction | Begin a transaction. The transaction ends when a Commit or Rollback opcode is encountered. Depending on the ON CONFLICT setting, the transaction might also be rolled back if an error is encountered. If P1 is true, then the transaction is started on the temporary tables of the database only. The main database file is not write locked and other processes can continue to read the main database file. A write lock is obtained on the database file when a transaction is started. No other process can read or write the file while the transaction is underway. Starting a transaction also creates a rollback journal. A transaction must be started before any changes can be made to the database. |
| VerifyCookie | Check the value of global database parameter number P2 and make sure it is equal to P1. P2==0 is the schema cookie. P1==1 is the database version. If the values do not match, abort with an SQLITE_SCHEMA error. The cookie changes its value whenever the database schema changes. This operation is used to detect when that the cookie has changed and that the current process needs to reread the schema. Either a transaction needs to have been started or an OP_Open needs to be executed (to establish a read lock) before this opcode is invoked. |
Back to the SQLite Home Page