aborting due to "no such table: legacy_available_messages_view"

[venusjivani]

Hi @residentsummer, thanks for this project.

I'm getting below error.

❯ build/Release/watoi msgstore.db ./ChatStorage.sqlite app/Payload/WhatsApp.app/Frameworks/Core.framework/WhatsAppChat.momd
2021-12-26 02:36:30.753 watoi[92347:21851470] Probing msgstore.db at path: msgstore.db
2021-12-26 02:36:30.753 watoi[92347:21851470]     ok.
2021-12-26 02:36:30.755 watoi[92347:21851470] Android store loaded
2021-12-26 02:36:30.756 watoi[92347:21851470] Probing WhatsAppChat.momd at path: app/Payload/WhatsApp.app/Frameworks/Core.framework/WhatsAppChat.momd
2021-12-26 02:36:30.756 watoi[92347:21851470]     ok.
2021-12-26 02:36:30.756 watoi[92347:21851470] Probing ChatStorage.sqlite at path: ./ChatStorage.sqlite
2021-12-26 02:36:30.756 watoi[92347:21851470]     ok.
2021-12-26 02:36:30.774 watoi[92347:21851470] CoreData loaded
CoreData: warning: Unable to load class named 'WAChatSession' for entity 'WAChatSession'.  Class not found, using default NSManagedObject instead.
CoreData: warning: Unable to load class named 'WAGroupInfo' for entity 'WAGroupInfo'.  Class not found, using default NSManagedObject instead.
....
....
....
2021-12-26 02:36:31.112 watoi[92347:21851470] Loaded 1206 chat(s)
2021-12-26 02:36:31.113 watoi[92347:21851470] no such table: legacy_available_messages_view
[1]    92347 abort      build/Release/watoi msgstore.db ./ChatStorage.sqlite

Many thanks,
VJ

[venusjivani]

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

[davidvella]

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";

[officialasit]

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 

[officialasit]

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

[davidvella]

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

[officialasit]

@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?

[davidvella]

@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:

watoi/watoi/main.m

Line 494 in 4ede6ff

NSString *text = [amsg objectForKey:@"data"]; // or null

The text column should be data in the view.

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

Updated the view again

[officialasit]

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

[venusjivani]

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

[shikharkunal99]

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

[shikharkunal99]

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