Monday, November 7, 2011

Using SQLite Database with Two Tables in Android

In the Connexions app, there are 2 tables in the SQlite database: Favorites and Notes.  The app originally had 1 table for Favorites and later the Notes table was added.  The code had to be refactored because it was easier to not handle both tables in the same ContentProvider.

First the SQLiteOpenHelper inner class was pulled out of ConnexionsProvider and moved to a separate DatabaseHelper class that is shared by both ContentProviders.  This class is used to create the tables if they do not exist or to upgrade the database by adding the Notes table if the users database version number is below the version in the code.  To update a database, place the needed code in the onUpgrade() method and raise the version number.

...
/**  database version */
    private static final int DATABASE_VERSION = 5;
...
@Override
public void onCreate(SQLiteDatabase db) 
{
    db.execSQL("CREATE TABLE " + ConnexionsProvider.FAVS_TABLE + " ("
            + Favs.ID + " INTEGER PRIMARY KEY,"
            + Favs.TITLE + " TEXT,"
            + Favs.URL + " TEXT,"
            + Favs.ICON + " TEXT,"
            + Favs.OTHER + " TEXT"
            + ");");
    db.execSQL("CREATE TABLE " + ConnexionsProvider.NOTES_TABLE + " ("
            + Notes._ID + " INTEGER PRIMARY KEY,"
            + Notes.TITLE + " TEXT,"
            + Notes.NOTE + " TEXT,"
            + Notes.URL + " TEXT"
            + ");");
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) 
{
    //db.execSQL("alter table " + FAVS_TABLE + " add column " + Favs.OTHER + " text");
    db.execSQL("CREATE TABLE " + ConnexionsProvider.NOTES_TABLE + " ("
            + Notes._ID + " INTEGER PRIMARY KEY,"
            + Notes.TITLE + " TEXT,"
            + Notes.NOTE + " TEXT,"
            + Notes.URL + " TEXT"
            + ");");
}
Information about the table columns is coded in a class that implements BaseColumns. The class has static fields that are used to identify the columns of the table. Below is a code snippet from the Favs class for the Favorites table. The _id field is required as the default key for the table.
...
public class Favs implements BaseColumns
{
    /** Private constructor.  Cannot instanciate this class */
    private Favs()
    {
        
    }
    
    /** URI of code allowed to access table, I think*/
    public static final Uri CONTENT_URI = Uri.parse("content://org.cnx.android.providers.ConnexionsProvider");
    /** title column name*/
    public static final String TITLE = "fav_title";
    /** url column name*/
    public static final String URL = "fav_url";
    /** url column name*/
    public static final String ICON = "fav_icon";
    /** id column name*/
    public static final String ID = "_id";
    /** other column name*/
    public static final String OTHER = "fav_other";
}
The columns in the Favs class are mapped in a HashMap in the ConnexionsProvider class. The HashMap is used when performing a query. The NotesProvider class has similar code.
...
static
{
    FavsProjectionMap = new HashMap();
    FavsProjectionMap.put(Favs.ID, Favs.ID);
    FavsProjectionMap.put(Favs.TITLE, Favs.TITLE);
    FavsProjectionMap.put(Favs.URL, Favs.URL);
    FavsProjectionMap.put(Favs.ICON, Favs.ICON);
    FavsProjectionMap.put(Favs.OTHER, Favs.OTHER);
}
...
public Cursor query(Uri uri, String[] projection, String selection, String[] selectionArgs, String sortOrder)
{
    SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
    qb.setTables(FAVS_TABLE);
    qb.setProjectionMap(FavsProjectionMap);
    SQLiteDatabase db = dbHelper.getReadableDatabase();
    return qb.query(db, projection, selection, selectionArgs, null, null, sortOrder);
}
...
The Favorites table should not store duplicates so there is code to check for the URL of the content being added before adding it. If it is found to be already in the database, the URL is not added.
...
private boolean checkForDuplicate(String url)
{
    boolean dup = false;
    Cursor c = query(Favs.CONTENT_URI,null,"fav_url='"+url+"'",null, null);
    int urlColumn = c.getColumnIndex(Favs.URL);
    if(c.getCount()>0)
    {
        c.moveToNext();
        if(c.getString(urlColumn).equals(url))
        {
            dup = true;
        }
    }
    c.close();
    return dup;
}
...
To be used by the app, ContentProviders must be addded to the AndroidManifest.xml file.
...


...
The ContentProviders are accessed by calling getContentResolver() in an Activity. An example in the NoteEditorAvtivity class, the checkDBForNote() method queries the database to see if a note exists for the content URL. It also shows usage of the Notes class that implements BaseColumns.
...
private void checkDBForNote()
{
    cursor = getContentResolver().query(Notes.CONTENT_URI, null, "notes_url='" + content.getUrl().toString() + "'", null, null);
    if(cursor.getCount()>0)
    {
        cursor.moveToNext();
        int notesColumn = cursor.getColumnIndex(Notes.NOTE);
        editText.append(cursor.getString(notesColumn)); 
        editText.setSelection(editText.getText().length());
        state = STATE_UPDATE;
    }
    else
    {
        state = STATE_EDIT;
        editText.setText("");
    }
}
...
You can browse the source or download a zip file of the source. Connexions for Android is available in the Android Market or from the Connexions website.