Room Persistence Library - CREATE VIEW

user_1703943 0
Viewed 3042

I need to use a SQL VIEW in a query using Room Persistence Library.

Using Commonsware's answer here I've been able to run a raw SQL statement to create the view during DB creation.

 Room.databaseBuilder(context, MyDatabase.class, DB_NAME)
            .addCallback(new RoomDatabase.Callback() {
                @Override
                public void onCreate(@NonNull SupportSQLiteDatabase db) {
                    super.onCreate(db);
                    db.execSQL("CREATE VIEW view_name " +
                            "AS SELECT [...] "
                    );
                }
            })
            .build();

The VIEW is actually created on the SQLite DB and works fine, but I cannot refer to the it in my Dao's @Query because I get a compile-time error:

Error:(100, 48) error: There is a problem with the query: [SQLITE_ERROR] SQL error or missing database (no such table: view_name)

Any idea on how to let Room to know about my view or to ignore the error?

android-room
1 Answers

UPDATE 17/12/2018

Version 2.1.0 and higher of the Room persistence library now provides support for SQLite database views: https://developer.android.com/training/data-storage/room/creating-views (see D-D's comment)

UPDATE 15/12/2017

Be aware that this solution actually breaks DB migrations.

Problem is with the Entity primary key that obviously doesn't exist on the view, so migration is not valid.

See CommonsWare's comment for a possible hacky workaround.

ORIGINAL ANSWER

It seems that this is not possible at the moment using Room.

Anyway I've done it using a workaround: I've created an Entity with the same name and columns as the view (only the name is actually mandatory), this will create a table on DB and allow you to use that table name in queries without compile-time errors.

Then during Room DB creation I DROP this entity's table just before the CREATE VIEW.

Room
    .databaseBuilder(context, DueDatabase.class, DB_NAME)
    .addCallback(new RoomDatabase.Callback() {
       @Override
       public void onCreate(@NonNull SupportSQLiteDatabase db) {
          super.onCreate(db);
          //Drop the fake table and create a view with the same name
          db.execSQL("DROP TABLE view_name");
          db.execSQL("CREATE VIEW view_name " +
                     "AS SELECT [...]"
          );
       }
    })
    .build();
user_17039430
Related