@Override
public synchronized void onCreate(
@NonNull SQLiteDatabase db, @NonNull TableDefaultsCustomizer customizer) {
super.onCreate(db, customizer);
final String sql =
"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, "
+ 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, sql);
db.execSQL(sql);
}
@Override
public synchronized void onCreate(
@NonNull SQLiteDatabase db, @NonNull TableDefaultsCustomizer customizer) {
super.onCreate(db, customizer);
final String trips =
"CREATE TABLE "
+ getTableName()
+ " ("
+ COLUMN_NAME
+ " TEXT PRIMARY KEY, "
+ COLUMN_FROM
+ " DATE, "
+ COLUMN_TO
+ " DATE, "
+ COLUMN_FROM_TIMEZONE
+ " TEXT, "
+ COLUMN_TO_TIMEZONE
+ " TEXT, "
+ COLUMN_COMMENT
+ " TEXT, "
+ COLUMN_COST_CENTER
+ " TEXT, "
+ COLUMN_DEFAULT_CURRENCY
+ " TEXT, "
+ COLUMN_PROCESSING_STATUS
+ " TEXT, "
+ COLUMN_FILTERS
+ " TEXT, "
+ 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, trips);
db.execSQL(trips);
}
@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);
// Once we create the table, we need to move our "trips" mileage into a single item in the
// distance table
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
+ "\""
+ mDefaultCurrencyCode
+ "\" 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);
}
}
@Override
public synchronized void onUpgrade(
@NonNull SQLiteDatabase db,
int oldVersion,
int newVersion,
@NonNull TableDefaultsCustomizer customizer) {
super.onUpgrade(db, oldVersion, newVersion, customizer);
if (oldVersion <= 6) { // Fix the database to replace absolute paths with relative ones
Cursor tripsCursor = null;
try {
tripsCursor =
db.query(
TripsTable.TABLE_NAME,
new String[] {TripsTable.COLUMN_NAME},
null,
null,
null,
null,
null);
if (tripsCursor != null && tripsCursor.moveToFirst()) {
final int nameIndex = tripsCursor.getColumnIndex(TripsTable.COLUMN_NAME);
do {
String absPath = tripsCursor.getString(nameIndex);
if (absPath.endsWith(File.separator)) {
absPath = absPath.substring(0, absPath.length() - 1);
}
final String relPath =
absPath.substring(absPath.lastIndexOf(File.separatorChar) + 1, absPath.length());
Logger.debug("Updating Abs. Trip Path: {} => {}", absPath, relPath);
final ContentValues tripValues = new ContentValues(1);
tripValues.put(TripsTable.COLUMN_NAME, relPath);
if (db.update(
TripsTable.TABLE_NAME,
tripValues,
TripsTable.COLUMN_NAME + " = ?",
new String[] {absPath})
== 0) {
Logger.error(this, "Trip Update Error Occured");
}
} while (tripsCursor.moveToNext());
}
} finally {
if (tripsCursor != null) {
tripsCursor.close();
}
}
}
if (oldVersion <= 8) { // Added a timezone column to the trips table
final String alterTrips1 =
"ALTER TABLE "
+ TripsTable.TABLE_NAME
+ " ADD "
+ TripsTable.COLUMN_FROM_TIMEZONE
+ " TEXT";
final String alterTrips2 =
"ALTER TABLE "
+ TripsTable.TABLE_NAME
+ " ADD "
+ TripsTable.COLUMN_TO_TIMEZONE
+ " TEXT";
Logger.debug(this, alterTrips1);
Logger.debug(this, alterTrips2);
db.execSQL(alterTrips1);
db.execSQL(alterTrips2);
}
if (oldVersion <= 10) {
final String alterTrips1 =
"ALTER TABLE " + TripsTable.TABLE_NAME + " ADD " + TripsTable.COLUMN_COMMENT + " TEXT";
final String alterTrips2 =
"ALTER TABLE "
+ TripsTable.TABLE_NAME
+ " ADD "
+ TripsTable.COLUMN_DEFAULT_CURRENCY
+ " TEXT";
Logger.debug(this, alterTrips1);
Logger.debug(this, alterTrips2);
db.execSQL(alterTrips1);
db.execSQL(alterTrips2);
}
if (oldVersion <= 11) { // Added trips filters, payment methods, and mileage table
final String alterTrips =
"ALTER TABLE " + TripsTable.TABLE_NAME + " ADD " + TripsTable.COLUMN_FILTERS + " TEXT";
Logger.debug(this, alterTrips);
db.execSQL(alterTrips);
}
if (oldVersion
<= 12) { // Added better distance tracking, cost center to the trips, and status to
// trips/receipts
final String alterTripsWithCostCenter =
"ALTER TABLE "
+ TripsTable.TABLE_NAME
+ " ADD "
+ TripsTable.COLUMN_COST_CENTER
+ " TEXT";
final String alterTripsWithProcessingStatus =
"ALTER TABLE "
+ TripsTable.TABLE_NAME
+ " ADD "
+ TripsTable.COLUMN_PROCESSING_STATUS
+ " TEXT";
Logger.debug(this, alterTripsWithCostCenter);
Logger.debug(this, alterTripsWithProcessingStatus);
db.execSQL(alterTripsWithCostCenter);
db.execSQL(alterTripsWithProcessingStatus);
}
if (oldVersion <= 14) {
onUpgradeToAddSyncInformation(db, oldVersion, newVersion);
}
}
