SQLITE in Android

SQLite ( www.sqlite.org )

Learn :  http://www.vogella.de/articles/AndroidSQLite/article.html#sqlite_shell
Download: http://sourceforge.net/projects/sqlitebrowser/

1. SQLite is an Open Source Database which is embedded into Android.
2. SQLite supports standard relational database features like SQL syntax, transactions and prepared statements.
3. In addition it requires only little memory at runtime (approx. 250 KByte).
4. Data types: 
             TEXT (similar to String in Java)
            INTEGER (similar to long in Java)
            REAL (similar to double in Java).
5. SQLite itself does not validate dataType. e.g. you can write an integer into a string column and vice versa.
6. SQLite is available on every Android device. Using an SQLite database in Android does not require any database setup or administration.


SQLiteOpenHelper (class)
    1. Must override to methods:
        1. onCreate() is called by the framework, if the database does not exists.
        2. onUpgrade() is called, if the database version is increased in your application code.
            This method allows you to update the database schema.
    2.  Provide methods: (to read and write in DB )
        1. getReadableDatabase()
        2. getWriteableDatabase()
             These both return a SQLiteDatabase object that represents the database and provides methods for SQLite operations.
    3. The database tables should use the identifier _id for the primary key of the table.
   

SQLiteDatabase (public class)
 1. SQLiteDatabase has methods to create, delete, execute SQL commands, and perform other common database management tasks.
2. Database names must be unique within an application, not across all applications.
3. provides the insert(), update() and delete() methods and also execSQL() for special
4. Queries can be created via the rawQuery() and query() methods or via the SQLiteQueryBuilder class .
5. rawQuery() directly accepts an SQL statement as input.
6. query() provides a structure to do SQL query.

SQLiteQueryBuilder (public class )

     1. For complex queries, like require column aliases, you should use SQLiteQueryBuilder
     2. It provides several convienent methods for building queries.

SQLiteDatabase.CursorFactory (interface)
    1. Used to allow returning sub-classes of Cursor when calling query.
    2. public abstract Cursor newCursor (SQLiteDatabase db, SQLiteCursorDriver masterQuery, String editTable, SQLiteQuery query)
  
SQLiteCursor (class)
    1. A Cursor implementation that exposes results from a query on a SQLiteDatabase.
    2. SQLiteCursor is not internally synchronized so code using a SQLiteCursor from multiple threads should perform its own synchronization when using the SQLiteCursor.

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.

    getCount()                                       --> number of elements in the array
    moveToFirst() and moveToNext()   --> To move between individual data rows
    isAfterLast()                                    --> To check if the end of the query result has been reached.
    get*()                                              --> To access the column data Ex: getLong(columnIndex)
    columnIndex                                    --> Index of the current column
    getColumnIndexOrThrow(String)     --> to get the column index for a column name
   
ListViews, ListActivities and SimpleCursorAdapter
ListViews are Views which allow to display a list of elements.
ListActivities are specialized Activities which make the usage of ListViews easier.
Two arrays have to define column names and IDs of Views.
The SimpleCursorAdapter class will map the columns to the Views based on the Cursor passed to it.

Using SQLite
ContentProvider is typically more resource efficient as you can avoid the creation of model objects.
Loader class is used in Android 3.0, In Android 4.0 we use DAO or ContentProvider.

ContentProvider and sharing data:
Used to share data with other applications you can use a ContentProvider.
It allows applications to access data. In most cases this data is stored in an SQlite database.
Access it using URI
URI is defined as android:authorities attribute in AndroidManifest.xml file.

Own ContentProvider:
1. define a class which extends android.content.ContentProvider
2. define in "AndroidManifest.xml" file with "android:authorities" attribute which allows to identify it
3. implement several methods, e.g. query(), insert(), update(), delete(), getType() and onCreate().
In case you don't support certain methods its good practice to throw an UnsupportedOperationException().
4. Set attribute android:exported=false to use Internally