Example usage for android.database.sqlite SQLiteDatabase update

List of usage examples for android.database.sqlite SQLiteDatabase update

Introduction

In this page you can find the example usage for android.database.sqlite SQLiteDatabase update.

Prototype

public int update(String table, ContentValues values, String whereClause, String[] whereArgs) 

Source Link

Document

Convenience method for updating rows in the database.

Usage

From source file:edu.stanford.mobisocial.dungbeetle.DBHelper.java

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    Log.w(TAG, "Upgrading database from version " + oldVersion + " to " + newVersion);

    if (oldVersion <= 23) {
        Log.w(TAG, "Schema too old to migrate, dropping all.");
        dropAll(db);/*from   www . j  av a  2s.com*/
        onCreate(db);
        return;
    }

    if (oldVersion <= 24) {
        Log.w(TAG, "Adding columns 'presence' and 'status' to contact table.");
        db.execSQL("ALTER TABLE " + Contact.TABLE + " ADD COLUMN " + Contact.STATUS + " TEXT");
        db.execSQL("ALTER TABLE " + Contact.TABLE + " ADD COLUMN " + Contact.PRESENCE + " INTEGER DEFAULT "
                + Presence.AVAILABLE);
    }

    if (oldVersion <= 25) {
        Log.w(TAG, "Adding columns 'presence' and 'status' to contact table.");
        db.execSQL("ALTER TABLE " + Group.TABLE + " ADD COLUMN " + Group.FEED_NAME + " TEXT");
    }

    if (oldVersion <= 26) {
        Log.w(TAG, "Adding column 'picture' to contact table.");
        db.execSQL("ALTER TABLE " + Contact.TABLE + " ADD COLUMN " + Contact.PICTURE + " BLOB");
    }

    if (oldVersion <= 27) {
        Log.w(TAG, "Adding column 'last_presence_time' to contact table.");
        db.execSQL("ALTER TABLE " + Contact.TABLE + " ADD COLUMN " + Contact.LAST_PRESENCE_TIME
                + " INTEGER DEFAULT 0");
    }

    if (oldVersion <= 28) {
        Log.w(TAG, "Adding column 'picture' to my_info table.");
        db.execSQL("ALTER TABLE " + MyInfo.TABLE + " ADD COLUMN " + MyInfo.PICTURE + " BLOB");
    }
    if (oldVersion <= 29) {
        Log.w(TAG, "Adding column 'version' to group table.");
        db.execSQL("ALTER TABLE " + Group.TABLE + " ADD COLUMN " + Group.VERSION + " INTEGER DEFAULT -1");
    }
    if (oldVersion <= 30) {
        Log.w(TAG, "Adding column 'E' to object table.");
        db.execSQL("ALTER TABLE " + DbObject.TABLE + " ADD COLUMN " + DbObject.ENCODED + " BLOB");
        createIndex(db, "INDEX", "objects_by_encoded", DbObject.TABLE, DbObject.ENCODED);
    }
    if (oldVersion <= 31) {
        Log.w(TAG, "Adding column 'child_feed' to object table.");
        db.execSQL("ALTER TABLE " + DbObject.TABLE + " ADD COLUMN " + DbObject.CHILD_FEED_NAME + " TEXT");
        createIndex(db, "INDEX", "child_feeds", DbObject.TABLE, DbObject.CHILD_FEED_NAME);
    }
    if (oldVersion <= 32) {
        // Bug fix.
        Log.w(TAG, "Updating app state objects.");
        db.execSQL("UPDATE " + DbObject.TABLE + " SET " + DbObject.CHILD_FEED_NAME + " = NULL WHERE "
                + DbObject.CHILD_FEED_NAME + " = " + DbObject.FEED_NAME);
    }
    if (oldVersion <= 33) {
        Log.w(TAG, "Adding column 'nearby' to contact table.");
        db.execSQL("ALTER TABLE " + Contact.TABLE + " ADD COLUMN " + Contact.NEARBY + " INTEGER DEFAULT 0");
    }
    if (oldVersion <= 34) {
        Log.w(TAG, "Adding column 'secret' to contact table.");
        db.execSQL("ALTER TABLE " + Contact.TABLE + " ADD COLUMN " + Contact.SHARED_SECRET + " BLOB");

    }
    if (oldVersion <= 35) {
        Log.w(TAG, "Adding column 'last_updated' to group table.");
        db.execSQL("ALTER TABLE " + Group.TABLE + " ADD COLUMN " + Group.LAST_UPDATED + " INTEGER");
    }
    if (oldVersion <= 36) {
        // Can't easily drop columns, but 'update_id' and 'is_child_feed' are dead columns.

        Log.w(TAG, "Adding column 'parent_feed_id' to group table.");
        db.execSQL(
                "ALTER TABLE " + Group.TABLE + " ADD COLUMN " + Group.PARENT_FEED_ID + " INTEGER DEFAULT -1");

        Log.w(TAG, "Adding column 'last_object_id' to group table.");
        db.execSQL(
                "ALTER TABLE " + Group.TABLE + " ADD COLUMN " + Group.LAST_OBJECT_ID + " INTEGER DEFAULT -1");
    }
    if (oldVersion <= 37) {
        // Can't easily drop columns, but 'update_id' and 'is_child_feed' are dead columns.

        Log.w(TAG, "Adding column 'num_unread' to group table.");
        db.execSQL("ALTER TABLE " + Group.TABLE + " ADD COLUMN " + Group.NUM_UNREAD + " INTEGER DEFAULT 0");
    }
    if (oldVersion <= 38) {
        Log.w(TAG, "Adding column 'raw' to object table.");
        db.execSQL("ALTER TABLE " + DbObject.TABLE + " ADD COLUMN " + DbObject.RAW + " BLOB");
    }
    // sadly, we have to do this again because incoming voice obj's were not being split!
    if (oldVersion <= 50) {
        Log.w(TAG, "Converting voice and picture objs to raw.");

        Log.w(TAG, "Converting objs to raw.");
        Cursor c = db.query(DbObject.TABLE, new String[] { DbObject._ID },
                DbObject.TYPE + " = ? AND " + DbObject.RAW + " IS NULL", new String[] { PictureObj.TYPE }, null,
                null, null);
        ArrayList<Long> ids = new ArrayList<Long>();
        if (c.moveToFirst())
            do {
                ids.add(c.getLong(0));
            } while (c.moveToNext());
        c.close();
        DbEntryHandler dbh = DbObjects.forType(PictureObj.TYPE);
        for (Long id : ids) {
            c = db.query(DbObject.TABLE, new String[] { DbObject.JSON, DbObject.RAW }, DbObject._ID + " = ? ",
                    new String[] { String.valueOf(id.longValue()) }, null, null, null);
            if (c.moveToFirst())
                try {
                    String json = c.getString(0);
                    byte[] raw = c.getBlob(1);
                    c.close();
                    if (raw == null) {
                        Pair<JSONObject, byte[]> p = dbh.splitRaw(new JSONObject(json));
                        if (p != null) {
                            json = p.first.toString();
                            raw = p.second;
                            updateJsonAndRaw(db, id, json, raw);
                        }
                    }
                } catch (JSONException e) {
                }
            c.close();
        }
        c = db.query(DbObject.TABLE, new String[] { DbObject._ID },
                DbObject.TYPE + " = ? AND " + DbObject.RAW + " IS NULL", new String[] { VoiceObj.TYPE }, null,
                null, null);
        ids = new ArrayList<Long>();
        if (c.moveToFirst())
            do {
                ids.add(c.getLong(0));
            } while (c.moveToNext());
        c.close();
        dbh = DbObjects.forType(VoiceObj.TYPE);
        for (Long id : ids) {
            c = db.query(DbObject.TABLE, new String[] { DbObject.JSON, DbObject.RAW }, DbObject._ID + " = ? ",
                    new String[] { String.valueOf(id.longValue()) }, null, null, null);
            if (c.moveToFirst())
                try {
                    String json = c.getString(0);
                    byte[] raw = c.getBlob(1);
                    c.close();
                    if (raw == null) {
                        Pair<JSONObject, byte[]> p = dbh.splitRaw(new JSONObject(json));
                        if (p != null) {
                            json = p.first.toString();
                            raw = p.second;
                            updateJsonAndRaw(db, id, json, raw);
                        }
                    }
                } catch (JSONException e) {
                }
            c.close();
        }
    }
    if (oldVersion <= 40) {
        Log.w(TAG, "Adding column 'E' to object table.");
        db.execSQL("ALTER TABLE " + DbObject.TABLE + " ADD COLUMN " + DbObject.HASH + " INTEGER");
        createIndex(db, "INDEX", "objects_by_hash", DbObject.TABLE, DbObject.HASH);
        db.execSQL("DROP INDEX objects_by_encoded");
        db.delete(DbObject.TABLE, DbObject.TYPE + " = ?", new String[] { "profile" });
        db.delete(DbObject.TABLE, DbObject.TYPE + " = ?", new String[] { "profilepicture" });
        ContentValues cv = new ContentValues();
        cv.putNull(DbObject.ENCODED);
        db.update(DbObject.TABLE, cv, null, null);
    }
    if (oldVersion <= 41) {
        db.execSQL("DROP INDEX objects_by_sequence_id");
        db.execSQL("CREATE INDEX objects_by_sequence_id ON " + DbObject.TABLE + "(" + DbObject.CONTACT_ID + ", "
                + DbObject.FEED_NAME + ", " + DbObject.SEQUENCE_ID + ")");
    }
    //secret to life, etc
    if (oldVersion <= 42) {
        db.execSQL("DROP INDEX objects_by_creator_id");
        db.execSQL("CREATE INDEX objects_by_creator_id ON " + DbObject.TABLE + "(" + DbObject.CONTACT_ID + ", "
                + DbObject.SENT + ")");
    }

    if (oldVersion <= 44) {
        // oops.
        db.execSQL("DROP TABLE IF EXISTS " + DbRelation.TABLE);
        createRelationBaseTable(db);
    }
    if (oldVersion <= 45) {
        db.execSQL("ALTER TABLE " + Contact.TABLE + " ADD COLUMN " + Contact.LAST_OBJECT_ID + " INTEGER");
        db.execSQL("ALTER TABLE " + Contact.TABLE + " ADD COLUMN " + Contact.LAST_UPDATED + " INTEGER");
        db.execSQL("ALTER TABLE " + Contact.TABLE + " ADD COLUMN " + Contact.NUM_UNREAD + " INTEGER DEFAULT 0");
    }
    if (oldVersion <= 46) {
        db.execSQL("ALTER TABLE " + DbObject.TABLE + " ADD COLUMN " + DbObject.DELETED + " INTEGER DEFAULT 0");
    }
    if (oldVersion <= 47) {
        addRelationIndexes(db);
    }
    if (oldVersion <= 44) {
        createUserAttributesTable(db);
    }

    if (oldVersion <= 49) {
        if (oldVersion > 44) {
            db.execSQL("ALTER TABLE " + DbRelation.TABLE + " ADD COLUMN " + DbRelation.RELATION_TYPE + " TEXT");
            createIndex(db, "INDEX", "relations_by_type", DbRelation.TABLE, DbRelation.RELATION_TYPE);
        }
        db.execSQL("UPDATE " + DbRelation.TABLE + " SET " + DbRelation.RELATION_TYPE + " = 'parent'");
    }
    if (oldVersion <= 52) {
        Log.w(TAG, "Adding column 'about' to my_info table.");
        try {
            db.execSQL("ALTER TABLE " + MyInfo.TABLE + " ADD COLUMN " + MyInfo.ABOUT + " TEXT DEFAULT ''");
        } catch (Exception e) {
            // because of bad update, we just ignore the duplicate column error
        }
    }
    if (oldVersion <= 53) {
        db.execSQL("ALTER TABLE " + Contact.TABLE + " ADD COLUMN " + Contact.HIDDEN + " INTEGER DEFAULT 0");
    }
    if (oldVersion <= 55) {
        db.execSQL("ALTER TABLE " + DbObj.TABLE + " ADD COLUMN " + DbObj.COL_KEY_INT + " INTEGER");
    }
    if (oldVersion <= 56) {
        db.execSQL("DROP INDEX attrs_by_contact_id");
        createIndex(db, "INDEX", "attrs_by_contact_id", DbContactAttributes.TABLE,
                DbContactAttributes.CONTACT_ID);
    }
    if (oldVersion <= 57) {
        db.execSQL("ALTER TABLE " + DbObject.TABLE + " ADD COLUMN " + DbObject.LAST_MODIFIED_TIMESTAMP
                + " INTEGER");
        db.execSQL("UPDATE " + DbObject.TABLE + " SET " + DbObject.LAST_MODIFIED_TIMESTAMP + " = "
                + DbObject.TIMESTAMP);
    }
    if (oldVersion <= 58) {
        db.execSQL("ALTER TABLE " + Group.TABLE + " ADD COLUMN " + Group.GROUP_TYPE + " TEXT DEFAULT 'group'");
        db.execSQL("UPDATE " + Group.TABLE + " SET " + Group.GROUP_TYPE + " = 'group'");
    }
    if (oldVersion <= 59) {
        createIndex(db, "INDEX", "objects_last_modified", DbObject.TABLE, DbObject.LAST_MODIFIED_TIMESTAMP);
    }
    if (oldVersion <= 60) {
        db.execSQL("ALTER TABLE " + Contact.TABLE + " ADD COLUMN " + Contact.PUBLIC_KEY_HASH_64
                + " INTEGER DEFAULT 0");
        createIndex(db, "INDEX", "contacts_by_pkp", Contact.TABLE, Contact.PUBLIC_KEY_HASH_64);
        Cursor peeps = db
                .rawQuery("SELECT " + Contact._ID + "," + Contact.PUBLIC_KEY + " FROM " + Contact.TABLE, null);
        peeps.moveToFirst();
        while (!peeps.isAfterLast()) {
            db.execSQL("UPDATE " + Contact.TABLE + " SET " + Contact.PUBLIC_KEY_HASH_64 + " = "
                    + hashPublicKey(peeps.getBlob(1)) + " WHERE " + Contact._ID + " = " + peeps.getLong(0));
            peeps.moveToNext();
        }
        peeps.close();
    }
    db.setVersion(VERSION);
}

From source file:org.opendatakit.common.android.utilities.ODKDatabaseUtils.java

private void upsertDataIntoExistingDBTable(SQLiteDatabase db, String tableId,
        ArrayList<ColumnDefinition> orderedColumns, ContentValues cvValues, boolean shouldUpdate) {
    String rowId = null;/*from w  w  w .j  a  v a2  s  .co m*/
    String whereClause = null;
    boolean specifiesConflictType = cvValues.containsKey(DataTableColumns.CONFLICT_TYPE);
    boolean nullConflictType = specifiesConflictType && (cvValues.get(DataTableColumns.CONFLICT_TYPE) == null);
    String[] whereArgs = new String[specifiesConflictType ? (1 + (nullConflictType ? 0 : 1)) : 1];
    boolean update = false;

    if (cvValues.size() <= 0) {
        throw new IllegalArgumentException(t + ": No values to add into table " + tableId);
    }

    ContentValues cvDataTableVal = new ContentValues();
    cvDataTableVal.putAll(cvValues);

    if (cvDataTableVal.containsKey(DataTableColumns.ID)) {
        // The user specified a row id; we need to determine whether to
        // insert or update the record, or to reject the action because
        // there are either checkpoint records for this row id, or, if
        // a server conflict is associated with this row, that the
        // _conflict_type to update was not specified.
        //
        // i.e., the tuple (_id, _conflict_type) should be unique. If
        // we find that there are more than 0 or 1 records matching this
        // tuple, then we should reject the update request.
        //
        // TODO: perhaps we want to allow updates to the local conflict
        // row if there are no checkpoints on it? I.e., change the
        // tri-state conflict type to a pair of states (local / remote).
        // and all local changes are flagged local. Remote only exists
        // if the server is in conflict.

        rowId = cvDataTableVal.getAsString(DataTableColumns.ID);
        if (rowId == null) {
            throw new IllegalArgumentException(DataTableColumns.ID + ", if specified, cannot be null");
        }

        if (specifiesConflictType) {
            if (nullConflictType) {
                whereClause = DataTableColumns.ID + " = ?" + " AND " + DataTableColumns.CONFLICT_TYPE
                        + " IS NULL";
                whereArgs[0] = rowId;
            } else {
                whereClause = DataTableColumns.ID + " = ?" + " AND " + DataTableColumns.CONFLICT_TYPE + " = ?";
                whereArgs[0] = rowId;
                whereArgs[1] = cvValues.getAsString(DataTableColumns.CONFLICT_TYPE);
            }
        } else {
            whereClause = DataTableColumns.ID + " = ?";
            whereArgs[0] = rowId;
        }

        String sel = "SELECT * FROM " + tableId + " WHERE " + whereClause;
        String[] selArgs = whereArgs;
        Cursor cursor = rawQuery(db, sel, selArgs);

        // There must be only one row in the db for the update to work
        if (shouldUpdate) {
            if (cursor.getCount() == 1) {
                update = true;
            } else if (cursor.getCount() > 1) {
                throw new IllegalArgumentException(
                        t + ": row id " + rowId + " has more than 1 row in table " + tableId);
            }
        } else {
            if (cursor.getCount() > 0) {
                throw new IllegalArgumentException(
                        t + ": id " + rowId + " is already present in table " + tableId);
            }
        }

    } else {
        rowId = "uuid:" + UUID.randomUUID().toString();
    }

    // TODO: This is broken w.r.t. updates of partial fields
    // TODO: This is broken w.r.t. updates of partial fields
    // TODO: This is broken w.r.t. updates of partial fields
    // TODO: This is broken w.r.t. updates of partial fields

    if (!cvDataTableVal.containsKey(DataTableColumns.ID)) {
        cvDataTableVal.put(DataTableColumns.ID, rowId);
    }

    if (update) {
        if (!cvDataTableVal.containsKey(DataTableColumns.SYNC_STATE)
                || (cvDataTableVal.get(DataTableColumns.SYNC_STATE) == null)) {
            cvDataTableVal.put(DataTableColumns.SYNC_STATE, SyncState.changed.name());
        }

        if (cvDataTableVal.containsKey(DataTableColumns.LOCALE)
                && (cvDataTableVal.get(DataTableColumns.LOCALE) == null)) {
            cvDataTableVal.put(DataTableColumns.LOCALE, DataTableColumns.DEFAULT_LOCALE);
        }

        if (cvDataTableVal.containsKey(DataTableColumns.SAVEPOINT_TYPE)
                && (cvDataTableVal.get(DataTableColumns.SAVEPOINT_TYPE) == null)) {
            cvDataTableVal.put(DataTableColumns.SAVEPOINT_TYPE, SavepointTypeManipulator.complete());
        }

        if (!cvDataTableVal.containsKey(DataTableColumns.SAVEPOINT_TIMESTAMP)
                || cvDataTableVal.get(DataTableColumns.SAVEPOINT_TIMESTAMP) == null) {
            String timeStamp = TableConstants.nanoSecondsFromMillis(System.currentTimeMillis());
            cvDataTableVal.put(DataTableColumns.SAVEPOINT_TIMESTAMP, timeStamp);
        }

        if (!cvDataTableVal.containsKey(DataTableColumns.SAVEPOINT_CREATOR)
                || (cvDataTableVal.get(DataTableColumns.SAVEPOINT_CREATOR) == null)) {
            cvDataTableVal.put(DataTableColumns.SAVEPOINT_CREATOR, DataTableColumns.DEFAULT_SAVEPOINT_CREATOR);
        }
    } else {

        if (!cvDataTableVal.containsKey(DataTableColumns.ROW_ETAG)
                || cvDataTableVal.get(DataTableColumns.ROW_ETAG) == null) {
            cvDataTableVal.put(DataTableColumns.ROW_ETAG, DataTableColumns.DEFAULT_ROW_ETAG);
        }

        if (!cvDataTableVal.containsKey(DataTableColumns.SYNC_STATE)
                || (cvDataTableVal.get(DataTableColumns.SYNC_STATE) == null)) {
            cvDataTableVal.put(DataTableColumns.SYNC_STATE, SyncState.new_row.name());
        }

        if (!cvDataTableVal.containsKey(DataTableColumns.CONFLICT_TYPE)) {
            cvDataTableVal.putNull(DataTableColumns.CONFLICT_TYPE);
        }

        if (!cvDataTableVal.containsKey(DataTableColumns.FILTER_TYPE)
                || (cvDataTableVal.get(DataTableColumns.FILTER_TYPE) == null)) {
            cvDataTableVal.put(DataTableColumns.FILTER_TYPE, DataTableColumns.DEFAULT_FILTER_TYPE);
        }

        if (!cvDataTableVal.containsKey(DataTableColumns.FILTER_VALUE)
                || (cvDataTableVal.get(DataTableColumns.FILTER_VALUE) == null)) {
            cvDataTableVal.put(DataTableColumns.FILTER_VALUE, DataTableColumns.DEFAULT_FILTER_VALUE);
        }

        if (!cvDataTableVal.containsKey(DataTableColumns.FORM_ID)) {
            cvDataTableVal.putNull(DataTableColumns.FORM_ID);
        }

        if (!cvDataTableVal.containsKey(DataTableColumns.LOCALE)
                || (cvDataTableVal.get(DataTableColumns.LOCALE) == null)) {
            cvDataTableVal.put(DataTableColumns.LOCALE, DataTableColumns.DEFAULT_LOCALE);
        }

        if (!cvDataTableVal.containsKey(DataTableColumns.SAVEPOINT_TYPE)
                || (cvDataTableVal.get(DataTableColumns.SAVEPOINT_TYPE) == null)) {
            cvDataTableVal.put(DataTableColumns.SAVEPOINT_TYPE, SavepointTypeManipulator.complete());
        }

        if (!cvDataTableVal.containsKey(DataTableColumns.SAVEPOINT_TIMESTAMP)
                || cvDataTableVal.get(DataTableColumns.SAVEPOINT_TIMESTAMP) == null) {
            String timeStamp = TableConstants.nanoSecondsFromMillis(System.currentTimeMillis());
            cvDataTableVal.put(DataTableColumns.SAVEPOINT_TIMESTAMP, timeStamp);
        }

        if (!cvDataTableVal.containsKey(DataTableColumns.SAVEPOINT_CREATOR)
                || (cvDataTableVal.get(DataTableColumns.SAVEPOINT_CREATOR) == null)) {
            cvDataTableVal.put(DataTableColumns.SAVEPOINT_CREATOR, DataTableColumns.DEFAULT_SAVEPOINT_CREATOR);
        }
    }

    cleanUpValuesMap(orderedColumns, cvDataTableVal);

    boolean dbWithinTransaction = db.inTransaction();
    try {
        if (!dbWithinTransaction) {
            db.beginTransaction();
        }

        if (update) {
            db.update(tableId, cvDataTableVal, whereClause, whereArgs);
        } else {
            db.insertOrThrow(tableId, null, cvDataTableVal);
        }

        if (!dbWithinTransaction) {
            db.setTransactionSuccessful();
        }
    } finally {
        if (!dbWithinTransaction) {
            db.endTransaction();
        }
    }

}

From source file:org.totschnig.myexpenses.provider.TransactionDatabase.java

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    try {// w  ww  . j av  a 2  s  . co  m
        Log.w(TAG, "Upgrading database from version " + oldVersion + " to " + newVersion + ".");
        if (oldVersion < 17) {
            db.execSQL("drop table accounts");
            db.execSQL("CREATE TABLE accounts (_id integer primary key autoincrement, label text not null, "
                    + "opening_balance integer, description text, currency text not null);");
            //db.execSQL("ALTER TABLE expenses add column account_id integer");
        }

        if (oldVersion < 18) {
            db.execSQL(
                    "CREATE TABLE payee (_id integer primary key autoincrement, name text unique not null);");
            db.execSQL("ALTER TABLE expenses add column payee text");
        }

        if (oldVersion < 19) {
            db.execSQL("ALTER TABLE expenses add column transfer_peer text");
        }

        if (oldVersion < 20) {
            db.execSQL(
                    "CREATE TABLE transactions ( _id integer primary key autoincrement, comment text not null, "
                            + "date datetime not null, amount integer not null, cat_id integer, account_id integer, "
                            + "payee  text, transfer_peer integer default null);");
            db.execSQL("INSERT INTO transactions (comment,date,amount,cat_id,account_id,payee,transfer_peer)"
                    + " SELECT comment,date,CAST(ROUND(amount*100) AS INTEGER),cat_id,account_id,payee,transfer_peer FROM expenses");
            db.execSQL("DROP TABLE expenses");
            db.execSQL("ALTER TABLE accounts RENAME to accounts_old");
            db.execSQL("CREATE TABLE accounts (_id integer primary key autoincrement, label text not null, "
                    + "opening_balance integer, description text, currency text not null);");
            db.execSQL("INSERT INTO accounts (label,opening_balance,description,currency)"
                    + " SELECT label,CAST(ROUND(opening_balance*100) AS INTEGER),description,currency FROM accounts_old");
            db.execSQL("DROP TABLE accounts_old");
        }

        if (oldVersion < 21) {
            db.execSQL(
                    "CREATE TABLE paymentmethods (_id integer primary key autoincrement, label text not null, type integer default 0);");
            db.execSQL(
                    "CREATE TABLE accounttype_paymentmethod (type text, method_id integer, primary key (type,method_id));");
            ContentValues initialValues;
            long _id;
            for (PaymentMethod.PreDefined pm : PaymentMethod.PreDefined.values()) {
                initialValues = new ContentValues();
                initialValues.put("label", pm.name());
                initialValues.put("type", pm.paymentType);
                _id = db.insert("paymentmethods", null, initialValues);
                initialValues = new ContentValues();
                initialValues.put("method_id", _id);
                initialValues.put("type", "BANK");
                db.insert("accounttype_paymentmethod", null, initialValues);
            }
            db.execSQL("ALTER TABLE transactions add column payment_method_id integer");
            db.execSQL("ALTER TABLE accounts add column type text default 'CASH'");
        }

        if (oldVersion < 22) {
            db.execSQL("CREATE TABLE templates ( _id integer primary key autoincrement, comment text not null, "
                    + "amount integer not null, cat_id integer, account_id integer, payee text, transfer_peer integer default null, "
                    + "payment_method_id integer, title text not null);");
        }

        if (oldVersion < 23) {
            db.execSQL("ALTER TABLE templates RENAME to templates_old");
            db.execSQL("CREATE TABLE templates ( _id integer primary key autoincrement, comment text not null, "
                    + "amount integer not null, cat_id integer, account_id integer, payee text, transfer_peer integer default null, "
                    + "payment_method_id integer, title text not null, unique(account_id, title));");
            try {
                db.execSQL(
                        "INSERT INTO templates(comment,amount,cat_id,account_id,payee,transfer_peer,payment_method_id,title)"
                                + " SELECT comment,amount,cat_id,account_id,payee,transfer_peer,payment_method_id,title FROM templates_old");
            } catch (SQLiteConstraintException e) {
                Log.e(TAG, e.getLocalizedMessage());
                //theoretically we could have entered duplicate titles for one account
                //we silently give up in that case (since this concerns only a narrowly distributed alpha version)
            }
            db.execSQL("DROP TABLE templates_old");
        }

        if (oldVersion < 24) {
            db.execSQL("ALTER TABLE templates add column usages integer default 0");
        }

        if (oldVersion < 25) {
            //for transactions that were not transfers, transfer_peer was set to null in transactions, but to 0 in templates
            db.execSQL("update transactions set transfer_peer=0 WHERE transfer_peer is null;");
        }

        if (oldVersion < 26) {
            db.execSQL("alter table accounts add column color integer default -6697984");
        }

        if (oldVersion < 27) {
            db.execSQL("CREATE TABLE feature_used (feature text not null);");
        }

        if (oldVersion < 28) {
            db.execSQL("ALTER TABLE transactions RENAME to transactions_old");
            db.execSQL(
                    "CREATE TABLE transactions(_id integer primary key autoincrement, comment text, date datetime not null, amount integer not null, "
                            + "cat_id integer references categories(_id), account_id integer not null references accounts(_id),payee text, "
                            + "transfer_peer integer references transactions(_id), transfer_account integer references accounts(_id), "
                            + "method_id integer references paymentmethods(_id));");
            db.execSQL(
                    "INSERT INTO transactions (_id,comment,date,amount,cat_id,account_id,payee,transfer_peer,transfer_account,method_id) "
                            + "SELECT _id,comment,date,amount, "
                            + "CASE WHEN transfer_peer THEN null ELSE CASE WHEN cat_id THEN cat_id ELSE null END END, "
                            + "account_id,payee, "
                            + "CASE WHEN transfer_peer THEN transfer_peer ELSE null END, "
                            + "CASE WHEN transfer_peer THEN cat_id ELSE null END, "
                            + "CASE WHEN payment_method_id THEN payment_method_id ELSE null END "
                            + "FROM transactions_old");
            db.execSQL("ALTER TABLE accounts RENAME to accounts_old");
            db.execSQL(
                    "CREATE TABLE accounts (_id integer primary key autoincrement, label text not null, opening_balance integer, description text, "
                            + "currency text not null, type text not null check (type in ('CASH','BANK','CCARD','ASSET','LIABILITY')) default 'CASH', color integer default -3355444);");
            db.execSQL("INSERT INTO accounts (_id,label,opening_balance,description,currency,type,color) "
                    + "SELECT _id,label,opening_balance,description,currency,type,color FROM accounts_old");
            //previously templates where not deleted if referred to accounts were deleted
            db.execSQL(
                    "DELETE FROM templates where account_id not in (SELECT _id FROM accounts) or (cat_id != 0 and transfer_peer = 1 and cat_id not in (SELECT _id from accounts))");
            db.execSQL("ALTER TABLE templates RENAME to templates_old");
            db.execSQL(
                    "CREATE TABLE templates ( _id integer primary key autoincrement, comment text not null, amount integer not null, "
                            + "cat_id integer references categories(_id), account_id integer not null references accounts(_id),payee text, "
                            + "transfer_peer boolean default false, transfer_account integer references accounts(_id),method_id integer references paymentmethods(_id), "
                            + "title text not null, usages integer default 0, unique(account_id,title));");
            db.execSQL(
                    "INSERT INTO templates (_id,comment,amount,cat_id,account_id,payee,transfer_peer,transfer_account,method_id,title,usages) "
                            + "SELECT _id,comment,amount,"
                            + "CASE WHEN transfer_peer THEN null ELSE CASE WHEN cat_id THEN cat_id ELSE null END END, "
                            + "account_id,payee, " + "CASE WHEN transfer_peer THEN 1 ELSE 0 END, "
                            + "CASE WHEN transfer_peer THEN cat_id ELSE null END, "
                            + "CASE WHEN payment_method_id THEN payment_method_id ELSE null END, "
                            + "title,usages FROM templates_old");
            db.execSQL("ALTER TABLE categories RENAME to categories_old");
            db.execSQL(
                    "CREATE TABLE categories (_id integer primary key autoincrement, label text not null, parent_id integer references categories(_id), "
                            + "usages integer default 0, unique (label,parent_id));");
            db.execSQL("INSERT INTO categories (_id,label,parent_id,usages) "
                    + "SELECT _id,label,CASE WHEN parent_id THEN parent_id ELSE null END,usages FROM categories_old");
            db.execSQL("ALTER TABLE paymentmethods RENAME to paymentmethods_old");
            db.execSQL(
                    "CREATE TABLE paymentmethods (_id integer primary key autoincrement, label text not null, type integer check (type in (-1,0,1)) default 0);");
            db.execSQL(
                    "INSERT INTO paymentmethods (_id,label,type) SELECT _id,label,type FROM paymentmethods_old");
            db.execSQL("ALTER TABLE accounttype_paymentmethod RENAME to accounttype_paymentmethod_old");
            db.execSQL(
                    "CREATE TABLE accounttype_paymentmethod (type text not null check (type in ('CASH','BANK','CCARD','ASSET','LIABILITY')), method_id integer references paymentmethods (_id), primary key (type,method_id));");
            db.execSQL(
                    "INSERT INTO accounttype_paymentmethod (type,method_id) SELECT type,method_id FROM accounttype_paymentmethod_old");
            db.execSQL("DROP TABLE transactions_old");
            db.execSQL("DROP TABLE accounts_old");
            db.execSQL("DROP TABLE templates_old");
            db.execSQL("DROP TABLE categories_old");
            db.execSQL("DROP TABLE paymentmethods_old");
            db.execSQL("DROP TABLE accounttype_paymentmethod_old");
            //Changes to handle
            //1) Transfer account no longer stored as cat_id but in transfer_account (in transactions and templates)
            //2) parent_id for categories uses foreign key on itself, hence root categories have null instead of 0 as parent_id
            //3) catId etc now need to be null instead of 0
            //4) transactions payment_method_id renamed to method_id
        }

        if (oldVersion < 29) {
            db.execSQL("ALTER TABLE transactions add column status integer default 0");
        }

        if (oldVersion < 30) {
            db.execSQL("ALTER TABLE transactions add column parent_id integer references transactions (_id)");
            //      db.execSQL("CREATE VIEW committed AS SELECT * FROM transactions WHERE status != 2;");
            //      db.execSQL("CREATE VIEW uncommitted AS SELECT * FROM transactions WHERE status = 2;");
            ContentValues initialValues = new ContentValues();
            initialValues.put("_id", 0);
            initialValues.put("parent_id", 0);
            initialValues.put("label", "__SPLIT_TRANSACTION__");
            db.insert("categories", null, initialValues);
        }

        if (oldVersion < 31) {
            //in an alpha version distributed on Google Play, we had SPLIT_CATID as -1
            ContentValues initialValues = new ContentValues();
            initialValues.put("_id", 0);
            initialValues.put("parent_id", 0);
            db.update("categories", initialValues, "_id=-1", null);
        }

        if (oldVersion < 32) {
            db.execSQL("ALTER TABLE accounts add column grouping text not null check (grouping in "
                    + "('NONE','DAY','WEEK','MONTH','YEAR')) default 'NONE'");
        }

        if (oldVersion < 33) {
            db.execSQL("ALTER TABLE accounts add column usages integer default 0");
            db.execSQL(
                    "UPDATE accounts SET usages = (SELECT count(*) FROM transactions WHERE account_id = accounts._id AND parent_id IS null)");
        }

        if (oldVersion < 34) {
            //fix for https://github.com/mtotschnig/MyExpenses/issues/69
            db.execSQL(
                    "UPDATE transactions set date = (SELECT date from transactions parent WHERE parent._id = transactions.parent_id) WHERE parent_id IS NOT null");
        }

        if (oldVersion < 35) {
            db.execSQL(
                    "ALTER TABLE transactions add column cr_status text not null check (cr_status in ('UNRECONCILED','CLEARED','RECONCILED')) default 'UNRECONCILED'");
        }

        if (oldVersion < 36) {
            //move payee field in transactions from text to foreign key
            db.execSQL("ALTER TABLE transactions RENAME to transactions_old");
            db.execSQL("CREATE TABLE transactions (" + " _id integer primary key autoincrement,"
                    + " comment text, date datetime not null," + " amount integer not null,"
                    + " cat_id integer references categories(_id),"
                    + " account_id integer not null references accounts(_id),"
                    + " payee_id integer references payee(_id),"
                    + " transfer_peer integer references transactions(_id),"
                    + " transfer_account integer references accounts(_id),"
                    + " method_id integer references paymentmethods(_id),"
                    + " parent_id integer references transactions(_id)," + " status integer default 0,"
                    + " cr_status text not null check (cr_status in ('UNRECONCILED','CLEARED','RECONCILED')) default 'RECONCILED')");
            //insert all payees that are stored in transactions, but are not in payee
            db.execSQL(
                    "INSERT INTO payee (name) SELECT DISTINCT payee FROM transactions_old WHERE payee != '' AND NOT exists (SELECT 1 FROM payee WHERE name=transactions_old.payee)");
            db.execSQL("INSERT INTO transactions "
                    + "(_id,comment,date,amount,cat_id,account_id,payee_id,transfer_peer,transfer_account,method_id,parent_id,status,cr_status) "
                    + "SELECT " + "_id, " + "comment, " + "date, " + "amount, " + "cat_id, " + "account_id, "
                    + "(SELECT _id from payee WHERE name = payee), " + "transfer_peer, " + "transfer_account, "
                    + "method_id," + "parent_id," + "status," + "cr_status " + "FROM transactions_old");
            db.execSQL("DROP TABLE transactions_old");

            //move payee field in templates from text to foreign key
            db.execSQL("ALTER TABLE templates RENAME to templates_old");
            db.execSQL("CREATE TABLE templates (" + " _id integer primary key autoincrement," + " comment text,"
                    + " amount integer not null," + " cat_id integer references categories(_id),"
                    + " account_id integer not null references accounts(_id),"
                    + " payee_id integer references payee(_id)," + " transfer_peer boolean default false,"
                    + " transfer_account integer references accounts(_id),"
                    + " method_id integer references paymentmethods(_id)," + " title text not null,"
                    + " usages integer default 0," + " unique(account_id,title));");
            //insert all payees that are stored in templates, but are not in payee
            db.execSQL(
                    "INSERT INTO payee (name) SELECT DISTINCT payee FROM templates_old WHERE payee != '' AND NOT exists (SELECT 1 FROM payee WHERE name=templates_old.payee)");
            db.execSQL("INSERT INTO templates "
                    + "(_id,comment,amount,cat_id,account_id,payee_id,transfer_peer,transfer_account,method_id,title,usages) "
                    + "SELECT " + "_id, " + "comment, " + "amount, " + "cat_id, " + "account_id, "
                    + "(SELECT _id from payee WHERE name = payee), " + "transfer_peer, " + "transfer_account, "
                    + "method_id," + "title," + "usages " + "FROM templates_old");
            db.execSQL("DROP TABLE templates_old");

            db.execSQL("DROP VIEW IF EXISTS committed");
            db.execSQL("DROP VIEW IF EXISTS uncommitted");
            //for the definition of the view, it is safe to rely on the constants,
            //since we will not alter the view, but drop it, and recreate it, if needed
            //      String viewTransactions = VIEW_DEFINITION(TABLE_TRANSACTIONS);
            //      db.execSQL("CREATE VIEW transactions_committed "  + viewTransactions + " WHERE " + KEY_STATUS + " != " + STATUS_UNCOMMITTED + ";");
            //      db.execSQL("CREATE VIEW transactions_uncommitted" + viewTransactions + " WHERE " + KEY_STATUS +  " = " + STATUS_UNCOMMITTED + ";");
            //      db.execSQL("CREATE VIEW transactions_all" + viewTransactions);
            //      db.execSQL("CREATE VIEW templates_all" +  VIEW_DEFINITION(TABLE_TEMPLATES));
        }

        if (oldVersion < 37) {
            db.execSQL("ALTER TABLE transactions add column number text");
            db.execSQL("ALTER TABLE paymentmethods add column is_numbered boolean default 0");
            ContentValues initialValues = new ContentValues();
            initialValues.put("is_numbered", true);
            db.update("paymentmethods", initialValues, "label = ?", new String[] { "CHEQUE" });
        }

        if (oldVersion < 38) {
            db.execSQL("ALTER TABLE templates add column plan_id integer");
            db.execSQL("ALTER TABLE templates add column plan_execution boolean default 0");
        }

        if (oldVersion < 39) {
            //      db.execSQL("CREATE VIEW transactions_extended" + VIEW_DEFINITION_EXTENDED(TABLE_TRANSACTIONS) + " WHERE " + KEY_STATUS + " != " + STATUS_UNCOMMITTED + ";");
            //      db.execSQL("CREATE VIEW templates_extended" +  VIEW_DEFINITION_EXTENDED(TABLE_TEMPLATES));
            db.execSQL(
                    "CREATE TABLE currency (_id integer primary key autoincrement, code text unique not null);");
            insertCurrencies(db);
        }

        if (oldVersion < 40) {
            //added currency to extended view
            db.execSQL("DROP VIEW IF EXISTS transactions_extended");
            db.execSQL("DROP VIEW IF EXISTS templates_extended");
            //      db.execSQL("CREATE VIEW transactions_extended" + VIEW_DEFINITION_EXTENDED(TABLE_TRANSACTIONS) + " WHERE " + KEY_STATUS + " != " + STATUS_UNCOMMITTED + ";");
            //      db.execSQL("CREATE VIEW templates_extended" +  VIEW_DEFINITION_EXTENDED(TABLE_TEMPLATES));
        }

        if (oldVersion < 41) {
            db.execSQL("CREATE TABLE planinstance_transaction "
                    + "(template_id integer references templates(_id), " + "instance_id integer, "
                    + "transaction_id integer references transactions(_id), "
                    + "primary key (instance_id,transaction_id));");
        }

        if (oldVersion < 42) {
            //migrate date field to unix time stamp (UTC)
            db.execSQL("ALTER TABLE transactions RENAME to transactions_old");
            db.execSQL("CREATE TABLE transactions (" + " _id integer primary key autoincrement,"
                    + " comment text, date datetime not null," + " amount integer not null,"
                    + " cat_id integer references categories(_id),"
                    + " account_id integer not null references accounts(_id),"
                    + " payee_id integer references payee(_id),"
                    + " transfer_peer integer references transactions(_id),"
                    + " transfer_account integer references accounts(_id),"
                    + " method_id integer references paymentmethods(_id),"
                    + " parent_id integer references transactions(_id)," + " status integer default 0,"
                    + " cr_status text not null check (cr_status in ('UNRECONCILED','CLEARED','RECONCILED')) default 'RECONCILED',"
                    + " number text)");
            db.execSQL("INSERT INTO transactions "
                    + "(_id,comment,date,amount,cat_id,account_id,payee_id,transfer_peer,transfer_account,method_id,parent_id,status,cr_status,number) "
                    + "SELECT " + "_id, " + "comment, " + "strftime('%s',date,'utc'), " + "amount, "
                    + "cat_id, " + "account_id, " + "payee_id, " + "transfer_peer, " + "transfer_account, "
                    + "method_id," + "parent_id," + "status," + "cr_status, " + "number "
                    + "FROM transactions_old");
            db.execSQL("DROP TABLE transactions_old");
        }

        if (oldVersion < 43) {
            db.execSQL("UPDATE accounts set currency = 'ZMW' WHERE currency = 'ZMK'");
            db.execSQL("UPDATE currency set code = 'ZMW' WHERE code = 'ZMK'");
        }

        if (oldVersion < 44) {
            //add ON DELETE CASCADE
            //accounts table sort_key column
            db.execSQL("ALTER TABLE planinstance_transaction RENAME to planinstance_transaction_old");
            db.execSQL("CREATE TABLE planinstance_transaction "
                    + "(template_id integer references templates(_id) ON DELETE CASCADE, "
                    + "instance_id integer, "
                    + "transaction_id integer references transactions(_id) ON DELETE CASCADE, "
                    + "primary key (instance_id,transaction_id));");
            db.execSQL("INSERT INTO planinstance_transaction " + "(template_id,instance_id,transaction_id)"
                    + "SELECT " + "template_id,instance_id,transaction_id FROM planinstance_transaction_old");
            db.execSQL("DROP TABLE planinstance_transaction_old");
            db.execSQL("ALTER TABLE transactions RENAME to transactions_old");
            db.execSQL("CREATE TABLE transactions (" + " _id integer primary key autoincrement,"
                    + " comment text, date datetime not null," + " amount integer not null,"
                    + " cat_id integer references categories(_id),"
                    + " account_id integer not null references accounts(_id) ON DELETE CASCADE,"
                    + " payee_id integer references payee(_id),"
                    + " transfer_peer integer references transactions(_id),"
                    + " transfer_account integer references accounts(_id),"
                    + " method_id integer references paymentmethods(_id),"
                    + " parent_id integer references transactions(_id) ON DELETE CASCADE,"
                    + " status integer default 0,"
                    + " cr_status text not null check (cr_status in ('UNRECONCILED','CLEARED','RECONCILED')) default 'RECONCILED',"
                    + " number text)");
            db.execSQL("INSERT INTO transactions "
                    + "(_id,comment,date,amount,cat_id,account_id,payee_id,transfer_peer,transfer_account,method_id,parent_id,status,cr_status,number) "
                    + "SELECT " + "_id, " + "comment, " + "date, " + "amount, " + "cat_id, " + "account_id, "
                    + "payee_id, " + "transfer_peer, " + "transfer_account, " + "method_id," + "parent_id,"
                    + "status," + "cr_status, " + "number " + "FROM transactions_old");
            db.execSQL("DROP TABLE transactions_old");
            db.execSQL("ALTER TABLE templates RENAME to templates_old");
            db.execSQL("CREATE TABLE templates (" + " _id integer primary key autoincrement," + " comment text,"
                    + " amount integer not null," + " cat_id integer references categories(_id),"
                    + " account_id integer not null references accounts(_id) ON DELETE CASCADE,"
                    + " payee_id integer references payee(_id)," + " transfer_peer boolean default 0,"
                    + " transfer_account integer references accounts(_id) ON DELETE CASCADE,"
                    + " method_id integer references paymentmethods(_id)," + " title text not null,"
                    + " usages integer default 0," + " plan_id integer, "
                    + " plan_execution boolean default 0, " + " unique(account_id,title));");
            db.execSQL("INSERT INTO templates "
                    + "(_id,comment,amount,cat_id,account_id,payee_id,transfer_peer,transfer_account,method_id,title,usages,plan_id,plan_execution) "
                    + "SELECT " + "_id, " + "comment, " + "amount, " + "cat_id, " + "account_id, "
                    + "payee_id, " + "transfer_peer, " + "transfer_account, " + "method_id," + "title,"
                    + "usages, " + "plan_id, " + "plan_execution " + "FROM templates_old");
            db.execSQL("ALTER TABLE accounts add column sort_key integer");
        }

        if (oldVersion < 45) {
            db.execSQL("ALTER TABLE accounts add column exclude_from_totals boolean default 0");
            //added  to extended view
            db.execSQL("DROP VIEW IF EXISTS transactions_extended");
            db.execSQL("DROP VIEW IF EXISTS templates_extended");
            //      db.execSQL("CREATE VIEW transactions_extended" + VIEW_DEFINITION_EXTENDED(TABLE_TRANSACTIONS) + " WHERE " + KEY_STATUS + " != " + STATUS_UNCOMMITTED + ";");
            //      db.execSQL("CREATE VIEW templates_extended" +  VIEW_DEFINITION_EXTENDED(TABLE_TEMPLATES));
        }

        if (oldVersion < 46) {
            db.execSQL("ALTER TABLE payee add column name_normalized text");
            Cursor c = db.query("payee", new String[] { "_id", "name" }, null, null, null, null, null);
            if (c != null) {
                if (c.moveToFirst()) {
                    ContentValues v = new ContentValues();
                    while (c.getPosition() < c.getCount()) {
                        v.put("name_normalized", Utils.normalize(c.getString(1)));
                        db.update("payee", v, "_id = " + c.getLong(0), null);
                        c.moveToNext();
                    }
                }
                c.close();
            }
        }

        if (oldVersion < 47) {
            db.execSQL("ALTER TABLE templates add column uuid text");
            db.execSQL(EVENT_CACHE_CREATE);
        }

        if (oldVersion < 48) {
            //added method_label to extended view
            //do not comment out, since it is needed by the uuid update
            refreshViews(db);
            //need to inline to protect against later renames

            if (oldVersion < 47) {
                String[] projection = new String[] { "templates._id", "amount", "comment", "cat_id",
                        "CASE WHEN " + "  " + "transfer_peer" + " " + " THEN " + "  (SELECT " + "label"
                                + " FROM " + "accounts" + " WHERE " + "_id" + " = " + "transfer_account" + ") "
                                + " ELSE " + " CASE WHEN " + " (SELECT " + "parent_id" + " FROM " + "categories"
                                + " WHERE " + "_id" + " = " + "cat_id" + ") " + " THEN " + " (SELECT " + "label"
                                + " FROM " + "categories" + " WHERE " + "_id" + " = " + " (SELECT "
                                + "parent_id" + " FROM " + "categories" + " WHERE " + "_id" + " = " + "cat_id"
                                + ")) " + "  || ' : ' || " + " (SELECT " + "label" + " FROM " + "categories"
                                + " WHERE " + "_id" + " = " + "cat_id" + ") " + " ELSE" + " (SELECT " + "label"
                                + " FROM " + "categories" + " WHERE " + "_id" + " = " + "cat_id" + ") "
                                + " END " + " END AS  " + "label",
                        "name", "transfer_peer", "transfer_account", "account_id", "method_id",
                        "paymentmethods.label AS method_label", "title", "plan_id", "plan_execution", "uuid",
                        "currency" };
                SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
                qb.setTables("templates LEFT JOIN payee ON payee_id = payee._id"
                        + " LEFT JOIN accounts ON account_id = accounts._id"
                        + " LEFT JOIN paymentmethods ON method_id = paymentmethods._id");
                Cursor c = qb.query(db, projection, null, null, null, null, null);
                if (c != null) {
                    if (c.moveToFirst()) {
                        ContentValues templateValues = new ContentValues(), eventValues = new ContentValues();
                        String planCalendarId = MyApplication.getInstance().checkPlanner();
                        while (c.getPosition() < c.getCount()) {
                            Template t = new Template(c);
                            templateValues.put(DatabaseConstants.KEY_UUID, t.getUuid());
                            long templateId = c.getLong(c.getColumnIndex("_id"));
                            long planId = c.getLong(c.getColumnIndex("plan_id"));
                            eventValues.put(Events.DESCRIPTION, t.compileDescription(mCtx));
                            db.update("templates", templateValues, "_id = " + templateId, null);
                            if (Build.VERSION.SDK_INT >= Build.VERSION_CODES.ICE_CREAM_SANDWICH) {
                                try {
                                    mCtx.getContentResolver().update(Events.CONTENT_URI, eventValues,
                                            Events._ID + "= ? AND " + Events.CALENDAR_ID + " = ?",
                                            new String[] { String.valueOf(planId), planCalendarId });
                                } catch (Exception e) {
                                    //fails with IllegalArgumentException on 2.x devices,
                                    //since the same uri works for inserting and querying
                                    //but also on HUAWEI Y530-U00 with 4.3
                                    //probably SecurityException could arise here
                                }
                            }
                            c.moveToNext();
                        }
                    }
                    c.close();
                }
            }
        }

        if (oldVersion < 49) {
            //forgotten to drop in previous upgrade
            db.execSQL("DROP TABLE IF EXISTS templates_old");
        }

        if (oldVersion < 50) {
            db.execSQL("ALTER TABLE transactions add column picture_id text");
            db.execSQL("DROP TABLE IF EXISTS feature_used");
        }

        if (oldVersion < 51) {
            File pictureDir = Utils.getPictureDir(false);
            //fallback if not mounted
            if (pictureDir == null) {
                pictureDir = new File(
                        Environment.getExternalStorageDirectory().getPath() + "/Android/data/"
                                + MyApplication.getInstance().getPackageName() + "/files",
                        Environment.DIRECTORY_PICTURES);
            }
            if (!pictureDir.exists()) {
                AcraHelper.report(new Exception("Unable to calculate pictureDir during upgrade"));
            }
            //if pictureDir does not exist, we use its URI nonetheless, in order to have the data around
            //for potential trouble handling
            String prefix = Uri.fromFile(pictureDir).toString() + "/";
            String postfix = ".jpg";
            //if picture_id concat expression will also be null
            db.execSQL("UPDATE transactions set picture_id = '" + prefix + "'||picture_id||'" + postfix + "'");

            db.execSQL("CREATE TABLE stale_uris ( picture_id text);");
            db.execSQL(
                    "CREATE TRIGGER cache_stale_uri BEFORE DELETE ON transactions WHEN old.picture_id NOT NULL "
                            + " BEGIN INSERT INTO stale_uris VALUES (old.picture_id); END");
        }

        if (oldVersion < 52) {
            db.execSQL("CREATE INDEX transactions_cat_id_index on transactions(cat_id)");
            db.execSQL("CREATE INDEX templates_cat_id_index on templates(cat_id)");
        }

        if (oldVersion < 53) {
            //add VOID status
            db.execSQL("ALTER TABLE transactions RENAME to transactions_old");
            db.execSQL("CREATE TABLE " + "transactions" + "( " + "_id" + " integer primary key autoincrement, "
                    + "comment" + " text, " + "date" + " datetime not null, " + "amount" + " integer not null, "
                    + "cat_id" + " integer references " + "categories" + "(" + "_id" + "), " + "account_id"
                    + " integer not null references " + "accounts" + "(" + "_id" + ") ON DELETE CASCADE,"
                    + "payee_id" + " integer references " + "payee" + "(" + "_id" + "), " + "transfer_peer"
                    + " integer references " + "transactions" + "(" + "_id" + "), " + "transfer_account"
                    + " integer references " + "accounts" + "(" + "_id" + ")," + "method_id"
                    + " integer references " + "paymentmethods" + "(" + "_id" + ")," + "parent_id"
                    + " integer references " + "transactions" + "(" + "_id" + ") ON DELETE CASCADE, " + "status"
                    + " integer default 0, " + "cr_status" + " text not null check (" + "cr_status"
                    + " in ('UNRECONCILED','CLEARED','RECONCILED','VOID')) default 'RECONCILED', " + "number"
                    + " text, " + "picture_id" + " text);");
            db.execSQL("INSERT INTO transactions "
                    + "(_id,comment,date,amount,cat_id,account_id,payee_id,transfer_peer,transfer_account,method_id,parent_id,status,cr_status,number,picture_id) "
                    + "SELECT " + "_id, " + "comment, " + "date, " + "amount, " + "cat_id, " + "account_id, "
                    + "payee_id, " + "transfer_peer, " + "transfer_account, " + "method_id," + "parent_id,"
                    + "status," + "cr_status, " + "number, " + "picture_id " + "FROM transactions_old");
            db.execSQL("DROP TABLE transactions_old");
            db.execSQL(
                    "CREATE TRIGGER cache_stale_uri BEFORE DELETE ON transactions WHEN old.picture_id NOT NULL "
                            + " BEGIN INSERT INTO stale_uris VALUES (old.picture_id); END");
            db.execSQL("CREATE INDEX transactions_cat_id_index on transactions(cat_id)");
        }

        if (oldVersion < 54) {
            db.execSQL("DROP TRIGGER cache_stale_uri");
            db.execSQL("CREATE TRIGGER cache_stale_uri " + "AFTER DELETE ON " + "transactions" + " "
                    + "WHEN old." + "picture_id" + " NOT NULL " + "AND NOT EXISTS " + "(SELECT 1 FROM "
                    + "transactions" + " " + "WHERE " + "picture_id" + " = old." + "picture_id" + ") "
                    + "BEGIN INSERT INTO " + "stale_uris" + " VALUES (old." + "picture_id" + "); END");
            //all Accounts with old default color are updated to the new one
            db.execSQL(String.format(Locale.US, "UPDATE accounts set color = %d WHERE color = %d", 0xff009688,
                    0xff99CC00));
        }

        if (oldVersion < 55) {
            db.execSQL("ALTER TABLE categories add column label_normalized text");
            Cursor c = db.query("categories", new String[] { "_id", "label" }, null, null, null, null, null);
            if (c != null) {
                if (c.moveToFirst()) {
                    ContentValues v = new ContentValues();
                    while (c.getPosition() < c.getCount()) {
                        v.put("label_normalized", Utils.normalize(c.getString(1)));
                        db.update("categories", v, "_id = " + c.getLong(0), null);
                        c.moveToNext();
                    }
                }
                c.close();
            }
        }

        if (oldVersion < 56) {
            db.execSQL("ALTER TABLE templates add column last_used datetime");
            db.execSQL("ALTER TABLE categories add column last_used datetime");
            db.execSQL("ALTER TABLE accounts add column last_used datetime");
            db.execSQL("CREATE TRIGGER sort_key_default AFTER INSERT ON accounts "
                    + "BEGIN UPDATE accounts SET sort_key = (SELECT coalesce(max(sort_key),0) FROM accounts) + 1 "
                    + "WHERE _id = NEW._id; END");
            //The sort key could be set by user in previous versions, now it is handled internally
            Cursor c = db.query("accounts", new String[] { "_id", "sort_key" }, null, null, null, null,
                    "sort_key ASC");
            boolean hasAccountSortKeySet = false;
            if (c != null) {
                if (c.moveToFirst()) {
                    ContentValues v = new ContentValues();
                    while (c.getPosition() < c.getCount()) {
                        v.put("sort_key", c.getPosition() + 1);
                        db.update("accounts", v, "_id = ?", new String[] { c.getString(0) });
                        if (c.getInt(1) != 0)
                            hasAccountSortKeySet = true;
                        c.moveToNext();
                    }
                }
                c.close();
            }
            String legacy = PrefKey.SORT_ORDER_LEGACY.getString("USAGES");
            PrefKey.SORT_ORDER_TEMPLATES.putString(legacy);
            PrefKey.SORT_ORDER_CATEGORIES.putString(legacy);
            PrefKey.SORT_ORDER_ACCOUNTS.putString(hasAccountSortKeySet ? "CUSTOM" : legacy);
            PrefKey.SORT_ORDER_LEGACY.remove();
        }
    } catch (SQLException e) {
        throw Utils.hasApiLevel(Build.VERSION_CODES.JELLY_BEAN)
                ? new SQLiteUpgradeFailedException("Database upgrade failed", e)
                : e;
    }

    if (oldVersion < 57) {
        //fix custom app uris
        if (ContextCompat.checkSelfPermission(mCtx,
                Manifest.permission.WRITE_CALENDAR) == PackageManager.PERMISSION_GRANTED) {
            Cursor c = db.query("templates", new String[] { "_id", "plan_id" }, "plan_id IS NOT null", null,
                    null, null, null);
            if (c != null) {
                if (c.moveToFirst()) {
                    while (!c.isAfterLast()) {
                        Plan.updateCustomAppUri(c.getLong(1), Template.buildCustomAppUri(c.getLong(0)));
                        c.moveToNext();
                    }
                }
                c.close();
            }
        }

        //Drop unique constraint on templates

        db.execSQL("ALTER TABLE templates RENAME to templates_old");
        db.execSQL("CREATE TABLE templates (" + " _id integer primary key autoincrement," + " comment text,"
                + " amount integer not null," + " cat_id integer references categories(_id),"
                + " account_id integer not null references accounts(_id) ON DELETE CASCADE,"
                + " payee_id integer references payee(_id)," + " transfer_peer boolean default 0,"
                + " transfer_account integer references accounts(_id) ON DELETE CASCADE,"
                + " method_id integer references paymentmethods(_id)," + " title text not null,"
                + " usages integer default 0," + " plan_id integer, " + " plan_execution boolean default 0, "
                + " uuid text, " + " last_used datetime);");
        db.execSQL("INSERT INTO templates "
                + "(_id,comment,amount,cat_id,account_id,payee_id,transfer_peer,transfer_account,method_id,title,usages,plan_id,plan_execution,uuid,last_used) "
                + "SELECT " + "_id, " + "comment, " + "amount, " + "cat_id, " + "account_id, " + "payee_id, "
                + "transfer_peer, " + "transfer_account, " + "method_id," + "title," + "usages, " + "plan_id, "
                + "plan_execution, uuid, last_used " + "FROM templates_old");
        db.execSQL("DROP TABLE templates_old");
        //Recreate changed views
        refreshViews(db);
    }

    if (oldVersion < 58) {
        //cache fraction digits
        Cursor c = db.rawQuery("SELECT distinct currency from accounts", null);
        if (c != null) {
            if (c.moveToFirst()) {
                while (!c.isAfterLast()) {
                    Money.ensureFractionDigitsAreCached(Utils.getSaveInstance(c.getString(0)));
                    c.moveToNext();
                }
            }
            c.close();
        }
    }
}

From source file:biz.shadowservices.DegreesToolbox.DataFetcher.java

public FetchResult updateData(Context context, boolean force) {
    //Open database
    DBOpenHelper dbhelper = new DBOpenHelper(context);
    SQLiteDatabase db = dbhelper.getWritableDatabase();

    // check for internet connectivity
    try {/*from  w w w  .j  a  va2 s  .  com*/
        if (!isOnline(context)) {
            Log.d(TAG, "We do not seem to be online. Skipping Update.");
            return FetchResult.NOTONLINE;
        }
    } catch (Exception e) {
        exceptionReporter.reportException(Thread.currentThread(), e, "Exception during isOnline()");
    }
    SharedPreferences sp = PreferenceManager.getDefaultSharedPreferences(context);
    if (!force) {
        try {
            if (sp.getBoolean("loginFailed", false) == true) {
                Log.d(TAG, "Previous login failed. Skipping Update.");
                DBLog.insertMessage(context, "i", TAG, "Previous login failed. Skipping Update.");
                return FetchResult.LOGINFAILED;
            }
            if (sp.getBoolean("autoupdates", true) == false) {
                Log.d(TAG, "Automatic updates not enabled. Skipping Update.");
                DBLog.insertMessage(context, "i", TAG, "Automatic updates not enabled. Skipping Update.");
                return FetchResult.NOTALLOWED;
            }
            if (!isBackgroundDataEnabled(context) && sp.getBoolean("obeyBackgroundData", true)) {
                Log.d(TAG, "Background data not enabled. Skipping Update.");
                DBLog.insertMessage(context, "i", TAG, "Background data not enabled. Skipping Update.");
                return FetchResult.NOTALLOWED;
            }
            if (!isAutoSyncEnabled() && sp.getBoolean("obeyAutoSync", true)
                    && sp.getBoolean("obeyBackgroundData", true)) {
                Log.d(TAG, "Auto sync not enabled. Skipping Update.");
                DBLog.insertMessage(context, "i", TAG, "Auto sync not enabled. Skipping Update.");
                return FetchResult.NOTALLOWED;
            }
            if (isWifi(context) && !sp.getBoolean("wifiUpdates", true)) {
                Log.d(TAG, "On wifi, and wifi auto updates not allowed. Skipping Update");
                DBLog.insertMessage(context, "i", TAG,
                        "On wifi, and wifi auto updates not allowed. Skipping Update");
                return FetchResult.NOTALLOWED;
            } else if (!isWifi(context)) {
                Log.d(TAG, "We are not on wifi.");
                if (!isRoaming(context) && !sp.getBoolean("2DData", true)) {
                    Log.d(TAG, "Automatic updates on 2Degrees data not enabled. Skipping Update.");
                    DBLog.insertMessage(context, "i", TAG,
                            "Automatic updates on 2Degrees data not enabled. Skipping Update.");
                    return FetchResult.NOTALLOWED;
                } else if (isRoaming(context) && !sp.getBoolean("roamingData", false)) {
                    Log.d(TAG, "Automatic updates on roaming mobile data not enabled. Skipping Update.");
                    DBLog.insertMessage(context, "i", TAG,
                            "Automatic updates on roaming mobile data not enabled. Skipping Update.");
                    return FetchResult.NOTALLOWED;
                }

            }
        } catch (Exception e) {
            exceptionReporter.reportException(Thread.currentThread(), e,
                    "Exception while finding if to update.");
        }

    } else {
        Log.d(TAG, "Update Forced");
    }

    try {
        String username = sp.getString("username", null);
        String password = sp.getString("password", null);
        if (username == null || password == null) {
            DBLog.insertMessage(context, "i", TAG, "Username or password not set.");
            return FetchResult.USERNAMEPASSWORDNOTSET;
        }

        // Find the URL of the page to send login data to.
        Log.d(TAG, "Finding Action. ");
        HttpGetter loginPageGet = new HttpGetter("https://secure.2degreesmobile.co.nz/web/ip/login");
        String loginPageString = loginPageGet.execute();
        if (loginPageString != null) {
            Document loginPage = Jsoup.parse(loginPageString,
                    "https://secure.2degreesmobile.co.nz/web/ip/login");
            Element loginForm = loginPage.getElementsByAttributeValue("name", "loginFrm").first();
            String loginAction = loginForm.attr("action");
            // Send login form
            List<NameValuePair> loginValues = new ArrayList<NameValuePair>();
            loginValues.add(new BasicNameValuePair("externalURLRedirect", ""));
            loginValues.add(new BasicNameValuePair("hdnAction", "login_userlogin"));
            loginValues.add(new BasicNameValuePair("hdnAuthenticationType", "M"));
            loginValues.add(new BasicNameValuePair("hdnlocale", ""));

            loginValues.add(new BasicNameValuePair("userid", username));
            loginValues.add(new BasicNameValuePair("password", password));
            Log.d(TAG, "Sending Login ");
            HttpPoster sendLoginPoster = new HttpPoster(loginAction, loginValues);
            // Parse result

            String loginResponse = sendLoginPoster.execute();
            Document loginResponseParsed = Jsoup.parse(loginResponse);
            // Determine if this is a pre-pay or post-paid account.
            boolean postPaid;
            if (loginResponseParsed
                    .getElementById("p_CustomerPortalPostPaidHomePage_WAR_customerportalhomepage") == null) {
                Log.d(TAG, "Pre-pay account or no account.");
                postPaid = false;
            } else {
                Log.d(TAG, "Post-paid account.");
                postPaid = true;
            }

            String homepageUrl = "https://secure.2degreesmobile.co.nz/group/ip/home";
            if (postPaid) {
                homepageUrl = "https://secure.2degreesmobile.co.nz/group/ip/postpaid";
            }
            HttpGetter homepageGetter = new HttpGetter(homepageUrl);
            String homepageHTML = homepageGetter.execute();
            Document homePage = Jsoup.parse(homepageHTML);

            Element accountSummary = homePage.getElementById("accountSummary");
            if (accountSummary == null) {
                Log.d(TAG, "Login failed.");
                return FetchResult.LOGINFAILED;
            }
            db.delete("cache", "", null);
            /* This code fetched some extra details for postpaid users, but on reflection they aren't that useful.
             * Might reconsider this.
             *
             if (postPaid) {
                     
               Element accountBalanceSummaryTable = accountSummary.getElementsByClass("tableBillSummary").first();
               Elements rows = accountBalanceSummaryTable.getElementsByTag("tr");
               int rowno = 0;
               for (Element row : rows) {
                  if (rowno > 1) {
             break;
                  }
                  //Log.d(TAG, "Starting row");
                  //Log.d(TAG, row.html());
                  Double value;
                  try {
             Element amount = row.getElementsByClass("tableBillamount").first();
             String amountHTML = amount.html();
             Log.d(TAG, amountHTML.substring(1));
             value = Double.parseDouble(amountHTML.substring(1));
                  } catch (Exception e) {
             Log.d(TAG, "Failed to parse amount from row.");
             value = null;
                  }
                  String expiresDetails = "";
                  String expiresDate = null;
                  String name = null;
                  try {
             Element details = row.getElementsByClass("tableBilldetail").first();
             name = details.ownText();
             Element expires = details.getElementsByTag("em").first();
             if (expires != null) {
                 expiresDetails = expires.text();
             } 
             Log.d(TAG, expiresDetails);
             Pattern pattern;
             pattern = Pattern.compile("\\(payment is due (.*)\\)");
             Matcher matcher = pattern.matcher(expiresDetails);
             if (matcher.find()) {
                /*Log.d(TAG, "matched expires");
                Log.d(TAG, "group 0:" + matcher.group(0));
                Log.d(TAG, "group 1:" + matcher.group(1));
                Log.d(TAG, "group 2:" + matcher.group(2)); *
                String expiresDateString = matcher.group(1);
                Date expiresDateObj;
                if (expiresDateString != null) {
                   if (expiresDateString.length() > 0) {
                      try {
                         expiresDateObj = DateFormatters.EXPIRESDATE.parse(expiresDateString);
                         expiresDate = DateFormatters.ISO8601DATEONLYFORMAT.format(expiresDateObj);
                      } catch (java.text.ParseException e) {
                         Log.d(TAG, "Could not parse date: " + expiresDateString);
                      }
                   }   
                }
             }
                  } catch (Exception e) {
             Log.d(TAG, "Failed to parse details from row.");
                  }
                  String expirev = null;
                  ContentValues values = new ContentValues();
                  values.put("name", name);
                  values.put("value", value);
                  values.put("units", "$NZ");
                  values.put("expires_value", expirev );
                  values.put("expires_date", expiresDate);
                  db.insert("cache", "value", values );
                  rowno++;
               }
            } */
            Element accountSummaryTable = accountSummary.getElementsByClass("tableAccountSummary").first();
            Elements rows = accountSummaryTable.getElementsByTag("tr");
            for (Element row : rows) {
                // We are now looking at each of the rows in the data table.
                //Log.d(TAG, "Starting row");
                //Log.d(TAG, row.html());
                Double value;
                String units;
                try {
                    Element amount = row.getElementsByClass("tableBillamount").first();
                    String amountHTML = amount.html();
                    //Log.d(TAG, amountHTML);
                    String[] amountParts = amountHTML.split("&nbsp;", 2);
                    //Log.d(TAG, amountParts[0]);
                    //Log.d(TAG, amountParts[1]);
                    if (amountParts[0].contains("Included") || amountParts[0].equals("All You Need")
                            || amountParts[0].equals("Unlimited Text*")) {
                        value = Values.INCLUDED;
                    } else {
                        try {
                            value = Double.parseDouble(amountParts[0]);
                        } catch (NumberFormatException e) {
                            exceptionReporter.reportException(Thread.currentThread(), e, "Decoding value.");
                            value = 0.0;
                        }
                    }
                    units = amountParts[1];
                } catch (NullPointerException e) {
                    //Log.d(TAG, "Failed to parse amount from row.");
                    value = null;
                    units = null;
                }
                Element details = row.getElementsByClass("tableBilldetail").first();
                String name = details.getElementsByTag("strong").first().text();
                Element expires = details.getElementsByTag("em").first();
                String expiresDetails = "";
                if (expires != null) {
                    expiresDetails = expires.text();
                }
                Log.d(TAG, expiresDetails);
                Pattern pattern;
                if (postPaid == false) {
                    pattern = Pattern.compile("\\(([\\d\\.]*) ?\\w*? ?expiring on (.*)\\)");
                } else {
                    pattern = Pattern.compile("\\(([\\d\\.]*) ?\\w*? ?will expire on (.*)\\)");
                }
                Matcher matcher = pattern.matcher(expiresDetails);
                Double expiresValue = null;
                String expiresDate = null;
                if (matcher.find()) {
                    /*Log.d(TAG, "matched expires");
                    Log.d(TAG, "group 0:" + matcher.group(0));
                    Log.d(TAG, "group 1:" + matcher.group(1));
                    Log.d(TAG, "group 2:" + matcher.group(2)); */
                    try {
                        expiresValue = Double.parseDouble(matcher.group(1));
                    } catch (NumberFormatException e) {
                        expiresValue = null;
                    }
                    String expiresDateString = matcher.group(2);
                    Date expiresDateObj;
                    if (expiresDateString != null) {
                        if (expiresDateString.length() > 0) {
                            try {
                                expiresDateObj = DateFormatters.EXPIRESDATE.parse(expiresDateString);
                                expiresDate = DateFormatters.ISO8601DATEONLYFORMAT.format(expiresDateObj);
                            } catch (java.text.ParseException e) {
                                Log.d(TAG, "Could not parse date: " + expiresDateString);
                            }
                        }
                    }
                }
                ContentValues values = new ContentValues();
                values.put("name", name);
                values.put("value", value);
                values.put("units", units);
                values.put("expires_value", expiresValue);
                values.put("expires_date", expiresDate);
                db.insert("cache", "value", values);
            }

            if (postPaid == false) {
                Log.d(TAG, "Getting Value packs...");
                // Find value packs
                HttpGetter valuePacksPageGet = new HttpGetter(
                        "https://secure.2degreesmobile.co.nz/group/ip/prevaluepack");
                String valuePacksPageString = valuePacksPageGet.execute();
                //DBLog.insertMessage(context, "d", "",  valuePacksPageString);
                if (valuePacksPageString != null) {
                    Document valuePacksPage = Jsoup.parse(valuePacksPageString);
                    Elements enabledPacks = valuePacksPage.getElementsByClass("yellow");
                    for (Element enabledPack : enabledPacks) {
                        Element offerNameElemt = enabledPack
                                .getElementsByAttributeValueStarting("name", "offername").first();
                        if (offerNameElemt != null) {
                            String offerName = offerNameElemt.val();
                            DBLog.insertMessage(context, "d", "", "Got element: " + offerName);
                            ValuePack[] packs = Values.valuePacks.get(offerName);
                            if (packs == null) {
                                DBLog.insertMessage(context, "d", "",
                                        "Offer name: " + offerName + " not matched.");
                            } else {
                                for (ValuePack pack : packs) {
                                    ContentValues values = new ContentValues();
                                    values.put("plan_startamount", pack.value);
                                    values.put("plan_name", offerName);
                                    DBLog.insertMessage(context, "d", "",
                                            "Pack " + pack.type.id + " start value set to " + pack.value);
                                    db.update("cache", values, "name = '" + pack.type.id + "'", null);
                                }
                            }
                        }
                    }
                }
            }

            SharedPreferences.Editor prefedit = sp.edit();
            Date now = new Date();
            prefedit.putString("updateDate", DateFormatters.ISO8601FORMAT.format(now));
            prefedit.putBoolean("loginFailed", false);
            prefedit.putBoolean("networkError", false);
            prefedit.commit();
            DBLog.insertMessage(context, "i", TAG, "Update Successful");
            return FetchResult.SUCCESS;

        }
    } catch (ClientProtocolException e) {
        DBLog.insertMessage(context, "w", TAG, "Network error: " + e.getMessage());
        return FetchResult.NETWORKERROR;
    } catch (IOException e) {
        DBLog.insertMessage(context, "w", TAG, "Network error: " + e.getMessage());
        return FetchResult.NETWORKERROR;
    } finally {
        db.close();
    }
    return null;
}