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

The following examples show how to use io.requery.android.database.sqlite.SQLiteDatabase#execSQL() . You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source File: DatabaseGeneralTest.java    From sqlite-android with Apache License 2.0 6 votes vote down vote up
@MediumTest
@Test
public void testSchemaChange1() throws Exception {
    SQLiteDatabase db1 = mDatabase;
    Cursor cursor;

    db1.execSQL("CREATE TABLE db1 (_id INTEGER PRIMARY KEY, data TEXT);");

    cursor = db1.query("db1", null, null, null, null, null, null);
    assertNotNull("Cursor is null", cursor);

    db1.execSQL("CREATE TABLE db2 (_id INTEGER PRIMARY KEY, data TEXT);");

    assertEquals(0, cursor.getCount());
    cursor.close();
}
 
Example 2
Source File: UpdateHelper.java    From fingen with Apache License 2.0 6 votes vote down vote up
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);
    }
}
 
Example 3
Source File: UpdateHelper.java    From fingen with Apache License 2.0 6 votes vote down vote up
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();
    }
}
 
Example 4
Source File: UpdateHelper.java    From fingen with Apache License 2.0 5 votes vote down vote up
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);
}
 
Example 5
Source File: UpdateHelper.java    From fingen with Apache License 2.0 5 votes vote down vote up
public static void update33(SQLiteDatabase db) {
    db.execSQL("DELETE FROM log_Products WHERE TransactionID < 0");

    Cursor cursor = db.rawQuery("SELECT _id, Amount FROM log_Transactions " +
            "WHERE _id not in (SELECT TransactionID FROM log_Products) AND Deleted = 0", null);

    ContentValues cv = new ContentValues();

    if (cursor != null) {
        try {
            if (cursor.moveToFirst()) {
                while (!cursor.isAfterLast()) {
                    cv.clear();
                    cv.put(DBHelper.C_SYNC_FBID, "");
                    cv.put(DBHelper.C_SYNC_TS, -1);
                    cv.put(DBHelper.C_SYNC_DELETED, 0);
                    cv.put(DBHelper.C_SYNC_DIRTY, 0);
                    cv.put(DBHelper.C_SYNC_LASTEDITED, "");
                    cv.put(DBHelper.C_LOG_PRODUCTS_TRANSACTIONID, cursor.getLong(0));
                    cv.put(DBHelper.C_LOG_PRODUCTS_PRODUCTID, 0);
                    cv.put(DBHelper.C_LOG_PRODUCTS_CATEGORY_ID, -1);
                    cv.put(DBHelper.C_LOG_PRODUCTS_PROJECT_ID, -1);
                    cv.put(DBHelper.C_LOG_PRODUCTS_PRICE, cursor.getDouble(1));
                    cv.put(DBHelper.C_LOG_PRODUCTS_QUANTITY, 1);
                    db.insert(DBHelper.T_LOG_PRODUCTS, null, cv);
                    cursor.moveToNext();
                }
            }
        } finally {
            cursor.close();
        }
    }
}
 
Example 6
Source File: UpdateHelper.java    From fingen with Apache License 2.0 5 votes vote down vote up
public static void update32(SQLiteDatabase db) {
    db.execSQL("ALTER TABLE " + DBHelper.T_LOG_TRANSACTIONS + " ADD COLUMN "+DBHelper.C_LOG_TRANSACTIONS_SPLIT+" INTEGER DEFAULT 0;");

    db.execSQL(DBHelper.SQL_CREATE_TABLE_REF_PRODUCTS);
    db.execSQL(DBHelper.SQL_CREATE_TABLE_LOG_PRODUCTS);

    ContentValues cv = new ContentValues();
    cv.put(DBHelper.C_ID, 0);
    cv.put(DBHelper.C_REF_PRODUCTS_NAME, "default_product");
    db.insert(DBHelper.T_REF_PRODUCTS, "", cv);

    Cursor cursorTransactions = db.rawQuery("SELECT _id, Amount FROM log_Transactions WHERE Deleted = 0", null);
    if (cursorTransactions != null) {
        try {
            if (cursorTransactions.moveToFirst()) {
                while (!cursorTransactions.isAfterLast()) {
                    cv.clear();
                    cv.put(DBHelper.C_SYNC_FBID, "");
                    cv.put(DBHelper.C_SYNC_TS, -1);
                    cv.put(DBHelper.C_SYNC_DELETED, 0);
                    cv.put(DBHelper.C_SYNC_DIRTY, 0);
                    cv.put(DBHelper.C_SYNC_LASTEDITED, "");
                    cv.put(DBHelper.C_LOG_PRODUCTS_TRANSACTIONID, cursorTransactions.getLong(0));
                    cv.put(DBHelper.C_LOG_PRODUCTS_PRODUCTID, 0);
                    cv.put(DBHelper.C_LOG_PRODUCTS_CATEGORY_ID, -1);
                    cv.put(DBHelper.C_LOG_PRODUCTS_PROJECT_ID, -1);
                    cv.put(DBHelper.C_LOG_PRODUCTS_PRICE, cursorTransactions.getDouble(1));
                    cv.put(DBHelper.C_LOG_PRODUCTS_QUANTITY, 1);
                    db.insert(DBHelper.T_LOG_PRODUCTS, null, cv);
                    cursorTransactions.moveToNext();
                }
            }
        } finally {
            cursorTransactions.close();
        }
    }
    db.execSQL("CREATE INDEX [idx_Products] ON [log_Products] ([Deleted], [TransactionID], [ProductID]);");
}
 
Example 7
Source File: UpdateHelper.java    From fingen with Apache License 2.0 5 votes vote down vote up
public static void update26(SQLiteDatabase db) {
    Cursor cursorIndexes = db.rawQuery("PRAGMA index_list(log_Transactions)", null);
    if (cursorIndexes.getCount() == 0) {
        db.execSQL("CREATE INDEX [idx] ON [log_Transactions] ([Deleted], [DateTime], [SrcAccount], [DestAccount], [Payee], [Category], [Project], [Department], [Location], [SimpleDebt]);");
    }
    cursorIndexes.close();
}
 
Example 8
Source File: UpdateHelper.java    From fingen with Apache License 2.0 5 votes vote down vote up
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]);");
}
 
Example 9
Source File: FtsDbHelper.java    From FairEmail with GNU General Public License v3.0 5 votes vote down vote up
@Override
public void onCreate(SQLiteDatabase db) {
    Log.i("FTS create");
    db.execSQL("CREATE VIRTUAL TABLE `message`" +
            " USING fts5" +
            " (`account` UNINDEXED" +
            ", `folder` UNINDEXED" +
            ", `time` UNINDEXED" +
            ", `address`" +
            ", `subject`" +
            ", `keyword`" +
            ", `text`" +
            ", tokenize = \"unicode61 remove_diacritics 2\")");
    // https://www.sqlite.org/fts5.html#unicode61_tokenizer
}
 
Example 10
Source File: UpdateHelper.java    From fingen with Apache License 2.0 5 votes vote down vote up
public static void update19(SQLiteDatabase db) {
    db.execSQL("ALTER TABLE ref_Senders RENAME TO ref_Senders_old");
    db.execSQL("CREATE TABLE ref_Senders (_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, FBID TEXT, TS INTEGER, Deleted INTEGER, Dirty INTEGER, LastEdited TEXT, Name TEXT NOT NULL, PhoneNo TEXT NOT NULL, AmountPos INTEGER, BalancePos INTEGER, LeadingCurrencySymbol INTEGER, DateFormat TEXT, isActive INTEGER NOT NULL, UNIQUE (Name, Deleted) ON CONFLICT ABORT, UNIQUE (PhoneNo, Deleted) ON CONFLICT ABORT);");
    db.execSQL(
            "INSERT INTO ref_Senders (_id, FBID, TS, Deleted, Dirty, LastEdited, Name, PhoneNo, AmountPos, BalancePos, LeadingCurrencySymbol, DateFormat, isActive) " +
                    "SELECT _id, FBID, TS, Deleted, Dirty, LastEdited, Name, PhoneNo, AmountPos, BalancePos, LeadingCurrencySymbol, DateFormat, isActive FROM ref_Senders_old");
    db.execSQL("DROP TABLE ref_Senders_old");
}
 
Example 11
Source File: DBHelper.java    From fingen with Apache License 2.0 5 votes vote down vote up
public void updateLogProducts(SQLiteDatabase db) throws IOException {
    db.execSQL("DELETE FROM log_Products WHERE TransactionID < 0");

    Cursor cursor = db.rawQuery("SELECT _id, Amount FROM log_Transactions " +
            "WHERE _id not in (SELECT TransactionID FROM log_Products) AND Deleted = 0", null);

    ContentValues cv = new ContentValues();

    if (cursor != null) {
        try {
            if (cursor.moveToFirst()) {
                while (!cursor.isAfterLast()) {
                    cv.clear();
                    cv.put(DBHelper.C_SYNC_FBID, "");
                    cv.put(DBHelper.C_SYNC_TS, -1);
                    cv.put(DBHelper.C_SYNC_DELETED, 0);
                    cv.put(DBHelper.C_SYNC_DIRTY, 0);
                    cv.put(DBHelper.C_SYNC_LASTEDITED, "");
                    cv.put(DBHelper.C_LOG_PRODUCTS_TRANSACTIONID, cursor.getLong(0));
                    cv.put(DBHelper.C_LOG_PRODUCTS_PRODUCTID, 0);
                    cv.put(DBHelper.C_LOG_PRODUCTS_CATEGORY_ID, -1);
                    cv.put(DBHelper.C_LOG_PRODUCTS_PROJECT_ID, -1);
                    cv.put(DBHelper.C_LOG_PRODUCTS_PRICE, cursor.getDouble(1));
                    cv.put(DBHelper.C_LOG_PRODUCTS_QUANTITY, 1);
                    db.insert(DBHelper.T_LOG_PRODUCTS, null, cv);
                    cursor.moveToNext();
                }
            }
        } finally {
            cursor.close();
        }
    }
}
 
Example 12
Source File: DBHelper.java    From fingen with Apache License 2.0 5 votes vote down vote up
public void updateRunningBalance(SQLiteDatabase database) throws IOException {
    String sql[] = readQueryFromAssets("sql/update_running_balance.sql", mContext);
    for (String s : sql) {
        if (s != null && !s.isEmpty() && !s.equals("\n")) {
            android.util.Log.d(TAG, s);
            database.execSQL(s);
        }
    }
}
 
Example 13
Source File: DBHelper.java    From fingen with Apache License 2.0 5 votes vote down vote up
public void rebuildDB() throws IOException {
    SQLiteDatabase db = getDatabase();
    db.beginTransaction();

    String tableNames[] = new String[]{T_REF_ACCOUNTS, T_REF_ACCOUNTS_SETS, T_LOG_ACCOUNTS_SETS,
            T_REF_CATEGORIES, T_REF_PAYEES, T_REF_PROJECTS, T_REF_LOCATIONS, T_REF_DEPARTMENTS,
            T_REF_SIMPLEDEBTS, T_LOG_TRANSACTIONS, T_LOG_TEMPLATES, T_LOG_INCOMING_SMS, T_LOG_SMS_PARSER_PATTERNS,
            T_REF_DEBTS, T_LOG_BUDGET, T_LOG_BUDGET_DEBTS, T_REF_SENDERS, T_REF_PRODUCTS, T_LOG_PRODUCTS
    };

    for (String tableName : tableNames) {
        db.delete(tableName, "Deleted > 0", null);
    }

    updateFullNames("ref_Categories", true, db);
    updateFullNames("ref_Payees", true, db);
    updateFullNames("ref_Projects", true, db);
    updateFullNames("ref_Locations", true, db);
    updateFullNames("ref_Departments", true, db);

    updateFullNames("ref_Accounts", false, db);
    updateFullNames("ref_SimpleDebts", false, db);
    updateFullNames("log_Templates", false, db);

    updateRunningBalance(db);

    updateLogProducts(db);

    db.setTransactionSuccessful();
    db.endTransaction();

    db.execSQL("VACUUM");
}
 
Example 14
Source File: UpdateHelper.java    From fingen with Apache License 2.0 4 votes vote down vote up
public static void update20(SQLiteDatabase db) {
    db.execSQL("ALTER TABLE log_Templates ADD COLUMN Comment TEXT;");
}
 
Example 15
Source File: UpdateHelper.java    From fingen with Apache License 2.0 4 votes vote down vote up
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);
    }
}
 
Example 16
Source File: UpdateHelper.java    From fingen with Apache License 2.0 4 votes vote down vote up
public static void update25(SQLiteDatabase db) {
    //добавляем колонку со стартовой суммой долга
    db.execSQL("ALTER TABLE ref_SimpleDebts ADD COLUMN StartAmount REAL NOT NULL DEFAULT 0;");
    //добавляем колонку с валютой долга
    db.execSQL("ALTER TABLE ref_SimpleDebts ADD COLUMN Currency INTEGER REFERENCES ref_Currencies(_id) ON DELETE SET NULL ON UPDATE CASCADE;");
    //считываем идентификаторы всех долгов
    Cursor cursorDebts = db.rawQuery("SELECT * FROM ref_SimpleDebts", null);
    List<Long> debtIDs = new ArrayList<>();
    if (cursorDebts != null) {
        try {
            if (cursorDebts.moveToFirst()) {
                while (!cursorDebts.isAfterLast()) {
                    debtIDs.add(cursorDebts.getLong(cursorDebts.getColumnIndex("_id")));
                    cursorDebts.moveToNext();
                }
            }
        } finally {
            cursorDebts.close();
        }
    }
    //перебираем долги и проставляем валюту, как первую попавшуюся валюту транзакции по этому долгу
    Cursor cursorTransactions;
    Cursor cursorAccounts;
    boolean cabbageFound;
    long accountID;
    long cabbageID;
    for (long debtID : debtIDs) {
        cabbageFound = false;
        cursorTransactions = db.query("log_Transactions", new String[]{"SrcAccount"},
                "SimpleDebt = " + String.valueOf(debtID), null, null, null, null);
        if (cursorTransactions != null) {
            try {
                if (cursorTransactions.moveToFirst()) {
                    accountID = cursorTransactions.getLong(0);
                    cursorAccounts = db.query("ref_Accounts", new String[]{"Currency"},
                            "_id = " + String.valueOf(accountID), null, null, null, null);
                    if (cursorAccounts != null) {
                        try {
                            if (cursorAccounts.moveToFirst()) {
                                cabbageID = cursorAccounts.getLong(0);
                                cabbageFound = true;
                                db.execSQL(String.format("UPDATE ref_SimpleDebts SET Currency = %s WHERE _id = %s",
                                        String.valueOf(cabbageID), String.valueOf(debtID)));
                            }
                        } finally {
                            cursorAccounts.close();
                        }
                    }
                }
            } finally {
                cursorTransactions.close();
            }
        }
        if (!cabbageFound) {
            db.delete("ref_SimpleDebts", String.format("_id = %s", String.valueOf(debtID)), null);
        }
    }
}
 
Example 17
Source File: UpdateHelper.java    From fingen with Apache License 2.0 4 votes vote down vote up
public static void update30(SQLiteDatabase db) {
    db.execSQL("ALTER TABLE " + DBHelper.T_LOG_TRANSACTIONS + " ADD COLUMN "+DBHelper.C_LOG_TRANSACTIONS_FN+" INTEGER DEFAULT 0;");
    db.execSQL("ALTER TABLE " + DBHelper.T_LOG_TRANSACTIONS + " ADD COLUMN "+DBHelper.C_LOG_TRANSACTIONS_FD+" INTEGER DEFAULT 0;");
    db.execSQL("ALTER TABLE " + DBHelper.T_LOG_TRANSACTIONS + " ADD COLUMN "+DBHelper.C_LOG_TRANSACTIONS_FP+" INTEGER DEFAULT 0;");
}
 
Example 18
Source File: DBHelper.java    From fingen with Apache License 2.0 4 votes vote down vote up
@Override
public void onCreate(SQLiteDatabase db) {

    Log.d(TAG, SQL_CREATE_TABLE_REF_CURRENCIES);
    db.execSQL(SQL_CREATE_TABLE_REF_CURRENCIES);

    Cabbage cabbage;
    List<String> codes = Arrays.asList("RUB", "USD", "EUR", "UAH", "BYN", "KZT", "ABC");
    for (String code : codes) {
        cabbage = CabbageManager.createFromCode(code, mContext);
        if (cabbage != null) {
            db.insertOrThrow(T_REF_CURRENCIES, null, cabbage.getCV());
        }
    }

    Log.d(TAG, SQL_CREATE_TABLE_REF_ACCOUNTS);
    db.execSQL(SQL_CREATE_TABLE_REF_ACCOUNTS);

    Log.d(TAG, SQL_CREATE_TABLE_REF_PROJECTS);
    db.execSQL(SQL_CREATE_TABLE_REF_PROJECTS);

    Log.d(TAG, SQL_CREATE_TABLE_REF_DEPARTMENTS);
    db.execSQL(SQL_CREATE_TABLE_REF_DEPARTMENTS);

    Log.d(TAG, SQL_CREATE_TABLE_REF_LOCATIONS);
    db.execSQL(SQL_CREATE_TABLE_REF_LOCATIONS);

    Log.d(TAG, SQL_CREATE_TABLE_REF_CATEGORIES);
    db.execSQL(SQL_CREATE_TABLE_REF_CATEGORIES);

    Log.d(TAG, SQL_CREATE_TABLE_REF_PAYEES);
    db.execSQL(SQL_CREATE_TABLE_REF_PAYEES);

    Log.d(TAG, SQL_CREATE_TABLE_LOG_TRANSACTIONS);
    db.execSQL(SQL_CREATE_TABLE_LOG_TRANSACTIONS);
    db.execSQL(I_LOG_TRANSACTIONS_IDX);

    Log.d(TAG, SQL_CREATE_TABLE_LOG_INCOMING_SMS);
    db.execSQL(SQL_CREATE_TABLE_LOG_INCOMING_SMS);

    Log.d(TAG, SQL_CREATE_TABLE_LOG_SMS_PARSER_PATTERNS);
    db.execSQL(SQL_CREATE_TABLE_LOG_SMS_PARSER_PATTERNS);

    Log.d(TAG, SQL_CREATE_TABLE_REF_DEBTS);
    db.execSQL(SQL_CREATE_TABLE_REF_DEBTS);

    Log.d(TAG, SQL_CREATE_TABLE_LOG_BUDGET);
    db.execSQL(SQL_CREATE_TABLE_LOG_BUDGET);

    Log.d(TAG, SQL_CREATE_TABLE_LOG_BUDGET_DEBTS);
    db.execSQL(SQL_CREATE_TABLE_LOG_BUDGET_DEBTS);

    Log.d(TAG, SQL_CREATE_TABLE_LOG_TEMPLATES);
    db.execSQL(SQL_CREATE_TABLE_LOG_TEMPLATES);

    Log.d(TAG, SQL_CREATE_TABLE_REF_SIMPLEDEBTS);
    db.execSQL(SQL_CREATE_TABLE_REF_SIMPLEDEBTS);

    Log.d(TAG, SQL_CREATE_TABLE_REF_SENDERS);
    db.execSQL(SQL_CREATE_TABLE_REF_SENDERS);

    Log.d(TAG, SQL_CREATE_TABLE_REF_ACCOUNTS_SETS);
    db.execSQL(SQL_CREATE_TABLE_REF_ACCOUNTS_SETS);

    Log.d(TAG, SQL_CREATE_TABLE_LOG_ACCOUNTS_SETS);
    db.execSQL(SQL_CREATE_TABLE_LOG_ACCOUNTS_SETS);

    Log.d(TAG, SQL_CREATE_TABLE_REF_PRODUCTS);
    db.execSQL(SQL_CREATE_TABLE_REF_PRODUCTS);

    Log.d(TAG, SQL_CREATE_TABLE_LOG_PRODUCTS);
    db.execSQL(SQL_CREATE_TABLE_LOG_PRODUCTS);
    db.execSQL(I_LOG_PRODUCTS_IDX);

    ContentValues cv = new ContentValues();
    cv.put(C_ID, 0);
    cv.put(C_REF_PRODUCTS_NAME, "default_product");
    db.insert(T_REF_PRODUCTS, "", cv);

    db.execSQL(SQL_CREATE_TABLE_LOG_RB);
    db.execSQL(IDX_RB_ACCOUNTS);
    db.execSQL(IDX_RB_TRANSACTIONS);
    db.execSQL(IDX_RB_DATETIME);
}
 
Example 19
Source File: FtsDbHelper.java    From FairEmail with GNU General Public License v3.0 4 votes vote down vote up
static void optimize(SQLiteDatabase db) {
    Log.i("FTS optimize");
    db.execSQL("INSERT INTO message (message) VALUES ('optimize')");
}
 
Example 20
Source File: FtsDbHelper.java    From FairEmail with GNU General Public License v3.0 4 votes vote down vote up
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    Log.i("FTS upgrade from " + oldVersion + " to " + newVersion);
    db.execSQL("DROP TABLE `message`");
    onCreate(db);
}