no such table:@DatabseView

Question

I was trying to create a database view in android but getting this error:

error: There is a problem with the query: [SQLITE_ERROR] SQL error or missing database (no such table: ChatsView) public abstract androidx.lifecycle.LiveData> getMessageViewLIst();

Please help me with this that how can i get data from a DatabaseView in room persistence android

DatabaseView Class

@DatabaseView("SELECT chats.username as username," +
    "(SELECT chat.message FROM ChatEntityModel as chat WHERE chat.username=chats.username ORDER BY id DESC LIMIT 1) as lastMsg" +
    "(SELECT chat.timeStamp FROM ChatEntityModel as chat WHERE chat.username=chats.username ORDER BY id DESC LIMIT 1) as lastMsgTime " +
    "(SELECT count(1) FROM ChatEntityModel as chat WHERE chat.username=chats.username and status=4 ) as totalNewMsgCount From ChatEntityModel as chats",viewName = "ChatsView")
data class ChatsView (
        val username:String,
        val lastMsg:String,
        val lastMsgTime:String,
        val totalNewMsgCount:String
)

Entity Class

@Entity(tableName = "ChatEntityModel")
data class ChatEntityModel (


@ColumnInfo(name = "message") var KEY_MESSAGE: String,
@ColumnInfo(name = "username") var KEY_USERNAME : String,
@ColumnInfo(name = "msgsend") var KEY_MSG_SEND : Boolean,
@ColumnInfo(name = "timeStamp") var KEY_TIMESTAMP :String,
@ColumnInfo(name = "status") var KEY_STATUS :Int,
@ColumnInfo(name = "msgid") var KEY_MSG_ID :String,
@ColumnInfo(name = "timestamp_delivered") var KEY_TIME_DELIVERED :String,
@ColumnInfo(name = "timestamp_read") var KEY_TIME_READ :String,
@ColumnInfo(name = "progress") var KEY_PROGRESS :String,
@ColumnInfo(name = "type") var KEY_TYPE : String


){
@PrimaryKey(autoGenerate = true)@ColumnInfo(name = "id")var KEY_ID:Int=0
}

DaoAccess class

@Database(entities = [(ChatEntityModel::class)], views = [(ChatsView::class)], version = 1, 
exportSchema = false)
abstract class DaoDatabaseAccess:RoomDatabase() {
abstract fun ChattingDao():ChattingDao
//    abstract fun ChattViewDao():ChattViewDao


}

Dao

@Dao
interface ChattingDao {

@Insert(onConflict = OnConflictStrategy.ABORT)
fun insertChatList(chats: List<ChatEntityModel>)

@Insert(onConflict = OnConflictStrategy.ABORT)
fun inserChat(chats: ChatEntityModel)

@Update
fun updateMovie(chats: ChatEntityModel)

@Query("SELECT * FROM ChatEntityModel WHERE id = :id_")
fun getMovie(id_: Int): ChatEntityModel

@Query("SELECT * FROM ChatEntityModel WHERE username=:Jid ORDER BY id ASC")
fun getChatList(Jid:String?): LiveData<List<ChatEntityModel>>

@Query("Update  ChatEntityModel SET status=2 , timestamp_delivered=:timeDelivered WHERE msgid=:msg_id 
and status <> 3  and username=:Jid ")
fun setChatDelivered(timeDelivered: String?,msg_id:String?,Jid: String?)

@Query("SELECT * FROM ChatEntityModel WHERE status=0 ORDER BY id LIMIT 1" )
fun getUnsentMessage(): List<ChatEntityModel>

@Query("SELECT msgid FROM ChatEntityModel WHERE status=4 and username=:username ORDER BY id" )
fun getUnReadMessage(username:String): List<String>

@Query("UPDATE ChatEntityModel SET status=1 WHERE msgid= :msgId and status=0")
fun setMessageSent(msgId: String?)

@Query("SELECT * FROM ChatEntityModel WHERE msgid =:msgId")
fun checkIfExists(msgId:String?): List<ChatEntityModel>

@Query("Update  ChatEntityModel SET status=3 , timestamp_read=:currentTimeMillis WHERE msgid in (:receiptId) and username=:Jid ")
fun setChatRead(currentTimeMillis: String?, receiptId:List<String>,Jid: String?)

@Query("SELECT * FROM ChatEntityModel WHERE  status=4 and username=:Jid ")
fun getUnReadChats(Jid: String?):LiveData<List<ChatEntityModel>>

@Query("UPDATE ChatEntityModel set status=5 WHERE  status=4 and  msgid in (:chat) ")
fun setChatReceivedRead(chat:List<String>)

@Query("SELECT * FROM ChatsView")
fun getMessageViewLIst(): LiveData<List<ChatsView>>

}

Answer 1

Maybe it's not related to your problem, but maybe it is, sometimes errors are weird. The request of your View as several SELECT which isn't correct, maybe try fixing your Query.

EDIT: For more clarity, the issue is that the Query was wrong and was missing comas.