aborting due to "no such table: legacy_available_messages_view" about watoi HOT 11 OPEN

I got it working. It looks like Whatsapp have changed the schema of the db a lot. I had to recreate the view
and insert it into the android msgstore.db manually.

I found the original view here

This is what I wrote:

CREATE view legacy_available_messages_view
AS
  SELECT messages.*,
         chat._id          AS chat_row_id,
         expire_timestamp,
         jid.raw_string    AS key_remote_jid,
         jid2.raw_string   AS remote_resource,
         messages.text_data AS data,
         messages.from_me  AS key_from_me
  FROM   message AS messages
         JOIN chat
           ON chat._id = messages.chat_row_id
         JOIN jid AS jid
           ON chat.jid_row_id = jid._id
         LEFT JOIN jid AS jid2
                ON messages.sender_jid_row_id = jid2._id
         LEFT JOIN deleted_chat_job AS job
                ON job.chat_row_id = chat._id
         LEFT JOIN message_ephemeral AS message_ephemeral
                ON messages._id = message_ephemeral.message_row_id
  WHERE  Ifnull(NOT( ( Ifnull(messages.starred, 0) = 0
                       AND messages._id <= Ifnull(job.deleted_message_row_id, 1)
                     )
                      OR ( Ifnull(messages.starred, 0) = 1
                           AND messages._id <=
                               Ifnull(job.deleted_starred_message_row_id,
                               1) )
                      OR ( ( job.deleted_message_categories IS NOT NULL )
                           AND ( ( Ifnull(messages.starred, 0) = 0
                                   AND messages._id <=
                           Ifnull(job.deleted_categories_message_row_id,
                           1) )
                                  OR ( Ifnull(messages.starred, 0) = 1
                                       AND messages._id <=
                     Ifnull(job.deleted_categories_starred_message_row_id,
                     1) ) ) ) ), 0) 

This is me just hacking to get something to work. Some values are missing such as messages.media_wa_type from orig db (not sure what this should be).

Someone will need to change watoi code to either create the view if missing or change this query:

- (void) importMessages {
    NSString *query = @"SELECT * FROM legacy_available_messages_view where"
                       " key_remote_jid == '%@'"
                       " AND status != 6"  // Some system messages
                       " ORDER BY timestamp";

from watoi.

whatsapp-viewer also failed due to this new table, here is issue detail.

from watoi.

Hey @davidvella , I tried the sql commands on my database but now I'm getting this error:

Loaded 466 chat(s)
no such column: messages.textdata
zsh: abort      watoi/build/Release/watoi msgstore.db ./ChatStorage.sqlite 

from watoi.

Fixed by replacing messages.textdata AS text, with messages.text_data AS text, in the SQL Query.

from watoi.

Fixed by replacing messages.textdata AS text, with messages.text_data AS text, in the SQL Query.

Thanks @officialasit. I originally made changes in both the sql and the code. Thanks for spotting the typo. Will update original comment

from watoi.

@davidvella I have restored the messages, but all the messages seem to be blank. Like its showing the conversations, but no text in them. Any solutions?

from watoi.

@davidvella I have restored the messages, but all the messages seem to be blank. Like its showing the conversations, but no text in them. Any solutions?

It looks like my mistake again.

If you look at:

The text column should be data in the view.

I did hack the solution. Something else I must have changed.

Updated the view again

from watoi.

@davidvella Thanks a lot. It worked this time. :)

from watoi.

@davidvella many thanks, manages to successfully restored using the hack you suggested 🙏

from watoi.

@davidvella can you please tell the steps to insert the legacy_available_messages_view into msgstore.db?

from watoi.

nvm. i used https://sqlitebrowser.org/dl/ to open the db file and update the view

from watoi.