In this chapter, you will learn about SQLite's database concepts. Just as with most databases, SQLite too can add data using the SQL command called INSERT. It can also modify data using the UPDATE command and remove data using the DELETE command. It can also retrieve data using the SELECT command.
These four commands form the base line for any SQL database RDMS in the market. This set of commands manipulate the data, and this type of searching is called a query.
This persistent and structured way of storing data is simply called a database, and the data itself is stored using tables. Each table consists of columns and rows, with a look and feel similar to Microsoft Excel.
SQLite is based on the C language and a related API (RDBMS) in the market. The C language, for example, is easy to understand and is based on the fundamentals of database design with RDBMS. However, learning the actual API will benefit your skills and understanding.
In order to understand the API, you will have to learn the components that make up the database to improve your knowledge. Understanding data structures, SQL transactions, concurrency, and data-locking mechanisms, and creating good optimized queries will help you design great database systems.
Lastly, you need to put this understanding into some software code for the app you write and see how it is integrated and executed. The API language extension will be discussed further in this chapter.
The design objective of SQLite was to keep the role of administration and operation easy to use and simple to manage. SQLite is ACID (atomicity, consistency, isolation, and durability) compliant, and is fully transactional using T-SQL.
As a part of the design, the SQLite database has a variety of datatypes like most databases. One of the types is the INTEGER type that has 64 bit numeric values. This database uses 64 bit numeric values and the data is stored in 1, 2, 3, 4, 5, 6, or 8 bytes. The TEXT type encoding uses UTF-8 for storing it in the database. The BLOB datatype can be stored directly, with a default size of 1,000,000,000 bytes.
SQLite also uses the REAL type, which is a 64 bit floating point value, and there is the standard NULL value as well. The REAL type will be applied to the FLOAT, DOUBLE, and REAL datatypes. The TEXT type applies to the NCHAR, NVARCHAR, TEXT, and VARCHAR datatypes. The NUMERIC type applies to DATE, DATETIME, and BOOLEAN. SQLite also uses CRUD (Create, Read, Update, and Delete), and this database is not case sensitive.
The statements are shown as follows:
CREATE statement is used to create new tables in the SQLite database. The basic syntax and a simple example of CREATE TABLE is shown here. The CREATE TABLE statement has a database name that is fixed. It is followed by a table name, which has a start and close bracket. Within this statement, there is a list of column(s) to be created, starting with their names and datatypes, as shown in the following:CREATE table database-name. table-name( column1 datatype, column2 datatype, column3, datatype, PRIMARY KEY column1);INSERT statement will have a table name followed by a set of columns on the first half. The second half will have the variables, where the data coming from that will be inserted into the table. It is important to ensure that the programmer uses the same datatype as the column created; otherwise, there will be an error or a warning:INSERT into table-name(column1,column2,column3) VALUES(variable1,variable2,variable3);UPDATE statement is used to update records or rows within a table. The UPDATE statement will have a table name, followed by a set of columns to update on the left-hand side, and some data variables on the right-hand side, as shown in the following:UPDATE table-name SET column1=variable1, column2=variable2, column3=variable3) [where variable4 = 10];SELECT statement is used to select information, records, or rows within a table. This is shown in Figure 7. The SELECT statement will have a set of columns on the first half, followed by a table name and a condition, as shown here:SELECT column1, column2, column3 FROM table-name WHERE column1 > 10;DELETE statement is used to delete records or rows within a table. This is shown here. The DELETE statement will have a set of columns on the first half, followed by a table and any condition:DELETE from table-name where column1 >10;The extension and core APIs are the sections that form the API made with the C language. The core database carries out functions such as processing SQL syntax and connecting to the database. Other tasks, such as error trapping and string formatting, are also dealt with the core API. As mentioned earlier, the extension API allows programmers to add or extend the current API with a new functionality that will add the functionality that does not exist presently or as a current definition with the SQLite program.
Although data structures are outlined, as mentioned previously, using the tokenizer or parser, their importance is reduced, since coders are interested in other parts, such as the connectivity syntax, parameters, or current functions, and not the internals of the products. In order to write some good code, programmers must be clued up on SQLite locks, transactions, and the API itself.
Although not a part of the API itself, the pager and B tree parts of the SQLite system contribute heavily as a part of locking and transactions mechanisms.
There are eight methods and two objects that make up the C/C++ interface part of the SQLite database system. The two objects are: sqlite3, which is the actual database connection object, and sqlite3_stmt, which is the prepare statement object.
The eight methods comprise the following:
sqlite3_exec(): This is a wrapper functionsqlite3_close(): This is a destructor for sqlite3sqlite3_finalize():This is a destructor for sqlite3_stmtsqlite3_column(): This holds the column values for sqlite3_stmtsqlite3_step():This allows you to step to the next result row and is an advancement of sqlite3_stmtsqlite3_bind(): This is how SQL is broken down into parameters from the stored application datasqlite3_prepare():This is a part of the constructor for sqlite3_stmt, where byte code is produced from SQL that has been compiled, so it can carry out the SQL statements (SELECT, UPDATE)sqlite3_open(): This is the constructor of sqlite3, which allows a connection to an existing or a new SQLite databaseInitially, SQLite was easy to learn and had only five C/C++ interfaces, but now, it has grown in size, functionality, and interfaces to over 200 APIs. It can be daunting to use 200 APIs, but SQLite has been designed in such a way that you only use the API, but now, it has grown in size and function.
These six core interfaces, once mastered, will give programmers a great understanding of SQLite. They are listed here:
SQLite3_open(): This function makes a connection to the SQLite database and, once successful, a database connection object will be returned. None of the other interfaces will be available until the SQLite3_open() interface has been set up. They require a starting point, or a reference to a database, and a database connection object.SQLite3_prepare(): This function will convert and set up SQL statements into a formatted object, and the output will be a pointer that will be stored in reference to that object. In order to progress, this interface requires a database connection object produced by the SQLite3_open() function.SQLite3_column(): This interface does not interrogate the SQL, it just produces a prepared statement. This interface is now not the preferred choice for new applications, but the alternative SQLite3_prepare_V2() interface must be used.SQLite3_step(): This interface will look at the prepared statement as set up by the SQLite3_prepare() function and will return a single column from the current record set. This is not purely a function, but a placeholder for the type of functions that return values with different datatypes. These form a part of the results set. There are other functions that are a part of the sqlite3_column() setup, and they are, sqlite3_column_blob(), sqlite3_column_bytes(), sqlite3_column_bytes16(), sqlite3_column_count(), sqlite3_column_double(), sqlite3_column_int(), sqlite3_column_int64(), sqlite3_column_text(), sqlite3_column_type(), and sqlite3_column_value().SQLite3_finalize(): This function is the interface that destroys the prepared statement to stop any memory leaks in the system.SQLite3_close(): This interface will shut any database connection and prepared statements before closing or ceasing operations.There are other routines, such as sqlite3_bind() and sqlite3_reset(), that enable prior statements to be used again and again. Usually, statements are created, prepared, and destroyed once, but the aforementioned routines can be used at multiple instances.
SQLite has the sqlite3_config() interface that is first selected before any connections to the database are initiated. This interface will have the ability to set global changes for the database. It can also allocate memory, set up allocators for real-time embedded systems, and page caching for a predefined application usage. It can also make adjustments for different treading models.
This database system is flexible, and using sqlite3_create_collation(), sqlite3_create_function(), sqlite3_create_module(), and sqlite3_vfs_register() functions will allow the SQLite system to have a new proprietary functionality on the RDBMS. For example, the sql_create_function() function will create additional functionality for aggregate or scalar purposes. These are sqlite3_agreegate_context(), sqlite3_result(), sqlite3_user_data(), and sqlite3_value().
These are the standard built-in functionalities of the SQLite system that prove how flexible the system can be to programmers. It is this flexibility, together with the technology that has helped it grow and cultivate to a place where it has become the best SQL database for mobiles today.
In addition, there are many other interfaces and functions that are too many to include in this book. They can be found under the C/C++ interface specification of this product.
SQLite, by default, will do most of the tasks required by programmers, users, or DBAs. Programmers are always looking to go beyond the normal bounds of the relational database system, or take advantage of these extensions to fulfill their solution requirements.