iAndroid: Using SQLite on Android Application

iAndroid: Using SQLite on Android Application
Reference Source : Here
With a little care, SQLite can be used as a data store or full database on Android devices
As with most platforms, Android gives us a few options to store data so that it persists even after we’ve terminated the application. Of the various ways we can do this, text files — either stored in the application’s own files directory or to the phone’s SD card — represent one approach. Preferences are also frequently used to store data because they can be both hidden from the user and persist as long as the application is installed. And while not strictly speaking in the same category, Assets can be useful for storing read-only data, too. Assets are essentially files that you bundle into the application package prior to compilation in the project assets folder, which can be accessed at runtime. I will take a closer look at these later.Sometimes, however, we need to be able to carry out complex operations on persistent data, or the volume of data requires a more efficient method of management than a flat text file or preference entry will allow. This is where a mobile database comes in.

Android comes with SQLite (version 3.5.9+), which has been available on the platform since release 1.5 of the OS (the “Cupcake” release). For readers unfamiliar with SQLite, it is a self-contained, server-less, transactional SQL database. While it has its limitations, SQLite serves as a powerful tool in the Android developer’s arsenal.

What I’ll principally cover here is one way to use a SQLite database in Android, concentrating on its management; specifically, creation and update, rather than all the runtime operations.

Managing SQLite

To begin with, we can manage SQLite using a class that extends SQLiteOpenHelper, which comes with a constructor and two required methods; onCreate and onUpgrade.

Naturally, the first of these is executed when the constructor is instantiated; it is here that, via the superclass, we provide four important pieces of data:

  • Context. This is the context of the application. It can be useful to set this in the constructor and store it locally for later use in other methods.
  • Database name. This is the filename (as a String) of the physical database file being accessed.
  • Cursor factory. The cursor factory in use, if any.
  • Database Version. This is the version of your database (as an integer), which I’ll discuss in more detail later. Your initial version should be 1.

For our example, we put these four pieces together and get the following:

1
2
3
4
5
6
7
8
9
10
11
class DB extends SQLiteOpenHelper {
    final static int DB_VERSION = 1;
final static String DB_NAME = "mydb.s3db";
    Context context;
    
    public DB(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
        // Store the context for later use
        this.context = context;
    }

The constructor does two things. First, it checks whether the database exists and, if not, will call the onCreate method. Second, if the database does exist, it will check whether the existing database version number differs from the one implemented in the constructor, so as to determine if the database has been updated. If it has, the onUpgrade method will be called.

Additionally, as we now know that the onCreate method is called only when the database does not exist, it can be used as a handy way to determine if you’re dealing with a first run of the application following installation. As such, you can use this method to call any other methods that you need executed only on the first run, such as EULA dialogs.

Let’s look at the database itself. For the purposes of this article, I’m just going to use a very simple employee database with a SQL creation script as follows:

1
2
3
4
5
6
7
CREATE TABLE employees (
 _id INTEGER PRIMARY KEY AUTOINCREMENT,
 name TEXT NOT NULL,
 ext TEXT NOT NULL,
 mob TEXT NOT NULL,
 age INTEGER NOT NULL DEFAULT '0'
);

We can easily construct this by hard-coding and executing the creation SQL, line by line, in our code as follows:

1
2
3
4
5
6
@Override
public void onCreate(SQLiteDatabase database) {
    database.execSQL(
        "CREATE TABLE employees ( _id INTEGER PRIMARY KEY "
    + "AUTOINCREMENT, name TEXT NOT NULL, ext TEXT NOT NULL, "
    + "mob TEXT NOT NULL, age INTEGER NOT NULL DEFAULT '0')");
}

As you can see, this can get quite unwieldy once the database reaches a certain size and complexity, so the ideal solution would be to bundle a SQL creation script as an asset file. To use this approach, you need to write a method that takes in a SQL script from the assets directory and parses it, executing it line by line:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
@Override
public void onCreate(SQLiteDatabase database) {
executeSQLScript(database, "create.sql");
}
private void executeSQLScript(SQLiteDatabase database, 
                              String dbname) {
ByteArrayOutputStream outputStream = new  
                              ByteArrayOutputStream();
    byte buf[] = new byte[1024];
    int len;
        AssetManager assetManager = context.getAssets();
    InputStream inputStream = null;
        
    try{
        inputStream = assetManager.open(dbname);
        while ((len = inputStream.read(buf)) != -1) {
            outputStream.write(buf, 0, len);
        }
        outputStream.close();
        inputStream.close();
            
        String[] createScript = 
                   outputStream.toString().split(";");
        for (int i = 0; i < createScript.length; i++) {
                String sqlStatement = createScript[i].trim();
            // TODO You may want to parse out comments here
            if (sqlStatement.length() > 0) {
                    database.execSQL(sqlStatement + ";");
                }
        }
    } catch (IOException e){
        // TODO Handle Script Failed to Load
    } catch (SQLException e) {
        // TODO Handle Script Failed to Execute
    }
}

While this is a more complex approach than simply executing each SQL statement for very simple databases, it quickly pays dividends once the database becomes more complex or you need to pre-populate it. You’ll also see that I abstracted the creation into a separate method called executeSQLScript so that it can be reused in other situations.

Interacting with the Database

Now that the database is created, I want to be able to interact with it. Here’s a brief rundown:

The first step is to open the database and there are two ways to do this: usinggetReadableDatabase() and getWritableDatabase(). The former is faster and uses fewer resources. It should be used for anything that does not require writing or changing the database. The latter call is better suited to INSERTs, UPDATEs, and the like.

In Android, recordsets are returned from queries as Cursor objects. To carry out a query, use either the query() or rawQuery() method. For example, the following two calls return exactly the same data:

1
2
3
4
DB db = new DB(this);
SQLiteDatabase qdb = db.getReadableDatabase();
Cursor recordset1 = 
     qdb.query("mytable", null, null, null, null, null, null);
Cursor recordset2 = qdb.rawQuery("SELECT * FROM mytable", null);

The first call uses a bewildering number of parameters. They are the table name, a string array of the column names, the WHERE clause, the selection arguments, the GROUP BY clause, the HAVING clause, and the ORDER BY clause, respectively. You’ll note that setting many of these as null has the effect of their being treated as wildcards or omitted altogether.

Most of these parameters are fairly straightforward to anyone familiar with SQL. The selection arguments, however, require a little more explanation because they form a string array that interacts with the WHERE parameter, systematically replacing any “?” characters in the clause with a value from the array.

With the rawQuery() approach, there are only two parameters: the first is the SQL query, and the second is the selection argument — akin to those used in the query method. Selection arguments may be preferable to use with complex queries, such as those that use JOINs.

Similarly, INSERTUPDATEDELETE, and a range of other common operations are handled with methods similar to query(); or, as with rawQuery(), they can be executed as raw SQL code using execSQL().

Database Upgrades

Returning to management of the database, let’s look at the tricky scenario of database upgrades. Over time, an app will likely change. New functionality may be added, or it may be better optimized. These changes, in turn, may lead to a requirement to upgrade the database schema and change the value for DB_VERSION in the updated application code to reflect this.

One potential problem is that replacing our database with a new version will end up invalidating the previous version and lead to the loss of data that was present in existing user installations. A second problem is that, once our application has reached a point whereby more than two versions have been released, we cannot presume that the user has been diligently upgrading all along, so a simple upgrade from one version to the next may no longer work.

To deal with this, we already know that if we introduce a new database version, theonUpgrade() method will be called. So ideally, we can use our SQL script parser method and execute one or more update scripts.

Let’s look at what we intend to change in Version 2 of our database in the example:

  • Normalize the phone number data (extension, mobile) into a separate “numbers” table, which includes a numeric field to denote the type of phone number.
  • Add a salary field to the employee table.

Using the Version 1 schema as a starting point, this can be handled by writing a SQL script that updates the schema and then populates it with the data from the older version:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE TABLE numbers (
   _id INTEGER PRIMARY KEY AUTOINCREMENT,
   employid INTEGER NOT NULL,
   number TEXT NOT NULL,
   ntype INTEGER NOT NULL DEFAULT '0'
);
CREATE INDEX employid ON numbers(employid);
INSERT INTO numbers (employid, number, ntype) SELECT _id, ext, 0
    FROM employees;
INSERT INTO numbers (employid, number, ntype) SELECT _id, mob, 1
    FROM employees;
CREATE TABLE temp (
     _id INTEGER PRIMARY KEY AUTOINCREMENT,
     name TEXT NOT NULL,
     salary INTEGER NOT NULL DEFAULT '0'
);
INSERT INTO temp (_id, name) SELECT _id, name FROM employees;
DROP TABLE employees;
ALTER TABLE temp RENAME TO employees;

Naturally, the more complex the changes in your database schema, the more complex the script you’ll need to write to handle this. SQLite has more limited support for SQL than many databases, so sometimes you’ll need to devise workarounds for these limitations. For example, in the aforementioned update script, I had to employ a temporary table as a workaround for the lack of DROP COLUMN functionality.

Now that I have the SQL upgrade script, I need to handle how it is executed when theonUpgrade method is called. One approach is to do the following:

1
2
3
4
5
6
7
8
9
10
11
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {     
if (newVersion > oldVersion) {
    switch (oldVersion) {
        case 1:
            executeSQLScript(database, "update_v2.sql");
        case 2:
            executeSQLScript(database, "update_v3.sql");
    }
}
}

There are two things to note in this code. The first is that I check to see whether the new database version is greater than the old one. I wrote the code this way because theonUpgrade() method will be called any time these two versions are different, leading to a situation in which version downgrades are also possible. Our code does not envisage this ever occurring, but an else clause and accompanying code could be added to handle this.

The second thing is that there are no break statements between the cases in our switch. This is because each script simply updates from one version to the next, meaning that an upgrade from Version 1 to 3 will first run the upgrade script from Version 1 to 2, then continue on to run the upgrade script from Version 2 to 3. If the database is already at Version 2, it will simply skip the first script and only run the upgrade script from Version 2 to 3.

Thus, each time you upgrade the database, you will only need to replace the create script with one that reflects the new schema (for new installs), and an update script (that handles only an update from the previous version) to handle all possible upgrades. Meanwhile, in our Java code, we need to update the value for DB_VERSION and, naturally, any operations that may be affected by the new database schema.

Conclusion

SQLite can be a very useful means to store and manage data in a persistent manner on Android devices. However, as with any database, care needs to be taken to administer it correctly, particularly with regard to version changes.

Using the script parser solution and saving this part of the application logic as a series of SQL scripts is an efficient and simple management technique to avoid having to write complex methods to the core application to handle each upgrade, thus allowing you to concentrate on the application’s business logic instead.

Thanks 🙂 Keep Coding 🙂

SolutionPointLogo

Advertisements

iConcepts : Mobile Emulators & Simulators: The Ultimate Guide

Mobile Emulators & Simulators: The Ultimate Guide

Image

Reference Source  : See Here

I’ve mixed all this information to create this ultimate guide to 37 download resources for hundreds of emulators and simulators. Enjoy!

This list includes content from Programming the Mobile Web book with frequent updated information & links.

List of mobile and tablet emulators for mobile web design & development testing
Name Official Platform Type Browser testing Native testing Compatibility

IOS SIMULATOR

Official iOS Simulator Safari only Objective-C MacOS
Download3.7Gb (login required) Devices: iPhone 3GS, iPod Touch, iPhone 4, iPad (Tablet)
Comes with XCode and Native SDK. You can’t emulate Accelerometer/Gyroscope(DeviceMotion API). You can’t emulate URI-schemes, such as click-to-call. As a Simulator, it doesn’t provide an AppStore; you can’t install other browsers for testing, such as Opera Mini or Skyfire.

ANDROID EMULATOR

Official Android Emulator Android Browser – others Java WindowsLinux
Download20Mb and 60Mb per platform package Devices: Generic devices using 1.1, 1.5, 1.6, 2.0, 2.1, 2.2, 2.3, 3.0 O.S. platform
We need to download images of the platforms after downloading the SDK. Look atChapter 4 of the book for details. After downloading the platform, you can installFirefoxOpera MiniOpera MobileSkyfire and UCWEB in your Android emulator for testing. You can download Motorola, Samsung and Nook add-ons (see below). Now it includes tablet support in HoneyComb (3.0)

HP WEBOS EMULATOR

Official webOS Virtual Machine webOS Browser JavaScript – C++ WindowsLinux
Download260Mb Devices: Palm Pre, Palm Pixi, Palm Pixi Plus
Comes with SDK

SAMSUNG GALAXY TAB ADD-ON

Official Android Add-on Android Browser – others Java WindowsLinux
Download52Mb Devices: Samsung Galaxy Tab (Tablet)
Requires Android SDK with 2.2 package. The download is done using the Android 2.3 SDK searching for third-party packages.

MOTOROLA XOOM ADD-ON

Official Android Add-on Android Browser – others Java WindowsLinux
Download2Mb Devices: Motorola Xoom (Tablet)
Requires Android SDK with 3.0 package. It’s only a skin for the emulator.

NOKIA SYMBIAN EMULATORS

Official Symbian Emulator S60 Browser – others Java ME / WRT webapps / C++ Windows
Download500-750Mb each (requires login) Devices: All Symbian devicesincluding: 3rd edition FP2 (such as Nokia N96), 5th edition (such as Nokia 5800 XpressMusic), Symbian^3 (such as N8) and an specific N97 emulator
Large download and installation. You need to check previous requirements to be installed first. You can install Opera MiniOpera MobileBolt Browser andUCWEB on Symbian emulators.

NOKIA SERIES 40 EMULATORS

Official Nokia OS Emulator Nokia Browser – others Java ME / Flash Lite Windows
Download30-100Mb each(requires login) Devices: All devices including: 2nd edition, 3rd edition, 3rd edition FP1, 3rd edition FP2, 5th edition, 5th edition FP1, 5th edition FP1 Lite, 6th edition, 6th edition FP1
You can install Opera MiniUCWEBBolt Browser and OVI Browser Betaon Series 40 emulators.

MOTOROLA ADD-ONS SDK FOR ANDROID

Official Android Add-on Android Browser – others WindowsLinux
Download30-60Mb each device Devices: Motorola: Atrix, Bravo, Cliq 2, Dext, Milestone, BackFlip, Devour, Qrty, i1, Droid2, Droid X, Flipout, Defy, Flipside, Cliq, Citrus, others
Requires Android Emulator. There are 20 different downloads.

BLACKBERRY SIMULATORS

Official RIM OS Emulator RIM Browser – others Java Windows
Download50-170Mb eachRequires login Devices: 35 different models including series Bold, Curve, Pearl, Storm, Storm2, Style, Torch & Tour. Every model has different emulators combining OS version and operator firmware.
For web browsing testing you need to install and open a proxy service. SeeChapter 4 for a full compatibility table and installation guide. You can installOpera Mini and Bolt Browser on BlackBerry emulators.

BLACKBERRY PLAYBOOK SIMULATOR

Official Tablet OS Virtual Machine Internal Browser Adobe AIR MacWindows
Download105MbRequires login Devices: BlackBerry PlayBook (tablet)
HTML5 browser support. Adobe AIR & Packaged HTML5 WebWorkers app support.

WINDOWS PHONE 7 SIMULATOR

Official Windows Phone Emulator Internet Explorer Silverlight Windows
Download (Visual Studio Express included)500Mb Devices: Generic Windows Phone 7
Comes with Visual Studio Express (free version) or as an add-in for commercial versions

SONY ERICSSON SDK FOR ANDROID

Official Android Add-on Android Browser – others MacWindowsLinux
Download50Mb Devices: Sony Ericsson Xperia X10
Requires Android Emulator.

NOOK SDK FOR ANDROID

Official Android Add-on Android Browser – others MacWindowsLinux
Download130MbRequires Login Devices: Barnes & Noble Nook Color
Requires Android Emulator.

OPERA MOBILE EMULATOR

Official Symbian / Android / Windows Simulator Opera Mobile No native MacWindowsLinux
Download15-25Mb Devices: Multiple devices & OS emulation support
You can also use emulators (Symbian, Android) and install the real Opera Mobile inside.

OPERA MINI SIMULATOR

Official Many Online emulator Opera Mini No native MacWindowsLinux
Use OnlineUse online v.4 Devices: Generic Java device – No Opera Mini for iOS emulation
You can also use emulators (Symbian, Android, Java, BlackBerry) and install the real Opera Mini inside.

FIREFOX FOR MOBILE SIMULATOR

Official Generic Engine Simulator Firefox Mobile No native MacWindowsLinux
Download20Mb Devices: Multiple devices – beta version.
You can also use emulators (Android) and install the real Firefox Mobile inside. The desktop simulator is very basic and it’s in beta state.

QT SIMULATOR

Official Symbian & Maemo Simulator No Browser support Qt MacWindowsLinux
Download500-900Mb Devices: Maemo (such as Nokia N900), Symbian Touch (such as Nokia N8) and Symbian non-touch (such as Nokie E71)
It does not support web browsing testing. You can test and create QtWebKit apps (hybrids) with HTML5 and CSS3.

NOKIA WEB SDK SIMULATOR

Official Symbian & Maemo Simulator Webapp testing WRT and webapps MacWindowsLinux
Download200MbRequires Login Devices: Maemo (such as Nokia N900), Symbian Touch (such as Nokia N8) and Symbian non-touch (such as Nokie E71)
It does not support direct web browsing testing. You can test and create webapps & widgets.

PHONEGAP SIMULATOR

3rd-party Generic Simulator WebKit-based PhoneGap hybrid testing MacWindowsLinux
Download15Mb Devices: Apple iPhone, Generic Android, and others
The rendering engine is a simulation. You can use it for hybrid PhoneGap testing or for simple WebKit-based browser simulation.

ADOBE DEVICE CENTRAL CS5

3rd party Several Simulator Generic browser support Flash MacWindows
Included with Creative Suite (commercial) Devices: Device Library with more than 100 devices.
The web browsing support up to CS5 version is very basic (just a sized webkit browser for every device) and non-accurate.

BADA SIMULATOR

Official Bada OS Simulator No Browser Support Bada C Windows
Download200/400MbRequires Login Devices: Maemo (such as Nokia N900), Symbian Touch (such as Nokia N8) and Symbian non-touch (such as Nokie E71)
It does not support direct web browsing testing. You can test and create webapps & widgets.

GARNET EMULATOR

Official Garnet OS (formerly Palm OS) Emulator NetFront Garnet MacWindowsLinux
Download200/400Mb Devices: Old Palm OS devices

WINDOWS MOBILE 6 EMULATOR

Official Windows Mobile Emulator Internet Explorer .NET / C++ Windows
Download600Mb Devices: Generic Windows Mobile Devices – Standard and Professional versions
* This emulator requires special attention for using the browser. Look at chapter 4 ofProgramming the Mobile Web for detailed instructions on how to use it. You can install Opera Mobile and Opera Mini inside the emulator.

WINDOWS MOBILE 6.5 EMULATOR IMAGES

Official Windows Mobile Add-on Internet Explorer .NET / C++ Windows
Download250Mb Devices: Generic Windows Mobile Devices – Standard and Professional versions
Requires Windows Mobile 6 SDK

WINDOWS MOBILE 6.1 EMULATOR IMAGES

Official Windows Mobile Add-on Internet Explorer .NET / C++ Windows
Download400Mb Devices: Generic Windows Mobile Devices – Standard and Professional versions
Requires Windows Mobile 6 SDK

DEVICE EMULATOR WITH WINDOWS MOBILE 5

Official Windows Mobile Emulator Internet Explorer .NET / C++ Windows
Download60Mb Devices: Generic Windows Mobile Devices – Standard and Professional versions
* This emulator requires special attention for using the browser. Look at chapter 4 ofProgramming the Mobile Web for detailed instructions on how to use it.

I-MODE HTML SIMULATOR

Official Native Simulator i-Mode Browser (Japan) No native Windows
Download38Mb Devices: Generic Windows Mobile Devices – Standard and Professional versions
Simulator for Japanese’s i-mode browsers

MITE 2.0

3rd-party Generic Simulator Generic No native Windows
Download75MbRequires login Devices: Generic Browser Testing with a library of hundreds of devices.
The rendering engine is not real. This tool simulates network requests as some mobile device and it renders the response on a generic webkit with some customization per device.

SONY ERICSSON SDK FOR WINDOWS MOBILE 6.1

Official Windows Mobile Add-on Internet Explorer .NET / C++ Windows
Download7Mb Devices: Sony Ericsson Xperia X1
Requires Windows Mobile SDK

SAMSUNG DEVICE EMULATOR SKINS FOR WINDOWS MOBILE

Official Samsung Add-on Internet Explorer .NET / C++ Windows
Download7Mb Devices: GT-B7300 Omnia Lite, GT-B7330 Omnia Pro, GT-B7620 Armani, GT-I8000 Omnia II, SPH-i350 Intrepid
Requires Windows Mobile SDK

SONY ERICSSON PHONEGAP SIMULATOR

Official Generic Simulator PhoneGap apps PhoneGap hybrids Windows
Download2Mb Devices: Generic Android (such as Xperia X10) and Symbian (such as Satio)
Requires Windows Mobile SDK

LG SDK FOR THE JAVA PLATFORM

Official LG Simulator No Browser Support Java ME Windows
Download60-100Mb Devices: All propietary-OS LG devices
Only for Java ME testing

SONY ERICSSON SDK FOR THE JAVA PLATFORM

Official LG Simulator No Browser Support Java ME Windows
Download120Mb Devices: All propietary-OS Sony Ericsson devices
Only for Java ME testing

SAMSUNG JAVA SDK

Official Samsung Simulator No Browser Support Java ME Windows
Download150MbRequires login Devices: All propietary-OS Samsung devices
Only for Java ME testing

SAMSUNG MOBILE WIDGET EMULATOR

Official Samsung Simulator Only Widgets Widgets Windows
Download150MbRequires login Devices: All propietary-OS, Symbian and Windows Mobile Samsung devices
Only for widget testing

MOTOROLA JAVA ME SDKS

Official Motorola Simulator No Browser Support Java ME Windows
Download40-150Mb eachRequires login Devices: All propietary-OS Motorola devices
Only for Java ME testing

WAC EMULATOR

3rd-party Several Simulator Only Widgets Widgets MacWindowsLinux
Download150Mb each Devices: Generic Widget platform
Only for widget testing

BREW SDK

Official Brew Simulator Basic browser Flash / C++ Windows
Download200Mb Devices: Generic BREW platform

EMULATORS AND SIMULATORS

Generally speaking, an emulator is a piece of software that translates compiled code from an original architecture to the platform where it is running, such as the greatMAME. In the mobile development world, a device emulator is a desktop application that emulates mobile device hardware and operating systems, allowing us to test and debug our applications and see how they are working. There are also operating system emulators that don’t represent any real device hardware but rather the operating system as a whole. These exist for Windows Mobile and Android.

On the other hand, a simulator is a less complex application that simulates some of the behavior of a device, but does not emulate hardware and does not work over the real operating system. These tools are simpler and less useful than emulators. A simulator may be created by the device manufacturer or by some other company offering a simulation environment for developers.

If you want to download every emulator on this list you will need at least one MacOS desktop and one Windows desktop, downloading at least 25Gb of applications.

Do you know any other emulator? Leave it in the comments area?

Thanks 🙂 Keep Coding 🙂

SolutionPointLogo