Creating a multi-table SQL database in android

Question

I'm trying to create a multi-table database for my android application. I'm following the suggestion given on this site http://androidforbeginners.blogspot.com/2010/01/creating-multiple-sqlite-database.html for doing it. I keep on getting the error below. The error seems to be cause by the onCreate of the databaes tables.

If you look at my onCreate in the the DBHelper class I have two commented out. This allows it to work no matter which one is left uncommeted.

There has to be a way to create a multi-table database because a single table in a database almost defeats the purpose of having a database.

10-23 02:11:35.383: ERROR/AndroidRuntime(300): Caused by: android.database.sqlite.SQLiteException: Can't upgrade read-only database from version 0 to 1: /data/data/com.parkingticket/databases/Tickets.db

Thanks in advance.

Here is my code

package com.parkingticket;

import java.sql.SQLException;

import android.content.ContentValues; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteDatabase.CursorFactory; import android.database.sqlite.SQLiteException; import android.database.sqlite.SQLiteOpenHelper; import android.util.Log;

public class TicketDBAdapter { private static final String DATABASE_NAME="Tickets.db"; private static final int DATABASE_VERSION = 1;

private static final String PARKEDCARS_TABLE = "ParkedCars";
private static final String PARKINGMETERS_TABLE = "ParkingMeters";
private static final String PARKINGTICKETS_TABLE = "ParkingTickets";
private static final String POLICEOFFICERS_TABLE = "PoliceOfficers";

// The name and column index for each column in PARKEDCARS
public static final String KEY_CARID = "carID";
    public static final int CARID_COLUMN = 0;
public static final String KEY_CARMAKE = "Make";
    public static final int CARMAKE_COLUMN = 1;
public static final String KEY_CARMODEL = "Model";
    public static final int CARMODEL_COLUMN = 2;
public static final String KEY_CARCOLOR = "Color";
    public static final int CARCOLOR_COLUMN = 3;
public static final String KEY_CARLICENSENUMBER = "LicenseNumber";
    public static final int CARLICENSENUMBER_COLUMN = 4;
public static final String KEY_CARMINUTESPARKED = "MinutesParked";
    public static final int CARMINUTESPARKED_COLUMN = 5;

// The name and column index for each column in PARKINGMETERS
public static final String KEY_METERID = "meterID";
    public static final int METERID_COLUMN = 0;
public static final String KEY_MINUTESPURCHASED = "MinutesPurchased";
    public static final int MINUTESPURCHASED_COLUMN = 1;

// The name and column index for each column in PARKINGTICKETS
    //TODO create the columns and indexs for parking tickets

// The name and column index for each column in POLICEOFFICERS
public static final String KEY_OFFICERID = "officerID";
    public static final int OFFICERID_COLUMN = 0;
public static final String KEY_OFFICERNAME = "Name";
    public static final int OFFICERNAME_COLUMN = 1;
public static final String KEY_OFFICERBADGE = "BadgeNumber";
    public static final int OFFICERBADE_COLUMN = 2;


//Variable to hold the database instance
private SQLiteDatabase ticketDB;

//Context of the application using the database.
private final Context context;

//Database open/upgrade helper
private TicketDBHelper ticketDBHelper;

public TicketDBAdapter(Context _context)
{
    context = _context;
    ticketDBHelper = new TicketDBHelper(context, DATABASE_NAME, null, DATABASE_VERSION);
}

public void open() throws SQLiteException
{
    try
    {
        ticketDB = ticketDBHelper.getWritableDatabase();
    }
    catch(SQLiteException ex)
    {
        ticketDB = ticketDBHelper.getReadableDatabase();
    }
}

public void close()
{
    ticketDB.close();
}

 //Insert a new ParkedCar
public long insertParkedCar(ParkedCar _car)
{
    //Create a new row of values to insert
    ContentValues newParkedCarValues = new ContentValues();

    //Assign values for each row
    newParkedCarValues.put(KEY_CARMAKE, _car.getMake());
    newParkedCarValues.put(KEY_CARMODEL, _car.getModel());
    newParkedCarValues.put(KEY_CARCOLOR, _car.getColor());
    newParkedCarValues.put(KEY_CARLICENSENUMBER, _car.getLicenseNumber());
    newParkedCarValues.put(KEY_CARMINUTESPARKED, _car.getMinutesParked());

    //Insert the row
    return ticketDB.insert(PARKEDCARS_TABLE, null, newParkedCarValues);
}

//Remove a ParkedCar based on its index
public boolean removeParkedCar(long _rowIndex)
{
    return ticketDB.delete(PARKEDCARS_TABLE, KEY_CARID + "=" + _rowIndex, null)>0;
}

//Update a ParkedCar's MinutesParked
//TODO Create an update for ParkedCar's minutesParked.

public Cursor getAllParkedCarsCursor()
{
    return ticketDB.query(PARKEDCARS_TABLE, new String[] {KEY_CARID, KEY_CARMAKE, KEY_CARMODEL, KEY_CARCOLOR, KEY_CARLICENSENUMBER, KEY_CARMINUTESPARKED}, null, null, null, null, null);
}

public Cursor setCursorParkedCar(long _rowIndex) throws SQLException
{
    Cursor result = ticketDB.query(true, PARKEDCARS_TABLE, new String []{KEY_CARID}, KEY_CARID + "=" + _rowIndex, null, null, null, null, null);

    if ((result.getCount() == 0) || !result.moveToFirst())
    {
        throw new SQLException("No ParkedCar found for row: " + _rowIndex);
    }

    return result;
}

public static class TicketDBHelper extends SQLiteOpenHelper
{
    public TicketDBHelper(Context context, String name, CursorFactory factory, int version)
    {
        super(context, name, factory, version);
    }

    //SQL Statement to create PARKEDCARS table
    private static final String PARKEDCARS_CREATE = "create table " + PARKEDCARS_TABLE + " (" + KEY_CARID + " integer primary key autoincrement, " + KEY_CARMAKE + " text not null," + KEY_CARMODEL + " text not null," + KEY_CARCOLOR + " text not null," + KEY_CARLICENSENUMBER + " text not null," + KEY_CARMINUTESPARKED + "int not null);";

    //SQL Statement to create ParkingMeters table
    private static final String PARKINGMETERS_CREATE = "create table" + PARKINGMETERS_TABLE + " (" + KEY_METERID + " integer primary key autoincrement, " + KEY_MINUTESPURCHASED + " int not null);";

    //SQL Statement to create ParkingTickets table
    //TODO create the statement for parkingTickets

    //SQL Statement to create PoliceOfficers table
    private static final String POLICEOFFICERS_CREATE = "create table" + POLICEOFFICERS_TABLE + " (" + KEY_OFFICERID + " integer primary key autoincrement, " + KEY_OFFICERNAME + " text not null," + KEY_OFFICERBADGE + "text not null);";

    //Called when no database exists in disk and the helper class needs to create a new one.
    @Override
    public void onCreate(SQLiteDatabase _db)
    {

// _db.execSQL(PARKEDCARS_CREATE); _db.execSQL(PARKINGMETERS_CREATE); // _db.execSQL(POLICEOFFICERS_CREATE); }

    //Called when there is a database verion mismatch meaning that the version of the database on disk needs to be upgraded to the current version
    @Override
    public void onUpgrade(SQLiteDatabase _db, int _oldVersion, int _newVersion)
    {
        //Log the version upgrade.
        Log.w("TaskDBAdapter", "Upgrading from version " + _oldVersion + " to " + _newVersion + ", which will destroy all old data");

        //Upgrade the existing database to conform to the new version
        //Multiple previous versions can be handled by comparing _oldVersoin and _newVersion values

        //The simplest case is to drop teh old table and create a new one.
        _db.execSQL("DROP TABLE IF EXISTS " + PARKEDCARS_TABLE);
        _db.execSQL("DROP TABLE IF EXISTS " + PARKINGMETERS_TABLE);
        _db.execSQL("DROP TABLE IF EXISTS " + POLICEOFFICERS_TABLE);

        onCreate(_db);
    }
}

}

Answer 1

Put a Log statement in your open() method when you catch the SQLiteException to see if ticketDBHelper.getWritableDatabase() is failing and subsequently calling ticketDBHelper.getReadableDatabase(), which as the name implies, opens the database as read-only.

Answer 2

Hi, I have written a multi table app which works fine. Sounds like you may have a problem opening the existing db file for writing. I would suggest deleting that from your emulator/device, incrementing the version number and rerunning the app. My open method is pasted here.

public DbAdapter open() throws SQLException {
    mDbHelper = new DatabaseHelper(mCtx);
    mDb = mDbHelper.getWritableDatabase();
    return this;
}

Anthony Nolan

Answer 3

I have found a solution to the problem but not a direct answer on this site.

http://pheide.com/page/11/tab/24#post13

Why using a abstract class to create the databases the extending that class works I can't figure out for the life of me because it seems I'm doing the same thing in my one class up above. None the less it works as far as I've played around with it.