I have a column called start_time which is of String type inside my_table. I would like to create an additional column named start_time_int (additional, because some parts of the project require the String type whereas some would do better with the int type) where I copy all the values from start_time, parse them to long and paste them to start_time_int. Is this sort of migration possible with just SQL commands?
Is this sort of migration possible with just SQL commands?
Yes, at least the end result can be achieved.
However, the format of start_time matters.
Furthermore, there is probably no need for a migration as it is very likely, again the format of start_time matters, to dynamically convert the result and to not store the same information twice.
Assuming that start_time is in the format YYYY-MM-DD hh:mm e.g. 2021-10-01 10:25 then as the format is an SQLite recognised format you can get the long version using strftime('%s',start_time) AS start_time_int when querying the table and there will be a start_time_int column that is an INTEGER (long).
Here's a Demo App that shows both techniques based upon a start_time in the format as above.
The database has two tables table1 and table2 which initially have the same schema an _id column and a start_time column.
When the database version is increased to 2 from 1 then an additional column start_time_int is added in the onUpgrade method via an ALTER TABLE table1 ADD COLUMN start_time_int INTEGER, this being followed by an UPDATE table1 SET start_time_int = strftime('%s',start_time) WHERE start_time_int IS NULL; this setting the start_time_int value (which as there is no DEFAULT specified in the start_time_int column definition is set to null).
onCreate version has also been changed. As this has demo been designed for testing the switch between versions the onCreate method has logic to apply the respective table creation.However, table2 remains unchanged. This table is used to demonstrates that there is no need to change the table as the Long value can easily be extracted.
The Database Helper (the class that extends SQLiteOpenHelper) DBHelper :-
class DBHelper extends SQLiteOpenHelper {
public static final String DBNAME = "thedatabase.db";
public static final int DBVERSION = 1; /*<<<<<<<< */
public static final String TABLE1_NAME = "table1";
public static final String TABLE2_NAME = "table2";
public static final String ID_COLUMN = BaseColumns._ID;
public static final String START_TIME_COLUMN = "start_time";
public static final String START_TIME_INT_COLUMN = "start_time_int";
SQLiteDatabase db;
private static volatile DBHelper instance;
private DBHelper(Context context) {
super(context,DBNAME,null,DBVERSION);
db = this.getWritableDatabase();
}
public static DBHelper getInstance(Context context) {
if (instance == null) {
instance = new DBHelper(context);
}
return instance;
}
@Override
public void onCreate(SQLiteDatabase db) {
if (DBVERSION == 1) {
db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE1_NAME + " (" +
ID_COLUMN + " INTEGER PRIMARY KEY" +
", " + START_TIME_COLUMN + " TEXT" +
")");
} else {
db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE1_NAME + " (" +
ID_COLUMN + " INTEGER PRIMARY KEY" +
", " + START_TIME_COLUMN + " TEXT " +
", " + START_TIME_INT_COLUMN + " INTEGER " +
")");
}
db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE2_NAME + " (" +
ID_COLUMN + " INTEGER PRIMARY KEY" +
", " + START_TIME_COLUMN + " TEXT" +
")");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
if (oldVersion < 2) {
Log.d("ONUPGRADE", "On Upgrade called. Oldversion is " + oldVersion + " New version is " + newVersion);
db.execSQL("ALTER TABLE " + TABLE1_NAME + " ADD COLUMN " + START_TIME_INT_COLUMN + " INTEGER;");
db.execSQL("UPDATE " + TABLE1_NAME + " SET " + START_TIME_INT_COLUMN + " = strftime('%s'," + START_TIME_COLUMN + ") WHERE " + START_TIME_INT_COLUMN + " IS NULL;");
}
}
public long insert(String tableName, String start_time) {
ContentValues cv = new ContentValues();
cv.put(START_TIME_COLUMN,start_time);
return db.insert(tableName,null,cv);
}
@SuppressLint("Range")
public void logTable2() {
Cursor csr = db.query(TABLE2_NAME,
new String[]{
ID_COLUMN,
START_TIME_COLUMN,
"strftime('%s'," + START_TIME_COLUMN + ") AS " + START_TIME_INT_COLUMN
},
null,null,null,null,null
);
while(csr.moveToNext()) {
Log.d(
"TABLE2INFO",
"StartTime = " + csr.getString(csr.getColumnIndex(START_TIME_COLUMN)) +
" AS INTEGER(long) " + csr.getLong(csr.getColumnIndex(START_TIME_INT_COLUMN))
);
}
}
@SuppressLint("Range")
public void logTable1V1() {
Cursor csr = db.query(TABLE2_NAME,
null, /* (all columns) */
null,null,null,null,null
);
while(csr.moveToNext()) {
Log.d(
"TABLE1V1INFO",
"StartTime = " + csr.getString(csr.getColumnIndex(START_TIME_COLUMN))
);
}
}
@SuppressLint("Range")
public void logTable1V2() {
Cursor csr = db.query(TABLE1_NAME,
null, /* (all columns) */
null,null,null,null,null
);
DatabaseUtils.dumpCursor(csr);
while(csr.moveToNext()) {
Log.d(
"TABLE1V2INFO",
"StartTime = " + csr.getString(csr.getColumnIndex(START_TIME_COLUMN)) +
" AS INTEGER(Long)" + csr.getLong(csr.getColumnIndex(START_TIME_INT_COLUMN))
);
}
}
}
Here's the code in an Activity i.e. MainActivity that opens the database, inserts some rows into table1 and table2 when the version is 1, the rows being the same for each table. If the version is 1 the logTableV1 method is used to write the data to the log. If the version is 2 then the logTableV2 is used to write the table to the log with the new start_time_int value.
In both cases the logTable2 method writes the start_time AND the start_time_int which has been dynamically generated in the SQL.
Results
Run 1 - new Install with Version 1 :-
2021-10-23 08:10:33.974 D/TABLE1V1INFO: StartTime = 2021-10-01 10:25
2021-10-23 08:10:33.974 D/TABLE1V1INFO: StartTime = 2021-10-02 11:15
2021-10-23 08:10:33.975 D/TABLE2INFO: StartTime = 2021-10-01 10:25 AS INTEGER(long) 1633083900
2021-10-23 08:10:33.975 D/TABLE2INFO: StartTime = 2021-10-02 11:15 AS INTEGER(long) 1633173300
Run 2 - Version changed to 2 :-
2021-10-23 08:15:23.944 D/TABLE1V2INFO: StartTime = 2021-10-01 10:25 AS INTEGER(Long)1633083900
2021-10-23 08:15:23.944 D/TABLE1V2INFO: StartTime = 2021-10-02 11:15 AS INTEGER(Long)1633173300
2021-10-23 08:15:23.945 D/TABLE2INFO: StartTime = 2021-10-01 10:25 AS INTEGER(long) 1633083900
2021-10-23 08:15:23.945 D/TABLE2INFO: StartTime = 2021-10-02 11:15 AS INTEGER(long) 1633173300
As previously stated the format of the start_time matters, if it is not YYYY-MM-DD hh:mm, then changes to the above would likely be required. However, the principle of either extracting the reformatted data dynamically or altering the table followed by an update apply.
You may wish to consider reading SQLite - Date and Time Functions