Android - Sqlite

1 - About

sqlite supports in Android.

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

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

  • Remote. sqlite3 from adb shell. <note important>You have to be on a emulator or rooted device or you have to install sqllite3</note>

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>

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


Data Science
Data Analysis
Statistics
Data Science
Linear Algebra Mathematics
Trigonometry

Powered by ComboStrap