## SQLite Database There are many ways to handle data on mobile devices. Unstructured data such as a jpeg file or html page is best stored in files. Structured or relational data such as your list of contacts is best stored and managed using relational databases. Android supports the very simple and very cool SQLite. In this lecture, we will discuss how to use Android SQLite database through a simple demo project example. We will relate this simple example to the SQLite database you need to design and implement as part of MyRuns3. If you've not used SQLite (an industry standard and great for mobiles) before then this should be cool. I have tried to describe the software architecture around three layers: the app layer, data storage layer, and the SQLite layer. Abstraction is heavily leverage in use of SQLite on mobiles such as Android. I love abstraction -- it's a powerful idea and allows you to hide the unnecessary details from the app designer. We will also tie databases to Content Providers -- another important tool in the Android toolbox. OK. Let's get going... ## What this lecture will teach you - Databases and Content Providers - Database demo app - Content Values - Layered architecture - Cursors - Performance issues: CursorLoader and asynchronously to cursor data - The app layer: TestDatabaseActivity - The data storage layer: CommentsDataSource - The SQLite layer: finally the SQLiteOpenHelper plumbing exposed! ## Demo projects The demo code used in this lecture include: * Simple use of the camera [databasedemo.zip](../code/databasedemo.zip). This demo code is taken from Lars Voglel's tutorial on [SQLite](http://www.vogella.com/articles/AndroidSQLite/article.html) and slightly modified to include the delate all option. ## Resources **Important:** Make sure you read the course book section on (it has an example of using a background thread to query the database and another thread to write information to the database): * [SQLite Databases](http://commonsware.com/Android/) Other resources: * [Android SQLite Database and ContentProvider Tutorial](http://www.vogella.com/articles/AndroidSQLite/article.html) * Android developers [sqlite](http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html). Contains the SQLite database management classes that an application would use to manage its own private database. * [SQLiteOpenHelper](http://developer.android.com/reference/android/database/sqlite/SQLiteOpenHelper.html) a helper class to manage database creation and version management. * [SQLite Tutorial](http://souptonuts.sourceforge.net/readme_sqlite_tutorial.html) ## Databases and Content Providers Every Android app that wants to store structured data (e.g., contact information, march madness bracket) needs to create its own *private* database that the app creates and manages by itself. The database access is restricted to the app that created it. If any application wanted to share SQLite its data (e.g., contact list) with other applications running in your phone it would use [Content Providers](http://developer.android.com/guide/topics/providers/content-providers.html) -- content providers provide a uniform way to store, share and consume structured between applications but also within a single application (yes you set up an SQLite database and wrap it in a content provider to manage data). Importantly, content providers provide a generic interface to a data source by decoupling the data storage layer (the app's database) from the application layer. So the application talks content provider language and is unaware of how data is stored or the internals of the database -- it abstracts the data source and provides management APIs to the data. The database plumbing is of little interest to applications, they just want to get the data and manage it in the app domain. More on content providers in the next lecture; let's get back to databases. SQLite is a transactional database engine which is lightweight: in overhead, fast performance, small footprint and implemented as a c library. When an application creates a private database it is stored in the following folder on your phone -- but you have to root your phone to see it: ~~~ /data/data//database ~~~ ## Database demo app The application simply allows the user to add canned comments to the database and display them to the UI. Comments can be added one at a time, deleted (from the top) one at a time or all comments can be deleted. So *comment* is the main object that the user deals with. The application is very simple but allows us to understand how the database is set up and accessed. When the application first starts up there is not existing database so it displays no items. As the user adds and deletes items (i.e., comment) the UI reflects that. Finally, the user can delete all items in the data base. These various steps are shown in the screen dumps below of the app. ![](images/app.png) The first image represents the app when it is first installed and started. No database items have been stored. The next iteration is when the user has tapped the **add new** button a number of times and as a result a number of items have been (randomly selected) *inserted* in the database and rendered to a listview from the array below. Next, the user tapped on **delete first** once and the top item is *deleted* from the database and removed from the listview. ~~~{.java} String[] comments = new String[] { "England", "Dartmouth", "CS65", "is", "is", "the", "best", "coolest", "place", "in", "the", "universe!" }; ~~~ Next, the app is destroyed and then restarted. When the app is started again it shows the state of the database when it was last closed. In this case there are a number of items still in the database. ![](images/app2.png) ## Systems Design While the app is very simple the plumbing around constructing a database is complex at first. The layered architecture is shown i the figure below. The operation on the SQLite database is wrapped in a few layers of abstraction. I view these layers as follows: - **app layer** -- just deals with comments and the UI - **data storage layer** -- application specific and deals with creating the database and operations on it but these are still database independent. - **SQLIte layer** -- this deals with all the plumbing and database - specific operations through a **db helper** You won't see these layers necessarily like this in the literature but I think it helps explain the architecture and operations. There is a separation of concerns: the app deals with objects that matter to it, i.e., comments; and the lower layers deal with storage of data, and eventually the detail operations of an SQL database. You want to hide these details from the user. For example, I'm sure you are thinking -- why make this so hard? Why not have the app reach down into the SQLite code and just leave it like that. But we use abstraction -- of layering -- to hid the grubby details from the app layer. ![](images/design.png) ## Content Values and Cursors The Android framework provides two classes that support database interaction -- helps with the plumbing; these are content values and cursors. Typically an app with want to store relational data -- a set of data such a contact information (e.g., name, phone number, email address). This relational information will be stored as a row with three columns for each entry. One can view the table as a bunch of rows and columns: ~~~ ID NAME PHONE EMAIL 01 Andy Campbell 603 717 1111 campbell@cs.dartmouth.edu 02 Fred Campbell 603 717 1111 campbell@cs.dartmouth.edu 03 Jane Campbell 603 717 1111 campbell@cs.dartmouth.edu 04 Bill Campbell 603 717 1111 campbell@cs.dartmouth.edu ~~~ The ID is not part of the contact necessarily but used to index the data -- not important at the app layer, but at the data storage layer. Wow that is a lot of Campbells. [Content values](http://developer.android.com/reference/android/content/ContentValues.html) are used to insert a new row into the data base: 5 Mary Campbell 603 717 2479 campbell@cs.dartmouth.edu. So the resulting database would look like this: ~~~ ID NAME PHONE EMAIL 01 Andy Campbell 603 717 1111 campbell@cs.dartmouth.edu 02 Fred Campbell 603 717 1111 campbell@cs.dartmouth.edu 03 Jane Campbell 603 717 1111 campbell@cs.dartmouth.edu 04 Bill Campbell 603 717 1111 campbell@cs.dartmouth.edu 05 Mary Campbell 603 717 1111 campbell@cs.dartmouth.edu ~~~ ## Content Values Importantly each content value object represents a single table row and maps the column names to values. For example, if we jump a head into the data storage layer we'd see the following code that uses *ContentValues* and *Cursors*. In the code below the application wants to insert a new row into the database -- in the case of our application this is an application specific *comment*. createComment() is called with a single comment (e.g., Dartmouth). A ContentValues object is created to store the complete row which includes the comment only -- but in our contact example above would be NAME, PHONE, EMAIL -- they'd all be loaded into *values* below. **Tip:** Note, in MyRuns3 where you build a database. It will have a table of exercises. Each row will include a number of different items (under some column) -- for example: activityType, dateTime, duration, distance, avgPace, avgSpeed, calorie, heart rate, comment. Consider this row an *ExerciseEntry* -- which compares in this simple example to the Comment object. When you insert in the database you will be adding an ExerciseEntry object. When you query you might get one or more ExerciseEntry objects. Point is: your database code will be much more complex than this simple example. But it will use content values and cursors in the same manner as discussed here. ~~~{.java} public Comment createComment(String comment) { ContentValues values = new ContentValues(); values.put(MySQLiteHelper.COLUMN_COMMENT, comment); long insertId = database.insert(MySQLiteHelper.TABLE_COMMENTS, null, values); Cursor cursor = database.query(MySQLiteHelper.TABLE_COMMENTS, allColumns, MySQLiteHelper.COLUMN_ID + " = " + insertId, null, null, null, null); cursor.moveToFirst(); Comment newComment = cursorToComment(cursor); cursor.close(); return newComment; } ~~~ Once the comment is loaded into the content values the insert operation is called on the SQLite layer. ## Cursors As you can see from the snippet of code above the code inserts a comment -- the parameters passed include *values*, which is the content values object) -- and the immediately reads back what is written (paranoid code). The query on the database returns a **cursor** which includes the comment object inserted. Note, the insertId returned by the database.insert() is passed as part of the database.query(). A general comment on cursors: they can contain a single row or the complete table (all rows). In addition, cursors are not copies of the database but serve as pointers to the results set provided by the underlying layer. As you see from the snippet there is control over navigation of the cursor -- see *cursor.moveToFirst()* in the code. This method provides a managed way to move to the first element in the cursor -- recall there could be many rows returned and cursor.moveToFirst() moves the position of the cursor to the first row. There are a number of [navigation methods](http://developer.android.com/reference/android/database/Cursor.html) but here are some commonly used ones: - move(int offset) - move the cursor by a relative amount, forward or backward, from the current position. - moveToFirst() - move the cursor to the first row - moveToLast() - move the cursor to the last row. - moveToNext() - move the cursor to the next row. - moveToPosition(int position) - move the cursor to an absolute position. - moveToPrevious() - move the cursor to the previous row. Other important methods very useful for interacting with cursor objects include: - getPosition() - returns the current position of the cursor in the row set. - getCount() - returns the numbers of rows in the cursor. - getColumnNames() - returns a string array holding the names of all of the columns in the result set in the order in which they were listed in the result. - getColumnIndexOrThrow(String columnName) - returns the zero-based index for the given column name, or throws IllegalArgumentException if the column doesn't exist. - getColumnCount() - return total number of columns And finally important control methods on the object: - close() - closes the Cursor, releasing all of its resources and making it completely invalid. In our code snippet we simply move the cursor to the first row (cursor.moveToFirst()) and the call a helper function that extracts the cursor information and instantiates an comment object that includes the now application specific information. ## Performance issues: CursorLoader and asynchronously to cursor data Because it takes time to interact with a database in storage and you *do not* want to block the UI (while the code gets a potentially large amount of data back from the database) there are some clever optimizations that relate to creating threads to go off and wait for the data to be loaded into the cursor -- and only when the data is available in the cursor does the main UI code step back in. Android provides the means to *asynchronously* read from the database. The [CursorLoader](http://developer.android.com/reference/android/content/CursorLoader.html) associated with the Load Manager provides such an asynchronous mechanism. In short, the CursorLoader uses a [ContentResolver](http://developer.android.com/reference/android/content/ContentResolver.html) and returns a Cursor. The CursorLoader implements the loader protocol in a standard way for querying cursors, building on AsyncTaskLoader to perform the cursor query on a background thread so that it does not block the application's UI. ContentResolver are a mechanism accessing shared content associated with Content Providers. We will discuss this in the next lecture -- or so. ## The App Layer: TestDatabaseActivity In what follows we describe the top level app that drives the UI (and therefore user input) and interacts with a database created in the onCreate() method. We discuss the user IO and lifecycle methods associated with the launched activity that drives and managed the user interaction and the data store (at an abstract level: note, there is not exposure to the details of the database structure, it could be any sort of database -- file, SQL, something else). So the key design issue here is that the details of the database implementation (storage layer) is abstracted from the app layer. This is cool because you could, if you wanted, completely replace the database implementation defined by the storage layer. ### Creating the database TestDatabaseActivity is the application code and the UI (shown above) simply provides three buttons and a listview which is mapped to a simple_list_item_1. When the activity is launched it's onCreate() method creates the database and then opens it. All items (in this case comments) are then read from the database into a list. The list is then bound to the listview using an adapter. The comments are then rendered to the view when the list adapter is set. ~~~{.java} public class TestDatabaseActivity extends ListActivity { private CommentsDataSource datasource; @Override public void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.main); datasource = new CommentsDataSource(this); datasource.open(); List values = datasource.getAllComments(); // Use the SimpleCursorAdapter to show the // elements in a ListView ArrayAdapter adapter = new ArrayAdapter(this, android.R.layout.simple_list_item_1, values); setListAdapter(adapter); } ~~~ ### Setting up the UI and onClick() callback All the buttons in the UI use the same onClick() callback. There is a switch statement for each of the three buttons: add, delete and delete all. In the case of "add new" the app randomly selects a comment and adds the new comment to the database using datasource.createComment(comments[nextInt]). The datasource.createComment method returns the comment that was successfully written to the database. The code then adds the new comment to the adapter. The adapter is notify that the data set if changed and updates the UI using the adapter.notifyDataSetChanged() method. ~~~{.java} public void onClick(View view) { @SuppressWarnings("unchecked") ArrayAdapter adapter = (ArrayAdapter) getListAdapter(); Comment comment = null; switch (view.getId()) { case R.id.add: String[] comments = new String[] { "England", "Dartmouth", "CS65", "is", "is", "the", "best", "coolest", "place", "in", "the", "universe!" }; int nextInt = new Random().nextInt(9); // Save the new comment to the database comment = datasource.createComment(comments[nextInt]); adapter.add(comment); break; case R.id.delete: if (getListAdapter().getCount() > 0) { comment = (Comment) getListAdapter().getItem(0); datasource.deleteComment(comment); adapter.remove(comment); } break; case R.id.deleteall: if (getListAdapter().getCount() > 0) { datasource.deleteAllComments(); adapter.clear(); } break; } adapter.notifyDataSetChanged(); } ~~~ In the case of "delete first" the app will delete the first item in the database. The delete code simply checks that the database is not empty and then gets the first comment from the list adapter and calls datasource.deleteComment(comment) to remove the comment from the database. Finally, the comment is removed from the adapter. Again, adapter.notifyDataSetChanged() updates the UI. In the case of "delete all" the code simply checks the database is not empty and then issues a datasource.deleteAllComments(). The adapter is then cleared and notified on the change. ### Activity lifecycle processing The final part if the TestDatabaseActivity app code includes code for the onResume() and onPause() lifecycle events. In the case of onPause() the database is closed. In the case of onResume() the database is opened. Recall that onPause/onResume are used to release resources used by the application and then reinitialize when the application is brought back into focus. ~~~{.java} @Override protected void onResume() { datasource.open(); super.onResume(); } @Override protected void onPause() { datasource.close(); super.onPause(); } ~~~ ### App specific data storage object: comment The application simply allows the user to add canned comments to the database and display them to the UI. Comments can be added one at a time, deleted (from the top) one at a time or all comments can be deleted. So *comment* is the main object that the user deals with. The Comment class includes getters/setters for id and comment. Every time a comment needs to be added a Comment is instantiated. The Comment class is the app model and contains the data that is inserted, queried and deleted in the database. Comments are also shown in the UI. ~~~{.java} public class Comment { private long id; private String comment; public long getId() { return id; } public void setId(long id) { this.id = id; } public String getComment() { return comment; } public void setComment(String comment) { this.comment = comment; } // Will be used by the ArrayAdapter in the ListView @Override public String toString() { return comment; } } ~~~ ## The data storage layer: CommentsDataSource CommentsDataSource maintains the database connection and supports adding, fetching and deleting comments. CommentsDataSource creates the MySQLiteHelper class which details with the actual SQLite database. Very little of the internal details of the SQLite layer are exposed to the data storage layer, as you can see in the code below. The database constants such as the column names are exposed; in our case just one column name: COLUMN_COMMENT and the column ID: COLUMN_ID. These database constants are made public by the SQLite layer discussed in the next section. These public constants are needed for inserting and querying comment objects in the database. ### Constructor The CommentsDataSource constructor creates the database. It first creates a db helper MySQLiteOpenHelper to deal with operations on the database. After the db helper is created the constructor opens the database, as shown the code below. ~~~{.java} public void open() throws SQLException { database = dbHelper.getWritableDatabase(); } ~~~ The database is a [SQLiteDatabase](http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html) object, exposing methods to manage a SQLite database. SQLiteDatabase has methods to create, delete, execute SQL commands, and perform other common database management tasks. The code calls *getWritableDatabase()* to open and obtain a writable instance of the underlying database using the db helper implemented in the SQLite layer. If the database does not exist the helper executes its onCreate() handler -- see MySQLiteHelper code. Whether the database has already been created or not the getWritableDatabase() returns a reference to the database. When a database has been opened successfully for the first time it will be cached by the MySQLiteHelper within MySQLiteHelper. If there is an exception in trying to open the database due to an error with SQL parsing or execution then an [SQLException](http://developer.android.com/reference/java/sql/SQLException.html) is thrown. ~~~{.java} public class CommentsDataSource { // Database fields private SQLiteDatabase database; private MySQLiteHelper dbHelper; private String[] allColumns = { MySQLiteHelper.COLUMN_ID, MySQLiteHelper.COLUMN_COMMENT }; private static final String TAG = "DBDEMO"; public CommentsDataSource(Context context) { dbHelper = new MySQLiteHelper(context); } public void open() throws SQLException { database = dbHelper.getWritableDatabase(); } public void close() { dbHelper.close(); } ~~~ ### App specific operations on storage: insert and deleting comments The data storage layer also provides the key operations on the database in terms of the application specific data object, in our case: comments. These operations are: - createComment(String comment), which inserts a comment in the database as a content values. This method also issues a query to read back what was written. - deleteComment(Comment comment), which deletes a comment - deleteAllComments(), empties the database. - getAllComments() , which gets all the comments ~~~{.java} public Comment createComment(String comment) { ContentValues values = new ContentValues(); values.put(MySQLiteHelper.COLUMN_COMMENT, comment); long insertId = database.insert(MySQLiteHelper.TABLE_COMMENTS, null, values); Cursor cursor = database.query(MySQLiteHelper.TABLE_COMMENTS, allColumns, MySQLiteHelper.COLUMN_ID + " = " + insertId, null, null, null, null); cursor.moveToFirst(); Comment newComment = cursorToComment(cursor); // Log the comment stored Log.d(TAG, "comment = " + cursorToComment(cursor).toString() + " insert ID = " + insertId); cursor.close(); return newComment; } public void deleteComment(Comment comment) { long id = comment.getId(); Log.d(TAG, "delete comment = " + id); System.out.println("Comment deleted with id: " + id); database.delete(MySQLiteHelper.TABLE_COMMENTS, MySQLiteHelper.COLUMN_ID + " = " + id, null); } public void deleteAllComments() { System.out.println("Comment deleted all"); Log.d(TAG, "delete all = "); database.delete(MySQLiteHelper.TABLE_COMMENTS, null, null); } ~~~ Each of these application specific operations translate to specific operations on the database including relating to a single comment: - database.insert(MySQLiteHelper.TABLE_COMMENTS, null, values); - database.query(MySQLiteHelper.TABLE_COMMENTS, allColumns, MySQLiteHelper.COLUMN_ID + " = " + insertId, null, null, null, null); - database.delete(MySQLiteHelper.TABLE_COMMENTS, MySQLiteHelper.COLUMN_ID + " = " + id, null); and an operation on the complete database, in this case delete all comments and get all comments (as shown in the snippet code below): - database.delete(MySQLiteHelper.TABLE_COMMENTS, null, null); - database.query(MySQLiteHelper.TABLE_COMMENTS, allColumns, null, null, null, null, null); The code snippet below reads all comments from the database. The cursorToComment() method simply creates a comment from a cursor object and copies the string over. The getAllComments() is called by the launched activity -- TestDatabaseActivity -- to render the comments in the database to the UI when the activity is restarted after being destroyed or resumed. ~~~{.java} public List getAllComments() { List comments = new ArrayList(); Cursor cursor = database.query(MySQLiteHelper.TABLE_COMMENTS, allColumns, null, null, null, null, null); cursor.moveToFirst(); while (!cursor.isAfterLast()) { Comment comment = cursorToComment(cursor); Log.d(TAG, "get comment = " + cursorToComment(cursor).toString()); comments.add(comment); cursor.moveToNext(); } // Make sure to close the cursor cursor.close(); return comments; } private Comment cursorToComment(Cursor cursor) { Comment comment = new Comment(); comment.setId(cursor.getLong(0)); comment.setComment(cursor.getString(1)); return comment; } } ~~~ ### Database operations The definition of the operations discussed above can be found in the [SQLiteDatabase](http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#query(java.lang.String, java.lang.String[], java.lang.String, java.lang.String[], java.lang.String, java.lang.String, java.lang.String)). For example consider: **database.query(MySQLiteHelper.TABLE_COMMENTS, allColumns, null, null, null, null, null);** The definition of the method and the parameters are as follows: **public Cursor query (String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy)** Query the given table, returning a Cursor over the result set. The definition of the parameters are as follows: - **table** The table name to compile the query against. - **columns** A list of which columns to return. Passing null will return all columns, which is discouraged to prevent reading data from storage that isn't going to be used. - **selection** A filter declaring which rows to return, formatted as an SQL WHERE clause (excluding the WHERE itself). Passing null will return all rows for the given table. - **selectionArgs** You may include ?s in selection, which will be replaced by the values from selectionArgs, in order that they appear in the selection. The values will be bound as Strings. - **groupBy** A filter declaring how to group rows, formatted as an SQL GROUP BY clause (excluding the GROUP BY itself). Passing null will cause the rows to not be grouped. - **having** A filter declare which row groups to include in the cursor, if row grouping is being used, formatted as an SQL HAVING clause (excluding the HAVING itself). Passing null will cause all row groups to be included, and is required when row grouping is not being used. - **orderBy** How to order the rows, formatted as an SQL ORDER BY clause (excluding the ORDER BY itself). Passing null will use the default sort order, which may be unordered. - **limit** Limits the number of rows returned by the query, formatted as LIMIT clause. Passing null denotes no LIMIT clause. Here is another what to view the [schema syntax](http://www.vogella.com/tutorials/AndroidSQLite/article.html) that I think is clean: ![](images/table.png) The limit argument is not shown in the table but is shown in the code below and [described here](http://www.mysamplecode.com/2011/10/android-sqlite-query-example-selection.html). In the example below the "where" statement is: "run_Id=10". Because we specify placeholder values in the where clause using ``?'' we have to pass the selectionArgs parameter as well -- that is, new String[]{ String.valueOf(id) } ~~~{.java} private static final String COLUMN_LOCATION_RUN_ID = "run_id"; query(10); // only hard coded for illustration ..... public RunCursor queryRun(long id) { Cursor wrapped = getReadableDatabase().query(TABLE_RUN, null, // all columns COLUMN_RUN_ID + " = ?", // look for a run ID new String[]{ String.valueOf(id) }, // with this value null, // group by null, // having null, // Order by "1"); // limit 1 row return new RunCursor(wrapped); } ~~~ ## The SQLite layer: finally the SQLiteOpenHelper plumbing exposed! The MySQLiteHelper (which extends SQLiteOpenHelper) implements the helper class to manage database creation and version management. It implements onCreate() and onUpgrade() to take care of opening the database if it exists, creating it if it does not, and upgrading it as necessary. Transactions are used to make sure the database is always in a sensible state (e.g., database.execSQL(DATABASE_CREATE)). ### Creating the SQLite database Specifically the methods are: - onCreate(SQLiteDatabase database) is called when the database is created for the first time. This is where the creation of tables and the initial population of the tables should happen. - onUpgrade(SQLiteDatabse database, int oldVersion, int newVersion) is invoked when we make a major modification to the database such drop tables, add tables, or do anything else it needs to upgrade to the new schema version. ~~~{.java} @Override public void onCreate(SQLiteDatabase database) { database.execSQL(DATABASE_CREATE); } ~~~ The onCreate() method is invoked when the database is created for the *first time*. It calls database.execSQL(DATABASE_CREATE) to create the database. Note that the DATABASE_CREATE captures the SQL database schema for this app: ~~~{.java} // Database creation sql statement private static final String DATABASE_CREATE = "create table " + TABLE_COMMENTS + "(" + COLUMN_ID + " integer primary key autoincrement, " + COLUMN_COMMENT + " text not null);"; ~~~ We create the table and specify the columns which is simply ID and a comment. Importantly, this method is invoked when the database does not exist on the disk. Therefore it is only ever called once. After that the database only needs to be opened and not created each time the app runs -- make sense. ### SQLite create table syntax The syntax for the SQLite create table looks a little odd first time you see it. The "CREATE TABLE" command is used to create a new table in an SQLite database -- see [create table](http://www.sqlite.org/lang_createtable.html) for details. I am looking for a better reference that explains the table. - "create table " is the command issued - TABLE_COMMENTS is the name of the table for each column we need to specify 1. The name of each column in the table. 2. The declared type of each column in the table. In our example, we have two columns only the first is the COLUMN_ID and we declare the type as "integer primary key autoincrement" meaning it starts from 0 and increments up as we add entries to the table. Similarly each COLUMN_ID will have a COLUMN_COMMENT and we declared type of the column to be "text not null" If you notice the syntax for the column information is surrounded by ( ..). **Tip:** MyRuns3 will have a more complex database table than the simple example in this demo. In the code below the table is called CREATE_TABLE_ENTRIES and includes exercise objects. This code will be given out as part of the MyRuns3. The onCreate() and onUpgrade() methods are also detailed. The code in the methods looks very similar to this comment demo code but the database is considerable different. **You need to get started early on this weeks lab to build the database**. ~~~{.java} // SQL query to create the table for the first time // Data types are defined below public static final String CREATE_TABLE_ENTRIES = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME_ENTRIES + " (" + KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + KEY_INPUT_TYPE + " INTEGER NOT NULL, " + KEY_ACTIVITY_TYPE + " INTEGER NOT NULL, " + KEY_DATE_TIME + " DATETIME NOT NULL, " + KEY_DURATION + " INTEGER NOT NULL, " + KEY_DISTANCE + " FLOAT, " + KEY_AVG_PACE + " FLOAT, " + KEY_AVG_SPEED + " FLOAT," + KEY_CALORIES + " INTEGER, " + KEY_CLIMB + " FLOAT, " + KEY_HEARTRATE + " INTEGER, " + KEY_COMMENT + " TEXT, " + KEY_PRIVACY + " INTEGER, " + KEY_GPS_DATA + " BLOB " + ");"; public static void onCreate(SQLiteDatabase database) { database.execSQL(CREATE_TABLE_ENTRIES); } public static void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion) { Log.w(HistoryTable.class.getName(), "Upgrading database from version " + oldVersion + " to " + newVersion + ", which will destroy all old data"); database.execSQL("DROP TABLE IF EXISTS "); onCreate(database); } ~~~ ### Constructor This MySQLiteHelper includes the constructor and two methods to deal with the database. The constructor is: ~~~{.java} public MySQLiteHelper(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); } ~~~ This creates the MySQLiteHelper (which extends [SQLiteOpenHelper](http://developer.android.com/reference/android/database/sqlite/SQLiteOpenHelper.html)) object to create, open, and manage a database. This method always returns very quickly. The database is not actually created or opened until getWritableDatabase() (see CommentsDataSource). The parameters past in the super class are as follows: - context to use to open or create the database; - name of the database file; commments.db is our case. - factory to use for creating cursor objects, or null for the default; null in our case. - version number of the database (starting at 1); if the database is older, onUpgrade(SQLiteDatabase, int, int) will be used to upgrade the database; if the database is newer, onDowngrade(SQLiteDatabase, int, int) will be used to downgrade the database. We do not use downgrade in our case. In addition, upgrade is unlikely to be called for this and MyRuns project. Note that the TABLE_COMMENTS, COLUMN_ID and COLUMN_COMMENT are made public and therefore accessible to the database storage layer, as discussed earlier. ~~~{.java} public class MySQLiteHelper extends SQLiteOpenHelper { public static final String TABLE_COMMENTS = "comments"; public static final String COLUMN_ID = "_id"; public static final String COLUMN_COMMENT = "comment"; private static final String DATABASE_NAME = "commments.db"; private static final int DATABASE_VERSION = 1; // Database creation sql statement private static final String DATABASE_CREATE = "create table " + TABLE_COMMENTS + "(" + COLUMN_ID + " integer primary key autoincrement, " + COLUMN_COMMENT + " text not null);"; public MySQLiteHelper(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); } @Override public void onCreate(SQLiteDatabase database) { database.execSQL(DATABASE_CREATE); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { Log.w(MySQLiteHelper.class.getName(), "Upgrading database from version " + oldVersion + " to " + newVersion + ", which will destroy all old data"); db.execSQL("DROP TABLE IF EXISTS " + TABLE_COMMENTS); onCreate(db); } } ~~~ Both onCreate() and onUpgrade() methods receive an SQLiteDatabase object as parameter which represents the database. SQLiteOpenHelper provides the methods getWriteableDatabase() to get access to an SQLiteDatabase object; either in read or write mode. See [SQLiteOpenHelper](http://developer.android.com/reference/android/database/sqlite/SQLiteOpenHelper.html) for a description of the getWriteableDatabase() method.The database tables should use the identifier "_id" (as shown above) for the primary key of the table.