房间持久性库-创建视图 [英] Room Persistence Library - CREATE VIEW

问题描述

我需要在使用Room Persistence Library的查询中使用SQL VIEW.

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

在此处使用Commonsware的答案，我已经能够在数据库创建期间运行原始SQL语句来创建视图.

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();

该VIEW实际上是在SQLite数据库上创建的，并且可以正常工作，但是由于出现编译时错误，我无法在我的Dao的@Query中引用该视图:

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:

错误:(100，48)错误:查询存在问题:[SQLITE_ERROR] SQL错误或缺少数据库(没有这样的表:view_name)

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

关于如何让Room知道我的观点或忽略错误的任何想法吗?

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

推荐答案

更新17/12/2018

版本2.1.0及更高版本现在提供对SQLite数据库视图的支持: https://developer.android.com/training/data-storage/房间/创造景观 (请参阅D-D的评论)

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)

更新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.

请参阅 CommonsWare的评论，以获取可能的黑客解决方法.

See CommonsWare's comment for a possible hacky workaround.

原始答案

似乎目前无法使用Room.

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.

然后在创建Room DB的过程中，我在CREATE VIEW之前删除该实体的表.

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();

这篇关于房间持久性库-创建视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！