Examples with SupportSQLiteDatabase androidx.sqlite.db.SupportSQLiteDatabase used on opensource projects

Example 6 with SupportSQLiteDatabase

use of androidx.sqlite.db.SupportSQLiteDatabase in project SeriesGuide by UweTrottmann.

the class MigrationTest method migrationFrom48To49_containsCorrectData.

@Test
public void migrationFrom48To49_containsCorrectData() throws IOException {
    SupportSQLiteDatabase dbOld = migrationTestHelper.createDatabase(TEST_DB_NAME, SgRoomDatabase.VERSION_48_EPISODE_PLAYS);
    RoomDatabaseTestHelper.insertShow(SHOW, dbOld);
    RoomDatabaseTestHelper.insertSeason(SEASON, dbOld);
    RoomDatabaseTestHelper.insertEpisode(dbOld, EPISODE, SHOW.getTvdbId(), SEASON.getTvdbId(), SEASON.getNumber(), true);
    // Insert activity
    dbOld.execSQL("INSERT INTO activity (activity_episode, activity_show, activity_time) VALUES (21, 42, 123456789)");
    dbOld.close();
    final SupportSQLiteDatabase db = getMigratedDatabase(SgRoomDatabase.VERSION_49_AUTO_ID_MIGRATION);
    // Old tables should still exist, data should remain.
    assertTestData_series_seasons_episodes(db);
    // New tables have different structure.
    queryAndAssert(db, "SELECT _id, series_tvdb_id, series_tmdb_id, series_title, series_runtime, series_poster, series_next FROM sg_show", dbShow -> {
        // Row id should be auto-generated.
        assertThat(dbShow.getLong(0)).isNotEqualTo(SHOW.getTvdbId());
        // TVDB id should be in new column.
        assertThat(dbShow.getInt(1)).isEqualTo(SHOW.getTvdbId());
        // TMDB id should not be set, but exist.
        assertThat(dbShow.isNull(2)).isTrue();
        // Some other values that should have moved to other columns.
        assertThat(dbShow.getString(3)).isEqualTo(SHOW.getTitle());
        assertThat(dbShow.getInt(4)).isEqualTo(SHOW.getRuntime());
        assertThat(dbShow.getString(5)).isEqualTo(SHOW.getPoster());
        // Next episode changed from TVDB to row ID, so reset to default value.
        assertThat(dbShow.getString(6)).isEmpty();
    });
    Cursor showIdQuery = db.query("SELECT _id FROM sg_show");
    showIdQuery.moveToFirst();
    long showId = showIdQuery.getLong(0);
    showIdQuery.close();
    queryAndAssert(db, "SELECT _id, series_id, season_tmdb_id, season_tvdb_id, season_number, season_order FROM sg_season", dbSeason -> {
        // Row id should be auto-generated.
        assertThat(dbSeason.getLong(0)).isNotEqualTo(SEASON.getTvdbId());
        // Show ID should now be internal ID, not TVDB ID.
        assertThat(dbSeason.getInt(1)).isEqualTo(showId);
        // TMDB id should not be set, but exist.
        assertThat(dbSeason.isNull(2)).isTrue();
        // TVDB ID should be in new column.
        assertThat(dbSeason.getInt(3)).isEqualTo(SEASON.getTvdbId());
        assertThat(dbSeason.getInt(4)).isEqualTo(SEASON.getNumber());
        // order is new, should be the number.
        assertThat(dbSeason.getInt(5)).isEqualTo(SEASON.getNumber());
    });
    Cursor seasonIdQuery = db.query("SELECT _id FROM sg_season");
    seasonIdQuery.moveToFirst();
    long seasonId = seasonIdQuery.getLong(0);
    seasonIdQuery.close();
    queryAndAssert(db, "SELECT _id, series_id, season_id, episode_tmdb_id, episode_tvdb_id, episode_title, episode_number, episode_order, episode_season_number FROM sg_episode", dbEpisode -> {
        // Row id should be auto-generated.
        assertThat(dbEpisode.getLong(0)).isNotEqualTo(EPISODE.getTvdbId());
        // Show and season ID should now be internal ID, not TVDB ID.
        assertThat(dbEpisode.getLong(1)).isEqualTo(showId);
        assertThat(dbEpisode.getLong(2)).isEqualTo(seasonId);
        // TMDB id should not be set, but exist.
        assertThat(dbEpisode.isNull(3)).isTrue();
        assertThat(dbEpisode.getInt(4)).isEqualTo(EPISODE.getTvdbId());
        assertThat(dbEpisode.getString(5)).isEqualTo(EPISODE.getName());
        assertThat(dbEpisode.getInt(6)).isEqualTo(EPISODE.getNumber());
        // order is new, should be the number.
        assertThat(dbEpisode.getInt(7)).isEqualTo(EPISODE.getNumber());
        assertThat(dbEpisode.getInt(8)).isEqualTo(SEASON.getNumber());
    });
    // Ensure new type column was populated.
    queryAndAssert(db, "SELECT * FROM activity", cursor -> {
        int activity_type = cursor.getColumnIndex("activity_type");
        assertThat(cursor.getInt(activity_type)).isEqualTo(ActivityType.TVDB_ID);
    });
    // Ensure unique index now includes type column by inserting same IDs, but different type.
    String activityStmt = "INSERT INTO activity (activity_episode, activity_show, activity_time, activity_type)" + " VALUES (21, 42, 123456789, " + ActivityType.TMDB_ID + ")";
    db.execSQL(activityStmt);
    SQLiteConstraintException constraintException = assertThrows(SQLiteConstraintException.class, () -> db.execSQL(activityStmt));
    assertThat(constraintException).hasMessageThat().contains("UNIQUE constraint");
}

Example 7 with SupportSQLiteDatabase

use of androidx.sqlite.db.SupportSQLiteDatabase in project SeriesGuide by UweTrottmann.

the class MigrationTest method migrationFrom46To47_containsCorrectData.

@Test
public void migrationFrom46To47_containsCorrectData() throws IOException {
    SupportSQLiteDatabase db = migrationTestHelper.createDatabase(TEST_DB_NAME, SgRoomDatabase.VERSION_46_SERIES_SLUG);
    RoomDatabaseTestHelper.insertShow(SHOW, db);
    RoomDatabaseTestHelper.insertSeason(SEASON, db);
    RoomDatabaseTestHelper.insertEpisode(EPISODE, SHOW.getTvdbId(), SEASON.getTvdbId(), SEASON.getNumber(), db);
    db.close();
    db = getMigratedDatabase(SgRoomDatabase.VERSION_47_SERIES_POSTER_THUMB);
    assertTestData_series_seasons_episodes(db);
    queryAndAssert(db, "SELECT series_poster_small, poster FROM series", series -> assertThat(series.getString(0)).isEqualTo(ImageTools.TVDB_LEGACY_CACHE_PREFIX + series.getString(1)));
}

Example 8 with SupportSQLiteDatabase

use of androidx.sqlite.db.SupportSQLiteDatabase in project SeriesGuide by UweTrottmann.

the class MigrationTest method migrationFrom47To48_containsCorrectData.

@Test
public void migrationFrom47To48_containsCorrectData() throws IOException {
    int v47 = SgRoomDatabase.VERSION_47_SERIES_POSTER_THUMB;
    SupportSQLiteDatabase db = migrationTestHelper.createDatabase(TEST_DB_NAME, v47);
    RoomDatabaseTestHelper.insertShow(SHOW, db);
    RoomDatabaseTestHelper.insertSeason(SEASON, db);
    TestEpisode testEpisode = getTestEpisode(21);
    RoomDatabaseTestHelper.insertEpisode(db, testEpisode, SHOW.getTvdbId(), SEASON.getTvdbId(), SEASON.getNumber(), true);
    testEpisode = getTestEpisode(22);
    RoomDatabaseTestHelper.insertEpisode(db, testEpisode, SHOW.getTvdbId(), SEASON.getTvdbId(), SEASON.getNumber(), false);
    MovieDetails testMovieDetails = getTestMovieDetails(12);
    testMovieDetails.setWatched(true);
    RoomDatabaseTestHelper.insertMovie(db, testMovieDetails);
    testMovieDetails = getTestMovieDetails(13);
    testMovieDetails.setWatched(false);
    RoomDatabaseTestHelper.insertMovie(db, testMovieDetails);
    db.close();
    db = getMigratedDatabase(SgRoomDatabase.VERSION_48_EPISODE_PLAYS);
    assertTestData_series_seasons_episodes(db);
    // Watched episode should have 1 play.
    queryAndAssert(db, "SELECT plays FROM episodes WHERE _id=21", episodeWatched -> assertThat(episodeWatched.getInt(0)).isEqualTo(1));
    queryAndAssert(db, "SELECT plays FROM episodes WHERE _id=22", episodeNotWatched -> assertThat(episodeNotWatched.getInt(0)).isEqualTo(0));
    // Watched movie should have 1 play.
    queryAndAssert(db, "SELECT movies_plays FROM movies WHERE movies_tmdbid=12", movieWatched -> assertThat(movieWatched.getInt(0)).isEqualTo(1));
    queryAndAssert(db, "SELECT movies_plays FROM movies WHERE movies_tmdbid=13", movieNotWatched -> assertThat(movieNotWatched.getInt(0)).isEqualTo(0));
}

Example 9 with SupportSQLiteDatabase

use of androidx.sqlite.db.SupportSQLiteDatabase in project SeriesGuide by UweTrottmann.

the class SeriesGuideProvider method query.

@Override
public Cursor query(@NonNull Uri uri, String[] projection, String selection, String[] selectionArgs, String sortOrder) {
    if (LOGV) {
        Timber.v("query(uri=%s, proj=%s)", uri, Arrays.toString(projection));
    }
    SupportSQLiteOpenHelper databaseHelper = SgRoomDatabase.getInstance(getContext()).getOpenHelper();
    final int match = sUriMatcher.match(uri);
    // support close op for legacy database import tool, will reopen on next op
    if (match == CLOSE) {
        databaseHelper.close();
        return null;
    }
    // always get writable database, might have to be upgraded
    final SupportSQLiteDatabase db = databaseHelper.getWritableDatabase();
    switch(match) {
        default:
            {
                // Most cases are handled with simple SelectionBuilder
                final SelectionBuilder builder = buildSelection(uri, match);
                Cursor query = null;
                try {
                    query = builder.map(BaseColumns._COUNT, // support count base column
                    "count(*)").where(selection, selectionArgs).query(db, projection, sortOrder);
                } catch (SQLiteException e) {
                    Timber.e(e, "Failed to query with uri=%s", uri);
                }
                if (query != null) {
                    // noinspection ConstantConditions
                    query.setNotificationUri(getContext().getContentResolver(), uri);
                }
                return query;
            }
    }
}

Example 10 with SupportSQLiteDatabase

use of androidx.sqlite.db.SupportSQLiteDatabase in project sqldelight by square.

the class MigrationTest method testMigrationWorks.

@Test
public void testMigrationWorks() throws IOException {
    // Set up version 1 db.
    SupportSQLiteOpenHelper.Callback callback = new SupportSQLiteOpenHelper.Callback(1) {

        @Override
        public void onCreate(SupportSQLiteDatabase db) {
            db.execSQL("CREATE TABLE person (\n" + "  _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n" + "  first_name TEXT NOT NULL\n" + ");");
            db.execSQL("CREATE TABLE `group` (\n" + "  _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n" + "  'where' INTEGER NOT NULL,\n" + "  [having] INTEGER NOT NULL\n" + ");");
            db.execSQL("INSERT INTO person VALUES (1, 'alec');");
        }

        @Override
        public void onUpgrade(SupportSQLiteDatabase db, int oldVersion, int newVersion) {
        }
    };
    SupportSQLiteOpenHelper.Configuration configuration = SupportSQLiteOpenHelper.Configuration.builder(InstrumentationRegistry.getTargetContext()).callback(callback).name("test.db").build();
    SupportSQLiteOpenHelper helper = new FrameworkSQLiteOpenHelperFactory().create(configuration);
    // Creates the db.
    helper.getWritableDatabase().execSQL("DELETE FROM person WHERE first_name NOT IN ('alec')");
    helper.close();
    // Migrate the db with a queryWrapper
    SqlDriver database = new AndroidSqliteDriver(QueryWrapper.Companion.getSchema(), InstrumentationRegistry.getTargetContext(), "test.db");
    QueryWrapper queryWrapper = QueryWrapper.Companion.invoke(database);
    // Assert info is correct
    Person person = queryWrapper.getPersonQueries().nameIn(Arrays.asList("alec")).executeAsOne();
    assertThat(person).isEqualTo(new Person(1, "alec", "sup"));
    database.close();
}