DistanceTable extends TripForeignKeyAbstractSqlTable<Distance> { @Override public synchronized void onUpgrade(@NonNull SQLiteDatabase db, int oldVersion, int newVersion, @NonNull TableDefaultsCustomizer customizer) { super.onUpgrade(db, oldVersion, newVersion, customizer); if (oldVersion <= 12) { final String createSqlV12 = "CREATE TABLE " + TABLE_NAME + " (" + COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + COLUMN_PARENT + " TEXT REFERENCES " + TripsTable.COLUMN_NAME + " ON DELETE CASCADE," + COLUMN_DISTANCE + " DECIMAL(10, 2) DEFAULT 0.00," + COLUMN_LOCATION + " TEXT," + COLUMN_DATE + " DATE," + COLUMN_TIMEZONE + " TEXT," + COLUMN_COMMENT + " TEXT," + COLUMN_RATE_CURRENCY + " TEXT NOT NULL, " + COLUMN_RATE + " DECIMAL(10, 2) DEFAULT 0.00" + ");"; Logger.debug(this, createSqlV12); db.execSQL(createSqlV12); final String distanceMigrateBase = "INSERT INTO " + DistanceTable.TABLE_NAME + "(" + DistanceTable.COLUMN_PARENT + ", " + DistanceTable.COLUMN_DISTANCE + ", " + DistanceTable.COLUMN_LOCATION + ", " + DistanceTable.COLUMN_DATE + ", " + DistanceTable.COLUMN_TIMEZONE + ", " + DistanceTable.COLUMN_COMMENT + ", " + DistanceTable.COLUMN_RATE_CURRENCY + ")" + " SELECT " + TripsTable.COLUMN_NAME + ", " + TripsTable.COLUMN_MILEAGE + " , \"\" as " + DistanceTable.COLUMN_LOCATION + ", " + TripsTable.COLUMN_FROM + ", " + TripsTable.COLUMN_FROM_TIMEZONE + " , \"\" as " + DistanceTable.COLUMN_COMMENT + ", "; final String distanceMigrateNotNullCurrency = distanceMigrateBase + TripsTable.COLUMN_DEFAULT_CURRENCY + " FROM " + TripsTable.TABLE_NAME + " WHERE " + TripsTable.COLUMN_DEFAULT_CURRENCY + " IS NOT NULL AND " + TripsTable.COLUMN_MILEAGE + " > 0;"; final String distanceMigrateNullCurrency = distanceMigrateBase + "\"" + userPreferenceManager.get(UserPreference.General.DefaultCurrency) + "\" as " + DistanceTable.COLUMN_RATE_CURRENCY + " FROM " + TripsTable.TABLE_NAME + " WHERE " + TripsTable.COLUMN_DEFAULT_CURRENCY + " IS NULL AND " + TripsTable.COLUMN_MILEAGE + " > 0;"; Logger.debug(this, distanceMigrateNotNullCurrency); Logger.debug(this, distanceMigrateNullCurrency); db.execSQL(distanceMigrateNotNullCurrency); db.execSQL(distanceMigrateNullCurrency); } if (oldVersion <= 14) { onUpgradeToAddSyncInformation(db, oldVersion, newVersion); } if (oldVersion <= 18) { final String addTripIdColumn = String.format("ALTER TABLE %s ADD %s INTEGER REFERENCES %s ON DELETE CASCADE", TABLE_NAME, COLUMN_PARENT_TRIP_ID, TripsTable.TABLE_NAME); Logger.debug(this, addTripIdColumn); db.execSQL(addTripIdColumn); final String fillTripId = String.format("UPDATE %s SET %s = ( SELECT %s FROM %s WHERE %s = %s LIMIT 1 )", TABLE_NAME, COLUMN_PARENT_TRIP_ID, TripsTable.COLUMN_ID, TripsTable.TABLE_NAME, TripsTable.COLUMN_NAME, COLUMN_PARENT); Logger.debug(this, fillTripId); db.execSQL(fillTripId); final String finalColumns = TextUtils.join(",", new String[]{ COLUMN_ID, COLUMN_PARENT_TRIP_ID, COLUMN_DISTANCE, COLUMN_LOCATION, COLUMN_DATE, COLUMN_TIMEZONE, COLUMN_COMMENT, COLUMN_RATE_CURRENCY, COLUMN_RATE, AbstractSqlTable.COLUMN_DRIVE_SYNC_ID, AbstractSqlTable.COLUMN_DRIVE_IS_SYNCED, AbstractSqlTable.COLUMN_DRIVE_MARKED_FOR_DELETION, AbstractSqlTable.COLUMN_LAST_LOCAL_MODIFICATION_TIME}); final String copyTable = "CREATE TABLE " + TABLE_NAME + "_copy" + " (" + COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + COLUMN_PARENT_TRIP_ID + " INTEGER REFERENCES " + TripsTable.COLUMN_NAME + " ON DELETE CASCADE," + COLUMN_DISTANCE + " DECIMAL(10, 2) DEFAULT 0.00," + COLUMN_LOCATION + " TEXT," + COLUMN_DATE + " DATE," + COLUMN_TIMEZONE + " TEXT," + COLUMN_COMMENT + " TEXT," + COLUMN_RATE_CURRENCY + " TEXT NOT NULL, " + COLUMN_RATE + " DECIMAL(10, 2) DEFAULT 0.00, " + AbstractSqlTable.COLUMN_DRIVE_SYNC_ID + " TEXT, " + AbstractSqlTable.COLUMN_DRIVE_IS_SYNCED + " BOOLEAN DEFAULT 0, " + AbstractSqlTable.COLUMN_DRIVE_MARKED_FOR_DELETION + " BOOLEAN DEFAULT 0, " + AbstractSqlTable.COLUMN_LAST_LOCAL_MODIFICATION_TIME + " DATE " + ");"; Logger.debug(this, copyTable); db.execSQL(copyTable); final String insertData = String.format("INSERT INTO %s_copy ( %s ) SELECT %s FROM %s ;", TABLE_NAME, finalColumns, finalColumns, TABLE_NAME); Logger.debug(this, insertData); db.execSQL(insertData); final String dropOldTable = String.format("DROP TABLE %s;", TABLE_NAME); Logger.debug(this, dropOldTable); db.execSQL(dropOldTable); final String renameTable = String.format("ALTER TABLE %s_copy RENAME TO %s;", TABLE_NAME, TABLE_NAME); Logger.debug(this, renameTable); db.execSQL(renameTable); onUpgradeToAddUUID(db, oldVersion); } if (oldVersion <= 19) { final String alterDistance = "ALTER TABLE " + TABLE_NAME + " ADD " + COLUMN_PAYMENT_METHOD_ID + " INTEGER REFERENCES " + PaymentMethodsTable.TABLE_NAME + " ON DELETE NO ACTION"; final String alterDistance2 = "ALTER TABLE " + TABLE_NAME + " ADD " + COLUMN_LOCATION_HIDDEN_AUTO_COMPLETE + " BOOLEAN DEFAULT 0"; final String alterDistance3 = "ALTER TABLE " + TABLE_NAME + " ADD " + COLUMN_COMMENT_HIDDEN_AUTO_COMPLETE + " BOOLEAN DEFAULT 0"; Logger.debug(this, alterDistance); Logger.debug(this, alterDistance2); Logger.debug(this, alterDistance3); db.execSQL(alterDistance); db.execSQL(alterDistance2); db.execSQL(alterDistance3); } } DistanceTable(@NonNull SQLiteOpenHelper sqLiteOpenHelper, @NonNull Table<Trip> tripsTable,
                         @NonNull Table<PaymentMethod> paymentMethodTable, @NonNull UserPreferenceManager userPreferenceManager); @Override synchronized void onCreate(@NonNull SQLiteDatabase db, @NonNull TableDefaultsCustomizer customizer); @Override synchronized void onUpgrade(@NonNull SQLiteDatabase db, int oldVersion, int newVersion, @NonNull TableDefaultsCustomizer customizer); static final String TABLE_NAME; static final String COLUMN_PARENT_TRIP_ID; static final String COLUMN_DISTANCE; static final String COLUMN_LOCATION; static final String COLUMN_DATE; static final String COLUMN_TIMEZONE; static final String COLUMN_COMMENT; static final String COLUMN_RATE; static final String COLUMN_RATE_CURRENCY; static final String COLUMN_PAYMENT_METHOD_ID; static final String COLUMN_LOCATION_HIDDEN_AUTO_COMPLETE; static final String COLUMN_COMMENT_HIDDEN_AUTO_COMPLETE; @Deprecated
    static final String COLUMN_PARENT; }
@Test public void onUpgradeFromV12() { final int oldVersion = 12; final int newVersion = DatabaseHelper.DATABASE_VERSION; final TableDefaultsCustomizer customizer = mock(TableDefaultsCustomizer.class); mDistanceTable.onUpgrade(mSQLiteDatabase, oldVersion, newVersion, customizer); verify(mSQLiteDatabase, atLeastOnce()).execSQL(mSqlCaptor.capture()); verifyZeroInteractions(customizer); assertTrue(mSqlCaptor.getAllValues().get(0).contains("distance")); assertTrue(mSqlCaptor.getAllValues().get(0).contains("id")); assertTrue(mSqlCaptor.getAllValues().get(0).contains("parent")); assertTrue(mSqlCaptor.getAllValues().get(0).contains("distance")); assertTrue(mSqlCaptor.getAllValues().get(0).contains("location")); assertTrue(mSqlCaptor.getAllValues().get(0).contains("date")); assertTrue(mSqlCaptor.getAllValues().get(0).contains("timezone")); assertTrue(mSqlCaptor.getAllValues().get(0).contains("comment")); assertTrue(mSqlCaptor.getAllValues().get(0).contains("rate")); assertTrue(mSqlCaptor.getAllValues().get(0).contains("rate_currency")); assertEquals(mSqlCaptor.getAllValues().get(0), "CREATE TABLE distance (id INTEGER PRIMARY KEY AUTOINCREMENT,parent TEXT REFERENCES name ON DELETE CASCADE,distance DECIMAL(10, 2) DEFAULT 0.00,location TEXT,date DATE,timezone TEXT,comment TEXT,rate_currency TEXT NOT NULL, rate DECIMAL(10, 2) DEFAULT 0.00);"); assertEquals(mSqlCaptor.getAllValues().get(1), "INSERT INTO distance(parent, distance, location, date, timezone, comment, rate_currency) SELECT name, miles_new , \"\" as location, from_date, from_timezone , \"\" as comment, trips_default_currency FROM trips WHERE trips_default_currency IS NOT NULL AND miles_new > 0;"); assertEquals(mSqlCaptor.getAllValues().get(2), "INSERT INTO distance(parent, distance, location, date, timezone, comment, rate_currency) SELECT name, miles_new , \"\" as location, from_date, from_timezone , \"\" as comment, \"USD\" as rate_currency FROM trips WHERE trips_default_currency IS NULL AND miles_new > 0;"); assertEquals(mSqlCaptor.getAllValues().get(3), "ALTER TABLE " + mDistanceTable.getTableName() + " ADD drive_sync_id TEXT"); assertEquals(mSqlCaptor.getAllValues().get(4), "ALTER TABLE " + mDistanceTable.getTableName() + " ADD drive_is_synced BOOLEAN DEFAULT 0"); assertEquals(mSqlCaptor.getAllValues().get(5), "ALTER TABLE " + mDistanceTable.getTableName() + " ADD drive_marked_for_deletion BOOLEAN DEFAULT 0"); assertEquals(mSqlCaptor.getAllValues().get(6), "ALTER TABLE " + mDistanceTable.getTableName() + " ADD last_local_modification_time DATE"); }
@Test public void onUpgradeFromV14() { final int oldVersion = 14; final int newVersion = DatabaseHelper.DATABASE_VERSION; final TableDefaultsCustomizer customizer = mock(TableDefaultsCustomizer.class); mDistanceTable.onUpgrade(mSQLiteDatabase, oldVersion, newVersion, customizer); verify(mSQLiteDatabase, atLeastOnce()).execSQL(mSqlCaptor.capture()); verifyZeroInteractions(customizer); assertEquals(mSqlCaptor.getAllValues().get(0), "ALTER TABLE " + mDistanceTable.getTableName() + " ADD drive_sync_id TEXT"); assertEquals(mSqlCaptor.getAllValues().get(1), "ALTER TABLE " + mDistanceTable.getTableName() + " ADD drive_is_synced BOOLEAN DEFAULT 0"); assertEquals(mSqlCaptor.getAllValues().get(2), "ALTER TABLE " + mDistanceTable.getTableName() + " ADD drive_marked_for_deletion BOOLEAN DEFAULT 0"); assertEquals(mSqlCaptor.getAllValues().get(3), "ALTER TABLE " + mDistanceTable.getTableName() + " ADD last_local_modification_time DATE"); }
@Test public void onUpgradeFromV18() { final int oldVersion = 18; final int newVersion = DatabaseHelper.DATABASE_VERSION; final TableDefaultsCustomizer customizer = mock(TableDefaultsCustomizer.class); mDistanceTable.onUpgrade(mSQLiteDatabase, oldVersion, newVersion, customizer); verify(mSQLiteDatabase, atLeastOnce()).execSQL(mSqlCaptor.capture()); verifyZeroInteractions(customizer); assertEquals(mSqlCaptor.getAllValues().get(0), String.format("ALTER TABLE %s ADD parentKey INTEGER REFERENCES %s ON DELETE CASCADE", mDistanceTable.getTableName(), TripsTable.TABLE_NAME)); assertEquals(mSqlCaptor.getAllValues().get(1), String.format("UPDATE %s SET parentKey = ( SELECT %s FROM %s WHERE %s = parent LIMIT 1 )", mDistanceTable.getTableName(), TripsTable.COLUMN_ID, TripsTable.TABLE_NAME, TripsTable.COLUMN_NAME)); assertTrue(mSqlCaptor.getAllValues().get(2).contains("CREATE TABLE " + mDistanceTable.getTableName() + "_copy (id INTEGER PRIMARY KEY AUTOINCREMENT")); assertTrue(mSqlCaptor.getAllValues().get(3).contains(String.format("INSERT INTO %s_copy", mDistanceTable.getTableName()))); assertTrue(mSqlCaptor.getAllValues().get(3).contains(String.format("FROM %s", mDistanceTable.getTableName()))); assertEquals(mSqlCaptor.getAllValues().get(4), "DROP TABLE " + mDistanceTable.getTableName() + ";"); assertEquals(mSqlCaptor.getAllValues().get(5), String.format("ALTER TABLE %s_copy RENAME TO %s;", mDistanceTable.getTableName(), mDistanceTable.getTableName())); assertEquals(mSqlCaptor.getAllValues().get(6), String.format("ALTER TABLE %s ADD entity_uuid TEXT", mDistanceTable.getTableName())); }
@Test public void onUpgradeFromV19() { final int oldVersion = 19; final int newVersion = DatabaseHelper.DATABASE_VERSION; final TableDefaultsCustomizer customizer = mock(TableDefaultsCustomizer.class); mDistanceTable.onUpgrade(mSQLiteDatabase, oldVersion, newVersion, customizer); verify(mSQLiteDatabase, atLeastOnce()).execSQL(mSqlCaptor.capture()); verifyZeroInteractions(customizer); assertEquals(mSqlCaptor.getAllValues().get(0), "ALTER TABLE " + mDistanceTable.getTableName() + " ADD paymentMethodKey INTEGER REFERENCES " + PaymentMethodsTable.TABLE_NAME + " ON DELETE NO ACTION"); assertEquals(mSqlCaptor.getAllValues().get(1), "ALTER TABLE " + mDistanceTable.getTableName() + " ADD location_hidden_auto_complete BOOLEAN DEFAULT 0"); assertEquals(mSqlCaptor.getAllValues().get(2), "ALTER TABLE " + mDistanceTable.getTableName() + " ADD comment_hidden_auto_complete BOOLEAN DEFAULT 0"); }
@Test public void onUpgradeAlreadyOccurred() { final int oldVersion = DatabaseHelper.DATABASE_VERSION; final int newVersion = DatabaseHelper.DATABASE_VERSION; final TableDefaultsCustomizer customizer = mock(TableDefaultsCustomizer.class); mDistanceTable.onUpgrade(mSQLiteDatabase, oldVersion, newVersion, customizer); verify(mSQLiteDatabase, never()).execSQL(mSqlCaptor.capture()); verifyZeroInteractions(customizer); }