Before I wrote this question, i've searched in all proposed answers, "stackoverflow" shows me but nothing fit my case.
My problem: I wrote Room migration to copy old data from a table and add it to another table, and i need to specify all the columns in the query. I have something like 130 columns.
database.execSQL("INSERT INTO newTable(col1, ..n) SELECT col1, .... coln FROM oldTable")
When i executed my migration, i got this error:
method exceeds compiler instruction limit: 16602 in androidx.room.RoomOpenHelper$ValidationResult
Thx for your helps.
CodePudding user response：
Running a test using 150 columns (151 including the id column) and thus :-
2022-05-11 06:29:50.050 16308-16308/a.a.so72188145javaroommaxcolumns D/INSERTSQL: INSERT INTO newtable (id,col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,col13,col14,col15,col16,col17,col18,col19,col20,col21,col22,col23,col24,col25,col26,col27,col28,col29,col30,col31,col32,col33,col34,col35,col36,col37,col38,col39,col40,col41,col42,col43,col44,col45,col46,col47,col48,col49,col50,col51,col52,col53,col54,col55,col56,col57,col58,col59,col60,col61,col62,col63,col64,col65,col66,col67,col68,col69,col70,col71,col72,col73,col74,col75,col76,col77,col78,col79,col80,col81,col82,col83,col84,col85,col86,col87,col88,col89,col90,col91,col92,col93,col94,col95,col96,col97,col98,col99,col100,col101,col102,col103,col104,col105,col106,col107,col108,col109,col110,col111,col112,col113,col114,col115,col116,col117,col118,col119,col120,col121,col122,col123,col124,col125,col126,col127,col128,col129,col130,col131,col132,col133,col134,col135,col136,col137,col138,col139,col140,col141,col142,col143,col144,col145,col146,col147,col148,col149,col150) SELECT id,col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,col13,col14,col15,col16,col17,col18,col19,col20,col21,col22,col23,col24,col25,col26,col27,col28,col29,col30,col31,col32,col33,col34,col35,col36,col37,col38,col39,col40,col41,col42,col43,col44,col45,col46,col47,col48,col49,col50,col51,col52,col53,col54,col55,col56,col57,col58,col59,col60,col61,col62,col63,col64,col65,col66,col67,col68,col69,col70,col71,col72,col73,col74,col75,col76,col77,col78,col79,col80,col81,col82,col83,col84,col85,col86,col87,col88,col89,col90,col91,col92,col93,col94,col95,col96,col97,col98,col99,col100,col101,col102,col103,col104,col105,col106,col107,col108,col109,col110,col111,col112,col113,col114,col115,col116,col117,col118,col119,col120,col121,col122,col123,col124,col125,col126,col127,col128,col129,col130,col131,col132,col133,col134,col135,col136,col137,col138,col139,col140,col141,col142,col143,col144,col145,col146,col147,col148,col149,col150 FROM oldtable;
Worked fine (Room 2.5.0-aplha01 and Android 30 emulator). I suspect that the issue may not be the number of columns but elsewhere in the SQL.
- The above SQL was extracted from the log, it was output to the log exactly as was then used (see testing code below).
A work-around IF you have columns that can be null and a sufficient number of them to circumvent the issue. Have an INSERT that inserts a manageable number of columns allowing the other columns to default to null, followed by 1 or more UPDATEs to apply the subsequent values to override the nulls.
Another work-around could be to extract all columns SELECT * .... into a Cursor and then build a ContentValues from the Cursor (on a per row basis) and use the SupportSQliteDatabase's insert method. Something along the lines of:-
Cursor csr = database.query("SELECT * FROM oldtable");
database.beginTransaction();
ContentValues cv = new ContentValues();
while (csr.moveToNext()) {
cv.clear();
for (int i=0; i < csr.getColumnCount(); i ) {
if (!csr.getColumnName(i).equals("col133")) { //<<<<< skip col133 as an example
cv.put(csr.getColumnName(i),csr.getString(i));
}
}
database.insert("newTable", SQLiteDatabase.CONFLICT_IGNORE,cv);
}
database.setTransactionSuccessful();
database.endTransaction();
Full code used for testing
- Note that the only change between the 2 is the version number. So initially the code is run (after uninstalling the App) with version 1 (so no Migration). To test it's just a matter, of then changing the version to 2.
The @Entity annotated class OldTable:-
@Entity
class OldTable {
@PrimaryKey
Long id=null;
String col1;
String col2;
String col3;
.... (i.e. all missing numbers)
String col147;
String col148;
String col149;
String col150;
}
The @Dao annotated interface (not actually used) AllDao
@Dao
interface AllDao {
@Insert(onConflict = OnConflictStrategy.IGNORE)
long insert(OldTable oldTable);
}
The @Database annotated class TheDatabase :-
@Database(entities = {OldTable.class},version = MainActivity.DATABASE_VERSION,exportSchema = false)
abstract class TheDatabase extends RoomDatabase {
abstract AllDao getAllDao();
private static volatile TheDatabase INSTANCE = null;
static TheDatabase getINSTANCE(Context context) {
if (INSTANCE == null) {
INSTANCE = Room.databaseBuilder(context,TheDatabase.class,MainActivity.DATABASE_NAME)
.allowMainThreadQueries()
.addMigrations(MIGRATION_1_2)
.build();
}
return INSTANCE;
}
static Migration MIGRATION_1_2 = new Migration(1,2) {
@Override
public void migrate(@NonNull SupportSQLiteDatabase database) {
StringBuilder sb = new StringBuilder();
StringBuilder sb2 = new StringBuilder();
ArrayList<String> columns = new ArrayList<>();
for (int i=0; i < MainActivity.column_count;i ) {
columns.add("col" (i 1));
if (i>0) {
sb.append(",");
sb2.append(",");
}
sb.append(columns.get(i));
sb2.append(columns.get(i)).append(" TEXT");
}
String columnsAsCSV = sb.toString();
String columnDefs = sb2.toString();
database.execSQL("DROP TABLE IF EXISTS newtable");
database.execSQL("CREATE TABLE IF NOT EXISTS newtable (id INTEGER PRIMARY KEY," columnDefs ");");
String insertSQL = "INSERT INTO newtable (id," columnsAsCSV ") SELECT id," columnsAsCSV " FROM oldtable;";
Log.d("INSERTSQL",insertSQL);
database.execSQL(insertSQL);
}
};
}
And finally putting it alltogether MainActivity :-
public class MainActivity extends AppCompatActivity {
public static final String DATABASE_NAME = "the_database.db";
public static final int DATABASE_VERSION = 2;
public static final int column_count = 150;
private ArrayList<String> column_names = new ArrayList<>();
private String allColumnsAsCSV = "";
TheDatabase db;
AllDao dao;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
BuildColumns();
db = TheDatabase.getINSTANCE(this);
dao = db.getAllDao();
SupportSQLiteDatabase supdb = db.getOpenHelper().getWritableDatabase();
StringBuilder sb = new StringBuilder();
for (int i=0;i < 100; i ) {
sb = new StringBuilder();
sb.append("INSERT INTO oldtable (").append(allColumnsAsCSV).append(") VALUES(");
for(int ii=0;ii<column_count;ii ) {
if (ii > 0) {
sb.append(",");
}
sb.append("'").append(column_names.get(ii)).append(i).append(ii).append("'");
}
sb.append(")");
supdb.execSQL(sb.toString());
}
}
void BuildColumns() {
column_names.clear();
allColumnsAsCSV = "";
StringBuilder sb = new StringBuilder();
for (int i=0; i < column_count; i ) {
column_names.add("col" (i 1));
if (i>0) {
sb.append(",");
}
sb.append(column_names.get(i));
}
allColumnsAsCSV = sb.toString();
}
}