[Q] Store and Send data automatically Using SQLite Database topic

Hi my app will do wifi scan and send the data (using JSON Array) to splunk server(web server) periodically, now at the time of wifi disconnection I cant send, so I am planning to store the scan result using SQLite database and automatically send them to server when it reconnect to the wifi network. Kindly help me how to do that.

FYI: when the user is disconnected the app will scan and store data for every 1 min and when reconnect it should send automatically the stored data to the server.

I can start the scan when disconnection happens but dont know how to store the data in JSON format in SQLite and send them automatically to server without getting request form the server.

Thanks in Advance

[Q] app slow down when using sqlite database topic

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

SQLite + FTS (full text search) topic

Hi,

On my Debian stable 64-bit system, SQLite3 has FTS (full text search) enabled (although at version 3 rather than the recommended version 4):

Python 3.2.3 (default, Feb 20 2013, 14:44:27) [GCC 4.7.2] on linux2
Type "copyright", "credits" or "license()" for more information.
>>> import sqlite3
>>> con = sqlite3.connect(":memory:")
>>> cur = con.execute("pragma compile_options")
>>> for row in cur:
print(row)
....
('ENABLE_FTS3',)
....

But on Windows when I use the official Python 3.3 32-bit binary from www.python.org this is not enabled.

My guess is that on Debian, the packagers install a full SQLite 3 and the Python package uses that. But on Windows I think the Python packagers bundle their own SQLite (quite rightly since it might not already be installed).

I'd like the Windows binary to include SQLite 3 with FTS4 support, but I don't know how much work that involves or if it would make the Python .msi file too big?

Anyway, I guess if anyone else is interested in this they could perhaps reply to indicate this?

If you're curious about the feature, it is documented here:
http://www.sqlite.org/fts3.html