SQLite SQLite on Android on Android

bawltherapistSoftware and s/w Development

Dec 13, 2013 (3 years and 8 months ago)

653 views

SQLite on Android
SQLite on Android
Dropbox:
https://www.dropbox.com/sh/r8j884d8jggwomj/dXUg
TvbvyM
David Allen
Quantum Mobile Solutions
dallen@qmobilesolutions.com
V2.2 © Quantum Mobile Solutions Sep 2013SQLite on Android
SQLite on Android
Part 1
Part 1
Introduction to
Introduction to
Databases and SQLite
Databases and SQLiteIntroduction to SQLite
Introduction to SQLite
• Simple, small (~350KB), light weight RDMS implementation with
simple API
• Each database is stored as a single file containing both Pragma & Data
• Writes cause file locking and are always sequential and blocking
• Reads can be multi-tasked
• Designed for local use
• NOT Client-Server, is an integral part of the client application
• Ideal for embedded and mobile devices
• Open Source
• ACID Compliant (Atomicity, Consistency, Isolation, Durability)
• Uses dynamic, weakly types data types and syntax
• Uses SQL query language – Implements most of SQL-92
• SQL is mostly portable between major database technologies
• Does not fully implement ALTER TABLE (can’t modify/delete columns)
• Partial support for TRIGGERS (does not support ‘For Each Statement’)Introduction to SQLite
Introduction to SQLite
• SQLite is reasonably performant
• Typically comparable to MySQL
• Most reported issues are related to single file database and incorrect
pragma settings
• Performance tweaks to suite specific use
• Available on multiple OS’s
• Ships natively on most mobile platforms as standard (not Windows Phone
but SQLite library and wrappers are available)
• Version included with Android varies with OS version (& even
manufacturer):
• ≤ 2.1 : SQLite 3.5.9
• 2.2 - 2.3.3 : SQlite 3.6.22
• 3.0 – 4.0.3 : SQLite 3.7.4
• 4.1 – 4.2.x : SQLite 3.7.11
• Latest SQLite Version: 3.7.17
• SDK automatically links in the version for the lowest supported OS of
your project (usually 3.5.9 or 3.6.22)Architectural Tips
Architectural Tips
• Assess when to use a local database and when to make data
available via web services
• Be mindful of processing power, battery usage, bandwidth
• What is/are the producer and consumer of the data?
• When to use local database (such as SQLite)
• Data needs to be available when no connectivity
• Persistent Storage
• Structured Data
• Data Manipulation
• Relatively Complex Data
• Data Relationships
• When to use Web Services, Not a local database
• Highly Complex Relationships and queries
• Very Large tables
• Rapidly changing data
• Lots of ‘Data processing’Architectural Tips
Architectural Tips
- Database Design
- Database Design
• Use meaningful database, table, field and field names
• Model the database – Entity Relationship Diagram (ERD)
• Avoid Domain pollution (orthogonal and normalized)
• Normalize the database and make Orthogonal
• Tables should only store groups of intrinsically related fields which
form an “Entity”
• If a table only has 1-1 relationships – it’s fields may not need to
be in their own table (often happens if you think too OO).
• Tables must not have duplicate fields
• Tables should have one field as the primary key to uniquely
identify each record (often this is an abstract reference)
• Tables often have one or more fields as foreign or secondary keys
• Do not duplicate fields across multiple tables
• Reference data in other tables via the primary and foreign keys
• But don’t overdo it – it should make sense in the context of your
application and its’ expected life cycleArchitectural Tips
Architectural Tips
- Database Design
- Database Design
• Some denormalization is acceptable if driven by performance
considerations
• Use an abstracted access mechanism to ensure data integrity
across tables
• Your application should never alter the database schema, only the data
it holds
• Other than initialization/upgrading etc
• Avoid using the following design patterns:
• Common lookup tables
• Entity – Attribute – Value tables
• Data as elements (fields / tables)
• Enforce data integrity through database constraints
• Use foreign key constraints rather than check constraintsERD Example
ERD Example
EMPLOYEE NOTES COMPANY NOTES
EMPLOYEE TABLE COMPANY TABLE
TABLE TABLE
_ID : Integer
_ID : Integer
_ID : Integer _ID : Integer
Personal_ID : Integer Company_ID : Integer
Employee_ID : Integer Company_ID : Integer
SSN : Blob (ENCRYPTED) Company_Name : Text
DTS : Integer DTS : Integer
Company_Type : Integer
Employee_ID : Integer
Heading : Text Heading : Text
Address Line 1 : Text
Employee_Type : Integer
Body : Text Body : Text
Address Line 2 : Text
Company_ID : Integer
Group_ID : Integer Group_ID : Integer
Surname : Blob (ENCRYPTED) Address City : Text
Given_Name : Blob (ENCRYPTED) Address Region : Text
Address Postcode : Text COMPANY TYPE TABLE
Middle_Name : Blob (ENCRYPTED)
EMPLOYEE TYPE TABLE
Address Country : Text _ID : Integer
Title : Blob (ENCRYPTED)
_ID : Integer
Switchboard_Number : Text Type_ID : Integer
Postfix : Blob (ENCRYPTED)
Type_ID : Integer
Informal_Name : Blob (ENCRYPTED) Email : Text Type_Name : Text
Type_Name : Text
Address Line 1 : Blob (ENCRYPTED) Contact_Name : Text
Address Line 2 : Blob (ENCRYPTED) Contact_Position : Text
Contact_Tel : Text
Address City : Blob (ENCRYPTED)
Contact_Email : Text
Address Region : Blob (ENCRYPTED)
USER PREFERENCES TABLE
Date_Added : Integer
Postcode : Blob (ENCRYPTED)
_ID : Integer
Country : Blob (ENCRYPTED)
User_ID : Integer
Cell_Number :Blob (ENCRYPTED)
USER TABLE Language_ID : Integer
Work_Number : Blob (ENCRYPTED)
_ID : Integer Preference 1 :
Email : Blob (ENCRYPTED)
User_ID : Integer Preference 2 :
User_ID : Integer
User_Name :Text
Status : Text
User_Type : Integer
Date_Added : Integer
Password_HASH : Blob
USER TYPE TABLE
Password_Salt : Blob
_ID : Integer
AlternativeID_Flag : Boolean
Type_ID : Integer
Ephemeral_Key : Blob (ENCRYPTED)
Type_Name : Text
External_Reference : Integer
Permissions : TextSQLite on Android
SQLite on Android
Part 2
Part 2
SQLite on Android
SQLite on AndroidSQLite Data Types in Android
SQLite Data Types in Android
• SQLite is weakly typed
• It maps different primitive data types onto its own primitive types
and uses variable length storage to handle different sizes
• In theory, you don’t have to worry too much about typing
• Android SQLite does not fully implement the SQLite Mapping so you
have to do some yourself
• Supported Data Types
• Text (String)
• Integer (Byte, Integer, Short, Long)
• Real (Float, Real, Double)
• Blob (Byte Array)
• Suggested Common Mappings:
• Boolean -> Integer (0 = false, 1 = True) (define constant to use)
• Date -> Integer or Long
• Break Objects into primitivesRun Database Functions
Run Database Functions
In Separate Thread
In Separate Thread
• Database calls and functions can take significant time to complete
• You cant guarantee that the tables you are manipulating are being
stored in cached memory
• More likely, you will be accessing the data via the File System,
which is ‘slow’
• If an operation on the main thread (GUI thread) takes more than
25ms, it may be noticeable to the user
• This causes ‘janking’ – the interface becomes unresponsive and
’clunky’
• Users experience is severely degraded
• Simple queries may be fast enough, larger & complex queries, and
writes are likely to take too long
• Run Database access and operations on a separate thread
• Use background threads, threadpools, background services or even
service providers
• Pass the data back to Activates and update Views as necessary
• Use Callbacks, Event handlers, Shared variablesAndroid SQLite API Strategy
Android SQLite API Strategy
• Android Built in Helpers
• Provided as a ‘helper’ framework in Android to make it ‘easier’
• OK if you are doing very simple things and don’t want to
understand much about the database
• Very Android specific, so bad if you are developing cross platform
• Create Your Own Helper Framework
• Allows you to create an abstraction which suites your business
and/or your application
• Improves maintainability & Promotes re-use
• Using Raw SQL
• Very powerful – allows you to do just about anything
• SQL works cross platform – best approach for cross platform dev
• Does require you to learn SQL (but easy for the simple things Androids
helpers allow you to do)
• Our recommendation: Mostly raw SQL with some Built In helpers and
your own frameworkAndroid SQLite API
Android SQLite API
- Using Raw SQL
- Using Raw SQL
• SQL is a textual script
• Build your SQL script based on the data required
• Base it on ‘static’ SQL statements or
• Create SQL script builders
 Best approach
 Create ‘layered’ builders that can be combined
 Once builder framework complete, becomes easy to use and create
new queries.
 Framework easily converted to other languages for other platforms
• Methods run on a connection to the database: SQLiteDatabase class.
• Execute an SQL statement
• SQLiteDatabase.execSQL(SQL_Script);
• Execute an SQL Query
• Cursor = SQLiteDatabase.rawQuery(SQL_Query_Script, null);
• SQLStatement
• SQLiteDatabase.compileStatement(SQL_Script);Debugging Databases –
Debugging Databases –
Using the Android Device Bridge (ADB)
Using the Android Device Bridge (ADB)
• Use the ADB Bridge to look at the SQLite database and tables
• Only allows access to the database from an emulator NOT an actual
device
• A device must be Rooted to be able to see the database from
outside the application
• The database files can be seen via the Files window in Eclipse
• But NOT the schema (tables) or data
• Use the command line ADB to query the Database
• To see tables, table schemas and dataDebugging Databases –
Debugging Databases –
Using the Android Device Bridge (ADB)
Using the Android Device Bridge (ADB)
• adb –s emulator-<emulatorID> shell (or adb –s <deviceID> shell)
• e.g. adb –s emulator-5554 shell
• Results in . prompt
• sqlite3 /data/data/<Package-Name>/databases/<Database-Name>
• e.g. sqlite3 /data/data/com.qms.presentation/databases/test.db
• Results in sqlite> prompt
• Some useful commands (run against the sqlite> prompt)
• .tables - lists all the tables in the database
• select * from <table-name> - lists all the entries in the table
• select <field1-name> from <table-name> where <field2-name> =
<value> - lists the value of field1 for entries where field2 = value
• select <field1-name> from <table-name> where <field2-name>
like ‘<value>%’
• .header on – prints field names for each column listed in query
• .mode column - prints query results in columns
• .exit - takes you back to adb promptDatabase Management
Database Management
• Must Open a connection to the database file to access it
• Must Manage Database Connections
• References to Database Connections prevent objects from being garbage
collected and can cause Memory Leaks
• Not just in android, this is also true for Objective C and C#/.net
• Each instance of a database connection (SQLiteDatabase class) must be
closed within its scope to prevent memory leaks
• No equivalent of the C# Using in Java, so must do manually
• LogCat will detect failure to do this an flag them to you!Database Management
Database Management
• Create Database
• SQLiteDatabase = context.openOrCreateDatabase(dbName,
SQLiteDatabase.OPEN_READWRITE, null);
• Also opens the database - if the database already exists, will open it
• Delete Database
• Boolean = context.deleteDatabase(dbName);
• Boolean = SQLiteDatabase.deleteDatabase(dbFile);
• Open Database
• SQLiteDatabase = context.openOrCreateDatabase(dbName,
SQLiteDatabase.OPEN_READWRITE, null);
• SQLiteDatabase.openDatabase(fullDbNameIncPath, null,
SQLiteDatabase.OPEN_READWRITE);
• Close Database
• SQLiteDatabase.close();
• Set Database Version
• SQLiteDatabase.setVersion(version);
• SQLiteDatabase.execSQL("PRAGMA user_version = " + version);Database Management
Database Management
- Using SQLiteOpenHelper Class
- Using SQLiteOpenHelper Class
• Extend SQLiteOpenHelper
• Override callback methods as required
• Create an instance of it
• new YourSQLiteOpenHelper(context, dbName, null, version)
• Create or Open Database
• SQLiteDatabase = YourSQLiteOpenHelper.getWritableDatabase();
• SQLiteDatabase = YourSQLiteOpenHelper.getReadableDatabase();
• Also opens the database - if the database already exists, will open it
• Close Database
• YourSQLiteOpenHelper.close();Database Management
Database Management
- Using SQLiteOpenHelper Class
- Using SQLiteOpenHelper Class
• Provides Callback Methods to trigger schema (table) creation, schema
modification (upgrades/downgrades) and Connection configuration
• OnCreate(SQLiteDatabase mDatabase);
• OnUpgrade(SQLiteDatabase mDatabase, int oldVer, int newVer);
• OnDowngrade(SQLiteDatabase mDatabase, int oldVer, int newVer);
• OnConfigure(SQLiteDatabase mDatabase); (since API 16)
• Callbacks are convenient
• Remove the need for manually configuring connection settings each
time connection made
• Implement within a transaction automatically rolls back on failure
• Callbacks can be very useful when using with asynchronous service
such as a content provider, as allows them to defer to avoid
blockingPragma Instructions
Pragma Instructions
• Pragma Instructions configure the database and database connection
• They are applied using the SQLiteDatabase.execSQL(command)
method:
• SQLiteDatabase.execSQL("PRAGMA " + instruction + " ;");
• Selection of Pragma instructions:
• user_version = integer
• foreign_keys = On|Off
• auto_vacuum = None|Full|Incremental
• incremental_vacuum
• cache_size = integer
• page_size = integer
• case_sensitive_like = On|Off
• journal_mode = Delete|Truncate|Persist|Memory|Wal|Off
• locking_mode = Normal|Exclusive
• synchonous = Off|Normal|Full
• temp_store = Default|File|MemoryUsing Raw SQL
Using Raw SQL
Tables – Create & Delete
Tables – Create & Delete
• Create Table
• SQLiteDatabase.execSQL("CREATE TABLE " + tableName + " (" +
fieldName + DataType + “, “ + fieldName + DataType
+……..+ “, “ + fieldName + DataType + ");");
• Can set Primary Key, Not Null, Unique, AutoIncrement and others
• SQLiteDatabase.execSQL("CREATE TABLE " + tableName + " (" +
primaryKeyFieldName + " INTEGER PRIMARY KEY
AUTOINCREMENT " +……..+ “, “ + fieldName + DataType + ");");
• Example
• mDatabase.execSQL(“CREATE TABLE Company (
id INTEGER PRIMARY KEY AUTOINCREMENT,
Name TEXT UNIQUE NOT NULL, Value REAL);” );
• Delete Table
• SQLiteDatabase.execSQL(“DROP TABLE " + tableName + ";" );
• Example
• mDatabase.execSQL(“DELETE TABLE Company;”);Using Raw SQL
Using Raw SQL
Tables – Alter Table
Tables – Alter Table
• Alter Table
• Used to Modify the Schema of a Table
• Limited Implementation in SQLite
• Add Column
• SQLiteDatabase.execSQL(“ALTER TABLE " + tableName +
" ADD COLUMN" + fieldName + DataType + ";");
• Example
• mDatabase.execSQL(“ALTER TABLE Company ADD COLUMN
Category TEXT;” );
• Rename Table
• mDatabase.execSQL(“ALTER TABLE " + currentTableName +
" RENAME TO" + newTableName + ";");
• Example
• mDatabase.execSQL(“ALTER TABLE Company RENAME TO Comp;” );
• When Implementing Other Changes:
• Rename Table, Create new table, Copy data over, Delete old tableEnforcing Data Integrity
Enforcing Data Integrity
– Foreign Keys
– Foreign Keys
• To enforce foreign key constraints, you must enable them on each
connection instance (database does not store the foreign key state)
• SQLiteDatabase.execSQL("PRAGMA foreign_keys=ON;");
• Constraints are defined when creating a Table
• Enforcing a “Must Exist” constraint:
• CREATE TABLE " + tableName + " (" + fieldName + DataType + ", " +
fieldName + DataType +……..+ ", " + fieldName + DataType + “,
FOREIGN KEY + " (" + fieldName + ") REFERENCES " + refTableName
+ " (" + refFieldName + ") + “ );"
• Enforcing a “Delete References” constraint:
• Add the following to after the REFERENCE: “ON DELETE CASCADE”
• Example
• CREATE TABLE Artist (Id INTEGER PRIMARY KEY, Name TEXT);
• CREATE TABLE Album (Id INTEGER, Name TEXT, ArtistId INTEGER,
FOREIGN KEY (ArtistId) REFERENCES Artist(id) ON DELETE
CASCADE);Inserting Data into Tables
Inserting Data into Tables
• For SQL statements that return a value, use the Android Convenience
classes or use raw SQL with the SQLiteStatement class
• SQLiteStatement = SQLiteDatabase.compileStatement(sql);
• Call method on SQLiteStatement instance
• Insert Row into Table – using Raw SQL
• Use SQLiteStatement.excuteInsert();
• "INSERT INTO " + tableName + " (" + field1 + ", " + field2 + ", "
+ fieldN + ") VALUES (" + value1 + ", " + value2 +
", " + valueN + "); "
• rowNum = ( mDatabase.compileStatement(“INSERT INTO Artists (id,
name) VALUES (467, Stereophonics);”); ).excuteInsert();
• Insert Row into Table – using Android helper class
• SQLiteDatabase.insertOrThrow(tableName, null, contentValues);
• Where contentValues contains field–value pairs
• ContentValues data = new ContentValues();
• data.put(“id”, 467); data.put(“name”, “Stereophonics”);
• rowNum = mDatabase.insertOrThrow(Artists, null, data);Updating Data in Tables
Updating Data in Tables
• Using Raw SQL
• “UPDATE ” + tableName + “ SET ” + fieldNameA + “=“ + valueA + “;”
• “UPDATE ” + tableName + “ SET ” + fieldNameA + “=“ + valueA +
fieldNameB + “=“ + valueB + “ WHERE “ + fieldname1 + “=“
+ matchValue1 + “ AND ”+ fieldname2 + “= ” + matchValue2 +“;”
• Note: if any values are strings they must be surrounded with single
quotes.
• numRows = ( SQLiteDatabase.compileStatement(“UPDATE Artists SET
Id=470 WHERE Name=‘Stereophonics’;”); ).excuteInsert();
• Updating Rows using Android Helper Classes
• numRows = SQLiteDatabase.update(tableName, contentValues);
• numRows = SQLiteDatabase.update(tableName, contentValues,
fieldname1 + “=? AND ”+ fieldname2 + “=? ”,
new String[]{matchValue1, matchValue2});
• ContentValues data = new ContentValues();
• data.put(“id”, 470);
• numRows = mDatabase.update(Artists, data, “Name=?”, new
String[]{“Stereophonics”});Deleting Data in Tables
Deleting Data in Tables
• Using Raw SQL
• “DELETE FROM” + tableName + “ WHERE ” + fieldname1 + “=“
+ matchValue1 + “ AND ”+ fieldname2 + “= ” + matchValue2 +“;”
• Note: if any values are strings they must be surrounded with single
quotes.
• numRows = ( SQLiteDatabase.compileStatement(“DELETE FROM
Artists WHERE Id=570;”); ).excuteInsert();
• Deleting Rows using Android Helper Classes
• numRows = SQLiteDatabase.delete(tableName,
fieldname1 + “=? AND ”+ fieldname2 + “=? ”,
new String[]{matchValue1, matchValue2});
• numRows = mDatabase.delete(Artists, “Id=?”, new
String[]{Integer.toString(570)});Running Queries
Running Queries
• To get data out of a Database we run queries
• May use raw SQL:
• SQLiteDatabase.rawQuery(query, null);
• May use Android helper classes:
• SQLiteDatabase.query(………..);
• Simple queries can be handled by the Android helper classes but more
complex queries will need raw SQL
• Queries return instances of the Cursor class
• A failed Query will return null, so Always check to see if a Cursor is null
before working with it
• Returned Cursors always have an index that points to the end of the
cursor, so you must move it to the start of the cursor: cursor.moveToFirst()Running Queries
Running Queries
- Cursors
- Cursors
• A Cursor is a List of Lists of Objects
• After Cursors are created, they must be manually closed to prevent
memory leaks
• cursor.close();
• When Cursors are returned, their index is pointing past the end of the list,
so they must have their index moved to the beginning of the list
• cursor.moveToFirst();
• Get the number of records in a cursor using: cursor.getCount();
• To read the contents of a cursor, need to iterate through and read
elements according to their Type and position in the inner list:
• do while (!cursor.isAfterLast()){…… cursor.getTYPE(position);
…… cursor.moveToNext(); }
• To access a returned field value, you should know what position in
the inner list it is at, and what type it is. However:
• You can get the position of column
cursor.getColumnIndex(columnName)
• You can get the Type of the value using cursor.getType(position)Running Queries
Running Queries
- Convenience Methods
- Convenience Methods
• The SQLiteDatabase class provides a number of query convenience
classes. The most generalized is shown here:
• cursor = sqliteDatabase.query(boolean distinctFlag, String tableName,
String[] columnNames, String matchCriteria,
String[] matchArguments, String groupBy,
String having, String orderBy, String limit);
• distinctFlag – each row returned is unique
• columnNames – array of the fields to return (null will return all)
• matchCriteria – the match clause, formatted as SQL WHERE (without the
WHERE statement). Can include ‘?’ ‘s which will be replaced by
values from matchArguments (in order). Null will return all rows.
• matchArguments – Values to replace any ‘?’ ‘s in the matchCriteria
• groupBy – indicates how to group the rows, formatted as an SQL GROUP
(without the GROUP statement). Null results in no grouping.
• having – indicated which row groups (from groupBy) to return. Formatted
as an SQL HAVING (without the HAVING statement) –used to
implement aggregate where functions). Null returns all groups.
• orderBy – indicates how to order the returned rows. Formatted as an SQL
ORDER BY (without the ORDER BY statement). Null= no ordering.
• limit – specifies the maximum number of records to return. Null = no limit.Running Queries
Running Queries
- Convenience Methods Example
- Convenience Methods Example
Here is an Example:
• cursor = mDatabase.query(true, “Artists”,
new String[] {“Name”, “Genre”} “Active=?”,
new String[] {“1”}, “Genre”, Null, “Name DESC”, “10”);
• This returns a cursor containing up to 10, Distinct records containing
the Name and Genre values from the Artists table, grouped by Genre
and ordered by the Name in descending order within those groups.
• Note we used 1 instead of True – as Android SQLite doesn’t map
Booleans automatically (would actually define a constant True=1)
• As you can see, it a bit difficult to understand
• It gets rapidly worse if you want to have more complex criteria
• Worse still, you cant check the SQL statement that it results in and runs,
you can only check the result – so makes it even more difficult to debug
• Recommendations:
• Use Raw SQL for anything but simple queries
• Create your own helper/convenience methods, based around Raw SQL
and custom SQL BuildersRunning Queries
Running Queries
- Raw SQL Example
- Raw SQL Example
Here is the same Example in SQL:
• cursor = mDatabase.rawquery(“ SELECT Name, Genre FROM Artists
WHERE Active=1
GROUP BY Genre
ORDER BY Name DESC
LIMIT=10” );
• Much easier to follow!
Here is a more complex, cross table Example (not possible using conveniences):
• cursor = mDatabase.rawquery(“
SELECT Artists.Name, Albums.Name, COUNT(Albums.Id) AS Num
FROM (Artists INNER JOIN Albums ON Albums.ArtistId=Artists.Id)
WHERE Active=1
GROUP BY Genre
ORDER BY (Artist.Name DESC, Album.Name ASC)” );
• This returns a cursor containing records containing the Artists Name, Albums
Name and Number of Albums from the Artists table and Albums table,
grouped by Genre and ordered by the Artists Name in descending order then
the Album name in Ascending order within those groups.Using Prepared Statements
Using Prepared Statements
Prepared Statements define a reusable SQL statement with arguments
where parameters may be defined separately and bound (inserted)
separately.
• Android’s SQLite API provides limited support for Prepared (or
Parameterized) Statements
• No support for Queries returning Cursors
• Need to ‘pre-cast’ parameters
• Does not support Stored Procedures (neither does standard SQLite)
• Advantages:
• Easy to dynamically create and execute statements via loops
• More performant for multiple dynamically created statements
• Provides security against SQL injection attacks (not relevant unless
you are obtaining SQL across an internet connection)
• Disadvanatges:
• Not as easy to undertand
• Cant see the actual SQL for debuggingUsing Prepared Statements
Using Prepared Statements
• Uses the SQLiteStatement class, where the Argument placeholders
are used instead of values
• The Argument placeholder is ‘?’
• The Values holder is an array of objects
• Values are bound via the SQLiteStatement.bindTYPE group of
methods
• If all the values are strings may use the one time
SQLiteStatement.bindAllArgsAsStrings method
• If not, must use the appropriate method for each object TYPE, with
an index identifying which placeholder the value will be bound to
• bindLong(int index, long value)
• bindDouble(int index, double value)
• bindString(int index, String value)
• bindBlob(int index, byte[] value)
• bindNull(int index)
• Can store the values in Object[] and then test the type using
instanceof and then use the appropriate methodUsing Prepared Statements
Using Prepared Statements
• Uses the standard SQLStatement execute methods:
• excecute(), excuteInsert(), executeUpdateDelete(),
simpleQueryForLong(), simpleQueryForString(),
simpleQueryForBlob()
Here is an Example:
int[] ids = new int[] {21,22,23};
String[] artistNames = new String[] {“Travis”, “Blue”, “Embrace”};
SQliteDatabase mDatabase = …….;
SQLiteStatment command = mDatabase.compileStatetment(“INSERT INTO
ArtistsTable (ID, Name) VALUES (? , ?)”;
int i =1;
for (int id : ids) {
command.bindLong(1, id);
command.bindString(2, artistNames[i]);
command.exexute();
}Using Prepared Statements
Using Prepared Statements
Here is a generalized Example:
public static void executePreparedSqlStatement(SQLiteDatabase mDatabase,
String dynamicStatement, ArrayList<Object[]> parameters) {
SQLiteStatement command = mDatabase.compileStatement(dynamicStatement);
for (Object[] values : parameters)
{
command = preparStatement(command, values);
int i = 1;
for (Object value : values ) {
if (value == null) { command.bindNull(i); }
else if ((value instanceof Integer) || (value instanceof Short) || (value instanceof
Long)) { command.bindLong(i, (Long)value); }
else if (value instanceof Boolean) {
if ((Boolean)value){ command.bindLong(i, DB_TRUE);
} else { command.bindLong(i, DB_FALSE); } }
else if ( value instanceof String) { command.bindString(i, (String)value); }
else if ( (value instanceof Float) || (value instanceof Double) ) {
command.bindDouble(i, (Double)value); }
else if ( value instanceof byte[]) { command.bindBlob(i, (byte[])value); }
i++;
}
command.execute();
}
}Transactions
Transactions
Sometimes you want to ensure that a series of operations on a database
happens as a group, and either they all succeed or none of them occur (an
all or none scenario). For example, when inserting related data in multiple
tables. This is achieved via Transactions.
• The steps are:
• Start Transaction
• Do stuff on Database(s)
• Mark Transaction as Successful or not
• End (commit) Transaction
• Should be wrapped in a try-catch-finally construct
• If a Transaction is marked as Successful then all the SQL Actions
between the start and end (commit) are committed when the
transaction is ended, else they are discarded.
• Transactions can be implemented via convenience methods provided
by Android or via Raw SQL
• SQLite does not directly support named Transactions.
• Avoid nested transactions – does not scale well & can cause deadlocksTransactions
Transactions
- Android Convenience Methods
- Android Convenience Methods
Transactions are one of the areas of SQLite usage where the convenience
methods provided by Android really are much easier to use than Raw SQL.
• SQLiteDatabase.beginTransaction();
• Begins a transaction in Exclusive mode
• Transactions can also be started in Immediate Mode & with a Listener
• SQLiteDatabase.setTransaction();
• SQLiteDatabase.endTransactionSuccessful();
• The process takes the form:
mDatabase.startTransaction();
try{
…. Perform SQL Commands ….
mDatabase.setTransactionSuccessfull();
} catch {
…. Perform Actions on failure such as notifcation ….
}
} finally {
mDatabase.endTransaction(); }Transactions
Transactions
- Raw SQL
- Raw SQL
Using Raw SQL to implement transactions gives you much more freedom
and flexibility but much more complexity, as you will probably have to
compile SQL statements, and handle the various modes and exceptions. It
is unlikely that you will ever need to use anything but the default
behaviour provided by the Android convenience classes.
• Uses the SQLiteDatabase.execSQL method
• Basic Commands
• SQLiteDatabase.execSQL(“BEGIN”);
• SQLiteDatabase.execSQL(“COMMIT”);
• SQLiteDatabase.execSQL(“ROLLBACK”);
• The SQL commands inside the transactions are either Committed or
Rolled back (discarded) depending on which command you invoke in
your code.
• If the database connection is closed or an error occurs then it defaults
to a Roll back.Transactions
Transactions
• In Android, by default, conflicts such as constraint violations cause
exceptions which need to be handled
• Conflicts in a SQL command within the transaction can be intercepted
and handled differently by specifying the ON CONFLICT or OR
statement in the command.
• ON CONFLICT ROLLBACK | ABORT | FAIL | IGNORE | REPLACE
• ROLLBACK – the entire transaction fails and is rolled back
• ABORT – the entire current statement is discarded but the transaction
does not roll back and continues
• FAIL – the current statement stops executing, but changes already
performed by it are retained. The transaction does not roll back and
continues
• IGNORE - the current statement skips the current row and continues
executing. The transaction does not roll back and continues
• REPLACE – Depends on the constraint violation
• Note: Android provides the insertWithOnConflict and
updateWithOnConflict convenience methods to implement thisTransactions
Transactions
• Transactions can be:
• DEFERRED (default) – No Database locks are acquired until the first
commands that accesses it. A read access will create shared
lock and a write access will create a Reserved lock. So, the
database could be accessed by a separate thread in between
the start of a transaction and the first write access.
• IMMEDIATE – Reserved locks are acquired as soon as the transaction
begins. Other threads can read from the database but not
write to it.
• EXCLUSIVE – Reserved locks are acquired as soon as the transaction
begins. Other threads can not read or write to the database.
• SQLiteDatabase.execSQL(“BEGIN EXCLUSIVE”);
• Android provides the SQLiteDatabase.beginTransactionNonExlusive()
convenience methods to begin a transaction in Immediate mode.
• Android does not provide a convenience method to begin a transaction
in Deferred mode.Transactions
Transactions
- Raw SQL
- Raw SQL
• Begin … Commit pairs can not be nested
• By default, Transactions implemented in Raw SQL can not be nested
• Nesting is implemented by using SAVEPOINT, RELEASE and
ROLLBACK TO SavePointName
• SAVEPOINT savePointName – Similar to BEGIN.
• RELEASE savePointName – Mergers named transaction into parent.
Similar to COMMIT, but can be undone by parent Rollback
• ROLLBACK TO savePointName – Rolls back all commands until the
named SavePoint is reached
• Nesting Rules:
• Last transaction started is the first to be Committed or Rolled back
• BEGIN only works if transaction stack is empty
• COMMIT commits all outstanding transactions. Empties stack
• RELEASE releases most recent transaction & keeps releasing them
back off the stack until finds on with matching SavePoint name
• ROLLBACK TO removes transactions back until matching SavePoint
• ROLLBACK removes all transactions from the stack.Triggers
Triggers
• Triggers cause SQL statements to run if certain criteria are met
• Triggers can be set on one or more tables
• Triggers can invoke SQL to be run on other tables, as long as they
are in the same database
• Each Trigger can only relate to a single type of action
• ‘Key Criteria Filters’ provided for fields, e.g. new and old
• There are no Android helper classes or convenience methods
• Example
• SQiteDatabase.execSQL(“
CREATE TRIGGER Trigger1
AFTER INSERT ON Albums
BEGIN
UPDATE Artists SET Active=1 WHERE Id = new.Id;
END;” );
• This trigger causes the Active field to be set to 1 (True) for an Artist
in the Artists table when a new record is inserted for that Artist in
the Albums tableCreate Your Own Framework
Create Your Own Framework
• Allows you to make you code much more maintainable
• It reduces bugs and coding errors as more functionality is added
• Allows new developers to add features without needing to know details
of how you have constructed the database or the SQLite framework
• If you develop multiple products, then the framework can be reused
(and improved) for those products
• By avoiding platform specific features and idioms, you can create a
framework API that is essentially the same across multiple platforms
• For example the API to query a table is called the same and has the
same parameters in our Database framework for Android, iOS and .NET
• By using SQL statements and avoiding platform specific APIs where
possible, the framework code can be similar across platforms
• For example, we have over 90% commonality in our Database
framework across Android and .NET
• For maximum reuse and maintainability, make your framework multi-
layered.SQL Builders
SQL Builders
• SQL Builders make it easy for you to construct parts or all of a SQL
Statement or Query
• They basically take inputs and create the textual SQL Statements
• Once a builder is working and tested you know it can be reused
• Typical query builder components might:
• Create Conditional Statements
• Create Query Conditional Logic
• Create Keys
• Create Constraints
• Create Triggers
• Typical builders might use these components to:
• Create Tables
• Create Queries
• Create Pragma Statements
• We have created a generic query builder framework for Android and
C#/.Net (for use with Xamarin Studio)SQLite on Android
SQLite on Android
Part 3
Part 3
SQLite Tips & Tricks
SQLite Tips & TricksPerformance Tips
Performance Tips
• There isn’t ‘one solution fits all’
• The default configuration suits most situations.
• However there are some practices you should always follow and
some that you should investigate at if you notice performance issues.
• You should always:
• Run database access and operations in a separate thread
• Utilize Transactions for multiple / dynamic statements
• Utilize Prepared statements for repeated operations
• Avoid Synchronous operations
• Use Indexing
• Try different additional strategies if you still have performance issues
• Consider modifying schema
• Consider adjusting database configuration via PragmasPerformance Tips
Performance Tips
Run Database in Separate Threads
Run Database in Separate Threads
• Database calls and functions can take significant time to complete
• You cant guarantee that the tables you are manipulating are being
stored in cached memory
• More likely, you will be accessing the data via the File System,
which is ‘slow’
• If an operation on the main thread (GUI thread) takes more than
25ms, it may be noticeable to the user
• This causes ‘janking’ – the interface becomes unresponsive and
’clunky’
• Users experience is severely degraded
• Simple queries may be fast enough, larger & complex queries, and
writes are likely to take too long
• Run Database access and operations on a separate thread
• Use background threads, threadpools, background services or even
service providers
• Pass the data back to Activates and update Views as necessary
• Use Callbacks, Event handlers, Shared variablesPerformance Tips
Performance Tips
Transactions
Transactions
• By default SQLite wraps every executed SQL statement in a
transaction, which caries an overhead
• This can massively reduce performance if using multiple or dynamic
commands and queries
• Avoid by managing your own transaction around groups of commands
and queries. Can mark as successful, even on partial failure, if you
don’t want changes rolled back.
• OR use Pragmas to turn off transaction journal if you don’t need
transactionsPerformance Tips
Performance Tips
Prepared Statements
Prepared Statements
• If you have multiple statements to execute that have the same
format, but some values change, use prepared statements
• SQLite parses each statement before executing. This takes time and
can massively reduce performance if you are executing the same
statement with multiple values
• A prepared or parameterized statement only gets parsed once,
irrespective of the number of times it is called (as long as the object
exists in context)
• Use with Transactions to optimize performance
• Note: Android’s SQLite implementation does not fully support
prepared statements:
• No Stored Procedures
• No support for Queries that return Cursors (simple queries
that return a single value are supported)
• ‘?’ used for value placeholders in prepared statement and Object[]
used to hold values which are bound to ‘prepared’ statementPerformance Tips
Performance Tips
Transactions & Prepared Statements
Transactions & Prepared Statements
Example:
int[] ids = new int[] {21,22,23,…………};
String[] artistNames = new String[] {“Travis”, “Blue”, “Embrace”, …………….};
SQliteDatabase mDatabase = …….;
mDatabase.beginTransaction();
try {
SQLiteStatment command = mDatabase.compileStatetment(“INSERT INTO
ArtistsTable (ID, Name) VALUES (? , ?)”;
int i =1;
for (int id : ids) {
command.bindLong(1, id);
command.bindString(2, artistNames[i]);
command.exexute();
}
mDatabase.setTransactionSuccessful();
} catch (Exception ex) {
}
mDatabase.endTransaction();Performance Tips
Performance Tips
• Use multiple databases to
• Avoid locking issues
• Allow multi-threaded access
• But be careful that this doesn’t over complicate things and be
aware that SQLite doesn’t support triggers across databases
• Avoid multiple open and close statements on a database
• Consider using a Database Connection Manager
• Avoid Synchronous database access & Synchronous access methods
• Use transactions for related groups and sets of data manipulation
• Use Indexes when creating tables (PRIMARY and UNIQUE) or by
CREATE INDEX
• Use SQL statements to filter data rather then code
• Consider splitting up very large tables to improve search speeds
• Store Compressed data to save memoryPerformance Tips
Performance Tips
• Use LIMIT=1 when checking for something existing
• For large, complex nested queries, break up and use temporary
tables to store results
• Compact the database - Use VACUUM
• Especially after deleting a lot of data
• Manual and auto (or on demand based on pagecount and freelist
count)
• Manual: mDatabase.execSQL(“VACUUM”);
• Do in thread, may take time do after big delete
• Avoid GLOB and LIKE where possible.
• Avoid comparing LENGTH on long Strings
• Use IN instead of OR when comparing a single field to multiple values
• IN can use an index, OR can’t
• Consider using IMMEDIATE mode for transactionsPerformance Tips
Performance Tips
Database Settings Using Pragmas
Database Settings Using Pragmas
• Tweak Pragma settings on each connection
• Try setting:
• temp_store = MEMORY
• page_size = 4096 (default is 1024)
• cache_size = 5000 (default is 2000)
• journal_mode = WAL (Android 3.0 and higher)
OFF (if not using Transactions)
• locking_mode = EXCLUSIVE;
• synchronous = OFF (or NORMAL)
• count_changes = OFF
• read_uncommited = TRUE (can allow you to increase scope of
transactions)
• Some of these settings have interdependences, either in operation or
in the order they may be appliedReminder: Pragma Instructions
Reminder: Pragma Instructions
• Pragma Instructions set properties on the database and the database
connection
• They are applied using the SQLiteDatabase.execSQL(command)
method:
• SQLiteDatabase.execSQL("PRAGMA " + instruction + " ;");
• Selection of Pragma instructions:
• user_version = integer
• foreign_keys = On|Off
• auto_vacuum = None|Full|Incremental
• incremental_vacuum
• cache_size = integer
• page_size = integer
• case_sensitive_like = On|Off
• journal_mode = Delete|Truncate|Persist|Memory|Wal|Off
• locking_mode = Normal|Exclusive
• synchonous = Off|Normal|Full
• temp_store = Default|File|MemoryTip:
Tip:
Updating the Database Schema
Updating the Database Schema
Managing database schema updates is potentially onerous.
• There are n(n+1)/2 possible update scenarios for version n of the
application and the same number of downgrade scenarios)
• You must manage and test each!
• Here is a strategy that reduces that to n-1 scripts and automatically
manages which to runTip:
Tip:
Updating the Database Schema
Updating the Database Schema
Creating the scripts:
For each Database in your application
• Create a script upgrading the database from version n to n+1
• Create a script downgrading the database from version m to m-1
• For Example:
BEGIN TRANSACTION;
CREATE TEMPORARY TABLE t1_backup(a,b,c);
INSERT INTO t1_backup SELECT a,b,c FROM t1;
DROP TABLE t1;
CREATE TABLE t1(d,a,c);
INSERT INTO t1 SELECT a,c FROM t1_backup;
DROP TABLE t1_backup;
COMMIT;Updating Database Schema
Updating Database Schema
• Create a dummy class that contains two dummy public methods –
upgrade and downgrade.
• We will override these methods for each version, calling the
associated scripts from them and store each instance in an array.
DB_Patch.java
package com.qms.android.database;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
public class DB_Patch {
public void upgrade(Context ctx, SQLiteDatabase database){
}
public void downgrade(SQLiteDatabase database){
}
} Updating Database Schema
Updating Database Schema
• Create an instance of the dummy Class for each version, overriding the
methods to perform the upgrades and downgrades
• For each Database in the application, create a static private property
(variable) which will be an array of the Dummy Class
• The number of entries will be the number of versions of the database
• Set the contents of the array by assigning the instances of the dummy
class that was created for each version, in version order.
• In Java, this can be done as part of the static property definition, using
anonymous classes/methods (C# & Objective C require separate
instances)
• These contain the upgrade and downgrade scripts in the
associated upgrade and downgrade anonymous methods.
• Create a private method to manage and perform the
upgrade/downgrade by invoking the anonymous methods stored in the
array – making it private hides the anonymous methods an scripts.
• Create a public method to call the private methodUpdating Database Schema
Updating Database Schema
Example Static property declaration
private static final DB_Patch[] APP_DB_PATCHES = new DB_Patch[]{
new DB_Patch() { // VERSION 0 to 1
public void upgrade (Context ctx, SQLiteDatabase mDatabase){
// upgrade scripts for App_Database ver 0 to
createDatabaseMethod(mDatabase);
mDatabase.setVersion(1);
}
// VERSION 1 to 0 - Note: Should never be run
public void downgrade (SQLiteDatabase database){ }
}
// VERSIONS 1 <> 2
new DB_Patch() { // VERSION 1 to 2
public void upgrade (Context ctx, SQLiteDatabase mDatabase){
// upgrade scripts for App_Database ver 1 to 2
mDatabase.execSQL(upgradeScript1_2);
mDatabase.setVersion(2);
}
// VERSION 2 to 1 -
public void downgrade (SQLiteDatabase database){
// downgrade scripts for App_Database ver 2 to 1
mDatabase.execSQL(downgradeScript2_1);
mDatabase.setVersion(1);
}
}
// VERSIONS 2 <> 3
new DB_Patch() { // VERSION 2 to 3
public void upgrade (Context ctx, SQLiteDatabase mDatabase){
// upgrade scripts for App_Database ver 2 to 3
mDatabase.execSQL(upgradeScript2_3);
mDatabase.setVersion(3);
}
// VERSION 3 to 2 -
public void downgrade (SQLiteDatabase database){
// downgrade scripts for App_Database ver 2 to 1
mDatabase.execSQL(downgradeScript3_2);
mDatabase.setVersion(2);
}
}
};Updating Database Schema
Updating Database Schema
Private Method to perform Upgrade / Downgrade
private static boolean applyPatches(Context ctx, SQLiteDatabase mDatabase, int oldVer, int newVer, DB_Patch[] dbPatches){
boolean result = false;
if ((oldVer>= 0) && (newVer >= 0) && (oldVer <= dbPatches.length) && (newVer <= dbPatches.length)){
if (newVer > oldVer){ // It's an upgrade, so run each upgrade script in order
try {
for (int i = oldVer; i < newVer; i++){
dbPatches[i].upgrade(ctx, mDatabase);
}
result = true;
} catch (Exception e){
}
}
if (newVer < oldVer){ // It's an downgrade, so run each downgrade script in order
try {
for (int i = oldVer; i > newVer; i--){
dbPatches[i-1].downgrade(mDatabase);
}
result = true;
} catch (Exception e){
}
}
}
return result;
}
Public Method to be called to invoke databse upgrade / downgrade
public static boolean appDatabaseApplyPatches(Context ctx, SQLiteDatabase mDatabase, int oldVer, int newVer){
return applyPatches(ctx, mDatabase, oldVer, newVer, VALENS_DB_PATCHES);
}Questions
Questions
Dropbox:
https://www.dropbox.com/sh/r8j884d8jggwomj/dXUg
TvbvyM
David Allen
Quantum Mobile Solutions
dallen@qmobilesolutions.com