Data Base in Android (SQLite)

SQLite Database in Android: SQLite is an Open Source Database which is embedded into Android. SQLite supports standard relational database features like SQL syntax, transactions and prepared statements. In addition it requires only little memory at runtime (approx. 250KByte). SQLite is available on every Android device. Using an SQLite database in Android does not require any database setup or administration.

Things to consider when dealing with SQLite:

1. Data type integrity is not maintained in SQLite, you can put a value of a certain data type in a column of another dataype (put string in an integer and vice versa).
2. Referential integrity is not maintained in SQLite, there is no FOREIGN KEY constraints or JOIN statements.
3. SQLite Full Unicode support is optional and not installed by default.

You only have to define the SQL statements for creating and updating the database. Afterwards 
the database is automatically managed for you by the Android platform. Access to an SQLite database involves accessing the filesystem. This can be slow. Therefore it is recommended to perform database operations asynchronously, for example via the AsyncTask class. .

- If your application creates a database, this database is saved in the directory DATA/data/APP_NAME/database/filename. The parts of the above directory are constructed based on the following rules. DATA is the path which the Environment.getDataDirectory() method returns. APP_NAME is your application name. FILENAME  is the name you specify in your application code for the database.

SQLite Architecture

 1.Packages: The package Android.database contains all general classes for working with databases. android.database.SQlite contains the SQLite specific classes.

2. SQLiteOpenHelper: To create and upgrade a database in your Android application you usually subclass SQLiteOpenHelper. In the constructor of your subclass you call the super() method of SQLiteOpenHelper specifying the database name and the current database version. In this class you need to override the onCreate() and onUpgrade() methods. 

1. onCreate(SQLiteDatabase db): invoked when the database is created, this is where we can create tables and columns to them, create views or triggers.
2. onUpgrade(SQLiteDatabse db, int oldVersion, int newVersion): invoked when we make a modification to the database such as altering, dropping , creating new tables.This method allows you to update the database schema. 

Both methods receive an SQLiteDatabase object as parameter which represents the database. SQLiteOpenHelper provides the methods getReadableDataBase() and getWritableDatabase() to get access to an SQLiteDatabase object; either in read or write mode. The database tables should use the identifier _id for the primary key of the table.

3. SQLiteDatabase: SQLitedatabase is the base class for working with a SQLite database in Android and provides methods to open, query, update and close the database. More specifically SQLiteDatabase provides the insert(), update() and delete() methods. In addition it provides the execSQL() method, which allows to execute SQL directly. The object ContentValues allows to define key/values. The "key" represents the table column identifier and the "value" represents the content for the table record in this column. ContentValues can be used for inserts and updates of database entries. Queries can be created via the rawQuery() and query() methods or via the SQLiteQueryBuilder class .

rawQuery() directly accepts an SQL statement as input.

query() provides a structured interface for specifying the SQL query.

SQLiteQueryBuilder is a convenience class that helps to build SQL queries.

4. rawQuery() Example

The following gives an example of a rawQuery() call. the rawQuery method has two parameters:

1. String query: the select statement.
2. String[] selection args: the arguments if a WHERE clause is included in the select statement.

      Cursor cursor = getReadableDatabase().

       rawQuery("select * from todo where _id = ?", new String[] { id });

5. query() Example: The following gives an example of a query() call.

return database.query(DATABASE_TABLE,new String[] { KEY_ROWID, KEY_CATEGORY, KEY_SUMMARY, KEY_DESCRIPTION }, null, null, null, null, null); 

The method query() has the following parameters.

Parameters of the query() method

Parameter	Comment
String dbName	The table name to compile the query against.
int[] columnNames	A list of which table columns to return.  Passing "null" will return all columns.
String whereClause	Where-clause, i.e. filter for the selection  of data, null will select all data.
String[] selectionArgs	You may include ?s in the "whereClause"".  These placeholders will get replaced by the  values from the selectionArgs array.
String[] groupBy	A filter declaring how to group rows,  null will cause the rows to not be grouped.
String[] having	Filter for the groups, null means no filter.
String[] orderBy	Table columns which will be used to  order the data, null means no ordering.

If a condition is not required you can pass null, e.g. for the group by clause.

The "whereClause" is specified without the word "where", for example a "where" statement might look like: "_id=19 and summary=?". If you specify placeholder values in the where clause via ?, you pass them as the selectionArgs parameter to the query.

6. Cursor : A query returns a Cursor object . A Cursor represents the result of a query and basically points to one row of the query result. This way Android can buffer the query results efficiently; as it does not have to load all data into memory. To get the number of elements of the resulting query use the getCount() method. To move between individual data rows, you can use the moveToFirst() and moveToNext() methods. The isAfterLast() method allows to check if the end of the query result has been reached.

Cursor provides typed get*() methods, e.g. getLong(columnIndex), getString(columnIndex) to access the column data for the current position of the result. The "columnIndex" is the number of the column you are accessing.

Cursor also provides the getColumnIndexOrThrow(String) method which allows to get the column index for a column name of the table. there are some common methdos that you will use with cursors:

1. boolean moveToNext(): moves the cursor by one record in the result set, returns false if moved past the last row in the result set.
2. boolean moveToFirst(): moves the cursor to the first row in the result set, returns false if the result set is empty.
3. boolean moveToPosition(int position): moves the cursor to a certain row index within the boolean result set, returns false if the position is un-reachable
4. boolean moveToPrevious():moves the cursor to the preevious row in the result set, returns false if the cursor is past the first row.
5. boolean moveToLast():moves the cursor to the lase row in the result set, returns false if the result set is empty.

there are also some useful methods to check the position of a cursor: boolean isAfterLast(), isBeforeFirst, isFirst,isLast and isNull(columnIndex).