Code example for SQLiteDatabase

Methods: execSQLgetVersionrawQuery, x 1

0
    private LearningDataDao learningDataDao = null;
 
    @Override 
    public void onCreate(SQLiteDatabase database, ConnectionSource connectionSource) {
        Log.v(TAG, "Now we are creating a new database!");
        Log.i(TAG, "Newly created db version: " + database.getVersion()); 
 
        try { 
            TableUtils.createTable(connectionSource, Card.class);
            TableUtils.createTable(connectionSource, Deck.class);
            TableUtils.createTable(connectionSource, Setting.class);
            TableUtils.createTable(connectionSource, Filter.class);
            TableUtils.createTable(connectionSource, Category.class);
            TableUtils.createTable(connectionSource, LearningData.class);
 
            getSettingDao().create(new Setting()); 
            getCategoryDao().create(new Category()); 
 
            if (database.getVersion() == 0) {
                convertOldDatabase(database);
            } 
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("Database creation error: " + e.toString());
        } 
    } 
 
    /* Convert database from AnyMemo < 9.0 */ 
    private void convertOldDatabase(SQLiteDatabase database) {
        Cursor res = database.rawQuery("select name from sqlite_master where type = 'table' and name = 'dict_tbl'", null);
        boolean isOldDatabase = res.getCount() > 0;
        res.close();
        // This is old database 
        if (isOldDatabase) {
            // copy all cards 
            database.execSQL("insert into cards (ordinal, question, answer, note)" +
                    " select _id as ordinal, question, answer, note from dict_tbl"); 
 
 
            // Make sure the count matches in old database; 
            int count_dict = 0, count_learn = 0;
            Cursor result = database.rawQuery("SELECT _id FROM dict_tbl", null);
            count_dict = result.getCount();
            result.close();
            result = database.rawQuery("SELECT _id FROM learn_tbl", null);
            count_learn = result.getCount();
            result.close();
            if(count_learn != count_dict){
                database.execSQL("DELETE FROM learn_tbl");
                database.execSQL("INSERT INTO learn_tbl(_id) SELECT _id FROM dict_tbl");
                database.execSQL("UPDATE learn_tbl SET date_learn = '2010-01-01', interval = 0, grade = 0, easiness = 2.5, acq_reps = 0, ret_reps  = 0, lapses = 0, acq_reps_since_lapse = 0, ret_reps_since_lapse = 0");
            } 
 
            // copy learning data 
            database.execSQL("update cards set learningData_id = ("
                + " select _id as learningData_id" 
                + " from learn_tbl where learn_tbl._id = cards.id)"); 
            database.execSQL("insert into learning_data (acqReps, acqRepsSinceLapse, easiness,"
                + " grade, lapses, lastLearnDate, nextLearnDate, retReps, " 
                + " retRepsSinceLapse)" 
                + " select acq_reps as acqReps , acq_reps_since_lapse as acqRepsSinceLapse," 
                + " easiness, grade, lapses," 
                + " date_learn || ' 00:00:00.000000' as lastLearnDate," 
                + " datetime(julianday(date_learn) + interval) || '.000000' as nextLearnDate," 
                + " ret_reps as retReps, ret_reps_since_lapse as retRepsSinceLapse" 
                + " from learn_tbl"); 
 
 
            // copy categories 
            database.execSQL("insert into categories (name)"
                + " select category as name from dict_tbl where category != ''" 
                + " and category is not null" 
                + " group by category"); 
            database.execSQL("update cards set category_id = ("
                + " select id as category_id from categories as cat" 
                + " join dict_tbl as dic on dic.category = cat.name" 
                + " where cards.id = dic._id)"); 
 
            // Update category if the category is null 
            database.execSQL("update cards "
                    + " set category_id = 1" 
                    + " where category_id is null"); 
 
            database.execSQL("update cards set updateDate='2010-01-01 00:00:00.000000'," + 
                    "creationDate='2010-01-01 00:00:00.000000'"); 
            database.execSQL("update categories set updateDate='2010-01-01 00:00:00.000000'"); 
            database.execSQL("update learning_data set updateDate='2010-01-01 00:00:00.000000'"); 
 
            // Set unused fields 
            database.execSQL("update cards"
                + " set cardType = 0"); 
 
 
        } 
    } 
-1
CodeRank
SQLiteDatabase.execSQL("DELETE FROM learn_tbl")SQLiteDatabase.execSQL("INSERT INTO learn_tbl(_id) SELECT _id FROM dict_tbl")StartSQLiteDatabase.getVersion()SQLiteDatabase.execSQL("insert into learning_data (acqReps, acqRepsSinceLapse, easiness, grade, lapses, lastLearnDate, nextLearnDate, retReps,  retRepsSinceLapse) select acq_reps as acqReps , acq_reps_since_lapse as acqRepsSinceLapse, easiness, grade, lapses, date_learn || ' 00:00:00.000000' as lastLearnDate, datetime(julianday(date_learn) + interval) || '.000000' as nextLearnDate, ret_reps as retReps, ret_reps_since_lapse as retRepsSinceLapse from learn_tbl")SQLiteDatabase.execSQL("insert into categories (name) select category as name from dict_tbl where category != '' and category is not null group by category")SQLiteDatabase.execSQL("update cards  set category_id = 1 where category_id is null")SQLiteDatabase.execSQL("update cards set updateDate='2010-01-01 00:00:00.000000',creationDate='2010-01-01 00:00:00.000000'")SQLiteDatabase.execSQL("UPDATE learn_tbl SET date_learn = '2010-01-01', interval = 0, grade = 0, easiness = 2.5, acq_reps = 0, ret_reps  = 0, lapses = 0, acq_reps_since_lapse = 0, ret_reps_since_lapse = 0")SQLiteDatabase.execSQL("update cards set learningData_id = ( select _id as learningData_id from learn_tbl where learn_tbl._id = cards.id)")SQLiteDatabase.execSQL("update learning_data set updateDate='2010-01-01 00:00:00.000000'")SQLiteDatabase.execSQL("update cards set cardType = 0")SQLiteDatabase.rawQuery("SELECT _id FROM learn_tbl",null)SQLiteDatabase.rawQuery("select name from sqlite_master where type = 'table' and name = 'dict_tbl'",null)SQLiteDatabase.execSQL("update cards set category_id = ( select id as category_id from categories as cat join dict_tbl as dic on dic.category = cat.name where cards.id = dic._id)")SQLiteDatabase.execSQL("insert into cards (ordinal, question, answer, note) select _id as ordinal, question, answer, note from dict_tbl")SQLiteDatabase.execSQL("update categories set updateDate='2010-01-01 00:00:00.000000'")SQLiteDatabase.rawQuery("SELECT _id FROM dict_tbl",null)