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

@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();
}
 

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 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 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 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();
        }
    }
}
 

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]);");
}
 

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();
}
 

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]);");
}
 

@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
}
 

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");
}
 

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();
        }
    }
}
 

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);
        }
    }
}
 

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");
}
 

public static void update20(SQLiteDatabase db) {
    db.execSQL("ALTER TABLE log_Templates ADD COLUMN Comment TEXT;");
}
 

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 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);
        }
    }
}
 

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;");
}
 

@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);
}
 

static void optimize(SQLiteDatabase db) {
    Log.i("FTS optimize");
    db.execSQL("INSERT INTO message (message) VALUES ('optimize')");
}
 

@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);
}