In this chapter, I will introduce you the Structured Query Language (SQL) and the mobile database SQLite. Whether you are an experienced technologist at SQL or a novice, using this book will help you understand this cool subject, which is gaining momentum. SQLite is a database that is used on a mobile smartphone or tablet, which is local to the device. SQLite has been modified by different vendors to harden and secure it for a variety of uses and applications.
SQLite was released in 2000 and has now grown to be de facto database on a mobile or smartphone. It is an open source piece of software with a low footprint and overheads, which is packed with a RDBMS (relational database management system).
Mr. D. Richard Hipp is the inventor and author of SQLite, which was designed and developed on a battleship while he was with a company called General Dynamics in the US Navy. The programming was built for the 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 experienced 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 system.
Initially, SQLite 1.0 used 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 the data with key order. From 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 started growing.
As described in Wikipedia, SQL was as follows:
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 versions 2 and 3 in 2004. It was funded by AOL (America Online) in 2004. It works with a variety of browsers that sometimes have in-built support for this technology. For example, there are many extensions that use Chrome or Firefox that 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, where this database's use within the mobile and tablet application space will increase.
SQLite is based on 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 datatype.
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's data and technology model, which has become the standard of the RDBMS today. IBM Corporation had invented the language called Structured English Query Language (SEQUEL), where the word "English" was dropped to become SQL.
SQL has become the standard for the RDMS, which is used by databases such as Oracle, Sybase, and Microsoft's SQL Server.
Today, there are American National Standards Institute (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.
Presto 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 data manipulation and definition language built with tuple and algebra calculation in a relational format. This 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 and security accesses. SQL includes procedural elements as a part of its setup.