Chapter1 ·aG8 the publ oman.ae2aoi地m and even sell the code.Even the contributors are asked to sign an affidavit to protect from any copyrights warfare that may occur in future. Cross-platform:Database files from one system can be moved to a system running a different architecture without any hassle.This is possible b the database file format is binary and all the machines use the same format. In the following chapters,we will be pulling out a database from an Android emulator to Windows. a se rer and is de e attributes lead to a very lightweight database engine.SOLite Version 3.7.8 has a footprint of less than 350 KiB(kibibyte)compared to its other SQL database engines, which are much larger. Fool proof:The code base is well comme psnkoLtXae understand and modular The test cases and test scri ximat y10 times more code than the source code of SQLite library and they claim 100 percent branch test coverage.This level of testing reaffirms the faith instilled in SOLite by developers. coverage 【71
Chapter 1 [ 7 ] • No-copyright: SQLite, instead of a license, comes with a blessing. The source code of SQLite is in the public domain; you are free to modify, distribute, and even sell the code. Even the contributors are asked to sign an affidavit to protect from any copyrights warfare that may occur in future. • Cross-platform: Database files from one system can be moved to a system running a different architecture without any hassle. This is possible because the database file format is binary and all the machines use the same format. In the following chapters, we will be pulling out a database from an Android emulator to Windows. • Compact: An SQLite database is a single ordinary disk file; it comes without a server and is designed to be lightweight and simple. These attributes lead to a very lightweight database engine. SQLite Version 3.7.8 has a footprint of less than 350 KiB (kibibyte) compared to its other SQL database engines, which are much larger. • Fool proof: The code base is well commented, easy to understand, and modular. The test cases and test scripts in SQLite have approximately 1084 times more code than the source code of SQLite library and they claim 100 percent branch test coverage. This level of testing reaffirms the faith instilled in SQLite by developers. Interested readers can read more about branch test coverage from Wikipedia at http://en.wikipedia.org/wiki/Code_ coverage
Enter SQLite The SQLite architecture The core,SQLcompiler,backend,and database form the SQLite architecture Interface Tokenizer B-Tre The SQLite interface At the top of the SQLite library stack,according to documentation,much of the public interface to the SQLite library is implemented by the wen.c,legacy.c, andsource fles.This is the point of communication for other programs The SQL compiler string passed from ok nthe interfa ensand hands the Bison and.at the same time.is thread safe and prevents memory leaks.The parser builds a parse tree from the tokens passed by the tokenizer and passes the tree to the egenerator.The generator produces virtual machine code from the input and machi as executables.More information about the Lemon parser generator can be found at http://en.wikipedia.org/wiki/Lemon_Parser_ enerator. [8]
Enter SQLite [ 8 ] The SQLite architecture The core, SQL compiler, backend, and database form the SQLite architecture: Interface SQL Command Processor Virtual Machine Core Backend B-Tree Pager OS Interface Tokenizer Parser Code Generator SQL Compiler The SQLite interface At the top of the SQLite library stack, according to documentation, much of the public interface to the SQLite library is implemented by the wen.c, legacy.c, and vdbeapi.c source files. This is the point of communication for other programs and scripts. The SQL compiler Tokenizer breaks the SQL string passed from the interface into tokens and hands the tokens over to the parser, one by one. Tokenizer is hand-coded in C. The parser for SQLite is generated by the Lemon parser generator. It is faster than YACC and Bison and, at the same time, is thread safe and prevents memory leaks. The parser builds a parse tree from the tokens passed by the tokenizer and passes the tree to the code generator. The generator produces virtual machine code from the input and passes it to the virtual machine as executables. More information about the Lemon parser generator can be found at http://en.wikipedia.org/wiki/Lemon_Parser_ Generator
Chapter1 The virtual machine The virtual machine.also known as virtual database engine (VDBE).is the heart of SQLite.It is responsible for fetching and changing values in the database.It ex the program generated by the code generator to manipulate database files.Each SOL statem ent is first converted into virtual machine language for VDBE.Each instruction of VDBE contains an opcode and up to three additional operands. The SQLite backend w et3 Pe平 h nodin organ d ng b back datB-r requ che this est is the eicient and relabe manner.The eracthe asts provides an abstraction layer to port to different operating systems.It hides the unneces ary details of communicating with different operating systems from SQLite calls and handles them on behalf of SQLite. These are the internals of SQLite and an application developer in Android need not droid because the SQLite Andro ly used the concept of abstra tion and all en.On tneooa5t an the that will catert the poss sible use A quick review of database fundamentals A database,in simple words,is an organized way to store data in a continual fashion.Data is saved in tables.A table consists of columns with different datatypes. mtheYou may think of a tablc as a山 of object-orie programmi that ohiect I91
Chapter 1 [ 9 ] The virtual machine The virtual machine, also known as Virtual Database Engine (VDBE), is the heart of SQLite. It is responsible for fetching and changing values in the database. It executes the program generated by the code generator to manipulate database files. Each SQL statement is first converted into virtual machine language for VDBE. Each instruction of VDBE contains an opcode and up to three additional operands. The SQLite backend B-trees, along with Pager and the OS Interface, form the backend of the SQLite architecture. B-trees are used to organize the data. The pager on the other hand assists B-tree by caching, modifying, and rolling back data. B-tree, when required, requests particular pages from the cache; this request is processed by the pager in an efficient and reliable manner. The OS Interface, as the name suggests, provides an abstraction layer to port to different operating systems. It hides the unnecessary details of communicating with different operating systems from SQLite calls and handles them on behalf of SQLite. These are the internals of SQLite and an application developer in Android need not worry about the internals of Android because the SQLite Android libraries have effectively used the concept of abstraction and all the complexities are hidden. One just needs to master the APIs provided, and that will cater to all the possible use cases of SQLite in an Android application. A quick review of database fundamentals A database, in simple words, is an organized way to store data in a continual fashion. Data is saved in tables. A table consists of columns with different datatypes. Every row in a table corresponds to a data record. You may think of a table as an Excel spreadsheet. From the perspective of object-oriented programming, every table in a database usually describes an object (represented by a class). Each table column illustrates a class attribute. Every record in a table represents a particular instance of that object
Enter SQLite Let's look at a quick example.Let's assume you have a shop database with a table called i Inventory.This table might be used to store the information about all the prodtehe shops.The taventory table might contain these columns Id (n er),Cost (numbe (0/1),and mer).You could then add a record t the abase for a product D Cost In stock available 1 340023 2310 1 .2 Shoe 231257 235 .Added(with the INSERT command) Modified(with the UPDATE command) Removed (with the DELETE command) You may search for particular data within a database by utiliz a query.A query (us what is known as g the SELECT mand)can i e,or a n nds Fach SO ble teres a semicolon () What is an SQLite statement? An SQLite statement is written in SQL,which is issued to a database to retrieve data or to create,insert,update,or delete data in the database. All SQLite statements start with any of the keywords:SELECT,INSERT,UPDATE,DELETE, ALTER,DROP,and so on,and all the statements end with a semicolon(:)For instance: CREATE TABLE table_name (column_name INTEGER); The CREATE TABLE command is used to create a new table in an SQLite database. A CREATE TABLE command describes the following attributes of the new table that is being created: The name of the new table The database in which the table is c the main database,the t reated.Tables The name of each column in the table -【10]
Enter SQLite [ 10 ] Let's look at a quick example. Let's assume you have a Shop database with a table called Inventory. This table might be used to store the information about all the products in the shops. The Inventory table might contain these columns: Product name (string), Product Id (number), Cost (number), In stock (0/1), and Numbers available (number). You could then add a record to the database for a product named Shoe: ID Product name Product Id Cost In stock Numbers available 1 Carpet 340023 2310 1 4 2 Shoe 231257 235 1 2 Data in the database is supposed to be checked and influenced. The data within a table can be as follows: • Added (with the INSERT command) • Modified (with the UPDATE command) • Removed (with the DELETE command) You may search for particular data within a database by utilizing what is known as a query. A query (using the SELECT command) can involve one table, or a number of tables. To generate a query, you must determine the tables, data columns, and values of the data of interest using SQL commands. Each SQL command is concluded with a semicolon (;). What is an SQLite statement? An SQLite statement is written in SQL, which is issued to a database to retrieve data or to create, insert, update, or delete data in the database. All SQLite statements start with any of the keywords: SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, and so on, and all the statements end with a semicolon (;). For instance: CREATE TABLE table_name (column_name INTEGER); The CREATE TABLE command is used to create a new table in an SQLite database. A CREATE TABLE command describes the following attributes of the new table that is being created: • The name of the new table. • The database in which the new table is created. Tables may be generated in the main database, the temp database, or in any database attached. • The name of each column in the table
Chapter1 The declared type of each column in the table. A default value or expression for each column in the table. A default relation sequence to be used with each column. Preferably,aRM KEY for the table.This will support both single-column and composite(multiple-column)primary keys. A set of SQL constraints for each table.Constraints such as UNIQUE,NOT NULL,CHECK,and FOREIGN KEY are supported. 。 In some cases,the table will be a WITHOUT ROWID table The following is a simple SQLite statement to create a table "CREATE TABLE· KEY ID INTEGER PRIMARY KEY," KEY NAME+TEXT," +KEY_UMBER+”INTEGER Here,CREATE TABLE is the command to create a table with the BLE nd are th ted with th in e of data toh stored in a column to be defined at the time of creation of the tableis the data column constraint(rules enforced on data columns in the table). SOLite su pports more attributes that can be used for creating a table,for instance, let us create a create table statement that inputs a default value for empty columns.Notice that for KEY_NAME,we are providing a default value as xyz and for the KEY_NUMBER column,we are providing a default value of 100: KEY ID ON INTEGER PRIMARY KEY, KEY_NAME+TEXT DEFAULT xyz, KEY_NUMBER INTEGER DEFAULT 100*+") 【11 www.allitebooks.com
Chapter 1 [ 11 ] • The declared type of each column in the table. • A default value or expression for each column in the table. • A default relation sequence to be used with each column. • Preferably, a PRIMARY KEY for the table. This will support both single-column and composite (multiple-column) primary keys. • A set of SQL constraints for each table. Constraints such as UNIQUE, NOT NULL, CHECK, and FOREIGN KEY are supported. • In some cases, the table will be a WITHOUT ROWID table. The following is a simple SQLite statement to create a table: String databaseTable = "CREATE TABLE " + TABLE_CONTACTS +"(" + KEY_ID + " INTEGER PRIMARY KEY," + KEY_NAME + " TEXT," + KEY_NUMBER + " INTEGER" + ")"; Here, CREATE TABLE is the command to create a table with the name TABLE_ CONTACTS. KEY_ID, KEY_NAME and KEY_NUMBER are the column IDs. SQLite requires a unique ID to be provided for each column. INTEGER and TEXT are the datatypes associated with the corresponding columns. SQLite requires the type of data to be stored in a column to be defined at the time of creation of the table. PRIMARY KEY is the data column constraint (rules enforced on data columns in the table). SQLite supports more attributes that can be used for creating a table, for instance, let us create a create table statement that inputs a default value for empty columns. Notice that for KEY_NAME, we are providing a default value as xyz and for the KEY_NUMBER column, we are providing a default value of 100: String databaseTable = "CREATE TABLE " + TABLE_CONTACTS + "(" + KEY_ID + " INTEGER PRIMARY KEY," + KEY_NAME + " TEXT DEFAULT xyz," + KEY_NUMBER + " INTEGER DEFAULT 100" + ")"; Here, when a row is inserted in the database, these columns will be preinitialized with the default values as defined in the CREATE SQL statement. www.allitebooks.com