Enter SQLite There are more keywords,but we don't want you to get bored with a huge list.We will be covering other keywords in the subsequent chapters. The SQLite syntax SQLite follows a unique set of rules and guidelines called syntax. An important point to be noted is that SQLite is case-insensitive,but there are moandsk at the SoLite DELETE statemenerent some comr et us loo te DE vith also DELETE FROM table WHERE (condition) Datatypes in SQLite SQLite uses a dynamic and weakly typed SQL syntax,whereas most of the SQL databases use static,rigid typing.If we look at other languages,Java is a statically typed language and Python is a dynamically typed language.So what do we mean when we say dynamic or static?Let us look at an example: a="android" In st ped will th an excepti n, whereas in a dynami ith galuo is dot Lite is backwards com This is be patible with the more common static t systems Hence the SQL statements that we use for static systems can be used seamlessly here. Storage classes e classes that are m 31h3 to as primitive datatypes: .NULL:This represents a missing value from the database INTEG This upports a range 1,2 emnagniuc .D auto. al 8-b ory,they are tege [12]
Enter SQLite [ 12 ] There are more keywords, but we don't want you to get bored with a huge list. We will be covering other keywords in the subsequent chapters. The SQLite syntax SQLite follows a unique set of rules and guidelines called syntax. An important point to be noted is that SQLite is case-insensitive, but there are some commands that are case-sensitive, for example, GLOB and glob have different meaning in SQLite. Let us look at the SQLite DELETE statement's syntax for instance. Although we have used capital letters, replacing them with lowercase letters will also work fine: DELETE FROM table WHERE {condition}; Datatypes in SQLite SQLite uses a dynamic and weakly typed SQL syntax, whereas most of the SQL databases use static, rigid typing. If we look at other languages, Java is a statically typed language and Python is a dynamically typed language. So what do we mean when we say dynamic or static? Let us look at an example: a=5 a="android" In statically typed languages, this will throw an exception, whereas in a dynamically typed language it will work. In SQLite, the datatype of a value is not associated with its container, but with the value itself. This is not a cause of concern when dealing with statically typed systems, where a value is determined by a container. This is because SQLite is backwards compatible with the more common static type systems. Hence, the SQL statements that we use for static systems can be used seamlessly here. Storage classes In SQLite, we have storage classes that are more general than datatypes. Internally, SQLite stores data in five storage classes that can also be referred to as primitive datatypes: • NULL: This represents a missing value from the database. • INTEGER: This supports a range of signed integers from 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value. SQLite handles this automatically based on the value. At the time of processing in the memory, they are converted to the most general 8-byte signed integer form
Chapter1 REAL:This is a floating point value,and SQLite uses this as an 8-byte IEEE floating point number to store such values. BLOB:This type stores a large array of binary data,exactly how it was provided as input. SQLite itself does not validate if the types written to the columns are actually of the defined type,for example,you can write an integer into a string column and vice versa.We can even have a single column with different storage classes: id col_t 23 NULL test The Boolean datatype SQLite does not have a separate stora e class for Boolean and uses the int forhis purpos ntgrsentthe falsesate wheresrprsue sate. This means that there is an indirect support for Boolean and we can create Boolean type columns only.The catch is,it won't contain the familiar TRUE/FALSE values. The Date and Time datatype As we saw for the Boolean datatype,there is no storage class for the Date and Time datatypes in SQLite.SQLite has five built-in date and time functions to help us with it;we can use date and time as integer,text,or real values.Moreover,the values are owing cod SELECT date('now'); 【13]
Chapter 1 [ 13 ] • REAL: This is a floating point value, and SQLite uses this as an 8-byte IEEE floating point number to store such values. • TEXT: SQLite supports various character encodings, such as UTF-8, UTF-16BE, or UTF-16LE. This value is a text string. • BLOB: This type stores a large array of binary data, exactly how it was provided as input. SQLite itself does not validate if the types written to the columns are actually of the defined type, for example, you can write an integer into a string column and vice versa. We can even have a single column with different storage classes: id col_t - - 1 23 2 NULL 3 test The Boolean datatype SQLite does not have a separate storage class for Boolean and uses the Integer class for this purpose. Integer 0 represents the false state whereas 1 represents a true state. This means that there is an indirect support for Boolean and we can create Boolean type columns only. The catch is, it won't contain the familiar TRUE/FALSE values. The Date and Time datatype As we saw for the Boolean datatype, there is no storage class for the Date and Time datatypes in SQLite. SQLite has five built-in date and time functions to help us with it; we can use date and time as integer, text, or real values. Moreover, the values are interchangeable, depending on the need of the application. For example, to compute the current date, use the following code: SELECT date('now');
Enter SQLite SQLite in Android ux kernel.Android runtim e Android vork,and finally Android applications that run on top of everything.The Android runtime uses Dalvik virtual machine(DVM) to execut the dex code.In newer versions of Android,that is,from KitKat(4.4), Android has enabled an experimental feature known as ART,which will eventually It is b ne (rr). pased on A we can se e provides nat ka data nGL ES WebKit and so on.These libraries,written in C/C++,run over the Linux kernel and,along with the Android runtime.forms the backbone of the application framework,as shown in the following diagram: Phone FRAMEWORK Before we start exploring SQLite in Android,let's take a look at the other persistent storage alternatives in Android: Shared preference Data is stored in a shared preference in the kev-value form.The file itself is an XML file containing the key-value pairs.The file is present in the internal storage of an application,and access to it can be preferences.It is advised to use this incase we have to savesmall collection this ase we have to save a small collectio A general example st read position in a f saving a user's prererence t ow at [14]
Enter SQLite [ 14 ] SQLite in Android The Android software stack consists of core Linux kernel, Android runtime, Android libraries that support the Android framework, and finally Android applications that run on top of everything. The Android runtime uses Dalvik virtual machine (DVM) to execute the dex code. In newer versions of Android, that is, from KitKat (4.4), Android has enabled an experimental feature known as ART, which will eventually replace DVM. It is based on Ahead of Time (AOT), whereas DVM is based on Just in Time (JIT). In the following diagram, we can see that SQLite provides native database support and is part of the libraries that support the application framework along with libraries such as SSL, OpenGL ES, WebKit, and so on. These libraries, written in C/C++, run over the Linux kernel and, along with the Android runtime, forms the backbone of the application framework, as shown in the following diagram: Surface Manager OpenGL | ES SGL Media Framework FreeType SSL SQLite Webkit libc Display Driver Keypad Driver Camera Driver WiFi Driver Flash Memory Driver Audio Drivers Blinder (IPC) Driver Power Management LINUX KERNEL LIBRARIES Core Libraries Dalvik Virtual Machine ANDROID RUNTIME APPLICATION FRAMEWORK Activity Manager Package Manager Window Manager Telephony Manager Resource Manager Content Provider Location Manager View System Notification Manager Home Contacts Phone Browser . APPLICATIONS Before we start exploring SQLite in Android, let's take a look at the other persistent storage alternatives in Android: • Shared preference: Data is stored in a shared preference in the key-value form. The file itself is an XML file containing the key-value pairs. The file is present in the internal storage of an application, and access to it can be public or private as needed. Android provides APIs to write and read shared preferences. It is advised to use this in case we have to save a small collection of such data. A general example would be saving the last read position in a PDF, or saving a user's preference to show a rating box
Chapter1 Internal/external storage:This terminology can be a little misleading Android defines two storage spaces to save files.On some devices,you might have an external storage device in form of an SD card,whereas on others, you will find that the system has partitioned its memory into two parts,to and external.Paths to the external as well as internal is linge can by us may or may not be available in c SQLite version Since API le evel 1 ihYersi versions of SOLite.We can easily verify this via the use of a too called SOLite3 d platform-tools folder inside the Android SDK installation folder adb shell soLite3 -version SQL1te3.7.11:APr16-19 SQL1te3.7.4:APr11-15 SQL1te3.6.22:API8-10 SQLite 3.5.9:API 3 7 We need not worry about the different versions of SQLite and should stick to 3.5.9 m37含2g8ewpm6io for compatibility, particular version,it will hardly matter Some additional handy SOLite3 commands are as follows .dump:To print out the contents of a table schema:To .help:For instructions 【15]
Chapter 1 [ 15 ] • Internal/external storage: This terminology can be a little misleading; Android defines two storage spaces to save files. On some devices, you might have an external storage device in form of an SD card, whereas on others, you will find that the system has partitioned its memory into two parts, to be labeled as internal and external. Paths to the external as well as internal storage can be fetched by using Android APIs. Internal storage, by default, is limited and accessible only to the application, whereas the external storage may or may not be available in case it is mounted. android:installLocation can be used in the manifest to specify the internal/external installation location of an application. SQLite version Since API level 1, Android ships with SQLite. At the time of writing this book, the current version of SQLite was 3.8.4.1. According to the documentation, the version of SQLite is 3.4.0, but different Android versions are known to ship with different versions of SQLite. We can easily verify this via the use of a tool called SQLite3 present in the platform-tools folder inside the Android SDK installation folder and Android Emulator: adb shell SQLite3 -version SQLite 3.7.11: API 16 - 19 SQLite 3.7.4: API 11 - 15 SQLite 3.6.22: API 8 - 10 SQLite 3.5.9: API 3 - 7 We need not worry about the different versions of SQLite and should stick to 3.5.9 for compatibility, or we can go by the saying that API 14 is the new minSdkVersion and switch it with 3.7.4. Until and unless you have something very specific to a particular version, it will hardly matter. Some additional handy SQLite3 commands are as follows: • .dump: To print out the contents of a table • .schema: To print the SQL CREATE statement for an existing table • .help: For instructions
Enter SOLite Database packages The android.database package contains all the necessary classes for working with databases.The android.database.SQLite package contains the SQLite-specific classes. APIs Android provides various APIs to enable us to create,access,modify,and delete a database.The complete list can be quite overwhelming;for the sake of brevity, we will cover the most important and used ones. The SQLiteOpenHelper class The soLiteopenHelper class is the first and most essential class of Android to work with SQLite databases;it is present in the android.database.SQLite namespace. sQLiteopenHelper is a helper class that is designed for extension and to implement the tasks and actions you deem important when crea ng a e.This is provided by the the SQLite d e cre application.soL en constructors defined as follows: SOLiteOpenHelper(Context context,String name,SOLiteDatabase CursorFactory factory,int version) The application context permits access to all the shared resources and assets for the application.The name parameter consists of the database file ame in the Android storage.sQLiteDatabase.CursorPactory is a factory class that creates cursor ine app n-specific on nu version parameter (or more particularly,its schema The constructor of s t is th d to cr a helper of ct t crea open,or managea The com 1 ete 0 the name of a database or null for an in-memory database.The sot cureorFactory factory creates a cursor obiect that acts as the result set for all the queries.The version parameter defines the version number of the database and is used to upgrade/downgrade the database.The errorHandler parameter in the second constructor is used when SQLite reports database corruption 【16]
Enter SQLite [ 16 ] Database packages The android.database package contains all the necessary classes for working with databases. The android.database.SQLite package contains the SQLite-specific classes. APIs Android provides various APIs to enable us to create, access, modify, and delete a database. The complete list can be quite overwhelming; for the sake of brevity, we will cover the most important and used ones. The SQLiteOpenHelper class The SQLiteOpenHelper class is the first and most essential class of Android to work with SQLite databases; it is present in the android.database.SQLite namespace. SQLiteOpenHelper is a helper class that is designed for extension and to implement the tasks and actions you deem important when creating, opening, and using a database. This helper class is provided by the Android framework to work with the SQLite database and helps in managing the database creation and version management. The modus operandi would be to extend the class and implement tasks and actions as required by our application. SQLiteOpenHelper has constructors defined as follows: SQLiteOpenHelper(Context context, String name, SQLiteDatabase. CursorFactory factory, int version) SQLiteOpenHelper(Context context, String name, SQLiteDatabase. CursorFactory factory, int version, DatabaseErrorHandler errorHandler) The application context permits access to all the shared resources and assets for the application. The name parameter consists of the database filename in the Android storage. SQLiteDatabase.CursorFactory is a factory class that creates cursor objects that act as the output set for all the queries you apply against SQLite under Android. The application-specific version number for the database will be the version parameter (or more particularly, its schema). The constructor of SQLiteOpenHelper is used to create a helper object to create, open, or manage a database. The context is the application context that allows access to all the shared resources and assets. The name parameter either contains the name of a database or null for an in-memory database. The SQLiteDatabase. CursorFactory factory creates a cursor object that acts as the result set for all the queries. The version parameter defines the version number of the database and is used to upgrade/downgrade the database. The errorHandler parameter in the second constructor is used when SQLite reports database corruption