21 min read

In this article by Gene Da Rocha, author or the book Learning SQLite for iOS we are introduced to the background of the Structured Query Language (SQL) and the mobile database SQLite. Whether you are an experienced technologist at SQL or a novice, using the book will be a great aid to help you understand this cool subject, which is gaining momentum. SQLite is the database used on the mobile smartphone or tablet that is local to the device. SQLite has been modified by different vendors to harden and secure it for a variety of uses and applications.

(For more resources related to this topic, see here.)

SQLite was released in 2000 and has grown to be as a defacto database on a mobile or smartphone today. It is an open source piece of software with a low footprint or overhead, which is packaged with a relational database management system.

Mr D. Richard Hipp is the inventor and author for SQLite, which was designed and developed on a battleship while he was at a company called General Dynamics at the U. S. Navy. The programming was built for a HP-UX operating system with Informix as the database engine. It took many hours in the data to upgrade or install the database software and was an over-the-top database for this experience DBA (database administrator). Mr Hipp wanted a portable, self-contained, easy-to-use database, which could be mobile, quick to install, and not dependent on the operating.

Initially, SQLite 1.0 used the gdbm as its storage system, but later, it was replaced with its own B-tree implementation and technology for the database. The B-tree implementation was enhanced to support transactions and store rows of data with key order. By 2001 onwards, open source family extensions for other languages, such as Java, Python, and Perl, were written to support their applications. The database and its popularity within the open source community and others were growing.

Originally based upon relational algebra and tuple relational calculus, SQL consists of a data definition and manipulation language. The scope of SQL includes data insert, query, update and delete, schema creation and modification, and data access control. Although SQL is often described as, and to a great extent is, a declarative language (4GL), it also includes procedural elements.

Internationalization supported UTF-16 and UTF-8 and included text-collating sequences in version 2 and 3 in 2004. It was supported by funding from AOL (America Online) in 2004. It works with a variety of browsers, which sometimes have in-built support for this technology. For example, there are so many extensions that use Chrome or Firefox, which allow you to manage the database.

There have been many features added to this product. The future with the growth in mobile phones sets this quick and easy relational database system to quantum leap its use within the mobile and tablet application space.

SQLite is based on the PostgreSQL as a point of reference. SQLite does not enforce any type checking. The schema does not constrain it since the type of value is dynamic, and a trigger will be activated by converting the data type.

About SQL

In June 1970, a research paper was published by Dr. E.F. Codd called A Relational Model of Data for Large Shared Data Banks. The Association of Computer Machinery (ACM) accepted Codd data and technology model, which has today become the standard for the RDBMS (Relational Database Management System). IBM Corporation had invented the language called by Structured English Query Language (SEQUEL), where the word “English” was dropped to become SQL.

SQL is still pronounced as what has today become the standard for the RDBMS (Relational Database Management System) had a product called which has today become the SQL technology, followed by Oracle, Sybase and Microsoft’s SQL Server. The standard commercial relational database management system language today is SQL (SEQUEL).

Today, there are ANSI standards for SQL, and there are many variations of this technology. Among the mentioned manufacturers, there are also others available in the open source world, for example, an SQL query engine such as Presto.

This is the distribution engine for SQL under open source, which is made to execute interactive analytic queries. Presto queries are run under databases from a variety of data source sizes—gigabytes to petabytes.

Companies such as Facebook and Dropbox use the Presto SQL engine for their queries and analytics in data warehouse and related applications.

SQL is made up of a data manipulation and definition language built with tuple and algebra calculation in a relational format. The SQL language has a variety of statements but most would recognize the INSERT, SELECT, UPDATE and DELETE statements. These statements form a part of the database schema management process and aid the data access and security access. SQL includes procedural elements as part of its setup.

Is SQLite used anywhere?

Companies may use applications but they are not aware of the SQL engines that drive their data storage and information. Although, it has become a standard with the American National Standards Institute (ANSI) in 1986, SQL features and functionality are not 100% portable among different SQL systems and require code changes to be useful. These standards are always up for revision to ensure ANSI is maintained.

There are many variants of SQL engines on the market from companies, such as Oracle, SQL Server (Microsoft), DB2 (IBM), Sybase (SAP), MYSQL (Oracle), and others. Different companies operate several types of pricing structures, such as free open source, or a paid per seat or by transactions or server types or loads. Today, there is a preference for using server technology and SQL in the cloud with different providers, for example, Amazon Web Services (AWS).

SQLite, as it names suggests, is SQL in a light environment, which is also flexible and versatile.

Enveloped and embedded database among other processes

SQLite has been designed and developed to work and coexist with other applications and processes in its area. RDBMS is tightly integrated with the native application software, which requires storing information but is masked and hidden from users, and it requires minimal administration or maintenance.

SQLite can work with different API hidden from users and requires minimal administration or maintenance areas. RDBMS is intertwined with other applications; that is, it requires minimal supervision; there is no network traffic; no network access conflicts or configuration; no access limitations with privileges or permissions; and a large reduced overhead. These make it easier and quicker to deploy your applications to the app stores or other locations.

The different components work seamlessly together in a harmonized way to link up data with the SQLite library and other processes. These show how the Apache process and the C/C++ process work together with the SQLite-C library to interface and link with it so that it becomes seamless and integrates with the operating system.

SQLite has been developed and integrated in such a way that it will interface and gel with a variety of applications and multiple solutions. As a lightweight RDBMS, it can stand on its own by its versatility and is not cumbersome or too complex to benefit your application. It can be used on many platforms and comes with a binary compatible format, which is easier to dovetail within your mobile application.

The different types of I.T. professionals will be involved with SQLite since it holds the data, affects performance, and involves database design, user or mobile interface design specialists, analysts and consultancy types. These professionals could use their previous knowledge of SQL to quickly grasp SQLite. SQLite can act as both data processor for information or deal with data in memory to perform well.

The different software pieces of a jigsaw can interface properly by using the C API interface to SQLite, which some another programming language code. For example, C or C++ code can be programmed to communicate with the SQLITE C API, which will then talk to the operating system, and thus communicate with the database engine. Another language such as PHP can communicate using its own language data objects, which will in turn communicate with the SQLite C API and the database.

SQLite is a great database to learn especially for computer scientists who want to use a tool that can open your mind to investigate caching, B-Tree structures and algorithms, database design architecture, and other concepts.

The architecture of the SQLite database

As a library within the OS-Interface, SQLite will have many functions implemented through a programming called tclsqlite.c. Since many technologies and reserved words are used, to language, and in this case, it will have the C language. The core functions are to be found in main.c, legacy.c, and vmbeapi.c. There is also a source code file in C for the TCL language to avoid any confusion; the prefix of sqlite3 is used at the beginning within the SQLite library.

The Tokeniser code base is found within tokenize.c. Its task is to look at strings that are passed to it and partition or separate them into tokens, which are then passed to the parser.

The Parser code base is found within parse.y. The Lemon LALR(1) parser generator is the parser for SQLite; it uses the context of tokens and assigns them a meaning. To keep within the low-sized footprint of RDBMS, only one C file is used for the parse generator.

The Code Generator is then used to create SQL statements from the outputted tokens of the parser. It will produce virtual machine code that will carry out the work of the SQL statements. Several files such as attach.c, build.c, delete.c, select.c, and update.c will handle the SQL statements and syntax.

Virtual machine executes the code that is generated from the Code Generator. It has in-built storage where each instruction may have up to three additional operands as a part of each code. The source file is called vdbe.c, which is a part of the SQLite database library. Built-in is also a computing engine, which has been specially created to integrate with the database system.

There are two header files for virtual machine; the header files that interface a link between the SQLite libraries are vdbe.h and vdbeaux.c, which have utilities used by other modules. The vdbeapi.c file also connects to virtual machine with sqlite_bind and other related interfaces. The C language routines are called from the SQL functions that reference them. For example, functions such as count() are defined in func.c and date functions are located in date.c.

B-tree is the type of table implementation used in SQLite; and the C source file is btree.c. The btree.h header file defines the interface to the B-tree system. There is a different B-tree setup for every table and index and held within the same file. There is a header portion within the btree.c, which will have details of the B-tree in a large comment field.

The Pager or Page Cache using the B-tree will ask for data in a fixed sized format. The default size is 1024 bytes, which can be between 512 and 65536 bytes. Commit and Rollback operations, coupled with the caching, reading, and writing of data are handled by Page Cache or Pager. Data locking mechanisms are also handled by the Page Cache. The C file page.c is implemented to handle requests within the SQLite library and the header file is pager.h.

The OS Interface C file is defined in os.h. It addresses how SQLite can be used on different operating systems and become transparent and portable to the user thus, becoming a valuable solution for any developer. An abstract layer to handle Win32 and POSIX compliant systems is also in place. Different operating systems have their own C file. For example, os_win.c is for Windows, os_unix.c is for Unix, coupled with their own os_win.h and os_unix.h header files.

Util.c is the C file that will handle memory allocation and string comparisons. The Utf.c C file will hold the Unicode conversion subroutines.

The Utf.c C file will hold the Unicode data, sort it within the SQL engine, and use the engine itself as a mechanism for computing data. Since the memory of the device is limited and the database size has the same constraints, the developer has to think outside the box to use these techniques.

These types of memory and resource management form a part of the approach when the overlay techniques were used in the past when disk and memory was limited.

  SELECT parameter1, STTDEV(parameter2)

      FROM Table1 Group by parameter1

      HAVING parameter1 > MAX(parameter3)

IFeatures

As part of its standards, SQLite uses and implements most of the SQL-92 standards, but not all the potential features or parts of functionality are used or realized. For example, the SQLite uses and implements most of the SQL-92 standards but not all potent columns. The support for triggers is not 100% as it cannot write output to views, but as a substitute, the INSTEAD OF statement can be used.

As mentioned previously, the use of a type for a column is different; most relational database systems assign them to individual values. SQLite will convert a string into an integer if the columns preferred type is an integer. It is a good piece of functionality when bound to this type of scripting language, but the technique is not portable to other RDBMS systems. It also has its criticisms for not having a good data integrity mechanism compared to others in relation to statically typed columns.

As mentioned previously, it has many bindings to many languages, such as Basic, C, C#, C++, D, Java, JavaScript, Lua, PHP, Objective-C, Python, Ruby, and TCL. Its popularity by the open source community and its usage by customers and developers have enabled its growth to continue.

This lightweight RDBMS can be used on Google Chrome, Firefox, Safari, Opera, and the Android Browsers and has middleware support using ADO.NET, ODBC, COM (ActiveX), and XULRunner. It also has the support for web application frameworks such as Django (Python-based), Ruby on Rails, and Bugzilla (Mozilla). There are other applications such as Adobe Photoshop Light, which uses SQLite and Skype. It is also part of the Windows 8, Symbian OS, Android, and OpenBSD operating. Apple also included it via API support via OSXvia OSXother applications like Adobe Photoshop Light.

Apart from not having the large overhead of other database engines, SQLite has some major enhancements such as the EXPLAIN keyword with its manifest typing. To control constraint conflicts, the REPLACE and ON CONFLICT statements are used. Within the same query, multiple independent databases can be accessed using the DETACH and ATTACH statements. New SQL functions and collating sequences can be created using the predefined API’s, which offer much more flexibility.

As there is no configuration required, SQLite just does the job and works. There is no need to initialize, stop, restart, or start server processes and no administrator is required to create the database with proper access control or security permits. After any failure, no user actions are required to recover the database since it is self-repairing:

  • SQLite is more advanced than is thought of in the first place. Unlike other RDBMS, it does not require a server setup via a server to serve up data or incur network traffic costs. There are no TCP/IP calls and frequent communication backwards or forwards.
  • SQLite is direct; the operating system process will deal with database access to its file; and control database writes and reads with no middle-man process handshaking.

By having no server backend, the process of installation, configuration, or administration is reduced significantly and the access to the database is granted to programs that require this type of data operations. This is an advantage in one way but is also a disadvantage for security and protection from data-driven misuse and data concurrency or data row locking mechanisms.

It also allows the database to be accessed several times by different applications at the same time.

It supports a form of portability for the cross-platform database file that can be located with the database file structure. The database file can be updated on one system and copied to another on either 32 bit or 64 bit with different architectures. This does not make a difference to SQLite.

The usage of different architecture and the promises of developers to keep the file system stable and compatible with the previous, current, and future developments will allow this database to grow and thrive. SQLite databases don’t need to upload old data to the new formatted and upgraded databases; it just works.

By having a single disk file for the database, the information can be copied on a USB and shared or just reused on another device very quickly keeping all the information intact. Other RDBMS single-disk file for the database; the information can be copied on a USB and shared or just reused on another device very quickly keeping all the information in tact to grow and thrive.

Another feature of this portable database is its size, which can start on a single 512-byte page and expand to 2147483646 pages at 65536 bytes per page or in bytes 140,737,488,224,256, which equates to about 140 terabytes. Most other RDBMS are much larger, but IBM’s Cloudscape is small with a 2MB jar file. It is still larger than SQLite. The Firebird alternative’s client (frontend) library is about 350KB, whereas the Berkeley Oracle database is around 450kb without SQL support and with one simple key/value pair’s option.

This advanced portable database system and its source code is in the public domain. They have no copyright or any claim on the source code. However, there are open source license issues and controls for some test code and documentation. This is great news for developers who might want to code up new extensions or database functionality that works with their programs, which could be made into a ‘product extension’ for SQLite.

You cannot have this sort of access to SQL source code around since everything has a patent, limited access, or just no access. There are signed affidavits by developers to disown any copyright interest in the SQLite code. SQLite is different, because it is just not governed or ruled by copyright law; the way software should really work or it used.

There are signed affidavits by developers to disown any copyright interest in the SQLite code. This means that you can define a column with a datatype of integer, but its property is dictated by the inputted values and not the column itself.

This can allow any value to be stored in any declared data type for this column with the exception of an integer primary key. This feature would suit TCL or Python, which are dynamically typed programming languages.

When you allocate space in most RDBMS in any declared char(50), the database system will allocate the full 50 bytes of disk space even if you do not allocate the full 50 bytes of disk space. So, out of char(50) sized column, three characters were used, then the disk space would be only three characters plus two for overhead including data type, length but not 50 characters such as other database engines.

This type of operation would reduce disk space usage and use only what space was required.

By using the small allocation with variable length records, the applications runs faster, the database access is quicker, manifest typing can be used, and the database is small and nimble.

The ease of using this RDBMS makes it easier for most programmers at an intermediate level to create applications using this technology with its detailed documentation and examples.

Other RDBMS are internally complex with links to data structures and objects. SQLite comprises using a virtual machine language that uses the EXPLAIN reserved word in front of a query.

Virtual machine has increased and benefitted this database engine by providing an excellent process or controlled environment between back end (where the results are computed and outputted) and the front end (where the SQL is parsed and executed).

The SQL implementation language is comparable to other RDBMS especially with its lightweight base; it does support recursive triggers and requires the FOR EACH row behavior. The FOR EACH statement is not currently supported, but functionality cannot be ruled out in the future.

There is a complete ALTER TABLE support with some exceptions. For example, the RENAME TABLE, ADD COLUMN, or ALTER COLUMN is supported, but the DROP COLUMN, ADD CONSTRAINT, or ALTER COLUMN is not supported. Again, this functionality cannot be ruled out in the future.

The RIGHT OUTER JOIN and FULL OUTER JOIN are not support, but the RIGHT OUTER JOIN, FULL OUTER JOIN, and LEFT OUTER JOIN are implemented. The views within this RDBMS are read only.

As described so far in the this article, SQLite is a nimble and easy way to use database that developers can engage with quickly, use existing skills, and output systems to mobile devices and tablets far simpler than ever before. With the advantage of today’s HTML5 and other JavaScript frameworks, the advancement of SQL and the number of SQLite installations will quantum leap.

Working with SQLite

The website for SQLite is www.sqlite.org where you can download all the binaries for the database, documentation, and source code, which works on operating systems such as Linux, Windows and MAC OS X.

The SQLite share library or DLL is the library to be used for the Windows operating system and can be installed or seen via Visual Studio with the C++ language. So, the developer can write the code using the library that is presently linked in reference via the application. When execution has taken place, the DLL will load and all references in the code will link to those in the DLL at the right time.

The SQLite3 command-line program, CLP, is a self-contained program that has all the components built in for you to run at the command line.

It also comes with an extension for TCL. So within TCL, you can connect and update the SQLite database. SQLite downloads come with the TAR version for Unix systems and the ZIP version for Windows systems.

iOS with SQLite

On the hundreds of thousands of apps on all the app stores, it would be difficult to find the one that does not require a database of some sort to store or handle data in a particular way. There are different formats of data called datafeeds, but they all require some temporary or permanent storage. Small amounts of data may not be applicable but medium or large amounts of data will require a storage mechanism such as a database to assist the app.

Using SQLite with iOS will enable developers to use their existing skills to run their DBMS on this platform as well. For SQLite, there is the C-library that is embedded and available to use with iOS with the Xcode IDE.

Apple fully supports SQLite, which uses an include statement as a part of the library call, but there is not easy made mechanism to engage. Developers also tend to use FMDB—a cocoa/objective-C wrapper around SQLite.

As SQLite is fast and lightweight, its usage of existing SQL knowledge is reliable and supported by Apple on Mac OS and iOS and support from many developers as well as being integrated without much outside involvement.

The third SQLite library is under the general tab once the main project name is highlighted on the left-hand side. Then, at the bottom of the page or within the ‘Linked Frameworks and Library’, click + and a modal window appears. Enter the word sqlite and select sqlite; then, select the libsqlite3.dylib library.

This one way to set up the environment to get going.

In effect, it is the C++ wrapper called the libsqlite3.dylib library within the framework section, which allows the API to work with the SQLite commands. The way in which a text file is created in iOS is the way SQLite will be created. It will use the location (document directory) to save the file that is the one used by iOS.

Before anything can happen, the database must be opened and ready for querying and upon the success of data, the constant SQLITE_OK is set to 0.

In order to create a table in the SQLite table using the iOS connection and API, the method sqlite3_exec is set up to work with the open sqlite3 object and the create table SQL statement with a callback function. When the callback function is executed and a status is returned of SQLITE_OK, it is successful; otherwise, the other constant SQLITE_ERROR is set to 1. Once the C++ wrapper is used and the access to SQLite commands are available, it is an easier process to use SQLite with iOS.

Summary

In this article, you read the history of SQL, the impact of relational databases, and the use of a mobile SQL database namely SQLite. It outlines the history and beginnings of SQLite and how it has grown to be the most used database on mobile devices so far.

Resources for Article:

 

Further resources on this subject:

Packt

LEAVE A REPLY

Please enter your comment!
Please enter your name here