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...

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...

The sqlite3_open() C API function is used to open a connection to the database and is held in a single operating system file. This function actually opens the file, and thus, a secure connection is made that is not shared. If the memory option is used, then the database will be created in random access memory (RAM), once the connection is established. The database will then be removed and deleted from RAM when the connection closed.

SQLite will attempt to open an existing database, and if an entered database name does not exist, then it will assume that the programmer wants to create one. SQLite is clever if you want to create a database and then close it without any operation, such as creating a table: it will not actually spend resources creating the database, only an empty file will exist:

sqlite3 aFile.db "create table aTable(field1 int); drop table aTable;"

The preceding statement will create the required default file with a table and will then drop/delete it, leaving...

These are the eight methods and two objects that form the SQLite interface. These are the basic list of functions that each user/reader must be aware of when using SQLite in code. These statements don't change, nor does their functionality. These are the key statements to ensure that users are aware of the name, format, and where these functions are used:

Using SQL within C code and the API will involve parameterized SQL—the way to include data placeholders in an SQL statement. These are the two types of parameterized binding: named and positional. See Figure 10 for more details on how these types of parameterized binding are used. The first statement is positional where its position is located or marked by a question mark, and these positions are based on the number of columns.

The real variable names setup in the programmable language, such as C or Java, as shown in the second insert statement in Figure 10, outlines the named parameters that use a colon as a prefix to indicate it on an SQL statement. By default, NULL is used as a default value if there is no value for it to be bound to.

Once a statement is bound, you can call on it again more than once without wasting the performance or time to recompile it again.

The whole idea of using parameterized SQL is to reuse the same code with different parameters without recompiling...

Handling errors is mandatory when writing systems, especially if it is for mobile devices; so, attention to detail and catching issues with code is vital. The SQLITE_BUSY and SQLITE_ERROR functions are used by programmers to notify and trap errors, and store them for a later analysis.

For example, SQL_ERROR is activated when resources such as locks cannot be granted or are not available, whereas the SQL_BUSY covers issues with transactions and related matters. Another function called sqlite3_errcode() will handle any general SQLite error. These methods and functions are the standard way of handling errors with SQLite.

The sqlite3_get_table() function is used to execute SQL statements that actually return data, such as the SELECT statement, but the sqlite3_exec() function is a one-way traffic execution and does not return any data, for example, the INSERT statement. See the following code for more information:

db1= open('property.db')
sql_statement= db1.exec("insert into property_info(id,property_id,desc) values(1,2,'Property Description 1')")sql_statement= db1.exec("insert into property_info(id,property_id,desc) values(2,2,'Property Description 2')")

Another issue with SQLite and SQL statements generally is SQL injection attacks. These can deface websites, result in data corruption, and also affect the reputation of your website and its customers. If the input to SQL parameters is direct, then a weakness could be penetrable. SQL data input must be checked and filtered to allow no one to change the current statement with data elements or even replace SQL statements to perform corrupt acts. This can be done using this statement:

SELECT * from property where property_name='%s';

The preceding code shows that an injection can take place where %s is the input string, and it can be changed to be something else, thus changing the outcome result. To protect SQL, constrain the input, use parameters with stored procedures, and use parameters with dynamic SQL to reduce the threats.

To prevent your website from being used for XSS or XSRF attacks, disallow the HTML tags in text input provided by users by using functions to find...