Reasons for using SQLite

There are many features that make SQLite a great database for mobile technologies. For example, there is no administration or configuration involved, the transactions are atomic, the database is self-contained in a single cross-platform file, and it holds advanced features, such as table expressions and partial indexes. The reasons for using SQLite are listed here:

It has a small, versatile, and easy-to-use API. It is very standard-compliant and is written using the ANSI-C compliant. There are no external dependencies on any external programs or services, and the code is well commented. The source code is in the public domain and has a standalone CLI (command-line interface) at its disposal. It is cross-platform compliant, works with Mac, Linux, BSD, Android, Solaris, VxWorks, and Windows (WinCE, Win32, WinRT).

Its code footprint is very small, less than 500 kB when configured. The amount of application range that uses this database is huge. Almost all the products can have or have the need for a database that SQLite can handle.

It may not have all the bells and whistles of an enterprise system, but it is very flexible and easily available. SQLite is used by a variety of companies such as Adobe, Dropbox, Skype, and many more users.

SQLite is tested independently with its own test facilities and criteria. There are tests for memory usage, crash and power loss, fuzz tests boundary value and disable optimization tests, regression tests, and behavior checks among others. The test harnesses are also independently developed and verified.

The testing process for SQLite is well tested and matured, and the TCL tests are built using the TCL Scripting language. The test harnesses are made using the C code that creates the TCL interface. There are over 800 files of test scripts that hold over 10 gigabytes of data and over 30,000 test cases.

There are also SQL logic tests that run SQL statements against other database engines, such as SQL Server, PostgreSQL, Oracle, and SQLite itself. These form a part of the SLT (SQL Logic Test) that runs over 7 million queries and 1 gigabyte of test data as a part of the testing load.

Also, there are many types of stress and performance testing, including anomaly tests, which include the behavior of SQLite on a variety of checks and see how it performs when errors occur. All the tests are run on all the platforms that SQLite works with. There is a subset of testing scripts that are used as a quick test; however, over 200,000 test cases—enough to capture any errors, or misfit code—can still be executed quickly.

There are also tests for checking the memory usage that look at memory allocation and the use of the malloc() function. All the SQL databases use the malloc() function to allocate and release memory. Since SQLite is heavily used in embedded systems, it is required to handle errors in a graceful manner.

I/O testing is carried out to ensure that I/O errors are handled and dealt with properly. These issues maybe with regard to network errors, configuration, disk issues, or permissions. Errors are created to see their effects and to see how the software handles them.

A virtual file system (VFS) is also used to simulate the database crashing as part of the testing procedures. There are also simulations using power failures, so any measurement can be recorded. The crash test processes are completed separately.

There are also fuzz tests that take care to see that SQLite works with odd and different inputs and all the results are checked. Processes are spawned and the VFS is used to simulate crashes. In addition to the standard fuzz test, there are fuzz tests for SQL that look at the syntax and inputting to the database to check the responses and results. These form a part of the TCL testing, and there are over 100,000 fuzz tests. All the results are recorded and analyzed.

All branch tests for this database are 100% tested and measured. There are also measurements and tests to ensure that any automatic resource leaks are detected, noted, and dealt with. Usually, resource leaks occur when, in certain circumstances, resources are allocated by the malloc() function; but they are not released when other processes may require the same resource or some form of shared resource. When the resource is not freed or released as instructed, then it leads to resource leakage.

SQLite also has dynamic analysis that checks the internal and external SQLite code while the code is being executed or is in use online. This type of analysis is used to ensure that SQLite has the best availability and quality for users.

Valgrind is the simulator of the Linux binary and x86 environments. As a simulator, it is much slower, but it is effective. Memsys2 has a memory allocation system that is pluggable; it uses the malloc() and free() functions. If SQLite is compiled with the SQLite_MEMDEBUG compile-time option, then, as a part of the debugging memory allocator, a larger wrapper is used around the malloc(), realloc(), and free() functions. If Memsys2 is used, it looks for memory allocation errors at runtime.

There are the mutex subsystems in SQLite that use the sqlite3_mutex_held() and sqlite3_mutex_ notheld() function. This is a pluggable subsystem, and these two interfaces detect whether a mutex subsystem has a particular thread. SQLite uses the assert() set of functions to ensure that multithreaded applications work correctly within the database system.

SQLite uses a rollback journal to ensure that all the changes on the database are recorded before actually making changes to the database. SQLite has to work with different conditions so that it does not conflict or cause undetermined or odd behavior that must be managed. Since the code is developed in C, it may work with many implementations and libraries during development, but in the production area, it must confirm and may not work sometimes.

So, checks such as a shifting using a negative number may be tried, or trying the memcpy() function to copy buffers that are overlapping and checking that unsigned or signed variables apply to char datatypes. To cope with, and cater for these issues, the compiler (GCC) may use the -fraction to within the test suites.

Before the code is released, it goes through a ping and checks that unsigned or signed, or analyzed and compile time errors will be checked before going forward. Both the connections—Connection 1 and Connection 2—are shown as follows:

Figure 9: SQLite object model with C API

Figure 9 outlines the views that a programmer will look at—B-tree and pager, rather than components such as tokenizers or parser. Figure 9 outlines the relationship between the components. To know SQLite properly, programmers must understand the locks, API, and transactions of SQLite.

As Figure 9 illustrates, pager and B-tree access is forbidden, but important within locks and transactions. The connection to the database and SQL statements is most important when the API has many data structures. For example, a connection to any SQLite database is held as one transaction and also as one connection to the RDMS. A SQL statement is internally represented in the form of a virtual database of engine (VDBE) byte code.

With the B-tree and pager components of SQLite, it will support many database objects within each connection, as shown in Figure 9. Every database object has a B-tree object, which has a relationship with a pager object. The SQL code (SQL statements) within each connection are shown in Figure 9. Every database object has a B-tree object, which has a relationship.

When B-tree requires information, it prompts the pager component to get the data from the database. The pager component will move the data into its memory buffer, and the B-tree component will then associate it via its cursor to retrieve and view the data.

Executing SQL statements and commands is a part of the main piece of the API that has two methods, which are either prepared or wrapped queries. If the page is modified by the cursor, the original page/data must be kept just in case of a database rollback. The pager is very important and has to deal with write and read events to and from the RDBMS.

A transaction is set up once an operation is in place. For example, a database connection setup will effectively be considered as one transaction. Also remember that a connection cannot have more than one transaction open or available at any given time.

Therefore, SQL statements from a standard connection will work on the same transaction. If the conditions of your program require more than one statement in different transactions, you have to engage in using multiple connections, as illustrated in Figure 9.

It is very important to know how to set up a database connection from the application to the SQLite database. If there is no connection, then any of the commands used to retrieve, update, or insert data are useless. The connection statement will define the data process and its name and will set up a transaction to allow the data to pass to the database and back. Once a connection is made, the rest of the process sets up the database interactivity. It is not a difficult task to complete, but setting up a database is important to learn, because it is the starting point of all database activities and applications.