Java Code Examples for io.requery.android.database.sqlite.SQLiteDatabase#update()

public static void update29(SQLiteDatabase db) {
    String tableName = DBHelper.T_LOG_TRANSACTIONS;
    String columnName = DBHelper.C_LOG_TRANSACTIONS_COMMENT;
    db.execSQL("ALTER TABLE " + tableName + " ADD COLUMN SearchString TEXT;");
    db.execSQL("ALTER TABLE " + DBHelper.T_REF_SENDERS + " ADD COLUMN "+DBHelper.C_REF_SENDERS_ADD_CREDIT_LIMIT_TO_BALANCE+" INTEGER NOT NULL DEFAULT 0;");

    Cursor cursor = db.query(tableName, new String[]{DBHelper.C_ID, columnName}, "Deleted = 0", null, null, null, null);
    ContentValues cv = new ContentValues();
    try {
        if (cursor.moveToFirst()) {
            while (!cursor.isAfterLast()) {
                cv.clear();
                cv.put("SearchString", Translit.toTranslit(cursor.getString(1).toLowerCase()));
                db.update(tableName, cv, "_id = " + cursor.getString(0), null);
                cursor.moveToNext();
            }
        }
    } finally {
        cursor.close();
    }
}
 

public static void update31(SQLiteDatabase db, Context context) {
    db.execSQL("ALTER TABLE " + DBHelper.T_REF_PROJECTS + " ADD COLUMN "+DBHelper.C_REF_PROJECTS_COLOR+" TEXT DEFAULT '#ffffff';");

    Cursor cursorProjects = db.rawQuery("SELECT _id FROM ref_Projects", null);
    List<Long> projectIDs = new ArrayList<>();
    if (cursorProjects != null) {
        try {
            if (cursorProjects.moveToFirst()) {
                while (!cursorProjects.isAfterLast()) {
                    projectIDs.add(cursorProjects.getLong(0));
                    cursorProjects.moveToNext();
                }
            }
        } finally {
            cursorProjects.close();
        }
    }

    ContentValues cv = new ContentValues();
    for (long id : projectIDs) {
        cv.clear();
        cv.put(DBHelper.C_REF_PROJECTS_COLOR, String.format("#%06X", (0xFFFFFF & ColorUtils.getColor(context))));
        db.update(DBHelper.T_REF_PROJECTS, cv, String.format("_id = %s", String.valueOf(id)), null);
    }
}
 

public static void update23(SQLiteDatabase db) {
    ContentValues cv = new ContentValues();
    cv.put("OrderNumber", 0);
    db.execSQL("ALTER TABLE ref_Currencies RENAME TO ref_Currencies_old");
    db.execSQL("CREATE TABLE ref_Currencies (_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, FBID TEXT, TS INTEGER, Deleted INTEGER, Dirty INTEGER, LastEdited TEXT, Code TEXT NOT NULL, Symbol TEXT NOT NULL, Name TEXT NOT NULL, DecimalCount INTEGER NOT NULL, OrderNumber INTEGER, UNIQUE (Code, Deleted) ON CONFLICT ABORT);");
    db.execSQL(
            "INSERT INTO ref_Currencies (_id, FBID, TS, Deleted, Dirty, LastEdited, Code, Symbol, Name, DecimalCount) " +
            "SELECT _id, FBID, TS, Deleted, Dirty, LastEdited, Code, Symbol, Name, DecimalCount " +
            "FROM ref_Currencies_old");
    db.execSQL("DROP TABLE ref_Currencies_old");
    db.update("ref_Currencies", cv, null, null);
}
 

public static void update24(SQLiteDatabase db) {
    db.execSQL("CREATE TABLE ref_Accounts_Sets (_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, FBID TEXT, TS INTEGER, Deleted INTEGER, Dirty INTEGER, LastEdited TEXT, Name TEXT, UNIQUE (Name, Deleted) ON CONFLICT ABORT);");
    db.execSQL("CREATE TABLE log_Accounts_Sets (_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, FBID TEXT, TS INTEGER, Deleted INTEGER, Dirty INTEGER, LastEdited TEXT, SetID INTEGER NOT NULL ON CONFLICT ABORT REFERENCES [ref_Accounts_Sets]([_id]) ON DELETE CASCADE ON UPDATE CASCADE,AccountID INTEGER NOT NULL ON CONFLICT ABORT REFERENCES [ref_Accounts]([_id]) ON DELETE CASCADE ON UPDATE CASCADE,UNIQUE (SetID, AccountID, Deleted) ON CONFLICT ABORT);");

    ContentValues values = new ContentValues();

    values.put("Lat", 0);
    db.update("log_Transactions", values, "Lat IS NULL", null);

    values.clear();
    values.put("Lon", 0);
    db.update("log_Transactions", values, "Lon IS NULL", null);

    values.clear();
    values.put("Accuracy", -1);
    db.update("log_Transactions", values, "Accuracy IS NULL", null);

    values.clear();
    values.put("SimpleDebt", -1);
    db.update("log_Transactions", values, "SimpleDebt IS NULL", null);

    values.clear();
    values.put("ParentID", -1);
    db.update("ref_Categories", values, "ParentID IS NULL", null);

    db.execSQL("CREATE INDEX [idx] ON [log_Transactions] ([Deleted], [DateTime], [SrcAccount], [DestAccount], [Payee], [Category], [Project], [Department], [Location], [SimpleDebt]);");
}
 

public static void updateFullNames(String tableName, boolean useFullName, SQLiteDatabase db) {
        long t = System.currentTimeMillis();
        String nameColumn;
        if (tableName.equals(T_LOG_TRANSACTIONS)) {
            nameColumn = useFullName ? getFullNameColumn(tableName) : C_LOG_TRANSACTIONS_COMMENT;
        } else {
            nameColumn = useFullName ? getFullNameColumn(tableName) : "Name";
        }
        String fields[];
        if (useFullName) {
            fields = new String[]{C_ID, nameColumn, C_SEARCH_STRING, C_FULL_NAME};
        } else {
            fields = new String[]{C_ID, nameColumn, C_SEARCH_STRING};
        }
        Cursor cursor = db.query(tableName, fields, "Deleted = 0", null, null, null, null);
        ContentValues cv = new ContentValues();
        String translit;
        int i = 0;
        try {
            if (cursor.moveToFirst()) {
                while (!cursor.isAfterLast()) {
                    cv.clear();
                    if (useFullName) {
                        cv.put(C_FULL_NAME, cursor.getString(1));
                    }
                    translit = Translit.toTranslit(cursor.getString(1).toLowerCase());
                    if (!cursor.getString(2).equals(translit)) {
                        cv.put(C_SEARCH_STRING, translit);
                    }
                    if (cv.size() != 0) {
                        db.update(tableName, cv, "_id = " + cursor.getString(0), null);
                    }
                    cursor.moveToNext();
                    i++;
//                    Log.d(TAG, cursor.getString(0));
                }
            }
        } finally {
            cursor.close();
        }
//        t = System.currentTimeMillis() - t;
//        Log.d(TAG, "Update full names in " + tableName + " - " + String.valueOf(t) + "ms");
    }
 

public static void update18(SQLiteDatabase db, Context context) {
    //переходим со строкового хранения дат в таблице транзакций на числовое
    //Переименовали старую версию таблицы
    db.execSQL(String.format("ALTER TABLE %s RENAME TO t_transactions_old", "log_Transactions"));

    //Создали новую версию таблицы
    db.execSQL("CREATE TABLE log_Transactions (_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, FBID TEXT, TS INTEGER, Deleted INTEGER, Dirty INTEGER, LastEdited TEXT, DateTime INTEGER NOT NULL, SrcAccount INTEGER NOT NULL REFERENCES ref_Accounts(_id) ON DELETE CASCADE ON UPDATE CASCADE, Payee INTEGER REFERENCES ref_Payees(_id) ON DELETE SET NULL ON UPDATE CASCADE, Category INTEGER REFERENCES ref_Categories(_id) ON DELETE SET NULL ON UPDATE CASCADE, Amount REAL NOT NULL, Project INTEGER REFERENCES ref_Projects(_id) ON DELETE SET NULL ON UPDATE CASCADE, SimpleDebt INTEGER REFERENCES ref_SimpleDebts(_id) ON DELETE SET NULL ON UPDATE CASCADE, Department INTEGER REFERENCES ref_Departments(_id) ON DELETE SET NULL ON UPDATE CASCADE, Location INTEGER REFERENCES ref_Locations(_id) ON DELETE SET NULL ON UPDATE CASCADE, Comment TEXT, File TEXT, DestAccount INTEGER NOT NULL REFERENCES ref_Accounts(_id) ON DELETE CASCADE ON UPDATE CASCADE, ExchangeRate REAL NOT NULL, AutoCreated INTEGER NOT NULL, Lon REAL, Lat REAL, Accuracy INTEGER);");

    Cursor cursor = db.query("t_transactions_old", "_id, DateTime, SrcAccount, Payee, Category, Amount, Project, Location, Comment, File, DestAccount, ExchangeRate, AutoCreated, Lat, Lon, Accuracy, Department, SimpleDebt".split(","), null, null, null, null, null);

    if (cursor != null) {
        try {
            if (cursor.moveToFirst()) {
                DateTimeFormatter dtf = DateTimeFormatter.getInstance(context);
                ContentValues values = new ContentValues();
                while (!cursor.isAfterLast()) {
                    values.clear();
                    values.put("DateTime", dtf.parseDateTimeSqlString(cursor.getString(cursor.getColumnIndex("DateTime"))).getTime());
                    values.put("SrcAccount", cursor.getLong(cursor.getColumnIndex("SrcAccount")));
                    values.put("Payee", cursor.getLong(cursor.getColumnIndex("Payee")));
                    values.put("Category", cursor.getLong(cursor.getColumnIndex("Category")));
                    values.put("Amount", cursor.getDouble(cursor.getColumnIndex("Amount")));
                    values.put("Project", cursor.getLong(cursor.getColumnIndex("Project")));
                    values.put("Department", cursor.getLong(cursor.getColumnIndex("Department")));
                    values.put("Location", cursor.getLong(cursor.getColumnIndex("Location")));
                    values.put("Comment", cursor.getString(cursor.getColumnIndex("Comment")));
                    values.put("File", cursor.getString(cursor.getColumnIndex("File")));
                    values.put("DestAccount", cursor.getLong(cursor.getColumnIndex("DestAccount")));
                    values.put("ExchangeRate", cursor.getDouble(cursor.getColumnIndex("ExchangeRate")));
                    values.put("AutoCreated", cursor.getInt(cursor.getColumnIndex("AutoCreated")) == 1);
                    if (!cursor.isNull(cursor.getColumnIndex("Lat"))) {
                        values.put("Lat", cursor.getDouble(cursor.getColumnIndex("Lat")));
                    } else {
                        values.put("Lat", 0);
                    }
                    if (!cursor.isNull(cursor.getColumnIndex("Lon"))) {
                        values.put("Lon", cursor.getDouble(cursor.getColumnIndex("Lon")));
                    } else {
                        values.put("Lon", 0);
                    }
                    if (!cursor.isNull(cursor.getColumnIndex("Accuracy"))) {
                        values.put("Accuracy", cursor.getInt(cursor.getColumnIndex("Accuracy")));
                    } else {
                        values.put("Accuracy", -1);
                    }
                    if (!cursor.isNull(cursor.getColumnIndex("SimpleDebt"))) {
                        values.put("SimpleDebt", cursor.getLong(cursor.getColumnIndex("SimpleDebt")));
                    } else {
                        values.put("SimpleDebt", -1);
                    }

                    db.insert("log_Transactions", null, values);

                    cursor.moveToNext();
                }
            }
        } finally {
            cursor.close();
        }
    }
    db.execSQL("DROP TABLE t_transactions_old");

    //создаем в таблицах поля, необходимые для работы синхронизации и меняем constraints (пересоздаем все таблицы)
    ContentValues cv = new ContentValues();
    cv.put("FBID", "");
    cv.put("TS", -1);
    cv.put("Deleted", 0);
    cv.put("Dirty", false);
    cv.put("LastEdited", "");
    for (int i = 0; i < tableInfo17.length; i++) {
        db.execSQL(String.format("ALTER TABLE %s RENAME TO %s_old", tableInfo17[i].getTableName(), tableInfo17[i].getTableName()));
        db.execSQL(tableInfo18[i].getTableCreateSQL());
        db.execSQL(String.format("INSERT INTO %s (%s) SELECT %s FROM %s_old", tableInfo18[i].getTableName(), tableInfo17[i].getTableFields(), tableInfo17[i].getTableFields(), tableInfo17[i].getTableName()));
        db.execSQL(String.format("DROP TABLE %s_old", tableInfo17[i].getTableName()));
        db.update(tableInfo18[i].getTableName(), cv, null, null);
    }
}
 

public static void update21(SQLiteDatabase db) {
    ContentValues cv = new ContentValues();
    cv.put("Comment", "");
    db.update("log_Templates", cv, "Comment IS NULL", null);
}