Android - Sqlite

> Android

1 - About

sqlite supports in Android.

Sqlites comes packaged with the Android OS as a C++ library.

Advertising

3 - Management

3.1 - Database Structure with SQLiteOpenHelper

The table and columns name definition comes from the contract subclass

public class myDbHelper extends SQLiteOpenHelper {
 
    // If you change the database schema, you must increment the database version.
	// Android will call:
	//     * onCreate if the application is not yet installed
	//     * onUpgrade if the application is already installed
    private static final int DATABASE_VERSION = 2;
 
    static final String DATABASE_NAME = "myDb.db";
 
    public myDbHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }
 
    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        // Create a table to hold locations.  A location consists of the string supplied in the
        // location setting, the city name, and the latitude and longitude
        final String SQL_CREATE_CHILD_TABLE = "CREATE TABLE " + Contract.ChildTableEntry.TABLE_NAME + " (" +
                Contract.ChildTableEntry._ID + " INTEGER PRIMARY KEY," +
                Contract.ChildTableEntry.COLUMN_UNIQUE + " TEXT UNIQUE NOT NULL, " +
                Contract.ChildTableEntry.COLUMN_NAME + " TEXT NOT NULL, " +
                Contract.ChildTableEntry.COLUMN_REAL + " REAL NOT NULL, " +
                " );";
 
        final String SQL_CREATE_PARENT_TABLE = "CREATE TABLE " + Contract.ParentTableEntry.TABLE_NAME + " (" +
 
                Contract.ParentTableEntry._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
 
                // the ID of the location entry associated with this weather data
                Contract.ParentTableEntry.COLUMN_CHILD_KEY + " INTEGER NOT NULL, " +
                Contract.ParentTableEntry.COLUMN_DATE + " INTEGER NOT NULL, " +
                Contract.ParentTableEntry.COLUMN_SHORT_DESC + " TEXT NOT NULL, " +
                Contract.ParentTableEntry.COLUMN_INTEGER + " INTEGER NOT NULL," +
 
 
                // Set up the location column as a foreign key to location table.
                " FOREIGN KEY (" + Contract.ParentTableEntry.COLUMN_CHILD_KEY + ") REFERENCES " +
                Contract.ChildTableEntry.TABLE_NAME + " (" + Contract.ChildTableEntry._ID + "), " +
 
                // A UNIQUE constraint with REPLACE strategy 
		" UNIQUE (" + Contract.ParentTableEntry.COLUMN_DATE + ", " + Contract.ParentTableEntry.COLUMN_CHILD_KEY + ") ON CONFLICT REPLACE);";
 
        sqLiteDatabase.execSQL(SQL_CREATE_CHILD_TABLE);
        sqLiteDatabase.execSQL(SQL_CREATE_PARENT_TABLE);
    }
 
    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) {
        // Example for a cache 
		// Otherwise you may get "alter" statement here
        sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + Contract.ChildTableEntry.TABLE_NAME);
        sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + Contract.ParentTableEntry.TABLE_NAME);
        onCreate(sqLiteDatabase);
    }
}

3.2 - Database Operations

3.2.1 - DDL

final HashSet<String> tableNameHashSet = new HashSet<String>();
tableNameHashSet.add(Contract.ParentTableEntry.TABLE_NAME);
tableNameHashSet.add(Contract.ChildTableEntry.TABLE_NAME);
 
mContext.deleteDatabase(myDBDbHelper.DATABASE_NAME);
SQLiteDatabase db = new myDBDbHelper(this.mContext).getWritableDatabase();
assertEquals(true, db.isOpen());
 
// have we created the tables we want?
Cursor c = db.rawQuery("SELECT name FROM sqlite_master WHERE type='table'", null);
assertTrue("Error: This means that the database has not been created correctly", c.moveToFirst());
 
// verify that the tables have been created
do {
	tableNameHashSet.remove(c.getString(0));
} while( c.moveToNext() );
 
// if this fails, it means that your database doesn't contain both the location entry
// and weather entry tables
assertTrue("Error: Your database was created without both the tables", tableNameHashSet.isEmpty());
 
// now, do our tables contain the correct columns?
c = db.rawQuery("PRAGMA table_info(" + Contract.ChildTableEntry.TABLE_NAME + ")", null);
 
assertTrue("Error: This means that we were unable to query the database for table information.", c.moveToFirst());
 
// Build a HashSet of all of the column names we want to look for
final HashSet<String> childTableColumnHashSet = new HashSet<String>();
childTableColumnHashSet .add(Contract.ChildTableEntry._ID);
childTableColumnHashSet .add(Contract.ChildTableEntry.COLUMN_NAME);
childTableColumnHashSet .add(Contract.ChildTableEntry.COLUMN_COORD_LAT);
childTableColumnHashSet .add(Contract.ChildTableEntry.COLUMN_COORD_LONG);
childTableColumnHashSet .add(Contract.ChildTableEntry.COLUMN_LOCATION_SETTING);
 
int columnNameIndex = c.getColumnIndex("name");
do {
	String columnName = c.getString(columnNameIndex);
	childTableColumnHashSet .remove(columnName);
} while(c.moveToNext());
 
// if this fails, it means that your database doesn't contain all of the required location
// entry columns
assertTrue("Error: The database doesn't contain all of the required child entry columns", childTableColumnHashSet .isEmpty());
db.close();
 
// Delete the database
mContext.deleteDatabase(myDBDbHelper.DATABASE_NAME);

3.2.2 - DML

// First step: Get reference to writable database
// If there's an error in those massive SQL table creation Strings,
// errors will be thrown here when you try to get a writable database.
WeatherDbHelper dbHelper = new myDBDbHelper(mContext);
SQLiteDatabase db = dbHelper.getWritableDatabase();
 
// Second Step: Create ContentValues of what you want to insert
// (you can use the createNorthPoleLocationValues if you wish)
ContentValues testValues = new ContentValues();
testValues.put(Contract.ChildTableEntry.COLUMN_CITY_NAME, "North Pole");
testValues.put(Contract.ChildTableEntry.COLUMN_COORD_LAT, 64.7488);
testValues.put(Contract.ChildTableEntry.COLUMN_COORD_LONG, -147.353);
 
// Third Step: Insert ContentValues into database and get a row ID back
long childTableRowId;
childTableRowId = db.insert(Contract.ChildTableEntry.TABLE_NAME, null, testValues);
 
// Verify we got a row back. If the value = -1, the insert has failed ! 
assertTrue(childTableRowId != -1);
 
// Data's inserted.  
 
// Verification of the insertion the round trip.
 
// Fourth Step: Query the database and receive a Cursor back
Cursor cursor = db.query(
		Contract.ChildTableEntry.TABLE_NAME,  // Table to Query
		null, // all columns
		null, // Columns for the "where" clause
		null, // Values for the "where" clause
		null, // columns to group by
		null, // columns to filter by row groups
		null // sort order
);
 
// Move the cursor to a valid database row and check to see if we got any records back
// from the query
assertTrue( "Error: No Records returned from  query", cursor.moveToFirst() );
 
// Fifth Step: Validate data in resulting Cursor with the original ContentValues
// (you can use the validateCurrentRecord function in TestUtilities to validate the
// query if you like)
Set<Map.Entry<String, Object>> valueSet = testValues.valueSet();
for (Map.Entry<String, Object> entry : valueSet) {
	String columnName = entry.getKey();
	int idx = cursor.getColumnIndex(columnName);
	assertFalse("Column '" + columnName + "' not found. " + error, idx == -1);
	String expectedValue = entry.getValue().toString();
	assertEquals("Value '" + entry.getValue().toString() +
			"' did not match the expected value '" +
			expectedValue + "'. " + error, expectedValue, cursor.getString(idx));
}
 
// Move the cursor to demonstrate that there is only one record in the database
assertFalse( "Error: More than one record returned from location query",
		cursor.moveToNext() );
 
// Sixth Step: Close Cursor and Database
cursor.close();
db.close();

Example: Database Test (Create DB, Insert, Query, ….) See TestDb.java

3.3 - Location

Android stores SQLite databases in /data/data/[application package name]/databases

3.4 - Shell

adb -d shell sqlite3 --version
sqlite3 /data/data/com.example.google.rss.rssexample/databases/rssitems.db
  • Locally, copy the database file from your device to your host machine:
adb pull <database-file-on-device>
# Start the sqlite3 tool from the /tools directory, specifying the database file:
sqlite3 <database-file-on-host>
Advertising

3.5 - Library

  • Sqlite Asset helper - Android helper class to manage database creation and version management using an application's raw asset files.

4 - Documentation / Reference