[Q] app slow down when using sqlite database

Search This thread

axr0284

Member
Oct 15, 2012
7
0
I am trying to create a circular buffer using sqlite. For some reason every time I instantiate my db access class, the os start skipping frames (I am using the emulator to run my code).

02-22 20:22:03.172: I/Choreographer(860): Skipped 628 frames! The application may be doing too much work on its main thread.

I do not understand what I am doing wrong. I am calling the database class from an intentService (I assume this should not slow down the main thread at all) as follows:
Code:
private SqliteLog mSqliteLog;
mSqliteLog = new SqliteLog(context);
mSqliteLog.writelogInformation("sleepMode", "ON");
I added my code at the end of this message
Code:
/**
 * SqliteLog
 * 
 *
 * Base class for sqliteLog control
 * 
 * 
 */
public class SqliteLog {

    // Debug log tag
    private static final String tag = "SqliteLog";

    // Version of database
    private static final int DATABASE_VERSION = 1;

    // Name of database
    private static final String DATABASE_NAME = "log";

    // Table of database
    private static final String TABLE_NAME = "log";

    public static final String ROWID_NAME = "id";
    public static final String PREFERENCE_NAME = tag + "Pref";

    public static final String COLUMN_LOGNUMBER = "logNumber";
    public static final String COLUMN_TIME = "time";
    public static final String COLUMN_FUNCTION = "function";
    public static final String COLUMN_DESCRIPTION = "description";
    public static final int TABLE_SIZE = 20;

    private static final String DATABASE_CREATE ="create table " + TABLE_NAME + " (" + ROWID_NAME + " integer primary key autoincrement, " +
                                                                                    COLUMN_LOGNUMBER + " INTEGER NOT NULL, " +
                                                                                    COLUMN_TIME + " TEXT NOT NULL, " +
                                                                                    COLUMN_FUNCTION + " TEXT NOT NULL, " +
                                                                                    COLUMN_DESCRIPTION + " TEXT NOT NULL " +
                                                                                  ");";

    //The context of the calling class;
    private Context thisContext;

    /**
     * <p>Constructor for SqliteLog
     * @param context :- Context of calling class
     * 
     */
    public SqliteLog(Context context) {
        Log.d(tag,"SqliteLog constructor called");

        thisContext = context;
    }

    /**
     * writelogInformation :- Writes a row into the log table
     * 
     */
    public void writelogInformation(String functionName, String descriptionInfo) {
        // Retrieve preferences
        SharedPreferences SqliteLogPref = thisContext.getSharedPreferences(PREFERENCE_NAME,  Context.MODE_PRIVATE);
        int logNumber = SqliteLogPref.getInt("logNumber", 1);

        // Open database for writing
        DatabaseHelper databaseHelper = new DatabaseHelper(thisContext);
        SQLiteDatabase sQLiteDatabase = databaseHelper.getWritableDatabase();

        // Define the column name and data
        ContentValues values = new ContentValues();
        values.put(COLUMN_LOGNUMBER, logNumber);
        values.put(COLUMN_TIME, getTime());
        values.put(COLUMN_FUNCTION, functionName);
        values.put(COLUMN_DESCRIPTION, descriptionInfo);

        // Update database
        sQLiteDatabase.update(TABLE_NAME, values, null, null);

        // Close database
        databaseHelper.close();

        // Test if next database update will need to be wrapped around
        logNumber = (logNumber % TABLE_SIZE) + 1;

        // Store preferences
        SharedPreferences.Editor editor = SqliteLogPref.edit();
        editor.putInt("logNumber", logNumber);
        editor.commit();
    }

    /**
     * clearLog :- Erase all information from table
     * 
     */
    public void clearLog() {
        // Retrieve preferences
        SharedPreferences SqliteLogPref = thisContext.getSharedPreferences(PREFERENCE_NAME, 0);

        // Store preferences
        SharedPreferences.Editor editor = SqliteLogPref.edit();
        editor.putInt("logNumber", 1);
        editor.commit();

        // Delete all rows
        DatabaseHelper databaseHelper = new DatabaseHelper(thisContext);
        SQLiteDatabase sQLiteDatabase = databaseHelper.getReadableDatabase();
        sQLiteDatabase.delete (TABLE_NAME, null, null);
    }

    /**
     * readlogInformation :- Read the whole table
     * 
     */
    public String[] readlogInformation() {
        // Create string array of appropriate length
        String[] returnArray;

        // Retrieve preferences
        SharedPreferences SqliteLogPref = thisContext.getSharedPreferences(PREFERENCE_NAME, 0);
        int logNumber = SqliteLogPref.getInt("logNumber", 0);

        // Open database for reading
        DatabaseHelper databaseHelper = new DatabaseHelper(thisContext);

        try {
            SQLiteDatabase sQLiteDatabase = databaseHelper.getReadableDatabase();

            // Get a cursor to the correct cell
            Cursor cursor = sQLiteDatabase.query(TABLE_NAME, null, null, null, null, null, null);

            // Get number of rows in table
            int lengthOfTable = 0;

            // Move cursor to where it needs to be
            if (cursor != null) {
                lengthOfTable = cursor.getCount();

                // If count is less than max, then we have not wrapped around yet
                if(lengthOfTable < TABLE_SIZE) {
                    cursor.moveToFirst();
                }

                // Position cursor appropriately
                else {
                    cursor.moveToPosition(logNumber-1);

                }

                // Create string array of appropriate length
                returnArray = new String[lengthOfTable];

                for(int i=1; i<=lengthOfTable; i++) {

                    returnArray[i] = cursor.getString(1) + "; " + cursor.getString(2) + "; " + cursor.getString(3);
                }
            }
            else {
                Log.e(tag,"Cursor null");

                // Create string array of appropriate length
                returnArray = new String[0];
            }
        } catch(SQLiteException e) {
            Log.d(tag,"SQLiteException when using getReadableDatabase");

            // Create string array of appropriate length
            returnArray = new String[0];

        }


        // Close database
        databaseHelper.close();

        return returnArray;
    }

    /**
     * readlogInformation :- Read the whole table
     * 
     */
    public String getTime() {
        // Create a new time object
        Time currentTime = new Time(Time.getCurrentTimezone());

        // Get current time
        currentTime.setToNow();

        return currentTime.toString();
    }


    /**
     * DatabaseHelper
     * 
     *
     * Class to help control database
     * 
     * 
     */
    private static class DatabaseHelper extends SQLiteOpenHelper {
        /**
         * <p>Constructor for DatabaseHelper
         * @param context :- Context of calling class<p>
         * 
         */
        DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);

            Log.d(tag,"DatabaseHelper constructor called");
        }

        /**
         * <p>onCreate
         * @param db :- Pass an sqlite object
         * 
         */
        @Override
        public void onCreate(SQLiteDatabase db) {
            Log.d(tag,"onCreate called");

            // Create database
            db.execSQL(DATABASE_CREATE);

            // Insert a new row
            ContentValues values = new ContentValues();

            // Create a certain number of rows
            for(int i=1; i<=TABLE_SIZE; i++) {  
                values.clear();
                values.put(COLUMN_LOGNUMBER, i);
                values.put(COLUMN_FUNCTION, "empty");
                values.put(COLUMN_DESCRIPTION, "empty");
                db.insert(TABLE_NAME, "null", values);          
            }

            Log.d(tag,"database created");
        }

        /**
         * <p>onUpgrade
         * @param db :- Pass an sqlite object
         * @param oldVersion :- Old version of table
         * @param newVersion :- New version of table
         * 
         */
        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.d(tag,"onUpgrade called");

            // Not used, but you could upgrade the database with ALTER
            // Scripts
        }
    }
}
I have been trying to figure this out for a while now. I would appreciate any insight, Amish